1 Package Body IBY_UTILITY_PVT AS
2 /* $Header: ibyvutlb.pls 120.44.12020000.3 2012/11/16 08:53:19 gmamidip 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 FUNCTION isNumeric (p_input IN VARCHAR2) RETURN VARCHAR2
92 IS
93 l_number NUMBER;
94 BEGIN
95 l_number := p_input;
96 RETURN 'Y';
97 EXCEPTION
98 WHEN OTHERS THEN
99 RETURN 'N';
100 END isNumeric;
101
102 PROCEDURE handle_exceptions
103 (
104 p_api_name IN VARCHAR2,
105 p_pkg_name IN VARCHAR2,
106 p_rollback_point IN VARCHAR2,
107 p_exception_type IN VARCHAR2,
108 x_msg_count OUT NOCOPY NUMBER,
109 x_msg_data OUT NOCOPY VARCHAR2,
110 x_return_status OUT NOCOPY VARCHAR2
111 )
112 IS
113 l_api_name VARCHAR2(30);
114 l_ora_err_code NUMBER;
115 -- the maximum length of Oracle error msg is 512
116 -- make it longer for future enh
117 l_ora_err_msg VARCHAR2(2000);
118 l_error_reason VARCHAR2(80);
119 BEGIN
120
121 iby_debug_pub.add('entered IBY_UTILITY_PVT.handle_exceptions()');
122
123 l_api_name := UPPER(p_api_name);
124
125 -- rollback to the savepoint
126 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT(p_rollback_point);
127
128 IF p_exception_type = iby_utility_pvt.g_expt_err
129 THEN
130 x_return_status := FND_API.G_RET_STS_ERROR;
131 l_error_reason := 'FND_API.G_RET_STS_ERROR';
132 ELSIF p_exception_type = iby_utility_pvt.g_expt_unexp_err
133 THEN
134 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
135 l_error_reason := 'FND_API.G_RET_STS_UNEXP_ERROR';
136 ELSIF p_exception_type = iby_utility_pvt.g_expt_otr_err
137 THEN
138 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
139 l_error_reason := 'G_EXC_OTHERS';
140
141 l_ora_err_code := SQLCODE;
142 l_ora_err_msg := SQLERRM;
143
144 iby_debug_pub.add('Oracle error in ' || p_pkg_name || '.' || p_api_name || ', code: '|| l_ora_err_code);
145 iby_debug_pub.add('Oracle error in ' || p_pkg_name || '.' || p_api_name || ', msg : '|| l_ora_err_msg);
146 --dbms_output.put_line('Oracle error, code: '|| l_ora_err_code);
147 --dbms_output.put_line('Oracle error, msg : '|| l_ora_err_msg);
148
149 fnd_message.set_name('IBY', 'IBY_G_SQL_ERR');
150 fnd_message.set_token('API', p_pkg_name || '.' || p_api_name);
151 fnd_message.set_token('SQLCODE', l_ora_err_code);
152 fnd_message.set_token('SQLERRM', l_ora_err_msg);
153 FND_MSG_PUB.ADD;
154
155 END IF;
156
157 FND_MSG_PUB.Count_And_Get(
158 p_count => x_msg_count,
159 p_data => x_msg_data);
160
161 iby_debug_pub.add('x_msg_count: ' || x_msg_count);
162 iby_debug_pub.add('x_msg_data: ' || x_msg_data);
163 iby_debug_pub.add('x_return_status: ' || x_return_status);
164
165 -- finally add the exception msg to debug log
166 /*
167 iby_debug_pub.add_return_messages(
168 p_count => x_msg_count,
169 p_data => x_msg_data,
170 p_reason => l_error_reason
171 );
172 */
173
174 END Handle_Exceptions;
175
176 PROCEDURE handleException
177 (
178 p_err_msg IN VARCHAR2,
179 p_err_code IN VARCHAR2
180 )
181 IS
182 l_ibycode_start NUMBER;
183 l_ibycode_end NUMBER;
184 l_index NUMBER;
185 l_msg_len NUMBER;
186 l_val_concat NUMBER;
187 l_tok_concat NUMBER;
188
189 l_err_code VARCHAR2(200);
190 l_token_name VARCHAR2(200);
191 l_token_val VARCHAR2(200);
192 BEGIN
193 l_msg_len := LENGTH(p_err_msg);
194 --
195 -- check if 'IBY_' is present in the error message; if so
196 -- then the exception was internally generated
197 --
198 l_ibycode_start := INSTR(p_err_msg,iby_utility_pvt.C_ERRCODE_PREFIX);
199
200 IF (l_ibycode_start > 0) THEN
201
202 l_ibycode_end:=INSTR(p_err_msg,iby_utility_pvt.C_TOKEN_CONCATENATOR)-1;
203 IF (l_ibycode_end < 1) THEN
204 l_ibycode_end := l_msg_len;
205 END IF;
206 l_err_code := SUBSTR(p_err_msg,l_ibycode_start,l_ibycode_end-l_ibycode_start+1);
207 FND_MESSAGE.SET_NAME('IBY',l_err_code);
208
209 -- +1 to go the position of the token concatenator; +1 again to
210 -- to go past it to the beginning of the token name
211 --
212 l_index:= l_ibycode_end+1+1;
213 WHILE l_index < l_msg_len LOOP
214 l_val_concat := INSTR(p_err_msg,iby_utility_pvt.C_TOKEN_VAL_CONCATENATOR,l_index);
215 IF (l_val_concat < 1) THEN
216 EXIT;
217 END IF;
218 l_tok_concat := INSTR(p_err_msg,iby_utility_pvt.C_TOKEN_CONCATENATOR,l_index);
219 IF (l_tok_concat < 1) THEN
220 -- the error message is usually of the form:
221 --
222 -- ORA-2000: IBY_XXXX#TOKENNAME=TOKENVAL#...<new line>
223 -- ORA-06512 ...
224 --
225 -- we wish to ignore the text after the
226 -- last token on the first line
227 --
228
229 -- note the -1 is for the newline character after the last
230 -- token value
231 --
232 l_tok_concat := INSTR(p_err_msg,'ORA',l_index)-1;
233 IF (l_tok_concat < 1) THEN
234 -- +1 so that the last character of the token value is
235 -- included
236 l_tok_concat := l_msg_len +1;
237 END IF;
238 END IF;
239
240 l_token_name := SUBSTR(p_err_msg,l_index,(l_val_concat-1)-l_index+1);
241 l_token_val := SUBSTR(p_err_msg,l_val_concat+1,(l_tok_concat-1)-(l_val_concat+1)+1);
242 FND_MESSAGE.SET_TOKEN(l_token_name,l_token_val);
243 --
244 -- go +1 character past the token concatenator
245 --
246 l_index := l_tok_concat + 1;
247 END LOOP;
248 FND_MSG_PUB.ADD;
249 --
250 -- no IBY message found; simply put the exact text of
251 -- the exception into the FND_MSG stack
252 --
253 ELSE
254 FND_MESSAGE.SET_NAME('IBY', 'IBY_9999');
255 FND_MESSAGE.SET_TOKEN('MESSAGE_TEXT',p_err_msg);
256 FND_MSG_PUB.ADD;
257 END IF;
258 END handleException;
259
260
261 PROCEDURE get_property
262 (
263 p_name IN VARCHAR2,
264 x_val OUT NOCOPY VARCHAR2
265 )
266 IS
267 BEGIN
268 FND_PROFILE.get(p_name,x_val);
269 END get_property;
270
271
272 PROCEDURE set_property
273 (
274 p_name IN VARCHAR2,
275 p_val IN VARCHAR2
276 )
277 IS
278 l_rtn_sts BOOLEAN;
279 BEGIN
280
281 l_rtn_sts := FND_PROFILE.save(p_name,p_val,'SITE');
282 --
283 -- 2nd put is so that the value is visible in the
284 -- current session
285 --
286 FND_PROFILE.put(p_name,p_val);
287 COMMIT;
288
289 END set_property;
290
291
292
293 --
294 -- Name: get_jtf_property
295 -- Args: p_name => property name
296 -- Outs: x_val => property value
297 --
298 -- Notes: gets an IBY property from old JTF Property manager tables.
299 -- only the first element of the value list is returned.
300 -- this function is used by ibyprupg.sql
301 --
302 FUNCTION get_jtf_property(p_name IN VARCHAR2)
303 RETURN VARCHAR2 IS
304
305 p_api_version_number NUMBER := 1.0;
306 p_init_msg_list VARCHAR2(2000) := FND_API.G_FALSE;
307 p_application_id NUMBER := 673;
308 p_profile_id NUMBER;
309 p_profile_name VARCHAR2(2000) := 'JTF_PROPERTY_MANAGER_DEFAULT_1';
310 p_perz_data_id NUMBER;
311 p_perz_data_type VARCHAR2(2000) := 'JTF';
312
313 x_return_status VARCHAR2(2000);
314 x_msg_count NUMBER := 0;
315 x_msg_data VARCHAR2(2000);
316 x_perz_data_id NUMBER;
317 x_perz_data_name VARCHAR2(2000);
318 x_perz_data_type VARCHAR2(2000);
319 x_perz_data_desc VARCHAR2(2000);
320 x_data_attrib_tbl JTF_PERZ_DATA_PUB.DATA_ATTRIB_TBL_TYPE;
321
322 p_temp_var VARCHAR2(2);
323 x_val VARCHAR2(2000);
324
325 BEGIN
326
327 JTF_PERZ_DATA_PUB.GET_PERZ_DATA( p_api_version_number,
328 p_init_msg_list,
329 p_application_id,
330 p_profile_id,
331 p_profile_name,
332 p_perz_data_id,
333 p_name,
334 p_perz_data_type,
335 x_perz_data_id,
336 x_perz_data_name,
337 x_perz_data_type,
338 x_perz_data_desc,
339 x_data_attrib_tbl,
340 x_return_status,
341 x_msg_count,
342 x_msg_data
343 );
344
345 IF ((x_return_status IS NULL OR
346 x_return_status <> FND_API.G_RET_STS_SUCCESS) OR
347 (x_data_attrib_tbl.count<1)) THEN
348 x_val := NULL;
349 ELSE
350 x_val := x_data_attrib_tbl(1).attribute_value;
351 END IF;
352
353 return x_val;
354
355 END get_jtf_property;
356
357
358 --
359 --
360 FUNCTION encode64(s IN VARCHAR2) RETURN VARCHAR2 IS
361 r RAW(32767);
362 i pls_integer;
363 x pls_integer;
364 y pls_integer;
365 v VARCHAR2(32767);
366 BEGIN
367
368 init_map;
369 r := UTL_RAW.CAST_TO_RAW( s );
370
371 -- For every 3 bytes, split them into 4 6-bit units and map them to
372 -- the Base64 characters
373 i := 1;
374 WHILE ( i + 2 <= utl_raw.length(r) ) LOOP
375 x := to_number(utl_raw.substr(r, i, 1), '0X') * 65536 +
376 to_number(utl_raw.substr(r, i + 1, 1), '0X') * 256 +
377 to_number(utl_raw.substr(r, i + 2, 1), '0X');
378 y := floor(x / 262144); v := v || map(y); x := x - y * 262144;
379 y := floor(x / 4096); v := v || map(y); x := x - y * 4096;
380 y := floor(x / 64); v := v || map(y); x := x - y * 64;
381 v := v || map(x);
382 i := i + 3;
383 END LOOP;
384
385 -- Process the remaining bytes that has fewer than 3 bytes.
386 --when last two bytes are '='
387 IF ( utl_raw.length(r) - i = 0) THEN
388 x := to_number(utl_raw.substr(r, i, 1), '0X');
389 y := floor(x / 4); v := v || map(y); x := x - y * 4;
390 x := x * 16; v := v || map(x);
391 v := v || '==';
392 --when last one byte is '='
393 ELSIF ( utl_raw.length(r) - i = 1) THEN
394 x := to_number(utl_raw.substr(r, i, 1), '0X') * 256 +
395 to_number(utl_raw.substr(r, i + 1, 1), '0X');
396 y := floor(x / 1024); v := v || map(y); x := x - y * 1024;
397 y := floor(x / 16); v := v || map(y); x := x - y * 16;
398 x := x * 4; v := v || map(x);
399 v := v || '=';
400 END IF;
401
402 RETURN v;
403
404 END encode64;
405
406 --
407 --
408 FUNCTION decode64(s IN VARCHAR2) RETURN VARCHAR2 IS
409 i pls_integer;
410 x pls_integer;
411 y pls_integer;
412 iTh pls_integer;
413 iPlusOne pls_integer;
414 iPlusTwo pls_integer;
415 iPlusThree pls_integer;
416 v VARCHAR2(32767);
417 BEGIN
418 init_map;
419 i := 1;
420 v := '';
421 --Processing 4 bytes at a time. For every 4 bytes, convert them into 3 bytes.
422 WHILE ( i + 3 <= LENGTH(s) ) LOOP
423 iTh := get_map( SUBSTR( s, i, 1) );
424 iPlusOne := get_map( SUBSTR( s, i + 1, 1) );
425 iPlusTwo := get_map( SUBSTR( s, i + 2, 1) );
426 iPlusThree := get_map( SUBSTR( s, i + 3, 1) );
427
428 --when the last two bytes equal '='
429 IF( iPlusTwo = 64 ) THEN
430 x := iTh * 64 + iPlusOne;
431 x := floor(x/16);
432 v := v || fnd_global.local_chr( x );
433
434 --when the last byte equals '='
435 ELSIF( iPlusThree = 64 ) THEN
436 x := iTh * 4096 + iPlusOne * 64 + iPlusTwo;
437 y := floor(x/1024);
438 v := v || fnd_global.local_chr( y );
439 x := x - y * 1024;
440 x := floor(x/4);
441 v := v || fnd_global.local_chr( x );
442
443 --when all the bytes hold values to be converted.
444 ELSE
445 x := iTh * 262144 + iPlusOne * 4096 + iPlusTwo * 64 + iPlusThree;
446 y := floor(x/65536);
447 v := v || fnd_global.local_chr( y );
448 x := x - y * 65536;
449 y := floor(x/256);
450 v := v || fnd_global.local_chr( y );
451 x := x - y * 256;
452 v := v || fnd_global.local_chr( x );
453 END IF;
454 i := i + 4;
455 END LOOP;
456
457 RETURN v;
458
459 END decode64;
460
461 --
462 --
463 FUNCTION get_local_nls
464 RETURN VARCHAR2
465 IS
466 BEGIN
467
468 return userenv('LANGUAGE');
469
470 EXCEPTION WHEN others THEN
471 return NULL;
472 END get_local_nls;
473
474 --
475 --
476 FUNCTION get_nls_charset( p_nls VARCHAR2 )
477 RETURN VARCHAR2
478 IS
479 l_charset_index INTEGER;
480
481 -- charset value seperator in NLSLang parameters
482 --
483 c_charset_seperator CONSTANT VARCHAR2(1) := '.';
484 BEGIN
485
486 IF (p_nls IS NULL) THEN
487 return null;
488 END IF;
489
490 l_charset_index := INSTR(p_nls,c_charset_seperator);
491
492 IF ( l_charset_index > 0 ) THEN
493 return SUBSTR(p_nls,l_charset_index+1);
494 ELSE
495 return NULL;
496 END IF;
497
498 END get_nls_charset;
499
500
501 /* ========================================================================
502 -- Function Name: MAKE_ASCII
503 --
504 -- Purpose: Function to convert a string with possibly
505 -- non-ASCII characters to ASCII. Internally, this
506 -- function calls the SQL CONVERT function.
507 --
508 -- The usage of this function will be to
509 -- substitute certain 8-bit chars in European
510 -- languages by their closest ASCII (7-bit)
511 -- equivalent.
512 --
513 -- Example:
514 -- {a with various accent marks} -> a
515 -- {e with various accent marks} -> e
516 -- and so on.
517 --
518 -- Sometimes, no straightforward conversion is possible
519 -- because there is no equivalent in ASCII for a
520 -- particular character. In this case the SQL CONVERT
521 -- function will substitute a '?' for that character.
522 --
523 -- MAKE_ASCII("Senaj(o with two dots)ki") returns "Senajoki"
524 -- MAKE_ASCII("Senaj(o with a slash)ki") returns "Senaj?ki"
525 --
526 -- Parameters:
527 -- IN 1) p_from_text VARCHAR2
528 -- The raw text which might contain accented
529 -- or otherwise non-ASCII characters.
530 --
531 -- OUT None
532 --
533 -- RETURN VARCHAR2 - The converted text with only ASCII characters.
534 -- =======================================================================*/
535
536 FUNCTION MAKE_ASCII(
537 p_from_text IN VARCHAR2
538 ) RETURN VARCHAR2 IS
539
540 l_converted_text VARCHAR2(300);
541
542 BEGIN
543
544 --
545 -- Call the SQL CONVERT method to convert given string
546 -- to US ASCII format.
547 --
548 -- See http://st-doc/8.0/817/server.817/a85397/function.htm#77039
549 -- for CONVERT syntax
550 --
551 l_converted_text := convert(p_from_text, 'US7ASCII');
552
553 RETURN l_converted_text;
554
555 EXCEPTION
556 WHEN OTHERS THEN
557 RETURN p_from_text;
558
559 END MAKE_ASCII;
560
561 FUNCTION get_call_exec
562 (
563 p_pkg_name VARCHAR2,
564 p_function_name VARCHAR2,
565 p_params JTF_VARCHAR2_TABLE_200
566 )
567 RETURN VARCHAR2
568 IS
569 l_call VARCHAR2(3000);
570 l_bind_counter NUMBER := 1;
571 BEGIN
572
573 l_call :='CALL '|| p_pkg_name || '.' || p_function_name || '(';
574
575 FOR i IN p_params.FIRST..p_params.LAST LOOP
576 IF (NOT (i = p_params.FIRST)) THEN
577 l_call := l_call || ',';
578 END IF;
579
580 IF (p_params(i) IS NULL) THEN
581 l_call := l_call || ':' || TO_CHAR(l_bind_counter);
582 l_bind_counter := l_bind_counter+1;
583 ELSE
584 l_call := l_call || p_params(i);
585 END IF;
586 END LOOP;
587
588 l_call := l_call || ')';
589
590 return l_call;
591 END get_call_exec;
592
593 PROCEDURE set_view_param
594 (
595 p_name iby_view_parameters_gt.name%TYPE,
596 p_val iby_view_parameters_gt.value%TYPE
597 )
598 IS
599 BEGIN
600
601 INSERT INTO iby_view_parameters_gt
602 (name,value,created_by,creation_date,last_updated_by,last_update_date,
603 last_update_login,object_version_number)
604 VALUES
605 (p_name,p_val,fnd_global.user_id,sysdate,fnd_global.user_id,
606 sysdate,fnd_global.login_id,1);
607
608 -- no commit, as data deleted at the end of the current
609 -- transaction
610 END set_view_param;
611
612 FUNCTION get_view_param( p_name iby_view_parameters_gt.name%TYPE )
613 RETURN iby_view_parameters_gt.value%TYPE
614 IS
615 l_val iby_view_parameters_gt.value%TYPE;
616
617 CURSOR c_param_val
618 (ci_name iby_view_parameters_gt.name%TYPE)
619 IS
620 SELECT value
621 FROM iby_view_parameters_gt
622 WHERE (name=ci_name);
623
624 BEGIN
625
626 IF (c_param_val%ISOPEN) THEN
627 CLOSE c_param_val;
628 END IF;
629
630 OPEN c_param_val(p_name);
631 FETCH c_param_val INTO l_val;
632
633 IF (c_param_val%NOTFOUND) THEN
634 l_val := NULL;
635 END IF;
636 CLOSE c_param_val;
637
638 RETURN l_val;
639
640 END get_view_param;
641
642 PROCEDURE init_debug_mode_for_extract
643 IS
644 BEGIN
645 set_view_param(PARAM_EXTRACT_DEBUG_MODE, 'Y');
646 END init_debug_mode_for_extract;
647
648 FUNCTION debug_mode_for_extract_enabled
649 RETURN VARCHAR2
650 IS
651 BEGIN
652 RETURN NVL(get_view_param(PARAM_EXTRACT_DEBUG_MODE), 'N');
653 END debug_mode_for_extract_enabled;
654
655
656 FUNCTION check_lookup_val( p_val IN VARCHAR2, p_lookup IN VARCHAR2 )
657 RETURN BOOLEAN
658 IS
659 l_count NUMBER;
660 BEGIN
661
662 SELECT count(lookup_code)
663 INTO l_count
664 FROM fnd_lookups
665 WHERE (lookup_type = p_lookup)
666 AND (lookup_code = p_val)
667 AND (enabled_flag = 'Y')
668 AND (NVL(end_date_active,SYSDATE-10) < SYSDATE);
669
670 IF (l_count<1) THEN
671 RETURN false;
672 ELSE
673 RETURN true;
674 END IF;
675
676 END check_lookup_val;
677
678 FUNCTION validate_party_id(p_party_id IN hz_parties.party_id%TYPE)
679 RETURN BOOLEAN
680 IS
681 l_count NUMBER;
682
683 CURSOR c_party(ci_party_id IN hz_parties.party_id%TYPE)
684 IS
685 SELECT COUNT(party_id)
686 INTO l_count
687 FROM hz_parties
688 WHERE party_id = ci_party_id;
689 BEGIN
690 IF (c_party%ISOPEN) THEN CLOSE c_party; END IF;
691
692 OPEN c_party(p_party_id);
693 FETCH c_party INTO l_count;
694 CLOSE c_party;
695
696 RETURN (l_count>0);
697 END validate_party_id;
698
699 FUNCTION validate_app_id(p_app_id IN fnd_application.application_id%TYPE)
700 RETURN BOOLEAN
701 IS
702 l_count NUMBER;
703
704 CURSOR c_app(ci_app_id IN fnd_application.application_id%TYPE)
705 IS
706 SELECT count(*)
707 FROM fnd_application
708 WHERE (application_id = ci_app_id);
709
710 BEGIN
711 IF (c_app%ISOPEN) THEN CLOSE c_app; END IF;
712
713 OPEN c_app(p_app_id);
714 FETCH c_app INTO l_count;
715 CLOSE c_app;
716
717 IF (l_count<1) THEN
718 RETURN FALSE;
719 ELSE
720 RETURN TRUE;
721 END IF;
722 END validate_app_id;
723
724
725 FUNCTION validate_territory
726 ( p_territory IN fnd_territories.territory_code%TYPE )
727 RETURN BOOLEAN
728 IS
729
730 l_territory fnd_territories.territory_code%TYPE;
731
732 CURSOR c_terr( ci_code IN fnd_territories.territory_code%TYPE )
733 IS
734 SELECT territory_code
735 FROM fnd_territories
736 WHERE (territory_code = ci_code)
737 AND (NVL(obsolete_flag,'N') = 'N');
738
739 BEGIN
740 IF (c_terr%ISOPEN) THEN CLOSE c_terr; END IF;
741
742 OPEN c_terr(p_territory);
743 FETCH c_terr INTO l_territory;
744 CLOSE c_terr;
745
746 RETURN (NOT l_territory IS NULL);
747 END validate_territory;
748
749
750 FUNCTION is_trivial(p_string VARCHAR2)
751 RETURN BOOLEAN
752 IS
753 BEGIN
754 IF (p_string IS NULL) THEN RETURN TRUE;
755 ELSIF (RTRIM(LTRIM(p_string)) IS NULL) THEN RETURN TRUE;
756 ELSE RETURN FALSE; END IF;
757 END is_trivial;
758
759 FUNCTION validate_organization(p_org_id IN iby_trxn_summaries_all.org_id%TYPE,
760 p_org_type IN iby_trxn_summaries_all.org_type%TYPE)
761 RETURN VARCHAR2
762 IS
763
764 l_org_id NUMBER;
765 l_return_value VARCHAR2(1) := 'N';
766
767 BEGIN
768
769 BEGIN
770 SELECT organization_id
771 INTO l_org_id
772 FROM hr_operating_units
773 WHERE organization_id = p_org_id;
774
775 EXCEPTION
776 WHEN no_data_found THEN
777 l_org_id := NULL;
778
779 END;
780
781 IF (l_org_id IS NOT NULL) THEN
782 l_return_value := 'Y';
783 END IF;
784
785 RETURN l_return_value;
786
787 END validate_organization;
788
789
790 PROCEDURE validate_pmt_channel_code(p_instrument_type IN iby_creditcard.instrument_type%TYPE,
791 p_payment_channel_code IN OUT NOCOPY iby_trxn_summaries_all.payment_channel_code%TYPE,
792 p_valid OUT NOCOPY VARCHAR2)
793 IS
794 l_payment_channel_code iby_trxn_summaries_all.payment_channel_code%TYPE;
795 l_return_value VARCHAR2(1) := 'N';
796
797 BEGIN
798
799 BEGIN
800 SELECT payment_channel_code
801 INTO l_payment_channel_code
802 FROM iby_fndcpt_pmt_chnnls_b
803 WHERE instrument_type = p_instrument_type
804 AND (UPPER(payment_channel_code) = UPPER(p_payment_channel_code)
805 OR p_payment_channel_code is null);
806
807 EXCEPTION
808 WHEN no_data_found THEN
809 l_payment_channel_code := NULL;
810
811 WHEN too_many_rows THEN
812 IF (p_instrument_type = 'BANKACCOUNT') THEN
813 l_payment_channel_code := 'BANK_ACCT_XFER';
814
815 ELSIF (p_instrument_type = 'MANUAL') THEN
816 l_payment_channel_code := 'CHECK';
817
818 ELSE
819 l_payment_channel_code := NULL;
820 END IF;
821
822 END;
823
824 IF (l_payment_channel_code IS NOT NULL AND
825 UPPER(l_payment_channel_code) = NVL(UPPER(p_payment_channel_code), UPPER(l_payment_channel_code))) THEN
826 l_return_value := 'Y';
827 END IF;
828
829 p_payment_channel_code := l_payment_channel_code;
830 p_valid := l_return_value;
831
832 END validate_pmt_channel_code;
833
834 -- This function is moved from appayutb.pls to provide the count of requests in 'Processing', 'Terminated' in Payments Dashboard.
835 FUNCTION get_psr_snapshot_count(p_snapshot_code IN VARCHAR2)
836 RETURN NUMBER IS
837
838 l_status_code FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE;
839
840 l_ret_val NUMBER;
841 l_count1 NUMBER;
842 l_count2 NUMBER;
843 l_count3 NUMBER;
844 l_count4 NUMBER;
845 BEGIN
846 IF(g_psr_snapshot_table.EXISTS(p_snapshot_code) AND g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count IS NOT NULL) THEN
847 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
848 iby_debug_pub.add(debug_msg => 'IBY_UTILITY_PVT.get_psr_snapshot_count :Getting from the cache',
849 debug_level => FND_LOG.LEVEL_STATEMENT,
850 module => 'IBY_UTILITY_PVT.get_psr_snapshot_count');
851 END IF;
852 l_ret_val:= g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count;
853 ELSE
854 IF (p_snapshot_code = 'NEED_ACTION_BY_ME') THEN
855
856 SELECT SUM(icount) tcount
857 INTO l_count1
858 FROM
859 (SELECT COUNT(*) icount
860 FROM iby_pay_service_requests iby
861 WHERE iby.calling_app_id = 200
862 AND iby.process_type = 'STANDARD'
863 AND iby.payment_service_request_status IN ( 'INFORMATION_REQUIRED', 'PENDING_REVIEW_DOC_VAL_ERRORS', 'PENDING_REVIEW_PMT_VAL_ERRORS', 'PENDING_REVIEW')
864 UNION
865 SELECT COUNT(*) icount
866 FROM iby_pay_service_requests iby
867 WHERE iby.calling_app_id = 200
868 AND iby.process_type = 'STANDARD'
869 AND iby.payment_service_request_status = 'PAYMENTS_CREATED'
870 AND EXISTS
871 (SELECT 'NEED_ACTION_BY_ME'
872 FROM iby_payments_all pmt
873 WHERE iby.payment_service_request_id = pmt.payment_service_request_id
874 AND EXISTS
875 (SELECT 'NEED_ACTION_BY_ME'
876 FROM iby_pay_instructions_all instr
877 WHERE instr.payment_instruction_id = pmt.payment_instruction_id
878 AND instr.payment_instruction_status IN ('CREATION_ERROR', 'FORMATTED_READY_TO_TRANSMIT', 'TRANSMISSION_FAILED',
879 'FORMATTED_READY_FOR_PRINTING', 'SUBMITTED_FOR_PRINTING', 'CREATED_READY_FOR_PRINTING',
880 'CREATED_READY_FOR_FORMATTING', 'FORMATTED', 'CREATED', 'TRANSMITTED')
881 AND instr.payments_complete_code = 'NO'
882 AND iby_utility_pvt.check_user_access(instr.payment_instruction_id) = 'Y'
883 )
884 )
885 );
886
887 SELECT count(*)
888 INTO l_count2
889 FROM ap_inv_selection_criteria_all ap
890 WHERE ap.status IN ('REVIEW', 'MISSING RATES' )
891 AND NOT EXISTS ( SELECT 'NEED_ACTION_BY_ME'
892 FROM iby_pay_service_requests iby
893 WHERE iby.calling_app_id = 200
894 AND iby.call_app_pay_service_req_code =
895 ap.checkrun_name);
896
897
898 l_ret_val := l_count1 + l_count2;
899 g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count := l_ret_val;
900
901 ELSIF p_snapshot_code = 'PROCESSING' THEN
902 /*Modified the query for the Bug 7560766
903 Added a condition to identify the PPRs which
904 are waiting for the user action*/
905 SELECT
906 count(*)
907 INTO l_count1
908 FROM iby_pay_service_requests iby
909 WHERE iby.calling_app_id = 200
910 AND iby.process_type = 'STANDARD'
911 AND
912 (
913 iby.payment_service_request_status IN ('INSERTED', 'SUBMITTED',
914 'ASSIGNMENT_COMPLETE',
915 'DOCUMENTS_VALIDATED',
916 'RETRY_DOCUMENT_VALIDATION',
917 'RETRY_PAYMENT_CREATION')
918 OR
919 (
920 iby.payment_service_request_status IN ('PAYMENTS_CREATED')
921 AND EXISTS
922 (SELECT 'PROCESSING'
923 FROM iby_payments_all pmt
924 WHERE
925 pmt.payment_service_request_id = iby.payment_service_request_id
926 AND pmt.payment_status NOT IN('REMOVED', 'VOID', 'VOID_BY_SETUP', 'VOID_BY_OVERFLOW', 'REMOVED_PAYMENT_STOPPED',
927 'REMOVED_DOCUMENT_SPOILED', 'REMOVED_INSTRUCTION_TERMINATED', 'REMOVED_REQUEST_TERMINATED', 'ISSUED', 'TRANSMITTED', 'REJECTED',
928 'FAILED_VALIDATION', 'FAILED_BY_CALLING_APP', 'FAILED_BY_REJECTION_LEVEL')
929 AND pmt.payments_complete_flag <> 'Y'
930 AND NOT EXISTS
931 (SELECT 'NEED_ACTION'
932 FROM iby_pay_instructions_all inst
933 WHERE pmt.payment_instruction_id = inst.payment_instruction_id
934 AND inst.payment_instruction_status IN('CREATION_ERROR',
935 'FORMATTED_READY_TO_TRANSMIT',
936 'TRANSMISSION_FAILED',
937 'FORMATTED_READY_FOR_PRINTING',
938 'SUBMITTED_FOR_PRINTING',
939 'CREATED_READY_FOR_PRINTING',
940 'CREATED_READY_FOR_FORMATTING',
941 'FORMATTED',
942 'CREATED',
943 'FORMATTED_ELECTRONIC'))
944 )
945 )
946 );
947
948
949 SELECT count(*)
950 INTO l_count2
951 FROM ap_inv_selection_criteria_all ap
952 WHERE ap.status IN ('UNSTARTED', 'SELECTING', 'CANCELING',
953 'CALCULATING', 'SELECTED')
954 AND NOT EXISTS ( SELECT 'PROCESSING'
955 FROM iby_pay_service_requests iby
956 WHERE iby.calling_app_id = 200
957 AND iby.call_app_pay_service_req_code =
958 ap.checkrun_name);
959
960 l_ret_val := l_count1 + l_count2;
961 g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count := l_ret_val;
962
963 ELSIF p_snapshot_code = 'USER_TERMINATED' THEN
964 SELECT count(*)
965 INTO l_count1
966 FROM ap_inv_selection_criteria_all ap
967 WHERE EXISTS ( SELECT 'IBY USER_TERMINATED'
968 FROM iby_pay_service_requests iby
969 WHERE iby.calling_app_id = 200
970 AND iby.call_app_pay_service_req_code =
971 ap.checkrun_name
972 AND iby.payment_service_request_status IN
973 ('TERMINATED'))
974 AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
975
976 SELECT count(*)
977 INTO l_count2
978 FROM ap_inv_selection_criteria_all ap
979 WHERE ap.status IN ('CANCELED', 'CANCELLED NO PAYMENTS')
980 AND TRUNC(ap.creation_date) =TRUNC(sysdate)
981 AND NOT EXISTS ( SELECT 'AP USER_TERMINATED'
982 FROM iby_pay_service_requests iby
983 WHERE iby.calling_app_id = 200
984 AND iby.call_app_pay_service_req_code =
985 ap.checkrun_name)
986 AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
987
988 l_ret_val := l_count1 + l_count2;
989 g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count := l_ret_val;
990
991 ELSIF p_snapshot_code = 'PROGRAM_ERRORS' THEN
992
993 SELECT count(*)
994 INTO l_count1
995 FROM ap_inv_selection_criteria_all ap
996 WHERE EXISTS ( SELECT 'PROGRAM ERRORS'
997 FROM iby_pay_service_requests iby
998 WHERE iby.calling_app_id = 200
999 AND iby.call_app_pay_service_req_code =
1000 ap.checkrun_name
1001 AND iby.payment_service_request_status IN
1002 ('PENDING_REVIEW_DOC_VAL_ERRORS',
1003 'PENDING_REVIEW_PMT_VAL_ERRORS'))
1004 AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
1005
1006 l_ret_val := l_count1;
1007 g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count := l_ret_val;
1008
1009 ELSIF p_snapshot_code = 'COMPLETED' THEN
1010
1011 SELECT count(*)
1012 INTO l_count1
1013 FROM ap_inv_selection_criteria_all ap
1014 WHERE EXISTS ( SELECT 'COMPLETED'
1015 FROM iby_pay_service_requests iby
1016 WHERE iby.calling_app_id = 200
1017 AND iby.call_app_pay_service_req_code =
1018 ap.checkrun_name
1019 AND iby.payment_service_request_status IN
1020 ('PAYMENTS_CREATED','COMPLETED')
1021 AND AP_PAYMENT_UTIL_PKG.get_payment_status_flag(iby.payment_service_request_id) = 'Y')
1022 AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
1023
1024 l_ret_val := l_count1;
1025 g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count := l_ret_val;
1026
1027 ELSIF p_snapshot_code = 'TOTAL' THEN
1028
1029 -- The total value is calculated in the UI
1030 NULL;
1031
1032 END IF;
1033 END IF;
1034 RETURN l_ret_val;
1035
1036 END;
1037
1038 -- This function is moved from appayutb.pls to provide the count of requests in 'Processing', 'Terminated' in Payments Dashboard.
1039 /* Bug Number: 7279395
1040 * Caching is implemented based on the psr id. Hence pages or procedures
1041 * which are accessing this function should take the responsibility to
1042 * initialize g_psr_table by calling the initialize procedure
1043 *
1044 *Bug 8883966: Added new input parameter p_from_cache.
1045 */
1046 FUNCTION get_payment_status_flag(p_psr_id IN NUMBER,
1047 p_from_cache IN VARCHAR2 DEFAULT 'FALSE')
1048 RETURN VARCHAR2 IS
1049
1050 l_payment_status_flag VARCHAR2(1);
1051 l_total_pmt_count NUMBER;
1052 l_pmt_complete_count NUMBER;
1053
1054
1055 BEGIN
1056
1057 IF ( p_psr_id IS NOT NULL ) THEN
1058
1059 IF(g_psr_table.EXISTS(p_psr_id) AND g_psr_table(p_psr_id).payment_status_flag IS NOT NULL
1060 AND p_from_cache = 'TRUE') THEN
1061 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1062 iby_debug_pub.add(debug_msg => 'IBY_UTILITY_PVT.get_payment_status_flag :Getting from the cache',
1063 debug_level => FND_LOG.LEVEL_STATEMENT,
1064 module => 'IBY_UTILITY_PVT.get_payment_status_flag');
1065 END IF;
1066 l_payment_status_flag:= g_psr_table(p_psr_id).payment_status_flag;
1067 ELSE
1068
1069 /*Bug 7248943*/
1070 select count (*) total_pmt_count,
1071 count(case when PAYMENTS_COMPLETE_FLAG = 'Y' then 1 else null
1072 end) pmt_complete_count
1073 into l_total_pmt_count, l_pmt_complete_count /*Bug 7248943*/
1074 FROM iby_payments_all
1075 WHERE payment_service_request_id = p_psr_id
1076 AND payment_status NOT IN ('REMOVED', 'VOID_BY_SETUP',
1077 'VOID_BY_OVERFLOW', 'REMOVED_PAYMENT_STOPPED',
1078 'REMOVED_DOCUMENT_SPOILED',
1079 'REMOVED_INSTRUCTION_TERMINATED',
1080 'REMOVED_REQUEST_TERMINATED',
1081 'REJECTED', -- Bug 6897223- new statuses added
1082 'FAILED_BY_CALLING_APP',
1083 'FAILED_BY_REJECTION_LEVEL',
1084 'FAILED_VALIDATION',
1085 'INSTRUCTION_FAILED_VALIDATION'); --Bug 6686639
1086
1087 IF l_total_pmt_count > 0 THEN
1088 --
1089 /*Removed query for Bug 7248943*/
1090
1091 IF ( l_total_pmt_count = l_pmt_complete_count) THEN
1092 l_payment_status_flag := 'Y';
1093 g_psr_table(p_psr_id).payment_status_flag := l_payment_status_flag;
1094 ELSIF ((l_total_pmt_count > l_pmt_complete_count) AND
1095 (l_pmt_complete_count <> 0))THEN
1096 l_payment_status_flag := 'P';
1097 g_psr_table(p_psr_id).payment_status_flag := l_payment_status_flag;
1098 ELSIF ((l_total_pmt_count > l_pmt_complete_count) AND
1099 (l_pmt_complete_count = 0)) THEN
1100 l_payment_status_flag := 'N';
1101 g_psr_table(p_psr_id).payment_status_flag := l_payment_status_flag;
1102 END IF;
1103 --
1104 ELSE
1105 --
1106
1107 l_payment_status_flag := 'N';
1108 g_psr_table(p_psr_id).payment_status_flag := l_payment_status_flag;
1109
1110 END IF;
1111 END IF;
1112 ELSE
1113 l_payment_status_flag := 'N';
1114
1115 END IF;
1116
1117
1118 RETURN l_payment_status_flag;
1119
1120 END get_payment_status_flag;
1121
1122 -- This function is moved from appayutb.pls to provide the count of requests in 'Processing', 'Terminated' in Payments Dashboard.
1123 /* Bug Number: 7279395
1124 * Caching is implemented based on the psr id. Hence pages or procedures
1125 * which are accessing this function should take the responsibility to
1126 * initialize g_psr_table by calling the initialize procedure
1127 *
1128 * Bug 8883966: Added new input parameter p_from_cache.
1129 */
1130 FUNCTION get_psr_status(p_psr_id IN NUMBER,
1131 p_psr_status IN VARCHAR2,
1132 p_from_cache IN VARCHAR2 DEFAULT 'FALSE')
1133 RETURN VARCHAR2 IS
1134
1135 l_psr_status VARCHAR2(30);
1136 l_total_pmt_count NUMBER;
1137 l_instr_count NUMBER;
1138 l_pmt_terminate_count NUMBER;
1139 l_pmt_spoil_skip NUMBER;
1140 l_valid_completed_pmt NUMBER;
1141
1142 BEGIN
1143 IF(g_psr_table.EXISTS(p_psr_id) AND g_psr_table(p_psr_id).psr_status IS NOT NULL
1144 AND p_from_cache = 'TRUE') THEN
1145 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1146 iby_debug_pub.add(debug_msg => 'IBY_UTILITY_PVT.get_psr_status :Getting from the cache',
1147 debug_level => FND_LOG.LEVEL_STATEMENT,
1148 module => 'IBY_UTILITY_PVT.get_psr_status');
1149 END IF;
1150 l_psr_status:= g_psr_table(p_psr_id).psr_status;
1151 ELSE
1152
1153 IF p_psr_status <> 'PAYMENTS_CREATED' AND p_psr_status <> 'COMPLETED' THEN
1154
1155 IF p_psr_status IN ('INSERTED', 'SUBMITTED',
1156 'ASSIGNMENT_COMPLETE',
1157 'DOCUMENTS_VALIDATED',
1158 'RETRY_DOCUMENT_VALIDATION',
1159 'RETRY_PAYMENT_CREATION') THEN
1160
1161 l_psr_status := 'BUILDING';
1162 g_psr_table(p_psr_id).psr_status := l_psr_status;
1163 RETURN l_psr_status;
1164 --
1165 END IF;
1166 --
1167 RETURN p_psr_status;
1168 ELSIF p_psr_status = 'COMPLETED' THEN
1169 BEGIN
1170 select 1
1171 into l_valid_completed_pmt
1172 from dual
1173 where exists(select 'VALID_PAYMENT'
1174 from iby_payments_all
1175 where payment_service_request_id = p_psr_id
1176 and payments_complete_flag = 'Y'
1177 and payment_status in ('INSTRUCTION_CREATED', 'ISSUED', 'FORMATTED', 'TRANSMITTED'));
1178
1179 l_psr_status := 'CONFIRMED';
1180 EXCEPTION
1181 WHEN no_data_found then
1182 l_psr_status := 'TERMINATED';
1183 END;
1184
1185 RETURN l_psr_status;
1186 END IF;
1187
1188 /*Bug 7248943*/
1189 select count (*) total_pmt_count,
1190 count(case when payment_instruction_id IS NOT NULL then 1
1191 else null end) instr_count,
1192 count(case when PAYMENT_STATUS IN ('REMOVED_INSTRUCTION_TERMINATED',
1193 'REMOVED_REQUEST_TERMINATED',
1194 'VOID',
1195 'REMOVED',
1196 'REMOVED_PAYMENT_STOPPED',
1197 'VOID_BY_SETUP',
1198 'VOID_BY_OVERFLOW',
1199 'REMOVED_DOCUMENT_SPOILED',
1200 'REJECTED',
1201 'FAILED_BY_CALLING_APP',
1202 'FAILED_BY_REJECTION_LEVEL',
1203 'FAILED_VALIDATION',
1204 'INSTRUCTION_FAILED_VALIDATION') then 1
1205 else null end) pmt_terminate_count,
1206 count(case when PAYMENT_STATUS IN ('REMOVED_DOCUMENT_SPOILED',
1207 'REMOVED_DOCUMENT_SKIPPED') then 1
1208 else null end) pmt_spoil_skip
1209 into l_total_pmt_count, l_instr_count, l_pmt_terminate_count, l_pmt_spoil_skip
1210 from iby_payments_all
1211 WHERE payment_service_request_id = p_psr_id ;
1212 /*Bug 7248943*/
1213
1214 IF (l_instr_count = 0 AND p_psr_status = 'PAYMENTS_CREATED') THEN
1215
1216 l_psr_status := 'BUILT';
1217 g_psr_table(p_psr_id).psr_status := l_psr_status;
1218 RETURN l_psr_status;
1219
1220 END IF;
1221
1222
1223 if (l_pmt_spoil_skip > 0) THEN
1224
1225 l_psr_status := 'CONFIRMED';
1226 g_psr_table(p_psr_id).psr_status := l_psr_status;
1227 ELSE
1228
1229
1230 IF l_total_pmt_count > 0 THEN
1231 --
1232
1233 IF ( l_total_pmt_count = l_pmt_terminate_count) THEN
1234 l_psr_status := 'TERMINATED';
1235 g_psr_table(p_psr_id).psr_status := l_psr_status;
1236 ELSIF get_payment_status_flag(p_psr_id, p_from_cache) = 'Y' THEN
1237 l_psr_status := 'CONFIRMED';
1238 g_psr_table(p_psr_id).psr_status := l_psr_status;
1239 ELSE
1240 l_psr_status := 'FORMATTING';
1241 g_psr_table(p_psr_id).psr_status := l_psr_status;
1242 END IF;
1243 ELSE
1244 l_psr_status := p_psr_status;
1245 g_psr_table(p_psr_id).psr_status := l_psr_status;
1246
1247 END IF;
1248 END IF;
1249
1250 END IF;
1251
1252 RETURN l_psr_status;
1253
1254 END get_psr_status;
1255
1256 /* Bug Number: 7279395
1257 * This procedure is used to initialize the table type variable
1258 * g_psr_table.
1259 * The pages which are accessing the functions get_psr_status and
1260 * get_payment_status_flag should take the responsibility of initializing
1261 * g_psr_table by calling this procedure.
1262 */
1263 PROCEDURE initialize
1264 IS
1265 BEGIN
1266 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1267 iby_debug_pub.add(debug_msg => 'ENTER: ' || 'IBY_UTILITY_PVT.initialize',
1268 debug_level => FND_LOG.LEVEL_STATEMENT,
1269 module => 'IBY_UTILITY_PVT.initialize');
1270 END IF;
1271 g_psr_table.DELETE;
1272 g_psr_snapshot_table.DELETE;
1273 g_instr_access_table.DELETE;
1274 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1275 iby_debug_pub.add(debug_msg => 'Exit: ' || 'IBY_UTILITY_PVT.initialize',
1276 debug_level => FND_LOG.LEVEL_STATEMENT,
1277 module => 'IBY_UTILITY_PVT.initialize');
1278 END IF;
1279 END initialize;
1280
1281
1282 Function check_user_access(p_pay_instruction_id IN Number) RETURN VARCHAR2 IS
1283 l_access varchar2(1) := 'Y';
1284 BEGIN
1285 IF(g_instr_access_table.EXISTS(p_pay_instruction_id) AND g_instr_access_table(p_pay_instruction_id).access_flag IS NOT NULL)
1286 THEN
1287 l_access := g_instr_access_table(p_pay_instruction_id).access_flag;
1288 ELSE
1289 begin
1290 select 'N' into l_access from dual where exists ( select 'Inaccessible org' from iby_payments_all where
1291 payment_instruction_id = p_pay_instruction_id and MO_GLOBAL.CHECK_ACCESS(org_id) = 'N');
1292
1293 Exception
1294 when NO_DATA_FOUND
1295 then
1296 l_access := 'Y';
1297 end;
1298 g_instr_access_table(p_pay_instruction_id).access_flag := l_access;
1299 END IF;
1300 return l_access;
1301 END check_user_access;
1302
1303
1304
1305 FUNCTION get_format_program_name(p_pay_instruction_id IN NUMBER) RETURN VARCHAR2 IS
1306
1307 l_conc_prog varchar2(100);
1308
1309 BEGIN
1310
1311 SELECT decode(decode(template_type_code,
1312 'RTF','PDF',
1313 'ETEXT','ETEXT',
1314 'XSL-XML','XML',
1315 'PDF','PDF'),'PDF', 'IBY_FD_PAYMENT_FORMAT','IBY_FD_PAYMENT_FORMAT_TEXT')
1316 into
1317 l_conc_prog
1318 FROM iby_pay_instructions_all ins,
1319 iby_payment_profiles pp,
1320 iby_formats_b format,
1321 XDO_TEMPLATES_B temp
1322 WHERE ins.payment_instruction_id = p_pay_instruction_id
1323 AND ins.payment_profile_id = pp.payment_profile_id
1324 AND format.FORMAT_CODE = pp.PAYMENT_FORMAT_CODE
1325 AND format.FORMAT_TEMPLATE_CODE = temp.template_code
1326 AND nvl(temp.end_date, SYSDATE)>= SYSDATE;
1327
1328
1329 return l_conc_prog;
1330
1331 END get_format_program_name;
1332
1333 /*-----------------------------------------------------------------------------------------
1334 | FUNCTION - get_psr_snapshot_count PIPELINED.
1335 |
1336 |
1337 | DESCRIPTION - This function is designed for the Payables Payment Manager
1338 | Home Page . The function returns the total count of Payment
1339 | Process Requests with a particular Status or a combination
1340 | of Payment Process Request Statuses that map to a particular
1341 | snapshot code
1342 |
1343 | SNAPSHOT CODE STATUS
1344 | ------------- ------------------------------------------------
1345 | NEED_ACTION_BY_ME AP:
1346 | 'REVIEW', 'MISSING RATES'
1347 | IBY:
1348 | 'INFORMATION_REQUIRED'
1349 | 'PENDING_REVIEW_DOC_VAL_ERRORS',
1350 | 'PENDING_REVIEW_PMT_VAL_ERRORS',
1351 | 'PENDING_REVIEW'
1352 |
1353 | PROCESSING AP:
1354 | 'UNSTARTED', 'SELECTING', 'CANCELING',
1355 | 'CALCULATING', 'SELECTED'
1356 | IBY:
1357 | 'INSERTED', 'SUBMITTED',
1358 | 'ASSIGNMENT_COMPLETE','DOCUMENTS_VALIDATED',
1359 | 'RETRY_DOCUMENT_VALIDATION',
1360 | 'RETRY_PAYMENT_CREATION'
1361 |
1362 | USER_TERMINATED AP:
1363 | 'CANCELED' , 'CANCELLED NO PAYMENTS'
1364 | IBY:
1365 | 'TERMINATED'
1366 |
1367 | PROGRAM_ERRORS IBY:
1368 | 'PENDING_REVIEW_DOC_VAL_ERRORS'
1369 | 'PENDING_REVIEW_PMT_VAL_ERRORS'
1370 |
1371 | COMPLETED IBY:
1372 | 'PAYMENTS_CREATED'
1373 |
1374 | TOTAL COUNT(*) IN AP
1375 |
1376 |===========================================================================================
1377 |Understanding PIPELINED FUNCTION:
1378 |-----------------------------------
1379 |PIPELINED functions are piece of code that can be used for querying SQL.
1380 |Basically, when you would like a PLSQL routine to be the source
1381 |of data -- instead of a table -- you would use a pipelined function.
1382 |PIPELINED functions will operate like a table.
1383 |Using PL/SQL table functions can significantly lower the over-head of
1384 |doing such transformations. PL/SQL table functions accept and return
1385 |multiple rows, delivering them as they are ready rather than all at once,
1386 |and can be made to execute as parallel operations.
1387 |
1388 -----------------------------------------------------------------------------------------
1389 */
1390
1391 FUNCTION get_psr_snapshot_pipe RETURN snapshot_count_t PIPELINED
1392 IS
1393
1394 p_snapshot_code VARCHAR2(100) := 'Test';
1395 l_ret_val snapshot_count_type;
1396 l_status_code FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE;
1397 --l_ret_val NUMBER;
1398 l_count1 NUMBER;
1399 l_count2 NUMBER;
1400 l_count3 NUMBER;
1401 l_count4 NUMBER;
1402 -- Variables for count
1403 l_need_action NUMBER;
1404 l_processing NUMBER;
1405 l_terminated NUMBER;
1406 l_errors NUMBER;
1407 l_completed NUMBER;
1408 l_process_count NUMBER;
1409 l_access VARCHAR2(1);
1410 --Loop For every ppr
1411 CURSOR c_psr_snapshot
1412 IS
1413 SELECT ipsr.payment_service_request_id ,
1414 ipsr.payment_service_request_status ,
1415 aisc.status ,
1416 aisc.checkrun_id ,
1417 aisc.checkrun_name ,
1418 aisc.creation_date
1419 FROM iby_pay_service_requests ipsr ,
1420 ap_inv_selection_criteria_all aisc
1421 WHERE ipsr.call_app_pay_service_req_code(+) = aisc.checkrun_name;
1422 --AND process_type = 'STANDARD' ;
1423 BEGIN
1424 IF(g_psr_snapshot_table.EXISTS(p_snapshot_code) AND g_psr_snapshot_table(p_snapshot_code).psr_snapshot_count IS NOT NULL) THEN
1425
1426 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1427 iby_debug_pub.add(debug_msg => 'IBY_UTILITY_PVT.get_psr_snapshot_count :Getting from the cache', debug_level => FND_LOG.LEVEL_STATEMENT, module => 'IBY_UTILITY_PVT.get_psr_snapshot_count');
1428 END IF ;
1429 ELSE
1430 -- Initialize the variables.
1431 l_need_action :=0;
1432 l_processing :=0;
1433 l_terminated :=0;
1434 l_errors :=0;
1435 l_completed :=0;
1436 FOR c_rec_snap IN c_psr_snapshot
1437 LOOP
1438
1439 /* IBY NEED ACTION */
1440 --Handling everything with iby_pay_service_requests first.
1441 --For IBY_PAY_SERVICE_REQUESTS.
1442 IF c_rec_snap.payment_service_request_status IN ('INFORMATION_REQUIRED' , 'PENDING_REVIEW_DOC_VAL_ERRORS','PENDING_REVIEW_PMT_VAL_ERRORS', 'PENDING_REVIEW') THEN
1443 l_need_action := l_need_action + 1;
1444 --For records in ap_inv_selection_criteria_all but not in iby_pay_service_request. For PPR which are still before build.
1445 ELSIF c_rec_snap.payment_service_request_status IS NULL AND c_rec_snap.status IN ('REVIEW', 'MISSING RATES' ) THEN
1446 l_need_action := l_need_action + 1;
1447 /* IBY NEED ACTION */
1448 /* IBY IN PROCESS. */
1449 ELSIF c_rec_snap.payment_service_request_status IN ('INSERTED', 'SUBMITTED','ASSIGNMENT_COMPLETE','DOCUMENTS_VALIDATED','RETRY_DOCUMENT_VALIDATION','RETRY_PAYMENT_CREATION') THEN
1450 l_processing := l_processing + 1;
1451 ELSIF c_rec_snap.payment_service_request_status IS NULL AND c_rec_snap.status IN ('UNSTARTED', 'SELECTING', 'CANCELING','CALCULATING', 'SELECTED') THEN
1452 l_processing := l_processing + 1;
1453 /* IBY IN PROCESS. */
1454 /* PAYMENTS_CREATED and it can be in either processing or need action group */
1455 --Special case Payment Created
1456 ELSIF c_rec_snap.payment_service_request_status IN ('PAYMENTS_CREATED') THEN
1457 -- Need to check the payment instruction and individual payments.
1458 l_access := 'N';
1459 --Check org access also verifies the Payment Instruction statuses that are valid.
1460 l_access := check_org_access(c_rec_snap.payment_service_request_id);
1461 IF l_access = 'Y'
1462 THEN
1463 l_need_action := l_need_action + 1;
1464 ELSE
1465 -- For Processing Action.
1466 BEGIN
1467 SELECT 'Y'
1468 INTO l_process_count
1469 FROM dual
1470 WHERE EXISTS
1471 (SELECT 'PROCESSING'
1472 FROM iby_payments_all pmt
1473 WHERE pmt.payment_service_request_id =c_rec_snap.payment_service_request_id
1474 AND pmt.payment_status NOT IN('REMOVED', 'VOID', 'VOID_BY_SETUP', 'VOID_BY_OVERFLOW', 'REMOVED_PAYMENT_STOPPED', 'REMOVED_DOCUMENT_SPOILED', 'REMOVED_INSTRUCTION_TERMINATED',
1475 'REMOVED_REQUEST_TERMINATED', 'ISSUED', 'TRANSMITTED', 'REJECTED')
1476 AND pmt.payments_complete_flag <> 'Y'
1477 AND NOT EXISTS
1478 (SELECT 'NEED_ACTION'
1479 FROM iby_pay_instructions_all inst
1480 WHERE pmt.payment_instruction_id = inst.payment_instruction_id
1481 AND (inst.payment_instruction_status IN('CREATION_ERROR', 'FORMATTED_READY_TO_TRANSMIT', 'TRANSMISSION_FAILED', 'FORMATTED_READY_FOR_PRINTING', 'SUBMITTED_FOR_PRINTING',
1482 'CREATED_READY_FOR_PRINTING', 'CREATED_READY_FOR_FORMATTING', 'FORMATTED', 'CREATED', 'FORMATTED_ELECTRONIC')
1483 OR (inst.payment_instruction_status = 'TRANSMITTED' AND IBY_FD_USER_API_PUB.Is_transmitted_Pmt_Inst_Compl(inst.PAYMENT_INSTRUCTION_ID) = 'N'))
1484 )
1485 );
1486 -- If it comes here then it is in processing status
1487 l_processing := l_processing + 1;
1488 EXCEPTION
1489 WHEN NO_DATA_FOUND THEN
1490 NULL; -- Do Nothing.
1491 WHEN OTHERS THEN
1492 iby_debug_pub.add(debug_msg => 'IBY_UTILITY_PVT.get_psr_snapshot_count :Error in processing count', debug_level => FND_LOG.LEVEL_STATEMENT, module => 'IBY_UTILITY_PVT.get_psr_snapshot_count');
1493 END;
1494 END IF;
1495 /* PAYMENTS_CREATED and it can be in either processing or need action group */
1496 /* IBY Terminated */
1497 ELSIF c_rec_snap.payment_service_request_status IN ('TERMINATED') AND (c_rec_snap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999))THEN
1498 l_terminated := l_terminated + 1;
1499 ELSIF c_rec_snap.payment_service_request_status IS NULL AND (c_rec_snap.status IN('CANCELED', 'CANCELLED NO PAYMENTS'))
1500 AND (c_rec_snap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999)) THEN
1501 l_terminated := l_terminated + 1;
1502 /* IBY Terminated */
1503 /* IBY Errors */
1504 ELSIF c_rec_snap.payment_service_request_status IN ('PENDING_REVIEW_DOC_VAL_ERRORS', 'PENDING_REVIEW_PMT_VAL_ERRORS') AND
1505 (c_rec_snap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999)) THEN
1506 l_errors := l_errors + 1;
1507 /* IBY Errors */
1508 /* IBY Completed */
1509 ELSIF c_rec_snap.payment_service_request_status IN ('PAYMENTS_CREATED','COMPLETED') AND
1510 (c_rec_snap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999)) AND
1511 (AP_PAYMENT_UTIL_PKG.get_payment_status_flag(c_rec_snap.payment_service_request_id) = 'Y') THEN
1512 l_completed := l_completed + 1;
1513 /* IBY Completed */
1514 END IF;
1515 END LOOP;
1516
1517 END IF;
1518 -- dbms_output.put_line('Outputs:: Action::'||l_need_action ||' Processing::'||l_processing||' Terminated::'||l_terminated||' Errors::'||l_errors||' Completed::'|| l_completed);
1519
1520 SELECT l_need_action,l_processing,l_terminated,l_errors,l_completed
1521 INTO l_ret_val
1522 FROM DUAL;
1523 --INSERTING INTO PIPE SO IT CAN BE QUERIED.
1524 PIPE ROW( l_ret_val);
1525 RETURN ;
1526 END get_psr_snapshot_pipe;
1527
1528 FUNCTION check_org_access(
1529 p_payment_service_request_id IN NUMBER)
1530 RETURN VARCHAR2
1531 IS
1532 l_instr_id NUMBER;
1533 l_access VARCHAR2(1);
1534 CURSOR c_org_access
1535 IS
1536 SELECT pmt_all.INSTR_ID,
1537 pmt_all.ORG_ID
1538 FROM
1539 (SELECT DISTINCT pmt.payment_instruction_id INSTR_ID,
1540 pmt.org_id ORG_ID
1541 FROM iby_payments_all pmt,
1542 iby_pay_instructions_all instr
1543 WHERE p_payment_service_request_id = pmt.payment_service_request_id
1544 AND instr.payment_instruction_id = pmt.payment_instruction_id
1545 AND (instr.payment_instruction_status IN ('CREATION_ERROR', 'FORMATTED_READY_TO_TRANSMIT', 'TRANSMISSION_FAILED', 'FORMATTED_READY_FOR_PRINTING',
1546 'SUBMITTED_FOR_PRINTING', 'CREATED_READY_FOR_PRINTING', 'CREATED_READY_FOR_FORMATTING', 'FORMATTED', 'CREATED')
1547 OR (instr.payment_instruction_status = 'TRANSMITTED' AND IBY_FD_USER_API_PUB.Is_transmitted_Pmt_Inst_Compl(instr.PAYMENT_INSTRUCTION_ID) = 'N'))
1548 ) pmt_all;
1549 BEGIN
1550 -- Initialize all variables.
1551 l_instr_id := NULL;
1552 l_access := 'N';
1553 FOR i_org_access IN c_org_access
1554 LOOP
1555 IF l_instr_id IS NOT NULL AND l_access = 'N' THEN
1556 RETURN 'N';
1557 END IF;
1558 l_access := 'Y'; -- Initialize for new instruction
1559 l_instr_id := i_org_access.INSTR_ID;
1560 IF MO_GLOBAL.check_access(i_org_access.ORG_ID) = 'N' THEN
1561 l_access := 'N';
1562 END IF;
1563 END LOOP;
1564
1565 -- Take care of the last instruction here.
1566 IF l_access = 'Y' THEN -- User has access to this last instruction, hence PPR
1567 RETURN 'Y';
1568 ELSE
1569 RETURN 'N';
1570 END IF;
1571 END check_org_access;
1572
1573 FUNCTION Spawn_Random_Card
1574 (p_prefix IN VARCHAR2,
1575 p_card_len IN NUMBER,
1576 p_mod_check IN VARCHAR2
1577 )
1578 RETURN VARCHAR2
1579 IS
1580 l_prefix_len NUMBER ;
1581 l_random_num NUMBER ;
1582 l_random_num_len NUMBER ;
1583 l_digit_sum NUMBER ;
1584 l_digit NUMBER ;
1585 l_ptr NUMBER ;
1586 l_psuedo_num VARCHAR2(30);
1587 BEGIN
1588 l_prefix_len := LENGTH(p_prefix);
1589 IF(l_prefix_len IS NULL) THEN
1590 l_prefix_len := 0;
1591 END IF ;
1592 IF(NVL(p_card_len,0) = 0) THEN
1593 RETURN NULL;
1594 END IF;
1595 IF(l_prefix_len >= p_card_len) THEN
1596 RETURN p_prefix;
1597 END IF;
1598 --test_debug('l_prefix_len: '|| l_prefix_len);
1599 IF (p_mod_check = 'Y') THEN
1600 l_random_num_len := p_card_len-l_prefix_len-1;
1601 ELSE
1602 l_random_num_len := p_card_len-l_prefix_len;
1603 END IF ;
1604 --test_debug('l_random_num_len: '|| l_random_num_len);
1605 --l_random_num := MOD(fnd_crypto.randomnumber, POWER(10,l_random_num_len));
1606 l_random_num := TO_NUMBER(SUBSTR(fnd_crypto.randomnumber,0,l_random_num_len));
1607 --test_debug('l_random_num: '|| l_random_num);
1608 l_psuedo_num := p_prefix || l_random_num;
1609 --test_debug('l_psuedo_num: '|| l_psuedo_num);
1610 IF (NVL(p_mod_check,'N') = 'N') THEN
1611 --test_debug('N -> returning : '|| l_psuedo_num);
1612 RETURN l_psuedo_num;
1613 END IF ;
1614 l_digit_sum := 0;
1615 FOR i IN 1..LENGTH(l_psuedo_num) LOOP
1616 l_ptr := LENGTH(l_psuedo_num) - i + 1;
1617 l_digit := TO_NUMBER(SUBSTR(l_psuedo_num, l_ptr, 1));
1618 --test_debug('l_ptr : '|| l_ptr);
1619 --test_debug('l_digit : '|| l_digit);
1620 IF(MOD(i,2) = 1) THEN
1621 l_digit := l_digit * 2;
1622 END IF ;
1623
1624 CASE l_digit
1625 WHEN 10 THEN l_digit_sum := l_digit_sum + 1;
1626 WHEN 12 THEN l_digit_sum := l_digit_sum + 3;
1627 WHEN 14 THEN l_digit_sum := l_digit_sum + 5;
1628 WHEN 16 THEN l_digit_sum := l_digit_sum + 7;
1629 WHEN 18 THEN l_digit_sum := l_digit_sum + 9;
1630 ELSE l_digit_sum := l_digit_sum + l_digit;
1631 END CASE ;
1632 --test_debug('l_digit_sum : '|| l_digit_sum);
1633 END LOOP ;
1634 l_digit := 10 - TO_NUMBER(SUBSTR(l_digit_sum,-1));
1635 IF(l_digit = 10) THEN l_digit := 0; END IF ;
1636 --test_debug('check digit : '|| l_digit);
1637 l_psuedo_num := l_psuedo_num || l_digit;
1638 RETURN l_psuedo_num;
1639 END Spawn_Random_Card;
1640
1641
1642
1643 -- This function populates psr snapshot PPRs in to GT table
1644 PROCEDURE populate_psr_snapshot_count
1645 IS
1646
1647 BEGIN
1648
1649 -- Deleting all records from GT table
1650 DELETE FROM IBY_PAY_DASHBOARD_GT;
1651 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1652 iby_debug_pub.add(debug_msg => 'Deleted all records from GT table',
1653 debug_level => FND_LOG.LEVEL_STATEMENT,
1654 module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1655 END IF;
1656
1657
1658 -- Inserting PPR's which need Action - 'NEED_ACTION_BY_ME'
1659
1660 INSERT INTO IBY_PAY_DASHBOARD_GT(PAYMENT_SERVICE_REQUEST_ID,
1661 CHECKRUN_ID,
1662 CHECKRUN_NAME,
1663 STATE)
1664 SELECT iby.payment_service_request_id,
1665 null,
1666 iby.CALL_APP_PAY_SERVICE_REQ_CODE,
1667 'NEED_ACTION_BY_ME'
1668 FROM iby_pay_service_requests iby
1669 WHERE iby.calling_app_id = 200
1670 AND iby.process_type = 'STANDARD'
1671 AND iby.payment_service_request_status IN ( 'INFORMATION_REQUIRED', 'PENDING_REVIEW_DOC_VAL_ERRORS', 'PENDING_REVIEW_PMT_VAL_ERRORS', 'PENDING_REVIEW')
1672 UNION
1673 SELECT iby.payment_service_request_id,
1674 null,
1675 iby.CALL_APP_PAY_SERVICE_REQ_CODE,
1676 'NEED_ACTION_BY_ME'
1677 FROM iby_pay_service_requests iby
1678 WHERE iby.calling_app_id = 200
1679 AND iby.process_type = 'STANDARD'
1680 AND iby.payment_service_request_status = 'PAYMENTS_CREATED'
1681 AND EXISTS
1682 (SELECT 'NEED_ACTION_BY_ME'
1683 FROM iby_payments_all pmt
1684 WHERE iby.payment_service_request_id = pmt.payment_service_request_id
1685 AND EXISTS
1686 (SELECT 'NEED_ACTION_BY_ME'
1687 FROM iby_pay_instructions_all instr
1688 WHERE instr.payment_instruction_id = pmt.payment_instruction_id
1689 AND instr.payment_instruction_status IN ('CREATION_ERROR', 'FORMATTED_READY_TO_TRANSMIT', 'TRANSMISSION_FAILED',
1690 'FORMATTED_READY_FOR_PRINTING', 'SUBMITTED_FOR_PRINTING', 'CREATED_READY_FOR_PRINTING',
1691 'CREATED_READY_FOR_FORMATTING', 'FORMATTED', 'CREATED', 'TRANSMITTED')
1692 AND instr.payments_complete_code = 'NO'
1693 AND iby_utility_pvt.check_user_access(instr.payment_instruction_id) = 'Y'
1694 )
1695 );
1696
1697 INSERT INTO IBY_PAY_DASHBOARD_GT(PAYMENT_SERVICE_REQUEST_ID,
1698 CHECKRUN_ID,
1699 CHECKRUN_NAME,
1700 STATE)
1701 SELECT null,
1702 ap.checkrun_id,
1703 ap.checkrun_name,
1704 'NEED_ACTION_BY_ME'
1705 FROM ap_inv_selection_criteria_all ap
1706 WHERE ap.status IN ('REVIEW', 'MISSING RATES' )
1707 AND NOT EXISTS ( SELECT 'NEED_ACTION_BY_ME'
1708 FROM iby_pay_service_requests iby
1709 WHERE iby.calling_app_id = 200
1710 AND iby.call_app_pay_service_req_code =
1711 ap.checkrun_name);
1712
1713 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1714 iby_debug_pub.add(debug_msg => 'Populated Need Action PPRs ',
1715 debug_level => FND_LOG.LEVEL_STATEMENT,
1716 module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1717 END IF;
1718
1719 -- Inserting PPRs which are in Processing Status - 'PROCESSING'
1720 /*Modified the query for the Bug 7560766
1721 Added a condition to identify the PPRs which
1722 are waiting for the user action*/
1723 INSERT INTO IBY_PAY_DASHBOARD_GT(PAYMENT_SERVICE_REQUEST_ID,
1724 CHECKRUN_ID,
1725 CHECKRUN_NAME,
1726 STATE)
1727 SELECT iby.payment_service_request_id,
1728 null,
1729 iby.CALL_APP_PAY_SERVICE_REQ_CODE,
1730 'PROCESSING'
1731 FROM iby_pay_service_requests iby
1732 WHERE iby.calling_app_id = 200
1733 AND iby.process_type = 'STANDARD'
1734 AND
1735 (
1736 iby.payment_service_request_status IN ('INSERTED', 'SUBMITTED',
1737 'ASSIGNMENT_COMPLETE',
1738 'DOCUMENTS_VALIDATED',
1739 'RETRY_DOCUMENT_VALIDATION',
1740 'RETRY_PAYMENT_CREATION')
1741 OR
1742 (
1743 iby.payment_service_request_status IN ('PAYMENTS_CREATED')
1744 AND EXISTS
1745 (SELECT 'PROCESSING'
1746 FROM iby_payments_all pmt
1747 WHERE
1748 pmt.payment_service_request_id = iby.payment_service_request_id
1749 AND pmt.payment_status NOT IN('REMOVED', 'VOID', 'VOID_BY_SETUP', 'VOID_BY_OVERFLOW', 'REMOVED_PAYMENT_STOPPED',
1750 'REMOVED_DOCUMENT_SPOILED', 'REMOVED_INSTRUCTION_TERMINATED', 'REMOVED_REQUEST_TERMINATED', 'ISSUED', 'TRANSMITTED', 'REJECTED',
1751 'FAILED_VALIDATION', 'FAILED_BY_CALLING_APP', 'FAILED_BY_REJECTION_LEVEL')
1752 AND pmt.payments_complete_flag <> 'Y'
1753 AND NOT EXISTS
1754 (SELECT 'NEED_ACTION'
1755 FROM iby_pay_instructions_all inst
1756 WHERE pmt.payment_instruction_id = inst.payment_instruction_id
1757 AND inst.payment_instruction_status IN('CREATION_ERROR',
1758 'FORMATTED_READY_TO_TRANSMIT',
1759 'TRANSMISSION_FAILED',
1760 'FORMATTED_READY_FOR_PRINTING',
1761 'SUBMITTED_FOR_PRINTING',
1762 'CREATED_READY_FOR_PRINTING',
1763 'CREATED_READY_FOR_FORMATTING',
1764 'FORMATTED',
1765 'CREATED',
1766 'FORMATTED_ELECTRONIC'))
1767 )
1768 )
1769 );
1770
1771
1772 INSERT INTO IBY_PAY_DASHBOARD_GT(PAYMENT_SERVICE_REQUEST_ID,
1773 CHECKRUN_ID,
1774 CHECKRUN_NAME,
1775 STATE)
1776 SELECT null,
1777 ap.checkrun_id,
1778 ap.checkrun_name,
1779 'PROCESSING'
1780 FROM ap_inv_selection_criteria_all ap
1781 WHERE ap.status IN ('UNSTARTED', 'SELECTING', 'CANCELING',
1782 'CALCULATING', 'SELECTED')
1783 AND NOT EXISTS ( SELECT 'PROCESSING'
1784 FROM iby_pay_service_requests iby
1785 WHERE iby.calling_app_id = 200
1786 AND iby.call_app_pay_service_req_code =
1787 ap.checkrun_name);
1788
1789 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1790 iby_debug_pub.add(debug_msg => 'Populated Processing PPRs ',
1791 debug_level => FND_LOG.LEVEL_STATEMENT,
1792 module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1793 END IF;
1794
1795 -- Inserting PPRs which are terminated by User --'USER_TERMINATED'
1796 INSERT INTO IBY_PAY_DASHBOARD_GT(PAYMENT_SERVICE_REQUEST_ID,
1797 CHECKRUN_ID,
1798 CHECKRUN_NAME,
1799 STATE)
1800 SELECT null,
1801 ap.checkrun_id,
1802 ap.checkrun_name,
1803 'USER_TERMINATED'
1804 FROM ap_inv_selection_criteria_all ap
1805 WHERE EXISTS ( SELECT 'IBY USER_TERMINATED'
1806 FROM iby_pay_service_requests iby
1807 WHERE iby.calling_app_id = 200
1808 AND iby.call_app_pay_service_req_code =
1809 ap.checkrun_name
1810 AND iby.payment_service_request_status IN
1811 ('TERMINATED'))
1812 AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
1813
1814 INSERT INTO IBY_PAY_DASHBOARD_GT(PAYMENT_SERVICE_REQUEST_ID,
1815 CHECKRUN_ID,
1816 CHECKRUN_NAME,
1817 STATE)
1818 SELECT null,
1819 ap.checkrun_id,
1820 ap.checkrun_name,
1821 'USER_TERMINATED'
1822 FROM ap_inv_selection_criteria_all ap
1823 WHERE ap.status IN ('CANCELED', 'CANCELLED NO PAYMENTS')
1824 AND TRUNC(ap.creation_date) =TRUNC(sysdate)
1825 AND NOT EXISTS ( SELECT 'AP USER_TERMINATED'
1826 FROM iby_pay_service_requests iby
1827 WHERE iby.calling_app_id = 200
1828 AND iby.call_app_pay_service_req_code =
1829 ap.checkrun_name)
1830 AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
1831
1832 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1833 iby_debug_pub.add(debug_msg => 'Populated User Terminated PPRs ',
1834 debug_level => FND_LOG.LEVEL_STATEMENT,
1835 module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1836 END IF;
1837
1838
1839 -- Inserting PPRs which are halted with Program Errors -'PROGRAM_ERRORS'
1840
1841 INSERT INTO IBY_PAY_DASHBOARD_GT(PAYMENT_SERVICE_REQUEST_ID,
1842 CHECKRUN_ID,
1843 CHECKRUN_NAME,
1844 STATE)
1845 SELECT null,
1846 ap.checkrun_id,
1847 ap.checkrun_name,
1848 'PROGRAM_ERRORS'
1849 FROM ap_inv_selection_criteria_all ap
1850 WHERE EXISTS ( SELECT 'PROGRAM ERRORS'
1851 FROM iby_pay_service_requests iby
1852 WHERE iby.calling_app_id = 200
1853 AND iby.call_app_pay_service_req_code =
1854 ap.checkrun_name
1855 AND iby.payment_service_request_status IN
1856 ('PENDING_REVIEW_DOC_VAL_ERRORS',
1857 'PENDING_REVIEW_PMT_VAL_ERRORS'))
1858 AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
1859
1860 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1861 iby_debug_pub.add(debug_msg => 'Populated Program Errors PPRs ',
1862 debug_level => FND_LOG.LEVEL_STATEMENT,
1863 module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1864 END IF;
1865
1866 -- Insert PPRs which are completed - 'COMPLETED'
1867
1868 INSERT INTO IBY_PAY_DASHBOARD_GT(PAYMENT_SERVICE_REQUEST_ID,
1869 CHECKRUN_ID,
1870 CHECKRUN_NAME,
1871 STATE)
1872 SELECT null,
1873 ap.checkrun_id,
1874 ap.checkrun_name,
1875 'COMPLETED'
1876 FROM ap_inv_selection_criteria_all ap
1877 WHERE EXISTS ( SELECT 'COMPLETED'
1878 FROM iby_pay_service_requests iby
1879 WHERE iby.calling_app_id = 200
1880 AND iby.call_app_pay_service_req_code =
1881 ap.checkrun_name
1882 AND iby.payment_service_request_status IN
1883 ('PAYMENTS_CREATED','COMPLETED')
1884 AND AP_PAYMENT_UTIL_PKG.get_payment_status_flag(iby.payment_service_request_id) = 'Y')
1885 AND ap.creation_date BETWEEN TRUNC(SYSDATE) AND (TRUNC(SYSDATE) + 0.99999);
1886
1887 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1888 iby_debug_pub.add(debug_msg => 'Populated Completed PPRs ',
1889 debug_level => FND_LOG.LEVEL_STATEMENT,
1890 module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1891 iby_debug_pub.add(debug_msg => 'Exit: ' || 'IBY_UTILITY_PVT.populate_psr_snapshot_count',
1892 debug_level => FND_LOG.LEVEL_STATEMENT,
1893 module => 'IBY_UTILITY_PVT.populate_psr_snapshot_count');
1894 END IF;
1895
1896 END populate_psr_snapshot_count;
1897
1898 END IBY_UTILITY_PVT;
[Home] [Help]