USP_DATAFORMTEMPLATE_VIEW_DEPOSIT

The load procedure used by the view dataform template "Deposit Page Expression 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	Deposit 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
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@NUMSYSTEMS	tinyint	INOUT	Number of accounting systems
@PDACCOUNTSYSTEM	nvarchar(50)	INOUT	Account system
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Payment currency
@TRANSACTIONCURRENCY	nvarchar(100)	INOUT	Payment currency
@BANKACCOUNTCURRENCYID	uniqueidentifier	INOUT	Bank account currency
@TRANSACTIONAMOUNT	numeric(19, 4)	INOUT	Deposit amount
@ACTUALAMOUNT	numeric(19, 4)	INOUT	Actual amount
@PDACCOUNTSYSTEMID	uniqueidentifier	INOUT	Account system
@SHOWBOTHAMOUNTS	bit	INOUT	Show both amounts
@HASSYSTEMDISTRIBUTIONS	bit	INOUT	HASSYSTEMDISTRIBUTIONS

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DEPOSIT
                (
                    @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,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @NUMSYSTEMS tinyint = null output,
                    @PDACCOUNTSYSTEM nvarchar(50) = null output
                    ,@TRANSACTIONCURRENCYID uniqueidentifier = null output
                    ,@TRANSACTIONCURRENCY nvarchar(100) = null output
                    ,@BANKACCOUNTCURRENCYID uniqueidentifier = null output
                    ,@TRANSACTIONAMOUNT numeric(19,4) = null output
                    ,@ACTUALAMOUNT numeric(19,4) = null output
                    ,@PDACCOUNTSYSTEMID uniqueidentifier = null output
                    ,@SHOWBOTHAMOUNTS bit = null output
                    ,@HASSYSTEMDISTRIBUTIONS bit = null output
                )
                as
                    set nocount on;

                    Declare @ttlcorrections numeric(19,4)
                    select @ttlcorrections = sum(cast((case when FT.TYPECODE=24 then -FT.BASEAMOUNT else FT.BASEAMOUNT end) as decimal(21,4))) 
                    from dbo.FINANCIALTRANSACTION FT
                    where FT.PARENTID = @ID and FT.TYPECODE in (24, 25)

                    set @DATALOADED = 0;

                    select
                        @DATALOADED = 1,
                        @REFERENCE = isnull(cast(left(FT.DESCRIPTION,100) as nvarchar(100)),''),
                        @TRANSACTIONDATE = cast(FT.[DATE] as datetime),
                        @POSTSTATUSCODE = isnull(cast(case FT.POSTSTATUSCODE when 1 then 1 when 2 then 0 when 3 then 2 end as tinyint), 2 ),
                        @POSTDATE = cast(FT.POSTDATE as datetime),
                        @NUMBEROFPAYMENTS = BANKACCOUNTDEPOSIT.NUMBEROFPAYMENTS,
                        @AMOUNT = FT.BASEAMOUNT,
                        @STATUSCODE = BANKACCOUNTDEPOSIT.STATUSCODE,
                        @BANKACCOUNTID = BANKACCOUNTTRANSACTION.BANKACCOUNTID,
                        @BANKACCOUNTNAME = rtrim(BANKACCOUNT.ACCOUNTNAME),
                        @PROJECTEDAMOUNT = BANKACCOUNTDEPOSIT.PROJECTEDAMOUNT,
                        @DIFFERENCEAMOUNT = coalesce(FT.BASEAMOUNT - 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 = FT.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),
                        @NUMSYSTEMS = dbo.UFN_PDACCOUNTSYSTEM_GETNUMBEROFSYSTEMSFORUSER(@CURRENTAPPUSERID),
                        @PDACCOUNTSYSTEM = PDACCOUNTSYSTEM.NAME
                        ,@TRANSACTIONCURRENCYID = BANKACCOUNTDEPOSIT.TRANSACTIONCURRENCYID
                        ,@TRANSACTIONCURRENCY = CURRENCY.NAME + ' (' + CURRENCY.ISO4217 + ')'
                        ,@BANKACCOUNTCURRENCYID = BANKACCOUNT.TRANSACTIONCURRENCYID
                        ,@TRANSACTIONAMOUNT = FT.TRANSACTIONAMOUNT
                        ,@ACTUALAMOUNT = BANKACCOUNTDEPOSIT.TOTALPAYMENTAMOUNT
                        ,@PDACCOUNTSYSTEMID = BANKACCOUNT.PDACCOUNTSYSTEMID
                        ,@SHOWBOTHAMOUNTS = CASE WHEN BANKACCOUNTDEPOSIT.TRANSACTIONCURRENCYID = BANKACCOUNT.TRANSACTIONCURRENCYID THEN 0 ELSE 1 END
                    from dbo.BANKACCOUNTDEPOSIT
                    inner join dbo.BANKACCOUNTTRANSACTION_EXT BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTDEPOSIT.ID
                    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = BANKACCOUNTTRANSACTION.ID
                    inner join dbo.BANKACCOUNT on BANKACCOUNTTRANSACTION.BANKACCOUNTID = BANKACCOUNT.ID
                    inner join dbo.CURRENCY on BANKACCOUNTDEPOSIT.TRANSACTIONCURRENCYID = CURRENCY.ID
                    inner join dbo.PDACCOUNTSYSTEM on BANKACCOUNT.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                    where BANKACCOUNTDEPOSIT.ID = @ID

            set @HASSYSTEMDISTRIBUTIONS = 0;

            if exists (select 1 from dbo.BANKACCOUNTDEPOSITPAYMENT P 
                inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = P.ID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                inner join dbo.JOURNALENTRY T on T.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID 
                where P.DEPOSITID = @ID
                and T.TYPECODE= 1)
                set @HASSYSTEMDISTRIBUTIONS = 1;

                    if @HASSYSTEMDISTRIBUTIONS = 0 
                        begin
                        if exists(select 1 from dbo.BANKACCOUNTDEPOSITCORRECTION_EXT
                            inner join dbo.FINANCIALTRANSACTION C on C.ID = BANKACCOUNTDEPOSITCORRECTION_EXT.ID
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = C.ID
                            inner join dbo.JOURNALENTRY T on T.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
                            where C.PARENTID = @ID and C.TYPECODE in (24, 25)
                            and T.TYPECODE = 1)
                            set @HASSYSTEMDISTRIBUTIONS = 1;
                        end

                    return 0;