SQL Query optimization

2699805

It's running for a very long time. Can someone help me with optimizing this query.

select distinct pv.vendor_name,

pv.segment1 as vendor_number,

pv.vendor_type_lookup_code,

apt.name as payment_terms,

pha.segment1 as PO_Num,

count(distinct concat(pv.segment1, (concat(pha.segment1,pla.line_num)))) as number_of_lines,

pah.object_revision_num,

approve_dt.action_date as approve_date,

pa.accepted_flag as po_action_flag,

to_date(pa.action_date) as po_action_date,

pll.promised_date as po_promised_date,

aia.invoice_num,

max(pmt_due.due_date) as payment_due_date, -- latest due date

min(aia.creation_date) as invoice_creation_date, --- earliest invoice creation date,

pha.attribute4 as VMI,

pha.attribute9 as VCI

from apps.po_vendors pv,

apps.po_vendor_sites_all pvs,

apps.ap_terms apt,

apps.po_headers_all pha,

apps.po_action_history pah,

apps.po_lines_all pla,

(select po_header_id, promised_date

from apps.po_line_locations_all

where promised_date is not null) pll,

apps.po_distributions_all pda,

apps.po_acceptances pa,

apps.ap_invoices_all aia,

(select distinct invoice_id, po_distribution_id from apps.ap_invoice_distributions_all) aida,

(select action_hist1.action_date,

action_hist1.object_id,

action_hist1.object_revision_num

from apps.po_action_history action_hist1

where action_hist1.object_type_code = 'PO'

and action_hist1.action_code = 'APPROVE'

)approve_dt,

(select invoice_id,

max(due_date) due_date

from apps.ap_payment_schedules_all

group by invoice_id

  ) pmt_due

where pv.vendor_id = pvs.vendor_id and

pv.terms_id = apt.term_id and

pv.vendor_id = pha.vendor_id and

pha.po_header_id = pla.po_header_id and

pha.po_header_id = pah.object_id and -- po action history table should have every PO, right?

pah.object_id = approve_dt.object_id(+) and

pah.object_revision_num = approve_dt.object_revision_num(+) and

pah.object_id = pa.po_header_id(+) and

pah.object_revision_num = pa.revision_num(+) and

pha.po_header_id = pda.po_header_id and

pha.po_header_id = pll.po_header_id(+) and

pda.po_distribution_id = aida.po_distribution_id(+) and

aida.invoice_id = aia.invoice_id and

aia.invoice_id = pmt_due.invoice_id(+) and

pha.type_lookup_code = 'STANDARD' and

pah.object_type_code = 'PO' and

pvs.vendor_site_code in ('PRIMARY', 'HOME') and

pha.creation_date between '18-JUN-17' and '25-JUN-17'

group by

pv.vendor_name,

apt.name,

pv.segment1,

pv.vendor_type_lookup_code,

pha.segment1,

aia.invoice_num,

pah.object_revision_num,

approve_dt.action_date,

pa.accepted_flag,

pa.action_date,

pll.promised_date,

pha.attribute4,

pha.attribute9