<SQL_EXPLAIN_PLAN>

<DATA_DS>
<![CDATA[
Datamodel SQL Explain Plan Report
================================================================
Driver Details:JDBC Driver:Oracle JDBC
driver:weblogic.jdbc.wrapper.PoolConnection_oracle_jdbc_driver_T4CConnection:11.2.0
.3.0
DBName:Oracle
DBVersion:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQLQuery:EXPLAIN PLAN SET STATEMENT_ID = 'dm_plan_Q_220419_082217' FOR
with report as (
SELECT /*+ materialize */
hou.name Business_Unit
, ppa.segment1 project_number
, ppat.name project_name
, ppt.project_type
, hao2.name project_owning_organization
, ppa.project_status_code
, TO_CHAR(ppa.start_date, 'MM/DD/YYYY') start_date
, (SELECT LISTAGG(per2.full_name,' / ') WITHIN GROUP(ORDER BY per2.full_name)
FROM per_person_names_f per2,pjf_project_parties ppp2,pjf_proj_role_types_tl pprtl2
WHERE 1=1
AND pei.project_id = ppp2.project_id
AND ppp2.project_role_id =
pprtl2.project_role_id
AND ppp2.resource_source_id =
per2.person_id
AND pprtl2.language = USERENV('LANG')
AND pprtl2.project_role_name = 'Project
Manager'
AND per2.name_type = 'GLOBAL'
AND ppp2.end_date_active IS NULL
) Project_Manager
, ppeb.element_number task_number
, (
SELECT
LISTAGG(p1.name,' | ') WITHIN GROUP(ORDER BY t1.denorm_wbs_level)
FROM
pjf_proj_elements_tl p1
, (
SELECT
proj_element_id
, denorm_wbs_level
FROM
pjf_proj_elements_b
WHERE
denorm_top_element_id = ppeb.denorm_top_element_id
and
(
denorm_wbs_level = 1
OR proj_element_id = ppeb.proj_element_id
OR proj_element_id = ppeb.denorm_parent_element_id
)
)
t1
WHERE
p1.proj_element_id = t1.proj_element_id
and p1.language = USERENV('LANG')
 ) task_name

, pei.expenditure_item_id transaction_number
, pei.capitalizable_flag
, pet.expenditure_type_name
, hao1.name expenditure_organization
, pei.user_def_attribute1 po_number
, (SELECT aia.invoice_num FROM ap_invoices_all aia
WHERE aia.invoice_id = pei.original_header_id)
invoice_number
,(select psv1.segment1 from ap_invoices_all aia,poz_suppliers_v psv1 where
aia.vendor_id=psv1.vendor_id and aia.invoice_id = pei.original_header_id)
supplier_number
, (select psv1.vendor_name from ap_invoices_all aia,poz_suppliers_v psv1 where
aia.vendor_id=psv1.vendor_id and aia.invoice_id = pei.original_header_id)
supplier_name
, TO_CHAR(pei.expenditure_item_date, 'MM/DD/YYYY') expenditure_item_date
, pcd.prvdr_gl_period_name Accounting_Period

, NVL(NVL(NVL(

(SELECT distinct (gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3

|| '.' || gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6 || '.' ||
gcc.segment7 || '.' || gcc.segment8)
FROM AP_PAYMENT_HIST_DISTS aphd,XLA_DISTRIBUTION_LINKS
xdl,xla_ae_lines xal, gl_code_combinations gcc
where aphd.invoice_payment_id = pei.original_header_id
AND aphd.PAY_DIST_LOOKUP_CODE='DISCOUNT'
AND xdl.SOURCE_DISTRIBUTION_ID_NUM_1 = pei.original_dist_id
AND xdl.event_id = aphd.ACCOUNTING_EVENT_ID
and xal.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
and xal.code_combination_id = gcc.code_combination_id
and xal.accounting_class_code = 'DISCOUNT'),

(SELECT distinct (gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3

|| '.' || gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6 || '.' ||
gcc.segment7 || '.' || gcc.segment8)
FROM XLA_DISTRIBUTION_LINKS XDL
,XLA_AE_HEADERS XAH
,XLA_AE_LINES XAL
,GL_CODE_COMBINATIONS GCC
WHERE 1=1
AND pei.ORIGINAL_DIST_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND pei.ORIGINAL_HEADER_ID = XDL.applied_to_source_id_num_1
AND XDL.EVENT_ID = XAH.EVENT_ID
AND XDL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND xal.accounting_class_code IN
('RAW_COST','FREIGHT','DISCOUNT','SELF_ASSESSED_TAX','ITEM EXPENSE','MISCELLANEOUS
EXPENSE','NRTAX')
AND XAL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID)),

( select distinct (gcc.segment1|| '.' || gcc.segment2 || '.' || gcc.segment3 ||

'.' || gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6 || '.' ||
gcc.segment7 || '.' || gcc.segment8)
from xla_ae_headers xah, xla_ae_lines xal , gl_code_combinations
gcc
where 1=1 and xah.event_id = pcd.acct_event_id
AND xal.accounting_class_code IN
('RAW_COST','FREIGHT','DISCOUNT','SELF_ASSESSED_TAX','ITEM EXPENSE','MISCELLANEOUS
EXPENSE','NRTAX')
AND xah.ae_header_id =xal.ae_header_id
AND xal.code_combination_id
=gcc.code_combination_id
AND XAH.EVENT_TYPE_CODE LIKE '%DIST%'
and rownum =1))
,(select distinct (gcc.segment1 || '.' || gcc.segment2 || '.' ||
gcc.segment3 || '.' || gcc.segment4 || '.' || gcc.segment5 || '.' ||
gcc.segment6 || '.' || gcc.segment7 || '.' || gcc.segment8)
from gl_code_combinations gcc

where 1 = 1
AND pcd.RAW_COST_DR_CCID = gcc.code_combination_id ))
raw_cost_account_string
, pei.PROJFUNC_CURRENCY_CODE currency
, pei.project_raw_cost Current_Asset_Cost
, pei.orig_transaction_reference
FROM
pjc_exp_items_all pei
, pjc_cost_dist_lines_all pcd
, pjc_prj_asset_lns_all ppal
, pjc_prj_asset_ln_dets ppad
, pjf_projects_all_b ppa
, hr_operating_units hou
, pjf_projects_all_tl ppat
, pjf_project_types_tl ppt
, hr_all_organization_units hao2
, pjf_proj_role_types_tl ppr
, pjf_exp_types_tl pet
, hr_all_organization_units hao1
, pjf_proj_elements_b ppeb

WHERE
1 = 1
AND ppal.project_asset_line_detail_id(+) = ppad.project_asset_line_detail_id
AND ppad.expenditure_item_id(+) = pei.expenditure_item_id
AND pei.org_id = hou.organization_id
AND ppat.project_id = pei.project_id
AND ppat.language = userenv('lang')
AND ppt.project_type_id = ppa.project_type_id
AND ppt.language = userenv('lang')
AND ppa.carrying_out_organization_id = hao2.organization_id
AND ppr.project_role_name = 'Project Manager'
AND pei.expenditure_type_id = pet.expenditure_type_id
AND pet.language = userenv('lang')
AND pei.expenditure_organization_id = hao1.organization_id
AND pcd.prvdr_gl_date <= TO_DATE('03-31-2022','MM-DD-YYYY')
AND pcd.capitalizable_flag = 'Y'
AND pcd.expenditure_item_id = pei.expenditure_item_id
AND (NVL(ppal.transfer_status_code,'P') = 'P'
OR (ppal.transfer_status_code = 'T' AND
(SELECT period_counter FROM fa_deprn_periods WHERE book_type_code =
'USA CORPORATE' AND period_name = ppal.fa_period_name)
> (SELECT period_counter FROM fa_deprn_periods WHERE
book_type_code = 'USA CORPORATE' AND period_name = 'MAR-2022')))
 AND hou.name = 'WM US Business Unit'
AND ppt.project_type NOT IN
(
SELECT
MEANING
FROM
FND_LOOKUP_VALUES
WHERE
LOOKUP_TYPE ='PPM_PROJECT_TYPES_NO_ASSET'
AND LANGUAGE = 'US'
)
AND pei.task_id = ppeb.proj_element_id

ORDER BY
21
, 2
, 9 )

select /* QUERY_SRC('datamodel: _datamodel.xdm,dataset:Q') */ Business_Unit,

project_number,
project_name,
project_type,
project_owning_organization,
project_status_code,
start_date,
Project_Manager,
task_number,
task_name,
transaction_number,
capitalizable_flag,
expenditure_type_name,
expenditure_organization,
po_number,
invoice_number,
supplier_number,
supplier_name,
expenditure_item_date,
Accounting_Period,
raw_cost_account_string,
currency,
Current_Asset_Cost,
orig_transaction_reference,
regexp_substr(raw_cost_account_string,'[^.]+',1) COMPANY,
regexp_substr(raw_cost_account_string,'[^.]+',1,2) FACILITY,
regexp_substr(raw_cost_account_string,'[^.]+',1,4) ACCOUNT

from report

ORDER BY 21,2, 9
SQL Query Timeout: 600
Number of SQL Executions: 1
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
------------------
Plan hash value: 4032128294

-----------------------------------------------------------------------------------
-----------------------------------------------------
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
-----------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 9196 | 731K (1)| 00:00:29 |
| 1 | TEMP TABLE TRANSFORMATION |
| | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) |
SYS_TEMP_0FD9D6AB6_2A3AEFF | | | | |
| 3 | SORT GROUP BY |
| 1 | 57 | | |
| 4 | NESTED LOOPS |
| 1 | 57 | 5 (0)| 00:00:01 |
| 5 | NESTED LOOPS |
| 1 | 57 | 5 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID BATCHED | pjf_proj_elements_b
| 1 | 23 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN |
PJF_PROJ_ELEMENTS_N9 | 3 | | 1 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN |
PJF_PROJ_ELEMENTS_TL_N1 | 1 | | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID |
pjf_proj_elements_tl | 1 | 34 | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | ap_invoices_all
| 1 | 18 | 3 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | AP_INVOICES_ALL_U1
| 1 | | 2 (0)| 00:00:01 |
| 12 | NESTED LOOPS OUTER |
| 1 | 76 | 6 (0)| 00:00:01 |
| 13 | NESTED LOOPS |
| 1 | 53 | 5 (0)| 00:00:01 |
| 14 | NESTED LOOPS |
| 1 | 43 | 4 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | ap_invoices_all
| 1 | 16 | 3 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | AP_INVOICES_ALL_U1
| 1 | | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | poz_suppliers
| 1 | 27 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | POZ_SUPPLIERS_U1
| 1 | | 0 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | HZ_PARTIES_PK
| 1 | 10 | 1 (0)| 00:00:01 |
|* 20 | TABLE ACCESS BY INDEX ROWID BATCHED |
hz_addtnl_party_names | 1 | 23 | 1 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN |
HZ_ADDTNL_PARTY_NAMES_N2 | 1 | | 0 (0)| 00:00:01 |
| 22 | NESTED LOOPS OUTER |
| 1 | 96 | 7 (0)| 00:00:01 |
| 23 | NESTED LOOPS |
| 1 | 73 | 6 (0)| 00:00:01 |
| 24 | NESTED LOOPS |
| 1 | 36 | 4 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID | ap_invoices_all
| 1 | 16 | 3 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | AP_INVOICES_ALL_U1
| 1 | | 2 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | poz_suppliers
| 1 | 20 | 1 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | POZ_SUPPLIERS_U1
| 1 | | 0 (0)| 00:00:01 |
| 29 | TABLE ACCESS BY INDEX ROWID | hz_parties
| 1 | 37 | 2 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | HZ_PARTIES_PK
| 1 | | 1 (0)| 00:00:01 |
|* 31 | TABLE ACCESS BY INDEX ROWID BATCHED |
hz_addtnl_party_names | 1 | 23 | 1 (0)| 00:00:01 |
|* 32 | INDEX RANGE SCAN |
HZ_ADDTNL_PARTY_NAMES_N2 | 1 | | 0 (0)| 00:00:01 |
| 33 | SORT UNIQUE |
| 1 | 125 | 292K (1)| 00:00:12 |
| 34 | NESTED LOOPS SEMI |
| 1 | 125 | 292K (1)| 00:00:12 |
| 35 | NESTED LOOPS |
| 34 | 3502 | 292K (1)| 00:00:12 |
| 36 | MERGE JOIN CARTESIAN |
| 23512 | 1722K| 13236 (1)| 00:00:01 |
|* 37 | TABLE ACCESS BY INDEX ROWID |
ap_payment_hist_dists | 1 | 17 | 4 (0)| 00:00:01 |
|* 38 | INDEX RANGE SCAN |
AP_PAYMENT_HIST_DISTS_N5 | 4 | | 3 (0)| 00:00:01 |
| 39 | BUFFER SORT |
| 1238K| 68M| 13232 (1)| 00:00:01 |
| 40 | TABLE ACCESS STORAGE FULL |
gl_code_combinations | 1238K| 68M| 13232 (1)| 00:00:01 |
|* 41 | TABLE ACCESS BY INDEX ROWID BATCHED | xla_ae_lines
| 1 | 28 | 46 (0)| 00:00:01 |
|* 42 | INDEX RANGE SCAN | XLA_AE_LINES_N5
| 97 | | 3 (0)| 00:00:01 |
|* 43 | TABLE ACCESS BY INDEX ROWID BATCHED |
xla_distribution_links | 1 | 22 | 4 (0)| 00:00:01 |
|* 44 | INDEX RANGE SCAN |
XLA_DISTRIBUTION_LINKS_N3 | 1 | | 3 (0)| 00:00:01 |
| 45 | SORT UNIQUE |
| 1 | 123 | 437K (1)| 00:00:18 |
| 46 | NESTED LOOPS |
| 1 | 123 | 437K (1)| 00:00:18 |
| 47 | NESTED LOOPS |
| 1 | 123 | 437K (1)| 00:00:18 |
| 48 | NESTED LOOPS |
| 1 | 65 | 437K (1)| 00:00:18 |
| 49 | NESTED LOOPS |
| 1 | 37 | 437K (1)| 00:00:18 |
|* 50 | TABLE ACCESS STORAGE FULL |
xla_distribution_links | 1 | 25 | 437K (1)| 00:00:18 |
|* 51 | TABLE ACCESS BY INDEX ROWID BATCHED | xla_ae_headers
| 1 | 12 | 3 (0)| 00:00:01 |
|* 52 | INDEX RANGE SCAN | XLA_AE_HEADERS_U1
| 1 | | 2 (0)| 00:00:01 |
|* 53 | TABLE ACCESS BY INDEX ROWID BATCHED | xla_ae_lines
| 1 | 28 | 3 (0)| 00:00:01 |
|* 54 | INDEX RANGE SCAN | XLA_AE_LINES_U1
| 1 | | 2 (0)| 00:00:01 |
|* 55 | INDEX UNIQUE SCAN |
GL_CODE_COMBINATIONS_U1 | 1 | | 1 (0)| 00:00:01 |
| 56 | TABLE ACCESS BY INDEX ROWID |
gl_code_combinations | 1 | 58 | 2 (0)| 00:00:01 |
| 57 | SORT UNIQUE |
| 1 | 107 | 10 (10)| 00:00:01 |
|* 58 | COUNT STOPKEY |
| | | | |
| 59 | NESTED LOOPS |
| 1 | 107 | 9 (0)| 00:00:01 |
| 60 | NESTED LOOPS |
| 1 | 107 | 9 (0)| 00:00:01 |
| 61 | NESTED LOOPS |
| 1 | 49 | 7 (0)| 00:00:01 |
|* 62 | TABLE ACCESS BY INDEX ROWID BATCHED | xla_ae_headers
| 1 | 25 | 4 (0)| 00:00:01 |
|* 63 | INDEX RANGE SCAN | XLA_AE_HEADERS_N2
| 1 | | 3 (0)| 00:00:01 |
|* 64 | TABLE ACCESS BY INDEX ROWID BATCHED | xla_ae_lines
| 1 | 24 | 3 (0)| 00:00:01 |
|* 65 | INDEX RANGE SCAN | XLA_AE_LINES_U1
| 4 | | 2 (0)| 00:00:01 |
|* 66 | INDEX UNIQUE SCAN |
GL_CODE_COMBINATIONS_U1 | 1 | | 1 (0)| 00:00:01 |
| 67 | TABLE ACCESS BY INDEX ROWID |
gl_code_combinations | 1 | 58 | 2 (0)| 00:00:01 |
| 68 | TABLE ACCESS BY INDEX ROWID |
gl_code_combinations | 1 | 58 | 3 (0)| 00:00:01 |
|* 69 | INDEX UNIQUE SCAN |
GL_CODE_COMBINATIONS_U1 | 1 | | 2 (0)| 00:00:01 |
| 70 | SORT ORDER BY |
| 1 | 2753 | 731K (1)| 00:00:29 |
|* 71 | FILTER |
| | | | |
| 72 | NESTED LOOPS ANTI |
| 1 | 2753 | 1273 (1)| 00:00:01 |
| 73 | NESTED LOOPS |
| 1 | 2751 | 1268 (1)| 00:00:01 |
| 74 | NESTED LOOPS |
| 1 | 2687 | 1267 (1)| 00:00:01 |
|* 75 | HASH JOIN |
| 1 | 2658 | 1265 (1)| 00:00:01 |
| 76 | MERGE JOIN CARTESIAN |
| 1 | 2613 | 1199 (1)| 00:00:01 |
| 77 | NESTED LOOPS OUTER |
| 1 | 2573 | 1196 (1)| 00:00:01 |
| 78 | NESTED LOOPS |
| 1 | 569 | 1190 (1)| 00:00:01 |
| 79 | NESTED LOOPS OUTER |
| 1 | 542 | 1188 (1)| 00:00:01 |
| 80 | NESTED LOOPS OUTER |
| 1 | 531 | 1186 (1)| 00:00:01 |
| 81 | NESTED LOOPS |
| 1 | 521 | 1184 (1)| 00:00:01 |
| 82 | NESTED LOOPS |
| 1 | 480 | 1183 (1)| 00:00:01 |
| 83 | NESTED LOOPS |
| 1 | 447 | 1182 (1)| 00:00:01 |
| 84 | NESTED LOOPS |
| 1 | 412 | 1181 (1)| 00:00:01 |
| 85 | NESTED LOOPS |
| 1 | 348 | 1180 (1)| 00:00:01 |
|* 86 | HASH JOIN |
| 1 | 319 | 1178 (1)| 00:00:01 |
| 87 | JOIN FILTER CREATE | :BF0000
| 1 | 220 | 12 (0)| 00:00:01 |
| 88 | NESTED LOOPS |
| 1 | 220 | 12 (0)| 00:00:01 |
| 89 | NESTED LOOPS |
| 1 | 220 | 12 (0)| 00:00:01 |
| 90 | NESTED LOOPS |
| 1 | 174 | 10 (0)| 00:00:01 |
| 91 | NESTED LOOPS |
| 1 | 117 | 8 (0)| 00:00:01 |
| 92 | MERGE JOIN CARTESIAN |
| 1 | 88 | 7 (0)| 00:00:01 |
|* 93 | TABLE ACCESS STORAGE FULL |
pjf_proj_role_types_tl | 1 | 24 | 4 (0)| 00:00:01 |
| 94 | BUFFER SORT |
| 1 | 64 | 3 (0)| 00:00:01 |
|* 95 | TABLE ACCESS BY INDEX ROWID|
hr_organization_units_f_tl | 1 | 64 | 3 (0)| 00:00:01 |
|* 96 | INDEX RANGE SCAN |
HR_ALL_ORG_UNITS_F_TL_N1 | 1 | | 1 (0)| 00:00:01 |
|* 97 | TABLE ACCESS BY INDEX ROWID |
hr_all_organization_units_f | 1 | 29 | 1 (0)| 00:00:01 |
|* 98 | INDEX UNIQUE SCAN |
HR_ALL_ORGANIZATION_UNITS_PK | 1 | | 0 (0)| 00:00:01 |
|* 99 | TABLE ACCESS BY INDEX ROWID |
hr_organization_information_f | 1 | 57 | 2 (0)| 00:00:01 |
|*100 | INDEX RANGE SCAN |
HR_ORGANIZATION_INFORMATION_N2 | 1 | | 1 (0)| 00:00:01 |
|*101 | INDEX RANGE SCAN |
HR_ORG_UNIT_CLASS_U2 | 1 | | 1 (0)| 00:00:01 |
|*102 | TABLE ACCESS BY INDEX ROWID |
hr_org_unit_classifications_f | 1 | 46 | 2 (0)| 00:00:01 |
| 103 | JOIN FILTER USE | :BF0000
| 38736 | 3744K| 1166 (1)| 00:00:01 |
|*104 | TABLE ACCESS STORAGE FULL | pjc_exp_items_all
| 38736 | 3744K| 1166 (1)| 00:00:01 |
|*105 | TABLE ACCESS BY INDEX ROWID |
hr_all_organization_units_f | 1 | 29 | 2 (0)| 00:00:01 |
|*106 | INDEX RANGE SCAN |
HR_ALL_ORGANIZATION_UNITS_PK | 1 | | 1 (0)| 00:00:01 |
|*107 | TABLE ACCESS BY INDEX ROWID |
hr_organization_units_f_tl | 1 | 64 | 1 (0)| 00:00:01 |
|*108 | INDEX UNIQUE SCAN |
HR_ORGANIZATION_UNITS_F_TL_PK | 1 | | 0 (0)| 00:00:01 |
| 109 | TABLE ACCESS BY INDEX ROWID | pjf_proj_elements_b
| 1 | 35 | 1 (0)| 00:00:01 |
|*110 | INDEX UNIQUE SCAN |
PJF_PROJ_ELEMENTS_U1 | 1 | | 0 (0)| 00:00:01 |
| 111 | TABLE ACCESS BY INDEX ROWID | pjf_exp_types_tl
| 1 | 33 | 1 (0)| 00:00:01 |
|*112 | INDEX UNIQUE SCAN | PJF_EXP_TYPES_TL_U1
| 1 | | 0 (0)| 00:00:01 |
| 113 | TABLE ACCESS BY INDEX ROWID | pjf_projects_all_tl
| 1 | 41 | 1 (0)| 00:00:01 |
|*114 | INDEX UNIQUE SCAN |
PJF_PROJECTS_ALL_TL_PK | 1 | | 0 (0)| 00:00:01 |
| 115 | TABLE ACCESS BY INDEX ROWID |
pjc_prj_asset_ln_dets | 1 | 10 | 2 (0)| 00:00:01 |
|*116 | INDEX RANGE SCAN |
PJC_PRJ_ASSET_LN_DETS_N2 | 1 | | 1 (0)| 00:00:01 |
| 117 | TABLE ACCESS BY INDEX ROWID |
pjc_prj_asset_lns_all | 1 | 11 | 2 (0)| 00:00:01 |
|*118 | INDEX RANGE SCAN |
PJC_PRJ_ASSET_LNS_ALL_N5 | 1 | | 1 (0)| 00:00:01 |
|*119 | TABLE ACCESS BY INDEX ROWID |
pjc_cost_dist_lines_all | 1 | 27 | 2 (0)| 00:00:01 |
|*120 | INDEX RANGE SCAN |
PJC_COST_DIST_LINES_ALL_U1 | 1 | | 1 (0)| 00:00:01 |
| 121 | VIEW PUSHED PREDICATE | VW_SSQ_2
| 1 | 2004 | 6 (0)| 00:00:01 |
| 122 | SORT GROUP BY |
| 1 | 93 | 6 (0)| 00:00:01 |
| 123 | NESTED LOOPS |
| 1 | 93 | 6 (0)| 00:00:01 |
| 124 | NESTED LOOPS |
| 1 | 93 | 6 (0)| 00:00:01 |
| 125 | NESTED LOOPS |
| 1 | 59 | 4 (0)| 00:00:01 |
|*126 | TABLE ACCESS BY INDEX ROWID | pjf_project_parties
| 1 | 28 | 3 (0)| 00:00:01 |
|*127 | INDEX RANGE SCAN |
PJF_PROJECT_PARTIES_N1 | 1 | | 2 (0)| 00:00:01 |
|*128 | TABLE ACCESS BY INDEX ROWID |
pjf_proj_role_types_tl | 1 | 31 | 1 (0)| 00:00:01 |
|*129 | INDEX UNIQUE SCAN |
PJF_PROJ_ROLE_TYPES_TL_U1 | 1 | | 0 (0)| 00:00:01 |
|*130 | INDEX RANGE SCAN |
PER_PERSON_NAMES_F_N12 | 1 | | 1 (0)| 00:00:01 |
|*131 | TABLE ACCESS BY INDEX ROWID | per_person_names_f
| 1 | 34 | 2 (0)| 00:00:01 |
| 132 | BUFFER SORT |
| 19 | 760 | 1193 (1)| 00:00:01 |
|*133 | TABLE ACCESS STORAGE FULL |
pjf_project_types_tl | 19 | 760 | 3 (0)| 00:00:01 |
| 134 | VIEW | index$_join$_028
| 2592 | 113K| 65 (0)| 00:00:01 |
|*135 | HASH JOIN |
| | | | |
|*136 | HASH JOIN |
| | | | |
| 137 | INDEX STORAGE FAST FULL SCAN |
PJF_PROJECTS_ALL_B_N2 | 2592 | 113K| 24 (0)| 00:00:01 |
| 138 | INDEX STORAGE FAST FULL SCAN |
PJF_PROJECTS_ALL_B_N10 | 2592 | 113K| 20 (0)| 00:00:01 |
| 139 | INDEX STORAGE FAST FULL SCAN |
PJF_PROJECTS_ALL_B_U5 | 2592 | 113K| 14 (0)| 00:00:01 |
|*140 | TABLE ACCESS BY INDEX ROWID BATCHED |
hr_all_organization_units_f | 1 | 29 | 2 (0)| 00:00:01 |
|*141 | INDEX RANGE SCAN |
HR_ALL_ORGANIZATION_UNITS_PK | 1 | | 1 (0)| 00:00:01 |
|*142 | TABLE ACCESS BY INDEX ROWID |
hr_organization_units_f_tl | 1 | 64 | 1 (0)| 00:00:01 |
|*143 | INDEX UNIQUE SCAN |
HR_ORGANIZATION_UNITS_F_TL_PK | 1 | | 0 (0)| 00:00:01 |
| 144 | VIEW PUSHED PREDICATE | VW_NSO_1
| 1 | 2 | 5 (0)| 00:00:01 |
| 145 | NESTED LOOPS SEMI |
| 1 | 114 | 5 (0)| 00:00:01 |
| 146 | TABLE ACCESS BY INDEX ROWID BATCHED |
fnd_lookup_values_tl | 1 | 70 | 4 (0)| 00:00:01 |
|*147 | INDEX RANGE SCAN |
FND_LOOKUP_VALUES_TL_U2 | 1 | | 3 (0)| 00:00:01 |
|*148 | INDEX UNIQUE SCAN |
FND_LOOKUP_VALUES_B_U1 | 6 | 264 | 1 (0)| 00:00:01 |
| 149 | TABLE ACCESS BY INDEX ROWID | fa_deprn_periods
| 1 | 25 | 1 (0)| 00:00:01 |
|*150 | INDEX UNIQUE SCAN | FA_DEPRN_PERIODS_U3
| 1 | | 0 (0)| 00:00:01 |
| 151 | TABLE ACCESS BY INDEX ROWID | fa_deprn_periods
| 1 | 25 | 1 (0)| 00:00:01 |
|*152 | INDEX UNIQUE SCAN | FA_DEPRN_PERIODS_U3
| 1 | | 0 (0)| 00:00:01 |
| 153 | SORT ORDER BY |
| 1 | 9196 | 3 (34)| 00:00:01 |
| 154 | VIEW |
| 1 | 9196 | 2 (0)| 00:00:01 |
| 155 | TABLE ACCESS STORAGE FULL |
SYS_TEMP_0FD9D6AB6_2A3AEFF | 1 | 2753 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
-----------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

6 - filter("DENORM_WBS_LEVEL"=1 OR "PROJ_ELEMENT_ID"=:B1 OR
"PROJ_ELEMENT_ID"=:B2)
7 - access("DENORM_TOP_ELEMENT_ID"=:B1)
8 - access("P1"."PROJ_ELEMENT_ID"="PROJ_ELEMENT_ID" AND
"P1"."LANGUAGE"=USERENV('LANG'))
11 - access("AIA"."INVOICE_ID"=:B1)
16 - access("AIA"."INVOICE_ID"=:B1)
18 - access("AIA"."VENDOR_ID"="POZ_VENDOR"."VENDOR_ID")
19 - access("PARTY_ID"="POZ_VENDOR"."PARTY_ID")
20 - filter("TCA_ADDITIONAL_NAMES"."PREFERRED_FLAG"(+)='Y' AND
"TCA_ADDITIONAL_NAMES"."STATUS_FLAG"(+)='A' AND
"TCA_ADDITIONAL_NAMES"."PARTY_NAME_TYPE"(+)='PHONETIC')
21 - access("PARTY_ID"="TCA_ADDITIONAL_NAMES"."PARTY_ID"(+))
26 - access("AIA"."INVOICE_ID"=:B1)
28 - access("AIA"."VENDOR_ID"="POZ_VENDOR"."VENDOR_ID")
30 - access("PARTY_ID"="POZ_VENDOR"."PARTY_ID")
31 - filter("TCA_ADDITIONAL_NAMES"."PREFERRED_FLAG"(+)='Y' AND
"TCA_ADDITIONAL_NAMES"."STATUS_FLAG"(+)='A' AND
"TCA_ADDITIONAL_NAMES"."PARTY_NAME_TYPE"(+)='PHONETIC')
32 - access("PARTY_ID"="TCA_ADDITIONAL_NAMES"."PARTY_ID"(+))
37 - filter("APHD"."PAY_DIST_LOOKUP_CODE"='DISCOUNT')
38 - access("APHD"."INVOICE_PAYMENT_ID"=:B1)
41 - filter("XAL"."ACCOUNTING_CLASS_CODE"='DISCOUNT')
42 - access("XAL"."CODE_COMBINATION_ID"="GCC"."CODE_COMBINATION_ID")
43 - filter("XDL"."SOURCE_DISTRIBUTION_ID_NUM_1"=:B1 AND
"XDL"."EVENT_ID"="APHD"."ACCOUNTING_EVENT_ID")
44 - access("XAL"."AE_HEADER_ID"="XDL"."AE_HEADER_ID" AND
"XAL"."AE_LINE_NUM"="XDL"."AE_LINE_NUM")
50 - filter("XDL"."SOURCE_DISTRIBUTION_ID_NUM_1"=:B1 AND
"XDL"."APPLIED_TO_SOURCE_ID_NUM_1"=:B2)
51 - filter("XDL"."EVENT_ID"="XAH"."EVENT_ID")
 52 - access("XDL"."AE_HEADER_ID"="XAH"."AE_HEADER_ID")
53 - filter("XAL"."ACCOUNTING_CLASS_CODE"='DISCOUNT' OR
"XAL"."ACCOUNTING_CLASS_CODE"='FREIGHT' OR
"XAL"."ACCOUNTING_CLASS_CODE"='ITEM EXPENSE' OR
"XAL"."ACCOUNTING_CLASS_CODE"='MISCELLANEOUS EXPENSE' OR
"XAL"."ACCOUNTING_CLASS_CODE"='NRTAX' OR
"XAL"."ACCOUNTING_CLASS_CODE"='RAW_COST' OR
"XAL"."ACCOUNTING_CLASS_CODE"='SELF_ASSESSED_TAX')
54 - access("XAH"."AE_HEADER_ID"="XAL"."AE_HEADER_ID" AND
"XDL"."AE_LINE_NUM"="XAL"."AE_LINE_NUM")
55 - access("XAL"."CODE_COMBINATION_ID"="GCC"."CODE_COMBINATION_ID")
58 - filter(ROWNUM=1)
62 - filter("XAH"."EVENT_TYPE_CODE" LIKE '%DIST%')
63 - access("XAH"."EVENT_ID"=:B1)
64 - filter("XAL"."ACCOUNTING_CLASS_CODE"='DISCOUNT' OR
"XAL"."ACCOUNTING_CLASS_CODE"='FREIGHT' OR
"XAL"."ACCOUNTING_CLASS_CODE"='ITEM EXPENSE' OR
"XAL"."ACCOUNTING_CLASS_CODE"='MISCELLANEOUS EXPENSE' OR
"XAL"."ACCOUNTING_CLASS_CODE"='NRTAX' OR
"XAL"."ACCOUNTING_CLASS_CODE"='RAW_COST' OR
"XAL"."ACCOUNTING_CLASS_CODE"='SELF_ASSESSED_TAX')
65 - access("XAH"."AE_HEADER_ID"="XAL"."AE_HEADER_ID")
66 - access("XAL"."CODE_COMBINATION_ID"="GCC"."CODE_COMBINATION_ID")
69 - access("GCC"."CODE_COMBINATION_ID"=:B1)
71 - filter(NVL("PPAL"."TRANSFER_STATUS_CODE",'P')='P' OR
"PPAL"."TRANSFER_STATUS_CODE"='T' AND (SELECT "PERIOD_COUNTER"
FROM "fa_deprn_periods" "FA_DEPRN_PERIODS" WHERE "PERIOD_NAME"=:B1
AND "BOOK_TYPE_CODE"='USA CORPORATE')> (SELECT
"PERIOD_COUNTER" FROM "fa_deprn_periods" "FA_DEPRN_PERIODS" WHERE
"PERIOD_NAME"='MAR-2022' AND "BOOK_TYPE_CODE"='USA
CORPORATE'))
75 - access("PPT"."PROJECT_TYPE_ID"="PPA"."PROJECT_TYPE_ID")
86 - access("PEI"."ORG_ID"="ORGANIZATION_ID")
93 - storage("PPR"."PROJECT_ROLE_NAME"='Project Manager')
filter("PPR"."PROJECT_ROLE_NAME"='Project Manager')
95 - filter("EFFECTIVE_END_DATE">=TRUNC(SYSDATE@!) AND (("BUSINESS_GROUP_ID"=0 OR
"BUSINESS_GROUP_ID"=1) OR

"BUSINESS_GROUP_ID"=DECODE(SYS_CONTEXT('FND_VPD_CTX','FND_ENTERPRISE_ID'),NULL,"BUS
INESS_GROUP_ID",TO_NUMBER(SYS_CONTEXT('FND_VP
D_CTX','FND_ENTERPRISE_ID')))) AND
"EFFECTIVE_START_DATE"<=TRUNC(SYSDATE@!))
96 - access("LANGUAGE"=USERENV('LANG') AND "NAME"='WM US Business Unit')
97 - filter("BUSINESS_GROUP_ID"=0 OR "BUSINESS_GROUP_ID"=1 OR

"BUSINESS_GROUP_ID"=DECODE(SYS_CONTEXT('FND_VPD_CTX','FND_ENTERPRISE_ID'),NULL,"BUS
INESS_GROUP_ID",TO_NUMBER(SYS_CONTEXT('FND_VP
D_CTX','FND_ENTERPRISE_ID'))))
98 - access("ORGANIZATION_ID"="ORGANIZATION_ID" AND
"EFFECTIVE_START_DATE"="EFFECTIVE_START_DATE" AND
"EFFECTIVE_END_DATE"="EFFECTIVE_END_DATE")
filter("EFFECTIVE_END_DATE">=TRUNC(SYSDATE@!) AND
"EFFECTIVE_START_DATE"<=TRUNC(SYSDATE@!))
99 - filter("EFFECTIVE_END_DATE">=TRUNC(SYSDATE@!) AND
"EFFECTIVE_START_DATE"<=TRUNC(SYSDATE@!) AND (("BUSINESS_GROUP_ID"=0
OR "BUSINESS_GROUP_ID"=1) OR
"BUSINESS_GROUP_ID"=DECODE(SYS_CONTEXT('FND_VPD_CTX','FND_ENTERPRISE_ID'),NULL,"BUS
INESS_GROUP_ID",
TO_NUMBER(SYS_CONTEXT('FND_VPD_CTX','FND_ENTERPRISE_ID')))))
 100 - access("ORGANIZATION_ID"="ORGANIZATION_ID" AND
"ORG_INFORMATION_CONTEXT"='FUN_BUSINESS_UNIT')
101 - access("ORGANIZATION_ID"="ORGANIZATION_ID" AND
"CLASSIFICATION_CODE"='FUN_BUSINESS_UNIT' AND
"EFFECTIVE_END_DATE">=TRUNC(SYSDATE@!) AND
"EFFECTIVE_START_DATE"<=TRUNC(SYSDATE@!))
filter("EFFECTIVE_END_DATE">=TRUNC(SYSDATE@!))
102 - filter("STATUS"='A' AND (("BUSINESS_GROUP_ID"=0 OR "BUSINESS_GROUP_ID"=1) OR

"BUSINESS_GROUP_ID"=DECODE(SYS_CONTEXT('FND_VPD_CTX','FND_ENTERPRISE_ID'),NULL,"BUS
INESS_GROUP_ID",TO_NUMBER(SYS_CONTEXT('FND_VP
D_CTX','FND_ENTERPRISE_ID')))))
104 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"PEI"."ORG_ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"PEI"."ORG_ID"))
105 - filter("BUSINESS_GROUP_ID"=0 OR "BUSINESS_GROUP_ID"=1 OR

"BUSINESS_GROUP_ID"=DECODE(SYS_CONTEXT('FND_VPD_CTX','FND_ENTERPRISE_ID'),NULL,"BUS
INESS_GROUP_ID",TO_NUMBER(SYS_CONTEXT('FND_VP
D_CTX','FND_ENTERPRISE_ID'))))
106 - access("PEI"."EXPENDITURE_ORGANIZATION_ID"="ORGANIZATION_ID" AND
"EFFECTIVE_END_DATE">=TRUNC(SYSDATE@!) AND
"EFFECTIVE_START_DATE"<=TRUNC(SYSDATE@!))
filter("EFFECTIVE_END_DATE">=TRUNC(SYSDATE@!))
107 - filter("BUSINESS_GROUP_ID"=0 OR "BUSINESS_GROUP_ID"=1 OR

"BUSINESS_GROUP_ID"=DECODE(SYS_CONTEXT('FND_VPD_CTX','FND_ENTERPRISE_ID'),NULL,"BUS
INESS_GROUP_ID",TO_NUMBER(SYS_CONTEXT('FND_VP
D_CTX','FND_ENTERPRISE_ID'))))
108 - access("ORGANIZATION_ID"="ORGANIZATION_ID" AND "LANGUAGE"=USERENV('LANG')
AND
"EFFECTIVE_START_DATE"="EFFECTIVE_START_DATE" AND
"EFFECTIVE_END_DATE"="EFFECTIVE_END_DATE")
filter("EFFECTIVE_END_DATE">=TRUNC(SYSDATE@!) AND
"EFFECTIVE_START_DATE"<=TRUNC(SYSDATE@!))
110 - access("PEI"."TASK_ID"="PPEB"."PROJ_ELEMENT_ID")
112 - access("PEI"."EXPENDITURE_TYPE_ID"="PET"."EXPENDITURE_TYPE_ID" AND
"PET"."LANGUAGE"=USERENV('LANG'))
114 - access("PPAT"."PROJECT_ID"="PEI"."PROJECT_ID" AND
"PPAT"."LANGUAGE"=USERENV('LANG'))
116 - access("PPAD"."EXPENDITURE_ITEM_ID"(+)="PEI"."EXPENDITURE_ITEM_ID")
118 -
access("PPAL"."PROJECT_ASSET_LINE_DETAIL_ID"(+)="PPAD"."PROJECT_ASSET_LINE_DETAIL_I
D")
119 - filter("PCD"."CAPITALIZABLE_FLAG"='Y' AND "PCD"."PRVDR_GL_DATE"<=TO_DATE('
2022-03-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
120 - access("PCD"."EXPENDITURE_ITEM_ID"="PEI"."EXPENDITURE_ITEM_ID")
126 - filter("PPP2"."END_DATE_ACTIVE" IS NULL)
127 - access("PPP2"."PROJECT_ID"="PEI"."PROJECT_ID")
128 - filter("PPRTL2"."PROJECT_ROLE_NAME"='Project Manager')
129 - access("PPP2"."PROJECT_ROLE_ID"="PPRTL2"."PROJECT_ROLE_ID" AND
"PPRTL2"."LANGUAGE"=USERENV('LANG'))
130 - access("PPP2"."RESOURCE_SOURCE_ID"="PERSON_ID" AND "NAME_TYPE"='GLOBAL')
131 - filter("BUSINESS_GROUP_ID"=0 OR "BUSINESS_GROUP_ID"=1 OR

"BUSINESS_GROUP_ID"=DECODE(SYS_CONTEXT('FND_VPD_CTX','FND_ENTERPRISE_ID'),NULL,"BUS
INESS_GROUP_ID",TO_NUMBER(SYS_CONTEXT('FND_VP
D_CTX','FND_ENTERPRISE_ID'))))
133 - storage("PPT"."LANGUAGE"=USERENV('LANG'))
 filter("PPT"."LANGUAGE"=USERENV('LANG'))
135 - access(ROWID=ROWID)
136 - access(ROWID=ROWID)
140 - filter("BUSINESS_GROUP_ID"=0 OR "BUSINESS_GROUP_ID"=1 OR

"BUSINESS_GROUP_ID"=DECODE(SYS_CONTEXT('FND_VPD_CTX','FND_ENTERPRISE_ID'),NULL,"BUS
INESS_GROUP_ID",TO_NUMBER(SYS_CONTEXT('FND_VP
D_CTX','FND_ENTERPRISE_ID'))))
141 - access("PPA"."CARRYING_OUT_ORGANIZATION_ID"="ORGANIZATION_ID" AND
"EFFECTIVE_END_DATE">=TRUNC(SYSDATE@!) AND
"EFFECTIVE_START_DATE"<=TRUNC(SYSDATE@!))
filter("EFFECTIVE_END_DATE">=TRUNC(SYSDATE@!))
142 - filter("BUSINESS_GROUP_ID"=0 OR "BUSINESS_GROUP_ID"=1 OR

"BUSINESS_GROUP_ID"=DECODE(SYS_CONTEXT('FND_VPD_CTX','FND_ENTERPRISE_ID'),NULL,"BUS
INESS_GROUP_ID",TO_NUMBER(SYS_CONTEXT('FND_VP
D_CTX','FND_ENTERPRISE_ID'))))
143 - access("ORGANIZATION_ID"="ORGANIZATION_ID" AND "LANGUAGE"=USERENV('LANG')
AND
"EFFECTIVE_START_DATE"="EFFECTIVE_START_DATE" AND
"EFFECTIVE_END_DATE"="EFFECTIVE_END_DATE")
filter("EFFECTIVE_END_DATE">=TRUNC(SYSDATE@!) AND
"EFFECTIVE_START_DATE"<=TRUNC(SYSDATE@!))
147 - access("LOOKUP_TYPE"='PPM_PROJECT_TYPES_NO_ASSET' AND
"MEANING"="PPT"."PROJECT_TYPE" AND "LANGUAGE"='US' AND
"ENTERPRISE_ID"=1 AND "SANDBOX_ID"='1')
filter("MEANING"="PPT"."PROJECT_TYPE" AND "LANGUAGE"='US' AND
"SANDBOX_ID"='1' AND "ENTERPRISE_ID"=1)
148 - access("LOOKUP_TYPE"='PPM_PROJECT_TYPES_NO_ASSET' AND
"VIEW_APPLICATION_ID"="VIEW_APPLICATION_ID" AND
"LOOKUP_CODE"="LOOKUP_CODE" AND "SET_ID"="SET_ID" AND
"ENTERPRISE_ID"=1 AND "SANDBOX_ID"='1')
150 - access("BOOK_TYPE_CODE"='USA CORPORATE' AND "PERIOD_NAME"=:B1)
152 - access("BOOK_TYPE_CODE"='USA CORPORATE' AND "PERIOD_NAME"='MAR-2022')

]]>
</DATA_DS>
</SQL_EXPLAIN_PLAN>