[Home] [Help]
PACKAGE BODY: APPS.PSB_BUDGET_PERIOD_PVT
Source
1 PACKAGE BODY PSB_BUDGET_PERIOD_PVT AS
2 /* $Header: PSBVPRDB.pls 120.2 2005/07/13 11:29:01 shtripat ship $ */
3 --
4 -- Global Variables
5 --
6
7 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_BUDGET_PERIOD_PVT';
8 G_MONTH CONSTANT VARCHAR2(1) := 'M' ;
9 G_QTR CONSTANT VARCHAR2(1) := 'Q' ;
10 G_SEMI CONSTANT VARCHAR2(1) := 'S' ;
11 G_MONTH_NUM NUMBER := 1 ;
12 G_QTR_NUM NUMBER := 2 ;
13 G_SEMI_NUM NUMBER := 5 ;
14 G_DBUG VARCHAR2(2000);
15
16 /* ----------------------------------------------------------------------- */
17 --
18 -- Private Procedure Declarations
19 --
20 PROCEDURE Check_Duplicate_Year_Types
21 (p_calendar_id IN NUMBER,
22 p_curr_year_type IN NUMBER,
23 p_budget_period_id IN NUMBER,
24 p_return_status OUT NOCOPY VARCHAR2) ;
25 --
26 PROCEDURE Check_Used_In_WS
27 (p_calendar_id IN NUMBER,
28 p_return_status OUT NOCOPY VARCHAR2) ;
29 --
30 PROCEDURE Create_Periods(
31 p_calendar_id IN NUMBER,
32 p_year_id IN NUMBER,
33 p_year_name IN VARCHAR2,
34 p_start_date IN DATE,
35 p_end_date IN DATE,
36 p_budget_period_type IN VARCHAR2,
37 p_calc_period_type IN VARCHAR2,
38 p_return_status OUT NOCOPY VARCHAR2 ,
39 p_msg_count OUT NOCOPY number,
40 p_msg_data OUT NOCOPY varchar2
41 );
42 --
43 PROCEDURE Create_New_Distr_Calc_Period(
44 p_calendar_id IN NUMBER,
45 p_year_id IN NUMBER,
46 p_year_name IN VARCHAR2,
47 p_start_date IN DATE,
48 p_end_date IN DATE,
49 p_budget_period_type IN VARCHAR2,
50 p_calc_period_type IN VARCHAR2,
51 p_update_dist IN VARCHAR2,
52 p_update_calc IN VARCHAR2,
53 p_return_status OUT NOCOPY VARCHAR2 ,
54 p_msg_count OUT NOCOPY number,
55 p_msg_data OUT NOCOPY varchar2
56 );
57 --
58
59 -- Begin Table Handler Procedures
60 --
61 PROCEDURE INSERT_ROW (
62 p_api_version in number,
63 p_init_msg_list in varchar2 := fnd_api.g_false,
64 p_commit in varchar2 := fnd_api.g_false,
65 p_validation_level in number := fnd_api.g_valid_level_full,
66 p_return_status OUT NOCOPY varchar2,
67 p_msg_count OUT NOCOPY number,
68 p_msg_data OUT NOCOPY varchar2,
69 p_rowid IN OUT NOCOPY varchar2,
70 p_budget_period_id in number,
71 p_budget_calendar_id in number,
72 p_description in varchar2,
73 p_start_date in date,
74 p_end_date in date,
75 p_name in varchar2,
76 p_budget_year_type_id in number,
77 p_parent_budget_period_id in number,
78 p_budget_period_type in varchar2,
79 p_period_distribution_type in varchar2,
80 p_calculation_period_type in varchar2,
81 p_attribute1 in varchar2,
82 p_attribute2 in varchar2,
83 p_attribute3 in varchar2,
84 p_attribute4 in varchar2,
85 p_attribute5 in varchar2,
86 p_attribute6 in varchar2,
87 p_attribute7 in varchar2,
88 p_attribute8 in varchar2,
89 p_attribute9 in varchar2,
90 p_attribute10 in varchar2,
91 p_context in varchar2,
92 p_mode in varchar2,
93 p_requery OUT NOCOPY varchar2
94 ) is
95 cursor C is select ROWID from PSB_BUDGET_PERIODS
96 where BUDGET_PERIOD_ID = P_BUDGET_PERIOD_ID;
97 P_LAST_UPDATE_DATE DATE;
98 P_LAST_UPDATED_BY NUMBER;
99 P_LAST_UPDATE_LOGIN NUMBER;
100 --
101 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row' ;
102 l_api_version CONSTANT NUMBER := 1.0 ;
103 l_return_status VARCHAR2(1);
104 --
105 BEGIN
106 --
107 SAVEPOINT Insert_Row ;
108 --
109 if FND_API.to_Boolean (p_init_msg_list) then
110 FND_MSG_PUB.initialize;
111 end if;
112 --
113 p_return_status := FND_API.G_RET_STS_SUCCESS ;
114 --
115 Check_Used_In_WS
116 (p_calendar_id => p_budget_calendar_id,
117 p_return_status => l_return_status);
118
119 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
120 RAISE FND_API.G_EXC_ERROR ;
121 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
123 END IF;
124 --
125 P_LAST_UPDATE_DATE := SYSDATE;
126 if(P_MODE = 'I') then
127 P_LAST_UPDATED_BY := 1;
128 P_LAST_UPDATE_LOGIN := 0;
129 elsif (P_MODE = 'R') then
130 P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
131 if P_LAST_UPDATED_BY is NULL then
132 P_LAST_UPDATED_BY := -1;
133 end if;
134 P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
135 if P_LAST_UPDATE_LOGIN is NULL then
136 P_LAST_UPDATE_LOGIN := -1;
137 end if;
138 else
139 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
140 FND_MSG_PUB.Add ;
141 raise FND_API.G_EXC_ERROR;
142 end if;
143 --
144 Check_Consecutive_Year_Types (
145 p_api_version => 1.0,
146 p_init_msg_list => fnd_api.g_false,
147 p_commit => fnd_api.g_false,
148 p_validation_level => fnd_api.g_valid_level_full,
149 p_return_status => p_return_status,
150 p_msg_count => p_msg_count,
151 p_msg_data => p_msg_data,
152 p_calendar_id => p_budget_calendar_id,
153 p_curr_year_type => p_budget_year_type_id,
154 p_curr_start_date => p_start_date,
155 p_curr_end_date => p_end_date,
156 p_mode_type => 'A'
157 );
158 --
159 if l_return_status = FND_API.G_RET_STS_ERROR then
160 RAISE FND_API.G_EXC_ERROR ;
161 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
163 end if;
164 --
165 Check_Duplicate_Year_Types
166 (p_calendar_id => p_budget_calendar_id,
167 p_curr_year_type => p_budget_year_type_id,
168 p_budget_period_id => p_budget_period_id,
169 p_return_status => l_return_status);
170 --
171 if l_return_status = FND_API.G_RET_STS_ERROR then
172 RAISE FND_API.G_EXC_ERROR ;
173 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
175 end if;
176 --
177 insert into PSB_BUDGET_PERIODS (
178 budget_period_id,
179 budget_calendar_id,
180 description,
181 start_date,
182 end_date,
183 name,
184 budget_year_type_id,
185 parent_budget_period_id,
186 budget_period_type,
187 period_distribution_type,
188 calculation_period_type,
189 attribute1,
190 attribute2,
191 attribute3,
192 attribute4,
193 attribute5,
194 attribute6,
195 attribute7,
196 attribute8,
197 attribute9,
198 attribute10,
199 context,
200 creation_date,
201 created_by,
202 last_update_date,
203 last_updated_by,
204 last_update_login
205 ) values (
206 p_budget_period_id,
207 p_budget_calendar_id,
208 p_description,
209 p_start_date,
210 p_end_date,
211 p_name,
212 p_budget_year_type_id,
213 p_parent_budget_period_id,
214 p_budget_period_type,
215 p_period_distribution_type,
216 p_calculation_period_type,
217 p_attribute1,
218 p_attribute2,
219 p_attribute3,
220 p_attribute4,
221 p_attribute5,
222 p_attribute6,
223 p_attribute7,
224 p_attribute8,
225 p_attribute9,
226 p_attribute10,
227 p_context,
228 p_last_update_date,
229 p_last_updated_by,
230 p_last_update_date,
231 p_last_updated_by,
232 p_last_update_login
233 );
234 --
235 open c;
236 fetch c into P_ROWID;
237 if (c%notfound) then
238 close c;
239 raise FND_API.G_EXC_ERROR ;
240 --raise no_data_found;
241 end if;
242 close c;
243 --
244 -- create periods and calc --
245 if (p_budget_period_type = 'Y') THEN
246 Create_Periods(
247 p_calendar_id => p_budget_calendar_id ,
248 p_year_id => p_budget_period_id,
249 p_year_name => p_name,
250 p_start_date => p_start_date,
251 p_end_date => p_end_date,
252 p_budget_period_type => p_period_distribution_type,
253 p_calc_period_type => p_calculation_period_type,
254 p_return_status => l_return_status,
255 p_msg_count => p_msg_count,
256 p_msg_data => p_msg_data
257 );
258 if l_return_status = FND_API.G_RET_STS_ERROR then
259 RAISE FND_API.G_EXC_ERROR ;
260 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
262 end if;
263 --+ set requery so form can do execute query when create period button not
264 --+ selected....
265 p_requery := 'Y' ;
266 end if;
267
268 --
269 -- Standard check of p_commit.
270 if FND_API.to_Boolean (p_commit) then
271 commit work;
272 end if;
273 -- Standard call to get message count and if count is 1, get message info.
274 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
275 p_data => p_msg_data);
276 --
277 EXCEPTION
278 --
279 when FND_API.G_EXC_ERROR then
280 --
281 rollback to INSERT_ROW ;
282 p_return_status := FND_API.G_RET_STS_ERROR;
283 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
284 p_data => p_msg_data);
285 --
286 when FND_API.G_EXC_UNEXPECTED_ERROR then
287 --
288 rollback to INSERT_ROW ;
289 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
290 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
291 p_data => p_msg_data);
292 --
293 when OTHERS then
294 --
295 rollback to INSERT_ROW ;
296 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
297 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
298 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
299 l_api_name);
300 END if;
301 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
302 p_data => p_msg_data);
303 --
304 END INSERT_ROW;
305 --
306 PROCEDURE LOCK_ROW (
307 p_api_version in number,
308 p_init_msg_list in varchar2 := fnd_api.g_false,
309 p_commit in varchar2 := fnd_api.g_false,
310 p_validation_level in number := fnd_api.g_valid_level_full,
311 p_return_status OUT NOCOPY varchar2,
312 p_msg_count OUT NOCOPY number,
313 p_msg_data OUT NOCOPY varchar2,
314 p_row_locked OUT NOCOPY varchar2,
315 p_budget_period_id in number,
316 p_budget_calendar_id in number,
317 p_description in varchar2,
318 p_start_date in date,
319 p_end_date in date,
320 p_name in varchar2,
321 p_budget_year_type_id in number,
322 p_parent_budget_period_id in number,
323 p_budget_period_type in varchar2,
324 p_period_distribution_type in varchar2,
325 p_calculation_period_type in varchar2,
326 p_attribute1 in varchar2,
327 p_attribute2 in varchar2,
328 p_attribute3 in varchar2,
329 p_attribute4 in varchar2,
330 p_attribute5 in varchar2,
331 p_attribute6 in varchar2,
332 p_attribute7 in varchar2,
333 p_attribute8 in varchar2,
334 p_attribute9 in varchar2,
335 p_attribute10 in varchar2,
336 p_context in varchar2
337
338 ) is
339 cursor c1 is select
340 budget_calendar_id,
341 description,
342 start_date,
343 end_date,
344 name,
345 budget_year_type_id,
346 parent_budget_period_id,
347 budget_period_type,
348 period_distribution_type,
349 calculation_period_type,
350 attribute1,
351 attribute2,
352 attribute3,
353 attribute4,
354 attribute5,
355 attribute6,
356 attribute7,
357 attribute8,
358 attribute9,
359 attribute10,
360 context
361 from PSB_BUDGET_PERIODS
362 where BUDGET_PERIOD_ID = P_BUDGET_PERIOD_ID
363 for update of BUDGET_PERIOD_ID nowait;
364 tlinfo c1%rowtype;
365 --
366 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row' ;
367 l_api_version CONSTANT NUMBER := 1.0 ;
368 l_return_status VARCHAR2(1);
369 --
370 BEGIN
371 --
372 SAVEPOINT Lock_Row ;
373 --
374 if FND_API.to_Boolean (p_init_msg_list) then
375 FND_MSG_PUB.initialize;
376 end if;
377 --
378 p_return_status := FND_API.G_RET_STS_SUCCESS ;
379 p_row_locked := FND_API.G_TRUE ;
380 --
381 open c1;
382 fetch c1 into tlinfo;
383 if (c1%notfound) then
384 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
385 fnd_msg_pub.add ;
386 close c1;
387 raise fnd_api.g_exc_error ;
388 end if;
389 close c1;
390 --
391 if ( (tlinfo.BUDGET_CALENDAR_ID = P_BUDGET_CALENDAR_ID)
392 AND (tlinfo.NAME = P_NAME)
393 AND (tlinfo.START_DATE = P_START_DATE)
394 AND (tlinfo.END_DATE = P_END_DATE)
395 AND ((tlinfo.DESCRIPTION = P_DESCRIPTION)
396 OR ((tlinfo.DESCRIPTION is null)
397 AND (P_DESCRIPTION is null)))
398 AND ((tlinfo.BUDGET_YEAR_TYPE_ID = P_BUDGET_YEAR_TYPE_ID)
399 OR ((tlinfo.BUDGET_YEAR_TYPE_ID is null)
400 AND (P_BUDGET_YEAR_TYPE_ID is null)))
401 AND ((tlinfo.PARENT_BUDGET_PERIOD_ID = P_PARENT_BUDGET_PERIOD_ID)
402 OR ((tlinfo.PARENT_BUDGET_PERIOD_ID is null)
403 AND (P_PARENT_BUDGET_PERIOD_ID is null)))
404 AND (tlinfo.BUDGET_PERIOD_TYPE = P_BUDGET_PERIOD_TYPE)
405 AND ((tlinfo.PERIOD_DISTRIBUTION_TYPE = P_PERIOD_DISTRIBUTION_TYPE)
406 OR ((tlinfo.PERIOD_DISTRIBUTION_TYPE is null)
407 AND (P_PERIOD_DISTRIBUTION_TYPE is null)))
408 AND ((tlinfo.CALCULATION_PERIOD_TYPE = P_CALCULATION_PERIOD_TYPE)
409 OR ((tlinfo.CALCULATION_PERIOD_TYPE is null)
410 AND (P_CALCULATION_PERIOD_TYPE is null)))
411 AND ((tlinfo.ATTRIBUTE1 = P_ATTRIBUTE1)
412 OR ((tlinfo.ATTRIBUTE1 is null)
413 AND (P_ATTRIBUTE1 is null)))
414 AND ((tlinfo.ATTRIBUTE2 = P_ATTRIBUTE2)
415 OR ((tlinfo.ATTRIBUTE2 is null)
416 AND (P_ATTRIBUTE2 is null)))
417 AND ((tlinfo.ATTRIBUTE3 = P_ATTRIBUTE3)
418 OR ((tlinfo.ATTRIBUTE3 is null)
419 AND (P_ATTRIBUTE3 is null)))
420 AND ((tlinfo.ATTRIBUTE4 = P_ATTRIBUTE4)
421 OR ((tlinfo.ATTRIBUTE4 is null)
422 AND (P_ATTRIBUTE4 is null)))
423 AND ((tlinfo.ATTRIBUTE5 = P_ATTRIBUTE5)
424 OR ((tlinfo.ATTRIBUTE5 is null)
425 AND (P_ATTRIBUTE5 is null)))
426 AND ((tlinfo.ATTRIBUTE6 = P_ATTRIBUTE6)
427 OR ((tlinfo.ATTRIBUTE6 is null)
428 AND (P_ATTRIBUTE6 is null)))
429 AND ((tlinfo.ATTRIBUTE7 = P_ATTRIBUTE7)
430 OR ((tlinfo.ATTRIBUTE7 is null)
431 AND (P_ATTRIBUTE7 is null)))
432 AND ((tlinfo.ATTRIBUTE8 = P_ATTRIBUTE8)
433 OR ((tlinfo.ATTRIBUTE8 is null)
434 AND (P_ATTRIBUTE8 is null)))
435 AND ((tlinfo.ATTRIBUTE9 = P_ATTRIBUTE9)
439 OR ((tlinfo.ATTRIBUTE10 is null)
436 OR ((tlinfo.ATTRIBUTE9 is null)
437 AND (P_ATTRIBUTE9 is null)))
438 AND ((tlinfo.ATTRIBUTE10 = P_ATTRIBUTE10)
440 AND (P_ATTRIBUTE10 is null)))
441 AND ((tlinfo.CONTEXT = P_CONTEXT)
442 OR ((tlinfo.CONTEXT is null)
443 AND (P_CONTEXT is null)))
444 ) then
445 null;
446 else
447 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
448 fnd_msg_pub.add ;
449 raise fnd_api.g_exc_error ;
450 end if;
451
452 EXCEPTION
453 when app_exception.record_lock_exception then
454 --
455 rollback to LOCK_ROW ;
456 p_row_locked := FND_API.G_FALSE ;
457 p_return_status := FND_API.G_RET_STS_ERROR;
458 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
459 p_data => p_msg_data);
460 --
461 when FND_API.G_EXC_ERROR then
462 --
463 rollback to LOCK_ROW ;
464 p_return_status := FND_API.G_RET_STS_ERROR;
465 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
466 p_data => p_msg_data);
467 --
468 when FND_API.G_EXC_UNEXPECTED_ERROR then
469 --
470 rollback to LOCK_ROW ;
471 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
472 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
473 p_data => p_msg_data);
474 --
475 when OTHERS then
476 --
477 rollback to LOCK_ROW ;
478 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
479 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
480 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
481 l_api_name);
482 END if;
483 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
484 p_data => p_msg_data);
485 --
486 END LOCK_ROW;
487 --
488 PROCEDURE UPDATE_ROW (
489 p_api_version in number,
490 p_init_msg_list in varchar2 := fnd_api.g_false,
491 p_commit in varchar2 := fnd_api.g_false,
492 p_validation_level in number := fnd_api.g_valid_level_full,
493 p_return_status OUT NOCOPY varchar2,
494 p_msg_count OUT NOCOPY number,
495 p_msg_data OUT NOCOPY varchar2,
496 p_budget_period_id in number,
497 p_budget_calendar_id in number,
498 p_description in varchar2,
499 p_start_date in date,
500 p_end_date in date,
501 p_name in varchar2,
502 p_budget_year_type_id in number,
503 p_parent_budget_period_id in number,
504 p_budget_period_type in varchar2,
505 p_period_distribution_type in varchar2,
506 p_calculation_period_type in varchar2,
507 p_attribute1 in varchar2,
508 p_attribute2 in varchar2,
509 p_attribute3 in varchar2,
510 p_attribute4 in varchar2,
511 p_attribute5 in varchar2,
512 p_attribute6 in varchar2,
513 p_attribute7 in varchar2,
514 p_attribute8 in varchar2,
515 p_attribute9 in varchar2,
516 p_attribute10 in varchar2,
517 p_context in varchar2,
518 p_mode in varchar2,
519 p_requery OUT NOCOPY varchar2
520 ) is
521 P_LAST_UPDATE_DATE DATE;
522 P_LAST_UPDATED_BY NUMBER;
523 P_LAST_UPDATE_LOGIN NUMBER;
524 --
525 l_api_name CONSTANT VARCHAR2(30) := 'Update Row';
526 l_api_version CONSTANT NUMBER := 1.0 ;
527 l_return_status VARCHAR2(1);
528 l_update_dist VARCHAR2(1) := 'N';
529 l_update_calc VARCHAR2(1) := 'N';
530 l_pd_dist_type VARCHAR2(10);
531 l_pd_calc_type VARCHAR2(10);
532 --
533 BEGIN
534 --
535 SAVEPOINT Update_Row ;
536 --
537 if FND_API.to_Boolean (p_init_msg_list) then
538 FND_MSG_PUB.initialize;
539 end if;
540
541 -- Initialize API return status to success
542
543 p_return_status := FND_API.G_RET_STS_SUCCESS ;
544
545 --
546 Check_Used_In_WS
547 (p_calendar_id => p_budget_calendar_id,
548 p_return_status => l_return_status);
549
550 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
551 RAISE FND_API.G_EXC_ERROR ;
552 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
554 END IF;
555
556 --
557 P_LAST_UPDATE_DATE := SYSDATE;
558 if(P_MODE = 'I') then
559 P_LAST_UPDATED_BY := 1;
560 P_LAST_UPDATE_LOGIN := 0;
561 elsif (P_MODE = 'R') then
562 P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
563 if P_LAST_UPDATED_BY is NULL then
564 P_LAST_UPDATED_BY := -1;
565 end if;
566 P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
567 if P_LAST_UPDATE_LOGIN is NULL then
568 P_LAST_UPDATE_LOGIN := -1;
569 end if;
570 else
571 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
572 FND_MSG_PUB.Add ;
573 raise FND_API.G_EXC_ERROR ;
574 end if;
575
576 --
577 -- get original value of distribution type and calc type to determine
578 -- whether to re-create them
579 --
580 select period_distribution_type,calculation_period_type
581 into l_pd_dist_type, l_pd_calc_type
582 from psb_budget_periods
583 where budget_period_id = p_budget_period_id;
584
588
585 if sql%notfound then
586 raise FND_API.G_EXC_ERROR;
587 end if;
589 if l_pd_dist_type <> p_period_distribution_type then
590 l_update_dist := 'Y';
591 end if;
592
593 if l_pd_calc_type <> p_calculation_period_type then
594 l_update_calc := 'Y';
595 end if;
596
597 -- do the update of the record
598 --
599 update PSB_BUDGET_PERIODS set
600 budget_calendar_id = p_budget_calendar_id,
601 name = p_name,
602 start_date = p_start_date,
603 end_date = p_end_date,
604 description = p_description,
605 budget_year_type_id = p_budget_year_type_id,
606 parent_budget_period_id = p_parent_budget_period_id,
607 budget_period_type = p_budget_period_type,
608 period_distribution_type = p_period_distribution_type,
609 calculation_period_type = p_calculation_period_type,
610 attribute1 = p_attribute1,
611 attribute2 = p_attribute2,
612 attribute3 = p_attribute3,
613 attribute4 = p_attribute4,
614 attribute5 = p_attribute5,
615 attribute6 = p_attribute6,
616 attribute7 = p_attribute7,
617 attribute8 = p_attribute8,
618 attribute9 = p_attribute9,
619 attribute10 = p_attribute10,
620 context = p_context,
621 last_update_date = p_last_update_date,
622 last_updated_by = p_last_updated_by,
623 last_update_login = p_last_update_login
624 where BUDGET_PERIOD_ID = P_BUDGET_PERIOD_ID
625 ;
626 if (sql%notfound) then
627 -- raise no_data_found;
628 raise FND_API.G_EXC_ERROR ;
629 end if;
630
631 -- create new periods or calcs
632 Create_New_Distr_Calc_Period(
633 p_calendar_id => p_budget_calendar_id ,
634 p_year_id => p_budget_period_id,
635 p_year_name => p_name,
636 p_start_date => p_start_date,
637 p_end_date => p_end_date,
638 p_budget_period_type => p_period_distribution_type,
639 p_calc_period_type => p_calculation_period_type,
640 p_update_dist => l_update_dist,
641 p_update_calc => l_update_calc,
642 p_return_status => l_return_status,
643 p_msg_count => p_msg_count,
644 p_msg_data => p_msg_data
645 );
646 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
647 RAISE FND_API.G_EXC_ERROR ;
648 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
650 END IF;
651
652 IF l_update_dist = 'Y' THEN
653 p_requery := 'Y' ;
654 END IF ;
655
656 --
657 --
658 -- Standard check of p_commit.
659
660 if FND_API.to_Boolean (p_commit) then
661 commit work;
662 end if;
663
664 -- Standard call to get message count and if count is 1, get message info.
665
666 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
667 p_data => p_msg_data);
668 --
669 EXCEPTION
670
671 when FND_API.G_EXC_ERROR then
672 --
673 rollback to Update_Row ;
674 p_return_status := FND_API.G_RET_STS_ERROR;
675 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
676 p_data => p_msg_data);
677 --
678 when FND_API.G_EXC_UNEXPECTED_ERROR then
679 --
680 rollback to Update_Row ;
681 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
682 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
683 p_data => p_msg_data);
684 --
685 when OTHERS then
686 --
687 rollback to Update_Row ;
688 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
689 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
690 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
691 l_api_name);
692 end if;
693 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
694 p_data => p_msg_data);
695 --
696
697 END UPDATE_ROW;
698 --
699 PROCEDURE ADD_ROW (
700 p_api_version in number,
701 p_init_msg_list in varchar2 := fnd_api.g_false,
702 p_commit in varchar2 := fnd_api.g_false,
703 p_validation_level in number := fnd_api.g_valid_level_full,
704 p_return_status OUT NOCOPY varchar2,
705 p_msg_count OUT NOCOPY number,
706 p_msg_data OUT NOCOPY varchar2,
707 p_rowid in OUT NOCOPY varchar2,
708 p_budget_period_id in number,
709 p_budget_calendar_id in number,
710 p_description in varchar2,
711 p_start_date in date,
712 p_end_date in date,
713 p_name in varchar2,
714 p_budget_year_type_id in number,
715 p_parent_budget_period_id in number,
716 p_budget_period_type in varchar2,
717 p_period_distribution_type in varchar2,
718 p_calculation_period_type in varchar2,
719 p_attribute1 in varchar2,
720 p_attribute2 in varchar2,
721 p_attribute3 in varchar2,
722 p_attribute4 in varchar2,
723 p_attribute5 in varchar2,
724 p_attribute6 in varchar2,
725 p_attribute7 in varchar2,
726 p_attribute8 in varchar2,
727 p_attribute9 in varchar2,
731 p_requery OUT NOCOPY varchar2
728 p_attribute10 in varchar2,
729 p_context in varchar2,
730 p_mode in varchar2,
732 ) is
733 cursor c1 is select rowid from PSB_BUDGET_PERIODS
734 where BUDGET_PERIOD_ID = P_BUDGET_PERIOD_ID
735 ;
736 dummy c1%rowtype;
737 --
738 l_api_name CONSTANT VARCHAR2(30) := 'Add Row' ;
739 l_api_version CONSTANT NUMBER := 1.0 ;
740 --
741 BEGIN
742 --
743 SAVEPOINT Add_Row ;
744 --
745 -- Initialize message list if p_init_msg_list is set to TRUE.
746 --
747 if FND_API.to_Boolean (p_init_msg_list) then
748 FND_MSG_PUB.initialize;
749 end if;
750 --
751 p_return_status := FND_API.G_RET_STS_SUCCESS ;
752 --
753 open c1;
754 fetch c1 into dummy;
755 if (c1%notfound) then
756 close c1;
757 INSERT_ROW (
758 p_api_version,
759 p_init_msg_list,
760 p_commit,
761 p_validation_level,
762 p_return_status,
763 p_msg_count,
764 p_msg_data,
765 p_rowid,
766 p_budget_period_id,
767 p_budget_calendar_id,
768 p_description,
769 p_start_date,
770 p_end_date,
771 p_name,
772 p_budget_year_type_id,
773 p_parent_budget_period_id,
774 p_budget_period_type,
775 p_period_distribution_type,
776 p_calculation_period_type,
777 p_attribute1,
778 p_attribute2,
779 p_attribute3,
780 p_attribute4,
781 p_attribute5,
782 p_attribute6,
783 p_attribute7,
784 p_attribute8,
785 p_attribute9,
786 p_attribute10,
787 p_context,
788 p_mode,
789 p_requery );
790 --
791 if FND_API.to_Boolean (p_commit) then
792 commit work;
793 end if;
794 -- Standard call to get message count and if count is 1, get message info.
795 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
796 p_data => p_msg_data);
797
798 return;
799 END if;
800 close c1;
801 UPDATE_ROW (
802 p_api_version,
803 p_init_msg_list,
804 p_commit,
805 p_validation_level,
806 p_return_status,
807 p_msg_count,
808 p_msg_data,
809 p_budget_period_id,
810 p_budget_calendar_id,
811 p_description,
812 p_start_date,
813 p_end_date,
814 p_name,
815 p_budget_year_type_id,
816 p_parent_budget_period_id,
817 p_budget_period_type,
818 p_period_distribution_type,
819 p_calculation_period_type,
820 p_attribute1,
821 p_attribute2,
822 p_attribute3,
823 p_attribute4,
824 p_attribute5,
825 p_attribute6,
826 p_attribute7,
827 p_attribute8,
828 p_attribute9,
829 p_attribute10,
830 p_context,
831 p_mode,
832 p_requery );
833 -- Standard check of p_commit.
834
835 if FND_API.to_Boolean (p_commit) then
836 commit work;
837 end if;
838
839 -- Standard call to get message count and if count is 1, get message info.
840
841 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
842 p_data => p_msg_data);
843
844 END ADD_ROW;
845 --
846 PROCEDURE DELETE_ROW (
847 p_api_version in number,
848 p_init_msg_list in varchar2 := fnd_api.g_false,
849 p_commit in varchar2 := fnd_api.g_false,
850 p_validation_level in number := fnd_api.g_valid_level_full,
851 p_return_status OUT NOCOPY varchar2,
852 p_msg_count OUT NOCOPY number,
853 p_msg_data OUT NOCOPY varchar2,
854 p_budget_period_id in number
855 ) is
856 --
857 l_api_name CONSTANT VARCHAR2(30) := 'Delete Row' ;
858 l_api_version CONSTANT NUMBER := 1.0 ;
859 l_budget_calendar_id NUMBER;
860 l_start_date DATE;
861 l_end_date DATE;
862 l_budget_year_type_id NUMBER;
863 l_return_status VARCHAR2(1);
864 --
865 BEGIN
866 --
867 SAVEPOINT Delete_Row ;
868 --
869 -- Initialize message list if p_init_msg_list is set to TRUE.
870 --
871 if FND_API.to_Boolean (p_init_msg_list) then
872 FND_MSG_PUB.initialize;
873 end if;
874 --
875 p_return_status := FND_API.G_RET_STS_SUCCESS ;
876 --
877 -- check first if calendar not used in worksheet
878 select budget_calendar_id,start_date,end_date,budget_year_type_id
879 into l_budget_calendar_id,l_start_date,l_end_date,l_budget_year_type_id
880 FROM psb_budget_periods
881 WHERE budget_period_id = p_budget_period_id;
882
883 --
884 Check_Used_In_WS
885 (p_calendar_id => l_budget_calendar_id,
886 p_return_status => l_return_status);
887
888 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
889 RAISE FND_API.G_EXC_ERROR ;
890 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
891 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
892 END IF;
893 --
894 delete from PSB_BUDGET_PERIODS
898 end if;
895 where parent_budget_period_id = p_budget_period_id;
896 if (sql%notfound) THEN
897 null;
899
900 delete from PSB_BUDGET_PERIODS
901 where BUDGET_PERIOD_ID = P_BUDGET_PERIOD_ID;
902 if (sql%notfound) then
903 -- raise no_data_found;
904 raise FND_API.G_EXC_ERROR ;
905 end if;
906 --
907 -- Standard check of p_commit.
908 --
909 if FND_API.to_Boolean (p_commit) then
910 commit work;
911 end if;
912
913 -- Standard call to get message count and if count is 1, get message info.
914
915 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
916 p_data => p_msg_data);
917 --
918 EXCEPTION
919 when FND_API.G_EXC_ERROR then
920 --
921 rollback to Delete_Row;
922 p_return_status := FND_API.G_RET_STS_ERROR;
923 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
924 p_data => p_msg_data);
925 --
926 when FND_API.G_EXC_UNEXPECTED_ERROR then
927 --
928 rollback to Delete_Row;
929 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
930 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
931 p_data => p_msg_data);
932 --
933 when OTHERS then
934 --
935 rollback to Delete_Row ;
936 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
937 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
938 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
939 l_api_name);
940 end if;
941 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
942 p_data => p_msg_data);
943 --
944 END DELETE_ROW;
945 --
946 -- End of Table Handler Procedures
947 --
948 PROCEDURE Check_Duplicate_Year_Types
949 (p_calendar_id IN NUMBER,
950 p_curr_year_type IN NUMBER,
951 p_budget_period_id IN NUMBER,
952 p_return_status OUT NOCOPY VARCHAR2) IS
953 --
954 l_type_count NUMBER ;
955 --
956 BEGIN
957 --
958 SELECT count(*)
959 INTO l_type_count
960 FROM PSB_BUDGET_PERIODS
961 WHERE budget_calendar_id = p_calendar_id
962 AND budget_year_type_id = p_curr_year_type
963 AND budget_period_id <> p_budget_period_id ;
964 --
965 IF l_type_count > 0 THEN
966 FND_MESSAGE.SET_NAME('PSB', 'PSB_DUP_YEAR_TYPE_IN_CAL');
967 FND_MSG_PUB.Add ;
968 RAISE FND_API.G_EXC_ERROR ;
969 END IF;
970 --
971 p_return_status := FND_API.G_RET_STS_SUCCESS ;
972 --
973 EXCEPTION
974 --
975 when FND_API.G_EXC_ERROR then
976 --
977 p_return_status := FND_API.G_RET_STS_ERROR;
978 --
979 when FND_API.G_EXC_UNEXPECTED_ERROR then
980 --
981 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
982 --
983 END Check_Duplicate_Year_Types;
984 --
985 PROCEDURE Check_Consecutive_Year_Types(
986 p_api_version IN NUMBER,
987 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
988 p_commit IN VARCHAR2 := FND_API.G_FALSE,
989 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
990 p_return_status OUT NOCOPY VARCHAR2,
991 p_msg_count OUT NOCOPY NUMBER,
992 p_msg_data OUT NOCOPY VARCHAR2,
993 p_calendar_id IN NUMBER,
994 p_curr_year_type IN NUMBER,
995 p_curr_start_date IN DATE,
996 p_curr_end_date IN DATE,
997 p_mode_type IN VARCHAR2
998 ) IS
999 --
1000 l_api_name CONSTANT VARCHAR2(30) := 'Check_Consecutive_Years' ;
1001 l_api_version CONSTANT NUMBER := 1.0 ;
1002 l_return_status VARCHAR2(1);
1003 l_prior_year_seq NUMBER ;
1004 l_next_year_seq NUMBER ;
1005 l_prior_seq NUMBER ;
1006 l_next_seq NUMBER ;
1007 l_prior_end_date DATE;
1008 l_next_start_date DATE;
1009 --
1010 CURSOR prior_year_csr IS
1011 SELECT max(b.sequence_number), end_date
1012 FROM psb_budget_periods a,
1013 psb_budget_year_types b,
1014 psb_budget_year_types c
1015 WHERE a.budget_year_type_id = b.budget_year_type_id
1016 AND b.sequence_number < c.sequence_number
1017 AND c.budget_year_type_id = p_curr_year_type
1018 AND a.budget_calendar_id = p_calendar_id
1019 GROUP BY end_date
1020 ORDER BY end_date DESC;
1021 --
1022 CURSOR next_year_csr IS
1023 SELECT min(b.sequence_number), start_date
1024 FROM psb_budget_periods a,
1025 psb_budget_year_types b,
1026 psb_budget_year_types c
1027 WHERE a.budget_year_type_id = b.budget_year_type_id
1028 AND b.sequence_number > c.sequence_number
1029 AND c.budget_year_type_id = p_curr_year_type
1030 AND a.budget_calendar_id = p_calendar_id
1031 GROUP BY start_date ;
1032 --
1033 CURSOR prior_type_csr IS
1034 SELECT max(a.sequence_number)
1035 FROM psb_budget_year_types a,
1036 psb_budget_year_types b
1037 WHERE a.sequence_number < b.sequence_number
1038 AND b.budget_year_type_id = p_curr_year_type;
1039 --
1040 CURSOR next_type_csr IS
1041 SELECT min(a.sequence_number)
1042 FROM psb_budget_year_types a,
1043 psb_budget_year_types b
1047 BEGIN
1044 WHERE a.sequence_number > b.sequence_number
1045 AND b.budget_year_type_id = p_curr_year_type;
1046 --
1048 --
1049 p_return_status := FND_API.G_RET_STS_SUCCESS;
1050 OPEN prior_year_csr ;
1051 FETCH prior_year_csr INTO l_prior_year_seq, l_prior_end_date ;
1052 IF prior_year_csr%NOTFOUND THEN
1053 IF (p_mode_type = 'A') THEN
1054 l_prior_end_date := p_curr_start_date - 1 ;
1055 ELSE
1056 l_prior_end_date := p_curr_start_date + 1 ;
1057 END IF;
1058 END IF;
1059 CLOSE prior_year_csr ;
1060 --
1061 OPEN next_year_csr ;
1062 FETCH next_year_csr INTO l_next_year_seq, l_next_start_date ;
1063 IF next_year_csr%NOTFOUND THEN
1064 IF (p_mode_type = 'A') THEN
1065 l_next_start_date := p_curr_end_date + 1 ;
1066 ELSE
1067 l_next_start_date := p_curr_end_date - 1 ;
1068 END IF;
1069 END IF;
1070 CLOSE next_year_csr ;
1071 --
1072 OPEN prior_type_csr ;
1073 FETCH prior_type_csr INTO l_prior_seq ;
1074 CLOSE prior_type_csr ;
1075 --
1076 OPEN next_type_csr ;
1077 FETCH next_type_csr INTO l_next_seq ;
1078 CLOSE next_type_csr ;
1079 --
1080 IF (p_mode_type = 'A') THEN
1081 IF (p_curr_start_date <> l_prior_end_date + 1 ) OR
1082 (p_curr_end_date <> l_next_start_date - 1 ) THEN
1083 FND_MESSAGE.SET_NAME('PSB', 'PSB_YEAR_DATE_MUST_BE_CONSEC');
1084 FND_MSG_PUB.Add ;
1085 RAISE FND_API.G_EXC_ERROR ;
1086 ELSE
1087 IF ((l_prior_year_seq is not null and
1088 l_prior_seq is not null) and
1089 l_prior_year_seq <> l_prior_seq ) OR
1090 ((l_next_year_seq is not null and
1091 l_next_seq is not null) and
1092 l_next_year_seq <> l_next_seq ) THEN
1093 FND_MESSAGE.SET_NAME('PSB', 'PSB_YEAR_TYPE_MUST_BE_CONSEC');
1094 FND_MSG_PUB.Add ;
1095 RAISE FND_API.G_EXC_ERROR ;
1096 END IF ;
1097 END IF;
1098 END IF;
1099 --
1100 IF (p_mode_type = 'D') THEN
1101 IF (p_curr_start_date = l_prior_end_date + 1 ) AND
1102 (p_curr_end_date = l_next_start_date - 1 ) THEN
1103 FND_MESSAGE.SET_NAME('PSB', 'PSB_CANNOT_DELETE_YEAR');
1104 FND_MSG_PUB.Add ;
1105 RAISE FND_API.G_EXC_ERROR ;
1106 END IF;
1107 END IF;
1108 --
1109 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1110
1111 -- Standard call to get message count and if count is 1, get message info.
1112 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1113 p_data => p_msg_data);
1114 --
1115 --
1116 EXCEPTION
1117 --
1118 when FND_API.G_EXC_ERROR then
1119 --
1120 p_return_status := FND_API.G_RET_STS_ERROR;
1121 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1122 p_data => p_msg_data);
1123 --
1124 when FND_API.G_EXC_UNEXPECTED_ERROR then
1125 --
1126 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1127 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1128 p_data => p_msg_data);
1129 --
1130 when OTHERS then
1131 --
1132 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1133 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1134 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1135 l_api_name);
1136 END if;
1137 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1138 p_data => p_msg_data);
1139 --
1140 --
1141 END Check_Consecutive_Year_Types ;
1142 --
1143 PROCEDURE Create_Child_Periods
1144 (p_calendar_id IN NUMBER,
1145 p_parent_id IN NUMBER,
1146 p_parent_name IN VARCHAR2,
1147 p_start_date IN DATE,
1148 p_end_date IN DATE,
1149 p_child_type IN VARCHAR2,
1150 p_period_type IN VARCHAR2,
1151 p_return_status OUT NOCOPY VARCHAR2,
1152 p_msg_count OUT NOCOPY NUMBER,
1153 p_msg_data OUT NOCOPY VARCHAR2
1154 ) IS
1155 --
1156 l_start_date DATE;
1157 l_end_date DATE;
1158 l_lstart_date DATE;
1159 l_short_name VARCHAR2(30);
1160 l_counter NUMBER;
1161 l_budget_period_id NUMBER;
1162 l_return_status VARCHAR2(1);
1163 l_rowid VARCHAR2(100);
1164 l_requery VARCHAR2(1);
1165 --
1166 BEGIN
1167 --
1168 l_start_date := p_start_date ;
1169 l_end_date := p_start_date ;
1170 l_counter := 1 ;
1171 --
1172 WHILE l_end_date < p_end_date LOOP
1173 --
1174 -- The end date of the child period is calculated by
1175 -- 1) Find the Start Date of the last month
1176 -- For example if type is QTR then add 2 months to the
1177 -- start date to determine start date of last month
1178 -- 2) End Date is End Date of the last month
1179 --
1180 IF p_child_type = G_MONTH THEN
1181 l_lstart_date := l_start_date ;
1182 ELSIF p_child_type = G_QTR THEN
1183 l_lstart_date := ADD_MONTHS(l_start_date, G_QTR_NUM);
1184 ELSIF p_child_type = G_SEMI THEN
1185 l_lstart_date := ADD_MONTHS(l_start_date, G_SEMI_NUM) ;
1186 END IF;
1187 --
1188 l_end_date := LAST_DAY(l_lstart_date) ;
1189 l_short_name := substr(p_parent_name, 1, 10)||'-'||to_char(l_counter);
1190 --
1191 SELECT psb_budget_periods_s.nextval
1195 --
1192 INTO l_budget_period_id
1193 FROM dual;
1194 --
1196 INSERT_ROW (
1197 p_api_version => 1.0,
1198 p_init_msg_list => fnd_api.g_false,
1199 p_commit => fnd_api.g_false,
1200 p_validation_level => fnd_api.g_valid_level_full,
1201 p_return_status => l_return_status,
1202 p_msg_count => p_msg_count,
1203 p_msg_data => p_msg_data,
1204 p_rowid => l_rowid,
1205 p_budget_period_id => l_budget_period_id,
1206 p_budget_calendar_id => p_calendar_id,
1207 p_description => l_short_name,
1208 p_start_date => l_start_date,
1209 p_end_date => l_end_date ,
1210 p_name => l_short_name,
1211 p_budget_year_type_id => null,
1212 p_parent_budget_period_id => p_parent_id,
1213 p_budget_period_type => p_period_type,
1214 p_period_distribution_type => null,
1215 p_calculation_period_type => null,
1216 p_attribute1 => null,
1217 p_attribute2 => null,
1218 p_attribute3 => null,
1219 p_attribute4 => null,
1220 p_attribute5 => null,
1221 p_attribute6 => null,
1222 p_attribute7 => null,
1223 p_attribute8 => null,
1224 p_attribute9 => null,
1225 p_attribute10 => null,
1226 p_context => null,
1227 p_mode => 'R',
1228 p_requery => l_requery
1229 );
1230 --
1231 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1232 RAISE FND_API.G_EXC_ERROR ;
1233 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1234 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1235 END IF;
1236 --
1237 --p_requery := 'N';
1238 l_start_date := l_end_date + 1 ;
1239 l_counter := l_counter + 1 ;
1240 --
1241 END LOOP;
1242 --
1243 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1244 --
1245 EXCEPTION
1246
1247 when FND_API.G_EXC_ERROR then
1248 --
1249 p_return_status := FND_API.G_RET_STS_ERROR;
1250 --
1251 when FND_API.G_EXC_UNEXPECTED_ERROR then
1252 --
1253 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1254 --
1255 END Create_Child_Periods ;
1256 --
1257 PROCEDURE Create_Budget_Periods
1258 (p_calendar_id IN NUMBER,
1259 p_year_id IN NUMBER,
1260 p_year_name IN VARCHAR2,
1261 p_start_date IN DATE,
1262 p_end_date IN DATE,
1263 p_budget_period_type IN VARCHAR2,
1264 p_period_record_type IN VARCHAR2,
1265 p_return_status OUT NOCOPY VARCHAR2,
1266 p_msg_count OUT NOCOPY NUMBER,
1267 p_msg_data OUT NOCOPY VARCHAR2) IS
1268 --
1269 l_return_status VARCHAR2(1);
1270 --
1271 BEGIN
1272 --
1273 Create_Child_Periods (p_calendar_id => p_calendar_id,
1274 p_parent_id => p_year_id,
1275 p_parent_name => p_year_name,
1276 p_start_date => p_start_date,
1277 p_end_date => p_end_date,
1278 p_child_type => p_budget_period_type,
1279 p_period_type => p_period_record_type,
1280 p_return_status => l_return_status,
1281 p_msg_count => p_msg_count,
1282 p_msg_data => p_msg_data);
1283 --
1284 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1285 RAISE FND_API.G_EXC_ERROR ;
1286 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1287 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1288 END IF;
1289 --
1290 EXCEPTION
1291
1292 when FND_API.G_EXC_ERROR then
1293 --
1294 p_return_status := FND_API.G_RET_STS_ERROR;
1295 --
1296 when FND_API.G_EXC_UNEXPECTED_ERROR then
1297 --
1298 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1299 --
1300 END Create_Budget_Periods ;
1301 --
1302 PROCEDURE Create_New_Distr_Calc_Period(
1303 p_calendar_id IN NUMBER,
1304 p_year_id IN NUMBER,
1305 p_year_name IN VARCHAR2,
1306 p_start_date IN DATE,
1307 p_end_date IN DATE,
1308 p_budget_period_type IN VARCHAR2,
1309 p_calc_period_type IN VARCHAR2,
1310 p_update_dist IN VARCHAR2,
1311 p_update_calc IN VARCHAR2,
1312 p_return_status OUT NOCOPY VARCHAR2,
1313 p_msg_count OUT NOCOPY number,
1314 p_msg_data OUT NOCOPY varchar2
1315 ) IS
1316 --
1317 l_api_name CONSTANT VARCHAR2(30) := 'Create_Periods';
1318 l_api_version CONSTANT NUMBER := 1.0;
1319 l_return_status VARCHAR2(1);
1320 --
1321 BEGIN
1322
1323 -- Initialize API return status to success
1324
1325 l_return_status := FND_API.G_RET_STS_SUCCESS;
1326
1327 --
1328 -- delete existing budget periods and create new ones
1329
1330 IF (p_update_dist = 'Y') THEN
1331
1332 -- delete old distribution lines
1333
1334 delete from psb_budget_periods where
1338 if sql%notfound then
1335 budget_calendar_id = p_calendar_id
1336 AND parent_budget_period_id = p_year_id
1337 AND budget_period_type = 'P';
1339 null;
1340 end if;
1341
1342 -- create new budget periods only if distribution type <> 'Y'
1343
1344 IF p_budget_period_type <> 'Y' THEN
1345
1346 Create_Budget_Periods(
1347 p_calendar_id => p_calendar_id,
1348 p_year_id => p_year_id,
1349 p_year_name => p_year_name,
1350 p_start_date => p_start_date,
1351 p_end_date => p_end_date,
1352 p_budget_period_type => p_budget_period_type,
1353 p_period_record_type => 'P',
1354 p_return_status => l_return_status,
1355 p_msg_count => p_msg_count,
1356 p_msg_data => p_msg_data);
1357 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1358 RAISE FND_API.G_EXC_ERROR ;
1359 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1361 END IF;
1362
1363 END IF;
1364
1365 END IF;
1366
1367 --
1368 -- delete current calc records and create new ones
1369
1370 IF (p_update_calc = 'Y') THEN
1371
1372 delete from psb_budget_periods where
1373 budget_calendar_id = p_calendar_id
1374 AND parent_budget_period_id = p_year_id
1375 AND budget_period_type = 'C' ;
1376 if sql%notfound then
1377 null;
1378 end if;
1379
1380 -- create new calc only if calc type is M/S/Q
1381 IF (p_calc_period_type = 'M' OR
1382 p_calc_period_type = 'S' OR
1383 p_calc_period_type = 'Q' )
1384 THEN
1385 Create_Budget_Periods(
1386 p_calendar_id => p_calendar_id,
1387 p_year_id => p_year_id,
1388 p_year_name => p_year_name,
1389 p_start_date => p_start_date,
1390 p_end_date => p_end_date,
1391 p_budget_period_type => p_calc_period_type,
1392 p_period_record_type => 'C',
1393 p_return_status => l_return_status,
1394 p_msg_count => p_msg_count,
1395 p_msg_data => p_msg_data);
1396 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1397 RAISE FND_API.G_EXC_ERROR ;
1398 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1400 END IF;
1401
1402 END IF;
1403
1404 END IF;
1405
1406 --
1407 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1408 --
1409 EXCEPTION
1410
1411 when FND_API.G_EXC_ERROR then
1412 --
1413 p_return_status := FND_API.G_RET_STS_ERROR;
1414 --
1415 when FND_API.G_EXC_UNEXPECTED_ERROR then
1416 --
1417 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1418 --
1419 END Create_New_Distr_Calc_Period;
1420 ----
1421 PROCEDURE Create_Periods(
1422 p_calendar_id IN NUMBER,
1423 p_year_id IN NUMBER,
1424 p_year_name IN VARCHAR2,
1425 p_start_date IN DATE,
1426 p_end_date IN DATE,
1427 p_budget_period_type IN VARCHAR2,
1428 p_calc_period_type IN VARCHAR2,
1429 p_return_status OUT NOCOPY VARCHAR2,
1430 p_msg_count OUT NOCOPY number,
1431 p_msg_data OUT NOCOPY varchar2
1432 ) IS
1433 --
1434 l_api_name CONSTANT VARCHAR2(30) := 'Create_Periods';
1435 l_api_version CONSTANT NUMBER := 1.0;
1436 l_return_status VARCHAR2(1);
1437 --
1438 BEGIN
1439
1440 -- Initialize API return status to success
1441
1442 l_return_status := FND_API.G_RET_STS_SUCCESS;
1443 --
1444 IF p_budget_period_type <> 'Y' THEN
1445
1446 Create_Budget_Periods(
1447 p_calendar_id => p_calendar_id,
1448 p_year_id => p_year_id,
1449 p_year_name => p_year_name,
1450 p_start_date => p_start_date,
1451 p_end_date => p_end_date,
1452 p_budget_period_type => p_budget_period_type,
1453 p_period_record_type => 'P',
1454 p_return_status => l_return_status,
1455 p_msg_count => p_msg_count,
1456 p_msg_data => p_msg_data);
1457 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1458 RAISE FND_API.G_EXC_ERROR ;
1459 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1461 END IF;
1462 END IF;
1463
1464 --
1465 IF (p_calc_period_type = 'M' OR
1466 p_calc_period_type = 'S' OR
1467 p_calc_period_type = 'Q' )
1468 THEN
1469
1470 Create_Budget_Periods(
1471 p_calendar_id => p_calendar_id,
1472 p_year_id => p_year_id,
1473 p_year_name => p_year_name,
1474 p_start_date => p_start_date,
1475 p_end_date => p_end_date,
1476 p_budget_period_type => p_calc_period_type,
1477 p_period_record_type => 'C',
1478 p_return_status => l_return_status,
1479 p_msg_count => p_msg_count,
1480 p_msg_data => p_msg_data);
1481
1482 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1483 RAISE FND_API.G_EXC_ERROR ;
1487
1484 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1485 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1486 END IF;
1488 END IF;
1489
1490 --
1491 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1492 --
1493 EXCEPTION
1494
1495 when FND_API.G_EXC_ERROR then
1496 --
1497 p_return_status := FND_API.G_RET_STS_ERROR;
1498 --
1499 when FND_API.G_EXC_UNEXPECTED_ERROR then
1500 --
1501 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1502 --
1503 END Create_Periods ;
1504 --
1505 PROCEDURE Copy_Years_In_Calendar(
1506 p_api_version IN NUMBER,
1507 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1508 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1509 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1510 p_return_status OUT NOCOPY VARCHAR2,
1511 p_msg_count OUT NOCOPY NUMBER,
1512 p_msg_data OUT NOCOPY VARCHAR2,
1513 p_source_cal_id IN NUMBER,
1514 p_target_cal_id IN NUMBER,
1515 p_shift_flag IN VARCHAR2) IS
1516 --
1517 CURSOR cal_years_csr IS
1518 SELECT a.budget_calendar_id,
1519 a.description,
1520 a.start_date,
1521 a.end_date,
1522 a.name,
1523 a.budget_year_type_id,
1524 a.parent_budget_period_id,
1525 a.budget_period_type,
1526 a.period_distribution_type,
1527 a.calculation_period_type,
1528 a.attribute1,
1529 a.attribute2,
1530 a.attribute3,
1531 a.attribute4,
1532 a.attribute5,
1533 a.attribute6,
1534 a.attribute7,
1535 a.attribute8,
1536 a.attribute9,
1537 a.attribute10,
1538 a.context,
1539 b.year_category_type
1540 FROM psb_budget_periods a,
1541 psb_budget_year_types b
1542 WHERE budget_calendar_id = p_source_cal_id
1543 AND a.budget_year_type_id = b.budget_year_type_id
1544 ORDER BY start_date;
1545 --
1546 l_prev_year_type_id NUMBER ;
1547 CURSOR prev_type_csr IS
1548 SELECT a.year_category_type,a.budget_year_type_id
1549 FROM psb_budget_year_types a,
1550 psb_budget_year_types b
1551 WHERE a.sequence_number < b.sequence_number
1552 AND b.budget_year_type_id = l_prev_year_type_id
1553 ORDER BY a.sequence_number DESC;
1554 --
1555 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Years_In_Calendar';
1556 l_api_version CONSTANT NUMBER := 1.0;
1557 l_first_rec BOOLEAN := TRUE ;
1558 l_skip_first_rec BOOLEAN := FALSE ;
1559 l_year_id NUMBER ;
1560 l_year_type_id NUMBER ;
1561 l_return_status VARCHAR2(1);
1562 l_rowid VARCHAR2(100);
1563 l_requery VARCHAR2(1);
1564 l_calc_type VARCHAR2(10);
1565 l_year_type VARCHAR2(10);
1566 --
1567 BEGIN
1568 -- Standard Start of API savepoint
1569
1570 SAVEPOINT Copy_Years_In_Calendar;
1571
1572 -- Initialize message list if p_init_msg_list is set to TRUE.
1573
1574 if FND_API.to_Boolean (p_init_msg_list) then
1575 FND_MSG_PUB.initialize;
1576 end if;
1577
1578 -- Initialize API return status to success
1579
1580 l_return_status := FND_API.G_RET_STS_SUCCESS;
1581 --
1582
1583 FOR cal_years_rec IN cal_years_csr LOOP
1584 --
1585 -- If Shifting the calendar then need to move the years. For first
1586 -- year record, use immediate previous year type data, if found;
1587 -- otherwise, ignore the year record.
1588 --
1589 IF (l_first_rec AND p_shift_flag = 'Y') THEN
1590 l_first_rec := FALSE ;
1591 l_prev_year_type_id := cal_years_rec.budget_year_type_id ;
1592
1593 OPEN prev_type_csr;
1594 FETCH prev_type_csr into l_year_type, l_year_type_id;
1595
1596 IF prev_type_csr%NOTFOUND THEN
1597 l_skip_first_rec := TRUE;
1598 ELSE
1599 l_skip_first_rec := FALSE;
1600 END IF;
1601
1602 CLOSE prev_type_csr;
1603 END IF;
1604
1605 IF l_skip_first_rec AND p_shift_flag = 'Y' THEN
1606 l_skip_first_rec := FALSE ;
1607 -- disregard first year to be shifted
1608 ELSE
1609 --
1610 SELECT psb_budget_periods_s.nextval
1611 INTO l_year_id
1612 FROM dual;
1613 --
1614 IF p_shift_flag = 'N' THEN
1615 l_year_type_id := cal_years_rec.budget_year_type_id ;
1616 l_year_type := cal_years_rec.year_category_type ;
1617 END IF ;
1618 --
1619 IF l_year_type = 'PY' THEN
1620 l_calc_type := NULL ;
1621 ELSE
1622 l_calc_type := cal_years_rec.calculation_period_type ;
1623 END IF ;
1624 --
1625 INSERT_ROW (
1626 p_api_version => 1.0,
1627 p_init_msg_list => fnd_api.g_false,
1628 p_commit => fnd_api.g_false,
1629 p_validation_level => fnd_api.g_valid_level_full,
1630 p_return_status => l_return_status,
1631 p_msg_count => p_msg_count,
1632 p_msg_data => p_msg_data,
1633 p_rowid => l_rowid,
1634 p_budget_period_id => l_year_id,
1635 p_budget_calendar_id => p_target_cal_id,
1636 p_description => cal_years_rec.name,
1637 p_start_date => cal_years_rec.start_date,
1638 p_end_date => cal_years_rec.end_date ,
1639 p_name => cal_years_rec.name,
1640 p_budget_year_type_id => l_year_type_id,
1641 p_parent_budget_period_id => null,
1642 p_budget_period_type => 'Y',
1643 p_period_distribution_type => cal_years_rec.period_distribution_type,
1644 p_calculation_period_type => l_calc_type,
1645 p_attribute1 => cal_years_rec.attribute1,
1646 p_attribute2 => cal_years_rec.attribute2,
1647 p_attribute3 => cal_years_rec.attribute3,
1648 p_attribute4 => cal_years_rec.attribute4,
1649 p_attribute5 => cal_years_rec.attribute5,
1650 p_attribute6 => cal_years_rec.attribute6,
1651 p_attribute7 => cal_years_rec.attribute7,
1652 p_attribute8 => cal_years_rec.attribute8,
1653 p_attribute9 => cal_years_rec.attribute9,
1654 p_attribute10 => cal_years_rec.attribute10,
1655 p_context => cal_years_rec.context,
1656 p_mode => 'R' ,
1657 p_requery => l_requery
1658 );
1659 --
1660 --
1661 END IF;
1662 --
1663 l_first_rec := FALSE ;
1664 l_skip_first_rec := FALSE ;
1665 l_year_type_id := cal_years_rec.budget_year_type_id ;
1666 l_year_type := cal_years_rec.year_category_type ;
1667 --
1668 END LOOP;
1669 --
1670 -- When copying calendar the period names of the target periods must
1671 -- same as the source. Since the Insert_Row creates it with default
1672 -- names we are updating it with source names
1673 --
1674 UPDATE psb_budget_periods a
1675 SET (name, description) =
1676 (SELECT name, description
1677 FROM psb_budget_periods b
1678 WHERE budget_calendar_id = p_source_cal_id
1679 AND budget_period_type = 'P'
1680 AND b.start_date = a.start_date)
1681 WHERE a.budget_calendar_id = p_target_cal_id
1682 AND a.budget_period_type = 'P' ;
1683 --
1684 --
1685 --
1686 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1687 --
1688 EXCEPTION
1689
1690 when FND_API.G_EXC_ERROR then
1691 --
1692 rollback to Copy_Years_In_Calendar;
1693 p_return_status := FND_API.G_RET_STS_ERROR;
1694 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1695 p_data => p_msg_data);
1696 --
1697 when FND_API.G_EXC_UNEXPECTED_ERROR then
1698 --
1699 rollback to Copy_Years_In_Calendar;
1700 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1701 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1702 p_data => p_msg_data);
1703 --
1704 when OTHERS then
1705 --
1706 rollback to Copy_Years_In_Calendar ;
1707 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1708 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1709 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1710 l_api_name);
1711 end if;
1712 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1713 p_data => p_msg_data);
1714 --
1715 END Copy_Years_In_Calendar;
1716 --
1717 --
1718
1719 PROCEDURE Check_Used_In_WS
1720 (p_calendar_id IN NUMBER,
1721 p_return_status OUT NOCOPY VARCHAR2) IS
1722 --
1723 l_type_count NUMBER ;
1724 --
1725 BEGIN
1726 --
1727 SELECT count(*)
1728 INTO l_type_count
1729 FROM PSB_WORKSHEETS
1730 WHERE budget_calendar_id = p_calendar_id;
1731 --
1732 IF l_type_count > 0 THEN
1733 FND_MESSAGE.SET_NAME('PSB', 'PSB_CALENDAR_USED_IN_WORKSHEET');
1734 FND_MSG_PUB.Add ;
1735 RAISE FND_API.G_EXC_ERROR ;
1736 END IF;
1737 --
1738 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1739 --
1740 EXCEPTION
1741 --
1742 when FND_API.G_EXC_ERROR then
1743 --
1744 p_return_status := FND_API.G_RET_STS_ERROR;
1745 --
1746 when FND_API.G_EXC_UNEXPECTED_ERROR then
1747 --
1748 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1749 --
1750 END Check_Used_In_WS;
1751
1752 /* ----------------------------------------------------------------------- */
1753
1754 -- Get Debug Information
1755
1756 -- This Module is used to retrieve Debug Information for Funds Checker. It
1757 -- prints Debug Information when run as a Batch Process from SQL*Plus. For
1758 -- the Debug Information to be printed on the Screen, the SQL*Plus parameter
1759 -- 'Serveroutput' should be set to 'ON'
1760
1761 FUNCTION get_debug RETURN VARCHAR2 IS
1762
1763 BEGIN
1764
1765 return(g_dbug);
1766
1767 END get_debug;
1768
1769 /* ----------------------------------------------------------------------- */
1770
1771 END PSB_BUDGET_PERIOD_PVT ;