EM_VCHR_INQ_VW2 (SQL View)	Index Back
Buyer Payment Installments This view is used by the Market Pay Module to retrieve all of the voucher inquiry payment installment rows.

EM_VCHR_INQ_VW2

(SQL View)

Buyer Payment Installments

This view is used by the Market Pay Module to retrieve all of the voucher inquiry payment installment rows.

SELECT a.business_unit , d.voucher_id , d.PYMNT_CNT , sec.oprid , B.SETID , b.vendor_id , a.invoice_id , d.vndr_loc , a.invoice_dt , d.txn_currency_cd , d.PYMNT_GROSS_AMT , d.dscnt_PAY_amt , d.currency_pymnt , d.paid_amt , 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 , d.PYMNT_ID , d.SCHEDULED_PAY_DT , d.PYMNT_appr_status , d.DSCNT_DUE_DT , d.DSCNT_PAY_AMT , D.REMIT_SETID , d.REMIT_VENDOR , D.PAY_CYCLE , D.PAY_CYCLE_SEQ_NUM , D.BANK_SETID , d.BANK_CD , d.BANK_ACCT_KEY , d.PYMNT_METHOD , d.DESCR254_MIXED , A.VCHR_LINE_APPR , A.doc_tol_hdr_status , A.budget_hdr_status , A.AP_MATCH_TYPE FROM PS_VOUCHER a , ps_vendor b , ps_sec_bu_opr sec , ps_pymnt_vchr_xref d , ps_pymt_trms_hdr t WHERE sec.business_unit = a.business_unit 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 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 d.pymnt_selct_status = 'E')) AND a.entry_status <> 'X' AND d.pymnt_type <> 'W' AND d.pymnt_selct_status <> 'X' AND ((a.VOUCHER_STYLE='PPAY' AND d.PREPAID_STATUS='P' ) OR a.VOUCHER_STYLE<>'PPAY') 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 PYMNT_CNT Number(5,0) INTEGER NOT NULL Payments
Default Value: 1

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

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

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

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

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

9 INVOICE_DT Date(10) DATE Invoice Date

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

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

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

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

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

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

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

17 ENTERED_DT Date(10) DATE Entered on

18 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

19 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

20 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

21 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

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

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

24 PYMNT_ID Character(10) VARCHAR2(10) NOT NULL Payment Number

25 SCHEDULED_PAY_DT Date(10) DATE Scheduled to Pay

26 PYMNT_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
N=Approved Status
P=Pending

27 DSCNT_DUE_DT Date(10) DATE Discount Due Date

28 DSCNT_PAY_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Payment Discount Amount

29 REMIT_SETID Character(5) VARCHAR2(5) NOT NULL Remit SetID

30 REMIT_VENDOR Character(10) VARCHAR2(10) NOT NULL Remit Vendor

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

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

33 BANK_SETID Character(5) VARCHAR2(5) NOT NULL The PeopleSoft tableset ID associated with a given bank/counterparty.

34 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code

35 BANK_ACCT_KEY Character(4) VARCHAR2(4) NOT NULL A user defined unique identifier that facilitates the identification of a given account with a given bank

36 PYMNT_METHOD Character(3) VARCHAR2(3) NOT NULL Payment Method
ACH=Automated Clearing House
BEF=Draft - Customer EFT
BOO=Draft - Customer Initiated
CHK=System Check
D=Deposit
DD=Direct Debit
DFT=Draft - Supplier Initiated
DRA=Draft
EFT=Electronic Funds Transfer
GE=Giro - EFT
GM=Giro - Manual
LC=Letter of Credit
MAN=Manual Check
TRW=Treasury Wire
WIR=Wire Transfer

37 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description

38 VCHR_LINE_APPR Character(1) VARCHAR2(1) NOT NULL Invoice Line Approval Status
0=Line Routing Not Required
1=Completed
2=Routed for Approval

39 DOC_TOL_HDR_STATUS Character(1) VARCHAR2(1) NOT NULL Document Heade Document Tolerance Status
E=Error
N=Not Checked
R=Reset
V=Valid

40 BUDGET_HDR_STATUS Character(1) VARCHAR2(1) NOT NULL Budget Checking Header Status
E=Error in Budget Check
I=Document In Processing
N=Not Budget Checked
P=Provisionally Valid
V=Valid

41 AP_MATCH_TYPE Character(1) VARCHAR2(1) NOT NULL Match Type
C=Matched with Credit Note
D=Matched with Debit Memo
O=Matched with Override
P=Auto - Matched

#	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	PYMNT_CNT	Number(5,0)	INTEGER NOT NULL	Payments Default Value: 1
4	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
5	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
6	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier
7	INVOICE_ID	Character(30)	VARCHAR2(30) NOT NULL	Invoice Number Prompt Table: EM_INVOI_USR_VW
8	VNDR_LOC	Character(10)	VARCHAR2(10) NOT NULL	Vendor Location Prompt Table: EM_VNDR_LOC_VW
9	INVOICE_DT	Date(10)	DATE	Invoice Date
10	TXN_CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Transaction Currency
11	GROSS_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Gross Invoice Amount
12	DSCNT_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Discount Amount
13	CURRENCY_PYMNT	Character(3)	VARCHAR2(3) NOT NULL	Payment Currency
14	PAID_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Paid Amount
15	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.
16	PYMNT_HOLD	Character(1)	VARCHAR2(1) NOT NULL	Hold Payment N=No Y=Yes
17	ENTERED_DT	Date(10)	DATE	Entered on
18	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
19	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
20	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
21	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
22	SETID_PAYTRMS	Character(5)	VARCHAR2(5) NOT NULL	Payment Terms Setid
23	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.
24	PYMNT_ID	Character(10)	VARCHAR2(10) NOT NULL	Payment Number
25	SCHEDULED_PAY_DT	Date(10)	DATE	Scheduled to Pay
26	PYMNT_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 N=Approved Status P=Pending
27	DSCNT_DUE_DT	Date(10)	DATE	Discount Due Date
28	DSCNT_PAY_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Payment Discount Amount
29	REMIT_SETID	Character(5)	VARCHAR2(5) NOT NULL	Remit SetID
30	REMIT_VENDOR	Character(10)	VARCHAR2(10) NOT NULL	Remit Vendor
31	PAY_CYCLE	Character(6)	VARCHAR2(6) NOT NULL	Pay Cycle
32	PAY_CYCLE_SEQ_NUM	Number(6,0)	INTEGER NOT NULL	Pay Cycle Sequence Number
33	BANK_SETID	Character(5)	VARCHAR2(5) NOT NULL	The PeopleSoft tableset ID associated with a given bank/counterparty.
34	BANK_CD	Character(5)	VARCHAR2(5) NOT NULL	Bank Code
35	BANK_ACCT_KEY	Character(4)	VARCHAR2(4) NOT NULL	A user defined unique identifier that facilitates the identification of a given account with a given bank
36	PYMNT_METHOD	Character(3)	VARCHAR2(3) NOT NULL	Payment Method ACH=Automated Clearing House BEF=Draft - Customer EFT BOO=Draft - Customer Initiated CHK=System Check D=Deposit DD=Direct Debit DFT=Draft - Supplier Initiated DRA=Draft EFT=Electronic Funds Transfer GE=Giro - EFT GM=Giro - Manual LC=Letter of Credit MAN=Manual Check TRW=Treasury Wire WIR=Wire Transfer
37	DESCR254_MIXED	Character(254)	VARCHAR2(254) NOT NULL	Description
38	VCHR_LINE_APPR	Character(1)	VARCHAR2(1) NOT NULL	Invoice Line Approval Status 0=Line Routing Not Required 1=Completed 2=Routed for Approval
39	DOC_TOL_HDR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Document Heade Document Tolerance Status E=Error N=Not Checked R=Reset V=Valid
40	BUDGET_HDR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Budget Checking Header Status E=Error in Budget Check I=Document In Processing N=Not Budget Checked P=Provisionally Valid V=Valid
41	AP_MATCH_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Match Type C=Matched with Credit Note D=Matched with Debit Memo O=Matched with Override P=Auto - Matched