[Home] [Help]
PACKAGE BODY: APPS.FPA_PORTFOLIO_PVT
Source
1 PACKAGE BODY FPA_PORTFOLIO_PVT as
2 /* $Header: FPAVPTFB.pls 120.1 2005/08/18 11:46:58 appldev noship $ */
3
4 PROCEDURE Create_Portfolio
5 (
6 p_api_version IN NUMBER,
7 p_portfolio_rec IN FPA_Portfolio_PVT.portfolio_rec_type,
8 x_portfolio_id OUT NOCOPY VARCHAR2,
9 x_return_status OUT NOCOPY VARCHAR2,
10 x_msg_data OUT NOCOPY VARCHAR2,
11 x_msg_count OUT NOCOPY NUMBER
12
13 )
14 IS
15 -- A cursor to get the new unique id for the scenario
16 CURSOR portfolio_s_c
17 IS
18 SELECT
19 fpa_portfolio_s.nextval AS portfolio_id
20 FROM
21 dual;
22 l_language varchar2(4);
23
24
25 CURSOR c_language IS
26 SELECT language_code
27 FROM fnd_languages
28 WHERE installed_flag IN ('I','B');
29
30 -- A record to hold the new sequence value
31 portfolio_s_r portfolio_s_c%ROWTYPE;
32 BEGIN
33
34 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
35 fnd_log.string
36 (
37 FND_LOG.LEVEL_PROCEDURE,
38 'fpa.sql.fpa_portfolio_pvt.create_portfolio.begin',
39 'Entering fpa_portfolo_pvt.create_portfolio'
40 );
41 END IF;
42
43 -- Get the next sequence value for the scenario identifier
44 OPEN portfolio_s_c;
45 FETCH portfolio_s_c INTO portfolio_s_r;
46 CLOSE portfolio_s_c;
47
48 -- We return the id of the new scenario to the caller
49 x_portfolio_id := portfolio_s_r.portfolio_id;
50
51 -- Change it to handle for exception seciton *****
52
53 -- Add the new portfolio to the dimension
54 dbms_aw.execute('MAINTAIN portfolio_d ADD '|| portfolio_s_r.portfolio_id );
55 dbms_aw.execute('LMT portfolio_d TO '|| portfolio_s_r.portfolio_id );
56 dbms_aw.execute('portfolio_class_code_m='|| p_portfolio_rec.portfolio_type );
57
58 -- check if the organization is null , then update with NA
59 IF p_portfolio_rec.portfolio_start_org_id IS NULL THEN
60 dbms_aw.execute('portfolio_organization_m = NA');
61 ELSE
62 dbms_aw.execute('portfolio_organization_m= '|| p_portfolio_rec.portfolio_start_org_id );
63 END IF;
64
65
66 --dbms_output.put_line('owner id is'||p_portfolio_rec.portfolio_owner_id);
67 -- the below is deperecated , as the person id will nto be mainted in the AW
68 /*
69 BEGIN
70 dbms_aw.execute('MAINTAIN person_id_d ADD '|| p_portfolio_rec.portfolio_owner_id);
71 EXCEPTION
72 WHEN OTHERS THEN
73 --dbms_output.put_line(SQLCODE);
74 -- Check for existing dim values, if it already exists during MAINTAIN, then ignore it.
75 IF SQLCODE = -34034 THEN
76 NULL;
77 END IF;
78 END;
79
80 dbms_aw.execute('owner_portfolio_r= '|| p_portfolio_rec.portfolio_owner_id);
81 */
82
83 IF (c_language%ISOPEN) THEN
84 CLOSE c_language;
85 END IF;
86
87 OPEN c_language;
88 LOOP
89 FETCH c_language INTO l_language;
90 EXIT WHEN c_language%NOTFOUND;
91
92 --dbms_output.put_line('before insert');
93
94
95 -- Insert the record on the pa_objects_tl table
96 INSERT INTO FPA_OBJECTS_TL ( object,id, name,
97 description, language, source_lang,
98 created_by, creation_date, last_updated_by,
99 last_update_date, last_update_login
100 )
101 VALUES
102 ( 'PORTFOLIO',portfolio_s_r.portfolio_id,p_portfolio_rec.portfolio_name,
103 p_portfolio_rec.portfolio_desc,
104 l_Language,
105 USERENV('LANG'),
106 0,
107 SYSDATE,
108 0,
109 SYSDATE,
110 0
111 );
112 END LOOP;
113 --dbms_output.put_line('after insert');
114
115
116 CLOSE c_language;
117
118 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
119 fnd_log.string
120 (
121 FND_LOG.LEVEL_PROCEDURE,
122 'fpa_portfolo_pvt.create_portfolio.end',
123 'Exiting fpa_portfolio_pvt.create_portfolio'
124 );
125 END IF;
126
127
128 EXCEPTION
129 WHEN OTHERS THEN
130
131 --dbms_output.put_line('eception portfolio creat');
132
133 IF portfolio_s_c%ISOPEN THEN
134 CLOSE portfolio_s_c;
135 END IF;
136
137 IF (c_language%ISOPEN) THEN
138 CLOSE c_language;
139 END IF;
140
141 ROLLBACK;
142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
143
144 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
145 fnd_log.string
146 (
147 FND_LOG.LEVEL_ERROR,
148 'fpa_portfolio_pvt.create_portfolio',
149 SQLERRM
150 );
151 END IF;
152 FND_MSG_PUB.count_and_get
153 (
154 p_count => x_msg_count,
155 p_data => x_msg_data
156 );
157 RAISE;
158 END create_portfolio;
159
160
161 /************************************************************************************
162 ************************************************************************************/
163 -- The procedure Delete_Portfolio revmoves the portfolio from aw
164
165 PROCEDURE Delete_Portfolio
166 (
167 p_api_version IN NUMBER,
168 p_portfolio_id IN NUMBER,
169 x_return_status OUT NOCOPY VARCHAR2,
170 x_msg_data OUT NOCOPY VARCHAR2,
171 x_msg_count OUT NOCOPY NUMBER
172 )
173 IS
174
175 BEGIN
176
177 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
178 fnd_log.string
179 (
180 FND_LOG.LEVEL_PROCEDURE,
181 'fpa_portfolio_pvt.Delete_Portfolio.begin',
182 'Entering fpa_portfolio_pvt.Delete_Portfolio'
183 );
184 END IF;
185
186 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
187 fnd_log.string
188 (
189 FND_LOG.LEVEL_PROCEDURE,
190 ' fpa_portfolio_pvt.Delete_Portfolio',
191 ' Before remove portfolio maintain portfolio_d '
192 );
193 END IF;
194
195 -- Delete the portfolio from the AW space.
196 dbms_aw.Execute('maintain portfolio_d delete ' || p_portfolio_id );
197 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
198 fnd_log.string
199 (
200 FND_LOG.LEVEL_PROCEDURE,
201 ' fpa_portfolio_pvt.Delete_Portfolio',
202 ' After remove portfolio maintain portfolio_d '
203 );
204 END IF;
205
206
207 --Delete portfolio from FPA_OBJECTS_TL
208 DELETE FROM FPA_OBJECTS_TL
209 WHERE object = 'PORTFOLIO'
210 AND id = p_portfolio_id;
211
212 -- CHANGES PENDING
213 -- 1) REMOVE CORRESPONDING PLANNING CYCLE
214 -- 2) REMOVE CORRESPONDING PORTFOLIO USERSS
215
216
217
218 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
219 fnd_log.string
220 (
221 FND_LOG.LEVEL_PROCEDURE,
222 'fpa_portfolio_pvt.Delete_Portfolio.end',
223 'Exiting fpa_portfolio_pvt.Delete_Portfolio'
224 );
225 END IF;
226
227
228 EXCEPTION
229 WHEN OTHERS THEN
230 ROLLBACK;
231 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
232
233 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
234 fnd_log.string
235 (
236 FND_LOG.LEVEL_ERROR,
237 'fpa_portfolio_pvt.Delete_Portfolio.end',
238 SQLERRM
239 );
240 END IF;
241 FND_MSG_PUB.count_and_get
242 (
243 p_count => x_msg_count,
244 p_data => x_msg_data
245 );
246 RAISE;
247
248 END Delete_Portfolio;
249
250
251
252 /************************************************************************************
253 ************************************************************************************/
254 -- The procedure Upadate_Portfolio_Descr update the portfolio description
255
256
257 PROCEDURE Upadate_Portfolio_Descr
258 (
259 p_api_version IN NUMBER,
260 p_portfolio_rec IN FPA_Portfolio_PVT.portfolio_rec_type,
261 x_return_status OUT NOCOPY VARCHAR2,
262 x_msg_data OUT NOCOPY VARCHAR2,
263 x_msg_count OUT NOCOPY NUMBER
264 )
265 IS
266 BEGIN
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_pvt.update_portfolio_Descr.begin',
273 'Entering fpa_portfolo_pvt.update_portfolio_descr'
274 );
275 END IF;
276 UPDATE FPA_OBJECTS_TL objtl
277 SET objtl.NAME =p_portfolio_rec.portfolio_name,
278 objtl.DESCRIPTION =p_portfolio_rec.portfolio_desc,
279 objtl.SOURCE_LANG = userenv('LANG')
280 where objtl.id = p_portfolio_rec.portfolio_id
281 and userenv('LANG') IN (objtl.LANGUAGE, objtl.SOURCE_LANG);
282
283
284 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
285 fnd_log.string
286 (
287 FND_LOG.LEVEL_PROCEDURE,
288 'fpa.sql.fpa_portfolio_pvt.update_portfolio_desc.end',
289 'Exiting fpa_portfolo_pvt.update_portfolio_descr'
290 );
291 END IF;
292
293 EXCEPTION
294 WHEN OTHERS THEN
295 ROLLBACK;
296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
297
298 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
299 fnd_log.string
300 (
301 FND_LOG.LEVEL_ERROR,
302 'fpa_portfolio_pvt.update_portfolio',
303 SQLERRM
304 );
305 END IF;
306 FND_MSG_PUB.count_and_get
307 (
308 p_count => x_msg_count,
309 p_data => x_msg_data
310 );
311 RAISE;
312
313 END; -- End Update_Portfolio
314
315
316 /************************************************************************************
317 ************************************************************************************/
318 -- The procedure Upadate_Portfolio_type update the portfolio class code/type measure
319
320 PROCEDURE Upadate_Portfolio_type
321 (
322 p_api_version IN NUMBER,
323 p_portfolio_id IN NUMBER,
324 p_portfolio_class_code IN NUMBER,
325 x_return_status OUT NOCOPY VARCHAR2,
326 x_msg_data OUT NOCOPY VARCHAR2,
327 x_msg_count OUT NOCOPY NUMBER
328 )
329 IS
330 BEGIN
331 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
332 fnd_log.string
333 (
334 FND_LOG.LEVEL_PROCEDURE,
335 'fpa.sql.fpa_portfolio_pvt.update_portfolio_type.begin',
336 'Entering fpa_portfolo_pvt.update_portfolio_type'
337 );
338 END IF;
339 -- Update name and description
340 dbms_aw.execute('LMT portfolio_d TO '||p_portfolio_id);
341 dbms_aw.execute('portfolio_class_code_m='|| p_portfolio_class_code );
342
343 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
344 fnd_log.string
345 (
346 FND_LOG.LEVEL_PROCEDURE,
347 'fpa.sql.fpa_portfolio_pvt.update_portfolio_type.end',
348 'Exiting fpa_portfolo_pvt.update_portfolio_type'
349 );
350 END IF;
351
352 EXCEPTION
353 WHEN OTHERS THEN
354 ROLLBACK;
355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
356
357 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
358 fnd_log.string
359 (
360 FND_LOG.LEVEL_ERROR,
361 'fpa_portfolio_pvt.update_portfolio_type',
362 SQLERRM
363 );
364 END IF;
365 FND_MSG_PUB.count_and_get
366 (
367 p_count => x_msg_count,
368 p_data => x_msg_data
369 );
370 RAISE;
371
372 END; -- End Update_Portfolio_type
373
374
375 /************************************************************************************
376 ************************************************************************************/
377 -- The procedure Upadate_Portfolio_organization update the portfolio start organization measure
378
379 PROCEDURE Upadate_Portfolio_organization
380 (
381 p_api_version IN NUMBER,
382 p_portfolio_id IN NUMBER,
383 p_portfolio_organization IN NUMBER,
384 x_return_status OUT NOCOPY VARCHAR2,
385 x_msg_data OUT NOCOPY VARCHAR2,
386 x_msg_count OUT NOCOPY NUMBER
387 )
388 IS
389 BEGIN
390
391 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
392 fnd_log.string
393 (
394 FND_LOG.LEVEL_PROCEDURE,
395 'fpa.sql.fpa_portfolio_pvt.update_portfolio_organization.begin',
396 'Entering fpa_portfolo_pvt.update_portfolio_organization'
397 );
398 END IF;
399 dbms_aw.execute('LMT portfolio_d TO '||p_portfolio_id);
400 IF p_portfolio_organization IS NULL THEN
401 dbms_aw.execute('portfolio_organization_m = NA');
402 ELSE
403 dbms_aw.execute('portfolio_organization_m= '|| p_portfolio_organization );
404 END IF;
405
406 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
407 fnd_log.string
408 (
409 FND_LOG.LEVEL_PROCEDURE,
410 'fpa.sql.fpa_portfolio_pvt.update_portfolio_organization.end',
411 'Exiting fpa_portfolo_pvt.update_portfolio_organization'
412 );
413 END IF;
414
415 EXCEPTION
416 WHEN OTHERS THEN
417 ROLLBACK;
418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
419
420 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
421 fnd_log.string
422 (
423 FND_LOG.LEVEL_ERROR,
424 'fpa_portfolio_pvt.update_portfolio_organization',
425 SQLERRM
426 );
427 END IF;
428 FND_MSG_PUB.count_and_get
429 (
430 p_count => x_msg_count,
431 p_data => x_msg_data
432 );
433 RAISE;
434
435 END; -- End Update_Portfolio_organization
436
437
438 /************************************************************************************
439 ************************************************************************************/
440 -- The procedure check_portfolio_name validates the duplicate portfolio name.
441
442 FUNCTION Check_Portfolio_name
443 (
444 p_api_version IN NUMBER,
445 p_portfolio_id IN NUMBER,
446 p_portfolio_name IN VARCHAR2,
447 x_return_status OUT NOCOPY VARCHAR2,
448 x_msg_data OUT NOCOPY VARCHAR2,
449 x_msg_count OUT NOCOPY NUMBER
450 )
451 return NUMBER
452 IS
453 PortfolioCnt NUMBER;
454 BEGIN
455
456 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
457 fnd_log.string
458 (
459 FND_LOG.LEVEL_PROCEDURE,
460 'fpa.sql.fpa_portfolio_pvt.Check_portfolio_name.begin',
461 'Entering fpa_portfolo_pvt.Check_portfolio_name'
462 );
463 END IF;
464 --dbms_output.PUT_LINE('portfolio name is '||p_portfolio_name);
465 --dbms_output.PUT_LINE('portfolio id is '||p_portfolio_id);
466
467 -- Check if the portfolio name is exist
468 IF p_portfolio_id is NULL THEN
469 -- Case will be for , Create portfolio
470 SELECT count(*)
471 INTO PortfolioCnt
472 FROM fpa_portfs_vl portfo
473 WHERE portfo.Name = p_portfolio_name;
474
475 ELSE
476 -- case will be for, Update Portfolio
477 SELECT count(*)
478 INTO PortfolioCnt
479 FROM fpa_portfs_vl portfo
480 WHERE portfo.Name = p_portfolio_name
481 AND Portfo.portfolio <> p_portfolio_id;
482 END IF;
483 --dbms_output.PUT_LINE(PortfolioCnt);
484 RETURN PortfolioCnt;
485
486 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
487 fnd_log.string
488 (
489 FND_LOG.LEVEL_PROCEDURE,
490 'fpa.sql.fpa_portfolio_pvt.Check_portfolio_name.end',
491 'Exting fpa_portfolo_pvt.Check_portfolio_name'
492 );
493 END IF;
494
495
496 EXCEPTION
497 WHEN OTHERS THEN
498 ROLLBACK;
499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
500
501 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
502 fnd_log.string
503 (
504 FND_LOG.LEVEL_ERROR,
505 'fpa_portfolio_pvt.Check_portfolio_name',
506 SQLERRM
507 );
508 END IF;
509 FND_MSG_PUB.count_and_get
510 (
511 p_count => x_msg_count,
512 p_data => x_msg_data
513 );
514 RAISE;
515
516 END; -- End Check_Portfolio_name
517
518
519 END;
520