[Home] [Help]
504:
505: CURSOR parent_of_previous_task( c_task_version_id NUMBER )
506: IS
507: SELECT object_id_from1
508: FROM pa_object_relationships
509: WHERE object_id_to1 = c_task_version_id
510: and object_type_from in ('PA_STRUCTURES','PA_TASKS') -- bug 6429275
511: and object_type_to = 'PA_TASKS'
512: and relationship_type = 'S';
1019: WHERE project_id = p_project_id
1020: AND object_type = 'PA_TASKS'
1021: AND parent_structure_version_id = c_structure_version_id
1022: and element_version_id in ( SELECT object_id_to1
1023: FROM pa_object_relationships
1024: WHERE object_id_from1 = c_parent_task_version_id
1025: and relationship_type = 'S'
1026: )
1027: AND display_sequence = ( SELECT max( display_sequence )
1029: WHERE project_id = p_project_id
1030: AND object_type = 'PA_TASKS'
1031: AND parent_structure_version_id = c_structure_version_id
1032: and element_version_id in ( SELECT object_id_to1
1033: FROM pa_object_relationships
1034: WHERE object_id_from1 = c_parent_task_version_id
1035: and relationship_type = 'S'
1036: )
1037: );
1066:
1067: CURSOR cur_child_exists(c_task_ver_id NUMBER)
1068: IS
1069: select 'Y'
1070: FROM pa_object_relationships
1071: where object_id_from1 = c_task_ver_id
1072: AND object_type_from IN ('PA_TASKS', 'PA_STRUCTURES') --Corrected 'PA_TASK' TO 'PA_TASKS' for Bug 4035526
1073: and relationship_type = 'S';
1074: l_child_exists VARCHAR2(1);
1141:
1142: --hsiu added for task status
1143: CURSOR get_parent_version_id(c_elem_ver_id NUMBER) IS
1144: select object_id_from1
1145: from pa_object_relationships
1146: where object_id_to1 = c_elem_ver_id
1147: and object_type_to = 'PA_TASKS'
1148: and relationship_type = 'S';
1149: l_parent_ver_id NUMBER;
2561:
2562: CURSOR cur_pa_obj_rel( c_task_version_id NUMBER )
2563: IS
2564: SELECT object_id_from1
2565: FROM pa_object_relationships
2566: WHERE object_id_to1 = c_task_version_id
2567: and relationship_type = 'S';
2568:
2569: CURSOR cur_pa_proj_elem_ver( c_task_version_id NUMBER )
3926: ,p_msg_name => l_error_message_code);
3927: raise api_error;
3928: END IF;
3929:
3930: DELETE FROM pa_object_relationships
3931: WHERE relationship_type = 'S' AND
3932: (object_id_from1 = cur_task_ver_rec.element_version_id OR
3933: object_id_to1 = cur_task_ver_rec.element_version_id);
3934:
3950: ,p_msg_name => l_error_message_code);
3951: raise api_error;
3952: END IF;
3953:
3954: DELETE FROM pa_object_relationships
3955: WHERE relationship_type = 'S' AND
3956: (object_id_from1 = cur_str_ver_rec.element_version_id OR
3957: object_id_to1 = cur_str_ver_rec.element_version_id);
3958:
4656: FETCH cur_pa_proj_elem_vers INTO l_cur_pa_proj_elem_vers_rec;
4657: IF cur_pa_proj_elem_vers%FOUND
4658: THEN
4659: FOR cur_task_vers_rec IN cur_task_vers( l_cur_pa_proj_elem_vers_rec.element_version_id ) LOOP
4660: DELETE FROM pa_object_relationships WHERE relationship_type = 'S' AND
4661: (object_id_to1 = cur_task_vers_rec.element_version_id OR
4662: object_id_from1 = cur_task_vers_rec.element_version_id);
4663:
4664: --it is possible that COSTING and WORKPLAN str are same. so delete from schedule versions as well.
7068: -- End of code added for bug 3387963
7069:
7070: /* Code added for 6211012*/
7071:
7072: TYPE typ_obj_rela_id is TABLE OF pa_object_relationships.object_relationship_id%TYPE
7073: INDEX BY BINARY_INTEGER;
7074: l_obj_rel_id typ_obj_rela_id;
7075:
7076: TYPE typ_obj_typ_from is TABLE OF pa_object_relationships.object_type_from%TYPE
7072: TYPE typ_obj_rela_id is TABLE OF pa_object_relationships.object_relationship_id%TYPE
7073: INDEX BY BINARY_INTEGER;
7074: l_obj_rel_id typ_obj_rela_id;
7075:
7076: TYPE typ_obj_typ_from is TABLE OF pa_object_relationships.object_type_from%TYPE
7077: INDEX BY BINARY_INTEGER;
7078: l_obj_typ_from typ_obj_typ_from;
7079:
7080: TYPE typ_obj_from_id is TABLE OF pa_object_relationships.object_id_from1%TYPE
7076: TYPE typ_obj_typ_from is TABLE OF pa_object_relationships.object_type_from%TYPE
7077: INDEX BY BINARY_INTEGER;
7078: l_obj_typ_from typ_obj_typ_from;
7079:
7080: TYPE typ_obj_from_id is TABLE OF pa_object_relationships.object_id_from1%TYPE
7081: INDEX BY BINARY_INTEGER;
7082: l_obj_from_id typ_obj_from_id;
7083:
7084: TYPE typ_obj_typ_to is TABLE OF pa_object_relationships.object_type_to%TYPE
7080: TYPE typ_obj_from_id is TABLE OF pa_object_relationships.object_id_from1%TYPE
7081: INDEX BY BINARY_INTEGER;
7082: l_obj_from_id typ_obj_from_id;
7083:
7084: TYPE typ_obj_typ_to is TABLE OF pa_object_relationships.object_type_to%TYPE
7085: INDEX BY BINARY_INTEGER;
7086: l_obj_typ_to typ_obj_typ_to;
7087:
7088: TYPE typ_obj_to_id is TABLE OF pa_object_relationships.object_id_to1%TYPE
7084: TYPE typ_obj_typ_to is TABLE OF pa_object_relationships.object_type_to%TYPE
7085: INDEX BY BINARY_INTEGER;
7086: l_obj_typ_to typ_obj_typ_to;
7087:
7088: TYPE typ_obj_to_id is TABLE OF pa_object_relationships.object_id_to1%TYPE
7089: INDEX BY BINARY_INTEGER;
7090: l_obj_to_id typ_obj_to_id;
7091:
7092: TYPE typ_rel_typ is TABLE OF pa_object_relationships.relationship_type%TYPE
7088: TYPE typ_obj_to_id is TABLE OF pa_object_relationships.object_id_to1%TYPE
7089: INDEX BY BINARY_INTEGER;
7090: l_obj_to_id typ_obj_to_id;
7091:
7092: TYPE typ_rel_typ is TABLE OF pa_object_relationships.relationship_type%TYPE
7093: INDEX BY BINARY_INTEGER;
7094: l_rel_typ typ_rel_typ;
7095:
7096: TYPE typ_rel_subtyp is TABLE OF pa_object_relationships.relationship_subtype%TYPE
7092: TYPE typ_rel_typ is TABLE OF pa_object_relationships.relationship_type%TYPE
7093: INDEX BY BINARY_INTEGER;
7094: l_rel_typ typ_rel_typ;
7095:
7096: TYPE typ_rel_subtyp is TABLE OF pa_object_relationships.relationship_subtype%TYPE
7097: INDEX BY BINARY_INTEGER;
7098: l_rel_subtyp typ_rel_subtyp;
7099:
7100: TYPE typ_rec_ver_num is TABLE OF pa_object_relationships.Record_Version_Number%TYPE
7096: TYPE typ_rel_subtyp is TABLE OF pa_object_relationships.relationship_subtype%TYPE
7097: INDEX BY BINARY_INTEGER;
7098: l_rel_subtyp typ_rel_subtyp;
7099:
7100: TYPE typ_rec_ver_num is TABLE OF pa_object_relationships.Record_Version_Number%TYPE
7101: INDEX BY BINARY_INTEGER;
7102: l_rec_ver_num typ_rec_ver_num;
7103:
7104: TYPE typ_wt_percent is TABLE OF pa_object_relationships.weighting_percentage%TYPE
7100: TYPE typ_rec_ver_num is TABLE OF pa_object_relationships.Record_Version_Number%TYPE
7101: INDEX BY BINARY_INTEGER;
7102: l_rec_ver_num typ_rec_ver_num;
7103:
7104: TYPE typ_wt_percent is TABLE OF pa_object_relationships.weighting_percentage%TYPE
7105: INDEX BY BINARY_INTEGER;
7106: l_wt_percent typ_wt_percent;
7107:
7108:
7109: CURSOR cur_pa_obj_rela_1(c_src_fin_version_id NUMBER,
7110: c_dest_project_id NUMBER
7111: ) IS
7112: SELECT
7113: pa_object_relationships_s.nextval,
7114: pobj.object_type_from,
7115: ppev1.element_version_id,
7116: pobj.object_type_to,
7117: ppev2.element_version_id,
7126: relationship_type,
7127: relationship_subtype,
7128: Record_Version_Number,
7129: weighting_percentage
7130: FROM pa_object_relationships
7131: START WITH object_id_from1 = c_src_fin_version_id
7132: AND RELATIONSHIP_TYPE = 'S'
7133: CONNECT BY object_id_from1 = PRIOR object_id_to1
7134: AND RELATIONSHIP_TYPE = 'S' ) pobj,
7512:
7513: IF l_obj_rel_id.COUNT >0 THEN
7514:
7515: FORALL i IN l_obj_rel_id.FIRST..l_obj_rel_id.LAST
7516: insert into PA_OBJECT_RELATIONSHIPS (
7517: object_relationship_id,
7518: object_type_from,
7519: object_id_from1,
7520: object_type_to,
7564: CLOSE cur_pa_obj_rela_1;
7565:
7566: /*
7567:
7568: INSERT INTO PA_OBJECT_RELATIONSHIPS (
7569: object_relationship_id
7570: ,object_type_from
7571: ,object_id_from1
7572: ,object_type_to
7581: ,LAST_UPDATE_LOGIN
7582: ,weighting_percentage
7583: )
7584: SELECT
7585: pa_object_relationships_s.nextval
7586: ,pobj.object_type_from
7587: ,ppev1.element_version_id
7588: ,pobj.object_type_to
7589: ,ppev2.element_version_id
7604: relationship_type,
7605: relationship_subtype,
7606: Record_Version_Number,
7607: weighting_percentage
7608: FROM pa_object_relationships
7609: START WITH object_id_from1 = l_src_fin_version_id
7610: AND RELATIONSHIP_TYPE = 'S'
7611: CONNECT BY object_id_from1 = PRIOR object_id_to1
7612: AND RELATIONSHIP_TYPE = 'S' ) pobj,
8083: --3916763 End
8084:
8085: /* Code added for 6211012*/
8086:
8087: TYPE typ_obj_rela_id is TABLE OF pa_object_relationships.object_relationship_id%TYPE
8088: INDEX BY BINARY_INTEGER;
8089: l_obj_rel_id typ_obj_rela_id;
8090:
8091: TYPE typ_obj_typ_from is TABLE OF pa_object_relationships.object_type_from%TYPE
8087: TYPE typ_obj_rela_id is TABLE OF pa_object_relationships.object_relationship_id%TYPE
8088: INDEX BY BINARY_INTEGER;
8089: l_obj_rel_id typ_obj_rela_id;
8090:
8091: TYPE typ_obj_typ_from is TABLE OF pa_object_relationships.object_type_from%TYPE
8092: INDEX BY BINARY_INTEGER;
8093: l_obj_typ_from typ_obj_typ_from;
8094:
8095: TYPE typ_obj_from_id is TABLE OF pa_object_relationships.object_id_from1%TYPE
8091: TYPE typ_obj_typ_from is TABLE OF pa_object_relationships.object_type_from%TYPE
8092: INDEX BY BINARY_INTEGER;
8093: l_obj_typ_from typ_obj_typ_from;
8094:
8095: TYPE typ_obj_from_id is TABLE OF pa_object_relationships.object_id_from1%TYPE
8096: INDEX BY BINARY_INTEGER;
8097: l_obj_from_id typ_obj_from_id;
8098:
8099: TYPE typ_obj_typ_to is TABLE OF pa_object_relationships.object_type_to%TYPE
8095: TYPE typ_obj_from_id is TABLE OF pa_object_relationships.object_id_from1%TYPE
8096: INDEX BY BINARY_INTEGER;
8097: l_obj_from_id typ_obj_from_id;
8098:
8099: TYPE typ_obj_typ_to is TABLE OF pa_object_relationships.object_type_to%TYPE
8100: INDEX BY BINARY_INTEGER;
8101: l_obj_typ_to typ_obj_typ_to;
8102:
8103: TYPE typ_obj_to_id is TABLE OF pa_object_relationships.object_id_to1%TYPE
8099: TYPE typ_obj_typ_to is TABLE OF pa_object_relationships.object_type_to%TYPE
8100: INDEX BY BINARY_INTEGER;
8101: l_obj_typ_to typ_obj_typ_to;
8102:
8103: TYPE typ_obj_to_id is TABLE OF pa_object_relationships.object_id_to1%TYPE
8104: INDEX BY BINARY_INTEGER;
8105: l_obj_to_id typ_obj_to_id;
8106:
8107: TYPE typ_rel_typ is TABLE OF pa_object_relationships.relationship_type%TYPE
8103: TYPE typ_obj_to_id is TABLE OF pa_object_relationships.object_id_to1%TYPE
8104: INDEX BY BINARY_INTEGER;
8105: l_obj_to_id typ_obj_to_id;
8106:
8107: TYPE typ_rel_typ is TABLE OF pa_object_relationships.relationship_type%TYPE
8108: INDEX BY BINARY_INTEGER;
8109: l_rel_typ typ_rel_typ;
8110:
8111: TYPE typ_rel_subtyp is TABLE OF pa_object_relationships.relationship_subtype%TYPE
8107: TYPE typ_rel_typ is TABLE OF pa_object_relationships.relationship_type%TYPE
8108: INDEX BY BINARY_INTEGER;
8109: l_rel_typ typ_rel_typ;
8110:
8111: TYPE typ_rel_subtyp is TABLE OF pa_object_relationships.relationship_subtype%TYPE
8112: INDEX BY BINARY_INTEGER;
8113: l_rel_subtyp typ_rel_subtyp;
8114:
8115: TYPE typ_rec_ver_num is TABLE OF pa_object_relationships.Record_Version_Number%TYPE
8111: TYPE typ_rel_subtyp is TABLE OF pa_object_relationships.relationship_subtype%TYPE
8112: INDEX BY BINARY_INTEGER;
8113: l_rel_subtyp typ_rel_subtyp;
8114:
8115: TYPE typ_rec_ver_num is TABLE OF pa_object_relationships.Record_Version_Number%TYPE
8116: INDEX BY BINARY_INTEGER;
8117: l_rec_ver_num typ_rec_ver_num;
8118:
8119: TYPE typ_wt_percent is TABLE OF pa_object_relationships.weighting_percentage%TYPE
8115: TYPE typ_rec_ver_num is TABLE OF pa_object_relationships.Record_Version_Number%TYPE
8116: INDEX BY BINARY_INTEGER;
8117: l_rec_ver_num typ_rec_ver_num;
8118:
8119: TYPE typ_wt_percent is TABLE OF pa_object_relationships.weighting_percentage%TYPE
8120: INDEX BY BINARY_INTEGER;
8121: l_wt_percent typ_wt_percent;
8122:
8123:
8124: CURSOR cur_pa_obj_rela_2(c_src_str_version_id NUMBER,
8125: c_struc_version_id NUMBER
8126: ) IS
8127: SELECT /*+ USE_HASH(ppev2 ppev1)*/
8128: pa_object_relationships_s.nextval,
8129: pobj.object_type_from,
8130: ppev1.element_version_id,
8131: pobj.object_type_to,
8132: ppev2.element_version_id,
8137: FROM ( SELECT object_type_from, object_id_from1,
8138: object_type_to, object_id_to1,
8139: relationship_type, relationship_subtype,
8140: Record_Version_Number, weighting_percentage
8141: FROM pa_object_relationships
8142: START WITH object_id_from1 = c_src_str_version_id
8143: AND RELATIONSHIP_TYPE = 'S'
8144: CONNECT BY object_id_from1 = PRIOR object_id_to1
8145: AND RELATIONSHIP_TYPE = 'S' ) pobj,
9729:
9730: IF l_obj_rel_id.COUNT >0 THEN
9731:
9732: FORALL i IN l_obj_rel_id.FIRST..l_obj_rel_id.LAST
9733: insert into PA_OBJECT_RELATIONSHIPS (
9734: object_relationship_id,
9735: object_type_from,
9736: object_id_from1,
9737: object_type_to,
9780:
9781: CLOSE cur_pa_obj_rela_2;
9782:
9783: /*
9784: INSERT INTO PA_OBJECT_RELATIONSHIPS (
9785: object_relationship_id
9786: ,object_type_from
9787: ,object_id_from1
9788: ,object_type_to
9798: ,weighting_percentage
9799: )
9800: /*Added the hint for the bug 3513853*/
9801: /* SELECT /*+ USE_HASH(ppev2 ppev1)*/
9802: /* pa_object_relationships_s.nextval
9803: ,pobj.object_type_from
9804: ,ppev1.element_version_id
9805: ,pobj.object_type_to
9806: ,ppev2.element_version_id
9821: relationship_type,
9822: relationship_subtype,
9823: Record_Version_Number,
9824: weighting_percentage
9825: FROM pa_object_relationships
9826: START WITH object_id_from1 = rec_dest_wp_struc_versions.attribute15
9827: AND RELATIONSHIP_TYPE = 'S'
9828: CONNECT BY object_id_from1 = PRIOR object_id_to1
9829: AND RELATIONSHIP_TYPE = 'S' ) pobj,
10979: l_latest_pub_str_ver_id NUMBER;
10980:
10981: /* Code added for 6211012*/
10982:
10983: TYPE typ_obj_rela_id is TABLE OF pa_object_relationships.object_relationship_id%TYPE
10984: INDEX BY BINARY_INTEGER;
10985: l_obj_rel_id typ_obj_rela_id;
10986:
10987: TYPE typ_obj_typ_from is TABLE OF pa_object_relationships.object_type_from%TYPE
10983: TYPE typ_obj_rela_id is TABLE OF pa_object_relationships.object_relationship_id%TYPE
10984: INDEX BY BINARY_INTEGER;
10985: l_obj_rel_id typ_obj_rela_id;
10986:
10987: TYPE typ_obj_typ_from is TABLE OF pa_object_relationships.object_type_from%TYPE
10988: INDEX BY BINARY_INTEGER;
10989: l_obj_typ_from typ_obj_typ_from;
10990:
10991: TYPE typ_obj_from_id is TABLE OF pa_object_relationships.object_id_from1%TYPE
10987: TYPE typ_obj_typ_from is TABLE OF pa_object_relationships.object_type_from%TYPE
10988: INDEX BY BINARY_INTEGER;
10989: l_obj_typ_from typ_obj_typ_from;
10990:
10991: TYPE typ_obj_from_id is TABLE OF pa_object_relationships.object_id_from1%TYPE
10992: INDEX BY BINARY_INTEGER;
10993: l_obj_from_id typ_obj_from_id;
10994:
10995: TYPE typ_obj_typ_to is TABLE OF pa_object_relationships.object_type_to%TYPE
10991: TYPE typ_obj_from_id is TABLE OF pa_object_relationships.object_id_from1%TYPE
10992: INDEX BY BINARY_INTEGER;
10993: l_obj_from_id typ_obj_from_id;
10994:
10995: TYPE typ_obj_typ_to is TABLE OF pa_object_relationships.object_type_to%TYPE
10996: INDEX BY BINARY_INTEGER;
10997: l_obj_typ_to typ_obj_typ_to;
10998:
10999: TYPE typ_obj_to_id is TABLE OF pa_object_relationships.object_id_to1%TYPE
10995: TYPE typ_obj_typ_to is TABLE OF pa_object_relationships.object_type_to%TYPE
10996: INDEX BY BINARY_INTEGER;
10997: l_obj_typ_to typ_obj_typ_to;
10998:
10999: TYPE typ_obj_to_id is TABLE OF pa_object_relationships.object_id_to1%TYPE
11000: INDEX BY BINARY_INTEGER;
11001: l_obj_to_id typ_obj_to_id;
11002:
11003: TYPE typ_rel_typ is TABLE OF pa_object_relationships.relationship_type%TYPE
10999: TYPE typ_obj_to_id is TABLE OF pa_object_relationships.object_id_to1%TYPE
11000: INDEX BY BINARY_INTEGER;
11001: l_obj_to_id typ_obj_to_id;
11002:
11003: TYPE typ_rel_typ is TABLE OF pa_object_relationships.relationship_type%TYPE
11004: INDEX BY BINARY_INTEGER;
11005: l_rel_typ typ_rel_typ;
11006:
11007: TYPE typ_rel_subtyp is TABLE OF pa_object_relationships.relationship_subtype%TYPE
11003: TYPE typ_rel_typ is TABLE OF pa_object_relationships.relationship_type%TYPE
11004: INDEX BY BINARY_INTEGER;
11005: l_rel_typ typ_rel_typ;
11006:
11007: TYPE typ_rel_subtyp is TABLE OF pa_object_relationships.relationship_subtype%TYPE
11008: INDEX BY BINARY_INTEGER;
11009: l_rel_subtyp typ_rel_subtyp;
11010:
11011: TYPE typ_rec_ver_num is TABLE OF pa_object_relationships.Record_Version_Number%TYPE
11007: TYPE typ_rel_subtyp is TABLE OF pa_object_relationships.relationship_subtype%TYPE
11008: INDEX BY BINARY_INTEGER;
11009: l_rel_subtyp typ_rel_subtyp;
11010:
11011: TYPE typ_rec_ver_num is TABLE OF pa_object_relationships.Record_Version_Number%TYPE
11012: INDEX BY BINARY_INTEGER;
11013: l_rec_ver_num typ_rec_ver_num;
11014:
11015: TYPE typ_wt_percent is TABLE OF pa_object_relationships.weighting_percentage%TYPE
11011: TYPE typ_rec_ver_num is TABLE OF pa_object_relationships.Record_Version_Number%TYPE
11012: INDEX BY BINARY_INTEGER;
11013: l_rec_ver_num typ_rec_ver_num;
11014:
11015: TYPE typ_wt_percent is TABLE OF pa_object_relationships.weighting_percentage%TYPE
11016: INDEX BY BINARY_INTEGER;
11017: l_wt_percent typ_wt_percent;
11018:
11019:
11020: CURSOR cur_pa_obj_rela_3(c_src_str_version_id NUMBER,
11021: c_struc_version_id NUMBER
11022: ) IS
11023: SELECT /*+ USE_HASH(ppev2 ppev1)*/
11024: pa_object_relationships_s.nextval,
11025: pobj.object_type_from,
11026: ppev1.element_version_id,
11027: pobj.object_type_to,
11028: ppev2.element_version_id,
11033: FROM ( SELECT object_type_from, object_id_from1,
11034: object_type_to, object_id_to1,
11035: relationship_type, relationship_subtype,
11036: Record_Version_Number, weighting_percentage
11037: FROM pa_object_relationships
11038: --bug#3094283WHERE RELATIONSHIP_TYPE = 'S'
11039: start with object_id_from1 = c_src_str_version_id
11040: and RELATIONSHIP_TYPE = 'S' /* Bug 2881667 - Added this condition */
11041: connect by object_id_from1 = prior object_id_to1
12723:
12724: IF l_obj_rel_id.COUNT >0 THEN
12725:
12726: FORALL i IN l_obj_rel_id.FIRST..l_obj_rel_id.LAST
12727: insert into PA_OBJECT_RELATIONSHIPS (
12728: object_relationship_id,
12729: object_type_from,
12730: object_id_from1,
12731: object_type_to,
12773:
12774: CLOSE cur_pa_obj_rela_3;
12775:
12776: /*
12777: INSERT INTO PA_OBJECT_RELATIONSHIPS (
12778: object_relationship_id,
12779: object_type_from,
12780: object_id_from1,
12781: object_type_to,
12791: weighting_percentage
12792: )
12793: /*Added the hint for the bug 3513853*/
12794: /* SELECT /*+ USE_HASH(ppev2 ppev1)*/
12795: /* pa_object_relationships_s.nextval,
12796: pobj.object_type_from,
12797: ppev1.element_version_id,
12798: pobj.object_type_to,
12799: ppev2.element_version_id,
12809: FROM ( SELECT object_type_from, object_id_from1,
12810: object_type_to, object_id_to1,
12811: relationship_type, relationship_subtype,
12812: Record_Version_Number, weighting_percentage
12813: FROM pa_object_relationships
12814: --bug#3094283WHERE RELATIONSHIP_TYPE = 'S'
12815: start with object_id_from1 = l_dest_str_versions_tbl(k).src_str_version_id
12816: and RELATIONSHIP_TYPE = 'S' /* Bug 2881667 Added this condition */
12817: /* connect by object_id_from1 = prior object_id_to1
13703: SELECT object_id_from1 -- parent task id
13704: ,object_relationship_id, -- relationship id reqd for update relationship API
13705: record_version_number,
13706: weighting_percentage -- Bug 3075609. We need to get hold of the weightage of the task in db.
13707: FROM pa_object_relationships
13708: WHERE object_id_to1 = c_task_version_id
13709: AND relationship_type = 'S';
13710:
13711: -- Added for DFF attributes for calling update_schedule_version
13767: TYPE wbs_number_tbl IS TABLE OF pa_proj_element_versions.wbs_number%TYPE
13768: INDEX BY BINARY_INTEGER;
13769: TYPE child_count_tbl IS TABLE OF NUMBER
13770: INDEX BY BINARY_INTEGER;
13771: TYPE weighting_percentage_tbl IS TABLE OF pa_object_relationships.weighting_percentage%TYPE
13772: INDEX BY BINARY_INTEGER;
13773: TYPE task_version_id_tbl IS TABLE OF pa_proj_element_versions.element_version_id%TYPE
13774: INDEX BY BINARY_INTEGER;
13775:
13812:
13813: -- version level variables required only for update task case.
13814: l_proj_elem_ver_rvn pa_proj_element_versions.record_version_number%TYPE; -- record version number of proj element table
13815: l_proj_elem_ver_rowid VARCHAR2(255); -- rowid of the task being updated.
13816: l_relationship_id pa_object_relationships.object_relationship_id%TYPE;
13817: l_obj_rel_rvn NUMBER;
13818:
13819: -- flexfield attributes used for updating/creating element version and schedule versions table.
13820: l_attribute_category PA_PROJ_ELEMENT_VERSIONS.attribute_category%TYPE;
15118:
15119: l_task_ver_id_tmp pa_proj_element_versions.element_version_id%TYPE;
15120: l_dummy_number NUMBER;
15121: l_db_parent_task_ver_id_tmp pa_proj_element_versions.element_version_id%TYPE;
15122: l_relationship_id_tmp pa_object_relationships.object_relationship_id%TYPE;
15123:
15124: BEGIN
15125:
15126: OPEN cur_pa_task_ver( l_structure_version_id, l_task_in_rec.pa_task_id );
15733: -- This cursor identified the number of progressible tasks under a parent.
15734: CURSOR get_child_count(c_parent_element_version_id NUMBER)
15735: IS
15736: SELECT count(rel.object_id_to1)
15737: FROM PA_OBJECT_RELATIONSHIPS rel, pa_proj_element_versions pev, pa_proj_elements pe, pa_task_types tt
15738: WHERE rel.object_id_from1 = c_parent_element_version_id
15739: AND rel.object_type_to = 'PA_TASKS'
15740: AND rel.relationship_type = 'S'
15741: AND rel.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
16179: l_db_parent_task_version_id pa_proj_element_versions.element_version_id%TYPE;
16180: l_parent_task_version_id pa_proj_element_versions.element_version_id%TYPE;
16181:
16182: -- Bug 3075609. This will hold the weightage of the task version currently in database.
16183: l_db_task_weightage pa_object_relationships.weighting_percentage%TYPE;
16184: BEGIN
16185: IF (l_parent_task_id IS NOT null)
16186: THEN
16187: l_parent_task_version_id := l_task_version_id_tbl(l_parent_task_id);
16474:
16475: DECLARE
16476: l_structure_version_id_from pa_proj_element_versions.element_version_id%TYPE;
16477: l_task_version_id_from pa_proj_element_versions.element_version_id%TYPE;
16478: l_relationship_subtype pa_object_relationships.relationship_subtype%TYPE;
16479:
16480: BEGIN
16481:
16482: -- Set the following local variables before calling PA_RELATIONSHIP_PUB.Create_Relationship
17177: -- reassign the task weightages for the affected peer tasks.
17178: CURSOR cur_get_child_info(c_parent_element_version_id NUMBER)
17179: IS
17180: SELECT rel.OBJECT_RELATIONSHIP_ID,tt.prog_entry_enable_flag
17181: FROM PA_OBJECT_RELATIONSHIPS rel, pa_proj_element_versions pev, pa_proj_elements pe, pa_task_types tt
17182: WHERE rel.object_id_from1 = c_parent_element_version_id
17183: AND rel.object_type_to = 'PA_TASKS'
17184: AND rel.relationship_type = 'S'
17185: AND rel.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
17187: AND pev.proj_element_id = pe.proj_element_id
17188: AND tt.object_type = 'PA_TASKS' /* bug 3279978 FP M Enhancement */
17189: AND pe.type_id = tt.task_type_id;
17190:
17191: TYPE obj_rel_id_tbl is table of pa_object_relationships.object_relationship_id%TYPE
17192: INDEX BY BINARY_INTEGER;
17193:
17194: l_parent_version_id pa_proj_element_versions.element_version_id%TYPE;
17195: l_prog_entry_enable_flag_tbl PA_PLSQL_DATATYPES.Char1TabTyp;
17224: END IF;
17225: END LOOP;
17226: -- update back the records.
17227: FORALL i in l_obj_rel_id_tbl.first..l_obj_rel_id_tbl.last
17228: UPDATE pa_object_relationships
17229: set weighting_percentage = l_task_weightage_tbl(i)
17230: where object_relationship_id = l_obj_rel_id_tbl(i);
17231: END IF;
17232: EXIT WHEN nvl(l_obj_rel_id_tbl.last,0) < l_plsql_max_array_size;
21150: (
21151: summed_effort pa_proj_elem_ver_schedule.planned_effort%TYPE
21152: ,summed_duration pa_proj_elem_ver_schedule.duration%TYPE
21153: ,child_count Number
21154: ,cum_alloted_weightage pa_object_relationships.weighting_percentage%TYPE
21155: ,processed_child_count Number
21156: -- Following added for bug 3053472
21157: ,loop_count Number
21158: ,residual_task_count Number
21164:
21165: TYPE prog_enable_flag_tbl IS TABLE OF pa_task_types.prog_entry_enable_flag%TYPE
21166: INDEX BY BINARY_INTEGER;
21167:
21168: TYPE object_rel_id_tbl IS TABLE OF pa_object_relationships.object_relationship_id%TYPE
21169: INDEX BY BINARY_INTEGER;
21170:
21171: TYPE parent_task_ver_id_tbl IS TABLE OF pa_object_relationships.object_id_from1%TYPE --pa_proj_element_versions.element_version_id%TYPE 3746912
21172: INDEX BY BINARY_INTEGER;
21167:
21168: TYPE object_rel_id_tbl IS TABLE OF pa_object_relationships.object_relationship_id%TYPE
21169: INDEX BY BINARY_INTEGER;
21170:
21171: TYPE parent_task_ver_id_tbl IS TABLE OF pa_object_relationships.object_id_from1%TYPE --pa_proj_element_versions.element_version_id%TYPE 3746912
21172: INDEX BY BINARY_INTEGER;
21173:
21174: TYPE duration_tbl IS TABLE OF pa_proj_elem_ver_schedule.duration%TYPE
21175: INDEX BY BINARY_INTEGER;
21176:
21177: TYPE effort_tbl IS TABLE OF pji_xbs_plans_v.labor_effort%TYPE --pa_proj_elem_ver_schedule.planned_effort%TYPE 3746912
21178: INDEX BY BINARY_INTEGER;
21179:
21180: TYPE task_weightage_tbl IS TABLE OF pa_object_relationships.weighting_percentage%TYPE
21181: INDEX BY BINARY_INTEGER;
21182: -- End of Type Declarations.
21183:
21184:
21197: NVL(pxpv.labor_effort,0) + NVL(pxpv.equipment_effort,0),
21198: sch.duration,
21199: ptt.prog_entry_enable_flag
21200: from pa_proj_element_versions ver,
21201: pa_object_relationships rel,
21202: pa_proj_elem_ver_schedule sch,
21203: pa_proj_elements ele,
21204: pa_task_types ptt,
21205: pji_xbs_plans_v pxpv
21484: DECLARE
21485: l_task_weightage_tbl task_weightage_tbl;
21486:
21487: l_parent_id pa_proj_element_versions.element_version_id%TYPE;
21488: l_task_weightage pa_object_relationships.weighting_percentage%TYPE;
21489:
21490: --Bug 3053472 - remaining weightage is no more required.
21491: --l_remaining_weightage pa_object_relationships.weighting_percentage%TYPE;
21492:
21487: l_parent_id pa_proj_element_versions.element_version_id%TYPE;
21488: l_task_weightage pa_object_relationships.weighting_percentage%TYPE;
21489:
21490: --Bug 3053472 - remaining weightage is no more required.
21491: --l_remaining_weightage pa_object_relationships.weighting_percentage%TYPE;
21492:
21493: l_residual_weightage pa_object_relationships.weighting_percentage%TYPE;
21494: BEGIN
21495: IF nvl(l_object_rel_id_tbl.last,0) > 0 THEN
21489:
21490: --Bug 3053472 - remaining weightage is no more required.
21491: --l_remaining_weightage pa_object_relationships.weighting_percentage%TYPE;
21492:
21493: l_residual_weightage pa_object_relationships.weighting_percentage%TYPE;
21494: BEGIN
21495: IF nvl(l_object_rel_id_tbl.last,0) > 0 THEN
21496: FOR i in l_object_rel_id_tbl.first..l_object_rel_id_tbl.last LOOP
21497: l_parent_id := l_parent_task_ver_id_tbl(i);
21581: END IF;
21582: END IF;
21583: END LOOP;
21584:
21585: -- Do a bulk update into pa_object_relationships table as the weightage has
21586: -- been calculated for all the tasks.
21587: FORALL i in l_object_rel_id_tbl.first..l_object_rel_id_tbl.last
21588: UPDATE pa_object_relationships
21589: SET weighting_percentage = l_task_weightage_tbl(i)
21584:
21585: -- Do a bulk update into pa_object_relationships table as the weightage has
21586: -- been calculated for all the tasks.
21587: FORALL i in l_object_rel_id_tbl.first..l_object_rel_id_tbl.last
21588: UPDATE pa_object_relationships
21589: SET weighting_percentage = l_task_weightage_tbl(i)
21590: WHERE object_relationship_id = l_object_rel_id_tbl(i);
21591: IF l_debug_mode = 'Y' THEN
21592: pa_debug.g_err_stage:= 'Updated ' || sql%rowcount || ' Records in PA_OBJECT_RELATIONSHIPS';
21588: UPDATE pa_object_relationships
21589: SET weighting_percentage = l_task_weightage_tbl(i)
21590: WHERE object_relationship_id = l_object_rel_id_tbl(i);
21591: IF l_debug_mode = 'Y' THEN
21592: pa_debug.g_err_stage:= 'Updated ' || sql%rowcount || ' Records in PA_OBJECT_RELATIONSHIPS';
21593: pa_debug.write(G_PKG_NAME,pa_debug.g_err_stage,l_debug_level3);
21594: END IF;
21595: END IF;
21596: END;
22531: decode(sign(NVL(POR.Lag_Day,0)),
22532: '0', '', '-1', '', '+') || decode(POR.Lag_Day,0, NULL, POR.Lag_day)/* bug#8583608(10*8*60)*/ LagDays --bug 4348156
22533: From PA_Proj_Elements PPE,
22534: PA_Proj_Element_Versions PEV,
22535: PA_Object_Relationships POR,
22536: PA_PROJ_ELEMENT_VERSIONS PEV2
22537: Where PPE.project_id = PEV.project_id
22538: and PPE.proj_element_id = PEV.proj_element_id
22539: and PEV.element_version_id = POR.object_id_to1
22561: decode(sign(NVL(POR.Lag_Day,0)),
22562: '0', '', '-1', '', '+') || decode(POR.Lag_Day,0, NULL, POR.Lag_day)/* bug#8583608 (10*8*60)*/ LagDays --bug 4348156
22563: From PA_Proj_Elements PPE,
22564: PA_Proj_Element_Versions PEV,
22565: PA_Object_Relationships POR,
22566: PA_PROJ_ELEMENT_VERSIONS PEV2
22567: Where PPE.project_id = PEV.project_id
22568: and PPE.proj_element_id = PEV.proj_element_id
22569: and PEV.element_version_id = POR.object_id_to1
22630: raise;
22631: END;
22632: IF l_object_type='PA_TASKS' THEN
22633: BEGIN
22634: DELETE FROM pa_object_relationships por1
22635: WHERE por1.object_relationship_id IN
22636: (SELECT por.object_relationship_id
22637: FROM pa_object_relationships por
22638: WHERE por.relationship_type = 'D'
22633: BEGIN
22634: DELETE FROM pa_object_relationships por1
22635: WHERE por1.object_relationship_id IN
22636: (SELECT por.object_relationship_id
22637: FROM pa_object_relationships por
22638: WHERE por.relationship_type = 'D'
22639: AND por.object_id_from1 = p_element_version_id
22640: AND por.object_id_from2 = por.object_id_to2
22641: AND por.object_type_from = 'PA_TASKS'
22650: raise;
22651: END;
22652: ELSIF l_object_type='PA_STRUCTURES' THEN
22653: BEGIN
22654: DELETE FROM pa_object_relationships por1
22655: WHERE por1.object_relationship_id IN
22656: (SELECT por.object_relationship_id
22657: FROM pa_proj_element_versions ppev,
22658: pa_object_relationships por
22654: DELETE FROM pa_object_relationships por1
22655: WHERE por1.object_relationship_id IN
22656: (SELECT por.object_relationship_id
22657: FROM pa_proj_element_versions ppev,
22658: pa_object_relationships por
22659: WHERE relationship_type ='D'
22660: AND ppev.parent_structure_version_id = p_element_version_id
22661: AND ppev.object_type = 'PA_TASKS'
22662: AND por.object_id_from2 = por.object_id_to2