[Home] [Help]
38:
39: -- Bug 12384023: Deriving job_id based on assignment start date
40: PROCEDURE get_default_job
41: (
42: p_person_id IN pa_resource_assignments.person_id%TYPE
43: ,p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE
44: ,p_planning_start_date IN pa_resource_assignments.planning_start_date%TYPE
45: ,p_project_role_id IN pa_resource_assignments.project_role_id%TYPE
46: ,x_job_id OUT NOCOPY pa_resource_assignments.job_id%TYPE
39: -- Bug 12384023: Deriving job_id based on assignment start date
40: PROCEDURE get_default_job
41: (
42: p_person_id IN pa_resource_assignments.person_id%TYPE
43: ,p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE
44: ,p_planning_start_date IN pa_resource_assignments.planning_start_date%TYPE
45: ,p_project_role_id IN pa_resource_assignments.project_role_id%TYPE
46: ,x_job_id OUT NOCOPY pa_resource_assignments.job_id%TYPE
47: )
40: PROCEDURE get_default_job
41: (
42: p_person_id IN pa_resource_assignments.person_id%TYPE
43: ,p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE
44: ,p_planning_start_date IN pa_resource_assignments.planning_start_date%TYPE
45: ,p_project_role_id IN pa_resource_assignments.project_role_id%TYPE
46: ,x_job_id OUT NOCOPY pa_resource_assignments.job_id%TYPE
47: )
48: IS
41: (
42: p_person_id IN pa_resource_assignments.person_id%TYPE
43: ,p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE
44: ,p_planning_start_date IN pa_resource_assignments.planning_start_date%TYPE
45: ,p_project_role_id IN pa_resource_assignments.project_role_id%TYPE
46: ,x_job_id OUT NOCOPY pa_resource_assignments.job_id%TYPE
47: )
48: IS
49: l_person_id pa_resource_assignments.person_id%TYPE;
42: p_person_id IN pa_resource_assignments.person_id%TYPE
43: ,p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE
44: ,p_planning_start_date IN pa_resource_assignments.planning_start_date%TYPE
45: ,p_project_role_id IN pa_resource_assignments.project_role_id%TYPE
46: ,x_job_id OUT NOCOPY pa_resource_assignments.job_id%TYPE
47: )
48: IS
49: l_person_id pa_resource_assignments.person_id%TYPE;
50: l_g_miss_num CONSTANT NUMBER := FND_API.G_MISS_NUM;
45: ,p_project_role_id IN pa_resource_assignments.project_role_id%TYPE
46: ,x_job_id OUT NOCOPY pa_resource_assignments.job_id%TYPE
47: )
48: IS
49: l_person_id pa_resource_assignments.person_id%TYPE;
50: l_g_miss_num CONSTANT NUMBER := FND_API.G_MISS_NUM;
51:
52:
53: BEGIN
54: IF p_person_id IS NULL AND p_resource_assignment_id IS NOT NULL THEN
55: BEGIN
56: SELECT DECODE (p_person_id, l_g_miss_num,null,nvl(p_person_id,person_id))
57: INTO l_person_id
58: FROM PA_RESOURCE_ASSIGNMENTS
59: WHERE resource_assignment_id= p_resource_assignment_id;
60:
61: EXCEPTION
62: WHEN NO_DATA_FOUND THEN
170: WHERE pfo.fin_plan_version_id=p_budget_version_id
171: AND pbv.budget_version_id=p_budget_version_id;
172:
173:
174: CURSOR c_data_in_db_csr(c_resource_asg_id pa_resource_assignments.resource_assignment_id%TYPE)
175: IS
176: SELECT sum(quantity) quantity
177: FROM pa_budget_lines
178: WHERE resource_assignment_id = c_resource_asg_id;
258: ,prl.alias resource_name
259: FROM pa_projects_all ppa
260: ,pa_proj_elements pt
261: ,pa_resource_list_members prl
262: ,pa_resource_assignments pra
263: WHERE pra.resource_assignment_id = p_resource_assignment_id
264: AND ppa.project_id = pra.project_id
265: AND pt.proj_element_id(+) = pra.task_id
266: AND prl.resource_list_member_id = pra.resource_list_member_id;
399: l_task_id_tbl.extend(p_resource_assignment_id_tbl.COUNT);
400:
401: --Added the check for FND_API.G_MISS_XXX in the below decodes as the UI can pass input parameters
402: --as FND_API.G_MISS_XXX even if the existing value of the attribute is NULL. NOTE: The input parameter
403: --to update_planning_transactions should be NULL if the corresponding column in pa_resource_assignments
404: --should not be changed and the input parameter should be FND_API.G_MISS_XXX if thhe corresponding column
405: --in pa_resource_assignments should be nulled out.
406: FOR i IN p_resource_assignment_id_tbl.FIRST .. p_resource_assignment_id_tbl.LAST LOOP
407:
401: --Added the check for FND_API.G_MISS_XXX in the below decodes as the UI can pass input parameters
402: --as FND_API.G_MISS_XXX even if the existing value of the attribute is NULL. NOTE: The input parameter
403: --to update_planning_transactions should be NULL if the corresponding column in pa_resource_assignments
404: --should not be changed and the input parameter should be FND_API.G_MISS_XXX if thhe corresponding column
405: --in pa_resource_assignments should be nulled out.
406: FOR i IN p_resource_assignment_id_tbl.FIRST .. p_resource_assignment_id_tbl.LAST LOOP
407:
408: --dbms_output.put_line('6.2 '||p_resource_assignment_id_tbl(i) );
409:
458: SELECT rbs_element_id,
459: txn_accum_header_id
460: INTO x_rbs_element_id_tbl(i),
461: x_txn_accum_header_id_tbl(i)
462: FROM pa_resource_assignments
463: WHERE resource_assignment_id = p_resource_assignment_id_tbl(i);
464:
465: --If Non Null values are passed then the existing values should be compared with the passed values to find out
466: --the changes. The Old Values should be passed in _old_.._tab parameters of calculate API and new values should
549: x_rbs_element_id_tbl(i),
550: l_non_labor_resource_tbl(i),
551: x_txn_accum_header_id_tbl(i),
552: l_task_id_tbl(i)
553: FROM pa_resource_assignments pra,
554: (SELECT pra.resource_assignment_id
555: ,pbl.txn_currency_code
556: FROM pa_budget_lines pbl,
557: pa_resource_assignments pra
553: FROM pa_resource_assignments pra,
554: (SELECT pra.resource_assignment_id
555: ,pbl.txn_currency_code
556: FROM pa_budget_lines pbl,
557: pa_resource_assignments pra
558: WHERE pbl.resource_assignment_id(+)=pra.resource_assignment_id
559: AND pra.resource_assignment_id=p_resource_assignment_id_tbl(i)
560: AND ROWNUM=1) pbl
561: WHERE pra.resource_assignment_id=p_resource_assignment_id_tbl(i);
753: ,l_resource_name
754: FROM pa_projects_all ppa
755: ,pa_proj_elements pt
756: ,pa_resource_list_members prl
757: ,pa_resource_assignments pra
758: WHERE pra.resource_assignment_id = p_resource_assignment_id_tbl(i)
759: AND ppa.project_id = pra.project_id
760: AND pt.proj_element_id(+) = pra.task_id
761: /* Bug fix:4200168 AND prl.resource_list_member_id(+) = pra.resource_list_member_id;*/
1518: l_ra_id_temp_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1519: l_curr_code_temp_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE:= SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
1520: --
1521: l_res_assignment_id_temp_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1522: l_res_assignment_id pa_resource_assignments.resource_assignment_id%TYPE;
1523: l_call_calc_api VARCHAR2(1);
1524:
1525: -- Start of variable to be used in Calculate API Call
1526: l_line_start_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
1553: l_ins_cal_burdened_cost_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1554: l_ins_cal_raw_cost_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1555: l_ins_index NUMBER := 1;
1556:
1557: l_start_date pa_resource_assignments.planning_start_date%TYPE := NULL;
1558: l_compl_date pa_resource_assignments.planning_start_date%TYPE := NULL;
1559: l_start_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
1560: l_compl_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
1561:
1554: l_ins_cal_raw_cost_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1555: l_ins_index NUMBER := 1;
1556:
1557: l_start_date pa_resource_assignments.planning_start_date%TYPE := NULL;
1558: l_compl_date pa_resource_assignments.planning_start_date%TYPE := NULL;
1559: l_start_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
1560: l_compl_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
1561:
1562: l_rlm_id_no_of_rows NUMBER;
1568: l_proj_curr_code pa_projects_all.project_currency_code%TYPE;
1569: l_proj_func_curr_code pa_projects_all.projfunc_currency_code%TYPE;
1570:
1571: -- Bug 3836358 -- ADDED for usage when p_skip_duplicates_flag is passed as Y
1572: l_task_id_temp PA_RESOURCE_ASSIGNMENTS.TASK_ID%TYPE;
1573: l_pji_rollup_required VARCHAR2(1);
1574:
1575: l_fp_cols_rec pa_fp_gen_amount_utils.fp_cols; -- IPM
1576: l_rm_temp_count number;
2762: --All the elements in this table should be set to 'RES_ASSIGNMENT'
2763:
2764: --NOTE : This loop is also used to transfer the l_incur_by_res_type(i) to l_res_type_code_tbl(i) if
2765: --l_incurred_by_res_flag_tbl(i) is Y. Ultimately l_res_type_code_tbl will be used in populationg
2766: --res type code in pa_resource_assignments
2767: l_res_type_code_tbl.EXTEND(l_eligible_rlm_ids_tbl.COUNT-l_res_type_code_tbl.COUNT);
2768: FOR i IN l_eligible_rlm_ids_tbl.FIRST..l_eligible_rlm_ids_tbl.LAST LOOP
2769:
2770: l_txn_src_typ_code_rbs_prm_tbl(i):='RES_ASSIGNMENT';
3997: l_trace_stage := 720;
3998: -- hr_utility.trace('PA_FP_PLAN_TXN_PUB.add_planning_transactions: '||to_char(l_trace_stage));
3999:
4000: -----------------------------------------------------------------------------------------------------
4001: -- BULK INSERTING DATA INTO PA_RESOURCE_ASSIGNMENTS--------------------------------------------------
4002: -----------------------------------------------------------------------------------------------------
4003: -- Loop throught the task element version id table and do a bulk insert in to pa_resource_assignments
4004: -- 1. In the context of WORKPLAN, Loop for p_task_element_version_id_tbl, and insert into pa_resource
4005: -- assignment. l_elligble_rlm_ids will only have rlm id for 'PEOPLE' resource class. so a local
3999:
4000: -----------------------------------------------------------------------------------------------------
4001: -- BULK INSERTING DATA INTO PA_RESOURCE_ASSIGNMENTS--------------------------------------------------
4002: -----------------------------------------------------------------------------------------------------
4003: -- Loop throught the task element version id table and do a bulk insert in to pa_resource_assignments
4004: -- 1. In the context of WORKPLAN, Loop for p_task_element_version_id_tbl, and insert into pa_resource
4005: -- assignment. l_elligble_rlm_ids will only have rlm id for 'PEOPLE' resource class. so a local
4006: -- index l_ppl_index(=1) is used for resource data. Data is inserted in pa_resource_assignments
4007: -- only if quanity dat ais present. this check is done and eligible data to be inserted is fetched
4002: -----------------------------------------------------------------------------------------------------
4003: -- Loop throught the task element version id table and do a bulk insert in to pa_resource_assignments
4004: -- 1. In the context of WORKPLAN, Loop for p_task_element_version_id_tbl, and insert into pa_resource
4005: -- assignment. l_elligble_rlm_ids will only have rlm id for 'PEOPLE' resource class. so a local
4006: -- index l_ppl_index(=1) is used for resource data. Data is inserted in pa_resource_assignments
4007: -- only if quanity dat ais present. this check is done and eligible data to be inserted is fetched
4008: -- in local plsql tables.
4009: -- -- Bug 3749516 removing equipment_quantity reference - refer prev. code in source control for
4010: -- -- reference
4019: -- validation API then that value should be used.
4020: -----------------------------------------------------------------------------------------------------
4021:
4022: IF l_debug_mode = 'Y' THEN
4023: pa_debug.g_err_stage:='BULK INSERTING DATA INTO PA_RESOURCE_ASSIGNMENTS ';
4024: pa_debug.write('PA_FP_PLANNING_TRANSACTION_PUB.add_planning_transactions: ' || g_module_name,pa_debug.g_err_stage,3);
4025: END IF;
4026:
4027: l_call_calc_api := 'N';
4323: END IF;
4324:
4325: IF l_ins_task_elem_version_id_tbl.COUNT > 0 THEN
4326: FORALL i IN l_ins_task_elem_version_id_tbl.FIRST .. l_ins_task_elem_version_id_tbl.LAST
4327: INSERT INTO PA_RESOURCE_ASSIGNMENTS (
4328: RESOURCE_ASSIGNMENT_ID,BUDGET_VERSION_ID,PROJECT_ID,TASK_ID,RESOURCE_LIST_MEMBER_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY
4329: ,LAST_UPDATE_LOGIN,UNIT_OF_MEASURE,TRACK_AS_LABOR_FLAG,STANDARD_BILL_RATE,AVERAGE_BILL_RATE,AVERAGE_COST_RATE
4330: ,PROJECT_ASSIGNMENT_ID,PLAN_ERROR_CODE,TOTAL_PLAN_REVENUE,TOTAL_PLAN_RAW_COST,TOTAL_PLAN_BURDENED_COST,TOTAL_PLAN_QUANTITY
4331: ,AVERAGE_DISCOUNT_PERCENTAGE,TOTAL_BORROWED_REVENUE,TOTAL_TP_REVENUE_IN,TOTAL_TP_REVENUE_OUT,TOTAL_REVENUE_ADJ
4345: ,RATE_EXPENDITURE_ORG_ID,INCUR_BY_RES_CLASS_CODE,INCUR_BY_ROLE_ID
4346: ,PROJECT_ROLE_ID,RESOURCE_CLASS_FLAG,NAMED_ROLE,TXN_ACCUM_HEADER_ID
4347: ,PM_PRODUCT_CODE, PM_RES_ASSIGNMENT_REFERENCE, resource_rate_based_flag)
4348: VALUES (
4349: PA_RESOURCE_ASSIGNMENTS_S.NEXTVAL -- RESOURCE_ASSIGNMENT_ID
4350: ,l_budget_version_id -- BUDGET_VERSION_ID
4351: ,p_project_id -- PROJECT_ID
4352: ,l_ins_proj_element_id_tbl(i) -- TASK_ID
4353: ,l_people_res_class_rlm_id -- RESOURCE_LIST_MEMBER_ID
4604: -- Bug 12384023: Deriving job_id based on assignment start date :end
4605:
4606: --dbms_output.put_line('Inserting for TA');
4607: ---------------------------------------------------------
4608: -- BULK Inserting records into pa_resource_assignments
4609: -- by iterating throught elligle resource list member ids
4610: ---------------------------------------------------------
4611: -----------------------------------------------------------------
4612: -- The Insert Statement below has been modified for changes due to
4614: -- TA context. The Bulk insert is run once once for the same index as of rlm_ids.
4615: -- The Exit Condition below takes care of the insert running only once.
4616: -------------------------------------------------------------------
4617: FORALL j IN l_eligible_rlm_ids_tbl.FIRST .. l_eligible_rlm_ids_tbl.LAST
4618: INSERT INTO PA_RESOURCE_ASSIGNMENTS (
4619: RESOURCE_ASSIGNMENT_ID,BUDGET_VERSION_ID,PROJECT_ID,TASK_ID,RESOURCE_LIST_MEMBER_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY
4620: ,LAST_UPDATE_LOGIN,UNIT_OF_MEASURE,TRACK_AS_LABOR_FLAG,STANDARD_BILL_RATE,AVERAGE_BILL_RATE,AVERAGE_COST_RATE
4621: ,PROJECT_ASSIGNMENT_ID,PLAN_ERROR_CODE,TOTAL_PLAN_REVENUE,TOTAL_PLAN_RAW_COST,TOTAL_PLAN_BURDENED_COST,TOTAL_PLAN_QUANTITY
4622: ,AVERAGE_DISCOUNT_PERCENTAGE,TOTAL_BORROWED_REVENUE,TOTAL_TP_REVENUE_IN,TOTAL_TP_REVENUE_OUT,TOTAL_REVENUE_ADJ
4636: ,RATE_EXPENDITURE_ORG_ID,INCUR_BY_RES_CLASS_CODE,INCUR_BY_ROLE_ID
4637: ,PROJECT_ROLE_ID,RESOURCE_CLASS_FLAG,NAMED_ROLE,TXN_ACCUM_HEADER_ID,UNPLANNED_FLAG
4638: ,PM_PRODUCT_CODE, PM_RES_ASSIGNMENT_REFERENCE,SCHEDULED_DELAY, resource_rate_based_flag,
4639: cbs_element_id) --Bug#16200605
4640: VALUES(PA_RESOURCE_ASSIGNMENTS_S.NEXTVAL -- RESOURCE_ASSIGNMENT_ID
4641: ,l_budget_version_id -- BUDGET_VERSION_ID
4642: ,p_project_id -- PROJECT_ID
4643: ,decode(p_one_to_one_mapping_flag,'Y',l_proj_elem_rlm_tbl(j)
4644: ,l_proj_element_id_tbl(i)) -- TASK_ID
4904: ELSIF ((p_context = PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_BUDGET) OR
4905: (p_context = PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_FORECAST)) THEN
4906:
4907: ---------------------------------------------------------
4908: -- BULK Inserting records into pa_resource_assignments
4909: -- by iterating throught elligle resource list member ids
4910: ---------------------------------------------------------
4911:
4912: ----------------------------------------------------------------
4911:
4912: ----------------------------------------------------------------
4913: /* Notes to Dev - These changes are only done for B/F Context.
4914: If Skip Duplicate Flag is Passed as Y to Add Planning Transaction API then
4915: if a record already exists in PA_RESOURCE_ASSIGNMENTS for a given Planning
4916: Element passed then the particular record is to be skipped whcile doing a
4917: bulk insert into PA_RESOURCE_ASSIGNMENT.
4918: However If quantity/amounts are passed for the planning elment which has
4919: been skipped for Insert. Calculate API would still be called for it.
5155: /* Modified fix added for bug 11718094 */
5156:
5157:
5158: FORALL j IN l_eligible_rlm_ids_tbl.FIRST .. l_eligible_rlm_ids_tbl.LAST SAVE EXCEPTIONS
5159: INSERT INTO PA_RESOURCE_ASSIGNMENTS (
5160: RESOURCE_ASSIGNMENT_ID,BUDGET_VERSION_ID,PROJECT_ID,TASK_ID,RESOURCE_LIST_MEMBER_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY
5161: ,LAST_UPDATE_LOGIN,UNIT_OF_MEASURE,TRACK_AS_LABOR_FLAG,STANDARD_BILL_RATE,AVERAGE_BILL_RATE,AVERAGE_COST_RATE
5162: ,PROJECT_ASSIGNMENT_ID,PLAN_ERROR_CODE,TOTAL_PLAN_REVENUE,TOTAL_PLAN_RAW_COST,TOTAL_PLAN_BURDENED_COST,TOTAL_PLAN_QUANTITY
5163: ,AVERAGE_DISCOUNT_PERCENTAGE,TOTAL_BORROWED_REVENUE,TOTAL_TP_REVENUE_IN,TOTAL_TP_REVENUE_OUT,TOTAL_REVENUE_ADJ
5179: ,PM_PRODUCT_CODE, PM_RES_ASSIGNMENT_REFERENCE, resource_rate_based_flag,
5180: cbs_element_id --Bug#16200605
5181: )
5182: VALUES
5183: ( pa_resource_assignments_s.nextval -- RESOURCE_ASSIGNMENT_ID
5184: ,l_budget_version_id -- BUDGET_VERSION_ID
5185: ,p_project_id -- PROJECT_ID
5186: ,decode(p_one_to_one_mapping_flag,'Y',l_bf_proj_elem_tbl(j)
5187: ,l_proj_element_id_tbl(i)) -- TASK_ID
5420: l_bf_ins_currency_code_tbl(l_index+kk),
5421: l_bf_ins_cost_rate_tbl(l_index+kk),
5422: l_bf_ins_bill_rate_tbl(l_index+kk),
5423: l_bf_ins_burdened_rate_tbl(l_index+kk)
5424: FROM pa_resource_assignments
5425: WHERE project_id=p_project_id
5426: AND budget_version_id=l_budget_version_id
5427: AND task_id =l_bf_proj_elem_tbl(l_temp)
5428: AND resource_list_member_id=l_eligible_rlm_ids_tbl(l_temp)
7029:
7030: --Select the element version id for each ra id
7031: SELECT wbs_element_version_id
7032: INTO l_resource_rec_tbl(i).wbs_element_version_id
7033: FROM pa_resource_assignments
7034: WHERE resource_assignment_id = l_resource_assignment_id_tbl(i);
7035:
7036: IF p_attribute_category_tbl.EXISTS(i) THEN
7037: l_resource_rec_tbl(i).attribute_category := p_attribute_category_tbl(i);
7451: --table
7452: /*
7453: SELECT NVL(pbl.txn_currency_code,l_existing_curr_code)
7454: INTO l_currency_code_tbl(i)
7455: FROM pa_resource_assignments b,
7456: (SELECT pbl.txn_currency_code,
7457: pra.resource_assignment_id
7458: FROM pa_budget_lines pbl,
7459: pa_resource_assignments pra
7455: FROM pa_resource_assignments b,
7456: (SELECT pbl.txn_currency_code,
7457: pra.resource_assignment_id
7458: FROM pa_budget_lines pbl,
7459: pa_resource_assignments pra
7460: WHERE pbl.resource_assignment_id(+)=pra.resource_assignment_id
7461: AND pra.resource_assignment_id=l_resource_assignment_id_tbl(i)
7462: AND ROWNUM=1) pbl
7463: WHERE b.resource_assignment_id=l_resource_assignment_id_tbl(i);*/
7464:
7465: SELECT NVL(rac.txn_currency_code,l_existing_curr_code)
7466: INTO l_currency_code_tbl(i)
7467: FROM pa_resource_asgn_curr rac,
7468: pa_resource_assignments pra
7469: WHERE rac.resource_assignment_id(+)=pra.resource_assignment_id
7470: AND pra.resource_assignment_id=l_resource_assignment_id_tbl(i);
7471:
7472: END LOOP;
7558: --for a resource assignment with fixed spread curve. Hence the value returned should be considered
7559: l_sp_fixed_date_tbl := l_sp_fixed_date_new_tbl;
7560: --dbms_output.put_line(' cccc l_burdened_rate_override_tbl cnt is '||l_burdened_rate_override_tbl.count);
7561: IF l_debug_mode = 'Y' THEN
7562: pa_debug.g_err_stage:='Bulk updating pa_resource_assignments. start '||l_resource_assignment_id_tbl.FIRST ||' end '||l_resource_assignment_id_tbl.LAST;
7563: print_msg(pa_debug.g_err_stage,l_module_name);
7564: END IF;
7565:
7566: --dbms_output.put_line('6');
7563: print_msg(pa_debug.g_err_stage,l_module_name);
7564: END IF;
7565:
7566: --dbms_output.put_line('6');
7567: --Prepare the pl/sql tables for the all columns in pa_resource_assignments to make use of bulk update
7568: FORALL i IN l_resource_assignment_id_tbl.FIRST .. l_resource_assignment_id_tbl.LAST
7569:
7570: UPDATE PA_RESOURCE_ASSIGNMENTS
7571: SET resource_list_member_id = DECODE (l_resource_list_member_id_tbl(i),l_g_miss_num,null,nvl(l_resource_list_member_id_tbl(i),resource_list_member_id))
7566: --dbms_output.put_line('6');
7567: --Prepare the pl/sql tables for the all columns in pa_resource_assignments to make use of bulk update
7568: FORALL i IN l_resource_assignment_id_tbl.FIRST .. l_resource_assignment_id_tbl.LAST
7569:
7570: UPDATE PA_RESOURCE_ASSIGNMENTS
7571: SET resource_list_member_id = DECODE (l_resource_list_member_id_tbl(i),l_g_miss_num,null,nvl(l_resource_list_member_id_tbl(i),resource_list_member_id))
7572: ,last_update_date = sysdate
7573: ,last_updated_by = FND_GLOBAL.user_id
7574: ,last_update_login = FND_GLOBAL.login_id
8114: l_res_class_flag := PA_PROJECT_STRUCTURE_UTILS.get_resource_class_flag(l_project_id);
8115: IF l_res_class_flag = 'N'
8116: THEN
8117: FORALL i IN l_resource_assignment_id_tbl.FIRST .. l_resource_assignment_id_tbl.LAST
8118: UPDATE PA_RESOURCE_ASSIGNMENTS
8119: SET rate_based_flag = 'N',
8120: unit_of_measure = 'DOLLARS'
8121: WHERE resource_assignment_id= l_resource_assignment_id_tbl(i);
8122: END IF;
8742: src_rbc.TXN_BILL_RATE_OVERRIDE
8743: FROM pa_resource_asgn_curr src_rbc,
8744: pa_fp_ra_map_tmp map,
8745: --pa_budget_lines bl
8746: pa_resource_assignments ra
8747: WHERE ra.budget_version_id =l_targ_budget_version_id
8748: -- AND src_rbc.budget_version_id = l_src_budget_version_id
8749: AND map.target_res_assignment_id = ra.resource_assignment_id
8750: AND src_rbc.resource_assignment_id = map.source_res_assignment_id;
8774:
8775: --Bug 4097749. Update the resource assigments created above with the named_role attribute returned by
8776: --the TA validate API
8777: IF l_debug_mode = 'Y' THEN
8778: pa_debug.g_err_stage:='About to update named role/parent assignment id in pa_resource_assignments';
8779: pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
8780: END IF;
8781:
8782: --Bug 4215676 . Modified the update sql for performance issues.
8788: SELECT rlm.resource_class_code, rlm.inventory_item_id,
8789: rlm.expenditure_type
8790: INTO l_resource_class_code, l_inv_item_id, l_exp_type
8791: FROM pa_resource_list_members rlm,
8792: pa_resource_assignments ra
8793: WHERE ra.resource_assignment_id = l_ra_id_tbl(i)
8794: AND ra.resource_list_member_id = rlm.resource_list_member_id;
8795:
8796: IF l_resource_class_code IN ('PEOPLE', 'EQUIPMENT') THEN
8823: END;
8824: END IF;
8825: END LOOP;
8826: FORALL i IN 1..l_ra_id_tbl.COUNT
8827: UPDATE pa_resource_assignments
8828: SET named_role = l_named_role_tbl(i),
8829: parent_assignment_id=NULL,
8830: /* bug fix:5135927 : Added nvl for l_res_rate_base_flag */
8831: resource_rate_based_flag =NVL(l_res_rate_based_flag_tbl(i),'N')--IPM
8832: WHERE parent_assignment_id = l_ra_id_tbl(i)
8833: AND budget_version_id = l_targ_budget_version_id;
8834:
8835: IF l_debug_mode = 'Y' THEN
8836: pa_debug.g_err_stage:='Done with updating named role/parent assignment id in pa_resource_assignments';
8837: pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
8838: END IF;
8839:
8840:
8990:
8991: --Bug 4097749. Update the resource assigments created above with the named_role attribute returned by
8992: --the TA validate API
8993: IF l_debug_mode = 'Y' THEN
8994: pa_debug.g_err_stage:='About to update named role/parent assignment id in pa_resource_assignments';
8995: pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
8996: END IF;
8997:
8998: --Bug 4215676 . Modified the update sql for performance issues.
9003: ---->then in future copy task flows the resource assignments which were are copied now will also be considered
9004: ---->as NEW and copy_budget_lines will try to insert them which will violate the unique constraint
9005: ---->on pa_budget_lines
9006: FORALL i IN 1..l_ra_id_tbl.COUNT
9007: UPDATE pa_resource_assignments
9008: SET named_role = l_named_role_tbl(i),
9009: parent_assignment_id=NULL
9010: WHERE parent_assignment_id = l_ra_id_tbl(i)
9011: AND budget_version_id = l_targ_budget_version_id;
9010: WHERE parent_assignment_id = l_ra_id_tbl(i)
9011: AND budget_version_id = l_targ_budget_version_id;
9012:
9013: IF l_debug_mode = 'Y' THEN
9014: pa_debug.g_err_stage:='Done with updating named role/parent assignment id in pa_resource_assignments';
9015: pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
9016: END IF;
9017:
9018:
9192: src_rbc.TXN_BILL_RATE_OVERRIDE
9193: FROM pa_resource_asgn_curr src_rbc,
9194: pa_fp_ra_map_tmp map,
9195: --pa_budget_lines bl
9196: pa_resource_assignments ra
9197: WHERE ra.budget_version_id =l_targ_budget_version_id
9198: -- AND src_rbc.budget_version_id = l_src_budget_version_id
9199: AND map.target_res_assignment_id = ra.resource_assignment_id
9200: AND src_rbc.resource_assignment_id = map.source_res_assignment_id;
9404: i NUMBER;
9405: l_cntr NUMBER;
9406:
9407: l_currency_code_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
9408: l_task_id PA_RESOURCE_ASSIGNMENTS.TASK_ID%TYPE;
9409: l_rbs_element_id PA_RESOURCE_ASSIGNMENTS.RBS_ELEMENT_ID%TYPE;
9410: l_res_class_code PA_RESOURCE_ASSIGNMENTS.RESOURCE_CLASS_CODE%TYPE;
9411: l_rate_based_flag PA_RESOURCE_ASSIGNMENTS.RATE_BASED_FLAG%TYPE;
9412: l_mode varchar2(12) := null; --Bug 4160258
9405: l_cntr NUMBER;
9406:
9407: l_currency_code_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
9408: l_task_id PA_RESOURCE_ASSIGNMENTS.TASK_ID%TYPE;
9409: l_rbs_element_id PA_RESOURCE_ASSIGNMENTS.RBS_ELEMENT_ID%TYPE;
9410: l_res_class_code PA_RESOURCE_ASSIGNMENTS.RESOURCE_CLASS_CODE%TYPE;
9411: l_rate_based_flag PA_RESOURCE_ASSIGNMENTS.RATE_BASED_FLAG%TYPE;
9412: l_mode varchar2(12) := null; --Bug 4160258
9413:
9406:
9407: l_currency_code_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
9408: l_task_id PA_RESOURCE_ASSIGNMENTS.TASK_ID%TYPE;
9409: l_rbs_element_id PA_RESOURCE_ASSIGNMENTS.RBS_ELEMENT_ID%TYPE;
9410: l_res_class_code PA_RESOURCE_ASSIGNMENTS.RESOURCE_CLASS_CODE%TYPE;
9411: l_rate_based_flag PA_RESOURCE_ASSIGNMENTS.RATE_BASED_FLAG%TYPE;
9412: l_mode varchar2(12) := null; --Bug 4160258
9413:
9414:
9407: l_currency_code_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
9408: l_task_id PA_RESOURCE_ASSIGNMENTS.TASK_ID%TYPE;
9409: l_rbs_element_id PA_RESOURCE_ASSIGNMENTS.RBS_ELEMENT_ID%TYPE;
9410: l_res_class_code PA_RESOURCE_ASSIGNMENTS.RESOURCE_CLASS_CODE%TYPE;
9411: l_rate_based_flag PA_RESOURCE_ASSIGNMENTS.RATE_BASED_FLAG%TYPE;
9412: l_mode varchar2(12) := null; --Bug 4160258
9413:
9414:
9415: l_project_id pa_projects_all.project_id%TYPE; --Bug 4218331
9666: ,pbv.ci_id
9667: INTO l_project_id
9668: ,l_budget_version_id
9669: ,l_ci_id
9670: FROM pa_resource_assignments pra
9671: ,pa_budget_Versions pbv
9672: WHERE pbv.budget_version_id=pra.budget_version_id
9673: AND pbv.wp_version_flag='Y'
9674: AND pra.wbs_element_version_id=p_element_version_id_tbl(l_cntr)
9702: ,pbv.ci_id
9703: INTO l_project_id
9704: ,l_budget_version_id
9705: ,l_ci_id
9706: FROM pa_resource_assignments pra
9707: ,pa_budget_Versions pbv
9708: WHERE pbv.budget_version_id=pra.budget_version_id
9709: AND pra.resource_assignment_id =l_task_assmt_ids_tbl(1);
9710: EXCEPTION
9738: ,pbv.ci_id
9739: INTO l_project_id
9740: ,l_budget_version_id
9741: ,l_ci_id
9742: FROM pa_resource_assignments pra
9743: ,pa_budget_Versions pbv
9744: WHERE pbv.budget_version_id=pra.budget_version_id
9745: AND pra.resource_assignment_id =p_resource_assignment_tbl(1);
9746: EXCEPTION
9874: /*Start bug#16688443 */
9875: l_cbs_element_id_tbl := SYSTEM.pa_num_tbl_type();
9876: l_cbs_element_id_tbl.extend(l_resource_assignment_id_tbl.last);
9877: FOR i IN l_resource_assignment_id_tbl.first..l_resource_assignment_id_tbl.last LOOP
9878: select cbs_element_id into l_cbs_element_id_tbl(i) from pa_resource_assignments where resource_assignment_id = l_resource_assignment_id_tbl (i);
9879: END LOOP; /*End bug#16688443 */
9880:
9881: END IF;
9882:
9885: --that wbs_elememnt_version_id will be populated only for Workplan versions and hence all the checks done in
9886: --the commented SQL are already done in that API
9887: FORALL i IN 1..l_task_assmt_ids_tbl.COUNT
9888: DELETE
9889: FROM pa_resource_assignments pra
9890: WHERE resource_assignment_id=l_task_assmt_ids_tbl(i)
9891: RETURNING
9892: pra.resource_assignment_id,
9893: pra.task_id,
10028: /*Start bug#16688443 */
10029: l_cbs_element_id_tbl := SYSTEM.pa_num_tbl_type();
10030: l_cbs_element_id_tbl.extend(l_resource_assignment_id_tbl.last);
10031: FOR i IN l_resource_assignment_id_tbl.first..l_resource_assignment_id_tbl.last LOOP
10032: select cbs_element_id into l_cbs_element_id_tbl(i) from pa_resource_assignments where resource_assignment_id = l_resource_assignment_id_tbl (i);
10033: END LOOP; /*End bug#16688443 */
10034: END IF;
10035:
10036: IF nvl(p_calling_module,'-99') <> 'PROCESS_RES_CHG_DERV_CALC_PRMS' THEN
10036: IF nvl(p_calling_module,'-99') <> 'PROCESS_RES_CHG_DERV_CALC_PRMS' THEN
10037:
10038: FORALL i IN l_delete_assmt_flag_tbl.first..l_delete_assmt_flag_tbl.last
10039: DELETE
10040: FROM pa_resource_assignments pra
10041: WHERE resource_assignment_id=p_resource_assignment_tbl(i)
10042: AND l_delete_assmt_flag_tbl(i)='Y'
10043: RETURNING
10044: pra.resource_assignment_id,
10120: FORALL i IN p_element_version_id_tbl.first..p_element_version_id_tbl.last
10121: DELETE
10122: FROM pa_budget_lines pbl
10123: WHERE pbl.resource_assignment_id IN (SELECT pra.resource_assignment_id
10124: FROM pa_resource_assignments pra,
10125: pa_budget_versions pbv
10126: WHERE pra.budget_Version_id=pbv.budget_Version_id
10127: AND nvl(pbv.wp_version_flag,'N')=l_wp_version_flag
10128: AND pra.budget_Version_id = l_budget_version_id--Bug#4548675--Bug 4218331
10195: /*Start bug#16688443 */
10196: l_cbs_element_id_tbl := SYSTEM.pa_num_tbl_type();
10197: l_cbs_element_id_tbl.extend(l_resource_assignment_id_tbl.last);
10198: FOR i IN l_resource_assignment_id_tbl.first..l_resource_assignment_id_tbl.last LOOP
10199: select cbs_element_id into l_cbs_element_id_tbl(i) from pa_resource_assignments where resource_assignment_id = l_resource_assignment_id_tbl (i);
10200: END LOOP; /*End bug#16688443 */
10201:
10202: END IF;
10203: End If; --}
10209:
10210: If p_element_version_id_tbl.COUNT > 0 AND l_budget_version_id is NOT NULL Then --Bug fix:5349668 --{
10211: FORALL i IN p_element_version_id_tbl.first..p_element_version_id_tbl.last
10212: DELETE
10213: FROM pa_resource_assignments pra
10214: WHERE pra.wbs_element_version_id=p_element_version_id_tbl(i)
10215: AND EXISTS (SELECT 'X'
10216: FROM pa_budget_Versions pbv
10217: WHERE pbv.budget_version_id=pra.budget_Version_id
10302: INTO l_exists
10303: FROM DUAL
10304: WHERE EXISTS (SELECT 'X'
10305: FROM pa_budget_lines pbl,
10306: pa_resource_assignments pra
10307: WHERE pra.resource_assignment_id=p_resource_assignment_tbl(i)
10308: AND pbl.budget_Version_id=pra.budget_version_id
10309: AND pbl.resource_assignment_id=pra.resource_assignment_id);
10310: EXCEPTION
10420: /*Start bug#16688443 */
10421: l_cbs_element_id_tbl := SYSTEM.pa_num_tbl_type();
10422: l_cbs_element_id_tbl.extend(l_resource_assignment_id_tbl.last);
10423: FOR i IN l_resource_assignment_id_tbl.first..l_resource_assignment_id_tbl.last LOOP
10424: select cbs_element_id into l_cbs_element_id_tbl(i) from pa_resource_assignments where resource_assignment_id = l_resource_assignment_id_tbl (i);
10425: END LOOP; /*End bug#16688443 */
10426:
10427: END IF;
10428:
10434: END IF;
10435:
10436: FORALL i IN p_resource_assignment_tbl.first..p_resource_assignment_tbl.last
10437: DELETE
10438: FROM pa_resource_assignments pra
10439: WHERE pra.resource_assignment_id=p_resource_assignment_tbl(i)
10440: AND (l_currency_code_tbl(i) IS NULL
10441: OR
10442: NOT EXISTS ( SELECT 'EXISTS'
10605: --Prepare the pl/sql tables for task id, rbs element id , resource class code and rate based flag.
10606: --These pl/sql tables should be same in length to the pl/sql tables prepared while deleting the budget
10607: --lines. This can be done by looping thru the l_resource_assignment_id_tbl and looking for a matching
10608: --ra id in l_ra_id_in_pra_tbl If a matching ra id is not found in l_ra_id_in_pra_tbl then data
10609: -- is fetched directly from pa_resource_assignments for ra id in l_resource_assignment_id_tbl.
10610: l_ra_index:=1;
10611: l_counter:=0;--This is used just to keep track of the length of the pl/sql tables being prepared
10612:
10613: l_task_id_tbl.EXTEND(l_resource_assignment_id_tbl.last);
10650: INTO l_task_id,
10651: l_rbs_element_id,
10652: l_res_class_code,
10653: l_rate_based_flag
10654: FROM PA_RESOURCE_ASSIGNMENTS
10655: WHERE RESOURCE_ASSIGNMENT_ID = l_resource_assignment_id_tbl(i);
10656:
10657: EXIT; --Exit LOOP
10658:
11115: Purpose: This is a private api in the package. This API will
11116: validate the task data passed to the
11117: update_planning_transactions api This API checks
11118: for the existence of the element version id passed
11119: in pa_resource_assignments. If some of the element
11120: version Ids are not there then it call
11121: add_planning_transactions API to create records in
11122: pa_resource_assignments. This API will be called
11123: only when the context is WORKPLAN
11118: for the existence of the element version id passed
11119: in pa_resource_assignments. If some of the element
11120: version Ids are not there then it call
11121: add_planning_transactions API to create records in
11122: pa_resource_assignments. This API will be called
11123: only when the context is WORKPLAN
11124: =======================================================================*/
11125: /*******************************************************************************************************
11126: As part of Bug 3749516 All References to Equipment Effort or Equip Resource Class has been removed in
11184: l_raw_cost_tbl_tmp SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
11185: l_burdened_cost_tbl_tmp SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
11186:
11187:
11188: cursor c_res_assignment_id(c_wbs_element_version_id pa_resource_assignments.wbs_element_version_id%TYPE) IS
11189: SELECT resource_assignment_id,resource_class_code
11190: FROM pa_resource_assignments
11191: WHERE wbs_element_version_id = c_wbs_element_version_id
11192: AND ta_display_flag = 'N' -- Bug 3749516
11186:
11187:
11188: cursor c_res_assignment_id(c_wbs_element_version_id pa_resource_assignments.wbs_element_version_id%TYPE) IS
11189: SELECT resource_assignment_id,resource_class_code
11190: FROM pa_resource_assignments
11191: WHERE wbs_element_version_id = c_wbs_element_version_id
11192: AND ta_display_flag = 'N' -- Bug 3749516
11193: AND resource_class_code in (PA_FP_CONSTANTS_PKG.G_RESOURCE_CLASS_CODE_PPL);
11194:
11246: --------------------------------------------------------------------------
11247: -- Logic manifested below ------------------------------------------------
11248: -- -----------------------------------------------------------------------
11249: -- For each record of element version id in input parameter
11250: -- p_element_version_id_tbl, we scan through pa_resource_assignments table
11251: -- and bulk fetch the resource_class_code and resource_assignment_id into
11252: -- local PLSql tables. Now we have a inner loop which we run for each of
11253: -- the resouce_assignment_id fetched corresponding to the elem_ver_id of
11254: -- the parent loop. In the inner loop we populate the corresponding out
11255: -- param tables - x_resource_assignment_id_tbl,x_element_version_id_tbl
11256: -- ,x_planning_end_date_tbl, x_planning_start_date_tbl and
11257: -- x_planned_effort_tbl.
11258: --
11259: -- In case if there no records are retrieved in pa_resource_assignments
11260: -- for any element version id then we populate a separate set of tables
11261: -- from the corresponding IN parameters as l_element_version_id_tbl,
11262: -- l_planning_start_date_tbl,l_planning_end_date_tbl,
11263: -- l_planned_people_effort_tbl.
11773: pa_debug.write('Refresh_Plan_Txns: ' || g_module_name,pa_debug.g_err_stage,3);
11774: END IF;
11775:
11776: DELETE
11777: FROM pa_resource_assignments
11778: WHERE budget_version_id = p_budget_version_id
11779: -- IPM changes Bug 5003827 Issue 22
11780: RETURNING resource_assignment_id BULK COLLECT INTO l_delete_ra_id_tbl;
11781: /* Bug 5754758 - Commenting the code, as the maintain_data is now called in version level mode to delete the RACs.
11896: -- Bug 3658232 added null handling for resource class flag
11897: --Bug 13701718 CBS related Chnages start here
11898: l_res_class_flag := PA_FP_GEN_AMOUNT_UTILS.get_src_resource_class_flag(p_budget_version_id);
11899: IF nvl(l_res_class_flag,'Y') = 'N' THEN
11900: DELETE FROM pa_resource_assignments
11901: WHERE budget_version_id = p_budget_version_id
11902: RETURNING resource_assignment_id BULK COLLECT INTO l_delete_ra_id_tbl;
11903: ELSE
11904: DELETE FROM pa_resource_assignments
11900: DELETE FROM pa_resource_assignments
11901: WHERE budget_version_id = p_budget_version_id
11902: RETURNING resource_assignment_id BULK COLLECT INTO l_delete_ra_id_tbl;
11903: ELSE
11904: DELETE FROM pa_resource_assignments
11905: WHERE budget_version_id = p_budget_version_id
11906: AND
11907: NOT (resource_class_code = 'FINANCIAL_ELEMENTS' AND nvl(resource_class_flag,'N') = 'Y')
11908: -- IPM changes Bug 5003827 Issue 22
11950: pa_debug.g_err_stage:='Updaing res assignments with new FINANCIAL ELEMENTS rlmid : ' || l_fin_res_class_rlm_id;
11951: pa_debug.write('Refresh_Plan_Txns: ' || g_module_name,pa_debug.g_err_stage,3);
11952: END IF;
11953:
11954: UPDATE pa_resource_assignments
11955: SET resource_list_member_id = l_fin_res_class_rlm_id
11956: WHERE budget_version_id = p_budget_version_id;
11957: --AND resource_class_code = 'FINANCIAL_ELEMENTS' --Bug 4200168. RL/PL change both can not happen at the same time.
11958: --AND resource_class_flag = 'Y';
12005: -- END of IPM changes Bug 5003827 Issue 22
12006:
12007:
12008: -- Calling the rollup api to correct the amounts related data in
12009: -- pa_budget_versions and pa_resource_assignments for the entire version
12010: PA_FP_ROLLUP_PKG.rollup_budget_version
12011: (p_budget_version_id => p_budget_version_id
12012: ,p_entire_version => 'Y'
12013: ,x_return_status => l_return_status
12215: where v.parent_structure_version_id = c_parent_structure_version_id
12216: and v.financial_task_flag = 'Y' -- raja bug 3690418
12217: and v.task_level = 'L'
12218: and not exists (select 'x'
12219: from pa_resource_assignments pra
12220: where pra.budget_version_id = P_budget_version_id
12221: and pra.task_id = v.task_id
12222: and p_calling_context = 'SELECT_TASKS');
12223:
12236: and a.project_id = b.project_id
12237: and a.task_id = b.proj_element_id
12238: and a.task_id = a.top_task_id
12239: and not exists (select 'x'
12240: from pa_resource_assignments pra
12241: where pra.budget_version_id = P_budget_version_id
12242: and pra.task_id = a.task_id
12243: and p_calling_context = 'SELECT_TASKS');
12244:
12729: SELECT wbs_element_version_id
12730: ,name
12731: ,element_number
12732: ,resource_assignment_id
12733: FROM pa_resource_assignments pra
12734: ,pa_proj_elements ppe
12735: WHERE pra.project_id = p_project_id
12736: AND pra.budget_version_id = c_budget_version_id
12737: AND pra.ta_display_flag = 'Y'
12742: SELECT wbs_element_version_id
12743: ,name
12744: ,element_number
12745: ,resource_assignment_id
12746: FROM pa_resource_assignments pra
12747: ,pa_proj_elements ppe
12748: WHERE pra.project_id = p_project_id
12749: AND pra.budget_version_id = c_budget_version_id
12750: --AND pra.ta_display_flag = 'Y'
12907:
12908: FORALL i IN l_pub_budget_version_id_tbl.first .. l_pub_budget_version_id_tbl.last
12909: --Fix for bug#7279771, uncommented the code comment done earlier for resource_class_code,
12910: --resource_class_flag,ta_display_flag
12911: UPDATE pa_resource_assignments
12912: SET resource_list_member_id = l_people_res_class_rlm_id
12913: WHERE budget_version_id = l_pub_budget_version_id_tbl(i)
12914: /* The only records present in pa_resource_assignments for published versions with resource
12915: list NONE would fall under the below cateogry. Not including them as part of select as they
12910: --resource_class_flag,ta_display_flag
12911: UPDATE pa_resource_assignments
12912: SET resource_list_member_id = l_people_res_class_rlm_id
12913: WHERE budget_version_id = l_pub_budget_version_id_tbl(i)
12914: /* The only records present in pa_resource_assignments for published versions with resource
12915: list NONE would fall under the below cateogry. Not including them as part of select as they
12916: dont add any value to performance interms of better index usage. Retaining them in the comment
12917: for understanding purpose */
12918: AND resource_class_code = 'PEOPLE'
13127: pa_debug.g_err_stage:='Updaing res assignments with new FINANCIAL ELEMENTS rlmid : ' || l_fin_res_class_rlm_id;
13128: pa_debug.write('REFRESH_WP_SETTINGS: ' || g_module_name,pa_debug.g_err_stage,3);
13129: END IF;
13130:
13131: UPDATE pa_resource_assignments
13132: SET resource_list_member_id = l_people_res_class_rlm_id
13133: WHERE budget_version_id = l_budget_version_id_tbl(i)
13134: AND resource_class_code = 'PEOPLE'
13135: AND resource_class_flag = 'Y';
13158:
13159: -- Call calculate only if there are some planning transactions to be processed
13160: SELECT count(*)
13161: INTO l_res_assignment_count
13162: FROM pa_resource_assignments
13163: WHERE budget_version_id = l_budget_version_id_tbl(i);
13164:
13165: IF l_res_assignment_count > 0 THEN
13166: PA_FP_CALC_PLAN_PKG.calculate(
13309: IF nvl(l_txn_source_id_tbl.last,0) >= 1 THEN
13310: -- Update resource assignments data for the version
13311: -- Bug 3641252 changed the index from i to j
13312: FORALL j IN l_txn_source_id_tbl.first .. l_txn_source_id_tbl.last
13313: UPDATE pa_resource_assignments
13314: SET rbs_element_id = l_rbs_element_id_tbl(j)
13315: ,txn_accum_header_id = l_txn_accum_header_id_tbl(j)
13316: ,record_version_number = record_version_number + 1
13317: ,last_update_date = SYSDATE
13322: END IF;
13323: ELSE -- rbs version id is null
13324:
13325: -- Update all the resource assigments with null for rbs _element_id
13326: UPDATE pa_resource_assignments
13327: SET rbs_element_id = null
13328: ,txn_accum_header_id = null
13329: ,record_version_number = record_version_number + 1
13330: ,last_update_date = SYSDATE
13705: IF nvl(l_txn_source_id_tbl.last,0) >= 1 THEN
13706: -- Update resource assignments data for the version
13707: -- Bug 3641252 changed the index from i to j
13708: FORALL j IN l_txn_source_id_tbl.first .. l_txn_source_id_tbl.last
13709: UPDATE pa_resource_assignments
13710: SET rbs_element_id = l_rbs_element_id_tbl(j)
13711: ,txn_accum_header_id = l_txn_accum_header_id_tbl(j)
13712: ,record_version_number = record_version_number + 1
13713: ,last_update_date = SYSDATE
13718: END IF;
13719: ELSE -- rbs version id is null
13720:
13721: -- Update all the resource assigments with null for rbs _element_id
13722: UPDATE pa_resource_assignments
13723: SET rbs_element_id = null
13724: ,txn_accum_header_id = null
13725: ,record_version_number = record_version_number + 1
13726: ,last_update_date = SYSDATE
13806: END IF;
13807: RAISE;
13808: END Refresh_rbs_for_versions;
13809:
13810: --This function returns 'N' if a record already exists in pa_resource_assignments
13811: --for a given budget version id, task id and resource list member id
13812: --Returns 'Y' if the record is not already there
13813: FUNCTION DUP_EXISTS
13814: ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
13822:
13823: BEGIN
13824: SELECT 'Y'
13825: INTO l_dup_exists
13826: FROM pa_resource_assignments
13827: WHERE task_id=p_task_id
13828: AND resource_list_member_id=p_resource_list_member_id
13829: AND budget_version_id=p_budget_version_id
13830: AND project_assignment_id=-1