DROP TYPE PROD_CONTROLE_PEDCOLETA_TABLE;
/

DROP TYPE PROD_CONTROLE_PEDCOLETA_TYPE;
/

CREATE TYPE PROD_CONTROLE_PEDCOLETA_TYPE AS OBJECT (
    CDFILIAL        VARCHAR2(04),   --01
    CDREGIAO        VARCHAR2(4000), --02
    CDFORNECED      VARCHAR2(4000), --03
    NRPEDCOLETA     VARCHAR2(4000), --23 
    CDPRODUTO       VARCHAR2(10),   --04
    CDARVPROD       VARCHAR2(20),   --05
    NMPRODUTO       VARCHAR2(50),   --06
    SGUNIDADE       VARCHAR2(10),   --07
    SGUNPRODESTO    VARCHAR2(10),   --08
    COMPRADOR       VARCHAR2(12),   --09
    NRPEDIDOCOL     VARCHAR2(10),   --10
    DTINIPERCOT     DATE,           --11
    DTFINPERCOT     DATE,           --12
    CDMARCA         VARCHAR2(05),   --13
    IDITPEDPRIONEG  VARCHAR2(01),   --14
    IDPRODINCLFOR   VARCHAR2(01),   --15
    DSOBSRENEGOCIA  VARCHAR2(2000), --16
    QTPRODPEDCOL    FLOAT,          --17
    VRFATOCONV      FLOAT,          --18
    VRPRECUNITANT   FLOAT,          --19
    VRPRECUNIT      FLOAT,          --20
    VRPRECOOBJETIVO FLOAT,          --21
    VRPRECREN       FLOAT,          --22
    TOTALOBJ        FLOAT,          --23
    VLRTOTALRET     FLOAT,          --24
    NRORG           NUMBER          --25
);
/

CREATE TYPE PROD_CONTROLE_PEDCOLETA_TABLE IS TABLE OF PROD_CONTROLE_PEDCOLETA_TYPE;
/

CREATE OR REPLACE FUNCTION PROD_CONTROLE_PEDCOLETA(P_CDFILIAL       IN VARCHAR2,
                                                   P_NRPEDIDOCOL    IN VARCHAR2,
                                                   P_CDFORNECED     IN VARCHAR2,
                                                   P_NRPEDCOLETA    IN VARCHAR2,
                                                   P_SITUACAO       IN VARCHAR2,
                                                   P_CDREGIAO       IN VARCHAR2 DEFAULT 'T',
                                                   P_PARAMCOMPPRECO IN VARCHAR2 DEFAULT 'C',
                                                   P_MELHORPRECO    IN VARCHAR2 DEFAULT '1',
                                                   P_DTINIVALANT    IN DATE     DEFAULT NULL,
                                                   P_DTFINVALANT    IN DATE     DEFAULT NULL,
                                                   P_PARENT         IN VARCHAR2 DEFAULT 'N') RETURN PROD_CONTROLE_PEDCOLETA_TABLE PIPELINED 
    IS        
    OUT_RECORD PROD_CONTROLE_PEDCOLETA_TYPE;

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

    V_CDFILIAL          FILIAL.CDFILIAL%TYPE;
    V_CDFORNECED        VARCHAR2(4000);
    V_COMPRADOR         PEDCOLETA.CDOPERADOR%TYPE;
    V_NRPEDIDOCOL       PEDCOLETA.NRPEDIDOCOL%TYPE;
    V_DTINIPERCOT       PEDCOLETA.DTINIPERCOT%TYPE;
    V_DTFINPERCOT       PEDCOLETA.DTFINPERCOT%TYPE;
    V_IDITPEDPRIONEG    ITEMPEDCOL.IDITPEDPRIONEG%TYPE;
    V_DSOBSRENEGOCIA    ITEMPEDCOL.DSOBSRENEGOCIA%TYPE;
    V_NRORG             ITEMPEDCOL.NRORG%TYPE;
    V_CDREGIAO          VARCHAR2(4000);
    V_NRPEDCOLETA       VARCHAR2(4000);
    V_CDMARCA           MARCA.CDMARCA%TYPE;
    V_CDARVPROD         PRODUTO.CDARVPROD%TYPE; 
    V_CDPRODUTO         PRODUTO.CDPRODUTO%TYPE; 
    V_NMPRODUTO         PRODUTO.NMPRODUTO%TYPE; 
    V_SGUNIDADE         PRODUTO.SGUNIDADE%TYPE;
    V_VRFATOCONV        PRODUTO.VRFATOCONV%TYPE;
    V_SGUNPRODESTO      PRODUTO.SGUNIDADE%TYPE;
    V_IDPRODINCLFOR     PRMARCPEDCOL.IDPRODINCLFOR%TYPE;
    V_IDSITUPEDCOLETA   PEDCOLGERAL.IDSITUPEDCOLETA%TYPE;
    V_IDSITUPEDCOL      PEDCOLETA.IDSITUPEDCOL%TYPE;
    V_MAXNRCOLEPRECO    ITEMCOLE.NRCOLEPRECO%TYPE;

    V_VRITEMPEDCOL      FLOAT;
    V_VRPRECREN         FLOAT;
    V_VRPRECOOBJETIVO   FLOAT;
    V_TOTALOBJ          FLOAT; 
    V_QUANTIDADE        FLOAT;
    V_VRPRECRENECO      FLOAT;
    V_VLRTOTALRET       FLOAT;
    V_VRPRECUNITANT     FLOAT;
    V_FATOR_CONVERSAO   VARCHAR2(50);
BEGIN
    V_SQL := 'SELECT ITEMPEDCOL.CDFILIAL,';                           --01

    IF P_PARENT = 'S' THEN
        V_SQL := V_SQL ||
             ' LISTAGG(ITEMPEDCOL.CDFORNECED, ' || '''_''' || ') WITHIN GROUP (ORDER BY ITEMPEDCOL.CDFORNECED) CDFORNECED,'|| --02
             ' LISTAGG(ITEMPEDCOL.CDREGIAO, ' || '''_''' || ') WITHIN GROUP (ORDER BY ITEMPEDCOL.CDREGIAO) CDREGIAO,'||       --03
             ' LISTAGG(ITEMPEDCOL.NRPEDCOLETA, ' || '''_''' || ') WITHIN GROUP (ORDER BY ITEMPEDCOL.NRPEDCOLETA) CDREGIAO,';  --23
    ELSE
        V_SQL := V_SQL ||
             '       ITEMPEDCOL.CDFORNECED,'||                        --02
             '       ITEMPEDCOL.CDREGIAO,'||                          --03
             '       ITEMPEDCOL.NRPEDCOLETA,';                        --23
    END IF;

    V_SQL := V_SQL ||
             '       ITEMPEDCOL.IDITPEDPRIONEG,'||                    --04
             '       ITEMPEDCOL.DSOBSRENEGOCIA,'||                    --05
             '       ITEMPEDCOL.NRORG,'||                             --06
             '       ITEMPEDCOL.CDMARCA,'||                           --07
             '       PRODUTO.CDARVPROD,'||                            --08
             '       PRODUTO.CDPRODUTO,'||                            --09
             '       PRODUTO.NMPRODUTO,'||                            --10
             '       PRODUTO.SGUNIDADE,'||                            --11
             '       PRODUTO.VRFATOCONV,'||                           --12
             '       PRODESTO.SGUNIDADE SGUNPRODESTO,'||              --13
             '       PEDCOLETA.CDOPERADOR COMPRADOR,'||               --14
             '       PEDCOLETA.NRPEDIDOCOL,'||                        --15
             '       PEDCOLETA.DTINIPERCOT,'||                        --16
             '       PEDCOLETA.DTFINPERCOT,'||                        --17
             '       PRMARCPEDCOL.IDPRODINCLFOR,'||                   --18
             '       PEDCOLGERAL.IDSITUPEDCOLETA,'||                  --19
             '       PEDCOLETA.IDSITUPEDCOL,'||                       --20
             '       NVL(ITEMPEDCOL.VRITEMPEDCOL, 0) VRITEMPEDCOL,'|| --21
             '       SUM(NVL(ITEMPEDCOL.QTPRODPEDCOL, 0)) QUANTIDADE,'; --22

    IF P_PARAMCOMPPRECO = 'E' THEN
        V_SQL := V_SQL ||
             '       NVL(ITEMPEDCOL.VRPRECOOBJETIVO, 0) / NVL(PRODUTO.VRFATOCONV, 1) VRPRECOOBJETIVO,'||                              --23
             '       NVL(ITEMPEDCOL.VRPRECREN, 0) / NVL(PRODUTO.VRFATOCONV, 1) VRPRECREN,'||                                          --24
             '       NVL(ITEMPEDCOL.VRPRECRENECO, 0) / NVL(PRODUTO.VRFATOCONV, 1) VRPRECRENECO,'||                                    --25 
             '       (NVL(ITEMPEDCOL.VRPRECRENECO, 0) / NVL(PRODUTO.VRFATOCONV, 1)) * NVL(ITEMPEDCOL.QTPRODPEDCOL, 0) VLRTOTALRET,'|| --26 
             '       SUM((NVL(ITEMPEDCOL.VRPRECOOBJETIVO, 0) / NVL(PRODUTO.VRFATOCONV, 1)) * NVL(ITEMPEDCOL.QTPRODPEDCOL, 0)) TOTALOBJ'; --27
    ELSE 
        V_SQL := V_SQL ||
             '       NVL(ITEMPEDCOL.VRPRECOOBJETIVO, 0) VRPRECOOBJETIVO,'||                                                           --23  
             '       NVL(ITEMPEDCOL.VRPRECREN, 0) VRPRECREN,'||                                                                       --24
             '       NVL(ITEMPEDCOL.VRPRECRENECO, 0) VRPRECRENECO,'||                                                                 --25
             '       NVL(ITEMPEDCOL.VRPRECRENECO, 0) * NVL(ITEMPEDCOL.QTPRODPEDCOL, 0) VLRTOTALRET,'||                                --26 
             '       SUM(NVL(ITEMPEDCOL.VRPRECOOBJETIVO, 0) * NVL(ITEMPEDCOL.QTPRODPEDCOL, 0)) TOTALOBJ'; --27
    END IF;

    V_SQL := V_SQL ||
             '  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 PEDCOLGERAL ON PEDCOLETA.CDFILIAL = PEDCOLGERAL.CDFILIAL'||
             '                                        AND PEDCOLETA.NRPEDIDOCOL = PEDCOLGERAL.NRPEDIDOCOL'||
             '                  INNER JOIN PRMARCPEDCOL ON ITEMPEDCOL.CDFILIAL = PRMARCPEDCOL.CDFILIAL'||
             '                                         AND ITEMPEDCOL.CDPRODUTO = PRMARCPEDCOL.CDPRODUTO'||
             '                                         AND ITEMPEDCOL.CDMARCA = PRMARCPEDCOL.CDMARCA'||
             '                                         AND PEDCOLETA.NRPEDIDOCOL = PRMARCPEDCOL.NRPEDIDOCOL'||
             '                  INNER JOIN PRODUTO ON ITEMPEDCOL.CDPRODUTO = PRODUTO.CDPRODUTO'||
             '                  INNER JOIN PRODUTO PRODESTO ON PRODUTO.CDPRODESTO = PRODESTO.CDPRODUTO'||
             ' WHERE ITEMPEDCOL.CDFILIAL = '''||P_CDFILIAL||''''||
             '   AND INSTR('''||P_CDFORNECED||''', ITEMPEDCOL.CDFORNECED) > 0'||
             '   AND INSTR('''||P_NRPEDCOLETA||''', ITEMPEDCOL.NRPEDCOLETA) > 0'||
             '   AND PEDCOLETA.NRPEDIDOCOL = '''||P_NRPEDIDOCOL||'''';
    IF P_CDREGIAO != 'T' THEN
        V_SQL := V_SQL ||
             '   AND INSTR('''||P_CDREGIAO||''', ITEMPEDCOL.CDREGIAO) > 0';
    END IF;
    
    
    V_SQL := V_SQL ||
             ' GROUP BY ITEMPEDCOL.CDFILIAL,';
    IF P_PARENT = 'N' THEN
        V_SQL := V_SQL ||
             '       ITEMPEDCOL.CDFORNECED,'||
             '       ITEMPEDCOL.CDREGIAO,'||
             '       ITEMPEDCOL.NRPEDCOLETA,';
    END IF;
    
    V_SQL := V_SQL ||
             '       ITEMPEDCOL.IDITPEDPRIONEG,'||
             '       ITEMPEDCOL.DSOBSRENEGOCIA,'||
             '       ITEMPEDCOL.NRORG,'||
             '       ITEMPEDCOL.CDMARCA,'||
             '       PRODUTO.CDARVPROD,'||
             '       PRODUTO.CDPRODUTO,'||
             '       PRODUTO.NMPRODUTO,'||
             '       PRODUTO.SGUNIDADE,'||
             '       PRODUTO.VRFATOCONV,'||
             '       PRODESTO.SGUNIDADE,'||
             '       PEDCOLETA.CDOPERADOR,'||
             '       PEDCOLETA.NRPEDIDOCOL,'||
             '       PEDCOLETA.DTINIPERCOT,'||
             '       PEDCOLETA.DTFINPERCOT,'||
             '       PRMARCPEDCOL.IDPRODINCLFOR,'||
             '       PEDCOLGERAL.IDSITUPEDCOLETA,'||
             '       PEDCOLETA.IDSITUPEDCOL,'||
             '       NVL(ITEMPEDCOL.VRITEMPEDCOL, 0),';
             
    IF P_PARAMCOMPPRECO = 'E' THEN
        V_SQL := V_SQL ||
             '       NVL(ITEMPEDCOL.VRPRECOOBJETIVO, 0) / NVL(PRODUTO.VRFATOCONV, 1),'||
             '       NVL(ITEMPEDCOL.VRPRECREN, 0) / NVL(PRODUTO.VRFATOCONV, 1),'||
             '       NVL(ITEMPEDCOL.VRPRECRENECO, 0) / NVL(PRODUTO.VRFATOCONV, 1),'||
             '       (NVL(ITEMPEDCOL.VRPRECRENECO, 0) / NVL(PRODUTO.VRFATOCONV, 1)) * NVL(ITEMPEDCOL.QTPRODPEDCOL, 0)';
    ELSE 
        V_SQL := V_SQL ||
             '       NVL(ITEMPEDCOL.VRPRECOOBJETIVO, 0),'||
             '       NVL(ITEMPEDCOL.VRPRECREN, 0),'||
             '       NVL(ITEMPEDCOL.VRPRECRENECO, 0),'||
             '       NVL(ITEMPEDCOL.VRPRECRENECO, 0) * NVL(ITEMPEDCOL.QTPRODPEDCOL, 0)';
    END IF;
    
    
    
    
    
    DBMS_OUTPUT.PUT_LINE(V_SQL);
    
    OPEN C_ITEMPEDCOL FOR V_SQL;
    LOOP
        FETCH C_ITEMPEDCOL INTO V_CDFILIAL,        --01
                                V_CDFORNECED,      --02
                                V_CDREGIAO,        --03
                                V_NRPEDCOLETA,     --23 
                                V_IDITPEDPRIONEG,  --04
                                V_DSOBSRENEGOCIA,  --05
                                V_NRORG,           --06
                                V_CDMARCA,         --07
                                V_CDARVPROD,       --08
                                V_CDPRODUTO,       --09
                                V_NMPRODUTO,       --10
                                V_SGUNIDADE,       --11
                                V_VRFATOCONV,      --12
                                V_SGUNPRODESTO,    --13
                                V_COMPRADOR,       --14
                                V_NRPEDIDOCOL,     --15
                                V_DTINIPERCOT,     --16
                                V_DTFINPERCOT,     --17
                                V_IDPRODINCLFOR,   --18
                                V_IDSITUPEDCOLETA, --19
                                V_IDSITUPEDCOL,    --20
                                V_VRITEMPEDCOL,    --21
                                V_QUANTIDADE,      --22
                                V_VRPRECOOBJETIVO, --23
                                V_VRPRECREN,       --24
                                V_VRPRECRENECO,    --25
                                V_VLRTOTALRET,     --26
                                V_TOTALOBJ;        --27

        EXIT WHEN C_ITEMPEDCOL%NOTFOUND;

        V_VRPRECUNITANT := 0;

        IF INSTR(P_SITUACAO, V_IDSITUPEDCOLETA||V_IDSITUPEDCOL) > 0 OR
           INSTR(P_SITUACAO, V_IDSITUPEDCOL||V_IDSITUPEDCOLETA) > 0 THEN

            IF (P_DTINIVALANT IS NULL AND P_DTFINVALANT IS NULL)  THEN
                BEGIN
                    SELECT MAX(COLETAPR.NRCOLEPRECO)
                      INTO V_MAXNRCOLEPRECO
                      FROM COLETAPR
                     WHERE COLETAPR.CDOPERADOR   = V_COMPRADOR
                       AND NVL(COLETAPR.IDSITUCOLETA, 'C') <> 'A'
                       AND EXISTS (SELECT 1
                                     FROM ITEMCOLE
                                    WHERE ITEMCOLE.CDFILIAL = COLETAPR.CDFILIAL
                                      AND ITEMCOLE.NRCOLEPRECO = COLETAPR.NRCOLEPRECO
                                      AND ITEMCOLE.CDFILIAL     = V_CDFILIAL
                                      AND ITEMCOLE.CDPRODUTO    = V_CDPRODUTO
                                      AND ITEMCOLE.CDMARCA      = V_CDMARCA
                                      AND INSTR(V_CDREGIAO, ITEMCOLE.CDREGIAO) > 0
                                      AND INSTR(V_CDFORNECED, ITEMCOLE.CDFORNECED) > 0
                                      AND ITEMCOLE.VRPRECUNIITM > 0);

                    SELECT NVL(MIN(ITEMCOLE.VRPRECUNIITM), 0)
                      INTO V_VRPRECUNITANT
                      FROM ITEMCOLE
                     WHERE ITEMCOLE.CDFILIAL     = V_CDFILIAL
                       AND ITEMCOLE.CDPRODUTO    = V_CDPRODUTO
                       AND ITEMCOLE.CDMARCA      = V_CDMARCA
                       AND ITEMCOLE.NRCOLEPRECO  = V_MAXNRCOLEPRECO
                       AND INSTR(V_CDREGIAO, ITEMCOLE.CDREGIAO) > 0
                       AND INSTR(V_CDFORNECED, ITEMCOLE.CDFORNECED) > 0
                       AND ITEMCOLE.VRPRECUNIITM > 0;
                EXCEPTION
                    WHEN NO_DATA_FOUND THEN
                        V_VRPRECUNITANT := 0;
                END;
            ELSE
                IF P_MELHORPRECO = '1' THEN
                    BEGIN
                        SELECT NVL(MIN(ITEMCOLE.VRPRECUNIITM), 0)
                          INTO V_VRPRECUNITANT
                          FROM ITEMCOLE INNER JOIN COLETAPR ON ITEMCOLE.CDFILIAL = COLETAPR.CDFILIAL
                                                           AND ITEMCOLE.NRCOLEPRECO = COLETAPR.NRCOLEPRECO
                         WHERE ITEMCOLE.CDFILIAL     = V_CDFILIAL
                           AND INSTR(V_CDREGIAO, ITEMCOLE.CDREGIAO) > 0
                           AND INSTR(V_CDFORNECED, ITEMCOLE.CDFORNECED) > 0
                           AND ITEMCOLE.CDPRODUTO    = V_CDPRODUTO
                           AND ITEMCOLE.CDMARCA      = V_CDMARCA
                           AND COLETAPR.CDOPERADOR   = V_COMPRADOR
                           AND ITEMCOLE.VRPRECUNIITM > 0
                           AND NVL(COLETAPR.IDSITUCOLETA, 'C') <> 'A'
                           AND (ITEMCOLE.DTINIVALITM <= P_DTFINVALANT AND ITEMCOLE.DTFINVALITM >= P_DTINIVALANT);
                    EXCEPTION
                        WHEN NO_DATA_FOUND THEN
                            V_VRPRECUNITANT := 0;
                    END;
                ELSIF P_MELHORPRECO = '2' THEN
                    BEGIN
                        SELECT MAX(COLETAPR.NRCOLEPRECO)
                          INTO V_MAXNRCOLEPRECO
                          FROM COLETAPR
                         WHERE COLETAPR.CDOPERADOR   = V_COMPRADOR
                           AND NVL(COLETAPR.IDSITUCOLETA, 'C') <> 'A'
                           AND EXISTS (SELECT 1
                                         FROM ITEMCOLE
                                        WHERE ITEMCOLE.CDFILIAL    = COLETAPR.CDFILIAL
                                          AND ITEMCOLE.NRCOLEPRECO = COLETAPR.NRCOLEPRECO
                                          AND ITEMCOLE.CDFILIAL    = V_CDFILIAL
                                          AND ITEMCOLE.CDPRODUTO   = V_CDPRODUTO
                                          AND ITEMCOLE.CDMARCA     = V_CDMARCA
                                          AND INSTR(V_CDREGIAO, ITEMCOLE.CDREGIAO) > 0
                                          AND INSTR(V_CDFORNECED, ITEMCOLE.CDFORNECED) > 0
                                          AND (ITEMCOLE.DTINIVALITM <= P_DTFINVALANT AND ITEMCOLE.DTFINVALITM >= P_DTINIVALANT)
                                          AND ITEMCOLE.VRPRECUNIITM > 0);

                        SELECT NVL(MIN(ITEMCOLE.VRPRECUNIITM), 0)
                          INTO V_VRPRECUNITANT
                          FROM ITEMCOLE
                         WHERE ITEMCOLE.CDFILIAL     = V_CDFILIAL
                           AND INSTR(V_CDREGIAO, ITEMCOLE.CDREGIAO) > 0
                           AND INSTR(V_CDFORNECED, ITEMCOLE.CDFORNECED) > 0
                           AND ITEMCOLE.CDPRODUTO    = V_CDPRODUTO
                           AND ITEMCOLE.CDMARCA      = V_CDMARCA
                           AND ITEMCOLE.NRCOLEPRECO  = V_MAXNRCOLEPRECO
                           AND ITEMCOLE.VRPRECUNIITM > 0;
                    EXCEPTION
                        WHEN NO_DATA_FOUND THEN
                            V_VRPRECUNITANT := 0;
                    END;
                END IF;
            END IF;
        END IF;

        OUT_RECORD := PROD_CONTROLE_PEDCOLETA_TYPE(
            V_CDFILIAL,        --01
            V_CDREGIAO,        --02
            V_CDFORNECED,      --03
            V_NRPEDCOLETA,     --23
            V_CDPRODUTO,       --04
            V_CDARVPROD,       --05
            V_NMPRODUTO,       --06
            V_SGUNIDADE,       --07
            V_SGUNPRODESTO,    --08
            V_COMPRADOR,       --09
            V_NRPEDIDOCOL,     --10
            V_DTINIPERCOT,     --11
            V_DTFINPERCOT,     --12
            V_CDMARCA,         --13
            V_IDITPEDPRIONEG,  --14
            V_IDPRODINCLFOR,   --15
            V_DSOBSRENEGOCIA,  --16
            V_QUANTIDADE,      --17
            V_VRFATOCONV,      --18
            V_VRPRECUNITANT,   --19
            V_VRITEMPEDCOL,    --20
            V_VRPRECOOBJETIVO, --21
            V_VRPRECREN,       --22
            V_TOTALOBJ,        --23
            V_VLRTOTALRET,     --24
            V_NRORG);          --25

        PIPE ROW (OUT_RECORD);
    END LOOP;
    CLOSE C_ITEMPEDCOL;
    RETURN;
END PROD_CONTROLE_PEDCOLETA;
/