[Home] [Help]
PACKAGE BODY: APPS.FPA_SCENARIO_PVT
Source
1 PACKAGE BODY fpa_scenario_pvt AS
2 /* $Header: FPAVSCEB.pls 120.6 2011/08/03 22:09:00 skkoppul ship $ */
3
4 -- A global variable to determine if a procedure
5 -- should attach, update/commit and detach the AW
6 -- independently.
7 g_attach_aw BOOLEAN := true;
8
9 PROCEDURE create_scenario
10 (
11 p_api_version IN NUMBER,
12 p_scenario_name IN VARCHAR2,
13 p_scenario_desc IN VARCHAR2,
14 p_pc_id IN NUMBER,
15 x_scenario_id OUT NOCOPY NUMBER,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2
19 )
20 IS
21
22 l_api_version CONSTANT NUMBER := 1.0;
23
24 CURSOR scenario_s_c
25 IS
26 SELECT
27 fpa_scenario_s.nextval AS scenario_id
28 FROM
29 dual;
30
31 -- A cursor to hold language code
32 CURSOR l_language_csr
33 IS
34 SELECT language_code
35 FROM fnd_languages
36 WHERE installed_flag IN ('I','B');
37
38 -- A variable to hold language
39 l_language varchar2(4);
40
41
42 BEGIN
43
44 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
45 fnd_log.string
46 (
47 FND_LOG.LEVEL_PROCEDURE,
48 'fpa.sql.fpa_scenario_pvt.create_scenario.begin',
49 'Entering fpa_scenario_pvt.create_scenario'
50 );
51 END IF;
52
53 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
54 fnd_log.string
55 (
56 FND_LOG.LEVEL_PROCEDURE,
57 'fpa.sql.fpa_scenario_pvt.create_scenario',
58 'Getting next sequence value for scenario id.'
59 );
60 END IF;
61
62 -- Get the next sequence value for the scenario identifier
63 OPEN scenario_s_c;
64 FETCH scenario_s_c INTO x_scenario_id;
65 CLOSE scenario_s_c;
66
67 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
68 fnd_log.string
69 (
70 FND_LOG.LEVEL_PROCEDURE,
71 'fpa.sql.fpa_scenario_pvt.create_scenario',
72 'Maintaing scenario dimension with new scenario id.'
73 );
74 END IF;
75
76 -- Add the new scenario to the dimension
77 dbms_aw.execute('MAINTAIN scenario_d ADD ' || x_scenario_id);
78
79
80 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
81 fnd_log.string
82 (
83 FND_LOG.LEVEL_PROCEDURE,
84 'fpa.sql.fpa_scenario_pvt.create_scenario',
85 'Setting PC relation for new scenario.'
86 );
87 END IF;
88
89 dbms_aw.execute('PUSH scenario_d');
90 dbms_aw.execute('LMT scenario_d TO ' || x_scenario_id);
91 -- Associate the scenario with the planning cycle
92 dbms_aw.execute('planning_cycle_scenario_r = ' || p_pc_id);
93 dbms_aw.execute('POP scenario_d');
94
95 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
96 fnd_log.string
97 (
98 FND_LOG.LEVEL_PROCEDURE,
99 'fpa.sql.fpa_scenario_pvt.create_scenario',
100 'Updating FPA_OBJECTS_TL with scenario information name not null.'
101 );
102 END IF;
103
104 if (p_scenario_name is not null) then
105
106 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
107 fnd_log.string
108 (
109 FND_LOG.LEVEL_PROCEDURE,
110 'fpa.sql.fpa_scenario_pvt.create_scenario',
111 'Scenario name is not null, Updating FPA_OBJECTS_TL with scenario information name not null.'
112 );
113 END IF;
114
115 -- Open the cursor and insert scenario, name and description for each language code
116 OPEN l_language_csr;
117 LOOP
118 FETCH l_language_csr INTO l_language;
119 EXIT WHEN l_language_csr%NOTFOUND;
120 INSERT INTO FPA_OBJECTS_TL( object
121 ,id
122 ,name
123 ,description
124 ,LANGUAGE
125 ,SOURCE_LANG
126 ,created_by
127 ,creation_date
128 ,last_updated_by
129 ,last_update_date
130 ,last_update_login)
131 VALUES( 'SCENARIO'
132 ,x_scenario_id
133 ,p_scenario_name
134 ,p_scenario_desc
135 ,l_language
136 ,USERENV('LANG')
137 ,0
138 ,sysdate()
139 ,0
140 ,sysdate()
141 ,0);
142
143 END LOOP;
144 CLOSE l_language_csr;
145
146 end if;
147
148 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
149 fnd_log.string
150 (
151 FND_LOG.LEVEL_PROCEDURE,
152 'fpa.sql.fpa_scenario_pvt.create_scenario.end',
153 'Exiting fpa_scenario_pvt.create_scenario'
154 );
155 END IF;
156
157 EXCEPTION
158 WHEN OTHERS THEN
159 ROLLBACK;
160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
161 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
162 fnd_log.string
163 (
164 FND_LOG.LEVEL_ERROR,
165 'fpa.sql.fpa_scenario_pvt.create_scenario',
166 SQLERRM
167 );
168 END IF;
169 FND_MSG_PUB.count_and_get
170 (
171 p_count => x_msg_count,
172 p_data => x_msg_data
173 );
174 RAISE;
175
176 END create_scenario;
177
178 /*******************************************************************************************
179 *******************************************************************************************/
180
181 PROCEDURE copy_scenario_data
182 (
183 p_api_version IN NUMBER,
184 p_scenario_id_source IN NUMBER,
185 p_scenario_id_target IN NUMBER,
186 p_copy_proposed_proj IN VARCHAR2,
187 x_return_status OUT NOCOPY VARCHAR2,
188 x_msg_count OUT NOCOPY NUMBER,
189 x_msg_data OUT NOCOPY VARCHAR2
190 ) is
191
192 l_api_version CONSTANT NUMBER := 1.0;
193
194 begin
195
196 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
197 fnd_log.string
198 (
199 FND_LOG.LEVEL_PROCEDURE,
200 'fpa.sql.fpa_scenario_pvt.copy_scenario_data.begin',
201 'Entering fpa_scenario_pvt.copy_scenario_data'
202 );
203 END IF;
204
205 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
206 fnd_log.string
207 (
208 FND_LOG.LEVEL_PROCEDURE,
209 'fpa.sql.fpa_scenario_pvt.copy_scenario_data.begin',
210 'Calling AW program COPY_SCE_DATA_PRG for scenario source: ' || p_scenario_id_source || ' and scenario target: ' || p_scenario_id_target || ' and copy flag: ' || p_copy_proposed_proj || 'values.'
211 );
212 END IF;
213
214 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
215 fnd_log.string
216 (
217 FND_LOG.LEVEL_PROCEDURE,
218 'fpa.sql.fpa_scenario_pvt.copy_scenario_data.begin',
219 'call copy_sce_data_prg(' || p_scenario_id_source || ' ' || p_scenario_id_target || ' ''' || p_copy_proposed_proj || ''')'
220 );
221 END IF;
222
223
224 dbms_aw.execute('call copy_sce_data_prg(' || p_scenario_id_source || ' ' || p_scenario_id_target || ' ''' || p_copy_proposed_proj || ''')');
225
226 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
227 fnd_log.string
228 (
229 FND_LOG.LEVEL_PROCEDURE,
230 'fpa.sql.fpa_scenario_pvt.copy_scenario_data.end',
231 'Entering fpa_scenario_pvt.copy_scenario_data'
232 );
233 END IF;
234
235 EXCEPTION
236 WHEN OTHERS THEN
237 ROLLBACK;
238 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
239 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
240 fnd_log.string
241 (
242 FND_LOG.LEVEL_ERROR,
243 'fpa.sql.fpa_scenario_pvt.create_scenario',
244 SQLERRM
245 );
246 END IF;
247 FND_MSG_PUB.count_and_get
248 (
249 p_count => x_msg_count,
250 p_data => x_msg_data
251 );
252 RAISE;
253
254 END copy_scenario_data;
255
256 /*******************************************************************************************
257 *******************************************************************************************/
258
259 PROCEDURE lock_scenario
260 (
261 p_commit IN VARCHAR2 := FND_API.G_FALSE,
262 p_scenario_rec IN fpa_scenario_pvt.scenario_rec_type,
263 x_return_status OUT NOCOPY VARCHAR2,
264 x_msg_count OUT NOCOPY NUMBER,
265 x_msg_data OUT NOCOPY VARCHAR2
266 )
267 IS
268 BEGIN
269 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
270 fnd_log.string
271 (
272 FND_LOG.LEVEL_PROCEDURE,
273 'fpa.sql.fpa_scenario_pvt.lock_scenario.begin',
274 'Entering fpa_project_pvt.lock_scenario'
275 );
276 END IF;
277
278 IF g_attach_aw THEN
279 -- Attach the AW space read write.
280 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
281 fnd_log.string
282 (
283 FND_LOG.LEVEL_STATEMENT,
284 'fpa.sql.fpa_scenario_pvt.lock_scenario',
285 'Attaching OLAP workspace: '
286 );
287 END IF;
288
289 END IF;
290
291 dbms_aw.execute('LMT scenario_d TO '''
292 || p_scenario_rec.sce_shortname || '''');
293
294 -- Lock the scenario
295 dbms_aw.execute('is_scenario_locked_m = true');
296
297 -- Set the last update date
298 dbms_aw.execute('last_update_date_scenario_r = '''
299 || to_char(SYSDATE, 'MM-DD-YYYY') || '''');
300
301 IF g_attach_aw THEN
302 -- Update and commit our changes
303 IF (p_commit = FND_API.G_TRUE) THEN
304 dbms_aw.execute('UPDATE');
305 COMMIT;
306 END IF;
307
308 -- Finally, detach the workspace
309 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
310 fnd_log.string
311 (
312 FND_LOG.LEVEL_STATEMENT,
313 'fpa.sql.fpa_scenario_pvt.lock_scenario',
314 'Detaching OLAP workspace: '
315 );
316 END IF;
317 END IF;
318
319 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
320 fnd_log.string
321 (
322 FND_LOG.LEVEL_PROCEDURE,
323 'fpa.sql.fpa_scenario_pvt.lock_scenario.end',
324 'Exiting fpa_scenario_pvt.lock_scenario'
325 );
326 END IF;
327
328 EXCEPTION
329 WHEN OTHERS THEN
330 ROLLBACK;
331 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
332
333 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
334 fnd_log.string
335 (
336 FND_LOG.LEVEL_ERROR,
337 'fpa.sql.fpa_scenario_pvt.lock_scenario',
338 SQLERRM
339 );
340 END IF;
341
342 FND_MSG_PUB.count_and_get
343 (
344 p_count => x_msg_count,
345 p_data => x_msg_data
346 );
347 RAISE;
348 END lock_scenario;
349
350 function check_scenario_name
351 (
352 p_scenario_name IN VARCHAR2,
353 p_pc_id IN NUMBER,
354 x_return_status OUT NOCOPY VARCHAR2,
355 x_msg_count OUT NOCOPY NUMBER,
356 x_msg_data OUT NOCOPY VARCHAR2
357 ) RETURN number
358 is
359
360 l_sce_count NUMBER := 0;
361
362 begin
363
364 select count(a.scenario)
365 into l_sce_count
366 from fpa_sces_vl a,
367 fpa_aw_sces_v b
368 where a.scenario = b.scenario
369 and b.planning_cycle = p_pc_id
370 and upper(a.name) = upper(p_scenario_name);
371
372 return l_sce_count;
373
374 EXCEPTION
375 WHEN OTHERS THEN
376 ROLLBACK;
377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
378 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
379 fnd_log.string
380 (
381 FND_LOG.LEVEL_ERROR,
382 'fpa_scenario_pvt.Check_scenario_name',
383 SQLERRM
384 );
385 END IF;
386 FND_MSG_PUB.count_and_get
387 (
388 p_count => x_msg_count,
389 p_data => x_msg_data
390 );
391 RAISE;
392
393 end check_scenario_name;
394
395 /************************************************************************************
396 ************************************************************************************/
397 -- This procedure updates the scenario_approved_flag_m measure
398
399 PROCEDURE update_scen_approved_flag
400 (
401 p_scenario_id IN NUMBER,
402 p_approved_flag IN VARCHAR2,
403 x_return_status OUT NOCOPY VARCHAR2,
404 x_msg_count OUT NOCOPY NUMBER,
405 x_msg_data OUT NOCOPY VARCHAR2
406 ) IS
407
408 begin
409
410
411 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
412 fnd_log.string
413 (
414 FND_LOG.LEVEL_PROCEDURE,
415 'fpa.sql.fpa_scenario_pvt.update_scen_approved_flag.begin',
416 'Entering fpa_scenario_pvt.update_scen_approved_flag'
417 );
418 END IF;
419
420 -- Bug 4331948 . Reset apporved flag of all scenario for this planning cycle.
421 -- this make sure there will be only one scenario is apporved
422 -- fix start
423 dbms_aw.execute('LMT scenario_d TO '|| p_scenario_id );
424 dbms_aw.execute('LMT planning_cycle_d to scenario_d');
425 dbms_aw.execute('LMT scenario_d to planning_cycle_d');
426 dbms_aw.execute('scenario_approved_flag_m = na');
427 -- fix end
428
429 dbms_aw.execute('LMT scenario_d TO '|| p_scenario_id );
430
431 dbms_aw.execute('scenario_approved_flag_m = ' || p_approved_flag);
432
433 -- Set the last update date
434 -- need to wite code for this
435
436 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
437 fnd_log.string
438 (
439 FND_LOG.LEVEL_PROCEDURE,
440 'fpa.sql.fpa_scenario_pvt.update_scen_approved_flag.end',
441 'Exiting fpa_scenario_pvt.update_scen_approved_flag'
442 );
443 END IF;
444
445 EXCEPTION
446 WHEN OTHERS THEN
447 ROLLBACK;
448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
449
450 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
451 fnd_log.string
452 (
453 FND_LOG.LEVEL_ERROR,
454 'fpa.sql.fpa_scenario_pvt.update_scen_approved_flag',
455 SQLERRM
456 );
457 END IF;
458
459 FND_MSG_PUB.count_and_get
460 (
461 p_count => x_msg_count,
462 p_data => x_msg_data
463 );
464 RAISE;
465
466 end update_scen_approved_flag;
467
468 /************************************************************************************
469 ************************************************************************************/
470 -- This procedure updates the discount rate for a Scenario
471
472 procedure update_scenario_disc_rate
473 (
474 p_api_version IN NUMBER,
475 p_scenario_id IN NUMBER,
476 p_discount_rate IN NUMBER,
477 x_return_status OUT NOCOPY VARCHAR2,
478 x_msg_count OUT NOCOPY NUMBER,
479 x_msg_data OUT NOCOPY VARCHAR2
480 ) is
481
482 l_api_version CONSTANT NUMBER := 1.0;
483 l_decimal_marker VARCHAR2(1);
484 l_discount_rate VARCHAR2(100);
485
486 begin
487
488 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
489 fnd_log.string
490 (
491 FND_LOG.LEVEL_PROCEDURE,
492 'fpa.sql.fpa_scenario_pvt.update_scenario_disc_rate.begin',
493 'Entering fpa_scenario_pvt.update_scenario_disc_rate'
494 );
495 END IF;
496
497 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
498 fnd_log.string
499 (
500 FND_LOG.LEVEL_PROCEDURE,
501 'fpa.sql.fpa_scenario_pvt.update_scenario_disc_rate',
502 'Setting the discount rate.'
503 );
504 END IF;
505
506 dbms_aw.execute('PUSH scenario_d');
507 dbms_aw.execute('LMT scenario_d TO ' || p_scenario_id);
508
509 SELECT SUBSTR(VALUE,1,1)
510 INTO l_decimal_marker
511 FROM NLS_SESSION_PARAMETERS
512 WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';
513
514 l_discount_rate := REPLACE( To_Char(p_discount_rate/100), l_decimal_marker,'.');
515
516 -- Associate the scenario with the planning cycle
517 dbms_aw.execute('scenario_discount_rate_m = ' || l_discount_rate);
518 -- Update daily discount rate
519 dbms_aw.execute('scenario_discount_rate_daily_m = ((1+scenario_discount_rate_m)**(1/365))-1');
520 dbms_aw.execute('POP scenario_d');
521
522 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
523 fnd_log.string
524 (
525 FND_LOG.LEVEL_PROCEDURE,
526 'fpa.sql.fpa_scenario_pvt.update_scenario_disc_rate.end',
527 'Entering fpa_scenario_pvt.update_scenario_disc_rate'
528 );
529 END IF;
530
531 EXCEPTION
532 WHEN OTHERS THEN
533 ROLLBACK;
534 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
535 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
536 fnd_log.string
537 (
538 FND_LOG.LEVEL_ERROR,
539 'fpa.sql.fpa_scenario_pvt.create_scenario',
540 SQLERRM
541 );
542 END IF;
543 FND_MSG_PUB.count_and_get
544 (
545 p_count => x_msg_count,
546 p_data => x_msg_data
547 );
548 RAISE;
549
550 end update_scenario_disc_rate;
551
552 /*******************************************************************************************
553 *******************************************************************************************/
554
555 procedure update_scenario_funds_avail
556 (
557 p_api_version IN NUMBER,
558 p_scenario_id IN NUMBER,
559 p_scenario_funds IN NUMBER,
560 x_return_status OUT NOCOPY VARCHAR2,
561 x_msg_count OUT NOCOPY NUMBER,
562 x_msg_data OUT NOCOPY VARCHAR2
563 ) is
564
565 l_api_version CONSTANT NUMBER := 1.0;
566 l_pc_disply_factor VARCHAR2(30);
567 l_decimal_marker VARCHAR2(1) ;
568 l_scen_funds VARCHAR2(100) ;
569
570 begin
571
572 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
573 fnd_log.string
574 (
575 FND_LOG.LEVEL_PROCEDURE,
576 'fpa.sql.fpa_scenario_pvt.update_scenario_funds_avail.begin',
577 'Entering fpa_scenario_pvt.update_scenario_funds_avail'
578 );
579 END IF;
580
581 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
582 fnd_log.string
583 (
584 FND_LOG.LEVEL_PROCEDURE,
585 'fpa.sql.fpa_scenario_pvt.update_scenario_funds_avail',
586 'Setting the discount rate.'
587 );
588 END IF;
589
590 -- get the display factor, that will be used
591 -- to multiply with the funds avaialble.
592 SELECT b.PC_DISPLAY_FACTOR
593 INTO l_pc_disply_factor
594 FROM fpa_aw_sces_v a, fpa_aw_pc_disc_funds_v b
595 WHERE a.planning_cycle = b.planning_cycle
596 AND a.scenario = p_scenario_id ;
597
598
599 dbms_aw.execute('PUSH scenario_d');
600 dbms_aw.execute('LMT scenario_d TO ' || p_scenario_id);
601
602 SELECT SUBSTR(VALUE,1,1)
603 INTO l_decimal_marker
604 FROM NLS_SESSION_PARAMETERS
605 WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';
606 l_scen_funds := REPLACE( To_Char(p_scenario_funds*l_pc_disply_factor), l_decimal_marker,'.');
607
608 -- Associate the scenario with the planning cycle
609 dbms_aw.execute('scenario_funding_m = ' || l_scen_funds);
610 dbms_aw.execute('POP scenario_d');
611
612 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
613 fnd_log.string
614 (
615 FND_LOG.LEVEL_PROCEDURE,
616 'fpa.sql.fpa_scenario_pvt.update_scenario_funds_avail.end',
617 'Entering fpa_scenario_pvt.update_scenario_funds_avail'
618 );
619 END IF;
620
621 EXCEPTION
622 WHEN OTHERS THEN
623 ROLLBACK;
624 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
625 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
626 fnd_log.string
627 (
628 FND_LOG.LEVEL_ERROR,
629 'fpa.sql.fpa_scenario_pvt.update_scenario_funds_avail',
630 SQLERRM
631 );
632 END IF;
633 FND_MSG_PUB.count_and_get
634 (
635 p_count => x_msg_count,
636 p_data => x_msg_data
637 );
638 RAISE;
639
640 end update_scenario_funds_avail;
641
642 /*******************************************************************************************
643 *******************************************************************************************/
644 -- This procedure updates the scenario initial flag.
645 -- Only a single scenario per planning Cycle may hold this flag as true.
646 procedure update_scenario_initial_flag
647 (
648 p_api_version IN NUMBER,
649 p_scenario_id IN NUMBER,
650 x_return_status OUT NOCOPY VARCHAR2,
651 x_msg_count OUT NOCOPY NUMBER,
652 x_msg_data OUT NOCOPY VARCHAR2
653 ) is
654
655 l_api_version CONSTANT NUMBER := 1.0;
656
657 begin
658
659 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
660 fnd_log.string
661 (
662 FND_LOG.LEVEL_PROCEDURE,
663 'fpa.sql.fpa_scenario_pvt.update_scenario_initial_flag.begin',
664 'Entering fpa_scenario_pvt.update_scenario_initial_flag.'
665 );
666 END IF;
667
668 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
669 fnd_log.string
670 (
671 FND_LOG.LEVEL_PROCEDURE,
672 'fpa.sql.fpa_scenario_pvt.update_scenario_initial_flag.begin',
673 'Unsetting any previous flags and setting new flag.'
674 );
675 END IF;
676
677 dbms_aw.execute('PUSH scenario_d');
678 dbms_aw.execute('PUSH planning_cycle_d');
679 dbms_aw.execute('oknullstatus = y');
680 dbms_aw.execute('limit scenario_d to ' || p_scenario_id);
681 -- dbms_aw.execute('limit scenario_d to p_scenario_id');
682 dbms_aw.execute('limit planning_cycle_d to planning_cycle_scenario_r');
683 dbms_aw.execute('limit scenario_d to planning_cycle_d');
684 dbms_aw.execute('limit scenario_d keep scenario_initial_m');
685 dbms_aw.execute('scenario_initial_m = na');
686 dbms_aw.execute('limit scenario_d to ' || p_scenario_id);
687 -- dbms_aw.execute('limit scenario_d to p_scenario_id');
688 dbms_aw.execute('scenario_initial_m = yes');
689 dbms_aw.execute('POP scenario_d');
690 dbms_aw.execute('POP planning_cycle_d');
691
692 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
693 fnd_log.string
694 (
695 FND_LOG.LEVEL_PROCEDURE,
696 'fpa.sql.fpa_scenario_pvt.update_scenario_initial_flag.end',
697 'Entering fpa_scenario_pvt.update_scenario_initial_flag.'
698 );
699 END IF;
700
701 EXCEPTION
702 WHEN OTHERS THEN
703 ROLLBACK;
704 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
705 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
706 fnd_log.string
707 (
708 FND_LOG.LEVEL_ERROR,
709 'fpa.sql.fpa_scenario_pvt.update_scenario_initial_flag',
710 SQLERRM
711 );
712 END IF;
713 FND_MSG_PUB.count_and_get
714 (
715 p_count => x_msg_count,
716 p_data => x_msg_data
717 );
718 RAISE;
719
720 end update_scenario_initial_flag;
721
722 /*******************************************************************************************
723 *******************************************************************************************/
724 -- This procedure updates the scenario working flag.
725 -- Only a single scenario per planning Cycle may hold this flag as true.
726 procedure update_scenario_working_flag
727 (
728 p_api_version IN NUMBER,
729 p_scenario_id IN NUMBER,
730 x_return_status OUT NOCOPY VARCHAR2,
731 x_msg_count OUT NOCOPY NUMBER,
732 x_msg_data OUT NOCOPY VARCHAR2
733 ) is
734
735 l_api_version CONSTANT NUMBER := 1.0;
736
737 begin
738
739 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
740 fnd_log.string
741 (
742 FND_LOG.LEVEL_PROCEDURE,
743 'fpa.sql.fpa_scenario_pvt.update_scenario_working_flag.begin',
744 'Entering fpa_scenario_pvt.update_scenario_working_flag.'
745 );
746 END IF;
747
748 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
749 fnd_log.string
750 (
751 FND_LOG.LEVEL_PROCEDURE,
752 'fpa.sql.fpa_scenario_pvt.update_scenario_working_flag',
753 'Unsetting any previous flags and setting new flag.'
754 );
755 END IF;
756
757 dbms_aw.execute('PUSH scenario_d');
758 dbms_aw.execute('PUSH planning_cycle_d');
759 dbms_aw.execute('oknullstatus = y');
760 dbms_aw.execute('limit scenario_d to ' || p_scenario_id);
761 dbms_aw.execute('limit planning_cycle_d to planning_cycle_scenario_r');
762 dbms_aw.execute('limit scenario_d to planning_cycle_d');
763 dbms_aw.execute('limit scenario_d keep scenario_working_m');
764 dbms_aw.execute('scenario_working_m = na');
765 dbms_aw.execute('limit scenario_d to ' || p_scenario_id);
766 dbms_aw.execute('scenario_working_m = yes');
767 dbms_aw.execute('POP scenario_d');
768 dbms_aw.execute('POP planning_cycle_d');
769
770 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
771 fnd_log.string
772 (
773 FND_LOG.LEVEL_PROCEDURE,
774 'fpa.sql.fpa_scenario_pvt.update_scenario_working_flag.end',
775 'Entering fpa_scenario_pvt.update_scenario_working_flag.'
776 );
777 END IF;
778
779 EXCEPTION
780 WHEN OTHERS THEN
781 --dbms_output.put_line(SQLERRM);
782 ROLLBACK;
783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
784 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
785 fnd_log.string
786 (
787 FND_LOG.LEVEL_ERROR,
788 'fpa.sql.fpa_scenario_pvt.update_scenario_working_flag',
789 SQLERRM
790 );
791 END IF;
792 FND_MSG_PUB.count_and_get
793 (
794 p_count => x_msg_count,
795 p_data => x_msg_data
796 );
797 RAISE;
798
799 end update_scenario_working_flag;
800
801 /*******************************************************************************************
802 *******************************************************************************************/
803 -- This procedure sets or unsets the recommended flag for a scenario. The parameters are
804 -- p_scenario_id for the Scenario Id to be updated, and p_scenario_reccom_status holding the
805 -- values 'yes' for recommending the scenario, or 'na' for unrecommending the scenario.
806
807 procedure update_scenario_reccom_flag
808 (
809 p_api_version IN NUMBER,
810 p_scenario_id IN NUMBER,
811 p_scenario_reccom_status IN VARCHAR2,
812 x_return_status OUT NOCOPY VARCHAR2,
813 x_msg_count OUT NOCOPY NUMBER,
814 x_msg_data OUT NOCOPY VARCHAR2
815 ) is
816
817 l_api_version CONSTANT NUMBER := 1.0;
818
819 begin
820
821 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
822 fnd_log.string
823 (
824 FND_LOG.LEVEL_PROCEDURE,
825 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_flag.begin',
826 'Entering fpa_scenario_pvt.update_scenario_reccom_flag.'
827 );
828 END IF;
829
830 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
831 fnd_log.string
832 (
833 FND_LOG.LEVEL_PROCEDURE,
834 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_flag',
835 'Unsetting any previous flags and setting new flag.'
836 );
837 END IF;
838
839 dbms_aw.execute('PUSH scenario_d');
840 dbms_aw.execute('lmt scenario_d to ' || p_scenario_id);
841 dbms_aw.execute('scenario_recommended_flag_m = ' || p_scenario_reccom_status);
842 dbms_aw.execute('POP scenario_d');
843
844 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
845 fnd_log.string
846 (
847 FND_LOG.LEVEL_PROCEDURE,
848 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_flag.end',
849 'Entering fpa_scenario_pvt.update_scenario_reccom_flag.'
850 );
851 END IF;
852
853 EXCEPTION
854 WHEN OTHERS THEN
855 --dbms_output.put_line(SQLERRM);
856 ROLLBACK;
857 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
858 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
859 fnd_log.string
860 (
861 FND_LOG.LEVEL_ERROR,
862 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_flag',
863 SQLERRM
864 );
865 END IF;
866 FND_MSG_PUB.count_and_get
867 (
868 p_count => x_msg_count,
869 p_data => x_msg_data
870 );
871 RAISE;
872
873 end update_scenario_reccom_flag;
874
875 -- This procedure updates the recommending funding status relation for the projects within
876 -- a scenario.
877 -- This procedure is capable of updating a single project or multiple projects.
878 -- The parameter p_project_d must be of the following form:
879 -- '10001,' for a single project or (with trailing comma)
880 -- '10001, 10002, 10003,' for multiple projects. (each id separated with a comma, also
881 -- trailing coma.
882 procedure update_scenario_reccom_status
883 (
884 p_api_version IN NUMBER,
885 p_scenario_id IN NUMBER,
886 p_project_id IN VARCHAR2,
887 p_scenario_reccom_value IN VARCHAR2,
888 x_return_status OUT NOCOPY VARCHAR2,
889 x_msg_count OUT NOCOPY NUMBER,
890 x_msg_data OUT NOCOPY VARCHAR2
891 ) is
892
893 l_api_version CONSTANT NUMBER := 1.0;
894
895 l_project_id_string VARCHAR2(5000);
896
897 l_project_id VARCHAR2(10);
898
899 begin
900
901 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
902 fnd_log.string
903 (
904 FND_LOG.LEVEL_PROCEDURE,
905 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_status.begin',
906 'Entering fpa_scenario_pvt.update_scenario_reccom_status'
907 );
908 END IF;
909
910 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
911 fnd_log.string
912 (
913 FND_LOG.LEVEL_PROCEDURE,
914 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_status.begin',
915 'Limiting to scenario id passed.'
916 );
917 END IF;
918
919 dbms_aw.execute('PUSH scenario_d');
920 dbms_aw.execute('LMT scenario_d TO ' || p_scenario_id);
921
922 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
923 fnd_log.string
924 (
925 FND_LOG.LEVEL_PROCEDURE,
926 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_status.begin',
927 'Assing project id string passed to local string variable.'
928 );
929 END IF;
930
931 l_project_id_string := p_project_id;
932
933 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
934 fnd_log.string
935 (
936 FND_LOG.LEVEL_PROCEDURE,
937 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_status.begin',
938 'Loop over project id string and update recommended funding status relation.'
939 );
940 END IF;
941
942 WHILE (length(l_project_id_string) > 0) LOOP
943
944 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
945 fnd_log.string
946 (
947 FND_LOG.LEVEL_PROCEDURE,
948 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_status.begin',
949 'Current project string: ' || l_project_id_string || ' and project id: ' || l_project_id
950 );
951 END IF;
952
953 l_project_id := substr(l_project_id_string, 1, instr(l_project_id_string, ',') -1);
954 l_project_id_string := substr(l_project_id_string, (instr(l_project_id_string, ',') + 1));
955 --dbms_output.put_line('id: ' || l_project_id);
956 --dbms_output.put_line('string: ' || l_project_id_string);
957
958 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
959 fnd_log.string
960 (
961 FND_LOG.LEVEL_PROCEDURE,
962 'fpa.sql.fpa_scenario_pvt.',
963 'Updating FUNDING_STATUS_REC_SCENARIO_PROJECT_R relation for scenario: ' || p_scenario_id || ' and for project: ' || l_project_id
964 );
965 END IF;
966
967 dbms_aw.execute('funding_status_rec_scenario_project_r(project_d ' || l_project_id || ') = ''' || p_scenario_reccom_value || '''');
968
969 END LOOP;
970
971 dbms_aw.execute('POP scenario_d');
972
973 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
974 fnd_log.string
975 (
976 FND_LOG.LEVEL_PROCEDURE,
977 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_status.end',
978 'Exiting fpa_scenario_pvt.update_scenario_reccom_status.'
979 );
980 END IF;
981
982 EXCEPTION
983 WHEN OTHERS THEN
984 ROLLBACK;
985 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
986 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
987 fnd_log.string
988 (
989 FND_LOG.LEVEL_ERROR,
990 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_status',
991 SQLERRM
992 );
993 END IF;
994 FND_MSG_PUB.count_and_get
995 (
996 p_count => x_msg_count,
997 p_data => x_msg_data
998 );
999 RAISE;
1000
1001 end update_scenario_reccom_status;
1002
1003
1004
1005 -- Call OLAP Program to copy project data from initial scenario to current(target) scenario
1006 -- If multiple projects are being added from UI, the p_project_id is passed as a string of project Ids.
1007 -- delimited by space character.
1008 -- This API is called from Add Projects page when Projects are added from Initial Scenario or Current Plan
1009
1010 PROCEDURE copy_sce_project_data
1011 (
1012 p_api_version IN NUMBER,
1013 p_commit IN VARCHAR2,
1014 p_target_scen_id IN NUMBER,
1015 p_project_id_str IN VARCHAR2,
1016 x_return_status OUT NOCOPY VARCHAR2,
1017 x_msg_count OUT NOCOPY NUMBER,
1018 x_msg_data OUT NOCOPY VARCHAR2
1019 ) IS
1020 l_str varchar2(2000);
1021 source_scen_id number;
1022 BEGIN
1023
1024 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1025 fnd_log.string
1026 (
1027 FND_LOG.LEVEL_PROCEDURE,
1028 'fpa.sql.fpa_scenario_pvt.copy_sce_project_data.begin',
1029 'Entering fpa_scenario_pvt.copy_sce_project_data'
1030 );
1031 END IF;
1032
1033 -- get the initial scenario id. Always required, no matter what the source/mode is.
1034 -- if the source is pjp, project is selected from initial sce. and added to current sce.
1035 -- if source is pjt, get the project from current plan , add it to initial sce,
1036 -- and then invoke this api in pjp mode to add the same project to the current sce.
1037
1038 select scenario
1039 into source_scen_id
1040 from fpa_aw_sce_info_v
1041 where planning_cycle =
1042 (select planning_cycle from fpa_aw_sce_info_v where scenario = p_target_scen_id)
1043 and is_initial_scenario = 1 ;
1044
1045 l_str := 'call copy_proj_data_prg(' || p_target_scen_id || ', ' || source_scen_id || ', '' ' || p_project_id_str || ''')';
1046 dbms_aw.execute(l_str);
1047 --('call copy_proj_data_prg(' || p_target_scen_id || ' ' || source_scen_id || ' '' ' || p_project_id_str || ''')');
1048
1049 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1050 fnd_log.string
1051 (
1052 FND_LOG.LEVEL_PROCEDURE,
1053 'fpa.sql.fpa_scenario_pvt.copy_sce_project_data.end',
1054 'Entering fpa_scenario_pvt.copy_sce_project_data'
1055 );
1056 END IF;
1057
1058 EXCEPTION
1059 WHEN OTHERS THEN
1060 ROLLBACK;
1061 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1062 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1063 fnd_log.string
1064 (
1065 FND_LOG.LEVEL_ERROR,
1066 'fpa.sql.fpa_scenario_pvt.copy_sce_project_data',
1067 SQLERRM
1068 );
1069 END IF;
1070 FND_MSG_PUB.count_and_get
1071 (
1072 p_count => x_msg_count,
1073 p_data => x_msg_data
1074 );
1075 RAISE;
1076
1077 END copy_sce_project_data;
1078
1079 PROCEDURE remove_project_from_scenario
1080 (
1081 p_api_version IN NUMBER,
1082 p_commit IN VARCHAR2,
1083 p_scenario_id IN NUMBER,
1084 p_project_id IN NUMBER,
1085 x_return_status OUT NOCOPY VARCHAR2,
1086 x_msg_count OUT NOCOPY NUMBER,
1087 x_msg_data OUT NOCOPY VARCHAR2
1088 ) IS
1089
1090 l_str varchar2(2000);
1091 BEGIN
1092
1093 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1094 fnd_log.string
1095 (
1096 FND_LOG.LEVEL_PROCEDURE,
1097 'fpa.sql.fpa_scenario_pvt.remove_project_from_scenario.begin',
1098 'Entering fpa_scenario_pvt.remove_project_from_scenario'
1099 );
1100 END IF;
1101
1102 l_str := 'call remove_proj_from_sce_prg(' || p_scenario_id || ', ' || p_project_id || ')';
1103
1104 -- dbms_output.put_line(l_str);
1105 dbms_aw.execute(l_str);
1106
1107 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1108 fnd_log.string
1109 (
1110 FND_LOG.LEVEL_PROCEDURE,
1111 'fpa.sql.fpa_scenario_pvt.remove_project_from_scenario.end',
1112 'Entering fpa_scenario_pvt.remove_project_from_scenario'
1113 );
1114 END IF;
1115
1116 EXCEPTION
1117 WHEN OTHERS THEN
1118 ROLLBACK;
1119 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1120 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1121 fnd_log.string
1122 (
1123 FND_LOG.LEVEL_ERROR,
1124 'fpa.sql.fpa_scenario_pvt.remove_project_from_scenario',
1125 SQLERRM
1126 );
1127 END IF;
1128 FND_MSG_PUB.count_and_get
1129 (
1130 p_count => x_msg_count,
1131 p_data => x_msg_data
1132 );
1133 RAISE;
1134 END;
1135
1136
1137 PROCEDURE Update_Proj_User_Ranks
1138 ( p_api_version IN NUMBER,
1139 p_proj_metrics IN fpa_scen_proj_userrank_tbl,
1140 x_return_status OUT NOCOPY VARCHAR2,
1141 x_msg_data OUT NOCOPY VARCHAR2,
1142 x_msg_count OUT NOCOPY NUMBER )
1143 IS
1144 BEGIN
1145
1146 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1147 FND_LOG.String
1148 (
1149 FND_LOG.LEVEL_PROCEDURE,
1150 'fpa.sql.FPA_Scenario_Pvt.Update_Proj_User_Ranks.begin',
1151 'Entering FPA_Scenario_Pvt.Update_Proj_User_Ranks'
1152 );
1153 END IF;
1154
1155 --DBMS_OUTPUT.put_line(' ..p_proj_metrics(1).scenario=' || p_proj_metrics(1).scenarioID );
1156
1157 -- Update user ranking against project ID for the given scenario ID
1158 -- Limit Scenario ID
1159 IF( p_proj_metrics.Count > 0 ) THEN
1160 IF( p_proj_metrics.Count > 1 ) THEN
1161 dbms_aw.execute('LMT scenario_d TO ' || p_proj_metrics(1).scenarioID );
1162 END IF;
1163 FOR i IN p_proj_metrics.FIRST..p_proj_metrics.LAST
1164 LOOP
1165
1166 -- DBMS_OUTPUT.put_line(' ..Inside For loop...p_proj_metrics(i).project=' || p_proj_metrics(i).projectID );
1167 -- DBMS_OUTPUT.put_line(' ..Inside For loop...p_proj_metrics(i).user_rank=' || p_proj_metrics(i).user_rank );
1168 /* IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1169 FND_LOG.String
1170 (
1171 FND_LOG.LEVEL_PROCEDURE,
1172 'fpa.sql.FPA_Scenario_Pvt.Update_Proj_User_Ranks.end',
1173 'FPA_Scenario_Pvt.Update_Proj_User_Ranks: Inside for loop:projectID='||p_proj_metrics(i).projectID
1174 ||' user_rank='||p_proj_metrics(i).user_rank
1175 );
1176 END IF; */
1177 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1178 FND_LOG.String
1179 (
1180 FND_LOG.LEVEL_PROCEDURE,
1181 'fpa.sql.FPA_Scenario_Pvt.Update_Proj_User_Ranks.end',
1182 'FPA_Scenario_Pvt.Update_Proj_User_Ranks: Inside for loop:projectID='||p_proj_metrics(i).projectID);
1183 END IF;
1184 -- Limit project_d
1185 dbms_aw.execute('LMT project_d TO ' || p_proj_metrics(i).projectID );
1186
1187 -- Set the the user rank for each project
1188 IF p_proj_metrics(i).user_rank IS NULL THEN
1189 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1190 FND_LOG.String
1191 (
1192 FND_LOG.LEVEL_PROCEDURE,
1193 'fpa.sql.FPA_Scenario_Pvt.Update_Proj_User_Ranks.end',
1194 'FPA_Scenario_Pvt.Update_Proj_User_Ranks: Inside for loop:User Rank = na');
1195 END IF;
1196 dbms_aw.execute( 'scenario_project_user_rank_m = na');
1197 ELSE
1198 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1199 FND_LOG.String
1200 (
1201 FND_LOG.LEVEL_PROCEDURE,
1202 'fpa.sql.FPA_Scenario_Pvt.Update_Proj_User_Ranks.end',
1203 'FPA_Scenario_Pvt.Update_Proj_User_Ranks: Inside for loop:User Rank ='||p_proj_metrics(i).user_rank);
1204 END IF;
1205 dbms_aw.execute( 'scenario_project_user_rank_m = ' || p_proj_metrics(i).user_rank );
1206 END IF;
1207
1208 END LOOP;
1209 END IF;
1210
1211
1212 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1213 FND_LOG.String
1214 (
1215 FND_LOG.LEVEL_PROCEDURE,
1216 'fpa.sql.FPA_Scenario_Pvt.Update_Proj_User_Ranks.end',
1217 'Exiting FPA_Scenario_Pvt.Update_Proj_User_Ranks'
1218 );
1219 END IF;
1220
1221 EXCEPTION
1222 WHEN OTHERS THEN
1223 --DBMS_OUTPUT.put_line('...Inside Object level API EXCEPTION block...');
1224
1225 ROLLBACK;
1226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1227
1228 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
1229 FND_LOG.String
1230 (
1231 FND_LOG.LEVEL_ERROR,
1232 'fpa.sql.FPA_Scenario_Pvt.Update_Proj_User_Ranks',
1233 SQLERRM
1234 );
1235 END IF;
1236
1237 FND_MSG_PUB.count_and_get
1238 (
1239 p_count => x_msg_count,
1240 p_data => x_msg_data
1241 );
1242 RAISE;
1243
1244 END Update_Proj_User_Ranks;
1245
1246 /*******************************************************************************************
1247 *******************************************************************************************/
1248 -- This procedures call the program CALC_SCE_ALL_DATA_PRG. This is an AW program
1249 -- which recalculates all Scenario data.
1250 -- The parameter p_data_to_calc to this procedure is used to tell the AW program
1251 -- what level of data to calculate.
1252 -- For more information on this parameter refer to the documentation for CALC_SCE_ALL_DATA_PRG
1253 -- program.
1254 PROCEDURE calc_scenario_data
1255 (
1256 p_api_version IN NUMBER,
1257 p_scenario_id IN NUMBER,
1258 p_project_id IN NUMBER,
1259 p_class_code_id IN NUMBER,
1260 p_data_to_calc IN VARCHAR2,
1261 x_return_status OUT NOCOPY VARCHAR2,
1262 x_msg_count OUT NOCOPY NUMBER,
1263 x_msg_data OUT NOCOPY VARCHAR2
1264 ) is
1265
1266 l_api_version CONSTANT NUMBER := 1.0;
1267
1268 begin
1269
1270 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1271 fnd_log.string
1272 (
1273 FND_LOG.LEVEL_PROCEDURE,
1274 'fpa.sql.fpa_scenario_pvt.calc_scenario_data.begin',
1275 'Entering fpa_scenario_pvt.calc_scenario_data'
1276 );
1277 END IF;
1278
1279 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1280 fnd_log.string
1281 (
1282 FND_LOG.LEVEL_PROCEDURE,
1283 'fpa.sql.fpa_scenario_pvt.calc_scenario_data.',
1284 'Calling AW program CALC_SCE_ALL_DATA_PRG.'
1285 );
1286 END IF;
1287
1288 if p_project_id is null and p_class_code_id is null
1289 then dbms_aw.execute('call calc_sce_all_data_prg(' || p_scenario_id || ' na na ''' || p_data_to_calc || ''')');
1290 elsif p_project_id is null
1291 then dbms_aw.execute('call calc_sce_all_data_prg(' || p_scenario_id || ' na ' || p_class_code_id || '''' || p_data_to_calc || ''')');
1292 elsif p_class_code_id is null
1293 then dbms_aw.execute('call calc_sce_all_data_prg(' || p_scenario_id || ' ' || p_project_id || ' na ''' || p_data_to_calc || ''')');
1294 end if;
1295
1296 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1297 fnd_log.string
1298 (
1299 FND_LOG.LEVEL_PROCEDURE,
1300 'fpa.sql.fpa_scenario_pvt.calc_scenario_data.end',
1301 'Entering fpa_scenario_pvt.calc_scenario_data'
1302 );
1303 END IF;
1304
1305 EXCEPTION
1306 WHEN OTHERS THEN
1307 ROLLBACK;
1308 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1309 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1310 fnd_log.string
1311 (
1312 FND_LOG.LEVEL_ERROR,
1313 'fpa.sql.fpa_scenario_pvt.calc_scenario_data',
1314 SQLERRM
1315 );
1316 END IF;
1317 FND_MSG_PUB.count_and_get
1318 (
1319 p_count => x_msg_count,
1320 p_data => x_msg_data
1321 );
1322 RAISE;
1323
1324 END calc_scenario_data;
1325
1326
1327 END fpa_scenario_pvt;