1 PACKAGE body PA_FP_GEN_FCST_AMT_PUB3 as
2 /* $Header: PAFPFG3B.pls 120.8.12020000.3 2013/05/17 09:43:33 bpottipa 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_CBS_ELEMENT_ID_TAB IN PA_PLSQL_DATATYPES.IdTabTyp, --bug#16791711
2339 P_ETC_SOURCE_CODE_TAB IN PA_PLSQL_DATATYPES.Char30TabTyp,
2340 P_WP_STRUCTURE_VERSION_ID IN PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE,
2341 P_ACTUALS_THRU_DATE IN PA_PERIODS_ALL.END_DATE%TYPE,
2342 P_PLANNING_OPTIONS_FLAG IN VARCHAR2,
2343 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2344 X_MSG_COUNT OUT NOCOPY NUMBER,
2345 X_MSG_DATA OUT NOCOPY VARCHAR2)
2346 IS
2347 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_FCST_AMT_PUB3.GEN_ETC_REMAIN_BDGT_AMTS_BLK';
2348
2349 l_currency_flag VARCHAR2(30);
2350 l_rate_based_flag VARCHAR2(1);
2351 l_currency_count_for_flag NUMBER;
2352 l_prorating_always_flag VARCHAR2(1); -- currently unused
2353 l_target_version_type pa_budget_versions.version_type%type;
2354 l_source_version_type pa_budget_versions.version_type%type; /* Added for IPM */
2355
2356 /* For PC amounts */
2357 l_pc_currency_code pa_projects_all.project_currency_code%type;
2358 l_tot_quantity_pc_pfc NUMBER;
2359 l_tot_raw_cost_pc_pfc NUMBER;
2360 l_tot_brdn_cost_pc_pfc NUMBER;
2361 l_tot_revenue_pc_pfc NUMBER;
2362
2363 l_act_quantity_pc_pfc NUMBER;
2364
2365 /*For workplan actuals*/
2366 lx_act_quantity NUMBER;
2367 lx_act_txn_currency_code VARCHAR2(30);
2368 lx_act_txn_raw_cost NUMBER;
2369 lx_act_txn_brdn_cost NUMBER;
2370 lx_act_pc_raw_cost NUMBER;
2371 lx_act_pc_brdn_cost NUMBER;
2372 lx_act_pfc_raw_cost NUMBER;
2373 lx_act_pfc_brdn_cost NUMBER;
2374
2375 l_etc_quantity_pc_pfc NUMBER;
2376
2377 /* For TC amounts */
2378 l_tot_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2379 l_tot_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
2380 l_tot_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2381 l_tot_brdn_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2382 l_tot_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2383 l_tot_quantity_sum NUMBER;
2384
2385 l_act_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2386 l_act_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
2387 l_act_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2388 l_act_brdn_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2389 l_act_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2390 l_act_quantity_sum NUMBER;
2391
2392 /* ForPFC amounts */
2393 l_pfc_currency_code pa_projects_all.project_currency_code%type;
2394 l_rev_gen_method VARCHAR2(3);
2395
2396
2397 /* For ETC amounts */
2398 l_etc_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
2399 l_etc_quantity_sum NUMBER;
2400
2401 l_currency_count_act_min_tot NUMBER;
2402 l_currency_prorate_act_flag VARCHAR2(1);
2403 l_exit_flag VARCHAR2(1) := 'N';
2404
2405 /*For PC_TC amounts*/
2406 l_tot_quantity_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
2407 l_tot_raw_cost_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
2408 l_tot_brdn_cost_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
2409 l_tot_revenue_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
2410 l_tot_quantity_pc_sum NUMBER;
2411 l_act_quantity_pc_sum NUMBER;
2412 l_etc_quantity_pc_tab PA_PLSQL_DATATYPES.NumTabTyp;
2413 l_etc_quantity_pc_sum NUMBER;
2414
2415 /*For average rates*/
2416 l_pc_pfc_rate_quantity NUMBER;
2417 l_pc_pfc_rate_raw_cost NUMBER;
2418 l_pc_pfc_rate_brdn_cost NUMBER;
2419 l_pc_pfc_rate_revenue NUMBER;
2420
2421 l_pc_rate_quantity NUMBER; -- currently not used
2422 l_pc_rate_raw_cost NUMBER;
2423 l_pc_rate_brdn_cost NUMBER;
2424 l_pc_rate_revenue NUMBER;
2425
2426 l_txn_rate_quantity NUMBER;
2427 l_txn_rate_raw_cost NUMBER;
2428 l_txn_rate_brdn_cost NUMBER;
2429 l_txn_rate_revenue NUMBER;
2430
2431 l_pc_pfc_raw_cost_rate NUMBER;
2432 l_pc_pfc_brdn_cost_rate NUMBER;
2433 l_pc_pfc_revenue_rate NUMBER;
2434
2435 l_txn_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2436 l_txn_brdn_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2437 l_txn_revenue_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2438 l_pc_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2439 l_pc_brdn_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2440 l_pc_revenue_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
2441 l_transaction_source_code VARCHAR2(30);
2442
2443 /*For txn currency conversion*/
2444 l_task_id pa_tasks.task_id%type;
2445 l_planning_start_date pa_resource_assignments.planning_start_date%type;
2446 lx_acc_rate_date DATE;
2447 lx_acct_rate_type VARCHAR2(50);
2448 lx_acct_exch_rate NUMBER;
2449 lx_acct_raw_cost NUMBER;
2450 lx_project_rate_type VARCHAR2(50);
2451 lx_project_rate_date DATE;
2452 lx_project_exch_rate NUMBER;
2453 lx_projfunc_cost_rate_type VARCHAR2(50);
2454 lx_projfunc_cost_rate_date DATE;
2455 lx_projfunc_cost_exch_rate NUMBER;
2456 l_projfunc_raw_cost NUMBER;
2457
2458 /* Status variable for GET_CURRENCY_AMOUNTS api */
2459 l_status Varchar2(100);
2460 g_project_name pa_projects_all.name%TYPE;
2461
2462 /* Variables for Performance Bug 4194849 */
2463 l_src_res_asg_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE;
2464 l_tgt_res_asg_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE;
2465 l_fp_cols_src_rec PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
2466 l_curr_task_id PA_TASKS.TASK_ID%TYPE;
2467 l_resource_list_member_id PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_MEMBER_ID%TYPE;
2468 l_cbs_element_id PA_RESOURCE_ASSIGNMENTS.CBS_ELEMENT_ID%TYPE; --bug#16791711
2469 l_etc_source_code PA_TASKS.GEN_ETC_SOURCE_CODE%TYPE;
2470
2471 /* This user-defined exception is used to skip processing of
2472 * a single task as we process all of the tasks in a loop. */
2473 continue_loop EXCEPTION;
2474 l_dummy NUMBER;
2475
2476 l_ins_index BINARY_INTEGER;
2477 l_ins_src_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2478 l_ins_tgt_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2479 l_ins_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2480 l_ins_etc_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
2481 l_ins_txn_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2482 l_ins_txn_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2483 l_ins_txn_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2484 l_ins_pc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2485 l_ins_pc_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2486 l_ins_pc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2487 l_ins_pfc_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2488 l_ins_pfc_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
2489 l_ins_pfc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
2490
2491 l_msg_count NUMBER;
2492 l_msg_data VARCHAR2(2000);
2493 l_data VARCHAR2(2000);
2494 l_msg_index_out NUMBER:=0;
2495
2496 -- Variables added for Bug 5203622
2497 l_act_raw_cost_pc_pfc NUMBER;
2498 l_act_raw_cost_sum NUMBER;
2499 l_act_raw_cost_pc_sum NUMBER;
2500 l_tot_raw_cost_sum NUMBER;
2501 l_tot_revenue_sum NUMBER;
2502 l_tot_raw_cost_pc_sum NUMBER;
2503 l_tot_revenue_pc_sum NUMBER;
2504 l_other_rej_code PA_BUDGET_LINES.OTHER_REJECTION_CODE%TYPE;
2505 l_other_rej_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2506 l_ins_other_rej_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2507
2508 BEGIN
2509 IF p_pa_debug_mode = 'Y' THEN
2510 pa_debug.set_curr_function( p_function => 'GEN_ETC_REMAIN_BDGT_AMTS_BLK',
2511 p_debug_mode => p_pa_debug_mode);
2512 END IF;
2513
2514 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2515 X_MSG_COUNT := 0;
2516
2517 FOR main_loop IN 1..p_src_res_asg_id_tab.count LOOP
2518 BEGIN
2519
2520 /* Initialize Local Variables for Bug 4194849 */
2521 l_src_res_asg_id := p_src_res_asg_id_tab(main_loop);
2522 l_tgt_res_asg_id := p_tgt_res_asg_id_tab(main_loop);
2523 l_curr_task_id := p_task_id_tab(main_loop);
2524 l_resource_list_member_id := p_res_list_member_id_tab(main_loop);
2525 l_cbs_element_id := P_CBS_ELEMENT_ID_TAB(main_loop);--bug#16791711
2526 l_etc_source_code := p_etc_source_code_tab(main_loop);
2527
2528 IF l_etc_source_code = 'FINANCIAL_PLAN' THEN
2529 l_fp_cols_src_rec := p_fp_cols_src_rec_fp;
2530 ELSIF l_etc_source_code = 'WORKPLAN_RESOURCES' THEN
2531 l_fp_cols_src_rec := p_fp_cols_src_rec_wp;
2532 END IF;
2533
2534 /* Delete pl/sql tables for the current task being processed. */
2535 l_tot_currency_code_tab.delete;
2536 l_tot_quantity_tab.delete;
2537 l_tot_raw_cost_tab.delete;
2538 l_tot_brdn_cost_tab.delete;
2539 l_tot_revenue_tab.delete;
2540
2541 l_act_currency_code_tab.delete;
2542 l_act_quantity_tab.delete;
2543 l_act_raw_cost_tab.delete;
2544 l_act_brdn_cost_tab.delete;
2545 l_act_revenue_tab.delete;
2546
2547 l_tot_quantity_pc_tab.delete;
2548 l_tot_raw_cost_pc_tab.delete;
2549 l_tot_brdn_cost_pc_tab.delete;
2550 l_tot_revenue_pc_tab.delete;
2551 l_etc_quantity_pc_tab.delete;
2552
2553 l_txn_raw_cost_rate_tab.delete;
2554 l_txn_brdn_cost_rate_tab.delete;
2555 l_txn_revenue_rate_tab.delete;
2556 l_pc_raw_cost_rate_tab.delete;
2557 l_pc_brdn_cost_rate_tab.delete;
2558 l_pc_revenue_rate_tab.delete;
2559
2560 -- Bug 4231106: Before populating l_etc_quantity_tab, delete existing records
2561 l_etc_quantity_tab.delete;
2562
2563 /*Currency usage should be determined at the beginning.
2564 Default to use Transaction Currency (TC)
2565 If target version is not multi currency enabled, take Project Currency (PC)
2566 IF target version is multi currency enabled, the target planning resource is non
2567 rate based, and actuals currencies are not subset of the total currencies. We need
2568 to take PC amounts as quantity, sum up total quantity minus actual quantity,
2569 prorate this total PC ETC quantity across the planning currencies. Then convert
2570 them back from PC to TC (PC_TC).*/
2571
2572 IF nvl(l_tgt_res_asg_id,0) > 0 THEN
2573 SELECT rate_based_flag
2574 INTO l_rate_based_flag
2575 FROM pa_resource_assignments
2576 WHERE resource_assignment_id = l_tgt_res_asg_id;
2577 ELSE
2578 l_rate_based_flag:='N';
2579 END IF;
2580
2581 l_currency_flag := 'TC';
2582 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
2583 --l_rev_gen_method := PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(P_FP_COLS_TGT_REC.x_project_id);
2584
2585 IF (p_fp_cols_tgt_rec.x_version_type = 'REVENUE' and l_rev_gen_method = 'C') THEN
2586 l_currency_flag := 'PFC';
2587 ELSIF p_fp_cols_tgt_rec.X_PLAN_IN_MULTI_CURR_FLAG = 'N' THEN
2588 l_currency_flag := 'PC';
2589 ELSIF l_rate_based_flag = 'N' THEN
2590 SELECT COUNT(*) INTO l_currency_count_for_flag FROM (
2591 SELECT /*+ INDEX(act_tmp,PA_FP_FCST_GEN_TMP1_N1) INDEX(tot_tmp,PA_FP_CALC_AMT_TMP1_N1)*/
2592 DISTINCT act_tmp.txn_currency_code
2593 FROM PA_FP_FCST_GEN_TMP1 act_tmp,
2594 PA_FP_CALC_AMT_TMP1 tot_tmp
2595 WHERE act_tmp.project_element_id = tot_tmp.task_id
2596 AND act_tmp.res_list_member_id = tot_tmp.resource_list_member_id
2597 AND nvl(act_tmp.cbs_element_id, -1) = nvl(tot_tmp.cbs_element_id, -1) --bug#16791711
2598 AND tot_tmp.target_res_asg_id = l_tgt_res_asg_id
2599 AND data_type_code = DECODE(L_ETC_SOURCE_CODE,
2600 'WORKPLAN_RESOURCES', 'ETC_WP',
2601 'FINANCIAL_PLAN', 'ETC_FP')
2602 MINUS
2603 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
2604 DISTINCT txn_currency_code
2605 FROM PA_FP_CALC_AMT_TMP2
2606 WHERE target_res_asg_id = l_tgt_res_asg_id
2607 AND transaction_source_code = l_etc_source_code
2608 ) WHERE rownum = 1;
2609
2610 IF l_currency_count_for_flag > 0 THEN
2611 l_currency_flag := 'PC_TC';
2612 END IF;
2613 END IF;
2614
2615 /**************BY THIS TIME, WE DECIDED TO USE EITHER PC,TC,PC_TC or PFC**********/
2616
2617 -- Get Source version tpe
2618 IF l_etc_source_code = 'FINANCIAL_PLAN' THEN
2619 l_source_version_type := p_fp_cols_src_rec_fp.x_version_type;
2620 ELSE -- l_etc_source_code = 'WORKPLAN_RESOURCES'
2621 l_source_version_type := p_fp_cols_src_rec_wp.x_version_type;
2622 END IF;
2623
2624 l_target_version_type := p_fp_cols_tgt_rec.x_version_type;
2625 l_pc_currency_code := p_fp_cols_tgt_rec.x_project_currency_code;
2626 l_pfc_currency_code := p_fp_cols_tgt_rec.x_projfunc_currency_code;
2627 IF l_currency_flag = 'PC' OR l_currency_flag = 'PFC' THEN
2628 /* No matter ETC source is 'FINANCIAL_PLAN' or 'WORKPLAN_RESOURCES', always get
2629 total plan amounts in PC or PFC from financial data model.*/
2630 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
2631 NVL(SUM(NVL(total_plan_quantity,0)),0),
2632 NVL(SUM(NVL(
2633 DECODE(l_currency_flag, 'PC', total_pc_raw_cost,
2634 'PFC', total_pfc_raw_cost),0)),0),
2635 NVL(SUM(NVL(
2636 DECODE(l_currency_flag, 'PC', total_pc_burdened_cost,
2637 'PFC', total_pfc_burdened_cost),0)),0),
2638 NVL(SUM(NVL(
2639 DECODE(l_currency_flag, 'PC', total_pc_revenue,
2640 'PFC', total_pfc_revenue),0)),0)
2641 INTO l_tot_quantity_pc_pfc,
2642 l_tot_raw_cost_pc_pfc,
2643 l_tot_brdn_cost_pc_pfc,
2644 l_tot_revenue_pc_pfc
2645 FROM PA_FP_CALC_AMT_TMP2
2646 WHERE resource_assignment_id = l_src_res_asg_id
2647 AND transaction_source_code = l_etc_source_code;
2648
2649 -- IPM Change:
2650 -- For non-rate-based target transactions,
2651 -- if the Source is a Cost and Revenue together version,
2652 -- then regardless of the Target version type:
2653 -- set target quantity to source raw cost if it exists, OR
2654 -- set target quantity to source revenue otherwise.
2655 -- This is done to handle source planning transactions that
2656 -- have only revenue amounts (without cost amounts).
2657 --
2658 -- For non-rate-based target transactions and other Source
2659 -- version types, set target quantity to source raw cost as before.
2660
2661 IF l_rate_based_flag = 'N' THEN
2662 IF l_source_version_type = 'ALL' THEN
2663 IF nvl(l_tot_raw_cost_pc_pfc,0) = 0 THEN
2664 l_tot_quantity_pc_pfc := l_tot_revenue_pc_pfc;
2665 ELSE
2666 l_tot_quantity_pc_pfc := l_tot_raw_cost_pc_pfc;
2667 END IF;
2668 ELSE
2669 l_tot_quantity_pc_pfc := l_tot_raw_cost_pc_pfc;
2670 END IF;
2671 END IF;
2672
2673 IF l_etc_source_code = 'FINANCIAL_PLAN' THEN
2674 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
2675 DECODE(l_currency_flag,
2676 'PC', NVL(SUM(DECODE(l_rate_based_flag,
2677 'Y', quantity,
2678 'N', NVL(prj_raw_cost,0))),0),
2679 'PFC', NVL(SUM(DECODE(l_rate_based_flag,
2680 'Y', quantity,
2681 'N', NVL(pou_raw_cost,0))),0)),
2682 DECODE(l_currency_flag, -- Added for Bug 5203622
2683 'PC', NVL(SUM(NVL(prj_raw_cost,0)),0),
2684 'PFC', NVL(SUM(NVL(pou_raw_cost,0)),0))
2685 INTO l_act_quantity_pc_pfc,
2686 l_act_raw_cost_pc_pfc -- Added for Bug 5203622
2687 FROM PA_FP_FCST_GEN_TMP1
2688 WHERE project_element_id = l_curr_task_id
2689 AND res_list_member_id = l_resource_list_member_id
2690 AND nvl(cbs_element_id,-1) = nvl(l_cbs_element_id,-1)--bug#16791711
2691 AND data_type_code = 'ETC_FP';
2692
2693 ELSIF l_etc_source_code = 'WORKPLAN_RESOURCES' THEN
2694 /*Bug fix for 3973511
2695 Workplan side only stores amounts in one currency for each planning
2696 resource. Instead of relying on pa_progress_utils.get_actuals_for_task
2697 to get actuals data, we query directly to pa_budget_lines to get actual
2698 data from source workplan budget version */
2699 IF P_PA_DEBUG_MODE = 'Y' THEN
2700 pa_fp_gen_amount_utils.fp_debug(
2701 p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
2702 'GET_WP_ACTUALS_FOR_RA',
2703 p_module_name => l_module_name,
2704 p_log_level => 5);
2705 END IF;
2706 PA_FP_GEN_FCST_AMT_PUB1.GET_WP_ACTUALS_FOR_RA
2707 (P_FP_COLS_SRC_REC => l_fp_cols_src_rec,
2708 P_FP_COLS_TGT_REC => p_fp_cols_tgt_rec,
2709 P_SRC_RES_ASG_ID => l_src_res_asg_id,
2710 P_TASK_ID => l_curr_task_id,
2711 P_RES_LIST_MEM_ID => l_resource_list_member_id,
2712 P_ACTUALS_THRU_DATE => p_actuals_thru_date,
2713 X_ACT_QUANTITY => lx_act_quantity,
2714 X_ACT_TXN_CURRENCY_CODE => lx_act_txn_currency_code,
2715 X_ACT_TXN_RAW_COST => lx_act_txn_raw_cost,
2716 X_ACT_TXN_BRDN_COST => lx_act_txn_brdn_cost,
2717 X_ACT_PC_RAW_COST => lx_act_pc_raw_cost,
2718 X_ACT_PC_BRDN_COST => lx_act_pc_brdn_cost,
2719 X_ACT_PFC_RAW_COST => lx_act_pfc_raw_cost,
2720 X_ACT_PFC_BRDN_COST => lx_act_pfc_brdn_cost,
2721 X_RETURN_STATUS => x_return_status,
2722 X_MSG_COUNT => x_msg_count,
2723 X_MSG_DATA => x_msg_data );
2724 IF P_PA_DEBUG_MODE = 'Y' THEN
2725 pa_fp_gen_amount_utils.fp_debug(
2726 p_msg => 'After calling PA_FP_GEN_FCST_AMT_PUB1.'||
2727 'GET_WP_ACTUALS_FOR_RA in remain_bdgt:'||x_return_status,
2728 p_module_name => l_module_name,
2729 p_log_level => 5);
2730 END IF;
2731 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2732 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2733 END IF;
2734
2735 IF l_rate_based_flag = 'Y' THEN
2736 l_act_quantity_pc_pfc := lx_act_quantity;
2737 l_act_raw_cost_pc_pfc := lx_act_txn_raw_cost; -- Added for Bug 5203622
2738 ELSE
2739 IF l_currency_flag = 'PC' THEN
2740 l_act_quantity_pc_pfc := lx_act_pc_raw_cost;
2741 l_act_raw_cost_pc_pfc := lx_act_pc_raw_cost; -- Added for Bug 5203622
2742 ELSIF l_currency_flag = 'PFC' THEN
2743 l_act_quantity_pc_pfc := lx_act_pfc_raw_cost;
2744 l_act_raw_cost_pc_pfc := lx_act_pfc_raw_cost; -- Added for Bug 5203622
2745 END IF;
2746 END IF;
2747 END IF;
2748
2749 /* Get total ETC quantity */
2750 l_etc_quantity_pc_pfc := l_tot_quantity_pc_pfc - l_act_quantity_pc_pfc;
2751 -- ER 5726773: Instead of directly checking if (ETC <= 0), let the
2752 -- plan_etc_signs_match function decide if ETC should be generated.
2753 IF NOT pa_fp_fcst_gen_amt_utils.PLAN_ETC_SIGNS_MATCH
2754 (l_tot_quantity_pc_pfc, l_etc_quantity_pc_pfc) THEN
2755 /* only need to spread commitment and actual data*/
2756 RAISE continue_loop;
2757 END IF;
2758
2759 -- Bug 5203622: Added OTHER REJECTION CODE logic.
2760 l_other_rej_code := null;
2761 IF l_rate_based_flag = 'N' AND
2762 l_source_version_type = 'ALL' AND
2763 l_target_version_type = 'ALL' AND
2764 nvl(l_tot_raw_cost_pc_pfc,0) = 0 AND
2765 nvl(l_tot_revenue_pc_pfc,0) <> 0 AND
2766 nvl(l_act_raw_cost_pc_pfc,0) <> 0 THEN
2767 l_other_rej_code := 'PA_FP_ETC_REV_FIELD_ERR';
2768 END IF;
2769
2770 /* hr_utility.trace('project currency:'||l_ppc_currency_code);
2771 hr_utility.trace('etc qty '||l_etc_quantity_pc );*/
2772
2773 /*When not taking periodic rates, we need to calculate out the average
2774 rates from the source resource assignments that are mapped to the current
2775 target resource assignmentInsert the single PC record for total ETC.*/
2776 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
2777 NVL(SUM(NVL(total_plan_quantity,0)),0),
2778 DECODE(l_currency_flag,
2779 'PC', NVL(SUM(NVL(total_pc_raw_cost,0)),0),
2780 'PFC', NVL(SUM(NVL(total_pfc_raw_cost,0)),0)),
2781 DECODE(l_currency_flag,
2782 'PC', NVL(SUM(NVL(total_pc_burdened_cost,0)),0),
2783 'PFC', NVL(SUM(NVL(total_pfc_burdened_cost,0)),0)),
2784 DECODE(l_currency_flag,
2785 'PC', NVL(SUM(NVL(total_pc_revenue,0)),0),
2786 'PFC', NVL(SUM(NVL(total_pfc_revenue,0)),0))
2787 INTO l_pc_pfc_rate_quantity,
2788 l_pc_pfc_rate_raw_cost,
2789 l_pc_pfc_rate_brdn_cost,
2790 l_pc_pfc_rate_revenue
2791 FROM pa_fp_calc_amt_tmp2
2792 WHERE resource_assignment_id = l_src_res_asg_id
2793 AND transaction_source_code in ('FINANCIAL_PLAN',
2794 'WORKPLAN_RESOURCES');
2795
2796 -- IPM Change:
2797 -- For non-rate-based target transactions,
2798 -- if the Source is a Cost and Revenue together version,
2799 -- then regardless of the Target version type:
2800 -- set rate quantity to rate raw cost if it exists, OR
2801 -- set rate quantity to rate revenue otherwise.
2802 -- This is done to handle source planning transactions that
2803 -- have only revenue amounts (without cost amounts).
2804 --
2805 -- For non-rate-based target transactions and other Source
2806 -- version types, set rate quantity to rate raw cost as before.
2807
2808 IF l_rate_based_flag = 'N' THEN
2809 IF l_source_version_type = 'ALL' THEN
2810 IF nvl(l_pc_pfc_rate_raw_cost,0) = 0 THEN
2811 l_pc_pfc_rate_quantity := l_pc_pfc_rate_revenue;
2812 ELSE
2813 l_pc_pfc_rate_quantity := l_pc_pfc_rate_raw_cost;
2814 END IF;
2815 ELSE
2816 l_pc_pfc_rate_quantity := l_pc_pfc_rate_raw_cost;
2817 END IF;
2818 END IF;
2819
2820 IF l_pc_pfc_rate_quantity <> 0 THEN
2821 l_pc_pfc_raw_cost_rate := l_pc_pfc_rate_raw_cost / l_pc_pfc_rate_quantity;
2822 l_pc_pfc_brdn_cost_rate := l_pc_pfc_rate_brdn_cost / l_pc_pfc_rate_quantity;
2823 l_pc_pfc_revenue_rate := l_pc_pfc_rate_revenue / l_pc_pfc_rate_quantity;
2824 ELSE
2825 l_pc_pfc_raw_cost_rate := NULL;
2826 l_pc_pfc_brdn_cost_rate := NULL;
2827 l_pc_pfc_revenue_rate := NULL;
2828 END IF;
2829
2830 l_ins_index := l_ins_src_res_asg_id_tab.count + 1;
2831 l_ins_src_res_asg_id_tab(l_ins_index) := l_src_res_asg_id;
2832 l_ins_tgt_res_asg_id_tab(l_ins_index) := l_tgt_res_asg_id;
2833 l_ins_etc_quantity_tab(l_ins_index) := l_etc_quantity_pc_pfc;
2834 l_ins_txn_raw_cost_tab(l_ins_index) :=
2835 l_etc_quantity_pc_pfc * l_pc_pfc_raw_cost_rate;
2836 l_ins_txn_burdened_cost_tab(l_ins_index) :=
2837 l_etc_quantity_pc_pfc * l_pc_pfc_brdn_cost_rate;
2838 l_ins_txn_revenue_tab(l_ins_index) :=
2839 l_etc_quantity_pc_pfc * l_pc_pfc_revenue_rate;
2840 -- Added for Bug 5203622
2841 l_ins_other_rej_code_tab(l_ins_index) := l_other_rej_code;
2842
2843 IF l_currency_flag = 'PC' THEN
2844 l_ins_currency_code_tab(l_ins_index) := l_pc_currency_code;
2845 l_ins_pc_raw_cost_tab(l_ins_index) :=
2846 l_etc_quantity_pc_pfc * l_pc_pfc_raw_cost_rate;
2847 l_ins_pc_burdened_cost_tab(l_ins_index) :=
2848 l_etc_quantity_pc_pfc * l_pc_pfc_brdn_cost_rate;
2849 l_ins_pc_revenue_tab(l_ins_index) :=
2850 l_etc_quantity_pc_pfc * l_pc_pfc_revenue_rate;
2851 l_ins_pfc_raw_cost_tab(l_ins_index) := NULL;
2852 l_ins_pfc_burdened_cost_tab(l_ins_index) := NULL;
2853 l_ins_pfc_revenue_tab(l_ins_index) := NULL;
2854 ELSIF l_currency_flag = 'PFC' THEN
2855 l_ins_currency_code_tab(l_ins_index) := l_pfc_currency_code;
2856 l_ins_pc_raw_cost_tab(l_ins_index) := NULL;
2857 l_ins_pc_burdened_cost_tab(l_ins_index) := NULL;
2858 l_ins_pc_revenue_tab(l_ins_index) := NULL;
2859 l_ins_pfc_raw_cost_tab(l_ins_index) :=
2860 l_etc_quantity_pc_pfc * l_pc_pfc_raw_cost_rate;
2861 l_ins_pfc_burdened_cost_tab(l_ins_index) :=
2862 l_etc_quantity_pc_pfc * l_pc_pfc_brdn_cost_rate;
2863 l_ins_pfc_revenue_tab(l_ins_index) :=
2864 l_etc_quantity_pc_pfc * l_pc_pfc_revenue_rate;
2865 ELSE
2866 l_ins_currency_code_tab(l_ins_index) := NULL;
2867 l_ins_pc_raw_cost_tab(l_ins_index) := NULL;
2868 l_ins_pc_burdened_cost_tab(l_ins_index) := NULL;
2869 l_ins_pc_revenue_tab(l_ins_index) := NULL;
2870 l_ins_pfc_raw_cost_tab(l_ins_index) := NULL;
2871 l_ins_pfc_burdened_cost_tab(l_ins_index) := NULL;
2872 l_ins_pfc_revenue_tab(l_ins_index) := NULL;
2873 END IF;
2874
2875 /**************BY THIS TIME, WE HAVE ALL ETC DATA FOR PC or PFC*********/
2876
2877 ELSIF l_currency_flag = 'TC' THEN
2878 /* No matter ETC source is 'FINANCIAL_PLAN' or 'WORKPLAN_RESOURCES', always
2879 get total plan amounts by txn currency from financial data model.*/
2880 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
2881 txn_currency_code,
2882 SUM(NVL(total_plan_quantity,0)),
2883 SUM(NVL(total_txn_raw_cost,0)),
2884 SUM(NVL(total_txn_burdened_cost,0)),
2885 SUM(NVL(total_txn_revenue,0))
2886 BULK COLLECT INTO
2887 l_tot_currency_code_tab,
2888 l_tot_quantity_tab,
2889 l_tot_raw_cost_tab,
2890 l_tot_brdn_cost_tab,
2891 l_tot_revenue_tab
2892 FROM PA_FP_CALC_AMT_TMP2
2893 WHERE resource_assignment_id = l_src_res_asg_id
2894 AND transaction_source_code = l_etc_source_code
2895 GROUP BY txn_currency_code;
2896
2897 IF l_tot_currency_code_tab.count = 0 THEN
2898 RAISE continue_loop;
2899 END IF;
2900
2901 -- IPM Change:
2902 -- For non-rate-based target transactions,
2903 -- if the Source is a Cost and Revenue together version,
2904 -- then regardless of the Target version type:
2905 -- set target quantity to source raw cost if it exists, OR
2906 -- set target quantity to source revenue otherwise.
2907 -- This is done to handle source planning transactions that
2908 -- have only revenue amounts (without cost amounts).
2909 --
2910 -- For non-rate-based target transactions and other Source
2911 -- version types, set target quantity to source raw cost as before.
2912
2913 IF l_rate_based_flag = 'N' THEN
2914 IF l_source_version_type = 'ALL' THEN
2915 -- Set total quantity for each Currency depending on whether
2916 -- source raw cost exists (i.e. if it is a revenue-only txn).
2917 FOR i IN 1..l_tot_quantity_tab.count LOOP
2918 IF nvl(l_tot_raw_cost_tab(i),0) = 0 THEN
2919 l_tot_quantity_tab(i) := l_tot_revenue_tab(i);
2920 ELSE
2921 l_tot_quantity_tab(i) := l_tot_raw_cost_tab(i);
2922 END IF;
2923 END LOOP;
2924 ELSE
2925 l_tot_quantity_tab := l_tot_raw_cost_tab;
2926 END IF;
2927 END IF;
2928
2929 /* Bug 4085203
2930 The total plan amounts should be summed up irrespective of rate based
2931 or non rate based. Because for non rate based resource, we used the
2932 sum value when plan and actuals are using same one currency. When
2933 plan and actuals are using more than one currencies, the flow will
2934 not use the sum amounts.*/
2935 -- Added l_tot_raw_cost_sum, l_tot_revenue_sum for Bug 5203622
2936 l_tot_quantity_sum := 0;
2937 l_tot_raw_cost_sum := 0;
2938 l_tot_revenue_sum := 0;
2939 FOR i IN 1..l_tot_quantity_tab.count LOOP
2940 l_tot_quantity_sum := l_tot_quantity_sum + NVL(l_tot_quantity_tab(i),0);
2941 l_tot_raw_cost_sum := l_tot_raw_cost_sum + NVL(l_tot_raw_cost_tab(i),0);
2942 l_tot_revenue_sum := l_tot_revenue_sum + NVL(l_tot_revenue_tab(i),0);
2943 END LOOP;
2944
2945 IF l_etc_source_code = 'FINANCIAL_PLAN' THEN
2946 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
2947 txn_currency_code,
2948 SUM(NVL(quantity,0)),
2949 SUM(NVL(txn_raw_cost,0)),
2950 SUM(NVL(txn_brdn_cost,0)),
2951 SUM(NVL(txn_revenue,0))
2952 BULK COLLECT INTO
2953 l_act_currency_code_tab,
2954 l_act_quantity_tab,
2955 l_act_raw_cost_tab,
2956 l_act_brdn_cost_tab,
2957 l_act_revenue_tab
2958 FROM PA_FP_FCST_GEN_TMP1
2959 WHERE project_element_id = l_curr_task_id
2960 AND res_list_member_id = l_resource_list_member_id
2961 AND nvl(cbs_element_id,-1) = nvl(l_cbs_element_id,-1)--bug#16791711
2962 AND data_type_code = 'ETC_FP'
2963 GROUP BY txn_currency_code;
2964
2965 IF l_rate_based_flag = 'N' THEN
2966 l_act_quantity_tab := l_act_raw_cost_tab;
2967 END IF;
2968
2969 /* Bug 4085203
2970 The total actual amounts should be summed up irrespective of rate based
2971 or non rate based. Because for non rate based resource, we used the
2972 sum value when plan and actuals are using same one currency. When
2973 plan and actuals are using more than one currencies, the flow will
2974 not use the sum amounts.*/
2975 l_act_quantity_sum := 0;
2976 l_act_raw_cost_sum := 0; -- Added for Bug 5203622
2977 FOR i IN 1..l_act_quantity_tab.count LOOP
2978 l_act_quantity_sum := l_act_quantity_sum + l_act_quantity_tab(i);
2979 -- Added for Bug 5203622
2980 l_act_raw_cost_sum := l_act_raw_cost_sum + l_act_raw_cost_tab(i);
2981 END LOOP;
2982
2983 ELSIF l_etc_source_code = 'WORKPLAN_RESOURCES' THEN
2984 /*Bug fix for 3973511
2985 Workplan side only stores amounts in one currency for each planning
2986 resource. Instead of relying on pa_progress_utils.get_actuals_for_task
2987 to get actuals data, we query directly to pa_budget_lines to get actual
2988 data from source workplan budget version */
2989 IF P_PA_DEBUG_MODE = 'Y' THEN
2990 pa_fp_gen_amount_utils.fp_debug(
2991 p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
2992 'GET_WP_ACTUALS_FOR_RA',
2993 p_module_name => l_module_name,
2994 p_log_level => 5);
2995 END IF;
2996 PA_FP_GEN_FCST_AMT_PUB1.GET_WP_ACTUALS_FOR_RA
2997 (P_FP_COLS_SRC_REC => l_fp_cols_src_rec,
2998 P_FP_COLS_TGT_REC => p_fp_cols_tgt_rec,
2999 P_SRC_RES_ASG_ID => l_src_res_asg_id,
3000 P_TASK_ID => l_curr_task_id,
3001 P_RES_LIST_MEM_ID => l_resource_list_member_id,
3002 P_ACTUALS_THRU_DATE => p_actuals_thru_date,
3003 X_ACT_QUANTITY => lx_act_quantity,
3004 X_ACT_TXN_CURRENCY_CODE => lx_act_txn_currency_code,
3005 X_ACT_TXN_RAW_COST => lx_act_txn_raw_cost,
3006 X_ACT_TXN_BRDN_COST => lx_act_txn_brdn_cost,
3007 X_ACT_PC_RAW_COST => lx_act_pc_raw_cost,
3008 X_ACT_PC_BRDN_COST => lx_act_pc_brdn_cost,
3009 X_ACT_PFC_RAW_COST => lx_act_pfc_raw_cost,
3010 X_ACT_PFC_BRDN_COST => lx_act_pfc_brdn_cost,
3011 X_RETURN_STATUS => x_return_status,
3012 X_MSG_COUNT => x_msg_count,
3013 X_MSG_DATA => x_msg_data );
3014 IF P_PA_DEBUG_MODE = 'Y' THEN
3015 pa_fp_gen_amount_utils.fp_debug(
3016 p_msg => 'After calling PA_FP_GEN_FCST_AMT_PUB1.'||
3017 'GET_WP_ACTUALS_FOR_RA in remain_bdgt:'||x_return_status,
3018 p_module_name => l_module_name,
3019 p_log_level => 5);
3020 END IF;
3021 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3022 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3023 END IF;
3024
3025 l_act_currency_code_tab(1) := lx_act_txn_currency_code;
3026 l_act_quantity_tab(1) := lx_act_quantity;
3027 l_act_raw_cost_tab(1) := lx_act_txn_raw_cost;
3028 l_act_brdn_cost_tab(1):= lx_act_txn_brdn_cost;
3029 l_act_revenue_tab(1) := 0;
3030
3031 IF l_rate_based_flag = 'N' THEN
3032 l_act_quantity_tab := l_act_raw_cost_tab;
3033 END IF;
3034
3035 l_act_quantity_sum := l_act_quantity_tab(1);
3036 END IF;
3037
3038
3039 /* Check the relationship between total currency codes and actual currency
3040 codes. If actual currency codes are subset of total currency codes, then,
3041 take currency based approach; otherwise, take prorating based approach.
3042 'C' means take currency based calculation
3043 'P' means take prorating based calculation */
3044
3045 SELECT COUNT(*)
3046 INTO l_currency_count_act_min_tot
3047 FROM (
3048 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
3049 DISTINCT txn_currency_code
3050 FROM PA_FP_FCST_GEN_TMP1
3051 WHERE project_element_id = l_curr_task_id
3052 AND res_list_member_id = l_resource_list_member_id
3053 AND nvl(cbs_element_id,-1) = nvl(l_cbs_element_id,-1)--bug#16791711
3054 AND data_type_code = DECODE(L_ETC_SOURCE_CODE,
3055 'WORKPLAN_RESOURCES', 'ETC_WP',
3056 'FINANCIAL_PLAN', 'ETC_FP')
3057 MINUS
3058 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
3059 DISTINCT txn_currency_code
3060 FROM PA_FP_CALC_AMT_TMP2
3061 WHERE resource_assignment_id = l_src_res_asg_id
3062 AND transaction_source_code = l_etc_source_code
3063 ) WHERE rownum = 1;
3064
3065 IF l_currency_count_act_min_tot = 0 THEN
3066 l_currency_prorate_act_flag := 'C';
3067 ELSE
3068 l_currency_prorate_act_flag := 'P';
3069 END IF;
3070
3071 /*Bug fix: 4085203: If there only exists one plan currency,
3072 one actual currency and they are same, no matter it's rate
3073 based resource or non rate based resource, if etc quantity is
3074 calculated as less or equal to zero, then don't generate the ETC.*/
3075 IF l_act_currency_code_tab.count = 1 AND l_tot_currency_code_tab.count = 1 THEN
3076 l_etc_quantity_sum := l_tot_quantity_sum - l_act_quantity_sum;
3077 -- ER 5726773: Instead of directly checking if (ETC <= 0), let the
3078 -- plan_etc_signs_match function decide if ETC should be generated.
3079 IF NOT pa_fp_fcst_gen_amt_utils.PLAN_ETC_SIGNS_MATCH
3080 (l_tot_quantity_sum, l_etc_quantity_sum) THEN
3081 RAISE continue_loop;
3082 ELSE
3083 l_etc_quantity_tab(1) := l_etc_quantity_sum;
3084
3085 -- Bug 5203622: Store OTHER rejection code in the
3086 -- txn_currency_code column of pa_fp_calc_amt_tmp2.
3087 l_other_rej_code_tab(1) := null;
3088 IF l_rate_based_flag = 'N' AND
3089 l_source_version_type = 'ALL' AND
3090 l_target_version_type = 'ALL' AND
3091 nvl(l_tot_raw_cost_sum,0) = 0 AND
3092 nvl(l_tot_revenue_sum,0) <> 0 AND
3093 nvl(l_act_raw_cost_sum,0) <> 0 THEN
3094 l_other_rej_code_tab(1) := 'PA_FP_ETC_REV_FIELD_ERR';
3095 END IF;
3096 END IF;
3097 ELSE
3098 l_exit_flag := 'N';
3099 IF l_currency_prorate_act_flag = 'C' THEN
3100 FOR i IN 1..l_tot_currency_code_tab.count LOOP
3101 IF l_exit_flag = 'Y' THEN
3102 EXIT;
3103 END IF;
3104 l_etc_quantity_tab(i) := l_tot_quantity_tab(i);
3105 l_other_rej_code_tab(i) := null; -- Added for Bug 5203622
3106
3107 FOR j IN 1..l_act_currency_code_tab.count LOOP
3108 IF l_tot_currency_code_tab(i) = l_act_currency_code_tab(j) THEN
3109 l_etc_quantity_tab(i) := l_etc_quantity_tab(i) - l_act_quantity_tab(j);
3110
3111 -- Bug 5203622: Added OTHER REJECTION CODE logic.
3112 IF l_rate_based_flag = 'N' AND
3113 l_source_version_type = 'ALL' AND
3114 l_target_version_type = 'ALL' AND
3115 nvl(l_tot_raw_cost_tab(i),0) = 0 AND
3116 nvl(l_tot_revenue_tab(i),0) <> 0 AND
3117 nvl(l_act_raw_cost_tab(j),0) <> 0 THEN
3118 l_other_rej_code_tab(i) := 'PA_FP_ETC_REV_FIELD_ERR';
3119 END IF;
3120
3121 -- ER 5726773: Instead of directly checking if (ETC <= 0), let the
3122 -- plan_etc_signs_match function decide if ETC should be prorated.
3123 IF NOT pa_fp_fcst_gen_amt_utils.PLAN_ETC_SIGNS_MATCH
3124 (l_tot_quantity_tab(i), l_etc_quantity_tab(i)) THEN
3125 l_currency_prorate_act_flag := 'P';
3126 l_etc_quantity_tab.delete;
3127 l_other_rej_code_tab.delete; -- Added for Bug 5203622
3128 l_exit_flag := 'Y';
3129 EXIT;
3130 END IF;
3131 END IF;
3132 END LOOP;
3133 END LOOP;
3134 END IF;
3135
3136 IF l_currency_prorate_act_flag = 'P' THEN
3137 IF l_rate_based_flag = 'N' THEN
3138 l_currency_flag := 'PC_TC';
3139 ELSIF l_rate_based_flag = 'Y' THEN
3140 l_etc_quantity_sum := l_tot_quantity_sum - l_act_quantity_sum;
3141 -- ER 5726773: Instead of directly checking if (ETC <= 0), let the
3142 -- plan_etc_signs_match function decide if ETC should be generated.
3143 IF NOT pa_fp_fcst_gen_amt_utils.PLAN_ETC_SIGNS_MATCH
3144 (l_tot_quantity_sum, l_etc_quantity_sum) THEN
3145 /* no non-commitment ETC available,
3146 only actual and commitment amounts need to be spreaded */
3147 RAISE continue_loop;
3148 END IF;
3149
3150 FOR i IN 1..l_tot_currency_code_tab.count LOOP
3151 IF l_tot_quantity_sum <> 0 THEN
3152 l_etc_quantity_tab(i) := l_etc_quantity_sum
3153 * (l_tot_quantity_tab (i) / l_tot_quantity_sum) ;
3154 IF NOT l_other_rej_code_tab.EXISTS(i) then --Bug #15864175
3155 l_other_rej_code_tab(i) := NULL;
3156 END if;
3157 ELSE
3158 l_etc_quantity_tab(i) := NULL;
3159 IF NOT l_other_rej_code_tab.EXISTS(i) then --Bug #15864175
3160 l_other_rej_code_tab(i) := NULL;
3161 END if;
3162 END IF;
3163 /* hr_utility.trace(i||'th');
3164 hr_utility.trace('etc qty '||l_etc_qty );
3165 hr_utility.trace('etc curr'||l_ETC_CURRENCY_CODE );
3166 hr_utility.trace('etc rc '||l_etc_txn_raw_cost );
3167 hr_utility.trace('etc bc '||l_etc_txn_brdn_cost ); */
3168 END LOOP;
3169 END IF;
3170 END IF;
3171 END IF;
3172
3173 /*currency_flag may get changed to 'PC_TC', when actual currencies is subset of
3174 planning currencies, target resource is non_rate_based, but actual amount for
3175 one particular currency is less than plan amount. Then we need to revert from
3176 currency based approach to prorating based approach.For non_rate_based resource,
3177 prorating falls in to currency code of 'PC_TC'.*/
3178 IF l_currency_flag = 'TC' THEN
3179 /*When not taking periodic rates, we need to calculate out the average
3180 rates from the source resource assignments that are mapped to the current
3181 target resource assignment.*/
3182 FOR i IN 1..l_tot_currency_code_tab.count LOOP
3183 SELECT /*+ INDEX(pa_fp_calc_amt_tmp2,PA_FP_CALC_AMT_TMP2_N2)*/
3184 NVL(SUM(NVL(total_plan_quantity,0)),0),
3185 NVL(SUM(NVL(total_txn_raw_cost,0)),0),
3186 NVL(SUM(NVL(total_txn_burdened_cost,0)),0),
3187 NVL(SUM(NVL(total_txn_revenue,0)),0),
3188 NVL(SUM(NVL(total_pc_raw_cost,0)),0),
3189 NVL(SUM(NVL(total_pc_burdened_cost,0)),0),
3190 NVL(SUM(NVL(total_pc_revenue,0)),0)
3191 INTO l_txn_rate_quantity,
3192 l_txn_rate_raw_cost,
3193 l_txn_rate_brdn_cost,
3194 l_txn_rate_revenue,
3195 l_pc_rate_raw_cost,
3196 l_pc_rate_brdn_cost,
3197 l_pc_rate_revenue
3198 FROM pa_fp_calc_amt_tmp2
3199 WHERE resource_assignment_id = l_src_res_asg_id
3200 AND txn_currency_code = l_tot_currency_code_tab(i)
3201 AND transaction_source_code in ('FINANCIAL_PLAN',
3202 'WORKPLAN_RESOURCES');
3203
3204
3205 -- IPM Change:
3206 -- For non-rate-based target transactions,
3207 -- if the Source is a Cost and Revenue together version,
3208 -- then regardless of the Target version type:
3209 -- set rate quantity to rate raw cost if it exists, OR
3210 -- set rate quantity to rate revenue otherwise.
3211 -- This is done to handle source planning transactions that
3212 -- have only revenue amounts (without cost amounts).
3213 --
3214 -- For non-rate-based target transactions and other Source
3215 -- version types, set rate quantity to rate raw cost as before.
3216
3217 IF l_rate_based_flag = 'N' THEN
3218 IF l_source_version_type = 'ALL' THEN
3219 IF nvl(l_txn_rate_raw_cost,0) = 0 THEN
3220 l_txn_rate_quantity := l_txn_rate_revenue;
3221 ELSE
3222 l_txn_rate_quantity := l_txn_rate_raw_cost;
3223 END IF;
3224 ELSE
3225 l_txn_rate_quantity := l_txn_rate_raw_cost;
3226 END IF;
3227 END IF;
3228
3229 IF l_txn_rate_quantity <> 0 THEN
3230 l_txn_raw_cost_rate_tab(i) := l_txn_rate_raw_cost
3231 / l_txn_rate_quantity;
3232 l_txn_brdn_cost_rate_tab(i) := l_txn_rate_brdn_cost
3233 / l_txn_rate_quantity;
3234 l_txn_revenue_rate_tab(i) := l_txn_rate_revenue
3235 / l_txn_rate_quantity;
3236 l_pc_raw_cost_rate_tab(i) := l_pc_rate_raw_cost
3237 / l_txn_rate_quantity;
3238 l_pc_brdn_cost_rate_tab(i) := l_pc_rate_brdn_cost
3239 / l_txn_rate_quantity;
3240 l_pc_revenue_rate_tab(i) := l_pc_rate_revenue
3241 / l_txn_rate_quantity;
3242 ELSE
3243 l_txn_raw_cost_rate_tab(i) := NULL;
3244 l_txn_brdn_cost_rate_tab(i) := NULL;
3245 l_txn_revenue_rate_tab(i) := NULL;
3246 l_pc_raw_cost_rate_tab(i) := NULL;
3247 l_pc_brdn_cost_rate_tab(i) := NULL;
3248 l_pc_revenue_rate_tab(i) := NULL;
3249 END IF;
3250 END LOOP;
3251
3252 FOR i IN 1..l_etc_quantity_tab.count LOOP
3253 l_ins_index := l_ins_src_res_asg_id_tab.count + 1;
3254 l_ins_src_res_asg_id_tab(l_ins_index) := l_src_res_asg_id;
3255 l_ins_tgt_res_asg_id_tab(l_ins_index) := l_tgt_res_asg_id;
3256 l_ins_currency_code_tab(l_ins_index) := l_tot_currency_code_tab(i);
3257 l_ins_etc_quantity_tab(l_ins_index) := l_etc_quantity_tab(i);
3258 l_ins_txn_raw_cost_tab(l_ins_index) :=
3259 l_etc_quantity_tab(i) * l_txn_raw_cost_rate_tab(i);
3260 l_ins_txn_burdened_cost_tab(l_ins_index) :=
3261 l_etc_quantity_tab(i) * l_txn_brdn_cost_rate_tab(i);
3262 l_ins_txn_revenue_tab(l_ins_index) :=
3263 l_etc_quantity_tab(i) * l_txn_revenue_rate_tab(i);
3264 l_ins_pc_raw_cost_tab(l_ins_index) :=
3265 l_etc_quantity_tab(i) * l_pc_raw_cost_rate_tab(i);
3266 l_ins_pc_burdened_cost_tab(l_ins_index) :=
3267 l_etc_quantity_tab(i) * l_pc_brdn_cost_rate_tab(i);
3268 l_ins_pc_revenue_tab(l_ins_index) :=
3269 l_etc_quantity_tab(i) * l_pc_revenue_rate_tab(i);
3270 l_ins_pfc_raw_cost_tab(l_ins_index) := NULL;
3271 l_ins_pfc_burdened_cost_tab(l_ins_index) := NULL;
3272 l_ins_pfc_revenue_tab(l_ins_index) := NULL;
3273 -- Added for Bug 5203622
3274 l_ins_other_rej_code_tab(l_ins_index) := l_other_rej_code_tab(i);
3275 END LOOP;
3276
3277 END IF;
3278 END IF;
3279 /**************NOW WE HAVE ALL ETC DATA IN TC*************/
3280
3281 IF l_currency_flag = 'PC_TC' THEN
3282 /*Take PC for calculation, then convert back to TC.
3283 This only happens for non rate based resources*/
3284
3285 /*No matter ETC source is 'FINANCIAL_PLAN' or 'WORKPLAN_RESOURCES',
3286 always get total plan amounts in PC from financial data model*/
3287 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
3288 txn_currency_code,
3289 SUM(NVL(total_plan_quantity,0)),
3290 SUM(NVL(total_pc_raw_cost,0)),
3291 SUM(NVL(total_pc_burdened_cost,0)),
3292 SUM(NVL(total_pc_revenue,0))
3293 BULK COLLECT INTO
3294 l_tot_currency_code_tab,
3295 l_tot_quantity_pc_tab,
3296 l_tot_raw_cost_pc_tab,
3297 l_tot_brdn_cost_pc_tab,
3298 l_tot_revenue_pc_tab
3299 FROM PA_FP_CALC_AMT_TMP2
3300 WHERE resource_assignment_id = l_src_res_asg_id
3301 AND transaction_source_code = l_etc_source_code
3302 GROUP BY txn_currency_code;
3303
3304 -- Bug 4244609: Previously, we assigned raw cost or revenue to quantity
3305 -- based on Target version type. Now, we always set quantity = raw cost
3306 -- for non-rate-based resources.
3307
3308 -- IPM Change:
3309 -- For non-rate-based target transactions,
3310 -- if the Source is a Cost and Revenue together version,
3311 -- then regardless of the Target version type:
3312 -- set target quantity to source raw cost if it exists, OR
3313 -- set target quantity to source revenue otherwise.
3314 -- This is done to handle source planning transactions that
3315 -- have only revenue amounts (without cost amounts).
3316 --
3317 -- For non-rate-based target transactions and other Source
3318 -- version types, set target quantity to source raw cost as before.
3319
3320 IF l_source_version_type = 'ALL' THEN
3321 -- Set total quantity for each Currency depending on whether
3322 -- source raw cost exists (i.e. if it is a revenue-only txn).
3323 FOR i IN 1..l_tot_quantity_pc_tab.count LOOP
3324 IF nvl(l_tot_raw_cost_pc_tab(i),0) = 0 THEN
3325 l_tot_quantity_pc_tab(i) := l_tot_revenue_pc_tab(i);
3326 ELSE
3327 l_tot_quantity_pc_tab(i) := l_tot_raw_cost_pc_tab(i);
3328 END IF;
3329 END LOOP;
3330 ELSE
3331 l_tot_quantity_pc_tab := l_tot_raw_cost_pc_tab;
3332 END IF;
3333
3334 -- Added l_tot_raw_cost_pc_sum, l_tot_revenue_pc_sum for Bug 5203622
3335 l_tot_quantity_pc_sum := 0;
3336 l_tot_raw_cost_pc_sum := 0;
3337 l_tot_revenue_pc_sum := 0;
3338 FOR i IN 1..l_tot_quantity_pc_tab.count LOOP
3339 l_tot_quantity_pc_sum := l_tot_quantity_pc_sum + l_tot_quantity_pc_tab(i);
3340 l_tot_raw_cost_pc_sum := l_tot_raw_cost_pc_sum + l_tot_raw_cost_pc_tab(i);
3341 l_tot_revenue_pc_sum := l_tot_revenue_pc_sum + l_tot_revenue_pc_tab(i);
3342 END LOOP;
3343
3344 IF l_etc_source_code = 'FINANCIAL_PLAN' THEN
3345 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
3346 NVL(SUM( DECODE(l_rate_based_flag,
3347 'Y', NVL(quantity,0),
3348 'N', NVL(prj_raw_cost,0))),0),
3349 NVL(SUM(NVL(prj_raw_cost,0)),0)
3350 INTO l_act_quantity_pc_sum,
3351 l_act_raw_cost_pc_sum -- Added for Bug 5203622
3352 FROM PA_FP_FCST_GEN_TMP1
3353 WHERE project_element_id = l_curr_task_id
3354 AND res_list_member_id = l_resource_list_member_id
3355 AND nvl(cbs_element_id,-1) = nvl(l_cbs_element_id,-1)--bug#16791711
3356 AND data_type_code = 'ETC_FP';
3357
3358 ELSIF l_etc_source_code = 'WORKPLAN_RESOURCES' THEN
3359 /*Workplan side only stores amounts in one currency for each planning
3360 resource, so still rely on pa_progress_utils.get_actuals_for_task to
3361 get actuals data. This part needs to be revisted when workplan side is
3362 changed to support multi currencies.*/
3363 IF P_PA_DEBUG_MODE = 'Y' THEN
3364 pa_fp_gen_amount_utils.fp_debug(
3365 p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
3366 'GET_WP_ACTUALS_FOR_RA',
3367 p_module_name => l_module_name,
3368 p_log_level => 5);
3369 END IF;
3370 PA_FP_GEN_FCST_AMT_PUB1.GET_WP_ACTUALS_FOR_RA
3371 (P_FP_COLS_SRC_REC => l_fp_cols_src_rec,
3372 P_FP_COLS_TGT_REC => p_fp_cols_tgt_rec,
3373 P_SRC_RES_ASG_ID => l_src_res_asg_id,
3374 P_TASK_ID => l_curr_task_id,
3375 P_RES_LIST_MEM_ID => l_resource_list_member_id,
3376 P_ACTUALS_THRU_DATE => p_actuals_thru_date,
3377 X_ACT_QUANTITY => lx_act_quantity,
3378 X_ACT_TXN_CURRENCY_CODE => lx_act_txn_currency_code,
3379 X_ACT_TXN_RAW_COST => lx_act_txn_raw_cost,
3380 X_ACT_TXN_BRDN_COST => lx_act_txn_brdn_cost,
3381 X_ACT_PC_RAW_COST => lx_act_pc_raw_cost,
3382 X_ACT_PC_BRDN_COST => lx_act_pc_brdn_cost,
3383 X_ACT_PFC_RAW_COST => lx_act_pfc_raw_cost,
3384 X_ACT_PFC_BRDN_COST => lx_act_pfc_brdn_cost,
3385 X_RETURN_STATUS => x_return_status,
3386 X_MSG_COUNT => x_msg_count,
3387 X_MSG_DATA => x_msg_data );
3388 IF P_PA_DEBUG_MODE = 'Y' THEN
3389 pa_fp_gen_amount_utils.fp_debug(
3390 p_msg => 'After calling PA_FP_GEN_FCST_AMT_PUB1.'||
3391 'GET_WP_ACTUALS_FOR_RA in remain_bdgt:'||x_return_status,
3392 p_module_name => l_module_name,
3393 p_log_level => 5);
3394 END IF;
3395 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3396 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3397 END IF;
3398
3399 l_act_quantity_pc_sum := lx_act_pc_raw_cost;
3400 l_act_raw_cost_pc_sum := lx_act_pc_raw_cost; -- Added for Bug 5203622
3401 END IF;
3402
3403 /*Prorate total ETC quantity in PC based according to the transaction
3404 currency codes from the plan totals.*/
3405 /*Get total ETC quantity and Prorate ETC quantity*/
3406 l_etc_quantity_pc_sum := l_tot_quantity_pc_sum - l_act_quantity_pc_sum;
3407 -- ER 5726773: Instead of directly checking if (ETC <= 0), let the
3408 -- plan_etc_signs_match function decide if ETC should be generated.
3409 IF NOT pa_fp_fcst_gen_amt_utils.PLAN_ETC_SIGNS_MATCH
3410 (l_tot_quantity_pc_sum, l_etc_quantity_pc_sum) THEN
3411 /* only need to spread commitment data and actual data*/
3412 RAISE continue_loop;
3413 END IF;
3414
3415 -- Bug 5203622: Added OTHER REJECTION CODE logic.
3416 l_other_rej_code := null;
3417 IF l_rate_based_flag = 'N' AND
3418 l_source_version_type = 'ALL' AND
3419 l_target_version_type = 'ALL' AND
3420 nvl(l_tot_raw_cost_pc_sum,0) = 0 AND
3421 nvl(l_tot_revenue_pc_sum,0) <> 0 AND
3422 nvl(l_act_raw_cost_pc_sum,0) <> 0 THEN
3423 l_other_rej_code := 'PA_FP_ETC_REV_FIELD_ERR';
3424 END IF;
3425
3426 FOR i IN 1..l_tot_currency_code_tab.count LOOP
3427 IF NVL(l_tot_quantity_pc_sum,0) <> 0 THEN
3428 l_etc_quantity_pc_tab(i) := l_etc_quantity_pc_sum
3429 * (l_tot_quantity_pc_tab(i) / l_tot_quantity_pc_sum) ;
3430 ELSE
3431 l_etc_quantity_pc_tab(i) := NULL;
3432 --l_etc_quantity_pc_tab(i) := l_etc_quantity_pc_sum; -- ???
3433 END IF;
3434 -- Added for Bug 5203622
3435 l_other_rej_code_tab(i) := l_other_rej_code;
3436 END LOOP;
3437
3438 /* Convert PC into TC */
3439 FOR i IN 1..l_tot_currency_code_tab.count LOOP
3440 IF l_tot_currency_code_tab(i) = l_pc_currency_code THEN
3441 l_etc_quantity_tab(i) := l_etc_quantity_pc_tab(i);
3442 ELSE
3443 l_etc_quantity_tab(i) := NULL;
3444 BEGIN
3445 SELECT task_id,
3446 planning_start_date
3447 INTO l_task_id,
3448 l_planning_start_date
3449 FROM pa_resource_assignments
3450 WHERE resource_assignment_id = l_src_res_asg_id;
3451 EXCEPTION
3452 WHEN NO_DATA_FOUND THEN
3453 l_task_id := NULL;
3454 l_planning_start_date := NULL;
3455 END;
3456 IF P_PA_DEBUG_MODE = 'Y' THEN
3457 pa_fp_gen_amount_utils.fp_debug(
3458 p_msg => 'Before calling pa_multi_currency_txn.'||
3459 'get_currency_amounts in remain_bdgt',
3460 p_module_name => l_module_name,
3461 p_log_level => 5);
3462 END IF;
3463 -- Bug 4091344: Changed P_status parameter from x_return_status to
3464 -- local variable l_status. Afterwards, we check l_status and set
3465 -- x_return_status accordingly.
3466 pa_multi_currency_txn.get_currency_amounts (
3467 P_project_id => p_fp_cols_tgt_rec.x_project_id,
3468 P_exp_org_id => NULL,
3469 P_calling_module => 'WORKPLAN',
3470 P_task_id => l_task_id,
3471 P_EI_date => l_planning_start_date,
3472 P_denom_raw_cost => l_etc_quantity_pc_tab(i),
3473 P_denom_curr_code => l_pc_currency_code,
3474 P_acct_curr_code => l_pc_currency_code,
3475 P_accounted_flag => 'N',
3476 P_acct_rate_date => lx_acc_rate_date,
3477 P_acct_rate_type => lx_acct_rate_type,
3478 P_acct_exch_rate => lx_acct_exch_rate,
3479 P_acct_raw_cost => lx_acct_raw_cost,
3480 P_project_curr_code => l_tot_currency_code_tab(i),
3481 P_project_rate_type => lx_project_rate_type,
3482 P_project_rate_date => lx_project_rate_date,
3483 P_project_exch_rate => lx_project_exch_rate,
3484 P_project_raw_cost => l_etc_quantity_tab(i),
3485 P_projfunc_curr_code=> l_pc_currency_code,
3486 P_projfunc_cost_rate_type => lx_projfunc_cost_rate_type,
3487 P_projfunc_cost_rate_date => lx_projfunc_cost_rate_date,
3488 P_projfunc_cost_exch_rate => lx_projfunc_cost_exch_rate,
3489 P_projfunc_raw_cost => l_projfunc_raw_cost,
3490 P_system_linkage => 'NER',
3491 P_status => l_status,
3492 P_stage => x_msg_count);
3493
3494
3495 IF lx_project_exch_rate IS NULL OR l_status IS NOT NULL THEN
3496 x_return_status := FND_API.G_RET_STS_ERROR;
3497 g_project_name := NULL;
3498 BEGIN
3499 SELECT name INTO g_project_name from
3500 PA_PROJECTS_ALL WHERE
3501 project_id = p_fp_cols_tgt_rec.x_project_id;
3502 EXCEPTION
3503 WHEN OTHERS THEN
3504 g_project_name := NULL;
3505 END;
3506 PA_UTILS.ADD_MESSAGE
3507 ( p_app_short_name => 'PA'
3508 ,p_msg_name => 'PA_FP_PROJ_NO_TXNCONVRATE'
3509 ,p_token1 => 'G_PROJECT_NAME'
3510 ,p_value1 => g_project_name
3511 ,p_token2 => 'FROMCURRENCY'
3512 ,p_value2 => l_pc_currency_code
3513 ,p_token3 => 'TOCURRENCY'
3514 ,p_value3 => l_tot_currency_code_tab(i) );
3515 x_msg_data := 'PA_FP_PROJ_NO_TXNCONVRATE';
3516 END IF;
3517 IF P_PA_DEBUG_MODE = 'Y' THEN
3518 pa_fp_gen_amount_utils.fp_debug(
3519 p_msg => 'After calling pa_multi_currency_txn.'||
3520 'get_currency_amounts in remain_bdgt:'||x_return_status,
3521 p_module_name => l_module_name,
3522 p_log_level => 5);
3523 END IF;
3524 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3525 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3526 END IF;
3527 END IF;
3528 END LOOP;
3529
3530 /*When not taking periodic rates, we need to calculate out the average rates
3531 from the source resource assignments that are mapped to the current target
3532 resource assignment.*/
3533
3534 FOR i IN 1..l_tot_currency_code_tab.count LOOP
3535 SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
3536 NVL(SUM(NVL(total_plan_quantity,0)),0),
3537 NVL(SUM(NVL(total_txn_raw_cost,0)),0),
3538 NVL(SUM(NVL(total_txn_burdened_cost,0)),0),
3539 NVL(SUM(NVL(total_txn_revenue,0)),0),
3540 NVL(SUM(NVL(total_pc_raw_cost,0)),0),
3541 NVL(SUM(NVL(total_pc_burdened_cost,0)),0),
3542 NVL(SUM(NVL(total_pc_revenue,0)),0)
3543 INTO l_txn_rate_quantity,
3544 l_txn_rate_raw_cost,
3545 l_txn_rate_brdn_cost,
3546 l_txn_rate_revenue,
3547 l_pc_rate_raw_cost,
3548 l_pc_rate_brdn_cost,
3549 l_pc_rate_revenue
3550 FROM pa_fp_calc_amt_tmp2
3551 WHERE resource_assignment_id = l_src_res_asg_id
3552 AND txn_currency_code = l_tot_currency_code_tab(i)
3553 AND transaction_source_code in ('FINANCIAL_PLAN' ,
3554 'WORKPLAN_RESOURCES');
3555
3556 -- IPM Change:
3557 -- For non-rate-based target transactions,
3558 -- if the Source is a Cost and Revenue together version,
3559 -- then regardless of the Target version type:
3560 -- set rate quantity to rate raw cost if it exists, OR
3561 -- set rate quantity to rate revenue otherwise.
3562 -- This is done to handle source planning transactions that
3563 -- have only revenue amounts (without cost amounts).
3564 --
3565 -- For non-rate-based target transactions and other Source
3566 -- version types, set rate quantity to rate raw cost as before.
3567
3568 IF l_source_version_type = 'ALL' THEN
3569 IF nvl(l_txn_rate_raw_cost,0) = 0 THEN
3570 l_txn_rate_quantity := l_txn_rate_revenue;
3571 ELSE
3572 l_txn_rate_quantity := l_txn_rate_raw_cost;
3573 END IF;
3574 ELSE
3575 l_txn_rate_quantity := l_txn_rate_raw_cost;
3576 END IF;
3577
3578 -- IPM Change:
3579 -- Since quantity can now be either raw cost or revenue,
3580 -- rates should not always be computed by dividing by raw
3581 -- cost. Code modified to use l_txn_rate_quantity instead.
3582 IF l_txn_rate_quantity <> 0 THEN
3583 l_txn_raw_cost_rate_tab(i) := l_txn_rate_raw_cost
3584 / l_txn_rate_quantity; -- Added in IPM
3585 l_txn_brdn_cost_rate_tab(i) := l_txn_rate_brdn_cost
3586 / l_txn_rate_quantity;
3587 l_txn_revenue_rate_tab(i) := l_txn_rate_revenue
3588 / l_txn_rate_quantity;
3589 l_pc_raw_cost_rate_tab(i) := l_pc_rate_raw_cost
3590 / l_txn_rate_quantity;
3591 l_pc_brdn_cost_rate_tab(i) := l_pc_rate_brdn_cost
3592 / l_txn_rate_quantity;
3593 l_pc_revenue_rate_tab(i) := l_pc_rate_revenue
3594 / l_txn_rate_quantity;
3595 ELSE
3596 l_txn_raw_cost_rate_tab(i) := NULL; -- Added in IPM
3597 l_txn_brdn_cost_rate_tab(i) := NULL;
3598 l_txn_revenue_rate_tab(i) := NULL;
3599 l_pc_raw_cost_rate_tab(i) := NULL;
3600 l_pc_brdn_cost_rate_tab(i) := NULL;
3601 l_pc_revenue_rate_tab(i) := NULL;
3602 END IF;
3603 END LOOP;
3604
3605 FOR i IN 1..l_etc_quantity_tab.count LOOP
3606 l_ins_index := l_ins_src_res_asg_id_tab.count + 1;
3607 l_ins_src_res_asg_id_tab(l_ins_index) := l_src_res_asg_id;
3608 l_ins_tgt_res_asg_id_tab(l_ins_index) := l_tgt_res_asg_id;
3609 l_ins_currency_code_tab(l_ins_index) := l_tot_currency_code_tab(i);
3610 l_ins_etc_quantity_tab(l_ins_index) := l_etc_quantity_tab(i);
3611 l_ins_txn_raw_cost_tab(l_ins_index) :=
3612 l_etc_quantity_tab(i) * l_txn_raw_cost_rate_tab(i);
3613 l_ins_txn_burdened_cost_tab(l_ins_index) :=
3614 l_etc_quantity_tab(i) * l_txn_brdn_cost_rate_tab(i);
3615 l_ins_txn_revenue_tab(l_ins_index) :=
3616 l_etc_quantity_tab(i) * l_txn_revenue_rate_tab(i);
3617 l_ins_pc_raw_cost_tab(l_ins_index) :=
3618 l_etc_quantity_tab(i) * l_pc_raw_cost_rate_tab(i);
3619 l_ins_pc_burdened_cost_tab(l_ins_index) :=
3620 l_etc_quantity_tab(i) * l_pc_brdn_cost_rate_tab(i);
3621 l_ins_pc_revenue_tab(l_ins_index) :=
3622 l_etc_quantity_tab(i) * l_pc_revenue_rate_tab(i);
3623 l_ins_pfc_raw_cost_tab(l_ins_index) := NULL;
3624 l_ins_pfc_burdened_cost_tab(l_ins_index) := NULL;
3625 l_ins_pfc_revenue_tab(l_ins_index) := NULL;
3626 -- Added for Bug 5203622
3627 l_ins_other_rej_code_tab(l_ins_index) := l_other_rej_code_tab(i);
3628 END LOOP;
3629
3630 /***************NOW WE HAVE ALL ETC DATA IN PC_TC*************/
3631
3632 END IF;
3633 /* End the check for 'PC', 'TC' and 'PC_TC'*/
3634
3635 EXCEPTION
3636 WHEN CONTINUE_LOOP THEN
3637 l_dummy := 1;
3638 WHEN OTHERS THEN
3639 RAISE;
3640 END;
3641 END LOOP; -- main loop
3642
3643 /* If commitment is not included, record is inserted directly as 'ETC'
3644 record,if commitment is to be considered, record is inserted as
3645 'TOTAL_ETC' for further processing.*/
3646 IF P_FP_COLS_TGT_REC.X_GEN_INCL_OPEN_COMM_FLAG = 'Y' THEN
3647 l_transaction_source_code := 'TOTAL_ETC';
3648 ELSE
3649 l_transaction_source_code := 'ETC';
3650 END IF;
3651
3652 -- Bug 5203622: Store OTHER rejection code in the
3653 -- txn_currency_code column of pa_fp_calc_amt_tmp2.
3654 FORALL i IN 1..l_ins_etc_quantity_tab.count
3655 INSERT INTO PA_FP_CALC_AMT_TMP2
3656 ( RESOURCE_ASSIGNMENT_ID,
3657 TARGET_RES_ASG_ID,
3658 ETC_CURRENCY_CODE,
3659 ETC_PLAN_QUANTITY,
3660 ETC_TXN_RAW_COST,
3661 ETC_TXN_BURDENED_COST,
3662 ETC_TXN_REVENUE,
3663 ETC_PC_RAW_COST,
3664 ETC_PC_BURDENED_COST,
3665 ETC_PC_REVENUE,
3666 ETC_PFC_RAW_COST,
3667 ETC_PFC_BURDENED_COST,
3668 ETC_PFC_REVENUE,
3669 TRANSACTION_SOURCE_CODE,
3670 TXN_CURRENCY_CODE ) -- Added for Bug 5203622
3671 VALUES ( l_ins_src_res_asg_id_tab(i),
3672 l_ins_tgt_res_asg_id_tab(i),
3673 l_ins_currency_code_tab(i),
3674 l_ins_etc_quantity_tab(i),
3675 l_ins_txn_raw_cost_tab(i),
3676 l_ins_txn_burdened_cost_tab(i),
3677 l_ins_txn_revenue_tab(i),
3678 l_ins_pc_raw_cost_tab(i),
3679 l_ins_pc_burdened_cost_tab(i),
3680 l_ins_pc_revenue_tab(i),
3681 l_ins_pfc_raw_cost_tab(i),
3682 l_ins_pfc_burdened_cost_tab(i),
3683 l_ins_pfc_revenue_tab(i),
3684 l_transaction_source_code,
3685 l_ins_other_rej_code_tab(i) ); -- Added for Bug 5203622
3686
3687 IF P_PA_DEBUG_MODE = 'Y' THEN
3688 PA_DEBUG.RESET_CURR_FUNCTION;
3689 END IF;
3690 EXCEPTION
3691 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3692 l_msg_count := FND_MSG_PUB.count_msg;
3693 IF l_msg_count = 1 THEN
3694 PA_INTERFACE_UTILS_PUB.get_messages
3695 ( p_encoded => FND_API.G_TRUE,
3696 p_msg_index => 1,
3697 p_msg_count => l_msg_count,
3698 p_msg_data => l_msg_data,
3699 p_data => l_data,
3700 p_msg_index_out => l_msg_index_out);
3701 x_msg_data := l_data;
3702 x_msg_count := l_msg_count;
3703 ELSE
3704 x_msg_count := l_msg_count;
3705 END IF;
3706
3707 ROLLBACK;
3708 x_return_status := FND_API.G_RET_STS_ERROR;
3709
3710 IF P_PA_DEBUG_MODE = 'Y' THEN
3711 pa_fp_gen_amount_utils.fp_debug
3712 (p_msg => 'Invalid Arguments Passed',
3713 p_module_name => l_module_name,
3714 p_log_level => 5);
3715 PA_DEBUG.RESET_CURR_FUNCTION;
3716 END IF;
3717 RAISE;
3718 WHEN OTHERS THEN
3719 rollback;
3720 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3721 x_msg_count := 1;
3722 x_msg_data := substr(sqlerrm,1,240);
3723 --dbms_output.put_line('error msg :'||x_msg_data);
3724 FND_MSG_PUB.add_exc_msg
3725 ( p_pkg_name => 'PA_FP_GEN_FCST_AMT_PUB3',
3726 p_procedure_name => 'GEN_ETC_REMAIN_BDGT_AMTS_BLK',
3727 p_error_text => substr(sqlerrm,1,240));
3728
3729 IF P_PA_DEBUG_MODE = 'Y' THEN
3730 pa_fp_gen_amount_utils.fp_debug
3731 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
3732 p_module_name => l_module_name,
3733 p_log_level => 5);
3734 PA_DEBUG.RESET_CURR_FUNCTION;
3735 END IF;
3736 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3737 END GET_ETC_REMAIN_BDGT_AMTS_BLK;
3738
3739 -- gboomina added for AAI Requirement bug 8318932 - start
3740 /* AAI Enhancement
3741 * This method is meant to get the periodic budget lines from the source and
3742 * create the same in destination and then update the intermediate tmp2 with
3743 * the etc values.
3744 * The processing in this api happens in phases in first phase we direclty
3745 * copy the budgetlines from the source plan if the time phase match and
3746 * in second phase we distribute or club the amounts based on the time phases
3747 * of source and destination.
3748 */
3749 PROCEDURE GET_ETC_FROM_SRC_BDGT
3750 (P_FP_COLS_SRC_FP_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
3751 P_FP_COLS_SRC_WP_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
3752 P_FP_COLS_TGT_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
3753 P_ACTUALS_THRU_DATE IN PA_PERIODS_ALL.END_DATE%TYPE,
3754 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3755 X_MSG_COUNT OUT NOCOPY NUMBER,
3756 X_MSG_DATA OUT NOCOPY VARCHAR2)
3757 IS
3758
3759
3760 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_FCST_AMT_PUB3.GET_ETC_FROM_SRC_BDGT';
3761 l_txn_currency_flag varchar2(1) := 'Y';
3762
3763 -- Cursor For fully coping budget lines from source.
3764 CURSOR fcst_budget_line_src_tgt_all
3765 (c_proj_currency_code PA_PROJECTS_ALL.PROJECT_CURRENCY_CODE%TYPE,
3766 c_projfunc_currency_code PA_PROJECTS_ALL.PROJECT_CURRENCY_CODE%TYPE,
3767 c_target_bv_id NUMBER,
3768 c_project_id NUMBER ) IS
3769 SELECT ra.resource_assignment_id,
3770 ra.rate_based_flag,
3771 sbl.start_date,
3772 sbl.end_date,
3773 sbl.period_name,
3774 decode(l_txn_currency_flag,
3775 'Y', sbl.txn_currency_code,
3776 'N', c_proj_currency_code,
3777 'A', c_projfunc_currency_code),
3778 sum(sbl.quantity),
3779 sum(decode(l_txn_currency_flag,
3780 'Y', sbl.txn_raw_cost,
3781 'N', sbl.project_raw_cost,
3782 'A', sbl.raw_cost)),
3783 sum(decode(l_txn_currency_flag,
3784 'Y', sbl.txn_burdened_cost,
3785 'N', sbl.project_burdened_cost,
3786 'A', sbl.burdened_cost)),
3787 sum(decode(l_txn_currency_flag,
3788 'Y', sbl.quantity *
3789 NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.txn_raw_cost
3790 'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
3791 NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.project_raw_cost
3792 'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
3793 NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))), --sbl.raw_cost
3794 sum(decode(l_txn_currency_flag,
3795 'Y', sbl.quantity *
3796 NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.txn_burdened_cost
3797 'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
3798 NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.project_burdened_cost
3799 'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
3800 NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))), --sbl.burdened_cost
3801 NULL,
3802 NULL
3803 FROM PA_FP_CALC_AMT_TMP2 tmp4,
3804 pa_budget_lines sbl,
3805 pa_resource_assignments ra
3806 WHERE tmp4.resource_assignment_id = sbl.resource_assignment_id
3807 and (tmp4.TRANSACTION_SOURCE_CODE = 'WORKPLAN_RESOURCES' OR
3808 tmp4.TRANSACTION_SOURCE_CODE = 'FINANCIAL_PLAN')
3809 and tmp4.TARGET_RES_ASG_ID = ra.resource_assignment_id
3810 and ra.budget_version_id = c_target_bv_id
3811 and sbl.end_date > P_ACTUALS_THRU_DATE
3812 and sbl.cost_rejection_code is null
3813 and sbl.burden_rejection_code is null
3814 and sbl.other_rejection_code is null
3815 and sbl.pc_cur_conv_rejection_code is null
3816 and sbl.pfc_cur_conv_rejection_code is null
3817 and ra.project_id = c_project_id
3818 GROUP BY ra.resource_assignment_id,
3819 ra.rate_based_flag,
3820 sbl.start_date,
3821 sbl.end_date,
3822 sbl.period_name,
3823 decode(l_txn_currency_flag,
3824 'Y', sbl.txn_currency_code,
3825 'N', c_proj_currency_code,
3826 'A', c_projfunc_currency_code),
3827 NULL,
3828 NULL;
3829
3830 -- This cursor is used to copy budget lines when one of the source has same time phase.
3831 CURSOR fcst_budget_line_src_tgt_ptl
3832 (c_proj_currency_code PA_PROJECTS_ALL.PROJECT_CURRENCY_CODE%TYPE,
3833 c_projfunc_currency_code PA_PROJECTS_ALL.PROJECT_CURRENCY_CODE%TYPE,
3834 c_target_bv_id NUMBER,
3835 c_project_id NUMBER,
3836 c_gen_source VARCHAR2 ) IS
3837 SELECT ra.resource_assignment_id,
3838 ra.rate_based_flag,
3839 sbl.start_date,
3840 sbl.end_date,
3841 sbl.period_name,
3842 decode(l_txn_currency_flag,
3843 'Y', sbl.txn_currency_code,
3844 'N', c_proj_currency_code,
3845 'A', c_projfunc_currency_code),
3846 sum(sbl.quantity),
3847 sum(decode(l_txn_currency_flag,
3848 'Y', sbl.txn_raw_cost,
3849 'N', sbl.project_raw_cost,
3850 'A', sbl.raw_cost)),
3851 sum(decode(l_txn_currency_flag,
3852 'Y', sbl.txn_burdened_cost,
3853 'N', sbl.project_burdened_cost,
3854 'A', sbl.burdened_cost)),
3855 sum(decode(l_txn_currency_flag,
3856 'Y', sbl.quantity *
3857 NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.txn_raw_cost
3858 'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
3859 NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.project_raw_cost
3860 'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
3861 NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))), --sbl.raw_cost
3862 sum(decode(l_txn_currency_flag,
3863 'Y', sbl.quantity *
3864 NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.txn_burdened_cost
3865 'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
3866 NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.project_burdened_cost
3867 'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
3868 NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))), --sbl.burdened_cost
3869 NULL,
3870 NULL
3871 FROM PA_FP_CALC_AMT_TMP2 tmp4,
3872 pa_budget_lines sbl,
3873 pa_resource_assignments ra
3874 WHERE tmp4.resource_assignment_id = sbl.resource_assignment_id
3875 and tmp4.TRANSACTION_SOURCE_CODE = c_gen_source
3876 and tmp4.TARGET_RES_ASG_ID = ra.resource_assignment_id
3877 and ra.budget_version_id = c_target_bv_id
3878 and sbl.end_date > P_ACTUALS_THRU_DATE
3879 and sbl.cost_rejection_code is null
3880 and sbl.burden_rejection_code is null
3881 and sbl.other_rejection_code is null
3882 and sbl.pc_cur_conv_rejection_code is null
3883 and sbl.pfc_cur_conv_rejection_code is null
3884 and ra.project_id = c_project_id
3885 GROUP BY ra.resource_assignment_id,
3886 ra.rate_based_flag,
3887 sbl.start_date,
3888 sbl.end_date,
3889 sbl.period_name,
3890 decode(l_txn_currency_flag,
3891 'Y', sbl.txn_currency_code,
3892 'N', c_proj_currency_code,
3893 'A', c_projfunc_currency_code),
3894 NULL,
3895 NULL;
3896
3897 -- This cursor will summ the budget lines when the source time phase is diff and source period span is
3898 -- smaller than destination based on the one of the plans which has issue.
3899 CURSOR fcst_bdgt_line_src_tgt_sum
3900 (c_proj_currency_code PA_PROJECTS_ALL.PROJECT_CURRENCY_CODE%TYPE,
3901 c_projfunc_currency_code PA_PROJECTS_ALL.PROJECT_CURRENCY_CODE%TYPE,
3902 c_target_bv_id NUMBER,
3903 c_project_id NUMBER,
3904 c_time_phase VARCHAR2 ) IS
3905 SELECT ra.resource_assignment_id,
3906 ra.rate_based_flag,
3907 decode(c_time_phase,'G',pa_gl.PA_START_DATE,'P',pa_gl.GL_START_DATE),
3908 decode(c_time_phase,'G',pa_gl.PA_END_DATE,'P',pa_gl.GL_END_DATE),
3909 decode(c_time_phase,'G',pa_gl.PA_PERIOD_NAME,'P',pa_gl.GL_PERIOD_NAME),
3910 decode(l_txn_currency_flag,
3911 'Y', sbl.txn_currency_code,
3912 'N', c_proj_currency_code,
3913 'A', c_projfunc_currency_code),
3914 sum(sbl.quantity),
3915 sum(decode(l_txn_currency_flag,
3916 'Y', sbl.txn_raw_cost,
3917 'N', sbl.project_raw_cost,
3918 'A', sbl.raw_cost)),
3919 sum(decode(l_txn_currency_flag,
3920 'Y', sbl.txn_burdened_cost,
3921 'N', sbl.project_burdened_cost,
3922 'A', sbl.burdened_cost)),
3923 sum(decode(l_txn_currency_flag,
3924 'Y', sbl.quantity *
3925 NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.txn_raw_cost
3926 'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
3927 NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.project_raw_cost
3928 'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
3929 NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))), --sbl.raw_cost
3930 sum(decode(l_txn_currency_flag,
3931 'Y', sbl.quantity *
3932 NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.txn_burdened_cost
3933 'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
3934 NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.project_burdened_cost
3935 'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
3936 NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))), --sbl.burdened_cost
3937 NULL,
3938 NULL
3939 FROM PA_FP_CALC_AMT_TMP2 tmp4,
3940 pa_budget_lines sbl,
3941 pa_resource_assignments ra,
3942 PA_GL_PA_PERIODS_TMP pa_gl
3943 WHERE tmp4.resource_assignment_id = sbl.resource_assignment_id
3944 and (tmp4.TRANSACTION_SOURCE_CODE = 'WORKPLAN_RESOURCES' OR
3945 tmp4.TRANSACTION_SOURCE_CODE = 'FINANCIAL_PLAN')
3946 and tmp4.TARGET_RES_ASG_ID = ra.resource_assignment_id
3947 and ra.budget_version_id = c_target_bv_id
3948 and sbl.end_date > P_ACTUALS_THRU_DATE
3949 and sbl.period_name = decode(c_time_phase,'P',pa_gl.PA_PERIOD_NAME,'G',pa_gl.GL_PERIOD_NAME)
3950 and sbl.start_date = decode(c_time_phase,'P',pa_gl.PA_START_DATE,'G',pa_gl.GL_START_DATE)
3951 and sbl.cost_rejection_code is null
3952 and sbl.burden_rejection_code is null
3953 and sbl.other_rejection_code is null
3954 and sbl.pc_cur_conv_rejection_code is null
3955 and sbl.pfc_cur_conv_rejection_code is null
3956 and ra.project_id = c_project_id
3957 GROUP BY ra.resource_assignment_id,
3958 ra.rate_based_flag,
3959 decode(c_time_phase,'G',pa_gl.PA_START_DATE,'P',pa_gl.GL_START_DATE),
3960 decode(c_time_phase,'G',pa_gl.PA_END_DATE,'P',pa_gl.GL_END_DATE),
3961 decode(c_time_phase,'G',pa_gl.PA_PERIOD_NAME,'P',pa_gl.GL_PERIOD_NAME),
3962 decode(l_txn_currency_flag,
3963 'Y', sbl.txn_currency_code,
3964 'N', c_proj_currency_code,
3965 'A', c_projfunc_currency_code),
3966 NULL,
3967 NULL;
3968
3969 -- This cursor will summ the budget lines when the source time phase is diff and source period span is
3970 -- smaller than destination based on the one of the plans which has issue.
3971 CURSOR fcst_bdgt_line_src_tgt_sum_ptl
3972 (c_proj_currency_code PA_PROJECTS_ALL.PROJECT_CURRENCY_CODE%TYPE,
3973 c_projfunc_currency_code PA_PROJECTS_ALL.PROJECT_CURRENCY_CODE%TYPE,
3974 c_target_bv_id NUMBER,
3975 c_project_id NUMBER,
3976 c_gen_source VARCHAR2,
3977 c_time_phase VARCHAR2 ) IS
3978 SELECT ra.resource_assignment_id,
3979 ra.rate_based_flag,
3980 decode(c_time_phase,'G',pa_gl.PA_START_DATE,'P',pa_gl.GL_START_DATE),
3981 decode(c_time_phase,'G',pa_gl.PA_END_DATE,'P',pa_gl.GL_END_DATE),
3982 decode(c_time_phase,'G',pa_gl.PA_PERIOD_NAME,'P',pa_gl.GL_PERIOD_NAME),
3983 decode(l_txn_currency_flag,
3984 'Y', sbl.txn_currency_code,
3985 'N', c_proj_currency_code,
3986 'A', c_projfunc_currency_code),
3987 sum(sbl.quantity),
3988 sum(decode(l_txn_currency_flag,
3989 'Y', sbl.txn_raw_cost,
3990 'N', sbl.project_raw_cost,
3991 'A', sbl.raw_cost)),
3992 sum(decode(l_txn_currency_flag,
3993 'Y', sbl.txn_burdened_cost,
3994 'N', sbl.project_burdened_cost,
3995 'A', sbl.burdened_cost)),
3996 sum(decode(l_txn_currency_flag,
3997 'Y', sbl.quantity *
3998 NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.txn_raw_cost
3999 'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
4000 NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.project_raw_cost
4001 'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
4002 NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate))), --sbl.raw_cost
4003 sum(decode(l_txn_currency_flag,
4004 'Y', sbl.quantity *
4005 NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.txn_burdened_cost
4006 'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
4007 NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.project_burdened_cost
4008 'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
4009 NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate))), --sbl.burdened_cost
4010 NULL,
4011 NULL
4012 FROM PA_FP_CALC_AMT_TMP2 tmp4,
4013 pa_budget_lines sbl,
4014 pa_resource_assignments ra,
4015 PA_GL_PA_PERIODS_TMP pa_gl
4016 WHERE tmp4.resource_assignment_id = sbl.resource_assignment_id
4017 and tmp4.TRANSACTION_SOURCE_CODE = c_gen_source
4018 and tmp4.TARGET_RES_ASG_ID = ra.resource_assignment_id
4019 and ra.budget_version_id = c_target_bv_id
4020 and sbl.end_date > P_ACTUALS_THRU_DATE
4021 and sbl.period_name = decode(c_time_phase,'P',pa_gl.PA_PERIOD_NAME,'G',pa_gl.GL_PERIOD_NAME)
4022 and sbl.start_date = decode(c_time_phase,'P',pa_gl.PA_START_DATE,'G',pa_gl.GL_START_DATE)
4023 and sbl.cost_rejection_code is null
4024 and sbl.burden_rejection_code is null
4025 and sbl.other_rejection_code is null
4026 and sbl.pc_cur_conv_rejection_code is null
4027 and sbl.pfc_cur_conv_rejection_code is null
4028 and ra.project_id = c_project_id
4029 GROUP BY ra.resource_assignment_id,
4030 ra.rate_based_flag,
4031 decode(c_time_phase,'G',pa_gl.PA_START_DATE,'P',pa_gl.GL_START_DATE),
4032 decode(c_time_phase,'G',pa_gl.PA_END_DATE,'P',pa_gl.GL_END_DATE),
4033 decode(c_time_phase,'G',pa_gl.PA_PERIOD_NAME,'P',pa_gl.GL_PERIOD_NAME),
4034 decode(l_txn_currency_flag,
4035 'Y', sbl.txn_currency_code,
4036 'N', c_proj_currency_code,
4037 'A', c_projfunc_currency_code),
4038 NULL,
4039 NULL;
4040
4041
4042 -- This cursor will distribute the source budget amounts into the destination budget lines uniformly such
4043 -- destinations end periods fall in the source period span when source period span is greater than dest.
4044 CURSOR fcst_bdgt_line_src_tgt_dist
4045 (c_proj_currency_code PA_PROJECTS_ALL.PROJECT_CURRENCY_CODE%TYPE,
4046 c_projfunc_currency_code PA_PROJECTS_ALL.PROJECT_CURRENCY_CODE%TYPE,
4047 c_target_bv_id NUMBER,
4048 c_project_id NUMBER,
4049 c_time_phase VARCHAR2 ) IS
4050 SELECT ra.resource_assignment_id,
4051 ra.rate_based_flag,
4052 decode(c_time_phase,'G',pa_gl.PA_START_DATE,'P',pa_gl.GL_START_DATE),
4053 decode(c_time_phase,'G',pa_gl.PA_END_DATE,'P',pa_gl.GL_END_DATE),
4054 decode(c_time_phase,'G',pa_gl.PA_PERIOD_NAME,'P',pa_gl.GL_PERIOD_NAME),
4055 decode(l_txn_currency_flag,
4056 'Y', sbl.txn_currency_code,
4057 'N', c_proj_currency_code,
4058 'A', c_projfunc_currency_code),
4059 sum((sbl.quantity)/pa_gl.multiplier),
4060 sum((decode(l_txn_currency_flag,
4061 'Y', sbl.txn_raw_cost,
4062 'N', sbl.project_raw_cost,
4063 'A', sbl.raw_cost))/pa_gl.multiplier),
4064 sum((decode(l_txn_currency_flag,
4065 'Y', sbl.txn_burdened_cost,
4066 'N', sbl.project_burdened_cost,
4067 'A', sbl.burdened_cost))/pa_gl.multiplier),
4068 sum((decode(l_txn_currency_flag,
4069 'Y', sbl.quantity *
4070 NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.txn_raw_cost
4071 'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
4072 NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.project_raw_cost
4073 'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
4074 NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate)))/pa_gl.multiplier), --sbl.raw_cost
4075 sum((decode(l_txn_currency_flag,
4076 'Y', sbl.quantity *
4077 NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.txn_burdened_cost
4078 'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
4079 NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.project_burdened_cost
4080 'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
4081 NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate)))/pa_gl.multiplier), --sbl.burdened_cost
4082 NULL,
4083 NULL
4084 FROM PA_FP_CALC_AMT_TMP2 tmp4,
4085 pa_budget_lines sbl,
4086 pa_resource_assignments ra,
4087 PA_GL_PA_PERIODS_TMP pa_gl
4088 WHERE tmp4.resource_assignment_id = sbl.resource_assignment_id
4089 and (tmp4.TRANSACTION_SOURCE_CODE = 'WORKPLAN_RESOURCES' OR
4090 tmp4.TRANSACTION_SOURCE_CODE = 'FINANCIAL_PLAN')
4091 and tmp4.TARGET_RES_ASG_ID = ra.resource_assignment_id
4092 and ra.budget_version_id = c_target_bv_id
4093 and sbl.end_date > P_ACTUALS_THRU_DATE
4094 and sbl.period_name = decode(c_time_phase,'P',pa_gl.PA_PERIOD_NAME,'G',pa_gl.GL_PERIOD_NAME)
4095 and sbl.start_date = decode(c_time_phase,'P',pa_gl.PA_START_DATE,'G',pa_gl.GL_START_DATE)
4096 and sbl.cost_rejection_code is null
4097 and sbl.burden_rejection_code is null
4098 and sbl.other_rejection_code is null
4099 and sbl.pc_cur_conv_rejection_code is null
4100 and sbl.pfc_cur_conv_rejection_code is null
4101 and ra.project_id = c_project_id
4102 GROUP BY ra.resource_assignment_id,
4103 ra.rate_based_flag,
4104 decode(c_time_phase,'G',pa_gl.PA_START_DATE,'P',pa_gl.GL_START_DATE),
4105 decode(c_time_phase,'G',pa_gl.PA_END_DATE,'P',pa_gl.GL_END_DATE),
4106 decode(c_time_phase,'G',pa_gl.PA_PERIOD_NAME,'P',pa_gl.GL_PERIOD_NAME),
4107 decode(l_txn_currency_flag,
4108 'Y', sbl.txn_currency_code,
4109 'N', c_proj_currency_code,
4110 'A', c_projfunc_currency_code),
4111 NULL,
4112 NULL;
4113
4114 -- This cursor will distribute the source budget amounts into the destination budget lines uniformly such
4115 -- destinations end periods fall in the source period span when source period span is greater than dest.
4116 CURSOR fcst_bdgt_line_src_tgt_dist_pt
4117 (c_proj_currency_code PA_PROJECTS_ALL.PROJECT_CURRENCY_CODE%TYPE,
4118 c_projfunc_currency_code PA_PROJECTS_ALL.PROJECT_CURRENCY_CODE%TYPE,
4119 c_target_bv_id NUMBER,
4120 c_project_id NUMBER,
4121 c_gen_source VARCHAR2,
4122 c_time_phase VARCHAR2 ) IS
4123 SELECT ra.resource_assignment_id,
4124 ra.rate_based_flag,
4125 decode(c_time_phase,'G',pa_gl.PA_START_DATE,'P',pa_gl.GL_START_DATE),
4126 decode(c_time_phase,'G',pa_gl.PA_END_DATE,'P',pa_gl.GL_END_DATE),
4127 decode(c_time_phase,'G',pa_gl.PA_PERIOD_NAME,'P',pa_gl.GL_PERIOD_NAME),
4128 decode(l_txn_currency_flag,
4129 'Y', sbl.txn_currency_code,
4130 'N', c_proj_currency_code,
4131 'A', c_projfunc_currency_code),
4132 sum((sbl.quantity)/pa_gl.multiplier),
4133 sum((decode(l_txn_currency_flag,
4134 'Y', sbl.txn_raw_cost,
4135 'N', sbl.project_raw_cost,
4136 'A', sbl.raw_cost))/pa_gl.multiplier),
4137 sum((decode(l_txn_currency_flag,
4138 'Y', sbl.txn_burdened_cost,
4139 'N', sbl.project_burdened_cost,
4140 'A', sbl.burdened_cost))/pa_gl.multiplier),
4141 sum((decode(l_txn_currency_flag,
4142 'Y', sbl.quantity *
4143 NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.txn_raw_cost
4144 'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
4145 NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate), --sbl.project_raw_cost
4146 'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
4147 NVL(sbl.txn_cost_rate_override,sbl.txn_standard_cost_rate)))/pa_gl.multiplier), --sbl.raw_cost
4148 sum((decode(l_txn_currency_flag,
4149 'Y', sbl.quantity *
4150 NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.txn_burdened_cost
4151 'N', sbl.quantity * NVL(sbl.project_cost_exchange_rate,1) *
4152 NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate), --sbl.project_burdened_cost
4153 'A', sbl.quantity * NVL(sbl.projfunc_cost_exchange_rate,1) *
4154 NVL(sbl.burden_cost_rate_override,sbl.burden_cost_rate)))/pa_gl.multiplier), --sbl.burdened_cost
4155 NULL,
4156 NULL
4157 FROM PA_FP_CALC_AMT_TMP2 tmp4,
4158 pa_budget_lines sbl,
4159 pa_resource_assignments ra,
4160 PA_GL_PA_PERIODS_TMP pa_gl
4161 WHERE tmp4.resource_assignment_id = sbl.resource_assignment_id
4162 and tmp4.TRANSACTION_SOURCE_CODE = c_gen_source
4163 and tmp4.TARGET_RES_ASG_ID = ra.resource_assignment_id
4164 and ra.budget_version_id = c_target_bv_id
4165 and sbl.end_date > P_ACTUALS_THRU_DATE
4166 and sbl.period_name = decode(c_time_phase,'P',pa_gl.PA_PERIOD_NAME,'G',pa_gl.GL_PERIOD_NAME)
4167 and sbl.start_date = decode(c_time_phase,'P',pa_gl.PA_START_DATE,'G',pa_gl.GL_START_DATE)
4168 and sbl.cost_rejection_code is null
4169 and sbl.burden_rejection_code is null
4170 and sbl.other_rejection_code is null
4171 and sbl.pc_cur_conv_rejection_code is null
4172 and sbl.pfc_cur_conv_rejection_code is null
4173 and ra.project_id = c_project_id
4174 GROUP BY ra.resource_assignment_id,
4175 ra.rate_based_flag,
4176 decode(c_time_phase,'G',pa_gl.PA_START_DATE,'P',pa_gl.GL_START_DATE),
4177 decode(c_time_phase,'G',pa_gl.PA_END_DATE,'P',pa_gl.GL_END_DATE),
4178 decode(c_time_phase,'G',pa_gl.PA_PERIOD_NAME,'P',pa_gl.GL_PERIOD_NAME),
4179 decode(l_txn_currency_flag,
4180 'Y', sbl.txn_currency_code,
4181 'N', c_proj_currency_code,
4182 'A', c_projfunc_currency_code),
4183 NULL,
4184 NULL;
4185
4186 l_tgt_res_asg_id_tab pa_plsql_datatypes.IdTabTyp;
4187 l_tgt_rate_based_flag_tab pa_plsql_datatypes.Char15TabTyp;
4188 l_start_date_tab pa_plsql_datatypes.DateTabTyp;
4189 l_txn_currency_code_tab pa_plsql_datatypes.Char15TabTyp;
4190 l_end_date_tab pa_plsql_datatypes.DateTabTyp;
4191 l_period_name_tab pa_plsql_datatypes.Char30TabTyp;
4192 l_src_quantity_tab pa_plsql_datatypes.NumTabTyp;
4193 l_txn_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
4194 l_txn_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
4195 l_unround_txn_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
4196 l_unround_txn_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
4197 l_pfc_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
4198 l_pfc_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
4199 l_pc_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
4200 l_pc_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
4201 l_cost_rate_override_tab pa_plsql_datatypes.NumTabTyp;
4202 l_b_cost_rate_override_tab pa_plsql_datatypes.NumTabTyp;
4203
4204 -- Used to store partial
4205 l_pr_tgt_res_asg_id_tab pa_plsql_datatypes.IdTabTyp;
4206 l_pr_tgt_rate_based_flag_tab pa_plsql_datatypes.Char15TabTyp;
4207 l_pr_start_date_tab pa_plsql_datatypes.DateTabTyp;
4208 l_pr_txn_currency_code_tab pa_plsql_datatypes.Char15TabTyp;
4209 l_pr_end_date_tab pa_plsql_datatypes.DateTabTyp;
4210 l_pr_period_name_tab pa_plsql_datatypes.Char30TabTyp;
4211 l_pr_src_quantity_tab pa_plsql_datatypes.NumTabTyp;
4212 l_pr_txn_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
4213 l_pr_txn_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
4214 l_pr_unround_txn_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
4215 l_pr_unround_txn_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
4216 l_pr_pfc_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
4217 l_pr_pfc_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
4218 l_pr_pc_brdn_cost_tab pa_plsql_datatypes.NumTabTyp;
4219 l_pr_pc_raw_cost_tab pa_plsql_datatypes.NumTabTyp;
4220 l_pr_cost_rate_override_tab pa_plsql_datatypes.NumTabTyp;
4221 l_pr_b_cost_rate_override_tab pa_plsql_datatypes.NumTabTyp;
4222
4223
4224
4225 l_last_updated_by PA_RESOURCE_ASSIGNMENTS.LAST_UPDATED_BY%TYPE
4226 := FND_GLOBAL.user_id;
4227 l_last_update_login PA_RESOURCE_ASSIGNMENTS.LAST_UPDATE_LOGIN%TYPE
4228 := FND_GLOBAL.login_id;
4229
4230 l_override_quantity NUMBER;
4231 l_copy_lines varchar2(20);
4232 l_is_gl_greater VARCHAR2(1) := 'N';
4233 l_is_pa_greater VARCHAR2(1) := 'N';
4234 l_end_date DATE;
4235 l_dist_amounts VARCHAR2(1) := 'N';
4236 l_msg_count NUMBER;
4237 l_data VARCHAR2(2000);
4238 l_msg_data VARCHAR2(2000);
4239 l_msg_index_out NUMBER;
4240
4241 BEGIN
4242
4243 IF p_pa_debug_mode = 'Y' THEN
4244 pa_debug.set_curr_function( p_function => 'GET_ETC_FROM_SRC_BDGT',
4245 p_debug_mode => p_pa_debug_mode);
4246 END IF;
4247
4248 x_return_status := FND_API.G_RET_STS_SUCCESS;
4249 x_msg_count := 0;
4250
4251 IF P_FP_COLS_SRC_FP_REC.X_TIME_PHASED_CODE = P_FP_COLS_TGT_REC.X_TIME_PHASED_CODE AND
4252 P_FP_COLS_SRC_WP_REC.X_TIME_PHASED_CODE = P_FP_COLS_TGT_REC.X_TIME_PHASED_CODE THEN
4253 l_copy_lines := 'ALL';
4254 ELSIF P_FP_COLS_SRC_FP_REC.X_TIME_PHASED_CODE = P_FP_COLS_TGT_REC.X_TIME_PHASED_CODE /* AND
4255 P_FP_COLS_SRC_WP_REC.X_TIME_PHASED_CODE <> P_FP_COLS_TGT_REC.X_TIME_PHASED_CODE */THEN
4256 l_copy_lines := 'FINANCIAL_PLAN';
4257 ELSIF /*P_FP_COLS_SRC_FP_REC.X_TIME_PHASED_CODE <> P_FP_COLS_TGT_REC.X_TIME_PHASED_CODE AND*/
4258 P_FP_COLS_SRC_WP_REC.X_TIME_PHASED_CODE = P_FP_COLS_TGT_REC.X_TIME_PHASED_CODE THEN
4259 l_copy_lines := 'WORKPLAN_RESOURCES';
4260 ELSE
4261 l_copy_lines := 'NONE';
4262 END IF;
4263
4264 -- Need to check up if we need to do this.
4265 IF P_FP_COLS_TGT_REC.x_plan_in_multi_curr_flag ='N' THEN
4266 l_txn_currency_flag := 'N';
4267 ELSE
4268 l_txn_currency_flag := 'A';
4269 END IF;
4270
4271 IF l_copy_lines = 'ALL' THEN
4272 OPEN fcst_budget_line_src_tgt_all
4273 (P_FP_COLS_TGT_REC.x_project_currency_code,
4274 P_FP_COLS_TGT_REC.x_projfunc_currency_code,
4275 P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
4276 P_FP_COLS_TGT_REC.x_project_id);
4277 FETCH fcst_budget_line_src_tgt_all
4278 BULK COLLECT
4279 INTO l_tgt_res_asg_id_tab,
4280 l_tgt_rate_based_flag_tab,
4281 l_start_date_tab,
4282 l_end_date_tab,
4283 l_period_name_tab,
4284 l_txn_currency_code_tab,
4285 l_src_quantity_tab,
4286 l_txn_raw_cost_tab,
4287 l_txn_brdn_cost_tab,
4288 l_unround_txn_raw_cost_tab,
4289 l_unround_txn_brdn_cost_tab,
4290 l_cost_rate_override_tab,
4291 l_b_cost_rate_override_tab;
4292 CLOSE fcst_budget_line_src_tgt_all;
4293
4294 IF l_tgt_res_asg_id_tab.count > 0 THEN
4295 FOR i in 1..l_tgt_res_asg_id_tab.count LOOP
4296 l_override_quantity := l_src_quantity_tab(i);
4297 IF l_tgt_rate_based_flag_tab(i) = 'N' THEN
4298 l_src_quantity_tab(i):= l_txn_raw_cost_tab(i);
4299 l_override_quantity := l_unround_txn_raw_cost_tab(i);
4300 END IF;
4301 IF l_override_quantity <> 0 THEN
4302 l_cost_rate_override_tab(i) := l_unround_txn_raw_cost_tab(i) / l_override_quantity;
4303 l_b_cost_rate_override_tab(i) := l_unround_txn_brdn_cost_tab(i) / l_override_quantity;
4304 IF l_tgt_rate_based_flag_tab(i) = 'N' THEN
4305 l_cost_rate_override_tab(i) := 1;
4306 END IF;
4307 END IF;
4308 END LOOP;
4309
4310
4311 FORALL i IN l_tgt_res_asg_id_tab.FIRST..l_tgt_res_asg_id_tab.LAST
4312 INSERT INTO PA_BUDGET_LINES (
4313 BUDGET_LINE_ID,
4314 BUDGET_VERSION_ID,
4315 RESOURCE_ASSIGNMENT_ID,
4316 START_DATE,
4317 TXN_CURRENCY_CODE,
4318 TXN_RAW_COST,
4319 TXN_BURDENED_COST,
4320 END_DATE,
4321 PERIOD_NAME,
4322 QUANTITY,
4323 LAST_UPDATE_DATE,
4324 LAST_UPDATED_BY,
4325 CREATION_DATE,
4326 CREATED_BY,
4327 LAST_UPDATE_LOGIN,
4328 PROJECT_CURRENCY_CODE,
4329 PROJFUNC_CURRENCY_CODE,
4330 TXN_COST_RATE_OVERRIDE,
4331 BURDEN_COST_RATE_OVERRIDE
4332 )
4333 VALUES (
4334 pa_budget_lines_s.nextval,
4335 P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
4336 l_tgt_res_asg_id_tab(i),
4337 l_start_date_tab(i),
4338 l_txn_currency_code_tab(i),
4339 l_txn_raw_cost_tab(i),
4340 l_txn_brdn_cost_tab(i),
4341 l_end_date_tab(i),
4342 l_period_name_tab(i),
4343 l_src_quantity_tab(i),
4344 sysdate,
4345 FND_GLOBAL.USER_ID,
4346 sysdate,
4347 FND_GLOBAL.USER_ID,
4348 FND_GLOBAL.LOGIN_ID,
4349 P_FP_COLS_TGT_REC.X_PROJECT_CURRENCY_CODE,
4350 P_FP_COLS_TGT_REC.X_PROJFUNC_CURRENCY_CODE,
4351 l_cost_rate_override_tab(i),
4352 l_b_cost_rate_override_tab(i)
4353 );
4354 END IF; --l_tgt_res_asg_id_tab.count > 0
4355
4356 ELSIF l_copy_lines = 'FINANCIAL_PLAN' THEN
4357 OPEN fcst_budget_line_src_tgt_ptl
4358 (P_FP_COLS_TGT_REC.x_project_currency_code,
4359 P_FP_COLS_TGT_REC.x_projfunc_currency_code,
4360 P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
4361 P_FP_COLS_TGT_REC.x_project_id,
4362 l_copy_lines);
4363 FETCH fcst_budget_line_src_tgt_ptl
4364 BULK COLLECT
4365 INTO l_tgt_res_asg_id_tab,
4366 l_tgt_rate_based_flag_tab,
4367 l_start_date_tab,
4368 l_end_date_tab,
4369 l_period_name_tab,
4370 l_txn_currency_code_tab,
4371 l_src_quantity_tab,
4372 l_txn_raw_cost_tab,
4373 l_txn_brdn_cost_tab,
4374 l_unround_txn_raw_cost_tab,
4375 l_unround_txn_brdn_cost_tab,
4376 l_cost_rate_override_tab,
4377 l_b_cost_rate_override_tab;
4378 CLOSE fcst_budget_line_src_tgt_ptl;
4379
4380 IF l_tgt_res_asg_id_tab.count > 0 THEN
4381
4382 FOR i in 1..l_tgt_res_asg_id_tab.count LOOP
4383 l_override_quantity := l_src_quantity_tab(i);
4384 IF l_tgt_rate_based_flag_tab(i) = 'N' THEN
4385 l_src_quantity_tab(i):= l_txn_raw_cost_tab(i);
4386 l_override_quantity := l_unround_txn_raw_cost_tab(i);
4387 END IF;
4388 IF l_override_quantity <> 0 THEN
4389 l_cost_rate_override_tab(i) := l_unround_txn_raw_cost_tab(i) / l_override_quantity;
4390 l_b_cost_rate_override_tab(i) := l_unround_txn_brdn_cost_tab(i) / l_override_quantity;
4391 IF l_tgt_rate_based_flag_tab(i) = 'N' THEN
4392 l_cost_rate_override_tab(i) := 1;
4393 END IF;
4394 END IF;
4395 END LOOP;
4396
4397
4398 FORALL i IN l_tgt_res_asg_id_tab.FIRST..l_tgt_res_asg_id_tab.LAST
4399 INSERT INTO PA_BUDGET_LINES (
4400 BUDGET_LINE_ID,
4401 BUDGET_VERSION_ID,
4402 RESOURCE_ASSIGNMENT_ID,
4403 START_DATE,
4404 TXN_CURRENCY_CODE,
4405 TXN_RAW_COST,
4406 TXN_BURDENED_COST,
4407 END_DATE,
4408 PERIOD_NAME,
4409 QUANTITY,
4410 LAST_UPDATE_DATE,
4411 LAST_UPDATED_BY,
4412 CREATION_DATE,
4413 CREATED_BY,
4414 LAST_UPDATE_LOGIN,
4415 PROJECT_CURRENCY_CODE,
4416 PROJFUNC_CURRENCY_CODE,
4417 TXN_COST_RATE_OVERRIDE,
4418 BURDEN_COST_RATE_OVERRIDE,
4419 RAW_COST_SOURCE,
4420 BURDENED_COST_SOURCE,
4421 QUANTITY_SOURCE)
4422 VALUES (
4423 pa_budget_lines_s.nextval,
4424 P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
4425 l_tgt_res_asg_id_tab(i),
4426 l_start_date_tab(i),
4427 l_txn_currency_code_tab(i),
4428 l_txn_raw_cost_tab(i),
4429 l_txn_brdn_cost_tab(i),
4430 l_end_date_tab(i),
4431 l_period_name_tab(i),
4432 l_src_quantity_tab(i),
4433 sysdate,
4434 FND_GLOBAL.USER_ID,
4435 sysdate,
4436 FND_GLOBAL.USER_ID,
4437 FND_GLOBAL.LOGIN_ID,
4438 P_FP_COLS_TGT_REC.X_PROJECT_CURRENCY_CODE,
4439 P_FP_COLS_TGT_REC.X_PROJFUNC_CURRENCY_CODE,
4440 l_cost_rate_override_tab(i),
4441 l_b_cost_rate_override_tab(i),
4442 'SP',
4443 'SP',
4444 'SP');
4445
4446 END IF; --l_tgt_res_asg_id_tab.count > 0
4447
4448 ELSIF l_copy_lines = 'WORKPLAN_RESOURCES' THEN
4449 OPEN fcst_budget_line_src_tgt_ptl
4450 (P_FP_COLS_TGT_REC.x_project_currency_code,
4451 P_FP_COLS_TGT_REC.x_projfunc_currency_code,
4452 P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
4453 P_FP_COLS_TGT_REC.x_project_id,
4454 l_copy_lines);
4455 FETCH fcst_budget_line_src_tgt_ptl
4456 BULK COLLECT
4457 INTO l_tgt_res_asg_id_tab,
4458 l_tgt_rate_based_flag_tab,
4459 l_start_date_tab,
4460 l_end_date_tab,
4461 l_period_name_tab,
4462 l_txn_currency_code_tab,
4463 l_src_quantity_tab,
4464 l_txn_raw_cost_tab,
4465 l_txn_brdn_cost_tab,
4466 l_unround_txn_raw_cost_tab,
4467 l_unround_txn_brdn_cost_tab,
4468 l_cost_rate_override_tab,
4469 l_b_cost_rate_override_tab;
4470 CLOSE fcst_budget_line_src_tgt_ptl;
4471
4472 IF l_tgt_res_asg_id_tab.count > 0 THEN
4473
4474 FOR i in 1..l_tgt_res_asg_id_tab.count LOOP
4475 l_override_quantity := l_src_quantity_tab(i);
4476 IF l_tgt_rate_based_flag_tab(i) = 'N' THEN
4477 l_src_quantity_tab(i):= l_txn_raw_cost_tab(i);
4478 l_override_quantity := l_unround_txn_raw_cost_tab(i);
4479 END IF;
4480 IF l_override_quantity <> 0 THEN
4481 l_cost_rate_override_tab(i) := l_unround_txn_raw_cost_tab(i) / l_override_quantity;
4482 l_b_cost_rate_override_tab(i) := l_unround_txn_brdn_cost_tab(i) / l_override_quantity;
4483 IF l_tgt_rate_based_flag_tab(i) = 'N' THEN
4484 l_cost_rate_override_tab(i) := 1;
4485 END IF;
4486 END IF;
4487 END LOOP;
4488
4489
4490 FORALL i IN l_tgt_res_asg_id_tab.FIRST..l_tgt_res_asg_id_tab.LAST
4491 INSERT INTO PA_BUDGET_LINES (
4492 BUDGET_LINE_ID,
4493 BUDGET_VERSION_ID,
4494 RESOURCE_ASSIGNMENT_ID,
4495 START_DATE,
4496 TXN_CURRENCY_CODE,
4497 TXN_RAW_COST,
4498 TXN_BURDENED_COST,
4499 END_DATE,
4500 PERIOD_NAME,
4501 QUANTITY,
4502 LAST_UPDATE_DATE,
4503 LAST_UPDATED_BY,
4504 CREATION_DATE,
4505 CREATED_BY,
4506 LAST_UPDATE_LOGIN,
4507 PROJECT_CURRENCY_CODE,
4508 PROJFUNC_CURRENCY_CODE,
4509 TXN_COST_RATE_OVERRIDE,
4510 BURDEN_COST_RATE_OVERRIDE,
4511 RAW_COST_SOURCE,
4512 BURDENED_COST_SOURCE,
4513 QUANTITY_SOURCE)
4514 VALUES (
4515 pa_budget_lines_s.nextval,
4516 P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
4517 l_tgt_res_asg_id_tab(i),
4518 l_start_date_tab(i),
4519 l_txn_currency_code_tab(i),
4520 l_txn_raw_cost_tab(i),
4521 l_txn_brdn_cost_tab(i),
4522 l_end_date_tab(i),
4523 l_period_name_tab(i),
4524 l_src_quantity_tab(i),
4525 sysdate,
4526 FND_GLOBAL.USER_ID,
4527 sysdate,
4528 FND_GLOBAL.USER_ID,
4529 FND_GLOBAL.LOGIN_ID,
4530 P_FP_COLS_TGT_REC.X_PROJECT_CURRENCY_CODE,
4531 P_FP_COLS_TGT_REC.X_PROJFUNC_CURRENCY_CODE,
4532 l_cost_rate_override_tab(i),
4533 l_b_cost_rate_override_tab(i),
4534 'SP',
4535 'SP',
4536 'SP');
4537
4538 END IF; -- l_tgt_res_asg_id_tab.count > 0
4539
4540 END IF; -- l_copy_lines = 'ALL'
4541
4542 -- Till now copying of budgetlines directly from source is done. Now we need to prorate the data or accumulate
4543 -- same based on source and dest periods.
4544
4545 IF l_copy_lines <> 'ALL' THEN
4546
4547 -- getting planning end date to cache periods temp table only for required span.
4548 -- Doing this processing to avoid unnecessary periods being pulled.
4549 BEGIN
4550 SELECT MAX(PLANNING_END_DATE)
4551 INTO l_end_date
4552 FROM PA_FP_CALC_AMT_TMP1;
4553 EXCEPTION
4554 WHEN NO_DATA_FOUND THEN
4555 SELECT MAX(pbl.end_date)
4556 INTO l_end_date
4557 FROM PA_BUDGET_LINES pbl,
4558 PA_FP_CALC_AMT_TMP2 tmp
4559 WHERE tmp.resource_assignment_id = pbl.resource_assignment_id ;
4560 END;
4561
4562 PROCESS_PA_GL_DATES( p_start_date => P_ACTUALS_THRU_DATE
4563 ,p_end_date => l_end_date
4564 ,p_org_id => P_FP_COLS_TGT_REC.X_ORG_ID
4565 ,X_GL_GREATER_FLAG => l_is_gl_greater
4566 ,X_RETURN_STATUS => x_return_status
4567 ,X_MSG_COUNT => x_msg_count
4568 ,X_MSG_DATA => x_msg_data);
4569
4570 END IF;
4571
4572 -- Checking now to see if we need to distribute the source lines or club.
4573 IF P_FP_COLS_TGT_REC.X_TIME_PHASED_CODE = 'G' AND l_is_gl_greater = 'Y' THEN
4574 l_dist_amounts := 'N';
4575 ELSIF P_FP_COLS_TGT_REC.X_TIME_PHASED_CODE = 'P' AND l_is_gl_greater = 'Y' THEN
4576 l_dist_amounts := 'Y';
4577 ELSIF P_FP_COLS_TGT_REC.X_TIME_PHASED_CODE = 'G' AND l_is_gl_greater = 'N' THEN
4578 l_dist_amounts := 'Y';
4579 ELSIF P_FP_COLS_TGT_REC.X_TIME_PHASED_CODE = 'P' AND l_is_gl_greater = 'N' THEN
4580 l_dist_amounts := 'N';
4581 ELSE
4582 l_dist_amounts := 'N';
4583 END IF;
4584
4585
4586 IF l_copy_lines = 'NONE' THEN
4587
4588 IF l_dist_amounts = 'N' THEN
4589 OPEN fcst_bdgt_line_src_tgt_sum
4590 (P_FP_COLS_TGT_REC.x_project_currency_code,
4591 P_FP_COLS_TGT_REC.x_projfunc_currency_code,
4592 P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
4593 P_FP_COLS_TGT_REC.x_project_id,
4594 P_FP_COLS_SRC_WP_REC.x_time_phased_code);
4595 FETCH fcst_bdgt_line_src_tgt_sum
4596 BULK COLLECT
4597 INTO l_pr_tgt_res_asg_id_tab,
4598 l_pr_tgt_rate_based_flag_tab,
4599 l_pr_start_date_tab,
4600 l_pr_end_date_tab,
4601 l_pr_period_name_tab,
4602 l_pr_txn_currency_code_tab,
4603 l_pr_src_quantity_tab,
4604 l_pr_txn_raw_cost_tab,
4605 l_pr_txn_brdn_cost_tab,
4606 l_pr_unround_txn_raw_cost_tab,
4607 l_pr_unround_txn_brdn_cost_tab,
4608 l_pr_cost_rate_override_tab,
4609 l_pr_b_cost_rate_override_tab;
4610 CLOSE fcst_bdgt_line_src_tgt_sum;
4611 ELSE
4612
4613 OPEN fcst_bdgt_line_src_tgt_dist
4614 (P_FP_COLS_TGT_REC.x_project_currency_code,
4615 P_FP_COLS_TGT_REC.x_projfunc_currency_code,
4616 P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
4617 P_FP_COLS_TGT_REC.x_project_id,
4618 P_FP_COLS_SRC_WP_REC.x_time_phased_code);
4619 FETCH fcst_bdgt_line_src_tgt_dist
4620 BULK COLLECT
4621 INTO l_pr_tgt_res_asg_id_tab,
4622 l_pr_tgt_rate_based_flag_tab,
4623 l_pr_start_date_tab,
4624 l_pr_end_date_tab,
4625 l_pr_period_name_tab,
4626 l_pr_txn_currency_code_tab,
4627 l_pr_src_quantity_tab,
4628 l_pr_txn_raw_cost_tab,
4629 l_pr_txn_brdn_cost_tab,
4630 l_pr_unround_txn_raw_cost_tab,
4631 l_pr_unround_txn_brdn_cost_tab,
4632 l_pr_cost_rate_override_tab,
4633 l_pr_b_cost_rate_override_tab;
4634 CLOSE fcst_bdgt_line_src_tgt_dist;
4635
4636 END IF; --l_dist_amounts = 'N'
4637
4638 IF l_pr_tgt_res_asg_id_tab.count >0 THEN
4639 FOR i in 1..l_pr_tgt_res_asg_id_tab.count LOOP
4640 l_override_quantity := l_pr_src_quantity_tab(i);
4641 IF l_pr_tgt_rate_based_flag_tab(i) = 'N' THEN
4642 l_pr_src_quantity_tab(i):= l_pr_txn_raw_cost_tab(i);
4643 l_override_quantity := l_pr_unround_txn_raw_cost_tab(i);
4644 END IF;
4645 IF l_override_quantity <> 0 THEN
4646 l_pr_cost_rate_override_tab(i) := l_pr_unround_txn_raw_cost_tab(i) / l_override_quantity;
4647 l_pr_b_cost_rate_override_tab(i) := l_pr_unround_txn_brdn_cost_tab(i) / l_override_quantity;
4648 IF l_pr_tgt_rate_based_flag_tab(i) = 'N' THEN
4649 l_pr_cost_rate_override_tab(i) := 1;
4650 END IF;
4651 END IF;
4652 END LOOP;
4653
4654
4655 FORALL i IN l_pr_tgt_res_asg_id_tab.FIRST..l_pr_tgt_res_asg_id_tab.LAST
4656 INSERT INTO PA_BUDGET_LINES (
4657 BUDGET_LINE_ID,
4658 BUDGET_VERSION_ID,
4659 RESOURCE_ASSIGNMENT_ID,
4660 START_DATE,
4661 TXN_CURRENCY_CODE,
4662 TXN_RAW_COST,
4663 TXN_BURDENED_COST,
4664 END_DATE,
4665 PERIOD_NAME,
4666 QUANTITY,
4667 LAST_UPDATE_DATE,
4668 LAST_UPDATED_BY,
4669 CREATION_DATE,
4670 CREATED_BY,
4671 LAST_UPDATE_LOGIN,
4672 PROJECT_CURRENCY_CODE,
4673 PROJFUNC_CURRENCY_CODE,
4674 TXN_COST_RATE_OVERRIDE,
4675 BURDEN_COST_RATE_OVERRIDE,
4676 RAW_COST_SOURCE,
4677 BURDENED_COST_SOURCE,
4678 QUANTITY_SOURCE)
4679 VALUES (
4680 pa_budget_lines_s.nextval,
4681 P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
4682 l_pr_tgt_res_asg_id_tab(i),
4683 l_pr_start_date_tab(i),
4684 l_pr_txn_currency_code_tab(i),
4685 l_pr_txn_raw_cost_tab(i),
4686 l_pr_txn_brdn_cost_tab(i),
4687 l_pr_end_date_tab(i),
4688 l_pr_period_name_tab(i),
4689 l_pr_src_quantity_tab(i),
4690 sysdate,
4691 FND_GLOBAL.USER_ID,
4692 sysdate,
4693 FND_GLOBAL.USER_ID,
4694 FND_GLOBAL.LOGIN_ID,
4695 P_FP_COLS_TGT_REC.X_PROJECT_CURRENCY_CODE,
4696 P_FP_COLS_TGT_REC.X_PROJFUNC_CURRENCY_CODE,
4697 l_pr_cost_rate_override_tab(i),
4698 l_pr_b_cost_rate_override_tab(i),
4699 'SP',
4700 'SP',
4701 'SP');
4702 END IF; --IF l_pr_tgt_res_asg_id_tab.count >0 THEN
4703
4704 ELSIF l_copy_lines = 'FINANCIAL_PLAN' THEN
4705
4706 IF l_dist_amounts = 'N' THEN
4707 OPEN fcst_bdgt_line_src_tgt_sum_ptl
4708 (P_FP_COLS_TGT_REC.x_project_currency_code,
4709 P_FP_COLS_TGT_REC.x_projfunc_currency_code,
4710 P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
4711 P_FP_COLS_TGT_REC.x_project_id,
4712 'WORKPLAN_RESOURCES',
4713 P_FP_COLS_SRC_WP_REC.x_time_phased_code);
4714 FETCH fcst_bdgt_line_src_tgt_sum_ptl
4715 BULK COLLECT
4716 INTO l_pr_tgt_res_asg_id_tab,
4717 l_pr_tgt_rate_based_flag_tab,
4718 l_pr_start_date_tab,
4719 l_pr_end_date_tab,
4720 l_pr_period_name_tab,
4721 l_pr_txn_currency_code_tab,
4722 l_pr_src_quantity_tab,
4723 l_pr_txn_raw_cost_tab,
4724 l_pr_txn_brdn_cost_tab,
4725 l_pr_unround_txn_raw_cost_tab,
4726 l_pr_unround_txn_brdn_cost_tab,
4727 l_pr_cost_rate_override_tab,
4728 l_pr_b_cost_rate_override_tab;
4729 CLOSE fcst_bdgt_line_src_tgt_sum_ptl;
4730 ELSE
4731 OPEN fcst_bdgt_line_src_tgt_dist_pt
4732 (P_FP_COLS_TGT_REC.x_project_currency_code,
4733 P_FP_COLS_TGT_REC.x_projfunc_currency_code,
4734 P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
4735 P_FP_COLS_TGT_REC.x_project_id,
4736 'WORKPLAN_RESOURCES',
4737 P_FP_COLS_SRC_WP_REC.x_time_phased_code);
4738 FETCH fcst_bdgt_line_src_tgt_dist_pt
4739 BULK COLLECT
4740 INTO l_pr_tgt_res_asg_id_tab,
4741 l_pr_tgt_rate_based_flag_tab,
4742 l_pr_start_date_tab,
4743 l_pr_end_date_tab,
4744 l_pr_period_name_tab,
4745 l_pr_txn_currency_code_tab,
4746 l_pr_src_quantity_tab,
4747 l_pr_txn_raw_cost_tab,
4748 l_pr_txn_brdn_cost_tab,
4749 l_pr_unround_txn_raw_cost_tab,
4750 l_pr_unround_txn_brdn_cost_tab,
4751 l_pr_cost_rate_override_tab,
4752 l_pr_b_cost_rate_override_tab;
4753 CLOSE fcst_bdgt_line_src_tgt_dist_pt;
4754
4755 END IF; --l_dist_amounts = 'N'
4756
4757 IF l_pr_tgt_res_asg_id_tab.count >0 THEN
4758 FOR i in 1..l_pr_tgt_res_asg_id_tab.count LOOP
4759 l_override_quantity := l_pr_src_quantity_tab(i);
4760 IF l_pr_tgt_rate_based_flag_tab(i) = 'N' THEN
4761 l_pr_src_quantity_tab(i):= l_pr_txn_raw_cost_tab(i);
4762 l_override_quantity := l_pr_unround_txn_raw_cost_tab(i);
4763 END IF;
4764 IF l_override_quantity <> 0 THEN
4765 l_pr_cost_rate_override_tab(i) := l_pr_unround_txn_raw_cost_tab(i) / l_override_quantity;
4766 l_pr_b_cost_rate_override_tab(i) := l_pr_unround_txn_brdn_cost_tab(i) / l_override_quantity;
4767 IF l_pr_tgt_rate_based_flag_tab(i) = 'N' THEN
4768 l_pr_cost_rate_override_tab(i) := 1;
4769 END IF;
4770 END IF;
4771 END LOOP;
4772
4773 -- We have to merge the data for scenario where it is possible that the destination plan is project level then
4774 -- for same resource assignment in destination with different source in that case we will get unique constraint
4775 -- error if we have data for the same period.
4776 FORALL i IN l_pr_tgt_res_asg_id_tab.FIRST..l_pr_tgt_res_asg_id_tab.LAST
4777 MERGE INTO PA_BUDGET_LINES pbl
4778 USING ( SELECT NULL as BUDGET_LINE_ID,
4779 P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID as BUDGET_VERSION_ID,
4780 l_pr_tgt_res_asg_id_tab(i) as RESOURCE_ASSIGNMENT_ID,
4781 l_pr_start_date_tab(i) as START_DATE,
4782 l_pr_txn_currency_code_tab(i) as TXN_CURRENCY_CODE,
4783 l_pr_txn_raw_cost_tab(i) as TXN_RAW_COST,
4784 l_pr_txn_brdn_cost_tab(i) as TXN_BURDENED_COST,
4785 l_pr_end_date_tab(i) as END_DATE,
4786 l_pr_period_name_tab(i) as PERIOD_NAME,
4787 l_pr_src_quantity_tab(i) as QUANTITY,
4788 sysdate as LAST_UPDATE_DATE,
4789 FND_GLOBAL.USER_ID as LAST_UPDATED_BY,
4790 sysdate as CREATION_DATE,
4791 FND_GLOBAL.USER_ID as CREATED_BY,
4792 FND_GLOBAL.LOGIN_ID as LAST_UPDATE_LOGIN,
4793 P_FP_COLS_TGT_REC.X_PROJECT_CURRENCY_CODE as PROJECT_CURRENCY_CODE,
4794 P_FP_COLS_TGT_REC.X_PROJFUNC_CURRENCY_CODE as PROJFUNC_CURRENCY_CODE,
4795 l_pr_cost_rate_override_tab(i) as TXN_COST_RATE_OVERRIDE,
4796 l_pr_b_cost_rate_override_tab(i) as BURDEN_COST_RATE_OVERRIDE ,
4797 'SP' as RAW_COST_SOURCE,
4798 'SP' as BURDENED_COST_SOURCE,
4799 'SP' as QUANTITY_SOURCE
4800 FROM dual) tmp
4801 ON ( tmp.RESOURCE_ASSIGNMENT_ID = pbl.RESOURCE_ASSIGNMENT_ID AND
4802 tmp.START_DATE = pbl.START_DATE AND
4803 tmp.TXN_CURRENCY_CODE = pbl.TXN_CURRENCY_CODE)
4804 WHEN MATCHED THEN
4805 UPDATE
4806 SET pbl.TXN_RAW_COST = nvl(pbl.TXN_RAW_COST,0) + nvl(tmp.TXN_RAW_COST,0)
4807 ,pbl.TXN_BURDENED_COST = nvl(pbl.TXN_BURDENED_COST,0) + nvl(tmp.TXN_BURDENED_COST,0)
4808 ,pbl.QUANTITY = nvl(pbl.QUANTITY,0) + nvl(tmp.QUANTITY,0)
4809 ,pbl.LAST_UPDATE_DATE = sysdate
4810 ,pbl.LAST_UPDATED_BY = FND_GLOBAL.USER_ID
4811 ,pbl.LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
4812 WHEN NOT MATCHED THEN
4813 INSERT (
4814 pbl.BUDGET_LINE_ID,
4815 pbl.BUDGET_VERSION_ID,
4816 pbl.RESOURCE_ASSIGNMENT_ID,
4817 pbl.START_DATE,
4818 pbl.TXN_CURRENCY_CODE,
4819 pbl.TXN_RAW_COST,
4820 pbl.TXN_BURDENED_COST,
4821 pbl.END_DATE,
4822 pbl.PERIOD_NAME,
4823 pbl.QUANTITY,
4824 pbl.LAST_UPDATE_DATE,
4825 pbl.LAST_UPDATED_BY,
4826 pbl.CREATION_DATE,
4827 pbl.CREATED_BY,
4828 pbl.LAST_UPDATE_LOGIN,
4829 pbl.PROJECT_CURRENCY_CODE,
4830 pbl.PROJFUNC_CURRENCY_CODE,
4831 pbl.TXN_COST_RATE_OVERRIDE,
4832 pbl.BURDEN_COST_RATE_OVERRIDE,
4833 pbl.RAW_COST_SOURCE,
4834 pbl.BURDENED_COST_SOURCE,
4835 pbl.QUANTITY_SOURCE)
4836 VALUES (
4837 pa_budget_lines_s.nextval,
4838 tmp.BUDGET_VERSION_ID,
4839 tmp.RESOURCE_ASSIGNMENT_ID,
4840 tmp.START_DATE,
4841 tmp.TXN_CURRENCY_CODE,
4842 tmp.TXN_RAW_COST,
4843 tmp.TXN_BURDENED_COST,
4844 tmp.END_DATE,
4845 tmp.PERIOD_NAME,
4846 tmp.QUANTITY,
4847 tmp.LAST_UPDATE_DATE,
4848 tmp.LAST_UPDATED_BY,
4849 tmp.CREATION_DATE,
4850 tmp.CREATED_BY,
4851 tmp.LAST_UPDATE_LOGIN,
4852 tmp.PROJECT_CURRENCY_CODE,
4853 tmp.PROJFUNC_CURRENCY_CODE,
4854 tmp.TXN_COST_RATE_OVERRIDE,
4855 tmp.BURDEN_COST_RATE_OVERRIDE,
4856 tmp.RAW_COST_SOURCE,
4857 tmp.BURDENED_COST_SOURCE,
4858 tmp.QUANTITY_SOURCE);
4859
4860 END IF; --IF l_pr_tgt_res_asg_id_tab.count >0 THEN
4861
4862 ELSIF l_copy_lines = 'WORKPLAN_RESOURCES' THEN
4863
4864
4865 IF l_dist_amounts = 'N' THEN
4866 OPEN fcst_bdgt_line_src_tgt_sum_ptl
4867 (P_FP_COLS_TGT_REC.x_project_currency_code,
4868 P_FP_COLS_TGT_REC.x_projfunc_currency_code,
4869 P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
4870 P_FP_COLS_TGT_REC.x_project_id,
4871 'FINANCIAL_PLAN',
4872 P_FP_COLS_SRC_FP_REC.x_time_phased_code);
4873 FETCH fcst_bdgt_line_src_tgt_sum_ptl
4874 BULK COLLECT
4875 INTO l_pr_tgt_res_asg_id_tab,
4876 l_pr_tgt_rate_based_flag_tab,
4877 l_pr_start_date_tab,
4878 l_pr_end_date_tab,
4879 l_pr_period_name_tab,
4880 l_pr_txn_currency_code_tab,
4881 l_pr_src_quantity_tab,
4882 l_pr_txn_raw_cost_tab,
4883 l_pr_txn_brdn_cost_tab,
4884 l_pr_unround_txn_raw_cost_tab,
4885 l_pr_unround_txn_brdn_cost_tab,
4886 l_pr_cost_rate_override_tab,
4887 l_pr_b_cost_rate_override_tab;
4888 CLOSE fcst_bdgt_line_src_tgt_sum_ptl;
4889 ELSE
4890 OPEN fcst_bdgt_line_src_tgt_dist_pt
4891 (P_FP_COLS_TGT_REC.x_project_currency_code,
4892 P_FP_COLS_TGT_REC.x_projfunc_currency_code,
4893 P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID,
4894 P_FP_COLS_TGT_REC.x_project_id,
4895 'FINANCIAL_PLAN',
4896 P_FP_COLS_SRC_FP_REC.x_time_phased_code);
4897 FETCH fcst_bdgt_line_src_tgt_dist_pt
4898 BULK COLLECT
4899 INTO l_pr_tgt_res_asg_id_tab,
4900 l_pr_tgt_rate_based_flag_tab,
4901 l_pr_start_date_tab,
4902 l_pr_end_date_tab,
4903 l_pr_period_name_tab,
4904 l_pr_txn_currency_code_tab,
4905 l_pr_src_quantity_tab,
4906 l_pr_txn_raw_cost_tab,
4907 l_pr_txn_brdn_cost_tab,
4908 l_pr_unround_txn_raw_cost_tab,
4909 l_pr_unround_txn_brdn_cost_tab,
4910 l_pr_cost_rate_override_tab,
4911 l_pr_b_cost_rate_override_tab;
4912 CLOSE fcst_bdgt_line_src_tgt_dist_pt;
4913
4914 END IF; --l_dist_amounts = 'N'
4915
4916 IF l_pr_tgt_res_asg_id_tab.count >0 THEN
4917
4918 FOR i in 1..l_pr_tgt_res_asg_id_tab.count LOOP
4919 l_override_quantity := l_pr_src_quantity_tab(i);
4920 IF l_pr_tgt_rate_based_flag_tab(i) = 'N' THEN
4921 l_pr_src_quantity_tab(i):= l_pr_txn_raw_cost_tab(i);
4922 l_override_quantity := l_pr_unround_txn_raw_cost_tab(i);
4923 END IF;
4924 IF l_override_quantity <> 0 THEN
4925 l_pr_cost_rate_override_tab(i) := l_pr_unround_txn_raw_cost_tab(i) / l_override_quantity;
4926 l_pr_b_cost_rate_override_tab(i) := l_pr_unround_txn_brdn_cost_tab(i) / l_override_quantity;
4927 IF l_pr_tgt_rate_based_flag_tab(i) = 'N' THEN
4928 l_pr_cost_rate_override_tab(i) := 1;
4929 END IF;
4930 END IF;
4931 END LOOP;
4932
4933
4934 -- We have to merge the data for scenario where it is possible that the destination plan is project level then
4935 -- for same resource assignment in destination with different source in that case we will get unique constraint
4936 -- error if we have data for the same period.
4937 FORALL i IN l_pr_tgt_res_asg_id_tab.FIRST..l_pr_tgt_res_asg_id_tab.LAST
4938 MERGE INTO PA_BUDGET_LINES pbl
4939 USING ( SELECT NULL as BUDGET_LINE_ID,
4940 P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID as BUDGET_VERSION_ID,
4941 l_pr_tgt_res_asg_id_tab(i) as RESOURCE_ASSIGNMENT_ID,
4942 l_pr_start_date_tab(i) as START_DATE,
4943 l_pr_txn_currency_code_tab(i) as TXN_CURRENCY_CODE,
4944 l_pr_txn_raw_cost_tab(i) as TXN_RAW_COST,
4945 l_pr_txn_brdn_cost_tab(i) as TXN_BURDENED_COST,
4946 l_pr_end_date_tab(i) as END_DATE,
4947 l_pr_period_name_tab(i) as PERIOD_NAME,
4948 l_pr_src_quantity_tab(i) as QUANTITY,
4949 sysdate as LAST_UPDATE_DATE,
4950 FND_GLOBAL.USER_ID as LAST_UPDATED_BY,
4951 sysdate as CREATION_DATE,
4952 FND_GLOBAL.USER_ID as CREATED_BY,
4953 FND_GLOBAL.LOGIN_ID as LAST_UPDATE_LOGIN,
4954 P_FP_COLS_TGT_REC.X_PROJECT_CURRENCY_CODE as PROJECT_CURRENCY_CODE,
4955 P_FP_COLS_TGT_REC.X_PROJFUNC_CURRENCY_CODE as PROJFUNC_CURRENCY_CODE,
4956 l_pr_cost_rate_override_tab(i) as TXN_COST_RATE_OVERRIDE,
4957 l_pr_b_cost_rate_override_tab(i) as BURDEN_COST_RATE_OVERRIDE,
4958 'SP' as RAW_COST_SOURCE,
4959 'SP' as BURDENED_COST_SOURCE,
4960 'SP' as QUANTITY_SOURCE
4961 FROM dual) tmp
4962 ON ( tmp.RESOURCE_ASSIGNMENT_ID = pbl.RESOURCE_ASSIGNMENT_ID AND
4963 tmp.START_DATE = pbl.START_DATE AND
4964 tmp.TXN_CURRENCY_CODE = pbl.TXN_CURRENCY_CODE)
4965 WHEN MATCHED THEN
4966 UPDATE
4967 SET pbl.TXN_RAW_COST = nvl(pbl.TXN_RAW_COST,0) + nvl(tmp.TXN_RAW_COST,0)
4968 ,pbl.TXN_BURDENED_COST = nvl(pbl.TXN_BURDENED_COST,0) + nvl(tmp.TXN_BURDENED_COST,0)
4969 ,pbl.QUANTITY = nvl(pbl.QUANTITY,0) + nvl(tmp.QUANTITY,0)
4970 ,pbl.LAST_UPDATE_DATE = sysdate
4971 ,pbl.LAST_UPDATED_BY = FND_GLOBAL.USER_ID
4972 ,pbl.LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
4973 WHEN NOT MATCHED THEN
4974 INSERT (
4975 pbl.BUDGET_LINE_ID,
4976 pbl.BUDGET_VERSION_ID,
4977 pbl.RESOURCE_ASSIGNMENT_ID,
4978 pbl.START_DATE,
4979 pbl.TXN_CURRENCY_CODE,
4980 pbl.TXN_RAW_COST,
4981 pbl.TXN_BURDENED_COST,
4982 pbl.END_DATE,
4983 pbl.PERIOD_NAME,
4984 pbl.QUANTITY,
4985 pbl.LAST_UPDATE_DATE,
4986 pbl.LAST_UPDATED_BY,
4987 pbl.CREATION_DATE,
4988 pbl.CREATED_BY,
4989 pbl.LAST_UPDATE_LOGIN,
4990 pbl.PROJECT_CURRENCY_CODE,
4991 pbl.PROJFUNC_CURRENCY_CODE,
4992 pbl.TXN_COST_RATE_OVERRIDE,
4993 pbl.BURDEN_COST_RATE_OVERRIDE,
4994 pbl.RAW_COST_SOURCE,
4995 pbl.BURDENED_COST_SOURCE,
4996 pbl.QUANTITY_SOURCE)
4997 VALUES (
4998 pa_budget_lines_s.nextval,
4999 tmp.BUDGET_VERSION_ID,
5000 tmp.RESOURCE_ASSIGNMENT_ID,
5001 tmp.START_DATE,
5002 tmp.TXN_CURRENCY_CODE,
5003 tmp.TXN_RAW_COST,
5004 tmp.TXN_BURDENED_COST,
5005 tmp.END_DATE,
5006 tmp.PERIOD_NAME,
5007 tmp.QUANTITY,
5008 tmp.LAST_UPDATE_DATE,
5009 tmp.LAST_UPDATED_BY,
5010 tmp.CREATION_DATE,
5011 tmp.CREATED_BY,
5012 tmp.LAST_UPDATE_LOGIN,
5013 tmp.PROJECT_CURRENCY_CODE,
5014 tmp.PROJFUNC_CURRENCY_CODE,
5015 tmp.TXN_COST_RATE_OVERRIDE,
5016 tmp.BURDEN_COST_RATE_OVERRIDE,
5017 tmp.RAW_COST_SOURCE,
5018 tmp.BURDENED_COST_SOURCE,
5019 tmp.QUANTITY_SOURCE);
5020
5021 END IF; --IF l_pr_tgt_res_asg_id_tab.count >0 THEN
5022
5023 END IF; -- l_copy_lines = 'NONE'
5024
5025 -- Processing for pa_res_curr table:
5026
5027 DELETE pa_resource_asgn_curr_tmp;
5028
5029 INSERT INTO pa_resource_asgn_curr_tmp
5030 ( resource_assignment_id,
5031 txn_currency_code,
5032 txn_raw_cost_rate_override,
5033 txn_burden_cost_rate_override,
5034 txn_bill_rate_override )
5035 SELECT DISTINCT
5036 bl.resource_assignment_id,
5037 bl.txn_currency_code,
5038 rbc.txn_raw_cost_rate_override,
5039 rbc.txn_burden_cost_rate_override,
5040 rbc.txn_bill_rate_override
5041 FROM pa_resource_assignments ra,
5042 pa_budget_lines bl,
5043 pa_resource_asgn_curr rbc,
5044 PA_FP_CALC_AMT_TMP2 tmp4
5045 WHERE ra.budget_version_id = P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID
5046 AND ra.project_id = P_FP_COLS_TGT_REC.x_project_id
5047 AND ra.resource_assignment_id = tmp4.target_res_asg_id
5048 AND bl.resource_assignment_id = ra.resource_assignment_id
5049 AND bl.resource_assignment_id = rbc.resource_assignment_id (+)
5050 AND bl.txn_currency_code = rbc.txn_currency_code (+);
5051
5052
5053 -- Call the maintenance api in ROLLUP mode
5054 IF p_pa_debug_mode = 'Y' THEN
5055 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
5056 P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.' ||
5057 'MAINTAIN_DATA',
5058 P_MODULE_NAME => l_module_name);
5059 END IF;
5060 PA_RES_ASG_CURRENCY_PUB.MAINTAIN_DATA
5061 ( P_FP_COLS_REC => P_FP_COLS_TGT_REC,
5062 P_CALLING_MODULE => 'FORECAST_GENERATION',
5063 P_VERSION_LEVEL_FLAG => 'N',
5064 P_ROLLUP_FLAG => 'Y',
5065 X_RETURN_STATUS => x_return_status,
5066 X_MSG_COUNT => x_msg_count,
5067 X_MSG_DATA => x_msg_data );
5068 IF p_pa_debug_mode = 'Y' THEN
5069 PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
5070 P_MSG => 'After calling PA_RES_ASG_CURRENCY_PUB.' ||
5071 'MAINTAIN_DATA: '||x_return_status,
5072 P_MODULE_NAME => l_module_name);
5073 END IF;
5074 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5075 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
5076 END IF;
5077
5078 -- Bug 8346446 AAI QA
5079 -- Not pulling the source assigment id in etc2 coz it could be possible that the dest is at top task level
5080 -- or project or structure could be mapped or split in those cases the amounts would be doubled or trippled
5081 -- if the destination assignment id is mapped to multiple source asssgn ids.
5082 -- Pulling distinct records to avoid duplicates coz etc should be processed only for target res ids.
5083 INSERT INTO PA_FP_CALC_AMT_TMP2
5084 ( --RESOURCE_ASSIGNMENT_ID, -- Bug 8346446
5085 TARGET_RES_ASG_ID,
5086 ETC_CURRENCY_CODE,
5087 ETC_PLAN_QUANTITY,
5088 ETC_TXN_RAW_COST,
5089 ETC_TXN_BURDENED_COST,
5090 TRANSACTION_SOURCE_CODE
5091 )
5092 SELECT --tmp4.resource_assignment_id, -- bug 8346446
5093 distinct
5094 ra.resource_assignment_id,
5095 sbl.txn_currency_code,
5096 sum(sbl.quantity),
5097 sum(sbl.txn_raw_cost),
5098 sum(sbl.txn_burdened_cost),
5099 'ETC'
5100 FROM PA_FP_CALC_AMT_TMP2 tmp4,
5101 pa_budget_lines sbl,
5102 pa_resource_assignments ra
5103 WHERE tmp4.TARGET_RES_ASG_ID = ra.resource_assignment_id
5104 AND sbl.resource_assignment_id=ra.resource_assignment_id
5105 AND ra.budget_version_id = P_FP_COLS_TGT_REC.X_BUDGET_VERSION_ID
5106 AND ra.project_id = P_FP_COLS_TGT_REC.x_project_id
5107 AND ra.budget_version_id = sbl.budget_version_id
5108 AND sbl.init_quantity IS NULL
5109 GROUP BY
5110 tmp4.resource_assignment_id,
5111 ra.resource_assignment_id,
5112 sbl.txn_currency_code,
5113 'ETC';
5114
5115 IF P_PA_DEBUG_MODE = 'Y' THEN
5116 PA_DEBUG.RESET_CURR_FUNCTION;
5117 END IF;
5118 EXCEPTION
5119 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
5120 l_msg_count := FND_MSG_PUB.count_msg;
5121 IF l_msg_count = 1 THEN
5122 PA_INTERFACE_UTILS_PUB.get_messages
5123 ( p_encoded => FND_API.G_TRUE,
5124 p_msg_index => 1,
5125 p_msg_count => l_msg_count,
5126 p_msg_data => l_msg_data,
5127 p_data => l_data,
5128 p_msg_index_out => l_msg_index_out);
5129 x_msg_data := l_data;
5130 x_msg_count := l_msg_count;
5131 ELSE
5132 x_msg_count := l_msg_count;
5133 END IF;
5134
5135 ROLLBACK;
5136
5137 x_return_status := FND_API.G_RET_STS_ERROR;
5138 IF P_PA_DEBUG_MODE = 'Y' THEN
5139 pa_fp_gen_amount_utils.fp_debug
5140 ( p_msg => 'Invalid Arguments Passed',
5141 p_module_name => l_module_name,
5142 p_log_level => 5);
5143 PA_DEBUG.RESET_CURR_FUNCTION;
5144 END IF;
5145 RAISE;
5146
5147 WHEN OTHERS then
5148 ROLLBACK;
5149 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5150 x_msg_count := 1;
5151 x_msg_data := substr(sqlerrm,1,240);
5152 FND_MSG_PUB.add_exc_msg
5153 ( p_pkg_name => 'PA_FP_GEN_FCST_AMT_PUB3',
5154 p_procedure_name => 'GEN_ETC_FROM_SRC_BDGT',
5155 p_error_text => substr(sqlerrm,1,240));
5156
5157 IF P_PA_DEBUG_MODE = 'Y' THEN
5158 pa_fp_gen_amount_utils.fp_debug
5159 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
5160 p_module_name => l_module_name,
5161 p_log_level => 5);
5162 PA_DEBUG.RESET_CURR_FUNCTION;
5163 END IF;
5164 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5165 END GET_ETC_FROM_SRC_BDGT;
5166
5167
5168 -- skkoppul added for AAI Requirement - start
5169 /*****************************************************************************
5170 ** This procedure populates PA_GL_PA_PERIODS_TMP temporary table with all **
5171 ** the PA periods to GL period mapping and the conversion mutiplier so **
5172 ** that when converting from larger period like Month to smaller period **
5173 ** like week, the conversion multiplier can be used to distribute amounts **
5174 ** evenly. This procedure only stores the mapping entities for the lowest **
5175 ** start and greatest end dates of the plan. **
5176 *****************************************************************************/
5177 PROCEDURE PROCESS_PA_GL_DATES
5178 (
5179 p_start_date IN DATE,
5180 p_end_date IN DATE,
5181 p_org_id IN NUMBER,
5182 X_GL_GREATER_FLAG OUT NOCOPY VARCHAR2,
5183 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
5184 X_MSG_COUNT OUT NOCOPY NUMBER,
5185 X_MSG_DATA OUT NOCOPY VARCHAR2
5186 )
5187 IS
5188 l_is_gl_greater VARCHAR2(1) := 'N';
5189 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_FCST_AMT_PUB3.PROCESS_PA_GL_DATES';
5190
5191 BEGIN
5192
5193 -- Get PA to GL mapping periods along with their period names and dates
5194 -- during a time period using the start and end dates
5195 INSERT
5196 INTO PA_GL_PA_PERIODS_TMP
5197 (
5198 PA_PERIOD_NAME ,
5199 GL_PERIOD_NAME ,
5200 PA_START_DATE ,
5201 PA_END_DATE ,
5202 GL_START_DATE ,
5203 GL_END_DATE
5204 )
5205 (SELECT PAP.PERIOD_NAME ,
5206 PAP.GL_PERIOD_NAME,
5207 PAP.START_DATE ,
5208 PAP.END_DATE ,
5209 GLP.START_DATE ,
5210 GLP.END_DATE
5211 FROM PA_PERIODS_ALL PAP ,
5212 GL_PERIODS GLP ,
5213 GL_SETS_OF_BOOKS GSOB ,
5214 PA_IMPLEMENTATIONS_ALL PAIMP
5215 WHERE PAP.GL_PERIOD_NAME = GLP.PERIOD_NAME
5216 AND GLP.PERIOD_SET_NAME = GSOB.PERIOD_SET_NAME
5217 AND GSOB.SET_OF_BOOKS_ID = PAIMP.SET_OF_BOOKS_ID
5218 AND p_start_date <= LEAST(PAP.END_DATE,GLP.END_DATE)
5219 AND p_end_date >= GREATEST(PAP.START_DATE,GLP.START_DATE)
5220 AND PAIMP.org_id = PAP.org_id
5221 AND PAP.org_id = p_org_id
5222 );
5223
5224 -- check if which period has a bigger time unit ex: GL is defined as Monthly
5225 -- and PA periods are Weekly, GL period is greater than PA Period
5226 BEGIN
5227 SELECT 'Y'
5228 INTO l_is_gl_greater
5229 FROM
5230 (SELECT COUNT(*)
5231 FROM PA_GL_PA_PERIODS_TMP
5232 GROUP BY GL_PERIOD_NAME
5233 HAVING COUNT(*) > 1
5234 )
5235 WHERE rownum = 1;
5236 EXCEPTION
5237 WHEN NO_DATA_FOUND THEN
5238 l_is_gl_greater := 'N';
5239 END;
5240
5241 X_GL_GREATER_FLAG := l_is_gl_greater;
5242 -- whichever is the greater time unit, derive a mutiplier for each period
5243 -- by looking into how many smaller periods fall into the larger period
5244 IF l_is_gl_greater = 'Y' THEN
5245
5246 UPDATE PA_GL_PA_PERIODS_TMP tmp1
5247 SET multiplier =
5248 (SELECT COUNT(*)
5249 FROM PA_GL_PA_PERIODS_TMP tmp2
5250 WHERE tmp1.GL_PERIOD_NAME = tmp2.GL_PERIOD_NAME
5251 GROUP BY GL_PERIOD_NAME
5252 );
5253 ELSE
5254 -- two cases where l_is_gl_greater is 'N'
5255 -- 1) GL periods (week) have lesser time unit than PA period (month)
5256 -- 2) GL periods (month) have same time unit as PA period (month)
5257 UPDATE PA_GL_PA_PERIODS_TMP tmp1
5258 SET multiplier =
5259 (SELECT COUNT(*)
5260 FROM PA_GL_PA_PERIODS_TMP tmp2
5261 WHERE tmp1.PA_PERIOD_NAME = tmp2.PA_PERIOD_NAME
5262 GROUP BY PA_PERIOD_NAME
5263 );
5264 END IF;
5265
5266 IF P_PA_DEBUG_MODE = 'Y' THEN
5267 PA_DEBUG.RESET_CURR_FUNCTION;
5268 END IF;
5269
5270 EXCEPTION
5271 WHEN OTHERS THEN
5272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5273 rollback;
5274 x_msg_data := SUBSTR(SQLERRM,1,240);
5275 FND_MSG_PUB.add_exc_msg
5276 ( p_pkg_name => 'PA_FP_GEN_FCST_AMT_PUB3'
5277 ,p_procedure_name => 'PROCESS_PA_GL_DATES');
5278 IF P_PA_DEBUG_MODE = 'Y' THEN
5279 pa_fp_gen_amount_utils.fp_debug
5280 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
5281 p_module_name => l_module_name,
5282 p_log_level => 5);
5283 PA_DEBUG.Reset_Curr_Function;
5284 END IF;
5285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5286 END PROCESS_PA_GL_DATES;
5287 -- gboomina added for AAI Requirement bug 8318932 - end
5288
5289 END PA_FP_GEN_FCST_AMT_PUB3;
[Home] [Help]