Introduction For Releases 11i to 12, this presentation describes the Oracle accrual processes for periodic (expense) and perpetual (inventory) Accounts Payables Accruals, also known as uninvoiced receipts or purchase clearing accruals. Accrual integration between A/P, Purchasing, WIP, Inventory and G/L is discussed. Setup and balancing steps, implementation and conversion issues, and frequent business issues are discussed. In addition, custom reporting is also explored, with SQL examples included. Scope of this Paper This paper covers Release 11i, and differences with Release 12 are noted.
To the extent possible, known bug fixes are identified, as of February 26, 2008, along with an explanation of how to correct improper setups. Much of this information can be derived from the Release 11i Oracle Purchasing Reference Manual, the Receipt Accruals Topical Essay, and sections for the Accrual Reconciliation Report, Accrual Write-Off Form, and the Accrual Write-Off Report. Please note that many changes have occurred since the Oracle Reference Manuals were published. This paper is current up to Release 11. 5. 10. 2 plus patches as noted in the paper.
Other information comes from the author’s design, development and debug experience with these accrual processes, and valuable contributions from Oracle Development teams, independent consultants and Oracle customers. Any included SQL*PLUS scripts are deemed correct but no warranties, guarantees or other liability is assumed by the author or Douglas Volz Consulting, Inc. Please note that the SQL scripts are based on Release 11. 5. 10. 2 and some will not run properly in a Release 12 environment. In Release 12 the A/P Accrual functionality has been substantially rewritten. Features beyond the scope of this paper include: Use of Release 12 Subledger Accounting Rules for the purchase order distribution and A/P accrual accounts ? Accruals and the relationship to encumbrance accounting ? Use of SQL*PLUS scripts to correct a specific customer situation ? Use of Warehouse Management System (WMS) and how this impacts inventory accounting Why Are These Accrual Processes So Important? These accrual processes monitor the accuracy of your receiving and payables processes, by indicating if you correctly invoiced what you received. If you do not know you received and paid for your goods correctly you do not know if you have correct margins (and overall profit).
These accrual processes add valuable controls for ensuring accurate inventory counts, proper invoice matching and vendor payments. If you under receive yet still pay the proper amount to your vendor, the perpetual accrual will demonstrate an out of balance. If you correctly receive your quantities, yet you do not match your invoices to the quantities received, again, the perpetual accrual account balances will not clear. So these accrual accounts provide a vital barometer for monitoring your receiving and invoice matching processes.
So many companies fail to recognize this critical control point and do not understand how the accrual accounts validate their inventory balances and invoice liabilities. To reemphasize this point, even with accurate cycle count policies, if your company does not reconcile receipts against invoices matched, or reconcile the accrual account balances, you could easily lose control of your vendor liabilities and suffer a large, expensive, write-off when you finally clean up your accruals. This is especially true if your receiving and payables matching processes have not been carefully defined, implemented, or controlled.
As an example, one manufacturing company wrote off over $10 million, because of the above process problems. In another example a distribution company lost control over what they paid their suppliers and it took them two years to regain control their receiving and payables processes. In both examples the author found significant issues with how the processes were defined and implemented. What Are These Accrual Accounts? One of the basic principles for accrual accounting is matching the benefit of receiving a good or service with its cost or expense, at the time you get the goods or services.
Usually, when you receive inventory or a vendor service like outside processing, this occurs before you get the vendor’s invoice. In order to offset the increase to Receiving, a temporary Accounts Payable accrual account is also credited, so that debits equal credits, and also allowing you to recognize the accrued liability from your vendor. These accrual entries are temporary, to be reversed out or cleared to zero when the actual invoice is entered and matched in Oracle Payables.
So at month-end, your total payables liability is both the Trade Payables Account (sometimes called A/P Control Account) plus these temporary accruals for receipts not yet invoiced. Periodic Versus Perpetual Accruals There are two types of payables accruals for uninvoiced receipts, periodic and perpetual accruals. Periodic occurs at period-and, and perpetual occurs when the goods or services are received. Typically for manufacturing and distribution enterprises, uninvoiced receipts for expense purchase orders are accrued at the end of the period, using the Oracle Purchasing Receipt Accrual – Period End process.
Inventory and outside processing receipts are always accrued at the time of receipt, thereby ensuring a perpetual balance between the inventory valuation reports and the accounting entries. The only valid reasons to accrue expense purchase orders at time of receipt are (1) government entities whose regulations require this practice, (2) commercial enterprise which operates mostly in the project environment, with project inventory and controls (such as a DOD or MOD contractor) or (3) special implementation issues.
One example occurs when the first phase of software implementation implements Oracle Financials without Oracle Inventory; you can use expense receipts and expense (inventory) items in Oracle Purchasing to represent inventory receipts, until you bring up Oracle Inventory in a later phase. Please heed the above advice! Why? If you accrue both expense and inventory purchase orders at time of receipt, you experience the following problems: 1. Receiving inspection balances will include both inventory assets and expenses.
At month-end you need to manually reclass your balance in receiving inspection to eliminate the expenses from your balance sheet. 2. You significantly increase the number of entries you need to research and reconcile in your perpetual A/P Accrual Account(s). Since your expense receipts could double the number of accrual accounting entries to process, the Accrual Reconciliation Report could take twice as long to process. And even worse, your staff responsible for researching these discrepancies may have double the accounting entries to reconcile and clear. 3.
As an example, one large manufacturing customer, was accruing both inventory and expense purchase orders. For 18 months of transactions, they had 400,000 accrual accounting entries to process, with over 200,000 entries for expense receipts. Their Accrual Reconciliation Report executed in approximately 5 to 8 hours! 4. Due to the significant increase in receipts and invoices, you could lose control of your A/P Accrual Account(s). You might be unable to cope with the accrual transaction volume. And your auditors may give you an accounting exception on top of this.
Basic Accounting Entries for A/P Accruals Starting with Release 10, you have three purchase order destination types: expense, inventory, and shop floor (outside processing). If you accrue expense receipts at period end, and you are not using encumbrances (also known as commitments in the project world), there are no accounting transactions when you receive expense purchases. However, if you accrue expense receipts at time of receipt, you will create perpetual accrual transactions similar to inventory and outside processing purchase orders.
Each purchase order line may have multiple PO destinations, and each PO destination has four stored accounts, defined when you create the PO lines and distributions. In Release 11 and 11i, these accounts are set up through the Purchasing Account Generator. Release 12 uses Subledger Accounting Rules, part of the Subledger Accounting Module. When using the standard rules with no changes to Account Generator, the accounts are: Charge Account: for expense destinations, typically an expense account, for inventory and outside processing destinations; the inventory organization’s default material account.
However, note that the charge account assigned to the PO distribution (CODE_COMBINATION_ID in PO_DISTRIBUTIONS_ALL) is not used for the Oracle Inventory delivery transaction into the subinventory or WIP job. The subinventory or WIP valuation accounts are used (if using Average, FIFO or LIFO Costing the material valuation account is used). Accrual Account: for expense destinations, the Expense A/P Accrual Account, and for inventory and outside processing destinations, the Inventory A/P Accrual Account from the inventory organization based on the “ship to” location for that PO scheduled delivery (called the PO shipment on the Oracle forms).
Variance Account: for all expense destinations whether or not you accrue expenses at period end, this is the charge account the expense is normally charged against. For inventory and outside processing destinations, this is the inventory organization’s Invoice Price Variance Account, based on the “ship to” location for that PO scheduled delivery. Budget Account: this account is used for encumbrance or budgetary accounting and is not part this paper’s scope. Perpetual Accrual Entries. With these three PO destinations, you have four basic transactions to consider.
Purchase order receipts, purchase order returns (return to vendor), invoice matches, and invoice distribution corrections. In addition, you can also perform corrections to purchase order receipts or returns. The purchase order transactions occur in Oracle Purchasing, and Oracle Payables has the invoice transactions. Additionally, if you are using inventory consignment entries, available since Release 11. 5. 10, Oracle Inventory creates consignment entries (they are called “Transfer to Regular”, indicating that the title for the consigned goods transfers from the vendor to regular ownership to the company).
See section titled “Inventory Consignment Accrual Entries” for more information about the “Transfer to Regular” transactions. The Oracle Purchasing entries for a receipt of one item with a PO unit price of 100 is: AccountDebitCredit Receiving Account100 A/P Accrual Account100 As mentioned earlier, for inventory or outside processing (shop floor) purchase orders, the A/P Accrual Account comes from the inventory organization A/P Accrual Account. The Receiving Inspection valuation account, the Receiving Account, comes from the organization’s receiving parameters, for any purchase order destination.
If you are accruing expense receipts at time of receipt, then the A/P Accrual Account comes from Oracle Purchasing Expense A/P Accrual Account. For these inventory and outside processing receipts (or for expense receipts if accrued on receipt), assuming the same quantity of 1, with an invoice price of 80, the basic invoice matching entries are: AccountDebitCredit A/P Accrual Account100 Trade Payables Account80 Invoice Price Variance Account20 Oracle Payables gets the A/P Accrual Account and Invoice Price Variance Account from the stored values on the PO distribution.
Invoice price variance equals the difference between the PO unit price and the invoice unit price, times the quantity invoiced. If there were any foreign currency gains or losses, then a separate gain or loss account would be used (from the Oracle Payables Financials Options Form, Accounting Option, Exchange Rate Gains and Losses fields). If the above entry was for an expense distribution that was accrued at time of receipt, instead of charging the Invoice Price Variance Account, Oracle Payables would hit the PO distribution Charge Account.
The Trade Payables Account comes from the Oracle Payables liability account, either from the system level or by vendor. Returns to vendor (RTV) work in opposite to purchase order receipts. For a return of the same unit, the typical RTV entries are: AccountDebitCredit A/P Accrual Account100 Receiving Account100 Oracle Purchasing correction transactions are similar to the receipt or RTV transactions, and allow you to change the quantity received or returned for a specified transaction. Oracle Payables redistribution or reversal entries correct the “charge” side of payables entry, which is the A/P Accrual Account for these perpetual accruals.
So unless you charged the wrong A/P Accrual Account or charged the wrong amount or invoice quantity, you would never want to redistribution a valid entry to the A/P Accrual Account. However, if you charged the A/P Accrual Account in error, you could do a “redistribution” entry, and credit the prior charge account, match the invoice again, and debit the new charge account. These redistribution entries are also a great way to fix UOM problems, especially if the invoice line was matched to the wrong quantity. Here is a summary of the discussed accounting entries: pic] Inventory Consignment Accrual Entries. Starting with Release 11. 5. 10. x you can now deliver consigned inventory into your WIP and recognize the transfer of ownership from your supplier. As a result, you now have a transaction in Oracle Inventory that looks very much like a PO Receipt (with an entry to the A/P Accrual Account). The overall processing steps are: ? Enter and approve a blanket purchase agreement ? Do a WIP Component Issue (backflush or manual) ? Run Create Consumption Advice (creates a blanket release)
The WIP Component Issue happens first which then drives the “Transfer to Regular” consignment entry. Sometimes this is called an “implicit consignment entry” since you don’t have to manually perform the consignment entry. The accounting entries are: Backflush or manual push into WIP: AccountDebitCredit WIP Material Account100 Staging Subinventory Material Account100 (If using Average, FIFO, or LIFO Costing you would use the Organization’s Material Account) Transfer to Regular Transaction (Inventory Consignment): AccountDebitCredit Staging Subinventory Material Account100
Inventory A/P Accrual Account100 (If using Average, FIFO, or LIFO Costing you would use the Organization’s Material Account) Periodic or Expense Accrual Entries. When you accrue expenses at month-end, you use the Receipt Accrual – Period End process in Oracle Purchasing. These temporary expense accruals are directly written into the Oracle General Ledger Interface Table (GL_INTERFACE) without corresponding accounting entries in Oracle Purchasing. The accounting entries are: AccountDebitCredit Charge Account100 Expense A/P Accrual Account100
You use the Uninvoiced Receipts Report in Oracle Purchasing to display the expense month-end accruals recorded into the G/L. In the following month, you reverse the GL batches to create reversal journal entries. To use the Uninvoiced Receipts Report, the following tips may be helpful: ? Accrued Receipts setting: o No (before running the month-end Receipts Accruals-Period End process) o Yes (after running the month-end Receipts Accruals-Period End process) ? Include On-line accruals: No ? The desired Period Name ? And the desired sort selection (purchasing Category or Vendor) pic] See the section titled “How to Research and Make Corrections to Periodic Expense Accruals” and “Expense A/P Accruals” for more information on Periodic Expense Accruals. Other Related Accounting Transactions Once you have received quantities into Receiving Inspection, you then perform a delivery transaction in Oracle Inventory, Oracle Work in Process or in Oracle Purchasing. Based on your delivery controls for your items, you may have a separate step to inspect then deliver into stock (standard receipt), or do both the PO receipt and Delivery (direct receipt) at the same time.
But whether you do a standard receipt or a direct receipt transaction the accounting entries the same. For inventory destinations (which are processed in Oracle Inventory), for the same item as above, a purchase order unit price of 100, Standard Costing with no material overhead, with a standard unit cost of 120, the accounting entries are: AccountDebitCredit Subinventory Valuation Acct(s)120 Receiving Account100 Purchase Price Variance (PPV) 20 (If you use Average or FIFO or LIFO Costing, you debit the organization level Material Account for 100, and credit the Receiving Account for 100)
If you delivered vendor services into work in process for outside processing (OSP), with a standard unit cost for 120, and you set the OSP resource to charge at standard, then the basic accounting entries are: AccountDebitCredit WIP Job OSP Valuation Account120 Receiving Account100 OSP Resource Variance (PPV) 20 Notice that the Receiving Account is increased by the Oracle Purchasing receipt transactions and decreased by deliveries to Oracle Inventory or Oracle Work in Process. So for these transactions, the Receiving Account is one huge clearing account across multiple Oracle products.
To see the accounting entries, then, you need to run multiple subledger reports in Release 11/11i. For the Receiving Subledger, run the Receiving Account Distribution Detail Report, for the Inventory Subledger run the Material Account Distribution Summary Report and for the WIP Subledger run the WIP Account Summary Report. You will find all of these reports on the standard Cost Manager Responsibility under Cost Manager => Reports => Transactions. If you are accruing expenses at time of receipt, then the delivery transaction occurs in Oracle Purchasing.
Assuming the same item, with the unit price of 100, the accounting entries are: AccountDebitCredit Charge Account100 Receiving Account100 These entries may be reported on the Receiving Account Distribution Detail Report. Setup Steps for the Accrual Processes Setup in Oracle Purchasing. On the Purchasing Options Form (Purchasing Superuser => Setup => Organizations => Purchasing Options), you set the option to Accrue Expense Items (expense destinations) at Period End or On Receipt, and also set the Expense A/P Accrual Account.
Unless you have specialized requirements, ALWAYS set the Accrue Expense Items to Period End, and set the Expense A/P Accrual Account to a unique account (the entire accounting flexfield) that is different from your Inventory A/P Accrual Account, Receiving Account, subinventory valuation accounts, or any other system account. The Accrue Inventory Items field is a display only field that always shows On Receipt. You set Accrue Expense Items to Period End so you accrue expense purchases at month-end, and you use a unique Expense A/P Accrual Account to aid in month-end reconciliation and analysis. pic] For Receiving Inspection, on the Receiving Options Form (Cost Manager => Setup => Account Assignments => Receiving Options), you set the Receiving Account. As with the A/P Accrual Accounts, set the Receiving Account to a unique account (the entire accounting flexfield) that is different from your Inventory A/P Accrual Account, Expense A/P Accrual Account, subinventory valuation accounts, or any other system account, for each inventory organization.
Unlike the other Purchasing parameters, the Receiving parameters are set separately by inventory organization. [pic] When you define your purchase orders and your PO scheduled deliveries (PO shipment lines), never manually set the Accrue On Receipt Flag. For your inventory and outside processing purchase order lines, the Enter Purchase Order Form will automatically set this to Yes. If you accrue expenses at period end, then the Accrue On Receipt Flag will automatically be set to No for your expense destinations. pic] Setup in Oracle Inventory. Using the Organization Parameters Form, in the Other Account defaults zone (Cost Manager=> Setup => Account Assignments => Organization Parameters), you define the Inventory A/P Accrual Account. If you have decentralized your Account Payables departments, or you need to separate out your A/P accrual liabilities by legal entity, then you should consider using more than one Inventory A/P Accrual Account for your inventory organizations. [pic] Standard Tools for A/P Accruals
Now that you understand the fundamental purpose for these accrual accounts, and how to set up Oracle Purchasing, Oracle Payables, and Oracle Inventory for these accrual processes, this section summarizes the available tools you can use to manage the A/P Accrual Accounts. And the subsequent sections describe how you use these tools to reconcile your accounts and how they fit into the month-end close. Inventory A/P Accruals ? Use the Accrual Reconciliation Report to reconcile and manage your Inventory (perpetual) A/P Accruals.
This report provides a detailed comparison showing how payables invoice lines match to purchase order receipts, by item, purchase order, purchase order line and release. ? Use the Write-Off Accrual Form to “mark” A/P invoice and PO receipt entries as “written-off”. By doing so you can exclude entries from the report and keep only the “real” differences on the Accrual Reconciliation Report. ? Use the Accrual Write-Off Report for your manual journal entry. When you “write-off” your entries on the Write-Off Accrual Form, no journal entry happens (not until Release 12).
Use this report as supporting backup for your manual journal entry that you need to make. The Accrual Reconciliation Report will no longer report these written-off entries; by making a manual journal entry your General Ledger balances will agree to your Accrual Reconciliation Report. Expense A/P Accruals ? Use the Uninvoiced Receipts Report as supporting backup for the A/P Expense Accruals. Every week you should run the Uninvoiced Receipts Report to ensure there are no abnormal expense accruals (errors due to incorrect received quantities for example).
Run it once more before the end of the month, BEFORE running the Receipts Accrual – Period End program (see the next section for more details on the timing for running this report). ? Use the Receipts Accrual – Period End program to create your month-end temporary expense accruals, for your expense receipts (if you do not receive your expenses on your purchase orders you will have no expense accruals). Payables has to be closed BEFORE you run this program. In the new month you will go into the G/L to reverse the G/L batches that were created (by selecting “Reverse” on the G/L batch).
As a result, each month you will re-accrue your expenses, thereby ensuring you have accrued only the open expense receipts. Running the Accrual Processes at Month-End This section describes how you use these programs and reports at month-end. Assuming you accrue expenses at month-end, and have inventory receipts, the month-end procedures are: 1. For the month you are about to close, complete all purchase order receipt processing in Oracle Purchasing and all deliveries in Oracle Inventory and Oracle Work in Process. 2. For the month you are about to close, complete all invoice processing in Oracle Payables. . Review the month-end expense accruals, using the Uninvoiced Receipts Report. 4. If the expense accruals are correct, run the Receipt Accruals – Period End program, to accrue your expense receipts. If the reported accruals need adjustment, make the appropriate transactions or adjustments before you run the expense Receipt Accrual – Period End process. See the section below for expense accrual corrections. 5. Close the Oracle Payables accounting period. You close Payables first to avoid matching a period 1 invoice against a period 2 receiving quantity. Note: many companies are not able to do this due to invoice processing delays, a functional business issue). 6. Close your Oracle Purchasing accounting period. You now cannot make any purchase order transactions in Oracle Purchasing, for any inventory organization that uses that purchasing operating unit. 7. For month-end reporting and reconciliation, run your Receiving Value Report (and Receiving Value Report by Destination Account if you accrue expenses on receipt), to get clean cut-off reports, typically just after midnight on the last day of the fiscal month. . Process any remaining inventory transactions for your inventory organizations/sites. 9. Close your Oracle inventory accounting period(s). 10. For month-end reporting and reconciliation, if you use intransit with inter-organization transactions, run the Intransit Value Report, typically just after midnight on the last day of the fiscal month. 11. For the same reasons as above, run your various Inventory Value Report and All Inventory Value Reports, using multiple sorts such as sorted by subinventory or summary reports.
And similar to the Receiving and Intransit Value Reports, you would typically run these reports just after midnight on the last day of the fiscal month. For your subinventories, you can always run the Transaction Historical Summary Report to obtain a clean cut-off report; this report allows you to specify a “rollback” date, such as the period ending date. 12. Run the Accrual Rebuild Reconciliation Report, cumulatively, using to and from date ranges. If using the newer programs in Release 11. 5. 10, you would use the Accrual Rebuild Reconciliation – No Report program.
If applicable, run the Accrual Rebuild Reconciliation Report, using an aging date, to separate out recent activity from older discrepancies. And if you are using the 11. 5. 10 features for an Incremental Rebuild, then you would only run it for the accounting period you just closed. (Using the 11. 5. 10 features you need to separately run the Accrual Reconciliation Report, as the Rebuild no longer creates report output). 13. Review the Accrual Reconciliation Report for discrepancies and errors. See section below called “How to Balance Your A/P Accrual Accounts” for more information on how to research these discrepancies. 14.
Balance the Accrual Reconciliation Report to your Oracle Purchasing and Oracle Payables subledger, and if applicable, to Oracle Inventory and Oracle Work in Process. See section below for more information on how to balance the report. 15. After researching discrepancies on the Accrual Reconciliation Report, use the Accrual Write-Off Form to manually write the discrepancies off and remove the accounting entries from the Accrual Reconciliation Report. 16. After writing off the discrepancies, rerun the Accrual Reconciliation Report without rebuilding, to examine any remaining discrepancies on the Accrual Reconciliation Report.
You don’t have to rebuild the temporary table, because the Accrual Write-Off Form updates the temporary table with the latest write-off condition. This saves you significant processing time for the Accrual Reconciliation Report. 17. Run the Accrual Write-Off Report to provide supporting detail for your manual write-off journal entry. 18. Write the manual write-off journal entry and record it in your general ledger. orders when no further transactions are required on the purchase order. Performing purchase order hard closes in error has been a serious issue for many Oracle customers.
In fact, most non-government users of Oracle do not use “final close” as it causes too many problems – you can never reopen the purchase order again. How the Accrual Reconciliation Rebuild and Report Works The processing steps are: ? Determine the install status ? Select valid accrual accounts ? Find all accrual entries and insert into a temporary table ? Matches invoices to receipts ? Determines the “write-off” condition ? Calculate PO line control totals (in Release 11. 5. 10 only done by the report submission) ? Calculate “IPV” for A/P entries not matched to POs ?
Print the report (separate report request in 11. 5. 10) The Release 11/11i A/P Accrual Account on the purchase order distribution is created by Account Generator rules and you can have multiple A/P Accrual Accounts that might not equal the system level A/P Accrual Accounts (a feature for the government customers). For Releases 11 and 11i, the accrual processes determine the valid accrual accounts by selecting the unique accrual accounts from the purchase order distributions. Expense purchase order distributions are ignored if you have set your Purchasing Options to accrue Expense Items at Period End.
All of the selected accounting entries, whether related to a purchase order or not, are analyzed and the payables invoices are matched to purchase order receipts, by purchase order line (called the Accrual Matching Process in the diagram below). In fact, the Accrual Reconciliation Report is the only place in Oracle Applications where you can determine how well you matched your invoices to receipts. Reconciliation Rebuild Report. After Release 11. 5. 9 it is called the Accrual Reconciliation Rebuild – No Report program. So in Release 11. 5. 10 and beyond you have to rebuild the information then separately run the Accrual Reconciliation Report.
Once all of the entries have been inserted into the temp table, the Rebuild program calculates how well the payables invoices match to the PO receipts and assigns the Payables Accrual Codes. The matching processes also determine the write-off condition and the net amounts on the purchase order line. Here is a diagram for showing the various sources of information and general A/P accrual processes: [pic] How to Use and Read the Accrual Reconciliation Report This section supplements the Oracle Purchasing Reference Manual for the Accrual Reconciliation Report. Background Information
The purpose of the Accrual Reconciliation Report is to report all transactions affecting your perpetual A/P Accrual Account(s), thereby giving you a subsidiary ledger to reconcile against your general ledger balances. The report has the ability to screen out purchase order activity, where the net purchase order line amounts and quantities for invoices and receipts fall within limits specified at report runtime. Up to Release 11. 5. 9, you have two ways to run the Accrual Reconciliation Report: Accrual Rebuild Reconciliation Report and Accrual Reconciliation Report.
Both run methods print the Accrual Reconciliation Report, and the report title is the same for both methods (in Release 11. 5. 10 the Accrual Rebuild Reconciliation – No Report only compiles the needed information and does not submit the Accrual Reconciliation Report). The Accrual Rebuild Reconciliation Report option gathers all A/P Accrual accounting entries from Oracle Purchasing and Oracle Payables (see Insert Transactions on the diagram above), and if installed, Oracle Inventory, and Oracle Work in Process. Once completed, these entries reside in a temporary table and remain there until you rebuild this information again.
The Accrual Rebuild Reconciliation programs will also pick up the Inventory Transfer to Regular transactions and in addition, include other inventory and WIP transactions that are coded incorrectly to the A/P Accrual Account. Once you have run the Accrual Rebuild Reconciliation programs and built the temp table, you can re-run the Accrual Reconciliation Report for selected information. For example, you might have a dispute for a certain vendor, so you would run the report for specific information on the vendor. Typically you use the Accrual Rebuild Reconciliation Report once at month-end, and use the Accrual Reconciliation Report as needed.
Since Oracle Purchasing and Oracle Payables is usually centralized functionality, this report is for multiple receiving/inventory organizations, run by operating unit (Oracle Payables and Purchasing is setup by Operating Unit; whereas Oracle Inventory is setup by inventory organization. Inventory organizations belong to a specific Operating Unit. ) In order to run the Accrual Programs, Oracle Purchasing and Oracle Payables must be installed and you must receive inventory or for special circumstances, expense receipts at time of receipt. If you do not receive inventory, or accrue expenses on receipt, there are no accrual transactions o reconcile and the Accrual Reconciliation Rebuild program errors out with a message to that effect. The report example below is an earlier 180 column version from Release 11. 5. 9, as it is difficult to show a screenshot for the 240 column version. The 240 column version includes the PO release information, as well as separate columns for entered transaction amounts. This information is missing on the earlier 180 column version. [pic] Accrual Transaction Column The Accrual Reconciliation Report uses the Accrual Transaction column to denote the transaction type for Oracle Purchasing, Oracle Inventory, and Oracle Work in Process transactions.
And for Oracle Payables transactions, the Accrual Transaction column indicates how well the invoice was matched to the receipt. Please review the following list of Accrual Transactions: Payables Transactions Payables entries are usually a positive quantity and amount, unless the entry is a reversal. This is because the Payables entries usually debit the A/P Accrual Account. A/P Line Match – an A/P invoice matching transaction for which there is a receipt against the same purchase order and purchase order line.
A/P PO Match – an A/P invoice matching transaction for which there is a receipt on the correct purchase order but against a different purchase order line. This accrual transaction tells you that your receiving department is receiving items against the wrong purchase order line or your payables department is matching invoices to the wrong purchase order line. A/P Item Match – an A/P invoice matching transaction for which there is a receipt for the item but against a different purchase order and purchase order line. This accrual transaction usually tells you that your payables department matched the invoice to the wrong purchase order.
A/P No Match – an A/P invoice that has no corresponding receipt, however, this invoice is for a valid purchase order for an item you intend to receive. If you have many of these transactions, or if the transaction date is old, you could have serious process problems with your receiving and payables departments. A/P No Item – an A/P invoice matching transaction matched to a purchase order line with an invalid item number. The item is missing from your item master, but present on your transactions. This is usually caused by incorrect data conversion from a previous legacy system.
The report lists the reciprocal of the transaction amount in the Invoice Price Variance column. A/P No PO – an A/P transaction charged to the A/P accrual account that is not matched to any purchase order. Such a transaction should typically be charged to an expense account, with frequent examples being sales tax or freight charged to the A/P Accrual Account instead of an expense account. You should redistribution the charge to the appropriate account, or you may use the Accrual Write-Off Form to remove this entry from the report. Exchange Rate Var – A/P invoice exchange rate variance charged to the A/P Accrual Account in error.
You can use the Accrual Write-Off Form to remove this entry from the report. The report lists the reciprocal of the transaction amount in the Invoice Price Variance column. Invoice Price Var – A/P invoice price variance charged to the A/P Accrual Account in error. You can use the Accrual Write-Off Form to remove this entry from the report. The report lists the reciprocal of the transaction amount in the Invoice Price Variance column. Purchasing Accrual Transactions Purchasing entries are usually negative quantities and amounts, unless the transaction is a return or correction.
This is because the receipt transaction credits the A/P Accrual Account. Receive – a receipt transaction from a purchase order into receiving. Deliver – a receipt transaction from a purchase order into receiving, and then delivered to its destination. These transactions have been available since Release 10. You should not have any deliver transactions hitting the A/P Accrual Account. However, even if the transaction is called deliver instead of receive, this has no effect on the accrual processing or on the Accrual Reconciliation Report.
Match – an unordered receipt that is subsequently matched to a purchase order. In Oracle Purchasing, you have the ability to “receive” goods even if you do not know its purchase order. When you finally matched the unordered receipt to a purchase order, Oracle Purchasing creates accounting entries. These entries are identical to a normal purchase order receipt. Return to Vendor – a purchase order return from receiving to the vendor. Inventory Accrual Transactions The Accrual Reconciliation Report lists any Oracle Inventory transaction to the A/P Accrual Account.
Unless you use the Release 11. 5. 10 “Transfer from Regular” (inventory consignment) transactions, any transactions from Oracle Inventory represent errors, mistakes that you can write off on the Accrual Write-Off Form. Typically, these errors are from miscellaneous issue and receipt transactions. Work in Process Accrual Transactions The Accrual Reconciliation Report also shows any entry from WIP that uses a valid A/P Accrual Account. Normally you will not have any entries from WIP, as WIP does not create purchasing entries to the A/P Accrual Account.
Again, use the Accrual Write-Off Form or reverse the transactions from Oracle Work in Process. Transaction Amount This column displays the transaction debit or credit from the respective subledger, and as with all amount/price columns on the Accrual Reconciliation Report, it is reported in the base currency for your set of books. If no entries were omitted from the body of the report, such as written off transactions, or entries screened out by the amount and quantity tolerances, the report total for this column would equal your subledgers.
But because you usually screen out these write-offs and other entries, the report body does not display all transactions, and the Report Total for this column will not balance to your subledgers or to your general ledger. However, the “Report Summary By Source and Accrual Transaction” section summarizes the omitted entries, thereby allowing you to use its Total summary column to balance back to your subledgers or general ledger. Invoice Price Variance This column reports all “A/P No PO”, “A/P No Item”, “Invoice Price Var”, and “Exchange Rate Var” accrual transactions in the Invoice Price Variance column.
These transactions are reported in the Invoice Price Variance column to let you know that these transactions should not be part of your accrual balance. Net Accrual Balance This column is the Transaction Amount column less the Invoice Price Variance column. If you only excluded purchase order lines that netted to zero, or written off transactions from the body of the report, then this column reflects what your general ledger balance should be for your A/P Accrual Account, including any manual journal entries for written transactions. Accrual Reconciliation Rebuild Parameters
To create your perpetual A/P Accrual information, you first run the Accrual Reconciliation Rebuild (called the Accrual Reconciliation Rebuild – No Report after you have applied the latest patches). This is a sample screenshot from Release 11. 5. 9 and 11. 5. 10 before the latest patches. It is missing the “Incremental Build” parameter: [pic] Set of Books Currency – the currency defaults from the Set of Books assigned to your responsibility used during login. Unless you are using multiple currencies reporting you cannot change this value. Title – enter a custom subtitle for your report.
Only Oracle Purchasing has this feature on their reports. Full AP Classification – choose Yes if you want to see the same A/P Accrual Codes as in earlier versions of Release 11i. Choose No if you want to use the reduced list to: A/P PO Match, A/P Receipt Match and A/P No PO. Choosing No may result in a performance increase. G/L Dates From – the beginning transaction date for your A/P Accrual Account entries, or the earliest transaction date for entries representing your A/P Accrual Account balance. Please see the section “What Can You Do If the Report is Too Large” for more information on how to use the “G/L Dates From” and “To” parameters. G/L Dates) To – the ending transaction date, usually a month-end date for your financial period, used to restrict the report so you can balance it to your general ledger. Accrual Reconciliation Report Parameters After you run the rebuild program, and especially if you are on the latest patches, you will need to run the Accrual Reconciliation Report separately (11. 5. 10 before patches runs both the rebuild and report at the same time). Here are the parameters for the Accrual Reconciliation Report. [pic] Set of Books Currency – the currency defaults from the Set of Books assigned to your responsibility used during login.
Unless you are using multiple currencies reporting you cannot change this value. Title – enter a custom subtitle for your report. Only Oracle Purchasing has this feature on their reports. Sort By – you can sort the report two ways. There are two primary sorts, by account then item, or by account then vendor. The account/item sort (aging date, account, item, and purchase order line) is the primary sort, and you use the account/vendor sort (aging date, account, vendor, and purchase order line) to help resolve specific disputes with vendors. If you specify an ging date, then a new heading appears as the first sort on the report, the aging date range. G/L Dates From – the beginning transaction date for your A/P Accrual Account entries, or the earliest transaction date for entries representing your A/P Accrual Account balance. Please see the section “What Can You Do If the Report is Too Large” for more information on how to use the “G/L Dates From” and “To” parameters. (G/L Dates) To – the ending transaction date, usually a month-end date for your financial period, used to restrict the report so you can balance it to your general ledger.
Items From – the beginning item number, used when you wish to restrict the report to a range of item numbers. Typically, you only use this restriction when you are running an interim report, to report specific information. You would not normally use the item range restrictions for your month-end cumulative report. (Items) To – the ending item number, used when you restrict the report to a range of item numbers. Typically, you only use this restriction when you are running an interim report, to report specific information. Vendors From – the beginning vendor name, used to restrict the report to a range of vendors.
Typically, you only use this restriction when you are running an interim report, to report specific information. (Vendors) To – the ending vendor name, used to restrict the report to a range of vendors. Include All Transactions – enter a Yes or No to indicate if you want to use the following amount and quantity parameter filters. If you specify Yes, then the report will ignore “Transaction Amount Tolerance” and “Transaction Quantity Tolerance” filters. If you specify No, then both these parameters/filters will be used. This parameter is printed on the cover heet of the Accrual Reconciliation Report, but the amount and quantity tolerance parameters are omitted on the cover sheet for report. Transaction Amount Tolerance – you use this parameter with the “Transaction Quantity Tolerance” to limit the information printed on the report. This feature gives you the ability to screen out small amount differences between your receipts and invoices. Every purchase order line has a net amount balance from all the receipt and invoice activity; even transactions that are not related to a purchase order has its own transaction amount as the net amount for that transaction.
If you specify an amount tolerance, the report will not print out transactions with a net balance up to the absolute amount specified for the parameter, provided the transactions were also within the “Transaction Quantity Tolerance” parameter. The summary section “Report Summary By Source and Accrual Transaction” will display the net total of all transactions screened out by the “Transaction Amount Tolerance” and the “Tolerance Quantity Tolerance” parameters, under the “Excluded Txn Total” column. This parameter has no effect on what the Accrual Rebuild Reconciliation Report actually puts into the temporary table.
It is merely screening out information from the printed report. Transaction Quantity Tolerance – again, you use this parameter to limit the information printed on the report. This feature gives you the ability to screen out small quantity differences between your receipts and invoices. Every purchase order line has a net quantity balance from all the receipt and invoice activity; even transactions that are not related to a purchase order has its own transaction quantity as the net quantity for that transaction.
If you specify an quantity tolerance, the report will not print out transactions with a net quantity balance up to the absolute number specified for the parameter, provided the transactions also meet the requirements for the “Transaction Amount Tolerance” parameter. For example, suppose you had transactions for a purchase order line with a net receipt and invoice quantity balance of 5, and a net receipt and invoice amount balance of 25. If you specify 5 for the quantity tolerance and 25 or the amount tolerance, when you run the report, all transactions for the purchase order line will not appear on the report detail. However, if you specified 25 for the quantity tolerance and 5 for the amount tolerance, the transactions in this example would still appear on the report. Since these parameters are for the absolute amount on the purchase order line, if the net purchase order line amounts and quantities were negative, or a combination of negative and positive amounts and the entered parameters were the same, you would get the same result. You have to satisfy both conditions for both parameters.
This way you can screen out both minor quantity and amount differences, and avoid the mistake of screening at only amount differences when you may have major quantity differences you still need to research. And of course, any other transactions with a net purchase order line amount and quantity balances (or if unrelated to a purchase order, the transaction amount/quantity) within the two parameter amounts, will also not be reported in the report detail. Also note that these two parameters do not look at written-off transactions; written-off transactions are handled by the “Include Written Off Transactions” parameter.
The summary section “Report Summary By Source and Accrual Transaction” will display the net total of all transactions screened out by the “Transaction Amount Tolerance” and the “Tolerance Quantity Tolerance” parameters, under the “Excluded Txn Total” column. This feature allows you to screen out detail from the body of the report, and still allow you to balance to your general ledger, by summarizing all screened out transactions. This parameter has no effect on what the Accrual Rebuild Reconciliation Report actually puts into the temporary table.
It is merely screening out information so you have less to look at. Include Written Off Transactions – used to indicate whether you want to print written off entries on the report. When you specify Yes, an asterisk appears in the far right column to indicate a written off transaction. By selecting No, you decrease the report size, and any transactions not included in the body of the report detail, is summarized in the summary section “Report Summary By Source and Accrual Transaction”, under the “Write-Off Total” column.
Please see the Accrual Write-Off Form in the Oracle Purchasing Reference Manual for more information on how to write off accrual transactions. Aging Number of Days – the aging date represents the earliest transaction date for the purchase order line, and is used to group transactions on the report. If you specify an aging date, then a new heading appears as the first sort on the report, the aging date range. For example, if you received an item on July 1, 2007 and subsequently matched an invoice to the quantity received on August 6, 2007, the aging date for the purchase order line is 01-JUL-07.
If the parameter Aging Number of Days is 30, then the report groups all purchase order lines that have the earliest transaction date in the past thirty days, and then groups the next set of lines with the earliest transaction date from 30 to 60 days, and so on. You can use this feature to group your recent activity separately from your older activity, thereby making it easier for you to research your older problems first. Tips on Running the Accrual Reconciliation Rebuild Report (and Accrual Reconciliation Rebuild – No Report Program) 1.
To efficiently use the indices for the report, always use beginning and ending date ranges when you run the report. 2. Unless you have consistently written off earlier discrepancies, and can represent your A/P Accrual Account balance with a shorter date range, always run this report cumulatively, so the Accrual Reconciliation Report will balance to your cumulative general ledger balances. Please see the section “What Can You Do If the Report is Too Large” for more information. However, please note, that you must eventually reconcile your A/P Accrual Account and manage your balances so you can use a shorter date range.
If your auditors don’t get to you first (what, you haven’t reconciled in over two years?!! ), the Accrual Reconciliation Report performance will steadily decrease, since you are processing more and more transactions each month. 3. Since this report uses a large amount of database memory to populate the temporary table, calculate various values and to print the report, you need to work with your system administrator or database administrator to set a large enough shared global area (SGA) so this report runs efficiently.
Numerous companies have significantly cut the run time (up to four times faster) by doing this. How to Research and Make Corrections to Perpetual Accruals Now you have a thorough background on the reported transactions, and the various match conditions, this section describes how you might research discrepancies. First, please understand that it is very time consuming to research why purchase order and invoice transactions did not play correctly. Anything can go wrong and usually does! The most common problem is that your quantities received don’t match your quantities invoiced.
The receipt quantity could be higher or lower than your invoice quantity. You must research why the receipt and invoice quantities are different. Common problems include: 1. Did the receiving department enter the wrong quantities and catch the error on a subsequent cycle count correction (in Oracle Inventory)? 2. Did the receiving department received goods to the wrong purchase order line or purchase order? 3. Did the payables department match an invoice to the wrong purchase order or purchase order line, creating an out of balance on another PO line? 4.
Are there other process problems with receiving or payables or is this type of problem an exception? 5. Is the receiving department trying to return goods to a vendor by using a miscellaneous issue from Oracle Inventory, and not using the purchase order RTV transaction? 6. Is the payables department processing debit memos correctly? 7. If you are seeing payables invoices with no receipts (A/P No Match) are you missing receipt entries? Did the receipts go to a different purchase order? You need to scan the Accrual Reconciliation Report for the same item, for all purchase orders. . If you have A/P No Match accrual transactions on the report, usually you start with your payables department to discover what type of invoice distribution is causing the problem. Typically, for inventory for outside processing invoices, additional charges, such as freight or tooling charges, are sometimes charged to the A/P Accrual Account in error. However, if your payables department is not matching invoices to receipts, then multiple invoices have this matching problem, and you have a large process issue. 9.
For Exchange Rate or Invoice Price Var accrual transactions, you need to check your system accounts for improper setup. Also, your payables staff could be deliberately putting in the A/P Accrual Account for the above variances. Start with the oldest discrepancies and start researching to develop transaction patterns or knowledge of process issues. Sometimes you need to get proof of delivery (pods) from your vendors to prove your receiving accuracy. And if you do not keep up with the discrepancies on the Accrual Reconciliation Report, the accrual balances could easily get out of control.
Once you have identified and researched your discrepancies, the best way to make corrections is to reverse and correctly replay the offending transaction, especially for purchase order receipts, returns, and invoice distribution matches. However, if the purchase order or purchase order lines are closed, all you can do is use the Accrual Write-Off Form to manually write off the transaction, especially since you cannot change the purchase order price after the first receipt or invoice match.
If you are using standard costing, and you have miscellaneous inventory transactions hitting the A/P Accrual Account, because the standard unit cost may have changed, you are better off using the Accrual Write-Off Form. If you use average costing, since the average cost continually changes, you are also better off using the Accrual Write-Off Form. For some companies, such as media or software distribution, routinely have over receipts when marketing collateral or printed material has small overruns from the printer. And the printer correctly invoices you for the original purchase order quantity.
The only thing you can do is to use the new quantity and amount tolerance parameters, to avoid reporting these minor quantity discrepancies. When you have consistent quantity differences between your receipts and invoices, due to a fundamental business practice, there are usually too many transactions to manually write off. How to Research and Make Corrections to Periodic Expense Accruals For expense receipts accrued at period end, the easiest way for you to remove an invalid or incorrect accrual, assuming all purchase order activity is complete, is to close the purchase order or related purchase order line.
If the purchase order should still be open, the required research is similar to the perpetual receipt accruals. You have less work to do if you run the Uninvoiced Receipts Report first, before you run the Receipt Accrual – Period End program. If you run the Receipt Accrual – Period End program first, and you need to correct the accrued entries after the fact, not only do you have to make the appropriate receipt, invoice, or purchase order adjustments (change the quantity, close the purchase order etc. you need to reverse the accrual entry just made on a manual reversing journal entry. So the usual procedure is to run the Uninvoiced Receipts Report first, make any required adjustments corrections, and then run the Receipt Accrual – Period End program. One good feature to note is the Receipt Accrual – Period End program can be run multiple times if required. processing for the invoice. The Receipt Accrual – Period End program will not reverse out earlier accruals that were invoiced later on in the same period.
Vision Operations (USA) Uninvoiced Receipts Report Report Date: 24-FEB-2008 13:50 Uninvoiced Receipts Report Example Page: 36 of 36 PO – Release: 3011 Item: f20000 Vendor: Consolidated Supplie Line Type: Goods Category: SUPPLIES. OFFICE Accrual Amount: 40,000. 00 Line: 1 Description: Paper – requires 2-way match office supp Accrual Currency: USD
Quantity/Amount Quantity/Amount Functional Accrual Amount Shipment Received Billed Unit Unit Price PO Currency Unit Price —————– ——– ————— ————— ——— ———— ———— ———— Distribution Charge Account Accrual Account ———— ———————— ———————— 1 2,000. 0 0. 00 20. 00 USD 20. 00 40,000. 00 1 2000 20 01-510-7530-0000-000 01-000-2220-0000-000 40,000. 00 PO – Release: 3016 Item: f12000 Vendor: Star Gate Ltd Line Type: Goods Category: SUPPLIES. OFFICE Accrual Amount: 36,750. 00 Line: 1 Description: Mobile phone – expensable asset (tracked Accrual Currency: USD
Quantity/Amount Quantity/Amount Functional Accrual Amount Shipment Received Billed Unit Unit Price PO Currency Unit Price —————– ——– ————— ————— ——— ———— ———— ———— Distribution Charge Account Accrual Account ———— ———————— ———————— 1 75. 0 0. 00 490. 00 USD 490. 00 36,750. 00 1 75 490 01-510-7530-0000-000 01-000-2220-0000-000 36,750. 00 Grand Total: 31,459,270. 76 How to Balance Your A/P Accrual Accounts The Accrual Reconciliation Rebuild Report (or Accrual Reconciliation Rebuild – No Report after patches) and Accrual Reconciliation Report only picks up transactions that are either available to be transferred to the general ledger interface, or already transferred to the General Ledger.
For Oracle Purchasing, when you do a receipt or RTV transaction, the transaction is immediately transferred to the general ledger interface. For Oracle Inventory, Oracle Work in Process, and Oracle Payables, you run separate posting or transfer processes. The Oracle Inventory G/L Transfer and Period Close processes transfer both inventory and work in process entries to the general ledger interface. Oracle Payables has a separate “posting” process.
Only transferred entries are picked up, to ensure the Accrual Reconciliation Report will balance to your general ledger, after you run Journal Import and post these entries to your general ledger accounts. You use the report’s “Report Summary By Source and Accrual Transaction” section to balance to your general ledger. Even if you exclude entries from the body of the report by using the amount or quantity tolerances, or exclude written off transactions, the summary section always includes these omitted entries, so the Total column should always balance to your general ledger.
If you use multiple A/P Accrual Accounts, you will still see only one report summary section, so you have to add up multiple accounts in your general ledger, in order to tie back to the report. Also, you want to balance the Accrual Reconciliation Report to your subledger reports. For the following reports, use the same date range as your Accrual Reconciliation Report, and if possible only specify transferred/posted transactions, for your A/P Accrual Account(s). For Oracle Purchasing, use the Receiving Account Distribution Report.
For Oracle Payables, use the Account Analysis Report with Payables Detail. For Oracle Inventory, use the Material Distribution Summary Report, and for Oracle Work in Process, use the WIP Account Summary Report. The Appendix also lists SQL*PLUS scripts that you could also use, to summarize your Accrual Reconciliation Report by account, your subledgers and to summarize your G/L by Journal Source and Category. (Note: free for use as long as you acknowledge the author in your documentation. )
What to do When Your Accrual Reconciliation Report Doesn’t Balance If the Accrual Reconciliation Report doesn’t balance to your general ledger, then first look for accounting entries trapped in the general ledger interface table. If all entries have been posted into the general ledger, then see if the subledgers balance to the report. If the subledgers balance to the general ledger, and all entries have been posted into the general ledger, you should ask the following questions: 1. Do I have the latest code for the subsidiary ledger reports?
This includes the WIP Account Summary Report, Inventory Material Distribution Summary Report, Receiving Account Distribution Report and the Account Analysis Report with Payables Detail (from the G/L). 2. Do I have the latest Accrual Reconciliation Report? See MetaLink Note: 427771. 1 to get the latest patches for Release 11i and MetaLink Note: 433683. 1 for the latest FAQ information. Always check with Oracle MetaLink for the latest patch information. 3. Is the Accrual Reconciliation Report picking up all of the entries it found and inserted into its temporary table (PO_ACCRUAL_RECONCILE_TEMP_ALL)?
Please see the Appendix section at the end of this paper, use the A/P Accrual Summary by Account, Source and Code Report to sum up the temporary table and then manually compare this summary to the Accrual Reconciliation Report totals on the last page. 4. If you converted invoices or purchase orders from your legacy system, ask your consultants or information resources staff if there is invalid conversion data, which is breaking the SQL*PLUS code in the report. Common conversion issues include: a. Invalid item codes b. Not setting the receipt on accrual flag correctly for the PO
Distributions (PO_DISTRIBUTIONS_ALL). Inventory and Shop Floor (outside processing) destination type codes should be set to ‘Y’, and Expense destination types should be set to ‘N’, unless accruing expenses at time of receipts. c. Need a valid PO_DISTRIBUTION_ID in AP_INVOICE_DISTRIBUTIONS_ALL. This is the only way the Oracle Applications know which PO line is used to match to the invoice line. d. REFERENCE3 in RCV_RECEIVING_SUB_LEDGER must join to PO_DISTRIBUTION_ID column in PO_DISTRIBUTIONS_ALL. What to Check if the Accrual Reconciliation Report Does Not Complete
If your Accrual Reconciliation Report does not finish and produce a report, there are three primary reasons: 1. Are your setups correct? If you are sharing your A/P Accrual Account with other system accounts, such as the Receiving Account (for your Receiving Inspection areas), or any subinventory/WIP valuation account, the Accrual Reconciliation Report will also pick up these other transactions, since they are also coded to a valid A/P Accrual Account. If the number of transactions picked up by the report is very large, then the report will not complete, it will just run and run and run and run…
Correctly fixing this situation may be difficult, since you need to change your system accounts, your purchase order distributions, and your historical receiving and payables accounting entries. This is why correct setup for your system accounts is essential! 2. Does your Accrual Reconciliation Report have enough available memory in the database to process its logic? If your shared global area (SGA) is too small, then you will have significant swapping of memory with very poor performance. 3. Do you have the latest version of the Accrual Reconciliation Report?
See MetaLink Note: 427771. 1 to get the latest patches for Release 11i and MetaLink Note: 433683. 1 for the latest FAQ information. Always check with Oracle MetaLink for the latest bug information. What Can You Do If the Report is Too Large Suppose your Accrual Reconciliation Report does produce a report. And it is huge! There are three primary reasons why this can occur. 1. You are sharing the A/P Accrual Account with other system accounts. Please see the previous section, “What to Check if the Accrual Reconciliation Report Does Not Complete”, section 1 for more information. 2.
Suppose your setups are correct, and you cannot screen out any transactions (in order to screen purchase order lines that don’t balance to zero, the needed amount and quantity tolerances are too large). When you review the Accrual Reconciliation Report, it appears that your payables invoices do not match your purchase order receipts, evidenced by the fact that when you examine your purchase order line detail on the Accrual Reconciliation Report, the net purchase order line quantities and amounts do not balance to zero, or within the quantity and amount tolerances specified when you ran the report.
This condition indicates major process problems with your receiving and payables departments. Please review the “How to Research and Make Corrections to Perpetual Accruals” section for more information. 2. Suppose your setups are correct, and you don’t have process problems with your respective receiving and payables departments. Since the Accrual Reconciliation Report is cumulative by nature, in order to reconcile your cumulative general ledger A/P Accrual Account balance, over the course of time, your report grows larger and larger.
How can you limit the size of the report, and more importantly, limit the number of entries the report processes, thereby reducing the processing time for the Accrual Reconciliation Report? There are three basic ways to limit the size of the report. First, use the amount and quantity tolerance parameters to filter out “noise” from your Accrual Reconciliation Report. In certain situations, especially with fractional quantities and partial receipts followed with partial invoices, the quantity and amount received might not exactly equal the quantity and invoiced, due to rounding issues.
For the report parameter “Include All Transactions”, enter No, and for the “Transaction Amount Tolerance” and the “Transaction Quantity Tolerance” enter appropriate numbers to represent the immaterial quantity and amount balances that you no longer want to see on the report. If you enter Yes for the “Include All Transactions” parameter, then the Accrual Reconciliation Report ignores the amount and quantity tolerance parameters. Please see the “Accrual Reconciliation Report Parameters” section for more information about these parameters.
Second, you can use the “G/L Date From” and “To” parameters to limit the size of the report by excluding older transactions which have already been resolved or written off. If you have consistently reconciled your A/P Accrual Account from your Oracle implementation going forward, with write-offs for non-matching transactions, at some point, your balance in your A/P Accrual Account is represented by your more recent transactions, as opposed to your older transactions. Third, use the Accrual Write-Off Form to limit what is reported on the Accrual Reconciliation Report.
Here is a screenshot for the Accrual Write-Off Form, based on the latest patches for Release 11. 5. 10 (Payables or Purchasing Superuser => Accruals => Write-Offs). This form is much better then the prior versions, as you can now: 1. Write-off both purchase order and payables entries at the same time 2. Mark or indicate that the entries are to be written-off as you select them 3. And you have more selection criteria: shipment number, PO distribution number and transaction id. [pic] Implementation and Conversion Issues (Mistakes You Don’t Want to Make! )
If your implementation team incorrectly sets up the various accounts and system information, or your conversion team incorrectly converts your purchase orders, purchase order lines, purchase order distributions, receipts, accounting for receipts, payables invoices and invoice distributions, then the Accrual Reconciliation Report will report incorrect values and not run properly. Conversion and implementation problems have caused companies to completely lose control of their A/P Accrual Accounts. Common conversion issues are: 1. Not correctly matching invoices to receipts.
Oracle Payables uses the PO_DISTRIBUTION_ID in the AP_INVOICE_DISTRIBUTIONS_ALL table to indicate the invoice was matched to a purchase order line and distribution. If the PO_DISTRIBUTION_ID is invalid, the Accrual Reconciliation Report will miss these entries entirely. For invoices matched to purchase orders, the PO_DISTRIBUTION_ID in AP_INVOICE_DISTRIBUTIONS_ALL must always be valid. 2. Incorrect cumulative values for the purchase order line quantity received, returned, inspected, and billed. These values on the PO_LINE_LOCATIONS_ALL table must be correct, in order for you to correctly match your invoices to your receipts. . Incorrect foreign key relationships among the Oracle Purchasing tables, such has PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL, and PO_DISTRIBUTIONS_ALL. 4. Also note that the REFERENCE3 column in RCV_RECEIVING_SUB_LEDGER table must join to PO_DISTRIBUTION_ID column in the PO_DISTRIBUTIONS_ALL table. 5. Incorrectly setting the Accrue on Receipt Flag in PO_LINE_LOCATIONS_ALL and PO_DISTRIBUTIONS_ALL. Unless you are accruing expenses at time of receipt, your inventory and shop floor/outside processing destinations should always indicate ‘Y’ and expense destinations should always indicate ‘N’.
Common implementation issues include: 1. Setting up incorrect Expense or Inventory A/P Accrual Accounts, or sharing A/P Accrual Accounts with the Receiving Account or other subinventory/work in process valuation accounts. Once you start sharing accounts, especially for the Oracle Purchasing receive, deliver, correction, and return to vendor transactions, it is extremely difficult to change the historical account number. And since the Accrual Reconciliation Report uses the historical transactions, if the accounts on these transactions are never fixed, you will never have a useful Accrual Reconciliation Report.
For another example, another manufacturing customer, with incorrect A/P Accrual Accounts on the purchase order distributions, started with a cumulative report over 2,000 pages. After correcting the purchase order distributions and historical accounting entries, the Accrual Reconciliation Report was only 500 pages for 12 months. Further refinements reduced the Accrual Reconciliation Report to 200 pages. 2. Incorrectly implementing your receiving and payables processes. The Accrual Reconciliation Report tells you exactly how you did. If you to not orrectly match invoices to receipts, the negative receipt quantity on the report, will never balance to the positive invoice quantity, and the related amounts will also never match. Consequently, you will be unable to reduce the size of the report, without potential huge write-offs, and have to endure a very large report. If you correctly match, you can set small quantity and amount tolerances, to screen out purchase order lines and reduce the report size. One manufacturing customer, with these process problems, had a 4,000 page report after only 18 months of transactions!
Nothing matched, so the report could not screen the transactions out. Current Bug Fixes for Accrual and Other Related Processes As of February 26, 2008, according to Oracle MetaLink, the current cumulative patch information is on MetaLink Note: 427771. 1. MetaLink Note: 433683. 1 has the latest FAQ information. Always check with Oracle MetaLink for the most current information. Here is a summary of common patches as of February 26, 2008: Any 11i Release ? Running out of Extents o MSG-00111: ORA-01654: unable to extend index o Get your DBAs to look at tablespace for Purchasing / PO_ACCRUAL_RECONCILE_TEMP_ALL THE ACCRUAL WRITE-OFF REPORT (POXACWRO. RDF) HAS ADDITIONAL LINES BETWEEN THE ROWS o Get the patch from bug number 5943445 Release 11. 5. 10 and 11. 5. 10. 2 (Note: 433950. 1, always check MetaLink) ? Wrong Printer Setup – Needs Landscape240 o APP-FND-00314: Invalid printer and print style combination o REP-1212: Object ‘Body’ is not fully enclosed by its enclosing object ‘P_title‘. o Accrual Reconciliation Rebuild Errors with REP-1212 Even After Applying Latest Patches (Note:433950. 1) Release 11. 5. 10 and 11. 5. 10. 2 (Note: 456406. 1, always check MetaLink) ? Transfer To Regular” Inventory Consignment Entries Not Showing Supplier PO Information on the Accrual Reconciliation Report o Steps to Replicate: – Enter and approve a blanket purchase agreement – Do a WIP Component Issue (backflush or manual) – Run Create Consumption Advice (creates a blanket release) – Run transfer to GL from inventory – Run Accrual Reconciliation Rebuild – No Report – Run Accrual Reconciliation Report – The report has no supplier name and no PO numbers for these transactions
Release 11. 5. 9, 11. 5. 10 and 11. 5. 10. 2 (always check MetaLink) ? Latest Cumulative Patch – Note:427771. 1 (Fixes as of January 9, 2008) o Apply Patch 5109317 (“CONSOLIDATED FIXES FOR THE ACCRUAL RECONCILIATION REPORT”). o Apply Patch 5161329 (“CST:POXACRCR ACCRUAL REBUILD RECONCIL RPT ERRORS FOR A LARGE PERIOD”). o Apply Patch 5200436 (“Consigned Inv consolidated bugfix patch — CORRUPT CONSUMPTION ADVICE RELEASES CREATED”). o Apply Patch 5715136 (“ACCRUAL RECON REPORT (POXACREC) DOES NOT DISPLAY THE BLANKET RELEASE NUMBER”). Apply Patch 6618320 (“POXACREC GENERATES OLD DATA WITH NLG CURRENCY”) Enhancements or Custom Solutions to Consider The following are customization and enhancement ideas to consider for Release 11i and beyond. 1. Add the ability to exclude closed purchase orders and lines from the Accrual Reconciliation Report. Doing this will enable you to cut down the report and temporary table size in a consistent fashion. So you will be able consistently exclude receipts and invoices for a given purchase order line, regardless of when the transactions occurred.
If you try to exclude information by using the transaction date (G/L Date parameter), you could end up excluding only part of the entries for a given purchase order line, especially since the invoices usually lag behind the receipts. 2. Modify the Uninvoiced Receipts Report to sort and total by expense account. This report supposed to justify your expense accrual entries. Also consider reformatting the report for ease of use. Without these modifications, this report is very difficult to use. 3. Modify the Accrual Write-Off Form to write directly the GL_INTERFACE table.
This modification would eliminate a manual journal entry. 4. Create the ability to do mass write-offs. Using the insert logic from the Accrual Write-Off Form, you can create a SQL*PLUS script to write off certain discrepancies all at once. 5. Instead of writing off transactions individually, write them off on a net basis. So instead of holding the written off transactions individually in PO_ACCRUAL_WRITE_OFFS_ALL, add the INVENTORY_ITEM_ID column to AP_INVOICE_DISTRIBUTIONS_ALL, and upgrade the PO_ACCRUAL_WRITE_OFFS_ALL information into AP_INVOICE_DISTRIBUTIONS.
To the best of the author’s knowledge, Release 12 has addressed the Accrual Write-Off Form and Accrual Mass Write-Offs enhancements. Ideas left to consider are: 1. Automatically writing off PO line/distribution balances once the purchase order line or distribution is closed 2. Modifying the Uninvoiced Receipts Report to sort and total by expense account and to have a better layout 3. Excluding closed PO lines and distributions from the accrual processes Summary for Release 12 Enhancements This section lists the primary enhancements for Release 12.
Note that the underlying functionality was substantially rewritten in Release 12. ? Each month’s accrual entries are loaded incrementally o No more cumulative loading each month for the Accrual Temp table (no longer temporary) o PO and A/P entries which balance to zero are matched and removed o Much better performance and less tablespace too ? Separately define what are valid A/P Accrual Accounts – use the Inventory Organization Parameters not the distinct values found from the purchase order distributions o Easier to exclude invalid PO and A/P entries Matching is done at the PO distribution level (effectively at the PO Release level when using PO Releases) ? Better Reporting o Summary and Detail Accrual Reconciliation Reports o Summary reporting by account o Detail reporting with separate headings from report detail o Uses BI (XML) Publisher – reports can be downloaded into Excel o Separate reports for matching vs. non-matching miscellaneous entries (A/P, Inventory entries) ? Write-Off Improvements o Ability to do mass A/P Accrual Write-Offs Write-offs use SLA (sub-ledger architecture) and now create journal entries o Can do write-offs at the PO distribution level o Better write-off reporting Where to Get More Information: Webpages from Douglas Volz Consulting, Inc. : www. volzconsulting. com ? How to Setup, Use and Balance Your A/P Accrual Accounts Powerpoint Presentation ? SQL Report Examples for Your Use (SQL scripts are free to use providing you acknowledge the author) o Inventory A/P Accruals by PO and PO Release o G/L Summary by Journal Source and Category A/P Accrual Summary by Source and Accrual Code OAUG Discrete MFG Cost Sub-SIG: http://discretesig. oaug. org/CostManagement. htm The Discrete MFG Cost Sub-SIG is an OAUG sanctioned group, dedicated to helping the Oracle user community with how to best use the discrete costing features in the Oracle Applications. Send Doug Volz an email at [email protected] net if you would like more information. Oracle Corporation o MetaLink: FAQ For On Line Accruals And Accrual Reconciliation Reports Note: 433683. 1 o Oracle Cost EBS Public Forum: http://forums. oracle. com/forums/forum. spa? forumID=414 o Oracle Purchasing User Guide Conclusions ? These accrual processes are extremely important for every company that has purchase order receipts. This control account can help you manage your inventory quantities/value and control your vendor invoices. ? Likewise, the implementation of the receiving and payables business processes is very important, especially since you want to avoid the issues documented in this paper. Do everything you can to avoid unexpected write-offs at year-end. ? For Release 11i, consider the custom reporting solutions offered in this paper.
Summary reports for your A/P Accruals and G/L balances can save lots of reconciliation and analysis time. ? For Release 12, this functional area is greatly improved. Consider using these new features when you upgrade or implement Release 12. Acknowledgements of Kind Assistance People who have contributed to or influenced these materials: ? Sharon Widmer, Manager, Hitachi Consulting ? Espen Ingvoldstad, Cost Controller, Onninen AS ? Corporate Accounting Staff at Beckman Coulter ? Michel Bazinet, Director Product Costing Strategy, Oracle Corporation ? Solution Beacon, Alicia Hoekstra, Public Vision Databases: http://www. olutionbeacon. com/tools_vision. htm About the Author Douglas A. Volz is Owner and President of Douglas Volz Consulting, Inc. , founded in 2005 with the goal of helping Oracle customers use and implement Oracle Financial and Manufacturing applications. As a Senior Architect and Advisor for Oracle Application projects, with a particular interest in Project and Cost Management, Doug has 27 years accumulated experience, including 5 years in Oracle Development (co-designing Oracle Cost Management), 12 years consulting and 12 years in industry in Cost and Accounting Management positions.
Also during Doug’s early days at Oracle, he co-designed the Accrual Reconciliation Report. Doug’s manufacturing and cost systems experience covers project management, software design/development, delivery and consulting services, for both Oracle Corporation, and multiple international consulting firms. Prior to his systems career, Mr. Volz also held numerous management accounting positions for telecommunications, defense, and electronics companies. In his consulting roles, Doug has served over 100 clients. Many of these were multi-org, multi-currency with global footprints.
Countries include US, Mexico, UK, Netherlands, Belgium, Taiwan, P. R. O. C. , Norway, Japan and Germany. These assignments focused around business process improvements enabled by Oracle technology. Outside of his consulting roles, Doug leads the Cost Sub-Committee, for the Oracle Applications User Group for Discrete Manufacturing. He also advises and participates on the Oracle Customer Advisory Board for Fusion Costing (Post Release 12). Doug may be reached at [email protected] net. Appendix Section A: How to Read the Log File The log file for the Accrual Reconciliation Report tells you how the report works!