R12:Proposed Netting Report Performance Issue (Doc ID 1268248.1)

Last updated on AUGUST 24, 2016

Applies to:

Oracle Financials Common Modules - Version: 12.0.6 and later   [Release: 12.0 and later ]
Information in this document applies to any platform.
patch/115/sql/FUNXRPTB.pls

Symptoms

On : 12.0.6 version:

Find :
The Proposed Netting Report is suffering from performance issue.

Changes

'SELECT
API.INVOICE_NUM,
ALC.DISPLAYED_FIELD AS INVOICE_TYPE,
API.INVOICE_DATE AS INVOICE_DATE,
API.INVOICE_AMOUNT,
FNAP.INV_CURR_OPEN_AMT AS INVOICE_CURRENCY_OPEN_AMOUNT,
API.INVOICE_CURRENCY_CODE AS INVOICE_CURRENCY,
FNAP.OPEN_AMT AS INV_RECKONING_OPEN_AMOUNT,
FNB.BATCH_CURRENCY AS RECKONING_CURRENCY,
MIN(APS.DUE_DATE) AS DUE_DATE,
SUM(nvl(vat.vat_amount,0)) AS VAT_AMOUNT
FROM FUN_NET_AP_INVS_ALL FNAP,
FUN_NET_BATCHES_ALL FNB,
AP_INVOICES_ALL API,
==> ap_invoice_lines_all ail,
AP_LOOKUP_CODES ALC,
AP_PAYMENT_SCHEDULES_ALL APS,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVS,
(select ail2.invoice_id
,sum(ail2.amount) vat_amount
from ap_invoices_all ai2
,ap_invoice_lines_all ail2
,ap_tax_codes_all atc
where ai2.vendor_id = :SUPPLIER_ID
and ai2.vendor_site_id = :SITE_ID
and ail2.invoice_id = ai2.invoice_id
and ail2.line_type_lookup_code = ''TAX''
and atc.name = ail2.tax_classification_code
and atc.tax_type = ''SALES''
and atc.org_id = ail2.org_id
group by ail2.invoice_id
) vat
WHERE FNAP.INVOICE_ID = API.INVOICE_ID
AND FNAP.BATCH_ID = FNB.BATCH_ID
AND ALC.LOOKUP_CODE = API.INVOICE_TYPE_LOOKUP_CODE
AND ALC.LOOKUP_TYPE = ''INVOICE TYPE''
AND APS.INVOICE_ID = API.INVOICE_ID
AND PV.VENDOR_ID = API.VENDOR_ID
AND PVS.VENDOR_SITE_ID = API.VENDOR_SITE_ID
AND FNAP.BATCH_ID = :BATCH_ID
AND PV.VENDOR_ID = :SUPPLIER_ID
AND PVS.VENDOR_SITE_ID = :SITE_ID
and vat.invoice_id(+) = API.INVOICE_ID
GROUP BY
API.INVOICE_NUM,
ALC.DISPLAYED_FIELD,
API.INVOICE_DATE,
API.INVOICE_AMOUNT,
FNAP.INV_CURR_OPEN_AMT,
API.INVOICE_CURRENCY_CODE,
FNAP.OPEN_AMT,
FNB.BATCH_CURRENCY
ORDER BY
API.INVOICE_NUM');

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms