[Home] [Help]
PACKAGE BODY: APPS.PA_FP_MAP_BV_PUB
Source
1 PACKAGE body PA_FP_MAP_BV_PUB as
2 /* $Header: PAFPMBTB.pls 120.0 2005/05/29 19:14:08 appldev noship $ */
3 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4
5 PROCEDURE GEN_MAP_BV_TO_TARGET_RL
6 (P_SOURCE_BV_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
7 P_TARGET_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
8 P_ETC_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
9 P_CB_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
10 P_COMMIT_FLAG IN VARCHAR2,
11 P_INIT_MSG_FLAG IN VARCHAR2,
12 P_ACTUAL_THRU_DATE IN PA_PERIODS_ALL.END_DATE%TYPE,
13 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
14 X_MSG_COUNT OUT NOCOPY NUMBER,
15 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
16
17 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_map_bv_pub.gen_map_bv_to_target_rl';
18
19 CURSOR txn_sum_csr(c_project_currency_code PA_BUDGET_LINES.PROJECT_CURRENCY_CODE%TYPE,
20 c_multi_curr_flag PA_PROJ_FP_OPTIONS.PLAN_IN_MULTI_CURR_FLAG%TYPE)IS
21 SELECT /*+ INDEX(t3,PA_FP_CALC_AMT_TMP3_N2)*/
22 t3.res_list_member_id,
23 ra.task_id,
24 decode(c_multi_curr_flag,'Y',bl.txn_currency_code,
25 'N',c_project_currency_code),
26 sum(bl.quantity),
27 sum(decode(c_multi_curr_flag,'Y',bl.txn_raw_cost,
28 'N',bl.project_raw_cost)),
29 sum(decode(c_multi_curr_flag,'Y',bl.txn_burdened_cost,
30 'N',bl.project_burdened_cost)),
31 sum(decode(c_multi_curr_flag,'Y',bl.txn_revenue,
32 'N',bl.project_revenue)),
33 sum(bl.project_raw_cost),
34 sum(bl.project_burdened_cost),
35 sum(bl.project_revenue),
36 sum(bl.raw_cost),
37 sum(bl.burdened_cost),
38 sum(bl.revenue)
39 FROM pa_resource_assignments ra,
40 pa_budget_lines bl,
41 pa_fp_calc_amt_tmp3 t3
42 WHERE ra.resource_assignment_id = bl.resource_assignment_id
43 AND ra.resource_assignment_id = t3.res_asg_id
44 AND ra.budget_version_id = p_source_bv_id
45 AND bl.end_date <= nvl(p_actual_thru_date,bl.end_date)
46 and bl.cost_rejection_code is null
47 and bl.revenue_rejection_code is null
48 and bl.burden_rejection_code is null
49 and bl.other_rejection_code is null
50 and bl.pc_cur_conv_rejection_code is null
51 and bl.pfc_cur_conv_rejection_code is null
52 GROUP BY
53 t3.res_list_member_id,
54 ra.task_id,
55 decode(c_multi_curr_flag,'Y',bl.txn_currency_code,
56 'N',c_project_currency_code);
57
58 l_txn_currency_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
59 l_rlm_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
60 l_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
61 l_qty_tab PA_PLSQL_DATATYPES.NumTabTyp;
62 l_txn_raw_cost_sum_tab PA_PLSQL_DATATYPES.NumTabTyp;
63 l_txn_burdend_cost_sum_tab PA_PLSQL_DATATYPES.NumTabTyp;
64 l_txn_revenue_sum_tab PA_PLSQL_DATATYPES.NumTabTyp;
65 l_pc_raw_cost_sum_tab PA_PLSQL_DATATYPES.NumTabTyp;
66 l_pc_burdend_cost_sum_tab PA_PLSQL_DATATYPES.NumTabTyp;
67 l_pc_revenue_sum_tab PA_PLSQL_DATATYPES.NumTabTyp;
68 l_pfc_raw_cost_sum_tab PA_PLSQL_DATATYPES.NumTabTyp;
69 l_pfc_burdend_cost_sum_tab PA_PLSQL_DATATYPES.NumTabTyp;
70 l_pfc_revenue_sum_tab PA_PLSQL_DATATYPES.NumTabTyp;
71
72
73 /* Local variable to call the pa_rlmi_rbs_map_pub.map_rlmi_rbs api */
74 l_rbs_version_id Number;
75 l_calling_process Varchar2(30);
76 l_calling_context varchar2(30);
77 l_process_code varchar2(30);
78 l_calling_mode Varchar2(30);
79 l_init_msg_list_flag Varchar2(1);
80 l_commit_flag Varchar2(1);
81 l_TXN_SOURCE_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
82 l_TXN_SOURCE_TYPE_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
83 l_PERSON_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
84 l_JOB_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
85 l_ORGANIZATION_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
86 l_VENDOR_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
87 l_EXPENDITURE_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
88 l_EVENT_TYPE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
89 l_NON_LABOR_RESOURCE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
90 l_EXPENDITURE_CATEGORY_tab PA_PLSQL_DATATYPES.Char30TabTyp;
91 l_REVENUE_CATEGORY_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
92 l_NLR_ORGANIZATION_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
93 l_EVENT_CLASSIFICATION_tab PA_PLSQL_DATATYPES.Char30TabTyp;
94 l_SYS_LINK_FUNCTION_tab PA_PLSQL_DATATYPES.Char30TabTyp;
95 l_PROJECT_ROLE_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
96 l_RESOURCE_CLASS_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
97 l_MFC_COST_TYPE_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
98 l_RESOURCE_CLASS_FLAG_tab PA_PLSQL_DATATYPES.Char1TabTyp;
99 l_FC_RES_TYPE_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
100 l_INVENTORY_ITEM_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
101 l_ITEM_CATEGORY_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
102 l_PERSON_TYPE_CODE_tab PA_PLSQL_DATATYPES.Char30TabTyp;
103 l_BOM_RESOURCE_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
104 l_NAMED_ROLE_tab PA_PLSQL_DATATYPES.Char80TabTyp;
105 l_INCURRED_BY_RES_FLAG_tab PA_PLSQL_DATATYPES.Char1TabTyp;
106 l_RATE_BASED_FLAG_tab PA_PLSQL_DATATYPES.Char1TabTyp;
107 l_TXN_TASK_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
108 l_TXN_WBS_ELEMENT_VER_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
109 l_TXN_RBS_ELEMENT_ID_tab PA_PLSQL_DATATYPES.IdTabTyp;
110 l_TXN_PLAN_START_DATE_tab PA_PLSQL_DATATYPES.DateTabTyp;
111 l_TXN_PLAN_END_DATE_tab PA_PLSQL_DATATYPES.DateTabTyp;
112 l_res_list_member_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
113 l_rbs_element_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
114 l_txn_accum_header_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
115 l_txn_src_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
116
117 l_tsk_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
118 l_resrc_assgn_id PA_PLSQL_DATATYPES.IdTabTyp;
119 l_count NUMBER;
120 l_msg_count NUMBER;
121 l_data VARCHAR2(2000);
122 l_msg_data VARCHAR2(2000);
123 l_msg_index_out NUMBER;
124
125 l_rl_uncategorized_flag VARCHAR2(1);
126 l_uc_res_list_rlm_id NUMBER;
127
128 BEGIN
129 --Setting initial values
130 IF p_init_msg_flag = 'Y' THEN
131 FND_MSG_PUB.initialize;
132 END IF;
133
134 X_MSG_COUNT := 0;
135 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
136
137 IF p_pa_debug_mode = 'Y' and p_init_msg_flag = 'Y' THEN
138 PA_DEBUG.init_err_stack('PA_FP_MAP_BV_PUB.GEN_MAP_BV_TO_TARGET_RL');
139 ELSIF p_pa_debug_mode = 'Y' and p_init_msg_flag = 'N' THEN
140 pa_debug.set_curr_function( p_function => 'GEN_MAP_BV_TO_TARGET_RL'
141 ,p_debug_mode => p_pa_debug_mode);
142 END IF;
143
144
145 l_rl_uncategorized_flag := PA_FP_GEN_AMOUNT_UTILS.
146 GET_RL_UNCATEGORIZED_FLAG(P_RESOURCE_LIST_ID => p_etc_fp_cols_rec.x_resource_list_id);
147
148 l_uc_res_list_rlm_id := PA_FP_GEN_AMOUNT_UTILS.
149 GET_UC_RES_LIST_RLM_ID(P_RESOURCE_LIST_ID => p_etc_fp_cols_rec.x_resource_list_id,
150 P_RESOURCE_CLASS_CODE => 'FINANCIAL_ELEMENTS');
151
152 IF p_cb_fp_cols_rec.x_resource_list_id <>
153 p_etc_fp_cols_rec.x_resource_list_id
154 AND l_rl_uncategorized_flag = 'N' THEN
155 --Calling the map_rlmi_rbs api
156 IF p_pa_debug_mode = 'Y' THEN
157 pa_fp_gen_amount_utils.fp_debug
158 (p_msg => 'Before calling
159 pa_rlmi_rbs_map_pub.map_rlmi_rbs',
160 p_module_name => l_module_name,
161 p_log_level => 5);
162 END IF;
163 /* PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS
164 ( p_budget_version_id => null,
165 p_resource_list_id =>
166 p_etc_fp_cols_rec.x_resource_list_id,
167 p_rbs_version_id => l_rbs_version_id,
168 p_calling_process => 'BUDGET_GENERATION',
169 p_calling_context => 'PLSQL',
170 p_process_code => l_process_code,
171 p_calling_mode => 'BUDGET_VERSION',
172 p_init_msg_list_flag => l_init_msg_list_flag,
173 p_commit_flag => l_commit_flag,
174 p_TXN_SOURCE_ID_tab => l_TXN_SOURCE_ID_tab,
175 p_TXN_SOURCE_TYPE_CODE_tab => l_TXN_SOURCE_TYPE_CODE_tab,
176 p_PERSON_ID_tab => l_PERSON_ID_tab,
177 p_JOB_ID_tab => l_JOB_ID_tab,
178 p_ORGANIZATION_ID_tab => l_ORGANIZATION_ID_tab,
179 p_VENDOR_ID_tab => l_VENDOR_ID_tab,
180 p_EXPENDITURE_TYPE_tab => l_EXPENDITURE_TYPE_tab,
181 p_EVENT_TYPE_tab => l_EVENT_TYPE_tab,
182 p_NON_LABOR_RESOURCE_tab => l_NON_LABOR_RESOURCE_tab,
183 p_EXPENDITURE_CATEGORY_tab => l_EXPENDITURE_CATEGORY_tab,
184 p_REVENUE_CATEGORY_CODE_tab=> l_REVENUE_CATEGORY_CODE_tab,
185 p_NLR_ORGANIZATION_ID_tab => l_NLR_ORGANIZATION_ID_tab,
186 p_EVENT_CLASSIFICATION_tab => l_EVENT_CLASSIFICATION_tab,
187 p_SYS_LINK_FUNCTION_tab => l_SYS_LINK_FUNCTION_tab,
188 p_PROJECT_ROLE_ID_tab => l_PROJECT_ROLE_ID_tab,
189 p_RESOURCE_CLASS_CODE_tab => l_RESOURCE_CLASS_CODE_tab,
190 p_MFC_COST_TYPE_ID_tab => l_MFC_COST_TYPE_ID_tab,
191 p_RESOURCE_CLASS_FLAG_tab => l_RESOURCE_CLASS_FLAG_tab,
192 p_FC_RES_TYPE_CODE_tab => l_FC_RES_TYPE_CODE_tab,
193 p_INVENTORY_ITEM_ID_tab => l_INVENTORY_ITEM_ID_tab,
194 p_ITEM_CATEGORY_ID_tab => l_ITEM_CATEGORY_ID_tab,
195 p_PERSON_TYPE_CODE_tab => l_PERSON_TYPE_CODE_tab,
196 p_BOM_RESOURCE_ID_tab => l_BOM_RESOURCE_ID_tab,
197 p_NAMED_ROLE_tab => l_NAMED_ROLE_tab,
198 p_INCURRED_BY_RES_FLAG_tab => l_INCURRED_BY_RES_FLAG_tab,
199 p_RATE_BASED_FLAG_tab => l_RATE_BASED_FLAG_tab,
200 p_TXN_TASK_ID_tab => l_TXN_TASK_ID_tab,
201 p_TXN_WBS_ELEMENT_VER_ID_tab=>l_TXN_WBS_ELEMENT_VER_ID_tab,
202 p_TXN_RBS_ELEMENT_ID_tab => l_TXN_RBS_ELEMENT_ID_tab,
203 p_TXN_PLAN_START_DATE_tab => l_TXN_PLAN_START_DATE_tab,
204 p_TXN_PLAN_END_DATE_tab => l_TXN_PLAN_END_DATE_tab,
205 x_txn_source_id_tab => l_txn_src_id_tab,
206 x_res_list_member_id_tab => l_res_list_member_id_tab,
207 x_rbs_element_id_tab => l_rbs_element_id_tab,
208 x_txn_accum_header_id_tab => l_txn_accum_header_id_tab,
209 x_return_status => x_return_status,
210 x_msg_count => x_msg_count,
211 x_msg_data => x_msg_data); */
212 /* bug 3576766 : p_project_id parameter added for
213 non centrally controlled resource list mapping. */
214 --hr_utility.trace('@@@before calling PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS :'||x_return_status);
215 PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS
216 (p_project_id => P_TARGET_FP_COLS_REC.x_project_id,
217 p_budget_version_id => null,
218 p_resource_list_id =>
219 p_etc_fp_cols_rec.x_resource_list_id,
220 p_rbs_version_id => null,
221 p_calling_process => 'BUDGET_GENERATION',
222 p_calling_context => 'PLSQL',
223 p_process_code => 'RES_MAP',
224 p_calling_mode => 'BUDGET_VERSION',
225 p_init_msg_list_flag => 'N',
226 p_commit_flag => 'N',
227 x_txn_source_id_tab => l_txn_src_id_tab,
228 x_res_list_member_id_tab => l_res_list_member_id_tab,
229 x_rbs_element_id_tab => l_rbs_element_id_tab,
230 x_txn_accum_header_id_tab => l_txn_accum_header_id_tab,
231 x_return_status => x_return_status,
232 x_msg_count => x_msg_count,
233 x_msg_data => x_msg_data);
234 --hr_utility.trace('@@after calling PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS :'||x_return_status);
235 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
236 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
237 END IF;
238 IF p_pa_debug_mode = 'Y' THEN
239 pa_fp_gen_amount_utils.fp_debug
240 (p_msg => 'Status after calling
241 pa_rlmi_rbs_map_pub.map_rlmi_rbs'
242 ||x_return_status,
243 p_module_name => l_module_name,
244 p_log_level => 5);
245 END IF;
246 /* dbms_output.put_line('Status of map_rlmi_rbs api: '
247 ||X_RETURN_STATUS); */
248 FORALL i IN 1..l_res_list_member_id_tab.count
249 INSERT INTO PA_FP_CALC_AMT_TMP3
250 (plan_version_id,
251 res_list_member_id,
252 res_asg_id)
253 VALUES
254 (p_etc_fp_cols_rec.x_budget_version_id,
255 l_res_list_member_id_tab(i),
256 l_txn_src_id_tab(i));
257 SELECT ra.task_id,
258 ra.resource_assignment_id
259 BULK COLLECT
260 INTO l_tsk_id_tab,
261 l_resrc_assgn_id
262 FROM pa_resource_assignments ra
263 WHERE ra.budget_version_id = p_etc_fp_cols_rec.
264 x_budget_version_id;
265
266 FORALL m in 1..l_tsk_id_tab.count
267 UPDATE /*+ INDEX(PA_FP_CALC_AMT_TMP3,PA_FP_CALC_AMT_TMP3_N2)*/
268 PA_FP_CALC_AMT_TMP3
269 SET task_id = l_tsk_id_tab(m)
270 WHERE res_asg_id = l_resrc_assgn_id(m);
271
272 OPEN txn_sum_csr(p_target_fp_cols_rec.x_project_currency_code,
273 p_target_fp_cols_rec.x_plan_in_multi_curr_flag);
274 FETCH txn_sum_csr
275 BULK COLLECT
276 INTO l_rlm_id_tab,
277 l_task_id_tab,
278 l_txn_currency_code_tab,
279 l_qty_tab,
280 l_txn_raw_cost_sum_tab,
281 l_txn_burdend_cost_sum_tab,
282 l_txn_revenue_sum_tab,
283 l_pc_raw_cost_sum_tab,
284 l_pc_burdend_cost_sum_tab,
285 l_pc_revenue_sum_tab,
286 l_pfc_raw_cost_sum_tab,
287 l_pfc_burdend_cost_sum_tab,
288 l_pfc_revenue_sum_tab;
289 CLOSE txn_sum_csr;
290
291 DELETE /*+ INDEX(PA_FP_CALC_AMT_TMP3,PA_FP_CALC_AMT_TMP3_N1)*/
292 FROM pa_fp_calc_amt_tmp3
293 WHERE plan_version_id =
294 P_ETC_FP_COLS_REC.x_budget_version_id;
295
296 FORALL k in 1..l_txn_src_id_tab.count
297 INSERT INTO pa_fp_calc_amt_tmp3
298 (plan_version_id,
299 task_id,
300 res_list_member_id,
301 res_asg_id,
302 txn_currency_code,
303 quantity,
304 txn_raw_cost,
305 txn_burdened_cost,
306 txn_revenue,
307 pc_raw_cost,
308 pc_burdened_cost,
309 pc_revenue,
310 pfc_raw_cost,
311 pfc_burdened_cost,
312 pfc_revenue)
313 VALUES (P_ETC_FP_COLS_REC.x_budget_version_id,
314 l_task_id_tab(k),
315 l_rlm_id_tab(k),
316 l_txn_src_id_tab(k),
317 l_txn_currency_code_tab(k),
318 l_qty_tab(k),
319 l_txn_raw_cost_sum_tab(k),
320 l_txn_burdend_cost_sum_tab(k),
321 l_txn_revenue_sum_tab(k),
322 l_pc_raw_cost_sum_tab(k),
323 l_pc_burdend_cost_sum_tab(k),
324 l_pc_revenue_sum_tab(k),
325 l_pfc_raw_cost_sum_tab(k),
326 l_pfc_burdend_cost_sum_tab(k),
327 l_pfc_revenue_sum_tab(k));
328 ELSIF p_cb_fp_cols_rec.x_resource_list_id =
329 p_etc_fp_cols_rec.x_resource_list_id THEN
330 INSERT INTO PA_FP_CALC_AMT_TMP3
331 (plan_version_id,
332 task_id,
333 res_list_member_id,
334 res_asg_id,
335 txn_currency_code,
336 quantity,
337 txn_raw_cost,
338 txn_burdened_cost,
339 txn_revenue,
340 pc_raw_cost,
341 pc_burdened_cost,
342 pc_revenue,
343 pfc_raw_cost,
344 pfc_burdened_cost,
345 pfc_revenue)
346 (SELECT ra.budget_version_id,
347 ra.task_id,
348 ra.resource_list_member_id,
349 ra.resource_assignment_id,
350 decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
351 'Y', bl.txn_currency_code,
352 'N',p_target_fp_cols_rec.x_project_currency_code),
353 sum(bl.quantity),
354 sum(decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
355 'Y',bl.txn_raw_cost,
356 'N',bl.project_raw_cost)),
357 sum(decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
358 'Y',bl.txn_burdened_cost,
359 'N',bl.project_burdened_cost)),
360 sum(decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
361 'Y',bl.txn_revenue,
362 'N',bl.project_revenue)),
363 sum(bl.project_raw_cost),
364 sum(bl.project_burdened_cost),
365 sum(bl.project_revenue),
366 sum(bl.raw_cost),
367 sum(bl.burdened_cost),
368 sum(bl.revenue)
369 FROM pa_resource_assignments ra,
370 pa_budget_lines bl
371 WHERE ra.resource_assignment_id = bl.resource_assignment_id
372 AND ra.budget_version_id = p_source_bv_id
373 and bl.cost_rejection_code is null
374 and bl.revenue_rejection_code is null
375 and bl.burden_rejection_code is null
376 and bl.other_rejection_code is null
377 and bl.pc_cur_conv_rejection_code is null
378 and bl.pfc_cur_conv_rejection_code is null
379 GROUP BY
380 ra.budget_version_id,
381 ra.task_id,
382 ra.resource_list_member_id,
383 ra.resource_assignment_id,
384 decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
385 'Y',bl.txn_currency_code,
386 'N',p_target_fp_cols_rec.x_project_currency_code));
387
388 ELSIF l_rl_uncategorized_flag = 'N' THEN
389 INSERT INTO PA_FP_CALC_AMT_TMP3
390 (plan_version_id,
391 task_id,
392 res_list_member_id,
393 res_asg_id,
394 txn_currency_code,
395 quantity,
396 txn_raw_cost,
397 txn_burdened_cost,
398 txn_revenue,
399 pc_raw_cost,
400 pc_burdened_cost,
401 pc_revenue,
402 pfc_raw_cost,
403 pfc_burdened_cost,
404 pfc_revenue)
405 (SELECT ra.budget_version_id,
406 ra.task_id,
407 l_uc_res_list_rlm_id,
408 ra.resource_assignment_id,
409 decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
410 'Y', bl.txn_currency_code,
411 'N',p_target_fp_cols_rec.x_project_currency_code),
412 sum(bl.quantity),
413 sum(decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
414 'Y',bl.txn_raw_cost,
415 'N',bl.project_raw_cost)),
416 sum(decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
417 'Y',bl.txn_burdened_cost,
418 'N',bl.project_burdened_cost)),
419 sum(decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
420 'Y',bl.txn_revenue,
421 'N',bl.project_revenue)),
422 sum(bl.project_raw_cost),
423 sum(bl.project_burdened_cost),
424 sum(bl.project_revenue),
425 sum(bl.raw_cost),
426 sum(bl.burdened_cost),
427 sum(bl.revenue)
428 FROM pa_resource_assignments ra,
429 pa_budget_lines bl
430 WHERE ra.resource_assignment_id = bl.resource_assignment_id
431 AND ra.budget_version_id = p_source_bv_id
432 and bl.cost_rejection_code is null
433 and bl.revenue_rejection_code is null
434 and bl.burden_rejection_code is null
435 and bl.other_rejection_code is null
436 and bl.pc_cur_conv_rejection_code is null
437 and bl.pfc_cur_conv_rejection_code is null
438 GROUP BY
439 ra.budget_version_id,
440 ra.task_id,
441 l_uc_res_list_rlm_id,
442 ra.resource_assignment_id,
443 decode(p_target_fp_cols_rec.x_plan_in_multi_curr_flag,
444 'Y',bl.txn_currency_code,
445 'N',p_target_fp_cols_rec.x_project_currency_code));
446 END IF;
447
448 IF p_pa_debug_mode = 'Y' AND p_init_msg_flag = 'Y' THEN
449 PA_DEBUG.reset_err_stack;
450 ELSIF p_pa_debug_mode = 'Y' AND p_init_msg_flag = 'N' THEN
451 PA_DEBUG.Reset_Curr_Function;
452 END IF;
453
454 EXCEPTION
455 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
456 l_msg_count := FND_MSG_PUB.count_msg;
457 IF l_msg_count = 1 THEN
458 PA_INTERFACE_UTILS_PUB.get_messages
459 ( p_encoded => FND_API.G_TRUE,
460 p_msg_index => 1,
461 p_msg_count => l_msg_count,
462 p_msg_data => l_msg_data,
463 p_data => l_data,
464 p_msg_index_out => l_msg_index_out);
465 x_msg_data := l_data;
466 x_msg_count := l_msg_count;
467 ELSE
468 x_msg_count := l_msg_count;
469 END IF;
470 ROLLBACK;
471
472 x_return_status := FND_API.G_RET_STS_ERROR;
473 IF P_PA_DEBUG_MODE = 'Y' THEN
474 pa_fp_gen_amount_utils.fp_debug
475 (p_msg => 'Invalid Arguments Passed',
476 p_module_name => l_module_name,
477 p_log_level => 5);
478 IF p_init_msg_flag = 'Y' THEN
479 PA_DEBUG.reset_err_stack;
480 ELSIF p_init_msg_flag = 'N' THEN
481 PA_DEBUG.Reset_Curr_Function;
482 END IF;
483 END IF;
484 RAISE;
485
486 WHEN OTHERS THEN
487 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
488 x_msg_data := SUBSTR(SQLERRM,1,240);
489 FND_MSG_PUB.add_exc_msg
490 ( p_pkg_name => 'PA_FP_MAP_BV_PUB'
491 ,p_procedure_name => 'GEN_MAP_BV_TO_TARGET_RL');
492 IF P_PA_DEBUG_MODE = 'Y' THEN
493 pa_fp_gen_amount_utils.fp_debug
494 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
495 p_module_name => l_module_name,
496 p_log_level => 5);
497 IF p_init_msg_flag = 'Y' THEN
498 PA_DEBUG.reset_err_stack;
499 ELSIF p_init_msg_flag = 'N' THEN
500 PA_DEBUG.Reset_Curr_Function;
501 END IF;
502 END IF;
503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
504
505 END GEN_MAP_BV_TO_TARGET_RL;
506
507 PROCEDURE MAINTAIN_RBS_DTLS
508 (P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
509 P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
510 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
511 X_MSG_COUNT OUT NOCOPY NUMBER,
512 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
513 l_module_name VARCHAR2(200) := 'pa.plsql.pa_fp_map_bv_pub.maintain_rbs_dtls';
514
515 l_res_list_member_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
516 l_rbs_element_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
517 l_txn_accum_header_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
518 l_txn_src_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
519
520 l_count NUMBER;
521 l_msg_count NUMBER;
522 l_data VARCHAR2(2000);
523 l_msg_data VARCHAR2(2000);
524 l_msg_index_out NUMBER;
525
526 BEGIN
527 --Calling the map_rlmi_rbs api
528 IF p_pa_debug_mode = 'Y' THEN
529 pa_debug.set_curr_function( p_function => 'MAINTAIN_RBS_DTLS'
530 ,p_debug_mode => p_pa_debug_mode);
531 pa_fp_gen_amount_utils.fp_debug
532 (p_msg => 'Before calling
533 pa_rlmi_rbs_map_pub.map_rlmi_rbs',
534 p_module_name => l_module_name,
535 p_log_level => 5);
536 END IF;
537 PA_RLMI_RBS_MAP_PUB.MAP_RLMI_RBS
538 ( p_budget_version_id => p_budget_version_id,
539 p_resource_list_id => null,
540 p_rbs_version_id => p_fp_cols_rec.x_rbs_version_id,
541 p_calling_process => 'BUDGET_GENERATION',
542 p_calling_context => 'PLSQL',
543 p_process_code => 'RBS_MAP',
544 p_calling_mode => 'BUDGET_VERSION',
545 p_init_msg_list_flag => 'N',
546 p_commit_flag => 'N',
547 x_txn_source_id_tab => l_txn_src_id_tab,
548 x_res_list_member_id_tab => l_res_list_member_id_tab,
549 x_rbs_element_id_tab => l_rbs_element_id_tab,
550 x_txn_accum_header_id_tab => l_txn_accum_header_id_tab,
551 x_return_status => x_return_status,
552 x_msg_count => x_msg_count,
553 x_msg_data => x_msg_data);
554 IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
555 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
556 END IF;
557 IF p_pa_debug_mode = 'Y' THEN
558 pa_fp_gen_amount_utils.fp_debug
559 (p_msg => 'Status after calling
560 pa_rlmi_rbs_map_pub.map_rlmi_rbs'
561 ||x_return_status,
562 p_module_name => l_module_name,
563 p_log_level => 5);
564 END IF;
565 /* dbms_output.put_line('Status of map_rlmi_rbs api: '
566 ||X_RETURN_STATUS); */
567
568 FORALL i IN 1..l_txn_src_id_tab.count
569 UPDATE pa_resource_assignments
570 SET rbs_element_id = l_rbs_element_id_tab(i),
571 txn_accum_header_id = l_txn_accum_header_id_tab(i)
572 WHERE resource_assignment_id = l_txn_src_id_tab(i);
573
574 IF p_pa_debug_mode = 'Y' THEN
575 PA_DEBUG.Reset_Curr_Function;
576 END IF;
577
578 EXCEPTION
579 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
580 l_msg_count := FND_MSG_PUB.count_msg;
581 IF l_msg_count = 1 THEN
582 PA_INTERFACE_UTILS_PUB.get_messages
583 ( p_encoded => FND_API.G_TRUE,
584 p_msg_index => 1,
585 p_msg_count => l_msg_count,
586 p_msg_data => l_msg_data,
587 p_data => l_data,
588 p_msg_index_out => l_msg_index_out);
589 x_msg_data := l_data;
590 x_msg_count := l_msg_count;
591 ELSE
592 x_msg_count := l_msg_count;
593 END IF;
594 ROLLBACK;
595
596 x_return_status := FND_API.G_RET_STS_ERROR;
597 IF P_PA_DEBUG_MODE = 'Y' THEN
598 pa_fp_gen_amount_utils.fp_debug
599 (p_msg => 'Invalid Arguments Passed',
600 p_module_name => l_module_name,
601 p_log_level => 5);
602 PA_DEBUG.Reset_Curr_Function;
603 END IF;
604 RAISE;
605
606 WHEN OTHERS THEN
607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
608 x_msg_data := SUBSTR(SQLERRM,1,240);
609 FND_MSG_PUB.add_exc_msg
610 ( p_pkg_name => 'PA_FP_MAP_BV_PUB'
611 ,p_procedure_name => 'MAINTAIN_RBS_DTLS');
612 IF P_PA_DEBUG_MODE = 'Y' THEN
613 pa_fp_gen_amount_utils.fp_debug
614 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
615 p_module_name => l_module_name,
616 p_log_level => 5);
617 PA_DEBUG.Reset_Curr_Function;
618 END IF;
619 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
620 END MAINTAIN_RBS_DTLS;
621
622 END PA_FP_MAP_BV_PUB;