[Home] [Help]
14: * txns that do not have any budget lines.
15: *
16: * IMPORTANT NOTE:
17: * This procedure should only be called before the final
18: * rollup of amounts in the pa_resource_asgn_curr table.
19: * The impact of calling this API out of order is that rolled
20: * up amounts and average rates will be nulled out for
21: * updated planning txns!
22: *
85: CURSOR nrb_txns_without_bl_csr IS
86: SELECT rbc.resource_assignment_id,
87: rbc.txn_currency_code
88: FROM pa_resource_assignments ra,
89: pa_resource_asgn_curr rbc
90: WHERE ra.budget_version_id = p_budget_version_id
91: AND ra.project_id = p_project_id
92: AND ra.rate_based_flag = 'N'
93: AND rbc.resource_assignment_id = ra.resource_assignment_id
103:
104: l_res_asg_id_tab PA_PLSQL_DATATYPES.NumTabTyp;
105: l_txn_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
106:
107: -- This flag tracks if the pa_resource_asgn_curr_tmp global
108: -- temp table has been deleted/cleared yet.
109: l_rbc_tmp_tbl_deleted_flag VARCHAR2(1);
110:
111: -- This flag tracks if calling maintain_data API in Insert
193: END IF;
194:
195: -- Initialize l_rbc_tmp_tbl_deleted_flag.
196: -- Intended Usage:
197: -- Logic that deletes the pa_resource_asgn_curr_tmp
198: -- global temp table should set this flag to 'Y' so that
199: -- downstream code knows that the table has been cleared
200: -- by this procedure.
201: l_rbc_tmp_tbl_deleted_flag := 'N';
251:
252: l_maint_data_ins_req_flag := 'Y';
253:
254: IF l_rbc_tmp_tbl_deleted_flag = 'N' THEN
255: DELETE pa_resource_asgn_curr_tmp;
256: l_rbc_tmp_tbl_deleted_flag := 'Y';
257:
258: IF p_pa_debug_mode = 'Y' THEN
259: PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
256: l_rbc_tmp_tbl_deleted_flag := 'Y';
257:
258: IF p_pa_debug_mode = 'Y' THEN
259: PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
260: ( p_msg => 'Records Deleted from pa_resource_asgn_curr_tmp',
261: --p_called_mode => p_called_mode,
262: p_module_name => l_module_name,
263: p_log_level => l_log_level );
264: END IF;
264: END IF;
265: END IF; -- IF l_rbc_tmp_tbl_deleted_flag = 'N' THEN
266:
267: FORALL i IN 1..l_res_asg_id_tab.count
268: INSERT INTO PA_RESOURCE_ASGN_CURR_TMP
269: ( resource_assignment_id,
270: txn_currency_code,
271: txn_raw_cost_rate_override,
272: txn_burden_cost_rate_override,
280:
281: IF p_pa_debug_mode = 'Y' THEN
282: PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
283: ( p_msg => 'Number of records Inserted into ' ||
284: 'PA_RESOURCE_ASGN_CURR_TMP:['||sql%Rowcount||']',
285: --p_called_mode => p_called_mode,
286: p_module_name => l_module_name,
287: p_log_level => l_log_level );
288: END IF;
338:
339: l_maint_data_ins_req_flag := 'Y';
340:
341: IF l_rbc_tmp_tbl_deleted_flag = 'N' THEN
342: DELETE pa_resource_asgn_curr_tmp;
343: l_rbc_tmp_tbl_deleted_flag := 'Y';
344:
345: IF p_pa_debug_mode = 'Y' THEN
346: PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
343: l_rbc_tmp_tbl_deleted_flag := 'Y';
344:
345: IF p_pa_debug_mode = 'Y' THEN
346: PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
347: ( p_msg => 'Records Deleted from pa_resource_asgn_curr_tmp',
348: --p_called_mode => p_called_mode,
349: p_module_name => l_module_name,
350: p_log_level => l_log_level );
351: END IF;
353:
354: -- Because the planning txns involved in Update Case 1
355: -- and Update Case 2 are mutually exclusive, no extra
356: -- logic needs to be added to avoid duplicate records
357: -- begin inserted into pa_resource_asgn_curr_tmp.
358: --
359: -- In the future, if additional Update Cases with
360: -- overlapping txns are added, then a precedence of
361: -- updates will need to be determined with corresponding
362: -- updates to the global temporary table.
363:
364: -- Note: all rate overrides are implicitly set to Null.
365: FORALL i IN 1..l_res_asg_id_tab.count
366: INSERT INTO PA_RESOURCE_ASGN_CURR_TMP
367: ( resource_assignment_id,
368: txn_currency_code )
369: VALUES
370: ( l_res_asg_id_tab(i),
372:
373: IF p_pa_debug_mode = 'Y' THEN
374: PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
375: ( p_msg => 'Number of records Inserted into ' ||
376: 'PA_RESOURCE_ASGN_CURR_TMP:['||sql%Rowcount||']',
377: --p_called_mode => p_called_mode,
378: p_module_name => l_module_name,
379: p_log_level => l_log_level );
380: END IF;
382: /* End Update Case 2 */
383:
384:
385: -- Call MAINTAIN_DATA to Insert rate overrides into the
386: -- pa_resource_asgn_curr table if required.
387: -- Note: temp table data should be completed populated
388: -- by this point by the preceding Update Cases.
389:
390: IF l_maint_data_ins_req_flag = 'Y' THEN
3655: END IF;
3656:
3657: -- For budgets, if we are not retaining manual lines, then
3658: -- all resources for the version need to be rolled up in the
3659: -- pa_resource_asgn_curr table. If we are retaining manual lines,
3660: -- then we can selectively roll up only non-manual lines to
3661: -- improve performance.
3662: -- For forecasts, we always need to roll up rates and amounts
3663: -- because of possible changes in actual amounts. As a result,
3675: -- the populate_display_qty() and maintain_data() API calls.
3676:
3677: -- Populate the display quantity for non-manually added resources.
3678:
3679: DELETE pa_resource_asgn_curr_tmp;
3680:
3681: INSERT INTO pa_resource_asgn_curr_tmp
3682: ( resource_assignment_id )
3683: SELECT ra.resource_assignment_id
3677: -- Populate the display quantity for non-manually added resources.
3678:
3679: DELETE pa_resource_asgn_curr_tmp;
3680:
3681: INSERT INTO pa_resource_asgn_curr_tmp
3682: ( resource_assignment_id )
3683: SELECT ra.resource_assignment_id
3684: FROM pa_resource_assignments ra
3685: WHERE ra.budget_version_id = p_budget_version_id
3707: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3708: raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3709: END IF;
3710:
3711: -- Call MAINTAIN_DATA to roll up amounts to pa_resource_asgn_curr
3712: -- for non-manually added resources. First, populate temp table data.
3713:
3714: DELETE pa_resource_asgn_curr_tmp;
3715:
3710:
3711: -- Call MAINTAIN_DATA to roll up amounts to pa_resource_asgn_curr
3712: -- for non-manually added resources. First, populate temp table data.
3713:
3714: DELETE pa_resource_asgn_curr_tmp;
3715:
3716: INSERT INTO pa_resource_asgn_curr_tmp
3717: ( resource_assignment_id,
3718: txn_currency_code,
3712: -- for non-manually added resources. First, populate temp table data.
3713:
3714: DELETE pa_resource_asgn_curr_tmp;
3715:
3716: INSERT INTO pa_resource_asgn_curr_tmp
3717: ( resource_assignment_id,
3718: txn_currency_code,
3719: txn_raw_cost_rate_override,
3720: txn_burden_cost_rate_override,
3726: rbc.txn_burden_cost_rate_override,
3727: rbc.txn_bill_rate_override
3728: FROM pa_resource_assignments ra,
3729: pa_budget_lines bl,
3730: pa_resource_asgn_curr rbc
3731: WHERE ra.budget_version_id = p_budget_version_id
3732: AND ra.project_id = p_project_id
3733: AND ra.transaction_source_code IS NOT NULL
3734: AND bl.resource_assignment_id = ra.resource_assignment_id
3796: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3797: raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3798: END IF;
3799:
3800: -- Call MAINTAIN_DATA to roll up amounts to pa_resource_asgn_curr.
3801:
3802: IF l_fp_cols_rec.x_plan_class_code = 'BUDGET' THEN
3803: l_calling_module := 'BUDGET_GENERATION';
3804: ELSIF l_fp_cols_rec.x_plan_class_code = 'FORECAST' THEN
3837:
3838: -- IPM: Delete records from PA_RESOURCE_ASSIGNMENTS that:
3839: -- 1. Do not have budget lines.
3840: -- 2. Have a non-null transaction_source_code.
3841: -- 3. Do not have a record in the PA_RESOURCE_ASGN_CURR table.
3842: -- In this way, we can ensure that all records in the resource
3843: -- assignments table have a corresponding records in the new
3844: -- pa_resource_asgn_curr entity.
3845:
3840: -- 2. Have a non-null transaction_source_code.
3841: -- 3. Do not have a record in the PA_RESOURCE_ASGN_CURR table.
3842: -- In this way, we can ensure that all records in the resource
3843: -- assignments table have a corresponding records in the new
3844: -- pa_resource_asgn_curr entity.
3845:
3846: DELETE FROM pa_resource_assignments ra
3847: WHERE ra.budget_version_id = p_budget_version_id
3848: AND ra.transaction_source_code IS NOT NULL
3849: AND NOT EXISTS (SELECT null
3850: FROM pa_budget_lines bl
3851: WHERE bl.resource_assignment_id = ra.resource_assignment_id)
3852: AND NOT EXISTS (SELECT null
3853: FROM pa_resource_asgn_curr rbc
3854: WHERE rbc.resource_assignment_id = ra.resource_assignment_id);
3855:
3856: -- END OF IPM: New Entity and Display Quantity ERs --------------------
3857:
4652: END IF;
4653: RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4654: END IF;
4655:
4656: -- UT Fix: calling maintain_data API to synch up PA_RESOURCE_ASGN_CURR so that this table can be used
4657: -- further down in this API and in Calculate API when it is called in RESOURCE_ASSIGNMENT mode.
4658: -- Call MAINTAIN_DATA to roll up amounts to pa_resource_asgn_curr
4659:
4660:
4654: END IF;
4655:
4656: -- UT Fix: calling maintain_data API to synch up PA_RESOURCE_ASGN_CURR so that this table can be used
4657: -- further down in this API and in Calculate API when it is called in RESOURCE_ASSIGNMENT mode.
4658: -- Call MAINTAIN_DATA to roll up amounts to pa_resource_asgn_curr
4659:
4660:
4661: IF p_pa_debug_mode = 'Y' THEN
4662: PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
4788: ,l_txn_avg_bill_rate_tbl
4789: ,l_txn_src_code_tbl
4790: ,l_planning_end_date_tbl_1
4791: ,l_etc_source_tbl
4792: FROM pa_resource_asgn_curr prac,
4793: pa_resource_assignments pra
4794: WHERE prac.budget_version_id = p_budget_version_id
4795: AND pra.budget_version_id = p_budget_version_id
4796: AND prac.resource_assignment_id = pra.resource_assignment_id;