[Home] [Help]
PACKAGE BODY: APPS.PA_FCST_GLOBAL
Source
1 PACKAGE BODY pa_fcst_global as
2 /* $Header: PARFSGLB.pls 120.2 2006/01/11 17:48:18 ramurthy noship $ */
3
4 PROCEDURE GetDefaultValue(x_start_period OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
5 x_show_amount OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
6 x_project_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
7 x_project_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
8 x_view_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9 x_apply_prob_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
10 x_class_display OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
11 x_prj_owner_display OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
12 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
13 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
14 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
15 )
16 IS
17
18
19 l_start_period VARCHAR2(30);
20 l_init_msg_list VARCHAR2(20) := FND_API.G_TRUE;
21 l_start_date DATE;
22 l_end_date DATE;
23 l_csr_end_date DATE;
24 l_period_type VARCHAR2(30);
25
26 l_class_display VARCHAR2(60);
27 l_prj_owner_display NUMBER;
28
29 /* NPE Changes Begin*/
30 l_msg_count NUMBER := 0;
31 l_data VARCHAR2(2000);
32 l_msg_data VARCHAR2(2000);
33 l_msg_index_out NUMBER;
34 l_return_status VARCHAR2(2000);
35 l_user_profile_option_name1 varchar2(1000);
36 l_user_profile_option_name2 varchar2(1000);
37 org_count NUMBER;
38 l_default_calendar VARCHAR2(240);
39 /* NPE Changes End */
40
41
42 CURSOR C1(l_start_date DATE) IS
43 SELECT end_date
44 FROM pa_fcst_periods_tmp
45 WHERE start_date >= l_start_date
46 order by start_date;
47
48
49
50 BEGIN
51
52 Populate_Fcst_Periods;
53
54 x_return_status := FND_API.G_RET_STS_SUCCESS;
55
56 --Clear the global PL/SQL message table
57
58 IF FND_API.TO_BOOLEAN( l_init_msg_list ) THEN
59 FND_MSG_PUB.initialize;
60 END IF;
61
62
63 /* Initializing Global variable */
64
65 pa_fcst_global.Global_proj_fcst_show_amt := 'REVENUE';
66 pa_fcst_global.Global_view_type := 'PERIODIC';
67 pa_fcst_global.Global_ProbabilityPerFlag := 'N';
68 pa_fcst_global.Global_Period_Set_Name := NULL;
69
70
71 /* Assigning Global variable to Out variables. */
72
73 x_show_amount := pa_fcst_global.Global_proj_fcst_show_amt;
74 x_project_type := null;
75 x_project_status := null;
76 x_view_type := pa_fcst_global.Global_view_type;
77 x_apply_prob_flag := pa_fcst_global.Global_ProbabilityPerFlag;
78
79
80 l_period_type := pa_fcst_global.Global_period_type;
81
82
83 /* Added the logic to display the classification and Project owner colum
84 display in the screen */
85
86 BEGIN
87 /* NPE Changes - The MO: Operating Unit should be checked here .
88 If we can put a join with pa_implementations then
89 in NO_data_found, we can raise message for missing Mo: operating Unit.
90 Since I am not very sure, so keeping the logic to check for Mo operating unit
91 later in the code*/
92
93 SELECT KEY_MEMBER_ROLE_ID,
94 FORECAST_CLASS_CATEGORY
95 INTO l_prj_owner_display,
96 l_class_display
97 FROM pa_forecasting_options;
98
99
100 EXCEPTION
101 WHEN NO_DATA_FOUND THEN
102 null;
103
104 END;
105
106
107 pa_fcst_global.Global_Class_category := l_class_display;
108 pa_fcst_global.Global_key_member_id := l_prj_owner_display;
109
110
111 If l_class_display IS NULL THEN
112 x_class_display := 'N';
113 else
114 x_class_display := 'Y';
115 End If;
116
117
118 If l_prj_owner_display IS NULL THEN
119 x_prj_owner_display := 'N';
120 else
121 x_prj_owner_display := 'Y';
122 End If;
123
124 /* NPE Changes Begin - Added displaying error messages for missing period type profile*/
125
126 -- Not needed as the value is alreday coming from pa_fcst_global.Global_period_type
127 /* select fnd_profile.value('PA_FORECASTING_PERIOD_TYPE')
128 into l_period_type
129 from dual; */
130
131
132 If l_period_type is null THEN
133 x_return_status := FND_API.G_RET_STS_ERROR;
134
135 SELECT USER_PROFILE_OPTION_NAME INTO l_user_profile_option_name1
136 FROM fnd_profile_options_tl
137 WHERE profile_option_name='PA_FORECASTING_PERIOD_TYPE'
138 AND language=userenv('LANG');
139
140 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
141 p_msg_name => 'PA_UNDEFINED_PROFILES',
142 p_token1 => 'PROFILES',
143 p_value1 => l_user_profile_option_name1);
144
145 l_msg_count := FND_MSG_PUB.count_msg;
146 if l_msg_count > 0 then
147 if l_msg_count = 1 then
148 PA_INTERFACE_UTILS_PUB.get_messages
149 (p_encoded => FND_API.G_TRUE,
150 p_msg_index => 1,
151 p_msg_count => l_msg_count,
152 p_msg_data => l_msg_data,
153 p_data => l_data,
154 p_msg_index_out => l_msg_index_out);
155 x_msg_data := l_data;
156 x_msg_count := l_msg_count;
157 else
158 x_msg_count := l_msg_count;
159 end if;
160 pa_debug.reset_err_stack;
161 return;
162 end if;
163
164 end if;
165 /* NPE Changes End */
166
167
168 BEGIN
169
170 --Calling procedure to initialize Global_Period_Set_Name;
171 pa_fcst_global.SetPeriodSetName;
172
173 SELECT period_name, start_date, end_date
174 INTO l_start_period, l_start_date, l_end_date
175 FROM pa_fcst_periods_tmp_v
176 WHERE period_type = pa_fcst_global.Global_period_type
177 AND trunc(sysdate) between start_date and end_date
178 AND to_char(period_year) = to_char(sysdate,'YYYY');
179
180
181 x_start_period := l_start_period;
182
183
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186
187 begin
188
189 SELECT period_name, start_date, end_date
190 INTO l_start_period, l_start_date, l_end_date
191 FROM pa_fcst_periods_tmp_v
192 WHERE period_type = pa_fcst_global.Global_period_type
193 and start_date =
194 ( SELECT max(start_date) from pa_fcst_periods_tmp_v
195 WHERE period_type = pa_fcst_global.Global_period_type
196 AND start_date < sysdate
197 );
198
199 x_start_period := l_start_period;
200
201 /* NPE Changes Begin - Added displaying error messages for missing calendar */
202 EXCEPTION
203
204 WHEN NO_DATA_FOUND THEN
205
206 x_return_status := FND_API.G_RET_STS_ERROR;
207 l_default_calendar := FND_PROFILE.VALUE('PA_PRM_DEFAULT_CALENDAR');
208 select count(*) into org_count from pa_implementations;
209
210 IF l_default_calendar is null THEN
211 SELECT USER_PROFILE_OPTION_NAME INTO l_user_profile_option_name2
212 FROM fnd_profile_options_tl
213 WHERE profile_option_name='PA_PRM_DEFAULT_CALENDAR'
214 AND language=userenv('LANG');
215
216 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
217 p_msg_name => 'PA_UNDEFINED_PROFILES',
218 p_token1 => 'PROFILES',
219 p_value1 => l_user_profile_option_name2);
220
221 END IF;
222 IF org_count=0 THEN
223 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
224 p_msg_name => 'PA_INCORRECT_MO_OPERATING_UNIT');
225
226 END IF;
227
228 l_msg_count := FND_MSG_PUB.count_msg;
229 if l_msg_count > 0 then
230 if l_msg_count = 1 then
231 PA_INTERFACE_UTILS_PUB.get_messages
232 (p_encoded => FND_API.G_TRUE,
233 p_msg_index => 1,
234 p_msg_count => l_msg_count,
235 p_msg_data => l_msg_data,
236 p_data => l_data,
237 p_msg_index_out => l_msg_index_out);
238 x_msg_data := l_data;
239 x_msg_count := l_msg_count;
240 else
241 x_msg_count := l_msg_count;
242 end if;
243 pa_debug.reset_err_stack;
244 return;
245 end if;
246 end;
247 /* NPE Changes End */
248 END;
249
250
251
252
253 OPEN C1(l_start_date);
254
255 LOOP
256
257 FETCH C1
258 INTO l_csr_end_date;
259
260 EXIT WHEN C1%NOTFOUND;
261
262 EXIT WHEN l_period_type = 'PA' and C1%ROWCOUNT = 13;
263
264 EXIT WHEN l_period_type = 'GL' and C1%ROWCOUNT = 6;
265
266
267
268 END LOOP;
269
270 CLOSE C1;
271
272
273 pa_fcst_global.Global_proj_fcst_start_date := l_start_date;
274 pa_fcst_global.Global_proj_fcst_end_date := l_csr_end_date;
275
276
277 EXCEPTION
278 WHEN OTHERS THEN
279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
280 x_msg_count := 1;
281 x_msg_data := SQLERRM;
282 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FCST_GLOBAL',
283 p_procedure_name => 'GetDefaultValue');
284
285 END GetDefaultValue;
286
287 procedure pa_fcst_proj_get_default(p_project_id IN NUMBER,
288 x_show_amount_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
289 x_start_period_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
290 x_apply_prob_per_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
291 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
292 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
293 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
294 IS
295
296 l_start_date DATE;
297 l_end_date DATE;
298 l_period_type VARCHAR2(30);
299 l_project_type_class VARCHAR2(30);
300 l_init_msg_list VARCHAR2(20) := FND_API.G_TRUE;
301 /* NPE Changes Begin*/
302 l_msg_count NUMBER := 0;
303 l_data VARCHAR2(2000);
304 l_msg_data VARCHAR2(2000);
305 l_msg_index_out NUMBER;
306 l_return_status VARCHAR2(2000);
307 l_user_profile_option_name1 varchar2(1000);
308 l_user_profile_option_name2 varchar2(1000);
309 org_count NUMBER;
310 l_default_calendar VARCHAR2(240);
311 /* NPE Changes End */
312
313 BEGIN
314
315 x_return_status := FND_API.G_RET_STS_SUCCESS;
316
317
318 --Clear the global PL/SQL message table
319
320 IF FND_API.TO_BOOLEAN( l_init_msg_list ) THEN
321 FND_MSG_PUB.initialize;
322 END IF;
323
324 BEGIN
325 /* NPE Changes - The MO: Operating Unit should be checked here .
326 I am not sure that why we are taking _all tables
327 here. If we can put a join with pa_implementations then
328 in NO_data_found, we can raise message for missing Mo: operating Unit.
329 Since we are not sure, so keeping the logic to check for Mo operating unit
330 later in the code*/
331
332 SELECT pr2.project_type_class_code
333 INTO l_project_type_class
334 FROM pa_projects_all pr1,
335 pa_project_types_all pr2
336 WHERE pr1.project_id = p_project_id
337 AND pr2.project_type = pr1.project_type
338 AND nvl(pr1.org_id,-99)=nvl(pr2.org_id,-99);
339
340 EXCEPTION
341 WHEN NO_DATA_FOUND THEN
342 NULL;
343
344 END;
345
346 pa_fcst_global.Global_project_type_class := l_project_type_class;
347
348 /* Set the Global variable for Project Id */
349
350 pa_fcst_global.Global_ProjectId := p_project_id;
351
352 /* Initializing Global variables */
353 IF pa_fcst_global.Global_project_type_class = 'CONTRACT' THEN
354 Global_proj_fcst_show_amt := 'REVENUE';
355 ELSE
356 Global_proj_fcst_show_amt := 'COST';
357 END IF;
358
359 Global_ProbabilityPerFlag := 'N';
360
361 /* Assigning Global value into output variables */
362
363 x_show_amount_type := pa_fcst_global.Global_proj_fcst_show_amt;
364 x_apply_prob_per_flag := pa_fcst_global.Global_ProbabilityPerFlag;
365 l_period_type := pa_fcst_global.global_period_type;
366
367 /* NPE Changes Begin - Added displaying error messages for missing period type profile*/
368 /*
369 select fnd_profile.value('PA_FORECASTING_PERIOD_TYPE')
370 into l_period_type
371 from dual;
372 */
373 If l_period_type is null THEN
374 x_return_status := FND_API.G_RET_STS_ERROR;
375
376 SELECT USER_PROFILE_OPTION_NAME INTO l_user_profile_option_name1
377 FROM fnd_profile_options_tl
378 WHERE profile_option_name='PA_FORECASTING_PERIOD_TYPE'
379 AND language=userenv('LANG');
380
381 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
382 p_msg_name => 'PA_UNDEFINED_PROFILES',
383 p_token1 => 'PROFILES',
384 p_value1 => l_user_profile_option_name1);
385
386 l_msg_count := FND_MSG_PUB.count_msg;
387 if l_msg_count > 0 then
388 if l_msg_count = 1 then
389 PA_INTERFACE_UTILS_PUB.get_messages
390 (p_encoded => FND_API.G_TRUE,
391 p_msg_index => 1,
392 p_msg_count => l_msg_count,
393 p_msg_data => l_msg_data,
394 p_data => l_data,
395 p_msg_index_out => l_msg_index_out);
396 x_msg_data := l_data;
397 x_msg_count := l_msg_count;
398 else
399 x_msg_count := l_msg_count;
400 end if;
401 pa_debug.reset_err_stack;
402 return;
403 end if;
404
405 end if;
406 /* NPE Changes End */
407
408 BEGIN
409
410
411 SELECT period_name
412 INTO x_start_period_name
413 FROM pa_fcst_periods_tmp_v
414 WHERE period_type = l_period_type
415 AND trunc(sysdate) between start_date and end_date
416 AND to_char(period_year) = to_char(sysdate,'YYYY');
417
418 EXCEPTION
419 WHEN NO_DATA_FOUND THEN
420
421 begin
422
423 SELECT period_name
424 INTO x_start_period_name
425 FROM pa_fcst_periods_tmp_v
426 WHERE period_type = pa_fcst_global.Global_period_type
427 and start_date =
428 ( SELECT max(start_date) from pa_fcst_periods_tmp_v
429 WHERE period_type = pa_fcst_global.Global_period_type
430 AND start_date < sysdate
431 );
432 /* NPE Changes Begin - Added displaying error messages for missing calendar */
433 EXCEPTION
434
435 WHEN NO_DATA_FOUND THEN
436
437 x_return_status := FND_API.G_RET_STS_ERROR;
438 l_default_calendar := FND_PROFILE.VALUE('PA_PRM_DEFAULT_CALENDAR');
439 select count(*) into org_count from pa_implementations;
440
441 IF l_default_calendar is null THEN
442 SELECT USER_PROFILE_OPTION_NAME INTO l_user_profile_option_name2
443 FROM fnd_profile_options_tl
444 WHERE profile_option_name='PA_PRM_DEFAULT_CALENDAR'
445 AND language=userenv('LANG');
446
447 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
448 p_msg_name => 'PA_UNDEFINED_PROFILES',
449 p_token1 => 'PROFILES',
450 p_value1 => l_user_profile_option_name2);
451 END IF;
452 IF org_count=0 THEN
453 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
454 p_msg_name => 'PA_INCORRECT_MO_OPERATING_UNIT');
455
456 END IF;
457
458 l_msg_count := FND_MSG_PUB.count_msg;
459 if l_msg_count > 0 then
460 if l_msg_count = 1 then
461 PA_INTERFACE_UTILS_PUB.get_messages
462 (p_encoded => FND_API.G_TRUE,
463 p_msg_index => 1,
464 p_msg_count => l_msg_count,
465 p_msg_data => l_msg_data,
466 p_data => l_data,
467 p_msg_index_out => l_msg_index_out);
468 x_msg_data := l_data;
469 x_msg_count := l_msg_count;
470 else
471 x_msg_count := l_msg_count;
472 end if;
473 pa_debug.reset_err_stack;
474 return;
475 end if;
476
477 end;
478 /* NPE Changes End */
479 END;
480
481 EXCEPTION
482 WHEN OTHERS THEN
483 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
484 x_msg_count := 1;
485 x_msg_data := SQLERRM;
486 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FCST_GLOBAL',
487 p_procedure_name => 'pa_fcst_proj_get_default');
488
489 END pa_fcst_proj_get_default;
490
491 PROCEDURE Set_CrossProject_GlobalValue(p_start_period IN VARCHAR2,
492 p_Show_amount IN VARCHAR2,
493 p_apply_prob_flag IN VARCHAR2,
494 p_page_first_flag IN VARCHAR2,
495 p_project_number IN VARCHAR2,
496 p_project_name IN VARCHAR2,
497 p_project_type IN VARCHAR2,
498 p_organization_name IN VARCHAR2,
499 p_project_status IN VARCHAR2,
500 p_project_manager_name IN VARCHAR2,
501 p_project_customer_name IN VARCHAR2,
502 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
503 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
504 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
505 )
506
507 IS
508
509 l_period_name VARCHAR2(30);
510 l_start_date DATE;
511 l_end_date DATE;
512 l_period_type VARCHAR2(30);
513 l_org_id NUMBER(15);
514
515 l_csr_end_date DATE;
516
517 CURSOR C1(l_period_type VARCHAR2,l_start_date DATE) IS
518 -- Bug 4874283 - perf changes - remove trunc so index U2 is used
519 SELECT end_date
520 FROM pa_fcst_periods_tmp_v
521 WHERE period_type = l_period_type
522 -- AND trunc(start_date) >= trunc(l_start_date) -- 4874283
523 AND start_date >= trunc(l_start_date) -- 4874283
524 order by start_date;
525
526
527 l_init_msg_list VARCHAR2(20) := FND_API.G_TRUE;
528
529 BEGIN
530
531 x_return_status := FND_API.G_RET_STS_SUCCESS;
532
533 --Clear the global PL/SQL message table
534
535 IF FND_API.TO_BOOLEAN( l_init_msg_list ) THEN
536 FND_MSG_PUB.initialize;
537 END IF;
538 /* Populate the Period Temp Table */
539 Populate_Fcst_Periods;
540
541 pa_fcst_global.Global_Page_First_Flag := p_page_first_flag;
542
543 IF p_show_amount <> 'X' THEN
544 pa_fcst_global.Global_proj_fcst_show_amt := p_show_amount;
545 END IF;
546
547 IF p_apply_prob_flag <> 'X' THEN
548 pa_fcst_global.Global_ProbabilityPerFlag := p_apply_prob_flag;
549 END IF;
550
551 IF p_project_number <> 'X' THEN
552 pa_fcst_global.Global_Project_Number := p_project_number;
553 ELSE pa_fcst_global.Global_Project_Number :='ALL';
554 END IF;
555
556 IF p_project_name <> 'X' THEN
557 pa_fcst_global.Global_Project_Name := p_project_name;
558 ELSE pa_fcst_global.Global_Project_Name :='XXXXXXXXXXXXXXX';
559 END IF;
560
561 IF p_project_type <> 'X' THEN
562 pa_fcst_global.Global_project_type := p_project_type;
563 ELSE pa_fcst_global.Global_project_type := 'ALL';
564 END IF;
565
566 IF p_organization_name <> 'X' THEN
567 pa_fcst_global.Global_Orgnization_Name := p_organization_name;
568
569 select organization_id
570 into l_org_id
571 from hr_all_organization_units_tl
572 where name = p_organization_name
573 AND language = userenv('LANG');
574 pa_fcst_global.Global_Orgnization_Id :=l_org_id;
575
576 ELSE pa_fcst_global.Global_Orgnization_Name :='ALL';
577 END IF;
578
579 IF p_project_status <> 'X' THEN
580 pa_fcst_global.Global_project_status := p_project_status;
581 ELSE pa_fcst_global.Global_project_status := 'ALL';
582 END IF;
583
584 IF p_project_manager_name <> 'X' THEN
585 pa_fcst_global.GLobal_Project_Manager_Name := p_project_manager_name;
586 ELSE pa_fcst_global.GLobal_Project_Manager_Name := 'XXXXXXXXXXXXXXX';
587 END IF;
588
589 IF p_project_customer_name <> 'X' THEN
590 pa_fcst_global.GLobal_Project_Customer_Name := p_project_customer_name;
591 ELSE pa_fcst_global.GLobal_Project_Customer_Name := 'XXXXXXXXXXXXXXX';
592 END IF;
593
594 l_period_type := pa_fcst_global.Global_period_type;
595
596 BEGIN
597
598 SELECT
599 Start_Date,
600 End_Date
601 INTO
602 l_start_date,
603 l_end_date
604 FROM pa_fcst_periods_tmp_v
605 WHERE period_name = p_start_period
606 AND period_type = l_period_type;
607
608
609 EXCEPTION
610 WHEN NO_DATA_FOUND THEN
611 null;
612
613 END;
614
615 OPEN C1(l_period_type, l_start_date);
616
617 LOOP
618
619 FETCH C1
620 INTO l_csr_end_date;
621
622 EXIT WHEN C1%NOTFOUND;
623
624 EXIT WHEN l_period_type = 'PA' and C1%ROWCOUNT = 13;
625
626 EXIT WHEN l_period_type = 'GL' and C1%ROWCOUNT = 6;
627
628 END LOOP;
629
630 CLOSE C1;
631
632
633 pa_fcst_global.Global_proj_fcst_start_date := l_start_date;
634 pa_fcst_global.Global_proj_fcst_end_date := l_csr_end_date;
635
636
637 EXCEPTION
638 WHEN OTHERS THEN
639 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
640 x_msg_count := 1;
641 x_msg_data := SQLERRM;
642 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FCST_GLOBAL',
643 p_procedure_name => 'Set_CrossProject_GlobalValue');
644
645 END Set_CrossProject_GlobalValue;
646
647 PROCEDURE Set_Project_GlobalValue(p_project_id IN NUMBER,
648 p_start_period IN VARCHAR2,
649 p_show_amount IN VARCHAR2,
650 p_apply_prob_flag IN VARCHAR2,
651 p_apply_prob_per IN NUMBER,
652 x_project_type_class OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
653 x_project_TM_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
654 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
655 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
656 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
657 )
658 IS
659
660 l_period_name VARCHAR2(30);
661 l_start_date DATE;
662 l_end_date DATE;
663 l_period_type VARCHAR2(30);
664 l_csr_end_date DATE;
665 ll_start_date DATE; -- Added for bug# 3620818
666 ll_end_date DATE;
667 l_pl_start_date DATE;
668 l_pl_end_date DATE;
669 l_project_type_class VARCHAR2(30);
670 x_rev_gen_method VARCHAR2(1);
671 x_error_msg VARCHAR2(1);
672
673 CURSOR C1(l_period_type VARCHAR2,l_start_date DATE) IS
674 -- Bug 4874283 - perf changes - remove trunc so index U2 is used
675 SELECT end_date
676 FROM pa_fcst_periods_tmp_v
677 WHERE period_type = l_period_type
678 -- AND trunc(start_date) >= trunc(l_start_date) -- 4874283
679 AND start_date >= trunc(l_start_date) -- 4874283
680 order by start_date;
681
682 l_init_msg_list VARCHAR2(20) := FND_API.G_TRUE;
683
684 BEGIN
685
686 x_return_status := FND_API.G_RET_STS_SUCCESS;
687
688 --Clear the global PL/SQL message table
689
690 IF FND_API.TO_BOOLEAN( l_init_msg_list ) THEN
691 FND_MSG_PUB.initialize;
692 END IF;
693
694
695 pa_fcst_global.Global_ProjectId := p_project_id;
696 pa_fcst_global.Global_proj_fcst_show_amt := p_show_amount;
697 pa_fcst_global.Global_ProbabilityPerFlag := p_apply_prob_flag;
698 pa_fcst_global.Global_ProbabilityPer := p_apply_prob_per;
699 l_period_type := pa_fcst_global.global_period_type;
700
701
702 SELECT min(start_date), -- Added for bug# 3620818
703 max(end_date)
704 INTO ll_start_date, -- Added for bug# 3620818
705 ll_end_date
706 FROM pa_project_assignments
707 WHERE project_id = p_project_id;
708
709 IF l_period_type = 'GL' THEN
710
711 SELECT min(p1.start_date),
712 max(p1.end_date)
713 INTO l_pl_start_date,
714 l_pl_end_date
715 FROM pa_fcst_periods_tmp_v p1,
716 pa_projects_all p2
717 WHERE p1.period_type = 'GL'
718 /* Commented the AND condition and modified for bug #3620818
719 AND p1.start_date between p2.start_date and
720 NVL(p2.completion_date, ll_end_date) */
721 AND ( p1.start_date between nvl(p2.start_date, ll_start_date) and
722 NVL(p2.completion_date, ll_end_date)
723 OR
724 nvl(p2.start_date, ll_start_date) between p1.start_date and p1.end_date )
725 AND p2.project_id = p_project_id;
726
727 ELSIF l_period_type = 'PA' THEN
728
729 SELECT min(start_date),
730 max(end_date)
731 INTO l_pl_start_date,
732 l_pl_end_date
733 FROM pa_fcst_periods_tmp_v
734 WHERE period_type = 'PA'
735 AND (to_char(period_year) = to_char(sysdate,'YYYY')
736 OR start_date between add_months(sysdate,-3) and
737 add_months(sysdate,6));
738
739 END IF;
740
741 pa_fcst_global.Global_pl_start_date := l_pl_start_date;
742 pa_fcst_global.Global_pl_end_date := l_pl_end_date;
743
744 SELECT pr2.project_type_class_code
745 INTO l_project_type_class
746 FROM pa_projects_all pr1,
747 pa_project_types_all pr2
748 WHERE pr1.project_id = p_project_id
749 AND pr2.project_type = pr1.project_type
750 AND nvl(pr1.org_id,-99)=nvl(pr2.org_id,-99);
751
752 pa_fcst_global.Global_project_type_class := l_project_type_class;
753 x_project_type_class := l_project_type_class;
754
755 BEGIN
756 PA_RATE_PVT_PKG.get_revenue_generation_method(p_project_id=>p_project_id ,
757 x_rev_gen_method =>x_rev_gen_method,
758 x_error_msg =>x_error_msg);
759
760
761
762 IF x_rev_gen_method = 'T' THEN
763 x_project_TM_flag :='Y';
764 ELSE
765 x_project_TM_flag :='N';
766 END IF;
767
768
769 EXCEPTION
770 WHEN OTHERS THEN
771 x_project_TM_flag := 'N';
772 END;
773
774 SELECT
775 Start_Date,
776 End_Date
777 INTO
778 l_start_date,
779 l_end_date
780 FROM pa_fcst_periods_tmp_v
781 WHERE period_name = p_start_period
782 AND period_type = l_period_type;
783
784 OPEN C1(l_period_type, l_start_date);
785
786 LOOP
787
788 FETCH C1
789 INTO l_csr_end_date;
790
791 EXIT WHEN C1%NOTFOUND;
792
793
794 EXIT WHEN l_period_type= 'PA' and C1%ROWCOUNT = 13;
795
796
797 EXIT WHEN l_period_type= 'GL' and C1%ROWCOUNT = 6;
798
799
800 END LOOP;
801
802 CLOSE C1;
803
804 pa_fcst_global.Global_proj_fcst_start_date := l_start_date;
805 pa_fcst_global.Global_proj_fcst_end_date := l_csr_end_date;
806
807
808 EXCEPTION
809 WHEN OTHERS THEN
810 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
811 x_msg_count := 1;
812 x_msg_data := SQLERRM;
813 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FCST_GLOBAL',
814 p_procedure_name => 'Set_Project_GlobalValue');
815
816 END Set_Project_GlobalValue;
817
818 PROCEDURE SetPeriodSetName
819 IS
820
821 -- R12 MOAC changes and bug 4874283 perf fix.
822 -- See previous version for old cursor definition - I have deleted
823 -- it so that it doesn't show up in grep for impact.
824
825 CURSOR cur_period_set_name
826 IS
827 SELECT sob.period_set_name
828 FROM gl_sets_of_books sob,
829 pa_implementations_all pia
830 WHERE pia.set_of_books_id = sob.set_of_books_id
831 AND ((mo_global.get_current_org_id is NULL AND -- 4874283
832 mo_global.check_access(pia.org_id) = 'Y') -- 4874283
833 OR -- 4874283
834 (mo_global.get_current_org_id is NOT NULL AND -- 4874283
835 pia.org_id = mo_global.get_current_org_id)); -- 4874283
836
837
838 BEGIN
839 OPEN cur_period_set_name;
840 FETCH cur_period_set_name INTO pa_fcst_global.Global_Period_Set_Name;
841 CLOSE cur_period_set_name;
842 END SetPeriodSetName;
843
844
845 PROCEDURE Set_Global_Project_Id(p_project_id IN NUMBER,
846 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
847 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
848 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
849 )
850 IS
851
852 BEGIN
853
854 pa_fcst_global.Global_ProjectId := p_project_id;
855
856 END Set_Global_Project_Id;
857
858
859
860 PROCEDURE Get_Project_Info(p_project_id IN NUMBER,
861 x_project_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
862 x_project_number OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
863 x_FI_Date OUT NOCOPY Date, --File.Sql.39 bug 4440895
864 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
865 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
866 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
867 )
868 IS
869
870 BEGIN
871
872 x_return_status := FND_API.G_RET_STS_SUCCESS;
873
874 select name, segment1
875 into x_project_name, x_project_number
876 from pa_projects_all
877 where project_id = p_project_id;
878
879 select plan_run_date
880 into x_FI_Date
881 from pa_budget_versions
882 where project_id = p_project_id
883 and budget_type_code = 'FORECASTING_BUDGET_TYPE';
884
885 EXCEPTION
886 WHEN OTHERS THEN
887 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
888 x_msg_count := 1;
889 x_msg_data := SQLERRM;
890 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FCST_GLOBAL',
891 p_procedure_name => 'Get_Project_Info');
892
893 END Get_Project_Info;
894
895 FUNCTION GetPeriodSetName RETURN VARCHAR2 IS
896 BEGIN
897 RETURN( pa_fcst_global.Global_Period_Set_Name );
898 END GetPeriodSetName;
899
900
901 FUNCTION GetProjFcstShowAmount RETURN VARCHAR2 IS
902 BEGIN
903 RETURN (pa_fcst_global.global_proj_fcst_show_amt);
904 END GetProjFcstShowAmount;
905
906
907 FUNCTION GetProjectId RETURN NUMBER IS
908 BEGIN
909 RETURN (pa_fcst_global.Global_ProjectId);
910 END GetProjectId;
911
912 FUNCTION GetProjFcstStartDate RETURN DATE IS
913 BEGIN
914 RETURN (global_proj_fcst_start_date);
915 END GetProjFcstStartDate;
916
917 FUNCTION GetProjFcstEndDate RETURN DATE IS
918 BEGIN
919 RETURN (global_proj_fcst_end_date);
920 END GetProjFcstEndDate;
921
922 FUNCTION GetProbabilityPerFlag RETURN VARCHAR2 IS
923 BEGIN
924 RETURN (Global_ProbabilityPerFlag);
925 END GetProbabilityPerFlag;
926
927 FUNCTION GetProbabilityPer RETURN NUMBER IS
928 BEGIN
929 RETURN (Global_ProbabilityPer);
930 END GetProbabilityPer;
931
932 FUNCTION GetPeriodType RETURN VARCHAR2 IS
933 BEGIN
934 RETURN Global_period_type;
935 END GetPeriodType;
936
937
938 FUNCTION GetProjType RETURN VARCHAR2
939 IS
940
941 BEGIN
942
943 RETURN pa_fcst_global.Global_project_type;
944
945 END GetProjType;
946
947
948
949 FUNCTION GetProjStatusCode RETURN VARCHAR2
950 IS
951
952 BEGIN
953
954 RETURN pa_fcst_global.Global_project_status;
955
956 END GetProjStatusCode;
957
958 FUNCTION GetPageFirstFlag RETURN VARCHAR2
959 IS
960 BEGIN
961 RETURN pa_fcst_global.Global_Page_First_Flag;
962 END GetPageFirstFlag;
963
964 FUNCTION GetProjectNumber RETURN VARCHAR2
965 IS
966 BEGIN
967 RETURN pa_fcst_global.Global_Project_Number;
968 END GetProjectNumber;
969
970 FUNCTION GetProjectName RETURN VARCHAR2
971 IS
972 BEGIN
973 RETURN pa_fcst_global.Global_Project_Name;
974 END GetProjectName;
975
976 FUNCTION GetProjectOrgId RETURN NUMBER
977 IS
978 BEGIN
979 RETURN pa_fcst_global.Global_Orgnization_Id;
980 END GetProjectOrgId;
981
982 FUNCTION GetProjectOrgName RETURN VARCHAR2
983 IS
984 BEGIN
985 RETURN pa_fcst_global.Global_Orgnization_Name;
986 END GetProjectOrgName;
987
988 FUNCTION GetProjectStartDate RETURN DATE
989 IS
990 BEGIN
991 RETURN pa_fcst_global.Global_Project_Start_Date;
992 END GetProjectStartDate;
993
994
995 FUNCTION GetProjectStartDateOpt RETURN VARCHAR2
996 IS
997 BEGIN
998 RETURN pa_fcst_global.Global_Project_Start_Date_Opt;
999 END GetProjectStartDateOpt;
1000
1001 FUNCTION GetProjectCompDate RETURN DATE
1002 IS
1003 BEGIN
1004 RETURN pa_fcst_global.Global_Project_Comp_Date;
1005 END GetProjectCompDate;
1006
1007 FUNCTION GetProjectCompDateOpt RETURN VARCHAR2
1008 IS
1009 BEGIN
1010 RETURN pa_fcst_global.Global_Project_Comp_Date_Opt;
1011 END GetProjectCompDateOpt;
1012
1013 FUNCTION GetProjectMangerName RETURN VARCHAR2
1014 IS
1015 BEGIN
1016 RETURN pa_fcst_global.GLobal_Project_Manager_Name;
1017 END GetProjectMangerName;
1018
1019 FUNCTION GetProjectMangerId RETURN NUMBER
1020 IS
1021 BEGIN
1022 RETURN pa_fcst_global.GLobal_Project_Manager_Id;
1023 END GetProjectMangerId;
1024
1025 FUNCTION GetProjectCustomerName RETURN VARCHAR2
1026 IS
1027 BEGIN
1028 RETURN pa_fcst_global.GLobal_Project_Customer_Name;
1029 END GetProjectCustomerName;
1030
1031 FUNCTION GetClassCatgory RETURN VARCHAR2
1032 IS
1033
1034 BEGIN
1035
1036 RETURN pa_fcst_global.Global_Class_category;
1037
1038 END GetClassCatgory;
1039
1040
1041 FUNCTION GetKeyMemberId RETURN VARCHAR2
1042 IS
1043
1044 BEGIN
1045
1046 RETURN pa_fcst_global.Global_key_member_id;
1047
1048 END GetKeyMemberId;
1049
1050 FUNCTION GetPlStartDate RETURN DATE
1051 IS
1052 BEGIN
1053 RETURN pa_fcst_global.Global_pl_start_date;
1054 END GetPlStartDate;
1055
1056 FUNCTION GetPlEndDate RETURN DATE
1057 IS
1058 BEGIN
1059 RETURN pa_fcst_global.Global_pl_end_date;
1060 END GetPlEndDate;
1061
1062 FUNCTION GetProjectTypeClass RETURN VARCHAR2
1063 IS
1064 BEGIN
1065 RETURN pa_fcst_global.Global_project_type_class;
1066 END GetProjectTypeClass;
1067
1068 FUNCTION find_project_owner(
1069 p_project_id IN NUMBER,
1070 p_proj_start_date IN DATE,
1071 p_proj_end_date IN DATE
1072 )
1073 RETURN VARCHAR2
1074 IS
1075
1076 CURSOR csr_prj_owner IS
1077 SELECT resd.resource_name
1078 FROM pa_resources_denorm resd,
1079 pa_project_parties prjp
1080 WHERE resd.person_id = prjp.resource_source_id
1081 AND prjp.project_id = p_project_id
1082 AND prjp.project_role_id = pa_fcst_global.GetKeyMemberId
1083 AND (sysdate between resd.RESOURCE_EFFECTIVE_START_DATE and resd.RESOURCE_EFFECTIVE_END_DATE
1084 OR (p_proj_start_date between resd.RESOURCE_EFFECTIVE_START_DATE and resd.RESOURCE_EFFECTIVE_END_DATE
1085 OR p_proj_end_date between resd.RESOURCE_EFFECTIVE_START_DATE and resd.RESOURCE_EFFECTIVE_END_DATE))
1086 order by resd.resource_name;
1087
1088
1089 l_project_owner pa_resources_denorm.resource_name%TYPE;
1090
1091
1092 BEGIN
1093
1094 OPEN csr_prj_owner;
1095
1096 LOOP
1097
1098 FETCH csr_prj_owner
1099 INTO l_project_owner;
1100
1101 EXIT;
1102
1103
1104 END LOOP;
1105
1106
1107 CLOSE csr_prj_owner;
1108
1109
1110 RETURN l_project_owner;
1111
1112 EXCEPTION
1113 WHEN NO_DATA_FOUND THEN
1114 RETURN NULL;
1115
1116 END find_project_owner;
1117
1118 FUNCTION find_project_fixed_price(p_project_id IN NUMBER) RETURN VARCHAR2 IS
1119 fixed_price_flag VARCHAR2(1);
1120 x_rev_gen_method VARCHAR2(1);
1121 x_error_msg VARCHAR2(1);
1122
1123 BEGIN
1124 BEGIN
1125 PA_RATE_PVT_PKG.get_revenue_generation_method(p_project_id=>p_project_id ,
1126 x_rev_gen_method =>x_rev_gen_method,
1127 x_error_msg =>x_error_msg);
1128
1129 IF (x_rev_gen_method = 'E' OR x_rev_gen_method = 'C') THEN
1130 fixed_price_flag :='Y';
1131 ELSE
1132 fixed_price_flag :='N';
1133 END IF;
1134
1135 EXCEPTION
1136 WHEN OTHERS THEN
1137 fixed_price_flag := 'N';
1138 END;
1139
1140 RETURN (fixed_price_flag);
1141
1142 END find_project_fixed_price;
1143
1144 FUNCTION SetCrossProjectViewUser RETURN VARCHAR2 IS
1145 l_cross_view_user VARCHAR2(1) :='N';
1146 l_resp_id NUMBER;
1147 l_resp_appl_id NUMBER;
1148 l_user_id NUMBER;
1149 l_person_id NUMBER;
1150 BEGIN
1151
1152 l_user_id := FND_GLOBAL.USER_ID;
1153 l_person_id := pa_utils.GetEmpIdFromUser( l_user_id );
1154 l_resp_id := fnd_global.resp_id;
1155 l_resp_appl_id := fnd_global.resp_appl_id;
1156
1157 IF fnd_profile.value_specific('PA_SUPER_PROJECT',l_user_id,
1158 l_resp_id, l_resp_appl_id) = 'Y' THEN
1159 l_cross_view_user := 'Y';
1160 ELSE
1161 l_cross_view_user := 'N';
1162 END IF;
1163
1164 IF l_cross_view_user = 'N' THEN
1165
1166 IF fnd_profile.value_specific('PA_SUPER_PROJECT_VIEW',l_user_id,
1167 l_resp_id, l_resp_appl_id) = 'Y' THEN
1168 l_cross_view_user := 'Y';
1169 END IF;
1170 END IF;
1171
1172 RETURN l_cross_view_user;
1173
1174 END SetCrossProjectViewUser;
1175
1176 FUNCTION IsCrossProjectViewUser RETURN VARCHAR2 IS
1177 BEGIN
1178 RETURN (Global_CrossProjectViewUser);
1179 END IsCrossProjectViewUser;
1180
1181 Procedure Populate_Fcst_Periods IS
1182
1183 l_period_type VARCHAR2(2):= FND_PROFILE.VALUE('PA_FORECASTING_PERIOD_TYPE');
1184
1185 BEGIN
1186 BEGIN
1187 DELETE pa_fcst_periods_tmp;
1188 EXCEPTION
1189 WHEN NO_DATA_FOUND THEN
1190 NULL;
1191 WHEN OTHERS THEN
1192 raise;
1193
1194 END;
1195
1196 IF l_period_type ='GL' THEN
1197 INSERT INTO pa_fcst_periods_tmp
1198 (PERIOD_NAME,
1199 START_DATE,
1200 END_DATE)
1201 SELECT distinct
1202 glper.period_name,
1203 glper.start_date,
1204 glper.end_date
1205 FROM pa_implementations imp,
1206 gl_sets_of_books gl,
1207 gl_periods glper,
1208 gl_period_statuses glpersts,
1209 gl_lookups prsts,
1210 gl_date_period_map glmaps
1211 WHERE imp.set_of_books_id = gl.set_of_books_id
1212 AND gl.period_set_name = glper.period_set_name
1213 AND gl.accounted_period_type = glper.period_type
1214 AND glpersts.set_of_books_id = gl.set_of_books_id
1215 AND glpersts.period_type = glper.period_type
1216 AND glpersts.period_name = glper.period_name
1217 AND glpersts.period_year = glper.period_year
1218 AND glpersts.closing_status = prsts.lookup_code
1219 AND glmaps.period_type = glper.period_type
1220 AND glmaps.period_name = glper.period_name
1221 AND glmaps.period_set_name = glper.period_set_name
1222 AND glpersts.application_id = Pa_Period_Process_Pkg.Application_Id
1223 AND prsts.lookup_code IN('C','F','N','O','P')
1224 AND prsts.lookup_type ='CLOSING_STATUS';
1225 ELSIF l_period_type ='PA' THEN
1226 -- R12 MOAC changes and bug 4874283 perf fix.
1227 -- See previous version for old insert statement - I have deleted
1228 -- it so that it doesn't show up in grep for impact.
1229
1230 INSERT INTO pa_fcst_periods_tmp
1231 (PERIOD_NAME,
1232 START_DATE,
1233 END_DATE)
1234 SELECT PER.PERIOD_NAME,
1235 PER.START_DATE,
1236 PER.END_DATE
1237 FROM PA_PERIODS_ALL PER
1238 WHERE ((mo_global.get_current_org_id is NULL AND -- 4874283
1239 mo_global.check_access(per.org_id) = 'Y') -- 4874283
1240 OR -- 4874283
1241 (mo_global.get_current_org_id is NOT NULL AND -- 4874283
1242 per.org_id = mo_global.get_current_org_id)); -- 4874283
1243 END IF;
1244 END Populate_Fcst_Periods;
1245 BEGIN
1246 Global_CrossProjectViewUser := SetCrossProjectViewUser;
1247 END pa_fcst_global;