[Home] [Help]
111: Fnd_Global.USER_ID,SYSDATE,Fnd_Global.USER_ID,Fnd_Global.LOGIN_ID,sup_id,'Y'
112: FROM
113: (
114: select /*+ ordered
115: index(prg PJI_XBS_DENORM_N1) */
116: distinct(prg.SUP_ID) SUP_ID
117: from
118: PJI_XBS_DENORM prg,
119: PA_PROJECTS_ALL prj
114: select /*+ ordered
115: index(prg PJI_XBS_DENORM_N1) */
116: distinct(prg.SUP_ID) SUP_ID
117: from
118: PJI_XBS_DENORM prg,
119: PA_PROJECTS_ALL prj
120: where
121: prg.STRUCT_VERSION_ID is null and
122: prg.SUB_ID = p_wbs_version_id_tbl(l_i) and
258: SELECT denorm.sup_project_id,'N',denorm.sup_project_id,denorm.sup_emt_id,
259: denorm.sub_emt_id,'N',emt.name,DECODE(denorm.sub_level-sup_level,0,'N','Y'),DECODE(denorm.sub_leaf_flag,'Y','N','Y'),
260: SYSDATE, SYSDATE, 1, 1,
261: 0 ,p_element_version_id, ver.display_sequence,p_element_version_id, denorm.relationship_type
262: FROM pji_xbs_denorm denorm, pa_proj_elements emt,pa_proj_element_versions ver
263: WHERE 1=1
264: AND denorm.sup_project_id = p_project_id
265: AND denorm.struct_version_id = p_element_version_id
266: AND denorm.sub_level - denorm.sup_level<=1
294: , p_element_version_id
295: , ver.display_sequence
296: , p_element_version_id
297: , denorm.relationship_type
298: FROM pji_xbs_denorm denorm
299: , pa_proj_elements emt
300: ,pa_proj_element_versions ver
301: WHERE 1=1
302: AND denorm.sup_project_id = p_project_id
312:
313:
314: SELECT COUNT(*)
315: INTO l_count
316: FROM pji_xbs_denorm
317: WHERE sup_project_id = p_project_id
318: AND sup_id = p_element_version_id
319: AND struct_type = 'PRG'
320: AND sub_level>sup_level
384: ,p_element_version_id
385: , 0
386: , denorm.sup_id
387: , 'WF'
388: FROM pji_xbs_denorm denorm, pa_proj_elem_ver_structure struct_ver, pa_proj_elements emt
389: WHERE 1=1
390: AND denorm.sup_project_id = p_project_id
391: AND denorm.sup_id = p_element_version_id
392: AND denorm.struct_version_id IS NULL
421: ,p_element_version_id
422: , ver.display_sequence
423: ,denorm.struct_version_id
424: , denorm.relationship_type
425: FROM pa_proj_elements emt, pji_xbs_denorm denorm,
426: (SELECT sub_id wbs_version_id, sub_leaf_flag
427: FROM pji_xbs_denorm
428: WHERE 1=1
429: AND sup_project_id = p_project_id
423: ,denorm.struct_version_id
424: , denorm.relationship_type
425: FROM pa_proj_elements emt, pji_xbs_denorm denorm,
426: (SELECT sub_id wbs_version_id, sub_leaf_flag
427: FROM pji_xbs_denorm
428: WHERE 1=1
429: AND sup_project_id = p_project_id
430: AND sup_id = p_element_version_id
431: AND struct_version_id IS NULL
470: , p_element_version_id
471: , ver.display_sequence
472: , denorm.struct_version_id
473: , denorm.relationship_type
474: FROM pji_xbs_denorm denorm
475: , pa_proj_elements emt
476: , (SELECT sub_id wbs_version_id, sub_leaf_flag
477: FROM pji_xbs_denorm
478: WHERE 1=1
473: , denorm.relationship_type
474: FROM pji_xbs_denorm denorm
475: , pa_proj_elements emt
476: , (SELECT sub_id wbs_version_id, sub_leaf_flag
477: FROM pji_xbs_denorm
478: WHERE 1=1
479: AND sup_project_id = p_project_id
480: AND sup_id = p_element_version_id
481: AND struct_version_id IS NULL
517: ,p_element_version_id
518: , -1
519: , sub_id
520: , denorm.relationship_type
521: FROM pji_xbs_denorm denorm, pa_proj_elem_ver_structure struct_ver,pa_proj_elements emt
522: , (SELECT sub_id wbs_version_id, sub_leaf_flag
523: FROM pji_xbs_denorm
524: WHERE 1=1
525: AND sup_project_id = p_project_id
519: , sub_id
520: , denorm.relationship_type
521: FROM pji_xbs_denorm denorm, pa_proj_elem_ver_structure struct_ver,pa_proj_elements emt
522: , (SELECT sub_id wbs_version_id, sub_leaf_flag
523: FROM pji_xbs_denorm
524: WHERE 1=1
525: AND sup_project_id = p_project_id
526: AND sup_id = p_element_version_id
527: AND struct_version_id IS NULL
547: AND sup_project_id = p_project_id
548: AND prg_flag = 'Y'
549: AND child_element_id IN
550: (SELECT sub_rollup_id
551: FROM pji_xbs_denorm,
552: (SELECT sub_id wbs_version_id, sub_leaf_flag
553: FROM pji_xbs_denorm
554: WHERE 1=1
555: AND sup_project_id = p_project_id
549: AND child_element_id IN
550: (SELECT sub_rollup_id
551: FROM pji_xbs_denorm,
552: (SELECT sub_id wbs_version_id, sub_leaf_flag
553: FROM pji_xbs_denorm
554: WHERE 1=1
555: AND sup_project_id = p_project_id
556: AND sup_id = p_element_version_id
557: AND struct_version_id IS NULL
625: MAX(DECODE(header_p.plan_version_id,p_plan1_version_id,header.plan_version_id,NULL)),
626: MAX(DECODE(header_p.plan_version_id,p_plan2_version_id,header.plan_version_id,NULL)),
627: MAX(DECODE(header_p.plan_version_id,p_current_version_id,header.wbs_version_id,NULL))
628: FROM
629: pji_xbs_denorm denorm
630: , pa_proj_elements elem
631: , pji_pjp_wbs_header header
632: , pji_pjp_wbs_header header_p
633: WHERE 1=1
3459: for program in workplan context.
3460: */
3461: PROCEDURE CHECK_WP_PARAM_CONSISTENCY
3462: ( p_project_id IN pa_projects_all.project_id%TYPE
3463: ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
3464: ,p_margin_code IN pa_proj_fp_options.margin_derived_from_code%TYPE
3465: ,p_published_flag IN VARCHAR2
3466: ,p_calendar_type IN pji_fp_xbs_accum_f.calendar_type%TYPE
3467: ,p_calendar_id IN pa_projects_all.calendar_id%TYPE
3602: the same project and project functional currency.
3603: */
3604: PROCEDURE CHECK_WP_CURRENCY_CONSISTENCY
3605: ( p_project_id IN pa_projects_all.project_id%TYPE
3606: ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
3607: ,x_pc_flag OUT NOCOPY VARCHAR2
3608: ,x_pfc_flag OUT NOCOPY VARCHAR2
3609: ,x_return_status OUT NOCOPY VARCHAR2
3610: ,x_msg_count OUT NOCOPY NUMBER
3617: FROM pa_projects_all
3618: WHERE project_id = c_project_id;
3619:
3620: CURSOR check_project_currency(c_project_id pa_projects_all.project_id%TYPE,
3621: c_wbs_version_id pji_xbs_denorm.sup_project_id%TYPE,
3622: c_currency_code pa_projects_all.project_currency_code%TYPE)
3623: IS
3624: SELECT 1
3625: FROM pji_xbs_denorm denorm,
3621: c_wbs_version_id pji_xbs_denorm.sup_project_id%TYPE,
3622: c_currency_code pa_projects_all.project_currency_code%TYPE)
3623: IS
3624: SELECT 1
3625: FROM pji_xbs_denorm denorm,
3626: pa_proj_elements ele,
3627: pa_projects_all proj
3628: WHERE denorm.sup_project_id = c_project_id
3629: AND denorm.sup_id = c_wbs_version_id
3633: AND ele.project_id = proj.project_id
3634: AND proj.project_currency_code <> c_currency_code;
3635:
3636: CURSOR check_projfunc_currency(c_project_id pa_projects_all.project_id%TYPE,
3637: c_wbs_version_id pji_xbs_denorm.sup_project_id%TYPE,
3638: c_currency_code pa_projects_all.project_currency_code%TYPE)
3639: IS
3640: SELECT 1
3641: FROM pji_xbs_denorm denorm,
3637: c_wbs_version_id pji_xbs_denorm.sup_project_id%TYPE,
3638: c_currency_code pa_projects_all.project_currency_code%TYPE)
3639: IS
3640: SELECT 1
3641: FROM pji_xbs_denorm denorm,
3642: pa_proj_elements ele,
3643: pa_projects_all proj
3644: WHERE denorm.sup_project_id = c_project_id
3645: AND denorm.sup_id = c_wbs_version_id
3732: program hierarchy have the same margin mask.
3733: */
3734: PROCEDURE CHECK_WP_MARGIN_CONSISTENCY
3735: ( p_project_id IN pa_projects_all.project_id%TYPE
3736: ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
3737: ,p_margin_code IN pa_proj_fp_options.margin_derived_from_code%TYPE
3738: ,x_margin_flag OUT NOCOPY VARCHAR2
3739: ,x_return_status OUT NOCOPY VARCHAR2
3740: ,x_msg_count OUT NOCOPY NUMBER
3741: ,x_msg_data OUT NOCOPY VARCHAR2)
3742: AS
3743:
3744: CURSOR check_margin_mask(c_project_id pa_projects_all.project_id%TYPE,
3745: c_wbs_version_id pji_xbs_denorm.sup_project_id%TYPE,
3746: c_margin_code pa_proj_fp_options.margin_derived_from_code%TYPE)
3747: IS
3748: SELECT 1
3749: FROM pji_xbs_denorm denorm,
3745: c_wbs_version_id pji_xbs_denorm.sup_project_id%TYPE,
3746: c_margin_code pa_proj_fp_options.margin_derived_from_code%TYPE)
3747: IS
3748: SELECT 1
3749: FROM pji_xbs_denorm denorm,
3750: pji_pjp_wbs_header header,
3751: pa_proj_fp_options opt
3752: WHERE denorm.sup_project_id = c_project_id
3753: AND denorm.sup_id = c_wbs_version_id
3829: */
3830:
3831: PROCEDURE CHECK_WP_STATUS_CONSISTENCY
3832: ( p_project_id IN pa_projects_all.project_id%TYPE
3833: ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
3834: ,p_published_flag IN VARCHAR2
3835: ,x_workpub_flag OUT NOCOPY VARCHAR2
3836: ,x_return_status OUT NOCOPY VARCHAR2
3837: ,x_msg_count OUT NOCOPY NUMBER
3838: ,x_msg_data OUT NOCOPY VARCHAR2)
3839: AS
3840:
3841: CURSOR check_published_flag(c_project_id pa_projects_all.project_id%TYPE,
3842: c_wbs_version_id pji_xbs_denorm.sup_project_id%TYPE,
3843: c_published_flag VARCHAR2)
3844: IS
3845: SELECT 1
3846: FROM pji_xbs_denorm denorm,
3842: c_wbs_version_id pji_xbs_denorm.sup_project_id%TYPE,
3843: c_published_flag VARCHAR2)
3844: IS
3845: SELECT 1
3846: FROM pji_xbs_denorm denorm,
3847: pa_proj_elements ele
3848: WHERE denorm.sup_project_id = c_project_id
3849: AND denorm.sup_id = c_wbs_version_id
3850: AND denorm.struct_type = 'PRG'
3922: same time phasing.
3923: */
3924: PROCEDURE CHECK_WP_TIME_CONSISTENCY
3925: ( p_project_id IN pa_projects_all.project_id%TYPE
3926: ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
3927: ,p_calendar_type IN pji_fp_xbs_accum_f.calendar_type%TYPE
3928: ,p_calendar_id IN pa_projects_all.calendar_id%TYPE
3929: ,x_time_phase_flag OUT NOCOPY VARCHAR2
3930: ,x_return_status OUT NOCOPY VARCHAR2
3933: AS
3934:
3935:
3936: CURSOR get_pgm_ver_details(c_project_id pa_projects_all.project_id%TYPE,
3937: c_wbs_version_id pji_xbs_denorm.sup_project_id%TYPE)
3938: IS
3939: SELECT header.project_id, header.plan_version_id
3940: FROM pji_xbs_denorm denorm,
3941: pji_pjp_wbs_header header
3936: CURSOR get_pgm_ver_details(c_project_id pa_projects_all.project_id%TYPE,
3937: c_wbs_version_id pji_xbs_denorm.sup_project_id%TYPE)
3938: IS
3939: SELECT header.project_id, header.plan_version_id
3940: FROM pji_xbs_denorm denorm,
3941: pji_pjp_wbs_header header
3942: WHERE denorm.sup_project_id = c_project_id
3943: AND denorm.sup_id = c_wbs_version_id
3944: AND denorm.struct_type = 'PRG'
4029: the same RBS.
4030: */
4031: PROCEDURE CHECK_WP_RBS_CONSISTENCY
4032: ( p_project_id IN pa_projects_all.project_id%TYPE
4033: ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
4034: ,p_rbs_version_id IN pa_proj_fp_options.rbs_version_id%TYPE
4035: ,x_rbs_flag OUT NOCOPY VARCHAR2
4036: ,x_return_status OUT NOCOPY VARCHAR2
4037: ,x_msg_count OUT NOCOPY NUMBER
4038: ,x_msg_data OUT NOCOPY VARCHAR2)
4039: AS
4040:
4041: CURSOR check_rbs_flag(c_project_id pa_projects_all.project_id%TYPE,
4042: c_wbs_version_id pji_xbs_denorm.sup_project_id%TYPE,
4043: c_rbs_version_id pa_proj_fp_options.rbs_version_id%TYPE)
4044: IS
4045: SELECT 1
4046: FROM pji_xbs_denorm denorm,
4042: c_wbs_version_id pji_xbs_denorm.sup_project_id%TYPE,
4043: c_rbs_version_id pa_proj_fp_options.rbs_version_id%TYPE)
4044: IS
4045: SELECT 1
4046: FROM pji_xbs_denorm denorm,
4047: pji_pjp_wbs_header header,
4048: pa_proj_fp_options opt
4049: WHERE denorm.sup_project_id = c_project_id
4050: AND denorm.sup_id = c_wbs_version_id
4704: * will return 'T' else 'F'. This logic is true for PA calendar also.
4705: */
4706: PROCEDURE Check_Perf_Cal_Consistency(
4707: p_project_id IN pa_projects_all.project_id%TYPE
4708: ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
4709: ,x_gl_flag OUT NOCOPY VARCHAR2
4710: ,x_pa_flag OUT NOCOPY VARCHAR2
4711: ,x_return_status OUT NOCOPY VARCHAR2
4712: ,x_msg_count OUT NOCOPY NUMBER
4743: COUNT(DISTINCT info.pa_calendar_id)
4744: INTO l_num_of_gl_cal,
4745: l_num_of_pa_cal
4746: FROM
4747: pji_xbs_denorm denorm
4748: , pa_proj_elements elem
4749: , pa_projects_all proj
4750: , pji_org_extr_info info
4751: WHERE 1=1
4800: * will return 'T' else 'F'.
4801: */
4802: PROCEDURE Check_Perf_Curr_Consistency(
4803: p_project_id IN pa_projects_all.project_id%TYPE
4804: ,p_wbs_version_id IN pji_xbs_denorm.sup_project_id%TYPE
4805: ,x_pfc_flag OUT NOCOPY VARCHAR2
4806: ,x_return_status OUT NOCOPY VARCHAR2
4807: ,x_msg_count OUT NOCOPY NUMBER
4808: ,x_msg_data OUT NOCOPY VARCHAR2)
4834: --Obtain the PFC count
4835: SELECT COUNT(DISTINCT proj.projfunc_currency_code)
4836: INTO l_num_of_projfunc_curr
4837: FROM
4838: pji_xbs_denorm denorm
4839: , pa_proj_elements elem
4840: , pa_projects_all proj
4841: WHERE 1=1
4842: AND denorm.sup_project_id = p_project_id -- project_id
5105:
5106: BEGIN
5107: SELECT DISTINCT head.plan_version_id,head.project_id ,pji.sub_id wbs_version_id BULK COLLECT
5108: INTO l_plan_version_id_tbl, l_project_id_tbl,l_wbs_version_id_tbl
5109: FROM pji_xbs_Denorm pji
5110: ,pji_pjp_wbs_header head
5111: WHERE pji.struct_type='PRG'
5112: --AND pji.sup_level<>pji.sub_level --Bug 4624479
5113: 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)
5322: CURSOR struct_list(c_structure_version_id pa_proj_element_versions.element_version_id%TYPE
5323: , c_structure_type VARCHAR2) IS
5324: SELECT vs.element_version_id, vs.project_id
5325: FROM pa_proj_elem_ver_structure vs
5326: , pji_xbs_denorm denorm
5327: WHERE denorm.struct_version_id IS NULL
5328: AND denorm.struct_type = 'PRG'
5329: AND NVL(denorm.relationship_type,'WF') IN ('WF',c_structure_type)
5330: AND denorm.sup_id = c_structure_version_id
5425: END IF;
5426:
5427: SELECT COUNT(DISTINCT UPPER(pt.project_type_class_code))
5428: INTO l_proj_type_count
5429: FROM pji_xbs_denorm denorm
5430: ,pa_proj_elem_ver_structure vs
5431: ,pa_projects_all proj
5432: ,pa_project_types_all pt
5433: WHERE struct_type = 'PRG'
5678:
5679: Select count(*)
5680: INTO l_count
5681: FROM
5682: pji_xbs_denorm denorm
5683: , pa_proj_elements elem
5684: , pa_proj_elem_ver_structure ppevs
5685: WHERE 1=1
5686: AND denorm.sup_project_id = p_project_id