REL 12.2 AP: COTS Format Program (R12) / Format Payment Instructions with Text Output taking over 2 hours to run for 60 payments.

Last updated on JANUARY 16, 2018

Applies to:

Symptoms

On : 12.2.2 version, Other-MiscIss & KeyIndReport

When Attempting to run Format Payment Instructions with Text Output it is taking over 2 hours to run for 60 payments.

PPP is configured to use the following formats.

US Federal Bulk NCR Format
US Federal Bulk CCDP Format

SQL ID: fdg8v3jwh36zx

SELECT EXT_BANK_ACCOUNT
FROM
 IBY_XML_FD_PEBA_1_0_VD WHERE PAYMENT_ID = :B1


call     count       cpu    elapsed       disk      query    current       rows
------- ------  -------- ---------- ---------- ---------- ---------- ----------
Parse       53      0.01       0.02          0          0          0   0
Execute    106      0.21       0.35          0        120          0   0
Fetch       53   7839.21   29290.13  380339532  373462091        106  53
------- ------  -------- ---------- ---------- ---------- ---------- ----------
total      212   7839.43   29290.50  380339532  373462211        106 53

These other SQL statements also are taking long time:

SQL ID : 4hx07m9czdwvr
SELECT XMLAGG(XML_PMT_LVL.PAYMENT) FROM IBY_XML_FD_PMT_1_0_V XML_PMT_LVL, IBY_PAY_INSTRUCTIONS_ALL INS WHERE XML_PMT_LVL.PAYMENT_INSTRUCTION_ID = :B1 AND INS.PAYMENT_INSTRUCTION_ID = XML_PMT_LVL.PAYMENT_INSTRUCTION_ID AND ((XML_PMT_LVL.PAYMENT_STATUS IN ('INSTRUCTION_CREATED', 'VOID_BY_SETUP', 'VOID_BY_OVERFLOW') AND INS.PROCESS_TYPE = 'STANDARD') OR INS.PROCESS_TYPE = 'IMMEDIATE')

SQL ID: 2x8xxx1d3gz2y
SELECT XMLTYPE.GETCLOBVAL(INSTRUCTION) FROM IBY_XML_FD_INS_1_0_V WHERE PAYMENT_INSTRUCTION_ID = :B1

Changes

 NONE

Cause