BNK_RCN_TRAN_VW (SQL View)	Index Back
Revaluation: External Trans. View that revalues External Transactions into bank account's default currency

BNK_RCN_TRAN_VW

(SQL View)

Revaluation: External Trans.

View that revalues External Transactions into bank account's default currency

SELECT A.bnk_id_nbr , A.bank_account_num , A.tran_ref_id , A.tran_dt , A.BUSINESS_UNIT , A.TRAN_AMT , A.CURRENCY_CD , A.tran_descr , A.recon_trans_code , A.recon_type , A.recon_status , A.reconcile_dt , A.reconcile_oprid , A.recon_cycle_nbr , A.RECORD_SEQ_NUMBER , A.sttlmnt_dt_actual , A.ACCTG_TMPL_ID , A.BUILD_ACCTG_STATUS , A.TRA_PROCESS_STATUS , A.PROCESS_INSTANCE , A.OPRID , A.DTTM_ENTERED , A.RECON_RUN_ID , %Round(%DecMult(%DecDiv(A.TRAN_AMT , B.RATE_DIV) , B.RATE_MULT) , B.DECIMAL_POSITIONS) , B.BASE_CURRENCY_BNK FROM PS_BNK_RCN_TRAN A , PS_BNK_CURR_RVL_VW B WHERE A.BNK_ID_NBR = B.BNK_ID_NBR AND B.BANK_ACCOUNT_NUM = A.BANK_ACCOUNT_NUM AND B.BNK_ID_NBR = A.BNK_ID_NBR AND B.BANK_ACCOUNT_NUM = A.BANK_ACCOUNT_NUM AND B.TXN_CURRENCY_CD = A.CURRENCY_CD AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_BNK_CURR_RVL_V2 WHERE SETID = B.SETID AND BANK_CD = B.BANK_CD AND BANK_ACCT_KEY = B.BANK_ACCT_KEY AND BASE_CURRENCY_BNK = B.BASE_CURRENCY_BNK AND TXN_CURRENCY_CD = B.TXN_CURRENCY_CD AND RATE_INDEX = B.RATE_INDEX AND CUR_RT_TYPE = B.CUR_RT_TYPE AND EFFDT <= A.TRAN_DT)

# 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.
Prompt Table: BANK_ID_VW

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

3 TRAN_REF_ID Character(20) VARCHAR2(20) NOT NULL Represents the unique transaction identifier in bank reconciliation processing.

4 TRAN_DT Date(10) DATE Represents the transaction date in bank reconciliation processing.

5 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

6 TRAN_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Represents a transaction amount in bank reconciliation processing.

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

8 TRAN_DESCR Character(60) VARCHAR2(60) NOT NULL Describes the transaction.

9 RECON_TRANS_CODE Character(3) VARCHAR2(3) NOT NULL The PeopleSoft-specific bank transaction code
ACH=Automated Clearing House
B=Direct Debit
BA=Bank Adjustments
BOE=Bill of Exchange - Disburse
BOO=Bill of Order - Disburse
C=Trade Receipt
CHK=Check
D=Deposits
DD=Direct Debit - Disburse
EFT=Electronic Funds Transfer
F=Fee Expense
G=General Transaction
I=Interest Expense
M=Miscellaneous Transaction
MAN=Manual Check
P=Payment
R=Drafts
S=Stop Pay
TRW=Treasury Wire
U=Funding Receipt
V=Voided Payment
WIR=Wire - Disbursement
WRI=Wire - Incoming

10 RECON_TYPE Character(1) VARCHAR2(1) NOT NULL Represents a series of options as to how a treasury wishes to perform reconciliation for a given bank
A=Automatically Reconciled
B=Balance System(s) to BankTran
F=Forced to Reconcile
M=Manually Reconciled
S=Semi Manual
U=Unreconciled
Default Value: U

11 RECON_STATUS Character(3) VARCHAR2(3) NOT NULL Reconciliation Status
ALG=Alignment Check
AMB=Ambiguous Match
AMT=Amounts Not Equal
CUR=Currency not Defined
DAT=Tran Date <> Value Date
DPI=Duplicate Input Transactions
DUP=Duplicate Transaction
MSC=Miscellaneous Transaction
NAM=Payee Names Not Equal
NTB=Not Found in Statement
NTF=Not Found In System
NTP=Not Found in POS Data
NUL=Null Transaction ID Ref
OVR=Overflow
PND=Pending Reconciliation
REC=Reconciled
RPR=Reprinted Check
RR=Requires Review - BSAC
STP=Stop Payment
TYP=Wrong Transaction Type
UNR=Unreconciled
VOI=Voided Check
Default Value: UNR

12 RECONCILE_DT Date(10) DATE The transaction reconciliation date.

13 RECONCILE_OPRID Character(30) VARCHAR2(30) NOT NULL The user ID that performed the reconciliation for a given transaction.

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

15 RECORD_SEQ_NUMBER Number(10,0) DECIMAL(10) NOT NULL An internal work field utilized to enumerate database records in bank statement processing.

16 STTLMNT_DT_ACTUAL Date(10) DATE Date in which a deal transaction is actually settled with a counterparty.

17 ACCTG_TMPL_ID Character(15) VARCHAR2(15) NOT NULL A unique key identifier that represents a predefined accounting template, that is a set of rules used to generate debits and credits.

18 BUILD_ACCTG_STATUS Character(1) VARCHAR2(1) NOT NULL An internal work field that indicates the point in time construction status for a a given treasu

19 TRA_PROCESS_STATUS Character(1) VARCHAR2(1) NOT NULL The processing status for a given accounting entry build.
A=Accruing
D=Deactivated
E=Error
I=In Process
N=Not Processed
P=Processed
T=Temporary Hold

20 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance

21 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

22 DTTM_ENTERED DateTime(26) TIMESTAMP Date Time Stamp for Entry

23 RECON_RUN_ID Character(15) VARCHAR2(15) NOT NULL Recon Run ID

24 BANK_ACCT_RVL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Field used to store the amount on a transaction revalued in the base currency of the transactions bank account.

25 BASE_CURRENCY_BNK Character(3) VARCHAR2(3) NOT NULL The base currency of the subject bank general ledger 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. Prompt Table: BANK_ID_VW
2	BANK_ACCOUNT_NUM	Character(35)	VARCHAR2(35) NOT NULL	The literal unique identifier associated with a given bank/counterparty account. Prompt Table: BANK_ACCTNUM_VW
3	TRAN_REF_ID	Character(20)	VARCHAR2(20) NOT NULL	Represents the unique transaction identifier in bank reconciliation processing.
4	TRAN_DT	Date(10)	DATE	Represents the transaction date in bank reconciliation processing.
5	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
6	TRAN_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Represents a transaction amount in bank reconciliation processing.
7	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
8	TRAN_DESCR	Character(60)	VARCHAR2(60) NOT NULL	Describes the transaction.
9	RECON_TRANS_CODE	Character(3)	VARCHAR2(3) NOT NULL	The PeopleSoft-specific bank transaction code ACH=Automated Clearing House B=Direct Debit BA=Bank Adjustments BOE=Bill of Exchange - Disburse BOO=Bill of Order - Disburse C=Trade Receipt CHK=Check D=Deposits DD=Direct Debit - Disburse EFT=Electronic Funds Transfer F=Fee Expense G=General Transaction I=Interest Expense M=Miscellaneous Transaction MAN=Manual Check P=Payment R=Drafts S=Stop Pay TRW=Treasury Wire U=Funding Receipt V=Voided Payment WIR=Wire - Disbursement WRI=Wire - Incoming
10	RECON_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Represents a series of options as to how a treasury wishes to perform reconciliation for a given bank A=Automatically Reconciled B=Balance System(s) to BankTran F=Forced to Reconcile M=Manually Reconciled S=Semi Manual U=Unreconciled Default Value: U
11	RECON_STATUS	Character(3)	VARCHAR2(3) NOT NULL	Reconciliation Status ALG=Alignment Check AMB=Ambiguous Match AMT=Amounts Not Equal CUR=Currency not Defined DAT=Tran Date <> Value Date DPI=Duplicate Input Transactions DUP=Duplicate Transaction MSC=Miscellaneous Transaction NAM=Payee Names Not Equal NTB=Not Found in Statement NTF=Not Found In System NTP=Not Found in POS Data NUL=Null Transaction ID Ref OVR=Overflow PND=Pending Reconciliation REC=Reconciled RPR=Reprinted Check RR=Requires Review - BSAC STP=Stop Payment TYP=Wrong Transaction Type UNR=Unreconciled VOI=Voided Check Default Value: UNR
12	RECONCILE_DT	Date(10)	DATE	The transaction reconciliation date.
13	RECONCILE_OPRID	Character(30)	VARCHAR2(30) NOT NULL	The user ID that performed the reconciliation for a given transaction.
14	RECON_CYCLE_NBR	Number(10,0)	DECIMAL(10) NOT NULL	The bank statement ID.
15	RECORD_SEQ_NUMBER	Number(10,0)	DECIMAL(10) NOT NULL	An internal work field utilized to enumerate database records in bank statement processing.
16	STTLMNT_DT_ACTUAL	Date(10)	DATE	Date in which a deal transaction is actually settled with a counterparty.
17	ACCTG_TMPL_ID	Character(15)	VARCHAR2(15) NOT NULL	A unique key identifier that represents a predefined accounting template, that is a set of rules used to generate debits and credits.
18	BUILD_ACCTG_STATUS	Character(1)	VARCHAR2(1) NOT NULL	An internal work field that indicates the point in time construction status for a a given treasu
19	TRA_PROCESS_STATUS	Character(1)	VARCHAR2(1) NOT NULL	The processing status for a given accounting entry build. A=Accruing D=Deactivated E=Error I=In Process N=Not Processed P=Processed T=Temporary Hold
20	PROCESS_INSTANCE	Number(10,0)	DECIMAL(10) NOT NULL	Process Instance
21	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
22	DTTM_ENTERED	DateTime(26)	TIMESTAMP	Date Time Stamp for Entry
23	RECON_RUN_ID	Character(15)	VARCHAR2(15) NOT NULL	Recon Run ID
24	BANK_ACCT_RVL_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Field used to store the amount on a transaction revalued in the base currency of the transactions bank account.
25	BASE_CURRENCY_BNK	Character(3)	VARCHAR2(3) NOT NULL	The base currency of the subject bank general ledger business unit.