Oracle Applications User Group Discrete MFG SIG Cost

Oracle Applications User Group Discrete MFG SIG – Cost Group April 27 th 2010 Release 11 i & 12: Major Tables & Relationships for Costing, Session I Helping people using Oracle Applications since 1990 Slide

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

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

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

OAUG Discrete MFG SIG – Cost Group April 27, 2010 Agenda – Session II p Cost Setup § Define Resources § Define Overheads § Define Departments § Supply Chain / Sourcing Rules Setup p Update Standard Costs § Update Average Costs § Cost History p Rollup And Item Cost Review § Rollup and Rollup Cost Review Overview § Define BOMs and Routings § Make Item Costing p 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 Helping people using Oracle Applications since 1990 5

OAUG Discrete MFG SIG – Cost Group April 27, 2010 Introduction to Session I – Costing Overview Helping people using Oracle Applications since 1990 6

OAUG Discrete MFG SIG – Cost Group April 27, 2010 Organization Overview Helping people using Oracle Applications since 1990 7

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

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

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

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

OAUG Discrete MFG SIG – Cost Group April 27, 2010 Organization Tables – What Did You Just Use? p 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_information 1 ledger_id, -- set_of_books_id in R 11 i hoi. org_information 2 LE_ID, hoi. org_information 3 OU_ID FROM hr. HR_ORGANIZATION_INFORMATION hoi WHERE hoi. org_information_context = 'Accounting Information'; Helping people using Oracle Applications since 1990 12

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

OAUG Discrete MFG SIG – Cost Group April 27, 2010 Define Your Inventory Organization p 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, primary_costing_method -- 1 = STD, 2 = AVG costing FROM inv. MTL_PARAMETERS; Helping people using Oracle Applications since 1990 14

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

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

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

OAUG Discrete MFG SIG – Cost Group April 27, 2010 More Organization Tables p 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”, haou 2. 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 2, 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 haou 2. organization_id = to_number(hoi. org_information 3) -- operating unit id AND gl. ledger_id = to_number(hoi. org_information 1) -- ledger_id (R 11 i set of books) Helping people using Oracle Applications since 1990 18

OAUG Discrete MFG SIG – Cost Group April 27, 2010 Organization Views to Help You p Here are useful organization views § ORG_ORGANIZATION_DEFINITIONS § CST_ORGANIZATION_DEFINITIONS § HR_OPERATING_UNITS_ALL p 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 Helping people using Oracle Applications since 1990 19

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

OAUG Discrete MFG SIG – Cost Group April 27, 2010 Cost Groups p p 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. Helping people using Oracle Applications since 1990 21

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

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

OAUG Discrete MFG SIG – Cost Group April 27, 2010 What Did You Just Use for Cost Types? p CST_COST_TYPES § § COST_TYPE_ID COST_TYPE ORGANIZATION_ID (only used if multi-org is unchecked) COSTING_METHOD_TYPE p Reserved Cost Types COST TYPE COST_TYPE_ID COSTINGMETHOD_ 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; Helping people using Oracle Applications since 1990 24

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

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

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

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

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

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

OAUG Discrete MFG SIG – Cost Group April 27, 2010 Where Are These Subelements? p 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) Helping people using Oracle Applications since 1990 31

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

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

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

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

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

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

OAUG Discrete MFG SIG – Cost Group April 27, 2010 Where are These Cost Settings? p 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 Helping people using Oracle Applications since 1990 38

OAUG Discrete MFG SIG – Cost Group April 27, 2010 Where are These Summary Costs? p 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_OVERHEAD PL_RESOURCE_COST PL_OUTSIDE_PROCESSING PL_OVERHEAD_COST TL_MATERIAL_OVERHEAD TL_RESOURCE_COST TL_OUTSIDE_PROCESSING TL_OVERHEAD_COST Helping people using Oracle Applications since 1990 39

OAUG Discrete MFG SIG – Cost Group April 27, 2010 Summary Cost Relationships p CST_ITEM_COSTS– General formulas § TL Cost + PL Cost = ITEM_COST § UNBURDENED_COST + BURDENED_COST = ITEM_COST § ∑ Cost Elements = ITEM_COST MATERIAL_COST + MATERIAL_OVERHEAD_COST + RESOURCE_COST + OUTSIDE_PROCESSING_COST + OVERHEAD_COST 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 Helping people using Oracle Applications since 1990 40

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

OAUG Discrete MFG SIG – Cost Group April 27, 2010 Where are These Detailed Costs – Buy Items? p 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_RATE: 1 X ITEM_COST: 0. 13 § Newly defined items might not have any cost details Helping people using Oracle Applications since 1990 42

OAUG Discrete MFG SIG – Cost Group April 27, 2010 Cost Relationships: Summary and Detail p 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 Helping people using Oracle Applications since 1990 43

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

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

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

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

OAUG Discrete MFG SIG – Cost Group April 27, 2010 Extensible Cost Views p 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 COLUMN 1_HEADING COLUMN 2_HEADING INVENTORY_FLAG DISABLE_DATE …various prompt columns Helping people using Oracle Applications since 1990 48

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

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