Payables and Cash Management - EBS (MOSC)

MOSC Banner

AP_INVOICES_INTERFACE performance issue - FULL SCAN happens when queried by INVOICE NUM

edited Sep 27, 2019 5:02AM in Payables and Cash Management - EBS (MOSC) 8 commentsAnswered

I coded the below login in my program to identify duplicate invoices in ap interface.

But when querying the interface table ap_invoices_interface by invoice num and vendor id, explain shows it as FULL TABLE scan. Results into performance issue.

Please help if any smart way to reduce the performance issue.

---------------------------------------------------------------------

declare

l_dup_inv_cnt number;

cur1 is select vendor_id, invoice_id, invoice_num

from ap_invoices_interface

where source = 'XML GATEWAY';

begin

for i_rec in cur1

loop

select count(1) into l_dup_inv_cnt

FROM ap.ap_invoices_interface

WHERE vendor_id = i_rec.vendor_id

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center