R12 AP: The Create Accounting Program (XLAACCPB) has a Performance Problem

Last updated on JANUARY 16, 2018

Applies to:

Symptoms

Create Accounting Program (XLAACCPB) is running for a long time. It takes from 1 to 15 hours (Up to 9 days in one case)

The following SQL statement is taking a long time:

SELECT SUM(APHD.AMOUNT), SUM(APHD.INVOICE_DIST_AMOUNT),
 SUM(APHD.BANK_CURR_AMOUNT)
FROM
AP_PAYMENT_HIST_DISTS APHD, AP_PAYMENT_HISTORY_ALL APH WHERE
 APHD.INVOICE_DISTRIBUTION_ID IN ( SELECT :B1 FROM DUAL UNION SELECT
 DISTINCT AID_AWT.INVOICE_DISTRIBUTION_ID FROM AP_INVOICE_DISTRIBUTIONS_ALL
 AID_AWT, AP_INVOICE_DISTRIBUTIONS_ALL AID_ITEM WHERE 1=1 AND
 AID_ITEM.INVOICE_DISTRIBUTION_ID = :B1 AND AID_ITEM.LINE_TYPE_LOOKUP_CODE
 <> 'AWT' AND AID_AWT.INVOICE_ID = AID_ITEM.INVOICE_ID AND
 AID_AWT.AWT_RELATED_ID = AID_ITEM.INVOICE_DISTRIBUTION_ID AND
 AID_AWT.LINE_TYPE_LOOKUP_CODE = 'AWT' ) AND APHD.PAY_DIST_LOOKUP_CODE IN
 ('CASH', 'DISCOUNT', 'AWT') AND NVL(APH.POSTED_FLAG, 'N') IN ('Y', 'S') AND
 APH.PAYMENT_HISTORY_ID = APHD.PAYMENT_HISTORY_ID AND APH.TRANSACTION_TYPE
 IN ('PAYMENT CREATED', 'PAYMENT CANCELLED', 'PAYMENT ADJUSTED', 'MANUAL
 PAYMENT ADJUSTED', 'UPGRADED MANUAL PMT ADJUSTED', 'REFUND RECORDED',
 'REFUND ADJUSTED', 'REFUND CANCELLED', 'MANUAL REFUND ADJUSTED') AND NOT
 EXISTS (SELECT 'Event Reversed' FROM AP_PAYMENT_HISTORY_ALL APH_REL WHERE
 APH_REL.CHECK_ID = APH.CHECK_ID AND NVL(APH_REL.RELATED_EVENT_ID,
 APH_REL.ACCOUNTING_EVENT_ID) = NVL(APH.RELATED_EVENT_ID,
 APH.ACCOUNTING_EVENT_ID) AND APH_REL.REV_PMT_HIST_ID IS NOT NULL)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        7      0.01       0.00          0          0          0           0
Execute 105879     59.10      58.59          0          0          0           0
Fetch   105879   1601.15    1609.02        926   32938724          0      105879
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   211765   1660.26    1667.63        926   32938724          0      105879

Changes

Cause