Transcript How to Setup, Use and Balance Your A/P Accrual Accounts

Manufacturing Accounting by
Product Line For Release 11
Peggy McNamara &
Douglas A. Volz
Logitech Inc. &
KPMG LLP
April 1999
1
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.
2
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

3
Agenda
How did Logitech and KPMG solve this
issue?
 Fundamentals of manufacturing
transactions

•
•
•
•
Receiving transactions
Inventory transactions
WIP transactions
Accounting line types
4
Agenda
Logitech example - which entries to
reclassify?
 How frequently to reclassify?
 Program logic steps
 Other design decisions
 Design analysis example

5
Agenda
Gotchas to avoid
 Enhancements for Oracle to consider
 Conclusions

6
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

7
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
8
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
9
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
10
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

11
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
12
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
13
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
14
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

15
Affected Subledgers

Oracle Purchasing

Oracle Inventory

Oracle Work in Process
16
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

17
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
18
Fundamentals of Manufacturing
Transactions

Standard Costing
•
•



Receiving Transaction Programs
Material Cost Processor
Resource Cost Processor
WIP Variance Cost Program
Standard Cost Update Program
19
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
20
Underlying Transaction Tables
Manufacturing Transaction Tables
Type
Transaction Table Accounting Table
Receiving
RCV_
RCV_RECEIVING
TRANSACTIONS
SUB_LEDGER
Stores and WIP
MTL_MATERIAL_ MTL_
Material
TRANSACTIONS
TRANSACTION_
ACCOUNTS
WIP Resources, WIP_
WIP_
OSP, Overhead, TRANSACTIONS
TRANSACTION_
and WIP Cost
ACCOUNTS
Updates
21
Receiving Transaction Types
Transaction
Type
ACCEPT
CORRECT **
DELIVER
MATCH **
RECEIVE **
REJECT
RETURN TO
RECEIVING
RETURN TO **
VENDOR
SHIP
TRANSFER
UNORDERED
Receiving Transactions Description
Accept items following an inspection
Correct a previous transaction entry
Deliver a shipment of items to the requestor
Match unordered items to a purchase order
Receive a shipment of items
Reject items following an inspection
Return delivered items to receiving
Return Items to the supplier
Intransit shipment or internal order
Transfer items between receiving locations
Receive items without matching to a source
document
** Creates Accounting Entries for Purchasing
22
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
23
User Defined Material
Transactions

Using a combination of transaction
sources and actions, you can define
new:
•
•
•
•
Miscellaneous issue/receipt transactions
Inter–organization transfers
Subinventory transfers
Release 10 average cost update
transactions
24
Material Transaction Sources
ID
1
2
3
4
5
6
7
8
9
10
11
12
13
Name
Purchasing
Sales Orders
Account
Not used
Work in
Process
Account Alias
Internal
Requisition
Internal Order
Cycle Count
Physical
Inventory
Standard Cost
Update
RMA
Inventory
Description
Purchase Orders (PO) and PO Returns
Sales Order Shipments
Misc. account issue receipt transactions
Not used in any release, not a valid source
Any WIP material transaction
Misc. transactions using the account alias
Transactions sourced by internal requisitions
Transactions sourced by internal sales orders
Cycle count transactions
Physical inventory transactions
Standard cost update transactions
RMA shipment returns
Various inventory transfer transactions
25
Material Transaction Actions
ID Meaning
1 Issue from
stores
ID
24
Meaning
Cost update
Description
Standard and average cost
updates
2
27
Receipt in stores
28
Staging transfer
Various inventory receipt
transactions.
Staging transfers for sales
and internal sales orders
29
Delivery
adjustments
30
WIP scrap
adjustment
31
32
Assembly
completion
Assembly return
WIP assembly completion
into stores
WIP assembly return from
stores
24
Cost update
Standard and average cost
updates
3
4
Description
Purchase Orders
(PO) and PO
Returns
Subinventory Sales Order
Transfer
Shipments
Direct
Inter-org
organization ransfers no
transfer
intransit
Cycle count
Cycle count
adjustment
adjustment
8
Physical
inventory
adjustment
12 Intransit
receipt
21 Intransit
shipment
24 Cost update
Physical
inventory
adjustment
Inter-org receipt
from intransit
Inter-org
shipment to
intransit
Standard and
average cost
updates
Adjustments to PO and
intransit deliveries into
stores
WIP scrap transaction
26
Material Transaction Types
ID
1
2
3
4
8
12
15
17
18
21
24
25
31
32
33
34
35
36
37
38
40
TRANSACTION_TYPE_NAME
Account issue transaction
Subinventory transfer sourced from inventory
Direct transfer between two organizations
Cycle Count adjustment transactions
Physical Inventory adjustment transactions
Receive from intransit
Return material authorization
WIP assembly return transaction
Purchase order receipt
Ship to intransit sourced from Inventory
Standard cost update transactions
WIP cost update transactions
Account alias issue transaction
Miscellaneous issue transaction
Sales order issue transaction
Internal order issue transaction
WIP component issue transaction
Return to vendor from stores
Return return material authorization
WIP negative component issue
Account Receipt transaction
27
Material Transaction Types
(Contd)
ID
41
42
43
44
48
50
51
52
53
54
61
62
70
71
72
80
88
90
TRANSACTION_TYPE_NAME
Account alias receipt transaction
Miscellaneous receipt transaction
WIP component return transaction
WIP assembly completion transaction
WIP negative component return
Subinventory transfer sourced by Internal order
Backflush subinventory transfer
Staging transfer on a Sales order
Staging transfer on an Internal order
Direct transfer between two orgs on a internal order
Delivery of intransit material sourced by Internal requisition
Ship to intransit sourced by Internal order
Delivery adjustments on an Intransit receipt sourced by
Inventory
Delivery adjustments on a Purchase order receipt
Delivery adjustments on intransit receipt sourced by Internal
Requisition
Average cost update transactions
Average Cost Adjustment from Intercompany Invoices
WIP scrap transactions
28
WIP Transactions
ID Meaning
1 Resource
transaction
2 Overhead
transaction
3 Outside
processing
4 Cost update
5 Period
close
variance
6 Job close
variance
Description
Resource transactions and overhead earned
on resources
Overhead earned on shop floor move
transactions (move-based overhead)
Outside processing PO deliveries into WIP
WIP cost updates
Expense job period close variances, or
repetitive schedule variances if your
variances are recognized at month-end
WIP job variances, or cancelled repetitive
schedules
29
Accounting Line Types
ID
1
2
3
4
5
6
7
8
9
10
11
12
13
14
99
Cost Accounting Line Types
Inv valuation
Account
Overhead absorption
Resource absorption
Receiving inspection
Purchase price or rate variance
WIP valuation
WIP variance
Inter-org payables
Inter-org receivables
Inter-org transfer credit
Inter-org freight charge
Average cost variance
Intransit inventory
Unknown
30
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.
31
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)

32
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

33
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

34
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.

35
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
36
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
37
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
38
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
don’t want to reclassify miscellaneous
transactions
39
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
40
Design Analysis Example –
Account Alias Issue/Receipts

Transaction_source_type_id = 6
• Exclude the accounting entry where
the accounting_line_type = 2, usually
don’t want to reclassify miscellaneous
transactions
• However you may wish to enable a
descriptive flexfield, so you can
designate specified account alias to
reclassify
41
Design Analysis Example – InterOrg 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)
• Don’t want to reclassify the various
inter-org transfer accounting entries
unless for inventory valuation
42
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
43
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

44
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;
45
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

46
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

47
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

48
Thank You for Your Attendance
and Participation
Peggy McNamara & Douglas A.
Volz
49