USP_SEARCHLIST_BILLINGITEM

Search for a Billing Item.
Parameters

Parameter	Parameter Type	Mode	Description
@NAME	nvarchar(100)	IN	Name
@TYPECODE	tinyint	IN	Type
@INCLUDEINACTIVE	bit	IN	Include inactive
@STARTDATE	date	IN	Start date
@ENDDATE	date	IN	End date
@COST	money	IN	Cost
@CHARGEDATE	date	IN	Charge date
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
@INCLUDEFLATRATE	tinyint	IN	INCLUDEFLATRATE
Definition

Copy


            CREATE procedure dbo.USP_SEARCHLIST_BILLINGITEM
            (
                @NAME nvarchar(100) = null,                
                @TYPECODE tinyint = null,
                @INCLUDEINACTIVE bit = null,                
                @STARTDATE date = null,
                @ENDDATE date = null,
                @COST Money = null,
                @CHARGEDATE date = null,
                @MAXROWS smallint = 500,
                @INCLUDEFLATRATE tinyint = 1
            )
            as
                set nocount on;

                declare @CURRENTDATE date

                set @CURRENTDATE = GetDate()

                select distinct top(@MAXROWS) 
                    BILLINGITEM.ID,
                    PRODUCT.NAME,
                    case when BILLINGITEM.PRICETYPECODE = 1 then '$' + Convert(varchar,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)),1) else null end as COST,
                    dbo.UFN_BILLINGITEM_GETALLVALIDDATES(BILLINGITEM.ID) as DATES,
                    BILLINGITEM.PRICETYPE,                     
                    BILLINGITEM.DESCRIPTION,
                    case when BILLINGITEM.ISINACTIVE =0 then 1 else 0 end as ACTIVE                     
                from dbo.BILLINGITEM inner join dbo.PRODUCT on BILLINGITEM.ID=PRODUCT.ID
                where (@NAME is null or (PRODUCT.NAME like @NAME + '%')) and
                    (dbo.UFN_BILLINGITEM_VALIDFORSPECIFIEDDATE(BILLINGITEM.[ID], @CHARGEDATE)=1 or (@CHARGEDATE is null)) and
                    ((@INCLUDEINACTIVE = 1) or (ISINACTIVE = 0)) and
                    ((@TYPECODE is null) or (BILLINGITEM.PRICETYPECODE=@TYPECODE)) and
                    ((@INCLUDEFLATRATE = 1) or (BILLINGITEM.PRICETYPECODE <> 1)) and
                    ((@STARTDATE is null) or (BILLINGITEM.PRICETYPECODE = 1 and exists(select * from dbo.BILLINGITEMPRICE where BILLINGITEMPRICE.BILLINGITEMID=BILLINGITEM.ID and STARTDATE=@STARTDATE)) or (BILLINGITEM.PRICETYPECODE in (2,3) and exists(select * from dbo.BILLINGITEMDATES BID where BID.BILLINGITEMID=BILLINGITEM.ID and STARTDATE=@STARTDATE))) and
                    ((@ENDDATE is null) or (BILLINGITEM.PRICETYPECODE = 1 and exists(select * from dbo.BILLINGITEMPRICE where BILLINGITEMPRICE.BILLINGITEMID=BILLINGITEM.ID and ENDDATE=@ENDDATE)) or (BILLINGITEM.PRICETYPECODE in (2,3) and exists(select * from dbo.BILLINGITEMDATES BID where BID.BILLINGITEMID=BILLINGITEM.ID and ENDDATE=@ENDDATE))) and
                    ((@COST is null) or 
                    (BILLINGITEM.PRICETYPECODE = 1 and 
                    (exists(select * from BILLINGITEMPRICE where ID in (select TOP 1 ID from dbo.BILLINGITEMPRICE BIP where BIP.BILLINGITEMID=BILLINGITEM.ID and (DATERANGETYPECODE=0 OR (STARTDATE<=@CURRENTDATE and (ENDDATE>=@CURRENTDATE or ENDDATE IS NULL))) order by STARTDATE asc) 
                    and PRICE=@COST) or
                    exists(select * from BILLINGITEMPRICE where ID in (select TOP 1 ID from dbo.BILLINGITEMPRICE BIP where BIP.BILLINGITEMID=BILLINGITEM.ID order by STARTDATE desc) 
                    and PRICE=@COST))) or
                    (BILLINGITEM.PRICETYPECODE = 2 and
                    (exists(select * from dbo.BILLINGITEMPRICEBYGRADELEVEL BIP where BIP.BILLINGITEMDATESID in (select TOP 1 ID from dbo.BILLINGITEMDATES BID where BID.BILLINGITEMID=BILLINGITEM.ID and (DATERANGETYPECODE=0 OR (STARTDATE<=@CURRENTDATE and (ENDDATE>=@CURRENTDATE or ENDDATE IS NULL))) order by STARTDATE asc) and PRICE=@COST) or 
                    exists(select * from dbo.BILLINGITEMPRICEBYGRADELEVEL BIP where BIP.BILLINGITEMDATESID in (select TOP 1 ID from dbo.BILLINGITEMDATES BID where BID.BILLINGITEMID=BILLINGITEM.ID order by STARTDATE desc) and PRICE=@COST))) or 
                    (BILLINGITEM.PRICETYPECODE = 3 and 
                    (exists(select * from dbo.BILLINGITEMPRICEBYSCHOOL BIP where BIP.BILLINGITEMDATESID in (select TOP 1 ID from dbo.BILLINGITEMDATES BID where BID.BILLINGITEMID=BILLINGITEM.ID and (DATERANGETYPECODE=0 OR (STARTDATE<=@CURRENTDATE and (ENDDATE>=@CURRENTDATE or ENDDATE IS NULL))) order by STARTDATE asc) and PRICE=@COST)    or 
                    exists(select * from dbo.BILLINGITEMPRICEBYSCHOOL BIP where BIP.BILLINGITEMDATESID in (select TOP 1 ID from dbo.BILLINGITEMDATES BID where BID.BILLINGITEMID=BILLINGITEM.ID order by STARTDATE desc) and PRICE=@COST))))
                order by PRODUCT.NAME asc