[Home] [Help]
268: ELSIF p_etc_source_code = 'WORKPLAN_RESOURCES' THEN
269: /*Bug fix for 3973511
270: Workplan side only stores amounts in one currency for each planning
271: resource. Instead of relying on pa_progress_utils.get_actuals_for_task
272: to get actuals data, we query directly to pa_budget_lines to get actual
273: data from source workplan budget version */
274: IF P_PA_DEBUG_MODE = 'Y' THEN
275: pa_fp_gen_amount_utils.fp_debug(
276: p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
503: ELSIF p_etc_source_code = 'WORKPLAN_RESOURCES' THEN
504: /*Bug fix for 3973511
505: Workplan side only stores amounts in one currency for each planning
506: resource. Instead of relying on pa_progress_utils.get_actuals_for_task
507: to get actuals data, we query directly to pa_budget_lines to get actual
508: data from source workplan budget version */
509: IF P_PA_DEBUG_MODE = 'Y' THEN
510: pa_fp_gen_amount_utils.fp_debug(
511: p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
1408: AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id
1409: AND tmp_ra.transaction_source_code = 'WORKPLAN_RESOURCES';
1410:
1411: -- Variables added for Bug 5203622
1412: l_other_rej_code PA_BUDGET_LINES.OTHER_REJECTION_CODE%TYPE;
1413: l_other_rej_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1414:
1415: BEGIN
1416: IF p_pa_debug_mode = 'Y' THEN
2500: l_tot_raw_cost_sum NUMBER;
2501: l_tot_revenue_sum NUMBER;
2502: l_tot_raw_cost_pc_sum NUMBER;
2503: l_tot_revenue_pc_sum NUMBER;
2504: l_other_rej_code PA_BUDGET_LINES.OTHER_REJECTION_CODE%TYPE;
2505: l_other_rej_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2506: l_ins_other_rej_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2507:
2508: BEGIN
2693: ELSIF l_etc_source_code = 'WORKPLAN_RESOURCES' THEN
2694: /*Bug fix for 3973511
2695: Workplan side only stores amounts in one currency for each planning
2696: resource. Instead of relying on pa_progress_utils.get_actuals_for_task
2697: to get actuals data, we query directly to pa_budget_lines to get actual
2698: data from source workplan budget version */
2699: IF P_PA_DEBUG_MODE = 'Y' THEN
2700: pa_fp_gen_amount_utils.fp_debug(
2701: p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
2983: ELSIF l_etc_source_code = 'WORKPLAN_RESOURCES' THEN
2984: /*Bug fix for 3973511
2985: Workplan side only stores amounts in one currency for each planning
2986: resource. Instead of relying on pa_progress_utils.get_actuals_for_task
2987: to get actuals data, we query directly to pa_budget_lines to get actual
2988: data from source workplan budget version */
2989: IF P_PA_DEBUG_MODE = 'Y' THEN
2990: pa_fp_gen_amount_utils.fp_debug(
2991: p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
3800: NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))), --sbl.burdened_cost
3801: NULL,
3802: NULL
3803: FROM PA_FP_CALC_AMT_TMP2 tmp4,
3804: pa_budget_lines sbl,
3805: pa_resource_assignments ra
3806: WHERE tmp4.resource_assignment_id = sbl.resource_assignment_id
3807: and (tmp4.TRANSACTION_SOURCE_CODE = 'WORKPLAN_RESOURCES' OR
3808: tmp4.TRANSACTION_SOURCE_CODE = 'FINANCIAL_PLAN')
3868: NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))), --sbl.burdened_cost
3869: NULL,
3870: NULL
3871: FROM PA_FP_CALC_AMT_TMP2 tmp4,
3872: pa_budget_lines sbl,
3873: pa_resource_assignments ra
3874: WHERE tmp4.resource_assignment_id = sbl.resource_assignment_id
3875: and tmp4.TRANSACTION_SOURCE_CODE = c_gen_source
3876: and tmp4.TARGET_RES_ASG_ID = ra.resource_assignment_id
3936: NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))), --sbl.burdened_cost
3937: NULL,
3938: NULL
3939: FROM PA_FP_CALC_AMT_TMP2 tmp4,
3940: pa_budget_lines sbl,
3941: pa_resource_assignments ra,
3942: PA_GL_PA_PERIODS_TMP pa_gl
3943: WHERE tmp4.resource_assignment_id = sbl.resource_assignment_id
3944: and (tmp4.TRANSACTION_SOURCE_CODE = 'WORKPLAN_RESOURCES' OR
4009: NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))), --sbl.burdened_cost
4010: NULL,
4011: NULL
4012: FROM PA_FP_CALC_AMT_TMP2 tmp4,
4013: pa_budget_lines sbl,
4014: pa_resource_assignments ra,
4015: PA_GL_PA_PERIODS_TMP pa_gl
4016: WHERE tmp4.resource_assignment_id = sbl.resource_assignment_id
4017: and tmp4.TRANSACTION_SOURCE_CODE = c_gen_source
4081: NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate)))/pa_gl.multiplier), --sbl.burdened_cost
4082: NULL,
4083: NULL
4084: FROM PA_FP_CALC_AMT_TMP2 tmp4,
4085: pa_budget_lines sbl,
4086: pa_resource_assignments ra,
4087: PA_GL_PA_PERIODS_TMP pa_gl
4088: WHERE tmp4.resource_assignment_id = sbl.resource_assignment_id
4089: and (tmp4.TRANSACTION_SOURCE_CODE = 'WORKPLAN_RESOURCES' OR
4154: NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate)))/pa_gl.multiplier), --sbl.burdened_cost
4155: NULL,
4156: NULL
4157: FROM PA_FP_CALC_AMT_TMP2 tmp4,
4158: pa_budget_lines sbl,
4159: pa_resource_assignments ra,
4160: PA_GL_PA_PERIODS_TMP pa_gl
4161: WHERE tmp4.resource_assignment_id = sbl.resource_assignment_id
4162: and tmp4.TRANSACTION_SOURCE_CODE = c_gen_source
4308: END LOOP;
4309:
4310:
4311: FORALL i IN l_tgt_res_asg_id_tab.FIRST..l_tgt_res_asg_id_tab.LAST
4312: INSERT INTO PA_BUDGET_LINES (
4313: BUDGET_LINE_ID,
4314: BUDGET_VERSION_ID,
4315: RESOURCE_ASSIGNMENT_ID,
4316: START_DATE,
4330: TXN_COST_RATE_OVERRIDE,
4331: BURDEN_COST_RATE_OVERRIDE
4332: )
4333: VALUES (
4334: pa_budget_lines_s.nextval,
4335: P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
4336: l_tgt_res_asg_id_tab(i),
4337: l_start_date_tab(i),
4338: l_txn_currency_code_tab(i),
4395: END LOOP;
4396:
4397:
4398: FORALL i IN l_tgt_res_asg_id_tab.FIRST..l_tgt_res_asg_id_tab.LAST
4399: INSERT INTO PA_BUDGET_LINES (
4400: BUDGET_LINE_ID,
4401: BUDGET_VERSION_ID,
4402: RESOURCE_ASSIGNMENT_ID,
4403: START_DATE,
4419: RAW_COST_SOURCE,
4420: BURDENED_COST_SOURCE,
4421: QUANTITY_SOURCE)
4422: VALUES (
4423: pa_budget_lines_s.nextval,
4424: P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
4425: l_tgt_res_asg_id_tab(i),
4426: l_start_date_tab(i),
4427: l_txn_currency_code_tab(i),
4487: END LOOP;
4488:
4489:
4490: FORALL i IN l_tgt_res_asg_id_tab.FIRST..l_tgt_res_asg_id_tab.LAST
4491: INSERT INTO PA_BUDGET_LINES (
4492: BUDGET_LINE_ID,
4493: BUDGET_VERSION_ID,
4494: RESOURCE_ASSIGNMENT_ID,
4495: START_DATE,
4511: RAW_COST_SOURCE,
4512: BURDENED_COST_SOURCE,
4513: QUANTITY_SOURCE)
4514: VALUES (
4515: pa_budget_lines_s.nextval,
4516: P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
4517: l_tgt_res_asg_id_tab(i),
4518: l_start_date_tab(i),
4519: l_txn_currency_code_tab(i),
4553: EXCEPTION
4554: WHEN NO_DATA_FOUND THEN
4555: SELECT MAX(pbl.end_date)
4556: INTO l_end_date
4557: FROM PA_BUDGET_LINES pbl,
4558: PA_FP_CALC_AMT_TMP2 tmp
4559: WHERE tmp.resource_assignment_id = pbl.resource_assignment_id ;
4560: END;
4561:
4652: END LOOP;
4653:
4654:
4655: FORALL i IN l_pr_tgt_res_asg_id_tab.FIRST..l_pr_tgt_res_asg_id_tab.LAST
4656: INSERT INTO PA_BUDGET_LINES (
4657: BUDGET_LINE_ID,
4658: BUDGET_VERSION_ID,
4659: RESOURCE_ASSIGNMENT_ID,
4660: START_DATE,
4676: RAW_COST_SOURCE,
4677: BURDENED_COST_SOURCE,
4678: QUANTITY_SOURCE)
4679: VALUES (
4680: pa_budget_lines_s.nextval,
4681: P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
4682: l_pr_tgt_res_asg_id_tab(i),
4683: l_pr_start_date_tab(i),
4684: l_pr_txn_currency_code_tab(i),
4773: -- We have to merge the data for scenario where it is possible that the destination plan is project level then
4774: -- for same resource assignment in destination with different source in that case we will get unique constraint
4775: -- error if we have data for the same period.
4776: FORALL i IN l_pr_tgt_res_asg_id_tab.FIRST..l_pr_tgt_res_asg_id_tab.LAST
4777: MERGE INTO PA_BUDGET_LINES pbl
4778: USING ( SELECT NULL as BUDGET_LINE_ID,
4779: P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID as BUDGET_VERSION_ID,
4780: l_pr_tgt_res_asg_id_tab(i) as RESOURCE_ASSIGNMENT_ID,
4781: l_pr_start_date_tab(i) as START_DATE,
4833: pbl.RAW_COST_SOURCE,
4834: pbl.BURDENED_COST_SOURCE,
4835: pbl.QUANTITY_SOURCE)
4836: VALUES (
4837: pa_budget_lines_s.nextval,
4838: tmp.BUDGET_VERSION_ID,
4839: tmp.RESOURCE_ASSIGNMENT_ID,
4840: tmp.START_DATE,
4841: tmp.TXN_CURRENCY_CODE,
4934: -- We have to merge the data for scenario where it is possible that the destination plan is project level then
4935: -- for same resource assignment in destination with different source in that case we will get unique constraint
4936: -- error if we have data for the same period.
4937: FORALL i IN l_pr_tgt_res_asg_id_tab.FIRST..l_pr_tgt_res_asg_id_tab.LAST
4938: MERGE INTO PA_BUDGET_LINES pbl
4939: USING ( SELECT NULL as BUDGET_LINE_ID,
4940: P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID as BUDGET_VERSION_ID,
4941: l_pr_tgt_res_asg_id_tab(i) as RESOURCE_ASSIGNMENT_ID,
4942: l_pr_start_date_tab(i) as START_DATE,
4994: pbl.RAW_COST_SOURCE,
4995: pbl.BURDENED_COST_SOURCE,
4996: pbl.QUANTITY_SOURCE)
4997: VALUES (
4998: pa_budget_lines_s.nextval,
4999: tmp.BUDGET_VERSION_ID,
5000: tmp.RESOURCE_ASSIGNMENT_ID,
5001: tmp.START_DATE,
5002: tmp.TXN_CURRENCY_CODE,
5038: rbc.txn_raw_cost_rate_override,
5039: rbc.txn_burden_cost_rate_override,
5040: rbc.txn_bill_rate_override
5041: FROM pa_resource_assignments ra,
5042: pa_budget_lines bl,
5043: pa_resource_asgn_curr rbc,
5044: PA_FP_CALC_AMT_TMP2 tmp4
5045: WHERE ra.budget_version_id = P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID
5046: AND ra.project_id = P_FP_COLS_TGT_REC.x_project_id
5097: sum(sbl.txn_raw_cost),
5098: sum(sbl.txn_burdened_cost),
5099: 'ETC'
5100: FROM PA_FP_CALC_AMT_TMP2 tmp4,
5101: pa_budget_lines sbl,
5102: pa_resource_assignments ra
5103: WHERE tmp4.TARGET_RES_ASG_ID = ra.resource_assignment_id
5104: AND sbl.resource_assignment_id=ra.resource_assignment_id
5105: AND ra.budget_version_id = P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID