The parameter I use is not returning anything when running proc in test by package

Asked

Viewed 30 times

1

I have a precedent that when I consult her I have the right results. However, when I pass parameters in plsql Debug it does not consider the field in question.

The field is the same type of table (varchar2) and the others work, this one does not.

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;
  • How’s your test code?

  • David, I found out that it was the name of the parameter that was giving conflict. Probably by using 'REFERENCE' at other points of the query. Thanks

  • could post your procedure how answer? vlw :)

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.