
if exists(select  1 from sys.procedures where name = 'p_plug_Farma_tipoRegistro_tres')
    begin 
		drop procedure p_plug_Farma_tipoRegistro_tres;
    end;
go

--p_plug_Farma_tipoRegistro_tres 11,'20190319',1,1
create procedure p_plug_Farma_tipoRegistro_tres (@filial int, @data  date, @cd_emp int=1, @CargaInicial tinyint=0)
as
/*
Esse SQL deve trazer todos os fabricantes que apareceram no movimento do dia, 
	todos os SQLs são executados nessa ordem para obter todos os cd_prods e no fim ele 
	pega o cd_fabric de cada cd_prod para obter as informações dos fabricantes
*/
-------------------------------------------------------------------------------------------------------------------------

set nocount on;
Create table #TmpProd (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;
end

if @CargaInicial =1
begin
	insert into #TmpProd
	select cd_prod from Est_Prod where cd_emp=@cd_emp
end
-------------------------------------------------------------------------------------------------------------------------
--Parte final onde ele pega todos os cd_prods que obteve antes para obter as informações de fabricantes
 
SELECT distinct 
       3 AS tipoRegistro
     , FABRIC.cgc_fabric AS cnpj
     , FABRIC.nm_fabric AS razaoSocial
     , FABRIC.nm_fabric AS nomeFantasia
     , 0 AS codigoIbgeCidade
     , '' AS inscricaoEstadual
     , '' AS endereçoLogradouro
     , '' AS enderecoNumero
     , '' AS enderecoComplemento
     , 00000000 AS cep
     , '' AS telefone
     , '' AS fax
     , '' AS email
     , FABRIC.cd_fabric
FROM
     est_prod PROD
     INNER JOIN est_prod_fabric FABRIC ON 
		PROD.cd_fabric = FABRIC.cd_fabric
	join #TmpProd t on 
		prod.cd_prod = t.Cd_prod
WHERE PROD.cd_emp = @cd_emp 
 
-------------------------------------------------------------------------------------------------------------------------
 