EM_SELPMTINQ_VW

(SQL View)
 Index Back

EBPP-Last 10 Payments

EBPP-Created 2000/10/22-Last Mod 2000/10/22 by Matthias Davet This view selects the last 10 payments

SELECT DISTINCT D.OPRID ,RMT.SETID , RMT.DESCR , RMT.BENEFICIARY_BANK , RMT.BNK_ID_NBR , RMT.BRANCH_ID , RMT.BENEF_BRANCH , RMT.BANK_ACCOUNT_NUM , RMT.CHECK_DIGIT , C.PYMNT_ID , C.PYMNT_ID_REF , C.PYMNT_DT , C.CURRENCY_PYMNT , C.PYMNT_STATUS , A.REMIT_VENDOR , A.VNDR_LOC , VEN.NAME1 , A.BANK_SETID , A.BANK_CD , A.BANK_ACCT_KEY , A.PYMNT_METHOD , A.BUSINESS_UNIT , FS.DESCRSHORT , C.PYMNT_AMT , C.BANK_REF_ID FROM PS_PYMNT_VCHR_XREF A , PS_VOUCHER B , PS_PAYMENT_TBL C , PS_VENDOR_USER D , PS_VNDR_BANK_ACCT RMT , PS_VENDOR VEN , PS_BUS_UNIT_TBL_FS FS WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.VOUCHER_ID = B.VOUCHER_ID AND A.DUE_DT IS NOT NULL AND C.BANK_SETID = A.BANK_SETID AND C.BANK_CD = A.BANK_CD AND C.BANK_ACCT_KEY = A.BANK_ACCT_KEY AND C.PYMNT_ID = A.PYMNT_ID AND D.SETID = B.VENDOR_SETID AND D.VENDOR_ID = B.VENDOR_ID AND C.PYMNT_STATUS IN ('P','S','V') AND RMT.SETID = A.REMIT_SETID AND RMT.VENDOR_ID = A.REMIT_VENDOR AND RMT.VNDR_LOC = A.VNDR_LOC AND RMT.BANK_ACCT_SEQ_NBR = A.BANK_ACCT_SEQ_NBR AND RMT.EFFDT = ( SELECT MAX(EFFDT) FROM PS_VNDR_BANK_ACCT RMT1 WHERE RMT1.SETID = RMT.SETID AND RMT1.VENDOR_ID = RMT.VENDOR_ID AND RMT1.VNDR_LOC = RMT.VNDR_LOC AND RMT1.BANK_ACCT_SEQ_NBR = RMT.BANK_ACCT_SEQ_NBR AND RMT1.EFFDT <= B.ENTERED_DT) AND VEN.SETID = A.REMIT_SETID AND VEN.VENDOR_ID = A.REMIT_VENDOR AND FS.BUSINESS_UNIT = A.BUSINESS_UNIT

  • Related Language Record: EM_SELPMTINQ_LN

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    2 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    3 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    4 BENEFICIARY_BANK Character(30) VARCHAR2(30) NOT NULL The bank/counterparty associated with a given beneficiary.
    5 BNK_ID_NBR Character(20) VARCHAR2(20) NOT NULL A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
    6 BRANCH_ID Character(10) VARCHAR2(10) NOT NULL The local branch office identifier associated with a given bank/counterparty.
    7 BENEF_BRANCH Character(30) VARCHAR2(30) NOT NULL Beneficiary Branch field
    8 BANK_ACCOUNT_NUM Character(35) VARCHAR2(35) NOT NULL The literal unique identifier associated with a given bank/counterparty account.
    9 CHECK_DIGIT Character(2) VARCHAR2(2) NOT NULL Check Digit
    10 PYMNT_ID Character(10) VARCHAR2(10) NOT NULL Payment Number
    11 PYMNT_ID_REF Character(20) VARCHAR2(20) NOT NULL Payment Reference
    12 PYMNT_DT Date(10) DATE Payment Date
    13 CURRENCY_PYMNT Character(3) VARCHAR2(3) NOT NULL Payment Currency
    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 REMIT_VENDOR Character(10) VARCHAR2(10) NOT NULL Remit Vendor
    16 REMIT_LOC Character(10) VARCHAR2(10) NOT NULL Remit to Location

    Prompt Table: EM_VNDR_LOC_VW

    17 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1

    Prompt Table: EM_RM_VENDOR_VW

    18 BANK_SETID Character(5) VARCHAR2(5) NOT NULL The PeopleSoft tableset ID associated with a given bank/counterparty.

    Default Value: OPR_BANK_VW_AP.BANK_SETID

    19 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code

    Prompt Table: BANK_CD_TBL

    20 BANK_ACCT_KEY Character(4) VARCHAR2(4) NOT NULL A user defined unique identifier that facilitates the identification of a given account with a given bank
    21 PYMNT_METHOD Character(3) VARCHAR2(3) NOT NULL Payment Method
    ACH=Automated Clearing House
    BEF=Draft - Customer EFT
    BOO=Draft - Customer Initiated
    CHK=System Check
    D=Deposit
    DD=Direct Debit
    DFT=Draft - Supplier Initiated
    DRA=Draft
    EFT=Electronic Funds Transfer
    GE=Giro - EFT
    GM=Giro - Manual
    LC=Letter of Credit
    MAN=Manual Check
    TRW=Treasury Wire
    WIR=Wire Transfer
    22 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    23 EM_BU_NAME_SHORT Character(10) VARCHAR2(10) NOT NULL Buyer Short Name
    24 PYMNT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Payment Amount
    25 BANK_REF_ID Character(16) VARCHAR2(16) NOT NULL A reference identifier associated with a given bank/counterparty transaction.