Speed up my query

my query is taking a very long time to run and wanted to know if someone can spot a code improvement that would speed it up.

SELECT DISTINCT
o.organization_CODE as “ORG”,
po.segment1 “PO NUMBER”,
rsh.RECEIPT_NUM “Receipt Number”,
aia.invoice_num as “Invoice Number”,
rsl.creation_date “Receipt Date”,–(Optional Parameter)
(SELECT description
FROM apps.fnd_user fu
WHERE rsl.created_by = fu.user_id) “Received By”,
l.item_description “Item”,
pda.DESTINATION_TYPE_CODE as “PO Type”,
po.CURRENCY_CODE as “Currency Type”,
po.AUTHORIZATION_STATUS “P.O. Status”,
v.vendor_name “Supplier”,
v.segment1 “Supplier Code”,
plt.line_type “Line Type”,
HR1.LOCATION_CODE “SHIP TO”,
loc.quantity “PO Qty”,
loc.quantity_received “Received Qty”,
loc.quantity_BILLED “Billed Qty”,
loc.quantity_cancelled “Cancelled Qty”,
l.BASE_UNIT_PRICE “Unit Price”,
l.BASE_UNIT_PRICE * loc.quantity_received “Received Value”,
po.closed_code “Header Closed Code”,
loc.closed_code “Shipment Closed Code”, --(Optional Parameter)
po.cancel_flag “Cancelled Status”

From
po_headers_all po,
MTL_PARAMETERS o,
ap_suppliers v,
PO_LINE_TYPES plt,
HR_LOCATIONS_ALL HR1,
mtl_system_items_b mtl,
po_lines_all l,
po_line_locations_all loc,
hr_operating_units hro,
PO_DISTRIBUTIONS_ALL pda,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
ap_invoice_distributions_all aida,
ap_invoices_all aia

where
po.po_header_id=l.po_header_id
and aia.invoice_id (+) = aida.invoice_id
and po.po_header_id=pda.po_header_id
and l.po_line_id=pda.po_line_id
and rsh.shipment_header_id = rsl.shipment_header_id
and pda.po_distribution_id =aida.po_distribution_id(+)
and rsl.po_line_id = l.po_line_id
and po.org_id=hro.organization_id
and l.line_type_id=plt.line_type_id
and l.po_line_id=loc.po_line_id
and po.vendor_id=v.vendor_id
AND HR1.SHIP_TO_LOCATION_ID=loc.ship_to_location_id
and loc.ship_to_organization_id=o.organization_id
and plt.line_type = ‘Rework’ --(default parameter not editable)
–and po.segment1 = ‘7460344016’
and o.organization_CODE in (‘CHE’,‘CHS’) --(defined by org user is logged into)
order by o.organization_CODE asc, rsl.creation_date asc;

This topic was automatically closed 41 days after the last reply. New replies are no longer allowed.