BANK_BAL_CUR_VW (SQL View)	Index Back
Bank Balance View This view will be used to display all Balances for a given Bank Account.

BANK_BAL_CUR_VW

(SQL View)

Bank Balance View

This view will be used to display all Balances for a given Bank Account.

SELECT DISTINCT ' ' ,' ' ,0 ,A.ASOFDATE ,B.CURRENCY_CD ,0 ,0 ,0 , ' ' , ' ' , ' ' , ' ' FROM PS_BANK_STMT_HDR A ,PS_BANK_BALANCES B ,PS_BANK_ACCT_TBL C ,PS_BANK_CD_TBL D WHERE A.BNK_ID_NBR = B.BNK_ID_NBR AND A.BANK_ACCOUNT_NUM = B.BANK_ACCOUNT_NUM AND A.STATEMENT_ID = B.RECON_CYCLE_NBR AND A.STATEMENT_ID = ( SELECT MAX(M.STATEMENT_ID ) FROM PS_BANK_STMT_HDR M WHERE M.BNK_ID_NBR = A.BNK_ID_NBR AND M.BANK_ACCOUNT_NUM = A.BANK_ACCOUNT_NUM AND M.STATEMENT_ID = A.STATEMENT_ID ) AND A.ASOFDATE = ( SELECT MAX(N.ASOFDATE) FROM PS_BANK_STMT_HDR N WHERE N.BNK_ID_NBR = A.BNK_ID_NBR AND N.BANK_ACCOUNT_NUM = A.BANK_ACCOUNT_NUM AND N.STATEMENT_ID = A.STATEMENT_ID ) AND C.BANK_CD = D.BANK_CD AND C.SETID = D.SETID AND D.BNK_ID_NBR = B.BNK_ID_NBR AND C.BANK_ACCOUNT_NUM=B.BANK_ACCOUNT_NUM

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BNK_ID_NBR Character(20) VARCHAR2(20) NOT NULL A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.

2 BANK_ACCOUNT_NUM Character(35) VARCHAR2(35) NOT NULL The literal unique identifier associated with a given bank/counterparty account.

3 RECON_CYCLE_NBR Number(10,0) DECIMAL(10) NOT NULL The bank statement ID.

4 ASOFDATE Date(10) DATE As of Date

5 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

6 BALANCE Signed Number(28,3) DECIMAL(26,3) NOT NULL Balance

7 STMT_BAL_OPEN Signed Number(28,3) DECIMAL(26,3) NOT NULL The bank statement opening balance as submitted by the bank.

8 STMT_BAL_CLOSE Signed Number(28,3) DECIMAL(26,3) NOT NULL The bank statement closing balance as submitted by the bank.

9 SETID Character(5) VARCHAR2(5) NOT NULL SetID

10 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code

11 BANK_ACCT_KEY Character(4) VARCHAR2(4) NOT NULL A user defined unique identifier that facilitates the identification of a given account with a given bank

12 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BNK_ID_NBR	Character(20)	VARCHAR2(20) NOT NULL	A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
2	BANK_ACCOUNT_NUM	Character(35)	VARCHAR2(35) NOT NULL	The literal unique identifier associated with a given bank/counterparty account.
3	RECON_CYCLE_NBR	Number(10,0)	DECIMAL(10) NOT NULL	The bank statement ID.
4	ASOFDATE	Date(10)	DATE	As of Date
5	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
6	BALANCE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Balance
7	STMT_BAL_OPEN	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	The bank statement opening balance as submitted by the bank.
8	STMT_BAL_CLOSE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	The bank statement closing balance as submitted by the bank.
9	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
10	BANK_CD	Character(5)	VARCHAR2(5) NOT NULL	Bank Code
11	BANK_ACCT_KEY	Character(4)	VARCHAR2(4) NOT NULL	A user defined unique identifier that facilitates the identification of a given account with a given bank
12	BUSINESS_UNIT_GL	Character(5)	VARCHAR2(5) NOT NULL	GL Business Unit