USP_DATAFORMTEMPLATE_VIEW_BILLINGTRANSACTIONSUMMARY

The load procedure used by the view dataform template "Billing Transaction Summary View 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.
@FINANCIALTRANSACTIONID	uniqueidentifier	INOUT	FINANCIALTRANSACTIONID
@TYPECODE	tinyint	INOUT	TYPECODE
@TYPE	nvarchar(100)	INOUT	TYPE
@CONSTITUENTID	uniqueidentifier	INOUT	CONSTITUENTID
@CONSTITUENTNAME	nvarchar(200)	INOUT	CONSTITUENTNAME
@DATE	datetime	INOUT	Date
@DUEDATE	datetime	INOUT	Due date
@AMOUNT	money	INOUT	Amount
@POSTSTATUSCODE	tinyint	INOUT	POSTSTATUSCODE
@POSTSTATUS	nvarchar(100)	INOUT	Post status
@POSTDATE	datetime	INOUT	Post date
@HEADERPAGEID	uniqueidentifier	INOUT	HEADERPAGEID
@PAYMENTMETHODCODE	tinyint	INOUT	PAYMENTMETHODCODE
@PAYMENTMETHOD	nvarchar(100)	INOUT	Payment method
@REFERENCENUMBER	nvarchar(20)	INOUT	Reference number
@REFERENCEDATE	UDT_FUZZYDATE	INOUT	Reference date
@CHECKNUMBER	nvarchar(20)	INOUT	Check number
@CHECKDATE	UDT_FUZZYDATE	INOUT	Check date
@CONSTITUENTACCOUNT	nvarchar(254)	INOUT	Account
@DIRECTDEBITRESULTCODE	nvarchar(10)	INOUT	Result code
@CREDITCARDTYPE	nvarchar(100)	INOUT	Card type
@CREDITCARDHOLDERNAME	nvarchar(255)	INOUT	Card holder name
@CREDITCARDPARTIALNUMBER	nvarchar(16)	INOUT	Partial number
@CREDITCARDEXPIRESON	UDT_FUZZYDATE	INOUT	Expiration date
@CREDITCARDAUTHORIZATIONCODE	nvarchar(20)	INOUT	Authorization code
@OTHERPAYMENTMETHODCODE	nvarchar(100)	INOUT	Other payment method
@ACCOUNTINGMETHODCODE	tinyint	INOUT	ACCOUNTINGMETHODCODE
@AMOUNTAPPLIED	money	INOUT	Amount applied
@CHARGEREVERSED	bit	INOUT	CHARGEREVERSED
@PAYINGFORID	uniqueidentifier	INOUT	PAYINGFORID
@PAYINGFORNAME	nvarchar(200)	INOUT	Paying for

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_BILLINGTRANSACTIONSUMMARY
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @FINANCIALTRANSACTIONID uniqueidentifier = null output,
                    @TYPECODE tinyint = null output,
                    @TYPE nvarchar(100) = null output,
                    @CONSTITUENTID uniqueidentifier = null output,
                    @CONSTITUENTNAME nvarchar(200) = null output,
                    @DATE datetime = null output,
                    @DUEDATE datetime = null output,
                    @AMOUNT money = null output,
                    @POSTSTATUSCODE tinyint = null output,
                    @POSTSTATUS nvarchar(100) = null output,
                    @POSTDATE datetime = null output,
                    @HEADERPAGEID uniqueidentifier = null output,
                    @PAYMENTMETHODCODE tinyint = null output,
                    @PAYMENTMETHOD nvarchar(100) = null output,
                    @REFERENCENUMBER nvarchar(20) = null output,
                    @REFERENCEDATE dbo.UDT_FUZZYDATE = null output,
                    @CHECKNUMBER nvarchar(20) = null output,
                    @CHECKDATE dbo.UDT_FUZZYDATE = null output,
                    @CONSTITUENTACCOUNT nvarchar(254) = null output,
                    @DIRECTDEBITRESULTCODE nvarchar(10) = null output,
                    @CREDITCARDTYPE nvarchar(100) = null output,
                    @CREDITCARDHOLDERNAME nvarchar(255) = null output,
                    @CREDITCARDPARTIALNUMBER nvarchar(16) = null output,
                    @CREDITCARDEXPIRESON dbo.UDT_FUZZYDATE = null output,
                    @CREDITCARDAUTHORIZATIONCODE nvarchar(20) = null output,
                    @OTHERPAYMENTMETHODCODE nvarchar(100) = null output,
                    @ACCOUNTINGMETHODCODE tinyint = null output,
                    @AMOUNTAPPLIED money = null output,                    
                    @CHARGEREVERSED bit = null output,
                    @PAYINGFORID uniqueidentifier = null output,
                    @PAYINGFORNAME nvarchar(200) = null output
                )
                as
                    set nocount on;

                    set @DATALOADED = 0;

                    -- Open the symmetric key for decryption
                    exec dbo.USP_GET_KEY_ACCESS;

                    select  @DATALOADED = 1,
                            @FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID,
                            @TYPECODE = FINANCIALTRANSACTION.TYPECODE,
                            @TYPE = FINANCIALTRANSACTION.TYPE,
                            @CONSTITUENTID =  case when (FINANCIALTRANSACTION.TYPECODE = 105) then CONSTIT_PAIDBY.ID else CONSTITUENT.ID end,
                            @CONSTITUENTNAME = case when (FINANCIALTRANSACTION.TYPECODE = 105) then CONSTIT_PAIDBY.NAME else CONSTITUENT.NAME end,
                            @DATE = FINANCIALTRANSACTION.DATE,
                            @DUEDATE = CHARGE.DUEDATE,
                            @AMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
                            @POSTSTATUSCODE = FTLI.POSTSTATUSCODE,
                            @POSTSTATUS = FTLI.POSTSTATUS,
                            @POSTDATE = FTLI.POSTDATE,
                            @HEADERPAGEID = case when (FINANCIALTRANSACTION.TYPECODE = 105) then 
                                                case when dbo.UFN_CONSTITUENT_ISSTUDENT(CONSTIT_PAIDBY.ID) = 1 then 
                                                    '81d24e0e-8db9-427b-8856-b4771505cbaa' 
                                                else 
                                                    'A1D3ED71-FD60-47E5-AA4C-D01E48CDE167' 
                                                end
                                            else
                                                case when dbo.UFN_CONSTITUENT_ISSTUDENT(CONSTITUENT.ID) = 1 then 
                                                '81d24e0e-8db9-427b-8856-b4771505cbaa' 
                                                else 
                                                    'A1D3ED71-FD60-47E5-AA4C-D01E48CDE167' 
                                                end
                                            end,
                            @PAYMENTMETHODCODE = RECEIVABLEPAYMENT.PAYMENTMETHODCODE,
                            @PAYMENTMETHOD = RECEIVABLEPAYMENT.PAYMENTMETHOD,
                            @REFERENCENUMBER = RECEIVABLEPAYMENT.REFERENCENUMBER,
                            @REFERENCEDATE = RECEIVABLEPAYMENT.REFERENCEDATE,
                            @CHECKNUMBER = RECEIVABLEPAYMENTCHECK.CHECKNUMBER,
                            @CHECKDATE = RECEIVABLEPAYMENTCHECK.CHECKDATE,
                            @CONSTITUENTACCOUNT = CONSTITUENTACCOUNT.DESCRIPTION,
                            @DIRECTDEBITRESULTCODE = RECEIVABLEPAYMENTDIRECTDEBIT.DIRECTDEBITRESULTCODE,
                            @CREDITCARDTYPE = CREDITTYPECODE.DESCRIPTION,
                            @CREDITCARDHOLDERNAME = RECEIVABLEPAYMENTCREDITCARD.CARDHOLDERNAME,
                            @CREDITCARDPARTIALNUMBER = case when len(coalesce(RECEIVABLEPAYMENTCREDITCARD.CREDITCARDPARTIALNUMBER, '')) = 0 then 
                                                            ''
                                                        else 
                                                            replicate('*', 16 - len(RECEIVABLEPAYMENTCREDITCARD.CREDITCARDPARTIALNUMBER)) + RECEIVABLEPAYMENTCREDITCARD.CREDITCARDPARTIALNUMBER 
                                                        end,
                            @CREDITCARDEXPIRESON = RECEIVABLEPAYMENTCREDITCARD.EXPIRESON,
                            @CREDITCARDAUTHORIZATIONCODE = RECEIVABLEPAYMENTCREDITCARD.AUTHORIZATIONCODE,
                            @OTHERPAYMENTMETHODCODE= OTHERPAYMENTMETHODCODE.DESCRIPTION,
                            @AMOUNTAPPLIED = isnull((select sum(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT)
                                                    from dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_APPLICATION
                                                        inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_SOURCEORTARGETLINEITEM
                                                            on -- Charge join to applied to line item
                                                               ((FINANCIALTRANSACTION.TYPECODE = 104) and 
                                                                (FINANCIALTRANSACTIONLINEITEM_SOURCEORTARGETLINEITEM.ID = FINANCIALTRANSACTIONLINEITEM_APPLICATION.TARGETLINEITEMID)) or 
                                                               -- Payment or credit join to source line item
                                                               ((FINANCIALTRANSACTION.TYPECODE in (105, 106)) and 
                                                                (FINANCIALTRANSACTIONLINEITEM_SOURCEORTARGETLINEITEM.ID = FINANCIALTRANSACTIONLINEITEM_APPLICATION.SOURCELINEITEMID)) 
                                                        inner join dbo.FINANCIALTRANSACTION
                                                            on FINANCIALTRANSACTIONLINEITEM_SOURCEORTARGETLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID 
                                                    where (FINANCIALTRANSACTIONLINEITEM_APPLICATION.DELETEDON is null) and
                                                            (FINANCIALTRANSACTION.ID = @ID)), 0),
                            @PAYINGFORID = CONSTITUENT.ID,
                            @PAYINGFORNAME = CONSTITUENT.NAME
                    from dbo.FINANCIALTRANSACTION
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI 
                            on ((FINANCIALTRANSACTION.ID = FTLI.FINANCIALTRANSACTIONID) and (FTLI.TYPECODE = 0) and (FTLI.DELETEDON is null))
   left outer join dbo.CHARGE -- Charge specific info
                            on FINANCIALTRANSACTION.ID = CHARGE.ID
                        inner join dbo.CONSTITUENT
                            on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
                        left outer join dbo.RECEIVABLECREDIT -- Credit specific info
                            on FINANCIALTRANSACTION.ID = RECEIVABLECREDIT.ID
                        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.CREDITTYPECODE
                            on RECEIVABLEPAYMENTCREDITCARD.CREDITTYPECODEID = CREDITTYPECODE.ID
                        left outer join dbo.RECEIVABLEPAYMENTDIRECTDEBIT
                            on RECEIVABLEPAYMENT.ID = RECEIVABLEPAYMENTDIRECTDEBIT.ID
                        left outer join dbo.CONSTITUENTACCOUNT
                            on RECEIVABLEPAYMENTDIRECTDEBIT.CONSTITUENTACCOUNTID = CONSTITUENTACCOUNT.ID
                        left outer join dbo.RECEIVABLEPAYMENTOTHERMETHOD
                            on RECEIVABLEPAYMENTOTHERMETHOD.ID = RECEIVABLEPAYMENT.ID
                        left outer join dbo.OTHERPAYMENTMETHODCODE
                            on RECEIVABLEPAYMENTOTHERMETHOD.OTHERPAYMENTMETHODCODEID = OTHERPAYMENTMETHODCODE.ID
                        left outer join dbo.CONSTITUENT as CONSTIT_PAIDBY
                            on RECEIVABLEPAYMENT.CONSTITUENTID = CONSTIT_PAIDBY.ID
                        where FINANCIALTRANSACTION.ID = @ID

                    if @TYPECODE = 104
                      set @CHARGEREVERSED =dbo.UFN_CHARGE_ISREVERSED(@ID)
                    else
                      set @CHARGEREVERSED =0

                    close symmetric key sym_BBInfinity;

                  set @ACCOUNTINGMETHODCODE = dbo.UFN_APPLICATIONRULES_GETACCOUNTINGMETHODCODE()

                return 0;