EM_BUY_OUTBL_VW

(SQL View)
Index Back

MP - Seller Balances View

MP - Seller B

SELECT VEN.VENDOR_ID , SEC.OPRID , V.BUSINESS_UNIT , VEN.NAME1 , V.VNDR_LOC , VEN.VENDOR_NAME_SHORT , V.VOUCHER_STYLE , P.TXN_CURRENCY_CD , P.PYMNT_HOLD , RT.TO_CUR , RT.RT_TYPE , RT.RATE_MULT , RT.RATE_DIV , COUNT(*) , SUM(P.PYMNT_GROSS_AMT) , 0 , 0 FROM PS_VOUCHER V , PS_PYMNT_VCHR_XREF P , PS_RT_RATE_TBL RT , PS_SEC_BU_OPR SEC , PS_VENDOR VEN WHERE SEC.BUSINESS_UNIT=V.BUSINESS_UNIT AND (V.PROCESS_MAN_CLOSE = 'N' OR V.CLOSE_STATUS <> 'C') AND V.ENTRY_STATUS <> 'X' AND P.PYMNT_SELCT_STATUS NOT IN ('P','E','X') AND V.VENDOR_SETID = VEN.SETID AND V.VENDOR_ID = VEN.VENDOR_ID AND V.VOUCHER_ID = P.VOUCHER_ID AND V.BUSINESS_UNIT = P.BUSINESS_UNIT 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 = P.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 VEN.VENDOR_ID, SEC.OPRID, V.BUSINESS_UNIT, VEN.NAME1, V.VNDR_LOC, VEN.VENDOR_NAME_SHORT , P.TXN_CURRENCY_CD , P.PYMNT_HOLD, RT.TO_CUR , RT.RT_TYPE , RT.RATE_MULT , RT.RATE_DIV, V.VOUCHER_STYLE

  • Related Language Record: EM_BUY_OUTBLANG
  • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
    2 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    3 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    4 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    5 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
    6 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name
    7 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
    8 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
    9 PYMNT_HOLD Character(1) VARCHAR2(1) NOT NULL Hold Payment
    N=No
    Y=Yes
    10 TO_CUR Character(3) VARCHAR2(3) NOT NULL To Currency Code
    11 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.
    12 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
    13 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
    14 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
    15 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
    16 EM_PREPAY_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Prepayment Unapplied
    17 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