My Oracle Support Banner

R12 AP: Performance Problem: Invoice Query Takes Too Long (Doc ID 1338970.1)

Last updated on JANUARY 16, 2018

Applies to:

Oracle Payables - Version 12.1.3 and later
Information in this document applies to any platform.
***Checked for relevance on 18-Dec-2015***

Symptoms

Invoice inquiry performs poorly when querying with dates, statuses, amounts.

Following sql is taking a long time:

 


SELECT /*+ push_pred(AP_INVOICES_V.pv) */ 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 trunc(INVOICE_DATE) BETWEEN :1 AND :2 and (exists
(select 1 from ap_holds h where ap_invoices_v.invoice_id = h.invoice_id and
h.release_lookup_code is null and h.hold_lookup_code = :3 ) ) and
(PAY_GROUP_LOOKUP_CODE=:4) order by CUST_REGISTRATION_NUMBER

 

 

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
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.