CREATE OR REPLACE PROCEDURE CALC_SERVCOMPLEMENTAR
(
   P_NRCARDAPIOBASE IN NUMBER,
   P_IDORIGEM IN VARCHAR2,
   P_NRATIVCLISEGMER IN NUMBER,
   P_CDREGIAO IN VARCHAR2,
   P_VRPEACRESCUSTO IN FLOAT,
   P_NRTIPOPUBLICO IN NUMBER,
   P_QTSEGSEX IN FLOAT,
   P_QTSABADO IN FLOAT,
   P_QTDOMFERI IN FLOAT,
   P_QTTURNOS IN FLOAT,   
   P_DTCUSTO IN DATE,
   P_NRCARDTRABCOMPLEMENTADO IN NUMBER,
   P_NRORG IN NUMBER,
   P_CDOPERADOR IN VARCHAR2,
   P_CDFILIALLOGADA IN VARCHAR2,
   P_NRCARDAPIOTRAB OUT NUMBER,
   P_LISTAERROS OUT VARCHAR2,
   P_IDIOMA IN VARCHAR2 DEFAULT 'PT_BR'
)
AS
   PRAGMA AUTONOMOUS_TRANSACTION;   
   V_CONFIGURACOES VARCHAR2(10);
   V_GRUPOS VARCHAR2(10);
BEGIN
   V_CONFIGURACOES := RETORNA_NOVO_CODIGO('LISTA', 1, 10);
   V_GRUPOS := RETORNA_NOVO_CODIGO('LISTA', 1, 10);
   
   -- Monta lista de itens da configuracao
   INSERT INTO LISTA
   (NRLISTA, CDLISTA,
   NRORG, NRORGULTATU, NRORGINCLUSAO,
   CDOPERINCLUSAO, CDOPERULTATU,
   DTINCLUSAO, DTULTATU)
   SELECT V_CONFIGURACOES, TO_CHAR(C.NRCONFIGCARDBASE),
        P_NRORG, P_NRORG, P_NRORG,
        P_CDOPERADOR, P_CDOPERADOR,
        SYSDATE, SYSDATE         
   FROM CONFIGCARDBASE C
   WHERE C.NRCARDAPIOBASE = P_NRCARDAPIOBASE
    AND C.IDCLASSIFICACAO = 'ALIMENTAR';

   -- Monta lista dos grupos
   INSERT INTO LISTA
   (NRLISTA, CDLISTA,
   NRORG, NRORGULTATU, NRORGINCLUSAO,
   CDOPERINCLUSAO, CDOPERULTATU,
   DTINCLUSAO, DTULTATU)
   SELECT DISTINCT V_GRUPOS, TO_CHAR(G.CDGRTIPOPR) || '|' || TO_CHAR(P.NRPORCIONAMENTO) || '|' || TO_CHAR(GR.NRGRAMATURACARDBASE),
        P_NRORG, P_NRORG, P_NRORG,
        P_CDOPERADOR, P_CDOPERADOR,
        SYSDATE, SYSDATE         
   FROM CONFIGCARDBASE C
        INNER JOIN TIPOPRAT T ON T.CDTIPOPRAT = C.CDTIPOPRAT
        INNER JOIN GRTIPOPR G ON G.CDGRTIPOPR = T.CDGRTIPOPR
        LEFT JOIN (SELECT A.CDGRTIPOPR, MAX(NRPORCIONAMENTO) AS NRPORCIONAMENTO
                     FROM ADESAOCARDBASE A
                          INNER JOIN ITEMADESAOCBASE I ON I.NRADESAOCARDBASE = A.NRADESAOCARDBASE
                    WHERE A.NRCARDAPIOBASE = P_NRCARDAPIOBASE
                    GROUP BY A.CDGRTIPOPR) P ON P.CDGRTIPOPR = G.CDGRTIPOPR
        LEFT JOIN (SELECT G.CDGRTIPOPR, MAX(G.NRGRAMATURACARDBASE) NRGRAMATURACARDBASE
                     FROM GRAMATURACARDBASE G
                    WHERE G.NRCARDAPIOBASE = P_NRCARDAPIOBASE
                      AND (G.NRTIPOPUBLICO = P_NRTIPOPUBLICO OR 
                             (G.NRTIPOPUBLICO IS NULL AND
                               NOT EXISTS (
                                             SELECT 1
                                               FROM GRAMATURACARDBASE
                                              WHERE NRCARDAPIOBASE = G.NRCARDAPIOBASE
                                                AND CDGRTIPOPR = G.CDGRTIPOPR                                                     
                                                AND VRGRAMATURA = G.VRGRAMATURA
                                                AND SGUNIDADE = G.SGUNIDADE
                                                AND NRTIPOPUBLICO = P_NRTIPOPUBLICO
                                          )
                             )
                          )
                    GROUP BY CDGRTIPOPR) GR ON GR.CDGRTIPOPR = G.CDGRTIPOPR      
   WHERE C.NRCARDAPIOBASE = P_NRCARDAPIOBASE
    AND C.IDCLASSIFICACAO = 'ALIMENTAR';

   IMPORTA_CARDBASE_NO_CARDTRAB(P_NRCARDAPIOBASE, P_IDORIGEM,
                                P_NRATIVCLISEGMER, P_CDREGIAO,
                                P_VRPEACRESCUSTO, P_NRTIPOPUBLICO,
                                P_QTSEGSEX, P_QTSABADO,
                                P_QTDOMFERI, P_QTTURNOS,
                                V_CONFIGURACOES, V_GRUPOS,
                                P_DTCUSTO, P_NRCARDTRABCOMPLEMENTADO,
                                P_NRORG, P_CDOPERADOR,
                                P_CDFILIALLOGADA, P_NRCARDAPIOTRAB,
                                P_LISTAERROS, P_IDIOMA);
   
   -- Apaga lista de itens da configuracao
   DELETE FROM LISTA WHERE NRLISTA = V_CONFIGURACOES;
   -- Apaga lista de grupos
   DELETE FROM LISTA WHERE NRLISTA = V_GRUPOS;

   IF P_LISTAERROS IS NOT NULL THEN
      ROLLBACK;
   ELSE
      COMMIT;
   END IF;
END;
/