
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[f_dat_fechainicioFacturado]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[f_dat_fechainicioFacturado]
GO

CREATE FUNCTION [dbo].[f_dat_fechainicioFacturado]
(@p_alquiler int,
@p_catalogo int)
RETURNS smalldatetime
AS
BEGIN
	
declare @fecha as  smalldatetime
declare @temp as smalldatetime
set @fecha =(
select top 1  MAX( pre.fecha_fh)  from doc_itemdocumento itempre
inner join doc_documento pre on pre.id=itempre.id_documento 
inner join doc_subtipoDocumento sub on pre.id_subtipoDocumento =sub.id and sub.id_tipoDocumento in (4)
 and  sub.prefijo like('PRE%') and sub.active=1
inner join doc_estadoTipoDocumento estadopre on estadopre.id=pre.id_estadotipodocumento 
and estadopre.estadoTipoDocumento_codigo not in('CAN') 
and pre.id_documento=@p_alquiler
where 
(@p_catalogo =0 or @p_catalogo =itempre.id_catalogo )
and itempre.id_catalogo not in (
select itemalq.id_catalogo
from 
 doc_itemdocumento itemalq  
inner join alq_tarifa tar on tar.id_itemdocumento =itemalq.id and tar.active=1 and itemalq.active=1
where  itemalq .id_documento  in (@p_alquiler)
union all
select det.id_catalogo from 
 equ_detallealquiler det where
  det.[id_documento.alquiler] in (@p_alquiler)
)
and itempre.id_catalogo in (select id from [cat_catalogo.otro] where active=1 and ( periodoalquiler is null or periodoalquiler <1 )
union all
select id from [cat_catalogo.repuesto] where active=1 and id>0))

if not @fecha is null and @fecha>0
begin 
--set @temp= convert(smalldatetime ,convert(varchar(100) ,(@fecha  + 0.999),103))
set @temp= (@fecha  + 0.999)
set @fecha =  CONVERT (smalldatetime ,  cast(DAY(@temp) as varchar(10))+ '/'+cast(month(@temp) as varchar(10)) +'/'+cast(year(@temp) as varchar(10)) +' 00:00' ,103 )
end

if @fecha is null 
begin 
set @fecha =0
end

return @fecha 
END


GO





