[Home] [Help]
PACKAGE BODY: APPS.HZ_DSS_SETUP_PUB
Source
1 PACKAGE BODY HZ_DSS_SETUP_PUB AS
2 /*$Header: ARHPDSTB.pls 115.6 2003/01/08 07:39:03 jypandey noship $ */
3
4 ----------------------------------
5 -- declaration of global variables
6 ----------------------------------
7
8 -------------------------------------------------
9 -- private procedures and functions
10 -------------------------------------------------
11
12
13 --------------------------------------
14 -- public procedures and functions
15 --------------------------------------
16
17 /**
18 * PROCEDURE create_entity_profile
19 *
20 * DESCRIPTION
21 * Creates entity profile.
22 *
23 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
24 *
25 *
26 * ARGUMENTS
27 *
28 *
29 * NOTES
30 *
31 * MODIFICATION HISTORY
32 *
33 * 08-06 -2002 Colathur Vijayan ("VJN") o Created.
34 *
35 */
36
37 PROCEDURE create_entity_profile(
38 p_init_msg_list IN VARCHAR2,
39 p_dss_entity_profile IN DSS_ENTITY_PROFILE_TYPE,
40 x_entity_id OUT NOCOPY NUMBER,
41 x_return_status OUT NOCOPY VARCHAR2,
42 x_msg_count OUT NOCOPY NUMBER,
43 x_msg_data OUT NOCOPY VARCHAR2
44 ) IS
45 row_id varchar2(64);
46 BEGIN
47
48 -- standard start of API savepoint
49 SAVEPOINT create_entity_profile;
50
51 -- initialize message list if p_init_msg_list is set to TRUE.
52 IF FND_API.to_Boolean(p_init_msg_list) THEN
53 FND_MSG_PUB.initialize;
54 END IF;
55
56 -- initialize API return status to success.
57 x_return_status := FND_API.G_RET_STS_SUCCESS;
58
59 -- VALIDATION
60 IF p_dss_entity_profile.object_id IS NOT NULL AND p_dss_entity_profile.instance_set_id IS NOT NULL
61 THEN
62 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OB_AND_INS_NON_NULL');
63 FND_MSG_PUB.ADD;
64 RAISE FND_API.G_EXC_ERROR;
65 ELSIF p_dss_entity_profile.object_id IS NULL AND p_dss_entity_profile.instance_set_id IS NULL
66 THEN
67 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OB_AND_INS_BOTH_NULL');
68 FND_MSG_PUB.ADD;
69 RAISE FND_API.G_EXC_ERROR;
70 END IF;
71
72 IF p_dss_entity_profile.object_id IS NOT NULL
73 THEN
74 IF HZ_DSS_VALIDATE_PKG.exist_fnd_object_id(p_dss_entity_profile.object_id)= 'N'
75 THEN
76 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OBJ_ID_INVALID');
77 FND_MSG_PUB.ADD;
78 RAISE FND_API.G_EXC_ERROR;
79 END IF;
80 END IF;
81
82 IF p_dss_entity_profile.instance_set_id IS NOT NULL
83 THEN
84 IF HZ_DSS_VALIDATE_PKG.exist_fnd_instance_set_id(p_dss_entity_profile.instance_set_id) = 'N'
85 THEN
86 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_INS_SET_ID_INVALID');
87 FND_MSG_PUB.ADD;
88 RAISE FND_API.G_EXC_ERROR;
89 END IF;
90 END IF;
91
92 IF p_dss_entity_profile.parent_entity_id IS NOT NULL
93 THEN
94 IF HZ_DSS_VALIDATE_PKG.exist_entity_id(p_dss_entity_profile.parent_entity_id) = 'N'
95 THEN
96 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_PAR_ENT_ID_INVALID');
97 FND_MSG_PUB.ADD;
98 RAISE FND_API.G_EXC_ERROR;
99 END IF;
100 END IF;
101
102 -- STATUS VALIDATION
103 IF p_dss_entity_profile.status is not null then
104 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(
105 p_dss_entity_profile.status, 'REGISTRY_STATUS')= 'N'
106 THEN
107 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_STATUS_VAL_INVALID');
108 FND_MSG_PUB.ADD;
109 RAISE FND_API.G_EXC_ERROR;
110 END IF;
111 END IF;
112
113 ---validate group_assignment_level have a valid value
114 IF (p_dss_entity_profile.group_assignment_level is null OR
115 p_dss_entity_profile.group_assignment_level = FND_API.G_MISS_CHAR) THEN
116 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
117 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'group_assignment_level' );
118 FND_MSG_PUB.ADD;
119 RAISE FND_API.G_EXC_ERROR;
120 ELSE
121
122 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(p_dss_entity_profile.
123 group_assignment_level, 'HZ_DSS_GROUP_ASSIGN_LEVELS') = 'N'
124 THEN
125 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_ASS_LEVEL_INVALID');
126 FND_MSG_PUB.ADD;
127 RAISE FND_API.G_EXC_ERROR;
128 END IF;
129 END IF;
130
131 -- Call the low level table handler default status to A
132 HZ_DSS_ENTITIES_PKG.Insert_Row (
133 x_rowid => row_id,
134 x_entity_id => x_entity_id,
135 x_status => nvl(p_dss_entity_profile.status,'A') ,
136 x_object_id => p_dss_entity_profile.object_id,
137 x_instance_set_id => p_dss_entity_profile.instance_set_id,
138 x_parent_entity_id => p_dss_entity_profile.parent_entity_id,
139 x_parent_fk_column1 => p_dss_entity_profile.parent_fk_column1,
140 x_parent_fk_column2 => p_dss_entity_profile.parent_fk_column2,
141 x_parent_fk_column3 => p_dss_entity_profile.parent_fk_column3,
142 x_parent_fk_column4 => p_dss_entity_profile.parent_fk_column4,
143 x_parent_fk_column5 => p_dss_entity_profile.parent_fk_column5,
144 x_group_assignment_level=> p_dss_entity_profile.group_assignment_level ,
145 x_object_version_number => 1
146 );
147
148
149 -- standard call to get message count and if count is 1, get message info.
150 FND_MSG_PUB.Count_And_Get(
151 p_encoded => FND_API.G_FALSE,
152 p_count => x_msg_count,
153 p_data => x_msg_data);
154
155 EXCEPTION
156 WHEN FND_API.G_EXC_ERROR THEN
157 ROLLBACK TO create_entity_profile;
158 x_return_status := FND_API.G_RET_STS_ERROR;
159 FND_MSG_PUB.Count_And_Get(
160 p_encoded => FND_API.G_FALSE,
161 p_count => x_msg_count,
162 p_data => x_msg_data);
163
164 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
165 ROLLBACK TO create_entity_profile;
166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
167 FND_MSG_PUB.Count_And_Get(
168 p_encoded => FND_API.G_FALSE,
169 p_count => x_msg_count,
170 p_data => x_msg_data);
171
172 WHEN OTHERS THEN
173 ROLLBACK TO create_entity_profile;
174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
175 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
176 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
177 FND_MSG_PUB.ADD;
178 FND_MSG_PUB.Count_And_Get(
179 p_encoded => FND_API.G_FALSE,
180 p_count => x_msg_count,
181 p_data => x_msg_data);
182 END create_entity_profile ;
183
184
185
186
187
188 /**
189 * PROCEDURE update_entity_profile
190 *
191 * DESCRIPTION
192 * Updates entity profile.
193 *
194 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
195 *
196 *
197 * ARGUMENTS
198 *
199 *
200 * NOTES
201 *
202 * MODIFICATION HISTORY
203 *
204 * 08-06 -2002 Colathur Vijayan ("VJN") o Created.
205 *
206 */
207
208 PROCEDURE update_entity_profile(
209 p_init_msg_list IN VARCHAR2,
210 p_dss_entity_profile IN DSS_ENTITY_PROFILE_TYPE,
211 x_object_version_number IN OUT NOCOPY NUMBER,
212 x_return_status OUT NOCOPY VARCHAR2,
213 x_msg_count OUT NOCOPY NUMBER,
214 x_msg_data OUT NOCOPY VARCHAR2
215 ) IS
216 l_object_version_number NUMBER;
217 l_rowid ROWID;
218 l_object_id HZ_DSS_ENTITIES.OBJECT_ID%TYPE;
219 l_instance_set_id HZ_DSS_ENTITIES.INSTANCE_SET_ID%TYPE;
220
221 BEGIN
222
223 -- standard start of API savepoint
224 SAVEPOINT update_entity_profile;
225
226 -- initialize message list if p_init_msg_list is set to TRUE.
227 IF FND_API.to_Boolean(p_init_msg_list) THEN
228 FND_MSG_PUB.initialize;
229 END IF;
230
231 -- initialize API return status to success.
232 x_return_status := FND_API.G_RET_STS_SUCCESS;
233
234 -- check whether record has been updated by another user. If not, lock it.
235
236 BEGIN
237 SELECT object_version_number, rowid , object_id, instance_set_id
238 INTO l_object_version_number, l_rowid , l_object_id , l_instance_set_id
239 FROM HZ_DSS_ENTITIES
240 WHERE entity_id = p_dss_entity_profile.entity_id
241 FOR UPDATE NOWAIT;
242 EXCEPTION
243 WHEN NO_DATA_FOUND THEN
244 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_ENT_ID_INVALID');
245 FND_MSG_PUB.ADD;
246 RAISE FND_API.G_EXC_ERROR;
247 END;
248
249 IF NOT ((x_object_version_number is null and l_object_version_number is null)
250 OR (x_object_version_number = l_object_version_number))
251 THEN
252 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
253 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_DSS_ENTITIES');
254 FND_MSG_PUB.ADD;
255 RAISE FND_API.G_EXC_ERROR;
256 END IF;
257
258 x_object_version_number := nvl(l_object_version_number, 1) + 1;
259
260
261 -- VALIDATION
262
263 -- Bug: 2620112 VALIDATION parent Entity ID is valid
264 IF p_dss_entity_profile.parent_entity_id IS NOT NULL
265 THEN
266 IF HZ_DSS_VALIDATE_PKG.exist_entity_id(
267 p_dss_entity_profile.parent_entity_id) = 'N'
268 THEN
269 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_PAR_ENT_ID_INVALID');
270 FND_MSG_PUB.ADD;
271 RAISE FND_API.G_EXC_ERROR;
272 END IF;
273 END IF;
274
275 -- Bug: 2620112 VALIDATION group_assignment_level is valid
276 if p_dss_entity_profile.group_assignment_level = FND_API.G_MISS_CHAR then
277 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_TO_NULL');
278 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'GROUP_ASSIGNMENT_LEVEL' );
279 FND_MSG_PUB.ADD;
280 RAISE FND_API.G_EXC_ERROR;
281 end if;
282
283 if p_dss_entity_profile.group_assignment_level is not null then
284 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(
285 p_dss_entity_profile.group_assignment_level,
286 'HZ_DSS_GROUP_ASSIGN_LEVELS') = 'N'
287 THEN
288 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_ASS_LEVEL_INVALID');
289 FND_MSG_PUB.ADD;
290 RAISE FND_API.G_EXC_ERROR;
291 END IF;
292 end if;
293
294 --Bug: 2620112 Validation that object_id or instance_set_id are
295 --NON updateable
296 IF ( p_dss_entity_profile.object_id <> FND_API.G_MISS_NUM OR
297 l_object_id IS NOT NULL )
298 AND ( l_object_id IS NULL OR
299 p_dss_entity_profile.object_id <> l_object_id ) THEN
300 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
301 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'OBJECT_ID' );
302 FND_MSG_PUB.ADD;
303 RAISE FND_API.G_EXC_ERROR;
304 END IF;
305
306 IF ( p_dss_entity_profile.instance_set_id <> FND_API.G_MISS_NUM OR
307 l_instance_set_id IS NOT NULL )
308 AND ( l_instance_set_id IS NULL OR
309 p_dss_entity_profile.instance_set_id <> l_instance_set_id ) THEN
310 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
311 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'INSTANCE_SET_ID' );
312 FND_MSG_PUB.ADD;
313 RAISE FND_API.G_EXC_ERROR;
314 END IF;
315
316 -- STATUS VALIDATION only if not null
317 IF p_dss_entity_profile.status is not null then
318 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(
319 p_dss_entity_profile.status, 'REGISTRY_STATUS')= 'N'
320 THEN
321 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_STATUS_VAL_INVALID');
322 FND_MSG_PUB.ADD;
323 RAISE FND_API.G_EXC_ERROR;
324 END IF;
325 END IF;
326
327
328 -- Call the low level table handler
329 HZ_DSS_ENTITIES_PKG.Update_Row (
330 x_rowid => l_rowid,
331 x_status => p_dss_entity_profile.status,
332 x_object_id => p_dss_entity_profile.object_id,
333 x_instance_set_id => p_dss_entity_profile.instance_set_id,
334 x_parent_entity_id => p_dss_entity_profile.parent_entity_id,
335 x_parent_fk_column1 => p_dss_entity_profile.parent_fk_column1,
336 x_parent_fk_column2 => p_dss_entity_profile.parent_fk_column2,
337 x_parent_fk_column3 => p_dss_entity_profile.parent_fk_column3,
338 x_parent_fk_column4 => p_dss_entity_profile.parent_fk_column4,
339 x_parent_fk_column5 => p_dss_entity_profile.parent_fk_column5,
340 x_group_assignment_level=> p_dss_entity_profile.group_assignment_level ,
341 x_object_version_number => x_object_version_number );
342
343
344 -- standard call to get message count and if count is 1, get message info.
345 FND_MSG_PUB.Count_And_Get(
346 p_encoded => FND_API.G_FALSE,
347 p_count => x_msg_count,
348 p_data => x_msg_data);
349
350 EXCEPTION
351 WHEN FND_API.G_EXC_ERROR THEN
352 ROLLBACK TO update_entity_profile;
353 x_return_status := FND_API.G_RET_STS_ERROR;
354 FND_MSG_PUB.Count_And_Get(
355 p_encoded => FND_API.G_FALSE,
356 p_count => x_msg_count,
357 p_data => x_msg_data);
358
359 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
360 ROLLBACK TO update_entity_profile;
361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
362 FND_MSG_PUB.Count_And_Get(
363 p_encoded => FND_API.G_FALSE,
364 p_count => x_msg_count,
365 p_data => x_msg_data);
366
367 WHEN OTHERS THEN
368 ROLLBACK TO update_entity_profile;
369 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
370 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
371 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
372 FND_MSG_PUB.ADD;
373 FND_MSG_PUB.Count_And_Get(
374 p_encoded => FND_API.G_FALSE,
375 p_count => x_msg_count,
376 p_data => x_msg_data);
377 END update_entity_profile ;
378
379
380
381 --------------------------------------
382 -- create_scheme_function
383 --------------------------------------
384
385 /**
386 * PROCEDURE create_scheme_function
387 *
388 * DESCRIPTION
389 * Creates a Function association for a particular Security Scheme.
390 *
391 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
392 *
393 *
394 * ARGUMENTS
395 *
396 *
397 * NOTES
398 *
399 * MODIFICATION HISTORY
400 *
401 * 08-06 -2002 Colathur Vijayan ("VJN") o Created.
402 *
403 */
404
405 PROCEDURE create_scheme_function (
406 p_init_msg_list IN VARCHAR2,
407 p_dss_scheme_function IN dss_scheme_function_type,
408 x_return_status OUT NOCOPY VARCHAR2,
409 x_msg_count OUT NOCOPY NUMBER,
410 x_msg_data OUT NOCOPY VARCHAR2
411 )
412 IS
413 row_id varchar2(64);
414 l_duplicate_count NUMBER := 0;
415 BEGIN
416
417 -- standard start of API savepoint
418 SAVEPOINT create_scheme_function ;
419
420 -- initialize message list if p_init_msg_list is set to TRUE.
421 IF FND_API.to_Boolean(p_init_msg_list) THEN
422 FND_MSG_PUB.initialize;
423 END IF;
424
425 -- initialize API return status to success.
426 x_return_status := FND_API.G_RET_STS_SUCCESS;
427
428 -- VALIDATION
429
430 --Bug 2645639 check if there already is a function defined
431 --for combination of security_scheme_code and data operation
432
433 select count(*) into l_duplicate_count
434 from HZ_DSS_SCHEME_FUNCTIONS
435 where security_scheme_code = p_dss_scheme_function.security_scheme_code
436 and data_operation_code = p_dss_scheme_function.data_operation_code;
437
438 IF l_duplicate_count >= 1 then
439 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_DUP_SCHEME_FUNCTION');
440 FND_MESSAGE.SET_TOKEN('SCHEME' ,p_dss_scheme_function.security_scheme_code);
441 FND_MESSAGE.SET_TOKEN('OPERATION' ,p_dss_scheme_function.data_operation_code);
442 FND_MSG_PUB.ADD;
443 RAISE FND_API.G_EXC_ERROR;
444 END IF;
445
446 ---Validate that security_scheme code is not null and is valid
447 IF (p_dss_scheme_function.security_scheme_code is null OR
448 p_dss_scheme_function.security_scheme_code = FND_API.G_MISS_CHAR) THEN
449 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
450 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'security_scheme_code' );
451 FND_MSG_PUB.ADD;
452 RAISE FND_API.G_EXC_ERROR;
453 ELSE
454
455 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(p_dss_scheme_function.
456 security_scheme_code,'HZ_SECURITY_SCHEMES')= 'N' THEN
457 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_SEC_SCH_CODE_INVALID');
458 FND_MSG_PUB.ADD;
459 RAISE FND_API.G_EXC_ERROR;
460 END IF;
461 END IF;
462
463
464 ---Validate that data_operation_code is not null and is valid
465 IF (p_dss_scheme_function.data_operation_code is null OR
466 p_dss_scheme_function.data_operation_code = FND_API.G_MISS_CHAR) THEN
467 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
468 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'data_operation_code' );
469 FND_MSG_PUB.ADD;
470 RAISE FND_API.G_EXC_ERROR;
471 ELSE
472 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(p_dss_scheme_function.
473 data_operation_code, 'HZ_DATA_OPERATIONS')= 'N' THEN
474 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_DAT_OP_CODE_INVALID');
475 FND_MSG_PUB.ADD;
476 RAISE FND_API.G_EXC_ERROR;
477 END IF;
478 END IF;
479
480 ---Validate that function_id is not null and is valid
481 IF (p_dss_scheme_function.function_id is null OR
482 p_dss_scheme_function.function_id = FND_API.G_MISS_NUM) THEN
483 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
484 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'function_id' );
485 FND_MSG_PUB.ADD;
486 RAISE FND_API.G_EXC_ERROR;
487 ELSE
488 IF HZ_DSS_VALIDATE_PKG.exist_function_id(p_dss_scheme_function.
489 function_id)= 'N' THEN
490 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_FUN_ID_INVALID');
491 FND_MSG_PUB.ADD;
492 RAISE FND_API.G_EXC_ERROR;
493 END IF;
494 END IF;
495
496 -- STATUS VALIDATION
497 IF p_dss_scheme_function.status is not null then
498 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(p_dss_scheme_function.status,
499 'REGISTRY_STATUS')= 'N' THEN
500 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_STATUS_VAL_INVALID');
501 FND_MSG_PUB.ADD;
502 RAISE FND_API.G_EXC_ERROR;
503 END IF;
504 END IF;
505
506
507 -- Call the low level table handler
508 --default status to A
509 HZ_DSS_SCHEME_FUNCTIONS_PKG.Insert_Row (
510 x_rowid => row_id,
511 x_security_scheme_code => p_dss_scheme_function.security_scheme_code,
512 x_data_operation_code => p_dss_scheme_function.data_operation_code,
513 x_function_id => p_dss_scheme_function.function_id,
514 x_status => nvl(p_dss_scheme_function.status, 'A'),
515 x_object_version_number => 1 );
516
517
518 -- standard call to get message count and if count is 1, get message info.
519 FND_MSG_PUB.Count_And_Get(
520 p_encoded => FND_API.G_FALSE,
521 p_count => x_msg_count,
522 p_data => x_msg_data);
523
524 EXCEPTION
525 WHEN FND_API.G_EXC_ERROR THEN
526 ROLLBACK TO create_scheme_function ;
527 x_return_status := FND_API.G_RET_STS_ERROR;
528 FND_MSG_PUB.Count_And_Get(
529 p_encoded => FND_API.G_FALSE,
530 p_count => x_msg_count,
531 p_data => x_msg_data);
532
533 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
534 ROLLBACK TO create_scheme_function ;
535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
536 FND_MSG_PUB.Count_And_Get(
537 p_encoded => FND_API.G_FALSE,
538 p_count => x_msg_count,
539 p_data => x_msg_data);
540
541 WHEN OTHERS THEN
542 ROLLBACK TO create_scheme_function ;
543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
544 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
545 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
546 FND_MSG_PUB.ADD;
547 FND_MSG_PUB.Count_And_Get(
548 p_encoded => FND_API.G_FALSE,
549 p_count => x_msg_count,
550 p_data => x_msg_data);
551 END create_scheme_function ;
552
553
554
555
556 /**
557 * PROCEDURE update_scheme_function
558 *
559 * DESCRIPTION
560 * Updates Security Scheme.
561 *
562 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
563 *
564 *
565 * ARGUMENTS
566 *
567 *
568 * NOTES
569 *
570 * MODIFICATION HISTORY
571 *
572 * 08-06 -2002 Colathur Vijayan ("VJN") o Created.
573 *
574 */
575 PROCEDURE update_scheme_function (
576 p_init_msg_list IN VARCHAR2,
577 p_dss_scheme_function IN dss_scheme_function_type,
578 x_object_version_number IN OUT NOCOPY NUMBER,
579 x_return_status OUT NOCOPY VARCHAR2,
580 x_msg_count OUT NOCOPY NUMBER,
581 x_msg_data OUT NOCOPY VARCHAR2
582 )
583 IS
584 l_object_version_number NUMBER;
585 l_rowid ROWID;
586 BEGIN
587
588 -- standard start of API savepoint
589 SAVEPOINT update_scheme_function ;
590
591 -- initialize message list if p_init_msg_list is set to TRUE.
592 IF FND_API.to_Boolean(p_init_msg_list) THEN
593 FND_MSG_PUB.initialize;
594 END IF;
595
596 -- initialize API return status to success.
597 x_return_status := FND_API.G_RET_STS_SUCCESS;
598
599
600 -- check whether record has been updated by another user. If not, lock it.
601
602 BEGIN
603 SELECT object_version_number, rowid
604 INTO l_object_version_number, l_rowid
605 FROM HZ_DSS_SCHEME_FUNCTIONS
606 WHERE security_scheme_code = p_dss_scheme_function.security_scheme_code and
607 data_operation_code = p_dss_scheme_function.data_operation_code and
608 function_id = p_dss_scheme_function.function_id
609 FOR UPDATE NOWAIT;
610 EXCEPTION
611 WHEN NO_DATA_FOUND THEN
612 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_SCHEME_NOT_FOUND');
613 FND_MSG_PUB.ADD;
614 RAISE FND_API.G_EXC_ERROR;
615 END;
616
617 IF NOT ((x_object_version_number is null and l_object_version_number is null)
618 OR (x_object_version_number = l_object_version_number))
619 THEN
620 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
621 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_DSS_SCHEME_FUNCTIONS');
622 FND_MSG_PUB.ADD;
623 RAISE FND_API.G_EXC_ERROR;
624 END IF;
625
626 x_object_version_number := nvl(l_object_version_number, 1) + 1;
627
628
629 -- STATUS VALIDATION
630 IF p_dss_scheme_function.status is not null then
631 IF HZ_DSS_VALIDATE_PKG.exist_in_ar_lookups(
632 p_dss_scheme_function.status, 'REGISTRY_STATUS')= 'N'
633 THEN
634 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_STATUS_VAL_INVALID');
635 FND_MSG_PUB.ADD;
636 RAISE FND_API.G_EXC_ERROR;
637 END IF;
638 END IF;
639
640
641 -- Call the low level table handler
642 HZ_DSS_SCHEME_FUNCTIONS_PKG.Update_Row (
643 x_rowid => l_rowid,
644 x_status => p_dss_scheme_function.status,
645 x_object_version_number => x_object_version_number
646 ) ;
647
648
649 -- standard call to get message count and if count is 1, get message info.
650 FND_MSG_PUB.Count_And_Get(
651 p_encoded => FND_API.G_FALSE,
652 p_count => x_msg_count,
653 p_data => x_msg_data);
654
655 EXCEPTION
656 WHEN FND_API.G_EXC_ERROR THEN
657 ROLLBACK TO update_scheme_function ;
658 x_return_status := FND_API.G_RET_STS_ERROR;
659 FND_MSG_PUB.Count_And_Get(
660 p_encoded => FND_API.G_FALSE,
661 p_count => x_msg_count,
662 p_data => x_msg_data);
663
664 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
665 ROLLBACK TO update_scheme_function ;
666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
667 FND_MSG_PUB.Count_And_Get(
668 p_encoded => FND_API.G_FALSE,
669 p_count => x_msg_count,
670 p_data => x_msg_data);
671
672 WHEN OTHERS THEN
673 ROLLBACK TO update_scheme_function ;
674 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
675 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
676 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
677 FND_MSG_PUB.ADD;
678 FND_MSG_PUB.Count_And_Get(
679 p_encoded => FND_API.G_FALSE,
680 p_count => x_msg_count,
681 p_data => x_msg_data);
682 END update_scheme_function ;
683
684
685
686 END HZ_DSS_SETUP_PUB;