EM_SP_VCH_IQ_VW (SQL View)	Index Back
Supplier Manage Bills View This view is used by eSettlements to retrieve all of the voucher inquiry panel rows.

EM_SP_VCH_IQ_VW

(SQL View)

Supplier Manage Bills View

This view is used by eSettlements to retrieve all of the voucher inquiry panel rows.

SELECT a.business_unit , sec.oprid , a.voucher_id , a.invoice_id , b.setid , b.vendor_id , d.vndr_loc , a.invoice_dt , d.txn_currency_cd , a.gross_amt , a.dscnt_amt , d.currency_pymnt , SUM(d.paid_amt) , d.due_dt , d.pymnt_hold , a.entered_dt , d.pymnt_selct_status , a.voucher_style , t.setid , a.pymnt_terms_cd , D.PAY_CYCLE , D.PAY_CYCLE_SEQ_NUM FROM PS_VOUCHER a , ps_vendor b , ps_vendor_user sec , ps_pymnt_vchr_xref d , ps_set_cntrl_group gp , ps_pymt_trms_hdr t WHERE sec.setid = a.vendor_setid AND sec.vendor_id = a.vendor_id AND 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 D.PYMNT_CNT = ( SELECT MIN(H.PYMNT_CNT) FROM PS_PYMNT_VCHR_XREF H WHERE H.BUSINESS_UNIT = D.BUSINESS_UNIT AND H.VOUCHER_ID = D.VOUCHER_ID) AND gp.setcntrlvalue = a.business_unit AND gp.rec_group_id = 'FS_14' 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 a.pymnt_terms_cd = t.pymnt_terms_cd AND (( a.process_man_close = 'N' OR a.close_status <> 'C') OR ( a.process_man_close <> 'N' AND a.close_status = 'C' AND d.pymnt_selct_status='E' ) ) AND a.entry_status = 'P' AND d.pymnt_type <> 'W' AND d.pymnt_selct_status <> 'X' AND d.due_dt IS NOT NULL AND ((a.VOUCHER_STYLE='PPAY' AND d.PREPAID_STATUS='P' ) OR a.VOUCHER_STYLE<>'PPAY') GROUP BY a.business_unit , sec.oprid , a.voucher_id , a.invoice_id , b.setid, b.vendor_id , d.vndr_loc, a.invoice_dt , d.txn_currency_cd , a.gross_amt , a.dscnt_amt , d.currency_pymnt , d.due_dt , d.pymnt_hold , a.entered_dt , d.pymnt_selct_status , a.voucher_style , t.setid , a.pymnt_terms_cd, D.PAY_CYCLE , D.PAY_CYCLE_SEQ_NUM

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EM_BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL From Business Unit

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

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

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

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

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

8 INVOICE_DT Date(10) DATE Invoice Date

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 DSCNT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Discount Amount

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

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

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

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

16 ENTERED_DT Date(10) DATE Entered on

17 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

18 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

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

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

21 PAY_CYCLE Character(6) VARCHAR2(6) NOT NULL Pay Cycle

22 PAY_CYCLE_SEQ_NUM Number(6,0) INTEGER NOT NULL Pay Cycle Sequence Number

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EM_BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	From Business Unit
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
4	INVOICE_ID	Character(30)	VARCHAR2(30) NOT NULL	Invoice Number Prompt Table: EM_INVOI_USR_VW
5	VENDOR_SETID	Character(5)	VARCHAR2(5) NOT NULL	Vendor SetID
6	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier
7	VNDR_LOC	Character(10)	VARCHAR2(10) NOT NULL	Vendor Location Prompt Table: EM_VNDR_LOC_VW
8	INVOICE_DT	Date(10)	DATE	Invoice Date
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	DSCNT_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Discount Amount
12	CURRENCY_PYMNT	Character(3)	VARCHAR2(3) NOT NULL	Payment Currency
13	PAID_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Paid Amount
14	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.
15	PYMNT_HOLD	Character(1)	VARCHAR2(1) NOT NULL	Hold Payment N=No Y=Yes
16	ENTERED_DT	Date(10)	DATE	Entered on
17	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
18	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
19	SETID_PAYTRMS	Character(5)	VARCHAR2(5) NOT NULL	Payment Terms Setid
20	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.
21	PAY_CYCLE	Character(6)	VARCHAR2(6) NOT NULL	Pay Cycle
22	PAY_CYCLE_SEQ_NUM	Number(6,0)	INTEGER NOT NULL	Pay Cycle Sequence Number