[Home] [Help]
PACKAGE BODY: APPS.FPA_PORTFOLIO_PROJECT_SETS_PVT
Source
1 PACKAGE BODY fpa_portfolio_project_sets_pvt AS
2 /* $Header: FPAVPRSB.pls 120.2 2006/06/16 22:27:49 sishanmu noship $ */
3
4 PROCEDURE create_project_set
5 ( p_api_version IN NUMBER,
6 p_pc_id IN fpa_aw_pc_info_v.planning_cycle%TYPE,
7 x_return_status OUT NOCOPY VARCHAR2,
8 x_msg_data OUT NOCOPY VARCHAR2,
9 x_msg_count OUT NOCOPY NUMBER) IS
10 cursor c_project_sets(p_portfolio_id in number) is
11 SELECT init_project_set_id, appr_project_set_id
12 FROM FPA_AW_PROJECT_SETS_V
13 WHERE portfolio = p_portfolio_id;
14
15 l_project_sets c_project_sets%ROWTYPE;
16
17 l_portfolio_id fpa_aw_portfs_v.portfolio%TYPE;
18 l_init_project_set_id fpa_aw_project_sets_v.init_project_set_id%TYPE;
19 l_appr_project_set_id fpa_aw_project_sets_v.appr_project_set_id%TYPE;
20 l_project_set_name pa_project_sets_v.name%TYPE;
21 l_project_set_id fpa_aw_project_sets_v.init_project_set_id%TYPE;
22 l_appr_scen_id fpa_aw_sce_info_v.scenario%TYPE;
23 l_project_id_tbl SYSTEM.pa_num_tbl_type;
24 l_portfolio_name fpa_portfs_vl.name%TYPE;
25 l_pc_name fpa_pcs_vl.NAME%TYPE;
26 -- l_pset_attr fpa_project_sets_v.status%TYPE;
27 -- l_count number;
28
29 l_count number(15);
30
31 cursor c_portfolio_owner is
32 select hzp.party_id
33 from pa_project_parties ppp, pa_project_role_types pprt, hz_parties hzp, per_people_f per, fpa_aw_pc_info_v pc
34 where ppp.object_type = 'PJP_PORTFOLIO'
35 and ppp.project_role_id = pprt.project_role_id
36 and pprt.project_role_type = 'PORTFOLIO_OWNER'
37 and ppp.resource_source_id = per.person_id
38 and per.party_id = hzp.party_id
39 and ppp.object_id = pc.portfolio
40 and pc.planning_cycle = p_pc_id;
41
42 l_portfolio_owner_id number(15);
43
44
45 -- This is a local procedure since there is no need to call this API separately
46 PROCEDURE delete_project_set_lines
47 ( p_api_version IN NUMBER,
48 p_project_set_id IN fpa_aw_project_sets_v.init_project_set_id%TYPE,
49 x_return_status OUT NOCOPY VARCHAR2,
50 x_msg_data OUT NOCOPY VARCHAR2,
51 x_msg_count OUT NOCOPY NUMBER)
52 IS
53
54 l_project_id_tbl SYSTEM.pa_num_tbl_type;
55 NO_PROJECTS_FOUND EXCEPTION;
56
57 BEGIN
58
59 FND_MSG_PUB.Initialize;
60 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
61 FND_LOG.STRING
62 (
63 FND_LOG.LEVEL_PROCEDURE,
64 'FPA.SQL.fpa_portfolio_project_sets_pvt.delete_project_set_lines.begin',
65 'Entering FPA_PORTF_PROJECT_SETS.delete_project_set_lines'
66 );
67 END IF;
68
69
70 IF PA_PROJECT_SET_UTILS.do_lines_exist(p_project_set_id) = 'Y' then -- check if lines exist for this project Set
71 SELECT project_id BULK COLLECT
72 INTO l_project_id_tbl
73 FROM pa_project_set_lines
74 WHERE project_set_id = p_project_set_id;
75
76 -- Call API to delete one row at at a time
77 FOR i in l_project_id_tbl.FIRST..l_project_id_tbl.LAST
78 LOOP
79 PA_PROJECT_SETS_PUB.delete_project_set_line
80 (
81 p_project_set_id => p_project_set_id
82 ,p_project_id => l_project_id_tbl(i)
83 ,x_return_status => x_return_status
84 ,x_msg_count => x_msg_count
85 ,x_msg_data => x_msg_data
86 );
87 END LOOP;
88 END IF;
89
90 -- reset boolean variable to NA for all the projects that were deleted from the project set
91 dbms_aw.execute('PUSH project_d');
92 dbms_aw.execute('LMT project_set_d to ' || p_project_set_id);
93 dbms_aw.execute('LMT project_d to project_set_project_m eq yes');
94 dbms_aw.execute('project_set_project_m = na');
95 dbms_aw.execute('POP project_d');
96
97 /* IF (p_commit = FND_API.G_TRUE) THEN
98 COMMIT;
99 dbms_aw.execute('UPDATE');
100 END IF;
101 */
102
103 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
104 FND_LOG.STRING
105 (
106 FND_LOG.LEVEL_PROCEDURE,
107 'FPA.SQL.fpa_portfolio_project_sets.delete_project_set_lines.end',
108 'Exiting fpa_portfolio_project_sets.delete_project_set_lines'
109 );
110 END IF;
111
112 EXCEPTION
113 WHEN OTHERS THEN
114 ROLLBACK;
115
116 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
117
118 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
119 FND_LOG.STRING
120 (
121 FND_LOG.LEVEL_ERROR,
122 'FPA.SQL.fpa_portfolio_project_sets.delete_project_set_lines',
123 SQLERRM
124 );
125 END IF;
126
127 FND_MSG_PUB.count_and_get
128 (
129 p_count => x_msg_count,
130 p_data => x_msg_data
131 );
132 RAISE;
133
134 END delete_project_set_lines;
135
136
137 BEGIN
138
139 -- l_project_set_name := 'Project_set_1'||TO_CHAR(SYSDATE,'HHMISS');
140
141 /* Temp code ends*/
142 -- Get the Portfolio ownerId. This is used to set owner for project set.
143
144 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
145 FND_LOG.STRING
146 (
147 FND_LOG.LEVEL_PROCEDURE,
148 'FPA.SQL.fpa_portfolio_project_sets.create_project_set',
149 'Fetch Portfolio Owner Id'
150 );
151 END IF;
152
153 open c_portfolio_owner;
154 fetch c_portfolio_owner into l_portfolio_owner_id;
155 close c_portfolio_owner;
156
157 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
158 FND_LOG.STRING
159 (
160 FND_LOG.LEVEL_PROCEDURE,
161 'FPA.SQL.fpa_portfolio_project_sets.create_project_set',
162 'Portfolio Owner ID ='||l_portfolio_owner_id
163 );
164 END IF;
165
166 FND_MSG_PUB.Initialize;
167
168 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
169 FND_LOG.STRING
170 (
171 FND_LOG.LEVEL_PROCEDURE,
172 'FPA.SQL.fpa_portfolio_project_sets.create_project_set.begin',
173 'Entering fpa_portfolio_project_sets.create_project_set'
174 );
175 END IF;
176
177 SELECT portfolio
178 INTO l_portfolio_id
179 FROM fpa_aw_pc_info_v
180 WHERE planning_cycle = p_pc_id;
181
182 --Select portfolio name, to be part of the project_set name.
183 SELECT name
184 INTO l_portfolio_name
185 FROM fpa_portfs_vl
186 WHERE portfolio = l_portfolio_id;
187
188 --Select pc name, to be part of the project_set name.
189 SELECT name
190 INTO l_pc_name
191 FROM fpa_pcs_vl
192 WHERE planning_cycle = p_pc_id;
193
194 --Coin the ProjectSet name.
195 l_project_set_name := l_portfolio_name||' - '||l_pc_name||' - ';
196
197 open c_project_sets(l_portfolio_id);
198 fetch c_project_sets into l_project_sets;
199 if l_project_sets.init_project_set_id is not null then
200 null;
201 -- Cursor returned a row. that is, project sets exist for this portfolio.
202 -- clean up the project sets; 1. Rename the project set to reflect current PC and 2. Delete projects
203
204 -- initial Project set
205 delete_project_set_lines
206 ( p_api_version => p_api_version,
207 p_project_set_id => l_project_sets.init_project_set_id,
208 x_return_status => x_return_status,
209 x_msg_count => x_msg_count,
210 x_msg_data => x_msg_data);
211 -- approved project set
212 delete_project_set_lines
213 ( p_api_version => p_api_version,
214 p_project_set_id => l_project_sets.appr_project_set_id,
215 x_return_status => x_return_status,
216 x_msg_count => x_msg_count,
217 x_msg_data => x_msg_data);
218
219 ELSE
220
221 -- Should create Project sets for the first time for this portfolio
222 -- create empty proj. sets - each for initial and approved scenario
223
224 -- Start Project Set creation
225 -- Create Project set for initial scenario
226 PA_PROJECT_SETS_PUB.create_project_set
227 (p_project_set_name => l_project_set_name||Fnd_message.get_string('FPA','FPA_PROJECT_SET_INIT'),
228 p_party_id => l_portfolio_owner_id,
229 p_effective_start_date => TRUNC(SYSDATE),
230 p_access_level => 1,
231 p_party_name => NULL,
232 x_project_set_id => l_init_project_set_id,
233 x_return_status => x_return_status,
234 x_msg_count => x_msg_count,
235 x_msg_data => x_msg_data);
236
237 Fnd_message.CLEAR;
238
239 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then -- Project Set creation failed
240 raise PROGRAM_ERROR;
241 end if;
242 -- project set created successfully
243
244 -- l_project_set_id returned here is project_set_id for initial scenario.
245 -- set portfolio_project_set_submitted_r
246 -- New project set. so maintain project_set_d and set the relation
247 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
248 FND_LOG.STRING
249 (
250 FND_LOG.LEVEL_PROCEDURE,
251 'FPA.SQL.fpa_portfolio_project_sets.create_project_set.',
252 'Maintaining project_set_d with value ' || l_init_project_set_id
253 );
254 END IF;
255 dbms_aw.execute('MAINTAIN project_set_d ADD ' || l_init_project_set_id);
256
257 --Limit the values of portfolio, project_set_id
258 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
259 FND_LOG.STRING
260 (
261 FND_LOG.LEVEL_PROCEDURE,
262 'FPA.SQL.fpa_portfolio_project_sets.create_project_set.',
263 'Limiting portfolio to ' || l_portfolio_id
264 );
265 END IF;
266 dbms_aw.execute('LMT portfolio_d TO ' || l_portfolio_id);
267
268 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
269 FND_LOG.STRING
270 (
271 FND_LOG.LEVEL_PROCEDURE,
272 'FPA.SQL.fpa_portfolio_project_sets.create_project_set.',
273 'Setting PORTFOLIO_PROJECT_SET_SUBMITTED_R with value ' || l_init_project_set_id
274 );
275 END IF;
276 dbms_aw.execute('PORTFOLIO_PROJECT_SET_SUBMITTED_R = ' ||l_init_project_set_id);
277
278 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
279 FND_LOG.STRING
280 (
281 FND_LOG.LEVEL_PROCEDURE,
282 'FPA.SQL.fpa_portfolio_project_sets.create_project_set.',
283 'Calling PA_PROJECT_SETS_PUB.create_project_set.'
284 );
285 END IF;
286 -- Create Project set for approved scenario
287 PA_PROJECT_SETS_PUB.create_project_set
288 (p_project_set_name => l_project_set_name||Fnd_message.get_string('FPA','FPA_PROJECT_SET_APPR'),
289 p_party_id => l_portfolio_owner_id,
290 p_effective_start_date => TRUNC(SYSDATE),
291 p_access_level => 1,
292 p_party_name => NULL,
293 x_project_set_id => l_appr_project_set_id,
294 x_return_status => x_return_status,
295 x_msg_count => x_msg_count,
296 x_msg_data => x_msg_data);
297
298 Fnd_message.CLEAR;
299
300 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then -- Project Set creation failed
301 raise PROGRAM_ERROR;
302 end if;
303
304 -- project set created successfully
305 -- l_project_set_id returned here is project_set_id for approved scenario.
306 -- set portfolio_project_set_approved_r
307 -- New project set. so maintain project_set_d and set the relation
308 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
309 FND_LOG.STRING
310 (
311 FND_LOG.LEVEL_PROCEDURE,
312 'FPA.SQL.fpa_portfolio_project_sets.create_project_set.begin',
313 'Maintaining project_set_d with value ' || l_appr_project_set_id
314 );
315 END IF;
316 dbms_aw.execute('MAINTAIN project_set_d ADD ' || l_appr_project_set_id);
317
318 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
319 FND_LOG.STRING
320 (
321 FND_LOG.LEVEL_PROCEDURE,
322 'FPA.SQL.fpa_portfolio_project_sets.create_project_set.begin',
323 'Limiting portfolio to value ' || l_portfolio_id
324 );
325 END IF;
326 --Limit the values of portfolio, project_set_id
327 dbms_aw.execute('LMT portfolio_d TO ' || l_portfolio_id);
328
329 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
330 FND_LOG.STRING
331 (
332 FND_LOG.LEVEL_PROCEDURE,
333 'FPA.SQL.fpa_portfolio_project_sets.create_project_set.begin',
334 'Setting PORTFOLIO_PROJECT_SET_APPROVED_R with value ' || l_appr_project_set_id
335 );
336 END IF;
337 dbms_aw.execute('PORTFOLIO_PROJECT_SET_APPROVED_R = ' ||l_appr_project_set_id);
338
339 -- End Project Set creation
340
341 END IF;
342 close c_project_sets;
343
344 /* IF (p_commit = FND_API.G_TRUE) THEN
345 dbms_aw.execute('UPDATE');
346 COMMIT;
347 END IF;
348 */
349
350 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
351 FND_LOG.STRING
352 (
353 FND_LOG.LEVEL_PROCEDURE,
354 'FPA.SQL.fpa_portfolio_project_sets.Create_project_set.end',
355 'Exiting fpa_portfolio_project_sets.Create_project_set'
356 );
357 END IF;
358
359
360 EXCEPTION
361 WHEN OTHERS THEN
362 ROLLBACK;
363
364
365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
366
367 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
368 FND_LOG.STRING
369 (
370 FND_LOG.LEVEL_ERROR,
371 'FPA.SQL.fpa_portfolio_project_sets.Create_project_set',
372 SQLERRM
373 );
374 END IF;
375
376 FND_MSG_PUB.count_and_get
377 (
378 p_count => x_msg_count,
379 p_data => x_msg_data
380 );
381 RAISE;
382
383
384
385 END create_project_set;
386
387
388
389 PROCEDURE add_project_set_lines
390 ( p_api_version IN NUMBER,
391 p_scen_id IN fpa_aw_sce_info_v.scenario%TYPE,
392 x_return_status OUT NOCOPY VARCHAR2,
393 x_msg_data OUT NOCOPY VARCHAR2,
394 x_msg_count OUT NOCOPY NUMBER)
395 IS
396
397 cursor c_scenario_project_set_det is
398 select sc.scenario, sc.planning_cycle, is_initial_scenario,
399 sc.approved_flag, pc.portfolio, pset.INIT_PROJECT_SET_ID, pset.APPR_PROJECT_SET_ID
400 from fpa_aw_sce_info_v sc, fpa_aw_pc_info_v pc, fpa_aw_project_sets_v pset
401 where sc.planning_cycle = pc.planning_cycle
402 and pc.portfolio = pset.portfolio
403 and sc.scenario = p_scen_id;
404
405 l_scenario_project_set_rec c_scenario_project_set_det%rowtype;
406
407 l_is_appr_scenario fpa_aw_sce_info_v.approved_flag%TYPE;
408 l_project_set_id fpa_aw_project_sets_v.init_project_set_id%TYPE;
409 l_pc_id fpa_aw_sce_info_v.planning_cycle%TYPE;
410 l_exists VARCHAR2(4);
411 l_proj_list SYSTEM.pa_num_tbl_type;
412
413 BEGIN
414
415 FND_MSG_PUB.Initialize;
416
417 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
418 FND_LOG.STRING
419 (
420 FND_LOG.LEVEL_PROCEDURE,
421 'FPA.SQL.fpa_portfolio_project_sets.add_projects_project_set.begin',
422 'Entering fpa_portfolio_project_sets.add_projects_project_set'
423 );
424 END IF;
425
426 open c_scenario_project_set_det;
427
428 fetch c_scenario_project_set_det into l_scenario_project_set_rec;
429
430 IF c_scenario_project_set_det%FOUND then
431
432 if l_scenario_project_set_rec.approved_flag = 1 then
433 l_project_set_id := l_scenario_project_set_rec.APPR_PROJECT_SET_ID;
434
435 elsif l_scenario_project_set_rec.is_initial_scenario = 1 then
436 l_project_set_id := l_scenario_project_set_rec.INIT_PROJECT_SET_ID;
437
438 end if;
439
440 END IF;
441
442 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
443 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
444 'fpa.sql.fpa_main_process_pvt.add_project_set_lines',
445 'processing project_set_id = '|| l_project_set_id);
446 END IF;
447
448 CLOSE c_scenario_project_set_det;
449
450 -- a scenario can have both initial and approved flags set.
451 -- Bug 5208493 - Add only approved projects from Approved Project set
452
453 IF l_scenario_project_set_rec.approved_flag = 1 then
454 BEGIN
455 SELECT project BULK COLLECT
456 INTO l_proj_list
457 FROM fpa_aw_proj_info_v
458 WHERE scenario = p_scen_id and
459 recommended_funding_status = 'FUNDING_APPROVED';
460 EXCEPTION
461 WHEN OTHERS THEN
462 null;
463 END;
464 ELSIF l_scenario_project_set_rec.is_initial_scenario = 1 then
465 BEGIN
466 SELECT project BULK COLLECT
467 INTO l_proj_list
468 FROM fpa_aw_proj_info_v
469 WHERE scenario = p_scen_id;
470 EXCEPTION
471 WHEN OTHERS THEN
472 null;
473 END;
474 END IF;
475
476 IF l_proj_list.count > 0 then -- If there are no projects in Initial scenario, do nothing.
477 -- just exit the procedure.
478
479 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
480 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
481 'fpa.sql.fpa_main_process_pvt.add_project_set_lines',
482 'Number of projects in this scenario = '|| l_proj_list.count);
483 END IF;
484
485 FOR i in l_proj_list.FIRST..l_proj_list.LAST
486 LOOP
487 l_exists := PA_PROJECT_SET_UTILS.check_projects_in_set(l_project_set_id, l_proj_list(i));
488 IF l_exists = 'N' THEN
489 -- add the project to the project set, if it does not yet exist
490 PA_PROJECT_SETS_PUB.create_project_set_line
491 ( p_project_set_id => l_project_set_id
492 ,p_project_id => l_proj_list(i)
493 ,x_return_status => x_return_status
494 ,x_msg_count => x_msg_count
495 ,x_msg_data => x_msg_data
496 );
497 END IF;
498 END LOOP;
499
500
501 --Limit the value of project_set_id
502 dbms_aw.execute('LMT project_set_d TO ' ||l_project_set_id);
503
504 --Set the measure value between the project_set_id and each of
505 --the new projects added to the project_set_id
506
507 FOR i in l_proj_list.FIRST..l_proj_list.LAST
508 LOOP
509 dbms_aw.execute('LMT project_d TO ' || l_proj_list(i));
510 dbms_aw.execute('project_set_project_m = yes');
511 END LOOP;
512 END IF;
513 /* IF (p_commit = FND_API.G_TRUE) THEN
514 dbms_aw.execute('UPDATE');
515 COMMIT;
516 END IF;
517 */
518
519 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
520 FND_LOG.STRING
521 (
522 FND_LOG.LEVEL_PROCEDURE,
523 'FPA.SQL.fpa_portfolio_project_sets.add_projects_project_set.end',
524 'Exiting fpa_portfolio_project_sets.add_projects_project_set'
525 );
526 END IF;
527
528 EXCEPTION
529 WHEN OTHERS THEN
530 ROLLBACK;
531
532 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
533
534 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
535 FND_LOG.STRING
536 (
537 FND_LOG.LEVEL_ERROR,
538 'FPA.SQL.fpa_portfolio_project_sets.add_projects_project_set',
539 SQLERRM
540 );
541 END IF;
542
543 FND_MSG_PUB.count_and_get
544 (
545 p_count => x_msg_count,
546 p_data => x_msg_data
547 );
548 RAISE;
549 END add_project_set_lines;
550
551
552 END fpa_portfolio_project_sets_pvt;