R12 AP: Format Payment Instructions With Text Output Running For More Than 8 Hours (Doc ID 1268028.1)

Last updated on JANUARY 23, 2017

Applies to:

Oracle Payables - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms


On : 12.0.4 and 12.1.3 versions,
Format Payment Instructions with Text Output (IBY_FD_PAYMENT_FORMAT_TEXT) is running for more than 8 hours.

Following SQLs is consuming long time:

SELECT * FROM (SELECT I.payment_instruction_id, PMT.payment_service_request_id,
I.pay_admin_assigned_ref_code, I.bank_assigned_ref_code, I.creation_date,
I.payments_complete_code, I.org_id, IBY_FD_USER_API_PUB.Pmt_Instr_Action_Enabled
(I.payment_instruction_status, I.org_id, I.payment_instruction_id, I.request_id)
AS Action_Switcher, IBY_FD_USER_API_PUB.Payment_Instruction_Action(I.payment_instruction_status)
AS Destination, IBY1.meaning status FROM iby_pay_instructions_all I, iby_payments_all PMT,
fnd_lookups IBY1 WHERE PMT.payment_instruction_id = I.payment_instruction_id
AND I.payment_instruction_status = IBY1.lookup_code (+)
AND IBY1.lookup_type(+)='IBY_PAY_INSTRUCTION_STATUSES' GROUP BY I.payment_instruction_id,
PMT.payment_service_request_id , I.pay_admin_assigned_ref_code, I.bank_assigned_ref_code,
I.creation_date, I.payments_complete_code, I.org_id,
IBY_FD_USER_API_PUB.Pmt_Instr_Action_Enabled(I.payment_instruction_status, I.org_id,
I.payment_instruction_id, I.request_id ),
IBY_FD_USER_API_PUB.Payment_Instruction_Action(I.payment_instruction_status ),
IBY1.meaning) QRSLT WHERE PAYMENT_SERVICE_REQUEST_ID = :1

 

SELECT SYSDATE, :B4 , (SI.AMOUNT_REMAINING - SI.PROPOSED_PAYMENT_AMOUNT -
NVL(IBYDOCS.PAYMENT_CURR_DISCOUNT_TAKEN, 0)), 0, DECODE(SI.AMOUNT_REMAINING -
SI.PROPOSED_PAYMENT_AMOUNT - NVL(IBYDOCS.PAYMENT_CURR_DISCOUNT_TAKEN, 0), 0, 'Y', 'P'),
NULL, PS.INVOICE_ID, PS.PAYMENT_NUM FROM AP_PAYMENT_SCHEDULES_ALL PS, AP_INVOICES_ALL INV,
AP_SELECTED_INVOICES_ALL SI, IBY_FD_PAYMENTS_V IBYPMTS, IBY_FD_DOCS_PAYABLE_V IBYDOCS
WHERE SI.CHECKRUN_NAME = :B3 AND SI.PAYMENT_NUM = PS.PAYMENT_NUM AND
SI.INVOICE_ID = PS.INVOICE_ID AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF1 = TO_CHAR(SI.CHECKRUN_ID)
AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF2 = TO_CHAR(SI.INVOICE_ID)
AND IBYDOCS.CALLING_APP_DOC_UNIQUE_REF3 = TO_CHAR(SI.PAYMENT_NUM)
AND IBYPMTS.PAYMENT_ID = IBYDOCS.PAYMENT_ID AND IBYPMTS.COMPLETED_PMTS_GROUP_ID = :B2
AND IBYPMTS.ORG_ID = :B1 AND IBYPMTS.ORG_TYPE = 'OPERATING_UNIT' AND INV.INVOICE_ID = SI.INVOICE_ID
AND INV.INVOICE_ID = PS.INVOICE_ID AND INV.INVOICE_TYPE_LOOKUP_CODE <> 'INTEREST'

 

Changes

 After upgraded from 11i to R12

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