SQL & PL/SQL

Hi,

I have a query like below:
SELECT OOD.ORGANIZATION_NAME INV_ORG_NAME,
OOD.ORGANIZATION_CODE ORG_CODE,
MSIB.SEGMENT1 ITEM,
MSIB.DESCRIPTION ITEM_DESCRIPTION,
MSIB.PRIMARY_UOM_CODE PRIMARY_UOM_CODE,
MSIB.ITEM_TYPE ITEM_TYPE,
MSIB.INVENTORY_ITEM_STATUS_CODE ITEM_STATUS,
APPS.XXBG_UTILS.GET_LOOKUP_VALUE(MSIB.INVENTORY_PLANNING_CODE,
'MTL_MATERIAL_PLANNING') INVENTORY_PLANNING_METHOD,
MSIB.MIN_MINMAX_QUANTITY MIN_MINMAX_QUANTITY,
MSIB.MAX_MINMAX_QUANTITY MAX_MINMAX_QUANTITY,
(SELECT MIN_MINMAX_QUANTITY
FROM APPS.MTL_ITEM_SUB_INVENTORIES MISI
WHERE MSIB.INVENTORY_ITEM_ID = MISI.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID = MISI.ORGANIZATION_ID
AND MISI.SECONDARY_INVENTORY = MOQD.SUBINVENTORY_CODE) SUBINV_MIN,
(SELECT MAX_MINMAX_QUANTITY
FROM APPS.MTL_ITEM_SUB_INVENTORIES MISI
WHERE MSIB.INVENTORY_ITEM_ID = MISI.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID = MISI.ORGANIZATION_ID
AND MISI.SECONDARY_INVENTORY = MOQD.SUBINVENTORY_CODE) SUBINV_MAX,
MSIB.INVENTORY_ITEM_ID,
MSIB.ORGANIZATION_ID,
(SELECT CAT.CATEGORY_SET_NAME
FROM APPS.XXBG_INV_ITEM_CATEGORIES_V CAT
WHERE MSIB.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID(+)
AND MSIB.ORGANIZATION_ID = CAT.ORGANIZATION_ID(+)
AND CAT.CONTROL_LEVEL_DISP = 'Org'
AND CAT.CATEGORY_SET_NAME = 'Criticality') CATEGORY_SET_NAME,
(SELECT CAT.CATEGORY_CONCAT_SEGS
FROM APPS.XXBG_INV_ITEM_CATEGORIES_V CAT
WHERE MSIB.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID(+)
AND MSIB.ORGANIZATION_ID = CAT.ORGANIZATION_ID(+)
AND CAT.CONTROL_LEVEL_DISP = 'Org'
AND CAT.CATEGORY_SET_NAME = 'Criticality') CATEGORY,
(SELECT CAT1.CATEGORY_CONCAT_SEGS
FROM APPS.XXBG_INV_ITEM_CATEGORIES_V CAT1
WHERE MSIB.INVENTORY_ITEM_ID = CAT1.INVENTORY_ITEM_ID(+)
AND MSIB.ORGANIZATION_ID = CAT1.ORGANIZATION_ID(+)
AND CAT1.CONTROL_LEVEL_DISP = 'Org'
AND CAT1.CATEGORY_SET_NAME = 'Inventory') OWNER_DEPT,
(SELECT NVL(ITEM_COST, 0)
FROM APPS.CST_ITEM_COSTS CIC
WHERE CIC.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND CIC.ORGANIZATION_ID = MSIB.ORGANIZATION_ID) UNIT_ITEM_COST,
NVL(SUM(MOQD.PRIMARY_TRANSACTION_QUANTITY), 0) ON_HAND_QUANTITY,
MOQD.SUBINVENTORY_CODE SUBINVENTORY,
MIL.CONCATENATED_SEGMENTS LOCATOR
FROM APPS.ORG_ORGANIZATION_DEFINITIONS OOD,
APPS.MTL_SYSTEM_ITEMS_B MSIB,
APPS.MTL_ONHAND_QUANTITIES_DETAIL MOQD,
APPS.MTL_ITEM_LOCATIONS_KFV MIL
WHERE OOD.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MSIB.INVENTORY_ITEM_ID = MOQD.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID = MOQD.ORGANIZATION_ID
AND MOQD.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND MOQD.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
--AND OOD.ORGANIZATION_ID = 1565
GROUP BY OOD.ORGANIZATION_NAME,
OOD.ORGANIZATION_CODE,
MSIB.SEGMENT1,
MSIB.DESCRIPTION,
MSIB.PRIMARY_UOM_CODE,
MSIB.ITEM_TYPE,
MSIB.INVENTORY_ITEM_STATUS_CODE,
MSIB.INVENTORY_PLANNING_CODE,
MSIB.MIN_MINMAX_QUANTITY,
MSIB.MAX_MINMAX_QUANTITY,
MSIB.INVENTORY_ITEM_ID,
MSIB.ORGANIZATION_ID,
MOQD.SUBINVENTORY_CODE,
MIL.CONCATENATED_SEGMENTS
UNION
SELECT OOD.ORGANIZATION_NAME INV_ORG_NAME,
OOD.ORGANIZATION_CODE ORG_CODE,
MSIB.SEGMENT1 ITEM,
MSIB.DESCRIPTION ITEM_DESCRIPTION,
MSIB.PRIMARY_UOM_CODE PRIMARY_UOM_CODE,
MSIB.ITEM_TYPE ITEM_TYPE,
MSIB.INVENTORY_ITEM_STATUS_CODE ITEM_STATUS,
APPS.XXBG_UTILS.GET_LOOKUP_VALUE(MSIB.INVENTORY_PLANNING_CODE,
'MTL_MATERIAL_PLANNING') INVENTORY_PLANNING_METHOD,
MSIB.MIN_MINMAX_QUANTITY MIN_MINMAX_QUANTITY,
MSIB.MAX_MINMAX_QUANTITY MAX_MINMAX_QUANTITY,
MISI.MIN_MINMAX_QUANTITY SUBINV_MIN,
MISI.MAX_MINMAX_QUANTITY SUBINV_MAX,
MSIB.INVENTORY_ITEM_ID,
MSIB.ORGANIZATION_ID,
(SELECT CAT.CATEGORY_SET_NAME
FROM APPS.XXBG_INV_ITEM_CATEGORIES_V CAT
WHERE MSIB.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID(+)
AND MSIB.ORGANIZATION_ID = CAT.ORGANIZATION_ID(+)
AND CAT.CONTROL_LEVEL_DISP = 'Org'
AND CAT.CATEGORY_SET_NAME = 'Criticality') CATEGORY_SET_NAME,
(SELECT CAT.CATEGORY_CONCAT_SEGS
FROM APPS.XXBG_INV_ITEM_CATEGORIES_V CAT
WHERE MSIB.INVENTORY_ITEM_ID = CAT.INVENTORY_ITEM_ID(+)
AND MSIB.ORGANIZATION_ID = CAT.ORGANIZATION_ID(+)
AND CAT.CONTROL_LEVEL_DISP = 'Org'
AND CAT.CATEGORY_SET_NAME = 'Criticality') CATEGORY,
(SELECT CAT1.CATEGORY_CONCAT_SEGS
FROM APPS.XXBG_INV_ITEM_CATEGORIES_V CAT1
WHERE MSIB.INVENTORY_ITEM_ID = CAT1.INVENTORY_ITEM_ID(+)
AND MSIB.ORGANIZATION_ID = CAT1.ORGANIZATION_ID(+)
AND CAT1.CONTROL_LEVEL_DISP = 'Org'
AND CAT1.CATEGORY_SET_NAME = 'Inventory') OWNER_DEPT,
(SELECT NVL(ITEM_COST, 0)
FROM APPS.CST_ITEM_COSTS CIC
WHERE CIC.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND CIC.ORGANIZATION_ID = MSIB.ORGANIZATION_ID) UNIT_ITEM_COST,
0 ON_HAND_QUANTITY,
MISI.SECONDARY_INVENTORY SUBINVENTORY,
MIL.CONCATENATED_SEGMENTS LOCATOR
FROM APPS.ORG_ORGANIZATION_DEFINITIONS OOD,
APPS.MTL_SYSTEM_ITEMS_B MSIB,
APPS.MTL_ITEM_SUB_INVENTORIES MISI,
APPS.MTL_SECONDARY_LOCATORS MSL,
APPS.MTL_ITEM_LOCATIONS_KFV MIL
WHERE OOD.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MSIB.INVENTORY_ITEM_ID = MISI.INVENTORY_ITEM_ID(+)
AND MSIB.ORGANIZATION_ID = MISI.ORGANIZATION_ID(+)
AND MISI.INVENTORY_ITEM_ID = MSL.INVENTORY_ITEM_ID(+)
AND MISI.ORGANIZATION_ID = MSL.ORGANIZATION_ID(+)
AND MISI.SECONDARY_INVENTORY = MSL.SUBINVENTORY_CODE(+)
AND MSL.SECONDARY_LOCATOR = MIL.INVENTORY_LOCATION_ID(+)
AND MSL.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
--AND MSIB.ORGANIZATION_ID = 1565

It takes one hour to give the output.. Could anyone tell me how to optimize this? Please help.