New Labor Cost Features in Oracle R12.2

After years of complex custom forms, custom tables and very intricate extensions, Oracle R12.2 Projects can now provide functionality to support the Federal Davis-Bacon Act Requirements.

A ‘Prevailing Wage’ rate can sometimes be very difficult to determine. These ‘Prevailing Wages’ are Government mandated rates that cover a broad range of skills and competencies. Engineering and Construction is a particular vertical area that is impacted by the law. The following table below is an example of how complicated it might be to provide multiple ‘Prevailing Wage’ calculations for one person:

Now comes the more difficult part; in order to determine the ‘Prevailing Wage’, a comparison must be made between the employee’s cost rate in Oracle (for example $24 per hour) and the applicable ‘Prevailing Wage’ rates determined by the Davis-Bacon Act Labor Wage Documents (which are generally published twice a year). The rate the employee is going to get paid will be the higher of the two rates being compared (the employee’s rate in Oracle, or the specific ‘Prevailing Wage’ rate provided within the Davis-Bacon Act Labor Wage Documents). In this particular example, the employee’s work as a Welder (if using the 3rd Criteria of Grade) would pay him $25 per hour, since his Oracle rate is lower at $24 per hour. The same comparison of rates will happen for the other jobs the employee worked (such as Equipment Operator).

So, from the example above, it’s quite obvious that labor costing in Oracle Projects for Prevailing Wages has been a real challenge. In order to accomplish this type of costing in earlier releases, very detailed Labor Cost Extensions, Custom Forms and even custom tables were needed. Now, inrelease R12.2, the functionality is handled in a more standard way. A new rate source option of ‘HR’ has been added to Labor Costing Rules within Projects:

When this option is selected, the labor rates will be derived from HR Rate by Criteria matrices. Rate by Criteria is a new feature within Oracle HR that allows multiple rates to be defined for an employee based on criteria such as the assigned project or type of work (i.e. the Welder) or the location where the work occurred (Location can be used as one of the criteria.)

It’s important to note that ‘Prevailing Wages’ are not just associated with Federal Contracts. ‘Prevailing Wages’ can also apply to State, County and Municipal contracts as well. In short, this type of labor costing, where an employee can have multiple cost rates on the same day is very prevalent. Thankfully, in Oracle Projects Release R12.2, the process and functionality have made this challenging issue much easier to deal with. The added functionality of using HR as the Labor Costing Rule and associating additional rate criteria, ‘Prevailing Wage’ costing will now be a much easier process.

Implementing E-Business Tax (Considerations)

What many do not realize is that when you implement E-Business Tax, address validation for customers and supplier/vendors is enforced. And, the address validation is not controlled by Tax, but by the Trading Community Manager. This is necessary when you consider that in the U.S. there are approximately 500,000 taxing locations, including states, county, cities, and school districts just to name a few, and each of these are further delineated by postal/zip codes.

Through the Trading Community Manager, all of the tax structure is set up and all of the tax locations are set up. When using a third-party product like Taxware or Vertex, these locations are called “geocodes,” which is an alphanumeric code that represents the combination of state, county, city, and postal/school district codes. (Please note that geocodes will be used in the rest of the article and when used they just represent the combination of state, city, county, postal code and in Oracle the term tends to be used whenever you are talking about the location with the Trading Community Manger.) Address validation is required because it is impossible for the tax to calculate if it cannot find the correct address location or geocode.

The address validation has caused many issues with both customers and suppliers. And, if a customer or supplier with an invalid addresses does get in, they will error out when trying to be used and have to be manually corrected. All these issue increase time and expense on projects. One of the biggest issues is that I rarely see the county on the address in Legacy Systems including 11i upgrades. In the U.S. many of the thousands of counties have taxes.

The solution to having a successful implementation is to make sure that addresses are corrected in the legacy system so that the conversions can be successful.

Another issue with Tax is that you need to tax at all levels. There are approximately 500,000 geocode combinations, 500,000 tax codes and 500,000 tax rates. When you think about this, it’s impossible to do and maintain manually. I would recommend getting Taxware, Vertex or some other third-party vendor. These vendors provide all the geocodes, tax codes and tax rates. They also come with monthly updates for all three, as needed. For instance, if you only need state tax you could probably perform this manually as you would only need about 53 (includes territories and states) tax codes and rates. But, you would still need a geocode file, as address validation would still be on (although you have the option of changing the validations).

If you have foreign operations, it’s actually easier to set up for Vat tax because, for example, in China it used to be at the country level but moved down to the province level – that’s far fewer geocodes and only one tax rate of 17% (last time I checked). The U.K. also has very few rates, with input/output netting, limited rates and geocodes.

If you have an issue with validation you may follow below instructions on how to verify and or set up geocodes:

Make sure you have to have the Trading Community Manager responsibility.

Then you need to navigate to Geography Hierarchy. The screen below opens and enter “US” and clicking “GO” will bring up the second screen.

At this point you need to click on “View Details” and the states open up as below:

This first screen shows states. If you want to see just Florida then enter “FL” and click “GO” and the screen below shows up.

Again, to move down a screen click on “View Details” and you will see counties.

There are three counties entered for Florida. In actuality, Florida has over 400 counties. And this is because seeded value in Geocodes only include a very few for instance Brevard will give you Miami, Orange will give you Orlando. This shows that there is a need to load geocodes because seeded values usually only cover a few counties and major cities. If you see the county you are interested in, then click on it to view details for the city. If you do not see your county click on “Add 5 Rows” as shown below, then enter the value you need: I entered Osceola and then clicked “Apply”.

Now we have four counties, but we still need to view details and add a city.

Since we entered the county there will be no cities. So, click “Add  Rows” to enter the city–Orlando– then, click “Apply” and you have the screen below:

I have entered Kissimmee and hit apply now the city, state and county are setup. You can enter as many cities as you need per county. If you wanted to add more cities just click on “Add 5 Rows” again.

If postal code was needed just view details then “Add 5 Rows” and enter 34747. Click “Apply” and you will now have the postal code.

I hope by showing the steps for setting up one geocode shows why I recommend that geocodes be loaded instead of entered, so the implementations will go more smoothly.

In summary, implementing Tax causes address validation, which means addresses should be fixed in the legacy system. And as there are approximately 500,000 geocodes, tax rates and tax codes, there needs to be some mechanism for loading these items. Vertex and Taxware provide all the geocodes, tax rates and codes. There also are vendors that will supply just the geocodes. So decisions will have to be made in what direction must be taken when implementing E-Business Tax.

Payment Process Request(PPR) in R12

The 11i Accounts Payable Payment Batch functionality has been replaced by the Payment Process Request (PPR) in R12. PPR can be accessed via Payment Manager, a fund disbursement HTML page (as opposed to the prior 11i Oracle forms).

Setup Required for Payment Process Request:

The Payment Template is now the required setup for PPR and contains the following tabs:

• Selection Criteria (pay through date, payment method, payee, pay group, operating unit, legal entity and payment currency),
• Payment Attributes (disbursement bank, payment document, payment process, profile, exchange rate, type, and payment date setup),
• Processing (automation setup for how PPR should run—whether to stop the PPR at each stage of the process for user review before proceeding to another, or just to confirm the process without the review of selected and build invoices)
• Validate Failure Results (setup for PPR on how to handle a validation failure).

Users only need to select the Payment Template during PPR submission and all the selection criteria will be automatically defaulted. But, it can be overwritten by the user before submission.

Payment Process Request Submission:

A PPR can be initiated by clicking the “Submit Single Payment Request” link in the Payment Manager dashboard:

Payment Process Request Submission:

A PPR can be initiated by clicking the “Submit Single Payment Request” link in the Payment Manager dashboard:

Payment Process Request Status:

Here are the different PPR statuses and what they mean:

New : The “New” status shows that the PPR has been submitted and the Auto Select program is running.

Invoice Pending Review: PPR will show an “Invoice Pending Review” status if the “Stop Process for Review After Scheduled Payment Selection” option is enabled in the Processing automation settings. This status also indicates that the Auto Select program has been completed successfully.

Invoices Selected: After reviewing the selected invoices—once the user proceeds to the build step of PPR—the PPR status is updated to “Invoice Selected”, indicating that the selected invoices are being reserved for the current PPR.

Pending Proposed Payment Review: The “Pending Proposed Payment Review” status will appear if the “Stop Process for Review After Creation of Proposed Payments” option has been enabled in the Processing automation setup. This status also indicates that PPR is in the build step.

Formatting: The “Formatting” status takes place when Payment Instruction files are generated from a Proposed Payment. Electronic payments will usually be marked as “Formatted” at this stage. Payments by check are marked as “Formatted – Ready for Printing”, meaning the payment instruction file was created and is waiting to be sent to your printer. Once the user selects the printer then the status becomes “Printing”.  After printing is complete and then the PPR status changes to “Printed”.

Confirmed: Once the payment instructions have been transmitted/printed and confirmed, then the status gets updated to “Confirmed”, indicating successful completion of PPR.


Cancelled: If there is no invoice which meets the selection criteria, then PPR is automatically terminated and the status changes to “Cancelled”.


Terminated: The “Terminated” status indicates that the user has terminated the PPR before the confirmation.

R12 Inventory to General Ledger SQL Logic

SELECT DISTINCT glh.*
FROM xla_transaction_entities_upg xte,
xla_events xe,
xla_distribution_links xdl,
mtl_transaction_accounts mta,
xla_ae_headers xah,
xla_ae_lines xal,
gl_import_references gir,
gl_je_headers glh
WHERE 1 = 1
AND xte.source_id_int_1 = &transaction_id
AND xte.entity_id = xe.entity_id
AND mta.transaction_id = xte.source_id_int_1
AND xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
AND xdl.source_distribution_id_num_1 = mta.inv_sub_ledger_id
AND xdl.ae_header_id = xah.ae_header_id
AND xal.ae_header_id = xdl.ae_header_id
AND xal.ae_header_id = xah.ae_header_id
AND gir.gl_sl_link_table = 'MTA'
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.je_header_id = glh.je_header_id;

Oracle Fixed Assets Accounting Entries

In the Assets module, following are some of the accounts we setup. Some accounts (like Clearing) are used by other modules as well.
Note: All of these accounts are natural accounts, and are created as flexfield values for the natural account (GL account) segment.

	Account	Segment Qualifier
1	Asset Cost	Asset Account
2	Asset Clearing	Asset Account
3	Depreciation Expense	Expense Account
4	Accumulated Depreciation	Contra-Asset Account
5	Deferred Depreciation Reserve	Liability Account
6	Deferred Depreciation Expense	Expense Account
7	Depreciation Adjustment	Expense Account
8	Proceeds of Sale Clearing	Asset Account
9	Cost of removal Clearing	Liability Account
10	Gain & Loss	Revenue Account

Here are the entries made by FA.

Transaction	Natural Account	Debit	Credit
Asset is added	Asset Cost	$100
	Asset Clearing		$100
Asset Depreciation	Depreciation Expense	$100
	Accumulated Depreciation		$100
Asset Retirement – W/o proceeds and W/o removal cost	Accumulated Depreciation	$60
	Gain/Loss	$40
	Asset Cost		$100
Asset Retirement – With Proceeds, W/o Cost of removal	Accumulated Depreciation	$60
	Gain/Loss	$40
	Gain/Loss		$10
	Proceeds from Sale	$10
	Asset Cost		$100
Asset Retirement -  With Cost of removal	Accumulated Depreciation	$60
	Gain/Loss	$40
	Gain/Loss	$5
	Cost of removal Clearing		$5
	Asset Cost		$100

Accrual Process for Period-End Accruals

Accrual Process for Period-End Accruals

Key points for accruing expense purchases at period-end include:

• You record the total uninvoiced receipt liabilities accrued during the accounting period.

• Actual journal entries are created for the amount of the receipt liabilities, debiting the charge account and crediting the PO distribution accrual account (normally the Expense A/P Accrual Account defined in the Define Purchasing Options form).

• You reverse accrual journal entries manually at the start of the new accounting period.

• If you are using encumbrance accounting, purchase order encumbrance is relieved when the invoice(s) matched to the purchase order are posted to the general ledger.

Receiving Transactions

Purchasing does not record any accounting entries for expense during a receiving transaction if you use period-end accruals. You record all of your uninvoiced liabilities at month end using the Receipt Accruals - Period-End process. See: Receipt Accruals - Period End Process

Receipts Accruals-Period End

Use the Receipt Accruals - Period End process to create period-end accruals for your uninvoiced receipts for expense distributions. Purchasing creates an accrual journal entry in your general ledger for each uninvoiced receipt you choose using this form. If you use encumbrance or budgetary control, Purchasing reverses your encumbrance entry when creating the corresponding accrual entry.
Purchasing never accrues an uninvoiced receipt twice. Each time you create accrual entries for a specific uninvoiced receipt, Purchasing marks this receipt as accrued and ignores it the next time you run the Receipt Accrual - Period-End process. Purchasing creates accrual entries only up to the quantity the supplier did not invoice for partially invoiced receipts.
Purchasing creates the following accounting entries for each distribution you accrue using the Receipt Accruals - Period-End process:

Account	Debit	Credit
PO charge account @ Uninvoiced Quantity * PO Price	XX
Expense A/P accrual account @ Uninvoiced Quantity * PO price		XX

As soon as you open the next period, Purchasing reverses the accrual entries using the following accounting entries:

Account	Debit	Credit
Expense A/P accrual account @ Uninvoiced Quantity * PO price	XX
PO charge account @ Uninvoiced Quantity * PO Price		XX

Match, Approve, and Post an Invoice

When you enter an invoice in Payables, you match each invoice line to a specific purchase order shipment in Purchasing. You can set up Payables to ensure that you pay only for the quantity you received. If you accrue your uninvoiced receipts at period-end, Payables records the expense transactions part of the accounting transactions:

Account	Debit	Credit
PO Distribution Charge Account @ Invoice Quantity * PO Price	XX
PO Distribution Variance Account @ Invoice Quantity * (Invoice Price - PO Price)	XX
A/P Liability @ (Invoice price * Invoice Quantity)		XX

Attention: Normally, you charge the original expense account for any invoice price variances, so your PO distribution variance account is the same as the PO distribution charge account. You do not record invoice price variances for expense purchases. Purchasing uses the Account Generator to set your purchase order distribution variance account to be the same as your purchase order charge account. If you want to record your invoice price variances to a separate account, use the Account Generator to define the business rules you use to determine the correct invoice price variance account.

Complete Period Transactions

If you use encumbrance or budgetary control, Purchasing creates encumbrance journal entries in your general ledger each time you approve a purchase order. Similar to accrual journal entries, encumbrance journal entries recognize a liability towards your supplier before any invoicing transactions occur. Unlike accrual journal entries, encumbrance journal entries are not actual transactions. General Ledger tracks actual and encumbrance journal entries and balances separately.

Period-End Checklist

Purchasing provides you with complete flexibility and control for your period-end accruals. You can use the Uninvoiced Receipts Report to analyze your uninvoiced non-inventory receipts before you accrue these receipts. You can then use the Receipt Accruals - Period-End process as many times as you want to generate accrual entries for the receipts you choose.
For your period-end reconciliation, you should perform the following steps:

1. Identify the purchasing period you want to reconcile and close.

2. Enter all receiving transactions for goods and services you received during the period. Purchasing automatically creates receipt accruals for all receipts you entered up to the end of this period. To prevent any period-end disruption, Purchasing lets you provide a receipt date that is different from the date you enter the receipts. You never have to enter all the receipts for a period before the end of this period. You can enter these receipts later. You simply need to back date the receipt date.

3. Enter and match all invoices you received during the period for your receipt accrual entries. You should make sure that you solve all posting holds problems in Payables before accruing receipts. Purchasing creates accrual journal entries for all purchase orders you received and did not match to an invoice. If you matched a purchase order to an invoice, Purchasing does not accrue the corresponding receipts. Purchasing does not accrue any purchase order that you closed on or before the end of the accrual period you choose. If the invoice is on posting hold, Payables has not yet accounted for the liability corresponding to the invoice. Under these conditions, the liability corresponding to this invoice would not appear in your books for the period. Payables lets you recognize this liability in the following period.

4. Close your accounts payable period corresponding to the purchasing period for your receipt accrual entries.

Note: The List of Values for period end accruals does not require the Accounts Payable period to be closed, however it's strongly recommended that closed periods are used, as the receipt accruals process will not pick up invoices entered after the accruals process is run for the period.rcvaccov

5. For period-end accruals of expense purchases, run the Uninvoiced Receipts Report. Use this report to analyze your uninvoiced receipts. The Uninvoiced Receipts Report lets you use the same selection criteria for your uninvoiced receipts as the Receipt Accruals - Period-End process. You always know exactly what you accrue and for what amount.

6. For period-end accruals of expense purchases, use the Receipt Accruals - Period-End process as many times as you need. You can use the search criteria to choose what you want to accrue and accrue your receipts steps by steps. You create accruals for a specific purchasing period. Purchasing automatically accrues all uninvoiced receipts your entered up to the end of the accrual period you specify. See: Receipt Accruals - Period End Process.

Each time you use the Receipt Accruals - Period-End process, Purchasing creates an unposted journal entries batch in your general ledger for your receipt accruals. If you are using encumbrance, Purchasing creates another journal entries batch in your general ledger corresponding to the encumbrance reversal entries for the uninvoiced receipts you accrued.

Purchasing never accrues your uninvoiced receipt twice. Each time you create accrual entries for a specific uninvoiced receipt, Purchasing marks this receipt as accrued and ignores it the next time you use the Receipt Accruals - Period-End process. Purchasing creates accrual entries only up to the quantity your supplier did not invoice for your partially invoiced receipts.

7. Post Accrual and Encumbrance Reversal journal entry batches in your general ledger (See the following section to identify Accrual and Encumbrance Reversal journal entry batches.)

8. Perform all the steps you need to close your accounting period and generate your period-end reports and financial statements in your general ledger.

9. Use your general ledger system to reverse all the receipt accrual and encumbrance reversal batches you created for your period-end accruals.

10. Close the purchasing period for your receipt accruals. When you close a purchasing period, Purchasing automatically un-marks all the receipts you previously accrued to make sure you can accrue these receipts again if they are still uninvoiced in the next period. See: Uninvoiced Receipts Report.