sql query is taking long time to execute

See more:

SQL

SELECT /*+RULE*/
pv.vendor_name
--y.inventory_item_id,
,mc.segment1 Business_unit
,mc.segment2 Product_line
,y.segment1 "ITEM"
,mtp.organization_code
,y.description Item_description
,y.attribute1 country_of_origin
,y.primary_uom_code
--,y.attribute2 "Tarrif_Code"
,(
SELECT mcr.attribute2 FROM mtl_cross_references mcr WHERE CROSS_REFERENCE_TYPE = 'CA Purchase'
AND mcr.inventory_item_id = y.inventory_item_id
AND ROWNUM < 2
) "Tarrif_Code"
--,y.attribute3 "Tarrif_Class"
,(
SELECT mcr.attribute3 FROM mtl_cross_references mcr WHERE CROSS_REFERENCE_TYPE = 'CA Purchase'
AND mcr.inventory_item_id = y.inventory_item_id
AND ROWNUM < 2
) "Tarrif_Class"
,
(SELECT NVL(ffvv.description, 0.00) FROM FND_FLEX_VALUES_VL ffvv
WHERE ffvv.flex_value
=
(
(
SELECT mcr.attribute2 FROM mtl_cross_references mcr WHERE CROSS_REFERENCE_TYPE = 'CA Purchase'
AND mcr.inventory_item_id = y.inventory_item_id
AND ROWNUM < 2
)
||'_'||
(
SELECT mcr.attribute3 FROM mtl_cross_references mcr WHERE CROSS_REFERENCE_TYPE = 'CA Purchase'
AND mcr.inventory_item_id = y.inventory_item_id
AND ROWNUM < 2
)
)
) "Tarrif Rate"
,pv.ATTRIBUTE1 "Freight_Rate"
,pq.currency_code
,pv.attribute2 Brokerage_Rate
,cic.item_cost Std_frozen_cost
,cic.COST_TYPE_ID
,cct.COST_TYPE
,pq.vendor_item_num
,pq.unit_price
,y.inventory_item_status_code item_status_code
,(moqd.primary_transaction_quantity) ON_hand_Qty
,NVL((SELECT SUM(NVL(quantity_invoiced,0)) FROM ra_customer_trx_all a1, ra_customer_trx_lines_all b1
WHERE a1.customer_trx_id = b1.customer_trx_id
AND b1.inventory_item_id = y.inventory_item_id
AND trx_date BETWEEN TRUNC(SYSDATE - 366) AND TRUNC(SYSDATE-1)
AND a1.complete_flag='Y') + (SELECT SUM(NVL(quantity,0)) FROM XXAB.XXAB_BRM_SALES_ANALYSIS_REPT a
WHERE a.inventory_item_id = y.inventory_item_id
AND a.fiscal_date BETWEEN TRUNC(SYSDATE - 366) AND TRUNC(SYSDATE-1)),0) total_units_sold
FROM
apps.mtl_system_items_b y,
mtl_parameters mtp,
--FND_FLEX_VALUES_VL ffvv,
(SELECT inventory_item_id, organization_id, SUM(primary_transaction_quantity) primary_transaction_quantity
FROM mtl_onhand_quantities_detail
GROUP BY organization_id, inventory_item_id) moqd
, CST_COST_TYPES_V cct
, mtl_item_categories mic
, mtl_categories_vl mc
, cst_item_costs cic
, MRP_ITEM_SOURCING ms
, po_vendors pv
,(SELECT NVL(b.vendor_product_num,' ') vendor_item_num,a.vendor_id, b.item_id, a.currency_code,MAX(b.unit_price) unit_price
FROM po_headers_all a, po_lines_all b
WHERE a.type_lookup_code ='QUOTATION'
AND a.po_header_id = b.po_header_id
GROUP BY NVL(b.vendor_product_num,' ') ,a.vendor_id, b.item_id, a.currency_code
) pq
WHERE
y.organization_id=mtp.organization_id -- 85 GRW 20060406
AND moqd.inventory_item_id(+) = y.inventory_item_id
AND moqd.organization_id(+) = y.organization_id
AND mic.inventory_item_id = y.inventory_item_id
AND mic.organization_id = y.organization_id
AND mic.category_id = mc.category_id
AND cic.COST_TYPE_ID = cct.COST_TYPE_ID
--and cic.cost_type_id(+)=1
AND cic.inventory_item_id(+) = y.inventory_item_id
AND cic.organization_id(+) = y.organization_id
AND mc.structure_id = 50242 ----50270 --Product structure
AND mic.category_set_id = 1100000001
AND ms.inventory_item_id(+) = y.inventory_item_id
AND compile_designator(+)='ACCO_MRP'
AND ms.vendor_id = pv.vendor_id(+)
AND pq.item_id(+)= ms.inventory_item_id
AND pq.vendor_id(+) = ms.vendor_id
--AND ffvv.flex_value(+) = (y.ATTRIBUTE2 || '_' || y.ATTRIBUTE3)
AND mtp.organization_code = 'BRM'