1 PACKAGE BODY PSB_Year_Type_PVT AS
2 /* $Header: PSBVYTPB.pls 120.2 2005/07/13 11:31:51 shtripat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_Year_Type_PVT';
5 G_DBUG VARCHAR2(1500);
6
7 /* ----------------------------------------------------------------------- */
8
9 PROCEDURE Check_Unique_Sequence
10 ( p_api_version IN NUMBER,
11 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
12 p_commit IN VARCHAR2 := FND_API.G_FALSE,
13 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
14 p_return_status OUT NOCOPY VARCHAR2,
15 p_msg_count OUT NOCOPY NUMBER,
16 p_msg_data OUT NOCOPY VARCHAR2,
17 p_year_type_id IN NUMBER,
18 p_year_type_seq IN NUMBER
19 ) IS
20
21 l_api_name CONSTANT VARCHAR2(30) := 'Check_Unique_Sequence';
22 l_api_version CONSTANT NUMBER := 1.0;
23 l_seq_count NUMBER ;
24
25 BEGIN
26
27 -- Standard Start of API savepoint
28
29 SAVEPOINT Check_Unique_Sequence;
30
31 -- Standard call to check for call compatibility.
32
33 if not FND_API.Compatible_API_Call (l_api_version,
34 p_api_version,
35 l_api_name,
36 G_PKG_NAME)
37 then
38 raise FND_API.G_EXC_UNEXPECTED_ERROR;
39 end if;
40
41 -- Initialize message list if p_init_msg_list is set to TRUE.
42
43 if FND_API.to_Boolean (p_init_msg_list) then
44 FND_MSG_PUB.initialize;
45 end if;
46
47 -- Initialize API return status to success
48
49 p_return_status := FND_API.G_RET_STS_SUCCESS;
50
51 --
52 SELECT count(*)
53 INTO l_seq_count
54 FROM psb_budget_year_types
55 WHERE sequence_number = p_year_type_seq
56 AND ((p_year_type_id IS NULL ) OR
57 ( budget_year_type_id <> p_year_type_id)) ;
58 --
59 if l_seq_count > 0 then
60 FND_MESSAGE.Set_Name('PSB', 'PSB_DUP_YEAR_TYPE_SEQ');
61 FND_MSG_PUB.Add;
62 RAISE FND_API.G_EXC_ERROR;
63 end if;
64 --
65
66 -- Standard check of p_commit.
67
68 if FND_API.to_Boolean (p_commit) then
69 commit work;
70 end if;
71
72 -- Standard call to get message count and if count is 1, get message info.
73
74 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
75 p_data => p_msg_data);
76
77 EXCEPTION
78
79 when FND_API.G_EXC_ERROR then
80
81 rollback to Check_Unique_Sequence;
82
83 p_return_status := FND_API.G_RET_STS_ERROR;
84
85 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
86 p_data => p_msg_data);
87
88
89 when FND_API.G_EXC_UNEXPECTED_ERROR then
90
91 rollback to Check_Unique_Sequence ;
92
93 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
94
95 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
96 p_data => p_msg_data);
97
98
99 when OTHERS then
100
101 rollback to Check_Unique_Sequence;
102
103 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
104
105 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
106
107 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
108 l_api_name);
109 end if;
110
111 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
112 p_data => p_msg_data);
113
114 END Check_Unique_Sequence;
115 --
116 --
117 PROCEDURE Check_Sequence
118 ( p_api_version IN NUMBER,
119 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
120 p_commit IN VARCHAR2 := FND_API.G_FALSE,
121 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
122 p_return_status OUT NOCOPY VARCHAR2,
123 p_msg_count OUT NOCOPY NUMBER,
124 p_msg_data OUT NOCOPY VARCHAR2,
125 p_year_type IN VARCHAR2,
126 p_year_type_seq In NUMBER) IS
127
128 l_api_name CONSTANT VARCHAR2(30) := 'Check_Sequence';
129 l_api_version CONSTANT NUMBER := 1.0;
130 l_py_max NUMBER;
131 l_pp_min NUMBER;
132 l_cy_min NUMBER;
133 l_dummy NUMBER;
134 --
135 CURSOR ytp_csr IS
136 SELECT max(decode(year_category_type, 'PY', sequence_number)),
137 min(decode(year_category_type, 'PP', sequence_number)),
138 min(decode(year_category_type, 'CY', sequence_number)),
139 1
140 FROM psb_budget_year_types
141 GROUP BY 1 ;
142 --
143 BEGIN
144
145 -- Standard Start of API savepoint
146
147 SAVEPOINT Check_Sequence;
148
149 -- Standard call to check for call compatibility.
150
151 if not FND_API.Compatible_API_Call (l_api_version,
152 p_api_version,
153 l_api_name,
154 G_PKG_NAME)
155 then
156 raise FND_API.G_EXC_UNEXPECTED_ERROR;
157 end if;
158
159 -- Initialize message list if p_init_msg_list is set to TRUE.
160
161 if FND_API.to_Boolean (p_init_msg_list) then
162 FND_MSG_PUB.initialize;
163 end if;
164
165 -- Initialize API return status to success
166
167 p_return_status := FND_API.G_RET_STS_SUCCESS;
168
169 -- API body
170 open ytp_csr ;
171 fetch ytp_csr into l_py_max, l_pp_min, l_cy_min, l_dummy ;
172 close ytp_csr ;
173 --
174 if p_year_type = 'PY' then
175 if p_year_type_seq > l_cy_min then
176 FND_MESSAGE.Set_name ('PSB', 'PSB_PY_GREATER_THAN_CY');
177 FND_MSG_PUB.Add;
178 RAISE FND_API.G_EXC_ERROR;
179 end if;
180 --
181 if p_year_type_seq > l_pp_min then
182 FND_MESSAGE.Set_name ('PSB', 'PSB_PY_GREATER_THAN_PP');
183 FND_MSG_PUB.Add;
184 RAISE FND_API.G_EXC_ERROR;
185 end if;
186 end if;
187 --
188 --
189 if p_year_type = 'CY' then
190 if p_year_type_seq < l_py_max then
191 FND_MESSAGE.Set_name ('PSB', 'PSB_PY_GREATER_THAN_CY');
192 FND_MSG_PUB.Add;
193 RAISE FND_API.G_EXC_ERROR;
194 end if;
195 --
196 if p_year_type_seq > l_pp_min then
197 FND_MESSAGE.Set_name ('PSB', 'PSB_CY_GREATER_THAN_PP');
198 FND_MSG_PUB.Add;
199 RAISE FND_API.G_EXC_ERROR;
200 end if;
201 end if;
202 --
203 --
204 if p_year_type = 'PP' then
205 if p_year_type_seq < l_cy_min then
206 FND_MESSAGE.Set_name ('PSB', 'PSB_CY_GREATER_THAN_PP');
207 FND_MSG_PUB.Add;
208 RAISE FND_API.G_EXC_ERROR;
209 end if;
210 --
211 if p_year_type_seq < l_py_max then
212 FND_MESSAGE.Set_name ('PSB', 'PSB_PY_GREATER_THAN_PP');
213 FND_MSG_PUB.Add;
214 RAISE FND_API.G_EXC_ERROR;
215 end if;
216 end if;
217 --
218 -- End of API body.
219
220 -- Standard check of p_commit.
221
222 if FND_API.to_Boolean (p_commit) then
223 commit work;
224 end if;
225
226 -- Standard call to get message count and if count is 1, get message info.
227
228 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
229 p_data => p_msg_data);
230
231 EXCEPTION
232
233 when FND_API.G_EXC_ERROR then
234
235 rollback to Check_Sequence ;
236
237 p_return_status := FND_API.G_RET_STS_ERROR;
238
239 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
240 p_data => p_msg_data);
241
242 when FND_API.G_EXC_UNEXPECTED_ERROR then
243
244 rollback to Check_Sequence;
245
246 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
247
248 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
249 p_data => p_msg_data);
250
251
252 when OTHERS then
253
254 rollback to Check_Sequence;
255
256 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
257
258 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
259
260 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
261 l_api_name);
262 end if;
263
264 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
265 p_data => p_msg_data);
266
267 END Check_Sequence;
268 --
269 --
270 PROCEDURE Check_CY_Count
271 ( p_api_version IN NUMBER,
272 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
273 p_commit IN VARCHAR2 := FND_API.G_FALSE,
274 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
275 p_return_status OUT NOCOPY VARCHAR2,
276 p_msg_count OUT NOCOPY NUMBER,
277 p_msg_data OUT NOCOPY VARCHAR2,
278 p_year_type_id IN NUMBER)
279 IS
280
281 l_api_name CONSTANT VARCHAR2(30) := 'Check_CY_Count';
282 l_api_version CONSTANT NUMBER := 1.0;
283 l_cy_count NUMBER ;
284
285 BEGIN
286
287 -- Standard Start of API savepoint
288
289 SAVEPOINT Check_CY_Count;
290
291 -- Standard call to check for call compatibility.
292
293 if not FND_API.Compatible_API_Call (l_api_version,
294 p_api_version,
295 l_api_name,
296 G_PKG_NAME)
297 then
298 raise FND_API.G_EXC_UNEXPECTED_ERROR;
299 end if;
300
301 -- Initialize message list if p_init_msg_list is set to TRUE.
302
303 if FND_API.to_Boolean (p_init_msg_list) then
304 FND_MSG_PUB.initialize;
305 end if;
306
307 -- Initialize API return status to success
308
309 p_return_status := FND_API.G_RET_STS_SUCCESS;
310
311 -- API body
312 SELECT count(*)
313 INTO l_cy_count
314 FROM psb_budget_year_types
315 WHERE year_category_type = 'CY'
316 AND ((p_year_type_id IS NULL) OR
317 (budget_year_type_id <> p_year_type_id));
318 --
319 if l_cy_count > 0 then
320 FND_MESSAGE.Set_Name('PSB', 'PSB_DUP_CY_TYPE');
321 FND_MSG_PUB.Add;
322 RAISE FND_API.G_EXC_ERROR ;
323 end if;
324 -- End of API body.
325
326 -- Standard check of p_commit.
327
328 if FND_API.to_Boolean (p_commit) then
329 commit work;
330 end if;
331
332 -- Standard call to get message count and if count is 1, get message info.
333
334 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
335 p_data => p_msg_data);
336
337 EXCEPTION
338
339 when FND_API.G_EXC_ERROR then
340
341 rollback to Check_CY_Count;
342
343 p_return_status := FND_API.G_RET_STS_ERROR;
344
345 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
346 p_data => p_msg_data);
347
348
349 when FND_API.G_EXC_UNEXPECTED_ERROR then
350
351 rollback to Check_CY_Count;
352
353 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
354
355 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
356 p_data => p_msg_data);
357
358
359 when OTHERS then
360
361 rollback to Check_CY_Count;
362
363 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
364
365 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
366
367 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
368 l_api_name);
369 end if;
370
371 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
372 p_data => p_msg_data);
373
374 END Check_CY_Count;
375 --
376 --
377 PROCEDURE Check_Reference
378 ( p_api_version IN NUMBER,
379 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
380 p_commit IN VARCHAR2 := FND_API.G_FALSE,
381 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
382 p_return_status OUT NOCOPY VARCHAR2,
383 p_msg_count OUT NOCOPY NUMBER,
384 p_msg_data OUT NOCOPY VARCHAR2,
385 p_year_type_id IN NUMBER
386 ) IS
387
388 l_api_name CONSTANT VARCHAR2(30) := 'Check_Reference';
389 l_api_version CONSTANT NUMBER := 1.0;
390 l_dummy NUMBER ;
391
392 BEGIN
393
394 -- Standard Start of API savepoint
395
396 SAVEPOINT Check_Ref_Integrity;
397
398 -- Standard call to check for call compatibility.
399
400 if not FND_API.Compatible_API_Call (l_api_version,
401 p_api_version,
402 l_api_name,
403 G_PKG_NAME)
404 then
405 raise FND_API.G_EXC_UNEXPECTED_ERROR;
406 end if;
407
408 -- Initialize message list if p_init_msg_list is set to TRUE.
409
410 if FND_API.to_Boolean (p_init_msg_list) then
411 FND_MSG_PUB.initialize;
412 end if;
413
414 -- Initialize API return status to success
415
416 p_return_status := FND_API.G_RET_STS_SUCCESS;
417
418 -- API body
419 SELECT 1
420 INTO l_dummy
421 FROM dual
422 WHERE NOT EXISTS
423 (SELECT 1
424 FROM psb_budget_periods
425 WHERE budget_year_type_id = p_year_type_id);
426 -- End of API body.
427
428 -- Standard check of p_commit.
429
430 if FND_API.to_Boolean (p_commit) then
431 commit work;
432 end if;
433
434 -- Standard call to get message count and if count is 1, get message info.
435
436 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
437 p_data => p_msg_data);
438
439 EXCEPTION
440
441 when NO_DATA_FOUND then
442
443 FND_MESSAGE.Set_Name('PSB', 'PSB_CANNOT_DELETE_YTP');
444 FND_MSG_PUB.Add;
445
446 p_return_status := FND_API.G_RET_STS_ERROR;
447
448 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
449 p_data => p_msg_data);
450
451
452 when FND_API.G_EXC_ERROR then
453
454 rollback to Check_Ref_Integrity;
455
456 p_return_status := FND_API.G_RET_STS_ERROR;
457
458 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
459 p_data => p_msg_data);
460
461
462 when FND_API.G_EXC_UNEXPECTED_ERROR then
463
464 rollback to Check_Ref_Integrity;
465
466 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
467
468 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
469 p_data => p_msg_data);
470
471
472 when OTHERS then
473
474 rollback to Check_Ref_Integrity;
475
476 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
477
478 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
479
480 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
481 l_api_name);
482 end if;
483
484
485 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
486 p_data => p_msg_data);
487
488 END Check_Reference ;
489 --
490 --
491 PROCEDURE Check_Unique_Name
492 ( p_api_version IN NUMBER,
493 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
494 p_commit IN VARCHAR2 := FND_API.G_FALSE,
498 p_msg_data OUT NOCOPY VARCHAR2,
495 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
496 p_return_status OUT NOCOPY VARCHAR2,
497 p_msg_count OUT NOCOPY NUMBER,
499 p_year_type_id IN NUMBER,
500 p_name IN VARCHAR2
501 ) IS
502
503 l_api_name CONSTANT VARCHAR2(30) := 'Check_Unique_Name';
504 l_api_version CONSTANT NUMBER := 1.0;
505 l_seq_count NUMBER ;
506
507 BEGIN
508
509 -- Standard Start of API savepoint
510
511 SAVEPOINT Check_Unique_Name;
512
513 -- Standard call to check for call compatibility.
514
515 if not FND_API.Compatible_API_Call (l_api_version,
516 p_api_version,
517 l_api_name,
518 G_PKG_NAME)
519 then
520 raise FND_API.G_EXC_UNEXPECTED_ERROR;
521 end if;
522
523 -- Initialize message list if p_init_msg_list is set to TRUE.
524
525 if FND_API.to_Boolean (p_init_msg_list) then
526 FND_MSG_PUB.initialize;
527 end if;
528
529 -- Initialize API return status to success
530
531 p_return_status := FND_API.G_RET_STS_SUCCESS;
532
533 --
534 SELECT count(*)
535 INTO l_seq_count
536 FROM psb_budget_year_types
537 WHERE name = p_name
538 AND ((p_year_type_id IS NULL ) OR
539 ( budget_year_type_id <> p_year_type_id)) ;
540 --
541 if l_seq_count > 0 then
542 FND_MESSAGE.Set_Name('PSB', 'PSB_DUPLICATE_NAME');
543 FND_MSG_PUB.Add;
544 RAISE FND_API.G_EXC_ERROR;
545 end if;
546 --
547
548 -- Standard check of p_commit.
549
550 if FND_API.to_Boolean (p_commit) then
551 commit work;
552 end if;
553
554 -- Standard call to get message count and if count is 1, get message info.
555
556 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
557 p_data => p_msg_data);
558
559 EXCEPTION
560
561 when FND_API.G_EXC_ERROR then
562
563 rollback to Check_Unique_Name;
564
565 p_return_status := FND_API.G_RET_STS_ERROR;
566
567 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
568 p_data => p_msg_data);
569
570
571 when FND_API.G_EXC_UNEXPECTED_ERROR then
572
573 rollback to Check_Unique_Name ;
574
575 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
576
577 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
578 p_data => p_msg_data);
579
580
581 when OTHERS then
582
583 rollback to Check_Unique_Name;
584
585 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
586
587 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
588
589 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
590 l_api_name);
591 end if;
592
593 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
594 p_data => p_msg_data);
595
596 END Check_Unique_Name;
597 --
598 --
599
600
601 /* ----------------------------------------------------------------------- */
602
603 -- Get Debug Information
604
605 -- This Module is used to retrieve Debug Information for this routine. It
606 -- prints Debug Information when run as a Batch Process from SQL*Plus. For
607 -- the Debug Information to be printed on the Screen, the SQL*Plus parameter
608 -- 'Serveroutput' should be set to 'ON'
609
610 FUNCTION get_debug RETURN VARCHAR2 IS
611
612 BEGIN
613
614 return(g_dbug);
615
616 END get_debug;
617
618 /* ----------------------------------------------------------------------- */
619
620 procedure INSERT_ROW
621 ( p_api_version IN NUMBER,
622 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
623 p_commit IN VARCHAR2 := FND_API.G_FALSE,
624 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
625 p_return_status OUT NOCOPY VARCHAR2,
626 p_msg_count OUT NOCOPY NUMBER,
627 p_msg_data OUT NOCOPY VARCHAR2,
628 --
629 p_ROWID in OUT NOCOPY VARCHAR2,
630 p_BUDGET_YEAR_TYPE_ID in NUMBER,
631 p_YEAR_CATEGORY_TYPE in VARCHAR2,
632 p_SEQUENCE_NUMBER in NUMBER,
633 p_NAME in VARCHAR2,
634 p_DESCRIPTION in VARCHAR2,
635 p_CREATION_DATE in DATE,
636 p_CREATED_BY in NUMBER,
637 p_LAST_UPDATE_DATE in DATE,
638 p_LAST_UPDATED_BY in NUMBER,
639 p_LAST_UPDATE_LOGIN in NUMBER
640 ) is
641
642 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_ROW';
643 l_api_version CONSTANT NUMBER := 1.0;
644 l_seq_count NUMBER ;
645
646 cursor C is select ROWID from PSB_BUDGET_YEAR_TYPES
647 where BUDGET_YEAR_TYPE_ID = p_BUDGET_YEAR_TYPE_ID
648 ;
649 BEGIN
650
651 -- Standard Start of API savepoint
652
653 SAVEPOINT Check_Unique_Name;
654
655 -- Standard call to check for call compatibility.
659 l_api_name,
656
657 if not FND_API.Compatible_API_Call (l_api_version,
658 p_api_version,
660 G_PKG_NAME)
661 then
662 raise FND_API.G_EXC_UNEXPECTED_ERROR;
663 end if;
664
665 -- Initialize message list if p_init_msg_list is set to TRUE.
666
667 if FND_API.to_Boolean (p_init_msg_list) then
668 FND_MSG_PUB.initialize;
669 end if;
670
671 -- Initialize API return status to success
672
673 p_return_status := FND_API.G_RET_STS_SUCCESS;
674
675 insert into PSB_BUDGET_YEAR_TYPES (
676 BUDGET_YEAR_TYPE_ID,
677 YEAR_CATEGORY_TYPE,
678 NAME,
679 SEQUENCE_NUMBER,
680 CREATION_DATE,
681 CREATED_BY,
682 LAST_UPDATE_DATE,
683 LAST_UPDATED_BY,
684 LAST_UPDATE_LOGIN
685 ) values (
686 p_BUDGET_YEAR_TYPE_ID,
687 p_YEAR_CATEGORY_TYPE,
688 p_NAME,
689 p_SEQUENCE_NUMBER,
690 p_CREATION_DATE,
691 p_CREATED_BY,
692 p_LAST_UPDATE_DATE,
693 p_LAST_UPDATED_BY,
694 p_LAST_UPDATE_LOGIN
695 );
696
697 insert into PSB_BUDGET_YEAR_TYPES_TL (
698 BUDGET_YEAR_TYPE_ID,
699 NAME,
700 DESCRIPTION,
701 LAST_UPDATE_DATE,
702 LAST_UPDATED_BY,
703 LAST_UPDATE_LOGIN,
704 CREATED_BY,
705 CREATION_DATE,
706 LANGUAGE,
707 SOURCE_LANG
708 ) select
709 p_BUDGET_YEAR_TYPE_ID,
710 p_NAME,
711 p_DESCRIPTION,
712 p_LAST_UPDATE_DATE,
713 p_LAST_UPDATED_BY,
714 p_LAST_UPDATE_LOGIN,
715 p_CREATED_BY,
716 p_CREATION_DATE,
717 L.LANGUAGE_CODE,
718 userenv('LANG')
719 from FND_LANGUAGES L
720 where L.INSTALLED_FLAG in ('I', 'B')
721 and not exists
722 (select NULL
723 from PSB_BUDGET_YEAR_TYPES_TL T
724 where T.BUDGET_YEAR_TYPE_ID = p_BUDGET_YEAR_TYPE_ID
725 and T.LANGUAGE = L.LANGUAGE_CODE);
726
727 open c;
728 fetch c into p_ROWID;
729 if (c%notfound) then
730 close c;
731 raise no_data_found;
732 end if;
733 close c;
734
735 -- Standard check of p_commit.
736
737 if FND_API.to_Boolean (p_commit) then
738 commit work;
739 end if;
740
741 -- Standard call to get message count and if count is 1, get message info.
742
743 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
744 p_data => p_msg_data);
745
746 EXCEPTION
747
748 when FND_API.G_EXC_ERROR then
749
750 rollback to Check_Unique_Name;
751
752 p_return_status := FND_API.G_RET_STS_ERROR;
753
754 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
755 p_data => p_msg_data);
756
757
758 when FND_API.G_EXC_UNEXPECTED_ERROR then
759
760 rollback to Check_Unique_Name ;
761
762 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
763
764 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
765 p_data => p_msg_data);
766
767
768 when OTHERS then
769
770 rollback to Check_Unique_Name;
771
772 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
773
774 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
775
776 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
777 l_api_name);
778 end if;
779
780 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
781 p_data => p_msg_data);
782
783 end INSERT_ROW;
784
785
786 procedure LOCK_ROW
787 ( p_api_version IN NUMBER,
788 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
789 p_commit IN VARCHAR2 := FND_API.G_FALSE,
790 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
791 p_return_status OUT NOCOPY VARCHAR2,
792 p_msg_count OUT NOCOPY NUMBER,
793 p_msg_data OUT NOCOPY VARCHAR2,
794 --
795 p_BUDGET_YEAR_TYPE_ID in NUMBER,
796 p_YEAR_CATEGORY_TYPE in VARCHAR2,
797 p_SEQUENCE_NUMBER in NUMBER,
798 p_NAME in VARCHAR2,
799 p_DESCRIPTION in VARCHAR2
800 ) is
801 cursor c is select
802 YEAR_CATEGORY_TYPE,
803 SEQUENCE_NUMBER
804 from PSB_BUDGET_YEAR_TYPES
805 where BUDGET_YEAR_TYPE_ID = p_BUDGET_YEAR_TYPE_ID
806 for update of BUDGET_YEAR_TYPE_ID nowait;
807 recinfo c%rowtype;
808
809 cursor c1 is select
810 NAME,
811 DESCRIPTION,
812 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
813 from PSB_BUDGET_YEAR_TYPES_TL
814 where BUDGET_YEAR_TYPE_ID = p_BUDGET_YEAR_TYPE_ID
815 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
816 for update of BUDGET_YEAR_TYPE_ID nowait;
817
818 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_ROW';
819 l_api_version CONSTANT NUMBER := 1.0;
820
821 BEGIN
822
823 -- Standard Start of API savepoint
824
825 SAVEPOINT Check_Unique_Name;
826
830 p_api_version,
827 -- Standard call to check for call compatibility.
828
829 if not FND_API.Compatible_API_Call (l_api_version,
831 l_api_name,
832 G_PKG_NAME)
833 then
834 raise FND_API.G_EXC_UNEXPECTED_ERROR;
835 end if;
836
837 -- Initialize message list if p_init_msg_list is set to TRUE.
838
839 if FND_API.to_Boolean (p_init_msg_list) then
840 FND_MSG_PUB.initialize;
841 end if;
842
843 -- Initialize API return status to success
844
845 p_return_status := FND_API.G_RET_STS_SUCCESS;
846
847 open c;
848 fetch c into recinfo;
849 if (c%notfound) then
850 close c;
851 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
852 app_exception.raise_exception;
853 end if;
854 close c;
855 if ( (recinfo.YEAR_CATEGORY_TYPE = p_YEAR_CATEGORY_TYPE)
856 AND (recinfo.SEQUENCE_NUMBER = p_SEQUENCE_NUMBER)
857 ) then
858 null;
859 else
860 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
861 app_exception.raise_exception;
862 end if;
863
864 for tlinfo in c1 loop
865 if (tlinfo.BASELANG = 'Y') then
866 if ( (tlinfo.NAME = p_NAME)
867 AND (tlinfo.DESCRIPTION = p_DESCRIPTION)
868 ) then
869 null;
870 else
871 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
872 app_exception.raise_exception;
873 end if;
874 end if;
875 end loop;
876 return;
877
878 -- Standard check of p_commit.
879
880 if FND_API.to_Boolean (p_commit) then
881 commit work;
882 end if;
883
884 -- Standard call to get message count and if count is 1, get message info.
885
886 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
887 p_data => p_msg_data);
888
889 EXCEPTION
890
891 when FND_API.G_EXC_ERROR then
892
893 rollback to Check_Unique_Name;
894
895 p_return_status := FND_API.G_RET_STS_ERROR;
896
897 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
898 p_data => p_msg_data);
899
900
901 when FND_API.G_EXC_UNEXPECTED_ERROR then
902
903 rollback to Check_Unique_Name ;
904
905 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
906
907 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
908 p_data => p_msg_data);
909
910
911 when OTHERS then
912
913 rollback to Check_Unique_Name;
914
915 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
916
917 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
918
919 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
920 l_api_name);
921 end if;
922
923 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
924 p_data => p_msg_data);
925
926 end LOCK_ROW;
927
928
929 procedure UPDATE_ROW
930 ( p_api_version IN NUMBER,
931 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
932 p_commit IN VARCHAR2 := FND_API.G_FALSE,
933 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
934 p_return_status OUT NOCOPY VARCHAR2,
935 p_msg_count OUT NOCOPY NUMBER,
936 p_msg_data OUT NOCOPY VARCHAR2,
937 --
938 p_BUDGET_YEAR_TYPE_ID in NUMBER,
939 p_YEAR_CATEGORY_TYPE in VARCHAR2,
940 p_SEQUENCE_NUMBER in NUMBER,
941 p_NAME in VARCHAR2,
942 p_DESCRIPTION in VARCHAR2,
943 p_LAST_UPDATE_DATE in DATE,
944 p_LAST_UPDATED_BY in NUMBER,
945 p_LAST_UPDATE_LOGIN in NUMBER
946 ) is
947
948 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ROW';
949 l_api_version CONSTANT NUMBER := 1.0;
950 l_seq_count NUMBER ;
951
952 BEGIN
953
954 -- Standard Start of API savepoint
955
956 SAVEPOINT Check_Unique_Name;
957
958 -- Standard call to check for call compatibility.
959
960 if not FND_API.Compatible_API_Call (l_api_version,
961 p_api_version,
962 l_api_name,
963 G_PKG_NAME)
964 then
965 raise FND_API.G_EXC_UNEXPECTED_ERROR;
966 end if;
967
968 -- Initialize message list if p_init_msg_list is set to TRUE.
969
970 if FND_API.to_Boolean (p_init_msg_list) then
971 FND_MSG_PUB.initialize;
972 end if;
973
974 -- Initialize API return status to success
975
976 p_return_status := FND_API.G_RET_STS_SUCCESS;
977
978 update PSB_BUDGET_YEAR_TYPES set
979 YEAR_CATEGORY_TYPE = p_YEAR_CATEGORY_TYPE,
980 SEQUENCE_NUMBER = p_SEQUENCE_NUMBER,
981 LAST_UPDATE_DATE = p_LAST_UPDATE_DATE,
982 LAST_UPDATED_BY = p_LAST_UPDATED_BY,
983 LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
984 where BUDGET_YEAR_TYPE_ID = p_BUDGET_YEAR_TYPE_ID;
985
986 if (sql%notfound) then
987 raise no_data_found;
988 end if;
989
990 update PSB_BUDGET_YEAR_TYPES_TL set
991 NAME = p_NAME,
995 LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN,
992 DESCRIPTION = p_DESCRIPTION,
993 LAST_UPDATE_DATE = p_LAST_UPDATE_DATE,
994 LAST_UPDATED_BY = p_LAST_UPDATED_BY,
996 SOURCE_LANG = userenv('LANG')
997 where BUDGET_YEAR_TYPE_ID = p_BUDGET_YEAR_TYPE_ID
998 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
999
1000 if (sql%notfound) then
1001 raise no_data_found;
1002 end if;
1003
1004 -- Standard check of p_commit.
1005
1006 if FND_API.to_Boolean (p_commit) then
1007 commit work;
1008 end if;
1009
1010 -- Standard call to get message count and if count is 1, get message info.
1011
1012 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1013 p_data => p_msg_data);
1014
1015 EXCEPTION
1016
1017 when FND_API.G_EXC_ERROR then
1018
1019 rollback to Check_Unique_Name;
1020
1021 p_return_status := FND_API.G_RET_STS_ERROR;
1022
1023 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1024 p_data => p_msg_data);
1025
1026
1027 when FND_API.G_EXC_UNEXPECTED_ERROR then
1028
1029 rollback to Check_Unique_Name ;
1030
1031 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1032
1033 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1034 p_data => p_msg_data);
1035
1036
1037 when OTHERS then
1038
1039 rollback to Check_Unique_Name;
1040
1041 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1042
1043 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1044
1045 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1046 l_api_name);
1047 end if;
1048
1049 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1050 p_data => p_msg_data);
1051
1052 end UPDATE_ROW;
1053
1054 procedure DELETE_ROW
1055 ( p_api_version IN NUMBER,
1056 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1057 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1058 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1059 p_return_status OUT NOCOPY VARCHAR2,
1060 p_msg_count OUT NOCOPY NUMBER,
1061 p_msg_data OUT NOCOPY VARCHAR2,
1062 p_BUDGET_YEAR_TYPE_ID in NUMBER
1063 ) is
1064
1065 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ROW';
1066 l_api_version CONSTANT NUMBER := 1.0;
1067
1068 BEGIN
1069
1070 -- Standard Start of API savepoint
1071
1072 SAVEPOINT Check_Unique_Name;
1073
1074 -- Standard call to check for call compatibility.
1075
1076 if not FND_API.Compatible_API_Call (l_api_version,
1077 p_api_version,
1078 l_api_name,
1079 G_PKG_NAME)
1080 then
1081 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1082 end if;
1083
1084 -- Initialize message list if p_init_msg_list is set to TRUE.
1085
1086 if FND_API.to_Boolean (p_init_msg_list) then
1087 FND_MSG_PUB.initialize;
1088 end if;
1089
1090 -- Initialize API return status to success
1091
1092 p_return_status := FND_API.G_RET_STS_SUCCESS;
1093
1094 delete from PSB_BUDGET_YEAR_TYPES_TL
1095 where BUDGET_YEAR_TYPE_ID = p_BUDGET_YEAR_TYPE_ID;
1096
1097 if (sql%notfound) then
1098 raise no_data_found;
1099 end if;
1100
1101 delete from PSB_BUDGET_YEAR_TYPES
1102 where BUDGET_YEAR_TYPE_ID = p_BUDGET_YEAR_TYPE_ID;
1103
1104 if (sql%notfound) then
1105 raise no_data_found;
1106 end if;
1107
1108 -- Standard check of p_commit.
1109
1110 if FND_API.to_Boolean (p_commit) then
1111 commit work;
1112 end if;
1113
1114 -- Standard call to get message count and if count is 1, get message info.
1115
1116 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1117 p_data => p_msg_data);
1118
1119 EXCEPTION
1120
1121 when FND_API.G_EXC_ERROR then
1122
1123 rollback to Check_Unique_Name;
1124
1125 p_return_status := FND_API.G_RET_STS_ERROR;
1126
1127 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1128 p_data => p_msg_data);
1129
1130
1131 when FND_API.G_EXC_UNEXPECTED_ERROR then
1132
1133 rollback to Check_Unique_Name ;
1134
1135 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1136
1137 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1138 p_data => p_msg_data);
1139
1140
1141 when OTHERS then
1142
1143 rollback to Check_Unique_Name;
1144
1145 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1146
1147 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1148
1149 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1150 l_api_name);
1151 end if;
1152
1153 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1154 p_data => p_msg_data);
1155
1156 end DELETE_ROW;
1157
1158 procedure ADD_LANGUAGE
1159 is
1160 begin
1161 delete from PSB_BUDGET_YEAR_TYPES_TL T
1162 where not exists
1163 (select NULL
1164 from PSB_BUDGET_YEAR_TYPES B
1165 where B.BUDGET_YEAR_TYPE_ID = T.BUDGET_YEAR_TYPE_ID
1166 );
1167
1168 update PSB_BUDGET_YEAR_TYPES_TL T set (
1169 NAME,
1170 DESCRIPTION
1171 ) = (select
1172 B.NAME,
1173 B.DESCRIPTION
1174 from PSB_BUDGET_YEAR_TYPES_TL B
1175 where B.BUDGET_YEAR_TYPE_ID = T.BUDGET_YEAR_TYPE_ID
1176 and B.LANGUAGE = T.SOURCE_LANG)
1177 where (
1178 T.BUDGET_YEAR_TYPE_ID,
1179 T.LANGUAGE
1180 ) in (select
1181 SUBT.BUDGET_YEAR_TYPE_ID,
1182 SUBT.LANGUAGE
1183 from PSB_BUDGET_YEAR_TYPES_TL SUBB, PSB_BUDGET_YEAR_TYPES_TL SUBT
1184 where SUBB.BUDGET_YEAR_TYPE_ID = SUBT.BUDGET_YEAR_TYPE_ID
1185 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1186 and (SUBB.NAME <> SUBT.NAME
1187 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
1188 ));
1189
1190 insert into PSB_BUDGET_YEAR_TYPES_TL (
1191 BUDGET_YEAR_TYPE_ID,
1192 NAME,
1193 DESCRIPTION,
1194 LAST_UPDATE_DATE,
1195 LAST_UPDATED_BY,
1196 LAST_UPDATE_LOGIN,
1197 CREATED_BY,
1198 CREATION_DATE,
1199 LANGUAGE,
1200 SOURCE_LANG
1201 ) select
1202 B.BUDGET_YEAR_TYPE_ID,
1203 B.NAME,
1204 B.DESCRIPTION,
1205 B.LAST_UPDATE_DATE,
1206 B.LAST_UPDATED_BY,
1207 B.LAST_UPDATE_LOGIN,
1208 B.CREATED_BY,
1209 B.CREATION_DATE,
1210 L.LANGUAGE_CODE,
1211 B.SOURCE_LANG
1212 from PSB_BUDGET_YEAR_TYPES_TL B, FND_LANGUAGES L
1213 where L.INSTALLED_FLAG in ('I', 'B')
1214 and B.LANGUAGE = userenv('LANG')
1215 and not exists
1216 (select NULL
1217 from PSB_BUDGET_YEAR_TYPES_TL T
1218 where T.BUDGET_YEAR_TYPE_ID = B.BUDGET_YEAR_TYPE_ID
1219 and T.LANGUAGE = L.LANGUAGE_CODE);
1220 end ADD_LANGUAGE;
1221
1222 END PSB_Year_Type_PVT ;
[Home] [Help]