EM_VCH_ANAL_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_VCH_ANAL_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 , a.voucher_id , a.invoice_id , a.vendor_setid , a.vendor_id , a.vndr_loc , a.invoice_dt , d.txn_currency_cd , a.gross_amt , a.dscnt_amt , d.due_dt , a.entered_dt , d.pymnt_selct_status , a.voucher_style , E.DESCRSHORT , a.DSCNT_DUE_DT , d.SCHEDULED_PAY_DT FROM PS_VOUCHER a , ps_pymnt_vchr_xref d , ps_set_cntrl_group gp , PS_PYMT_TRMS_HDR E WHERE a.business_unit = d.business_unit AND a.voucher_id = d.voucher_id AND a.vendor_setid = E.setid AND a.pymnt_terms_cd = E.PYMNT_TERMS_CD AND gp.setcntrlvalue = a.business_unit AND gp.rec_group_id = 'FS_14' AND ( a.process_man_close = 'N' OR a.close_status <> 'C') AND a.entry_status <> 'X' AND d.pymnt_type <> 'W' AND d.pymnt_selct_status = 'N' AND d.due_dt IS NOT NULL AND ((a.VOUCHER_STYLE='PPAY' AND d.PREPAID_STATUS='P' ) OR a.VOUCHER_STYLE<>'PPAY') AND NOT EXISTS ( SELECT 'X' FROM PS_EM_PROPOSAL_VW F WHERE a.business_unit = F.EM_BUSINESS_UNIT AND a.voucher_id = F.VOUCHER_ID AND F.PROPOSAL_STATUS <> 'D' AND F.PROPOSAL_STATUS <> 'C') AND EXISTS ( SELECT 'X' FROM PS_EM_BU_VNAGRT_VW G WHERE %OperatorId = G.OPRID AND a.BUSINESS_UNIT = G.BUSINESS_UNIT) AND EXISTS ( SELECT 'X' FROM PS_EM_VNRID_USR_VW H WHERE %OperatorId = H.OPRID AND a.VENDOR_ID = H.VENDOR_ID) AND EXISTS ( SELECT 'X' FROM PS_EM_VNDR_LOC_VW I WHERE a.VENDOR_ID = I.VENDOR_ID AND a.VNDR_LOC = I.VNDR_LOC)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

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

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

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

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

6 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location

7 INVOICE_DT Date(10) DATE Invoice Date

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

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

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

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

12 ENTERED_DT Date(10) DATE Entered on

13 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

14 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

15 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description

16 DSCNT_DUE_DT Date(10) DATE Discount Due Date

17 SCHEDULED_PAY_DT Date(10) DATE Scheduled to Pay

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EM_BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	From Business Unit
2	VOUCHER_ID	Character(8)	VARCHAR2(8) NOT NULL	Voucher ID
3	INVOICE_ID	Character(30)	VARCHAR2(30) NOT NULL	Invoice Number Prompt Table: EM_INVOI_USR_VW
4	VENDOR_SETID	Character(5)	VARCHAR2(5) NOT NULL	Vendor SetID
5	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier
6	VNDR_LOC	Character(10)	VARCHAR2(10) NOT NULL	Vendor Location
7	INVOICE_DT	Date(10)	DATE	Invoice Date
8	TXN_CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Transaction Currency
9	GROSS_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Gross Invoice Amount
10	DSCNT_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Discount Amount
11	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.
12	ENTERED_DT	Date(10)	DATE	Entered on
13	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
14	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
15	DESCRSHORT	Character(10)	VARCHAR2(10) NOT NULL	Short Description
16	DSCNT_DUE_DT	Date(10)	DATE	Discount Due Date
17	SCHEDULED_PAY_DT	Date(10)	DATE	Scheduled to Pay