1 PACKAGE BODY HZ_DSS_VALIDATE_PKG AS
2 /* $Header: ARHPDSVB.pls 120.2 2005/10/30 04:21:59 appldev noship $ */
3
4
5 -------------------------------------------------
6 -- public procedures and functions
7 -------------------------------------------------
8
9 --------------------------------------------
10 -- return_no_of_dss_groups
11 --------------------------------------------
12
13 FUNCTION return_no_of_dss_groups
14 -- Return number of rows in hz_dss_groups
15 RETURN NUMBER
16 IS
17 CURSOR c0
18 IS
19 SELECT count (*)
20 FROM hz_dss_groups_b ;
21 l_yn number ;
22 result VARCHAR2(1);
23 BEGIN
24 OPEN c0;
25 FETCH c0 INTO l_yn;
26 CLOSE c0;
27 RETURN l_yn ;
28 END return_no_of_dss_groups ;
29
30 --------------------------------------------
31 -- get_object_id_entities
32 --------------------------------------------
33 FUNCTION get_object_id_entities
34 -- Return object_id from hz_dss_entities, based on the given entity id
35 (p_entity_id NUMBER)
36 RETURN NUMBER
37 IS
38 CURSOR c0
39 IS
40 SELECT object_id
41 FROM hz_dss_entities
42 WHERE entity_id = p_entity_id;
43 l_yn number ;
44 result VARCHAR2(1);
45 BEGIN
46 OPEN c0;
47 FETCH c0 INTO l_yn;
48 CLOSE c0;
49 RETURN l_yn ;
50 END get_object_id_entities ;
51
52
53 --------------------------------------------
54 -- get_instance_set_id_entities
55 --------------------------------------------
56 FUNCTION get_instance_set_id_entities
60 IS
57 -- Return instance_set_id from hz_dss_entities, based on the given entity id
58 (p_entity_id NUMBER)
59 RETURN NUMBER
61 CURSOR c0
62 IS
63 SELECT instance_set_id
64 FROM hz_dss_entities
65 WHERE entity_id = p_entity_id;
66 l_yn number ;
67 result VARCHAR2(1);
68 BEGIN
69 OPEN c0;
70 FETCH c0 INTO l_yn;
71 CLOSE c0;
72 RETURN l_yn ;
73 END get_instance_set_id_entities ;
74
75
76
77 --------------------------------------------
78 -- get_object_id_fnd_ins_sets
79 --------------------------------------------
80 FUNCTION get_object_id_fnd_ins_sets
81 -- Return object_id from fnd_grants based on the passed in instance_set_id
82 (p_instance_set_id NUMBER)
83 RETURN NUMBER
84 IS
85 CURSOR c0
86 IS
87 SELECT object_id
88 FROM fnd_object_instance_sets
89 WHERE instance_set_id = p_instance_set_id ;
90 l_yn number ;
91 result VARCHAR2(1);
92 BEGIN
93 OPEN c0;
94 FETCH c0 INTO l_yn;
95 CLOSE c0;
96 RETURN l_yn ;
97 END get_object_id_fnd_ins_sets ;
98
99
100 --------------------------------------------
101 -- exist_in_dss_groups
102 --------------------------------------------
103
104 FUNCTION exist_in_dss_groups_b
105 -- Return Y if the group code exists in hz_dss_groups
106 -- N otherwise
107 (p_dss_group_code VARCHAR2 )
108 RETURN VARCHAR2
109 IS
110 CURSOR c0
111 IS
112 SELECT 'Y'
113 FROM hz_dss_groups_b
114 WHERE dss_group_code = p_dss_group_code;
115 l_yn VARCHAR2(1);
116 result VARCHAR2(1);
117 BEGIN
118 OPEN c0;
119 FETCH c0 INTO l_yn;
120 IF c0%NOTFOUND THEN
121 result := 'N';
122 ELSE
123 result := 'Y';
124 END IF;
125 CLOSE c0;
126 RETURN result;
127 END exist_in_dss_groups_b ;
128
129 --------------------------------------------
130 -- exist_in_dss_groups_vl
131 --------------------------------------------
132
133 FUNCTION exist_in_dss_groups_vl
134 -- Return Y if the group name exists in hz_dss_groups_vl
135 -- N otherwise
136 (p_dss_group_name VARCHAR2 )
137 RETURN VARCHAR2
138 IS
139 CURSOR c0
140 IS
141 SELECT 'Y'
142 FROM hz_dss_groups_vl
143 WHERE dss_group_name = p_dss_group_name;
144 l_yn VARCHAR2(1);
145 result VARCHAR2(1);
146 BEGIN
147 OPEN c0;
148 FETCH c0 INTO l_yn;
149 IF c0%NOTFOUND THEN
150 result := 'N';
151 ELSE
152 result := 'Y';
153 END IF;
154 CLOSE c0;
155 RETURN result;
156 END exist_in_dss_groups_vl ;
157
158
159 FUNCTION exist_in_dss_groups_vl
160 -- Return Y if the object name exists in hz_dss_groups_vl, for a row whose
161 -- primary key <> passed in primary key value
162 -- N otherwise
163 (p_dss_group_name VARCHAR2, p_dss_group_code VARCHAR2 )
164 RETURN VARCHAR2
165 IS
166 CURSOR c0
167 IS
168 SELECT 'Y'
169 FROM hz_dss_groups_vl
170 WHERE dss_group_name = p_dss_group_name
171 and dss_group_code <> p_dss_group_code ;
172 l_yn VARCHAR2(1);
173 result VARCHAR2(1);
174 BEGIN
175 OPEN c0;
176 FETCH c0 INTO l_yn;
177 IF c0%NOTFOUND THEN
178 result := 'N';
179 ELSE
180 result := 'Y';
181 END IF;
182 CLOSE c0;
183 RETURN result;
184 END exist_in_dss_groups_vl ;
185
186
187 --------------------------------------------
188 -- exist_in_hz_class_categories
189 --------------------------------------------
190
191 FUNCTION exist_in_hz_class_categories
192 -- Return Y if the class code exists in hz_class_categories
193 -- N otherwise
194 (p_class_category VARCHAR2 )
195 RETURN VARCHAR2
196 IS
197 CURSOR c0
198 IS
199 SELECT 'Y'
200 FROM hz_class_categories
201 WHERE class_category = p_class_category ;
202 l_yn VARCHAR2(1);
203 result VARCHAR2(1);
204 BEGIN
205 OPEN c0;
206 FETCH c0 INTO l_yn;
207 IF c0%NOTFOUND THEN
208 result := 'N';
209 ELSE
210 result := 'Y';
211 END IF;
212 CLOSE c0;
213 RETURN result;
214 END exist_in_hz_class_categories ;
215
216 --------------------------------------------
217 -- exist_in_hz_relationship_types
218 --------------------------------------------
219
220 FUNCTION exist_in_hz_relationship_types
221 -- Return Y if the class code exists in hz_relationship_types
222 -- N otherwise
223 (p_relationship_type_id NUMBER )
224 RETURN VARCHAR2
225 IS
226 CURSOR c0
227 IS
228 SELECT 'Y'
229 FROM hz_relationship_types
230 WHERE relationship_type_id = p_relationship_type_id ;
231 l_yn VARCHAR2(1);
232 result VARCHAR2(1);
233 BEGIN
234 OPEN c0;
235 FETCH c0 INTO l_yn;
236 IF c0%NOTFOUND THEN
237 result := 'N';
238 ELSE
239 result := 'Y';
240 END IF;
241 CLOSE c0;
242 RETURN result;
243 END exist_in_hz_relationship_types ;
244
245
246 --------------------------------------------
247 -- exist_in_dss_entities
248 --------------------------------------------
249
250 FUNCTION exist_in_dss_entities
251 -- Return Y if the assignment exists in hz_dss_entities
252 -- N otherwise
256 CURSOR c0
253 (p_database_object_name VARCHAR2 )
254 RETURN VARCHAR2
255 IS
257 IS
258 select 'y'
259 from fnd_objects FND , hz_dss_entities ENT
260 where FND.database_object_name = p_database_object_name and
261 FND.object_id = ENT.object_id and
262 ENT.group_assignment_level = 'ASSIGN';
263 l_yn VARCHAR2(1);
264 result VARCHAR2(1);
265 BEGIN
266 OPEN c0;
267 FETCH c0 INTO l_yn;
268 IF c0%NOTFOUND THEN
269 result := 'N';
270 ELSE
271 result := 'Y';
272 END IF;
273 CLOSE c0;
274 RETURN result;
275 END exist_in_dss_entities ;
276
277 --------------------------------------------
278 -- exist_in_dss_entities
279 --------------------------------------------
280
281 FUNCTION exist_in_dss_entities
282 -- Return Y if the assignment exists in hz_dss_entities
283 -- N otherwise
284 (p_entity_id NUMBER )
285 RETURN VARCHAR2
286 IS
287 CURSOR c0
288 IS
289 select 'y'
290 from hz_dss_entities
291 where entity_id = p_entity_id ;
292 l_yn VARCHAR2(1);
293 result VARCHAR2(1);
294 BEGIN
295 OPEN c0;
296 FETCH c0 INTO l_yn;
297 IF c0%NOTFOUND THEN
298 result := 'N';
299 ELSE
300 result := 'Y';
301 END IF;
302 CLOSE c0;
303 RETURN result;
304 END exist_in_dss_entities ;
305
306 --------------------------------------------
307 -- is_an_obj_id_in_dss_entities
308 --------------------------------------------
309
310 FUNCTION is_an_obj_id_in_dss_entities
311 -- Return Y if the passed in entity id corresponds to an object in hz_dss_entities
312 -- N otherwise
313 (p_entity_id NUMBER )
314 RETURN VARCHAR2
315 IS
316 CURSOR c0
317 IS
318 select 'y'
319 from hz_dss_entities
320 where entity_id = p_entity_id
321 and object_id is not null ;
322 l_yn VARCHAR2(1);
323 result VARCHAR2(1);
324 BEGIN
325 OPEN c0;
326 FETCH c0 INTO l_yn;
327 IF c0%NOTFOUND THEN
328 result := 'N';
329 ELSE
330 result := 'Y';
331 END IF;
332 CLOSE c0;
333 RETURN result;
334 END is_an_obj_id_in_dss_entities ;
335
336
337
338 --------------------------------------------
339 -- exist_in_dss_assignments
340 --------------------------------------------
341
342 FUNCTION exist_in_dss_assignments
343 -- Return Y if the assignment code exists in hz_dss_assignments
344 -- N otherwise
345 (p_assignment_id VARCHAR2 )
346 RETURN VARCHAR2
347 IS
348 CURSOR c0
349 IS
350 SELECT 'Y'
351 FROM hz_dss_assignments
352 WHERE assignment_id = p_assignment_id ;
353 l_yn VARCHAR2(1);
354 result VARCHAR2(1);
355 BEGIN
356 OPEN c0;
357 FETCH c0 INTO l_yn;
358 IF c0%NOTFOUND THEN
359 result := 'N';
360 ELSE
361 result := 'Y';
362 END IF;
363 CLOSE c0;
364 RETURN result;
365 END exist_in_dss_assignments ;
366
367
368 --------------------------------------
369 -- exist_in_ar_lookups
370 --------------------------------------
371 FUNCTION exist_in_ar_lookups
372 -- Return Y if lookup_code and lookup_type are found in AR_LOOKUPS
373 -- N otherwise
374 (p_lookup_code VARCHAR2, p_lookup_type VARCHAR2 )
375 RETURN VARCHAR2
376 IS
377 CURSOR c0
378 IS
379 select 'Y'
380 from ar_lookups
381 where lookup_type = p_lookup_type
382 and lookup_code = p_lookup_code
383 and enabled_flag = 'Y' ;
384 l_yn VARCHAR2(1);
385 result VARCHAR2(1);
386 BEGIN
387 OPEN c0;
388 FETCH c0 INTO l_yn;
389 IF c0%NOTFOUND THEN
390 result := 'N';
391 ELSE
392 result := 'Y';
393 END IF;
394 CLOSE c0;
395 RETURN result;
396 END exist_in_ar_lookups ;
397
398 --------------------------------------
399 -- exist_in_ar_lookups_gl
400 --------------------------------------
401 FUNCTION exist_in_ar_lookups_gl
402 -- Return Y if lookup_code and lookup_type are found in AR_LOOKUPS
403 -- N otherwise
404 (p_lookup_code VARCHAR2, p_lookup_type VARCHAR2 )
405 RETURN VARCHAR2
406 IS
407 CURSOR c0
408 IS
409 select 'Y'
410 from ar_lookups
411 where lookup_type = p_lookup_type
412 and lookup_code = p_lookup_code;
413 l_yn VARCHAR2(1);
414 result VARCHAR2(1);
415 BEGIN
416 OPEN c0;
417 FETCH c0 INTO l_yn;
418 IF c0%NOTFOUND THEN
419 result := 'N';
420 ELSE
421 result := 'Y';
422 END IF;
423 CLOSE c0;
424 RETURN result;
425 END exist_in_ar_lookups_gl ;
426
427 --------------------------------------
428 -- exist_in_fnd_lookups
429 --------------------------------------
430 FUNCTION exist_in_fnd_lookups
431 -- Return Y if lookup_code and lookup_type are found in FND_LOOKUP_VALUES
432 -- N otherwise
433 (p_lookup_code VARCHAR2, p_lookup_type VARCHAR2 )
434 RETURN VARCHAR2
435 IS
436 CURSOR c0
437 IS
438 select 'Y'
439 from fnd_lookup_values
440 where lookup_type = p_lookup_type
441 and lookup_code = p_lookup_code
442 and enabled_flag = 'Y' ;
443 l_yn VARCHAR2(1);
444 result VARCHAR2(1);
445 BEGIN
446 OPEN c0;
447 FETCH c0 INTO l_yn;
448 IF c0%NOTFOUND THEN
449 result := 'N';
450 ELSE
451 result := 'Y';
452 END IF;
453 CLOSE c0;
454 RETURN result;
455 END exist_in_fnd_lookups ;
456 --------------------------------------------
457 -- exist_fnd_object_id
458 --------------------------------------------
459
460 FUNCTION exist_fnd_object_id
461 -- Return Y if the object id exists in FND
462 -- N otherwise
463 (p_object_id NUMBER )
464 RETURN VARCHAR2
465 IS
466 CURSOR c0
467 IS
468 SELECT 'Y'
469 FROM fnd_objects
470 WHERE object_id = p_object_id;
471 l_yn VARCHAR2(1);
472 result VARCHAR2(1);
473 BEGIN
474 OPEN c0;
475 FETCH c0 INTO l_yn;
476 IF c0%NOTFOUND THEN
477 result := 'N';
478 ELSE
479 result := 'Y';
480 END IF;
481 CLOSE c0;
482 RETURN result;
483 END exist_fnd_object_id ;
484
485
486 --------------------------------------------
487 -- exist_fnd_instance_set_id
488 --------------------------------------------
489
490
491 FUNCTION exist_fnd_instance_set_id
492 -- Return Y if the instance set id exists in FND
493 -- N otherwise
494 (p_instance_set_id NUMBER )
495 RETURN VARCHAR2
496 IS
497 CURSOR c0
498 IS
499 SELECT 'Y'
500 FROM fnd_object_instance_sets
501 WHERE instance_set_id = p_instance_set_id;
502 l_yn VARCHAR2(1);
503 result VARCHAR2(1);
504 BEGIN
505 OPEN c0;
506 FETCH c0 INTO l_yn;
507 IF c0%NOTFOUND THEN
508 result := 'N';
509 ELSE
510 result := 'Y';
511 END IF;
512 CLOSE c0;
513 RETURN result;
514 END exist_fnd_instance_set_id ;
515
516 --------------------------------------------
517 -- exist_entity_id
518 --------------------------------------------
519 FUNCTION exist_entity_id
520 -- Return Y if the entity id exists in HZ_DSS_ENTITIES
521 -- N otherwise
522 (p_entity_id NUMBER )
523 RETURN VARCHAR2
524 IS
525 CURSOR c0
526 IS
527 SELECT 'Y'
528 FROM hz_dss_entities
529 WHERE entity_id = p_entity_id ;
530 l_yn VARCHAR2(1);
531 result VARCHAR2(1);
532 BEGIN
533 OPEN c0;
534 FETCH c0 INTO l_yn;
535 IF c0%NOTFOUND THEN
536 result := 'N';
537 ELSE
538 result := 'Y';
539 END IF;
540 CLOSE c0;
541 RETURN result;
542 END exist_entity_id;
543
544
545 --------------------------------------------
546 -- exist_function_id
547 --------------------------------------------
548 FUNCTION exist_function_id
549 -- Return Y if function id exists in fnd_form_functions
550 -- N otherwise
551 (p_function_id NUMBER )
552 RETURN VARCHAR2
553 IS
554 CURSOR c0
555 IS
556 select 'Y'
557 from fnd_form_functions where function_id = p_function_id;
558 l_yn VARCHAR2(1);
559 result VARCHAR2(1);
560 BEGIN
561 OPEN c0;
562 FETCH c0 INTO l_yn;
563 IF c0%NOTFOUND THEN
564 result := 'N';
565 ELSE
566 result := 'Y';
567 END IF;
568 CLOSE c0;
569 RETURN result;
570 END exist_function_id ;
571
572 END HZ_DSS_VALIDATE_PKG;
[Home] [Help]