EM_SPOUTBAL2_VW

(SQL View)
 Index Back

Customer Outstanding Bal-PPAY

Seller Bill Presentment / Customer Outstanding Balance view 2 - PrePay

SELECT V.BUSINESS_UNIT , SEC.OPRID , BU.DESCRSHORT , 'PPAY' , V.TXN_CURRENCY_CD , RT.TO_CUR , RT.RT_TYPE , RT.RATE_MULT , RT.RATE_DIV , P.PYMNT_HOLD , COUNT(*) , 0 , %DecMult(SUM(PP.AVAILABLE_AMT), -1) , 0 FROM PS_VOUCHER V , PS_PYMNT_VCHR_XREF P , PS_VCHR_PPAY_XREF PP , PS_RT_RATE_TBL RT , PS_VENDOR_USER SEC , PS_BUS_UNIT_TBL_FS BU WHERE SEC.SETID=V.VENDOR_SETID AND SEC.VENDOR_ID = V.VENDOR_ID AND V.BUSINESS_UNIT = BU.BUSINESS_UNIT AND V.BUSINESS_UNIT = P.BUSINESS_UNIT AND V.VOUCHER_ID = P.VOUCHER_ID AND V.BUSINESS_UNIT = PP.BUSINESS_UNIT AND V.VOUCHER_ID = PP.VOUCHER_ID AND PP.SEQNUM = 1 AND V.VOUCHER_STYLE = 'PPAY' AND P.PREPAID_STATUS = 'P' AND P.DUE_DT IS NOT NULL AND RT.RT_RATE_INDEX = ( SELECT RT_RATE_INDEX FROM PS_INSTALLATION_EM) AND RT.TERM = 0 AND RT.FROM_CUR = V.TXN_CURRENCY_CD AND RT.EFFDT = ( SELECT MAX(EFFDT) FROM PS_RT_RATE_TBL RT1 WHERE RT1.RT_RATE_INDEX = RT.RT_RATE_INDEX AND RT1.TERM = RT.TERM AND RT1.RT_TYPE = RT.RT_TYPE AND RT1.FROM_CUR = RT.FROM_CUR AND RT1.TO_CUR = RT.TO_CUR AND RT1.EFFDT <= %CurrentDateIn) AND RT.RATE_DIV <>0 GROUP BY V.BUSINESS_UNIT , SEC.OPRID , BU.DESCRSHORT , V.TXN_CURRENCY_CD , RT.TO_CUR , RT.RT_TYPE , RT.RATE_MULT , RT.RATE_DIV, P.PYMNT_HOLD

  • Related Language Record: EM_SPOUTBL1_LNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    2 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    3 EM_BU_NAME_SHORT Character(10) VARCHAR2(10) NOT NULL Buyer Name
    4 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
    5 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
    6 TO_CUR Character(3) VARCHAR2(3) NOT NULL To Currency Code
    7 RT_TYPE Character(5) VARCHAR2(5) NOT NULL Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.
    8 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
    9 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
    10 PYMNT_HOLD Character(1) VARCHAR2(1) NOT NULL Hold Payment
    N=No
    Y=Yes
    11 EM_NUM_LN_TOTAL Number(5,0) INTEGER NOT NULL EBPP-Added 2000/10/01-Last Mod 2000/10/19 by Matthias Davet Total # of lines
    12 EM_TOTAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL EBPP-Added 2000/10/01-Last Mod 2000/10/19 by Matthias Davet Total amount
    13 EM_PREPAY_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Prepayment Unapplied
    14 EM_NET_BAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL EBPP-Added 2000/10/01-Last Mod 2000/10/19 by Matthias Davet Total amount