USP_DATAFORMTEMPLATE_VIEW_DISBURSEMENT

The load procedure used by the view dataform template "Bank Account Disbursement View"

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	Payee
@TRANSACTIONDATE	datetime	INOUT	Check date
@POSTSTATUS	nvarchar(14)	INOUT	Post status
@POSTDATE	datetime	INOUT	Post date
@AMOUNT	numeric(19, 4)	INOUT	Check amount
@BANKACCOUNTID	uniqueidentifier	INOUT	Bank account
@BANKACCOUNTNAME	nvarchar(100)	INOUT	Account name
@BANKACCOUNTTYPE	nvarchar(60)	INOUT	Type
@BANKACCOUNTNUMBER	nvarchar(50)	INOUT	Account number
@BANKID	uniqueidentifier	INOUT	Bank
@TRANSACTIONNUMBER	int	INOUT	Check number
@TYPE	nvarchar(17)	INOUT	Stock type
@TRANSACTIONTYPECODE	tinyint	INOUT	Transaction Type
@TRANSACTIONTYPE	nvarchar(60)	INOUT	Transaction Type
@FORMATTEDADDRESS	nvarchar(300)	INOUT	Address
@BANKNAME	nvarchar(100)	INOUT	Bank
@VENDORID	uniqueidentifier	INOUT	Vendor ID
@STATUS	nvarchar(11)	INOUT	Check status

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DISBURSEMENT
(
    @ID uniqueidentifier
    ,@DATALOADED bit = 0 output
    ,@REFERENCE nvarchar(100) = null output
    ,@TRANSACTIONDATE datetime = null output
    ,@POSTSTATUS nvarchar(14) = null output
    ,@POSTDATE datetime = null output
    ,@AMOUNT numeric(19,4) = null output
    ,@BANKACCOUNTID uniqueidentifier = null output
    ,@BANKACCOUNTNAME nvarchar(100) = null output
    ,@BANKACCOUNTTYPE nvarchar(60) = null output
    ,@BANKACCOUNTNUMBER nvarchar(50) = null output
    ,@BANKID uniqueidentifier = null output
    ,@TRANSACTIONNUMBER int = null output
    ,@TYPE nvarchar(17) = null output
    ,@TRANSACTIONTYPECODE tinyint = null output
    ,@TRANSACTIONTYPE nvarchar(60) = null output
    ,@FORMATTEDADDRESS nvarchar(300) = null output
    ,@BANKNAME nvarchar(100) = null output
    ,@VENDORID uniqueidentifier = null output
    ,@STATUS nvarchar(11) = null output

)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 0;

    exec USP_GET_KEY_ACCESS;

    select
        @DATALOADED = 1
        ,@REFERENCE = BAT.REFERENCE
        ,@TRANSACTIONDATE = BAT.TRANSACTIONDATE
        ,@POSTSTATUS = BAT.POSTSTATUS
        ,@POSTDATE = BAT.POSTDATE
        ,@AMOUNT = BAT.AMOUNT
        ,@BANKACCOUNTID = BAT.BANKACCOUNTID
        ,@BANKACCOUNTNAME = BA.ACCOUNTNAME
        ,@BANKACCOUNTTYPE = BA.ACCOUNTTYPE
        ,@BANKACCOUNTNUMBER = convert(nvarchar(50), DecryptByKey(BA.ACCOUNTNUMBER))
        ,@BANKID = BA.BANKID
        ,@TRANSACTIONNUMBER = BAT.TRANSACTIONNUMBER
        ,@TYPE = DF.TYPE
        ,@TRANSACTIONTYPECODE = BAT.TRANSACTIONTYPECODE
        ,@TRANSACTIONTYPE = case BAT.TRANSACTIONTYPECODE when 1 then 'Check' else '' end
        ,@FORMATTEDADDRESS = dbo.UFN_BUILDFULLADDRESS( null, BATE.ADDRESSBLOCK, BATE.CITY, BATE.STATEID, BATE.POSTCODE, BATE.COUNTRYID)
        ,@BANKNAME = C.NAME 
        ,@VENDORID = V.ID
        ,@STATUS = BAT.STATUS

    from dbo.BANKACCOUNTTRANSACTION as BAT
        inner join dbo.BANKACCOUNTTRANSACTION_EXT as BATE on BAT.ID = BATE.ID
        inner join dbo.BANKACCOUNT as BA on BAT.BANKACCOUNTID = BA.ID
        inner join dbo.CONSTITUENT as C on C.ID = BA.BANKID
        inner join dbo.FINANCIALTRANSACTION FT on BA.ID = FT.ID
        inner join dbo.VENDOR as V on V.ID = FT.CONSTITUENTID
        inner join dbo.DISBURSEMENTPROCESS DP on BATE.DISBURSEMENTPROCESSID = DP.ID 
        inner join dbo.DISBURSEMENTPROCESSFORMAT DPF on DP.ID = DPF.DISBURSEMENTPROCESSID 
        inner join dbo.DISBURSEMENTFORMAT DF on DPF.DISBURSEMENTFORMATID = DF.ID
    where BAT.ID = @ID;

    exec USP_CLOSE_KEY_ACCESS;

    return 0;