[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
3679: END IF;
3680:
3681: -- For budgets, if we are not retaining manual lines, then
3682: -- all resources for the version need to be rolled up in the
3683: -- pa_resource_asgn_curr table. If we are retaining manual lines,
3684: -- then we can selectively roll up only non-manual lines to
3685: -- improve performance.
3686: -- For forecasts, we always need to roll up rates and amounts
3687: -- because of possible changes in actual amounts. As a result,
3699: -- the populate_display_qty() and maintain_data() API calls.
3700:
3701: -- Populate the display quantity for non-manually added resources.
3702:
3703: DELETE pa_resource_asgn_curr_tmp;
3704:
3705: INSERT INTO pa_resource_asgn_curr_tmp
3706: ( resource_assignment_id )
3707: SELECT ra.resource_assignment_id
3701: -- Populate the display quantity for non-manually added resources.
3702:
3703: DELETE pa_resource_asgn_curr_tmp;
3704:
3705: INSERT INTO pa_resource_asgn_curr_tmp
3706: ( resource_assignment_id )
3707: SELECT ra.resource_assignment_id
3708: FROM pa_resource_assignments ra
3709: WHERE ra.budget_version_id = p_budget_version_id
3731: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3732: raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3733: END IF;
3734:
3735: -- Call MAINTAIN_DATA to roll up amounts to pa_resource_asgn_curr
3736: -- for non-manually added resources. First, populate temp table data.
3737:
3738: DELETE pa_resource_asgn_curr_tmp;
3739:
3734:
3735: -- Call MAINTAIN_DATA to roll up amounts to pa_resource_asgn_curr
3736: -- for non-manually added resources. First, populate temp table data.
3737:
3738: DELETE pa_resource_asgn_curr_tmp;
3739:
3740: INSERT INTO pa_resource_asgn_curr_tmp
3741: ( resource_assignment_id,
3742: txn_currency_code,
3736: -- for non-manually added resources. First, populate temp table data.
3737:
3738: DELETE pa_resource_asgn_curr_tmp;
3739:
3740: INSERT INTO pa_resource_asgn_curr_tmp
3741: ( resource_assignment_id,
3742: txn_currency_code,
3743: txn_raw_cost_rate_override,
3744: txn_burden_cost_rate_override,
3750: rbc.txn_burden_cost_rate_override,
3751: rbc.txn_bill_rate_override
3752: FROM pa_resource_assignments ra,
3753: pa_budget_lines bl,
3754: pa_resource_asgn_curr rbc
3755: WHERE ra.budget_version_id = p_budget_version_id
3756: AND ra.project_id = p_project_id
3757: AND ra.transaction_source_code IS NOT NULL
3758: AND bl.resource_assignment_id = ra.resource_assignment_id
3820: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3821: raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3822: END IF;
3823:
3824: -- Call MAINTAIN_DATA to roll up amounts to pa_resource_asgn_curr.
3825:
3826: IF l_fp_cols_rec.x_plan_class_code = 'BUDGET' THEN
3827: l_calling_module := 'BUDGET_GENERATION';
3828: ELSIF l_fp_cols_rec.x_plan_class_code = 'FORECAST' THEN
3861:
3862: -- IPM: Delete records from PA_RESOURCE_ASSIGNMENTS that:
3863: -- 1. Do not have budget lines.
3864: -- 2. Have a non-null transaction_source_code.
3865: -- 3. Do not have a record in the PA_RESOURCE_ASGN_CURR table.
3866: -- In this way, we can ensure that all records in the resource
3867: -- assignments table have a corresponding records in the new
3868: -- pa_resource_asgn_curr entity.
3869:
3864: -- 2. Have a non-null transaction_source_code.
3865: -- 3. Do not have a record in the PA_RESOURCE_ASGN_CURR table.
3866: -- In this way, we can ensure that all records in the resource
3867: -- assignments table have a corresponding records in the new
3868: -- pa_resource_asgn_curr entity.
3869:
3870: DELETE FROM pa_resource_assignments ra
3871: WHERE ra.budget_version_id = p_budget_version_id
3872: AND ra.transaction_source_code IS NOT NULL
3873: AND NOT EXISTS (SELECT null
3874: FROM pa_budget_lines bl
3875: WHERE bl.resource_assignment_id = ra.resource_assignment_id)
3876: AND NOT EXISTS (SELECT null
3877: FROM pa_resource_asgn_curr rbc
3878: WHERE rbc.resource_assignment_id = ra.resource_assignment_id);
3879:
3880: -- END OF IPM: New Entity and Display Quantity ERs --------------------
3881:
4676: END IF;
4677: RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4678: END IF;
4679:
4680: -- UT Fix: calling maintain_data API to synch up PA_RESOURCE_ASGN_CURR so that this table can be used
4681: -- further down in this API and in Calculate API when it is called in RESOURCE_ASSIGNMENT mode.
4682: -- Call MAINTAIN_DATA to roll up amounts to pa_resource_asgn_curr
4683:
4684:
4678: END IF;
4679:
4680: -- UT Fix: calling maintain_data API to synch up PA_RESOURCE_ASGN_CURR so that this table can be used
4681: -- further down in this API and in Calculate API when it is called in RESOURCE_ASSIGNMENT mode.
4682: -- Call MAINTAIN_DATA to roll up amounts to pa_resource_asgn_curr
4683:
4684:
4685: IF p_pa_debug_mode = 'Y' THEN
4686: PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
4812: ,l_txn_avg_bill_rate_tbl
4813: ,l_txn_src_code_tbl
4814: ,l_planning_end_date_tbl_1
4815: ,l_etc_source_tbl
4816: FROM pa_resource_asgn_curr prac,
4817: pa_resource_assignments pra
4818: WHERE prac.budget_version_id = p_budget_version_id
4819: AND pra.budget_version_id = p_budget_version_id
4820: AND prac.resource_assignment_id = pra.resource_assignment_id;