Item cost tables in oracle apps

Item cost tables in oracle apps

In this post, we will be discussing about the Item cost tables in oracle apps. Item cost tables help to store the item costing information in oracle apps. We will share some of the important tables related to item costing. We do get the Costing type information, Item cost, Material cost, Material overhead related information in these item cost tables. Here below are the most important item cost tables and the sql query using these item cost tables to extract the item costing information’s. 

Most Important Item cost tables in oracle apps

1.cst_cost_types

2.cst_item_costs

3.mtl_system_items_b

4.mtl_parameters

Detail SQL Query using Item cost tables in oracle apps

SELECT msi.segment1 "ITEM_NAME", msi.inventory_item_id,

item_cst.item_cost, mp.organization_code, mp.organization_id,

cost_type.cost_type, cost_type.description cost_type_description, item_cst.tl_material,

item_cst.tl_material_overhead, item_cst.material_cost,

item_cst.material_overhead_cost, item_cst.tl_item_cost,

 item_cst.unburdened_cost, item_cst.burden_cost,

 pl_resource recorce_cost, pl_overhead overhead_cost

  FROM apps.cst_cost_types cost_type, apps.cst_item_costs item_cst,

  apps.mtl_system_items_b msi, apps.mtl_parameters mp

 WHERE cost_type.cost_type_id = item_cst.cost_type_id

   AND item_cst.organization_id = msi.organization_id

   AND msi.organization_id = mp.organization_id

   AND item_cst.inventory_item_id = msi.inventory_item_id

   AND cost_type.cost_type = 'Average'

   AND msi.segment1 = '1233333' ---ITEM CODE----

   AND mp.organization_code = '121'  --ORG CODE--