AP_WC_PPYAVL_VW

(SQL View)
 Index Back

AP Workcenter Available Prepay

AP Workcenter Prepayments Available

SELECT A.BUSINESS_UNIT , A.VOUCHER_ID , A.VENDOR_SETID , A.VENDOR_ID , B.VENDOR_NAME_SHORT , B.NAME1 , A.INVOICE_ID , A.INVOICE_DT , A.GROSS_AMT , A.TXN_CURRENCY_CD , SUM(C.AVAILABLE_AMT) , A.TXN_CURRENCY_CD , A.OPRID , A.OPRID_LAST_UPDT , A.ORIGIN , A.VOUCHER_STYLE , A.VCHR_SRC , A.ENTERED_DT FROM PS_VOUCHER A , PS_VENDOR B , PS_VCHR_PPAY_XREF C , PS_PYMNT_VCHR_XREF D WHERE A.VENDOR_SETID = B.SETID AND A.VENDOR_ID = B.VENDOR_ID AND A.VOUCHER_STYLE = 'PPAY' AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.VOUCHER_ID = C.VOUCHER_ID AND C.PPAY_SEQNUM = ( SELECT MAX(CC.PPAY_SEQNUM) FROM PS_VCHR_PPAY_XREF CC WHERE CC.BUSINESS_UNIT = C.BUSINESS_UNIT AND CC.VOUCHER_ID = C.VOUCHER_ID AND CC.PYMNT_CNT = C.PYMNT_CNT) AND C.PREPAID_STATUS = 'P' AND C.AVAILABLE_AMT <> 0 AND A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.VOUCHER_ID = D.VOUCHER_ID AND C.PYMNT_CNT = D.PYMNT_CNT AND D.PYMNT_SELCT_STATUS = 'P' GROUP BY A.BUSINESS_UNIT, A.VOUCHER_ID, A.VENDOR_SETID, A.VENDOR_ID, B.VENDOR_NAME_SHORT, B.NAME1, A.INVOICE_ID, A.INVOICE_DT, A.GROSS_AMT, A.TXN_CURRENCY_CD, A.OPRID, A.OPRID_LAST_UPDT , A.ORIGIN , A.VOUCHER_STYLE , A.VCHR_SRC, A.ENTERED_DT

  • Related Language Record: AP_WC_PPYAVL_LV

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    2 VOUCHER_ID Character(8) VARCHAR2(8) NOT NULL Voucher ID
    3 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    4 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
    5 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name
    6 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    7 INVOICE_ID Character(30) VARCHAR2(30) NOT NULL Invoice Number
    8 INVOICE_DT Date(10) DATE Invoice Date
    9 GROSS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Invoice Amount
    10 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
    11 AVAILABLE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Prepayment Available Amount
    12 CURRENCY_PYMNT Character(3) VARCHAR2(3) NOT NULL Payment Currency
    13 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    14 OPRID_LAST_UPDT Character(30) VARCHAR2(30) NOT NULL Last User to Update
    15 ORIGIN Character(3) VARCHAR2(3) NOT NULL Origin
    16 VOUCHER_STYLE Character(4) VARCHAR2(4) NOT NULL Voucher Style
    ADJ=Adjustments
    AMR=Amortization Voucher
    CLBK=Claim Voucher
    CORR=Reversal Voucher
    JRNL=Journal Voucher
    PPAY=Prepaid Voucher
    REG=Regular Voucher
    RGTR=Register Voucher
    SGLP=Single Payment Voucher
    THRD=Third Party Voucher
    TMPL=Template Voucher
    17 VCHR_SRC Character(4) VARCHAR2(4) NOT NULL Voucher Source
    18 ENTERED_DT Date(10) DATE Entered on