My Oracle Support Banner

PAXTRAPE: Performance Issue When Searching Using Items Dates And Bill Group Fields (Doc ID 2591143.1)

Last updated on SEPTEMBER 26, 2019

Applies to:

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

Symptoms

On the Expenditure Inquiry (PAXTRAPE) form, search using the Item Dates and Bill Group fields only in the Expenditure Inquiry Screen has a severe performance issue.

Problem query is as follows:

SELECT EXPENDITURE_ITEM_ID,PROJECT_NUMBER,TASK_NUMBER,EXPENDITURE_TYPE,
  INVENTORY_ITEM,WIP_RESOURCE,EXPENDITURE_ITEM_DATE,QUANTITY,
  UNIT_OF_MEASURE_M,PROJECT_CURRENCY_CODE,BURDENED_COST,PROJECT_BURDENED_COST,
  ACCRUED_REVENUE,BILL_AMOUNT,EXPENDITURE_COMMENT,
  ::
FROM
 (select * from PA_EXPEND_ITEMS_ADJUST2_V where project_id in (select /*+
  no_unnest no_push_subq */ project_id from pa_projects_v_paxtrape))
  pa_expend_items_adjust2_v

WHERE (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 =
  pa_expend_items_adjust2_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))) and (EXPENDITURE_ITEM_DATE  BETWEEN
  to_date('2018/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') AND
  to_date('2019/06/01 23:59:59', 'YYYY/MM/DD HH24:MI:SS')) and
(BILLABLE_FLAG=
  :1) and (NET_ZERO_ADJUSTMENT_FLAG <> :2) and (ORG_ID=:3) and
(BILL_GROUP=:4)
   order by EXPENDITURE_ITEM_ID, expenditure_item_date, task_id,
  expenditure_id, NVL(source_expenditure_item_id, NVL
  (adjusted_expenditure_item_id, NVL(transferred_from_exp_item_id,
  expenditure_item_id))), expenditure_item_id

Changes

 

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.