[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- delete WBS/XBS slices for specific struct_version_id -- ###delete###
if g_pa_debug_mode = 'Y'
then
PJI_UTILS.WRITE2LOG(
'PJI_PJP - Delete specific WBS/XBS slices -'
|| ' p_wbs_version_id = '
|| p_wbs_version_id,
null,
g_msg_level_high_detail
);
delete /*+ index(xbs, PA_XBS_DENORM_N2) */
from pa_xbs_denorm xbs
where struct_type in ('WBS', 'XBS')
and struct_version_id = p_wbs_version_id;
-- delete PRG slices for specific prg_group and p_wbs_version_id
begin
-- get prg_group information from versions table
select
distinct
prg_group
into l_prg_group_id
from pa_proj_element_versions
where element_version_id = p_wbs_version_id;
'PJI_PJP - Deleted specific PRG slices -'
|| ' p_wbs_version_id = '
|| p_wbs_version_id
|| ' prg_group_id = '
|| l_prg_group_id,
null,
g_msg_level_high_detail
);
delete
from pa_xbs_denorm
where struct_type = 'PRG'
and prg_group = l_prg_group_id;
delete /*+ index(prg, PA_XBS_DENORM_N1) */
from pa_xbs_denorm prg
where struct_type = 'PRG'
and struct_version_id is null
and sup_id = p_wbs_version_id
and sub_id = p_wbs_version_id;
-- don't delete contents from PJI_FP_AGGR_XBS_T
-- Sadiq will call cleanup_xbs_denorm(p_worker_id, 'ONLINE')
-- -----------------------------------------------------
-- PRG Online Mode --
elsif l_denorm_type = 'PRG'
then
null; -- do nothing for now
-- delete WBS/XBS slices for specific struct_version_id -- ###delete###
FOR WBS_DELETE_NODE IN
(
select
distinct
decode( invert.id,
1, i_log.event_object,
2, i_log.attribute2
) event_object_id
from PJI_PA_PROJ_EVENTS_LOG i_log,
(
select 1 id
from dual
UNION ALL
select 2 id
from dual
) invert
where 1=1
and i_log.worker_id = P_WORKER_ID
and i_log.event_type in ('WBS_CHANGE', 'WBS_PUBLISH')
) LOOP
if g_pa_debug_mode = 'Y'
then
PJI_UTILS.WRITE2LOG(
'PJI_PJP - Delete specific WBS/XBS slices -'
|| ' struct_version_id = '
|| WBS_DELETE_NODE.event_object_id,
null,
g_msg_level_high_detail
);
delete
from PA_XBS_DENORM
where 1=1
and STRUCT_TYPE in ('WBS', 'XBS')
and STRUCT_VERSION_ID = WBS_DELETE_NODE.event_object_id;
-- delete PRG slices for specific prg_group
FOR PRG_DELETE_NODE IN
(
select
distinct
decode( invert.id,
1, i_log.event_object,
2, i_log.attribute1
) event_object_id
from PJI_PA_PROJ_EVENTS_LOG i_log,
(
select 1 id
from dual
UNION ALL
select 2 id
from dual
) invert
where 1=1
and i_log.worker_id = P_WORKER_ID
and i_log.event_type = 'PRG_CHANGE'
and i_log.event_object <> -1
) LOOP
if g_pa_debug_mode = 'Y'
then
PJI_UTILS.WRITE2LOG(
'PJI_PJP - Program groups to be deleted -'
|| ' prg_group = '
|| PRG_DELETE_NODE.event_object_id,
null,
g_msg_level_high_detail
);
select
distinct
project_id
from pa_proj_element_versions
where 1=1
and object_type = 'PA_STRUCTURES'
and prg_group IS NOT NULL /* 4904076 */
and prg_group = PRG_DELETE_NODE.event_object_id
) LOOP
-- delete all slices of all projects belonging
-- to specific projects in a group
if g_pa_debug_mode = 'Y'
then
PJI_UTILS.WRITE2LOG(
'PJI_PJP - Delete -'
|| ' sup_project_id = '
|| PRG_PROJECT_NODE.project_id,
null,
g_msg_level_high_detail
);
delete
from PA_XBS_DENORM
where 1=1
-- and struct_type = 'PRG'
and sup_project_id = PRG_PROJECT_NODE.project_id;
-- delete All slices for sup_project_id
FOR PRG_2_DELETE_NODE IN
(
select
distinct
i_log.ATTRIBUTE1
from PJI_PA_PROJ_EVENTS_LOG i_log
where 1=1
and i_log.worker_id = P_WORKER_ID
and i_log.event_type = 'PRG_CHANGE'
and i_log.event_object = -1
) LOOP
/* Bug 5006375 , 5608947 */
FOR PRG_PROJECT_NODE IN
(
select
distinct
project_id
from pa_proj_element_versions
where 1=1
and object_type = 'PA_STRUCTURES'
and prg_group IS NULL /* 4904076 */
and project_id = PRG_2_DELETE_NODE.attribute1
) LOOP
if g_pa_debug_mode = 'Y'
then
PJI_UTILS.WRITE2LOG(
'PJI_PJP - Delete specific ALL slices -'
|| ' sup_project_id = '
|| PRG_PROJECT_NODE.project_id,
null,
g_msg_level_high_detail
);
delete
from PA_XBS_DENORM
where 1=1
-- and struct_type = 'PRG'
and sup_project_id = PRG_PROJECT_NODE.project_id;
-- OPERATION_TYPE in ('I', 'U', 'D') ==> since we delete the entire
--version or program we do not
--use this parameter for now
--
--
-- *** Use PJI_PA_PROJ_EVENTS_LOG *** to determine which PRG/WBS
-- data to process. ***
prg_denorm(
p_worker_id,
l_extraction_type
);
cursor c_version_exists is select 1 from dual
where exists (select 1
from pa_xbs_denorm
where struct_version_id is null
and sup_id=p_wbs_version_id_from
and sub_id=p_wbs_version_id_from );
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
select 1
into l_prg_exists
from dual
where exists (select 1
from pa_xbs_denorm
where struct_version_id is null
and sup_id=p_wbs_version_id_to
and sub_id=p_wbs_version_id_to);
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
select proj_element_id ,project_id
into l_top_proj_element_id ,l_target_project_id
from PA_PROJ_ELEMENT_VERSIONS
where parent_structure_version_id= p_wbs_version_id_to
and object_type='PA_STRUCTURES';
/* First inserting all the records of the old version changing the project_element_id mapping to element_number i.e
sup_emt_id,sub_emt_id,subro_id to sup_element_numbner,sub_element_numbner,subroelement_numbner ,
once that is done getting the pro_element_id back for the new version by mapping the same element number
to the new proj_element_ids*/
declare
cursor c_temp(p_wbs_version_id_from in number,p_wbs_version_id_to in number,l_top_proj_element_id in number) is
select temp.struct_type,temp.prg_group,projv1s.project_id sup_project_id, projv1s.proj_element_id sup_emt_id
,projv1s.element_version_id sup_id,projv1s.parent_structure_version_id struct_version_id
,temp.sub_leaf_flag, temp.sup_level, temp.sub_level,temp.relationship_type
, decode( temp.struct_type,'WBS',l_top_proj_element_id,null) struct_emt_id
,temp.sub_rollup_id,0,0,0,temp.sub_element_number,temp.subro_element_number
from pa_xbs_denorm_temp temp
,PA_PROJ_ELEMENT_VERSIONS projv1s
,pa_proj_elements proje1s
where proje1s.element_number= temp.sup_element_number
and proje1s.proj_element_id =projv1s.proj_element_id
and projv1s.parent_structure_version_id=temp.struct_version_id
and projv1s.parent_structure_version_id=p_wbs_version_id_to;
insert into PA_XBS_DENORM_temp
(
struct_type, prg_group, sup_project_id,
sup_emt_id, sup_id, struct_version_id,
sub_leaf_flag, sup_level, sub_level,
relationship_type, struct_emt_id, sub_rollup_id,
sub_id, sub_emt_id, subro_id,
sub_element_number,
sup_element_number,
subro_element_number
)
select xbs.struct_type,
null prg_group
,xbs.sup_project_id
,xbs.sup_emt_id,xbs.sup_id,p_wbs_version_id_to struct_version_id
, xbs.sub_leaf_flag,xbs.sup_level, xbs.sub_level
, xbs.relationship_type,xbs.struct_emt_id,xbs.sub_rollup_id
,xbs.sub_id,xbs.sub_emt_id,xbs.subro_id
,(select projeb.element_number from pa_proj_elements projeb
where projeb.proj_element_id= xbs.sub_emt_id ) sub_element_number
,(select decode( proje.object_type,'PA_TASKS',proje.element_number,'PA_STRUCTURES',to_char(l_top_proj_element_id ),null) from pa_proj_elements proje
where proje.proj_element_id= xbs.sup_emt_id ) sup_element_number
,(select projec.element_number from pa_proj_elements projec
where projec.proj_element_id= xbs.subro_id) subro_element_number
from pa_xbs_denorm xbs
,PA_PROJ_ELEMENT_VERSIONS projsup
where xbs.sup_emt_id =projsup.proj_element_id
and xbs.sup_id=projsup.element_version_id
and xbs.struct_version_id =p_wbs_version_id_from
and projsup.parent_structure_version_id=xbs.struct_version_id;
delete from pa_xbs_denorm_temp;
insert into PA_XBS_DENORM_temp
(
struct_type
,prg_group
,sup_project_id
,sup_emt_id
,sup_id
,struct_version_id
,sub_leaf_flag
,sup_level
,sub_level
,relationship_type
,struct_emt_id
,sub_rollup_id
,sub_id
,sub_emt_id
,subro_id
,sub_element_number
,subro_element_number
)
VALUES
( struct_typetab(i)
,prg_grouptab(i)
,sup_project_idtab(i)
,sup_emt_idtab(i)
,sup_idtab(i)
,struct_version_idtab(i)
,sub_leaf_flagtab(i)
,sup_leveltab(i)
,sub_leveltab(i)
,relationship_typetab(i)
,struct_emt_idtab(i)
,sub_rollup_idtab(i)
,sub_idtab(i)
,sub_emt_idtab(i)
,subro_idtab(i)
,sub_element_numbertab(i)
,subro_element_numbertab(i)
);
select temp.struct_type,temp.prg_group
,temp.struct_version_id ,temp.sup_project_id
,temp.sup_emt_id , projv1b.proj_element_id sub_emt_id
,temp.sup_id ,projv1b.element_version_id sub_id
,temp.sup_level, temp.sub_level
,temp.relationship_type ,temp.sub_leaf_flag
,temp.struct_emt_id,temp.sub_rollup_id
,(select proje1.proj_element_id from pa_proj_elements proje1
,PA_PROJ_ELEMENT_VERSIONS projv1
WHERE PROJE1.PROJECT_ID=TEMP.SUP_PROJECT_ID
AND PROJE1.OBJECT_TYPE='PA_TASKS'
AND PROJE1.ELEMENT_NUMBER= TEMP.SUBRO_ELEMENT_NUMBER
AND PROJE1.PROJ_ELEMENT_ID= PROJV1.PROJ_ELEMENT_ID
AND PROJV1.PARENT_STRUCTURE_VERSION_ID= TEMP.STRUCT_VERSION_ID
AND PROJV1.PARENT_STRUCTURE_VERSION_ID=p_wbs_version_id_to
) subro_id
from pa_xbs_denorm_temp temp
,PA_PROJ_ELEMENT_VERSIONS projv1b
,pa_proj_elements proje1b
where proje1b.element_number= temp.sub_element_number
and proje1b.proj_element_id =projv1b.proj_element_id
and projv1b.parent_structure_version_id=temp.struct_version_id
and projv1b.parent_structure_version_id=p_wbs_version_id_to;
insert into PA_XBS_DENORM(
struct_type
,prg_group
,struct_version_id
,sup_project_id
,sup_emt_id
,sub_emt_id
,sup_id
,sub_id
,sup_level
,sub_level
,relationship_type
,sub_leaf_flag
,struct_emt_id
,sub_rollup_id
,subro_id
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
( struct_typetab(i)
,prg_grouptab(i)
,struct_version_idtab(i)
,sup_project_idtab(i)
,sup_emt_idtab(i)
,sub_emt_idtab(i)
,sup_idtab(i)
,sub_idtab(i)
,sup_leveltab(i)
,sub_leveltab(i)
,relationship_typetab(i)
,sub_leaf_flagtab(i)
,struct_emt_idtab(i)
,sub_rollup_idtab(i)
,subro_idtab(i)
,l_last_update_date
,l_last_updated_by
,l_creation_date
,l_created_by
,l_last_update_login
);
/* The below insert replaces the call to Pji_Pjp_Sum_Rollup.update_xbs_denorm
it inserts all the data to pji_xbs_Denorm from pa_xbs_Denorm*/
IF struct_typetab.COUNT <>0 then
FORALL i IN struct_typetab.FIRST..struct_typetab.LAST
insert into PJI_XBS_DENORM(
struct_type
,prg_group
,struct_version_id
,sup_project_id
,sup_emt_id
,sub_emt_id
,sup_id
,sub_id
,sup_level
,sub_level
,relationship_type
,sub_leaf_flag
-- ,struct_emt_id
,sub_rollup_id
,subro_id
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
( struct_typetab(i)
,prg_grouptab(i)
,struct_version_idtab(i)
,sup_project_idtab(i)
,sup_emt_idtab(i)
,sub_emt_idtab(i)
,sup_idtab(i)
,sub_idtab(i)
,sup_leveltab(i)
,sub_leveltab(i)
,relationship_typetab(i)
,sub_leaf_flagtab(i)
-- ,struct_emt_idtab(i)
,sub_rollup_idtab(i)
,subro_idtab(i)
,l_last_update_date
,l_last_updated_by
,l_creation_date
,l_created_by
,l_last_update_login
);
-- write_log('insert bulk into xbs_Denorm done 1');
/* inserting the PRG lines*/
insert
into PA_XBS_DENORM
(
struct_type,
prg_group,
struct_version_id,
sup_project_id,
sup_emt_id,
sub_emt_id,
subro_id,
sup_level,
sub_rollup_id,
sub_leaf_flag,
sub_level,
relationship_type,
struct_emt_id,
sup_id,
sub_id,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
select xbs.struct_type,
null prg_group,
xbs.struct_version_id,l_target_project_id sup_project_id,
l_top_proj_element_id sup_emt_id,l_top_proj_element_id sub_emt_id,
xbs.subro_id,1,
l_top_proj_element_id sub_rollup_id,xbs.sub_leaf_flag,
1,xbs.relationship_type,
xbs.struct_emt_id,to_number(p_wbs_version_id_to) wsup_id,
to_number(p_wbs_version_id_to) wsub_id,l_last_update_date,
l_last_updated_by,l_creation_date,
l_created_by ,l_last_update_login
from PA_XBS_DENORM xbs
where xbs.struct_version_id is null
and xbs.sup_id=p_wbs_version_id_from
and xbs.sub_id=p_wbs_version_id_from;
insert into PJI_XBS_DENORM
(
STRUCT_TYPE,
PRG_GROUP,
STRUCT_VERSION_ID,
SUP_PROJECT_ID,
SUP_ID,
SUP_EMT_ID,
SUBRO_ID,
SUB_ID,
SUB_EMT_ID,
SUP_LEVEL,
SUB_LEVEL,
SUB_ROLLUP_ID,
SUB_LEAF_FLAG,
RELATIONSHIP_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
select
den.STRUCT_TYPE,
den.PRG_GROUP,
den.STRUCT_VERSION_ID,
den.SUP_PROJECT_ID,
den.SUP_ID,
den.SUP_EMT_ID,
den.SUBRO_ID,
den.SUB_ID,
den.SUB_EMT_ID,
1 SUP_LEVEL,
1 SUB_LEVEL,
den.SUB_ROLLUP_ID,
den.SUB_LEAF_FLAG,
den.RELATIONSHIP_TYPE,
l_last_update_date,
l_last_updated_by,
l_creation_date,
l_created_by,
l_last_update_login
from
PA_XBS_DENORM den
where den.struct_version_id is null
and den.sup_id=p_wbs_version_id_to
and den.sub_id=p_wbs_version_id_to;
select xbs.struct_type,xbs.prg_group,
to_number(p_wbs_version_id_to) wstruct_version_id,xbs.sup_project_id,
xbs.sup_emt_id,xbs.sub_emt_id,
projsup.element_version_id sup_id,
(select projsub.element_version_id from PA_PROJ_ELEMENT_VERSIONS projsub
where projsub.proj_element_id= xbs.sub_emt_id
and projsup.parent_structure_version_id=projsub.parent_structure_version_id) sub_id,
xbs.sup_level,xbs.sub_level,
xbs.relationship_type,xbs.sub_leaf_flag,
xbs.struct_emt_id,xbs.sub_rollup_id,
xbs.subro_id
from pa_xbs_denorm xbs,
PA_PROJ_ELEMENT_VERSIONS projsup
where projsup.parent_structure_version_id= p_wbs_version_id_to
and projsup.proj_element_id= xbs.sup_emt_id
and xbs.struct_version_id= p_wbs_version_id_from;
insert into PA_XBS_DENORM(
struct_type
,prg_group
,struct_version_id
,sup_project_id
,sup_emt_id
,sub_emt_id
,sup_id
,sub_id
,sup_level
,sub_level
,relationship_type
,sub_leaf_flag
,struct_emt_id
,sub_rollup_id
,subro_id
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
( struct_typetab(i)
,prg_grouptab(i)
,struct_version_idtab(i)
,sup_project_idtab(i)
,sup_emt_idtab(i)
,sub_emt_idtab(i)
,sup_idtab(i)
,sub_idtab(i)
,sup_leveltab(i)
,sub_leveltab(i)
,relationship_typetab(i)
,sub_leaf_flagtab(i)
,struct_emt_idtab(i)
,sub_rollup_idtab(i)
,subro_idtab(i)
,l_last_update_date
,l_last_updated_by
,l_creation_date
,l_created_by
,l_last_update_login
);
/* The below insert replaces the call to Pji_Pjp_Sum_Rollup.update_xbs_denorm
it inserts all the data to pji_xbs_Denorm from pa_xbs_Denorm*/
IF struct_typetab.COUNT <>0 then
FORALL i IN struct_typetab.FIRST..struct_typetab.LAST
insert into PJI_XBS_DENORM(
struct_type
,prg_group
,struct_version_id
,sup_project_id
,sup_emt_id
,sub_emt_id
,sup_id
,sub_id
,sup_level
,sub_level
,relationship_type
,sub_leaf_flag
-- ,struct_emt_id
,sub_rollup_id
,subro_id
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
( struct_typetab(i)
,prg_grouptab(i)
,struct_version_idtab(i)
,sup_project_idtab(i)
,sup_emt_idtab(i)
,sub_emt_idtab(i)
,sup_idtab(i)
,sub_idtab(i)
,sup_leveltab(i)
,sub_leveltab(i)
,relationship_typetab(i)
,sub_leaf_flagtab(i)
-- ,struct_emt_idtab(i)
,sub_rollup_idtab(i)
,subro_idtab(i)
,l_last_update_date
,l_last_updated_by
,l_creation_date
,l_created_by
,l_last_update_login
);
-- write_log('insert bulk into xbs_Denorm done 1');
/* inserting the PRG lines*/
insert
into PA_XBS_DENORM
(
struct_type,
prg_group,
struct_version_id,
sup_project_id,
sup_emt_id,
sub_emt_id,
subro_id,
sup_level,
sub_rollup_id,
sub_leaf_flag,
sub_level,
relationship_type,
struct_emt_id,
sup_id,
sub_id,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
select xbs.struct_type,xbs.prg_group,
xbs.struct_version_id,xbs.sup_project_id,
xbs.sup_emt_id,xbs.sub_emt_id,
xbs.subro_id,1,
xbs.sub_rollup_id,xbs.sub_leaf_flag,
1,xbs.relationship_type,
xbs.struct_emt_id,to_number(p_wbs_version_id_to) wsup_id,
to_number(p_wbs_version_id_to) wsub_id,l_last_update_date,
l_last_updated_by,l_creation_date,
l_created_by ,l_last_update_login
from PA_XBS_DENORM xbs
where xbs.struct_version_id is null
and xbs.sup_id=p_wbs_version_id_from
and xbs.sub_id=p_wbs_version_id_from;
insert into PJI_XBS_DENORM
(
STRUCT_TYPE,
PRG_GROUP,
STRUCT_VERSION_ID,
SUP_PROJECT_ID,
SUP_ID,
SUP_EMT_ID,
SUBRO_ID,
SUB_ID,
SUB_EMT_ID,
SUP_LEVEL,
SUB_LEVEL,
SUB_ROLLUP_ID,
SUB_LEAF_FLAG,
RELATIONSHIP_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
select
den.STRUCT_TYPE,
den.PRG_GROUP,
den.STRUCT_VERSION_ID,
den.SUP_PROJECT_ID,
den.SUP_ID,
den.SUP_EMT_ID,
den.SUBRO_ID,
den.SUB_ID,
den.SUB_EMT_ID,
den.SUP_LEVEL,
den.SUB_LEVEL,
den.SUB_ROLLUP_ID,
den.SUB_LEAF_FLAG,
den.RELATIONSHIP_TYPE,
l_last_update_date,
l_last_updated_by,
l_creation_date,
l_created_by,
l_last_update_login
from
PA_XBS_DENORM den
where den.struct_version_id is null
and den.sup_id=p_wbs_version_id_to
and den.sub_id=p_wbs_version_id_to;
-- delete RBS slices for specific struct_version_id -- ###delete###
if g_pa_debug_mode = 'Y'
then
PJI_UTILS.WRITE2LOG(
'PJI_PJP - Delete specific RBS slices -'
|| ' p_rbs_version_id = '
|| p_rbs_version_id,
null,
g_msg_level_high_detail
);
delete
from PA_RBS_DENORM
where 1=1
and STRUCT_VERSION_ID = p_rbs_version_id;
-- don't delete contents from PJI_FP_AGGR_RBS_T
-- Sadiq will call cleanup_xbs_denorm(p_worker_id, 'ONLINE')
-- -----------------------------------------------------
-- Bulk Mode --
elsif p_denorm_type = 'ALL'
then
-- ----------------------------------------------
-- process RBS hiearchies during PJP summarization
if (
not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(
l_process,
'PJI_PJP_SUM_DENORM.POPULATE_RBS_DENORM(p_worker_id);'
delete
from PA_RBS_DENORM
where STRUCT_VERSION_ID in
(
select EVENT_OBJECT
from PJI_PA_PROJ_EVENTS_LOG
where 1=1
and EVENT_TYPE = 'PJI_RBS_CHANGE'
and WORKER_ID = P_WORKER_ID
);
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
delete
from PA_RBS_DENORM
where STRUCT_VERSION_ID = p_rbs_version_id;
delete
from PJI_RBS_DENORM
where STRUCT_VERSION_ID = p_rbs_version_id;
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
insert
into PJI_RBS_DENORM
(
struct_version_id,
sup_id,
subro_id,
sub_id,
sup_level,
sub_level,
sub_leaf_flag,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
select
struct_version_id,
sup_id,
subro_id,
sub_id,
sup_level,
sub_level,
sub_leaf_flag,
l_last_update_date,
l_last_updated_by,
l_creation_date,
l_created_by,
l_last_update_login
from PA_RBS_DENORM
where STRUCT_VERSION_ID = p_rbs_version_id;
select
distinct
PRG_LEVEL,
PROJECT_ID,
PROJ_ELEMENT_ID,
ELEMENT_VERSION_ID,
PARENT_STRUCTURE_VERSION_ID,
PRG_GROUP
from
(
select /*+ ordered */ -- all Projects in batch_map
distinct
1 prg_level,
ver.project_id,
ver.proj_element_id,
ver.element_version_id,
ver.parent_structure_version_id,
ver.prg_group
from
PJI_PJP_PROJ_BATCH_MAP map,
PA_PROJ_ELEMENT_VERSIONS ver
where
p_extraction_type in ('FULL', 'UPGRADE') and
ver.object_type = 'PA_STRUCTURES' and
ver.prg_group is null and
map.worker_id = p_worker_id and
map.PJI_PROJECT_STATUS is null and
ver.project_id = map.project_id
UNION ALL
select /*+ ordered */
-- Programs (UPGRADE only. In FULL, batch_map has necessary projects)
distinct
pvt_nodes1.prg_level,
pvt_nodes1.project_id,
pvt_nodes1.proj_element_id,
pvt_nodes1.element_version_id,
pvt_nodes1.parent_structure_version_id,
pvt_nodes1.prg_group
from
(
select /*+ ordered */
distinct
ver.prg_group
from
PA_PROJ_ELEMENT_VERSIONS ver,
PJI_PJP_PROJ_BATCH_MAP map
where
p_extraction_type in ('FULL', 'UPGRADE') and
ver.object_type = 'PA_STRUCTURES' and
ver.prg_group is not null and
map.worker_id = p_worker_id and
map.PJI_PROJECT_STATUS is null and
ver.project_id = map.project_id
) batch_map,
PA_PROJ_ELEMENT_VERSIONS pvt_nodes1
where
p_extraction_type in ('FULL', 'UPGRADE') and
pvt_nodes1.object_type = 'PA_STRUCTURES' and
pvt_nodes1.prg_group is not null and
pvt_nodes1.prg_group = batch_map.prg_group
UNION ALL
select
distinct
pvt_nodes2.prg_level,
pvt_nodes2.project_id,
pvt_nodes2.proj_element_id,
pvt_nodes2.element_version_id,
pvt_nodes2.parent_structure_version_id,
pvt_nodes2.prg_group
from PA_PROJ_ELEMENT_VERSIONS pvt_nodes2,
(
select
distinct
decode( invert.id,
1, i_log.event_object,
2, i_log.attribute1
) event_object_id
from PJI_PA_PROJ_EVENTS_LOG i_log,
(
select 1 id
from dual
where p_extraction_type IN ('INCREMENTAL', 'PARTIAL')
UNION ALL
select 2 id
from dual
where p_extraction_type IN ('INCREMENTAL', 'PARTIAL')
) invert
where 1=1
and i_log.worker_id = P_WORKER_ID
and i_log.event_type = 'PRG_CHANGE'
) log11
where 1=1
and pvt_nodes2.prg_group = log11.event_object_id
and pvt_nodes2.object_type = 'PA_STRUCTURES'
and pvt_nodes2.prg_group IS NOT NULl /* 4904076 */
UNION ALL
select
distinct
1 prg_level,
pvt_nodes3.project_id,
pvt_nodes3.proj_element_id,
pvt_nodes3.element_version_id,
pvt_nodes3.parent_structure_version_id,
pvt_nodes3.prg_group
from PA_PROJ_ELEMENT_VERSIONS pvt_nodes3,
(
select
distinct
decode( invert.id,
1, i_log.event_object,
2, i_log.attribute1
) event_object_id
from PJI_PA_PROJ_EVENTS_LOG i_log,
(
select 1 id
from dual
where p_extraction_type IN ('INCREMENTAL', 'PARTIAL')
UNION ALL
select 2 id
from dual
where p_extraction_type IN ('INCREMENTAL', 'PARTIAL')
) invert
where 1=1
and i_log.worker_id = P_WORKER_ID
and i_log.event_type = 'PRG_CHANGE'
and i_log.event_object = -1
) log22
where 1=1
and pvt_nodes3.project_id = log22.event_object_id -- log22.attribute2
and pvt_nodes3.object_type = 'PA_STRUCTURES'
and pvt_nodes3.prg_group is null
)
order by
PRG_LEVEL DESC, /* This DESC order by will take care the hierarchy of projects*/
PROJECT_ID,
PROJ_ELEMENT_ID,
ELEMENT_VERSION_ID,
PARENT_STRUCTURE_VERSION_ID,
PRG_GROUP
) LOOP
if (p_extraction_type = 'FULL' or p_extraction_type = 'UPGRADE') then
if (l_project_id is null) then
l_project_id := prg_node.PROJECT_ID;
update PJI_PJP_PROJ_BATCH_MAP
set PJI_PROJECT_STATUS = 'C'
where WORKER_ID = p_worker_id and
PROJECT_ID = l_project_id;
-- Determine if the node to be inserted is a leaf
-- If the node to be inserted has not been inserted before,
-- then we know that the node is a leaf
if PRG_NODE.prg_group is not null then
select count(*)
into l_prg_node_count
from PJI_FP_AGGR_XBS pdt_count
where 1=1
and pdt_count.sup_id = PRG_NODE.element_version_id
and pdt_count.worker_id = P_WORKER_ID
and rownum = 1;
'PJI_PJP - Inserting PRG node self -'
|| ' sup_id = '
|| PRG_NODE.element_version_id,
null,
g_msg_level_low_detail
);
-- Insert PRG node self --
insert
into PJI_FP_AGGR_XBS
(
struct_type,
prg_group,
struct_version_id,
sup_project_id,
sup_id,
sup_emt_id,
subro_id,
sub_id,
sub_emt_id,
sup_level,
sub_level,
sub_rollup_id,
sub_leaf_flag_id,
sub_leaf_flag,
status_id,
worker_id
)
values (
'PRG', -- structure type
PRG_NODE.prg_group, -- prg group
null, -- structure version id
PRG_NODE.project_id, -- parent project id
PRG_NODE.element_version_id, -- parent id
PRG_NODE.proj_element_id, -- sup emt id
null, -- immediate child id
PRG_NODE.element_version_id, -- child id
PRG_NODE.proj_element_id, -- sub emt_id
PRG_NODE.prg_level, -- parent level
PRG_NODE.prg_level, -- child level
PRG_NODE.proj_element_id, -- child rollup id
l_prg_leaf_flag_id, -- child leaf flag id
l_prg_leaf_flag, -- child leaf flag
'self', -- status id
P_WORKER_ID -- worker id
);
select
distinct
prt_parent.object_id_from1,
prt_parent.relationship_type,
ver.prg_level
from PA_OBJECT_RELATIONSHIPS prt_parent,
PA_PROJ_ELEMENT_VERSIONS ver
where 1=1
and prt_parent.object_id_to1 = PRG_NODE.element_version_id
and prt_parent.object_type_from = 'PA_TASKS'
and prt_parent.object_type_to = 'PA_STRUCTURES'
and (
prt_parent.relationship_type = 'LF'
or
prt_parent.relationship_type = 'LW'
)
and ver.element_version_id = prt_parent.object_id_from1
) LOOP
select pvt_parent1.parent_structure_version_id,
pvt_parent1.project_id,
pvt_parent1.proj_element_id
into l_prg_temp_parent,
l_prj_temp_parent,
l_prg_dummy_rollup -- ###dummy### -- l_prg_temp_rollup
from PA_PROJ_ELEMENT_VERSIONS pvt_parent1
where 1=1
and pvt_parent1.element_version_id = PRG_PARENT_NODE.object_id_from1;
select link_task_flag
into l_prg_dummy_task_flag
from pa_proj_elements
where 1=1
and proj_element_id = l_prg_dummy_rollup;
select dt_ver1.proj_element_id
into l_prg_temp_rollup
from pa_object_relationships dt_rel,
pa_proj_element_versions dt_ver1,
pa_proj_element_versions dt_ver2
where 1=1
and dt_ver1.element_version_id = dt_rel.object_id_from1
and dt_rel.object_type_from = 'PA_TASKS'
and dt_rel.object_type_to = 'PA_TASKS'
and dt_rel.object_id_to1 = dt_ver2.element_version_id
and dt_ver2.proj_element_id = l_prg_dummy_rollup;
select dt_ver1.proj_element_id
into l_prg_temp_rollup
from pa_object_relationships dt_rel,
pa_proj_element_versions dt_ver1
where 1=1
and dt_ver1.element_version_id = dt_rel.object_id_from1
and dt_rel.object_type_from = 'PA_TASKS'
and dt_rel.object_type_to = 'PA_TASKS'
and dt_rel.object_id_to1 = PRG_PARENT_NODE.object_id_from1;
select pvt_parent4.proj_element_id
into l_prg_temp_sup_emt
from PA_PROJ_ELEMENT_VERSIONS pvt_parent4
where 1=1
and pvt_parent4.element_version_id = l_prg_temp_parent;
select pvt_parent5.proj_element_id
into l_prg_temp_sub_emt
from PA_PROJ_ELEMENT_VERSIONS pvt_parent5
where 1=1
and pvt_parent5.element_version_id = PRG_NODE.element_version_id;
'PJI_PJP - Inserting PRG node parent -'
|| ' element_version_id = '
|| PRG_NODE.element_version_id
|| ' sup_id = '
|| l_prg_temp_parent
|| ' sub_rollup_id = '
|| l_prg_temp_rollup,
null,
g_msg_level_low_detail
);
-- Insert PRG node's parent --
insert
into PJI_FP_AGGR_XBS
(
struct_type,
prg_group,
struct_version_id,
sup_project_id,
sup_id,
sup_emt_id,
subro_id,
sub_id,
sub_emt_id,
sup_level,
sub_level,
sub_rollup_id,
sub_leaf_flag_id,
sub_leaf_flag,
relationship_type,
status_id,
worker_id
)
values (
'PRG', -- structure type
PRG_NODE.prg_group, -- prg group
null, -- structure version id
l_prj_temp_parent, -- parent project id
l_prg_temp_parent, -- parent id
l_prg_temp_sup_emt, -- sup emt_id
PRG_NODE.proj_element_id, -- immediate child id
PRG_NODE.element_version_id, -- child id
l_prg_temp_sub_emt, -- sub emt_id
PRG_PARENT_NODE.prg_level, -- parent level
PRG_NODE.prg_level, -- child level
l_prg_temp_rollup, -- child rollup id
l_prg_leaf_flag_id, -- child leaf flag id
l_prg_leaf_flag, -- child leaf flag
PRG_PARENT_NODE.relationship_type, -- relationship type (new)
'parent', -- status id
P_WORKER_ID -- worker id
);
select
distinct
pdt_child.sup_id,
pdt_child.sub_id,
pdt_child.sub_leaf_flag_id
from PJI_FP_AGGR_XBS pdt_child
where 1=1
and pdt_child.sup_id = PRG_NODE.element_version_id
and pdt_child.sup_id <> pdt_child.sub_id
and pdt_child.worker_id = P_WORKER_ID
) LOOP
-- l_prg_temp_level --
select pdt_child1.sub_level
into l_prg_temp_level
from PJI_FP_AGGR_XBS pdt_child1
where 1=1
and pdt_child1.sup_id = PRG_CHILDREN_NODE.sub_id
and pdt_child1.sup_id = pdt_child1.sub_id
and pdt_child1.worker_id = P_WORKER_ID;
select pvt_child1.project_id
into l_prj_temp_parent
from PA_PROJ_ELEMENT_VERSIONS pvt_child1
where 1=1
and pvt_child1.element_version_id = PRG_PARENT_NODE.object_id_from1;
select pvt_child2.proj_element_id
into l_prg_temp_sup_emt
from PA_PROJ_ELEMENT_VERSIONS pvt_child2
where 1=1
and pvt_child2.element_version_id = l_prg_temp_parent;
select pvt_child3.proj_element_id
into l_prg_temp_sub_emt
from PA_PROJ_ELEMENT_VERSIONS pvt_child3
where 1=1
and pvt_child3.element_version_id = PRG_CHILDREN_NODE.sub_id;
'PJI_PJP - Inserting PRG node child -'
|| ' sup_id = '
|| l_prg_temp_parent
|| ' sub_emt_id = '
|| l_prg_temp_sub_emt
|| ' sub_level = '
|| l_prg_temp_level,
-- || PRG_CHILDREN_NODE.sup_id,
null,
g_msg_level_low_detail
);
-- Insert PRG node's child --
insert
into PJI_FP_AGGR_XBS
(
struct_type,
prg_group,
struct_version_id,
sup_project_id,
sup_id,
sup_emt_id,
subro_id,
sub_id,
sub_emt_id,
sup_level,
sub_level,
sub_rollup_id,
sub_leaf_flag_id,
sub_leaf_flag,
status_id,
worker_id
)
values (
'PRG', -- structure type
PRG_NODE.prg_group, -- prg group
null, -- struct_version_id
l_prj_temp_parent, -- parent project id
l_prg_temp_parent, -- parent id
l_prg_temp_sup_emt, -- sup emt_id
PRG_NODE.proj_element_id, -- immediate child id
PRG_CHILDREN_NODE.sub_id, -- child id
l_prg_temp_sub_emt, -- sub emt_id
PRG_PARENT_NODE.prg_level, -- parent level
l_prg_temp_level, -- child level
null, -- child rollup id
PRG_CHILDREN_NODE.sub_leaf_flag_id, -- child leaf flag
l_prg_leaf_flag, -- child leaf flag
'children', -- status id
P_WORKER_ID -- worker id
);
select count(*)
into l_prg_element_version_count
from PA_PROJ_ELEMENT_VERSIONS cv
where cv.parent_structure_version_id = PRG_NODE.element_version_id
and rownum = 1;
update PJI_PJP_PROJ_BATCH_MAP
set PJI_PROJECT_STATUS = null
where WORKER_ID = p_worker_id and
PJI_PROJECT_STATUS = 'C';
select
distinct
sup_emt_id
into l_struct_emt_id
from pji_fp_aggr_xbs
where 1=1
and struct_type = 'PRG'
and sup_id = P_WBS_VERSION_ID
and worker_id = P_WORKER_ID;
select projects.structure_sharing_code,projects.project_id
into l_sharing_code,l_project_id
from pa_projects_all projects,
pa_proj_element_versions versions
where 1=1
and projects.project_id = versions.project_id
and versions.object_type = 'PA_STRUCTURES'
and versions.element_version_id = P_WBS_VERSION_ID;
select wvt_nodes.wbs_level,
wvt_nodes.project_id,
wvt_nodes.proj_element_id,
wvt_nodes.element_version_id,
wvt_nodes.parent_structure_version_id,
wvt_nodes.financial_task_flag -- ###financial###
from PA_PROJ_ELEMENT_VERSIONS wvt_nodes
where 1=1
and (
P_EXTRACTION_TYPE = 'FULL'
or
P_EXTRACTION_TYPE = 'UPGRADE'
)
and wvt_nodes.object_type = 'PA_TASKS'
and exists -- ###dummy###
(
select 1
from pa_proj_elements ele
where link_task_flag = 'N'
and ele.project_id = l_project_id
and ele.proj_element_id = wvt_nodes.proj_element_id
and rownum <= 1
)
and wvt_nodes.parent_structure_version_id = P_WBS_VERSION_ID
and wvt_nodes.wbs_level is not null
ORDER BY wbs_level DESC
) LOOP
-- -----------------------------------------------------
-- Check WBS node self --
-- Determine if the node to be inserted is a leaf
-- If the node to be inserted has not been inserted before,
-- then we know that the node is a leaf
select count(*)
into l_wbs_node_count
from PJI_FP_AGGR_XBS wdt_count
where wdt_count.sup_id = WBS_NODE.element_version_id
and wdt_count.worker_id = P_WORKER_ID
and rownum = 1;
'PJI_PJP - Inserting WBS node self - element_version_id = '
|| WBS_NODE.element_version_id,
null,
g_msg_level_low_detail
);
-- Insert WBS node self --
insert
into PJI_FP_AGGR_XBS
(
struct_type,
prg_group,
struct_emt_id,
struct_version_id,
sup_project_id,
sup_id,
sup_emt_id,
subro_id,
sub_id,
sub_emt_id,
sup_level,
sub_level,
sub_rollup_id,
sub_leaf_flag_id,
sub_leaf_flag,
relationship_type,
status_id,
worker_id
)
values (
'WBS', -- structure type
null, -- prg group
l_struct_emt_id, -- structure element id
P_WBS_VERSION_ID, -- structure version id
WBS_NODE.project_id, -- parent project id
WBS_NODE.element_version_id, -- parent id
WBS_NODE.proj_element_id, -- sup emt id
null, -- immediate child id
WBS_NODE.element_version_id, -- child id
WBS_NODE.proj_element_id, -- sub emt_id
WBS_NODE.wbs_level, -- parent level
WBS_NODE.wbs_level, -- child level
null, -- child rollup id
l_wbs_leaf_flag_id, -- child leaf flag id
l_wbs_leaf_flag, -- child leaf flag
decode(l_sharing_code,
'SHARE_FULL', 'WF',
'SHARE_PARTIAL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'WF', 'LW'),
'SPLIT_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
'SPLIT_NO_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
'PJI$NULL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW')),
-- sub financial task flag -- ###financial###
'self', -- status id
P_WORKER_ID -- worker id
);
select wrt_parent.object_id_from1
into l_wbs_temp_parent
from PA_OBJECT_RELATIONSHIPS wrt_parent
where 1=1
and wrt_parent.object_id_to1 = WBS_NODE.element_version_id
and wrt_parent.object_type_from = 'PA_TASKS'
and wrt_parent.object_type_to = 'PA_TASKS'
and wrt_parent.relationship_type = 'S';
select wvt_parent1.proj_element_id
into l_wbs_temp_sup_emt
from PA_PROJ_ELEMENT_VERSIONS wvt_parent1
where 1=1
and wvt_parent1.element_version_id = l_wbs_temp_parent;
select wvt_parent2.proj_element_id
into l_wbs_temp_sub_emt
from PA_PROJ_ELEMENT_VERSIONS wvt_parent2
where 1=1
and wvt_parent2.element_version_id = WBS_NODE.element_version_id;
'PJI_PJP - Inserting WBS node parent - l_wbs_temp_parent = '
|| l_wbs_temp_parent,
null,
g_msg_level_low_detail
);
-- Insert WBS node's parent --
insert
into PJI_FP_AGGR_XBS
(
struct_type,
prg_group,
struct_emt_id,
struct_version_id,
sup_project_id,
sup_id,
sup_emt_id,
subro_id,
sub_id,
sub_emt_id,
sup_level,
sub_level,
sub_rollup_id,
sub_leaf_flag_id,
sub_leaf_flag,
relationship_type,
status_id,
worker_id
)
values (
'WBS', -- structure type
null, -- prg group
l_struct_emt_id, -- structure element id
P_WBS_VERSION_ID, -- structure version id
WBS_NODE.project_id, -- parent project id
l_wbs_temp_parent, -- parent id
l_wbs_temp_sup_emt, -- sup_emt_id
WBS_NODE.proj_element_id, -- immediate child id
WBS_NODE.element_version_id, -- child id
l_wbs_temp_sub_emt, -- sub_emt_id
WBS_NODE.wbs_level -1, -- parent level
WBS_NODE.wbs_level, -- child level
null, -- child rollup id
l_wbs_leaf_flag_id, -- child leaf flag id
l_wbs_leaf_flag, -- child leaf flag
decode(l_sharing_code,
'SHARE_FULL', 'WF',
'SHARE_PARTIAL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'WF', 'LW'),
'SPLIT_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
'SPLIT_NO_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
'PJI$NULL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW')),
-- sub financial task flag -- ###financial###
'parent', -- status id
P_WORKER_ID -- worker id
);
select wdt_child.sup_id,
wdt_child.sub_id,
wdt_child.sub_leaf_flag_id,
wdt_child.relationship_type
from PJI_FP_AGGR_XBS wdt_child
where 1=1
and wdt_child.sup_id = WBS_NODE.element_version_id
and wdt_child.sup_id <> wdt_child.sub_id
and wdt_child.worker_id = P_WORKER_ID
) LOOP
-- l_wbs_temp_level --
select wdt_child1.sub_level
into l_wbs_temp_level
from PJI_FP_AGGR_XBS wdt_child1
where 1=1
and wdt_child1.sup_id = WBS_CHILDREN_NODE.sub_id
and wdt_child1.sup_id = wdt_child1.sub_id
and wdt_child1.worker_id = P_WORKER_ID;
select wvt_child1.proj_element_id
into l_wbs_temp_sup_emt
from PA_PROJ_ELEMENT_VERSIONS wvt_child1
where 1=1
and wvt_child1.element_version_id = l_wbs_temp_parent;
select wvt_child2.proj_element_id
into l_wbs_temp_sub_emt
from PA_PROJ_ELEMENT_VERSIONS wvt_child2
where 1=1
and wvt_child2.element_version_id = WBS_CHILDREN_NODE.sub_id;
'PJI_PJP - Inserting WBS node child - sup_id = '
|| WBS_CHILDREN_NODE.sup_id,
null,
g_msg_level_low_detail
);
-- Insert WBS node's child --
insert
into PJI_FP_AGGR_XBS
(
struct_type,
prg_group,
struct_emt_id,
struct_version_id,
sup_project_id,
sup_id,
sup_emt_id,
subro_id,
sub_id,
sub_emt_id,
sup_level,
sub_level,
sub_rollup_id,
sub_leaf_flag_id,
sub_leaf_flag,
relationship_type,
status_id,
worker_id
)
values (
'WBS', -- structure type
null, -- prg group
l_struct_emt_id, -- structure element id
P_WBS_VERSION_ID, -- structure version id
WBS_NODE.project_id, -- parent project id
l_wbs_temp_parent, -- parent id
l_wbs_temp_sup_emt, -- sup emt_id
WBS_NODE.proj_element_id, -- immediate child id
WBS_CHILDREN_NODE.sub_id, -- child id
l_wbs_temp_sub_emt, -- sub emt_id
WBS_NODE.wbs_level - 1, -- parent level
l_wbs_temp_level, -- child level
null, -- child rollup id
WBS_CHILDREN_NODE.sub_leaf_flag_id, -- child leaf flag id
l_wbs_leaf_flag, -- child leaf flag
decode(l_sharing_code,
'SHARE_FULL', 'WF',
'SHARE_PARTIAL', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'WF', 'LW'),
'SPLIT_MAPPING', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'LF', 'LW'),
'SPLIT_NO_MAPPING', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'LF', 'LW'),
'PJI$NULL', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'LF', 'LW')),
-- sub financial task flag -- ###financial###
'children', -- status id
P_WORKER_ID -- worker id
);
'PJI_PJP - Inserting XBS node self - sup_id = '
|| P_WBS_VERSION_ID,
null,
g_msg_level_low_detail
);
-- Insert XBS node --
insert
into PJI_FP_AGGR_XBS
(
struct_type,
prg_group,
struct_version_id,
sup_project_id,
sup_id,
sup_emt_id,
subro_id,
sub_id,
sub_emt_id,
sup_level,
sub_level,
sub_rollup_id,
sub_leaf_flag_id,
sub_leaf_flag,
relationship_type,
status_id,
worker_id
)
values (
'XBS', -- structure type
null, -- prg group
P_WBS_VERSION_ID, -- structure version id
WBS_NODE.project_id, -- parent project id
P_WBS_VERSION_ID, -- parent id
l_struct_emt_id, -- sup emt id
null, -- immediate child id
WBS_NODE.element_version_id, -- child id
WBS_NODE.proj_element_id, -- sub emt_id
0, -- parent level (l_wbs_level_id)
WBS_NODE.wbs_level, -- child level
null, -- child rollup id
l_wbs_leaf_flag_id, -- child leaf flag id
l_wbs_leaf_flag, -- child leaf flag
decode(l_sharing_code,
'SHARE_FULL', 'WF',
'SHARE_PARTIAL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'WF', 'LW'),
'SPLIT_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
'SPLIT_NO_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
'PJI$NULL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW')),
-- sub financial task flag -- ###financial###
'self', -- status id
P_WORKER_ID -- worker id
);
select max(pvt_level.prg_level)
into l_prg_level_id
from PA_PROJ_ELEMENT_VERSIONS pvt_level
where 1=1
and pvt_level.object_type = 'PA_STRUCTURES'
and pvt_level.prg_group IS NOT NULL /* 4904076 */
and prg_group = p_prg_group_id;
'PJI_PJP - PRG Inserts - l_prg_level_id = '
|| l_prg_level_id,
null,
g_msg_level_high_detail
);
select
distinct
pvt_nodes1.project_id,
pvt_nodes1.proj_element_id,
pvt_nodes1.element_version_id,
pvt_nodes1.parent_structure_version_id,
pvt_nodes1.prg_group,
pvt_nodes1.prg_level /*4625702*/
from PA_PROJ_ELEMENT_VERSIONS pvt_nodes1
where 1=1
and pvt_nodes1.object_type = 'PA_STRUCTURES'
and pvt_nodes1.element_version_id = p_wbs_version_id
) LOOP
IF l_prg_level_id > 1 -- ###prg_group_is_null###
and
PRG_NODE.prg_group is null
THEN
if g_pa_debug_mode = 'Y'
then
PJI_UTILS.WRITE2LOG(
'PJI_PJP - PRG Group is null Data Bug - element_version_id = '
|| PRG_NODE.element_version_id,
null,
g_msg_level_data_bug
);
-- Determine if the node to be inserted is a leaf
-- If the node to be inserted has not been inserted before,
-- then we know that the node is a leaf
select count(*)
into l_prg_node_count
from PJI_FP_AGGR_XBS_T pdt_count
where 1=1
and pdt_count.sup_id = PRG_NODE.element_version_id
and pdt_count.worker_id = P_WORKER_ID
and rownum = 1;
'PJI_PJP - Inserting PRG node self - sup_id = '
|| PRG_NODE.element_version_id,
null,
g_msg_level_low_detail
);
-- Insert PRG node self --
insert
into PJI_FP_AGGR_XBS_T
(
struct_type,
prg_group,
struct_version_id,
sup_project_id,
sup_id,
sup_emt_id,
subro_id,
sub_id,
sub_emt_id,
sup_level,
sub_level,
sub_rollup_id,
sub_leaf_flag_id,
sub_leaf_flag,
status_id,
worker_id
)
values (
'PRG', -- structure type
PRG_NODE.prg_group, -- prg group
null, -- structure version id
PRG_NODE.project_id, -- parent project id
PRG_NODE.element_version_id, -- parent id
PRG_NODE.proj_element_id, -- sup emt id
null, -- immediate child id
PRG_NODE.element_version_id, -- child id
PRG_NODE.proj_element_id, -- sub emt_id
nvl(PRG_NODE.prg_level,1) , -- 4625702 l_prg_level_id, -- parent level
nvl(PRG_NODE.prg_level,1) , -- 4625702 l_prg_level_id, -- child level
PRG_NODE.proj_element_id, -- child rollup id
l_prg_leaf_flag_id, -- child leaf flag id
l_prg_leaf_flag, -- child leaf flag
'self', -- status id
P_WORKER_ID -- worker id
);
select
distinct
prt_parent.object_id_from1,
prt_parent.relationship_type,
ver.prg_level
from PA_OBJECT_RELATIONSHIPS prt_parent,
PA_PROJ_ELEMENT_VERSIONS ver
where 1=1
and prt_parent.object_id_to1 = PRG_NODE.element_version_id
and prt_parent.object_type_from = 'PA_TASKS'
and prt_parent.object_type_to = 'PA_STRUCTURES'
and (
prt_parent.relationship_type = 'LF'
or
prt_parent.relationship_type = 'LW'
)
and ver.element_version_id = prt_parent.object_id_from1
) LOOP
-- l_prg_temp_parent --
-- l_prj_temp_parent --
-- l_prg_dummy_rollup --
select pvt_parent1.parent_structure_version_id,
pvt_parent1.project_id,
pvt_parent1.proj_element_id
into l_prg_temp_parent,
l_prj_temp_parent,
l_prg_dummy_rollup -- ###dummy### -- l_prg_temp_rollup
from PA_PROJ_ELEMENT_VERSIONS pvt_parent1
where 1=1
and pvt_parent1.element_version_id = PRG_PARENT_NODE.object_id_from1;
select link_task_flag
into l_prg_dummy_task_flag
from pa_proj_elements
where 1=1
and proj_element_id = l_prg_dummy_rollup;
select dt_ver1.proj_element_id
into l_prg_temp_rollup
from pa_object_relationships dt_rel,
pa_proj_element_versions dt_ver1
/* commented for bug 3838523 pa_proj_element_versions dt_ver2*/
where 1=1
and dt_ver1.element_version_id = dt_rel.object_id_from1
and dt_rel.object_type_from = 'PA_TASKS'
and dt_rel.object_type_to = 'PA_TASKS'
and dt_rel.object_id_to1 = PRG_PARENT_NODE.object_id_from1;
select pvt_parent4.proj_element_id
into l_prg_temp_sup_emt
from PA_PROJ_ELEMENT_VERSIONS pvt_parent4
where 1=1
and pvt_parent4.element_version_id = l_prg_temp_parent;
select pvt_parent5.proj_element_id
into l_prg_temp_sub_emt
from PA_PROJ_ELEMENT_VERSIONS pvt_parent5
where 1=1
and pvt_parent5.element_version_id = PRG_NODE.element_version_id;
'PJI_PJP - Inserting PRG node parent -'
|| ' element_version_id = '
|| PRG_NODE.element_version_id
|| ' sup_id = '
|| l_prg_temp_parent
|| ' sub_rollup_id = '
|| l_prg_temp_rollup,
null,
g_msg_level_low_detail
);
-- Insert PRG node's parent --
insert
into PJI_FP_AGGR_XBS_T
(
struct_type,
prg_group,
struct_version_id,
sup_project_id,
sup_id,
sup_emt_id,
subro_id,
sub_id,
sub_emt_id,
sup_level,
sub_level,
sub_rollup_id,
sub_leaf_flag_id,
sub_leaf_flag,
relationship_type,
status_id,
worker_id
)
values (
'PRG', -- structure type
PRG_NODE.prg_group, -- prg group
null, -- structure version id
l_prj_temp_parent, -- parent project id
l_prg_temp_parent, -- parent id
l_prg_temp_sup_emt, -- sup emt_id
PRG_NODE.proj_element_id, -- immediate child id
PRG_NODE.element_version_id, -- child id
l_prg_temp_sub_emt, -- sub emt_id
PRG_PARENT_NODE.prg_level, -- parent level
l_prg_level_id, -- child level
l_prg_temp_rollup, -- child rollup id
l_prg_leaf_flag_id, -- child leaf flag id
l_prg_leaf_flag, -- child leaf flag
PRG_PARENT_NODE.relationship_type, -- relationship type (new)
'parent', -- status id
P_WORKER_ID -- worker id
);
select
distinct
pdt_child.sup_id,
pdt_child.sub_id,
pdt_child.sub_leaf_flag_id
from PJI_FP_AGGR_XBS_T pdt_child
where 1=1
and pdt_child.sup_id = PRG_NODE.element_version_id
and pdt_child.sup_id <> pdt_child.sub_id
and pdt_child.worker_id = P_WORKER_ID
) LOOP
-- l_prg_temp_level --
select pdt_child1.sub_level
into l_prg_temp_level
from PJI_FP_AGGR_XBS_T pdt_child1
where 1=1
--and pdt_child1.struct_type = 'PRG'
and pdt_child1.sup_id = PRG_CHILDREN_NODE.sub_id
and pdt_child1.sub_id = PRG_CHILDREN_NODE.sub_id
and pdt_child1.worker_id = P_WORKER_ID;
select pvt_child1.project_id
into l_prj_temp_parent
from PA_PROJ_ELEMENT_VERSIONS pvt_child1
where 1=1
and pvt_child1.element_version_id = PRG_PARENT_NODE.object_id_from1;
select pvt_child2.proj_element_id
into l_prg_temp_sup_emt
from PA_PROJ_ELEMENT_VERSIONS pvt_child2
where 1=1
and pvt_child2.element_version_id = l_prg_temp_parent;
select pvt_child3.proj_element_id
into l_prg_temp_sub_emt
from PA_PROJ_ELEMENT_VERSIONS pvt_child3
where 1=1
and pvt_child3.element_version_id = PRG_CHILDREN_NODE.sub_id;
'PJI_PJP - Inserting PRG node child -'
|| ' element_version_id = '
|| PRG_NODE.element_version_id
|| ' sup_id = '
|| l_prg_temp_parent
|| ' sub_emt_id = '
|| l_prg_temp_sub_emt
|| ' sub_level = '
|| l_prg_temp_level,
-- || PRG_CHILDREN_NODE.sup_id,
null,
g_msg_level_low_detail
);
-- Insert PRG node's child --
insert
into PJI_FP_AGGR_XBS_T
(
struct_type,
prg_group,
struct_version_id,
sup_project_id,
sup_id,
sup_emt_id,
subro_id,
sub_id,
sub_emt_id,
sup_level,
sub_level,
sub_rollup_id,
sub_leaf_flag_id,
sub_leaf_flag,
status_id,
worker_id
)
values (
'PRG', -- structure type
PRG_NODE.prg_group, -- prg group
null, -- struct_version_id
l_prj_temp_parent, -- parent project id
l_prg_temp_parent, -- parent id
l_prg_temp_sup_emt, -- sup emt id
PRG_NODE.proj_element_id, -- immediate child id
PRG_CHILDREN_NODE.sub_id, -- child id
l_prg_temp_sub_emt, -- sub emt id
PRG_PARENT_NODE.prg_level, -- parent level
l_prg_temp_level, -- child level
null, -- child rollup id
PRG_CHILDREN_NODE.sub_leaf_flag_id, -- child leaf flag
l_prg_leaf_flag, -- child leaf flag
'children', -- status id
P_WORKER_ID -- worker id
);
select count(*)
into l_wbs_count
from PA_PROJ_ELEMENT_VERSIONS wvt_count
where 1=1
and wvt_count.object_type = 'PA_TASKS'
and wvt_count.proj_element_id in -- ###dummy###
(
select proj_element_id
from pa_proj_elements
where link_task_flag = 'N'
)
and wvt_count.parent_structure_version_id = P_WBS_VERSION_ID
and rownum = 1;
select max(wvt_level.wbs_level)
into l_wbs_level_id
from PA_PROJ_ELEMENT_VERSIONS wvt_level
where 1=1
and wvt_level.object_type = 'PA_TASKS'
and wvt_level.proj_element_id in -- ###dummy###
(
select proj_element_id
from pa_proj_elements
where link_task_flag = 'N'
)
and wvt_level.parent_structure_version_id = P_WBS_VERSION_ID;
select
distinct
sup_emt_id
into l_struct_emt_id
from pji_fp_aggr_xbs_t
where 1=1
and struct_type = 'PRG'
and sup_id = P_WBS_VERSION_ID
and worker_id = P_WORKER_ID;
select structure_sharing_code
into l_sharing_code
from pa_projects_all projects,
pa_proj_element_versions versions
where 1=1
and projects.project_id = versions.project_id
and versions.object_type = 'PA_STRUCTURES'
and versions.element_version_id = P_WBS_VERSION_ID;
'PJI_PJP - WBS Inserts - l_wbs_level_id = '
|| l_wbs_level_id,
null,
g_msg_level_high_detail
);
select wvt_nodes.project_id,
wvt_nodes.proj_element_id,
wvt_nodes.element_version_id,
wvt_nodes.parent_structure_version_id,
wvt_nodes.financial_task_flag -- ###financial###
from PA_PROJ_ELEMENT_VERSIONS wvt_nodes
where 1=1
and wvt_nodes.object_type = 'PA_TASKS'
and wvt_nodes.proj_element_id in -- ###dummy###
(
select proj_element_id
from pa_proj_elements
where link_task_flag = 'N'
)
and wvt_nodes.parent_structure_version_id = P_WBS_VERSION_ID
and wvt_nodes.wbs_level = l_wbs_level_id
) LOOP
-- -----------------------------------------------------
-- Check WBS node self --
-- Determine if the node to be inserted is a leaf
-- If the node to be inserted has not been inserted before,
-- then we know that the node is a leaf
select count(*)
into l_wbs_node_count
from PJI_FP_AGGR_XBS_T wdt_count
where wdt_count.sup_id = WBS_NODE.element_version_id
and wdt_count.worker_id = P_WORKER_ID
and rownum = 1;
'PJI_PJP - Inserting WBS node self - element_version_id = '
|| WBS_NODE.element_version_id,
null,
g_msg_level_low_detail
);
-- Insert WBS node self --
insert
into PJI_FP_AGGR_XBS_T
(
struct_type,
prg_group,
struct_emt_id,
struct_version_id,
sup_project_id,
sup_id,
sup_emt_id,
subro_id,
sub_id,
sub_emt_id,
sup_level,
sub_level,
sub_rollup_id,
sub_leaf_flag_id,
sub_leaf_flag,
relationship_type,
status_id,
worker_id
)
values (
'WBS', -- structure type
null, -- prg group
l_struct_emt_id, -- structure element id
P_WBS_VERSION_ID, -- structure version id
WBS_NODE.project_id, -- parent project id
WBS_NODE.element_version_id, -- parent id
WBS_NODE.proj_element_id, -- sup emt_id
null, -- immediate child id
WBS_NODE.element_version_id, -- child id
WBS_NODE.proj_element_id, -- sub emt_id
l_wbs_level_id, -- parent level
l_wbs_level_id, -- child level
null, -- child rollup id
l_wbs_leaf_flag_id, -- child leaf flag id
l_wbs_leaf_flag, -- child leaf flag
decode(l_sharing_code,
'SHARE_FULL', 'WF',
'SHARE_PARTIAL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'WF', 'LW'),
'SPLIT_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
'SPLIT_NO_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
'PJI$NULL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW')),
-- sub financial task flag -- ###financial###
'self', -- status id
P_WORKER_ID -- worker id
);
select count(*) -- ###parent_is_one###
into l_wbs_test_node
from PA_OBJECT_RELATIONSHIPS rel,
(
select element_version_id
from PA_PROJ_ELEMENT_VERSIONS
where 1=1
and WBS_LEVEL > 1
and element_version_id = WBS_NODE.element_version_id
) ver
where 1=1
and rel.OBJECT_TYPE_FROM = 'PA_TASKS'
and rel.OBJECT_TYPE_TO = 'PA_TASKS'
and rel.RELATIONSHIP_TYPE = 'S'
and ver.ELEMENT_VERSION_ID = rel.OBJECT_ID_to1 (+);
select wrt_parent.object_id_from1
into l_wbs_temp_parent
from PA_OBJECT_RELATIONSHIPS wrt_parent
where 1=1
and wrt_parent.object_id_to1 = WBS_NODE.element_version_id
and wrt_parent.object_type_from = 'PA_TASKS'
and wrt_parent.object_type_to = 'PA_TASKS'
and wrt_parent.relationship_type = 'S';
select wvt_parent1.proj_element_id
into l_wbs_temp_sup_emt
from PA_PROJ_ELEMENT_VERSIONS wvt_parent1
where 1=1
and wvt_parent1.element_version_id = l_wbs_temp_parent;
select wvt_parent2.proj_element_id
into l_wbs_temp_sub_emt
from PA_PROJ_ELEMENT_VERSIONS wvt_parent2
where 1=1
and wvt_parent2.element_version_id = WBS_NODE.element_version_id;
'PJI_PJP - Inserting WBS node parent - l_wbs_temp_parent = '
|| l_wbs_temp_parent,
null,
g_msg_level_low_detail
);
-- Insert WBS node's parent --
insert
into PJI_FP_AGGR_XBS_T
(
struct_type,
prg_group,
struct_emt_id,
struct_version_id,
sup_project_id,
sup_id,
sup_emt_id,
subro_id,
sub_id,
sub_emt_id,
sup_level,
sub_level,
sub_rollup_id,
sub_leaf_flag_id,
sub_leaf_flag,
relationship_type,
status_id,
worker_id
)
values (
'WBS', -- structure type
null, -- prg group
l_struct_emt_id, -- structure element id
P_WBS_VERSION_ID, -- structure version id
WBS_NODE.project_id, -- parent project id
l_wbs_temp_parent, -- parent id
l_wbs_temp_sup_emt, -- sup_emt_id
WBS_NODE.proj_element_id, -- immediate child id
WBS_NODE.element_version_id, -- child id
l_wbs_temp_sub_emt, -- sub_emt_id
l_wbs_level_id - 1, -- parent level
l_wbs_level_id, -- child level
null, -- child rollup id
l_wbs_leaf_flag_id, -- child leaf flag id
l_wbs_leaf_flag, -- child leaf flag
decode(l_sharing_code,
'SHARE_FULL', 'WF',
'SHARE_PARTIAL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'WF', 'LW'),
'SPLIT_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
'SPLIT_NO_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
'PJI$NULL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW')),
-- sub financial task flag -- ###financial###
'parent', -- status id
P_WORKER_ID -- worker id
);
select wdt_child.sup_id,
wdt_child.sub_id,
wdt_child.sub_leaf_flag_id,
wdt_child.relationship_type
from PJI_FP_AGGR_XBS_T wdt_child
where 1=1
and wdt_child.sup_id = WBS_NODE.element_version_id
and wdt_child.sup_id <> wdt_child.sub_id
and wdt_child.worker_id = P_WORKER_ID
) LOOP
-- l_wbs_temp_level --
select wdt_child1.sub_level
into l_wbs_temp_level
from PJI_FP_AGGR_XBS_T wdt_child1
where 1=1
and wdt_child1.sup_id = WBS_CHILDREN_NODE.sub_id
and wdt_child1.sup_id = wdt_child1.sub_id
and wdt_child1.worker_id = P_WORKER_ID;
select wvt_child1.proj_element_id
into l_wbs_temp_sup_emt
from PA_PROJ_ELEMENT_VERSIONS wvt_child1
where 1=1
and wvt_child1.element_version_id = l_wbs_temp_parent;
select wvt_child2.proj_element_id
into l_wbs_temp_sub_emt
from PA_PROJ_ELEMENT_VERSIONS wvt_child2
where 1=1
and wvt_child2.element_version_id = WBS_CHILDREN_NODE.sub_id;
'PJI_PJP - Inserting WBS node child - sup_id = '
|| WBS_CHILDREN_NODE.sup_id,
null,
g_msg_level_low_detail
);
-- Insert WBS node's child --
insert
into PJI_FP_AGGR_XBS_T
(
struct_type,
prg_group,
struct_emt_id,
struct_version_id,
sup_project_id,
sup_id,
sup_emt_id,
subro_id,
sub_id,
sub_emt_id,
sup_level,
sub_level,
sub_rollup_id,
sub_leaf_flag_id,
sub_leaf_flag,
relationship_type,
status_id,
worker_id
)
values (
'WBS', -- structure type
null, -- prg group
l_struct_emt_id, -- structure element id
P_WBS_VERSION_ID, -- structure version id
WBS_NODE.project_id, -- parent project id
l_wbs_temp_parent, -- parent id
l_wbs_temp_sup_emt, -- sup emt_id
WBS_NODE.proj_element_id, -- immediate child id
WBS_CHILDREN_NODE.sub_id, -- child id
l_wbs_temp_sub_emt, -- sub emt_id
l_wbs_level_id - 1, -- parent level
l_wbs_temp_level, -- child level
null, -- child rollup id
WBS_CHILDREN_NODE.sub_leaf_flag_id, -- child leaf flag id
l_wbs_leaf_flag, -- child leaf flag
decode(l_sharing_code,
'SHARE_FULL', 'WF',
'SHARE_PARTIAL', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'WF', 'LW'),
'SPLIT_MAPPING', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'LF', 'LW'),
'SPLIT_NO_MAPPING', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'LF', 'LW'),
'PJI$NULL', decode(nvl(WBS_CHILDREN_NODE.relationship_type, 'N'), 'Y', 'LF', 'LW')),
-- sub financial task flag -- ###financial###
'children', -- status id
P_WORKER_ID -- worker id
);
select count(*)
into l_struct_emt_id_count
from pa_proj_element_versions
where 1=1
and element_version_id = P_WBS_VERSION_ID
and rownum = 1;
select
distinct
proj_element_id
into l_struct_emt_id
from pa_proj_element_versions
where 1=1
and element_version_id = P_WBS_VERSION_ID;
'PJI_PJP - Inserting XBS node self - sup_id = '
|| P_WBS_VERSION_ID,
null,
g_msg_level_low_detail
);
-- Insert XBS node --
insert
into PJI_FP_AGGR_XBS_T
(
struct_type,
prg_group,
struct_version_id,
sup_project_id,
sup_id,
sup_emt_id,
subro_id,
sub_id,
sub_emt_id,
sup_level,
sub_level,
sub_rollup_id,
sub_leaf_flag_id,
sub_leaf_flag,
relationship_type,
status_id,
worker_id
)
values (
'XBS', -- structure type
null, -- prg group
P_WBS_VERSION_ID, -- structure version id
WBS_NODE.project_id, -- parent project id
P_WBS_VERSION_ID, -- parent id
l_struct_emt_id, -- sup emt_id
null, -- immediate child id
WBS_NODE.element_version_id, -- child id
WBS_NODE.proj_element_id, -- sub emt_id
0, -- parent level
l_wbs_level_id, -- child level
null, -- child rollup id
l_wbs_leaf_flag_id, -- child leaf flag id
l_wbs_leaf_flag, -- child leaf flag
decode(l_sharing_code,
'SHARE_FULL', 'WF',
'SHARE_PARTIAL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'WF', 'LW'),
'SPLIT_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
'SPLIT_NO_MAPPING', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW'),
'PJI$NULL', decode(nvl(WBS_NODE.financial_task_flag, 'N'), 'Y', 'LF', 'LW')),
-- sub financial task flag -- ###financial###
'self', -- status id
P_WORKER_ID -- worker id
);
select max(pvt_level.rbs_level)
into l_rbs_level_id
from PA_RBS_ELEMENTS pvt_level
where 1=1
and pvt_level.user_created_flag = 'N';
select max(pvt_level.rbs_level)
into l_rbs_level_id
from PA_RBS_ELEMENTS pvt_level,
(
select distinct event_type, event_object
from PJI_PA_PROJ_EVENTS_LOG
where 1=1
and event_type = 'PJI_RBS_CHANGE'
and worker_id = P_WORKER_ID
) log
where 1=1
and pvt_level.user_created_flag = 'N'
and pvt_level.rbs_version_id = log.event_object;
select max(pvt_level.rbs_level)
into l_rbs_level_id
from PA_RBS_ELEMENTS pvt_level
where 1=1
and pvt_level.user_created_flag = 'N'
and pvt_level.rbs_version_id = P_RBS_VERSION_ID;
'PJI_PJP - RBS Inserts - l_rbs_level_id = '
|| l_rbs_level_id,
null,
g_msg_level_high_detail
);
select
distinct
-- pvt_nodes.project_id,
pvt_nodes1.rbs_version_id, -- pvt_nodes.proj_element_id,
pvt_nodes1.rbs_element_id, -- pvt_nodes.element_version_id,
pvt_nodes1.parent_element_id -- pvt_nodes.parent_structure_version_id,
-- pvt_nodes.rbs_group
from PA_RBS_ELEMENTS pvt_nodes1,
pji_pjp_proj_batch_map map,
pa_rbs_prj_assignments assignments
where 1=1
and P_EXTRACTION_TYPE = 'FULL'
and pvt_nodes1.user_created_flag = 'N'
and (
pvt_nodes1.rbs_level = l_rbs_level_id
or
(
l_rbs_level_id = 1
and
pvt_nodes1.rbs_level is null
)
)
and map.project_id = assignments.project_id
and assignments.rbs_version_id = pvt_nodes1.rbs_version_id
UNION ALL
select
distinct
-- pvt_nodes.project_id,
pvt_nodes2.rbs_version_id, -- pvt_nodes.proj_element_id,
pvt_nodes2.rbs_element_id, -- pvt_nodes.element_version_id,
pvt_nodes2.parent_element_id -- pvt_nodes.parent_structure_version_id,
-- pvt_nodes.rbs_group
from PA_RBS_ELEMENTS pvt_nodes2,
(
select
distinct
log1.event_type, log1.event_object
from PJI_PA_PROJ_EVENTS_LOG log1
where 1=1
and log1.event_type = 'PJI_RBS_CHANGE'
and worker_id = P_WORKER_ID
) log11
where 1=1
and (
P_EXTRACTION_TYPE = 'INCREMENTAL'
or
P_EXTRACTION_TYPE = 'PARTIAL'
or
P_EXTRACTION_TYPE = 'RBS'
)
and pvt_nodes2.user_created_flag = 'N'
and pvt_nodes2.rbs_version_id = log11.event_object
and pvt_nodes2.rbs_level = l_rbs_level_id
UNION ALL
select
distinct
-- pvt_nodes.project_id,
pvt_nodes1.rbs_version_id, -- pvt_nodes.proj_element_id,
pvt_nodes1.rbs_element_id, -- pvt_nodes.element_version_id,
pvt_nodes1.parent_element_id -- pvt_nodes.parent_structure_version_id,
-- pvt_nodes.rbs_group
from PA_RBS_ELEMENTS pvt_nodes1
where 1=1
and P_EXTRACTION_TYPE = 'UPGRADE'
and pvt_nodes1.user_created_flag = 'N'
and (
pvt_nodes1.rbs_level = l_rbs_level_id
or
(
l_rbs_level_id = 1
and
pvt_nodes1.rbs_level is null
)
)
and pvt_nodes1.rbs_version_id = P_RBS_VERSION_ID
) LOOP
-- -----------------------------------------------------
-- Check RBS node self --
-- Determine if the node to be inserted is a leaf
-- If the node to be inserted has not been inserted before,
-- then we know that the node is a leaf
select count(*)
into l_rbs_node_count
from PJI_FP_AGGR_RBS pdt_count
where 1=1
and pdt_count.sup_id = RBS_NODE.rbs_element_id
and pdt_count.worker_id = P_WORKER_ID
and rownum = 1;
'PJI_PJP - Inserting RBS node self - rbs_element_id = '
|| RBS_NODE.rbs_element_id,
null,
g_msg_level_low_detail
);
-- Insert RBS node self --
insert
into PJI_FP_AGGR_RBS
(
struct_version_id,
sup_id,
subro_id,
sub_id,
sup_level,
sub_level,
sub_leaf_flag_id,
sub_leaf_flag,
status_id,
worker_id
)
values (
RBS_NODE.rbs_version_id, -- rbs version id
RBS_NODE.rbs_element_id, -- parent id
null, -- immediate child id
RBS_NODE.rbs_element_id, -- child id
l_rbs_level_id, -- parent level
l_rbs_level_id, -- child level
l_rbs_leaf_flag_id, -- child leaf flag id
l_rbs_leaf_flag, -- child leaf flag
'self', -- status id
P_WORKER_ID -- worker id
);
select
distinct
prt_parent.parent_element_id
from PA_RBS_ELEMENTS prt_parent
where 1=1
and prt_parent.user_created_flag = 'N'
and prt_parent.rbs_element_id = RBS_NODE.rbs_element_id -- prt_parent.child_id
) LOOP
-- l_rbs_temp_parent --
l_rbs_temp_parent := RBS_PARENT_NODE.parent_element_id;
'PJI_PJP - Inserting RBS node parent - l_rbs_temp_parent - = '
|| l_rbs_temp_parent,
null,
g_msg_level_low_detail
);
-- Insert RBS node's parent --
insert
into PJI_FP_AGGR_RBS
(
struct_version_id,
sup_id,
subro_id,
sub_id,
sup_level,
sub_level,
sub_leaf_flag_id,
sub_leaf_flag,
status_id,
worker_id
)
values (
RBS_NODE.rbs_version_id, -- rbs version id
l_rbs_temp_parent, -- parent id
RBS_NODE.rbs_element_id, -- immediate child id
RBS_NODE.rbs_element_id, -- child id
l_rbs_level_id - 1, -- parent level
l_rbs_level_id, -- child level
l_rbs_leaf_flag_id, -- child leaf flag id
l_rbs_leaf_flag, -- child leaf flag
'parent', -- status id
P_WORKER_ID -- worker id
);
select
distinct
pdt_child.sup_id,
pdt_child.sub_id,
pdt_child.sub_leaf_flag_id
from PJI_FP_AGGR_RBS pdt_child
where 1=1
and pdt_child.sup_id = RBS_NODE.rbs_element_id
and pdt_child.sup_id <> pdt_child.sub_id
and pdt_child.worker_id = P_WORKER_ID
) LOOP
-- l_rbs_temp_level --
select pdt_child1.sub_level
into l_rbs_temp_level
from PJI_FP_AGGR_RBS pdt_child1
where 1=1
and pdt_child1.sup_id = RBS_CHILDREN_NODE.sub_id
and pdt_child1.sup_id = pdt_child1.sub_id
and pdt_child1.worker_id = P_WORKER_ID;
'PJI_PJP - Inserting RBS node child - sup_id = '
|| RBS_CHILDREN_NODE.sup_id,
null,
g_msg_level_low_detail
);
-- Insert RBS node's child --
insert
into PJI_FP_AGGR_RBS
(
struct_version_id,
sup_id,
subro_id,
sub_id,
sup_level,
sub_level,
sub_leaf_flag_id,
sub_leaf_flag,
status_id,
worker_id
)
values (
RBS_NODE.rbs_version_id, -- rbs version id
l_rbs_temp_parent, -- parent id
RBS_NODE.rbs_element_id, -- immediate child id
RBS_CHILDREN_NODE.sub_id, -- child id
l_rbs_level_id - 1, -- parent level
l_rbs_temp_level, -- child level
RBS_CHILDREN_NODE.sub_leaf_flag_id, -- child leaf flag
l_rbs_leaf_flag, -- child leaf flag
'children', -- status id
P_WORKER_ID -- worker id
);
select max(pvt_level.rbs_level)
into l_rbs_level_id
from PA_RBS_ELEMENTS pvt_level
where 1=1
and pvt_level.user_created_flag = 'N'
and pvt_level.rbs_version_id = P_RBS_VERSION_ID;
'PJI_PJP - RBS Inserts - l_rbs_level_id = '
|| l_rbs_level_id,
null,
g_msg_level_high_detail
);
select
distinct
-- pvt_nodes.project_id,
pvt_nodes1.rbs_version_id, -- pvt_nodes.proj_element_id,
pvt_nodes1.rbs_element_id, -- pvt_nodes.element_version_id,
pvt_nodes1.parent_element_id -- pvt_nodes.parent_structure_version_id,
-- pvt_nodes.rbs_group
from PA_RBS_ELEMENTS pvt_nodes1
where 1=1
and pvt_nodes1.user_created_flag = 'N'
and (
pvt_nodes1.rbs_level = l_rbs_level_id
or
(
l_rbs_level_id = 1
and
pvt_nodes1.rbs_level is null
)
)
and pvt_nodes1.rbs_version_id = P_RBS_VERSION_ID
) LOOP
-- -----------------------------------------------------
-- Check RBS node self --
-- Determine if the node to be inserted is a leaf
-- If the node to be inserted has not been inserted before,
-- then we know that the node is a leaf
select count(*)
into l_rbs_node_count
from PJI_FP_AGGR_RBS_T pdt_count
where 1=1
and pdt_count.sup_id = RBS_NODE.rbs_element_id
and pdt_count.worker_id = P_WORKER_ID
and rownum = 1;
'PJI_PJP - Inserting RBS node self - rbs_element_id = '
|| RBS_NODE.rbs_element_id,
null,
g_msg_level_low_detail
);
-- Insert RBS node self --
insert
into PJI_FP_AGGR_RBS_T
(
struct_version_id,
sup_id,
subro_id,
sub_id,
sup_level,
sub_level,
sub_leaf_flag_id,
sub_leaf_flag,
status_id,
worker_id
)
values (
RBS_NODE.rbs_version_id, -- rbs version id
RBS_NODE.rbs_element_id, -- parent id
null, -- immediate child id
RBS_NODE.rbs_element_id, -- child id
l_rbs_level_id, -- parent level
l_rbs_level_id, -- child level
l_rbs_leaf_flag_id, -- child leaf flag id
l_rbs_leaf_flag, -- child leaf flag
'self', -- status id
P_WORKER_ID -- worker id
);
select
distinct
prt_parent.parent_element_id
from PA_RBS_ELEMENTS prt_parent
where 1=1
and prt_parent.user_created_flag = 'N'
and prt_parent.rbs_element_id = RBS_NODE.rbs_element_id -- prt_parent.child_id
) LOOP
-- l_rbs_temp_parent --
l_rbs_temp_parent := RBS_PARENT_NODE.parent_element_id;
'PJI_PJP - Inserting RBS node parent - l_rbs_temp_parent - = '
|| l_rbs_temp_parent,
null,
g_msg_level_low_detail
);
-- Insert RBS node's parent --
insert
into PJI_FP_AGGR_RBS_T
(
struct_version_id,
sup_id,
subro_id,
sub_id,
sup_level,
sub_level,
sub_leaf_flag_id,
sub_leaf_flag,
status_id,
worker_id
)
values (
RBS_NODE.rbs_version_id, -- rbs version id
l_rbs_temp_parent, -- parent id
RBS_NODE.rbs_element_id, -- immediate child id
RBS_NODE.rbs_element_id, -- child id
l_rbs_level_id - 1, -- parent level
l_rbs_level_id, -- child level
l_rbs_leaf_flag_id, -- child leaf flag id
l_rbs_leaf_flag, -- child leaf flag
'parent', -- status id
P_WORKER_ID -- worker id
);
select
distinct
pdt_child.sup_id,
pdt_child.sub_id,
pdt_child.sub_leaf_flag_id
from PJI_FP_AGGR_RBS_T pdt_child
where 1=1
and pdt_child.sup_id = RBS_NODE.rbs_element_id
and pdt_child.sup_id <> pdt_child.sub_id
and pdt_child.worker_id = P_WORKER_ID
) LOOP
-- l_rbs_temp_level --
select pdt_child1.sub_level
into l_rbs_temp_level
from PJI_FP_AGGR_RBS_T pdt_child1
where 1=1
and pdt_child1.sup_id = RBS_CHILDREN_NODE.sub_id
and pdt_child1.sup_id = pdt_child1.sub_id
and pdt_child1.worker_id = P_WORKER_ID;
'PJI_PJP - Inserting RBS node child - sup_id = '
|| RBS_CHILDREN_NODE.sup_id,
null,
g_msg_level_low_detail
);
-- Insert RBS node's child --
insert
into PJI_FP_AGGR_RBS_T
(
struct_version_id,
sup_id,
subro_id,
sub_id,
sup_level,
sub_level,
sub_leaf_flag_id,
sub_leaf_flag,
status_id,
worker_id
)
values (
RBS_NODE.rbs_version_id, -- rbs version id
l_rbs_temp_parent, -- parent id
RBS_NODE.rbs_element_id, -- immediate child id
RBS_CHILDREN_NODE.sub_id, -- child id
l_rbs_level_id - 1, -- parent level
l_rbs_temp_level, -- child level
RBS_CHILDREN_NODE.sub_leaf_flag_id, -- child leaf flag
l_rbs_leaf_flag, -- child leaf flag
'children', -- status id
P_WORKER_ID -- worker id
);
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
insert
into PA_XBS_DENORM
(
struct_type,
prg_group,
struct_emt_id,
struct_version_id,
sup_project_id,
sup_id,
sup_emt_id,
subro_id,
sub_id,
sub_emt_id,
sup_level,
sub_level,
sub_rollup_id,
sub_leaf_flag,
relationship_type,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
select
distinct
interim.struct_type,
interim.prg_group,
interim.struct_emt_id,
interim.struct_version_id,
interim.sup_project_id,
interim.sup_id,
interim.sup_emt_id,
interim.subro_id,
interim.sub_id,
interim.sub_emt_id,
interim.sup_level,
interim.sub_level,
interim.sub_rollup_id,
interim.sub_leaf_flag,
interim.relationship_type,
l_last_update_date,
l_last_updated_by,
l_creation_date,
l_created_by,
l_last_update_login
from PJI_FP_AGGR_XBS interim,
PA_XBS_DENORM denorm
where 1=1
and interim.worker_id = p_worker_id
and nvl(interim.struct_type, -1) = nvl(denorm.struct_type (+), -1)
and nvl(interim.prg_group, -1) = nvl(denorm.prg_group (+), -1)
and nvl(interim.struct_emt_id, -1) = nvl(denorm.struct_emt_id (+), -1)
and nvl(interim.struct_version_id, -1) = nvl(denorm.struct_version_id (+), -1)
and nvl(interim.sup_project_id, -1) = nvl(denorm.sup_project_id (+), -1)
and nvl(interim.sup_id, -1) = nvl(denorm.sup_id (+), -1)
and nvl(interim.sup_emt_id, -1) = nvl(denorm.sup_emt_id (+), -1)
and nvl(interim.subro_id, -1) = nvl(denorm.subro_id (+), -1)
and nvl(interim.sub_id, -1) = nvl(denorm.sub_id (+), -1)
and nvl(interim.sub_emt_id, -1) = nvl(denorm.sub_emt_id (+), -1)
and nvl(interim.sup_level, -1) = nvl(denorm.sup_level (+), -1)
and nvl(interim.sub_level, -1) = nvl(denorm.sub_level (+), -1)
and nvl(interim.sub_rollup_id, -1) = nvl(denorm.sub_rollup_id (+), -1)
and nvl(interim.sub_leaf_flag, -1) = nvl(denorm.sub_leaf_flag (+), -1)
and nvl(interim.relationship_type, -1) = nvl(denorm.relationship_type (+), -1)
and denorm.struct_type is null
order by
interim.struct_version_id,
interim.sup_id,
interim.sub_id;
insert
into PA_XBS_DENORM
(
struct_type,
prg_group,
struct_emt_id,
struct_version_id,
sup_project_id,
sup_id,
sup_emt_id,
subro_id,
sub_id,
sub_emt_id,
sup_level,
sub_level,
sub_rollup_id,
sub_leaf_flag,
relationship_type,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
select
distinct
interim.struct_type,
interim.prg_group,
interim.struct_emt_id,
interim.struct_version_id,
interim.sup_project_id,
interim.sup_id,
interim.sup_emt_id,
interim.subro_id,
interim.sub_id,
interim.sub_emt_id,
interim.sup_level,
interim.sub_level,
interim.sub_rollup_id,
interim.sub_leaf_flag,
interim.relationship_type,
l_last_update_date,
l_last_updated_by,
l_creation_date,
l_created_by,
l_last_update_login
from PJI_FP_AGGR_XBS_T interim
where interim.worker_id = p_worker_id
order by
interim.struct_version_id,
interim.sup_id,
interim.sub_id;
insert
into PA_XBS_DENORM
(
struct_type,
prg_group,
struct_emt_id,
struct_version_id,
sup_project_id,
sup_id,
sup_emt_id,
subro_id,
sub_id,
sub_emt_id,
sup_level,
sub_level,
sub_rollup_id,
sub_leaf_flag,
relationship_type,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
select
distinct
interim.struct_type,
interim.prg_group,
interim.struct_emt_id,
interim.struct_version_id,
interim.sup_project_id,
interim.sup_id,
interim.sup_emt_id,
interim.subro_id,
interim.sub_id,
interim.sub_emt_id,
interim.sup_level,
interim.sub_level,
interim.sub_rollup_id,
interim.sub_leaf_flag,
interim.relationship_type,
l_last_update_date,
l_last_updated_by,
l_creation_date,
l_created_by,
l_last_update_login
from PJI_FP_AGGR_XBS interim
where interim.worker_id = p_worker_id
order by
interim.struct_version_id,
interim.sup_id,
interim.sub_id;
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
insert
into PA_RBS_DENORM
(
struct_version_id,
sup_id,
subro_id,
sub_id,
sup_level,
sub_level,
sub_leaf_flag,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
select
interim.struct_version_id,
interim.sup_id,
interim.subro_id,
interim.sub_id,
interim.sup_level,
interim.sub_level,
interim.sub_leaf_flag,
l_last_update_date,
l_last_updated_by,
l_creation_date,
l_created_by,
l_last_update_login
from PJI_FP_AGGR_RBS interim,
PA_RBS_DENORM denorm
where 1=1
and interim.worker_id = p_worker_id
and nvl(interim.struct_version_id, -1) = nvl(denorm.struct_version_id (+), -1)
and nvl(interim.sup_id, -1) = nvl(denorm.sup_id (+), -1)
and nvl(interim.subro_id, -1) = nvl(denorm.subro_id (+), -1)
and nvl(interim.sub_id, -1) = nvl(denorm.sub_id (+), -1)
and nvl(interim.sup_level, -1) = nvl(denorm.sup_level (+), -1)
and nvl(interim.sub_level, -1) = nvl(denorm.sub_level (+), -1)
and nvl(interim.sub_leaf_flag, -1) = nvl(denorm.sub_leaf_flag (+), -1)
and denorm.struct_version_id is null;
insert
into PA_RBS_DENORM
(
struct_version_id,
sup_id,
subro_id,
sub_id,
sup_level,
sub_level,
sub_leaf_flag,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
select
interim.struct_version_id,
interim.sup_id,
interim.subro_id,
interim.sub_id,
interim.sup_level,
interim.sub_level,
interim.sub_leaf_flag,
l_last_update_date,
l_last_updated_by,
l_creation_date,
l_created_by,
l_last_update_login
from PJI_FP_AGGR_RBS_T interim
where interim.worker_id = p_worker_id;
insert
into PA_RBS_DENORM
(
struct_version_id,
sup_id,
subro_id,
sub_id,
sup_level,
sub_level,
sub_leaf_flag,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
select
interim.struct_version_id,
interim.sup_id,
interim.subro_id,
interim.sub_id,
interim.sup_level,
interim.sub_level,
interim.sub_leaf_flag,
l_last_update_date,
l_last_updated_by,
l_creation_date,
l_created_by,
l_last_update_login
from PJI_FP_AGGR_RBS interim
where interim.worker_id = p_worker_id;
delete
from PJI_FP_AGGR_XBS_T
where worker_id = p_worker_id;
delete
from PJI_FP_AGGR_XBS
where worker_id = p_worker_id;
delete
from PJI_FP_AGGR_RBS_T
where worker_id = p_worker_id;
delete
from PJI_FP_AGGR_RBS
where worker_id = p_worker_id;