The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_default_last_update DATE := to_date('01-01-2000','DD-MM-YYYY');
INSERT INTO HRI.HRI_DEBUG
(Text1
,Text2
,time_date)
values
(Text1
,Text2
,sysdate);
SELECT ghn.entity_id top_entity_id
FROM per_gen_hierarchy_nodes ghn
WHERE ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
AND ghn.parent_hierarchy_node_id is null;
SELECT ghr.business_group_id
, ghr.type
, ghr.hierarchy_id
, ghv.hierarchy_version_id
, ghv.date_from
, ghv.date_to
, ghv.version_number
, ghr.name
FROM per_gen_hierarchy ghr
, per_gen_hierarchy_versions ghv
WHERE ghr.hierarchy_id = ghv.hierarchy_id
AND ghr.business_group_id = ghv.business_group_id
AND ghr.type = 'FEDREP'; -- bug 2492438
SELECT ghn.business_group_id business_group_id
, ghn.hierarchy_node_id hierarchy_node_id
, ghn.entity_id entity_id
, LEVEL entity_level
, ghn.node_type node_type
FROM per_gen_hierarchy_nodes ghn
WHERE ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
START WITH ghn.entity_id = cp_top_entity_id
CONNECT BY ghn.parent_hierarchy_node_id = PRIOR ghn.hierarchy_node_id
-- Note: this looks excessive but making sure both
-- the prior record and the current record
-- are both using the specified org struct version
AND PRIOR ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
AND ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
;
SELECT ghn.business_group_id business_group_id
, ghn.hierarchy_node_id hierarchy_node_id
, ghn.entity_id entity_id
, LEVEL -1 entity_level
, ghn.node_type node_type
FROM per_gen_hierarchy_nodes ghn
WHERE ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
START WITH ghn.entity_id = cp_top_entity_id
CONNECT BY ghn.parent_hierarchy_node_id = PRIOR ghn.hierarchy_node_id
-- Note: this looks excessive but making sure both
-- the prior record and the current record
-- are both using the specified org struct version
AND PRIOR ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
AND ghn.hierarchy_version_id = cp_gen_hierarchy_version_id
;
BEGIN -- Handle Gen Children tree walk exceptions and Insert exceptions
-- 3. Loop through and insert a row for each Parnet Child Entity
-- combination
l_this_parent_entity := l_gen_parent_rec.entity_id;
INSERT INTO
HRI_GEN_HRCHY_SUMMARY
(hierarchy_id
,hierarchy_version_id
,business_group_id
,hierarchy_node_id
,entity_id
,entity_level
,node_type
,sub_entity_bg_id
,sub_hierarchy_node_id
,sub_entity_id
,sub_entity_level
,sub_node_type
/* -- This done by trigger HRI_GEN_HRCHY_SUMMARY_WHO
,created_by
,creation_date
,last_updated_by
,last_update_login
,last_update_date/**/
)
VALUES
(l_gen_struct_ver_rec.hierarchy_id
,l_gen_struct_ver_rec.hierarchy_version_id
,l_gen_parent_rec.business_group_id
,l_gen_parent_rec.hierarchy_node_id
,l_gen_parent_rec.entity_id
,l_gen_parent_rec.entity_level
,l_gen_parent_rec.node_type
,l_entity_child_rec.business_group_id
,l_entity_child_rec.hierarchy_node_id
,l_entity_child_rec.entity_id
,l_gen_parent_rec.entity_level + l_entity_child_rec.entity_level
,l_entity_child_rec.node_type
/* -- This done by trigger HRI_GEN_HRCHY_SUMMARY_WHO
,-1 --created_by
,sysdate
,-1 --last_updated_by
,-1 --last_update_login
,sysdate --last_update_date/**/
);
output( 'Child or Insert Exception ');
TYPE l_sup_updates_tabtype IS TABLE OF DATE INDEX BY BINARY_INTEGER;
l_sup_updates_tab l_sup_updates_tabtype;
SELECT asg.person_id
, asg.business_group_id
, 0 supervisor_hier_level
, NVL(asg.last_update_date, g_default_last_update)
last_ptntl_change_date
FROM per_all_assignments_f asg
WHERE DECODE(cp_primary_ass_only, 'Y', asg.primary_flag, 1)
= DECODE(cp_primary_ass_only, 'Y', 'Y', 1) -- primary assignments only
AND asg.supervisor_id IS NULL -- not supervised themselves.
AND asg.assignment_type = 'E'
AND cp_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.person_id IN
(SELECT DISTINCT asg.supervisor_id
FROM per_all_assignments_f asg
WHERE DECODE(cp_primary_ass_only, 'Y', asg.primary_flag, 1)
= DECODE(cp_primary_ass_only, 'Y', 'Y', 1) -- primary assignments only
AND asg.supervisor_id IS NOT NULL
AND asg.assignment_type = 'E'
AND cp_date BETWEEN
asg.effective_start_date AND asg.effective_end_date
) --
AND asg.business_group_id
= NVL(cp_business_group_id, asg.business_group_id);
SELECT hier.business_group_id
, hier.person_id
, hier.assignment_id
, hier.primary_flag
, LEVEL-1 supervisor_level
, hier.assignment_id supv_asg_id
, NVL(hier.last_update_date, g_default_last_update)
last_ptntl_change_date
FROM per_all_assignments_f hier
WHERE cp_date BETWEEN
hier.effective_start_date AND hier.effective_end_date
AND hier.assignment_type = 'E'
AND DECODE(cp_primary_ass_only, 'Y', hier.primary_flag, 1)
= DECODE(cp_primary_ass_only,'Y', 'Y', 1) -- primary assignments only
START WITH hier.person_id = cp_supervisor_id
CONNECT BY hier.supervisor_id = PRIOR hier.person_id
AND hier.assignment_type = 'E'
AND cp_date BETWEEN
PRIOR hier.effective_start_date
AND PRIOR hier.effective_end_date
AND DECODE(cp_primary_ass_only, 'Y', hier.primary_flag, 1)
= DECODE(cp_primary_ass_only,'Y', 'Y', 1); -- primary assignments only
SELECT hier.business_group_id
, hier.person_id
, hier.assignment_id
, hier.primary_flag
, LEVEL-1 subordinate_level
, NVL(hier.last_update_date, g_default_last_update)
last_ptntl_change_date
FROM per_all_assignments_f hier
WHERE cp_date BETWEEN
hier.effective_start_date AND hier.effective_end_date
AND hier.assignment_type = 'E'
AND DECODE(cp_primary_ass_only, 'Y', hier.primary_flag, 1)
= DECODE(cp_primary_ass_only,'Y', 'Y', 1) -- primary assignments only
START WITH hier.person_id = cp_supervisor_id
CONNECT BY hier.supervisor_id = PRIOR hier.person_id
AND hier.assignment_type = 'E'
AND cp_date BETWEEN
PRIOR hier.effective_start_date AND PRIOR hier.effective_end_date
AND DECODE(cp_primary_ass_only, 'Y', hier.primary_flag, 1)
= DECODE(cp_primary_ass_only,'Y', 'Y', 1); -- primary assignments only
l_sup_updates_tab(cur_people_rec.person_id) := cur_people_rec.last_ptntl_change_date;
IF (l_sup_updates_tab(cur_supervisors_rec.person_id) >
cur_reports_rec.last_ptntl_change_date) THEN
l_sup_updates_tab(cur_reports_rec.person_id) :=
l_sup_updates_tab(cur_supervisors_rec.person_id);
l_sup_updates_tab(cur_reports_rec.person_id) :=
cur_reports_rec.last_ptntl_change_date;
INSERT INTO hri_supv_hrchy_summary(
supv_business_group_id
, supv_person_id
, supv_assignment_id
, supv_level
, supv_last_ptntl_change
, sub_business_group_id
, sub_person_id
, sub_assignment_id
, sub_primary_asg_flag
, sub_level
, sub_last_ptntl_change
/* -- This done by trigger HRI_SUPV_HRCHY_SUMMARY_WHO
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
/**/
)
VALUES(
cur_supervisors_rec.business_group_id
, cur_supervisors_rec.person_id
, cur_supervisors_rec.supv_asg_id
, cur_supervisors_rec.supervisor_level
, l_sup_updates_tab(cur_supervisors_rec.person_id)
, cur_reports_rec.business_group_id
, cur_reports_rec.person_id
, cur_reports_rec.assignment_id
, cur_reports_rec.primary_flag
, cur_reports_rec.subordinate_level + cur_supervisors_rec.supervisor_level
, l_sup_updates_tab(cur_reports_rec.person_id)
/* -- This done by trigger HRI_SUPV_HRCHY_SUMMARY_WHO
, SYSDATE
, -1
, SYSDATE
, -1
, -1
/**/
);