[Home] [Help]
PACKAGE BODY: APPS.PA_FP_GEN_FCST_AMT_PUB5
Source
1 PACKAGE body PA_FP_GEN_FCST_AMT_PUB5 as
2 /* $Header: PAFPFG5B.pls 120.2.12020000.2 2013/05/17 09:56:25 bpottipa ship $ */
3
4 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 PROCEDURE GET_ETC_EARNED_VALUE_AMTS (
7 P_SRC_RES_ASG_ID IN PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE,
8 P_TGT_RES_ASG_ID IN PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE,
9 P_FP_COLS_SRC_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
10 P_FP_COLS_TGT_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
11 P_TASK_ID IN PA_TASKS.TASK_ID%TYPE,
12 P_RESOURCE_LIST_MEMBER_ID IN PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_MEMBER_ID%TYPE,
13 P_ETC_SOURCE_CODE IN PA_TASKS.GEN_ETC_SOURCE_CODE%TYPE,
14 P_WP_STRUCTURE_VERSION_ID IN PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE,
15 P_ACTUALS_THRU_DATE IN PA_PERIODS_ALL.END_DATE%TYPE,
16 P_PLANNING_OPTIONS_FLAG IN VARCHAR2,
17 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
18 X_MSG_COUNT OUT NOCOPY NUMBER,
19 X_MSG_DATA OUT NOCOPY VARCHAR2)
20 IS
21 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_FCST_AMT_PUB5.GET_ETC_EARNED_VALUE_AMTS';
22
23 l_structure_type VARCHAR2(30):= null;
24 l_structure_status VARCHAR2(30):= null;
25 l_structure_status_flag VARCHAR2(1):= null;
26 l_wp_structure_version_id NUMBER;
27 lx_percent_complete NUMBER;
28 l_percent_complete NUMBER;
29
30 l_rate_based_flag VARCHAR2(1);
31 l_currency_flag VARCHAR2(30);
32 l_currency_count_flag VARCHAR2(1);
33 l_pc_currency_code pa_projects_all.project_currency_code%type;
34 l_pfc_currency_code pa_projects_all.project_currency_code%type;
35 l_rev_gen_method VARCHAR2(3);
36
37 /*For workplan actuals*/
38 lx_act_quantity NUMBER;
39 lx_act_txn_currency_code VARCHAR2(30);
40 lx_act_txn_raw_cost NUMBER;
41 lx_act_txn_brdn_cost NUMBER;
42 lx_act_pc_raw_cost NUMBER;
43 lx_act_pc_brdn_cost NUMBER;
44 lx_act_pfc_raw_cost NUMBER;
45 lx_act_pfc_brdn_cost NUMBER;
46
47 l_act_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
48 l_act_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
49 l_act_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
50 l_act_brdn_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
51 l_act_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
52 l_target_version_type pa_budget_versions.version_type%type;
53
54 l_etc_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
55
56 /*For average rates*/
57 l_pc_rate_quantity NUMBER;
58 l_pc_rate_raw_cost NUMBER;
59 l_pc_rate_brdn_cost NUMBER;
60 l_pc_rate_revenue NUMBER;
61 l_pfc_rate_raw_cost NUMBER;
62 l_pfc_rate_brdn_cost NUMBER;
63 l_pfc_rate_revenue NUMBER;
64
65 l_txn_rate_quantity NUMBER;
66 l_txn_rate_raw_cost NUMBER;
67 l_txn_rate_brdn_cost NUMBER;
68 l_txn_rate_revenue NUMBER;
69
70 l_txn_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
71 l_txn_brdn_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
72 l_txn_revenue_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
73 l_pc_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
74 l_pc_brdn_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
75 l_pc_revenue_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
76 l_pfc_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
77 l_pfc_brdn_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
78 l_pfc_revenue_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
79 l_transaction_source_code VARCHAR2(30);
80
81 l_msg_count NUMBER;
82 l_msg_data VARCHAR2(2000);
83 l_data VARCHAR2(2000);
84 l_msg_index_out NUMBER:=0;
85 BEGIN
86 IF p_pa_debug_mode = 'Y' THEN
87 pa_debug.set_curr_function( p_function => 'GEN_ETC_BDGT_COMPLETE_AMTS',
88 p_debug_mode => p_pa_debug_mode);
89 END IF;
90 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
91 X_MSG_COUNT := 0;
92
93 /* Get percent complete from workplan side:
94 For getting the financial percent complete,
95 we dont have to pass the structure version id.
96 It always comes from the latest published
97 financial structure version. */
98 IF P_ETC_SOURCE_CODE = 'FINANCIAL_PLAN' THEN
99 l_structure_type := 'FINANCIAL';
100 ELSE
101 l_structure_type := 'WORKPLAN';
102 l_wp_structure_version_id := P_WP_STRUCTURE_VERSION_ID;
103
104 l_structure_status_flag :=
105 PA_PROJECT_STRUCTURE_UTILS.Check_Struc_Ver_Published(
106 P_FP_COLS_TGT_REC.X_PROJECT_ID,l_wp_structure_version_id);
107 IF l_structure_status_flag = 'Y' THEN
108 l_structure_status := 'PUBLISHED';
109 ELSE
110 l_structure_status := 'WORKING';
111 END IF;
112 END IF;
113
114 IF P_PA_DEBUG_MODE = 'Y' THEN
115 pa_fp_gen_amount_utils.fp_debug
116 (p_msg => 'Before calling PA_PROGRESS_UTILS.REDEFAULT_BASE_PC',
117 p_module_name => l_module_name,
118 p_log_level => 5);
119 END IF;
120 PA_PROGRESS_UTILS.REDEFAULT_BASE_PC (
121 p_Project_ID => P_FP_COLS_TGT_REC.X_PROJECT_ID,
122 p_Proj_element_id => P_TASK_ID,
123 p_Structure_type => l_structure_type,
124 p_object_type => 'PA_TASKS',
125 p_As_Of_Date => P_ACTUALS_THRU_DATE,
126 P_STRUCTURE_VERSION_ID => l_wp_structure_version_id,
127 P_STRUCTURE_STATUS => l_structure_status,
128 p_calling_context => 'FINANCIAL_PLANNING',
129 X_base_percent_complete => lx_percent_complete,
130 x_return_status => x_return_status,
131 x_msg_count => x_msg_count,
132 x_msg_data => x_msg_data );
133 IF P_PA_DEBUG_MODE = 'Y' THEN
134 pa_fp_gen_amount_utils.fp_debug
135 (p_msg => 'After calling PA_PROGRESS_UTILS.'||
136 'REDEFAULT_BASE_PC:'||x_return_status,
137 p_module_name => l_module_name,
138 p_log_level => 5);
139 END IF;
140 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
141 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
142 END IF;
143
144 l_percent_complete := NVL(lx_percent_complete,0)/100;
145
146 IF l_percent_complete = 1 THEN
147 IF P_PA_DEBUG_MODE = 'Y' THEN
148 PA_DEBUG.RESET_CURR_FUNCTION;
149 END IF;
150 RETURN;
151 ELSIF l_percent_complete = 0 THEN
152 IF P_PA_DEBUG_MODE = 'Y' THEN
153 pa_fp_gen_amount_utils.fp_debug(
154 p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB3.'||
155 'GET_ETC_REMAIN_BDGT_AMTS',
156 p_module_name => l_module_name,
157 p_log_level => 5);
158 END IF;
159 PA_FP_GEN_FCST_AMT_PUB3.GET_ETC_REMAIN_BDGT_AMTS (
160 P_SRC_RES_ASG_ID => P_SRC_RES_ASG_ID,
161 P_TGT_RES_ASG_ID => P_TGT_RES_ASG_ID,
162 P_FP_COLS_SRC_REC => P_FP_COLS_SRC_REC,
163 P_FP_COLS_TGT_REC => P_FP_COLS_TGT_REC,
164 P_TASK_ID => P_TASK_ID,
165 P_RESOURCE_LIST_MEMBER_ID => P_RESOURCE_LIST_MEMBER_ID,
166 P_ETC_SOURCE_CODE => P_ETC_SOURCE_CODE,
167 P_WP_STRUCTURE_VERSION_ID => P_WP_STRUCTURE_VERSION_ID,
168 P_ACTUALS_THRU_DATE => P_ACTUALS_THRU_DATE,
169 P_PLANNING_OPTIONS_FLAG => P_PLANNING_OPTIONS_FLAG,
170 X_RETURN_STATUS => X_RETURN_STATUS,
171 X_MSG_COUNT => X_MSG_COUNT,
172 X_MSG_DATA => X_MSG_DATA );
173 IF P_PA_DEBUG_MODE = 'Y' THEN
174 pa_fp_gen_amount_utils.fp_debug(
175 p_msg => 'After calling PA_FP_GEN_FCST_AMT_PUB3.'||
176 'GET_ETC_REMAIN_BDGT_AMTS:'||x_return_status,
177 p_module_name => l_module_name,
178 p_log_level => 5);
179 END IF;
180 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
181 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
182 END IF;
183 IF P_PA_DEBUG_MODE = 'Y' THEN
184 PA_DEBUG.RESET_CURR_FUNCTION;
185 END IF;
186 RETURN;
187 END IF;
188
189 IF NVL(P_TGT_RES_ASG_ID,-99)>0 THEN
190 SELECT rate_based_flag
191 INTO l_rate_based_flag
192 FROM pa_resource_assignments
193 WHERE resource_assignment_id = p_tgt_res_asg_id;
194 ELSE
195 l_rate_based_flag:='N';
196 END IF;
197
198 /* When generate cost based revenue version, always take PFC
199 When target version is not multi currency enabled, take PC */
200 l_currency_flag := 'TC';
201
202 l_rev_gen_method := nvl(P_FP_COLS_TGT_REC.x_revenue_derivation_method,PA_FP_GEN_FCST_PG_PKG.
203 GET_REV_GEN_METHOD(P_FP_COLS_TGT_REC.x_project_id)); --Bug 5152892
204
205 IF (p_fp_cols_tgt_rec.x_version_type = 'REVENUE' and l_rev_gen_method = 'C') THEN
206 l_currency_flag := 'PFC';
207 ELSIF p_fp_cols_tgt_rec.X_PLAN_IN_MULTI_CURR_FLAG = 'N' THEN
208 l_currency_flag := 'PC';
209 END IF;
210
211 l_pc_currency_code := p_fp_cols_tgt_rec.x_project_currency_code;
212 l_pfc_currency_code := p_fp_cols_tgt_rec.x_projfunc_currency_code;
213 l_target_version_type := p_fp_cols_src_rec.x_version_type;
214 IF p_etc_source_code = 'FINANCIAL_PLAN' THEN
215 /* Get actual amounts from financial side - PA_FP_FCST_GEN_TMP1 */
216 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
217 DECODE(l_currency_flag,
218 'PC',l_pc_currency_code,
219 'TC',txn_currency_code,
220 'PFC',l_pfc_currency_code),
221 SUM(NVL(quantity,0)),
222 SUM(DECODE(l_currency_flag,
223 'PC', NVL(prj_raw_cost,0),
224 'TC', NVL(txn_raw_cost,0),
225 'PFC', NVL(pou_raw_cost,0))),
226 SUM(DECODE(l_currency_flag,
227 'PC', NVL(prj_brdn_cost,0),
228 'TC', NVL(txn_brdn_cost,0),
229 'PFC', NVL(pou_brdn_cost,0))),
230 SUM(DECODE(l_currency_flag,
231 'PC', NVL(prj_revenue,0),
232 'TC', NVL(txn_revenue,0),
233 'PFC', NVL(pou_revenue,0)))
234 BULK COLLECT INTO
235 l_act_currency_code_tab,
236 l_act_quantity_tab,
237 l_act_raw_cost_tab,
238 l_act_brdn_cost_tab,
239 l_act_revenue_tab
240 FROM PA_FP_FCST_GEN_TMP1
241 WHERE project_element_id = p_task_id
242 AND res_list_member_id = p_resource_list_member_id
243 AND data_type_code = 'ETC_FP'
244 GROUP BY DECODE(l_currency_flag,
245 'PC',l_pc_currency_code,
246 'TC',txn_currency_code,
247 'PFC', l_pfc_currency_code);
248
249 IF l_rate_based_flag = 'N' THEN
250 l_act_quantity_tab := l_act_raw_cost_tab;
251 END IF;
252
253 ELSIF p_etc_source_code = 'WORKPLAN_RESOURCES' THEN
254 IF P_PA_DEBUG_MODE = 'Y' THEN
255 pa_fp_gen_amount_utils.fp_debug(
256 p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
257 'GET_WP_ACTUALS_FOR_RA',
258 p_module_name => l_module_name,
259 p_log_level => 5);
260 END IF;
261 PA_FP_GEN_FCST_AMT_PUB1.GET_WP_ACTUALS_FOR_RA
262 (P_FP_COLS_SRC_REC => p_fp_cols_src_rec,
263 P_FP_COLS_TGT_REC => p_fp_cols_tgt_rec,
264 P_SRC_RES_ASG_ID => p_src_res_asg_id,
265 P_TASK_ID => p_task_id,
266 P_RES_LIST_MEM_ID => p_resource_list_member_id,
267 P_ACTUALS_THRU_DATE => p_actuals_thru_date,
268 X_ACT_QUANTITY => lx_act_quantity,
269 X_ACT_TXN_CURRENCY_CODE => lx_act_txn_currency_code,
270 X_ACT_TXN_RAW_COST => lx_act_txn_raw_cost,
271 X_ACT_TXN_BRDN_COST => lx_act_txn_brdn_cost,
272 X_ACT_PC_RAW_COST => lx_act_pc_raw_cost,
273 X_ACT_PC_BRDN_COST => lx_act_pc_brdn_cost,
274 X_ACT_PFC_RAW_COST => lx_act_pfc_raw_cost,
275 X_ACT_PFC_BRDN_COST => lx_act_pfc_brdn_cost,
276 X_RETURN_STATUS => x_return_status,
277 X_MSG_COUNT => x_msg_count,
278 X_MSG_DATA => x_msg_data );
279 IF P_PA_DEBUG_MODE = 'Y' THEN
280 pa_fp_gen_amount_utils.fp_debug(
281 p_msg => 'After calling PA_FP_GEN_FCST_AMT_PUB1.'||
282 'GET_WP_ACTUALS_FOR_RA in earned_value:'||x_return_status,
283 p_module_name => l_module_name,
284 p_log_level => 5);
285 END IF;
286 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
287 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
288 END IF;
289
290 IF l_currency_flag = 'PC' THEN
291 l_act_currency_code_tab(1) := l_pc_currency_code;
292 IF l_rate_based_flag = 'Y' THEN
293 l_act_quantity_tab(1) := lx_act_quantity;
294 ELSE
295 l_act_quantity_tab(1) := lx_act_pc_raw_cost;
296 END IF;
297 ELSIF l_currency_flag = 'TC' THEN
298 l_act_currency_code_tab(1) := lx_act_txn_currency_code;
299 IF l_rate_based_flag = 'Y' THEN
300 l_act_quantity_tab(1) := lx_act_quantity;
301 ELSE
302 l_act_quantity_tab(1) := lx_act_txn_raw_cost;
303 END IF;
304 ELSIF l_currency_flag = 'PFC' THEN
305 l_act_currency_code_tab(1) := l_pfc_currency_code;
306 IF l_rate_based_flag = 'Y' THEN
307 l_act_quantity_tab(1) := lx_act_quantity;
308 ELSE
309 l_act_quantity_tab(1) := lx_act_pfc_raw_cost;
310 END IF;
311 END IF;
312 END IF;
313
314 /* Get total ETC quantity */
315 FOR i IN 1..l_act_currency_code_tab.count LOOP
316 /* ???Do we need to handle zero actuals here??*/
317 l_etc_quantity_tab(i) := l_act_quantity_tab(i)
318 * (1 - l_percent_complete)/l_percent_complete;
319 END LOOP;
320
321 /*When not taking periodic rates, we need to calculate out the average rates
322 from the source resource assignments that are mapped to the current target
323 resource assignment. */
324 FOR i IN 1..l_act_currency_code_tab.count LOOP
325 IF p_etc_source_code = 'FINANCIAL_PLAN' THEN
326 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
327 SUM(NVL(quantity,0)),
328 SUM(DECODE(l_currency_flag,
329 'PC', NVL(prj_raw_cost,0),
330 'TC', NVL(txn_raw_cost,0),
331 'PFC', NVL(pou_raw_cost,0))),
332 SUM(DECODE(l_currency_flag,
333 'PC', NVL(prj_brdn_cost,0),
334 'TC', NVL(txn_brdn_cost,0),
335 'PFC', NVL(pou_brdn_cost,0))),
336 SUM(DECODE(l_currency_flag,
337 'PC', NVL(prj_revenue,0),
338 'TC', NVL(txn_revenue,0),
339 'PFC', NVL(pou_revenue,0))),
340 SUM(NVL(prj_raw_cost,0)),
341 SUM(NVL(prj_brdn_cost,0)),
342 SUM(NVL(prj_revenue,0)),
343 SUM(NVL(pou_raw_cost,0)),
344 SUM(NVL(pou_brdn_cost,0)),
345 SUM(NVL(pou_revenue,0))
346 INTO l_txn_rate_quantity,
347 l_txn_rate_raw_cost,
348 l_txn_rate_brdn_cost,
349 l_txn_rate_revenue,
350 l_pc_rate_raw_cost,
351 l_pc_rate_brdn_cost,
352 l_pc_rate_revenue,
353 l_pfc_rate_raw_cost,
354 l_pfc_rate_brdn_cost,
355 l_pfc_rate_revenue
356 FROM PA_FP_FCST_GEN_TMP1
357 WHERE project_element_id = p_task_id
358 AND res_list_member_id = p_resource_list_member_id
359 AND data_type_code = 'ETC_FP'
360 AND DECODE(l_currency_flag, 'TC',txn_currency_code,
361 'PC', l_act_currency_code_tab(i),
362 'PFC',l_act_currency_code_tab(i)) = l_act_currency_code_tab(i);
363 ELSIF p_etc_source_code = 'WORKPLAN_RESOURCES' THEN
364 l_txn_rate_quantity := lx_act_quantity;
365 l_txn_rate_raw_cost := lx_act_txn_raw_cost;
366 l_txn_rate_brdn_cost := lx_act_txn_brdn_cost;
367 l_txn_rate_revenue := 0;
368 l_pc_rate_raw_cost := lx_act_pc_raw_cost;
369 l_pc_rate_brdn_cost := lx_act_pc_brdn_cost;
370 l_pc_rate_revenue := 0;
371 l_pfc_rate_raw_cost := lx_act_pfc_raw_cost;
372 l_pfc_rate_brdn_cost := lx_act_pfc_brdn_cost;
373 l_pfc_rate_revenue := 0;
374 END IF;
375
376 IF l_rate_based_flag = 'N' THEN
377 l_txn_rate_quantity := l_txn_rate_raw_cost;
378 END IF;
379
380 IF l_txn_rate_quantity <> 0 THEN
381 l_txn_raw_cost_rate_tab(i) := l_txn_rate_raw_cost
382 / l_txn_rate_quantity;
383 l_txn_brdn_cost_rate_tab(i) := l_txn_rate_brdn_cost
384 / l_txn_rate_quantity;
385 l_txn_revenue_rate_tab(i) := l_txn_rate_revenue
386 / l_txn_rate_quantity;
387 l_pc_raw_cost_rate_tab(i) := l_pc_rate_raw_cost
388 / l_txn_rate_quantity;
389 l_pc_brdn_cost_rate_tab(i) := l_pc_rate_brdn_cost
390 / l_txn_rate_quantity;
391 l_pc_revenue_rate_tab(i) := l_pc_rate_revenue
392 / l_txn_rate_quantity;
393 l_pfc_raw_cost_rate_tab(i) := l_pfc_rate_raw_cost
394 / l_txn_rate_quantity;
395 l_pfc_brdn_cost_rate_tab(i) := l_pfc_rate_brdn_cost
396 / l_txn_rate_quantity;
397 l_pfc_revenue_rate_tab(i) := l_pfc_rate_revenue
398 / l_txn_rate_quantity;
399 ELSE
400 l_txn_raw_cost_rate_tab(i) := NULL;
401 l_txn_brdn_cost_rate_tab(i) := NULL;
402 l_txn_revenue_rate_tab(i) := NULL;
403 l_pc_raw_cost_rate_tab(i) := NULL;
404 l_pc_brdn_cost_rate_tab(i) := NULL;
405 l_pc_revenue_rate_tab(i) := NULL;
406 l_pfc_raw_cost_rate_tab(i) := NULL;
407 l_pfc_brdn_cost_rate_tab(i) := NULL;
408 l_pfc_revenue_rate_tab(i) := NULL;
409 END IF;
410 END LOOP;
411
412 /* Insert total ETC amounts */
413 /* If commitment is not included, record is inserted directly as
414 'ETC' record, if commitment is to be considered, record is inserted
415 as 'TOTAL_ETC' for further processing. */
416 IF P_FP_COLS_TGT_REC.X_GEN_INCL_OPEN_COMM_FLAG = 'Y' THEN
417 l_transaction_source_code := 'TOTAL_ETC';
418 ELSE
419 l_transaction_source_code := 'ETC';
420 END IF;
421
422 FORALL I IN 1..l_act_currency_code_tab.count
423 INSERT INTO PA_FP_CALC_AMT_TMP2 (
424 RESOURCE_ASSIGNMENT_ID,
425 TARGET_RES_ASG_ID,
426 ETC_CURRENCY_CODE,
427 ETC_PLAN_QUANTITY,
428 ETC_TXN_RAW_COST,
429 ETC_TXN_BURDENED_COST,
430 ETC_TXN_REVENUE,
431 ETC_PC_RAW_COST,
432 ETC_PC_BURDENED_COST,
433 ETC_PC_REVENUE,
434 ETC_PFC_RAW_COST,
435 ETC_PFC_BURDENED_COST,
436 ETC_PFC_REVENUE,
437 TRANSACTION_SOURCE_CODE )
438 VALUES (
439 P_SRC_RES_ASG_ID,
440 P_TGT_RES_ASG_ID,
441 l_act_currency_code_tab(i),
442 l_etc_quantity_tab(i) ,
443 l_etc_quantity_tab(i) * l_txn_raw_cost_rate_tab(i),
444 l_etc_quantity_tab(i) * l_txn_brdn_cost_rate_tab(i),
445 l_etc_quantity_tab(i) * l_txn_revenue_rate_tab(i),
446 l_etc_quantity_tab(i) * l_pc_raw_cost_rate_tab(i),
447 l_etc_quantity_tab(i) * l_pc_brdn_cost_rate_tab(i),
448 l_etc_quantity_tab(i) * l_pc_revenue_rate_tab(i),
449 l_etc_quantity_tab(i) * l_pfc_raw_cost_rate_tab(i),
450 l_etc_quantity_tab(i) * l_pfc_brdn_cost_rate_tab(i),
451 l_etc_quantity_tab(i) * l_pfc_revenue_rate_tab(i),
452 l_transaction_source_code);
453
454 IF P_PA_DEBUG_MODE = 'Y' THEN
455 PA_DEBUG.RESET_CURR_FUNCTION;
456 END IF;
457
458 EXCEPTION
459 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
460 l_msg_count := FND_MSG_PUB.count_msg;
461 IF l_msg_count = 1 THEN
462 PA_INTERFACE_UTILS_PUB.get_messages
463 ( p_encoded => FND_API.G_TRUE,
464 p_msg_index => 1,
465 p_msg_count => l_msg_count,
466 p_msg_data => l_msg_data,
467 p_data => l_data,
468 p_msg_index_out => l_msg_index_out);
469 x_msg_data := l_data;
470 x_msg_count := l_msg_count;
471 ELSE
472 x_msg_count := l_msg_count;
473 END IF;
474
475 ROLLBACK;
476 x_return_status := FND_API.G_RET_STS_ERROR;
477
478 IF P_PA_DEBUG_MODE = 'Y' THEN
479 pa_fp_gen_amount_utils.fp_debug
480 (p_msg => 'Invalid Arguments Passed',
481 p_module_name => l_module_name,
482 p_log_level => 5);
483 PA_DEBUG.RESET_CURR_FUNCTION;
484 END IF;
485 RAISE;
486 WHEN OTHERS THEN
487 rollback;
488 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
489 x_msg_count := 1;
490 x_msg_data := substr(sqlerrm,1,240);
491 -- dbms_output.put_line('error msg :'||x_msg_data);
492 FND_MSG_PUB.add_exc_msg
493 ( p_pkg_name => 'PA_FP_GEN_FCST_AMT_PUB5',
494 p_procedure_name => 'GEN_ETC_EARNED_VALUE_AMTS',
495 p_error_text => substr(sqlerrm,1,240));
496
497 IF P_PA_DEBUG_MODE = 'Y' THEN
498 pa_fp_gen_amount_utils.fp_debug
499 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
500 p_module_name => l_module_name,
501 p_log_level => 5);
502 PA_DEBUG.RESET_CURR_FUNCTION;
503 END IF;
504 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
505 END GET_ETC_EARNED_VALUE_AMTS;
506
507
508 PROCEDURE GET_ETC_EARNED_VALUE_AMTS_BLK (
509 P_SRC_RES_ASG_ID_TAB IN PA_PLSQL_DATATYPES.IdTabTyp,
510 P_TGT_RES_ASG_ID_TAB IN PA_PLSQL_DATATYPES.IdTabTyp,
511 P_FP_COLS_SRC_REC_FP IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
512 P_FP_COLS_SRC_REC_WP IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
513 P_FP_COLS_TGT_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
514 P_TASK_ID_TAB IN PA_PLSQL_DATATYPES.IdTabTyp,
515 P_RES_LIST_MEMBER_ID_TAB IN PA_PLSQL_DATATYPES.IdTabTyp,
516 P_CBS_ELEMENT_ID_TAB IN PA_PLSQL_DATATYPES.IdTabTyp, --bug#16791711
517 P_ETC_SOURCE_CODE_TAB IN PA_PLSQL_DATATYPES.Char30TabTyp,
518 P_WP_STRUCTURE_VERSION_ID IN PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE,
519 P_ACTUALS_THRU_DATE IN PA_PERIODS_ALL.END_DATE%TYPE,
520 P_PLANNING_OPTIONS_FLAG IN VARCHAR2,
521 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
522 X_MSG_COUNT OUT NOCOPY NUMBER,
523 X_MSG_DATA OUT NOCOPY VARCHAR2)
524 IS
525 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_FCST_AMT_PUB5.GET_ETC_EARNED_VALUE_AMTS_BLK';
526
527 l_structure_type VARCHAR2(30):= null;
528 l_structure_status VARCHAR2(30):= null;
529 l_structure_status_flag VARCHAR2(1):= null;
530 l_wp_structure_version_id NUMBER;
531 lx_percent_complete NUMBER;
532 l_percent_complete NUMBER;
533
534 l_rate_based_flag VARCHAR2(1);
535 l_currency_flag VARCHAR2(30);
536 l_currency_count_flag VARCHAR2(1);
537 l_pc_currency_code pa_projects_all.project_currency_code%type;
538 l_pfc_currency_code pa_projects_all.project_currency_code%type;
539 l_rev_gen_method VARCHAR2(3);
540
541 /*For workplan actuals*/
542 lx_act_quantity NUMBER;
543 lx_act_txn_currency_code VARCHAR2(30);
544 lx_act_txn_raw_cost NUMBER;
545 lx_act_txn_brdn_cost NUMBER;
546 lx_act_pc_raw_cost NUMBER;
547 lx_act_pc_brdn_cost NUMBER;
548 lx_act_pfc_raw_cost NUMBER;
549 lx_act_pfc_brdn_cost NUMBER;
550
551 l_act_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
552 l_act_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
553 l_act_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
554 l_act_brdn_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
555 l_act_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
556 l_target_version_type pa_budget_versions.version_type%type;
557
558 l_etc_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
559
560 /*For average rates*/
561 l_pc_rate_quantity NUMBER;
562 l_pc_rate_raw_cost NUMBER;
563 l_pc_rate_brdn_cost NUMBER;
564 l_pc_rate_revenue NUMBER;
565 l_pfc_rate_raw_cost NUMBER;
566 l_pfc_rate_brdn_cost NUMBER;
567 l_pfc_rate_revenue NUMBER;
568
569 l_txn_rate_quantity NUMBER;
570 l_txn_rate_raw_cost NUMBER;
571 l_txn_rate_brdn_cost NUMBER;
572 l_txn_rate_revenue NUMBER;
573
574 l_txn_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
575 l_txn_brdn_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
576 l_txn_revenue_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
577 l_pc_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
578 l_pc_brdn_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
579 l_pc_revenue_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
580 l_pfc_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
581 l_pfc_brdn_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
582 l_pfc_revenue_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
583 l_transaction_source_code VARCHAR2(30);
584
585 /*Added for Bulk insert at version level*/
586 l_blk_src_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
587 l_blk_tgt_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
588 l_blk_act_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
589 l_blk_etc_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
590 l_blk_etc_txn_rcost_tab PA_PLSQL_DATATYPES.NumTabTyp;
591 l_blk_etc_txn_bcost_tab PA_PLSQL_DATATYPES.NumTabTyp;
592 l_blk_etc_txn_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
593 l_blk_etc_pc_rcost_tab PA_PLSQL_DATATYPES.NumTabTyp;
594 l_blk_etc_pc_bcost_tab PA_PLSQL_DATATYPES.NumTabTyp;
595 l_blk_etc_pc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
596 l_blk_etc_pfc_rcost_tab PA_PLSQL_DATATYPES.NumTabTyp;
597 l_blk_etc_pfc_bcost_tab PA_PLSQL_DATATYPES.NumTabTyp;
598 l_blk_etc_pfc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
599
600 continue_loop EXCEPTION;
601 l_count NUMBER := 0;
602 l_dummy NUMBER;
603
604 l_remain_bdgt_src_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
605 l_remain_bdgt_tgt_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
606 l_remain_bdgt_rlm_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
607 l_remain_bdgt_cbs_id_tab PA_PLSQL_DATATYPES.IdTabTyp; --bug#16791711
608 l_remain_bdgt_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
609 l_remain_bdgt_etc_src_tab PA_PLSQL_DATATYPES.Char30TabTyp;
610 l_cnt NUMBER := 0;
611
612 l_msg_count NUMBER;
613 l_msg_data VARCHAR2(2000);
614 l_data VARCHAR2(2000);
615 l_msg_index_out NUMBER:=0;
616 BEGIN
617 IF p_pa_debug_mode = 'Y' THEN
618 pa_debug.set_curr_function( p_function => 'GEN_ETC_BDGT_COMPLETE_AMTS_BLK',
619 p_debug_mode => p_pa_debug_mode);
620 END IF;
621 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
622 X_MSG_COUNT := 0;
623
624 IF P_SRC_RES_ASG_ID_TAB.count = 0 THEN
625 RETURN;
626 END IF;
627
628 FOR main_loop IN 1..P_SRC_RES_ASG_ID_TAB.count LOOP
629 BEGIN
630 l_act_currency_code_tab.delete;
631 l_act_quantity_tab.delete;
632 l_act_raw_cost_tab.delete;
633 l_act_brdn_cost_tab.delete;
634 l_act_revenue_tab.delete;
635
636 l_txn_raw_cost_rate_tab.delete;
637 l_txn_brdn_cost_rate_tab.delete;
638 l_txn_revenue_rate_tab.delete;
639 l_pc_raw_cost_rate_tab.delete;
640 l_pc_brdn_cost_rate_tab.delete;
641 l_pc_revenue_rate_tab.delete;
642 l_pfc_raw_cost_rate_tab.delete;
643 l_pfc_brdn_cost_rate_tab.delete;
644 l_pfc_revenue_rate_tab.delete;
645
646 l_etc_quantity_tab.delete;
647
648 l_wp_structure_version_id := NULL;
649 l_structure_status := NULL;
650
651 /* Get percent complete from workplan side:
652 For getting the financial percent complete,
653 we dont have to pass the structure version id.
654 It always comes from the latest published
655 financial structure version. */
656 IF P_ETC_SOURCE_CODE_TAB(main_loop) = 'FINANCIAL_PLAN' THEN
657 l_structure_type := 'FINANCIAL';
658 ELSE
659 l_structure_type := 'WORKPLAN';
660 l_wp_structure_version_id := P_WP_STRUCTURE_VERSION_ID;
661
662 l_structure_status_flag :=
663 PA_PROJECT_STRUCTURE_UTILS.Check_Struc_Ver_Published(
664 P_FP_COLS_TGT_REC.X_PROJECT_ID,l_wp_structure_version_id);
665 IF l_structure_status_flag = 'Y' THEN
666 l_structure_status := 'PUBLISHED';
667 ELSE
668 l_structure_status := 'WORKING';
669 END IF;
670 END IF;
671
672 IF P_PA_DEBUG_MODE = 'Y' THEN
673 pa_fp_gen_amount_utils.fp_debug
674 (p_msg => 'Before calling PA_PROGRESS_UTILS.REDEFAULT_BASE_PC',
675 p_module_name => l_module_name,
676 p_log_level => 5);
677 END IF;
678 PA_PROGRESS_UTILS.REDEFAULT_BASE_PC (
679 p_Project_ID => P_FP_COLS_TGT_REC.X_PROJECT_ID,
680 p_Proj_element_id => P_TASK_ID_TAB(main_loop),
681 p_Structure_type => l_structure_type,
682 p_object_type => 'PA_TASKS',
683 p_As_Of_Date => P_ACTUALS_THRU_DATE,
684 P_STRUCTURE_VERSION_ID => l_wp_structure_version_id,
685 P_STRUCTURE_STATUS => l_structure_status,
686 p_calling_context => 'FINANCIAL_PLANNING',
687 X_base_percent_complete => lx_percent_complete,
688 x_return_status => x_return_status,
689 x_msg_count => x_msg_count,
690 x_msg_data => x_msg_data );
691 IF P_PA_DEBUG_MODE = 'Y' THEN
692 pa_fp_gen_amount_utils.fp_debug
693 (p_msg => 'After calling PA_PROGRESS_UTILS.'||
694 'REDEFAULT_BASE_PC:'||x_return_status,
695 p_module_name => l_module_name,
696 p_log_level => 5);
697 END IF;
698 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
699 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
700 END IF;
701
702 l_percent_complete := NVL(lx_percent_complete,0)/100;
703
704 IF l_percent_complete = 1 THEN
705 RAISE continue_loop;
706 ELSIF l_percent_complete = 0 THEN
707 l_cnt := l_cnt + 1;
708 l_remain_bdgt_src_ra_id_tab(l_cnt) := P_SRC_RES_ASG_ID_TAB(main_loop);
709 l_remain_bdgt_tgt_ra_id_tab(l_cnt) := P_TGT_RES_ASG_ID_TAB(main_loop);
710 l_remain_bdgt_rlm_id_tab(l_cnt) := P_RES_LIST_MEMBER_ID_TAB(main_loop);
711 l_remain_bdgt_cbs_id_tab(l_cnt) := P_CBS_ELEMENT_ID_TAB(main_loop); --bug#16791711
712 l_remain_bdgt_task_id_tab(l_cnt) := P_TASK_ID_TAB(main_loop);
713 l_remain_bdgt_etc_src_tab(l_cnt) := P_ETC_SOURCE_CODE_TAB(main_loop);
714 RAISE continue_loop;
715 END IF;
716
717 IF NVL(P_TGT_RES_ASG_ID_TAB(main_loop),-99)>0 THEN
718 SELECT rate_based_flag
719 INTO l_rate_based_flag
720 FROM pa_resource_assignments
721 WHERE resource_assignment_id = p_tgt_res_asg_id_tab(main_loop);
722 ELSE
723 l_rate_based_flag:='N';
724 END IF;
725
726 /* When generate cost based revenue version, always take PFC
727 When target version is not multi currency enabled, take PC */
728 l_currency_flag := 'TC';
729
730 l_rev_gen_method := nvl(P_FP_COLS_TGT_REC.x_revenue_derivation_method,PA_FP_GEN_FCST_PG_PKG.
731 GET_REV_GEN_METHOD(P_FP_COLS_TGT_REC.x_project_id)); --Bug 5152892
732
733 IF (p_fp_cols_tgt_rec.x_version_type = 'REVENUE' and l_rev_gen_method = 'C') THEN
734 l_currency_flag := 'PFC';
735 ELSIF p_fp_cols_tgt_rec.X_PLAN_IN_MULTI_CURR_FLAG = 'N' THEN
736 l_currency_flag := 'PC';
737 END IF;
738
739 l_pc_currency_code := p_fp_cols_tgt_rec.x_project_currency_code;
740 l_pfc_currency_code := p_fp_cols_tgt_rec.x_projfunc_currency_code;
741 l_target_version_type := p_fp_cols_tgt_rec.x_version_type;
742 IF p_etc_source_code_tab(main_loop) = 'FINANCIAL_PLAN' THEN
743 /* Get actual amounts from financial side - PA_FP_FCST_GEN_TMP1 */
744 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
745 DECODE(l_currency_flag,
746 'PC',l_pc_currency_code,
747 'TC',txn_currency_code,
748 'PFC',l_pfc_currency_code),
749 SUM(NVL(quantity,0)),
750 SUM(DECODE(l_currency_flag,
751 'PC', NVL(prj_raw_cost,0),
752 'TC', NVL(txn_raw_cost,0),
753 'PFC', NVL(pou_raw_cost,0))),
754 SUM(DECODE(l_currency_flag,
755 'PC', NVL(prj_brdn_cost,0),
756 'TC', NVL(txn_brdn_cost,0),
757 'PFC', NVL(pou_brdn_cost,0))),
758 SUM(DECODE(l_currency_flag,
759 'PC', NVL(prj_revenue,0),
760 'TC', NVL(txn_revenue,0),
761 'PFC', NVL(pou_revenue,0)))
762 BULK COLLECT INTO
763 l_act_currency_code_tab,
764 l_act_quantity_tab,
765 l_act_raw_cost_tab,
766 l_act_brdn_cost_tab,
767 l_act_revenue_tab
768 FROM PA_FP_FCST_GEN_TMP1
769 WHERE project_element_id = p_task_id_tab(main_loop)
770 AND res_list_member_id = p_res_list_member_id_tab(main_loop)
771 AND nvl(cbs_element_id,-1) = nvl(P_CBS_ELEMENT_ID_TAB(main_loop),-1) --bug#16791711
772 AND data_type_code = 'ETC_FP'
773 GROUP BY DECODE(l_currency_flag,
774 'PC',l_pc_currency_code,
775 'TC',txn_currency_code,
776 'PFC', l_pfc_currency_code);
777
778 IF l_rate_based_flag = 'N' THEN
779 l_act_quantity_tab := l_act_raw_cost_tab;
780 END IF;
781
782 ELSIF p_etc_source_code_tab(main_loop) = 'WORKPLAN_RESOURCES' THEN
783 IF P_PA_DEBUG_MODE = 'Y' THEN
784 pa_fp_gen_amount_utils.fp_debug(
785 p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
786 'GET_WP_ACTUALS_FOR_RA',
787 p_module_name => l_module_name,
788 p_log_level => 5);
789 END IF;
790 PA_FP_GEN_FCST_AMT_PUB1.GET_WP_ACTUALS_FOR_RA
791 (P_FP_COLS_SRC_REC => p_fp_cols_src_rec_wp,
792 P_FP_COLS_TGT_REC => p_fp_cols_tgt_rec,
793 P_SRC_RES_ASG_ID => p_src_res_asg_id_tab(main_loop),
794 P_TASK_ID => p_task_id_tab(main_loop),
795 P_RES_LIST_MEM_ID => p_res_list_member_id_tab(main_loop),
796 P_ACTUALS_THRU_DATE => p_actuals_thru_date,
797 X_ACT_QUANTITY => lx_act_quantity,
798 X_ACT_TXN_CURRENCY_CODE => lx_act_txn_currency_code,
799 X_ACT_TXN_RAW_COST => lx_act_txn_raw_cost,
800 X_ACT_TXN_BRDN_COST => lx_act_txn_brdn_cost,
801 X_ACT_PC_RAW_COST => lx_act_pc_raw_cost,
802 X_ACT_PC_BRDN_COST => lx_act_pc_brdn_cost,
803 X_ACT_PFC_RAW_COST => lx_act_pfc_raw_cost,
804 X_ACT_PFC_BRDN_COST => lx_act_pfc_brdn_cost,
805 X_RETURN_STATUS => x_return_status,
806 X_MSG_COUNT => x_msg_count,
807 X_MSG_DATA => x_msg_data );
808 IF P_PA_DEBUG_MODE = 'Y' THEN
809 pa_fp_gen_amount_utils.fp_debug(
810 p_msg => 'After calling PA_FP_GEN_FCST_AMT_PUB1.'||
811 'GET_WP_ACTUALS_FOR_RA in earned_value:'||x_return_status,
812 p_module_name => l_module_name,
813 p_log_level => 5);
814 END IF;
815 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
816 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
817 END IF;
818
819 IF l_currency_flag = 'PC' THEN
820 l_act_currency_code_tab(1) := l_pc_currency_code;
821 IF l_rate_based_flag = 'Y' THEN
822 l_act_quantity_tab(1) := lx_act_quantity;
823 ELSE
824 l_act_quantity_tab(1) := lx_act_pc_raw_cost;
825 END IF;
826 ELSIF l_currency_flag = 'TC' THEN
827 l_act_currency_code_tab(1) := lx_act_txn_currency_code;
828 IF l_rate_based_flag = 'Y' THEN
829 l_act_quantity_tab(1) := lx_act_quantity;
830 ELSE
831 l_act_quantity_tab(1) := lx_act_txn_raw_cost;
832 END IF;
833 ELSIF l_currency_flag = 'PFC' THEN
834 l_act_currency_code_tab(1) := l_pfc_currency_code;
835 IF l_rate_based_flag = 'Y' THEN
836 l_act_quantity_tab(1) := lx_act_quantity;
837 ELSE
838 l_act_quantity_tab(1) := lx_act_pfc_raw_cost;
839 END IF;
840 END IF;
841 END IF;
842
843 /* Get total ETC quantity */
844 FOR i IN 1..l_act_currency_code_tab.count LOOP
845 /* ???Do we need to handle zero actuals here??*/
846 l_etc_quantity_tab(i) := l_act_quantity_tab(i)
847 * (1 - l_percent_complete)/l_percent_complete;
848 END LOOP;
849
850 /*When not taking periodic rates, we need to calculate out the average rates
851 from the source resource assignments that are mapped to the current target
852 resource assignment. */
853 FOR i IN 1..l_act_currency_code_tab.count LOOP
854 IF p_etc_source_code_tab(main_loop) = 'FINANCIAL_PLAN' THEN
855 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
856 SUM(NVL(quantity,0)),
857 SUM(DECODE(l_currency_flag,
858 'PC', NVL(prj_raw_cost,0),
859 'TC', NVL(txn_raw_cost,0),
860 'PFC', NVL(pou_raw_cost,0))),
861 SUM(DECODE(l_currency_flag,
862 'PC', NVL(prj_brdn_cost,0),
863 'TC', NVL(txn_brdn_cost,0),
864 'PFC', NVL(pou_brdn_cost,0))),
865 SUM(DECODE(l_currency_flag,
866 'PC', NVL(prj_revenue,0),
867 'TC', NVL(txn_revenue,0),
868 'PFC', NVL(pou_revenue,0))),
869 SUM(NVL(prj_raw_cost,0)),
870 SUM(NVL(prj_brdn_cost,0)),
871 SUM(NVL(prj_revenue,0)),
872 SUM(NVL(pou_raw_cost,0)),
873 SUM(NVL(pou_brdn_cost,0)),
874 SUM(NVL(pou_revenue,0))
875 INTO l_txn_rate_quantity,
876 l_txn_rate_raw_cost,
877 l_txn_rate_brdn_cost,
878 l_txn_rate_revenue,
879 l_pc_rate_raw_cost,
880 l_pc_rate_brdn_cost,
881 l_pc_rate_revenue,
882 l_pfc_rate_raw_cost,
883 l_pfc_rate_brdn_cost,
884 l_pfc_rate_revenue
885 FROM PA_FP_FCST_GEN_TMP1
886 WHERE project_element_id = p_task_id_tab(main_loop)
887 AND res_list_member_id = p_res_list_member_id_tab(main_loop)
888 AND nvl(cbs_element_id,-1) = nvl(P_CBS_ELEMENT_ID_TAB(main_loop),-1) --bug#16791711
889 AND data_type_code = 'ETC_FP'
890 AND DECODE(l_currency_flag, 'TC',txn_currency_code,
891 'PC', l_act_currency_code_tab(i),
892 'PFC',l_act_currency_code_tab(i)) = l_act_currency_code_tab(i);
893 ELSIF p_etc_source_code_tab(main_loop) = 'WORKPLAN_RESOURCES' THEN
894 l_txn_rate_quantity := lx_act_quantity;
895 IF l_currency_flag = 'PC' THEN
896 l_txn_rate_raw_cost := lx_act_pc_raw_cost;
897 l_txn_rate_brdn_cost := lx_act_pc_brdn_cost;
898 ELSIF l_currency_flag = 'PFC' THEN
899 l_txn_rate_raw_cost := lx_act_pfc_raw_cost;
900 l_txn_rate_brdn_cost := lx_act_pfc_brdn_cost;
901 ELSE
902 l_txn_rate_raw_cost := lx_act_txn_raw_cost;
903 l_txn_rate_brdn_cost := lx_act_txn_brdn_cost;
904 END IF;
905 l_txn_rate_revenue := 0;
906 l_pc_rate_raw_cost := lx_act_pc_raw_cost;
907 l_pc_rate_brdn_cost := lx_act_pc_brdn_cost;
908 l_pc_rate_revenue := 0;
909 l_pfc_rate_raw_cost := lx_act_pfc_raw_cost;
910 l_pfc_rate_brdn_cost := lx_act_pfc_brdn_cost;
911 l_pfc_rate_revenue := 0;
912 END IF;
913
914 IF l_rate_based_flag = 'N' THEN
915 l_txn_rate_quantity := l_txn_rate_raw_cost;
916 END IF;
917
918 IF l_txn_rate_quantity <> 0 THEN
919 l_txn_raw_cost_rate_tab(i) := l_txn_rate_raw_cost
920 / l_txn_rate_quantity;
921 l_txn_brdn_cost_rate_tab(i) := l_txn_rate_brdn_cost
922 / l_txn_rate_quantity;
923 l_txn_revenue_rate_tab(i) := l_txn_rate_revenue
924 / l_txn_rate_quantity;
925 l_pc_raw_cost_rate_tab(i) := l_pc_rate_raw_cost
926 / l_txn_rate_quantity;
927 l_pc_brdn_cost_rate_tab(i) := l_pc_rate_brdn_cost
928 / l_txn_rate_quantity;
929 l_pc_revenue_rate_tab(i) := l_pc_rate_revenue
930 / l_txn_rate_quantity;
931 l_pfc_raw_cost_rate_tab(i) := l_pfc_rate_raw_cost
932 / l_txn_rate_quantity;
933 l_pfc_brdn_cost_rate_tab(i) := l_pfc_rate_brdn_cost
934 / l_txn_rate_quantity;
935 l_pfc_revenue_rate_tab(i) := l_pfc_rate_revenue
936 / l_txn_rate_quantity;
937 ELSE
938 l_txn_raw_cost_rate_tab(i) := NULL;
939 l_txn_brdn_cost_rate_tab(i) := NULL;
940 l_txn_revenue_rate_tab(i) := NULL;
941 l_pc_raw_cost_rate_tab(i) := NULL;
942 l_pc_brdn_cost_rate_tab(i) := NULL;
943 l_pc_revenue_rate_tab(i) := NULL;
944 l_pfc_raw_cost_rate_tab(i) := NULL;
945 l_pfc_brdn_cost_rate_tab(i) := NULL;
946 l_pfc_revenue_rate_tab(i) := NULL;
947 END IF;
948 l_count := l_count + 1;
949 l_blk_src_res_asg_id_tab(l_count) := P_SRC_RES_ASG_ID_TAB(main_loop);
950 l_blk_tgt_res_asg_id_tab(l_count) := P_TGT_RES_ASG_ID_TAB(main_loop);
951 l_blk_act_currency_code_tab(l_count) := l_act_currency_code_tab(i);
952 l_blk_etc_quantity_tab(l_count) := l_etc_quantity_tab(i);
953 l_blk_etc_txn_rcost_tab(l_count) := l_etc_quantity_tab(i) * l_txn_raw_cost_rate_tab(i);
954 l_blk_etc_txn_bcost_tab(l_count) := l_etc_quantity_tab(i) * l_txn_brdn_cost_rate_tab(i);
955 l_blk_etc_txn_revenue_tab(l_count) := l_etc_quantity_tab(i) * l_txn_revenue_rate_tab(i);
956 l_blk_etc_pc_rcost_tab(l_count) := l_etc_quantity_tab(i) * l_pc_raw_cost_rate_tab(i);
957 l_blk_etc_pc_bcost_tab(l_count) := l_etc_quantity_tab(i) * l_pc_brdn_cost_rate_tab(i);
958 l_blk_etc_pc_revenue_tab(l_count) := l_etc_quantity_tab(i) * l_pc_revenue_rate_tab(i);
959 l_blk_etc_pfc_rcost_tab(l_count) := l_etc_quantity_tab(i) * l_pfc_raw_cost_rate_tab(i);
960 l_blk_etc_pfc_bcost_tab(l_count) := l_etc_quantity_tab(i) * l_pfc_brdn_cost_rate_tab(i);
961 l_blk_etc_pfc_revenue_tab(l_count) := l_etc_quantity_tab(i) * l_pfc_revenue_rate_tab(i);
962 END LOOP;
963 EXCEPTION
964 WHEN CONTINUE_LOOP THEN
965 l_dummy := 1;
966 WHEN OTHERS THEN
967 RAISE;
968 END;
969 END LOOP; /*Main loop*/
970
971 /* Insert total ETC amounts */
972 /* If commitment is not included, record is inserted directly as
973 'ETC' record, if commitment is to be considered, record is inserted
974 as 'TOTAL_ETC' for further processing. */
975 IF P_FP_COLS_TGT_REC.X_GEN_INCL_OPEN_COMM_FLAG = 'Y' THEN
976 l_transaction_source_code := 'TOTAL_ETC';
977 ELSE
978 l_transaction_source_code := 'ETC';
979 END IF;
980
981 FORALL I IN 1..l_blk_act_currency_code_tab.count
982 INSERT INTO PA_FP_CALC_AMT_TMP2 (
983 RESOURCE_ASSIGNMENT_ID,
984 TARGET_RES_ASG_ID,
985 ETC_CURRENCY_CODE,
986 ETC_PLAN_QUANTITY,
987 ETC_TXN_RAW_COST,
988 ETC_TXN_BURDENED_COST,
989 ETC_TXN_REVENUE,
990 ETC_PC_RAW_COST,
991 ETC_PC_BURDENED_COST,
992 ETC_PC_REVENUE,
993 ETC_PFC_RAW_COST,
994 ETC_PFC_BURDENED_COST,
995 ETC_PFC_REVENUE,
996 TRANSACTION_SOURCE_CODE )
997 VALUES (
998 l_blk_src_res_asg_id_tab(i),
999 l_blk_tgt_res_asg_id_tab(i),
1000 l_blk_act_currency_code_tab(i),
1001 l_blk_etc_quantity_tab(i),
1002 l_blk_etc_txn_rcost_tab(i),
1003 l_blk_etc_txn_bcost_tab(i),
1004 l_blk_etc_txn_revenue_tab(i),
1005 l_blk_etc_pc_rcost_tab(i),
1006 l_blk_etc_pc_bcost_tab(i),
1007 l_blk_etc_pc_revenue_tab(i),
1008 l_blk_etc_pfc_rcost_tab(i),
1009 l_blk_etc_pfc_bcost_tab(i),
1010 l_blk_etc_pfc_revenue_tab(i),
1011 l_transaction_source_code);
1012
1013 IF l_remain_bdgt_src_ra_id_tab.count > 0 THEN
1014 IF P_PA_DEBUG_MODE = 'Y' THEN
1015 pa_fp_gen_amount_utils.fp_debug(
1016 p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB3.'||
1017 'GET_ETC_REMAIN_BDGT_AMTS',
1018 p_module_name => l_module_name,
1019 p_log_level => 5);
1020 END IF;
1021 PA_FP_GEN_FCST_AMT_PUB3.GET_ETC_REMAIN_BDGT_AMTS_BLK(
1022 P_SRC_RES_ASG_ID_TAB => l_remain_bdgt_src_ra_id_tab,
1023 P_TGT_RES_ASG_ID_TAB => l_remain_bdgt_tgt_ra_id_tab,
1024 P_FP_COLS_SRC_REC_FP => P_FP_COLS_SRC_REC_FP,
1025 P_FP_COLS_SRC_REC_WP => P_FP_COLS_SRC_REC_WP,
1026 P_FP_COLS_TGT_REC => P_FP_COLS_TGT_REC,
1027 P_TASK_ID_TAB => l_remain_bdgt_task_id_tab,
1028 P_RES_LIST_MEMBER_ID_TAB => l_remain_bdgt_rlm_id_tab,
1029 P_CBS_ELEMENT_ID_TAB => l_remain_bdgt_cbs_id_tab,--bug#16791711
1030 P_ETC_SOURCE_CODE_TAB => l_remain_bdgt_etc_src_tab,
1031 P_WP_STRUCTURE_VERSION_ID => P_WP_STRUCTURE_VERSION_ID,
1032 P_ACTUALS_THRU_DATE => P_ACTUALS_THRU_DATE,
1033 P_PLANNING_OPTIONS_FLAG => P_PLANNING_OPTIONS_FLAG,
1034 X_RETURN_STATUS => X_RETURN_STATUS,
1035 X_MSG_COUNT => X_MSG_COUNT,
1036 X_MSG_DATA => X_MSG_DATA);
1037 IF P_PA_DEBUG_MODE = 'Y' THEN
1038 pa_fp_gen_amount_utils.fp_debug(
1039 p_msg => 'After calling PA_FP_GEN_FCST_AMT_PUB3.'||
1040 'GET_ETC_REMAIN_BDGT_AMTS:'||x_return_status,
1041 p_module_name => l_module_name,
1042 p_log_level => 5);
1043 END IF;
1044 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1045 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1046 END IF;
1047 END IF;
1048
1049 IF P_PA_DEBUG_MODE = 'Y' THEN
1050 PA_DEBUG.RESET_CURR_FUNCTION;
1051 END IF;
1052 EXCEPTION
1053 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1054 l_msg_count := FND_MSG_PUB.count_msg;
1055 IF l_msg_count = 1 THEN
1056 PA_INTERFACE_UTILS_PUB.get_messages
1057 ( p_encoded => FND_API.G_TRUE,
1058 p_msg_index => 1,
1059 p_msg_count => l_msg_count,
1060 p_msg_data => l_msg_data,
1061 p_data => l_data,
1062 p_msg_index_out => l_msg_index_out);
1063 x_msg_data := l_data;
1064 x_msg_count := l_msg_count;
1065 ELSE
1066 x_msg_count := l_msg_count;
1067 END IF;
1068
1069 ROLLBACK;
1070 x_return_status := FND_API.G_RET_STS_ERROR;
1071
1072 IF P_PA_DEBUG_MODE = 'Y' THEN
1073 pa_fp_gen_amount_utils.fp_debug
1074 (p_msg => 'Invalid Arguments Passed',
1075 p_module_name => l_module_name,
1076 p_log_level => 5);
1077 PA_DEBUG.RESET_CURR_FUNCTION;
1078 END IF;
1079 RAISE;
1080 WHEN OTHERS THEN
1081 rollback;
1082 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1083 x_msg_count := 1;
1084 x_msg_data := substr(sqlerrm,1,240);
1085 -- dbms_output.put_line('error msg :'||x_msg_data);
1086 FND_MSG_PUB.add_exc_msg
1087 ( p_pkg_name => 'PA_FP_GEN_FCST_AMT_PUB5',
1088 p_procedure_name => 'GEN_ETC_EARNED_VALUE_AMTS_BLK',
1089 p_error_text => substr(sqlerrm,1,240));
1090
1091 IF P_PA_DEBUG_MODE = 'Y' THEN
1092 pa_fp_gen_amount_utils.fp_debug
1093 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1094 p_module_name => l_module_name,
1095 p_log_level => 5);
1096 PA_DEBUG.RESET_CURR_FUNCTION;
1097 END IF;
1098 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1099 END GET_ETC_EARNED_VALUE_AMTS_BLK;
1100
1101 END PA_FP_GEN_FCST_AMT_PUB5;