Health and Wealth Tips for you and your family...:)

Mudra's for better life...try daily

PeopleSoft Finance Tables

PeopleSoft Finance - CORE Tables for AP and GL General Ledger Tables   

PS_LEDGER	The general ledger data table
PS_JRNL_HEADER	Stores general ledger journal header data.
PS_JRNL_LN	Stores general ledger journal line data.
PS_COMBO_RULE_TBL	Stores ChartField combination rules.
PS_COMBO_GROUP_TBL	Stores ChartField combination groups.
PS_COMBO_DATA_TBL	ChartField Combo data table.
PS_BU_LED_COMB_TBL	Business Unit CharField Combos for Ledger.
PS_GL_ACCOUNT_TBL	GL Accounts table.

Accounts Payable/Voucher Tables      

PS_GRP_AP	Stores AP control groups.
PS_VOUCHER	AP voucher header table.
PS_VOUCHER_LINE	AP voucher line.
PS_DISTRIB_LINE	Voucher distribution table.
PS_PAYMENT_TBL	AP disbursements table.
PS_PYMNT_VCHR_XREF	Voucher Scheduled Payment.
PYMNT_XREF_VW
PS_RT_RATE_TBL	Market Rate Data Tale. There is also the market rate default view RT_DFLT_VW.
PS_VCHR_ACCTG_LINE	AP accounting entries.
RT_RATE_TBL	Market Rate Data Tale. There is also the market rate default view
RT_DFLT_VW

Voucher Build Staging Tables

PS_VCHR_HDR_STG
PS_VCHR_LINE_STG
PS_VCHR_DIST_STG
PS_VCHR_PYMT_STG

Vendor Tables       

VENDOR	Stores vendor related information.
VENDOR_ADDR	Vendor address related info.
VENDOR_LOC	Vendor locations.
VENDOR_PAY	Payment processing specifications such as payment method, payment handling code, bank code and so forth.
VNDR_ADDR_SCROL	Index to address table
VNDR_CNTCT_SCR	Index to contact table.
VNDR_LOC_SCROL	Index to location table.
VENDOR_ID_NBRS	Additional ID numbers (Dun & Bradstreet numbers).
VENDOR_ADDR_PHN	Vendor phone numbers.
VENDOR_CNTCT	Vendor contacts.
VENDOR_CONVER	Vendor converstaions.
VENDOR_INVOICE	Stores vendor invoice related flags.
VENDOR_TYPE	Vendor type codes.
VNDR_BANK_ACCT	Vendor electronic payment information.
VNDR_CNTCT_PHN	Vendor contact phone numbers.
VNDR_IND_CLASS	Vendor Standard Industrial Classification (SIC) codes.
VNDR_URL	Vendor website address.
VNDR_VAT_RGSTRN	Vendor VAT information.
VENDOR_WTHD	Withholding data
VENDOR_WTHD_JUR	Withholding type.

Requisition Tables

REQ_HDR	Requisition Header
REQ_LINE	Requisition Line
REQ_LINE_SHIP	Requisition Line Schedule
REQ_LN_DISTRIB	Requisition Distribution

Purchase Order Tables

PS_PO_HDR	PO header detail
PS_PO_LINE	Po line details
PS_PO_LINE_DISTRIB	PO line distribution details
PO_LINE_MATCHED PS_VENDOR	Vendor details.
PS_PERSONAL_DATA	Personal data details
PS_IF_WFLOW_PO	PO work flow details
PS_PO_LINE_SHIP	PO ship details.
PS_PO_RECV_LN_VW	When PO received then its details
PS_VCHR_LN_PO_VW	When we create voucher of PO PS_RECV_HDR---Receiver header table RECV_LN

Accounts Receivable Tables

PS_ITEMPS_ITEM_ACTIVITY
PS_ITEM_DSTPS_PENDING_ITEM
PS_PENDING_DST
PS_GROUP_CONTROL
PS_DEPOSIT_CONTROL
PS_PAYMENT
PS_PAYMENT_ID_CUST
PS_PAYMENT_ID_ITEM

SELECT * FROM PSSECOPTIONS -- PS Password security details
SELECT * FROM PSSTATUS           -- PS PeopleTools version details
SELECT * FROM PSRELEASE        -- PS PeopleSoft Application Details

PeopleSoft Commitment Control Tables

PeopleSoft Commitment Control

Log tables that are updated by the budget processor:
KK_SOURCE_HDR
KK_SOURCE_LN
KK_ACTIVITY_LOG
KK_REFERENCED
KK_LIQUIDATION
KK_TRANS_LOG
KK_TRAN_ID_TBL
KK_OVERRIDE_TBL
KK_EXCPTN_TBL

KK_SOURCE_HDR
- When budget checking is run against a transaction, the budget processor will record the information KK_SOURCE_HDR.
- A unique KK_TRAN_ID is assigned for each transaction header being budget checked.
- The transaction header key is inserted into the table (e.g. Voucher ID, PO ID, Journal ID)
- Data stored in this table remains in the table as long as the transaction is not deleted.
- Even if there is no budget impacted by the transaction, the data in this table will remain in the table.
- This is an indicator that the transaction was budget checked (regardless of whether it passed or not).

KK_SOURCE_LN
- This table is a child table of KK_SOURCE_HDR and is linked by the KK_TRAN_ID.
- It stores transaction line information and KK_TRAN_LN is assigned to each line.
- Only transaction line that pass budget checking and impacts the budget ledger will be inserted into the KK_SOURCE_LN table.
- When the transaction is deleted, all rows associated with the transaction are deleted.

KK_ACTIVITY_LOG
- This table is a child table of KK_SOURCE_LN and is linked by the KK_TRAN_ID and KK_TRAN_LN.
- It stores budget level information used to update LEDGER_KK
- For each budget ledger that is impacted by the transaction line, a row is inserted to the table. Therefore, if there are two budget ledgers (e.g. APPROP and ORG) are impacted for a journal ID ABC, line1, then two rows are inserted to this KK_ACTIVITY_LOG table.
- Same as KK_SOURCE_HDR and KK_SOURCE_LN, when the transaction is deleted from the system the data associated with the transaction is deleted as well.
- The budget checking will update the LEDGER_KK base on the information on this table.

KK_LIQUIDATION
- This table is used to store and track open balances for documents that are referenced by subsequent transactions. This table is similar to open pre-enc/enc table in E&G 7.5.
- If the transaction is passed budget checking, one row per transaction line per commitment control ledger group is added or updated.
- If the source transaction is deleted all rows related to the transaction are deleted.
- KK_POSTED_AMT field represent the total source transaction amount.
- MONETARY_AMOUNT field represent the remaining open balance.
E.g.
PO amount = 100 (KK_POSTED_AMT = 100)
PO Voucher amount = 80.
---
Open balance = 20 (MONETARY_AMOUNT = 20)

KK_REFERENCED
- This table stored the link for procure to pay transactions.
- It stores the reference data between Requisition and Purchase Order or Purchase Order and Voucher.
- KK_TRAN_ID field stored the predecessor document transaction id (PO trans id that is referenced to PO Voucher)
- KK_REFD_ID field stored the referenced document. (PO Voucher trans id that is

Miscellaneous tables
KK_TRANS_LOG - Stores the history of a document. KK_TRANS_LOG contains much of the same data that KK_ACTIVITY_LOG stores. The primary difference between the tables is a sequence number that is defined on the transaction log. Each time a transaction is budget checked, the budget processor inserts data into the log record and increments the sequence number field by one. This allows users to see how documents change between passes of the budget processor.

This table is populated only if the _Update Transaction Log_ option in the Source Transaction definition is set to _Update Log_ (Commitment Control, Define Control Budgets, Budget Setup, Source Transactions, Options page).

KK_TRAN_ID_TBL - Stored the last transaction id used.

KK_OVERRIDE_TBL - Stored the user id or operator id that override the budget checking for a particular document.

KK_EXCPTN_TBL - Stores Commitment Control transaction budget checking exceptions.

PeopleSoft Commitment Control

Understanding commitment control

Basic concepts of commitment control: 

Commitment control can be used to track expenses against pre-defined control budgets as well as to track recognized revenue against revenue estimate budgets. In this article, we’ll concentrate more on the expense side of commitment control, as it is more widely used.

Defining control budgets

An organization may draw budgets for different countries in which it operates or for its various departments. Going further, it may then define budget amounts for different areas of spending, such as IT hardware, construction of buildings, and so on. Finally, it will also need to specify the time periods for which the budget applies, such as a month, a quarter, six months, or a year.

In other words, a budget needs the following components:

1) Account, to specify expense areas such as hardware expenses, construction expense, and so on
2) One or more chartfields to specify the level of budget such as Operating unit, Department, Product, and so on
3) Time period to specify if the budgeted amount applies to a month quarter or year, and so on

Let’s take a simple example to understand how control budgets are defined. An organization defines budgets for each of its departments. Budgets are defined for different expense categories, such as the purchase of computers and purchase of office supplies such as notepads, pens, and so on. It sets up budgets for each month of the year.

Assume that following chartfield values are used by the organization:

DepartmentDescriptionAccountDescription700Sales135000Hardware expenses900Manufacturing335000Stationery expenses

Now, the budgets are defined for each period and a combination of chartfields as follows:

PeriodDepartmentAccountBudget amountJanuary 2012700135000$100,000January 2012700335000$10,000January 2012900135000$120,000January 2012900335000$15,000February 2012700135000$200,000February 2012700335000$40,000February 2012900135000$150,000February 2012900335000$30,000

Thus, $100,000 has been allocated for hardware purchases for the Sales department for January 2012. Purchases will be allowed until this budget is exhausted. If a purchase exceeds the available amount, it will be prevented from taking place.

Tracking expense transactions
Commitment control spending transactions are classified into Pre-encumbrance, Encumbrance, and Expenditure categories. To understand this, we’ll consider a simple procurement example. This involves the PeopleSoft Purchasing and Accounts Payable modules. In an organization, a department manager may decide that he/she needs three new computers for the newly recruited staff. A purchase requisition may be created by the manager to request purchase of these computers. Once it is approved by the appropriate authority, it is passed on to the procurement group. This group may refer to the procurement policies, inquire with various vendors about prices, and decide to buy these computers from a particular vendor. The procurement group then creates a purchase order containing the quantity, configuration, price, and so on and sends it to the vendor. Once the vendor delivers the computers, the organization receives the invoice and creates a voucher to process the vendor payment. Voucher creation takes place in the Accounts Payable module, while creation of requisition and purchase order takes place in the Purchasing module.

In commitment control terms, Pre-encumbrance is the amount that may be spent in future, but there is no obligation to spend it. In the previous example, the requisition constitutes the pre-encumbrance amount. Note that the requisition is an internal document which may or may not get approved, thus there is no obligation to spend the money to purchase computers.

Encumbrance is the amount for which there is a legal obligation to spend in future. In the previous example, the purchase order sent to the vendor constitutes the encumbrance amount, as we have asked the vendor to deliver the goods.

Finally, when a voucher is created, it indicates the Expenditure amount that is actually being spent. A voucher indicates that we have already received the goods and, in accounting terms, the expense has been recognized.

To understand how PeopleSoft handles this, think of four different buckets: Budget amount, Pre-encumbrance amount, Encumbrance amount, and Expenditure amount.

Step 1
Budget definition is the first step in commitment control. Let’s say that an organization has budgeted $50,000 for purchase of IT hardware at the beginning of the year 2011. At that time, these buckets will show the amounts as follows:

BudgetPre-encumbranceEncumbranceExpenditureAvailable budget amount$50,000000$50,000

Available budget amount is calculated using the following formula:

Available budget amount = Budget amount – (Pre-encumbrance + Encumbrance + Expenditure)

Step 2
Now when the requisition for three computers (costing a total of $3,000) is created, it is checked against the available budget. It will be approved, as the entire $50,000 budget amount is available. After getting approved, the requisition amount of $3,000 is recorded as pre-encumbrance and the available budget is accordingly reduced. Thus, the budget amounts are updated as shown next:

BudgetPre-encumbranceEncumbranceExpenditureAvailable budget amount$50,000$3,00000$47,000

Step 3
A purchase order can be created only after a requisition is successfully budget checked. When the purchase order i created (again for $3,000), it is once again checked against the available budget and will pass due to sufficient available budget. Thus, once approved, the purchase order amount of $3,000 is recorded as encumbrance, while the pre-encumbrance is eliminated. In other words, the pre-encumbrance amount is converted into an encumbrance amount, as now there is a legal obligation to spend it. A purchase order can be sent to the vendor only after it is successfully budget checked. Now, the amounts are updated as shown next:

Budget

Pre-encumbranceEncumbranceExpenditureAvailable budget amount$50,0000$3,0000$47,000

Step 4
When the voucher gets created (for $3,000), it is once again checked against the available budget and will pass, as the available budget is sufficient. Once approved, the voucher amount of $3,000 is recorded as expenditure, while the encumbrance is eliminated. In other words, the encumbrance amount is converted into actual expenditure amount. Now, the amounts are updated as shown next:

BudgetPre-encumbranceEncumbranceExpenditureAvailable budget amount$50,00000$3,000$47,000

The process of eliminating the previous encumbrance or pre-encumbrance amount is known as liquidation. Thus, whenever a document (purchase order or voucher) is budget checked, the amount for the previous document is liquidated.

Thus, a transaction will move successively through these three stages with the system checking if available budget is sufficient to process it. Whenever the transaction encounters insufficient budget, it is flagged as an exception.

So, now the obvious question is how do we implement this in PeopleSoft? To put it simply, we need the following building blocks at the minimum:

Ledgers to record budget, pre-encumbrance, encumbrance, and expenditure amounts
Batch processes to budget check various transactions
Of course, there are other configurations involved as well. We’ll discuss them in the upcoming section.

Commitment control configurations
In this section, we’ll go through the following important configurations needed to use the commitment control feature:

Enabling commitment control
Setting up the system-level commitment control options
Defining the commitment control ledgers and ledger groups
Defining the budget period calendar
Configuring the control budget definition
Linking the commitment control ledger group with the actual transaction ledger group
Defining commitment control transactions
Enabling commitment control
Before using the commitment control feature for a PeopleSoft module, it needs to be enabled on the Installation Options page.

Follow this navigation to enable or disable commitment control for an individual module:

Setup Financials / Supply Chain | Install | Installation Options | Products

The following screenshot shows the Installation Options – Products page:

Oracle PeopleSoft Enterprise Financial Management 9.1 Implementation

This page allows a system administrator to activate any installed PeopleSoft modules as well as to enable commitment control feature for a PeopleSoft module.

The PeopleSoft Products section lists all PeopleSoft modules. Select the checkbox next to a module that is implemented by the organization.

The Enable Commitment Control section shows the PeopleSoft modules for which commitment control can be enabled. Select the checkbox next to a module to activate commitment control and validate transactions in it against defined budgets.

Setting up system-level commitment control options
After enabling commitment control for desired modules, we need to set up some processing options at the system level.

Follow this navigation to set up these system level options:

Setup Financials / Supply Chain | Install | Installation Options | Commitment Control

The following screenshot shows the Installation Options – Commitment Control page:

Oracle PeopleSoft Enterprise Financial Management 9.1 Implementation

Default Budget Date: This field specifies the default budget date that is populated on the requisitions, purchase orders and vouchers. The available options are Accounting Date Default (to use the document accounting date as the budget date) and Predecessor Doc Date Default (to use the budget date from the predecessor document). For example, the purchase order inherits requisition’s budget date, and the voucher inherits the purchase order’s budget date.
Reversal Date Option: There are situations when changes are made to an already budget-checked transaction. Whenever this happens, the document needs to be budget checked again. This field determines how the changed transactions are posted. Consider a case where a requisition for $1,000 is created and successfully budget checked in January. In February, the requisition has to be increased to $1,200. It will need to be budget checked again. The available options are Prior Date (this option completely reverses pre-encumbrance entries for January for $1,000 and creates new entries for $1,200 in February) and Current Date (this option creates additional entries for $200 for February, while leaving $1,000 entries for January unchanged).
BP Liquidation Option: We already saw that system liquidates the pre-encumbrance and encumbrance amounts while budget checking purchase orders and vouchers. This field determines the period when the previous amount is liquidated, if the transactions occur in different periods. The available options are Current Document Budget period (liquidate the amounts in the current period) and Prior Document Budget period (liquidate the amounts in the period when the source document was created).
Enable Budget Pre-Check: This is a useful option to test the expense transactions without actually committing the amounts (pre-encumbrance, encumbrance, and expenditure) in respective ledgers. We may budget check a transaction and find that it fails the validation. Rather than budget checking and then handling the exception, it is much more efficient to simply do a budget pre-check. The system shows all the potential error messages which can help us in correcting the transaction data appropriately. Select the checkbox next to a module to enable this feature.