create or replace PROCEDURE GERA_PIVOT_GRADEDISTRIBUICAO(P_LCDFILIAL    IN VARCHAR2,
                                                         P_LCDFILIDEST  IN VARCHAR2,
                                                         P_DTINIPED     IN PEDIDO.DTPEDIDO%TYPE,
                                                         P_DTFIMPED     IN PEDIDO.DTPEDIDO%TYPE,
                                                         P_DTINIENTREGA IN ITEMPEDIDO.DTENTITPED%TYPE,
                                                         P_DTFIMENTREGA IN ITEMPEDIDO.DTENTITPED%TYPE,                                                         
                                                         P_PRODINI      IN PRODUTO.CDARVPROD%TYPE,
                                                         P_PRODFIM      IN PRODUTO.CDARVPROD%TYPE,  
                                                         P_TCDFILIAL    IN VARCHAR2,
                                                         P_TCDFILIDEST  IN VARCHAR2,
                                                         P_IDSITITEMPED IN VARCHAR2,
                                                         P_EMISSAOTODOS IN VARCHAR2
                                                        ) 
IS

V_COMANDO               VARCHAR2(5000);
V_CAMPOS                VARCHAR2(5000);
V_VALUES                VARCHAR2(5000);
V_IDX                   NUMBER := 1;
V_NRPAGINA              NUMBER := 1;
V_CDKEY                 NUMBER := 1;

V_CDFILIALPED_ANTERIOR  VARCHAR2(4);             
V_CDFILIALPED           VARCHAR2(4);
V_CDFILIALPED_1         VARCHAR2(4);
V_CDFILIALPED_2         VARCHAR2(4);
V_CDFILIALPED_3         VARCHAR2(4);
V_CDFILIALPED_4         VARCHAR2(4);
V_CDFILIALPED_5         VARCHAR2(4);
V_CDFILIALPED_6         VARCHAR2(4);
V_CDFILIALPED_7         VARCHAR2(4);
V_CDFILIALPED_8         VARCHAR2(4);
V_CDFILIALPED_9         VARCHAR2(4);
V_CDFILIALPED_10        VARCHAR2(4);
V_CDFILIAL              VARCHAR2(4);
V_NMFILIAL              VARCHAR2(30);

V_CDPRODUTO             VARCHAR2(10); 
V_CDARVPROD             VARCHAR2(20); 
V_NMPRODUTO             VARCHAR2(50); 

V_DTENTITPED            DATE;

V_VRPREUNITPED          FLOAT;
V_QTITEMPED             FLOAT;
V_QTPED_1               FLOAT;
V_QTPED_2               FLOAT;
V_QTPED_3               FLOAT;
V_QTPED_4               FLOAT;
V_QTPED_5               FLOAT;
V_QTPED_6               FLOAT;
V_QTPED_7               FLOAT;
V_QTPED_8               FLOAT;
V_QTPED_9               FLOAT;
V_QTPED_10              FLOAT;

CURSOR C_PROD_DTENTREGA (C_EMISSAOTODOS  IN VARCHAR2, C_START_DATE_DTPEDIDO IN DATE, C_END_DATE_DTPEDIDO IN DATE, C_START_DATE_DTENTREGA IN DATE, C_END_DATE_DTENTREGA IN DATE, C_IDSITITEMPED IN VARCHAR2, C_CDFILIAL IN VARCHAR2, C_CDFILIALPED IN VARCHAR2, C_PRODINI IN VARCHAR2, C_PRODFIM IN VARCHAR2, C_TCDFILIAL IN VARCHAR2, C_TCDFILIDEST IN VARCHAR2) IS
SELECT DISTINCT I.CDPRODUTO, PR.CDARVPROD, PR.NMPRODUTO, TRUNC(I.DTENTITPED)  AS DTENTITPED, I.VRPREUNITPED
  FROM PEDIDO P
 INNER JOIN ITEMPEDIDO I
    ON P.CDFILIAL = I.CDFILIAL
   AND P.NRPEDIDO = I.NRPEDIDO
 INNER JOIN PRODUTO PR
    ON I.CDPRODUTO = PR.CDPRODUTO
 WHERE TRUNC(I.DTENTITPED) BETWEEN C_START_DATE_DTENTREGA AND C_END_DATE_DTENTREGA
   AND ((P.DTPEDIDO BETWEEN C_START_DATE_DTPEDIDO AND C_END_DATE_DTPEDIDO) OR C_EMISSAOTODOS = 'T')
   AND ((INSTR(C_CDFILIAL, P.CDFILIAL) > 0  ) OR C_TCDFILIAL = 'T')
   AND ((INSTR(C_CDFILIALPED, P.CDFILIALPED) > 0  ) OR C_TCDFILIDEST = 'T')
   AND (INSTR(C_IDSITITEMPED, I.IDSITITEMPED) > 0)
   AND PR.CDARVPROD BETWEEN C_PRODINI AND C_PRODFIM
   AND P.CDFILIALPED IS NOT NULL
 ORDER BY TRUNC(I.DTENTITPED);
 
CURSOR C_FILDESTINO(C_IDSITITEMPED IN VARCHAR2, C_DTENTITPED IN DATE) IS
SELECT CDFILIAL, NMFILIAL, CDFILIALPED, CASE WHEN MOD(ROWNUM,10) = 0 THEN 10 ELSE MOD(ROWNUM,10) END AS CDKEY, 
       CASE WHEN ROWNUM/10 <= 1 THEN 1 
            WHEN ROWNUM/10 <= 2 THEN 2 
            WHEN ROWNUM/10 <= 3 THEN 3
            WHEN ROWNUM/10 <= 4 THEN 4
            WHEN ROWNUM/10 <= 5 THEN 5
            WHEN ROWNUM/10 <= 6 THEN 6
            WHEN ROWNUM/10 <= 7 THEN 7
            WHEN ROWNUM/10 <= 8 THEN 8
            WHEN ROWNUM/10 <= 9 THEN 9
            WHEN ROWNUM/10 <= 10 THEN 10 ELSE 0 END NRPAGINA
 FROM(SELECT  DISTINCT P.CDFILIAL, F.NMFILIAL, P.CDFILIALPED 
        FROM PEDIDO P
       INNER JOIN FILIAL F
          ON P.CDFILIAL = F.CDFILIAL
       INNER JOIN ITEMPEDIDO I
          ON P.CDFILIAL = I.CDFILIAL
         AND P.NRPEDIDO = I.NRPEDIDO
       INNER JOIN PRODUTO PR
          ON I.CDPRODUTO = PR.CDPRODUTO
       WHERE (INSTR(C_IDSITITEMPED, I.IDSITITEMPED) > 0)
         AND TRUNC(I.DTENTITPED) = C_DTENTITPED
         AND P.CDFILIALPED IS NOT NULL
       ORDER BY NVL(P.CDFILIALPED, P.CDFILIAL));
 
FUNCTION RETORNA_QT_DESTINO(F_CDFILIAL IN VARCHAR2, F_CDFILIALPED IN VARCHAR2, F_IDSITITEMPED  IN VARCHAR2, F_CDPRODUTO IN VARCHAR2, F_DTENTITPED IN DATE, F_VRPREUNITPED IN FLOAT) RETURN FLOAT 
IS    
V_QTITEM FLOAT;
BEGIN
   BEGIN
      SELECT SUM(QTITEMPED) AS QTITEMPED
        INTO V_QTITEM
        FROM(SELECT I.CDPRODUTO, PR.CDARVPROD, PR.NMPRODUTO, 
                    CASE WHEN I.IDSITITEMPED IN ('7')         THEN (I.QTITEMPED - I.QTEXPITEMPED) 
                         WHEN I.IDSITITEMPED IN ('1','2','3') THEN (I.QTITEMPED)
                     ELSE (I.QTEXPITEMPED) END AS QTITEMPED
               FROM PEDIDO P
              INNER JOIN FILIAL F
                 ON P.CDFILIAL = F.CDFILIAL
              INNER JOIN ITEMPEDIDO I
                 ON P.CDFILIAL = I.CDFILIAL
                AND P.NRPEDIDO = I.NRPEDIDO
              INNER JOIN PRODUTO PR
                 ON I.CDPRODUTO = PR.CDPRODUTO
              WHERE P.CDFILIAL = F_CDFILIAL
                AND P.CDFILIALPED = F_CDFILIALPED
                AND (INSTR(F_IDSITITEMPED, I.IDSITITEMPED) > 0)
                AND PR.CDPRODUTO = F_CDPRODUTO
                AND TRUNC(I.DTENTITPED) = F_DTENTITPED
                AND I.VRPREUNITPED = F_VRPREUNITPED
                AND P.CDFILIALPED IS NOT NULL)
       GROUP BY CDPRODUTO, CDARVPROD, NMPRODUTO;
   EXCEPTION 
        WHEN NO_DATA_FOUND THEN                                        
         V_QTITEMPED := 0;
   END;

RETURN V_QTITEM; 
   
END RETORNA_QT_DESTINO;

BEGIN

   DELETE FROM PRODFILIDEST; --SEMPRE DELETA, NO INICIO PARA REGERAR A ATABELA COM NOVO FILTRO
   COMMIT;
   
   /*PRODUTOS*/ 
   OPEN C_PROD_DTENTREGA(P_EMISSAOTODOS, P_DTINIPED, P_DTFIMPED, P_DTINIENTREGA, P_DTFIMENTREGA, P_IDSITITEMPED, P_LCDFILIAL, P_LCDFILIDEST, P_PRODINI, P_PRODFIM, P_TCDFILIAL, P_TCDFILIDEST);
      LOOP
      
         FETCH C_PROD_DTENTREGA INTO V_CDPRODUTO, V_CDARVPROD, V_NMPRODUTO, V_DTENTITPED, V_VRPREUNITPED;
         EXIT WHEN C_PROD_DTENTREGA%NOTFOUND;
         
         OPEN C_FILDESTINO(P_IDSITITEMPED, V_DTENTITPED);
            LOOP
            
               FETCH C_FILDESTINO INTO V_CDFILIAL, V_NMFILIAL, V_CDFILIALPED, V_CDKEY, V_NRPAGINA ;
               EXIT WHEN C_FILDESTINO%NOTFOUND;                         
               
               CASE V_CDKEY
                  WHEN 1  THEN  V_CDFILIALPED_1 := V_CDFILIALPED; 
                                V_QTPED_1  :=  RETORNA_QT_DESTINO(V_CDFILIAL, V_CDFILIALPED, P_IDSITITEMPED, V_CDPRODUTO, V_DTENTITPED, V_VRPREUNITPED); 

                  WHEN 2  THEN  V_CDFILIALPED_2 := V_CDFILIALPED;   
                                V_QTPED_2  :=  RETORNA_QT_DESTINO(V_CDFILIAL, V_CDFILIALPED, P_IDSITITEMPED, V_CDPRODUTO, V_DTENTITPED, V_VRPREUNITPED);                              

                  WHEN 3  THEN  V_CDFILIALPED_3 := V_CDFILIALPED;   
                                V_QTPED_3  :=  RETORNA_QT_DESTINO(V_CDFILIAL, V_CDFILIALPED, P_IDSITITEMPED, V_CDPRODUTO, V_DTENTITPED, V_VRPREUNITPED);

                  WHEN 4  THEN  V_CDFILIALPED_4 := V_CDFILIALPED;   
                                V_QTPED_4  :=  RETORNA_QT_DESTINO(V_CDFILIAL, V_CDFILIALPED, P_IDSITITEMPED, V_CDPRODUTO, V_DTENTITPED, V_VRPREUNITPED);  

                  WHEN 5  THEN  V_CDFILIALPED_5 := V_CDFILIALPED;   
                                V_QTPED_5  :=  RETORNA_QT_DESTINO(V_CDFILIAL, V_CDFILIALPED, P_IDSITITEMPED, V_CDPRODUTO, V_DTENTITPED, V_VRPREUNITPED);  

                  WHEN 6  THEN  V_CDFILIALPED_6 := V_CDFILIALPED;   
                                V_QTPED_6  :=  RETORNA_QT_DESTINO(V_CDFILIAL, V_CDFILIALPED, P_IDSITITEMPED, V_CDPRODUTO, V_DTENTITPED, V_VRPREUNITPED); 

                  WHEN 7  THEN  V_CDFILIALPED_7 := V_CDFILIALPED;   
                                V_QTPED_7  :=  RETORNA_QT_DESTINO(V_CDFILIAL, V_CDFILIALPED, P_IDSITITEMPED, V_CDPRODUTO, V_DTENTITPED, V_VRPREUNITPED);   

                  WHEN 8  THEN  V_CDFILIALPED_8 := V_CDFILIALPED;   
                                V_QTPED_8  :=  RETORNA_QT_DESTINO(V_CDFILIAL, V_CDFILIALPED, P_IDSITITEMPED, V_CDPRODUTO, V_DTENTITPED, V_VRPREUNITPED);

                  WHEN 9  THEN  V_CDFILIALPED_9 := V_CDFILIALPED;   
                                V_QTPED_9  :=  RETORNA_QT_DESTINO(V_CDFILIAL, V_CDFILIALPED, P_IDSITITEMPED, V_CDPRODUTO, V_DTENTITPED, V_VRPREUNITPED); 

                          ELSE V_CDFILIALPED_10 := V_CDFILIALPED;   
                               V_QTPED_10 :=   RETORNA_QT_DESTINO(V_CDFILIAL, V_CDFILIALPED, P_IDSITITEMPED, V_CDPRODUTO, V_DTENTITPED, V_VRPREUNITPED);  
                               V_COMANDO := NULL;
                               V_COMANDO := 'INSERT INTO PRODFILIDEST
                                                 (NRPAGINA, CDFILIAL, NMFILIAL, CDPRODUTO, CDARVPROD, NMPRODUTO, DTENTITPED, VRPREUNITPED, 
                                                  CDFILIALPED_1, 
                                                  QTPED_1,
                                                  CDFILIALPED_2, 
                                                  QTPED_2,
                                                  CDFILIALPED_3,
                                                  QTPED_3,                                     
                                                  CDFILIALPED_4,
                                                  QTPED_4,
                                                  CDFILIALPED_5,
                                                  QTPED_5,
                                                  CDFILIALPED_6, 
                                                  QTPED_6,
                                                  CDFILIALPED_7,
                                                  QTPED_7,
                                                  CDFILIALPED_8, 
                                                  QTPED_8,
                                                  CDFILIALPED_9,
                                                  QTPED_9,
                                                  CDFILIALPED_10,
                                                  QTPED_10                                     
                                                  )
                                              VALUES
                                                 ('||CHR(39)||V_NRPAGINA      ||CHR(39)||', '
                                                   ||CHR(39)||V_CDFILIAL      ||CHR(39)||', '
                                                   ||CHR(39)||V_NMFILIAL      ||CHR(39)||', '
                                                   ||CHR(39)||V_CDPRODUTO     ||CHR(39)||', '
                                                   ||CHR(39)||V_CDARVPROD     ||CHR(39)||', '
                                                   ||CHR(39)||V_NMPRODUTO     ||CHR(39)||', '
                                                   ||CHR(39)||V_DTENTITPED    ||CHR(39)||', '
                                                   ||CHR(39)||V_VRPREUNITPED  ||CHR(39)||', '
                                                   ||CHR(39)||V_CDFILIALPED_1 ||CHR(39)||', '
                                                   ||CHR(39)||V_QTPED_1       ||CHR(39)||', '
                                                   ||CHR(39)||V_CDFILIALPED_2 ||CHR(39)||', '
                                                   ||CHR(39)||V_QTPED_2       ||CHR(39)||', '
                                                   ||CHR(39)||V_CDFILIALPED_3 ||CHR(39)||', '
                                                   ||CHR(39)||V_QTPED_3       ||CHR(39)||', '                              
                                                   ||CHR(39)||V_CDFILIALPED_4 ||CHR(39)||', '
                                                   ||CHR(39)||V_QTPED_4       ||CHR(39)||', '
                                                   ||CHR(39)||V_CDFILIALPED_5 ||CHR(39)||', '
                                                   ||CHR(39)||V_QTPED_5       ||CHR(39)||', '
                                                   ||CHR(39)||V_CDFILIALPED_6 ||CHR(39)||', '
                                                   ||CHR(39)||V_QTPED_6       ||CHR(39)||', '
                                                   ||CHR(39)||V_CDFILIALPED_7 ||CHR(39)||', '
                                                   ||CHR(39)||V_QTPED_7       ||CHR(39)||', '
                                                   ||CHR(39)||V_CDFILIALPED_8 ||CHR(39)||', '
                                                   ||CHR(39)||V_QTPED_8       ||CHR(39)||', '
                                                   ||CHR(39)||V_CDFILIALPED_9 ||CHR(39)||', '
                                                   ||CHR(39)||V_QTPED_9       ||CHR(39)||', '
                                                   ||CHR(39)||V_CDFILIALPED_10||CHR(39)||', '
                                                   ||CHR(39)||V_QTPED_10      ||CHR(39)||')';
                                                   
                               EXECUTE IMMEDIATE V_COMANDO;                         
                               COMMIT;

                               V_CDFILIALPED_1  := '';
                               V_QTPED_1        := 0 ;   
                               V_CDFILIALPED_2  := ''; 
                               V_QTPED_2        := 0 ;       
                               V_CDFILIALPED_3  := ''; 
                               V_QTPED_3        := 0 ;       
                               V_CDFILIALPED_4  := ''; 
                               V_QTPED_4        := 0 ;       
                               V_CDFILIALPED_5  := '';
                               V_QTPED_5        := 0 ;       
                               V_CDFILIALPED_6  := ''; 
                               V_QTPED_6        := 0 ;       
                               V_CDFILIALPED_7  := ''; 
                               V_QTPED_7        := 0 ;       
                               V_CDFILIALPED_8  := ''; 
                               V_QTPED_8        := 0 ;       
                               V_CDFILIALPED_9  := ''; 
                               V_QTPED_9        := 0 ;       
                               V_CDFILIALPED_10 := '';
                               V_QTPED_10       := 0 ;      
           
               END CASE;
               
            END LOOP;--FIM DO LOOP DE C_FILDESTINO
         CLOSE C_FILDESTINO;-- CLOSE DO CURSOR C_FILDESTINO 
         
         V_COMANDO := NULL;
         V_COMANDO := 'INSERT INTO PRODFILIDEST
                           (NRPAGINA, CDFILIAL, NMFILIAL, CDPRODUTO, CDARVPROD, NMPRODUTO, DTENTITPED, VRPREUNITPED, 
                            CDFILIALPED_1, 
                            QTPED_1,
                            CDFILIALPED_2, 
                            QTPED_2,
                            CDFILIALPED_3,
                            QTPED_3,                                     
                            CDFILIALPED_4,
                            QTPED_4,
                            CDFILIALPED_5,
                            QTPED_5,
                            CDFILIALPED_6, 
                            QTPED_6,
                            CDFILIALPED_7,
                            QTPED_7,
                            CDFILIALPED_8, 
                            QTPED_8,
                            CDFILIALPED_9,
                            QTPED_9,
                            CDFILIALPED_10,
                            QTPED_10                                     
                            )
                        VALUES
                           ('||CHR(39)||V_NRPAGINA      ||CHR(39)||', '
                             ||CHR(39)||V_CDFILIAL      ||CHR(39)||', '
                             ||CHR(39)||V_NMFILIAL      ||CHR(39)||', '
                             ||CHR(39)||V_CDPRODUTO     ||CHR(39)||', '
                             ||CHR(39)||V_CDARVPROD     ||CHR(39)||', '
                             ||CHR(39)||V_NMPRODUTO     ||CHR(39)||', '
                             ||CHR(39)||V_DTENTITPED    ||CHR(39)||', '
                             ||CHR(39)||V_VRPREUNITPED  ||CHR(39)||', '
                             ||CHR(39)||V_CDFILIALPED_1 ||CHR(39)||', '
                             ||CHR(39)||V_QTPED_1       ||CHR(39)||', '
                             ||CHR(39)||V_CDFILIALPED_2 ||CHR(39)||', '
                             ||CHR(39)||V_QTPED_2       ||CHR(39)||', '
                             ||CHR(39)||V_CDFILIALPED_3 ||CHR(39)||', '
                             ||CHR(39)||V_QTPED_3       ||CHR(39)||', '                              
                             ||CHR(39)||V_CDFILIALPED_4 ||CHR(39)||', '
                             ||CHR(39)||V_QTPED_4       ||CHR(39)||', '
                             ||CHR(39)||V_CDFILIALPED_5 ||CHR(39)||', '
                             ||CHR(39)||V_QTPED_5       ||CHR(39)||', '
                             ||CHR(39)||V_CDFILIALPED_6 ||CHR(39)||', '
                             ||CHR(39)||V_QTPED_6       ||CHR(39)||', '
                             ||CHR(39)||V_CDFILIALPED_7 ||CHR(39)||', '
                             ||CHR(39)||V_QTPED_7       ||CHR(39)||', '
                             ||CHR(39)||V_CDFILIALPED_8 ||CHR(39)||', '
                             ||CHR(39)||V_QTPED_8       ||CHR(39)||', '
                             ||CHR(39)||V_CDFILIALPED_9 ||CHR(39)||', '
                             ||CHR(39)||V_QTPED_9       ||CHR(39)||', '
                             ||CHR(39)||V_CDFILIALPED_10||CHR(39)||', '
                             ||CHR(39)||V_QTPED_10      ||CHR(39)||')';
                             
         EXECUTE IMMEDIATE V_COMANDO;                         
         COMMIT; 

         V_CDFILIALPED_1  := '';
         V_QTPED_1        := 0 ;   
         V_CDFILIALPED_2  := ''; 
         V_QTPED_2        := 0 ;       
         V_CDFILIALPED_3  := ''; 
         V_QTPED_3        := 0 ;       
         V_CDFILIALPED_4  := ''; 
         V_QTPED_4        := 0 ;       
         V_CDFILIALPED_5  := '';
         V_QTPED_5        := 0 ;       
         V_CDFILIALPED_6  := ''; 
         V_QTPED_6        := 0 ;       
         V_CDFILIALPED_7  := ''; 
         V_QTPED_7        := 0 ;       
         V_CDFILIALPED_8  := ''; 
         V_QTPED_8        := 0 ;       
         V_CDFILIALPED_9  := ''; 
         V_QTPED_9        := 0 ;       
         V_CDFILIALPED_10 := '';
         V_QTPED_10       := 0 ;         
         
      END LOOP;--FIM DO LOOP DE C_PROD_DTENTREGA
   CLOSE C_PROD_DTENTREGA;-- CLOSE DO CURSOR C_PROD_DTENTREGA  
   
   COMMIT;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('GERA_PIVOT_GRADEDISTRIBUICAO','6.35.045'); END;
/
