[Home] [Help]
215:
216: select 'Y'
217: into l_exists
218: from pa_budget_lines a,
219: pa_resource_assignments b
220: where a.resource_assignment_id = b.resource_assignment_id
221: and b.budget_version_id = p_budget_version_id
222: and rownum < 2;
223:
230:
231:
232: /*
233: API Name : Plan_Amount_Exists_Task_Res
234: API Description : Return 'Y' if at least one record exists in Resource Assignments (pa_resource_assignments)
235: for the given Budget Version Id, Task Id, Resource List Member Id
236: API Created By : Vthakkar
237: API Creation Date : 15-MAR-2004
238: */
248:
249: select 'Y'
250: into l_exists
251: from pa_budget_lines a,
252: pa_resource_assignments b
253: where a.resource_assignment_id = b.resource_assignment_id
254: and b.budget_version_id = p_budget_version_id
255: and b.task_id = Nvl(p_task_id,b.task_id)
256: and b.resource_list_member_id = Nvl(p_resource_list_member_id,b.resource_list_member_id)
864: delete from pa_proj_period_profiles where project_id=p_project_id;
865:
866: /* Bug 3683382 this delete is not required functionally as records can not
867: exist for a project level option in this table
868: -- delete from pa_resource_assignments table
869: delete from pa_resource_assignments where project_id = p_project_id;
870: */
871:
872: /*start of bug 3342975 Refer to Update "16-JAN-04 sagarwal"
865:
866: /* Bug 3683382 this delete is not required functionally as records can not
867: exist for a project level option in this table
868: -- delete from pa_resource_assignments table
869: delete from pa_resource_assignments where project_id = p_project_id;
870: */
871:
872: /*start of bug 3342975 Refer to Update "16-JAN-04 sagarwal"
873: in the history above. This has been added as part of code merge */
5418: where exists
5419: (select 1
5420: from pa_budget_versions bv
5421: , pa_tasks t
5422: , pa_resource_assignments a
5423: where a.budget_version_id = bv.budget_version_id
5424: and a.task_id = t.task_id
5425: and a.resource_assignment_type = 'USER_ENTERED'
5426: and t.top_task_id = x_task_id
5445: where exists
5446: (select 1
5447: from pa_budget_versions bv
5448: , pa_tasks t
5449: , pa_resource_assignments a
5450: where a.budget_version_id = bv.budget_version_id
5451: and a.task_id = t.task_id
5452: and a.resource_assignment_type = 'USER_ENTERED'
5453: and t.top_task_id = x_task_id
7683: cursor cur_check_elements is
7684: SELECT 'Y'
7685: FROM dual
7686: WHERE EXISTS (SELECT 'x'
7687: FROM pa_resource_assignments
7688: WHERE budget_version_id = p_budget_version_id);
7689:
7690: l_exists varchar2(1) := 'N';
7691:
8930: task can be deleted from old budgets model, organization forecasting, and new
8931: Budgeting and Forecasting perspective. For old budgets model and organization
8932: forecasting, presence of a task in resource assignments table implies that amounts
8933: exist for the task and so the task can not be deleted. For financial planning model,
8934: since records exists in pa_resource_assignments even when no budget lines exists,
8935: pa_fp_elements table has to be verified to check if plan amounts exist for a task.
8936: If p_validation_mode is U,
8937: p_task_id should not be present in BASELINED versions and should not be present in
8938: other versions with amounts
8967: l_validation_success VARCHAR2(1) := 'Y'; /* Y when delete is allowed, N when delete is not allowed */
8968:
8969: /* Changes for FP.M, Tracking Bug No - 3354518
8970: Replacing all references of PA_TASKS by PA_STRUCT_TASK_WBS_V
8971: and of PA_FP_ELEMNTS by PA_RESOURCE_ASSIGNMENTS
8972: as PA_FP_ELEMENTS is being obsoleted*/
8973:
8974:
8975: CURSOR delete_task_R_mode_cur IS
8978: WHERE EXISTS (
8979: /* Commenting out as part of FP.M, Tracking Bug No - 3354518
8980: Since We will now check the existence of a budget version
8981: (having wp_version_flag = 'N') using pa_budget_version and
8982: pa_resource_assignments */
8983:
8984: /* SELECT 1
8985: FROM pa_fp_elements fe */
8986: /* Bug 2993894 ,pa_budget_versions bv */ -- Commenting out code for FP.M, Tracking Bug No - 3354518
8993: START WITH pt.task_id = p_task_id)*/ -- Commenting out code for FP.M, Tracking Bug No - 3354518
8994: /* Bug 2993894 AND bv.budget_version_id = fe.fin_plan_version_id */
8995: /* UNION ALL */ -- Commenting out code for FP.M, Tracking Bug No - 3354518
8996: SELECT 1
8997: FROM pa_resource_assignments r,
8998: pa_budget_versions bv
8999: WHERE r.task_id IN
9000: (SELECT pt.task_id /*Changing refernece of pa_struct_task_wbs_v below to pa_tasks*/
9001: FROM PA_TASKS pt /*Reverting changes for FPM, view pa_struct_task_wbs_v cannot be used in connect by clause*/
9019: SELECT 'N' validation_success -- If cursor returns a record, deletion is not allowed
9020: FROM DUAL
9021: WHERE EXISTS (
9022: SELECT 1
9023: FROM -- pa_fp_elements fe Commenting out for to replace pa_fp_elements by PA_RESOURCE_ASSIGNMENTS as part of FP.M, Tracking Bug No - 3354518
9024: pa_resource_assignments fe,
9025: pa_budget_versions bv
9026: WHERE fe.task_id IN
9027: (SELECT pt.task_id
9020: FROM DUAL
9021: WHERE EXISTS (
9022: SELECT 1
9023: FROM -- pa_fp_elements fe Commenting out for to replace pa_fp_elements by PA_RESOURCE_ASSIGNMENTS as part of FP.M, Tracking Bug No - 3354518
9024: pa_resource_assignments fe,
9025: pa_budget_versions bv
9026: WHERE fe.task_id IN
9027: (SELECT pt.task_id
9028: FROM PA_STRUCT_TASK_WBS_V pt
9035: AND (fe.plan_amount_exists_flag = 'Y' OR bv.budget_status_code = 'B')
9036: AND nvl(bv.wp_version_flag,'N') = 'N' -- Added for FP.M, Tracking Bug No - 3354518
9037: UNION ALL
9038: SELECT 1
9039: FROM pa_resource_assignments r,
9040: pa_budget_versions bv
9041: WHERE r.task_id IN
9042: (SELECT pt.task_id
9043: FROM PA_STRUCT_TASK_WBS_V pt
9243: l_debug_mode VARCHAR2(1);
9244:
9245: cursor task_ra_csr (tid pa_tasks.task_id%TYPE) is
9246: select 1
9247: from pa_resource_assignments
9248: where task_id = tid;
9249: task_ra_rec task_ra_csr%ROWTYPE;
9250:
9251: BEGIN
9331: -- VALIDATION: Old parent task
9332: open task_ra_csr(p_old_parent_task_id);
9333: fetch task_ra_csr into task_ra_rec;
9334: IF task_ra_csr%FOUND then
9335: -- records in pa_resource_assignments: VALIDATION FAILED
9336: x_return_status := FND_API.G_RET_STS_ERROR;
9337: BEGIN
9338: select task_name
9339: into l_task_name
9481: if PA_FP_ELEMENTS contains a entry for this task.
9482: This procedure is now obsoleted.
9483: However noticing that this procedure is re-usable,
9484: we change all references to pa_tasks to pa_struct_task_wbs_v
9485: and all references of pa_fp_elements to pa_resource_assignments*/
9486:
9487: FUNCTION check_task_in_fp_option
9488: (
9489: /* p_task_id IN pa_tasks.task_id%TYPE */
9496:
9497: CURSOR C1 IS
9498: SELECT 'Y'
9499: FROM DUAL
9500: /* Changing reference of pa_fp_elements to pa_resource_assignments */
9501: WHERE EXISTS (SELECT 'X' FROM pa_resource_assignments WHERE TASK_ID = P_TASK_ID);
9502: /* WHERE EXISTS (SELECT 'X' FROM PA_FP_ELEMENTS WHERE TASK_ID = P_TASK_ID); */
9503:
9504: BEGIN
9497: CURSOR C1 IS
9498: SELECT 'Y'
9499: FROM DUAL
9500: /* Changing reference of pa_fp_elements to pa_resource_assignments */
9501: WHERE EXISTS (SELECT 'X' FROM pa_resource_assignments WHERE TASK_ID = P_TASK_ID);
9502: /* WHERE EXISTS (SELECT 'X' FROM PA_FP_ELEMENTS WHERE TASK_ID = P_TASK_ID); */
9503:
9504: BEGIN
9505:
9600: IS
9601: SELECT sum(l.raw_cost)
9602: , sum(l.burdened_cost)
9603: FROM pa_budget_versions v
9604: , pa_resource_assignments a
9605: , pa_budget_lines l
9606: WHERE v.project_id = p_project_id
9607: AND v.budget_type_code = p_budget_type_code
9608: AND v.current_flag = 'Y'
9633: IS
9634: SELECT sum(l.raw_cost)
9635: , sum(l.burdened_cost)
9636: FROM pa_budget_versions v
9637: , pa_resource_assignments a
9638: , pa_budget_lines l
9639: WHERE v.project_id = p_project_id
9640: AND v.budget_type_code = p_budget_type_code
9641: AND v.current_flag = 'Y'
9643: AND a.task_id = p_task_id
9644: AND a.resource_assignment_id = l.resource_assignment_id;
9645:
9646:
9647: -- NEW FP Model Can join to PA_RESOURCE_ASSIGNMENTS for Denormalized Amounts
9648: --
9649: -- !!! * * * PLEASE NOTE * * * !!!
9650: -- For this cursor, the pa_resource_assignments table contains multiple
9651: -- row types for a given baselined version,i.e.:
9646:
9647: -- NEW FP Model Can join to PA_RESOURCE_ASSIGNMENTS for Denormalized Amounts
9648: --
9649: -- !!! * * * PLEASE NOTE * * * !!!
9650: -- For this cursor, the pa_resource_assignments table contains multiple
9651: -- row types for a given baselined version,i.e.:
9652: --
9653: -- ROLLED_UP
9654: -- USER_ENTERED
9666: IS
9667: SELECT sum(a.TOTAL_PLAN_RAW_COST)
9668: , sum(a.TOTAL_PLAN_BURDENED_COST)
9669: FROM pa_budget_versions v
9670: , pa_resource_assignments a
9671: WHERE v.project_id = p_project_id
9672: AND v.fin_plan_type_id = p_fin_plan_type_id
9673: AND v.current_flag = 'Y'
9674: AND v.budget_version_id = a.budget_version_id
10412: SELECT 'Y'
10413: INTO l_exists
10414: FROM DUAL
10415: WHERE EXISTS (SELECT 'x'
10416: FROM PA_RESOURCE_ASSIGNMENTS a, pa_proj_element_versions pelm
10417: WHERE a.budget_version_id = p_budget_version_id
10418: AND a.task_id = pelm.proj_element_id
10419: AND a.resource_class_code = PA_FP_CONSTANTS_PKG.G_RESOURCE_CLASS_CODE_FIN
10420: AND a.task_id = p_task_id
10424: SELECT 'Y'
10425: INTO l_exists
10426: FROM DUAL
10427: WHERE EXISTS (SELECT 'x'
10428: FROM PA_RESOURCE_ASSIGNMENTS a
10429: WHERE a.budget_version_id = p_budget_version_id
10430: AND a.resource_class_code = PA_FP_CONSTANTS_PKG.G_RESOURCE_CLASS_CODE_FIN
10431: AND a.task_id = p_task_id
10432: AND a.resource_class_flag = 'Y');
10443: /* To determine if a task has resources attached to it as planning element */
10444:
10445: FUNCTION IS_RESOURCE_ATTACHED_TO_TASK(
10446: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
10447: ,p_task_id IN pa_resource_assignments.task_id%TYPE)
10448: --,p_wbs_element_version_id IN pa_resource_assignments.wbs_element_version_id%TYPE)
10449: RETURN VARCHAR2 IS
10450: l_exists VARCHAR2(1);
10451: BEGIN
10444:
10445: FUNCTION IS_RESOURCE_ATTACHED_TO_TASK(
10446: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
10447: ,p_task_id IN pa_resource_assignments.task_id%TYPE)
10448: --,p_wbs_element_version_id IN pa_resource_assignments.wbs_element_version_id%TYPE)
10449: RETURN VARCHAR2 IS
10450: l_exists VARCHAR2(1);
10451: BEGIN
10452: -- sagarwal -- Removed redundant join to pa_budget_versions from select statement below
10453: SELECT 'Y'
10454: INTO l_exists
10455: FROM DUAL
10456: WHERE EXISTS (SELECT 'x'
10457: FROM PA_RESOURCE_ASSIGNMENTS a
10458: WHERE a.budget_version_id = p_budget_version_id
10459: AND a.task_id = p_task_id
10460: --Commented for bug 3793136
10461: --AND a.wbs_element_version_id = p_wbs_element_version_id
12370: SELECT 'Y' INTO l_task_assignments_exist_flag
12371: FROM dual WHERE EXISTS
12372: (SELECT 1
12373: FROM pa_budget_versions bv,
12374: pa_resource_assignments ra
12375: WHERE bv.project_id = p_project_id
12376: AND bv.wp_version_flag = 'Y'
12377: AND ra.budget_version_id = bv.budget_version_id
12378: AND ra.ta_display_flag = 'Y');
13522: API Creation Date : 07-MAY-2007
13523: */
13524:
13525: FUNCTION Get_NP_RA_Description
13526: (p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE Default Null,
13527: p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE Default Null
13528: ) RETURN VARCHAR2
13529: IS
13530: l_description pa_budget_lines.description%TYPE;
13551: API Creation Date : 07-MAY-2007
13552: */
13553:
13554: FUNCTION Get_Change_Reason
13555: (p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE Default Null,
13556: p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE Default Null,
13557: p_time_phased_code IN varchar2
13558: ) RETURN VARCHAR2
13559: IS