USP_DATAFORMTEMPLATE_EDITLOAD_RECEIVABLECREDIT

The load procedure used by the edit dataform template "Credit 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
@CREDITDATE	date	INOUT	Credit date
@POSTDATE	datetime	INOUT	Post date
@POSTSTATUSCODE	tinyint	INOUT	Post status
@BILLINGITEMID	uniqueidentifier	INOUT	Crediting for
@AMOUNT	money	INOUT	Credit amount
@DESCRIPTION	nvarchar(100)	INOUT	Description
@CREDITID	nvarchar(60)	INOUT
@BILLINGITEMNAME	nvarchar(100)	INOUT	Crediting 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

Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RECEIVABLECREDIT
                    (
                        @ID uniqueidentifier,
                        @DATALOADED bit = 0 output,
                        @TSLONG bigint = 0 output,
                        @STUDENTID uniqueidentifier = null output,    
                        @STUDENTNAME nvarchar(255) = null output,
                        @CREDITDATE date = null output,                        
                        @POSTDATE datetime = null output,
                        @POSTSTATUSCODE tinyint = null output,
                      @BILLINGITEMID uniqueidentifier = null output,
                        @AMOUNT money =null output,
            @DESCRIPTION nvarchar(100) = null output,
            @CREDITID 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
                    )
                    as
                        set nocount on;

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

                        select
                            @DATALOADED = 1,
                            @TSLONG = RECEIVABLECREDIT.TSLONG,
                            @STUDENTID = dbo.FINANCIALTRANSACTION.CONSTITUENTID,
                            @STUDENTNAME=dbo.CONSTITUENT.NAME,
                            @CREDITDATE = dbo.FINANCIALTRANSACTION.[DATE],                            
                            @POSTDATE = FTLI.POSTDATE,
                            @POSTSTATUSCODE = FTLI.POSTSTATUSCODE,                            
                            @AMOUNT = dbo.FINANCIALTRANSACTION.TRANSACTIONAMOUNT,              
              @CREDITID = 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.RECEIVABLECREDIT 
                            inner join dbo.FINANCIALTRANSACTION on RECEIVABLECREDIT.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.STUDENTPROGRESSION on dbo.RECEIVABLECREDIT.STUDENTPROGRESSIONID = dbo.STUDENTPROGRESSION.ID
                            left outer join dbo.SCHOOLGRADELEVEL on dbo.STUDENTPROGRESSION.SCHOOLGRADELEVELID = dbo.SCHOOLGRADELEVEL.ID
                            left outer join dbo.EDUCATIONALHISTORY on dbo.RECEIVABLECREDIT.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
                            RECEIVABLECREDIT.ID = @ID;

            select top 1 @BILLINGITEMID = RECEIVABLECREDITLINEITEM.BILLINGITEMID,
                                     @BILLINGITEMNAME = BILLINGITEM.NAME,
                                     @PRICETYPECODE = BILLINGITEM.PRICETYPECODE,
                                     @DESCRIPTION = FINANCIALTRANSACTIONLINEITEM.DESCRIPTION
                        from dbo.RECEIVABLECREDIT
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM
                                on RECEIVABLECREDIT.ID=FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                            inner join dbo.RECEIVABLECREDITLINEITEM 
                                on RECEIVABLECREDITLINEITEM.ID=FINANCIALTRANSACTIONLINEITEM.ID
                            inner join dbo.BILLINGITEM 
                                on RECEIVABLECREDITLINEITEM.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'

                        return 0;