This is a query which I have to use as part of extracting Receipts based on certain conditions from Oracle 11i as a part of Conversion.
--> One important check is to check for invoices on hold. I just need to pull the details wherein the invoices are on hold. And the condition for checking is
In AP_HOLDS_ALL table -- 1) the invoice should exist 2) release lookup code should be NULL 3) status flag should be S or NULL and 4) there should not exist any line in AP_HOLDS_ALL table with same invoice ID with release lookup code as not null.
I have tried and added the below criteria in last of the following query, but it fetches the rows very very slow (100 000 records in a day).
How to improve the performance?
select * -- multiple Columns
from rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_transactions rt,
hr_operating_units hou,
po_headers_all poh,
po_lines_all pol,
po_line_locations_all pll,
po_distributions_all pda,
po_vendors pv,
po_vendor_sites_all pvs
where 1 = 1
and rsh.shipment_header_id = rsl.shipment_header_id
and rsh.shipment_header_id = rt.shipment_header_id
and rsl.shipment_line_id = rt.shipment_line_id
and rt.po_header_id = poh.po_header_id
and rt.po_line_id = pol.po_line_id
and rt.po_line_location_id = pll.line_location_id
and rt.po_distribution_id = pda.po_distribution_id
and poh.po_header_id = pol.po_header_id
and pol.po_line_id = pll.po_line_id
and pll.line_location_id = pda.line_location_id
and poh.org_id = hou.organization_id
and poh.type_lookup_code = 'STANDARD'
and poh.authorization_status = 'APPROVED'
and poh.approved_flag = 'Y'
and rsh.ship_to_org_id = pll.ship_to_organization_id
and rt.organization_id = pll.ship_to_organization_id
and pol.org_id = hou.organization_id
and pll.org_id = hou.organization_id
and pda.org_id = hou.organization_id
and hou.date_to is null
and (rt.transaction_type = 'DELIVER' or rt.transaction_type = 'RECEIVE')
and rt.vendor_site_id = pvs.vendor_site_id
and rt.vendor_id = pv.vendor_id
and pv.vendor_id = pvs.vendor_id
and (nvl(pda.quantity_ordered, 0) - nvl(pda.quantity_cancelled, 0)) > 0
and nvl(pda.quantity_delivered, 0) > 0
and nvl(pda.quantity_billed, 0) > 0
and nvl(rsl.quantity_received, 0) > 0
and ((nvl(pda.quantity_delivered, 0) = nvl(pda.quantity_billed, 0)) or
(nvl(pda.quantity_delivered, 0) > nvl(pda.quantity_billed, 0)) or
(nvl(pda.quantity_delivered, 0) < nvl(pda.quantity_billed, 0)))
and exists
(select aida.po_distribution_id
from ap_invoices_all aia, ap_invoice_distributions_all aida
where aia.cancelled_date is null
and aida.po_distribution_id = pda.po_distribution_id
and exists
(select c.invoice_id
from ap_holds_all c
where c.release_lookup_code is null
and c.invoice_id = aia.invoice_id
and c.org_id = nvl(p_leg_operating_unit, c.org_id)
and (c.status_flag = 'S' or c.status_flag is null)
and not exists
(select 1
from ap_holds_all d
where d.invoice_id = c.invoice_id
and d.org_id = nvl(p_leg_operating_unit, d.org_id)
and d.release_lookup_code is not null))
and aia.org_id = nvl(p_leg_operating_unit, aia.org_id)
and aia.invoice_id = aida.invoice_id)
and poh.org_id = nvl(p_leg_operating_unit, poh.org_id)
- p_leg_operating_unit is a parameter which is having NULL value as I am trying to get the values for all the OU's
** last exists is causing the issue.
