Major Tables and Relationships for Costing April 2010.ppt

The File is about a presentation to demonstrate how to define costing in oracle EBS R12

1. Oracle Applications User Group
Discrete MFG SIG – Cost Group
April 27
th
2010
Release 11i & 12:
Major Tables & Relationships for Costing, Session I

2. Helping people using Oracle Applications since
1990
Slide 2
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Background
Explore the Discrete Cost Applications
Look behind the forms and reports and see the
underlying tables and data structures
The goal is to provide a better understanding for how the
Applications work
Session I focuses on item cost basics; Session II focuses
on Cost Rollup, Update and Mass Edits

3. Helping people using Oracle Applications since
1990
Slide 3
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Agenda – Session I
Introduction for the next two sessions
Session I – Basic Item Costing and Organization Definition
Session II – Cost Rollup and Cost Transactions
Costing Overview
Cost Structure Overview
Organization Overview
Cost Setup
Define Cost Types
Define Material Sub-elements
Define Material Overhead Sub-elements
Define Overheads
Define Resources
Define Material Overhead Defaults

4. Helping people using Oracle Applications since
1990
Slide 4
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Agenda – Session I (continued)
Item Costing
 Buy Items
 Item Cost Summary Table
 Item Cost Details Table
 Item Cost Views & Inquiries
Wrap Up – How Did We Do?

5. Helping people using Oracle Applications since
1990
Slide 5
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Agenda – Session II
Cost Setup
Define Resources
Define Overheads
Define Departments
Supply Chain / Sourcing Rules Setup
Rollup And Item Cost Review
 Rollup and Rollup Cost Review Overview
 Define BOMs and Routings
 Make Item Costing
Copying, Editing, and Purging Costs
 Copy Costs Between Cost Types and Between Inventory Organizations
 Mass Editing Cost Information
 Mass Editing Item Accounts
 Purging Cost Information
Update Standard Costs
 Update Standard Costs
 Update Average Costs
 Cost History

6. Helping people using Oracle Applications since
1990
Slide 6
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Introduction to Session I – Costing Overview
GENERAL
LEDGER
ACCOUNTS
PAYABLE
PURCHASING
FIXED
ASSETS
ACCOUNTS
RECEIVABLE
INVENTORY
ORDER
ENTRY
WORK IN
PROCESS
Shipment
Transactions
Invoice
Info
BILL OF
MATERIAL
MRPSERVICE
Repair
RMA's
S/O Requirements
WIP Supply
to MRP
MRP Release
to WIP
ENGINEERING
COST
MANAGEMENT
BOM's/Routings
for Roll Up
Costs
Values
Trans
ENG Items
planned in
MRP
WIP Consumption &
WIP Completions
Asset
Additions
Invoices/
Payments
Asset Cost
Accum Depr
Depr Exp
PUR provides PO's
and Receipts
Delivery
Trans
Invoices /
Cash Receipt Trans
INV Receipt
Trans &
Exp Accr
Items
BOM's
Items from
BOM's
Matl
Requirements
Inventory/
WIP Trans
Values Trans
Values INV
Stds Adj
Misc Trans
for Warranty
Repair Parts
CASH
MANAGEMENT
(Bank Recon)
PROJECT
ACCOUNTING
Asset
Additions
Inventory
Transfers
Project
Charges

7. Helping people using Oracle Applications since
1990
Slide 7
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Organization Overview
FG
Subinventory
STORES
Subinventory
MRB
Subinventory
Global
Organization
(Item Master)
Inventory
Organization
Inventory
Location
General Ledger
Operating Unit
The Global Org can be
across OUs
Legal Entity

8. Helping people using Oracle Applications since
1990
Slide 8
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Item Costing Setup Overview
Cost
Types
Sub-
Elements
Sub-Element
Defaults
Item Costing
Cost Mass
Edits
Cost
Rollup
Org
Setup
Session II

9. Helping people using Oracle Applications since
1990
Slide 9
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Organization Setup in Cost, HRMS or Inventory
Cost Mgmt => Setup => Account Assignments => Organization

10. Helping people using Oracle Applications since
1990
Slide 10
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Organization Setup for Accounting Information
Click on the “Others” button and you get these choices:

11. Helping people using Oracle Applications since
1990
Slide 11
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Organization Setup for Accounting Information
Associate your organization with Ledger, LE and OU

12. Helping people using Oracle Applications since
1990
Slide 12
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Organization Tables – What Did You Just Use?
HR_ORGANIZATION_INFORMATION
Holds basic information for organizations
Simultaneously holds the LE, OU, Ledger and Inventory ids
Specify the org_information_context = 'Accounting Information‘
SELECT hoi.organization_id inv_organization_id,
hoi.org_information_id unique_key,
hoi.org_information_context type_of_data,
hoi.org_information1 ledger_id, -- set_of_books_id in R11i
hoi.org_information2 LE_ID,
hoi.org_information3 OU_ID
FROM hr.HR_ORGANIZATION_INFORMATION hoi
WHERE hoi.org_information_context = 'Accounting Information';

13. Helping people using Oracle Applications since
1990
Slide 13
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Define your Inventory Organization

14. Helping people using Oracle Applications since
1990
Slide 14
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Define Your Inventory Organization
MTL_PARAMETERS
Defines your basic inventory organization controls
•Costing Method
•Valuation accounts (Average Costing), default valuation accounts (Std)
•Purchase price, invoice price variance, inventory A/P accrual accounts
•Negative quantities allowed (yes or no)
•Enabled products: EAM, Project MFG, WMS, Process, etc.
•And many, many more attributes
SELECTorganization_code,
organization_id,
primary_costing_method -- 1 = STD, 2 = AVG
costing
FROM inv.MTL_PARAMETERS;

15. Helping people using Oracle Applications since
1990
Slide 15
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Define your Costing Method

16. Helping people using Oracle Applications since
1990
Slide 16
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Shipping Network Defaults

17. Helping people using Oracle Applications since
1990
Slide 17
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Cost Account Defaults

18. Helping people using Oracle Applications since
1990
Slide 18
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
More Organization Tables
HR_ALL_ORGANIZATION_UNITS
Holds the names for your different types of organizations
Here is a combined query to get your inventory org information:
SELECT gl.name “Ledger”,
haou2.name "Operating Unit",
haou.name “Inventory Org”
mp.organization_code "Org Code“
FROM inv.mtl_parameters mp,
hr.hr_organization_information hoi,
hr.hr_all_organization_units haou,
hr.hr_all_organization_units haou2,
gl.gl_ledgers gl
WHERE hoi.org_information_context = 'Accounting Information'
AND hoi.organization_id = mp.organization_id
AND hoi.organization_id = haou.organization_id -- inventory organization name
AND haou2.organization_id = to_number(hoi.org_information3) -- operating unit id
AND gl.ledger_id = to_number(hoi.org_information1) -- ledger_id (R11i set of books)

19. Helping people using Oracle Applications since
1990
Slide 19
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Organization Views to Help You
Here are useful organization views
ORG_ORGANIZATION_DEFINITIONS
CST_ORGANIZATION_DEFINITIONS
HR_OPERATING_UNITS_ALL
For performance on custom reports I advocate storing the following
information in MTL_PARAMETERS using ATTRIBUTE columns:
OPERATING_UNIT_ID
LEGAL_ENTITY_ID
BUSINESS_GROUP_ID
LEDGER_ID
CURRENCY_CODE

20. Helping people using Oracle Applications since
1990
Slide 20
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Cost Groups

21. Helping people using Oracle Applications since
1990
Slide 21
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Cost Groups
Defaulted for Discrete Costing, one Cost Group per Organization
Can have multiple CGs for Project MFG & Warehouse Mgmt (WMS)
Project MFG allows multiple item costs by project or project group
For WMS, per the reference manual:
First, for all costing methods, accounts are determined by the cost group, not
by the subinventory or the organization parameters. The cost groups allow
items in the same subinventory to be held in different accounts.
For actual costing, such as Average, FIFO, and LIFO, organizations, item costs
are held by the cost groups. When cost groups are assigned by item status, the
cost groups hold different item costs for items of different status. In FIFO and
LIFO organizations, the layer cost is maintained with the cost group.
In a Standard cost organization, a single Standard Cost is maintained for each
item. All inventory of that item, regardless of Cost Group, carries the same
Standard Cost.

22. Helping people using Oracle Applications since
1990
Slide 22
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Which Tables for Cost Groups?
CST_COST_GROUPS
COST_GROUP_ID
COST_GROUP
ORGANIZATION_ID
CST_COST_GROUP_ACCOUNTS
COST_GROUP_ID
MATERIAL_ACCOUNT
MATERIAL_OVERHEAD_ACCOUNT
RESOURCE_ACCOUNT
OVERHEAD_ACCOUNT
OUTSIDE_PROCESSING_ACCOUNT
CST_COST_GROUP_ASSIGNMENTS
COST_GROUP_ID
ORGANIZATION_ID

23. Helping people using Oracle Applications since
1990
Slide 23
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Change Gears to Item Costing Setup
Cost Type Definition

24. Helping people using Oracle Applications since
1990
Slide 24
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
What Did You Just Use for Cost Types?
CST_COST_TYPES
COST_TYPE_ID
COST_TYPE
ORGANIZATION_ID (only used if multi-org is unchecked)
COSTING_METHOD_TYPE
Reserved Cost Types
COST TYPE COST_TYPE_ID COSTING-
METHOD_
TYPE
FROZEN (Standard) 1 1
AVERAGE 2 2
PENDING 3 1
FIFO 5 5
LIFO 6 6
CTO 7 1
SELECTcost_type,
cost_type_id,
organization_id
FROM bom.CST_COST_TYPES;

25. Helping people using Oracle Applications since
1990
Slide 25
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Change Gears to Item Cost Setup
Seeded Cost Elements – No Form to Access This
COST_ELEMENT_ID
COST_ELEMENT
COST_ELEMENT COST_ELEMENT_ID
Material 1
Material Overhead 2
Resource 3
Outside Processing 4
Overhead 5

26. Helping people using Oracle Applications since
1990
Slide 26
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
On to Subelements
Material Subelements

27. Helping people using Oracle Applications since
1990
Slide 27
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Subelements (Continued)
Overheads – Material Overheads

28. Helping people using Oracle Applications since
1990
Slide 28
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Subelements (Continued)
Overheads – Production Overheads

29. Helping people using Oracle Applications since
1990
Slide 29
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Subelements (Continued)
Resources

30. Helping people using Oracle Applications since
1990
Slide 30
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Subelements (Continued)
OSP
Resources

31. Helping people using Oracle Applications since
1990
Slide 31
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Where Are These Subelements?
BOM_RESOURCES
RESOURCE_ID
RESOURCE_CODE
ORGANIZATION_ID
COST_ELEMENT_ID
PURCHASE_ITEM_ID (for OSP Resources)
FUNCTIONAL_CURRENCY_FLAG
UNIT_OF_MEASURE
RESOURCE_TYPE
AUTOCHARGE_TYPE
STANDARD_RATE_FLAG (1 or 2)
DEFAULT_BASIS_TYPE
ABSORPTION_ACCOUNT
RATE_VARIANCE_ACCOUNT
ALLOW_COSTS_FLAG (1 or 2)

32. Helping people using Oracle Applications since
1990
Slide 32
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Resource Charging Concepts
Autocharge
Type
AUTOCHARGE
_ TYPE
WIP move 1
Manual 2
PO receipt 3
PO Move 4
Default Basis
Type
DEFAULT_
BASIS_TYPE
Typically
used for:
Item 1 All costs
Lot 2 All Costs
Resource Units 3 Prod.
Ovhds
Resource Value 4 Prod.
Ovhds
Total Value 5 Mat’l
Ovhds
Activity 6 Activity
Based
Costs

33. Helping people using Oracle Applications since
1990
Slide 33
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Item Cost Defaults
Material Overhead Defaults

34. Helping people using Oracle Applications since
1990
Slide 34
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Where Are the Material Overhead Defaults?
CST_ITEM_OVERHEAD_DEFAULTS
ORGANIZATION_ID
ITEM_TYPE
CATEGORY_SET_ID
CATEGORY_ID
MATERIAL_OVERHEAD_ID
BASIS_TYPE
USAGE_RATE_OR_AMOUNT

35. Helping people using Oracle Applications since
1990
Slide 35
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Item Costing
Start with the Find Window

36. Helping people using Oracle Applications since
1990
Slide 36
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Buy Item Summary Cost Information

37. Helping people using Oracle Applications since
1990
Slide 37
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Item Cost Settings by Cost Type

38. Helping people using Oracle Applications since
1990
Slide 38
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Where are These Cost Settings?
CST_ITEM_COSTS
ORGANIZATION_ID
COST_TYPE_ID
INVENTORY_ITEM_ID
INVENTORY_ASSET_FLAG
LOT_SIZE
SHRINKAGE_RATE
COST_UPDATE_ID
ROLLUP_ID
ASSIGNMENT_SET_ID
“Who Columns”
Only items with an INVENTORY_ASSET_FLAG of ‘1’ (Yes)
can have a cost record
The item costs in your Costing Method Cost Type (Standard,
Average, etc.) are controlled by the forms and programs

39. Helping people using Oracle Applications since
1990
Slide 39
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Where are These Summary Costs?
CST_ITEM_COSTS
ORGANIZATION_ID
COST_TYPE_ID
INVENTORY_ITEM_ID
INVENTORY_ASSET_FLAG
LOT_SIZE
SHRINKAGE_RATE
COST_UPDATE_ID
ROLLUP_ID
ASSIGNMENT_SET_ID
MATERIAL_COST
MATERIAL_OVERHEAD_COST
RESOURCE_COST
OUTSIDE_PROCESSING_COST
OVERHEAD_COST
ITEM_COST
UNBURDENED_COST
BURDEN_COST
PL_ITEM_COST
TL_ITEM_COST
PL_MATERIAL
PL_MATERIAL_OVERHEAD
PL_RESOURCE_COST
PL_OUTSIDE_PROCESSING
PL_OVERHEAD_COST
TL_MATERIAL
TL_MATERIAL_OVERHEAD
TL_RESOURCE_COST
TL_OUTSIDE_PROCESSING
TL_OVERHEAD_COST

40. Helping people using Oracle Applications since
1990
Slide 40
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Summary Cost Relationships
CST_ITEM_COSTS– General formulas
TL Cost + PL Cost = ITEM_COST
UNBURDENED_COST + BURDENED_COST = ITEM_COST
∑ Cost Elements = ITEM_COST
COLUMN Value
MATERIAL_COST 0.13
MATERIAL_OVERHEAD_COST 0
RESOURCE_COST 0
OUTSIDE_PROCESSING_COST 0
OVERHEAD_COST 0
ITEM_COST 0.13

MATERIAL_COST +
MATERIAL_OVERHEAD_COST +
RESOURCE_COST +
OUTSIDE_PROCESSING_COST +
OVERHEAD_COST
ITEM_COST

41. Helping people using Oracle Applications since
1990
Slide 41
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Detailed Cost Information – Buy Items

42. Helping people using Oracle Applications since
1990
Slide 42
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Where are These Detailed Costs – Buy Items?
CST_ITEM_COST_DETAILS
ORGANIZATION_ID
COST_TYPE_ID
INVENTORY_ITEM_ID
RESOURCE_ID
COST_ELEMENT_ID
ROLLUP_SOURCE_TYPE
LEVEL_TYPE
SOURCE_ORGANIZATION_ID
ALLOCATION_PERCENT
ITEM_COST
RESOURCE_RATE X
USAGE_RATE_OR_AMOUNT X
BASIS_FACTOR X
NET_YIELD_OR_SHRINKAGE_RATE
 ITEM_COST
Newly defined items might not have any cost details
RESOURCE_RATE: 1 X
USAGE_RATE_OR_AMOUNT: 0.13 X
BASIS_FACTOR: 1 X
NET_YIELD_OR_SHRINKAGE_RATE: 1 X
ITEM_COST: 0.13

43. Helping people using Oracle Applications since
1990
Slide 43
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Cost Relationships: Summary and Detail
General Concepts
∑ CST_ITEM_COST_DETAILS = ∑ CST_ITEM_COSTS
True by COST_ELEMENT_ID, LEVEL_TYPE or in total
When you run a Cost Mass Edit it automatically corrects the
Summary Cost table to equal ∑ CST_ITEM_COST_DETAILS
If not equal the Standard Cost Update will fail
If not equal the ∑ (Qty X Unit Costs) ≠ ∑ accounting entries

44. Helping people using Oracle Applications since
1990
Slide 44
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
What About Cost Views?

45. Helping people using Oracle Applications since
1990
Slide 45
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Pre-Built or Seeded Cost View Choices

46. Helping people using Oracle Applications since
1990
Slide 46
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Element by sub-element Example

47. Helping people using Oracle Applications since
1990
Slide 47
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Cost Details for Buy Items
CST_ITEM_COST_DETAILS

48. Helping people using Oracle Applications since
1990
Slide 48
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
Extensible Cost Views
You can build your own item cost views
These views are registered in CST_INQUIRY_TYPES
This table drives the Cost View Inquiry screens
•UNIQUE_ID
•INQUIRY_NAME
•DESCRIPTION
•USER_DEFINED
•GUI_TEMPLATE_TYPE
•VIEW_NAME
•COLUMN1_HEADING
•COLUMN2_HEADING
•INVENTORY_FLAG
•DISABLE_DATE
•…various prompt columns

49. Helping people using Oracle Applications since
1990
Slide 49
OAUG Discrete MFG SIG – Cost Group
April 27, 2010
End of Session I
How Did We Do?
Looking for feedback
Is this useful?
Want less detail? More Detail?
More technical information? Or less technical information?

50. Helping people using Oracle Applications since
1990
Slide 50
OAUG Discrete MFG SIG – Cost Group
April 27, 2010