DROP TYPE GRID_CONTROLE_PEDCOLETA_TABLE;
/

DROP TYPE GRID_CONTROLE_PEDCOLETA_TYPE;
/

CREATE TYPE GRID_CONTROLE_PEDCOLETA_TYPE AS OBJECT (
    CDFILIAL        VARCHAR2(04),  --01
    NRPEDIDOCOL     VARCHAR2(10),  --02
    NRPEDCOLETA     VARCHAR2(10),  --03
    IDSITUPEDCOL    VARCHAR2(01),  --04
    DTPEDCOLETA     DATE,          --05
    DTINIPERCOT     DATE,          --06
    DTFINPERCOT     DATE,          --07
    IDSITUPEDCOLETA VARCHAR2(01),  --08
    IDRENEGOCIAPCOL VARCHAR2(01),  --09
    DTEXPIRAPEDCOL  DATE,          --10
    DTINIPERGERAL   DATE,          --11
    DTFINPERGERAL   DATE,          --12
    CDOPERLANC      VARCHAR2(12),  --13
    NRORG           NUMBER,        --14
    COMPRADOR       VARCHAR2(12),  --15
    NMCOMPRADOR     VARCHAR2(40),  --16
    CDFORNECED      VARCHAR2(21),  --17
    IDTPIJURFORN    VARCHAR2(01),  --18
    NRINSJURFORN    VARCHAR2(20),  --19
    NMFANTFORN      VARCHAR2(250), --20
    NMRAZSOCFORN    VARCHAR2(100), --21
    CDREGIAO        VARCHAR2(03),  --22
    NMREGIAO        VARCHAR2(30),  --23
    OPERACEITE      VARCHAR2(40),  --24
    EXISTS_ITEMCOLE VARCHAR2(01),  --25
    CDFILCENTCMP    VARCHAR2(04),  --26
    IDSISDOS        VARCHAR2(01),  --27
    IDUTSTATUSLISPR VARCHAR2(01),  --28
    CDOPERADOR      VARCHAR2(12),  --29
    IDUTLCTRLTERMO  VARCHAR2(01),  --30
    QTDEFORN        NUMBER         --31
);
/

CREATE TYPE GRID_CONTROLE_PEDCOLETA_TABLE AS TABLE OF GRID_CONTROLE_PEDCOLETA_TYPE;
/

CREATE OR REPLACE FUNCTION GRID_CONTROLE_PEDCOLETA(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 GRID_CONTROLE_PEDCOLETA_TABLE PIPELINED IS
    
    OUT_RECORD GRID_CONTROLE_PEDCOLETA_TYPE;

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

    V_CDFILIAL        PEDCOLETA.CDFILIAL%TYPE;
    V_NRPEDIDOCOL     PEDCOLETA.NRPEDIDOCOL%TYPE; 
    V_NRPEDCOLETA     PEDCOLETA.NRPEDCOLETA%TYPE; 
    V_IDSITUPEDCOL    PEDCOLETA.IDSITUPEDCOL%TYPE;
    V_DTPEDCOLETA     PEDCOLETA.DTPEDCOLETA%TYPE;
    V_DTINIPERCOT     PEDCOLETA.DTINIPERCOT%TYPE;
    V_DTFINPERCOT     PEDCOLETA.DTFINPERCOT%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_CDFORNECED      FORNECEDOR.CDFORNECED%TYPE;  
    V_IDTPIJURFORN    FORNECEDOR.IDTPIJURFORN%TYPE;
    V_NRINSJURFORN    FORNECEDOR.NRINSJURFORN%TYPE;
    V_NMFANTFORN      FORNECEDOR.NMFANTFORN%TYPE;  
    V_NMRAZSOCFORN    FORNECEDOR.NMRAZSOCFORN%TYPE;
    V_CDREGIAO        REGIAO.CDREGIAO%TYPE;
    V_NMREGIAO        REGIAO.NMREGIAO%TYPE;
    V_OPERACEITE      OPERADOR.NMOPERADOR%TYPE;
    V_EXISTS_ITEMCOLE VARCHAR2(1);
    V_CDFILCENTCMP    FILIAL.CDFILIAL%TYPE;
    V_IDSISDOS        PARAMGERAL.IDSISDOS%TYPE;
    V_IDUTSTATUSLISPR PARASUPRI.IDUTSTATUSLISPR%TYPE;
    V_CDOPERADOR      OPERADOR.CDOPERADOR%TYPE;
    V_IDUTLCTRLTERMO  FORNECEDOR.IDUTLCTRLTERMO%TYPE;
    V_QTDEFORN        NUMBER;
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
          INTO V_IDUTSTATUSLISPR
          FROM PARASUPRI
         WHERE CDFILIAL = V_CDFILCENTCMP;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            V_IDUTSTATUSLISPR := NULL;
    END;    
    
    V_SQL := 'SELECT PEDCOLETA.CDFILIAL,'||              --01
             '       PEDCOLETA.NRPEDIDOCOL,'||           --02
             '       PEDCOLETA.NRPEDCOLETA,'||           --03
             '       PEDCOLETA.IDSITUPEDCOL,'||          --04
             '       PEDCOLETA.DTPEDCOLETA,'||           --05
             '       PEDCOLETA.DTINIPERCOT,'||           --06
             '       PEDCOLETA.DTFINPERCOT,'||           --07
             '       PEDCOLGERAL.IDSITUPEDCOLETA,'||     --08
             '       PEDCOLGERAL.IDRENEGOCIAPCOL,'||     --09
             '       PEDCOLGERAL.DTEXPIRAPEDCOL,'||      --10
             '       PEDCOLGERAL.DTINIPERGERAL,'||       --11
             '       PEDCOLGERAL.DTFINPERGERAL,'||       --12
             '       PEDCOLGERAL.CDOPERLANC,'||          --13
             '       PEDCOLGERAL.NRORG,'||               --14
             '       OPERADOR.CDOPERADOR COMPRADOR,'||   --15
             '       OPERADOR.NMOPERADOR NMCOMPRADOR,'|| --16
             '       FORNECEDOR.CDFORNECED,'||           --17
             '       FORNECEDOR.IDTPIJURFORN,'||         --18
             '       FORNECEDOR.NRINSJURFORN,'||         --19
             '       FORNECEDOR.NMFANTFORN,'||           --20
             '       FORNECEDOR.NMRAZSOCFORN,'||         --21
             '       REGIAO.CDREGIAO,'||                 --22
             '       REGIAO.NMREGIAO,'||                 --23
             '       PEDCOLETA.CDOPERADOR,'||            --24
             '       FORNECEDOR.IDUTLCTRLTERMO,'||       --25
             '       COUNT(DISTINCT(PEDCOLETA.NRPEDIDOCOL||FORNECEDOR.CDFORNECED)) OVER (PARTITION BY PEDCOLETA.NRPEDIDOCOL) QTDEFORN'|| --26 
             '  FROM PEDCOLETA INNER JOIN PEDCOLGERAL ON PEDCOLETA.CDFILIAL = PEDCOLGERAL.CDFILIAL'||
             '                                       AND PEDCOLETA.NRPEDIDOCOL = PEDCOLGERAL.NRPEDIDOCOL'||
             '                  INNER JOIN FORNECEDOR ON PEDCOLETA.CDFORNECED = FORNECEDOR.CDFORNECED'||
             '                  INNER JOIN REGIAO ON PEDCOLETA.CDREGIAO = REGIAO.CDREGIAO'||
             '                  INNER JOIN OPERADOR ON PEDCOLGERAL.CDOPERADOR = OPERADOR.CDOPERADOR'||
             ' WHERE PEDCOLETA.DTINIPERCOT >= '''||P_DTINIVALITM||''''||
             '   AND PEDCOLETA.DTFINPERCOT <= '''||P_DTFINVALITM||'''';
    
    IF P_CDFORNECED IS NOT NULL THEN
        V_SQL := V_SQL ||
             '   AND INSTR('''||P_CDFORNECED||''', FORNECEDOR.CDFORNECED) > 0';
    END IF;

    IF P_CDREGIAO IS NOT NULL THEN
        V_SQL := V_SQL ||
             '   AND INSTR('''||P_CDREGIAO||''', REGIAO.CDREGIAO) > 0';
    END IF;

    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 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_NRPEDCOLETA,     --03
                               V_IDSITUPEDCOL,    --04
                               V_DTPEDCOLETA,     --05
                               V_DTINIPERCOT,     --06
                               V_DTFINPERCOT,     --07
                               V_IDSITUPEDCOLETA, --08
                               V_IDRENEGOCIAPCOL, --09 
                               V_DTEXPIRAPEDCOL,  --10 
                               V_DTINIPERGERAL,   --11
                               V_DTFINPERGERAL,   --12 
                               V_CDOPERLANC,      --13
                               V_NRORG,           --14
                               V_COMPRADOR,       --15
                               V_NMCOMPRADOR,     --16
                               V_CDFORNECED,      --17
                               V_IDTPIJURFORN,    --18
                               V_NRINSJURFORN,    --19
                               V_NMFANTFORN,      --20
                               V_NMRAZSOCFORN,    --21
                               V_CDREGIAO,        --22
                               V_NMREGIAO,        --23
                               V_CDOPERADOR,      --24
                               V_IDUTLCTRLTERMO,  --25
                               V_QTDEFORN;        --26

        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;

        BEGIN
            SELECT 'S'
              INTO V_EXISTS_ITEMCOLE
              FROM DUAL
             WHERE EXISTS (SELECT 1
                             FROM ITEMPEDCOL INNER JOIN PEDCOLETA ON ITEMPEDCOL.CDFILIAL = PEDCOLETA.CDFILIAL
                                                                 AND ITEMPEDCOL.CDFORNECED = PEDCOLETA.CDFORNECED
                                                                 AND ITEMPEDCOL.CDREGIAO = PEDCOLETA.CDREGIAO
                                                                 AND ITEMPEDCOL.NRPEDCOLETA = PEDCOLETA.NRPEDCOLETA
                                             INNER JOIN ITEMCOLE ON ITEMPEDCOL.CDFILIAL = ITEMCOLE.CDFILIAL
                                                                AND PEDCOLETA.NRPEDIDOCOL = ITEMCOLE.NRPEDIDOCOL
                                                                AND ITEMPEDCOL.CDFORNECED = ITEMCOLE.CDFORNECED
                                                                AND ITEMPEDCOL.CDREGIAO = ITEMCOLE.CDREGIAO
                                                                AND ITEMPEDCOL.CDPRODUTO = ITEMCOLE.CDPRODUTO
                                                                AND ITEMPEDCOL.CDMARCA = ITEMCOLE.CDMARCA
                            WHERE PEDCOLETA.CDFILIAL    = V_CDFILIAL
                              AND PEDCOLETA.NRPEDIDOCOL = V_NRPEDIDOCOL
                              AND PEDCOLETA.NRPEDCOLETA = V_NRPEDCOLETA);
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                V_EXISTS_ITEMCOLE := 'N';
        END;
        
        OUT_RECORD := GRID_CONTROLE_PEDCOLETA_TYPE(
            V_CDFILIAL,        --01
            V_NRPEDIDOCOL,     --02
            V_NRPEDCOLETA,     --03
            V_IDSITUPEDCOL,    --04
            V_DTPEDCOLETA,     --05
            V_DTINIPERCOT,     --06
            V_DTFINPERCOT,     --07
            V_IDSITUPEDCOLETA, --08
            V_IDRENEGOCIAPCOL, --09
            V_DTEXPIRAPEDCOL,  --10
            V_DTINIPERGERAL,   --11
            V_DTFINPERGERAL,   --12
            V_CDOPERLANC,      --13
            V_NRORG,           --14
            V_COMPRADOR,       --15
            V_NMCOMPRADOR,     --16
            V_CDFORNECED,      --17
            V_IDTPIJURFORN,    --18
            V_NRINSJURFORN,    --19
            V_NMFANTFORN,      --20
            V_NMRAZSOCFORN,    --21
            V_CDREGIAO,        --22
            V_NMREGIAO,        --23
            V_OPERACEITE,      --24
            V_EXISTS_ITEMCOLE, --25
            V_CDFILCENTCMP,    --26
            V_IDSISDOS,        --27
            V_IDUTSTATUSLISPR, --28
            V_CDOPERADOR,      --29
            V_IDUTLCTRLTERMO,  --30
            V_QTDEFORN         --31
        );
        PIPE ROW (OUT_RECORD);
    END LOOP;
    CLOSE C_PEDCOLETA;
    RETURN;

END GRID_CONTROLE_PEDCOLETA;
/