1 PACKAGE BODY igs_ad_gen_016 AS
2 /* $Header: IGSAD99B.pls 120.1 2005/09/30 04:45:35 appldev ship $ */
3 /* ------------------------------------------------------------------------------------------------------------------------
4 ||Created By : knag
5 ||Date Created By : 05-NOV-2003
6 ||Purpose:
7 ||Known limitations,enhancements,remarks:
8 ||Change History
9 ||Who When What
10 rbezawad 30-Oct-2004 Added check_security_exception procedure to verity if there is any Security Policy error
11 IGS_SC_POLICY_EXCEPTION or IGS_SC_POLICY_UPD_DEL_EXCEP is set in message stack w.r.t. bug fix 3919112.
12 ||-----------------------------------------------------------------------------------------------------------------------*/
13
14 l_language VARCHAR2(2000);
15 l_security_group_id NUMBER := 0;
16
17 -- Lookups
18 FUNCTION get_lookup (p_lookup_type IN VARCHAR2,
19 p_lookup_code IN VARCHAR2,
20 p_application_id IN NUMBER,
21 p_enabled_flag IN VARCHAR2 DEFAULT NULL)
22 RETURN VARCHAR2 -- Returns TRUE/FALSE
23 IS
24 lv_ret_success VARCHAR2(5);
25 ln_hash_lkcode_idx NUMBER;
26 ln_hash_lktype_idx NUMBER;
27 lb_is_lktype_cached BOOLEAN;
28
29 CURSOR c_lookup (lv_lookup_type VARCHAR2, ln_appl_id NUMBER)
30 IS
31 SELECT lookup_code, enabled_flag, meaning
32 FROM fnd_lookup_values
33 WHERE lookup_type = lv_lookup_type
34 AND view_application_id = ln_appl_id
35 AND language = l_language
36 AND security_group_id = l_security_group_id;
37
38 lr_fetched_lkcode c_lookup%ROWTYPE;
39
40 BEGIN
41 IF l_language IS NULL THEN
42 SELECT USERENV('LANG') INTO l_language FROM DUAL;
43 END IF;
44
45 -- If parameters are not valid return
46 IF (p_lookup_code IS NULL OR p_lookup_type IS NULL OR p_application_id IS NULL) THEN
47 RETURN ('FALSE');
48
49 ELSE
50 lv_ret_success := 'FALSE';
51 -- Get the hash value of the Type + Code + View Appl ID
52 ln_hash_lkcode_idx := DBMS_UTILITY.get_hash_value (
53 p_lookup_type||'@*?'||p_lookup_code||'@*?'||igs_ge_number.to_cann(p_application_id)
54 ||'@*?'||l_language||'@*?'||igs_ge_number.to_cann(l_security_group_id),
55 1000,
56 25000);
57
58 IF g_hash_lookup_code_tab.EXISTS(ln_hash_lkcode_idx) THEN
59 lv_ret_success := 'TRUE';
60 IF p_enabled_flag IS NOT NULL AND
61 p_enabled_flag <> g_hash_lookup_code_tab(ln_hash_lkcode_idx).enabled_flag THEN
62 lv_ret_success := 'FALSE';
63 END IF;
64 RETURN (lv_ret_success);
65
66 ELSE
67 -- Check if the Type is already cached
68 -- Get the hash value of the Type + View Appl ID
69 ln_hash_lktype_idx := DBMS_UTILITY.get_hash_value (
70 p_lookup_type||'@*?'||igs_ge_number.to_cann(p_application_id)
71 ||'@*?'||igs_ge_number.to_cann(l_security_group_id),
72 1000,
73 25000);
74
75 IF g_hash_lookup_type_tab.EXISTS(ln_hash_lktype_idx) THEN
76 -- Since all lookup codes are already hashed,
77 -- so the parameter one is invalid
78 RETURN ('FALSE');
79
80 ELSE
81 -- Type is not cached so cache it.
82 lb_is_lktype_cached := FALSE;
83
84 lv_ret_success := 'FALSE';
85 OPEN c_lookup (p_lookup_type, p_application_id);
86 LOOP
87 FETCH c_lookup into lr_fetched_lkcode;
88 EXIT WHEN c_lookup%NOTFOUND;
89
90 -- Cache the Lookup Type only once
91 IF NOT lb_is_lktype_cached THEN
92 lb_is_lktype_cached := TRUE;
93 g_hash_lookup_type_tab(ln_hash_lktype_idx) := p_lookup_type;
94 END IF;
95
96 ln_hash_lkcode_idx := DBMS_UTILITY.get_hash_value (
97 p_lookup_type||'@*?'||lr_fetched_lkcode.lookup_code||'@*?'||igs_ge_number.to_cann(p_application_id)
98 ||'@*?'||l_language||'@*?'||igs_ge_number.to_cann(l_security_group_id),
99 1000,
100 25000);
101
102 g_hash_lookup_code_tab(ln_hash_lkcode_idx).lookup_code := lr_fetched_lkcode.lookup_code;
103 g_hash_lookup_code_tab(ln_hash_lkcode_idx).enabled_flag := lr_fetched_lkcode.enabled_flag;
104 g_hash_lookup_code_tab(ln_hash_lkcode_idx).meaning := lr_fetched_lkcode.meaning;
105
106
107 IF p_lookup_code = lr_fetched_lkcode.lookup_code THEN
108 lv_ret_success := 'TRUE';
109 IF p_enabled_flag IS NOT NULL AND
110 p_enabled_flag <> lr_fetched_lkcode.enabled_flag THEN
111 lv_ret_success := 'FALSE';
112 END IF;
113 END IF;
114
115 END LOOP;
116 CLOSE c_lookup;
117
118 RETURN (lv_ret_success);
119
120 END IF;
121 END IF;
122 END IF;
123 END get_lookup;
124
125 FUNCTION get_lkup_meaning (p_lookup_type IN VARCHAR2,
126 p_lookup_code IN VARCHAR2,
127 p_application_id IN NUMBER)
128 RETURN VARCHAR2 -- Returns meaning
129 IS
130 lv_ret_success VARCHAR2(5);
131 ln_hash_lkcode_idx NUMBER;
132 ln_hash_lktype_idx NUMBER;
133 lb_is_lktype_cached BOOLEAN;
134
135 CURSOR c_lookup (lv_lookup_type VARCHAR2, ln_appl_id NUMBER)
136 IS
137 SELECT lookup_code, enabled_flag, meaning
138 FROM fnd_lookup_values
139 WHERE lookup_type = lv_lookup_type
140 AND view_application_id = ln_appl_id
141 AND language = l_language
142 AND security_group_id = l_security_group_id;
143
144 lr_fetched_lkcode c_lookup%ROWTYPE;
145
146 BEGIN
147 IF l_language IS NULL THEN
148 SELECT USERENV('LANG') INTO l_language FROM DUAL;
149 END IF;
150
151 -- If parameters are not valid return
152 IF (p_lookup_code IS NULL OR p_lookup_type IS NULL OR p_application_id IS NULL) THEN
153 RETURN ('FALSE');
154
155 ELSE
156 IF get_lookup (p_lookup_type => p_lookup_type,
157 p_lookup_code => p_lookup_code,
158 p_application_id => p_application_id) = 'TRUE' THEN
159
160 -- Get the hash value of the Type + Code + View Appl ID
161 ln_hash_lkcode_idx := DBMS_UTILITY.get_hash_value (
162 p_lookup_type||'@*?'||p_lookup_code||'@*?'||igs_ge_number.to_cann(p_application_id)
163 ||'@*?'||l_language||'@*?'||igs_ge_number.to_cann(l_security_group_id),
164 1000,
165 25000);
166
167 RETURN (g_hash_lookup_code_tab(ln_hash_lkcode_idx).meaning);
168 END IF;
169 RETURN (NULL);
170 END IF;
171 END get_lkup_meaning;
172
173 -- Messages
174 FUNCTION is_err_msg (p_message_name IN VARCHAR2,
175 p_application_short_name IN VARCHAR2)
176 RETURN VARCHAR2 -- Returns TRUE/FALSE
177 IS
178 lv_ret_success VARCHAR2(5);
179 ln_hash_msg_type_idx NUMBER;
180
181 CURSOR c_appl_id (lv_appl_short_name VARCHAR2)
182 IS
183 SELECT application_id
184 FROM fnd_application
185 WHERE application_short_name = lv_appl_short_name;
186
187 ln_application_id fnd_application.application_id%TYPE;
188
189 CURSOR c_msg_type (lv_message_name VARCHAR2, ln_appl_id NUMBER)
190 IS
191 SELECT NVL(type,'ERROR') type
192 FROM fnd_new_messages
193 WHERE message_name = lv_message_name
194 AND application_id = ln_appl_id
195 AND language_code = l_language;
196
197 lr_fetched_msg_type c_msg_type%ROWTYPE;
198
199 BEGIN
200 IF l_language IS NULL THEN
201 SELECT USERENV('LANG') INTO l_language FROM DUAL;
202 END IF;
203
204 OPEN c_appl_id (p_application_short_name);
205 FETCH c_appl_id INTO ln_application_id;
206 CLOSE c_appl_id;
207
208 -- If parameters are not valid return
209 IF (p_message_name IS NULL OR ln_application_id IS NULL) THEN
210 RETURN ('FALSE');
211
212 ELSE
213 lv_ret_success := 'FALSE';
214 -- Get the hash value of the Type + Code + View Appl ID
215 ln_hash_msg_type_idx := DBMS_UTILITY.get_hash_value (
216 p_message_name||'@*?'||igs_ge_number.to_cann(ln_application_id)||'@*?'||l_language,
217 1000,
218 25000);
219
220 IF g_hash_msg_type_tab.EXISTS(ln_hash_msg_type_idx) THEN
221 lv_ret_success := 'TRUE';
222 IF NVL(g_hash_msg_type_tab(ln_hash_msg_type_idx),'@*?') <> 'ERROR' THEN
223 lv_ret_success := 'FALSE';
224 END IF;
225 RETURN (lv_ret_success);
226
227 ELSE
228 lv_ret_success := 'FALSE';
229 OPEN c_msg_type (p_message_name, ln_application_id);
230 LOOP
231 FETCH c_msg_type into lr_fetched_msg_type;
232 EXIT WHEN c_msg_type%NOTFOUND;
233
234 ln_hash_msg_type_idx := DBMS_UTILITY.get_hash_value (
235 p_message_name||'@*?'||igs_ge_number.to_cann(ln_application_id)||'@*?'||l_language,
236 1000,
237 25000);
238
239 g_hash_msg_type_tab(ln_hash_msg_type_idx) := lr_fetched_msg_type.type;
240
241 lv_ret_success := 'TRUE';
242 IF NVL(lr_fetched_msg_type.type,'@*?') <> 'ERROR' THEN
243 lv_ret_success := 'FALSE';
244 END IF;
245 END LOOP;
246 CLOSE c_msg_type;
247
248 RETURN (lv_ret_success);
249 END IF;
250 END IF;
251 END is_err_msg;
252
253 -- Import process source categories
254 FUNCTION chk_src_cat (p_source_type_id IN NUMBER,
255 p_category IN VARCHAR2)
256 RETURN BOOLEAN
257 IS
258 lv_ret_success VARCHAR2(5);
259 lv_include_ind igs_ad_source_cat_all.include_ind%TYPE;
260 lv_detail_level_ind igs_ad_source_cat_all.detail_level_ind%TYPE;
261 lv_discrepancy_rule_cd igs_ad_source_cat_all.discrepancy_rule_cd%TYPE;
262 BEGIN
263 lv_include_ind := 'Y';
264 lv_ret_success := get_srccat (p_source_type_id => p_source_type_id,
265 p_category_name => p_category,
266 p_include_ind => lv_include_ind,
267 p_detail_level_ind => lv_detail_level_ind,
268 p_discrepancy_rule_cd => lv_discrepancy_rule_cd);
269
270 IF lv_ret_success = 'TRUE' THEN
271 RETURN (TRUE);
272 ELSE
273 RETURN (FALSE);
274 END IF;
275 END chk_src_cat;
276
277 FUNCTION find_source_cat_rule (p_source_type_id IN NUMBER,
278 p_category IN VARCHAR2)
279 RETURN VARCHAR2
280 IS
281 lv_ret_success VARCHAR2(5);
282 lv_include_ind igs_ad_source_cat_all.include_ind%TYPE;
283 lv_detail_level_ind igs_ad_source_cat_all.detail_level_ind%TYPE;
284 lv_discrepancy_rule_cd igs_ad_source_cat_all.discrepancy_rule_cd%TYPE;
285 BEGIN
286 lv_ret_success := get_srccat (p_source_type_id => p_source_type_id,
287 p_category_name => p_category,
288 p_include_ind => lv_include_ind,
289 p_detail_level_ind => lv_detail_level_ind,
290 p_discrepancy_rule_cd => lv_discrepancy_rule_cd);
291
292 RETURN (lv_discrepancy_rule_cd);
293 END find_source_cat_rule;
294
295 FUNCTION get_srccat (p_source_type_id IN NUMBER,
296 p_category_name IN VARCHAR2,
297 p_include_ind IN VARCHAR2 DEFAULT NULL)
298 RETURN VARCHAR2 -- Returns TRUE/FALSE
299 IS
300 lv_detail_level_ind igs_ad_source_cat_all.detail_level_ind%TYPE;
301 lv_discrepancy_rule_cd igs_ad_source_cat_all.discrepancy_rule_cd%TYPE;
302 BEGIN
303 RETURN (get_srccat (p_source_type_id => p_source_type_id,
304 p_category_name => p_category_name,
305 p_include_ind => p_include_ind,
306 p_detail_level_ind => lv_detail_level_ind,
307 p_discrepancy_rule_cd => lv_discrepancy_rule_cd));
308 END get_srccat;
309
310 FUNCTION get_srccat (p_source_type_id IN NUMBER,
311 p_category_name IN VARCHAR2,
312 p_include_ind IN VARCHAR2 DEFAULT NULL,
313 p_detail_level_ind IN OUT NOCOPY VARCHAR2,
314 p_discrepancy_rule_cd OUT NOCOPY VARCHAR2)
315 RETURN VARCHAR2 -- Returns TRUE/FALSE
316 IS
317 lv_ret_success VARCHAR2(5);
318 ln_hash_srccat_idx NUMBER;
319 ln_hash_stypeid_idx NUMBER;
320 lb_is_stypeid_cached BOOLEAN;
321
322 CURSOR c_srccat (ln_source_type_id NUMBER)
323 IS
324 SELECT category_name, include_ind, detail_level_ind, discrepancy_rule_cd
325 FROM igs_ad_source_cat_all
326 WHERE source_type_id = ln_source_type_id;
327
328 lr_fetched_srccat c_srccat%ROWTYPE;
329
330 BEGIN
331 -- If parameters are not valid return
332 IF (p_source_type_id IS NULL OR p_category_name IS NULL) THEN
333 RETURN ('FALSE');
334
335 ELSE
336 lv_ret_success := 'FALSE';
337 -- Get the hash value of the Source Type ID + Category
338 ln_hash_srccat_idx := DBMS_UTILITY.get_hash_value (
339 igs_ge_number.to_cann(p_source_type_id)||'@*?'||p_category_name,
340 1000,
341 25000);
342
343 IF g_hash_srccat_tab.EXISTS(ln_hash_srccat_idx) THEN
344 lv_ret_success := 'TRUE';
345 IF p_include_ind IS NOT NULL AND
346 p_include_ind <> g_hash_srccat_tab(ln_hash_srccat_idx).include_ind THEN
347 lv_ret_success := 'FALSE';
348 END IF;
349
350 IF p_detail_level_ind IS NOT NULL AND
354
351 p_detail_level_ind <> NVL(g_hash_srccat_tab(ln_hash_srccat_idx).detail_level_ind,'@*?') THEN
352 lv_ret_success := 'FALSE';
353 END IF;
355 IF lv_ret_success = 'TRUE' THEN
356 IF p_detail_level_ind IS NULL THEN
357 p_detail_level_ind := g_hash_srccat_tab(ln_hash_srccat_idx).detail_level_ind;
358 END IF;
359
360 IF p_detail_level_ind = 'Y' THEN
361 -- Get discrepancy rule from attribute level discrepancy rule
362 p_discrepancy_rule_cd := 'D';
363 ELSE
364 -- Get discrepancy rule for the entity (category)
365 p_discrepancy_rule_cd := g_hash_srccat_tab(ln_hash_srccat_idx).discrepancy_rule_cd;
366 END IF;
367 END IF;
368 RETURN (lv_ret_success);
369
370 ELSE
371 -- Check if the Source Type ID is already cached
372 -- Get the hash value of the Source Type ID
373 ln_hash_stypeid_idx := DBMS_UTILITY.get_hash_value (
374 igs_ge_number.to_cann(p_source_type_id),
375 1000,
376 25000);
377
378 IF g_hash_stypeid_tab.EXISTS(ln_hash_stypeid_idx) THEN
379 -- Since all categories are already hashed,
380 -- so the parameter one is invalid
381 RETURN ('FALSE');
382
383 ELSE
384 -- Source Type ID is not cached so cache it.
385 lb_is_stypeid_cached := FALSE;
386
387 lv_ret_success := 'FALSE';
388 OPEN c_srccat (p_source_type_id);
389 LOOP
390 FETCH c_srccat into lr_fetched_srccat;
391 EXIT WHEN c_srccat%NOTFOUND;
392
393 -- Cache the Source Type ID only once
394 IF NOT lb_is_stypeid_cached THEN
395 lb_is_stypeid_cached := TRUE;
396 g_hash_stypeid_tab(ln_hash_stypeid_idx) := p_source_type_id;
397 END IF;
398
399 ln_hash_srccat_idx := DBMS_UTILITY.get_hash_value (
400 igs_ge_number.to_cann(p_source_type_id)||'@*?'||lr_fetched_srccat.category_name,
401 1000,
402 25000);
403
404 g_hash_srccat_tab(ln_hash_srccat_idx).category_name := lr_fetched_srccat.category_name;
405 g_hash_srccat_tab(ln_hash_srccat_idx).include_ind := lr_fetched_srccat.include_ind;
406 g_hash_srccat_tab(ln_hash_srccat_idx).detail_level_ind := lr_fetched_srccat.detail_level_ind;
407 g_hash_srccat_tab(ln_hash_srccat_idx).discrepancy_rule_cd := lr_fetched_srccat.discrepancy_rule_cd;
408
409 IF p_category_name = g_hash_srccat_tab(ln_hash_srccat_idx).category_name THEN
410 lv_ret_success := 'TRUE';
411
412 IF p_include_ind IS NOT NULL AND
413 p_include_ind <> g_hash_srccat_tab(ln_hash_srccat_idx).include_ind THEN
414 lv_ret_success := 'FALSE';
415 END IF;
416
417 IF p_detail_level_ind IS NOT NULL AND
418 p_detail_level_ind <> NVL(g_hash_srccat_tab(ln_hash_srccat_idx).detail_level_ind,'@*?') THEN
419 lv_ret_success := 'FALSE';
420 END IF;
421
422 IF lv_ret_success = 'TRUE' THEN
423 IF p_detail_level_ind IS NULL THEN
424 p_detail_level_ind := g_hash_srccat_tab(ln_hash_srccat_idx).detail_level_ind;
425 END IF;
426
427 IF p_detail_level_ind = 'Y' THEN
428 -- Get discrepancy rule from attribute level discrepancy rule
429 p_discrepancy_rule_cd := 'D';
430 ELSE
431 -- Get discrepancy rule for the entity (category)
432 p_discrepancy_rule_cd := g_hash_srccat_tab(ln_hash_srccat_idx).discrepancy_rule_cd;
433 END IF;
434 END IF;
435 END IF;
436
437 END LOOP;
438 CLOSE c_srccat;
439
440 RETURN (lv_ret_success);
441 END IF;
442 END IF;
443 END IF;
444 END get_srccat;
445
446 -- Code Classes
447 FUNCTION get_codeclass (p_class IN VARCHAR2,
448 p_code_id IN NUMBER)
449 RETURN VARCHAR2 -- Returns TRUE/FALSE
450 IS
451 ln_code_id igs_ad_code_classes.code_id%TYPE;
452 lv_name igs_ad_code_classes.name%TYPE;
453 lv_system_status igs_ad_code_classes.system_status%TYPE;
454 lv_closed_ind igs_ad_code_classes.closed_ind%TYPE;
455 BEGIN
456 ln_code_id := p_code_id;
457 lv_closed_ind := 'N';
458 RETURN (get_codeclass (p_class => p_class,
459 p_code_id => ln_code_id,
460 p_name => lv_name,
461 p_system_status => lv_system_status,
462 p_closed_ind => lv_closed_ind));
463 END get_codeclass;
464
465 FUNCTION get_codeclass (p_class IN VARCHAR2,
466 p_name IN VARCHAR2)
467 RETURN VARCHAR2 -- Returns TRUE/FALSE
468 IS
469 ln_code_id igs_ad_code_classes.code_id%TYPE;
470 lv_name igs_ad_code_classes.name%TYPE;
471 lv_system_status igs_ad_code_classes.system_status%TYPE;
472 lv_closed_ind igs_ad_code_classes.closed_ind%TYPE;
473 BEGIN
474 lv_name := p_name;
475 lv_closed_ind := 'N';
476 RETURN (get_codeclass (p_class => p_class,
477 p_code_id => ln_code_id,
478 p_name => lv_name,
479 p_system_status => lv_system_status,
480 p_closed_ind => lv_closed_ind));
481 END get_codeclass;
482
483 FUNCTION get_codeclass (p_class IN VARCHAR2,
484 p_code_id IN OUT NOCOPY NUMBER,
485 p_name IN OUT NOCOPY VARCHAR2)
486 RETURN VARCHAR2 -- Returns TRUE/FALSE
487 IS
488 lv_system_status igs_ad_code_classes.system_status%TYPE;
489 lv_closed_ind igs_ad_code_classes.closed_ind%TYPE;
490 BEGIN
491 lv_closed_ind := 'N';
492 RETURN (get_codeclass (p_class => p_class,
493 p_code_id => p_code_id,
494 p_name => p_name,
495 p_system_status => lv_system_status,
496 p_closed_ind => lv_closed_ind));
497 END get_codeclass;
498
499 FUNCTION get_def_code (p_class IN VARCHAR2,
500 p_code_id OUT NOCOPY NUMBER,
501 p_name OUT NOCOPY VARCHAR2,
502 p_system_status IN VARCHAR2)
503 RETURN VARCHAR2 -- Returns TRUE/FALSE
504 IS
505 lv_ret_success VARCHAR2(5);
506 ln_code_id igs_ad_code_classes.code_id%TYPE;
507 lv_name igs_ad_code_classes.name%TYPE;
508 lv_system_status igs_ad_code_classes.system_status%TYPE;
509 lv_closed_ind igs_ad_code_classes.closed_ind%TYPE;
510 ln_hash_dflt_cc_id_hashidx_idx NUMBER;
511 ln_hash_ccode_idx NUMBER;
512 BEGIN
513 -- If parameters are not valid return
514 IF (p_class IS NULL OR p_system_status IS NULL) THEN
515 RETURN ('FALSE');
516
517 ELSE
518 ln_hash_dflt_cc_id_hashidx_idx := DBMS_UTILITY.get_hash_value (
519 p_class||'@*?'||p_system_status||'@*?'||'Y',
520 1000,
521 25000);
522
523 IF g_hash_dflt_cc_id_hashidx_tab.EXISTS(ln_hash_dflt_cc_id_hashidx_idx) THEN
524 ln_hash_ccode_idx := g_hash_dflt_cc_id_hashidx_tab(ln_hash_dflt_cc_id_hashidx_idx);
525 ln_code_id := g_hash_ccode_tab(ln_hash_ccode_idx).code_id;
526 lv_name := g_hash_ccode_tab(ln_hash_ccode_idx).name;
527 lv_ret_success := 'TRUE';
528 END IF;
529
530 IF ln_code_id IS NULL THEN
531 lv_system_status := p_system_status;
532 lv_closed_ind := 'N';
533 lv_ret_success := get_codeclass (p_class => p_class,
534 p_code_id => ln_code_id,
535 p_name => lv_name,
536 p_system_status => lv_system_status,
537 p_closed_ind => lv_closed_ind);
538 END IF;
539 p_code_id := ln_code_id;
540 p_name := lv_name;
541 RETURN (lv_ret_success);
542 END IF;
543 END get_def_code;
544
545 FUNCTION get_codeclass (p_class IN VARCHAR2,
546 p_code_id IN OUT NOCOPY NUMBER,
547 p_name IN OUT NOCOPY VARCHAR2,
548 p_system_status IN OUT NOCOPY VARCHAR2,
549 p_closed_ind IN VARCHAR2 DEFAULT NULL)
550 RETURN VARCHAR2 -- Returns TRUE/FALSE
551 IS
552 lv_ret_success VARCHAR2(5);
553 ln_hash_name_cc_id_hashidx_idx NUMBER;
554 ln_hash_dflt_cc_id_hashidx_idx NUMBER;
555 ln_hash_ccode_idx NUMBER;
556 ln_hash_cclass_idx NUMBER;
557 lb_is_cclass_cached BOOLEAN;
558
559 CURSOR c_ccode (lv_class VARCHAR2)
560 IS
561 SELECT code_id, name, system_status, closed_ind, system_default
562 FROM igs_ad_code_classes
563 WHERE class = lv_class
564 AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
565
566 lr_fetched_ccode c_ccode%ROWTYPE;
567
568 BEGIN
569 -- If parameters are not valid return
570 IF (p_class IS NULL OR (p_code_id IS NULL AND p_name IS NULL)) AND
571 (p_class IS NULL OR p_system_status IS NULL) THEN
572 RETURN ('FALSE');
573
574 ELSE
575 -- Code name has been passed
576 IF p_name IS NOT NULL THEN
577 -- Get the hash value of the Class + Code Name
578 ln_hash_name_cc_id_hashidx_idx := DBMS_UTILITY.get_hash_value (
579 p_class||'@*?'||p_name,
580 1000,
581 25000);
582
583 IF g_hash_name_cc_id_hashidx_tab.EXISTS(ln_hash_name_cc_id_hashidx_idx) THEN
587 ELSIF p_code_id IS NOT NULL THEN
584 ln_hash_ccode_idx := g_hash_name_cc_id_hashidx_tab(ln_hash_name_cc_id_hashidx_idx);
585 END IF;
586
588 -- Get the hash value of the Type + Code ID
589 ln_hash_ccode_idx := DBMS_UTILITY.get_hash_value (
590 p_class||'@*?'||igs_ge_number.to_cann(p_code_id),
591 1000,
592 25000);
593 END IF;
594
595 lv_ret_success := 'TRUE';
596
597 IF ln_hash_ccode_idx IS NOT NULL AND
598 g_hash_ccode_tab.EXISTS(ln_hash_ccode_idx) THEN
599 IF p_code_id IS NOT NULL AND
600 p_code_id <> g_hash_ccode_tab(ln_hash_ccode_idx).code_id THEN
601 lv_ret_success := 'FALSE';
602 END IF;
603
604 IF p_name IS NOT NULL AND
605 p_name <> g_hash_ccode_tab(ln_hash_ccode_idx).name THEN
606 lv_ret_success := 'FALSE';
607 END IF;
608
609 IF p_system_status IS NOT NULL AND
610 p_system_status <> NVL(g_hash_ccode_tab(ln_hash_ccode_idx).system_status,'@*?') THEN
611 lv_ret_success := 'FALSE';
612 END IF;
613
614 IF p_closed_ind IS NOT NULL AND
615 p_closed_ind <> g_hash_ccode_tab(ln_hash_ccode_idx).closed_ind THEN
616 lv_ret_success := 'FALSE';
617 END IF;
618
619 IF lv_ret_success = 'TRUE' THEN
620 IF p_code_id IS NULL THEN
621 p_code_id := g_hash_ccode_tab(ln_hash_ccode_idx).code_id;
622 END IF;
623
624 IF p_name IS NULL THEN
625 p_name := g_hash_ccode_tab(ln_hash_ccode_idx).name;
626 END IF;
627
628 IF p_system_status IS NULL THEN
629 p_system_status := g_hash_ccode_tab(ln_hash_ccode_idx).system_status;
630 END IF;
631 END IF;
632 RETURN (lv_ret_success);
633
634 ELSE
635 -- Check if the Class is already cached
636 -- Get the hash value of the Class
637 ln_hash_cclass_idx := DBMS_UTILITY.get_hash_value (
638 p_class,
639 1000,
640 25000);
641
642 IF g_hash_cclass_tab.EXISTS(ln_hash_cclass_idx) THEN
643 -- Since all codes are already hashed,
644 -- so the parameter one is invalid
645 RETURN ('FALSE');
646
647 ELSE
648 -- Class is not cached so cache it.
649 lb_is_cclass_cached := FALSE;
650
651 lv_ret_success := 'FALSE';
652 OPEN c_ccode (p_class);
653 LOOP
654 FETCH c_ccode into lr_fetched_ccode;
655 EXIT WHEN c_ccode%NOTFOUND;
656
657 -- Cache the Class only once
658 IF NOT lb_is_cclass_cached THEN
659 lb_is_cclass_cached := TRUE;
660 g_hash_cclass_tab(ln_hash_cclass_idx) := p_class;
661 END IF;
662
663 ln_hash_ccode_idx := DBMS_UTILITY.get_hash_value (
664 p_class||'@*?'||igs_ge_number.to_cann(lr_fetched_ccode.code_id),
665 1000,
666 25000);
667
668 g_hash_ccode_tab(ln_hash_ccode_idx).code_id := lr_fetched_ccode.code_id;
669 g_hash_ccode_tab(ln_hash_ccode_idx).name := lr_fetched_ccode.name;
670 g_hash_ccode_tab(ln_hash_ccode_idx).system_status := lr_fetched_ccode.system_status;
671 g_hash_ccode_tab(ln_hash_ccode_idx).closed_ind := lr_fetched_ccode.closed_ind;
672 g_hash_ccode_tab(ln_hash_ccode_idx).system_default := lr_fetched_ccode.system_default;
673
674 ln_hash_name_cc_id_hashidx_idx := DBMS_UTILITY.get_hash_value (
675 p_class||'@*?'||lr_fetched_ccode.name,
676 1000,
677 25000);
678
679 g_hash_name_cc_id_hashidx_tab(ln_hash_name_cc_id_hashidx_idx) := ln_hash_ccode_idx;
680
681 IF lr_fetched_ccode.closed_ind = 'N' AND
682 NVL(lr_fetched_ccode.system_default,'N') = 'Y' AND
683 lr_fetched_ccode.system_status IS NOT NULL THEN
684 ln_hash_dflt_cc_id_hashidx_idx := DBMS_UTILITY.get_hash_value (
685 p_class||'@*?'||lr_fetched_ccode.system_status||'@*?'||'Y',
686 1000,
687 25000);
688
689 g_hash_dflt_cc_id_hashidx_tab(ln_hash_dflt_cc_id_hashidx_idx) := ln_hash_ccode_idx;
690 END IF;
691
692 IF (NVL(p_code_id,-1) = g_hash_ccode_tab(ln_hash_ccode_idx).code_id) OR
693 (NVL(p_name,'@*?') = g_hash_ccode_tab(ln_hash_ccode_idx).name) OR
694 (NVL(p_system_status,'@*?') = NVL(g_hash_ccode_tab(ln_hash_ccode_idx).system_status,'?*@') AND
695 p_code_id IS NULL AND p_name IS NULL) THEN
696 lv_ret_success := 'TRUE';
697
698 IF p_code_id IS NOT NULL AND
699 p_code_id <> g_hash_ccode_tab(ln_hash_ccode_idx).code_id THEN
700 lv_ret_success := 'FALSE';
701 END IF;
702
703 IF p_name IS NOT NULL AND
704 p_name <> g_hash_ccode_tab(ln_hash_ccode_idx).name THEN
705 lv_ret_success := 'FALSE';
706 END IF;
707
708 IF p_system_status IS NOT NULL AND
709 p_system_status <> NVL(g_hash_ccode_tab(ln_hash_ccode_idx).system_status,'@*?') THEN
710 lv_ret_success := 'FALSE';
711 END IF;
712
713 IF p_closed_ind IS NOT NULL AND
714 p_closed_ind <> g_hash_ccode_tab(ln_hash_ccode_idx).closed_ind THEN
715 lv_ret_success := 'FALSE';
716 END IF;
717
718 IF lv_ret_success = 'TRUE' THEN
719 IF p_code_id IS NULL THEN
720 p_code_id := g_hash_ccode_tab(ln_hash_ccode_idx).code_id;
721 END IF;
722
723 IF p_name IS NULL THEN
724 p_name := g_hash_ccode_tab(ln_hash_ccode_idx).name;
725 END IF;
726
727 IF p_system_status IS NULL THEN
728 p_system_status := g_hash_ccode_tab(ln_hash_ccode_idx).system_status;
729 END IF;
730 END IF;
731 END IF;
732
733 END LOOP;
734 CLOSE c_ccode;
735
736 RETURN (lv_ret_success);
737 END IF;
738 END IF;
739 END IF;
740 END get_codeclass;
741
742 -- Application Type to Admission Process Category
743 FUNCTION get_appl_type_apc (p_application_type IN VARCHAR2)
744 RETURN VARCHAR2 -- Returns TRUE/FALSE
745 IS
746 lv_admission_cat igs_ad_prcs_cat.admission_cat%TYPE;
747 lv_s_admission_process_type igs_ad_prcs_cat.s_admission_process_type%TYPE;
748 BEGIN
749 RETURN (get_appl_type_apc (p_application_type => p_application_type,
750 p_admission_cat => lv_admission_cat,
751 p_s_admission_process_type => lv_s_admission_process_type));
752 END get_appl_type_apc;
753
754 FUNCTION get_appl_type_apc (p_application_type IN VARCHAR2,
755 p_admission_cat OUT NOCOPY VARCHAR2,
756 p_s_admission_process_type OUT NOCOPY VARCHAR2)
757 RETURN VARCHAR2 -- Returns TRUE/FALSE
758 IS
759 lv_ret_success VARCHAR2(5);
760 ln_hash_appl_type_apc_idx NUMBER;
761
762 CURSOR c_appl_type_apc
763 IS
764 SELECT admission_application_type, admission_cat, s_admission_process_type
765 FROM igs_ad_ss_appl_typ;
766
767 lr_fetched_appl_type_apc c_appl_type_apc%ROWTYPE;
768
769 BEGIN
770
771 -- If parameters are not valid return
772 IF (p_application_type IS NULL) THEN
773 RETURN ('FALSE');
774
775 ELSE
776 -- Get the hash value of the Application Type
777 ln_hash_appl_type_apc_idx := DBMS_UTILITY.get_hash_value (
778 p_application_type,
779 1000,
780 25000);
781
782 IF g_hash_appl_type_apc_tab.EXISTS(ln_hash_appl_type_apc_idx) THEN
783 p_admission_cat := g_hash_appl_type_apc_tab(ln_hash_appl_type_apc_idx).admission_cat;
784 p_s_admission_process_type := g_hash_appl_type_apc_tab(ln_hash_appl_type_apc_idx).s_admission_process_type;
785 RETURN ('TRUE');
786
787 ELSE
788 IF g_hash_appl_type_apc_tab.COUNT > 0 THEN
789 RETURN ('FALSE');
790
791 ELSE
792 lv_ret_success := 'FALSE';
793
794 OPEN c_appl_type_apc;
795 LOOP
796 FETCH c_appl_type_apc into lr_fetched_appl_type_apc;
797 EXIT WHEN c_appl_type_apc%NOTFOUND;
798
799 ln_hash_appl_type_apc_idx := DBMS_UTILITY.get_hash_value (
800 lr_fetched_appl_type_apc.admission_application_type,
801 1000,
802 25000);
803
804 g_hash_appl_type_apc_tab(ln_hash_appl_type_apc_idx).admission_cat := lr_fetched_appl_type_apc.admission_cat;
805 g_hash_appl_type_apc_tab(ln_hash_appl_type_apc_idx).s_admission_process_type := lr_fetched_appl_type_apc.s_admission_process_type;
806
807 IF p_application_type = lr_fetched_appl_type_apc.admission_application_type THEN
808 p_admission_cat := lr_fetched_appl_type_apc.admission_cat;
809 p_s_admission_process_type := lr_fetched_appl_type_apc.s_admission_process_type;
810 lv_ret_success := 'TRUE';
811 END IF;
812
813 END LOOP;
814 CLOSE c_appl_type_apc;
815
816 RETURN (lv_ret_success);
817
818 END IF;
819 END IF;
820 END IF;
821 END get_appl_type_apc;
822
823 -- Admission Process Category Steps
824 FUNCTION get_apcs (p_admission_cat IN VARCHAR2,
825 p_s_admission_process_type IN VARCHAR2,
826 p_s_admission_step_type IN VARCHAR2)
827 RETURN VARCHAR2 -- Returns TRUE/FALSE
828 IS
829 lv_mandatory_step_ind igs_ad_prcs_cat_step_all.mandatory_step_ind%TYPE;
830 ln_step_type_restriction_num igs_ad_prcs_cat_step_all.step_type_restriction_num%TYPE;
831 BEGIN
832 RETURN (get_apcs (p_admission_cat => p_admission_cat,
833 p_s_admission_process_type => p_s_admission_process_type,
834 p_s_admission_step_type => p_s_admission_step_type,
835 p_mandatory_step_ind => lv_mandatory_step_ind,
836 p_step_type_restriction_num => ln_step_type_restriction_num));
837 END get_apcs;
838
839 FUNCTION get_apcs_mnd (p_admission_cat IN VARCHAR2,
840 p_s_admission_process_type IN VARCHAR2,
841 p_s_admission_step_type IN VARCHAR2)
842 RETURN VARCHAR2 -- Returns TRUE/FALSE
843 IS
844 lv_mandatory_step_ind igs_ad_prcs_cat_step_all.mandatory_step_ind%TYPE;
845 ln_step_type_restriction_num igs_ad_prcs_cat_step_all.step_type_restriction_num%TYPE;
846 BEGIN
847 lv_mandatory_step_ind := 'Y';
848 RETURN (get_apcs (p_admission_cat => p_admission_cat,
849 p_s_admission_process_type => p_s_admission_process_type,
850 p_s_admission_step_type => p_s_admission_step_type,
851 p_mandatory_step_ind => lv_mandatory_step_ind,
852 p_step_type_restriction_num => ln_step_type_restriction_num));
853 END get_apcs_mnd;
854
855 FUNCTION get_apcs (p_admission_cat IN VARCHAR2,
856 p_s_admission_process_type IN VARCHAR2,
857 p_s_admission_step_type IN VARCHAR2,
858 p_mandatory_step_ind IN OUT NOCOPY VARCHAR2)
859 RETURN VARCHAR2 -- Returns TRUE/FALSE
860 IS
861 ln_step_type_restriction_num igs_ad_prcs_cat_step_all.step_type_restriction_num%TYPE;
862 BEGIN
863 RETURN (get_apcs (p_admission_cat => p_admission_cat,
864 p_s_admission_process_type => p_s_admission_process_type,
865 p_s_admission_step_type => p_s_admission_step_type,
866 p_mandatory_step_ind => p_mandatory_step_ind,
867 p_step_type_restriction_num => ln_step_type_restriction_num));
868 END get_apcs;
869
870 FUNCTION get_apcs (p_admission_cat IN VARCHAR2,
871 p_s_admission_process_type IN VARCHAR2,
872 p_s_admission_step_type IN VARCHAR2,
873 p_mandatory_step_ind IN OUT NOCOPY VARCHAR2,
874 p_step_type_restriction_num OUT NOCOPY NUMBER)
875 RETURN VARCHAR2 -- Returns TRUE/FALSE
876 IS
877 lv_ret_success VARCHAR2(5);
878 ln_hash_apcs_idx NUMBER;
879 ln_hash_apc_idx NUMBER;
880 lb_is_apc_cached BOOLEAN;
881 lv_step_group_type_excl igs_ad_prcs_cat_step_all.step_group_type%TYPE;
882
883 CURSOR c_apcs (lv_admission_cat VARCHAR2, lv_s_admission_process_type VARCHAR2)
884 IS
885 SELECT s_admission_step_type, mandatory_step_ind, step_type_restriction_num
886 FROM igs_ad_prcs_cat_step_all
887 WHERE admission_cat = lv_admission_cat
888 AND s_admission_process_type = lv_s_admission_process_type
889 AND step_group_type <> lv_step_group_type_excl;
890
891 lr_fetched_apcs c_apcs%ROWTYPE;
892
893 BEGIN
894
895 lv_step_group_type_excl := 'TRACK';
896 -- If parameters are not valid return
897 IF (p_admission_cat IS NULL OR p_s_admission_process_type IS NULL OR p_s_admission_step_type IS NULL) THEN
898 RETURN ('FALSE');
899
900 ELSE
901 -- Get the hash value of the Admission Category + System Process Type + System Step
902 ln_hash_apcs_idx := DBMS_UTILITY.get_hash_value (
903 p_admission_cat||'@*?'||p_s_admission_process_type||'@*?'||p_s_admission_step_type,
904 1000,
905 25000);
906
907 lv_ret_success := 'TRUE';
908
909 IF g_hash_apcs_tab.EXISTS(ln_hash_apcs_idx) THEN
910 IF p_mandatory_step_ind IS NOT NULL AND
911 p_mandatory_step_ind <> g_hash_apcs_tab(ln_hash_apcs_idx).mandatory_step_ind THEN
912 lv_ret_success := 'FALSE';
913 END IF;
914
915 IF lv_ret_success = 'TRUE' THEN
916 IF p_mandatory_step_ind IS NULL THEN
917 p_mandatory_step_ind := g_hash_apcs_tab(ln_hash_apcs_idx).mandatory_step_ind;
918 END IF;
919
920 p_step_type_restriction_num := g_hash_apcs_tab(ln_hash_apcs_idx).step_type_restriction_num;
921 END IF;
922
923 RETURN (lv_ret_success);
924
925 ELSE
926 -- Check if the Admission Category + System Process Type is already cached
927 -- Get the hash value of the Admission Category + System Process Type
928 ln_hash_apc_idx := DBMS_UTILITY.get_hash_value (
929 p_admission_cat||'@*?'||p_s_admission_process_type,
930 1000,
931 25000);
932
933 IF g_hash_apc_tab.EXISTS(ln_hash_apc_idx) THEN
934 -- Since all codes are already hashed,
935 -- so the parameter one is invalid
936 RETURN ('FALSE');
937
938 ELSE
939 -- Admission Category + System Process Type is not cached so cache it.
940 lb_is_apc_cached := FALSE;
941
942 lv_ret_success := 'FALSE';
943 OPEN c_apcs (p_admission_cat, p_s_admission_process_type);
944 LOOP
945 FETCH c_apcs into lr_fetched_apcs;
946 EXIT WHEN c_apcs%NOTFOUND;
947
948 -- Cache the Admission Category + System Process Type only once
949 IF NOT lb_is_apc_cached THEN
950 lb_is_apc_cached := TRUE;
951 g_hash_apc_tab(ln_hash_apc_idx).admission_cat := p_admission_cat;
952 g_hash_apc_tab(ln_hash_apc_idx).s_admission_process_type := p_s_admission_process_type;
953 END IF;
954
955 ln_hash_apcs_idx := DBMS_UTILITY.get_hash_value (
956 p_admission_cat||'@*?'||p_s_admission_process_type||'@*?'||lr_fetched_apcs.s_admission_step_type,
957 1000,
958 25000);
959
960 g_hash_apcs_tab(ln_hash_apcs_idx).s_admission_step_type := lr_fetched_apcs.s_admission_step_type;
961 g_hash_apcs_tab(ln_hash_apcs_idx).mandatory_step_ind := lr_fetched_apcs.mandatory_step_ind;
962 g_hash_apcs_tab(ln_hash_apcs_idx).step_type_restriction_num := lr_fetched_apcs.step_type_restriction_num;
963
964 IF (p_s_admission_step_type = g_hash_apcs_tab(ln_hash_apcs_idx).s_admission_step_type) THEN
965 lv_ret_success := 'TRUE';
966
967 IF p_mandatory_step_ind IS NOT NULL AND
968 p_mandatory_step_ind <> g_hash_apcs_tab(ln_hash_apcs_idx).mandatory_step_ind THEN
969 lv_ret_success := 'FALSE';
970 END IF;
971
972 IF lv_ret_success = 'TRUE' THEN
973 IF p_mandatory_step_ind IS NULL THEN
974 p_mandatory_step_ind := g_hash_apcs_tab(ln_hash_apcs_idx).mandatory_step_ind;
975 END IF;
976
977 p_step_type_restriction_num := g_hash_apcs_tab(ln_hash_apcs_idx).step_type_restriction_num;
978 END IF;
979 END IF;
980
981 END LOOP;
982 CLOSE c_apcs;
983
984 RETURN (lv_ret_success);
985 END IF;
986 END IF;
987 END IF;
988 END get_apcs;
989
990 -- Extract message from stack
991 PROCEDURE extract_msg_from_stack (p_msg_at_index NUMBER,
992 p_return_status OUT NOCOPY VARCHAR2,
993 p_msg_count OUT NOCOPY NUMBER,
994 p_msg_data OUT NOCOPY VARCHAR2,
995 p_hash_msg_name_text_type_tab OUT NOCOPY igs_ad_gen_016.g_msg_name_text_type_table)
996 IS
997 l_old_msg_count NUMBER;
998 l_new_msg_count NUMBER;
999 l_msg_inc_factr NUMBER := 1;
1000 l_msg_idx_start NUMBER;
1001 l_msg_txt fnd_new_messages.message_text%TYPE;
1002 l_app_nme varchar2(1000);
1003 l_msg_nme varchar2(2000);
1004 l_err_msg_logged varchar2(5);
1005 l_hash_msg_name_text_type_tab g_msg_name_text_type_table;
1006 l_table_index binary_integer := 0;
1007 l_msg_ret fnd_new_messages.message_text%TYPE;
1008 l_msg_sqlerrm VARCHAR2(4000);
1009 BEGIN
1010 l_old_msg_count := p_msg_at_index;
1011 l_new_msg_count := igs_ge_msg_stack.count_msg;
1012 --dbms_output.put_line('upper bound message level - '||to_char(l_new_msg_count));
1013
1014 p_msg_count := l_new_msg_count - l_old_msg_count;
1015
1016 WHILE (l_new_msg_count - l_old_msg_count) > 0
1017 LOOP
1018 --igs_ge_msg_stack.get(l_old_msg_count+l_msg_inc_factr,'F',l_msg_txt,l_msg_idx_start);
1019 igs_ge_msg_stack.get(l_old_msg_count+l_msg_inc_factr,'T',l_msg_txt,l_msg_idx_start);
1020
1021 --dbms_output.put_line('message read from stack index - '||to_char(l_msg_idx_start));
1022
1023 igs_ge_msg_stack.delete_msg(l_msg_idx_start);
1024 l_new_msg_count := l_new_msg_count -1;
1025
1026 fnd_message.parse_encoded (l_msg_txt, l_app_nme, l_msg_nme);
1027 fnd_message.set_encoded (l_msg_txt);
1028 l_msg_txt := fnd_message.get;
1029
1030 --dbms_output.put_line(fnd_global.tab||l_app_nme||' '||l_msg_nme||' '||l_msg_txt);
1031
1032 IF NVL(l_err_msg_logged,'@*?') <> 'TRUE' THEN
1033 l_err_msg_logged := igs_ad_gen_016.is_err_msg (l_msg_nme, l_app_nme);
1034 IF NVL(l_err_msg_logged,'@*?') <> 'TRUE' THEN
1035 IF l_msg_ret IS NULL THEN
1036 IF SUBSTR(l_msg_txt,1,4) <> 'ORA-' THEN
1037 l_msg_ret := l_msg_txt;
1038 ELSE
1039 l_msg_ret := SUBSTR(l_msg_txt,12,LENGTH(l_msg_txt));
1040 END IF;
1041 p_return_status := 'S';
1042 END IF;
1043 ELSE
1044 IF SUBSTR(l_msg_txt,1,4) <> 'ORA-' THEN
1045 l_msg_ret := l_msg_txt;
1046 ELSE
1047 l_msg_ret := SUBSTR(l_msg_txt,12,LENGTH(l_msg_txt));
1048 END IF;
1049 p_return_status := 'E';
1050 END IF;
1051 END IF;
1052
1053 l_hash_msg_name_text_type_tab(l_table_index).appl := l_app_nme;
1054 IF igs_ad_gen_016.is_err_msg (l_msg_nme, l_app_nme) = 'TRUE' THEN
1055 l_hash_msg_name_text_type_tab(l_table_index).type := 'E';
1056 ELSE
1057 l_hash_msg_name_text_type_tab(l_table_index).type := 'S';
1058 END IF;
1059 l_hash_msg_name_text_type_tab(l_table_index).name := l_msg_nme;
1060 l_hash_msg_name_text_type_tab(l_table_index).text := l_msg_txt;
1061 l_table_index := l_table_index + 1;
1062 END LOOP;
1063
1064 l_msg_sqlerrm := SQLERRM;
1065 IF SQLCODE <> 0 THEN
1066 IF ABS(TO_CHAR(SQLCODE)) > 20000 THEN
1067 p_return_status := 'E';
1068 ELSE
1069 p_return_status := 'U';
1070 p_msg_count := l_hash_msg_name_text_type_tab.COUNT + 1;
1071 l_hash_msg_name_text_type_tab(l_table_index).appl := 'ORA';
1072 l_hash_msg_name_text_type_tab(l_table_index).type := 'U';
1073 l_hash_msg_name_text_type_tab(l_table_index).name := 'ORA';
1074 l_hash_msg_name_text_type_tab(l_table_index).text := l_msg_sqlerrm;
1075 l_msg_ret := l_msg_sqlerrm;
1076 END IF;
1077 END IF;
1078
1079 p_msg_data := l_msg_ret;
1080 p_hash_msg_name_text_type_tab := l_hash_msg_name_text_type_tab;
1081
1082 --dbms_output.put_line('returned message - '||l_msg_ret);
1083 --dbms_output.put_line('returned SQLERR message - '||l_msg_sqlerrm);
1084 --dbms_output.put_line('message level when done - '||to_char(igs_ge_msg_stack.count_msg));
1085 --dbms_output.put_line('return status - '||p_return_status||' message count - '||to_char(p_msg_count)||' message data - '||p_msg_data);
1086 END extract_msg_from_stack;
1087
1088
1089
1090 PROCEDURE check_security_exception
1091 IS
1092 /* ------------------------------------------------------------------------------------------------------------------------
1093 Created By : rbezawad
1094 Date Created By : 30-Oct-04
1095 Purpose: This procedure will be called from Library(pld) to check if there are any error messages set in the stack
1096 which are related to Security Policies (IGS_SC_POLICY_EXCEPTION, IGS_SC_POLICY_UPD_DEL_EXCEP). And it again sets
1097 Security Policy error on top, so that this can be shown to the user on form. It also clears the Message stack so that
1098 user won't see the same error message again and again when some other operation is performed from the same form
1099 i.e., in same session.
1100 Known limitations,enhancements,remarks:
1101 Change History
1102 Who When What
1103 -----------------------------------------------------------------------------------------------------------------------*/
1104 l_encoded_text VARCHAR2(4000);
1105 l_msg_count NUMBER;
1106 l_msg_index NUMBER;
1107 l_app_short_name VARCHAR2(50);
1108 l_message_name VARCHAR2(50);
1109
1110 BEGIN
1111
1112 l_msg_count := IGS_GE_MSG_STACK.COUNT_MSG;
1113 WHILE l_msg_count <> 0 loop
1114 IGS_GE_MSG_STACK.GET(l_msg_count, 'T', l_encoded_text, l_msg_index);
1115 IGS_GE_MSG_STACK.DELETE_MSG(l_msg_index);
1116 fnd_message.parse_encoded(l_encoded_text, l_app_short_name, l_message_name);
1117 IF l_message_name = 'IGS_SC_POLICY_EXCEPTION' OR l_message_name = 'IGS_SC_POLICY_UPD_DEL_EXCEP' THEN
1118 fnd_message.set_encoded(l_encoded_text);
1119 END IF;
1120 l_msg_count := l_msg_count -1;
1121 END LOOP;
1122
1123 END check_security_exception;
1124
1125 END igs_ad_gen_016;
[Home] [Help]