Attached Sql is taking too long to execute, kindly advise if there is another way to write this down and any way its performance improvement.
SELECT msib.segment1,
QPLL.list_line_id,
msib.DESCRIPTION,
qlhh.name pricelist,
pv.vendor_name Vendor,
qpll.start_date_active StartDate,
qpll.end_date_active EndDate,
qpll.operand price,
(SELECT mcb1.segment1
FROM mtl_categories_b mcb1,
inv.mtl_system_items_b msib1,
apps.mtl_item_categories mic
WHERE msib1.inventory_item_id = mic.inventory_item_id
AND msib1.organization_id = mic.organization_id
AND mic.category_id = mcb1.category_id
AND msib1.segment1 = msib.segment1
AND ROWNUM = 1
AND mcb1.structure_id =
(SELECT mcs.structure_id
FROM mtl_category_sets_b mcs
WHERE mcs.category_set_id =
(SELECT mcs_tl.category_set_id
FROM mtl_category_sets_tl mcs_tl
WHERE mcs_tl.category_set_name =
'XX_PRICE_GROUP')))
price_group,
(SELECT mcb1.segment1
FROM mtl_categories_b mcb1,
inv.mtl_system_items_b msib1,
apps.mtl_item_categories mic
WHERE msib1.inventory_item_id = mic.inventory_item_id
AND msib1.organization_id = mic.organization_id
AND mic.category_id = mcb1.category_id
AND msib1.segment1 = msib.segment1
AND ROWNUM = 1
AND mcb1.structure_id =
(SELECT mcs.structure_id
FROM mtl_category_sets_b mcs
WHERE mcs.category_set_id =
(SELECT mcs_tl.category_set_id
FROM mtl_category_sets_tl mcs_tl
WHERE mcs_tl.category_set_name =
'XX_PRICE_GROUP')))
CategorytTo,
(SELECT mcb1.segment1
FROM mtl_categories_b mcb1,
inv.mtl_system_items_b msib1,
apps.mtl_item_categories mic
WHERE msib1.inventory_item_id = mic.inventory_item_id
AND msib1.organization_id = mic.organization_id
AND mic.category_id = mcb1.category_id
AND msib1.segment1 = msib.segment1
AND ROWNUM = 1
AND mcb1.structure_id =
(SELECT mcs.structure_id
FROM mtl_category_sets_b mcs
WHERE mcs.category_set_id =
(SELECT mcs_tl.category_set_id
FROM mtl_category_sets_tl mcs_tl
WHERE mcs_tl.category_set_name =
'XX_PRICE_GROUP')))
CategoryFrom,
(SELECT mcst.category_set_name
FROM mtl_system_items_b msib1,
mtl_category_sets mcst,
mtl_item_categories mic
WHERE mic.category_set_id = mcst.category_set_id
AND msib1.organization_id = mic.organization_id
AND msib1.inventory_item_id = mic.inventory_item_id
AND msib1.segment1 = msib.segment1
AND ROWNUM = 1
AND mcst.category_set_name = 'XX_PRICE_GROUP')
Item_Category_name,
(SELECT mcb.segment1
FROM mtl_categories_b mcb,
inv.mtl_system_items_b msib1,
apps.mtl_item_categories mic
WHERE msib1.inventory_item_id = mic.inventory_item_id
AND msib1.organization_id = mic.organization_id
AND mic.category_id = mcb.category_id
AND msib1.segment1 = msib.segment1
AND ROWNUM = 1
AND mcb.structure_id =
(SELECT mcs.structure_id
FROM mtl_category_sets_b mcs
WHERE mcs.category_set_id =
(SELECT mcs_tl.category_set_id
FROM mtl_category_sets_tl mcs_tl
WHERE mcs_tl.category_set_name =
'XX_PRICE_GROUP')))
Item_Category_Value
FROM qp_list_lines qpll,
qp_pricing_attributes qppr,
qp_list_headers_tl qlhh,
mtl_system_items_b msib,
po_approved_supplier_list asl,
po_vendors pv
WHERE qpll.list_header_id = qlhh.list_header_id
AND pv.vendor_id = asl.vendor_id
AND asl.item_id = msib.inventory_item_id
AND msib.organization_id = asl.owning_organization_id
AND qppr.list_line_id = qpll.list_line_id
AND msib.inventory_item_id =
DECODE (UPPER (QPPR.Product_Attr_value),
'ALL', NULL,
QPPR.Product_Attr_Value)
AND (qpll.end_date_active IS NULL OR qpll.end_date_active >= SYSDATE)
AND qlhh.name = 'XX_Pricelist'
GROUP BY msib.segment1,
msib.description,
qpll.list_line_id,
qlhh.name,
pv.vendor_name,
qpll.start_date_active,
qpll.end_date_active,
qpll.operand
ORDER BY msib.segment1;