[Home] [Help]
1: PACKAGE BODY PA_PROJ_ELEMENTS_UTILS AS
2: /* $Header: PATSK1UB.pls 120.15.12010000.3 2008/11/28 11:09:39 vgovvala ship $ */
3:
4: Invalid_Arg_Exc_WP Exception ;
5: g_module_name VARCHAR2(100) := 'pa.plsql.pa_proj_elements_utils';
1: PACKAGE BODY PA_PROJ_ELEMENTS_UTILS AS
2: /* $Header: PATSK1UB.pls 120.15.12010000.3 2008/11/28 11:09:39 vgovvala ship $ */
3:
4: Invalid_Arg_Exc_WP Exception ;
5: g_module_name VARCHAR2(100) := 'pa.plsql.pa_proj_elements_utils';
6:
7: -- Added for Bug 6156686
8: TYPE l_lookup_cache_tbl_typ IS TABLE OF VARCHAR2(80)
9: INDEX BY VARCHAR2(70);
17:
18: PROCEDURE SetGlobalStrucVerId ( p_structure_version_id IN NUMBER )
19: IS
20: BEGIN
21: PA_PROJ_ELEMENTS_UTILS.g_Struc_Ver_Id := p_structure_version_id;
22: END SetGlobalStrucVerId;
23:
24: FUNCTION GetGlobalStrucVerId RETURN NUMBER
25: IS
23:
24: FUNCTION GetGlobalStrucVerId RETURN NUMBER
25: IS
26: BEGIN
27: RETURN ( PA_PROJ_ELEMENTS_UTILS.g_Struc_Ver_Id );
28: END GetGlobalStrucVerId;
29:
30: --This function checks if a WORKPLAN (split or shared) task
31: --may be updtaed by the logged in user.
221: CURSOR cur_elem_ver
222: IS
223: SELECT ppe.name, ppe.element_number, ppev.parent_structure_version_id, ppe.project_id
224: FROM pa_proj_element_versions ppev,
225: pa_proj_elements ppe
226: WHERE ppe.proj_element_id = ppev.proj_element_id
227: AND ppev.element_version_id = p_element_version_id
228: AND ppev.object_type = 'PA_TASKS';
229:
233: CURSOR cur_elem_ver_stru( p_version_id NUMBER )
234: IS
235: SELECT ppevs.version_number, ppevs.name, ppe.element_number, ppe.name, ppe.proj_element_id, ppe.project_id
236: FROM pa_proj_elem_ver_structure ppevs,
237: pa_proj_elements ppe
238: WHERE ppevs.element_version_id = p_version_id
239: AND ppe.proj_element_id = ppevs.proj_element_id
240: AND ppevs.project_id = (select project_id
241: from pa_proj_element_versions
326: l_debug_level2 CONSTANT NUMBER := 2;
327: l_debug_level3 CONSTANT NUMBER := 3;
328: l_debug_level4 CONSTANT NUMBER := 4;
329: l_debug_level5 CONSTANT NUMBER := 5;
330: l_module_name VARCHAR2(100) := 'pa.plsql.PA_PROJ_ELEMENTS_UTILS';
331:
332: l_date1 DATE ;
333: l_pev_structure_id1 pa_proj_elem_ver_structure.pev_structure_id%TYPE ;
334: l_element_version_id1 pa_proj_elem_ver_structure.element_version_id%TYPE ;
403: --cursor to select the structure version id for a workplan structure
404: CURSOR cur_elem_ver_stru3
405: IS
406: SELECT ppe.proj_element_id
407: FROM pa_proj_elements ppe,
408: pa_proj_structure_types ppst,
409: pa_structure_types pst
410: WHERE ppe.project_id = p_project_id
411: AND ppe.object_type = 'PA_STRUCTURES'
594: CLOSE cur_elem_ver_stru2;
595: END IF;
596:
597: FND_MSG_PUB.add_exc_msg
598: ( p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS'
599: ,p_procedure_name => 'Get_Last_Upd_Working_Wp_Ver'
600: ,p_error_text => x_msg_data);
601:
602: IF l_debug_mode = 'Y' THEN
613: ) RETURN VARCHAR2 IS
614: CURSOR cur_obj_rel
615: IS
616: SELECT 'x'
617: FROM pa_object_relationships por, pa_proj_elements ppe, pa_proj_element_versions ppev
618: WHERE object_id_from1 = p_structure_version_id
619: AND relationship_type = 'S'
620: AND por.object_id_to1 = ppev.element_version_id
621: AND ppe.proj_element_id = ppev.proj_element_id
703: ,p_object_type IN VARCHAR2 := 'PA_TASKS'
704: ) return VARCHAR2
705: IS
706: cursor c1 is
707: select 1 from pa_proj_elements
708: where project_id = p_project_id
709: and object_type = p_object_type
710: and element_number = p_element_number
711: and PARENT_STRUCTURE_ID = p_structure_id
956: return;
957: END IF;
958: -- anlee end of changes
959:
960: IF PA_PROJ_ELEMENTS_UTILS.structure_type(
961: p_structure_version_id => p_parent_structure_ver_id
962: ,p_task_version_id => null
963: ,p_structure_type => 'WORKPLAN'
964: ) = 'Y'
1026: END IF;
1027: */
1028: END IF;
1029:
1030: IF PA_PROJ_ELEMENTS_UTILS.structure_type(
1031: p_structure_version_id => p_parent_structure_ver_id
1032: ,p_task_version_id => null
1033: ,p_structure_type => 'FINANCIAL'
1034: ) = 'Y' THEN
1095: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1096:
1097: -- 4537865
1098: x_error_message_code := SQLCODE;
1099: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
1100: p_procedure_name => 'Check_Delete_task_Ver_Ok',
1101: p_error_text => SUBSTRB(SQLERRM,1,240)); -- 4537865
1102: RAISE;
1103: END Check_Delete_task_Ver_Ok;
1180: WHERE EXISTS
1181: (SELECT NULL
1182: FROM pa_object_relationships por,
1183: pa_proj_element_versions pev,
1184: pa_proj_elements pe
1185: WHERE por.object_id_from1 = p_element_version_id
1186: AND por.object_type_from ='PA_TASKS'
1187: AND por.relationship_type = 'S'
1188: AND por.object_id_to1=pev.element_version_id
1291: EXCEPTION
1292: WHEN OTHERS THEN
1293: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1294: x_error_message_code := SQLCODE ;
1295: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
1296: p_procedure_name => 'Check_Date_range',
1297: p_error_text => SUBSTRB(SQLERRM,1,240));
1298: RAISE;
1299: end Check_Date_range;
1364: END IF;
1365: ELSE
1366: select element_version_id
1367: into x_task_version_id
1368: from pa_proj_elements ppe, pa_proj_element_versions ppev
1369: where ppe.proj_element_id = ppev.proj_element_id
1370: AND ppe.name = p_task_name
1371: AND ppev.parent_structure_version_id = p_structure_version_id;
1372: null;
2120: FUNCTION LINK_FLAG( p_element_id NUMBER ) RETURN VARCHAR2 IS
2121: CURSOR cur_proj_elements
2122: IS
2123: SELECT link_task_flag
2124: FROM pa_proj_elements
2125: WHERE proj_element_id = p_element_id;
2126:
2127: l_link_task_flag VARCHAR2(1) :='N'; --bug 4180390
2128: BEGIN
2536:
2537: /* Bug2680486 -- Performance changes -- Commented the following cursor query and restructured it. */
2538: /* cursor c1 IS
2539: select 'Y'
2540: from PA_PROJ_ELEMENTS
2541: where type_id = p_task_type_id;
2542: */
2543:
2544: cursor c1 IS
2545: select 'Y'
2546: from dual
2547: where exists (
2548: select 'xyz'
2549: from PA_PROJ_ELEMENTS
2550: where type_id = p_task_type_id
2551: AND project_id > -1
2552: AND object_type = 'PA_TASKS'
2553: );
2621: IS
2622: --bug 2863836: modified cursor to refer to system status
2623: cursor c1 is
2624: select b.project_system_status_code
2625: from pa_proj_elements a, pa_project_statuses b
2626: where a.proj_element_id = p_task_id
2627: and a.status_code = b.project_status_code
2628: and b.status_type = 'TASK';
2629: l_dummy pa_proj_elements.status_code%TYPE;
2625: from pa_proj_elements a, pa_project_statuses b
2626: where a.proj_element_id = p_task_id
2627: and a.status_code = b.project_status_code
2628: and b.status_type = 'TASK';
2629: l_dummy pa_proj_elements.status_code%TYPE;
2630: l_retval VARCHAR2(1);
2631: BEGIN
2632: open c1;
2633: FETCH c1 into l_dummy;
2685: OPEN c1 (p_task_id);
2686: FETCH c1 INTO v_c1;
2687: CLOSE c1;
2688:
2689: l_element_version_id := PA_PROJ_ELEMENTS_UTILS.GET_LATEST_FIN_PUB_TASK_VER_ID
2690: (p_project_id => v_c1.project_id,
2691: p_task_id => p_task_id);
2692:
2693: IF l_element_version_id IS NULL THEN
2731:
2732: l_task_ver_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ; -- 4201927 for performance issue
2733: BEGIN
2734:
2735: PA_PROJ_ELEMENTS_UTILS.CHECK_DELETE_TASK_VER_OK(
2736: p_project_id => p_project_id
2737: ,p_task_version_id => p_task_version_id
2738: ,p_parent_structure_ver_id => p_parent_structure_ver_id
2739: ,x_return_status => x_return_status
2749: OPEN c1;
2750: LOOP
2751: FETCH c1 into l_task_ver_id;
2752: EXIT WHEN c1%NOTFOUND;
2753: PA_PROJ_ELEMENTS_UTILS.Check_delete_task_ver_ok(
2754: p_project_id => p_project_id
2755: ,p_task_version_id => l_task_ver_id
2756: ,p_parent_structure_ver_id => p_parent_structure_ver_id
2757: ,x_return_status => x_return_status
2772:
2773: IF nvl(l_task_ver_id_tbl.LAST,0) > 0 THEN
2774: FOR i in reverse l_task_ver_id_tbl.FIRST..l_task_ver_id_tbl.LAST LOOP
2775:
2776: PA_PROJ_ELEMENTS_UTILS.Check_delete_task_ver_ok(
2777: p_project_id => p_project_id
2778: ,p_task_version_id => l_task_ver_id_tbl(i)
2779: ,p_parent_structure_ver_id => p_parent_structure_ver_id
2780: ,x_return_status => x_return_status
2798: WHEN OTHERS THEN
2799: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2800: -- 4537865
2801: x_error_message_code := SQLCODE ;
2802: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
2803: p_procedure_name => 'Check_Del_all_task_Ver_Ok
2804: ');
2805: RAISE;
2806: END Check_Del_all_task_Ver_Ok;
2813: IS
2814: CURSOR get_status IS
2815: select ppe.project_id, ppe.proj_element_id
2816: from pa_project_statuses pps,
2817: pa_proj_elements ppe,
2818: pa_proj_element_versions ppev
2819: where ppev.element_version_id = p_parent_task_ver_id
2820: and ppe.project_id = ppev.project_id
2821: and ppe.proj_element_id = ppev.proj_element_id
2896:
2897: --if financial, check if ok to create subtask.
2898: --Bug 5988335 Adding condition for partial share project to skip financial validation
2899: --when a new subtask is getting created in workplan.
2900: If (PA_PROJ_ELEMENTS_UTILS.CHECK_IS_FINANCIAL_TASK(l_proj_element_id) = 'Y') AND
2901: (PA_PROJ_TASK_STRUC_PUB.GET_SHARE_TYPE(l_project_id) <> 'SHARE_PARTIAL') --Bug 5988335
2902: THEN
2903: PA_TASK_UTILS.CHECK_CREATE_SUBTASK_OK(x_task_id => l_proj_element_id,
2904: x_err_code => l_err_code,
2968: EXCEPTION
2969: WHEN OTHERS THEN
2970: x_return_status := 'N';
2971: x_error_message_code := SQLCODE ;
2972: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
2973: p_procedure_name => 'Check_create_subtask_ok',
2974: p_error_text => SUBSTRB(SQLERRM,1,240));
2975: RAISE ;
2976: END Check_create_subtask_ok;
3026: VARCHAR2
3027: IS
3028: CURSOR l_element_has_phase_csr IS
3029: SELECT 'Y'
3030: FROM pa_proj_elements
3031: WHERE proj_element_id = p_proj_element_id
3032: AND phase_version_id is not null;
3033:
3034: l_has_phase VARCHAR2(1) := 'N';
3068: VARCHAR2
3069: IS
3070: CURSOR l_exist_non_top_task_csr IS
3071: select 'N'
3072: from pa_proj_elements ppe,
3073: pa_proj_element_versions ppev
3074: where ppe.proj_element_id = p_proj_element_id
3075: and ppe.proj_element_id = ppev.proj_element_id
3076: and nvl(ppev.wbs_level, 0) <> 1;
3115:
3116: /* Bug 2680486 -- Performance changes -- Added join of project_id in the following cursor*/
3117: CURSOR l_phase_in_use_csr IS
3118: select 'Y'
3119: from pa_proj_elements ppe,
3120: pa_proj_elements ppe2
3121: where ppe.proj_element_id = p_task_id
3122: and ppe.parent_structure_id = ppe2.parent_structure_id
3123: and ppe2.phase_version_id = p_phase_version_id
3116: /* Bug 2680486 -- Performance changes -- Added join of project_id in the following cursor*/
3117: CURSOR l_phase_in_use_csr IS
3118: select 'Y'
3119: from pa_proj_elements ppe,
3120: pa_proj_elements ppe2
3121: where ppe.proj_element_id = p_task_id
3122: and ppe.parent_structure_id = ppe2.parent_structure_id
3123: and ppe2.phase_version_id = p_phase_version_id
3124: and ppe2.proj_element_id <> p_task_id
3151: x_error_message_code OUT NOCOPY VARCHAR2) -- 4537865
3152: IS
3153: CURSOR c1 is
3154: select 1 from
3155: pa_proj_elements a,
3156: pa_proj_element_versions b,
3157: pa_proj_elem_ver_structure c
3158: where a.proj_element_id = p_task_id
3159: and a.project_id = p_project_id
3208: IS
3209: CURSOR get_status IS
3210: select '1'
3211: from pa_project_statuses pps,
3212: pa_proj_elements ppe,
3213: pa_proj_element_versions ppev
3214: where ppev.element_version_id = p_task_ver_id
3215: and ppe.project_id = ppev.project_id
3216: and ppe.proj_element_id = ppev.proj_element_id
3232: EXCEPTION
3233: WHEN OTHERS THEN
3234: x_return_status := 'N' ;
3235: x_error_message_code := SQLCODE ;
3236: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3237: p_procedure_name => 'check_move_task_ok',
3238: p_error_text => SUBSTRB(SQLERRM,1,240));
3239: RAISE ;
3240: END check_move_task_ok;
3362: -- 4537865
3363: -- Not resetting x_error_message_code as at this point it would have been already populated.
3364: WHEN OTHERS THEN
3365: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3366: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3367: p_procedure_name => 'Check_chg_stat_cancel_ok');
3368:
3369: -- 4537865
3370: x_error_message_code := SQLCODE ;
3380: END IF;
3381:
3382: SELECT name
3383: INTO l_ret
3384: FROM pa_proj_elements
3385: WHERE proj_element_id = p_proj_element_id;
3386:
3387: RETURN l_ret;
3388: EXCEPTION
3386:
3387: RETURN l_ret;
3388: EXCEPTION
3389: WHEN OTHERS THEN
3390: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3391: p_procedure_name => 'get_element_name');
3392: RAISE;
3393: END get_element_name;
3394:
3401: END IF;
3402:
3403: SELECT element_number
3404: INTO l_ret
3405: FROM pa_proj_elements
3406: WHERE proj_element_id = p_proj_element_id;
3407:
3408: RETURN l_ret;
3409: EXCEPTION
3407:
3408: RETURN l_ret;
3409: EXCEPTION
3410: WHEN OTHERS THEN
3411: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3412: p_procedure_name => 'get_element_number');
3413: RAISE;
3414: END get_element_number;
3415:
3422: END IF;
3423:
3424: SELECT name||'('||element_number||')'
3425: INTO l_ret
3426: FROM pa_proj_elements
3427: WHERE proj_element_id = p_proj_element_id;
3428:
3429: RETURN l_ret;
3430: EXCEPTION
3428:
3429: RETURN l_ret;
3430: EXCEPTION
3431: WHEN OTHERS THEN
3432: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3433: p_procedure_name => 'get_element_name_number');
3434: RAISE;
3435: END get_element_name_number;
3436:
3556:
3557: PROCEDURE GET_STRUCTURE_INFO
3558: ( p_project_id IN pa_projects_all.project_id%TYPE
3559: ,p_structure_type IN pa_structure_types.structure_type_class_code%TYPE
3560: ,p_structure_id IN pa_proj_elements.proj_element_id%TYPE
3561: ,p_is_wp_separate_from_fn IN VARCHAR2
3562: ,p_is_wp_versioning_enabled IN VARCHAR2
3563: ,x_structure_version_id OUT NOCOPY pa_proj_element_versions.element_version_id%TYPE -- 4537865
3564: ,x_task_unpub_ver_status_code OUT NOCOPY pa_proj_element_versions.task_unpub_ver_status_code%TYPE -- 4537865
3778: x_structure_version_id := NULL;
3779: -- 4537865 : End
3780:
3781: FND_MSG_PUB.add_exc_msg
3782: ( p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS'
3783: ,p_procedure_name => 'GET_STRUCTURE_INFO'
3784: ,p_error_text => x_msg_data);
3785:
3786: IF l_debug_mode = 'Y' THEN
3798: -- Purpose : This procedure will check whether the task has transaction or not.This API will be
3799: -- : called from Set_Financial_task_API.
3800: -- Note : Check whether it is a financial task or workplan task.
3801: -- : Fetch the parent_structure_version_id for the passed proj_element_Id from
3802: -- : PA_PROJ_ELEMENT_VERSIONS and pass to API PA_PROJ_ELEMENTS_UTILS.structure_type
3803:
3804: -- Assumptions : Only called for Financial task
3805:
3806: -- Parameters Type Required Description and Purpose
3865: PA_DEBUG.set_curr_function( p_function =>'CHECK_TASK_HAS_TRANSACTION' , p_debug_mode => l_debug_mode );
3866: END IF;
3867:
3868: IF l_debug_mode = 'Y' THEN
3869: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : Printing Input parameters';
3870: Pa_Debug.WRITE(g_module_name , Pa_Debug.g_err_stage , l_debug_level3);
3871: Pa_Debug.WRITE(g_module_name , 'p_task_id'||':'||p_task_id , l_debug_level3);
3872: END IF;
3873:
3872: END IF;
3873:
3874:
3875: IF l_debug_mode = 'Y' THEN
3876: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : Validating Input Paramater';
3877: Pa_Debug.WRITE(g_module_name , Pa_Debug.g_err_stage , l_debug_level3);
3878: END IF;
3879:
3880: -- Validating for Input parameter
3881: IF ( p_task_id IS NOT NULL ) THEN
3882:
3883: --Commented to be reviewed once more with set financial task
3884: --IF l_debug_mode = 'Y' THEN
3885: -- Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : Checking for financial type';
3886: -- Pa_Debug.WRITE ( g_module_name , Pa_Debug.g_err_stage , l_debug_level3 );
3887: --END IF;
3888:
3889: --Select the parent structure version id for the passed task id
3892: --CLOSE c_get_parent_str_ver_id;
3893:
3894: --check if the structure type is financial
3895: --IF (
3896: -- PA_PROJ_ELEMENTS_UTILS.structure_type
3897: -- ( p_structure_version_id => l_prnt_str_ver_id
3898: -- , p_task_version_id => null
3899: -- , p_structure_type => 'FINANCIAL'
3900: -- ) = 'Y'
3903:
3904: -- Commenting code for 4903460 and replacing this wth new code
3905: --Check if task has expenditure item
3906: /* IF l_debug_mode = 'Y' THEN
3907: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check expenditure item for '|| p_task_id;
3908: Pa_Debug.WRITE(g_module_name , Pa_Debug.g_err_stage , l_debug_level3);
3909: END IF;
3910: l_status_code :=
3911: pa_proj_tsk_utils.check_exp_item_exists(null, p_task_id);
3919: END IF;
3920:
3921: --Check if task has purchase order distribution
3922: IF l_debug_mode = 'Y' THEN
3923: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check purchase order for '|| p_task_id;
3924: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
3925: l_debug_level3);
3926: END IF;
3927: l_status_code :=
3936: END IF;
3937:
3938: -- Check if task has purchase order requisition
3939: IF l_debug_mode = 'Y' THEN
3940: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check purchase order requisition for '|| p_task_id;
3941: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
3942: l_debug_level3);
3943: END IF;
3944:
3954: END IF;
3955:
3956: -- Check if task has supplier invoices
3957: IF l_debug_mode = 'Y' THEN
3958: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check supplier invoice for '|| p_task_id;
3959: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
3960: l_debug_level3);
3961: END IF;
3962:
3972: END IF;
3973:
3974: -- Check if task has supplier invoice distribution
3975: IF l_debug_mode = 'Y' THEN
3976: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check supplier inv distribution for '|| p_task_id;
3977: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
3978: l_debug_level3);
3979: END IF;
3980:
3990: END IF;
3991:
3992: -- Check if task has commitment transaction
3993: IF l_debug_mode = 'Y' THEN
3994: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check commitment transaction for '|| p_task_id;
3995: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
3996: l_debug_level3);
3997: END IF;
3998: l_status_code :=
4007: END IF;
4008:
4009: -- Check if task has compensation rule set
4010: IF l_debug_mode = 'Y' THEN
4011: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check compensation rule set for '|| p_task_id;
4012: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4013: l_debug_level3);
4014: END IF;
4015:
4025: END IF; */
4026: -- End of Commenting for 4903460
4027: -- Check if task is in use in an external system
4028: IF l_debug_mode = 'Y' THEN
4029: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check for task used in external system for'|| p_task_id;
4030: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4031: l_debug_level3);
4032: END IF;
4033:
4053: END IF;
4054:
4055: -- Check if task is used in allocations
4056: IF l_debug_mode = 'Y' THEN
4057: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check if project allocations uses task '|| p_task_id;
4058: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4059: l_debug_level3);
4060: END IF;
4061:
4070: -- Commenting for Performance fix 4903460
4071: /*
4072: -- Check if task has draft invoices
4073: IF l_debug_mode = 'Y' THEN
4074: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check draft invoice for '|| p_task_id;
4075: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4076: l_debug_level3);
4077: END IF;
4078:
4088: END IF;
4089:
4090: -- Check if task has Project_customers
4091: IF l_debug_mode = 'Y' THEN
4092: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check Project Customers for '|| p_task_id;
4093: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4094: l_debug_level3);
4095: END IF;
4096:
4107: END IF; */
4108: --End of Commenting for Performance fix 4903460
4109:
4110: -- Start of new code for Performance fix 4903460
4111: PA_PROJ_ELEMENTS_UTILS.perform_task_validations
4112: (
4113: p_project_id => p_project_id
4114: ,p_task_id => p_task_id
4115: ,x_error_code => x_error_code
4123:
4124: -- Check if project contract is installed
4125: IF (pa_install.is_product_installed('OKE')) THEN
4126: IF l_debug_mode = 'Y' THEN
4127: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : Check contract association for task '|| p_task_id;
4128: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4129: l_debug_level3);
4130: END IF;
4131:
4153: --end of OTL check.
4154: Is_IEX_Installed := pa_install.is_product_installed('IEX');
4155: If Is_IEX_Installed then
4156: IF l_debug_mode = 'Y' THEN
4157: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check if task '|| p_task_id || ' is charged in iexpense';
4158: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4159: l_debug_level3);
4160: END IF;
4161:
4170: END IF;
4171: END IF;
4172: --BEGIN
4173: IF l_debug_mode = 'Y' THEN
4174: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : PA_FIN_PLAN_UTILS.CHECK_DELETE_TASK_OK'|| p_task_id;
4175: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4176: l_debug_level3);
4177: END IF;
4178:
4524: --If sharing is enabled, then financial and workplan tasks would be common
4525: IF str_sharing_code = 'SHARE_FULL' OR
4526: str_sharing_code = 'SHARE_PARTIAL' THEN
4527: return_flag := 'Y';
4528: ELSIF PA_PROJ_ELEMENTS_UTILS.CHECK_IS_FINANCIAL_TASK(p_proj_element_id) = 'N' THEN
4529: return_flag := 'Y';
4530: ELSE
4531: return_flag := 'N';
4532: END IF;
4587: ,p_task_id IN NUMBER) return NUMBER
4588: is
4589: l_task_version_id NUMBER;
4590: begin
4591: select b.element_version_id into l_task_version_id from pa_proj_elements a, pa_proj_element_versions b
4592: where a.proj_element_id = b.proj_element_id
4593: and a.proj_element_id = p_task_id
4594: and b.parent_structure_version_id = p_structure_version_id;
4595:
4624: --
4625: CURSOR cur_check_deliv (cp_task_version_id number) IS
4626: SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code
4627: FROM pa_proj_element_versions ppev,
4628: pa_proj_elements ppe
4629: WHERE ppe.project_id = ppev.project_id
4630: AND ppe.proj_element_id = ppev.proj_element_id
4631: AND ppev.object_type = 'PA_TASKS'
4632: AND ppe.object_type = 'PA_TASKS'
4669: IS
4670: CURSOR cur_check_deliv_bt (cp_target_task_version_id number) IS
4671: SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
4672: FROM pa_proj_element_versions ppev,
4673: pa_proj_elements ppe
4674: WHERE ppe.project_id = ppev.project_id
4675: AND ppe.proj_element_id = ppev.proj_element_id
4676: AND ppev.object_type = 'PA_TASKS'
4677: AND ppe.object_type = 'PA_TASKS'
4688:
4689: CURSOR cur_check_deliv_tb (cp_task_version_id number) IS
4690: SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
4691: FROM pa_proj_element_versions ppev,
4692: pa_proj_elements ppe
4693: WHERE ppe.project_id = ppev.project_id
4694: AND ppe.proj_element_id = ppev.proj_element_id
4695: AND ppev.object_type = 'PA_TASKS'
4696: AND ppe.object_type = 'PA_TASKS'
4758: /* This cursor goes from leaf node to top of the branch*/
4759: CURSOR check_for_deliv_cur (cp_task_version_id number) IS
4760: SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code
4761: FROM pa_proj_element_versions ppev,
4762: pa_proj_elements ppe
4763: WHERE ppe.project_id = ppev.project_id
4764: AND ppe.proj_element_id = ppev.proj_element_id
4765: AND ppev.object_type = 'PA_TASKS'
4766: AND ppe.object_type = 'PA_TASKS'
4824: IS
4825: CURSOR C
4826: IS
4827: SELECT 'Y'
4828: FROM pa_wf_processes pwp, pa_proj_elements ppe -- Bug #3967939
4829: WHERE pwp.ENTITY_KEY2 = to_char(p_proj_element_id) -- Bug#3619754 : Added to_char
4830: AND ppe.PROJ_ELEMENT_ID = p_proj_element_id -- Bug #3967939
4831: AND pwp.ITEM_TYPE = ppe.WF_ITEM_TYPE; -- Bug #3967939
4832:
4852: CURSOR C
4853: IS
4854: select max(item_key)
4855: from pa_wf_processes wp
4856: , pa_proj_elements pe
4857: where wp.item_type = pe.wf_item_type
4858: and wp.wf_type_code = p_wf_type_code
4859: and to_char(pe.proj_element_id) = wp.entity_key2 --Bug 3619754 Added By avaithia
4860: and to_char(pe.project_id) = wp.entity_key1 --Bug 3619754 Added By avaithia
4878: IS
4879: CURSOR c_item_type
4880: IS
4881: select wf_item_type
4882: from pa_proj_elements pe
4883: where
4884: pe.project_id = p_project_id and pe.proj_element_id =p_proj_element_id;
4885:
4886: l_status VARCHAR2(240) := '';
4921: -- p_proj_element_id NUMBER Y The proj_element_id to be checked
4922: FUNCTION check_fin_or_wp_structure( p_proj_element_id IN NUMBER ) RETURN VARCHAR2 IS
4923: CURSOR cur_chk_fin_or_wp_structure IS
4924: SELECT 'Y'
4925: FROM pa_proj_elements ppe
4926: ,pa_proj_structure_types ppst
4927: ,pa_structure_types pst
4928: WHERE ppe.proj_element_id = p_proj_element_id
4929: AND ppe.object_type = 'PA_STRUCTURES'
4988: IS
4989: CURSOR cur_check_deliv_bt (cp_target_task_version_id number) IS
4990: SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
4991: FROM pa_proj_element_versions ppev,
4992: pa_proj_elements ppe
4993: WHERE ppe.project_id = ppev.project_id
4994: AND ppe.proj_element_id = ppev.proj_element_id
4995: AND ppev.object_type = 'PA_TASKS'
4996: AND ppe.object_type = 'PA_TASKS'
5007:
5008: CURSOR cur_check_deliv_tb (cp_task_version_id number) IS
5009: SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
5010: FROM pa_proj_element_versions ppev,
5011: pa_proj_elements ppe
5012: WHERE ppe.project_id = ppev.project_id
5013: AND ppe.proj_element_id = ppev.proj_element_id
5014: AND ppev.object_type = 'PA_TASKS'
5015: AND ppe.object_type = 'PA_TASKS'
5220: CURSOR cur_pa_proj
5221: IS
5222:
5223: SELECT 'x'
5224: FROM pa_proj_elements ppe, pa_proj_structure_types ppst
5225: WHERE ppe.project_id = p_project_id
5226: AND ppe.object_type = 'PA_STRUCTURES'
5227: AND ppe.proj_element_id = ppst.proj_element_id
5228: AND ppst.structure_type_id = 1; --'WORKPLAN'
5249: ) return VARCHAR2
5250: IS
5251: CURSOR c1 IS
5252: SELECT 'Y'
5253: FROM pa_proj_elements a,
5254: pa_proj_structure_types b,
5255: pa_structure_types c,
5256: pa_proj_structure_types d,
5257: pa_structure_types e
5876: l_dummy number;
5877: cursor child_exist IS
5878: select 1 from dual where exists(
5879: select 1
5880: from pa_object_relationships por, pa_proj_element_versions ppev, pa_proj_elements ppe
5881: where por.object_type_from = 'PA_TASKS'
5882: and por.object_id_from1 = p_task_version_id
5883: and por.relationship_type = 'S'
5884: and por.object_id_to1 = ppev.element_version_id
5903: raise;
5904: END IS_LOWEST_PROJ_TASK;
5905:
5906:
5907: END PA_PROJ_ELEMENTS_UTILS;