R12: Performance Issue In AP Voucher Inquiry (Doc ID 2026927.1)

Last updated on OCTOBER 29, 2015

Applies to:

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

Symptoms

On : 12.1.3 version, Invoice Issues & MassAdd

While navigation from Trading partner field on Invoice workbench, system hangs.

Trace file shows the costliest sql is:

 

SELECT /*+ cardinality(AP_INVOICES_V.AI 100) 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_GR OUP_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 invoice_id in (select ai1.invoice_id from ap_invoices_all ai1 where ai1.doc_sequence_id > 0 and ai1.doc_sequence_value BETWEEN to_number(:1) AND to_number(:2) UNION ALL select ai2.invoice_id from ap_invoices_all ai2 where voucher_num BETWEEN :3 AND :4) order by BATCH_NAME



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