[Home] [Help]
PACKAGE BODY: APPS.FPA_SCENARIO_PVT
Source
1 PACKAGE BODY fpa_scenario_pvt AS
2 /* $Header: FPAVSCEB.pls 120.1.12010000.1 2008/07/30 16:28:02 appldev 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
484 begin
485
486 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
487 fnd_log.string
488 (
489 FND_LOG.LEVEL_PROCEDURE,
490 'fpa.sql.fpa_scenario_pvt.update_scenario_disc_rate.begin',
491 'Entering fpa_scenario_pvt.update_scenario_disc_rate'
492 );
493 END IF;
494
495 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
496 fnd_log.string
497 (
498 FND_LOG.LEVEL_PROCEDURE,
499 'fpa.sql.fpa_scenario_pvt.update_scenario_disc_rate',
500 'Setting the discount rate.'
501 );
502 END IF;
503
504 dbms_aw.execute('PUSH scenario_d');
505 dbms_aw.execute('LMT scenario_d TO ' || p_scenario_id);
506 -- Associate the scenario with the planning cycle
507 dbms_aw.execute('scenario_discount_rate_m = ' || p_discount_rate/100);
508 -- Update daily discount rate
509 dbms_aw.execute('scenario_discount_rate_daily_m = ((1+scenario_discount_rate_m)**(1/365))-1');
510 dbms_aw.execute('POP scenario_d');
511
512 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
513 fnd_log.string
514 (
515 FND_LOG.LEVEL_PROCEDURE,
516 'fpa.sql.fpa_scenario_pvt.update_scenario_disc_rate.end',
517 'Entering fpa_scenario_pvt.update_scenario_disc_rate'
518 );
519 END IF;
520
521 EXCEPTION
522 WHEN OTHERS THEN
523 ROLLBACK;
524 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
525 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
526 fnd_log.string
527 (
528 FND_LOG.LEVEL_ERROR,
529 'fpa.sql.fpa_scenario_pvt.create_scenario',
530 SQLERRM
531 );
532 END IF;
533 FND_MSG_PUB.count_and_get
534 (
535 p_count => x_msg_count,
536 p_data => x_msg_data
537 );
538 RAISE;
539
540 end update_scenario_disc_rate;
541
542 /*******************************************************************************************
543 *******************************************************************************************/
544
545 procedure update_scenario_funds_avail
546 (
547 p_api_version IN NUMBER,
548 p_scenario_id IN NUMBER,
549 p_scenario_funds IN NUMBER,
550 x_return_status OUT NOCOPY VARCHAR2,
551 x_msg_count OUT NOCOPY NUMBER,
552 x_msg_data OUT NOCOPY VARCHAR2
553 ) is
554
555 l_api_version CONSTANT NUMBER := 1.0;
556 l_pc_disply_factor VARCHAR2(30);
557 begin
558
559 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
560 fnd_log.string
561 (
562 FND_LOG.LEVEL_PROCEDURE,
563 'fpa.sql.fpa_scenario_pvt.update_scenario_funds_avail.begin',
564 'Entering fpa_scenario_pvt.update_scenario_funds_avail'
565 );
566 END IF;
567
568 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
569 fnd_log.string
570 (
571 FND_LOG.LEVEL_PROCEDURE,
572 'fpa.sql.fpa_scenario_pvt.update_scenario_funds_avail',
573 'Setting the discount rate.'
574 );
575 END IF;
576
577 -- get the display factor, that will be used
578 -- to multiply with the funds avaialble.
579 SELECT b.PC_DISPLAY_FACTOR
580 INTO l_pc_disply_factor
581 FROM fpa_aw_sces_v a, fpa_aw_pc_disc_funds_v b
582 WHERE a.planning_cycle = b.planning_cycle
583 AND a.scenario = p_scenario_id ;
584
585
586 dbms_aw.execute('PUSH scenario_d');
587 dbms_aw.execute('LMT scenario_d TO ' || p_scenario_id);
588 -- Associate the scenario with the planning cycle
589 dbms_aw.execute('scenario_funding_m = ' || p_scenario_funds*l_pc_disply_factor);
590 dbms_aw.execute('POP scenario_d');
591
592 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
593 fnd_log.string
594 (
595 FND_LOG.LEVEL_PROCEDURE,
596 'fpa.sql.fpa_scenario_pvt.update_scenario_funds_avail.end',
597 'Entering fpa_scenario_pvt.update_scenario_funds_avail'
598 );
599 END IF;
600
601 EXCEPTION
602 WHEN OTHERS THEN
603 ROLLBACK;
604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
605 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
606 fnd_log.string
607 (
608 FND_LOG.LEVEL_ERROR,
609 'fpa.sql.fpa_scenario_pvt.update_scenario_funds_avail',
610 SQLERRM
611 );
612 END IF;
613 FND_MSG_PUB.count_and_get
614 (
615 p_count => x_msg_count,
616 p_data => x_msg_data
617 );
618 RAISE;
619
620 end update_scenario_funds_avail;
621
622 /*******************************************************************************************
623 *******************************************************************************************/
624 -- This procedure updates the scenario initial flag.
625 -- Only a single scenario per planning Cycle may hold this flag as true.
626 procedure update_scenario_initial_flag
627 (
628 p_api_version IN NUMBER,
629 p_scenario_id IN NUMBER,
630 x_return_status OUT NOCOPY VARCHAR2,
631 x_msg_count OUT NOCOPY NUMBER,
632 x_msg_data OUT NOCOPY VARCHAR2
633 ) is
634
635 l_api_version CONSTANT NUMBER := 1.0;
636
637 begin
638
639 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
640 fnd_log.string
641 (
642 FND_LOG.LEVEL_PROCEDURE,
643 'fpa.sql.fpa_scenario_pvt.update_scenario_initial_flag.begin',
644 'Entering fpa_scenario_pvt.update_scenario_initial_flag.'
645 );
646 END IF;
647
648 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
649 fnd_log.string
650 (
651 FND_LOG.LEVEL_PROCEDURE,
652 'fpa.sql.fpa_scenario_pvt.update_scenario_initial_flag.begin',
653 'Unsetting any previous flags and setting new flag.'
654 );
655 END IF;
656
657 dbms_aw.execute('PUSH scenario_d');
658 dbms_aw.execute('PUSH planning_cycle_d');
659 dbms_aw.execute('oknullstatus = y');
660 dbms_aw.execute('limit scenario_d to ' || p_scenario_id);
661 -- dbms_aw.execute('limit scenario_d to p_scenario_id');
662 dbms_aw.execute('limit planning_cycle_d to planning_cycle_scenario_r');
663 dbms_aw.execute('limit scenario_d to planning_cycle_d');
664 dbms_aw.execute('limit scenario_d keep scenario_initial_m');
665 dbms_aw.execute('scenario_initial_m = na');
666 dbms_aw.execute('limit scenario_d to ' || p_scenario_id);
667 -- dbms_aw.execute('limit scenario_d to p_scenario_id');
668 dbms_aw.execute('scenario_initial_m = yes');
669 dbms_aw.execute('POP scenario_d');
670 dbms_aw.execute('POP planning_cycle_d');
671
672 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
673 fnd_log.string
674 (
675 FND_LOG.LEVEL_PROCEDURE,
676 'fpa.sql.fpa_scenario_pvt.update_scenario_initial_flag.end',
677 'Entering fpa_scenario_pvt.update_scenario_initial_flag.'
678 );
679 END IF;
680
681 EXCEPTION
682 WHEN OTHERS THEN
683 ROLLBACK;
684 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
685 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
686 fnd_log.string
687 (
688 FND_LOG.LEVEL_ERROR,
689 'fpa.sql.fpa_scenario_pvt.update_scenario_initial_flag',
690 SQLERRM
691 );
692 END IF;
693 FND_MSG_PUB.count_and_get
694 (
695 p_count => x_msg_count,
696 p_data => x_msg_data
697 );
698 RAISE;
699
700 end update_scenario_initial_flag;
701
702 /*******************************************************************************************
703 *******************************************************************************************/
704 -- This procedure updates the scenario working flag.
705 -- Only a single scenario per planning Cycle may hold this flag as true.
706 procedure update_scenario_working_flag
707 (
708 p_api_version IN NUMBER,
709 p_scenario_id IN NUMBER,
710 x_return_status OUT NOCOPY VARCHAR2,
711 x_msg_count OUT NOCOPY NUMBER,
712 x_msg_data OUT NOCOPY VARCHAR2
713 ) is
714
715 l_api_version CONSTANT NUMBER := 1.0;
716
717 begin
718
719 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
720 fnd_log.string
721 (
722 FND_LOG.LEVEL_PROCEDURE,
723 'fpa.sql.fpa_scenario_pvt.update_scenario_working_flag.begin',
724 'Entering fpa_scenario_pvt.update_scenario_working_flag.'
725 );
726 END IF;
727
728 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
729 fnd_log.string
730 (
731 FND_LOG.LEVEL_PROCEDURE,
732 'fpa.sql.fpa_scenario_pvt.update_scenario_working_flag',
733 'Unsetting any previous flags and setting new flag.'
734 );
735 END IF;
736
737 dbms_aw.execute('PUSH scenario_d');
738 dbms_aw.execute('PUSH planning_cycle_d');
739 dbms_aw.execute('oknullstatus = y');
740 dbms_aw.execute('limit scenario_d to ' || p_scenario_id);
741 dbms_aw.execute('limit planning_cycle_d to planning_cycle_scenario_r');
742 dbms_aw.execute('limit scenario_d to planning_cycle_d');
743 dbms_aw.execute('limit scenario_d keep scenario_working_m');
744 dbms_aw.execute('scenario_working_m = na');
745 dbms_aw.execute('limit scenario_d to ' || p_scenario_id);
746 dbms_aw.execute('scenario_working_m = yes');
747 dbms_aw.execute('POP scenario_d');
748 dbms_aw.execute('POP planning_cycle_d');
749
750 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
751 fnd_log.string
752 (
753 FND_LOG.LEVEL_PROCEDURE,
754 'fpa.sql.fpa_scenario_pvt.update_scenario_working_flag.end',
755 'Entering fpa_scenario_pvt.update_scenario_working_flag.'
756 );
757 END IF;
758
759 EXCEPTION
760 WHEN OTHERS THEN
761 --dbms_output.put_line(SQLERRM);
762 ROLLBACK;
763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
764 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
765 fnd_log.string
766 (
767 FND_LOG.LEVEL_ERROR,
768 'fpa.sql.fpa_scenario_pvt.update_scenario_working_flag',
769 SQLERRM
770 );
771 END IF;
772 FND_MSG_PUB.count_and_get
773 (
774 p_count => x_msg_count,
775 p_data => x_msg_data
776 );
777 RAISE;
778
779 end update_scenario_working_flag;
780
781 /*******************************************************************************************
782 *******************************************************************************************/
783 -- This procedure sets or unsets the recommended flag for a scenario. The parameters are
784 -- p_scenario_id for the Scenario Id to be updated, and p_scenario_reccom_status holding the
785 -- values 'yes' for recommending the scenario, or 'na' for unrecommending the scenario.
786
787 procedure update_scenario_reccom_flag
788 (
789 p_api_version IN NUMBER,
790 p_scenario_id IN NUMBER,
791 p_scenario_reccom_status IN VARCHAR2,
792 x_return_status OUT NOCOPY VARCHAR2,
793 x_msg_count OUT NOCOPY NUMBER,
794 x_msg_data OUT NOCOPY VARCHAR2
795 ) is
796
797 l_api_version CONSTANT NUMBER := 1.0;
798
799 begin
800
801 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
802 fnd_log.string
803 (
804 FND_LOG.LEVEL_PROCEDURE,
805 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_flag.begin',
806 'Entering fpa_scenario_pvt.update_scenario_reccom_flag.'
807 );
808 END IF;
809
810 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
811 fnd_log.string
812 (
813 FND_LOG.LEVEL_PROCEDURE,
814 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_flag',
815 'Unsetting any previous flags and setting new flag.'
816 );
817 END IF;
818
819 dbms_aw.execute('PUSH scenario_d');
820 dbms_aw.execute('lmt scenario_d to ' || p_scenario_id);
821 dbms_aw.execute('scenario_recommended_flag_m = ' || p_scenario_reccom_status);
822 dbms_aw.execute('POP scenario_d');
823
824 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
825 fnd_log.string
826 (
827 FND_LOG.LEVEL_PROCEDURE,
828 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_flag.end',
829 'Entering fpa_scenario_pvt.update_scenario_reccom_flag.'
830 );
831 END IF;
832
833 EXCEPTION
834 WHEN OTHERS THEN
835 --dbms_output.put_line(SQLERRM);
836 ROLLBACK;
837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
838 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
839 fnd_log.string
840 (
841 FND_LOG.LEVEL_ERROR,
842 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_flag',
843 SQLERRM
844 );
845 END IF;
846 FND_MSG_PUB.count_and_get
847 (
848 p_count => x_msg_count,
849 p_data => x_msg_data
850 );
851 RAISE;
852
853 end update_scenario_reccom_flag;
854
855 -- This procedure updates the recommending funding status relation for the projects within
856 -- a scenario.
857 -- This procedure is capable of updating a single project or multiple projects.
858 -- The parameter p_project_d must be of the following form:
859 -- '10001,' for a single project or (with trailing comma)
860 -- '10001, 10002, 10003,' for multiple projects. (each id separated with a comma, also
861 -- trailing coma.
862 procedure update_scenario_reccom_status
863 (
864 p_api_version IN NUMBER,
865 p_scenario_id IN NUMBER,
866 p_project_id IN VARCHAR2,
867 p_scenario_reccom_value IN VARCHAR2,
868 x_return_status OUT NOCOPY VARCHAR2,
869 x_msg_count OUT NOCOPY NUMBER,
870 x_msg_data OUT NOCOPY VARCHAR2
871 ) is
872
873 l_api_version CONSTANT NUMBER := 1.0;
874
875 l_project_id_string VARCHAR2(5000);
876
877 l_project_id VARCHAR2(10);
878
879 begin
880
881 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
882 fnd_log.string
883 (
884 FND_LOG.LEVEL_PROCEDURE,
885 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_status.begin',
886 'Entering fpa_scenario_pvt.update_scenario_reccom_status'
887 );
888 END IF;
889
890 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
891 fnd_log.string
892 (
893 FND_LOG.LEVEL_PROCEDURE,
894 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_status.begin',
895 'Limiting to scenario id passed.'
896 );
897 END IF;
898
899 dbms_aw.execute('PUSH scenario_d');
900 dbms_aw.execute('LMT scenario_d TO ' || p_scenario_id);
901
902 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
903 fnd_log.string
904 (
905 FND_LOG.LEVEL_PROCEDURE,
906 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_status.begin',
907 'Assing project id string passed to local string variable.'
908 );
909 END IF;
910
911 l_project_id_string := p_project_id;
912
913 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
914 fnd_log.string
915 (
916 FND_LOG.LEVEL_PROCEDURE,
917 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_status.begin',
918 'Loop over project id string and update recommended funding status relation.'
919 );
920 END IF;
921
922 WHILE (length(l_project_id_string) > 0) LOOP
923
924 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
925 fnd_log.string
926 (
927 FND_LOG.LEVEL_PROCEDURE,
928 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_status.begin',
929 'Current project string: ' || l_project_id_string || ' and project id: ' || l_project_id
930 );
931 END IF;
932
933 l_project_id := substr(l_project_id_string, 1, instr(l_project_id_string, ',') -1);
934 l_project_id_string := substr(l_project_id_string, (instr(l_project_id_string, ',') + 1));
935 --dbms_output.put_line('id: ' || l_project_id);
936 --dbms_output.put_line('string: ' || l_project_id_string);
937
938 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
939 fnd_log.string
940 (
941 FND_LOG.LEVEL_PROCEDURE,
942 'fpa.sql.fpa_scenario_pvt.',
943 'Updating FUNDING_STATUS_REC_SCENARIO_PROJECT_R relation for scenario: ' || p_scenario_id || ' and for project: ' || l_project_id
944 );
945 END IF;
946
947 dbms_aw.execute('funding_status_rec_scenario_project_r(project_d ' || l_project_id || ') = ''' || p_scenario_reccom_value || '''');
948
949 END LOOP;
950
951 dbms_aw.execute('POP scenario_d');
952
953 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
954 fnd_log.string
955 (
956 FND_LOG.LEVEL_PROCEDURE,
957 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_status.end',
958 'Exiting fpa_scenario_pvt.update_scenario_reccom_status.'
959 );
960 END IF;
961
962 EXCEPTION
963 WHEN OTHERS THEN
964 ROLLBACK;
965 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
966 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
967 fnd_log.string
968 (
969 FND_LOG.LEVEL_ERROR,
970 'fpa.sql.fpa_scenario_pvt.update_scenario_reccom_status',
971 SQLERRM
972 );
973 END IF;
974 FND_MSG_PUB.count_and_get
975 (
976 p_count => x_msg_count,
977 p_data => x_msg_data
978 );
979 RAISE;
980
981 end update_scenario_reccom_status;
982
983
984
985 -- Call OLAP Program to copy project data from initial scenario to current(target) scenario
986 -- If multiple projects are being added from UI, the p_project_id is passed as a string of project Ids.
987 -- delimited by space character.
988 -- This API is called from Add Projects page when Projects are added from Initial Scenario or Current Plan
989
990 PROCEDURE copy_sce_project_data
991 (
992 p_api_version IN NUMBER,
993 p_commit IN VARCHAR2,
994 p_target_scen_id IN NUMBER,
995 p_project_id_str IN VARCHAR2,
996 x_return_status OUT NOCOPY VARCHAR2,
997 x_msg_count OUT NOCOPY NUMBER,
998 x_msg_data OUT NOCOPY VARCHAR2
999 ) IS
1000 l_str varchar2(2000);
1001 source_scen_id number;
1002 BEGIN
1003
1004 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1005 fnd_log.string
1006 (
1007 FND_LOG.LEVEL_PROCEDURE,
1008 'fpa.sql.fpa_scenario_pvt.copy_sce_project_data.begin',
1009 'Entering fpa_scenario_pvt.copy_sce_project_data'
1010 );
1011 END IF;
1012
1013 -- get the initial scenario id. Always required, no matter what the source/mode is.
1014 -- if the source is pjp, project is selected from initial sce. and added to current sce.
1015 -- if source is pjt, get the project from current plan , add it to initial sce,
1016 -- and then invoke this api in pjp mode to add the same project to the current sce.
1017
1018 select scenario
1019 into source_scen_id
1020 from fpa_aw_sce_info_v
1021 where planning_cycle =
1022 (select planning_cycle from fpa_aw_sce_info_v where scenario = p_target_scen_id)
1023 and is_initial_scenario = 1 ;
1024
1025 l_str := 'call copy_proj_data_prg(' || p_target_scen_id || ', ' || source_scen_id || ', '' ' || p_project_id_str || ''')';
1026 dbms_aw.execute(l_str);
1027 --('call copy_proj_data_prg(' || p_target_scen_id || ' ' || source_scen_id || ' '' ' || p_project_id_str || ''')');
1028
1029 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1030 fnd_log.string
1031 (
1032 FND_LOG.LEVEL_PROCEDURE,
1033 'fpa.sql.fpa_scenario_pvt.copy_sce_project_data.end',
1034 'Entering fpa_scenario_pvt.copy_sce_project_data'
1035 );
1036 END IF;
1037
1038 EXCEPTION
1039 WHEN OTHERS THEN
1040 ROLLBACK;
1041 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1042 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1043 fnd_log.string
1044 (
1045 FND_LOG.LEVEL_ERROR,
1046 'fpa.sql.fpa_scenario_pvt.copy_sce_project_data',
1047 SQLERRM
1048 );
1049 END IF;
1050 FND_MSG_PUB.count_and_get
1051 (
1052 p_count => x_msg_count,
1053 p_data => x_msg_data
1054 );
1055 RAISE;
1056
1057 END copy_sce_project_data;
1058
1059 PROCEDURE remove_project_from_scenario
1060 (
1061 p_api_version IN NUMBER,
1062 p_commit IN VARCHAR2,
1063 p_scenario_id IN NUMBER,
1064 p_project_id IN NUMBER,
1065 x_return_status OUT NOCOPY VARCHAR2,
1066 x_msg_count OUT NOCOPY NUMBER,
1067 x_msg_data OUT NOCOPY VARCHAR2
1068 ) IS
1069
1070 l_str varchar2(2000);
1071 BEGIN
1072
1073 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1074 fnd_log.string
1075 (
1076 FND_LOG.LEVEL_PROCEDURE,
1077 'fpa.sql.fpa_scenario_pvt.remove_project_from_scenario.begin',
1078 'Entering fpa_scenario_pvt.remove_project_from_scenario'
1079 );
1080 END IF;
1081
1082 l_str := 'call remove_proj_from_sce_prg(' || p_scenario_id || ', ' || p_project_id || ')';
1083
1084 -- dbms_output.put_line(l_str);
1085 dbms_aw.execute(l_str);
1086
1087 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1088 fnd_log.string
1089 (
1090 FND_LOG.LEVEL_PROCEDURE,
1091 'fpa.sql.fpa_scenario_pvt.remove_project_from_scenario.end',
1092 'Entering fpa_scenario_pvt.remove_project_from_scenario'
1093 );
1094 END IF;
1095
1096 EXCEPTION
1097 WHEN OTHERS THEN
1098 ROLLBACK;
1099 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1100 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1101 fnd_log.string
1102 (
1103 FND_LOG.LEVEL_ERROR,
1104 'fpa.sql.fpa_scenario_pvt.remove_project_from_scenario',
1105 SQLERRM
1106 );
1107 END IF;
1108 FND_MSG_PUB.count_and_get
1109 (
1110 p_count => x_msg_count,
1111 p_data => x_msg_data
1112 );
1113 RAISE;
1114 END;
1115
1116
1117 PROCEDURE Update_Proj_User_Ranks
1118 ( p_api_version IN NUMBER,
1119 p_proj_metrics IN fpa_scen_proj_userrank_tbl,
1120 x_return_status OUT NOCOPY VARCHAR2,
1121 x_msg_data OUT NOCOPY VARCHAR2,
1122 x_msg_count OUT NOCOPY NUMBER )
1123 IS
1124 BEGIN
1125
1126 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1127 FND_LOG.String
1128 (
1129 FND_LOG.LEVEL_PROCEDURE,
1130 'fpa.sql.FPA_Scenario_Pvt.Update_Proj_User_Ranks.begin',
1131 'Entering FPA_Scenario_Pvt.Update_Proj_User_Ranks'
1132 );
1133 END IF;
1134
1135 --DBMS_OUTPUT.put_line(' ..p_proj_metrics(1).scenario=' || p_proj_metrics(1).scenarioID );
1136
1137 -- Update user ranking against project ID for the given scenario ID
1138 -- Limit Scenario ID
1139 dbms_aw.execute('LMT scenario_d TO ' || p_proj_metrics(1).scenarioID );
1140
1141 FOR i IN p_proj_metrics.FIRST..p_proj_metrics.LAST
1142 LOOP
1143
1144 -- DBMS_OUTPUT.put_line(' ..Inside For loop...p_proj_metrics(i).project=' || p_proj_metrics(i).projectID );
1145 -- DBMS_OUTPUT.put_line(' ..Inside For loop...p_proj_metrics(i).user_rank=' || p_proj_metrics(i).user_rank );
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.end',
1151 'FPA_Scenario_Pvt.Update_Proj_User_Ranks: Inside for loop:projectID='||p_proj_metrics(i).projectID
1152 ||' user_rank='||p_proj_metrics(i).user_rank
1153 );
1154 END IF;
1155
1156 -- Limit project_d
1157 dbms_aw.execute('LMT project_d TO ' || p_proj_metrics(i).projectID );
1158
1159 -- Set the the user rank for each project
1160 IF p_proj_metrics(i).user_rank IS NULL THEN
1161 dbms_aw.execute( 'scenario_project_user_rank_m = na');
1162 ELSE
1163 dbms_aw.execute( 'scenario_project_user_rank_m = ' || p_proj_metrics(i).user_rank );
1164 END IF;
1165
1166 END LOOP;
1167
1168
1169 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1170 FND_LOG.String
1171 (
1172 FND_LOG.LEVEL_PROCEDURE,
1173 'fpa.sql.FPA_Scenario_Pvt.Update_Proj_User_Ranks.end',
1174 'Exiting FPA_Scenario_Pvt.Update_Proj_User_Ranks'
1175 );
1176 END IF;
1177
1178 EXCEPTION
1179 WHEN OTHERS THEN
1180 --DBMS_OUTPUT.put_line('...Inside Object level API EXCEPTION block...');
1181
1182 ROLLBACK;
1183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1184
1185 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
1186 FND_LOG.String
1187 (
1188 FND_LOG.LEVEL_ERROR,
1189 'fpa.sql.FPA_Scenario_Pvt.Update_Proj_User_Ranks',
1190 SQLERRM
1191 );
1192 END IF;
1193
1194 FND_MSG_PUB.count_and_get
1195 (
1196 p_count => x_msg_count,
1197 p_data => x_msg_data
1198 );
1199 RAISE;
1200
1201 END Update_Proj_User_Ranks;
1202
1203 /*******************************************************************************************
1204 *******************************************************************************************/
1205 -- This procedures call the program CALC_SCE_ALL_DATA_PRG. This is an AW program
1206 -- which recalculates all Scenario data.
1207 -- The parameter p_data_to_calc to this procedure is used to tell the AW program
1208 -- what level of data to calculate.
1209 -- For more information on this parameter refer to the documentation for CALC_SCE_ALL_DATA_PRG
1210 -- program.
1211 PROCEDURE calc_scenario_data
1212 (
1213 p_api_version IN NUMBER,
1214 p_scenario_id IN NUMBER,
1215 p_project_id IN NUMBER,
1216 p_class_code_id IN NUMBER,
1217 p_data_to_calc IN VARCHAR2,
1218 x_return_status OUT NOCOPY VARCHAR2,
1219 x_msg_count OUT NOCOPY NUMBER,
1220 x_msg_data OUT NOCOPY VARCHAR2
1221 ) is
1222
1223 l_api_version CONSTANT NUMBER := 1.0;
1224
1225 begin
1226
1227 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1228 fnd_log.string
1229 (
1230 FND_LOG.LEVEL_PROCEDURE,
1231 'fpa.sql.fpa_scenario_pvt.calc_scenario_data.begin',
1232 'Entering fpa_scenario_pvt.calc_scenario_data'
1233 );
1234 END IF;
1235
1236 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1237 fnd_log.string
1238 (
1239 FND_LOG.LEVEL_PROCEDURE,
1240 'fpa.sql.fpa_scenario_pvt.calc_scenario_data.',
1241 'Calling AW program CALC_SCE_ALL_DATA_PRG.'
1242 );
1243 END IF;
1244
1245 if p_project_id is null and p_class_code_id is null
1246 then dbms_aw.execute('call calc_sce_all_data_prg(' || p_scenario_id || ' na na ''' || p_data_to_calc || ''')');
1247 elsif p_project_id is null
1248 then dbms_aw.execute('call calc_sce_all_data_prg(' || p_scenario_id || ' na ' || p_class_code_id || '''' || p_data_to_calc || ''')');
1249 elsif p_class_code_id is null
1250 then dbms_aw.execute('call calc_sce_all_data_prg(' || p_scenario_id || ' ' || p_project_id || ' na ''' || p_data_to_calc || ''')');
1251 end if;
1252
1253 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1254 fnd_log.string
1255 (
1256 FND_LOG.LEVEL_PROCEDURE,
1257 'fpa.sql.fpa_scenario_pvt.calc_scenario_data.end',
1258 'Entering fpa_scenario_pvt.calc_scenario_data'
1259 );
1260 END IF;
1261
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 ROLLBACK;
1265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1266 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1267 fnd_log.string
1268 (
1269 FND_LOG.LEVEL_ERROR,
1270 'fpa.sql.fpa_scenario_pvt.calc_scenario_data',
1271 SQLERRM
1272 );
1273 END IF;
1274 FND_MSG_PUB.count_and_get
1275 (
1276 p_count => x_msg_count,
1277 p_data => x_msg_data
1278 );
1279 RAISE;
1280
1281 END calc_scenario_data;
1282
1283
1284 END fpa_scenario_pvt;