oracle r12 a/p accruals oaug cost mgmt sig meeting march 15, 2011

Oracle R12 A/P Accruals

OAUG Cost Mgmt SIG MeetingMarch 15, 2011

Douglas VolzDouglas Volz Consulting

[email protected]

Introduction Learn about the Oracle inventory and expense

accrual processes for Release 12

Find out about the available improvements

Discuss the underlying business processes

Discuss current problems you may have

Slide 2

Agenda Welcome to Our Session!

Cost Management SIG Business

Release 12 A/P Accruals

Why are these accrual processes so important? Periodic vs. perpetual accruals Basic accounting entries for the A/P accruals Setup steps for the accrual processes Using the Accrual Reconciliation Reports Using the expense accrual processes Summary of Release 12 Enhancements

Conclusions

Slide 3

OAUG Cost Management SIG Business Collaborate11 – April 10th to 14th

OAUG Cost Mgmt SIG MeetingApril 11, 2011, 9:15am to 10:15am Room W311H

Oracle OpenWorld Call for Papers – March 9th to March 27th

OpenWorld 2011 October 2-6, 2011 Call for papers url:

https://oracleus.wingateweb.com/portal/cfp/cfpLogin.ww;jsessionid=abcKMIA3cEszN7U97gH6s

Open vacancy for OAUG Cost SIG Membership Chairperson

Questions, concerns, ideas for the OAUG Cost Mgmt SIG: Doug Volz [email protected] Jerry Devore [email protected] Sharon Widmer [email protected]

Slide 4

https://oracleus.wingateweb.com/portal/cfp/cfpLogin.ww;jsessionid=abcKMIA3cEszN7U97gH6s

mailto:[email protected]



Release 12 A/P Accruals

Slide 5

Why Are These Accrual Processes so Important?

Valuable integration control

Monitors accuracy of receiving and payables processes

Provides a vital barometer over these activities

Helps to ensure accurate inventory counts

Ultimately affects your profits

Slide 6

What Are These Accrual Accounts?

Principle of accrual accounting

Timing difference between receiving and invoicing

Represents the accrued liability from your vendors or suppliers

Slide 7

Periodic Versus Perpetual Accruals

Periodic accruals occurs at month-end

With computers came perpetual accruals

Perpetual accruals ensure that valued quantities equals accounting entries

Don’t accrue expenses upon receipt (unless you are a governmental agency or heavily into Project Control)

Slide 8

Why Not Use Perpetual Accruals for Expenses?

Receiving inspection then includes both assets and expenses Have to reclass balances

Doubles the accrual entries that you need to reconcile Significantly more entries to review, research,

and correct Increased processing for the Accrual

Reconciliation programs

Slide 9

Background Information for A/P Accruals

May have multiple lines per PO

May have multiple scheduled receipts and PO releases per line

May have multiple PO distributions per line or scheduled receipt

Four stored accounts per PO distribution

PO HEADERS(PO_HEADERS_ALL)

PO LINES(PO_LINES_ALL)

PO SHIPMENTS(PO_LINE_LOCATIONS_ALL)

PO DISTRIBUTIONS(PO_DISTRIBUTIONS_ALL)

PO RELEASES(PO_RELEASES_ALL)

Slide 10

More Background Information

Slide 11

Type of Account

Charge Account

Accrual Account

Variance (IPV) Account

Budget Account

Expense Destination

Inventory / WIP Destination

Cost Center or Expense Account

Default Subinv. or Org Mat’l

AcctExpense A/P

Accrual Account

Inventory A/P Accrual Account

Cost Center or Expense Account

Inventory IPV Account

Expense Budget Account

Default Subinv/Org

Encumbrance Account

Accrual Accounting Entries – Perpetual Accruals Releases 10 to 11.5.9

Purchase order receipt A/P Invoice match PO Return to vendor A/P Invoice correction PO Receipt delivery Expense A/P accrual

Release 11.5.10 Transfer to Regular (consignment) Internal Orders / Logical Transactions Retroactive Price Adjustments

Release 12

Slide 12

Accounting Entries for a PO Receipt Receive one item @ $100 PO unit price

Account Debit CreditReceiving Account 100

A/P Accrual Account 100

Receiving Value Account Inventory A/P Accrual Account 100

100

Slide 13

Accounting Entries for an A/P Invoice Match Same item, $80 invoice unit price, $100 PO unit

price

Account Debit CreditA/P Accrual Account 100

A/P Trade Payables 80Invoice Price Variance 20

A/P Accrual Account A/P Trade Payables 100 80

Invoice Price Variance

20 Slide 14

Accounting Entries for a PO Return to Vendor

Same item, $100 PO unit price

Account Debit CreditA/P Accrual Account 100

Receiving Account 100

A/P Accrual Account Receiving Account

100 100

Slide 15

Accounting Entries for an A/P Invoice Correction

Same item, $80 invoice unit price

Account Debit CreditNew Charge Account 80

Old Charge Account 80

New Charge Account Old Charge Account

80 80

Slide 16

Accounting Entries for PO Receipt Deliveries

Account Debit CreditDelivery to stock inventory (PO Receipt)Material (Subinv/Org) AccountXXX

Receiving Account XXX

Delivery to WIP inventory (Outside Processing Receipt)

Job OSP Account XXX Receiving Account XXX

If using standard costing you may have PPV entries as well

Slide 17

Purchase Order Accounting Accrual Summary by T Account

Receiving Value Inventory A/P Accrual

100 100 100 100

Material Value Invoice Price Variance

100 20

Trade Payables

80

TransactionReceipt Delivery to

Stock Invoice Match

Delivery to Stock

Invoice Match

Invoice Match

Slide 18

Accounting Entries for Inventory ConsignmentAccount Debit CreditDelivery to stock inventory (PO Receipt for ”Transfer to

Regular”)Material (Subinv/Org) AccountXXX

AP Accrual Account XXX

Transfer to WIP inventory (Backflush from Subinventory above)

Account Debit CreditWIP Material Account XXX

Material (Subinv/Org) Account XXX

Available feature since Release 11.5.10

Slide 19

Accounting Entries for Internal Orders

Receipt into Inventory (Internal Order Intransit Ship)Account Debit CreditSubinv. or Org Accounts XXX

AP Accrual Account XXX

Intercompany Invoice Match (Payables)Account Debit CreditI/C A/P Accrual Account XXX

Trade Payables Account XXX

Reported on Miscellaneous Accrual Report

Slide 20

Month-End Period Accruals

For expenses onlyAccount Debit CreditCharge Account XXX

Expense A/P Accrual Account XXX Release 11i, run the Receipt Accruals Period End program

Creates entries directly into the G/L Interface lines Then run Journal Import

Release 12, run the Receipt Accruals Period End program Creates Receiving Accounting Distributions Then run Create Accounting Program to create SLA entries and Run Transfer Journal Entries to G/L program, or (optionally) transfer to

G/L at same time as Create Accounting is run

Next accounting Period, reverse the Accrual: R11i: In the G/L find the G/L Batch Header and click on Reverse

Batch R12: The reversal entry is automatically done for you

Slide 21

Where You Setup the Accrual Processes

Setup in Oracle Purchasing

Setup in Oracle Inventory

Slide 22

Setup in Oracle Purchasing Use the Define Purchasing Options Form

Set the Expense Accrual Account

Set the Accrual Options for Expenses

Slide 23

Setup in Oracle Purchasing Use the Define Receiving Options for each Org:

Receiving Account

Retroactive Price Adjustment

Slide 24

When Defining Purchase Orders Much safer, harder to find the Accrue on Receipt Flag

You have to set up the Accrue on Receipt field on your folder form before you

can see the Accrue on Receipt settings

Slide 25

Setup in Oracle Inventory Organization Parameters ─ Inventory AP Accrual

Account

Set your Inventory A/P Accrual Account

Set your PPV and IPV Accounts

Slide 26

Setup in Oracle Inventory (cont’d)

Slide 27

Separate inventory and expense accrual accounts

for intercompany A/P accruals

Intercompany Accrual Accounts

New R12 Setup For A/P Accruals List of A/P Accrual Accounts to Reconcile

Slide 28

Caution

Use separate accounts for each type of system account: Expense A/P Accrual Account Inventory A/P Accrual Account Receiving Account Expense Purchase Order Charge Accounts Other inventory, WIP and price variance accounts

Teach Payables staff to not use the A/P Accrual Account This account should be always assigned automatically Or set up G/L security rules to prevent use

Slide 29

R12 Accrual Reconciliation and Write-OffMajor Enhancement

11i issues Poor Performance

Full data load causing years of accrual distributions being loaded and reloaded again

Inflexibility and Confusion Forms and Reports are data

dumps. Difficult to identify the transaction to write off

Data can not be exported in excel format.

Possibility for Errors Manual write-off to GL can

lead to errors and mistakes No Summary Reporting

R12 Enhancements Enhanced Performance

Incremental data load using from and to date to load data with outstanding accrual balances only

Support writing off balances Increased Usability

Headers and lines separation Summarization display AP PO data only Miscellaneous data only Data exportable to excel Will help user for investigation Mass write-off

Automatic Accounting Creation No more manual adjustment to GL Write-off will post to SLA as any

other distributions

Slide 30 Slides courtesy of Oracle Cost Development

R12 Accrual Reconciliation and Write-Off Major Enhancement Recap

Performance enhancement: Incremental load Accrual data only with outstanding balance

Usability enhancement: Information categorized using specific windows and

reports• AP and PO Accrual data• Miscellaneous AP and Inventory

Writing off accrual balance at PO distribution Ability to export Accrual data reported in excel format Mass write-off

Remove possible financial mistakes: Automatic accrual write-offs posting to the GL


New R12 Table Structure

Permanent table structures

CST_ACCRUAL_ACCOUNTS (holds valid accrual accounts)

CST_AP_PO_RECONCILIATION (matched AP/PO entries w/outstanding balances)

CST_MISC_RECONCILIATION (unmatched AP and INV entries)

CST_RECONCILIATION_BUILD (accrual load submissions)

CST_RECONCILIATION_SUMMARY (summary of CST_AP_PO_RECONCILIATION)

CST_WRITE_OFFS (write-off transaction header table)

CST_WRITE_OFF_DETAILS (write-off transaction details table)

Slide 32

R12 Accrual Reconciliation and Write-Off Functional FlowFunctional Flow


R12 Accrual Reconciliation and Write-Off Functional FlowFunctional Flow: Define your Accrual Account


R12 Accrual Reconciliation and Write-Off Functional FlowFunctional Flow: Run Accrual Load Program


R12 Accrual Reconciliation and Write-Off Functional Flow: Accrual Load ProgramFunctional Flow: Accrual Load Program

Accrual data load run as a standalone concurrent program

Incremental load: Load is run for a specific date range. Transactional data picked up only from the data range specified

Accrual data is summarized and accrual balances are calculated at the PO distribution level

- PO, A/P and Write Off balances as well as last activity date

- Zero balance accrual data are deleted from the table, this should eliminate 80%+ of the data


R12 Accrual Reconciliation and Write-Off Functional Flow: Run Summary Accrual Reconciliation ReportFunctional Flow: Run Summary Accrual Reconciliation Report


R12 Accrual Reconciliation and Write-Off Functional Flow: Functional Flow: Summary Accrual Reconciliation

Step3 Report: Summary Accrual Reconciliation


R12 Accrual Reconciliation and Write-Off Functional Flow: Run AP and PO Accrual Reconciliation ReportFunctional Flow: Run AP and PO Accrual Reconciliation Report


R12 Accrual Reconciliation and Write-Off Functional Flow: AP and PO Accrual Example - Reconciliation Report…Functional Flow: AP and PO Accrual Example - Reconciliation Report…

We decide to write this balance off for these

un-billed receipts


R12 Accrual Reconciliation and Write-Off Functional Flow: AP and PO Accrual Example - Write-Off Window…Functional Flow: AP and PO Accrual Example - Write-Off Window…

Submit the Write Off and the balance disappears online

We begin by looking for the PO balance we want to write off…


R12 Accrual Reconciliation and Write-Off Functional Flow: AP and PO Accrual Example - Functional Flow: AP and PO Accrual Example - AP PO Write Off…

If you resubmit the AP PO Accrual Reconciliation report…

….The PO#5418 disappeared


R12 Accrual Reconciliation and Write-Off Functional Flow: AP and PO Accrual Example - Functional Flow: AP and PO Accrual Example - View Write Off for AP PO…

All Write off transactions for the PO Distribution balance will be displayed

If you query the write off View Window, you will find your write off transactions…


R12 Accrual Reconciliation and Write-Off Functional Flow: AP and PO Accrual Example - Functional Flow: AP and PO Accrual Example - Write Off Reversal …

A write-off reversal transaction has been created to offset the previous write off

You can reverse the write off transaction if you think error has been made…


R12 Accrual Reconciliation and Write-Off Functional Flow: AP and PO Accrual Example - Functional Flow: AP and PO Accrual Example - Write Off Reversal…

The PO balance is again available for write off

From AP PO Write Off Window, the PO balance re-appears…


R12 Accrual Reconciliation and Write-Off Functional Flow: AP and PO Accrual Example - Functional Flow: AP and PO Accrual Example - Write Off Reversal…

The PO balance shows up again

AP PO Accrual Reconciliation Report shows the PO balance again…


R12 Accrual Reconciliation and Write-Off Accrual Reconciliation Process: Accrual Data life Cycle

Accrual Reconciliation Data

Removed ó

DeletedUser Write Off the balance

Written Off Transaction Data

Createdó

Inserted

Reversedó

Inserted the opposite Write Off

User Reverse the Write Off transaction

Createdó

Inserted

Trigger the creation of the Write Off transaction to represent the

amount being written off

Recreate the Accrual Reconciliation data being previously written off

IncrementalLoad

Load of Accrual DataWith outstanding

balance


R12 Accrual Reconciliation and Write-Off Accrual Reconciliation: Major Benefits

Write Off Activity is tracked by a Write Off transaction Enhance the control of the write off activity Enhance audit ability

Only accrual data with outstanding balance will be kept in the accrual reconciliation tables Decrease the volume of data Enhance the performance Enhance the usability


R12 Accrual Reconciliation and Write-Off Functional Flow: Run Miscellaneous Accrual Reconciliation ReportFunctional Flow: Run Miscellaneous Accrual Reconciliation Report


R12 Accrual Reconciliation and Write-Off Miscellaneous Accrual Reconciliation ReportMiscellaneous Accrual Reconciliation Report


R12 Accrual Reconciliation and Write-Off Miscellaneous Accrual ReconciliationMiscellaneous Accrual Reconciliation: Miscellaneous Write-Off Window


R12 Accrual Reconciliation and Write-Off Write Off transaction Accounting: Leverage Sub Ledger Accounting

Upon completion of the process the accounting report will show the status of the accounting

To post the accounting to the GL, we can execute the “Create Accounting – Cost Management” process with the process category “Receiving”…


R12 Accrual Reconciliation and Write-Off Write Off transaction Accounting: Leverage Sub Ledger Accounting – Continue…

Once posted the SLA accounting entries are viewable online in the view write off Window…


Summary Comparison for R11i and R12 R11i: Inventory A/P Accruals

Accrual Reconciliation Report – detail report Write-Off Accrual Screen/Form Accrual Write-Off Reports

R12: Inventory A/P Accruals Accrual Reconciliation Detail and Summary Reports Write-Off Accrual Screen/Form

• Separate write-off forms for AP/PO vs. Inventory transactions• When you write off entries you create Write-Off journal entries

Accrual Write-Off Reports New Reports

• AP and PO Accrual Reconciliation Report• Summary Accrual Reconciliation Report• Miscellaneous Accrual Reconciliation Report

Slide 55

How to Balance the Accrual Reconciliation Report

Only picks up transactions available to the general ledger Create Accounting – Final mode has been run

Use Subledger Accounting or G/L reports to reconcile Subledger Accounting – Journal Entries Report Account Analysis Reports General Ledger summary and journal line reports

Or use original accounting distribution reports

Receiving Account Distribution Report Payables Account Analysis Report Material Distribution Summary Report

Slide 56

What to Check If the Perpetual Accruals do Not Balance

Check for any conversion issues

Check for manual G/L journal entries

Make sure create accounting has been run

Slide 57

Process Problems for Receiving

Why receipts do not equal invoices

Wrong receipt quantities (quantity and/or UOM is incorrect)

Receiving goods to the wrong PO line or PO

Trying to receive or return goods using miscellaneous inventory transactions

Routine over receipts with correct invoice quantities

Slide 58

Process Problems for Payables

Why invoices do not equal receipts

Incorrect invoice quantities (UOM is wrong, etc.)

Payables not processing debit memos correctly

Expenses, invoice price or exchange rate variances miscoded

Payables matching the invoice to the wrong PO, PO line or PO Release

Not matching invoices to POs at all

Slide 59

Process Problems for Purchasing

Why invoices may not be processed correctly

Purchase orders prematurely closed or “final closed” Purged purchase orders

Incorrect purchase order unit prices

Late or no purchase orders created for the receipt

Slide 60

Expense A/P Accruals

Expense A/P Accruals – for both R11i and R12 Uninvoiced Receipts Report

• Supporting backup for your month-end expense accruals

• Run this report BEFORE running the Receipts Accrual – Period End program

Receipts Accrual – Period End program• This program creates your month-end expense

accruals• Payables has to be closed BEFORE you run this

month-end Purchasing program• R11i only creates entries into the G/L Interface• R12 creates Receiving accounting distributions

Slide 61

How to Run the Expense Accrual Programs and Reports

Run the Uninvoiced Receipts Report by specifying Accrued Receipts:

• No (before running the month-end process),• Yes (after running the month-end process),

Include On-line accruals: No The desired Period Name And the desired sort selection

(purchasing Category or Vendor)

Slide 62

How to Run the Expense Accrual Programs and Reports Run the Receipt Accruals – Period-End program after A/P

is closed• Enter the Minimum Accrual Amount• Leave Supplier and Item Category information blank• Select the accounting period

– Purchasing Period must be OPEN, G/L Period must be OPEN and A/P Period must be CLOSED.

• When Purchasing is closed the expense receipts are un-marked

Slide 63

How to Balance the Receipt Accrual - Period End Process

Use careful cut-off controls for receipt and invoice transactions

Close purchase orders and lines after you have reconciled the Expense A/P Accrual Account

Uninvoiced Receipts Report should agree to your Receipt Accruals - Period-End journals R11i: Goes directly into your General Ledger Interface table R12: Goes directly into your Receiving Accounting

Distributions

Make sure you match your expense A/P invoices to your expense PO receipts

Slide 64

How to Balance the Receipt Accrual - Period End Process

Caution, for earlier R12.0x and 12.1 Releases:

For both online and period-end accruals, both accounting entries use the journal category of Receiving

See Metalink Note:• How To Change The Seeded Default Journal Category from

"Receiving" to Accrual"? [ID 555547.1]

Slide 65

Implementation Issues (Mistakes You Don’t Want to Make)

Sharing system accounts

Badly defined receiving and payables processes

Little or no system testing for your accrual processes

After “go live”, late review for the first Accrual Reconciliation ReportIf your company has badly defined and/or badly executed PO

Receipt and A/P Invoice Match processes you will have a mess!

Slide 66

Release 12 A/P Accrual Issues

XML or BI Publisher Reports can become quite huge(especially if your Procurement and Payment processes are broken)

Inventory A/P accrual entries are reported on the Miscellaneous Accrual Reconciliation Report, even for Inter-Company Internal Orders or Logical Shipments/Logical Receipts (depending on FOB)

Accrual Reconciliation Load Run will fail if run more than one Accrual Reconciliation Load at a time

Slide 67

Release 12 Summary - Major Rewrite for A/P Accruals Each month’s accrual entries are loaded incrementally

No more cumulative loading each month for the Accrual Temp table (no longer temporary)

PO and A/P entries which balance to zero are matched and removed

Much better performance and less tablespace too

Separately define what are valid A/P Accrual Accounts 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)

Slide 68

Release 12 Summary - Major Rewrite for A/P Accruals

Summary and Detail Accrual Reconciliation Reports Summary reporting by account Detail reporting with separate headings from

report detail Uses XML/BI Publisher – download reports into

Excel Separate reports for matching vs. non-matching

miscellaneous entries (A/P, Inventory entries)

Ability to do mass A/P Accrual Write-Offs Write-offs use SLA (sub-ledger architecture) and

now create journal entries Can do write-offs at the PO distribution level With better write-off reporting

Slide 69

Conclusions

These accrual processes are very important in ensuring your margins are accurate

Proper implementation and data conversion is essential

Requires accurate receiving and A/P matching processes

Release 12 has major enhancements to consider

Slide 70

Where to get more information Douglas Volz Consulting

Collaborate08: How to Setup and Use Your A/P Accruals (R11i) http://www.volzconsulting.com/resources.html

Oracle Corporation Metalink:

• FAQ For On Line Accruals & Accrual Reconciliation Reports [ID: 433683.1]

• Impact of R12 Design in PO Accounting, [ID: 429105.1] • R12 How To Diagnose And Reconcile Inventory AP Accrual

Transactions Using Reconciliation Reports [ID 728871.1] • Master Note For Oracle EBS Procurement Suite - Encumbrance

And Accrual Accounting [ID 1138043.1] Oracle Purchasing User Guide Oracle Cost EBS Public Forum:

http://forums.oracle.com/forums/forum.jspa?forumID=414

Slide 71

http://www.volzconsulting.com/resources.html

Acknowledgements

You, the OAUG Cost Mgmt SIG, thanks for attending this session

Oracle Cost Development

Partners in crime at my clients over the years

Douglas Volz ([email protected])

Professional BackgroundDoug Volz is a Senior Architect and Advisor for Oracle Application projects, with a particular interest in Cost and Project Management. He has 30 years accumulated experience, including 5 years in Oracle Development (co-designing Oracle Cost Management) and 12 years in industry in Cost and Accounting Management positions. His 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, Italy, Switzerland and Germany.

Doug leads the OAUG Cost Management Special Interest Group. He also advises and participates on the Oracle Customer Advisory Board for Fusion Costing.

Core Expertise Multi-organization, Multi-currency ERP

Implementations Project Management and Senior Project Advisor Core manufacturing processes

Cost Management Inventory Bills of Material WIP

Systems Integration and Data Conversions

Experience Sample of clients served:

Beckman Coulter (US) Matsushita (UK, Mexico) Logitech (US, Taiwan, P.R.C.) Celgene Corporation (US, Switzerland) NTL (UK, now Virgin Media) TCI International (US) Onninen AS (Norway)

Thank You for Your Attendance and Participation

oracle r12 a/p accruals oaug cost mgmt sig meeting march 15, 2011

Documents