
/****** Object:  StoredProcedure [dbo].[P_PDV_CAD_PROD_PIS_COFINS]    Script Date: 11/26/2013 17:01:53 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_REPLICA_EST_PROD_CPL_MEDIAF]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_REPLICA_EST_PROD_CPL_MEDIAF]
GO
 
/****** Object:  StoredProcedure [dbo].[P_PDV_CAD_PROD_PIS_COFINS]    Script Date: 11/26/2013 17:01:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE P_REPLICA_EST_PROD_CPL_MEDIAF (@CD_EMP INT=1)
AS

/***************************************************************************************************************/
-- Funcao	: Replica o cadastro de produtos para as tabelas de mediaf e mediap
-- Autor	: Ramam Freitas
-- Data		: 26/11/2013
-- Obs.		: Programar esta rotina para agendamento no job de 3 em 3 horas
/***************************************************************************************************************/

CREATE TABLE #TMP_PROD (CD_EMP		INT,
						CD_FILIAL	INT,
						CD_PROD		INT)


INSERT INTO #TMP_PROD
SELECT 
	c.CD_EMP,
	c.CD_FILIAL,
	c.CD_PROD
FROM 
	EST_PROD_CPL C
	LEFT JOIN EST_PROD_CPL_MEDIAF F ON 
	C.CD_EMP	= F.CD_EMP AND
	C.CD_FILIAL = F.CD_FILIAL AND
	C.CD_PROD	= F.CD_PROD
WHERE
	C.CD_EMP=@CD_EMP
	AND F.CD_EMP IS NULL

/***************************************************************************************************************/
--	INSERE OS PRODUTOS NA TABELA DE MEDIAF
/***************************************************************************************************************/
INSERT INTO EST_PROD_CPL_MEDIAF(CD_EMP,CD_FILIAL,CD_PROD,CD_SIGLA,QTDE_MEDIAF,
								QTDE_FINANCIADO,QTDE_FACEAMENTO,QTDE_LIMITADOR,VALID_QTDE_FACEAMENTO,
								QT_VD_ATUAL,QT_VD_MES1,QT_VD_MES2,QT_VD_MES3,LIMITP,LIMITV,
								CD_USU,DT_CAD,DT_ULT_ALT,SAZ,DT_NASC,MEDIAF_TRANSF)
SELECT 
	c.CD_EMP,
	c.CD_FILIAL,
	c.CD_PROD,
	4 CD_SIGLA,
	0 QTDE_MEDIAF,
	0 QTDE_FINANCIADO,
	0 QTDE_FACEAMENTO,
	0 QTDE_LIMITADOR,
	NULL VALID_QTDE_FACEAMENTO,
	0 QT_VD_ATUAL,
	0 QT_VD_MES1,
	0 QT_VD_MES2,
	0 QT_VD_MES3,
	0 LIMITP,
	0 LIMITV,
	c.CD_USU,
	GETDATE() DT_CAD,
	null DT_ULT_ALT,
	0 SAZ,
	null  DT_NASC,
	0 MEDIAF_TRANSF
FROM 
	EST_PROD_CPL C
	INNER JOIN #TMP_PROD P ON 
	C.CD_EMP	= P.CD_EMP AND
	C.CD_FILIAL = P.CD_FILIAL AND
	C.CD_PROD	= P.CD_PROD
WHERE
	C.CD_EMP=@CD_EMP
	
	
/***************************************************************************************************************/
--	INSERE OS PRODUTOS NA TABELA DE MEDIAP
/***************************************************************************************************************/



TRUNCATE TABLE #TMP_PROD
	
INSERT INTO #TMP_PROD
SELECT 
	c.CD_EMP,	
	c.CD_FILIAL,	
	c.CD_PROD
FROM 
	EST_PROD_CPL C
	LEFT JOIN EST_PROD_CPL_MEDIAP P ON 
	C.CD_EMP	= P.CD_EMP AND
	C.CD_FILIAL = P.CD_FILIAL AND
	C.CD_PROD	= P.CD_PROD
WHERE
	C.CD_EMP=@CD_EMP
	AND P.CD_EMP IS NULL

INSERT INTO  EST_PROD_CPL_MEDIAP (CD_EMP,CD_FILIAL,CD_PROD,QTDE_MEDIAP,
									CD_USU,DT_CAD,DT_ULT_ALT,FALTA_CRONICA)
SELECT 
	c.CD_EMP,	
	c.CD_FILIAL,	
	c.CD_PROD,	
	0 QTDE_MEDIAP,	
	c.CD_USU,
	GETDATE() DT_CAD,	
	null DT_ULT_ALT,	
	0 FALTA_CRONICA 
FROM 
	EST_PROD_CPL C
	INNER JOIN #TMP_PROD P ON 
	C.CD_EMP	= P.CD_EMP AND
	C.CD_FILIAL = P.CD_FILIAL AND
	C.CD_PROD	= P.CD_PROD
WHERE
	C.CD_EMP=@CD_EMP 


DROP TABLE #TMP_PROD 	
 /***************************************************************************************************************/
--	FIM
/***************************************************************************************************************/
