BNK_SDSTMT_VW

(SQL View)
 Index Back

Bank Recon Statement View

Bank Recon Statement View - Current Day

SELECT A.BNK_ID_NBR ,A.BANK_ACCOUNT_NUM ,A.RECON_CYCLE_NBR ,A.RECORD_SEQ_NUMBER ,A.RECON_REF_ID ,A.RECON_BANK_DT ,A.RECON_TRAN_AMT ,A.VALUE_DT ,A.RECON_TRANS_CODE ,A.RECON_STATUS ,A.PROCESS_INSTANCE ,A.STMT_ACTIVITY_TYPE ,A.CURRENCY_CD ,A.FUNDS_TYPE ,A.RECON_RUN_ID ,A.BANK_REF_ID ,A.BANK_TRANS_CODE ,A.BANK_STMT_CODE ,A.AVAILABLE_BAL ,A.FLOAT1 ,A.FLOAT2 ,B.RECON_REF_ID_01 ,B.RECON_REF_ID_02 ,B.RECON_REF_ID_03 ,B.RECON_REF_ID_04 ,B.RECON_REF_ID_05 ,B.RECON_REF_ID_06 ,B.RECON_REF_ID_07 ,B.RECON_REF_ID_07 ,B.RECON_REF_ID_09 ,B.RECON_REF_ID_10 ,A.DESCRLONG FROM PS_BANK_SDSTMT_TBL A LEFT OUTER JOIN PS_BNK_SDADDND_TBL B ON A.BNK_ID_NBR = B.BNK_ID_NBR AND A.BANK_ACCOUNT_NUM = B.BANK_ACCOUNT_NUM AND A.RECON_CYCLE_NBR = B.RECON_CYCLE_NBR AND A.RECORD_SEQ_NUMBER = B.RECORD_SEQ_NUMBER

  • Parent record: BANK_STMT_HDR

    • # 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 RECON_CYCLE_NBR Number(10,0) DECIMAL(10) NOT NULL The bank statement ID.
    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 RECON_REF_ID Character(20) VARCHAR2(20) NOT NULL The transaction reference identifier on the inbound bank statement.
    6 RECON_BANK_DT Date(10) DATE The bank statement date.
    7 RECON_TRAN_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL The bank statement transaction amount.
    8 VALUE_DT Date(10) DATE Equivalent to a deal transaction's settlement date.
    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_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
    11 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
    12 STMT_ACTIVITY_TYPE Character(5) VARCHAR2(5) NOT NULL Represents a series of values that provide a more granular definition of a given reconcil

    Prompt Table: BANK_ACT_TYPE

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

    Default Value: BNK_RCN_CYC.CURRENCY_CD

    Prompt Table: CURRENCY_CD_TBL

    14 FUNDS_TYPE Character(1) VARCHAR2(1) NOT NULL Funds Availability Type
    0=Immediate Availability
    1=One-day Availability
    2=Two-or more-day Availability
    D=Distributed Availability-vary
    S=Distributed Availability-fixed
    V=Value Dated
    Z=unknown
    15 RECON_RUN_ID Character(15) VARCHAR2(15) NOT NULL Recon Run ID
    16 BANK_REF_ID Character(16) VARCHAR2(16) NOT NULL A reference identifier associated with a given bank/counterparty transaction.
    17 BANK_TRANS_CODE Character(20) VARCHAR2(20) NOT NULL A code that describes the type of transaction submitted by the bank via report or flat file.
    18 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.

    Prompt Table: BSP_TXN_CODE_VW

    19 AVAILABLE_BAL Signed Number(28,3) DECIMAL(26,3) NOT NULL Available Balance
    20 FLOAT1 Signed Number(28,3) DECIMAL(26,3) NOT NULL One Day Float Balance
    21 FLOAT2 Signed Number(28,3) DECIMAL(26,3) NOT NULL Two or More Days Float Balance
    22 RECON_REF_ID_01 Character(25) VARCHAR2(25) NOT NULL The transaction reference identifier on the inbound bank statement.
    23 RECON_REF_ID_02 Character(25) VARCHAR2(25) NOT NULL The transaction reference identifier on the inbound bank statement.
    24 RECON_REF_ID_03 Character(25) VARCHAR2(25) NOT NULL The transaction reference identifier on the inbound bank statement.
    25 RECON_REF_ID_04 Character(25) VARCHAR2(25) NOT NULL The transaction reference identifier on the inbound bank statement.
    26 RECON_REF_ID_05 Character(25) VARCHAR2(25) NOT NULL The transaction reference identifier on the inbound bank statement.
    27 RECON_REF_ID_06 Character(25) VARCHAR2(25) NOT NULL The transaction reference identifier on the inbound bank statement.
    28 RECON_REF_ID_07 Character(25) VARCHAR2(25) NOT NULL The transaction reference identifier on the inbound bank statement.
    29 RECON_REF_ID_08 Character(25) VARCHAR2(25) NOT NULL The transaction reference identifier on the inbound bank statement.
    30 RECON_REF_ID_09 Character(25) VARCHAR2(25) NOT NULL The transaction reference identifier on the inbound bank statement.
    31 RECON_REF_ID_10 Character(25) VARCHAR2(25) NOT NULL The transaction reference identifier on the inbound bank statement.
    32 DESCRLONG Long Character CLOB Long Description