create or replace PROCEDURE CALC_CUSTO_PRODUTO_ACABADO(
	P_CDFILCENT    IN FILIAL.CDFILIAL%TYPE,
    P_LISTAFILSERV IN LISTA.NRLISTA%TYPE,
    P_DTINICARD  IN DATE,
    P_DTFINCARD  IN DATE,
    P_DTVIGENCIA IN DATE,
    P_TIPOCUSTO  IN TIPOCUSTO.CDTIPOCUSTO%TYPE,
    P_CDOPERADOR IN OPERADOR.CDOPERADOR%TYPE,
    P_NRORG      IN NUMBER
) IS

	V_EXISTE NUMBER;
	V_NRCUSTOPACOZ NUMBER;
	
BEGIN

	V_EXISTE := 0;
    
	FOR C_DADOS IN (
		SELECT DISTINCT CDFILIAL, /*CDSERVFILI, S.DTCARDAPIO,*/ S.CDPRODACABADO, SUM(VRCUSTOUNITPROD) VRCUSTOUNITPROD,
			   CDPRATO, CDFILAUXPRAT
		 FROM (SELECT C.CDFILIAL, C.CDSERVFILI, C.DTCARDAPIO, R.CDPRODPRODUCAO AS CDPRODACABADO, 
					  ROUND(NVL(CUSTO_PRODUTO(P_CDFILCENT, R.CDPRODUTO, /*DECODE(PF.CDTIPCUSTEST, '', '03', PF.CDTIPCUSTEST)*/P_TIPOCUSTO, C.DTCARDAPIO, SYSDATE), 0),5) *
						ROUND(R.QTPRODUTO + NVL(R.QTCOMPLPROD,0),5) AS VRCUSTOUNITPROD,
					  R.CDPRATO, R.CDFILAUXPRAT
				 FROM (SELECT C.CDFILIAL, C.CDSERVFILI, C.DTCARDAPIO, P.CDTIPOPRAT, P.CDFILAUXPRAT, P.CDPRATO
						 FROM CARDADIA C 
					    INNER JOIN COMPCARD P ON C.CDFILIAL = P.CDFILIAL AND P.NRCARDAPIO = C.NRCARDAPIO 
						 					 AND C.DTCARDAPIO BETWEEN P_DTINICARD AND P_DTFINCARD
					    INNER JOIN (SELECT SUBSTR(CDLISTA,1,4) AS CDFILIAL, SUBSTR(CDLISTA,5,5) AS CDSERVFILI FROM LISTA
									 WHERE NRLISTA = P_LISTAFILSERV) L ON C.CDFILIAL = L.CDFILIAL AND C.CDSERVFILI = L.CDSERVFILI
					    WHERE NVL(C.QTCLIESTICAR,0) > 0                 
					    GROUP BY C.CDFILIAL,C.CDSERVFILI, C.DTCARDAPIO, P.CDTIPOPRAT, P.CDFILAUXPRAT, P.CDPRATO) C,
					  TABLE(RECEITA_UTIL(C.CDFILIAL, C.CDSERVFILI, C.CDPRATO, C.CDFILAUXPRAT, '0', 'Z', C.CDPRATO,0,0,'S')) R
			    WHERE (R.CDPRODPRODUCAO IS NOT NULL)
				  --AND R.IDTPREQPROD IN ('P', 'D')
				  AND R.IDTPREQPROD = 'P'
				  AND R.IDTPREQPRODPAI <> 'E'
				  AND (E_AGREGADA(P_CDFILCENT, C.CDFILIAL, C.CDSERVFILI, C.CDTIPOPRAT, C.DTCARDAPIO) > 0)
                  AND C.DTCARDAPIO BETWEEN P_DTINICARD AND P_DTFINCARD
			  ) S
	    GROUP BY CDFILIAL, CDSERVFILI, S.DTCARDAPIO, S.CDPRODACABADO, CDPRATO, CDFILAUXPRAT
	    ORDER BY CDFILIAL, S.CDPRODACABADO, CDPRATO, CDFILAUXPRAT/*, CDSERVFILI, S.DTCARDAPIO */
	) LOOP
		BEGIN
			SELECT COUNT(*)
			  INTO V_EXISTE
			  FROM CUSTOPACOZ
			 WHERE DTVIGENCIA = P_DTVIGENCIA         
			   AND CDCENTRAL = P_CDFILCENT
			   AND CDFILIAL = C_DADOS.CDFILIAL
			   AND CDPRODUTO = C_DADOS.CDPRODACABADO;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN BEGIN
				V_EXISTE := 0;
			END;
		END;
		
		IF V_EXISTE > 0 THEN
			UPDATE CUSTOPACOZ
			   SET VRUNIT = C_DADOS.VRCUSTOUNITPROD,
			       DTULTATU = SYSDATE,
				   CDOPERULTATU = P_CDOPERADOR
			 WHERE DTVIGENCIA = P_DTVIGENCIA         
			   AND CDCENTRAL = P_CDFILCENT
			   AND CDFILIAL = C_DADOS.CDFILIAL
			   AND CDPRODUTO = C_DADOS.CDPRODACABADO;
		ELSE
			NOVO_CODIGO('CUSTOPACOZ', V_NRCUSTOPACOZ);
			
			INSERT INTO CUSTOPACOZ 
				(NRCUSTOPACOZ, DTVIGENCIA, CDCENTRAL, CDFILIAL, CDPRODUTO, 
				 VRUNIT, CDOPERINCLUSAO)
		    VALUES (V_NRCUSTOPACOZ, P_DTVIGENCIA, P_CDFILCENT, C_DADOS.CDFILIAL, C_DADOS.CDPRODACABADO, 
				 C_DADOS.VRCUSTOUNITPROD, P_CDOPERADOR);
		END IF;
	END LOOP;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('CALC_CUSTO_PRODUTO_ACABADO','X.XX.XXX'); END;
/
