[Home] [Help]
PACKAGE BODY: APPS.PA_FP_REV_GEN_PUB
Source
1 PACKAGE body PA_FP_REV_GEN_PUB as
2 /* $Header: PAFPGCRB.pls 120.7 2007/02/06 09:57:59 dthakker ship $ */
3
4 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 PROCEDURE GEN_COST_BASED_REVENUE
7 (P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
8 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
9 P_ETC_START_DATE IN PA_BUDGET_VERSIONS.ETC_START_DATE%TYPE,
10 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
11 X_MSG_COUNT OUT NOCOPY NUMBER,
12 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
13
14 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_REV_GEN_PUB.GEN_COST_BASED_REVENUE';
15
16 l_pfc_project_value PA_PROJECTS_ALL.PROJECT_VALUE%TYPE;
17 l_pc_project_value PA_PROJECTS_ALL.PROJECT_VALUE%TYPE;
18 l_pfc_burdened_cost PA_BUDGET_VERSIONS.BURDENED_COST%TYPE := 0;
19 l_pfc_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE := 0;
20 l_rev_tab PA_PLSQL_DATATYPES.NumTabTyp;
21 l_rev NUMBER;
22 l_running_rev NUMBER := 0;
23 l_diff NUMBER;
24
25 l_msg_count NUMBER;
26 l_msg_data VARCHAR2(2000);
27 l_data VARCHAR2(2000);
28 l_msg_index_out NUMBER:=0;
29
30 --l_total_cost PA_BUDGET_VERSIONS.BURDENED_COST%TYPE;
31 l_budget_line_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
32 l_txn_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
33 l_project_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
34 l_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
35
36 l_txn_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
37 l_pc_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
38 l_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
39 l_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
40
41 l_txn_rev_tab PA_PLSQL_DATATYPES.NumTabTyp;
42 l_pc_rev_tab PA_PLSQL_DATATYPES.NumTabTyp;
43 l_pfc_rev_tab PA_PLSQL_DATATYPES.NumTabTyp;
44 l_rev_pc_exchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
45 l_rev_pfc_exchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
46 l_txn_bill_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
47
48
49 l_running_txn_rev NUMBER := 0;
50 l_running_pc_rev NUMBER := 0;
51 l_ratio NUMBER;
52
53 l_plan_class_code PA_FIN_PLAN_TYPES_B.PLAN_CLASS_CODE%TYPE;
54 l_init_rev_sum PA_BUDGET_LINES.INIT_REVENUE%TYPE := 0;
55 l_pc_init_rev_sum PA_BUDGET_LINES.PROJECT_INIT_REVENUE%TYPE := 0;
56 l_burdened_cost_sum PA_BUDGET_LINES.BURDENED_COST%TYPE;
57
58 l_appr_cost_plan_type_flag PA_BUDGET_VERSIONS.APPROVED_COST_PLAN_TYPE_FLAG%TYPE;
59 l_appr_rev_plan_type_flag PA_BUDGET_VERSIONS.APPROVED_COST_PLAN_TYPE_FLAG%TYPE;
60 l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
61 l_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE;
62 l_approved_fp_version_id PA_PROJ_FP_OPTIONS.FIN_PLAN_VERSION_ID%TYPE;
63 l_approved_fp_options_id PA_PROJ_FP_OPTIONS.PROJ_FP_OPTIONS_ID%TYPE;
64
65 l_cost_or_rev_code VARCHAR2(30) := 'COST';
66
67 --l_pfc_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
68 l_txn_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
69 l_pc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
70 l_pfc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
71
72 l_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
73 l_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
74 l_eliminated_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
75
76 l_budget_line_id_tmp PA_BUDGET_LINES.BUDGET_LINE_ID%TYPE;
77 l_res_asg_id_tmp PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE;
78 l_start_date_tmp PA_BUDGET_LINES.START_DATE%TYPE;
79 l_txn_currency_code_tmp PA_BUDGET_LINES.TXN_CURRENCY_CODE%TYPE;
80 l_budget_line_id_dup PA_BUDGET_LINES.BUDGET_LINE_ID%TYPE;
81
82 l_upd_count NUMBER := 0;
83 l_del_count NUMBER := 0;
84 l_dup_bl_id NUMBER;
85 l_budget_line_id_upd_tab PA_PLSQL_DATATYPES.IdTabTyp;
86 l_budget_line_id_del_tab PA_PLSQL_DATATYPES.IdTabTyp;
87 l_txn_burdened_cost_upd_tab PA_PLSQL_DATATYPES.NumTabTyp;
88 l_pc_burdened_cost_upd_tab PA_PLSQL_DATATYPES.NumTabTyp;
89 l_pfc_burdened_cost_upd_tab PA_PLSQL_DATATYPES.NumTabTyp;
90
91 l_txn_raw_cost_upd_tab PA_PLSQL_DATATYPES.NumTabTyp;
92 l_pc_raw_cost_upd_tab PA_PLSQL_DATATYPES.NumTabTyp;
93 l_pfc_raw_cost_upd_tab PA_PLSQL_DATATYPES.NumTabTyp;
94
95 l_txn_revenue_upd_tab PA_PLSQL_DATATYPES.NumTabTyp;
96 l_pc_revenue_upd_tab PA_PLSQL_DATATYPES.NumTabTyp;
97 l_pfc_revenue_upd_tab PA_PLSQL_DATATYPES.NumTabTyp;
98
99 l_quantity_upd_tab PA_PLSQL_DATATYPES.NumTabTyp;
100
101 l_dup_bl_num NUMBER;
102 l_bill_amt_pfc_revenue NUMBER;
103 l_bill_amt_pc_revenue NUMBER;
104
105 l_txn_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
106
107 --Added foll 3 variables for bug 4127427 to calculate l_ratio for PC seperately
108 l_pc_burdened_cost PA_BUDGET_LINES.PROJECT_BURDENED_COST%TYPE := 0;
109 l_pc_revenue PA_BUDGET_LINES.PROJECT_REVENUE%TYPE := 0;
110 l_ratio_pc NUMBER;
111
112 /* Variables added for Bug 4549862 */
113 l_gen_src_code PA_PROJ_FP_OPTIONS.GEN_COST_SRC_CODE%TYPE;
114
115 -- Variables added for IPM Enhancements
116 l_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
117 l_ipm_currency_code_tab PA_PLSQL_DATATYPES.Char15TabTyp;
118 l_rc_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
119 l_bc_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
120 l_calling_module VARCHAR2(30);
121
122 -- Added in IPM to track if a record in the existing set of
123 -- pl/sql tables needs to be removed.
124 l_remove_record_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
125 l_remove_records_flag VARCHAR2(1);
126
127 l_tmp_index NUMBER;
128 l_tmp_budget_line_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
129 l_tmp_txn_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
130 l_tmp_pc_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
131 l_tmp_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
132 l_tmp_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
133 l_tmp_txn_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
134 l_tmp_pc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
135 l_tmp_pfc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
136 l_tmp_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
137 l_tmp_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
138 l_tmp_txn_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
139 l_tmp_txn_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
140 l_tmp_project_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
141 l_tmp_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
142
143 BEGIN
144 /* Setting initial values */
145 X_MSG_COUNT := 0;
146 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
147
148 IF p_pa_debug_mode = 'Y' THEN
149 PA_DEBUG.SET_CURR_FUNCTION
150 ( p_function => 'GEN_COMMITMENT_AMOUNTS',
151 p_debug_mode => p_pa_debug_mode );
152 END IF;
153
154 IF p_fp_cols_rec.x_version_type = 'ALL' THEN
155 l_cost_or_rev_code := 'COST';
156 ELSE
157 l_cost_or_rev_code := 'REVENUE';
158 END IF;
159
160 SELECT fpt.PLAN_CLASS_CODE
161 INTO l_plan_class_code
162 FROM PA_BUDGET_VERSIONS bv,
163 PA_FIN_PLAN_TYPES_B fpt
164 WHERE bv.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
165 AND bv.fin_plan_type_id = fpt.fin_plan_type_id;
166
167 IF p_pa_debug_mode = 'Y' THEN
168 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
169 ( p_msg => 'Value of l_plan_class_code: '||l_plan_class_code,
170 p_module_name => l_module_name,
171 p_log_level => 5 );
172 END IF;
173 --dbms_output.put_line('Value of l_plan_class_code: '||l_plan_class_code);
174
175 -- Bug 4549862: Initialize new l_gen_src_code variable.
176 IF l_plan_class_code = 'BUDGET' THEN
177 l_gen_src_code := p_fp_cols_rec.x_gen_src_code;
178 ELSIF l_plan_class_code = 'FORECAST' THEN
179 l_gen_src_code := p_fp_cols_rec.x_gen_etc_src_code;
180 END IF;
181
182
183 -- IPM : New Entity ER --------------------------------------
184 -- When the source/target planning options match, source
185 -- override rates are copied to the target version in the
186 -- new entity table (pa_resource_asgn_curr).
187 -- When the Revenue Accrual Method is COST, all source
188 -- override rates are not applicable, since revenue is
189 -- derived from cost amounts.
190 -- Therefore, call the MAINTAIN_DATA API in Insert mode
191 -- with existing cost override rates and Null for bill
192 -- rate overrides.
193 -- Note: It is not necessary to actually check if the source/
194 -- target planning options match to clean up the bill
195 -- rate override rates.
196 -- Note: In some cases, we do not have to worry about cleaning
197 -- up the bill rate overrides. For example, if the source
198 -- is a Cost-only version. We currently do not have the
199 -- source version type readily available. This will be
200 -- added in the near future to avoid uneccessary processing.
201
202 -- Get existing cost rate overrides from pa_resource_asgn_curr.
203 IF p_fp_cols_rec.x_gen_ret_manual_line_flag = 'N' THEN
204 SELECT rbc.resource_assignment_id,
205 rbc.txn_currency_code,
206 rbc.txn_raw_cost_rate_override,
207 rbc.txn_burden_cost_rate_override
208 BULK COLLECT
209 INTO l_ra_id_tab,
210 l_ipm_currency_code_tab,
211 l_rc_rate_override_tab,
212 l_bc_rate_override_tab
213 FROM pa_resource_asgn_curr rbc
214 WHERE rbc.budget_version_id = p_budget_version_id;
215 ELSIF p_fp_cols_rec.x_gen_ret_manual_line_flag = 'Y' THEN
216 -- Only get data for non-manually added resources.
217 SELECT rbc.resource_assignment_id,
218 rbc.txn_currency_code,
219 rbc.txn_raw_cost_rate_override,
220 rbc.txn_burden_cost_rate_override
221 BULK COLLECT
222 INTO l_ra_id_tab,
223 l_ipm_currency_code_tab,
224 l_rc_rate_override_tab,
225 l_bc_rate_override_tab
226 FROM pa_resource_asgn_curr rbc,
227 pa_resource_assignments ra
228 WHERE rbc.budget_version_id = p_budget_version_id
229 AND rbc.resource_assignment_id = ra.resource_assignment_id
230 AND (ra.transaction_source_code IS NOT NULL
231 OR (ra.transaction_source_code IS NULL
232 AND NOT EXISTS (SELECT null
233 FROM pa_budget_lines bl
234 WHERE bl.resource_assignment_id =
235 ra.resource_assignment_id )));
236 END IF; -- x_gen_ret_manual_line_flag check
237
238 -- If there are any new entity records to update, then
239 -- populate the temp table and call the MAINTAIN_DATA API
240 -- in Insert mode to overwrite existing records with just
241 -- cost rate overrides and Null bill rate overrides.
242 IF l_ra_id_tab.count > 0 THEN
243
244 DELETE pa_resource_asgn_curr_tmp;
245
246 FORALL i IN 1..l_ra_id_tab.count
247 INSERT INTO pa_resource_asgn_curr_tmp (
248 resource_assignment_id,
249 txn_currency_code,
250 txn_raw_cost_rate_override,
251 txn_burden_cost_rate_override )
252 VALUES (
253 l_ra_id_tab(i),
254 l_ipm_currency_code_tab(i),
255 l_rc_rate_override_tab(i),
256 l_bc_rate_override_tab(i) );
257
258 IF l_plan_class_code = 'BUDGET' THEN
259 l_calling_module := PA_RES_ASG_CURRENCY_PUB.G_BUDGET_GENERATION;
260 ELSIF l_plan_class_code = 'FORECAST' THEN
261 l_calling_module := PA_RES_ASG_CURRENCY_PUB.G_FORECAST_GENERATION;
262 END IF;
263
264 -- Call the maintenance api in INSERT mode
265 IF p_pa_debug_mode = 'Y' THEN
266 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
267 P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.' ||
268 'MAINTAIN_DATA',
269 --P_CALLED_MODE => p_called_mode,
270 P_MODULE_NAME => l_module_name);
271 END IF;
272 PA_RES_ASG_CURRENCY_PUB.MAINTAIN_DATA
273 ( P_FP_COLS_REC => p_fp_cols_rec,
274 P_CALLING_MODULE => l_calling_module,
275 P_ROLLUP_FLAG => 'N',
276 P_VERSION_LEVEL_FLAG => 'N',
277 --P_CALLED_MODE => p_called_mode,
278 X_RETURN_STATUS => x_return_status,
279 X_MSG_COUNT => x_msg_count,
280 X_MSG_DATA => x_msg_data );
281 IF p_pa_debug_mode = 'Y' THEN
282 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
283 P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.' ||
284 'MAINTAIN_DATA: '||x_return_status,
285 --P_CALLED_MODE => p_called_mode,
286 P_MODULE_NAME => l_module_name);
287 END IF;
288 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
289 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
290 END IF;
291
292 END IF; --IF l_ra_id_tab.count > 0 THEN
293
294 -- END OF IPM : New Entity ER --------------------------------------
295
296
297 SELECT NVL(APPROVED_COST_PLAN_TYPE_FLAG, 'N'),
298 NVL(APPROVED_REV_PLAN_TYPE_FLAG, 'N')
299 INTO
300 l_appr_cost_plan_type_flag,
301 l_appr_rev_plan_type_flag
302 FROM PA_BUDGET_VERSIONS
303 WHERE BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
304
305 IF l_appr_cost_plan_type_flag = 'Y' OR
306 l_appr_rev_plan_type_flag = 'Y' THEN
307 BEGIN
308 SELECT PROJFUNC_OPP_VALUE,
309 PROJECT_OPP_VALUE
310 INTO l_pfc_project_value,
311 l_pc_project_value
312 FROM PA_PROJECT_OPP_ATTRS
313 WHERE PROJECT_ID = P_FP_COLS_REC.X_PROJECT_ID;
314 EXCEPTION
315 WHEN NO_DATA_FOUND THEN
316 l_pfc_project_value := null;
317 l_pc_project_value := null;
318 END;
319 ELSE
320 IF P_PA_DEBUG_MODE = 'Y' THEN
321 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
322 ( P_MSG => 'Before calling PA_FIN_PLAN_UTILS.'||
323 'Get_Appr_Rev_Plan_Type_Info',
324 P_MODULE_NAME => l_module_name,
325 P_LOG_LEVEL => 5 );
326 END IF;
327 PA_FIN_PLAN_UTILS.Get_Appr_Rev_Plan_Type_Info
328 ( p_project_id => P_FP_COLS_REC.X_PROJECT_ID,
329 x_plan_type_id => l_fin_plan_type_id,
330 x_return_status => x_return_status,
331 x_msg_count => x_msg_count,
332 x_msg_data => x_msg_data );
333 IF P_PA_DEBUG_MODE = 'Y' THEN
334 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
335 ( P_MSG => 'After calling PA_FIN_PLAN_UTILS.'||
336 'Get_Appr_Rev_Plan_Type_Info: '||x_return_status,
337 P_MODULE_NAME => l_module_name,
338 P_LOG_LEVEL => 5 );
339 END IF;
340 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
341 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
342 END IF;
343 IF (l_fin_plan_type_id IS NULL) THEN
344 BEGIN
345 SELECT PROJFUNC_OPP_VALUE,
346 PROJECT_OPP_VALUE
347 INTO l_pfc_project_value,
348 l_pc_project_value
349 FROM PA_PROJECT_OPP_ATTRS
350 WHERE PROJECT_ID = P_FP_COLS_REC.X_PROJECT_ID;
351 EXCEPTION
352 WHEN NO_DATA_FOUND THEN
353 l_pfc_project_value := null;
354 l_pc_project_value := null;
355 END;
356 ELSE
357 SELECT DECODE( FIN_PLAN_PREFERENCE_CODE,
358 'REVENUE_ONLY', 'REVENUE' ,
359 'COST_AND_REV_SEP', 'REVENUE',
360 'COST_AND_REV_SAME', 'ALL')
361 INTO l_version_type
362 FROM pa_proj_fp_options
363 WHERE fin_plan_type_id = l_fin_plan_type_id
364 AND fin_plan_option_level_code = 'PLAN_TYPE'
365 AND project_id = P_FP_COLS_REC.X_PROJECT_ID;
366
367 IF P_PA_DEBUG_MODE = 'Y' THEN
368 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
369 ( P_MSG => 'Before calling PA_FP_GEN_AMOUNT_UTILS.'||
370 'Get_Curr_Original_Version_Info',
371 P_MODULE_NAME => l_module_name,
372 P_LOG_LEVEL => 5 );
373 END IF;
374 PA_FP_GEN_AMOUNT_UTILS.Get_Curr_Original_Version_Info
375 ( p_project_id => P_FP_COLS_REC.X_PROJECT_ID,
376 p_fin_plan_type_id => l_fin_plan_type_id,
377 p_version_type => l_version_type,
378 p_status_code => 'CURRENT_APPROVED',
379 x_fp_options_id => l_approved_fp_options_id,
380 x_fin_plan_version_id => l_approved_fp_version_id,
381 x_return_status => x_return_status,
382 x_msg_count => x_msg_count,
383 x_msg_data => x_msg_data );
384 IF P_PA_DEBUG_MODE = 'Y' THEN
385 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
386 ( P_MSG => 'After calling PA_FP_GEN_AMOUNT_UTILS.'||
387 'Get_Curr_Original_Version_Info: '||x_return_status,
388 P_MODULE_NAME => l_module_name,
389 P_LOG_LEVEL => 5 );
390 END IF;
391 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
392 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
393 END IF;
394
395 IF (l_approved_fp_version_id IS NULL) THEN
396 BEGIN
397 SELECT PROJFUNC_OPP_VALUE,
398 PROJECT_OPP_VALUE
399 INTO l_pfc_project_value,
400 l_pc_project_value
401 FROM PA_PROJECT_OPP_ATTRS
402 WHERE PROJECT_ID = P_FP_COLS_REC.X_PROJECT_ID;
403 EXCEPTION
404 WHEN NO_DATA_FOUND THEN
405 l_pfc_project_value := null;
406 l_pc_project_value := null;
407 END;
408 ELSE
409 l_pfc_project_value := 0;
410 l_pc_project_value := 0;
411 SELECT NVL(REVENUE,0),
412 NVL(TOTAL_PROJECT_REVENUE,0)
413 INTO l_pfc_project_value,
414 l_pc_project_value
415 FROM PA_BUDGET_VERSIONS
416 WHERE BUDGET_VERSION_ID = l_approved_fp_version_id;
417 END IF;
418 END IF;
419 END IF;
420
421 IF NVL(l_pc_project_value,0) = 0 OR
422 NVL(l_pfc_project_value,0) = 0 THEN
423 PA_UTILS.ADD_MESSAGE
424 ( p_app_short_name => 'PA',
425 p_msg_name => 'PA_FCST_NO_PRJ_VALUE' );
426 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
427 END IF;
428
429 IF P_FP_COLS_REC.X_GEN_INCL_BILL_EVENT_FLAG = 'Y' THEN
430 IF P_PA_DEBUG_MODE = 'Y' THEN
431 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
432 ( P_MSG => 'Before calling PA_FP_GEN_BILLING_AMOUNTS.'
433 ||'GET_BILLING_EVENT_AMT_IN_PFC',
434 P_MODULE_NAME => l_module_name,
435 P_LOG_LEVEL => 5 );
436 END IF;
437 -- Added p_fp_cols_rec parameter for changes made for Bug 4067837.
438 PA_FP_GEN_BILLING_AMOUNTS.GET_BILLING_EVENT_AMT_IN_PFC
439 ( P_PROJECT_ID => P_FP_COLS_REC.X_PROJECT_ID,
440 P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
441 P_FP_COLS_REC => P_FP_COLS_REC,
442 P_PROJFUNC_CURRENCY_CODE => P_FP_COLS_REC.X_PROJFUNC_CURRENCY_CODE,
443 P_PROJECT_CURRENCY_CODE => P_FP_COLS_REC.X_PROJECT_CURRENCY_CODE,
444 X_PROJFUNC_REVENUE => l_bill_amt_pfc_revenue,
445 X_PROJECT_REVENUE => l_bill_amt_pc_revenue,
446 X_RETURN_STATUS => x_return_status,
447 X_MSG_COUNT => x_msg_count,
448 X_MSG_DATA => x_msg_data );
449 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
450 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
451 END IF;
452 IF P_PA_DEBUG_MODE = 'Y' THEN
453 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
454 ( P_MSG => 'After calling PA_FP_GEN_BILLING_AMOUNTS.'
455 ||'GET_BILLING_EVENT_AMT_IN_PFC',
456 P_MODULE_NAME => l_module_name,
457 P_LOG_LEVEL => 5);
458 END IF;
459 END IF;
460 --dbms_output.put_line('l_pfc_project_value:'||l_pfc_project_value);
461 --dbms_output.put_line('l_bill_amt_pfc_revenue:'||l_bill_amt_pfc_revenue);
462 l_pfc_project_value := l_pfc_project_value - NVL(l_bill_amt_pfc_revenue,0);
463 l_pc_project_value := l_pc_project_value - NVL(l_bill_amt_pc_revenue,0);
464
465 IF p_pa_debug_mode = 'Y' THEN
466 pa_fp_gen_amount_utils.fp_debug
467 ( p_msg => 'Value of l_pfc_project_value: '
468 || l_pfc_project_value,
469 p_module_name => l_module_name,
470 p_log_level => 5 );
471 pa_fp_gen_amount_utils.fp_debug
472 ( p_msg => 'Value of l_pc_project_value: '
473 || l_pc_project_value,
474 p_module_name => l_module_name,
475 p_log_level => 5 );
476 END IF;
477
478 -- Bug 4549862: Moved logic for actual revenue sum to this
479 -- point in the code to avoid duplicating shared code for the
480 -- resource schedule and non-resource-schedule flows later.
481
482 IF l_plan_class_code = 'FORECAST' THEN
483
484 -- Bug 4549862: Split query for actual revenue sum into
485 -- 2 separate queries with different WHERE clauses to
486 -- eliminate the DECODE statment on target time phase.
487
488 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
489 SELECT nvl(sum(nvl(init_revenue,0)),0),
490 nvl(sum(nvl(project_init_revenue,0)),0)
491 INTO l_init_rev_sum,
492 l_pc_init_rev_sum
493 FROM pa_budget_lines
494 WHERE budget_version_id = p_budget_version_id
495 AND start_date <= p_etc_start_date - 1;
496 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
497 SELECT nvl(sum(nvl(init_revenue,0)),0),
498 nvl(sum(nvl(project_init_revenue,0)),0)
499 INTO l_init_rev_sum,
500 l_pc_init_rev_sum
501 FROM pa_budget_lines
502 WHERE budget_version_id = p_budget_version_id;
503 END IF;
504
505 IF p_pa_debug_mode = 'Y' THEN
506 pa_fp_gen_amount_utils.fp_debug
507 ( p_msg => 'Value of l_init_rev_sum when the '
508 || 'plan_class_code is FORECAST:'
509 || l_init_rev_sum,
510 p_module_name => l_module_name,
511 p_log_level => 5 );
512 END IF;
513 /*dbms_output.put_line('Value of l_init_rev_sum when the plan_class_code is FORECAST:'
514 ||l_init_rev_sum);*/
515
516 l_pfc_project_value := l_pfc_project_value - l_init_rev_sum;
517 l_pc_project_value := l_pc_project_value - l_pc_init_rev_sum;
518
519 IF p_pa_debug_mode = 'Y' THEN
520 pa_fp_gen_amount_utils.fp_debug
521 ( p_msg => 'Value of l_pfc_project_value when the '
522 || 'plan_class_code is FORECAST: '
523 || l_pfc_project_value,
524 p_module_name => l_module_name,
525 p_log_level => 5 );
526 pa_fp_gen_amount_utils.fp_debug
527 ( p_msg => 'Value of l_pc_project_value when the '
528 || 'plan_class_code is FORECAST: '
529 || l_pc_project_value,
530 p_module_name => l_module_name,
531 p_log_level => 5 );
532 END IF;
533 /* dbms_output.put_line('Value of l_pfc_project_value when the plan_class_code is FORECAST:'
534 ||l_pfc_project_value);*/
535
536 END IF; -- l_plan_class_code = 'FORECAST' check
537
538
539 -- Bug 4549862: If Billing Event and/or Actual Revenue amounts
540 -- are more than the total project value, do not generate any
541 -- cost-based revenue amounts. This check is orthogonal to the
542 -- main issue addresed in the bug.
543
544 IF l_pc_project_value <= 0 OR
545 l_pfc_project_value <= 0 THEN
546
547 -- Bug 4549862: If the source is Staffing Plan, then all the budget
548 -- line (quantity and cost) data is being stored in PA_FP_ROLLUP_TMP.
549 -- Before returning from this API, call PUSH_RES_SCH_DATA_TO_BL to
550 -- Insert/Update data from the temp table to the budget lines.
551
552 IF l_gen_src_code = 'RESOURCE_SCHEDULE' THEN
553 IF P_PA_DEBUG_MODE = 'Y' THEN
554 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
555 ( P_MSG => 'Before calling PA_FP_REV_GEN_PUB.'
556 ||'PUSH_RES_SCH_DATA_TO_BL',
557 P_MODULE_NAME => l_module_name,
558 P_LOG_LEVEL => 5 );
559 END IF;
560 PA_FP_REV_GEN_PUB.PUSH_RES_SCH_DATA_TO_BL
561 ( P_BUDGET_VERSION_ID => p_budget_version_id,
562 P_FP_COLS_REC => p_fp_cols_rec,
563 P_ETC_START_DATE => p_etc_start_date,
564 P_PLAN_CLASS_CODE => l_plan_class_code,
565 X_RETURN_STATUS => x_return_status,
566 X_MSG_COUNT => x_msg_count,
567 X_MSG_DATA => x_msg_data );
568 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
569 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
570 END IF;
571 IF P_PA_DEBUG_MODE = 'Y' THEN
572 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
573 ( P_MSG => 'After calling PA_FP_REV_GEN_PUB.'
574 ||'PUSH_RES_SCH_DATA_TO_BL',
575 P_MODULE_NAME => l_module_name,
576 P_LOG_LEVEL => 5);
577 END IF;
578 END IF; -- insert/update temp table data to budget lines
579
580 IF P_PA_DEBUG_MODE = 'Y' THEN
581 PA_DEBUG.Reset_Curr_Function;
582 END IF;
583 RETURN;
584 END IF;
585
586 l_pfc_burdened_cost := 0;
587 l_pfc_revenue := 0;
588
589 -- Bug 4549862: When the target version is ALL, accrual method is COST,
590 -- and source is Staffing Plan, fetch burdened cost sums and individual
591 -- records for processing from PA_FP_ROLLUP_TMP, only selecting records
592 -- with BILLABLE_FLAG = 'Y'.
593 -- Note that checking l_gen_src_code determines this scenario, since
594 -- accrual method is COST for the entire API and generation of Revenue-
595 -- only versions from Staffing Plan is not supported.
596 -- In all other cases, fetch burdened cost sums and individual records
597 -- from PA_BUDGET_LINES using the existing logic.
598
599 IF l_gen_src_code = 'RESOURCE_SCHEDULE' THEN
600
601 IF l_plan_class_code = 'BUDGET' THEN
602
603 /* selecting the total burdened cost for pfc amounts. */
604 BEGIN
605 SELECT nvl(sum(nvl(bl.projfunc_burdened_cost,0)),0),
606 nvl(sum(nvl(bl.project_burdened_cost,0)),0)
607 INTO l_pfc_burdened_cost,
608 l_pc_burdened_cost
609 FROM pa_fp_rollup_tmp bl,
610 pa_resource_assignments ra
611 WHERE ra.budget_version_id = p_budget_version_id
612 AND ra.resource_assignment_id = bl.resource_assignment_id
613 AND bl.cost_rejection_code is null
614 AND bl.burden_rejection_code is null
615 AND bl.pc_cur_conv_rejection_code is null
616 AND bl.pfc_cur_conv_rejection_code is null
617 AND bl.BILLABLE_FLAG = 'Y';
618 EXCEPTION
619 WHEN NO_DATA_FOUND THEN
620 l_pfc_burdened_cost := 0;
621 END;
622
623 SELECT bl.budget_line_id,
624 nvl(bl.txn_burdened_cost,0),
625 nvl(bl.project_burdened_cost,0),
626 nvl(bl.projfunc_burdened_cost,0),
627 nvl(bl.quantity,0),
628 nvl(bl.txn_raw_cost,0),
629 nvl(bl.project_raw_cost,0),
630 nvl(bl.projfunc_raw_cost,0),
631 bl.resource_assignment_id,
632 bl.start_date,
633 bl.txn_currency_code
634 BULK COLLECT
635 INTO l_budget_line_id_tab,
636 l_txn_burdened_cost_tab,
637 l_pc_burdened_cost_tab,
638 l_burdened_cost_tab,
639 l_quantity_tab,
640 l_txn_raw_cost_tab,
641 l_pc_raw_cost_tab,
642 l_pfc_raw_cost_tab,
643 l_res_asg_id_tab,
644 l_start_date_tab,
645 l_txn_currency_code_tab
646 FROM pa_fp_rollup_tmp bl,
647 pa_resource_assignments ra
648 WHERE ra.budget_version_id = p_budget_version_id
649 AND ra.resource_assignment_id = bl.resource_assignment_id
650 AND bl.cost_rejection_code is null
651 AND bl.burden_rejection_code is null
652 AND bl.pc_cur_conv_rejection_code is null
653 AND bl.pfc_cur_conv_rejection_code is null
654 AND bl.BILLABLE_FLAG = 'Y'
655 ORDER BY bl.resource_assignment_id,
656 bl.start_date;
657 IF p_pa_debug_mode = 'Y' THEN
658 pa_fp_gen_amount_utils.fp_debug
659 ( p_msg => 'Count of l_budget_line_id_tab when the '
660 || 'plan_class_code is BUDGET: '
661 || l_budget_line_id_tab.count,
662 p_module_name => l_module_name,
663 p_log_level => 5 );
664 END IF;
665 /* dbms_output.put_line('Count of l_budget_line_id_tab when the plan_class_code is BUDGET:'
666 ||l_budget_line_id_tab.count);*/
667
668 ELSIF l_plan_class_code = 'FORECAST' THEN
669
670 -- In regards to the PA_FP_ROLLUP_TMP table, we are not maintaining
671 -- the None-timephase invariant that plan amount columns store Total
672 -- Amounts (planned plus actual amounts). Thus, there is no need to
673 -- subtract out actual amounts in the Select statement. Furthermore,
674 -- the temp table stores only ETC amounts, so checking bl.start_date
675 -- >= p_etc_start_date is ok for both time phased and non-time-phased
676 -- versions here.
677
678 SELECT bl.budget_line_id,
679 nvl(bl.txn_burdened_cost,0),
680 nvl(bl.project_burdened_cost,0),
681 nvl(bl.projfunc_burdened_cost,0),
682 nvl(bl.quantity, 0),
683 nvl(bl.txn_raw_cost,0),
684 nvl(bl.project_raw_cost,0),
685 nvl(bl.projfunc_raw_cost,0),
686 bl.resource_assignment_id,
687 bl.start_date,
688 bl.txn_currency_code
689 BULK COLLECT
690 INTO l_budget_line_id_tab,
691 l_txn_burdened_cost_tab,
692 l_pc_burdened_cost_tab,
693 l_burdened_cost_tab,
694 l_quantity_tab,
695 l_txn_raw_cost_tab,
696 l_pc_raw_cost_tab,
697 l_pfc_raw_cost_tab,
698 l_res_asg_id_tab,
699 l_start_date_tab,
700 l_txn_currency_code_tab
701 FROM pa_fp_rollup_tmp bl,
702 pa_resource_assignments ra
703 WHERE ra.budget_version_id = p_budget_version_id
704 AND ra.resource_assignment_id = bl.resource_assignment_id
705 AND bl.start_date >= p_etc_start_date
706 AND bl.cost_rejection_code is null
707 AND bl.burden_rejection_code is null
708 AND bl.pc_cur_conv_rejection_code is null
709 AND bl.pfc_cur_conv_rejection_code is null
710 AND bl.BILLABLE_FLAG = 'Y'
711 ORDER BY bl.resource_assignment_id,
712 bl.start_date;
713
714 IF p_pa_debug_mode = 'Y' THEN
715 pa_fp_gen_amount_utils.fp_debug
716 ( p_msg => 'Count of l_budget_line_id_tab when the '
717 || 'plan_class_code is FORECAST: '
718 || l_budget_line_id_tab.count,
719 p_module_name => l_module_name,
720 p_log_level => 5 );
721 END IF;
722
723 SELECT nvl(sum(nvl(bl.projfunc_burdened_cost,0)),0),
724 nvl(sum(nvl(bl.project_burdened_cost,0)),0)
725 INTO l_pfc_burdened_cost,
726 l_pc_burdened_cost
727 FROM pa_fp_rollup_tmp bl,
728 pa_resource_assignments ra
729 WHERE ra.budget_version_id = p_budget_version_id
730 AND ra.resource_assignment_id = bl.resource_assignment_id
731 AND bl.start_date >= p_etc_start_date
732 AND bl.cost_rejection_code is null
733 AND bl.burden_rejection_code is null
734 AND bl.pc_cur_conv_rejection_code is null
735 AND bl.pfc_cur_conv_rejection_code is null
736 AND bl.BILLABLE_FLAG = 'Y';
737
738 IF p_pa_debug_mode = 'Y' THEN
739 pa_fp_gen_amount_utils.fp_debug
740 ( p_msg => 'Value of l_pfc_burdened_cost when the '
741 || 'plan_class_code is FORECAST: '
742 || l_pfc_burdened_cost,
743 p_module_name => l_module_name,
744 p_log_level => 5 );
745 END IF;
746 /* dbms_output.put_line('Value of l_pfc_burdened_cost when the plan_class_code is FORECAST:'
747 ||l_pfc_burdened_cost);*/
748
749 END IF; -- plan class code check
750
751 ELSE -- l_gen_src_code <> 'RESOURCE_SCHEDULE'
752
753 IF l_plan_class_code = 'BUDGET' THEN
754 /* selecting the total burdened cost and revenue for pfc amounts. */
755 IF P_FP_COLS_REC.x_version_type = 'ALL' THEN
756 BEGIN
757 SELECT nvl(sum(nvl(bl.burdened_cost,0)),0),
758 nvl(sum(nvl(bl.project_burdened_cost,0)),0)
759 INTO l_pfc_burdened_cost,
760 l_pc_burdened_cost
761 FROM pa_budget_lines bl,
762 pa_resource_assignments ra,
763 pa_tasks ta /* Bug 4546405, ER 4376722 */
764 WHERE bl.budget_version_id = p_budget_version_id
765 AND ra.budget_version_id = p_budget_version_id
766 AND ra.resource_assignment_id = bl.resource_assignment_id
767 AND ra.transaction_source_code is not null
768 AND bl.cost_rejection_code is null
769 AND bl.revenue_rejection_code is null
770 AND bl.burden_rejection_code is null
771 AND bl.other_rejection_code is null
772 AND bl.pc_cur_conv_rejection_code is null
773 AND bl.pfc_cur_conv_rejection_code is null
774 AND NVL(ra.task_id,0) = ta.task_id (+) /* Bug 4546405, ER 4376722 */
775 AND NVL(ta.billable_flag,'Y') = 'Y' /* Bug 4546405, ER 4376722 */
776 AND ra.project_id = P_FP_COLS_REC.X_PROJECT_ID; /* Added for Bug 4546405 */
777 EXCEPTION
778 WHEN NO_DATA_FOUND THEN
779 l_pfc_burdened_cost := 0;
780 END;
781 ELSE
782 BEGIN
783 SELECT nvl(sum(nvl(bl.txn_revenue,0)),0),
784 nvl(sum(nvl(bl.project_revenue,0)),0)
785 INTO l_pfc_revenue,
786 l_pc_revenue
787 FROM pa_budget_lines bl,
788 pa_resource_assignments ra,
789 pa_tasks ta /* Bug 4546405, ER 4376722 */
790 WHERE bl.budget_version_id = p_budget_version_id
791 AND ra.budget_version_id = p_budget_version_id
792 AND ra.resource_assignment_id = bl.resource_assignment_id
793 AND ra.transaction_source_code is not null
794 AND bl.cost_rejection_code is null
795 AND bl.revenue_rejection_code is null
796 AND bl.burden_rejection_code is null
797 AND bl.other_rejection_code is null
798 AND bl.pc_cur_conv_rejection_code is null
799 AND bl.pfc_cur_conv_rejection_code is null
800 AND NVL(ra.task_id,0) = ta.task_id (+) /* Bug 4546405, ER 4376722 */
801 AND NVL(ta.billable_flag,'Y') = 'Y' /* Bug 4546405, ER 4376722 */
802 AND ra.project_id = P_FP_COLS_REC.X_PROJECT_ID; /* Added for Bug 4546405 */
803 EXCEPTION
804 WHEN NO_DATA_FOUND THEN
805 l_pfc_revenue := 0;
806 END;
807 END IF;
808 SELECT bl.budget_line_id,
809 nvl(bl.txn_burdened_cost,0),
810 nvl(bl.project_burdened_cost,0),
811 nvl(bl.burdened_cost,0),
812 nvl(bl.txn_revenue,0),
813 nvl(bl.project_revenue,0),
814 nvl(bl.revenue,0),
815 nvl(bl.quantity,0),
816 --nvl(bl.burdened_cost,0),
817 nvl(bl.txn_raw_cost,0),
818 nvl(bl.project_raw_cost,0),
819 nvl(bl.raw_cost,0),
820 bl.resource_assignment_id,
821 bl.start_date,
822 bl.txn_currency_code
823 BULK COLLECT
824 INTO l_budget_line_id_tab,
825 l_txn_burdened_cost_tab,
826 l_pc_burdened_cost_tab,
827 l_burdened_cost_tab,
828 l_txn_revenue_tab,
829 l_project_revenue_tab,
830 l_revenue_tab,
831 l_quantity_tab,
832 --l_pfc_burdened_cost_tab,
833 l_txn_raw_cost_tab,
834 l_pc_raw_cost_tab,
835 l_pfc_raw_cost_tab,
836 l_res_asg_id_tab,
837 l_start_date_tab,
838 l_txn_currency_code_tab
839 FROM pa_budget_lines bl,
840 pa_resource_assignments ra,
841 pa_tasks ta /* Bug 4546405, ER 4376722 */
842 WHERE bl.budget_version_id = p_budget_version_id
843 AND ra.budget_version_id = p_budget_version_id
844 AND ra.resource_assignment_id = bl.resource_assignment_id
845 AND ra.transaction_source_code is not null
846 AND bl.cost_rejection_code is null
847 AND bl.revenue_rejection_code is null
848 AND bl.burden_rejection_code is null
849 AND bl.other_rejection_code is null
850 AND bl.pc_cur_conv_rejection_code is null
851 AND bl.pfc_cur_conv_rejection_code is null
852 AND NVL(ra.task_id,0) = ta.task_id (+) /* Bug 4546405, ER 4376722 */
853 AND NVL(ta.billable_flag,'Y') = 'Y' /* Bug 4546405, ER 4376722 */
854 AND ra.project_id = P_FP_COLS_REC.X_PROJECT_ID /* Added for Bug 4546405 */
855 ORDER BY bl.resource_assignment_id,
856 bl.start_date;
857 IF p_pa_debug_mode = 'Y' THEN
858 pa_fp_gen_amount_utils.fp_debug
859 ( p_msg => 'Count of l_budget_line_id_tab when the '
860 || 'plan_class_code is BUDGET: '
861 || l_budget_line_id_tab.count,
862 p_module_name => l_module_name,
863 p_log_level => 5 );
864 END IF;
865 /* dbms_output.put_line('Count of l_budget_line_id_tab when the plan_class_code is BUDGET:'
866 ||l_budget_line_id_tab.count);*/
867 ELSIF l_plan_class_code = 'FORECAST' THEN
868 --bbb
869 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
870 SELECT bl.budget_line_id,
871 nvl(bl.txn_burdened_cost,0),
872 nvl(bl.project_burdened_cost,0),
873 nvl(bl.burdened_cost,0),
874 nvl(bl.txn_revenue,0),
875 nvl(bl.project_revenue,0),
876 nvl(bl.revenue,0),
877 nvl(bl.quantity, 0),
878 --nvl(bl.burdened_cost,0),
879 nvl(bl.txn_raw_cost,0),
880 nvl(bl.project_raw_cost,0),
881 nvl(bl.raw_cost,0),
882 bl.resource_assignment_id,
883 bl.start_date,
884 bl.txn_currency_code
885 BULK COLLECT
886 INTO l_budget_line_id_tab,
887 l_txn_burdened_cost_tab,
888 l_pc_burdened_cost_tab,
889 l_burdened_cost_tab,
890 l_txn_revenue_tab,
891 l_project_revenue_tab,
892 l_revenue_tab,
893 l_quantity_tab,
894 --l_pfc_burdened_cost_tab,
895 l_txn_raw_cost_tab,
896 l_pc_raw_cost_tab,
897 l_pfc_raw_cost_tab,
898 l_res_asg_id_tab,
899 l_start_date_tab,
900 l_txn_currency_code_tab
901 FROM pa_budget_lines bl,
902 pa_resource_assignments ra,
903 pa_tasks ta /* Bug 4546405, ER 4376722 */
904 WHERE bl.budget_version_id = p_budget_version_id
905 AND ra.budget_version_id = p_budget_version_id
906 AND ra.resource_assignment_id = bl.resource_assignment_id
907 AND ra.transaction_source_code is not null
908 AND bl.start_date >= p_etc_start_date
909 AND bl.cost_rejection_code is null
910 AND bl.revenue_rejection_code is null
911 AND bl.burden_rejection_code is null
912 AND bl.other_rejection_code is null
913 AND bl.pc_cur_conv_rejection_code is null
914 AND bl.pfc_cur_conv_rejection_code is null
915 AND NVL(ra.task_id,0) = ta.task_id (+) /* Bug 4546405, ER 4376722 */
916 AND NVL(ta.billable_flag,'Y') = 'Y' /* Bug 4546405, ER 4376722 */
917 AND ra.project_id = P_FP_COLS_REC.X_PROJECT_ID /* Added for Bug 4546405 */
918 ORDER BY bl.resource_assignment_id,
919 bl.start_date;
920 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
921 -- Bug 4292083: As a result of changes for this bug, we now maintain
922 -- the invariant that planned columns always store the Total amount.
923 -- Since we are only interested in ETC amounts, we need to subtract
924 -- out the Actual amounts.
925 -- Bug 4232094: Added WHERE clause condition:
926 -- NVL(bl.quantity,0) <> NVL(bl.init_quantity,0)
927 -- to address the None timephase case so that we only pick up budget
928 -- lines that have Plan amounts.
929 SELECT bl.budget_line_id,
930 nvl(bl.txn_burdened_cost,0) - nvl(bl.txn_init_burdened_cost,0),
931 nvl(bl.project_burdened_cost,0) - nvl(bl.project_init_burdened_cost,0),
932 nvl(bl.burdened_cost,0) - nvl(bl.init_burdened_cost,0),
933 nvl(bl.txn_revenue,0) - nvl(bl.txn_init_revenue,0),
934 nvl(bl.project_revenue,0) - nvl(bl.project_init_revenue,0),
935 nvl(bl.revenue,0) - nvl(bl.init_revenue,0),
936 nvl(bl.quantity,0) - nvl(bl.init_quantity,0),
937 --nvl(bl.burdened_cost,0) - nvl(bl.init_burdened_cost,0),
938 nvl(bl.txn_raw_cost,0) - nvl(bl.txn_init_raw_cost,0),
939 nvl(bl.project_raw_cost,0) - nvl(bl.project_init_raw_cost,0),
940 nvl(bl.raw_cost,0) - nvl(bl.init_raw_cost,0),
941 bl.resource_assignment_id,
942 bl.start_date,
943 bl.txn_currency_code
944 BULK COLLECT
945 INTO l_budget_line_id_tab,
946 l_txn_burdened_cost_tab,
947 l_pc_burdened_cost_tab,
948 l_burdened_cost_tab,
949 l_txn_revenue_tab,
950 l_project_revenue_tab,
951 l_revenue_tab,
952 l_quantity_tab,
953 --l_pfc_burdened_cost_tab,
954 l_txn_raw_cost_tab,
955 l_pc_raw_cost_tab,
956 l_pfc_raw_cost_tab,
957 l_res_asg_id_tab,
958 l_start_date_tab,
959 l_txn_currency_code_tab
960 FROM pa_budget_lines bl,
961 pa_resource_assignments ra,
962 pa_tasks ta /* Bug 4546405, ER 4376722 */
963 WHERE bl.budget_version_id = p_budget_version_id
964 AND ra.budget_version_id = p_budget_version_id
965 AND ra.resource_assignment_id = bl.resource_assignment_id
966 AND ra.transaction_source_code is not null
967 AND bl.cost_rejection_code is null
968 AND bl.revenue_rejection_code is null
969 AND bl.burden_rejection_code is null
970 AND bl.other_rejection_code is null
971 AND bl.pc_cur_conv_rejection_code is null
972 AND bl.pfc_cur_conv_rejection_code is null
973 AND NVL(bl.quantity,0) <> NVL(bl.init_quantity,0)
974 AND NVL(ra.task_id,0) = ta.task_id (+) /* Bug 4546405, ER 4376722 */
975 AND NVL(ta.billable_flag,'Y') = 'Y' /* Bug 4546405, ER 4376722 */
976 AND ra.project_id = P_FP_COLS_REC.X_PROJECT_ID /* Added for Bug 4546405 */
977 ORDER BY bl.resource_assignment_id,
978 bl.start_date;
979 END IF; -- timephased check
980
981 IF p_pa_debug_mode = 'Y' THEN
982 pa_fp_gen_amount_utils.fp_debug
983 ( p_msg => 'Count of l_budget_line_id_tab when the '
984 || 'plan_class_code is FORECAST: '
985 || l_budget_line_id_tab.count,
986 p_module_name => l_module_name,
987 p_log_level => 5 );
988 END IF;
989 /* dbms_output.put_line('Count of l_budget_line_id_tab when the plan_class_code is FORECAST:'
990 ||l_budget_line_id_tab.count);*/
991
992 -- Bug 4549862: Moved query for actual revenue sum to an earlier
993 -- point in the code to avoid duplicating shared code for the
994 -- resource schedule and non-resource-schedule flows.
995
996 -- Bug 4232094: Added WHERE clause condition:
997 -- NVL(bl.quantity,0) <> NVL(bl.init_quantity,0)
998 -- to address the None timephase case so that we only pick up budget
999 -- lines that have Plan amounts.
1000 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
1001 SELECT nvl(sum(nvl(bl.burdened_cost,0)),0),
1002 nvl(sum(nvl(bl.revenue,0)),0),
1003 nvl(sum(nvl(bl.project_burdened_cost,0)),0),
1004 nvl(sum(nvl(bl.project_revenue,0)),0)
1005 INTO l_pfc_burdened_cost,
1006 l_pfc_revenue,
1007 l_pc_burdened_cost,
1008 l_pc_revenue
1009 FROM pa_budget_lines bl,
1010 pa_resource_assignments ra,
1011 pa_tasks ta /* Bug 4546405, ER 4376722 */
1012 WHERE bl.budget_version_id = p_budget_version_id
1013 AND ra.budget_version_id = p_budget_version_id
1014 AND ra.resource_assignment_id = bl.resource_assignment_id
1015 AND ra.transaction_source_code is not null
1016 AND bl.start_date >= p_etc_start_date
1017 AND bl.cost_rejection_code is null
1018 AND bl.revenue_rejection_code is null
1019 AND bl.burden_rejection_code is null
1020 AND bl.other_rejection_code is null
1021 AND bl.pc_cur_conv_rejection_code is null
1022 AND bl.pfc_cur_conv_rejection_code is null
1023 AND NVL(ra.task_id,0) = ta.task_id (+) /* Bug 4546405, ER 4376722 */
1024 AND NVL(ta.billable_flag,'Y') = 'Y' /* Bug 4546405, ER 4376722 */
1025 AND ra.project_id = P_FP_COLS_REC.X_PROJECT_ID; /* Added for Bug 4546405 */
1026 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
1027 -- Bug 4292083: As a result of changes for this bug, we now maintain
1028 -- the invariant that planned columns always store the Total amount.
1029 -- Since we are only interested in ETC amounts, we need to subtract
1030 -- out the Actual amounts.
1031 SELECT nvl(sum(nvl(bl.burdened_cost,0) - nvl(bl.init_burdened_cost,0)),0),
1032 nvl(sum(nvl(bl.revenue,0) - nvl(bl.init_revenue,0)),0),
1033 nvl(sum(nvl(bl.project_burdened_cost,0) - nvl(bl.project_init_burdened_cost,0)),0),
1034 nvl(sum(nvl(bl.project_revenue,0) - nvl(bl.project_init_revenue,0)),0)
1035 INTO l_pfc_burdened_cost,
1036 l_pfc_revenue,
1037 l_pc_burdened_cost,
1038 l_pc_revenue
1039 FROM pa_budget_lines bl,
1040 pa_resource_assignments ra,
1041 pa_tasks ta /* Bug 4546405, ER 4376722 */
1042 WHERE bl.budget_version_id = p_budget_version_id
1043 AND ra.budget_version_id = p_budget_version_id
1044 AND ra.resource_assignment_id = bl.resource_assignment_id
1045 AND ra.transaction_source_code is not null
1046 AND bl.cost_rejection_code is null
1047 AND bl.revenue_rejection_code is null
1048 AND bl.burden_rejection_code is null
1049 AND bl.other_rejection_code is null
1050 AND bl.pc_cur_conv_rejection_code is null
1051 AND bl.pfc_cur_conv_rejection_code is null
1052 AND NVL(bl.quantity,0) <> NVL(bl.init_quantity,0)
1053 AND NVL(ra.task_id,0) = ta.task_id (+) /* Bug 4546405, ER 4376722 */
1054 AND NVL(ta.billable_flag,'Y') = 'Y' /* Bug 4546405, ER 4376722 */
1055 AND ra.project_id = P_FP_COLS_REC.X_PROJECT_ID; /* Added for Bug 4546405 */
1056 END IF; -- timephased check
1057
1058 IF p_pa_debug_mode = 'Y' THEN
1059 pa_fp_gen_amount_utils.fp_debug
1060 ( p_msg => 'Value of l_pfc_burdened_cost when the '
1061 || 'plan_class_code is FORECAST: '
1062 || l_pfc_burdened_cost,
1063 p_module_name => l_module_name,
1064 p_log_level => 5 );
1065 END IF;
1066 /* dbms_output.put_line('Value of l_pfc_burdened_cost when the plan_class_code is FORECAST:'
1067 ||l_pfc_burdened_cost);*/
1068
1069 -- Bug 4549862: Moved subtraction of actual revenue sum from
1070 -- total project value to an earlier point in the code to avoid
1071 -- duplicating shared code for the resource schedule and
1072 -- non-resource-schedule flows.
1073
1074 END IF; -- plan class code check
1075
1076 END IF; -- resource schedule flow check
1077
1078 -- Bug 4549862: Combined the three IF conditions (each in a separate
1079 -- set of parenthesis) into a single IF statement to avoid repeating
1080 -- the code to call the PUSH_RES_SCH_DATA_TO_BL API. Previously, each
1081 -- IF block performed the same logic to RETURN from this procedure.
1082
1083 IF ( l_budget_line_id_tab.count = 0 ) OR
1084 ( l_cost_or_rev_code = 'COST' AND l_pfc_burdened_cost = 0 ) OR
1085 ( l_cost_or_rev_code = 'REVENUE' AND l_pfc_revenue = 0 ) THEN
1086
1087 -- Bug 4549862: If the source is Staffing Plan, then all the budget
1088 -- line (quantity and cost) data is being stored in PA_FP_ROLLUP_TMP.
1089 -- Before returning from this API, call PUSH_RES_SCH_DATA_TO_BL to
1090 -- Insert/Update data from the temp table to the budget lines.
1091
1092 IF l_gen_src_code = 'RESOURCE_SCHEDULE' THEN
1093 IF P_PA_DEBUG_MODE = 'Y' THEN
1094 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1095 ( P_MSG => 'Before calling PA_FP_REV_GEN_PUB.'
1096 ||'PUSH_RES_SCH_DATA_TO_BL',
1097 P_MODULE_NAME => l_module_name,
1098 P_LOG_LEVEL => 5 );
1099 END IF;
1100 PA_FP_REV_GEN_PUB.PUSH_RES_SCH_DATA_TO_BL
1101 ( P_BUDGET_VERSION_ID => p_budget_version_id,
1102 P_FP_COLS_REC => p_fp_cols_rec,
1103 P_ETC_START_DATE => p_etc_start_date,
1104 P_PLAN_CLASS_CODE => l_plan_class_code,
1105 X_RETURN_STATUS => x_return_status,
1106 X_MSG_COUNT => x_msg_count,
1107 X_MSG_DATA => x_msg_data );
1108 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1109 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1110 END IF;
1111 IF P_PA_DEBUG_MODE = 'Y' THEN
1112 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1113 ( P_MSG => 'After calling PA_FP_REV_GEN_PUB.'
1114 ||'PUSH_RES_SCH_DATA_TO_BL',
1115 P_MODULE_NAME => l_module_name,
1116 P_LOG_LEVEL => 5);
1117 END IF;
1118 END IF; -- insert/update temp table data to budget lines
1119
1120 IF P_PA_DEBUG_MODE = 'Y' THEN
1121 PA_DEBUG.Reset_Curr_Function;
1122 END IF;
1123 RETURN;
1124 END IF;
1125
1126 IF l_cost_or_rev_code = 'COST' THEN
1127 l_ratio := l_pfc_project_value/l_pfc_burdened_cost;
1128 l_ratio_pc := l_pc_project_value/l_pc_burdened_cost;
1129 ELSIF l_cost_or_rev_code = 'REVENUE' THEN
1130 l_ratio := l_pfc_project_value/l_pfc_revenue;
1131 l_ratio_pc := l_pc_project_value/l_pc_revenue;
1132 END IF;
1133
1134 IF p_pa_debug_mode = 'Y' THEN
1135 pa_fp_gen_amount_utils.fp_debug
1136 ( p_msg => 'Value of l_ratio, l_ratio_pc:'
1137 ||l_ratio||','||l_ratio_pc,
1138 p_module_name => l_module_name,
1139 p_log_level => 5 );
1140 END IF;
1141
1142 -- IPM : Remove any records with zero burdened cost (which have already
1143 -- had NVL applied). This will avoid erroneously stamping revenue
1144 -- as 0 in certain cases.
1145
1146 l_remove_records_flag := 'N';
1147 -- Initialize l_remove_record_flag_tab
1148 FOR i in 1..l_budget_line_id_tab.count LOOP
1149 l_remove_record_flag_tab(i) := 'N';
1150 END LOOP;
1151
1152 FOR i in 1..l_budget_line_id_tab.count LOOP
1153 -- Note that the IF condition below will not result in a no_data_found
1154 -- on l_txn_revenue_tab(i) as long as the restriction is in place that
1155 -- generation of Revenue-only version from Staffing Plan is not supported.
1156 -- This is guaranteed by Case 2 of the Validate_Support_Cases() API.
1157 IF ( l_cost_or_rev_code = 'COST' AND l_txn_burdened_cost_tab(i) = 0 ) OR
1158 ( l_cost_or_rev_code = 'REVENUE' AND l_txn_revenue_tab(i) = 0 ) THEN
1159 l_remove_record_flag_tab(i) := 'Y';
1160 l_remove_records_flag := 'Y';
1161 END IF;
1162 END LOOP;
1163
1164 IF l_remove_records_flag = 'Y' THEN
1165
1166 -- 0. Clear out any data in the _tmp_ tables.
1167 l_tmp_budget_line_id_tab.delete;
1168 l_tmp_txn_burdened_cost_tab.delete;
1169 l_tmp_pc_burdened_cost_tab.delete;
1170 l_tmp_burdened_cost_tab.delete;
1171 l_tmp_quantity_tab.delete;
1172 l_tmp_txn_raw_cost_tab.delete;
1173 l_tmp_pc_raw_cost_tab.delete;
1174 l_tmp_pfc_raw_cost_tab.delete;
1175 l_tmp_res_asg_id_tab.delete;
1176 l_tmp_start_date_tab.delete;
1177 l_tmp_txn_currency_code_tab.delete;
1178
1179 IF l_gen_src_code <> 'RESOURCE_SCHEDULE' THEN
1180 l_tmp_txn_revenue_tab.delete;
1181 l_tmp_project_revenue_tab.delete;
1182 l_tmp_revenue_tab.delete;
1183 END IF;
1184
1185 -- 1. Copy records into _tmp_ tables
1186 l_tmp_index := 0;
1187 FOR i IN 1..l_budget_line_id_tab.count LOOP
1188 IF l_remove_record_flag_tab(i) <> 'Y' THEN
1189 l_tmp_index := l_tmp_index + 1;
1190 l_tmp_budget_line_id_tab(l_tmp_index) := l_budget_line_id_tab(i);
1191 l_tmp_txn_burdened_cost_tab(l_tmp_index) := l_txn_burdened_cost_tab(i);
1192 l_tmp_pc_burdened_cost_tab(l_tmp_index) := l_pc_burdened_cost_tab(i);
1193 l_tmp_burdened_cost_tab(l_tmp_index) := l_burdened_cost_tab(i);
1194 l_tmp_quantity_tab(l_tmp_index) := l_quantity_tab(i);
1195 l_tmp_txn_raw_cost_tab(l_tmp_index) := l_txn_raw_cost_tab(i);
1196 l_tmp_pc_raw_cost_tab(l_tmp_index) := l_pc_raw_cost_tab(i);
1197 l_tmp_pfc_raw_cost_tab(l_tmp_index) := l_pfc_raw_cost_tab(i);
1198 l_tmp_res_asg_id_tab(l_tmp_index) := l_res_asg_id_tab(i);
1199 l_tmp_start_date_tab(l_tmp_index) := l_start_date_tab(i);
1200 l_tmp_txn_currency_code_tab(l_tmp_index) := l_txn_currency_code_tab(i);
1201
1202 IF l_gen_src_code <> 'RESOURCE_SCHEDULE' THEN
1203 l_tmp_txn_revenue_tab(l_tmp_index) := l_txn_revenue_tab(i);
1204 l_tmp_project_revenue_tab(l_tmp_index) := l_project_revenue_tab(i);
1205 l_tmp_revenue_tab(l_tmp_index) := l_revenue_tab(i);
1206 END IF;
1207 END IF;
1208 END LOOP;
1209
1210 -- 2. Copy records from _tmp_ tables back to non-temporary tables.
1211 l_budget_line_id_tab := l_tmp_budget_line_id_tab;
1212 l_txn_burdened_cost_tab := l_tmp_txn_burdened_cost_tab;
1213 l_pc_burdened_cost_tab := l_tmp_pc_burdened_cost_tab;
1214 l_burdened_cost_tab := l_tmp_burdened_cost_tab;
1215 l_quantity_tab := l_tmp_quantity_tab;
1216 l_txn_raw_cost_tab := l_tmp_txn_raw_cost_tab;
1217 l_pc_raw_cost_tab := l_tmp_pc_raw_cost_tab;
1218 l_pfc_raw_cost_tab := l_tmp_pfc_raw_cost_tab;
1219 l_res_asg_id_tab := l_tmp_res_asg_id_tab;
1220 l_start_date_tab := l_tmp_start_date_tab;
1221 l_txn_currency_code_tab := l_tmp_txn_currency_code_tab;
1222
1223 IF l_gen_src_code <> 'RESOURCE_SCHEDULE' THEN
1224 l_txn_revenue_tab := l_tmp_txn_revenue_tab;
1225 l_project_revenue_tab := l_tmp_project_revenue_tab;
1226 l_revenue_tab := l_tmp_revenue_tab;
1227 END IF;
1228
1229 END IF; -- IPM record removal logic
1230
1231
1232 -- Bug 4096111: Relaxed restriction on when we match the total
1233 -- revenue to the Project Opportunity Value; we now also do the
1234 -- matching logic whenever the target is a Revenue version. Also,
1235 -- reordered the IF and ELSE blocks and conditions.
1236
1237 --dbms_output.put_line('Value of l_ratio:'||l_ratio);
1238
1239 IF ( p_fp_cols_rec.x_plan_in_multi_curr_flag = 'N' OR
1240 l_appr_cost_plan_type_flag = 'Y' OR
1241 l_appr_rev_plan_type_flag = 'Y' OR
1242 p_fp_cols_rec.x_version_type = 'REVENUE' ) THEN
1243
1244 FOR i in 1..l_budget_line_id_tab.count LOOP
1245 IF l_cost_or_rev_code = 'COST' THEN
1246 l_txn_rev_tab(i) := l_txn_burdened_cost_tab(i) * l_ratio;
1247 l_pc_rev_tab(i) := l_pc_burdened_cost_tab(i) * l_ratio_pc;
1248 ELSIF l_cost_or_rev_code = 'REVENUE' THEN
1249 l_txn_rev_tab(i) := l_txn_revenue_tab(i) * l_ratio;
1250 l_pc_rev_tab(i) := l_project_revenue_tab(i) * l_ratio_pc;
1251 END IF;
1252
1253 /*Handling rounding - Start*/
1254 l_txn_rev_tab(i) := pa_currency.round_trans_currency_amt1
1255 (x_amount => l_txn_rev_tab(i),
1256 x_curr_Code => l_txn_currency_code_tab(i));
1257 l_pc_rev_tab(i) := pa_currency.round_trans_currency_amt1
1258 (x_amount => l_pc_rev_tab(i),
1259 x_curr_Code => P_FP_COLS_REC.X_PROJECT_CURRENCY_CODE);
1260 /*Handling rounding - End*/
1261
1262 l_running_txn_rev := l_running_txn_rev + l_txn_rev_tab(i);
1263 l_running_pc_rev := l_running_pc_rev + l_pc_rev_tab(i);
1264
1265 IF i = l_budget_line_id_tab.count THEN
1266 IF l_running_txn_rev <> l_pfc_project_value THEN
1267 l_diff := l_pfc_project_value -l_running_txn_rev ;
1268 l_txn_rev_tab(i) := l_txn_rev_tab(i) + l_diff;
1269 END IF;
1270 IF l_running_pc_rev <> l_pc_project_value THEN
1271 l_diff := l_pc_project_value -l_running_pc_rev ;
1272 l_pc_rev_tab(i) := l_pc_rev_tab(i) + l_diff;
1273 END IF;
1274 END IF;
1275
1276 IF l_txn_rev_tab(i) <> 0 THEN
1277 l_rev_pc_exchg_rate_tab(i) := l_pc_rev_tab(i)/l_txn_rev_tab(i);
1278 ELSE
1279 l_rev_pc_exchg_rate_tab(i) := NULL;
1280 END IF;
1281
1282 IF l_quantity_tab(i) <> 0 THEN
1283 IF l_cost_or_rev_code = 'COST' THEN
1284 l_txn_bill_rate_override_tab(i):= l_txn_rev_tab(i)/l_quantity_tab(i);
1285 ELSIF l_cost_or_rev_code = 'REVENUE' THEN
1286 l_txn_bill_rate_override_tab(i):= 1;
1287 END IF;
1288 ELSE
1289 l_txn_bill_rate_override_tab(i):= NULL;
1290 END IF;
1291 END LOOP;
1292
1293 IF l_gen_src_code = 'RESOURCE_SCHEDULE' THEN
1294
1295 FORALL j in 1..l_budget_line_id_tab.count
1296 UPDATE pa_fp_rollup_tmp
1297 SET txn_revenue = l_txn_rev_tab(j),
1298 projfunc_revenue = l_txn_rev_tab(j),
1299 project_revenue = l_pc_rev_tab(j),
1300 projfunc_rev_rate_type = 'User',
1301 project_rev_rate_type = 'User'
1302 WHERE budget_line_id = l_budget_line_id_tab(j);
1303
1304 ELSE -- l_gen_src_code <> 'RESOURCE_SCHEDULE'
1305
1306 IF l_cost_or_rev_code = 'COST' THEN
1307 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
1308 FORALL j in 1..l_budget_line_id_tab.count
1309 UPDATE pa_budget_lines
1310 SET quantity = l_quantity_tab(j),
1311 txn_revenue = l_txn_rev_tab(j),
1312 txn_bill_rate_override = l_txn_bill_rate_override_tab(j),
1313 revenue = l_txn_rev_tab(j),
1314 projfunc_rev_rate_type = 'User',
1315 projfunc_rev_exchange_rate = 1,
1316 project_revenue = l_pc_rev_tab(j),
1317 project_rev_rate_type = 'User',
1318 project_rev_exchange_rate = l_rev_pc_exchg_rate_tab(j)
1319 WHERE budget_line_id = l_budget_line_id_tab(j);
1320 -- Bug 4292083: As a result of changes for this bug, we now maintain
1321 -- the invariant that planned columns always store the Total amount.
1322 -- We need to add Actuals to Plan amounts.
1323 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
1324 FORALL j in 1..l_budget_line_id_tab.count
1325 UPDATE pa_budget_lines
1326 SET quantity = NVL(init_quantity,0) + l_quantity_tab(j),
1327 txn_revenue = NVL(txn_init_revenue,0) + l_txn_rev_tab(j),
1328 txn_bill_rate_override = l_txn_bill_rate_override_tab(j),
1329 revenue = NVL(init_revenue,0) + l_txn_rev_tab(j),
1330 projfunc_rev_rate_type = 'User',
1331 projfunc_rev_exchange_rate = 1,
1332 project_revenue = NVL(project_init_revenue,0) + l_pc_rev_tab(j),
1333 project_rev_rate_type = 'User',
1334 project_rev_exchange_rate = l_rev_pc_exchg_rate_tab(j)
1335 WHERE budget_line_id = l_budget_line_id_tab(j);
1336 END IF; -- time phase check
1337 ELSIF l_cost_or_rev_code = 'REVENUE' THEN
1338 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
1339 FORALL jj in 1..l_budget_line_id_tab.count
1340 UPDATE pa_budget_lines
1341 SET quantity = l_txn_rev_tab(jj),
1342 txn_revenue = l_txn_rev_tab(jj),
1343 txn_bill_rate_override = l_txn_bill_rate_override_tab(jj),
1344 revenue = l_txn_rev_tab(jj),
1345 projfunc_rev_rate_type = 'User',
1346 projfunc_rev_exchange_rate = 1,
1347 project_revenue = l_pc_rev_tab(jj),
1348 project_rev_rate_type = 'User',
1349 project_rev_exchange_rate = l_rev_pc_exchg_rate_tab(jj)
1350 WHERE budget_line_id = l_budget_line_id_tab(jj);
1351 -- Bug 4292083: As a result of changes for this bug, we now maintain
1352 -- the invariant that planned columns always store the Total amount.
1353 -- We need to add Actuals to Plan amounts.
1354 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
1355 FORALL jj in 1..l_budget_line_id_tab.count
1356 UPDATE pa_budget_lines
1357 SET quantity = NVL(init_quantity,0) + l_txn_rev_tab(jj),
1358 txn_revenue = NVL(txn_init_revenue,0) + l_txn_rev_tab(jj),
1359 txn_bill_rate_override = l_txn_bill_rate_override_tab(jj),
1360 revenue = NVL(init_revenue,0) + l_txn_rev_tab(jj),
1361 projfunc_rev_rate_type = 'User',
1362 projfunc_rev_exchange_rate = 1,
1363 project_revenue = NVL(project_init_revenue,0) + l_pc_rev_tab(jj),
1364 project_rev_rate_type = 'User',
1365 project_rev_exchange_rate = l_rev_pc_exchg_rate_tab(jj)
1366 WHERE budget_line_id = l_budget_line_id_tab(jj);
1367 END IF; -- time phase check
1368 END IF;
1369 IF p_pa_debug_mode = 'Y' THEN
1370 pa_fp_gen_amount_utils.fp_debug
1371 ( p_msg => 'No. of rows updated in bdgt_lines '
1372 || 'table when multi_curr_flag is N: '
1373 || sql%rowcount,
1374 p_module_name => l_module_name,
1375 p_log_level => 5 );
1376 END IF;
1377 /* dbms_output.put_line('No. of rows updated in bdgt_lines table when multi_curr_flag is N: '
1378 ||sql%rowcount);*/
1379
1380 END IF; -- l_gen_src_code = 'RESOURCE_SCHEDULE' check
1381
1382 ELSE -- process without matching the Project Opp Value
1383
1384 IF l_cost_or_rev_code = 'COST' THEN
1385 FOR i in 1..l_budget_line_id_tab.count LOOP
1386 l_txn_rev_tab(i) := l_txn_burdened_cost_tab(i) * l_ratio;
1387 l_pc_rev_tab(i) := l_pc_burdened_cost_tab(i) * l_ratio_pc;
1388 l_pfc_rev_tab(i) := l_burdened_cost_tab(i) * l_ratio;
1389 END LOOP;
1390 ELSIF l_cost_or_rev_code = 'REVENUE' THEN
1391 FOR i in 1..l_budget_line_id_tab.count LOOP
1392 l_txn_rev_tab(i) := l_txn_revenue_tab(i) * l_ratio;
1393 l_pc_rev_tab(i) := l_project_revenue_tab(i) * l_ratio_pc;
1394 l_pfc_rev_tab(i) := l_revenue_tab(i) * l_ratio;
1395 END LOOP;
1396 END IF;
1397 IF p_pa_debug_mode = 'Y' THEN
1398 pa_fp_gen_amount_utils.fp_debug
1399 ( p_msg => 'Value of l_txn_rev_tab.count:'||l_txn_rev_tab.count,
1400 p_module_name => l_module_name,
1401 p_log_level => 5 );
1402 END IF;
1403
1404 FOR j in 1..l_txn_rev_tab.count LOOP
1405 IF l_txn_rev_tab(j) <> 0 THEN
1406 l_rev_pc_exchg_rate_tab(j) := l_pc_rev_tab(j)/l_txn_rev_tab(j);
1407 l_rev_pfc_exchg_rate_tab(j) := l_pfc_rev_tab(j)/l_txn_rev_tab(j);
1408 ELSE
1409 l_rev_pc_exchg_rate_tab(j) := NULL;
1410 l_rev_pfc_exchg_rate_tab(j) := NULL;
1411 END IF;
1412
1413 IF l_quantity_tab(j) <> 0 THEN
1414 IF l_cost_or_rev_code = 'COST' THEN
1415 l_txn_bill_rate_override_tab(j):= l_txn_rev_tab(j)/l_quantity_tab(j);
1416 ELSIF l_cost_or_rev_code = 'REVENUE' THEN
1417 l_txn_bill_rate_override_tab(j):= 1;
1418 END IF;
1419 ELSE
1420 l_txn_bill_rate_override_tab(j):= NULL;
1421 END IF;
1422
1423 /*Handling rounding - Start*/
1424 l_txn_rev_tab(j) := pa_currency.round_trans_currency_amt1
1425 (x_amount => l_txn_rev_tab(j),
1426 x_curr_Code => l_txn_currency_code_tab(j));
1427 /*Handling rounding - End*/
1428 END LOOP;
1429
1430 IF l_gen_src_code = 'RESOURCE_SCHEDULE' THEN
1431
1432 FORALL k in 1..l_budget_line_id_tab.count
1433 UPDATE pa_fp_rollup_tmp
1434 SET txn_revenue = l_txn_rev_tab(k),
1435 projfunc_revenue = l_pfc_rev_tab(k),
1436 project_revenue = l_pc_rev_tab(k),
1437 projfunc_rev_rate_type = 'User',
1438 project_rev_rate_type = 'User'
1439 WHERE budget_line_id = l_budget_line_id_tab(k);
1440
1441 ELSE -- l_gen_src_code <> 'RESOURCE_SCHEDULE'
1442
1443 IF l_cost_or_rev_code = 'COST' THEN
1444
1445 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
1446 FORALL k in 1..l_budget_line_id_tab.count
1447 UPDATE pa_budget_lines
1448 SET quantity = l_quantity_tab(k),
1449 txn_revenue = l_txn_rev_tab(k),
1450 txn_bill_rate_override = l_txn_bill_rate_override_tab(k),
1451 project_rev_rate_type = 'User',
1452 project_rev_exchange_rate = l_rev_pc_exchg_rate_tab(k),
1453 projfunc_rev_rate_type = 'User',
1454 projfunc_rev_exchange_rate = l_rev_pfc_exchg_rate_tab(k)
1455 WHERE budget_line_id = l_budget_line_id_tab(k);
1456 -- Bug 4292083: As a result of changes for this bug, we now maintain
1457 -- the invariant that planned columns always store the Total amount.
1458 -- We need to add Actuals to Plan amounts.
1459 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
1460 FORALL k in 1..l_budget_line_id_tab.count
1461 UPDATE pa_budget_lines
1462 SET quantity = NVL(init_quantity,0) + l_quantity_tab(k),
1463 txn_revenue = NVL(txn_init_revenue,0) + l_txn_rev_tab(k),
1464 txn_bill_rate_override = l_txn_bill_rate_override_tab(k),
1465 project_rev_rate_type = 'User',
1466 project_rev_exchange_rate = l_rev_pc_exchg_rate_tab(k),
1467 projfunc_rev_rate_type = 'User',
1468 projfunc_rev_exchange_rate = l_rev_pfc_exchg_rate_tab(k)
1469 WHERE budget_line_id = l_budget_line_id_tab(k);
1470 END IF; -- time phase check
1471 ELSIF l_cost_or_rev_code = 'REVENUE' THEN
1472 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
1473 FORALL kk in 1..l_budget_line_id_tab.count
1474 UPDATE pa_budget_lines
1475 SET quantity = l_txn_rev_tab(kk),
1476 txn_revenue = l_txn_rev_tab(kk),
1477 txn_bill_rate_override = l_txn_bill_rate_override_tab(kk),
1478 project_rev_rate_type = 'User',
1479 project_rev_exchange_rate = l_rev_pc_exchg_rate_tab(kk),
1480 projfunc_rev_rate_type = 'User',
1481 projfunc_rev_exchange_rate = l_rev_pfc_exchg_rate_tab(kk)
1482 WHERE budget_line_id = l_budget_line_id_tab(kk);
1483 -- Bug 4292083: As a result of changes for this bug, we now maintain
1484 -- the invariant that planned columns always store the Total amount.
1485 -- We need to add Actuals to Plan amounts.
1486 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
1487 FORALL kk in 1..l_budget_line_id_tab.count
1488 UPDATE pa_budget_lines
1489 SET quantity = NVL(init_quantity,0) + l_txn_rev_tab(kk),
1490 txn_revenue = NVL(txn_init_revenue,0) + l_txn_rev_tab(kk),
1491 txn_bill_rate_override = l_txn_bill_rate_override_tab(kk),
1492 project_rev_rate_type = 'User',
1493 project_rev_exchange_rate = l_rev_pc_exchg_rate_tab(kk),
1494 projfunc_rev_rate_type = 'User',
1495 projfunc_rev_exchange_rate = l_rev_pfc_exchg_rate_tab(kk)
1496 WHERE budget_line_id = l_budget_line_id_tab(kk);
1497 END IF; -- time phase check
1498 END IF;
1499 IF p_pa_debug_mode = 'Y' THEN
1500 pa_fp_gen_amount_utils.fp_debug
1501 ( p_msg => 'No. of rows updated in bdgt_lines table '
1502 || 'when multi_curr_flag is Y: '||sql%rowcount,
1503 p_module_name => l_module_name,
1504 p_log_level => 5 );
1505 END IF;
1506
1507 END IF; -- l_gen_src_code = 'RESOURCE_SCHEDULE' check
1508
1509 END IF; -- revenue calculation
1510
1511
1512 -- Bug 4549862: At this point, revenue amounts have been computed.
1513 -- If the source is Staffing Plan, then all the budget line data
1514 -- is stored in PA_FP_ROLLUP_TMP. Before returning from this API,
1515 -- we need to call PUSH_RES_SCH_DATA_TO_BL to Insert/Update data
1516 -- from the temp table to the budget lines.
1517
1518 IF l_gen_src_code = 'RESOURCE_SCHEDULE' THEN
1519
1520 IF P_PA_DEBUG_MODE = 'Y' THEN
1521 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1522 ( P_MSG => 'Before calling PA_FP_REV_GEN_PUB.'
1523 ||'PUSH_RES_SCH_DATA_TO_BL',
1524 P_MODULE_NAME => l_module_name,
1525 P_LOG_LEVEL => 5 );
1526 END IF;
1527 PA_FP_REV_GEN_PUB.PUSH_RES_SCH_DATA_TO_BL
1528 ( P_BUDGET_VERSION_ID => p_budget_version_id,
1529 P_FP_COLS_REC => p_fp_cols_rec,
1530 P_ETC_START_DATE => p_etc_start_date,
1531 P_PLAN_CLASS_CODE => l_plan_class_code,
1532 X_RETURN_STATUS => x_return_status,
1533 X_MSG_COUNT => x_msg_count,
1534 X_MSG_DATA => x_msg_data );
1535 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1536 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1537 END IF;
1538 IF P_PA_DEBUG_MODE = 'Y' THEN
1539 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
1540 ( P_MSG => 'After calling PA_FP_REV_GEN_PUB.'
1541 ||'PUSH_RES_SCH_DATA_TO_BL',
1542 P_MODULE_NAME => l_module_name,
1543 P_LOG_LEVEL => 5);
1544 END IF;
1545
1546 END IF; -- insert/update temp table data to budget lines
1547
1548 /* IF p_fp_cols_rec.x_version_type = 'REVENUE' THEN
1549 UPDATE pa_budget_lines
1550 SET quantity = null
1551 WHERE budget_version_id = p_budget_version_id;
1552 END IF; */
1553
1554 IF P_PA_DEBUG_MODE = 'Y' THEN
1555 PA_DEBUG.Reset_Curr_Function;
1556 END IF;
1557
1558 EXCEPTION
1559 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1560 /** MRC Elimination changes: PA_MRC_FINPLAN.G_CALLING_MODULE := Null; **/
1561 l_msg_count := FND_MSG_PUB.count_msg;
1562
1563 IF l_msg_count = 1 THEN
1564 PA_INTERFACE_UTILS_PUB.get_messages
1565 ( p_encoded => FND_API.G_TRUE
1566 ,p_msg_index => 1
1567 ,p_msg_count => l_msg_count
1568 ,p_msg_data => l_msg_data
1569 ,p_data => l_data
1570 ,p_msg_index_out => l_msg_index_out );
1571 x_msg_data := l_data;
1572 x_msg_count := l_msg_count;
1573 ELSE
1574 x_msg_count := l_msg_count;
1575 END IF;
1576 ROLLBACK;
1577
1578 x_return_status := FND_API.G_RET_STS_ERROR;
1579
1580 IF P_PA_DEBUG_MODE = 'Y' THEN
1581 PA_DEBUG.Reset_Curr_Function;
1582 END IF;
1583
1584 RAISE;
1585
1586 WHEN OTHERS THEN
1587 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1588 x_msg_data := SUBSTR(SQLERRM,1,240);
1589 FND_MSG_PUB.add_exc_msg
1590 ( p_pkg_name => 'PA_FP_REV_GEN_PUB'
1591 ,p_procedure_name => 'GEN_COST_BASED_REVENUE' );
1592
1593 IF P_PA_DEBUG_MODE = 'Y' THEN
1594 PA_DEBUG.Reset_Curr_Function;
1595 END IF;
1596
1597 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1598
1599 END GEN_COST_BASED_REVENUE;
1600
1601 /**
1602 * Created as part of fix for Bug 4549862.
1603 *
1604 * This private procedure is meant to be used by GEN_COST_BASED_REVENUE
1605 * when generating a Cost and Revenue together version with source of
1606 * Staffing Plan and revenue accrual method of COST.
1607 *
1608 * This procedure propagates generation data stored in PA_FP_ROLLUP_TMP
1609 * and Inserts/Updates it into PA_BUDGET_LINES. This includes txn/pc/pfc
1610 * amounts, rate overrides, pc/pfc exchange rates, cost/revenue rate types,
1611 * and rejection codes.
1612 *
1613 * This API should always be called by GEN_COST_BASED_REVENUE before
1614 * returning with return status of Success.
1615 **/
1616 PROCEDURE PUSH_RES_SCH_DATA_TO_BL
1617 (P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
1618 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
1619 P_ETC_START_DATE IN PA_BUDGET_VERSIONS.ETC_START_DATE%TYPE,
1620 P_PLAN_CLASS_CODE IN PA_FIN_PLAN_TYPES_B.PLAN_CLASS_CODE%TYPE,
1621 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1622 X_MSG_COUNT OUT NOCOPY NUMBER,
1623 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
1624
1625 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_REV_GEN_PUB.PUSH_RES_SCH_DATA_TO_BL';
1626
1627 l_msg_count NUMBER;
1628 l_msg_data VARCHAR2(2000);
1629 l_data VARCHAR2(2000);
1630 l_msg_index_out NUMBER:=0;
1631
1632 -- This cursor should be used when the target version is
1633 -- timephased by either PA or GL.
1634
1635 CURSOR GROUP_TO_INS_INTO_BL IS
1636 SELECT RESOURCE_ASSIGNMENT_ID,
1637 TXN_CURRENCY_CODE,
1638 START_DATE,
1639 END_DATE,
1640 PERIOD_NAME,
1641 SUM(QUANTITY),
1642 SUM(TXN_RAW_COST),
1643 SUM(TXN_BURDENED_COST),
1644 SUM(TXN_REVENUE),
1645 SUM(PROJECT_RAW_COST),
1646 SUM(PROJECT_BURDENED_COST),
1647 SUM(PROJECT_REVENUE),
1648 SUM(PROJFUNC_RAW_COST),
1649 SUM(PROJFUNC_BURDENED_COST),
1650 SUM(PROJFUNC_REVENUE),
1651 COST_REJECTION_CODE,
1652 BURDEN_REJECTION_CODE,
1653 PC_CUR_CONV_REJECTION_CODE,
1654 PFC_CUR_CONV_REJECTION_CODE,
1655 PROJECT_COST_RATE_TYPE,
1656 PROJFUNC_COST_RATE_TYPE,
1657 PROJECT_REV_RATE_TYPE,
1658 PROJFUNC_REV_RATE_TYPE
1659 FROM pa_fp_rollup_tmp
1660 GROUP BY resource_assignment_id,
1661 txn_currency_code,
1662 start_date,
1663 end_date,
1664 period_name,
1665 COST_REJECTION_CODE,
1666 BURDEN_REJECTION_CODE,
1667 PC_CUR_CONV_REJECTION_CODE,
1668 PFC_CUR_CONV_REJECTION_CODE,
1669 PROJECT_COST_RATE_TYPE,
1670 PROJFUNC_COST_RATE_TYPE,
1671 PROJECT_REV_RATE_TYPE,
1672 PROJFUNC_REV_RATE_TYPE;
1673
1674 -- This cursor should be used when the target version is
1675 -- a Budget and None timephased.
1676 -- Assumptions:
1677 -- 1. period_name should be populated as NULL
1678
1679 CURSOR GROUP_TO_INS_INTO_NTP_BDGT_BL IS
1680 SELECT RESOURCE_ASSIGNMENT_ID,
1681 TXN_CURRENCY_CODE,
1682 MIN(START_DATE),
1683 MAX(END_DATE),
1684 PERIOD_NAME,
1685 SUM(QUANTITY),
1686 SUM(TXN_RAW_COST),
1687 SUM(TXN_BURDENED_COST),
1688 SUM(TXN_REVENUE),
1689 SUM(PROJECT_RAW_COST),
1690 SUM(PROJECT_BURDENED_COST),
1691 SUM(PROJECT_REVENUE),
1692 SUM(PROJFUNC_RAW_COST),
1693 SUM(PROJFUNC_BURDENED_COST),
1694 SUM(PROJFUNC_REVENUE),
1695 COST_REJECTION_CODE,
1696 BURDEN_REJECTION_CODE,
1697 PC_CUR_CONV_REJECTION_CODE,
1698 PFC_CUR_CONV_REJECTION_CODE,
1699 PROJECT_COST_RATE_TYPE,
1700 PROJFUNC_COST_RATE_TYPE,
1701 PROJECT_REV_RATE_TYPE,
1702 PROJFUNC_REV_RATE_TYPE
1703 FROM pa_fp_rollup_tmp
1704 GROUP BY resource_assignment_id,
1705 txn_currency_code,
1706 period_name,
1707 COST_REJECTION_CODE,
1708 BURDEN_REJECTION_CODE,
1709 PC_CUR_CONV_REJECTION_CODE,
1710 PFC_CUR_CONV_REJECTION_CODE,
1711 PROJECT_COST_RATE_TYPE,
1712 PROJFUNC_COST_RATE_TYPE,
1713 PROJECT_REV_RATE_TYPE,
1714 PROJFUNC_REV_RATE_TYPE;
1715
1716 -- Bug 4549862: Added cursor for when the target version is None
1717 -- Time Phased and the context is Forecast Generation. In this
1718 -- case, budget lines may exist with actuals. Fetch temp table
1719 -- data for which budget lines do not yet exist, which should be
1720 -- Inserted into pa_budget_lines.
1721 -- The query is the same as that of GROUP_TO_INS_INTO_NTP_BDGT_BL
1722 -- but with an additional HAVING clause to find Insert records.
1723
1724 CURSOR GROUP_TO_INS_INTO_NTP_FCST_BL IS
1725 SELECT RESOURCE_ASSIGNMENT_ID,
1726 TXN_CURRENCY_CODE,
1727 MIN(START_DATE),
1728 MAX(END_DATE),
1729 PERIOD_NAME,
1730 SUM(QUANTITY),
1731 SUM(TXN_RAW_COST),
1732 SUM(TXN_BURDENED_COST),
1733 SUM(TXN_REVENUE),
1734 SUM(PROJECT_RAW_COST),
1735 SUM(PROJECT_BURDENED_COST),
1736 SUM(PROJECT_REVENUE),
1737 SUM(PROJFUNC_RAW_COST),
1738 SUM(PROJFUNC_BURDENED_COST),
1739 SUM(PROJFUNC_REVENUE),
1740 COST_REJECTION_CODE,
1741 BURDEN_REJECTION_CODE,
1742 PC_CUR_CONV_REJECTION_CODE,
1743 PFC_CUR_CONV_REJECTION_CODE,
1744 PROJECT_COST_RATE_TYPE,
1745 PROJFUNC_COST_RATE_TYPE,
1746 PROJECT_REV_RATE_TYPE,
1747 PROJFUNC_REV_RATE_TYPE
1748 FROM pa_fp_rollup_tmp tmp
1749 GROUP BY resource_assignment_id,
1750 txn_currency_code,
1751 period_name,
1752 COST_REJECTION_CODE,
1753 BURDEN_REJECTION_CODE,
1754 PC_CUR_CONV_REJECTION_CODE,
1755 PFC_CUR_CONV_REJECTION_CODE,
1756 PROJECT_COST_RATE_TYPE,
1757 PROJFUNC_COST_RATE_TYPE,
1758 PROJECT_REV_RATE_TYPE,
1759 PROJFUNC_REV_RATE_TYPE
1760 HAVING ( SELECT count(*)
1761 FROM pa_budget_lines bl
1762 WHERE tmp.resource_assignment_id = bl.resource_assignment_id
1763 AND tmp.txn_currency_code = bl.txn_currency_code ) = 0;
1764
1765 -- Bug 4549862: Added cursor for when the target version is None
1766 -- Time Phased and the context is Forecast Generation. In this
1767 -- case, budget lines may exist with actuals. Fetch temp table
1768 -- data for which budget lines exist, which whould be Updated
1769 -- into pa_budget_lines.
1770 -- The query is the same as that of GROUP_TO_INS_INTO_NTP_BDGT_BL
1771 -- but with an additional HAVING clause to find Update records.
1772
1773 CURSOR GROUP_TO_UPD_INTO_NTP_FCST_BL IS
1774 SELECT RESOURCE_ASSIGNMENT_ID,
1775 TXN_CURRENCY_CODE,
1776 MIN(START_DATE),
1777 MAX(END_DATE),
1778 PERIOD_NAME,
1779 SUM(QUANTITY),
1780 SUM(TXN_RAW_COST),
1781 SUM(TXN_BURDENED_COST),
1782 SUM(TXN_REVENUE),
1783 SUM(PROJECT_RAW_COST),
1784 SUM(PROJECT_BURDENED_COST),
1785 SUM(PROJECT_REVENUE),
1786 SUM(PROJFUNC_RAW_COST),
1787 SUM(PROJFUNC_BURDENED_COST),
1788 SUM(PROJFUNC_REVENUE),
1789 COST_REJECTION_CODE,
1790 BURDEN_REJECTION_CODE,
1791 PC_CUR_CONV_REJECTION_CODE,
1792 PFC_CUR_CONV_REJECTION_CODE,
1793 PROJECT_COST_RATE_TYPE,
1794 PROJFUNC_COST_RATE_TYPE,
1795 PROJECT_REV_RATE_TYPE,
1796 PROJFUNC_REV_RATE_TYPE
1797 FROM pa_fp_rollup_tmp tmp
1798 GROUP BY resource_assignment_id,
1799 txn_currency_code,
1800 period_name,
1801 COST_REJECTION_CODE,
1802 BURDEN_REJECTION_CODE,
1803 PC_CUR_CONV_REJECTION_CODE,
1804 PFC_CUR_CONV_REJECTION_CODE,
1805 PROJECT_COST_RATE_TYPE,
1806 PROJFUNC_COST_RATE_TYPE,
1807 PROJECT_REV_RATE_TYPE,
1808 PROJFUNC_REV_RATE_TYPE
1809 HAVING ( SELECT count(*)
1810 FROM pa_budget_lines bl
1811 WHERE tmp.resource_assignment_id = bl.resource_assignment_id
1812 AND tmp.txn_currency_code = bl.txn_currency_code ) > 0;
1813
1814 /* Variables added for Bug 4549862 */
1815
1816 l_bl_RES_ASSIGNMENT_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
1817 l_bl_TXN_CURRENCY_CODE_tab PA_PLSQL_DATATYPES.Char15TabTyp;
1818 l_bl_START_DATE_tab PA_PLSQL_DATATYPES.DateTabTyp;
1819 l_bl_END_DATE_tab PA_PLSQL_DATATYPES.DateTabTyp;
1820 l_bl_PERIOD_NAME_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1821 l_bl_QUANTITY_tab PA_PLSQL_DATATYPES.NumTabTyp;
1822 l_bl_TXN_RAW_COST_tab PA_PLSQL_DATATYPES.NumTabTyp;
1823 l_bl_TXN_BURDENED_COST_tab PA_PLSQL_DATATYPES.NumTabTyp;
1824 l_bl_TXN_REVENUE_tab PA_PLSQL_DATATYPES.NumTabTyp;
1825
1826 l_bl_PC_RAW_COST_tab PA_PLSQL_DATATYPES.NumTabTyp;
1827 l_bl_PC_BURDENED_COST_tab PA_PLSQL_DATATYPES.NumTabTyp;
1828 l_bl_PC_REVENUE_tab PA_PLSQL_DATATYPES.NumTabTyp;
1829 l_bl_PFC_RAW_COST_tab PA_PLSQL_DATATYPES.NumTabTyp;
1830 l_bl_PFC_BURDENED_COST_tab PA_PLSQL_DATATYPES.NumTabTyp;
1831 l_bl_PFC_REVENUE_tab PA_PLSQL_DATATYPES.NumTabTyp;
1832
1833 l_bl_COST_REJ_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1834 l_bl_BURDEN_REJ_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1835 l_bl_PC_CUR_REJ_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1836 l_bl_PFC_CUR_REJ_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1837 l_bl_PC_COST_RT_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1838 l_bl_PFC_COST_RT_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1839 l_bl_PC_REV_RT_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1840 l_bl_PFC_REV_RT_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1841
1842 l_upd_bl_RES_ASSIGNMENT_ID_tab PA_PLSQL_DATATYPES.IDTabTyp;
1843 l_upd_bl_TXN_CURRENCY_CODE_tab PA_PLSQL_DATATYPES.Char15TabTyp;
1844 l_upd_bl_START_DATE_tab PA_PLSQL_DATATYPES.DateTabTyp;
1845 l_upd_bl_END_DATE_tab PA_PLSQL_DATATYPES.DateTabTyp;
1846 l_upd_bl_PERIOD_NAME_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1847 l_upd_bl_QUANTITY_tab PA_PLSQL_DATATYPES.NumTabTyp;
1848 l_upd_bl_TXN_RAW_COST_tab PA_PLSQL_DATATYPES.NumTabTyp;
1849 l_upd_bl_TXN_BURDENED_COST_tab PA_PLSQL_DATATYPES.NumTabTyp;
1850 l_upd_bl_TXN_REVENUE_tab PA_PLSQL_DATATYPES.NumTabTyp;
1851
1852 l_upd_bl_PC_RAW_COST_tab PA_PLSQL_DATATYPES.NumTabTyp;
1853 l_upd_bl_PC_BURDENED_COST_tab PA_PLSQL_DATATYPES.NumTabTyp;
1854 l_upd_bl_PC_REVENUE_tab PA_PLSQL_DATATYPES.NumTabTyp;
1855 l_upd_bl_PFC_RAW_COST_tab PA_PLSQL_DATATYPES.NumTabTyp;
1856 l_upd_bl_PFC_BURDENED_COST_tab PA_PLSQL_DATATYPES.NumTabTyp;
1857 l_upd_bl_PFC_REVENUE_tab PA_PLSQL_DATATYPES.NumTabTyp;
1858
1859 l_upd_bl_COST_REJ_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1860 l_upd_bl_BURDEN_REJ_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1861 l_upd_bl_PC_CUR_REJ_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1862 l_upd_bl_PFC_CUR_REJ_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1863 l_upd_bl_PC_COST_RT_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1864 l_upd_bl_PFC_COST_RT_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1865 l_upd_bl_PC_REV_RT_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1866 l_upd_bl_PFC_REV_RT_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1867
1868 l_cost_pc_exchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1869 l_cost_pfc_exchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1870
1871 l_rev_pc_exchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1872 l_rev_pfc_exchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1873
1874 l_txn_rcost_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
1875 l_txn_bcost_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
1876 l_txn_bill_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
1877
1878 l_upd_cost_pc_exchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1879 l_upd_cost_pfc_exchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1880
1881 l_upd_rev_pc_exchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1882 l_upd_rev_pfc_exchg_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1883
1884 l_upd_rcost_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
1885 l_upd_bcost_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
1886 l_upd_bill_rate_override_tab PA_PLSQL_DATATYPES.NumTabTyp;
1887
1888 l_last_updated_by NUMBER := FND_GLOBAL.user_id;
1889 l_last_update_login NUMBER := FND_GLOBAL.login_id;
1890 l_sysdate DATE := SYSDATE;
1891
1892 BEGIN
1893 /* Setting initial values */
1894 X_MSG_COUNT := 0;
1895 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1896
1897 IF p_pa_debug_mode = 'Y' THEN
1898 PA_DEBUG.SET_CURR_FUNCTION
1899 ( p_function => 'PUSH_RES_SCH_DATA_TO_BL',
1900 p_debug_mode => p_pa_debug_mode );
1901 END IF;
1902
1903 -- Bug 4549862: Update pc/pfc rate types to 'User' in PA_FP_ROLLUP_TMP:
1904 -- 1. Revenue rate types will always be 'User', so no update needed.
1905 -- 2. Cost rate types for records of a given (resource assignment, txn
1906 -- currency, period) combination need to be updated if any record for
1907 -- that combination has cost rate type as 'User'. This will be the
1908 -- case if a commitment exists for the given combination.
1909
1910 -- Bug 4619257: Update pc/pfc rate types to 'User' in PA_FP_ROLLUP_TMP:
1911 -- 1. The assumption that Revenue rate types will always be 'User' is
1912 -- incorrect when some of the assignments mapping to a resource are
1913 -- billable and other are non-billable.
1914 -- Revenue rate types for records of a given (resource assignment,
1915 -- txn currency, period) combination need to be updated if any
1916 -- record for that combination has revenue rate type as 'User'.
1917
1918 IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
1919 -- Update Project Functional currency cost rate type
1920 UPDATE pa_fp_rollup_tmp tmp
1921 SET projfunc_cost_rate_type = 'User'
1922 WHERE projfunc_cost_rate_type <> 'User'
1923 AND EXISTS ( SELECT null
1924 FROM pa_fp_rollup_tmp tmp2
1925 WHERE tmp2.resource_assignment_id = tmp.resource_assignment_id
1926 AND tmp2.txn_currency_code = tmp.txn_currency_code
1927 AND tmp2.start_date = tmp.start_date
1928 AND tmp2.projfunc_cost_rate_type = 'User' );
1929
1930 -- Update Project currency cost rate type
1931 UPDATE pa_fp_rollup_tmp tmp
1932 SET project_cost_rate_type = 'User'
1933 WHERE project_cost_rate_type <> 'User'
1934 AND EXISTS ( SELECT null
1935 FROM pa_fp_rollup_tmp tmp2
1936 WHERE tmp2.resource_assignment_id = tmp.resource_assignment_id
1937 AND tmp2.txn_currency_code = tmp.txn_currency_code
1938 AND tmp2.start_date = tmp.start_date
1939 AND tmp2.project_cost_rate_type = 'User' );
1940
1941 -- Bug 4619257: Update Project Functional currency revenue rate type
1942 UPDATE pa_fp_rollup_tmp tmp
1943 SET projfunc_rev_rate_type = 'User'
1944 WHERE projfunc_rev_rate_type <> 'User'
1945 AND EXISTS ( SELECT null
1946 FROM pa_fp_rollup_tmp tmp2
1947 WHERE tmp2.resource_assignment_id = tmp.resource_assignment_id
1948 AND tmp2.txn_currency_code = tmp.txn_currency_code
1949 AND tmp2.start_date = tmp.start_date
1950 AND tmp2.projfunc_rev_rate_type = 'User' );
1951
1952 -- Bug 4619257: Update Project currency revenue rate type
1953 UPDATE pa_fp_rollup_tmp tmp
1954 SET project_rev_rate_type = 'User'
1955 WHERE project_rev_rate_type <> 'User'
1956 AND EXISTS ( SELECT null
1957 FROM pa_fp_rollup_tmp tmp2
1958 WHERE tmp2.resource_assignment_id = tmp.resource_assignment_id
1959 AND tmp2.txn_currency_code = tmp.txn_currency_code
1960 AND tmp2.start_date = tmp.start_date
1961 AND tmp2.project_rev_rate_type = 'User' );
1962
1963 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
1964 -- Update Project Functional currency cost rate type
1965 UPDATE pa_fp_rollup_tmp tmp
1966 SET projfunc_cost_rate_type = 'User'
1967 WHERE projfunc_cost_rate_type <> 'User'
1968 AND EXISTS ( SELECT null
1969 FROM pa_fp_rollup_tmp tmp2
1970 WHERE tmp2.resource_assignment_id = tmp.resource_assignment_id
1971 AND tmp2.txn_currency_code = tmp.txn_currency_code
1972 AND tmp2.projfunc_cost_rate_type = 'User' );
1973
1974 -- Update Project currency cost rate type
1975 UPDATE pa_fp_rollup_tmp tmp
1976 SET project_cost_rate_type = 'User'
1977 WHERE project_cost_rate_type <> 'User'
1978 AND EXISTS ( SELECT null
1979 FROM pa_fp_rollup_tmp tmp2
1980 WHERE tmp2.resource_assignment_id = tmp.resource_assignment_id
1981 AND tmp2.txn_currency_code = tmp.txn_currency_code
1982 AND tmp2.project_cost_rate_type = 'User' );
1983
1984 -- Bug 4619257: Update Project Functional currency revenue rate type
1985 UPDATE pa_fp_rollup_tmp tmp
1986 SET projfunc_rev_rate_type = 'User'
1987 WHERE projfunc_rev_rate_type <> 'User'
1988 AND EXISTS ( SELECT null
1989 FROM pa_fp_rollup_tmp tmp2
1990 WHERE tmp2.resource_assignment_id = tmp.resource_assignment_id
1991 AND tmp2.txn_currency_code = tmp.txn_currency_code
1992 AND tmp2.projfunc_rev_rate_type = 'User' );
1993
1994 -- Bug 4619257: Update Project currency revenue rate type
1995 UPDATE pa_fp_rollup_tmp tmp
1996 SET project_rev_rate_type = 'User'
1997 WHERE project_rev_rate_type <> 'User'
1998 AND EXISTS ( SELECT null
1999 FROM pa_fp_rollup_tmp tmp2
2000 WHERE tmp2.resource_assignment_id = tmp.resource_assignment_id
2001 AND tmp2.txn_currency_code = tmp.txn_currency_code
2002 AND tmp2.project_rev_rate_type = 'User' );
2003 END IF;
2004
2005
2006 -- Bug 4549862: If the target version is None timephased and the
2007 -- context is Forecast generation, then budget lines containing
2008 -- actuals may exist. As a result, some of the data in the temp
2009 -- table may need to be Inserted while other data in the table
2010 -- may need to be Updated in pa_budget_lines.
2011 --
2012 -- Group the temporary table data by resource assignment and txn
2013 -- currency code using separate cursors for the Insert/Update cases.
2014 -- Note that there are some cursors in GENERATE_BUDGET_AMT_RES_SCH
2015 -- with the same names as these cursors, but the queries are not
2016 -- the same.
2017 --
2018 -- One additional thing to note is that there is logic in the
2019 -- GENERATE_BUDGET_AMT_RES_SCH API to ensure that all project
2020 -- requirements/assignments that map to the same target resource
2021 -- have at most 1 distinct rejection code value per rejection
2022 -- code column in the temp table (when the target version is
2023 -- None timephased). This is important in guaranteeing that the
2024 -- cursors group the data correctly so that each ( resource /
2025 -- txn currency ) combination has only 1 budget line.
2026
2027 IF p_fp_cols_rec.x_time_phased_code = 'N' AND
2028 p_plan_class_code = 'FORECAST' THEN
2029
2030 -- Bug 4549862: Fetch data for Insert.
2031 OPEN GROUP_TO_INS_INTO_NTP_FCST_BL;
2032 FETCH GROUP_TO_INS_INTO_NTP_FCST_BL BULK COLLECT INTO
2033 l_bl_RES_ASSIGNMENT_ID_tab,
2034 l_bl_TXN_CURRENCY_CODE_tab,
2035 l_bl_START_DATE_tab,
2036 l_bl_END_DATE_tab,
2037 l_bl_PERIOD_NAME_tab,
2038 l_bl_QUANTITY_tab,
2039 l_bl_TXN_RAW_COST_tab,
2040 l_bl_TXN_BURDENED_COST_tab,
2041 l_bl_TXN_REVENUE_tab,
2042 l_bl_PC_RAW_COST_tab,
2043 l_bl_PC_BURDENED_COST_tab,
2044 l_bl_PC_REVENUE_tab,
2045 l_bl_PFC_RAW_COST_tab,
2046 l_bl_PFC_BURDENED_COST_tab,
2047 l_bl_PFC_REVENUE_tab,
2048 l_bl_COST_REJ_CODE_tab,
2049 l_bl_BURDEN_REJ_CODE_tab,
2050 l_bl_PC_CUR_REJ_CODE_tab,
2051 l_bl_PFC_CUR_REJ_CODE_tab,
2052 l_bl_PC_COST_RT_TYPE_tab,
2053 l_bl_PFC_COST_RT_TYPE_tab,
2054 l_bl_PC_REV_RT_TYPE_tab,
2055 l_bl_PFC_REV_RT_TYPE_tab;
2056 CLOSE GROUP_TO_INS_INTO_NTP_FCST_BL;
2057
2058 -- Bug 4549862: Fetch data for Update.
2059 OPEN GROUP_TO_UPD_INTO_NTP_FCST_BL;
2060 FETCH GROUP_TO_UPD_INTO_NTP_FCST_BL BULK COLLECT INTO
2061 l_upd_bl_RES_ASSIGNMENT_ID_tab,
2062 l_upd_bl_TXN_CURRENCY_CODE_tab,
2063 l_upd_bl_START_DATE_tab,
2064 l_upd_bl_END_DATE_tab,
2065 l_upd_bl_PERIOD_NAME_tab,
2066 l_upd_bl_QUANTITY_tab,
2067 l_upd_bl_TXN_RAW_COST_tab,
2068 l_upd_bl_TXN_BURDENED_COST_tab,
2069 l_upd_bl_TXN_REVENUE_tab,
2070 l_upd_bl_PC_RAW_COST_tab,
2071 l_upd_bl_PC_BURDENED_COST_tab,
2072 l_upd_bl_PC_REVENUE_tab,
2073 l_upd_bl_PFC_RAW_COST_tab,
2074 l_upd_bl_PFC_BURDENED_COST_tab,
2075 l_upd_bl_PFC_REVENUE_tab,
2076 l_upd_bl_COST_REJ_CODE_tab,
2077 l_upd_bl_BURDEN_REJ_CODE_tab,
2078 l_upd_bl_PC_CUR_REJ_CODE_tab,
2079 l_upd_bl_PFC_CUR_REJ_CODE_tab,
2080 l_upd_bl_PC_COST_RT_TYPE_tab,
2081 l_upd_bl_PFC_COST_RT_TYPE_tab,
2082 l_upd_bl_PC_REV_RT_TYPE_tab,
2083 l_upd_bl_PFC_REV_RT_TYPE_tab;
2084 CLOSE GROUP_TO_UPD_INTO_NTP_FCST_BL;
2085
2086 -- Bug 4549862: If the context is Budget generation, then we do
2087 -- not need to worry about the existence of budget lines containing
2088 -- actuals, so all temp table data can be Inserted into the budget
2089 -- lines table. If the context is Forecast generation and the target
2090 -- version is timephased by either PA or GL, then budget lines with
2091 -- actuals will only exist for periods through the Actuals Through
2092 -- Date. Since the temp table will contain ETC data in this case,
2093 -- all temp table data can be Inserted into the budget lines table.
2094 -- Therefore, in the ELSIF and ELSE blocks, fetch data for Insert.
2095
2096 ELSIF p_fp_cols_rec.x_time_phased_code = 'N' AND
2097 p_plan_class_code = 'BUDGET' THEN
2098
2099 OPEN GROUP_TO_INS_INTO_NTP_BDGT_BL;
2100 FETCH GROUP_TO_INS_INTO_NTP_BDGT_BL BULK COLLECT INTO
2101 l_bl_RES_ASSIGNMENT_ID_tab,
2102 l_bl_TXN_CURRENCY_CODE_tab,
2103 l_bl_START_DATE_tab,
2104 l_bl_END_DATE_tab,
2105 l_bl_PERIOD_NAME_tab,
2106 l_bl_QUANTITY_tab,
2107 l_bl_TXN_RAW_COST_tab,
2108 l_bl_TXN_BURDENED_COST_tab,
2109 l_bl_TXN_REVENUE_tab,
2110 l_bl_PC_RAW_COST_tab,
2111 l_bl_PC_BURDENED_COST_tab,
2112 l_bl_PC_REVENUE_tab,
2113 l_bl_PFC_RAW_COST_tab,
2114 l_bl_PFC_BURDENED_COST_tab,
2115 l_bl_PFC_REVENUE_tab,
2116 l_bl_COST_REJ_CODE_tab,
2117 l_bl_BURDEN_REJ_CODE_tab,
2118 l_bl_PC_CUR_REJ_CODE_tab,
2119 l_bl_PFC_CUR_REJ_CODE_tab,
2120 l_bl_PC_COST_RT_TYPE_tab,
2121 l_bl_PFC_COST_RT_TYPE_tab,
2122 l_bl_PC_REV_RT_TYPE_tab,
2123 l_bl_PFC_REV_RT_TYPE_tab;
2124 CLOSE GROUP_TO_INS_INTO_NTP_BDGT_BL;
2125
2126 ELSE
2127
2128 OPEN GROUP_TO_INS_INTO_BL;
2129 FETCH GROUP_TO_INS_INTO_BL BULK COLLECT INTO
2130 l_bl_RES_ASSIGNMENT_ID_tab,
2131 l_bl_TXN_CURRENCY_CODE_tab,
2132 l_bl_START_DATE_tab,
2133 l_bl_END_DATE_tab,
2134 l_bl_PERIOD_NAME_tab,
2135 l_bl_QUANTITY_tab,
2136 l_bl_TXN_RAW_COST_tab,
2137 l_bl_TXN_BURDENED_COST_tab,
2138 l_bl_TXN_REVENUE_tab,
2139 l_bl_PC_RAW_COST_tab,
2140 l_bl_PC_BURDENED_COST_tab,
2141 l_bl_PC_REVENUE_tab,
2142 l_bl_PFC_RAW_COST_tab,
2143 l_bl_PFC_BURDENED_COST_tab,
2144 l_bl_PFC_REVENUE_tab,
2145 l_bl_COST_REJ_CODE_tab,
2146 l_bl_BURDEN_REJ_CODE_tab,
2147 l_bl_PC_CUR_REJ_CODE_tab,
2148 l_bl_PFC_CUR_REJ_CODE_tab,
2149 l_bl_PC_COST_RT_TYPE_tab,
2150 l_bl_PFC_COST_RT_TYPE_tab,
2151 l_bl_PC_REV_RT_TYPE_tab,
2152 l_bl_PFC_REV_RT_TYPE_tab;
2153 CLOSE GROUP_TO_INS_INTO_BL;
2154
2155 END IF; -- grouping temp table data
2156
2157
2158 -- Calculate cost and revenue rate overrides
2159
2160 FOR i IN 1..l_bl_RES_ASSIGNMENT_ID_tab.count LOOP
2161 IF l_bl_QUANTITY_tab(i) <> 0 THEN
2162 l_txn_bill_rate_override_tab(i) := l_bl_TXN_REVENUE_tab(i) / l_bl_QUANTITY_tab(i);
2163 l_txn_rcost_rate_override_tab(i) := l_bl_TXN_RAW_COST_tab(i) / l_bl_QUANTITY_tab(i);
2164 l_txn_bcost_rate_override_tab(i) := l_bl_TXN_BURDENED_COST_tab(i) / l_bl_QUANTITY_tab(i);
2165 ELSE
2166 l_txn_bill_rate_override_tab(i) := null;
2167 l_txn_rcost_rate_override_tab(i) := null;
2168 l_txn_bcost_rate_override_tab(i) := null;
2169 END IF;
2170 END LOOP;
2171
2172 FOR i IN 1..l_upd_bl_RES_ASSIGNMENT_ID_tab.count LOOP
2173 IF l_upd_bl_QUANTITY_tab(i) <> 0 THEN
2174 l_upd_bill_rate_override_tab(i) := l_upd_bl_TXN_REVENUE_tab(i) / l_upd_bl_QUANTITY_tab(i);
2175 l_upd_rcost_rate_override_tab(i) := l_upd_bl_TXN_RAW_COST_tab(i) / l_upd_bl_QUANTITY_tab(i);
2176 l_upd_bcost_rate_override_tab(i) := l_upd_bl_TXN_BURDENED_COST_tab(i) / l_upd_bl_QUANTITY_tab(i);
2177 ELSE
2178 l_upd_bill_rate_override_tab(i) := null;
2179 l_upd_rcost_rate_override_tab(i) := null;
2180 l_upd_bcost_rate_override_tab(i) := null;
2181 END IF;
2182 END LOOP;
2183
2184
2185 -- Calculate cost and revenue pc/pfc exchange rates
2186
2187 FOR i IN 1..l_bl_RES_ASSIGNMENT_ID_tab.count LOOP
2188 -- Calculate cost pc/pfc exchange rates
2189 -- Assumption: Raw cost and burden cost conversion rates are the same.
2190 IF l_bl_TXN_RAW_COST_tab(i) <> 0 THEN
2191 l_cost_pc_exchg_rate_tab(i) := l_bl_PC_RAW_COST_tab(i) / l_bl_TXN_RAW_COST_tab(i);
2192 l_cost_pfc_exchg_rate_tab(i) := l_bl_PFC_RAW_COST_tab(i) / l_bl_TXN_RAW_COST_tab(i);
2193 ELSE
2194 l_cost_pc_exchg_rate_tab(i) := null;
2195 l_cost_pfc_exchg_rate_tab(i) := null;
2196 END IF;
2197
2198 -- Calculate revenue pc/pfc exchange rates
2199 IF l_bl_TXN_REVENUE_tab(i) <> 0 THEN
2200 l_rev_pc_exchg_rate_tab(i) := l_bl_PC_REVENUE_tab(i) / l_bl_TXN_REVENUE_tab(i);
2201 l_rev_pfc_exchg_rate_tab(i) := l_bl_PFC_REVENUE_tab(i) / l_bl_TXN_REVENUE_tab(i);
2202 ELSE
2203 l_rev_pc_exchg_rate_tab(i) := null;
2204 l_rev_pfc_exchg_rate_tab(i) := null;
2205 END IF;
2206 END LOOP;
2207
2208 FOR i IN 1..l_upd_bl_RES_ASSIGNMENT_ID_tab.count LOOP
2209 -- Calculate cost pc/pfc exchange rates
2210 -- Assumption: Raw cost and burden cost conversion rates are the same.
2211 IF l_upd_bl_TXN_RAW_COST_tab(i) <> 0 THEN
2212 l_upd_cost_pc_exchg_rate_tab(i) := l_upd_bl_PC_RAW_COST_tab(i) / l_upd_bl_TXN_RAW_COST_tab(i);
2213 l_upd_cost_pfc_exchg_rate_tab(i) := l_upd_bl_PFC_RAW_COST_tab(i) / l_upd_bl_TXN_RAW_COST_tab(i);
2214 ELSE
2215 l_upd_cost_pc_exchg_rate_tab(i) := null;
2216 l_upd_cost_pfc_exchg_rate_tab(i) := null;
2217 END IF;
2218
2219 -- Calculate revenue pc/pfc exchange rates
2220 IF l_upd_bl_TXN_REVENUE_tab(i) <> 0 THEN
2221 l_upd_rev_pc_exchg_rate_tab(i) := l_upd_bl_PC_REVENUE_tab(i) / l_upd_bl_TXN_REVENUE_tab(i);
2222 l_upd_rev_pfc_exchg_rate_tab(i) := l_upd_bl_PFC_REVENUE_tab(i) / l_upd_bl_TXN_REVENUE_tab(i);
2223 ELSE
2224 l_upd_rev_pc_exchg_rate_tab(i) := null;
2225 l_upd_rev_pfc_exchg_rate_tab(i) := null;
2226 END IF;
2227 END LOOP;
2228
2229
2230 -- Insert into budget lines: quantity, cost and revenue amounts,
2231 -- txn currency code, period, start/end dates, pc/pfc exchange
2232 -- rates, cost/revenue rate types, rate overrides, and rejection codes.
2233
2234 IF l_bl_RES_ASSIGNMENT_ID_tab.COUNT > 0 THEN
2235
2236 FORALL bl_index IN 1 .. l_bl_START_DATE_tab.COUNT
2237 INSERT INTO PA_BUDGET_LINES(
2238 RESOURCE_ASSIGNMENT_ID,
2239 START_DATE,
2240 LAST_UPDATE_DATE,
2241 LAST_UPDATED_BY,
2242 CREATION_DATE,
2243 CREATED_BY,
2244 LAST_UPDATE_LOGIN,
2245 END_DATE,
2246 PERIOD_NAME,
2247 QUANTITY,
2248 TXN_CURRENCY_CODE,
2249 BUDGET_LINE_ID,
2250 BUDGET_VERSION_ID,
2251 PROJECT_CURRENCY_CODE,
2252 PROJFUNC_CURRENCY_CODE,
2253 TXN_COST_RATE_OVERRIDE,
2254 TXN_BILL_RATE_OVERRIDE,
2255 BURDEN_COST_RATE_OVERRIDE,
2256 TXN_RAW_COST,
2257 TXN_BURDENED_COST,
2258 TXN_REVENUE,
2259 PROJECT_RAW_COST,
2260 PROJECT_BURDENED_COST,
2261 PROJECT_REVENUE,
2262 RAW_COST,
2263 BURDENED_COST,
2264 REVENUE,
2265 COST_REJECTION_CODE,
2266 BURDEN_REJECTION_CODE,
2267 PC_CUR_CONV_REJECTION_CODE,
2268 PFC_CUR_CONV_REJECTION_CODE,
2269 PROJECT_COST_EXCHANGE_RATE,
2270 PROJFUNC_COST_EXCHANGE_RATE,
2271 PROJECT_REV_EXCHANGE_RATE,
2272 PROJFUNC_REV_EXCHANGE_RATE,
2273 PROJECT_COST_RATE_TYPE,
2274 PROJFUNC_COST_RATE_TYPE,
2275 PROJECT_REV_RATE_TYPE,
2276 PROJFUNC_REV_RATE_TYPE )
2277 VALUES(
2278 l_bl_RES_ASSIGNMENT_ID_tab(bl_index),
2279 l_bl_START_DATE_tab(bl_index),
2280 l_sysdate,
2281 l_last_updated_by,
2282 l_sysdate,
2283 l_last_updated_by,
2284 l_last_update_login,
2285 l_bl_END_DATE_tab(bl_index),
2286 l_bl_PERIOD_NAME_tab(bl_index),
2287 l_bl_QUANTITY_tab(bl_index),
2288 l_bl_TXN_CURRENCY_CODE_tab(bl_index),
2289 PA_BUDGET_LINES_S.nextval,
2290 P_BUDGET_VERSION_ID,
2291 P_FP_COLS_REC.X_PROJECT_CURRENCY_CODE,
2292 P_FP_COLS_REC.X_PROJFUNC_CURRENCY_CODE,
2293 l_txn_rcost_rate_override_tab(bl_index),
2294 l_txn_bill_rate_override_tab(bl_index),
2295 l_txn_bcost_rate_override_tab(bl_index),
2296 l_bl_TXN_RAW_COST_tab(bl_index),
2297 l_bl_TXN_BURDENED_COST_tab(bl_index),
2298 l_bl_TXN_REVENUE_tab(bl_index),
2299 l_bl_PC_RAW_COST_tab(bl_index),
2300 l_bl_PC_BURDENED_COST_tab(bl_index),
2301 l_bl_PC_REVENUE_tab(bl_index),
2302 l_bl_PFC_RAW_COST_tab(bl_index),
2303 l_bl_PFC_BURDENED_COST_tab(bl_index),
2304 l_bl_PFC_REVENUE_tab(bl_index),
2305 l_bl_COST_REJ_CODE_tab(bl_index),
2306 l_bl_BURDEN_REJ_CODE_tab(bl_index),
2307 l_bl_PC_CUR_REJ_CODE_tab(bl_index),
2308 l_bl_PFC_CUR_REJ_CODE_tab(bl_index),
2309 l_cost_pc_exchg_rate_tab(bl_index),
2310 l_cost_pfc_exchg_rate_tab(bl_index),
2311 l_rev_pc_exchg_rate_tab(bl_index),
2312 l_rev_pfc_exchg_rate_tab(bl_index),
2313 l_bl_PC_COST_RT_TYPE_tab(bl_index),
2314 l_bl_PFC_COST_RT_TYPE_tab(bl_index),
2315 l_bl_PC_REV_RT_TYPE_tab(bl_index),
2316 l_bl_PFC_REV_RT_TYPE_tab(bl_index) );
2317
2318 END IF; -- IF l_bl_RES_ASSIGNMENT_ID_tab.COUNT > 0 THEN
2319
2320
2321 -- Bug 4549862: If the target version is None timephased and the
2322 -- context is Forecast generation, then budget lines containing
2323 -- actuals may exist. As a result, some of the data in the temp
2324 -- table may need to be Inserted while other data in the table
2325 -- may need to be Updated in pa_budget_lines.
2326 --
2327 -- The following code Updates the budget lines.
2328
2329 IF l_upd_bl_RES_ASSIGNMENT_ID_tab.COUNT > 0 THEN
2330
2331 FORALL bl_index IN 1 .. l_upd_bl_START_DATE_tab.COUNT
2332 UPDATE PA_BUDGET_LINES
2333 SET LAST_UPDATE_DATE = l_sysdate,
2334 LAST_UPDATED_BY = l_last_updated_by,
2335 LAST_UPDATE_LOGIN = l_last_update_login,
2336 START_DATE = LEAST(START_DATE, l_upd_bl_START_DATE_tab(bl_index)),
2337 END_DATE = GREATEST(END_DATE, l_upd_bl_END_DATE_tab(bl_index)),
2338 QUANTITY =
2339 DECODE(INIT_QUANTITY, null, l_upd_bl_QUANTITY_tab(bl_index),
2340 INIT_QUANTITY + NVL(l_upd_bl_QUANTITY_tab(bl_index),0)),
2341 TXN_RAW_COST =
2342 DECODE(TXN_INIT_RAW_COST, null, l_upd_bl_TXN_RAW_COST_tab(bl_index),
2343 TXN_INIT_RAW_COST + NVL(l_upd_bl_TXN_RAW_COST_tab(bl_index),0)),
2344 TXN_BURDENED_COST =
2345 DECODE(TXN_INIT_BURDENED_COST, null, l_upd_bl_TXN_BURDENED_COST_tab(bl_index),
2346 TXN_INIT_BURDENED_COST + NVL(l_upd_bl_TXN_BURDENED_COST_tab(bl_index),0)),
2347 TXN_REVENUE =
2348 DECODE(TXN_INIT_REVENUE, null, l_upd_bl_TXN_REVENUE_tab(bl_index),
2349 TXN_INIT_REVENUE + NVL(l_upd_bl_TXN_REVENUE_tab(bl_index),0)),
2350 PROJECT_RAW_COST =
2351 DECODE(PROJECT_INIT_RAW_COST, null, l_upd_bl_PC_RAW_COST_tab(bl_index),
2352 PROJECT_INIT_RAW_COST + NVL(l_upd_bl_TXN_RAW_COST_tab(bl_index),0)),
2353 PROJECT_BURDENED_COST =
2354 DECODE(PROJECT_INIT_BURDENED_COST, null, l_upd_bl_PC_BURDENED_COST_tab(bl_index),
2355 PROJECT_INIT_BURDENED_COST + NVL(l_upd_bl_PC_BURDENED_COST_tab(bl_index),0)),
2356 PROJECT_REVENUE =
2357 DECODE(PROJECT_INIT_REVENUE, null, l_upd_bl_PC_REVENUE_tab(bl_index),
2358 PROJECT_INIT_REVENUE + NVL(l_upd_bl_PC_REVENUE_tab(bl_index),0)),
2359 RAW_COST =
2360 DECODE(INIT_RAW_COST, null, l_upd_bl_PFC_RAW_COST_tab(bl_index),
2361 INIT_RAW_COST + NVL(l_upd_bl_PFC_RAW_COST_tab(bl_index),0)),
2362 BURDENED_COST =
2363 DECODE(INIT_BURDENED_COST, null, l_upd_bl_PFC_BURDENED_COST_tab(bl_index),
2364 INIT_BURDENED_COST + NVL(l_upd_bl_PFC_BURDENED_COST_tab(bl_index),0)),
2365 REVENUE =
2366 DECODE(INIT_REVENUE, null, l_upd_bl_PFC_REVENUE_tab(bl_index),
2367 INIT_REVENUE + NVL(l_upd_bl_PFC_REVENUE_tab(bl_index),0)),
2368 TXN_COST_RATE_OVERRIDE = l_upd_rcost_rate_override_tab(bl_index),
2369 TXN_BILL_RATE_OVERRIDE = l_upd_bill_rate_override_tab(bl_index),
2370 BURDEN_COST_RATE_OVERRIDE = l_upd_bcost_rate_override_tab(bl_index),
2371 COST_REJECTION_CODE = l_upd_bl_COST_REJ_CODE_tab(bl_index),
2372 BURDEN_REJECTION_CODE = l_upd_bl_BURDEN_REJ_CODE_tab(bl_index),
2373 PC_CUR_CONV_REJECTION_CODE = l_upd_bl_PC_CUR_REJ_CODE_tab(bl_index),
2374 PFC_CUR_CONV_REJECTION_CODE = l_upd_bl_PFC_CUR_REJ_CODE_tab(bl_index),
2375 PROJECT_COST_EXCHANGE_RATE = l_upd_cost_pc_exchg_rate_tab(bl_index),
2376 PROJFUNC_COST_EXCHANGE_RATE = l_upd_cost_pfc_exchg_rate_tab(bl_index),
2377 PROJECT_REV_EXCHANGE_RATE = l_upd_rev_pc_exchg_rate_tab(bl_index),
2378 PROJFUNC_REV_EXCHANGE_RATE = l_upd_rev_pfc_exchg_rate_tab(bl_index),
2379 PROJECT_COST_RATE_TYPE = l_upd_bl_PC_COST_RT_TYPE_tab(bl_index),
2380 PROJFUNC_COST_RATE_TYPE = l_upd_bl_PFC_COST_RT_TYPE_tab(bl_index),
2381 PROJECT_REV_RATE_TYPE = l_upd_bl_PC_REV_RT_TYPE_tab(bl_index),
2382 PROJFUNC_REV_RATE_TYPE = l_upd_bl_PFC_REV_RT_TYPE_tab(bl_index)
2383 WHERE RESOURCE_ASSIGNMENT_ID = l_upd_bl_RES_ASSIGNMENT_ID_tab(bl_index)
2384 AND TXN_CURRENCY_CODE = l_upd_bl_TXN_CURRENCY_CODE_tab(bl_index);
2385
2386 END IF; -- l_upd_bl_RES_ASSIGNMENT_ID_tab.COUNT > 0 check
2387
2388 IF P_PA_DEBUG_MODE = 'Y' THEN
2389 PA_DEBUG.Reset_Curr_Function;
2390 END IF;
2391
2392 EXCEPTION
2393 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2394 /** MRC Elimination changes: PA_MRC_FINPLAN.G_CALLING_MODULE := Null; **/
2395 l_msg_count := FND_MSG_PUB.count_msg;
2396
2397 IF l_msg_count = 1 THEN
2398 PA_INTERFACE_UTILS_PUB.get_messages
2399 ( p_encoded => FND_API.G_TRUE
2400 ,p_msg_index => 1
2401 ,p_msg_count => l_msg_count
2402 ,p_msg_data => l_msg_data
2403 ,p_data => l_data
2404 ,p_msg_index_out => l_msg_index_out );
2405 x_msg_data := l_data;
2406 x_msg_count := l_msg_count;
2407 ELSE
2408 x_msg_count := l_msg_count;
2409 END IF;
2410 ROLLBACK;
2411
2412 x_return_status := FND_API.G_RET_STS_ERROR;
2413
2414 IF P_PA_DEBUG_MODE = 'Y' THEN
2415 PA_DEBUG.Reset_Curr_Function;
2416 END IF;
2417
2418 RAISE;
2419
2420 WHEN OTHERS THEN
2421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2422 x_msg_data := SUBSTR(SQLERRM,1,240);
2423 FND_MSG_PUB.add_exc_msg
2424 ( p_pkg_name => 'PA_FP_REV_GEN_PUB'
2425 ,p_procedure_name => 'PUSH_RES_SCH_DATA_TO_BL' );
2426
2427 IF P_PA_DEBUG_MODE = 'Y' THEN
2428 PA_DEBUG.Reset_Curr_Function;
2429 END IF;
2430
2431 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2432
2433 END PUSH_RES_SCH_DATA_TO_BL;
2434
2435
2436 END PA_FP_REV_GEN_PUB;