Manufacturing Accounting by Product Line For Release 11

1
Manufacturing Accounting by Product Line For
Release 11

• Peggy McNamara
• Douglas A. Volz

Logitech Inc. KPMG LLP April 1999
2
Update as of May 2007

• Doug wrote this presentation while at KPMG, based
  on his extensive development and architecture
  experience.
• Note that many changes have enhanced the Release
  11i product line capabilities, however this
  document is still useful as background to
  understand how the manufacturing transactions
  work. In addition, the sql examples (and in the
  word document, the sql for interfacing to the
  General Ledger) will still work with only minor
  changes.

3
Agenda

• Why is product line accounting important?
• Underlying business requirements
• Oracle Release 10 and 11 product line
  capabilities
• Affected subledgers
• Potential solutions and custom approaches

4
Agenda

• How did Logitech and KPMG solve this issue?
• Fundamentals of manufacturing transactions
• Receiving transactions
• Inventory transactions
• WIP transactions
• Accounting line types

5
Agenda

• Logitech example - which entries to reclassify?
• How frequently to reclassify?
• Program logic steps
• Other design decisions
• Design analysis example

6
Agenda

• Gotchas to avoid
• Enhancements for Oracle to consider
• Conclusions

7
Why is Product Line Accounting so Important?

• Better management visibility over profits and
  inventory levels
• Improve your ability to manage your bottom line
• Find out which products are winners and losers
• Helps to identify necessary corrective actions

8
Underlying Business Requirements

• Understand financial results by product line,
  including sales, cost of goods sold, gross
  margins, other cost of sales, and inventory
  values
• Another driving business requirement is a quick 3
  day close cycle for all operating subsidiaries

9
Desired Product Line Transactions

• Receiving valuation entries
• Subinventory/Stores valuation entries for all
  material movement
• Material overhead absorption
• Work in process valuation entries
• WIP resource, outside processing, and overhead
  transactions
• Cost of goods sold

10
Desired Product Line Transactions (Contd)

• Purchase price variances
• Cycle count variances
• Physical inventory variances
• Standard cost revaluation variances
• Manufacturing variances for closed WIP jobs
• WIP period close variances, for expense work
  orders

11
Release 10 and 11 Oracle Capabilities

• You can build up product costs by category
• Your costing category set is usually your product
  line
• Sales and COGS may be recorded by product line
• But limited capabilities for the other
  transactions

12
Reporting by Product Line for Release 10 and 11

• Receiving and Inventory Value Reports sort by
  category set
• Reports that do not sort by product line
• WIP Value Report
• Material Account Distribution Reports
• WIP Account Distribution Reports
• Receiving Account Distribution Report
• Purchase Price Variance Report
• Invoice Price Variance Report

13
New Release 11 Product Line Features

• Category Accounts
• Only available for the Material and Resource Cost
  Processor
• Not available for the Standard Cost Update
• Need custom hook to make the Material Cost
  Processor use the new category accounts
• But even then, the Material Cost Processor
  records by category account, not by subinventory
  and category accounts

14
New Release 11 Product Line Features (Contd)

• Category Accounts
• Does not need a custom hook for the WIP
  Resource Cost Processor
• But even then, the Resource Cost Processor
  records by category account, not by WIP class and
  category accounts

15
Verdict on Oracle Standard Functionality

• Not sufficient for Product Line Reporting and
  Accounting
• Not practical to setup subinventories and WIP
  classes by product line
• However, Oracle Cost Development team is aware of
  these limitations
• And the limited new functionality in Release 11
  is a positive sign that the functionality will
  increase in the future

16
Affected Subledgers

• Oracle Purchasing
• Oracle Inventory
• Oracle Work in Process

17
Potential Solutions and Custom Approaches

• Create the required reporting for manually
  reclassifying valuation and variance entries by
  product line
• Create the required programs to alter the
  accounting entries directly, in the respective
  purchasing, inventory and work in process
  subledgers
• Reclassify the accounting entries automatically
  in the General Ledger

18
How Did Logitech and KPMG Solve This Issue?

• Reclassify the accounting entries automatically
  in the General Ledger, why?
• It is the least invasive approach
• More likely to survive an upgrade without
  modification
• Still offers the speed of a automated approach
• And alternate three is the easiest to code, test
  and support in Production

19
Fundamentals of Manufacturing Transactions

• Standard Costing
• Receiving Transaction Programs
• Material Cost Processor
• Resource Cost Processor
• WIP Variance Cost Program
• Standard Cost Update Program

20
Fundamentals of Manufacturing Transactions

• Average Costing
• Material Cost Processor for Release 10 Average
  Costing
• New Release 11 Average Cost Processors for
  material and WIP transactions

21
Underlying Transaction Tables
22
Receiving Transaction Types
23
Material Transactions

• Transaction Sources
• Identify the source of the information for the
  transaction
• Transaction Actions
• Type of material movement, issue, receipt,
  update, etc.
• Transaction Types
• Defined by the combination of transaction sources
  and actions

24
User Defined Material Transactions

• Using a combination of transaction sources and
  actions, you can define new
• Miscellaneous issue/receipt transactions
• Interorganization transfers
• Subinventory transfers
• Release 10 average cost update transactions

25
Material Transaction Sources
26
Material Transaction Actions
27
Material Transaction Types
28
Material Transaction Types (Contd)
29
WIP Transactions
30
Accounting Line Types
31
Caution Bug on Accounting Line Types

• The WIP outside processing PO delivery
  transaction incorrectly uses accounting line type
  4, resource absorption, instead of 5, receiving
  inspection. Logitech and KPMG have logged a
  Release 11 Technical Assistance Request (TAR)
  with Oracle Support, but it is unresolved as of
  March 1, 1999.

32
Logitech Example - Which Entries to Reclassify?

• Storeroom and WIP inventory valuation
• Purchase price variance
• Cycle count and physical inventory variances
• Mfg variances (material usage, resource,
  overhead, outside processing efficiency)

33
Logitech Example - Which Entries to Reclassify?
(Contd)

• Standard cost update variances for storerooms and
  WIP
• Specified account alias
• Material overhead absorption
• Receiving inspection
• Intransit valuation

34
Program Logic Steps

• Verify that the Inventory Org accounting period
  is closed
• Reverse the purchasing receipt entries
• Reclassify the purchasing receipts
• Reverse the material accounting entries
• Reclassify the material accounting
• Reverse the WIP accounting entries
• Reclassify the WIP accounting entries

35
Other Design Decisions

• Get the product line from the cost of goods sold
  account in the item master
• Write the entries in summary
• Reclassify once at month-end, by inventory
  organization
• Normal setup setups, program registration, menu
  path, parameters, etc.

36
Design Analysis Example PO Receiving Entries

• RCV_RECEIVING_SUBLEDGER
• Ignore the A/P accrual account for the receiving
  entries
• Join to RCV_PARAMETERS.receiving_account_id to
  limit the entries for the RCV_RECEIVING_SUBLEDGER
  table

37
Design Analysis Example PO Delivery Entries

• Transaction_source_type_id 1
• Usually reclassify all of these entries
• You may decide to not reclassify material
  overhead absorption
• Accounting_line_type 3 and the
  transaction_source_type_id 1

38
Design Analysis Example Sales Orders/RMAs

• Transaction_source_type_id 2 12
• Usually reclassify only the inventory valuation
  entry, flexbuilder (Release 10) or account
  generator (Release 11) record by product line
• Usually handle the COGS and RMA entries in a
  separate insert statement

39
Design Analysis Example Account Issue/Receipt

• Transaction_source_type_id 3 and some 13 ids
• Exclude the accounting entry where the
  accounting_line_type 2, usually dont want to
  reclassify miscellaneous transactions

40
Design Analysis Example WIP

• Transaction_source_type_id 5
• Ignore the transaction_type_id 25, a Release 9
  cost update transaction for work in process
  valuation changes for components

41
Design Analysis Example Account Alias
Issue/Receipts

• Transaction_source_type_id 6
• Exclude the accounting entry where the
  accounting_line_type 2, usually dont want to
  reclassify miscellaneous transactions
• However you may wish to enable a descriptive
  flexfield, so you can designate specified account
  alias to reclassify

42
Design Analysis Example Inter-Org Transfers

• Transaction_source_type_id 7,8, and some 13 ids
• Exclude the accounting entry where the
  accounting_line_type in (9,10,11,12)
• Dont want to reclassify the various inter-org
  transfer accounting entries unless for inventory
  valuation

43
Design Analysis Example PIV, Cycle Count
Update Variances

• Physical inventory variances, cycle count, and
  standard cost update variances are usually all
  reclassified
• Transaction_source_type_id 9, 10, 11

44
Gotchas to Avoid

• Bug mentioned for WIP OSP accounting_line_type,
  for the receiving entry
• Defining new material transactions (user-defined)
• Need to be careful with cost update, WIP scrap
  and inter-org transfers, these transactions do
  not always reference a subinventory

45
More Gotchas to Avoid Joining to Subinventory
Information

• Select msub.attribute1
• From MTL_SECONDARY_INVENTORIES msub,
• MTL_MATERIAL_TRANSACTIONS mmt
• Where msub.secondary_inventory_name
• decode(mmt.transaction_action_id,
  3,decode(mmt.organization_id,mta.organization_id,
  mmt.subinventory_code,
  mmt.transfer_subinventory),
• 2,decode(sign(mta.primary_quantity),
• -1,mmt.subinventory_code,
• 1,mmt.transfer_subinventory,
• mmt.subinventory_code)
• , mmt.subinventory_code)
• and msub.organization_id mmt.organization_id

46
Enhancements for Oracle to consider

• Consistent use of the new category accounts by
  all cost programs
• Recording entries by subinventory and category
  accounts
• Recording entries by WIP class and category
  accounts
• Better product line reporting

47
Conclusions

• Release 11 functionality is not ready for
  product line accounting requirements
• You have numerous ways to customize
• In the future the new category accounts may offer
  better functionality
• Oracle development is aware of these limitations

48
Where to get more information

• The OAUG paper that this presentation is based
  upon
• Oracle Technical Reference Manuals
• Oracle Bills of Material (for cost processors)
• Oracle Inventory
• Oracle Purchasing (for receiving tables and
  processors)
• Oracle Work in Process

49
Thank You for Your Attendance and Participation

• Peggy McNamara Douglas A. Volz