UFN_BILLINGDATES_INUSE

Returns true if the provided academic year whose billing dates have been used.
Return

Return Type
bit
Parameters

Parameter	Parameter Type	Mode	Description
@ACADEMICYEARID	uniqueidentifier	IN
Definition

Copy


CREATE function dbo.UFN_BILLINGDATES_INUSE
(
    @ACADEMICYEARID uniqueidentifier
)
returns bit
with execute as caller
as begin
    declare @RETVAL bit = 0;
    --add any tables that use academic year dates



    -- check for flat rate billing item using an academic year

    if @RETVAL = 0 and exists 
            (select * 
                from dbo.ACADEMICYEAR 
                inner join (select 
                                STARTDATE, 
                                ENDDATE 
                            from 
                                dbo.BILLINGITEMPRICE 
                            where DATERANGETYPECODE = 2) as V1 on ACADEMICYEAR.BILLINGSTARTDATE = V1.STARTDATE and ACADEMICYEAR.BILLINGENDDATE = V1.ENDDATE 
                 where 
                    ID = @ACADEMICYEARID 
                    and not BILLINGSTARTDATE is null 
                    and not BILLINGENDDATE is null)    
        set @RETVAL = 1;



    -- check for billing item by grade level using an academic year

    if @RETVAL = 0 and exists 
            (select * 
                from dbo.ACADEMICYEAR 
                inner join (select 
                                STARTDATE, 
                                ENDDATE 
                            from 
                                dbo.BILLINGITEMDATES
                            where DATERANGETYPECODE = 2) as V1 on ACADEMICYEAR.BILLINGSTARTDATE = V1.STARTDATE and ACADEMICYEAR.BILLINGENDDATE = V1.ENDDATE 
                 where 
                    ID = @ACADEMICYEARID 
                    and not BILLINGSTARTDATE is null 
                    and not BILLINGENDDATE is null)    
        set @RETVAL = 1;

    return @RETVAL;
end