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 CLOB;
   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);

   /* - Declarao 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');

	  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;
        
       /*INICIO - Para servicos transportada e verificado se houve distribuicao efetivada dos itens*/   

        /*gera consplan para buscar pratos que nao sao representados por um produto de producao*/ 
        CONSPLAN_GERAL(V_NRLISTA, P_DTINICIAL, P_DTFINAL, 'CXX', '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,
                       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,
                       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)
            LOOP                        
                INSERT INTO PREVRETI (NRPRPREVRETI, CDFILIAL, CDSERVFILI, DTPREVRETI, 
                                      DTLIMITERETI, CDPRODUTO, QTPREVRETI, 
                                      CDPRATO, CDFILAUXPRAT, CDTIPOPRAT, 
                                      NRREQUEXTR, IDPREVRETI, HRLIMITERETI, CDOPERADOR)
                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, 
                        C_INSERT_PREV.CDPRATO, C_INSERT_PREV.CDFILAUXPRAT, C_INSERT_PREV.CDTIPOPRAT,
                        '0000000000', C_INSERT_PREV.IDPREVRETI, C_SERVTRANS.HRINICATEN, P_CDOPERADOR);     
                
                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;

        END LOOP;
       /*FIM - Para servicos transportada e verificado se houve distribuicao efetivada dos itens*/                                       
   
   V_CDSERVFILIAUX := '';
   V_CDFILIAL := '';
   END LOOP; /* LOOP DOS SERVIOS */
   CLOSE C_FILIAIS;
END;