[Home] [Help]
1: PACKAGE BODY PA_PROJ_ELEMENTS_UTILS AS
2: /* $Header: PATSK1UB.pls 120.23.12020000.4 2013/05/23 13:05:09 djambhek 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.23.12020000.4 2013/05/23 13:05:09 djambhek 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;
1182: WHERE EXISTS
1183: (SELECT NULL
1184: FROM pa_object_relationships por,
1185: pa_proj_element_versions pev,
1186: pa_proj_elements pe
1187: WHERE por.object_id_from1 = p_element_version_id
1188: AND por.object_type_from ='PA_TASKS'
1189: AND por.relationship_type = 'S'
1190: AND por.object_id_to1=pev.element_version_id
1206: WHERE EXISTS
1207: (SELECT NULL
1208: FROM pa_object_relationships por,
1209: pa_proj_element_versions pev,
1210: pa_proj_elements pe
1211: WHERE por.object_id_from1 = p_element_version_id
1212: AND por.object_type_from ='PA_TASKS'
1213: AND por.relationship_type = 'S'
1214: AND por.object_id_to1=pev.element_version_id
1332: EXCEPTION
1333: WHEN OTHERS THEN
1334: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1335: x_error_message_code := SQLCODE ;
1336: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
1337: p_procedure_name => 'Check_Date_range',
1338: p_error_text => SUBSTRB(SQLERRM,1,240));
1339: RAISE;
1340: end Check_Date_range;
1405: END IF;
1406: ELSE
1407: select element_version_id
1408: into x_task_version_id
1409: from pa_proj_elements ppe, pa_proj_element_versions ppev
1410: where ppe.proj_element_id = ppev.proj_element_id
1411: AND ppe.name = p_task_name
1412: AND ppev.parent_structure_version_id = p_structure_version_id;
1413: null;
2213: FUNCTION LINK_FLAG( p_element_id NUMBER ) RETURN VARCHAR2 IS
2214: CURSOR cur_proj_elements
2215: IS
2216: SELECT link_task_flag
2217: FROM pa_proj_elements
2218: WHERE proj_element_id = p_element_id;
2219:
2220: l_link_task_flag VARCHAR2(1) :='N'; --bug 4180390
2221: BEGIN
2629:
2630: /* Bug2680486 -- Performance changes -- Commented the following cursor query and restructured it. */
2631: /* cursor c1 IS
2632: select 'Y'
2633: from PA_PROJ_ELEMENTS
2634: where type_id = p_task_type_id;
2635: */
2636:
2637: cursor c1 IS
2638: select 'Y'
2639: from dual
2640: where exists (
2641: select 'xyz'
2642: from PA_PROJ_ELEMENTS
2643: where type_id = p_task_type_id
2644: AND project_id > -1
2645: AND object_type = 'PA_TASKS'
2646: );
2714: IS
2715: --bug 2863836: modified cursor to refer to system status
2716: cursor c1 is
2717: select b.project_system_status_code
2718: from pa_proj_elements a, pa_project_statuses b
2719: where a.proj_element_id = p_task_id
2720: and a.status_code = b.project_status_code
2721: and b.status_type = 'TASK';
2722: l_dummy pa_proj_elements.status_code%TYPE;
2718: from pa_proj_elements a, pa_project_statuses b
2719: where a.proj_element_id = p_task_id
2720: and a.status_code = b.project_status_code
2721: and b.status_type = 'TASK';
2722: l_dummy pa_proj_elements.status_code%TYPE;
2723: l_retval VARCHAR2(1);
2724: BEGIN
2725: open c1;
2726: FETCH c1 into l_dummy;
2778: OPEN c1 (p_task_id);
2779: FETCH c1 INTO v_c1;
2780: CLOSE c1;
2781:
2782: l_element_version_id := PA_PROJ_ELEMENTS_UTILS.GET_LATEST_FIN_PUB_TASK_VER_ID
2783: (p_project_id => v_c1.project_id,
2784: p_task_id => p_task_id);
2785:
2786: IF l_element_version_id IS NULL THEN
2824:
2825: l_task_ver_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE() ; -- 4201927 for performance issue
2826: BEGIN
2827:
2828: PA_PROJ_ELEMENTS_UTILS.CHECK_DELETE_TASK_VER_OK(
2829: p_project_id => p_project_id
2830: ,p_task_version_id => p_task_version_id
2831: ,p_parent_structure_ver_id => p_parent_structure_ver_id
2832: ,x_return_status => x_return_status
2842: OPEN c1;
2843: LOOP
2844: FETCH c1 into l_task_ver_id;
2845: EXIT WHEN c1%NOTFOUND;
2846: PA_PROJ_ELEMENTS_UTILS.Check_delete_task_ver_ok(
2847: p_project_id => p_project_id
2848: ,p_task_version_id => l_task_ver_id
2849: ,p_parent_structure_ver_id => p_parent_structure_ver_id
2850: ,x_return_status => x_return_status
2865:
2866: IF nvl(l_task_ver_id_tbl.LAST,0) > 0 THEN
2867: FOR i in reverse l_task_ver_id_tbl.FIRST..l_task_ver_id_tbl.LAST LOOP
2868:
2869: PA_PROJ_ELEMENTS_UTILS.Check_delete_task_ver_ok(
2870: p_project_id => p_project_id
2871: ,p_task_version_id => l_task_ver_id_tbl(i)
2872: ,p_parent_structure_ver_id => p_parent_structure_ver_id
2873: ,x_return_status => x_return_status
2891: WHEN OTHERS THEN
2892: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2893: -- 4537865
2894: x_error_message_code := SQLCODE ;
2895: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
2896: p_procedure_name => 'Check_Del_all_task_Ver_Ok
2897: ');
2898: RAISE;
2899: END Check_Del_all_task_Ver_Ok;
2906: IS
2907: CURSOR get_status IS
2908: select ppe.project_id, ppe.proj_element_id
2909: from pa_project_statuses pps,
2910: pa_proj_elements ppe,
2911: pa_proj_element_versions ppev
2912: where ppev.element_version_id = p_parent_task_ver_id
2913: and ppe.project_id = ppev.project_id
2914: and ppe.proj_element_id = ppev.proj_element_id
2989:
2990: --if financial, check if ok to create subtask.
2991: --Bug 5988335 Adding condition for partial share project to skip financial validation
2992: --when a new subtask is getting created in workplan.
2993: If (PA_PROJ_ELEMENTS_UTILS.CHECK_IS_FINANCIAL_TASK(l_proj_element_id) = 'Y') AND
2994: (PA_PROJ_TASK_STRUC_PUB.GET_SHARE_TYPE(l_project_id) <> 'SHARE_PARTIAL') --Bug 5988335
2995: THEN
2996: PA_TASK_UTILS.CHECK_CREATE_SUBTASK_OK(x_task_id => l_proj_element_id,
2997: x_err_code => l_err_code,
3061: EXCEPTION
3062: WHEN OTHERS THEN
3063: x_return_status := 'N';
3064: x_error_message_code := SQLCODE ;
3065: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3066: p_procedure_name => 'Check_create_subtask_ok',
3067: p_error_text => SUBSTRB(SQLERRM,1,240));
3068: RAISE ;
3069: END Check_create_subtask_ok;
3119: VARCHAR2
3120: IS
3121: CURSOR l_element_has_phase_csr IS
3122: SELECT 'Y'
3123: FROM pa_proj_elements
3124: WHERE proj_element_id = p_proj_element_id
3125: AND phase_version_id is not null;
3126:
3127: l_has_phase VARCHAR2(1) := 'N';
3161: VARCHAR2
3162: IS
3163: CURSOR l_exist_non_top_task_csr IS
3164: select 'N'
3165: from pa_proj_elements ppe,
3166: pa_proj_element_versions ppev
3167: where ppe.proj_element_id = p_proj_element_id
3168: and ppe.proj_element_id = ppev.proj_element_id
3169: and nvl(ppev.wbs_level, 0) <> 1;
3208:
3209: /* Bug 2680486 -- Performance changes -- Added join of project_id in the following cursor*/
3210: CURSOR l_phase_in_use_csr IS
3211: select 'Y'
3212: from pa_proj_elements ppe,
3213: pa_proj_elements ppe2
3214: where ppe.proj_element_id = p_task_id
3215: and ppe.parent_structure_id = ppe2.parent_structure_id
3216: and ppe2.phase_version_id = p_phase_version_id
3209: /* Bug 2680486 -- Performance changes -- Added join of project_id in the following cursor*/
3210: CURSOR l_phase_in_use_csr IS
3211: select 'Y'
3212: from pa_proj_elements ppe,
3213: pa_proj_elements ppe2
3214: where ppe.proj_element_id = p_task_id
3215: and ppe.parent_structure_id = ppe2.parent_structure_id
3216: and ppe2.phase_version_id = p_phase_version_id
3217: and ppe2.proj_element_id <> p_task_id
3244: x_error_message_code OUT NOCOPY VARCHAR2) -- 4537865
3245: IS
3246: CURSOR c1 is
3247: select 1 from
3248: pa_proj_elements a,
3249: pa_proj_element_versions b,
3250: pa_proj_elem_ver_structure c
3251: where a.proj_element_id = p_task_id
3252: and a.project_id = p_project_id
3301: IS
3302: CURSOR get_status IS
3303: select '1'
3304: from pa_project_statuses pps,
3305: pa_proj_elements ppe,
3306: pa_proj_element_versions ppev
3307: where ppev.element_version_id = p_task_ver_id
3308: and ppe.project_id = ppev.project_id
3309: and ppe.proj_element_id = ppev.proj_element_id
3325: EXCEPTION
3326: WHEN OTHERS THEN
3327: x_return_status := 'N' ;
3328: x_error_message_code := SQLCODE ;
3329: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3330: p_procedure_name => 'check_move_task_ok',
3331: p_error_text => SUBSTRB(SQLERRM,1,240));
3332: RAISE ;
3333: END check_move_task_ok;
3455: -- 4537865
3456: -- Not resetting x_error_message_code as at this point it would have been already populated.
3457: WHEN OTHERS THEN
3458: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3459: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3460: p_procedure_name => 'Check_chg_stat_cancel_ok');
3461:
3462: -- 4537865
3463: x_error_message_code := SQLCODE ;
3473: END IF;
3474:
3475: SELECT name
3476: INTO l_ret
3477: FROM pa_proj_elements
3478: WHERE proj_element_id = p_proj_element_id;
3479:
3480: RETURN l_ret;
3481: EXCEPTION
3479:
3480: RETURN l_ret;
3481: EXCEPTION
3482: WHEN OTHERS THEN
3483: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3484: p_procedure_name => 'get_element_name');
3485: RAISE;
3486: END get_element_name;
3487:
3494: END IF;
3495:
3496: SELECT element_number
3497: INTO l_ret
3498: FROM pa_proj_elements
3499: WHERE proj_element_id = p_proj_element_id;
3500:
3501: RETURN l_ret;
3502: EXCEPTION
3500:
3501: RETURN l_ret;
3502: EXCEPTION
3503: WHEN OTHERS THEN
3504: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3505: p_procedure_name => 'get_element_number');
3506: RAISE;
3507: END get_element_number;
3508:
3515: END IF;
3516:
3517: SELECT name||'('||element_number||')'
3518: INTO l_ret
3519: FROM pa_proj_elements
3520: WHERE proj_element_id = p_proj_element_id;
3521:
3522: RETURN l_ret;
3523: EXCEPTION
3521:
3522: RETURN l_ret;
3523: EXCEPTION
3524: WHEN OTHERS THEN
3525: fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS',
3526: p_procedure_name => 'get_element_name_number');
3527: RAISE;
3528: END get_element_name_number;
3529:
3649:
3650: PROCEDURE GET_STRUCTURE_INFO
3651: ( p_project_id IN pa_projects_all.project_id%TYPE
3652: ,p_structure_type IN pa_structure_types.structure_type_class_code%TYPE
3653: ,p_structure_id IN pa_proj_elements.proj_element_id%TYPE
3654: ,p_is_wp_separate_from_fn IN VARCHAR2
3655: ,p_is_wp_versioning_enabled IN VARCHAR2
3656: ,x_structure_version_id OUT NOCOPY pa_proj_element_versions.element_version_id%TYPE -- 4537865
3657: ,x_task_unpub_ver_status_code OUT NOCOPY pa_proj_element_versions.task_unpub_ver_status_code%TYPE -- 4537865
3871: x_structure_version_id := NULL;
3872: -- 4537865 : End
3873:
3874: FND_MSG_PUB.add_exc_msg
3875: ( p_pkg_name => 'PA_PROJ_ELEMENTS_UTILS'
3876: ,p_procedure_name => 'GET_STRUCTURE_INFO'
3877: ,p_error_text => x_msg_data);
3878:
3879: IF l_debug_mode = 'Y' THEN
3891: -- Purpose : This procedure will check whether the task has transaction or not.This API will be
3892: -- : called from Set_Financial_task_API.
3893: -- Note : Check whether it is a financial task or workplan task.
3894: -- : Fetch the parent_structure_version_id for the passed proj_element_Id from
3895: -- : PA_PROJ_ELEMENT_VERSIONS and pass to API PA_PROJ_ELEMENTS_UTILS.structure_type
3896:
3897: -- Assumptions : Only called for Financial task
3898:
3899: -- Parameters Type Required Description and Purpose
3958: PA_DEBUG.set_curr_function( p_function =>'CHECK_TASK_HAS_TRANSACTION' , p_debug_mode => l_debug_mode );
3959: END IF;
3960:
3961: IF l_debug_mode = 'Y' THEN
3962: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : Printing Input parameters';
3963: Pa_Debug.WRITE(g_module_name , Pa_Debug.g_err_stage , l_debug_level3);
3964: Pa_Debug.WRITE(g_module_name , 'p_task_id'||':'||p_task_id , l_debug_level3);
3965: END IF;
3966:
3965: END IF;
3966:
3967:
3968: IF l_debug_mode = 'Y' THEN
3969: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : Validating Input Paramater';
3970: Pa_Debug.WRITE(g_module_name , Pa_Debug.g_err_stage , l_debug_level3);
3971: END IF;
3972:
3973: -- Validating for Input parameter
3974: IF ( p_task_id IS NOT NULL ) THEN
3975:
3976: --Commented to be reviewed once more with set financial task
3977: --IF l_debug_mode = 'Y' THEN
3978: -- Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : Checking for financial type';
3979: -- Pa_Debug.WRITE ( g_module_name , Pa_Debug.g_err_stage , l_debug_level3 );
3980: --END IF;
3981:
3982: --Select the parent structure version id for the passed task id
3985: --CLOSE c_get_parent_str_ver_id;
3986:
3987: --check if the structure type is financial
3988: --IF (
3989: -- PA_PROJ_ELEMENTS_UTILS.structure_type
3990: -- ( p_structure_version_id => l_prnt_str_ver_id
3991: -- , p_task_version_id => null
3992: -- , p_structure_type => 'FINANCIAL'
3993: -- ) = 'Y'
3996:
3997: -- Commenting code for 4903460 and replacing this wth new code
3998: --Check if task has expenditure item
3999: /* IF l_debug_mode = 'Y' THEN
4000: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check expenditure item for '|| p_task_id;
4001: Pa_Debug.WRITE(g_module_name , Pa_Debug.g_err_stage , l_debug_level3);
4002: END IF;
4003: l_status_code :=
4004: pa_proj_tsk_utils.check_exp_item_exists(null, p_task_id);
4012: END IF;
4013:
4014: --Check if task has purchase order distribution
4015: IF l_debug_mode = 'Y' THEN
4016: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check purchase order for '|| p_task_id;
4017: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4018: l_debug_level3);
4019: END IF;
4020: l_status_code :=
4029: END IF;
4030:
4031: -- Check if task has purchase order requisition
4032: IF l_debug_mode = 'Y' THEN
4033: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check purchase order requisition for '|| p_task_id;
4034: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4035: l_debug_level3);
4036: END IF;
4037:
4047: END IF;
4048:
4049: -- Check if task has supplier invoices
4050: IF l_debug_mode = 'Y' THEN
4051: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check supplier invoice for '|| p_task_id;
4052: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4053: l_debug_level3);
4054: END IF;
4055:
4065: END IF;
4066:
4067: -- Check if task has supplier invoice distribution
4068: IF l_debug_mode = 'Y' THEN
4069: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check supplier inv distribution for '|| p_task_id;
4070: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4071: l_debug_level3);
4072: END IF;
4073:
4083: END IF;
4084:
4085: -- Check if task has commitment transaction
4086: IF l_debug_mode = 'Y' THEN
4087: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check commitment transaction for '|| p_task_id;
4088: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4089: l_debug_level3);
4090: END IF;
4091: l_status_code :=
4100: END IF;
4101:
4102: -- Check if task has compensation rule set
4103: IF l_debug_mode = 'Y' THEN
4104: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check compensation rule set for '|| p_task_id;
4105: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4106: l_debug_level3);
4107: END IF;
4108:
4118: END IF; */
4119: -- End of Commenting for 4903460
4120: -- Check if task is in use in an external system
4121: IF l_debug_mode = 'Y' THEN
4122: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check for task used in external system for'|| p_task_id;
4123: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4124: l_debug_level3);
4125: END IF;
4126:
4146: END IF;
4147:
4148: -- Check if task is used in allocations
4149: IF l_debug_mode = 'Y' THEN
4150: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check if project allocations uses task '|| p_task_id;
4151: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4152: l_debug_level3);
4153: END IF;
4154:
4163: -- Commenting for Performance fix 4903460
4164: /*
4165: -- Check if task has draft invoices
4166: IF l_debug_mode = 'Y' THEN
4167: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check draft invoice for '|| p_task_id;
4168: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4169: l_debug_level3);
4170: END IF;
4171:
4181: END IF;
4182:
4183: -- Check if task has Project_customers
4184: IF l_debug_mode = 'Y' THEN
4185: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check Project Customers for '|| p_task_id;
4186: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4187: l_debug_level3);
4188: END IF;
4189:
4200: END IF; */
4201: --End of Commenting for Performance fix 4903460
4202:
4203: -- Start of new code for Performance fix 4903460
4204: PA_PROJ_ELEMENTS_UTILS.perform_task_validations
4205: (
4206: p_project_id => p_project_id
4207: ,p_task_id => p_task_id
4208: ,x_error_code => x_error_code
4216:
4217: -- Check if project contract is installed
4218: IF (pa_install.is_product_installed('OKE')) THEN
4219: IF l_debug_mode = 'Y' THEN
4220: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : Check contract association for task '|| p_task_id;
4221: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4222: l_debug_level3);
4223: END IF;
4224:
4246: --end of OTL check.
4247: Is_IEX_Installed := pa_install.is_product_installed('IEX');
4248: If Is_IEX_Installed then
4249: IF l_debug_mode = 'Y' THEN
4250: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : check if task '|| p_task_id || ' is charged in iexpense';
4251: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4252: l_debug_level3);
4253: END IF;
4254:
4263: END IF;
4264: END IF;
4265: --BEGIN
4266: IF l_debug_mode = 'Y' THEN
4267: Pa_Debug.g_err_stage:= 'PA_PROJ_ELEMENTS_UTILS : CHECK_TASK_HAS_TRANSACTION : PA_FIN_PLAN_UTILS.CHECK_DELETE_TASK_OK'|| p_task_id;
4268: Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
4269: l_debug_level3);
4270: END IF;
4271:
4617: --If sharing is enabled, then financial and workplan tasks would be common
4618: IF str_sharing_code = 'SHARE_FULL' OR
4619: str_sharing_code = 'SHARE_PARTIAL' THEN
4620: return_flag := 'Y';
4621: ELSIF PA_PROJ_ELEMENTS_UTILS.CHECK_IS_FINANCIAL_TASK(p_proj_element_id) = 'N' THEN
4622: return_flag := 'Y';
4623: ELSE
4624: return_flag := 'N';
4625: END IF;
4681: ,p_task_id IN NUMBER) return NUMBER
4682: is
4683: l_task_version_id NUMBER;
4684: begin
4685: select b.element_version_id into l_task_version_id from pa_proj_elements a, pa_proj_element_versions b
4686: where a.proj_element_id = b.proj_element_id
4687: and a.proj_element_id = p_task_id
4688: and b.parent_structure_version_id = p_structure_version_id;
4689:
4718: --
4719: CURSOR cur_check_deliv (cp_task_version_id number) IS
4720: SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code
4721: FROM pa_proj_element_versions ppev,
4722: pa_proj_elements ppe
4723: WHERE ppe.project_id = ppev.project_id
4724: AND ppe.proj_element_id = ppev.proj_element_id
4725: AND ppev.object_type = 'PA_TASKS'
4726: AND ppe.object_type = 'PA_TASKS'
4763: IS
4764: CURSOR cur_check_deliv_bt (cp_target_task_version_id number) IS
4765: SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
4766: FROM pa_proj_element_versions ppev,
4767: pa_proj_elements ppe
4768: WHERE ppe.project_id = ppev.project_id
4769: AND ppe.proj_element_id = ppev.proj_element_id
4770: AND ppev.object_type = 'PA_TASKS'
4771: AND ppe.object_type = 'PA_TASKS'
4782:
4783: CURSOR cur_check_deliv_tb (cp_task_version_id number) IS
4784: SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
4785: FROM pa_proj_element_versions ppev,
4786: pa_proj_elements ppe
4787: WHERE ppe.project_id = ppev.project_id
4788: AND ppe.proj_element_id = ppev.proj_element_id
4789: AND ppev.object_type = 'PA_TASKS'
4790: AND ppe.object_type = 'PA_TASKS'
4852: /* This cursor goes from leaf node to top of the branch*/
4853: CURSOR check_for_deliv_cur (cp_task_version_id number) IS
4854: SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code
4855: FROM pa_proj_element_versions ppev,
4856: pa_proj_elements ppe
4857: WHERE ppe.project_id = ppev.project_id
4858: AND ppe.proj_element_id = ppev.proj_element_id
4859: AND ppev.object_type = 'PA_TASKS'
4860: AND ppe.object_type = 'PA_TASKS'
4918: IS
4919: CURSOR C
4920: IS
4921: SELECT 'Y'
4922: FROM pa_wf_processes pwp, pa_proj_elements ppe -- Bug #3967939
4923: WHERE pwp.ENTITY_KEY2 = to_char(p_proj_element_id) -- Bug#3619754 : Added to_char
4924: AND ppe.PROJ_ELEMENT_ID = p_proj_element_id -- Bug #3967939
4925: AND pwp.ITEM_TYPE = ppe.WF_ITEM_TYPE; -- Bug #3967939
4926:
4946: CURSOR C
4947: IS
4948: select max(item_key)
4949: from pa_wf_processes wp
4950: , pa_proj_elements pe
4951: where wp.item_type = pe.wf_item_type
4952: and wp.wf_type_code = p_wf_type_code
4953: and to_char(pe.proj_element_id) = wp.entity_key2 --Bug 3619754 Added By avaithia
4954: and to_char(pe.project_id) = wp.entity_key1 --Bug 3619754 Added By avaithia
4972: IS
4973: CURSOR c_item_type
4974: IS
4975: select wf_item_type
4976: from pa_proj_elements pe
4977: where
4978: pe.project_id = p_project_id and pe.proj_element_id =p_proj_element_id;
4979:
4980: l_status VARCHAR2(240) := '';
5015: -- p_proj_element_id NUMBER Y The proj_element_id to be checked
5016: FUNCTION check_fin_or_wp_structure( p_proj_element_id IN NUMBER ) RETURN VARCHAR2 IS
5017: CURSOR cur_chk_fin_or_wp_structure IS
5018: SELECT 'Y'
5019: FROM pa_proj_elements ppe
5020: ,pa_proj_structure_types ppst
5021: ,pa_structure_types pst
5022: WHERE ppe.proj_element_id = p_proj_element_id
5023: AND ppe.object_type = 'PA_STRUCTURES'
5082: IS
5083: CURSOR cur_check_deliv_bt (cp_target_task_version_id number) IS
5084: SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
5085: FROM pa_proj_element_versions ppev,
5086: pa_proj_elements ppe
5087: WHERE ppe.project_id = ppev.project_id
5088: AND ppe.proj_element_id = ppev.proj_element_id
5089: AND ppev.object_type = 'PA_TASKS'
5090: AND ppe.object_type = 'PA_TASKS'
5101:
5102: CURSOR cur_check_deliv_tb (cp_task_version_id number) IS
5103: SELECT ppe.proj_element_id,ppe.base_percent_comp_deriv_code, ppev.element_version_id
5104: FROM pa_proj_element_versions ppev,
5105: pa_proj_elements ppe
5106: WHERE ppe.project_id = ppev.project_id
5107: AND ppe.proj_element_id = ppev.proj_element_id
5108: AND ppev.object_type = 'PA_TASKS'
5109: AND ppe.object_type = 'PA_TASKS'
5314: CURSOR cur_pa_proj
5315: IS
5316:
5317: SELECT 'x'
5318: FROM pa_proj_elements ppe, pa_proj_structure_types ppst
5319: WHERE ppe.project_id = p_project_id
5320: AND ppe.object_type = 'PA_STRUCTURES'
5321: AND ppe.proj_element_id = ppst.proj_element_id
5322: AND ppst.structure_type_id = 1; --'WORKPLAN'
5343: ) return VARCHAR2
5344: IS
5345: CURSOR c1 IS
5346: SELECT 'Y'
5347: FROM pa_proj_elements a,
5348: pa_proj_structure_types b,
5349: pa_structure_types c,
5350: pa_proj_structure_types d,
5351: pa_structure_types e
6138: l_dummy number;
6139: cursor child_exist IS
6140: select 1 from dual where exists(
6141: select 1
6142: from pa_object_relationships por, pa_proj_element_versions ppev, pa_proj_elements ppe
6143: where por.object_type_from = 'PA_TASKS'
6144: and por.object_id_from1 = p_task_version_id
6145: and por.relationship_type = 'S'
6146: and por.object_id_to1 = ppev.element_version_id
6165: raise;
6166: END IS_LOWEST_PROJ_TASK;
6167:
6168:
6169: END PA_PROJ_ELEMENTS_UTILS;