1 PACKAGE BODY IBY_BUILD_UTILS_PKG AS
2 /*$Header: ibyblutb.pls 120.31.12020000.3 2013/02/25 11:09:14 kchavali ship $*/
3
4 /*
5 * Declare global variables
6 */
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_BUILD_UTILS_PKG';
8 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
10
11 --
12 -- List of document statuses that are used / set in this
13 -- module.
14 --
15 DOC_STATUS_VALIDATED CONSTANT VARCHAR2(100) := 'VALIDATED';
16 DOC_STATUS_FAIL_VALID CONSTANT VARCHAR2(100) := 'FAILED_VALIDATION';
17 DOC_STATUS_PAY_CREATED CONSTANT VARCHAR2(100) := 'PAYMENT_CREATED';
18 DOC_STATUS_CA_FAILED CONSTANT VARCHAR2(100) := 'FAILED_BY_CALLING_APP';
19 DOC_STATUS_RELN_FAIL CONSTANT VARCHAR2(100) := 'FAILED_BY_RELATED_DOCUMENT';
20 DOC_STATUS_PAY_VAL_FAIL CONSTANT VARCHAR2(100) := 'PAYMENT_FAILED_VALIDATION';
21 DOC_STATUS_FAIL_BY_REJLVL CONSTANT VARCHAR2(100)
22 := 'FAILED_BY_REJECTION_LEVEL';
23
24 --
25 -- List of payment statuses that are used / set in this
26 -- module.
27 --
28 PAY_STATUS_FAIL_VALID CONSTANT VARCHAR2(100) := 'FAILED_VALIDATION';
29 PAY_STATUS_CREATED CONSTANT VARCHAR2(100) := 'CREATED';
30 PAY_STATUS_CA_FAILED CONSTANT VARCHAR2(100) := 'FAILED_BY_CALLING_APP';
31 PAY_STATUS_FAIL_BY_REJLVL CONSTANT VARCHAR2(100)
32 := 'FAILED_BY_REJECTION_LEVEL';
33 PAY_STATUS_MODIFIED CONSTANT VARCHAR2(100) := 'MODIFIED';
34
35 --
36 -- Lookups for profile applicablility types (from IBY_PMT_PROF_LOV_APL_TYPES)
37 --
38 APL_TYPE_ORG CONSTANT VARCHAR2(100) := 'PAYER_ORG';
39 APL_TYPE_PMT_METHOD CONSTANT VARCHAR2(100) := 'PAYMENT_METHOD';
40 APL_TYPE_PMT_CURRENCY CONSTANT VARCHAR2(100) := 'CURRENCY_CODE';
41 APL_TYPE_INT_BANK_ACCT CONSTANT VARCHAR2(100) := 'INTERNAL_BANK_ACCOUNT';
42
43 --
44 -- List of transaction error statuses that are used / set in this
45 -- module.
46 --
47 TRXN_ERROR_ACTIVE CONSTANT VARCHAR2(100) := 'ACTIVE';
48
49 TYPE l_prof_der_rec_type IS RECORD(
50 l_profile_id IBY_DOCS_PAYABLE_ALL.payment_profile_id%TYPE,
51 l_valid_flag VARCHAR2(1)
52 );
53
54 TYPE l_prof_der_tbl_type IS TABLE OF l_prof_der_rec_type INDEX BY VARCHAR2(2000);
55 l_prof_der_tbl l_prof_der_tbl_type;
56
57 TYPE l_prof_val_tbl_type IS TABLE OF VARCHAR2(2000) INDEX BY VARCHAR2(2000);
58 l_prof_val_tbl l_prof_val_tbl_type;
59
60 TYPE profile_cod_tbl_type is table of IBY_PAYMENT_PROFILES.system_profile_code%TYPE index by BINARY_INTEGER;
61 l_profile_cod_tbl profile_cod_tbl_type;
62
63
64 /*--------------------------------------------------------------------
65 | NAME:
66 | print_debuginfo
67 |
68 | PURPOSE:
69 |
70 |
71 | PARAMETERS:
72 | IN
73 |
74 |
75 | OUT
76 |
77 |
78 | RETURNS:
79 |
80 | NOTES:
81 |
82 *---------------------------------------------------------------------*/
83 PROCEDURE print_debuginfo(p_module IN VARCHAR2,
84 p_debug_text IN VARCHAR2,
85 p_debug_level IN VARCHAR2 DEFAULT FND_LOG.LEVEL_STATEMENT
86 )
87 IS
88 l_default_debug_level VARCHAR2(200) := FND_LOG.LEVEL_STATEMENT;
89 PRAGMA AUTONOMOUS_TRANSACTION;
90
91 BEGIN
92
93 /*
94 * Set the debug level to the value passed in
95 * (provided this value is not null).
96 */
97 IF (p_debug_level IS NOT NULL) THEN
98 l_default_debug_level := p_debug_level;
99 END IF;
100
101 /*
102 * Write immediate validation messages to the common
103 * application logs. Write deferred validation messages
104 * to the concurrent manager log file.
105 *
106 * If FND_GLOBAL.conc_request_id is -1, it implies that
107 * this method has not been invoked via the concurrent
108 * manager (online validation case; write to apps log).
109 */
110 IF (l_default_debug_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
111
112 IF (FND_GLOBAL.conc_request_id = -1) THEN
113
114 /*
115 * OPTION I:
116 * Write debug text to the common application log file.
117 */
118 --IBY_DEBUG_PUB.add(
119 -- substr(RPAD(p_module,55) || ' : ' || p_debug_text, 0, 150),
120 -- FND_LOG.G_CURRENT_RUNTIME_LEVEL,
121 -- 'iby.plsql.IBY_VALIDATIONSETS_PUB'
122 -- );
123
124 FND_LOG.STRING(
125 l_default_debug_level,
126 'iby.plsql.IBY_VALIDATIONSETS_PUB',
127 substr(RPAD(p_module, 55) || ' : ' || p_debug_text, 0, 150)
128 );
129
130 /*
131 * OPTION II:
132 * Write debug text to DBMS output file.
133 */
134 --DBMS_OUTPUT.PUT_LINE(substr(RPAD(p_module,40)||' : '||
135 -- p_debug_text, 0, 150));
136
137 /*
138 * OPTION III:
139 * Write debug text to temporary table.
140 */
141
142 /*
143 * Use this script to create a debug table.
144 *
145 * CREATE TABLE TEMP_IBY_LOGS(TEXT VARCHAR2(4000), TIME DATE);
146 */
147
148 /* uncomment these two lines for debugging */
149 --INSERT INTO TEMP_IBY_LOGS VALUES (p_module || ': '
150 -- || p_debug_text, sysdate);
151 --COMMIT;
152
153 ELSE
154
155 /*
156 * OPTION I:
157 * Write debug text to the concurrent manager log file.
158 */
159 FND_FILE.PUT_LINE(FND_FILE.LOG, p_module || ': ' || p_debug_text);
160
161 /*
162 * OPTION II:
163 * Write debug text to DBMS output file.
164 */
165 --DBMS_OUTPUT.PUT_LINE(substr(RPAD(p_module,40)||' : '||
166 -- p_debug_text, 0, 150));
167
168 /*
169 * OPTION III:
170 * Write debug text to temporary table.
171 */
172
173 /*
174 * Use this script to create a debug table.
175 *
176 * CREATE TABLE TEMP_IBY_LOGS(TEXT VARCHAR2(4000), TIME DATE);
177 */
178
179 /* uncomment these two lines for debugging */
180 --INSERT INTO TEMP_IBY_LOGS VALUES (p_module || ': '
181 -- || p_debug_text, sysdate);
182 --COMMIT;
183
184 END IF; -- if not a conc request
185
186 END IF; -- if current debug level greater than runtime debug level
187
188 EXCEPTION
189 WHEN OTHERS THEN
190
191 ROLLBACK;
192
193 END print_debuginfo;
194
195 /*--------------------------------------------------------------------
196 | NAME:
197 | printXMLClob
198 |
199 | PURPOSE:
200 | Prints out the XML data contained within the given clob; to be
201 | used for testing purposes.
202 |
203 |
204 | PARAMETERS:
205 | IN
206 |
207 | OUT
208 |
209 |
210 | RETURNS:
211 |
212 | NOTES:
213 |
214 *---------------------------------------------------------------------*/
215 PROCEDURE printXMLClob(
216 p_xml_clob IN CLOB)
217 IS
218
219 l_xmlstr VARCHAR2(32767);
220 l_line VARCHAR2(2000);
221 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.printXMLClob';
222
223 BEGIN
224
225 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
226 print_debuginfo(l_module_name, 'ENTER');
227
228 END IF;
229 /*
230 * Do not attempt to print XML clob unless it contains
231 * CR-LFs.
232 */
233 IF (DBMS_LOB.INSTR(p_xml_clob, fnd_global.local_chr(10)) = 0) THEN
234
235 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
236 print_debuginfo(l_module_name, 'Clob does not contain '
237 || 'CR-LFs; cannot print entire XML Clob, '
238 || 'printing a snippet ..'
239 );
240
241 print_debuginfo(l_module_name, TO_CHAR(p_xml_clob));
242 print_debuginfo(l_module_name, 'EXIT');
243
244 END IF;
245 RETURN;
246
247 END IF;
248
249
250 /*
251 * Print the clob as an XML fragment.
252 */
253 l_xmlstr := dbms_lob.substr(p_xml_clob, 32767);
254 LOOP
255
256 EXIT WHEN l_xmlstr IS NULL;
257
258 l_line := SUBSTR(l_xmlstr,1,INSTR(l_xmlstr,
259 fnd_global.local_chr(10))-1);
260
261 IF (l_line IS NOT NULL) THEN
262
263 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
264 print_debuginfo('', l_line);
265
266 END IF;
267 l_xmlstr := SUBSTR(l_xmlstr,INSTR(l_xmlstr,
268 fnd_global.local_chr(10))+1);
269
270 ELSE
271
272 /*
273 * Fix for bug 5550778:
274 *
275 * Print the last chunk and set the
276 * xml string to null so that we do
277 * not go into an infinite loop
278 * in case we cannot find a '\n'
279 * in the XML clob.
280 */
281 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
282 print_debuginfo('', l_xmlstr);
283
284 END IF;
285 l_xmlstr := NULL;
286
287 END IF;
288
289 END LOOP;
290
291 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
292 print_debuginfo(l_module_name, 'EXIT');
293
294 END IF;
295 END printXMLClob;
296
297 /*--------------------------------------------------------------------
298 | NAME:
299 | checkForInvalidChars
300 |
301 | PURPOSE:
302 | Given an input string and a string of invalid characters, this
303 | procedure checks if any of the invalid characters are present
304 | in the given input string.
305 |
306 | If any of the invalid characters are found, an error message is
307 | inserted into the error table identifying the invalid character.
308 |
309 | If no invalid characters are found in the input string, this
310 | procedure simply returns.
311 |
312 | PARAMETERS:
313 | IN
314 | p_fieldName - The name of the input field (used for logging)
315 | p_fieldValue - The value of the input field
316 | p_isComposite - Indidicates that the field is a composite
317 | field created by appending multiple individual
318 | fields. For composite fields, the field value
319 | will not be printed in the error message.
320 | p_invalid_chars_list - List of invalid characters that
321 | should not be present in the field value
322 | OUT
323 | x_docErrorRec - Error message record. This should contain the
324 | document id when coming into this method.
325 | x_docErrorTab - Error messages table. An error message will be
326 | added to this table if the input field value
327 | contains an invalid char.
328 |
329 | RETURNS:
330 |
331 | NOTES:
332 |
333 *---------------------------------------------------------------------*/
334 PROCEDURE checkForInvalidChars(
335 p_fieldName IN VARCHAR2,
336 p_fieldValue IN VARCHAR2,
337 p_isComposite IN BOOLEAN,
338 p_validCharsList IN VARCHAR2,
339 p_invalidCharsList IN VARCHAR2,
340 x_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
341 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
342 docErrorTabType
343 )
344 IS
345 l_error_code VARCHAR2(100);
346 l_error_msg VARCHAR2(500);
347
348 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.checkForInvalidChars';
349
350 /*
351 * This dummy string is used as the third argument to the TRANSLATE()
352 * function. Only used if invalid chars list is provided.
353 *
354 * The value of the string does not matter. It is used as a mask;
355 * translate() will substitute any invalid chars in the field value
356 * with a dummy value from this string. We will diff these two strings
357 * to find the invalid characters.
358 */
359 l_inv_mask VARCHAR2(200) :=
360 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
361
362 /*
363 * Dummy string used as mask for checking valid characters. Only used
364 * if valid chars list is provided.
365 */
366 l_val_mask VARCHAR2(20) := ' ';
367
368 l_translated_value VARCHAR2(200);
369 l_invalid_chars_found VARCHAR2(200);
370 l_error_string VARCHAR2(1000);
371
372 BEGIN
373
374 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
375 print_debuginfo(l_module_name, 'ENTER');
376
377 END IF;
378 /*
379 * Don't do anything if the given value is null.
380 */
381 IF (p_fieldValue IS NULL) THEN
382 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
383 print_debuginfo(l_module_name, 'Value provided for field '''
384 || p_fieldName
385 || ''' is null. Skipping character validation'
386 );
387 print_debuginfo(l_module_name, 'EXIT');
388 END IF;
389 RETURN;
390 ELSE
391 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
392 print_debuginfo(l_module_name, 'Value provided for field '''
393 || p_fieldName || ''' is '
394 || p_fieldValue
395 );
396 END IF;
397 END IF;
398
399 /*
400 * MAIN CHARACTER VALIDATION LOGIC
401 */
402 /* character validation using valid chars list */
403 IF (p_validCharsList IS NOT NULL) THEN
404
405 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
406 print_debuginfo(l_module_name, 'Checking using valid chars list..');
407
408 END IF;
409 /*
410 * HOW DOES IT WORK?
411 *
412 * Given a list of valid characters, we call translate like this:
413 *
414 * translate(
415 * -- given string
416 * 'Oracle 9i',
417 * -- valid chars list
418 * 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
419 * -- mask
420 * ' '
421 * );
422 *
423 * This returns '9'. And so we caught the invalid character.
424 */
425
426 /*
427 * Call translate() to filter out the invalid chars.
428 */
429 SELECT translate(p_fieldValue, p_validCharsList, l_val_mask)
430 INTO l_translated_value
431 FROM DUAL;
432
433 /* strip spaces */
434 l_translated_value := LTRIM(l_translated_value);
435 l_translated_value := RTRIM(l_translated_value);
436
437 /*
438 * This means that all characters in the field value were
439 * found in the valid char list. So the value contains
440 * only valid characters. No invalid chars; Exit at this point.
441 */
442 IF (l_translated_value IS NULL) THEN
443 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
444 print_debuginfo(l_module_name, 'EXIT');
445 END IF;
446 RETURN;
447 ELSE
448 /*
449 * This means that some characters were present in the
450 * field value that could not be found in the valid char
451 * list. Therefore, invalid characters are present in this
452 * field.
453 */
454 l_invalid_chars_found := l_translated_value;
455 END IF;
456
457 ELSE
458 /* character validation using invalid chars list */
459 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
460 print_debuginfo(l_module_name, 'Checking using invalid chars list..');
461
462 END IF;
463 /*
464 * HOW DOES IT WORK?
465 *
466 * Given a list of invalid characters, we call translate like this:
467 *
468 * translate(
469 * -- given string
470 * '[email protected]',
471 * -- invalid chars list
472 * '@#$'
473 * -- mask
474 * 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
475 * );
476 *
477 * This returns 'rameshaoracle.com'.
478 *
479 * We diff the input string and the translated string to get '@'
480 * as the invalid character.
481 */
482
483 /*
484 * Call translate to substitute any invalid chars in
485 * the given string with dummy ones.
486 */
487 SELECT translate(p_fieldValue, p_invalidCharsList, l_inv_mask)
488 INTO l_translated_value
489 FROM DUAL;
490
491 /*
492 * If the translated value is not equal to the original
493 * field value, it means that there were some invalid
494 * characters in the field value.
495 *
496 * Diff the original and translated values to get the
497 * invalid characters.
498 */
499 IF (p_fieldValue <> l_translated_value) THEN
500 l_invalid_chars_found := diffStrings(p_fieldValue,
501 l_translated_value);
502 END IF;
503
504 END IF; -- valid chars list is not null
505
506 IF (l_invalid_chars_found IS NOT NULL) THEN
507
508 /*
509 * Do not show the field value for composite fields.
510 */
511 IF (p_isComposite = true) THEN
512
513 l_error_string := 'Found invalid char(s) '
514 || '''' || l_invalid_chars_found || ''''
515 || ' in field '
516 || '''' || p_fieldName || ''''
517 ;
518
519 l_error_code := 'IBY_FOUND_INVALID_CHARS';
520 FND_MESSAGE.set_name('IBY', l_error_code);
521
522 FND_MESSAGE.SET_TOKEN('INV_CHARS',
523 l_invalid_chars_found,
524 FALSE);
525
526 FND_MESSAGE.SET_TOKEN('FIELD_NAME',
527 p_fieldName,
528 FALSE);
529
530 ELSE
531
532 l_error_string := 'Found invalid char(s) '
533 || '''' || l_invalid_chars_found || ''''
534 || ' in field '
535 || '''' || p_fieldName || ''''
536 || ' with value '
537 || '''' || p_fieldValue || ''''
538 ;
539
540 l_error_code := 'IBY_INVALID_CHARS_IN_DATA';
541 FND_MESSAGE.set_name('IBY', l_error_code);
542
543 FND_MESSAGE.SET_TOKEN('INV_CHARS',
544 l_invalid_chars_found,
545 FALSE);
546
547 FND_MESSAGE.SET_TOKEN('FIELD_NAME',
548 p_fieldName,
549 FALSE);
550
551 FND_MESSAGE.SET_TOKEN('FIELD_VALUE',
552 p_fieldValue,
553 FALSE);
554
555 END IF;
556
557 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
558 print_debuginfo(l_module_name, l_error_string);
559
560 END IF;
561 /*
562 * Add error code and error message to the
563 * validation errors table
564 */
565 x_docErrorRec.error_code := l_error_code;
566 x_docErrorRec.error_message := FND_MESSAGE.get;
567
568 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
569 x_docErrorRec, x_docErrorTab);
570
571 END IF;
572
573 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
574 print_debuginfo(l_module_name, 'EXIT');
575
576 END IF;
577 END checkForInvalidChars;
578
579 /*--------------------------------------------------------------------
580 | NAME:
581 | getChar
582 |
583 | PURPOSE:
584 | Returns the character at a given position of a given string.
585 | Similar to ChatAt(index) in Java.
586 |
587 | Note that in PLSQL indexes are 1 based i.e., the very first
588 | character in the string has index 1 (as opposed to 0 based
589 | indexes in C and Java).
590 |
591 | Therefore, if the given string is 'I am the walrus', then
592 | getChar(str, 10) will return 'w'.
593 |
594 | NULL will be returned if the index is out-of-bounds.
595 |
596 | PARAMETERS:
597 | IN
598 | p_string - The name of the input field (used for logging)
599 | p_index - The value of the input field
600 |
601 | OUT
602 |
603 | RETURNS:
604 |
605 | VARCHAR2(1) - the character at the given index on the given string
606 |
607 | NOTES:
608 |
609 *---------------------------------------------------------------------*/
610 FUNCTION getChar(
611 p_string IN VARCHAR2,
612 p_index IN VARCHAR2
613 )
614 RETURN VARCHAR2
615 IS
616 l_char VARCHAR2(1);
617 BEGIN
618
619 l_char := SUBSTR(p_string, p_index, 1);
620 RETURN l_char;
621
622 END getChar;
623
624 /*--------------------------------------------------------------------
625 | NAME:
626 | diffStrings
627 |
628 | PURPOSE:
629 | Given two strings, this method performs a diff on them
630 | and returns their differences.
631 |
632 | The two args are not expected to be completely arbitary;
633 | instead arg2 is expected to be similar to arg1 with some
634 | differences. For this reason, both strings are expected to
635 | be of the same length.
636 |
637 | Whenever there are differences, the value from arg1 is added
638 | to the diff list. The value from arg2 is ignored.
639 |
640 | PARAMETERS:
641 | IN
642 | p_originalString - The original string
643 | p_compareString - The changed string
644 |
645 | OUT
646 |
647 | RETURNS:
648 |
649 | VARCHAR2 - the difference between the two strings
650 | NULL will be returned if both strings are the same
651 |
652 | NOTES:
653 | Comparison between the two strings will only take place uptil the
654 | length of the original string.
655 |
656 *---------------------------------------------------------------------*/
657 FUNCTION diffStrings(
658 p_originalString IN VARCHAR2,
659 p_compareString IN VARCHAR2
660 )
661 RETURN VARCHAR2
662 IS
663 l_orgChar VARCHAR2(1);
664 l_compChar VARCHAR2(1);
665 l_diffString VARCHAR2(2000);
666 l_length NUMBER(10);
667 BEGIN
668
669 IF (p_originalString = p_compareString) THEN
670 RETURN NULL;
671 END IF;
672
673 /*
674 * Compare the strings char-by-char and store the
675 * differences
676 */
677 l_diffString := '';
678 l_length := LEAST(length(p_originalString), length(p_compareString));
679
680 FOR i in 1 .. l_length LOOP
681
682 l_orgChar := SUBSTR(p_originalString, i, 1);
683 l_compChar := SUBSTR(p_compareString, i, 1);
684
685 IF (l_orgChar <> l_compChar) THEN
686 l_diffString := l_diffString || l_orgChar;
687 END IF;
688
689 END LOOP;
690
691 /*
692 * Return NULL if we were unable to find
693 * any differences.
694 */
695 IF (l_diffString = '') THEN
696 l_diffString := NULL;
697 END IF;
698
699 RETURN l_diffString;
700
701 END diffStrings;
702
703 /*--------------------------------------------------------------------
704 | NAME:
705 | createErrorRecord
706 |
707 | PURPOSE:
708 |
709 | PARAMETERS:
710 | IN
711 |
712 | OUT
713 |
714 |
715 | RETURNS:
716 |
717 | NOTES:
718 |
719 *---------------------------------------------------------------------*/
720 PROCEDURE createErrorRecord(
721 p_trxn_type IN IBY_TRANSACTION_ERRORS.transaction_type%TYPE,
722 p_doc_id IN IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE,
723 p_doc_status IN IBY_DOCS_PAYABLE_ALL.document_status%TYPE,
724 p_ca_id IN IBY_DOCS_PAYABLE_ALL.calling_app_id%TYPE,
725 p_ca_doc_id1 IN IBY_DOCS_PAYABLE_ALL.
726 calling_app_doc_unique_ref1%TYPE,
727 p_ca_doc_id2 IN IBY_DOCS_PAYABLE_ALL.
728 calling_app_doc_unique_ref2%TYPE,
729 p_ca_doc_id3 IN IBY_DOCS_PAYABLE_ALL.
730 calling_app_doc_unique_ref3%TYPE,
731 p_ca_doc_id4 IN IBY_DOCS_PAYABLE_ALL.
732 calling_app_doc_unique_ref4%TYPE,
733 p_ca_doc_id5 IN IBY_DOCS_PAYABLE_ALL.
734 calling_app_doc_unique_ref5%TYPE,
735 p_pp_tt_cd IN IBY_DOCS_PAYABLE_ALL.pay_proc_trxn_type_code%TYPE,
736 x_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
737 x_docTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType,
738 p_rel_doc_id IN IBY_DOCS_PAYABLE_ALL.calling_app_doc_ref_number%TYPE -- AWT Enh 16296267
739 DEFAULT NULL,
740 p_error_code IN VARCHAR2 DEFAULT NULL
741 )
742 IS
743 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.createErrorRecord';
744 l_error_code VARCHAR2(100);
745 l_error_msg VARCHAR2(500);
746
747 l_token_rec IBY_TRXN_ERROR_TOKENS%ROWTYPE;
748
749 BEGIN
750
751 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
752 print_debuginfo(l_module_name, 'ENTER');
753 print_debuginfo(l_module_name, 'Transaction type: ' || p_trxn_type);
754 print_debuginfo(l_module_name, 'Transaction status: ' || p_doc_status);
755
756 END IF;
757 x_docErrorRec.validation_set_code := 'CORE_PAYMENT_VALIDATION';
758 x_docErrorRec.transaction_type := p_trxn_type;
759
760 /*
761 * Fix for bug 5085226:
762 *
763 * Initialize the transaction error id to null for
764 * the error record, each time we enter this method.
765 *
766 * This prevents the previously set transaction error
767 * id from persisting in the x_docErrorRec for a
768 * new record.
769 */
770 x_docErrorRec.transaction_error_id
771 := NULL;
772
773 x_docErrorRec.transaction_id := p_doc_id;
774 x_docErrorRec.calling_app_id := p_ca_id;
775 x_docErrorRec.calling_app_doc_unique_ref1
776 := p_ca_doc_id1;
777 x_docErrorRec.calling_app_doc_unique_ref2
778 := p_ca_doc_id2;
779 x_docErrorRec.calling_app_doc_unique_ref3
780 := p_ca_doc_id3;
781 x_docErrorRec.calling_app_doc_unique_ref4
782 := p_ca_doc_id4;
783 x_docErrorRec.calling_app_doc_unique_ref5
784 := p_ca_doc_id5;
785 x_docErrorRec.pay_proc_trxn_type_code
786 := p_pp_tt_cd;
787 x_docErrorRec.error_date := sysdate;
788 x_docErrorRec.error_status := TRXN_ERROR_ACTIVE;
789
790 /*
791 * Error codes are mapped for certain document/payment
792 * statuses. Get the error code corresponding to the
793 * document/payment status.
794 */
795 IF (p_error_code IS NULL) THEN
796
797 CASE p_doc_status
798 WHEN DOC_STATUS_CA_FAILED THEN
799 l_error_code := 'IBY_DOC_FAILED_BY_CALL_APP';
800 WHEN DOC_STATUS_RELN_FAIL THEN
801 l_error_code := 'IBY_DOC_FAILED_BY_RELATED_DOC';
802 WHEN DOC_STATUS_PAY_VAL_FAIL THEN
803 l_error_code := 'IBY_DOC_FAILED_BY_PARENT_PMT';
804 WHEN DOC_STATUS_FAIL_BY_REJLVL THEN
805 l_error_code := 'IBY_DOC_FAILED_BY_REJ_LVL';
806 WHEN PAY_STATUS_FAIL_VALID THEN
807 l_error_code := 'IBY_PMT_FAILED_VALIDATION';
808 WHEN PAY_STATUS_CA_FAILED THEN
809 l_error_code := 'IBY_PMT_FAILED_BY_CALL_APP';
810 WHEN PAY_STATUS_FAIL_BY_REJLVL THEN
811 l_error_code := 'IBY_PMT_FAILED_BY_REJ_LVL';
812 ELSE
813 /* generic error message */
814 l_error_code := 'IBY_GENERIC_ERROR_MSG';
815 END CASE;
816
817 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
818 print_debuginfo(l_module_name, 'Error code derived from '
819 || 'doc/pmt status: '
820 || l_error_code);
821 END IF;
822 ELSE
823
824 l_error_code := p_error_code;
825
826 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
827 print_debuginfo(l_module_name, 'Error code based on input param: '
828 || l_error_code
829 );
830
831 END IF;
832 END IF;
833
834 FND_MESSAGE.set_name('IBY', l_error_code);
835
836 /*
837 * Error tokens are mapped for certain error messages.
838 */
839 CASE l_error_code
840 WHEN 'IBY_DOC_FAILED_BY_RELATED_DOC' THEN
841 FND_MESSAGE.SET_TOKEN('RELDOC', p_rel_doc_id, FALSE);
842 l_token_rec.token_name := 'RELDOC';
843 l_token_rec.token_value := p_rel_doc_id;
844 WHEN 'IBY_DOC_FAILED_BY_REJ_LVL' THEN
845 FND_MESSAGE.SET_TOKEN('TRGDOC', p_rel_doc_id, FALSE);
846 l_token_rec.token_name := 'TRGDOC';
847 l_token_rec.token_value := p_rel_doc_id;
848 WHEN 'IBY_PMT_FAILED_BY_REJ_LVL' THEN
849 FND_MESSAGE.SET_TOKEN('TRGPMT', p_rel_doc_id, FALSE);
850 l_token_rec.token_name := 'TRGPMT';
851 l_token_rec.token_value := p_rel_doc_id;
852 ELSE
853 /* do nothing */
854 NULL;
855 END CASE;
856
857 l_error_msg := FND_MESSAGE.get;
858
859 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
860 print_debuginfo(l_module_name, 'Error message derived from error code: '
861 || l_error_msg);
862
863 END IF;
864 x_docErrorRec.error_code := l_error_code;
865 x_docErrorRec.error_message := l_error_msg;
866
867 /*
868 * If we created a token record, add it to the token
869 * records table.
870 */
871 IF (l_token_rec.token_name IS NOT NULL) THEN
872 x_docTokenTab(x_docTokenTab.COUNT + 1) := l_token_rec;
873 END IF;
874
875 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
876 print_debuginfo(l_module_name, 'EXIT');
877
878 END IF;
879 END createErrorRecord;
880
881 /*--------------------------------------------------------------------
882 | NAME:
883 | createPmtErrorRecord
884 |
885 | PURPOSE:
886 |
887 | PARAMETERS:
888 | IN
889 |
890 | OUT
891 |
892 |
893 | RETURNS:
894 |
895 | NOTES:
896 |
897 *---------------------------------------------------------------------*/
898 PROCEDURE createPmtErrorRecord(
899 p_pmt_id IN IBY_PAYMENTS_ALL.payment_id%TYPE,
900 p_pmt_status IN IBY_PAYMENTS_ALL.
901 payment_status%TYPE,
902 p_error_code IN IBY_TRANSACTION_ERRORS.error_code%TYPE
903 DEFAULT NULL,
904 p_error_msg IN IBY_TRANSACTION_ERRORS.error_message%TYPE
905 DEFAULT NULL,
906 x_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE
907 )
908 IS
909
910 l_module_name VARCHAR2(200) := G_PKG_NAME || '.createPmtErrorRecord';
911
912 BEGIN
913
914 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
915 print_debuginfo(l_module_name, 'ENTER');
916
917 END IF;
918 x_docErrorRec.validation_set_code := 'CORE_PAYMENT_VALIDATION';
919 x_docErrorRec.transaction_type := 'PAYMENT';
920
921 x_docErrorRec.transaction_id := p_pmt_id;
922
923 x_docErrorRec.error_date := sysdate;
924 x_docErrorRec.error_status := TRXN_ERROR_ACTIVE;
925
926 x_docErrorRec.error_code := p_error_code;
927 x_docErrorRec.error_message := p_error_msg;
928
929 x_docErrorRec.override_allowed_on_error_flag
930 := 'N';
931
932 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
933 print_debuginfo(l_module_name, 'EXIT');
934
935 END IF;
936 END createPmtErrorRecord;
937
938 PROCEDURE getProfListFromProfileDrivers(
939 p_pmt_method_cd IN IBY_DOCS_PAYABLE_ALL.payment_method_code%TYPE,
940 p_org_id IN IBY_DOCS_PAYABLE_ALL.org_id%TYPE,
941 p_org_type IN IBY_DOCS_PAYABLE_ALL.org_type%TYPE,
942 p_pmt_currency IN IBY_DOCS_PAYABLE_ALL.payment_currency_code%TYPE,
943 p_int_bank_acct_id IN IBY_DOCS_PAYABLE_ALL.internal_bank_account_id%TYPE,
944 p_profile_id IN OUT NOCOPY IBY_DOCS_PAYABLE_ALL.payment_profile_id%TYPE,
945 p_valid_flag OUT NOCOPY VARCHAR2
946 )
947 IS
948 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
949 '.getProfListFromProfileDrivers';
950 l_prof_val_indx VARCHAR2(2000);
951 l_prof_der_indx VARCHAR2(2000);
952
953 BEGIN
954
955 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
956 print_debuginfo(l_module_name, 'ENTER');
957
958 print_debuginfo(l_module_name, 'Checking for profiles '
959 || 'applicable for given org id '
960 || p_org_id
961 || ' and org type '
962 || p_org_type
963 || ' and payment method '
964 || p_pmt_method_cd
965 || ' and payment currency '
966 || p_pmt_currency
967 || ' and internal bank account '
968 || p_int_bank_acct_id
969 || ' combination ...'
970 );
971
972 print_debuginfo(l_module_name, 'ENTER');
973
974
975 END IF;
976 IF(p_profile_id is not null) THEN
977 l_prof_val_indx := p_profile_id||'$'||p_org_id||'$'||p_org_type||'$'||p_pmt_method_cd||'$'||p_pmt_currency||'$'||p_int_bank_acct_id;
978 IF(l_prof_val_indx is not null) THEN
979 IF(l_prof_val_tbl.EXISTS(l_prof_val_indx)) THEN
980 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
981 print_debuginfo(l_module_name, 'Fetching the value from Cache Structure l_prof_val_tbl' || l_prof_val_indx);
982 END IF;
983 p_valid_flag := l_prof_val_tbl(l_prof_val_indx);
984 ELSE
985 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
986 print_debuginfo(l_module_name, 'Fetching the value from the DB for l_prof_val_tbl' || l_prof_val_indx);
987 END IF;
988 BEGIN
989 SELECT
990 'Y'
991 INTO p_valid_flag
992 FROM
993 IBY_APPLICABLE_PMT_PROFS app1,
994 IBY_APPLICABLE_PMT_PROFS app2,
995 IBY_APPLICABLE_PMT_PROFS app3,
996 IBY_APPLICABLE_PMT_PROFS app4,
997 IBY_SYS_PMT_PROFILES_B sys_prof,
998 IBY_ACCT_PMT_PROFILES_B acct_prof
999 WHERE
1000 /*
1001 * org id is a special case; since each org id has meaning
1002 * only within an org type, we need to always use
1003 * (org id, org type) as a combination.
1004 *
1005 * This means that both org id and org type need to be
1006 * stored in the same row. In order to simplify matters,
1007 * the org type field will be stored in the 'applicable
1008 * value from' column wherever org id is stored.
1009 *
1010 * If org id is null, do not check org type - special case.
1011 */
1012 (app1.applicable_type_code=APL_TYPE_ORG AND
1013 (app1.applicable_value_to IS NULL) OR
1014 (app1.applicable_value_to=TO_CHAR(p_org_id) AND
1015 app1.applicable_value_from=p_org_type))
1016 AND (app2.applicable_type_code=APL_TYPE_PMT_METHOD AND
1017 (app2.applicable_value_to=p_pmt_method_cd OR
1018 app2.applicable_value_to IS NULL))
1019 AND (app3.applicable_type_code=APL_TYPE_PMT_CURRENCY AND
1020 (app3.applicable_value_to=p_pmt_currency OR
1021 app3.applicable_value_to IS NULL))
1022 AND (app4.applicable_type_code=APL_TYPE_INT_BANK_ACCT AND
1023 (app4.applicable_value_to=TO_CHAR(p_int_bank_acct_id) OR
1024 app4.applicable_value_to IS NULL))
1025 AND
1026 app1.system_profile_code=app2.system_profile_code
1027 AND
1028 app2.system_profile_code=app3.system_profile_code
1029 AND
1030 app3.system_profile_code=app4.system_profile_code
1031 AND
1032 app4.system_profile_code=app1.system_profile_code
1033 AND
1034 app1.system_profile_code=sys_prof.system_profile_code
1035 AND
1036 sys_prof.system_profile_code=acct_prof.system_profile_code(+)
1037 /*
1038 * Fix for bug 5929889:
1039 *
1040 * Filter profiles by inactive date so that we do not
1041 * pick up end-dated profiles.
1042 */
1043 AND NVL(sys_prof.inactive_date, SYSDATE + 1) > SYSDATE
1044 AND acct_prof.payment_profile_id = p_profile_id;
1045 EXCEPTION
1046 WHEN TOO_MANY_ROWS THEN
1047 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1048 print_debuginfo(l_module_name, 'Modifying the value of the p_valid_flag when the Exception is TOO_MANY_ROWS for l_prof_val_tbl');
1049 END IF;
1050 p_valid_flag := 'Y';
1051 WHEN OTHERS THEN
1052 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1053 print_debuginfo(l_module_name, 'Modifying the value of the p_valid_flag when the Exception is OTHERS for l_prof_val_tbl');
1054 END IF;
1055 p_valid_flag := 'N';
1056 END;
1057 l_prof_val_tbl(l_prof_val_indx) := p_valid_flag;
1058 END IF;
1059 END IF;
1060 ELSE
1061 l_prof_der_indx := p_org_id||'$'||p_org_type||'$'||p_pmt_method_cd||'$'||p_pmt_currency||'$'||p_int_bank_acct_id;
1062 IF(l_prof_der_indx is not null) THEN
1063 IF(l_prof_der_tbl.EXISTS(l_prof_der_indx)) THEN
1064 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1065 print_debuginfo(l_module_name, 'Fetching the value from Cache Structure l_prof_der_tbl' || l_prof_der_indx);
1066 END IF;
1067 p_profile_id := l_prof_der_tbl(l_prof_der_indx).l_profile_id;
1068 p_valid_flag := l_prof_der_tbl(l_prof_der_indx).l_valid_flag;
1069 ELSE
1070 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1071 print_debuginfo(l_module_name, 'Fetching the value from the DB for l_prof_der_tbl' || l_prof_der_indx);
1072 END IF;
1073 BEGIN
1074 SELECT
1075 acct_prof.payment_profile_id, 'Y'
1076 INTO p_profile_id, p_valid_flag
1077 FROM
1078 IBY_APPLICABLE_PMT_PROFS app1,
1079 IBY_APPLICABLE_PMT_PROFS app2,
1080 IBY_APPLICABLE_PMT_PROFS app3,
1081 IBY_APPLICABLE_PMT_PROFS app4,
1082 IBY_SYS_PMT_PROFILES_B sys_prof,
1083 IBY_ACCT_PMT_PROFILES_B acct_prof
1084 WHERE
1085 /*
1086 * org id is a special case; since each org id has meaning
1087 * only within an org type, we need to always use
1088 * (org id, org type) as a combination.
1089 *
1090 * This means that both org id and org type need to be
1091 * stored in the same row. In order to simplify matters,
1092 * the org type field will be stored in the 'applicable
1093 * value from' column wherever org id is stored.
1094 *
1095 * If org id is null, do not check org type - special case.
1096 */
1097 (app1.applicable_type_code=APL_TYPE_ORG AND
1098 (app1.applicable_value_to IS NULL) OR
1099 (app1.applicable_value_to=TO_CHAR(p_org_id) AND
1100 app1.applicable_value_from=p_org_type))
1101 AND (app2.applicable_type_code=APL_TYPE_PMT_METHOD AND
1102 (app2.applicable_value_to=p_pmt_method_cd OR
1103 app2.applicable_value_to IS NULL))
1104 AND (app3.applicable_type_code=APL_TYPE_PMT_CURRENCY AND
1105 (app3.applicable_value_to=p_pmt_currency OR
1106 app3.applicable_value_to IS NULL))
1107 AND (app4.applicable_type_code=APL_TYPE_INT_BANK_ACCT AND
1108 (app4.applicable_value_to=TO_CHAR(p_int_bank_acct_id) OR
1109 app4.applicable_value_to IS NULL))
1110 AND
1111 app1.system_profile_code=app2.system_profile_code
1112 AND
1113 app2.system_profile_code=app3.system_profile_code
1114 AND
1115 app3.system_profile_code=app4.system_profile_code
1116 AND
1117 app4.system_profile_code=app1.system_profile_code
1118 AND
1119 app1.system_profile_code=sys_prof.system_profile_code
1120 AND
1121 sys_prof.system_profile_code=acct_prof.system_profile_code(+)
1122 /*
1123 * Fix for bug 5929889:
1124 *
1125 * Filter profiles by inactive date so that we do not
1126 * pick up end-dated profiles.
1127 */
1128 AND NVL(sys_prof.inactive_date, SYSDATE + 1) > SYSDATE;
1129 EXCEPTION
1130 WHEN TOO_MANY_ROWS THEN
1131 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1132 print_debuginfo(l_module_name, 'Modifying the value of the p_valid_flag when the Exception is TOO_MANY_ROWS for l_prof_der_tbl');
1133 END IF;
1134 p_valid_flag := 'M';
1135 p_profile_id := -1;
1136 WHEN OTHERS THEN
1137 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1138 print_debuginfo(l_module_name, 'Modifying the value of the p_valid_flag when the Exception is OTHERS for l_prof_der_tbl');
1139 END IF;
1140 p_valid_flag := 'N';
1141 p_profile_id := -1;
1142 END;
1143 l_prof_der_tbl(l_prof_der_indx).l_valid_flag := p_valid_flag;
1144 l_prof_der_tbl(l_prof_der_indx).l_profile_id := p_profile_id;
1145 END IF;
1146 END IF;
1147 END IF;
1148
1149 EXCEPTION
1150 WHEN OTHERS THEN
1151 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1152 print_debuginfo(l_module_name, 'Non-Fatal: Exception when '
1153 || 'attempting to get payment profile for given (payment '
1154 || 'method, org, currency, int bank acct) '
1155 || 'combination.'
1156 );
1157 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
1158 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
1159
1160 print_debuginfo(l_module_name, 'EXIT');
1161
1162 END IF;
1163 END getProfListFromProfileDrivers;
1164
1165
1166 /*--------------------------------------------------------------------
1167 | NAME:
1168 | getProfListFromProfileDrivers
1169 |
1170 | PURPOSE:
1171 | Derives the payment profile for the given (payment method,
1172 | org, payment currency, internal bank acct)
1173 | combination.
1174 |
1175 | PARAMETERS:
1176 | IN
1177 |
1178 | OUT
1179 |
1180 |
1181 | RETURNS:
1182 |
1183 |
1184 | NOTES:
1185 |
1186 *---------------------------------------------------------------------*/
1187 PROCEDURE getProfListFromProfileDrivers(
1188 p_pmt_method_cd IN IBY_DOCS_PAYABLE_ALL.payment_method_code%TYPE,
1189 p_org_id IN IBY_DOCS_PAYABLE_ALL.org_id%TYPE,
1190 p_org_type IN IBY_DOCS_PAYABLE_ALL.org_type%TYPE,
1191 p_pmt_currency IN IBY_DOCS_PAYABLE_ALL.payment_currency_code%TYPE,
1192 p_int_bank_acct_id IN IBY_DOCS_PAYABLE_ALL.internal_bank_account_id%TYPE,
1193 x_profilesTab IN OUT NOCOPY pmtProfTabType
1194 )
1195 IS
1196 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
1197 '.getProfListFromProfileDrivers';
1198
1199 /*
1200 * We need to select payment profiles that are applicable to
1201 * given (payment method, org, currency, int bank account).
1202 *
1203 *
1204 * | Profiles |
1205 * | applicable to |
1206 * | given pmt | Profiles applicable to
1207 * | method | given payment currency
1208 * | | /
1209 * | | | /
1210 * | V | L
1211 * | |
1212 * |----------------|--------------------------
1213 * |/ \| Profiles
1214 * | | applicable to
1215 * | Intersection | <-- given
1216 * | | org id and org type
1217 * |\ /|
1218 * |----------------|--------------------------
1219 * | |
1220 * | | .__
1221 * | | |\
1222 * | | \
1223 * | |
1224 * | | Profiles applicable to
1225 * | | given internal bank
1226 * | | account
1227 * | |
1228 * | |
1229 *
1230 * We need the intersection of (profiles applicable to
1231 * a given payment method) and (profiles applicable to
1232 * a given org) and (profiles applicable to given payment
1233 * currency) and (profiles applicable to given internal
1234 * bank account) as shown in the graphic.
1235 *
1236 * Therefore, we need to join with the IBY_APPLICABLE_PMT_PROFS
1237 * four times - once to get the profiles for the method, once to get
1238 * the profiles for the org, and once to get the profiles for the
1239 * currency etc. If we are able to get a non-null intersect for these
1240 * five queries, it means that there is a profile that matches the
1241 * (org, method, currency, bank acct) combination.
1242 *
1243 * If the 'applicable_value_to' is set to NULL, it means that the
1244 * profile is applicable to 'all orgs' | 'all methods' |
1245 * 'all formats' etc., depending upon the applicable_type_code.
1246 * Therefore, we need to factor this condition in the join.
1247 */
1248
1249 /*
1250 * Redundant joins of the form where a=b=c=a execute faster than
1251 * where a=b=c joins. That's why this select has a redundant join.
1252 */
1253 CURSOR c_profiles(
1254 p_pmt_method_cd IN IBY_DOCS_PAYABLE_ALL.
1255 payment_method_code%TYPE,
1256 p_org_id IN IBY_DOCS_PAYABLE_ALL.org_id%TYPE,
1257 p_org_type IN IBY_DOCS_PAYABLE_ALL.org_type%TYPE,
1258 p_pmt_currency IN IBY_DOCS_PAYABLE_ALL.
1259 payment_currency_code%TYPE,
1260 p_int_bank_acct_id IN IBY_DOCS_PAYABLE_ALL.
1261 internal_bank_account_id%TYPE
1262 )
1263 IS
1264 SELECT
1265 acct_prof.payment_profile_id
1266 FROM
1267 IBY_APPLICABLE_PMT_PROFS app1,
1268 IBY_APPLICABLE_PMT_PROFS app2,
1269 IBY_APPLICABLE_PMT_PROFS app3,
1270 IBY_APPLICABLE_PMT_PROFS app4,
1271 IBY_SYS_PMT_PROFILES_B sys_prof,
1272 IBY_ACCT_PMT_PROFILES_B acct_prof
1273 WHERE
1274 /*
1275 * org id is a special case; since each org id has meaning
1276 * only within an org type, we need to always use
1277 * (org id, org type) as a combination.
1278 *
1279 * This means that both org id and org type need to be
1280 * stored in the same row. In order to simplify matters,
1281 * the org type field will be stored in the 'applicable
1282 * value from' column wherever org id is stored.
1283 *
1284 * If org id is null, do not check org type - special case.
1285 */
1286 (app1.applicable_type_code=APL_TYPE_ORG AND
1287 (app1.applicable_value_to IS NULL) OR
1288 (app1.applicable_value_to=TO_CHAR(p_org_id) AND
1289 app1.applicable_value_from=p_org_type))
1290 AND (app2.applicable_type_code=APL_TYPE_PMT_METHOD AND
1291 (app2.applicable_value_to=p_pmt_method_cd OR
1292 app2.applicable_value_to IS NULL))
1293 AND (app3.applicable_type_code=APL_TYPE_PMT_CURRENCY AND
1294 (app3.applicable_value_to=p_pmt_currency OR
1295 app3.applicable_value_to IS NULL))
1296 AND (app4.applicable_type_code=APL_TYPE_INT_BANK_ACCT AND
1297 (app4.applicable_value_to=TO_CHAR(p_int_bank_acct_id) OR
1298 app4.applicable_value_to IS NULL))
1299 AND
1300 app1.system_profile_code=app2.system_profile_code
1301 AND
1302 app2.system_profile_code=app3.system_profile_code
1303 AND
1304 app3.system_profile_code=app4.system_profile_code
1305 AND
1306 app4.system_profile_code=app1.system_profile_code
1307 AND
1308 app1.system_profile_code=sys_prof.system_profile_code
1309 AND
1310 sys_prof.system_profile_code=acct_prof.system_profile_code(+)
1311 /*
1312 * Fix for bug 5929889:
1313 *
1314 * Filter profiles by inactive date so that we do not
1315 * pick up end-dated profiles.
1316 */
1317 AND NVL(sys_prof.inactive_date, SYSDATE + 1) > SYSDATE
1318 ;
1319
1320 BEGIN
1321
1322 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1323 print_debuginfo(l_module_name, 'ENTER');
1324
1325 print_debuginfo(l_module_name, 'Checking for profiles '
1326 || 'applicable for given org id '
1327 || p_org_id
1328 || ' and org type '
1329 || p_org_type
1330 || ' and payment method '
1331 || p_pmt_method_cd
1332 || ' and payment currency '
1333 || p_pmt_currency
1334 || ' and internal bank account '
1335 || p_int_bank_acct_id
1336 || ' combination ...'
1337 );
1338
1339 print_debuginfo(l_module_name, 'ENTER');
1340
1341 END IF;
1342 OPEN c_profiles(p_pmt_method_cd,
1343 p_org_id,
1344 p_org_type,
1345 p_pmt_currency,
1346 p_int_bank_acct_id
1347 );
1348 FETCH c_profiles BULK COLLECT INTO x_profilesTab;
1349 CLOSE c_profiles;
1350
1351 IF (x_profilesTab.COUNT = 0) THEN
1352
1353 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1354 print_debuginfo(l_module_name, 'No applicable payment profiles '
1355 || 'were found.');
1356
1357 END IF;
1358 ELSE
1359
1360 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1361 print_debuginfo(l_module_name, 'Count of applicable payment profiles: '
1362 || x_profilesTab.COUNT);
1363
1364 END IF;
1365 END IF;
1366
1367 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1368 print_debuginfo(l_module_name, 'EXIT');
1369
1370 END IF;
1371 EXCEPTION
1372 WHEN OTHERS THEN
1373 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1374 print_debuginfo(l_module_name, 'Non-Fatal: Exception when '
1375 || 'attempting to get payment profile for given (payment '
1376 || 'method, org, currency, int bank acct) '
1377 || 'combination.'
1378 );
1379 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
1380 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
1381
1382 print_debuginfo(l_module_name, 'EXIT');
1383
1384 END IF;
1385 END getProfListFromProfileDrivers;
1386
1387 /*--------------------------------------------------------------------
1388 | NAME:
1389 | getProfileMap
1390 |
1391 | PURPOSE:
1392 |
1393 | PARAMETERS:
1394 | IN
1395 |
1396 |
1397 | OUT
1398 |
1399 |
1400 | RETURNS:
1401 |
1402 | NOTES:
1403 |
1404 *---------------------------------------------------------------------*/
1405 PROCEDURE getProfileMap(
1406 x_profileMap IN OUT NOCOPY profileIdToCodeMapTabType
1407 )
1408 IS
1409 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.getProfileMap';
1410
1411 /*
1412 * Cursor to pick up mapping between a profile id and a
1413 * profile code.
1414 */
1415 CURSOR c_profile_map
1416 IS
1417 SELECT
1418 payment_profile_id,
1419 system_profile_code
1420 FROM
1421 IBY_PAYMENT_PROFILES;
1422
1423 BEGIN
1424
1425 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1426 print_debuginfo(l_module_name, 'ENTER');
1427
1428 END IF;
1429 /*
1430 * Pick up profile mapping.
1431 */
1432 OPEN c_profile_map;
1433 FETCH c_profile_map BULK COLLECT INTO x_profileMap;
1434 CLOSE c_profile_map;
1435
1436
1437 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1438 print_debuginfo(l_module_name, 'Profile map contains '
1439 || x_profileMap.COUNT
1440 || ' records.'
1441 );
1442
1443 print_debuginfo(l_module_name, 'EXIT');
1444
1445 END IF;
1446 END getProfileMap;
1447
1448
1449 /*--------------------------------------------------------------------
1450 | NAME:
1451 | getProfileCodeFromId
1452 |
1453 | PURPOSE:
1454 |
1455 | PARAMETERS:
1456 | IN
1457 |
1458 |
1459 | OUT
1460 |
1461 |
1462 | RETURNS:
1463 |
1464 | NOTES:
1465 |
1466 *---------------------------------------------------------------------*/
1467 FUNCTION getProfileCodeFromId(
1468 p_profile_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_profile_id%TYPE,
1469 p_profileMap IN profileIdToCodeMapTabType
1470 ) RETURN VARCHAR2
1471 IS
1472 l_profile_code IBY_PAYMENT_PROFILES.system_profile_code%TYPE;
1473 l_profil_id_index NUMBER(20);
1474
1475 BEGIN
1476
1477
1478 /*
1479 * Adding Caching Logic to avoid database hits for the same input
1480 * combination.
1481 *
1482 */
1483 l_profil_id_index := p_profile_id;
1484
1485 IF (l_profile_cod_tbl.EXISTS(l_profil_id_index)) THEN
1486 RETURN l_profile_cod_tbl(l_profil_id_index);
1487 END IF;
1488
1489
1490 IF (p_profileMap.COUNT = 0) THEN
1491 l_profile_code := NULL;
1492 RETURN l_profile_code;
1493 END IF;
1494
1495 l_profile_code := NULL;
1496 FOR i IN p_profileMap.FIRST .. p_profileMap.LAST LOOP
1497 IF (p_profileMap(i).pmt_profile_id = p_profile_id) THEN
1498 l_profile_code := p_profileMap(i).pmt_profile_cd;
1499
1500 /* Caching before exiting */
1501 l_profile_cod_tbl(l_profil_id_index) := l_profile_code;
1502
1503 EXIT;
1504 END IF;
1505 END LOOP;
1506
1507 RETURN l_profile_code;
1508
1509 END getProfileCodeFromId;
1510
1511 /*--------------------------------------------------------------------
1512 | NAME:
1513 | printWrappedString
1514 |
1515 | PURPOSE:
1516 |
1517 | PARAMETERS:
1518 | IN
1519 |
1520 |
1521 | OUT
1522 |
1523 |
1524 | RETURNS:
1525 |
1526 | NOTES:
1527 |
1528 *---------------------------------------------------------------------*/
1529 PROCEDURE printWrappedString(
1530 p_string IN VARCHAR2
1531 )
1532 IS
1533
1534 l_chunk VARCHAR2(80);
1535 l_length NUMBER := 1;
1536
1537 BEGIN
1538
1539 WHILE(length(p_string) >= l_length) LOOP
1540
1541 l_chunk := substrb(p_string, l_length, 75);
1542
1543 l_chunk := ltrim(l_chunk);
1544 l_chunk := rtrim(l_chunk);
1545
1546 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1547 print_debuginfo(' ', l_chunk);
1548
1549 END IF;
1550 l_length := (l_length + 75);
1551
1552 END LOOP;
1553
1554 END printWrappedString;
1555
1556 /*--------------------------------------------------------------------
1557 | NAME:
1558 | inactivateOldErrors
1559 |
1560 | PURPOSE:
1561 |
1562 | PARAMETERS:
1563 | IN
1564 |
1565 |
1566 | OUT
1567 |
1568 |
1569 | RETURNS:
1570 |
1571 | NOTES:
1572 |
1573 *---------------------------------------------------------------------*/
1574 PROCEDURE inactivateOldErrors(
1575 p_trxn_id IN IBY_TRANSACTION_ERRORS.transaction_id%TYPE,
1576 p_trxn_type IN IBY_TRANSACTION_ERRORS.transaction_type%TYPE
1577 )
1578 IS
1579 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.inactivateOldErrors';
1580
1581 BEGIN
1582
1583 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1584 print_debuginfo(l_module_name, 'ENTER');
1585
1586 print_debuginfo(l_module_name, 'Inactivating old errors for '
1587 || p_trxn_type
1588 || ' id: '
1589 || p_trxn_id
1590 );
1591
1592 END IF;
1593 /*
1594 * Set the error status to inactive in the IBY_TRANSACTION_ERRORS
1595 * table for all rows that contain errors against this
1596 * transaction.
1597 */
1598 UPDATE
1599 IBY_TRANSACTION_ERRORS err
1600 SET
1601 err.error_status = 'INACTIVE',
1602 err.last_update_date = sysdate,
1603 err.last_updated_by = fnd_global.user_id
1604 WHERE
1605 err.transaction_id = p_trxn_id AND
1606 err.transaction_type = p_trxn_type
1607 ;
1608
1609 /*
1610 * Fix for bug 5742548:
1611 *
1612 * When inactivating payment errors, remember to
1613 * inactivate errors on underlying documents payable
1614 * as well.
1615 */
1616 IF (p_trxn_type = 'PAYMENT') THEN
1617
1618 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1619 print_debuginfo(l_module_name, 'Inactivating old errors for '
1620 || 'child documents of payment id: '
1621 || p_trxn_id
1622 );
1623
1624 END IF;
1625 UPDATE
1626 IBY_TRANSACTION_ERRORS err
1627 SET
1628 err.error_status = 'INACTIVE',
1629 err.last_update_date = sysdate,
1630 err.last_updated_by = fnd_global.user_id
1631 WHERE
1632 err.transaction_id IN
1633 (
1634 SELECT
1635 document_payable_id
1636 FROM
1637 IBY_DOCS_PAYABLE_ALL
1638 WHERE
1639 payment_id = p_trxn_id
1640 )
1641 AND
1642 err.transaction_type = 'DOCUMENT_PAYABLE'
1643 ;
1644
1645 END IF;
1646
1647 /*
1648 * Reset the transaction status to a success / neutral
1649 * status. The transaction status will get updated to a
1650 * failure status in case the transaction fails validation
1651 * later.
1652 */
1653 IF (p_trxn_type = 'DOCUMENT_PAYABLE') THEN
1654
1655 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1656 print_debuginfo(l_module_name, 'Resetting status of doc'
1657 || ' id: '
1658 || p_trxn_id
1659 || ' to '
1660 || DOC_STATUS_VALIDATED
1661 );
1662
1663 END IF;
1664 UPDATE
1665 IBY_DOCS_PAYABLE_ALL doc
1666 SET
1667 doc.document_status = DOC_STATUS_VALIDATED
1668 WHERE
1669 doc.document_payable_id = p_trxn_id
1670 ;
1671
1672 ELSIF (p_trxn_type = 'PAYMENT') THEN
1673
1674 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1675 print_debuginfo(l_module_name, 'Resetting status of pmt'
1676 || ' id: '
1677 || p_trxn_id
1678 || ' to '
1679 || PAY_STATUS_CREATED
1680 );
1681
1682 END IF;
1683 UPDATE
1684 IBY_PAYMENTS_ALL pmt
1685 SET
1686 pmt.payment_status = PAY_STATUS_CREATED
1687 WHERE
1688 pmt.payment_id = p_trxn_id
1689 ;
1690
1691 END IF;
1692
1693 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1694 print_debuginfo(l_module_name, 'EXIT');
1695
1696 END IF;
1697 /*
1698 * Any exceptions that occur in this method are non-fatal.
1699 */
1700 EXCEPTION
1701 WHEN OTHERS THEN
1702
1703 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1704 print_debuginfo(l_module_name, 'Non Fatal: Exception occured '
1705 || 'when attempting to update error status to inactive '
1706 || 'for transaction.'
1707 );
1708
1709 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
1710 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
1711
1712 print_debuginfo(l_module_name, 'EXIT');
1713
1714 END IF;
1715 END inactivateOldErrors;
1716
1717 /*--------------------------------------------------------------------
1718 | NAME:
1719 | resetPaymentErrors
1720 |
1721 | PURPOSE:
1722 |
1723 | PARAMETERS:
1724 | IN
1725 |
1726 |
1727 | OUT
1728 |
1729 |
1730 | RETURNS:
1731 |
1732 | NOTES:
1733 |
1734 *---------------------------------------------------------------------*/
1735 PROCEDURE resetPaymentErrors(
1736 p_payment_request_id IN IBY_PAY_SERVICE_REQUESTS.
1737 payment_service_request_id%TYPE
1738 )
1739 IS
1740 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.resetPaymentErrors';
1741
1742 BEGIN
1743
1744 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1745 print_debuginfo(l_module_name, 'ENTER');
1746
1747 END IF;
1748 /*
1749 * STEP 1:
1750 *
1751 * Delete the existing errors stored against the payments
1752 * of the PPR.
1753 *
1754 * We are going to revalidate these payments, so old errors
1755 * need not be persisted at this point.
1756 */
1757 DELETE
1758 IBY_TRANSACTION_ERRORS err
1759 WHERE
1760 err.transaction_type = 'PAYMENT' AND
1761 err.transaction_id IN
1762 (
1763 SELECT
1764 payment_id
1765 FROM
1766 IBY_PAYMENTS_ALL
1767 WHERE
1768 payment_service_request_id = p_payment_request_id AND
1769 payment_status IN
1770 (
1771 PAY_STATUS_CREATED,
1772 PAY_STATUS_MODIFIED,
1773 PAY_STATUS_FAIL_VALID
1774 )
1775 )
1776 ;
1777
1778 /*
1779 * STEP 2:
1780 *
1781 * Delete existing errors against documents that
1782 * were failed because their parent payment
1783 * failed validation.
1784 *
1785 * Fix for bug 5742548:
1786 *
1787 * When deleting payment errors, remember to
1788 * delete errors on underlying documents payable
1789 * as well.
1790 */
1791 DELETE
1792 IBY_TRANSACTION_ERRORS err
1793 WHERE
1794 err.transaction_type = 'DOCUMENT_PAYABLE' AND
1795 err.transaction_id IN
1796 (
1797 SELECT
1798 docs.document_payable_id
1799 FROM
1800 IBY_DOCS_PAYABLE_ALL docs
1801 WHERE
1802 docs.document_status = DOC_STATUS_PAY_VAL_FAIL AND
1803 docs.payment_service_request_id = p_payment_request_id
1804 )
1805 ;
1806
1807
1808 /*
1809 * STEP 3:
1810 *
1811 * Reset the status of payments that are to be re-validated to
1812 * 'CREATED'.
1813 */
1814 UPDATE
1815 IBY_PAYMENTS_ALL
1816 SET
1817 payment_status = PAY_STATUS_CREATED
1818 WHERE
1819 payment_service_request_id = p_payment_request_id AND
1820 payment_status IN
1821 (
1822 PAY_STATUS_MODIFIED,
1823 PAY_STATUS_FAIL_VALID
1824 )
1825 ;
1826
1827 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1828 print_debuginfo(l_module_name, 'EXIT');
1829
1830 END IF;
1831 END resetPaymentErrors;
1832
1833
1834 /*--------------------------------------------------------------------
1835 | NAME:
1836 | resetDocumentErrors
1837 |
1838 | PURPOSE:
1839 |
1840 | PARAMETERS:
1841 | IN
1842 |
1843 |
1844 | OUT
1845 |
1846 |
1847 | RETURNS:
1848 |
1849 | NOTES:
1850 |
1851 *---------------------------------------------------------------------*/
1852 PROCEDURE resetDocumentErrors(
1853 p_payment_request_id IN IBY_PAY_SERVICE_REQUESTS.
1854 payment_service_request_id%TYPE
1855 )
1856 IS
1857 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.resetDocumentErrors';
1858
1859 BEGIN
1860
1861 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1862 print_debuginfo(l_module_name, 'ENTER');
1863
1864 END IF;
1865 /*
1866 * STEP 1:
1867 *
1868 * Delete existing errors against documents payable that
1869 * are to be re-validated
1870 */
1871 DELETE
1872 IBY_TRANSACTION_ERRORS err
1873 WHERE
1874 err.transaction_type = 'DOCUMENT_PAYABLE' AND
1875 err.transaction_id IN
1876 (
1877 SELECT
1878 document_payable_id
1879 FROM
1880 IBY_DOCS_PAYABLE_ALL
1881 WHERE
1882 payment_service_request_id = p_payment_request_id AND
1883 document_status = DOC_STATUS_FAIL_VALID
1884 )
1885 ;
1886
1887 /*
1888 * Reset the status of documents that are to be re-validated to
1889 * 'VALIDATED'.
1890 */
1891 UPDATE
1892 IBY_DOCS_PAYABLE_ALL
1893 SET
1894 document_status = DOC_STATUS_VALIDATED
1895 WHERE
1896 payment_service_request_id = p_payment_request_id AND
1897 document_status = DOC_STATUS_FAIL_VALID
1898 ;
1899
1900 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1901 print_debuginfo(l_module_name, 'EXIT');
1902
1903 END IF;
1904 END resetDocumentErrors;
1905
1906 /*--------------------------------------------------------------------
1907 | NAME:
1908 | resetPaymentInstructionErrors
1909 |
1910 | PURPOSE:
1911 |
1912 | PARAMETERS:
1913 | IN
1914 |
1915 |
1916 | OUT
1917 |
1918 |
1919 | RETURNS:
1920 |
1921 | NOTES:
1922 |
1923 *---------------------------------------------------------------------*/
1924 PROCEDURE resetPaymentInstructionErrors(
1925 p_instr_id IN IBY_PAY_INSTRUCTIONS_ALL.
1926 payment_instruction_id%TYPE
1927 )
1928 IS
1929 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.resetPaymentInstructionErrors';
1930
1931 BEGIN
1932
1933 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1934 print_debuginfo(l_module_name, 'ENTER');
1935
1936 END IF;
1937 /*
1938 * Delete existing errors against the payment instruction
1939 * that is to be re-validated
1940 *
1941 * There is no need to delete the errors against any
1942 * child elements (like payments) etc. because these
1943 * are untouched by the PICP.
1944 */
1945 DELETE
1946 IBY_TRANSACTION_ERRORS err
1947 WHERE
1948 err.transaction_type = 'PAYMENT_INSTRUCTION' AND
1949 err.transaction_id = p_instr_id
1950 ;
1951
1952 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1953 print_debuginfo(l_module_name, 'EXIT');
1954
1955 END IF;
1956 END resetPaymentInstructionErrors;
1957
1958 END IBY_BUILD_UTILS_PKG;
[Home] [Help]