SQL Script to find Item Cost

By | August 5, 2015

CST_ITEM_COSTS stores item cost control information by cost type.

For standard costing organizations, the item cost control information for the Frozen cost type is created when you enter a new item. For average cost organizations, item cost control information is created when you transact the item for the first time.

You can use the Item Costs window to enter cost control information.

Here is the query to find Item Cost:

/*********************************************************
*PURPOSE: Query to find Item Cost                        *
*AUTHOR: Shailender Thallam                              *
**********************************************************/
SELECT msi.segment1 "ITEM_NAME",
  msi.inventory_item_id,
  cic.item_cost ,
  mp.organization_code,
  mp.organization_id,
  cct.cost_type,
  cct.description,
  cic.tl_material,
  cic.tl_material_overhead,
  cic.material_cost,
  cic.material_overhead_cost,
  cic.tl_item_cost,
  cic.unburdened_cost,
  cic.burden_cost
FROM cst_cost_types cct,
  cst_item_costs cic,
  mtl_system_items_b msi,
  mtl_parameters mp
WHERE cct.cost_type_id    = cic.cost_type_id
AND cic.inventory_item_id = msi.inventory_item_id
AND cic.organization_id   = msi.organization_id
AND msi.organization_id   = mp.organization_id
AND msi.inventory_item_id = 45
AND mp.organization_id    = 204
AND cct.cost_type         = 'Frozen' --'Average' --'Pending'
  ;
WOW! Did you like this post? We'll send more interesting posts like SQL Script to find Item Cost to you!
Enter your Email Address:
  • Venkata Veeragoni

    In case of OPM below SQL can be used
    select msi.segment1 item,
    GMF_CMCOMMON.get_cmpt_cost( inventory_item_id, organization_id, sysdate, cmm.cost_type_id ,0) cost
    from mtl_system_items_b msi, CM_MTHD_MST cmm
    where cmm.cost_mthd_desc=’Standard Cost’