[Home] [Help]
1945: IF p_structure_flag ='N' THEN
1946:
1947: insert into PJI_PLAN_EXTR_TMP(project_id,plan_ver_id)
1948: select sup_project_id,sub_emt_id
1949: from pji_xbs_Denorm
1950: where sup_project_id=p_project_id
1951: and struct_version_id = p_struct_ver_id
1952: and sup_emt_id=p_project_element_id
1953: and sup_level <> sub_level
1954: and abs(sup_level - sub_level) <=p_level ;
1955: ELSE
1956: insert into PJI_PLAN_EXTR_TMP(project_id,plan_ver_id)
1957: select sub.sup_project_id,sub.sub_emt_id
1958: from pji_xbs_Denorm sup,pji_xbs_Denorm sub
1959: where sup.sup_project_id=p_project_id
1960: and sup.sup_project_id =sub.sup_project_id
1961: and sup.sup_id = p_struct_ver_id
1962: and sup.sub_id = sub.sup_id
3657: (
3658: SELECT
3659: sub_id,sup_id,sub_rollup_id,sup_emt_id,'WR' record_type
3660: FROM
3661: pji_xbs_Denorm wrk
3662: WHERE
3663: wrk.STRUCT_TYPE = 'PRG' AND
3664: wrk.SUP_ID = l_wk_struct_ver_id_tbl(i) AND
3665: ( wrk.RELATIONSHIP_TYPE <>'LF' OR wrk.RELATIONSHIP_TYPE IS NULL) AND
3667: UNION ALL
3668: SELECT
3669: sub_id,sup_id,sub_rollup_id,sup_emt_id,'PR' record_type
3670: FROM
3671: pji_xbs_Denorm pub
3672: WHERE
3673: pub.STRUCT_TYPE = 'PRG' AND
3674: pub.SUP_ID = l_lpb_struct_ver_id_tbl(i) AND
3675: ( pub.RELATIONSHIP_TYPE <>'LF' OR pub.RELATIONSHIP_TYPE IS NULL) AND
3677: UNION ALL
3678: SELECT
3679: sub_id,sup_id,sub_rollup_id,sup_emt_id,'BR' record_type
3680: FROM
3681: pji_xbs_Denorm base
3682: WHERE
3683: base.STRUCT_TYPE = 'PRG' AND
3684: base.SUP_ID = l_base_struct_ver_id_tbl(i) AND
3685: ( base.RELATIONSHIP_TYPE <>'LF' OR base.RELATIONSHIP_TYPE IS NULL) AND
3686: base.struct_version_id is null
3687: UNION ALL
3688: SELECT sub_id,sup_id,sub_rollup_id,sup_emt_id,'WS' record_type
3689: FROM
3690: pji_xbs_Denorm wrk
3691: WHERE
3692: wrk.STRUCT_TYPE = 'PRG' AND
3693: wrk.SUB_ID = l_wk_struct_ver_id_tbl(i) AND
3694: ( wrk.RELATIONSHIP_TYPE <>'LF' OR wrk.RELATIONSHIP_TYPE IS NULL) AND
3695: wrk.struct_version_id is null
3696: UNION ALL
3697: SELECT sub_id,sup_id,sub_rollup_id,sup_emt_id,'PS' record_type
3698: FROM
3699: pji_xbs_Denorm pub
3700: WHERE
3701: pub.STRUCT_TYPE = 'PRG' AND
3702: pub.SUB_ID = l_lpb_struct_ver_id_tbl(i) AND
3703: ( pub.RELATIONSHIP_TYPE <>'LF' OR pub.RELATIONSHIP_TYPE IS NULL) AND
3704: pub.struct_version_id is null
3705: UNION ALL
3706: SELECT sub_id,sup_id,sub_rollup_id,sup_emt_id,'BS' record_type
3707: FROM
3708: pji_xbs_Denorm base
3709: WHERE
3710: base.STRUCT_TYPE = 'PRG' AND
3711: base.SUB_ID = l_base_struct_ver_id_tbl(i) AND
3712: ( base.RELATIONSHIP_TYPE <>'LF' OR base.RELATIONSHIP_TYPE IS NULL) AND
3751: (
3752: SELECT
3753: wrk.SUB_ID wbs_version_id,'W' record_type
3754: FROM
3755: pji_xbs_Denorm wrk
3756: WHERE
3757: wrk.STRUCT_TYPE = 'PRG' AND
3758: wrk.SUP_ID = l_wk_struct_ver_id_tbl(i) AND
3759: ( wrk.RELATIONSHIP_TYPE <>'LF' OR wrk.RELATIONSHIP_TYPE IS NULL) AND
3761: UNION ALL
3762: SELECT
3763: pub.SUB_ID wbs_version_id,'P' record_type
3764: FROM
3765: pji_xbs_Denorm pub
3766: WHERE
3767: pub.STRUCT_TYPE = 'PRG' AND
3768: pub.SUP_ID = l_lpb_struct_ver_id_tbl(i) AND
3769: ( pub.RELATIONSHIP_TYPE <>'LF' OR pub.RELATIONSHIP_TYPE IS NULL) AND
3771: UNION ALL
3772: SELECT
3773: base.SUB_ID wbs_version_id,'B' record_type
3774: FROM
3775: pji_xbs_Denorm base
3776: WHERE
3777: base.STRUCT_TYPE = 'PRG' AND
3778: base.SUP_ID = l_base_struct_ver_id_tbl(i) AND
3779: ( base.RELATIONSHIP_TYPE <>'LF' OR base.RELATIONSHIP_TYPE IS NULL) AND