/* Sistemas Integrados TEKNISA

Nome: 7822_PLASTPO - EXPORTA_EXC_CONSUMO.SQL

Descricao:
Procedimento para exportacao de excecao no consumo das filial agregadas do Sistema PME

Criacao:
30/04/2007 - Felipe Amaral Gurgel
*/
CREATE OR REPLACE PROCEDURE EXPORTA_EXC_CONSUMO(
   P_NRLISTAFILISERV    IN VARCHAR2,
   P_DTCONSSERVINIGRAVA IN DATE,
   P_DTCONSSERVFINGRAVA IN DATE,
   P_CDFILIORIG         IN VARCHAR2,
   P_CDSERVORIG         IN VARCHAR2,
   P_IDQTDECONS         IN VARCHAR2,
   P_IDTIPOCONS         IN VARCHAR2,
   P_DTCONSSERVORIG     IN DATE,
   P_CDPRODUTO          IN VARCHAR2,
   P_IDSOBREPOR         IN VARCHAR2,
   P_CDFILICENTGRAVA    IN VARCHAR2,
   P_CDFILICENTORIG     IN VARCHAR2,
   P_CDFILCENTPME       IN VARCHAR2,
   P_NRLISTAPROD        IN VARCHAR2)    
IS
   V_DTGRAVA     DATE; 
BEGIN
   V_DTGRAVA := P_DTCONSSERVINIGRAVA;

   /*SE A OPCAO ESCOLHIDA FOI SOBREPOR ENTAO EXCLUI OS DIAS E PRODUTOS CONSUMIDOS NA FILIAL/SERVICO DESTINO PARA O PERIODO, DIAS DA SEMANA E TIPO DE CONSUMO SELECIONADOS PARA EXCESSÃO*/
   IF P_IDSOBREPOR = 'S' THEN   
      DELETE 
      FROM CONSSERVPME C
      WHERE (C.CDFILCENTPME = P_CDFILICENTGRAVA)
        AND (C.IDQTDECONPME = P_IDQTDECONS)
        AND (C.IDTIPOCONPME = '9')
        AND (C.DTCONSERVPME BETWEEN P_DTCONSSERVINIGRAVA AND P_DTCONSSERVFINGRAVA)
        AND (INSTR(P_IDTIPOCONS, TO_CHAR(C.DTCONSERVPME,'D')) <> 0)
        AND EXISTS(SELECT L.NRLISTA
                     FROM LISTA L 
                    WHERE L.NRLISTA = P_NRLISTAFILISERV
                      AND C.CDFILIAL = SUBSTR(L.CDLISTA, 1, 4)
                      AND C.CDSERVFILI = SUBSTR(L.CDLISTA, 5, 5))
        AND NOT EXISTS(SELECT CO.CDFILCENTPME
                          FROM CONSSERVPME CO
                         WHERE CO.CDFILIAL     = C.CDFILIAL
                           AND CO.CDSERVFILI   = C.CDSERVFILI
                           AND CO.CDFILCENTPME = C.CDFILCENTPME
                           AND CO.IDQTDECONPME = C.IDQTDECONPME
                           AND CO.IDTIPOCONPME = C.IDTIPOCONPME
                           AND CO.DTCONSERVPME = C.DTCONSERVPME
                           AND CO.CDFILIAL     = P_CDFILIORIG
                           AND CO.CDSERVFILI   = P_CDSERVORIG
                           AND CO.CDFILCENTPME = P_CDFILICENTORIG
                           AND CO.DTCONSERVPME = P_DTCONSSERVORIG);
   END IF;
   
   /*RETIRA OS REGISTROS DE DIA SEM CONSUMO DA TABELA PARA O TIPO DE CONSUMO SELECIONADO NA EXPORTACAO*/
   DELETE 
     FROM DIASEMCONSPME D
    WHERE (D.CDFILCENTPME = P_CDFILICENTGRAVA)
      AND (D.DTSEMCONSPME BETWEEN P_DTCONSSERVINIGRAVA AND P_DTCONSSERVFINGRAVA)
      AND (D.IDTPCONSPME = P_IDQTDECONS)
      AND (INSTR(P_IDTIPOCONS, TO_CHAR(D.DTSEMCONSPME,'D')) <> 0)
      AND EXISTS(SELECT L.NRLISTA
                   FROM LISTA L 
                  WHERE L.NRLISTA = P_NRLISTAFILISERV
                    AND D.CDFILIAL = SUBSTR(L.CDLISTA, 1, 4)
                    AND D.CDSERVFILI = SUBSTR(L.CDLISTA, 5, 5))
      AND NOT EXISTS (SELECT DD.CDFILCENTPME
                        FROM DIASEMCONSPME DD
                       WHERE DD.CDFILCENTPME = D.CDFILCENTPME
                         AND DD.CDFILIAL = D.CDFILIAL
                         AND DD.CDSERVFILI = D.CDSERVFILI
                         AND D.CDFILCENTPME = P_CDFILICENTORIG
                         AND DD.DTSEMCONSPME = P_DTCONSSERVORIG
                         AND DD.CDFILIAL = P_CDFILIORIG
                         AND DD.CDSERVFILI = P_CDSERVORIG);

   /*CASO EXISTA REGISTRO COMO DIA SEM CONSUMO DO TIPO 'A' = AMBOS, FAZ UPDATE PARA DESCONSIDERAR O TIPO DE CONSUMO IGUAL AO QUE ESTOU EXPORTANDO E RECEBA APENAS O OUTRO TIPO POSSIVEL, 'P' OU 'F'*/
   UPDATE DIASEMCONSPME D      
      SET D.IDTPCONSPME = CASE WHEN P_IDQTDECONS = 'P' THEN 'F' ELSE 'P' END    
    WHERE (D.CDFILCENTPME = P_CDFILICENTGRAVA)
      AND (INSTR(P_IDTIPOCONS, TO_CHAR(D.DTSEMCONSPME,'D')) <> 0)
      AND (D.DTSEMCONSPME BETWEEN P_DTCONSSERVINIGRAVA AND P_DTCONSSERVFINGRAVA)
      AND (D.IDTPCONSPME = 'A')
      AND EXISTS(SELECT L.NRLISTA
                   FROM LISTA L 
                  WHERE L.NRLISTA = P_NRLISTAFILISERV
                    AND D.CDFILIAL = SUBSTR(L.CDLISTA, 1, 4)
                    AND D.CDSERVFILI = SUBSTR(L.CDLISTA, 5, 5))
      AND NOT EXISTS (SELECT DD.CDFILCENTPME
                        FROM DIASEMCONSPME DD
                       WHERE DD.CDFILCENTPME = D.CDFILCENTPME
                         AND DD.CDFILIAL = D.CDFILIAL
                         AND DD.CDSERVFILI = D.CDSERVFILI
                         AND D.CDFILCENTPME = P_CDFILICENTORIG
                         AND DD.DTSEMCONSPME = P_DTCONSSERVORIG
                         AND DD.CDFILIAL = P_CDFILIORIG
                         AND DD.CDSERVFILI = P_CDSERVORIG);

   /*INSERE OS PRODUTOS NOS DIAS DE CONSUMO PARA AS FILIAIS E SERVICOS DESTINO*/
   INSERT INTO CONSSERVPME
            (CDFILCENTPME, CDFILIAL, CDSERVFILI, IDQTDECONPME, IDTIPOCONPME, DTCONSERVPME, CDPRODUTO, QTCONSERVPME)
            (SELECT P_CDFILICENTGRAVA, LF.CDFILIAL, LF.CDSERVFILI, A.IDQTDECONPME, A.IDTIPOCONPME, DT.DTDATA, A.CDPRODUTO, A.QTCONSERVPME
               FROM CONSSERVPME A INNER JOIN LISTA LP ON LP.NRLISTA = P_NRLISTAPROD AND A.CDPRODUTO = LP.CDLISTA
                                  CROSS JOIN (SELECT SUBSTR(L.CDLISTA, 1, 4) AS CDFILIAL, SUBSTR(L.CDLISTA, 5, 5) CDSERVFILI
                                                FROM LISTA L
                                               WHERE L.NRLISTA = P_NRLISTAFILISERV) LF
                                  CROSS JOIN (SELECT DT.DTDATA
                                               FROM DATA DT
                                              WHERE (DT.DTDATA BETWEEN P_DTCONSSERVINIGRAVA AND P_DTCONSSERVFINGRAVA)
                                                AND (INSTR(P_IDTIPOCONS, TO_CHAR(DT.DTDATA,'D')) <> 0)) DT            
              WHERE (A.CDFILCENTPME = P_CDFILICENTORIG)  
                AND (A.CDFILIAL     = P_CDFILIORIG)     
                AND (A.CDSERVFILI   = P_CDSERVORIG)     
                AND (A.IDQTDECONPME = P_IDQTDECONS)     
                AND (A.IDTIPOCONPME = '9')
                AND (A.DTCONSERVPME = P_DTCONSSERVORIG)
                AND (NOT EXISTS (SELECT B.CDPRODUTO
                                   FROM CONSSERVPME B
                                  WHERE B.CDFILCENTPME = P_CDFILICENTGRAVA
                                    AND B.CDFILIAL = LF.CDFILIAL
                                    AND B.CDSERVFILI = LF.CDSERVFILI
                                    AND B.IDQTDECONPME = P_IDQTDECONS
                                    AND B.IDTIPOCONPME = '9'
                                    AND B.DTCONSERVPME = DT.DTDATA
                                    AND B.CDPRODUTO = LP.CDLISTA)));
END;
/