[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- 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'
);
-- 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
index(prg PJI_XBS_DENORM_N1) */
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'
);
l_update_flag BOOLEAN;
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;
IF (l_count=0) OR l_update_flag THEN
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;
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 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;
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 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 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_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 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 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';