[Home] [Help]
PACKAGE BODY: APPS.PSB_DEFAULTS_PVT
Source
1 PACKAGE BODY PSB_DEFAULTS_PVT AS
2 /* $Header: PSBVPDFB.pls 120.3 2004/11/30 14:18:26 shtripat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_DEFAULTS_PVT';
5
6 /* ----------------------------------------------------------------------- */
7
8 PROCEDURE INSERT_ROW
9 ( p_api_version IN NUMBER,
10 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
11 p_commit IN VARCHAR2 := FND_API.G_FALSE,
12 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
13 p_return_status OUT NOCOPY VARCHAR2,
14 p_msg_count OUT NOCOPY NUMBER,
15 p_msg_data OUT NOCOPY VARCHAR2,
16 --
17 P_ROW_ID IN OUT NOCOPY VARCHAR2,
18 P_DEFAULT_RULE_ID in NUMBER,
19 P_NAME in VARCHAR2,
20 P_GLOBAL_DEFAULT_FLAG IN VARCHAR2,
21 P_DATA_EXTRACT_ID IN NUMBER,
22 P_BUSINESS_GROUP_ID IN NUMBER,
23 P_ENTITY_ID IN NUMBER,
24 P_PRIORITY IN NUMBER,
25 P_CREATION_DATE in DATE,
26 P_CREATED_BY in NUMBER,
27 P_LAST_UPDATE_DATE in DATE,
28 P_LAST_UPDATED_BY in NUMBER,
29 P_LAST_UPDATE_LOGIN in NUMBER,
30 /* Bug 1308558 Start */
31 P_OVERWRITE IN VARCHAR2 DEFAULT NULL
32 /* Bug 1308558 End */
33 ) IS
34
35 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_ROW';
36 l_api_version CONSTANT NUMBER := 1.0;
37 l_row_id varchar2(40);
38 --
39 cursor c1 is
40 select ROWID from psb_defaults
41 where default_rule_id = p_default_rule_id;
42
43 BEGIN
44
45 -- Standard Start of API savepoint
46
47 SAVEPOINT INSERT_ROW_PVT;
48
49 -- Standard call to check for call compatibility.
50
51 if not FND_API.Compatible_API_Call (l_api_version,
52 p_api_version,
53 l_api_name,
54 G_PKG_NAME)
55 then
56 raise FND_API.G_EXC_UNEXPECTED_ERROR;
57 end if;
58
59 -- Initialize message list if p_init_msg_list is set to TRUE.
60
61 if FND_API.to_Boolean (p_init_msg_list) then
62 FND_MSG_PUB.initialize;
63 end if;
64
65 -- Initialize API return status to success
66
67 p_return_status := FND_API.G_RET_STS_SUCCESS;
68
69
70 -- API body
71 INSERT INTO psb_defaults
72 (
73 DEFAULT_RULE_ID ,
74 NAME ,
75 GLOBAL_DEFAULT_FLAG ,
76 DATA_EXTRACT_ID ,
77 BUSINESS_GROUP_ID ,
78 ENTITY_ID ,
79 PRIORITY ,
80 CREATION_DATE ,
81 CREATED_BY ,
82 LAST_UPDATE_DATE ,
83 LAST_UPDATED_BY ,
84 LAST_UPDATE_LOGIN ,
85 /* Bug 1308558 Start */
86 OVERWRITE
87 )
88 VALUES
89 (
90 P_DEFAULT_RULE_ID ,
91 P_NAME ,
92 P_GLOBAL_DEFAULT_FLAG ,
93 P_DATA_EXTRACT_ID ,
94 P_BUSINESS_GROUP_ID ,
95 P_ENTITY_ID ,
96 P_PRIORITY ,
97 P_CREATION_DATE ,
98 P_CREATED_BY ,
99 P_LAST_UPDATE_DATE ,
100 P_LAST_UPDATED_BY ,
101 P_LAST_UPDATE_LOGIN ,
102 P_OVERWRITE
103 );
104
105 open c1;
106 fetch c1 into P_ROW_ID;
107 if (c1%notfound) then
108 close c1;
109
110 FND_MESSAGE.Set_Name('PSB', 'PSB_NO_DATA_FOUND');
111 FND_MSG_PUB.Add;
112 RAISE FND_API.G_EXC_ERROR ;
113 end if;
114 -- End of API body.
115
116 -- Standard check of p_commit.
117
118 if FND_API.to_Boolean (p_commit) then
119 commit work;
120 end if;
121
122 -- Standard call to get message count and if count is 1, get message info.
123
124 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
125 p_data => p_msg_data);
126
127 EXCEPTION
128
129 when FND_API.G_EXC_ERROR then
130
131 rollback to INSERT_ROW_PVT;
132
133 p_return_status := FND_API.G_RET_STS_ERROR;
134
135 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
136 p_data => p_msg_data);
137
138
139 when FND_API.G_EXC_UNEXPECTED_ERROR then
140
141 rollback to INSERT_ROW_PVT;
142
143 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
144
145 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
146 p_data => p_msg_data);
147
148
149 when OTHERS then
150
151 rollback to INSERT_ROW_PVT;
152
153 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
154
155 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
156
157 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
158 l_api_name);
159 end if;
160
161 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
162 p_data => p_msg_data);
163
164 END INSERT_ROW;
165
166 PROCEDURE UPDATE_ROW
167 ( p_api_version IN NUMBER,
168 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
169 p_commit IN VARCHAR2 := FND_API.G_FALSE,
170 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
171 p_return_status OUT NOCOPY VARCHAR2,
172 p_msg_count OUT NOCOPY NUMBER,
173 p_msg_data OUT NOCOPY VARCHAR2,
174 --
175 P_ROW_ID IN VARCHAR2,
176 P_DEFAULT_RULE_ID in NUMBER,
177 P_NAME in VARCHAR2,
178 P_GLOBAL_DEFAULT_FLAG IN VARCHAR2,
179 P_DATA_EXTRACT_ID IN NUMBER,
180 P_BUSINESS_GROUP_ID IN NUMBER,
181 P_ENTITY_ID IN NUMBER,
182 P_PRIORITY IN NUMBER,
183 P_LAST_UPDATE_DATE in DATE,
184 P_LAST_UPDATED_BY in NUMBER,
185 P_LAST_UPDATE_LOGIN in NUMBER,
186 /* Bug 1308558 Start */
187 P_OVERWRITE IN VARCHAR2 DEFAULT NULL
188 /* Bug 1308558 End */
189 ) IS
190
191 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ROW';
192 l_api_version CONSTANT NUMBER := 1.0;
193
194 BEGIN
195
196 -- Standard Start of API savepoint
197
198 SAVEPOINT UPDATE_ROW_PVT;
199
200 -- Standard call to check for call compatibility.
201
202 if not FND_API.Compatible_API_Call (l_api_version,
203 p_api_version,
204 l_api_name,
205 G_PKG_NAME)
206 then
207 raise FND_API.G_EXC_UNEXPECTED_ERROR;
208 end if;
209
210 -- Initialize message list if p_init_msg_list is set to TRUE.
211
212 if FND_API.to_Boolean (p_init_msg_list) then
213 FND_MSG_PUB.initialize;
214 end if;
215
216 -- Initialize API return status to success
217
218 p_return_status := FND_API.G_RET_STS_SUCCESS;
219
220 -- API body
221 UPDATE psb_defaults SET
222 DEFAULT_RULE_ID = P_DEFAULT_RULE_ID,
223 NAME = P_NAME,
224 GLOBAL_DEFAULT_FLAG = P_GLOBAL_DEFAULT_FLAG,
225 DATA_EXTRACT_ID = P_DATA_EXTRACT_ID,
226 BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID,
227 ENTITY_ID = P_ENTITY_ID,
228 PRIORITY = P_PRIORITY,
229 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
230 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
231 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
232 /* Bug 1308558 Start */
233 OVERWRITE = P_OVERWRITE
234 /* Bug 1308558 End */
235
236 WHERE rowid = p_row_id;
237
238 if (SQL%NOTFOUND) then
239 RAISE NO_DATA_FOUND;
240 end if;
241
242 -- End of API body.
243
244 -- Standard check of p_commit.
245
246 if FND_API.to_Boolean (p_commit) then
247 commit work;
248 end if;
249
250 -- Standard call to get message count and if count is 1, get message info.
251
252 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
253 p_data => p_msg_data);
254
255 EXCEPTION
256
257 when FND_API.G_EXC_ERROR then
258
259 rollback to UPDATE_ROW_PVT;
260
261 p_return_status := FND_API.G_RET_STS_ERROR;
262
263 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
264 p_data => p_msg_data);
265
266
267 when FND_API.G_EXC_UNEXPECTED_ERROR then
268
269 rollback to UPDATE_ROW_PVT;
270
271 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
272
273 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
274 p_data => p_msg_data);
275
276
277 when OTHERS then
278
279 rollback to UPDATE_ROW_PVT;
280
281 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
282
283 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
284
285 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
286 l_api_name);
287 end if;
288
289 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
290 p_data => p_msg_data);
291
292 END UPDATE_ROW;
293
294
295 PROCEDURE DELETE_ROW
296 ( p_api_version IN NUMBER,
297 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
298 p_commit IN VARCHAR2 := FND_API.G_FALSE,
299 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
300 p_return_status OUT NOCOPY VARCHAR2,
301 p_msg_count OUT NOCOPY NUMBER,
302 p_msg_data OUT NOCOPY VARCHAR2,
303 --
304 P_DEFAULT_RULE_ID IN NUMBER,
305 P_ENTITY_ID IN NUMBER,
306 /* Bug 1308558 Start */
307 P_SOURCE_FORM IN VARCHAR2 DEFAULT NULL
308 /* Bug 1308558 End */
309
310 ) IS
311
312 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ROW';
313 l_api_version CONSTANT NUMBER := 1.0;
314
315 BEGIN
316
317 -- Standard Start of API savepoint
318
319 SAVEPOINT DELETE_ROW_PVT;
320
321 -- Standard call to check for call compatibility.
322
323 if not FND_API.Compatible_API_Call (l_api_version,
324 p_api_version,
325 l_api_name,
326 G_PKG_NAME)
327 then
328 raise FND_API.G_EXC_UNEXPECTED_ERROR;
329 end if;
330
331 -- Initialize message list if p_init_msg_list is set to TRUE.
332
333 if FND_API.to_Boolean (p_init_msg_list) then
334 FND_MSG_PUB.initialize;
335 end if;
336
337 /* Bug 1308558 Start */
338 -- We need to conditionally delete the master record for
339 -- the existing records before to enhancement..
340 -- The p_overwrite flag value will be null for these records.
341
342 IF NVL(P_source_form, 'X') = 'D' THEN
343 --Deleting detail recordS to maintain the isolated delete
344 --relation between the master and detail
345
346 DELETE FROM psb_default_assignments
347 WHERE default_rule_id = p_default_rule_id;
348
349 DELETE FROM psb_default_account_distrs
350 WHERE default_rule_id = p_default_rule_id;
351
352 -- Check the existence of Non FTE record.
353 DELETE FROM psb_defaults
354 WHERE default_rule_id = p_default_rule_id
355 AND NOT EXISTS(
356 SELECT 1
357 FROM PSB_FTE_RULES_V
358 WHERE default_rule_id = p_default_rule_id
359 );
360 ELSE
361 DELETE FROM psb_entity
362 WHERE entity_id = p_entity_id;
363
364 DELETE FROM psb_allocrule_percents
365 WHERE allocation_rule_id = p_entity_id;
366
367 IF NVL(P_source_form, 'X') = 'F' THEN
368 -- Check the existence of FTE record.
369 DELETE FROM psb_defaults
370 WHERE default_rule_id = p_default_rule_id
371 AND NOT EXISTS(
372 SELECT 1
373 FROM PSB_NON_FTE_RULES_V
374 WHERE default_rule_id = p_default_rule_id
375 );
376 ELSE
377 --Deleting detail recordS to maintain the isolated delete
378 --relation between the master and detail
379
380 DELETE FROM psb_default_assignments
381 WHERE default_rule_id = p_default_rule_id;
382
383 DELETE FROM psb_default_account_distrs
384 WHERE default_rule_id = p_default_rule_id;
385
386 DELETE FROM psb_defaults
387 WHERE default_rule_id = p_default_rule_id;
388 END IF;
389 /*IF (SQL%NOTFOUND) THEN
390 RAISE NO_DATA_FOUND;
391 END IF;*/
392 END IF;
393 /* Bug 1308558 End */
394
395 -- Standard call to get message count and if count is 1, get message info.
396
397 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
398 p_data => p_msg_data);
399
400 -- Standard check of p_commit.
401
402 if FND_API.to_Boolean (p_commit) then
403 commit work;
404 end if;
405
406
407 EXCEPTION
408
409 when FND_API.G_EXC_ERROR then
410
411 rollback to DELETE_ROW_PVT;
412
413 p_return_status := FND_API.G_RET_STS_ERROR;
414
415 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
416 p_data => p_msg_data);
417
418
419 when FND_API.G_EXC_UNEXPECTED_ERROR then
420
421 rollback to DELETE_ROW_PVT;
422
423 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
424
425 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
426 p_data => p_msg_data);
427
428
429 when OTHERS then
430
431 rollback to DELETE_ROW_PVT;
432
433 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
434
435 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
436
437 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
438 l_api_name);
439 end if;
440
441 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
442 p_data => p_msg_data);
443 END DELETE_ROW;
444
445 PROCEDURE LOCK_ROW(
446 p_api_version IN NUMBER,
447 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
448 p_commit IN VARCHAR2 := FND_API.G_FALSE,
449 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
450 p_return_status OUT NOCOPY VARCHAR2,
451 p_msg_count OUT NOCOPY NUMBER,
452 p_msg_data OUT NOCOPY VARCHAR2,
453 --
454 p_row_locked OUT NOCOPY VARCHAR2,
455 --
456 P_ROW_ID IN VARCHAR2,
457 P_DEFAULT_RULE_ID in NUMBER,
458 P_NAME in VARCHAR2,
459 P_GLOBAL_DEFAULT_FLAG IN VARCHAR2,
460 P_DATA_EXTRACT_ID IN NUMBER,
461 P_BUSINESS_GROUP_ID IN NUMBER,
462 P_ENTITY_ID IN NUMBER,
463 P_PRIORITY IN NUMBER,
464 /* Bug 1308558 Start */
465 P_OVERWRITE IN VARCHAR2 DEFAULT NULL,
466 P_SOURCE_FORM IN VARCHAR2 DEFAULT 'F'
467 /* Bug 1308558 End */
468 ) IS
469
470 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_ROW';
471 l_api_version CONSTANT NUMBER := 1.0;
472 --
473 counter number;
474
475 CURSOR C IS SELECT * FROM psb_defaults
476 WHERE rowid = p_row_id
477 FOR UPDATE of default_rule_id NOWAIT;
478 Recinfo C%ROWTYPE;
479
480 BEGIN
481 --
482 SAVEPOINT Lock_Row_Pvt ;
483 --
484 IF NOT FND_API.Compatible_API_Call ( l_api_version,
485 p_api_version,
486 l_api_name,
487 G_PKG_NAME )
488 THEN
489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
490 END IF;
491 --
492
493 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
494 FND_MSG_PUB.initialize ;
495 END IF;
496 --
497 p_return_status := FND_API.G_RET_STS_SUCCESS ;
498 p_row_locked := FND_API.G_TRUE ;
499 --
500 OPEN C;
501 --
502 FETCH C INTO Recinfo;
503 IF (C%NOTFOUND) then
504 CLOSE C;
505 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
506 FND_MSG_PUB.Add;
507 RAISE FND_API.G_EXC_ERROR ;
508 END IF;
509
510 /* Bug 1308558 End */
511
512 IF NVL(p_source_form, 'F') = 'D' THEN
513 -- For Non FTE records
514 IF
515 (
516 (Recinfo.default_rule_id = p_default_rule_id)
517 AND (Recinfo.name = p_name)
518 AND ( (Recinfo.global_default_flag = p_global_default_flag)
519 OR ( (Recinfo.global_default_flag IS NULL)
520 AND (p_global_default_flag IS NULL)))
521 AND ( (Recinfo.data_extract_id = p_data_extract_id)
522 OR ( (Recinfo.data_extract_id IS NULL)
523 AND (p_data_extract_id IS NULL)))
524 AND ( (Recinfo.business_group_id = p_business_group_id)
525 OR ( (Recinfo.business_group_id IS NULL)
526 AND (p_business_group_id IS NULL)))
527 AND ( (Recinfo.entity_id = p_entity_id)
528 OR ( (Recinfo.entity_id IS NULL)
529 AND (p_entity_id IS NULL)))
530 AND ( (Recinfo.priority = p_priority)
531 OR ( (Recinfo.priority IS NULL)
532 AND (p_priority IS NULL)))
533 AND ( (Recinfo.overwrite = p_overwrite)
534 OR ( (Recinfo.overwrite IS NULL)
535 AND (p_overwrite IS NULL)))
536 )
537
538 THEN
539 Null;
540 ELSE
541 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
542 FND_MSG_PUB.Add;
543 RAISE FND_API.G_EXC_ERROR ;
544 END IF;
545 ELSE
546 -- For FTE records
547 IF
548 (
549 (Recinfo.default_rule_id = p_default_rule_id)
550 AND (Recinfo.name = p_name)
551 AND ( (Recinfo.global_default_flag = p_global_default_flag)
552 OR ( (Recinfo.global_default_flag IS NULL)
553 AND (p_global_default_flag IS NULL)))
554 AND ( (Recinfo.data_extract_id = p_data_extract_id)
555 OR ( (Recinfo.data_extract_id IS NULL)
556 AND (p_data_extract_id IS NULL)))
557 AND ( (Recinfo.business_group_id = p_business_group_id)
558 OR ( (Recinfo.business_group_id IS NULL)
559 AND (p_business_group_id IS NULL)))
560 AND ( (Recinfo.entity_id = p_entity_id)
561 OR ( (Recinfo.entity_id IS NULL)
562 AND (p_entity_id IS NULL)))
563 AND ( (Recinfo.priority = p_priority)
564 OR ( (Recinfo.priority IS NULL)
565 AND (p_priority IS NULL)))
566 )
567
568 THEN
569 Null;
570 ELSE
571 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
572 FND_MSG_PUB.Add;
573 RAISE FND_API.G_EXC_ERROR ;
574 END IF;
575 /* Bug 1308558 End */
576 END IF;
577
578 --
579 IF FND_API.To_Boolean ( p_commit ) THEN
580 COMMIT WORK;
581 END iF;
582 --
583 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
584 p_data => p_msg_data );
585 --
586 EXCEPTION
587 --
588 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
589 --
590 ROLLBACK TO Lock_Row_Pvt ;
591 p_row_locked := FND_API.G_FALSE;
592 p_return_status := FND_API.G_RET_STS_ERROR;
593 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
594 p_data => p_msg_data );
595 --
596 WHEN FND_API.G_EXC_ERROR THEN
597 --
598 ROLLBACK TO Lock_Row_Pvt ;
599 p_return_status := FND_API.G_RET_STS_ERROR;
600 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
601 p_data => p_msg_data );
602 --
603 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
604 --
605 ROLLBACK TO Lock_Row_Pvt ;
606 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
607 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
608 p_data => p_msg_data );
609 --
610 WHEN OTHERS THEN
611 --
612 ROLLBACK TO Lock_Row_Pvt ;
613 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
614 --
615 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
616 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
617 l_api_name);
618 END if;
619 --
620 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
621 p_data => p_msg_data );
622 END LOCK_ROW;
623
624
625 PROCEDURE Check_Unique
626 (
627 p_api_version IN NUMBER,
628 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
629 p_commit IN VARCHAR2 := FND_API.G_FALSE,
630 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
631 p_return_status OUT NOCOPY VARCHAR2,
632 p_msg_count OUT NOCOPY NUMBER,
633 p_msg_data OUT NOCOPY VARCHAR2,
634 --
635 P_DEFAULT_RULE_ID in NUMBER,
636 P_NAME IN VARCHAR2,
637 P_DATA_EXTRACT_ID IN NUMBER,
638 P_RETURN_VALUE IN OUT NOCOPY VARCHAR2
639 )
640 IS
641 --
642 l_api_name CONSTANT VARCHAR2(30) := 'Check_Unique';
643 l_api_version CONSTANT NUMBER := 1.0;
644 --
645 l_tmp VARCHAR2(1);
646
647 CURSOR c IS
648 SELECT '1'
649 FROM psb_defaults
650 WHERE name = p_name
651 AND ( (p_default_rule_id IS NULL)
652 OR ( default_rule_id <> p_default_rule_id) )
653 AND (data_extract_id = p_data_extract_id);
654
655
656 BEGIN
657 --
658 SAVEPOINT Check_Unique_Pvt ;
659 --
660 IF NOT FND_API.Compatible_API_Call ( l_api_version,
661 p_api_version,
662 l_api_name,
663 G_PKG_NAME )
664 THEN
665 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
666 END IF;
667 --
668
669 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
670 FND_MSG_PUB.initialize ;
671 END IF;
672 --
673 p_return_status := FND_API.G_RET_STS_SUCCESS ;
674 --
675
676 -- Checking the psb_defaults table for uniqueness.
677 OPEN c;
678 FETCH c INTO l_tmp;
679
680 --
681 -- p_Return_Value tells whether references exist or not.
682 IF (l_tmp IS NULL) THEN
683 p_Return_Value := 'FALSE';
684 ELSE
685 p_Return_Value := 'TRUE';
686 END IF;
687
688 CLOSE c;
689
690 --
691 IF FND_API.To_Boolean ( p_commit ) THEN
692 COMMIT WORK;
693 END iF;
694 --
695 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
696 p_data => p_msg_data );
697 --
698 EXCEPTION
699 --
700 WHEN FND_API.G_EXC_ERROR THEN
701 --
702 ROLLBACK TO Check_Unique_Pvt ;
703 p_return_status := FND_API.G_RET_STS_ERROR;
704 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
705 p_data => p_msg_data );
706 --
707 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
708 --
709 ROLLBACK TO Check_Unique_Pvt ;
710 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
711 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
712 p_data => p_msg_data );
713 --
714 WHEN OTHERS THEN
715 --
716 ROLLBACK TO Check_Unique_Pvt ;
717 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
718 --
719 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
720 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
721 l_api_name);
722 END if;
723 --
724 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
725 p_data => p_msg_data );
726 --
727 END Check_Unique;
728
729 PROCEDURE Check_Global_Default
730 (
731 p_api_version IN NUMBER,
732 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
733 p_commit IN VARCHAR2 := FND_API.G_FALSE,
734 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
735 p_return_status OUT NOCOPY VARCHAR2,
736 p_msg_count OUT NOCOPY NUMBER,
737 p_msg_data OUT NOCOPY VARCHAR2,
738 --
739 P_ROW_ID IN VARCHAR2,
740 P_DATA_EXTRACT_ID IN NUMBER,
741 P_GLOBAL_DEFAULT_FLAG IN VARCHAR2,
742 P_RETURN_VALUE IN OUT NOCOPY VARCHAR2
743 )
744 IS
745 --
746 l_api_name CONSTANT VARCHAR2(30) := 'Check_Global_Default';
747 l_api_version CONSTANT NUMBER := 1.0;
748 --
749 l_tmp varchar2(10);
750
751 CURSOR c IS
752 SELECT (1)
753 FROM psb_defaults
754 WHERE data_extract_id = p_data_extract_id
755 AND global_default_flag = p_global_default_flag
756 AND ( (p_row_id IS NULL)
757 OR ( rowid <> p_row_id) );
758
759
760 BEGIN
761 --
762 SAVEPOINT Check_Unique_Pvt ;
763 --
764 IF NOT FND_API.Compatible_API_Call ( l_api_version,
765 p_api_version,
766 l_api_name,
767 G_PKG_NAME )
768 THEN
769 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
770 END IF;
771 --
772
773 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
774 FND_MSG_PUB.initialize ;
775 END IF;
776 --
777 p_return_status := FND_API.G_RET_STS_SUCCESS ;
778 --
779
780 -- Checking the psb_defaults table for uniqueness.
781 OPEN c;
782 FETCH c INTO l_tmp;
783
784 --
785 -- p_Return_Value tells whether references exist or not.
786 IF ( l_tmp IS NOT NULL) THEN
787 P_Return_Value := 'TRUE';
788 ELSE
789 P_Return_Value := 'FALSE';
790 END IF;
791
792 CLOSE c;
793
794 --
795 IF FND_API.To_Boolean ( p_commit ) THEN
796 COMMIT WORK;
797 END iF;
798 --
799 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
800 p_data => p_msg_data );
801 --
802 EXCEPTION
803 --
804 WHEN FND_API.G_EXC_ERROR THEN
805 --
806 ROLLBACK TO Check_Unique_Pvt ;
807 p_return_status := FND_API.G_RET_STS_ERROR;
808 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
809 p_data => p_msg_data );
810 --
811 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
812 --
813 ROLLBACK TO Check_Unique_Pvt ;
814 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
815 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
816 p_data => p_msg_data );
817 --
818 WHEN OTHERS THEN
819 --
820 ROLLBACK TO Check_Unique_Pvt ;
821 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
822 --
823 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
824 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
825 l_api_name);
826 END if;
827 --
828 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
829 p_data => p_msg_data );
830 --
831 END Check_Global_Default;
832
833
834 PROCEDURE Check_References
835 (
836 p_api_version IN NUMBER,
837 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
838 p_commit IN VARCHAR2 := FND_API.G_FALSE,
839 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
840 p_return_status OUT NOCOPY VARCHAR2,
841 p_msg_count OUT NOCOPY NUMBER,
842 p_msg_data OUT NOCOPY VARCHAR2,
843 --
844 P_DEFAULT_RULE_ID in NUMBER,
845 P_NAME in VARCHAR2,
846 p_Return_Value IN OUT NOCOPY VARCHAR2
847 )
848 IS
849 --
850 l_api_name CONSTANT VARCHAR2(30) := 'Check_References';
851 l_api_version CONSTANT NUMBER := 1.0;
852 --
853 l_tmp VARCHAR2(1);
854 l_tmp1 varchar2(1);
855
856 CURSOR c IS
857 SELECT '1'
858 FROM psb_position_assignments pa, psb_position_pay_distributions ppd
859 WHERE pa.assignment_default_rule_id = p_default_rule_id
860 OR ppd.distribution_default_rule_id = p_default_rule_id;
861
862
863 BEGIN
864 --
865 SAVEPOINT Check_References_Pvt ;
866 --
867 IF NOT FND_API.Compatible_API_Call ( l_api_version,
868 p_api_version,
869 l_api_name,
870 G_PKG_NAME )
871 THEN
872 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
873 END IF;
874 --
875
876 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
877 FND_MSG_PUB.initialize ;
878 END IF;
879 --
880 p_return_status := FND_API.G_RET_STS_SUCCESS ;
881 --
882
883 -- Checking the Psb_set_relations table for references.
884 OPEN c;
885 FETCH c INTO l_tmp;
886
887 --
888 -- p_Return_Value tells whether references exist or not.
889 IF (l_tmp IS NULL) THEN
890 p_Return_Value := 'FALSE';
891 ELSE
892 p_Return_Value := 'TRUE';
893 END IF;
894
895 CLOSE c;
896 --
897 IF FND_API.To_Boolean ( p_commit ) THEN
898 COMMIT WORK;
899 END iF;
900 --
901 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
902 p_data => p_msg_data );
903
904 EXCEPTION
905 --
906 WHEN FND_API.G_EXC_ERROR THEN
907 --
908 ROLLBACK TO Check_References_Pvt ;
909 p_return_status := FND_API.G_RET_STS_ERROR;
910 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
911 p_data => p_msg_data );
912 --
913 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
914 --
915 ROLLBACK TO Check_References_Pvt ;
916 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
917 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
918 p_data => p_msg_data );
919 --
920 WHEN OTHERS THEN
921 --
922 ROLLBACK TO Check_References_Pvt ;
923 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
924 --
925 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
926 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
927 l_api_name);
928 END if;
929 --
930 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
931 p_data => p_msg_data );
932 --
933 END Check_References;
934
935
936 END PSB_DEFAULTS_PVT;