[Home] [Help]
114: WHERE pfo.fin_plan_version_id=p_budget_version_id
115: AND pbv.budget_version_id=p_budget_version_id;
116:
117:
118: CURSOR c_data_in_db_csr(c_resource_asg_id pa_resource_assignments.resource_assignment_id%TYPE)
119: IS
120: SELECT sum(quantity) quantity
121: FROM pa_budget_lines
122: WHERE resource_assignment_id = c_resource_asg_id;
202: ,prl.alias resource_name
203: FROM pa_projects_all ppa
204: ,pa_proj_elements pt
205: ,pa_resource_list_members prl
206: ,pa_resource_assignments pra
207: WHERE pra.resource_assignment_id = p_resource_assignment_id
208: AND ppa.project_id = pra.project_id
209: AND pt.proj_element_id(+) = pra.task_id
210: AND prl.resource_list_member_id = pra.resource_list_member_id;
343: l_task_id_tbl.extend(p_resource_assignment_id_tbl.COUNT);
344:
345: --Added the check for FND_API.G_MISS_XXX in the below decodes as the UI can pass input parameters
346: --as FND_API.G_MISS_XXX even if the existing value of the attribute is NULL. NOTE: The input parameter
347: --to update_planning_transactions should be NULL if the corresponding column in pa_resource_assignments
348: --should not be changed and the input parameter should be FND_API.G_MISS_XXX if thhe corresponding column
349: --in pa_resource_assignments should be nulled out.
350: FOR i IN p_resource_assignment_id_tbl.FIRST .. p_resource_assignment_id_tbl.LAST LOOP
351:
345: --Added the check for FND_API.G_MISS_XXX in the below decodes as the UI can pass input parameters
346: --as FND_API.G_MISS_XXX even if the existing value of the attribute is NULL. NOTE: The input parameter
347: --to update_planning_transactions should be NULL if the corresponding column in pa_resource_assignments
348: --should not be changed and the input parameter should be FND_API.G_MISS_XXX if thhe corresponding column
349: --in pa_resource_assignments should be nulled out.
350: FOR i IN p_resource_assignment_id_tbl.FIRST .. p_resource_assignment_id_tbl.LAST LOOP
351:
352: --dbms_output.put_line('6.2 '||p_resource_assignment_id_tbl(i) );
353:
402: SELECT rbs_element_id,
403: txn_accum_header_id
404: INTO x_rbs_element_id_tbl(i),
405: x_txn_accum_header_id_tbl(i)
406: FROM pa_resource_assignments
407: WHERE resource_assignment_id = p_resource_assignment_id_tbl(i);
408:
409: --If Non Null values are passed then the existing values should be compared with the passed values to find out
410: --the changes. The Old Values should be passed in _old_.._tab parameters of calculate API and new values should
493: x_rbs_element_id_tbl(i),
494: l_non_labor_resource_tbl(i),
495: x_txn_accum_header_id_tbl(i),
496: l_task_id_tbl(i)
497: FROM pa_resource_assignments pra,
498: (SELECT pra.resource_assignment_id
499: ,pbl.txn_currency_code
500: FROM pa_budget_lines pbl,
501: pa_resource_assignments pra
497: FROM pa_resource_assignments pra,
498: (SELECT pra.resource_assignment_id
499: ,pbl.txn_currency_code
500: FROM pa_budget_lines pbl,
501: pa_resource_assignments pra
502: WHERE pbl.resource_assignment_id(+)=pra.resource_assignment_id
503: AND pra.resource_assignment_id=p_resource_assignment_id_tbl(i)
504: AND ROWNUM=1) pbl
505: WHERE pra.resource_assignment_id=p_resource_assignment_id_tbl(i);
697: ,l_resource_name
698: FROM pa_projects_all ppa
699: ,pa_proj_elements pt
700: ,pa_resource_list_members prl
701: ,pa_resource_assignments pra
702: WHERE pra.resource_assignment_id = p_resource_assignment_id_tbl(i)
703: AND ppa.project_id = pra.project_id
704: AND pt.proj_element_id(+) = pra.task_id
705: /* Bug fix:4200168 AND prl.resource_list_member_id(+) = pra.resource_list_member_id;*/
1457: l_ra_id_temp_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1458: l_curr_code_temp_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE:= SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
1459: --
1460: l_res_assignment_id_temp_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1461: l_res_assignment_id pa_resource_assignments.resource_assignment_id%TYPE;
1462: l_call_calc_api VARCHAR2(1);
1463:
1464: -- Start of variable to be used in Calculate API Call
1465: l_line_start_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
1490: l_ins_cal_burdened_cost_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1491: l_ins_cal_raw_cost_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1492: l_ins_index NUMBER := 1;
1493:
1494: l_start_date pa_resource_assignments.planning_start_date%TYPE := NULL;
1495: l_compl_date pa_resource_assignments.planning_start_date%TYPE := NULL;
1496: l_start_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
1497: l_compl_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
1498:
1491: l_ins_cal_raw_cost_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1492: l_ins_index NUMBER := 1;
1493:
1494: l_start_date pa_resource_assignments.planning_start_date%TYPE := NULL;
1495: l_compl_date pa_resource_assignments.planning_start_date%TYPE := NULL;
1496: l_start_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
1497: l_compl_date_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
1498:
1499: l_rlm_id_no_of_rows NUMBER;
1505: l_proj_curr_code pa_projects_all.project_currency_code%TYPE;
1506: l_proj_func_curr_code pa_projects_all.projfunc_currency_code%TYPE;
1507:
1508: -- Bug 3836358 -- ADDED for usage when p_skip_duplicates_flag is passed as Y
1509: l_task_id_temp PA_RESOURCE_ASSIGNMENTS.TASK_ID%TYPE;
1510: l_pji_rollup_required VARCHAR2(1);
1511:
1512: l_fp_cols_rec pa_fp_gen_amount_utils.fp_cols; -- IPM
1513: l_rm_temp_count number;
2521: --All the elements in this table should be set to 'RES_ASSIGNMENT'
2522:
2523: --NOTE : This loop is also used to transfer the l_incur_by_res_type(i) to l_res_type_code_tbl(i) if
2524: --l_incurred_by_res_flag_tbl(i) is Y. Ultimately l_res_type_code_tbl will be used in populationg
2525: --res type code in pa_resource_assignments
2526: l_res_type_code_tbl.EXTEND(l_eligible_rlm_ids_tbl.COUNT-l_res_type_code_tbl.COUNT);
2527: FOR i IN l_eligible_rlm_ids_tbl.FIRST..l_eligible_rlm_ids_tbl.LAST LOOP
2528:
2529: l_txn_src_typ_code_rbs_prm_tbl(i):='RES_ASSIGNMENT';
3697: l_trace_stage := 720;
3698: -- hr_utility.trace('PA_FP_PLAN_TXN_PUB.add_planning_transactions: '||to_char(l_trace_stage));
3699:
3700: -----------------------------------------------------------------------------------------------------
3701: -- BULK INSERTING DATA INTO PA_RESOURCE_ASSIGNMENTS--------------------------------------------------
3702: -----------------------------------------------------------------------------------------------------
3703: -- Loop throught the task element version id table and do a bulk insert in to pa_resource_assignments
3704: -- 1. In the context of WORKPLAN, Loop for p_task_element_version_id_tbl, and insert into pa_resource
3705: -- assignment. l_elligble_rlm_ids will only have rlm id for 'PEOPLE' resource class. so a local
3699:
3700: -----------------------------------------------------------------------------------------------------
3701: -- BULK INSERTING DATA INTO PA_RESOURCE_ASSIGNMENTS--------------------------------------------------
3702: -----------------------------------------------------------------------------------------------------
3703: -- Loop throught the task element version id table and do a bulk insert in to pa_resource_assignments
3704: -- 1. In the context of WORKPLAN, Loop for p_task_element_version_id_tbl, and insert into pa_resource
3705: -- assignment. l_elligble_rlm_ids will only have rlm id for 'PEOPLE' resource class. so a local
3706: -- index l_ppl_index(=1) is used for resource data. Data is inserted in pa_resource_assignments
3707: -- only if quanity dat ais present. this check is done and eligible data to be inserted is fetched
3702: -----------------------------------------------------------------------------------------------------
3703: -- Loop throught the task element version id table and do a bulk insert in to pa_resource_assignments
3704: -- 1. In the context of WORKPLAN, Loop for p_task_element_version_id_tbl, and insert into pa_resource
3705: -- assignment. l_elligble_rlm_ids will only have rlm id for 'PEOPLE' resource class. so a local
3706: -- index l_ppl_index(=1) is used for resource data. Data is inserted in pa_resource_assignments
3707: -- only if quanity dat ais present. this check is done and eligible data to be inserted is fetched
3708: -- in local plsql tables.
3709: -- -- Bug 3749516 removing equipment_quantity reference - refer prev. code in source control for
3710: -- -- reference
3719: -- validation API then that value should be used.
3720: -----------------------------------------------------------------------------------------------------
3721:
3722: IF l_debug_mode = 'Y' THEN
3723: pa_debug.g_err_stage:='BULK INSERTING DATA INTO PA_RESOURCE_ASSIGNMENTS ';
3724: pa_debug.write('PA_FP_PLANNING_TRANSACTION_PUB.add_planning_transactions: ' || g_module_name,pa_debug.g_err_stage,3);
3725: END IF;
3726:
3727: l_call_calc_api := 'N';
3967: END IF;
3968:
3969: IF l_ins_task_elem_version_id_tbl.COUNT > 0 THEN
3970: FORALL i IN l_ins_task_elem_version_id_tbl.FIRST .. l_ins_task_elem_version_id_tbl.LAST
3971: INSERT INTO PA_RESOURCE_ASSIGNMENTS (
3972: RESOURCE_ASSIGNMENT_ID,BUDGET_VERSION_ID,PROJECT_ID,TASK_ID,RESOURCE_LIST_MEMBER_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY
3973: ,LAST_UPDATE_LOGIN,UNIT_OF_MEASURE,TRACK_AS_LABOR_FLAG,STANDARD_BILL_RATE,AVERAGE_BILL_RATE,AVERAGE_COST_RATE
3974: ,PROJECT_ASSIGNMENT_ID,PLAN_ERROR_CODE,TOTAL_PLAN_REVENUE,TOTAL_PLAN_RAW_COST,TOTAL_PLAN_BURDENED_COST,TOTAL_PLAN_QUANTITY
3975: ,AVERAGE_DISCOUNT_PERCENTAGE,TOTAL_BORROWED_REVENUE,TOTAL_TP_REVENUE_IN,TOTAL_TP_REVENUE_OUT,TOTAL_REVENUE_ADJ
3989: ,RATE_EXPENDITURE_ORG_ID,INCUR_BY_RES_CLASS_CODE,INCUR_BY_ROLE_ID
3990: ,PROJECT_ROLE_ID,RESOURCE_CLASS_FLAG,NAMED_ROLE,TXN_ACCUM_HEADER_ID
3991: ,PM_PRODUCT_CODE, PM_RES_ASSIGNMENT_REFERENCE, resource_rate_based_flag)
3992: VALUES (
3993: PA_RESOURCE_ASSIGNMENTS_S.NEXTVAL -- RESOURCE_ASSIGNMENT_ID
3994: ,l_budget_version_id -- BUDGET_VERSION_ID
3995: ,p_project_id -- PROJECT_ID
3996: ,l_ins_proj_element_id_tbl(i) -- TASK_ID
3997: ,l_people_res_class_rlm_id -- RESOURCE_LIST_MEMBER_ID
4209: pa_debug.write('PA_FP_PLANNING_TRANSACTION_PUB.add_planning_transactions: ' || g_module_name,pa_debug.g_err_stage,3);
4210: END IF;
4211: --dbms_output.put_line('Inserting for TA');
4212: ---------------------------------------------------------
4213: -- BULK Inserting records into pa_resource_assignments
4214: -- by iterating throught elligle resource list member ids
4215: ---------------------------------------------------------
4216: -----------------------------------------------------------------
4217: -- The Insert Statement below has been modified for changes due to
4219: -- TA context. The Bulk insert is run once once for the same index as of rlm_ids.
4220: -- The Exit Condition below takes care of the insert running only once.
4221: -------------------------------------------------------------------
4222: FORALL j IN l_eligible_rlm_ids_tbl.FIRST .. l_eligible_rlm_ids_tbl.LAST
4223: INSERT INTO PA_RESOURCE_ASSIGNMENTS (
4224: RESOURCE_ASSIGNMENT_ID,BUDGET_VERSION_ID,PROJECT_ID,TASK_ID,RESOURCE_LIST_MEMBER_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY
4225: ,LAST_UPDATE_LOGIN,UNIT_OF_MEASURE,TRACK_AS_LABOR_FLAG,STANDARD_BILL_RATE,AVERAGE_BILL_RATE,AVERAGE_COST_RATE
4226: ,PROJECT_ASSIGNMENT_ID,PLAN_ERROR_CODE,TOTAL_PLAN_REVENUE,TOTAL_PLAN_RAW_COST,TOTAL_PLAN_BURDENED_COST,TOTAL_PLAN_QUANTITY
4227: ,AVERAGE_DISCOUNT_PERCENTAGE,TOTAL_BORROWED_REVENUE,TOTAL_TP_REVENUE_IN,TOTAL_TP_REVENUE_OUT,TOTAL_REVENUE_ADJ
4240: ,SP_FIXED_DATE,PERSON_TYPE_CODE,RATE_BASED_FLAG,USE_TASK_SCHEDULE_FLAG,RATE_EXP_FUNC_CURR_CODE
4241: ,RATE_EXPENDITURE_ORG_ID,INCUR_BY_RES_CLASS_CODE,INCUR_BY_ROLE_ID
4242: ,PROJECT_ROLE_ID,RESOURCE_CLASS_FLAG,NAMED_ROLE,TXN_ACCUM_HEADER_ID,UNPLANNED_FLAG
4243: ,PM_PRODUCT_CODE, PM_RES_ASSIGNMENT_REFERENCE,SCHEDULED_DELAY, resource_rate_based_flag)
4244: VALUES(PA_RESOURCE_ASSIGNMENTS_S.NEXTVAL -- RESOURCE_ASSIGNMENT_ID
4245: ,l_budget_version_id -- BUDGET_VERSION_ID
4246: ,p_project_id -- PROJECT_ID
4247: ,decode(p_one_to_one_mapping_flag,'Y',l_proj_elem_rlm_tbl(j)
4248: ,l_proj_element_id_tbl(i)) -- TASK_ID
4483: ELSIF ((p_context = PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_BUDGET) OR
4484: (p_context = PA_FP_CONSTANTS_PKG.G_CALLING_MODULE_FORECAST)) THEN
4485:
4486: ---------------------------------------------------------
4487: -- BULK Inserting records into pa_resource_assignments
4488: -- by iterating throught elligle resource list member ids
4489: ---------------------------------------------------------
4490:
4491: ----------------------------------------------------------------
4490:
4491: ----------------------------------------------------------------
4492: /* Notes to Dev - These changes are only done for B/F Context.
4493: If Skip Duplicate Flag is Passed as Y to Add Planning Transaction API then
4494: if a record already exists in PA_RESOURCE_ASSIGNMENTS for a given Planning
4495: Element passed then the particular record is to be skipped whcile doing a
4496: bulk insert into PA_RESOURCE_ASSIGNMENT.
4497: However If quantity/amounts are passed for the planning elment which has
4498: been skipped for Insert. Calculate API would still be called for it.
4569: END IF;
4570:
4571:
4572: FORALL j IN l_eligible_rlm_ids_tbl.FIRST .. l_eligible_rlm_ids_tbl.LAST SAVE EXCEPTIONS
4573: INSERT INTO PA_RESOURCE_ASSIGNMENTS (
4574: RESOURCE_ASSIGNMENT_ID,BUDGET_VERSION_ID,PROJECT_ID,TASK_ID,RESOURCE_LIST_MEMBER_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY
4575: ,LAST_UPDATE_LOGIN,UNIT_OF_MEASURE,TRACK_AS_LABOR_FLAG,STANDARD_BILL_RATE,AVERAGE_BILL_RATE,AVERAGE_COST_RATE
4576: ,PROJECT_ASSIGNMENT_ID,PLAN_ERROR_CODE,TOTAL_PLAN_REVENUE,TOTAL_PLAN_RAW_COST,TOTAL_PLAN_BURDENED_COST,TOTAL_PLAN_QUANTITY
4577: ,AVERAGE_DISCOUNT_PERCENTAGE,TOTAL_BORROWED_REVENUE,TOTAL_TP_REVENUE_IN,TOTAL_TP_REVENUE_OUT,TOTAL_REVENUE_ADJ
4591: ,RATE_EXPENDITURE_ORG_ID,INCUR_BY_RES_CLASS_CODE,INCUR_BY_ROLE_ID
4592: ,PROJECT_ROLE_ID,RESOURCE_CLASS_FLAG,NAMED_ROLE,TXN_ACCUM_HEADER_ID
4593: ,PM_PRODUCT_CODE, PM_RES_ASSIGNMENT_REFERENCE, resource_rate_based_flag)
4594: VALUES
4595: ( pa_resource_assignments_s.nextval -- RESOURCE_ASSIGNMENT_ID
4596: ,l_budget_version_id -- BUDGET_VERSION_ID
4597: ,p_project_id -- PROJECT_ID
4598: ,decode(p_one_to_one_mapping_flag,'Y',l_bf_proj_elem_tbl(j)
4599: ,l_proj_element_id_tbl(i)) -- TASK_ID
4831: l_bf_ins_currency_code_tbl(l_index+kk),
4832: l_bf_ins_cost_rate_tbl(l_index+kk),
4833: l_bf_ins_bill_rate_tbl(l_index+kk),
4834: l_bf_ins_burdened_rate_tbl(l_index+kk)
4835: FROM pa_resource_assignments
4836: WHERE project_id=p_project_id
4837: AND budget_version_id=l_budget_version_id
4838: AND task_id =l_bf_proj_elem_tbl(l_temp)
4839: AND resource_list_member_id=l_eligible_rlm_ids_tbl(l_temp)
6359:
6360: --Select the element version id for each ra id
6361: SELECT wbs_element_version_id
6362: INTO l_resource_rec_tbl(i).wbs_element_version_id
6363: FROM pa_resource_assignments
6364: WHERE resource_assignment_id = l_resource_assignment_id_tbl(i);
6365:
6366: IF p_attribute_category_tbl.EXISTS(i) THEN
6367: l_resource_rec_tbl(i).attribute_category := p_attribute_category_tbl(i);
6762: --table
6763: /*
6764: SELECT NVL(pbl.txn_currency_code,l_existing_curr_code)
6765: INTO l_currency_code_tbl(i)
6766: FROM pa_resource_assignments b,
6767: (SELECT pbl.txn_currency_code,
6768: pra.resource_assignment_id
6769: FROM pa_budget_lines pbl,
6770: pa_resource_assignments pra
6766: FROM pa_resource_assignments b,
6767: (SELECT pbl.txn_currency_code,
6768: pra.resource_assignment_id
6769: FROM pa_budget_lines pbl,
6770: pa_resource_assignments pra
6771: WHERE pbl.resource_assignment_id(+)=pra.resource_assignment_id
6772: AND pra.resource_assignment_id=l_resource_assignment_id_tbl(i)
6773: AND ROWNUM=1) pbl
6774: WHERE b.resource_assignment_id=l_resource_assignment_id_tbl(i);*/
6775:
6776: SELECT NVL(rac.txn_currency_code,l_existing_curr_code)
6777: INTO l_currency_code_tbl(i)
6778: FROM pa_resource_asgn_curr rac,
6779: pa_resource_assignments pra
6780: WHERE rac.resource_assignment_id(+)=pra.resource_assignment_id
6781: AND pra.resource_assignment_id=l_resource_assignment_id_tbl(i);
6782:
6783: END LOOP;
6869: --for a resource assignment with fixed spread curve. Hence the value returned should be considered
6870: l_sp_fixed_date_tbl := l_sp_fixed_date_new_tbl;
6871: --dbms_output.put_line(' cccc l_burdened_rate_override_tbl cnt is '||l_burdened_rate_override_tbl.count);
6872: IF l_debug_mode = 'Y' THEN
6873: pa_debug.g_err_stage:='Bulk updating pa_resource_assignments. start '||l_resource_assignment_id_tbl.FIRST ||' end '||l_resource_assignment_id_tbl.LAST;
6874: print_msg(pa_debug.g_err_stage,l_module_name);
6875: END IF;
6876:
6877: --dbms_output.put_line('6');
6874: print_msg(pa_debug.g_err_stage,l_module_name);
6875: END IF;
6876:
6877: --dbms_output.put_line('6');
6878: --Prepare the pl/sql tables for the all columns in pa_resource_assignments to make use of bulk update
6879: FORALL i IN l_resource_assignment_id_tbl.FIRST .. l_resource_assignment_id_tbl.LAST
6880:
6881: UPDATE PA_RESOURCE_ASSIGNMENTS
6882: 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))
6877: --dbms_output.put_line('6');
6878: --Prepare the pl/sql tables for the all columns in pa_resource_assignments to make use of bulk update
6879: FORALL i IN l_resource_assignment_id_tbl.FIRST .. l_resource_assignment_id_tbl.LAST
6880:
6881: UPDATE PA_RESOURCE_ASSIGNMENTS
6882: 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))
6883: ,last_update_date = sysdate
6884: ,last_updated_by = FND_GLOBAL.user_id
6885: ,last_update_login = FND_GLOBAL.login_id
7916: src_rbc.TXN_BILL_RATE_OVERRIDE
7917: FROM pa_resource_asgn_curr src_rbc,
7918: pa_fp_ra_map_tmp map,
7919: --pa_budget_lines bl
7920: pa_resource_assignments ra
7921: WHERE ra.budget_version_id =l_targ_budget_version_id
7922: -- AND src_rbc.budget_version_id = l_src_budget_version_id
7923: AND map.target_res_assignment_id = ra.resource_assignment_id
7924: AND src_rbc.resource_assignment_id = map.source_res_assignment_id;
7948:
7949: --Bug 4097749. Update the resource assigments created above with the named_role attribute returned by
7950: --the TA validate API
7951: IF l_debug_mode = 'Y' THEN
7952: pa_debug.g_err_stage:='About to update named role/parent assignment id in pa_resource_assignments';
7953: pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
7954: END IF;
7955:
7956: --Bug 4215676 . Modified the update sql for performance issues.
7962: SELECT rlm.resource_class_code, rlm.inventory_item_id,
7963: rlm.expenditure_type
7964: INTO l_resource_class_code, l_inv_item_id, l_exp_type
7965: FROM pa_resource_list_members rlm,
7966: pa_resource_assignments ra
7967: WHERE ra.resource_assignment_id = l_ra_id_tbl(i)
7968: AND ra.resource_list_member_id = rlm.resource_list_member_id;
7969:
7970: IF l_resource_class_code IN ('PEOPLE', 'EQUIPMENT') THEN
7997: END;
7998: END IF;
7999: END LOOP;
8000: FORALL i IN 1..l_ra_id_tbl.COUNT
8001: UPDATE pa_resource_assignments
8002: SET named_role = l_named_role_tbl(i),
8003: parent_assignment_id=NULL,
8004: /* bug fix:5135927 : Added nvl for l_res_rate_base_flag */
8005: resource_rate_based_flag =NVL(l_res_rate_based_flag_tbl(i),'N')--IPM
8006: WHERE parent_assignment_id = l_ra_id_tbl(i)
8007: AND budget_version_id = l_targ_budget_version_id;
8008:
8009: IF l_debug_mode = 'Y' THEN
8010: pa_debug.g_err_stage:='Done with updating named role/parent assignment id in pa_resource_assignments';
8011: pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
8012: END IF;
8013:
8014:
8164:
8165: --Bug 4097749. Update the resource assigments created above with the named_role attribute returned by
8166: --the TA validate API
8167: IF l_debug_mode = 'Y' THEN
8168: pa_debug.g_err_stage:='About to update named role/parent assignment id in pa_resource_assignments';
8169: pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
8170: END IF;
8171:
8172: --Bug 4215676 . Modified the update sql for performance issues.
8177: ---->then in future copy task flows the resource assignments which were are copied now will also be considered
8178: ---->as NEW and copy_budget_lines will try to insert them which will violate the unique constraint
8179: ---->on pa_budget_lines
8180: FORALL i IN 1..l_ra_id_tbl.COUNT
8181: UPDATE pa_resource_assignments
8182: SET named_role = l_named_role_tbl(i),
8183: parent_assignment_id=NULL
8184: WHERE parent_assignment_id = l_ra_id_tbl(i)
8185: AND budget_version_id = l_targ_budget_version_id;
8184: WHERE parent_assignment_id = l_ra_id_tbl(i)
8185: AND budget_version_id = l_targ_budget_version_id;
8186:
8187: IF l_debug_mode = 'Y' THEN
8188: pa_debug.g_err_stage:='Done with updating named role/parent assignment id in pa_resource_assignments';
8189: pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
8190: END IF;
8191:
8192:
8366: src_rbc.TXN_BILL_RATE_OVERRIDE
8367: FROM pa_resource_asgn_curr src_rbc,
8368: pa_fp_ra_map_tmp map,
8369: --pa_budget_lines bl
8370: pa_resource_assignments ra
8371: WHERE ra.budget_version_id =l_targ_budget_version_id
8372: -- AND src_rbc.budget_version_id = l_src_budget_version_id
8373: AND map.target_res_assignment_id = ra.resource_assignment_id
8374: AND src_rbc.resource_assignment_id = map.source_res_assignment_id;
8578: i NUMBER;
8579: l_cntr NUMBER;
8580:
8581: l_currency_code_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
8582: l_task_id PA_RESOURCE_ASSIGNMENTS.TASK_ID%TYPE;
8583: l_rbs_element_id PA_RESOURCE_ASSIGNMENTS.RBS_ELEMENT_ID%TYPE;
8584: l_res_class_code PA_RESOURCE_ASSIGNMENTS.RESOURCE_CLASS_CODE%TYPE;
8585: l_rate_based_flag PA_RESOURCE_ASSIGNMENTS.RATE_BASED_FLAG%TYPE;
8586: l_mode varchar2(12) := null; --Bug 4160258
8579: l_cntr NUMBER;
8580:
8581: l_currency_code_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
8582: l_task_id PA_RESOURCE_ASSIGNMENTS.TASK_ID%TYPE;
8583: l_rbs_element_id PA_RESOURCE_ASSIGNMENTS.RBS_ELEMENT_ID%TYPE;
8584: l_res_class_code PA_RESOURCE_ASSIGNMENTS.RESOURCE_CLASS_CODE%TYPE;
8585: l_rate_based_flag PA_RESOURCE_ASSIGNMENTS.RATE_BASED_FLAG%TYPE;
8586: l_mode varchar2(12) := null; --Bug 4160258
8587:
8580:
8581: l_currency_code_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
8582: l_task_id PA_RESOURCE_ASSIGNMENTS.TASK_ID%TYPE;
8583: l_rbs_element_id PA_RESOURCE_ASSIGNMENTS.RBS_ELEMENT_ID%TYPE;
8584: l_res_class_code PA_RESOURCE_ASSIGNMENTS.RESOURCE_CLASS_CODE%TYPE;
8585: l_rate_based_flag PA_RESOURCE_ASSIGNMENTS.RATE_BASED_FLAG%TYPE;
8586: l_mode varchar2(12) := null; --Bug 4160258
8587:
8588:
8581: l_currency_code_tbl SYSTEM.PA_VARCHAR2_15_TBL_TYPE := SYSTEM.PA_VARCHAR2_15_TBL_TYPE();
8582: l_task_id PA_RESOURCE_ASSIGNMENTS.TASK_ID%TYPE;
8583: l_rbs_element_id PA_RESOURCE_ASSIGNMENTS.RBS_ELEMENT_ID%TYPE;
8584: l_res_class_code PA_RESOURCE_ASSIGNMENTS.RESOURCE_CLASS_CODE%TYPE;
8585: l_rate_based_flag PA_RESOURCE_ASSIGNMENTS.RATE_BASED_FLAG%TYPE;
8586: l_mode varchar2(12) := null; --Bug 4160258
8587:
8588:
8589: l_project_id pa_projects_all.project_id%TYPE; --Bug 4218331
8834: ,pbv.ci_id
8835: INTO l_project_id
8836: ,l_budget_version_id
8837: ,l_ci_id
8838: FROM pa_resource_assignments pra
8839: ,pa_budget_Versions pbv
8840: WHERE pbv.budget_version_id=pra.budget_version_id
8841: AND pbv.wp_version_flag='Y'
8842: AND pra.wbs_element_version_id=p_element_version_id_tbl(l_cntr)
8870: ,pbv.ci_id
8871: INTO l_project_id
8872: ,l_budget_version_id
8873: ,l_ci_id
8874: FROM pa_resource_assignments pra
8875: ,pa_budget_Versions pbv
8876: WHERE pbv.budget_version_id=pra.budget_version_id
8877: AND pra.resource_assignment_id =l_task_assmt_ids_tbl(1);
8878: EXCEPTION
8906: ,pbv.ci_id
8907: INTO l_project_id
8908: ,l_budget_version_id
8909: ,l_ci_id
8910: FROM pa_resource_assignments pra
8911: ,pa_budget_Versions pbv
8912: WHERE pbv.budget_version_id=pra.budget_version_id
8913: AND pra.resource_assignment_id =p_resource_assignment_tbl(1);
8914: EXCEPTION
9046: --that wbs_elememnt_version_id will be populated only for Workplan versions and hence all the checks done in
9047: --the commented SQL are already done in that API
9048: FORALL i IN 1..l_task_assmt_ids_tbl.COUNT
9049: DELETE
9050: FROM pa_resource_assignments pra
9051: WHERE resource_assignment_id=l_task_assmt_ids_tbl(i)
9052: RETURNING
9053: pra.resource_assignment_id,
9054: pra.task_id,
9190: IF nvl(p_calling_module,'-99') <> 'PROCESS_RES_CHG_DERV_CALC_PRMS' THEN
9191:
9192: FORALL i IN l_delete_assmt_flag_tbl.first..l_delete_assmt_flag_tbl.last
9193: DELETE
9194: FROM pa_resource_assignments pra
9195: WHERE resource_assignment_id=p_resource_assignment_tbl(i)
9196: AND l_delete_assmt_flag_tbl(i)='Y'
9197: RETURNING
9198: pra.resource_assignment_id,
9274: FORALL i IN p_element_version_id_tbl.first..p_element_version_id_tbl.last
9275: DELETE
9276: FROM pa_budget_lines pbl
9277: WHERE pbl.resource_assignment_id IN (SELECT pra.resource_assignment_id
9278: FROM pa_resource_assignments pra,
9279: pa_budget_versions pbv
9280: WHERE pra.budget_Version_id=pbv.budget_Version_id
9281: AND nvl(pbv.wp_version_flag,'N')=l_wp_version_flag
9282: AND pra.budget_Version_id = l_budget_version_id--Bug#4548675--Bug 4218331
9355:
9356: If p_element_version_id_tbl.COUNT > 0 AND l_budget_version_id is NOT NULL Then --Bug fix:5349668 --{
9357: FORALL i IN p_element_version_id_tbl.first..p_element_version_id_tbl.last
9358: DELETE
9359: FROM pa_resource_assignments pra
9360: WHERE pra.wbs_element_version_id=p_element_version_id_tbl(i)
9361: AND EXISTS (SELECT 'X'
9362: FROM pa_budget_Versions pbv
9363: WHERE pbv.budget_version_id=pra.budget_Version_id
9448: INTO l_exists
9449: FROM DUAL
9450: WHERE EXISTS (SELECT 'X'
9451: FROM pa_budget_lines pbl,
9452: pa_resource_assignments pra
9453: WHERE pra.resource_assignment_id=p_resource_assignment_tbl(i)
9454: AND pbl.budget_Version_id=pra.budget_version_id
9455: AND pbl.resource_assignment_id=pra.resource_assignment_id);
9456: EXCEPTION
9572: END IF;
9573:
9574: FORALL i IN p_resource_assignment_tbl.first..p_resource_assignment_tbl.last
9575: DELETE
9576: FROM pa_resource_assignments pra
9577: WHERE pra.resource_assignment_id=p_resource_assignment_tbl(i)
9578: AND (l_currency_code_tbl(i) IS NULL
9579: OR
9580: NOT EXISTS ( SELECT 'EXISTS'
9743: --Prepare the pl/sql tables for task id, rbs element id , resource class code and rate based flag.
9744: --These pl/sql tables should be same in length to the pl/sql tables prepared while deleting the budget
9745: --lines. This can be done by looping thru the l_resource_assignment_id_tbl and looking for a matching
9746: --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
9747: -- is fetched directly from pa_resource_assignments for ra id in l_resource_assignment_id_tbl.
9748: l_ra_index:=1;
9749: l_counter:=0;--This is used just to keep track of the length of the pl/sql tables being prepared
9750:
9751: l_task_id_tbl.EXTEND(l_resource_assignment_id_tbl.last);
9788: INTO l_task_id,
9789: l_rbs_element_id,
9790: l_res_class_code,
9791: l_rate_based_flag
9792: FROM PA_RESOURCE_ASSIGNMENTS
9793: WHERE RESOURCE_ASSIGNMENT_ID = l_resource_assignment_id_tbl(i);
9794:
9795: EXIT; --Exit LOOP
9796:
10249: Purpose: This is a private api in the package. This API will
10250: validate the task data passed to the
10251: update_planning_transactions api This API checks
10252: for the existence of the element version id passed
10253: in pa_resource_assignments. If some of the element
10254: version Ids are not there then it call
10255: add_planning_transactions API to create records in
10256: pa_resource_assignments. This API will be called
10257: only when the context is WORKPLAN
10252: for the existence of the element version id passed
10253: in pa_resource_assignments. If some of the element
10254: version Ids are not there then it call
10255: add_planning_transactions API to create records in
10256: pa_resource_assignments. This API will be called
10257: only when the context is WORKPLAN
10258: =======================================================================*/
10259: /*******************************************************************************************************
10260: As part of Bug 3749516 All References to Equipment Effort or Equip Resource Class has been removed in
10318: l_raw_cost_tbl_tmp SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
10319: l_burdened_cost_tbl_tmp SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
10320:
10321:
10322: cursor c_res_assignment_id(c_wbs_element_version_id pa_resource_assignments.wbs_element_version_id%TYPE) IS
10323: SELECT resource_assignment_id,resource_class_code
10324: FROM pa_resource_assignments
10325: WHERE wbs_element_version_id = c_wbs_element_version_id
10326: AND ta_display_flag = 'N' -- Bug 3749516
10320:
10321:
10322: cursor c_res_assignment_id(c_wbs_element_version_id pa_resource_assignments.wbs_element_version_id%TYPE) IS
10323: SELECT resource_assignment_id,resource_class_code
10324: FROM pa_resource_assignments
10325: WHERE wbs_element_version_id = c_wbs_element_version_id
10326: AND ta_display_flag = 'N' -- Bug 3749516
10327: AND resource_class_code in (PA_FP_CONSTANTS_PKG.G_RESOURCE_CLASS_CODE_PPL);
10328:
10380: --------------------------------------------------------------------------
10381: -- Logic manifested below ------------------------------------------------
10382: -- -----------------------------------------------------------------------
10383: -- For each record of element version id in input parameter
10384: -- p_element_version_id_tbl, we scan through pa_resource_assignments table
10385: -- and bulk fetch the resource_class_code and resource_assignment_id into
10386: -- local PLSql tables. Now we have a inner loop which we run for each of
10387: -- the resouce_assignment_id fetched corresponding to the elem_ver_id of
10388: -- the parent loop. In the inner loop we populate the corresponding out
10389: -- param tables - x_resource_assignment_id_tbl,x_element_version_id_tbl
10390: -- ,x_planning_end_date_tbl, x_planning_start_date_tbl and
10391: -- x_planned_effort_tbl.
10392: --
10393: -- In case if there no records are retrieved in pa_resource_assignments
10394: -- for any element version id then we populate a separate set of tables
10395: -- from the corresponding IN parameters as l_element_version_id_tbl,
10396: -- l_planning_start_date_tbl,l_planning_end_date_tbl,
10397: -- l_planned_people_effort_tbl.
10906: pa_debug.write('Refresh_Plan_Txns: ' || g_module_name,pa_debug.g_err_stage,3);
10907: END IF;
10908:
10909: DELETE
10910: FROM pa_resource_assignments
10911: WHERE budget_version_id = p_budget_version_id
10912: -- IPM changes Bug 5003827 Issue 22
10913: RETURNING resource_assignment_id BULK COLLECT INTO l_delete_ra_id_tbl;
10914: /* Bug 5754758 - Commenting the code, as the maintain_data is now called in version level mode to delete the RACs.
11027: -- Delete all the planning resources where neither rlmID is 0
11028: -- Or resource is 'FINANCIAL_ELEMENTS'
11029: -- Bug 3658232 added null handling for resource class flag
11030:
11031: DELETE FROM pa_resource_assignments
11032: WHERE budget_version_id = p_budget_version_id
11033: AND
11034: NOT (resource_class_code = 'FINANCIAL_ELEMENTS' AND nvl(resource_class_flag,'N') = 'Y')
11035: -- IPM changes Bug 5003827 Issue 22
11076: pa_debug.g_err_stage:='Updaing res assignments with new FINANCIAL ELEMENTS rlmid : ' || l_fin_res_class_rlm_id;
11077: pa_debug.write('Refresh_Plan_Txns: ' || g_module_name,pa_debug.g_err_stage,3);
11078: END IF;
11079:
11080: UPDATE pa_resource_assignments
11081: SET resource_list_member_id = l_fin_res_class_rlm_id
11082: WHERE budget_version_id = p_budget_version_id;
11083: --AND resource_class_code = 'FINANCIAL_ELEMENTS' --Bug 4200168. RL/PL change both can not happen at the same time.
11084: --AND resource_class_flag = 'Y';
11131: -- END of IPM changes Bug 5003827 Issue 22
11132:
11133:
11134: -- Calling the rollup api to correct the amounts related data in
11135: -- pa_budget_versions and pa_resource_assignments for the entire version
11136: PA_FP_ROLLUP_PKG.rollup_budget_version
11137: (p_budget_version_id => p_budget_version_id
11138: ,p_entire_version => 'Y'
11139: ,x_return_status => l_return_status
11341: where v.parent_structure_version_id = c_parent_structure_version_id
11342: and v.financial_task_flag = 'Y' -- raja bug 3690418
11343: and v.task_level = 'L'
11344: and not exists (select 'x'
11345: from pa_resource_assignments pra
11346: where pra.budget_version_id = P_budget_version_id
11347: and pra.task_id = v.task_id
11348: and p_calling_context = 'SELECT_TASKS');
11349:
11362: and a.project_id = b.project_id
11363: and a.task_id = b.proj_element_id
11364: and a.task_id = a.top_task_id
11365: and not exists (select 'x'
11366: from pa_resource_assignments pra
11367: where pra.budget_version_id = P_budget_version_id
11368: and pra.task_id = a.task_id
11369: and p_calling_context = 'SELECT_TASKS');
11370:
11855: SELECT wbs_element_version_id
11856: ,name
11857: ,element_number
11858: ,resource_assignment_id
11859: FROM pa_resource_assignments pra
11860: ,pa_proj_elements ppe
11861: WHERE pra.project_id = p_project_id
11862: AND pra.budget_version_id = c_budget_version_id
11863: AND pra.ta_display_flag = 'Y'
12020:
12021: FORALL i IN l_pub_budget_version_id_tbl.first .. l_pub_budget_version_id_tbl.last
12022: --Fix for bug#7279771, uncommented the code comment done earlier for resource_class_code,
12023: --resource_class_flag,ta_display_flag
12024: UPDATE pa_resource_assignments
12025: SET resource_list_member_id = l_people_res_class_rlm_id
12026: WHERE budget_version_id = l_pub_budget_version_id_tbl(i)
12027: /* The only records present in pa_resource_assignments for published versions with resource
12028: list NONE would fall under the below cateogry. Not including them as part of select as they
12023: --resource_class_flag,ta_display_flag
12024: UPDATE pa_resource_assignments
12025: SET resource_list_member_id = l_people_res_class_rlm_id
12026: WHERE budget_version_id = l_pub_budget_version_id_tbl(i)
12027: /* The only records present in pa_resource_assignments for published versions with resource
12028: list NONE would fall under the below cateogry. Not including them as part of select as they
12029: dont add any value to performance interms of better index usage. Retaining them in the comment
12030: for understanding purpose */
12031: AND resource_class_code = 'PEOPLE'
12226: pa_debug.g_err_stage:='Updaing res assignments with new FINANCIAL ELEMENTS rlmid : ' || l_fin_res_class_rlm_id;
12227: pa_debug.write('REFRESH_WP_SETTINGS: ' || g_module_name,pa_debug.g_err_stage,3);
12228: END IF;
12229:
12230: UPDATE pa_resource_assignments
12231: SET resource_list_member_id = l_people_res_class_rlm_id
12232: WHERE budget_version_id = l_budget_version_id_tbl(i)
12233: AND resource_class_code = 'PEOPLE'
12234: AND resource_class_flag = 'Y';
12257:
12258: -- Call calculate only if there are some planning transactions to be processed
12259: SELECT count(*)
12260: INTO l_res_assignment_count
12261: FROM pa_resource_assignments
12262: WHERE budget_version_id = l_budget_version_id_tbl(i);
12263:
12264: IF l_res_assignment_count > 0 THEN
12265: PA_FP_CALC_PLAN_PKG.calculate(
12408: IF nvl(l_txn_source_id_tbl.last,0) >= 1 THEN
12409: -- Update resource assignments data for the version
12410: -- Bug 3641252 changed the index from i to j
12411: FORALL j IN l_txn_source_id_tbl.first .. l_txn_source_id_tbl.last
12412: UPDATE pa_resource_assignments
12413: SET rbs_element_id = l_rbs_element_id_tbl(j)
12414: ,txn_accum_header_id = l_txn_accum_header_id_tbl(j)
12415: ,record_version_number = record_version_number + 1
12416: ,last_update_date = SYSDATE
12421: END IF;
12422: ELSE -- rbs version id is null
12423:
12424: -- Update all the resource assigments with null for rbs _element_id
12425: UPDATE pa_resource_assignments
12426: SET rbs_element_id = null
12427: ,txn_accum_header_id = null
12428: ,record_version_number = record_version_number + 1
12429: ,last_update_date = SYSDATE
12804: IF nvl(l_txn_source_id_tbl.last,0) >= 1 THEN
12805: -- Update resource assignments data for the version
12806: -- Bug 3641252 changed the index from i to j
12807: FORALL j IN l_txn_source_id_tbl.first .. l_txn_source_id_tbl.last
12808: UPDATE pa_resource_assignments
12809: SET rbs_element_id = l_rbs_element_id_tbl(j)
12810: ,txn_accum_header_id = l_txn_accum_header_id_tbl(j)
12811: ,record_version_number = record_version_number + 1
12812: ,last_update_date = SYSDATE
12817: END IF;
12818: ELSE -- rbs version id is null
12819:
12820: -- Update all the resource assigments with null for rbs _element_id
12821: UPDATE pa_resource_assignments
12822: SET rbs_element_id = null
12823: ,txn_accum_header_id = null
12824: ,record_version_number = record_version_number + 1
12825: ,last_update_date = SYSDATE
12905: END IF;
12906: RAISE;
12907: END Refresh_rbs_for_versions;
12908:
12909: --This function returns 'N' if a record already exists in pa_resource_assignments
12910: --for a given budget version id, task id and resource list member id
12911: --Returns 'Y' if the record is not already there
12912: FUNCTION DUP_EXISTS
12913: ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
12921:
12922: BEGIN
12923: SELECT 'Y'
12924: INTO l_dup_exists
12925: FROM pa_resource_assignments
12926: WHERE task_id=p_task_id
12927: AND resource_list_member_id=p_resource_list_member_id
12928: AND budget_version_id=p_budget_version_id
12929: AND project_assignment_id=-1