[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_phase := 'Insert into table FII_PERSON_ID_TMP';
INSERT into FII_PERSON_ID_TMP (person_id)
select /*+ leading(ct) full(ct) index(suph HRI_CS_SUPH_N4) use_nl(ct suph) */
distinct suph.sup_person_id
from fii_ccc_mgr_gt ct,
hri_cs_suph suph,
per_assignment_status_types ast
where ct.manager = suph.sub_person_id
and sysdate between suph.effective_start_date
and suph.effective_end_date
and suph.sup_assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_PERSON_ID_TMP');
g_phase := 'Insert into table FII_CC_MGR_HIER_GT';
INSERT INTO FII_CC_MGR_HIER_GT
(MGR_ID,
MGR_LEVEL,
DIRECT_ID,
DIRECT_LEVEL,
EMP_ID,
EMP_LEVEL,
NEXT_LEVEL_IS_LEAF,
IS_LEAF_FLAG,
AGGREGATION_FLAG)
select
sup.sup_person_id mgr_id,
sup.sup_level mgr_level,
sup.SUB_PERSON_ID direct_id,
sup.sub_level drect_level,
sub.sub_person_id emp_id,
sub.sub_level emp_level,
'N' next_level_is_leaf,
'N' is_leaf_flag,
decode(SIGN(sub.sub_level-G_AGGREGATION_LEVELS),1,'N','Y') aggregation_flag
from hri_cs_suph sup,
hri_cs_suph sub
where sup.sub_relative_level <= 1
and (sup.sub_relative_level = 1 OR sup.sup_level = 1)
and sup.sup_invalid_flag_code = 'N'
and sup.sub_invalid_flag_code = 'N'
and sup.sub_primary_asg_flag_code = 'Y'
and sysdate between sup.effective_start_date and sup.effective_end_date
and sup.sub_person_id = sub.sup_person_id
and sub.sup_invalid_flag_code = 'N'
and sub.sub_invalid_flag_code = 'N'
and sub.sub_primary_asg_flag_code = 'Y'
and sysdate between sub.effective_start_date and sub.effective_end_date
and sub.sub_person_id in (select person_id from FII_PERSON_ID_TMP);
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT ||
' rows of data into FII_CC_MGR_HIER_GT table');
g_phase := 'Update MGR_ID and MGR_LEVEL for records of top person';
Update FII_CC_MGR_HIER_GT
Set mgr_id = -999,
mgr_level = 0
Where mgr_level = 1
and direct_level = 1;
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_CC_MGR_HIER_GT');
g_phase := 'Insert all self records';
Insert into FII_CC_MGR_HIER_GT
(MGR_ID,
MGR_LEVEL,
DIRECT_ID,
DIRECT_LEVEL,
EMP_ID,
EMP_LEVEL,
NEXT_LEVEL_IS_LEAF,
IS_LEAF_FLAG,
AGGREGATION_FLAG)
select
EMP_ID,
EMP_LEVEL,
EMP_ID,
EMP_LEVEL,
EMP_ID,
EMP_LEVEL,
'N',
'N',
AGGREGATION_FLAG
from FII_CC_MGR_HIER_GT
where mgr_id = -999;
FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_CC_MGR_HIER_GT');
g_phase := 'Update column next_level_is_leaf...';
Update FII_CC_MGR_HIER_GT tab1
Set tab1.next_level_is_leaf = 'Y'
Where tab1.direct_id = tab1.emp_id
AND tab1.aggregation_flag = 'Y'
AND 1 = (select count(*)
from FII_CC_MGR_HIER_GT tab2
where tab2.mgr_id = tab1.direct_id
and tab2.aggregation_flag = 'Y');
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_CC_MGR_HIER_GT');
g_phase := 'Update column is_leaf_flag...';
Update FII_CC_MGR_HIER_GT
Set is_leaf_flag = 'Y'
Where mgr_id = direct_id
and direct_id = emp_id
and next_level_is_leaf = 'Y';
FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_CC_MGR_HIER_GT');
PROCEDURE Incre_Update (errbuf IN OUT NOCOPY VARCHAR2,
retcode IN OUT NOCOPY VARCHAR2) IS
BEGIN
if g_debug_flag = 'Y' then
FII_MESSAGE.Func_Ent('FII_CC_MGR_SUP_C.Incre_Update');
g_phase := 'DELETE FROM FII_CC_MGR_HIERARCHIES';
DELETE FROM FII_CC_MGR_HIERARCHIES
WHERE (mgr_id, mgr_level, direct_id, direct_level,
emp_id, emp_level, next_level_is_leaf, is_leaf_flag) IN
(SELECT mgr_id, mgr_level, direct_id, direct_level,
emp_id, emp_level, next_level_is_leaf, is_leaf_flag
FROM FII_CC_MGR_HIERARCHIES
MINUS
SELECT mgr_id, mgr_level, direct_id, direct_level,
emp_id, emp_level, next_level_is_leaf, is_leaf_flag
FROM FII_CC_MGR_HIER_GT
WHERE aggregation_flag = 'Y');
FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_CC_MGR_HIERARCHIES');
g_phase := 'INSERT INTO FII_CC_MGR_HIERARCHIES';
INSERT INTO FII_CC_MGR_HIERARCHIES
(mgr_id,
mgr_level,
direct_id,
direct_level,
emp_id,
emp_level,
next_level_is_leaf,
is_leaf_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
(SELECT mgr_id,
mgr_level,
direct_id,
direct_level,
emp_id,
emp_level,
next_level_is_leaf,
is_leaf_flag,
SYSDATE,
G_FII_USER_ID,
SYSDATE,
G_FII_USER_ID,
G_FII_LOGIN_ID
FROM FII_CC_MGR_HIER_GT
WHERE aggregation_flag = 'Y'
MINUS
SELECT mgr_id,
mgr_level,
direct_id,
direct_level,
emp_id,
emp_level,
next_level_is_leaf,
is_leaf_flag,
SYSDATE,
G_FII_USER_ID,
SYSDATE,
G_FII_USER_ID,
G_FII_LOGIN_ID
FROM FII_CC_MGR_HIERARCHIES);
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT ||
' rows of data into FII_CC_MGR_HIERARCHIES table');
FII_MESSAGE.Func_Succ('FII_CC_MGR_SUP_C.Incre_Update');
Error in phase ' || g_phase || ' of running FII_CC_MGR_SUP_C.Incre_Update; '
FII_MESSAGE.Func_Fail('FII_CC_MGR_SUP_C.Incre_Update');
END Incre_Update;
g_phase := 'Insert into table FII_CC_MGR_HIERARCHIES';
INSERT /*+ APPEND */ INTO FII_CC_MGR_HIERARCHIES
(MGR_ID,
MGR_LEVEL,
DIRECT_ID,
DIRECT_LEVEL,
EMP_ID,
EMP_LEVEL,
NEXT_LEVEL_IS_LEAF,
IS_LEAF_FLAG,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
select
MGR_ID,
MGR_LEVEL,
DIRECT_ID,
DIRECT_LEVEL,
EMP_ID,
EMP_LEVEL,
NEXT_LEVEL_IS_LEAF,
IS_LEAF_FLAG,
SYSDATE,
G_FII_USER_ID,
SYSDATE,
G_FII_USER_ID,
G_FII_LOGIN_ID
from
FII_CC_MGR_HIER_GT
where AGGREGATION_FLAG = 'Y';
FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT ||
' rows of data into FII_CC_MGR_HIERARCHIES table');