My Oracle Support Banner

Poor Performance Of The Seeded View, PA_EXPEND_ITEMS_ADJUST2_V, After The Upgrade From 12.1.3 to 12.2.9 (Doc ID 2824500.1)

Last updated on DECEMBER 08, 2021

Applies to:

Oracle Project Costing - Version 12.2.9 and later
Information in this document applies to any platform.

Symptoms

After the EBS upgrade from R12.1.3 to R12.2.9, custom report is run, but is running slow.  The cause is the seeded view, PA_EXPEND_ITEMS_ADJUST2_V, with version pav0409.odf 120.9.12020000.8.

The tkprof trace shows the problem performing statement.

SELECT v.EXPENDITURE_ITEM_ID,
v.PROJECT_NUMBER,
v.TASK_NUMBER,
v.EXPENDITURE_TYPE,
v.INVENTORY_ITEM,
v.WIP_RESOURCE,
v.EXPENDITURE_ITEM_DATE,
v.QUANTITY,
v.UNIT_OF_MEASURE_M,
v.PROJECT_CURRENCY_CODE,
v.BURDENED_COST,
v.PROJECT_BURDENED_COST,
v.ACCRUED_REVENUE,
v.BILL_AMOUNT,
v.EXPENDITURE_COMMENT,
v.EXPENDITURE_ORGANIZATION_NAME,
v.NON_LABOR_RESOURCE,
v.WORK_TYPE_NAME,
v.ASSIGNMENT_NAME,
v.NLR_ORGANIZATION_NAME,
v.DENOM_CURRENCY_CODE,
v.DENOM_RAW_COST,
v.DENOM_BURDENED_COST,
v.ACCT_CURRENCY_CODE,
v.ACCT_RATE_TYPE,
v.USER_ACCT_RATE_TYPE,
v.ACCT_RATE_DATE,
v.ACCT_EXCHANGE_RATE,
v.ACCT_RAW_COST,
v.ACCT_BURDENED_COST,
v.PROJECT_RATE_TYPE,
v.USER_PROJFUNC_COST_RATE_TYPE,
v.PROJFUNC_COST_RATE_TYPE,
v.USER_PROJECT_RATE_TYPE,
v.PROJECT_RATE_DATE,
v.PROJFUNC_COST_RATE_DATE,
v.PROJECT_EXCHANGE_RATE,
.
.
.
AND v.EXPENDITURE_ITEM_DATE BETWEEN to_date(:P_EXP_ITEM_DATE_FROM, 'YYYY/MM/DD HH24:MI:SS') AND to_date(:P_EXP_ITEM_DATE_TO, 'YYYY/MM/DD HH24:MI:SS')
AND V.PROJECT_ID = PORTFOLIO_OWNER.PROJECT_ID
AND V.EXPENDITURE_ITEM_DATE BETWEEN PORTFOLIO_OWNER.START_DATE_ACTIVE AND NVL(PORTFOLIO_OWNER.END_DATE_ACTIVE,V.EXPENDITURE_ITEM_DATE)
AND PORTFOLIO_OWNER.PORTFOLIO_OWNER_ID = nvl(:P_PORT_OWNER_ID,PORTFOLIO_OWNER.PORTFOLIO_OWNER_ID)
and v.project_id = pt.PROJECT_ID
and v.TASK_ID = pt.TASK_ID
and pt.REVENUE_ACCRUAL_METHOD = nvl(:p_revenue_accrual_method,pt.REVENUE_ACCRUAL_METHOD)
and pt.INVOICE_METHOD = nvl(:p_invoice_method,pt.INVOICE_METHOD)
AND v.net_zero_adjustment_flag=nvl(:P_NET_ZERO_ADJUSTMENT_FLAG,v.net_zero_adjustment_flag)
and v.project_id = clas.project_id(+)
and v.project_id = rep.project_id(+)
AND nvl(clas.class_code,'X') = nvl(:P_REGION,nvl(clas.class_code,'X'))
AND nvl(rep.class_code,'X') = nvl(:P_REPORTING,nvl(rep.class_code,'X'))
and v.task_id = tbill.task_id(+)
and v.project_id = pv.project_id (+)
--Adding this bit to pull cross charge exp items entered through OTL of providing Org
--
--
AND EXISTS
(SELECT 'X'
FROM PA_PROJECTS_ALL
WHERE PROJECT_ID = V.PROJECT_ID
AND ORG_ID = :P_ORG_ID
AND PROJECT_TYPE <> 'Internal Noncharge')
--
--
--Adding this bit to pull cross charge exp items entered through OTL of providing Org
AND 1=1 AND (EXPENDITURE_ITEM_ID =expenditure_item_id
AND (event_num IS NULL AND NOT EXISTS
(SELECT 1
FROM pa_cust_rev_dist_lines_all r
WHERE r.expenditure_item_id = v.expenditure_item_id
AND r.draft_invoice_num IS NOT NULL
HAVING SUM(NVL(r.bill_trans_bill_amount, 0)) <> 0
GROUP BY r.expenditure_item_id
)))
ORDER BY PORTFOLIO_OWNER,PROJECT_NUMBER, EXPENDITURE_ITEM_ID, expenditure_item_date, task_id,expenditure_id

 

Changes

Upgrade from 12.1.3 to 12.2.9.

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