USP_DATAFORMTEMPLATE_VIEW_BANKACCOUNT

The load procedure used by the view dataform template "Bank Account 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.
@ACCOUNTNAME	nvarchar(100)	INOUT	Account name
@BANKID	uniqueidentifier	INOUT	Bank
@ACCOUNTNUMBER	nvarchar(50)	INOUT	Account number
@ROUTINGNUMBER	nvarchar(9)	INOUT	Routing number
@STATUSCODE	tinyint	INOUT	Status
@ACCOUNTTYPECODE	tinyint	INOUT	Account type
@MINIMUMBALANCE	decimal(19, 4)	INOUT	Minimum balance
@DEFAULTPRINTER	nvarchar(255)	INOUT	Default printer
@RECONCILED	bit	INOUT	RECONCILED
@RECONCILEDBALANCE	decimal(19, 4)	INOUT	Reconciled balance
@LASTDEPOSITNUM	int	INOUT	Last deposit number
@BANKNAME	nvarchar(100)	INOUT	Bank name
@ADDRESS	nvarchar(300)	INOUT	ADDRESS
@PHONENUMBER	nvarchar(100)	INOUT	PHONENUMBER
@PHONETYPE	nvarchar(100)	INOUT	PHONETYPE
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT	EMAILADDRESS
@DONOTMAIL	bit	INOUT	DONOTMAIL
@DONOTEMAIL	bit	INOUT	DONOTEMAIL
@DONOTPHONE	bit	INOUT	DONOTPHONE
@WEBADDRESS	UDT_WEBADDRESS	INOUT	WEBADDRESS
@LOOKUPID	nvarchar(100)	INOUT	Lookup ID
@CURRENTBALANCE	money	INOUT	Current balance
@ENDINGBALANCE	money	INOUT	Ending balance
@GLACCOUNTID	uniqueidentifier	INOUT	GL account ID
@PDACCOUNTSEGMENTVALUEID	uniqueidentifier	INOUT	GL account code
@DEFAULTCASHACCOUNTTYPECODE	tinyint	INOUT	DEFAULTCASHACCOUNTTYPECODE
@ACCOUNTNEEDSTOBEREASSIGNED	bit	INOUT	ACCOUNTNEEDSTOBEREASSIGNED
@ORIGINALADJUSTMENTPOSTED	bit	INOUT	Original adjustment posted
@PDACCOUNTSYSTEMID	uniqueidentifier	INOUT	Account system
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Currency
@HASUNPOSTEDTRANSACTIONS	bit	INOUT	Has unposted transactions
@HASEFTINFO	bit	INOUT	Has EFT information
@HASSEPAINFO	bit	INOUT
@BANKINGSYSTEMCONDITIONSETTINGNAME	nvarchar(50)	INOUT

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_BANKACCOUNT
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @ACCOUNTNAME nvarchar(100) = null output,
                    @BANKID uniqueidentifier = null output,
                    @ACCOUNTNUMBER nvarchar(50) = null output,
                    @ROUTINGNUMBER nvarchar(9) = null output,
                    @STATUSCODE tinyint = null output,
                    @ACCOUNTTYPECODE tinyint = null output,
                    @MINIMUMBALANCE decimal(19, 4) = null output,
                    @DEFAULTPRINTER nvarchar(255) = null output,
                    @RECONCILED bit = null output,
                    @RECONCILEDBALANCE decimal(19,4) = null output,
                    @LASTDEPOSITNUM integer = null output,
                    @BANKNAME nvarchar(100) = null output,
                    @ADDRESS nvarchar(300) = null output,
                    @PHONENUMBER nvarchar(100) = null output,
                    @PHONETYPE nvarchar(100) = null output,
                    @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
                    @DONOTMAIL bit = null output,
                    @DONOTEMAIL bit = null output,
                    @DONOTPHONE bit = null output,
                    @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
                    @LOOKUPID nvarchar(100) = null output,
                    @CURRENTBALANCE money = null output,
                    @ENDINGBALANCE money = null output,
                    @GLACCOUNTID uniqueidentifier = null output,
                    @PDACCOUNTSEGMENTVALUEID uniqueidentifier = null output,
                    @DEFAULTCASHACCOUNTTYPECODE tinyint = null output,
                    @ACCOUNTNEEDSTOBEREASSIGNED bit = null output,
                    @ORIGINALADJUSTMENTPOSTED bit = null output,
                    @PDACCOUNTSYSTEMID uniqueidentifier = null output,
                    @TRANSACTIONCURRENCYID uniqueidentifier = null output,
                    @HASUNPOSTEDTRANSACTIONS bit = null output,
                    @HASEFTINFO bit = null output,
                    @HASSEPAINFO bit = null output,
                    @BANKINGSYSTEMCONDITIONSETTINGNAME nvarchar(50) = null output
                )
                as
                    set nocount on;

                    set @DATALOADED = 0;

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

                    select
                        @DATALOADED = 1,
                        @ACCOUNTNAME = BANKACCOUNT.ACCOUNTNAME,
                        @BANKID = BANKACCOUNT.BANKID,
                        @ACCOUNTNUMBER = convert(nvarchar(50), DecryptByKey(BANKACCOUNT.ACCOUNTNUMBER)),
                        @ROUTINGNUMBER = BANKACCOUNT.ROUTINGNUMBER,
                        @STATUSCODE = BANKACCOUNT.STATUSCODE,
                        @ACCOUNTTYPECODE = BANKACCOUNT.ACCOUNTTYPECODE,
                        @MINIMUMBALANCE = BANKACCOUNT.MINIMUMBALANCE,
                        @DEFAULTPRINTER = BANKACCOUNT.DEFAULTPRINTER,
                        @RECONCILED = BANKACCOUNT.RECONCILED,
                        @RECONCILEDBALANCE = BANKACCOUNT.RECONCILEDBALANCE,
                        @LASTDEPOSITNUM = BANKACCOUNT.LASTDEPOSITNUM,
                        @GLACCOUNTID = BANKACCOUNT.GLACCOUNTID,
                        @PDACCOUNTSEGMENTVALUEID = BANKACCOUNT.PDACCOUNTSEGMENTVALUEID,
                        @DEFAULTCASHACCOUNTTYPECODE = BANKACCOUNT.DEFAULTCASHACCOUNTTYPECODE,
                        @PDACCOUNTSYSTEMID = BANKACCOUNT.PDACCOUNTSYSTEMID,
                        @TRANSACTIONCURRENCYID = BANKACCOUNT.TRANSACTIONCURRENCYID,
                        @HASEFTINFO = CASE WHEN BANKACCOUNTEFTINFO.ID is null then 0 else 1 end,
                        @HASSEPAINFO = case when BANKACCOUNTSEPAINFO.ID is null then 0 else 1 end,
                        @BANKINGSYSTEMCONDITIONSETTINGNAME = BANKINGSYSTEM.CONDITIONSETTINGNAME
                    from dbo.BANKACCOUNT
                    left join dbo.BANKACCOUNTEFTINFO on BANKACCOUNTEFTINFO.ID = BANKACCOUNT.ID
                    left join dbo.BANKACCOUNTSEPAINFO on BANKACCOUNTSEPAINFO.ID = BANKACCOUNT.ID
                    left join dbo.BANKINGSYSTEM on BANKINGSYSTEM.ID = BANKACCOUNT.BANKINGSYSTEMID
                    where BANKACCOUNT.ID = @ID

                    begin try
                        set @CURRENTBALANCE = dbo.UFN_BANKACCOUNT_GETCURRENTBALANCE(@ID);
                        set @ENDINGBALANCE = dbo.UFN_BANKACCOUNT_GETENDINGBALANCE(@ID);
                    end try
                    begin catch
                    end catch

                    --CONSTITUENT INFO
                    select
                        @BANKNAME = CONSTITUENT.NAME,
                        @DONOTMAIL = CONSTITUENT.DONOTMAIL,
                        @DONOTEMAIL = CONSTITUENT.DONOTEMAIL,
                        @DONOTPHONE = CONSTITUENT.DONOTPHONE,
                        @WEBADDRESS = CONSTITUENT.WEBADDRESS,
                        @LOOKUPID = CONSTITUENT.LOOKUPID
                    from
                        dbo.CONSTITUENT
                    where
                        CONSTITUENT.ID = @BANKID;

                    --EMAIL
                    select
                        @EMAILADDRESS = EMAILADDRESS
                    from
                        dbo.EMAILADDRESS
                    where
                        EMAILADDRESS.CONSTITUENTID = @BANKID and
                        EMAILADDRESS.ISPRIMARY = 1;

                    --PHONE
                    select
                        @PHONENUMBER = PHONE.NUMBER,
                        @PHONETYPE = (select DESCRIPTION FROM dbo.PHONETYPECODE WHERE ID = PHONE.PHONETYPECODEID)
                    from
                        dbo.PHONE
                    where 
                        PHONE.CONSTITUENTID = @BANKID and
                        PHONE.ISPRIMARY = 1;

                    --ADDRESS
                    select 
                        @ADDRESS = dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID)
                    from
                        dbo.ADDRESS
                    where
                        ADDRESS.CONSTITUENTID = @BANKID and
                        ADDRESS.ISPRIMARY = 1;

                    select @ACCOUNTNEEDSTOBEREASSIGNED = CASE WHEN COUNT(ID) = 0 THEN 0 ELSE 1 END
                    from dbo.BANKACCOUNT 
                    where ((GLACCOUNTID = @GLACCOUNTID and @GLACCOUNTID is not null) or (PDACCOUNTSEGMENTVALUEID = @PDACCOUNTSEGMENTVALUEID and @PDACCOUNTSEGMENTVALUEID is not null))
                        and ID <> @ID;

                    set @ORIGINALADJUSTMENTPOSTED = (select POSTSTATUSCODE from dbo.BANKACCOUNTTRANSACTION
                            join dbo.BANKACCOUNTADJUSTMENT on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTADJUSTMENT.ID
                            where BANKACCOUNTADJUSTMENT.TRANSFERADJUSTMENTID = @ID and ISORIGINALADJUSTMENT = 1 );
                    if @ORIGINALADJUSTMENTPOSTED = 0  
                        set @ORIGINALADJUSTMENTPOSTED = 1
                    else
                        set @ORIGINALADJUSTMENTPOSTED = 0;

                    if exists(select 1 from dbo.BANKACCOUNTTRANSACTION BAT where BAT.BANKACCOUNTID = @ID and BAT.POSTSTATUSCODE = 1)
                        set @HASUNPOSTEDTRANSACTIONS = 1
                    else
                        set @HASUNPOSTEDTRANSACTIONS = 0

                        exec USP_CLOSE_KEY_ACCESS;

                    return 0;