BNK_BTB_GAP_VW(SQL View) |
Index Back |
|---|---|
In Transit: AP DisbursementsNeed aggregation due to scenario with single payment for many vouchers. |
| SELECT C.BNK_ID_NBR , C.BANK_ACCOUNT_NUM , A.FISCAL_YEAR , A.ACCOUNTING_PERIOD , A.BUSINESS_UNIT , C.PYMNT_ID_REF , C.PYMNT_DT , 'AP' , A.JOURNAL_ID , A.JOURNAL_DATE , C.BUSINESS_UNIT_GL , A.LEDGER , A.ACCOUNT , C.PYMNT_STATUS , C.RECON_STATUS , C.RECON_CYCLE_NBR , C.RECORD_SEQ_NUMBER , C.RECON_RUN_ID , C.STTLMNT_DT_ACTUAL , SUM(A.MONETARY_AMOUNT) , A.CURRENCY_CD , SUM(A.FOREIGN_AMOUNT) , A.FOREIGN_CURRENCY , D.DESCR , C.SOURCE_TXN FROM PS_VCHR_ACCTG_LINE A , PS_PYMNT_VCHR_XREF B , PS_PAYMENT_TBL C , PS_JRNL_HEADER D WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.VOUCHER_ID = B.VOUCHER_ID AND A.PYMNT_CNT = B.PYMNT_CNT AND B.BANK_SETID = C.BANK_SETID AND B.BANK_CD = C.BANK_CD AND B.BANK_ACCT_KEY = C.BANK_ACCT_KEY AND B.PYMNT_ID = C.PYMNT_ID AND A.PRIMARY_LEDGER = 'Y' AND D.BUSINESS_UNIT = A.BUSINESS_UNIT_GL AND D.JOURNAL_ID = A.JOURNAL_ID AND D.JOURNAL_DATE = A.JOURNAL_DATE AND D.UNPOST_SEQ = 0 AND D.LEDGER_GROUP = A.LEDGER_GROUP AND D.JRNL_HDR_STATUS = 'P' AND EXISTS ( SELECT 'X' FROM PS_BANK_ACCT_CHRT WHERE SETID = B.BANK_SETID AND BANK_CD = B.BANK_CD AND BANK_CD_CPTY = B.BANK_CD AND BANK_ACCT_KEY = B.BANK_ACCT_KEY AND COUNTERPARTY = B.BANK_CD AND BANK_ACCT_LED_TYPE IN ('A', 'C', 'H') AND ACCOUNT = A.ACCOUNT ) GROUP BY C.BNK_ID_NBR, C.BANK_ACCOUNT_NUM, A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, A.BUSINESS_UNIT, C.PYMNT_ID_REF, C.PYMNT_DT, A.JOURNAL_ID, A.JOURNAL_DATE, C.BUSINESS_UNIT_GL, A.LEDGER, A.ACCOUNT, C.PYMNT_STATUS, C.RECON_STATUS, C.RECON_CYCLE_NBR, C.RECORD_SEQ_NUMBER, C.RECON_RUN_ID, C.STTLMNT_DT_ACTUAL, A.CURRENCY_CD, A.FOREIGN_CURRENCY, D.DESCR, C.SOURCE_TXN |
| # | 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 | FISCAL_YEAR | Number(4,0) | SMALLINT NOT NULL | Fiscal Year |
| 4 | ACCOUNTING_PERIOD | Number(3,0) | SMALLINT NOT NULL | Identifies a time period to which you post transactions. Typically, an accounting period represents a month, but it can also represent a week, a day, or any user-defined interval. An accounting period has a beginning date and an ending date, and is defined in the calendar table. |
| 5 | SOURCE_BUS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Source Bus Unit |
| 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 | BTB_SOURCE | Character(4) | VARCHAR2(4) NOT NULL |
Book To Bank Source
AP=AP Disbursements ARCC=AR Deposits - Cash Clearing ARDD=AR Direct Debits ARDP=AR Deposits ARDR=AR Drafts EXAD=Expense Cash Advance EXPN=Expense Report GL=Journal Entry TR0=Other TRB=Bank Fees TRD=Treasury Deals TRE=Deal Fees TRF=Facility Fees TRGL=Manual Treasury Entry TRR=EFT Fees TRT=Letter of Credit Fees TRW=EFT Requests TRX=Bank Transfers XT=External Transactions |
| 9 | JOURNAL_ID | Character(10) | VARCHAR2(10) NOT NULL | Identifies a journal entry, consisting of a header and one or more lines. The Journal ID itself does not have to be unique, but together with the journal business unit and journal date, it forms a unique journal identifier. |
| 10 | JOURNAL_DATE | Date(10) | DATE | Specifies the date the journal was created. |
| 11 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 12 | LEDGER | Character(10) | VARCHAR2(10) NOT NULL | Ledger |
| 13 | ACCOUNT | Character(10) | VARCHAR2(10) NOT NULL | Account |
| 14 | PYMNT_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Payment Status
A=Alignment E=ACH/EFT Prenote L=Stale Dated Payment N=Reprinted with same ref number O=Overflow P=Paid R=Replaced S=Stopped T=Undo Escheatment U=Undo Cancel V=Void W=Escheated Check X=Deleted |
| 15 | 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 |
| 16 | RECON_CYCLE_NBR | Number(10,0) | DECIMAL(10) NOT NULL | The bank statement ID. |
| 17 | RECORD_SEQ_NUMBER | Number(10,0) | DECIMAL(10) NOT NULL | An internal work field utilized to enumerate database records in bank statement processing. |
| 18 | RECON_RUN_ID | Character(15) | VARCHAR2(15) NOT NULL | Recon Run ID |
| 19 | RECON_BANK_DT | Date(10) | DATE | The bank statement date. |
| 20 | 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. |
| 21 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 22 | FOREIGN_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Specifies the monetary amount of a debit or credit (accounting entry line) in the entry currency of the item. The sum of FOREIGN_AMOUNT values on AR lines should equal the ENTRY_AMT on the pending item. |
| 23 | FOREIGN_CURRENCY | Character(3) | VARCHAR2(3) NOT NULL | Foreign Currency Code |
| 24 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 25 | SOURCE_TXN | Character(4) | VARCHAR2(4) NOT NULL | Source Transaction |