PEOPLESOFT VOUCHER TABLES IN THE DATA WAREHOUSE

These are the commonly used and most useful fields from these tables. Access returns values rather than descriptions. Translations are listed below. * = Key field TABLE FIELD NAMES FORMAT DESCRIPTION

VOUCHER

* BUSINESS_UNIT * VOUCHER_ID INVOICE_ID INVOICE_DT VENDOR_ID VNDR_LOC ADDRESS_SEQ_NUM GRP_AP_ID OPRID CLOSE_STATUS ENTRY_STATUS ACCOUNTING_DT POST_STATUS_AP GROSS_AMT DSCNT_AMT FREIGHT_AMT PYMNT_TERMS_CD ENTERED_DT MANUAL_CLOSE_DT DESCR254_MIXED

Char 5 Char 8 Char 30 Date Char 10 Char 10 Num 3 Char 6 Char 8 Char 1 Char 1 Date Char 1 Num 17 Num 17 Num 17 Char 5 Date Date

Always = UIOWA Voucher number Invoice number Invoice date Vendor number Vendor location number Address number AP's internal control group number AP operator entering voucher C = Closed; O = Open C = Complete; O = Open; P = Postable; R = Recycle; S = Scheduled for Payment; X = Deleted Accounting date P = Posted; U = Unposted Gross invoice amount Discount amount Freight amount Payment Terms - i.e. 2%/10 Voucher entered date Manual close date

Char 254 Voucher comments

TABLE

FIELD NAMES

FORMAT

DESCRIPTION

VOUCHER_LINE

* BUSINESS_UNIT * VOUCHER_ID VOUCHER_LINE_NUM PO_ID LINE_NBR DESCR MERCHANDISE_AMT QTY_VCHR UNIT_OF_MEASURE UNIT_PRICE WTHD_SW WTDH_CD

Char 5 Char 8 Num 5 Char 10 Num 5 Char 30 Num 17 Num 17 Char 3 Num 17 Char 1 Char 5

Always = UIOWA Voucher number Voucher line number Purchase order number PO line number Voucher line description Voucher line amount Quantity vouchered Unit of measure Unit price Y = 1099 applicable; N = not 1099 applicable Withholding code - Either blank or a 2 digit code if the vendor is 1099 reportable. Always = UIOWA Voucher number Voucher line number MFK line number Institutional account number MFK amount Accounting date Purchase order number PO line number Fund 9-digit Department and SubDepartment numbers combined Cost center Function Grant/program number Organization account number Departmental account number SLID/SLAC combined

DISTRIB_LINE

* BUSINESS_UNIT * VOUCHER_ID * VOUCHER_LINE_NUM DISTRIB_LINE_NUM ACCOUNT MERCHANDISE_AMT ACCOUNTING_DT PO_ID LINE_NBR FUND_CODE DEPTID PROGRAM_CODE CLASS_FLD OPERATING_UNIT CHARTFIELD1 CHARTFIELD2 CHARTFIELD3

Char 5 Char 8 Num 5 Num 5 Char 4 Num 17 Date Char 10 Num 5 Char 3 Char 9 Char 4 Char 2 Char 8 Char 3 Char 5 Char 17

TABLE

FIELD NAMES

FORMAT

DESCRIPTION

PYMNT_VCHR_XREF

* BUSINESS_UNIT * VOUCHER_ID * PYMNT_CNT PYMNT_ID PAID_AMT PYMNT_GROSS_AMT DSCNT_PAY_AMT SCHEDULED_PAY_DT PAID_AMT_GROSS PAID_AMT_DSCNT REMIT_VENDOR VNDR_LOC REMIT_ADDR_SEQ_NUM PYMNT_MESSAGE PYMNT_HOLD PYMNT_HOLD_REASON PYMNT_SEPARATE PYMNT_HANDLING_CD

Char 5 Char 8 Num 5 Char 10 Num 17 Num 17 Num 17 Date Num 17 Num 17 Char 10 Char 10 Num 3 Char 30 Char 1 Char 3 Char 1 Char 2

Always = UIOWA Voucher number Payment count Payment number - Not check number; assigned by system Paid amount Gross payment amount Payment discount amount Scheduled pay date Gross paid amount Discount on paid amount Remit vendor number Vendor location number Remit address number Payment message - prints on the check Hold payments (Y / N)

Hold reason - see http://www.uiowa.edu/~fusap/ap/faq.shtml#21 for listing

GRP_AP

* BUSINESS_UNIT * GRP_AP_ID GROUP_DT GRP_AP_STATUS CTL_GROSS_AMT CTL_VOUCHERS ASSIGNED_TO_OPRID VCHR_NUM_BEGIN VCHR_NUM_END

Char 5 Char 6 Date Char 1 Num 17 Num 3 Char 8 Num 8 Num 8

Always = UIOWA AP's internal control group number Control group date - date the control group was created A = Assigned; E = Entry Set Aside; O = Open; R = Ready for Review; S = Supervisor Set Aside; V = Verified Control group gross amount Number of vouchers in control group AP staff assigned to work control group Beginning voucher number in control group Ending voucher number in control group

TABLE

FIELD NAMES

FORMAT

DESCRIPTION

UI_VCHR_TBL

VOUCHER_ID VOUCHER_LINE_NUM DISTRIB_LINE_NUM PYMNT_ID PYMNT_CNT VOUCHER_ID_RELATED INVOICE_ID INVOICE_DT GROSS_AMT PYMNT_GROSS_AMT DSCNT_PAY_AMT PYMNT_HANDLING_CD

Char 8 Num 5 Num 5 Char 10 Num 5 Char 8 Char 30 Date Num 17 Num 17 Num 17 Char 2

Voucher number Voucher line number MFK line number Payment number - Not check number; assigned by system Payment Count Voucher number of related voucher Invoice number Invoice date Gross Amount Gross payment amount Payment discount amount Payment handling code: DR = Dorm Refunds; FB = Flex Benefits; FR = FIRE (financial aid); FT = Freight; HD = High Dollar; IN = Internal Distribution; PY = Payroll; RA = Regular with Attachments; RE = Regular; TR = Travel AP's internal control group number Sales tax amount Freight amount Use tax amount Discount due date Payment Terms - i.e. 2%/10 Entered date

GRP_AP_ID SALETX_AMT FREIGHT_AMT USETAX_AMT DSCNT_DUE_DT PYMNT_TERMS_CD ENTERED_DT DESCR254_MIXED PO_ID LINE_NBR DESCR UNIT_PRICE UNIT_OF_MEASURE QTY_VCHR_CNVT UNIT_PRICE_CNVT MERCHANDISE_AMT_PO ACCOUNT

Char 6 Num 17 Num 17 Num 17 Date Char 5 Date

Char 254 Voucher comments Char 10 Num 5 Char 30 Num 17 Char 30 Num 16 Num 16 Num 17 Char 4 Purchase order number PO line number Voucher line description Unit price Unit of measure Voucher quantity converted Unit price converted ??? Institutional account number

TABLE UI_VCHR_TBL cont. FUND

FIELD NAMES

FORMAT Char 30 Char 9 Char 30 Char 4 Char 2 Char 6 Char 3 Char 5 Char 17 Num 17 Num 17 Date Char 5 Char 10 Num 3 Num 17 Char 1 Char 3 Char 1 Date Char 2 Char 1 Char 5 Char 1 Fund

DESCRIPTION

DEPTID DESCR2 PROGRAM_CODE CLASS_FLD OPERATING_UNIT CHARTFIELD1 CHARTFIELD2 CHARTFIELD3 QTY_VCHR MERCHANDISE_AMT SCHEDULED_PAY_DT REMIT_SETID REMIT_VENDOR REMIT_ADDR_SEQ_NUM DSCNT_AMT PYMNT_HOLD PYMNT_HOLD_REASON CLOSE_STATUS MANUAL_CLOSE_DT PRODUCT WTHD_SW WTHD_CD POST_STATUS_WTHD

9-digit Department and SubDepartment numbers combined ??? Cost Center Function Grant/program number Organization account number Departmental account number SLID/SLAC Combined Quantity vouchered MFK amount Scheduled pay date Always = UIOWA Remit vendor number Remit address number Discount amount Hold Payments (Y / N) Hold reason - see http://www.uiowa.edu/~fusap/ap/faq.shtml#21 for listing C = Closed; O = Open Manual close date Organizational unit Y = 1099 applicable; N = not 1099 applicable Withholding code - Either blank or a 2 digit code if the vendor is 1099 reportable. N = Not postable; P = Posted; U = Unposted

TABLE

FIELD NAMES

FORMAT

DESCRIPTION

UI_VCHR_ACCTG

* VOUCHER_ID * APPL_JRNL_ID * * PYMNT_CNT UI_MFK_CNT ACCOUNT FUND_CODE DEPTID PROGRAM_CODE CLASS_FLD OPERATING_UNIT CHARTFIELD1 CHARTFIELD2 CHARTFIELD3 MONETARY_AMOUNT UI_VA_DIST_STATUS REFERENCE_NUMBER PO_ID UI_VCHR_PERCENT UI_REMAINING_AMT

Char 8 Char 10 Num 5 Num 3 Char 4 Char 3 Char 9 Char 4 Char 2 Char 8 Char 3 Char 5 Char 17 Num 17 Char 1 Char 10 Char 10 Num 15 Num 17

Voucher number Journal template Payment count ????????? Institutional account number Fund 9-digit Department and SubDepartment numbers combined Cost center Function Grant/program number Organization account number Departmental account number SLID/SLAC Combined ????????????????/ No options listed Reference number Purchase order number Percent of total voucher Remaining accrual amount