Active Inventory Item Query

SELECT
(
SELECT
organization_name
FROM
org_organization_definitions
WHERE
organization_id = msi.organization_id
) org_name,
msi.segment1 item_number,
msi.description item_description,
msi.primary_unit_of_measure,
–msi.item_type,
(
SELECT
flv.meaning
FROM
apps.mtl_system_items_b msb,
apps.fnd_lookup_values flv
WHERE
lookup_type = ‘ITEM_TYPE’
AND msb.item_type = flv.lookup_code
AND msb.organization_id = msi.organization_id
AND msb.inventory_item_id = msi.inventory_item_id
) item_type,
msi.inventory_item_status_code item_status,
DECODE(msi.planning_make_buy_code,’2′,’BUY’,’1′,’MAKE’) make_buy,
gcck.concatenated_segments sales_account,
(
SELECT
gcck1.concatenated_segments
FROM
gl_code_combinations_kfv gcck1
WHERE
gcck1.code_combination_id = msi.cost_of_sales_account
) cost_of_sales_account
FROM
apps.mtl_system_items_fvl msi,
apps.gl_code_combinations_kfv gcck
WHERE
msi.organization_id =:p_organization_id
AND msi.sales_account = gcck.code_combination_id (+)
AND msi.inventory_item_status_code = ‘Active’;