USP_DATAFORMTEMPLATE_VIEW_DEPOSITPROFILE

The load procedure used by the view dataform template "Deposit Profile 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.
@REFERENCE	nvarchar(100)	INOUT	Reference
@TRANSACTIONDATE	datetime	INOUT	Deposit date
@POSTSTATUSCODE	tinyint	INOUT	Post status
@POSTDATE	datetime	INOUT	Post date
@NUMBEROFPAYMENTS	int	INOUT	Number of payments
@AMOUNT	numeric(19, 4)	INOUT	Base amount
@STATUSCODE	tinyint	INOUT	Deposit status
@BANKACCOUNTID	uniqueidentifier	INOUT	BANKACCOUNTID
@BANKACCOUNTNAME	nvarchar(100)	INOUT	BANKACCOUNTNAME
@PROJECTEDAMOUNT	numeric(19, 4)	INOUT	Projected amount
@DIFFERENCEAMOUNT	numeric(19, 4)	INOUT	Difference
@TRANSACTIONNUMBER	int	INOUT	Deposit number
@PROJECTEDNUMBEROFPAYMENTS	int	INOUT	Projected payments
@DIFFERENCENUMBEROFPAYMENTS	int	INOUT	Difference
@DEFAULTPAYMENTDATE	datetime	INOUT	Default payment date
@DEFAULTPAYMENTAMOUNT	numeric(19, 4)	INOUT	Default payment amount
@DEFAULTPAYMENTMETHOD	nvarchar(60)	INOUT	Default payment method
@POSTSTATUS	nvarchar(60)	INOUT	Post status
@BANKACCOUNTSTATUSCODE	tinyint	INOUT	Bank account status
@DEFAULTALLOWEDPAYMENTMETHODS	int	INOUT	Allowed payment methods
@OTHERPAYMENTMETHODCODEID	uniqueidentifier	INOUT	Other method
@CREDITTYPECODEID	uniqueidentifier	INOUT	Card type
@OTHERPAYMENTMETHODCODE	nvarchar(100)	INOUT	Other method description
@CREDITTYPECODE	nvarchar(100)	INOUT	Credit type description
@TOTALCORRECTIONS	numeric(19, 4)	INOUT	Total corrections
@STATUS	nvarchar(100)	INOUT	Deposit status
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Payment currency
@BANKACCOUNTCURRENCYID	uniqueidentifier	INOUT	Bank account currency
@BASECURRENCYID	uniqueidentifier	INOUT	Bank account currency
@TRANSACTIONAMOUNT	numeric(19, 4)	INOUT	Deposit amount
@TRANSACTIONEXCHANGERATE	decimal(20, 8)	INOUT	Exchange rate
@PAYMENTCURRENCY	nvarchar(110)	INOUT	Payment currency
@ACTUALAMOUNT	numeric(19, 4)	INOUT	Actual amount

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DEPOSITPROFILE
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @REFERENCE nvarchar(100) = null output,
                    @TRANSACTIONDATE datetime = null output,
                    @POSTSTATUSCODE tinyint = null output,
                    @POSTDATE datetime = null output,
                    @NUMBEROFPAYMENTS int = null output,
                    @AMOUNT numeric(19,4) = null output,
                    @STATUSCODE tinyint = null output,
                    @BANKACCOUNTID uniqueidentifier = null output,
                    @BANKACCOUNTNAME nvarchar(100) = null output,
                    @PROJECTEDAMOUNT numeric(19,4) = null output,
                    @DIFFERENCEAMOUNT numeric(19,4) = null output,
                    @TRANSACTIONNUMBER int = null output,
                    @PROJECTEDNUMBEROFPAYMENTS int = null output,
                    @DIFFERENCENUMBEROFPAYMENTS int = null output,
                    @DEFAULTPAYMENTDATE datetime = null output,
                    @DEFAULTPAYMENTAMOUNT numeric(19, 4) = null output,
                    @DEFAULTPAYMENTMETHOD nvarchar(60) = null output,
                    @POSTSTATUS nvarchar(60) = null output,
                    @BANKACCOUNTSTATUSCODE tinyint = null output,
                    @DEFAULTALLOWEDPAYMENTMETHODS integer = null output,
                    @OTHERPAYMENTMETHODCODEID uniqueidentifier = null output,
                    @CREDITTYPECODEID uniqueidentifier = null output,
                    @OTHERPAYMENTMETHODCODE nvarchar(100) = null output,
                    @CREDITTYPECODE nvarchar(100) = null output,
          @TOTALCORRECTIONS numeric(19,4) = null output
          ,@STATUS nvarchar(100) = null output
          ,@TRANSACTIONCURRENCYID uniqueidentifier = null output
          ,@BANKACCOUNTCURRENCYID uniqueidentifier = null output
          ,@BASECURRENCYID uniqueidentifier = null output
          ,@TRANSACTIONAMOUNT numeric(19,4) = null output
          ,@TRANSACTIONEXCHANGERATE decimal(20,8) = null output
          ,@PAYMENTCURRENCY nvarchar(110) = null output
          ,@ACTUALAMOUNT numeric(19,4) = null output
                )
                as
                    set nocount on;

          Declare @ttlcorrections numeric(19,4)
          set @ttlcorrections = (select sum(BANKACCOUNTDEPOSITCORRECTION.SIGNEDTRANSACTIONAMOUNT) from BANKACCOUNTDEPOSITCORRECTION
                            where BANKACCOUNTDEPOSITCORRECTION.DEPOSITID = @ID);

                    set @ttlcorrections = isnull(@ttlcorrections, 0) - 
                        isnull((select SUM(AMOUNT) 
                            from dbo.[CREDITPAYMENT]
                            inner join dbo.[BANKACCOUNTDEPOSITCREDITPAYMENT] on [BANKACCOUNTDEPOSITCREDITPAYMENT].[ID] = [CREDITPAYMENT].[ID]
                            where [BANKACCOUNTDEPOSITCREDITPAYMENT].[DEPOSITID] = @ID), 0)

                    set @DATALOADED = 0;

                    select
                        @DATALOADED = 1,
                        @REFERENCE = BANKACCOUNTTRANSACTION.REFERENCE,
                        @TRANSACTIONDATE = BANKACCOUNTTRANSACTION.TRANSACTIONDATE,
                        @POSTSTATUSCODE = BANKACCOUNTTRANSACTION.POSTSTATUSCODE,
                        @POSTDATE = BANKACCOUNTTRANSACTION.POSTDATE,
                        @NUMBEROFPAYMENTS = BANKACCOUNTDEPOSIT.NUMBEROFPAYMENTS,
                        @AMOUNT = BANKACCOUNTTRANSACTION.AMOUNT,
                        @STATUSCODE = BANKACCOUNTDEPOSIT.STATUSCODE,
            @STATUS = BANKACCOUNTDEPOSIT.STATUS,
                        @BANKACCOUNTID = BANKACCOUNTTRANSACTION.BANKACCOUNTID,
                        @BANKACCOUNTNAME = dbo.UFN_BANKACCOUNT_GETACCOUNTNAME(BANKACCOUNTTRANSACTION.BANKACCOUNTID),
                        @PROJECTEDAMOUNT = BANKACCOUNTDEPOSIT.PROJECTEDAMOUNT,
                        @DIFFERENCEAMOUNT = coalesce(BANKACCOUNTDEPOSIT.TOTALPAYMENTAMOUNT - BANKACCOUNTDEPOSIT.PROJECTEDAMOUNT, 0),
                        @TRANSACTIONNUMBER = BANKACCOUNTTRANSACTION.TRANSACTIONNUMBER,
                        @PROJECTEDNUMBEROFPAYMENTS = BANKACCOUNTDEPOSIT.PROJECTEDNUMBEROFPAYMENTS,
                        @DIFFERENCENUMBEROFPAYMENTS = coalesce(BANKACCOUNTDEPOSIT.NUMBEROFPAYMENTS - BANKACCOUNTDEPOSIT.PROJECTEDNUMBEROFPAYMENTS, 0),
                        @DEFAULTPAYMENTDATE = BANKACCOUNTDEPOSIT.DEFAULTPAYMENTDATE,
                        @DEFAULTPAYMENTAMOUNT = BANKACCOUNTDEPOSIT.DEFAULTPAYMENTAMOUNT,
                        @DEFAULTPAYMENTMETHOD = BANKACCOUNTDEPOSIT.DEFAULTPAYMENTMETHOD,
                        @POSTSTATUS = BANKACCOUNTTRANSACTION.POSTSTATUS,
                        @BANKACCOUNTSTATUSCODE = BANKACCOUNT.STATUSCODE,
                        @DEFAULTALLOWEDPAYMENTMETHODS = BANKACCOUNTDEPOSIT.DEFAULTALLOWEDPAYMENTMETHODS,
                        @OTHERPAYMENTMETHODCODEID=BANKACCOUNTDEPOSIT.OTHERPAYMENTMETHODCODEID,
                        @CREDITTYPECODEID=BANKACCOUNTDEPOSIT.CREDITTYPECODEID,
                        @OTHERPAYMENTMETHODCODE=dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(@OTHERPAYMENTMETHODCODEID),
                        @CREDITTYPECODE=dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(@CREDITTYPECODEID),
            @TOTALCORRECTIONS = coalesce(@ttlcorrections,0),
            @TRANSACTIONCURRENCYID = BANKACCOUNTDEPOSIT.TRANSACTIONCURRENCYID
            ,@BANKACCOUNTCURRENCYID = BANKACCOUNT.TRANSACTIONCURRENCYID
            ,@BASECURRENCYID = BANKACCOUNTTRANSACTION.BASECURRENCYID
            ,@TRANSACTIONAMOUNT = BANKACCOUNTTRANSACTION.TRANSACTIONAMOUNT
            ,@TRANSACTIONEXCHANGERATE = (select RATE from dbo.CURRENCYEXCHANGERATE where ID = BANKACCOUNTDEPOSIT.TRANSACTIONEXCHANGERATEID)
            ,@PAYMENTCURRENCY = dbo.UFN_CURRENCY_GETDESCRIPTION(BANKACCOUNTDEPOSIT.TRANSACTIONCURRENCYID)
            ,@ACTUALAMOUNT = BANKACCOUNTDEPOSIT.TOTALPAYMENTAMOUNT
                    from dbo.BANKACCOUNTDEPOSIT
                    join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTDEPOSIT.ID
                    inner join dbo.BANKACCOUNT on BANKACCOUNTTRANSACTION.BANKACCOUNTID = BANKACCOUNT.ID
                    where BANKACCOUNTDEPOSIT.ID = @ID

                    return 0;