EM_BUYPMTINQ_VW

(SQL View)
 Index Back

Payments keyed by Supplier


SELECT DISTINCT A.REMIT_SETID , A.REMIT_VENDOR , A.EMPLID , A.BANK_SETID , A.BANK_CD , A.BANK_ACCT_KEY , A.PYMNT_METHOD , A.PYMNT_ID_REF , A.PYMNT_ID , A.BANK_ACCOUNT_NUM , A.NAME1 , %subrec(address_sbr, A) , A.PYMNT_DT , A.CREATION_DT , SEC.OPRID , A.PYMNT_AMT , A.CURRENCY_PYMNT , A.PYMNT_HANDLING_CD , A.PYMNT_STATUS , A.PYMNT_RECONCILE_DT , A.CANCEL_ACTION , A.CANCEL_DT , A.ACCOUNTING_DT , A.POST_STATUS_AP , A.PROCESS_INSTANCE , A.PAY_CYCLE , A.PAY_CYCLE_SEQ_NUM , A.RECON_TYPE , A.DOC_TYPE , A.DOC_SEQ_NBR , A.DOC_SEQ_DATE , A.DOC_SEQ_STATUS , A.PREFERRED_LANGUAGE , A.PYMNT_TYPE , BNK.DESCR , A.STL_THROUGH , A.BATCH_ID , A.PMT_ID , A.BANK_REF_ID , A.PMT_PYMNT_STATUS FROM PS_PAYMENT_TBL A , PS_BANK_CD_TBL BNK , PS_PYMNT_VCHR_XREF XREF , PS_SEC_BU_OPR SEC WHERE A.BANK_SETID = XREF.BANK_SETID AND A.BANK_CD = XREF.BANK_CD AND A.BANK_ACCT_KEY = XREF.BANK_ACCT_KEY AND A.PYMNT_ID = XREF.PYMNT_ID AND A.PYMNT_STATUS <> 'X' AND SEC.BUSINESS_UNIT = XREF.BUSINESS_UNIT AND BNK.SETID = A.BANK_SETID AND BNK.BANK_CD = A.BANK_CD

  • Related Language Record: EM_BUYPMTINQ_LG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 REMIT_SETID Character(5) VARCHAR2(5) NOT NULL Remit SetID
    2 REMIT_VENDOR Character(10) VARCHAR2(10) NOT NULL Remit Vendor
    3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    4 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

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

    Prompt Table: BANK_CD_TBL

    6 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
    7 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
    8 PYMNT_ID_REF Character(20) VARCHAR2(20) NOT NULL Payment Reference
    9 PYMNT_ID Character(10) VARCHAR2(10) NOT NULL Payment Number
    10 BANK_ACCOUNT_NUM Character(35) VARCHAR2(35) NOT NULL The literal unique identifier associated with a given bank/counterparty account.
    11 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1

    Default Value: VENDOR_ADDR.NAME1

    12 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

    Prompt Table: COUNTRY_TBL

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

    Prompt Table: %EDIT_STATE

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

    Y/N Table Edit

    29 PYMNT_DT Date(10) DATE Payment Date

    Default Value: %date

    30 CREATION_DT Date(10) DATE Creation Date
    31 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    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_HANDLING_CD Character(2) VARCHAR2(2) NOT NULL Payment Handling
    35 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
    36 PYMNT_RECONCILE_DT Date(10) DATE Date Reconciled
    37 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
    38 CANCEL_DT Date(10) DATE Date Cancelled
    39 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
    40 POST_STATUS_AP Character(1) VARCHAR2(1) NOT NULL Post Status
    N=Payment Not Applied
    P=Posted
    U=Unposted

    Default Value: U

    41 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
    42 PAY_CYCLE Character(6) VARCHAR2(6) NOT NULL Pay Cycle
    43 PAY_CYCLE_SEQ_NUM Number(6,0) INTEGER NOT NULL Pay Cycle Sequence Number
    44 RECON_TYPE Character(1) VARCHAR2(1) NOT NULL Represents a series of options as to how a treasury wishes to perform reconciliation for a given bank
    A=Automatically Reconciled
    B=Balance System(s) to BankTran
    F=Forced to Reconcile
    M=Manually Reconciled
    S=Semi Manual
    U=Unreconciled
    45 DOC_TYPE Character(8) VARCHAR2(8) NOT NULL Specifies the business purpose of a financial transaction in countries that require all financial transactions to be tracked as "documents". A Document Type is associated with one and only one Journal Code.
    46 DOC_SEQ_NBR Character(12) VARCHAR2(12) NOT NULL Specifies the sequence number assigned to each financial transaction (a document). The sequence number may be manually entered or system-generated.
    47 DOC_SEQ_DATE Date(10) DATE Specifies the date that a document sequence number is assigned to a document or the date the document was created.
    48 DOC_SEQ_STATUS Character(1) VARCHAR2(1) NOT NULL A flag that indicates a document sequencing error. If there is no error the field is blank and document is considered valid for posting.
    1=Document Sequencing Disabled
    A=GL BU Undefined
    B=GL BU Blank
    C=Accounting Date blank
    D=Doc Type Blank
    E=Jrnl Type, Code, Doc Undefine
    F=Sequence Range Undefine
    G=Max. Sequence Number Reached
    H=Manual Nbr for Auto Doc Type
    I=No Manual Number Entered
    J=Duplicate Manual Number
    K=Invalid Ledger
    L=Invalid Ledger Group
    N=No Status
    49 PREFERRED_LANGUAGE Character(3) VARCHAR2(3) NOT NULL Preferred Language
    ARA=Arabic
    CFR=Canadian French
    CZE=Czech
    DAN=Danish
    DUT=Dutch
    E=English
    ENG=English
    ESP=Spanish
    F=French
    FIN=Finnish
    FRA=French
    GER=German
    HUN=Hungarian
    INE=International English
    ITA=Italian
    JPN=Japanese
    KOR=Korean
    NOR=Norwegian
    POL=Polish
    POR=Portuguese
    RUS=Russian
    SVE=Swedish
    THA=Thai
    UKE=UK English
    ZHS=Simplified Chinese
    ZHT=Traditional Chinese
    50 PYMNT_TYPE Character(1) VARCHAR2(1) NOT NULL Document Type
    E=Express Payment
    M=Manual Payment
    R=Regular payment
    W=Withholding Payment
    Z=Zero Payment Check
    51 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    52 STL_THROUGH Character(2) VARCHAR2(2) NOT NULL Settle through
    01=Financial Gateway
    02=Pay Cycle
    53 BATCH_ID Character(18) VARCHAR2(18) NOT NULL Batch ID
    54 PMT_ID Character(15) VARCHAR2(15) NOT NULL Payment id
    55 BANK_REF_ID Character(16) VARCHAR2(16) NOT NULL A reference identifier associated with a given bank/counterparty transaction.
    56 PMT_PYMNT_STATUS Character(2) VARCHAR2(2) NOT NULL Settlement Status
    C=Canceled
    CM=Cancelled with Message
    E=Error
    H=Hold
    L=Loaded
    N=None
    P=Paid
    PC=Pending Cancellation
    R=Received by Bank
    S=Send to Bank
    SC=In Process