UFN_BILLINGITEM_GETBILLINGITEMPRICE

Returns the billing item price

Return

Return Type
money

Parameters

Parameter	Parameter Type	Mode
@BILLINGITEMID	uniqueidentifier	IN
@CHARGEDATE	date	IN
@STUDENTID	uniqueidentifier	IN
@SCHOOLID	uniqueidentifier	IN
@GRADELEVELID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.UFN_BILLINGITEM_GETBILLINGITEMPRICE
(
    @BILLINGITEMID uniqueidentifier=null,
    @CHARGEDATE    date=null,
    @STUDENTID uniqueidentifier=null,
    @SCHOOLID uniqueidentifier=null,
    @GRADELEVELID uniqueidentifier=null
)
returns money
with execute as caller
as begin
    declare @PRICE money
    declare @TYPECODE tinyint

    if not @CHARGEDATE is null
    begin
        select @TYPECODE=PRICETYPECODE from dbo.BILLINGITEM where ID=@BILLINGITEMID

        if @TYPECODE=1
        begin
            select @PRICE=PRICE from dbo.BILLINGITEMPRICE where BILLINGITEMID=@BILLINGITEMID and (DATERANGETYPECODE=0 or (STARTDATE<=@CHARGEDATE and (ENDDATE>=@CHARGEDATE or ENDDATE is null)))
        end

        if @TYPECODE=2
        begin
            SELECT @PRICE=BIP.PRICE from dbo.BILLINGITEMDATES BID inner join dbo.BILLINGITEMPRICEBYGRADELEVEL BIP on BID.ID=BIP.BILLINGITEMDATESID 
            where BID.BILLINGITEMID=@BILLINGITEMID and (BID.DATERANGETYPECODE=0 or (BID.STARTDATE<=@CHARGEDATE and (BID.ENDDATE>=@CHARGEDATE or BID.ENDDATE is null))) and BIP.GRADELEVELID=@GRADELEVELID 
        end

        if @TYPECODE=3
        begin
            select @PRICE=BIP.PRICE 
            from 
                dbo.BILLINGITEMDATES BID 
            inner join dbo.BILLINGITEMPRICEBYSCHOOL BIP on BID.ID=BIP.BILLINGITEMDATESID 
            where (BID.BILLINGITEMID=@BILLINGITEMID) and 
                (BID.DATERANGETYPECODE=0 or (BID.STARTDATE<=@CHARGEDATE and 
                (BID.ENDDATE>=@CHARGEDATE or BID.ENDDATE is null))) and 
                (BIP.SCHOOLID=@SCHOOLID)
        end
    end
    return @PRICE 
end