[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)
866: delete from pa_proj_period_profiles where project_id=p_project_id;
867:
868: /* Bug 3683382 this delete is not required functionally as records can not
869: exist for a project level option in this table
870: -- delete from pa_resource_assignments table
871: delete from pa_resource_assignments where project_id = p_project_id;
872: */
873:
874: /*start of bug 3342975 Refer to Update "16-JAN-04 sagarwal"
867:
868: /* Bug 3683382 this delete is not required functionally as records can not
869: exist for a project level option in this table
870: -- delete from pa_resource_assignments table
871: delete from pa_resource_assignments where project_id = p_project_id;
872: */
873:
874: /*start of bug 3342975 Refer to Update "16-JAN-04 sagarwal"
875: in the history above. This has been added as part of code merge */
5420: where exists
5421: (select 1
5422: from pa_budget_versions bv
5423: , pa_tasks t
5424: , pa_resource_assignments a
5425: where a.budget_version_id = bv.budget_version_id
5426: and a.task_id = t.task_id
5427: and a.resource_assignment_type = 'USER_ENTERED'
5428: and t.top_task_id = x_task_id
5447: where exists
5448: (select 1
5449: from pa_budget_versions bv
5450: , pa_tasks t
5451: , pa_resource_assignments a
5452: where a.budget_version_id = bv.budget_version_id
5453: and a.task_id = t.task_id
5454: and a.resource_assignment_type = 'USER_ENTERED'
5455: and t.top_task_id = x_task_id
7689: cursor cur_check_elements is
7690: SELECT 'Y'
7691: FROM dual
7692: WHERE EXISTS (SELECT 'x'
7693: FROM pa_resource_assignments
7694: WHERE budget_version_id = p_budget_version_id);
7695:
7696: l_exists varchar2(1) := 'N';
7697:
8936: task can be deleted from old budgets model, organization forecasting, and new
8937: Budgeting and Forecasting perspective. For old budgets model and organization
8938: forecasting, presence of a task in resource assignments table implies that amounts
8939: exist for the task and so the task can not be deleted. For financial planning model,
8940: since records exists in pa_resource_assignments even when no budget lines exists,
8941: pa_fp_elements table has to be verified to check if plan amounts exist for a task.
8942: If p_validation_mode is U,
8943: p_task_id should not be present in BASELINED versions and should not be present in
8944: other versions with amounts
8973: l_validation_success VARCHAR2(1) := 'Y'; /* Y when delete is allowed, N when delete is not allowed */
8974:
8975: /* Changes for FP.M, Tracking Bug No - 3354518
8976: Replacing all references of PA_TASKS by PA_STRUCT_TASK_WBS_V
8977: and of PA_FP_ELEMNTS by PA_RESOURCE_ASSIGNMENTS
8978: as PA_FP_ELEMENTS is being obsoleted*/
8979:
8980:
8981: CURSOR delete_task_R_mode_cur IS
8984: WHERE EXISTS (
8985: /* Commenting out as part of FP.M, Tracking Bug No - 3354518
8986: Since We will now check the existence of a budget version
8987: (having wp_version_flag = 'N') using pa_budget_version and
8988: pa_resource_assignments */
8989:
8990: /* SELECT 1
8991: FROM pa_fp_elements fe */
8992: /* Bug 2993894 ,pa_budget_versions bv */ -- Commenting out code for FP.M, Tracking Bug No - 3354518
8999: START WITH pt.task_id = p_task_id)*/ -- Commenting out code for FP.M, Tracking Bug No - 3354518
9000: /* Bug 2993894 AND bv.budget_version_id = fe.fin_plan_version_id */
9001: /* UNION ALL */ -- Commenting out code for FP.M, Tracking Bug No - 3354518
9002: SELECT 1
9003: FROM pa_resource_assignments r,
9004: pa_budget_versions bv
9005: WHERE r.task_id IN
9006: (SELECT pt.task_id /*Changing refernece of pa_struct_task_wbs_v below to pa_tasks*/
9007: FROM PA_TASKS pt /*Reverting changes for FPM, view pa_struct_task_wbs_v cannot be used in connect by clause*/
9025: SELECT 'N' validation_success -- If cursor returns a record, deletion is not allowed
9026: FROM DUAL
9027: WHERE EXISTS (
9028: SELECT 1
9029: 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
9030: pa_resource_assignments fe,
9031: pa_budget_versions bv
9032: WHERE fe.task_id IN
9033: (SELECT pt.task_id
9026: FROM DUAL
9027: WHERE EXISTS (
9028: SELECT 1
9029: 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
9030: pa_resource_assignments fe,
9031: pa_budget_versions bv
9032: WHERE fe.task_id IN
9033: (SELECT pt.task_id
9034: FROM PA_STRUCT_TASK_WBS_V pt
9041: AND (fe.plan_amount_exists_flag = 'Y' OR bv.budget_status_code = 'B')
9042: AND nvl(bv.wp_version_flag,'N') = 'N' -- Added for FP.M, Tracking Bug No - 3354518
9043: UNION ALL
9044: SELECT 1
9045: FROM pa_resource_assignments r,
9046: pa_budget_versions bv
9047: WHERE r.task_id IN
9048: (SELECT pt.task_id
9049: FROM PA_STRUCT_TASK_WBS_V pt
9249: l_debug_mode VARCHAR2(1);
9250:
9251: cursor task_ra_csr (tid pa_tasks.task_id%TYPE) is
9252: select 1
9253: from pa_resource_assignments
9254: where task_id = tid;
9255: task_ra_rec task_ra_csr%ROWTYPE;
9256:
9257: BEGIN
9337: -- VALIDATION: Old parent task
9338: open task_ra_csr(p_old_parent_task_id);
9339: fetch task_ra_csr into task_ra_rec;
9340: IF task_ra_csr%FOUND then
9341: -- records in pa_resource_assignments: VALIDATION FAILED
9342: x_return_status := FND_API.G_RET_STS_ERROR;
9343: BEGIN
9344: select task_name
9345: into l_task_name
9487: if PA_FP_ELEMENTS contains a entry for this task.
9488: This procedure is now obsoleted.
9489: However noticing that this procedure is re-usable,
9490: we change all references to pa_tasks to pa_struct_task_wbs_v
9491: and all references of pa_fp_elements to pa_resource_assignments*/
9492:
9493: FUNCTION check_task_in_fp_option
9494: (
9495: /* p_task_id IN pa_tasks.task_id%TYPE */
9502:
9503: CURSOR C1 IS
9504: SELECT 'Y'
9505: FROM DUAL
9506: /* Changing reference of pa_fp_elements to pa_resource_assignments */
9507: WHERE EXISTS (SELECT 'X' FROM pa_resource_assignments WHERE TASK_ID = P_TASK_ID);
9508: /* WHERE EXISTS (SELECT 'X' FROM PA_FP_ELEMENTS WHERE TASK_ID = P_TASK_ID); */
9509:
9510: BEGIN
9503: CURSOR C1 IS
9504: SELECT 'Y'
9505: FROM DUAL
9506: /* Changing reference of pa_fp_elements to pa_resource_assignments */
9507: WHERE EXISTS (SELECT 'X' FROM pa_resource_assignments WHERE TASK_ID = P_TASK_ID);
9508: /* WHERE EXISTS (SELECT 'X' FROM PA_FP_ELEMENTS WHERE TASK_ID = P_TASK_ID); */
9509:
9510: BEGIN
9511:
9606: IS
9607: SELECT sum(l.raw_cost)
9608: , sum(l.burdened_cost)
9609: FROM pa_budget_versions v
9610: , pa_resource_assignments a
9611: , pa_budget_lines l
9612: WHERE v.project_id = p_project_id
9613: AND v.budget_type_code = p_budget_type_code
9614: AND v.current_flag = 'Y'
9639: IS
9640: SELECT sum(l.raw_cost)
9641: , sum(l.burdened_cost)
9642: FROM pa_budget_versions v
9643: , pa_resource_assignments a
9644: , pa_budget_lines l
9645: WHERE v.project_id = p_project_id
9646: AND v.budget_type_code = p_budget_type_code
9647: AND v.current_flag = 'Y'
9649: AND a.task_id = p_task_id
9650: AND a.resource_assignment_id = l.resource_assignment_id;
9651:
9652:
9653: -- NEW FP Model Can join to PA_RESOURCE_ASSIGNMENTS for Denormalized Amounts
9654: --
9655: -- !!! * * * PLEASE NOTE * * * !!!
9656: -- For this cursor, the pa_resource_assignments table contains multiple
9657: -- row types for a given baselined version,i.e.:
9652:
9653: -- NEW FP Model Can join to PA_RESOURCE_ASSIGNMENTS for Denormalized Amounts
9654: --
9655: -- !!! * * * PLEASE NOTE * * * !!!
9656: -- For this cursor, the pa_resource_assignments table contains multiple
9657: -- row types for a given baselined version,i.e.:
9658: --
9659: -- ROLLED_UP
9660: -- USER_ENTERED
9672: IS
9673: SELECT sum(a.TOTAL_PLAN_RAW_COST)
9674: , sum(a.TOTAL_PLAN_BURDENED_COST)
9675: FROM pa_budget_versions v
9676: , pa_resource_assignments a
9677: WHERE v.project_id = p_project_id
9678: AND v.fin_plan_type_id = p_fin_plan_type_id
9679: AND v.current_flag = 'Y'
9680: AND v.budget_version_id = a.budget_version_id
10418: SELECT 'Y'
10419: INTO l_exists
10420: FROM DUAL
10421: WHERE EXISTS (SELECT 'x'
10422: FROM PA_RESOURCE_ASSIGNMENTS a, pa_proj_element_versions pelm
10423: WHERE a.budget_version_id = p_budget_version_id
10424: AND a.task_id = pelm.proj_element_id
10425: AND a.resource_class_code = PA_FP_CONSTANTS_PKG.G_RESOURCE_CLASS_CODE_FIN
10426: AND a.task_id = p_task_id
10430: SELECT 'Y'
10431: INTO l_exists
10432: FROM DUAL
10433: WHERE EXISTS (SELECT 'x'
10434: FROM PA_RESOURCE_ASSIGNMENTS a
10435: WHERE a.budget_version_id = p_budget_version_id
10436: AND a.resource_class_code = PA_FP_CONSTANTS_PKG.G_RESOURCE_CLASS_CODE_FIN
10437: AND a.task_id = p_task_id
10438: AND a.resource_class_flag = 'Y');
10449: /* To determine if a task has resources attached to it as planning element */
10450:
10451: FUNCTION IS_RESOURCE_ATTACHED_TO_TASK(
10452: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
10453: ,p_task_id IN pa_resource_assignments.task_id%TYPE)
10454: --,p_wbs_element_version_id IN pa_resource_assignments.wbs_element_version_id%TYPE)
10455: RETURN VARCHAR2 IS
10456: l_exists VARCHAR2(1);
10457: BEGIN
10450:
10451: FUNCTION IS_RESOURCE_ATTACHED_TO_TASK(
10452: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
10453: ,p_task_id IN pa_resource_assignments.task_id%TYPE)
10454: --,p_wbs_element_version_id IN pa_resource_assignments.wbs_element_version_id%TYPE)
10455: RETURN VARCHAR2 IS
10456: l_exists VARCHAR2(1);
10457: BEGIN
10458: -- sagarwal -- Removed redundant join to pa_budget_versions from select statement below
10459: SELECT 'Y'
10460: INTO l_exists
10461: FROM DUAL
10462: WHERE EXISTS (SELECT 'x'
10463: FROM PA_RESOURCE_ASSIGNMENTS a
10464: WHERE a.budget_version_id = p_budget_version_id
10465: AND a.task_id = p_task_id
10466: --Commented for bug 3793136
10467: --AND a.wbs_element_version_id = p_wbs_element_version_id
12376: SELECT 'Y' INTO l_task_assignments_exist_flag
12377: FROM dual WHERE EXISTS
12378: (SELECT 1
12379: FROM pa_budget_versions bv,
12380: pa_resource_assignments ra
12381: WHERE bv.project_id = p_project_id
12382: AND bv.wp_version_flag = 'Y'
12383: AND ra.budget_version_id = bv.budget_version_id
12384: AND ra.ta_display_flag = 'Y');
13562: API Creation Date : 07-MAY-2007
13563: */
13564:
13565: FUNCTION Get_NP_RA_Description
13566: (p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE Default Null,
13567: p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE Default Null
13568: ) RETURN VARCHAR2
13569: IS
13570: l_description pa_budget_lines.description%TYPE;
13591: API Creation Date : 07-MAY-2007
13592: */
13593:
13594: FUNCTION Get_Change_Reason
13595: (p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE Default Null,
13596: p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE Default Null,
13597: p_time_phased_code IN varchar2
13598: ) RETURN VARCHAR2
13599: IS