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
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:
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
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:
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:
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:
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:
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:
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:
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
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
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:
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
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:
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:
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
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
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: )
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:
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:
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;
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:
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
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:
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
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:
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.
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:
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
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:
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:
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
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:
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:
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
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
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:
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
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:
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:
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
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
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:
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
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:
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:
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
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
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:
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
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:
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:
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
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
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:
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
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:
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:
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
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
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:
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
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:
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:
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:
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:
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:
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:
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
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
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:
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.
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:
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
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
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:
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 || ')';
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:
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
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
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:
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 );
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; */
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
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 );
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:
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
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
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;
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:
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:
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
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:
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:
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
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:
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:
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
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
1323:
1324: END calc_scenario_data;
1325:
1326:
1327: END fpa_scenario_pvt;