[Home] [Help]
PACKAGE BODY: APPS.FPA_SCORECARDS_PVT
Source
1 package body FPA_SCORECARDS_PVT as
2 /* $Header: FPAVSCRB.pls 120.6 2011/03/29 21:44:12 skkoppul ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(200) := 'FPA_SCORECARDS_PVT';
5 G_APP_NAME CONSTANT VARCHAR2(3) := FPA_UTILITIES_PVT.G_APP_NAME;
6 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
7 L_API_NAME CONSTANT VARCHAR2(35) := 'SCORECARDS';
8
9
10 PROCEDURE insert_tl_rec(
11 p_init_msg_list IN VARCHAR2,
12 p_scorecards_tl_rec IN FPA_SCORECARDS_TL_REC,
13 x_msg_count OUT NOCOPY NUMBER,
14 x_msg_data OUT NOCOPY VARCHAR2,
15 x_return_status OUT NOCOPY VARCHAR2
16 ) IS
17
18 l_api_version CONSTANT NUMBER := 1;
19 l_api_name CONSTANT VARCHAR2(30) := 'insert_tl_rec';
20 l_return_status VARCHAR2(1) := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
21 l_scorecards_tl_rec FPA_SCORECARDS_TL_REC := p_scorecards_tl_rec;
22 l_msg_log VARCHAR2(2000) := null;
23
24 CURSOR get_languages IS
25 SELECT *
26 FROM FND_LANGUAGES
27 WHERE INSTALLED_FLAG IN ('I', 'B');
28
29 -----------------------------------------
30 -- Set_Attributes for:FPA_SCORECARDS_TL --
31 -----------------------------------------
32 FUNCTION Set_Attributes (
33 p_scorecards_tl_rec IN FPA_SCORECARDS_TL_REC,
34 x_scorecards_tl_rec OUT NOCOPY FPA_SCORECARDS_TL_REC
35 ) RETURN VARCHAR2 IS
36 l_return_status VARCHAR2(1) := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
37 BEGIN
38 x_scorecards_tl_rec := p_scorecards_tl_rec;
39
40 x_scorecards_tl_rec.LANGUAGE := USERENV('LANG');
41 x_scorecards_tl_rec.SOURCE_LANG := USERENV('LANG');
42 x_scorecards_tl_rec.CREATED_BY := FND_GLOBAL.USER_ID;
43 x_scorecards_tl_rec.CREATION_DATE := SYSDATE;
44 x_scorecards_tl_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
45 x_scorecards_tl_rec.LAST_UPDATE_DATE := SYSDATE;
46 x_scorecards_tl_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
47
48 RETURN(l_return_status);
49
50 END Set_Attributes;
51
52 BEGIN
53 FPA_UTILITIES_PVT.START_ACTIVITY(
54 p_api_name => l_api_name,
55 p_pkg_name => G_PKG_NAME,
56 p_init_msg_list => p_init_msg_list,
57 p_msg_log => 'Entering Fpa_Scorecards_Pvt.insert_tl_rec');
58
59 --- Setting item attributes
60 l_return_status := Set_Attributes(
61 p_scorecards_tl_rec,
62 l_scorecards_tl_rec);
63
64 FOR l_lang_rec IN get_languages LOOP
65 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'l_lang_rec.language_code '||l_lang_rec.language_code);
66 l_scorecards_tl_rec.LANGUAGE := l_lang_rec.language_code;
67 l_scorecards_tl_rec.SOURCE_LANG := l_lang_rec.language_code; --bug 11875056
68 INSERT INTO FPA_SCORECARDS_TL(
69 project_id,
70 strategic_obj_id,
71 scenario_id,
72 comments,
73 language,
74 source_lang,
75 created_by,
76 creation_date,
77 last_updated_by,
78 last_update_date,
79 last_update_login)
80 VALUES (
81 l_scorecards_tl_rec.project_id,
82 l_scorecards_tl_rec.strategic_obj_id,
83 l_scorecards_tl_rec.scenario_id,
84 l_scorecards_tl_rec.comments,
85 l_scorecards_tl_rec.language,
86 l_scorecards_tl_rec.source_lang,
87 l_scorecards_tl_rec.created_by,
88 l_scorecards_tl_rec.creation_date,
89 l_scorecards_tl_rec.last_updated_by,
90 l_scorecards_tl_rec.last_update_date,
91 l_scorecards_tl_rec.last_update_login);
92
93 END LOOP;
94
95 x_return_status := l_return_status;
96
97 FPA_UTILITIES_PVT.END_ACTIVITY(
98 p_api_name => l_api_name,
99 p_pkg_name => G_PKG_NAME,
100 p_msg_log => null,
101 x_msg_count => x_msg_count,
102 x_msg_data => x_msg_data);
103
104 EXCEPTION
105
106 when OTHERS then
107 -- to change for using call with no rollback
108 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
109 p_api_name => l_api_name,
110 p_pkg_name => G_PKG_NAME,
111 p_exc_name => 'OTHERS',
112 p_msg_log => l_msg_log||SQLERRM,
113 x_msg_count => x_msg_count,
114 x_msg_data => x_msg_data,
115 p_api_type => G_API_TYPE);
116
117 END insert_tl_rec;
118
119
120
121 PROCEDURE update_tl_rec(
122 p_init_msg_list IN VARCHAR2,
123 p_scorecards_tl_rec IN FPA_SCORECARDS_TL_REC,
124 x_msg_count OUT NOCOPY NUMBER,
125 x_msg_data OUT NOCOPY VARCHAR2,
126 x_return_status OUT NOCOPY VARCHAR2
127 ) IS
128
129 l_api_version CONSTANT NUMBER := 1;
130 l_api_name CONSTANT VARCHAR2(30) := 'update_tl_rec';
131 l_return_status VARCHAR2(1) := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
132 l_scorecards_tl_rec FPA_SCORECARDS_TL_REC := p_scorecards_tl_rec;
133 l_msg_log VARCHAR2(2000) := null;
134 -----------------------------------------
135 -- Set_Attributes for:FPA_SCORECARDS_TL --
136 -----------------------------------------
137 FUNCTION Set_Attributes (
138 p_scorecards_tl_rec IN FPA_SCORECARDS_TL_REC,
139 x_scorecards_tl_rec OUT NOCOPY FPA_SCORECARDS_TL_REC
140 ) RETURN VARCHAR2 IS
141 l_return_status VARCHAR2(1) := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
142 BEGIN
143 x_scorecards_tl_rec := p_scorecards_tl_rec;
144
145 x_scorecards_tl_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
146 x_scorecards_tl_rec.LAST_UPDATE_DATE := SYSDATE;
147 x_scorecards_tl_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
148
149 RETURN(l_return_status);
150
151 END Set_Attributes;
152
153 BEGIN
154 FPA_UTILITIES_PVT.START_ACTIVITY(
155 p_api_name => l_api_name,
156 p_pkg_name => G_PKG_NAME,
157 p_init_msg_list => p_init_msg_list,
158 p_msg_log => 'Entering Fpa_Scorecards_Pvt.update_tl_rec');
159
160 --- Setting item attributes
161 l_return_status := Set_Attributes(
162 p_scorecards_tl_rec,
163 l_scorecards_tl_rec);
164
165 UPDATE fpa_scorecards_tl
166 SET comments = l_scorecards_tl_rec.comments,
167 last_updated_by = l_scorecards_tl_rec.last_updated_by,
168 last_update_date = l_scorecards_tl_rec.last_update_date,
169 last_update_login = l_scorecards_tl_rec.last_update_login
170 WHERE project_id = l_scorecards_tl_rec.project_id AND
171 scenario_id = l_scorecards_tl_rec.scenario_id AND
172 strategic_obj_id = l_scorecards_tl_rec.strategic_obj_id AND
173 language = userenv('LANG') AND
174 source_lang = userenv('LANG');
175
176 x_return_status := l_return_status;
177
178 FPA_UTILITIES_PVT.END_ACTIVITY(
179 p_api_name => l_api_name,
180 p_pkg_name => G_PKG_NAME,
181 p_msg_log => null,
182 x_msg_count => x_msg_count,
183 x_msg_data => x_msg_data);
184
185 EXCEPTION
186
187 when OTHERS then
188 -- to change for using call with no rollback
189 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
190 p_api_name => l_api_name,
191 p_pkg_name => G_PKG_NAME,
192 p_exc_name => 'OTHERS',
193 p_msg_log => l_msg_log||SQLERRM,
194 x_msg_count => x_msg_count,
195 x_msg_data => x_msg_data,
196 p_api_type => G_API_TYPE);
197
198 END update_tl_rec;
199
200
201
202 PROCEDURE Update_Calc_Pjt_Scorecard_Aw
203 (
204 p_api_version IN NUMBER,
205 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
206 p_commit IN VARCHAR2 := FND_API.G_FALSE,
207 p_planning_cycle_id IN NUMBER,
208 p_project_id IN NUMBER,
209 p_scorecard_tbl IN FPA_SCORECARDS_PVT.FPA_SCORECARD_TBL_TYPE,
210 x_return_status OUT NOCOPY VARCHAR2,
211 x_msg_count OUT NOCOPY NUMBER,
212 x_msg_data OUT NOCOPY VARCHAR2
213 ) IS
214
215 -- standard parameters
216 l_return_status VARCHAR2(1);
217 l_api_name CONSTANT VARCHAR2(30) := 'Update_Calc_Pjt_Score';
218 l_api_version CONSTANT NUMBER := 1.0;
219 l_msg_log VARCHAR2(2000) := null;
220 ----------------------------------------------------------------------------
221
222 i NUMBER := 0;
223 l_scorecard_rec FPA_SCORECARDS_PVT.FPA_SCORECARD_REC_TYPE;
224 l_tl_exists VARCHAR2(1) := null;
225 l_scorecards_tl_rec FPA_SCORECARDS_TL_REC;
226
227 cursor check_comments_csr (p_project_id IN NUMBER,
228 p_strategic_obj_id IN NUMBER) IS
229 select 'T'
230 from fpa_scorecards_tl
231 where project_id = p_project_id
232 and strategic_obj_id = p_strategic_obj_id
233 and scenario_id = -1
234 and language = USERENV('LANG')
235 and source_lang = USERENV('LANG');
236
237
238 BEGIN
239
240 -- l_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
241
242 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
243 -- call START_ACTIVITY to create savepoint, check compatibility
244 -- and initialize message list
245 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
246 p_api_name => l_api_name,
247 p_pkg_name => G_PKG_NAME,
248 p_init_msg_list => p_init_msg_list,
249 l_api_version => l_api_version,
250 p_api_version => p_api_version,
251 p_api_type => G_API_TYPE,
252 p_msg_log => 'Entering Fpa_Scorecards_Pvt.Update_Calc_Pjt_Scorecard_Aw',
253 x_return_status => x_return_status);
254
255 -- check if activity started successfully
256 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
257 l_msg_log := 'start_activity';
258 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
259 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
260 l_msg_log := 'start_activity';
261 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
262 end if;
263
264 if(p_scorecard_tbl.count > 0) then
265
266 dbms_aw.execute('LMT project_d TO ' || p_project_id );
267
268 l_scorecards_tl_rec.project_id := p_project_id;
269 l_scorecards_tl_rec.scenario_id := -1;
270
271
272 for i in p_scorecard_tbl.FIRST..p_scorecard_tbl.LAST
273 loop
274
275 l_scorecard_rec := p_scorecard_tbl(i);
276
277 l_tl_exists := null;
278 open check_comments_csr(p_project_id,
279 l_scorecard_rec.strategic_obj_id);
280 fetch check_comments_csr into l_tl_exists;
281 close check_comments_csr;
282
283 l_scorecards_tl_rec.strategic_obj_id := l_scorecard_rec.strategic_obj_id;
284 l_scorecards_tl_rec.comments := l_scorecard_rec.comments;
285
286 if (l_tl_exists is not null and l_tl_exists = FND_API.G_TRUE) then
287
288 update_tl_rec(
289 p_init_msg_list => p_init_msg_list,
290 p_scorecards_tl_rec => l_scorecards_tl_rec,
291 x_msg_count => x_msg_count,
292 x_msg_data => x_msg_data,
293 x_return_status => l_return_status
294 );
295
296 else
297
298 insert_tl_rec(
299 p_init_msg_list => p_init_msg_list,
300 p_scorecards_tl_rec => l_scorecards_tl_rec,
301 x_msg_count => x_msg_count,
302 x_msg_data => x_msg_data,
303 x_return_status => l_return_status
304 );
305
306 end if;
307
308 --DIMENSION investment_criteria from strategic_obj_d
309 dbms_aw.execute('LMT strategic_obj_d TO ' || l_scorecard_rec.strategic_obj_id );
310
311 if(l_scorecard_rec.new_score is null) then
312 dbms_aw.execute('project_entered_obj_score_m = NA' );
313 else
314 dbms_aw.execute('project_entered_obj_score_m = ' || FND_NUMBER.number_to_canonical(l_scorecard_rec.new_score));
315 end if;
316
317 end loop;
318
319 dbms_aw.execute('CALL CALC_PROJ_SCORECARD_PRG(' ||
320 p_planning_cycle_id || ',' ||
321 p_project_id || ')' );
322 -- Overwrite Root and Financial Category weighted score
323 dbms_aw.execute('LMT strategic_obj_d TO strategic_obj_d le 2');
324 dbms_aw.execute('project_strategic_obj_weights_score_m = NA' );
325
326 end if;
327 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
328
329
330 FPA_UTILITIES_PVT.END_ACTIVITY(
331 p_api_name => l_api_name,
332 p_pkg_name => G_PKG_NAME,
333 p_msg_log => null,
334 x_msg_count => x_msg_count,
335 x_msg_data => x_msg_data);
336
337
338 EXCEPTION
339 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
340 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
341 p_api_name => l_api_name,
342 p_pkg_name => G_PKG_NAME,
343 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
344 p_msg_log => l_msg_log,
345 x_msg_count => x_msg_count,
346 x_msg_data => x_msg_data,
347 p_api_type => G_API_TYPE);
348
349 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
350 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
351 p_api_name => l_api_name,
352 p_pkg_name => G_PKG_NAME,
353 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
354 p_msg_log => l_msg_log,
355 x_msg_count => x_msg_count,
356 x_msg_data => x_msg_data,
357 p_api_type => G_API_TYPE);
358
359 when OTHERS then
360 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
361 p_api_name => l_api_name,
362 p_pkg_name => G_PKG_NAME,
363 p_exc_name => 'OTHERS',
364 p_msg_log => l_msg_log||SQLERRM,
365 x_msg_count => x_msg_count,
366 x_msg_data => x_msg_data,
367 p_api_type => G_API_TYPE);
368
369 END Update_Calc_Pjt_Scorecard_Aw;
370
371
372 PROCEDURE Update_Calc_Scen_Scorecard_Aw
373 (
374 p_api_version IN NUMBER,
375 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
376 p_commit IN VARCHAR2 := FND_API.G_FALSE,
377 p_planning_cycle_id IN NUMBER,
378 p_scenario_id IN NUMBER,
379 p_project_id IN NUMBER,
380 p_scorecard_tbl IN FPA_SCORECARDS_PVT.FPA_SCORECARD_TBL_TYPE,
381 x_return_status OUT NOCOPY VARCHAR2,
382 x_msg_count OUT NOCOPY NUMBER,
383 x_msg_data OUT NOCOPY VARCHAR2
384 ) IS
385
386 -- standard parameters
387 l_return_status VARCHAR2(1);
388 l_api_name CONSTANT VARCHAR2(30) := 'Update_Calc_Scen_Score';
389 l_api_version CONSTANT NUMBER := 1.0;
390 l_msg_log VARCHAR2(2000) := null;
391 ----------------------------------------------------------------------------
392
393 i NUMBER := 0;
394 l_scorecard_rec FPA_SCORECARDS_PVT.FPA_SCORECARD_REC_TYPE;
395 l_scorecards_tl_rec FPA_SCORECARDS_TL_REC;
396 l_tl_exists VARCHAR2(1) := null;
397
398 cursor check_comments_csr (p_project_id IN NUMBER,
399 p_scenario_id IN NUMBER,
400 p_strategic_obj_id IN NUMBER) IS
401 select 'T'
402 from fpa_scorecards_tl
403 where project_id = p_project_id
404 and strategic_obj_id = p_strategic_obj_id
405 and scenario_id = p_scenario_id
406 and language = USERENV('LANG')
407 and source_lang = USERENV('LANG');
408
409 BEGIN
410
411 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
412 -- call START_ACTIVITY to create savepoint, check compatibility
413 -- and initialize message list
414 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
415 p_api_name => l_api_name,
416 p_pkg_name => G_PKG_NAME,
417 p_init_msg_list => p_init_msg_list,
418 l_api_version => l_api_version,
419 p_api_version => p_api_version,
420 p_api_type => G_API_TYPE,
421 p_msg_log => 'Entering Fpa_Scorecards_Pvt.Update_Calc_Scen_Scorecard_Aw',
422 x_return_status => x_return_status);
423
424 -- check if activity started successfully
425 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
426 l_msg_log := 'start_activity';
427 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
428 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
429 l_msg_log := 'start_activity';
430 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
431 end if;
432
433 if(p_scorecard_tbl.count > 0) then
434
435 dbms_aw.execute('LMT project_d TO ' || p_project_id );
436 dbms_aw.execute('LMT scenario_d TO ' || p_scenario_id );
437
438 l_scorecards_tl_rec.project_id := p_project_id;
439 l_scorecards_tl_rec.scenario_id := p_scenario_id;
440
441 for i in p_scorecard_tbl.FIRST..p_scorecard_tbl.LAST
442 loop
443
444 l_scorecard_rec := p_scorecard_tbl(i);
445
446 l_tl_exists := null;
447 open check_comments_csr(p_project_id,
448 p_scenario_id,
449 l_scorecard_rec.strategic_obj_id);
450
451 fetch check_comments_csr into l_tl_exists;
452 close check_comments_csr;
453
454 l_scorecards_tl_rec.strategic_obj_id := l_scorecard_rec.strategic_obj_id;
455 l_scorecards_tl_rec.comments := l_scorecard_rec.comments;
456
457 if (l_tl_exists is not null and l_tl_exists = FND_API.G_TRUE) then
458
459 update_tl_rec(
460 p_init_msg_list => p_init_msg_list,
461 p_scorecards_tl_rec => l_scorecards_tl_rec,
462 x_msg_count => x_msg_count,
463 x_msg_data => x_msg_data,
464 x_return_status => l_return_status
465 );
466 else
467
468 insert_tl_rec(
469 p_init_msg_list => p_init_msg_list,
470 p_scorecards_tl_rec => l_scorecards_tl_rec,
471 x_msg_count => x_msg_count,
472 x_msg_data => x_msg_data,
473 x_return_status => l_return_status
474 );
475
476 end if;
477
478 --DIMENSION investment_criteria from strategic_obj_d
479 dbms_aw.execute('LMT strategic_obj_d TO ' || l_scorecard_rec.strategic_obj_id );
480
481 if(l_scorecard_rec.new_score is null) then
482 dbms_aw.execute('scenario_project_obj_score_m = NA' );
483 else
484 dbms_aw.execute('scenario_project_obj_score_m = ' || FND_NUMBER.number_to_canonical(round(l_scorecard_rec.new_score,2)));
485 end if;
486
487 end loop;
488
489
490
491 dbms_aw.execute('CALL CALC_SCEN_SCORECARD_PRG(' ||
492 p_planning_cycle_id || ',' ||
493 p_scenario_id || ',' ||
494 p_project_id || ')' );
495 -- Overwrite Root and Financial Category weighted score
496 dbms_aw.execute('LMT strategic_obj_d TO strategic_obj_d le 2');
497 dbms_aw.execute('scenario_project_obj_wscore_m = NA' );
498
499 dbms_aw.execute('CALL CALC_SCE_COST_WSCORES_PRG(' ||
500 p_scenario_id || ')' );
501
502
503 dbms_aw.execute('CALL CALC_SCE_CLASS_COST_WSCORES_PRG(' ||
504 p_scenario_id || ',' ||
505 'na,' ||
506 p_project_id || ')' );
507
508 end if;
509 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
510
511 FPA_UTILITIES_PVT.END_ACTIVITY(
512 p_api_name => l_api_name,
513 p_pkg_name => G_PKG_NAME,
514 p_msg_log => null,
515 x_msg_count => x_msg_count,
516 x_msg_data => x_msg_data);
517
518
519 EXCEPTION
520 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
521 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
522 p_api_name => l_api_name,
523 p_pkg_name => G_PKG_NAME,
524 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
525 p_msg_log => l_msg_log,
526 x_msg_count => x_msg_count,
527 x_msg_data => x_msg_data,
528 p_api_type => G_API_TYPE);
529
530 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
531 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
532 p_api_name => l_api_name,
533 p_pkg_name => G_PKG_NAME,
534 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
535 p_msg_log => l_msg_log,
536 x_msg_count => x_msg_count,
537 x_msg_data => x_msg_data,
538 p_api_type => G_API_TYPE);
539
540 when OTHERS then
541 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
542 p_api_name => l_api_name,
543 p_pkg_name => G_PKG_NAME,
544 p_exc_name => 'OTHERS',
545 p_msg_log => l_msg_log||SQLERRM,
546 x_msg_count => x_msg_count,
547 x_msg_data => x_msg_data,
548 p_api_type => G_API_TYPE);
549
550 END Update_Calc_Scen_Scorecard_Aw;
551
552
553 PROCEDURE Calc_Scenario_Wscores_Aw
554 (
555 p_api_version IN NUMBER,
556 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
557 p_scenario_id IN NUMBER,
558 x_return_status OUT NOCOPY VARCHAR2,
559 x_msg_count OUT NOCOPY NUMBER,
560 x_msg_data OUT NOCOPY VARCHAR2
561 ) IS
562
563 -- standard parameters
564 l_return_status VARCHAR2(1);
565 l_api_name CONSTANT VARCHAR2(30) := 'Calc_Scenario_Wscores_Aw';
566 l_api_version CONSTANT NUMBER := 1.0;
567 l_msg_log VARCHAR2(2000) := null;
568 ----------------------------------------------------------------------------
569 l_init_scenario_id NUMBER := null;
570 l_planning_cycle_id NUMBER := null;
571
572 CURSOR INIT_SCENARIO_CSR (P_SCENARIO_ID IN NUMBER) IS
573 SELECT
574 PC.PLANNING_CYCLE, SCEI.SCENARIO
575 FROM
576 FPA_AW_SCE_INFO_V SCEI, FPA_AW_SCE_INFO_V SCE, FPA_AW_SCES_V PC
577 WHERE
578 SCEI.IS_INITIAL_SCENARIO = 1
579 AND SCE.PLANNING_CYCLE = PC.PLANNING_CYCLE
580 AND SCEI.PLANNING_CYCLE = PC.PLANNING_CYCLE
581 AND PC.SCENARIO = SCE.SCENARIO
582 AND SCE.SCENARIO = P_SCENARIO_ID;
583
584 PROCEDURE Calc_Scenario_Wscores(
585 p_planning_cycle_id IN NUMBER,
586 p_scenario_id IN NUMBER) IS
587 BEGIN
588
589 dbms_aw.execute('CALL CALC_SCEN_SCORECARD_PRG(' ||
590 l_planning_cycle_id || ',' ||
591 p_scenario_id || ',NA)' );
592
593 dbms_aw.execute('CALL CALC_SCE_COST_WSCORES_PRG(' ||
594 p_scenario_id || ')' );
595
596 dbms_aw.execute('CALL CALC_SCE_CLASS_COST_WSCORES_PRG(' ||
597 p_scenario_id || ', NA, NA)' );
598
599 END Calc_Scenario_Wscores;
600
601 BEGIN
602
603 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
604 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
605 p_api_name => l_api_name,
606 p_pkg_name => G_PKG_NAME,
607 p_init_msg_list => p_init_msg_list,
608 l_api_version => l_api_version,
609 p_api_version => p_api_version,
610 p_api_type => G_API_TYPE,
611 p_msg_log => 'Entering Fpa_Project_Load_Pvt.Calc_Scenario_Wscores_Aw',
612 x_return_status => x_return_status);
613
614 -- check if activity started successfully
615 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
616 l_msg_log := 'start_activity';
617 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
618 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
619 l_msg_log := 'start_activity';
620 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
621 end if;
622
623 -- calculate scenario weighted scores
624 -- fetch planning cycle (and initial scenario on the planning cycle)
625 -- for this scenario
626
627 open init_scenario_csr(p_scenario_id);
628 fetch init_scenario_csr into l_planning_cycle_id, l_init_scenario_id;
629 close init_scenario_csr;
630
631 calc_scenario_wscores(l_planning_cycle_id, p_scenario_id);
632
633 -- if p_scenario_id is not intial scenario then calculate new scenario scores
634 -- for initial scenario as projects got added to initial scenario also
635 -- during this load
636
637 if (l_init_scenario_id is not null and l_init_scenario_id <> p_scenario_id) then
638 calc_scenario_wscores(l_planning_cycle_id, l_init_scenario_id);
639 end if;
640
641
642 FPA_UTILITIES_PVT.END_ACTIVITY(
643 p_api_name => l_api_name,
644 p_pkg_name => G_PKG_NAME,
645 p_msg_log => null,
646 x_msg_count => x_msg_count,
647 x_msg_data => x_msg_data);
648
649 EXCEPTION
650 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
651 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
652 p_api_name => l_api_name,
653 p_pkg_name => G_PKG_NAME,
654 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
655 p_msg_log => l_msg_log,
656 x_msg_count => x_msg_count,
657 x_msg_data => x_msg_data,
658 p_api_type => G_API_TYPE);
659
660 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
661 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
662 p_api_name => l_api_name,
663 p_pkg_name => G_PKG_NAME,
664 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
665 p_msg_log => l_msg_log,
666 x_msg_count => x_msg_count,
667 x_msg_data => x_msg_data,
668 p_api_type => G_API_TYPE);
669
670 when OTHERS then
671 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
672 p_api_name => l_api_name,
673 p_pkg_name => G_PKG_NAME,
674 p_exc_name => 'OTHERS',
675 p_msg_log => l_msg_log||SQLERRM,
676 x_msg_count => x_msg_count,
677 x_msg_data => x_msg_data,
678 p_api_type => G_API_TYPE);
679
680 END Calc_Scenario_Wscores_Aw;
681
682 -- The procedure Update_Scenario_App_Scores copies the scores from the approved
683 -- scenario (scenario id is passed) to the approved scores variable.
684 PROCEDURE Update_Scenario_App_Scores
685 (
686 p_api_version IN NUMBER,
687 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
688 p_scenario_id IN NUMBER,
689 x_return_status OUT NOCOPY VARCHAR2,
690 x_msg_count OUT NOCOPY NUMBER,
691 x_msg_data OUT NOCOPY VARCHAR2
692 ) is
693
694 -- standard parameters
695 l_return_status VARCHAR2(1);
696 l_api_name CONSTANT VARCHAR2(30) := 'Update_Scenario_App_Scores';
697 l_api_version CONSTANT NUMBER := 1.0;
698 l_msg_log VARCHAR2(2000) := null;
699
700 begin
701
702 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
703 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
704 p_api_name => l_api_name,
705 p_pkg_name => G_PKG_NAME,
706 p_init_msg_list => p_init_msg_list,
707 l_api_version => l_api_version,
708 p_api_version => p_api_version,
709 p_api_type => G_API_TYPE,
710 p_msg_log => 'Entering Fpa_Project_Load_Pvt.Calc_Scenario_Wscores_Aw',
711 x_return_status => x_return_status);
712
713 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
714 FND_LOG.String
715 ( FND_LOG.LEVEL_PROCEDURE,
716 'FPA_Scorecard_Pvt.Update_Scenario_App_Scores.begin',
717 'Setting appropriate dimension limits.'
718 );
719 END IF;
720
721 dbms_aw.execute('limit scenario_d to ' || p_scenario_id);
722 dbms_aw.execute('limit project_d to scenario_project_m');
723 dbms_aw.execute('limit planning_cycle_d to scenario_d');
724 dbms_aw.execute('limit strategic_obj_d to pc_obj_m');
725
726 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
727 FND_LOG.String
728 ( FND_LOG.LEVEL_PROCEDURE,
729 'FPA_Scorecard_Pvt.Update_Scenario_App_Scores.',
730 'Updating Approved Scores.'
731 );
732 END IF;
733
734 dbms_aw.execute('project_approved_obj_score_m = scenario_project_obj_score_m');
735
736 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
737 FND_LOG.String
738 ( FND_LOG.LEVEL_PROCEDURE,
739 'FPA_Scorecard_Pvt.Update_Scenario_App_Scores.end',
740 'Finished updating approved projects.'
741 );
742 END IF;
743
744
745
746 FPA_UTILITIES_PVT.END_ACTIVITY(
747 p_api_name => l_api_name,
748 p_pkg_name => G_PKG_NAME,
749 p_msg_log => null,
750 x_msg_count => x_msg_count,
751 x_msg_data => x_msg_data);
752
753 EXCEPTION
754 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
755 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
756 p_api_name => l_api_name,
757 p_pkg_name => G_PKG_NAME,
758 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
759 p_msg_log => l_msg_log,
760 x_msg_count => x_msg_count,
761 x_msg_data => x_msg_data,
762 p_api_type => G_API_TYPE);
763
764 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
765 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
766 p_api_name => l_api_name,
767 p_pkg_name => G_PKG_NAME,
768 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
769 p_msg_log => l_msg_log,
770 x_msg_count => x_msg_count,
771 x_msg_data => x_msg_data,
772 p_api_type => G_API_TYPE);
773
774 when OTHERS then
775 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
776 p_api_name => l_api_name,
777 p_pkg_name => G_PKG_NAME,
778 p_exc_name => 'OTHERS',
779 p_msg_log => l_msg_log||SQLERRM,
780 x_msg_count => x_msg_count,
781 x_msg_data => x_msg_data,
782 p_api_type => G_API_TYPE);
783
784
785
786 end Update_Scenario_App_Scores;
787
788
789
790 PROCEDURE Handle_Comments
791 (
792 p_api_version IN NUMBER,
793 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
794 p_scenario_id IN NUMBER,
795 p_type IN VARCHAR2,
796 p_source_scenario_id IN NUMBER,
797 p_delete_project_id IN NUMBER,
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 -- standard parameters
804 l_return_status VARCHAR2(1);
805 l_api_name CONSTANT VARCHAR2(30) := 'Load_Comments';
806 l_api_version CONSTANT NUMBER := 1.0;
807 l_msg_log VARCHAR2(2000) := null;
808 ----------------------------------------------------------------------------
809 l_projects VARCHAR2(2000) := null;
810 l_init_scenario_id NUMBER;
811 l_source_scenario_id NUMBER;
812
813 l_scorecards_tl_rec FPA_SCORECARDS_PVT.FPA_SCORECARDS_TL_REC;
814
815 CURSOR initial_scenario_csr (p_scenario_id in number) IS
816 select
817 scei.scenario
818 from
819 fpa_aw_sce_info_v scei, fpa_aw_sce_info_v sce, fpa_aw_sces_v pc
820 where
821 scei.is_initial_scenario = 1
822 and sce.planning_cycle = pc.planning_cycle
823 and scei.planning_cycle = pc.planning_cycle
824 and pc.scenario = sce.scenario
825 and sce.scenario = p_scenario_id;
826
827 CURSOR pjt_comments_csr (p_scenario_id in number) IS
828 select
829 sce.scenario scenario,
830 sce.project project,
831 sce.investment_criteria investment_criteria,
832 pjtc.comments comments
833 from
834 fpa_aw_proj_str_scores_v sce, fpa_scorecards_tl pjtc
835 where
836 sce.project = pjtc.project_id
837 and sce.investment_criteria = pjtc.strategic_obj_id
838 and pjtc.scenario_id = -1
839 and sce.scenario = p_scenario_id
840 and pjtc.language = userenv('LANG')
841 and not exists
842 (select 1
843 from fpa_scorecards_tl sctl
844 where sctl.scenario_id = sce.scenario
845 and sctl.project_id = sce.project
846 and sctl.language = userenv('LANG')
847 and sctl.strategic_obj_id = sce.investment_criteria);
848
849
850 CURSOR pjp_comments_csr (p_source_scenario_id in number,
851 p_scenario_id in number) IS
852 select
853 pjts.project_id project,
854 pjts.strategic_obj_id investment_criteria,
855 pjts.comments comments
856 from
857 fpa_scorecards_tl pjts
858 where
859 pjts.scenario_id = p_source_scenario_id
860 and pjts.language = userenv('LANG')
861 and not exists
862 (select 1
863 from fpa_scorecards_tl sctl
864 where sctl.project_id = pjts.project_id
865 and sctl.strategic_obj_id = pjts.strategic_obj_id
866 and sctl.language = userenv('LANG')
867 and sctl.scenario_id = p_scenario_id);
868
869
870 BEGIN
871
872 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
873 -- call START_ACTIVITY to create savepoint, check compatibility
874 -- and initialize message list
875 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
876 p_api_name => l_api_name,
877 p_pkg_name => G_PKG_NAME,
878 p_init_msg_list => p_init_msg_list,
879 l_api_version => l_api_version,
880 p_api_version => p_api_version,
881 p_api_type => G_API_TYPE,
882 p_msg_log => 'Entering Fpa_Scorecards_Pvt.Handle_Comments '||p_scenario_id,
883 x_return_status => x_return_status);
884
885 -- check if activity started successfully
886 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
887 l_msg_log := 'start_activity';
888 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
889 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
890 l_msg_log := 'start_activity';
891 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
892 end if;
893
894 IF (p_delete_project_id is not null) then
895 DELETE
896 FROM FPA_SCORECARDS_TL
897 WHERE SCENARIO_ID = P_SCENARIO_ID
898 AND PROJECT_ID = P_DELETE_PROJECT_ID
899 AND LANGUAGE = userenv('LANG')
900 AND SOURCE_LANG = userenv('LANG');
901
902 FPA_UTILITIES_PVT.END_ACTIVITY(
903 p_api_name => l_api_name,
904 p_pkg_name => G_PKG_NAME,
905 p_msg_log => null,
906 x_msg_count => x_msg_count,
907 x_msg_data => x_msg_data);
908 RETURN;
909 END IF;
910
911 OPEN initial_scenario_csr(p_scenario_id);
912 FETCH initial_scenario_csr INTO l_init_scenario_id;
913 CLOSE initial_scenario_csr;
914
915
916 IF (p_type = 'PJT') THEN
917
918 FOR pjt_comments_csr_rec IN pjt_comments_csr(p_scenario_id) LOOP
919
920 l_scorecards_tl_rec.scenario_id := pjt_comments_csr_rec.scenario;
921 l_scorecards_tl_rec.project_id := pjt_comments_csr_rec.project;
922 l_scorecards_tl_rec.strategic_obj_id := pjt_comments_csr_rec.investment_criteria;
923 l_scorecards_tl_rec.comments := pjt_comments_csr_rec.comments;
924
925 insert_tl_rec(
926 p_init_msg_list => p_init_msg_list,
927 p_scorecards_tl_rec => l_scorecards_tl_rec,
928 x_msg_count => x_msg_count,
929 x_msg_data => x_msg_data,
930 x_return_status => l_return_status);
931 END LOOP;
932
933 IF pjt_comments_csr%ISOPEN THEN
934 CLOSE pjt_comments_csr;
935 END IF;
936
937 IF(l_init_scenario_id <> p_scenario_id) THEN
938 FOR pjt_comments_csr_rec IN pjt_comments_csr(l_init_scenario_id) LOOP
939
940 l_scorecards_tl_rec.scenario_id := pjt_comments_csr_rec.scenario;
941 l_scorecards_tl_rec.project_id := pjt_comments_csr_rec.project;
942 l_scorecards_tl_rec.strategic_obj_id := pjt_comments_csr_rec.investment_criteria;
943 l_scorecards_tl_rec.comments := pjt_comments_csr_rec.comments;
944
945 insert_tl_rec(
946 p_init_msg_list => p_init_msg_list,
947 p_scorecards_tl_rec => l_scorecards_tl_rec,
948 x_msg_count => x_msg_count,
949 x_msg_data => x_msg_data,
950 x_return_status => l_return_status);
951 END LOOP;
952
953 IF pjt_comments_csr%ISOPEN THEN
954 CLOSE pjt_comments_csr;
955 END IF;
956 END IF;
957
958 ELSIF (p_type = 'PJP') then
959
960 if (p_source_scenario_id is null) then
961 l_source_scenario_id := l_init_scenario_id;
962 else
963 l_source_scenario_id := p_source_scenario_id;
964 end if;
965
966 FOR pjp_comments_csr_rec IN pjp_comments_csr(l_source_scenario_id, p_scenario_id) LOOP
967
968 l_scorecards_tl_rec.scenario_id := p_scenario_id;
969 l_scorecards_tl_rec.project_id := pjp_comments_csr_rec.project;
970 l_scorecards_tl_rec.strategic_obj_id := pjp_comments_csr_rec.investment_criteria;
971 l_scorecards_tl_rec.comments := pjp_comments_csr_rec.comments;
972
973 insert_tl_rec(
974 p_init_msg_list => p_init_msg_list,
975 p_scorecards_tl_rec => l_scorecards_tl_rec,
976 x_msg_count => x_msg_count,
977 x_msg_data => x_msg_data,
978 x_return_status => l_return_status);
979
980 END LOOP;
981
982 IF pjp_comments_csr%ISOPEN THEN
983 CLOSE pjp_comments_csr;
984 END IF;
985
986 END IF; -- elsif PJP
987
988 FPA_UTILITIES_PVT.END_ACTIVITY(
989 p_api_name => l_api_name,
990 p_pkg_name => G_PKG_NAME,
991 p_msg_log => null,
992 x_msg_count => x_msg_count,
993 x_msg_data => x_msg_data);
994
995 EXCEPTION
996 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
997 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
998 p_api_name => l_api_name,
999 p_pkg_name => G_PKG_NAME,
1000 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
1001 p_msg_log => l_msg_log,
1002 x_msg_count => x_msg_count,
1003 x_msg_data => x_msg_data,
1004 p_api_type => G_API_TYPE);
1005
1006 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
1007 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1008 p_api_name => l_api_name,
1009 p_pkg_name => G_PKG_NAME,
1010 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
1011 p_msg_log => l_msg_log,
1012 x_msg_count => x_msg_count,
1013 x_msg_data => x_msg_data,
1014 p_api_type => G_API_TYPE);
1015
1016 when OTHERS then
1017 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1018 p_api_name => l_api_name,
1019 p_pkg_name => G_PKG_NAME,
1020 p_exc_name => 'OTHERS',
1021 p_msg_log => l_msg_log||SQLERRM,
1022 x_msg_count => x_msg_count,
1023 x_msg_data => x_msg_data,
1024 p_api_type => G_API_TYPE);
1025
1026 END Handle_Comments;
1027
1028
1029
1030 FUNCTION Read_Only(
1031 p_planning_cycle_id IN NUMBER,
1032 p_scenario_id IN NUMBER) RETURN VARCHAR2 IS
1033
1034 CURSOR PC_SCENARIO_CSR (P_SCENARIO_ID IN NUMBER) IS
1035 SELECT
1036 PC.PC_STATUS, PC.PORTFOLIO, PC.PLANNING_CYCLE,
1037 SCE.RECOMMENDED_FLAG, SCE.APPROVED_FLAG, SCE.IS_INITIAL_SCENARIO
1038 FROM
1039 FPA_AW_PC_INFO_V PC, FPA_AW_SCES_V SCEP, FPA_AW_SCE_INFO_V SCE
1040 WHERE
1041 SCEP.SCENARIO = SCE.SCENARIO
1042 AND SCEP.PLANNING_CYCLE = PC.PLANNING_CYCLE
1043 AND SCEP.SCENARIO = P_SCENARIO_ID;
1044
1045 CURSOR PC_CSR (P_PC_ID IN NUMBER) IS
1046 SELECT
1047 PC.PC_STATUS, PC.PORTFOLIO, PC.SCENARIO_COUNT
1048 FROM
1049 FPA_AW_PC_INFO_V PC
1050 WHERE
1051 PC.PLANNING_CYCLE = P_PC_ID;
1052
1053 l_priv_develop_scen VARCHAR2(1) := null;
1054 l_portfolio_id NUMBER;
1055 l_pc_id NUMBER;
1056 l_scen_count NUMBER;
1057 l_pc_status FPA_AW_PC_INFO_V.PC_STATUS%TYPE;
1058 l_scen_rec_flag FPA_AW_SCE_INFO_V.RECOMMENDED_FLAG%TYPE;
1059 l_scen_app_flag FPA_AW_SCE_INFO_V.APPROVED_FLAG%TYPE;
1060 l_scen_ini_flag FPA_AW_SCE_INFO_V.IS_INITIAL_SCENARIO%TYPE;
1061
1062 BEGIN
1063
1064 l_pc_id := p_planning_cycle_id;
1065
1066 if(p_scenario_id is not null) then
1067 open pc_scenario_csr (p_scenario_id);
1068 fetch pc_scenario_csr into l_pc_status, l_portfolio_id, l_pc_id,
1069 l_scen_rec_flag, l_scen_app_flag,
1070 l_scen_ini_flag;
1071 close pc_scenario_csr;
1072 if(l_scen_rec_flag = 1 or l_scen_app_flag = 1) then
1073 return 'T';
1074 end if;
1075
1076 l_priv_develop_scen := FPA_SECURITY_PVT.Check_User_Previlege(
1077 p_privilege => FPA_SECURITY_PVT.G_DEVELOP_SCENARIO,
1078 p_object_id => l_portfolio_id);
1079
1080 if(l_priv_develop_scen <> 'T' ) then
1081 return 'T';
1082 end if;
1083
1084 open pc_csr (l_pc_id);
1085 fetch pc_csr into l_pc_status, l_portfolio_id, l_scen_count;
1086 close pc_csr;
1087
1088 if(l_scen_ini_flag = 1 and l_scen_count > 1) then
1089 return 'T';
1090 end if;
1091 else
1092 open pc_csr (l_pc_id);
1093 fetch pc_csr into l_pc_status, l_portfolio_id, l_scen_count;
1094 close pc_csr;
1095 end if;
1096
1097 if(l_pc_status = 'CLOSED' or l_pc_status = 'APPROVED'
1098 or l_pc_status = 'SUBMITTED') then
1099 return 'T';
1100 end if;
1101
1102 return 'F';
1103
1104 EXCEPTION
1105 WHEN OTHERS THEN
1106 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1107 FND_LOG.String(
1108 FND_LOG.LEVEL_PROCEDURE,
1109 'fpa.sql.FPA_SCORECARD_PVT.Read_Only',
1110 'EXCEPTION:'||sqlerrm||p_planning_cycle_id||','||p_scenario_id);
1111 end if;
1112 return 'F';
1113 END Read_Only;
1114
1115
1116
1117
1118 END FPA_SCORECARDS_PVT;