

IF OBJECT_ID('P_GERA_EST_PROD_CPL_NASC') IS NOT NULL
	BEGIN
		DROP PROCEDURE P_GERA_EST_PROD_CPL_NASC
	END
 GO 
  
CREATE PROCEDURE P_GERA_EST_PROD_CPL_NASC AS
---Produtos em estoque sem media P

	IF  OBJECT_ID('TEMPDB.DBO.#TMP_NASC') IS NOT NULL
	BEGIN
		 DROP TABLE #TMP_NASC
	END
	 
	SELECT 
		P.CD_EMP,
		P.CD_PROD,
		P.CD_FILIAL ,
		ISNULL(P.DT_ULT_ALT,P.DT_CAD) DT_NASC
		INTO #TMP_NASC
	FROM
		EST_PROD_CPL_MEDIAP P
		INNER JOIN EST_PROD_CPL E ON
		P.CD_EMP	 = E.CD_EMP AND
		P.CD_FILIAL  = E.CD_FILIAL AND
		P.CD_PROD	 = E.CD_PROD 
		LEFT JOIN EST_PROD_CPL_NASC N  ON
		P.CD_EMP	 = N.CD_EMP AND
		P.CD_FILIAL  = N.CD_FILIAL AND
		P.CD_PROD	 = N.CD_PROD  
	WHERE
		E.CD_EMP=1 
		AND E.QT_EST> 0
		AND E.MEDIAF >0
		AND N.CD_EMP IS NULL
 
 	INSERT INTO  EST_PROD_CPL_NASC (CD_EMP,CD_FILIAL,CD_PROD,DT_NASC,MEDIA_F_LIBERADA,QTDE_MES_VENDA_FECHADO)
	SELECT DISTINCT
		T.CD_EMP,
		T.CD_FILIAL,
		T.CD_PROD,
		T.DT_NASC DT_NASC,
		 0 MEDIA_F_LIBERADA,
		datediff(month,DT_NASC, getdate())   QTDE_MES_VENDA_FECHADO  
	FROM 
		#TMP_NASC T  
	WHERE NOT DT_NASC IS NULL


/*******************************************************************************************************************/
/*******************************************************************************************************************/
/*******************************************************************************************************************/

---Produtos em Nf entrada sem media P
	IF  OBJECT_ID('TEMPDB.DBO.#TMP_NASC2') IS NOT NULL
	BEGIN
		 DROP TABLE #TMP_NASC2
	END
	 
	SELECT 
		I.CD_EMP,
		I.CD_PROD,
		I.CD_FILIAL ,
		MIN(E.DT_ENT) DT_NASC
		INTO #TMP_NASC2
	FROM
		EST_NF_ENT E
		INNER JOIN EST_NF_ENT_IT I ON
		E.CD_EMP	 = I.CD_EMP AND
		E.CD_FILIAL  = I.CD_FILIAL AND
		E.CD_NF		 = I.CD_NF
		INNER JOIN GLB_MOV_OP O ON 
		E.CD_NT_OP    = O.CD_MOV_OP 
		INNER JOIN PRC_FILIAL F ON 
		E.CD_EMP = F.CD_EMP AND 
		E.CD_FILIAL = F.CD_FILIAL
		LEFT JOIN EST_PROD_CPL_NASC N  ON
		I.CD_EMP	 = N.CD_EMP AND
		I.CD_FILIAL  = N.CD_FILIAL AND
		I.CD_PROD		 = N.CD_PROD  
	WHERE
		E.CD_EMP=1
		AND E.DT_ENT > F.DT_INI_SISTEMA
		AND O.ATU_EST = 1
		AND N.CD_EMP IS NULL
	GROUP BY
		I.CD_EMP,
		I.CD_PROD,
		I.CD_FILIAL 
		 

	INSERT INTO  EST_PROD_CPL_NASC (CD_EMP,CD_FILIAL,CD_PROD,DT_NASC,MEDIA_F_LIBERADA,QTDE_MES_VENDA_FECHADO)
	SELECT DISTINCT
		T.CD_EMP,
		T.CD_FILIAL,
		T.CD_PROD,
		T.DT_NASC DT_NASC,
		 0 MEDIA_F_LIBERADA,
		datediff(month,DT_NASC, getdate())   QTDE_MES_VENDA_FECHADO  
	FROM 
		#TMP_NASC2 T  


/*******************************************************************************************************************/
/*******************************************************************************************************************/
/*******************************************************************************************************************/

		
	---Produtos venda sem media P
	IF  OBJECT_ID('TEMPDB.DBO.#TMP_NASC3') IS NOT NULL
	BEGIN
		 DROP TABLE #TMP_NASC3
	END
	  
	SELECT 
		P.CD_EMP,
		P.CD_PROD,
		P.CD_FILIAL ,
		P.MEDIAF,
		VD.DT_VD
		INTO #TMP_NASC3
	FROM 
		EST_PROD_CPL P
		LEFT JOIN EST_PROD_CPL_NASC N ON
		P.CD_EMP	= N.CD_EMP AND
		P.CD_PROD	= N.CD_PROD AND
		P.CD_FILIAL = N.CD_FILIAL 
	
		INNER JOIN (SELECT 
						I.CD_EMP,
						I.CD_FILIAL,
						I.CD_PROD,
						MAX(I.DT_VD) DT_VD
					FROM 
						V_PDV_VD_IT I
						INNER JOIN PRC_FILIAL F ON 
						I.CD_EMP = F.CD_EMP AND 
						I.CD_FILIAL = F.CD_FILIAL
					WHERE
						I.CD_EMP=1
						AND I.DT_VD BETWEEN F.DT_INI_SISTEMA AND GETDATE()  
					GROUP BY 
						I.CD_EMP,
						I.CD_FILIAL,
						I.CD_PROD ) VD ON
		P.CD_EMP	= VD.CD_EMP AND
		P.CD_PROD	= VD.CD_PROD AND
		P.CD_FILIAL = VD.CD_FILIAL 
	WHERE
		P.CD_EMP=1
		AND P.MEDIAF < 1 
		AND N.CD_EMP IS NULL
	 
	INSERT INTO  EST_PROD_CPL_NASC (CD_EMP,CD_FILIAL,CD_PROD,DT_NASC,MEDIA_F_LIBERADA,QTDE_MES_VENDA_FECHADO)
	SELECT DISTINCT
		T.CD_EMP,
		T.CD_FILIAL,
		T.CD_PROD,
		T.DT_VD DT_NASC,
		1 MEDIA_F_LIBERADA,
		DATEDIFF (MONTH, T.DT_VD, GETDATE())  QTDE_MES_VENDA_FECHADO  
	FROM 
		#TMP_NASC3 T  
		LEFT JOIN EST_PROD_CPL_NASC N ON 
		T.CD_EMP	= N.CD_EMP AND
		T.CD_PROD	= N.CD_PROD AND
		T.CD_FILIAL = N.CD_FILIAL 
	WHERE N.CD_EMP IS NULL 

/*******************************************************************************************************************/
/******************************************************************************************************************/