1 / 50

Oracle Discrete Manufacturing Costing: Tables & Relationships Overview

Explore the underlying tables and data structures of Oracle Applications for costing. Understand item cost basics, cost rollup, updates, and mass edits in sessions focusing on Cost Applications.

rlance
Download Presentation

Oracle Discrete Manufacturing Costing: Tables & Relationships Overview

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 Helping people using Oracle Applications since 1990

  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 Helping people using Oracle Applications since 1990

  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? Helping people using Oracle Applications since 1990

  5. Agenda – Session II • Update Standard Costs • Update Standard Costs • Update Average Costs • Cost History • 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 Helping people using Oracle Applications since 1990

  6. Introduction to Session I – Costing Overview Helping people using Oracle Applications since 1990

  7. Organization Overview Helping people using Oracle Applications since 1990

  8. Item Costing Setup Overview Sub-Element Defaults OrgSetup Sub-Elements Session II Cost Mass Edits CostTypes Item Costing CostRollup Helping people using Oracle Applications since 1990

  9. Organization Setup in Cost, HRMS or Inventory • Cost Mgmt => Setup => Account Assignments => Organization Helping people using Oracle Applications since 1990

  10. Organization Setup for Accounting Information • Click on the “Others” button and you get these choices: Helping people using Oracle Applications since 1990

  11. Organization Setup for Accounting Information • Associate your organization with Ledger, LE and OU Helping people using Oracle Applications since 1990

  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, • 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'; Helping people using Oracle Applications since 1990

  13. Define your Inventory Organization Helping people using Oracle Applications since 1990

  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, • primary_costing_method -- 1 = STD, 2 = AVG costing • FROM inv.MTL_PARAMETERS; Helping people using Oracle Applications since 1990

  15. Define your Costing Method Helping people using Oracle Applications since 1990

  16. Shipping Network Defaults Helping people using Oracle Applications since 1990

  17. Cost Account Defaults Helping people using Oracle Applications since 1990

  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_information3) -- operating unit id AND gl.ledger_id = to_number(hoi.org_information1) -- ledger_id (R11i set of books) Helping people using Oracle Applications since 1990

  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 Helping people using Oracle Applications since 1990

  20. Cost Groups Helping people using Oracle Applications since 1990

  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. Helping people using Oracle Applications since 1990

  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 Helping people using Oracle Applications since 1990

  23. Change Gears to Item Costing Setup • Cost Type Definition Helping people using Oracle Applications since 1990

  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 • SELECT cost_type, • cost_type_id, • organization_id • FROM bom.CST_COST_TYPES; Helping people using Oracle Applications since 1990

  25. Change Gears to Item Cost Setup • Seeded Cost Elements – No Form to Access This • COST_ELEMENT_ID • COST_ELEMENT Helping people using Oracle Applications since 1990

  26. On to Subelements • Material Subelements Helping people using Oracle Applications since 1990

  27. Subelements (Continued) • Overheads – Material Overheads Helping people using Oracle Applications since 1990

  28. Subelements (Continued) Overheads – Production Overheads Helping people using Oracle Applications since 1990

  29. Subelements (Continued) • Resources Helping people using Oracle Applications since 1990

  30. Subelements (Continued) • OSPResources Helping people using Oracle Applications since 1990

  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) Helping people using Oracle Applications since 1990

  32. Resource Charging Concepts Helping people using Oracle Applications since 1990

  33. Item Cost Defaults • Material Overhead Defaults Helping people using Oracle Applications since 1990

  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 Helping people using Oracle Applications since 1990

  35. Item Costing • Start with the Find Window Helping people using Oracle Applications since 1990

  36. Buy Item Summary Cost Information Helping people using Oracle Applications since 1990

  37. Item Cost Settings by Cost Type Helping people using Oracle Applications since 1990

  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 Helping people using Oracle Applications since 1990

  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 Helping people using Oracle Applications since 1990

  40. 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 Summary Cost Relationships • CST_ITEM_COSTS– General formulas Helping people using Oracle Applications since 1990

  41. Detailed Cost Information – Buy Items Helping people using Oracle Applications since 1990

  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_RATE: 1 X ITEM_COST: 0.13 • Newly defined items might not have any cost details Helping people using Oracle Applications since 1990

  43. ∑ 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 Cost Relationships: Summary and Detail • General Concepts Helping people using Oracle Applications since 1990

  44. What About Cost Views? Helping people using Oracle Applications since 1990

  45. Pre-Built or Seeded Cost View Choices Helping people using Oracle Applications since 1990

  46. Element by sub-element Example Helping people using Oracle Applications since 1990

  47. Cost Details for Buy Items • CST_ITEM_COST_DETAILS Helping people using Oracle Applications since 1990

  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 Helping people using Oracle Applications since 1990

  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? Helping people using Oracle Applications since 1990

  50. Helping people using Oracle Applications since 1990

More Related