[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
delete from pa_olap_xbs_denorm_TMP ;
delete from pa_olap_wbs_header;
delete from pji_rep_xbs_denorm where project_id = p_project_id ;
insert into pa_olap_xbs_denorm_TMP (
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,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
STRUCT_EMT_ID,RELATIONSHIP_TYPE )
select 'PRG',to_number(null), to_number(null), e.project_id ,e.element_version_id , e.proj_element_id ,to_number(null), e.element_version_id ,
e.proj_element_id , 1,1,e.proj_element_id, 'Y',
sysdate , -1 , sysdate , -1 , - 1,
to_number(null), to_number(null)
from pa_proj_element_versions e where project_id = p_project_id and object_type = 'PA_STRUCTURES';
insert into pa_olap_wbs_header (
PROJECT_ID,PLAN_VERSION_ID,WBS_VERSION_ID,
WP_FLAG,CB_FLAG,CO_FLAG,LOCK_FLAG,
PLAN_TYPE_ID,MIN_TXN_DATE,MAX_TXN_DATE,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
PLAN_TYPE_CODE)
select bv.project_id , bv.budget_version_id
, DECODE ( NVL(bv.wp_version_flag, 'N')
, 'Y', bv.project_structure_version_id
, PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(bv.project_id) -- -9999 --
) wbs_struct_version_id
, NVL(bv.wp_version_flag, 'N')
, decode(bv.current_flag|| DECODE(bv.baselined_date, NULL, 'N', 'Y'),'YY','Y','N' )
, decode(bv.current_original_flag|| DECODE(bv.baselined_date, NULL, 'N', 'Y'),'YY','Y','N' )
,'N'
, fpo.fin_plan_type_id plan_type_id
, trunc(sysdate) , trunc(sysdate)
, sysdate , -1 , sysdate , -1 , -1
, DECODE (bv.version_type, 'COST' ,'C' , 'REVENUE', 'R', 'A') plan_type_code /* 4471527 */
FROM
( select p_project_id project_id from dual ) map,
-- pji_pjp_rbs_header rhd,
pa_budget_versions bv,
pa_proj_fp_options fpo,
-- pji_pa_proj_events_log pel,
pa_projects_all ppa,
pa_rbs_versions_b rvb --Added for bug#5728852
WHERE 1=1
AND ppa.project_id = map.project_id
AND bv.version_type is not NULL -- COST, REVENUE, etc. Should not be null.
AND bv.fin_plan_type_id is not NULL -- Old budgets model data is not picked up with this condition.
AND fpo.project_id = map.project_id
AND bv.fin_plan_type_id = fpo.fin_plan_type_id
AND bv.budget_version_id = fpo.fin_plan_version_id
AND fpo.fin_plan_option_level_code = 'PLAN_VERSION' -- Other values are: plan type and project.
AND bv.version_type IN ( 'ALL' , 'COST' , 'REVENUE')
AND fpo.rbs_version_id = rvb.rbs_version_id
AND map.project_id = bv.project_id ;
select proj_element_id into p_elem_id from pa_proj_element_versions
where element_version_id = t_element_version_id;
INSERT INTO pji_rep_xbs_denorm
(SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
SELECT p_project_id ,'N',p_project_id,-1,struct_ver.proj_element_id,
'N',struct_ver.NAME,'Y','Y' ,
SYSDATE, SYSDATE, 1, 1,
0 ,t_element_version_id, -1,t_element_version_id, 'WF'
FROM pa_proj_elem_ver_structure struct_ver
WHERE 1=1
AND struct_ver.project_id = p_project_id
AND struct_ver.element_version_id = t_element_version_id;
INSERT INTO pji_rep_xbs_denorm
(SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
SELECT p_project_id,'N',p_project_id,struct_ver.proj_element_id,
struct_ver.proj_element_id,'N',struct_ver.NAME,'N','N' ,
SYSDATE, SYSDATE, 1, 1,
0 ,t_element_version_id, 0, t_element_version_id, 'WF'
FROM pa_proj_elem_ver_structure struct_ver
WHERE 1=1
AND struct_ver.project_id = p_project_id
AND struct_ver.element_version_id = t_element_version_id;
INSERT INTO pji_rep_xbs_denorm
(SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
SELECT t1.project_id , 'N', t1.project_id , nvl(t1.parent_task_id , p_elem_id ) parent_task_id ,
t1.task_id ,'N' ,emt.name,'Y' ,decode(nvl(t2.parent_task_id ,-1) , -1 , 'N', 'Y' ),
SYSDATE, SYSDATE, 1, 1,
0 ,t_element_version_id, ver.display_sequence,t_element_version_id, 'WF'
FROM pa_tasks t1 , pa_proj_elements emt,pa_proj_element_versions ver,
( select distinct parent_task_id from pa_tasks t3 where t3.project_id = p_project_id ) t2
where t1.project_id = p_project_id
and t1.task_id = t2.parent_task_id (+)
AND t1.task_id = emt.PROJ_ELEMENT_ID
AND ver.project_id =t1.project_id
AND ver.parent_structure_version_id = t_element_version_id
AND ver.object_type = 'PA_TASKS'
AND ver.proj_element_id = t1.task_id;
-- Insert into event log for the project structure change of current project
FORALL l_i IN p_wbs_version_id_tbl.FIRST..p_wbs_version_id_tbl.LAST
INSERT INTO pa_pji_proj_events_log
(event_type,event_id, event_object, operation_type, status, last_update_date
, last_updated_by, creation_date, created_by, last_update_login, attribute1, attribute2)
SELECT 'STRUCT_CHANGE',pa_pji_proj_events_log_s.NEXTVAL,'-99','X','X',SYSDATE,
Fnd_Global.USER_ID,SYSDATE,Fnd_Global.USER_ID,Fnd_Global.LOGIN_ID,p_wbs_version_id_tbl(l_i),'N'
FROM dual WHERE exists (
SELECT ppa.project_id from PA_PROJECTS_ALL ppa, PA_PROJ_ELEM_VER_STRUCTURE ppevs WHERE
ppa.project_id = ppevs.project_id AND
ppevs.ELEMENT_VERSION_ID = p_wbs_version_id_tbl(l_i) AND
ppa.template_flag <> 'Y'
)
and not exists --changes start for 8738137
(select 'Y'
from pa_pji_proj_events_log
where event_type = 'STRUCT_CHANGE'
and event_object = '-99'
and attribute1 = to_char(p_wbs_version_id_tbl(l_i)) -- bug 13109678 : added to_char
and attribute2 ='N'); --changes end for 8738137
-- Insert into event log for the program structure change for all parent project and current project
FORALL l_i IN p_wbs_version_id_tbl.FIRST..p_wbs_version_id_tbl.LAST
INSERT INTO pa_pji_proj_events_log
(event_type,event_id, event_object, operation_type, status, last_update_date
, last_updated_by, creation_date, created_by, last_update_login, attribute1, attribute2)
SELECT
'STRUCT_CHANGE',pa_pji_proj_events_log_s.NEXTVAL,'-99','X','X',SYSDATE,
Fnd_Global.USER_ID,SYSDATE,Fnd_Global.USER_ID,Fnd_Global.LOGIN_ID,sup_id,'Y'
FROM
(
select /*+ ordered */ -- bug 13109678 : removed index hint
distinct(prg.SUP_ID) SUP_ID
from
PJI_XBS_DENORM prg,
PA_PROJECTS_ALL prj
where
prg.STRUCT_VERSION_ID is null and
prg.SUB_ID = p_wbs_version_id_tbl(l_i) and
prg.SUP_PROJECT_ID = prj.PROJECT_ID and
prj.SYS_PROGRAM_FLAG = 'Y' and
prj.template_flag <> 'Y' and not exists
(select 'Y' --changes start for 8738137
from pa_pji_proj_events_log
where event_type = 'STRUCT_CHANGE'
and event_object = '-99'
and attribute1 = to_char(prg.sup_id) -- bug 13109678 : added to_char
and attribute2 = 'Y') --changes end for 8738137
);
l_update_flag BOOLEAN;
SELECT object_id , LEVEL +1,
SYS_CONNECT_BY_PATH(object_id, '/') connect_path,
connect_by_isleaf leaf_node
FROM pa_ppr_obj_tmp_view r
-- OLAP_BUG
-- START WITH r.parent_object_id = p_element_version_id CONNECT BY
START WITH r.parent_object_id = -1 CONNECT BY
PRIOR r.object_id = r.parent_object_id ;
select nvl(p.pjt_rollup_enabled_flag,'N'), p.cbs_enable_flag, ver.proj_element_id -- SCOPE_00 Get cbs_enabled_flag
into l_pjt_rollup_flag, l_cbs_enabled_flag, l_new_elem_id
from pa_projects_all p , PA_PROJ_ELEMENT_VERSIONS ver
where p.project_id = p_project_id and p.project_id = ver.project_id
and ver.element_version_id = p_element_version_id ;
/*select nvl(p.pjt_rollup_enabled_flag,'N'), s.proj_element_id , s.element_version_id
into l_pjt_rollup_flag, l_new_elem_id , l_struct_ver_id
from pa_projects_all p , pa_proj_elem_ver_structure s, pa_proj_structure_types ppst, pa_structure_types pst
where p.project_id = p_project_id
and s.project_id = p.project_id
and s.proj_element_id = ppst.proj_element_id
and ppst.structure_type_id = pst.structure_type_id
and s.current_flag = 'Y'
and pst.structure_type_class_code = 'FINANCIAL';*/
SELECT COUNT(*)
INTO l_count
FROM pji_rep_xbs_denorm
WHERE sup_project_id = p_project_id
AND prg_flag = p_prg_flag
AND wbs_version_id = p_element_version_id
AND ROWNUM=1;
DELETE FROM pa_pji_proj_events_log
WHERE attribute1 = l_element_version_id_str
AND event_object = '-99'
AND attribute2 = p_prg_flag
AND event_type = 'STRUCT_CHANGE';
l_update_flag := TRUE;
l_update_flag := FALSE;
SELECT COUNT(*)
INTO l_new_count
FROM pa_ppr_obj_tmp_view
WHERE object_id = l_new_elem_id; -- Bug 16507592 ;
delete from pa_ppr_obj_tmp_view;
insert into pa_ppr_obj_tmp_view ( object_id , parent_object_id )
select /*+ ordered */
-- OLAP_BUG
-- a.proj_element_id , nvl(c.proj_element_id,p_element_version_id)
a.proj_element_id , nvl(c.proj_element_id,l_new_elem_id )
from PA_PROJ_ELEMENT_VERSIONS a , pa_object_relationships b , PA_PROJ_ELEMENT_VERSIONS c
where a.project_id = p_project_id
and a.object_type = 'PA_TASKS'
and a.parent_structure_version_id = l_struct_ver_id -- Bug 16507592
and a.element_version_id = b.object_id_to1 (+)
and b.object_type_From (+) = 'PA_TASKS'
and b.relationship_type (+) = 'S'
and b.object_id_from1 = c.element_version_id (+) ;
insert into pa_ppr_obj_tmp_view ( object_id , parent_object_id ) values ( l_new_elem_id , -1 );
l_task_arr.delete;
l_task_level_arr.delete;
l_connect_path_arr.delete;
l_leaf_node_arr.delete;
UPDATE pa_ppr_obj_tmp_view
SET connect_path = l_connect_path_arr(k),
leaf_node = l_leaf_node_arr(k),
wbs_rbs_level = l_task_level_arr(k)
WHERE object_id = l_task_arr(k);
--elsIF (l_count=0) OR l_update_flag THEN -- SCOPE_00 comment this IF Statement and add below IF statement
END IF; -- SCOPE_00 Add this end if
IF (l_count=0) OR l_update_flag THEN -- SCOPE_00 Add this IF statement
-- OLAP ENDS
IF (l_update_flag) THEN
DELETE FROM pji_rep_xbs_denorm
WHERE wbs_version_id = p_element_version_id
AND prg_flag = p_prg_flag;
INSERT INTO pji_rep_xbs_denorm
(SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
SELECT p_project_id,'N',p_project_id,-1,struct_ver.proj_element_id,
'N',struct_ver.NAME,'Y','Y' ,
SYSDATE, SYSDATE, 1, 1,
0 ,p_element_version_id, -1, p_element_version_id, 'WF'
FROM pa_proj_elem_ver_structure struct_ver
WHERE 1=1
AND struct_ver.project_id = p_project_id
AND struct_ver.element_version_id = p_element_version_id;
** Insert the self node for this project
*/
INSERT INTO pji_rep_xbs_denorm
(SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
SELECT p_project_id,'N',p_project_id,struct_ver.proj_element_id,
struct_ver.proj_element_id,'N',struct_ver.NAME,'N','N' ,
SYSDATE, SYSDATE, 1, 1,
0 ,p_element_version_id, 0, p_element_version_id, 'WF'
FROM pa_proj_elem_ver_structure struct_ver
WHERE 1=1
AND struct_ver.project_id = p_project_id
AND struct_ver.element_version_id = p_element_version_id;
INSERT INTO pji_rep_xbs_denorm
(SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
SELECT denorm.sup_project_id,'N',denorm.sup_project_id,denorm.sup_emt_id,
denorm.sub_emt_id,'N',emt.name,DECODE(denorm.sub_level-sup_level,0,'N','Y'),DECODE(denorm.sub_leaf_flag,'Y','N','Y'),
SYSDATE, SYSDATE, 1, 1,
0 ,p_element_version_id, ver.display_sequence,p_element_version_id, denorm.relationship_type
FROM pji_xbs_denorm denorm, pa_proj_elements emt,pa_proj_element_versions ver
WHERE 1=1
AND denorm.sup_project_id = p_project_id
AND denorm.struct_version_id = p_element_version_id
AND denorm.sub_level - denorm.sup_level<=1
AND denorm.struct_type = 'WBS'
AND denorm.SUB_EMT_ID = emt.PROJ_ELEMENT_ID
AND ver.project_id = p_project_id
AND ver.parent_structure_version_id = p_element_version_id
AND ver.object_type = 'PA_TASKS'
AND ver.proj_element_id = denorm.sub_emt_id;
INSERT INTO pji_rep_xbs_denorm
(SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
SELECT
denorm.sup_project_id
, 'N'
, denorm.sup_project_id
, denorm.sup_emt_id
, denorm.sub_emt_id
, 'N'
, emt.name
, 'Y'
, DECODE(denorm.sub_leaf_flag,'Y','N','Y')
, SYSDATE
, SYSDATE
, 1
, 1
, 0
, p_element_version_id
, ver.display_sequence
, p_element_version_id
, denorm.relationship_type
FROM pji_xbs_denorm denorm
, pa_proj_elements emt
,pa_proj_element_versions ver
WHERE 1=1
AND denorm.sup_project_id = p_project_id
AND denorm.sub_emt_id = emt.proj_element_id
AND denorm.struct_version_id = p_element_version_id
AND denorm.struct_type = 'XBS'
AND ver.project_id = p_project_id
AND ver.parent_structure_version_id = p_element_version_id
AND ver.object_type = 'PA_TASKS'
AND ver.proj_element_id = denorm.sub_emt_id;
SELECT COUNT(*)
INTO l_count
FROM pji_xbs_denorm
WHERE sup_project_id = p_project_id
AND sup_id = p_element_version_id
AND struct_type = 'PRG'
AND sub_level>sup_level
AND ROWNUM=1;
** Insert the virtual Header
*/
INSERT INTO pji_rep_xbs_denorm
(SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
SELECT p_project_id
, l_prg_flag
,struct_ver.project_id
,-1
,struct_ver.proj_element_id
,'Y'
,struct_ver.NAME
,'Y'
,'Y'
, SYSDATE
, SYSDATE
, 1
, 1
, 0
,p_element_version_id
, -1
,p_element_version_id
, 'WF'
FROM pa_proj_elem_ver_structure struct_ver
WHERE 1=1
AND struct_ver.project_id = p_project_id
AND struct_ver.element_version_id = p_element_version_id;
** Insert the project level self amount
*/
INSERT INTO pji_rep_xbs_denorm
(SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
SELECT p_project_id
, DECODE(struct_ver.project_id, p_project_id, l_prg_flag,DECODE(SUB_LEAF_FLAG,'Y','N','Y'))
,struct_ver.project_id
,denorm.sub_emt_id
,denorm.sub_emt_id
,'Y'
,struct_ver.NAME
,'N'
,'N'
, SYSDATE
, SYSDATE
, 1
, 1
, 0
,p_element_version_id
, 0
, denorm.sup_id
, 'WF'
FROM pji_xbs_denorm denorm, pa_proj_elem_ver_structure struct_ver, pa_proj_elements emt
WHERE 1=1
AND denorm.sup_project_id = p_project_id
AND denorm.sup_id = p_element_version_id
AND denorm.struct_version_id IS NULL
AND denorm.struct_type = 'PRG'
AND denorm.sub_id = struct_ver.element_version_id
AND denorm.sub_emt_id = emt.proj_element_id
AND emt.project_id = struct_ver.project_id
AND NVL(denorm.relationship_type,'WF') IN ('WF',l_relationship_type);
** Insert wbs information inside each structure
*/
INSERT INTO pji_rep_xbs_denorm
(SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
SELECT p_project_id
, DECODE(emt.project_id, p_project_id, l_prg_flag,DECODE(structs.SUB_LEAF_FLAG,'Y','N','Y'))
,emt.project_id
,denorm.sup_emt_id
,denorm.sub_emt_id
,'Y'
,emt.name
,DECODE(denorm.sub_level-denorm.sup_level,0,'N','Y')
,DECODE(denorm.sub_leaf_flag,'Y','N','Y')
, SYSDATE
, SYSDATE
, 1
, 1
, 0
,p_element_version_id
, ver.display_sequence
,denorm.struct_version_id
, denorm.relationship_type
FROM pa_proj_elements emt, pji_xbs_denorm denorm,
(SELECT sub_id wbs_version_id, sub_leaf_flag
FROM pji_xbs_denorm
WHERE 1=1
AND sup_project_id = p_project_id
AND sup_id = p_element_version_id
AND struct_version_id IS NULL
AND struct_type = 'PRG'
AND NVL(relationship_type,'WF') IN ('WF',l_relationship_type)
) structs
,pa_proj_element_versions ver
WHERE 1=1
AND denorm.sub_level-denorm.sup_level<=1
AND denorm.struct_type = 'WBS'
AND denorm.sub_emt_id = emt.proj_element_id
AND denorm.struct_version_id = structs.wbs_version_id
AND ver.project_id = emt.project_id
AND ver.parent_structure_version_id = denorm.struct_version_id
AND ver.object_type = 'PA_TASKS'
AND ver.proj_element_id = denorm.sub_emt_id;
** Insert the link between structure and the top level elements
*/
INSERT INTO pji_rep_xbs_denorm
(SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
SELECT
p_project_id
, DECODE(emt.project_id, p_project_id, l_prg_flag,DECODE(structs.SUB_LEAF_FLAG,'Y','N','Y'))
,emt.project_id
, denorm.sup_emt_id
, denorm.sub_emt_id
, 'Y'
, emt.name
, 'Y'
, DECODE(denorm.sub_leaf_flag,'Y','N','Y')
, SYSDATE
, SYSDATE
, 1
, 1
, 0
, p_element_version_id
, ver.display_sequence
, denorm.struct_version_id
, denorm.relationship_type
FROM pji_xbs_denorm denorm
, pa_proj_elements emt
, (SELECT sub_id wbs_version_id, sub_leaf_flag
FROM pji_xbs_denorm
WHERE 1=1
AND sup_project_id = p_project_id
AND sup_id = p_element_version_id
AND struct_version_id IS NULL
AND struct_type = 'PRG'
AND NVL(relationship_type,'WF') IN ('WF',l_relationship_type)
) structs
,pa_proj_element_versions ver
WHERE 1=1
AND denorm.sub_emt_id = emt.proj_element_id
AND denorm.struct_version_id = structs.wbs_version_id
AND denorm.struct_type = 'XBS'
AND ver.project_id = emt.project_id
AND ver.parent_structure_version_id = denorm.struct_version_id
AND ver.object_type = 'PA_TASKS'
AND ver.proj_element_id = denorm.sub_emt_id;
* Insert link from project to project
*/
INSERT INTO pji_rep_xbs_denorm
(SUP_PROJECT_ID, PRG_ROLLUP_FLAG, PROJECT_ID, PARENT_ELEMENT_ID,
CHILD_ELEMENT_ID, PRG_FLAG, NAME, ROLLUP_FLAG, DISPLAY_CHILD_FLAG,
CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY,
LAST_UPDATE_LOGIN, WBS_VERSION_ID,DISPLAY_SEQUENCE,OWNER_WBS_VERSION_ID,RELATIONSHIP_TYPE)
SELECT p_project_id
,DECODE(struct_ver.project_id, p_project_id, l_prg_flag,DECODE(structs.SUB_LEAF_FLAG,'Y','N','Y'))
,struct_ver.project_id
,denorm.sub_rollup_id
,denorm.sub_emt_id
,'Y'
,struct_ver.NAME
,'Y'
,'Y'
, SYSDATE
, SYSDATE
, 1
, 1
, 0
,p_element_version_id
, -1
, sub_id
, denorm.relationship_type
FROM pji_xbs_denorm denorm, pa_proj_elem_ver_structure struct_ver,pa_proj_elements emt
, (SELECT sub_id wbs_version_id, sub_leaf_flag
FROM pji_xbs_denorm
WHERE 1=1
AND sup_project_id = p_project_id
AND sup_id = p_element_version_id
AND struct_version_id IS NULL
AND struct_type = 'PRG'
AND NVL(relationship_type,'WF') IN ('WF',l_relationship_type)
) structs
WHERE 1=1
AND denorm.sup_id = structs.wbs_version_id
AND denorm.struct_type = 'PRG'
AND denorm.struct_version_id IS NULL
AND denorm.sub_rollup_id <> denorm.sup_emt_id
AND denorm.sub_id = struct_ver.element_version_id
AND denorm.sub_emt_id = emt.proj_element_id
AND emt.project_id = struct_ver.project_id;
UPDATE pji_rep_xbs_denorm
SET display_child_flag = 'Y'
WHERE rollup_flag = 'Y'
AND sup_project_id = p_project_id
AND prg_flag = 'Y'
AND child_element_id IN
(SELECT sub_rollup_id
FROM pji_xbs_denorm,
(SELECT sub_id wbs_version_id, sub_leaf_flag
FROM pji_xbs_denorm
WHERE 1=1
AND sup_project_id = p_project_id
AND sup_id = p_element_version_id
AND struct_version_id IS NULL
AND struct_type = 'PRG'
AND NVL(relationship_type,'WF') IN ('WF',l_relationship_type)
) structs
WHERE 1=1
AND struct_type ='PRG'
AND struct_version_id IS NULL
AND sup_id = structs.wbs_version_id
AND NVL(sub_rollup_id, sup_emt_id) <> sup_emt_id);
DELETE FROM PJI_PLAN_EXTR_TMP;
select p.pjt_rollup_enabled_flag
into l_pjt_rollup_enabled_flag
from pa_projects_all p
where p_project_id = p.project_id ;
INSERT INTO PJI_PLAN_EXTR_TMP
(PROJECT_ID, PLAN_VER_ID, LPB_PLAN_VER_ID, BASE_PLAN_VER_ID, PLAN1_VER_ID, PLAN2_VER_ID, STRUCT_VER_ID)
VALUES
(p_project_id, p_current_version_id, p_latest_version_id, p_baselined_version_id, p_plan1_version_id,
p_plan2_version_id, p_curr_wbs_vers_id);
INSERT INTO PJI_PLAN_EXTR_TMP
(PROJECT_ID, PLAN_VER_ID, LPB_PLAN_VER_ID, BASE_PLAN_VER_ID, PLAN1_VER_ID, PLAN2_VER_ID, STRUCT_VER_ID)
SELECT
header.project_id,
MAX(DECODE(header_p.plan_version_id,p_current_version_id,header.plan_version_id,NULL)),
MAX(DECODE(header_p.plan_version_id,p_latest_version_id,header.plan_version_id,NULL)),
MAX(DECODE(header_p.plan_version_id,p_baselined_version_id,header.plan_version_id,NULL)),
MAX(DECODE(header_p.plan_version_id,p_plan1_version_id,header.plan_version_id,NULL)),
MAX(DECODE(header_p.plan_version_id,p_plan2_version_id,header.plan_version_id,NULL)),
MAX(DECODE(header_p.plan_version_id,p_current_version_id,header.wbs_version_id,NULL))
FROM
pji_xbs_denorm denorm
, pa_proj_elements elem
, pji_pjp_wbs_header header
, pji_pjp_wbs_header header_p
WHERE 1=1
AND header_p.project_id = p_project_id
AND header_p.plan_version_id IN
(
p_current_version_id,
p_latest_version_id,
p_baselined_version_id,
p_plan1_version_id,
p_plan2_version_id
)
AND denorm.sup_project_id = header_p.project_id
AND denorm.sup_id = header_p.wbs_version_id
AND denorm.struct_type = 'PRG'
AND NVL(denorm.relationship_type,'WF') IN ('LW','WF')
AND denorm.struct_version_id IS NULL
AND denorm.sub_emt_id = elem.proj_element_id
AND header.project_id = elem.project_id
AND header.wbs_version_id = denorm.sub_id
AND header.wp_flag = 'Y'
GROUP BY header.project_id;
INSERT INTO PJI_PLAN_EXTR_TMP
(PROJECT_ID, PLAN_VER_ID, LPB_PLAN_VER_ID, BASE_PLAN_VER_ID, PLAN1_VER_ID, PLAN2_VER_ID, STRUCT_VER_ID)
VALUES
(p_project_id, p_current_version_id, p_latest_version_id, p_baselined_version_id, p_plan1_version_id, p_plan2_version_id, p_curr_wbs_vers_id);
SELECT calendar_id
INTO g_global_calendar_id
FROM fii_time_cal_name
WHERE period_set_name = Fnd_Profile.VALUE( 'BIS_ENTERPRISE_CALENDAR' )
AND period_type = Fnd_Profile.VALUE( 'BIS_PERIOD_TYPE' );
SELECT Min(calendar_type) into x_calendar_type
FROM pji_fp_xbs_accum_f WHERE project_id = p_project_id
and plan_version_id <> -1 and calendar_type <> 'A';
/* SELECT fin_plan_type_id
INTO l_fin_plan_type_id
FROM pa_budget_versions
WHERE budget_version_id = p_plan_version_id;
SELECT org.gl_calendar_id,org.pa_calendar_id
INTO l_gl_calendar_id, l_pa_calendar_id
FROM
pa_projects_all projects,
pji_org_extr_info org
/* WHERE NVL(projects.org_id,-99) = NVL(org.org_id,-99) -- Added NVL for bug 3989132 */
WHERE projects.org_id = org.org_id -- Removed NVL for Bug5376591
AND projects.project_id = p_project_id;
SELECT all_time_phased_code, cost_time_phased_code, revenue_time_phased_code
INTO l_all_cal_type, l_cost_cal_type, l_revenue_cal_type
FROM pa_proj_fp_options
WHERE project_id = p_project_id
AND fin_plan_version_id = l_working_version_id
AND fin_plan_option_level_code = Pa_Fp_Constants_Pkg.G_OPTION_LEVEL_PLAN_VERSION;
SELECT revenue_time_phased_code
INTO l_revenue_cal_type
FROM pa_proj_fp_options
WHERE project_id = p_project_id
AND fin_plan_version_id = p_rev_version_id
AND fin_plan_option_level_code = Pa_Fp_Constants_Pkg.G_OPTION_LEVEL_PLAN_VERSION;
-- We have make sure if cost is selected, it is valid, so if the selected one is not valid, that means
-- revenue is not valid, so it will be an invalid case
IF (x_calendar_type IS NULL) OR (x_calendar_type = 'N') THEN
x_time_phase_valid_flag := 'PJI_REP_PLAN_NOT_TF';
SELECT fin_plan_preference_code
INTO l_version_type
FROM pa_proj_fp_options
WHERE project_id = p_project_id
AND fin_plan_type_id = p_fin_plan_type_id
AND fin_plan_option_level_code = Pa_Fp_Constants_Pkg.G_OPTION_LEVEL_PLAN_TYPE;
** Pick the default factor by defined for the selected
** plan_type.
*/
SELECT factor_by_code
INTO l_factor_by_code
FROM pa_proj_fp_options
WHERE 1=1
AND fin_plan_option_level_code = 'PLAN_VERSION'
AND project_id = p_project_id
AND fin_plan_version_id = p_fin_plan_version_id;
SELECT
prj.org_id
, prj.project_currency_code
, prj.projfunc_currency_code
, NVL(prj.sys_program_flag,'N')
, info.gl_calendar_id
, info.pa_calendar_id
INTO
g_project_org_id
, g_proj_currency_code
, g_projfunc_currency_code
, g_prg_flag
, g_gl_calendar_id
, g_pa_calendar_id
FROM pa_projects_all prj
, pji_org_extr_info info
WHERE project_id = p_project_id
/* AND NVL(info.org_id,-99) = NVL(prj.org_id,-99); -- Added NVL for bug 3989132 */
SELECT rbs_version_id
INTO x_rbs_version_id
FROM
pa_proj_fp_options
WHERE fin_plan_version_id = p_plan_version_id;
SELECT rbs_version_id
FROM
pa_rbs_prj_assignments
WHERE
project_id = p_project_id
AND assignment_status ='ACTIVE'
AND prog_rep_usage_flag IN ('Y',p_prg_flag)
ORDER BY primary_reporting_rbs_flag DESC;
SELECT rbs_element_id
FROM
pa_rbs_elements rbs
WHERE
rbs.rbs_version_id = p_rbs_version_id
AND rbs.rbs_level = 1
ORDER BY rbs.user_created_flag;
SELECT hdr.wbs_version_id
INTO x_wbs_version_id
FROM
pji_pjp_wbs_header hdr
WHERE
hdr.project_id = p_project_id
AND hdr.plan_version_id = p_plan_version_id;*/ -- commented this as part of 16507592
SELECT wbs_version_id into x_wbs_version_id
FROM
(SELECT hdr.wbs_version_id
FROM pa_projects_all p ,
pji_pjp_wbs_header hdr
WHERE p.project_id = p_project_id and
hdr.project_id = p.project_id
AND hdr.plan_version_id = p_plan_version_id
AND NVL(p.pjt_rollup_enabled_flag,0) = 'N'
UNION ALL
SELECT pa_project_structure_utils.GET_FIN_STRUC_VER_ID(p_project_id)
FROM pa_projects_all p ,
pa_budget_versions b
WHERE p.project_id = p_project_id
AND b.project_id = p.project_id
AND b.budget_version_id = p_plan_version_id
AND fin_plan_type_id <> 10
AND NVL(p.pjt_rollup_enabled_flag,0) = 'Y'
UNION ALL
SELECT b.project_structure_version_id
FROM pa_projects_all p ,
pa_budget_versions b
WHERE p.project_id = p_project_id
AND b.project_id = p.project_id
AND b.budget_version_id = p_plan_version_id
AND fin_plan_type_id = 10
AND NVL(p.pjt_rollup_enabled_flag,0) = 'Y'
) ;
SELECT elm.proj_element_id
INTO x_wbs_element_id
FROM pa_proj_element_versions elm
WHERE elm.element_version_id = x_wbs_version_id;
/* SELECT hdr.wbs_version_id,elm.proj_element_id
INTO x_wbs_version_id, x_wbs_element_id
FROM
pji_pjp_wbs_header hdr
, pa_proj_element_versions elm
WHERE
hdr.wbs_version_id = elm.element_version_id
AND hdr.project_id = p_project_id
AND hdr.plan_version_id = p_plan_version_id;
SELECT element_version_id, proj_element_id
FROM
pa_proj_elem_ver_structure
WHERE
project_id = p_project_id
ORDER BY NVL(Latest_eff_published_flag,'N') DESC, NVL(current_working_flag,'N') DESC;
SELECT start_date,NVL(completion_date,TRUNC(SYSDATE))
INTO l_start_date, l_end_date
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT COUNT(*)
INTO l_rec_count
FROM pji_time_ent_period_v
WHERE start_date >= l_start_date
AND end_date <= l_end_date;
SELECT COUNT(*)
INTO l_rec_count
FROM pji_time_ent_qtr_v
WHERE start_date >= l_start_date
AND end_date <= l_end_date;
SELECT COUNT(*)
INTO l_rec_count
FROM pji_time_ent_year_v
WHERE start_date >= l_start_date
AND end_date <= l_end_date;
SELECT COUNT(*)
INTO l_rec_count
FROM pji_time_cal_period_v
WHERE start_date >= l_start_date
AND end_date <= l_end_date
AND calendar_id = p_calendar_id;
SELECT COUNT(*)
INTO l_rec_count
FROM pji_time_cal_qtr_v
WHERE start_date >= l_start_date
AND end_date <= l_end_date
AND calendar_id = p_calendar_id;
SELECT COUNT(*)
INTO l_rec_count
FROM pji_time_cal_year_v
WHERE start_date >= l_start_date
AND end_date <= l_end_date
AND calendar_id = p_calendar_id;
SELECT pt.plan_class_code
, pt.NAME
, op.FIN_PLAN_PREFERENCE_CODE
, op.MARGIN_DERIVED_FROM_CODE
, DECODE(op.fin_plan_preference_code,
'COST_AND_REV_SEP',op.report_labor_hrs_from_code,
'REVENUE_ONLY','REVENUE',
'COST')
, NVL(op.approved_cost_plan_type_flag,'N')
, NVL(op.approved_rev_plan_type_flag, 'N')
INTO l_class_code
, x_plan_type_name
, x_plan_pref_code
, x_plan_margin_mask
, x_plan_report_mask
, x_cost_app_flag
, x_rev_app_flag
FROM pa_fin_plan_types_vl pt
, pa_proj_fp_options op
WHERE 1=1
AND pt.fin_plan_type_id = p_fin_plan_type_id
AND op.fin_plan_type_id = pt.fin_plan_type_id
AND op.fin_plan_option_level_code = 'PLAN_TYPE'
AND op.project_id = p_project_id;
SELECT op.MARGIN_DERIVED_FROM_CODE
INTO x_plan_margin_mask
FROM pa_proj_fp_options op
WHERE op.fin_plan_version_id = p_plan_version_id
AND op.fin_plan_option_level_code = 'PLAN_VERSION'
AND op.project_id = p_project_id;
SELECT version_number, version_name, record_version_number,budget_status_code
INTO x_version_no, x_version_name, x_version_record_no , x_budget_status_code
FROM pa_budget_versions
WHERE budget_version_id = p_version_id;
SELECT info.gl_calendar_id, info.pa_calendar_id
INTO l_gl_calendar_id, l_pa_calendar_id
FROM pji_org_extr_info info, pa_projects_all proj
WHERE info.org_id = proj.org_id
AND proj.project_id = p_project_id;
select same_pa_gl_period into l_same_pa_gl_period
from pa_implementations;
select same_pa_gl_period into l_same_pa_gl_period
from pa_implementations_all where org_id =
(select org_id from pa_projects_all where project_id = p_project_id);
SELECT curr_rep_gl_period, curr_rep_pa_period, curr_rep_ent_period
INTO l_curr_rep_gl_period, l_curr_rep_pa_period, l_curr_rep_ent_period
FROM pji_system_settings;
SELECT
info.pa_curr_rep_period,
info.gl_curr_rep_period,
params.value
INTO l_specific_pa_period, l_specific_gl_period, l_specific_ent_period
FROM pji_org_extr_info info,
pji_system_parameters params,
pa_projects_all proj
WHERE proj.project_id = p_project_id
AND info.org_id = proj.org_id
AND params.name = 'PJI_PJP_ENT_CURR_REP_PERIOD';
SELECT start_date
INTO l_report_date
FROM pji_time_ent_period_v
WHERE TRUNC(SYSDATE) BETWEEN start_date AND end_date;
SELECT MAX(start_date)
INTO l_report_date
FROM pji_time_ent_period_v
WHERE end_date
SELECT start_date
INTO l_report_date
FROM pji_time_ent_period_v
WHERE name = l_period_name;
SELECT name
INTO l_period_name
FROM pji_time_ent_period_v
WHERE l_report_date BETWEEN start_date AND end_date;
SELECT MIN(TIM.start_date) first_open
INTO l_report_date
FROM
pji_time_cal_period_v TIM
, gl_period_statuses glps
, pa_implementations paimp
WHERE 1=1
AND TIM.calendar_id = x_calendar_id
AND paimp.set_of_books_id = glps.set_of_books_id
AND glps.application_id = l_application_id
AND glps.period_name = TIM.NAME
AND closing_status = 'O';
SELECT MIN(TIM.start_date) first_open
INTO l_report_date
FROM
pji_time_cal_period_v TIM
, pa_periods_all pa
, pa_implementations paimp
WHERE 1=1
AND TIM.calendar_id = x_calendar_id
AND paimp.org_id = pa.org_id
AND pa.period_name = TIM.NAME
AND status = 'O';
SELECT MAX(TIM.start_date) last_open
INTO l_report_date
FROM
pji_time_cal_period_v TIM
, gl_period_statuses glps
, pa_implementations paimp
WHERE 1=1
AND TIM.calendar_id = x_calendar_id
AND paimp.set_of_books_id = glps.set_of_books_id
AND glps.application_id = 275
AND glps.period_name = TIM.NAME
AND closing_status = 'O';
SELECT MAX(TIM.start_date) last_open
INTO l_report_date
FROM
pji_time_cal_period_v TIM
, pa_periods_all pa
, pa_implementations paimp
WHERE 1=1
AND TIM.calendar_id = x_calendar_id
AND paimp.org_id = pa.org_id
AND pa.period_name = TIM.NAME
AND status = 'O';
SELECT MAX(TIM.start_date) last_closed
INTO l_report_date
FROM
pji_time_cal_period_v TIM
, gl_period_statuses glps
, pa_implementations paimp
WHERE 1=1
AND TIM.calendar_id = x_calendar_id
AND paimp.set_of_books_id = glps.set_of_books_id
AND glps.application_id = l_application_id
AND glps.period_name = TIM.NAME
AND closing_status = 'C';
SELECT MAX(TIM.start_date) last_open
INTO l_report_date
FROM
pji_time_cal_period_v TIM
, pa_periods_all pa
, pa_implementations paimp
WHERE 1=1
AND TIM.calendar_id = x_calendar_id
AND paimp.org_id = pa.org_id
AND pa.period_name = TIM.NAME
AND status = 'C';
SELECT start_date
INTO l_report_date
FROM pji_time_cal_period_v
WHERE TRUNC(SYSDATE) BETWEEN start_date
AND end_date
AND calendar_id = x_calendar_id;
SELECT MAX(start_date)
INTO l_report_date
FROM pji_time_cal_period_v
WHERE end_date < l_report_date
AND calendar_id = x_calendar_id;
SELECT start_date
INTO l_report_date
FROM pji_time_cal_period_v
WHERE name = l_period_name
AND calendar_id = x_calendar_id;
SELECT name
INTO l_period_name
FROM pji_time_cal_period_v
WHERE l_report_date BETWEEN start_date AND end_date
AND calendar_id = x_calendar_id;
PROCEDURE update_curr_rep_periods(
p_pa_curr_rep_period VARCHAR2,
p_gl_curr_rep_period VARCHAR2,
p_ent_curr_rep_period VARCHAR2
) AS
-- ----------------------------------------------
-- declare statements --
l_org_id_count NUMBER := 0;
Pji_Utils.WRITE2LOG( 'update_curr_rep_periods: begining', TRUE , g_proc);
SELECT COUNT(info.org_id)
INTO l_org_id_count
FROM pji_org_extr_info info
WHERE 1=1
AND info.org_id = NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10))),-99);
-- insert p_org_id, pa_curr_rep_period and gl_curr_rep_period (everything else is null)
INSERT
INTO pji_org_extr_info
(
org_id,
pa_curr_rep_period,
gl_curr_rep_period
)
VALUES
(
NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10))),-99),
p_pa_curr_rep_period,
p_gl_curr_rep_period
);
-- update pa_curr_rep_period and gl_curr_rep_period
UPDATE pji_org_extr_info
SET pa_curr_rep_period = p_pa_curr_rep_period,
gl_curr_rep_period = p_gl_curr_rep_period
WHERE org_id = NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10))),-99);
SELECT COUNT(params.name)
INTO l_ent_period_count
FROM pji_system_parameters params
WHERE 1=1
AND params.name = 'PJI_PJP_ENT_CURR_REP_PERIOD';
INSERT
INTO pji_system_parameters
(
name,
value
)
VALUES
(
'PJI_PJP_ENT_CURR_REP_PERIOD',
p_ent_curr_rep_period
);
UPDATE pji_system_parameters
SET value = p_ent_curr_rep_period
WHERE name = 'PJI_PJP_ENT_CURR_REP_PERIOD';
Pji_Utils.WRITE2LOG( 'update_curr_rep_periods: finishing', TRUE , g_proc);
END update_curr_rep_periods;
SELECT TO_CHAR(sch.scheduled_start_date,'j'),TO_CHAR(sch.scheduled_finish_date,'j')
FROM
pji_pjp_wbs_header hdr
, pa_proj_elem_ver_schedule sch
WHERE
hdr.project_id = p_project_id
AND hdr.plan_version_id IN ( p_published_version_id,p_working_version_id)
AND sch.element_version_id = hdr.wbs_version_id
ORDER BY DECODE(hdr.plan_version_id, p_published_version_id, 0,1);
SELECT MAX(max_txn_date), MIN(min_txn_date)
INTO l_max_date, l_min_date
FROM pji_pjp_wbs_header
WHERE project_id = p_project_id
AND plan_version_id IN (l_plan_version_ids(1),l_plan_version_ids(2),l_plan_version_ids(3),l_plan_version_ids(4),
l_plan_version_ids(5),l_plan_version_ids(6),l_plan_version_ids(7),l_plan_version_ids(8),
l_plan_version_ids(9),l_plan_version_ids(10),l_plan_version_ids(11),l_plan_version_ids(12),
l_plan_version_ids(13),l_plan_version_ids(14),l_plan_version_ids(15),l_plan_version_ids(16),l_plan_version_ids(17) );
Pji_Utils.WRITE2LOG( 'Get_Default_Period_Dates: Done selecting pa_resource_assignments table', TRUE , g_proc);
SELECT DISTINCT UPPER(t.project_type_class_code)
INTO x_project_type
FROM pa_projects_all p
, pa_project_types_all t
WHERE 1=1
AND p.project_id = p_project_Id
AND p.project_type = t.project_type
/*AND NVL(p.org_id,-99) = NVL(t.org_id,-99); -- Added NVL for bug 3989132*/
SELECT object_type
INTO l_object_type
FROM pa_proj_elements
WHERE proj_element_id = p_wbs_element_id;
SELECT end_date
INTO l_report_date
FROM pji_time_ent_period
WHERE TO_DATE(p_report_date_julian,'j') BETWEEN start_date AND end_date;
SELECT end_date
INTO l_report_date
FROM pji_time_cal_period
WHERE TO_DATE(p_report_date_julian,'j') BETWEEN start_date AND end_date
AND calendar_id=p_calendar_id;
SELECT
NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10))),-99)
INTO l_env_org
FROM dual;
SELECT org_id
INTO l_project_org
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT project_currency_code,projfunc_currency_code
FROM pa_projects_all
WHERE project_id = c_project_id;
SELECT 1
FROM pji_xbs_denorm denorm,
pa_proj_elements ele,
pa_projects_all proj
WHERE denorm.sup_project_id = c_project_id
AND denorm.sup_id = c_wbs_version_id
AND denorm.struct_type = 'PRG'
AND denorm.struct_version_id IS NULL
AND denorm.sub_emt_id = ele.proj_element_id
AND ele.project_id = proj.project_id
AND proj.project_currency_code <> c_currency_code;
SELECT 1
FROM pji_xbs_denorm denorm,
pa_proj_elements ele,
pa_projects_all proj
WHERE denorm.sup_project_id = c_project_id
AND denorm.sup_id = c_wbs_version_id
AND denorm.struct_type = 'PRG'
AND denorm.struct_version_id IS NULL
AND denorm.sub_emt_id = ele.proj_element_id
AND ele.project_id = proj.project_id
AND proj.projfunc_currency_code <> c_currency_code;
SELECT 1
FROM pji_xbs_denorm denorm,
pji_pjp_wbs_header header,
pa_proj_fp_options opt
WHERE denorm.sup_project_id = c_project_id
AND denorm.sup_id = c_wbs_version_id
AND denorm.struct_type = 'PRG'
AND denorm.struct_version_id IS NULL
AND denorm.sub_id = header.wbs_version_id
AND header.wp_flag = 'Y'
AND header.plan_version_id = opt.fin_plan_version_id
AND opt.fin_plan_option_level_code = 'PLAN_VERSION'
AND opt.margin_derived_from_code <> c_margin_code;
SELECT 1
FROM pji_xbs_denorm denorm,
pa_proj_elements ele
WHERE denorm.sup_project_id = c_project_id
AND denorm.sup_id = c_wbs_version_id
AND denorm.struct_type = 'PRG'
AND denorm.struct_version_id IS NULL
AND denorm.sub_emt_id = ele.proj_element_id
AND Pa_Project_Structure_Utils.Check_Struc_Ver_Published(ele.project_id,denorm.sub_id) <> c_published_flag;
SELECT header.project_id, header.plan_version_id
FROM pji_xbs_denorm denorm,
pji_pjp_wbs_header header
WHERE denorm.sup_project_id = c_project_id
AND denorm.sup_id = c_wbs_version_id
AND denorm.struct_type = 'PRG'
AND denorm.struct_version_id IS NULL
AND header.wbs_version_id = denorm.sub_id
AND header.wp_flag = 'Y';
SELECT 1
FROM pji_xbs_denorm denorm,
pji_pjp_wbs_header header,
pa_proj_fp_options opt
WHERE denorm.sup_project_id = c_project_id
AND denorm.sup_id = c_wbs_version_id
AND denorm.struct_type = 'PRG'
AND denorm.struct_version_id IS NULL
AND denorm.sub_id = header.wbs_version_id
AND header.wp_flag = 'Y'
AND header.plan_version_id = opt.fin_plan_version_id
AND opt.fin_plan_option_level_code = 'PLAN_VERSION'
AND NVL(opt.rbs_version_id, -50) <> c_rbs_version_id; --Bug 4506849
SELECT header.plan_version_id
FROM pa_proj_elem_ver_structure str,
pji_pjp_wbs_header header
WHERE str.project_id = c_project_id
AND str.current_flag = 'Y'
AND header.project_id = str.project_id
AND header.wbs_version_id = str.element_version_id
AND header.wp_flag = 'Y';
SELECT header.plan_version_id
FROM pa_proj_elem_ver_structure str,
pji_pjp_wbs_header header
WHERE str.project_id = c_project_id
AND str.latest_eff_published_flag = 'Y'
AND header.project_id = str.project_id
AND header.wbs_version_id = str.element_version_id
AND header.wp_flag = 'Y';
SELECT wp_default_display_lvl
FROM pa_workplan_options_v
WHERE PROJECT_ID = c_project_id;
SELECT default_display_lvl
FROM pa_financial_options_v
WHERE PROJECT_ID = c_project_id;
SELECT COUNT(DISTINCT info.gl_calendar_id),
COUNT(DISTINCT info.pa_calendar_id)
INTO l_num_of_gl_cal,
l_num_of_pa_cal
FROM
pji_xbs_denorm denorm
, pa_proj_elements elem
, pa_projects_all proj
, pji_org_extr_info info
WHERE 1=1
AND denorm.sup_project_id = p_project_id -- project_id
AND denorm.sup_id = p_wbs_version_id -- wbs_version_id
AND denorm.struct_type = 'PRG'
AND denorm.struct_version_id IS NULL
AND NVL(denorm.relationship_type,'WF') IN ('LF','WF')
AND denorm.sub_emt_id = elem.proj_element_id
AND proj.project_id = elem.project_id
AND NVL(info.org_id,-99) = NVL(proj.org_id,-99);
SELECT COUNT(DISTINCT proj.projfunc_currency_code)
INTO l_num_of_projfunc_curr
FROM
pji_xbs_denorm denorm
, pa_proj_elements elem
, pa_projects_all proj
WHERE 1=1
AND denorm.sup_project_id = p_project_id -- project_id
AND denorm.sup_id = p_wbs_version_id -- wbs_version_id
AND denorm.struct_type = 'PRG'
AND denorm.struct_version_id IS NULL
AND NVL(denorm.relationship_type,'WF') IN ('LF','WF')
AND denorm.sub_emt_id = elem.proj_element_id
AND proj.project_id = elem.project_id;
* function is also used as select function in VO.xml also
*/
FUNCTION Measures_Total(
p_measure1 IN NUMBER
, p_measure2 IN NUMBER DEFAULT NULL
, p_measure3 IN NUMBER DEFAULT NULL
, p_measure4 IN NUMBER DEFAULT NULL
, p_measure5 IN NUMBER DEFAULT NULL
, p_measure6 IN NUMBER DEFAULT NULL
, p_measure7 IN NUMBER DEFAULT NULL
) RETURN NUMBER
IS
l_measures_total NUMBER;
select p.pjt_rollup_enabled_flag
into l_pjt_rollup_enabled_flag
from pa_projects_all p
where p.project_id = p_project_id ;
SELECT project_id
INTO l_project_id
FROM
pa_proj_element_versions
WHERE
ELEMENT_VERSION_ID = p_wbs_version_id;
Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: Done with Project Select', TRUE , g_proc);
SELECT DISTINCT head.wbs_version_id BULK COLLECT
INTO l_get_wbs_version_id_tbl
FROM pji_pjp_wbs_header head
WHERE head.plan_version_id IN ( l_plan_version_id_tbl(1),l_plan_version_id_tbl(2),l_plan_version_id_tbl(3),
l_plan_version_id_tbl(4), l_plan_version_id_tbl(5),l_plan_version_id_tbl(6),
l_plan_version_id_tbl(7),l_plan_version_id_tbl(8), l_plan_version_id_tbl(9),
l_plan_version_id_tbl(10),l_plan_version_id_tbl(11),l_plan_version_id_tbl(12),
l_plan_version_id_tbl(13),l_plan_version_id_tbl(14),l_plan_version_id_tbl(15),
l_plan_version_id_tbl(16),l_plan_version_id_tbl(17) )
AND head.project_id = l_project_id;
Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: No data found in wbs_version_id Select', TRUE , g_proc);
Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: Done with wbs_version_id Select', TRUE , g_proc);
SELECT DISTINCT NVL(head.plan_type_id,-1) BULK COLLECT
INTO l_plan_type_id_tbl
FROM pji_pjp_wbs_header head
WHERE head.plan_version_id IN ( l_plan_version_id_tbl(1),l_plan_version_id_tbl(2),l_plan_version_id_tbl(3),
l_plan_version_id_tbl(4), l_plan_version_id_tbl(5),l_plan_version_id_tbl(6),
l_plan_version_id_tbl(7),l_plan_version_id_tbl(8), l_plan_version_id_tbl(9),
l_plan_version_id_tbl(10),l_plan_version_id_tbl(11),l_plan_version_id_tbl(12),
l_plan_version_id_tbl(13),l_plan_version_id_tbl(14),l_plan_version_id_tbl(15),
l_plan_version_id_tbl(16),l_plan_version_id_tbl(17) )
AND head.project_id = l_project_id;
Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: No data found in plan_type_id Select', TRUE , g_proc);
Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: Done with plan_type_id Select', TRUE , g_proc);
l_plan_version_id_tbl.DELETE;
l_project_id_tbl.DELETE;
Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: Before selecting plan version,project,wbs', TRUE , g_proc);
SELECT DISTINCT head.plan_version_id,head.project_id ,pji.sub_id wbs_version_id BULK COLLECT
INTO l_plan_version_id_tbl, l_project_id_tbl,l_wbs_version_id_tbl
FROM pji_xbs_Denorm pji
,pji_pjp_wbs_header head
WHERE pji.struct_type='PRG'
--AND pji.sup_level<>pji.sub_level --Bug 4624479
AND pji.sup_id IN(l_get_wbs_version_id_tbl(1),l_get_wbs_version_id_tbl(2),l_get_wbs_version_id_tbl(3)
,l_get_wbs_version_id_tbl(4),l_get_wbs_version_id_tbl(5),l_get_wbs_version_id_tbl(6)
,l_get_wbs_version_id_tbl(7),l_get_wbs_version_id_tbl(8),l_get_wbs_version_id_tbl(9)
,l_get_wbs_version_id_tbl(10),l_get_wbs_version_id_tbl(11),l_get_wbs_version_id_tbl(12)
,l_get_wbs_version_id_tbl(13),l_get_wbs_version_id_tbl(14),l_get_wbs_version_id_tbl(15)
,l_get_wbs_version_id_tbl(16),l_get_wbs_version_id_tbl(17))
AND pji.sub_id=head.wbs_version_id
AND p_prg_rollup_flag='Y'
AND NVL(head.plan_type_id,-1) IN (l_plan_type_id_tbl(1),l_plan_type_id_tbl(2),l_plan_type_id_tbl(3)
,l_plan_type_id_tbl(4),l_plan_type_id_tbl(5),l_plan_type_id_tbl(6)
,l_plan_type_id_tbl(7),l_plan_type_id_tbl(8),l_plan_type_id_tbl(9)
,l_plan_type_id_tbl(10),l_plan_type_id_tbl(11),l_plan_type_id_tbl(12)
,l_plan_type_id_tbl(13),l_plan_type_id_tbl(14),l_plan_type_id_tbl(15)
,l_plan_type_id_tbl(16),l_plan_type_id_tbl(17))
AND ((head.cb_flag='Y' ) OR (head.co_flag='Y' ) OR (head.wp_flag='Y') OR (head.wp_flag='N'
AND head.plan_version_id = -1) );
Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: No data found in selecting plan version,project,wbs', TRUE , g_proc);
Pji_Utils.WRITE2LOG( 'Is_Smart_Slice_Created: Done with selecting plan version,project,wbs', TRUE , g_proc);
SELECT 'Y'
INTO l_exists FROM dual
WHERE EXISTS ( SELECT 1
FROM pji_rollup_level_status rst
WHERE rst.rbs_version_id = p_rbs_version_id
AND rst.plan_version_id = l_plan_version_id_tbl(i)
AND rst.project_id = l_project_id_tbl(i));
+ Additional Plan versions are selected
*/
PROCEDURE Chk_plan_vers_have_same_RBS(
p_fin_plan_version_id_tbl IN SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type(),
x_R_PlanVers_HavSame_RBS_Flag OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_fin_plan_version_id NUMBER := NULL;
SELECT DECODE(COUNT(DISTINCT rbs_version_id),l_count,'Y','N')
INTO l_R_PlanVers_HavSame_RBS_Flag
FROM pa_proj_fp_options
WHERE 1=1
AND fin_plan_option_level_code='PLAN_VERSION'
AND fin_plan_version_id IN (l_fin_plan_version_ids(1),l_fin_plan_version_ids(2),l_fin_plan_version_ids(3),l_fin_plan_version_ids(4),
l_fin_plan_version_ids(5),l_fin_plan_version_ids(6),l_fin_plan_version_ids(7),l_fin_plan_version_ids(8));
Pji_Utils.WRITE2LOG( 'Chk_plan_vers_have_same_RBS: Done selecting from pa_proj_fp_options table ', TRUE , g_proc);
SELECT vs.element_version_id, vs.project_id
FROM pa_proj_elem_ver_structure vs
, pji_xbs_denorm denorm
WHERE denorm.struct_version_id IS NULL
AND denorm.struct_type = 'PRG'
AND NVL(denorm.relationship_type,'WF') IN ('WF',c_structure_type)
AND denorm.sup_id = c_structure_version_id
AND denorm.sub_id = vs.element_version_id;
SELECT COUNT(DISTINCT UPPER(pt.project_type_class_code))
INTO l_proj_type_count
FROM pji_xbs_denorm denorm
,pa_proj_elem_ver_structure vs
,pa_projects_all proj
,pa_project_types_all pt
WHERE struct_type = 'PRG'
AND sup_project_id = p_project_id
AND struct_version_id IS NULL
AND NVL(denorm.relationship_type,'WF') IN ('WF',l_relationship_type)
AND denorm.sup_id = p_wbs_version_id
AND denorm.sub_id = vs.element_version_id
AND vs.project_id = proj.project_id
AND proj.project_type = pt.project_type
AND proj.org_id = pt.org_id ; --Added clause for performnace imp. for bug 5376591
SELECT info.gl_calendar_id, info.pa_calendar_id
INTO l_gl_calendar_id, l_pa_calendar_id
FROM pji_org_extr_info info, pa_projects_all proj
WHERE info.org_id = proj.org_id
AND proj.project_id = p_project_id;
SELECT name,TO_CHAR(start_date,'j')
INTO x_period_name, x_report_date_julian
FROM pji_time_ent_period_v
WHERE TO_DATE(p_as_of_date, 'j') BETWEEN start_date AND end_date;
SELECT name,TO_CHAR(start_date,'j')
INTO x_period_name, x_report_date_julian
FROM pji_time_cal_period_v
WHERE calendar_id = x_calendar_id
AND TO_DATE(p_as_of_date,'j') BETWEEN start_date AND end_date;
SELECT projfunc_currency_code, project_currency_code
INTO l_projfunc_currency_code,l_project_currency_code
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT COUNT(*)
INTO l_plan_type_count
FROM pa_proj_fp_options
WHERE project_id = p_project_id
AND fin_plan_type_id = p_plan_type_id
AND fin_plan_option_level_code = 'PLAN_TYPE'
AND ROWNUM =1;
Select count(*)
INTO l_count
FROM
pji_xbs_denorm denorm
, pa_proj_elements elem
, pa_proj_elem_ver_structure ppevs
WHERE 1=1
AND denorm.sup_project_id = p_project_id
AND denorm.sup_id = p_structure_version_id
AND denorm.struct_type = 'PRG'
AND NVL(denorm.relationship_type,'WF') IN (p_relationship_type,'WF')
AND denorm.struct_version_id IS NULL
AND denorm.sub_emt_id = elem.proj_element_id
AND ppevs.project_id = elem.project_id
AND ppevs.element_version_id = denorm.sub_id
AND ppevs.status_code = 'STRUCTURE_WORKING';
SELECT ATTRIBUTE12 into l_return_value
FROM pa_lookups
WHERE lookup_code = p_page_type and lookup_type='PA_PAGE_TYPES';
SELECT ATTRIBUTE13 into l_return_value
FROM pa_lookups
WHERE lookup_code = p_page_type and lookup_type='PA_PAGE_TYPES';
SELECT ATTRIBUTE14 into l_return_value
FROM pa_lookups
WHERE lookup_code = p_page_type and lookup_type='PA_PAGE_TYPES';
SELECT NVL(Fnd_Profile.value('PJI_DEF_RPT_CAL_TYPE'), 'E') FROM dual; -- Based on profile "PJI: Default Reporting Calendar Type"
SELECT NVL(Fnd_Profile.value('PJI_DEF_RPT_CAL_TYPE'), 'E') FROM dual; -- Based on Profile : PJI: Default Reporting Calendar Type
SELECT Sum(brdn_cost) LATEST_PUBLISHED_COST
FROM pji_fp_xbs_accum_f f,
pa_projects_all p,
pa_rbs_prj_assignments r,
pa_proj_elem_ver_structure s,
pa_proj_element_versions ppev,
pji_pjp_wbs_header h
WHERE p.project_id=f.project_id
AND p.project_id=r.project_id
AND p.project_id=s.project_id
AND p.project_id=ppev.project_id
AND p.project_id=h.project_id
AND f.rbs_version_id=-1
AND f.project_id= p_project_id
AND f.project_element_id = p_task_id
AND f.currency_code=p.projfunc_currency_code
AND f.rbs_aggr_level='T'
AND f.prg_rollup_flag='N'
AND f.period_type_id=32
AND f.plan_type_id=10
AND h.wp_flag='Y'
AND s.latest_eff_published_flag='Y'
AND s.element_version_id=h.wbs_version_id
AND s.element_version_id=ppev.element_version_id
AND ppev.object_type='PA_STRUCTURES'
AND f.plan_version_id=h.plan_version_id
AND r.WP_USAGE_FLAG = 'Y'
AND f.rbs_version_id = -1
AND f.rbs_element_id = -1
GROUP BY
p.project_id,
f.project_element_id,
f.calendar_type,
f.period_type_id,
f.curr_record_type_id,
f.plan_type_id,
f.plan_version_id,
f.rbs_version_id;
SELECT Sum(f.brdn_cost) BASELINE_COST
FROM
pji_fp_xbs_accum_f f,
pa_projects_all p,
PA_PROJ_ELEM_VER_STRUCTURE ppev,
pji_pjp_wbs_header h
WHERE
p.project_id = p_project_id AND
p.project_id=f.project_id AND
f.project_element_id = p_task_id AND
p.project_id = ppev.project_id AND
p.project_id = h.project_id AND
f.currency_code=p.projfunc_currency_code AND
f.rbs_aggr_level='T' AND
f.period_type_id=32 AND
f.plan_type_id=10 AND
f.prg_rollup_flag='N' AND
ppev.ELEMENT_VERSION_ID = h.wbs_version_id AND
ppev.CURRENT_flag = 'Y' AND --current baseline
h.wp_flag='Y' AND
f.plan_version_id=h.plan_version_id AND
f.rbs_version_id = -1 AND
f.rbs_element_id = -1
GROUP BY f.project_id,f.project_element_id;
SELECT CBS_VERSION_ID
INTO x_cbs_version_id
FROM
PA_PROJECTS_ALL
WHERE project_id=p_project_id;
SELECT rbs_element_id into x_cbs_element_id
FROM
pa_rbs_elements rbs
WHERE
RBS.RBS_VERSION_ID = X_CBS_VERSION_ID
AND rbs.rbs_level = 1;