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  
  
 SELECT COUNT(*)
     INTO V_DELETE
     FROM CONSSERVPME, LISTA
      WHERE (CDFILCENTPME = P_CDFILICENTGRAVA)
      AND (IDQTDECONPME = P_IDQTDECONS)
      AND (IDTIPOCONPME = P_IDTIPOCONSGRAVA)
      AND (DTCONSERVPME = P_DTCONSSERVGRAVA)
      AND NRLISTA = P_NRLISTAFILISERV
      AND (CDFILIAL     = SUBSTR(LISTA.CDLISTA, 1, 4))
      AND (CDSERVFILI   = SUBSTR(LISTA.CDLISTA, 5, 10)); 

   IF V_DELETE <> 0 THEN

   DELETE FROM CONSSERVPME
    WHERE (CDFILCENTPME = P_CDFILICENTGRAVA)
      AND (IDQTDECONPME = P_IDQTDECONS)
      AND (IDTIPOCONPME = P_IDTIPOCONSGRAVA)
      AND (DTCONSERVPME = P_DTCONSSERVGRAVA)
      AND (CDFILIAL IN (SELECT SUBSTR(L.CDLISTA, 1, 4)
                        FROM LISTA L, CONSSERVPME C
                        WHERE (L.NRLISTA = P_NRLISTAFILISERV)
                        AND (C.CDFILIAL     = SUBSTR(L.CDLISTA, 1, 4))))
      AND (CDSERVFILI IN (SELECT SUBSTR(L.CDLISTA, 5, 10)
                        FROM LISTA L, CONSSERVPME C
                        WHERE (L.NRLISTA = P_NRLISTAFILISERV)        
                        AND (C.CDSERVFILI   = SUBSTR(L.CDLISTA, 5, 10))))
      AND (CDFILCENTPME||CDFILIAL||CDSERVFILI||IDTIPOCONPME NOT IN (SELECT DISTINCT CDFILCENTPME||LISTA.CDLISTA||IDTIPOCONPME
                                                                        FROM CONSSERVPME, LISTA
                                                                       WHERE CDFILCENTPME = P_CDFILICENTGRAVA   
                                                                         AND P_CDFILIORIG = SUBSTR(LISTA.CDLISTA, 1, 4)
                                                                         AND P_CDSERVORIG = SUBSTR(LISTA.CDLISTA, 5, 10)
                                                                         AND IDQTDECONPME = P_IDQTDECONS        
                                                                         AND IDTIPOCONPME = P_IDTIPOCONS
                                                                         AND LISTA.NRLISTA = P_NRLISTAFILISERV));  
      END IF;
      END IF;
      BEGIN
      INSERT INTO CONSSERVPME
            (CDFILCENTPME, CDFILIAL, CDSERVFILI, IDQTDECONPME,
             IDTIPOCONPME, DTCONSERVPME, CDPRODUTO, QTCONSERVPME)
            (SELECT P_CDFILICENTGRAVA, SUBSTR(LS.CDLISTA, 1, 4) AS CDFILIAL, SUBSTR(LS.CDLISTA, 5, 10) AS CDSERVFILI,
                    CS.IDQTDECONPME, P_IDTIPOCONSGRAVA, P_DTCONSSERVGRAVA, CS.CDPRODUTO,
                    CS.QTCONSERVPME
               FROM CONSSERVPME CS, LISTA LP, LISTA LS /*lista com cdfilial concatenado com cdservfili*/
              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 (LP.CDLISTA      = CS.CDPRODUTO)
                AND (LP.NRLISTA      = P_NRLISTA)
                AND (LS.NRLISTA      = P_NRLISTAFILISERV)
                AND (CS.CDPRODUTO NOT IN (SELECT CDPRODUTO
                                            FROM CONSSERVPME
                                           WHERE CDFILCENTPME = P_CDFILICENTGRAVA
                                             AND CDFILIAL     = SUBSTR(LS.CDLISTA, 1, 4)
                                             AND CDSERVFILI   = SUBSTR(LS.CDLISTA, 5, 10)
                                             AND IDQTDECONPME = P_IDQTDECONS
                                             AND IDTIPOCONPME = P_IDTIPOCONSGRAVA
                                             AND DTCONSERVPME = P_DTCONSSERVGRAVA)));
   END;
END;
/