Title: Oracle Applications User Group Discrete MFG SIG
1Oracle Applications User GroupDiscrete MFG SIG
Cost Group April 27th 2010
- Release 11i 12 Major Tables Relationships
for Costing, Session I
2Background
- 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
3Agenda 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
4Agenda 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?
5Agenda 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
6Introduction to Session I Costing Overview
7Organization Overview
8Item Costing Setup Overview
Sub-Element Defaults
OrgSetup
Sub-Elements
Session II
Cost Mass Edits
CostTypes
Item Costing
CostRollup
9Organization Setup in Cost, HRMS or Inventory
- Cost Mgmt gt Setup gt Account Assignments gt
Organization
10Organization Setup for Accounting Information
- Click on the Others button and you get these
choices
11Organization Setup for Accounting Information
- Associate your organization with Ledger, LE and
OU
12Organization 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, ho
i.org_information_id unique_key, hoi.org_informati
on_context type_of_data, hoi.org_information1
ledger_id, -- set_of_books_id in
R11i hoi.org_information2 LE_ID, hoi.org_informati
on3 OU_ID FROM hr.HR_ORGANIZATION_INFORMATION
hoi WHERE hoi.org_information_context
'Accounting Information'
13Define your Inventory Organization
14Define 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
SELECT organization_code, organization_id, prima
ry_costing_method -- 1 STD, 2 AVG
costing FROM inv.MTL_PARAMETERS
15Define your Costing Method
16Shipping Network Defaults
17Cost Account Defaults
18More 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_i
nformation3) -- operating unit id - AND gl.ledger_id to_number(hoi.org_informatio
n1) -- ledger_id (R11i set of books)
19Organization 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
20Cost Groups
21Cost 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.
22Which 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
23Change Gears to Item Costing Setup
24What 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
SELECT cost_type, cost_type_id, organization_id
FROM bom.CST_COST_TYPES
25Change 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
26On to Subelements
27Subelements (Continued)
- Overheads Material Overheads
28Subelements (Continued)
Overheads Production Overheads
29Subelements (Continued)
30Subelements (Continued)
31Where 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)
32Resource 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 Matl Ovhds
Activity 6 Activity Based Costs
33Item Cost Defaults
- Material Overhead Defaults
34Where 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
35Item Costing
- Start with the Find Window
36Buy Item Summary Cost Information
37Item Cost Settings by Cost Type
38Where 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
39Where are These Summary 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
40Summary Cost Relationships
- CST_ITEM_COSTS General formulas
COLUMN Value
MATERIAL_COST 0.13
MATERIAL_OVERHEAD_COST 0
RESOURCE_COST 0
OUTSIDE_PROCESSING_COST 0
OVERHEAD_COST 0
ITEM_COST 0.13
41Detailed Cost Information Buy Items
42Where 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
RESOURCE_RATE
1 X USAGE_RATE_OR_AMOUNT
0.13 X BASIS_FACTOR
1 X NET_YIELD_OR_SHRINKAGE_RA
TE 1 X ITEM_COST
0.13
- Newly defined items might not have any cost
details
43Cost Relationships Summary and Detail
44What About Cost Views?
45Pre-Built or Seeded Cost View Choices
46Element by sub-element Example
47Cost Details for Buy Items
48Extensible 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
49End 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(No Transcript)