CREATE OR REPLACE PROCEDURE EXPORTA_CONSUMO
   (P_CDFILICENTGRAVA IN FILIAL.CDFILCENTPME%TYPE,
    P_IDTIPOCONSGRAVA IN VARCHAR2,
    P_DTCONSSERVGRAVA IN DATE,
    P_CDFILICENTORIG  IN FILIAL.CDFILCENTPME%TYPE,
    P_CDFILIORIG      IN FILIAL.CDFILIAL%TYPE,
    P_CDSERVORIG      IN SERVICO.CDSERVFILI%TYPE,
    P_IDQTDECONS      IN VARCHAR2,
    P_IDTIPOCONS      IN VARCHAR2,
    P_DTCONSSERV      IN DATE,
    P_NRLISTA         IN VARCHAR2,
    P_NRLISTAFILISERV IN VARCHAR2,
    P_SOBREPOR        IN VARCHAR2)
IS
   V_SOBREPOR VARCHAR2(1);
   V_DELETE NUMBER;
BEGIN
   V_SOBREPOR := P_SOBREPOR;

   IF V_SOBREPOR = 'S' THEN  
      /*SE A OPCAO E SOBREPOR, DELETA OS REGISTROS DA CONSSERVPME DAS FILIAIS E SERVICOS DA CENTRAL PARA O TIPO DE CONSUMO, DATA E DIA DA SEMANA*/
      DELETE 
        FROM CONSSERVPME C
       WHERE 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 C.CDFILCENTPME = P_CDFILICENTGRAVA
                       AND C.IDQTDECONPME = P_IDQTDECONS
                       AND C.IDTIPOCONPME = P_IDTIPOCONSGRAVA
                       AND C.DTCONSERVPME = P_DTCONSSERV)
         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.IDQTDECONPME = P_IDQTDECONS
                           AND CO.IDTIPOCONPME = P_IDTIPOCONS
                           AND CO.DTCONSERVPME = P_DTCONSSERV);
   END IF;
   
   BEGIN
      INSERT INTO CONSSERVPME
            (CDFILCENTPME, CDFILIAL, CDSERVFILI, IDQTDECONPME, IDTIPOCONPME, DTCONSERVPME, CDPRODUTO, QTCONSERVPME)
            (SELECT P_CDFILICENTGRAVA, LF.CDFILIAL, LF.CDSERVFILI, CS.IDQTDECONPME, P_IDTIPOCONSGRAVA, P_DTCONSSERVGRAVA, CS.CDPRODUTO, CS.QTCONSERVPME
               FROM CONSSERVPME CS INNER JOIN LISTA LP ON LP.NRLISTA = P_NRLISTA AND CS.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
              WHERE (CS.CDFILCENTPME = P_CDFILICENTORIG)
                AND (CS.CDFILIAL     = P_CDFILIORIG)
                AND (CS.CDSERVFILI   = P_CDSERVORIG)
                AND (CS.IDQTDECONPME = P_IDQTDECONS)
                AND (CS.IDTIPOCONPME = P_IDTIPOCONS)
                AND (CS.DTCONSERVPME = P_DTCONSSERV)
                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.DTCONSERVPME = P_DTCONSSERVGRAVA
                                  AND B.CDPRODUTO = LP.CDLISTA
                                  AND B.IDTIPOCONPME = P_IDTIPOCONSGRAVA));
   END;
END;
/