USP_DATAFORMTEMPLATE_EDIT_BILLINGITEM_VARYBYSCHOOL_COSTHISTORY

The save procedure used by the edit dataform template "Vary By School Billing Item Cost History Edit Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@STARTDATE	date	IN	From
@ENDDATE	date	IN	To
@DATERANGETYPECODE	tinyint	IN	Date range type
@BILLINGITEMSCHOOLENTRIES	xml	IN
Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_BILLINGITEM_VARYBYSCHOOL_COSTHISTORY
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @STARTDATE date,
                        @ENDDATE date,                        
                        @DATERANGETYPECODE tinyint,
                        @BILLINGITEMSCHOOLENTRIES xml
                    )
                    as
                        set nocount on;

                        if @CHANGEAGENTID is null  
                            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                        declare @CURRENTDATE datetime;
                        set @CURRENTDATE = getdate();

                        begin try                            
                            if @DATERANGETYPECODE=0
                            begin
                                set @STARTDATE=null
                                set @ENDDATE=null
                            end

                            declare @CHARGEEXISTS tinyint 
                            declare @LATESTCHARGEDATE date 
                            declare @CREDITEXISTS tinyint 
                            declare @LATESTCREDITDATE date 
                            declare @OLDSTARTDATE date
                            declare @OLDENDDATE date
                            declare @BILLINGITEMID uniqueidentifier 

                            select
                                @OLDSTARTDATE = STARTDATE,
                                @OLDENDDATE = ENDDATE,
                                @BILLINGITEMID=BILLINGITEMID
                            from
                                dbo.BILLINGITEMDATES
                            where
                                ID = @ID;

                            set @CHARGEEXISTS=dbo.UFN_BILLINGITEM_CHARGEEXISTSFORBILLINGITEM(@BILLINGITEMID, @OLDSTARTDATE, @OLDENDDATE);                        
                            if @CHARGEEXISTS=1
                                set @LATESTCHARGEDATE = dbo.UFN_BILLINGITEM_GETLATESTCHARGEDATEFORBILLINGITEM(@BILLINGITEMID);

                            if (Not @ENDDATE is null) and (@ENDDATE<@LATESTCHARGEDATE)
                                raiserror('ERR_INVALID_ENDDATE_CHARGE', 13, 1);

                            set @CREDITEXISTS=dbo.UFN_BILLINGITEM_CREDITEXISTSFORBILLINGITEM(@BILLINGITEMID, @OLDSTARTDATE, @OLDENDDATE);                        
                            if @CREDITEXISTS=1
                                set @LATESTCREDITDATE = dbo.UFN_BILLINGITEM_GETLATESTCREDITDATEFORBILLINGITEM(@BILLINGITEMID);

                            if (Not @ENDDATE is null) and (@ENDDATE<@LATESTCREDITDATE)
                                raiserror('ERR_INVALID_ENDDATE_CREDIT', 13, 1);

                            update dbo.BILLINGITEMDATES set
                                STARTDATE = @STARTDATE,
                                ENDDATE = @ENDDATE,                                
                                DATERANGETYPECODE = @DATERANGETYPECODE,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            where
                                ID = @ID;

                            exec dbo.USP_BILLINGITEM_GETSCHOOLENTRIES_UPDATEFROMXML @ID, @BILLINGITEMSCHOOLENTRIES, @CHANGEAGENTID, @CURRENTDATE;
                        end try

                        begin catch
                            exec dbo.USP_RAISE_ERROR;
                            return 1;
                        end catch

                        return 0;