EAP: Performance Issue With Pay Cycle Process AP_APY2015 at Step AP_PYMNTID.IDMAIN.Step41 SQL
Last updated on DECEMBER 04, 2017
Applies to:PeopleSoft Enterprise FIN Payables - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.
Form ID EFT2 (EFT w/Adv) is assigned to payment method EFT and all vouchers have option 'Pay Separate' selected. When the pay cycle processes a large batch of payments there is a performance issue with the AP_PYMNTID. IDMAIN.Step41 Do Select loop.
The loop will process one payment at a time and for each payment will run the following SQL:
-- 18:45:27.007 .......(AP_PYMNTID.IDMAIN.Step41) (SQL)
UPDATE PS_PAY_ADV2_TMP SET PYMNT_OVERFLOW_ID = 'ADVICE' WHERE 'ACH00' =
PAY_CYCLE AND 2 = PAY_CYCLE_SEQ_NUM AND 'SHARE' = REMIT_SETID AND
'USA0000002' = REMIT_VENDOR AND 'SHARE' = BANK_SETID AND 'USBNK' = BANK_CD
AND 'CHCK' = BANK_ACCT_KEY AND 'ACH' = PYMNT_METHOD AND PYMNT_OVERFLOW_ID <>
'ADVICE' AND ' ' = PYMNT_ID_REF AND 'X' = 'X'
-- Row(s) affected: 0
The bind for PYMNT_ID_REF is always blank because the Do Select does not include it in the select list, therefore no rows are ever updated.
1. Assign format id EFT2 (EFT w/Adv) to payment method EFT of bank account.
2. Create vouchers with separate payment selected.
3. Run pay cycle.
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