BNK_BTB_DRTRAN(SQL View) |
Index Back |
---|---|
Drafts in TransitUse 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 |