R12.2.X: Performance Issues for Create Settlement Batches program

(Doc ID 1947560.1)

Last updated on NOVEMBER 22, 2014

Applies to:

Oracle Payments - Version 12.2 to 12.2.4 [Release 12.2]
Information in this document applies to any platform.

Symptoms


Users experiencing serious performance issue with the create settlement batches process.
It runs more than a day for only one transaction.

 

According to the trace file, the following is the most expensive query:

 

SELECT

                         inv_line.line_number,
                         inv_line.po_number,
                         inv_line.line_type,
                         inv_line.description,
                         inv_line.extended_amount,inv_line.invoice_currency_code,inv_line.unit_price,
                         inv_line.quantity,
                         inv_line.unit_of_measure,
                         inv_line.inventory_item_id,
                         inv_line.discount_amount,
                         inv_line.sales_tax_amount,
                         inv_line.tax_rate,
                         inv_line.vat_tax_amount,
                         inv_line.doc_unique_ref
                       FROM ar_document_lines_v inv_line,ar_docs_receivables_v inv_header,
                         iby_trxn_summaries_all txn
                       WHERE(inv_header.doc_unique_ref = inv_line.doc_unique_ref(+))
                        AND(txn.initiator_extension_id = inv_header.order_ext_id)
                        AND(txn.trxntypeid IN('8',   '9',   '100'))
                        AND(txn.mbatchid = :1)

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