PeopleSoft Financials - Key Records for Queries

Accounts Payable

·  VOUCHER – AP Voucher Header Table

  • BUSINESS_UNIT, VOUCHER_ID

·  VOUCHER_LINE – Voucher Line

  • BUSINESS_UNIT, VOUCHER_ID, VOUCHER_LINE_NUM

·  DISTRIB_LINE – Voucher Distribution Table

  • BUSINESS_UNIT, VOUCHER_ID, VOUCHER_LINE_NUM, DISTRIB_LINE_NUM

·  VCHR_ACCTG_LINE – AP Accounting Entries

  • BUSINESS_UNIT, VOUCHER_ID, UNPOST_SEQ, APPL_JRNL_ID, POSTING_PROCESS, PYMNT_CNT, VOUCHER_LINE_NUM, DISTRIB_LINE_NUM, DST_ACCT_TYPE, CF_BAL_LINE_NUM, LEDGER, TAX_AUTHORITY_CD

·  PAYMENT_TBL – AP Disbursements

  • BANK_SETID, BANK_CD, BANK_ACCT_KEY, PYM NT_ID

·  PYMNT_VCHR_XREF – Voucher Scheduled Payment

  • BUSINESS_UNIT, VOUCHER_ID, PYMNT_CNT

·  VENDOR – Vendor Header Table

o  When joining the VENDOR record to another record, do not accept the auto-join criteria of BUSINESS_UNIT = BUSINESS_UNIT. The BUSINESS_UNIT field in VENDOR is blank, and is only populated in systems with multi-BUSINESS_UNITS.

  • SETID, VENDOR_ID

·  VENDOR_LOC – Vendor Location

  • SETID, VENDOR_ID, VNDR_LOC, EFFDT

·  VENDOR_ADDR – Vendor Address

  • SETID, VENDOR_ID, ADDRESS_SEQ_NUM, EFFDT

·  VENDOR_TYPE – Vendor Type

  • SETID, VENDOR_ID, VNDR_LOC, EFFDT, VENDOR_TYPE

Commitment Control

·  LEDGER_KK – Ledger Data (KK Ledger)

  • BUSINESS_UNIT, LEDGER, ACCOUNT, DEPTID, OPERATING_UNIT, PRODUCT, FUND_CODE, CLASS_FLD, PROGRAM_CODE, BUDGET_REF, AFFILIATE, AFFILIATE_INTRA1, AFFILIATE_INTRA2, CHARTFIELD1, CHARTFIED2, CHARTFIELD3, BUSINESS_UNIT_PC, PROJECT_ID, ACTIVITY_ID, RESOURCE_TYPE, BUDGET_PERIOD, CURRENCY_CD, STATISTICS_CD, FISCAL_YEAR, ACCOUNTING_PERIOD, KK_BUDG_TRANS_TYPE

Commitment Control – Cont’d

·  LEDG_KK_VW_BOR – Ledger View with KK Amount Types

o  Mimics LEDGER_BUDG (in 7.5); Selects Budget, Pre-enc, Enc and Exp amounts from LEDGER_KK

  • Same as LEDGER_KK

·  LEDG_KK_VW2_BOR – Recognized Amount/Collected Amount

o  Like LEDG_KK_VW_BOR with Recognized and Collected Revenue

  • Same as LEDGER_KK

·  LEDG_KK_VW3_BOR – GASB Record View

o  Used for GASB reporting; Pre-enc and Enc amounts from LEDGER_KK; Exp amounts from LEDGER

  • Same as LEDGER_KK

·  LEDG_KK_VW4_BOR – BOR KK All Amounts View

o  All amount fields (Budget, Pre-enc, Enc, Exp, Recognized & Collected Revenue)

  • Same as LEDGER_KK

·  KKACTSRC_VW_BOR – KK Activity Log/Source Header & Line View

o  Join Between KK_SOURCE_HDR, KK_SOURCE_LINE and KK_ACTIVITY_LOG

  • KK_TRAN_ID, KK_TRAN_DT_KK_TRAN_LN, REFERENCED_BUDGET, BALANCING_LINE, LEDGER_GROUP, LEDGER, FISCAL_YEAR, ACCOUNTING_PERIOD, SEQNBR, RVRSL_FLG

·  KK_SOURCE_HDR – KK Source Header Table

  • KK_TRAN_ID, KK_TRAN_DT

·  KK_SOURCE_LN – KK Source Line Table

  • KK_TRAN_ID, KK_TRAN_DT, KK_TRAN_LN

·  KK_ACTIVITY_LOG – KK Budget Activity Record

  • KK_TRAN_ID, KK_TRAN_DT, KK_TRAN_LN, REFERENCED_BUDGET, BALANCING_LINE, LEDGER_GROUP, LEDGER, FISCAL_YEAR, ACCOUNTING_PERIOD, SEQNBR, RVRSL_FLG

·  KK_TRANS_LOG – KK Transaction Log

  • KK_TRAN_ID, KK_TRAN_DT, KK_TRAN_LN, SEQNBR, DT_TIMESTAMP

·  KK_LIQUIDATION – KK Liquidation Table

  • KK_TRAN_ID, KK_TRAN_DT, KK_TRAN_LN_LEDGER_GROUP

·  KK_REFERENCED – Store Reference Data

o  Stores the reference data between Req to PO, PO to Voucher, and TAuth to Exp Report

  • KK_TRAN_ID, KK_TRAN_DT, KK_TRAN_LN

·  KK_EXCPTN_TBL – KK Transaction Exceptions

o  Stores KK transaction budget-checking exceptions

  • KK_TRAN_ID_KK_TRAN_DT, KK_TRAN_LN, LEDGER_GROUP, EXCPTN_TYPE

·  KK_OVERRIDE_TBL – Document/Budget Overrides

  • Same as LEDGER_KK + KK_TRAN_ID, KK_TRAN_DT

Commitment Control – Cont’d

·  KK_BUDGET_HDR – KK Budget Journal Header

  • BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ

·  KK_BUDGET_LN – KK Budget Journal Line

  • BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ, JOURNAL_LINE

General Ledger

·  LEDGER – Ledger Data

  • BUSINESS_UNIT, LEDGER, ACCOUNT, ALTACCT, DEPTID, OPERATING_UNIT, PRODUCT, FUND_CODE, CLASS_FLD, PROGRAM_CODE, BUDGET_REF, AFFILIATE, AFFILIATE_INTRA1, AFFILIATE_INTRA2, CHARTFIELD1, CHARTFIED2, CHARTFIELD3, BUSINESS_UNIT_PC, PROJECT_ID, ACTIVITY_ID, RESOURCE_TYPE, BUDGET_PERIOD, CURRENCY_CD, STATISTICS_CD, FISCAL_YEAR, ACCOUNTING_PERIOD

·  JRNL_HEADER – Journal Header Data

o  When Joining JRNL_HEADER to JRNL_LN, do not accept the auto-join criteria of LEDGER=LEDGER. The LEDGER field is not populated in JRNL_HEADER.

  • BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ

·  JRNL_LN – Journal Line Data

o  Need to use criteria for KK_AMOUNT_TYPE to determine if pre-enc, enc or exp

  • BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ, JOURNAL_LINE, LEDGER

·  MCG_PERSSERV_VW – Personal Services Data

  • TRANSACTION_ID

·  HR_ACCTG_LINE – Payroll Accounting Line

  • RUN_DT, SEQNUM, LINE_NBR

Grants/Projects – PeopleSoft and MCG Bolt-on

·  PROJECT – Projects

  • BUSINESS_UNIT, PROJECT_ID

·  PROJ_GRANT_BOR – Project/Grant Record Definition

  • SETID, PROJECT_ID, EFFDT

·  PROJECT_MGR – Project Manager

  • BUSINESS_UNIT, PROJECT_ID, EFFDT

·  MCG_GRT_INFO – MCG Grant Information Record

  • SETID, PROJECT_ID

·  MCG_GRT_SPONSOR – Sponsors

  • SETID, PROJECT_ID, SEQ_NBR

Grants/Projects – PeopleSoft and MCG Bolt-on – Cont’d

·  MCG_GRT_PERSON – Award Personnel

  • SETID, PROJECT_ID, SEQ_NBR

·  MCG_GRT_SUBCONT – MCG Project/Grant Subcontracts

  • SETID, PROJECT_ID, SEQ_NBR

·  MCG_GRT_IDC_RAT – IDC Rate Information

  • SETID, PROJECT_ID, EFFDT

Purchasing

·  PO_HDR – Purchase Order Header

  • BUSINESS_UNIT, PO_ID

·  PO_LINE – Purchase Order Line Item

  • BUSINESS_UNIT, PO_ID, LINE_NBR

·  PO_LINE_DISTRIB – PO Line Accounting Entries

  • BUSINESS_UNIT, PO_ID, LINE_NBR, SCHED_NBR, DST_ACCT_TYPE, DISTRIB_LINE_NUM

·  PO_LINE_SHIP – PO Line Shipping Schedule

  • BUSINESS_UNIT, PO_ID, LINE_NBR, SCHED_NBR

·  REQ_HDR – Requisition Header

  • BUSINESS_UNIT, REQ_ID

·  REQ_LINE – Requisition Line

  • BUSINESS_UNIT, REQ_ID, LINE_NBR

·  REQ_LINE_DISTRIB – Requisition Distribution

  • BUSINESS_UNIT, REQ_ID, LINE_NBR, SCHED_NBR, DISTRIB_LINE_NUM

·  REQ_LINE_SHIP – Requisition Line Delivery Schedule

  • BUSINESS_UNIT, REQ_ID, LINE_NBR, SCHED_NBR

·  RECV_HDR – Receiver Header

  • BUSINESS_UNIT, RECEIVER_ID

·  RECV_LN_DISTRIB – Receipt Accounting Entries

  • BUSINESS_UNIT, RECEIVER_ID, RECV_LN_NBR, RECV_SHIP_SEQ_NBR, DISTRIB_LINE_NUM

·  RECV_LN_SHIP – Receipt Shipping Schedule

  • BUSINESS_UNIT, RECEIVER_ID, RECV_LN_NBR, RECV_SHIP_SEQ_NBR

Records Used for ChartField Values

·  GL_ACCOUNT_TBL – Accounts

  • SETID, ACCOUNT, EFFDT

·  FUND_TBL – Fund Table

  • SETID, FUND_CODE, EFFDT

Records Used for ChartField Values – Cont’d

·  DEPT_TBL – Departments

  • SETID, DEPTID, EFFDT

·  PROGRAM_TBL – Program Table

  • SETID, PROGRAM_CODE, EFFDT

·  CLASS_CF_TBL – Class of Trade Table

  • SETID, CLASS_FLD, EFFDT

·  PROJECT – Projects

  • BUSINESS_UNIT, PROJECT_ID

Archived PO and Requisition Records from PS 7.5

  • Key fields are the same as the Purchasing none archived records.

·  PO_HDR_BOR – BOR PO Activity Header archive

·  PO_LINE_BOR – BOR PO Line Item archive

·  PO_LINE_DST_BOR – BOR PO Line Accounting Entries Archive

·  PO_LINE_SHIP_BOR – BOR PO Line Ship Schedule Archive

·  REQ_HDR_BOR – BOR Requisition Header Archive

·  REQ_LINE_BOR – BOR Requisition Line Archive

·  REQ_LN_DIST_BOR – BOR Requisition Distribution Archive

Miscellaneous Useful Records to Use in Queries or Query Development

·  ITM_CAT_TBL – Category Codes for Purchasing

o  When using this record, you must have a criteria statement for SETID = ‘12000’

  • SETID, CATEGORY_TYPE, CATEGORY_CD, CATEGORY_ID, EFFDT

·  XLATTABLE – Translate Value View

o  This record will show you the translate values for all fields in PSFin. (i.e., for PO_STATUS, X means Canceled, C means Complete, A means Approved, etc.)

  • FIELDNAME, FIELDVALUE, EFFDT

Page 1