My Oracle Support Banner

R12: AP: The Invoices Search Screen Has Performance Issues (Doc ID 2019334.1)

Last updated on AUGUST 19, 2020

Applies to:

Oracle Payables - Version 12.1.1 to 12.2.8 [Release 12.1 to 12.2]
Information in this document applies to any platform.

Symptoms

The Invoices Search screen has performance issue.  The following SQL is causing the issue:

 

SQL ID: 4hp0007p2a86h Plan Hash: 1767258300

SELECT /*+ push_pred(AP_INVOICES_V.pv) push_pred(AP_INVOICES_V.pvs) */
 CUST_REGISTRATION_NUMBER,INVOICE_TYPE,QUICK_PO_NUMBER,VENDOR_NAME,
 VENDOR_NUMBER,VENDOR_SITE_CODE,INVOICE_DATE,INVOICE_NUM,
 INVOICE_CURRENCY_CODE,INVOICE_AMOUNT,ORIGINAL_INVOICE_AMOUNT,DISPUTE_REASON,
 TOTAL_TAX_AMOUNT,CONTROL_AMOUNT,GL_DATE,PAYMENT_CURRENCY_CODE,
 PAYMENT_CROSS_RATE_DATE,PAYMENT_CROSS_RATE_TYPE,PAY_CURR_INVOICE_AMOUNT,
 PAYMENT_CROSS_RATE,DISTRIBUTION_SET_NAME,DESCRIPTION,QUICK_CREDIT,
 CREDITED_INVOICE_ID,CREDITED_INVOICE_NUM,PROJECT,TASK,EXPENDITURE_ITEM_DATE,
 EXPENDITURE_TYPE,EXPENDITURE_ORGANIZATION_NAME,PA_QUANTITY,USER_RATE_TYPE,
 EXCHANGE_DATE,BASE_AMOUNT,TERMS_DATE,TERMS_NAME,PAYMENT_METHOD_CODE,
 IBY_PAYMENT_METHOD,PAY_GROUP_LOOKUP_CODE,EXCLUSIVE_PAYMENT_FLAG,
 AMOUNT_APPLICABLE_TO_DISCOUNT,INVOICE_RECEIVED_DATE,GOODS_RECEIVED_DATE,
 PREPAYMENT_TYPE_LOOKUP_CODE,EARLIEST_SETTLEMENT_DATE,AWT_GROUP_NAME,
 PAY_AWT_GROUP_NAME,CUST_REGISTRATION_CODE,AMOUNT_WITHHELD,PREPAID_AMOUNT,
 DOC_CATEGORY_NAME,DOC_SEQUENCE_NAME,APPROVED_AMOUNT,APPROVAL_STATUS,
 APPROVAL_DESCRIPTION,CANCELLED_DATE,CANCELLED_AMOUNT,BATCH_NAME,ROW_ID,
 ACCTS_PAY_CODE_COMBINATION_ID,ALWAYS_TAKE_DISC_FLAG,AMOUNT_PAID,ATTRIBUTE1,
 ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,
 ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,
 ATTRIBUTE14,ATTRIBUTE15,ATTRIBUTE_CATEGORY,AUTHORIZED_BY,AWT_FLAG,
 AWT_GROUP_ID,PAY_AWT_GROUP_ID,BATCH_ID,CANCELLED_BY,CREATED_BY,
 CREATION_DATE,DISCOUNT_AMOUNT_TAKEN,DISTRIBUTION_SET_ID,DOC_CATEGORY_CODE,
 DOC_SEQUENCE_ID,DOC_SEQUENCE_VALUE,EXCHANGE_RATE,EXCHANGE_RATE_TYPE,
 EXCLUDE_FREIGHT_FROM_DISCOUNT,EXPENDITURE_ORGANIZATION_ID,HIGHEST_LINE_NUM,
 HOLD_FUTURE_PAYMENTS_FLAG,INVOICE_AMOUNT_LIMIT,LINE_TOTAL,INVOICE_ID,
 INCOME_TAX_REGION,INVOICE_TYPE_LOOKUP_CODE,LAST_UPDATED_BY,LAST_UPDATE_DATE,
 LAST_UPDATE_LOGIN,MINIMUM_ACCOUNTABLE_UNIT,NUM_1099,ORG_ID,
 PA_DEFAULT_DIST_CCID,PAY_DATE_BASIS_LOOKUP_CODE,PAYMENT_AMOUNT_TOTAL,
 PAYMENT_PRIORITY,PAYMENT_STATUS,PAYMENT_STATUS_FLAG,PO_HEADER_ID,
 POSTING_STATUS,PRECISION,PROJECT_ID,QUICK_PO_HEADER_ID,TYPE_1099,
 RECURRING_PAY_NUM,RECURRING_PAYMENT_ID,RECURRING_PAYMENT_TYPE,
 RECURRING_PERIOD_TYPE,REFERENCE_1,REFERENCE_2,SET_OF_BOOKS_ID,
 SET_OF_BOOKS_NAME,SITE_ALLOW_AWT_FLAG,SOURCE,TASK_ID,TEMP_CANCELLED_AMOUNT,
 TERMS_DATE_BASIS,TERMS_ID,VENDOR_ID,VENDOR_PREPAY_AMOUNT,VENDOR_SITE_ID,
 VOUCHER_NUM,TAXATION_COUNTRY_NAME,TAXATION_COUNTRY,
 TRX_BUSINESS_CATEGORY_NAME,TRX_BUSINESS_CATEGORY,USER_DEFINED_FISC_CLASS,
 TAX_RELATED_INVOICE_NUM,TAX_RELATED_INVOICE_ID,DOCUMENT_SUB_TYPE,
 SELF_ASSESSED_TAX_AMOUNT,TAX_INVOICE_INTERNAL_SEQ,
 SUPPLIER_TAX_INVOICE_NUMBER,TAX_INVOICE_RECORDING_DATE,
 SUPPLIER_TAX_INVOICE_DATE,SUPPLIER_TAX_EXCHANGE_RATE,PORT_OF_ENTRY_CODE,
 REMIT_TO_SUPPLIER_NAME,REMIT_TO_SUPPLIER_ID,REMIT_TO_SUPPLIER_SITE,
 REMIT_TO_SUPPLIER_SITE_ID,RELATIONSHIP_ID,EXTERNAL_BANK_ACCOUNT_ID,
 BANK_ACCOUNT_NAME,BANK_ACCOUNT_NUM,PAYMENT_REASON_CODE,PAYMENT_REASON,
 PAYMENT_REASON_COMMENTS,UNIQUE_REMITTANCE_IDENTIFIER,URI_CHECK_DIGIT,
 BANK_CHARGE_BEARER,BANK_CHARGE_BEARER_DSP,DELIVERY_CHANNEL_CODE,
 DELIVERY_CHANNEL,SETTLEMENT_PRIORITY,SETTLEMENT_PRIORITY_DSP,
 REMITTANCE_MESSAGE1,REMITTANCE_MESSAGE2,REMITTANCE_MESSAGE3,
 FORCE_REVALIDATION_FLAG,AWARD_ID,GLOBAL_ATTRIBUTE_CATEGORY,
 GLOBAL_ATTRIBUTE1,GLOBAL_ATTRIBUTE2,GLOBAL_ATTRIBUTE3,GLOBAL_ATTRIBUTE4,
 GLOBAL_ATTRIBUTE5,GLOBAL_ATTRIBUTE6,GLOBAL_ATTRIBUTE7,GLOBAL_ATTRIBUTE8,
 GLOBAL_ATTRIBUTE9,GLOBAL_ATTRIBUTE10,GLOBAL_ATTRIBUTE11,GLOBAL_ATTRIBUTE12,
 GLOBAL_ATTRIBUTE13,GLOBAL_ATTRIBUTE14,GLOBAL_ATTRIBUTE15,GLOBAL_ATTRIBUTE16,
 GLOBAL_ATTRIBUTE17,GLOBAL_ATTRIBUTE18,GLOBAL_ATTRIBUTE19,GLOBAL_ATTRIBUTE20,
 WFAPPROVAL_STATUS,APPROVAL_READY_FLAG,REQUESTER_ID,APPROVAL_ITERATION,
 LEGAL_ENTITY_ID,WFAPPROVAL_STATUS_DSP,NET_OF_RETAINAGE_FLAG,
 RELEASE_AMOUNT_NET_OF_TAX,PARTY_ID,PARTY_SITE_ID,PAYMENT_FUNCTION,
 PAY_PROC_TRXN_TYPE_CODE,DISC_IS_INV_LESS_TAX_FLAG,APPLICATION_ID
FROM
AP_INVOICES_V WHERE (VENDOR_NUMBER LIKE :1) order by
 CUST_REGISTRATION_NUMBER



   


Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


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