1 Package Body IBY_UTILITY_PVT AS
2 /* $Header: ibyvutlb.pls 120.13.12010000.9 2008/11/24 14:53:44 vkarlapu ship $ */
3
4
5 -- Added for encoding/decoding in Base64.
6 TYPE vc2_table IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
7
8 TYPE t_psr_record_type IS RECORD(
9 psr_status VARCHAR2(30),
10 payment_status_flag VARCHAR2(1)
11 );
12
13 TYPE psr_table_type IS TABLE OF t_psr_record_type INDEX BY BINARY_INTEGER;
14
15 g_psr_table psr_table_type;
16
17 TYPE t_psr_snapshot_record_type IS RECORD(
18 psr_snapshot_count NUMBER
19 );
20
21 TYPE psr_snapshot_table_type IS TABLE OF t_psr_snapshot_record_type INDEX BY varchar2(30);
22
23 g_psr_snapshot_table psr_snapshot_table_type;
24
25 TYPE t_instr_access_record_type IS RECORD(
26 instruction_id Number,
27 access_flag VARCHAR2(1)
28 );
29
30 TYPE instr_access_table_type IS TABLE OF t_instr_access_record_type INDEX BY BINARY_INTEGER;
31
32 g_instr_access_table instr_access_table_type;
33
34 map vc2_table;
35
36 --
37 -- UTILITY PROCEDURE #1: INIT_MAP
38 -- This procedure maps the numbers from 0 through 63 to character literals
39 -- for Base64 encoding. Initializes the Base64 mapping.
40 --
41 PROCEDURE init_map IS
42 BEGIN
43 -- do not initialize it more than once
44 IF (map.count > 0) THEN
45 RETURN;
46 END IF;
47
48 map(0) :='A'; map(1) :='B'; map(2) :='C'; map(3) :='D'; map(4) :='E';
49 map(5) :='F'; map(6) :='G'; map(7) :='H'; map(8) :='I'; map(9):='J';
50 map(10):='K'; map(11):='L'; map(12):='M'; map(13):='N'; map(14):='O';
51 map(15):='P'; map(16):='Q'; map(17):='R'; map(18):='S'; map(19):='T';
52 map(20):='U'; map(21):='V'; map(22):='W'; map(23):='X'; map(24):='Y';
53 map(25):='Z'; map(26):='a'; map(27):='b'; map(28):='c'; map(29):='d';
54 map(30):='e'; map(31):='f'; map(32):='g'; map(33):='h'; map(34):='i';
55 map(35):='j'; map(36):='k'; map(37):='l'; map(38):='m'; map(39):='n';
56 map(40):='o'; map(41):='p'; map(42):='q'; map(43):='r'; map(44):='s';
57 map(45):='t'; map(46):='u'; map(47):='v'; map(48):='w'; map(49):='x';
58 map(50):='y'; map(51):='z'; map(52):='0'; map(53):='1'; map(54):='2';
59 map(55):='3'; map(56):='4'; map(57):='5'; map(58):='6'; map(59):='7';
60 map(60):='8'; map(61):='9'; map(62):='+'; map(63):='/';
61 END init_map;
62
63
64 --
65 -- UTILITY FUNCTION#1: GET_MAP
66 -- This function returns the Base64 number equivalent for a character
67 -- literal passed. It returns values 0 through 63 for all the values that
68 -- are mapped. When it does not find the character in the table it returns
69 -- the value 64.
70 --
71 FUNCTION get_map( t IN VARCHAR2 ) RETURN NUMBER IS
72 temp NUMBER;
73 BEGIN
74 temp := 0;
75 WHILE temp < 64 LOOP
76 EXIT WHEN ( map(temp) = t );
77 temp := temp + 1;
78 END LOOP;
79 RETURN temp;
80 END get_map;
81
82 FUNCTION to_num( p_str IN VARCHAR2 ) RETURN NUMBER
83 IS
84 BEGIN
85 RETURN to_number( p_str );
86 EXCEPTION
87 WHEN OTHERS THEN
88 RETURN null;
89 END to_num;
90
91 PROCEDURE handle_exceptions
92 (
93 p_api_name IN VARCHAR2,
94 p_pkg_name IN VARCHAR2,
95 p_rollback_point IN VARCHAR2,
96 p_exception_type IN VARCHAR2,
97 x_msg_count OUT NOCOPY NUMBER,
98 x_msg_data OUT NOCOPY VARCHAR2,
99 x_return_status OUT NOCOPY VARCHAR2
100 )
101 IS
102 l_api_name VARCHAR2(30);
103 l_ora_err_code NUMBER;
104 -- the maximum length of Oracle error msg is 512
105 -- make it longer for future enh
106 l_ora_err_msg VARCHAR2(2000);
107 l_error_reason VARCHAR2(80);
108 BEGIN
109
110 iby_debug_pub.add('entered IBY_UTILITY_PVT.handle_exceptions()');
111
112 l_api_name := UPPER(p_api_name);
113
114 -- rollback to the savepoint
115 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(p_rollback_point);
116
117 IF p_exception_type = iby_utility_pvt.g_expt_err
118 THEN
119 x_return_status := FND_API.G_RET_STS_ERROR;
120 l_error_reason := 'FND_API.G_RET_STS_ERROR';
121 ELSIF p_exception_type = iby_utility_pvt.g_expt_unexp_err
122 THEN
123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
124 l_error_reason := 'FND_API.G_RET_STS_UNEXP_ERROR';
125 ELSIF p_exception_type = iby_utility_pvt.g_expt_otr_err
126 THEN
127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
128 l_error_reason := 'G_EXC_OTHERS';
129
130 l_ora_err_code := SQLCODE;
131 l_ora_err_msg := SQLERRM;
132
133 iby_debug_pub.add('Oracle error in ' || p_pkg_name || '.' || p_api_name || ', code: '|| l_ora_err_code);
134 iby_debug_pub.add('Oracle error in ' || p_pkg_name || '.' || p_api_name || ', msg : '|| l_ora_err_msg);
135 --dbms_output.put_line('Oracle error, code: '|| l_ora_err_code);
136 --dbms_output.put_line('Oracle error, msg : '|| l_ora_err_msg);
137
138 fnd_message.set_name('IBY', 'IBY_G_SQL_ERR');
139 fnd_message.set_token('API', p_pkg_name || '.' || p_api_name);
140 fnd_message.set_token('SQLCODE', l_ora_err_code);
141 fnd_message.set_token('SQLERRM', l_ora_err_msg);
142 FND_MSG_PUB.ADD;
143
144 END IF;
145
146 FND_MSG_PUB.Count_And_Get(
147 p_count => x_msg_count,
148 p_data => x_msg_data);
149
150 iby_debug_pub.add('x_msg_count: ' || x_msg_count);
151 iby_debug_pub.add('x_msg_data: ' || x_msg_data);
152 iby_debug_pub.add('x_return_status: ' || x_return_status);
153
154 -- finally add the exception msg to debug log
155 /*
156 iby_debug_pub.add_return_messages(
157 p_count => x_msg_count,
158 p_data => x_msg_data,
159 p_reason => l_error_reason
160 );
161 */
162
163 END Handle_Exceptions;
164
165 PROCEDURE handleException
166 (
167 p_err_msg IN VARCHAR2,
168 p_err_code IN VARCHAR2
169 )
170 IS
171 l_ibycode_start NUMBER;
172 l_ibycode_end NUMBER;
173 l_index NUMBER;
174 l_msg_len NUMBER;
175 l_val_concat NUMBER;
176 l_tok_concat NUMBER;
177
178 l_err_code VARCHAR2(200);
179 l_token_name VARCHAR2(200);
180 l_token_val VARCHAR2(200);
181 BEGIN
182 l_msg_len := LENGTH(p_err_msg);
183 --
184 -- check if 'IBY_' is present in the error message; if so
185 -- then the exception was internally generated
186 --
187 l_ibycode_start := INSTR(p_err_msg,iby_utility_pvt.C_ERRCODE_PREFIX);
188
189 IF (l_ibycode_start > 0) THEN
190
191 l_ibycode_end:=INSTR(p_err_msg,iby_utility_pvt.C_TOKEN_CONCATENATOR)-1;
192 IF (l_ibycode_end < 1) THEN
193 l_ibycode_end := l_msg_len;
194 END IF;
195 l_err_code := SUBSTR(p_err_msg,l_ibycode_start,l_ibycode_end-l_ibycode_start+1);
196 FND_MESSAGE.SET_NAME('IBY',l_err_code);
197
198 -- +1 to go the position of the token concatenator; +1 again to
199 -- to go past it to the beginning of the token name
200 --
201 l_index:= l_ibycode_end+1+1;
202 WHILE l_index < l_msg_len LOOP
203 l_val_concat := INSTR(p_err_msg,iby_utility_pvt.C_TOKEN_VAL_CONCATENATOR,l_index);
204 IF (l_val_concat < 1) THEN
205 EXIT;
206 END IF;
207 l_tok_concat := INSTR(p_err_msg,iby_utility_pvt.C_TOKEN_CONCATENATOR,l_index);
208 IF (l_tok_concat < 1) THEN
209 -- the error message is usually of the form:
210 --
211 -- ORA-2000: IBY_XXXX#TOKENNAME=TOKENVAL#...<new line>
212 -- ORA-06512 ...
213 --
214 -- we wish to ignore the text after the
215 -- last token on the first line
216 --
217
218 -- note the -1 is for the newline character after the last
219 -- token value
220 --
221 l_tok_concat := INSTR(p_err_msg,'ORA',l_index)-1;
222 IF (l_tok_concat < 1) THEN
223 -- +1 so that the last character of the token value is
224 -- included
225 l_tok_concat := l_msg_len +1;
226 END IF;
227 END IF;
228
229 l_token_name := SUBSTR(p_err_msg,l_index,(l_val_concat-1)-l_index+1);
230 l_token_val := SUBSTR(p_err_msg,l_val_concat+1,(l_tok_concat-1)-(l_val_concat+1)+1);
231 FND_MESSAGE.SET_TOKEN(l_token_name,l_token_val);
232 --
233 -- go +1 character past the token concatenator
234 --
235 l_index := l_tok_concat + 1;
236 END LOOP;
237 FND_MSG_PUB.ADD;
238 --
239 -- no IBY message found; simply put the exact text of
240 -- the exception into the FND_MSG stack
241 --
242 ELSE
243 FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
244 FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT',p_err_msg);
245 FND_MSG_PUB.ADD;
246 END IF;
247 END handleException;
248
249
250 PROCEDURE get_property
251 (
252 p_name IN VARCHAR2,
253 x_val OUT NOCOPY VARCHAR2
254 )
255 IS
256 BEGIN
257 FND_PROFILE.get(p_name,x_val);
258 END get_property;
259
260
261 PROCEDURE set_property
262 (
263 p_name IN VARCHAR2,
264 p_val IN VARCHAR2
265 )
266 IS
267 l_rtn_sts BOOLEAN;
268 BEGIN
269
270 l_rtn_sts := FND_PROFILE.save(p_name,p_val,'SITE');
271 --
272 -- 2nd put is so that the value is visible in the
273 -- current session
274 --
275 FND_PROFILE.put(p_name,p_val);
276 COMMIT;
277
278 END set_property;
279
280
281
282 --
283 -- Name: get_jtf_property
284 -- Args: p_name => property name
285 -- Outs: x_val => property value
286 --
287 -- Notes: gets an IBY property from old JTF Property manager tables.
288 -- only the first element of the value list is returned.
289 -- this function is used by ibyprupg.sql
290 --
291 FUNCTION get_jtf_property(p_name IN VARCHAR2)
292 RETURN VARCHAR2 IS
293
294 p_api_version_number NUMBER := 1.0;
295 p_init_msg_list VARCHAR2(2000) := FND_API.G_FALSE;
296 p_application_id NUMBER := 673;
297 p_profile_id NUMBER;
298 p_profile_name VARCHAR2(2000) := 'JTF_PROPERTY_MANAGER_DEFAULT_1';
299 p_perz_data_id NUMBER;
300 p_perz_data_type VARCHAR2(2000) := 'JTF';
301
302 x_return_status VARCHAR2(2000);
303 x_msg_count NUMBER := 0;
304 x_msg_data VARCHAR2(2000);
305 x_perz_data_id NUMBER;
306 x_perz_data_name VARCHAR2(2000);
307 x_perz_data_type VARCHAR2(2000);
308 x_perz_data_desc VARCHAR2(2000);
309 x_data_attrib_tbl JTF_PERZ_DATA_PUB.DATA_ATTRIB_TBL_TYPE;
310
311 p_temp_var VARCHAR2(2);
312 x_val VARCHAR2(2000);
313
314 BEGIN
315
316 JTF_PERZ_DATA_PUB.GET_PERZ_DATA( p_api_version_number,
317 p_init_msg_list,
318 p_application_id,
319 p_profile_id,
320 p_profile_name,
321 p_perz_data_id,
322 p_name,
323 p_perz_data_type,
324 x_perz_data_id,
325 x_perz_data_name,
326 x_perz_data_type,
327 x_perz_data_desc,
328 x_data_attrib_tbl,
329 x_return_status,
330 x_msg_count,
331 x_msg_data
332 );
333
334 IF ((x_return_status IS NULL OR
335 x_return_status <> FND_API.G_RET_STS_SUCCESS) OR
336 (x_data_attrib_tbl.count<1)) THEN
337 x_val := NULL;
338 ELSE
339 x_val := x_data_attrib_tbl(1).attribute_value;
340 END IF;
341
342 return x_val;
343
344 END get_jtf_property;
345
346
347 --
348 --
349 FUNCTION encode64(s IN VARCHAR2) RETURN VARCHAR2 IS
350 r RAW(32767);
351 i pls_integer;
352 x pls_integer;
353 y pls_integer;
354 v VARCHAR2(32767);
355 BEGIN
356
357 init_map;
358 r := UTL_RAW.CAST_TO_RAW( s );
359
360 -- For every 3 bytes, split them into 4 6-bit units and map them to
361 -- the Base64 characters
362 i := 1;
363 WHILE ( i + 2 <= utl_raw.length(r) ) LOOP
364 x := to_number(utl_raw.substr(r, i, 1), '0X') * 65536 +
365 to_number(utl_raw.substr(r, i + 1, 1), '0X') * 256 +
366 to_number(utl_raw.substr(r, i + 2, 1), '0X');
367 y := floor(x / 262144); v := v || map(y); x := x - y * 262144;
368 y := floor(x / 4096); v := v || map(y); x := x - y * 4096;
369 y := floor(x / 64); v := v || map(y); x := x - y * 64;
370 v := v || map(x);
371 i := i + 3;
372 END LOOP;
373
374 -- Process the remaining bytes that has fewer than 3 bytes.
375 --when last two bytes are '='
376 IF ( utl_raw.length(r) - i = 0) THEN
377 x := to_number(utl_raw.substr(r, i, 1), '0X');
378 y := floor(x / 4); v := v || map(y); x := x - y * 4;
379 x := x * 16; v := v || map(x);
380 v := v || '==';
381 --when last one byte is '='
382 ELSIF ( utl_raw.length(r) - i = 1) THEN
383 x := to_number(utl_raw.substr(r, i, 1), '0X') * 256 +
384 to_number(utl_raw.substr(r, i + 1, 1), '0X');
385 y := floor(x / 1024); v := v || map(y); x := x - y * 1024;
386 y := floor(x / 16); v := v || map(y); x := x - y * 16;
387 x := x * 4; v := v || map(x);
388 v := v || '=';
389 END IF;
390
391 RETURN v;
392
393 END encode64;
394
395 --
396 --
397 FUNCTION decode64(s IN VARCHAR2) RETURN VARCHAR2 IS
398 i pls_integer;
399 x pls_integer;
400 y pls_integer;
401 iTh pls_integer;
402 iPlusOne pls_integer;
403 iPlusTwo pls_integer;
404 iPlusThree pls_integer;
405 v VARCHAR2(32767);
406 BEGIN
407 init_map;
408 i := 1;
409 v := '';
410 --Processing 4 bytes at a time. For every 4 bytes, convert them into 3 bytes.
411 WHILE ( i + 3 <= LENGTH(s) ) LOOP
412 iTh := get_map( SUBSTR( s, i, 1) );
413 iPlusOne := get_map( SUBSTR( s, i + 1, 1) );
414 iPlusTwo := get_map( SUBSTR( s, i + 2, 1) );
415 iPlusThree := get_map( SUBSTR( s, i + 3, 1) );
416
417 --when the last two bytes equal '='
418 IF( iPlusTwo = 64 ) THEN
419 x := iTh * 64 + iPlusOne;
420 x := floor(x/16);
421 v := v || fnd_global.local_chr( x );
422
423 --when the last byte equals '='
424 ELSIF( iPlusThree = 64 ) THEN
425 x := iTh * 4096 + iPlusOne * 64 + iPlusTwo;
426 y := floor(x/1024);
427 v := v || fnd_global.local_chr( y );
428 x := x - y * 1024;
429 x := floor(x/4);
430 v := v || fnd_global.local_chr( x );
431
432 --when all the bytes hold values to be converted.
433 ELSE
434 x := iTh * 262144 + iPlusOne * 4096 + iPlusTwo * 64 + iPlusThree;
435 y := floor(x/65536);
436 v := v || fnd_global.local_chr( y );
437 x := x - y * 65536;
438 y := floor(x/256);
439 v := v || fnd_global.local_chr( y );
440 x := x - y * 256;
441 v := v || fnd_global.local_chr( x );
442 END IF;
443 i := i + 4;
444 END LOOP;
445
446 RETURN v;
447
448 END decode64;
449
450 --
451 --
452 FUNCTION get_local_nls
453 RETURN VARCHAR2
454 IS
455 BEGIN
456
457 return userenv('LANGUAGE');
458
459 EXCEPTION WHEN others THEN
460 return NULL;
461 END get_local_nls;
462
463 --
464 --
465 FUNCTION get_nls_charset( p_nls VARCHAR2 )
466 RETURN VARCHAR2
467 IS
468 l_charset_index INTEGER;
469
470 -- charset value seperator in NLSLang parameters
471 --
472 c_charset_seperator CONSTANT VARCHAR2(1) := '.';
473 BEGIN
474
475 IF (p_nls IS NULL) THEN
476 return null;
477 END IF;
478
479 l_charset_index := INSTR(p_nls,c_charset_seperator);
480
481 IF ( l_charset_index > 0 ) THEN
482 return SUBSTR(p_nls,l_charset_index+1);
483 ELSE
484 return NULL;
485 END IF;
486
487 END get_nls_charset;
488
489
490 /* ========================================================================
491 -- Function Name: MAKE_ASCII
492 --
493 -- Purpose: Function to convert a string with possibly
494 -- non-ASCII characters to ASCII. Internally, this
495 -- function calls the SQL CONVERT function.
496 --
497 -- The usage of this function will be to
498 -- substitute certain 8-bit chars in European
499 -- languages by their closest ASCII (7-bit)
500 -- equivalent.
501 --
502 -- Example:
503 -- {a with various accent marks} -> a
504 -- {e with various accent marks} -> e
505 -- and so on.
506 --
507 -- Sometimes, no straightforward conversion is possible
508 -- because there is no equivalent in ASCII for a
509 -- particular character. In this case the SQL CONVERT
510 -- function will substitute a '?' for that character.
511 --
512 -- MAKE_ASCII("Senaj(o with two dots)ki") returns "Senajoki"
513 -- MAKE_ASCII("Senaj(o with a slash)ki") returns "Senaj?ki"
514 --
515 -- Parameters:
516 -- IN 1) p_from_text VARCHAR2
517 -- The raw text which might contain accented
518 -- or otherwise non-ASCII characters.
519 --
520 -- OUT None
521 --
522 -- RETURN VARCHAR2 - The converted text with only ASCII characters.
523 -- =======================================================================*/
524
525 FUNCTION MAKE_ASCII(
526 p_from_text IN VARCHAR2
527 ) RETURN VARCHAR2 IS
528
529 l_converted_text VARCHAR2(300);
530
531 BEGIN
532
533 --
534 -- Call the SQL CONVERT method to convert given string
535 -- to US ASCII format.
536 --
537 -- See http://st-doc/8.0/817/server.817/a85397/function.htm#77039
538 -- for CONVERT syntax
539 --
540 l_converted_text := convert(p_from_text, 'US7ASCII');
541
542 RETURN l_converted_text;
543
544 EXCEPTION
545 WHEN OTHERS THEN
546 RETURN p_from_text;
547
548 END MAKE_ASCII;
549
550 FUNCTION get_call_exec
551 (
552 p_pkg_name VARCHAR2,
553 p_function_name VARCHAR2,
554 p_params JTF_VARCHAR2_TABLE_200
555 )
556 RETURN VARCHAR2
557 IS
558 l_call VARCHAR2(3000);
559 l_bind_counter NUMBER := 1;
560 BEGIN
561
562 l_call :='CALL '|| p_pkg_name || '.' || p_function_name || '(';
563
564 FOR i IN p_params.FIRST..p_params.LAST LOOP
565 IF (NOT (i = p_params.FIRST)) THEN
566 l_call := l_call || ',';
567 END IF;
568
569 IF (p_params(i) IS NULL) THEN
570 l_call := l_call || ':' || TO_CHAR(l_bind_counter);
571 l_bind_counter := l_bind_counter+1;
572 ELSE
573 l_call := l_call || p_params(i);
574 END IF;
575 END LOOP;
576
577 l_call := l_call || ')';
578
579 return l_call;
580 END get_call_exec;
581
582 PROCEDURE set_view_param
583 (
584 p_name iby_view_parameters_gt.name%TYPE,
585 p_val iby_view_parameters_gt.value%TYPE
586 )
587 IS
588 BEGIN
589
590 INSERT INTO iby_view_parameters_gt
591 (name,value,created_by,creation_date,last_updated_by,last_update_date,
592 last_update_login,object_version_number)
593 VALUES
594 (p_name,p_val,fnd_global.user_id,sysdate,fnd_global.user_id,
595 sysdate,fnd_global.login_id,1);
596
597 -- no commit, as data deleted at the end of the current
598 -- transaction
599 END set_view_param;
600
601 FUNCTION get_view_param( p_name iby_view_parameters_gt.name%TYPE )
602 RETURN iby_view_parameters_gt.value%TYPE
603 IS
604 l_val iby_view_parameters_gt.value%TYPE;
605
606 CURSOR c_param_val
607 (ci_name iby_view_parameters_gt.name%TYPE)
608 IS
609 SELECT value
610 FROM iby_view_parameters_gt
611 WHERE (name=ci_name);
612
613 BEGIN
614
615 IF (c_param_val%ISOPEN) THEN
616 CLOSE c_param_val;
617 END IF;
618
619 OPEN c_param_val(p_name);
620 FETCH c_param_val INTO l_val;
621
622 IF (c_param_val%NOTFOUND) THEN
623 l_val := NULL;
624 END IF;
625 CLOSE c_param_val;
626
627 RETURN l_val;
628
629 END get_view_param;
630
631 FUNCTION check_lookup_val( p_val IN VARCHAR2, p_lookup IN VARCHAR2 )
632 RETURN BOOLEAN
633 IS
634 l_count NUMBER;
635 BEGIN
636
637 SELECT count(lookup_code)
638 INTO l_count
639 FROM fnd_lookups
640 WHERE (lookup_type = p_lookup)
641 AND (lookup_code = p_val)
642 AND (enabled_flag = 'Y')
643 AND (NVL(end_date_active,SYSDATE-10) < SYSDATE);
644
645 IF (l_count<1) THEN
646 RETURN false;
647 ELSE
648 RETURN true;
649 END IF;
650
651 END check_lookup_val;
652
653 FUNCTION validate_party_id(p_party_id IN hz_parties.party_id%TYPE)
654 RETURN BOOLEAN
655 IS
656 l_count NUMBER;
657
658 CURSOR c_party(ci_party_id IN hz_parties.party_id%TYPE)
659 IS
660 SELECT COUNT(party_id)
661 INTO l_count
662 FROM hz_parties
663 WHERE party_id = ci_party_id;
664 BEGIN
665 IF (c_party%ISOPEN) THEN CLOSE c_party; END IF;
666
667 OPEN c_party(p_party_id);
668 FETCH c_party INTO l_count;
669 CLOSE c_party;
670
671 RETURN (l_count>0);
672 END validate_party_id;
673
674 FUNCTION validate_app_id(p_app_id IN fnd_application.application_id%TYPE)
675 RETURN BOOLEAN
676 IS
677 l_count NUMBER;
678
679 CURSOR c_app(ci_app_id IN fnd_application.application_id%TYPE)
680 IS
681 SELECT count(*)
682 FROM fnd_application
683 WHERE (application_id = ci_app_id);
684
685 BEGIN
686 IF (c_app%ISOPEN) THEN CLOSE c_app; END IF;
687
688 OPEN c_app(p_app_id);
689 FETCH c_app INTO l_count;
690 CLOSE c_app;
691
692 IF (l_count<1) THEN
693 RETURN FALSE;
694 ELSE
695 RETURN TRUE;
696 END IF;
697 END validate_app_id;
698
699
700 FUNCTION validate_territory
701 ( p_territory IN fnd_territories.territory_code%TYPE )
702 RETURN BOOLEAN
703 IS
704
705 l_territory fnd_territories.territory_code%TYPE;
706
707 CURSOR c_terr( ci_code IN fnd_territories.territory_code%TYPE )
708 IS
709 SELECT territory_code
710 FROM fnd_territories
711 WHERE (territory_code = ci_code)
712 AND (NVL(obsolete_flag,'N') = 'N');
713
714 BEGIN
715 IF (c_terr%ISOPEN) THEN CLOSE c_terr; END IF;
716
717 OPEN c_terr(p_territory);
718 FETCH c_terr INTO l_territory;
719 CLOSE c_terr;
720
721 RETURN (NOT l_territory IS NULL);
722 END validate_territory;
723
724
725 FUNCTION is_trivial(p_string VARCHAR2)
726 RETURN BOOLEAN
727 IS
728 BEGIN
729 IF (p_string IS NULL) THEN RETURN TRUE;
730 ELSIF (RTRIM(LTRIM(p_string)) IS NULL) THEN RETURN TRUE;
731 ELSE RETURN FALSE; END IF;
732 END is_trivial;
733
734 FUNCTION validate_organization(p_org_id IN iby_trxn_summaries_all.org_id%TYPE,
735 p_org_type IN iby_trxn_summaries_all.org_type%TYPE)
736 RETURN VARCHAR2
737 IS
738
739 l_org_id NUMBER;
740 l_return_value VARCHAR2(1) := 'N';
741
742 BEGIN
743
744 BEGIN
745 SELECT organization_id
746 INTO l_org_id
747 FROM hr_operating_units
748 WHERE organization_id = p_org_id;
749
750 EXCEPTION
751 WHEN no_data_found THEN
752 l_org_id := NULL;
753
754 END;
755
756 IF (l_org_id IS NOT NULL) THEN
757 l_return_value := 'Y';
758 END IF;
759
760 RETURN l_return_value;
761
762 END validate_organization;
763
764
765 PROCEDURE validate_pmt_channel_code(p_instrument_type IN iby_creditcard.instrument_type%TYPE,
766 p_payment_channel_code IN OUT NOCOPY iby_trxn_summaries_all.payment_channel_code%TYPE,
767 p_valid OUT NOCOPY VARCHAR2)
768 IS
769 l_payment_channel_code iby_trxn_summaries_all.payment_channel_code%TYPE;
770 l_return_value VARCHAR2(1) := 'N';
771
772 BEGIN
773
774 BEGIN
775 SELECT payment_channel_code
776 INTO l_payment_channel_code
777 FROM iby_fndcpt_pmt_chnnls_b
778 WHERE instrument_type = p_instrument_type
779 AND (payment_channel_code = p_payment_channel_code
780 OR p_payment_channel_code is null);
781
782 EXCEPTION
783 WHEN no_data_found THEN
784 l_payment_channel_code := NULL;
785
786 WHEN too_many_rows THEN
787 IF (p_instrument_type = 'BANKACCOUNT') THEN
788 l_payment_channel_code := 'BANK_ACCT_XFER';
789
790 ELSIF (p_instrument_type = 'MANUAL') THEN
791 l_payment_channel_code := 'CHECK';
792
793 ELSE
794 l_payment_channel_code := NULL;
795 END IF;
796
797 END;
798
799 IF (l_payment_channel_code IS NOT NULL AND
800 l_payment_channel_code = NVL(p_payment_channel_code, l_payment_channel_code)) THEN
801 l_return_value := 'Y';
802 END IF;
803
804 p_payment_channel_code := l_payment_channel_code;
805 p_valid := l_return_value;
806
807 END validate_pmt_channel_code;
808
809 -- This function is moved from appayutb.pls to provide the count of requests in 'Processing', 'Terminated' in Payments Dashboard.
810 FUNCTION get_psr_snapshot_count(p_snapshot_code IN VARCHAR2)
811 RETURN NUMBER IS
812
813 l_status_code FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE;
814
815 l_ret_val NUMBER;
816 l_count1 NUMBER;
817 l_count2 NUMBER;
818 l_count3 NUMBER;
819 l_count4 NUMBER;
820 BEGIN
821 IF(g_psr_snapshot_table.EXISTS(p_snapshot_code) AND g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count IS NOT NULL) THEN
822 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
823 iby_debug_pub.add(debug_msg => 'IBY_UTILITY_PVT.get_psr_snapshot_count :Getting from the cache',
824 debug_level => FND_LOG.LEVEL_STATEMENT,
825 module => 'IBY_UTILITY_PVT.get_psr_snapshot_count');
826 END IF;
827 l_ret_val:= g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count;
828 ELSE
829 IF (p_snapshot_code = 'NEED_ACTION_BY_ME') THEN
830
831 SELECT
832 count(*)
833 INTO l_count1
834 FROM iby_pay_service_requests iby
835 WHERE iby.calling_app_id = 200
836 AND iby.process_type = 'STANDARD'
837 AND
838 ( iby.payment_service_request_status IN (
839 'INFORMATION_REQUIRED',
840 'PENDING_REVIEW_DOC_VAL_ERRORS',
841 'PENDING_REVIEW_PMT_VAL_ERRORS',
842 'PENDING_REVIEW')
843 OR
844 (
845 iby.payment_service_request_status = 'PAYMENTS_CREATED'
846 AND EXISTS
847 (
848 select 'NEED_ACTION_BY_ME'
849 from iby_payments_all pmt, iby_pay_instructions_all instr
850 where iby.payment_service_request_id = pmt.payment_service_request_id
851 and instr.payment_instruction_id = pmt.payment_instruction_id
852 and instr.payment_instruction_status IN ('CREATION_ERROR',
853 'FORMATTED_READY_TO_TRANSMIT',
854 'TRANSMISSION_FAILED',
855 'FORMATTED_READY_FOR_PRINTING',
856 'SUBMITTED_FOR_PRINTING',
857 'CREATED_READY_FOR_PRINTING',
858 'CREATED_READY_FOR_FORMATTING',
859 'FORMATTED',
860 'CREATED')
861 and check_user_access(instr.payment_instruction_id) = 'Y'
862 )
863 )
864 );
865
866
867
868 SELECT count(*)
869 INTO l_count2
870 FROM ap_inv_selection_criteria_all ap
871 WHERE ap.status IN ('REVIEW', 'MISSING RATES' )
872 AND NOT EXISTS ( SELECT 'NEED_ACTION_BY_ME'
873 FROM iby_pay_service_requests iby
874 WHERE iby.calling_app_id = 200
875 AND iby.call_app_pay_service_req_code =
876 ap.checkrun_name);
877
878
879 l_ret_val := l_count1 + l_count2;
880 g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count := l_ret_val;
881
882 ELSIF p_snapshot_code = 'PROCESSING' THEN
883 /*Modified the query for the Bug 7560766
884 Added a condition to identify the PPRs which
885 are waiting for the user action*/
886 SELECT
887 count(*)
888 INTO l_count1
889 FROM iby_pay_service_requests iby
890 WHERE iby.calling_app_id = 200
891 AND iby.process_type = 'STANDARD'
892 AND
893 (
894 iby.payment_service_request_status IN ('INSERTED', 'SUBMITTED',
895 'ASSIGNMENT_COMPLETE',
896 'DOCUMENTS_VALIDATED',
897 'RETRY_DOCUMENT_VALIDATION',
898 'RETRY_PAYMENT_CREATION')
899 OR
900 (
901 iby.payment_service_request_status IN ('PAYMENTS_CREATED')
902 AND EXISTS
903 (SELECT 'PROCESSING'
904 FROM iby_payments_all pmt
905 WHERE
906 pmt.payment_service_request_id = iby.payment_service_request_id
907 AND pmt.payment_status NOT IN('REMOVED', 'VOID', 'VOID_BY_SETUP', 'VOID_BY_OVERFLOW', 'REMOVED_PAYMENT_STOPPED',
908 'REMOVED_DOCUMENT_SPOILED', 'REMOVED_INSTRUCTION_TERMINATED', 'REMOVED_REQUEST_TERMINATED', 'ISSUED', 'TRANSMITTED', 'REJECTED')
909 AND pmt.payments_complete_flag <> 'Y'
910 AND NOT EXISTS
911 (SELECT 'NEED_ACTION'
912 FROM iby_pay_instructions_all inst
913 WHERE pmt.payment_instruction_id = inst.payment_instruction_id
914 AND inst.payment_instruction_status IN('CREATION_ERROR',
915 'FORMATTED_READY_TO_TRANSMIT',
916 'TRANSMISSION_FAILED',
917 'FORMATTED_READY_FOR_PRINTING',
918 'SUBMITTED_FOR_PRINTING',
919 'CREATED_READY_FOR_PRINTING',
920 'CREATED_READY_FOR_FORMATTING',
921 'FORMATTED',
922 'CREATED',
923 'FORMATTED_ELECTRONIC'))
924 )
925 )
926 );
927
928
929 SELECT count(*)
930 INTO l_count2
931 FROM ap_inv_selection_criteria_all ap
932 WHERE ap.status IN ('UNSTARTED', 'SELECTING', 'CANCELING',
933 'CALCULATING', 'SELECTED')
934 AND NOT EXISTS ( SELECT 'PROCESSING'
935 FROM iby_pay_service_requests iby
936 WHERE iby.calling_app_id = 200
937 AND iby.call_app_pay_service_req_code =
938 ap.checkrun_name);
939
940 l_ret_val := l_count1 + l_count2;
941 g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count := l_ret_val;
942
943 ELSIF p_snapshot_code = 'USER_TERMINATED' THEN
944 SELECT count(*)
945 INTO l_count1
946 FROM ap_inv_selection_criteria_all ap
947 WHERE EXISTS ( SELECT 'IBY USER_TERMINATED'
948 FROM iby_pay_service_requests iby
949 WHERE iby.calling_app_id = 200
950 AND iby.call_app_pay_service_req_code =
951 ap.checkrun_name
952 AND iby.payment_service_request_status IN
953 ('TERMINATED'))
954 AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
955
956 SELECT count(*)
957 INTO l_count2
958 FROM ap_inv_selection_criteria_all ap
959 WHERE ap.status IN ('CANCELED', 'CANCELLED NO PAYMENTS')
960 AND TRUNC(ap.creation_date) =TRUNC(sysdate)
961 AND NOT EXISTS ( SELECT 'AP USER_TERMINATED'
962 FROM iby_pay_service_requests iby
963 WHERE iby.calling_app_id = 200
964 AND iby.call_app_pay_service_req_code =
965 ap.checkrun_name)
966 AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
967
968 l_ret_val := l_count1 + l_count2;
969 g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count := l_ret_val;
970
971 ELSIF p_snapshot_code = 'PROGRAM_ERRORS' THEN
972
973 SELECT count(*)
974 INTO l_count1
975 FROM ap_inv_selection_criteria_all ap
976 WHERE EXISTS ( SELECT 'PROGRAM ERRORS'
977 FROM iby_pay_service_requests iby
978 WHERE iby.calling_app_id = 200
979 AND iby.call_app_pay_service_req_code =
980 ap.checkrun_name
981 AND iby.payment_service_request_status IN
982 ('PENDING_REVIEW_DOC_VAL_ERRORS',
983 'PENDING_REVIEW_PMT_VAL_ERRORS'))
984 AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
985
986 l_ret_val := l_count1;
987 g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count := l_ret_val;
988
989 ELSIF p_snapshot_code = 'COMPLETED' THEN
990
991 SELECT count(*)
992 INTO l_count1
993 FROM ap_inv_selection_criteria_all ap
994 WHERE EXISTS ( SELECT 'COMPLETED'
995 FROM iby_pay_service_requests iby
996 WHERE iby.calling_app_id = 200
997 AND iby.call_app_pay_service_req_code =
998 ap.checkrun_name
999 AND iby.payment_service_request_status IN
1000 ('PAYMENTS_CREATED')
1001 AND AP_PAYMENT_UTIL_PKG.get_payment_status_flag(iby.payment_service_request_id) = 'Y')
1002 AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
1003
1004 l_ret_val := l_count1;
1005 g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count := l_ret_val;
1006
1007 ELSIF p_snapshot_code = 'TOTAL' THEN
1008
1009 --The total value is calculated in the UI
1010 NULL;
1011
1012 END IF;
1013 END IF;
1014 RETURN l_ret_val;
1015
1016 END;
1017
1018 -- This function is moved from appayutb.pls to provide the count of requests in 'Processing', 'Terminated' in Payments Dashboard.
1019 /* Bug Number: 7279395
1020 * Caching is implemented based on the psr id. Hence pages or procedures
1021 * which are accessing this function should take the responsibility to
1022 * initialize g_psr_table by calling the initialize procedure
1023 */
1024 FUNCTION get_payment_status_flag(p_psr_id IN NUMBER)
1025 RETURN VARCHAR2 IS
1026
1027 l_payment_status_flag VARCHAR2(1);
1028 l_total_pmt_count NUMBER;
1029 l_pmt_complete_count NUMBER;
1030
1031
1032 BEGIN
1033
1034 IF ( p_psr_id IS NOT NULL ) THEN
1035
1036 IF(g_psr_table.EXISTS(p_psr_id) AND g_psr_table(p_psr_id).payment_status_flag IS NOT NULL) THEN
1037 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1038 iby_debug_pub.add(debug_msg => 'IBY_UTILITY_PVT.get_payment_status_flag :Getting from the cache',
1039 debug_level => FND_LOG.LEVEL_STATEMENT,
1040 module => 'IBY_UTILITY_PVT.get_payment_status_flag');
1041 END IF;
1042 l_payment_status_flag:= g_psr_table(p_psr_id).payment_status_flag;
1043 ELSE
1044
1045 /*Bug 7248943*/
1046 select count (*) total_pmt_count,
1047 count(case when PAYMENTS_COMPLETE_FLAG = 'Y' then 1 else null
1048 end) pmt_complete_count
1049 into l_total_pmt_count, l_pmt_complete_count /*Bug 7248943*/
1050 FROM iby_payments_all
1051 WHERE payment_service_request_id = p_psr_id
1052 AND payment_status NOT IN ('REMOVED', 'VOID_BY_SETUP',
1053 'VOID_BY_OVERFLOW', 'REMOVED_PAYMENT_STOPPED',
1054 'REMOVED_DOCUMENT_SPOILED',
1055 'REMOVED_INSTRUCTION_TERMINATED',
1056 'REMOVED_REQUEST_TERMINATED',
1057 'REJECTED', -- Bug 6897223- new statuses added
1058 'FAILED_BY_CALLING_APP',
1059 'FAILED_BY_REJECTION_LEVEL',
1060 'FAILED_VALIDATION',
1061 'INSTRUCTION_FAILED_VALIDATION'); --Bug 6686639
1062
1063 IF l_total_pmt_count > 0 THEN
1064 --
1065 /*Removed query for Bug 7248943*/
1066
1067 IF ( l_total_pmt_count = l_pmt_complete_count) THEN
1068 l_payment_status_flag := 'Y';
1069 g_psr_table(p_psr_id).payment_status_flag := l_payment_status_flag;
1070 ELSIF ((l_total_pmt_count > l_pmt_complete_count) AND
1071 (l_pmt_complete_count <> 0))THEN
1072 l_payment_status_flag := 'P';
1073 g_psr_table(p_psr_id).payment_status_flag := l_payment_status_flag;
1074 ELSIF ((l_total_pmt_count > l_pmt_complete_count) AND
1075 (l_pmt_complete_count = 0)) THEN
1076 l_payment_status_flag := 'N';
1077 g_psr_table(p_psr_id).payment_status_flag := l_payment_status_flag;
1078 END IF;
1079 --
1080 ELSE
1081 --
1082
1083 l_payment_status_flag := 'N';
1084 g_psr_table(p_psr_id).payment_status_flag := l_payment_status_flag;
1085
1086 END IF;
1087 END IF;
1088 ELSE
1089 l_payment_status_flag := 'N';
1090
1091 END IF;
1092
1093
1094 RETURN l_payment_status_flag;
1095
1096 END get_payment_status_flag;
1097
1098 -- This function is moved from appayutb.pls to provide the count of requests in 'Processing', 'Terminated' in Payments Dashboard.
1099 /* Bug Number: 7279395
1100 * Caching is implemented based on the psr id. Hence pages or procedures
1101 * which are accessing this function should take the responsibility to
1102 * initialize g_psr_table by calling the initialize procedure
1103 */
1104 FUNCTION get_psr_status(p_psr_id IN NUMBER,
1105 p_psr_status IN VARCHAR2)
1106 RETURN VARCHAR2 IS
1107
1108 l_psr_status VARCHAR2(30);
1109 l_total_pmt_count NUMBER;
1110 l_instr_count NUMBER;
1111 l_pmt_terminate_count NUMBER;
1112 l_pmt_spoil_skip NUMBER;
1113
1114 BEGIN
1115 IF(g_psr_table.EXISTS(p_psr_id) AND g_psr_table(p_psr_id).psr_status IS NOT NULL) THEN
1116 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1117 iby_debug_pub.add(debug_msg => 'IBY_UTILITY_PVT.get_psr_status :Getting from the cache',
1118 debug_level => FND_LOG.LEVEL_STATEMENT,
1119 module => 'IBY_UTILITY_PVT.get_psr_status');
1120 END IF;
1121 l_psr_status:= g_psr_table(p_psr_id).psr_status;
1122 ELSE
1123
1124 IF p_psr_status <> 'PAYMENTS_CREATED' THEN
1125
1126 IF p_psr_status IN ('INSERTED', 'SUBMITTED',
1127 'ASSIGNMENT_COMPLETE',
1128 'DOCUMENTS_VALIDATED',
1129 'RETRY_DOCUMENT_VALIDATION',
1130 'RETRY_PAYMENT_CREATION') THEN
1131
1132 l_psr_status := 'BUILDING';
1133 g_psr_table(p_psr_id).psr_status := l_psr_status;
1134 RETURN l_psr_status;
1135 --
1136 END IF;
1137 --
1138 RETURN p_psr_status;
1139 END IF;
1140
1141 /*Bug 7248943*/
1142 select count (*) total_pmt_count,
1143 count(case when payment_instruction_id IS NOT NULL then 1
1144 else null end) instr_count,
1145 count(case when PAYMENT_STATUS IN ('REMOVED_INSTRUCTION_TERMINATED',
1146 'REMOVED_REQUEST_TERMINATED',
1147 'VOID',
1148 'REMOVED',
1149 'REMOVED_PAYMENT_STOPPED',
1150 'VOID_BY_SETUP',
1151 'VOID_BY_OVERFLOW',
1152 'REMOVED_DOCUMENT_SPOILED',
1153 'REJECTED',
1154 'FAILED_BY_CALLING_APP',
1155 'FAILED_BY_REJECTION_LEVEL',
1156 'FAILED_VALIDATION',
1157 'INSTRUCTION_FAILED_VALIDATION') then 1
1158 else null end) pmt_terminate_count,
1159 count(case when PAYMENT_STATUS IN ('REMOVED_DOCUMENT_SPOILED',
1160 'REMOVED_DOCUMENT_SKIPPED') then 1
1161 else null end) pmt_spoil_skip
1162 into l_total_pmt_count, l_instr_count, l_pmt_terminate_count, l_pmt_spoil_skip
1163 from iby_payments_all
1164 WHERE payment_service_request_id = p_psr_id ;
1165 /*Bug 7248943*/
1166
1167 IF (l_instr_count = 0 AND p_psr_status = 'PAYMENTS_CREATED') THEN
1168
1169 l_psr_status := 'BUILT';
1170 g_psr_table(p_psr_id).psr_status := l_psr_status;
1171 RETURN l_psr_status;
1172
1173 END IF;
1174
1175
1176 if (l_pmt_spoil_skip > 0) THEN
1177
1178 l_psr_status := 'CONFIRMED';
1179 g_psr_table(p_psr_id).psr_status := l_psr_status;
1180 ELSE
1181
1182
1183 IF l_total_pmt_count > 0 THEN
1184 --
1185
1186 IF ( l_total_pmt_count = l_pmt_terminate_count) THEN
1187 l_psr_status := 'TERMINATED';
1188 g_psr_table(p_psr_id).psr_status := l_psr_status;
1189 ELSIF get_payment_status_flag(p_psr_id) = 'Y' THEN
1190 l_psr_status := 'CONFIRMED';
1191 g_psr_table(p_psr_id).psr_status := l_psr_status;
1192 ELSE
1193 l_psr_status := 'FORMATTING';
1194 g_psr_table(p_psr_id).psr_status := l_psr_status;
1195 END IF;
1196 ELSE
1197 l_psr_status := p_psr_status;
1198 g_psr_table(p_psr_id).psr_status := l_psr_status;
1199
1200 END IF;
1201 END IF;
1202
1203 END IF;
1204
1205 RETURN l_psr_status;
1206
1207 END get_psr_status;
1208
1209 /* Bug Number: 7279395
1210 * This procedure is used to initialize the table type variable
1211 * g_psr_table.
1212 * The pages which are accessing the functions get_psr_status and
1213 * get_payment_status_flag should take the responsibility of initializing
1214 * g_psr_table by calling this procedure.
1215 */
1216 PROCEDURE initialize
1217 IS
1218 BEGIN
1219 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1220 iby_debug_pub.add(debug_msg => 'ENTER: ' || 'IBY_UTILITY_PVT.initialize',
1221 debug_level => FND_LOG.LEVEL_STATEMENT,
1222 module => 'IBY_UTILITY_PVT.initialize');
1223 END IF;
1224 g_psr_table.DELETE;
1225 g_psr_snapshot_table.DELETE;
1226 g_instr_access_table.DELETE;
1227 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1228 iby_debug_pub.add(debug_msg => 'Exit: ' || 'IBY_UTILITY_PVT.initialize',
1229 debug_level => FND_LOG.LEVEL_STATEMENT,
1230 module => 'IBY_UTILITY_PVT.initialize');
1231 END IF;
1232 END initialize;
1233
1234
1235 Function check_user_access(p_pay_instruction_id IN Number) RETURN VARCHAR2 IS
1236 l_access varchar2(1) := 'Y';
1237 BEGIN
1238 IF(g_instr_access_table.EXISTS(p_pay_instruction_id) AND g_instr_access_table(p_pay_instruction_id).access_flag IS NOT NULL)
1239 THEN
1240 l_access := g_instr_access_table(p_pay_instruction_id).access_flag;
1241 ELSE
1242 begin
1243 select 'N' into l_access from dual where exists ( select 'Inaccessible org' from iby_payments_all where
1244 payment_instruction_id = p_pay_instruction_id and MO_GLOBAL.CHECK_ACCESS(org_id) = 'N');
1245
1246 Exception
1247 when NO_DATA_FOUND
1248 then
1249 l_access := 'Y';
1250 end;
1251 g_instr_access_table(p_pay_instruction_id).access_flag := l_access;
1252 END IF;
1253 return l_access;
1254 END check_user_access;
1255
1256
1257
1258 END IBY_UTILITY_PVT;