EM_INVAPRDAT_VW (SQL View)	Index Back
My Pending Invoice view This view is used by eSettlements Review Invoices to filter invoices requiring MY approval.

EM_INVAPRDAT_VW

(SQL View)

My Pending Invoice view

This view is used by eSettlements Review Invoices to filter invoices requiring MY approval.

SELECT a.business_unit , a.voucher_id , bu.descrshort , a.invoice_id , b.setid , b.setid , b.vendor_id , b.name1 , d.vndr_loc , a.invoice_dt , d.txn_currency_cd , a.gross_amt , a.dscnt_amt , ' ' , 0 , t.descrshort , d.due_dt , d.pymnt_hold , a.entered_dt , d.pymnt_selct_status , a.appr_status , a.match_status_vchr , a.voucher_style , t.setid , a.pymnt_terms_cd , A.PYMNT_CNT_TOTAL , d2.rolename , ' ' , a.rate_div , a.rt_type , a.rate_mult , a.cur_rt_source FROM PS_VOUCHER a , ps_vendor b , ps_pymnt_vchr_xref d , ps_bus_unit_tbl_fs bu , ps_pymt_trms_hdr t , ps_em_vchr_apr_dat d2 WHERE a.vendor_setid = b.setid AND a.vendor_id = b.vendor_id AND a.business_unit = d.business_unit AND a.voucher_id = d.voucher_id AND a.business_unit = bu.business_unit AND a.business_unit = d2.business_unit AND a.voucher_id = d2.voucher_id AND d2.current_status='Y' AND a.pymnt_terms_cd = t.pymnt_terms_cd 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 t.setid = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE REC_GROUP_ID = 'FS_14' AND RECNAME = 'PYMT_TRMS_HDR' AND SETCNTRLVALUE = a.business_unit) AND d.due_dt IS NOT NULL UNION ALL SELECT DISTINCT a.business_unit , a.voucher_id , bu.descrshort , a.invoice_id , b.setid , b.setid , b.vendor_id , b.name1 , d.vndr_loc , a.invoice_dt , d.txn_currency_cd , a.gross_amt , a.dscnt_amt , ' ' , 0 , t.descrshort , d.due_dt , d.pymnt_hold , a.entered_dt , d.pymnt_selct_status , a.appr_status , a.match_status_vchr , a.voucher_style , t.setid , a.pymnt_terms_cd , A.PYMNT_CNT_TOTAL , ' ' , d2.oprid , a.rate_div , a.rt_type , a.rate_mult , a.cur_rt_source FROM PS_VOUCHER a , ps_vendor b , ps_pymnt_vchr_xref d , ps_bus_unit_tbl_fs bu , ps_pymt_trms_hdr t , ps_em_vchr_apru_vw d2 WHERE a.vendor_setid = b.setid AND a.vendor_id = b.vendor_id AND a.business_unit = d.business_unit AND a.voucher_id = d.voucher_id AND a.business_unit = bu.business_unit AND a.business_unit = d2.business_unit AND a.voucher_id = d2.voucher_id AND d2.current_status='Y' AND a.pymnt_terms_cd = t.pymnt_terms_cd AND a.entry_status ='P' AND a.vchr_apprvl_flg = 'W' AND a.match_status_vchr IN ('M','N','O') AND a.close_status <> 'C' AND a.process_man_close <> 'Y' AND t.setid = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE REC_GROUP_ID = 'FS_14' AND RECNAME = 'PYMT_TRMS_HDR' AND SETCNTRLVALUE = a.business_unit) AND d.due_dt IS NOT NULL

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EM_BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Buyer

2 VOUCHER_ID Character(8) VARCHAR2(8) NOT NULL Voucher ID

3 EM_BU_NAME_SHORT Character(10) VARCHAR2(10) NOT NULL Buyer Short Name

4 INVOICE_ID Character(30) VARCHAR2(30) NOT NULL Invoice Number
Prompt Table: EM_INVOI_USR_VW

5 SETID Character(5) VARCHAR2(5) NOT NULL SetID

6 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID

7 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

8 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1

9 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
Prompt Table: EM_VNDR_LOC_VW

10 INVOICE_DT Date(10) DATE Invoice Date

11 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency

12 GROSS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Invoice Amount

13 DSCNT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Discount Amount

14 CURRENCY_PYMNT Character(3) VARCHAR2(3) NOT NULL Payment Currency

15 PAID_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Paid Amount

16 EM_TERM_DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Payment Term

17 DUE_DT Date(10) DATE Specifies the date that a transaction is due. It is a generic field used in multiple PeopleSoft applications including Receivables Payables and Purchasing. For example it represents the date that payment is due for a voucher or the date that a scheduled shipment is due to be received.

18 PYMNT_HOLD Character(1) VARCHAR2(1) NOT NULL Hold Payment
N=No
Y=Yes

19 ENTERED_DT Date(10) DATE Entered on

20 PYMNT_SELCT_STATUS Character(1) VARCHAR2(1) NOT NULL Payment Selection Status
C=Closed
D=Negative Voucher Sum
E=Externally Paid
F=Federal Sanctions Error
I=In Process - EFT
N=Not Selected for Payment
O=Financial Sanctions Review
P=Paid
R=Requested for Payment
S=Settlement Payment
V=Convert Error
X=Canceled
Y=Prepaid

21 APPR_STATUS Character(1) VARCHAR2(1) NOT NULL Approval status flag used on appr_inst_log and derived records to track virtual approver status
A=Approved
D=Denied
P=Pending

22 MATCH_STATUS_VCHR Character(1) VARCHAR2(1) NOT NULL Match Status
C=Overridden - Credit Note
D=Match Dispute
E=Match Exceptions Exist
M=Matched
N=Not Applicable
O=Manually Overridden
T=To Be Matched

23 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

24 SETID_PAYTRMS Character(5) VARCHAR2(5) NOT NULL Payment Terms Setid

25 PYMNT_TERMS_CD Character(5) VARCHAR2(5) NOT NULL Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers.

26 PYMNT_CNT_TOTAL Number(5,0) INTEGER NOT NULL Total Payment Count

27 ROLENAME Character(30) VARCHAR2(30) NOT NULL The name of a Role in the Role Definition Table (see PSROLEDEFN).

28 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

29 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor

30 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.

31 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier

32 CUR_RT_SOURCE Character(1) VARCHAR2(1) NOT NULL Exchange Rate Source
T=Use Exchange Rate Tables
U=User Specified

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EM_BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Buyer
2	VOUCHER_ID	Character(8)	VARCHAR2(8) NOT NULL	Voucher ID
3	EM_BU_NAME_SHORT	Character(10)	VARCHAR2(10) NOT NULL	Buyer Short Name
4	INVOICE_ID	Character(30)	VARCHAR2(30) NOT NULL	Invoice Number Prompt Table: EM_INVOI_USR_VW
5	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
6	VENDOR_SETID	Character(5)	VARCHAR2(5) NOT NULL	Vendor SetID
7	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier
8	NAME1	Character(40)	VARCHAR2(40) NOT NULL	Name 1
9	VNDR_LOC	Character(10)	VARCHAR2(10) NOT NULL	Vendor Location Prompt Table: EM_VNDR_LOC_VW
10	INVOICE_DT	Date(10)	DATE	Invoice Date
11	TXN_CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Transaction Currency
12	GROSS_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Gross Invoice Amount
13	DSCNT_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Discount Amount
14	CURRENCY_PYMNT	Character(3)	VARCHAR2(3) NOT NULL	Payment Currency
15	PAID_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Paid Amount
16	EM_TERM_DESCRSHORT	Character(10)	VARCHAR2(10) NOT NULL	Payment Term
17	DUE_DT	Date(10)	DATE	Specifies the date that a transaction is due. It is a generic field used in multiple PeopleSoft applications including Receivables Payables and Purchasing. For example it represents the date that payment is due for a voucher or the date that a scheduled shipment is due to be received.
18	PYMNT_HOLD	Character(1)	VARCHAR2(1) NOT NULL	Hold Payment N=No Y=Yes
19	ENTERED_DT	Date(10)	DATE	Entered on
20	PYMNT_SELCT_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Payment Selection Status C=Closed D=Negative Voucher Sum E=Externally Paid F=Federal Sanctions Error I=In Process - EFT N=Not Selected for Payment O=Financial Sanctions Review P=Paid R=Requested for Payment S=Settlement Payment V=Convert Error X=Canceled Y=Prepaid
21	APPR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Approval status flag used on appr_inst_log and derived records to track virtual approver status A=Approved D=Denied P=Pending
22	MATCH_STATUS_VCHR	Character(1)	VARCHAR2(1) NOT NULL	Match Status C=Overridden - Credit Note D=Match Dispute E=Match Exceptions Exist M=Matched N=Not Applicable O=Manually Overridden T=To Be Matched
23	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
24	SETID_PAYTRMS	Character(5)	VARCHAR2(5) NOT NULL	Payment Terms Setid
25	PYMNT_TERMS_CD	Character(5)	VARCHAR2(5) NOT NULL	Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers.
26	PYMNT_CNT_TOTAL	Number(5,0)	INTEGER NOT NULL	Total Payment Count
27	ROLENAME	Character(30)	VARCHAR2(30) NOT NULL	The name of a Role in the Role Definition Table (see PSROLEDEFN).
28	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
29	RATE_DIV	Number(16,8)	DECIMAL(15,8) NOT NULL	Rate Divisor
30	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.
31	RATE_MULT	Signed Number(17,8)	DECIMAL(15,8) NOT NULL	Rate Multiplier
32	CUR_RT_SOURCE	Character(1)	VARCHAR2(1) NOT NULL	Exchange Rate Source T=Use Exchange Rate Tables U=User Specified