create or replace FUNCTION GET_COMPENSACAO_DEVOLUCAO(
    P_CDFILIAL IN VARCHAR2,
    P_NRLANCTONF IN VARCHAR2
) RETURN t_compensacao_table PIPELINED IS
BEGIN
    FOR r IN (
        SELECT D.CDFORNECED, D.NMRAZSOCFORN, D.NRINSJURFORN, D.IDTPIJURFORN, D.VALORAJUSTE,
               D.NRNOTAFISC, D.DTENTRSAID, D.DTBAIXAPAG, D.VRATUAPAG, D.CDREDUCONT,  
               MAX((SELECT CASE WHEN CC.CDREDUCONT IS NOT NULL AND CC.CDBANCO IS NOT NULL AND 
                                     CC.CDAGENCIA IS NOT NULL  AND CC.CDCONTCORR IS NOT NULL 
                                THEN CC.CDREDUCONT || ' / ' || CC.CDBANCO || ' / ' || CC.CDAGENCIA || ' / ' || CC.CDCONTCORR
                                ELSE NULL END
                      FROM CONTCORR CC, BANCO B
                     WHERE CC.CDREDUCONT = D.CDREDUCONT
                       AND CC.CDBANCO    = B.CDBANCO)) AS CONTACORRENTE, D.TIPO
          FROM (SELECT  FO.CDFORNECED, FO.NMRAZSOCFORN, FO.NRINSJURFORN, FO.IDTPIJURFORN,
                        (DV.VRDEVOLUCAOPAG * (CP.VRATUAPAG/BO.VRBOLPAG)) AS VALORAJUSTE,
                        NF.NRNOTAFISC, NF.DTENTRSAID, 
                        NVL(BP.DTBAIXAPAG, BO.DTVENBOLPAG) AS DTBAIXAPAG, 
                        CP.VRATUAPAG, NVL(BP.CDREDUCONT, BO.CDREDUCONT) CDREDUCONT,               
                        'CP' AS TIPO
                   FROM DEVOLUCAOPAG DV
                   JOIN BOLETPAGAR BO ON (DV.CDFILIBOLPAG = BO.CDFILIAL AND DV.NRLANBOLPAG = BO.NRLANBOLPAG)
                   JOIN CONTAPAGAR CP ON (BO.CDFILIAL = CP.CDFILIBPAG AND BO.NRLANBOLPAG = CP.NRLANBOLPAG)
                   JOIN NOTAFISCAL NF ON (CP.CDFILIAL = NF.CDFILIAL AND CP.NRLANCTONF = NF.NRLANCTONF)
                   JOIN FORNECEDOR FO ON (BO.CDFORNECED = FO.CDFORNECED)
                   LEFT JOIN BAIXAPAGA BP ON (BO.CDFILIBAIPAG = BP.CDFILIAL AND BO.NRBAIXAPAG = BP.NRBAIXAPAG)
                  WHERE DV.CDFILIAL   = P_CDFILIAL
                    AND DV.NRLANCTONF = P_NRLANCTONF
                  UNION ALL
                 SELECT FO.CDFORNECED, FO.NMRAZSOCFORN, FO.NRINSJURFORN, FO.IDTPIJURFORN, 
                        DV.VRDEVOLUCAOPAG AS VALORAJUSTE, '' AS NRNOTAFISC, NULL AS DTENTRSAID,
                        MB.DTMOVBANC AS DTBAIXAPAG,
                        MB.VRMOVBANC AS VRATUAPAG, DV.CDREDUCONT,
                        'LCC' AS TIPO                
                   FROM DEVOLUCAOPAG DV
                   JOIN MOVCREDBANC MC ON DV.NRMOVICRED = MC.NRMOVICRED AND DV.CDREDUCONT = MC.CDREDUCONT
                   JOIN MOVBANCARIO MB ON MB.NRMOVBANC  = MC.NRMOVBANC  AND MB.CDREDUCONT   = MC.CDREDUCONT
                   JOIN FORNECEDOR FO  ON MC.CDFORNECED   = FO.CDFORNECED
                  WHERE DV.CDFILIAL   = P_CDFILIAL
                    AND DV.NRLANCTONF = P_NRLANCTONF 
                  UNION ALL
                 SELECT FO.CDFORNECED, FO.NMRAZSOCFORN, FO.NRINSJURFORN, FO.IDTPIJURFORN, 
                        DV.VRDEVOLUCAOPAG AS VALORAJUSTE, '' AS NRNOTAFISC, NULL AS DTENTRSAID,
                        MB.DTMOVBANC AS DTBAIXAPAG, 
                        MB.VRMOVBANC AS VRATUAPAG, DV.CDREDUCONT,
                        'LCC' AS TIPO                 
                   FROM DEVOLUCAOPAG DV
                   JOIN MOVDEBBANC  MC ON DV.NRMOVIDEBI = MC.NRMOVIDEBI AND DV.CDREDUCONTDEBI = MC.CDREDUCONT
                   JOIN MOVBANCARIO MB ON MB.NRMOVBANC  = MC.NRMOVBANC  AND MB.CDREDUCONT     = MC.CDREDUCONT
                   JOIN FORNECEDOR FO  ON MC.CDFORNECED   = FO.CDFORNECED
                  WHERE DV.CDFILIAL   = P_CDFILIAL
                    AND DV.NRLANCTONF = P_NRLANCTONF ) D
         GROUP BY D.TIPO, D.CDFORNECED, D.NMRAZSOCFORN, D.NRINSJURFORN, D.IDTPIJURFORN, D.VALORAJUSTE,
                  D.NRNOTAFISC, D.DTENTRSAID, D.DTBAIXAPAG, D.VRATUAPAG, D.CDREDUCONT      
    ) LOOP
        PIPE ROW(t_compensacao_row(
            r.cdforneced, r.nmrazsocforn, r.nrinsjurforn, r.idtpijurforn,
            r.valorajuste, r.nrnotafisc, r.dtentrsaid, r.dtbaixapag,
            r.vratuapag, r.cdreducont, r.contacorrente, R.TIPO
        ));
    END LOOP;
    RETURN;
END;