My Oracle Support Banner

Proposed Netting Report Has a Poor Performance and Does Not Complete (Doc ID 1918460.1)

Last updated on FEBRUARY 09, 2017

Applies to:

Oracle Receivables - Version 12.2.6 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.x version, Receipts Related

You are attempting to run the Proposed Netting Report and it has poor performance and never completes.

The following query, from the trace file, is the slowest query:

SELECT API.INVOICE_NUM,
      ALC.DISPLAYED_FIELD AS INVOICE_TYPE,
      API.INVOICE_DATE AS INVOICE_DATE,
      LTRIM(TO_CHAR(API.INVOICE_AMOUNT,
                    '999999999999999999999.999999999999')) AS INVOICE_AMOUNT,
      LTRIM(TO_CHAR(FNAP.INV_CURR_OPEN_AMT,
                    '999999999999999999999.999999999999')) AS
INVOICE_CURRENCY_OPEN_AMOUNT,
      LTRIM(TO_CHAR(decode(FNAP.APPLIED_DISC,
                           0,
                           null,
                           decode('SINGLE_CURRENCY',
                                  'ACCOUNTING_CURRENCY',
                                
FUN_NET_ARAP_PKG.Derive_Conv_Amt(FNB.batch_id,
                                                                  
API.invoice_id,
                                                                  
FNAP.APPLIED_DISC,
                                                                   'AP'),
                                  FNAP.APPLIED_DISC)),
                    '999999999999999999999.999999999999')) AS
INV_APPLIED_DISC_AMOUNT,
      LTRIM(TO_CHAR(decode(FNAP.NETTED_AMT,
                           0,
                           null,
                           decode('SINGLE_CURRENCY',
                                  'ACCOUNTING_CURRENCY',
                                
FUN_NET_ARAP_PKG.Derive_Conv_Amt(FNB.batch_id,
                                                                  
API.invoice_id,
                                                                  
FNAP.NETTED_AMT,
                                                                   'AP'),
                                  FNAP.NETTED_AMT)),
                    '999999999999999999999.999999999999')) AS
NETTED_AMT_INV_CURR,
      API.INVOICE_CURRENCY_CODE AS INVOICE_CURRENCY,
      LTRIM(TO_CHAR(FNAP.OPEN_AMT, '999999999999999999999.999999999999')) AS
INV_RECKONING_OPEN_AMOUNT,
      FNB.BATCH_CURRENCY AS RECKONING_CURRENCY,
      MIN(APS.DUE_DATE) AS DUE_DATE,
      LTRIM(TO_CHAR(SUM(nvl(vat.vat_amount, 0)),
                    '999999999999999999999.999999999999')) 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,
         FNB.batch_id,
         API.invoice_id,
         FNAP.APPLIED_DISC,
         FNAP.NETTED_AMT
ORDER BY API.INVOICE_NUM

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
 1. Bug Summary
 2. Fixed File(s)
 3. Recommended Patch(es)
 4. Solution Steps
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.