[Home] [Help]
8: -- Procedure : Create_Deliverable
9: -- Type : PRIVATE
10: -- Purpose : Create_Deliverable Procedure will be called for creation of deliverable
11: -- Note : Call insert_row method of pa_proj_elements, pa_proj_element_versions,
12: -- : pa_proj_element_sch and pa_object_relationships packages
13: -- Assumptions : None
14:
15: -- Parameters Type Required Description and Purpose
16: -- --------------------------- ------ -------- --------------------------------------------------------
137: X_ROW_ID VARCHAR2(18);
138: l_proj_element_id PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE := NULL;
139: l_dlvr_version_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE := NULL;
140: l_new_pev_schedule_id PA_PROJ_ELEM_VER_SCHEDULE.PEV_SCHEDULE_ID%TYPE := NULL;
141: l_new_obj_rel_id PA_OBJECT_RELATIONSHIPS.OBJECT_RELATIONSHIP_ID%TYPE := NULL;
142: l_progress_weight PA_PROJ_ELEMENTS.PROGRESS_WEIGHT%TYPE := NULL; -- 3570283 added
143: l_prog_rollup_method VARCHAR2(30) := NULL;
144:
145: l_dlvr_prg_flag VARCHAR2(1) := 'N'; -- 3570283 added
399: l_debug_level3);
400: END IF;
401:
402: IF l_debug_mode = 'Y' THEN
403: Pa_Debug.WRITE(g_module_name,' PA_OBJECT_RELATIONSHIPS_PKG.Insert_Row Called Structure-To-Deliverable',
404: l_debug_level3);
405: END IF;
406:
407: -- call insert_row of pa_object_relationships package
403: Pa_Debug.WRITE(g_module_name,' PA_OBJECT_RELATIONSHIPS_PKG.Insert_Row Called Structure-To-Deliverable',
404: l_debug_level3);
405: END IF;
406:
407: -- call insert_row of pa_object_relationships package
408: -- it creates relationship from structure_to_deliverable
409:
410: PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW
411: (
406:
407: -- call insert_row of pa_object_relationships package
408: -- it creates relationship from structure_to_deliverable
409:
410: PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW
411: (
412: p_user_id => FND_GLOBAL.USER_ID
413: ,p_object_type_from => 'PA_STRUCTURES'
414: ,p_object_id_from1 => p_parent_struct_ver_id
436: RAISE FND_API.G_EXC_ERROR;
437: END IF;
438:
439: IF l_debug_mode = 'Y' THEN
440: Pa_Debug.WRITE(g_module_name,' Out of PA_OBJECT_RELATIONSHIPS_PKG.Insert_Row Structure-To-Deliverable str vers ['
441: ||p_parent_struct_ver_id||']str ['||p_parent_struct_ver_id||']' , l_debug_level3);
442: END IF;
443:
444: IF (P_TASK_ID IS NOT NULL ) THEN
448: l_debug_level3);
449: END IF;
450:
451: -- if task_id is not null , i.e. create_deliverable is called from task page
452: -- call insert_row of pa_object_relationships package
453: -- it creates relationship from task_to_deliverable
454: /*
455: PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW
456: (
451: -- if task_id is not null , i.e. create_deliverable is called from task page
452: -- call insert_row of pa_object_relationships package
453: -- it creates relationship from task_to_deliverable
454: /*
455: PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW
456: (
457: p_user_id => FND_GLOBAL.USER_ID
458: ,p_object_type_from => 'PA_TASKS'
459: ,p_object_id_from1 => p_task_version_id
603: -- Procedure : Update_Deliverable
604: -- Type : PRIVATE
605: -- Purpose : Update_Deliverable Procedure will be called for creation of deliverable
606: -- Note : Call update_row method of pa_proj_elements, pa_proj_element_versions,
607: -- : pa_proj_element_sch and pa_object_relationships packages
608: -- Assumptions : None
609:
610: -- Parameters Type Required Description and Purpose
611: -- --------------------------- ------ -------- --------------------------------------------------------
1813:
1814: END IF;
1815:
1816: --Here we are deleting the TASK TO DELIVERABLE Relationship
1817: DELETE FROM PA_OBJECT_RELATIONSHIPS
1818: WHERE OBJECT_ID_TO2 = p_dlv_element_id
1819: AND object_type_from = 'PA_TASKS' /* Included these 4 additional Clauses for Performance Bug # 3614361*/
1820: AND object_type_to = 'PA_DELIVERABLES'
1821: AND relationship_type = 'A'
1822: AND RELATIONSHIP_SUBTYPE ='TASK_TO_DELIVERABLE';
1823:
1824: -- 3946997 Added code to delete task assingment to deliverable association
1825:
1826: DELETE FROM PA_OBJECT_RELATIONSHIPS
1827: WHERE OBJECT_ID_TO2 = p_dlv_element_id
1828: AND object_type_from = 'PA_ASSIGNMENTS'
1829: AND object_type_to = 'PA_DELIVERABLES'
1830: AND relationship_type = 'A'
1832:
1833: -- 3946997 end
1834:
1835: -- 3749451 added below code to delete structure to deliverable association , when deliverable is deleted
1836: DELETE FROM PA_OBJECT_RELATIONSHIPS
1837: WHERE OBJECT_ID_TO2 = p_dlv_element_id
1838: AND object_type_from = 'PA_STRUCTURES'
1839: AND object_type_to = 'PA_DELIVERABLES'
1840: AND relationship_type = 'S'
1932: -- and delete the action's association with the deliverable
1933:
1934: --Included on 12-Feb-2004 by avaithia <
1935:
1936: DELETE FROM PA_OBJECT_RELATIONSHIPS
1937: WHERE OBJECT_ID_FROM2 = p_dlv_element_id
1938: RETURNING object_id_to2,object_id_to1
1939: BULK COLLECT INTO l_action_element_id_tbl,l_action_version_id_tbl;
1940:
2083: ,p_task_element_id IN pa_proj_elements.proj_element_id%TYPE
2084: ,p_task_version_id IN pa_proj_element_versions.element_version_id%TYPE
2085: ,p_dlv_element_id IN pa_proj_elements.proj_element_id%TYPE
2086: ,p_dlv_version_id IN pa_proj_element_versions.element_version_id%TYPE
2087: ,p_object_relationship_id IN pa_object_relationships.object_relationship_id%TYPE
2088: ,p_obj_rec_ver_number IN pa_object_relationships.record_version_number%TYPE
2089: ,p_project_id IN pa_projects_all.project_id%TYPE
2090: ,p_calling_context IN VARCHAR2 -- Bug 3555460
2091: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2084: ,p_task_version_id IN pa_proj_element_versions.element_version_id%TYPE
2085: ,p_dlv_element_id IN pa_proj_elements.proj_element_id%TYPE
2086: ,p_dlv_version_id IN pa_proj_element_versions.element_version_id%TYPE
2087: ,p_object_relationship_id IN pa_object_relationships.object_relationship_id%TYPE
2088: ,p_obj_rec_ver_number IN pa_object_relationships.record_version_number%TYPE
2089: ,p_project_id IN pa_projects_all.project_id%TYPE
2090: ,p_calling_context IN VARCHAR2 -- Bug 3555460
2091: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2092: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2128:
2129: IF p_calling_context <> 'TASK_ASSIGNMENT' THEN
2130:
2131: -- Not doing any explicit locking as it is taken care
2132: -- by PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW
2133:
2134: -- Call VALIDATE_DISASSOCIATION_ALLOWED to check whether
2135: -- disassociation is allowed or not. This API will also
2136: -- populate the error stack with proper error message if
2186: END IF;
2187:
2188: -- 3888280 end
2189:
2190: PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW
2191: ( p_object_relationship_id => p_object_relationship_id
2192: ,p_object_type_from => NULL
2193: ,p_object_id_from1 => NULL
2194: ,p_object_id_from2 => NULL
2561: RAISE Invalid_Arg_Exc_Dlv ;
2562: END IF ;
2563:
2564: FORALL i in p_element_id_tbl.FIRST..p_element_id_tbl.LAST
2565: INSERT INTO PA_OBJECT_RELATIONSHIPS (
2566: object_relationship_id
2567: ,object_type_from
2568: ,object_id_from1
2569: ,object_type_to
2580: ,last_update_login
2581: )
2582: VALUES
2583: (
2584: pa_object_relationships_s.nextval
2585: ,'PA_TASKS'
2586: ,decode(p_task_or_dlv,'PA_TASKS',p_task_or_dlv_ver_id,p_version_id_tbl(i))
2587: ,'PA_DELIVERABLES'
2588: ,decode(p_task_or_dlv,'PA_TASKS',p_version_id_tbl(i),p_task_or_dlv_ver_id)
2698: -- 3651542 added below cursor and local variables
2699:
2700: CURSOR c_dlvr_task_assgnt_asscn IS
2701: SELECT obj.object_relationship_id
2702: FROM PA_OBJECT_RELATIONSHIPS obj
2703: WHERE OBJ.object_id_from2 = p_task_element_id
2704: AND OBJ.object_id_to2 = p_dlv_element_id
2705: AND OBJ.object_type_to = 'PA_DELIVERABLES'
2706: AND OBJ.object_type_from = 'PA_ASSIGNMENTS'
2706: AND OBJ.object_type_from = 'PA_ASSIGNMENTS'
2707: AND OBJ.relationship_type = 'A'
2708: AND OBJ.relationship_subtype = 'ASSIGNMENT_TO_DELIVERABLE';
2709:
2710: l_dummy pa_object_relationships.object_relationship_Id%TYPE;
2711:
2712: is_asscn_exists VARCHAR2(1) := 'N';
2713:
2714: BEGIN
2764: RAISE Invalid_Arg_Exc_Dlv ;
2765: END IF ;
2766:
2767: IF p_debug_mode = 'Y' THEN
2768: pa_debug.g_err_stage:= 'Populating PA_OBJECT_RELATIONSHIPS';
2769: pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2770: END IF;
2771:
2772: -- 3651542 added below call to check whether task assignment to deliverable relation ship is
2819: -- IF (p_calling_module = 'SELF_SERVICE') OR ( p_calling_module = 'AMG' AND is_asscn_exists = 'N' ) --OR
2820: -- ( p_calling_module = 'AMG' AND nvl(PA_DELIVERABLE_UTILS.GET_DLVR_TASK_ASSCN_ID(p_dlv_element_id,p_task_element_id),-99) = -99) -- 3749487 changed from 'X' to -99
2821: THEN
2822: -- Populate object relationship table
2823: INSERT INTO PA_OBJECT_RELATIONSHIPS (
2824: object_relationship_id
2825: ,object_type_from
2826: ,object_id_from1
2827: ,object_type_to
2838: ,last_update_login
2839: )
2840: VALUES
2841: (
2842: pa_object_relationships_s.nextval
2843: ,decode(p_calling_context,'PA_TASKS','PA_TASKS','PA_ASSIGNMENTS')
2844: ,p_task_version_id
2845: ,'PA_DELIVERABLES'
2846: ,p_dlv_version_id
3022:
3023: IF nvl(l_dlv_version_tbl.LAST,0)>0 THEN
3024:
3025: FORALL j IN l_dlv_proj_elt_tbl.FIRST..l_dlv_proj_elt_tbl.LAST
3026: DELETE FROM PA_OBJECT_RELATIONSHIPS
3027: WHERE object_id_to2 = l_dlv_proj_elt_tbl(j)
3028: and object_type_to = 'PA_DELIVERABLES'; -- Added for perf bug# 3964586;
3029:
3030: -- 3986132 Added below code to delete DELIVERABLE to ACTION association from
3027: WHERE object_id_to2 = l_dlv_proj_elt_tbl(j)
3028: and object_type_to = 'PA_DELIVERABLES'; -- Added for perf bug# 3964586;
3029:
3030: -- 3986132 Added below code to delete DELIVERABLE to ACTION association from
3031: -- pa_object_relationships table
3032:
3033: FORALL j IN l_dlv_version_tbl.FIRST..l_dlv_version_tbl.LAST
3034: DELETE FROM PA_OBJECT_RELATIONSHIPS
3035: WHERE object_id_from1 = l_dlv_version_tbl(j)
3030: -- 3986132 Added below code to delete DELIVERABLE to ACTION association from
3031: -- pa_object_relationships table
3032:
3033: FORALL j IN l_dlv_version_tbl.FIRST..l_dlv_version_tbl.LAST
3034: DELETE FROM PA_OBJECT_RELATIONSHIPS
3035: WHERE object_id_from1 = l_dlv_version_tbl(j)
3036: and object_type_from = 'PA_DELIVERABLES'
3037: and object_type_to = 'PA_ACTIONS';
3038:
3205: SELECT 'Y'
3206: FROM DUAL
3207: WHERE EXISTS (SELECT 'Y'
3208: FROM pa_proj_element_versions pev
3209: ,pa_object_relationships obj1
3210: ,pa_object_relationships obj2
3211: WHERE obj1.object_id_from2 = p_task_element_id
3212: AND obj1.relationship_type = 'A'
3213: AND obj1.relationship_subtype = 'TASK_TO_DELIVERABLE'
3206: FROM DUAL
3207: WHERE EXISTS (SELECT 'Y'
3208: FROM pa_proj_element_versions pev
3209: ,pa_object_relationships obj1
3210: ,pa_object_relationships obj2
3211: WHERE obj1.object_id_from2 = p_task_element_id
3212: AND obj1.relationship_type = 'A'
3213: AND obj1.relationship_subtype = 'TASK_TO_DELIVERABLE'
3214: AND obj1.object_type_from = 'PA_TASKS'
3237: SELECT 'Y'
3238: FROM DUAL
3239: WHERE EXISTS (SELECT 'Y'
3240: FROM pa_proj_elements ppe
3241: ,pa_object_relationships obj1
3242: ,pa_task_types ptt
3243: WHERE obj1.object_id_from2 = p_task_element_id
3244: AND obj1.relationship_type = 'A'
3245: AND obj1.relationship_subtype = 'TASK_TO_DELIVERABLE'
3311: -- if p_delete_or_validate is D , do only deletion ( ver enabled case, publishing flow )
3312:
3313: IF p_delete_or_validate IN ('B','D') THEN
3314:
3315: DELETE FROM pa_object_relationships
3316: WHERE object_id_from2 = p_task_element_id
3317: AND object_type_from = 'PA_TASKS'
3318: AND object_type_to = 'PA_DELIVERABLES'
3319: AND relationship_type = 'A'
3320: AND relationship_subtype = 'TASK_TO_DELIVERABLE' ;
3321:
3322: END IF;
3323: ELSE
3324: DELETE FROM pa_object_relationships
3325: WHERE object_id_from2 = p_task_element_id
3326: AND object_type_from = 'PA_ASSIGNMENTS'
3327: AND object_type_to = 'PA_DELIVERABLES'
3328: AND relationship_type = 'A'
3436: from dual
3437: WHERE EXISTS(
3438: SELECT 'Y'
3439: FROM PA_PROJ_ELEMENT_VERSIONS pev1,
3440: PA_OBJECT_RELATIONSHIPS obj1,
3441: PA_PROJ_ELEMENT_VERSIONS pev,
3442: PA_OBJECT_RELATIONSHIPS obj2
3443: WHERE obj1.object_id_from2 = pev1.proj_element_id
3444: AND pev1.project_id = c_project_id
3438: SELECT 'Y'
3439: FROM PA_PROJ_ELEMENT_VERSIONS pev1,
3440: PA_OBJECT_RELATIONSHIPS obj1,
3441: PA_PROJ_ELEMENT_VERSIONS pev,
3442: PA_OBJECT_RELATIONSHIPS obj2
3443: WHERE obj1.object_id_from2 = pev1.proj_element_id
3444: AND pev1.project_id = c_project_id
3445: AND pev1.object_type = 'PA_TASKS'
3446: AND obj1.relationship_type = 'A'
3471: from dual
3472: WHERE EXISTS(
3473: SELECT 'Y'
3474: FROM pa_proj_elements ppe
3475: ,pa_object_relationships obj1
3476: ,pa_task_types ptt
3477: WHERE
3478: ppe.project_id = c_project_id
3479: and ppe.object_type = 'PA_TASKS'
3533: pa_debug.g_err_stage:= 'Delete the associations';
3534: pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
3535: END IF ;
3536:
3537: DELETE FROM pa_object_relationships
3538: WHERE object_type_from = 'PA_TASKS'
3539: AND object_type_to = 'PA_DELIVERABLES'
3540: AND relationship_type = 'A'
3541: AND relationship_subtype = 'TASK_TO_DELIVERABLE'
4423: ,'PA_PROJECTS'
4424: ) ;
4425:
4426: IF l_debug_mode = 'Y' THEN
4427: pa_debug.g_err_stage:= 'Populating PA_OBJECT_RELATIONSHIPS ';
4428: pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
4429: END IF;
4430:
4431:
4430:
4431:
4432: -- populate the object relationships table for STRUCTURE_TO_DELIVERABLE
4433: FORALL i in l_proj_element_id_tbl.FIRST..l_proj_element_id_tbl.LAST
4434: INSERT INTO PA_OBJECT_RELATIONSHIPS(
4435: object_relationship_id
4436: ,object_type_from
4437: ,object_id_from1
4438: ,object_type_to
4449: ,last_update_login
4450: )
4451: VALUES
4452: (
4453: pa_object_relationships_s.nextval
4454: ,'PA_STRUCTURES'
4455: ,l_parent_structure_version_id
4456: ,'PA_DELIVERABLES'
4457: ,l_target_element_ver_id_tbl(i)
4469:
4470:
4471: IF nvl(l_proj_element_id_tbl.LAST,0)>0 THEN
4472: IF (nvl(p_association_flag,'N') = 'Y' AND p_calling_context = 'COPY_PROJECT' ) THEN
4473: INSERT INTO PA_OBJECT_RELATIONSHIPS(
4474: object_relationship_id
4475: ,object_type_from
4476: ,object_id_from1
4477: ,object_type_to
4487: ,record_version_number
4488: ,last_update_login
4489: )
4490: SELECT
4491: pa_object_relationships_s.nextval
4492: ,'PA_TASKS'
4493: ,null
4494: ,'PA_DELIVERABLES'
4495: ,dlv2.element_version_id
4505: ,fnd_global.login_id
4506: FROM pa_proj_elements tsk1
4507: ,pa_proj_elements dlv1
4508: ,pa_proj_element_versions dlv2
4509: ,pa_object_relationships obj
4510: WHERE dlv1.project_id = p_target_project_id
4511: AND dlv1.object_type = 'PA_DELIVERABLES'
4512: AND dlv1.attribute15 = obj.object_id_to2
4513: AND obj.relationship_type = 'A'
4524: FOR i IN l_proj_element_id_tbl.FIRST..l_proj_element_id_tbl.LAST LOOP
4525:
4526: IF (nvl(p_association_flag,'N') = 'Y' AND p_calling_context = 'COPY_DELIVERABLES' ) THEN
4527:
4528: INSERT INTO PA_OBJECT_RELATIONSHIPS(
4529: object_relationship_id
4530: ,object_type_from
4531: ,object_id_from1
4532: ,object_type_to
4542: ,record_version_number
4543: ,last_update_login
4544: )
4545: SELECT
4546: pa_object_relationships_s.nextval
4547: ,'PA_TASKS'
4548: ,obj.object_id_from1
4549: ,'PA_DELIVERABLES'
4550: ,l_target_element_ver_id_tbl(i)
4557: ,l_proj_element_id_tbl(i)
4558: ,'TASK_TO_DELIVERABLE'
4559: ,1
4560: ,fnd_global.login_id
4561: FROM pa_object_relationships obj
4562: WHERE obj.object_id_to2 = p_dlv_element_id_tbl(i)
4563: AND obj.object_type_to = 'PA_DELIVERABLES'
4564: AND obj.object_type_from = 'PA_TASKS'
4565: AND obj.relationship_type = 'A'