1 PACKAGE body PA_FP_GEN_FCST_AMT_PUB3 as
2 /* $Header: PAFPFG3B.pls 120.7 2007/02/06 09:51:34 dthakker ship $ */
3
4 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 /* Assumption:
7 *1.Before getting into this procedure, we have all total plan amounts and commitment
8 amounts populated in temporary table PA_FP_CALC_AMT_TMP2 table with transaction
9 source codes of 'WORKPLAN'/'FINPLAN' or 'OPEN_COMMITMENTS'.
10 2.Rate based flag for target resource assignment gets updated correctly before coming
11 into any of ETC methods.
12 3.All considered scenarios:
13 Rate_based
14 non multi currency enabled: use PC
15 multi currency enabled
16 actuals currency is subset of total currency: use TC, currency based substraction
17 actuals currency is not subset of total currency: use TC, prorate ETC quantity
18 Non_rate_based
19 non multi currency enabled: use PC
20 multi currency enabled
21 actuals currency not subset of total currency: use TC, currency based substraction
22 actuals currency not subset of total currency: Compute ETC quantity in PC, prorate
23 this ETC quantity to different planning currencies based on PC amounts,
24 convert back from PC to TC.
25 */
26
27 PROCEDURE GET_ETC_REMAIN_BDGT_AMTS
28 (P_SRC_RES_ASG_ID IN PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE,
29 P_TGT_RES_ASG_ID IN PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE,
30 P_FP_COLS_SRC_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
31 P_FP_COLS_TGT_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
32 P_TASK_ID IN PA_TASKS.TASK_ID%TYPE,
33 P_RESOURCE_LIST_MEMBER_ID IN PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_MEMBER_ID%TYPE,
34 P_ETC_SOURCE_CODE IN PA_TASKS.GEN_ETC_SOURCE_CODE%TYPE,
35 P_WP_STRUCTURE_VERSION_ID IN PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE,
36 P_ACTUALS_THRU_DATE IN PA_PERIODS_ALL.END_DATE%TYPE,
37 P_PLANNING_OPTIONS_FLAG IN VARCHAR2,
38 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
39 X_MSG_COUNT OUT NOCOPY NUMBER,
40 X_MSG_DATA OUT NOCOPY VARCHAR2)
41 IS
42 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_FCST_AMT_PUB3.GEN_ETC_REMAIN_BDGT_AMTS';
43
44 l_currency_flag VARCHAR2(30);
45 l_rate_based_flag VARCHAR2(1);
46 l_currency_count_for_flag NUMBER;
47 l_prorating_always_flag VARCHAR2(1);
48 l_target_version_type pa_budget_versions.version_type%type;
49
50 /* For PC amounts */
51 l_pc_currency_code pa_projects_all.project_currency_code%type;
52 l_tot_quantity_pc_pfc NUMBER;
53 l_tot_raw_cost_pc_pfc NUMBER;
54 l_tot_brdn_cost_pc_pfc NUMBER;
55 l_tot_revenue_pc_pfc NUMBER;
56
57 l_act_quantity_pc_pfc NUMBER;
58
59 /*For workplan actuals*/
60 lx_act_quantity NUMBER;
61 lx_act_txn_currency_code VARCHAR2(30);
62 lx_act_txn_raw_cost NUMBER;
63 lx_act_txn_brdn_cost NUMBER;
64 lx_act_pc_raw_cost NUMBER;
65 lx_act_pc_brdn_cost NUMBER;
66 lx_act_pfc_raw_cost NUMBER;
67 lx_act_pfc_brdn_cost NUMBER;
68
69 l_etc_quantity_pc_pfc NUMBER;
70
71 /* For TC amounts */
72 l_tot_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
73 l_tot_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
74 l_tot_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
75 l_tot_brdn_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
76 l_tot_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
77 l_tot_quantity_sum NUMBER;
78
79 l_act_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
80 l_act_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
81 l_act_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
82 l_act_brdn_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
83 l_act_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
84 l_act_quantity_sum NUMBER;
85
86 /* ForPFC amounts */
87 l_pfc_currency_code pa_projects_all.project_currency_code%type;
88 l_rev_gen_method VARCHAR2(3);
89
90
91 /* For ETC amounts */
92 l_etc_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
93 l_etc_quantity_sum NUMBER;
94
95 l_currency_count_act_min_tot NUMBER;
96 l_currency_prorate_act_flag VARCHAR2(1);
97 l_exit_flag VARCHAR2(1) := 'N';
98
99 /*For PC_TC amounts*/
100 l_tot_quantity_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
101 l_tot_raw_cost_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
102 l_tot_brdn_cost_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
103 l_tot_revenue_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
104 l_tot_quantity_pc_sum NUMBER;
105 l_act_quantity_pc_sum NUMBER;
106 l_etc_quantity_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
107 l_etc_quantity_pc_sum NUMBER;
108
109 /*For average rates*/
110 l_pc_pfc_rate_quantity NUMBER;
111 l_pc_pfc_rate_raw_cost NUMBER;
112 l_pc_pfc_rate_brdn_cost NUMBER;
113 l_pc_pfc_rate_revenue NUMBER;
114
115 l_pc_rate_quantity NUMBER;
116 l_pc_rate_raw_cost NUMBER;
117 l_pc_rate_brdn_cost NUMBER;
118 l_pc_rate_revenue NUMBER;
119
120 l_txn_rate_quantity NUMBER;
121 l_txn_rate_raw_cost NUMBER;
122 l_txn_rate_brdn_cost NUMBER;
123 l_txn_rate_revenue NUMBER;
124
125 l_pc_pfc_raw_cost_rate NUMBER;
126 l_pc_pfc_brdn_cost_rate NUMBER;
127 l_pc_pfc_revenue_rate NUMBER;
128
129 l_txn_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
130 l_txn_brdn_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
131 l_txn_revenue_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
132 l_pc_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
133 l_pc_brdn_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
134 l_pc_revenue_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
135 l_transaction_source_code VARCHAR2(30);
136
137 /*For txn currency conversion*/
138 l_task_id pa_tasks.task_id%type;
139 l_planning_start_date pa_resource_assignments.planning_start_date%type;
140 lx_acc_rate_date DATE;
141 lx_acct_rate_type VARCHAR2(50);
142 lx_acct_exch_rate NUMBER;
143 lx_acct_raw_cost NUMBER;
144 lx_project_rate_type VARCHAR2(50);
145 lx_project_rate_date DATE;
146 lx_project_exch_rate NUMBER;
147 lx_projfunc_cost_rate_type VARCHAR2(50);
148 lx_projfunc_cost_rate_date DATE;
149 lx_projfunc_cost_exch_rate NUMBER;
150 l_projfunc_raw_cost NUMBER;
151
152 /* Status variable for GET_CURRENCY_AMOUNTS api */
153 l_status Varchar2(100);
154 g_project_name pa_projects_all.name%TYPE;
155
156 l_msg_count NUMBER;
157 l_msg_data VARCHAR2(2000);
158 l_data VARCHAR2(2000);
159 l_msg_index_out NUMBER:=0;
160 BEGIN
161 IF p_pa_debug_mode = 'Y' THEN
162 pa_debug.set_curr_function( p_function => 'GEN_ETC_REMAIN_BDGT_AMTS',
163 p_debug_mode => p_pa_debug_mode);
164 END IF;
165
166 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
167 X_MSG_COUNT := 0;
168
169 /*Currency usage should be determined at the beginning.
170 Default to use Transaction Currency (TC)
171 If target version is not multi currency enabled, take Project Currency (PC)
172 IF target version is multi currency enabled, the target planning resource is non
173 rate based, and actuals currencies are not subset of the total currencies. We need
174 to take PC amounts as quantity, sum up total quantity minus actual quantity,
175 prorate this total PC ETC quantity across the planning currencies. Then convert
176 them back from PC to TC (PC_TC).*/
177
178 IF nvl(p_tgt_res_asg_id,0) > 0 THEN
179 SELECT rate_based_flag
180 INTO l_rate_based_flag
181 FROM pa_resource_assignments
182 WHERE resource_assignment_id = p_tgt_res_asg_id;
183 ELSE
184 l_rate_based_flag:='N';
185 END IF;
186
187 l_currency_flag := 'TC';
188
189 l_rev_gen_method := nvl(P_FP_COLS_TGT_REC.x_revenue_derivation_method,PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(P_FP_COLS_TGT_REC.x_project_id)); -- Bug 5462471
190
191 --l_rev_gen_method := PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(P_FP_COLS_TGT_REC.x_project_id);
192
193 IF (p_fp_cols_tgt_rec.x_version_type = 'REVENUE' and l_rev_gen_method = 'C') THEN
194 l_currency_flag := 'PFC';
195 ELSIF p_fp_cols_tgt_rec.X_PLAN_IN_MULTI_CURR_FLAG = 'N' THEN
196 l_currency_flag := 'PC';
197 ELSIF l_rate_based_flag = 'N' THEN
198 SELECT COUNT(*) INTO l_currency_count_for_flag FROM (
199 SELECT /*+ INDEX(act_tmp,PA_FP_FCST_GEN_TMP1_N1) INDEX(tot_tmp,PA_FP_CALC_AMT_TMP1_N1)*/
200 DISTINCT act_tmp.txn_currency_code
201 FROM PA_FP_FCST_GEN_TMP1 act_tmp,
202 PA_FP_CALC_AMT_TMP1 tot_tmp
203 WHERE act_tmp.project_element_id = tot_tmp.task_id
204 AND act_tmp.res_list_member_id = tot_tmp.resource_list_member_id
205 AND tot_tmp.target_res_asg_id = p_tgt_res_asg_id
206 AND data_type_code = DECODE(P_ETC_SOURCE_CODE,
207 'WORKPLAN_RESOURCES', 'ETC_WP',
208 'FINANCIAL_PLAN', 'ETC_FP')
209 MINUS
210 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
211 DISTINCT txn_currency_code
212 FROM PA_FP_CALC_AMT_TMP2
213 WHERE target_res_asg_id = p_tgt_res_asg_id
214 AND transaction_source_code = p_etc_source_code
215 ) WHERE rownum = 1;
216
217 IF l_currency_count_for_flag > 0 THEN
218 l_currency_flag := 'PC_TC';
219 END IF;
220 END IF;
221
222 /**************BY THIS TIME, WE DECIDED TO USE EITHER PC,TC,PC_TC or PFC**********/
223
224 l_target_version_type := p_fp_cols_tgt_rec.x_version_type;
225 l_pc_currency_code := p_fp_cols_tgt_rec.x_project_currency_code;
226 l_pfc_currency_code := p_fp_cols_tgt_rec.x_projfunc_currency_code;
227 IF l_currency_flag = 'PC' OR l_currency_flag = 'PFC' THEN
228 /* No matter ETC source is 'FINANCIAL_PLAN' or 'WORKPLAN_RESOURCES', always get
229 total plan amounts in PC or PFC from financial data model.*/
230 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
231 NVL(SUM(NVL(total_plan_quantity,0)),0),
232 NVL(SUM(NVL(
233 DECODE(l_currency_flag, 'PC', total_pc_raw_cost,
234 'PFC', total_pfc_raw_cost),0)),0),
235 NVL(SUM(NVL(
236 DECODE(l_currency_flag, 'PC', total_pc_burdened_cost,
237 'PFC', total_pfc_burdened_cost),0)),0),
238 NVL(SUM(NVL(
239 DECODE(l_currency_flag, 'PC', total_pc_revenue,
240 'PFC', total_pfc_revenue),0)),0)
241 INTO l_tot_quantity_pc_pfc,
242 l_tot_raw_cost_pc_pfc,
243 l_tot_brdn_cost_pc_pfc,
244 l_tot_revenue_pc_pfc
245 FROM PA_FP_CALC_AMT_TMP2
246 WHERE resource_assignment_id = p_src_res_asg_id
247 AND transaction_source_code = p_etc_source_code;
248
249 IF l_rate_based_flag = 'N' THEN
250 l_tot_quantity_pc_pfc := l_tot_raw_cost_pc_pfc;
251 END IF;
252
253 IF p_etc_source_code = 'FINANCIAL_PLAN' THEN
254 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
255 DECODE(l_currency_flag,
256 'PC', NVL(SUM(DECODE(l_rate_based_flag,
257 'Y', quantity,
258 'N', NVL(prj_raw_cost,0))),0),
259 'PFC', NVL(SUM(DECODE(l_rate_based_flag,
260 'Y', quantity,
261 'N', NVL(pou_raw_cost,0))),0))
262 INTO l_act_quantity_pc_pfc
263 FROM PA_FP_FCST_GEN_TMP1
264 WHERE project_element_id = p_task_id
265 AND res_list_member_id = p_resource_list_member_id
266 AND data_type_code = 'ETC_FP';
267
268 ELSIF p_etc_source_code = 'WORKPLAN_RESOURCES' THEN
269 /*Bug fix for 3973511
270 Workplan side only stores amounts in one currency for each planning
271 resource. Instead of relying on pa_progress_utils.get_actuals_for_task
272 to get actuals data, we query directly to pa_budget_lines to get actual
273 data from source workplan budget version */
274 IF P_PA_DEBUG_MODE = 'Y' THEN
275 pa_fp_gen_amount_utils.fp_debug(
276 p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
277 'GET_WP_ACTUALS_FOR_RA',
278 p_module_name => l_module_name,
279 p_log_level => 5);
280 END IF;
281 PA_FP_GEN_FCST_AMT_PUB1.GET_WP_ACTUALS_FOR_RA
282 (P_FP_COLS_SRC_REC => p_fp_cols_src_rec,
283 P_FP_COLS_TGT_REC => p_fp_cols_tgt_rec,
284 P_SRC_RES_ASG_ID => p_src_res_asg_id,
285 P_TASK_ID => p_task_id,
286 P_RES_LIST_MEM_ID => p_resource_list_member_id,
287 P_ACTUALS_THRU_DATE => p_actuals_thru_date,
288 X_ACT_QUANTITY => lx_act_quantity,
289 X_ACT_TXN_CURRENCY_CODE => lx_act_txn_currency_code,
290 X_ACT_TXN_RAW_COST => lx_act_txn_raw_cost,
291 X_ACT_TXN_BRDN_COST => lx_act_txn_brdn_cost,
292 X_ACT_PC_RAW_COST => lx_act_pc_raw_cost,
293 X_ACT_PC_BRDN_COST => lx_act_pc_brdn_cost,
294 X_ACT_PFC_RAW_COST => lx_act_pfc_raw_cost,
295 X_ACT_PFC_BRDN_COST => lx_act_pfc_brdn_cost,
296 X_RETURN_STATUS => x_return_status,
297 X_MSG_COUNT => x_msg_count,
298 X_MSG_DATA => x_msg_data );
299 IF P_PA_DEBUG_MODE = 'Y' THEN
300 pa_fp_gen_amount_utils.fp_debug(
301 p_msg => 'After calling PA_FP_GEN_FCST_AMT_PUB1.'||
302 'GET_WP_ACTUALS_FOR_RA in remain_bdgt:'||x_return_status,
303 p_module_name => l_module_name,
304 p_log_level => 5);
305 END IF;
306 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
307 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
308 END IF;
309
310 IF l_rate_based_flag = 'Y' THEN
311 l_act_quantity_pc_pfc := lx_act_quantity;
312 ELSE
313 IF l_currency_flag = 'PC' THEN
314 l_act_quantity_pc_pfc := lx_act_pc_raw_cost;
315 ELSIF l_currency_flag = 'PFC' THEN
316 l_act_quantity_pc_pfc := lx_act_pfc_raw_cost;
317 END IF;
318 END IF;
319 END IF;
320
321 /* Get total ETC quantity */
322 l_etc_quantity_pc_pfc := l_tot_quantity_pc_pfc - l_act_quantity_pc_pfc;
323 IF l_etc_quantity_pc_pfc <= 0 THEN
324 /* actual quantity > total ETC quantity, only need to spread
325 commitment and actual data*/
326 IF P_PA_DEBUG_MODE = 'Y' THEN
327 PA_DEBUG.RESET_CURR_FUNCTION;
328 END IF;
329 RETURN;
330 END IF;
331
332 /* hr_utility.trace('project currency:'||l_ppc_currency_code);
333 hr_utility.trace('etc qty '||l_etc_quantity_pc );*/
334
335 /*When not taking periodic rates, we need to calculate out the average
336 rates from the source resource assignments that are mapped to the current
337 target resource assignmentInsert the single PC record for total ETC.*/
338 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
339 NVL(SUM(NVL(total_plan_quantity,0)),0),
340 DECODE(l_currency_flag,
341 'PC', NVL(SUM(NVL(total_pc_raw_cost,0)),0),
342 'PFC', NVL(SUM(NVL(total_pfc_raw_cost,0)),0)),
343 DECODE(l_currency_flag,
344 'PC', NVL(SUM(NVL(total_pc_burdened_cost,0)),0),
345 'PFC', NVL(SUM(NVL(total_pfc_burdened_cost,0)),0)),
346 DECODE(l_currency_flag,
347 'PC', NVL(SUM(NVL(total_pc_revenue,0)),0),
348 'PFC', NVL(SUM(NVL(total_pfc_revenue,0)),0))
349 INTO l_pc_pfc_rate_quantity,
350 l_pc_pfc_rate_raw_cost,
351 l_pc_pfc_rate_brdn_cost,
352 l_pc_pfc_rate_revenue
353 FROM pa_fp_calc_amt_tmp2
354 WHERE resource_assignment_id = p_src_res_asg_id
355 AND transaction_source_code in ('FINANCIAL_PLAN',
356 'WORKPLAN_RESOURCES');
357
358 IF l_rate_based_flag = 'N' THEN
359 l_pc_pfc_rate_quantity := l_pc_pfc_rate_raw_cost;
360 END IF;
361
362 IF l_pc_pfc_rate_quantity <> 0 THEN
363 l_pc_pfc_raw_cost_rate := l_pc_pfc_rate_raw_cost / l_pc_pfc_rate_quantity;
364 l_pc_pfc_brdn_cost_rate := l_pc_pfc_rate_brdn_cost / l_pc_pfc_rate_quantity;
365 l_pc_pfc_revenue_rate := l_pc_pfc_rate_revenue / l_pc_pfc_rate_quantity;
366 ELSE
367 l_pc_pfc_raw_cost_rate := NULL;
368 l_pc_pfc_brdn_cost_rate := NULL;
369 l_pc_pfc_revenue_rate := NULL;
370 END IF;
371
372 /*Insert single PC record
373 If commitment is not included, record is inserted directly as 'ETC'
374 record, if commitment is to be considered, record is inserted as
375 'TOTAL_ETC' for further processing. */
376 IF P_FP_COLS_TGT_REC.X_GEN_INCL_OPEN_COMM_FLAG = 'Y' THEN
377 l_transaction_source_code := 'TOTAL_ETC';
378 ELSE
379 l_transaction_source_code := 'ETC';
380 END IF;
381
382 INSERT INTO PA_FP_CALC_AMT_TMP2 (
383 RESOURCE_ASSIGNMENT_ID,
384 TARGET_RES_ASG_ID,
385 ETC_CURRENCY_CODE,
386 ETC_PLAN_QUANTITY,
387 ETC_TXN_RAW_COST,
388 ETC_TXN_BURDENED_COST,
389 ETC_TXN_REVENUE,
390 ETC_PC_RAW_COST,
391 ETC_PC_BURDENED_COST,
392 ETC_PC_REVENUE,
393 ETC_PFC_RAW_COST,
394 ETC_PFC_BURDENED_COST,
395 ETC_PFC_REVENUE,
396 TRANSACTION_SOURCE_CODE)
397 VALUES (
398 P_SRC_RES_ASG_ID,
399 P_TGT_RES_ASG_ID,
400 DECODE(l_currency_flag, 'PC', l_pc_currency_code,
401 'PFC', l_pfc_currency_code),
402 l_etc_quantity_pc_pfc,
403 l_etc_quantity_pc_pfc * l_pc_pfc_raw_cost_rate,
404 l_etc_quantity_pc_pfc * l_pc_pfc_brdn_cost_rate,
405 l_etc_quantity_pc_pfc * l_pc_pfc_revenue_rate,
406 DECODE(l_currency_flag,
407 'PC', l_etc_quantity_pc_pfc * l_pc_pfc_raw_cost_rate,
408 'PFC', NULL),
409 DECODE(l_currency_flag,
410 'PC', l_etc_quantity_pc_pfc * l_pc_pfc_brdn_cost_rate,
411 'PFC', NULL),
412 DECODE(l_currency_flag,
413 'PC', l_etc_quantity_pc_pfc * l_pc_pfc_revenue_rate,
414 'PFC', NULL),
415 DECODE(l_currency_flag,
416 'PFC', l_etc_quantity_pc_pfc * l_pc_pfc_raw_cost_rate,
417 'PC', NULL),
418 DECODE(l_currency_flag,
419 'PFC', l_etc_quantity_pc_pfc * l_pc_pfc_brdn_cost_rate,
420 'PC', NULL),
421 DECODE(l_currency_flag,
422 'PFC', l_etc_quantity_pc_pfc * l_pc_pfc_revenue_rate,
423 'PC', NULL),
424 l_transaction_source_code);
425
426 /**************BY THIS TIME, WE HAVE ALL ETC DATA FOR PC or PFC*********/
427
428 ELSIF l_currency_flag = 'TC' THEN
429 /* No matter ETC source is 'FINANCIAL_PLAN' or 'WORKPLAN_RESOURCES', always
430 get total plan amounts by txn currency from financial data model.*/
431 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
432 txn_currency_code,
433 SUM(NVL(total_plan_quantity,0)),
434 SUM(NVL(total_txn_raw_cost,0)),
435 SUM(NVL(total_txn_burdened_cost,0)),
436 SUM(NVL(total_txn_revenue,0))
437 BULK COLLECT INTO
438 l_tot_currency_code_tab,
439 l_tot_quantity_tab,
440 l_tot_raw_cost_tab,
441 l_tot_brdn_cost_tab,
442 l_tot_revenue_tab
443 FROM PA_FP_CALC_AMT_TMP2
444 WHERE resource_assignment_id = p_src_res_asg_id
445 AND transaction_source_code = p_etc_source_code
446 GROUP BY txn_currency_code;
447
448 IF l_tot_currency_code_tab.count = 0 THEN
449 IF P_PA_DEBUG_MODE = 'Y' THEN
450 PA_DEBUG.RESET_CURR_FUNCTION;
451 END IF;
452 RETURN;
453 END IF;
454 IF l_rate_based_flag = 'N' THEN
455 l_tot_quantity_tab := l_tot_raw_cost_tab;
456 END IF;
457
458 /* Bug 4085203
459 The total plan amounts should be summed up irrespective of rate based
460 or non rate based. Because for non rate based resource, we used the
461 sum value when plan and actuals are using same one currency. When
462 plan and actuals are using more than one currencies, the flow will
463 not use the sum amounts.*/
464 l_tot_quantity_sum := 0;
465 FOR i IN 1..l_tot_quantity_tab.count LOOP
466 l_tot_quantity_sum := l_tot_quantity_sum + NVL(l_tot_quantity_tab(i),0);
467 END LOOP;
468
469 IF p_etc_source_code = 'FINANCIAL_PLAN' THEN
470 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
471 txn_currency_code,
472 SUM(NVL(quantity,0)),
473 SUM(NVL(txn_raw_cost,0)),
474 SUM(NVL(txn_brdn_cost,0)),
475 SUM(NVL(txn_revenue,0))
476 BULK COLLECT INTO
477 l_act_currency_code_tab,
478 l_act_quantity_tab,
479 l_act_raw_cost_tab,
480 l_act_brdn_cost_tab,
481 l_act_revenue_tab
482 FROM PA_FP_FCST_GEN_TMP1
483 WHERE project_element_id = p_task_id
484 AND res_list_member_id = p_resource_list_member_id
485 AND data_type_code = 'ETC_FP'
486 GROUP BY txn_currency_code;
487
488 IF l_rate_based_flag = 'N' THEN
489 l_act_quantity_tab := l_act_raw_cost_tab;
490 END IF;
491
492 /* Bug 4085203
493 The total actual amounts should be summed up irrespective of rate based
494 or non rate based. Because for non rate based resource, we used the
495 sum value when plan and actuals are using same one currency. When
496 plan and actuals are using more than one currencies, the flow will
497 not use the sum amounts.*/
498 l_act_quantity_sum := 0;
499 FOR i IN 1..l_act_quantity_tab.count LOOP
500 l_act_quantity_sum := l_act_quantity_sum + l_act_quantity_tab(i);
501 END LOOP;
502
503 ELSIF p_etc_source_code = 'WORKPLAN_RESOURCES' THEN
504 /*Bug fix for 3973511
505 Workplan side only stores amounts in one currency for each planning
506 resource. Instead of relying on pa_progress_utils.get_actuals_for_task
507 to get actuals data, we query directly to pa_budget_lines to get actual
508 data from source workplan budget version */
509 IF P_PA_DEBUG_MODE = 'Y' THEN
510 pa_fp_gen_amount_utils.fp_debug(
511 p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
512 'GET_WP_ACTUALS_FOR_RA',
513 p_module_name => l_module_name,
514 p_log_level => 5);
515 END IF;
516 PA_FP_GEN_FCST_AMT_PUB1.GET_WP_ACTUALS_FOR_RA
517 (P_FP_COLS_SRC_REC => p_fp_cols_src_rec,
518 P_FP_COLS_TGT_REC => p_fp_cols_tgt_rec,
519 P_SRC_RES_ASG_ID => p_src_res_asg_id,
520 P_TASK_ID => p_task_id,
521 P_RES_LIST_MEM_ID => p_resource_list_member_id,
522 P_ACTUALS_THRU_DATE => p_actuals_thru_date,
523 X_ACT_QUANTITY => lx_act_quantity,
524 X_ACT_TXN_CURRENCY_CODE => lx_act_txn_currency_code,
525 X_ACT_TXN_RAW_COST => lx_act_txn_raw_cost,
526 X_ACT_TXN_BRDN_COST => lx_act_txn_brdn_cost,
527 X_ACT_PC_RAW_COST => lx_act_pc_raw_cost,
528 X_ACT_PC_BRDN_COST => lx_act_pc_brdn_cost,
529 X_ACT_PFC_RAW_COST => lx_act_pfc_raw_cost,
530 X_ACT_PFC_BRDN_COST => lx_act_pfc_brdn_cost,
531 X_RETURN_STATUS => x_return_status,
532 X_MSG_COUNT => x_msg_count,
533 X_MSG_DATA => x_msg_data );
534 IF P_PA_DEBUG_MODE = 'Y' THEN
535 pa_fp_gen_amount_utils.fp_debug(
536 p_msg => 'After calling PA_FP_GEN_FCST_AMT_PUB1.'||
537 'GET_WP_ACTUALS_FOR_RA in remain_bdgt:'||x_return_status,
538 p_module_name => l_module_name,
539 p_log_level => 5);
540 END IF;
541 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
542 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
543 END IF;
544
545 l_act_currency_code_tab(1) := lx_act_txn_currency_code;
546 l_act_quantity_tab(1) := lx_act_quantity;
547 l_act_raw_cost_tab(1) := lx_act_txn_raw_cost;
548 l_act_brdn_cost_tab(1):= lx_act_txn_brdn_cost;
549 l_act_revenue_tab(1) := 0;
550
551 IF l_rate_based_flag = 'N' THEN
552 l_act_quantity_tab := l_act_raw_cost_tab;
553 END IF;
554
555 l_act_quantity_sum := l_act_quantity_tab(1);
556 END IF;
557
558
559 /* Check the relationship between total currency codes and actual currency
560 codes. If actual currency codes are subset of total currency codes, then,
561 take currency based approach; otherwise, take prorating based approach.
562 'C' means take currency based calculation
563 'P' means take prorating based calculation */
564
565 SELECT COUNT(*)
566 INTO l_currency_count_act_min_tot
567 FROM (
568 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
569 DISTINCT txn_currency_code
570 FROM PA_FP_FCST_GEN_TMP1
571 WHERE project_element_id = p_task_id
572 AND res_list_member_id = p_resource_list_member_id
573 AND data_type_code = DECODE(P_ETC_SOURCE_CODE,
574 'WORKPLAN_RESOURCES', 'ETC_WP',
575 'FINANCIAL_PLAN', 'ETC_FP')
576 MINUS
577 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
578 DISTINCT txn_currency_code
579 FROM PA_FP_CALC_AMT_TMP2
580 WHERE resource_assignment_id = p_src_res_asg_id
581 AND transaction_source_code = p_etc_source_code
582 ) WHERE rownum = 1;
583
584 IF l_currency_count_act_min_tot = 0 THEN
585 l_currency_prorate_act_flag := 'C';
586 ELSE
587 l_currency_prorate_act_flag := 'P';
588 END IF;
589
590 /*Bug fix: 4085203: If there only exists one plan currency,
591 one actual currency and they are same, no matter it's rate
592 based resource or non rate based resource, if etc quantity is
593 calculated as less or equal to zero, then don't generate the ETC.*/
594 IF l_act_currency_code_tab.count = 1 AND l_tot_currency_code_tab.count = 1 THEN
595 l_etc_quantity_sum := l_tot_quantity_sum - l_act_quantity_sum;
596 IF l_etc_quantity_sum <= 0 THEN
597 IF P_PA_DEBUG_MODE = 'Y' THEN
598 PA_DEBUG.RESET_CURR_FUNCTION;
599 END IF;
600 RETURN;
601 ELSE
602 l_etc_quantity_tab(1) := l_etc_quantity_sum;
603 END IF;
604 ELSE
605 l_exit_flag := 'N';
606 IF l_currency_prorate_act_flag = 'C' THEN
607 FOR i IN 1..l_tot_currency_code_tab.count LOOP
608 IF l_exit_flag = 'Y' THEN
609 EXIT;
610 END IF;
611 l_etc_quantity_tab(i) := l_tot_quantity_tab(i);
612 FOR j IN 1..l_act_currency_code_tab.count LOOP
613 IF l_tot_currency_code_tab(i) = l_act_currency_code_tab(j) THEN
614 l_etc_quantity_tab(i) := l_etc_quantity_tab(i) - l_act_quantity_tab(j);
615 IF l_etc_quantity_tab(i) <= 0 THEN
616 l_currency_prorate_act_flag := 'P';
617 l_etc_quantity_tab.delete;
618 l_exit_flag := 'Y';
619 EXIT;
620 END IF;
621 END IF;
622 END LOOP;
623 END LOOP;
624 END IF;
625
626 IF l_currency_prorate_act_flag = 'P' THEN
627 IF l_rate_based_flag = 'N' THEN
628 l_currency_flag := 'PC_TC';
629 ELSIF l_rate_based_flag = 'Y' THEN
630 l_etc_quantity_sum := l_tot_quantity_sum - l_act_quantity_sum;
631 IF l_etc_quantity_sum <= 0 THEN
632 /* If actual quantity >= total planned quantity, no non-commitment ETC
633 available, only actual and commitment amounts need to be spreaded */
634 IF P_PA_DEBUG_MODE = 'Y' THEN
635 PA_DEBUG.RESET_CURR_FUNCTION;
636 END IF;
637 RETURN;
638 END IF;
639
640 FOR i IN 1..l_tot_currency_code_tab.count LOOP
641 IF l_tot_quantity_sum <> 0 THEN
642 l_etc_quantity_tab(i) := l_etc_quantity_sum
643 * (l_tot_quantity_tab (i) / l_tot_quantity_sum) ;
644 ELSE
645 l_etc_quantity_tab(i) := NULL;
646 END IF;
647 /* hr_utility.trace(i||'th');
648 hr_utility.trace('etc qty '||l_etc_qty );
649 hr_utility.trace('etc curr'||l_ETC_CURRENCY_CODE );
650 hr_utility.trace('etc rc '||l_etc_txn_raw_cost );
651 hr_utility.trace('etc bc '||l_etc_txn_brdn_cost ); */
652 END LOOP;
653 END IF;
654 END IF;
655 END IF;
656
657 /*currency_flag may get changed to 'PC_TC', when actual currencies is subset of
658 planning currencies, target resource is non_rate_based, but actual amount for
659 one particular currency is less than plan amount. Then we need to revert from
660 currency based approach to prorating based approach.For non_rate_based resource,
661 prorating falls in to currency code of 'PC_TC'.*/
662 IF l_currency_flag = 'TC' THEN
663 /*When not taking periodic rates, we need to calculate out the average
664 rates from the source resource assignments that are mapped to the current
665 target resource assignment.*/
666 FOR i IN 1..l_tot_currency_code_tab.count LOOP
667 SELECT /*+ INDEX(pa_fp_calc_amt_tmp2,PA_FP_CALC_AMT_TMP2_N2)*/
668 NVL(SUM(NVL(total_plan_quantity,0)),0),
669 NVL(SUM(NVL(total_txn_raw_cost,0)),0),
670 NVL(SUM(NVL(total_txn_burdened_cost,0)),0),
671 NVL(SUM(NVL(total_txn_revenue,0)),0),
672 NVL(SUM(NVL(total_pc_raw_cost,0)),0),
673 NVL(SUM(NVL(total_pc_burdened_cost,0)),0),
674 NVL(SUM(NVL(total_pc_revenue,0)),0)
675 INTO l_txn_rate_quantity,
676 l_txn_rate_raw_cost,
677 l_txn_rate_brdn_cost,
678 l_txn_rate_revenue,
679 l_pc_rate_raw_cost,
680 l_pc_rate_brdn_cost,
681 l_pc_rate_revenue
682 FROM pa_fp_calc_amt_tmp2
683 WHERE resource_assignment_id = p_src_res_asg_id
684 AND txn_currency_code = l_tot_currency_code_tab(i)
685 AND transaction_source_code in ('FINANCIAL_PLAN',
686 'WORKPLAN_RESOURCES');
687
688 IF l_rate_based_flag = 'N' THEN
689 l_txn_rate_quantity := l_txn_rate_raw_cost;
690 END IF;
691
692 IF l_txn_rate_quantity <> 0 THEN
693 l_txn_raw_cost_rate_tab(i) := l_txn_rate_raw_cost
694 / l_txn_rate_quantity;
695 l_txn_brdn_cost_rate_tab(i) := l_txn_rate_brdn_cost
696 / l_txn_rate_quantity;
697 l_txn_revenue_rate_tab(i) := l_txn_rate_revenue
698 / l_txn_rate_quantity;
699 l_pc_raw_cost_rate_tab(i) := l_pc_rate_raw_cost
700 / l_txn_rate_quantity;
701 l_pc_brdn_cost_rate_tab(i) := l_pc_rate_brdn_cost
702 / l_txn_rate_quantity;
703 l_pc_revenue_rate_tab(i) := l_pc_rate_revenue
704 / l_txn_rate_quantity;
705 ELSE
706 l_txn_raw_cost_rate_tab(i) := NULL;
707 l_txn_brdn_cost_rate_tab(i) := NULL;
708 l_txn_revenue_rate_tab(i) := NULL;
709 l_pc_raw_cost_rate_tab(i) := NULL;
710 l_pc_brdn_cost_rate_tab(i) := NULL;
711 l_pc_revenue_rate_tab(i) := NULL;
712 END IF;
713 END LOOP;
714
715 /*Bulk insert
716 If commitment is not included, record is inserted directly as 'ETC'
717 record, if commitment is to be considered, record is inserted as
718 'TOTAL_ETC' for further processing. */
719 IF P_FP_COLS_TGT_REC.X_GEN_INCL_OPEN_COMM_FLAG = 'Y' THEN
720 l_transaction_source_code := 'TOTAL_ETC';
721 ELSE
722 l_transaction_source_code := 'ETC';
723 END IF;
724 FORALL i IN 1..l_etc_quantity_tab.count
725 INSERT INTO PA_FP_CALC_AMT_TMP2 (
726 RESOURCE_ASSIGNMENT_ID,
727 TARGET_RES_ASG_ID,
728 ETC_CURRENCY_CODE,
729 ETC_PLAN_QUANTITY,
730 ETC_TXN_RAW_COST,
731 ETC_TXN_BURDENED_COST,
732 ETC_TXN_REVENUE,
733 ETC_PC_RAW_COST,
734 ETC_PC_BURDENED_COST,
735 ETC_PC_REVENUE,
736 TRANSACTION_SOURCE_CODE )
737 VALUES (
738 P_SRC_RES_ASG_ID,
739 P_TGT_RES_ASG_ID,
740 l_tot_currency_code_tab(i),
741 l_etc_quantity_tab(i),
742 l_etc_quantity_tab(i) * l_txn_raw_cost_rate_tab(i),
743 l_etc_quantity_tab(i) * l_txn_brdn_cost_rate_tab(i),
744 l_etc_quantity_tab(i) * l_txn_revenue_rate_tab(i),
745 l_etc_quantity_tab(i) * l_pc_raw_cost_rate_tab(i),
746 l_etc_quantity_tab(i) * l_pc_brdn_cost_rate_tab(i),
747 l_etc_quantity_tab(i) * l_pc_revenue_rate_tab(i),
748 l_transaction_source_code);
749 END IF;
750 END IF;
751 /**************NOW WE HAVE ALL ETC DATA IN TC*************/
752
753 IF l_currency_flag = 'PC_TC' THEN
754 /*Take PC for calculation, then convert back to TC.
755 This only happens for non rate based resources*/
756
757 /*No matter ETC source is 'FINANCIAL_PLAN' or 'WORKPLAN_RESOURCES',
758 always get total plan amounts in PC from financial data model*/
759 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
760 txn_currency_code,
761 SUM(NVL(total_plan_quantity,0)),
762 SUM(NVL(total_pc_raw_cost,0)),
763 SUM(NVL(total_pc_burdened_cost,0)),
764 SUM(NVL(total_pc_revenue,0))
765 BULK COLLECT INTO
766 l_tot_currency_code_tab,
767 l_tot_quantity_pc_tab,
768 l_tot_raw_cost_pc_tab,
769 l_tot_brdn_cost_pc_tab,
770 l_tot_revenue_pc_tab
771 FROM PA_FP_CALC_AMT_TMP2
772 WHERE resource_assignment_id = p_src_res_asg_id
773 AND transaction_source_code = p_etc_source_code
774 GROUP BY txn_currency_code;
775
776 IF l_target_version_type = 'COST' OR l_target_version_type = 'ALL' THEN
777 l_tot_quantity_pc_tab := l_tot_raw_cost_pc_tab;
778 ELSE
779 l_tot_quantity_pc_tab := l_tot_revenue_pc_tab;
780 END IF;
781
782 l_tot_quantity_pc_sum := 0;
783 FOR i IN 1..l_tot_quantity_pc_tab.count LOOP
784 l_tot_quantity_pc_sum := l_tot_quantity_pc_sum + l_tot_quantity_pc_tab(i);
785 END LOOP;
786
787 IF p_etc_source_code = 'FINANCIAL_PLAN' THEN
788 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
789 NVL(SUM( DECODE(l_rate_based_flag,
790 'Y', NVL(quantity,0),
791 'N', NVL(prj_raw_cost,0))),0)
792 INTO l_act_quantity_pc_sum
793 FROM PA_FP_FCST_GEN_TMP1
794 WHERE project_element_id = p_task_id
795 AND res_list_member_id = p_resource_list_member_id
796 AND data_type_code = 'ETC_FP';
797
798 ELSIF p_etc_source_code = 'WORKPLAN_RESOURCES' THEN
799 /*Workplan side only stores amounts in one currency for each planning
800 resource, so still rely on pa_progress_utils.get_actuals_for_task to
801 get actuals data. This part needs to be revisted when workplan side is
802 changed to support multi currencies.*/
803 IF P_PA_DEBUG_MODE = 'Y' THEN
804 pa_fp_gen_amount_utils.fp_debug(
805 p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
806 'GET_WP_ACTUALS_FOR_RA',
807 p_module_name => l_module_name,
808 p_log_level => 5);
809 END IF;
810 PA_FP_GEN_FCST_AMT_PUB1.GET_WP_ACTUALS_FOR_RA
811 (P_FP_COLS_SRC_REC => p_fp_cols_src_rec,
812 P_FP_COLS_TGT_REC => p_fp_cols_tgt_rec,
813 P_SRC_RES_ASG_ID => p_src_res_asg_id,
814 P_TASK_ID => p_task_id,
815 P_RES_LIST_MEM_ID => p_resource_list_member_id,
816 P_ACTUALS_THRU_DATE => p_actuals_thru_date,
817 X_ACT_QUANTITY => lx_act_quantity,
818 X_ACT_TXN_CURRENCY_CODE => lx_act_txn_currency_code,
819 X_ACT_TXN_RAW_COST => lx_act_txn_raw_cost,
820 X_ACT_TXN_BRDN_COST => lx_act_txn_brdn_cost,
821 X_ACT_PC_RAW_COST => lx_act_pc_raw_cost,
822 X_ACT_PC_BRDN_COST => lx_act_pc_brdn_cost,
823 X_ACT_PFC_RAW_COST => lx_act_pfc_raw_cost,
824 X_ACT_PFC_BRDN_COST => lx_act_pfc_brdn_cost,
825 X_RETURN_STATUS => x_return_status,
826 X_MSG_COUNT => x_msg_count,
827 X_MSG_DATA => x_msg_data );
828 IF P_PA_DEBUG_MODE = 'Y' THEN
829 pa_fp_gen_amount_utils.fp_debug(
830 p_msg => 'After calling PA_FP_GEN_FCST_AMT_PUB1.'||
831 'GET_WP_ACTUALS_FOR_RA in remain_bdgt:'||x_return_status,
832 p_module_name => l_module_name,
833 p_log_level => 5);
834 END IF;
835 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
836 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
837 END IF;
838
839 l_act_quantity_pc_sum := lx_act_pc_raw_cost;
840
841 END IF;
842
843 /*Prorate total ETC quantity in PC based according to the transaction
844 currency codes from the plan totals.*/
845 /*Get total ETC quantity and Prorate ETC quantity*/
846 l_etc_quantity_pc_sum := l_tot_quantity_pc_sum - l_act_quantity_pc_sum;
847 IF l_etc_quantity_pc_sum <= 0 THEN
848 /* actual quantity > total ETC quantity,only need to spread
849 commitment data and actual data*/
850 IF P_PA_DEBUG_MODE = 'Y' THEN
851 PA_DEBUG.RESET_CURR_FUNCTION;
852 END IF;
853 RETURN;
854 END IF;
855 FOR i IN 1..l_tot_currency_code_tab.count LOOP
856 IF NVL(l_tot_quantity_pc_sum,0) <> 0 THEN
857 l_etc_quantity_pc_tab(i) := l_etc_quantity_pc_sum
858 * (l_tot_quantity_pc_tab(i) / l_tot_quantity_pc_sum) ;
859 ELSE
860 l_etc_quantity_pc_tab(i) := NULL;
861 --l_etc_quantity_pc_tab(i) := l_etc_quantity_pc_sum; -- ???
862 END IF;
863 END LOOP;
864
865 /* Convert PC into TC */
866 FOR i IN 1..l_tot_currency_code_tab.count LOOP
867 IF l_tot_currency_code_tab(i) = l_pc_currency_code THEN
868 l_etc_quantity_tab(i) := l_etc_quantity_pc_tab(i);
869 ELSE
870 l_etc_quantity_tab(i) := NULL;
871 BEGIN
872 SELECT task_id,
873 planning_start_date
874 INTO l_task_id,
875 l_planning_start_date
876 FROM pa_resource_assignments
877 WHERE resource_assignment_id = p_src_res_asg_id;
878 EXCEPTION
879 WHEN NO_DATA_FOUND THEN
880 l_task_id := NULL;
881 l_planning_start_date := NULL;
882 END;
883 IF P_PA_DEBUG_MODE = 'Y' THEN
884 pa_fp_gen_amount_utils.fp_debug(
885 p_msg => 'Before calling pa_multi_currency_txn.'||
886 'get_currency_amounts in remain_bdgt',
887 p_module_name => l_module_name,
888 p_log_level => 5);
889 END IF;
890 -- Bug 4091344: Changed P_status parameter from x_return_status to
891 -- local variable l_status. Afterwards, we check l_status and set
892 -- x_return_status accordingly.
893 pa_multi_currency_txn.get_currency_amounts (
894 P_project_id => p_fp_cols_tgt_rec.x_project_id,
895 P_exp_org_id => NULL,
896 P_calling_module => 'WORKPLAN',
897 P_task_id => l_task_id,
898 P_EI_date => l_planning_start_date,
899 P_denom_raw_cost => l_etc_quantity_pc_tab(i),
900 P_denom_curr_code => l_pc_currency_code,
901 P_acct_curr_code => l_pc_currency_code,
902 P_accounted_flag => 'N',
903 P_acct_rate_date => lx_acc_rate_date,
904 P_acct_rate_type => lx_acct_rate_type,
905 P_acct_exch_rate => lx_acct_exch_rate,
906 P_acct_raw_cost => lx_acct_raw_cost,
907 P_project_curr_code => l_tot_currency_code_tab(i),
908 P_project_rate_type => lx_project_rate_type,
909 P_project_rate_date => lx_project_rate_date,
910 P_project_exch_rate => lx_project_exch_rate,
911 P_project_raw_cost => l_etc_quantity_tab(i),
912 P_projfunc_curr_code=> l_pc_currency_code,
913 P_projfunc_cost_rate_type => lx_projfunc_cost_rate_type,
914 P_projfunc_cost_rate_date => lx_projfunc_cost_rate_date,
915 P_projfunc_cost_exch_rate => lx_projfunc_cost_exch_rate,
916 P_projfunc_raw_cost => l_projfunc_raw_cost,
917 P_system_linkage => 'NER',
918 P_status => l_status,
919 P_stage => x_msg_count);
920
921
922 IF lx_project_exch_rate IS NULL OR l_status IS NOT NULL THEN
923 x_return_status := FND_API.G_RET_STS_ERROR;
924 g_project_name := NULL;
925 BEGIN
926 SELECT name INTO g_project_name from
927 PA_PROJECTS_ALL WHERE
928 project_id = p_fp_cols_tgt_rec.x_project_id;
929 EXCEPTION
930 WHEN OTHERS THEN
931 g_project_name := NULL;
932 END;
933 PA_UTILS.ADD_MESSAGE
934 ( p_app_short_name => 'PA'
935 ,p_msg_name => 'PA_FP_PROJ_NO_TXNCONVRATE'
936 ,p_token1 => 'G_PROJECT_NAME'
937 ,p_value1 => g_project_name
938 ,p_token2 => 'FROMCURRENCY'
939 ,p_value2 => l_pc_currency_code
940 ,p_token3 => 'TOCURRENCY'
941 ,p_value3 => l_tot_currency_code_tab(i) );
942 x_msg_data := 'PA_FP_PROJ_NO_TXNCONVRATE';
943 END IF;
944 IF P_PA_DEBUG_MODE = 'Y' THEN
945 pa_fp_gen_amount_utils.fp_debug(
946 p_msg => 'After calling pa_multi_currency_txn.'||
947 'get_currency_amounts in remain_bdgt:'||x_return_status,
948 p_module_name => l_module_name,
949 p_log_level => 5);
950 END IF;
951 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
952 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
953 END IF;
954 END IF;
955 END LOOP;
956
957 /*When not taking periodic rates, we need to calculate out the average rates
958 from the source resource assignments that are mapped to the current target
959 resource assignment.*/
960
961 FOR i IN 1..l_tot_currency_code_tab.count LOOP
962 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
963 NVL(SUM(NVL(total_plan_quantity,0)),0),
964 NVL(SUM(NVL(total_txn_raw_cost,0)),0),
965 NVL(SUM(NVL(total_txn_burdened_cost,0)),0),
966 NVL(SUM(NVL(total_txn_revenue,0)),0),
967 NVL(SUM(NVL(total_pc_raw_cost,0)),0),
968 NVL(SUM(NVL(total_pc_burdened_cost,0)),0),
969 NVL(SUM(NVL(total_pc_revenue,0)),0)
970 INTO l_txn_rate_quantity,
971 l_txn_rate_raw_cost,
972 l_txn_rate_brdn_cost,
973 l_txn_rate_revenue,
974 l_pc_rate_raw_cost,
975 l_pc_rate_brdn_cost,
976 l_pc_rate_revenue
977 FROM pa_fp_calc_amt_tmp2
978 WHERE resource_assignment_id = p_src_res_asg_id
979 AND txn_currency_code = l_tot_currency_code_tab(i)
980 AND transaction_source_code in ('FINANCIAL_PLAN' ,
981 'WORKPLAN_RESOURCES');
982
983 l_txn_raw_cost_rate_tab(i) := 1;
984 l_txn_rate_quantity := l_txn_rate_raw_cost;
985
986 IF l_txn_rate_raw_cost <> 0 THEN
987 l_txn_brdn_cost_rate_tab(i) := l_txn_rate_brdn_cost
988 / l_txn_rate_raw_cost;
989 l_txn_revenue_rate_tab(i) := l_txn_rate_revenue
990 / l_txn_rate_raw_cost;
991 l_pc_raw_cost_rate_tab(i) := l_pc_rate_raw_cost
992 / l_txn_rate_raw_cost;
993 l_pc_brdn_cost_rate_tab(i) := l_pc_rate_brdn_cost
994 / l_txn_rate_raw_cost;
995 l_pc_revenue_rate_tab(i) := l_pc_rate_revenue
996 / l_txn_rate_raw_cost;
997 ELSE
998 l_txn_brdn_cost_rate_tab(i) := NULL;
999 l_txn_revenue_rate_tab(i) := NULL;
1000 l_pc_raw_cost_rate_tab(i) := NULL;
1001 l_pc_brdn_cost_rate_tab(i) := NULL;
1002 l_pc_revenue_rate_tab(i) := NULL;
1003 END IF;
1004 END LOOP;
1005
1006 /* Bulk insert
1007 If commitment is not included, record is inserted directly as 'ETC'
1008 record,if commitment is to be considered, record is inserted as
1009 'TOTAL_ETC' for further processing.*/
1010 IF P_FP_COLS_TGT_REC.X_GEN_INCL_OPEN_COMM_FLAG = 'Y' THEN
1011 l_transaction_source_code := 'TOTAL_ETC';
1012 ELSE
1013 l_transaction_source_code := 'ETC';
1014 END IF;
1015
1016 FORALL i IN 1..l_etc_quantity_tab.count
1017 INSERT INTO PA_FP_CALC_AMT_TMP2 (
1018 RESOURCE_ASSIGNMENT_ID,
1019 TARGET_RES_ASG_ID,
1020 ETC_CURRENCY_CODE,
1021 ETC_PLAN_QUANTITY,
1022 ETC_TXN_RAW_COST,
1023 ETC_TXN_BURDENED_COST,
1024 ETC_TXN_REVENUE,
1025 ETC_PC_RAW_COST,
1026 ETC_PC_BURDENED_COST,
1027 ETC_PC_REVENUE,
1028 TRANSACTION_SOURCE_CODE )
1029 VALUES (
1030 P_SRC_RES_ASG_ID,
1031 P_TGT_RES_ASG_ID,
1032 l_tot_currency_code_tab(i),
1033 l_etc_quantity_tab(i),
1034 l_etc_quantity_tab(i) * l_txn_raw_cost_rate_tab(i),
1035 l_etc_quantity_tab(i) * l_txn_brdn_cost_rate_tab(i),
1036 l_etc_quantity_tab(i) * l_txn_revenue_rate_tab(i),
1037 l_etc_quantity_tab(i) * l_pc_raw_cost_rate_tab(i),
1038 l_etc_quantity_tab(i) * l_pc_brdn_cost_rate_tab(i),
1039 l_etc_quantity_tab(i) * l_pc_revenue_rate_tab(i),
1040 l_transaction_source_code);
1041
1042 /***************NOW WE HAVE ALL ETC DATA IN PC_TC*************/
1043
1044 END IF;
1045 /* End the check for 'PC', 'TC' and 'PC_TC'*/
1046
1047 IF P_PA_DEBUG_MODE = 'Y' THEN
1048 PA_DEBUG.RESET_CURR_FUNCTION;
1049 END IF;
1050 EXCEPTION
1051 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1052 l_msg_count := FND_MSG_PUB.count_msg;
1053 IF l_msg_count = 1 THEN
1054 PA_INTERFACE_UTILS_PUB.get_messages
1055 ( p_encoded => FND_API.G_TRUE,
1056 p_msg_index => 1,
1057 p_msg_count => l_msg_count,
1058 p_msg_data => l_msg_data,
1059 p_data => l_data,
1060 p_msg_index_out => l_msg_index_out);
1061 x_msg_data := l_data;
1062 x_msg_count := l_msg_count;
1063 ELSE
1064 x_msg_count := l_msg_count;
1065 END IF;
1066
1067 ROLLBACK;
1068 x_return_status := FND_API.G_RET_STS_ERROR;
1069
1070 IF P_PA_DEBUG_MODE = 'Y' THEN
1071 pa_fp_gen_amount_utils.fp_debug
1072 (p_msg => 'Invalid Arguments Passed',
1073 p_module_name => l_module_name,
1074 p_log_level => 5);
1075 PA_DEBUG.RESET_CURR_FUNCTION;
1076 END IF;
1077 RAISE;
1078 WHEN OTHERS THEN
1079 rollback;
1080 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1081 x_msg_count := 1;
1082 x_msg_data := substr(sqlerrm,1,240);
1083 --dbms_output.put_line('error msg :'||x_msg_data);
1084 FND_MSG_PUB.add_exc_msg
1085 ( p_pkg_name => 'PA_FP_GEN_FCST_AMT_PUB3',
1086 p_procedure_name => 'GEN_ETC_REMAIN_BDGT_AMTS',
1087 p_error_text => substr(sqlerrm,1,240));
1088
1089 IF P_PA_DEBUG_MODE = 'Y' THEN
1090 pa_fp_gen_amount_utils.fp_debug
1091 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1092 p_module_name => l_module_name,
1093 p_log_level => 5);
1094 PA_DEBUG.RESET_CURR_FUNCTION;
1095 END IF;
1096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1097 END GET_ETC_REMAIN_BDGT_AMTS;
1098
1099 PROCEDURE CHECK_SINGLE_CURRENCY
1100 (P_TGT_RES_ASG_ID IN PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE,
1101 X_SINGLE_CURRENCY_FLAG OUT NOCOPY VARCHAR2,
1102 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1103 X_MSG_COUNT OUT NOCOPY NUMBER,
1104 X_MSG_DATA OUT NOCOPY VARCHAR2)
1105 IS
1106 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_FCST_AMT_PUB3.CHECK_SINGLE_CURRENCY';
1107
1108 l_currency_count_for_flag VARCHAR2(1);
1109
1110 l_msg_count NUMBER;
1111 l_msg_data VARCHAR2(2000);
1112 l_data VARCHAR2(2000);
1113 l_msg_index_out NUMBER:=0;
1114 BEGIN
1115
1116 IF p_pa_debug_mode = 'Y' THEN
1117 pa_debug.set_curr_function( p_function => 'CHECK_SINGLE_CURRENCY',
1118 p_debug_mode => p_pa_debug_mode);
1119 END IF;
1120
1121 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1122 X_MSG_COUNT := 0;
1123
1124 IF P_PA_DEBUG_MODE = 'Y' THEN
1125 PA_DEBUG.RESET_CURR_FUNCTION;
1126 END IF;
1127
1128 SELECT COUNT(*) INTO l_currency_count_for_flag FROM (
1129 SELECT /*+ INDEX(tot_tmp,PA_FP_CALC_AMT_TMP2_N1)*/
1130 DISTINCT txn_currency_code
1131 FROM PA_FP_CALC_AMT_TMP2
1132 WHERE target_res_asg_id = p_tgt_res_asg_id
1133 AND (transaction_source_code = 'FINANCIAL_PLAN'
1134 OR transaction_source_code = 'WORKPLAN_RESOURCES'
1135 OR transaction_source_code = 'COMMITMENT')
1136 UNION
1137 SELECT /*+ INDEX(tot_tmp,PA_FP_CALC_AMT_TMP2_N1)*/
1138 DISTINCT act_tmp.txn_currency_code
1139 FROM PA_FP_FCST_GEN_TMP1 act_tmp,
1140 PA_FP_CALC_AMT_TMP2 tot_tmp
1141 WHERE act_tmp.source_id = tot_tmp.resource_assignment_id
1142 AND tot_tmp.target_res_asg_id = p_tgt_res_asg_id
1143 ) WHERE rownum <= 2;
1144
1145 IF l_currency_count_for_flag <= 1 THEN
1146 X_SINGLE_CURRENCY_FLAG := 'Y';
1147 ELSE
1148 X_SINGLE_CURRENCY_FLAG := 'N';
1149 END IF;
1150
1151 IF P_PA_DEBUG_MODE = 'Y' THEN
1152 PA_DEBUG.RESET_CURR_FUNCTION;
1153 END IF;
1154 EXCEPTION
1155 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1156 l_msg_count := FND_MSG_PUB.count_msg;
1157 IF l_msg_count = 1 THEN
1158 PA_INTERFACE_UTILS_PUB.get_messages
1159 ( p_encoded => FND_API.G_TRUE,
1160 p_msg_index => 1,
1161 p_msg_count => l_msg_count,
1162 p_msg_data => l_msg_data,
1163 p_data => l_data,
1164 p_msg_index_out => l_msg_index_out);
1165 x_msg_data := l_data;
1166 x_msg_count := l_msg_count;
1167 ELSE
1168 x_msg_count := l_msg_count;
1169 END IF;
1170
1171 ROLLBACK;
1172 x_return_status := FND_API.G_RET_STS_ERROR;
1173
1174 IF P_PA_DEBUG_MODE = 'Y' THEN
1175 pa_fp_gen_amount_utils.fp_debug
1176 (p_msg => 'Invalid Arguments Passed',
1177 p_module_name => l_module_name,
1178 p_log_level => 5);
1179 PA_DEBUG.RESET_CURR_FUNCTION;
1180 END IF;
1181 RAISE;
1182 WHEN OTHERS THEN
1183 rollback;
1184 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1185 x_msg_count := 1;
1186 x_msg_data := substr(sqlerrm,1,240);
1187 --dbms_output.put_line('error msg :'||x_msg_data);
1188 FND_MSG_PUB.add_exc_msg
1189 ( p_pkg_name => 'PA_FP_GEN_FCST_AMT_PUB3',
1190 p_procedure_name => 'CHECK_SINGLE_CURRENCY',
1191 p_error_text => substr(sqlerrm,1,240));
1192
1193 IF P_PA_DEBUG_MODE = 'Y' THEN
1194 pa_fp_gen_amount_utils.fp_debug
1195 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1196 p_module_name => l_module_name,
1197 p_log_level => 5);
1198 PA_DEBUG.RESET_CURR_FUNCTION;
1199 END IF;
1200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1201 END CHECK_SINGLE_CURRENCY;
1202
1203
1204 /* Assumption:
1205 1.Before getting into this procedure, we have called all ETC methods to derive the total
1206 ETC quantity and populated them in the temporary table PA_FP_CALC_AMT_TMP2 with
1207 transaction source codes of 'TOTAL_ETC'.
1208 2.Commitment can only be considered for cost/all version. For revenue forecast version,
1209 user can't select include commitment option from the UI.
1210 3.No matter for cost, revenue or all forecast version, always pick up cost/revenue rate
1211 from the source whenever applicable. */
1212
1213 /* Bug 4369741: Replaced single planning options flag parameter with
1214 * 2 separate parameters - 1 for Workplan and 1 for Financial Plan. */
1215
1216 PROCEDURE GET_ETC_COMMITMENT_AMTS
1217 (P_FP_COLS_TGT_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
1218 P_WP_PLANNING_OPTIONS_FLAG IN VARCHAR2, /* Added for Bug 4369741 */
1219 P_FP_PLANNING_OPTIONS_FLAG IN VARCHAR2, /* Added for Bug 4369741 */
1220 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1221 X_MSG_COUNT OUT NOCOPY NUMBER,
1222 X_MSG_DATA OUT NOCOPY VARCHAR2)
1223 IS
1224 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_FCST_AMT_PUB3.GEN_ETC_COMMITMENT_AMTS';
1225
1226 l_currency_flag VARCHAR2(30);
1227 l_rate_based_flag VARCHAR2(1);
1228 l_currency_count_for_flag NUMBER;
1229 l_prorating_always_flag VARCHAR2(1);
1230 l_target_version_type pa_budget_versions.version_type%type;
1231
1232 l_source_version_type pa_budget_versions.version_type%type; /* Added for IPM */
1233 l_tgt_res_asg_id_tab PA_PLSQL_DATATYPES.NumTabTyp;
1234 l_src_res_asg_id_tab PA_PLSQL_DATATYPES.NumTabTyp; /* Created for bug fix 4117267*/
1235 l_cmt_count NUMBER;
1236
1237 /* For PC amounts */
1238 l_pc_currency_code pa_projects_all.project_currency_code%type;
1239 l_cmt_quantity_pc_pfc NUMBER;
1240 l_cmt_raw_cost_pc_pfc NUMBER;
1241 l_cmt_brdn_cost_pc_pfc NUMBER;
1242 l_cmt_revenue_pc_pfc NUMBER;
1243
1244 l_etc_quantity_pc_pfc NUMBER;
1245 l_etc_noncmt_quantity_pc_pfc NUMBER;
1246
1247 /* For PFC amounts */
1248 l_pfc_currency_code pa_projects_all.project_currency_code%type;
1249 l_rev_gen_method VARCHAR2(3);
1250
1251 /* For TC amounts */
1252 l_etc_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1253 l_etc_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
1254 l_etc_quantity_sum NUMBER;
1255
1256 l_cmt_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1257 l_cmt_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
1258 l_cmt_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1259 l_cmt_brdn_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1260 l_cmt_quantity_sum NUMBER;
1261
1262 l_etc_noncmt_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
1263 l_etc_noncmt_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1264 l_etc_noncmt_brdn_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
1265 l_etc_noncmt_quantity_sum NUMBER;
1266
1267 /*For PC_TC amounts*/
1268 l_etc_quantity_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
1269 l_etc_raw_cost_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
1270 l_etc_brdn_cost_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
1271 l_etc_revenue_pc_tab PA_PLSQL_DATATYPES.NumTabTyp; -- Added for IPM
1272 l_etc_quantity_pc_sum NUMBER;
1273
1274 l_cmt_quantity_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
1275 l_cmt_raw_cost_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
1276 l_cmt_brdn_cost_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
1277 l_cmt_quantity_pc_sum NUMBER;
1278
1279 l_etc_noncmt_quantity_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
1280 l_etc_noncmt_raw_cost_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
1281 l_etc_noncmt_brdn_cost_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
1282 l_etc_noncmt_quantity_pc_sum NUMBER;
1283
1284 /*For average rates*/
1285 l_pc_pfc_rate_quantity NUMBER;
1286 l_pc_pfc_rate_raw_cost NUMBER;
1287 l_pc_pfc_rate_brdn_cost NUMBER;
1288 l_pc_pfc_rate_revenue NUMBER;
1289 l_txn_rate_quantity NUMBER;
1290 l_txn_rate_raw_cost NUMBER;
1291 l_txn_rate_brdn_cost NUMBER;
1292 l_txn_rate_revenue NUMBER;
1293
1294 l_pc_pfc_raw_cost_rate NUMBER;
1295 l_pc_pfc_brdn_cost_rate NUMBER;
1296 l_pc_pfc_revenue_rate NUMBER;
1297 l_txn_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1298 l_txn_brdn_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1299 l_txn_revenue_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
1300
1301 l_transaction_source_code VARCHAR2(30);
1302
1303 /*For txn currency conversion*/
1304 l_task_id pa_tasks.task_id%type;
1305 l_planning_start_date pa_resource_assignments.planning_start_date%type;
1306 lx_acc_rate_date DATE;
1307 lx_acct_rate_type VARCHAR2(50);
1308 lx_acct_exch_rate NUMBER;
1309 lx_acct_raw_cost NUMBER;
1310 lx_project_rate_type VARCHAR2(50);
1311 lx_project_rate_date DATE;
1312 lx_project_exch_rate NUMBER;
1313 lx_projfunc_cost_rate_type VARCHAR2(50);
1314 lx_projfunc_cost_rate_date DATE;
1315 lx_projfunc_cost_exch_rate NUMBER;
1316 l_projfunc_raw_cost NUMBER;
1317
1318 l_currency_prorate_cmt_flag VARCHAR2(1);
1319 l_currency_count_cmt_min_tot NUMBER;
1320 l_exit_flag VARCHAR2(1);
1321 l_continue_loop_flag VARCHAR2(1);
1322 l_msg_count NUMBER;
1323 l_msg_data VARCHAR2(2000);
1324 l_data VARCHAR2(2000);
1325 l_msg_index_out NUMBER:=0;
1326
1327 l_dummy NUMBER;
1328
1329 /* Bug 4369741: Added cursor src_tgt_cur_wp_fp_opt_same to be used in
1330 * the following scenarios:
1331 * 1. Target ETC generation source = 'WORKPLAN_RESOURCES'
1332 * P_WP_PLANNING_OPTIONS_FLAG = Y
1333 * 2. Target ETC generation source = 'FINANCIAL_PLAN'
1334 * P_FP_PLANNING_OPTIONS_FLAG = Y
1335 * 3. Target ETC generation source = 'TASK_LEVEL_SEL'
1336 * P_WP_PLANNING_OPTIONS_FLAG = Y
1337 * P_FP_PLANNING_OPTIONS_FLAG = Y */
1338
1339 CURSOR src_tgt_cur_wp_fp_opt_same IS
1340 SELECT DISTINCT target_res_asg_id,
1341 resource_assignment_id
1342 FROM PA_FP_CALC_AMT_TMP2
1343 WHERE TRANSACTION_SOURCE_CODE = 'TOTAL_ETC';
1344
1345 /* Bug 4369741: Added cursor src_tgt_cur_wp_fp_opt_diff to be used in
1346 * the following scenarios:
1347 * 1. Target ETC generation source = 'WORKPLAN_RESOURCES'
1348 * P_WP_PLANNING_OPTIONS_FLAG = N
1349 * 2. Target ETC generation source = 'FINANCIAL_PLAN'
1350 * P_FP_PLANNING_OPTIONS_FLAG = N
1351 * 3. Target ETC generation source = 'TASK_LEVEL_SEL'
1352 * P_WP_PLANNING_OPTIONS_FLAG = N
1353 * P_FP_PLANNING_OPTIONS_FLAG = N */
1354
1355 CURSOR src_tgt_cur_wp_fp_opt_diff IS
1356 SELECT DISTINCT target_res_asg_id,
1357 NULL
1358 FROM PA_FP_CALC_AMT_TMP2
1359 WHERE TRANSACTION_SOURCE_CODE = 'TOTAL_ETC';
1360
1361 /* Bug 4369741: Added cursor src_tgt_cur_wp_opt_same to be used in
1362 * the following scenarios:
1363 * 1. Target ETC generation source = 'TASK_LEVEL_SEL'
1364 * P_WP_PLANNING_OPTIONS_FLAG = Y
1365 * P_FP_PLANNING_OPTIONS_FLAG = N */
1366
1367 CURSOR src_tgt_cur_wp_opt_same IS
1368 SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP1_N1)*/
1369 DISTINCT tmp.target_res_asg_id tgt_res_asg_id,
1370 tmp.resource_assignment_id src_res_asg_id
1371 FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1372 PA_FP_CALC_AMT_TMP2 tmp
1373 WHERE tmp.TRANSACTION_SOURCE_CODE = 'TOTAL_ETC'
1374 AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id
1375 AND tmp_ra.transaction_source_code = 'WORKPLAN_RESOURCES'
1376 UNION ALL
1377 SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP1_N1)*/
1378 DISTINCT tmp.target_res_asg_id tgt_res_asg_id,
1379 NULL src_res_asg_id
1380 FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1381 PA_FP_CALC_AMT_TMP2 tmp
1382 WHERE tmp.TRANSACTION_SOURCE_CODE = 'TOTAL_ETC'
1383 AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id
1384 AND tmp_ra.transaction_source_code = 'FINANCIAL_PLAN';
1385
1386 /* Bug 4369741: Added cursor src_tgt_cur_fp_opt_same to be used in
1387 * the following scenarios:
1388 * 1. Target ETC generation source = 'TASK_LEVEL_SEL'
1389 * P_WP_PLANNING_OPTIONS_FLAG = N
1390 * P_FP_PLANNING_OPTIONS_FLAG = Y */
1391
1392 CURSOR src_tgt_cur_fp_opt_same IS
1393 SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP1_N1)*/
1394 DISTINCT tmp.target_res_asg_id tgt_res_asg_id,
1395 tmp.resource_assignment_id src_res_asg_id
1396 FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1397 PA_FP_CALC_AMT_TMP2 tmp
1398 WHERE tmp.TRANSACTION_SOURCE_CODE = 'TOTAL_ETC'
1399 AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id
1400 AND tmp_ra.transaction_source_code = 'FINANCIAL_PLAN'
1401 UNION ALL
1402 SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP1_N1)*/
1403 DISTINCT tmp.target_res_asg_id tgt_res_asg_id,
1404 NULL src_res_asg_id
1405 FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1406 PA_FP_CALC_AMT_TMP2 tmp
1407 WHERE tmp.TRANSACTION_SOURCE_CODE = 'TOTAL_ETC'
1408 AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id
1409 AND tmp_ra.transaction_source_code = 'WORKPLAN_RESOURCES';
1410
1411 -- Variables added for Bug 5203622
1412 l_other_rej_code PA_BUDGET_LINES.OTHER_REJECTION_CODE%TYPE;
1413 l_other_rej_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
1414
1415 BEGIN
1416 IF p_pa_debug_mode = 'Y' THEN
1417 pa_debug.set_curr_function( p_function => 'GET_ETC_COMMITMENT_AMTS',
1418 p_debug_mode => p_pa_debug_mode);
1419 END IF;
1420
1421 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1422 X_MSG_COUNT := 0;
1423
1424 /* Map the total ETC data from source resource assignments to
1425 target resource assignments */
1426 /* Bug:4155153
1427 IF P_PLANNING_OPTIONS_FLAG is Y, source res asg and target res asg are
1428 one to one; if FLAG is N, source res asg and target res asg might be
1429 many to one, so set l_src_res_asg_id_tab values to null. This src res
1430 asg id will only be used to get the source version rate when planning
1431 options are same.*/
1432
1433 /* Bug 4369741: Before, we fetched source/target resource assignment ids
1434 * based on a single planning options flag. Now, we need to check a flag
1435 * for each source. When the ETC generation source is Workplan, check
1436 * P_WP_PLANNING_OPTIONS_FLAG. When the ETC generation source is Financial
1437 * Plan, check P_FP_PLANNING_OPTIONS_FLAG. When the ETC generation source
1438 * is Task Level Selection, check both P_WP_PLANNING_OPTIONS_FLAG
1439 * and P_FP_PLANNING_OPTIONS_FLAG. */
1440
1441 IF P_FP_COLS_TGT_REC.x_gen_etc_src_code = 'FINANCIAL_PLAN' THEN
1442 IF P_FP_PLANNING_OPTIONS_FLAG = 'Y' THEN
1443 OPEN src_tgt_cur_wp_fp_opt_same;
1444 FETCH src_tgt_cur_wp_fp_opt_same
1445 BULK COLLECT
1446 INTO l_tgt_res_asg_id_tab ,
1447 l_src_res_asg_id_tab;
1448 CLOSE src_tgt_cur_wp_fp_opt_same;
1449 ELSIF P_FP_PLANNING_OPTIONS_FLAG = 'N' THEN
1450 OPEN src_tgt_cur_wp_fp_opt_diff;
1451 FETCH src_tgt_cur_wp_fp_opt_diff
1452 BULK COLLECT
1453 INTO l_tgt_res_asg_id_tab ,
1454 l_src_res_asg_id_tab;
1455 CLOSE src_tgt_cur_wp_fp_opt_diff;
1456 ELSE
1457 -- error handling code stub
1458 l_dummy := 1;
1459 END IF;
1460 ELSIF P_FP_COLS_TGT_REC.x_gen_etc_src_code = 'WORKPLAN_RESOURCES' THEN
1461 IF P_WP_PLANNING_OPTIONS_FLAG = 'Y' THEN
1462 OPEN src_tgt_cur_wp_fp_opt_same;
1463 FETCH src_tgt_cur_wp_fp_opt_same
1464 BULK COLLECT
1465 INTO l_tgt_res_asg_id_tab ,
1466 l_src_res_asg_id_tab;
1467 CLOSE src_tgt_cur_wp_fp_opt_same;
1468 ELSIF P_WP_PLANNING_OPTIONS_FLAG = 'N' THEN
1469 OPEN src_tgt_cur_wp_fp_opt_diff;
1470 FETCH src_tgt_cur_wp_fp_opt_diff
1471 BULK COLLECT
1472 INTO l_tgt_res_asg_id_tab ,
1473 l_src_res_asg_id_tab;
1474 CLOSE src_tgt_cur_wp_fp_opt_diff;
1475 ELSE
1476 -- error handling code stub
1477 l_dummy := 1;
1478 END IF;
1479 ELSIF P_FP_COLS_TGT_REC.x_gen_etc_src_code = 'TASK_LEVEL_SEL' THEN
1480 IF P_WP_PLANNING_OPTIONS_FLAG = 'Y' AND
1481 P_FP_PLANNING_OPTIONS_FLAG = 'Y' THEN
1482 OPEN src_tgt_cur_wp_fp_opt_same;
1483 FETCH src_tgt_cur_wp_fp_opt_same
1484 BULK COLLECT
1485 INTO l_tgt_res_asg_id_tab ,
1486 l_src_res_asg_id_tab;
1487 CLOSE src_tgt_cur_wp_fp_opt_same;
1488 ELSIF P_WP_PLANNING_OPTIONS_FLAG = 'Y' AND
1489 P_FP_PLANNING_OPTIONS_FLAG = 'N' THEN
1490 OPEN src_tgt_cur_wp_opt_same;
1491 FETCH src_tgt_cur_wp_opt_same
1492 BULK COLLECT
1493 INTO l_tgt_res_asg_id_tab ,
1494 l_src_res_asg_id_tab;
1495 CLOSE src_tgt_cur_wp_opt_same;
1496 ELSIF P_WP_PLANNING_OPTIONS_FLAG = 'N' AND
1497 P_FP_PLANNING_OPTIONS_FLAG = 'Y' THEN
1498 OPEN src_tgt_cur_fp_opt_same;
1499 FETCH src_tgt_cur_fp_opt_same
1500 BULK COLLECT
1501 INTO l_tgt_res_asg_id_tab ,
1502 l_src_res_asg_id_tab;
1503 CLOSE src_tgt_cur_fp_opt_same;
1504 ELSIF P_WP_PLANNING_OPTIONS_FLAG = 'N' AND
1505 P_FP_PLANNING_OPTIONS_FLAG = 'N' THEN
1506 OPEN src_tgt_cur_wp_fp_opt_diff;
1507 FETCH src_tgt_cur_wp_fp_opt_diff
1508 BULK COLLECT
1509 INTO l_tgt_res_asg_id_tab ,
1510 l_src_res_asg_id_tab;
1511 CLOSE src_tgt_cur_wp_fp_opt_diff;
1512 ELSE
1513 -- error handling code stub
1514 l_dummy := 1;
1515 END IF;
1516 ELSE
1517 -- error handling code stub
1518 l_dummy := 1;
1519 END IF; -- fetch source/target resource assignment ids
1520
1521 --hr_utility.trace('l_src_res_asg_id_tab TMP2 data : '||l_src_res_asg_id_tab(1));
1522 --hr_utility.trace('l_tgt_res_asg_id_tab TMP2 data : '||l_tgt_res_asg_id_tab(1));
1523 l_target_version_type := p_fp_cols_tgt_rec.x_version_type;
1524 l_pc_currency_code := p_fp_cols_tgt_rec.x_project_currency_code;
1525 l_pfc_currency_code := p_fp_cols_tgt_rec.x_projfunc_currency_code;
1526 /* Get commitment amounts for each target resource assignment */
1527
1528 FOR i IN 1..l_tgt_res_asg_id_tab.count LOOP
1529 -- Bug 4110695: Added wrapper loop for body of main loop so that we can use the
1530 -- pl/sql EXIT command to skip to the next iteration of the main loop to avoid
1531 -- further processing. This was done to replace RETURN with EXIT.
1532 FOR wrapper_loop_iterator IN 1..1 LOOP
1533
1534 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/ COUNT(*)
1535 INTO l_cmt_count
1536 FROM PA_FP_CALC_AMT_TMP2
1537 WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1538 AND transaction_source_code = 'OPEN_COMMITMENTS'
1539 AND rownum = 1;
1540
1541 /* If no commitment available for the current target resource assignment,
1542 simply update the temp table from total_etc records to net etc records. */
1543 IF l_cmt_count = 0 THEN
1544 UPDATE /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/ PA_FP_CALC_AMT_TMP2
1545 SET transaction_source_code = 'ETC'
1546 WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1547 AND transaction_source_code = 'TOTAL_ETC';
1548 ELSE
1549 l_etc_currency_code_tab.delete;
1550 l_etc_quantity_tab.delete;
1551
1552 l_cmt_currency_code_tab.delete;
1553 l_cmt_quantity_tab.delete;
1554 l_cmt_raw_cost_tab.delete;
1555 l_cmt_brdn_cost_tab.delete;
1556
1557 l_etc_noncmt_quantity_tab.delete;
1558 l_etc_noncmt_raw_cost_tab.delete;
1559 l_etc_noncmt_brdn_cost_tab.delete;
1560
1561 l_etc_quantity_pc_tab.delete;
1562 l_etc_raw_cost_pc_tab.delete;
1563 l_etc_brdn_cost_pc_tab.delete;
1564
1565 l_cmt_quantity_pc_tab.delete;
1566 l_cmt_raw_cost_pc_tab.delete;
1567 l_cmt_brdn_cost_pc_tab.delete;
1568
1569 l_etc_noncmt_quantity_pc_tab.delete;
1570 l_etc_noncmt_raw_cost_pc_tab.delete;
1571 l_etc_noncmt_brdn_cost_pc_tab.delete;
1572
1573 l_txn_raw_cost_rate_tab.delete;
1574 l_txn_brdn_cost_rate_tab.delete;
1575
1576 l_exit_flag := 'N';
1577 l_continue_loop_flag := 'N';
1578 /* Default to use Transaction Currency (TC)
1579 If target version is not multi currency enabled, take Project Currency (PC)
1580 If target version is multi currency enabled, the target planning resource is
1581 non rate based, and commitments currencies are not subset of the total ETC
1582 currencies. We need to take PC amounts as quantity, sum up total ETC quantity
1583 minus commitment quantity, prorate this total PC ETC quantity across the total
1584 ETC currencies. Then convert them back from PC to TC. (PC_TC)*/
1585
1586 IF nvl(l_tgt_res_asg_id_tab(i),0) > 0 THEN
1587 SELECT rate_based_flag
1588 INTO l_rate_based_flag
1589 FROM pa_resource_assignments
1590 WHERE resource_assignment_id = l_tgt_res_asg_id_tab(i);
1591 ELSE
1592 l_rate_based_flag:='N';
1593 END IF;
1594
1595 l_currency_flag := 'TC';
1596
1597 l_rev_gen_method := nvl(P_FP_COLS_TGT_REC.x_revenue_derivation_method,PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(P_FP_COLS_TGT_REC.x_project_id)); -- Bug 5462471
1598 --l_rev_gen_method := PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(P_FP_COLS_TGT_REC.x_project_id);
1599
1600 IF (p_fp_cols_tgt_rec.x_version_type = 'REVENUE' and l_rev_gen_method = 'C') THEN
1601 l_currency_flag := 'PFC';
1602 ELSIF p_fp_cols_tgt_rec.X_PLAN_IN_MULTI_CURR_FLAG = 'N' THEN
1603 l_currency_flag := 'PC';
1604 ELSIF l_rate_based_flag = 'N' THEN
1605 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1606 COUNT(*) INTO l_currency_count_for_flag FROM (
1607 SELECT DISTINCT txn_currency_code
1608 FROM PA_FP_CALC_AMT_TMP2
1609 WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1610 AND transaction_source_code = 'OPEN_COMMITMENTS'
1611 MINUS
1612 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1613 DISTINCT etc_currency_code
1614 FROM PA_FP_CALC_AMT_TMP2
1615 WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1616 AND transaction_source_code = 'TOTAL_ETC'
1617 ) WHERE rownum = 1;
1618 IF l_currency_count_for_flag > 0 THEN
1619 l_currency_flag := 'PC_TC';
1620 END IF;
1621 END IF;
1622
1623 /***********BY THIS TIME, WE DECIDED TO USE EITHER PC, TC or PC_PC*********/
1624
1625 IF l_currency_flag = 'PC' or l_currency_flag = 'PFC' THEN
1626 /* Get total etc amounts in PC for each target resource assignment */
1627 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1628 NVL(SUM(ETC_PLAN_QUANTITY),0)
1629 INTO l_etc_quantity_pc_pfc
1630 FROM PA_FP_CALC_AMT_TMP2
1631 WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1632 AND TRANSACTION_SOURCE_CODE = 'TOTAL_ETC';
1633
1634 /* Get commitment amounts in PC for currency target resource assignment */
1635 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1636 NVL(SUM(NVL(total_plan_quantity,0)),0),
1637 DECODE(l_currency_flag,
1638 'PC', NVL(SUM(NVL(total_pc_raw_cost,0)),0),
1639 'PFC', NVL(SUM(NVL(total_pfc_raw_cost,0)),0)),
1640 DECODE(l_currency_flag,
1641 'PC', NVL(SUM(NVL(total_pc_burdened_cost,0)),0),
1642 'PFC', NVL(SUM(NVL(total_pfc_burdened_cost,0)),0))
1643 INTO l_cmt_quantity_pc_pfc,
1644 l_cmt_raw_cost_pc_pfc,
1645 l_cmt_brdn_cost_pc_pfc
1646 FROM PA_FP_CALC_AMT_TMP2
1647 WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1648 AND transaction_source_code = 'OPEN_COMMITMENTS';
1649
1650
1651 IF l_rate_based_flag = 'N' THEN
1652 l_cmt_quantity_pc_pfc := l_cmt_raw_cost_pc_pfc;
1653 END IF;
1654
1655 /* Get total non-commitment ETC quantity */
1656 l_etc_noncmt_quantity_pc_pfc := l_etc_quantity_pc_pfc - l_cmt_quantity_pc_pfc;
1657 -- ER 5726773: Instead of directly checking if (ETC <= 0), let the
1658 -- plan_etc_signs_match function decide if ETC should be generated.
1659 IF NOT pa_fp_fcst_gen_amt_utils.PLAN_ETC_SIGNS_MATCH
1660 (l_etc_quantity_pc_pfc, l_etc_noncmt_quantity_pc_pfc) THEN
1661 /* Only need to spread commitment data and actual data */
1662 /* We need to exit current loop, and continue with the next loop */
1663 l_continue_loop_flag := 'Y';
1664 END IF;
1665
1666 IF l_continue_loop_flag <> 'Y' THEN
1667
1668 -- Bug 4309993: Replaced total_plan_quantity with etc_plan_quantity
1669 -- in the below query to fetch the correct rate quantity.
1670
1671 /*When not taking periodic rates, we need to calculate out the average
1672 rates from the source resource assignments that are mapped to the
1673 current target resource assignment.*/
1674 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1675 NVL(SUM(NVL(etc_plan_quantity,0)),0),
1676 NVL(SUM(DECODE(l_currency_flag,
1677 'PC', NVL(etc_pc_raw_cost,0),
1678 'PFC', NVL(etc_pfc_raw_cost,0))),0),
1679 NVL(SUM(DECODE(l_currency_flag,
1680 'PC', NVL(etc_pc_burdened_cost,0),
1681 'PFC', NVL(etc_pfc_burdened_cost,0))),0),
1682 NVL(SUM(DECODE(l_currency_flag,
1683 'PC', NVL(etc_pc_revenue,0),
1684 'PFC', NVL(etc_pfc_revenue,0))),0)
1685 INTO l_pc_pfc_rate_quantity,
1686 l_pc_pfc_rate_raw_cost,
1687 l_pc_pfc_rate_brdn_cost,
1688 l_pc_pfc_rate_revenue
1689 FROM pa_fp_calc_amt_tmp2
1690 WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1691 AND transaction_source_code = 'TOTAL_ETC';
1692
1693 -- IPM Change:
1694 -- For non-rate-based target transactions,
1695 -- set rate quantity to rate raw cost if it exists, OR
1696 -- set rate quantity to rate revenue otherwise.
1697 -- This is done to handle source planning transactions that
1698 -- have only revenue amounts (without cost amounts).
1699 --
1700 -- Note that source version type is not available in the
1701 -- context of this API. However, the logic should still be ok.
1702
1703 IF l_rate_based_flag = 'N' THEN
1704 IF nvl(l_pc_pfc_rate_raw_cost,0) = 0 THEN
1705 l_pc_pfc_rate_quantity := l_pc_pfc_rate_revenue;
1706 ELSE
1707 l_pc_pfc_rate_quantity := l_pc_pfc_rate_raw_cost;
1708 END IF;
1709 END IF;
1710
1711 -- Bug 5203622: Added OTHER REJECTION CODE logic.
1712 l_other_rej_code := null;
1713 IF l_rate_based_flag = 'N' AND
1714 l_target_version_type = 'ALL' AND
1715 nvl(l_pc_pfc_rate_raw_cost,0) = 0 AND
1716 nvl(l_pc_pfc_rate_revenue,0) <> 0 THEN
1717 l_other_rej_code := 'PA_FP_ETC_REV_FIELD_ERR';
1718 END IF;
1719
1720 IF l_pc_pfc_rate_quantity <> 0 THEN
1721 l_pc_pfc_raw_cost_rate := l_pc_pfc_rate_raw_cost / l_pc_pfc_rate_quantity;
1722 l_pc_pfc_brdn_cost_rate := l_pc_pfc_rate_brdn_cost / l_pc_pfc_rate_quantity;
1723 l_pc_pfc_revenue_rate := l_pc_pfc_rate_revenue / l_pc_pfc_rate_quantity;
1724 ELSE
1725 l_pc_pfc_raw_cost_rate := NULL;
1726 l_pc_pfc_brdn_cost_rate := NULL;
1727 l_pc_pfc_revenue_rate := NULL;
1728 END IF;
1729
1730 -- Bug 5203622: Store OTHER rejection code in the
1731 -- txn_currency_code column of pa_fp_calc_amt_tmp2.
1732 /* Insert the single PC record for total ETC with source rates */
1733 INSERT INTO PA_FP_CALC_AMT_TMP2 (
1734 TARGET_RES_ASG_ID,
1735 ETC_CURRENCY_CODE,
1736 ETC_PLAN_QUANTITY,
1737 ETC_TXN_RAW_COST,
1738 ETC_TXN_BURDENED_COST,
1739 ETC_TXN_REVENUE,
1740 TRANSACTION_SOURCE_CODE,
1741 TXN_CURRENCY_CODE, -- Added for Bug 5203622
1742 RESOURCE_ASSIGNMENT_ID) -- added for bug 5359863
1743 VALUES (
1744 l_tgt_res_asg_id_tab(i),
1745 DECODE(l_currency_flag, 'PC',l_pc_currency_code,
1746 'PFC', l_pfc_currency_code),
1747 l_etc_noncmt_quantity_pc_pfc,
1748 l_etc_noncmt_quantity_pc_pfc * l_pc_pfc_raw_cost_rate,
1749 l_etc_noncmt_quantity_pc_pfc * l_pc_pfc_brdn_cost_rate,
1750 l_etc_noncmt_quantity_pc_pfc * l_pc_pfc_revenue_rate,
1751 'ETC',
1752 l_other_rej_code, -- Added for Bug 5203622
1753 l_src_res_asg_id_tab(i)); -- added for bug 5359863
1754 END IF;
1755 /**************BY THIS TIME, WE HAVE ALL ETC DATA FOR PC or PFC*********/
1756
1757 ELSIF l_currency_flag = 'TC' THEN
1758 /* Get total etc amounts for multiple currencies */
1759 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1760 etc_currency_code,
1761 SUM(NVL(ETC_PLAN_QUANTITY,0))
1762 BULK COLLECT INTO
1763 l_etc_currency_code_tab,
1764 l_etc_quantity_tab
1765 FROM PA_FP_CALC_AMT_TMP2
1766 WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1767 AND TRANSACTION_SOURCE_CODE = 'TOTAL_ETC'
1768 GROUP BY etc_currency_code;
1769
1770 /* Get total non-commitment ETC quantity */
1771 /* Bug 4085203
1772 The total ETC amounts should be summed up irrespective of rate based
1773 or non rate based. Because for non rate based resource, we used the
1774 sum value when ETC and commitment are using same one currency. When
1775 ETC and commitment are using more than one currencies, the flow will
1776 not use the sum amounts.*/
1777 l_etc_quantity_sum := 0;
1778 FOR k IN 1..l_etc_quantity_tab.count LOOP
1779 l_etc_quantity_sum := l_etc_quantity_sum + l_etc_quantity_tab(k);
1780 END LOOP;
1781
1782 /* Get commitment amounts for multiple currencies */
1783 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1784 txn_currency_code,
1785 SUM(NVL(total_plan_quantity,0)),
1786 SUM(NVL(total_txn_raw_cost,0)),
1787 SUM(NVL(total_txn_burdened_cost,0))
1788 BULK COLLECT INTO
1789 l_cmt_currency_code_tab,
1790 l_cmt_quantity_tab,
1791 l_cmt_raw_cost_tab,
1792 l_cmt_brdn_cost_tab
1793 FROM PA_FP_CALC_AMT_TMP2
1794 WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1795 AND transaction_source_code = 'OPEN_COMMITMENTS'
1796 GROUP BY txn_currency_code;
1797
1798 IF l_rate_based_flag = 'N' THEN
1799 l_cmt_quantity_tab := l_cmt_raw_cost_tab;
1800 END IF;
1801
1802 /* Bug 4085203
1803 The total commitment amounts should be summed up irrespective of rate based
1804 or non rate based. Because for non rate based resource, we used the
1805 sum value when ETC and commitment are using same one currency. When
1806 ETC and commitment are using more than one currencies, the flow will
1807 not use the sum amounts.*/
1808 l_cmt_quantity_sum := 0;
1809 FOR k IN 1..l_cmt_quantity_tab.count LOOP
1810 l_cmt_quantity_sum := l_cmt_quantity_sum + l_cmt_quantity_tab(k);
1811 END LOOP;
1812
1813 /* Check the relationship between total ETC currency codes and commitment
1814 currency codes. If commitment currency codes are subset of total ETC
1815 currency codes, then, take currency based approach; otherwise, take
1816 prorating based approach.
1817 'C' means take currency based calculation
1818 'P' means take prorating based calculation */
1819 SELECT COUNT (*) INTO l_currency_count_cmt_min_tot
1820 FROM (
1821 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1822 DISTINCT txn_currency_code
1823 FROM PA_FP_CALC_AMT_TMP2
1824 WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1825 AND transaction_source_code = 'OPEN_COMMITMENTS'
1826 MINUS
1827 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1828 DISTINCT etc_currency_code
1829 FROM PA_FP_CALC_AMT_TMP2
1830 WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1831 AND transaction_source_code = 'TOTAL_ETC'
1832 );
1833
1834 IF l_currency_count_cmt_min_tot = 0 THEN
1835 l_currency_prorate_cmt_flag := 'C';
1836 ELSE
1837 l_currency_prorate_cmt_flag := 'P';
1838 END IF;
1839
1840 /*Bug fix: 4085203: If there only exists one etc currency,
1841 one commitment currency and they are same, no matter it's rate
1842 based resource or non rate based resource, if non_cmt_etc quantity is
1843 calculated as less or equal to zero, then don't generate the non_cmt_ETC.*/
1844 -- Bug 4110695: Replaced the RETURN statement with EXIT so that processing
1845 -- can continue for remaining planning resources. Surrounded body of main loop
1846 -- with a wrapper loop so that EXIT effectively skips this iteration.
1847 IF l_etc_currency_code_tab.count = 1 AND l_cmt_currency_code_tab.count = 1 THEN
1848 l_etc_noncmt_quantity_sum := l_etc_quantity_sum - l_cmt_quantity_sum;
1849 -- ER 5726773: Instead of directly checking if (ETC <= 0), let the
1850 -- plan_etc_signs_match function decide if ETC should be generated.
1851 IF NOT pa_fp_fcst_gen_amt_utils.PLAN_ETC_SIGNS_MATCH
1852 (l_etc_quantity_sum, l_etc_noncmt_quantity_sum) THEN
1853 EXIT;
1854 ELSE
1855 l_etc_noncmt_quantity_tab(1) := l_etc_noncmt_quantity_sum;
1856 END IF;
1857 ELSE
1858 IF l_currency_prorate_cmt_flag = 'C' THEN
1859 FOR m IN 1..l_etc_currency_code_tab.count LOOP
1860 IF l_exit_flag = 'Y' THEN
1861 EXIT;
1862 END IF;
1863 l_etc_noncmt_quantity_tab(m) := l_etc_quantity_tab(m);
1864 FOR n IN 1..l_cmt_currency_code_tab.count LOOP
1865 IF l_etc_currency_code_tab(m) = l_cmt_currency_code_tab(n) THEN
1866 l_etc_noncmt_quantity_tab(m) := l_etc_noncmt_quantity_tab(m)
1867 - l_cmt_quantity_tab(n);
1868 -- ER 5726773: Instead of directly checking if (ETC <= 0), let the
1869 -- plan_etc_signs_match function decide if ETC should be generated.
1870 IF NOT pa_fp_fcst_gen_amt_utils.PLAN_ETC_SIGNS_MATCH
1871 (l_etc_quantity_tab(m), l_etc_noncmt_quantity_tab(m)) THEN
1872 l_currency_prorate_cmt_flag := 'P';
1873 l_etc_noncmt_quantity_tab.delete;
1874 l_exit_flag := 'Y';
1875 EXIT;
1876 END IF;
1877 END IF;
1878 END LOOP;
1879 END LOOP;
1880 END IF;
1881
1882 IF l_currency_prorate_cmt_flag = 'P' THEN
1883 IF l_rate_based_flag = 'N' THEN
1884 l_currency_flag := 'PC_TC';
1885 ELSIF l_rate_based_flag = 'Y' THEN
1886 l_etc_noncmt_quantity_sum := l_etc_quantity_sum - l_cmt_quantity_sum;
1887 -- ER 5726773: Instead of directly checking if (ETC <= 0), let the
1888 -- plan_etc_signs_match function decide if ETC should be generated.
1889 IF NOT pa_fp_fcst_gen_amt_utils.PLAN_ETC_SIGNS_MATCH
1890 (l_etc_quantity_sum, l_etc_noncmt_quantity_sum) THEN
1891 /* no non-commitment ETC available, only actual quantity and commitment
1892 quantity need to be spreaded */
1893 /* We need to exit current loop, and continue with the next loop */
1894 l_continue_loop_flag := 'Y';
1895 ELSE
1896 /* Prorate ETC quantity */
1897 FOR m IN 1..l_etc_currency_code_tab.count LOOP
1898 IF l_etc_quantity_sum <> 0 THEN
1899 l_etc_noncmt_quantity_tab(m) := l_etc_noncmt_quantity_sum
1900 * (l_etc_quantity_tab (m) / l_etc_quantity_sum) ;
1901 ELSE
1902 l_etc_noncmt_quantity_tab(m) := NULL;
1903 END IF;
1904 END LOOP;
1905 END IF;
1906 END IF;
1907 END IF;
1908 END IF;
1909
1910 /*currency_flag may get changed to 'PC_TC', when actual currencies is subset of
1911 planning currencies, target resource is non_rate_based, but actual amount for
1912 one particular currency is less than plan amount. Then we need to revert from
1913 currency based approach to prorating based approach.For non_rate_based resource,
1914 prorating falls in to currency code of 'PC_TC'.*/
1915 IF l_continue_loop_flag <> 'Y' AND l_currency_flag <> 'PC_TC' THEN
1916 /*When not taking periodic rates, we need to calculate out the average rates
1917 from the source resource assignments that are mapped to the current target
1918 resource assignment.*/
1919 FOR k IN 1..l_etc_currency_code_tab.count LOOP
1920 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1921 NVL(SUM(NVL(etc_plan_quantity,0)),0),
1922 NVL(SUM(NVL(etc_txn_raw_cost,0)),0),
1923 NVL(SUM(NVL(etc_txn_burdened_cost,0)),0),
1924 NVL(SUM(NVL(etc_txn_revenue,0)),0)
1925 INTO l_txn_rate_quantity,
1926 l_txn_rate_raw_cost,
1927 l_txn_rate_brdn_cost,
1928 l_txn_rate_revenue
1929 FROM pa_fp_calc_amt_tmp2
1930 WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1931 AND etc_currency_code = l_etc_currency_code_tab(k)
1932 AND transaction_source_code = 'TOTAL_ETC';
1933
1934
1935 -- IPM Change:
1936 -- For non-rate-based target transactions,
1937 -- set rate quantity to rate raw cost if it exists, OR
1938 -- set rate quantity to rate revenue otherwise.
1939 -- This is done to handle source planning transactions that
1940 -- have only revenue amounts (without cost amounts).
1941 --
1942 -- Note that source version type is not available in the
1943 -- context of this API. However, the logic should still be ok.
1944
1945 IF l_rate_based_flag = 'N' THEN
1946 IF nvl(l_txn_rate_raw_cost,0) = 0 THEN
1947 l_txn_rate_quantity := l_txn_rate_revenue;
1948 ELSE
1949 l_txn_rate_quantity := l_txn_rate_raw_cost;
1950 END IF;
1951 END IF;
1952
1953 -- Bug 5203622: Added OTHER REJECTION CODE logic.
1954 l_other_rej_code_tab(k) := null;
1955 IF l_rate_based_flag = 'N' AND
1956 l_target_version_type = 'ALL' AND
1957 nvl(l_txn_rate_raw_cost,0) = 0 AND
1958 nvl(l_txn_rate_revenue,0) <> 0 THEN
1959 l_other_rej_code_tab(k) := 'PA_FP_ETC_REV_FIELD_ERR';
1960 END IF;
1961
1962 IF l_txn_rate_quantity <> 0 THEN
1963 l_txn_raw_cost_rate_tab(k) := l_txn_rate_raw_cost
1964 / l_txn_rate_quantity;
1965 l_txn_brdn_cost_rate_tab(k) := l_txn_rate_brdn_cost
1966 / l_txn_rate_quantity;
1967 l_txn_revenue_rate_tab(k) := l_txn_rate_revenue
1968 / l_txn_rate_quantity;
1969 ELSE
1970 l_txn_raw_cost_rate_tab(k) := NULL;
1971 l_txn_brdn_cost_rate_tab(k) := NULL;
1972 l_txn_revenue_rate_tab(k) := NULL;
1973 END IF;
1974 END LOOP;
1975
1976 -- Bug 5203622: Store OTHER rejection code in the
1977 -- txn_currency_code column of pa_fp_calc_amt_tmp2.
1978 /* Bulk insert for the ETC amounts for current target resource
1979 assignment with source rates */
1980 FORALL k IN 1..l_etc_currency_code_tab.count
1981 INSERT INTO PA_FP_CALC_AMT_TMP2 (
1982 TARGET_RES_ASG_ID,
1983 ETC_CURRENCY_CODE,
1984 ETC_PLAN_QUANTITY,
1985 ETC_TXN_RAW_COST,
1986 ETC_TXN_BURDENED_COST,
1987 ETC_TXN_REVENUE,
1988 TRANSACTION_SOURCE_CODE,
1989 RESOURCE_ASSIGNMENT_ID,
1990 TXN_CURRENCY_CODE ) -- Added for Bug 5203622
1991 VALUES (
1992 l_tgt_res_asg_id_tab(i),
1993 l_etc_currency_code_tab(k),
1994 l_etc_noncmt_quantity_tab(k),
1995 l_etc_noncmt_quantity_tab(k) * l_txn_raw_cost_rate_tab(k),
1996 l_etc_noncmt_quantity_tab(k) * l_txn_brdn_cost_rate_tab(k),
1997 l_etc_noncmt_quantity_tab(k) * l_txn_revenue_rate_tab(k),
1998 'ETC',
1999 l_src_res_asg_id_tab(i),
2000 l_other_rej_code_tab(k) ); -- Added for Bug 5203622
2001 END IF;
2002 END IF;
2003 /**************BY THIS TIME, WE HAVE NON_CMT ETC DATA FOR TC*********/
2004
2005 IF l_currency_flag = 'PC_TC' THEN
2006
2007 /*Take PC for calculation, then convert back to TC */
2008 /*No matter ETC source is 'FINANCIAL_PLAN' or 'WORKPLAN_RESOURCES',
2009 always get total plan amounts in PC from financial data model.*/
2010 l_pc_currency_code := p_fp_cols_tgt_rec.x_project_currency_code;
2011 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
2012 etc_currency_code,
2013 SUM(NVL(etc_plan_quantity,0)),
2014 SUM(NVL(etc_pc_raw_cost,0)),
2015 SUM(NVL(etc_pc_burdened_cost,0)),
2016 SUM(NVL(etc_pc_revenue,0)) -- Added in IPM
2017 BULK COLLECT INTO
2018 l_etc_currency_code_tab,
2019 l_etc_quantity_pc_tab,
2020 l_etc_raw_cost_pc_tab,
2021 l_etc_brdn_cost_pc_tab,
2022 l_etc_revenue_pc_tab -- Added in IPM
2023 FROM PA_FP_CALC_AMT_TMP2
2024 WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
2025 AND transaction_source_code = 'TOTAL_ETC'
2026 GROUP BY etc_currency_code;
2027
2028 -- IPM Change:
2029 -- For non-rate-based target transactions,
2030 -- set target quantity to source raw cost if it exists, OR
2031 -- set target quantity to source revenue otherwise.
2032 -- This is done to handle source planning transactions that
2033 -- have only revenue amounts (without cost amounts).
2034 --
2035 -- Note that source version type is not available in the
2036 -- context of this API. However, the logic should still be ok.
2037
2038 FOR k IN 1..l_etc_quantity_pc_tab.count LOOP
2039 IF nvl(l_etc_raw_cost_pc_tab(k),0) = 0 THEN
2040 l_etc_quantity_pc_tab(k) := l_etc_revenue_pc_tab(k);
2041 ELSE
2042 l_etc_quantity_pc_tab(k) := l_etc_raw_cost_pc_tab(k);
2043 END IF;
2044 END LOOP;
2045
2046 l_etc_quantity_pc_sum := 0;
2047 FOR k IN 1..l_etc_quantity_pc_tab.count LOOP
2048 l_etc_quantity_pc_sum := l_etc_quantity_pc_sum + l_etc_quantity_pc_tab(k);
2049 END LOOP;
2050
2051 /*Get the commitment amounts for the target planning resource in PC.*/
2052 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
2053 txn_currency_code,
2054 SUM(NVL(total_plan_quantity,0)),
2055 SUM(NVL(total_pc_raw_cost,0)),
2056 SUM(NVL(total_pc_burdened_cost,0))
2057 BULK COLLECT INTO
2058 l_cmt_currency_code_tab,
2059 l_cmt_quantity_pc_tab,
2060 l_cmt_raw_cost_pc_tab,
2061 l_cmt_brdn_cost_pc_tab
2062 FROM PA_FP_CALC_AMT_TMP2
2063 WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
2064 AND transaction_source_code = 'OPEN_COMMITMENTS'
2065 GROUP BY txn_currency_code;
2066
2067 l_cmt_quantity_pc_tab := l_cmt_raw_cost_pc_tab;
2068
2069 l_cmt_quantity_pc_sum := 0;
2070 FOR k IN 1..l_cmt_quantity_pc_tab.count LOOP
2071 l_cmt_quantity_pc_sum := l_cmt_quantity_pc_sum + l_cmt_quantity_pc_tab(k);
2072 END LOOP;
2073
2074 /* Get total ETC quantity in PC */
2075 l_etc_noncmt_quantity_pc_sum := l_etc_quantity_pc_sum- l_cmt_quantity_pc_sum;
2076
2077 -- ER 5726773: Instead of directly checking if (ETC <= 0), let the
2078 -- plan_etc_signs_match function decide if ETC should be generated.
2079 IF NOT pa_fp_fcst_gen_amt_utils.PLAN_ETC_SIGNS_MATCH
2080 (l_etc_quantity_pc_sum, l_etc_noncmt_quantity_pc_sum) THEN
2081 /* only need to spread commitment data and actual data */
2082 l_continue_loop_flag := 'Y';
2083 END IF;
2084
2085 IF l_continue_loop_flag <> 'Y' THEN
2086 /*Prorate total non-commitment ETC quantity in PC according to the transaction
2087 currency codes from the total ETC.*/
2088 FOR k IN 1..l_etc_quantity_pc_tab.count LOOP
2089 IF l_etc_quantity_pc_sum <> 0 THEN
2090 l_etc_noncmt_quantity_pc_tab (k) := l_etc_noncmt_quantity_pc_sum
2091 * (l_etc_quantity_pc_tab (k) / l_etc_quantity_pc_sum) ;
2092 ELSE
2093 l_etc_noncmt_quantity_pc_tab (k) := NULL;
2094 END IF;
2095 END LOOP;
2096
2097 /* Convert PC into TC */
2098 FOR k IN 1..l_etc_currency_code_tab.count LOOP
2099 IF l_etc_currency_code_tab(k) = l_pc_currency_code THEN
2100 l_etc_noncmt_quantity_tab(k) := l_etc_noncmt_quantity_pc_tab(k);
2101 ELSE
2102 l_etc_noncmt_quantity_tab(k) := NULL;
2103 BEGIN
2104 SELECT task_id,
2105 planning_start_date
2106 INTO l_task_id,
2107 l_planning_start_date
2108 FROM pa_resource_assignments
2109 WHERE resource_assignment_id = l_tgt_res_asg_id_tab(i);
2110 EXCEPTION
2111 WHEN NO_DATA_FOUND THEN
2112 l_task_id := NULL;
2113 l_planning_start_date := NULL;
2114 END;
2115 IF P_PA_DEBUG_MODE = 'Y' THEN
2116 pa_fp_gen_amount_utils.fp_debug(
2117 p_msg => 'Before calling pa_multi_currency_txn.'||
2118 'get_currency_amounts in remain_bdgt',
2119 p_module_name => l_module_name,
2120 p_log_level => 5);
2121 END IF;
2122 pa_multi_currency_txn.get_currency_amounts (
2123 P_project_id => p_fp_cols_tgt_rec.x_project_id,
2124 P_exp_org_id => NULL,
2125 P_calling_module => 'WORKPLAN',
2126 P_task_id => l_task_id,
2127 P_EI_date => l_planning_start_date,
2128 P_denom_raw_cost => l_etc_noncmt_quantity_pc_tab(k),
2129 P_denom_curr_code => l_pc_currency_code,
2130
2131 P_acct_curr_code => l_pc_currency_code,
2132 P_accounted_flag => 'N',
2133 P_acct_rate_date => lx_acc_rate_date,
2134 P_acct_rate_type => lx_acct_rate_type,
2135 P_acct_exch_rate => lx_acct_exch_rate,
2136 P_acct_raw_cost => lx_acct_raw_cost,
2137
2138 P_project_curr_code => l_etc_currency_code_tab(k),
2139 P_project_rate_type => lx_project_rate_type,
2140 P_project_rate_date => lx_project_rate_date,
2141 P_project_exch_rate => lx_project_exch_rate,
2142 P_project_raw_cost => l_etc_noncmt_quantity_tab(k),
2143
2144 P_projfunc_curr_code=> l_pc_currency_code,
2145 P_projfunc_cost_rate_type => lx_projfunc_cost_rate_type,
2146 P_projfunc_cost_rate_date => lx_projfunc_cost_rate_date,
2147 P_projfunc_cost_exch_rate => lx_projfunc_cost_exch_rate,
2148 P_projfunc_raw_cost => l_projfunc_raw_cost,
2149
2150 P_system_linkage => 'NER',
2151 P_status => x_return_status,
2152 P_stage => x_msg_count) ;
2153 IF P_PA_DEBUG_MODE = 'Y' THEN
2154 pa_fp_gen_amount_utils.fp_debug(
2155 p_msg => 'After calling pa_multi_currency_txn.'||
2156 'get_currency_amounts in remain_bdgt:'||x_return_status,
2157 p_module_name => l_module_name,
2158 p_log_level => 5);
2159 END IF;
2160 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2161 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2162 END IF;
2163 END IF;
2164
2165 END LOOP;
2166
2167 /*When not taking periodic rates, we need to calculate out the average
2168 rates from the source resource assignments that are mapped to the
2169 current target resource assignment.*/
2170 FOR k IN 1..l_etc_noncmt_quantity_tab.count LOOP
2171 SELECT /*+ INDEX(pa_fp_calc_amt_tmp2,PA_FP_CALC_AMT_TMP2_N1)*/
2172 NVL(SUM(NVL(etc_plan_quantity,0)),0),
2173 NVL(SUM(NVL(etc_txn_raw_cost,0)),0),
2174 NVL(SUM(NVL(etc_txn_burdened_cost,0)),0),
2175 NVL(SUM(NVL(etc_txn_revenue,0)),0)
2176 INTO l_txn_rate_quantity,
2177 l_txn_rate_raw_cost,
2178 l_txn_rate_brdn_cost,
2179 l_txn_rate_revenue
2180 FROM pa_fp_calc_amt_tmp2
2181 WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
2182 AND etc_currency_code = l_etc_currency_code_tab(k)
2183 AND transaction_source_code = 'TOTAL_ETC';
2184
2185 -- IPM Change:
2186 -- For non-rate-based target transactions,
2187 -- set target quantity to source raw cost if it exists, OR
2188 -- set target quantity to source revenue otherwise.
2189 -- This is done to handle source planning transactions that
2190 -- have only revenue amounts (without cost amounts).
2191 --
2192 -- Note that source version type is not available in the
2193 -- context of this API. However, the logic should still be ok.
2194
2195 IF nvl(l_txn_rate_raw_cost,0) = 0 THEN
2196 l_txn_rate_quantity := l_txn_rate_revenue;
2197 ELSE
2198 l_txn_rate_quantity := l_txn_rate_raw_cost;
2199 END IF;
2200
2201 -- Bug 5203622: Added OTHER REJECTION CODE logic.
2202 l_other_rej_code_tab(k) := null;
2203 IF l_rate_based_flag = 'N' AND
2204 l_target_version_type = 'ALL' AND
2205 nvl(l_txn_rate_raw_cost,0) = 0 AND
2206 nvl(l_txn_rate_revenue,0) <> 0 THEN
2207 l_other_rej_code_tab(k) := 'PA_FP_ETC_REV_FIELD_ERR';
2208 END IF;
2209
2210 IF l_txn_rate_quantity <> 0 THEN
2211
2212 l_txn_raw_cost_rate_tab(k) := l_txn_rate_raw_cost
2213 / l_txn_rate_quantity; -- Added in IPM
2214 l_txn_brdn_cost_rate_tab(k) := l_txn_rate_brdn_cost
2215 / l_txn_rate_quantity;
2216 l_txn_revenue_rate_tab(k) := l_txn_rate_revenue
2217 / l_txn_rate_quantity;
2218 ELSE
2219 l_txn_raw_cost_rate_tab(k) := NULL; -- Added in IPM
2220 l_txn_brdn_cost_rate_tab(k) := NULL;
2221 l_txn_revenue_rate_tab(k) := NULL;
2222 END IF;
2223 END LOOP;
2224
2225 -- Bug 5203622: Store OTHER rejection code in the
2226 -- txn_currency_code column of pa_fp_calc_amt_tmp2.
2227 /* Bulk insert */
2228 FORALL k IN 1..l_etc_noncmt_quantity_tab.count
2229 INSERT INTO PA_FP_CALC_AMT_TMP2 (
2230 TARGET_RES_ASG_ID,
2231 ETC_CURRENCY_CODE,
2232 ETC_PLAN_QUANTITY,
2233 ETC_TXN_RAW_COST,
2234 ETC_TXN_BURDENED_COST,
2235 ETC_TXN_REVENUE,
2236 TRANSACTION_SOURCE_CODE,
2237 RESOURCE_ASSIGNMENT_ID,
2238 TXN_CURRENCY_CODE ) -- Added for Bug 5203622
2239 VALUES (
2240 l_tgt_res_asg_id_tab(i),
2241 l_etc_currency_code_tab(k),
2242 l_etc_noncmt_quantity_tab(k),
2243 l_etc_noncmt_quantity_tab(k) * l_txn_raw_cost_rate_tab(k),
2244 l_etc_noncmt_quantity_tab(k) * l_txn_brdn_cost_rate_tab(k),
2245 l_etc_noncmt_quantity_tab(k) * l_txn_revenue_rate_tab(k),
2246 'ETC',
2247 l_src_res_asg_id_tab(i),
2248 l_other_rej_code_tab(k) ); -- Added for Bug 5203622
2249 END IF;
2250 /**************NOW WE HAVE ALL ETC DATA IN PC_TC*************/
2251
2252 END IF; /* End the check for PC, TC and PC_TC */
2253 END IF;
2254 END LOOP; --wrapper loop for Bug 4110695
2255 END LOOP;
2256
2257 IF P_PA_DEBUG_MODE = 'Y' THEN
2258 PA_DEBUG.RESET_CURR_FUNCTION;
2259 END IF;
2260 EXCEPTION
2261 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2262 l_msg_count := FND_MSG_PUB.count_msg;
2263 IF l_msg_count = 1 THEN
2264 PA_INTERFACE_UTILS_PUB.get_messages
2265 ( p_encoded => FND_API.G_TRUE,
2266 p_msg_index => 1,
2267 p_msg_count => l_msg_count,
2268 p_msg_data => l_msg_data,
2269 p_data => l_data,
2270 p_msg_index_out => l_msg_index_out);
2271 x_msg_data := l_data;
2272 x_msg_count := l_msg_count;
2273 ELSE
2274 x_msg_count := l_msg_count;
2275 END IF;
2276
2277 ROLLBACK;
2278 x_return_status := FND_API.G_RET_STS_ERROR;
2279
2280 IF P_PA_DEBUG_MODE = 'Y' THEN
2281 pa_fp_gen_amount_utils.fp_debug
2282 (p_msg => 'Invalid Arguments Passed',
2283 p_module_name => l_module_name,
2284 p_log_level => 5);
2285 PA_DEBUG.RESET_CURR_FUNCTION;
2286 END IF;
2287 RAISE;
2288 WHEN OTHERS THEN
2289 rollback;
2290 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2291 x_msg_count := 1;
2292 x_msg_data := substr(sqlerrm,1,240);
2293 --dbms_output.put_line('error msg :'||x_msg_data);
2294 FND_MSG_PUB.add_exc_msg
2295 ( p_pkg_name => 'PA_FP_GEN_FCST_AMT_PUB3',
2296 p_procedure_name => 'GEN_ETC_COMMITMENT_AMTS',
2297 p_error_text => substr(sqlerrm,1,240));
2298
2299 IF P_PA_DEBUG_MODE = 'Y' THEN
2300 pa_fp_gen_amount_utils.fp_debug
2301 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
2302 p_module_name => l_module_name,
2303 p_log_level => 5);
2304 PA_DEBUG.RESET_CURR_FUNCTION;
2305 END IF;
2306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2307 END GET_ETC_COMMITMENT_AMTS;
2308
2309
2310 /* Assumption:
2311 *1.Before getting into this procedure, we have all total plan amounts and commitment
2312 amounts populated in temporary table PA_FP_CALC_AMT_TMP2 table with transaction
2313 source codes of 'WORKPLAN'/'FINPLAN' or 'OPEN_COMMITMENTS'.
2314 2.Rate based flag for target resource assignment gets updated correctly before coming
2315 into any of ETC methods.
2316 3.All considered scenarios:
2317 Rate_based
2318 non multi currency enabled: use PC
2319 multi currency enabled
2320 actuals currency is subset of total currency: use TC, currency based substraction
2321 actuals currency is not subset of total currency: use TC, prorate ETC quantity
2322 Non_rate_based
2323 non multi currency enabled: use PC
2324 multi currency enabled
2325 actuals currency not subset of total currency: use TC, currency based substraction
2326 actuals currency not subset of total currency: Compute ETC quantity in PC, prorate
2327 this ETC quantity to different planning currencies based on PC amounts,
2328 convert back from PC to TC.
2329 */
2330 PROCEDURE GET_ETC_REMAIN_BDGT_AMTS_BLK
2331 (P_SRC_RES_ASG_ID_TAB IN PA_PLSQL_DATATYPES.IdTabTyp,
2332 P_TGT_RES_ASG_ID_TAB IN PA_PLSQL_DATATYPES.IdTabTyp,
2333 P_FP_COLS_SRC_REC_FP IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
2334 P_FP_COLS_SRC_REC_WP IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
2335 P_FP_COLS_TGT_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
2336 P_TASK_ID_TAB IN PA_PLSQL_DATATYPES.IdTabTyp,
2337 P_RES_LIST_MEMBER_ID_TAB IN PA_PLSQL_DATATYPES.IdTabTyp,
2338 P_ETC_SOURCE_CODE_TAB IN PA_PLSQL_DATATYPES.Char30TabTyp,
2339 P_WP_STRUCTURE_VERSION_ID IN PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE,
2340 P_ACTUALS_THRU_DATE IN PA_PERIODS_ALL.END_DATE%TYPE,
2341 P_PLANNING_OPTIONS_FLAG IN VARCHAR2,
2342 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2343 X_MSG_COUNT OUT NOCOPY NUMBER,
2344 X_MSG_DATA OUT NOCOPY VARCHAR2)
2345 IS
2346 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_FCST_AMT_PUB3.GEN_ETC_REMAIN_BDGT_AMTS_BLK';
2347
2348 l_currency_flag VARCHAR2(30);
2349 l_rate_based_flag VARCHAR2(1);
2350 l_currency_count_for_flag NUMBER;
2351 l_prorating_always_flag VARCHAR2(1); -- currently unused
2352 l_target_version_type pa_budget_versions.version_type%type;
2353 l_source_version_type pa_budget_versions.version_type%type; /* Added for IPM */
2354
2355 /* For PC amounts */
2356 l_pc_currency_code pa_projects_all.project_currency_code%type;
2357 l_tot_quantity_pc_pfc NUMBER;
2358 l_tot_raw_cost_pc_pfc NUMBER;
2359 l_tot_brdn_cost_pc_pfc NUMBER;
2360 l_tot_revenue_pc_pfc NUMBER;
2361
2362 l_act_quantity_pc_pfc NUMBER;
2363
2364 /*For workplan actuals*/
2365 lx_act_quantity NUMBER;
2366 lx_act_txn_currency_code VARCHAR2(30);
2367 lx_act_txn_raw_cost NUMBER;
2368 lx_act_txn_brdn_cost NUMBER;
2369 lx_act_pc_raw_cost NUMBER;
2370 lx_act_pc_brdn_cost NUMBER;
2371 lx_act_pfc_raw_cost NUMBER;
2372 lx_act_pfc_brdn_cost NUMBER;
2373
2374 l_etc_quantity_pc_pfc NUMBER;
2375
2376 /* For TC amounts */
2377 l_tot_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2378 l_tot_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
2379 l_tot_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2380 l_tot_brdn_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2381 l_tot_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2382 l_tot_quantity_sum NUMBER;
2383
2384 l_act_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2385 l_act_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
2386 l_act_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2387 l_act_brdn_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2388 l_act_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2389 l_act_quantity_sum NUMBER;
2390
2391 /* ForPFC amounts */
2392 l_pfc_currency_code pa_projects_all.project_currency_code%type;
2393 l_rev_gen_method VARCHAR2(3);
2394
2395
2396 /* For ETC amounts */
2397 l_etc_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
2398 l_etc_quantity_sum NUMBER;
2399
2400 l_currency_count_act_min_tot NUMBER;
2401 l_currency_prorate_act_flag VARCHAR2(1);
2402 l_exit_flag VARCHAR2(1) := 'N';
2403
2404 /*For PC_TC amounts*/
2405 l_tot_quantity_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
2406 l_tot_raw_cost_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
2407 l_tot_brdn_cost_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
2408 l_tot_revenue_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
2409 l_tot_quantity_pc_sum NUMBER;
2410 l_act_quantity_pc_sum NUMBER;
2411 l_etc_quantity_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
2412 l_etc_quantity_pc_sum NUMBER;
2413
2414 /*For average rates*/
2415 l_pc_pfc_rate_quantity NUMBER;
2416 l_pc_pfc_rate_raw_cost NUMBER;
2417 l_pc_pfc_rate_brdn_cost NUMBER;
2418 l_pc_pfc_rate_revenue NUMBER;
2419
2420 l_pc_rate_quantity NUMBER; -- currently not used
2421 l_pc_rate_raw_cost NUMBER;
2422 l_pc_rate_brdn_cost NUMBER;
2423 l_pc_rate_revenue NUMBER;
2424
2425 l_txn_rate_quantity NUMBER;
2426 l_txn_rate_raw_cost NUMBER;
2427 l_txn_rate_brdn_cost NUMBER;
2428 l_txn_rate_revenue NUMBER;
2429
2430 l_pc_pfc_raw_cost_rate NUMBER;
2431 l_pc_pfc_brdn_cost_rate NUMBER;
2432 l_pc_pfc_revenue_rate NUMBER;
2433
2434 l_txn_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2435 l_txn_brdn_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2436 l_txn_revenue_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2437 l_pc_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2438 l_pc_brdn_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2439 l_pc_revenue_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2440 l_transaction_source_code VARCHAR2(30);
2441
2442 /*For txn currency conversion*/
2443 l_task_id pa_tasks.task_id%type;
2444 l_planning_start_date pa_resource_assignments.planning_start_date%type;
2445 lx_acc_rate_date DATE;
2446 lx_acct_rate_type VARCHAR2(50);
2447 lx_acct_exch_rate NUMBER;
2448 lx_acct_raw_cost NUMBER;
2449 lx_project_rate_type VARCHAR2(50);
2450 lx_project_rate_date DATE;
2451 lx_project_exch_rate NUMBER;
2452 lx_projfunc_cost_rate_type VARCHAR2(50);
2453 lx_projfunc_cost_rate_date DATE;
2454 lx_projfunc_cost_exch_rate NUMBER;
2455 l_projfunc_raw_cost NUMBER;
2456
2457 /* Status variable for GET_CURRENCY_AMOUNTS api */
2458 l_status Varchar2(100);
2459 g_project_name pa_projects_all.name%TYPE;
2460
2461 /* Variables for Performance Bug 4194849 */
2462 l_src_res_asg_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE;
2463 l_tgt_res_asg_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE;
2464 l_fp_cols_src_rec PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
2465 l_curr_task_id PA_TASKS.TASK_ID%TYPE;
2466 l_resource_list_member_id PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_MEMBER_ID%TYPE;
2467 l_etc_source_code PA_TASKS.GEN_ETC_SOURCE_CODE%TYPE;
2468
2469 /* This user-defined exception is used to skip processing of
2470 * a single task as we process all of the tasks in a loop. */
2471 continue_loop EXCEPTION;
2472 l_dummy NUMBER;
2473
2474 l_ins_index BINARY_INTEGER;
2475 l_ins_src_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2476 l_ins_tgt_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2477 l_ins_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2478 l_ins_etc_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
2479 l_ins_txn_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2480 l_ins_txn_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2481 l_ins_txn_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2482 l_ins_pc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2483 l_ins_pc_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2484 l_ins_pc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2485 l_ins_pfc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2486 l_ins_pfc_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2487 l_ins_pfc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2488
2489 l_msg_count NUMBER;
2490 l_msg_data VARCHAR2(2000);
2491 l_data VARCHAR2(2000);
2492 l_msg_index_out NUMBER:=0;
2493
2494 -- Variables added for Bug 5203622
2495 l_act_raw_cost_pc_pfc NUMBER;
2496 l_act_raw_cost_sum NUMBER;
2497 l_act_raw_cost_pc_sum NUMBER;
2498 l_tot_raw_cost_sum NUMBER;
2499 l_tot_revenue_sum NUMBER;
2500 l_tot_raw_cost_pc_sum NUMBER;
2501 l_tot_revenue_pc_sum NUMBER;
2502 l_other_rej_code PA_BUDGET_LINES.OTHER_REJECTION_CODE%TYPE;
2503 l_other_rej_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2504 l_ins_other_rej_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2505
2506 BEGIN
2507 IF p_pa_debug_mode = 'Y' THEN
2508 pa_debug.set_curr_function( p_function => 'GEN_ETC_REMAIN_BDGT_AMTS_BLK',
2509 p_debug_mode => p_pa_debug_mode);
2510 END IF;
2511
2512 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2513 X_MSG_COUNT := 0;
2514
2515 FOR main_loop IN 1..p_src_res_asg_id_tab.count LOOP
2516 BEGIN
2517
2518 /* Initialize Local Variables for Bug 4194849 */
2519 l_src_res_asg_id := p_src_res_asg_id_tab(main_loop);
2520 l_tgt_res_asg_id := p_tgt_res_asg_id_tab(main_loop);
2521 l_curr_task_id := p_task_id_tab(main_loop);
2522 l_resource_list_member_id := p_res_list_member_id_tab(main_loop);
2523 l_etc_source_code := p_etc_source_code_tab(main_loop);
2524
2525 IF l_etc_source_code = 'FINANCIAL_PLAN' THEN
2526 l_fp_cols_src_rec := p_fp_cols_src_rec_fp;
2527 ELSIF l_etc_source_code = 'WORKPLAN_RESOURCES' THEN
2528 l_fp_cols_src_rec := p_fp_cols_src_rec_wp;
2529 END IF;
2530
2531 /* Delete pl/sql tables for the current task being processed. */
2532 l_tot_currency_code_tab.delete;
2533 l_tot_quantity_tab.delete;
2534 l_tot_raw_cost_tab.delete;
2535 l_tot_brdn_cost_tab.delete;
2536 l_tot_revenue_tab.delete;
2537
2538 l_act_currency_code_tab.delete;
2539 l_act_quantity_tab.delete;
2540 l_act_raw_cost_tab.delete;
2541 l_act_brdn_cost_tab.delete;
2542 l_act_revenue_tab.delete;
2543
2544 l_tot_quantity_pc_tab.delete;
2545 l_tot_raw_cost_pc_tab.delete;
2546 l_tot_brdn_cost_pc_tab.delete;
2547 l_tot_revenue_pc_tab.delete;
2548 l_etc_quantity_pc_tab.delete;
2549
2550 l_txn_raw_cost_rate_tab.delete;
2551 l_txn_brdn_cost_rate_tab.delete;
2552 l_txn_revenue_rate_tab.delete;
2553 l_pc_raw_cost_rate_tab.delete;
2554 l_pc_brdn_cost_rate_tab.delete;
2555 l_pc_revenue_rate_tab.delete;
2556
2557 -- Bug 4231106: Before populating l_etc_quantity_tab, delete existing records
2558 l_etc_quantity_tab.delete;
2559
2560 /*Currency usage should be determined at the beginning.
2561 Default to use Transaction Currency (TC)
2562 If target version is not multi currency enabled, take Project Currency (PC)
2563 IF target version is multi currency enabled, the target planning resource is non
2564 rate based, and actuals currencies are not subset of the total currencies. We need
2565 to take PC amounts as quantity, sum up total quantity minus actual quantity,
2566 prorate this total PC ETC quantity across the planning currencies. Then convert
2567 them back from PC to TC (PC_TC).*/
2568
2569 IF nvl(l_tgt_res_asg_id,0) > 0 THEN
2570 SELECT rate_based_flag
2571 INTO l_rate_based_flag
2572 FROM pa_resource_assignments
2573 WHERE resource_assignment_id = l_tgt_res_asg_id;
2574 ELSE
2575 l_rate_based_flag:='N';
2576 END IF;
2577
2578 l_currency_flag := 'TC';
2579 l_rev_gen_method := nvl(P_FP_COLS_TGT_REC.x_revenue_derivation_method,PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(P_FP_COLS_TGT_REC.x_project_id)); -- Bug 5462471
2580 --l_rev_gen_method := PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(P_FP_COLS_TGT_REC.x_project_id);
2581
2582 IF (p_fp_cols_tgt_rec.x_version_type = 'REVENUE' and l_rev_gen_method = 'C') THEN
2583 l_currency_flag := 'PFC';
2584 ELSIF p_fp_cols_tgt_rec.X_PLAN_IN_MULTI_CURR_FLAG = 'N' THEN
2585 l_currency_flag := 'PC';
2586 ELSIF l_rate_based_flag = 'N' THEN
2587 SELECT COUNT(*) INTO l_currency_count_for_flag FROM (
2588 SELECT /*+ INDEX(act_tmp,PA_FP_FCST_GEN_TMP1_N1) INDEX(tot_tmp,PA_FP_CALC_AMT_TMP1_N1)*/
2589 DISTINCT act_tmp.txn_currency_code
2590 FROM PA_FP_FCST_GEN_TMP1 act_tmp,
2591 PA_FP_CALC_AMT_TMP1 tot_tmp
2592 WHERE act_tmp.project_element_id = tot_tmp.task_id
2593 AND act_tmp.res_list_member_id = tot_tmp.resource_list_member_id
2594 AND tot_tmp.target_res_asg_id = l_tgt_res_asg_id
2595 AND data_type_code = DECODE(L_ETC_SOURCE_CODE,
2596 'WORKPLAN_RESOURCES', 'ETC_WP',
2597 'FINANCIAL_PLAN', 'ETC_FP')
2598 MINUS
2599 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
2600 DISTINCT txn_currency_code
2601 FROM PA_FP_CALC_AMT_TMP2
2602 WHERE target_res_asg_id = l_tgt_res_asg_id
2603 AND transaction_source_code = l_etc_source_code
2604 ) WHERE rownum = 1;
2605
2606 IF l_currency_count_for_flag > 0 THEN
2607 l_currency_flag := 'PC_TC';
2608 END IF;
2609 END IF;
2610
2611 /**************BY THIS TIME, WE DECIDED TO USE EITHER PC,TC,PC_TC or PFC**********/
2612
2613 -- Get Source version tpe
2614 IF l_etc_source_code = 'FINANCIAL_PLAN' THEN
2615 l_source_version_type := p_fp_cols_src_rec_fp.x_version_type;
2616 ELSE -- l_etc_source_code = 'WORKPLAN_RESOURCES'
2617 l_source_version_type := p_fp_cols_src_rec_wp.x_version_type;
2618 END IF;
2619
2620 l_target_version_type := p_fp_cols_tgt_rec.x_version_type;
2621 l_pc_currency_code := p_fp_cols_tgt_rec.x_project_currency_code;
2622 l_pfc_currency_code := p_fp_cols_tgt_rec.x_projfunc_currency_code;
2623 IF l_currency_flag = 'PC' OR l_currency_flag = 'PFC' THEN
2624 /* No matter ETC source is 'FINANCIAL_PLAN' or 'WORKPLAN_RESOURCES', always get
2625 total plan amounts in PC or PFC from financial data model.*/
2626 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
2627 NVL(SUM(NVL(total_plan_quantity,0)),0),
2628 NVL(SUM(NVL(
2629 DECODE(l_currency_flag, 'PC', total_pc_raw_cost,
2630 'PFC', total_pfc_raw_cost),0)),0),
2631 NVL(SUM(NVL(
2632 DECODE(l_currency_flag, 'PC', total_pc_burdened_cost,
2633 'PFC', total_pfc_burdened_cost),0)),0),
2634 NVL(SUM(NVL(
2635 DECODE(l_currency_flag, 'PC', total_pc_revenue,
2636 'PFC', total_pfc_revenue),0)),0)
2637 INTO l_tot_quantity_pc_pfc,
2638 l_tot_raw_cost_pc_pfc,
2639 l_tot_brdn_cost_pc_pfc,
2640 l_tot_revenue_pc_pfc
2641 FROM PA_FP_CALC_AMT_TMP2
2642 WHERE resource_assignment_id = l_src_res_asg_id
2643 AND transaction_source_code = l_etc_source_code;
2644
2645 -- IPM Change:
2646 -- For non-rate-based target transactions,
2647 -- if the Source is a Cost and Revenue together version,
2648 -- then regardless of the Target version type:
2649 -- set target quantity to source raw cost if it exists, OR
2650 -- set target quantity to source revenue otherwise.
2651 -- This is done to handle source planning transactions that
2652 -- have only revenue amounts (without cost amounts).
2653 --
2654 -- For non-rate-based target transactions and other Source
2655 -- version types, set target quantity to source raw cost as before.
2656
2657 IF l_rate_based_flag = 'N' THEN
2658 IF l_source_version_type = 'ALL' THEN
2659 IF nvl(l_tot_raw_cost_pc_pfc,0) = 0 THEN
2660 l_tot_quantity_pc_pfc := l_tot_revenue_pc_pfc;
2661 ELSE
2662 l_tot_quantity_pc_pfc := l_tot_raw_cost_pc_pfc;
2663 END IF;
2664 ELSE
2665 l_tot_quantity_pc_pfc := l_tot_raw_cost_pc_pfc;
2666 END IF;
2667 END IF;
2668
2669 IF l_etc_source_code = 'FINANCIAL_PLAN' THEN
2670 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
2671 DECODE(l_currency_flag,
2672 'PC', NVL(SUM(DECODE(l_rate_based_flag,
2673 'Y', quantity,
2674 'N', NVL(prj_raw_cost,0))),0),
2675 'PFC', NVL(SUM(DECODE(l_rate_based_flag,
2676 'Y', quantity,
2677 'N', NVL(pou_raw_cost,0))),0)),
2678 DECODE(l_currency_flag, -- Added for Bug 5203622
2679 'PC', NVL(SUM(NVL(prj_raw_cost,0)),0),
2680 'PFC', NVL(SUM(NVL(pou_raw_cost,0)),0))
2681 INTO l_act_quantity_pc_pfc,
2682 l_act_raw_cost_pc_pfc -- Added for Bug 5203622
2683 FROM PA_FP_FCST_GEN_TMP1
2684 WHERE project_element_id = l_curr_task_id
2685 AND res_list_member_id = l_resource_list_member_id
2686 AND data_type_code = 'ETC_FP';
2687
2688 ELSIF l_etc_source_code = 'WORKPLAN_RESOURCES' THEN
2689 /*Bug fix for 3973511
2690 Workplan side only stores amounts in one currency for each planning
2691 resource. Instead of relying on pa_progress_utils.get_actuals_for_task
2692 to get actuals data, we query directly to pa_budget_lines to get actual
2693 data from source workplan budget version */
2694 IF P_PA_DEBUG_MODE = 'Y' THEN
2695 pa_fp_gen_amount_utils.fp_debug(
2696 p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
2697 'GET_WP_ACTUALS_FOR_RA',
2698 p_module_name => l_module_name,
2699 p_log_level => 5);
2700 END IF;
2701 PA_FP_GEN_FCST_AMT_PUB1.GET_WP_ACTUALS_FOR_RA
2702 (P_FP_COLS_SRC_REC => l_fp_cols_src_rec,
2703 P_FP_COLS_TGT_REC => p_fp_cols_tgt_rec,
2704 P_SRC_RES_ASG_ID => l_src_res_asg_id,
2705 P_TASK_ID => l_curr_task_id,
2706 P_RES_LIST_MEM_ID => l_resource_list_member_id,
2707 P_ACTUALS_THRU_DATE => p_actuals_thru_date,
2708 X_ACT_QUANTITY => lx_act_quantity,
2709 X_ACT_TXN_CURRENCY_CODE => lx_act_txn_currency_code,
2710 X_ACT_TXN_RAW_COST => lx_act_txn_raw_cost,
2711 X_ACT_TXN_BRDN_COST => lx_act_txn_brdn_cost,
2712 X_ACT_PC_RAW_COST => lx_act_pc_raw_cost,
2713 X_ACT_PC_BRDN_COST => lx_act_pc_brdn_cost,
2714 X_ACT_PFC_RAW_COST => lx_act_pfc_raw_cost,
2715 X_ACT_PFC_BRDN_COST => lx_act_pfc_brdn_cost,
2716 X_RETURN_STATUS => x_return_status,
2717 X_MSG_COUNT => x_msg_count,
2718 X_MSG_DATA => x_msg_data );
2719 IF P_PA_DEBUG_MODE = 'Y' THEN
2720 pa_fp_gen_amount_utils.fp_debug(
2721 p_msg => 'After calling PA_FP_GEN_FCST_AMT_PUB1.'||
2722 'GET_WP_ACTUALS_FOR_RA in remain_bdgt:'||x_return_status,
2723 p_module_name => l_module_name,
2724 p_log_level => 5);
2725 END IF;
2726 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2727 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2728 END IF;
2729
2730 IF l_rate_based_flag = 'Y' THEN
2731 l_act_quantity_pc_pfc := lx_act_quantity;
2732 l_act_raw_cost_pc_pfc := lx_act_txn_raw_cost; -- Added for Bug 5203622
2733 ELSE
2734 IF l_currency_flag = 'PC' THEN
2735 l_act_quantity_pc_pfc := lx_act_pc_raw_cost;
2736 l_act_raw_cost_pc_pfc := lx_act_pc_raw_cost; -- Added for Bug 5203622
2737 ELSIF l_currency_flag = 'PFC' THEN
2738 l_act_quantity_pc_pfc := lx_act_pfc_raw_cost;
2739 l_act_raw_cost_pc_pfc := lx_act_pfc_raw_cost; -- Added for Bug 5203622
2740 END IF;
2741 END IF;
2742 END IF;
2743
2744 /* Get total ETC quantity */
2745 l_etc_quantity_pc_pfc := l_tot_quantity_pc_pfc - l_act_quantity_pc_pfc;
2746 -- ER 5726773: Instead of directly checking if (ETC <= 0), let the
2747 -- plan_etc_signs_match function decide if ETC should be generated.
2748 IF NOT pa_fp_fcst_gen_amt_utils.PLAN_ETC_SIGNS_MATCH
2749 (l_tot_quantity_pc_pfc, l_etc_quantity_pc_pfc) THEN
2750 /* only need to spread commitment and actual data*/
2751 RAISE continue_loop;
2752 END IF;
2753
2754 -- Bug 5203622: Added OTHER REJECTION CODE logic.
2755 l_other_rej_code := null;
2756 IF l_rate_based_flag = 'N' AND
2757 l_source_version_type = 'ALL' AND
2758 l_target_version_type = 'ALL' AND
2759 nvl(l_tot_raw_cost_pc_pfc,0) = 0 AND
2760 nvl(l_tot_revenue_pc_pfc,0) <> 0 AND
2761 nvl(l_act_raw_cost_pc_pfc,0) <> 0 THEN
2762 l_other_rej_code := 'PA_FP_ETC_REV_FIELD_ERR';
2763 END IF;
2764
2765 /* hr_utility.trace('project currency:'||l_ppc_currency_code);
2766 hr_utility.trace('etc qty '||l_etc_quantity_pc );*/
2767
2768 /*When not taking periodic rates, we need to calculate out the average
2769 rates from the source resource assignments that are mapped to the current
2770 target resource assignmentInsert the single PC record for total ETC.*/
2771 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
2772 NVL(SUM(NVL(total_plan_quantity,0)),0),
2773 DECODE(l_currency_flag,
2774 'PC', NVL(SUM(NVL(total_pc_raw_cost,0)),0),
2775 'PFC', NVL(SUM(NVL(total_pfc_raw_cost,0)),0)),
2776 DECODE(l_currency_flag,
2777 'PC', NVL(SUM(NVL(total_pc_burdened_cost,0)),0),
2778 'PFC', NVL(SUM(NVL(total_pfc_burdened_cost,0)),0)),
2779 DECODE(l_currency_flag,
2780 'PC', NVL(SUM(NVL(total_pc_revenue,0)),0),
2781 'PFC', NVL(SUM(NVL(total_pfc_revenue,0)),0))
2782 INTO l_pc_pfc_rate_quantity,
2783 l_pc_pfc_rate_raw_cost,
2784 l_pc_pfc_rate_brdn_cost,
2785 l_pc_pfc_rate_revenue
2786 FROM pa_fp_calc_amt_tmp2
2787 WHERE resource_assignment_id = l_src_res_asg_id
2788 AND transaction_source_code in ('FINANCIAL_PLAN',
2789 'WORKPLAN_RESOURCES');
2790
2791 -- IPM Change:
2792 -- For non-rate-based target transactions,
2793 -- if the Source is a Cost and Revenue together version,
2794 -- then regardless of the Target version type:
2795 -- set rate quantity to rate raw cost if it exists, OR
2796 -- set rate quantity to rate revenue otherwise.
2797 -- This is done to handle source planning transactions that
2798 -- have only revenue amounts (without cost amounts).
2799 --
2800 -- For non-rate-based target transactions and other Source
2801 -- version types, set rate quantity to rate raw cost as before.
2802
2803 IF l_rate_based_flag = 'N' THEN
2804 IF l_source_version_type = 'ALL' THEN
2805 IF nvl(l_pc_pfc_rate_raw_cost,0) = 0 THEN
2806 l_pc_pfc_rate_quantity := l_pc_pfc_rate_revenue;
2807 ELSE
2808 l_pc_pfc_rate_quantity := l_pc_pfc_rate_raw_cost;
2809 END IF;
2810 ELSE
2811 l_pc_pfc_rate_quantity := l_pc_pfc_rate_raw_cost;
2812 END IF;
2813 END IF;
2814
2815 IF l_pc_pfc_rate_quantity <> 0 THEN
2816 l_pc_pfc_raw_cost_rate := l_pc_pfc_rate_raw_cost / l_pc_pfc_rate_quantity;
2817 l_pc_pfc_brdn_cost_rate := l_pc_pfc_rate_brdn_cost / l_pc_pfc_rate_quantity;
2818 l_pc_pfc_revenue_rate := l_pc_pfc_rate_revenue / l_pc_pfc_rate_quantity;
2819 ELSE
2820 l_pc_pfc_raw_cost_rate := NULL;
2821 l_pc_pfc_brdn_cost_rate := NULL;
2822 l_pc_pfc_revenue_rate := NULL;
2823 END IF;
2824
2825 l_ins_index := l_ins_src_res_asg_id_tab.count + 1;
2826 l_ins_src_res_asg_id_tab(l_ins_index) := l_src_res_asg_id;
2827 l_ins_tgt_res_asg_id_tab(l_ins_index) := l_tgt_res_asg_id;
2828 l_ins_etc_quantity_tab(l_ins_index) := l_etc_quantity_pc_pfc;
2829 l_ins_txn_raw_cost_tab(l_ins_index) :=
2830 l_etc_quantity_pc_pfc * l_pc_pfc_raw_cost_rate;
2831 l_ins_txn_burdened_cost_tab(l_ins_index) :=
2832 l_etc_quantity_pc_pfc * l_pc_pfc_brdn_cost_rate;
2833 l_ins_txn_revenue_tab(l_ins_index) :=
2834 l_etc_quantity_pc_pfc * l_pc_pfc_revenue_rate;
2835 -- Added for Bug 5203622
2836 l_ins_other_rej_code_tab(l_ins_index) := l_other_rej_code;
2837
2838 IF l_currency_flag = 'PC' THEN
2839 l_ins_currency_code_tab(l_ins_index) := l_pc_currency_code;
2840 l_ins_pc_raw_cost_tab(l_ins_index) :=
2841 l_etc_quantity_pc_pfc * l_pc_pfc_raw_cost_rate;
2842 l_ins_pc_burdened_cost_tab(l_ins_index) :=
2843 l_etc_quantity_pc_pfc * l_pc_pfc_brdn_cost_rate;
2844 l_ins_pc_revenue_tab(l_ins_index) :=
2845 l_etc_quantity_pc_pfc * l_pc_pfc_revenue_rate;
2846 l_ins_pfc_raw_cost_tab(l_ins_index) := NULL;
2847 l_ins_pfc_burdened_cost_tab(l_ins_index) := NULL;
2848 l_ins_pfc_revenue_tab(l_ins_index) := NULL;
2849 ELSIF l_currency_flag = 'PFC' THEN
2850 l_ins_currency_code_tab(l_ins_index) := l_pfc_currency_code;
2851 l_ins_pc_raw_cost_tab(l_ins_index) := NULL;
2852 l_ins_pc_burdened_cost_tab(l_ins_index) := NULL;
2853 l_ins_pc_revenue_tab(l_ins_index) := NULL;
2854 l_ins_pfc_raw_cost_tab(l_ins_index) :=
2855 l_etc_quantity_pc_pfc * l_pc_pfc_raw_cost_rate;
2856 l_ins_pfc_burdened_cost_tab(l_ins_index) :=
2857 l_etc_quantity_pc_pfc * l_pc_pfc_brdn_cost_rate;
2858 l_ins_pfc_revenue_tab(l_ins_index) :=
2859 l_etc_quantity_pc_pfc * l_pc_pfc_revenue_rate;
2860 ELSE
2861 l_ins_currency_code_tab(l_ins_index) := NULL;
2862 l_ins_pc_raw_cost_tab(l_ins_index) := NULL;
2863 l_ins_pc_burdened_cost_tab(l_ins_index) := NULL;
2864 l_ins_pc_revenue_tab(l_ins_index) := NULL;
2865 l_ins_pfc_raw_cost_tab(l_ins_index) := NULL;
2866 l_ins_pfc_burdened_cost_tab(l_ins_index) := NULL;
2867 l_ins_pfc_revenue_tab(l_ins_index) := NULL;
2868 END IF;
2869
2870 /**************BY THIS TIME, WE HAVE ALL ETC DATA FOR PC or PFC*********/
2871
2872 ELSIF l_currency_flag = 'TC' THEN
2873 /* No matter ETC source is 'FINANCIAL_PLAN' or 'WORKPLAN_RESOURCES', always
2874 get total plan amounts by txn currency from financial data model.*/
2875 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
2876 txn_currency_code,
2877 SUM(NVL(total_plan_quantity,0)),
2878 SUM(NVL(total_txn_raw_cost,0)),
2879 SUM(NVL(total_txn_burdened_cost,0)),
2880 SUM(NVL(total_txn_revenue,0))
2881 BULK COLLECT INTO
2882 l_tot_currency_code_tab,
2883 l_tot_quantity_tab,
2884 l_tot_raw_cost_tab,
2885 l_tot_brdn_cost_tab,
2886 l_tot_revenue_tab
2887 FROM PA_FP_CALC_AMT_TMP2
2888 WHERE resource_assignment_id = l_src_res_asg_id
2889 AND transaction_source_code = l_etc_source_code
2890 GROUP BY txn_currency_code;
2891
2892 IF l_tot_currency_code_tab.count = 0 THEN
2893 RAISE continue_loop;
2894 END IF;
2895
2896 -- IPM Change:
2897 -- For non-rate-based target transactions,
2898 -- if the Source is a Cost and Revenue together version,
2899 -- then regardless of the Target version type:
2900 -- set target quantity to source raw cost if it exists, OR
2901 -- set target quantity to source revenue otherwise.
2902 -- This is done to handle source planning transactions that
2903 -- have only revenue amounts (without cost amounts).
2904 --
2905 -- For non-rate-based target transactions and other Source
2906 -- version types, set target quantity to source raw cost as before.
2907
2908 IF l_rate_based_flag = 'N' THEN
2909 IF l_source_version_type = 'ALL' THEN
2910 -- Set total quantity for each Currency depending on whether
2911 -- source raw cost exists (i.e. if it is a revenue-only txn).
2912 FOR i IN 1..l_tot_quantity_tab.count LOOP
2913 IF nvl(l_tot_raw_cost_tab(i),0) = 0 THEN
2914 l_tot_quantity_tab(i) := l_tot_revenue_tab(i);
2915 ELSE
2916 l_tot_quantity_tab(i) := l_tot_raw_cost_tab(i);
2917 END IF;
2918 END LOOP;
2919 ELSE
2920 l_tot_quantity_tab := l_tot_raw_cost_tab;
2921 END IF;
2922 END IF;
2923
2924 /* Bug 4085203
2925 The total plan amounts should be summed up irrespective of rate based
2926 or non rate based. Because for non rate based resource, we used the
2927 sum value when plan and actuals are using same one currency. When
2928 plan and actuals are using more than one currencies, the flow will
2929 not use the sum amounts.*/
2930 -- Added l_tot_raw_cost_sum, l_tot_revenue_sum for Bug 5203622
2931 l_tot_quantity_sum := 0;
2932 l_tot_raw_cost_sum := 0;
2933 l_tot_revenue_sum := 0;
2934 FOR i IN 1..l_tot_quantity_tab.count LOOP
2935 l_tot_quantity_sum := l_tot_quantity_sum + NVL(l_tot_quantity_tab(i),0);
2936 l_tot_raw_cost_sum := l_tot_raw_cost_sum + NVL(l_tot_raw_cost_tab(i),0);
2937 l_tot_revenue_sum := l_tot_revenue_sum + NVL(l_tot_revenue_tab(i),0);
2938 END LOOP;
2939
2940 IF l_etc_source_code = 'FINANCIAL_PLAN' THEN
2941 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
2942 txn_currency_code,
2943 SUM(NVL(quantity,0)),
2944 SUM(NVL(txn_raw_cost,0)),
2945 SUM(NVL(txn_brdn_cost,0)),
2946 SUM(NVL(txn_revenue,0))
2947 BULK COLLECT INTO
2948 l_act_currency_code_tab,
2949 l_act_quantity_tab,
2950 l_act_raw_cost_tab,
2951 l_act_brdn_cost_tab,
2952 l_act_revenue_tab
2953 FROM PA_FP_FCST_GEN_TMP1
2954 WHERE project_element_id = l_curr_task_id
2955 AND res_list_member_id = l_resource_list_member_id
2956 AND data_type_code = 'ETC_FP'
2957 GROUP BY txn_currency_code;
2958
2959 IF l_rate_based_flag = 'N' THEN
2960 l_act_quantity_tab := l_act_raw_cost_tab;
2961 END IF;
2962
2963 /* Bug 4085203
2964 The total actual amounts should be summed up irrespective of rate based
2965 or non rate based. Because for non rate based resource, we used the
2966 sum value when plan and actuals are using same one currency. When
2967 plan and actuals are using more than one currencies, the flow will
2968 not use the sum amounts.*/
2969 l_act_quantity_sum := 0;
2970 l_act_raw_cost_sum := 0; -- Added for Bug 5203622
2971 FOR i IN 1..l_act_quantity_tab.count LOOP
2972 l_act_quantity_sum := l_act_quantity_sum + l_act_quantity_tab(i);
2973 -- Added for Bug 5203622
2974 l_act_raw_cost_sum := l_act_raw_cost_sum + l_act_raw_cost_tab(i);
2975 END LOOP;
2976
2977 ELSIF l_etc_source_code = 'WORKPLAN_RESOURCES' THEN
2978 /*Bug fix for 3973511
2979 Workplan side only stores amounts in one currency for each planning
2980 resource. Instead of relying on pa_progress_utils.get_actuals_for_task
2981 to get actuals data, we query directly to pa_budget_lines to get actual
2982 data from source workplan budget version */
2983 IF P_PA_DEBUG_MODE = 'Y' THEN
2984 pa_fp_gen_amount_utils.fp_debug(
2985 p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
2986 'GET_WP_ACTUALS_FOR_RA',
2987 p_module_name => l_module_name,
2988 p_log_level => 5);
2989 END IF;
2990 PA_FP_GEN_FCST_AMT_PUB1.GET_WP_ACTUALS_FOR_RA
2991 (P_FP_COLS_SRC_REC => l_fp_cols_src_rec,
2992 P_FP_COLS_TGT_REC => p_fp_cols_tgt_rec,
2993 P_SRC_RES_ASG_ID => l_src_res_asg_id,
2994 P_TASK_ID => l_curr_task_id,
2995 P_RES_LIST_MEM_ID => l_resource_list_member_id,
2996 P_ACTUALS_THRU_DATE => p_actuals_thru_date,
2997 X_ACT_QUANTITY => lx_act_quantity,
2998 X_ACT_TXN_CURRENCY_CODE => lx_act_txn_currency_code,
2999 X_ACT_TXN_RAW_COST => lx_act_txn_raw_cost,
3000 X_ACT_TXN_BRDN_COST => lx_act_txn_brdn_cost,
3001 X_ACT_PC_RAW_COST => lx_act_pc_raw_cost,
3002 X_ACT_PC_BRDN_COST => lx_act_pc_brdn_cost,
3003 X_ACT_PFC_RAW_COST => lx_act_pfc_raw_cost,
3004 X_ACT_PFC_BRDN_COST => lx_act_pfc_brdn_cost,
3005 X_RETURN_STATUS => x_return_status,
3006 X_MSG_COUNT => x_msg_count,
3007 X_MSG_DATA => x_msg_data );
3008 IF P_PA_DEBUG_MODE = 'Y' THEN
3009 pa_fp_gen_amount_utils.fp_debug(
3010 p_msg => 'After calling PA_FP_GEN_FCST_AMT_PUB1.'||
3011 'GET_WP_ACTUALS_FOR_RA in remain_bdgt:'||x_return_status,
3012 p_module_name => l_module_name,
3013 p_log_level => 5);
3014 END IF;
3015 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3016 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3017 END IF;
3018
3019 l_act_currency_code_tab(1) := lx_act_txn_currency_code;
3020 l_act_quantity_tab(1) := lx_act_quantity;
3021 l_act_raw_cost_tab(1) := lx_act_txn_raw_cost;
3022 l_act_brdn_cost_tab(1):= lx_act_txn_brdn_cost;
3023 l_act_revenue_tab(1) := 0;
3024
3025 IF l_rate_based_flag = 'N' THEN
3026 l_act_quantity_tab := l_act_raw_cost_tab;
3027 END IF;
3028
3029 l_act_quantity_sum := l_act_quantity_tab(1);
3030 END IF;
3031
3032
3033 /* Check the relationship between total currency codes and actual currency
3034 codes. If actual currency codes are subset of total currency codes, then,
3035 take currency based approach; otherwise, take prorating based approach.
3036 'C' means take currency based calculation
3037 'P' means take prorating based calculation */
3038
3039 SELECT COUNT(*)
3040 INTO l_currency_count_act_min_tot
3041 FROM (
3042 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
3043 DISTINCT txn_currency_code
3044 FROM PA_FP_FCST_GEN_TMP1
3045 WHERE project_element_id = l_curr_task_id
3046 AND res_list_member_id = l_resource_list_member_id
3047 AND data_type_code = DECODE(L_ETC_SOURCE_CODE,
3048 'WORKPLAN_RESOURCES', 'ETC_WP',
3049 'FINANCIAL_PLAN', 'ETC_FP')
3050 MINUS
3051 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
3052 DISTINCT txn_currency_code
3053 FROM PA_FP_CALC_AMT_TMP2
3054 WHERE resource_assignment_id = l_src_res_asg_id
3055 AND transaction_source_code = l_etc_source_code
3056 ) WHERE rownum = 1;
3057
3058 IF l_currency_count_act_min_tot = 0 THEN
3059 l_currency_prorate_act_flag := 'C';
3060 ELSE
3061 l_currency_prorate_act_flag := 'P';
3062 END IF;
3063
3064 /*Bug fix: 4085203: If there only exists one plan currency,
3065 one actual currency and they are same, no matter it's rate
3066 based resource or non rate based resource, if etc quantity is
3067 calculated as less or equal to zero, then don't generate the ETC.*/
3068 IF l_act_currency_code_tab.count = 1 AND l_tot_currency_code_tab.count = 1 THEN
3069 l_etc_quantity_sum := l_tot_quantity_sum - l_act_quantity_sum;
3070 -- ER 5726773: Instead of directly checking if (ETC <= 0), let the
3071 -- plan_etc_signs_match function decide if ETC should be generated.
3072 IF NOT pa_fp_fcst_gen_amt_utils.PLAN_ETC_SIGNS_MATCH
3073 (l_tot_quantity_sum, l_etc_quantity_sum) THEN
3074 RAISE continue_loop;
3075 ELSE
3076 l_etc_quantity_tab(1) := l_etc_quantity_sum;
3077
3078 -- Bug 5203622: Store OTHER rejection code in the
3079 -- txn_currency_code column of pa_fp_calc_amt_tmp2.
3080 l_other_rej_code_tab(1) := null;
3081 IF l_rate_based_flag = 'N' AND
3082 l_source_version_type = 'ALL' AND
3083 l_target_version_type = 'ALL' AND
3084 nvl(l_tot_raw_cost_sum,0) = 0 AND
3085 nvl(l_tot_revenue_sum,0) <> 0 AND
3086 nvl(l_act_raw_cost_sum,0) <> 0 THEN
3087 l_other_rej_code_tab(1) := 'PA_FP_ETC_REV_FIELD_ERR';
3088 END IF;
3089 END IF;
3090 ELSE
3091 l_exit_flag := 'N';
3092 IF l_currency_prorate_act_flag = 'C' THEN
3093 FOR i IN 1..l_tot_currency_code_tab.count LOOP
3094 IF l_exit_flag = 'Y' THEN
3095 EXIT;
3096 END IF;
3097 l_etc_quantity_tab(i) := l_tot_quantity_tab(i);
3098 l_other_rej_code_tab(i) := null; -- Added for Bug 5203622
3099
3100 FOR j IN 1..l_act_currency_code_tab.count LOOP
3101 IF l_tot_currency_code_tab(i) = l_act_currency_code_tab(j) THEN
3102 l_etc_quantity_tab(i) := l_etc_quantity_tab(i) - l_act_quantity_tab(j);
3103
3104 -- Bug 5203622: Added OTHER REJECTION CODE logic.
3105 IF l_rate_based_flag = 'N' AND
3106 l_source_version_type = 'ALL' AND
3107 l_target_version_type = 'ALL' AND
3108 nvl(l_tot_raw_cost_tab(i),0) = 0 AND
3109 nvl(l_tot_revenue_tab(i),0) <> 0 AND
3110 nvl(l_act_raw_cost_tab(j),0) <> 0 THEN
3111 l_other_rej_code_tab(i) := 'PA_FP_ETC_REV_FIELD_ERR';
3112 END IF;
3113
3114 -- ER 5726773: Instead of directly checking if (ETC <= 0), let the
3115 -- plan_etc_signs_match function decide if ETC should be prorated.
3116 IF NOT pa_fp_fcst_gen_amt_utils.PLAN_ETC_SIGNS_MATCH
3117 (l_tot_quantity_tab(i), l_etc_quantity_tab(i)) THEN
3118 l_currency_prorate_act_flag := 'P';
3119 l_etc_quantity_tab.delete;
3120 l_other_rej_code_tab.delete; -- Added for Bug 5203622
3121 l_exit_flag := 'Y';
3122 EXIT;
3123 END IF;
3124 END IF;
3125 END LOOP;
3126 END LOOP;
3127 END IF;
3128
3129 IF l_currency_prorate_act_flag = 'P' THEN
3130 IF l_rate_based_flag = 'N' THEN
3131 l_currency_flag := 'PC_TC';
3132 ELSIF l_rate_based_flag = 'Y' THEN
3133 l_etc_quantity_sum := l_tot_quantity_sum - l_act_quantity_sum;
3134 -- ER 5726773: Instead of directly checking if (ETC <= 0), let the
3135 -- plan_etc_signs_match function decide if ETC should be generated.
3136 IF NOT pa_fp_fcst_gen_amt_utils.PLAN_ETC_SIGNS_MATCH
3137 (l_tot_quantity_sum, l_etc_quantity_sum) THEN
3138 /* no non-commitment ETC available,
3139 only actual and commitment amounts need to be spreaded */
3140 RAISE continue_loop;
3141 END IF;
3142
3143 FOR i IN 1..l_tot_currency_code_tab.count LOOP
3144 IF l_tot_quantity_sum <> 0 THEN
3145 l_etc_quantity_tab(i) := l_etc_quantity_sum
3146 * (l_tot_quantity_tab (i) / l_tot_quantity_sum) ;
3147 ELSE
3148 l_etc_quantity_tab(i) := NULL;
3149 END IF;
3150 /* hr_utility.trace(i||'th');
3151 hr_utility.trace('etc qty '||l_etc_qty );
3152 hr_utility.trace('etc curr'||l_ETC_CURRENCY_CODE );
3153 hr_utility.trace('etc rc '||l_etc_txn_raw_cost );
3154 hr_utility.trace('etc bc '||l_etc_txn_brdn_cost ); */
3155 END LOOP;
3156 END IF;
3157 END IF;
3158 END IF;
3159
3160 /*currency_flag may get changed to 'PC_TC', when actual currencies is subset of
3161 planning currencies, target resource is non_rate_based, but actual amount for
3162 one particular currency is less than plan amount. Then we need to revert from
3163 currency based approach to prorating based approach.For non_rate_based resource,
3164 prorating falls in to currency code of 'PC_TC'.*/
3165 IF l_currency_flag = 'TC' THEN
3166 /*When not taking periodic rates, we need to calculate out the average
3167 rates from the source resource assignments that are mapped to the current
3168 target resource assignment.*/
3169 FOR i IN 1..l_tot_currency_code_tab.count LOOP
3170 SELECT /*+ INDEX(pa_fp_calc_amt_tmp2,PA_FP_CALC_AMT_TMP2_N2)*/
3171 NVL(SUM(NVL(total_plan_quantity,0)),0),
3172 NVL(SUM(NVL(total_txn_raw_cost,0)),0),
3173 NVL(SUM(NVL(total_txn_burdened_cost,0)),0),
3174 NVL(SUM(NVL(total_txn_revenue,0)),0),
3175 NVL(SUM(NVL(total_pc_raw_cost,0)),0),
3176 NVL(SUM(NVL(total_pc_burdened_cost,0)),0),
3177 NVL(SUM(NVL(total_pc_revenue,0)),0)
3178 INTO l_txn_rate_quantity,
3179 l_txn_rate_raw_cost,
3180 l_txn_rate_brdn_cost,
3181 l_txn_rate_revenue,
3182 l_pc_rate_raw_cost,
3183 l_pc_rate_brdn_cost,
3184 l_pc_rate_revenue
3185 FROM pa_fp_calc_amt_tmp2
3186 WHERE resource_assignment_id = l_src_res_asg_id
3187 AND txn_currency_code = l_tot_currency_code_tab(i)
3188 AND transaction_source_code in ('FINANCIAL_PLAN',
3189 'WORKPLAN_RESOURCES');
3190
3191
3192 -- IPM Change:
3193 -- For non-rate-based target transactions,
3194 -- if the Source is a Cost and Revenue together version,
3195 -- then regardless of the Target version type:
3196 -- set rate quantity to rate raw cost if it exists, OR
3197 -- set rate quantity to rate revenue otherwise.
3198 -- This is done to handle source planning transactions that
3199 -- have only revenue amounts (without cost amounts).
3200 --
3201 -- For non-rate-based target transactions and other Source
3202 -- version types, set rate quantity to rate raw cost as before.
3203
3204 IF l_rate_based_flag = 'N' THEN
3205 IF l_source_version_type = 'ALL' THEN
3206 IF nvl(l_txn_rate_raw_cost,0) = 0 THEN
3207 l_txn_rate_quantity := l_txn_rate_revenue;
3208 ELSE
3209 l_txn_rate_quantity := l_txn_rate_raw_cost;
3210 END IF;
3211 ELSE
3212 l_txn_rate_quantity := l_txn_rate_raw_cost;
3213 END IF;
3214 END IF;
3215
3216 IF l_txn_rate_quantity <> 0 THEN
3217 l_txn_raw_cost_rate_tab(i) := l_txn_rate_raw_cost
3218 / l_txn_rate_quantity;
3219 l_txn_brdn_cost_rate_tab(i) := l_txn_rate_brdn_cost
3220 / l_txn_rate_quantity;
3221 l_txn_revenue_rate_tab(i) := l_txn_rate_revenue
3222 / l_txn_rate_quantity;
3223 l_pc_raw_cost_rate_tab(i) := l_pc_rate_raw_cost
3224 / l_txn_rate_quantity;
3225 l_pc_brdn_cost_rate_tab(i) := l_pc_rate_brdn_cost
3226 / l_txn_rate_quantity;
3227 l_pc_revenue_rate_tab(i) := l_pc_rate_revenue
3228 / l_txn_rate_quantity;
3229 ELSE
3230 l_txn_raw_cost_rate_tab(i) := NULL;
3231 l_txn_brdn_cost_rate_tab(i) := NULL;
3232 l_txn_revenue_rate_tab(i) := NULL;
3233 l_pc_raw_cost_rate_tab(i) := NULL;
3234 l_pc_brdn_cost_rate_tab(i) := NULL;
3235 l_pc_revenue_rate_tab(i) := NULL;
3236 END IF;
3237 END LOOP;
3238
3239 FOR i IN 1..l_etc_quantity_tab.count LOOP
3240 l_ins_index := l_ins_src_res_asg_id_tab.count + 1;
3241 l_ins_src_res_asg_id_tab(l_ins_index) := l_src_res_asg_id;
3242 l_ins_tgt_res_asg_id_tab(l_ins_index) := l_tgt_res_asg_id;
3243 l_ins_currency_code_tab(l_ins_index) := l_tot_currency_code_tab(i);
3244 l_ins_etc_quantity_tab(l_ins_index) := l_etc_quantity_tab(i);
3245 l_ins_txn_raw_cost_tab(l_ins_index) :=
3246 l_etc_quantity_tab(i) * l_txn_raw_cost_rate_tab(i);
3247 l_ins_txn_burdened_cost_tab(l_ins_index) :=
3248 l_etc_quantity_tab(i) * l_txn_brdn_cost_rate_tab(i);
3249 l_ins_txn_revenue_tab(l_ins_index) :=
3250 l_etc_quantity_tab(i) * l_txn_revenue_rate_tab(i);
3251 l_ins_pc_raw_cost_tab(l_ins_index) :=
3252 l_etc_quantity_tab(i) * l_pc_raw_cost_rate_tab(i);
3253 l_ins_pc_burdened_cost_tab(l_ins_index) :=
3254 l_etc_quantity_tab(i) * l_pc_brdn_cost_rate_tab(i);
3255 l_ins_pc_revenue_tab(l_ins_index) :=
3256 l_etc_quantity_tab(i) * l_pc_revenue_rate_tab(i);
3257 l_ins_pfc_raw_cost_tab(l_ins_index) := NULL;
3258 l_ins_pfc_burdened_cost_tab(l_ins_index) := NULL;
3259 l_ins_pfc_revenue_tab(l_ins_index) := NULL;
3260 -- Added for Bug 5203622
3261 l_ins_other_rej_code_tab(l_ins_index) := l_other_rej_code_tab(i);
3262 END LOOP;
3263
3264 END IF;
3265 END IF;
3266 /**************NOW WE HAVE ALL ETC DATA IN TC*************/
3267
3268 IF l_currency_flag = 'PC_TC' THEN
3269 /*Take PC for calculation, then convert back to TC.
3270 This only happens for non rate based resources*/
3271
3272 /*No matter ETC source is 'FINANCIAL_PLAN' or 'WORKPLAN_RESOURCES',
3273 always get total plan amounts in PC from financial data model*/
3274 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
3275 txn_currency_code,
3276 SUM(NVL(total_plan_quantity,0)),
3277 SUM(NVL(total_pc_raw_cost,0)),
3278 SUM(NVL(total_pc_burdened_cost,0)),
3279 SUM(NVL(total_pc_revenue,0))
3280 BULK COLLECT INTO
3281 l_tot_currency_code_tab,
3282 l_tot_quantity_pc_tab,
3283 l_tot_raw_cost_pc_tab,
3284 l_tot_brdn_cost_pc_tab,
3285 l_tot_revenue_pc_tab
3286 FROM PA_FP_CALC_AMT_TMP2
3287 WHERE resource_assignment_id = l_src_res_asg_id
3288 AND transaction_source_code = l_etc_source_code
3289 GROUP BY txn_currency_code;
3290
3291 -- Bug 4244609: Previously, we assigned raw cost or revenue to quantity
3292 -- based on Target version type. Now, we always set quantity = raw cost
3293 -- for non-rate-based resources.
3294
3295 -- IPM Change:
3296 -- For non-rate-based target transactions,
3297 -- if the Source is a Cost and Revenue together version,
3298 -- then regardless of the Target version type:
3299 -- set target quantity to source raw cost if it exists, OR
3300 -- set target quantity to source revenue otherwise.
3301 -- This is done to handle source planning transactions that
3302 -- have only revenue amounts (without cost amounts).
3303 --
3304 -- For non-rate-based target transactions and other Source
3305 -- version types, set target quantity to source raw cost as before.
3306
3307 IF l_source_version_type = 'ALL' THEN
3308 -- Set total quantity for each Currency depending on whether
3309 -- source raw cost exists (i.e. if it is a revenue-only txn).
3310 FOR i IN 1..l_tot_quantity_pc_tab.count LOOP
3311 IF nvl(l_tot_raw_cost_pc_tab(i),0) = 0 THEN
3312 l_tot_quantity_pc_tab(i) := l_tot_revenue_pc_tab(i);
3313 ELSE
3314 l_tot_quantity_pc_tab(i) := l_tot_raw_cost_pc_tab(i);
3315 END IF;
3316 END LOOP;
3317 ELSE
3318 l_tot_quantity_pc_tab := l_tot_raw_cost_pc_tab;
3319 END IF;
3320
3321 -- Added l_tot_raw_cost_pc_sum, l_tot_revenue_pc_sum for Bug 5203622
3322 l_tot_quantity_pc_sum := 0;
3323 l_tot_raw_cost_pc_sum := 0;
3324 l_tot_revenue_pc_sum := 0;
3325 FOR i IN 1..l_tot_quantity_pc_tab.count LOOP
3326 l_tot_quantity_pc_sum := l_tot_quantity_pc_sum + l_tot_quantity_pc_tab(i);
3327 l_tot_raw_cost_pc_sum := l_tot_raw_cost_pc_sum + l_tot_raw_cost_pc_tab(i);
3328 l_tot_revenue_pc_sum := l_tot_revenue_pc_sum + l_tot_revenue_pc_tab(i);
3329 END LOOP;
3330
3331 IF l_etc_source_code = 'FINANCIAL_PLAN' THEN
3332 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
3333 NVL(SUM( DECODE(l_rate_based_flag,
3334 'Y', NVL(quantity,0),
3335 'N', NVL(prj_raw_cost,0))),0),
3336 NVL(SUM(NVL(prj_raw_cost,0)),0)
3337 INTO l_act_quantity_pc_sum,
3338 l_act_raw_cost_pc_sum -- Added for Bug 5203622
3339 FROM PA_FP_FCST_GEN_TMP1
3340 WHERE project_element_id = l_curr_task_id
3341 AND res_list_member_id = l_resource_list_member_id
3342 AND data_type_code = 'ETC_FP';
3343
3344 ELSIF l_etc_source_code = 'WORKPLAN_RESOURCES' THEN
3345 /*Workplan side only stores amounts in one currency for each planning
3346 resource, so still rely on pa_progress_utils.get_actuals_for_task to
3347 get actuals data. This part needs to be revisted when workplan side is
3348 changed to support multi currencies.*/
3349 IF P_PA_DEBUG_MODE = 'Y' THEN
3350 pa_fp_gen_amount_utils.fp_debug(
3351 p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
3352 'GET_WP_ACTUALS_FOR_RA',
3353 p_module_name => l_module_name,
3354 p_log_level => 5);
3355 END IF;
3356 PA_FP_GEN_FCST_AMT_PUB1.GET_WP_ACTUALS_FOR_RA
3357 (P_FP_COLS_SRC_REC => l_fp_cols_src_rec,
3358 P_FP_COLS_TGT_REC => p_fp_cols_tgt_rec,
3359 P_SRC_RES_ASG_ID => l_src_res_asg_id,
3360 P_TASK_ID => l_curr_task_id,
3361 P_RES_LIST_MEM_ID => l_resource_list_member_id,
3362 P_ACTUALS_THRU_DATE => p_actuals_thru_date,
3363 X_ACT_QUANTITY => lx_act_quantity,
3364 X_ACT_TXN_CURRENCY_CODE => lx_act_txn_currency_code,
3365 X_ACT_TXN_RAW_COST => lx_act_txn_raw_cost,
3366 X_ACT_TXN_BRDN_COST => lx_act_txn_brdn_cost,
3367 X_ACT_PC_RAW_COST => lx_act_pc_raw_cost,
3368 X_ACT_PC_BRDN_COST => lx_act_pc_brdn_cost,
3369 X_ACT_PFC_RAW_COST => lx_act_pfc_raw_cost,
3370 X_ACT_PFC_BRDN_COST => lx_act_pfc_brdn_cost,
3371 X_RETURN_STATUS => x_return_status,
3372 X_MSG_COUNT => x_msg_count,
3373 X_MSG_DATA => x_msg_data );
3374 IF P_PA_DEBUG_MODE = 'Y' THEN
3375 pa_fp_gen_amount_utils.fp_debug(
3376 p_msg => 'After calling PA_FP_GEN_FCST_AMT_PUB1.'||
3377 'GET_WP_ACTUALS_FOR_RA in remain_bdgt:'||x_return_status,
3378 p_module_name => l_module_name,
3379 p_log_level => 5);
3380 END IF;
3381 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3382 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3383 END IF;
3384
3385 l_act_quantity_pc_sum := lx_act_pc_raw_cost;
3386 l_act_raw_cost_pc_sum := lx_act_pc_raw_cost; -- Added for Bug 5203622
3387 END IF;
3388
3389 /*Prorate total ETC quantity in PC based according to the transaction
3390 currency codes from the plan totals.*/
3391 /*Get total ETC quantity and Prorate ETC quantity*/
3392 l_etc_quantity_pc_sum := l_tot_quantity_pc_sum - l_act_quantity_pc_sum;
3393 -- ER 5726773: Instead of directly checking if (ETC <= 0), let the
3394 -- plan_etc_signs_match function decide if ETC should be generated.
3395 IF NOT pa_fp_fcst_gen_amt_utils.PLAN_ETC_SIGNS_MATCH
3396 (l_tot_quantity_pc_sum, l_etc_quantity_pc_sum) THEN
3397 /* only need to spread commitment data and actual data*/
3398 RAISE continue_loop;
3399 END IF;
3400
3401 -- Bug 5203622: Added OTHER REJECTION CODE logic.
3402 l_other_rej_code := null;
3403 IF l_rate_based_flag = 'N' AND
3404 l_source_version_type = 'ALL' AND
3405 l_target_version_type = 'ALL' AND
3406 nvl(l_tot_raw_cost_pc_sum,0) = 0 AND
3407 nvl(l_tot_revenue_pc_sum,0) <> 0 AND
3408 nvl(l_act_raw_cost_pc_sum,0) <> 0 THEN
3409 l_other_rej_code := 'PA_FP_ETC_REV_FIELD_ERR';
3410 END IF;
3411
3412 FOR i IN 1..l_tot_currency_code_tab.count LOOP
3413 IF NVL(l_tot_quantity_pc_sum,0) <> 0 THEN
3414 l_etc_quantity_pc_tab(i) := l_etc_quantity_pc_sum
3415 * (l_tot_quantity_pc_tab(i) / l_tot_quantity_pc_sum) ;
3416 ELSE
3417 l_etc_quantity_pc_tab(i) := NULL;
3418 --l_etc_quantity_pc_tab(i) := l_etc_quantity_pc_sum; -- ???
3419 END IF;
3420 -- Added for Bug 5203622
3421 l_other_rej_code_tab(i) := l_other_rej_code;
3422 END LOOP;
3423
3424 /* Convert PC into TC */
3425 FOR i IN 1..l_tot_currency_code_tab.count LOOP
3426 IF l_tot_currency_code_tab(i) = l_pc_currency_code THEN
3427 l_etc_quantity_tab(i) := l_etc_quantity_pc_tab(i);
3428 ELSE
3429 l_etc_quantity_tab(i) := NULL;
3430 BEGIN
3431 SELECT task_id,
3432 planning_start_date
3433 INTO l_task_id,
3434 l_planning_start_date
3435 FROM pa_resource_assignments
3436 WHERE resource_assignment_id = l_src_res_asg_id;
3437 EXCEPTION
3438 WHEN NO_DATA_FOUND THEN
3439 l_task_id := NULL;
3440 l_planning_start_date := NULL;
3441 END;
3442 IF P_PA_DEBUG_MODE = 'Y' THEN
3443 pa_fp_gen_amount_utils.fp_debug(
3444 p_msg => 'Before calling pa_multi_currency_txn.'||
3445 'get_currency_amounts in remain_bdgt',
3446 p_module_name => l_module_name,
3447 p_log_level => 5);
3448 END IF;
3449 -- Bug 4091344: Changed P_status parameter from x_return_status to
3450 -- local variable l_status. Afterwards, we check l_status and set
3451 -- x_return_status accordingly.
3452 pa_multi_currency_txn.get_currency_amounts (
3453 P_project_id => p_fp_cols_tgt_rec.x_project_id,
3454 P_exp_org_id => NULL,
3455 P_calling_module => 'WORKPLAN',
3456 P_task_id => l_task_id,
3457 P_EI_date => l_planning_start_date,
3458 P_denom_raw_cost => l_etc_quantity_pc_tab(i),
3459 P_denom_curr_code => l_pc_currency_code,
3460 P_acct_curr_code => l_pc_currency_code,
3461 P_accounted_flag => 'N',
3462 P_acct_rate_date => lx_acc_rate_date,
3463 P_acct_rate_type => lx_acct_rate_type,
3464 P_acct_exch_rate => lx_acct_exch_rate,
3465 P_acct_raw_cost => lx_acct_raw_cost,
3466 P_project_curr_code => l_tot_currency_code_tab(i),
3467 P_project_rate_type => lx_project_rate_type,
3468 P_project_rate_date => lx_project_rate_date,
3469 P_project_exch_rate => lx_project_exch_rate,
3470 P_project_raw_cost => l_etc_quantity_tab(i),
3471 P_projfunc_curr_code=> l_pc_currency_code,
3472 P_projfunc_cost_rate_type => lx_projfunc_cost_rate_type,
3473 P_projfunc_cost_rate_date => lx_projfunc_cost_rate_date,
3474 P_projfunc_cost_exch_rate => lx_projfunc_cost_exch_rate,
3475 P_projfunc_raw_cost => l_projfunc_raw_cost,
3476 P_system_linkage => 'NER',
3477 P_status => l_status,
3478 P_stage => x_msg_count);
3479
3480
3481 IF lx_project_exch_rate IS NULL OR l_status IS NOT NULL THEN
3482 x_return_status := FND_API.G_RET_STS_ERROR;
3483 g_project_name := NULL;
3484 BEGIN
3485 SELECT name INTO g_project_name from
3486 PA_PROJECTS_ALL WHERE
3487 project_id = p_fp_cols_tgt_rec.x_project_id;
3488 EXCEPTION
3489 WHEN OTHERS THEN
3490 g_project_name := NULL;
3491 END;
3492 PA_UTILS.ADD_MESSAGE
3493 ( p_app_short_name => 'PA'
3494 ,p_msg_name => 'PA_FP_PROJ_NO_TXNCONVRATE'
3495 ,p_token1 => 'G_PROJECT_NAME'
3496 ,p_value1 => g_project_name
3497 ,p_token2 => 'FROMCURRENCY'
3498 ,p_value2 => l_pc_currency_code
3499 ,p_token3 => 'TOCURRENCY'
3500 ,p_value3 => l_tot_currency_code_tab(i) );
3501 x_msg_data := 'PA_FP_PROJ_NO_TXNCONVRATE';
3502 END IF;
3503 IF P_PA_DEBUG_MODE = 'Y' THEN
3504 pa_fp_gen_amount_utils.fp_debug(
3505 p_msg => 'After calling pa_multi_currency_txn.'||
3506 'get_currency_amounts in remain_bdgt:'||x_return_status,
3507 p_module_name => l_module_name,
3508 p_log_level => 5);
3509 END IF;
3510 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3511 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3512 END IF;
3513 END IF;
3514 END LOOP;
3515
3516 /*When not taking periodic rates, we need to calculate out the average rates
3517 from the source resource assignments that are mapped to the current target
3518 resource assignment.*/
3519
3520 FOR i IN 1..l_tot_currency_code_tab.count LOOP
3521 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
3522 NVL(SUM(NVL(total_plan_quantity,0)),0),
3523 NVL(SUM(NVL(total_txn_raw_cost,0)),0),
3524 NVL(SUM(NVL(total_txn_burdened_cost,0)),0),
3525 NVL(SUM(NVL(total_txn_revenue,0)),0),
3526 NVL(SUM(NVL(total_pc_raw_cost,0)),0),
3527 NVL(SUM(NVL(total_pc_burdened_cost,0)),0),
3528 NVL(SUM(NVL(total_pc_revenue,0)),0)
3529 INTO l_txn_rate_quantity,
3530 l_txn_rate_raw_cost,
3531 l_txn_rate_brdn_cost,
3532 l_txn_rate_revenue,
3533 l_pc_rate_raw_cost,
3534 l_pc_rate_brdn_cost,
3535 l_pc_rate_revenue
3536 FROM pa_fp_calc_amt_tmp2
3537 WHERE resource_assignment_id = l_src_res_asg_id
3538 AND txn_currency_code = l_tot_currency_code_tab(i)
3539 AND transaction_source_code in ('FINANCIAL_PLAN' ,
3540 'WORKPLAN_RESOURCES');
3541
3542 -- IPM Change:
3543 -- For non-rate-based target transactions,
3544 -- if the Source is a Cost and Revenue together version,
3545 -- then regardless of the Target version type:
3546 -- set rate quantity to rate raw cost if it exists, OR
3547 -- set rate quantity to rate revenue otherwise.
3548 -- This is done to handle source planning transactions that
3549 -- have only revenue amounts (without cost amounts).
3550 --
3551 -- For non-rate-based target transactions and other Source
3552 -- version types, set rate quantity to rate raw cost as before.
3553
3554 IF l_source_version_type = 'ALL' THEN
3555 IF nvl(l_txn_rate_raw_cost,0) = 0 THEN
3556 l_txn_rate_quantity := l_txn_rate_revenue;
3557 ELSE
3558 l_txn_rate_quantity := l_txn_rate_raw_cost;
3559 END IF;
3560 ELSE
3561 l_txn_rate_quantity := l_txn_rate_raw_cost;
3562 END IF;
3563
3564 -- IPM Change:
3565 -- Since quantity can now be either raw cost or revenue,
3566 -- rates should not always be computed by dividing by raw
3567 -- cost. Code modified to use l_txn_rate_quantity instead.
3568 IF l_txn_rate_quantity <> 0 THEN
3569 l_txn_raw_cost_rate_tab(i) := l_txn_rate_raw_cost
3570 / l_txn_rate_quantity; -- Added in IPM
3571 l_txn_brdn_cost_rate_tab(i) := l_txn_rate_brdn_cost
3572 / l_txn_rate_quantity;
3573 l_txn_revenue_rate_tab(i) := l_txn_rate_revenue
3574 / l_txn_rate_quantity;
3575 l_pc_raw_cost_rate_tab(i) := l_pc_rate_raw_cost
3576 / l_txn_rate_quantity;
3577 l_pc_brdn_cost_rate_tab(i) := l_pc_rate_brdn_cost
3578 / l_txn_rate_quantity;
3579 l_pc_revenue_rate_tab(i) := l_pc_rate_revenue
3580 / l_txn_rate_quantity;
3581 ELSE
3582 l_txn_raw_cost_rate_tab(i) := NULL; -- Added in IPM
3583 l_txn_brdn_cost_rate_tab(i) := NULL;
3584 l_txn_revenue_rate_tab(i) := NULL;
3585 l_pc_raw_cost_rate_tab(i) := NULL;
3586 l_pc_brdn_cost_rate_tab(i) := NULL;
3587 l_pc_revenue_rate_tab(i) := NULL;
3588 END IF;
3589 END LOOP;
3590
3591 FOR i IN 1..l_etc_quantity_tab.count LOOP
3592 l_ins_index := l_ins_src_res_asg_id_tab.count + 1;
3593 l_ins_src_res_asg_id_tab(l_ins_index) := l_src_res_asg_id;
3594 l_ins_tgt_res_asg_id_tab(l_ins_index) := l_tgt_res_asg_id;
3595 l_ins_currency_code_tab(l_ins_index) := l_tot_currency_code_tab(i);
3596 l_ins_etc_quantity_tab(l_ins_index) := l_etc_quantity_tab(i);
3597 l_ins_txn_raw_cost_tab(l_ins_index) :=
3598 l_etc_quantity_tab(i) * l_txn_raw_cost_rate_tab(i);
3599 l_ins_txn_burdened_cost_tab(l_ins_index) :=
3600 l_etc_quantity_tab(i) * l_txn_brdn_cost_rate_tab(i);
3601 l_ins_txn_revenue_tab(l_ins_index) :=
3602 l_etc_quantity_tab(i) * l_txn_revenue_rate_tab(i);
3603 l_ins_pc_raw_cost_tab(l_ins_index) :=
3604 l_etc_quantity_tab(i) * l_pc_raw_cost_rate_tab(i);
3605 l_ins_pc_burdened_cost_tab(l_ins_index) :=
3606 l_etc_quantity_tab(i) * l_pc_brdn_cost_rate_tab(i);
3607 l_ins_pc_revenue_tab(l_ins_index) :=
3608 l_etc_quantity_tab(i) * l_pc_revenue_rate_tab(i);
3609 l_ins_pfc_raw_cost_tab(l_ins_index) := NULL;
3610 l_ins_pfc_burdened_cost_tab(l_ins_index) := NULL;
3611 l_ins_pfc_revenue_tab(l_ins_index) := NULL;
3612 -- Added for Bug 5203622
3613 l_ins_other_rej_code_tab(l_ins_index) := l_other_rej_code_tab(i);
3614 END LOOP;
3615
3616 /***************NOW WE HAVE ALL ETC DATA IN PC_TC*************/
3617
3618 END IF;
3619 /* End the check for 'PC', 'TC' and 'PC_TC'*/
3620
3621 EXCEPTION
3622 WHEN CONTINUE_LOOP THEN
3623 l_dummy := 1;
3624 WHEN OTHERS THEN
3625 RAISE;
3626 END;
3627 END LOOP; -- main loop
3628
3629 /* If commitment is not included, record is inserted directly as 'ETC'
3630 record,if commitment is to be considered, record is inserted as
3631 'TOTAL_ETC' for further processing.*/
3632 IF P_FP_COLS_TGT_REC.X_GEN_INCL_OPEN_COMM_FLAG = 'Y' THEN
3633 l_transaction_source_code := 'TOTAL_ETC';
3634 ELSE
3635 l_transaction_source_code := 'ETC';
3636 END IF;
3637
3638 -- Bug 5203622: Store OTHER rejection code in the
3639 -- txn_currency_code column of pa_fp_calc_amt_tmp2.
3640 FORALL i IN 1..l_ins_etc_quantity_tab.count
3641 INSERT INTO PA_FP_CALC_AMT_TMP2
3642 ( RESOURCE_ASSIGNMENT_ID,
3643 TARGET_RES_ASG_ID,
3644 ETC_CURRENCY_CODE,
3645 ETC_PLAN_QUANTITY,
3646 ETC_TXN_RAW_COST,
3647 ETC_TXN_BURDENED_COST,
3648 ETC_TXN_REVENUE,
3649 ETC_PC_RAW_COST,
3650 ETC_PC_BURDENED_COST,
3651 ETC_PC_REVENUE,
3652 ETC_PFC_RAW_COST,
3653 ETC_PFC_BURDENED_COST,
3654 ETC_PFC_REVENUE,
3655 TRANSACTION_SOURCE_CODE,
3656 TXN_CURRENCY_CODE ) -- Added for Bug 5203622
3657 VALUES ( l_ins_src_res_asg_id_tab(i),
3658 l_ins_tgt_res_asg_id_tab(i),
3659 l_ins_currency_code_tab(i),
3660 l_ins_etc_quantity_tab(i),
3661 l_ins_txn_raw_cost_tab(i),
3662 l_ins_txn_burdened_cost_tab(i),
3663 l_ins_txn_revenue_tab(i),
3664 l_ins_pc_raw_cost_tab(i),
3665 l_ins_pc_burdened_cost_tab(i),
3666 l_ins_pc_revenue_tab(i),
3667 l_ins_pfc_raw_cost_tab(i),
3668 l_ins_pfc_burdened_cost_tab(i),
3669 l_ins_pfc_revenue_tab(i),
3670 l_transaction_source_code,
3671 l_ins_other_rej_code_tab(i) ); -- Added for Bug 5203622
3672
3673 IF P_PA_DEBUG_MODE = 'Y' THEN
3674 PA_DEBUG.RESET_CURR_FUNCTION;
3675 END IF;
3676 EXCEPTION
3677 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3678 l_msg_count := FND_MSG_PUB.count_msg;
3679 IF l_msg_count = 1 THEN
3680 PA_INTERFACE_UTILS_PUB.get_messages
3681 ( p_encoded => FND_API.G_TRUE,
3682 p_msg_index => 1,
3683 p_msg_count => l_msg_count,
3684 p_msg_data => l_msg_data,
3685 p_data => l_data,
3686 p_msg_index_out => l_msg_index_out);
3687 x_msg_data := l_data;
3688 x_msg_count := l_msg_count;
3689 ELSE
3690 x_msg_count := l_msg_count;
3691 END IF;
3692
3693 ROLLBACK;
3694 x_return_status := FND_API.G_RET_STS_ERROR;
3695
3696 IF P_PA_DEBUG_MODE = 'Y' THEN
3697 pa_fp_gen_amount_utils.fp_debug
3698 (p_msg => 'Invalid Arguments Passed',
3699 p_module_name => l_module_name,
3700 p_log_level => 5);
3701 PA_DEBUG.RESET_CURR_FUNCTION;
3702 END IF;
3703 RAISE;
3704 WHEN OTHERS THEN
3705 rollback;
3706 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3707 x_msg_count := 1;
3708 x_msg_data := substr(sqlerrm,1,240);
3709 --dbms_output.put_line('error msg :'||x_msg_data);
3710 FND_MSG_PUB.add_exc_msg
3711 ( p_pkg_name => 'PA_FP_GEN_FCST_AMT_PUB3',
3712 p_procedure_name => 'GEN_ETC_REMAIN_BDGT_AMTS_BLK',
3713 p_error_text => substr(sqlerrm,1,240));
3714
3715 IF P_PA_DEBUG_MODE = 'Y' THEN
3716 pa_fp_gen_amount_utils.fp_debug
3717 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
3718 p_module_name => l_module_name,
3719 p_log_level => 5);
3720 PA_DEBUG.RESET_CURR_FUNCTION;
3721 END IF;
3722 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3723 END GET_ETC_REMAIN_BDGT_AMTS_BLK;
3724
3725
3726 END PA_FP_GEN_FCST_AMT_PUB3;
[Home] [Help]