EeS: Performance issue with My Invoice Lines > Review Exceptions (Doc ID 1970007.1)

Last updated on APRIL 16, 2015

Applies to:

PeopleSoft Enterprise FIN eSettlements - Version 9.1 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

Performance issue when clicking on the Review Exceptions hyperlink on the My Invoices Lines page.

The following SQL is taking about 3 minutes to complete:

SELECT FILL.EM_BUSINESS_UNIT,FILL.VOUCHER_ID,FILL.VOUCHER_LINE_NUM,FILL.DISTRIB_LINE_NUM,FILL.INVOICE_ID,FILL.VENDOR_SETID,FILL.VENDOR_ID,FILL.VNDR_LOC,FILL.DESCR,TO_CHAR(FILL.INVOICE_DT,'YYYY-MM-DD'),FILL.MERCHANDISE_AMT,FILL.TXN_CURRENCY_CD,TO_CHAR(FILL.ENTERED_DT,'YYYY-MM-DD'),FILL.APPR_STATUS,FILL.MATCH_STATUS_VCHR,FILL.LINE_STATUS,FILL.DISTRIB_LN_STATUS,FILL.BUSINESS_UNIT_GL,FILL.SETID,FILL.INV_ITEM_ID,FILL.ALTACCT,FILL.ACCOUNT,FILL.DEPTID,FILL.OPERATING_UNIT,FILL.PRODUCT,FILL.FUND_CODE,FILL.CLASS_FLD,FILL.PROGRAM_CODE,FILL.BUDGET_REF,FILL.AFFILIATE,FILL.AFFILIATE_INTRA1,FILL.AFFILIATE_INTRA2,FILL.CHARTFIELD1,FILL.CHARTFIELD2,FILL.CHARTFIELD3,FILL.BUSINESS_UNIT_PC,FILL.PROJECT_ID,FILL.ACTIVITY_ID,FILL.RESOURCE_TYPE,FILL.RESOURCE_CATEGORY,FILL.RESOURCE_SUB_CAT,FILL.ANALYSIS_TYPE,FILL.DESCR254_MIXED,FILL.VCHR_LINE_APPR
FROM PS_EM_INV_LINES_VW FILL
WHERE EM_BUSINESS_UNIT IN ('45000') and VENDOR_ID IN ('0000007406','0000039085','0000079736','0000151947');

When executing a Select count (*) for the above SQL, a count of 768,580 is returned.

The FROM clause references the Record View of PS_EM_INV_LINES_VW, which consists of  the following records below, which also lists the counts for the tables:

PS_VOUCHER -- 3,616,002

PS_VENDOR -- 66,510

PS_VOUCHER_LINE -- 13,404,838

PS_DISTRIB_LINE -- 13,968,958

In addition, the Record PeopleCode for EM_INV_LINES_VW.EM_BUSINESS_UNIT.FieldFormula calls the following function, Function LineSearchExeStatement(), which is executing the following "For loop" for every row returned by the above Select statement -- For &j = &jRows To 1 Step - 1 /* NOTE: Loop goes backwards due to possible deletions */

Lastly, there seems to be a performance issue with clicking on the other hyperlinks for Review Disputes and Review Pending Approvals on the My Invoice Lines page.

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