BNK_BTB_XXT_VW(SQL View) |
Index Back |
|---|---|
External txns not posted to GLUnbooked Payments/Deposits-- Reconciled External Transactions not posted to GL |
| SELECT B.BNK_ID_NBR ,B.BANK_ACCOUNT_NUM ,B.RECON_CYCLE_NBR ,B.RECORD_SEQ_NUMBER ,'X' ,A.TRAN_REF_ID ,A.TRAN_DT ,B.RECON_TRANS_CODE ,B.BANK_STMT_CODE ,'1' ,' ' ,A.TRAN_AMT ,'XT' FROM PS_BNK_RCN_TRAN A , PS_BANK_STMT_TBL B WHERE A.RECON_STATUS = 'REC' AND B.BNK_ID_NBR = A.BNK_ID_NBR AND B.BANK_ACCOUNT_NUM = A.BANK_ACCOUNT_NUM AND B.RECON_CYCLE_NBR = A.RECON_CYCLE_NBR AND B.RECORD_SEQ_NUMBER = A.RECORD_SEQ_NUMBER AND NOT EXISTS ( SELECT 'X' FROM PS_BNK_BTB_GXT_VW WHERE BNK_ID_NBR = A.BNK_ID_NBR AND BANK_ACCOUNT_NUM = A.BANK_ACCOUNT_NUM AND TRAN_REF_ID = A.TRAN_REF_ID AND TRAN_DT = 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. |
| 2 | BANK_ACCOUNT_NUM | Character(35) | VARCHAR2(35) NOT NULL | The literal unique identifier associated with a given bank/counterparty account. |
| 3 | STATEMENT_ID | Number(10,0) | DECIMAL(10) NOT NULL | Statement ID for bank statements, key on Bank Statement Header |
| 4 | RECORD_SEQ_NUMBER | Number(10,0) | DECIMAL(10) NOT NULL | An internal work field utilized to enumerate database records in bank statement processing. |
| 5 | TR_SOURCE_CD | Character(1) | VARCHAR2(1) NOT NULL |
Note: Changes to Translate Values must also be made to TRA_SOURCE_CD and SOURCE_TYPE. These 3 fields must be kept in sync.
Values that are inactive are for use in TR Accounting only
B=BSP D=Deals E=Deal Fees F=Facility Fees H=Hedges I=Interest M=Deal: Estimated Maturity Accr N=Netted Deal O=Other P=Investment Pools R=EFT Fees S=Securities T=LC Fees W=EFT Requests X=Bank Transfers |
| 6 | TRAN_REF_ID | Character(20) | VARCHAR2(20) NOT NULL | Represents the unique transaction identifier in bank reconciliation processing. |
| 7 | TRAN_DT | Date(10) | DATE | Represents the transaction date in bank reconciliation processing. |
| 8 | 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 |
| 9 | BANK_STMT_CODE | Character(8) | VARCHAR2(8) NOT NULL | Bank Statement code - used to identify the codes used on the bank statement for different entires of balances and transactions. |
| 10 | ADJUSTMENT_SIDE | Character(1) | VARCHAR2(1) NOT NULL |
Book to bank adjustment side.
1=Reconciled, not posted to GL 2=Reconciled and posted to GL |
| 11 | TYPE_ADJUST | Character(1) | VARCHAR2(1) NOT NULL |
Book to Bank Adjsutment Type
1=Payments in Transit 2=Deposits in Transit 3=GL Adjustments 4=Fees & Interest 5=Unbooked Payments 6=Unbooked Deposits 7=Bank Adjustments 8=Subtl Bank Adjustments 9=Subtl GL Adjustments |
| 12 | MONETARY_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Specifies the monetary amount of a debit or credit in the business unit base currency. Debit entries are positive and credit entries are negative. This amount is only zero if associated with a statistical account. |
| 13 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |