R12: Performance Issue With Payables Open Interface Import (APXIIMPT) For Refund API (AR_CM_APPLICATION_PUB.ACTIVITY_APPLICATION) (Doc ID 2248196.1)

Last updated on APRIL 04, 2025

Applies to:

Symptoms

On : 12.1.3 version, Transactions Related

Performance Issue With Payables Open Interface Import (APXIIMPT) For Refund API (AR_CM_APPLICATION_PUB.ACTIVITY_APPLICATION)

EXPECTED BEHAVIOR
-----------------------
API should not take long to execute

SQL ID: 9nppnwgynf8s9 Plan Hash: 653406116

SELECT INVOICE_ID, RTRIM(INVOICE_NUM) INVOICE_NUM,
RTRIM(INVOICE_TYPE_LOOKUP_CODE) INVOICE_TYPE_LOOKUP_CODE, INVOICE_DATE,
PO_NUMBER, VENDOR_ID, VENDOR_NUM, VENDOR_NAME, VENDOR_SITE_ID,
VENDOR_SITE_CODE, INVOICE_AMOUNT, RTRIM(INVOICE_CURRENCY_CODE)
INVOICE_CURRENCY_CODE, EXCHANGE_RATE, RTRIM(EXCHANGE_RATE_TYPE)
EXCHANGE_RATE_TYPE, EXCHANGE_DATE, TERMS_ID, TERMS_NAME, TERMS_DATE,
TRIM(DESCRIPTION) DESCRIPTION, AWT_GROUP_ID, AWT_GROUP_NAME,
PAY_AWT_GROUP_ID, PAY_AWT_GROUP_NAME, AMOUNT_APPLICABLE_TO_DISCOUNT,
SYSDATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, SYSDATE, CREATED_BY,
RTRIM(STATUS) STATUS, TRIM(ATTRIBUTE_CATEGORY) ATTRIBUTE_CATEGORY,
TRIM(ATTRIBUTE1) ATTRIBUTE1, TRIM(ATTRIBUTE2) ATTRIBUTE2, TRIM(ATTRIBUTE3)
ATTRIBUTE3, TRIM(ATTRIBUTE4) ATTRIBUTE4, TRIM(ATTRIBUTE5) ATTRIBUTE5,
TRIM(ATTRIBUTE6) ATTRIBUTE6, TRIM(ATTRIBUTE7) ATTRIBUTE7, TRIM(ATTRIBUTE8)
ATTRIBUTE8, TRIM(ATTRIBUTE9) ATTRIBUTE9, TRIM(ATTRIBUTE10) ATTRIBUTE10,
TRIM(ATTRIBUTE11) ATTRIBUTE11, TRIM(ATTRIBUTE12) ATTRIBUTE12,
TRIM(ATTRIBUTE13) ATTRIBUTE13, TRIM(ATTRIBUTE14) ATTRIBUTE14,
TRIM(ATTRIBUTE15) ATTRIBUTE15, TRIM(GLOBAL_ATTRIBUTE_CATEGORY)
GLOBAL_ATTRIBUTE_CATEGORY, TRIM(GLOBAL_ATTRIBUTE1) GLOBAL_ATTRIBUTE1,
TRIM(GLOBAL_ATTRIBUTE2) GLOBAL_ATTRIBUTE2, TRIM(GLOBAL_ATTRIBUTE3)
GLOBAL_ATTRIBUTE3, TRIM(GLOBAL_ATTRIBUTE4) GLOBAL_ATTRIBUTE4,
TRIM(GLOBAL_ATTRIBUTE5) GLOBAL_ATTRIBUTE5, TRIM(GLOBAL_ATTRIBUTE6)
GLOBAL_ATTRIBUTE6, TRIM(GLOBAL_ATTRIBUTE7) GLOBAL_ATTRIBUTE7,
TRIM(GLOBAL_ATTRIBUTE8) GLOBAL_ATTRIBUTE8, TRIM(GLOBAL_ATTRIBUTE9)
GLOBAL_ATTRIBUTE9, TRIM(GLOBAL_ATTRIBUTE10) GLOBAL_ATTRIBUTE10,
TRIM(GLOBAL_ATTRIBUTE11) GLOBAL_ATTRIBUTE11, TRIM(GLOBAL_ATTRIBUTE12)
GLOBAL_ATTRIBUTE12, TRIM(GLOBAL_ATTRIBUTE13) GLOBAL_ATTRIBUTE13,
TRIM(GLOBAL_ATTRIBUTE14) GLOBAL_ATTRIBUTE14, TRIM(GLOBAL_ATTRIBUTE15)
GLOBAL_ATTRIBUTE15, TRIM(GLOBAL_ATTRIBUTE16) GLOBAL_ATTRIBUTE16,
TRIM(GLOBAL_ATTRIBUTE17) GLOBAL_ATTRIBUTE17, TRIM(GLOBAL_ATTRIBUTE18)
GLOBAL_ATTRIBUTE18, TRIM(GLOBAL_ATTRIBUTE19) GLOBAL_ATTRIBUTE19,
TRIM(GLOBAL_ATTRIBUTE20) GLOBAL_ATTRIBUTE20, RTRIM(PAYMENT_CURRENCY_CODE)
PAYMENT_CURRENCY_CODE, PAYMENT_CROSS_RATE, RTRIM(PAYMENT_CROSS_RATE_TYPE)
PAYMENT_CROSS_RATE_TYPE, PAYMENT_CROSS_RATE_DATE, DOC_CATEGORY_CODE,
RTRIM(VOUCHER_NUM) VOUCHER_NUM, RTRIM(PAYMENT_METHOD_CODE)
PAYMENT_METHOD_CODE, RTRIM(PAY_GROUP_LOOKUP_CODE) PAY_GROUP_LOOKUP_CODE,
GOODS_RECEIVED_DATE, INVOICE_RECEIVED_DATE, GL_DATE,
ACCTS_PAY_CODE_COMBINATION_ID, RTRIM(ACCTS_PAY_CODE_CONCATENATED,'-'),
UPPER(EXCLUSIVE_PAYMENT_FLAG), PREPAY_NUM, PREPAY_LINE_NUM,
PREPAY_APPLY_AMOUNT, PREPAY_GL_DATE, UPPER(INVOICE_INCLUDES_PREPAY_FLAG),
NO_XRATE_BASE_AMOUNT, REQUESTER_ID, ORG_ID, OPERATING_UNIT, RTRIM(SOURCE)
SOURCE, GROUP_ID, REQUEST_ID, WORKFLOW_FLAG, VENDOR_EMAIL_ADDRESS,
NVL(CALC_TAX_DURING_IMPORT_FLAG, 'N'), CONTROL_AMOUNT,
ADD_TAX_TO_INV_AMT_FLAG, TAX_RELATED_INVOICE_ID, RTRIM(TAXATION_COUNTRY)
TAXATION_COUNTRY, RTRIM(DOCUMENT_SUB_TYPE) DOCUMENT_SUB_TYPE,
RTRIM(SUPPLIER_TAX_INVOICE_NUMBER) SUPPLIER_TAX_INVOICE_NUMBER,
SUPPLIER_TAX_INVOICE_DATE, SUPPLIER_TAX_EXCHANGE_RATE,
TAX_INVOICE_RECORDING_DATE, TAX_INVOICE_INTERNAL_SEQ, LEGAL_ENTITY_ID, NULL,
AP_IMPORT_UTILITIES_PKG.GET_TAX_ONLY_RCV_MATCHED_FLAG(INVOICE_ID),
AP_IMPORT_UTILITIES_PKG.GET_TAX_ONLY_FLAG(INVOICE_ID), APPLY_ADVANCES_FLAG,
APPLICATION_ID, PRODUCT_TABLE, REFERENCE_KEY1, REFERENCE_KEY2,
REFERENCE_KEY3, REFERENCE_KEY4, REFERENCE_KEY5, REFERENCE_1, REFERENCE_2,
NET_OF_RETAINAGE_FLAG, RTRIM(CUST_REGISTRATION_CODE) CUST_REGISTRATION_CODE,
RTRIM(CUST_REGISTRATION_NUMBER) CUST_REGISTRATION_NUMBER,
PAID_ON_BEHALF_EMPLOYEE_ID, PARTY_ID, PARTY_SITE_ID,
RTRIM(PAY_PROC_TRXN_TYPE_CODE) PAY_PROC_TRXN_TYPE_CODE,
RTRIM(PAYMENT_FUNCTION) PAYMENT_FUNCTION, RTRIM(PAYMENT_PRIORITY)
PAYMENT_PRIORITY, RTRIM(BANK_CHARGE_BEARER) BANK_CHARGE_BEARER,
RTRIM(REMITTANCE_MESSAGE1) REMITTANCE_MESSAGE1, RTRIM(REMITTANCE_MESSAGE2)
REMITTANCE_MESSAGE2, RTRIM(REMITTANCE_MESSAGE3) REMITTANCE_MESSAGE3,
RTRIM(UNIQUE_REMITTANCE_IDENTIFIER) UNIQUE_REMITTANCE_IDENTIFIER,
URI_CHECK_DIGIT, SETTLEMENT_PRIORITY, RTRIM(PAYMENT_REASON_CODE)
PAYMENT_REASON_CODE, RTRIM(PAYMENT_REASON_COMMENTS) PAYMENT_REASON_COMMENTS,
RTRIM(DELIVERY_CHANNEL_CODE) DELIVERY_CHANNEL_CODE,
EXTERNAL_BANK_ACCOUNT_ID, ORIGINAL_INVOICE_AMOUNT , DISPUTE_REASON,
RTRIM(REMIT_TO_SUPPLIER_NAME) REMIT_TO_SUPPLIER_NAME, REMIT_TO_SUPPLIER_ID ,
RTRIM(REMIT_TO_SUPPLIER_SITE) REMIT_TO_SUPPLIER_SITE,
REMIT_TO_SUPPLIER_SITE_ID, RELATIONSHIP_ID, REMIT_TO_SUPPLIER_NUM ,
REQUESTER_LAST_NAME ,REQUESTER_FIRST_NAME ,REQUESTER_EMPLOYEE_NUM ,NULL ,
NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL
FROM
AP_INVOICES_INTERFACE WHERE ((STATUS IS NULL) OR (STATUS = 'REJECTED')) AND
SOURCE = :B4 AND ((:B3 IS NULL AND NVL(INVOICE_TYPE_LOOKUP_CODE, 'STANDARD')
<> 'PAYMENT REQUEST') OR (INVOICE_ID = :B3 )) AND NVL(WORKFLOW_FLAG,'D') =
'D' AND ( (:B2 IS NULL) OR (ROWNUM <= :B2 )) AND ( (ORG_ID IS NOT NULL AND
:B1 IS NOT NULL AND ORG_ID = :B1 ) OR (:B1 IS NULL AND ORG_ID IS NOT NULL
AND (MO_GLOBAL.CHECK_ACCESS(ORG_ID)= 'Y')) OR (:B1 IS NOT NULL AND ORG_ID
IS NULL) OR (:B1 IS NULL AND ORG_ID IS NULL)) ORDER BY ORG_ID, INVOICE_ID,
VENDOR_ID, VENDOR_NUM, VENDOR_NAME, VENDOR_SITE_ID, VENDOR_SITE_CODE,
INVOICE_NUM FOR UPDATE OF INVOICE_ID NOWAIT

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 56 76.25 259.49 6536604 6578320 56 0
Fetch 112 0.02 0.03 0 0 0 56
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 169 76.27 259.53 6536604 6578320 56 56

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.