I have a procedure that when I make the query in it I have the correct results. However, when I pass via parameters in the plsql Debug it does not consider the field in question.
The field is the same type as the table (varchar2) and the others work, not this one.
PROCEDURE REL_GERACAO_ARQUIVO_REPASSE (DADOS OUT T_CURSOR,
DATA_CREDITO IN NVARCHAR2,
DATA_REPASSE IN NVARCHAR2,
EMPRESA IN NUMBER,
**REFERENCIA** IN VARCHAR2) IS
BEGIN
OPEN DADOS FOR
SELECT distinct
('01/'||lpad(result.REFERENCIA,2,1)||
'/'||substr(result.REFERENCIA,3,6)||
lpad(1,10,0)||
lpad(result.COD_EMPRS,10,0)||
lpad(vfss.num_plbnf,10,0)||
DATA_CREDITO || --- Parâmetro data de Crédito (Digitar a data entre caspeta simples)
DATA_REPASSE || --- Parâmetro data do Repasse (Digitar a data entre caspeta simples)
lpad(RESULT.COD_VERBA,10,0)||
lpad(abs(SUM(RESULT.ValorVerba)*100000000),21,0)||
lpad(abs(SUM(RESULT.vlr_acerto)*100000000),21,0)) AS "Geração - Arquivo de Repasse"
FROM
att.verba_fss vfss,
(
SELECT D.ID_MODULO,
D.ID_ROTINA,
D.REFERENCIA,
D.COD_EMPRS,
E.NOM_ABRVO_EMPRS,
E.NOM_RZSOC_EMPRS,
V.COD_GRUPO_FINANCEIRO,
V.COD_SUBGRUPO_FINANCEIRO,
D.NUM_RGTRO_EMPRG,
V.ORDEM ORDEM3,
D.COD_VERBA,
VER.DCR_VERBA,
DECODE(V.CRED_DEB,'C',1,-1) * D.VLR_DESCONTO AS ValorVerba,
0 as vlr_acerto,
V.CRED_DEB
FROM INT_FCO.TB_SCA_IMP_DESCONTO D
INNER JOIN INT_FCO.TB_SCR_SUBGRUPO_FINANC_VERBA V ON (D.COD_VERBA = V.COD_VERBA and v.fl_autopatroc = 0 )
INNER JOIN ATT.EMPRESA E ON (D.COD_EMPRS = E.COD_EMPRS)
INNER JOIN ATT.VERBA VER ON (D.COD_VERBA = VER.COD_VERBA)
WHERE D.ID_MODULO = 6
AND D.ID_ROTINA in (10,12)
AND D.FL_REPASSE = 1
) RESULT
WHERE RESULT.COD_EMPRS = EMPRESA --- Parâmetro empresa
AND RESULT.REFERENCIA = **REFERENCIA** --- Parâmetro mês ano (mmyyyy)
AND RESULT.COD_VERBA >=90000
AND RESULT.COD_VERBA = vfss.num_vrbfss
GROUP BY result.REFERENCIA,
RESULT.COD_EMPRS,
RESULT.COD_VERBA,
vfss.num_plbnf
;
END;