Oracle Applications User Group Discrete MFG SIG

1
Oracle Applications User GroupDiscrete MFG SIG
Cost Group April 27th 2010

• Release 11i 12 Major Tables Relationships
  for Costing, Session I

2
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
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
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
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
Introduction to Session I Costing Overview
7
Organization Overview
8
Item Costing Setup Overview
Sub-Element Defaults
OrgSetup
Sub-Elements
Session II
Cost Mass Edits
CostTypes
Item Costing
CostRollup
9
Organization Setup in Cost, HRMS or Inventory

• Cost Mgmt gt Setup gt Account Assignments gt
  Organization

10
Organization Setup for Accounting Information

• Click on the Others button and you get these
  choices

11
Organization Setup for Accounting Information

• Associate your organization with Ledger, LE and
  OU

12
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, 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'
13
Define your Inventory Organization
14
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

SELECT organization_code, organization_id, prima
ry_costing_method -- 1 STD, 2 AVG
costing FROM inv.MTL_PARAMETERS
15
Define your Costing Method
16
Shipping Network Defaults
17
Cost Account Defaults
18
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_i
  nformation3) -- operating unit id
• AND gl.ledger_id to_number(hoi.org_informatio
  n1) -- ledger_id (R11i set of books)

19
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
Cost Groups
21
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
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
Change Gears to Item Costing Setup

• Cost Type Definition

24
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
SELECT cost_type, cost_type_id, organization_id
FROM bom.CST_COST_TYPES
25
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
On to Subelements

• Material Subelements

27
Subelements (Continued)

• Overheads Material Overheads

28
Subelements (Continued)
Overheads Production Overheads
29
Subelements (Continued)

• Resources

30
Subelements (Continued)

• OSPResources

31
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
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 Matl Ovhds
Activity 6 Activity Based Costs
33
Item Cost Defaults

• Material Overhead Defaults

34
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
Item Costing

• Start with the Find Window

36
Buy Item Summary Cost Information
37
Item Cost Settings by Cost Type
38
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
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
Summary 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
41
Detailed Cost Information Buy Items

42
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

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

43
Cost Relationships Summary and Detail

• General Concepts

44
What About Cost Views?

45
Pre-Built or Seeded Cost View Choices

46
Element by sub-element Example

47
Cost Details for Buy Items

• CST_ITEM_COST_DETAILS

48
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
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
(No Transcript)