Query to Get item Unit Price in Oracle Apps

Query to Get item Unit Price in Oracle Apps

Hi friends , we are going to discuss about the Query to Get item Unit Price in Oracle Apps. We will share the detail sql query which helps to fetch the inventory item unit price details in oracle apps. We will share the complete sql query which will help you to extract the complete item cost details with unit price in oracle apps. We will also try to share some of the important tables too which will stores the item price related information's in oracle apps. This item unit price query will help you to develop the custom oracle reports about item price. Please find below the complete detail about the Query to Get item Unit Price in Oracle Apps.

Query to Get item Unit Price in Oracle Apps

5 Important Tables about Query to Get item Unit Price in Oracle Apps

Here below is the list of important tables too about item unit price in oracle apps.

1.CST_ITEM_COSTS

2.CST_PERPAVG_COST

3.CST_COST_ORGS_V

4.CST_COST_TYPES

5.MTL_SYSTEM_ITEMS_B

5.MTL_PARAMETERS

Detail SQL Query to Get item Unit Price in Oracle Apps

Here below is the detail sql query which helps to extract the item unit price information and other item costing related information' in oracle apps.

SELECT msi.segment1 "Item Name",

  cic.tl_item_cost "Item Unit Price",

  cic.item_cost ,

  mp.organization_code "Inventory Org",

  mp.organization_id "Org ID",

  cct.cost_type "Costing Type",

  cct.description "Cost Description',

  cic.tl_material,

  cic.tl_material_overhead "Material Overhead",

  cic.material_cost "Material Cost",

  cic.material_overhead_cost "Matieral Ovrhed Cost",

  cic.unburdened_cost "Unburdened Cost",

  cic.burden_cost

FROM   mtl_system_items_b msi,

  mtl_parameters mp,

  cst_item_costs cic,

cst_cost_types cct

WHERE 1=1

and mp.organization_id    = 403

and cct.cost_type_id    = cic.cost_type_id

AND cct.cost_type         = 'Average'

AND msi.inventory_item_id = 456654

AND cic.inventory_item_id = msi.inventory_item_id

AND cic.organization_id   = msi.organization_id

AND msi.organization_id   = mp.organization_id

order by msi.segment1,mp.organization_code

Query to Get item Unit Price in Oracle Apps