[Home] [Help]
PACKAGE BODY: APPS.PA_FP_WEBADI_UTILS
Source
1 PACKAGE BODY pa_fp_webadi_utils as
2 /* $Header: PAFPWAUB.pls 120.14.12010000.2 2008/08/26 23:31:31 jngeorge ship $*/
3 /***************************************************************/
4 /*This procedure is called to return the layout code based on */
5 /* p_budget_version_id. */
6 /***************************************************************/
7
8 G_BUDGET_VERSION_ID pa_budget_versions.budget_version_id%TYPE := -99 ;
9 G_TABLE_POPULATED VARCHAR2(1) := 'N' ;
10 G_TXN_CURRENCY_CODE_TBL PA_FP_WEBADI_PKG.l_txn_currency_code_tbl_typ ;
11
12
13 g_module_name VARCHAR2(30) := 'pa.plsql.PA_FP_WEBADI_UTILS';
14
15 l_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
16
17 PROCEDURE get_metadata_info(
18 p_budget_version_id IN NUMBER,
19 x_content_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
20 x_mapping_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
21 x_layout_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
22 x_integrator_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
23 x_rej_lines_exist OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
24 x_submit_budget OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
25 x_submit_forecast OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
26 x_err_msg_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
27 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
28 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
29 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
30 IS
31 l_plan_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
32 l_time_phased_code pa_proj_fp_options.cost_time_phased_code%TYPE;
33 l_fin_plan_level_code pa_proj_fp_options.cost_fin_plan_level_code%TYPE;
34 l_uncategorized_flag pa_resource_lists.UNCATEGORIZED_FLAG%TYPE;
35 l_group_resource_type_id pa_resource_lists.GROUP_RESOURCE_TYPE_ID%TYPE;
36 l_fin_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
37 l_project_id pa_proj_fp_options.project_id%TYPE;
38 l_cost_layout_code pa_proj_fp_options.cost_layout_code%TYPE;
39 l_revenue_layout_code pa_proj_fp_options.revenue_layout_code%TYPE;
40 l_all_layout_code pa_proj_fp_options.all_layout_code%TYPE;
41 l_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
42 l_cost_time_phased_code pa_proj_fp_options.cost_time_phased_Code%TYPE;
43 l_revenue_time_phased_Code pa_proj_fp_options.revenue_time_phased_Code%TYPE;
44 l_all_time_phased_Code pa_proj_fp_options.all_time_phased_Code%TYPE;
45 l_integrator_code bne_layouts_b.integrator_code%TYPE;
46 l_cost_period_mask_id pa_proj_fp_options.cost_period_mask_id%TYPE;
47 l_revenue_period_mask_id pa_proj_fp_options.rev_period_mask_id%TYPE;
48 l_all_period_mask_id pa_proj_fp_options.all_period_mask_id%TYPE;
49 l_no_of_periods NUMBER;
50 l_current_working_flag pa_budget_versions.current_working_flag%TYPE;
51 l_layout_meaning bne_layouts_vl.user_name%TYPE;
52 l_plan_class_name pa_lookups.meaning%TYPE;
53 l_layout_code VARCHAR2(300);
54
55 /* Added variables for debug messages/error. */
56 l_msg_count NUMBER := 0;
57 l_data VARCHAR2(2000);
58 l_msg_data VARCHAR2(2000);
59 l_msg_index_out NUMBER;
60 l_debug_mode VARCHAR2(30);
61 l_module_name VARCHAR2(200) := g_module_name || '.get_metadata_info';
62 l_debug_level3 CONSTANT NUMBER :=3;
63 l_debug_level5 CONSTANT NUMBER :=5;
64
65
66
67 BEGIN
68
69 -- 4497318.Perf Fix: The View name pa_resource_lists is reolaced by Table name pa_resource_lists_all_bg in the FROM clause.
70 SELECT a.GROUP_RESOURCE_TYPE_ID,a.UNCATEGORIZED_FLAG,b.current_working_flag
71 INTO l_group_resource_type_id,l_uncategorized_flag,l_current_working_flag
72 FROM pa_resource_lists_all_bg a, pa_budget_versions b
73 WHERE b.budget_version_id = p_budget_version_id
74 AND a.RESOURCE_LIST_ID = b.resource_list_id;
75
76 SELECT FIN_PLAN_PREFERENCE_CODE
77 ,fin_plan_type_id
78 ,project_id
79 ,cost_time_phased_code
80 ,revenue_time_phased_code
81 ,all_time_phased_code
82 ,cost_period_mask_id
83 ,rev_period_mask_id
84 ,all_period_mask_id
85 INTO l_plan_pref_code
86 ,l_fin_plan_type_id
87 ,l_project_id
88 ,l_cost_time_phased_code
89 ,l_revenue_time_phased_code
90 ,l_all_time_phased_code
91 ,l_cost_period_mask_id
92 ,l_revenue_period_mask_id
93 ,l_all_period_mask_id
94 FROM pa_proj_fp_options
95 WHERE fin_plan_version_id = p_budget_version_id
96 AND FIN_PLAN_OPTION_LEVEL_CODE = 'PLAN_VERSION';
97
98 SELECT pfo.cost_layout_code
99 ,pfo.revenue_layout_code
100 ,pfo.all_layout_code
101 ,ptb.plan_class_code
102 INTO l_cost_layout_code
103 ,l_revenue_layout_code
104 ,l_all_layout_code
105 ,l_plan_class_code
106 FROM pa_proj_fp_options pfo
107 ,pa_fin_plan_types_b ptb
108 WHERE pfo.fin_plan_type_id = l_fin_plan_type_id
109 AND pfo.FIN_PLAN_version_id IS NULL
110 AND pfo.fin_plan_type_id = ptb.fin_plan_type_id
111 AND pfo.project_id = l_project_id;
112
113
114
115 l_fin_plan_level_code := pa_fin_plan_utils.Get_Fin_Plan_Level_Code(
116 p_fin_plan_version_id => p_budget_version_id);
117 l_time_phased_code := pa_fin_plan_utils.get_time_phased_code(
118 p_fin_plan_version_id => p_budget_version_id);
119
120 x_rej_lines_exist := 'N';
121
122 x_submit_budget := 'false';
123 x_submit_forecast := 'false';
124
125 IF l_current_working_flag ='Y' AND l_plan_class_code='BUDGET' THEN
126 x_submit_budget := 'true';
127 END IF;
128 IF l_current_working_flag ='Y' AND l_plan_class_code='FORECAST' THEN
129 x_submit_forecast := 'true';
130 END IF;
131
132
133 IF l_plan_pref_code = 'COST_ONLY' THEN
134 x_layout_code := l_cost_layout_code;
135 ELSIF l_plan_pref_code = 'REVENUE_ONLY' THEN
136 x_layout_code := l_revenue_layout_code;
137 ELSIF l_plan_pref_code = 'COST_AND_REV_SAME' THEN
138 x_layout_code := l_all_layout_code;
139 END IF;
140
141
142 /* Calling the Client Extension to get the Custom Layout, if being passed
143 by the user. */
144
145 l_layout_code := x_layout_code;
146
147 pa_client_extn_budget.Get_Custom_Layout_Code(
148 p_budget_version_id => p_budget_version_id
149 , p_layout_code_in => l_layout_code
150 , x_layout_code_out => x_layout_code
151 , x_return_status => x_return_status
152 , x_msg_count => x_msg_count
153 , x_msg_data => x_msg_data);
154
155 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
156 l_msg_count := FND_MSG_PUB.count_msg;
157 IF l_msg_count = 1 THEN
158 PA_INTERFACE_UTILS_PUB.get_messages
159 (p_encoded => FND_API.G_TRUE,
160 p_msg_index => 1,
161 p_msg_count => l_msg_count,
162 p_msg_data => l_msg_data,
163 p_data => l_data,
164 p_msg_index_out => l_msg_index_out);
165 x_msg_data := l_data;
166 x_msg_count := l_msg_count;
167 ELSE
168 x_msg_count := l_msg_count;
169 END IF;
170 x_return_status := FND_API.G_RET_STS_ERROR;
171 END IF;
172
173
174 IF (l_plan_pref_code = 'COST_ONLY' AND x_layout_code IS NULL ) OR
175 (l_plan_pref_code = 'REVENUE_ONLY' AND x_layout_code IS NULL ) OR
176 (l_plan_pref_code = 'COST_AND_REV_SAME' AND x_layout_code IS NULL) THEN
177
178 x_return_status := FND_API.G_RET_STS_SUCCESS;
179 x_err_msg_code := 'PA_FP_WEBADI_OLD_LAYOUT';
180 RETURN;
181 END IF;
182
183
184 --Checking if the custom layout code maps to one of the layout codes present
185 -- in the system.
186
187 BEGIN
188 SELECT integrator_code
189 INTO l_integrator_code
190 FROM bne_layouts_b
191 WHERE layout_code = x_layout_code
192 and application_id = (SELECT application_id
193 FROM FND_APPLICATION
194 WHERE APPLICATION_SHORT_NAME = 'PA');
195
196
197
198 EXCEPTION
199 WHEN NO_DATA_FOUND THEN
200 IF l_debug_mode = 'Y' THEN
201 pa_debug.g_err_stage:='The custom layout code is not present the system.';
202 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
203 END IF;
204 x_return_status := FND_API.G_RET_STS_SUCCESS;
205 x_err_msg_code := 'PA_FP_WEBADI_OLD_LAYOUT';
206 RETURN;
207 END;
208
209
210
211 IF ((l_plan_class_code = 'BUDGET'
212 AND l_integrator_code NOT IN ('FINPLAN_BUDGET_PERIODIC' , 'FINPLAN_BUDGET_NON_PERIODIC' ))
213 OR (l_plan_class_code = 'FORECAST'
214 AND l_integrator_code NOT IN ('FINPLAN_FORECAST_PERIODIC' , 'FINPLAN_FORECAST_NON_PERIODIC' ))) THEN
215
216 SELECT user_name
217 INTO l_layout_meaning
218 FROM bne_layouts_tl
219 WHERE layout_code = x_layout_code
220 AND language = userenv('lang')
221 AND application_id = (SELECT application_id
222 FROM FND_APPLICATION
223 WHERE APPLICATION_SHORT_NAME = 'PA');
224
225 SELECT meaning
226 INTO l_plan_class_name
227 FROM pa_lookups
228 WHERE lookup_type = 'FIN_PLAN_CLASS'
229 AND Lookup_code = l_plan_class_code;
230
231 IF l_plan_class_code = 'BUDGET' THEN
232
233 x_return_status := FND_API.G_RET_STS_SUCCESS;
234 x_err_msg_code := 'PA_FP_WEBADI_INCRT_BDGT_LAYOUT';
235 RETURN;
236
237 ELSIF l_plan_class_code = 'FORECAST' THEN
238
239 x_return_status := FND_API.G_RET_STS_SUCCESS;
240 x_err_msg_code := 'PA_FP_WEBADI_INCRT_FCST_LAYOUT';
241 RETURN;
242
243 END IF;
244 END IF;
245
246
247
248 IF l_plan_class_code = 'BUDGET' THEN
249
250 IF l_integrator_code = 'FINPLAN_BUDGET_PERIODIC' THEN
251 x_integrator_code := 'FINPLAN_BUDGET_PERIODIC';
252 x_content_code := 'PA_FP_P_BUDGET_CNT';
253 x_mapping_code := 'PA_FP_PBUD_MAP';
254 ELSIF l_integrator_code = 'FINPLAN_BUDGET_NON_PERIODIC' THEN
255 x_integrator_code := 'FINPLAN_BUDGET_NON_PERIODIC';
256 x_content_code := 'PA_FP_NP_BUDGET_CNT';
257 x_mapping_code := 'PA_FP_NPBUD_MAP';
258 END IF;
259 ELSIF l_plan_class_code = 'FORECAST' THEN
260
261 IF l_integrator_code = 'FINPLAN_FORECAST_PERIODIC' THEN
262 x_integrator_code := 'FINPLAN_FORECAST_PERIODIC';
263 x_content_code := 'PA_FP_P_FORECAST_CNT';
264 x_mapping_code := 'PA_FP_PFC_MAP';
265
266 ELSIF l_integrator_code = 'FINPLAN_FORECAST_NON_PERIODIC' THEN
267 x_integrator_code := 'FINPLAN_FORECAST_NON_PERIODIC';
268 x_content_code := 'PA_FP_NP_FORECAST_CNT';
269 x_mapping_code := 'PA_FP_NPFC_MAP';
270 END IF;
271 END IF;
272
273
274 IF (l_plan_pref_code = 'COST_ONLY' AND l_cost_time_phased_code = 'N'
275 AND l_integrator_code IN ('FINPLAN_BUDGET_PERIODIC','FINPLAN_FORECAST_PERIODIC')) OR
276 (l_plan_pref_code = 'REVENUE_ONLY' AND l_revenue_time_phased_code = 'N'
277 AND l_integrator_code IN ('FINPLAN_BUDGET_PERIODIC','FINPLAN_FORECAST_PERIODIC')) OR
278 (l_plan_pref_code = 'COST_AND_REV_SAME' AND l_all_time_phased_code= 'N'
279 AND l_integrator_code IN ('FINPLAN_BUDGET_PERIODIC','FINPLAN_FORECAST_PERIODIC')) THEN
280
281 x_return_status := FND_API.G_RET_STS_SUCCESS;
282 x_err_msg_code := 'PA_FP_WEBADI_PER_LAYOUT';
283 RETURN;
284 END IF;
285
286 IF l_plan_pref_code = 'COST_ONLY' THEN
287
288 SELECT COUNT(*)
289 INTO l_no_of_periods
290 FROM pa_period_mask_details
291 WHERE period_mask_id = l_cost_period_mask_id
292 AND from_anchor_position not in (99999,-99999);
293 ELSIF l_plan_pref_code = 'REVENUE_ONLY' THEN
294
295 SELECT COUNT(*)
296 INTO l_no_of_periods
297 FROM pa_period_mask_details
298 WHERE period_mask_id = l_revenue_period_mask_id
299 AND from_anchor_position not in (99999,-99999);
300 ELSIF l_plan_pref_code = 'COST_AND_REV_SAME' THEN
301
302 SELECT COUNT(*)
303 INTO l_no_of_periods
304 FROM pa_period_mask_details
305 WHERE period_mask_id = l_all_period_mask_id
306 AND from_anchor_position not in (99999,-99999);
307 END IF;
308
309 IF l_no_of_periods > 52 THEN
310
311 IF l_plan_class_code = 'BUDGET' THEN
312
313 x_layout_code := 'NPE_BUDGET';
314 x_integrator_code := 'FINPLAN_BUDGET_NON_PERIODIC';
315 x_content_code := 'PA_FP_NP_BUDGET_CNT';
316 x_mapping_code := 'PA_FP_NPBUD_MAP';
317
318 ELSIF l_plan_class_code = 'FORECAST' THEN
319 x_layout_code := 'NPE_FORECAST'; --Need to check the layout that has to be coded for forecast.
320 x_integrator_code := 'FINPLAN_FORECAST_NON_PERIODIC';
321 x_content_code := 'PA_FP_NP_FORECAST_CNT';
322 x_mapping_code := 'PA_FP_NPFC_MAP';
323 END IF;
324 END IF;
325
326 x_rej_lines_exist := pa_fin_plan_utils.does_bv_have_rej_lines(p_budget_version_id);
327
328 EXCEPTION
329
330 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
331 l_msg_count := FND_MSG_PUB.count_msg;
332 IF l_msg_count = 1 THEN
333 PA_INTERFACE_UTILS_PUB.get_messages
334 (p_encoded => FND_API.G_TRUE
335 ,p_msg_index => 1
336 ,p_msg_count => l_msg_count
337 ,p_msg_data => l_msg_data
338 ,p_data => l_data
339 ,p_msg_index_out => l_msg_index_out);
340
341 x_msg_data := l_data;
342 x_msg_count := l_msg_count;
343 ELSE
344 x_msg_count := l_msg_count;
345 END IF;
346 x_return_status := FND_API.G_RET_STS_ERROR;
347
348 IF l_debug_mode = 'Y' THEN
349 pa_debug.g_err_stage:='Invalid Arguments Passed Or called api raised an error';
350 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
351
352 END IF;
353 -- reset curr function
354 pa_debug.reset_curr_function();
355 RETURN;
356 WHEN OTHERS THEN
357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
358 x_msg_count := 1;
359 x_msg_data := SQLERRM;
360
361 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_fp_webadi_utils'
362 ,p_procedure_name => 'get_metadata_info');
363
364 IF l_debug_mode = 'Y' THEN
365 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
366 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
367 END IF;
368 -- reset curr function
369 pa_debug.Reset_Curr_Function();
370 RAISE;
371
372 END get_metadata_info;
373
374
375 PROCEDURE validate_before_launch(p_budget_version_id IN NUMBER,
376 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
377 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
378 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
379 )
380 IS
381
382 l_budget_version_id NUMBER;
383 l_res_asg_id NUMBER;
384 l_project_id NUMBER;
385 l_task_id NUMBER;
386 l_period_profile_id NUMBER;
387 l_time_phased_code pa_proj_fp_options.cost_time_phased_code%TYPE;
388 l_fin_plan_level_code pa_proj_fp_options.cost_fin_plan_level_code%TYPE;
389 l_project_start_date DATE;
390 l_project_end_date DATE;
391 k NUMBER;
392 m NUMBER;
393 l_msg_count NUMBER := 0;
394 l_data VARCHAR2(2000);
395 l_msg_data VARCHAR2(2000);
396 l_msg_index_out NUMBER;
397 l_return_status VARCHAR2(2000);
398
399 v_task_start_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
400 v_task_end_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
401
402 -- 4497315.Perf Fix.SELECT query is changed.
403 CURSOR C1(l_budget_version_id number) IS
404 SELECT pt.start_date,pt.completion_date
405 FROM pa_resource_assignments pra,
406 pa_budget_versions pbv,
407 pa_tasks pt
408 WHERE pra.budget_version_id = pbv.budget_version_id
409 AND pbv.budget_version_id = l_budget_version_id
410 AND pt.task_id = pra.task_id
411 AND pra.project_id = pbv.project_id
412 AND pbv.project_id = pt.task_id;
413
414 BEGIN
415
416 l_budget_version_id := p_budget_version_id;
417 l_fin_plan_level_code := pa_fin_plan_utils.Get_Fin_Plan_Level_Code(
418 p_fin_plan_version_id => l_budget_version_id);
419 l_time_phased_code := pa_fin_plan_utils.get_time_phased_code(
420 p_fin_plan_version_id => l_budget_version_id);
421 SELECT period_profile_id
422 ,project_id
423 INTO l_period_profile_id
424 ,l_project_id
425 FROM pa_budget_versions
426 WHERE budget_version_id = l_budget_version_id;
427
428 SELECT start_date
429 ,completion_date
430 INTO l_project_start_date
431 ,l_project_end_date
432 FROM pa_projects_all p
433 WHERE p.project_id = l_project_id;
434
435
436 IF l_time_phased_code IN(PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_P,PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_G) THEN
437
438 IF l_period_profile_id IS NULL THEN
439 -- pa_debug.g_err_stage := 'period_profile_id is null when time phasing is PA or GL ';
440 -- pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
441 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
442 p_msg_name => 'PA_FP_PERIODPROFILE_UNDEFINED');
443 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
444 END IF;
445
446 ELSE /* if time phasing is none then */
447
448 IF l_time_phased_code = PA_FP_CONSTANTS_PKG.G_TIME_PHASED_CODE_N THEN
449 IF l_fin_plan_level_code = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT THEN
450 IF l_project_start_date IS NULL or l_project_end_date IS NULL THEN
451 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
452 p_msg_name => 'PA_BU_NO_TASK_PROJ_DATE');
453 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
454 END IF;
455 ELSE
456 OPEN C1(l_budget_version_id);
457 k :=0;
458 Loop
459 fetch C1 into v_task_start_date_tab(k),v_task_end_date_tab(k);
460 k := k+1;
461 EXIT WHEN C1%NOTFOUND;
462
463 END LOOP; --End k Loop
464
465 CLOSE C1;
466
467 FOR m in v_task_start_date_tab.first..v_task_start_date_tab.last LOOP
468
469 IF v_task_start_date_tab(m) IS NULL or v_task_end_date_tab(m) IS NULL THEN
470 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
471 p_msg_name => 'PA_BU_NO_TASK_PROJ_DATE');
472 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
473 END IF;
474
475 END LOOP; /* for loop*/
476
477 END IF;
478 END IF;
479 END IF;
480
481 EXCEPTION
482 WHEN FND_API.G_EXC_ERROR or PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
483 x_return_status := FND_API.G_RET_STS_ERROR;
484 l_msg_count := FND_MSG_PUB.count_msg;
485 IF l_msg_count = 1 THEN
486 PA_INTERFACE_UTILS_PUB.get_messages
487 (p_encoded => FND_API.G_TRUE,
488 p_msg_index => 1,
489 p_msg_count => l_msg_count,
490 p_msg_data => l_msg_data,
491 p_data => l_data,
492 p_msg_index_out => l_msg_index_out);
493
494 x_msg_data := l_data;
495 x_msg_count := l_msg_count;
496 ELSE
497 x_msg_count := l_msg_count;
498 END IF;
499
500
501
502 END;
503
504 procedure convert_task_num_to_id
505 (p_project_id IN NUMBER,
506 p_task_num IN VARCHAR2,
507 x_task_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
508 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
509 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
510 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
511 IS
512
513 BEGIN
514
515 x_return_status := FND_API.G_RET_STS_SUCCESS;
516
517 IF p_task_num is not NULL then
518
519 BEGIN
520
521 select task_id
522 into x_task_id
523 from pa_tasks
524 where project_id = p_project_id
525 and task_number = p_task_num;
526
527 EXCEPTION
528 When NO_DATA_FOUND THEN
529
530 x_return_status := FND_API.G_RET_STS_ERROR;
531
532 END;
533
534 ELSE x_task_id := 0;
535
536 END IF;
537
538 END;
539
540 procedure validate_currency_code
541 (p_budget_version_id IN NUMBER,
542 p_currency_code IN VARCHAR2,
543 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
544 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
545 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
546 IS
547 l_multi_curr_flag VARCHAR2(1);
548 l_txn_currency_id NUMBER;
549 l_proj_fp_options_id NUMBER;
550
551 BEGIN
552
553 select fp.PLAN_IN_MULTI_CURR_FLAG,
554 fp.proj_fp_options_id
555 into l_multi_curr_flag,
556 l_proj_fp_options_id
557 from pa_proj_fp_options fp, pa_budget_versions bv
558 where bv.budget_version_id = p_budget_version_id
559 and fp.fin_plan_version_id = p_budget_version_id
560 and fp.fin_plan_type_id = bv.fin_plan_type_id
561 and fp.fin_plan_option_level_code = 'PLAN_VERSION'
562 and fp.project_id = bv.project_id;
563
564 if l_multi_curr_flag = 'Y' then
565 begin
566 select fp_txn_currency_id
567 into l_txn_currency_id
568 from pa_fp_txn_currencies
569 where proj_fp_options_id = l_proj_fp_options_id --Sql Performance to avoid FTS fix sql id 16509328
570 and txn_currency_code = p_currency_code;
571 exception
572 When NO_DATA_FOUND THEN
573 x_return_status := FND_API.G_RET_STS_ERROR;
574
575 end;
576
577 end if;
578
579 END;
580
581 procedure validate_resource_info
582 (p_budget_version_id IN NUMBER,
583 p_resource_group_name IN VARCHAR2,
584 p_resource_alias IN VARCHAR2,
585 x_resource_list_member_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
586 x_resource_gp_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
587 x_resource_alias_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
588 x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
589
590
591 IS
592
593 l_resource_list_id NUMBER;
594 l_budget_line_id NUMBER;
595 ll_budget_line_id NUMBER;
596 l_budget_version_id NUMBER;
597 l_rlm_id_gp NUMBER;
598 l_rlm_id_alias NUMBER;
599 l_project_id NUMBER;
600 l_fin_plan_level_code pa_proj_fp_options.cost_fin_plan_level_code%TYPE;
601 l_plan_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
602 l_uncategorized_flag pa_resource_lists.UNCATEGORIZED_FLAG%TYPE;
603 l_group_resource_type_id pa_resource_lists.GROUP_RESOURCE_TYPE_ID%TYPE;
604 l_row_id ROWID;
605 ll_error_flag VARCHAR2(1);
606 l_track_as_labor_flag PA_RESOURCE_LIST_MEMBERS.TRACK_AS_LABOR_FLAG%TYPE;
607 l_dummy_res_list_id NUMBER;
608 l_unit_of_measure PA_RESOURCE_ASSIGNMENTS.UNIT_OF_MEASURE%TYPE;
609 l_dummy_id NUMBER;
610 l_return_status VARCHAR2(1);
611 l_msg_count NUMBER;
612 l_msg_data VARCHAR2(240);
613
614 BEGIN
615
616
617
618 Select resource_list_id, project_id
619 into l_resource_list_id,l_project_id
620 from pa_budget_versions
621 where budget_version_id = p_budget_version_id;
622
623 -- 4497318.Perf Fix.The View name pa_resource_lists is replaced by Table name pa_resource_lists_all_bg in the FROM clause.
624 SELECT GROUP_RESOURCE_TYPE_ID,UNCATEGORIZED_FLAG
625 INTO l_group_resource_type_id,l_uncategorized_flag
626 FROM pa_resource_lists_all_bg
627 WHERE RESOURCE_LIST_ID = l_resource_list_id;
628
629 If l_uncategorized_flag <> 'Y' Then
630 -- check if plan by resource or by non-resource
631 -- dbms_output.put_line('Planning by resource....');
632
633 If l_group_resource_type_id > 0 Then
634
635 --check if plan by resource group or by resource-only
636
637 --Resource Group
638 --dbms_output.put_line('Planning by resource group...');
639 Begin
640
641 Select resource_list_member_id
642 into l_rlm_id_gp
643 from pa_resource_list_members
644 where resource_list_id = l_resource_list_id
645 and parent_member_id is NULL
646 and alias = p_resource_group_name;
647
648 Exception
649 When NO_DATA_FOUND THEN
650
651 x_resource_gp_flag := 'Y';
652 x_return_status := FND_API.G_RET_STS_ERROR;
653
654 return;
655
656 End;
657
658 -- 4497318.Perf Fix.An AND condition is added in the WHERE clause.
659 SELECT count(*)
660 INTO l_dummy_id
661 FROM pa_resource_list_members
662 WHERE resource_list_id = l_resource_list_id
663 AND parent_member_id = l_rlm_id_gp
664 AND rownum=1;
665
666 if l_dummy_id = 0 then
667 --Plan by Resource Group only
668 x_resource_list_member_id := l_rlm_id_gp;
669
670 else
671 --Plan by Resource Group and Resource alias
672
673 Begin
674
675 Select resource_list_member_id
676 into l_rlm_id_alias
677 from pa_resource_list_members
678 where resource_list_id = l_resource_list_id
679 and parent_member_id = l_rlm_id_gp
680 and alias = p_resource_alias;
681
682 Exception
683 When NO_DATA_FOUND THEN
684 x_resource_alias_flag := 'Y';
685 x_return_status := FND_API.G_RET_STS_ERROR;
686 return;
687 End;
688
689 x_resource_list_member_id := l_rlm_id_alias;
690
691 end if; --end if dummy_id = 0
692
693 Else
694 --dbms_output.put_line('Planning by resource only.....');
695 --Resource alias only
696 Begin
697
698 Select resource_list_member_id
699 into l_rlm_id_alias
700 from pa_resource_list_members
701 where resource_list_id = l_resource_list_id
702 and alias = p_resource_alias;
703
704 Exception
705 When NO_DATA_FOUND THEN
706 x_resource_alias_flag := 'Y';
707 x_return_status := FND_API.G_RET_STS_ERROR;
708 return;
709 End;
710
711 END IF; --End if l_group_resource_type_id>0
712
713 ELSE -- if l_uncategorized <> 'Y'
714
715 PA_FIN_PLAN_UTILS.Get_Uncat_Resource_List_Info
716 (x_resource_list_id => l_dummy_res_list_id
717 ,x_resource_list_member_id => l_rlm_id_alias
718 ,x_track_as_labor_flag => l_track_as_labor_flag
719 ,x_unit_of_measure => l_unit_of_measure
720 ,x_return_status => l_return_status
721 ,x_msg_count => l_msg_count
722 ,x_msg_data => l_msg_data);
723
724 --l_rlm_id_alias := 1000;
725
726 END IF; -- End if l_uncategorized <> 'Y'
727
728 x_resource_list_member_id := l_rlm_id_alias;
729
730
731 END;
732
733 PROCEDURE GET_RES_ASSIGNMENT_INFO
734 (p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE
735 ,p_planning_level IN pa_proj_fp_options.cost_fin_plan_level_code%TYPE
736 ,x_task_number OUT NOCOPY pa_tasks.task_number%TYPE --File.Sql.39 bug 4440895
737 ,x_task_id OUT NOCOPY pa_tasks.task_id%TYPE --File.Sql.39 bug 4440895
738 ,x_resource_alias OUT NOCOPY pa_resource_list_members.alias%TYPE --File.Sql.39 bug 4440895
739 ,x_resource_group_alias OUT NOCOPY pa_resource_list_members.alias%TYPE --File.Sql.39 bug 4440895
740 ,x_parent_assignment_id OUT NOCOPY pa_resource_assignments.parent_assignment_id%TYPE --File.Sql.39 bug 4440895
741 ,x_resource_list_member_id OUT NOCOPY pa_resource_list_members.resource_list_member_id%TYPE --File.Sql.39 bug 4440895
742 ,x_resource_id OUT NOCOPY pa_resource_list_members.resource_id%TYPE --File.Sql.39 bug 4440895
743 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
744 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
745 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
746 IS
747
748 l_debug_mode VARCHAR2(10);
749 l_msg_count NUMBER ;
750 l_data VARCHAR2(2000);
751 l_msg_data VARCHAR2(2000);
752 l_msg_index_out NUMBER;
753
754 l_unit_of_measure pa_resource_assignments.unit_of_measure%TYPE;
755
756 -- Cursor c_task_level_info selects the task_name,resource alias
757 -- and resource group alias for a resource assignment id .
758 -- This is fired when version planning level is not PROJECT
759
760 CURSOR c_task_level_info(c_resource_assignment_id IN NUMBER) IS
761 SELECT pt.task_number
762 ,pt.task_id
763 ,decode(prlm.parent_member_id,null,decode(prl.group_resource_type_id,0,rtrim(prlm.alias),null)
764 ,rtrim(prlm.alias)) resource_alias -- Added rtrim for #2839138
765 ,decode(prlm.parent_member_id,null,rtrim(prlm.alias),rtrim(prlm_parent.alias)) resource_group_alias
766 -- Added rtrim for #2839138
767 ,pra.unit_of_measure
768 ,pra.parent_assignment_id
769 ,prlm.resource_list_member_id
770 ,prlm.resource_id
771 FROM pa_tasks pt
772 ,pa_resource_assignments pra
773 ,pa_resource_list_members prlm
774 ,pa_resource_list_members prlm_parent
775 ,pa_resource_lists_all_bg prl
776 WHERE pra.resource_assignment_id = c_resource_assignment_id
777 AND pra.project_id = pt.project_id
778 AND pra.task_id = pt.task_id
779 AND prlm.resource_list_member_id = pra.resource_list_member_id
780 AND prlm.parent_member_id = prlm_parent.resource_list_member_id(+)
781 AND prl.resource_list_id = prlm.resource_list_id;
782
783 -- Cursor c_project_level_info selects the resource alias
784 -- and resource group alias for a resource assignment id .
785 -- This is fired when version planning level is PROJECT.
786
787 CURSOR c_project_level_info(c_resource_assignment_id IN NUMBER) IS
788 SELECT decode(prlm.parent_member_id,null,decode(prl.group_resource_type_id,0,rtrim(prlm.alias),null)
789 ,rtrim(prlm.alias)) resource_alias -- Added rtrim for #2839138
790 ,decode(prlm.parent_member_id,null,rtrim(prlm.alias),rtrim(prlm_parent.alias)) resource_group_alias
791 -- Added rtrim for #2839138
792 ,pra.unit_of_measure
793 ,pra.parent_assignment_id
794 ,prlm.resource_list_member_id
795 ,prlm.resource_id
796 FROM pa_resource_assignments pra
797 ,pa_resource_list_members prlm
798 ,pa_resource_list_members prlm_parent
799 ,pa_resource_lists_all_bg prl
800 WHERE pra.resource_assignment_id = c_resource_assignment_id
801 AND prlm.resource_list_member_id = pra.resource_list_member_id
802 AND prl.resource_list_id = prlm.resource_list_id
803 AND prlm.parent_member_id = prlm_parent.resource_list_member_id(+);
804
805 BEGIN
806 x_msg_count := 0;
807 x_return_status := FND_API.G_RET_STS_SUCCESS;
808 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'Y');
809
810 IF l_debug_mode = 'Y' THEN
811 pa_debug.set_curr_function( p_function => 'GET_RES_ASSIGNMENT_INFO'
812 ,p_debug_mode => l_debug_mode );
813 END IF;
814
815 IF l_debug_mode = 'Y' THEN
816 pa_debug.g_err_stage := ':In PA_FP_WEBADI_UTILS.GET_RES_ASSIGNMENT_INFO' ;
817 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
818 END IF;
819
820 IF ((p_resource_assignment_id IS NULL) OR (p_planning_level IS NULL)) THEN
821
822 IF l_debug_mode = 'Y' THEN
823 pa_debug.g_err_stage :='Invalid input parameter';
824 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
825 END IF;
826
827 x_return_status := FND_API.G_RET_STS_ERROR;
828 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
829 ,p_msg_name => 'PA_FP_INV_PARAM_PASSED');
830 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
831 END IF;
832
833
834 IF l_debug_mode = 'Y' THEN
835 pa_debug.g_err_stage := 'PA_FP_WEBADI_UTILS.GET_RES_ASSIGNMENT_INFO';
836 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
837 END IF ;
838
839 IF (p_planning_level = PA_FP_CONSTANTS_PKG.G_BUDGET_ENTRY_LEVEL_PROJECT) THEN
840
841 IF l_debug_mode = 'Y' THEN
842 pa_debug.g_err_stage := 'opening project level cursor';
843 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
844 END IF ;
845
846 OPEN c_project_level_info(p_resource_assignment_id) ;
847 FETCH c_project_level_info INTO
848 x_resource_alias
849 ,x_resource_group_alias
850 ,l_unit_of_measure
851 ,x_parent_assignment_id
852 ,x_resource_list_member_id
853 ,x_resource_id;
854
855 IF c_project_level_info%NOTFOUND THEN
856
857 -- Indicates that the resource assignment id past
858 -- to the API is invalid.
859 x_return_status := FND_API.G_RET_STS_ERROR ;
860
861 END IF ;
862 -- 4346858.Cursor c_project_level_info is closed.
863 CLOSE c_project_level_info;
864
865
866 ELSE
867
868 IF l_debug_mode = 'Y' THEN
869 pa_debug.g_err_stage := 'opening task level cursor';
870 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
871 END IF ;
872
873 OPEN c_task_level_info(p_resource_assignment_id) ;
874 FETCH c_task_level_info INTO
875 x_task_number
876 ,x_task_id
877 ,x_resource_alias
878 ,x_resource_group_alias
879 ,l_unit_of_measure
880 ,x_parent_assignment_id
881 ,x_resource_list_member_id
882 ,x_resource_id;
883
884 IF c_task_level_info%NOTFOUND THEN
885 -- Indicates that the resource assignment id past
886 -- to the API is invalid.
887 x_return_status := FND_API.G_RET_STS_ERROR ;
888 END IF ;
889 -- 4346858.Cursor c_task_level_info is closed.
890 CLOSE c_task_level_info;
891 END IF ;
892
893 pa_debug.reset_curr_function;
894
895 EXCEPTION
896 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
897 x_return_status := FND_API.G_RET_STS_ERROR;
898 l_msg_count := FND_MSG_PUB.count_msg;
899 IF l_msg_count = 1 THEN
900 PA_INTERFACE_UTILS_PUB.get_messages
901 (p_encoded => FND_API.G_TRUE,
902 p_msg_index => 1,
903 p_msg_count => l_msg_count,
904 p_msg_data => l_msg_data,
905 p_data => l_data,
906 p_msg_index_out => l_msg_index_out);
907
908 x_msg_data := l_data;
909 x_msg_count := l_msg_count;
910 ELSE
911 x_msg_count := l_msg_count;
912 END IF;
913
914 IF l_debug_mode = 'Y' THEN
915 pa_debug.write('GET_RES_ASSIGNMENT_INFO: ' || g_module_name,'Invalid arguments passed',5);
916 pa_debug.write('GET_RES_ASSIGNMENT_INFO: ' || g_module_name,pa_debug.G_Err_Stack,5);
917 pa_debug.reset_curr_function;
918 END IF;
919 RETURN;
920 WHEN OTHERS THEN
921
922 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
923 x_msg_count := 1;
924 x_msg_data := SQLERRM;
925
926 FND_MSG_PUB.add_exc_msg
927 ( p_pkg_name => 'PA_FP_WEBADI_UTILS'
928 ,p_procedure_name => 'GET_RES_ASSIGNMENT_INFO'
929 ,p_error_text => sqlerrm);
930
931
932 IF l_debug_mode = 'Y' THEN
933 pa_debug.G_Err_Stack := SQLERRM;
934 pa_debug.write(g_module_name,pa_debug.G_Err_Stack,4);
935 pa_debug.write(g_module_name,pa_debug.G_Err_Stage,4);
936 pa_debug.reset_curr_function;
937
938 END IF;
939 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
940
941 END GET_RES_ASSIGNMENT_INFO ;
942
943 /* Bug 5350437: Commented the below API
944 PROCEDURE VALIDATE_CHANGE_REASON_CODE
945 (p_change_reason_code IN pa_budget_lines.change_reason_code%TYPE
946 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
947 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
948 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
949 IS
950 l_debug_mode VARCHAR2(1);
951 l_msg_count NUMBER ;
952 l_data VARCHAR2(2000);
953 l_msg_data VARCHAR2(2000);
954 l_msg_index_out NUMBER;
955 l_exists VARCHAR2(1) ;
956 l_lookup_code PA_LOOKUPS.LOOKUP_CODE%TYPE;
957
958 CURSOR c_code_exists_cur IS
959 SELECT LOOKUP_CODE
960 FROM PA_LOOKUPS
961 WHERE LOOKUP_TYPE = 'BUDGET CHANGE REASON'
962 AND LOOKUP_CODE = p_change_reason_code ;
963
964 BEGIN
965 x_msg_count := 0;
966 x_return_status := FND_API.G_RET_STS_SUCCESS;
967 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
968
969 IF l_debug_mode = 'Y' THEN
970 pa_debug.set_err_stack('PA_FP_WEBADI_UTILS.VALIDATE_CHANGE_REASON_CODE');
971 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
972 END IF;
973
974 -- Check for business rules violations
975
976 IF l_debug_mode = 'Y' THEN
977 pa_debug.g_err_stage:= 'Validating input parameters';
978 pa_debug.write(g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
979 END IF;
980
981 IF p_change_reason_code IS NOT NULL THEN
982 -- Open cursor c_code_exists_cur
983 OPEN c_code_exists_cur ;
984
985 FETCH c_code_exists_cur INTO l_lookup_code;
986
987 IF c_code_exists_cur%NOTFOUND THEN
988
989 IF l_debug_mode = 'Y' THEN
990 pa_debug.g_err_stage:= 'could not find change reason code';
991 pa_debug.write(g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
992 END IF;
993
994 x_return_status := FND_API.G_RET_STS_ERROR ;
995 END IF ;
996
997 IF l_debug_mode = 'Y' THEN
998 pa_debug.g_err_stage:= 'Exiting VALIDATE_CHANGE_REASON_CODE';
999 pa_debug.write(g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1000 pa_debug.reset_err_stack;
1001 END IF;
1002 END IF;
1003
1004 EXCEPTION
1005
1006 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1007 x_return_status := FND_API.G_RET_STS_ERROR;
1008 l_msg_count := FND_MSG_PUB.count_msg;
1009 IF l_msg_count = 1 THEN
1010 PA_INTERFACE_UTILS_PUB.get_messages
1011 (p_encoded => FND_API.G_TRUE,
1012 p_msg_index => 1,
1013 p_msg_count => l_msg_count,
1014 p_msg_data => l_msg_data,
1015 p_data => l_data,
1016 p_msg_index_out => l_msg_index_out);
1017
1018 x_msg_data := l_data;
1019 x_msg_count := l_msg_count;
1020 ELSE
1021 x_msg_count := l_msg_count;
1022
1023 END IF;
1024
1025 IF l_debug_mode = 'Y' THEN
1026 pa_debug.write('VALIDATE_CHANGE_REASON_CODE: ' || g_module_name,'Invalid arguments passed',5);
1027 pa_debug.write('VALIDATE_CHANGE_REASON_CODE: ' || g_module_name,pa_debug.G_Err_Stack,5);
1028 END IF;
1029 IF l_debug_mode = 'Y' THEN
1030
1031 pa_debug.g_err_stage:= 'Exiting VALIDATE_CHANGE_REASON_CODE';
1032 pa_debug.write(g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1033 pa_debug.reset_err_stack;
1034
1035 END IF;
1036 RETURN;
1037 WHEN OTHERS THEN
1038 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1039 x_msg_count := 1;
1040 x_msg_data := SQLERRM;
1041
1042 FND_MSG_PUB.add_exc_msg
1043 ( p_pkg_name => 'PA_FP_WEBADI_UTILS'
1044 ,p_procedure_name => 'VALIDATE_CHANGE_REASON_CODE'
1045 ,p_error_text => sqlerrm);
1046
1047 pa_debug.G_Err_Stack := SQLERRM;
1048 IF l_debug_mode = 'Y' THEN
1049 pa_debug.write('VALIDATE_CHANGE_REASON_CODE :' || g_module_name,pa_debug.G_Err_Stack,4);
1050 END IF;
1051 pa_debug.reset_err_stack;
1052
1053 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1054
1055 END VALIDATE_CHANGE_REASON_CODE ;
1056 */ -- end of bug 5350437
1057
1058 PROCEDURE VALIDATE_TXN_CURRENCY_CODE
1059 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
1060 ,p_proj_fp_options_id IN pa_proj_fp_options.proj_fp_options_id%TYPE
1061 ,p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE
1062 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1063 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1064 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1065 IS
1066 l_debug_mode VARCHAR2(1);
1067 l_msg_count NUMBER ;
1068 l_data VARCHAR2(2000);
1069 l_msg_data VARCHAR2(2000);
1070 l_msg_index_out NUMBER;
1071 l_exists VARCHAR2(1) ;
1072 l_curr_found BOOLEAN;
1073
1074
1075 CURSOR C_TXN_CURR_CODE IS
1076 SELECT txn_currency_code
1077 FROM pa_fp_txn_currencies
1078 WHERE fin_plan_version_id = p_budget_version_id
1079 AND proj_fp_options_id = p_proj_fp_options_id ;
1080
1081 BEGIN
1082
1083 x_msg_count := 0;
1084 x_return_status := FND_API.G_RET_STS_SUCCESS;
1085 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1086
1087 IF l_debug_mode = 'Y' THEN
1088 pa_debug.set_err_stack('PA_FP_WEBADI_UTILS.VALIDATE_TXN_CURRENCY_CODE');
1089 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1090 END IF;
1091
1092 -- Check for business rules violations
1093
1094 IF l_debug_mode = 'Y' THEN
1095 pa_debug.g_err_stage:= 'Validating input parameters';
1096 pa_debug.write(g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1097 END IF;
1098
1099 IF (p_txn_currency_code IS NULL) OR (p_budget_version_id is NULL )
1100 THEN
1101 IF l_debug_mode = 'Y' THEN
1102 pa_debug.g_err_stage:= 'Invalid input parameter';
1103 pa_debug.write(g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1104 END IF;
1105 PA_UTILS.ADD_MESSAGE
1106 (p_app_short_name => 'PA',
1107 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
1108 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1109
1110 END IF;
1111
1112
1113 IF (nvl(G_BUDGET_VERSION_ID,-99) <> p_budget_version_id
1114 OR nvl(G_TABLE_POPULATED,'N') <> 'Y' ) THEN
1115
1116 OPEN C_TXN_CURR_CODE ;
1117 FETCH C_TXN_CURR_CODE BULK COLLECT INTO
1118 G_TXN_CURRENCY_CODE_TBL ;
1119
1120 -- Initialize all the package level global variables
1121
1122 G_BUDGET_VERSION_ID := p_budget_version_id ;
1123 G_TABLE_POPULATED := 'Y' ;
1124
1125 END IF ;
1126
1127
1128 -- If the input currency code is there in the PLSQL table
1129 -- then return success else error .
1130
1131 l_curr_found := false;
1132 IF nvl(G_TXN_CURRENCY_CODE_TBL.last,0) >= 1 THEN
1133 FOR i in G_TXN_CURRENCY_CODE_TBL.FIRST..G_TXN_CURRENCY_CODE_TBL.LAST
1134 LOOP
1135 IF p_txn_currency_code = G_TXN_CURRENCY_CODE_TBL(i) THEN
1136 l_curr_found := true;
1137 END IF;
1138 END LOOP ;
1139 END IF ;
1140
1141 IF l_curr_found THEN
1142 x_return_status := FND_API.G_RET_STS_SUCCESS;
1143 ELSE
1144 x_return_status := FND_API.G_RET_STS_ERROR;
1145 END IF;
1146
1147 -- Close the cursor if its open
1148 IF C_TXN_CURR_CODE%ISOPEN THEN
1149 CLOSE C_TXN_CURR_CODE ;
1150 END IF ;
1151
1152 IF l_debug_mode = 'Y' THEN
1153 pa_debug.g_err_stage:= 'Exiting VALIDATE_TXN_CURRENCY_CODE';
1154 pa_debug.write(g_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1155 pa_debug.reset_err_stack;
1156 END IF;
1157
1158 EXCEPTION
1159
1160 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1161
1162 x_return_status := FND_API.G_RET_STS_ERROR;
1163 l_msg_count := FND_MSG_PUB.count_msg;
1164
1165 -- Close the cursor if its open
1166
1167 IF C_TXN_CURR_CODE%ISOPEN THEN
1168 CLOSE C_TXN_CURR_CODE ;
1169 END IF ;
1170
1171 IF l_msg_count = 1 THEN
1172 PA_INTERFACE_UTILS_PUB.get_messages
1173 (p_encoded => FND_API.G_TRUE,
1174 p_msg_index => 1,
1175 p_msg_count => l_msg_count,
1176 p_msg_data => l_msg_data,
1177 p_data => l_data,
1178 p_msg_index_out => l_msg_index_out);
1179
1180 x_msg_data := l_data;
1181 x_msg_count := l_msg_count;
1182 ELSE
1183 x_msg_count := l_msg_count;
1184 END IF;
1185
1186 IF l_debug_mode = 'Y' THEN
1187 pa_debug.reset_err_stack;
1188 END IF;
1189
1190 RETURN;
1191 WHEN OTHERS THEN
1192
1193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1194 x_msg_count := 1;
1195 x_msg_data := SQLERRM;
1196
1197 -- Close the cursor if its open
1198
1199 IF C_TXN_CURR_CODE%ISOPEN THEN
1200 CLOSE C_TXN_CURR_CODE ;
1201 END IF ;
1202
1203
1204 FND_MSG_PUB.add_exc_msg
1205 ( p_pkg_name => 'PA_FP_WEBADI_UTILS'
1206 ,p_procedure_name => 'VALIDATE_TXN_CURRENCY_CODE'
1207 ,p_error_text => sqlerrm);
1208
1209 pa_debug.G_Err_Stack := SQLERRM;
1210 IF l_debug_mode = 'Y' THEN
1211 pa_debug.write('VALIDATE_TXN_CURRENCY_CODE :' || g_module_name,pa_debug.G_Err_Stack,4);
1212 END IF;
1213 pa_debug.reset_err_stack;
1214
1215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1216
1217
1218 END VALIDATE_TXN_CURRENCY_CODE ;
1219
1220 /* Bug 5350437: Commented the below API
1221 PROCEDURE GET_VERSION_PERIODS_INFO
1222 ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
1223 ,x_period_name_tbl OUT NOCOPY pa_fp_webadi_pkg.l_period_name_tbl_typ --File.Sql.39 bug 4440895
1224 ,x_start_date_tbl OUT NOCOPY pa_fp_webadi_pkg.l_start_date_tbl_typ --File.Sql.39 bug 4440895
1225 ,x_end_date_tbl OUT NOCOPY pa_fp_webadi_pkg.l_end_date_tbl_typ --File.Sql.39 bug 4440895
1226 ,x_number_of_pds OUT NOCOPY pa_proj_period_profiles.number_of_periods%TYPE --File.Sql.39 bug 4440895
1227 ,x_period_profile_id OUT NOCOPY pa_budget_versions.period_profile_id%TYPE --File.Sql.39 bug 4440895
1228 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1229 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1230 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1231 )
1232 IS
1233
1234 l_debug_mode VARCHAR2(1) ;
1235 l_msg_count NUMBER := 0 ;
1236 l_data VARCHAR2(2000);
1237 l_msg_data VARCHAR2(2000);
1238 l_msg_index_out NUMBER;
1239
1240 CURSOR C_PERIOD_DATES_CUR IS
1241 SELECT
1242 null -- For SD
1243 , null -- For PD
1244 , ppp.period_name1
1245 , ppp.period_name2
1246 , ppp.period_name3
1247 , ppp.period_name4
1248 , ppp.period_name5
1249 , ppp.period_name6
1250 , ppp.period_name7
1251 , ppp.period_name8
1252 , ppp.period_name9
1253 , ppp.period_name10
1254 , ppp.period_name11
1255 , ppp.period_name12
1256 , ppp.period_name13
1257 , ppp.period_name14
1258 , ppp.period_name15
1259 , ppp.period_name16
1260 , ppp.period_name17
1261 , ppp.period_name18
1262 , ppp.period_name19
1263 , ppp.period_name20
1264 , ppp.period_name21
1265 , ppp.period_name22
1266 , ppp.period_name23
1267 , ppp.period_name24
1268 , ppp.period_name25
1269 , ppp.period_name26
1270 , ppp.period_name27
1271 , ppp.period_name28
1272 , ppp.period_name29
1273 , ppp.period_name30
1274 , ppp.period_name31
1275 , ppp.period_name32
1276 , ppp.period_name33
1277 , ppp.period_name34
1278 , ppp.period_name35
1279 , ppp.period_name36
1280 , ppp.period_name37
1281 , ppp.period_name38
1282 , ppp.period_name39
1283 , ppp.period_name40
1284 , ppp.period_name41
1285 , ppp.period_name42
1286 , ppp.period_name43
1287 , ppp.period_name44
1288 , ppp.period_name45
1289 , ppp.period_name46
1290 , ppp.period_name47
1291 , ppp.period_name48
1292 , ppp.period_name49
1293 , ppp.period_name50
1294 , ppp.period_name51
1295 , ppp.period_name52
1296 , null -- For SD
1297 , null -- For PD
1298 , ppp.period1_start_date
1299 , ppp.period2_start_date
1300 , ppp.period3_start_date
1301 , ppp.period4_start_date
1302 , ppp.period5_start_date
1303 , ppp.period6_start_date
1304 , ppp.period7_start_date
1305 , ppp.period8_start_date
1306 , ppp.period9_start_date
1307 , ppp.period10_start_date
1308 , ppp.period11_start_date
1309 , ppp.period12_start_date
1310 , ppp.period13_start_date
1311 , ppp.period14_start_date
1312 , ppp.period15_start_date
1313 , ppp.period16_start_date
1314 , ppp.period17_start_date
1315 , ppp.period18_start_date
1316 , ppp.period19_start_date
1317 , ppp.period20_start_date
1318 , ppp.period21_start_date
1319 , ppp.period22_start_date
1320 , ppp.period23_start_date
1321 , ppp.period24_start_date
1322 , ppp.period25_start_date
1323 , ppp.period26_start_date
1324 , ppp.period27_start_date
1325 , ppp.period28_start_date
1326 , ppp.period29_start_date
1327 , ppp.period30_start_date
1328 , ppp.period31_start_date
1329 , ppp.period32_start_date
1330 , ppp.period33_start_date
1331 , ppp.period34_start_date
1332 , ppp.period35_start_date
1333 , ppp.period36_start_date
1334 , ppp.period37_start_date
1335 , ppp.period38_start_date
1336 , ppp.period39_start_date
1337 , ppp.period40_start_date
1338 , ppp.period41_start_date
1339 , ppp.period42_start_date
1340 , ppp.period43_start_date
1341 , ppp.period44_start_date
1342 , ppp.period45_start_date
1343 , ppp.period46_start_date
1344 , ppp.period47_start_date
1345 , ppp.period48_start_date
1346 , ppp.period49_start_date
1347 , ppp.period50_start_date
1348 , ppp.period51_start_date
1349 , ppp.period52_start_date
1350 , null -- For SD
1351 , null -- For PD
1352 , ppp.period1_end_date
1353 , ppp.period2_end_date
1354 , ppp.period3_end_date
1355 , ppp.period4_end_date
1356 , ppp.period5_end_date
1357 , ppp.period6_end_date
1358 , ppp.period7_end_date
1359 , ppp.period8_end_date
1360 , ppp.period9_end_date
1361 , ppp.period10_end_date
1362 , ppp.period11_end_date
1363 , ppp.period12_end_date
1364 , ppp.period13_end_date
1365 , ppp.period14_end_date
1366 , ppp.period15_end_date
1367 , ppp.period16_end_date
1368 , ppp.period17_end_date
1369 , ppp.period18_end_date
1370 , ppp.period19_end_date
1371 , ppp.period20_end_date
1372 , ppp.period21_end_date
1373 , ppp.period22_end_date
1374 , ppp.period23_end_date
1375 , ppp.period24_end_date
1376 , ppp.period25_end_date
1377 , ppp.period26_end_date
1378 , ppp.period27_end_date
1379 , ppp.period28_end_date
1380 , ppp.period29_end_date
1381 , ppp.period30_end_date
1382 , ppp.period31_end_date
1383 , ppp.period32_end_date
1384 , ppp.period33_end_date
1385 , ppp.period34_end_date
1386 , ppp.period35_end_date
1387 , ppp.period36_end_date
1388 , ppp.period37_end_date
1389 , ppp.period38_end_date
1390 , ppp.period39_end_date
1391 , ppp.period40_end_date
1392 , ppp.period41_end_date
1393 , ppp.period42_end_date
1394 , ppp.period43_end_date
1395 , ppp.period44_end_date
1396 , ppp.period45_end_date
1397 , ppp.period46_end_date
1398 , ppp.period47_end_date
1399 , ppp.period48_end_date
1400 , ppp.period49_end_date
1401 , ppp.period50_end_date
1402 , ppp.period51_end_date
1403 , ppp.period52_end_date
1404 , ppp.number_of_periods
1405 , pbv.period_profile_id
1406 FROM
1407 pa_proj_period_profiles ppp
1408 , pa_budget_versions pbv
1409 WHERE pbv.budget_version_id = p_budget_version_id
1410 AND ppp.period_profile_id = pbv.period_profile_id ;
1411
1412 BEGIN
1413 x_msg_count := 0;
1414 x_return_status := FND_API.G_RET_STS_SUCCESS;
1415 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'Y');
1416
1417 IF l_debug_mode = 'Y' THEN
1418 pa_debug.set_err_stack('PA_FP_WEBADI_PKG.GET_VERSION_PERIOD_INFO');
1419 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1420 END IF;
1421
1422 IF l_debug_mode = 'Y' THEN
1423 pa_debug.g_err_stage := ':In PA_FP_WEBADI_PKG.GET_VERSION_PERIOD_INFO' ;
1424 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1425 END IF;
1426
1427 IF (p_budget_version_id IS NULL) THEN
1428 IF l_debug_mode = 'Y' THEN
1429 pa_debug.g_err_stage := 'INVALID INPUT PARAMETER';
1430 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
1431 END IF;
1432 x_return_status := FND_API.G_RET_STS_ERROR;
1433 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
1434 ,p_msg_name => 'PA_FP_INV_PARAM_PASSED');
1435 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1436 END IF;
1437
1438 OPEN C_PERIOD_DATES_CUR ;
1439
1440 FETCH C_PERIOD_DATES_CUR INTO
1441 x_period_name_tbl(1)
1442 , x_period_name_tbl(2)
1443 , x_period_name_tbl(3)
1444 , x_period_name_tbl(4)
1445 , x_period_name_tbl(5)
1446 , x_period_name_tbl(6)
1447 , x_period_name_tbl(7)
1448 , x_period_name_tbl(8)
1449 , x_period_name_tbl(9)
1450 , x_period_name_tbl(10)
1451 , x_period_name_tbl(11)
1452 , x_period_name_tbl(12)
1453 , x_period_name_tbl(13)
1454 , x_period_name_tbl(14)
1455 , x_period_name_tbl(15)
1456 , x_period_name_tbl(16)
1457 , x_period_name_tbl(17)
1458 , x_period_name_tbl(18)
1459 , x_period_name_tbl(19)
1460 , x_period_name_tbl(20)
1461 , x_period_name_tbl(21)
1462 , x_period_name_tbl(22)
1463 , x_period_name_tbl(23)
1464 , x_period_name_tbl(24)
1465 , x_period_name_tbl(25)
1466 , x_period_name_tbl(26)
1467 , x_period_name_tbl(27)
1468 , x_period_name_tbl(28)
1469 , x_period_name_tbl(29)
1470 , x_period_name_tbl(30)
1471 , x_period_name_tbl(31)
1472 , x_period_name_tbl(32)
1473 , x_period_name_tbl(33)
1474 , x_period_name_tbl(34)
1475 , x_period_name_tbl(35)
1476 , x_period_name_tbl(36)
1477 , x_period_name_tbl(37)
1478 , x_period_name_tbl(38)
1479 , x_period_name_tbl(39)
1480 , x_period_name_tbl(40)
1481 , x_period_name_tbl(41)
1482 , x_period_name_tbl(42)
1483 , x_period_name_tbl(43)
1484 , x_period_name_tbl(44)
1485 , x_period_name_tbl(45)
1486 , x_period_name_tbl(46)
1487 , x_period_name_tbl(47)
1488 , x_period_name_tbl(48)
1489 , x_period_name_tbl(49)
1490 , x_period_name_tbl(50)
1491 , x_period_name_tbl(51)
1492 , x_period_name_tbl(52)
1493 , x_period_name_tbl(53)
1494 , x_period_name_tbl(54)
1495 , x_start_date_tbl(1)
1496 , x_start_date_tbl(2)
1497 , x_start_date_tbl(3)
1498 , x_start_date_tbl(4)
1499 , x_start_date_tbl(5)
1500 , x_start_date_tbl(6)
1501 , x_start_date_tbl(7)
1502 , x_start_date_tbl(8)
1503 , x_start_date_tbl(9)
1504 , x_start_date_tbl(10)
1505 , x_start_date_tbl(11)
1506 , x_start_date_tbl(12)
1507 , x_start_date_tbl(13)
1508 , x_start_date_tbl(14)
1509 , x_start_date_tbl(15)
1510 , x_start_date_tbl(16)
1511 , x_start_date_tbl(17)
1512 , x_start_date_tbl(18)
1513 , x_start_date_tbl(19)
1514 , x_start_date_tbl(20)
1515 , x_start_date_tbl(21)
1516 , x_start_date_tbl(22)
1517 , x_start_date_tbl(23)
1518 , x_start_date_tbl(24)
1519 , x_start_date_tbl(25)
1520 , x_start_date_tbl(26)
1521 , x_start_date_tbl(27)
1522 , x_start_date_tbl(28)
1523 , x_start_date_tbl(29)
1524 , x_start_date_tbl(30)
1525 , x_start_date_tbl(31)
1526 , x_start_date_tbl(32)
1527 , x_start_date_tbl(33)
1528 , x_start_date_tbl(34)
1529 , x_start_date_tbl(35)
1530 , x_start_date_tbl(36)
1531 , x_start_date_tbl(37)
1532 , x_start_date_tbl(38)
1533 , x_start_date_tbl(39)
1534 , x_start_date_tbl(40)
1535 , x_start_date_tbl(41)
1536 , x_start_date_tbl(42)
1537 , x_start_date_tbl(43)
1538 , x_start_date_tbl(44)
1539 , x_start_date_tbl(45)
1540 , x_start_date_tbl(46)
1541 , x_start_date_tbl(47)
1542 , x_start_date_tbl(48)
1543 , x_start_date_tbl(49)
1544 , x_start_date_tbl(50)
1545 , x_start_date_tbl(51)
1546 , x_start_date_tbl(52)
1547 , x_start_date_tbl(53)
1548 , x_start_date_tbl(54)
1549 , x_end_date_tbl(1)
1550 , x_end_date_tbl(2)
1551 , x_end_date_tbl(3)
1552 , x_end_date_tbl(4)
1553 , x_end_date_tbl(5)
1554 , x_end_date_tbl(6)
1555 , x_end_date_tbl(7)
1556 , x_end_date_tbl(8)
1557 , x_end_date_tbl(9)
1558 , x_end_date_tbl(10)
1559 , x_end_date_tbl(11)
1560 , x_end_date_tbl(12)
1561 , x_end_date_tbl(13)
1562 , x_end_date_tbl(14)
1563 , x_end_date_tbl(15)
1564 , x_end_date_tbl(16)
1565 , x_end_date_tbl(17)
1566 , x_end_date_tbl(18)
1567 , x_end_date_tbl(19)
1568 , x_end_date_tbl(20)
1569 , x_end_date_tbl(21)
1570 , x_end_date_tbl(22)
1571 , x_end_date_tbl(23)
1572 , x_end_date_tbl(24)
1573 , x_end_date_tbl(25)
1574 , x_end_date_tbl(26)
1575 , x_end_date_tbl(27)
1576 , x_end_date_tbl(28)
1577 , x_end_date_tbl(29)
1578 , x_end_date_tbl(30)
1579 , x_end_date_tbl(31)
1580 , x_end_date_tbl(32)
1581 , x_end_date_tbl(33)
1582 , x_end_date_tbl(34)
1583 , x_end_date_tbl(35)
1584 , x_end_date_tbl(36)
1585 , x_end_date_tbl(37)
1586 , x_end_date_tbl(38)
1587 , x_end_date_tbl(39)
1588 , x_end_date_tbl(40)
1589 , x_end_date_tbl(41)
1590 , x_end_date_tbl(42)
1591 , x_end_date_tbl(43)
1592 , x_end_date_tbl(44)
1593 , x_end_date_tbl(45)
1594 , x_end_date_tbl(46)
1595 , x_end_date_tbl(47)
1596 , x_end_date_tbl(48)
1597 , x_end_date_tbl(49)
1598 , x_end_date_tbl(50)
1599 , x_end_date_tbl(51)
1600 , x_end_date_tbl(52)
1601 , x_end_date_tbl(53)
1602 , x_end_date_tbl(54)
1603 , x_number_of_pds
1604 , x_period_profile_id;
1605
1606 IF C_PERIOD_DATES_CUR%NOTFOUND THEN
1607 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc ;
1608 END IF ;
1609
1610
1611 IF l_debug_mode = 'Y' THEN
1612 pa_debug.g_err_stage := 'exiting get_version_period_info';
1613 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
1614 pa_debug.reset_err_stack;
1615 END IF ;
1616
1617 EXCEPTION
1618 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1619
1620 x_return_status := FND_API.G_RET_STS_ERROR;
1621 l_msg_count := FND_MSG_PUB.count_msg;
1622 -- Close the cursor
1623
1624 IF C_PERIOD_DATES_CUR%ISOPEN THEN
1625 CLOSE C_PERIOD_DATES_CUR ;
1626 END IF ;
1627
1628 IF l_debug_mode = 'Y' THEN
1629 pa_debug.g_err_stage := 'inside invalid arg exception of get_version_period_info';
1630 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
1631 END IF;
1632
1633 IF l_msg_count = 1 THEN
1634 PA_INTERFACE_UTILS_PUB.get_messages
1635 (p_encoded => FND_API.G_TRUE,
1636 p_msg_index => 1,
1637 p_msg_count => l_msg_count,
1638 p_msg_data => l_msg_data,
1639 p_data => l_data,
1640 p_msg_index_out => l_msg_index_out);
1641
1642 x_msg_data := l_data;
1643 x_msg_count := l_msg_count;
1644 ELSE
1645 x_msg_count := l_msg_count;
1646 END IF;
1647
1648 IF l_debug_mode = 'Y' THEN
1649 pa_debug.reset_err_stack;
1650 END IF ;
1651
1652 RETURN;
1653 WHEN OTHERS THEN
1654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1655 x_msg_count := 1;
1656 x_msg_data := SQLERRM;
1657
1658 IF C_PERIOD_DATES_CUR%ISOPEN THEN
1659 CLOSE C_PERIOD_DATES_CUR ;
1660 END IF ;
1661
1662 FND_MSG_PUB.add_exc_msg
1663 ( p_pkg_name => 'PA_FP_WEBADI_PKG'
1664 ,p_procedure_name => 'GET_VERSION_PERIOD_INFO' );
1665 IF l_debug_mode = 'Y' THEN
1666 pa_debug.write('DELETE_XFACE' || g_module_name,SQLERRM,4);
1667 pa_debug.write('DELETE_XFACE' || g_module_name,pa_debug.G_Err_Stack,4);
1668 END IF;
1669
1670 IF l_debug_mode = 'Y' THEN
1671 pa_debug.reset_err_stack;
1672 END IF ;
1673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1674
1675 END GET_VERSION_PERIODS_INFO;
1676 */ -- end of Bug 5350437
1677
1678 /* Bug 3986129: FP.M- Web ADI Dev Changes: The following api is no longer being called from PAFPWAPB.pls
1679 * retaining this just for reference */
1680 PROCEDURE CHECK_OVERLAPPING_DATES
1681 ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
1682 ,x_rec_failed_validation OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1683 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1684 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1685 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1686 )
1687 IS
1688
1689 l_debug_mode VARCHAR2(1) ;
1690 l_msg_count NUMBER := 0;
1691 l_data VARCHAR2(2000);
1692 l_msg_data VARCHAR2(2000);
1693 l_msg_index_out NUMBER;
1694 l_exists VARCHAR2(1) ;
1695 l_rowid ROWID ;
1696
1697 -- This cursor selects all the records from the xface table
1698 -- for which there exists records in eitherxface table or
1699 -- budget lines table with overlapping dates.
1700
1701 CURSOR C_OVERLAPPING_CUR IS
1702 SELECT a.rowid
1703 FROM PA_FP_WEBADI_XFACE_TMP a
1704 WHERE a.budget_version_id = p_budget_version_id
1705 AND ( EXISTS (SELECT 'Y'
1706 FROM PA_FP_WEBADI_XFACE_TMP b
1707 WHERE a.rowid <> b.rowid
1708 AND b.budget_version_id = p_budget_version_id
1709 AND b.resource_assignment_id = a.resource_assignment_id
1710 AND b.txn_currency_code = a.txn_currency_code
1711 AND a.start_date <= b.end_date
1712 AND a.end_date >= b.start_date )
1713 OR EXISTS (SELECT 'Y'
1714 FROM PA_BUDGET_LINES bl
1715 WHERE bl.budget_version_id = p_budget_version_id
1716 AND bl.resource_assignment_id = a.resource_assignment_id
1717 AND bl.txn_currency_code = a.txn_currency_code
1718 AND bl.start_date <> a.start_date
1719 AND a.start_date <= bl.end_date
1720 AND a.end_date >= bl.start_date )) ;
1721
1722 BEGIN
1723 x_msg_count := 0;
1724 x_return_status := FND_API.G_RET_STS_SUCCESS;
1725 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'Y');
1726
1727 IF l_debug_mode = 'Y' THEN
1728 pa_debug.set_err_stack('PA_FP_WEBADI_PKG.CHECK_OVERLAPPING_DATES');
1729 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1730 END IF;
1731
1732 IF l_debug_mode = 'Y' THEN
1733 pa_debug.g_err_stage := ':In PA_FP_WEBADI_PKG.CHECK_OVERLAPPING_DATES' ;
1734 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1735 END IF;
1736
1737 IF (p_budget_version_id IS NULL) THEN
1738 IF l_debug_mode = 'Y' THEN
1739 pa_debug.g_err_stage := 'INVALID INPUT PARAMETER';
1740 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
1741 END IF;
1742 x_return_status := FND_API.G_RET_STS_ERROR;
1743 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
1744 ,p_msg_name => 'PA_FP_INV_PARAM_PASSED');
1745 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1746 END IF;
1747
1748 IF l_debug_mode = 'Y' THEN
1749 pa_debug.g_err_stage := ':Opening Cursor C_OVERLAPPING_CUR ' ;
1750 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1751 END IF;
1752
1753 -- initialize x_rec_failed_validation initially.
1754 x_rec_failed_validation := 'N' ;
1755
1756 OPEN C_OVERLAPPING_CUR ;
1757 LOOP
1758 FETCH C_OVERLAPPING_CUR INTO
1759 l_rowid ;
1760 IF C_OVERLAPPING_CUR%FOUND THEN
1761 x_rec_failed_validation := 'Y' ;
1762 UPDATE PA_FP_WEBADI_XFACE_TMP tmp
1763 SET val_error_code = 'PA_FP_WEBADI_OVERLAPPING_DATE'
1764 ,val_error_flag = 'Y'
1765 WHERE rowid = l_rowid ;
1766 END IF ;
1767 EXIT WHEN C_OVERLAPPING_CUR%NOTFOUND ;
1768 END LOOP ;
1769
1770 CLOSE C_OVERLAPPING_CUR ;
1771
1772 IF l_debug_mode = 'Y' THEN
1773 pa_debug.reset_err_stack;
1774 END IF ;
1775
1776 EXCEPTION
1777 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1778 x_return_status := FND_API.G_RET_STS_ERROR;
1779 l_msg_count := FND_MSG_PUB.count_msg;
1780
1781 -- Close the cursor
1782
1783 IF C_OVERLAPPING_CUR%ISOPEN THEN
1784 CLOSE C_OVERLAPPING_CUR ;
1785 END IF ;
1786
1787 IF l_debug_mode = 'Y' THEN
1788 pa_debug.g_err_stage := 'inside invalid arg exception of check_overlapping_dates';
1789 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
1790 END IF;
1791
1792 IF l_msg_count = 1 THEN
1793 PA_INTERFACE_UTILS_PUB.get_messages
1794 (p_encoded => FND_API.G_TRUE,
1795 p_msg_index => 1,
1796 p_msg_count => l_msg_count,
1797 p_msg_data => l_msg_data,
1798 p_data => l_data,
1799 p_msg_index_out => l_msg_index_out);
1800
1801 x_msg_data := l_data;
1802 x_msg_count := l_msg_count;
1803 ELSE
1804 x_msg_count := l_msg_count;
1805 END IF;
1806
1807 IF l_debug_mode = 'Y' THEN
1808 pa_debug.reset_err_stack;
1809 END IF ;
1810
1811 RETURN;
1812 WHEN OTHERS THEN
1813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1814 x_msg_count := 1;
1815 x_msg_data := SQLERRM;
1816
1817 IF C_OVERLAPPING_CUR%ISOPEN THEN
1818 CLOSE C_OVERLAPPING_CUR ;
1819 END IF ;
1820
1821 FND_MSG_PUB.add_exc_msg
1822 ( p_pkg_name => 'PA_FP_WEBADI_PKG'
1823 ,p_procedure_name => 'C_OVERLAPPING_CUR' );
1824 IF l_debug_mode = 'Y' THEN
1825 pa_debug.write('check_overlapping_dates' || g_module_name,SQLERRM,4);
1826 pa_debug.write('check_overlapping_dates' || g_module_name,pa_debug.G_Err_Stack,4);
1827 END IF;
1828
1829 IF l_debug_mode = 'Y' THEN
1830 pa_debug.reset_err_stack;
1831 END IF ;
1832
1833 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1834
1835 END CHECK_OVERLAPPING_DATES;
1836
1837 /*==================================================================
1838 This API Accepts the error code, amount type and the currency type
1839 and returns the lookup code that contains the equivalent error message.
1840 This API is used in the context of WEBADI for throwing error to the
1841 user using the lookup code.
1842 ==================================================================*/
1843
1844 PROCEDURE GET_MC_ERROR_LOOKUP_CODE
1845 (p_mc_error_code IN pa_lookups.lookup_code%TYPE
1846 ,p_attr_set_cost_rev IN VARCHAR2
1847 ,p_attr_set_pc_pfc IN VARCHAR2
1848 ,x_error_lookup_code OUT NOCOPY pa_lookups.lookup_code%TYPE --File.Sql.39 bug 4440895
1849 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1850 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1851 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1852 AS
1853
1854 l_msg_count NUMBER := 0;
1855 l_data VARCHAR2(2000);
1856 l_msg_data VARCHAR2(2000);
1857 l_msg_index_out NUMBER;
1858 l_debug_mode VARCHAR2(1);
1859
1860 BEGIN
1861
1862 x_msg_count := 0;
1863 x_return_status := FND_API.G_RET_STS_SUCCESS;
1864 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1865
1866 IF l_debug_mode = 'Y' THEN
1867 pa_debug.g_err_stage:= 'Entering GET_ERROR_LOOKUP_CODE';
1868 pa_debug.write(g_module_name,pa_debug.g_err_stage,
1869 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1870
1871 pa_debug.set_curr_function( p_function => 'GET_ERROR_LOOKUP_CODE',
1872 p_debug_mode => l_debug_mode );
1873
1874 -- Check for business rules violations
1875 pa_debug.g_err_stage:= 'Validating input parameters';
1876 pa_debug.write(g_module_name,pa_debug.g_err_stage,
1877 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1878 END IF;
1879
1880 IF l_debug_mode = 'Y' THEN
1881 pa_debug.g_err_stage:= 'p_mc_error_code = '|| p_mc_error_code;
1882 pa_debug.write(g_module_name,pa_debug.g_err_stage,
1883 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1884 pa_debug.g_err_stage:= 'p_attr_set_cost_rev = '|| p_attr_set_cost_rev;
1885 pa_debug.write(g_module_name,pa_debug.g_err_stage,
1886 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1887 pa_debug.g_err_stage:= 'p_attr_set_pc_pfc = '|| p_attr_set_pc_pfc;
1888 pa_debug.write(g_module_name,pa_debug.g_err_stage,
1889 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1890 END IF;
1891
1892
1893 IF (p_mc_error_code IS NULL) OR
1894 (p_attr_set_cost_rev IS NULL) OR
1895 (p_attr_set_pc_pfc IS NULL)
1896 THEN
1897 PA_UTILS.ADD_MESSAGE
1898 (p_app_short_name => 'PA',
1899 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
1900 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1901 END IF;
1902
1903 --Determine the lookup code from the passed values
1904 IF p_mc_error_code = 'PA_FP_RATE_TYPE_REQ' OR p_mc_error_code = 'PA_FP_INVALID_RATE_TYPE' -- Rate type is null.
1905 THEN
1906 IF p_attr_set_pc_pfc = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_PROJFUNC
1907 THEN
1908 IF p_attr_set_cost_rev = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_COST
1909 THEN
1910 x_error_lookup_code := 'PA_FP_WEBADI_INV_PF_COST_RT';
1911 ELSE
1912 x_error_lookup_code := 'PA_FP_WEBADI_INV_PF_REV_RT'; -- amount is revenue.
1913 END IF;
1914
1915 ELSE
1916 IF p_attr_set_cost_rev = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_COST
1917 THEN
1918 x_error_lookup_code := 'PA_FP_WEBADI_INV_PR_COST_RT';
1919 ELSE
1920 x_error_lookup_code := 'PA_FP_WEBADI_INV_PR_REV_RT'; -- amount is revenue.
1921 END IF;
1922 END IF;
1923 ELSIF p_mc_error_code = 'PA_FP_USER_EXCH_RATE_REQ' -- Rate type is user and Rate is null.
1924 THEN
1925 IF p_attr_set_pc_pfc = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_PROJFUNC
1926 THEN
1927 IF p_attr_set_cost_rev = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_COST
1928 THEN
1929 x_error_lookup_code := 'PA_FP_WEBADI_INV_PF_COST_RATE';
1930 ELSE
1931 x_error_lookup_code := 'PA_FP_WEBADI_INV_PF_REV_RATE';
1932 END IF;
1933 ELSE
1934 IF p_attr_set_cost_rev = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_COST
1935 THEN
1936 x_error_lookup_code := 'PA_FP_WEBADI_INV_PR_COST_RATE';
1937 ELSE
1938 x_error_lookup_code := 'PA_FP_WEBADI_INV_PR_REV_RATE';
1939 END IF;
1940 END IF;
1941 ELSIF p_mc_error_code = 'PA_FP_INVALID_RATE_DATE_TYPE' -- Rate date type is null.
1942 THEN
1943 IF p_attr_set_pc_pfc = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_PROJFUNC
1944 THEN
1945 IF p_attr_set_cost_rev = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_COST
1946 THEN
1947 x_error_lookup_code := 'PA_FP_WEBADI_INV_PF_COST_RDT';
1948 ELSE
1949 x_error_lookup_code := 'PA_FP_WEBADI_INV_PF_REV_RDT';
1950 END IF;
1951 ELSE
1952 IF p_attr_set_cost_rev = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_COST
1953 THEN
1954 x_error_lookup_code := 'PA_FP_WEBADI_INV_PR_COST_RDT';
1955 ELSE
1956
1957 x_error_lookup_code := 'PA_FP_WEBADI_INV_PR_REV_RDT';
1958 END IF;
1959 END IF;
1960 ELSIF p_mc_error_code = 'PA_FP_INVALID_RATE_DATE' -- Rate date type is fixed date and rate date is null.
1961 THEN
1962 IF p_attr_set_pc_pfc = PA_FP_CONSTANTS_PKG.G_CURRENCY_TYPE_PROJFUNC
1963 THEN
1964 IF p_attr_set_cost_rev = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_COST
1965 THEN
1966 x_error_lookup_code := 'PA_FP_WEBADI_INV_PF_COST_RD';
1967 ELSE
1968 x_error_lookup_code := 'PA_FP_WEBADI_INV_PF_REV_RD';
1969 END IF;
1970 ELSE
1971 IF p_attr_set_cost_rev = PA_FP_CONSTANTS_PKG.G_AMOUNT_TYPE_COST
1972 THEN
1973 x_error_lookup_code := 'PA_FP_WEBADI_INV_PF_COST_RD';
1974 ELSE
1975 x_error_lookup_code := 'PA_FP_WEBADI_INV_PF_REV_RD';
1976 END IF;
1977 END IF;
1978 ELSE -- The parameters have invalid values.
1979 IF l_debug_mode = 'Y' THEN
1980 pa_debug.g_err_stage:= 'Error in input parameters';
1981 pa_debug.write(g_module_name,pa_debug.g_err_stage,
1982 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1983 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1984 END IF;
1985 END IF;
1986
1987 IF l_debug_mode = 'Y' THEN
1988 pa_debug.g_err_stage:= 'x_error_lookup_code = '||x_error_lookup_code;
1989 pa_debug.write(g_module_name,pa_debug.g_err_stage,
1990 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1991 END IF;
1992
1993 IF l_debug_mode = 'Y' THEN
1994 pa_debug.g_err_stage:= 'Exiting get_error_lookup_code';
1995 pa_debug.write(g_module_name,pa_debug.g_err_stage,
1996 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1997 pa_debug.reset_curr_function;
1998 END IF;
1999 EXCEPTION
2000
2001 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2002
2003 x_return_status := FND_API.G_RET_STS_ERROR;
2004 l_msg_count := FND_MSG_PUB.count_msg;
2005
2006 IF l_msg_count = 1 and x_msg_data IS NULL THEN
2007 PA_INTERFACE_UTILS_PUB.get_messages
2008 (p_encoded => FND_API.G_TRUE
2009 ,p_msg_index => 1
2010 ,p_msg_count => l_msg_count
2011 ,p_msg_data => l_msg_data
2012 ,p_data => l_data
2013 ,p_msg_index_out => l_msg_index_out);
2014 x_msg_data := l_data;
2015 x_msg_count := l_msg_count;
2016 ELSE
2017 x_msg_count := l_msg_count;
2018 END IF;
2019 IF l_debug_mode = 'Y' THEN
2020 pa_debug.reset_curr_function;
2021 END IF;
2022 RETURN;
2023
2024 WHEN others THEN
2025
2026 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2027 x_msg_count := 1;
2028 x_msg_data := SQLERRM;
2029
2030 FND_MSG_PUB.add_exc_msg
2031 ( p_pkg_name => 'PA_FP_WEBADI_UTILS'
2032 ,p_procedure_name => 'GET_ERROR_LOOKUP_CODE'
2033 ,p_error_text => x_msg_data);
2034
2035 IF l_debug_mode = 'Y' THEN
2036 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
2037 pa_debug.write(g_module_name,pa_debug.g_err_stage,
2038 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2039 END IF;
2040 IF l_debug_mode = 'Y' THEN
2041 pa_debug.reset_curr_function;
2042 END IF;
2043 RAISE;
2044
2045 END GET_MC_ERROR_LOOKUP_CODE;
2046
2047 /*==================================================================
2048 This api gets the lookup meanings and returns the lookup codes.
2049 This api is used in webadi.
2050 ==================================================================*/
2051
2052 PROCEDURE CONV_MC_ATTR_MEANING_TO_CODE /* webadi */
2053 (p_pc_cost_rate_type_name IN pa_conversion_types_v.user_conversion_type%TYPE
2054 ,p_pc_cost_rate_date_type_name IN pa_lookups.meaning%TYPE
2055 ,p_pfc_cost_rate_type_name IN pa_conversion_types_v.user_conversion_type%TYPE
2056 ,p_pfc_cost_rate_date_type_name IN pa_lookups.meaning%TYPE
2057 ,p_pc_rev_rate_type_name IN pa_conversion_types_v.user_conversion_type%TYPE
2058 ,p_pc_rev_rate_date_type_name IN pa_lookups.meaning%TYPE
2059 ,p_pfc_rev_rate_type_name IN pa_conversion_types_v.user_conversion_type%TYPE
2060 ,p_pfc_rev_rate_date_type_name IN pa_lookups.meaning%TYPE
2061 ,x_pc_cost_rate_type OUT NOCOPY pa_conversion_types_v.conversion_type%TYPE --File.Sql.39 bug 4440895
2062 ,x_pc_cost_rate_date_type OUT NOCOPY pa_lookups.lookup_code%TYPE --File.Sql.39 bug 4440895
2063 ,x_pfc_cost_rate_type OUT NOCOPY pa_conversion_types_v.conversion_type%TYPE --File.Sql.39 bug 4440895
2064 ,x_pfc_cost_rate_date_type OUT NOCOPY pa_lookups.lookup_code%TYPE --File.Sql.39 bug 4440895
2065 ,x_pc_rev_rate_type OUT NOCOPY pa_conversion_types_v.conversion_type%TYPE --File.Sql.39 bug 4440895
2066 ,x_pc_rev_rate_date_type OUT NOCOPY pa_lookups.lookup_code%TYPE --File.Sql.39 bug 4440895
2067 ,x_pfc_rev_rate_type OUT NOCOPY pa_conversion_types_v.conversion_type%TYPE --File.Sql.39 bug 4440895
2068 ,x_pfc_rev_rate_date_type OUT NOCOPY pa_lookups.lookup_code%TYPE --File.Sql.39 bug 4440895
2069 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2070 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2071 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2072 )
2073 AS
2074 l_msg_count NUMBER := 0;
2075 l_data VARCHAR2(2000);
2076 l_msg_data VARCHAR2(2000);
2077 l_msg_index_out NUMBER;
2078 l_debug_mode VARCHAR2(1);
2079
2080 l_conversion_type pa_conversion_types_v.conversion_type%TYPE;
2081 l_user_conversion_type pa_conversion_types_v.user_conversion_type%TYPE;
2082 l_lookup_code pa_lookups.lookup_code%TYPE;
2083 l_lookup_meaning pa_lookups.meaning%TYPE;
2084 l_lookup_type pa_lookups.lookup_type%TYPE;
2085
2086 cursor rate_type_cur is
2087 select conversion_type, user_conversion_type
2088 from pa_conversion_types_v
2089 where user_conversion_type IN (p_pc_cost_rate_type_name
2090 ,p_pfc_cost_rate_type_name
2091 ,p_pc_rev_rate_type_name
2092 ,p_pfc_rev_rate_type_name);
2093
2094 cursor rate_date_type_cur is
2095 select lookup_code, lookup_type, meaning
2096 from pa_lookups
2097 where lookup_type = 'PA_FP_RATE_DATE_TYPE'
2098 and meaning IN (p_pc_cost_rate_date_type_name
2099 ,p_pfc_cost_rate_date_type_name
2100 ,p_pc_rev_rate_date_type_name
2101 ,p_pfc_rev_rate_date_type_name);
2102
2103
2104 BEGIN
2105
2106 x_msg_count := 0;
2107 x_return_status := FND_API.G_RET_STS_SUCCESS;
2108 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
2109
2110 IF l_debug_mode = 'Y' THEN
2111 pa_debug.set_curr_function( p_function => 'CONV_MC_ATTR_MEANING_TO_CODE',
2112 p_debug_mode => l_debug_mode );
2113
2114 -- Check for business rules violations
2115 pa_debug.g_err_stage:= 'No Validation of input parameters is done in this API';
2116 pa_debug.write(g_module_name,pa_debug.g_err_stage,
2117 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2118
2119 pa_debug.g_err_stage:= 'Printing the input parameters.';
2120 pa_debug.write(g_module_name,pa_debug.g_err_stage,
2121 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2122
2123 pa_debug.g_err_stage:= 'p_pc_cost_rate_type_name : '||p_pc_cost_rate_type_name||
2124 ' p_pc_cost_rate_date_type_name : '||p_pc_cost_rate_date_type_name;
2125 pa_debug.write(g_module_name,pa_debug.g_err_stage,
2126 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2127
2128 pa_debug.g_err_stage:= 'p_pfc_cost_rate_type_name : '||p_pfc_cost_rate_type_name||
2129 ' p_pfc_cost_rate_date_type_name : '||p_pfc_cost_rate_date_type_name ;
2130 pa_debug.write(g_module_name,pa_debug.g_err_stage,
2131 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2132
2133 pa_debug.g_err_stage:= 'p_pc_rev_rate_type_name : '||p_pc_rev_rate_type_name||
2134 ' p_pc_rev_rate_date_type_name : '||p_pc_rev_rate_date_type_name;
2135 pa_debug.write(g_module_name,pa_debug.g_err_stage,
2136 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2137
2138 pa_debug.g_err_stage:= 'p_pfc_rev_rate_type_name : '||p_pfc_rev_rate_type_name||
2139 ' p_pfc_rev_rate_date_type_name : '||p_pfc_rev_rate_date_type_name ;
2140 pa_debug.write(g_module_name,pa_debug.g_err_stage,
2141 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2142 END IF;
2143
2144 FOR rate_type_rec IN rate_type_cur LOOP
2145 IF rate_type_rec.user_conversion_type = p_pc_cost_rate_type_name THEN
2146 x_pc_cost_rate_type := rate_type_rec.conversion_type;
2147 END IF ;
2148
2149 IF rate_type_rec.user_conversion_type = p_pfc_cost_rate_type_name THEN
2150 x_pfc_cost_rate_type:= rate_type_rec.conversion_type;
2151 END IF ;
2152
2153 IF rate_type_rec.user_conversion_type = p_pc_rev_rate_type_name THEN
2154 x_pc_rev_rate_type := rate_type_rec.conversion_type;
2155 END IF ;
2156
2157 IF rate_type_rec.user_conversion_type = p_pfc_rev_rate_type_name THEN
2158 x_pfc_rev_rate_type := rate_type_rec.conversion_type;
2159 END IF;
2160 END LOOP;
2161
2162 FOR rate_date_type_rec IN rate_date_type_cur LOOP
2163 IF rate_date_type_rec.meaning = p_pc_cost_rate_date_type_name THEN
2164 x_pc_cost_rate_date_type := rate_date_type_rec.lookup_code;
2165 END IF;
2166
2167 IF rate_date_type_rec.meaning = p_pfc_cost_rate_date_type_name THEN
2168 x_pfc_cost_rate_date_type := rate_date_type_rec.lookup_code;
2169 END IF ;
2170
2171 IF rate_date_type_rec.meaning = p_pc_rev_rate_date_type_name THEN
2172 x_pc_rev_rate_date_type := rate_date_type_rec.lookup_code;
2173 END IF ;
2174
2175 IF rate_date_type_rec.meaning = p_pfc_rev_rate_date_type_name THEN
2176 x_pfc_rev_rate_date_type := rate_date_type_rec.lookup_code;
2177 END IF;
2178 END LOOP;
2179
2180 IF l_debug_mode = 'Y' THEN
2181 pa_debug.g_err_stage:= 'Printing the output parameters.';
2182 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2183
2184 pa_debug.g_err_stage:= 'x_pc_cost_rate_type : '||x_pc_cost_rate_type||
2185 ' x_pc_cost_rate_date_type : '||x_pc_cost_rate_date_type ;
2186 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2187 pa_debug.g_err_stage:= 'x_pfc_cost_rate_type: '||x_pfc_cost_rate_type||
2188 ' x_pfc_cost_rate_date_type : '||x_pfc_cost_rate_date_type ;
2189 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2190
2191 pa_debug.g_err_stage:= 'x_pc_rev_rate_type : '||x_pc_rev_rate_type||
2192 ' x_pc_rev_rate_date_type : '||x_pc_rev_rate_date_type;
2193 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2194
2195 pa_debug.g_err_stage:= 'x_pfc_rev_rate_type : '||x_pfc_rev_rate_type||
2196 ' x_pfc_rev_rate_date_type : '||x_pfc_rev_rate_date_type ;
2197 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2198
2199 pa_debug.g_err_stage:= 'Exiting CONV_MC_ATTR_MEANING_TO_CODE';
2200 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2201 pa_debug.reset_curr_function;
2202 END IF;
2203
2204 EXCEPTION
2205
2206 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2207
2208 x_return_status := FND_API.G_RET_STS_ERROR;
2209 l_msg_count := FND_MSG_PUB.count_msg;
2210
2211
2212 IF l_msg_count = 1 and x_msg_data IS NULL THEN
2213 PA_INTERFACE_UTILS_PUB.get_messages
2214 (p_encoded => FND_API.G_TRUE
2215 ,p_msg_index => 1
2216 ,p_msg_count => l_msg_count
2217 ,p_msg_data => l_msg_data
2218 ,p_data => l_data
2219 ,p_msg_index_out => l_msg_index_out);
2220 x_msg_data := l_data;
2221 x_msg_count := l_msg_count;
2222 ELSE
2223 x_msg_count := l_msg_count;
2224 END IF;
2225 IF l_debug_mode = 'Y' THEN
2226 pa_debug.reset_curr_function;
2227 END IF;
2228 RETURN;
2229
2230 WHEN others THEN
2231
2232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2233 x_msg_count := 1;
2234 x_msg_data := SQLERRM;
2235
2236 FND_MSG_PUB.add_exc_msg
2237 ( p_pkg_name => 'PA_FP_WEBADI_UTILS'
2238 ,p_procedure_name => 'CONV_MC_ATTR_MEANING_TO_CODE'
2239 ,p_error_text => x_msg_data);
2240
2241 IF l_debug_mode = 'Y' THEN
2242 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
2243 pa_debug.write(g_module_name,pa_debug.g_err_stage,
2244 PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2245 END IF;
2246 IF l_debug_mode = 'Y' THEN
2247 pa_debug.reset_curr_function;
2248 END IF;
2249 RAISE;
2250
2251 END CONV_MC_ATTR_MEANING_TO_CODE ;
2252
2253 FUNCTION GET_AMOUNT_TYPE_NAME (
2254 p_amount_type_code IN PA_AMOUNT_TYPES_B.AMOUNT_TYPE_CODE%TYPE )
2255 RETURN PA_AMOUNT_TYPES_VL.AMOUNT_TYPE_NAME%TYPE
2256 IS
2257
2258 l_amount_type_name PA_AMOUNT_TYPES_VL.AMOUNT_TYPE_NAME%TYPE ;
2259
2260 BEGIN
2261
2262 SELECT amount_type_name
2263 INTO l_amount_type_name
2264 FROM pa_amount_types_vl
2265 WHERE amount_type_code = p_amount_type_code;
2266
2267 RETURN l_amount_type_name;
2268
2269 END GET_AMOUNT_TYPE_NAME;
2270
2271 /*==================================================================================
2272 This procedure is used to get the layout name and the layout type code when the
2273 layout type is passed.
2274 06-Apr-2005 prachand Created as a part of WebAdi changes.
2275 Initial Creation
2276 ===================================================================================*/
2277 PROCEDURE get_layout_details
2278 (p_layout_code IN pa_proj_fp_options.cost_layout_code%TYPE
2279 ,p_integrator_code IN bne_integrators_b.integrator_code%TYPE
2280 ,x_layout_name OUT NOCOPY bne_layouts_tl.user_name%TYPE --File.Sql.39 bug 4440895
2281 ,x_layout_type_code OUT NOCOPY pa_lookups.lookup_code%TYPE --File.Sql.39 bug 4440895
2282 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2283 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2284 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2285 ) IS
2286
2287 --Start of variables used for debugging
2288 l_return_status VARCHAR2(1);
2289 l_msg_count NUMBER := 0;
2290 l_msg_data VARCHAR2(2000);
2291 l_data VARCHAR2(2000);
2292 l_msg_index_out NUMBER;
2293 l_debug_mode VARCHAR2(30);
2294 l_debug_level3 CONSTANT NUMBER := 3;
2295 l_debug_level5 CONSTANT NUMBER := 5;
2296 l_module_name VARCHAR2(200) := g_module_name||'.get_layout_details';
2297 --End of variables used for debugging
2298 l_integrator_code VARCHAR2(30);
2299 l_layout_name bne_layouts_tl.user_name%TYPE;
2300 BEGIN
2301 x_msg_count := 0;
2302 x_return_status := FND_API.G_RET_STS_SUCCESS;
2303
2304 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2305 l_debug_mode := NVL(l_debug_mode, 'Y');
2306
2307 -- Set curr function
2308 pa_debug.set_curr_function(
2309 p_function =>'pa_fp_webadi_utils.get_layout_details'
2310 ,p_debug_mode => l_debug_mode );
2311
2312 IF l_debug_mode = 'Y' THEN
2313 pa_debug.g_err_stage:='Validating input parameters';
2314 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level3);
2315 END IF;
2316
2317 IF p_layout_code IS NULL THEN
2318
2319 IF l_debug_mode = 'Y' THEN
2320 pa_debug.g_err_stage:='p_layout_code is '||p_layout_code;
2321 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
2322 END IF;
2323 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2324 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
2325
2326 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2327 END IF;
2328
2329 SELECT integrator_code
2330 INTO l_integrator_code
2331 FROM bne_layouts_b
2332 WHERE layout_code= p_layout_code
2333 AND application_id = (SELECT application_id
2334 FROM FND_APPLICATION
2335 WHERE APPLICATION_SHORT_NAME = 'PA');
2336
2337
2338 IF p_integrator_code IS NOT NULL THEN
2339 l_integrator_code := p_integrator_code;
2340 END IF;
2341 x_layout_name := NULL;
2342 IF l_integrator_code = 'FINPLAN_BUDGET_PERIODIC' THEN
2343 x_layout_type_code := 'PERIODIC_BUDGET';
2344 ELSIF l_integrator_code = 'FINPLAN_BUDGET_NON_PERIODIC' THEN
2345 x_layout_type_code := 'NON_PERIODIC_BUDGET';
2346 ELSIF l_integrator_code = 'FINPLAN_FORECAST_PERIODIC' THEN
2347 x_layout_type_code := 'PERIODIC_FORECAST';
2348 ELSIF l_integrator_code = 'FINPLAN_FORECAST_NON_PERIODIC' THEN
2349 x_layout_type_code := 'NON_PERIODIC_FORECAST';
2350 END IF;
2351 -- reset curr function
2352 IF l_debug_mode = 'Y' THEN
2353 pa_debug.g_err_stage:='x_layout_type_code is '|| x_layout_type_code;
2354 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
2355 pa_debug.g_err_stage:='x_layout_name is '|| x_layout_name;
2356 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
2357
2358 END IF;
2359 pa_debug.reset_curr_function();
2360
2361 EXCEPTION
2362
2363 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2364 l_msg_count := FND_MSG_PUB.count_msg;
2365 IF l_msg_count = 1 THEN
2366 PA_INTERFACE_UTILS_PUB.get_messages
2367 (p_encoded => FND_API.G_TRUE
2368 ,p_msg_index => 1
2369 ,p_msg_count => l_msg_count
2370 ,p_msg_data => l_msg_data
2371 ,p_data => l_data
2372 ,p_msg_index_out => l_msg_index_out);
2373
2374 x_msg_data := l_data;
2375 x_msg_count := l_msg_count;
2376 ELSE
2377 x_msg_count := l_msg_count;
2378 END IF;
2379 x_return_status := FND_API.G_RET_STS_ERROR;
2380
2381 IF l_debug_mode = 'Y' THEN
2382 pa_debug.g_err_stage:='Invalid Arguments Passed Or called api raised an error';
2383 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
2384
2385 END IF;
2386 -- reset curr function
2387 pa_debug.reset_curr_function();
2388 RETURN;
2389 WHEN OTHERS THEN
2390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2391 x_msg_count := 1;
2392 x_msg_data := SQLERRM;
2393
2394 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_fp_webadi_utils'
2395 ,p_procedure_name => 'get_layout_details');
2396
2397 IF l_debug_mode = 'Y' THEN
2398 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
2399 pa_debug.write( l_module_name,pa_debug.g_err_stage,l_debug_level5);
2400 END IF;
2401 -- reset curr function
2402 pa_debug.Reset_Curr_Function();
2403 RAISE;
2404
2405 END get_layout_details;
2406
2407
2408 -- Bug 3986129: FP.M Web ADI Dev changes: Added the follwoing apis
2409
2410 -- This api would be called from a java method when the user wants to delete the data from the excel interface
2411 -- that is downloaded for a session.
2412
2413 PROCEDURE delete_interface_tbl_data
2414 (p_request_id IN pa_budget_versions.request_id%TYPE,
2415 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2416 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2417 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2418
2419 IS
2420 l_debug_mode VARCHAR2(30);
2421 l_module_name VARCHAR2(100) := 'PAFPWAUB.delete_interface_tbl_data';
2422 l_msg_count NUMBER := 0;
2423 l_data VARCHAR2(2000);
2424 l_msg_data VARCHAR2(2000);
2425 l_msg_index_out NUMBER;
2426
2427 l_run_id pa_fp_webadi_upload_inf.run_id%TYPE;
2428 l_budget_version_id pa_budget_versions.budget_version_id%TYPE;
2429
2430 BEGIN
2431 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2432
2433 x_msg_count := 0;
2434 x_return_status := FND_API.G_RET_STS_SUCCESS;
2435
2436 PA_DEBUG.Set_Curr_Function(p_function => l_module_name,
2437 p_debug_mode => l_debug_mode );
2438
2439 IF l_debug_mode = 'Y' THEN
2440 pa_debug.g_err_stage:='Entering delete_inter_face_data';
2441 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2442 pa_debug.g_err_stage:='Validating input parameters';
2443 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2444 END IF;
2445
2446 IF p_request_id IS NULL THEN
2447 -- throwing error as this is a mandatory parameter
2448 IF l_debug_mode = 'Y' THEN
2449 pa_debug.g_err_stage:='p_request_id is passed as null';
2450 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2451 END IF;
2452 pa_utils.add_message
2453 (p_app_short_name => 'PA',
2454 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
2455 p_token1 => 'PROCEDURENAME',
2456 p_value1 => l_module_name);
2457 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2458 END IF;
2459
2460 IF l_debug_mode = 'Y' THEN
2461 pa_debug.g_err_stage:='Getting run_id and budget_version_id';
2462 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2463 END IF;
2464
2465 BEGIN
2466 SELECT run_id,
2467 budget_version_id
2468 INTO l_run_id,
2469 l_budget_version_id
2470 FROM pa_fp_webadi_upload_inf
2471 WHERE request_id = p_request_id
2472 AND ROWNUM = 1;
2473 EXCEPTION
2474 WHEN NO_DATA_FOUND THEN
2475 IF l_debug_mode = 'Y' THEN
2476 pa_debug.g_err_stage:='Invalid request_id is passed';
2477 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2478 END IF;
2479 pa_utils.add_message
2480 (p_app_short_name => 'PA',
2481 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
2482 p_token1 => 'PROCEDURENAME',
2483 p_value1 => l_module_name);
2484 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2485 END;
2486
2487 IF l_debug_mode = 'Y' THEN
2488 pa_debug.g_err_stage:='Calling PA_FP_WEBADI_PKG.delete_xface';
2489 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2490 END IF;
2491
2492 PA_FP_WEBADI_PKG.delete_xface
2493 ( p_run_id => l_run_id
2494 ,x_return_status => x_return_status
2495 ,x_msg_count => x_msg_count
2496 ,x_msg_data => x_msg_data);
2497
2498 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2499 IF l_debug_mode = 'Y' THEN
2500 pa_debug.g_err_stage := 'Call to PA_FP_WEBADI_PKG.delete_xface returned with error';
2501 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2502 END IF;
2503 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2504 END IF;
2505
2506 IF l_debug_mode = 'Y' THEN
2507 pa_debug.g_err_stage:='PA_FP_WEBADI_PKG.delete_xface Called';
2508 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2509 pa_debug.g_err_stage:='Updating pa_budget_versions';
2510 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2511 END IF;
2512
2513 UPDATE pa_budget_versions
2514 SET plan_processing_code = null,
2515 request_id = null,
2516 record_version_number = record_version_number + 1
2517 WHERE budget_version_id = l_budget_version_id;
2518
2519 -- a explicit commit is required here to reflect the changes
2520 COMMIT;
2521
2522 IF l_debug_mode = 'Y' THEN
2523 pa_debug.g_err_stage:='Leaving delete_interface_tbl_data';
2524 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2525 END IF;
2526 pa_debug.reset_curr_function;
2527
2528 EXCEPTION
2529 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2530 l_msg_count := FND_MSG_PUB.count_msg;
2531 IF l_msg_count = 1 THEN
2532 PA_INTERFACE_UTILS_PUB.get_messages
2533 (p_encoded => FND_API.G_TRUE
2534 ,p_msg_index => 1
2535 ,p_msg_count => l_msg_count
2536 ,p_msg_data => l_msg_data
2537 ,p_data => l_data
2538 ,p_msg_index_out => l_msg_index_out);
2539 END IF;
2540 pa_debug.reset_curr_function;
2541 WHEN OTHERS THEN
2542 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PAFPWAUB'
2543 ,p_procedure_name => 'delete_interface_tbl_data');
2544
2545 IF l_debug_mode = 'Y' THEN
2546 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
2547 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2548 END IF;
2549 pa_debug.reset_curr_function;
2550 RAISE;
2551 END delete_interface_tbl_data;
2552
2553 -- This api would be called from a java method when the user wants to resubmit the request for the concurrent
2554 -- program, if the upload processing of the plan version fails for some reason.
2555
2556 PROCEDURE resubmit_conc_request
2557 (p_old_request_id IN pa_budget_versions.request_id%TYPE,
2558 x_new_request_id OUT NOCOPY pa_budget_versions.request_id%TYPE, --File.Sql.39 bug 4440895
2559 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2560 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2561 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2562
2563 IS
2564 l_debug_mode VARCHAR2(30);
2565 l_module_name VARCHAR2(100) := 'PAFPWAUB.resubmit_conc_request';
2566 l_msg_count NUMBER := 0;
2567 l_data VARCHAR2(2000);
2568 l_msg_data VARCHAR2(2000);
2569 l_msg_index_out NUMBER;
2570
2571 l_run_id pa_fp_webadi_upload_inf.run_id%TYPE;
2572 l_budget_version_id pa_budget_versions.budget_version_id%TYPE;
2573 l_new_request_id pa_budget_versions.request_id%TYPE;
2574 -- MOAC changes.
2575 l_org_id pa_projects_all.org_id%TYPE;
2576 BEGIN
2577 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2578
2579 x_msg_count := 0;
2580 x_return_status := FND_API.G_RET_STS_SUCCESS;
2581
2582 PA_DEBUG.Set_Curr_Function(p_function => l_module_name,
2583 p_debug_mode => l_debug_mode );
2584
2585 IF l_debug_mode = 'Y' THEN
2586 pa_debug.g_err_stage:='Entering resubmit_conc_request';
2587 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2588 pa_debug.g_err_stage:='Validating input parameters';
2589 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2590 END IF;
2591
2592 IF p_old_request_id IS NULL THEN
2593 -- throwing error as this is a mandatory parameter
2594 IF l_debug_mode = 'Y' THEN
2595 pa_debug.g_err_stage:='p_old_request_id is passed as null';
2596 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2597 END IF;
2598 pa_utils.add_message
2599 (p_app_short_name => 'PA',
2600 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
2601 p_token1 => 'PROCEDURENAME',
2602 p_value1 => l_module_name);
2603 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2604 END IF;
2605
2606 IF l_debug_mode = 'Y' THEN
2607 pa_debug.g_err_stage:='Getting run_id and budget_version_id';
2608 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2609 END IF;
2610
2611 BEGIN
2612 SELECT run_id,
2613 budget_version_id
2614 INTO l_run_id,
2615 l_budget_version_id
2616 FROM pa_fp_webadi_upload_inf
2617 WHERE request_id = p_old_request_id
2618 AND ROWNUM = 1;
2619 EXCEPTION
2620 WHEN NO_DATA_FOUND THEN
2621 IF l_debug_mode = 'Y' THEN
2622 pa_debug.g_err_stage:='Invalid request_id is passed';
2623 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2624 END IF;
2625 pa_utils.add_message
2626 (p_app_short_name => 'PA',
2627 p_msg_name => 'PA_FP_INV_PARAM_PASSED',
2628 p_token1 => 'PROCEDURENAME',
2629 p_value1 => l_module_name);
2630 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2631 END;
2632
2633 IF l_debug_mode = 'Y' THEN
2634 pa_debug.g_err_stage:='Resubmitting the concurrent request';
2635 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2636 END IF;
2637
2638 -- MOAC changes for release 12.
2639 -- Need to set the org id context before submitting a conc request.
2640 -- Getting the org id from pa_projects_all.
2641 -- Not embedding this sql in a block as this has to get a
2642 -- record/org id no matter what.
2643 -- If it fails let this be an unhandled exception and
2644 -- let it raise or handle at the end of the program.
2645
2646 SELECT ppa.org_id
2647 INTO l_org_id
2648 FROM pa_projects_all ppa,
2649 pa_budget_versions pbv
2650 WHERE pbv.project_id = ppa.project_id
2651 AND pbv.budget_version_id = l_budget_version_id;
2652 fnd_request.set_org_id(l_org_id);
2653
2654 -- End of MOAC changes.
2655
2656 l_new_request_id := FND_REQUEST.submit_request
2657 (application => 'PA',
2658 program => 'PAFPWACP',
2659 description => 'PRC: Process spreadsheet plan data',
2660 start_time => NULL,
2661 sub_request => false,
2662 argument1 => 'N',
2663 argument2 => l_run_id);
2664
2665 IF l_new_request_id = 0 THEN
2666 IF l_debug_mode = 'Y' THEN
2667 pa_debug.g_err_stage:='The concurrent request Resubmission falied';
2668 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2669 END IF;
2670
2671 x_return_status := FND_API.G_RET_STS_ERROR;
2672
2673 UPDATE pa_budget_versions
2674 SET plan_processing_code = 'XLUE'
2675 WHERE budget_version_id = l_budget_version_id;
2676
2677 x_new_request_id := l_new_request_id;
2678 ELSIF l_new_request_id > 0 THEN
2679 pa_debug.g_err_stage := 'plan data processing Request Id is'||TO_CHAR (l_new_request_id);
2680 IF l_debug_mode = 'Y' THEN
2681 pa_debug.write_file ('PA_FP_WEBADI_UTILS ' || pa_debug.g_err_stage);
2682 END IF;
2683
2684 IF l_debug_mode = 'Y' THEN
2685 pa_debug.g_err_stage:='Updating pa_budget_versions';
2686 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2687 END IF;
2688
2689 UPDATE pa_budget_versions
2690 SET plan_processing_code = 'XLUP',
2691 request_id = l_new_request_id
2692 WHERE budget_version_id = l_budget_version_id;
2693
2694 -- updating the interface table with the new request_id
2695 UPDATE pa_fp_webadi_upload_inf
2696 SET request_id = l_new_request_id
2697 WHERE budget_version_id = l_budget_version_id
2698 AND run_id = l_run_id;
2699
2700 -- returning back the new request_id
2701 x_new_request_id := l_new_request_id;
2702 END IF;
2703
2704 -- a explicit commit is required here to reflect the changes
2705 COMMIT;
2706
2707 IF l_debug_mode = 'Y' THEN
2708 pa_debug.g_err_stage:='Leaving resubmit_conc_request';
2709 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
2710 END IF;
2711 pa_debug.reset_curr_function;
2712
2713 EXCEPTION
2714 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2715 l_msg_count := FND_MSG_PUB.count_msg;
2716 IF l_msg_count = 1 THEN
2717 PA_INTERFACE_UTILS_PUB.get_messages
2718 (p_encoded => FND_API.G_TRUE
2719 ,p_msg_index => 1
2720 ,p_msg_count => l_msg_count
2721 ,p_msg_data => l_msg_data
2722 ,p_data => l_data
2723 ,p_msg_index_out => l_msg_index_out);
2724 END IF;
2725 pa_debug.reset_curr_function;
2726 WHEN OTHERS THEN
2727 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PAFPWAUB'
2728 ,p_procedure_name => 'resubmit_conc_request');
2729
2730 IF l_debug_mode = 'Y' THEN
2731 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
2732 pa_debug.write(l_module_name,pa_debug.g_err_stage,5);
2733 END IF;
2734 pa_debug.reset_curr_function;
2735 RAISE;
2736 END resubmit_conc_request;
2737
2738 -- Bug 3986129: FP.M Web ADI Dev changes: Ends
2739
2740 -- Bug 3986129: FP.M Web ADI Dev changes: Added the follwoing apis
2741 /* =================================================================================
2742 This function is used is FPM's Budget and Forecasting webadi download query to get the period amounts
2743 of the current baselined plan version
2744 =======================================================================================*/
2745 FUNCTION get_current_amount(
2746 p_fin_plan_type_id NUMBER,
2747 p_plan_class_code VARCHAR2,
2748 p_project_id NUMBER,
2749 p_fin_plan_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE,
2750 p_task_id NUMBER,
2751 p_resource_list_member_id NUMBER,
2752 p_uom pa_resource_assignments.unit_of_measure%TYPE,
2753 p_txn_curr_code pa_budget_lines.txn_currency_code%TYPE,
2754 p_amount VARCHAR2)
2755 RETURN NUMBER
2756 IS
2757 l_quantity number :=null;
2758 l_number number;
2759 l_curr_bv_id number := null;
2760
2761 /* Added for bug 6453050 */
2762 l_txn_raw_cost number :=null;
2763 l_txn_burdened_cost number :=null;
2764 l_txn_revenue number :=null;
2765 l_amount number :=null;
2766
2767 cursor bud_line_exists_cur is
2768 select 1
2769 from pa_budget_lines pbl, pa_resource_assignments pra
2770 where pra.project_id = p_project_id
2771 and pra.task_id = p_task_id
2772 and pra.resource_list_member_id = p_resource_list_member_id
2773 and pra.unit_of_measure = p_uom
2774 and pra.resource_assignment_id = pbl.resource_assignment_id
2775 and pra.budget_version_id = pbl.budget_version_id
2776 and pbl.budget_version_id = l_curr_bv_id;
2777 BEGIN
2778
2779 /*Derive the Current Budget Version Id using following logic
2780 If p_plan_class_code is BUDGET:
2781 current_version_id = current baselined version of same plan type
2782 If p_budget_version is a FORECAST version:
2783 current_version_id = current baselined version of APPROVED BUDGET plan type
2784 */
2785 if p_plan_class_code = 'BUDGET' then
2786 begin
2787 select pbv.budget_version_id
2788 into l_curr_bv_id
2789 from pa_budget_versions pbv,
2790 pa_proj_fp_options pfo
2791 where pfo.fin_plan_type_id = p_fin_plan_type_id
2792 and pfo.project_id = p_project_id
2793 and pfo.fin_plan_option_level_code = 'PLAN_VERSION'
2794 and pfo.fin_plan_preference_code = p_fin_plan_preference_code
2795 and pfo.fin_plan_version_id = pbv.budget_version_id
2796 and pbv.current_flag = 'Y';
2797 exception
2798 when no_data_found then
2799 l_curr_bv_id := null;
2800 end;
2801 elsif p_plan_class_code = 'FORECAST' then
2802 if p_fin_plan_preference_code = 'COST_ONLY' then
2803 -- looking for APPROVED COST BUDGET plan type
2804 begin
2805 select bv.budget_version_id
2806 into l_curr_bv_id
2807 from pa_proj_fp_options po,
2808 pa_budget_versions bv
2809 where po.project_id = p_project_id and
2810 po.fin_plan_option_level_code = 'PLAN_VERSION' and
2811 bv.approved_cost_plan_type_flag = 'Y' and
2812 po.fin_plan_version_id = bv.budget_version_id and
2813 bv.current_flag = 'Y';
2814 exception
2815 when NO_DATA_FOUND then
2816 l_curr_bv_id := null;
2817 end;
2818 else
2819 -- looking for APPROVED REVENUE BUDGET plan type
2820 begin
2821 select bv.budget_version_id
2822 into l_curr_bv_id
2823 from pa_proj_fp_options po,
2824 pa_budget_versions bv
2825 where po.project_id = p_project_id and
2826 po.fin_plan_option_level_code = 'PLAN_VERSION' and
2827 bv.approved_rev_plan_type_flag = 'Y' and
2828 po.fin_plan_version_id = bv.budget_version_id and
2829 bv.current_flag = 'Y';
2830 exception
2831 when NO_DATA_FOUND then
2832 l_curr_bv_id := -1;
2833 end;
2834 end if; -- l_fin_plan_pref_code
2835 end if;
2836
2837
2838
2839 if l_curr_bv_id is not null then
2840 -- Check if budget line exists for task, resource and uom
2841 open bud_line_exists_cur;
2842 fetch bud_line_exists_cur into l_number;
2843 if bud_line_exists_cur%notfound then
2844 l_quantity :=null;
2845 else
2846 begin
2847 /* Bug 5144013 : Changed the following select queries to refer
2848 to new entity pa_resource_asgn_curr instead of pa_budget_lines.
2849 This is done as part of merging the MRUP3 changes done in 11i into R12.
2850
2851 if p_amount = 'QUANTITY' THEN
2852 select total_display_quantity into l_quantity
2853 from pa_resource_asgn_curr rac,
2854 pa_resource_assignments pra
2855 where rac.budget_version_id = l_curr_bv_id
2856 and pra.budget_version_id = rac.budget_version_id
2857 and pra.task_id = p_task_id
2858 and pra.resource_list_member_id = p_resource_list_member_id
2859 and pra.unit_of_measure = p_uom
2860 and pra.resource_assignment_id = rac.resource_assignment_id
2861 and rac.txn_currency_code = p_txn_curr_code;
2862
2863 elsif p_amount ='RAW_COST' then
2864
2865 select total_txn_raw_cost into l_quantity
2866 from pa_resource_asgn_curr rac,
2867 pa_resource_assignments pra
2868 where rac.budget_version_id = l_curr_bv_id
2869 and pra.budget_version_id = rac.budget_version_id
2870 and pra.task_id = p_task_id
2871 and pra.resource_list_member_id = p_resource_list_member_id
2872 and pra.unit_of_measure = p_uom
2873 and pra.resource_assignment_id = rac.resource_assignment_id
2874 and rac.txn_currency_code = p_txn_curr_code;
2875
2876 elsif p_amount = 'BURDENED_COST' then
2877 select total_txn_burdened_cost into l_quantity
2878 from pa_resource_asgn_curr rac,
2879 pa_resource_assignments pra
2880 where rac.budget_version_id = l_curr_bv_id
2881 and pra.budget_version_id = rac.budget_version_id
2882 and pra.task_id = p_task_id
2883 and pra.resource_list_member_id = p_resource_list_member_id
2884 and pra.unit_of_measure = p_uom
2885 and pra.resource_assignment_id = rac.resource_assignment_id
2886 and rac.txn_currency_code = p_txn_curr_code;
2887 elsif p_amount = 'REVENUE' then
2888 select total_txn_revenue into l_quantity
2889 from pa_resource_asgn_curr rac,
2890 pa_resource_assignments pra
2891 where rac.budget_version_id = l_curr_bv_id
2892 and pra.budget_version_id = rac.budget_version_id
2893 and pra.task_id = p_task_id
2894 and pra.resource_list_member_id = p_resource_list_member_id
2895 and pra.unit_of_measure = p_uom
2896 and pra.resource_assignment_id = rac.resource_assignment_id
2897 and rac.txn_currency_code = p_txn_curr_code;
2898 else
2899 l_quantity := null;
2900 end if; */
2901 -- Commented above code and added below for Bug# 6453050
2902 select total_display_quantity
2903 ,total_txn_raw_cost
2904 ,total_txn_burdened_cost
2905 ,total_txn_revenue
2906 into l_quantity
2907 ,l_txn_raw_cost
2908 ,l_txn_burdened_cost
2909 ,l_txn_revenue
2910 from pa_resource_asgn_curr rac,
2911 pa_resource_assignments pra
2912 where rac.budget_version_id = l_curr_bv_id
2913 and pra.project_id = p_project_id
2914 and pra.budget_version_id = rac.budget_version_id
2915 and pra.task_id = p_task_id
2916 and pra.resource_list_member_id = p_resource_list_member_id
2917 and pra.unit_of_measure = p_uom
2918 and pra.resource_assignment_id = rac.resource_assignment_id
2919 and rac.txn_currency_code = p_txn_curr_code;
2920
2921 if p_amount = 'QUANTITY' THEN
2922 l_amount := l_quantity;
2923 elsif p_amount ='RAW_COST' then
2924 l_amount := l_txn_raw_cost;
2925 elsif p_amount = 'BURDENED_COST' then
2926 l_amount := l_txn_burdened_cost;
2927 elsif p_amount = 'REVENUE' then
2928 l_amount := l_txn_revenue;
2929 else
2930 l_amount := null;
2931 end if;
2932
2933 exception
2934 when no_data_found then
2935 l_amount :=to_number(null); -- Modified for Bug# 6453050
2936 end;
2937 end if; -- if bud_line_exists_cur%notfound
2938
2939 close bud_line_exists_cur;
2940
2941
2942 end if; --if p_curr_budget_version_id is not null
2943 return l_amount; -- Modified for Bug# 6453050
2944 EXCEPTION
2945 when others then
2946 return to_number(null);
2947
2948 END;
2949
2950
2951 /* =================================================================================
2952 This function is used is FPM's Budget and Forecasting webadi download query to
2953 get the period amounts of the original baselined plan version
2954 =======================================================================================*/
2955 FUNCTION get_original_amount(
2956 p_fin_plan_type_id NUMBER,
2957 p_plan_class_code VARCHAR2,
2958 p_project_id NUMBER,
2959 p_fin_plan_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE,
2960 p_task_id NUMBER,
2961 p_resource_list_member_id NUMBER,
2962 p_uom pa_resource_assignments.unit_of_measure%TYPE,
2963 p_txn_curr_code pa_budget_lines.txn_currency_code%TYPE,
2964 p_amount VARCHAR2)
2965 RETURN NUMBER
2966 IS
2967 l_quantity number :=null;
2968 l_number number;
2969 l_orig_bv_id number :=null;
2970
2971 cursor bud_line_exists_cur is
2972 select 1
2973 from pa_budget_lines pbl, pa_resource_assignments pra
2974 where pra.project_id = p_project_id
2975 and pra.task_id = p_task_id
2976 and pra.resource_list_member_id = p_resource_list_member_id
2977 and pra.unit_of_measure = p_uom
2978 and pra.resource_assignment_id = pbl.resource_assignment_id
2979 and pra.budget_version_id = pbl.budget_version_id
2980 and pbl.budget_version_id = l_orig_bv_id;
2981 BEGIN
2982
2983 /*Derive the Original Budget Version Id using following logic
2984 If p_plan_class_code is BUDGET :
2985 original_version_id = original baselined version of same plan type
2986 If p_plan_class_code is FORECAST :
2987 original_version_id = original baselined version of APPROVED BUDGET plan type
2988 */
2989 if p_plan_class_code = 'BUDGET' then
2990 begin
2991 select pbv.budget_version_id
2992 into l_orig_bv_id
2993 from pa_budget_versions pbv,
2994 pa_proj_fp_options pfo
2995 where pfo.fin_plan_type_id = p_fin_plan_type_id
2996 and pfo.project_id = p_project_id
2997 and pfo.fin_plan_option_level_code = 'PLAN_VERSION'
2998 and pfo.fin_plan_preference_code = p_fin_plan_preference_code
2999 and pfo.fin_plan_version_id = pbv.budget_version_id
3000 and pbv.current_original_flag = 'Y';
3001 exception
3002 when no_data_found then
3003 l_orig_bv_id := null;
3004 end;
3005 elsif p_plan_class_code = 'FORECAST' then
3006 if p_fin_plan_preference_code = 'COST_ONLY' then
3007 -- looking for APPROVED COST BUDGET plan type
3008 begin
3009 select bv.budget_version_id
3010 into l_orig_bv_id
3011 from pa_proj_fp_options po,
3012 pa_budget_versions bv
3013 where po.project_id = p_project_id and
3014 po.fin_plan_option_level_code = 'PLAN_VERSION' and
3015 bv.approved_cost_plan_type_flag = 'Y' and
3016 po.fin_plan_version_id = bv.budget_version_id and
3017 bv.current_original_flag = 'Y';
3018 exception
3019 when NO_DATA_FOUND then
3020 l_orig_bv_id := null;
3021 end;
3022 elsif p_fin_plan_preference_code = 'REVENUE_ONLY' then
3023 -- looking for APPROVED REVENUE BUDGET plan type
3024 begin
3025 select bv.budget_version_id
3026 into l_orig_bv_id
3027 from pa_proj_fp_options po,
3028 pa_budget_versions bv
3029 where po.project_id = p_project_id and
3030 po.fin_plan_option_level_code = 'PLAN_VERSION' and
3031 bv.approved_rev_plan_type_flag = 'Y' and
3032 po.fin_plan_version_id = bv.budget_version_id and
3033 bv.current_original_flag = 'Y';
3034 exception
3035 when NO_DATA_FOUND then
3036 l_orig_bv_id := null;
3037 end;
3038 else
3039 -- looking for APPROVED COST AND REVENUE BUDGET plan type
3040 begin
3041 select bv.budget_version_id
3042 into l_orig_bv_id
3043 from pa_proj_fp_options po,
3044 pa_budget_versions bv
3045 where po.project_id = p_project_id and
3046 po.fin_plan_option_level_code = 'PLAN_VERSION' and
3047 bv.approved_cost_plan_type_flag = 'Y' and
3048 bv.approved_rev_plan_type_flag = 'Y' and
3049 po.fin_plan_version_id = bv.budget_version_id and
3050 bv.current_original_flag = 'Y';
3051 exception
3052 when NO_DATA_FOUND then
3053 l_orig_bv_id := null;
3054 end;
3055 end if; -- l_fin_plan_pref_code
3056 end if; --p_plan_class_code
3057
3058 if l_orig_bv_id is not null then
3059 -- Check if budget line exists for task, resource and uom
3060 open bud_line_exists_cur;
3061 fetch bud_line_exists_cur into l_number;
3062 if bud_line_exists_cur%notfound then
3063 l_quantity :=null;
3064 else
3065 begin
3066 /* Bug 5144013 : Changed the following select queries to refer
3067 to new entity pa_resource_asgn_curr instead of pa_budget_lines.
3068 This is done as part of merging the MRUP3 changes done in 11i into R12.
3069 */
3070 if p_amount = 'QUANTITY' THEN
3071 select total_display_quantity into l_quantity
3072 from pa_resource_asgn_curr rac,
3073 pa_resource_assignments pra
3074 where rac.budget_version_id = l_orig_bv_id
3075 and pra.budget_version_id = rac.budget_version_id
3076 and pra.task_id = p_task_id
3077 and pra.resource_list_member_id = p_resource_list_member_id
3078 and pra.unit_of_measure = p_uom
3079 and pra.resource_assignment_id = rac.resource_assignment_id
3080 and rac.txn_currency_code = p_txn_curr_code;
3081
3082 elsif p_amount ='RAW_COST' then
3083
3084 select total_txn_raw_cost into l_quantity
3085 from pa_resource_asgn_curr rac,
3086 pa_resource_assignments pra
3087 where rac.budget_version_id = l_orig_bv_id
3088 and pra.budget_version_id = rac.budget_version_id
3089 and pra.task_id = p_task_id
3090 and pra.resource_list_member_id = p_resource_list_member_id
3091 and pra.unit_of_measure = p_uom
3092 and pra.resource_assignment_id = rac.resource_assignment_id
3093 and rac.txn_currency_code = p_txn_curr_code;
3094
3095 elsif p_amount = 'BURDENED_COST' then
3096 select total_txn_burdened_cost into l_quantity
3097 from pa_resource_asgn_curr rac,
3098 pa_resource_assignments pra
3099 where rac.budget_version_id = l_orig_bv_id
3100 and pra.budget_version_id = rac.budget_version_id
3101 and pra.task_id = p_task_id
3102 and pra.resource_list_member_id = p_resource_list_member_id
3103 and pra.unit_of_measure = p_uom
3104 and pra.resource_assignment_id = rac.resource_assignment_id
3105 and rac.txn_currency_code = p_txn_curr_code;
3106 elsif p_amount = 'REVENUE' then
3107 select total_txn_revenue into l_quantity
3108 from pa_resource_asgn_curr rac,
3109 pa_resource_assignments pra
3110 where rac.budget_version_id = l_orig_bv_id
3111 and pra.budget_version_id = rac.budget_version_id
3112 and pra.task_id = p_task_id
3113 and pra.resource_list_member_id = p_resource_list_member_id
3114 and pra.unit_of_measure = p_uom
3115 and pra.resource_assignment_id = rac.resource_assignment_id
3116 and rac.txn_currency_code = p_txn_curr_code;
3117 else
3118 l_quantity := null;
3119 end if; -- p_amount endif
3120
3121 exception
3122 when no_data_found then
3123 l_quantity :=to_number(null);
3124 end;
3125 end if; -- bud_line_exists_cur%notfound
3126
3127 close bud_line_exists_cur;
3128
3129
3130 end if; -- if p_orig_budget_version_id is not null
3131 return l_quantity;
3132 EXCEPTION
3133 when others then
3134 return to_number(null);
3135
3136 END;
3137
3138
3139 /* =================================================================================
3140 This function is used is FPM's Budget and Forecasting webadi download query to
3141 get the period amounts of the prior forecast plan version
3142 =======================================================================================*/
3143 FUNCTION get_prior_forecast_amount(
3144 p_fin_plan_type_id NUMBER,
3145 p_plan_class_code VARCHAR2,
3146 p_project_id NUMBER,
3147 p_fin_plan_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE,
3148 p_task_id NUMBER,
3149 p_resource_list_member_id NUMBER,
3150 p_uom pa_resource_assignments.unit_of_measure%TYPE,
3151 p_txn_curr_code pa_budget_lines.txn_currency_code%TYPE,
3152 p_amount VARCHAR2)
3153 RETURN NUMBER
3154 IS
3155 l_quantity number :=null;
3156 l_number number;
3157 l_pf_bv_id number :=null;
3158
3159 cursor bud_line_exists_cur is
3160 select 1
3161 from pa_budget_lines pbl, pa_resource_assignments pra
3162 where pra.project_id = p_project_id
3163 and pra.task_id = p_task_id
3164 and pra.resource_list_member_id = p_resource_list_member_id
3165 and pra.unit_of_measure = p_uom
3166 and pra.resource_assignment_id = pbl.resource_assignment_id
3167 and pra.budget_version_id = pbl.budget_version_id
3168 and pbl.budget_version_id = l_pf_bv_id;
3169 BEGIN
3170
3171 /*Derive the Prior Forecast Budget Version Id using following logic
3172 If p_plan_class_code is BUDGET :
3173 No Need to derive as Prior Forecast amounts are not going to be shown in
3174 Periodic Budget Layout in FP.M Budget and Forecasting WebADI
3175 If p_plan_class_code is FORECAST :
3176 x_prior_fcst_version_id = current baselined version of same plan type
3177 */
3178 if p_plan_class_code = 'BUDGET' then
3179 return to_number(null);
3180 elsif p_plan_class_code = 'FORECAST' then
3181 begin
3182 select pbv.budget_version_id
3183 into l_pf_bv_id
3184 from pa_budget_versions pbv,
3185 pa_proj_fp_options pfo
3186 where pfo.fin_plan_type_id = p_fin_plan_type_id
3187 and pfo.project_id = p_project_id
3188 and pfo.fin_plan_option_level_code = 'PLAN_VERSION'
3189 and pfo.fin_plan_preference_code = p_fin_plan_preference_code
3190 and pfo.fin_plan_version_id = pbv.budget_version_id
3191 and pbv.current_flag = 'Y';
3192 exception
3193 when no_data_found then
3194 l_pf_bv_id := null;
3195 end;
3196 end if; --p_plan_class_code
3197
3198 if l_pf_bv_id is not null then
3199 -- Check if budget line exists for task, resource and uom
3200 open bud_line_exists_cur;
3201 fetch bud_line_exists_cur into l_number;
3202 if bud_line_exists_cur%notfound then
3203 l_quantity :=null;
3204 else
3205 begin
3206 /* Bug 5144013 : Changed the following select queries to refer
3207 to new entity pa_resource_asgn_curr instead of pa_budget_lines.
3208 This is done as part of merging the MRUP3 changes done in 11i into R12.
3209 */
3210 if p_amount = 'QUANTITY' THEN
3211 select total_display_quantity into l_quantity
3212 from pa_resource_asgn_curr rac,
3213 pa_resource_assignments pra
3214 where rac.budget_version_id = l_pf_bv_id
3215 and pra.budget_version_id = rac.budget_version_id
3216 and pra.task_id = p_task_id
3217 and pra.resource_list_member_id = p_resource_list_member_id
3218 and pra.unit_of_measure = p_uom
3219 and pra.resource_assignment_id = rac.resource_assignment_id
3220 and rac.txn_currency_code = p_txn_curr_code;
3221
3222 elsif p_amount ='RAW_COST' then
3223
3224 select total_txn_raw_cost into l_quantity
3225 from pa_resource_asgn_curr rac,
3226 pa_resource_assignments pra
3227 where rac.budget_version_id = l_pf_bv_id
3228 and pra.budget_version_id = rac.budget_version_id
3229 and pra.task_id = p_task_id
3230 and pra.resource_list_member_id = p_resource_list_member_id
3231 and pra.unit_of_measure = p_uom
3232 and pra.resource_assignment_id = rac.resource_assignment_id
3233 and rac.txn_currency_code = p_txn_curr_code;
3234
3235 elsif p_amount = 'BURDENED_COST' then
3236 select total_txn_burdened_cost into l_quantity
3237 from pa_resource_asgn_curr rac,
3238 pa_resource_assignments pra
3239 where rac.budget_version_id = l_pf_bv_id
3240 and pra.budget_version_id = rac.budget_version_id
3241 and pra.task_id = p_task_id
3242 and pra.resource_list_member_id = p_resource_list_member_id
3243 and pra.unit_of_measure = p_uom
3244 and pra.resource_assignment_id = rac.resource_assignment_id
3245 and rac.txn_currency_code = p_txn_curr_code;
3246 elsif p_amount = 'REVENUE' then
3247 select total_txn_revenue into l_quantity
3248 from pa_resource_asgn_curr rac,
3249 pa_resource_assignments pra
3250 where rac.budget_version_id = l_pf_bv_id
3251 and pra.budget_version_id = rac.budget_version_id
3252 and pra.task_id = p_task_id
3253 and pra.resource_list_member_id = p_resource_list_member_id
3254 and pra.unit_of_measure = p_uom
3255 and pra.resource_assignment_id = rac.resource_assignment_id
3256 and rac.txn_currency_code = p_txn_curr_code;
3257 else
3258 l_quantity := null;
3259 end if; -- p_amount endif
3260
3261 exception
3262 when no_data_found then
3263 l_quantity :=to_number(null);
3264 end;
3265 end if; -- bud_line_exists_cur%notfound
3266
3267 close bud_line_exists_cur;
3268
3269
3270 end if; -- if p_orig_budget_version_id is not null
3271 return l_quantity;
3272 EXCEPTION
3273 when others then
3274 return to_number(null);
3275
3276 END;
3277
3278
3279 /* =================================================================================
3280 This function is used is FPM's Budget and Forecasting webadi download query to get the
3281 period amounts for the following amount types: RAW_COST_RATE,BURDENED_COST_RATE,BILL_RATE,
3282 'TOTAL_QTY''FCST_QTY',TOTAL_RAW_COST,FCST_RAW_COST,TOTAL_REV,FCST_REV,TOTAL_BURDENED_COST,
3283 FCST_BURDENED_COST,ACTUAL_QTY,ACTUAL_RAW_COST,ACTUAL_BURD_COST,ACTUAL_REVENUE,ETC_QTY,
3284 ETC_RAW_COST,ETC_BURDENED_COST,ETC_REVENUE
3285 =======================================================================================*/
3286 /* Bug 5144013: The following changes are made as part of merging the MRUP3 changes done in 11i into R12,
3287 a. display_quantity from pa_budget_lines is refered instead of quantity when p_amount_code
3288 is TOTAL_QTY or FCST_QTY.
3289 b. Rates are shown only when the rate_based_flag of the resource assignment is 'Y'.
3290 */
3291 FUNCTION get_period_amounts(
3292 p_budget_version_id NUMBER,
3293 p_amount_code VARCHAR2,
3294 p_resource_assignment_id pa_budget_lines.resource_assignment_id%TYPE,
3295 p_txn_currency_code pa_budget_lines.txn_currency_code%TYPE,
3296 p_prd_start_date DATE,
3297 p_prd_end_date DATE,
3298 preceding_date DATE,
3299 succedeing_date DATE)
3300 return number
3301 is
3302 l_return NUMBER :=null;
3303 begin
3304 if (p_prd_start_date is null and p_prd_end_date is null) and (preceding_date is null and succedeing_date is null) then
3305 return to_number(null);
3306 end if;
3307
3308 select decode(p_amount_code,'RAW_COST_RATE',DECODE(pra.rate_based_flag,'Y',(sum((decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
3309 decode(nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)),0,to_number(null),
3310 ((nvl(bl.quantity,0) - nvl(bl.init_quantity,0))*nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)))))))
3311 /decode(sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
3312 decode(nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)),0,to_number(null),
3313 (nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))),0, to_number(null)
3314 ,sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
3315 decode(nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)),0,to_number(null),
3316 (nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))))),NULL),
3317 'ETC_RAW_COST_RATE',DECODE(pra.rate_based_flag,'Y',(sum((decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
3318 decode(nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)),0,to_number(null),
3319 ((nvl(bl.quantity,0) - nvl(bl.init_quantity,0))*nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)))))))
3320 /decode(sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
3321 decode(nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)),0,to_number(null),
3322 (nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))),0, to_number(null)
3323 ,sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
3324 decode(nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)),0,to_number(null),
3325 (nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))))),NULL),
3326 'BURDENED_COST_RATE',DECODE(pra.rate_based_flag,'Y',(sum((decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
3327 decode(nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)),0,to_number(null),
3328 ((nvl(bl.quantity,0) - nvl(bl.init_quantity,0))*nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)))))))
3329 /decode(sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
3330 decode(nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)),0,to_number(null),
3331 (nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))),0, to_number(null)
3332 ,sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
3333 decode(nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)),0,to_number(null),
3334 (nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))))),NULL),
3335 'ETC_BURDENED_COST_RATE',DECODE(pra.rate_based_flag,'Y',(sum((decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
3336 decode(nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)),0,to_number(null),
3337 ((nvl(bl.quantity,0) - nvl(bl.init_quantity,0))*nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)))))))
3338 /decode(sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
3339 decode(nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)),0,to_number(null),
3340 (nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))),0, to_number(null)
3341 ,sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
3342 decode(nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)),0,to_number(null),
3343 (nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))))),NULL),
3344 'BILL_RATE',DECODE(pra.rate_based_flag,'Y',(sum((decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
3345 decode(nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)),0,to_number(null),
3346 ((nvl(bl.quantity,0) - nvl(bl.init_quantity,0))*nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)))))))
3347 /decode(sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
3348 decode(nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)),0,to_number(null),
3349 (nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))),0, to_number(null)
3350 ,sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
3351 decode(nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)),0,to_number(null),
3352 (nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))))),NULL),
3353 'ETC_BILL_RATE',DECODE(pra.rate_based_flag,'Y',(sum((decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
3354 decode(nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)),0,to_number(null),
3355 ((nvl(bl.quantity,0) - nvl(bl.init_quantity,0))*nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)))))))
3356 /decode(sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
3357 decode(nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)),0,to_number(null),
3358 (nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))),0, to_number(null)
3359 ,sum(decode(nvl(bl.quantity,0) - nvl(bl.init_quantity,0),0,to_number(null),
3360 decode(nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)),0,to_number(null),
3361 (nvl(bl.quantity,0) - nvl(bl.init_quantity,0))))))),NULL),
3362 'TOTAL_QTY',sum(bl.display_quantity),
3363 'FCST_QTY',sum(bl.display_quantity),
3364 'TOTAL_RAW_COST' ,sum(bl.txn_raw_cost),
3365 'FCST_RAW_COST' ,sum(bl.txn_raw_cost),
3366 'TOTAL_REV' ,sum(bl.txn_revenue),
3367 'FCST_REVENUE' ,sum(bl.txn_revenue),
3368 'TOTAL_BURDENED_COST' ,sum(bl.txn_burdened_cost),
3369 'FCST_BURDENED_COST' ,sum(bl.txn_burdened_cost),
3370 'ACTUAL_QTY',sum(bl.init_quantity),
3371 'ACTUAL_RAW_COST',sum(bl.txn_init_raw_cost),
3372 'ACTUAL_BURD_COST',sum(bl.txn_init_burdened_cost),
3373 'ACTUAL_REVENUE',sum(bl.txn_init_revenue),
3374 'ETC_QTY',DECODE(sum(bl.display_quantity),null,null,sum(bl.quantity-nvl(bl.init_quantity,0))),
3375 'ETC_RAW_COST',sum(bl.txn_raw_cost-nvl(bl.txn_init_raw_cost,0)),
3376 'ETC_BURDENED_COST',sum(bl.txn_burdened_cost-nvl(bl.txn_init_burdened_cost,0)),
3377 'ETC_REVENUE', sum(bl.txn_revenue-nvl(bl.txn_init_revenue,0)))
3378 into l_return
3379 from pa_budget_lines bl,
3380 pa_resource_assignments pra
3381 where bl.budget_version_id = p_budget_version_id
3382 and bl.resource_assignment_id = p_resource_assignment_id
3383 and bl.txn_currency_code = p_txn_currency_code
3384 and pra.resource_assignment_id = bl.resource_assignment_id
3385 and ((p_prd_start_date is not null and p_prd_end_date is not null and (decode(bl.start_date,p_prd_start_date,1,
3386 decode(bl.end_date,p_prd_end_date,1,
3387 decode((((p_prd_end_date-bl.end_date)/(abs(p_prd_end_date-bl.end_date)))*((bl.start_date-p_prd_start_date)/(abs(bl.start_date-p_prd_start_date)))),-1,0,1)))=1))
3388 or
3389 (p_prd_start_date is null and p_prd_end_date is null and decode(preceding_date,null,decode(((bl.start_date-succedeing_date)/abs(bl.start_date-succedeing_date)),1,1,0),
3390 decode(((bl.end_date-preceding_date)/abs(bl.end_date-preceding_date)),-1,1,0))=1))
3391 GROUP BY pra.rate_based_flag;
3392
3393 /*if l_return is null then
3394 l_return :=0;
3395 end if;*/ --Commented for bug 4365889 Issue# 8
3396
3397 return l_return;
3398
3399 exception
3400 when no_data_found then
3401 return to_number(null);
3402 end;
3403
3404
3405
3406 END PA_FP_WEBADI_UTILS;