[Home] [Help]
PACKAGE BODY: APPS.HZ_DSS_GROUPS_PUB
Source
1 PACKAGE BODY HZ_DSS_GROUPS_PUB AS
2 /* $Header: ARHPDSSB.pls 120.3 2005/10/18 19:34:06 jhuang noship $ */
3
4 ----------------------------------
5 -- declaration of global variables
6 ----------------------------------
7
8 -------------------------------------------------
9 -- private procedures and functions
10 -------------------------------------------------
11
12
13 --------------------------------------------
14 -- get_rank_of_dsg
15 --------------------------------------------
16
17 FUNCTION get_rank_of_dsg
18 -- Return rank if the passed in Data Sharing Group exists in hz_dss_group_b
19 -- -1 otherwise
20 (p_dss_group_code VARCHAR2 )
21 RETURN NUMBER
22 IS
23 CURSOR c0
24 IS
25 SELECT rank
26 FROM hz_dss_groups_b
27 WHERE dss_group_code= p_dss_group_code ;
28 result NUMBER ;
29 BEGIN
30 OPEN c0;
31 FETCH c0 INTO result ;
32 IF c0%NOTFOUND THEN
33 result := -1 ;
34 END IF;
35 CLOSE c0;
36 RETURN result;
37 END get_rank_of_dsg ;
38
39 --------------------------------------------
40 -- return_max_rank
41 --------------------------------------------
42
43 FUNCTION return_max_rank
44 -- Return the maximum rank in hz_dss_group_b , if number of rows is greater than 0.
45 -- 1 if no rows exist in hz_dss_groups_b
46 RETURN NUMBER
47 IS
48 CURSOR c0
49 IS
50 SELECT NVL(MAX(RANK),0)+ 1 FROM HZ_DSS_GROUPS_B;
51 result NUMBER ;
52 BEGIN
53 OPEN c0;
54 FETCH c0 INTO result ;
55 IF HZ_DSS_VALIDATE_PKG.return_no_of_dss_groups > 0
56 THEN
57 result := result -1 ;
58 END IF;
59 CLOSE c0;
60 RETURN result;
61 END return_max_rank ;
62
63
64
65
66 --------------------------------------------
67 -- resequence_ranks_to_create
68 --------------------------------------------
69
70 PROCEDURE resequence_ranks_to_create ( p_insert_before_group_rank IN NUMBER )
71 -- Resequence ranks in hz_dss_groups_b in lieu of creating a new DSG
72 IS
73 BEGIN
74 update hz_dss_groups_b set rank = rank + 1
75 where rank >= p_insert_before_group_rank ;
76
77 END resequence_ranks_to_create;
78
79
80 --------------------------------------------
81 -- resequence_ranks_to_update
82 --------------------------------------------
83
84 PROCEDURE resequence_ranks_to_update ( p_group_to_be_updated_rank IN NUMBER,
85 p_insert_before_group_rank IN NUMBER,
86 p_to_be_upd_group_code IN VARCHAR2,
87 p_order_before_group_code IN VARCHAR2)
88 -- Resequence ranks in hz_dss_groups_b in lieu of updating the rank of a DSG
89 IS
90 BEGIN
91 -- NOTE: IN THIS PROCEDURE WE DO NOT UPDATE THE RANK OF THE GROUP WHOSE RANK
92 -- NEEDS TO BE UPDATED. WE RESEQUENCE EVERYTHING ELSE OTHER THAN THAT.
93
94 -- INSERT BEFORE AN EXISTING GROUP CODE
95 IF p_order_before_group_code IS NOT NULL AND
96 p_order_before_group_code <> FND_API.G_MISS_CHAR
97 THEN
98 -----------------------------------------------------------------------------------
99 -- RESEQUENCE 1: STARTS FROM (INCLUDING) LEVEL OF INSERT BEFORE GROUP AND GOES DOWN.
100 -- THE GROUP TO BE UPDATED IS LEFT ALONE
101 -----------------------------------------------------------------------------------
102 -- FREEZE THE RANK OF GROUP TO BE UPDATED AND INCREMENT EVERY RANK THAT IS BIGGER
103 -- THAN THE RANK THAT WE WOULD WANT OUR GROUP TO MOVE INTO, AFTER THE UPDATE.
104 update hz_dss_groups_b set rank = rank + 1
105 where rank >= p_insert_before_group_rank and dss_group_code <> p_to_be_upd_group_code ;
106
107 ------------------------------------------------------------------------------------------
108 -- RESEQUENCE 2: STARTS FROM (EXCLUDING) ONE LEVEL BELOW GROUP TO BE UPDATED AND GOES DOWN.
109 -------------------------------------------------------------------------------------------
110
111 -- TO FILL THE HOLE CREATED BY RESEQUENCING THE RANKS, WE NEED TO DECREMENT
112 -- THE RANK OF ALL GROUP CODES THAT ARE HIGHER THAN THE RANK OF THE GROUP,
113 -- WHOSE RANK HAS TO BE UPDATED.
114 update hz_dss_groups_b set rank = rank - 1
115 where rank > p_group_to_be_updated_rank ;
116
117 -- INSERT LAST
118 ELSIF p_order_before_group_code = FND_API.G_MISS_CHAR
119 THEN
120 update hz_dss_groups_b set rank = rank - 1
121 where rank > p_group_to_be_updated_rank ;
122 END IF;
123
124
125 END resequence_ranks_to_update;
126
127
128
129 --------------------------------------
130 -- public procedures and functions
131 --------------------------------------
132
133 ---------------------------------------------------------------------
134 -- NOTE: For create_group we follow this convention:
135 -- IF order_before_group_code is null or g_miss_char --- we insert last and resequence ranks
136 -- IF order_before_group_code is a valid group code --- we insert before the order_before_group_code
137 --- and resequence ranks
138
139 -- NOTE: For update_group we follow this convention:
140 -- IF order_before_group_code is g_miss_char --- we insert last and resequence ranks
141 -- IF order_before_group_code is null --- we do not to anything to the ranks
142 -- IF order_before_group_code is a valid group code --- we insert before the order_before_group_code
143 --- and resequence ranks
144
145 ----------------------------------------------------------------------
146 /**
147 * PROCEDURE create_group
148 *
149 * DESCRIPTION
150 * Creates a data sharing group.
151 *
152 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
153 *
154 *
155 * ARGUMENTS
156 *
157 *
158 * NOTES
159 *
160 * MODIFICATION HISTORY
161 *
162 * 08-13-2002 Colathur Vijayan ("VJN") o Created.
163 *
164 */
165
166 PROCEDURE create_group(
167 p_init_msg_list IN VARCHAR2,
168 p_dss_group IN DSS_GROUP_REC_TYPE,
169 x_return_status OUT NOCOPY VARCHAR2,
170 x_msg_count OUT NOCOPY NUMBER,
171 x_msg_data OUT NOCOPY VARCHAR2
172 ) IS
173 row_id varchar2(64);
174 rank number;
175 temp number;
176 BEGIN
177
178 -- standard start of API savepoint
179 SAVEPOINT create_group;
180
181 -- initialize message list if p_init_msg_list is set to TRUE.
182 IF FND_API.to_Boolean(p_init_msg_list) THEN
183 FND_MSG_PUB.initialize;
184 END IF;
185
186 -- initialize API return status to success.
187 x_return_status := FND_API.G_RET_STS_SUCCESS;
188
189 --validation for mandatory column dss_group_code
190 if (p_dss_group.dss_group_code is null or
191 p_dss_group.dss_group_code = fnd_api.g_miss_char) then
192 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
193 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'dss_group_code' );
194 FND_MSG_PUB.ADD;
195 RAISE FND_API.G_EXC_ERROR;
196 end if;
197
198 --validation for mandatory column dss_group_name
199 if (p_dss_group.dss_group_name is null or
200 p_dss_group.dss_group_name = fnd_api.g_miss_char) then
201 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
202 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'dss_group_name' );
203 FND_MSG_PUB.ADD;
204 RAISE FND_API.G_EXC_ERROR;
205 end if;
206
207
208
209 -- VALIDATION
210 -- PASSED IN GROUP CODE SHOULD BE UNIQUE
211 IF HZ_DSS_VALIDATE_PKG.exist_in_dss_groups_b (p_dss_group.dss_group_code) = 'Y'
212 THEN
213 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_CODE_EXISTS_ALREADY');
214 FND_MSG_PUB.ADD;
215 RAISE FND_API.G_EXC_ERROR;
216 END IF;
217
218 -- IF PASSED IN INSERT BEFORE GROUP IS NEITHER NULL NOR G_MISS_CHAR,
219 -- IT SHOULD BE A VALID GROUP CODE
220
221 IF p_dss_group.order_before_group_code IS NOT NULL AND
222 p_dss_group.order_before_group_code <> FND_API.G_MISS_CHAR
223 THEN
224 IF HZ_DSS_VALIDATE_PKG.exist_in_dss_groups_b (p_dss_group.order_before_group_code) = 'N'
225 THEN
226 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_ORD_BEF_GR_CODE_INVALID');
227 FND_MSG_PUB.ADD;
228 RAISE FND_API.G_EXC_ERROR;
229 END IF;
230 END IF;
231
232
233 -- PASSED IN GROUP NAME SHOULD BE UNIQUE IN AN MLS LANGUAGE
234 IF HZ_DSS_VALIDATE_PKG.exist_in_dss_groups_vl (p_dss_group.dss_group_name) = 'Y'
235 THEN
236 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_NAME_EXISTS_ALREADY');
237 FND_MSG_PUB.ADD;
238 RAISE FND_API.G_EXC_ERROR;
239 END IF;
240
241
242 -- STATUS VALIDATION
243
244 IF p_dss_group.status is not null then
245 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(
246 p_dss_group.status, 'REGISTRY_STATUS')= 'N' THEN
247 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_STATUS_VAL_INVALID');
248 FND_MSG_PUB.ADD;
249 RAISE FND_API.G_EXC_ERROR;
250 END IF;
251 END IF;
252
253 -- BES ENABLE FLAG SHOULD BE Y OR N
254 IF UPPER( NVL(p_dss_group.bes_enable_flag,'N') ) IN ('Y','N') THEN
255 NULL;
256 ELSE
257 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_BES_FLAG_INVALID');
258 FND_MSG_PUB.ADD;
259 RAISE FND_API.G_EXC_ERROR;
260 END IF;
261
262 -- RANK RESEQUENCING
263 -- CASE 1: WE WANT TO INSERT BEFORE AN EXISTING GROUP CODE
264 IF p_dss_group.order_before_group_code IS NOT NULL AND
265 p_dss_group.order_before_group_code <> FND_API.G_MISS_CHAR
266 THEN
267 rank := get_rank_of_dsg(p_dss_group.order_before_group_code);
268 resequence_ranks_to_create(rank );
269 -- CASE 2: WE WANT TO INSERT LAST -- NO NEED TO RESEQUENCE HERE !!!!!
270 -- SINCE WE WANT TO INSERT LAST, WE NEED TO INCREMENT MAX RANK BY 1.
271 -- THE ONLY EXCEPTION TO THIS RULE, IS WHEN WE HAVE NO ROWS AND
272 -- WE WANT TO ADD A NEW ROW.
273 ELSE
274 IF HZ_DSS_VALIDATE_PKG.return_no_of_dss_groups > 0
275 THEN
276 rank := return_max_rank + 1 ;
277 ELSE
278 rank := return_max_rank ;
279 END IF;
280 END IF;
281
282
283 -- Call the low level table handler
284 HZ_DSS_GROUPS_PKG.Insert_Row (
285 x_rowid => row_id ,
286 x_dss_group_code => p_dss_group.dss_group_code,
287 x_rank => rank ,
288 x_status => nvl(p_dss_group.status,'A'),
289 x_dss_group_name => p_dss_group.dss_group_name,
290 x_description => p_dss_group.description,
291 x_bes_enable_flag => nvl(p_dss_group.bes_enable_flag,'Y'),
292 x_object_version_number => 1);
293
294
295 -- standard call to get message count and if count is 1, get message info.
296 FND_MSG_PUB.Count_And_Get(
297 p_encoded => FND_API.G_FALSE,
298 p_count => x_msg_count,
299 p_data => x_msg_data);
300
301 EXCEPTION
302 WHEN FND_API.G_EXC_ERROR THEN
303 ROLLBACK TO create_group ;
304 x_return_status := FND_API.G_RET_STS_ERROR;
305 FND_MSG_PUB.Count_And_Get(
306 p_encoded => FND_API.G_FALSE,
307 p_count => x_msg_count,
308 p_data => x_msg_data);
309
310 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
311 ROLLBACK TO create_group ;
312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
313 FND_MSG_PUB.Count_And_Get(
314 p_encoded => FND_API.G_FALSE,
315 p_count => x_msg_count,
316 p_data => x_msg_data);
317
318 WHEN OTHERS THEN
319 ROLLBACK TO create_group ;
320 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
321 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
322 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
323 FND_MSG_PUB.ADD;
324 FND_MSG_PUB.Count_And_Get(
325 p_encoded => FND_API.G_FALSE,
326 p_count => x_msg_count,
327 p_data => x_msg_data);
328 END create_group ;
329
330
331 /**
332 * PROCEDURE update_group
333 *
334 * DESCRIPTION
335 * Updates a data sharing group.
336 *
337 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
338 *
339 *
340 * ARGUMENTS
341 *
342 *
343 * NOTES
344 *
345 * MODIFICATION HISTORY
346 *
347 * 08-13-2002 Colathur Vijayan ("VJN") o Created.
348 *
349 */
350
351 PROCEDURE update_group (
352 p_init_msg_list IN VARCHAR2,
353 p_dss_group IN dss_group_rec_type,
354 x_object_version_number IN OUT NOCOPY NUMBER,
355 x_return_status OUT NOCOPY VARCHAR2,
356 x_msg_count OUT NOCOPY NUMBER,
357 x_msg_data OUT NOCOPY VARCHAR2
358 )
359 IS
360
361 rank NUMBER;
362 l_rank NUMBER;
363 temp1 NUMBER;
364 temp2 NUMBER;
365 l_object_version_number NUMBER;
366 l_rowid ROWID;
367 l_status VARCHAR2(1);
368
369 BEGIN
370
371 -- initialize message list if p_init_msg_list is set to TRUE.
372 IF FND_API.to_Boolean(p_init_msg_list) THEN
373 FND_MSG_PUB.initialize;
374 END IF;
375
376 -- initialize API return status to success.
377 x_return_status := FND_API.G_RET_STS_SUCCESS;
378
379 -- standard start of API savepoint
380 SAVEPOINT update_group;
381
382 --Non updateable to null
383 IF ( p_dss_group.dss_group_code IS NULL OR
384 p_dss_group.dss_group_code= FND_API.G_MISS_CHAR )
385 THEN
386 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_TO_NULL');
387 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'dss_group_code' );
388 FND_MSG_PUB.ADD;
389 RAISE FND_API.G_EXC_ERROR;
390 END IF;
391
392 --Non updateable to null
393 IF p_dss_group.dss_group_name = FND_API.G_MISS_CHAR THEN
394 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_TO_NULL');
395 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'dss_group_name' );
396 FND_MSG_PUB.ADD;
397 RAISE FND_API.G_EXC_ERROR;
398 END IF;
399
400 IF p_dss_group.bes_enable_flag = FND_API.G_MISS_CHAR THEN
401 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_TO_NULL');
402 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'bes_enable_flag' );
403 FND_MSG_PUB.ADD;
404 RAISE FND_API.G_EXC_ERROR;
405 END IF;
406
407 -- check whether record has been updated by another user. If not, lock it.
408 BEGIN
409 SELECT object_version_number, rowid, rank, status
410 INTO l_object_version_number, l_rowid, l_rank, l_status
411 FROM hz_dss_groups_b
412 WHERE dss_group_code = p_dss_group.dss_group_code
413 FOR UPDATE NOWAIT;
414 EXCEPTION
415 WHEN NO_DATA_FOUND THEN
416 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_CODE_INVALID');
417 FND_MSG_PUB.ADD;
418 RAISE FND_API.G_EXC_ERROR;
419 END;
420
421 IF NOT ((x_object_version_number IS NULL AND
422 l_object_version_number IS NULL) OR
423 (x_object_version_number = l_object_version_number))
424 THEN
425 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
426 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_DSS_GROUPS');
427 FND_MSG_PUB.ADD;
428 RAISE FND_API.G_EXC_ERROR;
429 END IF;
430
431 x_object_version_number := NVL(l_object_version_number, 1) + 1;
432
433 -- VALIDATION
434 -- PASSED IN GROUP CODE SHOULD BE VALID
435 -- already validated when getting object_version_number.
436 /*
437 IF HZ_DSS_VALIDATE_PKG.exist_in_dss_groups_b (p_dss_group.dss_group_code) = 'N' THEN
438 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_CODE_INVALID');
439 FND_MSG_PUB.ADD;
440 RAISE FND_API.G_EXC_ERROR;
441 END IF;
442 */
443
444 -- IF PASSED IN INSERT BEFORE GROUP IS NEITHER NULL NOR G_MISS_CHAR,
445 -- IT SHOULD BE A VALID GROUP CODE
446
447 IF p_dss_group.order_before_group_code IS NOT NULL AND
448 p_dss_group.order_before_group_code <> FND_API.G_MISS_CHAR
449 THEN
450 IF HZ_DSS_VALIDATE_PKG.exist_in_dss_groups_b (
451 p_dss_group.order_before_group_code) = 'N'
452 THEN
453 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_ORD_BEF_GR_CODE_INVALID');
454 FND_MSG_PUB.ADD;
455 RAISE FND_API.G_EXC_ERROR;
456 END IF;
457 END IF;
458
459 -- PASSED IN GROUP NAME SHOULD BE UNIQUE IN AN MLS LANGUAGE
460 IF p_dss_group.dss_group_name IS NOT NULL THEN
461 IF HZ_DSS_VALIDATE_PKG.exist_in_dss_groups_vl (
462 p_dss_group.dss_group_name, p_dss_group.dss_group_code) = 'Y'
463 THEN
464 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_NAME_EXISTS_ALREADY');
465 FND_MSG_PUB.ADD;
466 RAISE FND_API.G_EXC_ERROR;
467 END IF;
468 END IF;
469
470 -- STATUS VALIDATION
471
472 IF p_dss_group.status IS NOT NULL THEN
473 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(
474 p_dss_group.status,
475 'REGISTRY_STATUS')= 'N'
476 THEN
477 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_STATUS_VAL_INVALID');
478 FND_MSG_PUB.ADD;
479 RAISE FND_API.G_EXC_ERROR;
480 END IF;
481 END IF;
482
483 -- BES ENABLE FLAG SHOULD BE Y OR N
484 IF p_dss_group.bes_enable_flag IS NOT NULL THEN
485 IF p_dss_group.bes_enable_flag <> 'Y' AND
486 p_dss_group.bes_enable_flag <> 'N'
487 THEN
488 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_BES_FLAG_INVALID');
489 FND_MSG_PUB.ADD;
490 RAISE FND_API.G_EXC_ERROR;
491 END IF;
492 END IF;
493
494 -- RANK RESEQUENCING
495 -- BEFORE WE DO ANYTHING, WE LET THE RANK DEFAULT TO WHAT IS IN THE EXISTING ROW
496
497 rank := l_rank;
498
499 -- GROUP TO BE UPDATED
500 temp1 := get_rank_of_dsg(p_dss_group.dss_group_code) ;
501
502 -- GROUP BEFORE WHICH WE WANT TO INSERT
503 temp2 := get_rank_of_dsg(p_dss_group.order_before_group_code) ;
504
505 -- CASE 1 : WE WANT TO INSERT BEFORE AN EXISTING GROUP CODE
506 IF p_dss_group.order_before_group_code IS NOT NULL AND
507 p_dss_group.order_before_group_code <> FND_API.G_MISS_CHAR
508 THEN
509 resequence_ranks_to_update(
510 temp1, temp2, p_dss_group.dss_group_code,
511 p_dss_group.order_before_group_code);
512
513 -- NOTE: THIS OFFSET IS IMPORTANT
514 -- WHEN RANK NEEDS TO GO UP AFTER UPDATION
515 IF temp1 > temp2 THEN
516 rank := temp2 ;
517 -- WHEN RANK NEEDS TO GO DOWN AFTER UPDATION
518 ELSIF temp1 < temp2 THEN
519 rank := temp2 - 1;
520 END IF;
521
522 -- CASE 2 : WE WANT TO INSERT LAST
523 ELSIF p_dss_group.order_before_group_code = FND_API.G_MISS_CHAR THEN
524 rank := return_max_rank ;
525 resequence_ranks_to_update(
526 temp1, temp2, p_dss_group.dss_group_code,
527 p_dss_group.order_before_group_code);
528
529 END IF;
530
531 -- Bug#3711820 - update grants when the status of a dss group has been
532 -- changed.
533 --
534 -- reset grant end date if status has been switched from I to A.
535 -- end-dated fnd grants if status has been switched from A to I.
536 --
537 -- status is null means no change
538 -- status = G_MISS case has been caught by the lookup validation
539 --
540 IF p_dss_group.status IS NOT NULL AND
541 ((p_dss_group.status = 'A' AND l_status = 'I') OR
542 (p_dss_group.status = 'I' AND l_status = 'A'))
543 THEN
544
545 hz_dss_grants_pub.update_grant (
546 p_dss_group_code => p_dss_group.dss_group_code,
547 p_dss_group_status => p_dss_group.status,
548 x_return_status => x_return_status,
549 x_msg_count => x_msg_count,
550 x_msg_data => x_msg_data
551 );
552
553 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
554 RAISE FND_API.G_EXC_ERROR;
555 END IF;
556
557 END IF;
558
559 -- Call the low level table handler
560 HZ_DSS_GROUPS_PKG.Update_Row (
561 x_rowid => l_rowid ,
562 x_rank => rank ,
563 x_status => p_dss_group.status,
564 x_dss_group_name => p_dss_group.dss_group_name,
565 x_description => p_dss_group.description,
566 x_bes_enable_flag => p_dss_group.bes_enable_flag,
567 x_object_version_number => x_object_version_number
568 );
569
570 -- standard call to get message count and if count is 1, get message info.
571 FND_MSG_PUB.Count_And_Get(
572 p_encoded => FND_API.G_FALSE,
573 p_count => x_msg_count,
574 p_data => x_msg_data);
575
576 EXCEPTION
577 WHEN FND_API.G_EXC_ERROR THEN
578 ROLLBACK TO update_group ;
579 x_return_status := FND_API.G_RET_STS_ERROR;
580
581 FND_MSG_PUB.Count_And_Get(
582 p_encoded => FND_API.G_FALSE,
583 p_count => x_msg_count,
584 p_data => x_msg_data);
585
586 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
587 ROLLBACK TO update_group ;
588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
589
590 FND_MSG_PUB.Count_And_Get(
591 p_encoded => FND_API.G_FALSE,
592 p_count => x_msg_count,
593 p_data => x_msg_data);
594
595 WHEN OTHERS THEN
596 ROLLBACK TO update_group ;
597 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
598
599 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
600 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
601 FND_MSG_PUB.ADD;
602 FND_MSG_PUB.Count_And_Get(
603 p_encoded => FND_API.G_FALSE,
604 p_count => x_msg_count,
605 p_data => x_msg_data);
606 END update_group ;
607
608
609 /*------------------------------------------------------------------------
610 * PROCEDURE create_secured_module
611 *
612 * DESCRIPTION
613 * Creates a created_by_module based criterion
614 * for a data sharing sharing group
615 *
616 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
617 *
618 *
619 * ARGUMENTS
620 *
621 *
622 * NOTES
623 *
624 * MODIFICATION HISTORY
625 *
626 * 10-15-2002 Jyoti Pandey o Created.
627 *
628 ------------------------------------------------------------------------*/
629
630 PROCEDURE create_secured_module (
631 -- input parameters
632 p_init_msg_list IN VARCHAR2,
633 p_dss_secured_module IN dss_secured_module_type,
634 -- output parameters
635 x_secured_item_id OUT NOCOPY NUMBER,
636 x_return_status OUT NOCOPY VARCHAR2,
637 x_msg_count OUT NOCOPY NUMBER,
638 x_msg_data OUT NOCOPY VARCHAR2
639 ) IS
640 row_id varchar2(64);
641 l_dup_count NUMBER := 0;
642 BEGIN
643
644 -- standard start of API savepoint
645 SAVEPOINT create_secured_module ;
646
647 -- initialize message list if p_init_msg_list is set to TRUE.
648 IF FND_API.to_Boolean(p_init_msg_list) THEN
649 FND_MSG_PUB.initialize;
650 END IF;
651
652 -- initialize API return status to success.
653 x_return_status := FND_API.G_RET_STS_SUCCESS;
654
655 --mandatory fields
656 IF (p_dss_secured_module.dss_group_code is null OR
657 p_dss_secured_module.dss_group_code = FND_API.G_MISS_CHAR) THEN
658 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
659 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'dss_group_code' );
660 FND_MSG_PUB.ADD;
661 RAISE FND_API.G_EXC_ERROR;
662 END IF;
663
664 IF (p_dss_secured_module.created_by_module is null OR
665 p_dss_secured_module.created_by_module = FND_API.G_MISS_CHAR) THEN
666 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
667 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'created_by_module' );
668 FND_MSG_PUB.ADD;
669 RAISE FND_API.G_EXC_ERROR;
670 END IF;
671
672
673 -- DSG validation
674 IF HZ_DSS_VALIDATE_PKG.exist_in_dss_groups_b (
675 p_dss_secured_module.dss_group_code) = 'N'
676 THEN
677 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_CODE_INVALID');
678 FND_MSG_PUB.ADD;
679 RAISE FND_API.G_EXC_ERROR;
680 END IF;
681
682 -- status validation
683 IF p_dss_secured_module.status is not null then
684 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(
685 p_dss_secured_module.status, 'REGISTRY_STATUS')= 'N' THEN
686 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_STATUS_VAL_INVALID');
687 FND_MSG_PUB.ADD;
688 RAISE FND_API.G_EXC_ERROR;
689 END IF;
690 END IF;
691
692 ---created_by_module validation
693 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups_gl(
694 p_dss_secured_module.created_by_module, 'HZ_CREATED_BY_MODULES') ='N'
695 THEN
696 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_CREATED_MODULE_INVALID');
697 FND_MSG_PUB.ADD;
698 RAISE FND_API.G_EXC_ERROR;
699 END IF;
700
701 --Bug 2645685 Duplicate criteria should not be created for a Data Sharing
702 --group, class catefory and class code combination
703
704 select count(*) into l_dup_count
705 from HZ_DSS_CRITERIA
706 where dss_group_code = p_dss_secured_module.dss_group_code
707 and owner_table_name = 'AR_LOOKUPS'
708 and owner_table_id1 = 'HZ_CREATED_BY_MODULES'
709 and owner_table_id2 = p_dss_secured_module.created_by_module;
710
711 if l_dup_count >= 1 then
712 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_DUP_CRITERIA_MODULE');
713 FND_MESSAGE.SET_TOKEN('MODULE', p_dss_secured_module.created_by_module);
714 FND_MSG_PUB.ADD;
715 RAISE FND_API.G_EXC_ERROR;
716 END IF;
717
718
719 -- Call the low level table handler
720 HZ_DSS_CRITERIA_PKG.Insert_Row (
721 x_rowid => row_id,
722 x_secured_item_id => x_secured_item_id,
723 x_status => nvl(p_dss_secured_module.status,'A'),
724 x_dss_group_code => p_dss_secured_module.dss_group_code,
725 x_owner_table_name => 'AR_LOOKUPS',
726 x_owner_table_id1 => 'HZ_CREATED_BY_MODULES',
727 x_owner_table_id2 => p_dss_secured_module.created_by_module ,
728 x_object_version_number => 1 );
729
730
731 -- standard call to get message count and if count is 1, get message info.
732 FND_MSG_PUB.Count_And_Get(
733 p_encoded => FND_API.G_FALSE,
734 p_count => x_msg_count,
735 p_data => x_msg_data);
736
737 EXCEPTION
738 WHEN FND_API.G_EXC_ERROR THEN
739 ROLLBACK TO create_secured_module ;
740 x_return_status := FND_API.G_RET_STS_ERROR;
741 FND_MSG_PUB.Count_And_Get(
742 p_encoded => FND_API.G_FALSE,
743 p_count => x_msg_count,
744 p_data => x_msg_data);
745
746 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
747 ROLLBACK TO create_secured_module ;
748 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
749 FND_MSG_PUB.Count_And_Get(
750 p_encoded => FND_API.G_FALSE,
751 p_count => x_msg_count,
752 p_data => x_msg_data);
753
754 WHEN OTHERS THEN
755 ROLLBACK TO create_secured_module ;
756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
757 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
758 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
759 FND_MSG_PUB.ADD;
760 FND_MSG_PUB.Count_And_Get(
761 p_encoded => FND_API.G_FALSE,
762 p_count => x_msg_count,
763 p_data => x_msg_data);
764 END create_secured_module;
765
766 /*------------------------------------------------------------------------
767 * PROCEDURE update_secured_module
768 *
769 * DESCRIPTION
770 * Updates a created_by_module based criterion
771 * for a data sharing sharing group
772 *
773 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
774 *
775 *
776 * ARGUMENTS
777 *
778 *
779 * NOTES
780 *
781 * MODIFICATION HISTORY
782 *
783 * 10-15-2002 Jyoti Pandey o Created.
784 *
785 ------------------------------------------------------------------------*/
786
787 PROCEDURE update_secured_module (
788 p_init_msg_list IN VARCHAR2,
789 p_dss_secured_module IN dss_secured_module_type,
790 x_object_version_number IN OUT NOCOPY NUMBER,
791 x_return_status OUT NOCOPY VARCHAR2,
792 x_msg_count OUT NOCOPY NUMBER,
793 x_msg_data OUT NOCOPY VARCHAR2
794 )
795 IS
796 l_object_version_number NUMBER;
797 l_rowid ROWID;
798 l_dss_group_code HZ_DSS_CRITERIA.DSS_GROUP_CODE%TYPE;
799 l_created_by_module HZ_DSS_CRITERIA.owner_table_id2%TYPE;
800
801 BEGIN
802 -- initialize message list if p_init_msg_list is set to TRUE.
803 IF FND_API.to_Boolean(p_init_msg_list) THEN
804 FND_MSG_PUB.initialize;
805 END IF;
806
807 -- initialize API return status to success.
808 x_return_status := FND_API.G_RET_STS_SUCCESS;
809
810 -- standard start of API savepoint
811 SAVEPOINT update_secured_module ;
812
813 -- check whether record has been updated by another user. If not, lock it.
814 BEGIN
815 SELECT object_version_number, rowid,dss_group_code,owner_table_id2
816 INTO l_object_version_number, l_rowid,l_dss_group_code,l_created_by_module
817 FROM HZ_DSS_CRITERIA
818 WHERE secured_item_id = p_dss_secured_module.secured_item_id
819 FOR UPDATE NOWAIT;
820 EXCEPTION
821 WHEN NO_DATA_FOUND THEN
822 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_ITEM_ID_INVALID');
823 FND_MSG_PUB.ADD;
824 RAISE FND_API.G_EXC_ERROR;
825 END;
826
827
828 IF NOT ((x_object_version_number is null and l_object_version_number is null)
829 OR (x_object_version_number = l_object_version_number))
830 THEN
831 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
832 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_DSS_CRITERIA');
833 FND_MSG_PUB.ADD;
834 RAISE FND_API.G_EXC_ERROR;
835 END IF;
836
837 x_object_version_number := nvl(l_object_version_number, 1) + 1;
838
839 --Bug 2618664 Only status column can be updated
840 IF ( p_dss_secured_module.dss_group_code <> FND_API.G_MISS_CHAR OR
841 l_dss_group_code IS NOT NULL )
842 AND ( l_dss_group_code IS NULL OR
843 p_dss_secured_module.dss_group_code <> l_dss_group_code ) THEN
844 FND_MESSAGE.SET_NAME( 'AR', 'HZ_DSS_CRITERIA_IMMUTABLE' );
845 FND_MSG_PUB.ADD;
846 RAISE FND_API.G_EXC_ERROR;
847 END IF;
848
849
850 IF ( p_dss_secured_module.created_by_module <> FND_API.G_MISS_CHAR OR
851 l_created_by_module IS NOT NULL )
852 AND ( l_created_by_module IS NULL OR
853 p_dss_secured_module.created_by_module <> l_created_by_module ) THEN
854 FND_MESSAGE.SET_NAME( 'AR', 'HZ_DSS_CRITERIA_IMMUTABLE' );
855 FND_MSG_PUB.ADD;
856 RAISE FND_API.G_EXC_ERROR;
857 END IF;
858
859 -- status validation
860 IF p_dss_secured_module.status is not null then
861 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(
862 p_dss_secured_module.status, 'REGISTRY_STATUS')= 'N' THEN
863 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_STATUS_VAL_INVALID');
864 FND_MSG_PUB.ADD;
865 RAISE FND_API.G_EXC_ERROR;
866 END IF;
867 END IF;
868
869 /*
870 ---created_by_module validation
871 IF p_dss_secured_module.created_by_module is not null then
872 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(
873 p_dss_secured_module.created_by_module, 'HZ_CREATED_BY_MODULES') ='N'
874 THEN
875 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_CREATED_MODULE_INVALID');
876 FND_MSG_PUB.ADD;
877 RAISE FND_API.G_EXC_ERROR;
878 END IF;
879 END IF;
880 */
881
882
883 -- Call the low level table handler
884 HZ_DSS_CRITERIA_PKG.Update_Row (
885 x_rowid => l_rowid,
886 x_status => p_dss_secured_module.status,
887 x_dss_group_code => p_dss_secured_module.dss_group_code,
888 x_owner_table_name => 'AR_LOOKUPS',
889 x_owner_table_id1 => 'HZ_CREATED_BY_MODULES',
890 x_owner_table_id2 => p_dss_secured_module.created_by_module ,
891 x_object_version_number => x_object_version_number);
892
893 -- standard call to get message count and if count is 1, get message info.
894 FND_MSG_PUB.Count_And_Get(
895 p_encoded => FND_API.G_FALSE,
896 p_count => x_msg_count,
897 p_data => x_msg_data);
898
899 EXCEPTION
900 WHEN FND_API.G_EXC_ERROR THEN
901 ROLLBACK TO update_secured_module ;
902 x_return_status := FND_API.G_RET_STS_ERROR;
903 FND_MSG_PUB.Count_And_Get(
904 p_encoded => FND_API.G_FALSE,
905 p_count => x_msg_count,
906 p_data => x_msg_data);
907
908 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
909 ROLLBACK TO update_secured_module ;
910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
911 FND_MSG_PUB.Count_And_Get(
912 p_encoded => FND_API.G_FALSE,
913 p_count => x_msg_count,
914 p_data => x_msg_data);
915
916 WHEN OTHERS THEN
917 ROLLBACK TO update_secured_module ;
918 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
919 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
920 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
921 FND_MSG_PUB.ADD;
922 FND_MSG_PUB.Count_And_Get(
923 p_encoded => FND_API.G_FALSE,
924 p_count => x_msg_count,
925 p_data => x_msg_data);
926 END update_secured_module ;
927
928
929
930 /**
931 * PROCEDURE create_secured_criterion
932 *
933 * DESCRIPTION
934 * Creates a criterion that determines how a data sharing sharing group
935 * should be assigned to an entity.
936 *
937 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
938 *
939 *
940 * ARGUMENTS
941 *
942 *
943 * NOTES
944 *
945 * MODIFICATION HISTORY
946 *
947 * 08-27-2002 Colathur Vijayan ("VJN") o Created.
948 *
949 */
950
951 PROCEDURE create_secured_criterion (
952 p_init_msg_list IN VARCHAR2,
953 p_dss_secured_criterion IN dss_secured_criterion_type,
954 x_secured_item_id OUT NOCOPY NUMBER,
955 x_return_status OUT NOCOPY VARCHAR2,
956 x_msg_count OUT NOCOPY NUMBER,
957 x_msg_data OUT NOCOPY VARCHAR2
958 )
959 IS
960 row_id varchar2(64);
961 l_dup_count NUMBER := 0;
962 l_dss_secured_module HZ_DSS_GROUPS_PUB.dss_secured_module_type;
963
964 BEGIN
965
966 -- standard start of API savepoint
967 SAVEPOINT create_secured_criterion ;
968
969 -- initialize message list if p_init_msg_list is set to TRUE.
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 x_return_status := FND_API.G_RET_STS_SUCCESS;
976
977 --Call the create_secured_module API
978 IF (p_dss_secured_criterion.owner_table_name = 'AR_LOOKUPS' AND
979 p_dss_secured_criterion.owner_table_id1 = 'HZ_CREATED_BY_MODULES') THEN
980
981 l_dss_secured_module.secured_item_id :=
982 p_dss_secured_criterion.secured_item_id;
983 l_dss_secured_module.dss_group_code :=
984 p_dss_secured_criterion.dss_group_code;
985 l_dss_secured_module.created_by_module:=
986 p_dss_secured_criterion.owner_table_id2;
987 l_dss_secured_module.status := p_dss_secured_criterion.status;
988
989 --Call the create_secured_module API
990 create_secured_module(p_init_msg_list,
991 l_dss_secured_module,
992 x_secured_item_id ,
993 x_return_status,
994 x_msg_count,
995 x_msg_data);
996
997 ELSE
998
999 --mandatory dss_group
1000 IF (p_dss_secured_criterion.dss_group_code is null OR
1001 p_dss_secured_criterion.dss_group_code = FND_API.G_MISS_CHAR) THEN
1002 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
1003 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'dss_group_code' );
1004 FND_MSG_PUB.ADD;
1005 RAISE FND_API.G_EXC_ERROR;
1006 END IF;
1007
1008 -- VALIDATION
1009 -- PASSED IN GROUP CODE SHOULD BE VALID
1010 IF HZ_DSS_VALIDATE_PKG.exist_in_dss_groups_b (
1011 p_dss_secured_criterion.dss_group_code) = 'N'
1012 THEN
1016 END IF;
1013 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_CODE_INVALID');
1014 FND_MSG_PUB.ADD;
1015 RAISE FND_API.G_EXC_ERROR;
1017
1018 -- STATUS VALIDATION
1019 IF p_dss_secured_criterion.status is not null then
1020 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(
1021 p_dss_secured_criterion.status, 'REGISTRY_STATUS')= 'N' THEN
1022 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_STATUS_VAL_INVALID');
1023 FND_MSG_PUB.ADD;
1024 RAISE FND_API.G_EXC_ERROR;
1025 END IF;
1026 END IF;
1027
1028
1029 -- Call the low level table handler
1030 HZ_DSS_CRITERIA_PKG.Insert_Row (
1031 x_rowid => row_id,
1032 x_secured_item_id => x_secured_item_id,
1033 x_status => nvl(p_dss_secured_criterion.status,'A'),
1034 x_dss_group_code => p_dss_secured_criterion.dss_group_code,
1035 x_owner_table_name => p_dss_secured_criterion.owner_table_name,
1036 x_owner_table_id1 => p_dss_secured_criterion.owner_table_id1 ,
1037 x_owner_table_id2 => p_dss_secured_criterion.owner_table_id2 ,
1038 x_object_version_number => 1);
1039
1040 END IF;
1041
1042
1043 -- standard call to get message count and if count is 1, get message info.
1044 FND_MSG_PUB.Count_And_Get(
1045 p_encoded => FND_API.G_FALSE,
1046 p_count => x_msg_count,
1047 p_data => x_msg_data);
1048
1049 EXCEPTION
1050 WHEN FND_API.G_EXC_ERROR THEN
1051 ROLLBACK TO create_secured_criterion ;
1052 x_return_status := FND_API.G_RET_STS_ERROR;
1053 FND_MSG_PUB.Count_And_Get(
1054 p_encoded => FND_API.G_FALSE,
1055 p_count => x_msg_count,
1056 p_data => x_msg_data);
1057
1058 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1059 ROLLBACK TO create_secured_criterion ;
1060 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1061 FND_MSG_PUB.Count_And_Get(
1062 p_encoded => FND_API.G_FALSE,
1063 p_count => x_msg_count,
1064 p_data => x_msg_data);
1065
1066 WHEN OTHERS THEN
1067 ROLLBACK TO create_secured_criterion ;
1068 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1069 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
1070 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1071 FND_MSG_PUB.ADD;
1072 FND_MSG_PUB.Count_And_Get(
1073 p_encoded => FND_API.G_FALSE,
1074 p_count => x_msg_count,
1075 p_data => x_msg_data);
1076 END create_secured_criterion ;
1077
1078
1079 /**
1080 * PROCEDURE update_secured_criterion
1081 *
1082 * DESCRIPTION
1083 * Updates a criterion that determines how a data sharing sharing group
1084 * should be assigned to an entity.
1085 *
1086 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1087 *
1088 *
1089 * ARGUMENTS
1090 *
1091 *
1092 * NOTES
1093 *
1094 * MODIFICATION HISTORY
1095 *
1096 * 08-13-2002 Colathur Vijayan ("VJN") o Created.
1097 *
1098 */
1099
1100 PROCEDURE update_secured_criterion (
1101 p_init_msg_list IN VARCHAR2,
1102 p_dss_secured_criterion IN dss_secured_criterion_type,
1103 x_object_version_number IN OUT NOCOPY NUMBER,
1104 x_return_status OUT NOCOPY VARCHAR2,
1105 x_msg_count OUT NOCOPY NUMBER,
1106 x_msg_data OUT NOCOPY VARCHAR2
1107 )
1108 IS
1109 l_object_version_number NUMBER;
1110 l_rowid ROWID;
1111 l_dss_secured_module HZ_DSS_GROUPS_PUB.dss_secured_module_type;
1112
1113 BEGIN
1114 -- initialize message list if p_init_msg_list is set to TRUE.
1115 IF FND_API.to_Boolean(p_init_msg_list) THEN
1116 FND_MSG_PUB.initialize;
1117 END IF;
1118
1119 -- initialize API return status to success.
1120 x_return_status := FND_API.G_RET_STS_SUCCESS;
1121
1122 -- standard start of API savepoint
1123 SAVEPOINT update_secured_criterion ;
1124
1125 -- check whether record has been updated by another user. If not, lock it.
1126 BEGIN
1127 SELECT object_version_number, rowid
1128 INTO l_object_version_number, l_rowid
1129 FROM HZ_DSS_CRITERIA
1130 WHERE secured_item_id = p_dss_secured_criterion.secured_item_id
1131 FOR UPDATE NOWAIT;
1132 EXCEPTION
1133 WHEN NO_DATA_FOUND THEN
1134 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_ITEM_ID_INVALID');
1135 FND_MSG_PUB.ADD;
1136 RAISE FND_API.G_EXC_ERROR;
1137 END;
1138
1139
1140 IF NOT ((x_object_version_number is null and l_object_version_number is null)
1141 OR (x_object_version_number = l_object_version_number))
1142 THEN
1143 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1144 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_DSS_CRITERIA');
1145 FND_MSG_PUB.ADD;
1146 RAISE FND_API.G_EXC_ERROR;
1147 END IF;
1148
1149
1150
1151 --Call the create_secured_module API
1152 IF (p_dss_secured_criterion.owner_table_name = 'AR_LOOKUPS' AND
1153 p_dss_secured_criterion.owner_table_id1 = 'HZ_CREATED_BY_MODULES') THEN
1154
1155 l_dss_secured_module.secured_item_id :=
1156 p_dss_secured_criterion.secured_item_id;
1157 l_dss_secured_module.dss_group_code :=
1158 p_dss_secured_criterion.dss_group_code;
1159 l_dss_secured_module.created_by_module:=
1160 p_dss_secured_criterion.owner_table_id2;
1161 l_dss_secured_module.status := p_dss_secured_criterion.status;
1162
1163 --Call the create_secured_module API
1164 update_secured_module(p_init_msg_list,
1165 l_dss_secured_module,
1166 x_object_version_number,
1167 x_return_status,
1168 x_msg_count,
1169 x_msg_data);
1170
1171
1172 ELSE
1173
1174 -- VALIDATION
1175
1176 -- STATUS VALIDATION
1177
1178 IF p_dss_secured_criterion.status is not null then
1179 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(
1180 p_dss_secured_criterion.status, 'REGISTRY_STATUS')= 'N'
1181 THEN
1182 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_STATUS_VAL_INVALID');
1183 FND_MSG_PUB.ADD;
1184 RAISE FND_API.G_EXC_ERROR;
1185 END IF;
1186 END IF;
1187
1188 x_object_version_number := nvl(l_object_version_number, 1) + 1;
1189
1190 -- Call the low level table handler
1191 HZ_DSS_CRITERIA_PKG.Update_Row (
1192 x_rowid => l_rowid,
1193 x_status => p_dss_secured_criterion.status,
1194 x_dss_group_code => p_dss_secured_criterion.dss_group_code,
1195 x_owner_table_name => p_dss_secured_criterion.owner_table_name,
1196 x_owner_table_id1 => p_dss_secured_criterion.owner_table_id1 ,
1197 x_owner_table_id2 => p_dss_secured_criterion.owner_table_id2 ,
1198 x_object_version_number => x_object_version_number);
1199
1200 x_object_version_number := nvl(l_object_version_number, 1) + 1;
1201
1202 END IF;
1203 -- standard call to get message count and if count is 1, get message info.
1204 FND_MSG_PUB.Count_And_Get(
1205 p_encoded => FND_API.G_FALSE,
1206 p_count => x_msg_count,
1207 p_data => x_msg_data);
1208
1209 EXCEPTION
1210 WHEN FND_API.G_EXC_ERROR THEN
1211 ROLLBACK TO update_secured_criterion ;
1212 x_return_status := FND_API.G_RET_STS_ERROR;
1213 FND_MSG_PUB.Count_And_Get(
1214 p_encoded => FND_API.G_FALSE,
1215 p_count => x_msg_count,
1216 p_data => x_msg_data);
1217
1218 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1219 ROLLBACK TO update_secured_criterion ;
1220 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1221 FND_MSG_PUB.Count_And_Get(
1222 p_encoded => FND_API.G_FALSE,
1223 p_count => x_msg_count,
1224 p_data => x_msg_data);
1225
1226 WHEN OTHERS THEN
1227 ROLLBACK TO update_secured_criterion ;
1228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1229 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
1230 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1231 FND_MSG_PUB.ADD;
1232 FND_MSG_PUB.Count_And_Get(
1233 p_encoded => FND_API.G_FALSE,
1234 p_count => x_msg_count,
1235 p_data => x_msg_data);
1236 END update_secured_criterion ;
1237
1238
1239
1240
1241 /**
1242 * PROCEDURE create_secured_classification
1243 *
1244 * DESCRIPTION
1245 * Creates a criterion that determines how a data sharing sharing group
1246 * should be assigned to an entity.
1247 *
1248 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1249 *
1250 *
1251 * ARGUMENTS
1252 *
1253 *
1254 * NOTES
1255 *
1256 * MODIFICATION HISTORY
1257 *
1258 * 08-13-2002 Colathur Vijayan ("VJN") o Created.
1259 *
1260 */
1261
1262 PROCEDURE create_secured_classification(
1263 -- input parameters
1264 p_init_msg_list IN VARCHAR2,
1265 p_dss_secured_class IN dss_secured_class_type,
1266 -- output parameters
1267 x_secured_item_id OUT NOCOPY NUMBER,
1268 x_return_status OUT NOCOPY VARCHAR2,
1269 x_msg_count OUT NOCOPY NUMBER,
1270 x_msg_data OUT NOCOPY VARCHAR2
1271 )
1272 IS
1273 row_id varchar2(64);
1274 l_dup_count NUMBER := 0;
1275 BEGIN
1276
1277 -- standard start of API savepoint
1278 SAVEPOINT create_secured_classification ;
1279
1280 -- initialize message list if p_init_msg_list is set to TRUE.
1281 IF FND_API.to_Boolean(p_init_msg_list) THEN
1282 FND_MSG_PUB.initialize;
1283 END IF;
1284
1285 -- initialize API return status to success.
1286 x_return_status := FND_API.G_RET_STS_SUCCESS;
1287
1288 --mandatory fields
1289 IF (p_dss_secured_class.dss_group_code is null OR
1290 p_dss_secured_class.dss_group_code = FND_API.G_MISS_CHAR) THEN
1291 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
1292 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'dss_group_code' );
1293 FND_MSG_PUB.ADD;
1294 RAISE FND_API.G_EXC_ERROR;
1295 END IF;
1296
1297 IF (p_dss_secured_class.class_category is null OR
1298 p_dss_secured_class.class_category = FND_API.G_MISS_CHAR) THEN
1299 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
1300 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'class_category' );
1301 FND_MSG_PUB.ADD;
1302 RAISE FND_API.G_EXC_ERROR;
1303 END IF;
1304
1305 IF (p_dss_secured_class.class_code is null OR
1306 p_dss_secured_class.class_code = FND_API.G_MISS_CHAR) THEN
1310 RAISE FND_API.G_EXC_ERROR;
1307 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
1308 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'class_code' );
1309 FND_MSG_PUB.ADD;
1311 END IF;
1312
1313 -- VALIDATION
1314
1315 -- PASSED IN GROUP CODE SHOULD BE VALID
1316 IF HZ_DSS_VALIDATE_PKG.exist_in_dss_groups_b (p_dss_secured_class.dss_group_code) = 'N'
1317 THEN
1318 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_CODE_INVALID');
1319 FND_MSG_PUB.ADD;
1320 RAISE FND_API.G_EXC_ERROR;
1321 END IF;
1322
1323 -- PASSED IN CLASS CATEGORY SHOULD BE VALID
1324 IF HZ_DSS_VALIDATE_PKG.exist_in_hz_class_categories (p_dss_secured_class.class_category) = 'N'
1325 THEN
1326 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_CL_CATEGORY_INVALID');
1327 FND_MSG_PUB.ADD;
1328 RAISE FND_API.G_EXC_ERROR;
1329 END IF;
1330
1331 -- PASSED IN CLASS CODE SHOULD BE VALID
1332 IF HZ_DSS_VALIDATE_PKG.exist_in_fnd_lookups(p_dss_secured_class.class_code,p_dss_secured_class.class_category ) = 'N'
1333 THEN
1334 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_CL_CODE_INVALID');
1335 FND_MSG_PUB.ADD;
1336 RAISE FND_API.G_EXC_ERROR;
1337 END IF;
1338
1339
1340 -- STATUS VALIDATION
1341 IF p_dss_secured_class.status is not null then
1342 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(p_dss_secured_class.status,
1343 'REGISTRY_STATUS')= 'N' THEN
1344 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_STATUS_VAL_INVALID');
1345 FND_MSG_PUB.ADD;
1346 RAISE FND_API.G_EXC_ERROR;
1347 END IF;
1348 END IF;
1349
1350 --Bug 2645685 Duplicate criteria should not be created for a Data Sharing
1351 --group, class catefory and class code combination
1352
1353 select count(*) into l_dup_count
1354 from HZ_DSS_CRITERIA
1355 where dss_group_code = p_dss_secured_class.dss_group_code
1356 and owner_table_name = 'FND_LOOKUP_VALUES'
1357 and owner_table_id1 = p_dss_secured_class.class_category
1358 and owner_table_id2 = p_dss_secured_class.class_code;
1359
1360 if l_dup_count >= 1 then
1361 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_DUP_CRITERIA_CLASS');
1362 FND_MESSAGE.SET_TOKEN('CATEGORY', p_dss_secured_class.class_category);
1363 FND_MESSAGE.SET_TOKEN('CLASS', p_dss_secured_class.class_code);
1364 FND_MSG_PUB.ADD;
1365 RAISE FND_API.G_EXC_ERROR;
1366 END IF;
1367
1368
1369 -- Call the low level table handler
1370 HZ_DSS_CRITERIA_PKG.Insert_Row (
1371 x_rowid => row_id,
1372 x_secured_item_id => x_secured_item_id,
1373 x_status => nvl(p_dss_secured_class.status,'A'),
1374 x_dss_group_code => p_dss_secured_class.dss_group_code,
1375 x_owner_table_name => 'FND_LOOKUP_VALUES',
1376 x_owner_table_id1 => p_dss_secured_class.class_category,
1377 x_owner_table_id2 => p_dss_secured_class.class_code,
1378 x_object_version_number => 1);
1379
1380
1381 -- standard call to get message count and if count is 1, get message info.
1382 FND_MSG_PUB.Count_And_Get(
1383 p_encoded => FND_API.G_FALSE,
1384 p_count => x_msg_count,
1385 p_data => x_msg_data);
1386
1387 EXCEPTION
1388 WHEN FND_API.G_EXC_ERROR THEN
1389 ROLLBACK TO create_secured_classification ;
1390 x_return_status := FND_API.G_RET_STS_ERROR;
1391 FND_MSG_PUB.Count_And_Get(
1392 p_encoded => FND_API.G_FALSE,
1393 p_count => x_msg_count,
1394 p_data => x_msg_data);
1395
1396 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1397 ROLLBACK TO create_secured_classification ;
1398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1399 FND_MSG_PUB.Count_And_Get(
1400 p_encoded => FND_API.G_FALSE,
1401 p_count => x_msg_count,
1402 p_data => x_msg_data);
1403
1404 WHEN OTHERS THEN
1405 ROLLBACK TO create_secured_classification ;
1406 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1407 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
1408 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1409 FND_MSG_PUB.ADD;
1410 FND_MSG_PUB.Count_And_Get(
1411 p_encoded => FND_API.G_FALSE,
1412 p_count => x_msg_count,
1413 p_data => x_msg_data);
1414 END create_secured_classification ;
1415
1416 /**
1417 * PROCEDURE update_secured_classification
1418 *
1419 * DESCRIPTION
1420 * Updates a criterion that determines how a data sharing sharing group
1421 * should be assigned to an entity.
1422 *
1423 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1424 *
1425 *
1426 * ARGUMENTS
1427 *
1428 *
1429 * NOTES
1430 *
1431 * MODIFICATION HISTORY
1432 *
1433 * 08-13-2002 Colathur Vijayan ("VJN") o Created.
1434 *
1435 */
1436
1437 PROCEDURE update_secured_classification (
1438 -- input parameters
1439 p_init_msg_list IN VARCHAR2,
1440 p_dss_secured_class IN dss_secured_class_type,
1441 -- in/out parameters
1442 x_object_version_number IN OUT NOCOPY NUMBER,
1443 -- output parameters
1444 x_return_status OUT NOCOPY VARCHAR2,
1445 x_msg_count OUT NOCOPY NUMBER,
1446 x_msg_data OUT NOCOPY VARCHAR2
1447 )
1448 IS
1452 l_class_category HZ_DSS_CRITERIA.owner_table_id1%TYPE;
1449 l_object_version_number NUMBER;
1450 l_rowid ROWID;
1451 l_dss_group_code HZ_DSS_CRITERIA.DSS_GROUP_CODE%TYPE;
1453 l_class_code HZ_DSS_CRITERIA.owner_table_id2%TYPE;
1454
1455 BEGIN
1456 -- initialize message list if p_init_msg_list is set to TRUE.
1457 IF FND_API.to_Boolean(p_init_msg_list) THEN
1458 FND_MSG_PUB.initialize;
1459 END IF;
1460
1461 -- initialize API return status to success.
1462 x_return_status := FND_API.G_RET_STS_SUCCESS;
1463
1464 -- standard start of API savepoint
1465 SAVEPOINT update_secured_classification ;
1466
1467 -- check whether record has been updated by another user. If not, lock it.
1468
1469 BEGIN
1470 SELECT object_version_number, rowid , dss_group_code,
1471 owner_table_id1,owner_table_id2
1472 INTO l_object_version_number, l_rowid ,l_dss_group_code ,
1473 l_class_category , l_class_code
1474 FROM HZ_DSS_CRITERIA
1475 WHERE secured_item_id = p_dss_secured_class.secured_item_id
1476 FOR UPDATE NOWAIT;
1477 EXCEPTION
1478 WHEN NO_DATA_FOUND THEN
1479 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_ITEM_ID_INVALID');
1480 FND_MSG_PUB.ADD;
1481 RAISE FND_API.G_EXC_ERROR;
1482 END;
1483
1484 IF NOT ((x_object_version_number is null and l_object_version_number is null)
1485 OR (x_object_version_number = l_object_version_number))
1486 THEN
1487 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1488 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_DSS_CRITERIA');
1489 FND_MSG_PUB.ADD;
1490 RAISE FND_API.G_EXC_ERROR;
1491 END IF;
1492
1493 x_object_version_number := nvl(l_object_version_number, 1) + 1;
1494
1495 -- VALIDATION
1496 --Bug 2618664 Only status column can be updated
1497 IF ( p_dss_secured_class.dss_group_code <> FND_API.G_MISS_CHAR OR
1498 l_dss_group_code IS NOT NULL )
1499 AND ( l_dss_group_code IS NULL OR
1500 p_dss_secured_class.dss_group_code <> l_dss_group_code ) THEN
1501 FND_MESSAGE.SET_NAME( 'AR', 'HZ_DSS_CRITERIA_IMMUTABLE' );
1502 FND_MSG_PUB.ADD;
1503 RAISE FND_API.G_EXC_ERROR;
1504 END IF;
1505
1506
1507 IF ( p_dss_secured_class.class_category <> FND_API.G_MISS_CHAR OR
1508 l_class_category IS NOT NULL )
1509 AND ( l_class_category IS NULL OR
1510 p_dss_secured_class.class_category <> l_class_category ) THEN
1511 FND_MESSAGE.SET_NAME( 'AR', 'HZ_DSS_CRITERIA_IMMUTABLE' );
1512 FND_MSG_PUB.ADD;
1513 RAISE FND_API.G_EXC_ERROR;
1514 END IF;
1515
1516 IF ( p_dss_secured_class.class_code <> FND_API.G_MISS_CHAR OR
1517 l_class_code IS NOT NULL )
1518 AND ( l_class_code IS NULL OR
1519 p_dss_secured_class.class_code <> l_class_code ) THEN
1520 FND_MESSAGE.SET_NAME( 'AR', 'HZ_DSS_CRITERIA_IMMUTABLE' );
1521 FND_MSG_PUB.ADD;
1522 RAISE FND_API.G_EXC_ERROR;
1523 END IF;
1524
1525
1526 -- STATUS VALIDATION
1527 IF p_dss_secured_class.status is not null then
1528 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(p_dss_secured_class.status,
1529 'REGISTRY_STATUS')= 'N' THEN
1530 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_STATUS_VAL_INVALID');
1531 FND_MSG_PUB.ADD;
1532 RAISE FND_API.G_EXC_ERROR;
1533 END IF;
1534 END IF;
1535
1536 -- Call the low level table handler
1537 HZ_DSS_CRITERIA_PKG.Update_Row (
1538 x_rowid => l_rowid,
1539 x_status => p_dss_secured_class.status,
1540 x_dss_group_code => p_dss_secured_class.dss_group_code,
1541 x_owner_table_name => 'AR_LOOKUPS',
1542 x_owner_table_id1 => p_dss_secured_class.class_category,
1543 x_owner_table_id2 => p_dss_secured_class.class_code,
1544 x_object_version_number => x_object_version_number
1545 );
1546
1547 -- standard call to get message count and if count is 1, get message info.
1548 FND_MSG_PUB.Count_And_Get(
1549 p_encoded => FND_API.G_FALSE,
1550 p_count => x_msg_count,
1551 p_data => x_msg_data);
1552
1553 EXCEPTION
1554 WHEN FND_API.G_EXC_ERROR THEN
1555 ROLLBACK TO update_secured_classification ;
1556 x_return_status := FND_API.G_RET_STS_ERROR;
1557 FND_MSG_PUB.Count_And_Get(
1558 p_encoded => FND_API.G_FALSE,
1559 p_count => x_msg_count,
1560 p_data => x_msg_data);
1561
1562 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1563 ROLLBACK TO update_secured_classification ;
1564 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1565 FND_MSG_PUB.Count_And_Get(
1566 p_encoded => FND_API.G_FALSE,
1567 p_count => x_msg_count,
1568 p_data => x_msg_data);
1569
1570 WHEN OTHERS THEN
1571 ROLLBACK TO update_secured_classification ;
1572 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1573 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
1574 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1575 FND_MSG_PUB.ADD;
1576 FND_MSG_PUB.Count_And_Get(
1577 p_encoded => FND_API.G_FALSE,
1578 p_count => x_msg_count,
1579 p_data => x_msg_data);
1580 END update_secured_classification ;
1581
1582
1583
1584 /**
1585 * PROCEDURE create_secured_rel_type
1586 *
1587 * DESCRIPTION
1588 * The create_secured_rel_type procedure creates a record in HZ_DSS_CRITERIA that
1589 * identifies a Relationship Type to be used as a criterion to determine if data falls under
1590 * the Data Sharing Group.
1591 *
1592 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1593 *
1594 *
1595 * ARGUMENTS
1596 *
1597 *
1598 * NOTES
1599 *
1600 * MODIFICATION HISTORY
1601 *
1602 * 08-13-2002 Colathur Vijayan ("VJN") o Created.
1603 *
1604 */
1605
1606 PROCEDURE create_secured_rel_type (
1607 -- input parameters
1608 p_init_msg_list IN VARCHAR2,
1609 p_dss_secured_rel_type IN dss_secured_rel_type,
1610 -- output parameters
1611 x_secured_item_id OUT NOCOPY NUMBER,
1612 x_return_status OUT NOCOPY VARCHAR2,
1613 x_msg_count OUT NOCOPY NUMBER,
1614 x_msg_data OUT NOCOPY VARCHAR2
1615 )
1616 IS
1617 row_id varchar2(64);
1618 l_dup_count NUMBER := 0;
1619 l_rel_type HZ_RELATIONSHIP_TYPES.RELATIONSHIP_TYPE%TYPE;
1620
1621 BEGIN
1622
1623 -- standard start of API savepoint
1624 SAVEPOINT create_secured_rel_type ;
1625
1626 -- initialize message list if p_init_msg_list is set to TRUE.
1627 IF FND_API.to_Boolean(p_init_msg_list) THEN
1628 FND_MSG_PUB.initialize;
1629 END IF;
1630
1631 -- initialize API return status to success.
1632 x_return_status := FND_API.G_RET_STS_SUCCESS;
1633
1634 -- VALIDATION of mandatory fields
1635 IF (p_dss_secured_rel_type.dss_group_code is null OR
1636 p_dss_secured_rel_type.dss_group_code = FND_API.G_MISS_CHAR) THEN
1637 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
1638 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'dss_group_code' );
1639 FND_MSG_PUB.ADD;
1640 RAISE FND_API.G_EXC_ERROR;
1641 END IF;
1642
1643 IF (p_dss_secured_rel_type.relationship_type_id is null OR
1644 p_dss_secured_rel_type.relationship_type_id = FND_API.G_MISS_NUM) THEN
1645 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
1646 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'relationship_type_id' );
1647 FND_MSG_PUB.ADD;
1648 RAISE FND_API.G_EXC_ERROR;
1649 END IF;
1650
1651
1652
1653 -- PASSED IN GROUP CODE SHOULD BE VALID
1654 IF HZ_DSS_VALIDATE_PKG.exist_in_dss_groups_b (p_dss_secured_rel_type.dss_group_code) = 'N'
1655 THEN
1656 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_CODE_INVALID');
1657 FND_MSG_PUB.ADD;
1658 RAISE FND_API.G_EXC_ERROR;
1659 END IF;
1660
1661 -- PASSED IN RELATIONSHIP_TYPE_ID SHOULD BE VALID
1662 IF HZ_DSS_VALIDATE_PKG.exist_in_hz_relationship_types (p_dss_secured_rel_type.relationship_type_id) = 'N'
1663 THEN
1664 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_REL_TYPE_ID_INVALID');
1665 FND_MSG_PUB.ADD;
1666 RAISE FND_API.G_EXC_ERROR;
1667 END IF;
1668
1669
1670 -- STATUS VALIDATION
1671 IF p_dss_secured_rel_type.status is not null then
1672 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(p_dss_secured_rel_type.status,
1673 'REGISTRY_STATUS')= 'N' THEN
1674 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_STATUS_VAL_INVALID');
1675 FND_MSG_PUB.ADD;
1676 RAISE FND_API.G_EXC_ERROR;
1677 END IF;
1678 END IF;
1679
1680 --Bug 2645685 Duplicate criteria should not be created for a Data Sharing
1681 --group, class catefory and class code combination
1682
1683 select count(*) into l_dup_count
1684 from HZ_DSS_CRITERIA
1685 where dss_group_code = p_dss_secured_rel_type.dss_group_code
1686 and owner_table_name = 'HZ_RELATIONSHIP_TYPES'
1687 and owner_table_id1 = TO_CHAR(p_dss_secured_rel_type.relationship_type_id);
1688
1689 if l_dup_count >= 1 then
1690 --get the rel type
1691 select relationship_type into l_rel_type
1692 from HZ_RELATIONSHIP_TYPES
1693 where relationship_type_id = p_dss_secured_rel_type.relationship_type_id;
1694
1695 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_DUP_CRITERIA_REL');
1696 FND_MESSAGE.SET_TOKEN('RELROLE', l_rel_type);
1697 FND_MSG_PUB.ADD;
1698 RAISE FND_API.G_EXC_ERROR;
1699 END IF;
1700
1701
1702 -- Call the low level table handler
1703 HZ_DSS_CRITERIA_PKG.Insert_Row (
1704 x_rowid => row_id,
1705 x_secured_item_id => x_secured_item_id,
1706 x_status => nvl(p_dss_secured_rel_type.status,'A'),
1707 x_dss_group_code => p_dss_secured_rel_type.dss_group_code,
1708 x_owner_table_name => 'HZ_RELATIONSHIP_TYPES',
1709 x_owner_table_id1 => p_dss_secured_rel_type.relationship_type_id,
1710 x_object_version_number => 1);
1711
1712
1713 -- standard call to get message count and if count is 1, get message info.
1714 FND_MSG_PUB.Count_And_Get(
1715 p_encoded => FND_API.G_FALSE,
1716 p_count => x_msg_count,
1717 p_data => x_msg_data);
1718
1719 EXCEPTION
1720 WHEN FND_API.G_EXC_ERROR THEN
1721 ROLLBACK TO create_secured_rel_type ;
1722 x_return_status := FND_API.G_RET_STS_ERROR;
1723 FND_MSG_PUB.Count_And_Get(
1724 p_encoded => FND_API.G_FALSE,
1725 p_count => x_msg_count,
1726 p_data => x_msg_data);
1727
1728 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1729 ROLLBACK TO create_secured_rel_type ;
1733 p_count => x_msg_count,
1730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1731 FND_MSG_PUB.Count_And_Get(
1732 p_encoded => FND_API.G_FALSE,
1734 p_data => x_msg_data);
1735
1736 WHEN OTHERS THEN
1737 ROLLBACK TO create_secured_rel_type ;
1738 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1739 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
1740 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1741 FND_MSG_PUB.ADD;
1742 FND_MSG_PUB.Count_And_Get(
1743 p_encoded => FND_API.G_FALSE,
1744 p_count => x_msg_count,
1745 p_data => x_msg_data);
1746 END create_secured_rel_type ;
1747
1748 /**
1749 * PROCEDURE update_secured_rel_type
1750 *
1751 * DESCRIPTION
1752 * The UPDATE_SECURED_REL_TYPE procedure updates a record in HZ_DSS_CRITERIA that identifies
1753 * a Relationship Type to be used as a criterion to determine if data falls under the Data Sharing
1754 * Group. Currently, only the STATUS column can be updated at this time.
1755 *
1756 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1757 *
1758 *
1759 * ARGUMENTS
1760 *
1761 *
1762 * NOTES
1763 *
1764 * MODIFICATION HISTORY
1765 *
1766 * 08-13-2002 Colathur Vijayan ("VJN") o Created.
1767 *
1768 */
1769
1770 PROCEDURE update_secured_rel_type (
1771 p_init_msg_list IN VARCHAR2,
1772 p_dss_secured_rel_type IN dss_secured_rel_type,
1773 x_object_version_number IN OUT NOCOPY NUMBER,
1774 x_return_status OUT NOCOPY VARCHAR2,
1775 x_msg_count OUT NOCOPY NUMBER,
1776 x_msg_data OUT NOCOPY VARCHAR2
1777 )
1778 IS
1779 l_object_version_number NUMBER;
1780 l_rowid ROWID;
1781 l_dss_group_code HZ_DSS_CRITERIA.DSS_GROUP_CODE%TYPE;
1782 l_relationship_type_id HZ_DSS_CRITERIA.owner_table_id1%TYPE;
1783
1784 BEGIN
1785 -- initialize message list if p_init_msg_list is set to TRUE.
1786 IF FND_API.to_Boolean(p_init_msg_list) THEN
1787 FND_MSG_PUB.initialize;
1788 END IF;
1789
1790 -- initialize API return status to success.
1791 x_return_status := FND_API.G_RET_STS_SUCCESS;
1792
1793 -- standard start of API savepoint
1794 SAVEPOINT update_secured_rel_type ;
1795
1796 -- check whether record has been updated by another user. If not, lock it.
1797
1798 BEGIN
1799 SELECT object_version_number, rowid , dss_group_code, owner_table_id1
1800 INTO l_object_version_number, l_rowid,l_dss_group_code,
1801 l_relationship_type_id
1802 FROM HZ_DSS_CRITERIA
1803 WHERE secured_item_id = p_dss_secured_rel_type.secured_item_id
1804 FOR UPDATE NOWAIT;
1805 EXCEPTION
1806 WHEN NO_DATA_FOUND THEN
1807 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_ITEM_ID_INVALID');
1808 FND_MSG_PUB.ADD;
1809 RAISE FND_API.G_EXC_ERROR;
1810 END;
1811
1812 IF NOT ((x_object_version_number is null and l_object_version_number is null)
1813 OR (x_object_version_number = l_object_version_number))
1814 THEN
1815 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1816 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_DSS_CRITERIA');
1817 FND_MSG_PUB.ADD;
1818 RAISE FND_API.G_EXC_ERROR;
1819 END IF;
1820
1821 x_object_version_number := nvl(l_object_version_number, 1) + 1;
1822
1823 -- VALIDATION
1824 IF ( p_dss_secured_rel_type.dss_group_code <> FND_API.G_MISS_CHAR OR
1825 l_dss_group_code IS NOT NULL )
1826 AND ( l_dss_group_code IS NULL OR
1827 p_dss_secured_rel_type.dss_group_code <> l_dss_group_code ) THEN
1828 FND_MESSAGE.SET_NAME( 'AR', 'HZ_DSS_CRITERIA_IMMUTABLE' );
1829 FND_MSG_PUB.ADD;
1830 RAISE FND_API.G_EXC_ERROR;
1831 END IF;
1832
1833
1834 IF ( p_dss_secured_rel_type.relationship_type_id <> FND_API.G_MISS_NUM OR
1835 l_relationship_type_id IS NOT NULL )
1836 AND ( l_relationship_type_id IS NULL OR
1837 p_dss_secured_rel_type.relationship_type_id <> l_relationship_type_id )
1838 THEN
1839 FND_MESSAGE.SET_NAME( 'AR', 'HZ_DSS_CRITERIA_IMMUTABLE' );
1840 FND_MSG_PUB.ADD;
1841 RAISE FND_API.G_EXC_ERROR;
1842 END IF;
1843
1844
1845 -- STATUS VALIDATION
1846
1847 IF p_dss_secured_rel_type.status is not null then
1848 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(p_dss_secured_rel_type.status,
1849 'REGISTRY_STATUS')= 'N' THEN
1850 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_STATUS_VAL_INVALID');
1851 FND_MSG_PUB.ADD;
1852 RAISE FND_API.G_EXC_ERROR;
1853 END IF;
1854 END IF;
1855
1856
1857 -- Call the low level table handler
1858 HZ_DSS_CRITERIA_PKG.Update_Row (
1859 x_rowid => l_rowid,
1860 x_status => p_dss_secured_rel_type.status,
1861 x_dss_group_code => p_dss_secured_rel_type.dss_group_code,
1862 x_owner_table_name => 'HZ_RELATIONSHIP_TYPES',
1863 x_owner_table_id1 => p_dss_secured_rel_type.relationship_type_id,
1864 x_object_version_number => x_object_version_number);
1865
1866
1867 -- standard call to get message count and if count is 1, get message info.
1868 FND_MSG_PUB.Count_And_Get(
1869 p_encoded => FND_API.G_FALSE,
1870 p_count => x_msg_count,
1871 p_data => x_msg_data);
1872
1873 EXCEPTION
1874 WHEN FND_API.G_EXC_ERROR THEN
1875 ROLLBACK TO update_secured_rel_type ;
1876 x_return_status := FND_API.G_RET_STS_ERROR;
1877 FND_MSG_PUB.Count_And_Get(
1878 p_encoded => FND_API.G_FALSE,
1879 p_count => x_msg_count,
1880 p_data => x_msg_data);
1881
1882 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1883 ROLLBACK TO update_secured_rel_type ;
1884 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1885 FND_MSG_PUB.Count_And_Get(
1886 p_encoded => FND_API.G_FALSE,
1887 p_count => x_msg_count,
1888 p_data => x_msg_data);
1889
1890 WHEN OTHERS THEN
1891 ROLLBACK TO update_secured_rel_type ;
1892 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1893 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
1894 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1895 FND_MSG_PUB.ADD;
1896 FND_MSG_PUB.Count_And_Get(
1897 p_encoded => FND_API.G_FALSE,
1898 p_count => x_msg_count,
1899 p_data => x_msg_data);
1900 END update_secured_rel_type ;
1901
1902
1903
1904 /**
1905 * PROCEDURE create_assignment
1906 *
1907 * DESCRIPTION
1908 * The create_assignment procedure creates a Data Sharing Group Assignment (HZ_DSS_ASSIGNMENTS)
1909 * to a given entity.
1910 *
1911 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1912 *
1913 *
1914 * ARGUMENTS
1915 *
1916 *
1917 * NOTES
1918 *
1919 * MODIFICATION HISTORY
1920 *
1921 * 08-15-2002 Colathur Vijayan ("VJN") o Created.
1922 *
1923 */
1924
1925 PROCEDURE create_assignment (
1926 p_init_msg_list IN VARCHAR2,
1927 p_dss_assignment IN dss_assignment_type,
1928 x_assignment_id OUT NOCOPY NUMBER,
1929 x_return_status OUT NOCOPY VARCHAR2,
1930 x_msg_count OUT NOCOPY NUMBER,
1931 x_msg_data OUT NOCOPY VARCHAR2
1932 )
1933
1934 IS
1935 row_id varchar2(64);
1936 BEGIN
1937
1938 -- standard start of API savepoint
1939 SAVEPOINT create_assignment ;
1940
1941 -- initialize message list if p_init_msg_list is set to TRUE.
1942 IF FND_API.to_Boolean(p_init_msg_list) THEN
1943 FND_MSG_PUB.initialize;
1944 END IF;
1945
1946 -- initialize API return status to success.
1947 x_return_status := FND_API.G_RET_STS_SUCCESS;
1948
1949 -- VALIDATION
1950 -- PASSED IN GROUP CODE SHOULD BE VALID
1951 IF HZ_DSS_VALIDATE_PKG.exist_in_dss_groups_b (p_dss_assignment.dss_group_code) = 'N'
1952 THEN
1953 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_CODE_INVALID');
1954 FND_MSG_PUB.ADD;
1955 RAISE FND_API.G_EXC_ERROR;
1956 END IF;
1957
1958 -- OWNER TABLE NAME VALIDATION
1959 IF HZ_DSS_VALIDATE_PKG.exist_in_dss_entities(p_dss_assignment.owner_table_name) = 'N'
1960 THEN
1961 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OWN_TABLE_NAME_INVALID');
1962 FND_MSG_PUB.ADD;
1963 RAISE FND_API.G_EXC_ERROR;
1964 END IF;
1965
1966 -- STATUS VALIDATION
1967
1968 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(p_dss_assignment.status, 'REGISTRY_STATUS')= 'N'
1969 THEN
1970 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_STATUS_VAL_INVALID');
1971 FND_MSG_PUB.ADD;
1972 RAISE FND_API.G_EXC_ERROR;
1973 END IF;
1974
1975
1976
1977 -- Call the low level table handler
1978 HZ_DSS_ASSIGNMENTS_PKG.Insert_Row (
1979 x_rowid => row_id,
1980 x_assignment_id => x_assignment_id,
1981 x_status => p_dss_assignment.status,
1982 x_owner_table_name => p_dss_assignment.owner_table_name,
1983 x_owner_table_id1 => p_dss_assignment.owner_table_id1,
1984 x_owner_table_id2 => p_dss_assignment.owner_table_id2,
1985 x_owner_table_id3 => p_dss_assignment.owner_table_id3,
1986 x_owner_table_id4 => p_dss_assignment.owner_table_id4,
1987 x_owner_table_id5 => p_dss_assignment.owner_table_id5,
1988 x_dss_group_code => p_dss_assignment.dss_group_code,
1989 x_object_version_number => 1
1990 );
1991
1992
1993 -- standard call to get message count and if count is 1, get message info.
1994 FND_MSG_PUB.Count_And_Get(
1995 p_encoded => FND_API.G_FALSE,
1996 p_count => x_msg_count,
1997 p_data => x_msg_data);
1998
1999 EXCEPTION
2000 WHEN FND_API.G_EXC_ERROR THEN
2001 ROLLBACK TO create_assignment ;
2002 x_return_status := FND_API.G_RET_STS_ERROR;
2003 FND_MSG_PUB.Count_And_Get(
2004 p_encoded => FND_API.G_FALSE,
2005 p_count => x_msg_count,
2006 p_data => x_msg_data);
2007
2008 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2009 ROLLBACK TO create_assignment ;
2010 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2011 FND_MSG_PUB.Count_And_Get(
2012 p_encoded => FND_API.G_FALSE,
2013 p_count => x_msg_count,
2014 p_data => x_msg_data);
2015
2016 WHEN OTHERS THEN
2017 ROLLBACK TO create_assignment ;
2018 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2019 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
2020 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2021 FND_MSG_PUB.ADD;
2022 FND_MSG_PUB.Count_And_Get(
2023 p_encoded => FND_API.G_FALSE,
2024 p_count => x_msg_count,
2025 p_data => x_msg_data);
2026 END create_assignment ;
2027
2028 /**
2029 * PROCEDURE delete_assignment
2030 *
2031 * DESCRIPTION
2032 * The delete_assignment procedure deletes a Data Sharing Group assignment (HZ_DSS_ASSIGNMENTS).
2033 *
2034 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2035 *
2036 *
2037 * ARGUMENTS
2038 *
2039 *
2040 * NOTES
2041 *
2042 * MODIFICATION HISTORY
2043 *
2044 * 08-15-2002 Colathur Vijayan ("VJN") o Created.
2045 *
2046 */
2047 PROCEDURE delete_assignment (
2048 p_init_msg_list IN VARCHAR2,
2049 p_assignment_id IN NUMBER,
2050 x_return_status OUT NOCOPY VARCHAR2,
2051 x_msg_count OUT NOCOPY NUMBER,
2052 x_msg_data OUT NOCOPY VARCHAR2
2053 )
2054 IS
2055 row_id varchar2(64);
2056 BEGIN
2057
2058 -- standard start of API savepoint
2059 SAVEPOINT delete_assignment ;
2060
2061 -- initialize message list if p_init_msg_list is set to TRUE.
2062 IF FND_API.to_Boolean(p_init_msg_list) THEN
2063 FND_MSG_PUB.initialize;
2064 END IF;
2065
2066 -- initialize API return status to success.
2067 x_return_status := FND_API.G_RET_STS_SUCCESS;
2068
2069 -- VALIDATION
2070 -- PASSED IN ASSIGNMENT ID SHOULD BE VALID
2071 IF HZ_DSS_VALIDATE_PKG.exist_in_dss_assignments(p_assignment_id) = 'N'
2072 THEN
2073 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_ASS_ID_INVALID');
2074 FND_MSG_PUB.ADD;
2075 RAISE FND_API.G_EXC_ERROR;
2076 END IF;
2077
2078 -- Call the low level table handler
2079 HZ_DSS_ASSIGNMENTS_PKG.Delete_Row (
2080 x_assignment_id => p_assignment_id
2081 );
2082
2083
2084 -- standard call to get message count and if count is 1, get message info.
2085 FND_MSG_PUB.Count_And_Get(
2086 p_encoded => FND_API.G_FALSE,
2087 p_count => x_msg_count,
2088 p_data => x_msg_data);
2089
2090 EXCEPTION
2091 WHEN FND_API.G_EXC_ERROR THEN
2092 ROLLBACK TO delete_assignment ;
2093 x_return_status := FND_API.G_RET_STS_ERROR;
2094 FND_MSG_PUB.Count_And_Get(
2095 p_encoded => FND_API.G_FALSE,
2096 p_count => x_msg_count,
2097 p_data => x_msg_data);
2098
2099 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2100 ROLLBACK TO delete_assignment ;
2101 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2102 FND_MSG_PUB.Count_And_Get(
2103 p_encoded => FND_API.G_FALSE,
2104 p_count => x_msg_count,
2105 p_data => x_msg_data);
2106
2107 WHEN OTHERS THEN
2108 ROLLBACK TO delete_assignment ;
2109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2110 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
2111 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2112 FND_MSG_PUB.ADD;
2113 FND_MSG_PUB.Count_And_Get(
2114 p_encoded => FND_API.G_FALSE,
2115 p_count => x_msg_count,
2116 p_data => x_msg_data);
2117 END delete_assignment ;
2118
2119 /**
2120 * PROCEDURE create_secured_entity
2121 *
2122 * DESCRIPTION
2123 * The create_secured_entity procedure creates a Secured Entity entry (HZ_DSS_SECURED_ENTITIES)
2124 * for a Data Sharing Group.
2125 *
2126 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2127 *
2128 *
2129 * ARGUMENTS
2130 *
2131 *
2132 * NOTES
2133 *
2134 * MODIFICATION HISTORY
2135 *
2136 * 08-15-2002 Colathur Vijayan ("VJN") o Created.
2137 *
2138 */
2139
2140 PROCEDURE create_secured_entity (
2141 p_init_msg_list IN VARCHAR2,
2142 p_dss_secured_entity IN dss_secured_entity_type,
2143 x_dss_instance_set_id OUT NOCOPY NUMBER,
2144 x_return_status OUT NOCOPY VARCHAR2,
2145 x_msg_count OUT NOCOPY NUMBER,
2146 x_msg_data OUT NOCOPY VARCHAR2
2147 )
2148
2149 IS
2150 row_id varchar2(64);
2151 l_predicate varchar2(2000);
2152 l_object_id number ;
2153 l_dss_instance_set_id number ;
2154 l_instance_set_id number ;
2155
2156 l_dss_ois_name varchar2(30);
2157 l_dup_ois_cnt number;
2158 BEGIN
2159
2160 -- standard start of API savepoint
2161 SAVEPOINT create_secured_entity ;
2162
2163 -- initialize message list if p_init_msg_list is set to TRUE.
2164 IF FND_API.to_Boolean(p_init_msg_list) THEN
2165 FND_MSG_PUB.initialize;
2166 END IF;
2167
2168 -- initialize API return status to success.
2169 x_return_status := FND_API.G_RET_STS_SUCCESS;
2170
2171 --validation of mandatory fields
2172 IF (p_dss_secured_entity.dss_group_code is null OR
2173 p_dss_secured_entity.dss_group_code = FND_API.G_MISS_CHAR) THEN
2174 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
2178 END IF;
2175 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'dss_group_code' );
2176 FND_MSG_PUB.ADD;
2177 RAISE FND_API.G_EXC_ERROR;
2179
2180 IF (p_dss_secured_entity.entity_id is null OR
2181 p_dss_secured_entity.entity_id = FND_API.G_MISS_NUM) THEN
2182 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
2183 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'entity_id' );
2184 FND_MSG_PUB.ADD;
2185 RAISE FND_API.G_EXC_ERROR;
2186 END IF;
2187
2188
2189 -- VALIDATION
2190 -- PASSED IN GROUP CODE SHOULD BE VALID
2191 IF HZ_DSS_VALIDATE_PKG.exist_in_dss_groups_b (p_dss_secured_entity.dss_group_code) = 'N'
2192 THEN
2193 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_CODE_INVALID');
2194 FND_MSG_PUB.ADD;
2195 RAISE FND_API.G_EXC_ERROR;
2196 END IF;
2197
2198 -- ENTITY ID VALIDATION
2199 IF HZ_DSS_VALIDATE_PKG.exist_in_dss_entities(p_dss_secured_entity.entity_id ) = 'N'
2200 THEN
2201 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_ENT_ID_INVALID');
2202 FND_MSG_PUB.ADD;
2203 RAISE FND_API.G_EXC_ERROR;
2204 END IF;
2205
2206 -- STATUS VALIDATION
2207 IF p_dss_secured_entity.status is not null then
2208 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(p_dss_secured_entity.status,
2209 'REGISTRY_STATUS')= 'N' THEN
2210 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_STATUS_VAL_INVALID');
2211 FND_MSG_PUB.ADD;
2212 RAISE FND_API.G_EXC_ERROR;
2213 END IF;
2214 END IF;
2215
2216
2217
2218 -- LOGIC FOR GENERATING OBJECT INSTANCE SETS CORRESPONDING TO THE SECURED ENTITY
2219 -- GOES HERE
2220
2221 -- GET THE PREDICATE FIRST
2222
2223
2224 HZ_DSS_UTIL_PUB.generate_predicate (
2225 p_dss_group_code => p_dss_secured_entity.dss_group_code,
2226 p_entity_id => p_dss_secured_entity.entity_id,
2227 x_predicate => l_predicate,
2228 x_return_status => x_return_status,
2229 x_msg_count => x_msg_count,
2230 x_msg_data => x_msg_data
2231 );
2232
2233
2234 IF x_return_status NOT IN ( FND_API.G_RET_STS_SUCCESS, FND_API.G_TRUE) THEN
2235 RAISE FND_API.G_EXC_ERROR;
2236 END IF;
2237
2238 -- GET A SEQUENCE GENERATED DSS_INSTANCE_SET_ID TO BE INSERTED INTO FND_GRANTS
2239 select fnd_object_instance_sets_s.nextval
2240 into l_dss_instance_set_id
2241 from sys.dual;
2242
2243 -- GET OBJECT ID TO BE INSERTED INTO FND_OBJECT_INSTANCE_SETS
2244
2245 -- If there is an object id (non null) corresponding to the given entity id
2246 -- in HZ_DSS_ENTITIIES, grab it
2247
2248 IF HZ_DSS_VALIDATE_PKG.is_an_obj_id_in_dss_entities(p_dss_secured_entity.entity_id) = 'Y'
2249 THEN
2250 l_object_id := HZ_DSS_VALIDATE_PKG.get_object_id_entities
2251 (p_dss_secured_entity.entity_id);
2252 -- Else get the object instance set id that corresponds to the entity id, go to fnd grants and get the
2253 -- object id
2254 ELSE
2255 l_instance_set_id := HZ_DSS_VALIDATE_PKG.get_instance_set_id_entities
2256 (p_dss_secured_entity.entity_id);
2257 l_object_id := HZ_DSS_VALIDATE_PKG.get_object_id_fnd_ins_sets
2258 (l_instance_set_id);
2259 END IF;
2260
2261
2262 -- CALL FND_OBJECT_INSTANCE_SETS_PKG INSERT ROW HANDLER TO INSERT
2263 -- ALL THE INFORMATION COLLECTED BEFORE
2264
2265 -- Construct an Object Instance Set Name
2266
2267 l_dss_ois_name := 'HZ_DSS_' || substrb(p_dss_secured_entity.dss_group_code,1,18) ||
2268 '_';
2269 select count(*) into l_dup_ois_cnt from fnd_object_instance_sets
2270 where instance_set_name like l_dss_ois_name || '%';
2271 l_dss_ois_name := l_dss_ois_name || to_char(l_dup_ois_cnt + 1);
2272
2273
2274 FND_OBJECT_INSTANCE_SETS_PKG.INSERT_ROW (
2275 x_rowid => row_id,
2276 x_instance_set_id => l_dss_instance_set_id,
2277 x_instance_set_name => l_dss_ois_name,
2278 x_object_id => l_object_id,
2279 x_predicate => l_predicate,
2280 x_display_name => l_dss_ois_name,
2281 x_description => l_dss_ois_name,
2282 x_creation_date => hz_utility_v2pub.creation_date ,
2283 x_created_by => hz_utility_v2pub.created_by ,
2284 x_last_update_date => hz_utility_v2pub.last_update_date,
2285 x_last_updated_by => hz_utility_v2pub.last_updated_by ,
2286 x_last_update_login => hz_utility_v2pub.last_update_login );
2287
2288 -- Bug#3710516 - update grants when the status of a secured entity has been
2289 -- changed.
2290 --
2291 -- Create grants in case there are already grants created for the
2292 -- group.
2293
2294 hz_dss_grants_pub.create_grant (
2295 p_dss_group_code => p_dss_secured_entity.dss_group_code,
2296 p_dss_instance_set_id => l_dss_instance_set_id,
2297 p_secured_entity_status => NVL(p_dss_secured_entity.status,'A'),
2298 x_return_status => x_return_status,
2299 x_msg_count => x_msg_count,
2300 x_msg_data => x_msg_data
2301 );
2302
2303 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2304 RAISE FND_API.G_EXC_ERROR;
2305 END IF;
2306
2307 -- Call the low level table handler
2308 HZ_DSS_SECURED_ENTITIES_PKG.Insert_Row (
2309 x_rowid => row_id ,
2310 x_dss_group_code => p_dss_secured_entity.dss_group_code,
2314 x_object_version_number => 1
2311 x_entity_id => p_dss_secured_entity.entity_id,
2312 x_status => nvl(p_dss_secured_entity.status,'A'),
2313 x_dss_instance_set_id => l_dss_instance_set_id,
2315 );
2316
2317 --Bug#2620405 Instance set ID is not getting returned
2318 x_dss_instance_set_id := l_dss_instance_set_id;
2319
2320 -- standard call to get message count and if count is 1, get message info.
2321 FND_MSG_PUB.Count_And_Get(
2322 p_encoded => FND_API.G_FALSE,
2323 p_count => x_msg_count,
2324 p_data => x_msg_data);
2325
2326 EXCEPTION
2327 WHEN FND_API.G_EXC_ERROR THEN
2328 ROLLBACK TO create_secured_entity ;
2329 x_return_status := FND_API.G_RET_STS_ERROR;
2330 FND_MSG_PUB.Count_And_Get(
2331 p_encoded => FND_API.G_FALSE,
2332 p_count => x_msg_count,
2333 p_data => x_msg_data);
2334
2335 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2336 ROLLBACK TO create_secured_entity ;
2337 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2338 FND_MSG_PUB.Count_And_Get(
2339 p_encoded => FND_API.G_FALSE,
2340 p_count => x_msg_count,
2341 p_data => x_msg_data);
2342
2343 WHEN OTHERS THEN
2344 ROLLBACK TO create_secured_entity ;
2345 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2346 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
2347 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2348 FND_MSG_PUB.ADD;
2349 FND_MSG_PUB.Count_And_Get(
2350 p_encoded => FND_API.G_FALSE,
2351 p_count => x_msg_count,
2352 p_data => x_msg_data);
2353 END create_secured_entity ;
2354
2355
2356 /**
2357 * PROCEDURE update_secured_entity
2358 *
2359 * DESCRIPTION
2360 * The update_secured_entity procedure updates a Secured Entity record.
2361 * Currently, the only data updateable is the STATUS column.
2362 *
2363 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2364 *
2365 *
2366 * ARGUMENTS
2367 *
2368 *
2369 * NOTES
2370 *
2371 * MODIFICATION HISTORY
2372 *
2373 * 08-13-2002 Colathur Vijayan ("VJN") o Created.
2374 *
2375 */
2376 PROCEDURE update_secured_entity (
2377 p_init_msg_list IN VARCHAR2,
2378 p_dss_secured_entity IN dss_secured_entity_type,
2379 x_object_version_number IN OUT NOCOPY NUMBER,
2380 x_return_status OUT NOCOPY VARCHAR2,
2381 x_msg_count OUT NOCOPY NUMBER,
2382 x_msg_data OUT NOCOPY VARCHAR2
2383 ) IS
2384
2385 l_object_version_number NUMBER;
2386 l_rowid ROWID;
2387 l_dss_instance_set_id NUMBER;
2388 l_status VARCHAR2(1);
2389
2390 BEGIN
2391
2392 -- initialize message list if p_init_msg_list is set to TRUE.
2393 IF FND_API.to_Boolean(p_init_msg_list) THEN
2394 FND_MSG_PUB.initialize;
2395 END IF;
2396
2397 -- initialize API return status to success.
2398 x_return_status := FND_API.G_RET_STS_SUCCESS;
2399
2400 -- standard start of API savepoint
2401 SAVEPOINT update_secured_entity ;
2402
2403 -- check whether record has been updated by another user. If not, lock it.
2404 BEGIN
2405 SELECT object_version_number, dss_instance_set_id, rowid, status
2406 INTO l_object_version_number, l_dss_instance_set_id , l_rowid, l_status
2407 FROM hz_dss_secured_entities
2408 WHERE dss_group_code = p_dss_secured_entity.dss_group_code
2409 AND entity_id = p_dss_secured_entity.entity_id
2410 FOR UPDATE NOWAIT;
2411 EXCEPTION
2412 WHEN NO_DATA_FOUND THEN
2413 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_SEC_ENT_NOT_FOUND');
2414 FND_MSG_PUB.ADD;
2415 RAISE FND_API.G_EXC_ERROR;
2416 END;
2417
2418 IF NOT ((x_object_version_number IS NULL AND
2419 l_object_version_number IS NULL) OR
2420 (x_object_version_number = l_object_version_number))
2421 THEN
2422 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
2423 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_DSS_SECURED_ENTITIES');
2424 FND_MSG_PUB.ADD;
2425 RAISE FND_API.G_EXC_ERROR;
2426 END IF;
2427
2428 x_object_version_number := nvl(l_object_version_number, 1) + 1;
2429
2430 -- VALIDATION
2431
2432 -- STATUS VALIDATION
2433
2434 IF p_dss_secured_entity.status IS NOT NULL THEN
2435 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups (
2436 p_dss_secured_entity.status,
2437 'REGISTRY_STATUS')= 'N'
2438 THEN
2439 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_STATUS_VAL_INVALID');
2440 FND_MSG_PUB.ADD;
2441 RAISE FND_API.G_EXC_ERROR;
2442 END IF;
2443 END IF;
2444
2445 -- Bug#3710516 - update grants when the status of a secured entity has been
2446 -- changed.
2447 --
2448 -- reset grant end date if status has been switched from I to A.
2449 -- end-dated fnd grants if status has been switched from A to I.
2450 --
2451 -- status is null means no change
2452 -- status = G_MISS case has been caught by the lookup validation
2453 --
2454 IF p_dss_secured_entity.status IS NOT NULL AND
2455 ((p_dss_secured_entity.status = 'A' AND l_status = 'D') OR
2456 (p_dss_secured_entity.status = 'D' AND l_status = 'A'))
2457 THEN
2458
2459 hz_dss_grants_pub.update_grant (
2460 p_dss_group_code => p_dss_secured_entity.dss_group_code,
2461 p_dss_instance_set_id => l_dss_instance_set_id,
2462 p_secured_entity_status => p_dss_secured_entity.status,
2463 x_return_status => x_return_status,
2464 x_msg_count => x_msg_count,
2465 x_msg_data => x_msg_data
2466 );
2467
2468 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2469 RAISE FND_API.G_EXC_ERROR;
2470 END IF;
2471
2472 END IF;
2473
2474 -- Call the low level table handler
2475 HZ_DSS_SECURED_ENTITIES_PKG.Update_Row (
2476 x_rowid => l_rowid,
2477 x_status => p_dss_secured_entity.status,
2478 x_dss_instance_set_id => l_dss_instance_set_id ,
2479 x_object_version_number => x_object_version_number
2480 ) ;
2481
2482 -- standard call to get message count and if count is 1, get message info.
2483 FND_MSG_PUB.Count_And_Get(
2484 p_encoded => FND_API.G_FALSE,
2485 p_count => x_msg_count,
2486 p_data => x_msg_data);
2487
2488 EXCEPTION
2489 WHEN FND_API.G_EXC_ERROR THEN
2490 ROLLBACK TO update_secured_entity ;
2491 x_return_status := FND_API.G_RET_STS_ERROR;
2492
2493 FND_MSG_PUB.Count_And_Get(
2494 p_encoded => FND_API.G_FALSE,
2495 p_count => x_msg_count,
2496 p_data => x_msg_data);
2497
2498 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2499 ROLLBACK TO update_secured_entity ;
2500
2501 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2502 FND_MSG_PUB.Count_And_Get(
2503 p_encoded => FND_API.G_FALSE,
2504 p_count => x_msg_count,
2505 p_data => x_msg_data);
2506
2507 WHEN OTHERS THEN
2508 ROLLBACK TO update_secured_entity ;
2509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2510
2511 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
2512 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2513 FND_MSG_PUB.ADD;
2514 FND_MSG_PUB.Count_And_Get(
2515 p_encoded => FND_API.G_FALSE,
2516 p_count => x_msg_count,
2517 p_data => x_msg_data);
2518 END update_secured_entity ;
2519
2520 END HZ_DSS_GROUPS_PUB ;