1 package body FPA_Investment_Criteria_PVT as
2 /* $Header: FPAVINVB.pls 120.7 2007/11/29 05:44:02 kjai ship $ */
3
4 -- The procedure Create_StrategicObj_Objects_AW creates the AW objects related to
5 -- strategic objectives: strategic_obj_d
6 -- strategic_obj_h
7 -- strategic_obj_weight_m
8 -- However, this procedure may not be used at all since the objects above must be seeded
9 -- in order to have the necessary views on top of them.
10 /*
11 PROCEDURE create_strategicobj_objects_AW
12 (
13 p_commit IN VARCHAR2 := FND_API.G_FALSE,
14 p_Investment_rec_type IN FPA_Investment_Criteria_PVT.Investment_rec_type,
15 x_return_status OUT NOCOPY VARCHAR2,
16 x_msg_count OUT NOCOPY NUMBER,
17 x_msg_data OUT NOCOPY VARCHAR2
18 )
19 IS
20
21 BEGIN
22
23 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
24 fnd_log.string
25 (
26 FND_LOG.LEVEL_PROCEDURE,
27 'fpa.sql.fpa_resourcetype_pvt.create_resourcetype.begin',
28 'Entering fpa_resourcetype_pvt.create_resourcetype'
29 );
30 END IF;
31
32 -- if flag eq 'Y' create the strategic objective dimension.
33 IF (p_Investment_rec_type.create_strategic_obj_d = 'Y') THEN
34 dbms_aw.execute('define strategic_obj_d dimension text');
35 END IF;
36
37 -- if flag eq 'Y' create the strategic objective relation.
38 IF (p_Investment_rec_type.create_strategic_obj_h = 'Y') THEN
39 dbms_aw.execute('define strategic_obj_h relation strategic_obj_d <strategic_obj_d>');
40 END IF;
41
42 -- if flag eq 'Y' create the strategic_obj_weight_m measure.
43 IF (p_Investment_rec_type.create_strategic_obj_weight_m = 'Y') THEN
44 dbms_aw.execute('define strategic_obj_weight_m <strategic_obj_d> decimal');
45 END IF;
46
47 -- if flag eq 'Y' create the strategic_obj_score_m measure.
48 IF (p_Investment_rec_type.create_strategic_obj_score_m = 'Y') THEN
49 dbms_aw.Execute('define strategic_obj_score_m <strategic_obj_d> decimal');
50 END IF;
51
52 -- if flag eq 'Y' create the strategic_obj_wscore_m measure.
53 IF (p_investment_rec_type.create_strategic_obj_wscore_m = 'Y') THEN
54 dbms_aw.xecute('define strategic_obj_wscore_m <strategic_obj_d> decimal');
55 END IF;
56
57 -- if flag eq 'Y' create the strategic_obj_status_r measure.
58 IF (p_Investment_rec_type.create_strategic_obj_status_r = 'Y') THEN
59 DBMS_AW.Execute('define strategic_obj_status_r relation attribute_library_d <strategic_obj_d>');
60 END IF;
61
62 IF (p_commit = FND_API.G_TRUE) THEN
63 DBMS_AW.Execute('UPDATE');
64 COMMIT;
65 END IF;
66
67
68 EXCEPTION
69 WHEN OTHERS THEN
70 rollback;
71 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
72 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
73 ,p_data => x_msg_data);
74 raise;
75
76 END Create_StrategicObj_Objects_AW;
77 */
78 /*******************************************************************************************
79 *******************************************************************************************/
80
81 -- The procedure Create_StrategicObj_AW creates dimension values into strategic_obj_d
82 -- dimension in AW.
83 -- In release 1 this procedure will mainly be used at installation, where a sql script
84 -- will call this procedure to create the necessary strategic objectives.
85 -- This procedure only creates the strategic objective in AW, in order to create the
86 -- same object if RDBMS a different procedure must be called.
87 -- This procedure also updates the strategic objective hierarchy.
88 -- This procedure is used for SEEDING values at implementation and for creating values at
89 -- run time. When SEEDING values we do not use the sequence generator. Thus we need to
90 -- if procedure called for SEEDING or for run time use. We use the p_seeding parameter
91 -- for this purpose.
92 PROCEDURE create_strategicobj_aw
93 (
94 p_commit IN VARCHAR2 := FND_API.G_FALSE,
95 p_investment_rec_type IN FPA_Investment_Criteria_PVT.Investment_rec_type,
96 p_seeding IN VARCHAR2,
97 x_strategic_obj_id OUT NOCOPY VARCHAR2,
98 x_return_status OUT NOCOPY VARCHAR2,
99 x_msg_count OUT NOCOPY NUMBER,
100 x_msg_data OUT NOCOPY VARCHAR2
101 )
102 IS
103
104 -- A cursor to get the new unique id for the Strategic Objective.
105 CURSOR l_strobj_s_csr
106 IS
107 SELECT fpa_strategic_obj_s.nextval AS l_strobj_id
108 FROM dual;
109
110 -- A record to hold the new sequence value
111 l_strobj_s_r l_strobj_s_csr%ROWTYPE;
112
113 -- A variable to hold language
114 l_language varchar2(4);
115
116 -- A cursor to hold language code
117 CURSOR l_language_csr
118 IS
119 SELECT language_code
120 FROM fnd_languages
121 WHERE installed_flag IN ('I','B');
122
123 BEGIN
124
125 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
126 fnd_log.string
127 (
128 FND_LOG.LEVEL_PROCEDURE,
129 'fpa.sql.fpa_investment_criteria_pvt.create_strategicobj_aw.begin',
130 'Entering fpa_investment_criteria_pvt.create_strategicobj_aw'
131 );
132 END IF;
133
134
135 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
136 fnd_log.string
137 (FND_LOG.LEVEL_PROCEDURE,
138 'fpa.sql.fpa_investment_criteria_pvt.create_strategicobj_aw',
139 'Evaluating p_seeding parameter'
140 );
141 END IF;
142
143 if (upper(p_seeding) = 'Y') then
144 x_strategic_obj_id := p_investment_rec_type.strategic_obj_shortname;
145 else
146 -- Get the next sequence value for the strategic objective identifier
147 OPEN l_strobj_s_csr;
148 FETCH l_strobj_s_csr INTO l_strobj_s_r;
149 CLOSE l_strobj_s_csr;
150
151 -- We return the id of the new Strategic Objective to the caller
152 x_strategic_obj_id := l_strobj_s_r.l_strobj_id;
153 end if;
154
155 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
156 fnd_log.string
157 (FND_LOG.LEVEL_PROCEDURE,
158 'fpa.sql.fpa_investment_criteria_pvt.create_strategicobj_aw',
159 'Inserting into FPA_OBJECTS_TL'
160 );
161 END IF;
162
163 -- Open the cursor and insert p_strategic_obj_name, pc_desc for each language code
164 -- insert into fpa_objects_tl only when user creates str. obj from the UI.
165 -- Seed data insertion should happen using ldt files. Do not execute this insert in seed data mode
166 if (upper(p_seeding) <> 'Y') then
167 OPEN l_language_csr;
168 LOOP
169 FETCH l_language_csr INTO l_language;
170 EXIT WHEN l_language_csr%NOTFOUND;
171
172 INSERT INTO FPA_OBJECTS_TL( object
173 ,id
174 ,name
175 ,description
176 ,LANGUAGE
177 ,SOURCE_LANG
178 ,created_by
179 ,creation_date
180 ,last_updated_by
181 ,last_update_date
182 ,last_update_login)
183 VALUES( 'INVESTMENT_CRITERIA'
184 ,x_strategic_obj_id
185 ,p_investment_rec_type.strategic_obj_name
186 ,p_investment_rec_type.strategic_obj_desc
187 ,l_language
188 ,USERENV('LANG')
189 ,0
190 ,sysdate()
191 ,0
192 ,sysdate()
193 ,0);
194
195 END LOOP;
196 CLOSE l_language_csr;
197 end if;
198 -- add the strategic objective to the strategic objective dimension.
199 dbms_aw.execute('maintain strategic_obj_d add '
200 || x_strategic_obj_id);
201
202 -- if parent value is not null then add the parent to the
203 -- strategic objective hierarchy.
204 IF (p_investment_rec_type.strategic_obj_parent IS NOT NULL) THEN
205 dbms_aw.Execute('strategic_obj_h(strategic_obj_d '
206 || x_strategic_obj_id || ') = '
207 || p_Investment_rec_type.strategic_obj_parent);
208 END IF;
209
210 EXCEPTION
211 WHEN OTHERS THEN
212 ROLLBACK;
213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
214 FND_MSG_PUB.Count_And_Get
215 (
216 p_count => x_msg_count,
217 p_data => x_msg_data
218 );
219 RAISE;
220
221 END create_strategicobj_aw;
222
223 /*******************************************************************************************
224 *******************************************************************************************/
225
226 -- The procedure Delete_StrategicObj_AW deletes the individual Strategic Objectives from
227 -- the AW space.
228 PROCEDURE delete_strategicobj_aw
229 (
230 p_api_version IN NUMBER,
231 p_investment_rec_type IN fpa_investment_criteria_pvt.investment_rec_type,
232 x_return_status OUT NOCOPY VARCHAR2,
233 x_msg_count OUT NOCOPY NUMBER,
234 x_msg_data OUT NOCOPY VARCHAR2
235 )
236 IS
237
238 l_api_version CONSTANT NUMBER := 1.0;
239
240 BEGIN
241
242 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
243 fnd_log.string
244 (
245 FND_LOG.LEVEL_PROCEDURE,
246 'fpa.sql.fpa_resourcetype_pvt.create_resourcetype.begin',
247 'Entering fpa_resourcetype_pvt.create_resourcetype'
248 );
249 END IF;
250
251 -- Delete the Strategic Objective from the AW space.
252 dbms_aw.Execute('maintain strategic_obj_d delete ' || p_Investment_rec_type.strategic_obj_shortname);
253
254 --Delete from FPA_OBJECTS_TL
255 delete from FPA_OBJECTS_TL
256 where object = 'INVESTMENT_CRITERIA'
257 and id = p_Investment_rec_type.strategic_obj_shortname;
258
259
260 EXCEPTION
261 WHEN OTHERS THEN
262 ROLLBACK;
263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
264 FND_MSG_PUB.Count_And_Get
265 (
266 p_count => x_msg_count,
267 p_data => x_msg_data
268 );
269 RAISE;
270
271 END delete_strategicobj_aw;
272
273 /*******************************************************************************************
274 *******************************************************************************************/
275
276 -- The procedure Update_StrategicObj updates the name and description for the
277 -- investment criteria.
278
279 PROCEDURE update_strategicobj
280 (
281 p_commit IN VARCHAR2 := FND_API.G_FALSE,
282 p_investment_rec_type IN FPA_Investment_Criteria_PVT.Investment_rec_type,
283 x_return_status OUT NOCOPY VARCHAR2,
284 x_msg_count OUT NOCOPY NUMBER,
285 x_msg_data OUT NOCOPY VARCHAR2
286 )
287 IS
288
289 BEGIN
290
291 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
292 fnd_log.string
293 ( FND_LOG.LEVEL_PROCEDURE,
294 'fpa.sql.fpa_investment_criteria_pvt.update_strategicobj.begin',
295 'Entering fpa_investment_criteria_pvt.update_strategicobj');
296 END IF;
297
298 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
299 fnd_log.string
300 (FND_LOG.LEVEL_PROCEDURE,
301 'fpa.sql.fpa_investment_criteria_pvt.update_strategicobj.begin',
302 'Updating FPA_OBJECTS_TL for the investment criteria');
303 END IF;
304
305 update FPA_OBJECTS_TL
306 set name = p_investment_rec_type.strategic_obj_name
307 ,description = p_investment_rec_type.strategic_obj_desc
308 ,SOURCE_LANG = userenv('LANG')
309 ,last_update_date = sysdate
310 where id = p_investment_rec_type.strategic_obj_shortname
311 and object = 'INVESTMENT_CRITERIA'
312 and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
313
314 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
315 fnd_log.string
316 ( FND_LOG.LEVEL_PROCEDURE,
317 'fpa.sql.fpa_investment_criteria_pvt.update_strategicobj.end',
318 'Exiting fpa_investment_criteria_pvt.update_strategicobj');
319 END IF;
320
321
322
323 EXCEPTION
324 WHEN OTHERS THEN
325 ROLLBACK;
326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
327 FND_MSG_PUB.Count_And_Get
328 (
329 p_count => x_msg_count,
330 p_data => x_msg_data
331 );
332 RAISE;
333
334 end Update_StrategicObj;
335
336 /*******************************************************************************************
337 *******************************************************************************************/
338
339 -- The procedure Update_StrategicObj_Status_AW updates the status of the strategic
340 -- objectives in AW. This used mainly to tell Portfolio which objectives have been seeded.
341 -- Used by UI for the switcher bean for Add and Delete.
342
343 PROCEDURE update_strategicobj_status_aw
344 (
345 p_commit IN VARCHAR2 := FND_API.G_FALSE,
346 p_investment_rec_type IN FPA_Investment_Criteria_PVT.Investment_rec_type,
347 x_return_status OUT NOCOPY VARCHAR2,
348 x_msg_count OUT NOCOPY NUMBER,
349 x_msg_data OUT NOCOPY VARCHAR2
350 )
351 IS
352
353 BEGIN
354
355 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
356 fnd_log.string
357 (
358 FND_LOG.LEVEL_PROCEDURE,
359 'fpa.sql.fpa_resourcetype_pvt.create_resourcetype.begin',
360 'Entering fpa_resourcetype_pvt.create_resourcetype'
361 );
362 END IF;
363
364
365 -- Set the strategic_obj_status_r relation according to passed values.
366 dbms_aw.execute('strategic_obj_status_r(strategic_obj_d '
367 || p_Investment_rec_type.strategic_obj_shortname || ') = '''
368 || p_Investment_rec_type.strategic_obj_status || '''');
369
370 EXCEPTION
371 WHEN OTHERS THEN
372 ROLLBACK;
373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
374 FND_MSG_PUB.Count_And_Get
375 (
376 p_count => x_msg_count,
377 p_data => x_msg_data
378 );
379 RAISE;
380
381 end update_strategicobj_status_aw;
382
383 /*******************************************************************************
384 *******************************************************************************/
385
386 PROCEDURE update_strategicobj_level_aw
387 (
388 p_commit IN VARCHAR2 := FND_API.G_FALSE,
389 p_investment_rec_type IN FPA_Investment_Criteria_PVT.Investment_rec_type,
390 x_return_status OUT NOCOPY VARCHAR2,
391 x_msg_count OUT NOCOPY NUMBER,
392 x_msg_data OUT NOCOPY VARCHAR2
393 ) is
394
395 begin
396
397 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
398 fnd_log.string
399 (
400 FND_LOG.LEVEL_PROCEDURE,
401 'fpa.sql.FPA_Investment_Criteria_PVT.update_strategicobj_level_aw.begin',
402 'Entering FPA_Investment_Criteria_PVT.update_strategicobj_level_aw'
403 );
404 END IF;
405
406
407 -- Set the invest_criteria_level_r relation according to passed values.
408 dbms_aw.execute('invest_criteria_level_r(strategic_obj_d '
409 || p_Investment_rec_type.strategic_obj_shortname || ') = '''
410 || p_Investment_rec_type.strategic_obj_level || '''');
411
412 EXCEPTION
413 WHEN OTHERS THEN
414 ROLLBACK;
415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
416 FND_MSG_PUB.Count_And_Get
417 (
418 p_count => x_msg_count,
419 p_data => x_msg_data
420 );
421 RAISE;
422
423 end update_strategicobj_level_aw;
424
425 /*******************************************************************************
426 *******************************************************************************/
427
428 -- The procedure Update_StrategicObj_Weight_AW updates the Strategic Objective
429 -- Weight measure in AW.Update_StrategicObj_Weight_AW. (strategic_obj_weight_m)
430 PROCEDURE update_strategicobj_weight_aw
431 (
432 p_Investment_rec_type IN FPA_Investment_Criteria_PVT.Investment_rec_type,
433 x_return_status OUT NOCOPY varchar2,
434 x_msg_count OUT NOCOPY number,
435 x_msg_data OUT NOCOPY varchar2
436 )
437 IS
438
439 l_api_version CONSTANT NUMBER := 1.0;
440
441 l_investment_rec_type FPA_Investment_Criteria_PVT.Investment_rec_type;
442
443 l_objective_string VARCHAR2(5000); -- used to hold current obj string.
444 l_temp_string VARCHAR2(500); -- string to hold current obj and score.
445
446 l_temp VARCHAR2(1000);
447
448 BEGIN
449
450 -- copy passed record into local record.
451 l_Investment_rec_type := p_Investment_rec_type;
452
453 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
454 fnd_log.string
455 (
456 FND_LOG.LEVEL_PROCEDURE,
457 'fpa.sql.fpa_resourcetype_pvt.create_resourcetype.begin',
458 'Entering fpa_resourcetype_pvt.create_resourcetype'
459 );
460 END IF;
461
462 -- Attach the AW space read write.
463 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
464 fnd_log.string
465 (
466 FND_LOG.LEVEL_STATEMENT,
467 'fpa.sql.fpa_resourcetype_pvt.create_resourcetype',
468 'Attaching OLAP workspace: '
469 );
470 END IF;
471
472 -- At this point we parse the string containing all Objective shortnames and
473 -- their respective weights.
474 l_objective_string := l_investment_rec_type.strategic_scores_string;
475 WHILE (length(l_objective_string) > 0) LOOP
476
477 -- Get first objective and weight.
478 l_temp_string := substr(l_objective_string, 1, instr(l_objective_string, ';'));
479
480 -- Get the shortname for the objective.
481 l_Investment_rec_type.strategic_obj_shortname :=
482 substr(l_temp_string, 1, (instr(l_temp_string, ':')-1));
483
484 -- Get the weight for this objective.
485 l_Investment_rec_type.strategic_obj_weight :=
486 replace(rtrim(substr(l_temp_string,
487 (instr(l_temp_string,':')+1)), ';'), '%', '');
488
489 -- limit the strategic objective dimension
490 DBMS_AW.Execute('lmt strategic_obj_d to '
491 || l_Investment_rec_type.strategic_obj_shortname);
492
493 -- Set the strategic_obj_weight_m value equal to the one passed.
494 -- First check if there is a valid score.
495 if (l_Investment_rec_type.strategic_obj_weight is null) then
496 l_Investment_rec_type.strategic_obj_weight := 0;
497 end if;
498 DBMS_AW.Execute('strategic_obj_weight_m = ' || l_Investment_rec_type.strategic_obj_weight);
499
500 l_objective_string := substr(l_objective_string, (instr(l_objective_string, ';') + 1));
501 END LOOP;
502
503 EXCEPTION
504 WHEN OTHERS THEN
505 ROLLBACK;
506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
507 FND_MSG_PUB.Count_And_Get
508 (
509 p_count => x_msg_count,
510 p_data => x_msg_data
511 );
512 RAISE;
513
514 END update_strategicobj_weight_aw;
515
516 -- This procedure updates the Strategic Objective Scores for the Project Type
517 -- We will add all scores for all Projects and then we will take the average.
518 PROCEDURE Update_ProjectTypeObjScore_AW
519 (
520 p_commit IN VARCHAR2 := FND_API.G_FALSE,
521 p_Investment_rec_type IN FPA_Investment_Criteria_PVT.Investment_rec_type,
522 x_return_status OUT NOCOPY VARCHAR2,
523 x_msg_count OUT NOCOPY NUMBER,
524 x_msg_data OUT NOCOPY VARCHAR2
525 )
526 IS
527
528 BEGIN
529
530 -- Attach the AW space read write.
531
532 -- limit the project dimension to the current project
533 DBMS_AW.Execute('lmt project_type_d to '''
534 || p_Investment_rec_type.project_type_shortname || '''');
535
536 -- limit the project dimension to the current project
537 DBMS_AW.Execute('lmt scenario_d to '''
538 || p_Investment_rec_type.scenario_shortname || '''');
539
540 -- limit the project to the ones under the same project type.
541 DBMS_AW.Execute('lmt project_d to project_type_d');
542 -- keep projects which belong to the current scenario.
543 DBMS_AW.Execute('lmt project_d keep scenario_project_m');
544 -- limit strategic objective dimension to all.
545 DBMS_AW.Execute('limit strategic_obj_d to all');
546 -- sum all scores into current project type and then divide it by
547 -- the number of projects in status.
548 DBMS_AW.Execute('scenario_project_type_obj_score_m = '
549 || ' total(scenario_project_obj_score_m, strategic_obj_d)/statlen(project_d)');
550
551 IF (p_commit = FND_API.G_TRUE) THEN
552 DBMS_AW.Execute('update');
553 COMMIT;
554 END IF;
555
556
557 EXCEPTION
558 WHEN OTHERS THEN
559 ROLLBACK;
560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
561 FND_MSG_PUB.Count_And_Get
562 (
563 p_count => x_msg_count,
564 p_data => x_msg_data
565 );
566 RAISE;
567
568 END update_projecttypeobjscore_aw;
569
570 /*******************************************************************************************
571 *******************************************************************************************/
572 /*
573 -- The procedure Update_StrategicObj_WScore_AW updates the Strategic Weighted Score measure
574 -- in AW. (strategic_obj_wscore_m).
575 PROCEDURE update_strategicobj_wscore_aw
576 (
577 p_api_version IN NUMBER,
578 p_commit IN VARCHAR2 := FND_API.G_FALSE,
579 p_Investment_rec_type IN FPA_Investment_Criteria_PVT.Investment_rec_type,
580 x_return_status OUT NOCOPY VARCHAR2,
581 x_msg_count OUT NOCOPY NUMBER,
582 x_msg_data OUT NOCOPY VARCHAR2
583 )
584 IS
585
586 l_api_version CONSTANT number := 1.0;
587
588 BEGIN
589
590 -- Attach the AW space read write.
591
592 -- limit the project dimension to the current project
593 DBMS_AW.Execute('lmt project_d to ''' || p_Investment_rec_type.project_shortname || '''');
594
595 -- limit the project dimension to the current project
596 DBMS_AW.Execute('lmt scenario_d to ''' || p_Investment_rec_type.scenario_shortname || '''');
597
598 -- Set the strategic_obj_weight_m value equal to the one passed.
599 DBMS_AW.Execute('scenario_project_obj_wscore_m = (strategic_obj_weight_m/100) * (scenario_project_obj_score_m)');
600
601 IF (p_commit = FND_API.G_TRUE) THEN
602 DBMS_AW.Execute('update');
603 COMMIT;
604 END IF;
605
606
607 EXCEPTION
608 WHEN OTHERS THEN
609 ROLLBACK;
610 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
611 FND_MSG_PUB.Count_And_Get
612 (
613 p_count => x_msg_count,
614 p_data => x_msg_data
615 );
616 RAISE;
617
618 END update_strategicobj_wscore_aw;
619 */
620 /*******************************************************************************************
621 *******************************************************************************************/
622 /*
623 -- This procedure Updates the Weighted Objective Scores for Project Types.
624 PROCEDURE Update_ProjectTypeObjWScore_AW
625 (
626 p_api_version IN NUMBER,
627 p_commit IN VARCHAR2 := FND_API.G_FALSE,
628 p_investment_rec_type IN FPA_Investment_Criteria_PVT.Investment_rec_type,
629 x_return_status OUT NOCOPY VARCHAR2,
630 x_msg_count OUT NOCOPY NUMBER,
631 x_msg_data OUT NOCOPY VARCHAR2
632 )
633 IS
634
635 l_api_version CONSTANT NUMBER := 1.0;
636
637 BEGIN
638
639 -- Attach the AW space read write
640
641 -- limit scenario to the one passed.
642 DBMS_AW.Execute('lmt scenario_d to '''
643 || p_Investment_rec_type.scenario_shortname || '''');
644
645 -- limit project to the one passed.
646 DBMS_AW.Execute('lmt project_d to '''
647 || p_Investment_rec_type.project_shortname || '''');
648
649 -- limit project type to the one for the given project.
650 DBMS_AW.Execute('lmt project_type_d to project_d');
651
652 -- limit projects to the ones belonging to the current project type.
653 DBMS_AW.Execute('lmt project_d to project_type_d');
654
655 -- keep those projects belonging to the current scenario.
656 DBMS_AW.Execute('lmt project_d keep scenario_project_m');
657
658 -- limit strategic objectives to all.
659 DBMS_AW.Execute('lmt strategic_obj_d to all');
660
661 -- Get the Weighted Score for all projects into the project type and divide by the number of
662 -- projects.
663 DBMS_AW.Execute('scenario_project_type_obj_wscore_m =
664 total(scenario_project_obj_wscore_m, strategic_obj_d)/statlen(project_d)');
665
666 IF (p_commit = FND_API.G_TRUE) THEN
667 DBMS_AW.Execute('update');
668 COMMIT;
669 END IF;
670
671
672 EXCEPTION
673 WHEN OTHERS THEN
674 ROLLBACK;
675 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
676 FND_MSG_PUB.Count_And_Get
677 (
678 p_count => x_msg_count,
679 p_data => x_msg_data
680 );
681 RAISE;
682
683 END update_projecttypeobjwscore_aw;
684 */
685 /*******************************************************************************************
686 *******************************************************************************************/
687 /*
688 -- The procedure Update_StrategicObj_AScore_AW updates the Strategic Objective Average Score
689 -- for the Groups. We use the same variable as the individual objectives since the
690 -- individual scores are not entered at the Group level.
691 PROCEDURE Update_StrategicObj_AScore_AW
692 (
693 p_api_version IN number,
694 p_commit IN varchar2 := FND_API.G_FALSE,
695 p_Investment_rec_type IN FPA_Investment_Criteria_PVT.Investment_rec_type,
696 x_return_status OUT NOCOPY varchar2,
697 x_msg_count OUT NOCOPY number,
698 x_msg_data OUT NOCOPY varchar2
699 )
700 IS
701
702 l_api_version CONSTANT number := 1.0;
703
704 -- Define Cursor type.
705 TYPE obj_csr_type IS REF CURSOR;
706 l_obj_csr obj_csr_type;
707
708 l_sql varchar2(1000);
709 l_obj_member_name varchar2(30);
710
711 l_obj_group_tab_id number;
712 l_temp_tab_id number;
713 l_obj_count number;
714
715 BEGIN
716
717 -- Get the tab id for the Top Level Tab - Strategic Alignment. We know the shortname
718 -- for this tab because it is seeded.
719 select tab_id
720 into l_obj_group_tab_id
721 from fpa_inv_criteria_vl
722 where shortname = 'FPASTRALIGN';
723
724 -- build query to get children of FPASTRALIGN
725 l_sql := 'select tab_id ' ||
726 ' from fpa_inv_criteria_vl ' ||
727 ' where parent_tab_id = ' || l_obj_group_tab_id;
728
729 -- Attach the AW space read write.
730
731 -- limit the project dimension to the current project
732 DBMS_AW.Execute('lmt project_d to ''' || p_Investment_rec_type.project_shortname || '''');
733
734 -- limit the scnearioproject dimension to the current project
735 DBMS_AW.Execute('lmt scenario_d to ''' || p_Investment_rec_type.scenario_shortname || '''');
736
737 -- execute cursor.
738 open l_obj_csr for l_sql;
739 loop
740 -- fetch values into the variable.
741 fetch l_obj_csr into l_temp_tab_id;
742 exit when l_obj_csr%NOTFOUND;
743
744 -- execute another query to get number of children of current Strategic group
745 -- and name of Strategic group.
746 select distinct count(a.tab_id), b.shortname
747 into l_obj_count, l_obj_member_name
748 from fpa_inv_criteria_vl a, fpa_inv_criteria_vl b
749 where a.parent_tab_id = l_temp_tab_id
750 and b.tab_id = l_temp_tab_id
751 group by b.shortname;
752
753 -- limit strategic objective to the children of the current Group.
754 DBMS_AW.Execute('limit strategic_obj_d to ''' || l_obj_member_name || '''');
755 DBMS_AW.Execute('limit strategic_obj_d to children using strategic_obj_h');
756 -- set the average score of the Group
757 DBMS_AW.Execute('scenario_project_obj_score_m(strategic_obj_d ''' || l_obj_member_name || ''') = total(scenario_project_obj_score_m)/' || l_obj_count || '');
758
759 -- set the sum of weighted scores
760 DBMS_AW.Execute('scenario_project_obj_wscore_m(strategic_obj_d ''' || l_obj_member_name || ''') = total(scenario_project_obj_wscore_m)');
761
762 -- We must set the status of the dimension equal to all. Views based on AW use the current
763 -- dimension status.
764 DBMS_AW.Execute('limit strategic_obj_d to all');
765
766 end loop;
767
768 -- Now we get the average of all five strategic objectives.
769 DBMS_AW.Execute('lmt strategic_obj_d to ''FPASTRALIGN''');
770 DBMS_AW.Execute('limit strategic_obj_d to children using strategic_obj_h');
771 DBMS_AW.Execute('scenario_project_obj_score_m(strategic_obj_d ''FPASTRALIGN'') = total(scenario_project_obj_score_m)/5');
772
773 DBMS_AW.Execute('update');
774
775 if (p_commit = FND_API.G_TRUE) then
776 commit;
777 end if;
778
779
780 EXCEPTION
781 WHEN OTHERS THEN
782 ROLLBACK;
783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
784 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
785 ,p_data => x_msg_data);
786 RAISE;
787
788 END update_strategicobj_ascore_aw;
789 */
790 /*******************************************************************************************
791 *******************************************************************************************/
792
793 /*
794 -- Update the Average Score for the Project Type for the particular Objective Group.
795 procedure Update_ProjectTypeObjAScore_AW(
796 p_commit IN varchar2 := FND_API.G_FALSE
797 ,p_Investment_rec_type IN FPA_Investment_Criteria_PVT.Investment_rec_type
798 ,x_return_status OUT NOCOPY varchar2
799 ,x_msg_count OUT NOCOPY number
800 ,x_msg_data OUT NOCOPY varchar2
801 ) is
802
803 begin
804
805 -- Attach the AW space read write
806 DBMS_AW.Execute('aw attach ' || p_Investment_rec_type.AW_space || ' rw first');
807
808 -- limit project type to the one passed.
809 DBMS_AW.Execute('lmt project_type_d to ''' || p_Investment_rec_type.project_type_shortname || '''');
810
811 -- limit scenario to the one passed.
812 DBMS_AW.Execute('lmt scenario_d to ''' || p_Investment_rec_type.scenario_shortname || '''');
813
814 -- limit projects to the ones belonging to the current project type.
815 DBMS_AW.Execute('lmt project_d to project_type_d');
816
817 -- keep those projects belonging to the current scenario.
818 DBMS_AW.Execute('lmt project_d keep scenario_project_m');
819
820 -- limit strategic objectives to all.
821 DBMS_AW.Execute('lmt strategic_obj_d to all');
822
823 -- Get the average score of all projects into the project type and divide it by the
824 -- number of projects in status.
825 DBMS_AW.Execute('scenario_project_type_obj
826
827
828 EXCEPTION
829 WHEN FND_API.G_EXC_ERROR THEN
830 DBMS_AW.Execute('aw detach ' || p_Investment_rec_type.AW_space);
831 rollback;
832 x_return_status := FND_API.G_RET_STS_ERROR;
833 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
834 ,p_data => x_msg_data);
835 raise;
836 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
837 DBMS_AW.Execute('aw detach ' || p_Investment_rec_type.AW_space);
838 rollback;
839 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
840 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
841 ,p_data => x_msg_data);
842 raise;
843 WHEN NO_DATA_FOUND THEN
844 DBMS_AW.Execute('aw detach ' || p_Investment_rec_type.AW_space);
845 rollback;
846 x_return_status := FND_API.G_RET_STS_ERROR;
847 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
848 ,p_data => x_msg_data);
849 raise;
850 WHEN OTHERS THEN
851 DBMS_AW.Execute('aw detach ' || p_Investment_rec_type.AW_space);
852 rollback;
853 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
854 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
855 ,p_data => x_msg_data);
856 raise;
857
858 end Update_ProjectTypeObjAScore_AW;
859
860 */
861 /*******************************************************************************************
862 *******************************************************************************************/
863 /*
864 PROCEDURE Rollup_StrategicObj_WScore_AW
865 (
866 p_api_version IN NUMBER,
867 p_commit IN VARCHAR2 := FND_API.G_FALSE,
868 p_Investment_rec_type IN FPA_Investment_Criteria_PVT.Investment_rec_type,
869 x_return_status OUT NOCOPY VARCHAR2,
870 x_msg_count OUT NOCOPY NUMBER,
871 x_msg_data OUT NOCOPY VARCHAR2
872 )
873 IS
874
875 l_api_version CONSTANT number := 1.0;
876
877 BEGIN
878
879 -- Attach the AW space read write.
880
881 -- limit the project dimension to the current project
882 DBMS_AW.Execute('lmt project_d to ''' || p_Investment_rec_type.project_shortname || '''');
883
884 -- limit the project dimension to the current project
885 DBMS_AW.Execute('lmt scenario_d to ''' || p_Investment_rec_type.scenario_shortname || '''');
886
887 -- get the leaf nodes only
888 DBMS_AW.Execute('lmt strategic_obj_d to all');
889 DBMS_AW.Execute('lmt strategic_obj_d remove ancestors using strategic_obj_h');
890
891 -- get the next level parents.
892 DBMS_AW.Execute('lmt strategic_obj_d add parents using strategic_obj_h');
893
894 -- rollup the weighted score
895 DBMS_AW.Execute('rollup scenario_project_obj_wscore_m over strategic_obj_d using strategic_obj_h');
896
897 -- remove the leaf nodes to weight the rolled up weighted scores.
898 DBMS_AW.Execute('lmt strategic_obj_d remove descendants using strategic_obj_h');
899
900 -- weight the score
901 DBMS_AW.Execute('scenario_project_obj_wscore_m = scenario_project_obj_wscore_m * (strategic_obj_weight_m/100)');
902
903 -- Now we will calculate the next level
904 -- get the next level parents.
905 DBMS_AW.Execute('lmt strategic_obj_d add parents using strategic_obj_h');
906
907 -- rollup the weighted score
908 DBMS_AW.Execute('rollup scenario_project_obj_wscore_m over strategic_obj_d using strategic_obj_h');
909
910 -- At this point we are done rolling up data and weighting it. If by some
911 -- reason more levels are added to the hierarchy this will have to be
912 -- revistited.
913
914 IF (p_commit = FND_API.G_TRUE) THEN
915 DBMS_AW.Execute('update');
916 COMMIT;
917 END IF;
918
919
920 EXCEPTION
921 WHEN OTHERS THEN
922 ROLLBACK;
923 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
924 FND_MSG_PUB.Count_And_Get
925 (
926 p_count => x_msg_count,
927 p_data => x_msg_data
928 );
929 RAISE;
930
931 END rollup_strategicobj_wscore_aw;
932 */
933 /*******************************************************************************
934 *******************************************************************************/
935 -------------------------------------------------------------------------------
936 -- API create_strategicobj_aw , Overloaded API which does not take
937 -- FPA_Investment_Criteria_PVT.Investment_rec_type as a parameter
938 --
939 -- Params
940 -- p_commit IN VARCHAR2
941 -- p_seeding IN VARCHAR2
942 -- x_strategic_obj_id OUT NOCOPY VARCHAR2
943 -- x_return_status OUT NOCOPY VARCHAR2
944 -- x_msg_count OUT NOCOPY NUMBER
945 -- x_msg_data OUT NOCOPY VARCHAR2
946 -- p_strategic_obj_shortname IN NUMBER
947 -- p_strategic_obj_desc IN VARCHAR2
948 -- p_strategic_obj_name IN VARCHAR2
949 -- p_strategic_obj_level IN VARCHAR2
950 -- p_strategic_obj_parent IN VARCHAR2
951 -------------------------------------------------------------------------------
952 PROCEDURE create_strategicobj_aw
953 (
954 p_commit IN VARCHAR2 := FND_API.G_FALSE,
955 p_seeding IN VARCHAR2,
956 p_strategic_obj_shortname IN NUMBER,
957 p_strategic_obj_desc IN VARCHAR2,
958 p_strategic_obj_name IN VARCHAR2,
959 p_strategic_obj_level IN VARCHAR2,
960 p_strategic_obj_parent IN VARCHAR2,
961 x_strategic_obj_id OUT NOCOPY VARCHAR2,
962 x_return_status OUT NOCOPY VARCHAR2,
963 x_msg_count OUT NOCOPY NUMBER,
964 x_msg_data OUT NOCOPY VARCHAR2
965 )
966 IS
967 l_Investment_rec_type FPA_Investment_Criteria_PVT.Investment_rec_type;
968 BEGIN
969 l_Investment_rec_type.strategic_obj_shortname := p_strategic_obj_shortname;
970 l_Investment_rec_type.strategic_obj_desc := p_strategic_obj_desc;
971 l_Investment_rec_type.strategic_obj_name := p_strategic_obj_name;
972 l_Investment_rec_type.strategic_obj_level := p_strategic_obj_level;
973 l_investment_rec_type.strategic_obj_parent := p_strategic_obj_parent;
974 create_strategicobj_aw ( p_commit => p_commit,
975 p_investment_rec_type => l_investment_rec_type,
976 p_seeding => p_commit,
977 x_strategic_obj_id => x_strategic_obj_id,
978 x_return_status => x_return_status,
979 x_msg_count => x_msg_count,
980 x_msg_data => x_msg_data );
981 END create_strategicobj_aw;
982
983 --------------------------------------------------------------------------------
984 -- API update_strategicobj_status_aw , Overloaded API which does not take
985 -- FPA_Investment_Criteria_PVT.Investment_rec_type as a parameter
986 --
987 -- Params
988 -- p_commit IN VARCHAR2
989 -- x_return_status OUT NOCOPY VARCHAR2
990 -- x_msg_count OUT NOCOPY NUMBER
991 -- x_msg_data OUT NOCOPY VARCHAR2
992 -- p_strategic_obj_shortname IN NUMBER
993 -- p_strategic_obj_desc IN VARCHAR2
994 -- p_strategic_obj_name IN VARCHAR2
995 -- p_strategic_obj_level IN VARCHAR2
996 -- p_strategic_obj_parent IN VARCHAR2
997 -- p_strategic_obj_status IN VARCHAR2
998 --------------------------------------------------------------------------------
999 PROCEDURE update_strategicobj_status_aw
1000 (
1001 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1002 p_strategic_obj_shortname IN NUMBER,
1003 p_strategic_obj_desc IN VARCHAR2,
1004 p_strategic_obj_name IN VARCHAR2,
1005 p_strategic_obj_level IN VARCHAR2,
1006 p_strategic_obj_parent IN VARCHAR2,
1007 p_strategic_obj_status IN VARCHAR2,
1008 x_return_status OUT NOCOPY VARCHAR2,
1009 x_msg_count OUT NOCOPY NUMBER,
1010 x_msg_data OUT NOCOPY VARCHAR2
1011 )
1012 IS
1013 l_Investment_rec_type FPA_Investment_Criteria_PVT.Investment_rec_type;
1014 BEGIN
1015 l_Investment_rec_type.strategic_obj_shortname := p_strategic_obj_shortname;
1016 l_Investment_rec_type.strategic_obj_desc := p_strategic_obj_desc;
1017 l_Investment_rec_type.strategic_obj_name := p_strategic_obj_name;
1018 l_Investment_rec_type.strategic_obj_level := p_strategic_obj_level;
1019 l_investment_rec_type.strategic_obj_parent := p_strategic_obj_parent;
1020 l_investment_rec_type.strategic_obj_status := p_strategic_obj_status;
1021 update_strategicobj_status_aw( p_commit => p_commit,
1022 p_investment_rec_type => l_Investment_rec_type,
1023 x_return_status => x_return_status,
1024 x_msg_count => x_msg_count,
1025 x_msg_data => x_msg_data);
1026 END update_strategicobj_status_aw;
1027
1028 -------------------------------------------------------------------------------
1029 -- API update_strategicobj_status_aw , Overloaded API which does not take
1030 -- FPA_Investment_Criteria_PVT.Investment_rec_type as a parameter
1031 --
1032 -- Params
1033 -- p_commit IN VARCHAR2
1034 -- x_return_status OUT NOCOPY VARCHAR2
1035 -- x_msg_count OUT NOCOPY NUMBER
1036 -- x_msg_data OUT NOCOPY VARCHAR2
1037 -- p_strategic_obj_shortname IN NUMBER
1038 -- p_strategic_obj_desc IN VARCHAR2
1039 -- p_strategic_obj_name IN VARCHAR2
1040 -- p_strategic_obj_level IN VARCHAR2
1041 -- p_strategic_obj_parent IN VARCHAR2
1042 -------------------------------------------------------------------------------
1043 PROCEDURE update_strategicobj_level_aw
1044 (
1045 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1046 p_strategic_obj_shortname IN NUMBER,
1047 p_strategic_obj_desc IN VARCHAR2,
1048 p_strategic_obj_name IN VARCHAR2,
1049 p_strategic_obj_level IN VARCHAR2,
1050 p_strategic_obj_parent IN VARCHAR2,
1051 x_return_status OUT NOCOPY VARCHAR2,
1052 x_msg_count OUT NOCOPY NUMBER,
1053 x_msg_data OUT NOCOPY VARCHAR2
1054 )
1055 IS
1056 l_Investment_rec_type FPA_Investment_Criteria_PVT.Investment_rec_type;
1057 BEGIN
1058 l_Investment_rec_type.strategic_obj_shortname := p_strategic_obj_shortname;
1059 l_Investment_rec_type.strategic_obj_desc := p_strategic_obj_desc;
1060 l_Investment_rec_type.strategic_obj_name := p_strategic_obj_name;
1061 l_Investment_rec_type.strategic_obj_level := p_strategic_obj_level;
1062 l_investment_rec_type.strategic_obj_parent := p_strategic_obj_parent;
1063 update_strategicobj_level_aw ( p_commit => p_commit,
1064 p_investment_rec_type => l_Investment_rec_type,
1065 x_return_status => x_return_status,
1066 x_msg_count => x_msg_count,
1067 x_msg_data => x_msg_data);
1068 END update_strategicobj_level_aw;
1069
1070 END fpa_investment_criteria_pvt;
[Home] [Help]