CREATE OR REPLACE PROCEDURE SERVCOMPLEMENTAR_SERVPROPCOM
(
	P_NRSERVICOPROPCOM IN NUMBER,
	P_CDOPERADOR IN VARCHAR2,
	P_NRORG IN NUMBER,
	P_CDFILIALLOGADA IN VARCHAR2,
	P_LISTACARD OUT VARCHAR2,
  P_PLANEJAMENTO IN VARCHAR2 DEFAULT 'N'
)
AS
   V_IDSERVICOPROPCOM VARCHAR2(30);
   V_NRCARDAPIOBASE NUMBER;
   V_NRCARDAPIOTRAB NUMBER;	
   V_NRCARDAPIOTRAB_COMPLEMENTAR NUMBER;
   V_LISTAERROS NUMBER;
   V_CDSEGMER VARCHAR2(5);
   V_CDATIVCLI VARCHAR2(10);
   V_CDREGIAO VARCHAR2(3);
   V_VRPEACRESCUSTO FLOAT;
   V_NRTIPOPUBLICO NUMBER;
   V_NRPADRAOQUALIDADE NUMBER;
   V_QTSEGSEX FLOAT;
   V_QTSABADO FLOAT;
   V_QTDOMFERI FLOAT;
   V_QTTURNOS FLOAT;
   V_RETORNO VARCHAR2(4000);
   V_NRATIVCLISEGMER NUMBER;
   V_DTCUSTO DATE;
BEGIN
--=========================================================
--=== Validacao inicial
--=========================================================
	SELECT MAX(SIT_SERVICOPROPCOM(S.NRSERVICOPROPCOM)),
	       MAX(C.NRCARDAPIOTRAB),
	       MAX(S.NRCARDAPIOBASE),
          MAX(P.CDSEGMER),
          MAX(P.CDATIVCLI),
          MAX(C.CDREGIAO),
          MAX(C.VRPEACRESCUSTO),
          MAX(S.NRTIPOPUBLICO),
          MAX(S.QTSEGSEX),
          MAX(S.QTSABADO),
          MAX(S.QTDOMFERI),
          MAX(S.QTTURNOS),
          MAX(S.NRPADRAOQUALIDADE),
          MAX(C.DTCUSTO)
	  INTO V_IDSERVICOPROPCOM,	  
	       V_NRCARDAPIOTRAB,
	       V_NRCARDAPIOBASE,
          V_CDSEGMER,
          V_CDATIVCLI,
          V_CDREGIAO,
          V_VRPEACRESCUSTO,
          V_NRTIPOPUBLICO,
          V_QTSEGSEX,
          V_QTSABADO,
          V_QTDOMFERI,
          V_QTTURNOS,
          V_NRPADRAOQUALIDADE,
          V_DTCUSTO
	  FROM SERVICOPROPCOM S
          INNER JOIN VERSAOPROPCOM V ON V.NRVERSAOPROPCOM = S.NRVERSAOPROPCOM
          INNER JOIN PROPOSTACOMERCIAL P ON P.NRPROPOSTACOMERCIAL = V.NRPROPOSTACOMERCIAL
	  	    INNER JOIN DETALHESERVPROPCOM D ON D.NRDETALHESERVPROPCOM = S.NRDETALHESERVPROPCOM
          LEFT JOIN CARDAPIOTRAB C ON C.NRCARDAPIOTRAB = D.NRCARDAPIOTRAB
	 WHERE S.NRSERVICOPROPCOM = P_NRSERVICOPROPCOM;

	IF (P_PLANEJAMENTO = 'N') AND (V_IDSERVICOPROPCOM = 'AGUARDANDOPLANEJAMENTO') THEN
		-- Operacao bloqueada. Servico esta aguardando equipe de planejamento.
		RAISE_APPLICATION_ERROR(-20000, MSG('TCOPROC_SERVPROP_AGUARDANDOPLANEJAMENTO'));
	ELSIF V_NRCARDAPIOTRAB IS NULL THEN
		-- Operacao bloqueada. Servico nao foi calculado.	
		RAISE_APPLICATION_ERROR(-20000, MSG('TCOPROC_SERVPROP_N_CALCULADO'));
	END IF;

	P_LISTACARD := RETORNA_NOVO_CODIGO('LISTA', 1, 10);	

   SELECT MAX(NRATIVCLISEGMER)
     INTO V_NRATIVCLISEGMER
     FROM ATIVCLISEGMER
    WHERE CDSEGMER = V_CDSEGMER
      AND CDATIVCLI = V_CDATIVCLI;

   -- Encontra os cardapios bases cadastrados como complementar ao cardapio base do servico
	FOR C_SERVCOMPLEMENTAR IN
	(
		SELECT CB.NRCARDAPIOBASE,
			     CB.NRSERVICOPROPOSTA
		  FROM CARDCOMPLCARDBASE C
		       INNER JOIN CARDAPIOBASE CB ON CB.NRCARDAPIOBASE = C.NRCARDAPIOBASE
		       INNER JOIN SERVICOPROPOSTA S ON S.NRSERVICOPROPOSTA = CB.NRSERVICOPROPOSTA
		 WHERE C.NRCARDAPIOBASECOMPL = V_NRCARDAPIOBASE
		   AND CB.NRPADRAOQUALIDADE = V_NRPADRAOQUALIDADE
		   AND CB.IDATIVO = 'S'
		   AND (TRUNC(SYSDATE) BETWEEN CB.DTINIVIGENCIA AND CB.DTFIMVIGENCIA)
		   AND EXISTS (
		                SELECT 1
		                 FROM SEGMERCARDBASE
		                WHERE NRCARDAPIOBASE = CB.NRCARDAPIOBASE
		                  AND CDSEGMER = V_CDSEGMER
		             )
	) LOOP
      /*Calcula o servico complementar de acordo com o cardapio ja gerado no servico informado como parametro*/
		CALC_SERVCOMPLEMENTAR(C_SERVCOMPLEMENTAR.NRCARDAPIOBASE, 'CALCULOGN',
                            V_NRATIVCLISEGMER, V_CDREGIAO,
                            V_VRPEACRESCUSTO, V_NRTIPOPUBLICO,
                            V_QTSEGSEX, V_QTSABADO,
                            V_QTDOMFERI, V_QTTURNOS,                            
                            V_DTCUSTO, V_NRCARDAPIOTRAB,
                            P_NRORG, P_CDOPERADOR,
                            P_CDFILIALLOGADA, V_NRCARDAPIOTRAB_COMPLEMENTAR,
                            V_LISTAERROS);
		
      IF V_LISTAERROS IS NULL THEN
         V_RETORNO := TO_CHAR(C_SERVCOMPLEMENTAR.NRCARDAPIOBASE) || '|' || 'S' || '|' || TO_CHAR(V_NRCARDAPIOTRAB_COMPLEMENTAR);
      ELSE
         V_RETORNO := TO_CHAR(C_SERVCOMPLEMENTAR.NRCARDAPIOBASE) || '|' || 'N' || '|' || TO_CHAR(V_LISTAERROS);
      END IF;

      INSERT INTO LISTA
      (NRLISTA, CDLISTA,
       NRORG, NRORGULTATU, NRORGINCLUSAO,
       CDOPERINCLUSAO, CDOPERULTATU,
       DTINCLUSAO, DTULTATU)
      VALUES
      (P_LISTACARD, V_RETORNO,
       P_NRORG, P_NRORG, P_NRORG,
       P_CDOPERADOR, P_CDOPERADOR,
       SYSDATE, SYSDATE);

	END LOOP;
END;
/