USP_DATAFORMTEMPLATE_EDITLOAD_CHARGE

The load procedure used by the edit dataform template "Charge Edit Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@STUDENTID	uniqueidentifier	INOUT
@STUDENTNAME	nvarchar(255)	INOUT	Name
@CHARGEDATE	date	INOUT	Charge date
@DUEDATE	date	INOUT	Due date
@POSTDATE	datetime	INOUT	Post date
@POSTSTATUSCODE	tinyint	INOUT	Post status
@BILLINGITEMID	uniqueidentifier	INOUT	Charging for
@AMOUNT	money	INOUT	Charge amount
@DESCRIPTION	nvarchar(100)	INOUT	Description
@CHARGEID	nvarchar(60)	INOUT
@BILLINGITEMNAME	nvarchar(100)	INOUT	Charging for
@SCHOOLID	uniqueidentifier	INOUT	School
@GRADELEVELID	uniqueidentifier	INOUT	Grade level
@PRICETYPECODE	tinyint	INOUT	Billing item type
@SCHOOLNAME	nvarchar(100)	INOUT	School
@GRADELEVELCAPTION	nvarchar(100)	INOUT	Grade level
@CHARGEREVERSED	bit	INOUT

Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_CHARGE
                    (
                        @ID uniqueidentifier,
                        @DATALOADED bit = 0 output,
                        @TSLONG bigint = 0 output,
                        @STUDENTID uniqueidentifier = null output,    
                        @STUDENTNAME nvarchar(255) = null output,
                        @CHARGEDATE date = null output,
                        @DUEDATE date = null output,  
                        @POSTDATE datetime = null output,
                        @POSTSTATUSCODE tinyint = null output,
                          @BILLINGITEMID uniqueidentifier = null output,
                        @AMOUNT money =null output,
                        @DESCRIPTION nvarchar(100) = null output,
                        @CHARGEID nvarchar(60) = null output,
                        @BILLINGITEMNAME nvarchar(100) = null output,
                        @SCHOOLID uniqueidentifier = null output,
                        @GRADELEVELID uniqueidentifier = null output,
                        @PRICETYPECODE tinyint = null output,
                        @SCHOOLNAME nvarchar(100) = null output,
                        @GRADELEVELCAPTION nvarchar(100) = null output,
            @CHARGEREVERSED bit = null output
                    )
                    as
                        set nocount on;

                        set @DATALOADED = 0;
                        set @TSLONG = 0;

                        select
                            @DATALOADED = 1,
                            @TSLONG = CHARGE.TSLONG,
                            @STUDENTID = dbo.FINANCIALTRANSACTION.CONSTITUENTID,
                            @STUDENTNAME=dbo.CONSTITUENT.NAME,
                            @CHARGEDATE = dbo.FINANCIALTRANSACTION.[DATE],
                            @DUEDATE = dbo.CHARGE.DUEDATE,
                            @POSTDATE = FTLI.POSTDATE,
                            @POSTSTATUSCODE = FTLI.POSTSTATUSCODE,
                            @AMOUNT = dbo.FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
                            @CHARGEID = dbo.FINANCIALTRANSACTION.USERDEFINEDID,
                            @SCHOOLID = ISNULL(dbo.SCHOOLGRADELEVEL.SCHOOLID, dbo.EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID),
                            @GRADELEVELID = dbo.SCHOOLGRADELEVEL.GRADELEVELID,
                            @SCHOOLNAME = ISNULL(CONSTIT_SCHOOL.KEYNAME, CONSTIT_ENROLL_SCHOOL.KEYNAME),
                            @GRADELEVELCAPTION = dbo.GRADELEVEL.DESCRIPTION
                        from dbo.CHARGE 
                            inner join dbo.FINANCIALTRANSACTION 
                                on CHARGE.ID=FINANCIALTRANSACTION.ID
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI 
                                on ((FINANCIALTRANSACTION.ID = FTLI.FINANCIALTRANSACTIONID) and (FTLI.TYPECODE = 0) and (FTLI.DELETEDON is null))
                            left outer join dbo.CONSTITUENT 
                                on dbo.FINANCIALTRANSACTION.CONSTITUENTID=dbo.CONSTITUENT.ID
                            left outer join dbo.STUDENTCHARGE 
                                on dbo.CHARGE.ID = dbo.STUDENTCHARGE.ID
                            left outer join dbo.STUDENTPROGRESSION 
                                on dbo.STUDENTCHARGE.STUDENTPROGRESSIONID = dbo.STUDENTPROGRESSION.ID
                            left outer join dbo.SCHOOLGRADELEVEL 
                                on dbo.STUDENTPROGRESSION.SCHOOLGRADELEVELID = dbo.SCHOOLGRADELEVEL.ID
                            left outer join dbo.EDUCATIONALHISTORY 
                                on dbo.STUDENTCHARGE.EDUCATIONALHISTORYID = dbo.EDUCATIONALHISTORY.ID
                            left outer join dbo.CONSTITUENT as CONSTIT_SCHOOL 
                                on dbo.SCHOOLGRADELEVEL.SCHOOLID = CONSTIT_SCHOOL.ID
                            left outer join dbo.CONSTITUENT as CONSTIT_ENROLL_SCHOOL 
                                on dbo.EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID = CONSTIT_ENROLL_SCHOOL.ID
                            left outer join dbo.GRADELEVEL 
                                on dbo.SCHOOLGRADELEVEL.GRADELEVELID = dbo.GRADELEVEL.ID
                        where CHARGE.ID = @ID;

                        select top 1 @BILLINGITEMID = CHARGELINEITEM.BILLINGITEMID,
                                     @BILLINGITEMNAME = BILLINGITEM.NAME,
                                     @PRICETYPECODE = BILLINGITEM.PRICETYPECODE,
                                     @DESCRIPTION = FINANCIALTRANSACTIONLINEITEM.DESCRIPTION
                        from dbo.CHARGE
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM
                                on CHARGE.ID=FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                            inner join dbo.CHARGELINEITEM 
                    on CHARGELINEITEM.ID=FINANCIALTRANSACTIONLINEITEM.ID
                            inner join dbo.BILLINGITEM 
                                on CHARGELINEITEM.BILLINGITEMID = BILLINGITEM.ID
                        where (FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0) and
                                (FINANCIALTRANSACTIONLINEITEM.DELETEDON is null) and
                                (FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID);

                        if (@PRICETYPECODE = 1)
                            set @SCHOOLNAME = 'N/A'

                        if (@PRICETYPECODE <> 2)
                            set @GRADELEVELCAPTION = 'N/A'

            set @CHARGEREVERSED=dbo.UFN_CHARGE_ISREVERSED(@ID)

                        return 0;