Populate UWQ Summary Updates in 15 Minutes but Completes in 1 Hour (Doc ID 1137113.1)

Last updated on JANUARY 11, 2021

Applies to:

Oracle Advanced Collections - Version 12.1.2 and later
Information in this document applies to any platform.

Symptoms

The Concurrent Program 'IEX: Populate UWQ Summary Table' performs the refresh of the summary table in 15 minutes for less than 70k records, but request itself completes in 1 hour.

SQL
-------

SELECT STRAT.JTF_OBJECT_ID, WKITEM.WORK_ITEM_ID, WKITEM.SCHEDULE_START
SCHEDULE_START, WKITEM.SCHEDULE_END SCHEDULE_END,
STRY_TEMP_WKITEM.CATEGORY_TYPE CATEGORY, STRY_TEMP_WKITEM.WORK_TYPE,
STRY_TEMP_WKITEM.PRIORITY_TYPE, WKITEM.RESOURCE_ID, WKITEM.STRATEGY_ID,
STRAT.STRATEGY_TEMPLATE_ID, WKITEM.WORK_ITEM_TEMPLATE_ID,
WKITEM.STATUS_CODE, STRAT.STATUS_CODE, WKITEM.EXECUTE_START START_TIME,
WKITEM.EXECUTE_END END_TIME, WKITEM.WORK_ITEM_ORDER WKITEM_ORDER,
WKITEM.ESCALATED_YN
FROM
IEX_STRATEGIES STRAT, IEX_STRATEGY_WORK_ITEMS WKITEM,
IEX_STRY_TEMP_WORK_ITEMS_B STRY_TEMP_WKITEM, IEX_DLN_UWQ_SUMMARY SUM WHERE
STRAT.JTF_OBJECT_TYPE = 'IEX_BILLTO' AND STRAT.STATUS_CODE IN('OPEN',
'ONHOLD') AND WKITEM.STRATEGY_ID = STRAT.STRATEGY_ID
AND WKITEM.STATUS_CODE IN('OPEN', 'ONHOLD') AND
WKITEM.WORK_ITEM_TEMPLATE_ID = STRY_TEMP_WKITEM.WORK_ITEM_TEMP_ID AND
STRAT.JTF_OBJECT_ID = SUM.SITE_USE_ID
AND sum.LAST_UPDATE_DATE >= trunc(sysdate)
AND SUM.ORG_ID=NVL(:B2 ,SUM.ORG_ID) AND SUM.BUSINESS_LEVEL='BILL_TO';