CREATE OR REPLACE
PROCEDURE REQUISICAO_POR_SERVICO_PLA
  (P_CDFILIAL     IN VARCHAR2,
   P_SERVICOS     IN CLOB,
   P_DTINICIAL    IN DATE,
   P_DTFINAL      IN DATE,
   P_PEACRESCIMO  IN NUMBER,
   P_CDOPERADOR   IN VARCHAR2,
   P_DTHRCALCPR   IN DATE,
   P_NRCONSPLAN   IN VARCHAR2,
   P_STDTINICIAL  IN VARCHAR2 DEFAULT NULL,
   P_STDTFINAL    IN VARCHAR2 DEFAULT NULL,
   P_IDARREDOND   IN VARCHAR2,
   P_IDPRODUTOS   IN VARCHAR2) IS

   V_CDSERVFILI   VARCHAR2(5);

   V_TODOS_SERV   VARCHAR2(1);
   
   V_IDSERVTRAN   VARCHAR2(1);
   V_LISTA_SERV   VARCHAR2(4000);
   V_LISTA_SERV_TRANS VARCHAR2(4000);
   V_NRPRPREVRETI VARCHAR2(4);
   V_NRCONSPLAN VARCHAR2(20);
   V_NRLISTA VARCHAR2(20);

   /* - Declarao de cursores ---------------------------------------------- */

   CURSOR C_SERVICOS IS
      SELECT
         CDSERVFILI
      FROM
         SERVICO
      WHERE
         (CDFILIAL = P_CDFILIAL) AND
         ((INSTR(P_SERVICOS, CDSERVFILI) > 0) OR (V_TODOS_SERV = 'T'));

BEGIN

    V_LISTA_SERV := '';
    V_LISTA_SERV_TRANS := '';
    
    NOVO_CODIGO('LISTA'||P_CDFILIAL, V_NRLISTA);
    V_NRLISTA := SUBSTR(V_NRLISTA, 11, 10);
   
   /*Comando criado por que campos do tipo CLOB no podem ser comparados a valor em consultas SQL*/
   IF P_SERVICOS = 'T' THEN
      V_TODOS_SERV := 'T';
   END IF;
   
   /* REGISTRA O CALCULO NA TABELA DE LOG */

   OPEN C_SERVICOS;
   LOOP
      FETCH C_SERVICOS INTO V_CDSERVFILI;
      EXIT WHEN C_SERVICOS%NOTFOUND;

      INSERT INTO LOGPREVRETI
        (CDFILIAL,
         CDOPERADOR,
         DTHRCALCPR,
         NRCONSPLANPR,
         CDSERVFILI,
         DTINICIALPR,
         DTFINALPR,
         CDPRODINICPR,
         CDPRODFINAPR,
         PEACRESCIMPR)
      VALUES
        (P_CDFILIAL,
         P_CDOPERADOR,
         P_DTHRCALCPR,
         P_NRCONSPLAN,
         V_CDSERVFILI,
         P_DTINICIAL,
         P_DTFINAL,
         ' ',
         ' ',
         P_PEACRESCIMO);
        
        /*Separa servicos que sao transportada*/
        IF (V_IDSERVTRAN <> 'S') THEN 
            V_LISTA_SERV :=  V_LISTA_SERV || V_CDSERVFILI || '_';
        ELSE         
            V_LISTA_SERV_TRANS := V_LISTA_SERV_TRANS || V_CDSERVFILI || '_';
            INSERT INTO LISTA (NRLISTA, CDLISTA) VALUES (V_NRLISTA, P_CDFILIAL||V_CDSERVFILI);
            
             DELETE FROM PREVRETI
              WHERE CDFILIAL   = P_CDFILIAL            
                AND CDSERVFILI = V_CDSERVFILI
                AND DTPREVRETI BETWEEN P_DTINICIAL AND P_DTFINAL
                AND NRREQUEXTR = '0000000000'
                AND INSTR(P_IDPRODUTOS, IDPREVRETI) > 0;
        END IF;

   END LOOP; /* LOOP DOS SERVIOS */
   CLOSE C_SERVICOS;
   
   IF (V_LISTA_SERV <> '') THEN      
       CONSUMO_PLANEJADO_PLA(P_CDFILIAL,
                             V_LISTA_SERV,
                             P_DTINICIAL,
                             P_DTFINAL,
                             'A',
                             P_IDPRODUTOS,
                             'P',
                             P_PEACRESCIMO,
                             P_IDARREDOND,
                             P_NRCONSPLAN,
                             ' ',
                             'zzzzzzzzzz',
                             P_CDOPERADOR,
                             NULL,
                             NULL);
    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 = P_CDFILIAL) AND
         ((INSTR(V_LISTA_SERV_TRANS, CDSERVFILI) > 0)))
    LOOP   
        V_NRPRPREVRETI := '0000';
      
        FOR C_INSERT_PREV IN 
            (SELECT CDFILIAL, CDSERVFILI, DTCARDAPIO AS DTPREVRETI, DTCARDAPIO AS DTLIMITERETI, CDPRODUTO, 
                   SUM(QTTOTPRODUTO) AS QTPREVRETI, 
                   DECODE(C_SERVTRANS.IDUTRETIPRAT, 'S', CDPRATO, NULL) CDPRATO, 
                   DECODE(C_SERVTRANS.IDUTRETIPRAT, 'S', CDFILAUXPRAT, NULL) CDFILAUXPRAT,
                   DECODE(C_SERVTRANS.IDUTRETIPRAT, 'S', CDTIPOPRAT, NULL) CDTIPOPRAT, 
                   IDCONSPLAN AS IDPREVRETI, C_SERVTRANS.HRINICATEN AS HRLIMITERETI
              FROM PRODDISTPROD
             WHERE CDFILIAL = P_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, DTCARDAPIO, CDPRODUTO, 
                   CDPRATO, CDFILAUXPRAT, CDTIPOPRAT, IDCONSPLAN
             UNION ALL
            SELECT C.CDFILIAL, C.CDSERVFILI, C.DTCARDAPIO AS DTPREVRETI, C.DTCARDAPIO AS DTLIMITERETI, CC.CDPRODPRODUCAO AS CDPRODUTO, 
                   SUM(CC.QTTOTPRATO) AS QTPREVRETI,                    
                   DECODE(C_SERVTRANS.IDUTRETIPRAT, 'S', CC.CDPRATO, NULL) CDPRATO, 
                   DECODE(C_SERVTRANS.IDUTRETIPRAT, 'S', CC.CDFILAUXPRAT, NULL) CDFILAUXPRAT,
                   DECODE(C_SERVTRANS.IDUTRETIPRAT, 'S', CC.CDTIPOPRAT, NULL) CDTIPOPRAT,
                   'C' AS IDPREVRETI, C_SERVTRANS.HRINICATEN AS HRLIMITERETI
              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 = P_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, C.DTCARDAPIO, CC.CDPRODPRODUCAO, 
                   CC.CDPRATO, CC.CDFILAUXPRAT, CC.CDTIPOPRAT
             UNION ALL
            SELECT CP.CDFILICPLA AS CDFILIAL, CP.CDSERVCPLA AS CDSERVFILI, CP.DTCONSPLAN AS DTPREVRETI, CP.DTLIMITECPLA AS DTLIMITERETI, 
                   CP.CDPRODCPLA AS CDPRODUTO, CP.QTCONSPLAN AS QTPREVRETI,                    
                   DECODE(C_SERVTRANS.IDUTRETIPRAT, 'S', CP.CDPRATCONSPL, NULL) CDPRATO, 
                   DECODE(C_SERVTRANS.IDUTRETIPRAT, 'S', CP.CDFAPRATCPLA, NULL) CDFILAUXPRAT,
                   DECODE(C_SERVTRANS.IDUTRETIPRAT, 'S', CP.CDTPPRATCPLA, NULL) CDTIPOPRAT,
                   'C' AS IDPREVRETI, C_SERVTRANS.HRINICATEN AS HRLIMITERETI
              FROM CONSPLAN CP
              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 = P_CDFILIAL
                           AND C.CDSERVFILI = C_SERVTRANS.CDSERVFILI
                           AND C.DTCARDAPIO BETWEEN P_DTINICIAL AND P_DTFINAL
                           AND INSTR(P_IDPRODUTOS, 'C') > 0) 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 = P_CDFILIAL
               AND CP.IDCONSPLAN = 'C'
               AND EFET.CDPRATO IS NULL)
        LOOP                
            INSERT INTO PREVRETI (NRPRPREVRETI, CDFILIAL, CDSERVFILI, DTPREVRETI, 
                                  DTLIMITERETI, CDPRODUTO, QTPREVRETI, QTPREVRETISA,
                                  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.DTLIMITERETI, C_INSERT_PREV.CDPRODUTO, C_INSERT_PREV.QTPREVRETI, C_INSERT_PREV.QTPREVRETI, 
                        C_INSERT_PREV.CDPRATO, C_INSERT_PREV.CDFILAUXPRAT, C_INSERT_PREV.CDTIPOPRAT, '0000000000', 
                        C_INSERT_PREV.IDPREVRETI, C_INSERT_PREV.HRLIMITERETI, P_CDOPERADOR);     
            
            V_NRPRPREVRETI := LPAD(V_NRPRPREVRETI + 1, 4, 0);
            
        END lOOP;
        
    END LOOP;
    
    DELETE FROM LISTA WHERE NRLISTA = V_NRLISTA;
    DELETE FROM CONSPLAN WHERE NRCONSPLAN = V_NRCONSPLAN;
    /*FIM - Para servicos transportada e verificado se houve distribuicao efetivada dos itens*/    
END;