R12 PARGDR - Generate Draft Revenue For Range Of Projects Has Slow Performance (Doc ID 1264194.1)

Last updated on AUGUST 17, 2022

Applies to:

Symptoms

The PARGDR PRC: Generate Draft Revenue for a Range of Projects has slow performance. The process was run for 47 projects and took more than 2-1/2 hours to complete. The most expensive queries are:

SELECT /*+ LEADING(p)*/ p.project_id, p.segment1, p.distribution_rule,
p.project_type,
pc.class_code, p.carrying_out_organization_id,
p.public_sector_flag, p.project_level_funding_flag,
p.project_currency_code,
p.project_bil_rate_date_code,
p.project_bil_rate_type,
to_char(p.project_bil_rate_date,'YYYY/MM/DD'),
p.project_bil_exchange_rate,
p.projfunc_currency_code,
p.projfunc_bil_rate_date_code,
p.projfunc_bil_rate_type,
to_char(p.projfunc_bil_rate_date,'YYYY/MM/DD'),
p.projfunc_bil_exchange_rate,
p.revproc_currency_code,
p.funding_rate_date_code,
p.funding_rate_type,
to_char(p.funding_rate_date,'YYYY/MM/DD'),
p.funding_exchange_rate,
p.multi_currency_billing_flag,
p.assign_precedes_task,
p.enable_top_task_customer_flag,
p.enable_top_task_inv_mth_flag,
nvl(p.inv_by_bill_trans_curr_flag, 'N'),
nvl(p.revtrans_currency_type, 'PROJ_FUNC_CURR_ONLY'),
nvl(p.date_eff_funds_consumption,'N')
FROM pa_project_classes pc, pa_project_types t, pa_projects p
WHERE pa_project_utils.check_prj_stus_action_allowed(p.project_status_code,'GENERATE_REV') = 'Y'
AND p.segment1 BETWEEN :start_project_number
AND :end_project_number
AND t.project_type = p.project_type
AND t.direct_flag = 'Y'
AND pc.project_id(+) = p.project_id
AND pc.class_category(+) = :aa_category
AND EXISTS (SELECT NULL
FROM pa_summary_project_fundings pf
WHERE pf.project_id = p.project_id
AND nvl(pf.revproc_baselined_amount, 0) != 0)
AND ( NOT EXISTS (SELECT NULL
FROM pa_draft_revenues r
WHERE r.project_id = p.project_id
AND r.released_date||'' is null
AND r.generation_error_flag||'' =
decode(:mass_gen, 1, 'Y', r.generation_error_flag)
AND r.request_id != :current_request_id
AND r.draft_revenue_num_credited IS NULL
AND nvl(r.adjusting_revenue_flag,'N')=decode(:adj_rev_mode,1,'Y','N')
)
AND ( EXISTS (SELECT NULL
FROM pa_expenditure_items_all i
WHERE i.project_id = p.project_id
AND i.cost_distributed_flag ||'' = 'Y'
AND i.revenue_distributed_flag not IN
('Y', 'A')
AND ( i.revenue_distributed_flag != 'N'
OR
( i.revenue_distributed_flag = 'N'
AND nvl(i.accrued_revenue,0) = 0))
AND ( i.adjusted_expenditure_item_id is null
OR
( i.adjusted_expenditure_item_id is not null
AND EXISTS (SELECT 'x'
FROM pa_expenditure_items_all adj1
WHERE adj1.expenditure_item_id = i.adjusted_expenditure_item_id
AND nvl(adj1.accrued_revenue,0) = 0
AND adj1.revenue_distributed_flag || '' = 'N' )))
AND i.billable_flag ||'' = 'Y'
AND i.expenditure_item_date
<= NVL(TO_DATE(:acc_thru_dt, 'YYYY/MM/DD'),sysdate)
AND EXISTS ( select null from pa_tasks top, pa_tasks t
where top.task_id = t.top_task_id
and top.ready_to_distribute_flag ||''= 'Y'
AND t.task_id = i.task_id)
AND :adj_rev_mode=0
UNION ALL
SELECT NULL
FROM pa_event_types vt,
pa_events v, pa_tasks t
WHERE t.project_id = p.project_id
AND t.ready_to_distribute_flag ||'' = 'Y'
AND v.project_id = t.project_id
AND nvl(v.task_id, t.task_id) = t.task_id
AND v.revenue_distributed_flag = 'N'
AND nvl(v.adjusting_revenue_flag,'N')= decode(:adj_rev_mode,1,'Y','N')
AND TRUNC(v.completion_date)
<= TRUNC(NVL(TO_DATE(:acc_thru_dt, 'YYYY/MM/DD'),sysdate))
AND (DECODE(NVL(v.bill_trans_rev_amount, 0), 0 ,
DECODE(NVL(v.zero_revenue_amount_flag, 'N'), 'Y', 1, 0),1) = 1)
AND NVL(revenue_hold_flag, 'N') = 'N'
AND vt.event_type = v.event_type
AND vt.event_type_classification ||'' IN
('WRITE ON', 'WRITE OFF', 'MANUAL', 'AUTOMATIC','REALIZED_GAINS',
'REALIZED_LOSSES')
UNION ALL
SELECT /*+ USE_CONCAT*/
NULL
FROM PA_Billing_Extensions be, PA_Billing_Assignments bea
WHERE bea.active_flag = 'Y'
AND bea.billing_extension_id = be.billing_extension_id
AND (bea.project_id = p.project_id
OR bea.project_type = p.project_type
OR bea.distribution_rule = p.distribution_rule)
AND be.calling_process in ('Revenue','Both')
AND (nvl(be.call_after_reg_flag, 'N') = 'Y'
OR nvl(be.call_post_reg_flag, 'N') = 'Y')
AND :adj_rev_mode =0
AND be.trx_independent_flag = 'Y')))

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.02 0.03 0 0 0 0
Fetch 16 49.35 11166.30 1305121 2009830 0 29
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18 49.40 11166.35 1305121 2009830 0 29

and

SELECT 1
FROM
DUAL WHERE EXISTS( SELECT /*+ INDEX(Rtask PA_TASKS_N8) INDEX(ei
PA_EXPENDITURE_ITEMS_N9)*/ 1 FROM PA_EXPENDITURE_ITEMS_ALL EI, PA_TASKS
RTASK WHERE RTASK.PROJECT_ID = :B5 AND EI.TASK_ID = RTASK.TASK_ID AND
RTASK.CHARGEABLE_FLAG = 'Y' AND ((:B4 = 'Y' AND
(RTASK.REVENUE_ACCRUAL_METHOD = 'WORK' OR RTASK.INVOICE_METHOD = 'WORK'))
OR (:B4 = 'N' AND (:B3 = 'WORK' OR :B2 = 'WORK'))) AND
EI.COST_DISTRIBUTED_FLAG = 'Y' AND EI.REVENUE_DISTRIBUTED_FLAG = 'N' AND
EI.EXPENDITURE_ITEM_DATE <= :B1 AND EXISTS( SELECT /*+ INDEX(crdl
PA_CUST_REV_DIST_LINES_U1)*/ NULL FROM PA_CUST_REV_DIST_LINES CRDL,
PA_DRAFT_REVENUES DRX1 WHERE EI.PROJECT_ID = CRDL.PROJECT_ID AND
((EI.EXPENDITURE_ITEM_ID = CRDL.EXPENDITURE_ITEM_ID) OR
(EI.ADJUSTED_EXPENDITURE_ITEM_ID IS NOT NULL AND
EI.ADJUSTED_EXPENDITURE_ITEM_ID = CRDL.EXPENDITURE_ITEM_ID)) AND
NVL(CRDL.REVERSED_FLAG,'N') = 'N' AND NVL(CRDL.LINE_NUM_REVERSED,0) = 0 AND
DRX1.PROJECT_ID = CRDL.PROJECT_ID AND DRX1.DRAFT_REVENUE_NUM =
CRDL.DRAFT_REVENUE_NUM AND DECODE(DRX1.GENERATION_ERROR_FLAG,:B6 ,
DECODE(DRX1.RELEASED_DATE,NULL,1,0),0) = DECODE(DRX1.RELEASED_DATE,NULL,1,0)
UNION ALL SELECT 1 FROM PA_EXPENDITURE_ITEMS EI2 WHERE EI2.PROJECT_ID =
EI.PROJECT_ID AND EI2.EXPENDITURE_ITEM_ID = EI.ADJUSTED_EXPENDITURE_ITEM_ID
AND EI2.REVENUE_DISTRIBUTED_FLAG = 'Y' AND EI2.RAW_REVENUE = 0 ) )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 18957 2.84 808.24 29933 221217 0 0
Fetch 18957 0.03 0.04 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 37915 2.88 808.28 29933 221217 0 0

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.