BNK_BTB_DRTRAN

(SQL View)
Index Back

Drafts in Transit

Use for Book to Bank Reconciliation 10/07/2003: cmh :Removed ITEM

SELECT A.BNK_ID_NBR ,A.BANK_ACCOUNT_NUM ,C.FISCAL_YEAR ,C.ACCOUNTING_PERIOD ,A.DRAFT_BU ,A.DRAFT_ID ,A.ACCOUNTING_DT ,'ARDR' ,A.RECON_STATUS ,A.RECON_CYCLE_NBR ,A.RECORD_SEQ_NUMBER ,A.RECON_RUN_ID ,C.BUSINESS_UNIT_GL ,E.LEDGER ,E.ACCOUNT ,C.JOURNAL_ID ,C.JOURNAL_DATE ,SUM(C.MONETARY_AMOUNT) ,F.CURRENCY_CD ,SUM(C.FOREIGN_AMOUNT) ,F.FOREIGN_CURRENCY ,F.DESCR FROM PS_BNK_RCN_DRAFT A , PS_ITEM_ACTIVITY B , PS_ITEM_DST C , PS_BU_LED_AR_VW D , PS_JRNL_LN E , PS_JRNL_HEADER F WHERE B.BUSINESS_UNIT=A.DRAFT_BU AND B.DRAFT_BU=A.DRAFT_BU AND B.DRAFT_ID=A.DRAFT_ID AND C.BUSINESS_UNIT=B.BUSINESS_UNIT AND C.CUST_ID=B.CUST_ID AND C.ITEM=B.ITEM AND C.ITEM_LINE=B.ITEM_LINE AND C.ITEM_SEQ_NUM=B.ITEM_SEQ_NUM AND D.BUSINESS_UNIT=C.BUSINESS_UNIT_GL AND D.PRIMARY_LEDGER='Y' AND E.BUSINESS_UNIT=D.BUSINESS_UNIT AND E.JOURNAL_ID=C.JOURNAL_ID AND E.JOURNAL_DATE=C.JOURNAL_DATE AND E.JOURNAL_LINE=C.JOURNAL_LINE AND E.LEDGER=D.LEDGER AND F.BUSINESS_UNIT=E.BUSINESS_UNIT AND F.JOURNAL_ID=E.JOURNAL_ID AND F.JOURNAL_DATE=E.JOURNAL_DATE AND F.UNPOST_SEQ=E.UNPOST_SEQ AND F.JRNL_HDR_STATUS = 'P' AND F.UNPOST_SEQ=0 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=C.ACCOUNT) GROUP BY A.BNK_ID_NBR, A.BANK_ACCOUNT_NUM, C.FISCAL_YEAR, C.ACCOUNTING_PERIOD, A.DRAFT_ID, A.DRAFT_BU, A.ACCOUNTING_DT, A.RECON_STATUS, A.RECON_CYCLE_NBR, A.RECORD_SEQ_NUMBER, A.RECON_RUN_ID, C.BUSINESS_UNIT_GL, E.LEDGER, E.ACCOUNT, C.JOURNAL_ID, C.JOURNAL_DATE, F.CURRENCY_CD, F.FOREIGN_CURRENCY, F.DESCR

# 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 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
10 RECON_CYCLE_NBR Number(10,0) DECIMAL(10) NOT NULL The bank statement ID.
11 RECORD_SEQ_NUMBER Number(10,0) DECIMAL(10) NOT NULL An internal work field utilized to enumerate database records in bank statement processing.
12 RECON_RUN_ID Character(15) VARCHAR2(15) NOT NULL Recon Run ID
13 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
14 LEDGER Character(10) VARCHAR2(10) NOT NULL Ledger
15 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
16 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.
17 JOURNAL_DATE Date(10) DATE Specifies the date the journal was created.
18 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.
19 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
20 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.
21 FOREIGN_CURRENCY Character(3) VARCHAR2(3) NOT NULL Foreign Currency Code
22 DESCR Character(30) VARCHAR2(30) NOT NULL Description