USP_DATAFORMTEMPLATE_VIEW_BANKACCOUNTPROFILE

The load procedure used by the view dataform template "Bank Account 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.
@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
@DEFAULTCASHACCOUNTTYPECODE	tinyint	INOUT	Cash account type
@GLACCOUNTNUMBER	nvarchar(100)	INOUT	Cash account
@GLACCOUNTCODE	nvarchar(30)	INOUT	Cash account code
@SORTCODE	nvarchar(6)	INOUT	Sort code
@PDACCOUNTSYSTEM	nvarchar(50)	INOUT	Account system
@NUMSYSTEMS	tinyint	INOUT	Number of account systems
@STATUS	nvarchar(100)	INOUT	Status
@ACCOUNTTYPE	nvarchar(100)	INOUT	Account type
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Currency
@CURRENCY	nvarchar(110)	INOUT	Currency
@BANKINGSYSTEMID	uniqueidentifier	INOUT
@BIC	nvarchar(11)	INOUT
@BANKCODE	nvarchar(25)	INOUT
@UNMASKEDACCOUNT	nvarchar(255)	INOUT
@CANVIEWSENSITIVEINFORMATION	bit	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_BANKACCOUNTPROFILE
                (
                    @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,
                    @DEFAULTCASHACCOUNTTYPECODE tinyint = null output,
                    @GLACCOUNTNUMBER nvarchar(100) = null output,
                    @GLACCOUNTCODE nvarchar(30) = null output,
                    @SORTCODE nvarchar(6) = null output,
                    @PDACCOUNTSYSTEM nvarchar(50) = null output,
                    @NUMSYSTEMS tinyint = null output,
                    @STATUS nvarchar(100) = null output,
                    @ACCOUNTTYPE nvarchar(100) = null output,
                    @TRANSACTIONCURRENCYID uniqueidentifier = null output,
                    @CURRENCY nvarchar(110) = null output,
                    @BANKINGSYSTEMID uniqueidentifier = null output,
                    @BIC nvarchar(11) = null output,
                    @BANKCODE nvarchar(25) = null output,
                    @UNMASKEDACCOUNT nvarchar(255) = null output,
                    @CANVIEWSENSITIVEINFORMATION bit = null output,
                    @CURRENTAPPUSERID uniqueidentifier = null
                )
                as
                    set nocount on;

                    -- be sure to set this, in case the select returns no rows

                    set @DATALOADED = 0;

                    -- Open the symmetric key for encryption

                    exec dbo.USP_GET_KEY_ACCESS;

                    -- Can View Sensitive Information

                    set @CANVIEWSENSITIVEINFORMATION = 0;
                    declare @ISSYSADMIN bit = dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID);

                    if  @ISSYSADMIN = 0
                    begin
                     set @CANVIEWSENSITIVEINFORMATION = dbo.[UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'B01B8750-8BC3-4FA8-881E-BE2E605339BF'); 
                    end
                    else if @ISSYSADMIN = 1
                    begin
                        set @CANVIEWSENSITIVEINFORMATION = 1;
                    end

                    -- populate the output parameters, which correspond to fields on the form.  Note that

                    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

                    -- will display a "no data loaded" message.

                    select
                        @DATALOADED = 1,
                        @ACCOUNTNAME = BANKACCOUNT.ACCOUNTNAME,
                        @BANKID = BANKACCOUNT.BANKID,    
                        @ACCOUNTNUMBER = dbo.UFN_BANKACCOUNT_GETMASKEDACCOUNTNUMBER(BANKACCOUNT.ID),
                        --@ROUTINGNUMBER = coalesce(convert(nvarchar(9), DecryptByKey(BANKACCOUNT.ROUTINGNUMBER)),''),

                        --@ACCOUNTNUMBER = BANKACCOUNT.ACCOUNTNUMBER,

                        @ROUTINGNUMBER = BANKACCOUNT.ROUTINGNUMBER,
                        @STATUSCODE = BANKACCOUNT.STATUSCODE,
                        @STATUS = BANKACCOUNT.STATUS,
                        @ACCOUNTTYPECODE = BANKACCOUNT.ACCOUNTTYPECODE,
                        @ACCOUNTTYPE = BANKACCOUNT.ACCOUNTTYPE,
                        @MINIMUMBALANCE = BANKACCOUNT.MINIMUMBALANCE,
                        @DEFAULTPRINTER = BANKACCOUNT.DEFAULTPRINTER,
                        @RECONCILED = BANKACCOUNT.RECONCILED,
                        @RECONCILEDBALANCE = BANKACCOUNT.RECONCILEDBALANCE,
                        @LASTDEPOSITNUM = BANKACCOUNT.LASTDEPOSITNUM,
                        @DEFAULTCASHACCOUNTTYPECODE = BANKACCOUNT.DEFAULTCASHACCOUNTTYPECODE,
                        @GLACCOUNTNUMBER = GLACCOUNT.ACCOUNTNUMBER,
                        @GLACCOUNTCODE = PDACCOUNTSEGMENTVALUE.SHORTDESCRIPTION,
                        @SORTCODE = SORTCODE,
                        @PDACCOUNTSYSTEM =  (select NAME from PDACCOUNTSYSTEM where ID = 
                            (select PDACCOUNTSYSTEMID from BANKACCOUNT B where B.ID = BANKACCOUNT.ID)),
                        @NUMSYSTEMS = (select count(ID) from dbo.PDACCOUNTSYSTEM),
                        @TRANSACTIONCURRENCYID = BANKACCOUNT.TRANSACTIONCURRENCYID,
                        @CURRENCY = dbo.UFN_CURRENCY_GETDESCRIPTION(BANKACCOUNT.TRANSACTIONCURRENCYID),
                        @BANKINGSYSTEMID = BANKACCOUNT.BANKINGSYSTEMID,
                        @BIC = BANKACCOUNT.BIC,
                        @BANKCODE = BANKACCOUNT.BANKCODE,
                        @UNMASKEDACCOUNT =(
                        case 
                        when @CANVIEWSENSITIVEINFORMATION = 1 then
                        dbo.UFN_BANKACCOUNT_GETACCOUNTNUMBER(BANKACCOUNT.ID)
                        when @CANVIEWSENSITIVEINFORMATION = 0 then
                        dbo.UFN_BANKACCOUNT_GETMASKEDACCOUNTNUMBER(BANKACCOUNT.ID) 
                        end)
                    from 
                        dbo.BANKACCOUNT
                        left outer join dbo.PDACCOUNTSEGMENTVALUE on BANKACCOUNT.PDACCOUNTSEGMENTVALUEID = PDACCOUNTSEGMENTVALUE.ID
                        left outer join dbo.GLACCOUNT on BANKACCOUNT.GLACCOUNTID = GLACCOUNT.ID
                    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;

                    --close symmetric key sym_BBInfinity;

                    exec USP_CLOSE_KEY_ACCESS;

                    return 0;