[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 2007/02/06 09:52:14 dthakker 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_ETC_SOURCE_CODE_TAB IN PA_PLSQL_DATATYPES.Char30TabTyp,
517 P_WP_STRUCTURE_VERSION_ID IN PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE,
518 P_ACTUALS_THRU_DATE IN PA_PERIODS_ALL.END_DATE%TYPE,
519 P_PLANNING_OPTIONS_FLAG IN VARCHAR2,
520 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
521 X_MSG_COUNT OUT NOCOPY NUMBER,
522 X_MSG_DATA OUT NOCOPY VARCHAR2)
523 IS
524 l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_FCST_AMT_PUB5.GET_ETC_EARNED_VALUE_AMTS_BLK';
525
526 l_structure_type VARCHAR2(30):= null;
527 l_structure_status VARCHAR2(30):= null;
528 l_structure_status_flag VARCHAR2(1):= null;
529 l_wp_structure_version_id NUMBER;
530 lx_percent_complete NUMBER;
531 l_percent_complete NUMBER;
532
533 l_rate_based_flag VARCHAR2(1);
534 l_currency_flag VARCHAR2(30);
535 l_currency_count_flag VARCHAR2(1);
536 l_pc_currency_code pa_projects_all.project_currency_code%type;
537 l_pfc_currency_code pa_projects_all.project_currency_code%type;
538 l_rev_gen_method VARCHAR2(3);
539
540 /*For workplan actuals*/
541 lx_act_quantity NUMBER;
542 lx_act_txn_currency_code VARCHAR2(30);
543 lx_act_txn_raw_cost NUMBER;
544 lx_act_txn_brdn_cost NUMBER;
545 lx_act_pc_raw_cost NUMBER;
546 lx_act_pc_brdn_cost NUMBER;
547 lx_act_pfc_raw_cost NUMBER;
548 lx_act_pfc_brdn_cost NUMBER;
549
550 l_act_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
551 l_act_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
552 l_act_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
553 l_act_brdn_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
554 l_act_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
555 l_target_version_type pa_budget_versions.version_type%type;
556
557 l_etc_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
558
559 /*For average rates*/
560 l_pc_rate_quantity NUMBER;
561 l_pc_rate_raw_cost NUMBER;
562 l_pc_rate_brdn_cost NUMBER;
563 l_pc_rate_revenue NUMBER;
564 l_pfc_rate_raw_cost NUMBER;
565 l_pfc_rate_brdn_cost NUMBER;
566 l_pfc_rate_revenue NUMBER;
567
568 l_txn_rate_quantity NUMBER;
569 l_txn_rate_raw_cost NUMBER;
570 l_txn_rate_brdn_cost NUMBER;
571 l_txn_rate_revenue NUMBER;
572
573 l_txn_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
574 l_txn_brdn_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
575 l_txn_revenue_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
576 l_pc_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
577 l_pc_brdn_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
578 l_pc_revenue_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
579 l_pfc_raw_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
580 l_pfc_brdn_cost_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
581 l_pfc_revenue_rate_tab PA_PLSQL_DATATYPES.NumTabTyp;
582 l_transaction_source_code VARCHAR2(30);
583
584 /*Added for Bulk insert at version level*/
585 l_blk_src_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
586 l_blk_tgt_res_asg_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
587 l_blk_act_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
588 l_blk_etc_quantity_tab PA_PLSQL_DATATYPES.NumTabTyp;
589 l_blk_etc_txn_rcost_tab PA_PLSQL_DATATYPES.NumTabTyp;
590 l_blk_etc_txn_bcost_tab PA_PLSQL_DATATYPES.NumTabTyp;
591 l_blk_etc_txn_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
592 l_blk_etc_pc_rcost_tab PA_PLSQL_DATATYPES.NumTabTyp;
593 l_blk_etc_pc_bcost_tab PA_PLSQL_DATATYPES.NumTabTyp;
594 l_blk_etc_pc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
595 l_blk_etc_pfc_rcost_tab PA_PLSQL_DATATYPES.NumTabTyp;
596 l_blk_etc_pfc_bcost_tab PA_PLSQL_DATATYPES.NumTabTyp;
597 l_blk_etc_pfc_revenue_tab PA_PLSQL_DATATYPES.NumTabTyp;
598
599 continue_loop EXCEPTION;
600 l_count NUMBER := 0;
601 l_dummy NUMBER;
602
603 l_remain_bdgt_src_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
604 l_remain_bdgt_tgt_ra_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
605 l_remain_bdgt_rlm_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
606 l_remain_bdgt_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
607 l_remain_bdgt_etc_src_tab PA_PLSQL_DATATYPES.Char30TabTyp;
608 l_cnt NUMBER := 0;
609
610 l_msg_count NUMBER;
611 l_msg_data VARCHAR2(2000);
612 l_data VARCHAR2(2000);
613 l_msg_index_out NUMBER:=0;
614 BEGIN
615 IF p_pa_debug_mode = 'Y' THEN
616 pa_debug.set_curr_function( p_function => 'GEN_ETC_BDGT_COMPLETE_AMTS_BLK',
617 p_debug_mode => p_pa_debug_mode);
618 END IF;
619 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
620 X_MSG_COUNT := 0;
621
622 IF P_SRC_RES_ASG_ID_TAB.count = 0 THEN
623 RETURN;
624 END IF;
625
626 FOR main_loop IN 1..P_SRC_RES_ASG_ID_TAB.count LOOP
627 BEGIN
628 l_act_currency_code_tab.delete;
629 l_act_quantity_tab.delete;
630 l_act_raw_cost_tab.delete;
631 l_act_brdn_cost_tab.delete;
632 l_act_revenue_tab.delete;
633
634 l_txn_raw_cost_rate_tab.delete;
635 l_txn_brdn_cost_rate_tab.delete;
636 l_txn_revenue_rate_tab.delete;
637 l_pc_raw_cost_rate_tab.delete;
638 l_pc_brdn_cost_rate_tab.delete;
639 l_pc_revenue_rate_tab.delete;
640 l_pfc_raw_cost_rate_tab.delete;
641 l_pfc_brdn_cost_rate_tab.delete;
642 l_pfc_revenue_rate_tab.delete;
643
644 l_etc_quantity_tab.delete;
645
646 l_wp_structure_version_id := NULL;
647 l_structure_status := NULL;
648
649 /* Get percent complete from workplan side:
650 For getting the financial percent complete,
651 we dont have to pass the structure version id.
652 It always comes from the latest published
653 financial structure version. */
654 IF P_ETC_SOURCE_CODE_TAB(main_loop) = 'FINANCIAL_PLAN' THEN
655 l_structure_type := 'FINANCIAL';
656 ELSE
657 l_structure_type := 'WORKPLAN';
658 l_wp_structure_version_id := P_WP_STRUCTURE_VERSION_ID;
659
660 l_structure_status_flag :=
661 PA_PROJECT_STRUCTURE_UTILS.Check_Struc_Ver_Published(
662 P_FP_COLS_TGT_REC.X_PROJECT_ID,l_wp_structure_version_id);
663 IF l_structure_status_flag = 'Y' THEN
664 l_structure_status := 'PUBLISHED';
665 ELSE
666 l_structure_status := 'WORKING';
667 END IF;
668 END IF;
669
670 IF P_PA_DEBUG_MODE = 'Y' THEN
671 pa_fp_gen_amount_utils.fp_debug
672 (p_msg => 'Before calling PA_PROGRESS_UTILS.REDEFAULT_BASE_PC',
673 p_module_name => l_module_name,
674 p_log_level => 5);
675 END IF;
676 PA_PROGRESS_UTILS.REDEFAULT_BASE_PC (
677 p_Project_ID => P_FP_COLS_TGT_REC.X_PROJECT_ID,
678 p_Proj_element_id => P_TASK_ID_TAB(main_loop),
679 p_Structure_type => l_structure_type,
680 p_object_type => 'PA_TASKS',
681 p_As_Of_Date => P_ACTUALS_THRU_DATE,
682 P_STRUCTURE_VERSION_ID => l_wp_structure_version_id,
683 P_STRUCTURE_STATUS => l_structure_status,
684 p_calling_context => 'FINANCIAL_PLANNING',
685 X_base_percent_complete => lx_percent_complete,
686 x_return_status => x_return_status,
687 x_msg_count => x_msg_count,
688 x_msg_data => x_msg_data );
689 IF P_PA_DEBUG_MODE = 'Y' THEN
690 pa_fp_gen_amount_utils.fp_debug
691 (p_msg => 'After calling PA_PROGRESS_UTILS.'||
692 'REDEFAULT_BASE_PC:'||x_return_status,
693 p_module_name => l_module_name,
694 p_log_level => 5);
695 END IF;
696 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
697 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
698 END IF;
699
700 l_percent_complete := NVL(lx_percent_complete,0)/100;
701
702 IF l_percent_complete = 1 THEN
703 RAISE continue_loop;
704 ELSIF l_percent_complete = 0 THEN
705 l_cnt := l_cnt + 1;
706 l_remain_bdgt_src_ra_id_tab(l_cnt) := P_SRC_RES_ASG_ID_TAB(main_loop);
707 l_remain_bdgt_tgt_ra_id_tab(l_cnt) := P_TGT_RES_ASG_ID_TAB(main_loop);
708 l_remain_bdgt_rlm_id_tab(l_cnt) := P_RES_LIST_MEMBER_ID_TAB(main_loop);
709 l_remain_bdgt_task_id_tab(l_cnt) := P_TASK_ID_TAB(main_loop);
710 l_remain_bdgt_etc_src_tab(l_cnt) := P_ETC_SOURCE_CODE_TAB(main_loop);
711 RAISE continue_loop;
712 END IF;
713
714 IF NVL(P_TGT_RES_ASG_ID_TAB(main_loop),-99)>0 THEN
715 SELECT rate_based_flag
716 INTO l_rate_based_flag
717 FROM pa_resource_assignments
718 WHERE resource_assignment_id = p_tgt_res_asg_id_tab(main_loop);
719 ELSE
720 l_rate_based_flag:='N';
721 END IF;
722
723 /* When generate cost based revenue version, always take PFC
724 When target version is not multi currency enabled, take PC */
725 l_currency_flag := 'TC';
726
727 l_rev_gen_method := nvl(P_FP_COLS_TGT_REC.x_revenue_derivation_method,PA_FP_GEN_FCST_PG_PKG.
728 GET_REV_GEN_METHOD(P_FP_COLS_TGT_REC.x_project_id)); --Bug 5152892
729
730 IF (p_fp_cols_tgt_rec.x_version_type = 'REVENUE' and l_rev_gen_method = 'C') THEN
731 l_currency_flag := 'PFC';
732 ELSIF p_fp_cols_tgt_rec.X_PLAN_IN_MULTI_CURR_FLAG = 'N' THEN
733 l_currency_flag := 'PC';
734 END IF;
735
736 l_pc_currency_code := p_fp_cols_tgt_rec.x_project_currency_code;
737 l_pfc_currency_code := p_fp_cols_tgt_rec.x_projfunc_currency_code;
738 l_target_version_type := p_fp_cols_tgt_rec.x_version_type;
739 IF p_etc_source_code_tab(main_loop) = 'FINANCIAL_PLAN' THEN
740 /* Get actual amounts from financial side - PA_FP_FCST_GEN_TMP1 */
741 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
742 DECODE(l_currency_flag,
743 'PC',l_pc_currency_code,
744 'TC',txn_currency_code,
745 'PFC',l_pfc_currency_code),
746 SUM(NVL(quantity,0)),
747 SUM(DECODE(l_currency_flag,
748 'PC', NVL(prj_raw_cost,0),
749 'TC', NVL(txn_raw_cost,0),
750 'PFC', NVL(pou_raw_cost,0))),
751 SUM(DECODE(l_currency_flag,
752 'PC', NVL(prj_brdn_cost,0),
753 'TC', NVL(txn_brdn_cost,0),
754 'PFC', NVL(pou_brdn_cost,0))),
755 SUM(DECODE(l_currency_flag,
756 'PC', NVL(prj_revenue,0),
757 'TC', NVL(txn_revenue,0),
758 'PFC', NVL(pou_revenue,0)))
759 BULK COLLECT INTO
760 l_act_currency_code_tab,
761 l_act_quantity_tab,
762 l_act_raw_cost_tab,
763 l_act_brdn_cost_tab,
764 l_act_revenue_tab
765 FROM PA_FP_FCST_GEN_TMP1
766 WHERE project_element_id = p_task_id_tab(main_loop)
767 AND res_list_member_id = p_res_list_member_id_tab(main_loop)
768 AND data_type_code = 'ETC_FP'
769 GROUP BY DECODE(l_currency_flag,
770 'PC',l_pc_currency_code,
771 'TC',txn_currency_code,
772 'PFC', l_pfc_currency_code);
773
774 IF l_rate_based_flag = 'N' THEN
775 l_act_quantity_tab := l_act_raw_cost_tab;
776 END IF;
777
778 ELSIF p_etc_source_code_tab(main_loop) = 'WORKPLAN_RESOURCES' THEN
779 IF P_PA_DEBUG_MODE = 'Y' THEN
780 pa_fp_gen_amount_utils.fp_debug(
781 p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB1.'||
782 'GET_WP_ACTUALS_FOR_RA',
783 p_module_name => l_module_name,
784 p_log_level => 5);
785 END IF;
786 PA_FP_GEN_FCST_AMT_PUB1.GET_WP_ACTUALS_FOR_RA
787 (P_FP_COLS_SRC_REC => p_fp_cols_src_rec_wp,
788 P_FP_COLS_TGT_REC => p_fp_cols_tgt_rec,
789 P_SRC_RES_ASG_ID => p_src_res_asg_id_tab(main_loop),
790 P_TASK_ID => p_task_id_tab(main_loop),
791 P_RES_LIST_MEM_ID => p_res_list_member_id_tab(main_loop),
792 P_ACTUALS_THRU_DATE => p_actuals_thru_date,
793 X_ACT_QUANTITY => lx_act_quantity,
794 X_ACT_TXN_CURRENCY_CODE => lx_act_txn_currency_code,
795 X_ACT_TXN_RAW_COST => lx_act_txn_raw_cost,
796 X_ACT_TXN_BRDN_COST => lx_act_txn_brdn_cost,
797 X_ACT_PC_RAW_COST => lx_act_pc_raw_cost,
798 X_ACT_PC_BRDN_COST => lx_act_pc_brdn_cost,
799 X_ACT_PFC_RAW_COST => lx_act_pfc_raw_cost,
800 X_ACT_PFC_BRDN_COST => lx_act_pfc_brdn_cost,
801 X_RETURN_STATUS => x_return_status,
802 X_MSG_COUNT => x_msg_count,
803 X_MSG_DATA => x_msg_data );
804 IF P_PA_DEBUG_MODE = 'Y' THEN
805 pa_fp_gen_amount_utils.fp_debug(
806 p_msg => 'After calling PA_FP_GEN_FCST_AMT_PUB1.'||
807 'GET_WP_ACTUALS_FOR_RA in earned_value:'||x_return_status,
808 p_module_name => l_module_name,
809 p_log_level => 5);
810 END IF;
811 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
812 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
813 END IF;
814
815 IF l_currency_flag = 'PC' THEN
816 l_act_currency_code_tab(1) := l_pc_currency_code;
817 IF l_rate_based_flag = 'Y' THEN
818 l_act_quantity_tab(1) := lx_act_quantity;
819 ELSE
820 l_act_quantity_tab(1) := lx_act_pc_raw_cost;
821 END IF;
822 ELSIF l_currency_flag = 'TC' THEN
823 l_act_currency_code_tab(1) := lx_act_txn_currency_code;
824 IF l_rate_based_flag = 'Y' THEN
825 l_act_quantity_tab(1) := lx_act_quantity;
826 ELSE
827 l_act_quantity_tab(1) := lx_act_txn_raw_cost;
828 END IF;
829 ELSIF l_currency_flag = 'PFC' THEN
830 l_act_currency_code_tab(1) := l_pfc_currency_code;
831 IF l_rate_based_flag = 'Y' THEN
832 l_act_quantity_tab(1) := lx_act_quantity;
833 ELSE
834 l_act_quantity_tab(1) := lx_act_pfc_raw_cost;
835 END IF;
836 END IF;
837 END IF;
838
839 /* Get total ETC quantity */
840 FOR i IN 1..l_act_currency_code_tab.count LOOP
841 /* ???Do we need to handle zero actuals here??*/
842 l_etc_quantity_tab(i) := l_act_quantity_tab(i)
843 * (1 - l_percent_complete)/l_percent_complete;
844 END LOOP;
845
846 /*When not taking periodic rates, we need to calculate out the average rates
847 from the source resource assignments that are mapped to the current target
848 resource assignment. */
849 FOR i IN 1..l_act_currency_code_tab.count LOOP
850 IF p_etc_source_code_tab(main_loop) = 'FINANCIAL_PLAN' THEN
851 SELECT /*+ INDEX(PA_FP_FCST_GEN_TMP1,PA_FP_FCST_GEN_TMP1_N1)*/
852 SUM(NVL(quantity,0)),
853 SUM(DECODE(l_currency_flag,
854 'PC', NVL(prj_raw_cost,0),
855 'TC', NVL(txn_raw_cost,0),
856 'PFC', NVL(pou_raw_cost,0))),
857 SUM(DECODE(l_currency_flag,
858 'PC', NVL(prj_brdn_cost,0),
859 'TC', NVL(txn_brdn_cost,0),
860 'PFC', NVL(pou_brdn_cost,0))),
861 SUM(DECODE(l_currency_flag,
862 'PC', NVL(prj_revenue,0),
863 'TC', NVL(txn_revenue,0),
864 'PFC', NVL(pou_revenue,0))),
865 SUM(NVL(prj_raw_cost,0)),
866 SUM(NVL(prj_brdn_cost,0)),
867 SUM(NVL(prj_revenue,0)),
868 SUM(NVL(pou_raw_cost,0)),
869 SUM(NVL(pou_brdn_cost,0)),
870 SUM(NVL(pou_revenue,0))
871 INTO l_txn_rate_quantity,
872 l_txn_rate_raw_cost,
873 l_txn_rate_brdn_cost,
874 l_txn_rate_revenue,
875 l_pc_rate_raw_cost,
876 l_pc_rate_brdn_cost,
877 l_pc_rate_revenue,
878 l_pfc_rate_raw_cost,
879 l_pfc_rate_brdn_cost,
880 l_pfc_rate_revenue
881 FROM PA_FP_FCST_GEN_TMP1
882 WHERE project_element_id = p_task_id_tab(main_loop)
883 AND res_list_member_id = p_res_list_member_id_tab(main_loop)
884 AND data_type_code = 'ETC_FP'
885 AND DECODE(l_currency_flag, 'TC',txn_currency_code,
886 'PC', l_act_currency_code_tab(i),
887 'PFC',l_act_currency_code_tab(i)) = l_act_currency_code_tab(i);
888 ELSIF p_etc_source_code_tab(main_loop) = 'WORKPLAN_RESOURCES' THEN
889 l_txn_rate_quantity := lx_act_quantity;
890 IF l_currency_flag = 'PC' THEN
891 l_txn_rate_raw_cost := lx_act_pc_raw_cost;
892 l_txn_rate_brdn_cost := lx_act_pc_brdn_cost;
893 ELSIF l_currency_flag = 'PFC' THEN
894 l_txn_rate_raw_cost := lx_act_pfc_raw_cost;
895 l_txn_rate_brdn_cost := lx_act_pfc_brdn_cost;
896 ELSE
897 l_txn_rate_raw_cost := lx_act_txn_raw_cost;
898 l_txn_rate_brdn_cost := lx_act_txn_brdn_cost;
899 END IF;
900 l_txn_rate_revenue := 0;
901 l_pc_rate_raw_cost := lx_act_pc_raw_cost;
902 l_pc_rate_brdn_cost := lx_act_pc_brdn_cost;
903 l_pc_rate_revenue := 0;
904 l_pfc_rate_raw_cost := lx_act_pfc_raw_cost;
905 l_pfc_rate_brdn_cost := lx_act_pfc_brdn_cost;
906 l_pfc_rate_revenue := 0;
907 END IF;
908
909 IF l_rate_based_flag = 'N' THEN
910 l_txn_rate_quantity := l_txn_rate_raw_cost;
911 END IF;
912
913 IF l_txn_rate_quantity <> 0 THEN
914 l_txn_raw_cost_rate_tab(i) := l_txn_rate_raw_cost
915 / l_txn_rate_quantity;
916 l_txn_brdn_cost_rate_tab(i) := l_txn_rate_brdn_cost
917 / l_txn_rate_quantity;
918 l_txn_revenue_rate_tab(i) := l_txn_rate_revenue
919 / l_txn_rate_quantity;
920 l_pc_raw_cost_rate_tab(i) := l_pc_rate_raw_cost
921 / l_txn_rate_quantity;
922 l_pc_brdn_cost_rate_tab(i) := l_pc_rate_brdn_cost
923 / l_txn_rate_quantity;
924 l_pc_revenue_rate_tab(i) := l_pc_rate_revenue
925 / l_txn_rate_quantity;
926 l_pfc_raw_cost_rate_tab(i) := l_pfc_rate_raw_cost
927 / l_txn_rate_quantity;
928 l_pfc_brdn_cost_rate_tab(i) := l_pfc_rate_brdn_cost
929 / l_txn_rate_quantity;
930 l_pfc_revenue_rate_tab(i) := l_pfc_rate_revenue
931 / l_txn_rate_quantity;
932 ELSE
933 l_txn_raw_cost_rate_tab(i) := NULL;
934 l_txn_brdn_cost_rate_tab(i) := NULL;
935 l_txn_revenue_rate_tab(i) := NULL;
936 l_pc_raw_cost_rate_tab(i) := NULL;
937 l_pc_brdn_cost_rate_tab(i) := NULL;
938 l_pc_revenue_rate_tab(i) := NULL;
939 l_pfc_raw_cost_rate_tab(i) := NULL;
940 l_pfc_brdn_cost_rate_tab(i) := NULL;
941 l_pfc_revenue_rate_tab(i) := NULL;
942 END IF;
943 l_count := l_count + 1;
944 l_blk_src_res_asg_id_tab(l_count) := P_SRC_RES_ASG_ID_TAB(main_loop);
945 l_blk_tgt_res_asg_id_tab(l_count) := P_TGT_RES_ASG_ID_TAB(main_loop);
946 l_blk_act_currency_code_tab(l_count) := l_act_currency_code_tab(i);
947 l_blk_etc_quantity_tab(l_count) := l_etc_quantity_tab(i);
948 l_blk_etc_txn_rcost_tab(l_count) := l_etc_quantity_tab(i) * l_txn_raw_cost_rate_tab(i);
949 l_blk_etc_txn_bcost_tab(l_count) := l_etc_quantity_tab(i) * l_txn_brdn_cost_rate_tab(i);
950 l_blk_etc_txn_revenue_tab(l_count) := l_etc_quantity_tab(i) * l_txn_revenue_rate_tab(i);
951 l_blk_etc_pc_rcost_tab(l_count) := l_etc_quantity_tab(i) * l_pc_raw_cost_rate_tab(i);
952 l_blk_etc_pc_bcost_tab(l_count) := l_etc_quantity_tab(i) * l_pc_brdn_cost_rate_tab(i);
953 l_blk_etc_pc_revenue_tab(l_count) := l_etc_quantity_tab(i) * l_pc_revenue_rate_tab(i);
954 l_blk_etc_pfc_rcost_tab(l_count) := l_etc_quantity_tab(i) * l_pfc_raw_cost_rate_tab(i);
955 l_blk_etc_pfc_bcost_tab(l_count) := l_etc_quantity_tab(i) * l_pfc_brdn_cost_rate_tab(i);
956 l_blk_etc_pfc_revenue_tab(l_count) := l_etc_quantity_tab(i) * l_pfc_revenue_rate_tab(i);
957 END LOOP;
958 EXCEPTION
959 WHEN CONTINUE_LOOP THEN
960 l_dummy := 1;
961 WHEN OTHERS THEN
962 RAISE;
963 END;
964 END LOOP; /*Main loop*/
965
966 /* Insert total ETC amounts */
967 /* If commitment is not included, record is inserted directly as
968 'ETC' record, if commitment is to be considered, record is inserted
969 as 'TOTAL_ETC' for further processing. */
970 IF P_FP_COLS_TGT_REC.X_GEN_INCL_OPEN_COMM_FLAG = 'Y' THEN
971 l_transaction_source_code := 'TOTAL_ETC';
972 ELSE
973 l_transaction_source_code := 'ETC';
974 END IF;
975
976 FORALL I IN 1..l_blk_act_currency_code_tab.count
977 INSERT INTO PA_FP_CALC_AMT_TMP2 (
978 RESOURCE_ASSIGNMENT_ID,
979 TARGET_RES_ASG_ID,
980 ETC_CURRENCY_CODE,
981 ETC_PLAN_QUANTITY,
982 ETC_TXN_RAW_COST,
983 ETC_TXN_BURDENED_COST,
984 ETC_TXN_REVENUE,
985 ETC_PC_RAW_COST,
986 ETC_PC_BURDENED_COST,
987 ETC_PC_REVENUE,
988 ETC_PFC_RAW_COST,
989 ETC_PFC_BURDENED_COST,
990 ETC_PFC_REVENUE,
991 TRANSACTION_SOURCE_CODE )
992 VALUES (
993 l_blk_src_res_asg_id_tab(i),
994 l_blk_tgt_res_asg_id_tab(i),
995 l_blk_act_currency_code_tab(i),
996 l_blk_etc_quantity_tab(i),
997 l_blk_etc_txn_rcost_tab(i),
998 l_blk_etc_txn_bcost_tab(i),
999 l_blk_etc_txn_revenue_tab(i),
1000 l_blk_etc_pc_rcost_tab(i),
1001 l_blk_etc_pc_bcost_tab(i),
1002 l_blk_etc_pc_revenue_tab(i),
1003 l_blk_etc_pfc_rcost_tab(i),
1004 l_blk_etc_pfc_bcost_tab(i),
1005 l_blk_etc_pfc_revenue_tab(i),
1006 l_transaction_source_code);
1007
1008 IF l_remain_bdgt_src_ra_id_tab.count > 0 THEN
1009 IF P_PA_DEBUG_MODE = 'Y' THEN
1010 pa_fp_gen_amount_utils.fp_debug(
1011 p_msg => 'Before calling PA_FP_GEN_FCST_AMT_PUB3.'||
1012 'GET_ETC_REMAIN_BDGT_AMTS',
1013 p_module_name => l_module_name,
1014 p_log_level => 5);
1015 END IF;
1016 PA_FP_GEN_FCST_AMT_PUB3.GET_ETC_REMAIN_BDGT_AMTS_BLK(
1017 P_SRC_RES_ASG_ID_TAB => l_remain_bdgt_src_ra_id_tab,
1018 P_TGT_RES_ASG_ID_TAB => l_remain_bdgt_tgt_ra_id_tab,
1019 P_FP_COLS_SRC_REC_FP => P_FP_COLS_SRC_REC_FP,
1020 P_FP_COLS_SRC_REC_WP => P_FP_COLS_SRC_REC_WP,
1021 P_FP_COLS_TGT_REC => P_FP_COLS_TGT_REC,
1022 P_TASK_ID_TAB => l_remain_bdgt_task_id_tab,
1023 P_RES_LIST_MEMBER_ID_TAB => l_remain_bdgt_rlm_id_tab,
1024 P_ETC_SOURCE_CODE_TAB => l_remain_bdgt_etc_src_tab,
1025 P_WP_STRUCTURE_VERSION_ID => P_WP_STRUCTURE_VERSION_ID,
1026 P_ACTUALS_THRU_DATE => P_ACTUALS_THRU_DATE,
1027 P_PLANNING_OPTIONS_FLAG => P_PLANNING_OPTIONS_FLAG,
1028 X_RETURN_STATUS => X_RETURN_STATUS,
1029 X_MSG_COUNT => X_MSG_COUNT,
1030 X_MSG_DATA => X_MSG_DATA);
1031 IF P_PA_DEBUG_MODE = 'Y' THEN
1032 pa_fp_gen_amount_utils.fp_debug(
1033 p_msg => 'After calling PA_FP_GEN_FCST_AMT_PUB3.'||
1034 'GET_ETC_REMAIN_BDGT_AMTS:'||x_return_status,
1035 p_module_name => l_module_name,
1036 p_log_level => 5);
1037 END IF;
1038 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
1039 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1040 END IF;
1041 END IF;
1042
1043 IF P_PA_DEBUG_MODE = 'Y' THEN
1044 PA_DEBUG.RESET_CURR_FUNCTION;
1045 END IF;
1046 EXCEPTION
1047 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1048 l_msg_count := FND_MSG_PUB.count_msg;
1049 IF l_msg_count = 1 THEN
1050 PA_INTERFACE_UTILS_PUB.get_messages
1051 ( p_encoded => FND_API.G_TRUE,
1052 p_msg_index => 1,
1053 p_msg_count => l_msg_count,
1054 p_msg_data => l_msg_data,
1055 p_data => l_data,
1056 p_msg_index_out => l_msg_index_out);
1057 x_msg_data := l_data;
1058 x_msg_count := l_msg_count;
1059 ELSE
1060 x_msg_count := l_msg_count;
1061 END IF;
1062
1063 ROLLBACK;
1064 x_return_status := FND_API.G_RET_STS_ERROR;
1065
1066 IF P_PA_DEBUG_MODE = 'Y' THEN
1067 pa_fp_gen_amount_utils.fp_debug
1068 (p_msg => 'Invalid Arguments Passed',
1069 p_module_name => l_module_name,
1070 p_log_level => 5);
1071 PA_DEBUG.RESET_CURR_FUNCTION;
1072 END IF;
1073 RAISE;
1074 WHEN OTHERS THEN
1075 rollback;
1076 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1077 x_msg_count := 1;
1078 x_msg_data := substr(sqlerrm,1,240);
1079 -- dbms_output.put_line('error msg :'||x_msg_data);
1080 FND_MSG_PUB.add_exc_msg
1081 ( p_pkg_name => 'PA_FP_GEN_FCST_AMT_PUB5',
1082 p_procedure_name => 'GEN_ETC_EARNED_VALUE_AMTS_BLK',
1083 p_error_text => substr(sqlerrm,1,240));
1084
1085 IF P_PA_DEBUG_MODE = 'Y' THEN
1086 pa_fp_gen_amount_utils.fp_debug
1087 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
1088 p_module_name => l_module_name,
1089 p_log_level => 5);
1090 PA_DEBUG.RESET_CURR_FUNCTION;
1091 END IF;
1092 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1093 END GET_ETC_EARNED_VALUE_AMTS_BLK;
1094
1095 END PA_FP_GEN_FCST_AMT_PUB5;