USP_DATALIST_BILLINGITEM

                CREATE procedure dbo.USP_DATALIST_BILLINGITEM
                (
                    @INCLUDEINACTIVE bit = 0,
                    @DATEFILTER tinyint = null,
                    @TYPEFILTER tinyint = null
                )
                as
                    set nocount on

                    declare @CURRENTDATE date

                    set @CURRENTDATE = GetDate()

                    select BILLINGITEM.ID,
                                 BILLINGITEM.NAME,
                                 case when BILLINGITEM.PRICETYPECODE = 1 then Coalesce((select TOP 1 PRICE from dbo.BILLINGITEMPRICE where BILLINGITEMPRICE.BILLINGITEMID=BILLINGITEM.ID and (DATERANGETYPECODE=0 OR (STARTDATE<=@CURRENTDATE and (ENDDATE>=@CURRENTDATE or ENDDATE IS NULL))) order by STARTDATE asc), (select TOP 1 PRICE from dbo.BILLINGITEMPRICE where BILLINGITEMPRICE.BILLINGITEMID=BILLINGITEM.ID order by STARTDATE desc)) else null end as COST,
                                 dbo.UFN_BILLINGITEM_GETALLVALIDDATES(ID) as DATES,
                                 BILLINGITEM.PRICETYPE,
                                 BILLINGITEM.DESCRIPTION,
                                 BILLINGITEM.ISINACTIVE,
                                 BILLINGITEM.PRICETYPECODE
                    from dbo.BILLINGITEM
                    where ((@INCLUDEINACTIVE = 1) or (ISINACTIVE = 0)) and
                    ((@TYPEFILTER is null) or (BILLINGITEM.PRICETYPECODE=@TYPEFILTER)) and
                    ((@DATEFILTER is null) or (@DATEFILTER=1 and ((exists(select * from dbo.BILLINGITEMPRICE where BILLINGITEMPRICE.BILLINGITEMID=BILLINGITEM.ID and ((DATERANGETYPECODE=0) or (STARTDATE<=@CURRENTDATE and (ENDDATE>=@CURRENTDATE or ENDDATE is null))))) or (exists(select * from dbo.BILLINGITEMDATES where BILLINGITEMDATES.BILLINGITEMID=BILLINGITEM.ID and ((DATERANGETYPECODE=0) or (STARTDATE<=@CURRENTDATE and (ENDDATE>=@CURRENTDATE or ENDDATE is null)))))))
                    or (@DATEFILTER=10 and (exists(select * from dbo.BILLINGITEMPRICE where BILLINGITEMPRICE.BILLINGITEMID=BILLINGITEM.ID and DATERANGETYPECODE=0) or exists(select * from dbo.BILLINGITEMDATES where BILLINGITEMDATES.BILLINGITEMID=BILLINGITEM.ID and DATERANGETYPECODE=0)))
                    or (@DATEFILTER=50 and (exists(select * from dbo.BILLINGITEMPRICE where BILLINGITEMPRICE.BILLINGITEMID=BILLINGITEM.ID and (DATERANGETYPECODE=0 or exists(select * from dbo.ACADEMICYEAR AY where AY.STARTDATE<=@CURRENTDATE and AY.ENDDATE>=@CURRENTDATE and BILLINGITEMPRICE.STARTDATE>=AY.BILLINGSTARTDATE and BILLINGITEMPRICE.ENDDATE<=AY.BILLINGENDDATE)))or exists(select * from dbo.BILLINGITEMDATES where BILLINGITEMDATES.BILLINGITEMID=BILLINGITEM.ID and (DATERANGETYPECODE=0 or exists(select * from dbo.ACADEMICYEAR AY where AY.STARTDATE<=@CURRENTDATE and AY.ENDDATE>=@CURRENTDATE and BILLINGITEMDATES.STARTDATE>=AY.BILLINGSTARTDATE and BILLINGITEMDATES.ENDDATE<=AY.BILLINGENDDATE)))))
                    or (@DATEFILTER=51 and (exists(select * from dbo.BILLINGITEMPRICE where BILLINGITEMPRICE.BILLINGITEMID=BILLINGITEM.ID and (DATERANGETYPECODE=0 or exists(select * from dbo.ACADEMICYEAR AY where AY.ACADEMICYEARNAMECODEID in (select top 1 AY1.ACADEMICYEARNAMECODEID from dbo.ACADEMICYEAR AY1 where AY1.STARTDATE>@CURRENTDATE order by AY1.STARTDATE asc) and BILLINGITEMPRICE.STARTDATE>=AY.BILLINGSTARTDATE and BILLINGITEMPRICE.ENDDATE<=AY.BILLINGENDDATE)))
                    or exists(select * from dbo.BILLINGITEMDATES where BILLINGITEMDATES.BILLINGITEMID=BILLINGITEM.ID and (DATERANGETYPECODE=0 or exists(select * from dbo.ACADEMICYEAR AY where AY.ACADEMICYEARNAMECODEID in (select top 1 AY1.ACADEMICYEARNAMECODEID from dbo.ACADEMICYEAR AY1 where AY1.STARTDATE>@CURRENTDATE order by AY1.STARTDATE asc) and BILLINGITEMDATES.STARTDATE>=AY.BILLINGSTARTDATE and BILLINGITEMDATES.ENDDATE<=AY.BILLINGENDDATE))))))
                    order by BILLINGITEM.PRICETYPECODE asc