EX_PMT_VSRCH_VW

(SQL View)
 Index Back

Supplier Payment Inquiry Srch


SELECT A.PYMNT_ID , A.BANK_SETID , A.BANK_CD , A.BANK_ACCT_KEY , A.CREATION_DT , B.NAME1 , A.REMIT_SETID , A.REMIT_VENDOR , A.NAME1 , %subrec(ADDRESS_SBR, A) , A.POST_STATUS_AP , A.PYMNT_STATUS , A.CANCEL_ACTION , A.CANCEL_DT , A.PYMNT_DT , A.PYMNT_AMT , A.CURRENCY_PYMNT , A.PYMNT_METHOD , A.PYMNT_ID_REF FROM PS_PAYMENT_TBL A , PS_VENDOR B WHERE A.REMIT_SETID = B.SETID AND A.REMIT_VENDOR = B.VENDOR_ID AND ((EXISTS ( SELECT 'X' FROM PS_EX_SHEET_PYMNT C WHERE A.REMIT_SETID = C.VENDOR_SETID AND A.REMIT_VENDOR = C.VENDOR_ID AND A.PYMNT_ID = C.PYMNT_ID AND A.BANK_SETID = C.BANK_SETID AND A.BANK_CD = C.BANK_CD AND A.BANK_ACCT_KEY = C.BANK_ACCT_KEY)) OR (EXISTS ( SELECT 'X' FROM PS_EX_ADVNCE_PYMNT D WHERE A.REMIT_SETID = D.VENDOR_SETID AND A.REMIT_VENDOR = D.VENDOR_ID AND A.PYMNT_ID = D.PYMNT_ID AND A.BANK_SETID = D.BANK_SETID AND A.BANK_CD = D.BANK_CD AND A.BANK_ACCT_KEY = D.BANK_ACCT_KEY)))

  • Related Language Record: EX_PMTVSRCH_L_V

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 PYMNT_ID Character(10) VARCHAR2(10) NOT NULL Payment Number
    2 SETID Character(5) VARCHAR2(5) NOT NULL SetID

    Default Value: OPR_DEF_TBL_FS.SETID

    Prompt Table: SP_SETID_NONVW

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

    Prompt Table: EX_PYFRM_BNK_VW

    4 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

    Prompt Table: EX_PYFRM_ACC_VW

    5 CREATION_DT Date(10) DATE Creation Date
    6 VENDOR_NAME1 Character(40) VARCHAR2(40) NOT NULL Vendor Name1
    7 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID

    Default Value: OPR_DEF_TBL_FS.SETID

    Prompt Table: SP_SETID_NONVW

    8 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

    Prompt Table: EX_VND_PRMPT_VW

    9 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    10 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

    Prompt Table: COUNTRY_TBL

    11 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
    12 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
    13 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
    14 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
    15 CITY Character(30) VARCHAR2(30) NOT NULL City
    16 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
    17 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
    18 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
    AB=House Boat
    WW=Trailer
    19 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
    20 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
    21 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
    22 COUNTY Character(30) VARCHAR2(30) NOT NULL County
    23 STATE Character(6) VARCHAR2(6) NOT NULL State

    Prompt Table: %EDIT_STATE

    24 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    25 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
    26 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

    Y/N Table Edit

    27 POST_STATUS_AP Character(1) VARCHAR2(1) NOT NULL Post Status
    N=Payment Not Applied
    P=Posted
    U=Unposted
    28 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
    29 CANCEL_ACTION Character(1) VARCHAR2(1) NOT NULL Cancel Action
    C=Do Not Reissue/Close Liability
    H=Re-Open Voucher(s)/Put on Hold
    N=No Cancel Action
    P=Processed
    R=Re-Open Voucher(s)/Re-Issue
    S=Stale Dated Check
    W=Escheated
    30 CANCEL_DT Date(10) DATE Date Cancelled
    31 PYMNT_DT Date(10) DATE Payment Date
    32 PYMNT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Payment Amount
    33 CURRENCY_PYMNT Character(3) VARCHAR2(3) NOT NULL Payment Currency
    34 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
    35 PYMNT_ID_REF Character(20) VARCHAR2(20) NOT NULL Payment Reference