USP_RECEIVABLEPAYMENT_LOAD

Loads a given receivable payment.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@TSLONG	bigint	INOUT
@CONSTITUENTNAME	nvarchar(150)	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT
@AMOUNT	money	INOUT
@PAYMENTDATE	datetime	INOUT
@POSTSTATUSCODE	tinyint	INOUT
@POSTDATE	datetime	INOUT
@PAYMENTMETHODCODE	tinyint	INOUT
@CONSTITUENTACCOUNTID	uniqueidentifier	INOUT
@CHECKDATE	UDT_FUZZYDATE	INOUT
@CHECKNUMBER	nvarchar(20)	INOUT
@REFERENCEDATE	UDT_FUZZYDATE	INOUT
@REFERENCENUMBER	nvarchar(20)	INOUT
@CREDITCARDHOLDERNAME	nvarchar(255)	INOUT
@CREDITCARDPARTIALNUMBER	nvarchar(4)	INOUT
@CREDITCARDTYPECODEID	uniqueidentifier	INOUT
@CREDITCARDEXPIRESON	UDT_FUZZYDATE	INOUT
@DIRECTDEBITRESULTCODE	nvarchar(10)	INOUT
@OTHERPAYMENTMETHODCODEID	uniqueidentifier	INOUT
@RECEIVABLEPAYMENTID	nvarchar(60)	INOUT
@CREDITCARDAUTHORIZATIONCODE	nvarchar(20)	INOUT
@STUDENTID	uniqueidentifier	INOUT
@STUDENTNAME	nvarchar(150)	INOUT

Definition

Copy



            CREATE procedure dbo.USP_RECEIVABLEPAYMENT_LOAD
            (
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @TSLONG bigint = 0 output,
                @CONSTITUENTNAME nvarchar(150) output,
                @CONSTITUENTID uniqueidentifier output,
                @AMOUNT money = 0 output,
                @PAYMENTDATE datetime = null output,
                @POSTSTATUSCODE tinyint = 1 output,
                @POSTDATE datetime = null output,
                @PAYMENTMETHODCODE tinyint = 1 output,
                @CONSTITUENTACCOUNTID uniqueidentifier = null output,
                @CHECKDATE dbo.UDT_FUZZYDATE = '00000000' output,
                @CHECKNUMBER nvarchar(20) = '' output,
                @REFERENCEDATE dbo.UDT_FUZZYDATE = '00000000' output,
                @REFERENCENUMBER nvarchar(20) = '' output,
                @CREDITCARDHOLDERNAME nvarchar(255) = '' output,
                @CREDITCARDPARTIALNUMBER nvarchar(4) = '' output,
                @CREDITCARDTYPECODEID uniqueidentifier = null output,
                @CREDITCARDEXPIRESON dbo.UDT_FUZZYDATE = '00000000' output,
                @DIRECTDEBITRESULTCODE nvarchar(10) = '' output,
                @OTHERPAYMENTMETHODCODEID uniqueidentifier = null output,
                @RECEIVABLEPAYMENTID nvarchar(60) = null output,
                @CREDITCARDAUTHORIZATIONCODE nvarchar(20) = null output,
                @STUDENTID uniqueidentifier = null output,
                @STUDENTNAME nvarchar(150) = null output             
            )
            as
            begin
                set @DATALOADED = 0;

                -- Get payment information

                select @DATALOADED = 1,
                       @TSLONG = FINANCIALTRANSACTION.TSLONG,
                       @CONSTITUENTID = CONSTIT_PAIDBY.ID,
                       @CONSTITUENTNAME = CONSTIT_PAIDBY.NAME,
                       @RECEIVABLEPAYMENTID = FINANCIALTRANSACTION.USERDEFINEDID,
                       @PAYMENTDATE = FINANCIALTRANSACTION.DATE,
                       @AMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
                       @POSTSTATUSCODE = FTLI.POSTSTATUSCODE,
                       @POSTDATE = FTLI.POSTDATE,
                       @PAYMENTMETHODCODE = RECEIVABLEPAYMENT.PAYMENTMETHODCODE,
                       @REFERENCENUMBER = RECEIVABLEPAYMENT.REFERENCENUMBER,
                       @REFERENCEDATE = RECEIVABLEPAYMENT.REFERENCEDATE,
                       @CHECKNUMBER = RECEIVABLEPAYMENTCHECK.CHECKNUMBER,
                       @CHECKDATE = RECEIVABLEPAYMENTCHECK.CHECKDATE,
                       @CONSTITUENTACCOUNTID = RECEIVABLEPAYMENTDIRECTDEBIT.CONSTITUENTACCOUNTID,
                       @DIRECTDEBITRESULTCODE = RECEIVABLEPAYMENTDIRECTDEBIT.DIRECTDEBITRESULTCODE,
                       @CREDITCARDTYPECODEID = RECEIVABLEPAYMENTCREDITCARD.CREDITTYPECODEID,
                       @CREDITCARDHOLDERNAME = RECEIVABLEPAYMENTCREDITCARD.CARDHOLDERNAME,
                       @CREDITCARDPARTIALNUMBER = RECEIVABLEPAYMENTCREDITCARD.CREDITCARDPARTIALNUMBER,
                       @CREDITCARDEXPIRESON = RECEIVABLEPAYMENTCREDITCARD.EXPIRESON,
                       @CREDITCARDAUTHORIZATIONCODE = RECEIVABLEPAYMENTCREDITCARD.AUTHORIZATIONCODE,
                       @OTHERPAYMENTMETHODCODEID= RECEIVABLEPAYMENTOTHERMETHOD.OTHERPAYMENTMETHODCODEID,
                       @STUDENTID = CONSTITUENT.ID,
                       @STUDENTNAME = CONSTITUENT.NAME
                from dbo.FINANCIALTRANSACTION
                    inner join dbo.CONSTITUENT
                        on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI 
                        on ((FINANCIALTRANSACTION.ID = FTLI.FINANCIALTRANSACTIONID) and (FTLI.TYPECODE = 0) and (FTLI.DELETEDON is null))
                    left outer join dbo.RECEIVABLEPAYMENT
                        on FINANCIALTRANSACTION.ID = RECEIVABLEPAYMENT.ID
                    left outer join dbo.RECEIVABLEPAYMENTCHECK
                        on RECEIVABLEPAYMENT.ID = RECEIVABLEPAYMENTCHECK.ID
                    left outer join dbo.RECEIVABLEPAYMENTCREDITCARD
                        on RECEIVABLEPAYMENT.ID = RECEIVABLEPAYMENTCREDITCARD.ID
                    left outer join dbo.RECEIVABLEPAYMENTDIRECTDEBIT
                        on RECEIVABLEPAYMENT.ID = RECEIVABLEPAYMENTDIRECTDEBIT.ID
                    left outer join dbo.RECEIVABLEPAYMENTOTHERMETHOD
                        on RECEIVABLEPAYMENTOTHERMETHOD.ID = RECEIVABLEPAYMENT.ID
                    left outer join dbo.CONSTITUENT as CONSTIT_PAIDBY
                        on RECEIVABLEPAYMENT.CONSTITUENTID = CONSTIT_PAIDBY.ID
                where FINANCIALTRANSACTION.ID = @ID;

            end