1 PACKAGE BODY IBY_BUILD_UTILS_PKG AS
2 /*$Header: ibyblutb.pls 120.25.12010000.1 2008/07/28 05:39:39 appldev ship $*/
3
4 /*
5 * Declare global variables
6 */
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_BUILD_UTILS_PKG';
8
9 --
10 -- List of document statuses that are used / set in this
11 -- module.
12 --
13 DOC_STATUS_VALIDATED CONSTANT VARCHAR2(100) := 'VALIDATED';
14 DOC_STATUS_PAY_CREATED CONSTANT VARCHAR2(100) := 'PAYMENT_CREATED';
15 DOC_STATUS_CA_FAILED CONSTANT VARCHAR2(100) := 'FAILED_BY_CALLING_APP';
16 DOC_STATUS_RELN_FAIL CONSTANT VARCHAR2(100) := 'FAILED_BY_RELATED_DOCUMENT';
17 DOC_STATUS_PAY_VAL_FAIL CONSTANT VARCHAR2(100) := 'PAYMENT_FAILED_VALIDATION';
18 DOC_STATUS_FAIL_BY_REJLVL CONSTANT VARCHAR2(100)
19 := 'FAILED_BY_REJECTION_LEVEL';
20
21 --
22 -- List of payment statuses that are used / set in this
23 -- module.
24 --
25 PAY_STATUS_FAIL_VALID CONSTANT VARCHAR2(100) := 'FAILED_VALIDATION';
26 PAY_STATUS_CREATED CONSTANT VARCHAR2(100) := 'CREATED';
27 PAY_STATUS_CA_FAILED CONSTANT VARCHAR2(100) := 'FAILED_BY_CALLING_APP';
28 PAY_STATUS_FAIL_BY_REJLVL CONSTANT VARCHAR2(100)
29 := 'FAILED_BY_REJECTION_LEVEL';
30
31 --
32 -- Lookups for profile applicablility types (from IBY_PMT_PROF_LOV_APL_TYPES)
33 --
34 APL_TYPE_ORG CONSTANT VARCHAR2(100) := 'PAYER_ORG';
35 APL_TYPE_PMT_METHOD CONSTANT VARCHAR2(100) := 'PAYMENT_METHOD';
36 APL_TYPE_PMT_CURRENCY CONSTANT VARCHAR2(100) := 'CURRENCY_CODE';
37 APL_TYPE_INT_BANK_ACCT CONSTANT VARCHAR2(100) := 'INTERNAL_BANK_ACCOUNT';
38
39 --
40 -- List of transaction error statuses that are used / set in this
41 -- module.
42 --
43 TRXN_ERROR_ACTIVE CONSTANT VARCHAR2(100) := 'ACTIVE';
44
45 /*--------------------------------------------------------------------
46 | NAME:
47 | print_debuginfo
48 |
49 | PURPOSE:
50 |
51 |
52 | PARAMETERS:
53 | IN
54 |
55 |
56 | OUT
57 |
58 |
59 | RETURNS:
60 |
61 | NOTES:
62 |
63 *---------------------------------------------------------------------*/
64 PROCEDURE print_debuginfo(p_module IN VARCHAR2,
65 p_debug_text IN VARCHAR2,
66 p_debug_level IN VARCHAR2 DEFAULT FND_LOG.LEVEL_STATEMENT
67 )
68 IS
69 l_default_debug_level VARCHAR2(200) := FND_LOG.LEVEL_STATEMENT;
70 PRAGMA AUTONOMOUS_TRANSACTION;
71
72 BEGIN
73
74 /*
75 * Set the debug level to the value passed in
76 * (provided this value is not null).
77 */
78 IF (p_debug_level IS NOT NULL) THEN
79 l_default_debug_level := p_debug_level;
80 END IF;
81
82 /*
83 * Write immediate validation messages to the common
84 * application logs. Write deferred validation messages
85 * to the concurrent manager log file.
86 *
87 * If FND_GLOBAL.conc_request_id is -1, it implies that
88 * this method has not been invoked via the concurrent
89 * manager (online validation case; write to apps log).
90 */
91 IF (l_default_debug_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
92
93 IF (FND_GLOBAL.conc_request_id = -1) THEN
94
95 /*
96 * OPTION I:
97 * Write debug text to the common application log file.
98 */
99 --IBY_DEBUG_PUB.add(
100 -- substr(RPAD(p_module,55) || ' : ' || p_debug_text, 0, 150),
101 -- FND_LOG.G_CURRENT_RUNTIME_LEVEL,
102 -- 'iby.plsql.IBY_VALIDATIONSETS_PUB'
103 -- );
104
105 FND_LOG.STRING(
106 l_default_debug_level,
107 'iby.plsql.IBY_VALIDATIONSETS_PUB',
108 substr(RPAD(p_module, 55) || ' : ' || p_debug_text, 0, 150)
109 );
110
111 /*
112 * OPTION II:
113 * Write debug text to DBMS output file.
114 */
115 --DBMS_OUTPUT.PUT_LINE(substr(RPAD(p_module,40)||' : '||
116 -- p_debug_text, 0, 150));
117
118 /*
119 * OPTION III:
120 * Write debug text to temporary table.
121 */
122
123 /*
124 * Use this script to create a debug table.
125 *
126 * CREATE TABLE TEMP_IBY_LOGS(TEXT VARCHAR2(4000), TIME DATE);
127 */
128
129 /* uncomment these two lines for debugging */
130 --INSERT INTO TEMP_IBY_LOGS VALUES (p_module || ': '
131 -- || p_debug_text, sysdate);
132 --COMMIT;
133
134 ELSE
135
136 /*
137 * OPTION I:
138 * Write debug text to the concurrent manager log file.
139 */
140 FND_FILE.PUT_LINE(FND_FILE.LOG, p_module || ': ' || p_debug_text);
141
142 /*
143 * OPTION II:
144 * Write debug text to DBMS output file.
145 */
146 --DBMS_OUTPUT.PUT_LINE(substr(RPAD(p_module,40)||' : '||
147 -- p_debug_text, 0, 150));
148
149 /*
150 * OPTION III:
151 * Write debug text to temporary table.
152 */
153
154 /*
155 * Use this script to create a debug table.
156 *
157 * CREATE TABLE TEMP_IBY_LOGS(TEXT VARCHAR2(4000), TIME DATE);
158 */
159
160 /* uncomment these two lines for debugging */
161 --INSERT INTO TEMP_IBY_LOGS VALUES (p_module || ': '
162 -- || p_debug_text, sysdate);
163 --COMMIT;
164
165 END IF; -- if not a conc request
166
167 END IF; -- if current debug level greater than runtime debug level
168
169 EXCEPTION
170 WHEN OTHERS THEN
171
172 ROLLBACK;
173
174 END print_debuginfo;
175
176 /*--------------------------------------------------------------------
177 | NAME:
178 | printXMLClob
179 |
180 | PURPOSE:
181 | Prints out the XML data contained within the given clob; to be
182 | used for testing purposes.
183 |
184 |
185 | PARAMETERS:
186 | IN
187 |
188 | OUT
189 |
190 |
191 | RETURNS:
192 |
193 | NOTES:
194 |
195 *---------------------------------------------------------------------*/
196 PROCEDURE printXMLClob(
197 p_xml_clob IN CLOB)
198 IS
199
200 l_xmlstr VARCHAR2(32767);
201 l_line VARCHAR2(2000);
202 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.printXMLClob';
203
204 BEGIN
205
206 print_debuginfo(l_module_name, 'ENTER');
207
208 /*
209 * Do not attempt to print XML clob unless it contains
210 * CR-LFs.
211 */
212 IF (DBMS_LOB.INSTR(p_xml_clob, fnd_global.local_chr(10)) = 0) THEN
213
214 print_debuginfo(l_module_name, 'Clob does not contain '
215 || 'CR-LFs; cannot print entire XML Clob, '
216 || 'printing a snippet ..'
217 );
218
219 print_debuginfo(l_module_name, TO_CHAR(p_xml_clob));
220 print_debuginfo(l_module_name, 'EXIT');
221
222 RETURN;
223
224 END IF;
225
226
227 /*
228 * Print the clob as an XML fragment.
229 */
230 l_xmlstr := dbms_lob.substr(p_xml_clob, 32767);
231 LOOP
232
233 EXIT WHEN l_xmlstr IS NULL;
234
235 l_line := SUBSTR(l_xmlstr,1,INSTR(l_xmlstr,
236 fnd_global.local_chr(10))-1);
237
238 IF (l_line IS NOT NULL) THEN
239
240 print_debuginfo('', l_line);
241
242 l_xmlstr := SUBSTR(l_xmlstr,INSTR(l_xmlstr,
243 fnd_global.local_chr(10))+1);
244
245 ELSE
246
247 /*
248 * Fix for bug 5550778:
249 *
250 * Print the last chunk and set the
251 * xml string to null so that we do
252 * not go into an infinite loop
253 * in case we cannot find a '\n'
254 * in the XML clob.
255 */
256 print_debuginfo('', l_xmlstr);
257
258 l_xmlstr := NULL;
259
260 END IF;
261
262 END LOOP;
263
264 print_debuginfo(l_module_name, 'EXIT');
265
266 END printXMLClob;
267
268 /*--------------------------------------------------------------------
269 | NAME:
270 | checkForInvalidChars
271 |
272 | PURPOSE:
273 | Given an input string and a string of invalid characters, this
274 | procedure checks if any of the invalid characters are present
275 | in the given input string.
276 |
277 | If any of the invalid characters are found, an error message is
278 | inserted into the error table identifying the invalid character.
279 |
280 | If no invalid characters are found in the input string, this
281 | procedure simply returns.
282 |
283 | PARAMETERS:
284 | IN
285 | p_fieldName - The name of the input field (used for logging)
286 | p_fieldValue - The value of the input field
287 | p_isComposite - Indidicates that the field is a composite
288 | field created by appending multiple individual
289 | fields. For composite fields, the field value
290 | will not be printed in the error message.
291 | p_invalid_chars_list - List of invalid characters that
292 | should not be present in the field value
293 | OUT
294 | x_docErrorRec - Error message record. This should contain the
295 | document id when coming into this method.
296 | x_docErrorTab - Error messages table. An error message will be
297 | added to this table if the input field value
298 | contains an invalid char.
299 |
300 | RETURNS:
301 |
302 | NOTES:
303 |
304 *---------------------------------------------------------------------*/
305 PROCEDURE checkForInvalidChars(
306 p_fieldName IN VARCHAR2,
307 p_fieldValue IN VARCHAR2,
308 p_isComposite IN BOOLEAN,
309 p_validCharsList IN VARCHAR2,
310 p_invalidCharsList IN VARCHAR2,
311 x_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
312 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
313 docErrorTabType
314 )
315 IS
316 l_error_code VARCHAR2(100);
317 l_error_msg VARCHAR2(500);
318
319 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.checkForInvalidChars';
320
321 /*
322 * This dummy string is used as the third argument to the TRANSLATE()
323 * function. Only used if invalid chars list is provided.
324 *
325 * The value of the string does not matter. It is used as a mask;
326 * translate() will substitute any invalid chars in the field value
327 * with a dummy value from this string. We will diff these two strings
328 * to find the invalid characters.
329 */
330 l_inv_mask VARCHAR2(200) :=
331 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
332
333 /*
334 * Dummy string used as mask for checking valid characters. Only used
335 * if valid chars list is provided.
336 */
337 l_val_mask VARCHAR2(20) := ' ';
338
339 l_translated_value VARCHAR2(200);
340 l_invalid_chars_found VARCHAR2(200);
341 l_error_string VARCHAR2(1000);
342
343 BEGIN
344
345 print_debuginfo(l_module_name, 'ENTER');
346
347 /*
348 * Don't do anything if the given value is null.
349 */
350 IF (p_fieldValue IS NULL) THEN
351 print_debuginfo(l_module_name, 'Value provided for field '''
352 || p_fieldName
353 || ''' is null. Skipping character validation'
354 );
355 print_debuginfo(l_module_name, 'EXIT');
356 RETURN;
357 ELSE
358 print_debuginfo(l_module_name, 'Value provided for field '''
359 || p_fieldName || ''' is '
360 || p_fieldValue
361 );
362 END IF;
363
364 /*
365 * MAIN CHARACTER VALIDATION LOGIC
366 */
367 /* character validation using valid chars list */
368 IF (p_validCharsList IS NOT NULL) THEN
369
370 print_debuginfo(l_module_name, 'Checking using valid chars list..');
371
372 /*
373 * HOW DOES IT WORK?
374 *
375 * Given a list of valid characters, we call translate like this:
376 *
377 * translate(
378 * -- given string
379 * 'Oracle 9i',
380 * -- valid chars list
381 * 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
382 * -- mask
383 * ' '
384 * );
385 *
386 * This returns '9'. And so we caught the invalid character.
387 */
388
389 /*
390 * Call translate() to filter out the invalid chars.
391 */
392 SELECT translate(p_fieldValue, p_validCharsList, l_val_mask)
393 INTO l_translated_value
394 FROM DUAL;
395
396 /* strip spaces */
397 l_translated_value := LTRIM(l_translated_value);
398 l_translated_value := RTRIM(l_translated_value);
399
400 /*
401 * This means that all characters in the field value were
402 * found in the valid char list. So the value contains
403 * only valid characters. No invalid chars; Exit at this point.
404 */
405 IF (l_translated_value IS NULL) THEN
406 print_debuginfo(l_module_name, 'EXIT');
407 RETURN;
408 ELSE
409 /*
410 * This means that some characters were present in the
411 * field value that could not be found in the valid char
412 * list. Therefore, invalid characters are present in this
413 * field.
414 */
415 l_invalid_chars_found := l_translated_value;
416 END IF;
417
418 ELSE
419 /* character validation using invalid chars list */
420 print_debuginfo(l_module_name, 'Checking using invalid chars list..');
421
422 /*
423 * HOW DOES IT WORK?
424 *
425 * Given a list of invalid characters, we call translate like this:
426 *
427 * translate(
428 * -- given string
429 * '[email protected]',
430 * -- invalid chars list
431 * '@#$'
432 * -- mask
433 * 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
434 * );
435 *
436 * This returns 'rameshaoracle.com'.
437 *
438 * We diff the input string and the translated string to get '@'
439 * as the invalid character.
440 */
441
442 /*
443 * Call translate to substitute any invalid chars in
444 * the given string with dummy ones.
445 */
446 SELECT translate(p_fieldValue, p_invalidCharsList, l_inv_mask)
447 INTO l_translated_value
448 FROM DUAL;
449
450 /*
451 * If the translated value is not equal to the original
452 * field value, it means that there were some invalid
453 * characters in the field value.
454 *
455 * Diff the original and translated values to get the
456 * invalid characters.
457 */
458 IF (p_fieldValue <> l_translated_value) THEN
459 l_invalid_chars_found := diffStrings(p_fieldValue,
460 l_translated_value);
461 END IF;
462
463 END IF; -- valid chars list is not null
464
465 IF (l_invalid_chars_found IS NOT NULL) THEN
466
467 /*
468 * Do not show the field value for composite fields.
469 */
470 IF (p_isComposite = true) THEN
471
472 l_error_string := 'Found invalid char(s) '
473 || '''' || l_invalid_chars_found || ''''
474 || ' in field '
475 || '''' || p_fieldName || ''''
476 ;
477
478 l_error_code := 'IBY_FOUND_INVALID_CHARS';
479 FND_MESSAGE.set_name('IBY', l_error_code);
480
481 FND_MESSAGE.SET_TOKEN('INV_CHARS',
482 l_invalid_chars_found,
483 FALSE);
484
485 FND_MESSAGE.SET_TOKEN('FIELD_NAME',
486 p_fieldName,
487 FALSE);
488
489 ELSE
490
491 l_error_string := 'Found invalid char(s) '
492 || '''' || l_invalid_chars_found || ''''
493 || ' in field '
494 || '''' || p_fieldName || ''''
495 || ' with value '
496 || '''' || p_fieldValue || ''''
497 ;
498
499 l_error_code := 'IBY_INVALID_CHARS_IN_DATA';
500 FND_MESSAGE.set_name('IBY', l_error_code);
501
502 FND_MESSAGE.SET_TOKEN('INV_CHARS',
503 l_invalid_chars_found,
504 FALSE);
505
506 FND_MESSAGE.SET_TOKEN('FIELD_NAME',
507 p_fieldName,
508 FALSE);
509
510 FND_MESSAGE.SET_TOKEN('FIELD_VALUE',
511 p_fieldValue,
512 FALSE);
513
514 END IF;
515
516 print_debuginfo(l_module_name, l_error_string);
517
518 /*
519 * Add error code and error message to the
520 * validation errors table
521 */
522 x_docErrorRec.error_code := l_error_code;
523 x_docErrorRec.error_message := FND_MESSAGE.get;
524
525 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
526 x_docErrorRec, x_docErrorTab);
527
528 END IF;
529
530 print_debuginfo(l_module_name, 'EXIT');
531
532 END checkForInvalidChars;
533
534 /*--------------------------------------------------------------------
535 | NAME:
536 | getChar
537 |
538 | PURPOSE:
539 | Returns the character at a given position of a given string.
540 | Similar to ChatAt(index) in Java.
541 |
542 | Note that in PLSQL indexes are 1 based i.e., the very first
543 | character in the string has index 1 (as opposed to 0 based
544 | indexes in C and Java).
545 |
546 | Therefore, if the given string is 'I am the walrus', then
547 | getChar(str, 10) will return 'w'.
548 |
549 | NULL will be returned if the index is out-of-bounds.
550 |
551 | PARAMETERS:
552 | IN
553 | p_string - The name of the input field (used for logging)
554 | p_index - The value of the input field
555 |
556 | OUT
557 |
558 | RETURNS:
559 |
560 | VARCHAR2(1) - the character at the given index on the given string
561 |
562 | NOTES:
563 |
564 *---------------------------------------------------------------------*/
565 FUNCTION getChar(
566 p_string IN VARCHAR2,
567 p_index IN VARCHAR2
568 )
569 RETURN VARCHAR2
570 IS
571 l_char VARCHAR2(1);
572 BEGIN
573
574 l_char := SUBSTR(p_string, p_index, 1);
575 RETURN l_char;
576
577 END getChar;
578
579 /*--------------------------------------------------------------------
580 | NAME:
581 | diffStrings
582 |
583 | PURPOSE:
584 | Given two strings, this method performs a diff on them
585 | and returns their differences.
586 |
587 | The two args are not expected to be completely arbitary;
588 | instead arg2 is expected to be similar to arg1 with some
589 | differences. For this reason, both strings are expected to
590 | be of the same length.
591 |
592 | Whenever there are differences, the value from arg1 is added
593 | to the diff list. The value from arg2 is ignored.
594 |
595 | PARAMETERS:
596 | IN
597 | p_originalString - The original string
598 | p_compareString - The changed string
599 |
600 | OUT
601 |
602 | RETURNS:
603 |
604 | VARCHAR2 - the difference between the two strings
605 | NULL will be returned if both strings are the same
606 |
607 | NOTES:
608 | Comparison between the two strings will only take place uptil the
609 | length of the original string.
610 |
611 *---------------------------------------------------------------------*/
612 FUNCTION diffStrings(
613 p_originalString IN VARCHAR2,
614 p_compareString IN VARCHAR2
615 )
616 RETURN VARCHAR2
617 IS
618 l_orgChar VARCHAR2(1);
619 l_compChar VARCHAR2(1);
620 l_diffString VARCHAR2(2000);
621 l_length NUMBER(10);
622 BEGIN
623
624 IF (p_originalString = p_compareString) THEN
625 RETURN NULL;
626 END IF;
627
628 /*
629 * Compare the strings char-by-char and store the
630 * differences
631 */
632 l_diffString := '';
633 l_length := LEAST(length(p_originalString), length(p_compareString));
634
635 FOR i in 1 .. l_length LOOP
636
637 l_orgChar := SUBSTR(p_originalString, i, 1);
638 l_compChar := SUBSTR(p_compareString, i, 1);
639
640 IF (l_orgChar <> l_compChar) THEN
641 l_diffString := l_diffString || l_orgChar;
642 END IF;
643
644 END LOOP;
645
646 /*
647 * Return NULL if we were unable to find
648 * any differences.
649 */
650 IF (l_diffString = '') THEN
651 l_diffString := NULL;
652 END IF;
653
654 RETURN l_diffString;
655
656 END diffStrings;
657
658 /*--------------------------------------------------------------------
659 | NAME:
660 | createErrorRecord
661 |
662 | PURPOSE:
663 |
664 | PARAMETERS:
665 | IN
666 |
667 | OUT
668 |
669 |
670 | RETURNS:
671 |
672 | NOTES:
673 |
674 *---------------------------------------------------------------------*/
675 PROCEDURE createErrorRecord(
676 p_trxn_type IN IBY_TRANSACTION_ERRORS.transaction_type%TYPE,
677 p_doc_id IN IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE,
678 p_doc_status IN IBY_DOCS_PAYABLE_ALL.document_status%TYPE,
679 p_ca_id IN IBY_DOCS_PAYABLE_ALL.calling_app_id%TYPE,
680 p_ca_doc_id1 IN IBY_DOCS_PAYABLE_ALL.
681 calling_app_doc_unique_ref1%TYPE,
682 p_ca_doc_id2 IN IBY_DOCS_PAYABLE_ALL.
683 calling_app_doc_unique_ref2%TYPE,
684 p_ca_doc_id3 IN IBY_DOCS_PAYABLE_ALL.
685 calling_app_doc_unique_ref3%TYPE,
686 p_ca_doc_id4 IN IBY_DOCS_PAYABLE_ALL.
687 calling_app_doc_unique_ref4%TYPE,
688 p_ca_doc_id5 IN IBY_DOCS_PAYABLE_ALL.
689 calling_app_doc_unique_ref5%TYPE,
690 p_pp_tt_cd IN IBY_DOCS_PAYABLE_ALL.pay_proc_trxn_type_code%TYPE,
691 x_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
692 x_docTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType,
693 p_rel_doc_id IN IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE
694 DEFAULT NULL,
695 p_error_code IN VARCHAR2 DEFAULT NULL
696 )
697 IS
698 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.createErrorRecord';
699 l_error_code VARCHAR2(100);
700 l_error_msg VARCHAR2(500);
701
702 l_token_rec IBY_TRXN_ERROR_TOKENS%ROWTYPE;
703
704 BEGIN
705
706 print_debuginfo(l_module_name, 'ENTER');
707 print_debuginfo(l_module_name, 'Transaction type: ' || p_trxn_type);
708 print_debuginfo(l_module_name, 'Transaction status: ' || p_doc_status);
709
710 x_docErrorRec.validation_set_code := 'CORE_PAYMENT_VALIDATION';
711 x_docErrorRec.transaction_type := p_trxn_type;
712
713 /*
714 * Fix for bug 5085226:
715 *
716 * Initialize the transaction error id to null for
717 * the error record, each time we enter this method.
718 *
719 * This prevents the previously set transaction error
720 * id from persisting in the x_docErrorRec for a
721 * new record.
722 */
723 x_docErrorRec.transaction_error_id
724 := NULL;
725
726 x_docErrorRec.transaction_id := p_doc_id;
727 x_docErrorRec.calling_app_id := p_ca_id;
728 x_docErrorRec.calling_app_doc_unique_ref1
729 := p_ca_doc_id1;
730 x_docErrorRec.calling_app_doc_unique_ref2
731 := p_ca_doc_id2;
732 x_docErrorRec.calling_app_doc_unique_ref3
733 := p_ca_doc_id3;
734 x_docErrorRec.calling_app_doc_unique_ref4
735 := p_ca_doc_id4;
736 x_docErrorRec.calling_app_doc_unique_ref5
737 := p_ca_doc_id5;
738 x_docErrorRec.pay_proc_trxn_type_code
739 := p_pp_tt_cd;
740 x_docErrorRec.error_date := sysdate;
741 x_docErrorRec.error_status := TRXN_ERROR_ACTIVE;
742
743 /*
744 * Error codes are mapped for certain document/payment
745 * statuses. Get the error code corresponding to the
746 * document/payment status.
747 */
748 IF (p_error_code IS NULL) THEN
749
750 CASE p_doc_status
751 WHEN DOC_STATUS_CA_FAILED THEN
752 l_error_code := 'IBY_DOC_FAILED_BY_CALL_APP';
753 WHEN DOC_STATUS_RELN_FAIL THEN
754 l_error_code := 'IBY_DOC_FAILED_BY_RELATED_DOC';
755 WHEN DOC_STATUS_PAY_VAL_FAIL THEN
756 l_error_code := 'IBY_DOC_FAILED_BY_PARENT_PMT';
757 WHEN DOC_STATUS_FAIL_BY_REJLVL THEN
758 l_error_code := 'IBY_DOC_FAILED_BY_REJ_LVL';
759 WHEN PAY_STATUS_FAIL_VALID THEN
760 l_error_code := 'IBY_PMT_FAILED_VALIDATION';
761 WHEN PAY_STATUS_CA_FAILED THEN
762 l_error_code := 'IBY_PMT_FAILED_BY_CALL_APP';
763 WHEN PAY_STATUS_FAIL_BY_REJLVL THEN
764 l_error_code := 'IBY_PMT_FAILED_BY_REJ_LVL';
765 ELSE
766 /* generic error message */
767 l_error_code := 'IBY_GENERIC_ERROR_MSG';
768 END CASE;
769
770 print_debuginfo(l_module_name, 'Error code derived from '
771 || 'doc/pmt status: '
772 || l_error_code);
773 ELSE
774
775 l_error_code := p_error_code;
776
777 print_debuginfo(l_module_name, 'Error code based on input param: '
778 || l_error_code
779 );
780
781 END IF;
782
783 FND_MESSAGE.set_name('IBY', l_error_code);
784
785 /*
786 * Error tokens are mapped for certain error messages.
787 */
788 CASE l_error_code
789 WHEN 'IBY_DOC_FAILED_BY_RELATED_DOC' THEN
790 FND_MESSAGE.SET_TOKEN('RELDOC', p_rel_doc_id, FALSE);
791 l_token_rec.token_name := 'RELDOC';
792 l_token_rec.token_value := p_rel_doc_id;
793 WHEN 'IBY_DOC_FAILED_BY_REJ_LVL' THEN
794 FND_MESSAGE.SET_TOKEN('TRGDOC', p_rel_doc_id, FALSE);
795 l_token_rec.token_name := 'TRGDOC';
796 l_token_rec.token_value := p_rel_doc_id;
797 WHEN 'IBY_PMT_FAILED_BY_REJ_LVL' THEN
798 FND_MESSAGE.SET_TOKEN('TRGPMT', p_rel_doc_id, FALSE);
799 l_token_rec.token_name := 'TRGPMT';
800 l_token_rec.token_value := p_rel_doc_id;
801 ELSE
802 /* do nothing */
803 NULL;
804 END CASE;
805
806 l_error_msg := FND_MESSAGE.get;
807
808 print_debuginfo(l_module_name, 'Error message derived from error code: '
809 || l_error_msg);
810
811 x_docErrorRec.error_code := l_error_code;
812 x_docErrorRec.error_message := l_error_msg;
813
814 /*
815 * If we created a token record, add it to the token
816 * records table.
817 */
818 IF (l_token_rec.token_name IS NOT NULL) THEN
819 x_docTokenTab(x_docTokenTab.COUNT + 1) := l_token_rec;
820 END IF;
821
822 print_debuginfo(l_module_name, 'EXIT');
823
824 END createErrorRecord;
825
826 /*--------------------------------------------------------------------
827 | NAME:
828 | createPmtErrorRecord
829 |
830 | PURPOSE:
831 |
832 | PARAMETERS:
833 | IN
834 |
835 | OUT
836 |
837 |
838 | RETURNS:
839 |
840 | NOTES:
841 |
842 *---------------------------------------------------------------------*/
843 PROCEDURE createPmtErrorRecord(
844 p_pmt_id IN IBY_PAYMENTS_ALL.payment_id%TYPE,
845 p_pmt_status IN IBY_PAYMENTS_ALL.
846 payment_status%TYPE,
847 p_error_code IN IBY_TRANSACTION_ERRORS.error_code%TYPE
848 DEFAULT NULL,
849 p_error_msg IN IBY_TRANSACTION_ERRORS.error_message%TYPE
850 DEFAULT NULL,
851 x_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE
852 )
853 IS
854
855 l_module_name VARCHAR2(200) := G_PKG_NAME || '.createPmtErrorRecord';
856
857 BEGIN
858
859 print_debuginfo(l_module_name, 'ENTER');
860
861 x_docErrorRec.validation_set_code := 'CORE_PAYMENT_VALIDATION';
862 x_docErrorRec.transaction_type := 'PAYMENT';
863
864 x_docErrorRec.transaction_id := p_pmt_id;
865
866 x_docErrorRec.error_date := sysdate;
867 x_docErrorRec.error_status := TRXN_ERROR_ACTIVE;
868
869 x_docErrorRec.error_code := p_error_code;
870 x_docErrorRec.error_message := p_error_msg;
871
872 x_docErrorRec.override_allowed_on_error_flag
873 := 'N';
874
875 print_debuginfo(l_module_name, 'EXIT');
876
877 END createPmtErrorRecord;
878
879 /*--------------------------------------------------------------------
880 | NAME:
881 | getProfListFromProfileDrivers
882 |
883 | PURPOSE:
884 | Derives the payment profile for the given (payment method,
885 | org, payment currency, internal bank acct)
886 | combination.
887 |
888 | PARAMETERS:
889 | IN
890 |
891 | OUT
892 |
893 |
894 | RETURNS:
895 |
896 |
897 | NOTES:
898 |
899 *---------------------------------------------------------------------*/
900 PROCEDURE getProfListFromProfileDrivers(
901 p_pmt_method_cd IN IBY_DOCS_PAYABLE_ALL.payment_method_code%TYPE,
902 p_org_id IN IBY_DOCS_PAYABLE_ALL.org_id%TYPE,
903 p_org_type IN IBY_DOCS_PAYABLE_ALL.org_type%TYPE,
904 p_pmt_currency IN IBY_DOCS_PAYABLE_ALL.payment_currency_code%TYPE,
905 p_int_bank_acct_id IN IBY_DOCS_PAYABLE_ALL.internal_bank_account_id%TYPE,
906 x_profilesTab IN OUT NOCOPY pmtProfTabType
907 )
908 IS
909 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
910 '.getProfListFromProfileDrivers';
911
912 /*
913 * We need to select payment profiles that are applicable to
914 * given (payment method, org, currency, int bank account).
915 *
916 *
917 * | Profiles |
918 * | applicable to |
919 * | given pmt | Profiles applicable to
920 * | method | given payment currency
921 * | | /
922 * | | | /
923 * | V | L
924 * | |
925 * |----------------|--------------------------
926 * |/ \| Profiles
927 * | | applicable to
928 * | Intersection | <-- given
929 * | | org id and org type
930 * |\ /|
931 * |----------------|--------------------------
932 * | |
933 * | | .__
934 * | | |\
935 * | | \
936 * | |
937 * | | Profiles applicable to
938 * | | given internal bank
939 * | | account
940 * | |
941 * | |
942 *
943 * We need the intersection of (profiles applicable to
944 * a given payment method) and (profiles applicable to
945 * a given org) and (profiles applicable to given payment
946 * currency) and (profiles applicable to given internal
947 * bank account) as shown in the graphic.
948 *
949 * Therefore, we need to join with the IBY_APPLICABLE_PMT_PROFS
950 * four times - once to get the profiles for the method, once to get
951 * the profiles for the org, and once to get the profiles for the
952 * currency etc. If we are able to get a non-null intersect for these
953 * five queries, it means that there is a profile that matches the
954 * (org, method, currency, bank acct) combination.
955 *
956 * If the 'applicable_value_to' is set to NULL, it means that the
957 * profile is applicable to 'all orgs' | 'all methods' |
958 * 'all formats' etc., depending upon the applicable_type_code.
959 * Therefore, we need to factor this condition in the join.
960 */
961
962 /*
963 * Redundant joins of the form where a=b=c=a execute faster than
964 * where a=b=c joins. That's why this select has a redundant join.
965 */
966 CURSOR c_profiles(
967 p_pmt_method_cd IN IBY_DOCS_PAYABLE_ALL.
968 payment_method_code%TYPE,
969 p_org_id IN IBY_DOCS_PAYABLE_ALL.org_id%TYPE,
970 p_org_type IN IBY_DOCS_PAYABLE_ALL.org_type%TYPE,
971 p_pmt_currency IN IBY_DOCS_PAYABLE_ALL.
972 payment_currency_code%TYPE,
973 p_int_bank_acct_id IN IBY_DOCS_PAYABLE_ALL.
974 internal_bank_account_id%TYPE
975 )
976 IS
977 SELECT
978 acct_prof.payment_profile_id
979 FROM
980 IBY_APPLICABLE_PMT_PROFS app1,
981 IBY_APPLICABLE_PMT_PROFS app2,
982 IBY_APPLICABLE_PMT_PROFS app3,
983 IBY_APPLICABLE_PMT_PROFS app4,
984 IBY_SYS_PMT_PROFILES_B sys_prof,
985 IBY_ACCT_PMT_PROFILES_B acct_prof
986 WHERE
987 /*
988 * org id is a special case; since each org id has meaning
989 * only within an org type, we need to always use
990 * (org id, org type) as a combination.
991 *
992 * This means that both org id and org type need to be
993 * stored in the same row. In order to simplify matters,
994 * the org type field will be stored in the 'applicable
995 * value from' column wherever org id is stored.
996 *
997 * If org id is null, do not check org type - special case.
998 */
999 (app1.applicable_type_code=APL_TYPE_ORG AND
1000 (app1.applicable_value_to IS NULL) OR
1001 (app1.applicable_value_to=TO_CHAR(p_org_id) AND
1002 app1.applicable_value_from=p_org_type))
1003 AND (app2.applicable_type_code=APL_TYPE_PMT_METHOD AND
1004 (app2.applicable_value_to=p_pmt_method_cd OR
1005 app2.applicable_value_to IS NULL))
1006 AND (app3.applicable_type_code=APL_TYPE_PMT_CURRENCY AND
1007 (app3.applicable_value_to=p_pmt_currency OR
1008 app3.applicable_value_to IS NULL))
1009 AND (app4.applicable_type_code=APL_TYPE_INT_BANK_ACCT AND
1010 (app4.applicable_value_to=TO_CHAR(p_int_bank_acct_id) OR
1011 app4.applicable_value_to IS NULL))
1012 AND
1013 app1.system_profile_code=app2.system_profile_code
1014 AND
1015 app2.system_profile_code=app3.system_profile_code
1016 AND
1017 app3.system_profile_code=app4.system_profile_code
1018 AND
1019 app4.system_profile_code=app1.system_profile_code
1020 AND
1021 app1.system_profile_code=sys_prof.system_profile_code
1022 AND
1023 sys_prof.system_profile_code=acct_prof.system_profile_code(+)
1024 /*
1025 * Fix for bug 5929889:
1026 *
1027 * Filter profiles by inactive date so that we do not
1028 * pick up end-dated profiles.
1029 */
1030 AND NVL(sys_prof.inactive_date, SYSDATE + 1) > SYSDATE
1031 ;
1032
1033 BEGIN
1034
1035 print_debuginfo(l_module_name, 'ENTER');
1036
1037 print_debuginfo(l_module_name, 'Checking for profiles '
1038 || 'applicable for given org id '
1039 || p_org_id
1040 || ' and org type '
1041 || p_org_type
1042 || ' and payment method '
1043 || p_pmt_method_cd
1044 || ' and payment currency '
1045 || p_pmt_currency
1046 || ' and internal bank account '
1047 || p_int_bank_acct_id
1048 || ' combination ...'
1049 );
1050
1051 print_debuginfo(l_module_name, 'ENTER');
1052
1053 OPEN c_profiles(p_pmt_method_cd,
1054 p_org_id,
1055 p_org_type,
1056 p_pmt_currency,
1057 p_int_bank_acct_id
1058 );
1059 FETCH c_profiles BULK COLLECT INTO x_profilesTab;
1060 CLOSE c_profiles;
1061
1062 IF (x_profilesTab.COUNT = 0) THEN
1063
1064 print_debuginfo(l_module_name, 'No applicable payment profiles '
1065 || 'were found.');
1066
1067 ELSE
1068
1069 print_debuginfo(l_module_name, 'Count of applicable payment profiles: '
1070 || x_profilesTab.COUNT);
1071
1072 END IF;
1073
1074 print_debuginfo(l_module_name, 'EXIT');
1075
1076 EXCEPTION
1077 WHEN OTHERS THEN
1078 print_debuginfo(l_module_name, 'Non-Fatal: Exception when '
1079 || 'attempting to get payment profile for given (payment '
1080 || 'method, org, currency, int bank acct) '
1081 || 'combination.'
1082 );
1083 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
1084 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
1085
1086 print_debuginfo(l_module_name, 'EXIT');
1087
1088 END getProfListFromProfileDrivers;
1089
1090 /*--------------------------------------------------------------------
1091 | NAME:
1092 | getProfileMap
1093 |
1094 | PURPOSE:
1095 |
1096 | PARAMETERS:
1097 | IN
1098 |
1099 |
1100 | OUT
1101 |
1102 |
1103 | RETURNS:
1104 |
1105 | NOTES:
1106 |
1107 *---------------------------------------------------------------------*/
1108 PROCEDURE getProfileMap(
1109 x_profileMap IN OUT NOCOPY profileIdToCodeMapTabType
1110 )
1111 IS
1112 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.getProfileMap';
1113
1114 /*
1115 * Cursor to pick up mapping between a profile id and a
1116 * profile code.
1117 */
1118 CURSOR c_profile_map
1119 IS
1120 SELECT
1121 payment_profile_id,
1122 system_profile_code
1123 FROM
1124 IBY_PAYMENT_PROFILES;
1125
1126 BEGIN
1127
1128 print_debuginfo(l_module_name, 'ENTER');
1129
1130 /*
1131 * Pick up profile mapping.
1132 */
1133 OPEN c_profile_map;
1134 FETCH c_profile_map BULK COLLECT INTO x_profileMap;
1135 CLOSE c_profile_map;
1136
1137
1138 print_debuginfo(l_module_name, 'Profile map contains '
1139 || x_profileMap.COUNT
1140 || ' records.'
1141 );
1142
1143 print_debuginfo(l_module_name, 'EXIT');
1144
1145 END getProfileMap;
1146
1147
1148 /*--------------------------------------------------------------------
1149 | NAME:
1150 | getProfileCodeFromId
1151 |
1152 | PURPOSE:
1153 |
1154 | PARAMETERS:
1155 | IN
1156 |
1157 |
1158 | OUT
1159 |
1160 |
1161 | RETURNS:
1162 |
1163 | NOTES:
1164 |
1165 *---------------------------------------------------------------------*/
1166 FUNCTION getProfileCodeFromId(
1167 p_profile_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_profile_id%TYPE,
1168 p_profileMap IN profileIdToCodeMapTabType
1169 ) RETURN VARCHAR2
1170 IS
1171 l_profile_code IBY_PAYMENT_PROFILES.system_profile_code%TYPE;
1172
1173 BEGIN
1174
1175 IF (p_profileMap.COUNT = 0) THEN
1176 l_profile_code := NULL;
1177 RETURN l_profile_code;
1178 END IF;
1179
1180 l_profile_code := NULL;
1181 FOR i IN p_profileMap.FIRST .. p_profileMap.LAST LOOP
1182 IF (p_profileMap(i).pmt_profile_id = p_profile_id) THEN
1183 l_profile_code := p_profileMap(i).pmt_profile_cd;
1184 EXIT;
1185 END IF;
1186 END LOOP;
1187
1188 RETURN l_profile_code;
1189
1190 END getProfileCodeFromId;
1191
1192 /*--------------------------------------------------------------------
1193 | NAME:
1194 | printWrappedString
1195 |
1196 | PURPOSE:
1197 |
1198 | PARAMETERS:
1199 | IN
1200 |
1201 |
1202 | OUT
1203 |
1204 |
1205 | RETURNS:
1206 |
1207 | NOTES:
1208 |
1209 *---------------------------------------------------------------------*/
1210 PROCEDURE printWrappedString(
1211 p_string IN VARCHAR2
1212 )
1213 IS
1214
1215 l_chunk VARCHAR2(80);
1216 l_length NUMBER := 1;
1217
1218 BEGIN
1219
1220 WHILE(length(p_string) >= l_length) LOOP
1221
1222 l_chunk := substrb(p_string, l_length, 75);
1223
1224 l_chunk := ltrim(l_chunk);
1225 l_chunk := rtrim(l_chunk);
1226
1227 print_debuginfo(' ', l_chunk);
1228
1229 l_length := (l_length + 75);
1230
1231 END LOOP;
1232
1233 END printWrappedString;
1234
1235 /*--------------------------------------------------------------------
1236 | NAME:
1237 | inactivateOldErrors
1238 |
1239 | PURPOSE:
1240 |
1241 | PARAMETERS:
1242 | IN
1243 |
1244 |
1245 | OUT
1246 |
1247 |
1248 | RETURNS:
1249 |
1250 | NOTES:
1251 |
1252 *---------------------------------------------------------------------*/
1253 PROCEDURE inactivateOldErrors(
1254 p_trxn_id IN IBY_TRANSACTION_ERRORS.transaction_id%TYPE,
1255 p_trxn_type IN IBY_TRANSACTION_ERRORS.transaction_type%TYPE
1256 )
1257 IS
1258 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.inactivateOldErrors';
1259
1260 BEGIN
1261
1262 print_debuginfo(l_module_name, 'ENTER');
1263
1264 print_debuginfo(l_module_name, 'Inactivating old errors for '
1265 || p_trxn_type
1266 || ' id: '
1267 || p_trxn_id
1268 );
1269
1270 /*
1271 * Set the error status to inactive in the IBY_TRANSACTION_ERRORS
1272 * table for all rows that contain errors against this
1273 * transaction.
1274 */
1275 UPDATE
1276 IBY_TRANSACTION_ERRORS err
1277 SET
1278 err.error_status = 'INACTIVE',
1279 err.last_update_date = sysdate,
1280 err.last_updated_by = fnd_global.user_id
1281 WHERE
1282 err.transaction_id = p_trxn_id AND
1283 err.transaction_type = p_trxn_type
1284 ;
1285
1286 /*
1287 * Fix for bug 5742548:
1288 *
1289 * When inactivating payment errors, remember to
1290 * inactivate errors on underlying documents payable
1291 * as well.
1292 */
1293 IF (p_trxn_type = 'PAYMENT') THEN
1294
1295 print_debuginfo(l_module_name, 'Inactivating old errors for '
1296 || 'child documents of payment id: '
1297 || p_trxn_id
1298 );
1299
1300 UPDATE
1301 IBY_TRANSACTION_ERRORS err
1302 SET
1303 err.error_status = 'INACTIVE',
1304 err.last_update_date = sysdate,
1305 err.last_updated_by = fnd_global.user_id
1306 WHERE
1307 err.transaction_id IN
1308 (
1309 SELECT
1310 document_payable_id
1311 FROM
1312 IBY_DOCS_PAYABLE_ALL
1313 WHERE
1314 payment_id = p_trxn_id
1315 )
1316 AND
1317 err.transaction_type = 'DOCUMENT_PAYABLE'
1318 ;
1319
1320 END IF;
1321
1322 /*
1323 * Reset the transaction status to a success / neutral
1324 * status. The transaction status will get updated to a
1325 * failure status in case the transaction fails validation
1326 * later.
1327 */
1328 IF (p_trxn_type = 'DOCUMENT_PAYABLE') THEN
1329
1330 print_debuginfo(l_module_name, 'Resetting status of doc'
1331 || ' id: '
1332 || p_trxn_id
1333 || ' to '
1334 || DOC_STATUS_VALIDATED
1335 );
1336
1337 UPDATE
1338 IBY_DOCS_PAYABLE_ALL doc
1339 SET
1340 doc.document_status = DOC_STATUS_VALIDATED
1341 WHERE
1342 doc.document_payable_id = p_trxn_id
1343 ;
1344
1345 ELSIF (p_trxn_type = 'PAYMENT') THEN
1346
1347 print_debuginfo(l_module_name, 'Resetting status of pmt'
1348 || ' id: '
1349 || p_trxn_id
1350 || ' to '
1351 || PAY_STATUS_CREATED
1352 );
1353
1354 UPDATE
1355 IBY_PAYMENTS_ALL pmt
1356 SET
1357 pmt.payment_status = PAY_STATUS_CREATED
1358 WHERE
1359 pmt.payment_id = p_trxn_id
1360 ;
1361
1362 END IF;
1363
1364 print_debuginfo(l_module_name, 'EXIT');
1365
1366 /*
1367 * Any exceptions that occur in this method are non-fatal.
1368 */
1369 EXCEPTION
1370 WHEN OTHERS THEN
1371
1372 print_debuginfo(l_module_name, 'Non Fatal: Exception occured '
1373 || 'when attempting to update error status to inactive '
1374 || 'for transaction.'
1375 );
1376
1377 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
1378 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
1379
1380 print_debuginfo(l_module_name, 'EXIT');
1381
1382 END inactivateOldErrors;
1383
1384 END IBY_BUILD_UTILS_PKG;
[Home] [Help]