Estou com um probleminha em uma procedure e queria a ajuda de vocês.
Quando mando compilar a procedure abaixo, o oracle me mostra o seguinte erro:
160/30 PLS-00302: component 'NUM_LANCAMENTO' must be declared
160/30 PL/SQL: ORA-00984: column not allowed here
126/18 PL/SQL: SQL Statement ignored
Acontece que eu estou usnado ALIAS para alguns resultados do meu select na construção do cursor e não posso fazer de outra maneira, ou, não sei como fazer.
alguém tem uma luz, por favor?
create or replace
PROCEDURE PROC_SAFX01_SPED
IS
--- Inicio o cursor
cursor C_CUR is
SELECT
CASE
WHEN header.LEDGER_ID = 2043 then '017'
WHEN header.LEDGER_ID = 2021 then '019'
WHEN contas.SEGMENT1 = '1020' THEN '007'
WHEN contas.SEGMENT1 = '1019' THEN '007'
WHEN contas.SEGMENT1 = '1021' THEN '007'
WHEN contas.SEGMENT1 = '1018' THEN '007'
WHEN contas.SEGMENT1 = '1017' THEN '007'
WHEN contas.SEGMENT1 = '1016' THEN '007'
WHEN contas.SEGMENT1 = '1015' THEN '007'
WHEN contas.SEGMENT1 = '1028' THEN '007'
WHEN contas.SEGMENT1 = '1024' THEN '007'
WHEN contas.SEGMENT1 = '1030' THEN '007'
WHEN contas.SEGMENT1 = '1032' THEN '007'
WHEN contas.SEGMENT1 = '1031' THEN '007'
WHEN CONTAS.SEGMENT1 = '0002' THEN '007'
END "cod_empresa" --01
,CASE
WHEN contas.SEGMENT2 = '0007' THEN '001'
WHEN contas.SEGMENT2 = '0010' THEN '002'
WHEN contas.SEGMENT2 = '0013' THEN '003'
WHEN contas.SEGMENT2 = '0012' THEN '004'
WHEN contas.SEGMENT2 = '0011' THEN '005'
WHEN contas.SEGMENT2 = '0014' THEN '006'
WHEN contas.SEGMENT2 = '0033' THEN '007'
WHEN contas.SEGMENT2 = '0008' THEN '001'
WHEN contas.SEGMENT2 = '0009' THEN '002'
WHEN contas.SEGMENT2 = '1020' THEN '003'
WHEN contas.SEGMENT2 = '1019' THEN '004'
WHEN contas.SEGMENT2 = '1021' THEN '005'
WHEN contas.SEGMENT2 = '1018' THEN '006'
WHEN contas.SEGMENT2 = '1017' THEN '007'
WHEN contas.SEGMENT2 = '1016' THEN '008'
WHEN contas.SEGMENT2 = '1015' THEN '009'
WHEN contas.SEGMENT2 = '1028' THEN '010'
WHEN contas.SEGMENT2 = '1024' THEN '011'
WHEN contas.SEGMENT2 = '1030' THEN '014'
WHEN contas.SEGMENT2 = '1032' THEN '017'
WHEN contas.SEGMENT2 = '1031' THEN '019'
WHEN contas.segment2 = '0002' THEN '001'
END "cod_estab" --02
,'20' || SUBSTR (header.default_effective_date,7,2) || SUBSTR (header.default_effective_date,4,2) || SUBSTR (header.default_effective_date,1,2) "data_operacao" --03
,contas.SEGMENT4 "conta_deb_cred" --04
,CASE
WHEN linhas.ACCOUNTED_DR < 0 THEN 'C'
WHEN linhas.ACCOUNTED_CR < 0 THEN 'D'
WHEN linhas.ACCOUNTED_DR = 0 THEN 'C'
WHEN linhas.ACCOUNTED_CR = 0 THEN 'D'
end "ind_deb_cre" --05
,lote.je_batch_id "je_batch_id"
,header.je_header_id "je_header_id"
,linhas.JE_LINE_NUM "JE_LINE_NUM"
,CASE
WHEN linhas.ACCOUNTED_DR = 0 then REPLACE (REPLACE (REPLACE (to_char (linhas.ACCOUNTED_CR,'000000000000000.99'),'.',''),'-',''),' ','')
WHEN linhas.ACCOUNTED_CR = 0 then REPLACE (REPLACE (REPLACE (to_char (linhas.ACCOUNTED_DR,'000000000000000.99'),'.',''),'-',''),' ','')
end "vlr_lancto" --07
,'@' "contra_part" --08
,contas.SEGMENT3 "centro_custo" --09
,'@' "centro_despesa" --10
,'@' "histpadrao" --11
,NULL "cod_operacao" --12
,LINHAS.DESCRIPTION "histcompl" --13
,header.je_header_id "num_lancamento" --14
FROM
(
select
LEDGER_ID
,case when ACCOUNTED_DR IS NULL then 0 ELSE ACCOUNTED_DR end "ACCOUNTED_DR"
,case when ACCOUNTED_CR IS NULL then 0 ELSE ACCOUNTED_CR end "ACCOUNTED_CR"
,je_header_id
,DESCRIPTION
,CODE_COMBINATION_ID
,JE_LINE_NUM
from apps.gl_je_lines
) linhas,
(
select
CODE_COMBINATION_ID
,SEGMENT1
,SEGMENT2
,SEGMENT3
,SEGMENT4
from APPS.GL_CODE_COMBINATIONS
) contas,
(
select
LEDGER_ID
,je_header_id
,default_effective_date
,PERIOD_NAME
,je_batch_id
from apps.gl_je_headers
) header,
(
select
je_batch_id
from APPS.GL_JE_BATCHES
) lote
WHERE 1 = 1
AND linhas.LEDGER_ID = header.LEDGER_ID
AND linhas.je_header_id = header.je_header_id
and header.je_batch_id = lote.je_batch_id
AND linhas.CODE_COMBINATION_ID = contas.CODE_COMBINATION_ID
AND HEADER.JE_HEADER_ID = 12914432
AND header.LEDGER_ID = 2043 --in (2063,2473,2433,2513,2413,2453,2493,2533,2155,2153,2157,3173,3193)
AND HEADER.PERIOD_NAME = 'JUN-11'
;
BEGIN
for C_CUR2 IN C_CUR LOOP
INSERT INTO SAFX01_SPED
(
cod_empresa
,cod_estab
,conta_deb_cred
,ind_deb_cre
,je_batch_id
,je_header_id
,JE_LINE_NUM
,vlr_lancto
,contra_part
,centro_custo
,centro_despesa
,histpadrao
,cod_operacao
,histcompl
,num_lancamento
)
VALUES
(
C_CUR2.cod_empresa
,C_CUR2.cod_estab
,C_CUR2.conta_deb_cred
,C_CUR2.ind_deb_cre
,C_CUR2.je_batch_id
,C_CUR2.je_header_id
,C_CUR2.JE_LINE_NUM
,C_CUR2.vlr_lancto
,C_CUR2.contra_part
,C_CUR2.centro_custo
,C_CUR2.centro_despesa
,C_CUR2.histpadrao
,C_CUR2.cod_operacao
,C_CUR2.histcompl
,C_CUR2.num_lancamento
);
commit;
end LOOP;
END PROC_SAFX01_SPED;
