[Home] [Help]
PACKAGE BODY: APPS.FPA_PLANNINGCYCLE_PVT
Source
1 PACKAGE BODY fpa_planningcycle_pvt as
2 /* $Header: FPAVPCPB.pls 120.4 2007/06/21 15:20:16 rdave ship $ */
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- Monika
12 -- --------- ------ ------------------------------------------
13 -- Enter procedure, function bodies as shown below
14
15 PROCEDURE Create_Pc
16 (
17 p_api_version IN NUMBER,
18 p_pc_all_obj IN fpa_pc_all_obj,
19 x_planning_cycle_id OUT NOCOPY NUMBER,
20 x_return_status OUT NOCOPY VARCHAR2,
21 x_msg_data OUT NOCOPY VARCHAR2,
22 x_msg_count OUT NOCOPY NUMBER
23 )
24 IS
25 -- A cursor to get the new unique id for the Pc
26 CURSOR l_pc_s_csr
27 IS
28 SELECT fpa_planning_cycle_s.nextval AS l_pc_id
29 FROM dual;
30
31 -- A record to hold the new sequence value
32 l_pc_s_r l_pc_s_csr%ROWTYPE;
33
34 -- A variable to hold language
35 l_language varchar2(4);
36
37 -- A cursor to hold language code
38 CURSOR l_language_csr
39 IS
40 SELECT language_code
41 FROM fnd_languages
42 WHERE installed_flag IN ('I','B');
43
44 BEGIN
45
46 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
47 FND_LOG.String
48 (
49 FND_LOG.LEVEL_PROCEDURE,
50 'fpa.sql.FPA_PlanningCycle_Pvt.Create_Pc.begin',
51 'Entering FPA_PlanningCycle_Pvt.Create_Pc'
52 );
53 END IF;
54
55
56 -- Get the next sequence value for the PC identifier
57 OPEN l_pc_s_csr;
58 FETCH l_pc_s_csr INTO l_pc_s_r;
59 CLOSE l_pc_s_csr;
60
61 -- We return the id of the new PC to the caller
62 x_planning_cycle_id := l_pc_s_r.l_pc_id;
63
64
65 -- Close the Cursor
66 IF (l_language_csr%ISOPEN) THEN
67 CLOSE l_language_csr;
68 END IF;
69
70 --DBMS_OUTPUT.put_line('Before Inserting Pc Name and Desc.....');
71
72 -- Open the cursor and insert pc_name, pc_desc for each language code
73 OPEN l_language_csr;
74 LOOP
75 FETCH l_language_csr INTO l_language;
76 EXIT WHEN l_language_csr%NOTFOUND;
77
78 INSERT INTO FPA_OBJECTS_TL( object
79 ,id
80 ,name
81 ,description
82 ,LANGUAGE
83 ,SOURCE_LANG
84 ,created_by
85 ,creation_date
86 ,last_updated_by
87 ,last_update_date
88 ,last_update_login)
89 VALUES( 'PLANNING_CYCLE'
90 ,l_pc_s_r.l_pc_id
91 ,p_pc_all_obj.pc_desc_fields.name
92 ,p_pc_all_obj.pc_desc_fields.description
93 ,l_language
94 ,USERENV('LANG')
95 ,fnd_global.user_id
96 ,sysdate()
97 ,fnd_global.user_id
98 ,sysdate()
99 ,0);
100
101 END LOOP;
102 CLOSE l_language_csr;
103
104 --DBMS_OUTPUT.put_line('Before Maintain ID.....');
105
106 -- Add the new scenario to the dimension
107 dbms_aw.execute('MAINTAIN planning_cycle_d ADD ' || l_pc_s_r.l_pc_id );
108
109 --DBMS_OUTPUT.put_line('Before Limit ID.....');
110 dbms_aw.execute('PUSH planning_cycle_d');
111
112 dbms_aw.execute('LMT planning_cycle_d TO ' || l_pc_s_r.l_pc_id );
113
114 --DBMS_OUTPUT.put_line('Before setting the Portfolio relation.....portfolio_id=' || p_pc_all_obj.pc_info.portfolio);
115
116 -- Associate the planning cycle with the portfolio
117 dbms_aw.execute('portfolio_pc_r = ' || p_pc_all_obj.pc_info.portfolio );
118
119 dbms_aw.execute('POP planning_cycle_d');
120
121 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
122 FND_LOG.String
123 (
124 FND_LOG.LEVEL_PROCEDURE,
125 'fpa.sql.FPA_PlanningCycle_Pvt.Create_Pc.end',
126 'Exiting FPA_PlanningCycle_Pvt.Create_Pc'
127 );
128 END IF;
129
130 EXCEPTION
131 WHEN OTHERS THEN
132 IF l_pc_s_csr%ISOPEN THEN
133 CLOSE l_pc_s_csr;
134 END IF;
135 IF l_language_csr%ISOPEN THEN
136 CLOSE l_language_csr;
137 END IF;
138
139 ROLLBACK;
140 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
141
142 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
143 FND_LOG.String
144 (
145 FND_LOG.LEVEL_ERROR,
146 'fpa.sql.FPA_PlanningCycle_Pvt.Create_Pc',
147 SQLERRM
148 );
149 END IF;
150
151 FND_MSG_PUB.count_and_get
152 (
153 p_count => x_msg_count,
154 p_data => x_msg_data
155 );
156 RAISE;
157
158 END Create_Pc;
159
160
161 PROCEDURE Update_Pc_Invest_Mix
162 ( p_api_version IN NUMBER,
163 p_inv_matrix IN fpa_pc_inv_matrix_tbl,
164 x_return_status OUT NOCOPY VARCHAR2,
165 x_msg_data OUT NOCOPY VARCHAR2,
166 x_msg_count OUT NOCOPY NUMBER )
167 IS
168 l_pc_catg number;
169 l_aw_call varchar2(50);
170 BEGIN
171 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
172 FND_LOG.String
173 (
174 FND_LOG.LEVEL_PROCEDURE,
175 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Invest_Mix.begin',
176 'Entering FPA_PlanningCycle_Pvt.Update_Pc_Invest_Mix'
177 );
178 END IF;
179
180 --DBMS_OUTPUT.put_line(' ..Inside if in Update_Pc_Invest_Mix...');
181
182 --DBMS_OUTPUT.put_line(' ..p_inv_matrix(1).planning_cycle=' || p_inv_matrix(1).planning_cycle );
183
184 --DBMS_OUTPUT.put_line(' ..p_inv_matrix(1).pc_category from input parameter =' || p_inv_matrix(1).pc_category );
185
186
187 BEGIN
188 SELECT distinct(pc_category) INTO l_pc_catg
189 FROM fpa_aw_pc_inv_matrices_v
190 WHERE planning_cycle = p_inv_matrix(1).planning_cycle;
191 EXCEPTION when others then
192 null;
193 --DBMS_OUTPUT.put_line(' ..p_inv_matrix(1).pc_category from fpa view =' || l_pc_catg );
194 END;
195
196 --DBMS_OUTPUT.put_line(' ..outside .. p_inv_matrix(1).pc_category from fpa view =' || l_pc_catg );
197
198 IF p_inv_matrix(1).pc_category = l_pc_catg THEN
199 NULL;
200 ELSE
201 --DBMS_OUTPUT.put_line(' ..... STARTING EXECUTION OF AW PROGRAMS ..... ');
202 dbms_aw.execute('CALL set_pc_class_code_valid_prg('|| p_inv_matrix(1).planning_cycle ||' ,'|| p_inv_matrix(1).pc_category ||')');
203 END IF;
204
205 -- Update the InvestMix
206 -- Limit PCID
207 dbms_aw.execute('LMT planning_cycle_d TO ' || p_inv_matrix(1).planning_cycle );
208
209 FOR i IN p_inv_matrix.FIRST..p_inv_matrix.LAST
210 LOOP
211 --DBMS_OUTPUT.put_line(' ..Inside For loop...p_inv_matrix(i).class_code=' || p_inv_matrix(i).class_code );
212 --DBMS_OUTPUT.put_line(' ..Inside For loop...p_inv_matrix(i).investment_mix=' || p_inv_matrix(i).investment_mix );
213
214 -- Limit Class_code_d
215 dbms_aw.execute('LMT class_code_d TO ' || p_inv_matrix(i).class_code );
216 -- Set the funds percentage for each class code
217 dbms_aw.execute('pc_class_code_target_mix_m = ' || p_inv_matrix(i).investment_mix );
218
219 END LOOP;
220
221
222 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
223 FND_LOG.String
224 (
225 FND_LOG.LEVEL_PROCEDURE,
226 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Invest_Mix.end',
227 'Exiting FPA_PlanningCycle_Pvt.Update_Pc_Invest_Mix'
228 );
229 END IF;
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 --DBMS_OUTPUT.put_line('...Inside Object level API EXCEPTION block...');
234
235 ROLLBACK;
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
237
238 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
239 FND_LOG.String
240 (
241 FND_LOG.LEVEL_ERROR,
242 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Invest_Mix',
243 SQLERRM
244 );
245 END IF;
246
247 FND_MSG_PUB.count_and_get
248 (
249 p_count => x_msg_count,
250 p_data => x_msg_data
251 );
252 RAISE;
253
254 END Update_Pc_Invest_Mix;
255
256 PROCEDURE Update_Pc_Fin_Targets
257 ( p_api_version IN NUMBER,
258 p_fin_targets_tbl IN fpa_pc_fin_targets_tbl,
259 x_return_status OUT NOCOPY VARCHAR2,
260 x_msg_data OUT NOCOPY VARCHAR2,
261 x_msg_count OUT NOCOPY NUMBER )
262 IS
263 l_pc_catg number;
264 l_aw_call varchar2(50);
265 l_pc_disply_factor VARCHAR2(30);
266 l_target_name VARCHAR2(80);
267
268 BEGIN
269
270 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
271 FND_LOG.String
272 (
273 FND_LOG.LEVEL_PROCEDURE,
274 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Fin_Targets.begin',
275 'Entering FPA_PlanningCycle_Pvt.Update_Pc_Fin_Targets'
276 );
277 END IF;
278
279 --DBMS_OUTPUT.put_line(' ..Inside if in Update_Pc_Fin_Targets...');
280
281 --DBMS_OUTPUT.put_line(' ..p_fin_targets_tbl(1).planning_cycle=' || p_fin_targets_tbl(1).planning_cycle );
282
283 --DBMS_OUTPUT.put_line(' ..p_fin_targets_tbl(1).pc_category from input parameter =' || p_fin_targets_tbl(1).financial_metrics );
284
285 -- get the display factor, that will be used
286 -- to multiply with the funds avaialble.
287 SELECT b.PC_DISPLAY_FACTOR
288 INTO l_pc_disply_factor
289 FROM fpa_aw_pc_disc_funds_v b
290 WHERE b.planning_cycle = p_fin_targets_tbl(1).planning_cycle;
291
292
293
294 -- Update the financial targets
295 -- Limit PCID
296 dbms_aw.execute('LMT planning_cycle_d TO ' || p_fin_targets_tbl(1).planning_cycle );
297
298 FOR i IN p_fin_targets_tbl.FIRST..p_fin_targets_tbl.LAST
299 LOOP
300 --DBMS_OUTPUT.put_line(' ..Inside For loop...p_fin_targets_tbl(i).financial_target_from=' || p_fin_targets_tbl(i).financial_target_from );
301 --DBMS_OUTPUT.put_line(' ..Inside For loop...p_fin_targets_tbl(i).financial_target_to=' || p_fin_targets_tbl(i).financial_target_to );
302 -- Check to make sure TO target is greater than FROM target
303 if p_fin_targets_tbl(i).financial_target_from > p_fin_targets_tbl(i).financial_target_to
304 then
305 -- get translatable value of Financial metric in order to display appropriate error.
306 SELECT meaning
307 INTO l_target_name
308 FROM fpa_lookups_v
309 WHERE lookup_type = 'FPA_PC_FIN_TARGETS'
310 AND lookup_code = p_fin_targets_tbl(i).financial_metrics;
311 -- Set error message.
312 FND_MESSAGE.SET_NAME('FPA','FPA_TARGET_FROM_GT_TO');
313 FND_MESSAGE.SET_TOKEN('FINMETRIC', l_target_name);
314 FND_MSG_PUB.ADD;
315 RAISE FND_API.G_EXC_ERROR;
316 end if;
317
318 -- Limit financial_metrics_d
319 dbms_aw.execute('LMT financial_metrics_d TO ''' || p_fin_targets_tbl(i).financial_metrics || '''' );
320 -- Set the targets from for each fin matric
321 dbms_aw.execute('pc_fintargets_range_from_m = ' || p_fin_targets_tbl(i).financial_target_from*l_pc_disply_factor );
322 -- Set the targets to for each fin matric
323 dbms_aw.execute('pc_fintargets_range_to_m = ' || p_fin_targets_tbl(i).financial_target_to*l_pc_disply_factor );
324 END LOOP;
325
326
327 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
328 FND_LOG.String
329 (
330 FND_LOG.LEVEL_PROCEDURE,
331 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Fin_Targets.end',
332 'Exiting FPA_PlanningCycle_Pvt.Update_Pc_Fin_Targets'
333 );
334 END IF;
335
336 EXCEPTION
337 WHEN FND_API.G_EXC_ERROR THEN
338 RAISE FND_API.G_EXC_ERROR;
339 WHEN OTHERS THEN
340 --DBMS_OUTPUT.put_line('...Inside Object level API EXCEPTION block...');
341
342 ROLLBACK;
343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
344
345 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
346 FND_LOG.String
347 (
348 FND_LOG.LEVEL_ERROR,
349 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Fin_Targets',
350 SQLERRM
351 );
352 END IF;
353
354 FND_MSG_PUB.count_and_get
355 (
356 p_count => x_msg_count,
357 p_data => x_msg_data
358 );
359 RAISE;
360
361 END Update_Pc_Fin_Targets;
362
363 PROCEDURE Update_Pc_Inv_Criteria_Data
364 ( p_api_version IN NUMBER,
365 p_inv_crit_tbl IN fpa_pc_inv_criteria_tbl,
366 x_return_status OUT NOCOPY VARCHAR2,
367 x_msg_data OUT NOCOPY VARCHAR2,
368 x_msg_count OUT NOCOPY NUMBER )
369 IS
370 l_pc_obj varchar2(3);
371
372 BEGIN
373
374 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
375 FND_LOG.String
376 ( FND_LOG.LEVEL_PROCEDURE,
377 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data.begin',
378 'Entering FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data'
379 );
380 END IF;
381
382 --DBMS_OUTPUT.put_line(' ..Inside if in Update_Pc_Inv_Criteria_Data...');
383
384 --DBMS_OUTPUT.put_line(' ..p_inv_crit_tbl(1).planning_cycle=' || p_inv_crit_tbl(1).planning_cycle );
385 --DBMS_OUTPUT.put_line(' ..p_inv_crit_tbl(1).pc_project_score_source=' || p_inv_crit_tbl(1).pc_project_score_source );
386 --DBMS_OUTPUT.put_line(' ..p_inv_crit_tbl(1).pc_project_score_scale=' || p_inv_crit_tbl(1).pc_project_score_scale );
387
388 -- Update the investment criteria data
389 -- Limit PCID
390 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
391 FND_LOG.String
392 ( FND_LOG.LEVEL_PROCEDURE,
393 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data',
394 'Limit pc dimension to ' || p_inv_crit_tbl(1).planning_cycle || ' value.'
395 );
396 END IF;
397 dbms_aw.execute('LMT planning_cycle_d TO ' || p_inv_crit_tbl(1).planning_cycle);
398
399 -- Set the score source
400 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
401 FND_LOG.String
402 ( FND_LOG.LEVEL_PROCEDURE,
403 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data',
404 'Setting score_type_pc_r to: ' || p_inv_crit_tbl(1).pc_project_score_source || ' value.'
405 );
406 END IF;
407 dbms_aw.execute('score_type_pc_r = ''' || p_inv_crit_tbl(1).pc_project_score_source || '''');
408
409 -- Set the score scale
410 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
411 FND_LOG.String
412 ( FND_LOG.LEVEL_PROCEDURE,
413 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data',
414 'Setting pc_project_score_scale_m to: ' || p_inv_crit_tbl(1).pc_project_score_scale || ' value.'
415 );
416 END IF;
417 dbms_aw.execute('pc_project_score_scale_m = ' || p_inv_crit_tbl(1).pc_project_score_scale );
418
419
420 FOR i IN p_inv_crit_tbl.FIRST..p_inv_crit_tbl.LAST
421 LOOP
422
423 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
424 FND_LOG.String
425 ( FND_LOG.LEVEL_PROCEDURE,
426 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data',
427 'Debugging, we are in ' || i || ' value.'
428 );
429 END IF;
430
431
432 -- Limit strategic_obj_d
433 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
434 FND_LOG.String
435 ( FND_LOG.LEVEL_PROCEDURE,
436 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data',
437 'Limit strategic_obj_d dimension to ' || p_inv_crit_tbl(i).investment_criteria || ' value.'
438 );
439 END IF;
440 dbms_aw.execute('LMT strategic_obj_d TO ' || p_inv_crit_tbl(i).investment_criteria );
441
442 --DBMS_OUTPUT.put_line(' ..Inside For loop...after setting p_inv_crit_tbl(i).investment_criteria' );
443
444 -- Set the weights for each strategic_obj
445 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
446 FND_LOG.String
447 ( FND_LOG.LEVEL_PROCEDURE,
448 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data',
449 'Setting pc_strategic_obj_weights_m: ' || p_inv_crit_tbl(i).pc_inv_criteria_weight || ' value.'
450 );
451 END IF;
452 dbms_aw.execute('pc_strategic_obj_weights_m = ' || p_inv_crit_tbl(i).pc_inv_criteria_weight );
453
454 --DBMS_OUTPUT.put_line(' ..Inside For loop...after setting p_inv_crit_tbl(i).pc_inv_criteria_weight' );
455
456 -- Set the target from for each strategic_obj
457 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
458 FND_LOG.String
459 ( FND_LOG.LEVEL_PROCEDURE,
460 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data',
461 'Setting pc_obj_wscore_targets_from_m: ' || p_inv_crit_tbl(i).pc_inv_crit_score_target_from || ' value.'
462 );
463 END IF;
464
465 -- If the target_from value from UI is null, update it in AW with NA
466
467 IF p_inv_crit_tbl(i).pc_inv_crit_score_target_from IS NULL THEN
468 dbms_aw.execute('pc_obj_wscore_targets_from_m = NA');
469 ELSE
470 dbms_aw.execute('pc_obj_wscore_targets_from_m = ' || p_inv_crit_tbl(i).pc_inv_crit_score_target_from );
471 END IF;
472
473 --DBMS_OUTPUT.put_line(' ..Inside For loop...after setting p_inv_crit_tbl(i).pc_inv_crit_score_target_from' );
474
475 -- Set the target to for each strategic_obj
476 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
477 FND_LOG.String
478 ( FND_LOG.LEVEL_PROCEDURE,
479 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data',
480 'Setting pc_obj_wscore_targets_to_m: ' || p_inv_crit_tbl(i).pc_inv_crit_score_target_to || ' value.'
481 );
482 END IF;
483 -- If the target_to value from UI is null, update it in AW with NA
484
485 IF p_inv_crit_tbl(i).pc_inv_crit_score_target_to IS NULL THEN
486 dbms_aw.execute('pc_obj_wscore_targets_to_m = NA');
487 ELSE
488 dbms_aw.execute('pc_obj_wscore_targets_to_m = ' || p_inv_crit_tbl(i).pc_inv_crit_score_target_to );
489 END IF;
490 --DBMS_OUTPUT.put_line(' ..Inside For loop...after setting p_inv_crit_tbl(i).pc_inv_crit_score_target_to' );
491
492 END LOOP;
493
494 /*
495 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
496 FND_LOG.String
497 ( FND_LOG.LEVEL_PROCEDURE,
498 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data',
499 'Calling AW program: set_pc_invest_criteria_prg( ' || p_inv_crit_tbl(1).planning_cycle || ').'
500 );
501 END IF;
502 dbms_aw.execute('call set_pc_invest_criteria_prg(' || p_inv_crit_tbl(1).planning_cycle || ')');
503 */
504
505
506 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
507 FND_LOG.String
508 (
509 FND_LOG.LEVEL_PROCEDURE,
510 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data.end',
511 'Exiting FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data'
512 );
513 END IF;
514
515 EXCEPTION
516 WHEN OTHERS THEN
517 --DBMS_OUTPUT.put_line('...Inside Object level API EXCEPTION block...');
518
519 ROLLBACK;
520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
521
522 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
523 FND_LOG.String
524 (
525 FND_LOG.LEVEL_ERROR,
526 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Inv_Criteria_Data',
527 SQLERRM
528 );
529 END IF;
530
531 FND_MSG_PUB.count_and_get
532 (
533 p_count => x_msg_count,
534 p_data => x_msg_data
535 );
536 RAISE;
537
538 END Update_Pc_Inv_Criteria_Data;
539
540
541 PROCEDURE Update_Pc_Desc_Fields
542 ( p_api_version IN NUMBER,
543 p_pc_all_obj IN fpa_pc_all_obj,
544 x_return_status OUT NOCOPY VARCHAR2,
545 x_msg_data OUT NOCOPY VARCHAR2,
546 x_msg_count OUT NOCOPY NUMBER )
547 IS
548
549 BEGIN
550
551 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
552 FND_LOG.String
553 (
554 FND_LOG.LEVEL_PROCEDURE,
555 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Desc_Fields.begin',
556 'Entering FPA_PlanningCycle_Pvt.Update_Pc_Desc_Fields'
557 );
558 END IF;
559
560 -- Update name and description
561
562 --DBMS_OUTPUT.put_line('Before Updating Update_Pc_Desc_Fields...');
563
564 UPDATE FPA_OBJECTS_TL
565 SET name = p_pc_all_obj.pc_desc_fields.name
566 ,description = p_pc_all_obj.pc_desc_fields.description
567 ,SOURCE_LANG = userenv('LANG')
568 ,last_update_date = sysdate()
569 WHERE id = p_pc_all_obj.pc_desc_fields.id
570 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG)
571 AND OBJECT = 'PLANNING_CYCLE'; --Added for bug 6142322
572
573 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
574 FND_LOG.String
575 (
576 FND_LOG.LEVEL_PROCEDURE,
577 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Desc_Fields.end',
578 'Exiting FPA_PlanningCycle_Pvt.Update_Pc_Desc_Fields'
579 );
580 END IF;
581
582 EXCEPTION
583 WHEN OTHERS THEN
584 ROLLBACK;
585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
586
587 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
588 FND_LOG.String
589 (
590 FND_LOG.LEVEL_ERROR,
591 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Desc_Fields',
592 SQLERRM
593 );
594 END IF;
595
596 FND_MSG_PUB.count_and_get
597 (
598 p_count => x_msg_count,
599 p_data => x_msg_data
600 );
601 RAISE;
602
603 END Update_Pc_Desc_Fields;
604
605 PROCEDURE Set_Pc_Status
606 ( p_api_version IN NUMBER,
607 p_pc_id IN NUMBER,
608 p_pc_status_code IN VARCHAR2,
609 x_return_status OUT NOCOPY VARCHAR2,
610 x_msg_data OUT NOCOPY VARCHAR2,
611 x_msg_count OUT NOCOPY NUMBER )
612 IS
613
614 BEGIN
615 --DBMS_OUTPUT.put_line('Inside Set_Pc_Status....');
616
617 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
618 FND_LOG.String
619 (
620 FND_LOG.LEVEL_PROCEDURE,
621 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Status.begin',
622 'Entering FPA_PlanningCycle_Pvt.Set_Pc_Status'
623 );
624 END IF;
625 --DBMS_OUTPUT.put_line('Before limiting planning_cycle_d to: ' || p_pc_id );
626
627 -- Update the status
628 dbms_aw.execute('LMT planning_cycle_d TO ' || p_pc_id );
629
630 --DBMS_OUTPUT.put_line('Before setting status_pc_r ....');
631 -- Set the new staus
632 dbms_aw.execute('status_pc_r = ''' || p_pc_status_code || '''');
633
634
635 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
636 FND_LOG.String
637 (
638 FND_LOG.LEVEL_PROCEDURE,
639 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Status.end',
640 'Exiting FPA_PlanningCycle_Pvt.Set_Pc_Status'
641 );
642 END IF;
643
644 EXCEPTION
645 WHEN OTHERS THEN
646 ROLLBACK;
647 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
648
649 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
650 FND_LOG.String
651 (
652 FND_LOG.LEVEL_ERROR,
653 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Status',
654 SQLERRM
655 );
656 END IF;
657
658 FND_MSG_PUB.count_and_get
659 (
660 p_count => x_msg_count,
661 p_data => x_msg_data
662 );
663 RAISE;
664
665 END Set_Pc_Status;
666
667 PROCEDURE Set_Pc_Initiate_Date
668 ( p_api_version IN NUMBER,
669 p_pc_id IN NUMBER,
670 x_return_status OUT NOCOPY VARCHAR2,
671 x_msg_data OUT NOCOPY VARCHAR2,
672 x_msg_count OUT NOCOPY NUMBER )
673 IS
674 BEGIN
675 --DBMS_OUTPUT.put_line('Inside Set_Pc_Initiate_Date....');
676
677 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
678 FND_LOG.String
679 (
680 FND_LOG.LEVEL_PROCEDURE,
681 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Initiate_Date.begin',
682 'Entering FPA_PlanningCycle_Pvt.Set_Pc_Initiate_Date'
683 );
684 END IF;
685 --DBMS_OUTPUT.put_line('Before limiting planning_cycle_d to: ' || p_pc_id );
686
687 -- Limit the PC ID
688 dbms_aw.execute('LMT planning_cycle_d TO ' || p_pc_id );
689
690 -- Set the new Initiate Date
691
692 BEGIN
693 -- Add new Day to the Day_d dimension
694 /* dbms_aw.execute('MAINTAIN day_d ADD '''
695 || to_char(to_date(p_pc_init_date, 'DD-MON-YYYY'), 'DDMONYYYY' || ''''));
696 */
697 dbms_aw.execute('MAINTAIN day_d ADD ''' || to_char(SYSDATE, 'DDMONYYYY') || '''' );
698 EXCEPTION
699 WHEN OTHERS THEN
700 --DBMS_OUTPUT.put_line(SQLCODE);
701 -- Check for existing dim values, if it already exists during MAINTAIN, then ignore it.
702 IF SQLCODE = -34034 THEN
703 NULL;
704 END IF;
705 END;
706 -- Set the new Initiate Date
707 --DBMS_OUTPUT.PUT_LINE('....pc_initiate_date:' || SYSDATE);
708
709
710
711 dbms_aw.execute('pc_initiate_date_r = ''' || to_char(SYSDATE, 'DDMONYYYY') || '''');
712
713 /*
714 dbms_aw.execute('pc_initiate_date_r = '''
715 || to_char(to_date(p_pc_init_date, 'DD-MON-YYYY'), 'DDMONYYYY' || ''''));
716 */
717
718 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
719 FND_LOG.String
720 (
721 FND_LOG.LEVEL_PROCEDURE,
722 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Initiate_Date.end',
723 'Exiting FPA_PlanningCycle_Pvt.Set_Pc_Initiate_Date'
724 );
725 END IF;
726
727 EXCEPTION
728 WHEN OTHERS THEN
729 ROLLBACK;
730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
731
732 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
733 FND_LOG.String
734 (
735 FND_LOG.LEVEL_ERROR,
736 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Initiate',
737 SQLERRM
738 );
739 END IF;
740
741 FND_MSG_PUB.count_and_get
742 (
743 p_count => x_msg_count,
744 p_data => x_msg_data
745 );
746 RAISE;
747
748 END Set_Pc_Initiate_Date;
749
750
751 PROCEDURE Update_Pc_Class_Category
752 ( p_api_version IN NUMBER,
753 p_pc_id IN NUMBER,
754 p_catg_id IN NUMBER,
755 x_return_status OUT NOCOPY VARCHAR2,
756 x_msg_data OUT NOCOPY VARCHAR2,
757 x_msg_count OUT NOCOPY NUMBER )
758 IS
759
760 BEGIN
761 --DBMS_OUTPUT.put_line('Inside Update_Pc_Class_Category....');
762
763 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
764 FND_LOG.String
765 (
766 FND_LOG.LEVEL_PROCEDURE,
767 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Class_Category.begin',
768 'Entering FPA_PlanningCycle_Pvt.Update_Pc_Class_Category'
769 );
770 END IF;
771 --DBMS_OUTPUT.put_line('Before limiting planning_cycle_d to: ' || p_pc_id );
772
773 -- Update the Basic Info
774 -- Limit PCID
775 dbms_aw.execute('LMT planning_cycle_d TO ' || p_pc_id );
776
777 --DBMS_OUTPUT.put_line('Before setting pc_class_code_m ....');
778 -- Set the new class category
779 dbms_aw.execute('pc_category_m = ''' || p_catg_id || '''');
780
781 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
782 FND_LOG.String
783 (
784 FND_LOG.LEVEL_PROCEDURE,
785 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Class_Category.end',
786 'Exiting FPA_PlanningCycle_Pvt.Update_Pc_Class_Category'
787 );
788 END IF;
789
790 EXCEPTION
791 WHEN OTHERS THEN
792 ROLLBACK;
793 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
794
795 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
796 FND_LOG.String
797 (
798 FND_LOG.LEVEL_ERROR,
799 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Class_Category',
800 SQLERRM
801 );
802 END IF;
803
804 FND_MSG_PUB.count_and_get
805 (
806 p_count => x_msg_count,
807 p_data => x_msg_data
808 );
809 RAISE;
810
811 END Update_Pc_Class_Category;
812
813 PROCEDURE Update_Pc_Calendar
814 ( p_api_version IN NUMBER,
815 p_pc_info IN fpa_pc_info_obj,
816 x_return_status OUT NOCOPY VARCHAR2,
817 x_msg_data OUT NOCOPY VARCHAR2,
818 x_msg_count OUT NOCOPY NUMBER )
819 IS
820
821 BEGIN
822 --DBMS_OUTPUT.put_line('Inside Update_Pc_Calendar....');
823
824 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
825 FND_LOG.String
826 (
827 FND_LOG.LEVEL_PROCEDURE,
828 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Calendar.begin',
829 'Entering FPA_PlanningCycle_Pvt.Update_Pc_Calendar'
830 );
831 END IF;
832 --DBMS_OUTPUT.put_line('Before limiting planning_cycle_d to: ' || p_pc_info.planning_cycle );
833
834 -- Update the Calendar
835 -- Limit PCID
836 dbms_aw.execute('LMT planning_cycle_d TO ' || p_pc_info.planning_cycle );
837
838 --DBMS_OUTPUT.put_line('Before setting Update_Pc_Calendar ....');
839 BEGIN
840 -- Add new calendar to the dimension
841 dbms_aw.execute('MAINTAIN calendar_d ADD ''' || p_pc_info.calendar_name || '''');
842 EXCEPTION
843 WHEN OTHERS THEN
844 --DBMS_OUTPUT.put_line(SQLCODE);
845 -- Check for existing dim values, if it already exists during MAINTAIN, then ignore it.
846 IF SQLCODE = -34034 THEN
847 NULL;
848 END IF;
849 END;
850 -- Set the new calendar
851 dbms_aw.execute('calendar_pc_r = ''' || p_pc_info.calendar_name || '''');
852
853 BEGIN
854 -- Add new Period Type to the dimension
855 dbms_aw.execute('MAINTAIN period_type_d ADD ''' || p_pc_info.period_type || '''');
856 EXCEPTION
857 WHEN OTHERS THEN
858 --DBMS_OUTPUT.put_line(SQLCODE);
859 -- Check for existing dim values, if it already exists during MAINTAIN, then ignore it.
860 IF SQLCODE = -34034 THEN
861 NULL;
862 END IF;
863 END;
864 -- Set the new Period Type
865 dbms_aw.execute('period_type_pc_r = ''' || p_pc_info.period_type || '''');
866
867 BEGIN
868 -- Add new Calendar Period Type to the dimension
869 dbms_aw.execute('MAINTAIN cal_period_type_d ADD ''' || p_pc_info.calendar_name || '.' || p_pc_info.period_type || '''');
870 EXCEPTION
871 WHEN OTHERS THEN
872 --DBMS_OUTPUT.put_line(SQLCODE);
873 -- Check for existing dim values, if it already exists during MAINTAIN, then ignore it.
874 IF SQLCODE = -34034 THEN
875 NULL;
876 END IF;
877 END;
878 -- Set the new Calendar Period Type
879 dbms_aw.execute('cal_period_type_pc_r = ''' || p_pc_info.calendar_name || '.' || p_pc_info.period_type || '''');
880
881 dbms_aw.execute('pc_funding_period_from_m = ''' || p_pc_info.funding_period_from || '''');
882
883 dbms_aw.execute('pc_funding_period_to_m = ''' || p_pc_info.funding_period_to || '''');
884
885 dbms_aw.execute('pc_effective_period_to_m = ''' || p_pc_info.effective_period_to || '''');
886
887 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
888 FND_LOG.String
889 (
890 FND_LOG.LEVEL_PROCEDURE,
891 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Calendar.end',
892 'Exiting FPA_PlanningCycle_Pvt.Update_Pc_Calendar'
893 );
894 END IF;
895
896 EXCEPTION
897 WHEN OTHERS THEN
898 ROLLBACK;
899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
900
901 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
902 FND_LOG.String
903 (
904 FND_LOG.LEVEL_ERROR,
905 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Calendar',
906 SQLERRM
907 );
908 END IF;
909
910 FND_MSG_PUB.count_and_get
911 (
912 p_count => x_msg_count,
913 p_data => x_msg_data
914 );
915 RAISE;
916
917 END Update_Pc_Calendar;
918
919 PROCEDURE Update_Pc_Currency
920 ( p_api_version IN NUMBER,
921 p_pc_info IN fpa_pc_info_obj,
922 x_return_status OUT NOCOPY VARCHAR2,
923 x_msg_data OUT NOCOPY VARCHAR2,
924 x_msg_count OUT NOCOPY NUMBER )
925 IS
926
927 BEGIN
928 --DBMS_OUTPUT.put_line('Inside Update_Pc_Currency....');
929
930 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
931 FND_LOG.String
932 (FND_LOG.LEVEL_PROCEDURE,
933 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Currency.begin',
934 'Entering FPA_PlanningCycle_Pvt.Update_Pc_Currency'
935 );
936 END IF;
937 --DBMS_OUTPUT.put_line('Before limiting planning_cycle_d to: ' || p_pc_info.planning_cycle );
938
939 -- Update the Currency
940 -- Limit PCID
941 dbms_aw.execute('LMT planning_cycle_d TO ' || p_pc_info.planning_cycle );
942
943 --DBMS_OUTPUT.put_line('Before setting Update_Pc_Currency ....');
944 -- Set the new Currency
945
946 dbms_aw.execute('pc_currency_m = ''' || p_pc_info.currency_code || '''');
947
948 dbms_aw.execute('pc_conversion_rate_type_m = ''' || p_pc_info.conversion_rate_type || '''');
949
950 -- BEGIN
951 -- Add new Day to the dimension
952 -- dbms_aw.execute('MAINTAIN day_d ADD ''' || p_pc_info.conversion_rate_date || '''');
953 -- EXCEPTION
954 -- WHEN OTHERS THEN
955 --DBMS_OUTPUT.put_line(SQLCODE);
956 -- Check for existing dim values, if it already exists during MAINTAIN, then ignore it.
957 -- IF SQLCODE = -34034 THEN
958 -- NULL;
959 -- END IF;
960 -- RAISE ; -- need to put this later
961 -- END;
962
963 --DBMS_OUTPUT.PUT_LINE('....conversion_rate_date:' || p_pc_info.conversion_rate_date);
964
965 -- If conversion rate date is not null then set relation to that value
966 -- else set it to todays date.
967 if p_pc_info.conversion_rate_date is not null
968 then
969 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
970 FND_LOG.String
971 (FND_LOG.LEVEL_PROCEDURE,
972 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Currency',
973 'Setting currency_date_pc_r to : ' || to_char(fnd_date.chardate_to_date(p_pc_info.conversion_rate_date), 'DDMONYYYY' || '''') || ' value.'
974 );
975 END IF;
976
977 --Bug 4254274 : Applying to_date function on variable of type character i.e p_pc_info.conversion_rate_date
978 -- was raising error 'literal does not match format string'
979
980 dbms_aw.execute('currency_date_pc_r = '''
981 || to_char(fnd_date.chardate_to_date(p_pc_info.conversion_rate_date), 'DDMONYYYY' || ''''));
982
983 /*dbms_aw.execute('currency_date_pc_r = '''
984 || to_char(to_date(p_pc_info.conversion_rate_date,
985 'DD-MON-YYYY'), 'DDMONYYYY' || ''''));*/
986 else
987 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
988 FND_LOG.String
989 (FND_LOG.LEVEL_PROCEDURE,
990 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Currency',
991 'Setting currency_date_pc_r to sysdate value.'||to_char(sysdate,'DDMONYYYY' || '''')
992 );
993 END IF;
994
995 --Bug 4254274 : Applying hard coded format for to_date function on sysdate which is in nls_date_format
996 -- was raising error 'literal does not match format string'
997
998 dbms_aw.execute('currency_date_pc_r = '''
999 || to_char(sysdate,'DDMONYYYY' || ''''));
1000
1001 /*dbms_aw.execute('currency_date_pc_r = '''
1002 || to_char(to_date(sysdate,
1003 'DD-MON-YYYY'), 'DDMONYYYY' || ''''));*/
1004 end if;
1005
1006 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1007 FND_LOG.String
1008 (
1009 FND_LOG.LEVEL_PROCEDURE,
1010 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Currency.end',
1011 'Exiting FPA_PlanningCycle_Pvt.Update_Pc_Currency'
1012 );
1013 END IF;
1014
1015 EXCEPTION
1016 WHEN OTHERS THEN
1017 ROLLBACK;
1018 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1019
1020 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1021 FND_LOG.String
1022 (
1023 FND_LOG.LEVEL_ERROR,
1024 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Currency',
1025 SQLERRM
1026 );
1027 END IF;
1028
1029 FND_MSG_PUB.count_and_get
1030 (
1031 p_count => x_msg_count,
1032 p_data => x_msg_data
1033 );
1034 RAISE;
1035
1036 END Update_Pc_Currency;
1037
1038
1039 PROCEDURE Update_Pc_Sub_Due_Date
1040 ( p_api_version IN NUMBER,
1041 p_pc_info IN fpa_pc_info_obj,
1042 x_return_status OUT NOCOPY VARCHAR2,
1043 x_msg_data OUT NOCOPY VARCHAR2,
1044 x_msg_count OUT NOCOPY NUMBER )
1045 IS
1046
1047 BEGIN
1048 --DBMS_OUTPUT.put_line('Inside Update_Pc_Sub_Due_Date....');
1049
1050 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1051 FND_LOG.String
1052 (
1053 FND_LOG.LEVEL_PROCEDURE,
1054 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Sub_Due_Date.begin',
1055 'Entering FPA_PlanningCycle_Pvt.Update_Pc_Sub_Due_Date'
1056 );
1057 END IF;
1058 --DBMS_OUTPUT.put_line('Before limiting planning_cycle_d to: ' || p_pc_info.planning_cycle );
1059
1060 -- Update the Currency
1061 -- Limit PCID
1062 dbms_aw.execute('LMT planning_cycle_d TO ' || p_pc_info.planning_cycle );
1063
1064 --DBMS_OUTPUT.put_line('Before setting submission_due_date ....');
1065
1066 BEGIN
1067 -- Add new Day to the dimension
1068 dbms_aw.execute('MAINTAIN day_d ADD ''' || p_pc_info.submission_due_date || '''');
1069 EXCEPTION
1070 WHEN OTHERS THEN
1071 --DBMS_OUTPUT.put_line(SQLCODE);
1072 -- Check for existing dim values, if it already exists during MAINTAIN, then ignore it.
1073 IF SQLCODE = -34034 THEN
1074 NULL;
1075 END IF;
1076 -- RAISE ; -- need to put this later
1077 END;
1078
1079 --DBMS_OUTPUT.PUT_LINE('....submission_due_date:' || p_pc_info.submission_due_date);
1080
1081 --Bug 4254274 : Applying to_date function on variable of type character i.e p_pc_info.submission_due_date
1082 -- was raising error 'literal does not match format string'
1083
1084 dbms_aw.execute('pc_submission_due_date_r = '''
1085 || to_char(fnd_date.chardate_to_date(p_pc_info.submission_due_date), 'DDMONYYYY' || ''''));
1086
1087 /*dbms_aw.execute('pc_submission_due_date_r = '''
1088 || to_char(to_date(p_pc_info.submission_due_date,
1089 'DD-MON-YYYY'), 'DDMONYYYY' || ''''));*/
1090
1091 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1092 FND_LOG.String
1093 (
1094 FND_LOG.LEVEL_PROCEDURE,
1095 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Sub_Due_Date.end',
1096 'Exiting FPA_PlanningCycle_Pvt.Update_Pc_Sub_Due_Date'
1097 );
1098 END IF;
1099
1100 EXCEPTION
1101 WHEN OTHERS THEN
1102 ROLLBACK;
1103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1104
1105 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1106 FND_LOG.String
1107 (
1108 FND_LOG.LEVEL_ERROR,
1109 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Sub_Due_Date',
1110 SQLERRM
1111 );
1112 END IF;
1113
1114 FND_MSG_PUB.count_and_get
1115 (
1116 p_count => x_msg_count,
1117 p_data => x_msg_data
1118 );
1119 RAISE;
1120
1121 END Update_Pc_Sub_Due_Date;
1122
1123
1124
1125 PROCEDURE Update_Pc_Discount_funds
1126 ( p_api_version IN NUMBER,
1127 p_disc_funds IN fpa_pc_discount_obj,
1128 x_return_status OUT NOCOPY VARCHAR2,
1129 x_msg_data OUT NOCOPY VARCHAR2,
1130 x_msg_count OUT NOCOPY NUMBER )
1131 IS
1132
1133 BEGIN
1134 --DBMS_OUTPUT.put_line('Inside Update_Pc_Discount_funds....');
1135
1136 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1137 FND_LOG.String
1138 (
1139 FND_LOG.LEVEL_PROCEDURE,
1140 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Discount_funds.begin',
1141 'Entering FPA_PlanningCycle_Pvt.Update_Pc_Discount_funds'
1142 );
1143 END IF;
1144
1145 --DBMS_OUTPUT.put_line('Before limiting planning_cycle_d to: ' || p_disc_funds.planning_cycle );
1146
1147 -- Update the Discount
1148 -- Limit PCID
1149 dbms_aw.execute('LMT planning_cycle_d TO ' || p_disc_funds.planning_cycle);
1150
1151 --DBMS_OUTPUT.put_line('Before setting pc_discount_rate ....'|| p_disc_funds.pc_discount_rate);
1152 -- Set the new pc_discount_rate
1153 dbms_aw.execute('pc_discount_rate_m = ' || p_disc_funds.pc_discount_rate/100);
1154
1155 --DBMS_OUTPUT.put_line('Before setting pc funding....' || p_disc_funds.pc_funding);
1156
1157 -- Set the new funds
1158 dbms_aw.execute('pc_funding_m = ' || p_disc_funds.pc_funding );
1159
1160 --DBMS_OUTPUT.put_line('Before setting factor_d and factor_pc_r....' || p_disc_funds.pc_display_factor);
1161
1162 dbms_aw.execute('factor_pc_r = ''' || p_disc_funds.pc_display_factor || '''' );
1163
1164
1165 --DBMS_OUTPUT.put_line('Outside IF p_disc_funds IS NOT NULL .... ');
1166
1167
1168 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1169 FND_LOG.String
1170 (
1171 FND_LOG.LEVEL_PROCEDURE,
1172 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Discount_funds.end',
1173 'Exiting FPA_PlanningCycle_Pvt.Update_Pc_Discount_funds'
1174 );
1175 END IF;
1176
1177 EXCEPTION
1178 WHEN OTHERS THEN
1179 ROLLBACK;
1180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1181
1182 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1183 FND_LOG.String
1184 (
1185 FND_LOG.LEVEL_ERROR,
1186 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Discount_funds',
1187 SQLERRM
1188 );
1189 END IF;
1190
1191 FND_MSG_PUB.count_and_get
1192 (
1193 p_count => x_msg_count,
1194 p_data => x_msg_data
1195 );
1196 RAISE;
1197
1198 END Update_Pc_Discount_funds;
1199
1200
1201 FUNCTION Check_Pc_Name
1202 ( p_api_version IN NUMBER,
1203 p_portfolio_id IN NUMBER,
1204 p_pc_name IN VARCHAR2,
1205 p_pc_id IN NUMBER,
1206 x_return_status OUT NOCOPY VARCHAR2,
1207 x_msg_data OUT NOCOPY VARCHAR2,
1208 x_msg_count OUT NOCOPY NUMBER)
1209 RETURN number
1210
1211 IS
1212
1213 l_pcName_Count NUMBER;
1214
1215 BEGIN
1216
1217 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1218 FND_LOG.String
1219 (
1220 FND_LOG.LEVEL_PROCEDURE,
1221 'fpa.sql.FPA_PlanningCycle_Pvt.Check_Pc_Name.begin',
1222 'Entering FPA_PlanningCycle_Pvt.Check_Pc_Name'
1223 );
1224 END IF;
1225
1226 IF (p_pc_id is null) THEN
1227 SELECT count(p.name)
1228 INTO l_pcName_Count
1229 FROM fpa_aw_pc_info_v a, fpa_pcs_vl p
1230 WHERE a.planning_cycle = p.planning_cycle
1231 AND portfolio = p_portfolio_id
1232 AND p.name = p_pc_name ;
1233 --DBMS_OUTPUT.put_line('Inside pc_id = null');
1234 ELSE
1235 SELECT count(p.name)
1236 INTO l_pcName_Count
1237 FROM fpa_aw_pc_info_v a, fpa_pcs_vl p
1238 WHERE a.planning_cycle = p.planning_cycle
1239 AND portfolio = p_portfolio_id
1240 AND p.name = p_pc_name
1241 AND a.planning_cycle <> p_pc_id ;
1242 END IF;
1243
1244 RETURN l_pcName_Count;
1245
1246 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE THEN
1247 FND_LOG.String
1248 (
1249 FND_LOG.LEVEL_PROCEDURE,
1250 'fpa.sql.FPA_PlanningCycle_Pvt.Check_Pc_Name.end',
1251 'Exiting FPA_PlanningCycle_Pvt.Check_Pc_Name'
1252 );
1253 END IF;
1254
1255 EXCEPTION
1256 WHEN OTHERS THEN
1257 ROLLBACK;
1258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1259
1260 IF FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR THEN
1261 FND_LOG.String
1262 (
1263 FND_LOG.LEVEL_ERROR,
1264 'fpa.sql.FPA_PlanningCycle_Pvt.Check_Pc_Name',
1265 SQLERRM
1266 );
1267 END IF;
1268
1269 FND_MSG_PUB.count_and_get
1270 (
1271 p_count => x_msg_count,
1272 p_data => x_msg_data
1273 );
1274 RAISE;
1275
1276 END Check_Pc_Name;
1277
1278
1279 PROCEDURE Pa_Distrb_Lists_Insert_Row (
1280 p_api_version IN NUMBER,
1281 p_distr_list IN fpa_pc_distr_list_obj,
1282 p_list_id IN OUT NOCOPY NUMBER,
1283 x_return_status OUT NOCOPY VARCHAR2,
1284 x_msg_data OUT NOCOPY VARCHAR2,
1285 x_msg_count OUT NOCOPY NUMBER )
1286
1287 IS
1288 l_list_id NUMBER;
1289 l_obj_name VARCHAR2(80);
1290 BEGIN
1291 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1292 FND_LOG.String
1293 (
1294 FND_LOG.LEVEL_PROCEDURE,
1295 'fpa.sql.FPA_Process_Pvt.Pa_Distrb_Lists_Insert_Row.begin',
1296 'Entering FPA_Process_Pvt.Pa_Distrb_Lists_Insert_Row'
1297 );
1298 END IF;
1299
1300 l_obj_name := p_distr_list.name || '_' ||p_distr_list.object_id;
1301
1302 PA_DISTRIBUTION_LISTS_PKG.INSERT_ROW
1303 (
1304 P_LIST_ID => l_list_id,
1305 P_NAME => l_obj_name,
1306 P_DESCRIPTION => p_distr_list.description,
1307 P_RECORD_VERSION_NUMBER => NULL,
1308 P_CREATED_BY => fnd_global.user_id,
1309 P_CREATION_DATE => sysdate,
1310 P_LAST_UPDATED_BY => fnd_global.user_id,
1311 P_LAST_UPDATE_DATE => sysdate,
1312 P_LAST_UPDATE_LOGIN => fnd_global.user_id
1313 );
1314
1315 p_list_id := l_list_id;
1316
1317
1318 PA_OBJECT_DIST_LISTS_PKG.INSERT_ROW
1319 (
1320 P_LIST_ID => l_list_id,
1321 P_OBJECT_TYPE => p_distr_list.object_type,
1322 P_OBJECT_ID => p_distr_list.object_id,
1323 P_RECORD_VERSION_NUMBER => NULL,
1324 P_CREATED_BY => fnd_global.user_id,
1325 P_CREATION_DATE => sysdate,
1326 P_LAST_UPDATED_BY => fnd_global.user_id,
1327 P_LAST_UPDATE_DATE => sysdate,
1328 P_LAST_UPDATE_LOGIN => fnd_global.user_id
1329 );
1330
1331
1332 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1333 FND_LOG.String
1334 (
1335 FND_LOG.LEVEL_PROCEDURE,
1336 'fpa.sql.FPA_Process_Pvt.Pa_Distrb_Lists_Insert_Row.end',
1337 'Exiting FPA_Process_Pvt.Pa_Distrb_Lists_Insert_Row'
1338 );
1339 END IF;
1340
1341
1342 EXCEPTION
1343 WHEN OTHERS THEN
1344 ----DBMS_OUTPUT.put_line('...Inside EXCEPTION block...');
1345 ROLLBACK;
1346
1347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1348
1349 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1350 FND_LOG.String
1351 (
1352 FND_LOG.LEVEL_ERROR,
1353 'fpa.sql.FPA_Process_Pvt.Pa_Distrb_Lists_Insert_Row',
1354 SQLERRM
1355 );
1356 END IF;
1357
1358 FND_MSG_PUB.count_and_get
1359 (
1360 p_count => x_msg_count,
1361 p_data => x_msg_data
1362 );
1363 RAISE;
1364
1365 END Pa_Distrb_Lists_Insert_Row;
1366
1367
1368 PROCEDURE Pa_Dist_List_Items_Update_Row (
1369 p_api_version IN NUMBER,
1370 p_distr_list_items_tbl fpa_pc_distr_list_items_tbl,
1371 x_return_status OUT NOCOPY VARCHAR2,
1372 x_msg_data OUT NOCOPY VARCHAR2,
1373 x_msg_count OUT NOCOPY NUMBER )
1374 IS
1375 l_list_item_id NUMBER;
1376 l_list_id NUMBER;
1377 BEGIN
1378 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1379 FND_LOG.String
1380 (
1381 FND_LOG.LEVEL_PROCEDURE,
1382 'fpa.sql.FPA_Process_Pvt.Pa_Dist_List_Items_Update_Row.begin',
1383 'Entering FPA_Process_Pvt.Pa_Dist_List_Items_Update_Row'
1384 );
1385 END IF;
1386
1387
1388 -- If you don't get the list id from java, get it from pa_object_dist_lists
1389 IF p_distr_list_items_tbl(1).list_id IS NULL THEN
1390 BEGIN
1391 SELECT list_id INTO l_list_id
1392 FROM pa_object_dist_lists
1393 WHERE object_id = p_distr_list_items_tbl(1).planning_cycle
1394 AND object_type = 'PJP_PLANNING_CYCLE';
1395 EXCEPTION
1396 WHEN others THEN
1397 l_list_id := -1;
1398 END;
1399 ELSE
1400 l_list_id := p_distr_list_items_tbl(1).list_id ;
1401 END IF;
1402
1403
1404 IF p_distr_list_items_tbl IS NOT NULL THEN
1405
1406 FOR i IN p_distr_list_items_tbl.FIRST..p_distr_list_items_tbl.LAST
1407 LOOP
1408
1409 IF p_distr_list_items_tbl(i).list_item_id IS NOT NULL THEN
1410
1411 -- call update
1412 --DBMS_OUTPUT.put_line('... before update in Pa_Dist_List_Items_Update_Row...update row..');
1413
1414 PA_DIST_LIST_ITEMS_PKG.Update_Row
1415 (
1416 P_LIST_ITEM_ID => p_distr_list_items_tbl(i).list_item_id,
1417 P_LIST_ID => l_list_id,
1418 P_RECIPIENT_TYPE => p_distr_list_items_tbl(i).recipient_type,
1419 P_RECIPIENT_ID => p_distr_list_items_tbl(i).recipient_id,
1420 P_ACCESS_LEVEL => NULL,
1421 P_MENU_ID => NULL,
1422 P_EMAIL => p_distr_list_items_tbl(i).email_exists,
1423 P_RECORD_VERSION_NUMBER => NULL,
1424 P_LAST_UPDATED_BY => fnd_global.user_id,
1425 P_LAST_UPDATE_DATE => sysdate,
1426 P_LAST_UPDATE_LOGIN => fnd_global.user_id
1427 );
1428
1429 ELSE
1430
1431 -- call insert , set listItemId
1432 --DBMS_OUTPUT.put_line('... before insert in Pa_Dist_List_Items_Update_Row...insert row..');
1433
1434 PA_DIST_LIST_ITEMS_PKG.INSERT_ROW
1435 (
1436 P_LIST_ITEM_ID => l_list_item_id,
1437 P_LIST_ID => l_list_id,
1438 P_RECIPIENT_TYPE => p_distr_list_items_tbl(i).recipient_type,
1439 P_RECIPIENT_ID => p_distr_list_items_tbl(i).recipient_id,
1440 P_ACCESS_LEVEL => NULL,
1441 P_MENU_ID => NULL,
1442 P_EMAIL => p_distr_list_items_tbl(i).email_exists,
1443 P_RECORD_VERSION_NUMBER => NULL,
1444 P_CREATED_BY => fnd_global.user_id,
1445 P_CREATION_DATE => sysdate,
1446 P_LAST_UPDATED_BY => fnd_global.user_id,
1447 P_LAST_UPDATE_DATE => sysdate,
1448 P_LAST_UPDATE_LOGIN => fnd_global.user_id
1449 );
1450
1451
1452 END IF;
1453
1454 END LOOP;
1455
1456 END IF;
1457
1458 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1459 FND_LOG.String
1460 (
1461 FND_LOG.LEVEL_PROCEDURE,
1462 'fpa.sql.FPA_Process_Pvt.Pa_Dist_List_Items_Update_Row.end',
1463 'Exiting FPA_Process_Pvt.Pa_Dist_List_Items_Update_Row'
1464 );
1465 END IF;
1466
1467
1468 EXCEPTION
1469 WHEN OTHERS THEN
1470 --DBMS_OUTPUT.put_line('...Inside EXCEPTION block...');
1471 ROLLBACK;
1472
1473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1474
1475 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1476 FND_LOG.String
1477 (
1478 FND_LOG.LEVEL_ERROR,
1479 'fpa.sql.FPA_Process_Pvt.Pa_Dist_List_Items_Update_Row',
1480 SQLERRM
1481 );
1482 END IF;
1483
1484 FND_MSG_PUB.count_and_get
1485 (
1486 p_count => x_msg_count,
1487 p_data => x_msg_data
1488 );
1489 RAISE;
1490
1491 END Pa_Dist_List_Items_Update_Row;
1492
1493 PROCEDURE Set_Pc_Investment_Criteria (
1494 p_api_version IN NUMBER,
1495 p_pc_id IN NUMBER,
1496 x_return_status OUT NOCOPY VARCHAR2,
1497 x_msg_data OUT NOCOPY VARCHAR2,
1498 x_msg_count OUT NOCOPY NUMBER ) is
1499
1500 l_api_version CONSTANT NUMBER := 1.0;
1501
1502 begin
1503
1504 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1505 fnd_log.string
1506 (
1507 FND_LOG.LEVEL_PROCEDURE,
1508 'fpa.sql.fpa_planningcycle_pvt.Set_Pc_Investment_Criteria.begin',
1509 'Entering fpa_planningcycle_pvt.Set_Pc_Investment_Criteria.'
1510 );
1511 END IF;
1512
1513 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1514 fnd_log.string
1515 (
1516 FND_LOG.LEVEL_PROCEDURE,
1517 'fpa.sql.fpa_planningcycle_pvt.Set_Pc_Investment_Criteria.begin',
1518 'Calling AW Program SET_PC_INVEST_CRITERIA_PRG.'
1519 );
1520 END IF;
1521
1522 dbms_aw.execute('call SET_PC_INVEST_CRITERIA_PRG(' || p_pc_id || ')');
1523
1524 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1525 fnd_log.string
1526 (
1527 FND_LOG.LEVEL_PROCEDURE,
1528 'fpa.sql.fpa_planningcycle_pvt.Set_Pc_Investment_Criteria.end',
1529 'Entering fpa_planningcycle_pvt.Set_Pc_Investment_Criteria.'
1530 );
1531 END IF;
1532
1533 EXCEPTION
1534 WHEN OTHERS THEN
1535 ROLLBACK;
1536 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1537 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1538 fnd_log.string
1539 (
1540 FND_LOG.LEVEL_ERROR,
1541 'fpa.sql.fpa_planningcycle_pvt.Set_Pc_Investment_Criteria',
1542 SQLERRM
1543 );
1544 END IF;
1545 FND_MSG_PUB.count_and_get
1546 (
1547 p_count => x_msg_count,
1548 p_data => x_msg_data
1549 );
1550 RAISE;
1551
1552 end Set_Pc_Investment_Criteria;
1553
1554 PROCEDURE Set_Pc_Approved_Flag
1555 ( p_api_version IN NUMBER,
1556 p_pc_id IN NUMBER,
1557 x_return_status OUT NOCOPY VARCHAR2,
1558 x_msg_data OUT NOCOPY VARCHAR2,
1559 x_msg_count OUT NOCOPY NUMBER )
1560 is
1561
1562 l_api_version CONSTANT NUMBER := 1.0;
1563
1564 begin
1565
1566 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1567 FND_LOG.String
1568 (
1569 FND_LOG.LEVEL_PROCEDURE,
1570 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Approved_Flag.begin',
1571 'Entering FPA_PlanningCycle_Pvt.Set_Pc_Approved_Flag'
1572 );
1573 END IF;
1574
1575 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1576 FND_LOG.String
1577 (
1578 FND_LOG.LEVEL_PROCEDURE,
1579 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Approved_Flag',
1580 'Limiting to passed PC id'
1581 );
1582 END IF;
1583
1584 dbms_aw.execute('push planning_cycle_d');
1585 dbms_aw.execute('push portfolio_d');
1586 dbms_aw.execute('oknullstatus = y');
1587 dbms_aw.execute('limit planning_cycle_d to ' || p_pc_id);
1588
1589 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1590 FND_LOG.String
1591 (
1592 FND_LOG.LEVEL_PROCEDURE,
1593 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Approved_Flag',
1594 'Limiting to portfolio for passed PC id'
1595 );
1596 END IF;
1597 dbms_aw.execute('limit portfolio_d to portfolio_pc_r');
1598
1599 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1600 FND_LOG.String
1601 (
1602 FND_LOG.LEVEL_PROCEDURE,
1603 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Approved_Flag',
1604 'Limiting to planning cycles for the same portfolio'
1605 );
1606 END IF;
1607 dbms_aw.execute('limit planning_cycle_d to portfolio_d');
1608
1609 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1610 FND_LOG.String
1611 (
1612 FND_LOG.LEVEL_PROCEDURE,
1613 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Approved_Flag',
1614 'Reset last approved flag'
1615 );
1616 END IF;
1617 dbms_aw.execute('pc_last_approved_flag_m = na');
1618
1619 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1620 FND_LOG.String
1621 (
1622 FND_LOG.LEVEL_PROCEDURE,
1623 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Approved_Flag',
1624 'Setting last approved flag to passed pc ID'
1625 );
1626 END IF;
1627 dbms_aw.execute('limit planning_cycle_d to ' || p_pc_id);
1628 dbms_aw.execute('pc_last_approved_flag_m = yes');
1629 dbms_aw.execute('pop planning_cycle_d');
1630 dbms_aw.execute('pop portfolio_d');
1631
1632 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1633 FND_LOG.String
1634 (
1635 FND_LOG.LEVEL_PROCEDURE,
1636 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Approved_Flag.end',
1637 'Exiting fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Approved_Flag.end'
1638 );
1639 END IF;
1640
1641 EXCEPTION
1642 WHEN OTHERS THEN
1643 ROLLBACK;
1644 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1645 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1646 fnd_log.string
1647 (
1648 FND_LOG.LEVEL_ERROR,
1649 'fpa.sql.fpa_planningcycle_pvt.Set_Pc_Approved_Flag',
1650 SQLERRM
1651 );
1652 END IF;
1653 FND_MSG_PUB.count_and_get
1654 (
1655 p_count => x_msg_count,
1656 p_data => x_msg_data
1657 );
1658 RAISE;
1659
1660 end Set_Pc_Approved_Flag;
1661
1662 PROCEDURE Set_Pc_Last_Flag
1663 ( p_api_version IN NUMBER,
1664 p_pc_id IN NUMBER,
1665 x_return_status OUT NOCOPY VARCHAR2,
1666 x_msg_data OUT NOCOPY VARCHAR2,
1667 x_msg_count OUT NOCOPY NUMBER )
1668 is
1669
1670 l_api_version CONSTANT NUMBER := 1.0;
1671
1672 begin
1673
1674 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1675 FND_LOG.String
1676 (
1677 FND_LOG.LEVEL_PROCEDURE,
1678 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Last_Flag.begin',
1679 'Entering FPA_PlanningCycle_Pvt.Set_Pc_Last_Flag'
1680 );
1681 END IF;
1682
1683 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1684 FND_LOG.String
1685 (
1686 FND_LOG.LEVEL_PROCEDURE,
1687 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Last_Flag',
1688 'Limiting to passed PC id'
1689 );
1690 END IF;
1691
1692 dbms_aw.execute('push planning_cycle_d');
1693 dbms_aw.execute('push portfolio_d');
1694 dbms_aw.execute('oknullstatus = y');
1695 dbms_aw.execute('limit planning_cycle_d to ' || p_pc_id);
1696
1697 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1698 FND_LOG.String
1699 (
1700 FND_LOG.LEVEL_PROCEDURE,
1701 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Last_Flag',
1702 'Limiting to portfolio for passed PC id'
1703 );
1704 END IF;
1705 dbms_aw.execute('limit portfolio_d to portfolio_pc_r');
1706
1707 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1708 FND_LOG.String
1709 (
1710 FND_LOG.LEVEL_PROCEDURE,
1711 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Last_Flag',
1712 'Limiting to planning cycles for the same portfolio'
1713 );
1714 END IF;
1715 dbms_aw.execute('limit planning_cycle_d to portfolio_d');
1716
1717 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1718 FND_LOG.String
1719 (
1720 FND_LOG.LEVEL_PROCEDURE,
1721 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Last_Flag',
1722 'Reset last approved flag'
1723 );
1724 END IF;
1725 dbms_aw.execute('pc_last_flag_m = na');
1726
1727 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1728 FND_LOG.String
1729 (
1730 FND_LOG.LEVEL_PROCEDURE,
1731 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Last_Flag',
1732 'Setting last approved flag to passed pc ID'
1733 );
1734 END IF;
1735 dbms_aw.execute('limit planning_cycle_d to ' || p_pc_id);
1736 dbms_aw.execute('pc_last_flag_m = yes');
1737 dbms_aw.execute('pop planning_cycle_d');
1738 dbms_aw.execute('pop portfolio_d');
1739
1740 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1741 FND_LOG.String
1742 (
1743 FND_LOG.LEVEL_PROCEDURE,
1744 'fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Last_Flag.end',
1745 'Exiting fpa.sql.FPA_PlanningCycle_Pvt.Set_Pc_Last_Flag'
1746 );
1747 END IF;
1748
1749 EXCEPTION
1750 WHEN OTHERS THEN
1751 ROLLBACK;
1752 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1753 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1754 fnd_log.string
1755 (
1756 FND_LOG.LEVEL_ERROR,
1757 'fpa.sql.fpa_planningcycle_pvt.Set_Pc_Approved_Flag',
1758 SQLERRM
1759 );
1760 END IF;
1761 FND_MSG_PUB.count_and_get
1762 (
1763 p_count => x_msg_count,
1764 p_data => x_msg_data
1765 );
1766 RAISE;
1767
1768 end Set_Pc_Last_Flag;
1769
1770 /*************************************************************************************
1771 *************************************************************************************/
1772
1773 -- Procedure Update_Pc_Annual_Disc_Rates updates annual discount rates for any planning
1774 -- cycle.
1775
1776 PROCEDURE Update_Pc_Annual_Disc_Rates
1777 ( p_api_version IN NUMBER,
1778 p_pc_id IN NUMBER,
1779 p_period IN VARCHAR2,
1780 p_rate IN VARCHAR2,
1781 x_return_status OUT NOCOPY VARCHAR2,
1782 x_msg_data OUT NOCOPY VARCHAR2,
1783 x_msg_count OUT NOCOPY NUMBER )
1784 is
1785
1786 l_api_version CONSTANT NUMBER := 1.0;
1787
1788 begin
1789
1790 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1791 FND_LOG.String
1792 (FND_LOG.LEVEL_PROCEDURE,
1793 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Annual_Disc_Rates.begin',
1794 'Entering FPA_PlanningCycle_Pvt.Update_Pc_Annual_Disc_Rates');
1795 END IF;
1796
1797 IF l_api_version <> p_api_version THEN
1798 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1799 FND_LOG.STRING
1800 ( FND_LOG.LEVEL_PROCEDURE,
1801 'FPA.SQL.FPA_PlanningCycle_Pvt.Update_Pc_Annual_Disc_Rates.',
1802 'Checking API version.');
1803 END IF;
1804 RAISE FND_API.G_EXC_ERROR;
1805 END IF;
1806
1807 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1808 FND_LOG.String
1809 (FND_LOG.LEVEL_PROCEDURE,
1810 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Annual_Disc_Rates.',
1811 'Updating Annual discount rates for the PC in AW.');
1812 END IF;
1813
1814 dbms_aw.execute('lmt planning_cycle_d to ' || p_pc_id);
1815 dbms_aw.execute('lmt time_d to ''' || p_period || '''');
1816 dbms_aw.execute('pc_discount_rate_t_m = ' || p_rate);
1817
1818 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1819 FND_LOG.String
1820 (FND_LOG.LEVEL_PROCEDURE,
1821 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Annual_Disc_Rates.',
1822 'Done Updating Annual discount rates for the PC in AW.');
1823 END IF;
1824
1825 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1826 FND_LOG.String
1827 (FND_LOG.LEVEL_PROCEDURE,
1828 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Annual_Disc_Rates.end',
1829 'Entering FPA_PlanningCycle_Pvt.Update_Pc_Annual_Disc_Rates');
1830 END IF;
1831
1832 EXCEPTION
1833 WHEN FND_API.G_EXC_ERROR THEN
1834 RAISE;
1835 WHEN OTHERS THEN
1836 ROLLBACK;
1837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1838 IF FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1839 FND_LOG.String
1840 ( FND_LOG.LEVEL_ERROR,
1841 'fpa.sql.FPA_PlanningCycle_Pvt.Update_Pc_Annual_Disc_Rates',
1842 SQLERRM);
1843 END IF;
1844
1845 FND_MSG_PUB.count_and_get
1846 ( p_count => x_msg_count,
1847 p_data => x_msg_data);
1848
1849 RAISE;
1850
1851 END Update_Pc_Annual_Disc_Rates;
1852
1853
1854 END FPA_PLANNINGCYCLE_PVT;
1855