Nqquery New

[2021-09-01T10:26:18.
781+00:00] [OracleBIServerComponent] [TRACE:7] [] [] [ecid:

005milzCx5o7EC5pvOc9yX0001mg0003ff,0:4:1:1:3] [tid: b0768700] [messageid: USER-0]
[requestid: 642800fe] [sessionid: 64280000] [username: cedric.mabou@catalyst-
us.com] ############################################## [[
-------------------- SQL Request, logical request hash:
c15dae01
set variable LOGLEVEL = 7,PREFERRED_CURRENCY='User Preferred Currency
1',ENABLE_DIMENSIONALITY=1,NO_FORCE_TO_DETAIL_BIN=1;SELECT
0 s_0,
"Workforce Performance - Performance Rating Real Time"."- Hierarchy"."Level 1
Department" s_1,
"Workforce Performance - Performance Rating Real Time"."- Performance
Rating"."Calculated Manager Overall Rating" s_2,
Rating"."Overall Manager Rating" s_3,
Rating"."Status" s_4,
"Workforce Performance - Performance Rating Real Time"."- Rating Level"."Rating
Short Description" s_5,
"Workforce Performance - Performance Rating Real Time"."Performance Document
Details"."Performance Document Manager" s_6,
"Workforce Performance - Performance Rating Real Time"."Performance
Document"."Performance Document Period Name" s_7,
"Workforce Performance - Performance Rating Real Time"."Position"."Position
Name" s_8,
"Workforce Performance - Performance Rating Real Time"."Worker"."Assignment
Number" s_9,
"Workforce Performance - Performance Rating Real Time"."Worker"."Employee First
Name" s_10,
"Workforce Performance - Performance Rating Real Time"."Worker"."Employee Last
Name" s_11,
"Workforce Performance - Performance Rating Real Time"."Worker"."Person Number"
s_12,
"Workforce Performance - Performance Task Status Real Time"."- Performance Task
Status Details"."Is General Status" s_13,
Status Details"."Step Completion Date" s_14,
"Workforce Performance - Performance Task Status Real Time"."- Performance
Task"."Performance Task" s_15,
DESCRIPTOR_IDOF("Workforce Performance - Performance Rating Real
Time"."Position"."Position Name") s_16,
DESCRIPTOR_IDOF("Workforce Performance - Performance Task Status Real Time"."-
Performance Task Status Details"."Is General Status") s_17,
DESCRIPTOR_IDOF("Workforce Performance - Performance Task Status Real Time"."-
Performance Task"."Performance Task") s_18,
Rating"."Average Calculated Manager Overall Rating" s_19,
Status"."Performance Tasks Completed" s_20,
Status"."Performance Tasks" s_21
FROM "Workforce Performance - Performance Rating Real Time"
WHERE
((DESCRIPTOR_IDOF("Workforce Performance - Performance Rating Real
Time"."Worker"."Assignment Status Type") = 'ACTIVE') AND
(DESCRIPTOR_IDOF("Workforce Performance - Performance Task Status Real Time"."-
Performance Task Status Details"."Is General Status") = 'Y') AND
(DESCRIPTOR_IDOF("Workforce Performance - Performance Task Status Real Time"."-
Performance Task"."Performance Task") = 'FAPPR') AND ("Worker"."Person Number" =
'PZBT4092') AND ("- Performance Rating"."Overall Manager Rating" IS NOT NULL))
ORDER BY 1, 8 ASC NULLS LAST, 3 ASC NULLS LAST, 13 ASC NULLS LAST, 10 ASC NULLS
LAST, 11 ASC NULLS LAST, 12 ASC NULLS LAST, 9 ASC NULLS LAST, 7 ASC NULLS LAST, 2
ASC NULLS LAST, 4 ASC NULLS LAST, 6 ASC NULLS LAST, 5 ASC NULLS LAST, 15 ASC NULLS
LAST, 16 ASC NULLS LAST, 14 ASC NULLS LAST
FETCH FIRST 75001 ROWS ONLY
/* QUERY_SRC_CD='rawSQL' */
]]
[2021-09-01T10:26:18.781+00:00] [OracleBIServerComponent] [TRACE:7] [] [] [ecid:
us.com] -------------------- General Query Info: [[
Repository: Star, Subject Area: Core, Presentation: Workforce Performance -
Performance Rating Real Time
]]
us.com] -------------------- Measure 'Average Calculated Manager Overall Rating'
fixed to 'grand total' level in dimension 'Dim - Performance Task Status Details'
because of missing logical join
us.com] -------------------- Measure 'Average Calculated Manager Overall Rating'
fixed to 'grand total' level in dimension 'Dim - Performance Task' because of
missing logical join
us.com] -------------------- Measure 'Performance Tasks Completed' fixed to 'grand
total' level in dimension 'Dim - Rating Level' because of missing logical join
us.com] -------------------- Measure 'Performance Tasks' fixed to 'grand total'
level in dimension 'Dim - Rating Level' because of missing logical join
us.com] -------------------- Measure 'Fact - Performance Task Status' fixed to
'grand total' level in dimension 'Fact - Performance Rating' because of missing
logical join
logical join
logical join
logical join
logical join
logical join
us.com] -------------------- Logical Request (before navigation): [[
RqList
0 as c1 GB,
Dim - Department.Level 1 Department as c2 GB,
Fact - Performance Rating.Calculated Manager Overall Rating as c3 GB,
Fact - Performance Rating.Overall Manager Rating as c4 GB,
Fact - Performance Rating.Status as c5 GB,
Dim - Rating Level.Rating Short Description as c6 GB,
Dim - Performance Document Details.Performance Document Manager as c7 GB,
Dim - Performance Document.Performance Document Period Name as c8 GB,
Dim - HR Position.Position Name as c9 GB,
Dim - Employee.Assignment Number as c10 GB,
Dim - Employee.Employee First Name as c11 GB,
Dim - Employee.Employee Last Name as c12 GB,
Dim - Employee.Person Number as c13 GB,
Dim - Performance Task Status Details.Is General Status as c14 GB,
Dim - Performance Task Status Details.Step Completion Date as c15 GB,
Dim - Performance Task.Performance Task as c16 GB,
Dim - HR Position.Position Key as c17 GB,
Dim - Performance Task Status Details.Is General Status Code as c18 GB,
Dim - Performance Task.Performance Task Code as c19 GB,
Average Calculated Manager Overall Rating:[DAggr(Fact - Performance
Rating.Average Calculated Manager Overall Rating by [ Dim - Performance
Document.Performance Document Period Name, Dim - HR Position.Position Id, Dim -
Employee.Person Number, Dim - Employee.Employee First Name, Dim - Employee.Employee
Last Name, Fact - Performance Rating.Status, Fact - Performance Rating.Calculated
Manager Overall Rating, Fact - Performance Rating.Overall Manager Rating, Dim -
Rating Level.Rating Short Description, Dim - HR Position.Position Key, Dim - HR
Position.Position Name, Dim - Department.Level 1 Department ID, Dim -
Department.Level 1 Department, Dim - Employee.Assignment Number, Dim - Performance
Document Details.Performance Document Manager] SB All,All,All,All,All,All)] as c20
GB,
Performance Tasks Completed:[DAggr(Fact - Performance Task Status.Performance
Tasks Completed by [ Dim - Performance Document.Performance Document Period Name,
Dim - Performance Task Status Details.Step Completion Date, Dim - HR
Position.Position Id, Dim - Employee.Person Number, Dim - Employee.Employee First
Name, Dim - Employee.Employee Last Name, Dim - Performance Task.Performance Task
Code, Dim - Performance Task.Performance Task, Dim - HR Position.Position Key, Dim
- HR Position.Position Name, Dim - Department.Level 1 Department ID, Dim -
Department.Level 1 Department, Dim - Employee.Assignment Number, Dim - Performance
Task Status Details.Is General Status Code, Dim - Performance Task Status
Details.Is General Status, Dim - Performance Document Details.Performance Document
Manager] SB Performance Document Name,All)] as c21 GB,
Performance Tasks:[DAggr(Fact - Performance Task Status.Performance Tasks by
[ Dim - Performance Document.Performance Document Period Name, Dim - Performance
Task Status Details.Step Completion Date, Dim - HR Position.Position Id, Dim -
Employee.Person Number, Dim - Employee.Employee First Name, Dim - Employee.Employee
Last Name, Dim - Performance Task.Performance Task Code, Dim - Performance
Task.Performance Task, Dim - HR Position.Position Key, Dim - HR Position.Position
Name, Dim - Department.Level 1 Department ID, Dim - Department.Level 1 Department,
Dim - Employee.Assignment Number, Dim - Performance Task Status Details.Is General
Status Code, Dim - Performance Task Status Details.Is General Status, Dim -
Performance Document Details.Performance Document Manager] SB All)] as c22 GB,
Dim - HR Position.Position Id as c23 GB,
Dim - Department.Level 1 Department ID as c24 GB
DetailFilter: Dim - Employee.Person Number = 'PZBT4092' and Dim - Performance
Task.Performance Task Code = 'FAPPR' and Dim - Performance Task Status Details.Is
General Status Code = 'Y' and Dim - Employee.Assignment Status Type Code = 'ACTIVE'
and not Fact - Performance Rating.Overall Manager Rating is null
OrderBy: c1 asc, c8 asc NULLS LAST, c3 asc NULLS LAST, c13 asc NULLS LAST, c10 asc
NULLS LAST, c11 asc NULLS LAST, c12 asc NULLS LAST, c9 asc NULLS LAST, c7 asc NULLS
LAST, c2 asc NULLS LAST, c4 asc NULLS LAST, c6 asc NULLS LAST, c5 asc NULLS LAST,
c15 asc NULLS LAST, c16 asc NULLS LAST, c14 asc NULLS LAST
]]
us.com] -------------------- The logical query hits the plan cache [[
plan
RqList <<1436180229>> [for database 3023:1418785:HCM_OLTP,57] /* FETCH FIRST 75001
ROWS ONLY */
D1.c1 as c1 [for database 3023:1418785:HCM_OLTP,57],
D1.c22 as c22 [for database 3023:1418785:HCM_OLTP,57]
Child Nodes (RqJoinSpec): <<1436180275>> [for database 3023:1418785:HCM_OLTP,57]
RqJoinNode <<1436180276>> []
(
RqBreakFilter
<<1436180279>>[2,3,4,6,7,8,9,10,11,12,13,15,17,18,19,23,24,25] [for database
3023:1418785:HCM_OLTP,57]
RqList <<1436180280>> [for database 3023:1418785:HCM_OLTP,57]
0 as c1 GB [for database 3023:1418785:HCM_OLTP,57],
case when D901.c1 is not null then D901.c1 when D902.c1 is not
null then D902.c1 end as c2 GB [for database 3023:1418785:HCM_OLTP,57],
D901.c2 as c3 GB [for database 3023:1418785:HCM_OLTP,57],
case when D901.c10 is not null then D901.c10 when D902.c6 is
not null then D902.c6 end as c11 GB [for database 3023:1418785:HCM_OLTP,57],
Child Nodes (RqJoinSpec): <<1436180398>> [for database
3023:1418785:HCM_OLTP,57]
RqJoinNode <<1436180399>> []
(
RqList <<1436180403>> [for database
3023:1418785:HCM_OLTP,57]
D901.c7 as c1 [for database
3023:1418785:HCM_OLTP,57],
D901.c5 as c2 GB [for database
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
ifnull(D901.c2 , D901.c3) as c4 GB [for
database 3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57]
Child Nodes (RqJoinSpec): <<1436180439>> [for
database 3023:1418785:HCM_OLTP,57]
RqJoinNode <<1436180440>> []
(
3023:1418785:HCM_OLTP,57]
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57]
Child Nodes (RqJoinSpec):
<<1436180477>> [for database 3023:1418785:HCM_OLTP,57]
RqJoinNode <<1436180478>> []
(
RqList <<1436180482>>
[for database 3023:1418785:HCM_OLTP,57]
avg(case when
D1.c18 = 'MANAGER' then D1.c19 end by [ D1.c8, D1.c13, D1.c11, D1.c12, D1.c17,
case when D1.c18 = 'MANAGER' then D1.c19 end , case when D1.c18 = 'MANAGER' then
D1.c20 end , D1.c5, D1.c14, D1.c15, D1.c10, D1.c7] ) as c1 [for database
3023:1418785:HCM_OLTP,57],
case when D1.c18 =
'MANAGER' then D1.c20 end as c3 [for database 3023:1418785:HCM_OLTP,57],
case when D1.c18 =
D1.c5 as c5 [for
case when D1.c15 =
D1.c22 then NULL else D1.c21 end as c6 [for database 3023:1418785:HCM_OLTP,57],
D1.c7 as c7 [for
D1.c8 as c8 [for
D1.c9 as c9 [for
D1.c10 as c10 [for
D1.c11 as c11 [for
D1.c12 as c12 [for
D1.c13 as c13 [for
D1.c14 as c14 [for
D1.c15 as c15 [for
D1.c17 as c17 [for
Child Nodes
(RqJoinSpec): <<1436180553>> [for database 3023:1418785:HCM_OLTP,57]
RqJoinNode
<<1436180554>> []
(
RqList
C469780226 as c5 [for database 3023:1418785:HCM_OLTP,57],

C53212743 as c22 [for database 3023:1418785:HCM_OLTP,57]

Child Nodes
RqJoinNode <<1436180592>> []
(SELECT V340935018.RLT_RATING_SHORT_DESCR AS C469780226,

V296907724.FULL_NAME AS C358965261, V329124045.TPTL_CUSTOMARY_NAME AS
C359378142, V207753497.NAME AS C272505829,
V216021127.ASSIGNMENT_NUMBER AS C399099852, V216021127.FIRST_NAME633 AS
C50990987, V216021127.LAST_NAME662 AS C53347599,
V216021127.PERSON_NUMBER AS C458818009, V207753497.POSITION_ID74 AS
C244241163, V62234047.ORGANIZATION_ID AS C227489187,
V487837372.E_STATUS_CODE AS C262158206, V487837372.EP_ROLE_TYPE_CODE AS
C352987552, V487837372.ERO_CALCULATED_RATING AS C343919161,
V487837372.RLB_RATING_LEVEL_CODE AS C330634914, V62234047.NAME AS
V533469212.EVALUATION_ID AS C255742563, V130235680.CF_TREE_NODE_ID AS
C88025526, V130235680.ENTERPRISE_ID AS C81202873,
V130235680.TREE_CODE AS C293063572, V130235680.TREE_STRUCTURE_CODE AS
C255476159, V130235680.TREE_VERSION_ID AS C370085791,
V75077409.ORG_UNIT_CLASSIFICATION_ID AS C499688583,
V75077409.EFFECTIVE_START_DATE AS C255466098, V75077409.EFFECTIVE_END_DATE
AS C518101740, V524217779.ASSIGNMENT_ID AS C342861816,
V524217779.EFFECTIVE_START_DATE AS C427273052,
V524217779.EFFECTIVE_END_DATE AS C338379411,
V524217779.EFFECTIVE_LATEST_CHANGE AS C340606624,
V524217779.EFFECTIVE_SEQUENCE AS C234863912, V340935018.LANGUAGE AS
C138823076, V216021127.ASSIGNMENT_STATUS_TYPE AS C227063946,
V487837372.E_END_DATE AS C85578061, V487837372.EVALUATION_ID AS
PKA_EvaluationId0, V487837372.EVAL_RATING_ID AS PKA_EvalRatingId0,
V487837372.EP_EVAL_PARTICIPANT_ID AS PKA_EvalParticipantPEOEvalPar0,
V487837372.RATING_LEVEL_ID AS PKA_RatingLevelId0,
V487837372.RLB_BUSINESS_GROUP_ID AS PKA_RatingLevelBPEOBusinessGr0,
V296907724.PERSON_NAME_ID AS PKA_PersonNameId0,
V296907724.EFFECTIVE_START_DATE AS PKA_EffectiveStartDate0,
V296907724.EFFECTIVE_END_DATE AS PKA_EffectiveEndDate0,
V340935018.RLT_RATING_LEVEL_ID AS PKA_RatingLevelTranslationPEO0,
V340935018.RLT_BUSINESS_GROUP_ID AS PKA_RatingLevelTranslationPEO1,
V329124045.TPTL_TMPL_PERIOD_ID AS PKA_TemplatePeriodTranslation0,
V329124045.LANGUAGE AS PKA_Language0, V329124045.TPTL_BUSINESS_GROUP_ID AS
PKA_TemplatePeriodTranslation1, V207753497.EFFECTIVE_START_DATE82 AS
PKA_EffectiveStartDate1, V207753497.EFFECTIVE_END_DATE90 AS
PKA_EffectiveEndDate1, V207753497.POSITION_ID378 AS
PKA_PositionTranslationPEOPos0, V207753497.LANGUAGE AS
PKA_PositionTranslationPEOLan0, V207753497.EFFECTIVE_START_DATE398 AS
PKA_PositionTranslationPEOEff0, V207753497.EFFECTIVE_END_DATE388 AS
PKA_PositionTranslationPEOEff1, V216021127.PERSON_ID415 AS
PKA_PersonDetailsPEOPersonId0, V216021127.EFFECTIVE_START_DATE424 AS
PKA_PersonDetailsPEOEffective0, V216021127.EFFECTIVE_END_DATE433 AS
PKA_PersonDetailsPEOEffective1, V216021127.PERSON_NAME_ID564 AS
PKA_PersonNamePEOPersonNameId0, V216021127.EFFECTIVE_START_DATE574 AS
PKA_PersonNamePEOEffectiveSta0, V216021127.EFFECTIVE_END_DATE584 AS
PKA_PersonNamePEOEffectiveEnd0, V216021127.ASSIGNMENT_ID776 AS
PKA_AssignmentPEOAssignmentId0, V216021127.EFFECTIVE_START_DATE785 AS
PKA_AssignmentPEOEffectiveSta0, V216021127.EFFECTIVE_END_DATE794 AS
PKA_AssignmentPEOEffectiveEnd0, V216021127.EFFECTIVE_LATEST_CHANGE AS
PKA_AssignmentPEOEffectiveLat0, V216021127.EFFECTIVE_SEQUENCE AS
PKA_AssignmentPEOEffectiveSeq0 FROM (SELECT ((NVL( ( SELECT
AssignmentPEO.ASSIGNMENT_ID FROM PER_ALL_ASSIGNMENTS_M
AssignmentPEO WHERE AssignmentPEO.ASSIGNMENT_ID =
EvaluationPEO.ASSIGNMENT_ID AND AssignmentPEO.ASSIGNMENT_TYPE = 'E'
AND AssignmentPEO.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' AND
TRUNC(EvaluationPEO.END_DATE) BETWEEN AssignmentPEO.EFFECTIVE_START_DATE AND
AssignmentPEO.EFFECTIVE_END_DATE UNION SELECT
AssignmentPEO2.ASSIGNMENT_ID FROM PER_ALL_ASSIGNMENTS_M
AssignmentPEO1, PER_ALL_ASSIGNMENTS_M AssignmentPEO2 WHERE
AssignmentPEO1.ASSIGNMENT_ID = EvaluationPEO.ASSIGNMENT_ID AND
AssignmentPEO1.SOURCE_ASSIGNMENT_ID = AssignmentPEO2.assignment_id AND
AssignmentPEO1.ASSIGNMENT_TYPE = 'E' AND AssignmentPEO2.ASSIGNMENT_TYPE
= 'E' AND AssignmentPEO1.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND AssignmentPEO2.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' AND
TRUNC(EvaluationPEO.END_DATE) BETWEEN AssignmentPEO2.EFFECTIVE_START_DATE AND
AssignmentPEO2.EFFECTIVE_END_DATE AND
AssignmentPEO1.SOURCE_ASSIGNMENT_ID IS NOT NULL UNION SELECT
AssignmentPEO1, PER_ALL_ASSIGNMENTS_M AssignmentPEO2,
PER_ALL_ASSIGNMENTS_M AssignmentPEO3 WHERE
AssignmentPEO1.SOURCE_ASSIGNMENT_ID = AssignmentPEO2.ASSIGNMENT_ID AND
= 'E' AND AssignmentPEO3.ASSIGNMENT_TYPE = 'E' AND
AssignmentPEO1.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' AND
AssignmentPEO1.SOURCE_ASSIGNMENT_ID IS NOT NULL AND
AssignmentPEO3.SOURCE_ASSIGNMENT_ID IS NOT NULL), EvaluationPEO.ASSIGNMENT_ID
))) AS E_ASSIGNMENT_ID, EvaluationPEO.END_DATE AS E_END_DATE,
EvaluationPEO.EVALUATION_ID, EvaluationPEO.STATUS_CODE AS E_STATUS_CODE,
EvaluationPEO.TMPL_PERIOD_ID AS E_TMPL_PERIOD_ID, EvaluationPEO.WORKER_ID
AS E_WORKER_ID, EvalSectionPEO.EVAL_SECTION_ID,
EvalRatingOverallPEO.CALCULATED_RATING AS ERO_CALCULATED_RATING,
EvalRatingOverallPEO.EVAL_RATING_ID,
EvalRatingOverallPEO.PERFORMANCE_RATING_ID AS ERO_PERFORMANCE_RATING_ID,
EvalParticipantPEO.EVAL_PARTICIPANT_ID AS EP_EVAL_PARTICIPANT_ID,
EvalParticipantPEO.ROLE_TYPE_CODE AS EP_ROLE_TYPE_CODE,
RatingLevelBPEO.BUSINESS_GROUP_ID AS RLB_BUSINESS_GROUP_ID,
RatingLevelBPEO.RATING_LEVEL_CODE AS RLB_RATING_LEVEL_CODE,
RatingLevelBPEO.RATING_LEVEL_ID FROM HRA_EVALUATIONS EvaluationPEO,
HRA_EVAL_SECTIONS EvalSectionPEO, HRA_EVAL_RATINGS EvalRatingOverallPEO,
HRA_EVAL_PARTICIPANTS EvalParticipantPEO, HRT_RATING_LEVELS_B RatingLevelBPEO WHERE
(EvaluationPEO.EVALUATION_ID = EvalSectionPEO.EVALUATION_ID AND
EvalSectionPEO.EVAL_SECTION_ID = EvalRatingOverallPEO.REFERENCE_ID AND ('SECTION')
= EvalRatingOverallPEO.REFERENCE_TYPE AND EvalRatingOverallPEO.EVAL_PARTICIPANT_ID
= EvalParticipantPEO.EVAL_PARTICIPANT_ID AND EvalRatingOverallPEO.EVALUATION_ID =
EvalParticipantPEO.EVALUATION_ID AND EvalRatingOverallPEO.PERFORMANCE_RATING_ID =
RatingLevelBPEO.RATING_LEVEL_ID(+) AND EvalRatingOverallPEO.BUSINESS_GROUP_ID =
RatingLevelBPEO.BUSINESS_GROUP_ID(+)) AND
( ( ( (UPPER(EvalSectionPEO.SECTION_TYPE_CODE) = UPPER('OS') ) ) ) AND ((1=1)) ))
V487837372, (SELECT EvaluationPEO.EVALUATION_ID, EvaluationPEO.MANAGER_ID
FROM HRA_EVALUATIONS EvaluationPEO) V533469212, (SELECT
PersonNamePEO.PERSON_NAME_ID, PersonNamePEO.EFFECTIVE_START_DATE,
PersonNamePEO.EFFECTIVE_END_DATE, PersonNamePEO.FULL_NAME,
PersonDetailsPEO.PERSON_ID AS PERSON_ID557 FROM PER_PERSON_NAMES_F_V PersonNamePEO,
PER_ALL_PEOPLE_F PersonDetailsPEO WHERE PersonNamePEO.PERSON_ID =
PersonDetailsPEO.PERSON_ID(+) AND ( DATE'2021-09-01' BETWEEN
PersonNamePEO.EFFECTIVE_START_DATE AND PersonNamePEO.EFFECTIVE_END_DATE) AND
( DATE'2021-09-01' BETWEEN PersonDetailsPEO.EFFECTIVE_START_DATE(+) AND
PersonDetailsPEO.EFFECTIVE_END_DATE(+))) V296907724, (SELECT
RatingLevelBPEO.RATING_LEVEL_ID AS RLB_RATING_LEVEL_ID,
RatingLevelTranslationPEO.RATING_LEVEL_ID AS RLT_RATING_LEVEL_ID,
RatingLevelTranslationPEO.LANGUAGE,
RatingLevelTranslationPEO.BUSINESS_GROUP_ID AS RLT_BUSINESS_GROUP_ID,
RatingLevelTranslationPEO.RATING_SHORT_DESCR AS RLT_RATING_SHORT_DESCR FROM
HRT_RATING_LEVELS_B RatingLevelBPEO, HRT_RATING_LEVELS_TL RatingLevelTranslationPEO
WHERE RatingLevelBPEO.RATING_LEVEL_ID = RatingLevelTranslationPEO.RATING_LEVEL_ID
AND RatingLevelBPEO.BUSINESS_GROUP_ID =
RatingLevelTranslationPEO.BUSINESS_GROUP_ID) V340935018, (SELECT
TemplatePeriodBPEO.BUSINESS_GROUP_ID, TemplatePeriodBPEO.TMPL_PERIOD_ID,
TemplatePeriodTranslationPEO.CUSTOMARY_NAME AS TPTL_CUSTOMARY_NAME,
TemplatePeriodTranslationPEO.LANGUAGE,
TemplatePeriodTranslationPEO.TMPL_PERIOD_ID AS TPTL_TMPL_PERIOD_ID,
TemplatePeriodTranslationPEO.BUSINESS_GROUP_ID AS TPTL_BUSINESS_GROUP_ID FROM
HRA_TMPL_PERIODS_B TemplatePeriodBPEO, HRA_TMPL_PERIODS_TL
TemplatePeriodTranslationPEO WHERE TemplatePeriodBPEO.TMPL_PERIOD_ID =
TemplatePeriodTranslationPEO.TMPL_PERIOD_ID(+) AND
TemplatePeriodBPEO.BUSINESS_GROUP_ID =
TemplatePeriodTranslationPEO.BUSINESS_GROUP_ID(+) AND (USERENV('LANG')) =
TemplatePeriodTranslationPEO.LANGUAGE(+)) V329124045, (SELECT
AssignmentPEO.ASSIGNMENT_ID, AssignmentPEO.EFFECTIVE_START_DATE,
AssignmentPEO.EFFECTIVE_END_DATE, AssignmentPEO.EFFECTIVE_LATEST_CHANGE,
AssignmentPEO.EFFECTIVE_SEQUENCE, AssignmentPEO.ORGANIZATION_ID,
AssignmentPEO.PERSON_ID, AssignmentPEO.POSITION_ID FROM
PER_ALL_ASSIGNMENTS_M AssignmentPEO) V524217779, (SELECT
OrgUnitClassificationPEO.ORG_UNIT_CLASSIFICATION_ID,
OrgUnitClassificationPEO.EFFECTIVE_START_DATE,
OrgUnitClassificationPEO.EFFECTIVE_END_DATE,
OrgUnitClassificationPEO.ORGANIZATION_ID,
OrganizationUnitPEO.ORGANIZATION_ID AS UNIT_EO_ORGANIZATION_ID,
OrganizationUnitPEO.EFFECTIVE_START_DATE AS UNIT_EO_EFFECTIVE_START_DATE,
OrganizationUnitPEO.EFFECTIVE_END_DATE AS UNIT_EO_EFFECTIVE_END_DATE FROM
HR_ALL_ORGANIZATION_UNITS_F OrganizationUnitPEO, HR_ORG_UNIT_CLASSIFICATIONS_F
OrgUnitClassificationPEO WHERE (OrganizationUnitPEO.ORGANIZATION_ID =
OrgUnitClassificationPEO.ORGANIZATION_ID(+) AND ( DATE'2021-09-01' BETWEEN
OrganizationUnitPEO.EFFECTIVE_START_DATE AND
OrganizationUnitPEO.EFFECTIVE_END_DATE) AND ( DATE'2021-09-01' BETWEEN
OrgUnitClassificationPEO.EFFECTIVE_START_DATE(+) AND
OrgUnitClassificationPEO.EFFECTIVE_END_DATE(+))) AND
( ( (OrgUnitClassificationPEO.CLASSIFICATION_CODE = 'DEPARTMENT' ) ) )) V75077409,
(SELECT DeptTreeNodeCFPEO.CF_TREE_NODE_ID,
DeptTreeNodeCFPEO.DEP0_PK1_VALUE, DeptTreeNodeCFPEO.DEP30_PK1_VALUE,
DeptTreeNodeCFPEO.DEP31_PK1_VALUE, DeptTreeNodeCFPEO.ENTERPRISE_ID,
DeptTreeNodeCFPEO.TREE_CODE, DeptTreeNodeCFPEO.TREE_STRUCTURE_CODE,
DeptTreeNodeCFPEO.TREE_VERSION_ID,
FndTreeVersionEffDateEO.EFFECTIVE_END_DATE,
FndTreeVersionEffDateEO.EFFECTIVE_START_DATE,
FndTreeVersionEffDateEO.TREE_CODE AS TREE_CODE2226,
FndTreeVersionEffDateEO.TREE_STRUCTURE_CODE AS TREE_STRUCTURE_CODE2235,
FndTreeVersionEffDateEO.TREE_VERSION_ID AS TREE_VERSION_ID2244 FROM
PER_DEPT_TREE_NODE_CF DeptTreeNodeCFPEO, FND_TREE_VERSION_VL
FndTreeVersionEffDateEO WHERE (DeptTreeNodeCFPEO.TREE_CODE =
FndTreeVersionEffDateEO.TREE_CODE AND DeptTreeNodeCFPEO.TREE_VERSION_ID =
FndTreeVersionEffDateEO.TREE_VERSION_ID AND DeptTreeNodeCFPEO.TREE_STRUCTURE_CODE =
FndTreeVersionEffDateEO.TREE_STRUCTURE_CODE AND ( DATE'2021-09-01' BETWEEN
FndTreeVersionEffDateEO.EFFECTIVE_START_DATE AND
FndTreeVersionEffDateEO.EFFECTIVE_END_DATE)) AND
( ( (DeptTreeNodeCFPEO.TREE_STRUCTURE_CODE = 'PER_DEPT_TREE_STRUCTURE' ) AND
(FndTreeVersionEffDateEO.STATUS = 'ACTIVE' ) ) )) V130235680, (SELECT
organizationunitbasepeo.ORGANIZATION_ID,
organizationunittranslationp1.NAME,
organizationunitbasepeo.EFFECTIVE_START_DATE,
organizationunitbasepeo.EFFECTIVE_END_DATE,
organizationunitbasepeo.OBJECT_VERSION_NUMBER,
organizationunitbasepeo.LAST_UPDATE_DATE AS OU_LAST_UPDATE_DATE,
organizationunittranslationp1.ORGANIZATION_ID AS organization_id1,
organizationunittranslationp1.EFFECTIVE_START_DATE AS effective_start_date1,
organizationunittranslationp1.EFFECTIVE_END_DATE AS effective_end_date1,
organizationunittranslationp1.LANGUAGE,
organizationunittranslationp1.OBJECT_VERSION_NUMBER AS object_version_number1,
organizationunittranslationp1.LAST_UPDATE_DATE AS OUTL_LAST_UPDATE_DATE,
orginfopeo.ORG_INFORMATION2,
orginfopeo.EFFECTIVE_START_DATE AS effective_start_date2,
orginfopeo.EFFECTIVE_END_DATE AS effective_end_date2,
orginfopeo.LAST_UPDATE_DATE AS OI_LAST_UPDATE_DATE,
MaxEffectiveEndDatePEO.MAX_EFFECTIVE_END_DATE,
personNamePEO.DISPLAY_NAME,
personDetailPEO.PERSON_NUMBER,
treeVersionPEO.TREE_STRUCTURE_CODE,
treeVersionPEO.TREE_CODE,
treeVersionPEO.TREE_VERSION_ID,
treeVersionPEO.EFFECTIVE_START_DATE as tree_version_start_date,
treeVersionPEO.EFFECTIVE_END_DATE as tree_version_end_date,
treeVersionPEO.STATUS,
treeVersionPEO.LAST_UPDATE_DATE as tree_version_lud
FROM
HR_ALL_ORGANIZATION_UNITS_F organizationunitbasepeo,
HR_ORGANIZATION_UNITS_F_TL organizationunittranslationp1,
HR_ORG_UNIT_CLASSIFICATIONS_F orgunitclassificationdpeo,
HR_ORGANIZATION_INFORMATION_F orginfopeo,
(SELECT org2peo.ORGANIZATION_ID,
MAX(org2peo.EFFECTIVE_END_DATE) AS MAX_EFFECTIVE_END_DATE
FROM HR_ALL_ORGANIZATION_UNITS_F org2peo
GROUP BY org2peo.ORGANIZATION_ID
) MaxEffectiveEndDatePEO,
PER_PERSON_NAMES_F_V personNamePEO,
PER_ALL_PEOPLE_F personDetailPEO,
FND_TREE_VERSION treeVersionPEO
WHERE
organizationunitbasepeo.ORGANIZATION_ID =
organizationunittranslationp1.ORGANIZATION_ID
AND organizationunitbasepeo.ORGANIZATION_ID =
orgunitclassificationdpeo.ORGANIZATION_ID
AND organizationunittranslationp1.LANGUAGE = USERENV('LANG')
AND organizationunitbasepeo.EFFECTIVE_START_DATE BETWEEN
organizationunittranslationp1.EFFECTIVE_START_DATE AND
organizationunittranslationp1.EFFECTIVE_END_DATE
AND orgunitclassificationdpeo.CLASSIFICATION_CODE = 'DEPARTMENT'
orgunitclassificationdpeo.EFFECTIVE_START_DATE AND
orgunitclassificationdpeo.EFFECTIVE_END_DATE
AND organizationunitbasepeo.ORGANIZATION_ID = orginfopeo.ORGANIZATION_ID(+)
AND orginfopeo.ORG_INFORMATION_CONTEXT(+) = 'PER_ORG_MANAGER_INFO'
orginfopeo.EFFECTIVE_START_DATE(+) AND orginfopeo.EFFECTIVE_END_DATE(+)
AND orginfopeo.ORG_INFORMATION2 = personNamePEO.PERSON_ID(+)
AND TRUNC(SYSDATE) BETWEEN personNamePEO.EFFECTIVE_START_DATE(+) AND
personNamePEO.EFFECTIVE_END_DATE(+)
AND orginfopeo.ORG_INFORMATION2 = personDetailPEO.PERSON_ID(+)
AND TRUNC(SYSDATE) BETWEEN personDetailPEO.EFFECTIVE_START_DATE(+) AND
personDetailPEO.EFFECTIVE_END_DATE(+)
AND MaxEffectiveEndDatePEO.ORGANIZATION_ID =
organizationunitbasepeo.ORGANIZATION_ID
AND ( ( 1 = CASE WHEN (MaxEffectiveEndDatePEO.MAX_EFFECTIVE_END_DATE <=
treeVersionPEO.EFFECTIVE_END_DATE AND MaxEffectiveEndDatePEO.MAX_EFFECTIVE_END_DATE
between treeVersionPEO.EFFECTIVE_START_DATE AND treeVersionPEO.EFFECTIVE_END_DATE
AND organizationunitbasepeo.effective_end_date =
MaxEffectiveEndDatePEO.MAX_EFFECTIVE_END_DATE) THEN 1 ELSE 0 End )
OR ( 1 = CASE WHEN (MaxEffectiveEndDatePEO.MAX_EFFECTIVE_END_DATE >
treeVersionPEO.EFFECTIVE_END_DATE AND treeVersionPEO.EFFECTIVE_END_DATE between
organizationunitbasepeo.effective_start_date and
organizationunitbasepeo.effective_end_date) THEN 1 ELSE 0 End ) )
AND treeVersionPEO.TREE_STRUCTURE_CODE = 'PER_DEPT_TREE_STRUCTURE'
AND treeVersionPEO.STATUS = 'ACTIVE') V389562827, (SELECT
FROM
WHERE
OrganizationInformationPEO.ORG_INFORMATION_ID,
OrganizationInformationPEO.EFFECTIVE_START_DATE,
OrganizationInformationPEO.EFFECTIVE_END_DATE,
OrganizationInformationPEO.ORGANIZATION_ID,
OrganizationInformationPEO.ORG_INFORMATION2 FROM HR_ORGANIZATION_INFORMATION_F
OrganizationInformationPEO WHERE (( DATE'2021-09-01' BETWEEN
OrganizationInformationPEO.EFFECTIVE_START_DATE AND
OrganizationInformationPEO.EFFECTIVE_END_DATE)) AND
( ( (OrganizationInformationPEO.ORG_INFORMATION_CONTEXT =
'PER_ORG_MANAGER_INFO' ) ) )) V276876436, (SELECT PersonNamePEO.PERSON_NAME_ID,
PersonNamePEO.EFFECTIVE_START_DATE, PersonNamePEO.EFFECTIVE_END_DATE,
PersonNamePEO.PERSON_ID FROM PER_PERSON_NAMES_F_V PersonNamePEO WHERE ( DATE'2021-
09-01' BETWEEN PersonNamePEO.EFFECTIVE_START_DATE AND
PersonNamePEO.EFFECTIVE_END_DATE)) V65598971, (SELECT PositionPEO.POSITION_ID AS
POSITION_ID74, PositionPEO.EFFECTIVE_START_DATE AS EFFECTIVE_START_DATE82,
PositionPEO.EFFECTIVE_END_DATE AS EFFECTIVE_END_DATE90,
PositionTranslationPEO.POSITION_ID AS POSITION_ID378,
PositionTranslationPEO.EFFECTIVE_END_DATE AS EFFECTIVE_END_DATE388,
PositionTranslationPEO.EFFECTIVE_START_DATE AS EFFECTIVE_START_DATE398,
PositionTranslationPEO.LANGUAGE, PositionTranslationPEO.NAME FROM
HR_ALL_POSITIONS_F PositionPEO, HR_ALL_POSITIONS_F_TL PositionTranslationPEO WHERE
PositionPEO.POSITION_ID = PositionTranslationPEO.POSITION_ID(+) AND
(USERENV('LANG')) = PositionTranslationPEO.LANGUAGE(+) AND ( DATE'2021-09-01'
BETWEEN PositionPEO.EFFECTIVE_START_DATE AND PositionPEO.EFFECTIVE_END_DATE) AND
( DATE'2021-09-01' BETWEEN PositionTranslationPEO.EFFECTIVE_START_DATE(+) AND
PositionTranslationPEO.EFFECTIVE_END_DATE(+))) V207753497, (SELECT /*+ NO_PUSH_PRED
*/ PersonPEO.PERSON_ID AS PERSON_ID271, PersonDetailsPEO.PERSON_ID AS
PERSON_ID415, PersonDetailsPEO.EFFECTIVE_START_DATE AS
EFFECTIVE_START_DATE424, PersonDetailsPEO.EFFECTIVE_END_DATE AS
EFFECTIVE_END_DATE433, PersonDetailsPEO.PERSON_NUMBER,
PersonNamePEO.PERSON_NAME_ID AS PERSON_NAME_ID564,
PersonNamePEO.EFFECTIVE_START_DATE AS EFFECTIVE_START_DATE574,
PersonNamePEO.EFFECTIVE_END_DATE AS EFFECTIVE_END_DATE584,
PersonNamePEO.FIRST_NAME AS FIRST_NAME633, PersonNamePEO.LAST_NAME AS
LAST_NAME662, AssignmentPEO.ASSIGNMENT_ID AS ASSIGNMENT_ID776,
AssignmentPEO.EFFECTIVE_START_DATE AS EFFECTIVE_START_DATE785,
AssignmentPEO.EFFECTIVE_END_DATE AS EFFECTIVE_END_DATE794,
AssignmentPEO.EFFECTIVE_LATEST_CHANGE, AssignmentPEO.EFFECTIVE_SEQUENCE,
AssignmentPEO.ASSIGNMENT_NUMBER, AssignmentPEO.ASSIGNMENT_STATUS_TYPE,
AssignmentPEO.PERSON_ID AS PERSON_ID1245 FROM PER_PERSONS PersonPEO,
PER_ALL_PEOPLE_F PersonDetailsPEO, PER_PERSON_NAMES_F_V PersonNamePEO,
PER_ALL_ASSIGNMENTS_M AssignmentPEO WHERE (PersonPEO.PERSON_ID =
PersonDetailsPEO.PERSON_ID AND PersonPEO.PERSON_ID = PersonNamePEO.PERSON_ID AND
PersonPEO.PERSON_ID = AssignmentPEO.PERSON_ID AND ( DATE'2021-09-01' BETWEEN
PersonDetailsPEO.EFFECTIVE_START_DATE AND PersonDetailsPEO.EFFECTIVE_END_DATE) AND
( DATE'2021-09-01' BETWEEN PersonNamePEO.EFFECTIVE_START_DATE AND
PersonNamePEO.EFFECTIVE_END_DATE) AND ( DATE'2021-09-01' BETWEEN
AssignmentPEO.EFFECTIVE_START_DATE AND AssignmentPEO.EFFECTIVE_END_DATE)) AND
( ( (AssignmentPEO.EFFECTIVE_LATEST_CHANGE = 'Y' ) ) AND
(( ( (AssignmentPEO.ASSIGNMENT_TYPE = 'E' ) ) OR ( (AssignmentPEO.ASSIGNMENT_TYPE
= 'C' ) ) OR ( (AssignmentPEO.ASSIGNMENT_TYPE = 'N' ) ) OR
( (AssignmentPEO.ASSIGNMENT_TYPE = 'P' ) ) )) )) V216021127 WHERE
V487837372.EVALUATION_ID = V533469212.EVALUATION_ID(+) AND V533469212.MANAGER_ID =
V296907724.PERSON_ID557(+) AND V487837372.ERO_PERFORMANCE_RATING_ID =
V340935018.RLB_RATING_LEVEL_ID(+) AND V487837372.E_TMPL_PERIOD_ID =
V329124045.TMPL_PERIOD_ID(+) AND V487837372.E_ASSIGNMENT_ID =
V524217779.ASSIGNMENT_ID AND V487837372.E_WORKER_ID = V524217779.PERSON_ID AND
V524217779.ORGANIZATION_ID = V75077409.UNIT_EO_ORGANIZATION_ID(+) AND
V75077409.UNIT_EO_ORGANIZATION_ID = V130235680.DEP0_PK1_VALUE(+) AND
V130235680.DEP31_PK1_VALUE = V389562827.ORGANIZATION_ID(+) AND
V130235680.TREE_CODE2226 = V389562827.TREE_CODE(+) AND
V130235680.TREE_STRUCTURE_CODE2235 = V389562827.TREE_STRUCTURE_CODE(+) AND
V130235680.TREE_VERSION_ID2244 = V389562827.TREE_VERSION_ID(+) AND
V130235680.EFFECTIVE_START_DATE = V389562827.TREE_VERSION_START_DATE(+) AND
V130235680.EFFECTIVE_END_DATE = V389562827.TREE_VERSION_END_DATE(+) AND
V75077409.ORGANIZATION_ID = V276876436.ORGANIZATION_ID(+) AND
V276876436.ORG_INFORMATION2 = V65598971.PERSON_ID(+) AND V524217779.POSITION_ID =
V207753497.POSITION_ID74(+) AND V524217779.PERSON_ID = V216021127.PERSON_ID1245(+)
AND V524217779.ASSIGNMENT_ID = V216021127.ASSIGNMENT_ID776(+) AND
( ( ( (V487837372.E_END_DATE >= V524217779.EFFECTIVE_START_DATE ) ) AND
( (V487837372.E_END_DATE <= V524217779.EFFECTIVE_END_DATE ) ) ) AND
( ( (V216021127.PERSON_NUMBER = 'PZBT4092' ) ) AND
( (V216021127.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' ) ) AND ( ( (V340935018.LANGUAGE =
'US' ) ) OR ( (V340935018.LANGUAGE IS NULL ) ) ) AND
( ( (V524217779.EFFECTIVE_LATEST_CHANGE = 'Y' ) ) OR
( (V524217779.EFFECTIVE_LATEST_CHANGE IS NULL ) ) ) ) )) as T3870454
DetailFilter: not case when C352987552 = 'MANAGER' then C330634914 end is null
) as D1
DetailFilter: not case
when D1.c18 = 'MANAGER' then D1.c20 end is null [for database
3023:1418785:HCM_OLTP,57]
GroupBy: [ D1.c5,
D1.c7, D1.c8, D1.c9, D1.c10, D1.c11, D1.c12, D1.c13, D1.c14, D1.c15, D1.c17, case
when D1.c18 = 'MANAGER' then D1.c19 end , case when D1.c18 = 'MANAGER' then D1.c20
end , case when D1.c15 = D1.c22 then NULL else D1.c21 end ] [for database
3023:1418785:HCM_OLTP,57]
) as D1 LeftOuterJoin
(left drive) <<1436180479>> On D1.c17 = D2.c2
(
RqList <<1436180643>> [for
C61309499 as c1 [for
C36361321 as c2 [for
RqJoinNode
<<1436180650>> []
(SELECT
V483283710.MEANING AS C61309499, V483283710.LOOKUP_CODE AS C36361321,
V483283710.LOOKUP_TYPE AS C319008066 FROM HCM_LOOKUPS V483283710 WHERE
( ( (V483283710.LOOKUP_TYPE = 'HRA_PERF_DOC_STATUS' ) ) )) as T3870455
) as D2
) as D901
) as D901
InnerJoin <<1436180400>> On D901.c12 = D902.c8 and D901.c7
= D902.c3 and D901.c10 =NullsEqual D902.c6 and D901.c11 = D902.c7 and D901.c9
=NullsEqual D902.c5 and D901.c13 = D902.c12 and D901.c15 = D902.c17 and D901.c6
=NullsEqual D902.c2
(
3023:1418785:HCM_OLTP,57]
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57]
RqJoinNode <<1436180725>> []
(
3023:1418785:HCM_OLTP,57]
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57]
RqJoinNode <<1436180765>> []
(
RqList
count(distinct
case when D1.c20 = 'COMP' then D1.c21 else NULL end by [ D1.c5, D1.c12, D1.c10,
D1.c8, D1.c9, D1.c16, D1.c14, D1.c17, D1.c7, case when D1.c22 is null and D1.c23
is null then 'Y' else 'N' end , D1.c4] ) as c1 [for database
3023:1418785:HCM_OLTP,57],
count(distinct
D1.c21 by [ D1.c5, D1.c12, D1.c10, D1.c8, D1.c9, D1.c16, D1.c14, D1.c17, D1.c7,
case when D1.c22 is null and D1.c23 is null then 'Y' else 'N' end , D1.c4] ) as c2
[for database 3023:1418785:HCM_OLTP,57],
case when
D1.c17 = D1.c25 then NULL else D1.c24 end as c3 [for database
3023:1418785:HCM_OLTP,57],
D1.c4 as c4
D1.c5 as c5
D1.c6 as c6
D1.c7 as c7
D1.c8 as c8
D1.c9 as c9
D1.c10 as c10
D1.c12 as c12
D1.c14 as c14
case when
D1.c22 is null and D1.c23 is null then 'Y' else 'N' end as c15 [for database
3023:1418785:HCM_OLTP,57],
D1.c16 as c16
D1.c17 as c17
Child Nodes
RqJoinNode
<<1436180860>> []
(
RqList


Child
Nodes (RqJoinSpec): <<1436180899>> [for database 3023:1418785:HCM_OLTP,57]
RqJoinNode <<1436180900>> []
(SELECT V296907724.FULL_NAME AS C358965261, V329124045.TPTL_CUSTOMARY_NAME

AS C359378142, V207753497.NAME AS C272505829,
C50990987, V216021127.LAST_NAME662 AS C53347599,
V216021127.PERSON_NUMBER AS C458818009, V245781933.ES_STEP_COMPLETION_DATE
AS C343306837, V207753497.POSITION_ID74 AS C244241163,
V512212640.PRB_TASK_CODE AS C349902952, V62234047.ORGANIZATION_ID AS
C227489187, V245781933.ES_STEP_STATUS AS C447535174,
V245781933.EVAL_STEP_ID AS C270810120, V245781933.ES_EVAL_PARTICIPANT_ID AS
C465724726, V245781933.ES_PARENT_STEP_ID AS C475721557,
V62234047.NAME AS C276991226, V389562827.ORGANIZATION_ID AS C53212743,
V524217779.EFFECTIVE_SEQUENCE AS C234863912,
V216021127.ASSIGNMENT_STATUS_TYPE AS C227063946, V245781933.E_END_DATE AS
C386135389, V245781933.E_EVALUATION_ID AS PKA_EvaluationPEOEvaluationId0,
V512212640.PROCESS_TASK_ROLE_ID AS PKA_ProcessTaskRoleId0,
V512212640.BUSINESS_GROUP_ID AS PKA_BusinessGroupId0,
PKA_AssignmentPEOEffectiveSeq0 FROM (SELECT EvalStepPEO.EVAL_PARTICIPANT_ID AS
ES_EVAL_PARTICIPANT_ID, EvalStepPEO.EVAL_STEP_ID,
EvalStepPEO.PARENT_STEP_ID AS ES_PARENT_STEP_ID, EvalStepPEO.STEP_CODE AS
ES_STEP_CODE, EvalStepPEO.STEP_COMPLETION_DATE AS ES_STEP_COMPLETION_DATE,
EvalStepPEO.STEP_STATUS AS ES_STEP_STATUS, ((NVL( ( SELECT
EvaluationPEO.EVALUATION_ID AS E_EVALUATION_ID,
AS E_WORKER_ID, TemplateDefnBPEO.PROCESS_FLOW_ID AS TDB_PROCESS_FLOW_ID
FROM HRA_EVAL_STEPS EvalStepPEO, HRA_EVALUATIONS EvaluationPEO, HRA_TMPL_DEFNS_B
TemplateDefnBPEO WHERE (EvalStepPEO.EVALUATION_ID = EvaluationPEO.EVALUATION_ID AND
EvaluationPEO.TEMPLATE_DEFN_ID = TemplateDefnBPEO.TEMPLATE_DEFN_ID) AND ((1=1)))
ProcessTaskRoleBPEO.BUSINESS_GROUP_ID, ProcessTaskRoleBPEO.PROCESS_FLOW_ID
AS PRB_PROCESS_FLOW_ID, ProcessTaskRoleBPEO.PROCESS_TASK_ROLE_ID,
ProcessTaskRoleBPEO.TASK_CODE AS PRB_TASK_CODE FROM HRA_PF_TASK_ROLES_B
ProcessTaskRoleBPEO) V512212640, (SELECT TemplatePeriodBPEO.BUSINESS_GROUP_ID,
TemplatePeriodBPEO.TMPL_PERIOD_ID,
FROM
WHERE
FROM
WHERE
V245781933.E_EVALUATION_ID = V533469212.EVALUATION_ID(+) AND V533469212.MANAGER_ID
= V296907724.PERSON_ID557(+) AND V245781933.ES_STEP_CODE =
V512212640.PRB_TASK_CODE(+) AND V245781933.TDB_PROCESS_FLOW_ID =
V512212640.PRB_PROCESS_FLOW_ID(+) AND V245781933.E_TMPL_PERIOD_ID =
V524217779.ASSIGNMENT_ID(+) AND V245781933.E_WORKER_ID = V524217779.PERSON_ID(+)
AND V524217779.ORGANIZATION_ID = V75077409.UNIT_EO_ORGANIZATION_ID(+) AND
( ( ( (V245781933.E_END_DATE >= V524217779.EFFECTIVE_START_DATE(+) ) ) AND
( (V245781933.E_END_DATE <= V524217779.EFFECTIVE_END_DATE(+) ) ) ) AND
( ( (V216021127.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' ) ) AND
( (V512212640.PRB_TASK_CODE = 'FAPPR' ) ) AND ( (V216021127.PERSON_NUMBER =
'PZBT4092' ) ) AND ( ( (V524217779.EFFECTIVE_LATEST_CHANGE = 'Y' ) ) OR
DetailFilter: case when C465724726 is null and C475721557 is null then 'Y' else
'N' end = 'Y' [for database 3023:1418785:HCM_OLTP,57]
) as D1
GroupBy: [ D1.c4,
when D1.c17 = D1.c25 then NULL else D1.c24 end , case when D1.c22 is null and
D1.c23 is null then 'Y' else 'N' end ] [for database 3023:1418785:HCM_OLTP,57]
(
RqList <<1436180944>>
C61309499 as c1
C36361321 as c2
Child Nodes
RqJoinNode
<<1436180951>> []
(SELECT
( ( (V483283710.LOOKUP_TYPE = 'YES_NO' ) ) )) as T3870457
) as D2
InnerJoin (left drive)
<<1436180766>> On D1.c16 = D3.c2
(
RqList <<1436180958>> [for
C61309499 as c1 [for
C36361321 as c2 [for
RqJoinNode
<<1436180965>> []
(SELECT
( ( (V483283710.LOOKUP_TYPE = 'HRA_PROCESS_TASK_CODE' ) ) )) as T3870458
) as D3
) as D901
) as D902
) as D1
c15 asc NULLS LAST, c16 asc NULLS LAST, c14 asc NULLS LAST [for database
3023:1418785:HCM_OLTP,57]
]]
us.com] -------------------- Execution plan: [[
RqList <<1436180229>> [for database 3023:1418785:HCM_OLTP,57] /* FETCH FIRST 75001

ROWS ONLY */
Child Nodes (RqJoinSpec): <<1436180275>> [for database 3023:1418785:HCM_OLTP,57]
RqJoinNode <<1436180276>> []
(
RqBreakFilter
<<1436180279>>[2,3,4,6,7,8,9,10,11,12,13,15,17,18,19,23,24,25] [for database
3023:1418785:HCM_OLTP,57]
RqList <<1436180280>> [for database 3023:1418785:HCM_OLTP,57]
0 as c1 GB [for database 3023:1418785:HCM_OLTP,57],
Child Nodes (RqJoinSpec): <<1436180398>> [for database
3023:1418785:HCM_OLTP,57]
RqJoinNode <<1436180399>> []
(
3023:1418785:HCM_OLTP,57]
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57]
RqJoinNode <<1436180440>> []
(
3023:1418785:HCM_OLTP,57]
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57]
RqJoinNode <<1436180478>> []
(
RqList <<1436180482>>
avg(case when
D1.c18 = 'MANAGER' then D1.c19 end by [ D1.c8, D1.c13, D1.c11, D1.c12, D1.c17,
case when D1.c18 = 'MANAGER' then D1.c19 end , case when D1.c18 = 'MANAGER' then
D1.c20 end , D1.c5, D1.c14, D1.c15, D1.c10, D1.c7] ) as c1 [for database
3023:1418785:HCM_OLTP,57],
case when D1.c18 =
case when D1.c18 =
D1.c5 as c5 [for
case when D1.c15 =
D1.c22 then NULL else D1.c21 end as c6 [for database 3023:1418785:HCM_OLTP,57],
D1.c7 as c7 [for
D1.c8 as c8 [for
D1.c9 as c9 [for
D1.c10 as c10 [for
D1.c11 as c11 [for
D1.c12 as c12 [for
D1.c13 as c13 [for
D1.c14 as c14 [for
D1.c15 as c15 [for
D1.c17 as c17 [for
Child Nodes
RqJoinNode
<<1436180554>> []
(
RqList

Child Nodes
RqJoinNode <<1436180592>> []

C359378142, V207753497.NAME AS C272505829,
C50990987, V216021127.LAST_NAME662 AS C53347599,
FROM
WHERE
FROM
WHERE
DetailFilter: not case when C352987552 = 'MANAGER' then C330634914 end is null
) as D1
DetailFilter: not case
when D1.c18 = 'MANAGER' then D1.c20 end is null [for database
3023:1418785:HCM_OLTP,57]
GroupBy: [ D1.c5,
when D1.c18 = 'MANAGER' then D1.c19 end , case when D1.c18 = 'MANAGER' then D1.c20
end , case when D1.c15 = D1.c22 then NULL else D1.c21 end ] [for database
3023:1418785:HCM_OLTP,57]
(
RqList <<1436180643>> [for
C61309499 as c1 [for
C36361321 as c2 [for
RqJoinNode
<<1436180650>> []
(SELECT
( ( (V483283710.LOOKUP_TYPE = 'HRA_PERF_DOC_STATUS' ) ) )) as T3870455
) as D2
) as D901
) as D901
InnerJoin <<1436180400>> On D901.c12 = D902.c8 and D901.c7
= D902.c3 and D901.c10 =NullsEqual D902.c6 and D901.c11 = D902.c7 and D901.c9
=NullsEqual D902.c5 and D901.c13 = D902.c12 and D901.c15 = D902.c17 and D901.c6
=NullsEqual D902.c2
(
3023:1418785:HCM_OLTP,57]
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57]
RqJoinNode <<1436180725>> []
(
3023:1418785:HCM_OLTP,57]
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57],
3023:1418785:HCM_OLTP,57]
RqJoinNode <<1436180765>> []
(
RqList
count(distinct
case when D1.c20 = 'COMP' then D1.c21 else NULL end by [ D1.c5, D1.c12, D1.c10,
D1.c8, D1.c9, D1.c16, D1.c14, D1.c17, D1.c7, case when D1.c22 is null and D1.c23
is null then 'Y' else 'N' end , D1.c4] ) as c1 [for database
3023:1418785:HCM_OLTP,57],
count(distinct
D1.c21 by [ D1.c5, D1.c12, D1.c10, D1.c8, D1.c9, D1.c16, D1.c14, D1.c17, D1.c7,
case when D1.c22 is null and D1.c23 is null then 'Y' else 'N' end , D1.c4] ) as c2
case when
D1.c17 = D1.c25 then NULL else D1.c24 end as c3 [for database
3023:1418785:HCM_OLTP,57],
D1.c4 as c4
D1.c5 as c5
D1.c6 as c6
D1.c7 as c7
D1.c8 as c8
D1.c9 as c9
D1.c10 as c10
D1.c12 as c12
D1.c14 as c14
case when
D1.c22 is null and D1.c23 is null then 'Y' else 'N' end as c15 [for database
3023:1418785:HCM_OLTP,57],
D1.c16 as c16
D1.c17 as c17
Child Nodes
RqJoinNode
<<1436180860>> []
(
RqList

Child
Nodes (RqJoinSpec): <<1436180899>> [for database 3023:1418785:HCM_OLTP,57]
RqJoinNode <<1436180900>> []
(SELECT V296907724.FULL_NAME AS C358965261, V329124045.TPTL_CUSTOMARY_NAME

AS C359378142, V207753497.NAME AS C272505829,
C50990987, V216021127.LAST_NAME662 AS C53347599,
V216021127.PERSON_NUMBER AS C458818009, V245781933.ES_STEP_COMPLETION_DATE
AS C343306837, V207753497.POSITION_ID74 AS C244241163,
V512212640.PRB_TASK_CODE AS C349902952, V62234047.ORGANIZATION_ID AS
C227489187, V245781933.ES_STEP_STATUS AS C447535174,
V245781933.EVAL_STEP_ID AS C270810120, V245781933.ES_EVAL_PARTICIPANT_ID AS
C465724726, V245781933.ES_PARENT_STEP_ID AS C475721557,
V62234047.NAME AS C276991226, V389562827.ORGANIZATION_ID AS C53212743,
FROM
WHERE
FROM
WHERE
DetailFilter: case when C465724726 is null and C475721557 is null then 'Y' else
'N' end = 'Y' [for database 3023:1418785:HCM_OLTP,57]
) as D1
GroupBy: [ D1.c4,
when D1.c17 = D1.c25 then NULL else D1.c24 end , case when D1.c22 is null and
D1.c23 is null then 'Y' else 'N' end ] [for database 3023:1418785:HCM_OLTP,57]
(
RqList <<1436180944>>
C61309499 as c1
C36361321 as c2
Child Nodes
RqJoinNode
<<1436180951>> []
(SELECT
( ( (V483283710.LOOKUP_TYPE = 'YES_NO' ) ) )) as T3870457
) as D2
InnerJoin (left drive)
<<1436180766>> On D1.c16 = D3.c2
(
RqList <<1436180958>> [for
C61309499 as c1 [for
C36361321 as c2 [for
RqJoinNode
<<1436180965>> []
(SELECT
( ( (V483283710.LOOKUP_TYPE = 'HRA_PROCESS_TASK_CODE' ) ) )) as T3870458
) as D3
) as D901
) as D902
) as D1
c15 asc NULLS LAST, c16 asc NULLS LAST, c14 asc NULLS LAST [for database
3023:1418785:HCM_OLTP,57]
]]
us.com] -------------------- Sending query to database named HCM_OLTP (id:
<<1436180229>>), connection pool named Connection Pool, logical request hash
c15dae01, physical request hash 68dbe3b7: [[
WITH
SAWITH0 AS (select T3870454.C469780226 as c5,
T3870454.C358965261 as c7,
T3870454.C359378142 as c8,
T3870454.C272505829 as c9,
T3870454.C399099852 as c10,
T3870454.C50990987 as c11,
T3870454.C53347599 as c12,
T3870454.C458818009 as c13,
T3870454.C244241163 as c14,
T3870454.C227489187 as c15,
T3870454.C262158206 as c17,
T3870454.C352987552 as c18,
T3870454.C343919161 as c19,
T3870454.C330634914 as c20,
T3870454.C276991226 as c21,
T3870454.C53212743 as c22
from
C359378142, V207753497.NAME AS C272505829,
C50990987, V216021127.LAST_NAME662 AS C53347599,
PersonNamePEO.EFFECTIVE_END_DATE,
PersonNamePEO.FULL_NAME, PersonDetailsPEO.PERSON_ID AS PERSON_ID557
FROM PER_PERSON_NAMES_F_V PersonNamePEO, PER_ALL_PEOPLE_F PersonDetailsPEO WHERE
PersonNamePEO.PERSON_ID = PersonDetailsPEO.PERSON_ID(+) AND ( DATE'2021-09-01'
BETWEEN PersonNamePEO.EFFECTIVE_START_DATE AND PersonNamePEO.EFFECTIVE_END_DATE)
AND ( DATE'2021-09-01' BETWEEN PersonDetailsPEO.EFFECTIVE_START_DATE(+) AND
FROM
WHERE
FROM
WHERE
( (V524217779.EFFECTIVE_LATEST_CHANGE IS NULL ) ) ) ) )) T3870454
where ( case when T3870454.C352987552 = 'MANAGER' then T3870454.C330634914 end
is not null ) ),
SAWITH1 AS (select avg(case when D1.c18 = 'MANAGER' then D1.c19 end ) as c1,
case when D1.c18 = 'MANAGER' then D1.c20 end as c3,
case when D1.c18 = 'MANAGER' then D1.c19 end as c4,
D1.c5 as c5,
case when D1.c15 = D1.c22 then NULL else D1.c21 end as c6,
D1.c7 as c7,
D1.c8 as c8,
D1.c9 as c9,
D1.c10 as c10,
D1.c11 as c11,
D1.c12 as c12,
D1.c13 as c13,
D1.c14 as c14,
D1.c15 as c15,
D1.c17 as c17
from
SAWITH0 D1
where ( case when D1.c18 = 'MANAGER' then D1.c20 end is not null )
group by D1.c5, D1.c7, D1.c8, D1.c9, D1.c10, D1.c11, D1.c12, D1.c13, D1.c14,
D1.c15, D1.c17, case when D1.c18 = 'MANAGER' then D1.c19 end , case when D1.c18 =
'MANAGER' then D1.c20 end , case when D1.c15 = D1.c22 then NULL else D1.c21 end ),
T3870455.C36361321 as c2
from
(SELECT V483283710.MEANING AS C61309499, V483283710.LOOKUP_CODE AS
C36361321, V483283710.LOOKUP_TYPE AS C319008066 FROM HCM_LOOKUPS V483283710
WHERE ( ( (V483283710.LOOKUP_TYPE = 'HRA_PERF_DOC_STATUS' ) ) )) T3870455),
SAWITH3 AS (select D1.c1 as c1,
D2.c1 as c2,
D1.c17 as c3,
D1.c3 as c4,
D1.c4 as c5,
D1.c5 as c6,
D1.c6 as c7,
D1.c7 as c8,
D1.c8 as c9,
D1.c9 as c10,
D1.c10 as c11,
D1.c11 as c12,
D1.c12 as c13,
D1.c13 as c14,
D1.c14 as c15,
D1.c15 as c16
from
SAWITH1 D1 left outer join SAWITH2 D2 On D1.c17 = D2.c2),

D901.c5 as c2,
D901.c4 as c3,
nvl(D901.c2 , D901.c3) as c4,
D901.c6 as c5,
D901.c8 as c6,
D901.c9 as c7,
D901.c10 as c8,
D901.c11 as c9,
D901.c12 as c10,
D901.c13 as c11,
D901.c14 as c12,
D901.c15 as c13,
D901.c1 as c14,
D901.c16 as c15,
D901.c3 as c16
from
SAWITH3 D901),
T3870456.C359378142 as c5,
T3870456.C272505829 as c6,
T3870456.C399099852 as c7,
T3870456.C50990987 as c8,
T3870456.C53347599 as c9,
T3870456.C458818009 as c10,
T3870456.C343306837 as c12,
T3870456.C244241163 as c14,
T3870456.C349902952 as c16,
T3870456.C227489187 as c17,
T3870456.C447535174 as c20,
T3870456.C270810120 as c21,
T3870456.C465724726 as c22,
T3870456.C475721557 as c23,
T3870456.C276991226 as c24,
T3870456.C53212743 as c25
from
(SELECT V296907724.FULL_NAME AS C358965261,
V329124045.TPTL_CUSTOMARY_NAME AS C359378142, V207753497.NAME AS
C272505829, V216021127.ASSIGNMENT_NUMBER AS C399099852,
V216021127.FIRST_NAME633 AS C50990987, V216021127.LAST_NAME662 AS
C53347599, V216021127.PERSON_NUMBER AS C458818009,
V245781933.ES_STEP_COMPLETION_DATE AS C343306837, V207753497.POSITION_ID74
AS C244241163, V512212640.PRB_TASK_CODE AS C349902952,
V62234047.ORGANIZATION_ID AS C227489187, V245781933.ES_STEP_STATUS AS
C447535174, V245781933.EVAL_STEP_ID AS C270810120,
V245781933.ES_EVAL_PARTICIPANT_ID AS C465724726,
V245781933.ES_PARENT_STEP_ID AS C475721557, V62234047.NAME AS C276991226,
V389562827.ORGANIZATION_ID AS C53212743, V533469212.EVALUATION_ID AS
C255742563, V130235680.CF_TREE_NODE_ID AS C88025526,
V130235680.ENTERPRISE_ID AS C81202873, V130235680.TREE_CODE AS C293063572,
V130235680.TREE_STRUCTURE_CODE AS C255476159, V130235680.TREE_VERSION_ID AS
C370085791, V75077409.ORG_UNIT_CLASSIFICATION_ID AS C499688583,
TemplatePeriodTranslationPEO.BUSINESS_GROUP_ID AS TPTL_BUSINESS_GROUP_ID
FROM HRA_TMPL_PERIODS_B TemplatePeriodBPEO, HRA_TMPL_PERIODS_TL
FROM
WHERE
FROM
WHERE
( (V524217779.EFFECTIVE_LATEST_CHANGE IS NULL ) ) ) ) )) T3870456
where ( case when T3870456.C465724726 is null and T3870456.C475721557 is null
then 'Y' else 'N' end = 'Y' ) ),
SAWITH6 AS (select count(distinct case when D1.c20 = 'COMP' then D1.c21 else NULL
end ) as c1,
count(distinct D1.c21) as c2,
case when D1.c17 = D1.c25 then NULL else D1.c24 end as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c8 as c8,
D1.c9 as c9,
D1.c10 as c10,
D1.c12 as c12,
D1.c14 as c14,
case when D1.c22 is null and D1.c23 is null then 'Y' else 'N' end as c15,
D1.c16 as c16,
D1.c17 as c17
from
SAWITH5 D1
group by D1.c4, D1.c5, D1.c6, D1.c7, D1.c8, D1.c9, D1.c10, D1.c12, D1.c14, D1.c16,
D1.c17, case when D1.c17 = D1.c25 then NULL else D1.c24 end , case when D1.c22 is
null and D1.c23 is null then 'Y' else 'N' end ),
T3870457.C36361321 as c2
from
WHERE ( ( (V483283710.LOOKUP_TYPE = 'YES_NO' ) ) )) T3870457),
T3870458.C36361321 as c2
from
WHERE ( ( (V483283710.LOOKUP_TYPE = 'HRA_PROCESS_TASK_CODE' ) ) )) T3870458),
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c8 as c8,
D1.c9 as c9,
D1.c10 as c10,
D2.c1 as c11,
D1.c15 as c12,
D1.c12 as c13,
D3.c1 as c14,
D1.c14 as c15,
D1.c16 as c16,
D1.c17 as c17
from
(
SAWITH6 D1 left outer join SAWITH7 D2 On D1.c15 = D2.c2) inner join

SAWITH8 D3 On D1.c16 = D3.c2),
D901.c4 as c2,
D901.c5 as c3,
D901.c6 as c4,
D901.c7 as c5,
D901.c8 as c6,
D901.c9 as c7,
D901.c10 as c8,
nvl(D901.c11 , D901.c12) as c9,
D901.c13 as c10,
D901.c14 as c11,
D901.c15 as c12,
D901.c12 as c13,
D901.c16 as c14,
D901.c1 as c15,
D901.c2 as c16,
D901.c17 as c17
from
SAWITH9 D901),
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c8 as c8,
D1.c9 as c9,
D1.c10 as c10,
D1.c11 as c11,
D1.c12 as c12,
D1.c13 as c13,
D1.c14 as c14,
D1.c15 as c15,
D1.c16 as c16,
D1.c17 as c17,
D1.c18 as c18,
D1.c19 as c19,
D1.c20 as c20,
D1.c21 as c21,
D1.c22 as c22,
D1.c23 as c23,
D1.c24 as c24,
D1.c25 as c25
from
(select 0 as c1,
case when D901.c1 is not null then D901.c1 when D902.c1 is not null
then D902.c1 end as c2,
D901.c2 as c3,
D901.c3 as c4,
D901.c4 as c5,
D901.c5 as c6,
null then D902.c6 end as c11,
D902.c9 as c14,
D902.c10 as c15,
D902.c11 as c16,
D902.c13 as c18,
D902.c14 as c19,
D901.c14 as c20,
D902.c15 as c21,
D902.c16 as c22,
D901.c16 as c25,
ROW_NUMBER() OVER (PARTITION BY D901.c2, D901.c3, D901.c5, D901.c16,
D902.c10, D902.c13, D902.c14, case when D901.c1 is not null then D901.c1 when
D902.c1 is not null then D902.c1 end , case when D901.c6 is not null then D901.c6
when D902.c2 is not null then D902.c2 end , case when D901.c7 is not null then
D901.c7 when D902.c3 is not null then D902.c3 end , case when D901.c8 is not null
then D901.c8 when D902.c4 is not null then D902.c4 end , case when D901.c9 is not
null then D901.c9 when D902.c5 is not null then D902.c5 end , case when D901.c10
is not null then D901.c10 when D902.c6 is not null then D902.c6 end , case when
D901.c11 is not null then D901.c11 when D902.c7 is not null then D902.c7 end , case
when D901.c12 is not null then D901.c12 when D902.c8 is not null then D902.c8 end ,
case when D901.c13 is not null then D901.c13 when D902.c12 is not null then
D902.c12 end , case when D901.c15 is not null then D901.c15 when D902.c17 is not
null then D902.c17 end ORDER BY D901.c2 ASC, D901.c3 ASC, D901.c5 ASC, D901.c16
ASC, D902.c10 ASC, D902.c13 ASC, D902.c14 ASC, case when D901.c1 is not null then
D901.c1 when D902.c1 is not null then D902.c1 end ASC, case when D901.c6 is not
null then D901.c6 when D902.c2 is not null then D902.c2 end ASC, case when
D901.c7 is not null then D901.c7 when D902.c3 is not null then D902.c3 end ASC,
case when D901.c8 is not null then D901.c8 when D902.c4 is not null then D902.c4
end ASC, case when D901.c9 is not null then D901.c9 when D902.c5 is not null then
D902.c5 end ASC, case when D901.c10 is not null then D901.c10 when D902.c6 is not
null then D902.c6 end ASC, case when D901.c11 is not null then D901.c11 when
D902.c7 is not null then D902.c7 end ASC, case when D901.c12 is not null then
D901.c12 when D902.c8 is not null then D902.c8 end ASC, case when D901.c13 is not
null then D901.c13 when D902.c12 is not null then D902.c12 end ASC, case when
D901.c15 is not null then D901.c15 when D902.c17 is not null then D902.c17 end
ASC) as c26
from
SAWITH4 D901 inner join

SAWITH10 D902 On D901.c12 = D902.c8 and D901.c7 = D902.c3 and
SYS_OP_MAP_NONNULL(D901.c10) = SYS_OP_MAP_NONNULL(D902.c6) and D901.c11 = D902.c7
and SYS_OP_MAP_NONNULL(D901.c9) = SYS_OP_MAP_NONNULL(D902.c5) and D901.c13 =
D902.c12 and D901.c15 = D902.c17 and SYS_OP_MAP_NONNULL(D901.c6) =
SYS_OP_MAP_NONNULL(D902.c2)
) D1
where ( D1.c26 = 1 ) )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, D1.c6 as
c6, D1.c7 as c7, D1.c8 as c8, D1.c9 as c9, D1.c10 as c10, D1.c11 as c11, D1.c12 as
c12, D1.c13 as c13, D1.c14 as c14, D1.c15 as c15, D1.c16 as c16, D1.c17 as c17,
D1.c18 as c18, D1.c19 as c19, D1.c20 as c20, D1.c21 as c21, D1.c22 as c22 from
( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c8 as c8,
D1.c9 as c9,
D1.c10 as c10,
D1.c11 as c11,
D1.c12 as c12,
D1.c13 as c13,
D1.c14 as c14,
D1.c15 as c15,
D1.c16 as c16,
D1.c17 as c17,
D1.c18 as c18,
D1.c19 as c19,
D1.c20 as c20,
D1.c21 as c21,
D1.c22 as c22
from
SAWITH11 D1
order by c1, c8, c3, c13, c10, c11, c12, c9, c7, c2, c4, c6, c5, c15, c16, c14 ) D1
where rownum <= 75001
]]
005milzCx5o7EC5pvOc9yX0001mg0003ff,0:4:1:1:5:1] [tid: 82aa8700] [messageid: USER-
18] [requestid: 642800fe] [sessionid: 64280000] [username: cedric.mabou@catalyst-
<<1436180229>> pre query 0), connection pool named Connection Pool, logical request
hash c15dae01, physical request hash 902b3566: [[
BEGIN fnd_session_mgmt.attach_session('CAADDFA783F727FEE0531964410ABB54'); END;
]]
<<1436180229>> post query 0), connection pool named Connection Pool, logical
request hash c15dae01, physical request hash d90563e6: [[
BEGIN fnd_session_mgmt.detach_session; END;
]]
us.com] -------------------- Execution Node for logical request hash c15dae01 :
<<1436180229>>, Close Row Count = 0, Row Width = 14776 bytes
005milzCx5o7EC5pvOc9yX0001mg0003ff,0:4:1:2] [tid: b0768700] [messageid: USER-20]
<<1436180229>> DbGateway Exchange: HCM_OLTP.Connection Pool, Close Row Count = 0,
Row Width = 14776 bytes
us.com] -------------------- Query Status: Successful Completion
us.com] -------------------- Rows 0, bytes 0 retrieved from database query id:
<<1436180229>>
us.com] -------------------- Physical query response time 5.867 (seconds), id
<<1436180229>>
us.com] -------------------- Physical Query Summary Stats: Number of physical
queries 1, Cumulative time 5.867, DB-connect time 0.000 (seconds)
us.com] -------------------- Rows returned to Client 0
us.com] -------------------- Logical Query Summary Stats: Elapsed time 5.924, Total
time in BI Server 5.923, Response time 5.924, Compilation time 0.052 (seconds),
Logical hash c15dae01
<<1436180229>> DbGateway Exchange: HCM_OLTP.Connection Pool, Close Row Count = 0,
Row Width = 14776 bytes

Nqquery New

Uploaded by

Copyright:

Available Formats

Nqquery New

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Nqquery New

Uploaded by

Copyright:

Available Formats

[2021-09-01T10:26:18.

781+00:00] [OracleBIServerComponent] [TRACE:7] [] [] [ecid:

C469780226 as c5 [for database 3023:1418785:HCM_OLTP,57],

C358965261 as c7 [for database 3023:1418785:HCM_OLTP,57],

C359378142 as c8 [for database 3023:1418785:HCM_OLTP,57],

C272505829 as c9 [for database 3023:1418785:HCM_OLTP,57],

C399099852 as c10 [for database 3023:1418785:HCM_OLTP,57],

C50990987 as c11 [for database 3023:1418785:HCM_OLTP,57],

C458818009 as c13 [for database 3023:1418785:HCM_OLTP,57],

C244241163 as c14 [for database 3023:1418785:HCM_OLTP,57],

C227489187 as c15 [for database 3023:1418785:HCM_OLTP,57],

C262158206 as c17 [for database 3023:1418785:HCM_OLTP,57],

C352987552 as c18 [for database 3023:1418785:HCM_OLTP,57],

C343919161 as c19 [for database 3023:1418785:HCM_OLTP,57],

C330634914 as c20 [for database 3023:1418785:HCM_OLTP,57],

C276991226 as c21 [for database 3023:1418785:HCM_OLTP,57],

C53212743 as c22 [for database 3023:1418785:HCM_OLTP,57]

(SELECT V340935018.RLT_RATING_SHORT_DESCR AS C469780226,

C358965261 as c4 [for database 3023:1418785:HCM_OLTP,57],

C359378142 as c5 [for database 3023:1418785:HCM_OLTP,57],

C272505829 as c6 [for database 3023:1418785:HCM_OLTP,57],

C399099852 as c7 [for database 3023:1418785:HCM_OLTP,57],

C50990987 as c8 [for database 3023:1418785:HCM_OLTP,57],

C53347599 as c9 [for database 3023:1418785:HCM_OLTP,57],

C458818009 as c10 [for database 3023:1418785:HCM_OLTP,57],

C343306837 as c12 [for database 3023:1418785:HCM_OLTP,57],

C244241163 as c14 [for database 3023:1418785:HCM_OLTP,57],

C349902952 as c16 [for database 3023:1418785:HCM_OLTP,57],

C227489187 as c17 [for database 3023:1418785:HCM_OLTP,57],

C447535174 as c20 [for database 3023:1418785:HCM_OLTP,57],

C270810120 as c21 [for database 3023:1418785:HCM_OLTP,57],

C475721557 as c23 [for database 3023:1418785:HCM_OLTP,57],

C276991226 as c24 [for database 3023:1418785:HCM_OLTP,57],

C53212743 as c25 [for database 3023:1418785:HCM_OLTP,57]

(SELECT V296907724.FULL_NAME AS C358965261, V329124045.TPTL_CUSTOMARY_NAME

RqList <<1436180229>> [for database 3023:1418785:HCM_OLTP,57] /* FETCH FIRST 75001

C469780226 as c5 [for database 3023:1418785:HCM_OLTP,57],

C358965261 as c7 [for database 3023:1418785:HCM_OLTP,57],

C359378142 as c8 [for database 3023:1418785:HCM_OLTP,57],

C272505829 as c9 [for database 3023:1418785:HCM_OLTP,57],

C399099852 as c10 [for database 3023:1418785:HCM_OLTP,57],

C50990987 as c11 [for database 3023:1418785:HCM_OLTP,57],

C53347599 as c12 [for database 3023:1418785:HCM_OLTP,57],

C458818009 as c13 [for database 3023:1418785:HCM_OLTP,57],

C244241163 as c14 [for database 3023:1418785:HCM_OLTP,57],

C227489187 as c15 [for database 3023:1418785:HCM_OLTP,57],

C262158206 as c17 [for database 3023:1418785:HCM_OLTP,57],

C352987552 as c18 [for database 3023:1418785:HCM_OLTP,57],

C343919161 as c19 [for database 3023:1418785:HCM_OLTP,57],

C330634914 as c20 [for database 3023:1418785:HCM_OLTP,57],

C276991226 as c21 [for database 3023:1418785:HCM_OLTP,57],

C53212743 as c22 [for database 3023:1418785:HCM_OLTP,57]

(SELECT V340935018.RLT_RATING_SHORT_DESCR AS C469780226,

C358965261 as c4 [for database 3023:1418785:HCM_OLTP,57],

C359378142 as c5 [for database 3023:1418785:HCM_OLTP,57],

C272505829 as c6 [for database 3023:1418785:HCM_OLTP,57],

C399099852 as c7 [for database 3023:1418785:HCM_OLTP,57],

C50990987 as c8 [for database 3023:1418785:HCM_OLTP,57],

C53347599 as c9 [for database 3023:1418785:HCM_OLTP,57],

C458818009 as c10 [for database 3023:1418785:HCM_OLTP,57],