Item Cost query ( mtl_system_items_b,cst_item_costs,gl_code_combinations_kfv)

select msi.segment1 "ITEM_NUM",
msi.description "ITEM_DESCRIPTION",
msi.PRIMARY_UNIT_OF_MEASURE "UOM",
cct.cost_type "COST TYPE",
cic.DEFAULTED_FLAG "USE DEFAULT CONTROLS",
msi.INVENTORY_ASSET_FLAG "INVENTORY ASSET",
cic.BASED_ON_ROLLUP_FLAG "BASED ON ROLLUP",
cic.LOT_SIZE,
cic.shrinkage_rate "MANUFACTURING SHRINKAGE",
cic.item_cost "UNIT COST",
cic.MATERIAL_COST "MATERIAL",
cic.MATERIAL_OVERHEAD_COST "MATERIAL_OVERHEAD",
cic.RESOURCE_COST "RESOURCE",
cic.OUTSIDE_PROCESSING_COST "OUTSIDE_PROCESSING",
cic.OVERHEAD_cost "OVERHEAD",
gcc.CONCATENATED_SEGMENTS "COGS_ACCOUNT",
gcc1.CONCATENATED_SEGMENTS "SALES_ACCOUNT",
flv.MEANING "MAKE/BUY",
msi.DEFAULT_INCLUDE_IN_ROLLUP_flag "INCLUDE IN ROLLUP",
(SELECT  mic.segment1
                   || '.'
                   || mic.segment2
                   || '.'
                   || mic.segment3
                   || '.'
                   || mic.segment4
                   || '.'
                   || mic.segment5
                   || '.'
                   || mic.segment6
            FROM   MTL_ITEM_CATEGORIES_V mic, mtl_category_sets mcs
           WHERE       mic.CATEGORY_SET_ID = mcs.CATEGORY_SET_ID
                   AND mcs.CATEGORY_SET_NAME = 'ENVS CST Category Set'
                   AND mic.inventory_item_id = msi.inventory_item_id
                   AND mic.organization_id = msi.organization_id)
            "COST CATEGORY"
from mtl_system_items_b msi,
cst_cost_types cct,
cst_item_costs cic,
org_organization_definitions ood,
gl_code_combinations_kfv gcc,
gl_code_combinations_kfv gcc1,
FND_LOOKUP_VALUES flv
WHERE 1=1
AND 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 = ood.organization_id
AND gcc.CHART_OF_ACCOUNTS_ID = ood.CHART_OF_ACCOUNTS_ID
AND gcc.CODE_COMBINATION_ID =msi.COST_OF_SALES_ACCOUNT
AND gcc1.CHART_OF_ACCOUNTS_ID = ood.CHART_OF_ACCOUNTS_ID
AND gcc1.CODE_COMBINATION_ID =msi.expense_account
AND flv.lookup_type = 'MTL_PLANNING_MAKE_BUY'
and flv.LOOKUP_CODE=msi.PLANNING_MAKE_BUY_CODE
AND flv.LANGUAGE='US'
AND ood.organization_code = 'E31'
--and msi.segment1='02980050'
--and msi.inventory_item_id=1117374