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