/****** Object:  StoredProcedure [dbo].[P_EST_PROD_CPL_REPLICA]    Script Date: 09/04/2012 15:22:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_PROD_CPL_REPLICA]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_PROD_CPL_REPLICA]
GO
GO
GO
--exec 
CREATE PROCEDURE [dbo].[P_EST_PROD_CPL_REPLICA] (@CD_FILIAL_ORIG AS INT,@CD_FILIAL_NOVA AS INT) AS 

CREATE TABLE #RS_PROD_CURVA (CD_EMP INT, 
							 CD_FILIAL INT, 
							 CD_PROD INT, 
							 CD_SIGLA INT)

--Replicando produtos complemento
INSERT INTO EST_PROD_CPL
	(CD_EMP, 
	CD_FILIAL, 
	CD_PROD, 
	QT_EST, 
	QT_EST_FLUT, 
	PRAT, 
	GAV, 
	VLR_CUSTO_MD, 
	VLR_CUSTO, 
	QT_IMP, 
	DT_IMP, 
	SLD_INI, 
	VLR_CUSTO_INI, 
	CD_USU, 
	DT_ULT_ATU, 
	VLR_CONS, 
	VLR_REV, 
	PERC_IPI, 
	IPI_NF_SAIDA, 
	TP_PROD, 
	STS_PROD, 
	DT_CAD, 
	VLR_CUSTO_ANT, 
	VLR_CUSTO_TAB, 
	DT_ULT_ENTR_COMPRA, 
	VLR_COMPRA, 
	DT_ULT_ENTR_TRANSF, 
	VLR_TRANSF, 
	QTDE_PONTO_FIDEL)
SELECT     
	CD_EMP, 
	@CD_FILIAL_NOVA AS CD_FILIAL, 
	CD_PROD, 
	0 AS QT_EST, 
	0 AS QT_EST_FLUT, 
	PRAT, 
	GAV, 
	0 AS VLR_CUSTO_MD, 
	0 AS VLR_CUSTO, 
	0 AS QT_IMP, 
	NULL  AS DT_IMP, 
	0 AS SLD_INI, 
	0 AS VLR_CUSTO_INI, 
	1 AS CD_USU, 
	NULL AS DT_ULT_ATU, 
	0 AS VLR_CONS, 
	0 AS VLR_REV, 
	PERC_IPI, 
	IPI_NF_SAIDA, 
	TP_PROD, 
	STS_PROD, 
	GETDATE() AS DT_CAD, 
	0 AS VLR_CUSTO_ANT, 
	0 AS VLR_CUSTO_TAB, 
	NULL AS DT_ULT_ENTR_COMPRA, 
	0 AS VLR_COMPRA, 
	NULL AS DT_ULT_ENTR_TRANSF, 
	0 AS VLR_TRANSF, 
	QTDE_PONTO_FIDEL
FROM         
	EST_PROD_CPL
WHERE   
	CD_EMP = 1 
	AND CD_FILIAL = @CD_FILIAL_ORIG
	AND  NOT EXISTS (
					SELECT 
						CD_EMP 
					FROM 
						EST_PROD_CPL CPL
					WHERE 
						EST_PROD_CPL.CD_EMP = CPL.CD_EMP AND
						@CD_FILIAL_NOVA = CPL.CD_FILIAL AND
						EST_PROD_CPL.CD_PROD = CPL.CD_PROD
					)

--Replicando preços
INSERT INTO EST_PROD_PRECO
SELECT     
	CD_EMP, 
	@CD_FILIAL_NOVA AS CD_FILIAL, 
	CD_CTR, CD_PROD, VLR_TABELA, VLR_OFERTA, DT_CAD, CD_USU, STS_PRECO, VLR_CUSTO, PERC_ICMS, PERC_IMP, 
    PERC_COMIS, PERC_DESP, PERC_MARGEM, STS_IMP_ETQ_GOND, VLR_TABELA_ANT, VLR_OFERTA_ANT
FROM         
	EST_PROD_PRECO
WHERE     
	CD_EMP = 1 
	AND CD_FILIAL = @CD_FILIAL_ORIG
	AND  NOT EXISTS (
					SELECT 
						CD_EMP 
					FROM 
						EST_PROD_PRECO PRECO
					WHERE 
						EST_PROD_PRECO.CD_EMP = PRECO.CD_EMP AND
						@CD_FILIAL_NOVA = PRECO.CD_FILIAL AND
						EST_PROD_PRECO.CD_PROD = PRECO.CD_PROD)

--Incluindo media F zerada
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)
	SELECT     
		CD_EMP, 
		@CD_FILIAL_NOVA AS CD_FILIAL, 
		CD_PROD, 
		CD_SIGLA, 
		0 AS QTDE_MEDIAF, 
		0 AS QTDE_FINANCIADO, 
		0 AS QTDE_FACEAMENTO, 
		0 AS QTDE_LIMITADOR, 
		NULL AS VALID_QTDE_FACEAMENTO, 
		0 AS QT_VD_ATUAL, 
		0 AS QT_VD_MES1, 
		0 AS QT_VD_MES2, 
		0 AS QT_VD_MES3, 
		0 AS LIMITP, 
		0 AS LIMITV, 
		1 AS CD_USU, 
		DT_CAD, 
		NULL AS DT_ULT_ALT, 
		0 AS SAZ, 
		NULL AS DT_NASC
	FROM         
		EST_PROD_CPL_MEDIAF
	WHERE     
		CD_EMP = 1 
		AND CD_FILIAL = @CD_FILIAL_ORIG
		AND  NOT EXISTS (
					SELECT 
						CD_EMP 
					FROM 
						EST_PROD_CPL_MEDIAF fe
					WHERE 
						EST_PROD_CPL_MEDIAF.CD_EMP = fe.CD_EMP AND
						@CD_FILIAL_NOVA = fe.CD_FILIAL AND
						EST_PROD_CPL_MEDIAF.CD_PROD = fe.CD_PROD)

--Alterando media F a curva dos produtos para o padrao da filial de origem
INSERT INTO #RS_PROD_CURVA
	SELECT     
		CD_EMP, 
		@CD_FILIAL_NOVA AS CD_FILIAL, 
		CD_PROD, 
		CD_SIGLA 
	FROM         
		EST_PROD_CPL_MEDIAF
	WHERE     
		CD_EMP = 1 
		AND CD_FILIAL = @CD_FILIAL_ORIG

UPDATE 
	f
SET
	CD_SIGLA = pr.CD_SIGLA 
FROM
	#RS_PROD_CURVA pr INNER JOIN EST_PROD_CPL_MEDIAf f ON
	pr.CD_EMP = f.CD_EMP AND
	pr.CD_FILIAL = f.CD_FILIAL AND
	pr.CD_PROD = f.CD_PROD 	

DROP TABLE #RS_PROD_CURVA;
GO


