EM_VCHR_SRCH

(SQL View)
 Index Back

EBPP Voucher Approval Search

This SQL view was cloned from the voucher search record. The voucher search record did not implement any kind of row-level security. This new search record (SQL view) joins to the User BU Permissions table ( ), and only returns appropriate rows. This table is used to control eSettlements Voucher Approval pages.

SELECT DISTINCT a.business_unit , c.oprid , a.voucher_id , a.invoice_id , a.gross_amt , a.txn_currency_cd , a.invoice_dt , a.vendor_setid , b.vendor_name_short , a.vendor_id , b.name1 , a.vndr_loc , a.vchr_apprvl_flg , d.rolename FROM PS_VOUCHER a , ps_vendor b , ps_sec_bu_opr c , ps_em_vchr_apr_dat d , ps_em_es_vchr_vw e WHERE a.vendor_setid = b.setid AND a.vendor_id = b.vendor_id AND a.appr_status IN ('D','P') AND a.entry_status ='P' AND a.vchr_apprvl_flg = 'S' AND a.match_status_vchr IN ('M','N','O') AND a.close_status <> 'C' AND a.process_man_close <> 'Y' AND a.business_unit = c.business_unit AND a.business_unit = d.business_unit AND a.voucher_id = d.voucher_id AND d.current_status='Y' AND a.business_unit = e.business_unit AND a.voucher_id = e.voucher_id UNION SELECT DISTINCT aa.business_unit , cc.oprid , aa.voucher_id , aa.invoice_id , aa.gross_amt , aa.txn_currency_cd , aa.invoice_dt , aa.vendor_setid , bb.vendor_name_short , aa.vendor_id , bb.name1 , aa.vndr_loc , aa.vchr_apprvl_flg , ' ' FROM PS_VOUCHER aa , ps_vendor bb , ps_sec_bu_opr cc , ps_em_vchr_apru_vw dd , ps_em_es_vchr_vw ee WHERE aa.vendor_setid = bb.setid AND aa.vendor_id = bb.vendor_id AND aa.appr_status IN ('D','P') AND aa.entry_status ='P' AND aa.vchr_apprvl_flg = 'W' AND aa.match_status_vchr IN ('M','N','O') AND aa.close_status <> 'C' AND aa.process_man_close <> 'Y' AND aa.business_unit = cc.business_unit AND aa.business_unit = dd.business_unit AND aa.voucher_id = dd.voucher_id AND dd.current_status='Y' AND cc.oprid = dd.oprid AND aa.business_unit = ee.business_unit AND aa.voucher_id = ee.voucher_id

  • Related Language Record: EM_VCHR_SRCH_LG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT

    Prompt Table: SP_BU_AP_NONVW

    2 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    3 VOUCHER_ID Character(8) VARCHAR2(8) NOT NULL Voucher ID

    Default Value: NEXT

    4 INVOICE_ID Character(30) VARCHAR2(30) NOT NULL Invoice Number
    5 GROSS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Invoice Amount
    6 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
    7 INVOICE_DT Date(10) DATE Invoice Date
    8 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID

    Default Value: VENDOR.SETID

    9 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name
    10 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

    Default Value: VENDOR_SHRT_VW.VENDOR_ID

    Prompt Table: VENDOR_PRIM_VW

    11 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    12 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location

    Prompt Table: EM_VNDR_LOC_VW

    13 VCHR_APPRVL_FLG Character(1) VARCHAR2(1) NOT NULL Voucher Approval Flag
    D=Default from Higher Level
    P=Pre-Approved
    S=Virtual Approver
    W=Approval Framework
    14 ROLENAME Character(30) VARCHAR2(30) NOT NULL The name of a Role in the Role Definition Table (see PSROLEDEFN).