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

--p_plug_Farma_tipoRegistro_onze 9,'20190319'
create procedure p_plug_Farma_tipoRegistro_onze (@filial int, @data  date,  @cd_emp int=1)
as
set nocount on;

/*
--Esse SQL traz o cabeçalho de todas as compras que foram feitas no dia
*/


CREATE TABLE #TmpCompras (tiporegistro			INT
								, numeroregistro	INT
								, datareferenciacompra		DATETIME
								, valortotalcompraliquida	MONEY
								, tipoidentificacao			INT
								, cnpjcpffornecedor		VARCHAR(20)
								, numeronota			INT
								, tiponotacompra		INT
								, chavenfe				VARCHAR(50)
								, serienota				VARCHAR(50) 
								, cd_nf				INT)
 
INSERT INTO  #TmpCompras			
SELECT
       tiporegistro
     , numeroregistro
     , datareferenciacompra
     , valortotalcompraliquida
     , tipoidentificacao
     , cnpjcpffornecedor
     , numeronota
     , tiponotacompra
     , chavenfe
     , serienota
	 , cd_nf
FROM
	(
		SELECT
			   11 AS tipoRegistro
			 , ROW_NUMBER() OVER(ORDER BY COMPRAS.cd_nf ASC) AS numeroRegistro
			 , COMPRAS.*
		FROM
			( SELECT DISTINCT
						E.cd_nf
						, E.dt_ent AS datareferenciacompra
						, E.VLR_TOT_PROD AS valortotalcompraliquida
						, 1 AS tipoidentificacao
						, Replace(Replace(Replace(Case when O.dest_orig = 1 then 
																	F.cgc_cpf else lj.cgc end , '-', ''), '.', ''), '/', '') AS cnpjcpffornecedor
						, E.nr_nf AS numeronota
						, 1 AS tiponotacompra
						, COALESCE(nfe.nr_autorizador, '') AS chavenfe
						, IIF(nfe.nr_autorizador IS NULL, 'M1', serieinner.serie) AS serienota
				FROM
						est_nf_ent E
						JOIN glb_mov_op O ON E.cd_nt_mov = O.cd_mov_op
						JOIN glb_mov_op_tp_mov tp ON o.TP_MOV = tp.TP_MOV
						LEFT JOIN pg_forn F ON E.cd_forn = F.cd_forn
						LEFT JOIN prc_filial lj ON	E.cd_forn	= lj.cd_filial AND 
													E.cd_emp	= lj.cd_emp

						LEFT JOIN v_est_nf_ent_nfe nfe ON E.cd_emp = nfe.cd_emp
														AND e.cd_filial = nfe.cd_filial
														AND E.cd_nf = nfe.cd_nf
						JOIN est_nf_serie serieinner ON E.cd_emp = serieinner.cd_emp
														AND E.cd_nf_serie = serieinner.cd_nf_serie
				WHERE
						    (O.dest_orig = 1 OR O.dest_orig = 2)
						AND E.cd_emp  = @cd_emp
						AND E.dt_ent = @data
						AND E.cd_filial = @filial
						and tp.TP_MOV in (0,1,3,10, 42,51,53,63,65)

			) COMPRAS
		where 
			not cnpjcpffornecedor is null 
	) AS COMPRAS ;


--Remove notas sem item
delete from t from
	#TmpCompras t
	left join   est_nf_ent_it i ON 
				i.cd_emp	= @cd_emp
			AND i.cd_filial = @filial
			AND t.cd_nf		= i.cd_nf 
			and I.qt_it >0 
			
where
	i.cd_emp is null;

select * from #TmpCompras