APOP_INVOICE_MV (SQL View)	Index Back
Voucher Detail MV MV based on the Voucher Header transactional data (VOUCHER).

APOP_INVOICE_MV

(SQL View)

Voucher Detail MV

MV based on the Voucher Header transactional data (VOUCHER).

SELECT A.BUSINESS_UNIT , A.VOUCHER_ID , A.INVOICE_ID , A.INVOICE_DT , A.INVOICE_RCPT_DT , A.ENTERED_DT , A.VCHR_SRC , A.VOUCHER_STYLE , A.GROSS_AMT , A.PAY_AMT , A.DSCNT_AMT , A.TXN_CURRENCY_CD , A.VENDOR_SETID , A.VENDOR_ID , A.OPRID , A.ACCOUNTING_DT , A.BUSINESS_UNIT_PO , A.PO_ID , A.PYMNT_TERMS_CD , A.APPR_STATUS , A.APPR_INSTANCE , A.VCHR_APPRVL_FLG , A.DELETE_DT , A.OPRID_LAST_UPDT , A.ENTRY_STATUS , A.POST_STATUS_AP , A.ORIGIN , CASE WHEN (A.INVOICE_DT IS NULL OR A.INVOICE_DT >= A.ENTERED_DT) THEN 0 ELSE %DateDiff(A.INVOICE_DT, A.ENTERED_DT) END , CASE WHEN (A.INVOICE_RCPT_DT IS NULL OR A.INVOICE_RCPT_DT >= A.ENTERED_DT) THEN 0 ELSE %DateDiff(A.INVOICE_RCPT_DT, A.ENTERED_DT) END , A.ROWID , B.ROWID FROM %Table(VOUCHER) A , %Table(OPI_SYSCFG_TBL1) B WHERE (A.INVOICE_DT BETWEEN B.FROM_DT AND B.TO_DT OR A.DELETE_DT BETWEEN B.FROM_DT AND B.TO_DT ) AND A.VOUCHER_STYLE IN ('ADJ','CLBK','CORR','JRNL','PPAY','REG','RGTR','SGLP','THRD','AMR') AND A.VCHR_DRAFT_FLG <> 'Y' AND B.PRODUCT_OE ='ALL'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

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

3 INVOICE_ID Character(30) VARCHAR2(30) NOT NULL Invoice Number

4 INVOICE_DT Date(10) DATE Invoice Date

5 INVOICE_RCPT_DT Date(10) DATE InvoiceRecptDate

6 ENTERED_DT Date(10) DATE Entered on

7 VCHR_SRC Character(4) VARCHAR2(4) NOT NULL Voucher Source

8 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

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

10 PAY_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Payment Amount

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

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

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

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

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

16 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple

17 BUSINESS_UNIT_PO Character(5) VARCHAR2(5) NOT NULL PO Business Unit

18 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order

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

20 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

21 APPR_INSTANCE Number(9,0) DECIMAL(9) NOT NULL Unique number for each runtime occurrence of a virtual approver process

22 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

23 DELETE_DT Date(10) DATE Deletion Date

24 OPRID_LAST_UPDT Character(30) VARCHAR2(30) NOT NULL Last User to Update

25 ENTRY_STATUS Character(1) VARCHAR2(1) NOT NULL If translate values are added or changed, modify the following code to make sure the values appear accurately in the dropdown list in the search dialog: VCHR_SRCH_DEL.ENTRY_STATUS.SearchInit. Template Voucher status should not be visibile in the drop down.
C=Complete
D=Through batch defaults
E=Edited by Batch Voucher Module
L=Reviewed through Maintenance
M=Modified
O=Open
P=Postable
R=Recycle
S=Scheduled for Payment
T=Template Voucher
X=Deleted

26 POST_STATUS_AP Character(1) VARCHAR2(1) NOT NULL Post Status
N=Payment Not Applied
P=Posted
U=Unposted

27 ORIGIN Character(3) VARCHAR2(3) NOT NULL Origin

28 AP_DAYSBETWEEN Number(12,0) DECIMAL(12) NOT NULL Used to keep the difference between 2 dates.

29 AP_DAYSBETWEEN2 Number(12,0) DECIMAL(12) NOT NULL Used to keep the difference between 2 dates.

30 ROWID1 Character(254) VARCHAR2(254) NOT NULL Row ID

31 ROWID2 Character(254) VARCHAR2(254) NOT NULL Row ID

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
2	VOUCHER_ID	Character(8)	VARCHAR2(8) NOT NULL	Voucher ID
3	INVOICE_ID	Character(30)	VARCHAR2(30) NOT NULL	Invoice Number
4	INVOICE_DT	Date(10)	DATE	Invoice Date
5	INVOICE_RCPT_DT	Date(10)	DATE	InvoiceRecptDate
6	ENTERED_DT	Date(10)	DATE	Entered on
7	VCHR_SRC	Character(4)	VARCHAR2(4) NOT NULL	Voucher Source
8	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
9	GROSS_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Gross Invoice Amount
10	PAY_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Payment Amount
11	DSCNT_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Discount Amount
12	TXN_CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Transaction Currency
13	VENDOR_SETID	Character(5)	VARCHAR2(5) NOT NULL	Vendor SetID
14	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier
15	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
16	ACCOUNTING_DT	Date(10)	DATE	The accounting entry construction date for a given transaction (a generic field that crosses multiple
17	BUSINESS_UNIT_PO	Character(5)	VARCHAR2(5) NOT NULL	PO Business Unit
18	PO_ID	Character(10)	VARCHAR2(10) NOT NULL	Purchase Order
19	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.
20	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
21	APPR_INSTANCE	Number(9,0)	DECIMAL(9) NOT NULL	Unique number for each runtime occurrence of a virtual approver process
22	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
23	DELETE_DT	Date(10)	DATE	Deletion Date
24	OPRID_LAST_UPDT	Character(30)	VARCHAR2(30) NOT NULL	Last User to Update
25	ENTRY_STATUS	Character(1)	VARCHAR2(1) NOT NULL	If translate values are added or changed, modify the following code to make sure the values appear accurately in the dropdown list in the search dialog: VCHR_SRCH_DEL.ENTRY_STATUS.SearchInit. Template Voucher status should not be visibile in the drop down. C=Complete D=Through batch defaults E=Edited by Batch Voucher Module L=Reviewed through Maintenance M=Modified O=Open P=Postable R=Recycle S=Scheduled for Payment T=Template Voucher X=Deleted
26	POST_STATUS_AP	Character(1)	VARCHAR2(1) NOT NULL	Post Status N=Payment Not Applied P=Posted U=Unposted
27	ORIGIN	Character(3)	VARCHAR2(3) NOT NULL	Origin
28	AP_DAYSBETWEEN	Number(12,0)	DECIMAL(12) NOT NULL	Used to keep the difference between 2 dates.
29	AP_DAYSBETWEEN2	Number(12,0)	DECIMAL(12) NOT NULL	Used to keep the difference between 2 dates.
30	ROWID1	Character(254)	VARCHAR2(254) NOT NULL	Row ID
31	ROWID2	Character(254)	VARCHAR2(254) NOT NULL	Row ID