[Home] [Help]
PACKAGE BODY: APPS.FPA_PROCESS_PVT
Source
1 PACKAGE BODY fpa_process_pvt as
2 /* $Header: FPAVPRCB.pls 120.9 2006/05/05 16:05:19 appldev noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(200) := 'FPA_PROCESS_PVT';
6 G_APP_NAME CONSTANT VARCHAR2(3) := FPA_UTILITIES_PVT.G_APP_NAME;
7 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
8 L_API_NAME CONSTANT VARCHAR2(35) := 'PROCESS';
9
10
11 PROCEDURE Copy_Portfolio(p_portfolio_obj IN FPA_PORTFO_ALL_OBJ);
12 PROCEDURE create_update_access_list
13 ( p_portf_users_tbl IN FPA_PORTFOLIO_USERS_TBL,
14 p_portfolio_id NUMBER,
15 x_return_status OUT NOCOPY VARCHAR2,
16 x_msg_data OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER
18 );
19
20
21 /************************************************************************************/
22 -- PLANNING CYCLE PROCEDURES
23 /************************************************************************************/
24
25 /*
26 * CREATE case for Planning Cycle(PC). This creates the complete PC
27 * The calling program must populate all object types in fpa_pc_all_obj
28 * except for fpa_pc_inv_criteria_tbl, which is being done in this API.
29 */
30
31 PROCEDURE Create_Pc
32 ( p_api_version IN NUMBER,
33 p_commit IN VARCHAR2 := FND_API.G_FALSE,
34 p_pc_all_obj IN fpa_pc_all_obj,
35 x_planning_cycle_id OUT NOCOPY NUMBER,
36 x_return_status OUT NOCOPY VARCHAR2,
37 x_msg_data OUT NOCOPY VARCHAR2,
38 x_msg_count OUT NOCOPY NUMBER)
39
40 IS
41 l_pcName_Count NUMBER;
42 l_new_pc_id NUMBER;
43 l_distr_list_id NUMBER;
44 l_portfolio_name VARCHAR2(80);
45 l_inv_criteria_len NUMBER;
46 l_last_pcid NUMBER;
47 l_pc_info fpa_pc_info_obj;
48 l_inv_matrix_tbl fpa_pc_inv_matrix_tbl;
49 l_fin_targets_tbl fpa_pc_fin_targets_tbl;
50 l_discount_obj fpa_pc_discount_obj;
51 l_inv_criteria_tbl fpa_pc_inv_criteria_tbl;
52 l_inv_criteria_obj fpa_pc_inv_criteria_obj;
53 l_distr_list fpa_pc_distr_list_obj;
54 l_distr_list_items_tbl fpa_pc_distr_list_items_tbl;
55 l_inv_crit_count NUMBER := 0;
56
57 /*
58 * Investment Criteria Cursor to get default weights from setup
59 * while Creating PC in the Portfolio
60 */
61 CURSOR c_inv_criteria_setup IS
62 SELECT a.strategic_obj
63 ,nvl(e.strategic_obj_weight,0)
64 ,0 Targetfrom
65 ,0 Targetto
66 FROM fpa_aw_inv_criteria_v a
67 ,fpa_aw_inv_criteria_info_v e
68 WHERE a.strategic_obj = e.strategic_obj;
69
70
71 PROCEDURE Get_Inv_Crit_Setup_Defaults
72 IS
73 l_cntr NUMBER := 0;
74 BEGIN
75 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
76 FND_LOG.String
77 ( FND_LOG.LEVEL_PROCEDURE,
78 'fpa.sql.FPA_Process_Pvt.Create_Pc',
79 'Inside Get_Inv_Criteria_Setup_Defaults Sub Procedure'
80 );
81 END IF;
82 l_cntr := 1;
83
84 l_inv_criteria_tbl := fpa_pc_inv_criteria_tbl();
85
86 l_inv_criteria_tbl.EXTEND(l_inv_crit_count);
87
88 OPEN c_inv_criteria_setup;
89 --FOR i IN l_inv_criteria_tbl.FIRST .. l_inv_criteria_tbl.LAST
90 LOOP
91
92 l_inv_criteria_obj := fpa_pc_inv_criteria_obj(null,null,null,null,null,null,null,null);
93 FETCH c_inv_criteria_setup INTO l_inv_criteria_obj.investment_criteria,
94 l_inv_criteria_obj.pc_inv_criteria_weight,
95 l_inv_criteria_obj.pc_inv_crit_score_target_from,
96 l_inv_criteria_obj.pc_inv_crit_score_target_to;
97 EXIT WHEN c_inv_criteria_setup%NOTFOUND;
98 l_inv_criteria_obj.planning_cycle := l_new_pc_id;
99 l_inv_criteria_obj.pc_project_score_source := 'NEWSCORE';
100 l_inv_criteria_obj.pc_project_score_scale := 10;
101 l_inv_criteria_tbl(l_cntr) := l_inv_criteria_obj;
102 l_cntr := l_cntr + 1;
103 END LOOP;
104 CLOSE c_inv_criteria_setup;
105
106 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
107 FND_LOG.String
108 ( FND_LOG.LEVEL_PROCEDURE,
109 'fpa.sql.FPA_Process_Pvt.Create_Pc',
110 'Leaving Get_Inv_Criteria_Setup_Defaults Sub Procedure'
111 );
112 END IF;
113
114 EXCEPTION
115 WHEN OTHERS THEN
116 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
117 FND_LOG.String
118 ( FND_LOG.LEVEL_PROCEDURE,
119 'fpa.sql.FPA_Process_Pvt.Create_Pc.Get_Inv_Criteria_Setup_Defaults',
120 'Error occurred.'
121 );
122 END IF;
123
124
125 END Get_Inv_Crit_Setup_Defaults;
126
127 BEGIN
128 -- clear all previous messages.
129 FND_MSG_PUB.Initialize;
130
131 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
132 FND_LOG.String
133 (
134 FND_LOG.LEVEL_PROCEDURE,
135 'fpa.sql.FPA_Process_Pvt.Create_Pc.begin',
136 'Entering FPA_Process_Pvt.Create_Pc'
137 );
138 END IF;
139
140 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
141 FND_LOG.String
142 (
143 FND_LOG.LEVEL_PROCEDURE,
144 'fpa.sql.FPA_Process_Pvt.Create_Pc',
145 'Calling FPA_PlanningCycle_Pvt.Check_Pc_Name'
146 );
147 END IF;
148
149 l_pcName_Count := FPA_PlanningCycle_Pvt.Check_Pc_Name
150 (
151 p_api_version => p_api_version,
152 p_portfolio_id => p_pc_all_obj.pc_info.portfolio,
153 p_pc_name => p_pc_all_obj.pc_desc_fields.name,
154 p_pc_id => p_pc_all_obj.pc_desc_fields.id,
155 x_return_status => x_return_status,
156 x_msg_data => x_msg_data,
157 x_msg_count => x_msg_count
158 );
159
160 -- If Duplicate Pc Name exists, then raise error and halt all execution
161 IF l_pcName_Count > 0 THEN
162
163 -- Get the name of Portfolio for this Planning cycle
164 SELECT p.name INTO l_portfolio_name
165 FROM fpa_portfs_vl p
166 WHERE portfolio = p_pc_all_obj.pc_info.portfolio;
167
168 -- Specify the msg, add it in FND_MSG_PUB and raise exp error
169 FND_MESSAGE.SET_NAME('FPA','FPA_DUPLICATE_PCNAME');
170 FND_MESSAGE.SET_TOKEN('PORTFOLIO_NAME', l_portfolio_name);
171 FND_MESSAGE.SET_TOKEN('PC_NAME', p_pc_all_obj.pc_desc_fields.name);
172 FND_MSG_PUB.ADD;
173 RAISE FND_API.G_EXC_ERROR;
174
175 END IF;
176
177 -- Initialize local pcInfo and other objects from the input pc_all object
178 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
179 FND_LOG.String
180 ( FND_LOG.LEVEL_PROCEDURE,
181 'fpa.sql.FPA_Process_Pvt.Create_Pc',
182 'Initializing p_pc_all_obj members.'
183 );
184 END IF;
185
186 IF p_pc_all_obj.pc_info IS NOT NULL THEN
187 l_pc_info := p_pc_all_obj.pc_info;
188 END IF;
189 IF p_pc_all_obj.pc_investmix IS NOT NULL THEN
190 l_inv_matrix_tbl := p_pc_all_obj.pc_investmix;
191 END IF;
192 IF p_pc_all_obj.pc_targets IS NOT NULL THEN
193 l_fin_targets_tbl := p_pc_all_obj.pc_targets;
194 END IF;
195 IF p_pc_all_obj.pc_discount IS NOT NULL THEN
196 l_discount_obj := p_pc_all_obj.pc_discount;
197 END IF;
198 IF p_pc_all_obj.pc_invest_criteria IS NOT NULL THEN
199 l_inv_criteria_tbl := p_pc_all_obj.pc_invest_criteria;
200 END IF;
201
202 IF p_pc_all_obj.pc_distr_list IS NOT NULL THEN
203 l_distr_list := p_pc_all_obj.pc_distr_list;
204 END IF;
205 IF p_pc_all_obj.distr_list_items IS NOT NULL THEN
206 l_distr_list_items_tbl := p_pc_all_obj.distr_list_items;
207 END IF;
208
209 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
210 FND_LOG.String
211 ( FND_LOG.LEVEL_PROCEDURE,
212 'fpa.sql.FPA_Process_Pvt.Create_Pc',
213 'Calling Fpa_Utilities_Pvt.attach_AW.'
214 );
215 END IF;
216
217 -- Attach AW Workspace
218 Fpa_Utilities_Pvt.attach_AW
219 (
220 p_api_version => 1.0,
221 p_attach_mode => 'rw',
222 x_return_status => x_return_status,
223 x_msg_count => x_msg_count,
224 x_msg_data => x_msg_data
225 );
226
227 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
228 FND_LOG.String
229 ( FND_LOG.LEVEL_PROCEDURE,
230 'fpa.sql.FPA_Process_Pvt.Create_Pc',
231 'Calling FPA_PlanningCycle_Pvt.Create_Pc.'
232 );
233 END IF;
234
235 -- Call procedure FPA_PlanningCycle_Pvt.Create_Pc
236 FPA_PlanningCycle_Pvt.Create_Pc
237 (
238 p_api_version => 1.0,
239 p_pc_all_obj => p_pc_all_obj,
240 x_planning_cycle_id => l_new_pc_id,
241 x_return_status => x_return_status,
242 x_msg_data => x_msg_data,
243 x_msg_count => x_msg_count
244 );
245
246 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
247 FND_LOG.String
248 ( FND_LOG.LEVEL_PROCEDURE,
249 'fpa.sql.FPA_Process_Pvt.Create_Pc',
250 'Setting obtained pc Id to all necessary object members.'
251 );
252 END IF;
253
254 -- set the new PC ID in local pc_info and other objects
255 IF l_pc_info IS NOT NULL THEN
256 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
257 FND_LOG.String
258 ( FND_LOG.LEVEL_PROCEDURE,
259 'fpa.sql.FPA_Process_Pvt.Create_Pc',
260 'Setting l_pc_info.planning_cycle with value ' || l_new_pc_id
261 );
262 END IF;
263 l_pc_info.planning_cycle := l_new_pc_id;
264 END IF;
265 IF l_inv_matrix_tbl IS NOT NULL THEN
266 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
267 FND_LOG.String
268 ( FND_LOG.LEVEL_PROCEDURE,
269 'fpa.sql.FPA_Process_Pvt.Create_Pc',
270 'Setting l_inv_matrix_tbl(1).planning_cycle with value ' || l_new_pc_id
271 );
272 END IF;
273 l_inv_matrix_tbl(1).planning_cycle := l_new_pc_id;
274 END IF;
275 IF l_fin_targets_tbl IS NOT NULL THEN
276 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
277 FND_LOG.String
278 ( FND_LOG.LEVEL_PROCEDURE,
279 'fpa.sql.FPA_Process_Pvt.Create_Pc',
280 'Setting l_fin_targets_tbl(1).planning_cycle with value ' || l_new_pc_id
281 );
282 END IF;
283 l_fin_targets_tbl(1).planning_cycle := l_new_pc_id;
284 END IF;
285 IF l_discount_obj IS NOT NULL THEN
286 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
287 FND_LOG.String
288 ( FND_LOG.LEVEL_PROCEDURE,
289 'fpa.sql.FPA_Process_Pvt.Create_Pc',
290 'Setting l_discount_obj.planning_cycle with value ' || l_new_pc_id
291 );
292 END IF;
293 l_discount_obj.planning_cycle := l_new_pc_id;
294 END IF;
295
296 IF l_distr_list IS NOT NULL THEN
297 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
298 FND_LOG.String
299 ( FND_LOG.LEVEL_PROCEDURE,
300 'fpa.sql.FPA_Process_Pvt.Create_Pc',
301 'Setting l_distr_list.object_id with value ' || l_new_pc_id
302 );
303 END IF;
304 l_distr_list.object_id := l_new_pc_id;
305 END IF;
306
307 IF l_inv_criteria_tbl IS NOT NULL THEN
308 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
309 FND_LOG.String
310 ( FND_LOG.LEVEL_PROCEDURE,
311 'fpa.sql.FPA_Process_Pvt.Create_Pc',
312 'Setting l_inv_criteria_tbl(1).planning_cycle with value ' || l_new_pc_id
313 );
314 END IF;
315 l_inv_criteria_tbl(1).planning_cycle := l_new_pc_id;
316 END IF;
317 -- set the new PC ID in the return parameter
318 x_planning_cycle_id := l_new_pc_id;
319
320 /*
321 * Check if the current portfolio has last approved planning cycle.
322 *
323 */
324 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
325 FND_LOG.String
326 ( FND_LOG.LEVEL_PROCEDURE,
327 'fpa.sql.FPA_Process_Pvt.Create_Pc',
328 'Checking if portfolio has an approved PC.'
329 );
330 END IF;
331
332 -- Get the no. of Invest Criterias from setup.
333 SELECT count(strategic_obj)
334 INTO l_inv_crit_count
335 FROM fpa_aw_inv_criteria_v;
336
337 /* Check for the Investment Criteria Table Type Object.
338 * If it is null then we populate it with values from the Investment
339 * Criteria at the Application level.
340 * These are the Default values for the Current Planning Cycle
341 */
342
343 IF p_pc_all_obj.pc_invest_criteria IS NULL THEN
344 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
345 FND_LOG.String
346 ( FND_LOG.LEVEL_PROCEDURE,
347 'fpa.sql.FPA_Process_Pvt.Create_Pc',
348 'p_pc_all_obj.pc_invest_criteria is null and about to call Get_Inv_Crit_Setup_Defaults.'
349 );
350 END IF;
351 -- There is nothing received from java, get the setup defaults and populate
352 -- l_inv_criteria_tbl
353 Get_Inv_Crit_Setup_Defaults;
354 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
355 FND_LOG.String
356 ( FND_LOG.LEVEL_PROCEDURE,
357 'fpa.sql.FPA_Process_Pvt.Create_Pc',
358 'Calling FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data for Setup values, when UI did not have any values.'
359 );
360 END IF;
361 FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data
362 (
363 p_api_version => 1.0,
364 p_inv_crit_tbl => l_inv_criteria_tbl,
365 x_return_status => x_return_status,
366 x_msg_data => x_msg_data,
367 x_msg_count => x_msg_count
368 );
369 ELSE
370 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
371 FND_LOG.String
372 ( FND_LOG.LEVEL_PROCEDURE,
373 'fpa.sql.FPA_Process_Pvt.Create_Pc',
374 'p_pc_all_obj.pc_invest_criteria is not null'
375 );
376 END IF;
377
378 IF l_inv_crit_count <> p_pc_all_obj.pc_invest_criteria.COUNT THEN
379
380 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
381 FND_LOG.String
382 ( FND_LOG.LEVEL_PROCEDURE,
383 'fpa.sql.FPA_Process_Pvt.Create_Pc',
384 'UI criteria count and setup criteria count are not the same.'
385 );
386 END IF;
387 -- Call Get_Inv_Crit_Setup_Defaults, where we reinitialize l_inv_criteria_tbl
388 -- and get the invest criterias from setup.
389 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
390 FND_LOG.String
391 ( FND_LOG.LEVEL_PROCEDURE,
392 'fpa.sql.FPA_Process_Pvt.Create_Pc',
393 'Calling Get_Inv_Crit_Setup_Defaults.'
394 );
395 END IF;
396 Get_Inv_Crit_Setup_Defaults;
397
398 -- Call update for setup data.
399 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
400 FND_LOG.String
401 ( FND_LOG.LEVEL_PROCEDURE,
402 'fpa.sql.FPA_Process_Pvt.Create_Pc',
403 'Calling FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data for setting up default values.'
404 );
405 END IF;
406 FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data
407 (
408 p_api_version => 1.0,
409 p_inv_crit_tbl => l_inv_criteria_tbl,
410 x_return_status => x_return_status,
411 x_msg_data => x_msg_data,
412 x_msg_count => x_msg_count
413 );
414 -- Reassign java values to l_inv_criteria_tbl from java for next update
415 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
416 FND_LOG.String
417 ( FND_LOG.LEVEL_PROCEDURE,
418 'fpa.sql.FPA_Process_Pvt.Create_Pc',
419 'Reassigning UI values to l_inv_criteria_tbl.'
420 );
421 END IF;
422 l_inv_criteria_tbl := p_pc_all_obj.pc_invest_criteria;
423 l_inv_criteria_tbl(1).planning_cycle := l_new_pc_id;
424 --l_inv_crit_partial_count := l_inv_crit_count_java;
425
426 END IF;
427 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
428 FND_LOG.String
429 ( FND_LOG.LEVEL_PROCEDURE,
430 'fpa.sql.FPA_Process_Pvt.Create_Pc',
431 'Calling FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data for UI values.'
432 );
433 END IF;
434 FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data
435 (
436 p_api_version => 1.0,
437 p_inv_crit_tbl => l_inv_criteria_tbl,
438 x_return_status => x_return_status,
439 x_msg_data => x_msg_data,
440 x_msg_count => x_msg_count
441 );
442 END IF;
443
444
445 IF p_pc_all_obj.pc_investmix IS NOT NULL THEN
446
447 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
448 FND_LOG.String
449 ( FND_LOG.LEVEL_PROCEDURE,
450 'fpa.sql.FPA_Process_Pvt.Create_Pc.begin',
451 'Calling FPA_PlanningCycle_Pvt.Update_Pc_Invest_Mix.'
452 );
453 END IF;
454
455 FPA_PlanningCycle_Pvt.Update_Pc_Invest_Mix
456 (
457 p_api_version => 1.0,
458 p_inv_matrix => l_inv_matrix_tbl,
459 x_return_status => x_return_status,
460 x_msg_data => x_msg_data,
461 x_msg_count => x_msg_count
462 );
463 END IF;
464
465
466
467 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
468 FND_LOG.String
469 ( FND_LOG.LEVEL_PROCEDURE,
470 'fpa.sql.FPA_Process_Pvt.Create_Pc',
471 'Calling FPA_PlanningCycle_Pvt.Set_Pc_Status'
472 );
473 END IF;
474
475 FPA_PlanningCycle_Pvt.Set_Pc_Status
476 (
477 p_api_version => 1.0,
478 p_pc_id => l_new_pc_id,
479 p_pc_status_code => 'CREATED',
480 x_return_status => x_return_status,
481 x_msg_data => x_msg_data,
482 x_msg_count => x_msg_count
483 );
484
485 IF p_pc_all_obj.pc_info IS NOT NULL THEN
486 IF p_pc_all_obj.pc_info.pc_category IS NOT NULL THEN
487 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
488 FND_LOG.String
489 ( FND_LOG.LEVEL_PROCEDURE,
490 'fpa.sql.FPA_Process_Pvt.Create_Pc',
491 'Calling FPA_PlanningCycle_Pvt.Update_Pc_Class_Category.'
492 );
493 END IF;
494
495 FPA_PlanningCycle_Pvt.Update_Pc_Class_Category
496 (
497 p_api_version => 1.0,
498 p_pc_id => l_new_pc_id,
499 p_catg_id => p_pc_all_obj.pc_info.pc_category,
500 x_return_status => x_return_status,
501 x_msg_data => x_msg_data,
502 x_msg_count => x_msg_count
503 );
504 END IF;
505
506 IF p_pc_all_obj.pc_info.calendar_name IS NOT NULL THEN
507 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
508 FND_LOG.String
509 ( FND_LOG.LEVEL_PROCEDURE,
510 'fpa.sql.FPA_Process_Pvt.Create_Pc',
511 'Calling FPA_PlanningCycle_Pvt.Update_Pc_Calendar'
512 );
513 END IF;
514
515 FPA_PlanningCycle_Pvt.Update_Pc_Calendar
516 (
517 p_api_version => 1.0,
518 p_pc_info => l_pc_info,
519 x_return_status => x_return_status,
520 x_msg_data => x_msg_data,
521 x_msg_count => x_msg_count
522 );
523 END IF;
524
525 IF p_pc_all_obj.pc_info.currency_code IS NOT NULL THEN
526 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
527 FND_LOG.String
528 ( FND_LOG.LEVEL_PROCEDURE,
529 'fpa.sql.FPA_Process_Pvt.Create_Pc',
530 'Calling FPA_PlanningCycle_Pvt.Update_Pc_Currency'
531 );
532 END IF;
533 FPA_PlanningCycle_Pvt.Update_Pc_Currency
534 (
535 p_api_version => 1.0,
536 p_pc_info => l_pc_info,
537 x_return_status => x_return_status,
538 x_msg_data => x_msg_data,
539 x_msg_count => x_msg_count
540 );
541 END IF;
542
543 IF l_pc_info.submission_due_date IS NOT NULL THEN
544 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
545 FND_LOG.String
546 ( FND_LOG.LEVEL_PROCEDURE,
547 'fpa.sql.FPA_Process_Pvt.Create_Pc',
548 'Calling FPA_PlanningCycle_Pvt.Update_Pc_Sub_Due_Date'
549 );
550 END IF;
551
552
553 FPA_PlanningCycle_Pvt.Update_Pc_Sub_Due_Date
554 (
555 p_api_version => 1.0,
556 p_pc_info => l_pc_info,
557 x_return_status => x_return_status,
558 x_msg_data => x_msg_data,
559 x_msg_count => x_msg_count
560 );
561 END IF;
562
563 END IF;
564
565 IF p_pc_all_obj.pc_discount IS NOT NULL THEN
566 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
567 FND_LOG.String
568 ( FND_LOG.LEVEL_PROCEDURE,
569 'fpa.sql.FPA_Process_Pvt.Create_Pc',
570 'CAlling FPA_PlanningCycle_Pvt.Update_Pc_Discount_funds.'
571 );
572 END IF;
573
574
575 FPA_PlanningCycle_Pvt.Update_Pc_Discount_funds
576 (
577 p_api_version => 1.0,
578 p_disc_funds => l_discount_obj,
579 x_return_status => x_return_status,
580 x_msg_data => x_msg_data,
581 x_msg_count => x_msg_count
582 );
583 END IF;
584
585 IF p_pc_all_obj.pc_targets IS NOT NULL THEN
586 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
587 FND_LOG.String
588 ( FND_LOG.LEVEL_PROCEDURE,
589 'fpa.sql.FPA_Process_Pvt.Create_Pc',
590 'CAlling FPA_PlanningCycle_Pvt.Update_Pc_Fin_Targets.'
591 );
592 END IF;
593
594 FPA_PlanningCycle_Pvt.Update_Pc_Fin_Targets
595 (
596 p_api_version => 1.0,
597 p_fin_targets_tbl => l_fin_targets_tbl,
598 x_return_status => x_return_status,
599 x_msg_data => x_msg_data,
600 x_msg_count => x_msg_count
601 );
602 END IF;
603
604
605 IF p_pc_all_obj.pc_distr_list IS NOT NULL THEN
606 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
607 FND_LOG.String
608 ( FND_LOG.LEVEL_PROCEDURE,
609 'fpa.sql.FPA_Process_Pvt.Create_Pc',
610 'CAlling FPA_PlanningCycle_Pvt.Pa_Distrb_Lists_Insert_Row.'
611 );
612 END IF;
613
614 FPA_PlanningCycle_Pvt.Pa_Distrb_Lists_Insert_Row
615 (
616 p_api_version => 1.0,
617 p_distr_list => l_distr_list,
618 p_list_id => l_distr_list_id,
619 x_return_status => x_return_status,
620 x_msg_data => x_msg_data,
621 x_msg_count => x_msg_count
622 );
623 END IF;
624
625 --p_distr_list_id := l_distr_list_id
626 IF l_distr_list_items_tbl IS NOT NULL THEN
627 l_distr_list_items_tbl(1).list_id := l_distr_list_id;
628 END IF;
629
630 IF p_pc_all_obj.distr_list_items IS NOT NULL THEN
631 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
632 FND_LOG.String
633 ( FND_LOG.LEVEL_PROCEDURE,
634 'fpa.sql.FPA_Process_Pvt.Create_Pc',
635 'Calling FPA_PlanningCycle_Pvt.Pa_Dist_List_Items_Update_Row'
636 );
637 END IF;
638
639 FPA_PlanningCycle_Pvt.Pa_Dist_List_Items_Update_Row
640 (
641 p_api_version => 1.0,
642 p_distr_list_items_tbl => l_distr_list_items_tbl,
643 x_return_status => x_return_status,
644 x_msg_data => x_msg_data,
645 x_msg_count => x_msg_count
646 );
647 END IF;
648
649 -- Update and commit our changes
650 IF (p_commit = FND_API.G_TRUE) THEN
651 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
652 FND_LOG.String
653 ( FND_LOG.LEVEL_PROCEDURE,
654 'fpa.sql.FPA_Process_Pvt.Create_Pc',
655 'Updating and Committing.'
656 );
657 END IF;
658 dbms_aw.execute('UPDATE');
659 COMMIT;
660 END IF;
661
662 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
663 FND_LOG.String
664 ( FND_LOG.LEVEL_PROCEDURE,
665 'fpa.sql.FPA_Process_Pvt.Create_Pc.begin',
666 'CAlling Fpa_Utilities_Pvt.detach_AW.'
667 );
668 END IF;
669
670 -- Detach AW Workspace
671 Fpa_Utilities_Pvt.detach_AW
672 (
673 p_api_version => 1.0,
674 x_return_status => x_return_status,
675 x_msg_count => x_msg_count,
676 x_msg_data => x_msg_data
677 );
678
679
680 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
681 FND_LOG.String
682 (
683 FND_LOG.LEVEL_PROCEDURE,
684 'fpa.sql.FPA_Process_Pvt.Create_Pc.end',
685 'Exiting FPA_Process_Pvt.Create_Pc'
686 );
687 END IF;
688
689 EXCEPTION
690 WHEN FND_API.G_EXC_ERROR THEN
691 ROLLBACK;
692
693 x_return_status := FND_API.G_RET_STS_ERROR;
694 FND_MSG_PUB.Count_And_Get
695 (
696 p_count => x_msg_count,
697 p_data => x_msg_data
698 );
699 RAISE;
700
701 WHEN OTHERS THEN
702 FND_MESSAGE.SET_NAME('FPA','FPA_UNEXP_GENERAL_ERROR');
703 FND_MESSAGE.SET_TOKEN('SOURCE', 'fpa.sql.FPA_Process_Pvt.Create_Pc');
704 FND_MESSAGE.SET_TOKEN('SQL_ERR_CODE', SQLCODE);
705 FND_MESSAGE.SET_TOKEN('SQL_ERR_MSG', SQLERRM);
706 FND_MSG_PUB.ADD;
707 -- Detach AW Workspace
708 Fpa_Utilities_Pvt.detach_AW
709 (
710 p_api_version => 1.0,
711 x_return_status => x_return_status,
712 x_msg_count => x_msg_count,
713 x_msg_data => x_msg_data
714 );
715
716 ROLLBACK;
717 x_return_status := FND_API.G_RET_STS_ERROR;
718
719 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
720 FND_LOG.String
721 (
722 FND_LOG.LEVEL_ERROR,
723 'fpa.sql.FPA_Process_Pvt.Create_Pc',
724 SQLERRM
725 );
726 END IF;
727
728 FND_MSG_PUB.count_and_get
729 (
730 p_count => x_msg_count,
731 p_data => x_msg_data
732 );
733 --RAISE;
734
735 END Create_Pc;
736
737
738 /*
739 * UPDATE case for Planning Cycle(PC). This API checks for the null values
740 * in object types and only updates not null objects.
741 * The calling program populates only those objects in fpa_pc_all_obj
742 * which needs update.
743 */
744
745
746 PROCEDURE Update_Pc
747 ( p_api_version IN NUMBER,
748 p_commit IN VARCHAR2 := FND_API.G_FALSE,
749 p_pc_all_obj IN fpa_pc_all_obj,
750 x_return_status OUT NOCOPY VARCHAR2,
751 x_msg_data OUT NOCOPY VARCHAR2,
752 x_msg_count OUT NOCOPY NUMBER )
753 IS
754 l_pc_info fpa_pc_info_obj;
755 BEGIN
756 -- clear all previous messages.
757 FND_MSG_PUB.Initialize;
758
759 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
760 FND_LOG.String
761 (
762 FND_LOG.LEVEL_PROCEDURE,
763 'fpa.sql.FPA_Process_Pvt.Update_Pc.begin',
764 'Entering FPA_Process_Pvt.Update_Pc'
765 );
766 END IF;
767
768 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
769 FND_LOG.String
770 (
771 FND_LOG.LEVEL_PROCEDURE,
772 'fpa.sql.FPA_Process_Pvt.Update_Pc',
773 'Initializing l_pc_info with p_pc_all_obj.pc_info'
774 );
775 END IF;
776
777 -- Initialize local pcInfo object from the input pc_all object
778 l_pc_info := p_pc_all_obj.pc_info;
779
780 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
781 FND_LOG.String
782 (
783 FND_LOG.LEVEL_PROCEDURE,
784 'fpa.sql.FPA_Process_Pvt.Update_Pc',
785 'Calling Fpa_Utilities_Pvt.attach_AW'
786 );
787 END IF;
788
789 -- Attach AW Workspace
790 Fpa_Utilities_Pvt.attach_AW
791 (
792 p_api_version => 1.0,
793 p_attach_mode => 'rw',
794 x_return_status => x_return_status,
795 x_msg_count => x_msg_count,
796 x_msg_data => x_msg_data
797 );
798
799 IF p_pc_all_obj.pc_desc_fields IS NOT NULL THEN
800
801 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
802 FND_LOG.String
803 (
804 FND_LOG.LEVEL_PROCEDURE,
805 'fpa.sql.FPA_Process_Pvt.Update_Pc',
806 'Calling FPA_PlanningCycle_Pvt.Update_Pc_Desc_Fields'
807 );
808 END IF;
809 FPA_PlanningCycle_Pvt.Update_Pc_Desc_Fields
810 (
811 p_api_version => 1.0,
812 p_pc_all_obj => p_pc_all_obj,
813 x_return_status => x_return_status,
814 x_msg_data => x_msg_data,
815 x_msg_count => x_msg_count
816 );
817 END IF;
818
819 IF p_pc_all_obj.pc_investmix IS NOT NULL THEN
820
821 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
822 FND_LOG.String
823 (
824 FND_LOG.LEVEL_PROCEDURE,
825 'fpa.sql.FPA_Process_Pvt.Update_Pc',
826 'Calling FPA_PlanningCycle_Pvt.Update_Pc_Invest_Mix'
827 );
828 END IF;
829 FPA_PlanningCycle_Pvt.Update_Pc_Invest_Mix
830 (
831 p_api_version => 1.0,
832 p_inv_matrix => p_pc_all_obj.pc_investmix,
833 x_return_status => x_return_status,
834 x_msg_data => x_msg_data,
835 x_msg_count => x_msg_count
836 );
837 END IF;
838
839 IF p_pc_all_obj.pc_info IS NOT NULL THEN
840 IF p_pc_all_obj.pc_info.pc_category IS NOT NULL THEN
841
842 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
843 FND_LOG.String
844 (
845 FND_LOG.LEVEL_PROCEDURE,
846 'fpa.sql.FPA_Process_Pvt.Update_Pc',
847 'Calling FPA_PlanningCycle_Pvt.Update_Pc_Class_Category'
848 );
849 END IF;
850 FPA_PlanningCycle_Pvt.Update_Pc_Class_Category
851 (
852 p_api_version => 1.0,
853 p_pc_id => p_pc_all_obj.pc_info.planning_cycle,
854 p_catg_id => p_pc_all_obj.pc_info.pc_category,
855 x_return_status => x_return_status,
856 x_msg_data => x_msg_data,
857 x_msg_count => x_msg_count
858 );
859
860 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
861 FND_LOG.String
862 (
863 FND_LOG.LEVEL_PROCEDURE,
864 'fpa.sql.FPA_Process_Pvt.Update_Pc',
865 'Calling FPA_PlanningCycle_Pvt.Update_Pc_Calendar'
866 );
867 END IF;
868 FPA_PlanningCycle_Pvt.Update_Pc_Calendar
869 (
870 p_api_version => 1.0,
871 p_pc_info => l_pc_info,
872 x_return_status => x_return_status,
873 x_msg_data => x_msg_data,
874 x_msg_count => x_msg_count
875 );
876
877 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
878 FND_LOG.String
879 (
880 FND_LOG.LEVEL_PROCEDURE,
881 'fpa.sql.FPA_Process_Pvt.Update_Pc',
882 'Calling FPA_PlanningCycle_Pvt.Update_Pc_Currency'
883 );
884 END IF;
885 FPA_PlanningCycle_Pvt.Update_Pc_Currency
886 (
887 p_api_version => 1.0,
888 p_pc_info => l_pc_info,
889 x_return_status => x_return_status,
890 x_msg_data => x_msg_data,
891 x_msg_count => x_msg_count
892 );
893 END IF;
894 IF l_pc_info.submission_due_date IS NOT NULL THEN
895
896 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
897 FND_LOG.String
898 (
899 FND_LOG.LEVEL_PROCEDURE,
900 'fpa.sql.FPA_Process_Pvt.Update_Pc',
901 'Calling FPA_PlanningCycle_Pvt.Update_Pc_Sub_Due_Date'
902 );
903 END IF;
904 FPA_PlanningCycle_Pvt.Update_Pc_Sub_Due_Date
905 (
906 p_api_version => 1.0,
907 p_pc_info => l_pc_info,
908 x_return_status => x_return_status,
909 x_msg_data => x_msg_data,
910 x_msg_count => x_msg_count
911 );
912 END IF;
913
914 END IF;
915
916 IF p_pc_all_obj.pc_discount IS NOT NULL THEN
917 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
918 FND_LOG.String
919 (
920 FND_LOG.LEVEL_PROCEDURE,
921 'fpa.sql.FPA_Process_Pvt.Update_Pc',
922 'Calling FPA_PlanningCycle_Pvt.Update_Pc_Discount_funds'
923 );
924 END IF;
925 FPA_PlanningCycle_Pvt.Update_Pc_Discount_funds
926 (
927 p_api_version => 1.0,
928 p_disc_funds => p_pc_all_obj.pc_discount,
929 x_return_status => x_return_status,
930 x_msg_data => x_msg_data,
931 x_msg_count => x_msg_count
932 );
933 END IF;
934
935 IF p_pc_all_obj.pc_targets IS NOT NULL THEN
936
937 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
938 FND_LOG.String
939 (
940 FND_LOG.LEVEL_PROCEDURE,
941 'fpa.sql.FPA_Process_Pvt.Update_Pc',
942 'Calling FPA_PlanningCycle_Pvt.Update_Pc_Fin_Targets'
943 );
944 END IF;
945 FPA_PlanningCycle_Pvt.Update_Pc_Fin_Targets
946 (
947 p_api_version => 1.0,
948 p_fin_targets_tbl => p_pc_all_obj.pc_targets,
949 x_return_status => x_return_status,
950 x_msg_data => x_msg_data,
951 x_msg_count => x_msg_count
952 );
953 END IF;
954
955 IF p_pc_all_obj.pc_invest_criteria IS NOT NULL THEN
956
957 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
958 FND_LOG.String
959 (
960 FND_LOG.LEVEL_PROCEDURE,
961 'fpa.sql.FPA_Process_Pvt.Update_Pc',
962 'Calling FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data'
963 );
964 END IF;
965
966 FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data
967 (
968 p_api_version => 1.0,
969 p_inv_crit_tbl => p_pc_all_obj.pc_invest_criteria,
970 x_return_status => x_return_status,
971 x_msg_data => x_msg_data,
972 x_msg_count => x_msg_count
973 );
974 END IF;
975
976
977 IF p_pc_all_obj.distr_list_items IS NOT NULL THEN
978
979 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
980 FND_LOG.String
981 (
982 FND_LOG.LEVEL_PROCEDURE,
983 'fpa.sql.FPA_Process_Pvt.Update_Pc',
984 'Calling FPA_PlanningCycle_Pvt.Pa_Dist_List_Items_Update_Row'
985 );
986 END IF;
987 FPA_PlanningCycle_Pvt.Pa_Dist_List_Items_Update_Row
988 (
989 p_api_version => 1.0,
990 p_distr_list_items_tbl => p_pc_all_obj.distr_list_items,
991 x_return_status => x_return_status,
992 x_msg_data => x_msg_data,
993 x_msg_count => x_msg_count
994 );
995 END IF;
996
997
998 -- Update and commit our changes
999 IF (p_commit = FND_API.G_TRUE) THEN
1000 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1001 FND_LOG.String
1002 (
1003 FND_LOG.LEVEL_PROCEDURE,
1004 'fpa.sql.FPA_Process_Pvt.Update_Pc',
1005 'Updating AW and committing database.'
1006 );
1007 END IF;
1008 dbms_aw.execute('UPDATE');
1009 COMMIT;
1010 END IF;
1011
1012 -- Detach AW Workspace
1013 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1014 FND_LOG.String
1015 (
1016 FND_LOG.LEVEL_PROCEDURE,
1017 'fpa.sql.FPA_Process_Pvt.Update_Pc',
1018 'Calling Fpa_Utilities_Pvt.detach_AW.'
1019 );
1020 END IF;
1021
1022 Fpa_Utilities_Pvt.detach_AW
1023 (
1024 p_api_version => 1.0,
1025 x_return_status => x_return_status,
1026 x_msg_count => x_msg_count,
1027 x_msg_data => x_msg_data
1028 );
1029
1030 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1031 FND_LOG.String
1032 (
1033 FND_LOG.LEVEL_PROCEDURE,
1034 'fpa.sql.FPA_Process_Pvt.Update_Pc.end',
1035 'Exiting FPA_Process_Pvt.Update_Pc'
1036 );
1037 END IF;
1038
1039
1040 EXCEPTION
1041 WHEN OTHERS THEN
1042 FND_MESSAGE.SET_NAME('FPA','FPA_UNEXP_GENERAL_ERROR');
1043 FND_MESSAGE.SET_TOKEN('SOURCE', 'fpa.sql.FPA_Process_Pvt.Update_Pc');
1044 FND_MESSAGE.SET_TOKEN('SQL_ERR_CODE', SQLCODE);
1045 FND_MESSAGE.SET_TOKEN('SQL_ERR_MSG', SQLERRM);
1046 FND_MSG_PUB.ADD;
1047 ROLLBACK;
1048 -- Detach AW Workspace
1049 Fpa_Utilities_Pvt.detach_AW
1050 (
1051 p_api_version => 1.0,
1052 x_return_status => x_return_status,
1053 x_msg_count => x_msg_count,
1054 x_msg_data => x_msg_data
1055 );
1056
1057 x_return_status := FND_API.G_RET_STS_ERROR;
1058
1059 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1060 FND_LOG.String
1061 (
1062 FND_LOG.LEVEL_ERROR,
1063 'fpa.sql.FPA_Process_Pvt.Update_Pc',
1064 SQLERRM
1065 );
1066 END IF;
1067
1068 FND_MSG_PUB.count_and_get
1069 (
1070 p_count => x_msg_count,
1071 p_data => x_msg_data
1072 );
1073 -- RAISE;
1074 END Update_Pc;
1075
1076 /*
1077 * Sets the Initiate Process in Planning Cycle(PC).
1078 */
1079
1080 PROCEDURE Set_Pc_Initiate
1081 ( p_api_version IN NUMBER,
1082 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1083 p_pc_id IN NUMBER,
1084 p_pc_name IN VARCHAR2,
1085 p_pc_desc IN VARCHAR2,
1086 p_sub_due_date IN DATE,
1087 x_return_status OUT NOCOPY VARCHAR2,
1088 x_msg_data OUT NOCOPY VARCHAR2,
1089 x_msg_count OUT NOCOPY NUMBER )
1090 IS
1091 l_cal_name VARCHAR2(80);
1092 l_period_type VARCHAR2(80);
1093 l_cal_period_type VARCHAR2(60);
1094
1095 l_last_pc_id NUMBER;
1096
1097 CURSOR c_last_pc_id IS
1098 SELECT prevPC.planning_cycle
1099 FROM FPA_AW_PC_INFO_V prevPC,
1100 FPA_AW_PC_INFO_V currPC
1101 WHERE prevPC.portfolio = currPC.portfolio
1102 AND currPC.planning_cycle = p_pc_id
1103 AND prevPC.last_flag = 1;
1104
1105 BEGIN
1106
1107 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1108 FND_LOG.String
1109 (
1110 FND_LOG.LEVEL_PROCEDURE,
1111 'fpa.sql.FPA_Process_Pvt.Set_Pc_Initiate.begin',
1112 'Entering FPA_Process_Pvt.Set_Pc_Initiate'
1113 );
1114 END IF;
1115
1116 OPEN c_last_pc_id;
1117 FETCH c_last_pc_id INTO l_last_pc_id ;
1118 CLOSE c_last_pc_id;
1119
1120 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1121 FND_LOG.String
1122 (
1123 FND_LOG.LEVEL_PROCEDURE,
1124 'fpa.sql.FPA_Process_Pvt.Set_Pc_Initiate.begin',
1125 'Value of l_last_pc_id ='||l_last_pc_id||', Calling Fpa_Utilities_Pvt.attach_AW'
1126 );
1127 END IF;
1128 -- Attach AW Workspace
1129 Fpa_Utilities_Pvt.attach_AW
1130 (
1131 p_api_version => 1.0,
1132 p_attach_mode => 'rw',
1133 x_return_status => x_return_status,
1134 x_msg_count => x_msg_count,
1135 x_msg_data => x_msg_data
1136 );
1137 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1138 FND_LOG.String
1139 (
1140 FND_LOG.LEVEL_PROCEDURE,
1141 'fpa.sql.FPA_Process_Pvt.Set_Pc_Initiate',
1142 'Executing query to retrieve Calendar information.'
1143 );
1144 END IF;
1145
1146 SELECT CALENDAR_NAME , PERIOD_TYPE, CAL_PERIOD_TYPE
1147 INTO l_cal_name, l_period_type, l_cal_period_type
1148 FROM FPA_AW_PC_INFO_V
1149 WHERE PLANNING_CYCLE = p_pc_id;
1150
1151 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1152 FND_LOG.String
1153 (
1154 FND_LOG.LEVEL_PROCEDURE,
1155 'fpa.sql.FPA_Process_Pvt.Set_Pc_Initiate',
1156 'Calling fpa_utils_pvt.load_gl_calendar'
1157 );
1158 END IF;
1159 fpa_utils_pvt.load_gl_calendar
1160 (
1161 p_api_version => 1.0,
1162 p_commit => FND_API.G_TRUE,
1163 p_calendar_name => l_cal_name,
1164 p_period_type => l_period_type,
1165 p_cal_period_type => l_cal_period_type,
1166 x_return_status => x_return_status,
1167 x_msg_data => x_msg_data,
1168 x_msg_count => x_msg_count
1169 );
1170
1171 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1172 FND_LOG.String
1173 (
1174 FND_LOG.LEVEL_PROCEDURE,
1175 'fpa.sql.FPA_Process_Pvt.Set_Pc_Initiate',
1176 'Calling fpa_planningcycle_pvt.Set_Pc_Initiate_Date.'
1177 );
1178 END IF;
1179
1180 FPA_PlanningCycle_Pvt.Set_Pc_Initiate_Date
1181 (
1182 p_api_version => 1.0,
1183 p_pc_id => p_pc_id,
1184 x_return_status => x_return_status,
1185 x_msg_data => x_msg_data,
1186 x_msg_count => x_msg_count
1187 );
1188
1189 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1190 FND_LOG.String
1191 (
1192 FND_LOG.LEVEL_PROCEDURE,
1193 'fpa.sql.FPA_Process_Pvt.Set_Pc_Initiate.begin',
1194 'Calling fpa_planningcycle_pvt.Set_Pc_Investment_Criteria.'
1195 );
1196 END IF;
1197
1198 FPA_PlanningCycle_Pvt.Set_Pc_Investment_Criteria
1199 (
1200 p_api_version => 1.0,
1201 p_pc_id => p_pc_id,
1202 x_return_status => x_return_status,
1203 x_msg_data => x_msg_data,
1204 x_msg_count => x_msg_count
1205 );
1206
1207
1208 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1209 FND_LOG.String
1210 (
1211 FND_LOG.LEVEL_PROCEDURE,
1212 'fpa.sql.FPA_Process_Pvt.Set_Pc_Initiate.begin',
1213 'Calling FPA_PORTFOLIO_PROJECT_SETS_PVT.create_project_set'
1214 );
1215 END IF;
1216
1217 FPA_PORTFOLIO_PROJECT_SETS_PVT.create_project_set
1218 (
1219 p_api_version => 1.0,
1220 p_pc_id => p_pc_id,
1221 x_return_status => x_return_status,
1222 x_msg_data => x_msg_data,
1223 x_msg_count => x_msg_count
1224 );
1225
1226 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1227 FND_LOG.String
1228 (
1229 FND_LOG.LEVEL_PROCEDURE,
1230 'fpa.sql.FPA_Process_Pvt.Set_Pc_Initiate.begin',
1231 'Calling FPA_Main_Process_Pvt.Initiate_Workflow.'
1232 );
1233 END IF;
1234
1235 FPA_Main_Process_Pvt.Initiate_Workflow
1236 (
1237 p_pc_name => p_pc_name,
1238 p_pc_id => p_pc_id,
1239 p_last_pc_id => l_last_pc_id ,
1240 p_pc_description => p_pc_desc,
1241 p_pc_date_initiated => SYSDATE,
1242 p_due_date => p_sub_due_date,
1243 x_return_status => x_return_status,
1244 x_msg_data => x_msg_data,
1245 x_msg_count => x_msg_count
1246 );
1247
1248 -- Update and commit our changes
1249 IF (p_commit = FND_API.G_TRUE) THEN
1250 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1251 FND_LOG.String
1252 (
1253 FND_LOG.LEVEL_PROCEDURE,
1254 'fpa.sql.FPA_Process_Pvt.Set_Pc_Initiate.begin',
1255 'Updating AW and committing database.'
1256 );
1257 END IF;
1258 dbms_aw.execute('UPDATE');
1259 COMMIT;
1260 END IF;
1261
1262 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1263 FND_LOG.String
1264 (
1265 FND_LOG.LEVEL_PROCEDURE,
1266 'fpa.sql.FPA_Process_Pvt.Set_Pc_Initiate.begin',
1267 'Calling Fpa_Utilities_Pvt.detach_AW'
1268 );
1269 END IF;
1270 -- Detach AW Workspace
1271 Fpa_Utilities_Pvt.detach_AW
1272 (
1273 p_api_version => 1.0,
1274 x_return_status => x_return_status,
1275 x_msg_count => x_msg_count,
1276 x_msg_data => x_msg_data
1277 );
1278
1279 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1280 FND_LOG.String
1281 (
1282 FND_LOG.LEVEL_PROCEDURE,
1283 'fpa.sql.FPA_Process_Pvt.Set_Pc_Initiate.end',
1284 'Exiting FPA_Process_Pvt.Set_Pc_Initiate'
1285 );
1286 END IF;
1287
1288
1289 EXCEPTION
1290 WHEN OTHERS THEN
1291 FND_MESSAGE.SET_NAME('FPA','FPA_UNEXP_GENERAL_ERROR');
1292 FND_MESSAGE.SET_TOKEN('SOURCE', 'fpa.sql.FPA_Process_Pvt.Set_Pc_Initiate');
1293 FND_MESSAGE.SET_TOKEN('SQL_ERR_CODE', SQLCODE);
1294 FND_MESSAGE.SET_TOKEN('SQL_ERR_MSG', SQLERRM);
1295 FND_MSG_PUB.ADD;
1296 ROLLBACK;
1297 -- Detach AW Workspace
1298 Fpa_Utilities_Pvt.detach_AW
1299 (
1300 p_api_version => 1.0,
1301 x_return_status => x_return_status,
1302 x_msg_count => x_msg_count,
1303 x_msg_data => x_msg_data
1304 );
1305
1306 x_return_status := FND_API.G_RET_STS_ERROR;
1307
1308 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1309 FND_LOG.String
1310 (
1311 FND_LOG.LEVEL_ERROR,
1312 'fpa.sql.FPA_Process_Pvt.Set_Pc_Initiate',
1313 SQLERRM
1314 );
1315 END IF;
1316
1317 FND_MSG_PUB.count_and_get
1318 (
1319 p_count => x_msg_count,
1320 p_data => x_msg_data
1321 );
1322 --RAISE;
1323 END Set_Pc_Initiate;
1324
1325 /*
1326 * DELETES the User from Distribution list Subtab in Planning Cycle(PC).
1327 */
1328
1329 PROCEDURE Pa_Dist_List_Items_Delete_Row (
1330 p_api_version IN NUMBER,
1331 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1332 P_LIST_ITEM_ID IN NUMBER,
1333 x_return_status OUT NOCOPY VARCHAR2,
1334 x_msg_data OUT NOCOPY VARCHAR2,
1335 x_msg_count OUT NOCOPY NUMBER )
1336 IS
1337 BEGIN
1338 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1339 FND_LOG.String
1340 (
1341 FND_LOG.LEVEL_PROCEDURE,
1342 'fpa.sql.FPA_Process_Pvt.Pa_Dist_List_Items_Update_Row.begin',
1343 'Entering FPA_Process_Pvt.Pa_Dist_List_Items_Update_Row'
1344 );
1345 END IF;
1346
1347
1348 PA_DIST_LIST_ITEMS_PKG.Delete_Row
1349 (
1350 P_LIST_ITEM_ID => P_LIST_ITEM_ID
1351 );
1352
1353 -- Update and commit our changes
1354 IF (p_commit = FND_API.G_TRUE) THEN
1355 COMMIT;
1356 END IF;
1357
1358
1359 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1360 FND_LOG.String
1361 (
1362 FND_LOG.LEVEL_PROCEDURE,
1363 'fpa.sql.FPA_Process_Pvt.Pa_Dist_List_Items_Update_Row.end',
1364 'Exiting FPA_Process_Pvt.Pa_Dist_List_Items_Update_Row'
1365 );
1366 END IF;
1367
1368
1369 EXCEPTION
1370 WHEN OTHERS THEN
1371 FND_MESSAGE.SET_NAME('FPA','FPA_UNEXP_GENERAL_ERROR');
1372 FND_MESSAGE.SET_TOKEN('SOURCE', 'fpa.sql.FPA_Process_Pvt.Pa_Dist_List_Items_Update_Row');
1373 FND_MESSAGE.SET_TOKEN('SQL_ERR_CODE', SQLCODE);
1374 FND_MESSAGE.SET_TOKEN('SQL_ERR_MSG', SQLERRM);
1375 FND_MSG_PUB.ADD;
1376 ROLLBACK;
1377
1378 x_return_status := FND_API.G_RET_STS_ERROR;
1379
1380 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1381 FND_LOG.String
1382 (
1383 FND_LOG.LEVEL_ERROR,
1384 'fpa.sql.FPA_Process_Pvt.Pa_Dist_List_Items_Update_Row',
1385 SQLERRM
1386 );
1387 END IF;
1388
1389 FND_MSG_PUB.count_and_get
1390 (
1391 p_count => x_msg_count,
1392 p_data => x_msg_data
1393 );
1394 --RAISE;
1395
1396 END Pa_Dist_List_Items_Delete_Row;
1397
1398
1399 /************************************************************************************/
1400 -- PORTFOLIO PROCEDURES
1401 /************************************************************************************/
1402
1403 PROCEDURE Create_Portfolio
1404 (
1405 p_api_version IN NUMBER,
1406 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1407 p_portfolio_obj IN FPA_PORTFO_ALL_OBJ,
1408 x_portfolio_id OUT NOCOPY VARCHAR2,
1409 x_return_status OUT NOCOPY VARCHAR2,
1410 x_msg_data OUT NOCOPY VARCHAR2,
1411 x_msg_count OUT NOCOPY NUMBER
1412 )
1413 IS
1414 l_default_portf_user_tbl FPA_PORTFOLIO_USERS_TBL;
1415 l_msg_log VARCHAR2(2000) := null;
1416 BEGIN
1417
1418 -- clear all previous messages.
1419 FND_MSG_PUB.Initialize;
1420
1421 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1422 fnd_log.string
1423 (
1424 FND_LOG.LEVEL_PROCEDURE,
1425 'fpa.sql.fpa_process_pvt.create_portfolio.begin',
1426 'Entering fpa_process_pvt.create_portfolio'
1427 );
1428 END IF;
1429 Copy_Portfolio(p_portfolio_obj);
1430
1431
1432
1433 -- CHeck for DUPLICATE portfolio name
1434 IF FPA_Portfolio_PVT.Check_Portfolio_name(p_api_version ,
1435 NULL,
1436 portfolio_rec.portfolio_name,
1437 x_return_status,
1438 x_msg_data,
1439 x_msg_count) > 0 THEN
1440
1441 FND_MESSAGE.SET_NAME('FPA','FPA_DUP_PORTF_NAME');
1442 FND_MESSAGE.SET_TOKEN('PORTF_NAME',portfolio_rec.portfolio_name);
1443
1444 FND_MSG_PUB.ADD;
1445 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1446 fnd_log.string
1447 (
1448 FND_LOG.LEVEL_PROCEDURE,
1449 'fpa.sql.fpa_process_pvt.create_portfolio',
1450 'Duplicate Portfolio Name'
1451 );
1452 END IF;
1453 --RAISE known exception
1454 RAISE FND_API.G_EXC_ERROR;
1455 END IF;
1456
1457
1458 -- Attach the AW space read write.
1459 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1460 fnd_log.string
1461 (
1462 FND_LOG.LEVEL_STATEMENT,
1463 'fpa.sql.fpa_process_pvt.create_portfolio',
1464 'Attaching OLAP workspace: '
1465 );
1466 END IF;
1467 Fpa_Utilities_Pvt.attach_AW
1468 (
1469 p_api_version => 1.0,
1470 p_attach_mode => 'rw',
1471 x_return_status => x_return_status,
1472 x_msg_count => x_msg_count,
1473 x_msg_data => x_msg_data
1474 );
1475
1476
1477 -- Create portfolio
1478 FPA_Portfolio_PVT.Create_Portfolio
1479 (
1480 p_api_version,
1481 portfolio_rec,
1482 x_portfolio_id,
1483 x_return_status,
1484 x_msg_data,
1485 x_msg_count
1486 );
1487 -- Assign the portfolio ID that will be used by the newly created
1488 -- portfolio users
1489 portfolio_rec.portfolio_id := x_portfolio_id;
1490
1491
1492 --create/update the portfolio USERS access list
1493
1494 l_default_portf_user_tbl:= FPA_PORTFOLIO_USERS_TBL();
1495 l_default_portf_user_tbl:= FPA_PORTFOLIO_USERS_TBL(FPA_PORTFOLIO_USERS_OBJ(null,
1496 portfolio_rec.portfolio_id,
1497 portfolio_rec.Portfolio_owner_id,
1498 FPA_SECURITY_PVT.Get_Role_Id,sysdate,NULL));
1499
1500 -- Create the default portfolio user
1501 --Since each portfolio owner is also a security user
1502 -- so that usre need to created.
1503
1504 create_update_access_list
1505 ( l_default_portf_user_tbl,
1506 portfolio_rec.portfolio_id,
1507 x_return_status ,
1508 x_msg_data,
1509 x_msg_count
1510
1511 );
1512
1513 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR
1514 or x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
1515 l_msg_log := portfolio_rec.portfolio_id;
1516 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
1517 end if;
1518
1519 IF p_portfolio_obj.portfolio_users IS NOT NULL then
1520 -- Create or update the access list users
1521 create_update_access_list
1522 ( p_portfolio_obj.portfolio_users,
1523 portfolio_rec.portfolio_id,
1524 x_return_status ,
1525 x_msg_data,
1526 x_msg_count
1527 );
1528
1529 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR
1530 or x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
1531 l_msg_log := portfolio_rec.portfolio_id;
1532 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
1533 end if;
1534
1535 END IF;
1536
1537 --Update and commit our changes
1538 IF (p_commit = FND_API.G_TRUE) THEN
1539 dbms_aw.execute('UPDATE');
1540 COMMIT;
1541 END IF;
1542
1543
1544 -- Finally, detach the workspace
1545 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1546 fnd_log.string
1547 (
1548 FND_LOG.LEVEL_STATEMENT,
1549 'fpa.sql.fpa_process_pvt.create_portfolio',
1550 'Detaching OLAP workspace: '
1551 );
1552 END IF;
1553 -- Detach AW Workspace
1554 Fpa_Utilities_Pvt.detach_AW
1555 (
1556 p_api_version => 1.0,
1557 x_return_status => x_return_status,
1558 x_msg_count => x_msg_count,
1559 x_msg_data => x_msg_data
1560 );
1561
1562 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1563 fnd_log.string
1564 (
1565 FND_LOG.LEVEL_PROCEDURE,
1566 'fpa.sql.fpa_process_pvt.create_portfolio.end',
1567 'Exiting fpa_process_pvt.create_portfolio'
1568 );
1569 END IF;
1570
1571 EXCEPTION
1572 WHEN FPA_UTILITIES_PVT.G_EXCEPTION_ERROR THEN
1573
1574 ROLLBACK;
1575
1576 x_return_status := FND_API.G_RET_STS_ERROR;
1577 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1578 fnd_log.string
1579 (
1580 FND_LOG.LEVEL_ERROR,
1581 'fpa_process_pvt.create_portfolio, FPA_UTILITIES_PVT.G_EXCEPTION_ERROR '||l_msg_log,
1582 SQLERRM
1583 );
1584 END IF;
1585
1586 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1587 ,p_data => x_msg_data);
1588
1589 WHEN FND_API.G_EXC_ERROR THEN
1590
1591 ROLLBACK;
1592
1593 x_return_status := FND_API.G_RET_STS_ERROR;
1594 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1595 fnd_log.string
1596 (
1597 FND_LOG.LEVEL_ERROR,
1598 'fpa_process_pvt.create_portfolio',
1599 SQLERRM
1600 );
1601 END IF;
1602
1603 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1604 ,p_data => x_msg_data);
1605
1606 WHEN OTHERS THEN
1607 FND_MESSAGE.SET_NAME('FPA','FPA_UNEXP_GENERAL_ERROR');
1608 FND_MESSAGE.SET_TOKEN('SOURCE', 'fpa_process_pvt.create_portfolio');
1609 FND_MESSAGE.SET_TOKEN('SQL_ERR_CODE', SQLCODE);
1610 FND_MESSAGE.SET_TOKEN('SQL_ERR_MSG', SQLERRM);
1611 FND_MSG_PUB.ADD;
1612 -- Detach AW Workspace
1613 Fpa_Utilities_Pvt.detach_AW
1614 (
1615 p_api_version => 1.0,
1616 x_return_status => x_return_status,
1617 x_msg_count => x_msg_count,
1618 x_msg_data => x_msg_data
1619 );
1620
1621 ROLLBACK;
1622 x_return_status := FND_API.G_RET_STS_ERROR;
1623
1624 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1625 fnd_log.string
1626 (
1627 FND_LOG.LEVEL_ERROR,
1628 'fpa_process_pvt.create_portfolio',
1629 SQLERRM
1630 );
1631 END IF;
1632
1633 FND_MSG_PUB.count_and_get
1634 (
1635 p_count => x_msg_count,
1636 p_data => x_msg_data
1637 );
1638
1639 END;
1640
1641 /************************************************************************************
1642 ************************************************************************************/
1643 -- The procedure Delete_Portfolio removes the portfolio from aw and Tl table
1644
1645 PROCEDURE Delete_Portfolio
1646 (
1647 p_api_version IN NUMBER,
1648 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1649 p_portfolio_id IN NUMBER,
1650 x_return_status OUT NOCOPY VARCHAR2,
1651 x_msg_data OUT NOCOPY VARCHAR2,
1652 x_msg_count OUT NOCOPY NUMBER
1653 )
1654 IS
1655 BEGIN
1656 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1657 fnd_log.string
1658 (
1659 FND_LOG.LEVEL_PROCEDURE,
1660 'fpa_process_pvt.Delete_Portfolio.begin',
1661 'Entering fpa_process_pvt.Delete_Portfolio'
1662 );
1663 END IF;
1664 -- Attach the AW space read write.
1665 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1666 fnd_log.string
1667 (
1668 FND_LOG.LEVEL_STATEMENT,
1669 'fpa.sql.fpa_process_pvt.Delete_Portfolio',
1670 'Attaching OLAP workspace: '
1671 );
1672 END IF;
1673 Fpa_Utilities_Pvt.attach_AW
1674 (
1675 p_api_version => 1.0,
1676 p_attach_mode => 'rw',
1677 x_return_status => x_return_status,
1678 x_msg_count => x_msg_count,
1679 x_msg_data => x_msg_data
1680 );
1681
1682
1683
1684 -- Delete the portfolio
1685 FPA_Portfolio_PVT.Delete_Portfolio
1686 (
1687 p_api_version => p_api_version,
1688 p_portfolio_id => p_portfolio_id ,
1689 x_return_status => x_return_status,
1690 x_msg_data => x_msg_data,
1691 x_msg_count =>x_msg_count
1692 );
1693
1694
1695 --Update and commit our changes
1696 IF (p_commit = FND_API.G_TRUE) THEN
1697 dbms_aw.execute('UPDATE');
1698 COMMIT;
1699 END IF;
1700
1701 -- Finally, detach the workspace
1702 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1703 fnd_log.string
1704 (
1705 FND_LOG.LEVEL_STATEMENT,
1706 'fpa.sql.fpa_process_pvt.Delete_Portfolio',
1707 'Detaching OLAP workspace: '
1708 );
1709 END IF;
1710 -- Detach AW Workspace
1711 Fpa_Utilities_Pvt.detach_AW
1712 (
1713 p_api_version => 1.0,
1714 x_return_status => x_return_status,
1715 x_msg_count => x_msg_count,
1716 x_msg_data => x_msg_data
1717 );
1718
1719 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1720 fnd_log.string
1721 (
1722 FND_LOG.LEVEL_PROCEDURE,
1723 'fpa.sql.fpa_process_pvt.Delete_Portfolio.end',
1724 'Exiting fpa_process_pvt.Delete_Portfolio'
1725 );
1726 END IF;
1727
1728 EXCEPTION
1729
1730 WHEN OTHERS THEN
1731 -- Detach AW Workspace
1732 FND_MESSAGE.SET_NAME('FPA','FPA_UNEXP_GENERAL_ERROR');
1733 FND_MESSAGE.SET_TOKEN('SOURCE', 'fpa_process_pvt.Delete_Portfolio');
1734 FND_MESSAGE.SET_TOKEN('SQL_ERR_CODE', SQLCODE);
1735 FND_MESSAGE.SET_TOKEN('SQL_ERR_MSG', SQLERRM);
1736 FND_MSG_PUB.ADD;
1737 -- Detach AW Workspace
1738 Fpa_Utilities_Pvt.detach_AW
1739 (
1740 p_api_version => 1.0,
1741 x_return_status => x_return_status,
1742 x_msg_count => x_msg_count,
1743 x_msg_data => x_msg_data
1744 );
1745
1746 ROLLBACK;
1747 x_return_status := FND_API.G_RET_STS_ERROR;
1748
1749 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1750 fnd_log.string
1751 (
1752 FND_LOG.LEVEL_ERROR,
1753 'fpa_process_pvt.Delete_Portfolio',
1754 SQLERRM
1755 );
1756 END IF;
1757
1758 FND_MSG_PUB.count_and_get
1759 (
1760 p_count => x_msg_count,
1761 p_data => x_msg_data
1762 );
1763
1764
1765 END Delete_Portfolio;
1766
1767
1768
1769
1770 /************************************************************************************
1771 ************************************************************************************/
1772 -- The procedure update_Portfolio update the portfolio obhject measures
1773 -- in th AW and the table level
1774
1775 PROCEDURE Update_Portfolio
1776 (
1777 p_api_version IN NUMBER,
1778 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1779 p_portfolio_obj IN FPA_PORTFO_ALL_OBJ,
1780 x_return_status OUT NOCOPY VARCHAR2,
1781 x_msg_data OUT NOCOPY VARCHAR2,
1782 x_msg_count OUT NOCOPY NUMBER
1783 )
1784
1785 IS
1786 BEGIN
1787 -- clear all previous messages.
1788 FND_MSG_PUB.Initialize;
1789
1790 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1791 fnd_log.string
1792 (
1793 FND_LOG.LEVEL_PROCEDURE,
1794 'fpa.sql.fpa_process_pvt.update_portfolio.begin',
1795 'Entering fpa_process_pvt.update_portfolio'
1796 );
1797 END IF;
1798
1799 --copy input object to the record type
1800 Copy_Portfolio(p_portfolio_obj);
1801
1802 -- CHeck for DUPLICATE portfolio name
1803 IF FPA_Portfolio_PVT.Check_Portfolio_name(p_api_version ,
1804 portfolio_rec.portfolio_id,
1805 portfolio_rec.portfolio_name,
1806 x_return_status,
1807 x_msg_data,
1808 x_msg_count) > 0 THEN
1809
1810 FND_MESSAGE.SET_NAME('FPA','FPA_DUP_PORTF_NAME');
1811 FND_MESSAGE.SET_TOKEN('PORTF_NAME',portfolio_rec.portfolio_name);
1812 FND_MSG_PUB.ADD;
1813
1814 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1815 fnd_log.string
1816 (
1817 FND_LOG.LEVEL_PROCEDURE,
1818 'fpa.sql.fpa_process_pvt.update_portfolio',
1819 'Duplicate Portfolio Name'
1820 );
1821 END IF;
1822 --raise the known duplicate exception
1823 RAISE FND_API.G_EXC_ERROR;
1824 END IF;
1825
1826
1827 -- Attach the AW space read write.
1828 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1829 fnd_log.string
1830 (
1831 FND_LOG.LEVEL_STATEMENT,
1832 'fpa.sql.fpa_process_pvt.update_portfolio',
1833 'Attaching OLAP workspace: '
1834 );
1835 END IF;
1836 Fpa_Utilities_Pvt.attach_AW
1837 (
1838 p_api_version => 1.0,
1839 p_attach_mode => 'rw',
1840 x_return_status => x_return_status,
1841 x_msg_count => x_msg_count,
1842 x_msg_data => x_msg_data
1843 );
1844
1845
1846 --update portfolio name description
1847 FPA_Portfolio_PVT.Upadate_Portfolio_Descr
1848 (
1849 p_api_version,
1850 portfolio_rec,
1851 x_return_status,
1852 x_msg_data,
1853 x_msg_count
1854 );
1855 --update portfolio type
1856 FPA_Portfolio_PVT.Upadate_Portfolio_type
1857 (
1858 p_api_version,
1859 portfolio_rec.portfolio_id ,
1860 portfolio_rec.portfolio_type ,
1861 x_return_status,
1862 x_msg_data,
1863 x_msg_count
1864 );
1865
1866 --update portfolio organization
1867 FPA_Portfolio_PVT.Upadate_Portfolio_organization
1868 (
1869 p_api_version,
1870 portfolio_rec.portfolio_id ,
1871 portfolio_rec.Portfolio_start_org_id ,
1872 x_return_status ,
1873 x_msg_data,
1874 x_msg_count
1875 );
1876
1877 -- Update the portfolio onwer user
1878
1879 FPA_SECURITY_PVT.update_portfolio_owner
1880 (
1881 p_api_version => p_api_version,
1882 p_init_msg_list => 'F',
1883 p_portfolio_id => portfolio_rec.portfolio_id,
1884 p_person_id => portfolio_rec.Portfolio_owner_id,
1885 x_return_status => x_return_status,
1886 x_msg_count => x_msg_count,
1887 x_msg_data => x_msg_data
1888 );
1889
1890 IF p_portfolio_obj.portfolio_users IS NOT NULL then
1891 --create/update the access list
1892 create_update_access_list
1893 ( p_portfolio_obj.portfolio_users,
1894 portfolio_rec.portfolio_id,
1895 x_return_status ,
1896 x_msg_data,
1897 x_msg_count
1898
1899 );
1900 END IF ;
1901
1902 --Update and commit our changes
1903 IF (p_commit = FND_API.G_TRUE) THEN
1904 dbms_aw.execute('UPDATE');
1905 COMMIT;
1906 END IF;
1907
1908 -- Finally, detach the workspace
1909 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1910 fnd_log.string
1911 (
1912 FND_LOG.LEVEL_STATEMENT,
1913 'fpa.sql.fpa_process_pvt.create_portfolio',
1914 'Detaching OLAP workspace: '
1915 );
1916 END IF;
1917 -- Detach AW Workspace
1918 Fpa_Utilities_Pvt.detach_AW
1919 (
1920 p_api_version => 1.0,
1921 x_return_status => x_return_status,
1922 x_msg_count => x_msg_count,
1923 x_msg_data => x_msg_data
1924 );
1925
1926 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1927 fnd_log.string
1928 (
1929 FND_LOG.LEVEL_PROCEDURE,
1930 'fpa.sql.fpa_process_pvt.create_portfolio.end',
1931 'Exiting fpa_process_pvt.create_portfolio'
1932 );
1933 END IF;
1934
1935 EXCEPTION
1936 WHEN FND_API.G_EXC_ERROR THEN
1937 ROLLBACK;
1938
1939
1940 x_return_status := FND_API.G_RET_STS_ERROR;
1941 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1942 fnd_log.string
1943 (
1944 FND_LOG.LEVEL_ERROR,
1945 'fpa_process_pvt.create_portfolio',
1946 SQLERRM
1947 );
1948 END IF;
1949
1950 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1951 ,p_data => x_msg_data);
1952
1953 RAISE;
1954
1955 WHEN OTHERS THEN
1956 -- Detach AW Workspace
1957 FND_MESSAGE.SET_NAME('FPA','FPA_UNEXP_GENERAL_ERROR');
1958 FND_MESSAGE.SET_TOKEN('SOURCE', 'fpa_process_pvt.update_portfolio');
1959 FND_MESSAGE.SET_TOKEN('SQL_ERR_CODE', SQLCODE);
1960 FND_MESSAGE.SET_TOKEN('SQL_ERR_MSG', SQLERRM);
1961 FND_MSG_PUB.ADD;
1962 -- Detach AW Workspace
1963 Fpa_Utilities_Pvt.detach_AW
1964 (
1965 p_api_version => 1.0,
1966 x_return_status => x_return_status,
1967 x_msg_count => x_msg_count,
1968 x_msg_data => x_msg_data
1969 );
1970
1971 ROLLBACK;
1972 x_return_status := FND_API.G_RET_STS_ERROR;
1973
1974 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1975 fnd_log.string
1976 (
1977 FND_LOG.LEVEL_ERROR,
1978 'fpa_process_pvt.update_portfolio',
1979 SQLERRM
1980 );
1981 END IF;
1982
1983 FND_MSG_PUB.count_and_get
1984 (
1985 p_count => x_msg_count,
1986 p_data => x_msg_data
1987 );
1988
1989
1990
1991 END;
1992
1993 /************************************************************************************
1994 ************************************************************************************/
1995 -- The procedure delete_portfolio_user delets teh portfolio access list user
1996 -- The procedure calls the Fpa security package to delete a user.
1997
1998 PROCEDURE delete_Portfolio_user
1999 (
2000 p_api_version IN NUMBER,
2001 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2002 p_project_party_id IN NUMBER,
2003 x_return_status OUT NOCOPY VARCHAR2,
2004 x_msg_data OUT NOCOPY VARCHAR2,
2005 x_msg_count OUT NOCOPY NUMBER
2006 ) IS
2007 BEGIN
2008
2009 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2010 fnd_log.string
2011 (
2012 FND_LOG.LEVEL_PROCEDURE,
2013 'fpa.sql.fpa_process_pvt.delete_portfolio_user.begin',
2014 'Entering fpa_process_pvt.delete_portfolio_user'
2015 );
2016 END IF;
2017
2018
2019 FPA_SECURITY_PVT.Delete_Portfolio_User
2020 (
2021 p_api_version => 1,
2022 p_init_msg_list => 'F',
2023 p_portfolio_party_id => p_project_party_id,
2024 p_instance_set_name=> 'PJP_PORTFOLIO_SET',
2025 x_return_status => x_return_status,
2026 x_msg_count => x_msg_count,
2027 x_msg_data => x_msg_data
2028 );
2029
2030 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2031 fnd_log.string
2032 (
2033 FND_LOG.LEVEL_PROCEDURE,
2034 'fpa.sql.fpa_process_pvt.delete_portfolio_user.end',
2035 'Exiting fpa_process_pvt.delete_portfolio_user'
2036 );
2037 END IF;
2038 IF x_return_status <> 'S' THEN
2039 BEGIN
2040 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2041 FND_MSG_PUB.count_and_get
2042 (
2043 p_count => x_msg_count,
2044 p_data => x_msg_data
2045 );
2046 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2047 END;
2048 END IF;
2049
2050 EXCEPTION
2051 WHEN OTHERS THEN
2052 FND_MESSAGE.SET_NAME('FPA','FPA_UNEXP_GENERAL_ERROR');
2053 FND_MESSAGE.SET_TOKEN('SOURCE', 'fpa_process_pvt.delete_Portfolio_user');
2054 FND_MESSAGE.SET_TOKEN('SQL_ERR_CODE', SQLCODE);
2055 FND_MESSAGE.SET_TOKEN('SQL_ERR_MSG', SQLERRM);
2056 FND_MSG_PUB.ADD;
2057 -- Detach AW Workspace
2058 Fpa_Utilities_Pvt.detach_AW
2059 (
2060 p_api_version => 1.0,
2061 x_return_status => x_return_status,
2062 x_msg_count => x_msg_count,
2063 x_msg_data => x_msg_data
2064 );
2065
2066 ROLLBACK;
2067 x_return_status := FND_API.G_RET_STS_ERROR;
2068 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2069 fnd_log.string
2070 (
2071 FND_LOG.LEVEL_ERROR,
2072 'fpa_process_pvt.delete_Portfolio_user',
2073 SQLERRM
2074 );
2075 END IF;
2076
2077 FND_MSG_PUB.count_and_get
2078 (
2079 p_count => x_msg_count,
2080 p_data => x_msg_data
2081 );
2082
2083 END;
2084 /************************************************************************************
2085 ************************************************************************************/
2086 --Procedure to copy the data from Object fields into the
2087 -- portfolio_rec to record
2088 -- This procedure is used locally by the package procedure.
2089
2090 PROCEDURE Copy_Portfolio(p_portfolio_obj IN FPA_PORTFO_ALL_OBJ)
2091 IS
2092 BEGIN
2093
2094 portfolio_rec.portfolio_id :=p_portfolio_obj.portf_desc_fields.id;
2095 portfolio_rec.portfolio_name:=p_portfolio_obj.portf_desc_fields.name;
2096 portfolio_rec.portfolio_desc:=p_portfolio_obj.portf_desc_fields.description;
2097 portfolio_rec.portfolio_owner_id:=p_portfolio_obj.portf_info.owner;
2098 portfolio_rec.portfolio_type:=p_portfolio_obj.portf_info.portfolio_class_code;
2099 portfolio_rec.portfolio_start_org_id:=p_portfolio_obj.portf_info.portfolio_organization;
2100 END;
2101
2102 /************************************************************************************
2103 ************************************************************************************/
2104 -- The procedure create_update_access_list create or update the portfloio access list user.
2105 -- The procedure calls the Fpa security package update/crete a user.
2106
2107 PROCEDURE create_update_access_list
2108 ( p_portf_users_tbl IN FPA_PORTFOLIO_USERS_TBL,
2109 p_portfolio_id NUMBER,
2110 x_return_status OUT NOCOPY VARCHAR2,
2111 x_msg_data OUT NOCOPY VARCHAR2,
2112 x_msg_count OUT NOCOPY NUMBER
2113 )
2114 IS
2115 l_project_party_id number;
2116
2117 -- standard parameters
2118 l_return_status VARCHAR2(1);
2119 l_api_name CONSTANT VARCHAR2(30) := 'Create_Update_Access_List';
2120 l_api_version CONSTANT NUMBER := 1.0;
2121 l_msg_log VARCHAR2(2000) := null;
2122 ----------------------------------------------------------------------------
2123
2124
2125 BEGIN
2126 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2127 fnd_log.string
2128 (
2129 FND_LOG.LEVEL_PROCEDURE,
2130 'fpa.sql.fpa_process_pvt.create_update_access_list.begin',
2131 'Entering fpa_process_pvt.create_update_access_list'
2132 );
2133 END IF;
2134
2135 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
2136
2137 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
2138 p_api_name => l_api_name,
2139 p_pkg_name => G_PKG_NAME,
2140 p_init_msg_list => 'T',
2141 l_api_version => l_api_version,
2142 p_api_version => l_api_version,
2143 p_api_type => G_API_TYPE,
2144 p_msg_log => 'Entering fpa_process_pvt.create_update_access_list.begin',
2145 x_return_status => x_return_status);
2146
2147 -- check if activity started successfully
2148 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
2149 l_msg_log := 'start_activity';
2150 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
2151 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
2152 l_msg_log := 'start_activity';
2153 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
2154 end if;
2155
2156
2157 IF p_portf_users_tbl IS NOT NULL THEN
2158
2159 FOR i IN p_portf_users_tbl.FIRST..p_portf_users_tbl.LAST
2160 LOOP
2161 BEGIN
2162
2163 --Check if the record is for create or for update
2164 IF p_portf_users_tbl(i).project_party_id IS NULL THEN
2165 --Its a new users to be created
2166
2167 FPA_SECURITY_PVT.create_portfolio_user(
2168 p_api_version => 1,
2169 p_init_msg_list => 'F',
2170 p_object_id => p_portfolio_id ,
2171 p_project_role_id => p_portf_users_tbl(i).role_id,
2172 p_party_id => p_portf_users_tbl(i).hz_party_id,
2173 p_start_date_active => p_portf_users_tbl(i).start_Date,
2174 p_end_date_active => p_portf_users_tbl(i).end_Date,
2175 x_portfolio_party_id => l_project_party_id,
2176 x_return_status => x_return_status,
2177 x_msg_count => x_msg_count,
2178 x_msg_data => x_msg_data
2179 );
2180
2181
2182
2183 ELSE
2184 -- Its a update requeste
2185 FPA_SECURITY_PVT.update_portfolio_user(
2186 p_api_version => 1,
2187 p_init_msg_list => 'F',
2188 p_portfolio_party_id => p_portf_users_tbl(i).project_party_id,
2189 p_project_role_id => p_portf_users_tbl(i).role_id,
2190 p_start_date_active => p_portf_users_tbl(i).start_Date,
2191 p_end_date_active => p_portf_users_tbl(i).end_Date,
2192 x_return_status => x_return_status,
2193 x_msg_count => x_msg_count,
2194 x_msg_data => x_msg_data
2195 );
2196
2197 END IF;
2198
2199 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
2200 l_msg_log := p_portf_users_tbl(i).project_party_id||','||p_portf_users_tbl(i).role_id;
2201 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
2202 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
2203 l_msg_log := p_portf_users_tbl(i).project_party_id||','||p_portf_users_tbl(i).role_id;
2204 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
2205 end if;
2206
2207 /*
2208 -- CHECK IF THE SECUCITY CALL IS SUCCESSFUL
2209 IF x_return_status <> 'S' THEN
2210 BEGIN
2211 x_return_status := 'U';
2212 FND_MSG_PUB.count_and_get
2213 (
2214 p_count => x_msg_count,
2215 p_data => x_msg_data
2216 );
2217 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2218 END;
2219 END IF;
2220 */
2221 END;
2222 END LOOP;
2223
2224 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2225 fnd_log.string
2226 (
2227 FND_LOG.LEVEL_PROCEDURE,
2228 'fpa.sql.fpa_process_pvt.create_update_access_list.end',
2229 'Exiting fpa_process_pvt.create_update_access_list'
2230 );
2231 END IF;
2232
2233 END IF;
2234
2235 FPA_UTILITIES_PVT.END_ACTIVITY(
2236 p_api_name => l_api_name,
2237 p_pkg_name => G_PKG_NAME,
2238 p_msg_log => l_msg_log,
2239 x_msg_count => x_msg_count,
2240 x_msg_data => x_msg_data);
2241
2242 EXCEPTION
2243 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
2244 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
2245 p_api_name => l_api_name,
2246 p_pkg_name => G_PKG_NAME,
2247 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
2248 p_msg_log => l_msg_log,
2249 x_msg_count => x_msg_count,
2250 x_msg_data => x_msg_data,
2251 p_api_type => G_API_TYPE);
2252
2253 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
2254 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
2255 p_api_name => l_api_name,
2256 p_pkg_name => G_PKG_NAME,
2257 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
2258 p_msg_log => l_msg_log,
2259 x_msg_count => x_msg_count,
2260 x_msg_data => x_msg_data,
2261 p_api_type => G_API_TYPE);
2262
2263 when OTHERS then
2264 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
2265 p_api_name => l_api_name,
2266 p_pkg_name => G_PKG_NAME,
2267 p_exc_name => 'OTHERS',
2268 p_msg_log => l_msg_log||SQLERRM,
2269 x_msg_count => x_msg_count,
2270 x_msg_data => x_msg_data,
2271 p_api_type => G_API_TYPE);
2272
2273 END;
2274
2275 /************************************************************************************/
2276 -- COLLECT PROJECT PROCEDURES
2277 /************************************************************************************/
2278
2279
2280 PROCEDURE Collect_Projects
2281 ( p_api_version IN NUMBER,
2282 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2283 p_pc_id IN NUMBER,
2284 x_return_status OUT NOCOPY VARCHAR2,
2285 x_msg_data OUT NOCOPY VARCHAR2,
2286 x_msg_count OUT NOCOPY NUMBER
2287 )
2288 IS
2289
2290 l_pc_id NUMBER;
2291 l_api_version NUMBER;
2292
2293 l_pc_name VARCHAR2(80);
2294 l_pc_description VARCHAR2(240);
2295 l_pc_date_initiated DATE;
2296 l_due_date DATE;
2297
2298 BEGIN
2299
2300 FND_MSG_PUB.Initialize;
2301
2302 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2303 FND_LOG.STRING
2304 (
2305 FND_LOG.LEVEL_PROCEDURE,
2306 'FPA.SQL.FPA_PROCESS_PVT.Collect_Projects.begin',
2307 'Entering FPA_PROCESS_PVT.Collect_Projects'
2308 );
2309 END IF;
2310
2311 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2312 FND_LOG.STRING
2313 (
2314 FND_LOG.LEVEL_PROCEDURE,
2315 'FPA.SQL.FPA_PROCESS_PVT.Collect_Projects.',
2316 'Calling Fpa_Utilities_Pvt.attach_AW'
2317 );
2318 END IF;
2319
2320 -- Attach AW Workspace
2321 Fpa_Utilities_Pvt.attach_AW
2322 (
2323 p_api_version => 1.0,
2324 p_attach_mode => 'rw',
2325 x_return_status => x_return_status,
2326 x_msg_count => x_msg_count,
2327 x_msg_data => x_msg_data
2328 );
2329
2330 l_pc_id := p_pc_id;
2331
2332 --Changes per MJC start here.
2333
2334 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2335 FND_LOG.STRING
2336 (
2337 FND_LOG.LEVEL_PROCEDURE,
2338 'FPA.SQL.FPA_PROCESS_PVT.Collect_Projects.',
2339 'Executing query to get PC name, desc, submission date and due date.'
2340 );
2341 END IF;
2342
2343 select a.name ,a.description ,b.initiate_date ,b.submission_due_date
2344 into l_pc_name, l_pc_description, l_pc_date_initiated, l_due_date
2345 from fpa_pcs_vl a ,fpa_aw_pc_info_v b
2346 where a.planning_cycle = b.planning_cycle
2347 and a.planning_cycle = l_pc_id;
2348
2349 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2350 FND_LOG.STRING
2351 (
2352 FND_LOG.LEVEL_PROCEDURE,
2353 'FPA.SQL.FPA_PROCESS_PVT.Collect_Projects.',
2354 'Calling Fpa_Main_Process_Pvt.Cancel_Workflow'
2355 );
2356 END IF;
2357
2358 Fpa_Main_Process_Pvt.Cancel_Workflow
2359 (
2360 p_pc_name => l_pc_name,
2361 p_pc_id => l_pc_id,
2362 p_pc_description => l_pc_description,
2363 p_pc_date_initiated => l_pc_date_initiated,
2364 p_due_date => l_due_date,
2365 x_return_status => x_return_status,
2366 x_msg_count => x_msg_count,
2367 x_msg_data => x_msg_data
2368 );
2369
2370 /*
2371 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2372 FND_LOG.STRING
2373 (
2374 FND_LOG.LEVEL_PROCEDURE,
2375 'FPA.SQL.FPA_PROCESS_PVT.Collect_Projects.',
2376 'Calling Fpa_Main_Process_Pvt.Force_User_Action'
2377 );
2378 END IF;
2379
2380 Fpa_Main_Process_Pvt.Force_User_Action
2381 (
2382 p_itemkey => l_pc_id,
2383 p_event_name => 'CANCEL_WORKFLOW',
2384 x_return_status => x_return_status,
2385 x_msg_count => x_msg_count,
2386 x_msg_data => x_msg_data
2387 );
2388 */
2389 --Changes per MJC end here.
2390
2391 -- FPA_PROJECT_PVT.Collect_Projects()
2392
2393 l_api_version := 1;
2394
2395 IF l_api_version = p_api_version THEN
2396 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2397 FND_LOG.STRING
2398 (
2399 FND_LOG.LEVEL_PROCEDURE,
2400 'FPA.SQL.FPA_PROCESS_PVT.Collect_Projects.',
2401 'Calling FPA_PlanningCycle_Pvt.Set_Pc_Status'
2402 );
2403 END IF;
2404
2405 FPA_PlanningCycle_Pvt.Set_Pc_Status
2406 (
2407 p_api_version => l_api_version,
2408 p_pc_id => l_pc_id,
2409 p_pc_status_code => 'ANALYSIS',
2410 x_return_status => x_return_status,
2411 x_msg_data => x_msg_data,
2412 x_msg_count => x_msg_count
2413 );
2414
2415 -- The Procedure call to move workflow to the next node
2416
2417
2418 -- Update and commit our changes
2419 IF (p_commit = FND_API.G_TRUE) THEN
2420 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2421 FND_LOG.STRING
2422 (
2423 FND_LOG.LEVEL_PROCEDURE,
2424 'FPA.SQL.FPA_PROCESS_PVT.Collect_Projects.',
2425 'Updating AW and commiting database'
2426 );
2427 END IF;
2428
2429 dbms_aw.execute('UPDATE');
2430 COMMIT;
2431 END IF;
2432
2433 -- Detach AW Workspace
2434 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2435 FND_LOG.STRING
2436 (
2437 FND_LOG.LEVEL_PROCEDURE,
2438 'FPA.SQL.FPA_PROCESS_PVT.Collect_Projects.',
2439 'Calling Fpa_Utilities_Pvt.detach_AW'
2440 );
2441 END IF;
2442
2443 Fpa_Utilities_Pvt.detach_AW
2444 (
2445 p_api_version => l_api_version,
2446 x_return_status => x_return_status,
2447 x_msg_count => x_msg_count,
2448 x_msg_data => x_msg_data
2449 );
2450
2451 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2452 FND_LOG.STRING
2453 (
2454 FND_LOG.LEVEL_PROCEDURE,
2455 'FPA.SQL.FPA_PROCESS_PVT.Collect_Projects.end',
2456 'Ending FPA_PROCESS_PVT.Collect_Projects'
2457 );
2458 END IF;
2459 END IF;
2460 EXCEPTION
2461 WHEN OTHERS THEN
2462 ROLLBACK;
2463
2464 -- Detach AW Workspace
2465 Fpa_Utilities_Pvt.detach_AW
2466 (
2467 p_api_version => l_api_version,
2468 x_return_status => x_return_status,
2469 x_msg_count => x_msg_count,
2470 x_msg_data => x_msg_data
2471 );
2472
2473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2474
2475 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2476 FND_LOG.STRING
2477 (
2478 FND_LOG.LEVEL_ERROR,
2479 'FPA.SQL.FPA_PROCESS_PVT.Collect_Projects',
2480 SQLERRM
2481 );
2482 END IF;
2483
2484 FND_MSG_PUB.count_and_get
2485 (
2486 p_count => x_msg_count,
2487 p_data => x_msg_data
2488 );
2489 RAISE;
2490 END Collect_Projects;
2491
2492
2493 PROCEDURE Add_Projects
2494 ( p_api_version IN NUMBER,
2495 p_commit IN VARCHAR2,
2496 p_scenario_id IN NUMBER,
2497 p_proj_id_str IN varchar2,
2498 p_project_source IN VARCHAR2,
2499 x_return_status OUT NOCOPY VARCHAR2,
2500 x_msg_data OUT NOCOPY VARCHAR2,
2501 x_msg_count OUT NOCOPY NUMBER
2502 )
2503
2504
2505 -- if p_project_source = 'PJT'. Add proj. from current plan
2506 -- if p_project_source = 'PJP'. Add proj. from Initial Scenario
2507 -- p_scenario_id is always the current scenario
2508
2509 IS
2510 -- TYPE projectIdType is TABLE of varchar2(4000) index by binary_integer;
2511 l_api_version NUMBER;
2512 l_init_scenario_id NUMBER;
2513 l_data_to_calc varchar2(30);
2514
2515 p_count integer := 1;
2516 added_project_id varchar2(30);
2517 l_project_str varchar2(2000);
2518 -- projectIdTbl projectIdType;
2519 projectIdTbl FPA_VALIDATION_PVT.PROJECT_ID_TBL_TYPE;
2520 l_exists varchar2(1);
2521 l_project_set_id number(15);
2522
2523
2524 cursor c_init_project_set is
2525 select pset.INIT_PROJECT_SET_ID
2526 from fpa_aw_sce_info_v sc, fpa_aw_pc_info_v pc, fpa_aw_project_sets_v pset
2527 where sc.planning_cycle = pc.planning_cycle
2528 and pc.portfolio = pset.portfolio
2529 and sc.scenario = p_scenario_id;
2530
2531 /* cursor c_added_projects(p_scenario number) is
2532 -- SELECT scenario ,project,scenario_project_valid from fpa_aw_proj_info_v where scenario = p_scenario;
2533 SELECT scenario ,project, scenario_project_valid
2534 FROM table (CAST( ( olap_table ('fpa.fpapjp duration query', 'fpa_advanced_search_tbl','',
2535 'DIMENSION scenario FROM scenario_d DIMENSION project from project_d MEASURE scenario_project_valid from scenario_project_m')) as fpa_advanced_search_tbl))
2536 WHERE scenario_project_valid = 1 and scenario = p_scenario;
2537 */
2538
2539 -- l_added_projects fpa_advanced_search_tbl%rowtype;
2540
2541 BEGIN
2542
2543
2544
2545 FND_MSG_PUB.Initialize;
2546
2547 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2548 FND_LOG.STRING
2549 (
2550 FND_LOG.LEVEL_PROCEDURE,
2551 'FPA.SQL.FPA_PROCESS_PVT.Add_Projects.begin',
2552 'Entering FPA_PROCESS_PVT.Add_Projects'
2553 );
2554
2555 END IF;
2556
2557 l_api_version := 1;
2558
2559 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2560 FND_LOG.STRING
2561 (
2562 FND_LOG.LEVEL_PROCEDURE,
2563 'FPA.SQL.FPA_PROCESS_PVT.Add_Projects',
2564 'Getting Initial Scenario Id'
2565 );
2566 END IF;
2567
2568
2569 -- Now we get the Initial Scenario Id.
2570 select scenario
2571 into l_init_scenario_id
2572 from fpa_aw_sce_info_v
2573 where is_initial_scenario = 1
2574 and planning_cycle = (select planning_cycle
2575 from fpa_aw_sces_v
2576 where scenario = p_scenario_id);
2577
2578 -- p_proj_id_str is passed as a string of project ids. The API calls for calculating project level data
2579 -- are called for each project. The project id string should be parsed and the ids stored in a pl/sql table
2580 -- for looping.
2581
2582 -- Begin parsing project id string
2583 l_project_str := p_proj_id_str;
2584 while (length(l_project_str) > 0) LOOP
2585
2586 added_project_id := substr(l_project_str,1,instr(l_project_str, ',')-1);
2587 if added_project_id is null then
2588 projectIdTbl(p_count) := l_project_str;
2589 l_project_str := null;
2590 else
2591
2592 projectIdTbl(p_count) := added_project_id;
2593 l_project_str := substr(l_project_str, (instr(l_project_str, ',') + 1));
2594 end if;
2595 p_count := p_count+1;
2596 end loop;
2597 -- end of parsing
2598
2599 IF p_project_source = 'PJT' THEN
2600
2601 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2602 FND_LOG.STRING
2603 (
2604 FND_LOG.LEVEL_PROCEDURE,
2605 'FPA.SQL.FPA_PROCESS_PVT.Add_Projects.Source = PJT',
2606 'Entering FPA_PROCESS_PVT.Add_Projects'
2607 );
2608 END IF;
2609
2610 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2611 FND_LOG.STRING
2612 (
2613 FND_LOG.LEVEL_PROCEDURE,
2614 'FPA.SQL.FPA_PROCESS_PVT.Add_Projects.Source = PJT',
2615 'Calling FPA_PROCESS_PVT.load_project_details_aw'
2616 );
2617 END IF;
2618
2619 -- Attaching and detaching A/w is not required for details load. This is handled internally within the project_details_aw API.
2620 FPA_PROCESS_PVT.load_project_details_aw
2621 (
2622 p_api_version => 1.0,
2623 p_init_msg_list => 'F',
2624 p_commit => FND_API.G_TRUE,
2625 p_type => 'ADD',
2626 p_scenario_id => l_init_scenario_id,
2627 p_projects => p_proj_id_str,
2628 x_return_status => x_return_status,
2629 x_msg_count => x_msg_data,
2630 x_msg_data => x_msg_count
2631 );
2632 end if;
2633
2634 -- irrespective of the project source, PJT or PJP, AW should be attached R/w here.
2635
2636 Fpa_Utilities_Pvt.attach_AW
2637 (
2638 p_api_version => 1.0,
2639 p_attach_mode => 'rw',
2640 x_return_status => x_return_status,
2641 x_msg_count => x_msg_count,
2642 x_msg_data => x_msg_data
2643 );
2644
2645 if p_project_source = 'PJT' then
2646
2647 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2648 FND_LOG.String
2649 (
2650 FND_LOG.LEVEL_PROCEDURE,
2651 'fpa.sql.FPA_Process_Pvt.Add_Projects.Source = PJT',
2652 'Calling fpa_scenario_pvt.calc_scenario_data for updating Scenario project data..'
2653 );
2654 END IF;
2655
2656 -- open a cursor to get the newly added projects
2657 -- Calculate Fin Data rollup data for all newly added projects in the scenario
2658
2659 open c_init_project_set;
2660 fetch c_init_project_set into l_project_set_id;
2661 close c_init_project_set;
2662
2663 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2664 FND_LOG.String
2665 (
2666 FND_LOG.LEVEL_PROCEDURE,
2667 'fpa.sql.FPA_Process_Pvt.Add_Projects.Source = PJT',
2668 'cursor processed to get Project Set. ID = '|| l_project_set_id
2669 );
2670 END IF;
2671
2672 -- Update fpapjp - set portfolio project set relation
2673 dbms_aw.execute('LMT project_set_d TO ' ||l_project_set_id);
2674
2675
2676 for i in projectIdTbl.first .. projectIdTbl.last
2677 loop
2678
2679 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2680 FND_LOG.STRING
2681 (
2682 FND_LOG.LEVEL_PROCEDURE,
2683 'FPA.SQL.FPA_PROCESS_PVT.Add_Projects.Source = PJT',
2684 'Calling fpa_scenario_pvt.calc_sceario_data in PROJ Mode for projectId= '||projectIdTbl(i)
2685 );
2686 END IF;
2687
2688 l_data_to_calc := 'PROJ';
2689 fpa_scenario_pvt.calc_scenario_data
2690 (
2691 p_api_version => 1.0,
2692 p_scenario_id => l_init_scenario_id,
2693 p_project_id => projectIdTbl(i),
2694 p_class_code_id => null,
2695 p_data_to_calc => l_data_to_calc,
2696 x_return_status => x_return_status,
2697 x_msg_count => x_msg_count,
2698 x_msg_data => x_msg_data
2699 );
2700
2701
2702 -- Bug 4297801 Call Project sets api when projects are added from current plan
2703
2704 l_exists := PA_PROJECT_SET_UTILS.check_projects_in_set(l_project_set_id, projectIdTbl(i));
2705
2706 IF l_exists = 'N' THEN
2707 -- add the project to the project set, if it does not yet exist
2708 PA_PROJECT_SETS_PUB.create_project_set_line
2709 ( p_project_set_id => l_project_set_id
2710 ,p_project_id => projectIdTbl(i)
2711 ,x_return_status => x_return_status
2712 ,x_msg_count => x_msg_count
2713 ,x_msg_data => x_msg_data
2714 );
2715 END IF;
2716
2717 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2718 FND_LOG.String
2719 (
2720 FND_LOG.LEVEL_PROCEDURE,
2721 'fpa.sql.FPA_Process_Pvt.Add_Projects.Source = PJT',
2722 'Completed Project Set API PA_PROJECT_SETS_PUB.create_project_set_line'
2723 );
2724 END IF;
2725
2726
2727 dbms_aw.execute('LMT project_d TO ' || projectIdTbl(i));
2728 dbms_aw.execute('project_set_project_m = yes');
2729
2730 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2731 FND_LOG.String
2732 (
2733 FND_LOG.LEVEL_PROCEDURE,
2734 'fpa.sql.FPA_Process_Pvt.Add_Projects.Source = PJT',
2735 'Completed AW Updates for project_set_project_m'
2736 );
2737 END IF;
2738
2739 end loop;
2740
2741 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2742 FND_LOG.STRING
2743 (
2744 FND_LOG.LEVEL_PROCEDURE,
2745 'FPA.SQL.FPA_PROCESS_PVT.Add_Projects.Source = PJT',
2746 'Completed scenario Project rollup calculations. proceeding to classcode'
2747 );
2748 END IF;
2749
2750 -- calculate classcode level data for all classcodes in the scenario
2751 -- Call copy_sce_proj_data in PJT mode to calculate total cost, benefit,
2752 -- and other metrics at all levels for the Initial Scenario
2753 l_data_to_calc := 'CLASS';
2754 fpa_scenario_pvt.calc_scenario_data
2755 (
2756 p_api_version => 1.0,
2757 p_scenario_id => l_init_scenario_id,
2758 p_project_id => null,
2759 p_class_code_id => null,
2760 p_data_to_calc => l_data_to_calc,
2761 x_return_status => x_return_status,
2762 x_msg_count => x_msg_count,
2763 x_msg_data => x_msg_data
2764 );
2765
2766 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2767 FND_LOG.STRING
2768 (
2769 FND_LOG.LEVEL_PROCEDURE,
2770 'FPA.SQL.FPA_PROCESS_PVT.Add_Projects.Source = PJT',
2771 'Completed Classcode rollup calculations. proceeding to scenario'
2772 );
2773 END IF;
2774 l_data_to_calc := 'SCEN';
2775 fpa_scenario_pvt.calc_scenario_data
2776 (
2777 p_api_version => 1.0,
2778 p_scenario_id => l_init_scenario_id,
2779 p_project_id => null,
2780 p_class_code_id => null,
2781 p_data_to_calc => l_data_to_calc,
2782 x_return_status => x_return_status,
2783 x_msg_count => x_msg_count,
2784 x_msg_data => x_msg_data
2785 );
2786
2787 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2788 FND_LOG.STRING
2789 (
2790 FND_LOG.LEVEL_PROCEDURE,
2791 'FPA.SQL.FPA_PROCESS_PVT.Add_Projects.Source = PJT',
2792 'Calling fpa_scorecards_pvt.calc_Scenario_wscores_aw Calculate weighted and cost weighted scores '
2793 );
2794 END IF;
2795
2796
2797 /* fpa_scorecards_pvt.Calc_Scenario_Wscores_Aw
2798 (
2799 p_api_version => 1.0,
2800 p_init_msg_list => FND_API.G_FALSE,
2801 p_scenario_id => l_init_scenario_id,
2802 x_return_status => x_return_status,
2803 x_msg_count => x_msg_count,
2804 x_msg_data => x_msg_data
2805 );
2806
2807 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2808 FND_LOG.STRING
2809 (
2810 FND_LOG.LEVEL_PROCEDURE,
2811 'FPA.SQL.FPA_PROCESS_PVT.Add_Projects.Source = PJT',
2812 'Completed calculations for Initial scenario. ScenarioId ='||l_init_scenario_id
2813 );
2814 END IF;
2815 */
2816
2817 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2818 FND_LOG.STRING
2819 (
2820 FND_LOG.LEVEL_PROCEDURE,
2821 'fpa.sql.fpa_project_pvt.Refresh_project',
2822 'Calling fpa.sql.FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations'
2823 );
2824 END IF;
2825
2826 FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations(
2827 p_api_version => 1.0,
2828 p_init_msg_list => 'F',
2829 p_validation_set => 'FPA_VALIDATION_TYPES',
2830 p_header_object_id => l_init_scenario_id,
2831 p_header_object_type => 'SCENARIO',
2832 p_line_projects_tbl => projectIdTbl,
2833 p_type => 'CREATE',
2834 x_return_status => x_return_status,
2835 x_msg_count => x_msg_count,
2836 x_msg_data => x_msg_data);
2837
2838 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2839 FND_LOG.STRING
2840 (
2841 FND_LOG.LEVEL_PROCEDURE,
2842 'fpa.sql.fpa_project_pvt.Refresh_project',
2843 'End fpa.sql.FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations'
2844 );
2845 END IF;
2846
2847
2848 end if; -- end of PJT processing. that is, project added to initial scenario
2849
2850
2851 -- no need to check for p_proj_source = PJP because the logic below is executed for all cases except
2852 -- adding project from current plan to initial scenario, there is no current scenario.
2853 if l_init_scenario_id <> p_scenario_id then -- then we are adding projects from Current Plan to current scenario (and initial scenario)
2854
2855 FPA_SCENARIO_PVT.copy_sce_project_Data
2856 (
2857 p_api_version => l_api_version,
2858 p_commit => FND_API.G_FALSE,
2859 p_target_scen_id => p_scenario_id,
2860 p_project_id_str => p_proj_id_str,
2861 x_return_status => x_return_status,
2862 x_msg_data => x_msg_data,
2863 x_msg_count => x_msg_count
2864 );
2865
2866 for i in projectIdTbl.first .. projectIdTbl.last
2867 loop
2868
2869 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2870 FND_LOG.STRING
2871 (
2872 FND_LOG.LEVEL_PROCEDURE,
2873 'FPA.SQL.FPA_PROCESS_PVT.Add_Projects.Source = PJP',
2874 'Calling fpa_scenario_pvt.calc_sceario_data in PROJFIN Mode for projectId= '||projectIdTbl(i)
2875 );
2876 END IF;
2877 -- Calculate npv,irr,roi for projects added to target scenario.
2878 -- these mertrics sshould not be copied from source scen. since discount rates for
2879 -- source and target scenarios could be different
2880
2881 l_data_to_calc := 'PROJFIN';
2882 fpa_scenario_pvt.calc_scenario_data
2883 (
2884 p_api_version => 1.0,
2885 p_scenario_id => p_scenario_id,
2886 p_project_id => projectIdTbl(i),
2887 p_class_code_id => null,
2888 p_data_to_calc => l_data_to_calc,
2889 x_return_status => x_return_status,
2890 x_msg_count => x_msg_count,
2891 x_msg_data => x_msg_data
2892 );
2893 end loop;
2894
2895
2896
2897 -- calculate classcode level data for all classcodes in the scenario
2898 -- Call copy_sce_proj_data in PJT mode to calculate total cost, benefit,
2899 -- and other metrics at all levels for the Initial Scenario
2900
2901 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2902 FND_LOG.STRING
2903 (
2904 FND_LOG.LEVEL_PROCEDURE,
2905 'FPA.SQL.FPA_PROCESS_PVT.Add_Projects.CurrentScenario',
2906 'Completed scenario Project rollup calculations. proceeding to classcode'
2907 );
2908 END IF;
2909
2910 l_data_to_calc := 'CLASS';
2911 fpa_scenario_pvt.calc_scenario_data
2912 (
2913 p_api_version => 1.0,
2914 p_scenario_id => p_scenario_id,
2915 p_project_id => null,
2916 p_class_code_id => null,
2917 p_data_to_calc => l_data_to_calc,
2918 x_return_status => x_return_status,
2919 x_msg_count => x_msg_count,
2920 x_msg_data => x_msg_data
2921 );
2922
2923 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2924 FND_LOG.STRING
2925 (
2926 FND_LOG.LEVEL_PROCEDURE,
2927 'FPA.SQL.FPA_PROCESS_PVT.Add_Projects.Current Scenario',
2928 'Completed Classcode rollup calculations. proceeding to scenario'
2929 );
2930 END IF;
2931
2932 l_data_to_calc := 'SCEN';
2933 fpa_scenario_pvt.calc_scenario_data
2934 (
2935 p_api_version => 1.0,
2936 p_scenario_id => p_scenario_id,
2937 p_project_id => null,
2938 p_class_code_id => null,
2939 p_data_to_calc => l_data_to_calc,
2940 x_return_status => x_return_status,
2941 x_msg_count => x_msg_count,
2942 x_msg_data => x_msg_data
2943 );
2944 /*
2945 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2946 FND_LOG.STRING
2947 (
2948 FND_LOG.LEVEL_PROCEDURE,
2949 'FPA.SQL.FPA_PROCESS_PVT.Add_Projects.Source = PJT',
2950 'Calling fpa_scorecards_pvt.calc_Scenario_wscores_aw Calculate weighted and cost weighted scores '
2951 );
2952 END IF;
2953 */
2954
2955 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2956 FND_LOG.STRING
2957 (
2958 FND_LOG.LEVEL_PROCEDURE,
2959 'fpa.sql.fpa_project_pvt.add_project',
2960 'Calling fpa.sql.FPA_SCORECARDS_PVT.Handle_Comments'
2961 );
2962 END IF;
2963
2964 FPA_SCORECARDS_PVT.Handle_Comments(
2965 p_api_version => p_api_version,
2966 p_init_msg_list => FND_API.G_TRUE,
2967 p_scenario_id => p_scenario_id,
2968 p_type => 'PJP',
2969 p_source_scenario_id => null,
2970 p_delete_project_id => null,
2971 x_return_status => x_return_status,
2972 x_msg_count => x_msg_count,
2973 x_msg_data => x_msg_data);
2974
2975
2976 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2977 FND_LOG.STRING
2978 (
2979 FND_LOG.LEVEL_PROCEDURE,
2980 'fpa.sql.fpa_project_pvt.Refresh_project',
2981 'Calling fpa.sql.FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations'
2982 );
2983 END IF;
2984
2985 FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations(
2986 p_api_version => 1.0,
2987 p_init_msg_list => 'F',
2988 p_validation_set => 'FPA_VALIDATION_TYPES',
2989 p_header_object_id => p_scenario_id,
2990 p_header_object_type => 'SCENARIO',
2991 p_line_projects_tbl => projectIdTbl,
2992 p_type => 'CREATE',
2993 x_return_status => x_return_status,
2994 x_msg_count => x_msg_count,
2995 x_msg_data => x_msg_data);
2996
2997 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2998 FND_LOG.STRING
2999 (
3000 FND_LOG.LEVEL_PROCEDURE,
3001 'fpa.sql.fpa_project_pvt.Refresh_project',
3002 'End fpa.sql.FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations'
3003 );
3004 END IF;
3005
3006 end if; -- end of processing for PJP source.
3007
3008
3009 -- Update and commit our changes
3010
3011 IF (p_commit = FND_API.G_TRUE) THEN
3012 dbms_aw.execute('UPDATE');
3013 COMMIT;
3014 END IF;
3015
3016 -- Detach AW Workspace
3017 Fpa_Utilities_Pvt.detach_AW
3018 (
3019 p_api_version => l_api_version,
3020 x_return_status => x_return_status,
3021 x_msg_count => x_msg_count,
3022 x_msg_data => x_msg_data
3023 );
3024
3025
3026 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3027 FND_LOG.STRING
3028 (
3029 FND_LOG.LEVEL_PROCEDURE,
3030 'FPA.SQL.FPA_PROCESS_PVT.Add_Projects.end',
3031 'Ending FPA_PROCESS_PVT.Add_Projects'
3032 );
3033 END IF;
3034
3035 EXCEPTION
3036 WHEN OTHERS THEN
3037 ROLLBACK;
3038
3039 Fpa_Utilities_Pvt.detach_AW
3040 (
3041 p_api_version => l_api_version,
3042 x_return_status => x_return_status,
3043 x_msg_count => x_msg_count,
3044 x_msg_data => x_msg_data
3045 );
3046
3047 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3048
3049 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3050 FND_LOG.STRING
3051 (
3052 FND_LOG.LEVEL_ERROR,
3053 'FPA.SQL.FPA_PROCESS_PVT.Add_Projects',
3054 SQLERRM
3055 );
3056 END IF;
3057
3058 FND_MSG_PUB.count_and_get
3059 (
3060 p_count => x_msg_count,
3061 p_data => x_msg_data
3062 );
3063 RAISE;
3064 END Add_Projects;
3065
3066
3067
3068 PROCEDURE Refresh_Projects
3069 ( p_api_version IN NUMBER,
3070 p_commit IN VARCHAR2,
3071 p_scenario_id IN NUMBER,
3072 p_proj_id_str IN varchar2,
3073 x_return_status OUT NOCOPY VARCHAR2,
3074 x_msg_data OUT NOCOPY VARCHAR2,
3075 x_msg_count OUT NOCOPY NUMBER
3076 )
3077 IS
3078
3079 -- TYPE projectIdType is TABLE of varchar2(4000) index by binary_integer;
3080 l_api_version NUMBER;
3081 l_data_to_calc varchar2(30);
3082
3083 p_count integer := 1;
3084 added_project_id varchar2(30);
3085 l_project_str varchar2(2000);
3086 --projectIdTbl projectIdType;
3087 projectIdTbl FPA_VALIDATION_PVT.PROJECT_ID_TBL_TYPE;
3088
3089 BEGIN
3090
3091 FND_MSG_PUB.Initialize;
3092
3093 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3094 FND_LOG.STRING
3095 (
3096 FND_LOG.LEVEL_PROCEDURE,
3097 'FPA.SQL.FPA_PROCESS_PVT.Refresh_Projects.begin',
3098 'Entering FPA_PROCESS_PVT.Refresh_Projects'
3099 );
3100
3101 END IF;
3102
3103 l_api_version := 1;
3104
3105 l_project_str := p_proj_id_str;
3106 while (length(l_project_str) > 0) LOOP
3107
3108 added_project_id := substr(l_project_str,1,instr(l_project_str, ',')-1);
3109 if added_project_id is null then
3110 projectIdTbl(p_count) := l_project_str;
3111 l_project_str := null;
3112 else
3113
3114 projectIdTbl(p_count) := added_project_id;
3115 l_project_str := substr(l_project_str, (instr(l_project_str, ',') + 1));
3116 end if;
3117 p_count := p_count+1;
3118
3119 end loop;
3120
3121 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3122 FND_LOG.STRING
3123 (
3124 FND_LOG.LEVEL_PROCEDURE,
3125 'FPA.SQL.FPA_PROCESS_PVT.Refresh_Projects.Source',
3126 'Entering FPA_PROCESS_PVT.Refresh_Projects'
3127 );
3128 END IF;
3129
3130 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3131 FND_LOG.STRING
3132 (
3133 FND_LOG.LEVEL_PROCEDURE,
3134 'FPA.SQL.FPA_PROCESS_PVT.Refresh_Projects.Source',
3135 'Calling FPA_PROCESS_PVT.load_project_details_aw'
3136 );
3137 END IF;
3138 -- Attaching and detaching A/w is not required for details load. This is handled internally within the project_details_aw API.
3139 FPA_PROCESS_PVT.load_project_details_aw
3140 (
3141 p_api_version => 1.0,
3142 p_init_msg_list => 'F',
3143 p_commit => FND_API.G_TRUE,
3144 p_type => 'REFRESH',
3145 p_scenario_id => p_scenario_id,
3146 p_projects => p_proj_id_str,
3147 x_return_status => x_return_status,
3148 x_msg_count => x_msg_data,
3149 x_msg_data => x_msg_count
3150 );
3151
3152 Fpa_Utilities_Pvt.attach_AW
3153 (
3154 p_api_version => 1.0,
3155 p_attach_mode => 'rw',
3156 x_return_status => x_return_status,
3157 x_msg_count => x_msg_count,
3158 x_msg_data => x_msg_data
3159 );
3160
3161 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3162 FND_LOG.String
3163 (
3164 FND_LOG.LEVEL_PROCEDURE,
3165 'fpa.sql.FPA_Process_Pvt.Refresh_Projects.Source',
3166 'Calling fpa_scenario_pvt.calc_scenario_data for updating Scenario project data..'
3167 );
3168 END IF;
3169
3170 for i in projectIdTbl.first .. projectIdTbl.last
3171 loop
3172
3173 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3174 FND_LOG.STRING
3175 (
3176 FND_LOG.LEVEL_PROCEDURE,
3177 'FPA.SQL.FPA_PROCESS_PVT.Refresh_Projects.Source',
3178 'Calling fpa_scenario_pvt.calc_sceario_data in PROJ Mode for projectId= '||projectIdTbl(i)
3179 );
3180 END IF;
3181
3182 l_data_to_calc := 'PROJ';
3183 fpa_scenario_pvt.calc_scenario_data
3184 (
3185 p_api_version => 1.0,
3186 p_scenario_id => p_scenario_id,
3187 p_project_id => projectIdTbl(i),
3188 p_class_code_id => null,
3189 p_data_to_calc => l_data_to_calc,
3190 x_return_status => x_return_status,
3191 x_msg_count => x_msg_count,
3192 x_msg_data => x_msg_data
3193 );
3194
3195 -- dbms_aw.execute('LMT project_d TO ' || projectIdTbl(i));
3196
3197 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3198 FND_LOG.String
3199 (
3200 FND_LOG.LEVEL_PROCEDURE,
3201 'fpa.sql.FPA_Process_Pvt.Refresh_Projects.Source',
3202 'Completed AW Updates for project_set_project_m'
3203 );
3204 END IF;
3205
3206 end loop;
3207
3208 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3209 FND_LOG.STRING
3210 (
3211 FND_LOG.LEVEL_PROCEDURE,
3212 'FPA.SQL.FPA_PROCESS_PVT.Refresh_Projects.Source',
3213 'Completed scenario Project rollup calculations. proceeding to classcode'
3214 );
3215 END IF;
3216
3217 -- calculate classcode level data for all classcodes in the scenario
3218 -- Call copy_sce_proj_data in PJT mode to calculate total cost, benefit,
3219 -- and other metrics at all levels for the Initial Scenario
3220 l_data_to_calc := 'CLASS';
3221 fpa_scenario_pvt.calc_scenario_data
3222 (
3223 p_api_version => 1.0,
3224 p_scenario_id => p_scenario_id,
3225 p_project_id => null,
3226 p_class_code_id => null,
3227 p_data_to_calc => l_data_to_calc,
3228 x_return_status => x_return_status,
3229 x_msg_count => x_msg_count,
3230 x_msg_data => x_msg_data
3231 );
3232
3233 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3234 FND_LOG.STRING
3235 (
3236 FND_LOG.LEVEL_PROCEDURE,
3237 'FPA.SQL.FPA_PROCESS_PVT.Refresh_Projects.Source',
3238 'Completed Classcode rollup calculations. proceeding to scenario'
3239 );
3240 END IF;
3241 l_data_to_calc := 'SCEN';
3242 fpa_scenario_pvt.calc_scenario_data
3243 (
3244 p_api_version => 1.0,
3245 p_scenario_id => p_scenario_id,
3246 p_project_id => null,
3247 p_class_code_id => null,
3248 p_data_to_calc => l_data_to_calc,
3249 x_return_status => x_return_status,
3250 x_msg_count => x_msg_count,
3251 x_msg_data => x_msg_data
3252 );
3253
3254 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3255 FND_LOG.STRING
3256 (
3257 FND_LOG.LEVEL_PROCEDURE,
3258 'fpa.sql.fpa_project_pvt.Refresh_project',
3259 'Calling fpa.sql.FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations'
3260 );
3261 END IF;
3262
3263 FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations(
3264 p_api_version => 1.0,
3265 p_init_msg_list => 'F',
3266 p_validation_set => 'FPA_VALIDATION_TYPES',
3267 p_header_object_id => p_scenario_id,
3268 p_header_object_type => 'SCENARIO',
3269 p_line_projects_tbl => projectIdTbl,
3270 p_type => 'UPDATE',
3271 x_return_status => x_return_status,
3272 x_msg_count => x_msg_count,
3273 x_msg_data => x_msg_data);
3274
3275
3276 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3277 FND_LOG.STRING
3278 (
3279 FND_LOG.LEVEL_PROCEDURE,
3280 'fpa.sql.fpa_project_pvt.Refresh_project',
3281 'End fpa.sql.FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations'
3282 );
3283 END IF;
3284
3285
3286 -- Update and commit our changes
3287
3288 IF (p_commit = FND_API.G_TRUE) THEN
3289 dbms_aw.execute('UPDATE');
3290 COMMIT;
3291 END IF;
3292
3293 -- Detach AW Workspace
3294 Fpa_Utilities_Pvt.detach_AW
3295 (
3296 p_api_version => l_api_version,
3297 x_return_status => x_return_status,
3298 x_msg_count => x_msg_count,
3299 x_msg_data => x_msg_data
3300 );
3301
3302 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3303 FND_LOG.STRING
3304 (
3305 FND_LOG.LEVEL_PROCEDURE,
3306 'FPA.SQL.FPA_PROCESS_PVT.Refresh_Projects.Begin Validate',
3307 'Ending FPA_PROCESS_PVT.Refresh_Projects'
3308 );
3309 END IF;
3310
3311
3312
3313 Fpa_Validation_Pvt.Validate (
3314 p_api_version => 1.0,
3315 p_init_msg_list => 'F',
3316 p_validation_set => 'FPA_VALIDATION_TYPES',
3317 p_header_object_id => p_scenario_id,
3318 p_header_object_type => 'SCENARIO',
3319 p_line_projects_tbl => projectIdTbl,
3320 x_return_status => x_return_status,
3321 x_msg_count => x_msg_count,
3322 x_msg_data => x_msg_data);
3323
3324
3325 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3326 FND_LOG.STRING
3327 (
3328 FND_LOG.LEVEL_PROCEDURE,
3329 'FPA.SQL.FPA_PROCESS_PVT.Refresh_Projects.end',
3330 'Ending FPA_PROCESS_PVT.Refresh_Projects'
3331 );
3332 END IF;
3333
3334 EXCEPTION
3335 WHEN OTHERS THEN
3336 ROLLBACK;
3337
3338 Fpa_Utilities_Pvt.detach_AW
3339 (
3340 p_api_version => l_api_version,
3341 x_return_status => x_return_status,
3342 x_msg_count => x_msg_count,
3343 x_msg_data => x_msg_data
3344 );
3345
3346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3347
3348 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3349 FND_LOG.STRING
3350 (
3351 FND_LOG.LEVEL_ERROR,
3352 'FPA.SQL.FPA_PROCESS_PVT.Refresh_Projects',
3353 SQLERRM
3354 );
3355 END IF;
3356
3357 FND_MSG_PUB.count_and_get
3358 (
3359 p_count => x_msg_count,
3360 p_data => x_msg_data
3361 );
3362 RAISE;
3363 END Refresh_Projects;
3364
3365
3366
3367 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
3368
3369 PROCEDURE Remove_Projects
3370 ( p_api_version IN NUMBER,
3371 p_commit IN VARCHAR2,
3372 p_scenario_id IN NUMBER,
3373 p_proj_id IN NUMBER,
3374 x_return_status OUT NOCOPY VARCHAR2,
3375 x_msg_data OUT NOCOPY VARCHAR2,
3376 x_msg_count OUT NOCOPY NUMBER
3377 )
3378
3379 IS
3380
3381 cursor c_class_code is
3382 select class_code from fpa_aw_projs_v where project = p_proj_id;
3383
3384 l_api_version NUMBER;
3385 l_data_to_calc varchar2(30);
3386 l_class_code_id NUMBER;
3387
3388 BEGIN
3389
3390 FND_MSG_PUB.Initialize;
3391
3392 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3393 FND_LOG.STRING
3394 (
3395 FND_LOG.LEVEL_PROCEDURE,
3396 'FPA.SQL.FPA_PROCESS_PVT.Remove_Projects.begin',
3397 'Entering FPA_PROCESS_PVT.Remove_Projects'
3398 );
3399 END IF;
3400
3401 l_api_version := 1;
3402
3403 -- Get Classcode for the project_id passed as parameter.
3404 open c_class_code;
3405 fetch c_class_code into l_class_code_id;
3406 close c_class_code;
3407
3408 Fpa_Utilities_Pvt.attach_AW
3409 (
3410 p_api_version => 1.0,
3411 p_attach_mode => 'rw',
3412 x_return_status => x_return_status,
3413 x_msg_count => x_msg_count,
3414 x_msg_data => x_msg_data
3415 );
3416
3417 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3418 FND_LOG.STRING
3419 (
3420 FND_LOG.LEVEL_PROCEDURE,
3421 'FPA.SQL.FPA_PROCESS_PVT.Remove_Projects',
3422 'calling FPA_SCENARIO_PVT.remove_project_from_scenario'
3423 );
3424 END IF;
3425
3426
3427 -- API to set scenario_project_m all other project measures to na
3428 FPA_SCENARIO_PVT.remove_project_from_scenario
3429 (
3430 p_api_version => l_api_version,
3431 p_commit => FND_API.G_FALSE,
3432 p_scenario_id => p_scenario_id,
3433 p_project_id => p_proj_id,
3434 x_return_status => x_return_status,
3435 x_msg_data => x_msg_data,
3436 x_msg_count => x_msg_count
3437 );
3438
3439
3440 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3441 FND_LOG.STRING
3442 (
3443 FND_LOG.LEVEL_PROCEDURE,
3444 'fpa.sql.fpa_process_pvt.remove_project',
3445 'calling fpa_scorecards_pvt.handle_comments '||p_scenario_id||','||p_proj_id
3446 );
3447 END IF;
3448
3449
3450 FPA_SCORECARDS_PVT.Handle_Comments(
3451 p_api_version => p_api_version,
3452 p_init_msg_list => FND_API.G_TRUE,
3453 p_scenario_id => p_scenario_id,
3454 p_type => null,
3455 p_source_scenario_id => null,
3456 p_delete_project_id => p_proj_id,
3457 x_return_status => x_return_status,
3458 x_msg_count => x_msg_count,
3459 x_msg_data => x_msg_data);
3460
3461
3462 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3463 FND_LOG.STRING
3464 (
3465 FND_LOG.LEVEL_PROCEDURE,
3466 'FPA.SQL.FPA_PROCESS_PVT.Remove_Projects',
3467 'calling FPA_SCENARIO_PVT.calc_scenario_data in class mode'
3468 );
3469 END IF;
3470
3471
3472
3473 -- Recalculate metrics at rollup level.
3474 -- For classcode, calculate rollup at the classcode associated with the project being removed.
3475 -- Metrics for other classcodes are not affected.
3476 l_data_to_calc := 'CLASS';
3477 fpa_scenario_pvt.calc_scenario_data
3478 (
3479 p_api_version => 1.0,
3480 p_scenario_id => p_scenario_id,
3481 p_project_id => null,
3482 p_class_code_id => l_class_code_id,
3483 p_data_to_calc => l_data_to_calc,
3484 x_return_status => x_return_status,
3485 x_msg_count => x_msg_count,
3486 x_msg_data => x_msg_data
3487 );
3488
3489 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3490 FND_LOG.STRING
3491 (
3492 FND_LOG.LEVEL_PROCEDURE,
3493 'FPA.SQL.FPA_PROCESS_PVT.Remove_Projects',
3494 'calling FPA_SCENARIO_PVT.calc_scenario_data in Scenario mode'
3495 );
3496 END IF;
3497
3498 l_data_to_calc := 'SCEN';
3499 fpa_scenario_pvt.calc_scenario_data
3500 (
3501 p_api_version => 1.0,
3502 p_scenario_id => p_scenario_id,
3503 p_project_id => null,
3504 p_class_code_id => null,
3505 p_data_to_calc => l_data_to_calc,
3506 x_return_status => x_return_status,
3507 x_msg_count => x_msg_count,
3508 x_msg_data => x_msg_data
3509 );
3510
3511 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3512 FND_LOG.STRING
3513 (
3514 FND_LOG.LEVEL_PROCEDURE,
3515 'FPA.SQL.FPA_PROCESS_PVT.Remove_Projects',
3516 'Completed all API calls. Issue UPDATE to AW'
3517 );
3518 END IF;
3519
3520 -- Update and commit our changes
3521 IF (p_commit = FND_API.G_TRUE) THEN
3522 dbms_aw.execute('UPDATE');
3523 COMMIT;
3524 END IF;
3525
3526 -- Detach AW Workspace
3527 Fpa_Utilities_Pvt.detach_AW
3528 (
3529 p_api_version => l_api_version,
3530 x_return_status => x_return_status,
3531 x_msg_count => x_msg_count,
3532 x_msg_data => x_msg_data
3533 );
3534
3535 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3536 FND_LOG.STRING
3537 (
3538 FND_LOG.LEVEL_PROCEDURE,
3539 'FPA.SQL.FPA_PROCESS_PVT.Remove_Projects.end',
3540 'Ending FPA_PROCESS_PVT.Remove_Projects'
3541 );
3542 END IF;
3543
3544 EXCEPTION
3545 WHEN OTHERS THEN
3546 ROLLBACK;
3547
3548 -- Detach AW Workspace
3549 Fpa_Utilities_Pvt.detach_AW
3550 (
3551 p_api_version => l_api_version,
3552 x_return_status => x_return_status,
3553 x_msg_count => x_msg_count,
3554 x_msg_data => x_msg_data
3555 );
3556
3557 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3558
3559 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3560 FND_LOG.STRING
3561 (
3562 FND_LOG.LEVEL_ERROR,
3563 'FPA.SQL.FPA_PROCESS_PVT.Remove_Projects',
3564 SQLERRM
3565 );
3566 END IF;
3567
3568 FND_MSG_PUB.count_and_get
3569 (
3570 p_count => x_msg_count,
3571 p_data => x_msg_data
3572 );
3573 RAISE;
3574 END Remove_Projects;
3575
3576
3577 /************************************************************************************/
3578 /************************************************************************************/
3579
3580 PROCEDURE update_strategicobj_weight
3581 ( p_api_version IN NUMBER
3582 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3583 ,p_strategic_weights_string IN varchar2
3584 ,x_return_status OUT NOCOPY varchar2
3585 ,x_msg_count OUT NOCOPY number
3586 ,x_msg_data OUT NOCOPY varchar2
3587 )
3588 AS
3589 investment_rec fpa_investment_criteria_pvt.investment_rec_type;
3590
3591 l_api_version CONSTANT NUMBER := 1.0;
3592
3593 BEGIN
3594
3595 -- clear all previous messages.
3596 FND_MSG_PUB.Initialize;
3597
3598 -- Attach the AW space read write.
3599 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3600 fnd_log.string
3601 (
3602 FND_LOG.LEVEL_STATEMENT,
3603 'fpa.sql.fpa_process_pvt.update_strategicobj_weight',
3604 'Attaching OLAP workspace: '
3605 );
3606 END IF;
3607
3608 Fpa_Utilities_Pvt.attach_AW
3609 (
3610 p_api_version => 1.0,
3611 p_attach_mode => 'rw',
3612 x_return_status => x_return_status,
3613 x_msg_count => x_msg_count,
3614 x_msg_data => x_msg_data
3615 );
3616
3617 -- set the values in the record type equal to the ones passed.
3618 investment_rec.strategic_scores_string := p_strategic_weights_string;
3619
3620 fpa_investment_criteria_pvt.update_strategicobj_weight_aw
3621 (
3622 p_investment_rec_type => investment_rec,
3623 x_return_status => x_return_status,
3624 x_msg_count => x_msg_count,
3625 x_msg_data => x_msg_data
3626 );
3627
3628 -- Finally, detach the workspace
3629 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3630 fnd_log.string
3631 (
3632 FND_LOG.LEVEL_STATEMENT,
3633 'fpa.sql.fpa_process_pvt.create_portfolio',
3634 'Detaching OLAP workspace: '
3635 );
3636 END IF;
3637
3638 -- Update and commit our changes
3639 IF (p_commit = FND_API.G_TRUE) THEN
3640 dbms_aw.execute('UPDATE');
3641 COMMIT;
3642 END IF;
3643
3644 -- Detach AW Workspace
3645 Fpa_Utilities_Pvt.detach_AW
3646 (
3647 p_api_version => 1.0,
3648 x_return_status => x_return_status,
3649 x_msg_count => x_msg_count,
3650 x_msg_data => x_msg_data
3651 );
3652
3653 EXCEPTION
3654 WHEN OTHERS THEN
3655 ROLLBACK;
3656 -- Detach AW Workspace
3657 Fpa_Utilities_Pvt.detach_AW
3658 (
3659 p_api_version => 1.0,
3660 x_return_status => x_return_status,
3661 x_msg_count => x_msg_count,
3662 x_msg_data => x_msg_data
3663 );
3664
3665 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3666
3667 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3668 FND_LOG.String
3669 (
3670 FND_LOG.LEVEL_ERROR,
3671 'fpa.sql.FPA_Process_Pvt.Update_Pc',
3672 SQLERRM
3673 );
3674 END IF;
3675
3676 FND_MSG_PUB.count_and_get
3677 (
3678 p_count => x_msg_count,
3679 p_data => x_msg_data
3680 );
3681 RAISE;
3682 END;
3683
3684 /************************************************************************************/
3685 /************************************************************************************/
3686
3687 PROCEDURE update_strategicobj
3688 ( p_api_version IN NUMBER,
3689 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3690 p_strategic_obj_id IN NUMBER,
3691 p_strategic_obj_name IN VARCHAR2,
3692 p_strategic_obj_desc IN VARCHAR2,
3693 x_return_status OUT NOCOPY VARCHAR2,
3694 x_msg_count OUT NOCOPY NUMBER,
3695 x_msg_data OUT NOCOPY VARCHAR2
3696 )
3697 AS
3698 l_investment_rec fpa_investment_criteria_pvt.investment_rec_type;
3699
3700 l_api_version CONSTANT NUMBER := 1.0;
3701
3702 BEGIN
3703
3704 FND_MSG_PUB.Initialize;
3705
3706 l_investment_rec.strategic_obj_shortname := p_strategic_obj_id;
3707 l_investment_rec.strategic_obj_name := p_strategic_obj_name;
3708 l_investment_rec.strategic_obj_desc := p_strategic_obj_desc;
3709
3710 -- Attach the AW space read write.
3711 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3712 fnd_log.string ( FND_LOG.LEVEL_STATEMENT,
3713 'fpa.sql.fpa_resourcetype_pvt.create_resourcetype',
3714 'Attaching OLAP workspace: ');
3715 END IF;
3716
3717 Fpa_Utilities_Pvt.attach_AW( p_api_version => 1.0,
3718 p_attach_mode => 'rw',
3719 x_return_status => x_return_status,
3720 x_msg_count => x_msg_count,
3721 x_msg_data => x_msg_data);
3722
3723
3724 FPA_Investment_Criteria_PVT.update_strategicobj( p_commit => p_commit
3725 ,p_investment_rec_type => l_investment_rec
3726 ,x_return_status => x_return_status
3727 ,x_msg_count => x_msg_count
3728 ,x_msg_data => x_msg_data);
3729
3730 -- Update and commit our changes
3731 IF (p_commit = FND_API.G_TRUE) THEN
3732 dbms_aw.execute('UPDATE');
3733 COMMIT;
3734 END IF;
3735
3736 -- Detach AW Workspace
3737 Fpa_Utilities_Pvt.detach_AW(p_api_version => 1.0,
3738 x_return_status => x_return_status,
3739 x_msg_count => x_msg_count,
3740 x_msg_data => x_msg_data);
3741
3742 EXCEPTION
3743 WHEN OTHERS THEN
3744 ROLLBACK;
3745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3746 FND_MSG_PUB.Count_And_Get
3747 (
3748 p_count => x_msg_count,
3749 p_data => x_msg_data
3750 );
3751 RAISE;
3752
3753 END update_strategicobj;
3754
3755 PROCEDURE create_strategicobj
3756 ( p_api_version IN NUMBER,
3757 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3758 p_strategic_obj_name IN VARCHAR2,
3759 p_strategic_obj_desc IN VARCHAR2,
3760 p_strategic_obj_parent IN number,
3761 p_strategic_obj_level IN varchar2,
3762 x_return_status OUT NOCOPY varchar2,
3763 x_msg_count OUT NOCOPY number,
3764 x_msg_data OUT NOCOPY varchar2
3765 )
3766 AS
3767 l_api_version CONSTANT NUMBER := 1.0;
3768 l_investment_rec fpa_investment_criteria_pvt.investment_rec_type;
3769 l_stategic_obj_id varchar2(30);
3770 l_seq_nextval number;
3771
3772 BEGIN
3773
3774 -- clear all previous messages.
3775 FND_MSG_PUB.Initialize;
3776
3777
3778 -- investment_rec.strategic_obj_shortname := 'STROBJ' || l_seq_nextval;
3779 l_investment_rec.strategic_obj_name := p_strategic_obj_name;
3780 l_investment_rec.strategic_obj_desc := p_strategic_obj_desc;
3781 l_investment_rec.strategic_obj_parent := p_strategic_obj_parent;
3782 l_investment_rec.strategic_obj_level := p_strategic_obj_level;
3783
3784 -- Attach the AW space read write.
3785 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3786 fnd_log.string
3787 (
3788 FND_LOG.LEVEL_STATEMENT,
3789 'fpa.sql.fpa_resourcetype_pvt.create_resourcetype',
3790 'Attaching OLAP workspace: '
3791 );
3792 END IF;
3793
3794 Fpa_Utilities_Pvt.attach_AW
3795 (
3796 p_api_version => 1.0,
3797 p_attach_mode => 'rw',
3798 x_return_status => x_return_status,
3799 x_msg_count => x_msg_count,
3800 x_msg_data => x_msg_data
3801 );
3802
3803
3804 -- Call procedure to call Investment Criteria in AW
3805 fpa_investment_criteria_pvt.create_strategicobj_aw
3806 (
3807 p_commit => FND_API.G_TRUE,
3808 p_investment_rec_type => l_investment_rec,
3809 p_seeding => 'N',
3810 x_strategic_obj_id => l_stategic_obj_id,
3811 x_return_status => x_return_status,
3812 x_msg_count => x_msg_count,
3813 x_msg_data => x_msg_data
3814 );
3815
3816 -- Call AW procedure to update the strategic_obj_status_r for the user
3817 -- created strategic objectives. This will tel the UI what objectives
3818 -- may be deleted by the user.
3819 l_investment_rec.strategic_obj_status := 'DODELETE';
3820 l_investment_rec.strategic_obj_shortname := l_stategic_obj_id;
3821 FPA_Investment_Criteria_PVT.Update_StrategicObj_Status_AW(
3822 p_commit => FND_API.G_TRUE,
3823 p_investment_rec_type => l_investment_rec,
3824 x_return_status => x_return_status,
3825 x_msg_count => x_msg_count,
3826 x_msg_data => x_msg_data
3827 );
3828
3829 FPA_Investment_Criteria_PVT.update_strategicobj_level_aw( p_commit
3830 ,l_investment_rec
3831 ,x_return_status
3832 ,x_msg_count
3833 ,x_msg_data);
3834
3835 -- Update and commit our changes
3836 IF (p_commit = FND_API.G_TRUE) THEN
3837 dbms_aw.execute('UPDATE');
3838 COMMIT;
3839 END IF;
3840
3841 -- Detach AW Workspace
3842 Fpa_Utilities_Pvt.detach_AW
3843 (
3844 p_api_version => 1.0,
3845 x_return_status => x_return_status,
3846 x_msg_count => x_msg_count,
3847 x_msg_data => x_msg_data
3848 );
3849
3850 EXCEPTION
3851 WHEN OTHERS THEN
3852 -- Detach AW Workspace
3853 Fpa_Utilities_Pvt.detach_AW
3854 (
3855 p_api_version => 1.0,
3856 x_return_status => x_return_status,
3857 x_msg_count => x_msg_count,
3858 x_msg_data => x_msg_data
3859 );
3860 ROLLBACK;
3861 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3862 FND_MSG_PUB.Count_And_Get
3863 (
3864 p_count => x_msg_count,
3865 p_data => x_msg_data
3866 );
3867 RAISE;
3868
3869 END create_strategicobj;
3870
3871 PROCEDURE delete_strategicobj
3872 ( p_api_version IN NUMBER,
3873 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3874 p_strategic_obj_shortname IN VARCHAR2,
3875 x_return_status OUT NOCOPY VARCHAR2,
3876 x_msg_count OUT NOCOPY NUMBER,
3877 x_msg_data OUT NOCOPY VARCHAR2
3878 )
3879 AS
3880 l_investment_rec fpa_investment_criteria_pvt.investment_rec_type;
3881 l_api_version CONSTANT NUMBER := 1.0;
3882
3883 BEGIN
3884 FND_MSG_PUB.Initialize;
3885
3886 l_investment_rec.strategic_obj_shortname := p_strategic_obj_shortname;
3887
3888 -- Attach the AW space read write.
3889 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3890 fnd_log.string ( FND_LOG.LEVEL_STATEMENT,
3891 'fpa.sql.fpa_process_pvt.delete_strategicobj',
3892 'Attaching OLAP workspace: ');
3893 END IF;
3894
3895 Fpa_Utilities_Pvt.attach_AW( p_api_version => 1.0,
3896 p_attach_mode => 'rw',
3897 x_return_status => x_return_status,
3898 x_msg_count => x_msg_count,
3899 x_msg_data => x_msg_data);
3900
3901 fpa_investment_criteria_pvt.delete_strategicobj_aw
3902 (
3903 p_api_version => p_api_version,
3904 p_investment_rec_type => l_investment_rec,
3905 x_return_status => x_return_status,
3906 x_msg_count => x_msg_count,
3907 x_msg_data => x_msg_data
3908 );
3909
3910 -- Update and commit our changes
3911 IF (p_commit = FND_API.G_TRUE) THEN
3912 dbms_aw.execute('UPDATE');
3913 COMMIT;
3914 END IF;
3915
3916 -- Detach AW Workspace
3917 Fpa_Utilities_Pvt.detach_AW(p_api_version => 1.0,
3918 x_return_status => x_return_status,
3919 x_msg_count => x_msg_count,
3920 x_msg_data => x_msg_data);
3921
3922 EXCEPTION
3923 WHEN OTHERS THEN
3924 ROLLBACK;
3925 -- Detach AW Workspace
3926 Fpa_Utilities_Pvt.detach_AW
3927 (
3928 p_api_version => 1.0,
3929 x_return_status => x_return_status,
3930 x_msg_count => x_msg_count,
3931 x_msg_data => x_msg_data
3932 );
3933
3934 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3935
3936 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3937 FND_LOG.String
3938 (
3939 FND_LOG.LEVEL_ERROR,
3940 'fpa.sql.FPA_Process_Pvt.Update_Pc',
3941 SQLERRM
3942 );
3943 END IF;
3944
3945 FND_MSG_PUB.count_and_get
3946 (
3947 p_count => x_msg_count,
3948 p_data => x_msg_data
3949 );
3950 RAISE;
3951
3952 END delete_strategicobj;
3953
3954 /*******************************************************************************************
3955 *******************************************************************************************/
3956
3957 -- This procedures creates a new scenario. Scenarios are almost always created by copying
3958 -- from a source scenario.
3959 -- This procedure expects the source scenario id, the planning cycle id for the source scenario
3960 -- the new scenario name, and the new scenario description.
3961 -- If the scenario source id is null then we are creating the initial scenario.
3962
3963 PROCEDURE create_scenario
3964 (
3965 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3966 p_api_version IN NUMBER,
3967 p_scenario_id_source IN NUMBER,
3968 p_pc_id IN NUMBER,
3969 p_scenario_name IN VARCHAR2,
3970 p_scenario_desc IN VARCHAR2,
3971 p_copy_proposed_proj IN VARCHAR2,
3972 p_sce_disc_rate IN VARCHAR2,
3973 p_sce_funds_avail IN VARCHAR2,
3974 x_scenario_id OUT NOCOPY VARCHAR2,
3975 x_return_status OUT NOCOPY VARCHAR2,
3976 x_msg_count OUT NOCOPY NUMBER,
3977 x_msg_data OUT NOCOPY VARCHAR2
3978 ) is
3979
3980 l_api_version NUMBER := 1.0;
3981
3982 l_sce_name_count NUMBER;
3983
3984 l_data_to_calc VARCHAR2(10); -- variable used for
3985 -- fpa_scenario_pvt.calc_scenario_data
3986
3987 l_projects_tbl FPA_VALIDATION_PVT.PROJECT_ID_TBL_TYPE;
3988
3989 begin
3990
3991 -- clear all previous messages.
3992 FND_MSG_PUB.Initialize;
3993
3994
3995 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3996 FND_LOG.String
3997 (
3998 FND_LOG.LEVEL_PROCEDURE,
3999 'fpa.sql.FPA_Process_Pvt.create_scenario.begin',
4000 'Entering FPA_Process_Pvt.create_scenario'
4001 );
4002 END IF;
4003
4004 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4005 FND_LOG.String
4006 (
4007 FND_LOG.LEVEL_PROCEDURE,
4008 'fpa.sql.FPA_Process_Pvt.create_scenario',
4009 'Checking Scenario name does not exist for this planning cycle.'
4010 );
4011 END IF;
4012
4013 -- Check name does not exist for this planning cycle
4014 l_sce_name_count := fpa_scenario_pvt.check_scenario_name
4015 (
4016 p_scenario_name => p_scenario_name,
4017 p_pc_id => p_pc_id,
4018 x_return_status => x_return_status,
4019 x_msg_count => x_msg_count,
4020 x_msg_data => x_msg_data
4021 );
4022
4023 -- If Duplicate Scenario Name exists, then raise error and halt all execution
4024 IF l_sce_name_count > 0 THEN
4025 FND_MESSAGE.SET_NAME('FPA','FPA_DUPLICATE_SCE_NAME');
4026 FND_MESSAGE.SET_TOKEN('SCE_NAME', p_scenario_name);
4027 FND_MSG_PUB.ADD;
4028 RAISE FND_API.G_EXC_ERROR;
4029 END IF;
4030
4031 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4032 FND_LOG.String
4033 (
4034 FND_LOG.LEVEL_PROCEDURE,
4035 'fpa.sql.FPA_Process_Pvt.create_scenario',
4036 'Attaching AW space.'
4037 );
4038 END IF;
4039
4040 -- Attach AW Workspace
4041 Fpa_Utilities_Pvt.attach_AW
4042 (
4043 p_api_version => 1.0,
4044 p_attach_mode => 'rw',
4045 x_return_status => x_return_status,
4046 x_msg_count => x_msg_count,
4047 x_msg_data => x_msg_data
4048 );
4049
4050 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4051 FND_LOG.String
4052 (
4053 FND_LOG.LEVEL_PROCEDURE,
4054 'fpa.sql.FPA_Process_Pvt.create_scenario',
4055 'Calling procedure fpa_scenario_pvt.create_scenario.'
4056 );
4057 END IF;
4058
4059 -- Call procedure to crete scenario
4060 fpa_scenario_pvt.create_scenario
4061 (
4062 p_api_version => 1.0,
4063 p_scenario_name => p_scenario_name,
4064 p_scenario_desc => p_scenario_desc,
4065 p_pc_id => p_pc_id,
4066 x_scenario_id => x_scenario_id,
4067 x_return_status => x_return_status,
4068 x_msg_count => x_msg_count,
4069 x_msg_data => x_msg_data
4070 );
4071
4072 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4073 FND_LOG.String
4074 (
4075 FND_LOG.LEVEL_PROCEDURE,
4076 'fpa.sql.FPA_Process_Pvt.create_scenario',
4077 'Calling fpa_scenario_pvt.copy_scenario_data.'
4078 );
4079 END IF;
4080
4081 fpa_scenario_pvt.copy_scenario_data
4082 (
4083 p_api_version => 1.0,
4084 p_scenario_id_source => p_scenario_id_source,
4085 p_scenario_id_target => x_scenario_id,
4086 p_copy_proposed_proj => p_copy_proposed_proj,
4087 x_return_status => x_return_status,
4088 x_msg_count => x_msg_count,
4089 x_msg_data => x_msg_data
4090 );
4091
4092 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4093 FND_LOG.String
4094 (
4095 FND_LOG.LEVEL_PROCEDURE,
4096 'fpa.sql.FPA_Process_Pvt.create_scenario',
4097 'Calling fpa_scenario_pvt.update_scenario_disc_rate.'
4098 );
4099 END IF;
4100
4101 fpa_scenario_pvt.update_scenario_disc_rate
4102 (
4103 p_api_version => 1.0,
4104 p_scenario_id => x_scenario_id,
4105 p_discount_rate => p_sce_disc_rate,
4106 x_return_status => x_return_status,
4107 x_msg_count => x_msg_count,
4108 x_msg_data => x_msg_data
4109 );
4110
4111 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4112 FND_LOG.String
4113 (
4114 FND_LOG.LEVEL_PROCEDURE,
4115 'fpa.sql.FPA_Process_Pvt.create_scenario',
4116 'Calling fpa_scenario_pvt.update_scenario_funds_avail.'
4117 );
4118 END IF;
4119
4120 fpa_scenario_pvt.update_scenario_funds_avail
4121 (
4122 p_api_version => 1.0,
4123 p_scenario_id => x_scenario_id,
4124 p_scenario_funds => p_sce_funds_avail,
4125 x_return_status => x_return_status,
4126 x_msg_count => x_msg_count,
4127 x_msg_data => x_msg_data
4128 );
4129
4130 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4131 FND_LOG.String
4132 (
4133 FND_LOG.LEVEL_PROCEDURE,
4134 'fpa.sql.FPA_Process_Pvt.create_scenario',
4135 'Calling fpa_scenario_pvt.calc_scenario_data for Project Financial Metrics.'
4136 );
4137 END IF;
4138
4139 l_data_to_calc := 'PROJFIN';
4140
4141 fpa_scenario_pvt.calc_scenario_data
4142 (
4143 p_api_version => 1.0,
4144 p_scenario_id => x_scenario_id,
4145 p_project_id => null,
4146 p_class_code_id => null,
4147 p_data_to_calc => l_data_to_calc,
4148 x_return_status => x_return_status,
4149 x_msg_count => x_msg_count,
4150 x_msg_data => x_msg_data
4151 );
4152
4153 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4154 FND_LOG.String
4155 (
4156 FND_LOG.LEVEL_PROCEDURE,
4157 'fpa.sql.FPA_Process_Pvt.create_scenario',
4158 'Calling fpa_scenario_pvt.calc_scenario_data for Class Codes..'
4159 );
4160 END IF;
4161
4162 l_data_to_calc := 'CLASS';
4163
4164 fpa_scenario_pvt.calc_scenario_data
4165 (
4166 p_api_version => 1.0,
4167 p_scenario_id => x_scenario_id,
4168 p_project_id => null,
4169 p_class_code_id => null,
4170 p_data_to_calc => l_data_to_calc,
4171 x_return_status => x_return_status,
4172 x_msg_count => x_msg_count,
4173 x_msg_data => x_msg_data
4174 );
4175
4176 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4177 FND_LOG.String
4178 (
4179 FND_LOG.LEVEL_PROCEDURE,
4180 'fpa.sql.FPA_Process_Pvt.create_scenario',
4181 'Calling fpa_scenario_pvt.calc_scenario_data for Scenario.'
4182 );
4183 END IF;
4184
4185 l_data_to_calc := 'SCEN';
4186
4187 fpa_scenario_pvt.calc_scenario_data
4188 (
4189 p_api_version => 1.0,
4190 p_scenario_id => x_scenario_id,
4191 p_project_id => null,
4192 p_class_code_id => null,
4193 p_data_to_calc => l_data_to_calc,
4194 x_return_status => x_return_status,
4195 x_msg_count => x_msg_count,
4196 x_msg_data => x_msg_data
4197 );
4198
4199 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4200 FND_LOG.String
4201 (
4202 FND_LOG.LEVEL_PROCEDURE,
4203 'fpa.sql.FPA_Process_Pvt.create_scenario',
4204 'Calling fpa_scorecards_pvt.handle_comments.'
4205 );
4206 END IF;
4207
4208
4209 FPA_SCORECARDS_PVT.Handle_Comments(
4210 p_api_version => 1.0,
4211 p_init_msg_list => FND_API.G_TRUE,
4212 p_scenario_id => x_scenario_id,
4213 p_type => 'PJP',
4214 p_source_scenario_id => p_scenario_id_source,
4215 p_delete_project_id => null,
4216 x_return_status => x_return_status,
4217 x_msg_count => x_msg_count,
4218 x_msg_data => x_msg_data);
4219
4220
4221 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4222 FND_LOG.String
4223 (
4224 FND_LOG.LEVEL_PROCEDURE,
4225 'fpa.sql.FPA_Process_Pvt.create_scenario',
4226 'Calling Fpa_Validation_Process_Pvt.Validate_Budget_Versions.'
4227 );
4228 END IF;
4229
4230 FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations(
4231 p_api_version => 1.0,
4232 p_init_msg_list => 'F',
4233 p_validation_set => 'FPA_VALIDATION_TYPES',
4234 p_header_object_id => x_scenario_id,
4235 p_header_object_type => 'SCENARIO',
4236 x_return_status => x_return_status,
4237 x_msg_count => x_msg_count,
4238 x_msg_data => x_msg_data);
4239
4240 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4241 FND_LOG.STRING
4242 (
4243 FND_LOG.LEVEL_PROCEDURE,
4244 'fpa.sql.FPA_Process_Pvt.create_scenario',
4245 'End Fpa_Validation_Process_Pvt.Validate_Budget_Versions.end'
4246 );
4247 END IF;
4248
4249 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR
4250 and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4251 FND_LOG.STRING
4252 (
4253 FND_LOG.LEVEL_PROCEDURE,
4254 'fpa.sql.Fpa_Validation_Process_Pvt.Validate_Budget_Versions',
4255 'unexpected error - create_scenario.Validate_Budget_Versions'
4256 );
4257 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR
4258 and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4259 FND_LOG.STRING
4260 (
4261 FND_LOG.LEVEL_PROCEDURE,
4262 'fpa.sql.Fpa_Validation_Process_Pvt.Validate_Budget_Versions',
4263 'error - create_scenario.Validate_Budget_Versions'
4264 );
4265 end if;
4266
4267 -- Update and commit our changes
4268 IF (p_commit = FND_API.G_TRUE) THEN
4269 dbms_aw.execute('UPDATE');
4270 COMMIT;
4271 END IF;
4272
4273 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4274 FND_LOG.String
4275 (
4276 FND_LOG.LEVEL_PROCEDURE,
4277 'fpa.sql.FPA_Process_Pvt.create_scenario',
4278 'Detach AW space.'
4279 );
4280 END IF;
4281
4282 -- Detach AW Workspace
4283 Fpa_Utilities_Pvt.detach_AW
4284 (
4285 p_api_version => 1.0,
4286 x_return_status => x_return_status,
4287 x_msg_count => x_msg_count,
4288 x_msg_data => x_msg_data
4289 );
4290
4291 EXCEPTION
4292 WHEN FND_API.G_EXC_ERROR THEN
4293 ROLLBACK;
4294 x_return_status := FND_API.G_RET_STS_ERROR;
4295 FND_MSG_PUB.Count_And_Get
4296 (
4297 p_count => x_msg_count,
4298 p_data => x_msg_data
4299 );
4300 WHEN OTHERS THEN
4301 -- Detach AW Workspace
4302 Fpa_Utilities_Pvt.detach_AW
4303 (
4304 p_api_version => 1.0,
4305 x_return_status => x_return_status,
4306 x_msg_count => x_msg_count,
4307 x_msg_data => x_msg_data
4308 );
4309 ROLLBACK;
4310 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4311 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4312 FND_LOG.String
4313 (
4314 FND_LOG.LEVEL_ERROR,
4315 'fpa.sql.FPA_Process_Pvt.create_scenario',
4316 SQLERRM
4317 );
4318 END IF;
4319 FND_MSG_PUB.count_and_get
4320 (
4321 p_count => x_msg_count,
4322 p_data => x_msg_data
4323 );
4324
4325 END create_scenario;
4326
4327 /*******************************************************************************************
4328 *******************************************************************************************/
4329 -- This procedure sets the flag for the Initial Scenario, for the Working Scenario,
4330 -- For Recommending a scenario, and for Unrecommending a scenario..
4331 procedure set_scenario_action_flag
4332 (
4333 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4334 p_api_version IN NUMBER,
4335 p_scenario_id IN NUMBER,
4336 p_scenario_action IN VARCHAR2,
4337 x_return_status OUT NOCOPY VARCHAR2,
4338 x_msg_count OUT NOCOPY NUMBER,
4339 x_msg_data OUT NOCOPY VARCHAR2
4340 ) is
4341
4342 l_api_version CONSTANT NUMBER := 1.0;
4343 l_scenario_reccom_status VARCHAR2(30);
4344 l_approved_flag VARCHAR2(3);
4345 begin
4346
4347 -- clear all previous messages.
4348 FND_MSG_PUB.Initialize;
4349
4350 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4351 FND_LOG.String
4352 (
4353 FND_LOG.LEVEL_PROCEDURE,
4354 'fpa.sql.FPA_Process_Pvt.set_scenario_action_flag.begin',
4355 'Entering FPA_Process_Pvt.set_scenario_action_flag'
4356 );
4357 END IF;
4358
4359 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4360 FND_LOG.String
4361 (
4362 FND_LOG.LEVEL_PROCEDURE,
4363 'fpa.sql.FPA_Process_Pvt.set_scenario_action_flag',
4364 'Attaching AW space.'
4365 );
4366 END IF;
4367
4368 -- Attach AW Workspace
4369 Fpa_Utilities_Pvt.attach_AW
4370 (
4371 p_api_version => 1.0,
4372 p_attach_mode => 'rw',
4373 x_return_status => x_return_status,
4374 x_msg_count => x_msg_count,
4375 x_msg_data => x_msg_data
4376 );
4377
4378 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4379 FND_LOG.String
4380 (
4381 FND_LOG.LEVEL_PROCEDURE,
4382 'fpa.sql.FPA_Process_Pvt.set_scenario_action_flag',
4383 'Determininig what procedure to call based on p_scenario_action.'
4384 );
4385 END IF;
4386
4387 if upper(p_scenario_action) = 'RECOMMEND' then
4388 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4389 FND_LOG.String
4390 (
4391 FND_LOG.LEVEL_PROCEDURE,
4392 'fpa.sql.FPA_Process_Pvt.set_scenario_action_flag',
4393 'Determininig what procedure to call based on p_scenario_action.'
4394 );
4395 END IF;
4396 l_scenario_reccom_status := 'yes';
4397 fpa_scenario_pvt.update_scenario_reccom_flag
4398 (
4399 p_api_version => 1.0,
4400 p_scenario_id => p_scenario_id,
4401 p_scenario_reccom_status => l_scenario_reccom_status,
4402 x_return_status => x_return_status,
4403 x_msg_count => x_msg_count,
4404 x_msg_data => x_msg_data
4405 );
4406 elsif upper(p_scenario_action) = 'APPROVE' then
4407 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4408 FND_LOG.String
4409 (
4410 FND_LOG.LEVEL_PROCEDURE,
4411 'fpa.sql.FPA_Process_Pvt.set_scenario_action_flag',
4412 'Determininig what procedure to call based on p_scenario_action.'
4413 );
4414 END IF;
4415 l_approved_flag := 'yes';
4416 fpa_scenario_pvt.update_scen_approved_flag
4417 ( p_scenario_id => p_scenario_id,
4418 p_approved_flag => l_approved_flag,
4419 x_return_status => x_return_status,
4420 x_msg_count => x_msg_count,
4421 x_msg_data => x_msg_data
4422 );
4423
4424 elsif upper(p_scenario_action) = 'WITHDRAW' then
4425 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4426 FND_LOG.String
4427 (
4428 FND_LOG.LEVEL_PROCEDURE,
4429 'fpa.sql.FPA_Process_Pvt.set_scenario_action_flag',
4430 'Determininig what procedure to call based on p_scenario_action.'
4431 );
4432 END IF;
4433 l_scenario_reccom_status := 'na';
4434 fpa_scenario_pvt.update_scenario_reccom_flag
4435 (
4436 p_api_version => 1.0,
4437 p_scenario_id => p_scenario_id,
4438 p_scenario_reccom_status => l_scenario_reccom_status,
4439 x_return_status => x_return_status,
4440 x_msg_count => x_msg_count,
4441 x_msg_data => x_msg_data
4442 );
4443 elsif upper(p_scenario_action) = 'SETCURRENT' then
4444 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4445 FND_LOG.String
4446 (
4447 FND_LOG.LEVEL_PROCEDURE,
4448 'fpa.sql.FPA_Process_Pvt.set_scenario_action_flag',
4449 'Determininig what procedure to call based on p_scenario_action.'
4450 );
4451 END IF;
4452 fpa_scenario_pvt.update_scenario_working_flag
4453 (
4454 p_api_version => 1.0,
4455 p_scenario_id => p_scenario_id,
4456 x_return_status => x_return_status,
4457 x_msg_count => x_msg_count,
4458 x_msg_data => x_msg_data
4459 );
4460 end if;
4461
4462 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4463 FND_LOG.String
4464 (
4465 FND_LOG.LEVEL_PROCEDURE,
4466 'fpa.sql.FPA_Process_Pvt.set_scenario_action_flag',
4467 'Committing changes to database.'
4468 );
4469 END IF;
4470
4471 -- Update and commit our changes
4472 IF (p_commit = FND_API.G_TRUE) THEN
4473 dbms_aw.execute('UPDATE');
4474 COMMIT;
4475 END IF;
4476
4477 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4478 FND_LOG.String
4479 (
4480 FND_LOG.LEVEL_PROCEDURE,
4481 'fpa.sql.FPA_Process_Pvt.set_scenario_action_flag',
4482 'Detach AW space.'
4483 );
4484 END IF;
4485
4486 -- Detach AW Workspace
4487 Fpa_Utilities_Pvt.detach_AW
4488 (
4489 p_api_version => 1.0,
4490 x_return_status => x_return_status,
4491 x_msg_count => x_msg_count,
4492 x_msg_data => x_msg_data
4493 );
4494
4495 EXCEPTION
4496 WHEN FND_API.G_EXC_ERROR THEN
4497 ROLLBACK;
4498 x_return_status := FND_API.G_RET_STS_ERROR;
4499 FND_MSG_PUB.Count_And_Get
4500 (
4501 p_count => x_msg_count,
4502 p_data => x_msg_data
4503 );
4504 WHEN OTHERS THEN
4505 -- Detach AW Workspace
4506 Fpa_Utilities_Pvt.detach_AW
4507 (
4508 p_api_version => 1.0,
4509 x_return_status => x_return_status,
4510 x_msg_count => x_msg_count,
4511 x_msg_data => x_msg_data
4512 );
4513 ROLLBACK;
4514 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4515 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4516 FND_LOG.String
4517 (
4518 FND_LOG.LEVEL_ERROR,
4519 'fpa.sql.FPA_Process_Pvt.set_scenario_action_flag',
4520 SQLERRM
4521 );
4522 END IF;
4523 FND_MSG_PUB.count_and_get
4524 (
4525 p_count => x_msg_count,
4526 p_data => x_msg_data
4527 );
4528
4529 end set_scenario_action_flag;
4530
4531 procedure update_scenario_reccom_status
4532 (
4533 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4534 p_api_version IN NUMBER,
4535 p_scenario_id IN NUMBER,
4536 p_project_id IN VARCHAR2,
4537 p_scenario_reccom_value IN VARCHAR2,
4538 x_return_status OUT NOCOPY VARCHAR2,
4539 x_msg_count OUT NOCOPY NUMBER,
4540 x_msg_data OUT NOCOPY VARCHAR2
4541 ) is
4542
4543 l_api_version CONSTANT NUMBER := 1.0;
4544
4545 begin
4546
4547 -- clear all previous messages.
4548 FND_MSG_PUB.Initialize;
4549
4550
4551 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4552 FND_LOG.String
4553 (
4554 FND_LOG.LEVEL_PROCEDURE,
4555 'fpa.sql.FPA_Process_Pvt.update_scenario_reccom_status.begin',
4556 'Entering FPA_Process_Pvt.update_scenario_reccom_status'
4557 );
4558 END IF;
4559
4560 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4561 FND_LOG.String
4562 (
4563 FND_LOG.LEVEL_PROCEDURE,
4564 'fpa.sql.FPA_Process_Pvt.update_scenario_reccom_status',
4565 'Attaching AW space.'
4566 );
4567 END IF;
4568
4569 -- Attach AW Workspace
4570 Fpa_Utilities_Pvt.attach_AW
4571 (
4572 p_api_version => 1.0,
4573 p_attach_mode => 'rw',
4574 x_return_status => x_return_status,
4575 x_msg_count => x_msg_count,
4576 x_msg_data => x_msg_data
4577 );
4578
4579 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4580 FND_LOG.String
4581 (
4582 FND_LOG.LEVEL_PROCEDURE,
4583 'fpa.sql.FPA_Process_Pvt.update_scenario_reccom_status',
4584 'Calling procedure fpa_scenario_pvt.update_scenario_reccom_status.'
4585 );
4586 END IF;
4587
4588 fpa_scenario_pvt.update_scenario_reccom_status
4589 (
4590 p_api_version => 1.0,
4591 p_scenario_id => p_scenario_id,
4592 p_project_id => p_project_id,
4593 p_scenario_reccom_value => p_scenario_reccom_value,
4594 x_return_status => x_return_status,
4595 x_msg_count => x_msg_count,
4596 x_msg_data => x_msg_data
4597 );
4598
4599 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4600 FND_LOG.String
4601 (
4602 FND_LOG.LEVEL_PROCEDURE,
4603 'fpa.sql.FPA_Process_Pvt.update_scenario_reccom_status',
4604 'Calling procedure fpa_scenario_pvt.calc_scenario_data for Class Codes.'
4605 );
4606 END IF;
4607
4608 fpa_scenario_pvt.calc_scenario_data
4609 (
4610 p_api_version => 1.0,
4611 p_scenario_id => p_scenario_id,
4612 p_project_id => null,
4613 p_class_code_id => null,
4614 p_data_to_calc => 'CLASS',
4615 x_return_status => x_return_status,
4616 x_msg_count => x_msg_count,
4617 x_msg_data => x_msg_data
4618 );
4619
4620 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4621 FND_LOG.String
4622 (
4623 FND_LOG.LEVEL_PROCEDURE,
4624 'fpa.sql.FPA_Process_Pvt.update_scenario_reccom_status',
4625 'Calling procedure fpa_scenario_pvt.calc_scenario_data for Scenario.'
4626 );
4627 END IF;
4628
4629 fpa_scenario_pvt.calc_scenario_data
4630 (
4631 p_api_version => 1.0,
4632 p_scenario_id => p_scenario_id,
4633 p_project_id => null,
4634 p_class_code_id => null,
4635 p_data_to_calc => 'SCEN',
4636 x_return_status => x_return_status,
4637 x_msg_count => x_msg_count,
4638 x_msg_data => x_msg_data
4639 );
4640
4641 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4642 FND_LOG.String
4643 (
4644 FND_LOG.LEVEL_PROCEDURE,
4645 'fpa.sql.FPA_Process_Pvt.update_scenario_reccom_status',
4646 'Committing changes to database.'
4647 );
4648 END IF;
4649
4650 -- Update and commit our changes
4651 IF (p_commit = FND_API.G_TRUE) THEN
4652 dbms_aw.execute('UPDATE');
4653 COMMIT;
4654 END IF;
4655
4656 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4657 FND_LOG.String
4658 (
4659 FND_LOG.LEVEL_PROCEDURE,
4660 'fpa.sql.FPA_Process_Pvt.update_scenario_reccom_status',
4661 'Detach AW space.'
4662 );
4663 END IF;
4664
4665 -- Detach AW Workspace
4666 Fpa_Utilities_Pvt.detach_AW
4667 (
4668 p_api_version => 1.0,
4669 x_return_status => x_return_status,
4670 x_msg_count => x_msg_count,
4671 x_msg_data => x_msg_data
4672 );
4673
4674 EXCEPTION
4675 WHEN FND_API.G_EXC_ERROR THEN
4676 ROLLBACK;
4677 x_return_status := FND_API.G_RET_STS_ERROR;
4678 FND_MSG_PUB.Count_And_Get
4679 (
4680 p_count => x_msg_count,
4681 p_data => x_msg_data
4682 );
4683 WHEN OTHERS THEN
4684 -- Detach AW Workspace
4685 Fpa_Utilities_Pvt.detach_AW
4686 (
4687 p_api_version => 1.0,
4688 x_return_status => x_return_status,
4689 x_msg_count => x_msg_count,
4690 x_msg_data => x_msg_data
4691 );
4692 ROLLBACK;
4693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4694 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4695 FND_LOG.String
4696 (
4697 FND_LOG.LEVEL_ERROR,
4698 'fpa.sql.FPA_Process_Pvt.update_scenario_reccom_status',
4699 SQLERRM
4700 );
4701 END IF;
4702 FND_MSG_PUB.count_and_get
4703 (
4704 p_count => x_msg_count,
4705 p_data => x_msg_data
4706 );
4707
4708 END update_scenario_reccom_status;
4709
4710 PROCEDURE Submit_Project_Aw
4711 (
4712 p_api_version IN NUMBER,
4713 p_init_msg_list IN VARCHAR2,
4714 p_commit IN VARCHAR2,
4715 p_project_id IN NUMBER,
4716 x_return_status OUT NOCOPY VARCHAR2,
4717 x_msg_count OUT NOCOPY NUMBER,
4718 x_msg_data OUT NOCOPY VARCHAR2
4719 ) IS
4720
4721 l_return_status VARCHAR2(1);
4722 l_api_name CONSTANT VARCHAR2(30) := 'Submit_Project_Aw';
4723 l_api_version CONSTANT NUMBER := 1.0;
4724 l_msg_log VARCHAR2(200) := null;
4725
4726 CURSOR PROJ_FUNDING_STATUS_CSR(P_PROJECT_ID IN NUMBER) IS
4727 SELECT 'T'
4728 FROM PA_PROJECTS_ALL
4729 WHERE PROJECT_ID = P_PROJECT_ID
4730 AND FUNDING_APPROVAL_STATUS_CODE IN
4731 ('FUNDING_PROPOSED','FUNDING_ONHOLD','FUNDING_APPROVED');
4732
4733 l_flag VARCHAR2(1) := null;
4734
4735 BEGIN
4736
4737
4738 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
4739 -- call START_ACTIVITY to create savepoint, check compatibility
4740 -- and initialize message list
4741
4742 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
4743 p_api_name => l_api_name,
4744 p_pkg_name => G_PKG_NAME,
4745 p_init_msg_list => p_init_msg_list,
4746 l_api_version => l_api_version,
4747 p_api_version => p_api_version,
4748 p_api_type => G_API_TYPE,
4749 p_msg_log => 'Entering Fpa_Process_Pvt.Submit_Project_Aw',
4750 x_return_status => x_return_status);
4751
4752
4753 -- check if activity started successfully
4754 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
4755 l_msg_log := 'start_activity';
4756 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
4757 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
4758 l_msg_log := 'start_activity';
4759 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
4760 end if;
4761
4762 FPA_UTILITIES_PVT.Attach_AW
4763 (p_api_version => l_api_version,
4764 p_attach_mode => 'rw',
4765 x_return_status => x_return_status,
4766 x_msg_count => x_msg_count,
4767 x_msg_data => x_msg_data);
4768
4769
4770 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
4771
4772 FPA_PROJECT_PVT.Submit_Project_Aw(
4773 p_api_version => p_api_version,
4774 p_init_msg_list => p_init_msg_list,
4775 p_commit => p_commit,
4776 p_project_id => p_project_id,
4777 x_return_status => x_return_status,
4778 x_msg_count => x_msg_count,
4779 x_msg_data => x_msg_data);
4780
4781 FPA_UTILITIES_PVT.Detach_AW
4782 (p_api_version => 1.0,
4783 x_return_status => x_return_status,
4784 x_msg_count => x_msg_count,
4785 x_msg_data => x_msg_data);
4786
4787 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
4788
4789 FPA_UTILITIES_PVT.END_ACTIVITY(
4790 p_api_name => l_api_name,
4791 p_pkg_name => G_PKG_NAME,
4792 p_msg_log => null,
4793 x_msg_count => x_msg_count,
4794 x_msg_data => x_msg_data);
4795
4796 EXCEPTION
4797 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
4798
4799 FPA_UTILITIES_PVT.Detach_AW(
4800 p_api_version => l_api_version,
4801 x_return_status => x_return_status,
4802 x_msg_count => x_msg_count,
4803 x_msg_data => x_msg_data);
4804
4805 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
4806 p_api_name => l_api_name,
4807 p_pkg_name => G_PKG_NAME,
4808 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
4809 p_msg_log => l_msg_log,
4810 x_msg_count => x_msg_count,
4811 x_msg_data => x_msg_data,
4812 p_api_type => G_API_TYPE);
4813
4814 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
4815
4816 FPA_UTILITIES_PVT.Detach_AW(
4817 p_api_version => l_api_version,
4818 x_return_status => x_return_status,
4819 x_msg_count => x_msg_count,
4820 x_msg_data => x_msg_data);
4821
4822 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
4823 p_api_name => l_api_name,
4824 p_pkg_name => G_PKG_NAME,
4825 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
4826 p_msg_log => l_msg_log,
4827 x_msg_count => x_msg_count,
4828 x_msg_data => x_msg_data,
4829 p_api_type => G_API_TYPE);
4830
4831 when OTHERS then
4832
4833 FPA_UTILITIES_PVT.Detach_AW(
4834 p_api_version => l_api_version,
4835 x_return_status => x_return_status,
4836 x_msg_count => x_msg_count,
4837 x_msg_data => x_msg_data);
4838
4839 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
4840 p_api_name => l_api_name,
4841 p_pkg_name => G_PKG_NAME,
4842 p_exc_name => 'OTHERS',
4843 p_msg_log => l_msg_log||SQLERRM,
4844 x_msg_count => x_msg_count,
4845 x_msg_data => x_msg_data,
4846 p_api_type => G_API_TYPE);
4847
4848 END Submit_Project_Aw;
4849
4850 PROCEDURE Load_Project_Details_Aw
4851 (
4852 p_api_version IN NUMBER,
4853 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
4854 p_commit IN VARCHAR2,
4855 p_type IN VARCHAR2,
4856 p_scenario_id IN NUMBER,
4857 p_projects IN VARCHAR2,
4858 x_return_status OUT NOCOPY VARCHAR2,
4859 x_msg_count OUT NOCOPY NUMBER,
4860 x_msg_data OUT NOCOPY VARCHAR2) IS
4861
4862 l_return_status VARCHAR2(1);
4863 l_api_name CONSTANT VARCHAR2(30) := 'Load_Project_Details_Aw';
4864 l_api_version CONSTANT NUMBER := 1.0;
4865 l_msg_log VARCHAR2(200) := null;
4866
4867 BEGIN
4868
4869 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
4870 -- call START_ACTIVITY to create savepoint, check compatibility
4871 -- and initialize message list
4872
4873 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
4874 p_api_name => l_api_name,
4875 p_pkg_name => G_PKG_NAME,
4876 p_init_msg_list => p_init_msg_list,
4877 l_api_version => l_api_version,
4878 p_api_version => p_api_version,
4879 p_api_type => G_API_TYPE,
4880 p_msg_log => 'Entering Fpa_Process_Pvt.Load_Project_Details_Aw',
4881 x_return_status => x_return_status);
4882
4883
4884 -- check if activity started successfully
4885 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
4886 l_msg_log := 'start_activity';
4887 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
4888 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
4889 l_msg_log := 'start_activity';
4890 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
4891 end if;
4892
4893 FPA_UTILITIES_PVT.Attach_AW
4894 (p_api_version => l_api_version,
4895 p_attach_mode => 'rw',
4896 x_return_status => x_return_status,
4897 x_msg_count => x_msg_count,
4898 x_msg_data => x_msg_data);
4899
4900
4901 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
4902
4903
4904 FPA_PROJECT_PVT.Load_Project_Details_Aw(
4905 p_api_version => p_api_version,
4906 p_init_msg_list => p_init_msg_list,
4907 p_type => p_type,
4908 p_scenario_id => p_scenario_id,
4909 p_projects => p_projects,
4910 x_return_status => x_return_status,
4911 x_msg_count => x_msg_count,
4912 x_msg_data => x_msg_data);
4913
4914
4915 if (p_commit = FND_API.G_TRUE) then
4916 dbms_aw.execute('UPDATE');
4917 COMMIT;
4918 end if;
4919
4920 FPA_UTILITIES_PVT.Detach_AW
4921 (p_api_version => 1.0,
4922 x_return_status => x_return_status,
4923 x_msg_count => x_msg_count,
4924 x_msg_data => x_msg_data);
4925
4926 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
4927
4928 FPA_UTILITIES_PVT.END_ACTIVITY(
4929 p_api_name => l_api_name,
4930 p_pkg_name => G_PKG_NAME,
4931 p_msg_log => null,
4932 x_msg_count => x_msg_count,
4933 x_msg_data => x_msg_data);
4934
4935 EXCEPTION
4936 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
4937
4938 FPA_UTILITIES_PVT.Detach_AW(
4939 p_api_version => l_api_version,
4940 x_return_status => x_return_status,
4941 x_msg_count => x_msg_count,
4942 x_msg_data => x_msg_data);
4943
4944 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
4945 p_api_name => l_api_name,
4946 p_pkg_name => G_PKG_NAME,
4947 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
4948 p_msg_log => l_msg_log,
4949 x_msg_count => x_msg_count,
4950 x_msg_data => x_msg_data,
4951 p_api_type => G_API_TYPE);
4952
4953 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
4954
4955 FPA_UTILITIES_PVT.Detach_AW(
4956 p_api_version => l_api_version,
4957 x_return_status => x_return_status,
4958 x_msg_count => x_msg_count,
4959 x_msg_data => x_msg_data);
4960
4961 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
4962 p_api_name => l_api_name,
4963 p_pkg_name => G_PKG_NAME,
4964 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
4965 p_msg_log => l_msg_log,
4966 x_msg_count => x_msg_count,
4967 x_msg_data => x_msg_data,
4968 p_api_type => G_API_TYPE);
4969
4970 when OTHERS then
4971
4972 FPA_UTILITIES_PVT.Detach_AW(
4973 p_api_version => l_api_version,
4974 x_return_status => x_return_status,
4975 x_msg_count => x_msg_count,
4976 x_msg_data => x_msg_data);
4977
4978 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
4979 p_api_name => l_api_name,
4980 p_pkg_name => G_PKG_NAME,
4981 p_exc_name => 'OTHERS',
4982 p_msg_log => l_msg_log||SQLERRM,
4983 x_msg_count => x_msg_count,
4984 x_msg_data => x_msg_data,
4985 p_api_type => G_API_TYPE);
4986
4987 END Load_Project_Details_Aw;
4988
4989
4990 /********************************************************************************************
4991 ********************************************************************************************/
4992
4993 PROCEDURE Close_Pc
4994 (
4995 p_api_version IN NUMBER,
4996 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
4997 p_pc_id IN NUMBER,
4998 x_return_status OUT NOCOPY VARCHAR2,
4999 x_msg_count OUT NOCOPY NUMBER,
5000 x_msg_data OUT NOCOPY VARCHAR2
5001 ) is
5002
5003 begin
5004
5005 -- clear all previous messages.
5006 FND_MSG_PUB.Initialize;
5007
5008 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5009 fnd_log.string
5010 (
5011 FND_LOG.LEVEL_PROCEDURE,
5012 'fpa.sql.fpa_process_pvt.close_pc.begin',
5013 'Entering fpa_process_pvt.close_pc ,Calling Fpa_Utilities_Pvt.attach_AW'
5014 );
5015 END IF;
5016
5017 -- Attach AW Workspace
5018 Fpa_Utilities_Pvt.attach_AW
5019 (
5020 p_api_version => 1.0,
5021 p_attach_mode => 'rw',
5022 x_return_status => x_return_status,
5023 x_msg_count => x_msg_count,
5024 x_msg_data => x_msg_data
5025 );
5026
5027
5028 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5029 fnd_log.string
5030 (
5031 FND_LOG.LEVEL_STATEMENT,
5032 'fpa.sql.fpa_process_pvt.close_pc.begin',
5033 'Calling fpa_main_process_pvt.raise_closepc_event'
5034 );
5035 END IF;
5036
5037 fpa_main_process_pvt.raise_closepc_event( p_pc_id => p_pc_id,
5038 x_return_status => x_return_status,
5039 x_msg_count => x_msg_count,
5040 x_msg_data => x_msg_data
5041 );
5042
5043 -- Update and commit our changes
5044 IF (p_commit = FND_API.G_TRUE) THEN
5045 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5046 FND_LOG.String
5047 ( FND_LOG.LEVEL_PROCEDURE,
5048 'fpa.sql.FPA_Process_Pvt.Close_Pc',
5049 'Updating and Committing.'
5050 );
5051 END IF;
5052 dbms_aw.execute('UPDATE');
5053 COMMIT;
5054 END IF;
5055
5056 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5057 FND_LOG.String
5058 ( FND_LOG.LEVEL_PROCEDURE,
5059 'fpa.sql.FPA_Process_Pvt.Close_Pc',
5060 'CAlling Fpa_Utilities_Pvt.detach_AW.'
5061 );
5062 END IF;
5063
5064 -- Detach AW Workspace
5065 Fpa_Utilities_Pvt.detach_AW
5066 (
5067 p_api_version => 1.0,
5068 x_return_status => x_return_status,
5069 x_msg_count => x_msg_count,
5070 x_msg_data => x_msg_data
5071 );
5072
5073 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5074 FND_LOG.String
5075 (
5076 FND_LOG.LEVEL_PROCEDURE,
5077 'fpa.sql.FPA_Process_Pvt.Close_Pc.end',
5078 'Exiting FPA_Process_Pvt.Close_Pc'
5079 );
5080 END IF;
5081
5082 EXCEPTION
5083 WHEN FND_API.G_EXC_ERROR THEN
5084 ROLLBACK;
5085 x_return_status := FND_API.G_RET_STS_ERROR;
5086 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5087 fnd_log.string
5088 (FND_LOG.LEVEL_ERROR,
5089 'fpa_process_pvt.create_portfolio',
5090 SQLERRM);
5091 END IF;
5092 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
5093 ,p_data => x_msg_data);
5094 WHEN OTHERS THEN
5095 FND_MESSAGE.SET_NAME('FPA','FPA_UNEXP_GENERAL_ERROR');
5096 FND_MESSAGE.SET_TOKEN('SOURCE', 'fpa_process_pvt.Close_Pc');
5097 FND_MESSAGE.SET_TOKEN('SQL_ERR_CODE', SQLCODE);
5098 FND_MESSAGE.SET_TOKEN('SQL_ERR_MSG', SQLERRM);
5099 FND_MSG_PUB.ADD;
5100 -- Detach AW Workspace
5101 Fpa_Utilities_Pvt.detach_AW
5102 ( p_api_version => 1.0,
5103 x_return_status => x_return_status,
5104 x_msg_count => x_msg_count,
5105 x_msg_data => x_msg_data
5106 );
5107 ROLLBACK;
5108 x_return_status := FND_API.G_RET_STS_ERROR;
5109 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5110 fnd_log.string
5111 ( FND_LOG.LEVEL_ERROR,
5112 'fpa_process_pvt.Close_Pc',
5113 SQLERRM);
5114 END IF;
5115 FND_MSG_PUB.count_and_get
5116 ( p_count => x_msg_count,
5117 p_data => x_msg_data);
5118
5119 end Close_Pc;
5120
5121
5122 /*
5123 * Updates user ranks for all projects in the current scenario.
5124 */
5125
5126 PROCEDURE Update_Scen_Proj_User_Ranks
5127 ( p_api_version IN NUMBER,
5128 p_commit IN VARCHAR2 := FND_API.G_FALSE,
5129 p_projs IN fpa_scen_proj_userrank_all_obj,
5130 x_return_status OUT NOCOPY VARCHAR2,
5131 x_msg_data OUT NOCOPY VARCHAR2,
5132 x_msg_count OUT NOCOPY NUMBER )
5133 IS
5134 BEGIN
5135
5136 -- clear all previous messages.
5137 FND_MSG_PUB.Initialize;
5138
5139 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5140 FND_LOG.String
5141 (
5142 FND_LOG.LEVEL_PROCEDURE,
5143 'fpa.sql.FPA_Process_Pvt.Update_Scen_Proj_User_Ranks.begin',
5144 'Entering FPA_Process_Pvt.Update_Scen_Proj_User_Ranks'
5145 );
5146 END IF;
5147
5148 -- Attach AW Workspace
5149 Fpa_Utilities_Pvt.attach_AW
5150 (
5151 p_api_version => 1.0,
5152 p_attach_mode => 'rw',
5153 x_return_status => x_return_status,
5154 x_msg_count => x_msg_count,
5155 x_msg_data => x_msg_data
5156 );
5157
5158 FPA_Scenario_Pvt.Update_Proj_User_Ranks
5159 (
5160 p_api_version => 1.0,
5161 p_proj_metrics => p_projs.user_ranks,
5162 x_return_status => x_return_status,
5163 x_msg_data => x_msg_data,
5164 x_msg_count => x_msg_count
5165 );
5166
5167 -- Update and commit our changes
5168 IF (p_commit = FND_API.G_TRUE) THEN
5169 dbms_aw.execute('UPDATE');
5170 COMMIT;
5171 END IF;
5172
5173 -- Detach AW Workspace
5174 Fpa_Utilities_Pvt.detach_AW
5175 (
5176 p_api_version => 1.0,
5177 x_return_status => x_return_status,
5178 x_msg_count => x_msg_count,
5179 x_msg_data => x_msg_data
5180 );
5181 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5182 FND_LOG.String
5183 (
5184 FND_LOG.LEVEL_PROCEDURE,
5185 'fpa.sql.FPA_Process_Pvt.Update_Scen_Proj_User_Ranks.end',
5186 'Exiting FPA_Process_Pvt.Update_Scen_Proj_User_Ranks'
5187 );
5188 END IF;
5189
5190
5191 EXCEPTION
5192 WHEN OTHERS THEN
5193 FND_MESSAGE.SET_NAME('FPA','FPA_UNEXP_GENERAL_ERROR');
5194 FND_MESSAGE.SET_TOKEN('SOURCE', 'fpa.sql.FPA_Process_Pvt.Update_Scen_Proj_User_Ranks');
5195 FND_MESSAGE.SET_TOKEN('SQL_ERR_CODE', SQLCODE);
5196 FND_MESSAGE.SET_TOKEN('SQL_ERR_MSG', SQLERRM);
5197 FND_MSG_PUB.ADD;
5198 ROLLBACK;
5199 -- Detach AW Workspace
5200 Fpa_Utilities_Pvt.detach_AW
5201 (
5202 p_api_version => 1.0,
5203 x_return_status => x_return_status,
5204 x_msg_count => x_msg_count,
5205 x_msg_data => x_msg_data
5206 );
5207
5208 x_return_status := FND_API.G_RET_STS_ERROR;
5209
5210 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5211 FND_LOG.String
5212 (
5213 FND_LOG.LEVEL_ERROR,
5214 'fpa.sql.FPA_Process_Pvt.Update_Scen_Proj_User_Ranks',
5215 SQLERRM
5216 );
5217 END IF;
5218
5219 FND_MSG_PUB.count_and_get
5220 (
5221 p_count => x_msg_count,
5222 p_data => x_msg_data
5223 );
5224 RAISE;
5225 END Update_Scen_Proj_User_Ranks;
5226
5227
5228 -- sishanmu added on 01/25/2005
5229 PROCEDURE update_pjt_proj_funding_status
5230 ( p_api_version IN NUMBER,
5231 p_init_msg_list IN VARCHAR2,
5232 p_commit IN VARCHAR2,
5233 p_scenario_id IN NUMBER,
5234 x_return_status OUT NOCOPY VARCHAR2,
5235 x_msg_count OUT NOCOPY NUMBER,
5236 x_msg_data OUT NOCOPY VARCHAR2) IS
5237
5238 BEGIN
5239
5240 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5241 FND_LOG.String
5242 (
5243 FND_LOG.LEVEL_PROCEDURE,
5244 'fpa.sql.FPA_Process_Pvt.update_pjt_proj_funding_status.Begin',
5245 'Exiting FPA_Process_Pvt.update_pjt_proj_funding_status'
5246 );
5247 END IF;
5248
5249 x_return_status := FND_API.G_RET_STS_SUCCESS;
5250
5251 fpa_project_pvt.update_proj_funding_status
5252 (
5253 p_api_version => 1.0,
5254 p_init_msg_list => p_init_msg_list,
5255 p_commit => FND_API.G_FALSE,
5256 p_appr_scenario_id => p_scenario_id,
5257 x_return_status => x_return_status,
5258 x_msg_count => x_msg_count,
5259 x_msg_data => x_msg_data
5260 );
5261
5262 if x_return_status = FND_API.G_RET_STS_ERROR then
5263 RAISE FND_API.G_EXC_ERROR;
5264 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
5265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5266 end if;
5267
5268 IF p_commit = FND_API.G_TRUE THEN
5269 COMMIT;
5270 END IF;
5271
5272 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5273 FND_LOG.String
5274 (
5275 FND_LOG.LEVEL_PROCEDURE,
5276 'fpa.sql.FPA_Process_Pvt.update_pjt_proj_funding_status.end',
5277 'Exiting FPA_Process_Pvt.update_pjt_proj_funding_status'
5278 );
5279 END IF;
5280
5281
5282 EXCEPTION
5283 when FND_API.G_EXC_ERROR then
5284 IF p_commit = FND_API.G_TRUE THEN
5285 ROLLBACK;
5286 END IF;
5287 x_return_status := 'E';
5288
5289 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5290
5291 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5292 fnd_msg_pub.add_exc_msg(p_pkg_name => 'FPA_PROCESS_PVT',
5293 p_procedure_name => 'UPDATE_PJT_PROJ_FUNDING_STATUS',
5294 p_error_text => SUBSTRB(SQLERRM,1,240));
5295
5296
5297 IF p_commit = FND_API.G_TRUE THEN
5298 ROLLBACK;
5299 END IF;
5300
5301 RAISE;
5302
5303 WHEN OTHERS THEN
5304 IF p_commit = FND_API.G_TRUE THEN
5305 ROLLBACK;
5306 END IF;
5307 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5308 fnd_msg_pub.add_exc_msg(p_pkg_name => 'FPA_PROCESS_PVT',
5309 p_procedure_name => 'UPDATE_PJT_PROJ_FUNDING_STATUS',
5310 p_error_text => SUBSTRB(SQLERRM,1,240));
5311
5312 RAISE;
5313
5314 END update_pjt_proj_funding_status;
5315
5316 -- Function call returns 'T' or 'F' to enable or disable Scorecard link
5317 -- in project Setup
5318 FUNCTION proj_scorecard_link_enabled
5319 ( p_function_name IN VARCHAR2,
5320 p_project_id IN NUMBER)
5321 RETURN VARCHAR2 IS
5322
5323 l_licensed_flag varchar2(1) := 'F';
5324 l_pc_active_flag varchar2(1) := 'F';
5325 l_enabled_flag varchar2(1) := 'F';
5326 l_active_pc_count number(15);
5327 FPA_PJP_NOT_LICENSED EXCEPTION;
5328
5329 -- Cursor checks for Active planning cycles in the portfolio that the project belongs to.
5330 cursor c_pc_active is
5331 select count(a.project) Validpc
5332 from fpa_aw_projs_v a,
5333 fpa_aw_pcs_v b,
5334 fpa_aw_pc_info_v c
5335 where a.portfolio = b.portfolio
5336 and b.planning_cycle = c.planning_cycle
5337 and c.pc_status in ('COLLECTING', 'ANALYSIS')
5338 and a.project = p_project_id;
5339
5340 BEGIN
5341
5342 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5343 FND_LOG.String
5344 (
5345 FND_LOG.LEVEL_PROCEDURE,
5346 'fpa.sql.FPA_Process_Pvt.proj_scorecard_link_enabled.Begin',
5347 'Calling procedure FPA_Process_Pvt.proj_scorecard_link_enabled.'
5348 );
5349 END IF;
5350
5351 -- Check for Licensing profile option
5352 -- If the function returned 'N', Licensing is TURNED OFF.
5353 -- Scorelink should not be enabled. No need to check for active planning cycles.
5354 -- Raise an Exception, return 'F', and exit the program
5355 IF pa_product_install_utils.check_function_licensed(p_function_name) <> 'Y' then
5356 l_enabled_flag := 'F';
5357
5358 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5359 FND_LOG.String
5360 ( FND_LOG.LEVEL_STATEMENT,
5361 'fpa.sql.FPA_Process_Pvt.proj_scorecard_link_enabled',
5362 'PJP License for Function '||p_function_name||' = NO. Disable Scorecard Link'
5363 );
5364 END IF;
5365
5366 raise FPA_PJP_NOT_LICENSED;
5367 end if;
5368
5369 -- Licensing is available for PJP
5370 -- Now look for active planning cycle.
5371 -- If an active planning cycle exists, set enabled flag to 'T'
5372 -- Score card link should be ebabled.
5373 open c_pc_active;
5374 fetch c_pc_active into l_active_pc_count;
5375 if (l_active_pc_count > 0) and (FPA_PROJECT_PVT.valid_project(p_project_id) = FND_API.G_TRUE) then
5376 -- active pl cycle exists for this project.
5377 -- Project classfications match the correct portfolio and planning cycle.
5378 l_enabled_flag := 'T';
5379
5380 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5381 FND_LOG.String
5382 ( FND_LOG.LEVEL_STATEMENT,
5383 'fpa.sql.FPA_Process_Pvt.proj_scorecard_link_enabled',
5384 'ProjectID = '||p_project_id||' Planing Cycle is active. Enable Scorecard link'
5385 );
5386 END IF;
5387
5388 else
5389 -- Licensing is available for PJP but, no active planning cycle exist.
5390 -- Scorecard link should be disabled
5391 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5392 FND_LOG.String
5393 ( FND_LOG.LEVEL_STATEMENT,
5394 'fpa.sql.FPA_Process_Pvt.proj_scorecard_link_enabled',
5395 'ProjectID = '||p_project_id||' No active Planning Cycle. Disable Scorecard link'
5396 );
5397 END IF;
5398
5399 end if;
5400 close c_pc_active;
5401
5402 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5403 FND_LOG.String
5404 (
5405 FND_LOG.LEVEL_PROCEDURE,
5406 'fpa.sql.FPA_Process_Pvt.proj_scorecard_link_enabled.end',
5407 'Exiting FPA_Process_Pvt.proj_scorecard_link_enabled'
5408 );
5409 END IF;
5410
5411 RETURN l_enabled_flag;
5412
5413 EXCEPTION
5414 WHEN FPA_PJP_NOT_LICENSED then
5415 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5416 FND_LOG.String
5417 ( FND_LOG.LEVEL_EXCEPTION,
5418 'fpa.sql.FPA_Process_Pvt.proj_scorecard_link_enabled',
5419 'PJP Not Licensed. Score Link should be disabled'
5420 );
5421 END IF;
5422
5423 RETURN l_enabled_flag;
5424
5425 when OTHERS then
5426 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5427 FND_LOG.String
5428 ( FND_LOG.LEVEL_ERROR,
5429 'fpa.sql.FPA_Process_Pvt.proj_scorecard_link_enabled',
5430 'Score Link should be disabled for ProjectID '||p_project_id
5431 );
5432 END IF;
5433 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
5434 FND_LOG.String
5435 ( FND_LOG.LEVEL_ERROR,
5436 'fpa.sql.FPA_Process_Pvt.proj_scorecard_link_enabled',
5437 SQLERRM
5438 );
5439 END IF;
5440
5441 RETURN l_enabled_flag;
5442
5443 END proj_scorecard_link_enabled;
5444
5445 END FPA_PROCESS_PVT;