CREATE OR REPLACE
PROCEDURE REQUISICAO_ANALITICA_SETOR_PLA
  (P_FILIAL       IN VARCHAR2,
   P_SERVICOS     IN VARCHAR2,
   P_DTINICIAL    IN DATE,
   P_DTFINAL      IN DATE,
   P_IDARQUIVO    IN VARCHAR2,
   P_IDPRODUTOS   IN VARCHAR2,
   P_IDSEPPRATO   IN VARCHAR2,
   P_PEACRESCIMO  IN NUMBER,
   P_IDARREDOND   IN VARCHAR2,
   P_NRCONSPLAN   IN VARCHAR2,
   P_CDPRODUTOINI IN VARCHAR2,
   P_CDPRODUTOFIN IN VARCHAR2,
   P_CDOPERADOR   IN VARCHAR2,
   P_DTHRCALCPR   IN DATE,
   P_STDTINICIAL  IN VARCHAR2 DEFAULT NULL,
   P_STDTFINAL    IN VARCHAR2 DEFAULT NULL) IS

   V_CDSERVFILI    VARCHAR2(5);
   V_CDSERVFILIAUX VARCHAR2(4000);
   V_CDPRODUTOFIN  VARCHAR2(10);
   V_CDFILIAL      VARCHAR(4);   
   
   V_LISTA_SERV_TRANS VARCHAR2(4000);   
   V_NRPRPREVRETI VARCHAR2(4);  
   V_NRLISTA VARCHAR2(20);
   V_NRCONSPLAN VARCHAR2(20);

   /* - Declaracao de cursores ---------------------------------------------- */

   CURSOR C_FILIAIS IS
      SELECT DISTINCT CDFILIAL
        FROM SERVICO
       WHERE INSTR(P_FILIAL, CDFILIAL) > 0           
       UNION    
      SELECT DISTINCT SUBSTR(CDLISTA, 1, 4) AS CDFILIAL
        FROM LISTA
       WHERE NRLISTA = P_SERVICOS;  
BEGIN
    
    NOVO_CODIGO('LISTA', V_NRLISTA);
    V_NRLISTA := SUBSTR(V_NRLISTA, 11, 10);
	
   /* REGISTRA O CALCULO NA TABELA DE LOG */   
   OPEN C_FILIAIS;
   LOOP
      FETCH C_FILIAIS INTO V_CDFILIAL;
      EXIT WHEN C_FILIAIS%NOTFOUND;            
      FOR C_SERVICO IN(             
             SELECT CDSERVFILI, IDSERVTRAN  
               FROM SERVICO 
              WHERE CDFILIAL = V_CDFILIAL 
                AND (INSTR(P_SERVICOS, CDSERVFILI) > 0 OR P_SERVICOS = 'T') 
                AND NOT EXISTS (SELECT SUBSTR(CDLISTA, 5, 5) AS CDSERVFILI 
                                  FROM LISTA 
                                 WHERE NRLISTA = P_SERVICOS  
                                   AND SUBSTR(CDLISTA, 1, 4) = V_CDFILIAL) 
              UNION        
             SELECT SERVICO.CDSERVFILI, SERVICO.IDSERVTRAN
               FROM LISTA, SERVICO 
              WHERE NRLISTA = P_SERVICOS  
                AND SUBSTR(CDLISTA, 1, 4) = V_CDFILIAL
                AND SERVICO.CDFILIAL = V_CDFILIAL
                AND SERVICO.CDSERVFILI = SUBSTR(CDLISTA, 5, 5))
      LOOP 
         INSERT INTO LOGPREVRETI
           (CDFILIAL,
            CDOPERADOR,
            DTHRCALCPR,
            NRCONSPLANPR,
            CDSERVFILI,
            DTINICIALPR,
            DTFINALPR,
            CDPRODINICPR,
            CDPRODFINAPR,
            PEACRESCIMPR)
         VALUES
           (V_CDFILIAL,
            P_CDOPERADOR,
            P_DTHRCALCPR,
            P_NRCONSPLAN,
            C_SERVICO.CDSERVFILI,
            P_DTINICIAL,
            P_DTFINAL,
            P_CDPRODUTOINI,
            P_CDPRODUTOFIN,
            P_PEACRESCIMO);         

         IF (C_SERVICO.IDSERVTRAN <> 'S') THEN 
            V_CDSERVFILIAUX := V_CDSERVFILIAUX || '_' || C_SERVICO.CDSERVFILI; 
         ELSE         
            V_LISTA_SERV_TRANS := V_LISTA_SERV_TRANS || C_SERVICO.CDSERVFILI || '_';
            INSERT INTO LISTA (NRLISTA, CDLISTA) VALUES (V_NRLISTA, V_CDFILIAL||C_SERVICO.CDSERVFILI);
             
             DELETE FROM PREVRETI
              WHERE CDFILIAL   = V_CDFILIAL            
                AND CDSERVFILI = C_SERVICO.CDSERVFILI
                AND DTPREVRETI BETWEEN P_DTINICIAL AND P_DTFINAL
                AND NRREQUEXTR = '0000000000'
                AND INSTR(P_IDPRODUTOS, IDPREVRETI) > 0;

         END IF;
      END LOOP;        
      
      V_CDPRODUTOFIN := RPAD(P_CDPRODUTOFIN,10,'z');
        
       /*INICIO - Para servicos transportada e verificado se houve distribuicao efetivada dos itens*/   
        IF (V_LISTA_SERV_TRANS IS NOT NULL) THEN
            /*gera consplan para buscar pratos que nao sao representados por um produto de producao*/ 
            CONSPLAN_GERAL(V_NRLISTA, P_DTINICIAL, P_DTFINAL, P_IDPRODUTOS, 'P', 0, 'N', '0', 'ZZZZZZZZZZ',
                           P_CDOPERADOR, 'GERANOVCOD', V_NRCONSPLAN);    
                           
           FOR C_SERVTRANS IN 
                (SELECT
                 CDSERVFILI, HRINICATEN, IDUTRETIPRAT
              FROM
                 SERVICO
              WHERE
                 (CDFILIAL = V_CDFILIAL) AND
                 ((INSTR(V_LISTA_SERV_TRANS, CDSERVFILI) > 0)))
            LOOP                       
                V_NRPRPREVRETI := '0000';
                  
                FOR C_INSERT_PREV IN 
                    (SELECT CDFILIAL, CDSERVFILI, DTCARDAPIO AS DTPREVRETI, CDPRODUTO, 
                           SUM(QTTOTPRODUTO) AS QTTOTAL, 
                           CDPRATO, CDFILAUXPRAT, CDTIPOPRAT,
                           IDCONSPLAN AS IDPREVRETI, DECODE(CDCLIENTE, NULL, 'XXX', CDCLIENTE) AS CDCLIENTE, 
                           DECODE(CDDESTFILI, NULL, 'XXX', CDDESTFILI) AS CDDESTFILI, QTCOMENSAIS,
                           NVL(VERIFICA_SETORCOZ_COZ(CDFILIAL, CDTIPOPRAT, CDPRODUTO), '  ') AS SETOR
                      FROM PRODDISTPROD
                     WHERE CDFILIAL = V_CDFILIAL
                       AND CDSERVFILI = C_SERVTRANS.CDSERVFILI
                       AND DTCARDAPIO BETWEEN P_DTINICIAL AND P_DTFINAL
                       AND IDEFETIVADA = 'S'
                       AND INSTR(P_IDPRODUTOS, IDCONSPLAN) > 0
                     GROUP BY CDFILIAL, CDSERVFILI, DTCARDAPIO, CDPRODUTO, 
                           CDPRATO, CDFILAUXPRAT, CDTIPOPRAT, IDCONSPLAN,
                           CDCLIENTE, CDDESTFILI, QTCOMENSAIS
                     UNION ALL
                    SELECT C.CDFILIAL, C.CDSERVFILI, C.DTCARDAPIO AS DTPREVRETI, CC.CDPRODPRODUCAO AS CDPRODUTO,
                           SUM(CC.QTTOTPRATO) AS QTTOTAL,                            
                           CC.CDPRATO, CC.CDFILAUXPRAT, CC.CDTIPOPRAT,
                           'C' AS IDPREVRETI, DECODE(D.CDCLIENTE, NULL, 'XXX', D.CDCLIENTE) AS CDCLIENTE, 
                           DECODE(D.CDDESTFILI, NULL, 'XXX', D.CDDESTFILI) AS CDDESTFILI, D.QTCOMENSAIS,
                           NVL(VERIFICA_SETORCOZ_COZ(C.CDFILIAL, CC.CDTIPOPRAT, CC.CDPRODPRODUCAO), '  ') AS SETOR
                      FROM CARDADIA C
                     INNER JOIN DISTCARD D ON D.CDFILIAL = C.CDFILIAL AND D.NRCARDAPIO = C.NRCARDAPIO
                     INNER JOIN CARDDISTPROD P ON D.NRDISTCARD = P.NRDISTCARD AND C.NRCARDAPIO = P.NRCARDAPIO AND C.CDFILIAL = P.CDFILIAL AND C.CDSERVFILI = P.CDSERVFILI
                     INNER JOIN CNFDISTCARD CC ON CC.NRDISTCARD = P.NRDISTCARD AND CC.IDEFETIVADA = 'S'
                     WHERE C.CDFILIAL = V_CDFILIAL
                       AND C.CDSERVFILI = C_SERVTRANS.CDSERVFILI
                       AND C.DTCARDAPIO BETWEEN P_DTINICIAL AND P_DTFINAL
                       AND INSTR(P_IDPRODUTOS, 'C') > 0
                     GROUP BY C.CDFILIAL, C.CDSERVFILI, C.DTCARDAPIO, CC.CDPRODPRODUCAO, 
                           CC.CDPRATO, CC.CDFILAUXPRAT, CC.CDTIPOPRAT, D.CDCLIENTE, D.CDDESTFILI, D.QTCOMENSAIS
                     UNION ALL
                    SELECT CP.CDFILICPLA AS CDFILIAL, CP.CDSERVCPLA AS CDSERVFILI, CP.DTCONSPLAN AS DTPREVRETI,
                           CP.CDPRODCPLA AS CDPRODUTO, CP.QTCONSPLAN AS QTTOTAL,         
                           DECODE(CP.CDPRATCONSPL, 'PPPPPPPPPP', NVL(PARAM.CDPRACONSPER, 'PPPPPPPPPP'), DECODE(CP.CDPRATCONSPL, 'FFFFFFFFFF', NVL(PARAM.CDPRACONSFIX, 'FFFFFFFFFF'), CP.CDPRATCONSPL)) AS CDPRATO,
                           DECODE(CP.CDFAPRATCPLA, 'PP', NVL(PARAM.CDTPRCONSPER, 'PP'), DECODE(CP.CDFAPRATCPLA, 'FF', NVL(PARAM.CDTPRCONSFIX, 'FF'), CP.CDFAPRATCPLA)) AS CDFILAUXPRAT, 
                           CP.CDTPPRATCPLA AS CDTIPOPRAT,
                           CP.IDCONSPLAN AS IDPREVRETI, DECODE(CP.CDCLIECPLA, NULL, 'XXX', CP.CDCLIECPLA) AS CDCLIENTE, 
                           DECODE(CP.CDDESTFICPLA, NULL, 'XXX', CP.CDDESTFICPLA) AS CDDESTFILI, CP.QTCOMESTCPLA AS QTCOMENSAIS,
                           NVL(VERIFICA_SETORCOZ_COZ(CP.CDFILICPLA, CP.CDTPPRATCPLA, CP.CDPRODCPLA), '  ') AS SETOR
                      FROM CONSPLAN CP
                     INNER JOIN PARAMPLANEJA PARAM ON PARAM.CDFILIAL = V_CDFILIAL
                      LEFT JOIN (SELECT CC.CDPRATO, CC.CDFILAUXPRAT, C.DTCARDAPIO, C.CDFILIAL, C.CDSERVFILI
                                  FROM CARDADIA C
                                 INNER JOIN CARDDISTPROD P ON C.NRCARDAPIO = P.NRCARDAPIO AND C.CDFILIAL = P.CDFILIAL AND C.CDSERVFILI = P.CDSERVFILI
                                 INNER JOIN CNFDISTCARD CC ON CC.NRDISTCARD = P.NRDISTCARD AND CC.IDEFETIVADA = 'S'
                                 WHERE C.CDFILIAL = V_CDFILIAL
                                   AND C.CDSERVFILI = C_SERVTRANS.CDSERVFILI
                                   AND C.DTCARDAPIO BETWEEN P_DTINICIAL AND P_DTFINAL
                                   AND INSTR(P_IDPRODUTOS, 'C') > 0
                                   
                                   UNION  ALL
                                   
                                  SELECT --FOI NECESSARIO PARA TRATAR CASOS ONDE O COSNUMO FIXO E O PERCAPITA TEM CDPRATO IGUAIS.
                                         DECODE(C_SERVTRANS.IDUTRETIPRAT, 'S', CDPRATO, DECODE(IDCONSPLAN, 'P', DECODE(CDPRATO, NVL(PARAM.CDPRACONSPER, 'PPPPPPPPPP'), 'PPPPPPPPPP', CDPRATO), 
                                                            DECODE(IDCONSPLAN, 'F', DECODE(CDPRATO, NVL(PARAM.CDPRACONSFIX, 'FFFFFFFFFF'), 'FFFFFFFFFF', CDPRATO)))) AS CDPRATO,
                                         DECODE(C_SERVTRANS.IDUTRETIPRAT, 'S', CDFILAUXPRAT, DECODE(CDFILAUXPRAT, 'PP', NVL(PARAM.CDTPRCONSPER, 'PP'), DECODE(CDFILAUXPRAT, 'FF', NVL(PARAM.CDTPRCONSFIX, 'FF'), CDFILAUXPRAT))) AS CDFILAUXPRAT, 
                                         DTCARDAPIO, PRODDISTPROD.CDFILIAL, CDSERVFILI
                                    FROM PRODDISTPROD
                                   INNER JOIN PARAMPLANEJA PARAM ON PARAM.CDFILIAL = V_CDFILIAL
                                   WHERE PRODDISTPROD.CDFILIAL = V_CDFILIAL
                                     AND CDSERVFILI = C_SERVTRANS.CDSERVFILI
                                     AND DTCARDAPIO BETWEEN P_DTINICIAL AND P_DTFINAL
                                     AND INSTR(P_IDPRODUTOS, IDCONSPLAN) > 0
                                     AND NVL(IDEFETIVADA, 'N') = 'S') EFET ON EFET.CDFILIAL = CP.CDFILICPLA
                                                                          AND EFET.CDSERVFILI = CP.CDSERVCPLA
                                                                          AND EFET.DTCARDAPIO = CP.DTCONSPLAN
                                                                          AND EFET.CDPRATO = CP.CDPRATCONSPL
                                                                          AND EFET.CDFILAUXPRAT = CP.CDFAPRATCPLA
                     WHERE CP.NRCONSPLAN = V_NRCONSPLAN
                       AND CP.CDFILICPLA = V_CDFILIAL
                       AND CP.CDSERVCPLA = C_SERVTRANS.CDSERVFILI
                       AND INSTR(P_IDPRODUTOS, CP.IDCONSPLAN) > 0
                       AND EFET.CDPRATO IS NULL)
                LOOP                        
                    INSERT INTO PREVRETI (NRPRPREVRETI, CDFILIAL, CDSERVFILI, DTPREVRETI, 
                                          DTLIMITERETI, CDPRODUTO, QTPREVRETI, 
                                          CDPRATO, CDFILAUXPRAT, CDTIPOPRAT, 
                                          NRREQUEXTR, IDPREVRETI, HRLIMITERETI, CDOPERADOR, IDPREVTPPRAT)
                    VALUES (V_NRPRPREVRETI, C_INSERT_PREV.CDFILIAL, C_INSERT_PREV.CDSERVFILI, C_INSERT_PREV.DTPREVRETI, 
                            C_INSERT_PREV.DTPREVRETI, C_INSERT_PREV.CDPRODUTO, C_INSERT_PREV.QTTOTAL, 
                            DECODE(C_SERVTRANS.IDUTRETIPRAT, 'S', C_INSERT_PREV.CDPRATO, NULL),
                            DECODE(C_SERVTRANS.IDUTRETIPRAT, 'S', C_INSERT_PREV.CDFILAUXPRAT, NULL),  
                            DECODE(C_SERVTRANS.IDUTRETIPRAT, 'S', C_INSERT_PREV.CDTIPOPRAT, NULL),
                            '0000000000', C_INSERT_PREV.IDPREVRETI, C_SERVTRANS.HRINICATEN, P_CDOPERADOR, C_SERVTRANS.IDUTRETIPRAT);     
                    
                    INSERT INTO CONSPLAN (NRCONSPLAN, CDFILICPLA, CDSERVCPLA, DTCONSPLAN, 
                                          CDPRODCPLA, CDTPPRATCPLA, CDPRATCONSPL, CDFAPRATCPLA, 
                                          QTCONSPLAN, IDCONSPLAN, DTLIMITECPLA, HRLIMITECPLA, 
                                          CDCLIECPLA, CDDESTFICPLA, QTCOMESTCPLA, CDSETOCONSPL)
                    VALUES (P_NRCONSPLAN, C_INSERT_PREV.CDFILIAL, C_INSERT_PREV.CDSERVFILI, C_INSERT_PREV.DTPREVRETI, 
                            C_INSERT_PREV.CDPRODUTO, C_INSERT_PREV.CDTIPOPRAT, C_INSERT_PREV.CDPRATO, C_INSERT_PREV.CDFILAUXPRAT, 
                            C_INSERT_PREV.QTTOTAL, C_INSERT_PREV.IDPREVRETI, C_INSERT_PREV.DTPREVRETI, C_SERVTRANS.HRINICATEN,
                            C_INSERT_PREV.CDCLIENTE, C_INSERT_PREV.CDDESTFILI, C_INSERT_PREV.QTCOMENSAIS, C_INSERT_PREV.SETOR);
                            
                    V_NRPRPREVRETI := LPAD(V_NRPRPREVRETI + 1, 4, 0);
    
                END lOOP;
                            
               /* IF V_NRPRPREVRETI = '0000' THEN
                    V_CDSERVFILIAUX :=  V_CDSERVFILIAUX || C_SERVTRANS.CDSERVFILI || '_';
                END IF;*/
            END LOOP;
        END IF;
       /*FIM - Para servicos transportada e verificado se houve distribuicao efetivada dos itens*/                                       
        
        
	  IF (V_CDSERVFILIAUX IS NOT NULL) THEN
          CONSUMO_PLANEJADO_PLA(V_CDFILIAL, 
                                V_CDSERVFILIAUX, 
                                P_DTINICIAL, 
                                P_DTFINAL, 
                                P_IDARQUIVO, 
                                P_IDPRODUTOS, 
                                P_IDSEPPRATO, 
                                P_PEACRESCIMO, 
                                P_IDARREDOND, 
                                P_NRCONSPLAN, 
                                P_CDPRODUTOINI, 
                                V_CDPRODUTOFIN, 
                                P_CDOPERADOR, 
                                P_STDTINICIAL, 
                                P_STDTFINAL); 
      END IF;
      
   V_CDSERVFILIAUX := '';
   V_CDFILIAL := '';
   END LOOP; /* LOOP DOS SERVICOS */
   CLOSE C_FILIAIS;
END;
/