CREATE OR REPLACE FUNCTION PRICE_COLLECTION_GRID(P_CDFILIAL    IN VARCHAR2,
                                                 P_CDFORNECED  IN VARCHAR2,
                                                 P_CDREGIAO    IN VARCHAR2,
                                                 P_CDCOMPRADOR IN VARCHAR2,
                                                 P_CDOPERLANC  IN VARCHAR2,
                                                 P_DTINIVALITM IN DATE,
                                                 P_DTFINVALITM IN DATE,
                                                 P_SITUACAO    IN VARCHAR2,
                                                 P_IDGERACOT   IN VARCHAR2) RETURN PRICE_COLLECTION_GRID_TABLE PIPELINED IS
    
    OUT_RECORD PRICE_COLLECTION_GRID_TYPE;

    TYPE CURTYPE IS REF CURSOR;
    C_PEDCOLETA CURTYPE;
    V_SQL VARCHAR2(32000);

    V_CDFILIAL        PEDCOLGERAL.CDFILIAL%TYPE;
    V_NRPEDIDOCOL     PEDCOLGERAL.NRPEDIDOCOL%TYPE; 
    V_DTPEDCOLGERAL   PEDCOLGERAL.DTPEDCOLGERAL%TYPE;
    V_IDSITUPEDCOLETA PEDCOLGERAL.IDSITUPEDCOLETA%TYPE;
    V_IDRENEGOCIAPCOL PEDCOLGERAL.IDRENEGOCIAPCOL%TYPE;
    V_DTEXPIRAPEDCOL  PEDCOLGERAL.DTEXPIRAPEDCOL%TYPE;
    V_DTINIPERGERAL   PEDCOLGERAL.DTINIPERGERAL%TYPE;
    V_DTFINPERGERAL   PEDCOLGERAL.DTFINPERGERAL%TYPE;
    V_CDOPERLANC      PEDCOLGERAL.CDOPERLANC%TYPE;
    V_NRORG           PEDCOLGERAL.NRORG%TYPE;
    V_COMPRADOR       OPERADOR.CDOPERADOR%TYPE;
    V_NMCOMPRADOR     OPERADOR.NMOPERADOR%TYPE;
    V_OPERACEITE      OPERADOR.NMOPERADOR%TYPE;
    V_CDFILCENTCMP    FILIAL.CDFILIAL%TYPE;
    V_IDSISDOS        PARAMGERAL.IDSISDOS%TYPE;
    V_IDUTSTATUSLISPR PARASUPRI.IDUTSTATUSLISPR%TYPE;
    V_IDUTLPROCMP     PARASUPRI.IDUTLPROCMP%TYPE;
    V_IDSITUPEDCOL    PEDCOLETA.IDSITUPEDCOL%TYPE;
BEGIN
    BEGIN
        SELECT IDSISDOS
          INTO V_IDSISDOS
          FROM PARAMGERAL;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            V_IDSISDOS := NULL;
    END;
    
    BEGIN
        SELECT NVL(CDFILCENTCMP, CDFILIAL)
          INTO V_CDFILCENTCMP
          FROM FILIAL
         WHERE CDFILIAL = P_CDFILIAL;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            V_CDFILCENTCMP := NULL;
    END;
    
    BEGIN
        SELECT IDUTSTATUSLISPR, IDUTLPROCMP
          INTO V_IDUTSTATUSLISPR, V_IDUTLPROCMP
          FROM PARASUPRI
         WHERE CDFILIAL = V_CDFILCENTCMP;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            V_IDUTSTATUSLISPR := NULL;
            V_IDUTLPROCMP := NULL;
    END;    
    
    V_SQL := 'SELECT PEDCOLGERAL.CDFILIAL,'||            --01
             '       PEDCOLGERAL.NRPEDIDOCOL,'||         --02
             '       PEDCOLGERAL.DTPEDCOLGERAL,'||       --03
             '       PEDCOLGERAL.IDSITUPEDCOLETA,'||     --04
             '       PEDCOLGERAL.IDRENEGOCIAPCOL,'||     --05
             '       PEDCOLGERAL.DTEXPIRAPEDCOL,'||      --06
             '       PEDCOLGERAL.DTINIPERGERAL,'||       --07
             '       PEDCOLGERAL.DTFINPERGERAL,'||       --08
             '       PEDCOLGERAL.CDOPERLANC,'||          --09
             '       PEDCOLGERAL.NRORG,'||               --10
             '       OPERADOR.CDOPERADOR COMPRADOR,'||   --11
             '       OPERADOR.NMOPERADOR NMCOMPRADOR'||  --12
             '  FROM PEDCOLGERAL INNER JOIN OPERADOR ON PEDCOLGERAL.CDOPERADOR = OPERADOR.CDOPERADOR'||
             ' WHERE PEDCOLGERAL.DTINIPERGERAL >= '''||P_DTINIVALITM||''''||
             '   AND PEDCOLGERAL.DTFINPERGERAL <= '''||P_DTFINVALITM||'''';
             
    IF P_CDCOMPRADOR IS NOT NULL THEN
        V_SQL := V_SQL ||
             '   AND INSTR('''||P_CDCOMPRADOR||''', OPERADOR.CDOPERADOR) > 0';
    END IF;

    IF P_CDOPERLANC IS NOT NULL THEN
        V_SQL := V_SQL ||
             '   AND INSTR('''||P_CDOPERLANC||''', PEDCOLGERAL.CDOPERLANC) > 0';
    END IF;
    
    V_SQL := V_SQL ||    
             '   AND EXISTS (SELECT 1'||
             '                 FROM PEDCOLETA'||
             '                WHERE PEDCOLETA.CDFILIAL = PEDCOLGERAL.CDFILIAL'||
             '                  AND PEDCOLETA.NRPEDIDOCOL = PEDCOLGERAL.NRPEDIDOCOL';
    
    IF P_CDFORNECED IS NOT NULL THEN
        V_SQL := V_SQL ||
             '                  AND INSTR('''||P_CDFORNECED||''', PEDCOLETA.CDFORNECED) > 0';
    END IF;

    IF P_CDREGIAO IS NOT NULL THEN
        V_SQL := V_SQL ||
             '                  AND INSTR('''||P_CDREGIAO||''', PEDCOLETA.CDREGIAO) > 0';
    END IF;
    
    V_SQL := V_SQL ||
             '                  AND INSTR('''||P_SITUACAO||''', PEDCOLGERAL.IDRENEGOCIAPCOL || DECODE(PEDCOLGERAL.IDSITUPEDCOLETA, '''||'N'||''', PEDCOLETA.IDSITUPEDCOL, PEDCOLGERAL.IDSITUPEDCOLETA)) > 0';    
    
    IF P_IDGERACOT = 'G' THEN
        V_SQL := V_SQL ||
             '   AND EXISTS (SELECT 1'||
             '                 FROM ITEMCOLE I'||
             '                WHERE I.CDFILIAL = PEDCOLETA.CDFILIAL'||
             '                  AND I.NRPEDIDOCOL = PEDCOLETA.NRPEDIDOCOL'||
             '                  AND I.CDREGIAO = PEDCOLETA.CDREGIAO'||
             '                  AND I.CDFORNECED = PEDCOLETA.CDFORNECED)';
    ELSIF P_IDGERACOT = 'N' THEN
        V_SQL := V_SQL ||
             '   AND NOT EXISTS (SELECT 1'||
             '                     FROM ITEMCOLE I'||
             '                    WHERE I.CDFILIAL = PEDCOLETA.CDFILIAL'||
             '                      AND I.NRPEDIDOCOL = PEDCOLETA.NRPEDIDOCOL'||
             '                      AND I.CDREGIAO = PEDCOLETA.CDREGIAO'||
             '                      AND I.CDFORNECED = PEDCOLETA.CDFORNECED)';
    END IF;

    V_SQL := V_SQL ||                                    
             '   AND EXISTS (SELECT 1'||
             '                 FROM ITEMPEDCOL IPC INNER JOIN PEDCOLETA PC ON IPC.CDFILIAL = PC.CDFILIAL'||
             '                                                            AND IPC.NRPEDCOLETA = PC.NRPEDCOLETA'||
             '                                                            AND IPC.CDREGIAO = PC.CDREGIAO'||
             '                                                            AND IPC.CDFORNECED = PC.CDFORNECED'||
             '                WHERE IPC.CDFILIAL = PEDCOLETA.CDFILIAL'||
             '                  AND IPC.NRPEDCOLETA = PEDCOLETA.NRPEDCOLETA'||
             '                  AND IPC.CDREGIAO = PEDCOLETA.CDREGIAO'||
             '                  AND IPC.CDFORNECED = PEDCOLETA.CDFORNECED'||
             '                  AND PC.NRPEDIDOCOL = PEDCOLETA.NRPEDIDOCOL))';

    OPEN C_PEDCOLETA FOR V_SQL;
    LOOP
        FETCH C_PEDCOLETA INTO V_CDFILIAL,        --01
                               V_NRPEDIDOCOL,     --02
                               V_DTPEDCOLGERAL,   --03
                               V_IDSITUPEDCOLETA, --04
                               V_IDRENEGOCIAPCOL, --05 
                               V_DTEXPIRAPEDCOL,  --06 
                               V_DTINIPERGERAL,   --07
                               V_DTFINPERGERAL,   --08 
                               V_CDOPERLANC,      --09
                               V_NRORG,           --10
                               V_COMPRADOR,       --11
                               V_NMCOMPRADOR;     --12                       

        EXIT WHEN C_PEDCOLETA%NOTFOUND;

        BEGIN
            SELECT OPERADOR.NMOPERADOR
            INTO V_OPERACEITE
            FROM OPERADOR
            WHERE EXISTS(SELECT 1
                           FROM ACEITEPEDCOL
                          WHERE ACEITEPEDCOL.CDOPERADOR  = OPERADOR.CDOPERADOR
                            AND ACEITEPEDCOL.CDFILIAL    = V_CDFILIAL
                            AND ACEITEPEDCOL.NRPEDIDOCOL = V_NRPEDIDOCOL
                            AND ACEITEPEDCOL.IDACEITE    = 'N');
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                V_OPERACEITE := ' - ';
        END;

        SELECT IDSITUPEDCOL
          INTO V_IDSITUPEDCOL
          FROM PEDCOLETA
         WHERE CDFILIAL = V_CDFILIAL
           AND NRPEDIDOCOL = V_NRPEDIDOCOL
           AND ROWNUM = 1;

        OUT_RECORD := PRICE_COLLECTION_GRID_TYPE(
            V_CDFILIAL,        --01
            V_NRPEDIDOCOL,     --02
            V_DTPEDCOLGERAL,   --03
            V_IDSITUPEDCOLETA, --04
            V_IDRENEGOCIAPCOL, --05
            V_DTEXPIRAPEDCOL,  --06
            V_DTINIPERGERAL,   --07
            V_DTFINPERGERAL,   --08
            V_CDOPERLANC,      --09
            V_NRORG,           --10
            V_COMPRADOR,       --11
            V_NMCOMPRADOR,     --12
            V_OPERACEITE,      --13
            V_CDFILCENTCMP,    --14
            V_IDSISDOS,        --15
            V_IDUTSTATUSLISPR, --16
            V_IDUTLPROCMP,     --17
            V_IDSITUPEDCOL     --18
        );
        PIPE ROW (OUT_RECORD);

    END LOOP;
    CLOSE C_PEDCOLETA;
    RETURN;

END PRICE_COLLECTION_GRID;
/