IF OBJECT_ID('p_plug_Farma_tipoRegistro_sete') IS NOT NULL
	BEGIN
		DROP PROCEDURE p_plug_Farma_tipoRegistro_sete
	END
 
GO 

CREATE PROCEDURE [dbo].[p_plug_Farma_tipoRegistro_sete] ( @filial INT , @data  DATE, @cd_emp INT  = 1, @CargaInicial tinyint=0)
AS
BEGIN
	/*
		Esse SQL segue a mesma lgica do SQL de fabricantes, ele reni todos os cd_prods que apareceram 
		no movimento do dia para no final buscar as informaes de cada produto
	*/
	-------------------------------------------------------------------------------------------------------------------------
	set nocount on;
	Create table #TmpProd (Cd_prod Int);
	Create table #TmpProdJoin (Cd_prod Int);
	-------------------------------------------------------------------------------------------------------------------------
	if @CargaInicial =0
	begin
		INSERT INTO #TmpProd
			   SELECT
					  cd_prod
			   FROM
					est_nf_ent_it
			   WHERE dt_ent = @data
					 AND cd_emp = @cd_emp
					 AND cd_filial = @filial;
		-------------------------------------------------------------------------------------------------------------------------
		INSERT INTO #TmpProd
			   SELECT
					  cd_prod
			   FROM
					pdv_vd_IT
			   WHERE cd_emp = @cd_emp
					 AND cd_filial = @filial
					 AND dt_vd = @data;
		------------------------------------------------------------------------------------------------------------------------
		INSERT INTO #TmpProd
			   SELECT
					  cd_prod
			   FROM
					est_nf_sai_it IT
			   WHERE cd_emp = @cd_emp
					 AND cd_filial = @filial
					 AND dt_emi_nf = @data;  
		-------------------------------------------------------------------------------------------------------------------------
		INSERT INTO #TmpProd
			   SELECT
					  dev.cd_prod
			   FROM
					pdv_vd_dev_est_prod_imp dev
			   WHERE cd_emp = @cd_emp
					 AND cd_filial = @filial
					 AND dt_devo = @data;
		insert into #TmpProdJoin
		select distinct cd_prod from #TmpProd
	end
	if @CargaInicial =1
	begin
		insert into #TmpProdJoin
		select cd_prod from Est_Prod where cd_emp=@cd_emp
	end
	-------------------------------------------------------------------------------------------------------------------------
	-------------------------------------------------------------------------------------------------------------------------
	 SELECT
		   tiporegistro
		 , codigoprodutoassociado
		 , descricaoproduto
		 , apresentacaoproduto
		 , codigobarrasprincipal
		 , codigobarrasauxiliar1
		 , codigobarrasauxiliar2
		 , codigobarrasauxiliar3
		 , codigobarrasauxiliar4
		 , cnpjfabricante
		 , listaproduto
		 , codigosituacaotributaria
		 , percentualtributacaoicms
		 , precovenda
		 , precocusto
		 , precocustomedio
		 , percentualmarkup
		 , quantidademinima
		 , quantidadedemanda
		 , quantidadeestoque
		 , curvaabc
		 , giroestoque
		 , dataalteracao
		 , codigoncm
		 , portaria
		 , registroms
		 , dataalteracaoprecocusto
		 , descricaotipoprodutoassociado
		 , grupoprincipal
		 , fracao
		 , tp_prod
		 , cd_fabric
		 , FORMAT(dt_cad, 'yyyyMMdd') as dt_cad
	FROM
	(
		SELECT
			   7 AS tipoRegistro
			 , PROD.cd_prod AS codigoProdutoAssociado
			 , PROD.ds_prod AS descricaoProduto
			 , CPL.tp_prod
			 , '' AS apresentacaoProduto
			 ,  COALESCE( ( SELECT TOP 1 cd_barra FROM est_prod_cd_barra
									WHERE ean_caixa_fechada = 0  
											AND EAN_VALIDO=1
											AND cd_emp  = prod.cd_emp
											AND cd_prod = prod.cd_prod
											AND LEN(cd_barra) <= 14
									ORDER BY cd_barra ),  case when cpl.tp_prod = 10 then '88888888888888' else '99999999999999' end ) AS codigoBarrasPrincipal
			 ,  COALESCE(e.ean02,'') AS codigoBarrasAuxiliar1
			 ,  COALESCE(e.ean03,'') AS codigoBarrasAuxiliar2
			 ,  COALESCE(e.ean04,'') AS codigoBarrasAuxiliar3
			 ,  COALESCE(e.ean05,'') AS codigoBarrasAuxiliar4 
			 , FABRIC.cgc_fabric AS cnpjFabricante
			 , '3' AS listaProduto
			 , COALESCE(TRIB.cd_trib_fc, '060') AS codigoSituacaoTributaria		 
			 , COALESCE(TRIB_EST.PERC_ICMS_EST, 1) AS percentualTributacaoIcms
			 , COALESCE(CASE
							WHEN vlr_oferta > 0 THEN vlr_oferta
							ELSE vlr_tabela
						END, 1) AS precoVenda
			 , COALESCE(CPL.vlr_custo, 1) AS precoCusto
			 , COALESCE(CPL.vlr_custo_md, 1) AS precoCustoMedio
			 , (IIF(COALESCE(vlr_tabela, 1) >= COALESCE(CPL.vlr_custo, 1), 
					COALESCE(vlr_tabela, 1) - COALESCE(CPL.vlr_custo, 1), 
					COALESCE(vlr_tabela, 1))) / IIF(COALESCE(vlr_tabela, 1) = 0, 1, COALESCE(vlr_tabela, 1)) * 100 AS percentualMarkup
			 , round( ((cpl.mediaf/30) * cpl.eseg),2) AS quantidadeMinima
			 , round( ((cpl.mediaf/30) * cpl.emax),2) - cpl.qt_est  AS quantidadeDemanda
			 , cpl.qt_est AS quantidadeEstoque         
			 , case when CPL.CD_SIGLA = 5 then 'AA'
					when CPL.CD_SIGLA = 1 then 'A'
					when CPL.CD_SIGLA = 2 then 'B'
					when CPL.CD_SIGLA = 3 then 'C'
					ELSE 'D' END  curvaAbc
			 , cpl.mediaf/30 AS giroEstoque
			 , COALESCE(PROD.dt_ult_atu, PROD.dt_cad) AS dataAlteracao
			 , COALESCE(prod.nr_ncm, 99999999) AS codigoNcm
			 , '' AS portaria
			 , COALESCE(PROD.nr_reg_ms, '') AS registroMs
			 , PRECO.dt_registro AS dataAlteracaoPrecoCusto
			 , CLASSIF.ds_arv_merc_classificacao AS descricaoTipoProdutoAssociado
			 , CASE
				   WHEN LINHA.ds_arv_merc_linha IN('ETICO CONTROLADO', 'ETICO FARMA', 'ETICO OTC') THEN 2
				   WHEN LINHA.ds_arv_merc_linha IN('SIMILAR CONTROLADO', 'SIMILAR FARMA', 'SIMILAR OTC') THEN 3
				   WHEN LINHA.ds_arv_merc_linha IN('GENERICO CONTROLADO', 'GENERICO FARMA', 'GENERICO OTC') THEN 4
				   WHEN LINHA.ds_arv_merc_linha = 'PERFUMARIA' THEN 5
				   WHEN LINHA.ds_arv_merc_linha = 'CONVENIENCIA' THEN 6
				   ELSE 1
			   END AS grupoPrincipal
			 --,( SELECT qt_emb_compra FROM dbo.Ufd_est_prod_emb_apres(@filial, PROD.cd_prod, NULL)) AS fracao
			 ,CONVERT(MONEY,REPLACE(PROD.APRES_EMB_CP,',','.')) *  CONVERT(MONEY,REPLACE(PROD.APRES_QT_CP,',','.')) as fracao
			 , PROD.cd_fabric
			 , ISNULL(PROD.dt_cad,GETDATE()) dt_cad
		FROM
			 est_prod PROD
			 join #TmpProdJoin t on 
				PROD.cd_prod = t.cd_prod
			 left join v_est_prod_ean_plugfarma  e on  -- alterado para puxar da viso que trazer apenas ean_valido(1)
					PROD.cd_emp  = e.cd_emp 
				AND PROD.cd_prod = e.cd_prod
			 LEFT JOIN est_prod_fabric FABRIC ON PROD.cd_fabric = FABRIC.cd_fabric
			 JOIN est_prod_cpl CPL ON PROD.cd_emp = CPL.cd_emp 
										   AND PROD.cd_prod = CPL.cd_prod
			 INNER JOIN est_prod_est_arv_mercadologica ARVMERC ON ARVMERC.cd_emp = PROD.cd_emp
																  AND ARVMERC.cd_prod = PROD.cd_prod
			 INNER JOIN est_arv_merc_classificacao CLASSIF ON CLASSIF.cd_emp = ARVMERC.cd_emp
															  AND CLASSIF.cd_arv_merc_classificacao = ARVMERC.cd_arv_merc_classificacao
			 INNER JOIN est_arv_merc_linha LINHA ON LINHA.cd_emp = ARVMERC.cd_emp
													AND LINHA.cd_arv_merc_linha = ARVMERC.cd_arv_merc_linha
			 LEFT JOIN est_prod_cpl_trib_est TRIB_EST ON CPL.cd_emp				= TRIB_EST.cd_emp
															AND CPL.cd_filial	= TRIB_EST.cd_filial
															AND CPL.cd_prod		= TRIB_EST.cd_prod
															AND TRIB_EST.TP_MOV		= 0 
															AND TRIB_EST.TP_TRANSA	= 1 
			 LEFT JOIN est_trib TRIB ON TRIB_EST.cd_trib = TRIB.cd_trib
			 LEFT JOIN est_prod_preco PRECO ON preco.cd_emp = CPL.cd_emp
											   AND preco.cd_filial = CPL.cd_filial
											   AND preco.cd_prod = CPL.cd_prod
		WHERE 
			PROD.cd_emp = @cd_emp
			AND CPL.cd_filial = @Filial
	) AS PRODUTOS;
	-------------------------------------------------------------------------------------------------------------------------
END
GO


