AP_DB_VCHAMT_VW

(SQL View)
 Index Back

SELECT A.BUSINESS_UNIT , A.VOUCHER_ID , A.INVOICE_DT , C.SETID , C.VENDOR_ID , C.NAME1 , C.VENDOR_NAME_SHORT , A.VNDR_LOC , A.TXN_CURRENCY_CD , SUM(B.PYMNT_GROSS_AMT) , 0 FROM PS_VOUCHER A , PS_PYMNT_VCHR_XREF B , PS_VENDOR C WHERE A.VOUCHER_STYLE NOT IN ('PPAY', 'TMPL') AND (A.PROCESS_MAN_CLOSE = 'N' OR A.CLOSE_STATUS <> 'C') AND A.ENTRY_STATUS <> 'X' AND A.VENDOR_SETID = C.SETID AND A.VENDOR_ID = C.VENDOR_ID AND A.VOUCHER_ID = B.VOUCHER_ID AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND B.PYMNT_SELCT_STATUS NOT IN ('P', 'X', 'S') AND (B.NET_SELCT_STATUS <> 'S' OR B.NET_TXN_STATUS <> 'N') AND B.DUE_DT IS NOT NULL GROUP BY C.SETID, C.VENDOR_ID ,C.NAME1, C.VENDOR_NAME_SHORT, A.BUSINESS_UNIT, A.VOUCHER_ID, A.INVOICE_DT, A.VNDR_LOC, A.TXN_CURRENCY_CD UNION ALL SELECT A.BUSINESS_UNIT , A.VOUCHER_ID , A.INVOICE_DT , C.SETID , C.VENDOR_ID , C.NAME1 , C.VENDOR_NAME_SHORT , A.VNDR_LOC , A.TXN_CURRENCY_CD , 0 , SUM (B.AVAILABLE_AMT) FROM PS_VOUCHER A , PS_VCHR_PPAY_XREF B , PS_VENDOR C , PS_PYMNT_VCHR_XREF D WHERE A.VOUCHER_STYLE = 'PPAY' AND (A.PROCESS_MAN_CLOSE = 'N' OR A.CLOSE_STATUS <> 'C') AND A.ENTRY_STATUS <> 'X' AND A.VENDOR_SETID = C.SETID AND A.VENDOR_ID = C.VENDOR_ID AND A.VOUCHER_ID = B.VOUCHER_ID AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND B.PREPAID_STATUS = 'P' AND A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.VOUCHER_ID = D.VOUCHER_ID AND D.PYMNT_TYPE = 'R' AND D.PYMNT_SELCT_STATUS = 'P' GROUP BY C.SETID, C.VENDOR_ID , C.NAME1, C.VENDOR_NAME_SHORT, A.BUSINESS_UNIT, A.VOUCHER_ID, A.INVOICE_DT, A.VNDR_LOC, A.TXN_CURRENCY_CD

  • Related Language Record: AP_DB_VCHAMT_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 INVOICE_DT Date(10) DATE Invoice Date
    4 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    5 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
    6 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    7 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name
    8 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
    9 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
    10 GROSS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Invoice Amount
    11 PREPAID_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Prepaid Amount