IF EXISTS (SELECT
						 *
					 FROM
						 sys.objects
					 WHERE
						object_id = OBJECT_ID(N'[dbo].[P_GERA_EST_PROD_CPL]') AND
						type IN ( N'P', N'PC' ))
	DROP PROCEDURE [dbo].[P_GERA_EST_PROD_CPL]

GO

CREATE PROCEDURE P_GERA_EST_PROD_CPL (@FILIAL  INT=0, @PRODUTO INT=0)
WITH ENCRYPTION
AS
	SET NOCOUNT ON
		DECLARE @CD_FILIAL INT ;

		SELECT 
			1 CD_EMP
			, P.CD_PROD
			, P.VLR_CUSTO_GRP 
			, P.VLR_COMPRA_GRP 
			INTO #EST_PROD
		FROM 
			EST_PROD P
		WHERE CD_EMP = 1 
			AND CD_PROD = CASE WHEN @PRODUTO > 0 THEN @PRODUTO ELSE CD_PROD END



		DECLARE filiais CURSOR FAST_FORWARD READ_ONLY FOR
			SELECT
				CD_FILIAL
			FROM
				PRC_FILIAL
			WHERE
				CD_EMP = 1 AND
				CASE WHEN @FILIAL = 0 THEN 0
				ELSE CD_FILIAL
				END    = @FILIAL;

		OPEN filiais

		FETCH NEXT FROM filiais INTO @CD_FILIAL

		WHILE @@FETCH_STATUS = 0
			BEGIN
				
		
				--SELECT * FROM #EST_PROD
			
					PRINT 'Filial ' + CONVERT(VARCHAR, @CD_FILIAL) + ' '
								+ CONVERT(VARCHAR, GETDATE(), 108);

					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,
											 EMAX,
											 ESEG,
											 CD_SIGLA,
											 QT_EST_EM_TERC,
											 QT_EST_DE_TERC,
											 QT_EST_GAV,
											 QTDE_FINANCIADO,
											 QTDE_LIMITADOR,
											 QTDE_FACEAMENTO,
											 MEDIAF,
											 QT_VD_MES1,
											 QT_VD_MES2,
											 QT_VD_MES3,
											 PERC_DESC_FIDEL,
											 PERC_DESC_FIDEL_MAX,
											 PERC_COMISSAO_SERVICO,
											 QT_EST_DEPOSITOS,
											 QTDE_MES_VENDA_FECHADO,
											 PERC_COMIS,
											 PERC_DESC_LIMITE,
											 FLAG_VERBA_DESCON,
											 DT_ATUALIZA_PARAM,
											 VLR_FABRICA,
											 APRES_QT_VD,
											 COMPRA_POR_SUPRIMENTO,
											 FLAG_COMPRA_PHARMALINK,
											 VLR_RESSUP,
											 VLR_COMISSAO_FABRIC,
											 VLR_CUSTO_GER,
											 QTDE_DIAS_SLD_QT_EST,
											 FLAG_NAO_ANALISAR_CUSTO)
					SELECT
						 1 CD_EMP
						,@CD_FILIAL       CD_FILIAL
						,P.CD_PROD        CD_PROD
						,0                QT_EST
						,0                QT_EST_FLUT
						,0                PRAT
						,0                GAV
						,0                VLR_CUSTO_MD
						,P.VLR_CUSTO_GRP  VLR_CUSTO
						,0                QT_IMP
						,NULL             DT_IMP
						,0                SLD_INI
						,P.VLR_CUSTO_GRP  VLR_CUSTO_INI
						,1                CD_USU
						,NULL             DT_ULT_ATU
						,0                VLR_CONS
						,0                VLR_REV
						,0                PERC_IPI
						,0                IPI_NF_SAIDA
						,0                TP_PROD
						,0                STS_PROD
						,GETDATE()        DT_CAD
						,0                VLR_CUSTO_ANT
						,0                VLR_CUSTO_TAB
						,NULL             DT_ULT_ENTR_COMPRA
						,P.VLR_COMPRA_GRP VLR_COMPRA
						,0                DT_ULT_ENTR_TRANSF
						,0                VLR_TRANSF
						,0                QTDE_PONTO_FIDEL
						,0                EMAX
						,0                ESEG
						,4                CD_SIGLA
						,0                QT_EST_EM_TERC
						,0                QT_EST_DE_TERC
						,0                QT_EST_GAV
						,0                QTDE_FINANCIADO
						,0                QTDE_LIMITADOR
						,0                QTDE_FACEAMENTO
						,0                MEDIAF
						,0                QT_VD_MES1
						,0                QT_VD_MES2
						,0                QT_VD_MES3
						,0                PERC_DESC_FIDEL
						,0                PERC_DESC_FIDEL_MAX
						,0                PERC_COMISSAO_SERVICO
						,0                QT_EST_DEPOSITOS
						,0                QTDE_MES_VENDA_FECHADO
						,0                PERC_COMIS
						,0                PERC_DESC_LIMITE
						,0                FLAG_VERBA_DESCON
						,NULL             DT_ATUALIZA_PARAM
						,0                VLR_FABRICA
						,1                APRES_QT_VD
						,0                COMPRA_POR_SUPRIMENTO
						,0                FLAG_COMPRA_PHARMALINK
						,0                VLR_RESSUP
						,0                VLR_COMISSAO_FABRIC
						,0                VLR_CUSTO_GER
						,0                QTDE_DIAS_SLD_QT_EST
						,0                FLAG_NAO_ANALISAR_CUSTO
					FROM
						#EST_PROD P
						LEFT JOIN EST_PROD_CPL C ON
							P.CD_EMP = C.CD_EMP AND
							P.CD_PROD = C.CD_PROD AND
							C.CD_FILIAL = @CD_FILIAL
					WHERE
						P.CD_EMP = 1 AND
						C.CD_EMP IS NULL  ;

					INSERT INTO EST_PROD_PRECO
											(CD_EMP,
											 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,
											 VLR_CUSTO_GER,
											 ORIG_PRECO,
											 PRECO_ESPECIFICO,
											 VLR_OFERTA_AVISTA,
											 DT_REGISTRO)
					SELECT
						 1 CD_EMP
						,@CD_FILIAL CD_FILIAL
						,1			CD_CTR
						,P.CD_PROD  CD_PROD
						,0          VLR_TABELA
						,0          VLR_OFERTA
						,GETDATE()  DT_CAD
						,1          CD_USU
						,0          STS_PRECO
						,0          VLR_CUSTO
						,0          PERC_ICMS
						,0          PERC_IMP
						,0          PERC_COMIS
						,0          PERC_DESP
						,0          PERC_MARGEM
						,0          STS_IMP_ETQ_GOND
						,0          VLR_TABELA_ANT
						,0          VLR_OFERTA_ANT
						,0          VLR_CUSTO_GER
						,0          ORIG_PRECO
						,0          PRECO_ESPECIFICO
						,0          VLR_OFERTA_AVISTA
						,GETDATE()  DT_REGISTRO
					FROM
						 #EST_PROD P
						LEFT JOIN EST_PROD_PRECO C ON
							P.CD_EMP = C.CD_EMP AND
							P.CD_PROD = C.CD_PROD AND
							C.CD_FILIAL = @CD_FILIAL
					WHERE
						P.CD_EMP = 1 AND
						C.CD_EMP IS NULL   ;
						 
			  INSERT INTO EST_PROD_CPL_MEDIAF
					  (	CD_EMP,
					  CD_FILIAL,
					  CD_PROD,
					  CD_SIGLA,
					  QTDE_MEDIAF,
					  SAZ, VALID_QTDE_FACEAMENTO, QT_VD_ATUAL, 
					  QT_VD_MES1, 
					  QT_VD_MES2, 
					  QT_VD_MES3, 
					  LIMITP, 
					  LIMITV,
					  DT_NASC,
					  CD_USU,
					  DT_CAD )
				  SELECT 	1 AS CD_EMP,
						  @CD_FILIAL,
						  R.CD_PROD,
						  4 CD_SIGLA,
						  0 AS MEDIAF,
						  0 SAZ,
						  NULL,
						  0 QT_IT_ATUAL, 
						  0 QT_IT_MES1, 
						  0 QT_IT_MES2, 
						  0 QT_IT_MES3, 
						  0 LIMITP, 
						  0 LIMITV,
						  N.DT_NASC DT_NASC,
						  1 AS CD_USU,
						  GETDATE() AS DT_CAD
				  FROM 
				     #EST_PROD r
				    LEFT JOIN EST_PROD_CPL_NASC N ON 
				    R.CD_EMP = N.CD_EMP AND
				    R.CD_PROD = N.CD_PROD
				    AND N.CD_FILIAL = @CD_FILIAL
				  WHERE NOT EXISTS (SELECT * FROM EST_PROD_CPL_MEDIAF 
									  WHERE 
										  CD_EMP=1 AND 
										  CD_FILIAL=@CD_FILIAL AND 
										  CD_PROD=r.CD_PROD)
		 

		  INSERT INTO EST_PROD_CPL_MEDIAP
					  (	CD_EMP
						,CD_FILIAL
						,CD_PROD
						,QTDE_MEDIAP
						,CD_USU
						,DT_CAD
						,DT_ULT_ALT
						,FALTA_CRONICA
						,CD_CTR_PROC_CPL_LIB )
				  SELECT 	
						1 CD_EMP
						,@CD_FILIAL CD_FILIAL
						,CD_PROD
						,0 QTDE_MEDIAP
						,1 CD_USU
						,getdate() DT_CAD
						,null DT_ULT_ALT
						,0 FALTA_CRONICA
						,0 CD_CTR_PROC_CPL_LIB
						 
				  FROM 
				     #EST_PROD r 
				  WHERE NOT EXISTS (SELECT * FROM EST_PROD_CPL_MEDIAP 
									  WHERE 
										  CD_EMP=1 AND 
										  CD_FILIAL=@CD_FILIAL AND 
										  CD_PROD=r.CD_PROD)
		 


				FETCH NEXT FROM filiais INTO @CD_FILIAL
			END

		CLOSE filiais;

		DEALLOCATE filiais; 
		DROP TABLE #EST_PROD