[Home] [Help]
PACKAGE BODY: APPS.IBY_VALIDATIONSETS_PUB
Source
1 PACKAGE BODY IBY_VALIDATIONSETS_PUB AS
2 /*$Header: ibyvallb.pls 120.60.12010000.1 2008/07/28 05:43:18 appldev ship $*/
3
4 --
5 -- Declaring Global variables
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_VALIDATIONSETS_PUB';
8
9 --
10 -- User Defined Exceptions
11 --
12 g_abort_program EXCEPTION;
13
14 --
15 -- List of rejection level system options that are possible for
16 -- this module (document validation flow).
17 --
18 REJ_LVL_REQUEST CONSTANT VARCHAR2(100) := 'REQUEST';
19 REJ_LVL_DOCUMENT CONSTANT VARCHAR2(100) := 'DOCUMENT';
20 REJ_LVL_NONE CONSTANT VARCHAR2(100) := 'NONE';
21 REJ_LVL_PAYEE CONSTANT VARCHAR2(100) := 'PAYEE';
22
23 --
24 -- List of document statuses that are used / set in this
25 -- module (document validation flow).
26 --
27 DOC_STATUS_RDY_FOR_VAL CONSTANT VARCHAR2(100) := 'READY_FOR_VALIDATION';
28 DOC_STATUS_REJECTED CONSTANT VARCHAR2(100) := 'REJECTED';
29 DOC_STATUS_FAIL_VALID CONSTANT VARCHAR2(100) := 'FAILED_VALIDATION';
30 DOC_STATUS_VALIDATED CONSTANT VARCHAR2(100) := 'VALIDATED';
31 DOC_STATUS_RELN_FAIL CONSTANT VARCHAR2(100) := 'FAILED_BY_RELATED_DOCUMENT';
32 DOC_STATUS_FAIL_BY_REJLVL CONSTANT VARCHAR2(100)
33 := 'FAILED_BY_REJECTION_LEVEL';
34 DOC_STATUS_FAIL_BY_CA CONSTANT VARCHAR2(100)
35 := 'FAILED_BY_CALLING_APP';
36 DOC_STATUS_REMOVED CONSTANT VARCHAR2(100) := 'REMOVED';
37
38 --
39 -- List of payment statuses that are used / set in this
40 -- module (payment creation flow).
41 --
42 PAY_STATUS_INS_CRTD CONSTANT VARCHAR2(100) := 'INSTRUCTION_CREATED';
43
44 --
45 -- List of payment request statuses that are set in this
46 -- module (document validation flow).
47 --
48 REQ_STATUS_FAIL_VAL CONSTANT VARCHAR2(100) := 'VALIDATION_FAILED';
49 REQ_STATUS_VALIDATED CONSTANT VARCHAR2(100) := 'DOCUMENTS_VALIDATED';
50 REQ_STATUS_USER_REVW_ERR CONSTANT VARCHAR2(100) :=
51 'PENDING_REVIEW_DOC_VAL_ERRORS';
52
53 -- Transaction type (for inserting into IBY_TRANSACTION_ERRORS table)
54 TRXN_TYPE_DOC CONSTANT VARCHAR2(100) := 'DOCUMENT_PAYABLE';
55
56 -- Dummy record
57 l_dummy_err_token_tab trxnErrTokenTabType;
58
59 /*--------------------------------------------------------------------
60 | NAME:
61 | print_debuginfo
62 |
63 | PURPOSE:
64 | This procedure prints the debug message to the concurrent manager
65 | log file.
66 |
67 | PARAMETERS:
68 | IN
69 | p_debug_text - The debug message to be printed
70 |
71 | OUT
72 |
73 |
74 | RETURNS:
75 |
76 | NOTES:
77 |
78 *---------------------------------------------------------------------*/
79 PROCEDURE print_debuginfo(p_module IN VARCHAR2,
80 p_debug_text IN VARCHAR2,
81 p_debug_level IN VARCHAR2 DEFAULT
82 FND_LOG.LEVEL_STATEMENT
83 )
84 IS
85 l_default_debug_level VARCHAR2(200) := FND_LOG.LEVEL_STATEMENT;
86 BEGIN
87
88 /*
89 * Set the debug level to the value passed in
90 * (provided this value is not null).
91 */
92 IF (p_debug_level IS NOT NULL) THEN
93 l_default_debug_level := p_debug_level;
94 END IF;
95
96 /*
97 * Write the debug message to the concurrent manager log file.
98 */
99
100 /*
101 * Fix for bug 5578607:
102 *
103 * Call the underlying routine only if the current debug
104 * level exceeds the runtime debug level.
105 */
106 IF (l_default_debug_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
107
108 iby_build_utils_pkg.print_debuginfo(p_module, p_debug_text,
109 p_debug_level);
110
111 END IF;
112
113 END print_debuginfo;
114
115 /*--------------------------------------------------------------------
116 | NAME:
117 | performDBUpdates
118 |
119 | PURPOSE:
120 | Updates the status of the payment request and documents of the
121 | payment request.
122 |
123 | PARAMETERS:
124 | IN
125 |
126 |
127 | OUT
128 |
129 |
130 | RETURNS:
131 |
132 | NOTES:
133 |
134 *---------------------------------------------------------------------*/
135 PROCEDURE performDBUpdates(
136 p_pay_service_request_id
137 IN IBY_PAY_SERVICE_REQUESTS.
138 payment_service_request_id%type,
139 p_allDocsTab IN docPayTabType,
140 x_errorDocsTab IN OUT NOCOPY docStatusTabType,
141 p_allDocsSuccessFlag IN BOOLEAN,
142 p_allDocsFailedFlag IN BOOLEAN,
143 p_rejectionLevel IN VARCHAR2,
144 x_txnErrorsTab IN OUT NOCOPY docErrorTabType,
145 x_errTokenTab IN OUT NOCOPY trxnErrTokenTabType,
146 x_return_status IN OUT NOCOPY VARCHAR2
147 )
148 IS
149
150 l_request_status VARCHAR2(200);
151 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.performDBUpdates';
152 l_flag VARCHAR2(1) := 'N';
153 l_empty_table docStatusTabType;
154
155 BEGIN
156
157 print_debuginfo(l_module_name, 'ENTER');
158
159 /*
160 * Get the rejection level system option
161 */
162 print_debuginfo(l_module_name, 'Rejection level system option set to: '
163 || p_rejectionLevel);
164
165
166 /*-----------START DEBUG---------------*/
167
168 IF (x_errorDocsTab.COUNT <> 0) THEN
169
170 print_debuginfo(l_module_name, 'Printing list of error documents: ');
171
172 FOR i IN x_errorDocsTab.FIRST .. x_errorDocsTab.LAST LOOP
173
174 print_debuginfo(l_module_name, 'doc id: '
175 || x_errorDocsTab(i).doc_id
176 || ', doc status: '
177 || x_errorDocsTab(i).doc_status);
178
179 END LOOP;
180
181 ELSE
182
183 print_debuginfo(l_module_name, 'list of error documents is empty');
184
185 END IF;
186
187 /*-----------END DEBUG---------------*/
188
189 /*
190 * Log the states of important flags
191 */
192 IF (p_allDocsFailedFlag = TRUE) THEN
193 l_flag := 'Y';
194 END IF;
195 print_debuginfo(l_module_name, 'All docs failed flag: ' || l_flag);
196
197 l_flag := 'N';
198 IF (p_allDocsSuccessFlag = TRUE) THEN
199 l_flag := 'Y';
200 END IF;
201 print_debuginfo(l_module_name, 'All docs success flag: ' || l_flag);
202
203
204 IF (p_rejectionLevel = REJ_LVL_REQUEST) THEN
205
206 /*
207 * For request level rejections, even if one document
208 * in the request has failed validation, set the status
209 * of the request to 'failed validation'.
210 */
211 IF (p_allDocsSuccessFlag = TRUE) THEN
212 l_request_status := REQ_STATUS_VALIDATED;
213 ELSE
214 l_request_status := REQ_STATUS_FAIL_VAL;
215 END IF;
216
217 /*
218 * If all docs have not passed validations, it
219 * means that at least one doc is in error.
220 *
221 * In such a case, all documents in the request
222 * have to be failed.
223 */
224 IF (p_allDocsSuccessFlag <> TRUE) THEN
225 failAllDocsForRequest(p_allDocsTab, x_errorDocsTab,
226 x_txnErrorsTab, x_errTokenTab);
227 END IF;
228
229 ELSIF (p_rejectionLevel = REJ_LVL_DOCUMENT) THEN
230
231 /*
232 * If all documents have failed validation for this
233 * payment request, set the status of the request
234 * to 'failed validation'; Otherwise, set the status
235 * of the request as 'validated'.
236 */
237 IF (p_allDocsFailedFlag = TRUE) THEN
238 l_request_status := REQ_STATUS_FAIL_VAL;
239 ELSE
240 l_request_status := REQ_STATUS_VALIDATED;
241 END IF;
242
243 ELSIF (p_rejectionLevel = REJ_LVL_PAYEE) THEN
244
245 /*
246 * Payee rejection level is similar to the
247 * document rejection level, the difference
248 * being that all documents for a particular
249 * payee must be failed if any documents for that
250 * payee has failed. This cascade failure
251 * has already happened before this method is called.
252 */
253 IF (p_allDocsFailedFlag = TRUE) THEN
254 l_request_status := REQ_STATUS_FAIL_VAL;
255 ELSE
256 l_request_status := REQ_STATUS_VALIDATED;
257 END IF;
258
259 ELSIF (p_rejectionLevel = REJ_LVL_NONE) THEN
260
261 /*
262 * For rejection level 'none', set request status
263 * to 'validated' if all documents were successfully
264 * validated, else, set request status to 'user
265 * review'.
266 */
267 IF (p_allDocsSuccessFlag = TRUE) THEN
268 l_request_status := REQ_STATUS_VALIDATED;
269 ELSE
270 print_debuginfo(l_module_name, 'At least one doc '
271 || 'has failed. Setting request status to '
272 || REQ_STATUS_USER_REVW_ERR
273 );
274 l_request_status := REQ_STATUS_USER_REVW_ERR;
275
276 /*
277 * Special handling of failed documents:
278 *
279 * In the nomal scenario, when a document fails
280 * validation, it's status is set to 'REJECTED'
281 * and the document is kicked back to the calling
282 * app via a business event. This is the end of
283 * the lifecycle for these failed documents.
284 *
285 * In the case of rejection level 'NONE', if a
286 * document fails validation, it is not kicked back
287 * to the calling app. Instead, it sits in IBY
288 * and waits for the user to take corrective action
289 * via the IBY UI. Therefore, these documents that
290 * are failed but not kicked back, should have a
291 * special status to indicate that though failed,
292 * these documents are still 'alive' in IBY.
293 *
294 * Therefore, we use a special status 'FAILED_VALIDATION'
295 * to differentiate between documents that have failed
296 * but not have been kicked back, and documents
297 * that have failed and have been kicked back
298 * (those that have been kicked back will have
299 * status 'REJECTED').
300 */
301 FOR i in x_errorDocsTab.FIRST .. x_errorDocsTab.LAST LOOP
302 x_errorDocsTab(i).doc_status := DOC_STATUS_FAIL_VALID;
303 END LOOP;
304
305 END IF;
306
307 ELSE
308
309 print_debuginfo(l_module_name, 'Unknown rejection level: '
310 || p_rejectionLevel
311 || '. Aborting document validation ..',
312 FND_LOG.LEVEL_UNEXPECTED
313 );
314
315 APP_EXCEPTION.RAISE_EXCEPTION;
316
317 END IF;
318
319 /*
320 * Update the status of the invalid documents
321 */
322 IF (x_errorDocsTab.COUNT > 0) THEN
323
324 FOR i in x_errorDocsTab.FIRST..x_errorDocsTab.LAST LOOP
325
326 UPDATE
327 IBY_DOCS_PAYABLE_ALL
328 SET
329 document_status = x_errorDocsTab(i).doc_status,
330
331 /*
332 * Fix for bug 4405981:
333 *
334 * The straight through flag should be set to 'N',
335 * if the document was rejected / required manual
336 * intervention.
337 */
338 straight_through_flag =
339 DECODE(
340 x_errorDocsTab(i).doc_status,
341 DOC_STATUS_REJECTED, 'N',
342 DOC_STATUS_RELN_FAIL, 'N',
343 DOC_STATUS_FAIL_BY_REJLVL, 'N',
344 DOC_STATUS_FAIL_BY_CA, 'N',
345 DOC_STATUS_REMOVED, 'N',
346 'Y'
347 )
348 WHERE
349 document_payable_id = x_errorDocsTab(i).doc_id
350 AND
351 payment_service_request_id = p_pay_service_request_id;
352
353 END LOOP;
354
355 END IF;
356
357 /*
358 * All documents that haven't failed validation must have been
359 * successfully validated. Set the status of these docs to
360 * validated.
361 */
362 /* Update the status of the valid documents */
363 UPDATE
364 IBY_DOCS_PAYABLE_ALL
365 SET
366 document_status = DOC_STATUS_VALIDATED
367 WHERE
368 document_status NOT IN
369 (
370 DOC_STATUS_REJECTED,
371 DOC_STATUS_RELN_FAIL,
372 DOC_STATUS_FAIL_BY_REJLVL,
373 DOC_STATUS_FAIL_VALID,
374 DOC_STATUS_FAIL_BY_CA,
375 DOC_STATUS_REMOVED
376 ) AND
377 payment_service_request_id = p_pay_service_request_id;
378
379 /*
380 * We have collected all the error messages against the failed
381 * documents in a PLSQL table. Use this to update the
382 * IBY_TRANSACTION_ERRORS table.
383 */
384 insert_transaction_errors('N', x_txnErrorsTab, x_errTokenTab);
385
386 /*
387 * Finally, update the status of the payment request.
388 */
389 print_debuginfo(l_module_name, 'Updating status of payment request '
390 || p_pay_service_request_id || ' to ' || l_request_status || '.');
391
392 UPDATE
393 IBY_PAY_SERVICE_REQUESTS
394 SET
395 payment_service_request_status = l_request_status
396 WHERE
397 payment_service_request_id = p_pay_service_request_id;
398
399 /*
400 * Pass back the request status to the caller.
401 */
402 x_return_status := l_request_status;
403
404 print_debuginfo(l_module_name, 'EXIT');
405
406 EXCEPTION
407 WHEN OTHERS THEN
408 print_debuginfo(l_module_name, 'Fatal: Exception when updating '
409 || 'payment request/document status after document '
410 || 'validation. All changes will be rolled back. Payment request '
411 || 'id is ' || p_pay_service_request_id,
412 FND_LOG.LEVEL_UNEXPECTED
413 );
414 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
415 FND_LOG.LEVEL_UNEXPECTED);
416 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
417 FND_LOG.LEVEL_UNEXPECTED);
418
419 /*
420 * Propogate exception to caller.
421 */
422 RAISE;
423
424 END performDBUpdates;
425
426 /*--------------------------------------------------------------------
427 | NAME:
428 | validate_CH_EST
429 |
430 | PURPOSE:
431 | This function is to validate Switzerland ESR reference number.
432 |
433 |
434 | PARAMETERS:
435 | IN
436 |
437 |
438 | OUT
439 |
440 |
441 | RETURNS:
442 |
443 | NOTES:
444 |
445 *---------------------------------------------------------------------*/
446 FUNCTION validate_CH_EST ( p_esr_number IN VARCHAR2
447 ) RETURN BOOLEAN IS
448
449 l_check_string VARCHAR2(20) := '9468271350946827135';
450
451 l_number_carried NUMBER;
452 l_esr_length NUMBER;
453 l_esr_check_digit NUMBER(1);
454 l_esr_digit VARCHAR(1);
455 l_calculated_check_digit VARCHAR(10);
456 l_number_to_check NUMBER;
457
458 BEGIN
459 l_number_carried := 0;
460
461 l_esr_length := nvl(length(p_esr_number), 0);
462
463 -- Take the check digit
464 l_esr_check_digit := substr(p_esr_number, l_esr_length, 1);
465
466 FOR l_esr_position in 1..l_esr_length-1
467 LOOP
468 l_esr_digit := substr(p_esr_number, l_esr_position, 1);
469 l_number_to_check := l_number_carried + to_number(l_esr_digit);
470 l_number_carried := substr(l_check_string, l_number_to_check, 1);
471 END LOOP;
472
473 l_calculated_check_digit := to_char(10 - l_number_carried);
474
475 IF l_calculated_check_digit <> l_esr_check_digit THEN
476 return (FALSE);
477 ELSE
478 return (TRUE);
479 END IF;
480
481 END validate_CH_EST;
482
483
484 --
485 -- The following are public API's.
486 --
487
488 /*--------------------------------------------------------------------
489 | NAME:
490 | applyDocumentValidationSets
491 |
492 | PURPOSE:
493 | Picks up validation sets which are applicable to a document
494 | and validates each document in the payment request
495 |
496 | PARAMETERS:
497 | IN
498 |
499 |
500 | OUT
501 |
502 |
503 | RETURNS:
504 |
505 | NOTES:
506 |
507 *---------------------------------------------------------------------*/
508 PROCEDURE applyDocumentValidationSets(
509 p_pay_service_request_id IN IBY_PAY_SERVICE_REQUESTS.
510 payment_service_request_id%TYPE,
511 p_doc_rejection_level IN IBY_INTERNAL_PAYERS_ALL.
512 document_rejection_level_code%TYPE,
513 p_is_singpay_flag IN BOOLEAN,
514 x_return_status IN OUT NOCOPY VARCHAR2)
515 IS
516
517 l_stmt VARCHAR2(200);
518
519 /* 0 indicates success; non-zero indicates failure */
520 l_result NUMBER := 0;
521
522 /* holds list of all documents for a payment request */
523 l_docs_tab docPayTabType;
524
525 /* holds list of failed documents for a payment request */
526 l_invalid_docs_tab docStatusTabType;
527 l_invalid_doc_rec docStatusRecType;
528 l_doc_error_tab docErrorTabType;
529
530 /* holds list of validation sets applicable to a particular document */
531 l_val_sets_tab valSetTabType;
532
533 l_all_docs_success_flag BOOLEAN := FALSE;
534 l_all_docs_failed_flag BOOLEAN := TRUE;
535 l_doc_failed_flag BOOLEAN := FALSE;
536 l_is_valid BOOLEAN := FALSE;
537 l_already_failed_flag BOOLEAN := FALSE;
538
539 l_rejection_level VARCHAR2(200);
540
541 /* holds list of default format for each payee */
542 l_payee_format_tab payeeFormatTabType;
543
544 /* holds list of format linked to each profile */
545 l_profile_format_tab profileFormatTabType;
546
547 /* variables for fields from payment request */
548 req_ca_payreq_cd iby_pay_service_requests.
549 call_app_pay_service_req_code%TYPE;
550 req_ca_id iby_pay_service_requests.calling_app_id%TYPE;
551
552 l_bankAccountsArray CE_BANK_AND_ACCOUNT_UTIL.BankAcctIdTable;
553
554 l_print_var VARCHAR2(1) := '';
555 l_doc_err_rec IBY_TRANSACTION_ERRORS%ROWTYPE;
556 l_doc_token_tab trxnErrTokenTabType;
557
558 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
559 '.applyDocumentValidationSets';
560
561 /*
562 * Pick up all documents for the specified payment request
563 */
564
565 /*
566 * Pick up validated documents also in this cursor. Otherwise,
567 * if all invalid documents are dismissed by the user we will
568 * end up with a situation where no documents are picked up
569 * by this cursor, and the request will be left unprocessed
570 * during payment re-validation flow.
571 */
572 CURSOR c_docs_list(p_pay_service_request_id IBY_PAY_SERVICE_REQUESTS.
573 payment_service_request_id%TYPE)
574 IS
575 SELECT DISTINCT
576 docs.document_payable_id,
577 docs.calling_app_doc_unique_ref1,
578 docs.calling_app_doc_unique_ref2,
579 docs.calling_app_doc_unique_ref3,
580 docs.calling_app_doc_unique_ref4,
581 docs.calling_app_doc_unique_ref5,
582 docs.calling_app_doc_ref_number,
583 docs.calling_app_id,
584 docs.pay_proc_trxn_type_code,
585 docs.payment_grouping_number,
586 docs.ext_payee_id,
587 docs.payment_profile_id,
588 docs.org_id,
589 docs.org_type,
590 docs.payment_method_code,
591 docs.payment_format_code,
592 docs.payment_currency_code,
593 docs.internal_bank_account_id,
594 docs.payment_date,
595 nvl(iba_branch.country,iba_branch.bank_home_country) int_bank_country_code,
596 eba.country_code ext_bank_country_code,
597 eba.foreign_payment_use_flag foreign_pmts_allowed_flag
598 FROM
599 IBY_DOCS_PAYABLE_ALL docs,
600 CE_BANK_ACCOUNTS iba,
601 CE_BANK_BRANCHES_V iba_branch,
602 IBY_EXT_BANK_ACCOUNTS_V eba
603 WHERE
604 docs.payment_service_request_id = p_pay_service_request_id AND
605 docs.internal_bank_account_id = iba.bank_account_id AND
606 iba_branch.branch_party_id = iba.bank_branch_id AND
607 docs.external_bank_account_id = eba.ext_bank_account_id(+) AND
608 docs.document_status IN
609 (
610 DOC_STATUS_RDY_FOR_VAL,
611 DOC_STATUS_FAIL_VALID,
612 DOC_STATUS_FAIL_BY_REJLVL,
613 DOC_STATUS_RELN_FAIL,
614 DOC_STATUS_VALIDATED
615 )
616 ;
617
618 /*
619 * Pick up all validation sets applicable to the
620 * specified document
621 */
622 CURSOR c_validation_sets(p_document_payable_id
623 IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE)
624 IS
625 SELECT DISTINCT
626 docs.document_payable_id,
627 docs.payment_grouping_number,
628 docs.ext_payee_id,
629 val.validation_set_code,
630 val.validation_code_package,
631 val.validation_code_entry_point,
632 val_options.validation_assignment_id,
633 val_options.val_assignment_entity_type,
634 val.validation_set_display_name
635 FROM
636 IBY_VALIDATION_SETS_VL val,
637 IBY_VAL_ASSIGNMENTS val_options,
638 IBY_DOCS_PAYABLE_ALL docs,
639 IBY_SYS_PMT_PROFILES_B sys_prof,
640 IBY_ACCT_PMT_PROFILES_B acct_prof,
641 IBY_TRANSMIT_CONFIGS_B txconf,
642 IBY_TRANSMIT_PROTOCOLS_B txproto,
643 CE_BANK_ACCOUNTS iba,
644 CE_BANK_BRANCHES_V iba_branch
645 WHERE
646 docs.document_payable_id = p_document_payable_id
647 AND docs.internal_bank_account_id = iba.bank_account_id
648 AND iba_branch.branch_party_id = iba.bank_branch_id
649 AND val.validation_set_code = val_options.validation_set_code
650 AND val.validation_level_code = 'DOCUMENT'
651 AND (val_options.val_assignment_entity_type = 'METHOD'
652 AND val_options.assignment_entity_id =
653 docs.payment_method_code
654 OR val_options.val_assignment_entity_type = 'INTBANKACCOUNT'
655 AND val_options.assignment_entity_id =
656 docs.internal_bank_account_id
657 OR val_options.val_assignment_entity_type = 'FORMAT'
658 AND val_options.assignment_entity_id =
659 sys_prof.payment_format_code
660 OR val_options.val_assignment_entity_type = 'BANK'
661 AND val_options.assignment_entity_id =
662 sys_prof.bepid
663 OR val_options.val_assignment_entity_type = 'TRANSPROTOCOL'
664 AND val_options.assignment_entity_id =
665 txconf.transmit_protocol_code
666 )
667 AND docs.payment_profile_id = acct_prof.payment_profile_id(+)
668 AND acct_prof.transmit_configuration_id
669 = txconf.transmit_configuration_id(+)
670 AND txconf.transmit_protocol_code = txproto.transmit_protocol_code(+)
671 AND sys_prof.system_profile_code(+) = acct_prof.system_profile_code
672 AND NVL(val_options.inactive_date, sysdate+1) > sysdate
673
674 /*
675 * Fix for bug 4997133:
676 *
677 * Select validation sets that have the same payment method
678 * code as the document, or have payment method code as null.
679 *
680 * Payment method code null implies that the validation
681 * set is applicable to all payment methods.
682 */
683 AND (NVL(docs.payment_method_code, '0') =
684 NVL(val_options.payment_method_code, '0') OR
685 val_options.payment_method_code IS NULL
686 )
687
688 /*
689 * Fix for bug 4997133:
690 *
691 * Select validation sets that have the same country code
692 * code as the document, or have country code as null.
693 *
694 * Country code null implies that the validation
695 * set is applicable to all countries.
696 */
697 AND (iba_branch.country = val_options.territory_code OR
698 val_options.territory_code IS NULL
699 )
700 ;
701
702 /*
703 * Pick up all payees who have a default format set.
704 * Used in checking whether the provided payment format
705 * and profile are compatible.
706 */
707 CURSOR c_payee_format
708 IS
709 SELECT
710 payee.ext_payee_id,
711 payee.payment_format_code
712 FROM
713 IBY_EXTERNAL_PAYEES_ALL payee
714 WHERE
715 payee.payment_format_code IS NOT NULL
716 ;
717
718 /*
719 * Pick up all payment profiles along with their formats.
720 * Used in checking whether the provided payment format and
721 * profile are compatible.
722 */
723 CURSOR c_profile_format
724 IS
725 SELECT
726 payment_profile_id,
727 payment_format_code
728 FROM
729 IBY_PAYMENT_PROFILES
730 ;
731
732 BEGIN
733 print_debuginfo(l_module_name, 'ENTER');
734
735 print_debuginfo(l_module_name, 'Payment request id: '
736 || p_pay_service_request_id);
737
738 /*
739 * Fetch all the documents for this payment request.
740 */
741 OPEN c_docs_list(p_pay_service_request_id);
742 FETCH c_docs_list BULK COLLECT INTO l_docs_tab;
743 CLOSE c_docs_list;
744
745 /*
746 * Exit if no documents were found.
747 */
748 IF (l_docs_tab.COUNT = 0) THEN
749 print_debuginfo(l_module_name, 'No documents payable were '
750 || 'retrieved from DB for payment request '
751 || p_pay_service_request_id
752 || '. Exiting document validation..');
753
754 print_debuginfo(l_module_name, 'EXIT');
755 RETURN;
756 END IF;
757
758 /*
759 * Fetch list of default format for each payee.
760 */
761 OPEN c_payee_format;
762 FETCH c_payee_format BULK COLLECT INTO l_payee_format_tab;
763 CLOSE c_payee_format;
764
765 /*
766 * Fetch list of format for each profile.
767 */
768 OPEN c_profile_format;
769 FETCH c_profile_format BULK COLLECT INTO l_profile_format_tab;
770 CLOSE c_profile_format;
771
772 /*
773 * Loop through all the documents, validating them one-by-one.
774 */
775 FOR i in l_docs_tab.FIRST .. l_docs_tab.LAST LOOP
776
777 print_debuginfo(l_module_name, 'Validating document id: '
778 || l_docs_tab(i).doc_id
779 );
780
781 print_debuginfo(l_module_name, 'document identifiers: '
782 || ' ('
783 || 'calling app doc unique id1: '
784 || l_docs_tab(i).ca_doc_id1
785 || ' calling app doc unique id2: '
786 || l_docs_tab(i).ca_doc_id2
787 || ' calling app doc unique id3: '
788 || l_docs_tab(i).ca_doc_id3
789 || ' calling app doc ref num: '
790 || l_docs_tab(i).ca_doc_ref_num
791 || ')'
792 );
793
794 /*
795 * Fix for bug 5440434:
796 *
797 * Before doing any validations, set any
798 * existing validation error messages that
799 * exist against this document to 'inactive'
800 * status in the IBY_TRANSACTION_ERRORS table.
801 *
802 * Unless we do this, the old errors will
803 * continue to show up against this document
804 * in the IBY UI even if the document is validated
805 * successfully this time round.
806 */
807 IBY_BUILD_UTILS_PKG.inactivateOldErrors(l_docs_tab(i).doc_id,
808 TRXN_TYPE_DOC);
809
810 /*
811 * STEP 1:
812 * Core build program validations
813 */
814
815 /*
816 * First validate that payment profile on the document
817 * is valid for (pmt method, org, pmt currency,
818 * int bank acct) on the document.
819 */
820 /* Initialize flag */
821 l_is_valid := FALSE;
822
823 l_is_valid := validateProfileFromProfDrivers(
824 l_docs_tab(i).profile_id,
825 l_docs_tab(i).org_id,
826 l_docs_tab(i).org_type,
827 l_docs_tab(i).pmt_method_cd,
828 l_docs_tab(i).pmt_curr_code,
829 l_docs_tab(i).int_bank_acct_id
830 );
831
832 IF (l_is_valid = FALSE) THEN
833 /*
834 * If profile is not applicable,
835 * add doc to list of invalid documents.
836 */
837 print_debuginfo(l_module_name, 'Failing document id: '
838 || l_docs_tab(i).doc_id
839 || ' because payment profile is invalid');
840
841 l_invalid_doc_rec.doc_id := l_docs_tab(i).doc_id;
842 l_invalid_doc_rec.pmt_grp_num := l_docs_tab(i).pmt_grp_num;
843 l_invalid_doc_rec.payee_id := l_docs_tab(i).payee_id;
844 l_invalid_doc_rec.doc_status := DOC_STATUS_REJECTED;
845 l_invalid_docs_tab(l_invalid_docs_tab.COUNT + 1) :=
846 l_invalid_doc_rec;
847
848 l_doc_failed_flag := TRUE;
849
850 /*
851 * Once we fail a doc, we must add a corresponding
852 * error message to the error table.
853 */
854 IBY_BUILD_UTILS_PKG.createErrorRecord(
855 TRXN_TYPE_DOC,
856 l_docs_tab(i).doc_id,
857 l_invalid_doc_rec.doc_status,
858 l_docs_tab(i).ca_id,
859 l_docs_tab(i).ca_doc_id1,
860 l_docs_tab(i).ca_doc_id2,
861 l_docs_tab(i).ca_doc_id3,
862 l_docs_tab(i).ca_doc_id4,
863 l_docs_tab(i).ca_doc_id5,
864 l_docs_tab(i).pp_tt_cd,
865 l_doc_err_rec,
866 l_doc_token_tab,
867 NULL,
868 'IBY_DOC_INVALID_PROFILE'
869 );
870
871 insertIntoErrorTable(l_doc_err_rec, l_doc_error_tab,
872 l_doc_token_tab);
873
874 ELSE
875
876 print_debuginfo(l_module_name, 'Payment profile is valid for '
877 || 'document id: '
878 || l_docs_tab(i).doc_id);
879
880 END IF;
881
882 /*
883 * Validate that the profile on the document
884 * is compatible with the format on the document
885 * payee.
886 */
887
888 /* Initialize flag */
889 l_is_valid := FALSE;
890
891 l_is_valid := checkProfileFormatCompat(
892 l_docs_tab(i).doc_id,
893 l_docs_tab(i).payee_id,
894 l_docs_tab(i).profile_id,
895 l_payee_format_tab,
896 l_profile_format_tab
897 );
898
899 IF (l_is_valid = FALSE) THEN
900 /*
901 * If profile is not compatible with format,
902 * add doc to list of invalid documents.
903 */
904 print_debuginfo(l_module_name, 'Failing document id: '
905 || l_docs_tab(i).doc_id
906 || ' because payment profile is not compatible '
907 || ' with payment format.'
908 );
909
910 l_invalid_doc_rec.doc_id := l_docs_tab(i).doc_id;
911 l_invalid_doc_rec.pmt_grp_num := l_docs_tab(i).pmt_grp_num;
912 l_invalid_doc_rec.payee_id := l_docs_tab(i).payee_id;
913 l_invalid_doc_rec.doc_status := DOC_STATUS_REJECTED;
914 l_invalid_docs_tab(l_invalid_docs_tab.COUNT + 1) :=
915 l_invalid_doc_rec;
916
917 l_doc_failed_flag := TRUE;
918
919 /*
920 * Once we fail a doc, we must add a corresponding
921 * error message to the error table.
922 */
923 IBY_BUILD_UTILS_PKG.createErrorRecord(
924 TRXN_TYPE_DOC,
925 l_docs_tab(i).doc_id,
926 l_invalid_doc_rec.doc_status,
927 l_docs_tab(i).ca_id,
928 l_docs_tab(i).ca_doc_id1,
929 l_docs_tab(i).ca_doc_id2,
930 l_docs_tab(i).ca_doc_id3,
931 l_docs_tab(i).ca_doc_id4,
932 l_docs_tab(i).ca_doc_id5,
933 l_docs_tab(i).pp_tt_cd,
934 l_doc_err_rec,
935 l_doc_token_tab,
936 NULL,
937 'IBY_DOC_INV_PROFILE_FORMAT'
938 );
939
940 insertIntoErrorTable(l_doc_err_rec, l_doc_error_tab,
941 l_doc_token_tab);
942
943 ELSE
944
945 print_debuginfo(l_module_name, 'Payment profile is compatible '
946 || 'with payment format for '
947 || 'document id: '
948 || l_docs_tab(i).doc_id);
949
950 END IF;
951
952 /*
953 * Next, validate that internal bank account on doc is
954 * valid for (org, currency) on document.
955 */
956
957 /*
958 * Call CE API
959 */
960 CE_BANK_AND_ACCOUNT_UTIL.get_internal_bank_accts(
961 l_docs_tab(i).pmt_curr_code,
962 l_docs_tab(i).org_type,
963 l_docs_tab(i).org_id,
964 l_docs_tab(i).pmt_date,
965 l_bankAccountsArray
966 );
967
968 /* Initialize flag */
969 l_is_valid := FALSE;
970
971 IF (l_bankAccountsArray.COUNT = 0) THEN
972 /*
973 * If no valid bank accounts were returned
974 * by CE, then this bank account is invalid
975 * for this document.
976 */
977 l_is_valid := FALSE;
978 ELSE
979 FOR m in l_bankAccountsArray.FIRST..l_bankAccountsArray.LAST LOOP
980
981 /*
982 * Search if the internal bank account on the doc
983 * is present in the list provided by CE
984 */
985 IF (l_bankAccountsArray(m) = l_docs_tab(i).int_bank_acct_id)
986 THEN
987
988 l_is_valid := TRUE;
989 EXIT;
990
991 END IF;
992
993 END LOOP;
994 END IF;
995
996 IF (l_is_valid = FALSE) THEN
997
998 print_debuginfo(l_module_name, 'Internal bank account '
999 || l_docs_tab(i).int_bank_acct_id
1000 || ' is not applicable for org '
1001 || l_docs_tab(i).org_id
1002 || ', currency '
1003 || l_docs_tab(i).pmt_curr_code
1004 || ' and payment date '
1005 || l_docs_tab(i).pmt_date
1006 || ' combination');
1007
1008 /*
1009 * If int bank account is not applicable,
1010 * add doc to list of invalid documents.
1011 */
1012 print_debuginfo(l_module_name, 'Failing document id: '
1013 || l_docs_tab(i).doc_id
1014 || ' because internal bank account is invalid');
1015
1016 l_already_failed_flag :=
1017 checkIfDocFailed(l_docs_tab(i).doc_id,
1018 l_invalid_docs_tab);
1019
1020 /*
1021 * Add this doc to the list of invalid documents
1022 * only if it is not already present in the
1023 * invalid docs list.
1024 */
1025 IF (l_already_failed_flag = FALSE) THEN
1026
1027 l_invalid_doc_rec.doc_id := l_docs_tab(i).doc_id;
1028 l_invalid_doc_rec.pmt_grp_num := l_docs_tab(i).pmt_grp_num;
1029 l_invalid_doc_rec.payee_id := l_docs_tab(i).payee_id;
1030 l_invalid_doc_rec.doc_status := DOC_STATUS_REJECTED;
1031 l_invalid_docs_tab(l_invalid_docs_tab.COUNT + 1) :=
1032 l_invalid_doc_rec;
1033
1034 l_doc_failed_flag := TRUE;
1035
1036 /*
1037 * Once we fail a doc, we must add a corresponding
1038 * error message to the error table.
1039 */
1040 IBY_BUILD_UTILS_PKG.createErrorRecord(
1041 TRXN_TYPE_DOC,
1042 l_docs_tab(i).doc_id,
1043 l_invalid_doc_rec.doc_status,
1044 l_docs_tab(i).ca_id,
1045 l_docs_tab(i).ca_doc_id1,
1046 l_docs_tab(i).ca_doc_id2,
1047 l_docs_tab(i).ca_doc_id3,
1048 l_docs_tab(i).ca_doc_id4,
1049 l_docs_tab(i).ca_doc_id5,
1050 l_docs_tab(i).pp_tt_cd,
1051 l_doc_err_rec,
1052 l_doc_token_tab,
1053 NULL,
1054 'IBY_DOC_INV_INTBANKACCT'
1055 );
1056
1057 insertIntoErrorTable(l_doc_err_rec, l_doc_error_tab,
1058 l_doc_token_tab);
1059
1060 END IF;
1061
1062 ELSE
1063
1064 print_debuginfo(l_module_name, 'Internal bank acct is valid for '
1065 || 'document id: '
1066 || l_docs_tab(i).doc_id);
1067
1068 END IF; -- if internal bank acct is invalid
1069
1070 /*
1071 * Next, validate that external payee id on the doc is
1072 * valid (not -1). The ext payee id on the document
1073 * is derived from the payee context on the document;
1074 * these are fields such as payee id, payee party id,
1075 * payee party site id etc.
1076 *
1077 * The external party id is derived when the document
1078 * in inserted into the IBY tables. If the logic to
1079 * derive the ext payee id, could not find a matching
1080 * ext payee id for the document, then the ext payee id
1081 * would be set to -1 for that document.
1082 *
1083 * Fail all document that have the ext payee id set to
1084 * -1. The user is expected to seed the IBY_EXTERNAL_PAYEES_ALL
1085 * table such that the ext payee id is always available for
1086 * payee context on the document (otherwise, the
1087 * document cannot be paid!).
1088 */
1089
1090 /* Initialize flag */
1091 l_is_valid := FALSE;
1092
1093 IF (l_docs_tab(i).payee_id = -1) THEN
1094 l_is_valid := FALSE;
1095 ELSE
1096 l_is_valid := TRUE;
1097 END IF;
1098
1099 IF (l_is_valid = FALSE) THEN
1100
1101 /*
1102 * If ext payee id is not available,
1103 * add doc to list of invalid documents.
1104 */
1105 print_debuginfo(l_module_name, 'Failing document id: '
1106 || l_docs_tab(i).doc_id
1107 || ' because external payee id is not '
1108 || 'available for this document'
1109 );
1110
1111 l_already_failed_flag :=
1112 checkIfDocFailed(l_docs_tab(i).doc_id,
1113 l_invalid_docs_tab);
1114
1115 /*
1116 * Add this doc to the list of invalid documents
1117 * only if it is not already present in the
1118 * invalid docs list.
1119 */
1120 IF (l_already_failed_flag = FALSE) THEN
1121
1122 l_invalid_doc_rec.doc_id := l_docs_tab(i).doc_id;
1123 l_invalid_doc_rec.pmt_grp_num := l_docs_tab(i).pmt_grp_num;
1124 l_invalid_doc_rec.payee_id := l_docs_tab(i).payee_id;
1125 l_invalid_doc_rec.doc_status := DOC_STATUS_REJECTED;
1126 l_invalid_docs_tab(l_invalid_docs_tab.COUNT + 1) :=
1127 l_invalid_doc_rec;
1128
1129 l_doc_failed_flag := TRUE;
1130
1131 /*
1132 * Once we fail a doc, we must add a corresponding
1133 * error message to the error table.
1134 */
1135 IBY_BUILD_UTILS_PKG.createErrorRecord(
1136 TRXN_TYPE_DOC,
1137 l_docs_tab(i).doc_id,
1138 l_invalid_doc_rec.doc_status,
1139 l_docs_tab(i).ca_id,
1140 l_docs_tab(i).ca_doc_id1,
1141 l_docs_tab(i).ca_doc_id2,
1142 l_docs_tab(i).ca_doc_id3,
1143 l_docs_tab(i).ca_doc_id4,
1144 l_docs_tab(i).ca_doc_id5,
1145 l_docs_tab(i).pp_tt_cd,
1146 l_doc_err_rec,
1147 l_doc_token_tab,
1148 NULL,
1149 'IBY_DOC_INVALID_EXT_PAYEE'
1150 );
1151
1152 insertIntoErrorTable(l_doc_err_rec, l_doc_error_tab,
1153 l_doc_token_tab);
1154
1155 END IF;
1156
1157 ELSE
1158
1159 print_debuginfo(l_module_name, 'External payee id is valid for '
1160 || 'document id: '
1161 || l_docs_tab(i).doc_id);
1162
1163 END IF; -- if external payee id is invalid
1164
1165 /*
1166 * Validate that the external bank account can be used
1167 * for cross border payments (if the internal bank account
1168 * and external bank accounts belong to different countries).
1169 */
1170
1171 /* Initialize flag */
1172 l_is_valid := FALSE;
1173
1174 IF (l_docs_tab(i).int_bank_country_cd IS NOT NULL AND
1175 l_docs_tab(i).ext_bank_country_cd IS NOT NULL) THEN
1176
1177 IF (l_docs_tab(i).int_bank_country_cd <>
1178 l_docs_tab(i).ext_bank_country_cd) THEN
1179
1180 /*
1181 * This is a cross border payment.
1182 */
1183 IF (UPPER(l_docs_tab(i).foreign_pmts_ok_flag) = 'Y') THEN
1184
1185 l_is_valid := TRUE;
1186
1187 ELSE
1188
1189 /*
1190 * Fail document because cross-border
1191 * payments are not allowed to this external
1192 * bank account.
1193 */
1194 l_is_valid := FALSE;
1195
1196 END IF;
1197
1198 ELSE
1199
1200 /*
1201 * This is a domestic payment. Skip this
1202 * validation.
1203 */
1204 l_is_valid := TRUE;
1205
1206 END IF;
1207
1208 ELSE
1209
1210 /*
1211 * If either internal bank account country is null
1212 * or external bank account country is null, assume
1213 * that the payment is domestic, and skip the validation.
1214 */
1215 l_is_valid := TRUE;
1216
1217 END IF;
1218
1219
1220 IF (l_is_valid = FALSE) THEN
1221
1222 /*
1223 * If cross border payment has been failed,
1224 * add doc to list of invalid documents.
1225 */
1226
1227 -- Changed the error message for bug 5442800 (Panaraya)
1228 print_debuginfo(l_module_name, 'Failing document id: '
1229 || l_docs_tab(i).doc_id
1230 || 'The payee bank account is set up to allow '
1231 || 'domestic payments only. The internal bank '
1232 || 'account used to pay this document will result '
1233 || 'in an international payment to this payee bank '
1234 || 'account.'
1235 );
1236
1237 l_already_failed_flag :=
1238 checkIfDocFailed(l_docs_tab(i).doc_id,
1239 l_invalid_docs_tab);
1240
1241 /*
1242 * Add this doc to the list of invalid documents
1243 * only if it is not already present in the
1244 * invalid docs list.
1245 */
1246 IF (l_already_failed_flag = FALSE) THEN
1247
1248 l_invalid_doc_rec.doc_id := l_docs_tab(i).doc_id;
1249 l_invalid_doc_rec.pmt_grp_num := l_docs_tab(i).pmt_grp_num;
1250 l_invalid_doc_rec.payee_id := l_docs_tab(i).payee_id;
1251 l_invalid_doc_rec.doc_status := DOC_STATUS_REJECTED;
1252 l_invalid_docs_tab(l_invalid_docs_tab.COUNT + 1) :=
1253 l_invalid_doc_rec;
1254
1255 l_doc_failed_flag := TRUE;
1256
1257 /*
1258 * Once we fail a doc, we must add a corresponding
1259 * error message to the error table.
1260 */
1261 IBY_BUILD_UTILS_PKG.createErrorRecord(
1262 TRXN_TYPE_DOC,
1263 l_docs_tab(i).doc_id,
1264 l_invalid_doc_rec.doc_status,
1265 l_docs_tab(i).ca_id,
1266 l_docs_tab(i).ca_doc_id1,
1267 l_docs_tab(i).ca_doc_id2,
1268 l_docs_tab(i).ca_doc_id3,
1269 l_docs_tab(i).ca_doc_id4,
1270 l_docs_tab(i).ca_doc_id5,
1271 l_docs_tab(i).pp_tt_cd,
1272 l_doc_err_rec,
1273 l_doc_token_tab,
1274 NULL,
1275 'IBY_DOC_INV_CROSSBORDER_PMT'
1276 );
1277
1278 insertIntoErrorTable(l_doc_err_rec, l_doc_error_tab,
1279 l_doc_token_tab);
1280
1281 END IF;
1282
1283 END IF; -- if external payee id is invalid
1284
1285 /*
1286 * STEP 2:
1287 * Dynamic validations via validation sets
1288 */
1289
1290 /*
1291 * Fetch all applicable validation sets for this document.
1292 */
1293 OPEN c_validation_sets(l_docs_tab(i).doc_id);
1294 FETCH c_validation_sets BULK COLLECT INTO l_val_sets_tab;
1295 CLOSE c_validation_sets;
1296
1297 /*
1298 * Exit if no validation sets applicable to this doc were found.
1299 */
1300 IF (l_val_sets_tab.COUNT = 0) THEN
1301
1302 print_debuginfo(l_module_name, 'No validation sets applicable '
1303 || 'to document payable id '
1304 || l_docs_tab(i).doc_id
1305 || ' were found. Skipping validations for this doc..');
1306
1307 print_debuginfo(l_module_name, '+-------------------------+');
1308
1309 ELSE
1310
1311 /*
1312 * Loop through all the applicable validation sets
1313 * for each document
1314 */
1315 FOR j in l_val_sets_tab.FIRST .. l_val_sets_tab.LAST LOOP
1316
1317 print_debuginfo(l_module_name, 'Applicable validation set ('
1318 || l_val_sets_tab(j).val_assign_entity_type
1319 || ') : '
1320 || l_val_sets_tab(j).val_set_name
1321 );
1322
1323 /*
1324 * Dynamically call the corresponding validation code
1325 * entry point.
1326 */
1327 l_stmt := 'CALL '
1328 || l_val_sets_tab(j).val_code_pkg
1329 || '.'
1330 || l_val_sets_tab(j).val_code_entry_point
1331 || '(:1,:2,:3,:4,:5)';
1332
1333 print_debuginfo(l_module_name, 'Executing ' || l_stmt);
1334
1335 EXECUTE IMMEDIATE (l_stmt) USING
1336 IN l_val_sets_tab(j).val_assign_id,
1337 IN l_val_sets_tab(j).val_set_code,
1338 IN l_val_sets_tab(j).doc_id,
1339 IN 'N',
1340 OUT l_result;
1341
1342 print_debuginfo(l_module_name, 'Finished executing '
1343 || l_stmt);
1344
1345 print_debuginfo(l_module_name, 'Result: '
1346 || l_result);
1347
1348 IF (l_result <> 0) THEN
1349 /*
1350 * If document has failed validation, add it to
1351 * list of invalid documents.
1352 */
1353
1354 l_already_failed_flag :=
1355 checkIfDocFailed(l_val_sets_tab(j).doc_id,
1356 l_invalid_docs_tab);
1357
1358 /*
1359 * Add this doc to the list of invalid documents
1360 * only if it is not already present in the
1361 * invalid docs list.
1362 */
1363 IF (l_already_failed_flag = FALSE) THEN
1364
1365 l_invalid_doc_rec.doc_id := l_val_sets_tab(j).doc_id;
1366 l_invalid_doc_rec.pmt_grp_num :=
1367 l_val_sets_tab(j).pmt_grp_num;
1368 l_invalid_doc_rec.payee_id :=
1369 l_val_sets_tab(j).payee_id;
1370 l_invalid_doc_rec.doc_status := DOC_STATUS_REJECTED;
1371
1372 l_invalid_docs_tab(l_invalid_docs_tab.COUNT + 1) :=
1373 l_invalid_doc_rec;
1374
1375 l_doc_failed_flag := TRUE;
1376
1377 /*
1378 * Validations sets handle the error messages
1379 * themselves. Therefore, no need to populate
1380 * the error record here.
1381 */
1382
1383 END IF;
1384
1385 ELSE
1386 l_doc_failed_flag := FALSE;
1387
1388 END IF; -- if result <> 0
1389
1390 print_debuginfo(l_module_name, '+-------------------------+');
1391
1392 END LOOP; -- for each validation set
1393
1394 END IF; -- if count of val sets <> 0
1395
1396 /*
1397 * At least one document in the payment request has
1398 * been validated successfully. Keep track of this
1399 * fact; it will be used in updating the status
1400 * of the payment request.
1401 */
1402 IF (l_doc_failed_flag = FALSE) THEN
1403 l_all_docs_failed_flag := FALSE;
1404 END IF;
1405
1406 END LOOP; -- for each document
1407
1408 /*
1409 * Documents within a payment request may be related to each
1410 * other by the 'payment grouping number'. If any document is
1411 * failed, then all it's related documents must also be failed.
1412 *
1413 * If any documents have been failed due to validation
1414 * then make sure to fail it's sister docs.
1415 */
1416 IF (l_invalid_docs_tab.COUNT > 0) THEN
1417 failRelatedDocs(l_docs_tab, l_invalid_docs_tab, l_doc_error_tab,
1418 l_doc_token_tab);
1419 END IF;
1420
1421 /*
1422 * Check if at least one document has failed. This
1423 * information is used when updating the payment
1424 * request status.
1425 */
1426 IF (l_invalid_docs_tab.COUNT > 0) THEN
1427 l_all_docs_success_flag := FALSE;
1428 ELSE
1429 l_all_docs_success_flag := TRUE;
1430 END IF;
1431
1432 /*
1433 * Get the rejection level system option
1434 */
1435 IF (p_doc_rejection_level IS NOT NULL) THEN
1436
1437 /*
1438 * Use the document rejection level passed in
1439 * with the payment service request (if available).
1440 */
1441 l_rejection_level := p_doc_rejection_level;
1442
1443 ELSE
1444
1445 /*
1446 * If the document rejection level is not passed
1447 * in with the payment service request, derive
1448 * the document rejection level setting at the
1449 * enterprise level.
1450 */
1451 l_rejection_level := getDocRejLevelSysOption();
1452
1453 END IF;
1454
1455 print_debuginfo(l_module_name, 'Rejection level system option: '
1456 || l_rejection_level);
1457
1458 /*
1459 * If the rejection level is 'payee', then fail all
1460 * documents for this payee even if a single document
1461 * for this payee has failed.
1462 */
1463 IF (l_all_docs_success_flag = FALSE) THEN
1464
1465 IF (l_rejection_level = REJ_LVL_PAYEE) THEN
1466
1467 failAllDocsForPayee(l_docs_tab, l_invalid_docs_tab,
1468 l_doc_error_tab, l_doc_token_tab);
1469
1470 /*
1471 * Since docs related by payee id have been failed,
1472 * it is possible that some new payment grouping
1473 * no's have been failed as part of this process.
1474 *
1475 * Therefore, again scan all documents and fail
1476 * those that are related by orig doc id.
1477 */
1478 IF (l_invalid_docs_tab.COUNT > 0) THEN
1479 failRelatedDocs(l_docs_tab, l_invalid_docs_tab,
1480 l_doc_error_tab, l_doc_token_tab);
1481 END IF;
1482
1483 END IF;
1484
1485 END IF;
1486
1487 /*
1488 * All docs of payment request may have been failed because of
1489 * cascaded failures.
1490 *
1491 * Compare the docs in the payment request with the docs in the
1492 * error docs list to see if all have failed or not.
1493 */
1494 l_all_docs_failed_flag := checkIfAllDocsFailed(l_docs_tab,
1495 l_invalid_docs_tab);
1496
1497 /*
1498 * Update the status of the documents and the payment
1499 * request.
1500 */
1501 performDBUpdates(
1502 p_pay_service_request_id,
1503 l_docs_tab,
1504 l_invalid_docs_tab,
1505 l_all_docs_success_flag,
1506 l_all_docs_failed_flag,
1507 l_rejection_level,
1508 l_doc_error_tab,
1509 l_doc_token_tab,
1510 x_return_status
1511 );
1512
1513 /*
1514 * Get attributes from the payment request like
1515 * calling app payred id, calling app id etc.
1516 * These are required to raise business events.
1517 */
1518 getRequestAttributes(p_pay_service_request_id, req_ca_payreq_cd,
1519 req_ca_id);
1520
1521 /*
1522 * Finally, raise business events to inform the calling app
1523 * if any documents have failed.
1524 *
1525 * Note: this should be the last call after database records
1526 * have been inserted / updated. This is because you cannot
1527 * 'rollback' a business event once raised.
1528 */
1529 IF (p_is_singpay_flag = FALSE) THEN
1530
1531 raiseBizEvents(p_pay_service_request_id, req_ca_payreq_cd, req_ca_id,
1532 l_all_docs_success_flag, l_rejection_level);
1533
1534 ELSE
1535
1536 print_debuginfo(l_module_name, 'Not invoking raiseBizEvents() '
1537 || 'because the request '
1538 || p_pay_service_request_id
1539 || ' is a single payment.'
1540 );
1541
1542 END IF;
1543
1544 print_debuginfo(l_module_name, 'Completed validations for payment '
1545 || 'request :'
1546 || p_pay_service_request_id);
1547
1548 print_debuginfo(l_module_name, 'EXIT');
1549
1550 EXCEPTION
1551 WHEN OTHERS THEN
1552 FND_MESSAGE.SET_NAME('IBY', '');
1553 FND_MESSAGE.SET_TOKEN('SQLERR','applyDocumentValidationSets: '
1554 || substr(SQLERRM, 1, 300));
1555 FND_MSG_PUB.Add;
1556 print_debuginfo(l_module_name,substr(SQLERRM, 1, 300),
1557 FND_LOG.LEVEL_UNEXPECTED);
1558 RAISE g_abort_program;
1559
1560 END applyDocumentValidationSets;
1561
1562 /*--------------------------------------------------------------------
1563 | NAME:
1564 | performOnlineValidations
1565 |
1566 | PURPOSE:
1567 | Perform online/immediate validations. Immediate validations
1568 | invoke the same validation sets as deferred validations, but
1569 | errors are returned as warnings and status of the documents
1570 | are not affected.
1571 |
1572 | Online validations are performed on documents individually.
1573 | At the time when online validations are invoked the
1574 | payment request is not yet formed in the calling app.
1575 |
1576 | So this method is invoked for one document at a time by the
1577 | calling app.
1578 |
1579 | PARAMETERS:
1580 | IN
1581 | p_document_id
1582 | the id of the given document.
1583 |
1584 | OUT
1585 | x_return_status
1586 | -1 indicates that the given document has failed
1587 | at least one validation.
1588 | 0 indicates that the given document has passed
1589 | all validations.
1590 |
1591 | RETURNS:
1592 |
1593 | NOTES:
1594 |
1595 *---------------------------------------------------------------------*/
1596 PROCEDURE performOnlineValidations(
1597 p_document_id IN IBY_DOCS_PAYABLE_ALL.document_payable_id%type,
1598 x_return_status IN OUT NOCOPY NUMBER)
1599 IS
1600 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
1601 '.performOnlineValidations';
1602 l_stmt VARCHAR2(200);
1603 l_result NUMBER := 0;
1604 l_validation_failed BOOLEAN := FALSE;
1605
1606 /*
1607 * Pick up validation sets based on method, internal bank
1608 * account and format attributes on the given document.
1609 *
1610 * At the time of online validations, the payment profile
1611 * is not expected to be available. This means that we
1612 * cannot perform online validations based on bank or
1613 * transmission protocol (since these are derived from
1614 * the payment profile).
1615 */
1616
1617 /*
1618 * Fix for bug 5548886:
1619 *
1620 * Add index hint to improve performance.
1621 */
1622 CURSOR
1623 c_val_sets(p_document_payable_id VARCHAR2)
1624 IS
1625 SELECT /*+ INDEX(docs IBY_DOCS_PAYABLE_GT_N1) NO_EXPAND */
1626 docs.document_payable_id,
1627 val.validation_set_code,
1628 val.validation_code_package,
1629 val.validation_code_entry_point,
1630 val_options.validation_assignment_id,
1631 val_options.val_assignment_entity_type,
1632 val.validation_set_display_name
1633 FROM
1634 IBY_VALIDATION_SETS_VL val,
1635 IBY_VAL_ASSIGNMENTS val_options,
1636 IBY_DOCS_PAYABLE_GT docs
1637 WHERE
1638 docs.document_payable_id = p_document_payable_id
1639 AND
1640 val.validation_set_code = val_options.validation_set_code
1641 AND
1642 val.validation_level_code = 'DOCUMENT'
1643 AND (val_options.val_assignment_entity_type = 'METHOD'
1644 AND val_options.assignment_entity_id = docs.payment_method_code
1645 OR val_options.val_assignment_entity_type = 'INTBANKACCOUNT'
1646 AND val_options.assignment_entity_id = docs.internal_bank_account_id
1647 OR val_options.val_assignment_entity_type = 'FORMAT'
1648 AND val_options.assignment_entity_id = docs.payment_format_code
1649 )
1650 AND NVL(val_options.inactive_date, sysdate+1) >= sysdate
1651 ;
1652
1653 BEGIN
1654
1655 print_debuginfo(l_module_name, 'ENTER');
1656
1657 print_debuginfo(l_module_name, 'Document id: '
1658 || p_document_id);
1659
1660 /*
1661 * Loop through all applicable validation sets for this
1662 * document.
1663 *
1664 * Validation sets will be picked up based on provided
1665 * document attributes. Almost all the document attributes
1666 * are optional at the time of online validations, so
1667 * it is possible that few/none of the validation sets
1668 * are picked up.
1669 */
1670 FOR valset_rec in c_val_sets(p_document_id) LOOP
1671
1672 print_debuginfo(l_module_name, 'Applicable validation set ('
1673 || valset_rec.val_assignment_entity_type
1674 || ') : '
1675 || valset_rec.validation_set_display_name
1676 );
1677
1678 /*
1679 * Dynamically execute the validation
1680 */
1681 l_stmt := 'CALL '
1682 || valset_rec.validation_code_package
1683 || '.'
1684 || valset_rec.validation_code_entry_point
1685 || '(:1,:2,:3,:4,:5)';
1686
1687 print_debuginfo(l_module_name, 'Executing '|| l_stmt);
1688
1689 EXECUTE IMMEDIATE (l_stmt) USING
1690 IN valset_rec.validation_assignment_id,
1691 IN valset_rec.validation_set_code,
1692 IN valset_rec.document_payable_id,
1693 IN 'Y',
1694 OUT l_result;
1695
1696 print_debuginfo(l_module_name, 'result: '|| l_result);
1697
1698 IF (l_result <> 0) THEN
1699
1700 /*
1701 * If document has failed validation, set the
1702 * validation failed flag. This will be used
1703 * to determine the return status.
1704 */
1705 l_validation_failed := TRUE;
1706
1707 END IF;
1708
1709 END LOOP;
1710
1711 /*
1712 * If even a single validation has failed for this
1713 * document , set the return status to indicate
1714 * validation failure.
1715 */
1716 IF (l_validation_failed = TRUE) THEN
1717 x_return_status := -1;
1718 ELSE
1719 x_return_status := 0;
1720 END IF;
1721
1722 print_debuginfo(l_module_name, 'Return status before exiting: '
1723 || x_return_status
1724 );
1725
1726 print_debuginfo(l_module_name, 'EXIT');
1727
1728 EXCEPTION
1729 WHEN OTHERS THEN
1730 print_debuginfo(l_module_name, 'Exception occured when attempting '
1731 || 'online validation.'
1732 );
1733 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
1734 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
1735
1736 /*
1737 * Since online validations are really validation warnings,
1738 * set the return status to -1 and exit gracefully.
1739 */
1740 x_return_status := -1;
1741
1742 print_debuginfo(l_module_name, 'Return status before exiting: '
1743 || x_return_status
1744 );
1745
1746 print_debuginfo(l_module_name, 'EXIT');
1747
1748 END performOnlineValidations;
1749
1750 /*--------------------------------------------------------------------
1751 | NAME:
1752 | initDocumentData
1753 |
1754 | PURPOSE:
1755 | Initializes the document record from Oracle Payment's tables.
1756 | All the related fields of a document are picked up including
1757 | Payer/Payee/Payer Bank/Payee Bank.
1758 |
1759 | These fields are populated into the document record and passed
1760 | to the caller (validation sets) as an output parameter.
1761 |
1762 | PARAMETERS:
1763 | IN
1764 |
1765 |
1766 | OUT
1767 |
1768 |
1769 | RETURNS:
1770 |
1771 | NOTES:
1772 |
1773 *---------------------------------------------------------------------*/
1774 PROCEDURE initDocumentData(
1775 p_document_id IN IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE,
1776 x_document_rec IN OUT NOCOPY documentRecType,
1777 p_isOnline IN VARCHAR2
1778 )
1779 IS
1780
1781 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.initDocumentData';
1782
1783 /*
1784 * Cursor for deferred validation.
1785 *
1786 * Pick up all the document fields which need to be validated.
1787 *
1788 * Note: If you modify the fields in the query below, please
1789 * modify the fields in the 'documentRecType' record in the spec
1790 * as well.
1791 */
1792 CURSOR c_documentInfo (p_doc_id
1793 IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE)
1794 RETURN documentRecType
1795 IS
1796 SELECT
1797 docs.calling_app_id calling_app_id,
1798 docs.calling_app_doc_unique_ref1 calling_app_doc_id1,
1799 docs.calling_app_doc_unique_ref2 calling_app_doc_id2,
1800 docs.calling_app_doc_unique_ref3 calling_app_doc_id3,
1801 docs.calling_app_doc_unique_ref4 calling_app_doc_id4,
1802 docs.calling_app_doc_unique_ref5 calling_app_doc_id5,
1803 docs.pay_proc_trxn_type_code pay_proc_trxn_type_cd,
1804 docs.document_payable_id document_id,
1805 docs.payment_amount document_amount,
1806 docs.payment_currency_code document_pay_currency,
1807 docs.exclusive_payment_flag exclusive_payment_flag,
1808 docs.delivery_channel_code delivery_channel_code,
1809 docs.unique_remittance_identifier unique_remit_id_code,
1810 docs.payment_reason_comments payment_reason_comments,
1811 docs.settlement_priority settlement_priority,
1812 docs.remittance_message1 remittance_message1,
1813 docs.remittance_message2 remittance_message2,
1814 docs.remittance_message3 remittance_message3,
1815 docs.uri_check_digit uri_check_digit,
1816
1817
1818 iba_bnk_branch.bank_number int_bank_num,
1819 iba_bnk_branch.bank_name int_bank_name,
1820 iba_bnk_branch.bank_name_alt int_bank_name_alt,
1821 iba_bnk_branch.branch_number int_bank_branch_num,
1822 iba_bnk_branch.bank_branch_name int_bank_branch_name,
1823 iba_bnk_branch.bank_branch_name_alt int_bank_branch_name_alt,
1824
1825 iba.bank_account_num int_bank_acc_num,
1826 iba.bank_account_name int_bank_acc_name,
1827 iba.bank_account_name_alt int_bank_acc_name_alt,
1828 iba.bank_account_type int_bank_acc_type,
1829 iba.iban_number int_bank_acc_iban,
1830 '' int_bank_assigned_id1,
1831 '' int_bank_assigned_id2,
1832 iba.eft_user_num int_eft_user_number,
1833 iba.check_digits int_bank_acc_chk_dgts,
1834 iba.eft_requester_identifier int_eft_req_identifier,
1835 iba.short_account_name int_bank_acc_short_name,
1836 iba.account_holder_name int_bank_acc_holder_name,
1837 iba.account_holder_name_alt int_bank_acc_holder_name_alt,
1838
1839 payer.party_legal_name payer_le_name,
1840 payer.party_address_country payer_le_country,
1841 payer.party_phone payer_phone,
1842
1843 eba.bank_number ext_bank_num,
1844 eba.bank_name ext_bank_name,
1845 '' ext_bank_name_alt,
1846 eba.branch_number ext_bank_branch_num,
1847 eba.bank_branch_name ext_bank_branch_name,
1848 eba_bank_branch.bank_branch_name_alt ext_bank_branch_name_alt,
1849 eba.country_code ext_bank_country,
1850 eba_bank_branch.address_line1 ext_bank_branch_addr1,
1851 nvl(eba_bank_branch.country,eba_bank_branch.bank_home_country) ext_bank_branch_country,
1852
1853 eba.bank_account_number ext_bank_acc_num,
1854 eba.bank_account_name ext_bank_acc_name,
1855 eba.alternate_account_name ext_bank_acc_name_alt,
1856 eba.bank_account_type ext_bank_acc_type,
1857 eba.iban_number ext_bank_acc_iban,
1858 eba.check_digits ext_bank_acc_chk_dgts,
1859 eba.short_acct_name ext_bank_acc_short_name,
1860 eba.primary_acct_owner_name ext_bank_acc_holder_name,
1861 '' ext_bank_acc_holder_name_alt,
1862 eba.eft_swift_code ext_bank_acc_BIC, -- The documentRecType in the ibyvalls.pls was modified
1863
1864 payee.party_name payee_party_name,
1865 payee_addr.add_line1 payee_party_addr1,
1866 payee_addr.add_line2 payee_party_addr2,
1867 payee_addr.add_line3 payee_party_addr3,
1868 payee_addr.city payee_party_city,
1869 payee_addr.state payee_party_state,
1870 payee_addr.province payee_party_province,
1871 payee_addr.county payee_party_county,
1872 payee_addr.postal_code payee_party_postal,
1873 payee_addr.country payee_party_country,
1874
1875 docs.bank_charge_bearer bank_charge_bearer,
1876 docs.payment_reason_code payment_reason_code,
1877 docs.payment_method_code payment_method_cd,
1878 docs.payment_format_code payee_payment_format_cd
1879 FROM
1880 IBY_DOCS_PAYABLE_ALL docs,
1881 IBY_PP_FIRST_PARTY_V payer,
1882 HZ_PARTIES payee,
1883 CE_BANK_ACCOUNTS iba,
1884 CE_BANK_BRANCHES_V iba_bnk_branch,
1885 IBY_EXT_BANK_ACCOUNTS_INT_V eba,
1886 CE_BANK_BRANCHES_V eba_bank_branch,
1887
1888 /*
1889 * Fix for bug 5997016:
1890 *
1891 * The payee address cannot be always assumed to be stored in
1892 * HZ_LOCATIONS table (TCA).
1893 *
1894 * For employee type payees, the address is stored in
1895 * per_addresses (HR).
1896 *
1897 * The 'address source' column on the document payable identifies
1898 * the source of the address information -
1899 * TCA = address is stored in HZ_LOCATIONS
1900 * HR = address is stored in PER_ADDRESSES
1901 *
1902 * Therefore, we need to dynamically pick up the payee address
1903 * fields from the correct table. The SELECT statement below is
1904 * used to dynamically form the address table based on the
1905 * address source. This dynamic table is aliased as payee_addr.
1906 *
1907 * There is a dynamic address tabled formed in a similar manner
1908 * during the payment creation process as well [see method
1909 * IBY_PAYGROUP_PUB.auditPaymentData(..)].
1910 */
1911 (
1912 SELECT
1913
1914 /* payee add line1 */
1915 DECODE(
1916 doc.address_source,
1917
1918 -- supplier address line 1
1919 'TCA', payee_loc.address1,
1920
1921 -- employee add line 1
1922 DECODE
1923 (
1924 doc.employee_address_code,
1925
1926 -- employee home addr line 1
1927 'HOME', per_addr.address_line1,
1928
1929 -- employee office addr line 1
1930 'OFFICE',per_loc.address_line_1,
1931
1932 --address code not specified
1933 DECODE (per_addr.address_id,
1934 NULL, per_loc.address_line_1,
1935 per_addr.address_line1)
1936 )
1937
1938 ) add_line1,
1939
1940 /* payee add line2 */
1941 DECODE(
1942 doc.address_source,
1943
1944 -- supplier address line 2
1945 'TCA', payee_loc.address2,
1946
1947 -- employee add line 2
1948 DECODE
1949 (
1950 doc.employee_address_code,
1951
1952 -- employee home addr line 2
1953 'HOME', per_addr.address_line2,
1954
1955 -- employee office addr line 2
1956 'OFFICE',per_loc.address_line_2,
1957
1958 --address code not specified
1959 DECODE (per_addr.address_id,
1960 NULL, per_loc.address_line_2,
1961 per_addr.address_line2)
1962 )
1963 ) add_line2,
1964
1965
1966 /* payee add line3 */
1967 DECODE(
1968 doc.address_source,
1969
1970 -- supplier address line 3
1971 'TCA', payee_loc.address3,
1972
1973 -- employee add line 3
1974 DECODE
1975 (
1976 doc.employee_address_code,
1977
1978 -- employee home addr line 3
1979 'HOME', per_addr.address_line3,
1980
1981 -- employee office addr line 3
1982 'OFFICE',per_loc.address_line_3,
1983
1984 --address code not specified
1985 DECODE (per_addr.address_id,
1986 NULL, per_loc.address_line_3,
1987 per_addr.address_line3)
1988 )
1989
1990 ) add_line3,
1991
1992
1993 /* payee add line4 */
1994 DECODE(
1995 doc.address_source,
1996
1997 -- supplier address line 4
1998 'TCA', payee_loc.address4,
1999
2000 -- employee home/office addr line 4 (not available)
2001 null
2002
2003 ) add_line4,
2004
2005 /* payee city */
2006 DECODE(
2007 doc.address_source,
2008
2009 -- supplier city
2010 'TCA', payee_loc.city,
2011
2012 -- employee city
2013 DECODE
2014 (
2015 doc.employee_address_code,
2016
2017 -- employee home city
2018 'HOME', per_addr.town_or_city,
2019
2020 -- employee office city
2021 'OFFICE', per_loc.town_or_city,
2022
2023 -- address code not specified
2024 DECODE (per_addr.address_id,
2025 NULL, per_loc.town_or_city,
2026 per_addr.town_or_city)
2027 )
2028
2029 ) city,
2030
2031
2032 /* payee county */
2033 DECODE(
2034 doc.address_source,
2035
2036 -- supplier county
2037 'TCA', payee_loc.county,
2038
2039 -- employee county
2040 (
2041 DECODE(
2042 doc.employee_address_code,
2043
2044 -- employee home county
2045 'HOME',
2046 DECODE(
2047 per_addr.style,
2048 'US', NVL(per_addr.region_1, ''),
2049 'US_GLB', NVL(per_addr.region_1, ''),
2050 'IE', NVL(ap_web_db_expline_pkg.
2051 getcountyprovince(
2052 per_addr.style,
2053 per_addr.region_1),
2054 ''),
2055 'IE_GLB', NVL(ap_web_db_expline_pkg.
2056 getcountyprovince(
2057 per_addr.style,
2058 per_addr.region_1),
2059 ''),
2060 'GB', NVL(ap_web_db_expline_pkg.
2061 getcountyprovince(
2062 per_addr.style,
2063 per_addr.region_1),
2064 ''),
2065 ''),
2066
2067 -- employee office county
2068 'OFFICE',
2069 DECODE(
2070 per_loc.style,
2071 'US', NVL(per_loc.region_1, ''),
2072 'US_GLB', NVL(per_loc.region_1, ''),
2073 'IE', NVL(ap_web_db_expline_pkg.
2074 getcountyprovince(
2075 per_loc.style,
2076 per_loc.region_1),
2077 ''),
2078 'IE_GLB', NVL(ap_web_db_expline_pkg.
2079 getcountyprovince(
2080 per_loc.style,
2081 per_loc.region_1),
2082 ''),
2083 'GB', NVL(ap_web_db_expline_pkg.
2084 getcountyprovince(
2085 per_loc.style,
2086 per_loc.region_1),
2087 ''),
2088 ''),
2089
2090
2091 --address code not specified
2092 decode(per_addr.address_id,
2093 NULL,DECODE(
2094 per_loc.style,
2095 'US', NVL(per_loc.region_1, ''),
2096 'US_GLB', NVL(per_loc.region_1, ''),
2097 'IE', NVL(ap_web_db_expline_pkg.
2098 getcountyprovince(
2099 per_loc.style,
2100 per_loc.region_1),
2101 ''),
2102 'IE_GLB', NVL(ap_web_db_expline_pkg.
2103 getcountyprovince(
2104 per_loc.style,
2105 per_loc.region_1),
2106 ''),
2107 'GB', NVL(ap_web_db_expline_pkg.
2108 getcountyprovince(
2109 per_loc.style,
2110 per_loc.region_1),
2111 ''),
2112 ''),
2113 DECODE(
2114 per_addr.style,
2115 'US', NVL(per_addr.region_1, ''),
2116 'US_GLB', NVL(per_addr.region_1, ''),
2117 'IE', NVL(ap_web_db_expline_pkg.
2118 getcountyprovince(
2119 per_addr.style,
2120 per_addr.region_1),
2121 ''),
2122 'IE_GLB', NVL(ap_web_db_expline_pkg.
2123 getcountyprovince(
2124 per_addr.style,
2125 per_addr.region_1),
2126 ''),
2127 'GB', NVL(ap_web_db_expline_pkg.
2128 getcountyprovince(
2129 per_addr.style,
2130 per_addr.region_1),
2131 ''),
2132 ''))
2133 )
2134 )
2135 ) county,
2136
2137 /* payee province */
2138 DECODE(
2139 doc.address_source,
2140
2141 -- supplier province
2142 'TCA', payee_loc.province,
2143
2144 -- employee province
2145 (
2146 DECODE(
2147
2148 doc.employee_address_code,
2149
2150 -- employee home province
2151 'HOME',
2152 DECODE(per_addr.style,
2153 'US', '',
2154 'US_GLB', '',
2155 'IE', '',
2156 'IE_GLB', '',
2157 'GB', '',
2158 'CA', NVL(per_addr.region_1, ''),
2159 'CA_GLB', NVL(per_addr.region_1, ''),
2160 'JP', NVL(per_addr.region_1, ''),
2161 NVL(ap_web_db_expline_pkg.
2162 getcountyprovince(
2163 per_addr.style,
2164 per_addr.region_1),
2165 '')
2166 ),
2167
2168 -- employee office province
2169 'OFFICE',
2170 DECODE(per_loc.style,
2171 'US', '',
2172 'US_GLB', '',
2173 'IE', '',
2174 'IE_GLB', '',
2175 'GB', '',
2176 'CA', NVL(per_loc.region_1, ''),
2177 'CA_GLB', NVL(per_loc.region_1, ''),
2178 'JP', NVL(per_loc.region_1, ''),
2179 NVL(ap_web_db_expline_pkg.
2180 getcountyprovince(
2181 per_loc.style,
2182 per_loc.region_1),
2183 '')
2184 ),
2185
2186 --address code not specified
2187 decode(per_addr.address_id,
2188 NULL,DECODE(per_loc.style,
2189 'US', '',
2190 'US_GLB', '',
2191 'IE', '',
2192 'IE_GLB', '',
2193 'GB', '',
2194 'CA', NVL(per_loc.region_1, ''),
2195 'CA_GLB', NVL(per_loc.region_1, ''),
2196 'JP', NVL(per_loc.region_1, ''),
2197 NVL(ap_web_db_expline_pkg.
2198 getcountyprovince(
2199 per_loc.style,
2200 per_loc.region_1),
2201 '')
2202 ),
2203 DECODE(per_addr.style,
2204 'US', '',
2205 'US_GLB', '',
2206 'IE', '',
2207 'IE_GLB', '',
2208 'GB', '',
2209 'CA', NVL(per_addr.region_1, ''),
2210 'CA_GLB', NVL(per_addr.region_1, ''),
2211 'JP', NVL(per_addr.region_1, ''),
2212 NVL(ap_web_db_expline_pkg.
2213 getcountyprovince(
2214 per_addr.style,
2215 per_addr.region_1),
2216 '')
2217 ))
2218 )
2219 )
2220 ) province,
2221
2222 /* payee state */
2223 DECODE(
2224 doc.address_source,
2225
2226 -- supplier state
2227 'TCA', payee_loc.state,
2228
2229 -- employee state
2230 (
2231 DECODE(
2232 doc.employee_address_code,
2233
2234 -- employee home state
2235 'HOME',
2236 DECODE(per_addr.style,
2237 'CA', '',
2238 'CA_GLB', '',
2239 NVL(per_addr.region_2, '')),
2240
2241 -- employee office state
2242 'OFFICE',
2243 DECODE(per_loc.style,
2244 'CA', '',
2245 'CA_GLB', '',
2246 NVL(per_loc.region_2, '')),
2247
2248 --address code not specified
2249 decode(per_addr.address_id,
2250 NULL,DECODE(per_loc.style,
2251 'CA', '',
2252 'CA_GLB', '',
2253 NVL(per_loc.region_2, '')),
2254 DECODE(per_addr.style,
2255 'CA', '',
2256 'CA_GLB', '',
2257 NVL(per_addr.region_2, '')))
2258 )
2259 )
2260 ) state,
2261
2262 /* payee country */
2263 DECODE(
2264 doc.address_source,
2265
2266 -- supplier country
2267 'TCA', payee_loc.country,
2268
2269 -- employee country
2270 (
2271 DECODE(
2272 doc.employee_address_code,
2273
2274 -- employee home country
2275 'HOME', per_addr.country,
2276
2277 -- employee office country
2278 'OFFICE',per_loc.country,
2279
2280 --address code not specified
2281 DECODE (per_addr.address_id,
2282 NULL, per_loc.country,
2283 per_addr.country
2284 )
2285 )
2286 )
2287 ) country,
2288
2289 /* payee postal code */
2290 DECODE(
2291 doc.address_source,
2292
2293 -- supplier postal code
2294 'TCA', payee_loc.postal_code,
2295
2296 -- employee postal code
2297 (
2298 DECODE(
2299 doc.employee_address_code,
2300
2301 -- employee home postal code
2302 'HOME', per_addr.postal_code,
2303
2304 -- employee office postal code
2305 'OFFICE',per_loc.postal_code,
2306
2307 --address code not specified
2308 DECODE (per_addr.address_id,
2309 NULL, per_loc.postal_code,
2310 per_addr.postal_code
2311 )
2312 )
2313 )
2314 ) postal_code,
2315
2316
2317 /* payee address concat */
2318 DECODE(
2319 doc.address_source,
2320
2321 -- supplier address concat
2322 'TCA',
2323 payee_loc.address1
2324 || ', '
2325 || payee_loc.address2
2326 || ', '
2327 || payee_loc.address3
2328 || ', '
2329 || payee_loc.city
2330 || ', '
2331 || payee_loc.state
2332 || ', '
2333 || payee_loc.country
2334 || ', '
2335 || payee_loc.postal_code,
2336
2337 -- employee address concat
2338 (
2339 DECODE(
2340 doc.employee_address_code,
2341
2342 -- employee home address concat
2343 'HOME',
2344 per_addr.address_line1
2345 || ', '
2346 || per_addr.address_line2
2347 || ', '
2348 || per_addr.address_line3
2349 || ', '
2350 || per_addr.town_or_city
2351 || ', '
2352 || DECODE(
2353 per_addr.style,
2354 'CA', '',
2355 'CA_GLB', '',
2356 NVL(per_addr.region_2, '')
2357 )
2358 || ', '
2359 || per_addr.country
2360 || ', '
2361 || per_addr.postal_code,
2362
2363 -- employee office address concat
2364 'OFFICE',
2365 per_loc.address_line_1
2366 || ', '
2367 || per_loc.address_line_2
2368 || ', '
2369 || per_loc.address_line_3
2370 || ', '
2371 || per_loc.town_or_city
2372 || ', '
2373 || DECODE(
2374 per_loc.style,
2375 'CA', '',
2376 'CA_GLB', '',
2377 NVL(per_loc.region_2, '')
2378 )
2379 || ', '
2380 || per_loc.country
2381 || ', '
2382 || per_loc.postal_code,
2383
2384 -- address code not specified
2385 DECODE (per_addr.address_id,
2386 NULL, per_loc.address_line_1
2387 || ', '
2388 || per_loc.address_line_2
2389 || ', '
2390 || per_loc.address_line_3
2391 || ', '
2392 || per_loc.town_or_city
2393 || ', '
2394 || DECODE(
2395 per_loc.style,
2396 'CA', '',
2397 'CA_GLB', '',
2398 NVL(per_loc.region_2, '')
2399 )
2400 || ', '
2401 || per_loc.country
2402 || ', '
2403 || per_loc.postal_code,
2404
2405 per_addr.address_line1
2406 || ', '
2407 || per_addr.address_line2
2408 || ', '
2409 || per_addr.address_line3
2410 || ', '
2411 || per_addr.town_or_city
2412 || ', '
2413 || DECODE(
2414 per_addr.style,
2415 'CA', '',
2416 'CA_GLB', '',
2417 NVL(per_addr.region_2, '')
2418 )
2419 || ', '
2420 || per_addr.country
2421 || ', '
2422 || per_addr.postal_code)
2423 )
2424 )
2425 ) add_concat
2426
2427 FROM
2428 IBY_DOCS_PAYABLE_ALL doc,
2429
2430 /* Employee address related */
2431 HR_LOCATIONS per_loc,
2432 PER_ADDRESSES per_addr,
2433 PER_ALL_ASSIGNMENTS_F per_assgn,
2434
2435 /* Supplier address related */
2436 HZ_LOCATIONS payee_loc
2437 WHERE
2438 doc.document_payable_id = p_doc_id
2439 AND doc.employee_person_id = per_addr.person_id(+)
2440 AND per_addr.primary_flag(+) = 'Y'
2441 AND SYSDATE BETWEEN
2442 per_addr.date_from(+)
2443 AND NVL(per_addr.date_to(+), SYSDATE+1)
2444 AND doc.employee_person_id = per_assgn.person_id(+)
2445 AND per_assgn.location_id = per_loc.location_id(+)
2446 AND per_assgn.primary_flag(+) = 'Y'
2447 AND (TRUNC(SYSDATE) BETWEEN
2448 per_assgn.effective_start_date(+)
2449 AND per_assgn.effective_end_date(+)
2450 )
2451 AND doc.remit_to_location_id = payee_loc.location_id(+)
2452
2453 ) payee_addr
2454
2455 WHERE
2456 docs.document_payable_id = p_doc_id
2457 AND docs.legal_entity_id = payer.party_legal_id
2458 AND docs.payee_party_id = payee.party_id
2459 AND docs.internal_bank_account_id = iba.bank_account_id
2460 AND iba_bnk_branch.branch_party_id = iba.bank_branch_id
2461 AND docs.external_bank_account_id = eba.ext_bank_account_id(+)
2462 AND eba.bank_party_id = eba_bank_branch.bank_party_id(+)
2463 AND eba.branch_party_id = eba_bank_branch.branch_party_id(+)
2464 ;
2465
2466 /*
2467 * The cursor for online validation (i.e, immediate validation)
2468 * is similar to the cursor for deferred validation.
2469 * However, only document id is guaranteed to be available.
2470 * Other fields like payer id, payee id, internal bank account id,
2471 * external bank acount id etc. may or may not be provided.
2472 * Therefore, perform an outer join with all entities other than
2473 * the IBY_DOCS_PAYABLE_GT.
2474 */
2475 CURSOR c_onlineDocumentInfo(p_doc_id
2476 IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE)
2477 RETURN documentRecType
2478 IS
2479 SELECT
2480 docs.calling_app_id calling_app_id,
2481 docs.calling_app_doc_unique_ref1 calling_app_doc_id1,
2482 docs.calling_app_doc_unique_ref2 calling_app_doc_id2,
2483 docs.calling_app_doc_unique_ref3 calling_app_doc_id3,
2484 docs.calling_app_doc_unique_ref4 calling_app_doc_id4,
2485 docs.calling_app_doc_unique_ref5 calling_app_doc_id5,
2486 docs.pay_proc_trxn_type_code pay_proc_trxn_type_cd,
2487 docs.document_payable_id document_id,
2488 docs.payment_amount document_amount,
2489 docs.payment_currency_code document_pay_currency,
2490 docs.exclusive_payment_flag exclusive_payment_flag,
2491 docs.delivery_channel_code delivery_channel_code,
2492 docs.unique_remittance_identifier unique_remit_id_code,
2493 docs.payment_reason_comments payment_reason_comments,
2494 docs.settlement_priority settlement_priority,
2495 docs.remittance_message1 remittance_message1,
2496 docs.remittance_message2 remittance_message2,
2497 docs.remittance_message3 remittance_message3,
2498 docs.uri_check_digit uri_check_digit,
2499
2500
2501 iba_bnk_branch.bank_number int_bank_num,
2502 iba_bnk_branch.bank_name int_bank_name,
2503 iba_bnk_branch.bank_name_alt int_bank_name_alt,
2504 iba_bnk_branch.branch_number int_bank_branch_num,
2505 iba_bnk_branch.bank_branch_name int_bank_branch_name,
2506 iba_bnk_branch.bank_branch_name_alt int_bank_branch_name_alt,
2507
2508 iba.bank_account_num int_bank_acc_num,
2509 iba.bank_account_name int_bank_acc_name,
2510 iba.bank_account_name_alt int_bank_acc_name_alt,
2511 iba.bank_account_type int_bank_acc_type,
2512 iba.iban_number int_bank_acc_iban,
2513 '' int_bank_assigned_id1,
2514 '' int_bank_assigned_id2,
2515 iba.eft_user_num int_eft_user_number,
2516 iba.check_digits int_bank_acc_chk_dgts,
2517 iba.eft_requester_identifier int_eft_req_identifier,
2518 iba.short_account_name int_bank_acc_short_name,
2519 iba.account_holder_name int_bank_acc_holder_name,
2520 iba.account_holder_name_alt int_bank_acc_holder_name_alt,
2521
2522 payer.party_legal_name payer_le_name,
2523 payer.party_address_country payer_le_country,
2524 payer.party_phone payer_phone,
2525
2526 eba.bank_number ext_bank_num,
2527 eba.bank_name ext_bank_name,
2528 '' ext_bank_name_alt,
2529 eba.branch_number ext_bank_branch_num,
2530 eba.bank_branch_name ext_bank_branch_name,
2531 eba_bank_branch.bank_branch_name_alt ext_bank_branch_name_alt,
2532 eba.country_code ext_bank_country,
2533 eba_bank_branch.address_line1 ext_bank_branch_addr1,
2534 nvl(eba_bank_branch.country,eba_bank_branch.bank_home_country) ext_bank_branch_country,
2535
2536 eba.bank_account_number ext_bank_acc_num,
2537 eba.bank_account_name ext_bank_acc_name,
2538 eba.alternate_account_name ext_bank_acc_name_alt,
2539 eba.bank_account_type ext_bank_acc_type,
2540 eba.iban_number ext_bank_acc_iban,
2541 eba.check_digits ext_bank_acc_chk_dgts,
2542 eba.short_acct_name ext_bank_acc_short_name,
2543 eba.primary_acct_owner_name ext_bank_acc_holder_name,
2544 '' ext_bank_acc_holder_name_alt,
2545 eba.eft_swift_code ext_eft_swift_code, -- The documentRecType in the ibyvalls.pls was modified
2546 payee.party_name payee_party_name,
2547
2548 /*
2549 * Note regarding bugfix for bug 5997016:
2550 *
2551 * Normally, this cursor c_onlineDocumentInfo, and the cursor
2552 * above c_documentInfo are in sync. This means that both
2553 * cursors pick up the same data except that the
2554 * online document cursor picks up the document attributes
2555 * from IBY_DOCS_PAYABLE_GT whereas the offline
2556 * document validation cursor picks up the document
2557 * attributes from IBY_DOCS_PAYABLE_ALL table.
2558 *
2559 * In fix for bug 5997016, we made the offline doc validation
2560 * cursor pick up the address data dynamically from
2561 * HR or TCA tables depending on the address source column.
2562 *
2563 * In the online validation cursor, we will not propagate
2564 * the same logic. There are some reasons for this -
2565 *
2566 * 1. Some columns that are present in IBY_DOCS_PAYABLE_ALL
2567 * table are not present in IBY_DOCS_PAYABLE_GT table.
2568 * E.g., address_source is not available in the GT table.
2569 * Therefore, to support the dynamic payee address
2570 * functionality we would need to make a data model change.
2571 *
2572 * 2. The online validation API is meant to provide a
2573 * a quick response to the user as the validation is
2574 * called syncronously by the user. By adding complex
2575 * joins, we will be adding a performance penalty to
2576 * online validation.
2577 *
2578 * 3. The intent of online validations is to catch basic
2579 * errors in the document. The payee address validation
2580 * on the document is a corner case, and it is not
2581 * necessary to do this as part of online validation.
2582 *
2583 * The offline validation / batch validation will catch
2584 * these errors. The online validation is meant to be
2585 * simple and quick that targets the basic errors on the
2586 * document.
2587 *
2588 * We will continue to pick up the payee address from
2589 * HZ_LOCATIONS. In the case of employee type payees
2590 * the payee address fields will be null. This is
2591 * fine. The offline validation will catch these
2592 * errors anyway.
2593 */
2594 payee_loc.address1 payee_party_addr1,
2595 payee_loc.address2 payee_party_addr2,
2596 payee_loc.address3 payee_party_addr3,
2597 payee_loc.city payee_party_city,
2598 payee_loc.state payee_party_state,
2599 payee_loc.province payee_party_province,
2600 payee_loc.county payee_party_county,
2601 payee_loc.postal_code payee_party_postal,
2602 payee_loc.country payee_party_country,
2603
2604 docs.bank_charge_bearer bank_charge_bearer,
2605 docs.payment_reason_code payment_reason_code,
2606 docs.payment_method_code payment_method_cd,
2607 docs.payment_format_code payee_payment_format_cd
2608
2609 FROM
2610 IBY_DOCS_PAYABLE_GT docs,
2611 IBY_PP_FIRST_PARTY_V payer,
2612 HZ_PARTIES payee,
2613 HZ_LOCATIONS payee_loc,
2614 CE_BANK_ACCOUNTS iba,
2615 CE_BANK_BRANCHES_V iba_bnk_branch,
2616 IBY_EXT_BANK_ACCOUNTS_INT_V eba,
2617 CE_BANK_BRANCHES_V eba_bank_branch
2618 WHERE
2619 docs.document_payable_id = p_doc_id
2620 AND docs.legal_entity_id = payer.party_legal_id
2621 AND docs.payee_party_id = payee.party_id
2622 AND docs.remit_to_location_id = payee_loc.location_id(+)
2623 AND docs.internal_bank_account_id = iba.bank_account_id (+)
2624 AND iba_bnk_branch.branch_party_id (+) = iba.bank_branch_id
2625 AND docs.external_bank_account_id = eba.ext_bank_account_id(+)
2626 AND eba.bank_party_id = eba_bank_branch.bank_party_id(+)
2627 AND eba.branch_party_id = eba_bank_branch.branch_party_id(+)
2628 ;
2629
2630 -- bug 5230187 - Added outer joins to iba and iba_bnk_branch in cursor c_onlineDocumentInfo
2631 /*
2632 * If the c_documentInfo cursor returns no rows, then
2633 * use this cursor to pick up the identifying fields
2634 * of the document. This document will surely fail
2635 * validation, and we need to provide the identifying fields
2636 * to the validation sets.
2637 */
2638 CURSOR c_basicDocumentInfo (p_doc_id
2639 IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE)
2640 RETURN basicDocRecType
2641 IS
2642 SELECT
2643 docs.calling_app_id calling_app_id,
2644 docs.calling_app_doc_unique_ref1 calling_app_doc_id1,
2645 docs.calling_app_doc_unique_ref2 calling_app_doc_id2,
2646 docs.calling_app_doc_unique_ref3 calling_app_doc_id3,
2647 docs.calling_app_doc_unique_ref4 calling_app_doc_id4,
2648 docs.calling_app_doc_unique_ref5 calling_app_doc_id5,
2649 docs.pay_proc_trxn_type_code pay_proc_trxn_type_cd,
2650 docs.document_payable_id document_id
2651 FROM
2652 IBY_DOCS_PAYABLE_ALL docs
2653 WHERE
2654 docs.document_payable_id = p_doc_id
2655 ;
2656
2657 /*
2658 * If the c_onlineDocumentInfo cursor returns no rows, then
2659 * use this cursor to pick up the identifying fields
2660 * of the document. This document will surely fail
2661 * validation, and we need to provide the identifying fields
2662 * to the validation sets.
2663 */
2664 CURSOR c_basicOnlineDocumentInfo (p_doc_id
2665 IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE)
2666 RETURN basicDocRecType
2667 IS
2668 SELECT
2669 docs.calling_app_id calling_app_id,
2670 docs.calling_app_doc_unique_ref1 calling_app_doc_id1,
2671 docs.calling_app_doc_unique_ref2 calling_app_doc_id2,
2672 docs.calling_app_doc_unique_ref3 calling_app_doc_id3,
2673 docs.calling_app_doc_unique_ref4 calling_app_doc_id4,
2674 docs.calling_app_doc_unique_ref5 calling_app_doc_id5,
2675 docs.pay_proc_trxn_type_code pay_proc_trxn_type_cd,
2676 docs.document_payable_id document_id
2677 FROM
2678 IBY_DOCS_PAYABLE_GT docs
2679 WHERE
2680 docs.document_payable_id = p_doc_id
2681 ;
2682
2683 l_no_rows_flag BOOLEAN := FALSE;
2684 basic_doc_rec basicDocRecType;
2685
2686 BEGIN
2687
2688 print_debuginfo(l_module_name, 'ENTER');
2689 print_debuginfo(l_module_name, 'Picking up document data for: '
2690 || p_document_id);
2691 print_debuginfo(l_module_name, 'p_isOnline: '
2692 || p_isOnline);
2693
2694 /*
2695 * If the caller wants to perform an online validation
2696 * pick up the fields of the given document only.
2697 *
2698 * Otherwise, pick up the fields from the document, payer,
2699 * payer bank, payee and payee bank as well.
2700 */
2701 IF (UPPER(p_isOnline) = 'Y') THEN
2702
2703 OPEN c_onlineDocumentInfo(p_document_id);
2704 FETCH c_onlineDocumentInfo INTO x_document_rec;
2705 CLOSE c_onlineDocumentInfo;
2706
2707 /*
2708 * If the fetched doc id is null, it implies
2709 * no rows were found by the cursor.
2710 */
2711 IF (x_document_rec.document_id IS NULL) THEN
2712 print_debuginfo(l_module_name, 'No rows for online doc');
2713 l_no_rows_flag := TRUE;
2714 ELSE
2715 l_no_rows_flag := FALSE;
2716 END IF;
2717
2718 ELSE
2719
2720 OPEN c_documentInfo(p_document_id);
2721 FETCH c_documentInfo INTO x_document_rec;
2722 CLOSE c_documentInfo;
2723
2724 /*
2725 * If the fetched doc id is null, it implies
2726 * no rows were found by the cursor.
2727 */
2728 IF (x_document_rec.document_id IS NULL) THEN
2729 print_debuginfo(l_module_name, 'No rows for offline doc');
2730 l_no_rows_flag := TRUE;
2731 ELSE
2732 l_no_rows_flag := FALSE;
2733 END IF;
2734
2735 END IF;
2736
2737 /*
2738 * We try to get all the document fields required for validation
2739 * by joining with the payee/payer/int bank and ext bank
2740 * tables. If this join fails for some reason, then no
2741 * doc fields will be fetched. This will lead to an empty
2742 * document being returned which causes the program to abort.
2743 *
2744 * In such a case, pick up only the document fields from
2745 * IBY_DOCS_PAYABLE_ALL and pass it back. This document
2746 * will surely fail validation because of the missing fields.
2747 * Since we passed back the basic doc fields, this document
2748 * can be failed in a graceful manner.
2749 */
2750 IF (l_no_rows_flag = TRUE) THEN
2751
2752 print_debuginfo(l_module_name, 'Unable to find related fields '
2753 || 'for document '
2754 || p_document_id
2755 || '. Only basic document fields will be returned.'
2756 );
2757
2758 IF (UPPER(p_isOnline) = 'Y') THEN
2759
2760 OPEN c_basicOnlineDocumentInfo(p_document_id);
2761 FETCH c_basicOnlineDocumentInfo INTO basic_doc_rec;
2762 CLOSE c_basicOnlineDocumentInfo;
2763
2764 ELSE
2765
2766 OPEN c_basicDocumentInfo(p_document_id);
2767 FETCH c_basicDocumentInfo INTO basic_doc_rec;
2768 CLOSE c_basicDocumentInfo;
2769
2770 END IF;
2771
2772 /*
2773 * Copy the basic fields of the doc back into the
2774 * out param record which will be passed to the
2775 * validation sets
2776 */
2777 x_document_rec.calling_app_id := basic_doc_rec.calling_app_id;
2778
2779 x_document_rec.calling_app_doc_id1 :=
2780 basic_doc_rec.calling_app_doc_id1;
2781 x_document_rec.calling_app_doc_id2 :=
2782 basic_doc_rec.calling_app_doc_id2;
2783 x_document_rec.calling_app_doc_id3 :=
2784 basic_doc_rec.calling_app_doc_id3;
2785 x_document_rec.calling_app_doc_id4 :=
2786 basic_doc_rec.calling_app_doc_id4;
2787 x_document_rec.calling_app_doc_id5 :=
2788 basic_doc_rec.calling_app_doc_id5;
2789
2790 x_document_rec.pay_proc_trxn_type_cd :=
2791 basic_doc_rec.pay_proc_trxn_type_cd;
2792 x_document_rec.document_id := basic_doc_rec.document_id;
2793
2794 END IF;
2795
2796 print_debuginfo(l_module_name, 'EXIT');
2797
2798 EXCEPTION
2799 WHEN OTHERS THEN
2800 FND_MESSAGE.SET_NAME('IBY', '');
2801 FND_MESSAGE.SET_TOKEN('SQLERR','initDocumentData : '||
2802 substr(SQLERRM, 1, 300));
2803 FND_MSG_PUB.Add;
2804 print_debuginfo(l_module_name,' '||
2805 substr(SQLERRM, 1, 300), FND_LOG.LEVEL_UNEXPECTED);
2806 RAISE g_abort_program;
2807
2808
2809 END initDocumentData;
2810
2811 /*--------------------------------------------------------------------
2812 | NAME:
2813 | initCharValData
2814 |
2815 | PURPOSE:
2816 | Initializes the character validation record with data from
2817 | Oracle Payment's tables.
2818 |
2819 | Character validation checks if any invalid characters are
2820 | present in the document. So mostly VARCHAR fields are picked
2821 | up (it is not necessary to validate numeric fields for invalid
2822 | characters).
2823 |
2824 | Usually document lines are not sent to the bank, so these are
2825 | not picked up for validation.
2826 |
2827 | PARAMETERS:
2828 | IN
2829 |
2830 |
2831 | OUT
2832 |
2833 |
2834 | RETURNS:
2835 |
2836 | NOTES:
2837 |
2838 *---------------------------------------------------------------------*/
2839 PROCEDURE initCharValData(
2840 p_document_id IN IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE,
2841 x_charval_rec IN OUT NOCOPY charValRecType
2842 )
2843 IS
2844
2845 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.initCharValData';
2846
2847 CURSOR c_charval_fields (p_doc_id IBY_DOCS_PAYABLE_ALL.
2848 document_payable_id%TYPE)
2849 IS
2850 SELECT
2851
2852 /* DOCUMENT RELATED */
2853 doc.document_payable_id,
2854 doc.calling_app_id,
2855 doc.calling_app_doc_unique_ref1,
2856 doc.calling_app_doc_unique_ref2,
2857 doc.calling_app_doc_unique_ref3,
2858 doc.calling_app_doc_unique_ref4,
2859 doc.calling_app_doc_unique_ref5,
2860 doc.pay_proc_trxn_type_code,
2861 doc.calling_app_doc_ref_number,
2862 doc.unique_remittance_identifier,
2863 doc.uri_check_digit,
2864 doc.po_number,
2865 doc.document_description,
2866 doc.bank_assigned_ref_code,
2867 doc.payment_reason_comments,
2868 doc.remittance_message1,
2869 doc.remittance_message2,
2870 doc.remittance_message3,
2871 dlv.format_value,
2872 pmt_reason.format_value,
2873 lines.calling_app_document_line_code,
2874 lines.line_type,
2875 lines.line_name,
2876 lines.description,
2877 lines.unit_of_measure,
2878 lines.po_number,
2879
2880 /* PAYER */
2881 payer.party_number, -- payer number
2882 payer.party_name, -- payer name
2883 payer.party_legal_name, -- payer legal name
2884 payer.party_tax_id, -- payer tax id
2885 payer.party_address_line1, -- payer add line 1
2886 payer.party_address_line2, -- payer add line 2,
2887 payer.party_address_line3, -- payer add line 3
2888 payer.party_address_city, -- payer city
2889 payer.party_address_county , -- payer county
2890 payer.party_address_state, -- payer state
2891 payer.party_address_country, -- payer country
2892 payer.party_address_postal_code, -- payer postal code
2893
2894 /* PAYER BANK */
2895 iba_bnk_branch.bank_name, -- payer bank name
2896 iba_bnk_branch.bank_number, -- payer bank number
2897 iba_bnk_branch.branch_number, -- payer bank branch number
2898 iba_bnk_branch.bank_branch_name, -- payer bank branch name
2899 iba_bnk_branch.eft_swift_code, -- payer bank swift code
2900 iba_bnk_branch.address_line1, -- payer bank add line 1
2901 iba_bnk_branch.address_line2, -- payer bank add line 2
2902 iba_bnk_branch.address_line3, -- payer bank add line 3
2903 iba_bnk_branch.city, -- payer bank city
2904 iba_bnk_branch.province, -- payer bank county
2905 iba_bnk_branch.state, -- payer bank state
2906 nvl(iba_bnk_branch.country,iba_bnk_branch.bank_home_country), -- payer bank country
2907 iba_bnk_branch.zip, -- payer bank postal code
2908 iba_bnk_branch.bank_name_alt, -- payer bank name alt
2909 iba_bnk_branch.bank_branch_name_alt, -- payer bank branch name alt
2910
2911 iba.bank_account_name_alt, -- payer bank acct name alt
2912 iba.bank_account_type, -- payer bank acct type
2913 '', -- payer bank assigned id1
2914 '', -- payer bank assigned id2
2915 iba.eft_user_num, -- payer eft user number
2916 iba.eft_requester_identifier, -- payer eft req identifier
2917 iba.short_account_name, -- payer bank acct short name
2918 iba.account_holder_name_alt, -- payer bank acct holder name alt
2919 iba.account_holder_name, -- payer bank account holder name
2920 iba.bank_account_num, -- payer bank account num
2921 iba.bank_account_name, -- payer bank account name
2922 iba.iban_number, -- payer bank acct iban number
2923 iba.check_digits, -- payer bank acct check digits
2924
2925 /* PAYEE */
2926 payee.party_number, -- payee number
2927 payee.party_name, -- payee name
2928 payee.tax_reference, -- payee tax number
2929 payee_loc.address1, -- payee add line1
2930 payee_loc.address2, -- payee add line2
2931 payee_loc.address3, -- payee add line3
2932 payee_loc.city, -- payee city
2933 payee_loc.county, -- payee county
2934 payee_loc.province, -- payee province
2935 payee_loc.state, -- payee state
2936 payee_loc.country, -- payee country
2937 payee_loc.postal_code, -- payee postal code
2938
2939 /* PAYEE BANK */
2940 eba.bank_name, -- payee bank name
2941 eba.bank_number, -- payee bank number
2942 eba.branch_number, -- payee bank branch number
2943 eba.bank_branch_name, -- payee bank branch name
2944 eba.primary_acct_owner_name, -- payee bank account holder name
2945 eba.bank_account_number, -- payee bank account number
2946 eba.bank_account_name, -- payee bank account name
2947 eba.iban_number, -- payee bank account IBAN
2948 eba.eft_swift_code, -- payee bank swift code
2949 eba.check_digits, -- payee bank account check digits
2950 '', -- payee bank add line 1
2951 '', -- payee bank add line 2
2952 '', -- payee bank add line 3
2953 '', -- payee bank city
2954 '', -- payee bank county
2955 '', -- payee bank state
2956 '', -- payee bank country
2957 '', -- payee bank postal code
2958 '', -- payee bank name alternate
2959 '', -- payee bank branch name alternate
2960 eba.country_code, -- payee bank country code
2961 eba.alternate_account_name, -- payee bank account name alternate
2962 eba.bank_account_type, -- payee bank account type
2963 eba.short_acct_name, -- payee bank account short name
2964 '' -- payee bank acct holder name alt
2965 FROM
2966 /* Document related */
2967 IBY_DOCS_PAYABLE_ALL doc,
2968 IBY_PAYMENT_REASONS_VL pmt_reason,
2969 IBY_DELIVERY_CHANNELS_VL dlv,
2970 IBY_DOCUMENT_LINES lines,
2971 /* Payer */
2972 IBY_PP_FIRST_PARTY_V payer,
2973 /* Payer bank */
2974 CE_BANK_ACCOUNTS iba,
2975 CE_BANK_BRANCHES_V iba_bnk_branch,
2976 /* Payee */
2977 HZ_PARTIES payee,
2978 HZ_LOCATIONS payee_loc,
2979 /* Payee bank */
2980 IBY_EXT_BANK_ACCOUNTS_V eba
2981 WHERE
2982 /* document related */
2983 doc.document_payable_id = p_doc_id
2984 AND doc.payment_reason_code = pmt_reason.payment_reason_code(+)
2985 AND doc.delivery_channel_code = dlv.delivery_channel_code(+)
2986 AND doc.document_payable_id = lines.document_payable_id(+)
2987 /* payer */
2988 AND doc.legal_entity_id = payer.party_legal_id
2989 /* payer bank */
2990 AND doc.internal_bank_account_id = iba.bank_account_id
2991 AND iba_bnk_branch.branch_party_id = iba.bank_branch_id
2992 /* payee */
2993 AND doc.payee_party_id = payee.party_id
2994 AND doc.remit_to_location_id = payee_loc.location_id(+)
2995 /* payee bank */
2996 AND doc.external_bank_account_id = eba.ext_bank_account_id(+)
2997 ;
2998
2999 BEGIN
3000
3001 --
3002 -- Fetching fields from character validation cursor
3003 --
3004 print_debuginfo(l_module_name, 'Picking up character '
3005 || 'validation fields for: '
3006 || p_document_id);
3007
3008 OPEN c_charval_fields(p_document_id);
3009 FETCH c_charval_fields INTO x_charval_rec;
3010 CLOSE c_charval_fields;
3011
3012 EXCEPTION
3013 WHEN OTHERS THEN
3014 FND_MESSAGE.SET_NAME('IBY', '');
3015 FND_MESSAGE.SET_TOKEN('SQLERR','initCharValData: '
3016 || substr(SQLERRM, 1, 300));
3017 FND_MSG_PUB.Add;
3018 print_debuginfo(l_module_name,' '
3019 || substr(SQLERRM, 1, 300), FND_LOG.LEVEL_UNEXPECTED);
3020 RAISE g_abort_program;
3021
3022 END initCharValData;
3023
3024 /*--------------------------------------------------------------------
3025 | NAME:
3026 | initPaymentData
3027 |
3028 | PURPOSE:
3029 | Initializes the payment record from Oracle Payment's tables.
3030 |
3031 | Fields of the payment are picked up and populated into a
3032 | payment record. The validation sets will validate the
3033 | various payment fields.
3034 |
3035 |
3036 | PARAMETERS:
3037 | IN
3038 |
3039 |
3040 | OUT
3041 |
3042 |
3043 | RETURNS:
3044 |
3045 | NOTES:
3046 |
3047 *---------------------------------------------------------------------*/
3048 PROCEDURE initPaymentData(
3049 p_payment_id IN IBY_PAYMENTS_ALL.payment_id%type,
3050 x_payment_rec IN OUT NOCOPY paymentRecType
3051 )
3052 IS
3053
3054 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.initPaymentData';
3055
3056 /*
3057 * Pick up all the Payment fields which need to be validated.
3058 * Note: If any field is modified in the query below, the same
3059 * field in 'paymentRecType' record in the spec should be
3060 * modified accordingly.
3061 */
3062 CURSOR c_payment_rec (p_pay_id IBY_PAYMENTS_ALL.payment_id%TYPE)
3063 RETURN paymentRecType IS
3064 SELECT
3065 pay.payment_id pmt_id,
3066 pay.payment_amount pmt_amount,
3067 pay.payment_currency_code pmt_currency,
3068 pay.delivery_channel_code pmt_delivery_channel_code,
3069 payer.party_address_country pmt_payer_le_country,
3070 pay.payment_details pmt_detail,
3071 0 pmt_payment_reason_count,
3072 pay.int_bank_account_iban int_bank_account_iban,
3073 payer.party_address_line1,
3074 payer.party_address_city,
3075 payer.party_address_postal_code
3076
3077 FROM
3078 IBY_PAYMENTS_ALL pay,
3079 IBY_PP_FIRST_PARTY_V payer
3080 WHERE
3081 pay.payment_id = p_pay_id
3082 AND
3083 pay.legal_entity_id = payer.party_legal_id;
3084
3085 BEGIN
3086
3087 print_debuginfo(l_module_name, 'Picking up payment data for: '
3088 || p_payment_id);
3089
3090 /*
3091 * Fetch the payment related fields
3092 */
3093 OPEN c_payment_rec(p_payment_id);
3094 FETCH c_payment_rec INTO x_payment_rec;
3095
3096 -- Get payment_reason_count (for Belgium)
3097 SELECT count(distinct payment_reason_code)
3098 INTO x_payment_rec.pmt_payment_reason_count
3099 FROM iby_docs_payable_all
3100 WHERE payment_id = p_payment_id;
3101
3102 CLOSE c_payment_rec;
3103
3104 EXCEPTION
3105 WHEN OTHERS THEN
3106 FND_MESSAGE.SET_NAME('IBY', '');
3107 FND_MESSAGE.SET_TOKEN('SQLERR','initPaymentData : '||
3108 substr(SQLERRM, 1, 300));
3109 FND_MSG_PUB.Add;
3110 print_debuginfo(l_module_name,' '||
3111 substr(SQLERRM, 1, 300), FND_LOG.LEVEL_UNEXPECTED);
3112 RAISE g_abort_program;
3113
3114 END initPaymentData;
3115
3116 /*--------------------------------------------------------------------
3117 | NAME:
3118 | initInstructionData
3119 |
3120 | PURPOSE:
3121 | Initializes the instruction record from Oracle Payment's tables.
3122 |
3123 | Fields related to a payment instruction are picked up and
3124 | populated into an instruction record. The validation sets will
3125 | validate individual fields of this record.
3126 |
3127 | PARAMETERS:
3128 | IN
3129 |
3130 |
3131 | OUT
3132 |
3133 |
3134 | RETURNS:
3135 |
3136 | NOTES:
3137 |
3138 *---------------------------------------------------------------------*/
3139 PROCEDURE initInstructionData (
3140 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.
3141 payment_instruction_id%type,
3142 x_instruction_rec IN OUT NOCOPY instructionRecType
3143 )
3144 IS
3145
3146 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.initInstructionData';
3147
3148 /*
3149 * Picking up all the instruction fields which need to be validated.
3150 * Note: If any field is to be modified in the query below, the same
3151 * field in 'instructionRecType' in the spec should be changed accordingly.
3152 */
3153 CURSOR c_instruction_rec (p_instr_id IBY_PAY_INSTRUCTIONS_ALL.
3154 payment_instruction_id%type)
3155 RETURN instructionRecType
3156 IS
3157 SELECT
3158 instr.payment_instruction_id ins_id,
3159 0 ins_amount,
3160 0 ins_document_count
3161 FROM
3162 IBY_PAY_INSTRUCTIONS_ALL instr
3163 WHERE
3164 instr.payment_instruction_id = p_instr_id;
3165
3166 BEGIN
3167
3168 print_debuginfo(l_module_name, 'Picking up instruction data for: '
3169 || p_instruction_id);
3170
3171 /*
3172 * Fetching fields from Payment cursor
3173 */
3174 OPEN c_instruction_rec(p_instruction_id);
3175 FETCH c_instruction_rec INTO x_instruction_rec;
3176
3177 -- Get instruction_amount and document_count
3178 select sum(d.document_amount),
3179 count(d.document_payable_id)
3180 into x_instruction_rec.ins_amount,
3181 x_instruction_rec.ins_document_count
3182 from iby_docs_payable_all d, iby_payments_all p
3183 where p.payment_instruction_id = p_instruction_id
3184 and p.payment_id = d.payment_id
3185
3186 /*
3187 * Fix for bug 5672789:
3188 *
3189 * When calculating payment count for an instruction,
3190 * only pick up payments that are in
3191 * 'INSTRUCTION_CREATED' status.
3192 */
3193 and p.payment_status IN (PAY_STATUS_INS_CRTD)
3194 ;
3195
3196 CLOSE c_instruction_rec;
3197
3198 EXCEPTION
3199 WHEN OTHERS THEN
3200 FND_MESSAGE.SET_NAME('IBY', '');
3201 FND_MESSAGE.SET_TOKEN('SQLERR','initInstructionData : '||
3202 substr(SQLERRM, 1, 300));
3203 FND_MSG_PUB.Add;
3204 print_debuginfo(l_module_name,' '||
3205 substr(SQLERRM, 1, 300), FND_LOG.LEVEL_UNEXPECTED);
3206 RAISE g_abort_program;
3207
3208 END initInstructionData;
3209
3210 /*--------------------------------------------------------------------
3211 | NAME:
3212 | insert_transaction_errors
3213 |
3214 | PURPOSE:
3215 | Inserts the error messages into the errors table. For
3216 | online validations, the error messages are inserted into
3217 | IBY_TRANSACTION_ERRORS_GT; for deferred validations, the
3218 | error messages are inserted into IBY_TRANSACTION_ERRORS
3219 | table.
3220 |
3221 | Validation sets populate the transaction errors into a PLSQL
3222 | table. This method performs a bulk insert of the given records
3223 | into the transaction errors table.
3224 |
3225 | PARAMETERS:
3226 | IN
3227 |
3228 |
3229 | OUT
3230 |
3231 |
3232 | RETURNS:
3233 |
3234 | NOTES:
3235 |
3236 *---------------------------------------------------------------------*/
3237 PROCEDURE insert_transaction_errors(
3238 p_isOnlineVal IN VARCHAR2,
3239 x_docErrorTab IN OUT NOCOPY docErrorTabType,
3240 x_trxnErrTokenTab IN OUT NOCOPY trxnErrTokenTabType
3241 )
3242 IS
3243 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
3244 '.insert_transaction_errors';
3245
3246 /*
3247 * Column types for insertion into IBY_TRANSACTION_ERRORS table.
3248 */
3249 TYPE t_transaction_error_id IS TABLE OF
3250 IBY_TRANSACTION_ERRORS.transaction_error_id%TYPE
3251 INDEX BY BINARY_INTEGER;
3252 TYPE t_transaction_type IS TABLE OF
3253 IBY_TRANSACTION_ERRORS.transaction_type%TYPE
3254 INDEX BY BINARY_INTEGER;
3255 TYPE t_transaction_id IS TABLE OF
3256 IBY_TRANSACTION_ERRORS.transaction_id%TYPE
3257 INDEX BY BINARY_INTEGER;
3258 TYPE t_error_code IS TABLE OF
3259 IBY_TRANSACTION_ERRORS.error_code%TYPE
3260 INDEX BY BINARY_INTEGER;
3261 TYPE t_error_date IS TABLE OF
3262 IBY_TRANSACTION_ERRORS.error_date%TYPE
3263 INDEX BY BINARY_INTEGER;
3264 TYPE t_error_status IS TABLE OF
3265 IBY_TRANSACTION_ERRORS.error_status%TYPE
3266 INDEX BY BINARY_INTEGER;
3267 TYPE t_calling_app_doc_unique_ref1 IS TABLE OF
3268 IBY_TRANSACTION_ERRORS.calling_app_doc_unique_ref1%TYPE
3269 INDEX BY BINARY_INTEGER;
3270 TYPE t_ovrride_allowed_on_err_flg IS TABLE OF
3271 IBY_TRANSACTION_ERRORS.override_allowed_on_error_flag%TYPE
3272 INDEX BY BINARY_INTEGER;
3273 TYPE t_do_not_apply_error_flag IS TABLE OF
3274 IBY_TRANSACTION_ERRORS.do_not_apply_error_flag%TYPE
3275 INDEX BY BINARY_INTEGER;
3276 TYPE t_created_by IS TABLE OF
3277 IBY_TRANSACTION_ERRORS.created_by%TYPE
3278 INDEX BY BINARY_INTEGER;
3279 TYPE t_creation_date IS TABLE OF
3280 IBY_TRANSACTION_ERRORS.creation_date%TYPE
3281 INDEX BY BINARY_INTEGER;
3282 TYPE t_last_updated_by IS TABLE OF
3283 IBY_TRANSACTION_ERRORS.last_updated_by%TYPE
3284 INDEX BY BINARY_INTEGER;
3285 TYPE t_last_update_date IS TABLE OF
3286 IBY_TRANSACTION_ERRORS.last_update_date%TYPE
3287 INDEX BY BINARY_INTEGER;
3288 TYPE t_object_version_number IS TABLE OF
3289 IBY_TRANSACTION_ERRORS.object_version_number%TYPE
3290 INDEX BY BINARY_INTEGER;
3291 TYPE t_last_update_login IS TABLE OF
3292 IBY_TRANSACTION_ERRORS.last_update_login%TYPE
3293 INDEX BY BINARY_INTEGER;
3294 TYPE t_calling_app_id IS TABLE OF
3295 IBY_TRANSACTION_ERRORS.calling_app_id%TYPE
3296 INDEX BY BINARY_INTEGER;
3297 TYPE t_pay_proc_trxn_type_code IS TABLE OF
3298 IBY_TRANSACTION_ERRORS.pay_proc_trxn_type_code%TYPE
3299 INDEX BY BINARY_INTEGER;
3300 TYPE t_calling_app_doc_unique_ref2 IS TABLE OF
3301 IBY_TRANSACTION_ERRORS.calling_app_doc_unique_ref2%TYPE
3302 INDEX BY BINARY_INTEGER;
3303 TYPE t_calling_app_doc_unique_ref3 IS TABLE OF
3304 IBY_TRANSACTION_ERRORS.calling_app_doc_unique_ref3%TYPE
3305 INDEX BY BINARY_INTEGER;
3306 TYPE t_calling_app_doc_unique_ref4 IS TABLE OF
3307 IBY_TRANSACTION_ERRORS.calling_app_doc_unique_ref4%TYPE
3308 INDEX BY BINARY_INTEGER;
3309 TYPE t_calling_app_doc_unique_ref5 IS TABLE OF
3310 IBY_TRANSACTION_ERRORS.calling_app_doc_unique_ref5%TYPE
3311 INDEX BY BINARY_INTEGER;
3312 TYPE t_error_type IS TABLE OF
3313 IBY_TRANSACTION_ERRORS.error_type%TYPE
3314 INDEX BY BINARY_INTEGER;
3315 TYPE t_error_message IS TABLE OF
3316 IBY_TRANSACTION_ERRORS.error_message%TYPE
3317 INDEX BY BINARY_INTEGER;
3318 TYPE t_validation_set_code IS TABLE OF
3319 IBY_TRANSACTION_ERRORS.validation_set_code%TYPE
3320 INDEX BY BINARY_INTEGER;
3321 TYPE t_pass_date IS TABLE OF
3322 IBY_TRANSACTION_ERRORS.pass_date%TYPE
3323 INDEX BY BINARY_INTEGER;
3324 TYPE t_override_justification IS TABLE OF
3325 IBY_TRANSACTION_ERRORS.override_justification%TYPE
3326 INDEX BY BINARY_INTEGER;
3327 TYPE t_override_date IS TABLE OF
3328 IBY_TRANSACTION_ERRORS.override_date%TYPE
3329 INDEX BY BINARY_INTEGER;
3330
3331 l_transaction_error_id t_transaction_error_id;
3332 l_transaction_type t_transaction_type;
3333 l_transaction_id t_transaction_id;
3334 l_error_code t_error_code;
3335 l_error_date t_error_date;
3336 l_error_status t_error_status;
3337 l_calling_app_doc_unique_ref1 t_calling_app_doc_unique_ref1;
3338 l_ovrride_allowed_on_err_flg t_ovrride_allowed_on_err_flg;
3339 l_do_not_apply_error_flag t_do_not_apply_error_flag;
3340 l_created_by t_created_by;
3341 l_creation_date t_creation_date;
3342 l_last_updated_by t_last_updated_by;
3343 l_last_update_date t_last_update_date;
3344 l_object_version_number t_object_version_number;
3345 l_last_update_login t_last_update_login;
3346 l_calling_app_id t_calling_app_id;
3347 l_pay_proc_trxn_type_code t_pay_proc_trxn_type_code;
3348 l_calling_app_doc_unique_ref2 t_calling_app_doc_unique_ref2;
3349 l_calling_app_doc_unique_ref3 t_calling_app_doc_unique_ref3;
3350 l_calling_app_doc_unique_ref4 t_calling_app_doc_unique_ref4;
3351 l_calling_app_doc_unique_ref5 t_calling_app_doc_unique_ref5;
3352 l_error_type t_error_type;
3353 l_error_message t_error_message;
3354 l_validation_set_code t_validation_set_code;
3355 l_pass_date t_pass_date;
3356 l_override_justification t_override_justification;
3357 l_override_date t_override_date;
3358
3359 /*
3360 * Column types for insertion into IBY_TRXN_ERROR_TOKENS table.
3361 */
3362 TYPE t_trxn_error_id IS TABLE OF
3363 IBY_TRXN_ERROR_TOKENS.transaction_error_id%TYPE
3364 INDEX BY BINARY_INTEGER;
3365 TYPE t_token_name IS TABLE OF
3366 IBY_TRXN_ERROR_TOKENS.token_name%TYPE
3367 INDEX BY BINARY_INTEGER;
3368 TYPE t_crtd_by IS TABLE OF
3369 IBY_TRXN_ERROR_TOKENS.created_by%TYPE
3370 INDEX BY BINARY_INTEGER;
3371 TYPE t_crt_date IS TABLE OF
3372 IBY_TRXN_ERROR_TOKENS.creation_date%TYPE
3373 INDEX BY BINARY_INTEGER;
3374 TYPE t_last_updt_by IS TABLE OF
3375 IBY_TRXN_ERROR_TOKENS.last_updated_by%TYPE
3376 INDEX BY BINARY_INTEGER;
3377 TYPE t_last_updt_date IS TABLE OF
3378 IBY_TRXN_ERROR_TOKENS.last_update_date%TYPE
3379 INDEX BY BINARY_INTEGER;
3380 TYPE t_object_ver_number IS TABLE OF
3381 IBY_TRXN_ERROR_TOKENS.object_version_number%TYPE
3382 INDEX BY BINARY_INTEGER;
3383 TYPE t_token_value IS TABLE OF
3384 IBY_TRXN_ERROR_TOKENS.token_value%TYPE
3385 INDEX BY BINARY_INTEGER;
3386 TYPE t_lookup_type_source IS TABLE OF
3387 IBY_TRXN_ERROR_TOKENS.lookup_type_source%TYPE
3388 INDEX BY BINARY_INTEGER;
3389 TYPE t_last_updt_login IS TABLE OF
3390 IBY_TRXN_ERROR_TOKENS.last_update_login%TYPE
3391 INDEX BY BINARY_INTEGER;
3392
3393 l_trxn_error_id t_trxn_error_id;
3394 l_token_name t_token_name;
3395 l_crtd_by t_crtd_by;
3396 l_crt_date t_crt_date;
3397 l_last_updtd_by t_last_updt_by;
3398 l_last_updt_date t_last_updt_date;
3399 l_object_ver_number t_object_ver_number;
3400 l_token_value t_token_value;
3401 l_lookup_type_source t_lookup_type_source;
3402 l_last_updt_login t_last_updt_login;
3403
3404
3405 BEGIN
3406
3407 print_debuginfo(l_module_name, 'ENTER');
3408
3409 print_debuginfo(l_module_name, 'Online flag: ' || p_isOnlineVal);
3410
3411 IF (x_docErrorTab.COUNT > 0) THEN
3412
3413 /*
3414 * Create an array of values for each column. These arrays
3415 * will be used in the bulk insert.
3416 */
3417 FOR i in x_docErrorTab.FIRST..x_docErrorTab.LAST LOOP
3418
3419 l_transaction_error_id(i)
3420 := x_docErrorTab(i).transaction_error_id;
3421 l_transaction_type(i)
3422 := x_docErrorTab(i).transaction_type;
3423 l_transaction_id(i)
3424 := x_docErrorTab(i).transaction_id;
3425 l_error_code(i)
3426 := x_docErrorTab(i).error_code;
3427 l_error_date(i)
3428 := NVL(x_docErrorTab(i).error_date, sysdate);
3429 l_error_status(i)
3430 := NVL(x_docErrorTab(i).error_status, 'ACTIVE');
3431 l_calling_app_doc_unique_ref1(i)
3432 := x_docErrorTab(i).calling_app_doc_unique_ref1;
3433
3434 /*
3435 * Fix for bug 5206309:
3436 *
3437 * For payment instructions, the override
3438 * allowed flag needs to be defaulted to Y
3439 * for the time being.
3440 */
3441 IF (x_docErrorTab(i).transaction_type =
3442 'PAYMENT_INSTRUCTION') THEN
3443
3444 l_ovrride_allowed_on_err_flg(i)
3445 := NVL(x_docErrorTab(i).
3446 override_allowed_on_error_flag,
3447 'Y');
3448
3449 ELSE
3450
3451 l_ovrride_allowed_on_err_flg(i)
3452 := NVL(x_docErrorTab(i).
3453 override_allowed_on_error_flag,
3454 'N');
3455
3456 END IF;
3457
3458 l_do_not_apply_error_flag(i)
3459 := NVL(x_docErrorTab(i).do_not_apply_error_flag, 'N');
3460 l_created_by(i)
3461 := NVL(x_docErrorTab(i).created_by, fnd_global.user_id);
3462 l_creation_date(i)
3463 := NVL(x_docErrorTab(i).creation_date, sysdate);
3464 l_last_updated_by(i)
3465 := NVL(x_docErrorTab(i).last_updated_by,
3466 fnd_global.user_id);
3467 l_last_update_date(i)
3468 := NVL(x_docErrorTab(i).last_update_date, sysdate);
3469 l_object_version_number(i)
3470 := NVL(x_docErrorTab(i).object_version_number, 1);
3471 l_last_update_login(i)
3472 := NVL(x_docErrorTab(i).last_update_login,
3473 fnd_global.user_id);
3474 l_calling_app_id(i)
3475 := x_docErrorTab(i).calling_app_id;
3476 l_pay_proc_trxn_type_code(i)
3477 := x_docErrorTab(i).pay_proc_trxn_type_code;
3478 l_calling_app_doc_unique_ref2(i)
3479 := x_docErrorTab(i).calling_app_doc_unique_ref2;
3480 l_calling_app_doc_unique_ref3(i)
3481 := x_docErrorTab(i).calling_app_doc_unique_ref3;
3482 l_calling_app_doc_unique_ref4(i)
3483 := x_docErrorTab(i).calling_app_doc_unique_ref4;
3484 l_calling_app_doc_unique_ref5(i)
3485 := x_docErrorTab(i).calling_app_doc_unique_ref5;
3486 l_error_type(i)
3487 := NVL(x_docErrorTab(i).error_type, 'VALIDATION');
3488 l_error_message(i)
3489 := x_docErrorTab(i).error_message;
3490 l_validation_set_code(i)
3491 := x_docErrorTab(i).validation_set_code;
3492 l_pass_date(i)
3493 := x_docErrorTab(i).pass_date;
3494 l_override_justification(i)
3495 := x_docErrorTab(i).override_justification;
3496 l_override_date(i)
3497 := x_docErrorTab(i).override_date;
3498
3499 END LOOP;
3500
3501 END IF;
3502
3503 IF (UPPER(p_isOnlineVal) = 'N') THEN
3504
3505 /*
3506 * Insert error messages into IBY_TRANSACTION_ERRORS table.
3507 */
3508
3509 print_debuginfo(l_module_name, 'Bulk inserting errors into '
3510 || 'IBY_TRANSACTION_ERRORS.');
3511
3512 --FORALL i in x_docErrorTab.FIRST..x_docErrorTab.LAST
3513 -- INSERT INTO IBY_TRANSACTION_ERRORS VALUES x_docErrorTab(i);
3514
3515
3516 /*
3517 * Use named columns in bulk insert syntax to avoid any
3518 * dependencies on the order of the columns in the table.
3519 */
3520 FORALL i in x_docErrorTab.FIRST..x_docErrorTab.LAST
3521 INSERT INTO IBY_TRANSACTION_ERRORS
3522 (
3523 transaction_error_id,
3524 transaction_type,
3525 transaction_id,
3526 error_code,
3527 error_date,
3528 error_status,
3529 calling_app_doc_unique_ref1,
3530 override_allowed_on_error_flag,
3531 do_not_apply_error_flag,
3532 created_by,
3533 creation_date,
3534 last_updated_by,
3535 last_update_date,
3536 object_version_number,
3537 last_update_login,
3538 calling_app_id,
3539 pay_proc_trxn_type_code,
3540 calling_app_doc_unique_ref2,
3541 calling_app_doc_unique_ref3,
3542 calling_app_doc_unique_ref4,
3543 calling_app_doc_unique_ref5,
3544 error_type,
3545 error_message,
3546 validation_set_code,
3547 pass_date,
3548 override_justification,
3549 override_date
3550 )
3551 VALUES
3552 (
3553 l_transaction_error_id(i),
3554 l_transaction_type(i),
3555 l_transaction_id(i),
3556 l_error_code(i),
3557 l_error_date(i),
3558 l_error_status(i),
3559 l_calling_app_doc_unique_ref1(i),
3560 l_ovrride_allowed_on_err_flg(i),
3561 l_do_not_apply_error_flag(i),
3562 l_created_by(i),
3563 l_creation_date(i),
3564 l_last_updated_by(i),
3565 l_last_update_date(i),
3566 l_object_version_number(i),
3567 l_last_update_login(i),
3568 l_calling_app_id(i),
3569 l_pay_proc_trxn_type_code(i),
3570 l_calling_app_doc_unique_ref2(i),
3571 l_calling_app_doc_unique_ref3(i),
3572 l_calling_app_doc_unique_ref4(i),
3573 l_calling_app_doc_unique_ref5(i),
3574 l_error_type(i),
3575 l_error_message(i),
3576 l_validation_set_code(i),
3577 l_pass_date(i),
3578 l_override_justification(i),
3579 l_override_date(i)
3580 )
3581 ;
3582
3583 print_debuginfo(l_module_name, 'Finished populating '
3584 || 'IBY_TRANSACTION_ERRORS.');
3585
3586
3587 ELSE
3588
3589 /*
3590 * Insert error messages into IBY_TRANSACTION_ERRORS_GT table.
3591 */
3592
3593 print_debuginfo(l_module_name, 'Bulk Inserting errors into '
3594 || 'IBY_TRANSACTION_ERRORS_GT.');
3595
3596 --FORALL i in x_docErrorTab.FIRST..x_docErrorTab.LAST
3597 -- INSERT INTO IBY_TRANSACTION_ERRORS_GT VALUES x_docErrorTab(i);
3598
3599 /*
3600 * Use named columns in bulk insert syntax to avoid any
3601 * dependencies on the order of the columns in the table.
3602 */
3603 FORALL i in x_docErrorTab.FIRST..x_docErrorTab.LAST
3604 INSERT INTO IBY_TRANSACTION_ERRORS_GT
3605 (
3606 transaction_error_id,
3607 transaction_type,
3608 transaction_id,
3609 error_code,
3610 error_date,
3611 error_status,
3612 calling_app_doc_unique_ref1,
3613 override_allowed_on_error_flag,
3614 do_not_apply_error_flag,
3615 created_by,
3616 creation_date,
3617 last_updated_by,
3618 last_update_date,
3619 object_version_number,
3620 last_update_login,
3621 calling_app_id,
3622 pay_proc_trxn_type_code,
3623 calling_app_doc_unique_ref2,
3624 calling_app_doc_unique_ref3,
3625 calling_app_doc_unique_ref4,
3626 calling_app_doc_unique_ref5,
3627 error_type,
3628 error_message,
3629 validation_set_code,
3630 pass_date,
3631 override_justification,
3632 override_date
3633 )
3634 VALUES
3635 (
3636 l_transaction_error_id(i),
3637 l_transaction_type(i),
3638 l_transaction_id(i),
3639 l_error_code(i),
3640 l_error_date(i),
3641 l_error_status(i),
3642 l_calling_app_doc_unique_ref1(i),
3643 l_ovrride_allowed_on_err_flg(i),
3644 l_do_not_apply_error_flag(i),
3645 l_created_by(i),
3646 l_creation_date(i),
3647 l_last_updated_by(i),
3648 l_last_update_date(i),
3649 l_object_version_number(i),
3650 l_last_update_login(i),
3651 l_calling_app_id(i),
3652 l_pay_proc_trxn_type_code(i),
3653 l_calling_app_doc_unique_ref2(i),
3654 l_calling_app_doc_unique_ref3(i),
3655 l_calling_app_doc_unique_ref4(i),
3656 l_calling_app_doc_unique_ref5(i),
3657 l_error_type(i),
3658 l_error_message(i),
3659 l_validation_set_code(i),
3660 l_pass_date(i),
3661 l_override_justification(i),
3662 l_override_date(i)
3663 )
3664 ;
3665
3666 print_debuginfo(l_module_name, 'Finished populating '
3667 || 'IBY_TRANSACTION_ERRORS_GT.');
3668
3669
3670 END IF; -- p_isOnlineVal = 'N'
3671
3672 /*
3673 * For both online validations and deferred validations,
3674 * error tokens are always inserted into the IBY_TRXN_ERROR_TOKENS
3675 * table.
3676 */
3677
3678 IF (x_trxnErrTokenTab.COUNT > 0) THEN
3679
3680 /*
3681 * Create an array of values for each column. These arrays
3682 * will be used in the bulk insert.
3683 */
3684 FOR j in x_trxnErrTokenTab.FIRST..x_trxnErrTokenTab.LAST LOOP
3685
3686 l_trxn_error_id(j) := x_trxnErrTokenTab(j).
3687 transaction_error_id;
3688 l_token_name(j) := x_trxnErrTokenTab(j).
3689 token_name;
3690 l_crtd_by(j) := NVL(x_trxnErrTokenTab(j).
3691 created_by, fnd_global.user_id);
3692 l_crt_date(j) := NVL(x_trxnErrTokenTab(j).
3693 creation_date, sysdate);
3694 l_last_updtd_by(j) := NVL(x_trxnErrTokenTab(j).
3695 last_updated_by,
3696 fnd_global.user_id);
3697 l_last_updt_date(j) := NVL(x_trxnErrTokenTab(j).
3698 last_update_date, sysdate);
3699 l_object_ver_number(j) := NVL(x_trxnErrTokenTab(j).
3700 object_version_number, 1);
3701 l_token_value(j) := x_trxnErrTokenTab(j).
3702 token_value;
3703 l_lookup_type_source(j) := x_trxnErrTokenTab(j).
3704 lookup_type_source;
3705 l_last_updt_login(j) := NVL(x_trxnErrTokenTab(j).
3706 last_update_login,
3707 fnd_global.user_id);
3708
3709 END LOOP;
3710
3711
3712 --FORALL j in x_trxnErrTokenTab.FIRST..x_trxnErrTokenTab.LAST
3713 -- INSERT INTO IBY_TRXN_ERROR_TOKENS VALUES x_trxnErrTokenTab(j);
3714
3715 FORALL j in x_trxnErrTokenTab.FIRST..x_trxnErrTokenTab.LAST
3716 INSERT INTO IBY_TRXN_ERROR_TOKENS
3717 (
3718 transaction_error_id,
3719 token_name,
3720 created_by,
3721 creation_date,
3722 last_updated_by,
3723 last_update_date,
3724 object_version_number,
3725 token_value,
3726 lookup_type_source,
3727 last_update_login
3728 )
3729 VALUES
3730 (
3731 l_trxn_error_id(j),
3732 l_token_name(j),
3733 l_crtd_by(j),
3734 l_crt_date(j),
3735 l_last_updtd_by(j),
3736 l_last_updt_date(j),
3737 l_object_ver_number(j),
3738 l_token_value(j),
3739 l_lookup_type_source(j),
3740 l_last_updt_login(j)
3741 )
3742 ;
3743
3744 print_debuginfo(l_module_name, 'Finished populating '
3745 || 'IBY_TRXN_ERROR_TOKENS.');
3746
3747 END IF;
3748
3749 print_debuginfo(l_module_name, 'EXIT');
3750
3751 --
3752 -- Need to raise Business Event here with error information
3753 --
3754 EXCEPTION
3755 WHEN OTHERS THEN
3756 FND_MESSAGE.SET_NAME('IBY', '');
3757 FND_MESSAGE.SET_TOKEN('SQLERR',
3758 'insert_transaction_errors : ' || SQLERRM);
3759 FND_MSG_PUB.Add;
3760 print_debuginfo(l_module_name, SQLERRM, FND_LOG.LEVEL_UNEXPECTED);
3761 RAISE g_abort_program;
3762
3763 END insert_transaction_errors;
3764
3765 /*--------------------------------------------------------------------
3766 | NAME:
3767 | insert_transaction_errors
3768 |
3769 | PURPOSE:
3770 | Original procedure that has been overloaded.
3771 |
3772 | PARAMETERS:
3773 | IN
3774 |
3775 |
3776 | OUT
3777 |
3778 |
3779 | RETURNS:
3780 |
3781 | NOTES:
3782 |
3783 *---------------------------------------------------------------------*/
3784 PROCEDURE insert_transaction_errors(
3785 p_isOnlineVal IN VARCHAR2,
3786 x_docErrorTab IN OUT NOCOPY docErrorTabType
3787 )
3788 IS
3789 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
3790 '.insert_transaction_errors';
3791
3792 BEGIN
3793
3794 print_debuginfo(l_module_name, 'Old Method - ENTER');
3795
3796 insert_transaction_errors(p_isOnlineVal, x_docErrorTab,
3797 l_dummy_err_token_tab);
3798
3799 END insert_transaction_errors;
3800
3801 /*--------------------------------------------------------------------
3802 | NAME:
3803 | insertIntoErrorTable
3804 |
3805 | PURPOSE:
3806 | Inserts the document validation errors into PLSQL Table
3807 |
3808 | PARAMETERS:
3809 | IN
3810 |
3811 |
3812 | OUT
3813 |
3814 |
3815 | RETURNS:
3816 |
3817 | NOTES:
3818 |
3819 *---------------------------------------------------------------------*/
3820 PROCEDURE insertIntoErrorTable(
3821 x_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
3822 x_docErrorTab IN OUT NOCOPY docErrorTabType,
3823 x_trxnErrTokenTab IN OUT NOCOPY trxnErrTokenTabType
3824 )
3825 IS
3826
3827 l_transaction_error_id IBY_TRANSACTION_ERRORS.transaction_error_id%TYPE;
3828 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.insertIntoErrorTable';
3829 l_index NUMBER;
3830
3831 BEGIN
3832
3833 print_debuginfo (l_module_name, 'Validation Failed: '
3834 || x_docErrorRec.error_message);
3835
3836 /* Get the next sequence value from IBY_TRANSACTION_ERRORS_S */
3837 IF x_docErrorRec.transaction_error_id IS NULL THEN
3838
3839 SELECT
3840 IBY_TRANSACTION_ERRORS_S.NEXTVAL
3841 INTO
3842 l_transaction_error_id
3843 FROM
3844 DUAL
3845 ;
3846
3847 x_docErrorRec.transaction_error_id := l_transaction_error_id;
3848
3849 ELSE
3850
3851 l_transaction_error_id := x_docErrorRec.transaction_error_id;
3852
3853 END IF;
3854
3855 IF (x_trxnErrTokenTab.COUNT > 0) THEN
3856
3857 print_debuginfo(l_module_name, 'Token SQL table will be populated.');
3858
3859 FOR i in x_trxnErrTokenTab.FIRST..x_trxnErrTokenTab.LAST
3860 LOOP
3861
3862 IF x_trxnErrTokenTab(i).transaction_error_id is NULL THEN
3863 x_trxnErrTokenTab(i).transaction_error_id :=
3864 l_transaction_error_id;
3865 END IF;
3866
3867 END LOOP;
3868
3869 print_debuginfo(l_module_name, 'Transaction_error_id in token '
3870 || 'SQL table is populated.');
3871
3872 END IF;
3873
3874 print_debuginfo(l_module_name, 'Finished populating the error table.');
3875
3876 l_index := x_docErrorTab.COUNT + 1;
3877 x_docErrorTab(l_index) := x_docErrorRec;
3878
3879 /*
3880 * Reset the transaction error id on the x_docErrorRec object.
3881 * The transaction error id is the primary key of the
3882 * IBY_TRANSACTION_ERRORS table and cannot be reused for a
3883 * new error message.
3884 *
3885 * Since it is the callers responsibility to initialize the
3886 * transaction error id (if need be) on x_docErrorRec, we
3887 * can safely reset the transaction error id here.
3888 */
3889 x_docErrorRec.transaction_error_id := null;
3890
3891 END insertIntoErrorTable;
3892
3893 /*
3894 * The original procedure is overloaded
3895 */
3896 PROCEDURE insertIntoErrorTable(
3897 x_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
3898 x_docErrorTab IN OUT NOCOPY docErrorTabType
3899 )
3900 IS
3901
3902 l_transaction_error_id IBY_TRANSACTION_ERRORS.transaction_error_id%TYPE;
3903 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.insertIntoErrorTable';
3904
3905 BEGIN
3906 print_debuginfo (l_module_name, 'Old Method - Validation Failed: '
3907 || x_docErrorRec.error_message);
3908
3909 insertIntoErrorTable(x_docErrorRec, x_docErrorTab, l_dummy_err_token_tab);
3910
3911 END insertIntoErrorTable;
3912
3913 /*--------------------------------------------------------------------
3914 | NAME:
3915 | retrieveErrorMSG
3916 |
3917 | PURPOSE:
3918 | Function to retrieve an error message according to an object
3919 | code and an error message number provided.
3920 |
3921 | PARAMETERS:
3922 | IN
3923 |
3924 |
3925 | OUT
3926 |
3927 |
3928 | RETURNS:
3929 |
3930 | NOTES:
3931 |
3932 *---------------------------------------------------------------------*/
3933 PROCEDURE retrieveErrorMSG (
3934 p_object_code IN fnd_lookups.lookup_code%TYPE,
3935 p_msg_name IN fnd_new_messages.message_name%TYPE,
3936 p_message IN OUT NOCOPY fnd_new_messages.message_text%TYPE
3937 ) IS
3938
3939 l_msg_token fnd_lookups.meaning%TYPE;
3940
3941 BEGIN
3942
3943 -- Retrieve the message token
3944 SELECT meaning
3945 INTO l_msg_token
3946 FROM fnd_lookups
3947 WHERE lookup_type = 'IBY_VALIDATION_FIELDS'
3948 AND lookup_code = p_object_code;
3949
3950 FND_MESSAGE.SET_NAME('IBY', p_msg_name);
3951 FND_MESSAGE.SET_TOKEN('ERR_OBJECT', l_msg_token, false);
3952 p_message := fnd_message.get;
3953
3954 EXCEPTION
3955 WHEN OTHERS THEN
3956 print_debuginfo(G_PKG_NAME || '.retrieveErrorMSG',
3957 'Fatal: Exception when retrieving a validation error message.');
3958 print_debuginfo(G_PKG_NAME || '.retrieveErrorMSG',
3959 'SQL code: ' || SQLCODE);
3960 print_debuginfo(G_PKG_NAME || '.retrieveErrorMSG',
3961 'SQL err msg: '|| SQLERRM);
3962
3963 END retrieveErrorMSG;
3964
3965 /*--------------------------------------------------------------------
3966 | NAME:
3967 | evaluateCondition
3968 |
3969 | PURPOSE:
3970 | Procedure to evaluate a specific condition for a
3971 | particular field on the basis of a token. This will
3972 | minimize code in the Validation entry point procedures.
3973 |
3974 | The possible token values are:
3975 | EQUALSTO, NOTEQUALSTO, NOTNULL, LENGTH, MAXLENGTH,
3976 | MINLENGTH, MIN, MAX, MASK, LIKE, SET, CUSTOM, ASSIGN,
3977 | TYPE.
3978 |
3979 | For token 'CUSTOM', this makes a dynamic PLSQL call to
3980 | a procedure specified in the parameter 'p_value'.
3981 |
3982 | PARAMETERS:
3983 | IN
3984 |
3985 |
3986 | OUT
3987 |
3988 |
3989 | RETURNS:
3990 |
3991 | NOTES:
3992 |
3993 *---------------------------------------------------------------------*/
3994 PROCEDURE evaluateCondition(
3995 p_fieldName IN VARCHAR2,
3996 p_fieldValue IN VARCHAR2,
3997 p_token IN VARCHAR2,
3998 p_char_value IN VARCHAR2,
3999 p_num_value IN NUMBER,
4000 x_valResult OUT NOCOPY BOOLEAN,
4001 x_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE
4002 )
4003 IS
4004
4005 l_stmt VARCHAR2(200);
4006 l_cnt NUMBER;
4007 l_chr VARCHAR2(1);
4008 l_deliv_cnt NUMBER;
4009 l_temp_num NUMBER;
4010 l_num_flag VARCHAR2(1);
4011 l_lookup_code_cnt NUMBER;
4012 l_error_msg VARCHAR2(2000);
4013 l_transaction_error_id IBY_TRANSACTION_ERRORS.transaction_error_id%TYPE;
4014
4015 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.evaluateCondition';
4016
4017 BEGIN
4018 x_valResult := TRUE;
4019 x_docErrorRec.error_message :='';
4020
4021 print_debuginfo(l_module_name, 'ENTER');
4022
4023 print_debuginfo(l_module_name, 'Evaluating '
4024 || p_fieldName || ',' || p_fieldValue || ','
4025 || p_token || ',' || p_char_value || ','
4026 || p_num_value);
4027
4028 /*
4029 * Fix for bugs 5661094 and 5663530:
4030 *
4031 * Null fields need to be handled correctly. For example,
4032 * when checking whether a value has max length of 140,
4033 * if the value is null, then it must be treated as a
4034 * success.
4035 *
4036 * Most of the validation conditions using length checks
4037 * below will automatically get skipped if the value is null.
4038 *
4039 * The only special case is numeric values. Numeric values
4040 * should be treated as failed if the value is null.
4041 *
4042 * operator data type
4043 * --------- ---------
4044 * EQUALTO - char
4045 * NOTEQUALTO - char
4046 * GRTTHAN - char
4047 * GRTEQUAL - char
4048 * LESSTHAN - char
4049 * LESSEQUAL - char
4050 * ISNULL - any
4051 * NOTNULL - any
4052 * STRIS - char
4053 * STRISNOT - char
4054 * NUMERIC - num
4055 * STARTWITH - char
4056 * NOTSTARTWITH - char
4057 * INSET - char
4058 * INLOOKUPTYPE - char
4059 * DIGITSONLY - char
4060 * NOTINSET - char
4061 * INDELIV - char
4062 * VALID_CH_ESR - char
4063 * MAXLENGTH - char
4064 * MINLENGTH - char
4065 * EXACTLENGTH - char
4066 * MASK - char
4067 * LIKE - char
4068 * CUSTOM - char
4069 *
4070 * In the first if condition below, we treat numeric values
4071 * different from the rest of the data types.
4072 */
4073
4074 --
4075 -- Applying each condition as per the token
4076 --
4077
4078 IF (p_fieldvalue IS NULL and p_token = 'NUMERIC') THEN
4079 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INVALID', l_error_msg);
4080 x_docErrorRec.error_message := l_error_msg;
4081 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INVALID';
4082 x_valResult := FALSE;
4083 ELSIF (p_token = 'MAXLENGTH' AND length(p_fieldValue) > p_num_value) THEN
4084 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INC_LENGTH', l_error_msg);
4085 x_docErrorRec.error_message := l_error_msg;
4086 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INC_LENGTH';
4087 x_valResult := FALSE;
4088 ELSIF (p_token = 'MINLENGTH' AND length(p_fieldValue) < p_num_value) THEN
4089 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INC_LENGTH', l_error_msg);
4090 x_docErrorRec.error_message := l_error_msg;
4091 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INC_LENGTH';
4092 x_valResult := FALSE;
4093 ELSIF (p_token = 'EXACTLENGTH' AND length(p_fieldValue) <> p_num_value) THEN
4094 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INC_LENGTH', l_error_msg);
4095 x_docErrorRec.error_message := l_error_msg;
4096 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INC_LENGTH';
4097 x_valResult := FALSE;
4098 ELSIF (p_token = 'EQUALTO' AND to_number(p_fieldValue) <> p_num_value) THEN
4099 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INCORRECT', l_error_msg);
4100 x_docErrorRec.error_message := l_error_msg;
4101 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INCORRECT';
4102 x_valResult := FALSE;
4103 ELSIF (p_token = 'NOTEQUALTO' AND to_number(p_fieldValue) = p_num_value) THEN
4104 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INCORRECT', l_error_msg);
4105 x_docErrorRec.error_message := l_error_msg;
4106 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INCORRECT';
4107 x_valResult := FALSE;
4108 ELSIF (p_token = 'GRTTHAN' AND
4109 to_number(p_fieldValue) <= p_num_value) THEN
4110 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INCORRECT', l_error_msg);
4111 x_docErrorRec.error_message := l_error_msg;
4112 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INCORRECT';
4113 x_valResult := FALSE;
4114 ELSIF (p_token = 'GRTEQUAL' AND
4115 to_number(p_fieldValue) < p_num_value) THEN
4116 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INCORRECT', l_error_msg);
4117 x_docErrorRec.error_message := l_error_msg;
4118 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INCORRECT';
4119 x_valResult := FALSE;
4120 ELSIF (p_token = 'LESSTHAN' AND
4121 to_number(p_fieldValue) >= p_num_value) THEN
4122 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INCORRECT', l_error_msg);
4123 x_docErrorRec.error_message := l_error_msg;
4124 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INCORRECT';
4125 x_valResult := FALSE;
4126 ELSIF (p_token = 'LESSEQUAL' AND
4127 to_number(p_fieldValue) > p_num_value) THEN
4128 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_EXCEED_MAX', l_error_msg);
4129 x_docErrorRec.error_message := l_error_msg;
4130 x_docErrorRec.error_code := 'IBY_VALID_OBJ_EXCEED_MAX';
4131 x_valResult := FALSE;
4132 ELSIF (p_token = 'ISNULL' AND p_fieldValue is not NULL) THEN
4133 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_NULL', l_error_msg);
4134 x_docErrorRec.error_message := l_error_msg;
4135 x_docErrorRec.error_code := 'IBY_VALID_OBJ_NULL';
4136 x_valResult := FALSE;
4137 ELSIF (p_token = 'NOTNULL' AND p_fieldValue is NULL) THEN
4138 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_REQUIRED', l_error_msg);
4139 x_docErrorRec.error_message := l_error_msg;
4140 x_docErrorRec.error_code := 'IBY_VALID_OBJ_REQUIRED';
4141 x_valResult := FALSE;
4142 ELSIF (p_token = 'STRIS' AND p_fieldValue <> p_char_value ) THEN
4143 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INCORRECT', l_error_msg);
4144 x_docErrorRec.error_message := l_error_msg;
4145 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INCORRECT';
4146 x_valResult := FALSE;
4147 ELSIF (p_token = 'STRISNOT' AND p_fieldValue = p_char_value ) THEN
4148 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INCORRECT', l_error_msg);
4149 x_docErrorRec.error_message := l_error_msg;
4150 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INCORRECT';
4151 x_valResult := FALSE;
4152 ELSIF (p_token = 'NUMERIC') THEN
4153 begin
4154 l_temp_num := to_number(p_fieldValue);
4155 l_num_flag := 'Y';
4156 exception
4157 when others then
4158 l_num_flag := 'N';
4159 end;
4160
4161 IF l_num_flag = 'N' THEN
4162 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_NUM_VALUE', l_error_msg);
4163 x_docErrorRec.error_message := l_error_msg;
4164 x_docErrorRec.error_code := 'IBY_VALID_OBJ_NUM_VALUE';
4165 x_valResult := FALSE;
4166 END IF;
4167 ELSIF (p_token = 'STARTWITH' AND substr(p_fieldValue,1,length(p_char_value)) <> p_char_value ) THEN
4168 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INCORRECT', l_error_msg);
4169 x_docErrorRec.error_message := l_error_msg;
4170 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INCORRECT';
4171 x_valResult := FALSE;
4172 ELSIF (p_token = 'NOTSTARTWITH' AND substr(p_fieldValue,1,length(p_char_value)) = p_char_value ) THEN
4173 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INCORRECT', l_error_msg);
4174 x_docErrorRec.error_message := l_error_msg;
4175 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INCORRECT';
4176 x_valResult := FALSE;
4177 ELSIF (p_token = 'INSET' AND instr(p_char_value, p_fieldValue) = 0) THEN
4178 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INVALID', l_error_msg);
4179 x_docErrorRec.error_message := l_error_msg;
4180 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INVALID';
4181 x_valResult := FALSE;
4182 ELSIF (p_token = 'NOTINSET' AND instr(p_char_value, p_fieldValue) <> 0) THEN
4183 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INVALID', l_error_msg);
4184 x_docErrorRec.error_message := l_error_msg;
4185 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INVALID';
4186 x_valResult := FALSE;
4187 ELSIF p_token = 'INDELIV' THEN
4188 select count(*)
4189 into l_deliv_cnt
4190 from iby_delivery_channels_vl
4191 where territory_code = p_char_value
4192 and delivery_channel_code = p_fieldValue
4193 -- and enabled_flag = 'Y'
4194 ;
4195
4196 IF l_deliv_cnt = 0 THEN
4197 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INVALID', l_error_msg);
4198 x_docErrorRec.error_message := l_error_msg;
4199 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INVALID';
4200 x_valResult := FALSE;
4201 END IF;
4202 ELSIF (p_token = 'INLOOKUPTYPE') THEN
4203 select count(*)
4204 into l_lookup_code_cnt
4205 from fnd_lookups
4206 where lookup_type = p_char_value
4207 and lookup_code = p_fieldValue;
4208
4209 IF l_lookup_code_cnt = 0 THEN
4210 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INVALID', l_error_msg);
4211 x_docErrorRec.error_message := l_error_msg;
4212 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INVALID';
4213 x_valResult := FALSE;
4214 END IF;
4215 ELSIF (p_token = 'DIGITSONLY' and translate(trim(p_fieldValue),'0123456789',' ') <>
4216 rpad(' ', length(trim(p_fieldValue)), ' ') ) THEN
4217 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_NUM_ONLY', l_error_msg);
4218 x_docErrorRec.error_message := l_error_msg;
4219 x_docErrorRec.error_code := 'IBY_VALID_OBJ_NUM_ONLY';
4220 x_valResult := FALSE;
4221 ELSIF (p_token = 'VALID_CH_ESR') THEN
4222 if not validate_CH_EST(p_fieldValue) then
4223 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INVALID', l_error_msg);
4224 x_docErrorRec.error_message := l_error_msg;
4225 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INVALID';
4226 x_valResult := FALSE;
4227 end if;
4228 ELSIF (p_token = 'MASK' AND
4229 LENGTH(TRIM(TRANSLATE(p_fieldValue,p_char_value,' ')))=0) THEN
4230 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INVALID', l_error_msg);
4231 x_docErrorRec.error_message := l_error_msg;
4232 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INVALID';
4233 x_valResult := FALSE;
4234 ELSIF (p_token = 'CUSTOM') THEN
4235 l_stmt := 'BEGIN '||p_char_value||'(:1,:2,:3); END;';
4236
4237 EXECUTE IMMEDIATE (l_stmt) USING
4238 IN p_fieldName,
4239 IN p_fieldValue,
4240 OUT l_chr;
4241
4242 IF (l_chr <> '0') THEN
4243 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INVALID', l_error_msg);
4244 x_docErrorRec.error_message := l_error_msg;
4245 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INVALID';
4246 x_valResult := FALSE;
4247 END IF;
4248 -- the following ELSE is dummy
4249 ELSE
4250 retrieveErrorMSG (p_fieldName, 'IBY_VALID_OBJ_INVALID', l_error_msg);
4251 x_docErrorRec.error_message := l_error_msg;
4252 x_docErrorRec.error_code := 'IBY_VALID_OBJ_INVALID';
4253 END IF;
4254
4255 IF NOT x_valResult THEN
4256
4257 SELECT
4258 IBY_TRANSACTION_ERRORS_S.NEXTVAL
4259 INTO
4260 l_transaction_error_id
4261 FROM
4262 DUAL
4263 ;
4264
4265 x_docErrorRec.transaction_error_id := l_transaction_error_id;
4266
4267 INSERT INTO IBY_TRXN_ERROR_TOKENS
4268 (TRANSACTION_ERROR_ID, TOKEN_NAME, TOKEN_VALUE, LOOKUP_TYPE_SOURCE,
4269 CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
4270 LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER)
4271 VALUES
4272 (l_transaction_error_id, 'ERR_OBJECT', p_fieldName, 'IBY_VALIDATION_FIELDS',
4273 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
4274 fnd_global.user_id, 1);
4275
4276 END IF;
4277
4278 print_debuginfo(l_module_name, 'EXIT');
4279
4280 RETURN;
4281
4282 EXCEPTION
4283 WHEN OTHERS THEN
4284 FND_MESSAGE.SET_NAME('IBY', '');
4285 FND_MESSAGE.SET_TOKEN('SQLERR','evaluateCondition : '||
4286 substr(SQLERRM, 1, 300));
4287 FND_MSG_PUB.Add;
4288 print_debuginfo(l_module_name,' '||
4289 substr(SQLERRM, 1, 300), FND_LOG.LEVEL_UNEXPECTED);
4290 RAISE g_abort_program;
4291
4292 END evaluateCondition;
4293
4294 -- Utility procedures
4295 /*--------------------------------------------------------------------
4296 | NAME:
4297 | getParamValue
4298 |
4299 | PURPOSE:
4300 |
4301 |
4302 | PARAMETERS:
4303 | IN
4304 |
4305 |
4306 | OUT
4307 |
4308 |
4309 | RETURNS:
4310 |
4311 | NOTES:
4312 |
4313 *---------------------------------------------------------------------*/
4314 PROCEDURE getParamValue (
4315 p_validation_assign_id IN IBY_VAL_ASSIGNMENTS.
4316 validation_assignment_id%TYPE,
4317 p_validation_set_code IN IBY_VALIDATION_SETS_VL.
4318 validation_set_code%TYPE,
4319 p_validation_param_code IN IBY_VALIDATION_PARAMS_B.
4320 validation_parameter_code%TYPE,
4321 p_value OUT NOCOPY VARCHAR2)
4322
4323 IS
4324
4325 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.getParamValue';
4326
4327 BEGIN
4328
4329 select decode(vp.validation_parameter_type,
4330 'VARCHAR2', val_param_varchar2_value,
4331 'NUMBER', val_param_number_value,
4332 'DATE', val_param_date_value)
4333 into p_value
4334 from iby_val_assignments va,
4335 iby_validation_values vv,
4336 iby_validation_params_vl vp
4337 where va.validation_set_code = p_validation_set_code
4338 and va.validation_assignment_id = p_validation_assign_id
4339 and va.validation_set_code = vv.validation_set_code
4340 and va.validation_assignment_id = vv.validation_assignment_id
4341 and vv.validation_parameter_code = p_validation_param_code
4342 and vp.validation_set_code = va.validation_set_code
4343 and vp.validation_parameter_code = vv.validation_parameter_code;
4344
4345 /*
4346 * Fix for bug 5262536:
4347 *
4348 * The code below should only be applicable to field names.
4349 * Otherwise, it nulls out the limit value attached to
4350 * a validation set.
4351 *
4352 * Therefore, add an if condition for the block below
4353 * checking for the validation param code P_FIELD_NAME.
4354 */
4355 IF (p_validation_param_code = 'P_FIELD_NAME') THEN
4356 if substr(p_validation_set_code,1,8) = 'RULE_INS' and
4357 substr(p_value,1,4) <> 'INS_' then
4358 p_value := null;
4359 elsif substr(p_validation_set_code,1,8) = 'RULE_PMT' and
4360 substr(p_value,1,4) <> 'PMT_' then
4361 p_value := null;
4362 elsif substr(p_validation_set_code,1,8) = 'RULE_DOC' and
4363 substr(p_value,1,4) in ('INS_', 'PMT_') then
4364 p_value := null;
4365 end if;
4366 END IF;
4367
4368 EXCEPTION
4369 WHEN OTHERS THEN
4370 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
4371 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
4372 p_value := null;
4373
4374 END getParamValue;
4375
4376 /*--------------------------------------------------------------------
4377 | NAME:
4378 | getDocumentFieldValue
4379 |
4380 | PURPOSE:
4381 |
4382 |
4383 | PARAMETERS:
4384 | IN
4385 |
4386 |
4387 | OUT
4388 |
4389 |
4390 | RETURNS:
4391 |
4392 | NOTES:
4393 |
4394 *---------------------------------------------------------------------*/
4395 PROCEDURE getDocumentFieldValue (
4396 p_field_name IN VARCHAR2,
4397 p_document_rec IN documentRecType,
4398 p_field_value OUT NOCOPY VARCHAR2)
4399 IS
4400
4401 BEGIN
4402 if p_field_name = 'DOCUMENT_ID' then
4403 p_field_value := p_document_rec.document_id;
4404 elsif p_field_name = 'DOCUMENT_AMOUNT' then
4405 p_field_value := p_document_rec.document_amount;
4406 elsif p_field_name = 'DOCUMENT_PAY_CURRENCY' then
4407 p_field_value := p_document_rec.document_pay_currency;
4408 elsif p_field_name = 'EXCLUSIVE_PAYMENT_FLAG' then
4409 p_field_value := p_document_rec.exclusive_payment_flag;
4410 elsif p_field_name = 'DELIVERY_CHANNEL_CODE' then
4411 p_field_value := p_document_rec.delivery_channel_code;
4412 elsif p_field_name = 'UNIQUE_REMIT_ID_CODE' then
4413 p_field_value := p_document_rec.unique_remit_id_code;
4414 elsif p_field_name = 'INT_BANK_NUM' then
4415 p_field_value := p_document_rec.int_bank_num;
4416 elsif p_field_name = 'INT_BANK_NAME' then
4417 p_field_value := p_document_rec.int_bank_name;
4418 elsif p_field_name = 'INT_BANK_NAME_ALT' then
4419 p_field_value := p_document_rec.int_bank_name_alt;
4420 elsif p_field_name = 'INT_BANK_BRANCH_NUM' then
4421 p_field_value := p_document_rec.int_bank_branch_num;
4422 elsif p_field_name = 'INT_BANK_BRANCH_NAME' then
4423 p_field_value := p_document_rec.int_bank_branch_name;
4424 elsif p_field_name = 'INT_BANK_BRANCH_NAME_ALT' then
4425 p_field_value := p_document_rec.int_bank_branch_name_alt;
4426 elsif p_field_name = 'INT_BANK_ACC_NUM' then
4427 p_field_value := p_document_rec.int_bank_acc_num;
4428 elsif p_field_name = 'INT_BANK_ACC_NAME' then
4429 p_field_value := p_document_rec.int_bank_acc_name;
4430 elsif p_field_name = 'INT_BANK_ACC_NAME_ALT' then
4431 p_field_value := p_document_rec.int_bank_acc_name_alt;
4432 elsif p_field_name = 'INT_BANK_ACC_TYPE' then
4433 p_field_value := p_document_rec.int_bank_acc_type;
4434 elsif p_field_name = 'INT_BANK_ACC_IBAN' then
4435 p_field_value := p_document_rec.int_bank_acc_iban;
4436 elsif p_field_name = 'INT_BANK_ASSIGNED_ID1' then
4437 p_field_value := p_document_rec.int_bank_assigned_id1;
4438 elsif p_field_name = 'INT_BANK_ASSIGNED_ID2' then
4439 p_field_value := p_document_rec.int_bank_assigned_id2;
4440 elsif p_field_name = 'INT_EFT_USER_NUMBER' then
4441 p_field_value := p_document_rec.int_eft_user_number;
4442 elsif p_field_name = 'PAYER_LE_NAME' then
4443 p_field_value := p_document_rec.payer_le_name;
4444 elsif p_field_name = 'PAYER_LE_COUNTRY' then
4445 p_field_value := p_document_rec.payer_le_country;
4446 elsif p_field_name = 'PAYER_PHONE' then
4447 p_field_value := p_document_rec.payer_phone;
4448 elsif p_field_name = 'EXT_BANK_NUM' then
4449 p_field_value := p_document_rec.ext_bank_num;
4450 elsif p_field_name = 'EXT_BANK_NAME' then
4451 p_field_value := p_document_rec.ext_bank_name;
4452 elsif p_field_name = 'EXT_BANK_NAME_ALT' then
4453 p_field_value := p_document_rec.ext_bank_name_alt;
4454 elsif p_field_name = 'EXT_BANK_BRANCH_NUM' then
4455 p_field_value := p_document_rec.ext_bank_branch_num;
4456 elsif p_field_name = 'EXT_BANK_BRANCH_NAME' then
4457 p_field_value := p_document_rec.ext_bank_branch_name;
4458 elsif p_field_name = 'EXT_BANK_BRANCH_NAME_ALT' then
4459 p_field_value := p_document_rec.ext_bank_branch_name_alt;
4460 elsif p_field_name = 'EXT_BANK_COUNTRY' then
4461 p_field_value := p_document_rec.ext_bank_country;
4462 elsif p_field_name = 'EXT_BANK_BRANCH_COUNTRY' then
4463 p_field_value := p_document_rec.ext_bank_branch_country;
4464 elsif p_field_name = 'EXT_BANK_BRANCH_ADDR1' then
4465 p_field_value := p_document_rec.ext_bank_branch_addr1;
4466 elsif p_field_name = 'EXT_BANK_ACC_NUM' then
4467 p_field_value := p_document_rec.ext_bank_acc_num;
4468 elsif p_field_name = 'EXT_BANK_ACC_NAME' then
4469 p_field_value := p_document_rec.ext_bank_acc_name;
4470 elsif p_field_name = 'EXT_BANK_ACC_NAME_ALT' then
4471 p_field_value := p_document_rec.ext_bank_acc_name_alt;
4472 elsif p_field_name = 'EXT_BANK_ACC_TYPE' then
4473 p_field_value := p_document_rec.ext_bank_acc_type;
4474 elsif p_field_name = 'EXT_BANK_ACC_IBAN' then
4475 p_field_value := p_document_rec.ext_bank_acc_iban;
4476 elsif p_field_name = 'EXT_BANK_ACC_CHK_DGTS' then
4477 p_field_value := p_document_rec.ext_bank_acc_chk_dgts;
4478 elsif p_field_name = 'PAYEE_PARTY_NAME' then
4479 p_field_value := p_document_rec.payee_party_name;
4480 elsif p_field_name = 'PAYEE_PARTY_SITE_ADDR1' then
4481 p_field_value := p_document_rec.payee_party_addr1;
4482
4483 /*
4484 * Update by Ramesh:
4485 *
4486 * Change some of the payee address related field names
4487 * because of the way the names are seeded in
4488 * the IBY_VALIDATION_FIELDS lookup.
4489 *
4490 * For example, here the field name is PAYEE_PARTY_CITY
4491 * but in the lookup, it is seeded as PAYEE_PARTY_SITE_CITY.
4492 *
4493 * Because of the mismatch, the field value is returned as
4494 * null and the validation always fails. It is simpler
4495 * to rename the field names here that in the lookup.
4496 *
4497 * Hence changing the payee addredd related field names
4498 * here.
4499 */
4500 elsif p_field_name = 'PAYEE_PARTY_SITE_CITY' then
4501 p_field_value := p_document_rec.payee_party_city;
4502 elsif p_field_name = 'PAYEE_PARTY_SITE_POSTAL' then
4503 p_field_value := p_document_rec. payee_party_postal;
4504 elsif p_field_name = 'PAYEE_PARTY_SITE_COUNTRY' then
4505 p_field_value := p_document_rec.payee_party_country;
4506 elsif p_field_name = 'BANK_CHARGE_BEARER' then
4507 p_field_value := p_document_rec.bank_charge_bearer;
4508 elsif p_field_name = 'PAYMENT_REASON_CODE' then
4509 p_field_value := p_document_rec.payment_reason_code;
4510 elsif p_field_name = 'PAYMENT_METHOD_ID' then
4511 p_field_value := p_document_rec.payment_method_cd;
4512 elsif p_field_name = 'PAYMENT_FORMAT_ID' then
4513 p_field_value := p_document_rec.payment_format_cd;
4514 elsif p_field_name = 'PAYMENT_REASON_COMMENTS' then
4515 p_field_value := p_document_rec.PAYMENT_REASON_COMMENTS;
4516 elsif p_field_name = 'SETTLEMENT_PRIORITY' then
4517 p_field_value := p_document_rec.SETTLEMENT_PRIORITY;
4518 elsif p_field_name = 'REMITTANCE_MESSAGE1' then
4519 p_field_value := p_document_rec.REMITTANCE_MESSAGE1;
4520 elsif p_field_name = 'REMITTANCE_MESSAGE2' then
4521 p_field_value := p_document_rec.REMITTANCE_MESSAGE2;
4522 elsif p_field_name = 'REMITTANCE_MESSAGE3' then
4523 p_field_value := p_document_rec.REMITTANCE_MESSAGE3;
4524 elsif p_field_name = 'URI_CHECK_DIGIT' then
4525 p_field_value := p_document_rec.URI_CHECK_DIGIT;
4526
4527 /*
4528 * Updated by sodash
4529 * for Payee BIC validation
4530 */
4531 elsif p_field_name = 'EXT_EFT_SWIFT_CODE' then
4532 p_field_value := p_document_rec.ext_eft_swift_code;
4533
4534 else
4535 null;
4536 end if;
4537
4538 END getDocumentFieldValue;
4539
4540 /*--------------------------------------------------------------------
4541 | NAME:
4542 | getPaymentFieldValue
4543 |
4544 | PURPOSE:
4545 |
4546 |
4547 | PARAMETERS:
4548 | IN
4549 |
4550 |
4551 | OUT
4552 |
4553 |
4554 | RETURNS:
4555 |
4556 | NOTES:
4557 |
4558 *---------------------------------------------------------------------*/
4559 PROCEDURE getPaymentFieldValue (
4560 p_field_name IN VARCHAR2,
4561 p_payment_rec IN paymentRecType,
4562 p_field_value OUT NOCOPY VARCHAR2)
4563 IS
4564
4565 BEGIN
4566 if p_field_name = 'PMT_ID' then
4567 p_field_value := p_payment_rec.pmt_id;
4568 elsif p_field_name = 'PMT_AMOUNT' then
4569 p_field_value := p_payment_rec.pmt_amount;
4570 elsif p_field_name = 'PMT_CURRENCY' then
4571 p_field_value := p_payment_rec.pmt_currency;
4572 elsif p_field_name = 'PMT_DETAIL' then
4573 p_field_value := p_payment_rec.pmt_detail;
4574 elsif p_field_name = 'PMT_DELIVERY_CHANNEL_CODE' then
4575 p_field_value := p_payment_rec.pmt_delivery_channel_code;
4576 elsif p_field_name = 'PMT_PAYER_LE_COUNTRY' then
4577 p_field_value := p_payment_rec.pmt_payer_le_country;
4578 elsif p_field_name = 'PMT_PAYMENT_REASON_COUNT' then
4579 p_field_value := p_payment_rec.pmt_payment_reason_count;
4580
4581 /*
4582 * Updated by sodash
4583 * Payer IBAN and Payer Address Validations
4584 */
4585 elsif p_field_name = 'INT_BANK_ACC_IBAN' then
4586 p_field_value := p_payment_rec.int_bank_account_iban;
4587 elsif p_field_name = 'PARTY_ADDRESS_LINE1' then
4588 p_field_value := p_payment_rec.party_address_line1;
4589 elsif p_field_name = 'PARTY_ADDRESS_CITY' then
4590 p_field_value := p_payment_rec.party_address_city;
4591 elsif p_field_name = 'PARTY_ADDRESS_POSTAL_CODE' then
4592 p_field_value := p_payment_rec.party_address_postal_code;
4593
4594 else
4595 null;
4596 end if;
4597
4598 END getPaymentFieldValue;
4599
4600 /*--------------------------------------------------------------------
4601 | NAME:
4602 | getInstructionFieldValue
4603 |
4604 | PURPOSE:
4605 |
4606 |
4607 | PARAMETERS:
4608 | IN
4609 |
4610 |
4611 | OUT
4612 |
4613 |
4614 | RETURNS:
4615 |
4616 | NOTES:
4617 |
4618 *---------------------------------------------------------------------*/
4619 PROCEDURE getInstructionFieldValue (
4620 p_field_name IN VARCHAR2,
4621 p_instruction_rec IN instructionRecType,
4622 p_field_value OUT NOCOPY VARCHAR2)
4623 IS
4624
4625 BEGIN
4626 if p_field_name = 'INS_ID' then
4627 p_field_value := p_instruction_rec.ins_id;
4628 elsif p_field_name = 'INS_AMOUNT' then
4629 p_field_value := p_instruction_rec.ins_amount;
4630 elsif p_field_name = 'INS_DOCUMENT_COUNT' then
4631 p_field_value := p_instruction_rec.ins_document_count;
4632 else
4633 null;
4634 end if;
4635
4636 END getInstructionFieldValue;
4637
4638 /*--------------------------------------------------------------------
4639 | NAME:
4640 | getRequestAttributes
4641 |
4642 | PURPOSE:
4643 | Gets the calling app payment service request id, and the
4644 | calling app id for the given payment service request.
4645 |
4646 | PARAMETERS:
4647 | IN
4648 |
4649 | OUT
4650 |
4651 |
4652 | RETURNS:
4653 |
4654 | NOTES:
4655 |
4656 *---------------------------------------------------------------------*/
4657 PROCEDURE getRequestAttributes(
4658 p_payReqId IN IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE,
4659 x_caPayReqCd IN OUT NOCOPY
4660 IBY_PAY_SERVICE_REQUESTS.call_app_pay_service_req_code%TYPE,
4661 x_caId IN OUT NOCOPY
4662 IBY_PAY_SERVICE_REQUESTS.calling_app_id%TYPE
4663 )
4664 IS
4665 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.getRequestAttributes';
4666 BEGIN
4667
4668 SELECT
4669 call_app_pay_service_req_code,
4670 calling_app_id
4671 INTO
4672 x_caPayReqCd,
4673 x_caId
4674 FROM
4675 IBY_PAY_SERVICE_REQUESTS
4676 WHERE
4677 payment_service_request_id = p_payReqId;
4678
4679 END getRequestAttributes;
4680
4681 /*--------------------------------------------------------------------
4682 | NAME:
4683 | raiseBizEvents
4684 |
4685 | PURPOSE:
4686 | Raises business events to inform the calling app of a change
4687 | in document/payment request status.
4688 |
4689 | The payload for the business event will be an XML clob that
4690 | contains a list of failed documents (or the payment request id
4691 | if the entire request has failed).
4692 |
4693 | PARAMETERS:
4694 | IN
4695 |
4696 | OUT
4697 |
4698 |
4699 | RETURNS:
4700 |
4701 | NOTES:
4702 |
4703 *---------------------------------------------------------------------*/
4704 PROCEDURE raiseBizEvents(
4705 p_payreq_id IN VARCHAR2,
4706 p_cap_payreq_id IN VARCHAR2,
4707 p_cap_id IN NUMBER,
4708 x_allDocsSuccessFlag IN OUT NOCOPY BOOLEAN,
4709 p_rejectionLevel IN VARCHAR2
4710 )
4711 IS
4712
4713 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.raiseBizEvents';
4714 l_xml_clob CLOB;
4715 l_event_name VARCHAR2(200);
4716 l_event_key NUMBER;
4717 l_param_names JTF_VARCHAR2_TABLE_300;
4718 l_param_vals JTF_VARCHAR2_TABLE_300;
4719
4720 l_rej_doc_id_list IBY_DISBURSE_UI_API_PUB_PKG.docPayIDTab;
4721 l_rej_doc_status_list IBY_DISBURSE_UI_API_PUB_PKG.docPayStatusTab;
4722
4723 l_return_status VARCHAR2(500);
4724
4725 BEGIN
4726
4727 print_debuginfo(l_module_name, 'ENTER');
4728
4729 print_debuginfo(l_module_name, 'Payreq id: '
4730 || p_payreq_id);
4731
4732 /*
4733 * Get the rejection level system option
4734 */
4735 print_debuginfo(l_module_name, 'Rejection level system option: '
4736 || p_rejectionLevel);
4737
4738 /*
4739 * These tables are used to pass event keys
4740 * to the business event.
4741 */
4742 l_param_names := JTF_VARCHAR2_TABLE_300();
4743 l_param_vals := JTF_VARCHAR2_TABLE_300();
4744
4745 /*
4746 * The event key uniquely identifies a specific
4747 * occurance of an event. Therefore, it should be
4748 * a sequence number.
4749 */
4750 SELECT IBY_EVENT_KEY_S.nextval INTO l_event_key
4751 FROM DUAL;
4752
4753 IF (p_rejectionLevel = REJ_LVL_REQUEST) THEN
4754
4755 /*
4756 * For request level rejections, even if one
4757 * payment within the request fails, then the
4758 * entire payment request should be failed.
4759 */
4760 IF (x_allDocsSuccessFlag <> TRUE) THEN
4761
4762 /*
4763 * Invoke the callout API with the payment request id.
4764 * This API should trigger the calling application to
4765 * fail the payment request and all it's associated
4766 * docs.
4767 */
4768 print_debuginfo(l_module_name, 'Going to invoke API call '
4769 || 'remove_payment_request()');
4770
4771 /*
4772 * Invoke API to inform calling application
4773 * about the rejected payment request. This API
4774 * will remove all the documents payable in this
4775 * payment request from the processing cycle and
4776 * inform the calling application about this fact.
4777 */
4778 IBY_DISBURSE_UI_API_PUB_PKG.remove_payment_request (
4779 p_payreq_id,
4780 l_return_status
4781 );
4782
4783 print_debuginfo(l_module_name, 'Return status of '
4784 || 'remove_payment_request() API call: '
4785 || l_return_status,
4786 FND_LOG.LEVEL_UNEXPECTED
4787 );
4788
4789 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4790
4791 print_debuginfo(l_module_name, 'API call did not succeed. '
4792 || 'Aborting build program .. ',
4793 FND_LOG.LEVEL_UNEXPECTED
4794 );
4795
4796 APP_EXCEPTION.RAISE_EXCEPTION;
4797
4798 END IF;
4799
4800 ELSE
4801
4802 print_debuginfo(l_module_name, 'Not invoking API. '
4803 || ' Reason: All documents were valid.'
4804 );
4805
4806 END IF;
4807
4808 ELSIF (p_rejectionLevel = REJ_LVL_DOCUMENT OR
4809 p_rejectionLevel = REJ_LVL_PAYEE) THEN
4810 /*
4811 * Invoke an API call with the list of failed
4812 * documents. This API call should trigger the
4813 * calling app to fail these docs.
4814 */
4815
4816 /*
4817 * Select all docs that:
4818 * 1. Have the given pay req id
4819 * 2. Are not in 'validated' status
4820 */
4821 getRejectedDocs(p_payreq_id, l_rej_doc_id_list,
4822 l_rej_doc_status_list);
4823
4824 IF (l_rej_doc_id_list.COUNT = 0) THEN
4825
4826 print_debuginfo(l_module_name, 'Not invoking API '
4827 || ' because all documents were '
4828 || ' successfully validated. So no failed documents '
4829 || ' to notify.'
4830 );
4831
4832 ELSE
4833
4834 print_debuginfo(l_module_name, 'Printing list of failed '
4835 || 'documents');
4836
4837 FOR i IN l_rej_doc_id_list.FIRST .. l_rej_doc_id_list.LAST LOOP
4838
4839 print_debuginfo(l_module_name, 'Doc id: '
4840 || l_rej_doc_id_list(i)
4841 || ', doc status: '
4842 || l_rej_doc_status_list(i)
4843 );
4844
4845 END LOOP;
4846
4847 /*
4848 * Invoke API to inform calling application
4849 * about the rejected documents.
4850 */
4851 IBY_DISBURSE_UI_API_PUB_PKG.remove_documents_payable (
4852 l_rej_doc_id_list,
4853 l_rej_doc_status_list,
4854 l_return_status
4855 );
4856
4857 print_debuginfo(l_module_name, 'Return status of '
4858 || 'remove_documents_payable() API call: '
4859 || l_return_status,
4860 FND_LOG.LEVEL_UNEXPECTED
4861 );
4862
4863 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4864
4865 print_debuginfo(l_module_name, 'API call did not succeed. '
4866 || 'Aborting build program .. ',
4867 FND_LOG.LEVEL_UNEXPECTED
4868 );
4869
4870 APP_EXCEPTION.RAISE_EXCEPTION;
4871
4872 END IF;
4873
4874 END IF;
4875
4876 ELSIF (p_rejectionLevel = REJ_LVL_NONE) THEN
4877
4878 /*
4879 * Rejection level NONE means that manual intervention
4880 * is required in case any documents in the payment
4881 * service request have failed.
4882 *
4883 * Therefore, raise a business event for this rejection level
4884 * only if at least one document payable has failed in the
4885 * request.
4886 */
4887
4888 l_event_name :=
4889 'oracle.apps.iby.buildprogram.validation.notify_user_error';
4890
4891 IF (x_allDocsSuccessFlag <> TRUE) THEN
4892
4893 /*
4894 * Raise a business event with the payment request id.
4895 * This business event should inform the user that
4896 * at least some documents payable have failed. This
4897 * should cause the user to manually review the failed
4898 * documents via the UI and take corrective action.
4899 */
4900 l_param_names.EXTEND;
4901 l_param_vals.EXTEND;
4902 l_param_names(1) := 'calling_app_id';
4903 l_param_vals(1) := p_cap_id;
4904
4905 l_param_names.EXTEND;
4906 l_param_vals.EXTEND;
4907 l_param_names(1) := 'pay_service_request_id';
4908 l_param_vals(1) := p_cap_payreq_id;
4909
4910 print_debuginfo(l_module_name, 'Going to raise biz event '
4911 || l_event_name);
4912
4913 iby_workflow_pvt.raise_biz_event(l_event_name, l_event_key,
4914 l_param_names, l_param_vals);
4915
4916 print_debuginfo(l_module_name, 'Raised biz event '
4917 || l_event_name || ' with key '
4918 || l_event_key || '.');
4919
4920 ELSE
4921
4922 print_debuginfo(l_module_name, 'Not raising biz event '
4923 || l_event_name || '. Reason: All documents '
4924 || 'were valid.');
4925
4926 END IF;
4927
4928 ELSE
4929
4930 print_debuginfo(l_module_name, 'Unknown rejection level: '
4931 || p_rejectionLevel
4932 || '. Aborting payment creation ..',
4933 FND_LOG.LEVEL_UNEXPECTED
4934 );
4935
4936 APP_EXCEPTION.RAISE_EXCEPTION;
4937
4938 END IF;
4939
4940 print_debuginfo(l_module_name, 'EXIT');
4941
4942 EXCEPTION
4943 WHEN OTHERS THEN
4944 print_debuginfo(l_module_name, 'Fatal: Exception when attempting '
4945 || 'to raise business event.', FND_LOG.LEVEL_UNEXPECTED);
4946 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
4947 FND_LOG.LEVEL_UNEXPECTED);
4948 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
4949 FND_LOG.LEVEL_UNEXPECTED);
4950
4951 /*
4952 * Propogate exception to caller.
4953 */
4954 RAISE;
4955
4956 END raiseBizEvents;
4957
4958 /*--------------------------------------------------------------------
4959 | NAME:
4960 | failRelatedDocs
4961 |
4962 | PURPOSE:
4963 | Fail all documents related to an already rejected document.
4964 | Documents are related to each other by the 'payment grouping number'
4965 | field.
4966 |
4967 | PARAMETERS:
4968 | IN
4969 | p_allDocsTab List of all documents within this payment request
4970 |
4971 | IN/OUT
4972 | x_failedDocsTab List of documents from this payment request that
4973 | have failed validation. When this procedure
4974 | completes, all documents that are related to
4975 | the documents in this list will also be marked
4976 | as failed and added to this list
4977 |
4978 | RETURNS:
4979 |
4980 | NOTES:
4981 |
4982 *---------------------------------------------------------------------*/
4983 PROCEDURE failRelatedDocs(
4984 p_allDocsTab IN docPayTabType,
4985 x_failedDocsTab IN OUT NOCOPY docStatusTabType,
4986 x_docErrorTab IN OUT NOCOPY docErrorTabType,
4987 x_errTokenTab IN OUT NOCOPY trxnErrTokenTabType
4988 )
4989 IS
4990
4991 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
4992 '.failRelatedDocs';
4993
4994 l_newlyFailedDocsTab docStatusTabType;
4995 l_invalidDocRec docStatusRecType;
4996 --l_doc_err_rec docErrorRecType;
4997 l_doc_err_rec IBY_TRANSACTION_ERRORS%ROWTYPE;
4998
4999 TYPE pmtGrpNumTabType is TABLE OF
5000 IBY_DOCS_PAYABLE_ALL.payment_grouping_number%TYPE
5001 INDEX BY BINARY_INTEGER;
5002
5003 l_storedPmtGrpNumTab pmtGrpNumTabType;
5004 l_already_processed_flag BOOLEAN := FALSE;
5005 l_already_failed_flag BOOLEAN := FALSE;
5006
5007 BEGIN
5008
5009 print_debuginfo(l_module_name, 'ENTER');
5010
5011 FOR i in x_failedDocsTab.FIRST .. x_failedDocsTab.LAST LOOP
5012
5013 IF (x_failedDocsTab(i).doc_status <> DOC_STATUS_RELN_FAIL) THEN
5014
5015 /*
5016 * First check, if we have already processed this payment
5017 * grouping number. If yes, then skip to the next document.
5018 */
5019 l_already_processed_flag := FALSE;
5020 IF (l_storedPmtGrpNumTab.COUNT <> 0) THEN
5021 FOR k in l_storedPmtGrpNumTab.FIRST .. l_storedPmtGrpNumTab.LAST
5022 LOOP
5023
5024 IF (x_failedDocsTab(i).pmt_grp_num =
5025 l_storedPmtGrpNumTab(k)) THEN
5026 l_already_processed_flag := TRUE;
5027 EXIT;
5028 END IF;
5029
5030 END LOOP;
5031 END IF;
5032
5033 IF (l_already_processed_flag <> TRUE) THEN
5034
5035 /*
5036 * Loop through all the documents for this request,
5037 * failing any documents that have the same orig
5038 * doc id of the failed doc.
5039 */
5040 FOR j in p_allDocsTab.FIRST .. p_allDocsTab.LAST LOOP
5041
5042 IF (x_failedDocsTab(i).doc_id <> p_allDocsTab(j).doc_id)
5043 THEN
5044
5045 IF (x_failedDocsTab(i).pmt_grp_num =
5046 p_allDocsTab(j).pmt_grp_num) THEN
5047
5048 /*
5049 * Check if this doc has already failed and
5050 * been stored in the error docs list. If
5051 * it has already been failed, skip it.
5052 */
5053 l_already_failed_flag :=
5054 checkIfDocFailed(p_allDocsTab(j).doc_id,
5055 x_failedDocsTab);
5056
5057 IF (l_already_failed_flag = FALSE) THEN
5058
5059 /*
5060 * Add this document to list of docs
5061 * failed by relation
5062 */
5063 l_invalidDocRec.doc_id
5064 := p_allDocsTab(j).doc_id;
5065 l_invalidDocRec.pmt_grp_num :=
5066 p_allDocsTab(j).pmt_grp_num;
5067 l_invalidDocRec.payee_id :=
5068 p_allDocsTab(j).payee_id;
5069 l_invalidDocRec.doc_status :=
5070 DOC_STATUS_RELN_FAIL;
5071
5072 l_newlyFailedDocsTab(
5073 l_newlyFailedDocsTab.COUNT + 1) :=
5074 l_invalidDocRec;
5075
5076 print_debuginfo(l_module_name, 'Cascaded doc '
5077 || 'failure: '
5078 || x_failedDocsTab(i).doc_id
5079 || ' -> '
5080 || p_allDocsTab(j).doc_id
5081 || '. Related by '
5082 || x_failedDocsTab(i).pmt_grp_num
5083 || '.'
5084 );
5085
5086 /*
5087 * Once we fail a doc, we must add a
5088 * corresponding error message to the
5089 * error table.
5090 */
5091 IBY_BUILD_UTILS_PKG.createErrorRecord(
5092 TRXN_TYPE_DOC,
5093 p_allDocsTab(j).doc_id,
5094 l_invalidDocRec.doc_status,
5095 p_allDocsTab(j).ca_id,
5096 p_allDocsTab(j).ca_doc_id1,
5097 p_allDocsTab(j).ca_doc_id2,
5098 p_allDocsTab(j).ca_doc_id3,
5099 p_allDocsTab(j).ca_doc_id4,
5100 p_allDocsTab(j).ca_doc_id5,
5101 p_allDocsTab(j).pp_tt_cd,
5102 l_doc_err_rec,
5103 x_errTokenTab,
5104 x_failedDocsTab(i).doc_id
5105 );
5106
5107 insertIntoErrorTable(l_doc_err_rec,
5108 x_docErrorTab, x_errTokenTab);
5109
5110 END IF; -- not already failed
5111
5112 END IF;
5113
5114 END IF;
5115
5116 END LOOP; -- for each doc in request
5117
5118 /*
5119 * Once we have processed all documents which have this
5120 * orig doc id, we must not re-process them if there is
5121 * another error doc with the same orig doc id.
5122 *
5123 * Therefore, store the processed orig doc id. We will
5124 * skip any future error docs that have the same orig
5125 * doc id.
5126 */
5127 l_storedPmtGrpNumTab(l_storedPmtGrpNumTab.COUNT + 1) :=
5128 x_failedDocsTab(i).pmt_grp_num;
5129
5130 END IF; -- not already processed
5131
5132 END IF;
5133
5134 END LOOP; -- for all failed documents
5135
5136 print_debuginfo(l_module_name, 'Total # related docs failed: '
5137 || l_newlyFailedDocsTab.COUNT);
5138
5139 /*
5140 * Copy all the newly failed documents back into the
5141 * original failed documents table. The original failed
5142 * docs table will be used to update the database.
5143 */
5144 IF (l_newlyFailedDocsTab.COUNT <> 0) THEN
5145 FOR i in l_newlyFailedDocsTab.FIRST .. l_newlyFailedDocsTab.LAST LOOP
5146 x_failedDocsTab(x_failedDocsTab.COUNT + 1) :=
5147 l_newlyFailedDocsTab(i);
5148 END LOOP;
5149 END IF;
5150
5151 print_debuginfo(l_module_name, 'EXIT');
5152
5153 END failRelatedDocs;
5154
5155 /*--------------------------------------------------------------------
5156 | NAME:
5157 | failAllDocsForPayee
5158 |
5159 | PURPOSE:
5160 | Fail all documents that have the same payee id of an already
5161 | rejected document. This is needed for 'payee level rejections'.
5162 |
5163 |
5164 | PARAMETERS:
5165 | IN
5166 | p_allDocsTab List of all documents within this payment request
5167 |
5168 | IN/OUT
5169 | x_failedDocsTab List of documents from this payment request that
5170 | have failed validation. When this procedure
5171 | completes, all documents that are related to
5172 | the documents in this list by payee id will also
5173 | be marked as failed and added to this list
5174 |
5175 | RETURNS:
5176 |
5177 | NOTES:
5178 |
5179 *---------------------------------------------------------------------*/
5180 PROCEDURE failAllDocsForPayee(
5181 p_allDocsTab IN docPayTabType,
5182 x_failedDocsTab IN OUT NOCOPY docStatusTabType,
5183 x_docErrorTab IN OUT NOCOPY docErrorTabType,
5184 x_errTokenTab IN OUT NOCOPY trxnErrTokenTabType
5185 )
5186 IS
5187
5188 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
5189 '.failAllDocsForPayee';
5190
5191 l_newlyFailedDocsTab docStatusTabType;
5192 l_invalidDocRec docStatusRecType;
5193 l_doc_err_rec IBY_TRANSACTION_ERRORS%ROWTYPE;
5194 l_doc_token_tab trxnErrTokenTabType;
5195
5196 TYPE payeeTabType is TABLE OF
5197 IBY_DOCS_PAYABLE_ALL.ext_payee_id%TYPE
5198 INDEX BY BINARY_INTEGER;
5199
5200 l_storedPayeesTab payeeTabType;
5201 l_already_processed_flag BOOLEAN := FALSE;
5202 l_already_failed_flag BOOLEAN := FALSE;
5203 l_print_var VARCHAR2(1) := '';
5204
5205 BEGIN
5206
5207 print_debuginfo(l_module_name, 'ENTER');
5208
5209 IF (x_failedDocsTab.COUNT = 0) THEN
5210
5211 print_debuginfo(l_module_name, 'Exiting because error docs list '
5212 || 'is empty');
5213
5214 print_debuginfo(l_module_name, 'EXIT');
5215 RETURN;
5216
5217 END IF;
5218
5219
5220 FOR i in x_failedDocsTab.FIRST .. x_failedDocsTab.LAST LOOP
5221
5222 IF (x_failedDocsTab(i).doc_status <> DOC_STATUS_FAIL_BY_REJLVL) THEN
5223
5224 /*
5225 * First check, if we have already processed this payee
5226 * id. If yes, then skip to the next document.
5227 */
5228 l_already_processed_flag := FALSE;
5229 IF (l_storedPayeesTab.COUNT <> 0) THEN
5230
5231 FOR k in l_storedPayeesTab.FIRST .. l_storedPayeesTab.LAST
5232 LOOP
5233
5234 IF (x_failedDocsTab(i).payee_id =
5235 l_storedPayeesTab(k)) THEN
5236 l_already_processed_flag := TRUE;
5237 EXIT;
5238 END IF;
5239
5240 END LOOP;
5241
5242 END IF;
5243
5244 IF (l_already_processed_flag <> TRUE) THEN
5245
5246 /*
5247 * Loop through all the documents for this request,
5248 * failing any documents that have the same payee id
5249 * as the failed doc.
5250 */
5251 FOR j in p_allDocsTab.FIRST .. p_allDocsTab.LAST LOOP
5252
5253 IF (x_failedDocsTab(i).doc_id <> p_allDocsTab(j).doc_id)
5254 THEN
5255
5256 IF (x_failedDocsTab(i).payee_id =
5257 p_allDocsTab(j).payee_id) THEN
5258
5259 /*
5260 * Check if this doc has already failed and
5261 * been stored in the error docs list. If
5262 * it has already been failed, skip it.
5263 */
5264 l_already_failed_flag :=
5265 checkIfDocFailed(p_allDocsTab(j).doc_id,
5266 x_failedDocsTab);
5267
5268 IF (l_already_failed_flag = FALSE) THEN
5269 /*
5270 * Add this document to list of docs
5271 * failed because of same payee
5272 */
5273 l_invalidDocRec.doc_id :=
5274 p_allDocsTab(j).doc_id;
5275 l_invalidDocRec.pmt_grp_num :=
5276 p_allDocsTab(j).pmt_grp_num;
5277 l_invalidDocRec.payee_id :=
5278 p_allDocsTab(j).payee_id;
5279 l_invalidDocRec.doc_status :=
5280 DOC_STATUS_FAIL_BY_REJLVL;
5281
5282 l_newlyFailedDocsTab(
5283 l_newlyFailedDocsTab.COUNT + 1) :=
5284 l_invalidDocRec;
5285
5286 print_debuginfo(l_module_name, 'Cascaded doc '
5287 || 'failure: '
5288 || x_failedDocsTab(i).doc_id
5289 || ' -> '
5290 || p_allDocsTab(j).doc_id
5291 || '. Related by payee '
5292 || x_failedDocsTab(i).payee_id
5293 || '.'
5294 );
5295
5296 /*
5297 * Once we fail a doc, we must add a
5298 * corresponding error message to the
5299 * error table.
5300 */
5301 IBY_BUILD_UTILS_PKG.createErrorRecord(
5302 TRXN_TYPE_DOC,
5303 p_allDocsTab(j).doc_id,
5304 l_invalidDocRec.doc_status,
5305 p_allDocsTab(j).ca_id,
5306 p_allDocsTab(j).ca_doc_id1,
5307 p_allDocsTab(j).ca_doc_id2,
5308 p_allDocsTab(j).ca_doc_id3,
5309 p_allDocsTab(j).ca_doc_id4,
5310 p_allDocsTab(j).ca_doc_id5,
5311 p_allDocsTab(j).pp_tt_cd,
5312 l_doc_err_rec,
5313 x_errTokenTab,
5314 x_failedDocsTab(i).doc_id
5315 );
5316
5317 insertIntoErrorTable(l_doc_err_rec,
5318 x_docErrorTab, x_errTokenTab);
5319
5320 END IF; -- not already failed
5321
5322 END IF;
5323
5324 END IF;
5325
5326 END LOOP; -- for each doc in request
5327
5328 /*
5329 * Once we have processed all documents which have this
5330 * orig doc id, we must not re-process them if there is
5331 * another error doc with the same orig doc id.
5332 *
5333 * Therefore, store the processed orig doc id. We will
5334 * skip any future error docs that have the same orig
5335 * doc id.
5336 */
5337 print_debuginfo(l_module_name, 'Adding payee id: '
5338 || x_failedDocsTab(i).payee_id
5339 || ' to stored payees tab'
5340 );
5341 l_storedPayeesTab(l_storedPayeesTab.COUNT + 1) :=
5342 x_failedDocsTab(i).payee_id;
5343
5344 END IF; -- not already processed
5345
5346 END IF;
5347
5348 END LOOP; -- for all failed documents
5349
5350 print_debuginfo(l_module_name, 'Total # payee related docs failed: '
5351 || l_newlyFailedDocsTab.COUNT);
5352
5353 /*
5354 * Copy all the newly failed documents back into the
5355 * original failed documents table. The original failed
5356 * docs table will be used to update the database.
5357 */
5358 IF (l_newlyFailedDocsTab.COUNT <> 0) THEN
5359 FOR i in l_newlyFailedDocsTab.FIRST .. l_newlyFailedDocsTab.LAST LOOP
5360 x_failedDocsTab(x_failedDocsTab.COUNT + 1) :=
5361 l_newlyFailedDocsTab(i);
5362 END LOOP;
5363 END IF;
5364
5365 print_debuginfo(l_module_name, 'EXIT');
5366
5367 END failAllDocsForPayee;
5368
5369 /*--------------------------------------------------------------------
5370 | NAME:
5371 | failAllDocsForRequest
5372 |
5373 | PURPOSE:
5374 | Fail all documents in the payment request. All documents that
5375 | are present in the error docs list will already be failed with
5376 | status REJECTED. Add any non-failed docs present in the
5377 | payment request to the error docs list, and status set the
5378 | status of such added docs to FAILED_BY_REJ_LEVEL.
5379 | This is needed for 'request level rejections'.
5380 |
5381 |
5382 | PARAMETERS:
5383 | IN
5384 | p_allDocsTab List of all documents within this payment request
5385 |
5386 | IN/OUT
5387 | x_failedDocsTab List of documents from this payment request that
5388 | have failed validation. When this procedure
5389 | completes, all documents in this request will
5390 | be marked as failed and added to this list
5391 |
5392 | RETURNS:
5393 |
5394 | NOTES:
5395 |
5396 *---------------------------------------------------------------------*/
5397 PROCEDURE failAllDocsForRequest(
5398 p_allDocsTab IN docPayTabType,
5399 x_failedDocsTab IN OUT NOCOPY docStatusTabType,
5400 x_docErrorTab IN OUT NOCOPY docErrorTabType,
5401 x_errTokenTab IN OUT NOCOPY trxnErrTokenTabType
5402 )
5403 IS
5404
5405 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
5406 '.failAllDocsForRequest';
5407
5408 l_invalidDocRec docStatusRecType;
5409 l_already_failed_flag BOOLEAN := FALSE;
5410 l_print_var VARCHAR2(1) := '';
5411 l_doc_err_rec IBY_TRANSACTION_ERRORS%ROWTYPE;
5412 l_doc_token_tab trxnErrTokenTabType;
5413 l_index NUMBER := 0;
5414 l_triggering_doc IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE;
5415
5416 BEGIN
5417
5418 print_debuginfo(l_module_name, 'ENTER');
5419
5420 /*
5421 * We will be failing all successful documents in the
5422 * request because at least one document in the request
5423 * has failed, and the rejection level is set to 'REQUEST'.
5424 *
5425 * When failing the successful docs, we need to refer to
5426 * the triggering doc in the error message. The triggering
5427 * doc can be any failed document in the failed documents
5428 * list.
5429 */
5430 IF (x_failedDocsTab.COUNT <> 0) THEN
5431
5432 l_index := x_failedDocsTab.FIRST;
5433 l_triggering_doc := x_failedDocsTab(l_index).doc_id;
5434 print_debuginfo(l_module_name, 'Triggering doc id: '
5435 || l_triggering_doc);
5436
5437 END IF;
5438
5439 /*
5440 * Loop through all the documents for this request,
5441 * failing any documents that have not been already
5442 * failed.
5443 */
5444 FOR j in p_allDocsTab.FIRST .. p_allDocsTab.LAST LOOP
5445
5446 /*
5447 * Check if this doc has already failed and
5448 * been stored in the error docs list. If
5449 * it has already been failed, skip it.
5450 */
5451 l_already_failed_flag :=
5452 checkIfDocFailed(p_allDocsTab(j).doc_id,
5453 x_failedDocsTab);
5454
5455 IF (l_already_failed_flag = FALSE) THEN
5456 /*
5457 * Add this document to list of docs
5458 * failed because of same payee
5459 */
5460 l_invalidDocRec.doc_id :=
5461 p_allDocsTab(j).doc_id;
5462 l_invalidDocRec.pmt_grp_num :=
5463 p_allDocsTab(j).pmt_grp_num;
5464 l_invalidDocRec.payee_id :=
5465 p_allDocsTab(j).payee_id;
5466 l_invalidDocRec.doc_status :=
5467 DOC_STATUS_FAIL_BY_REJLVL;
5468
5469 x_failedDocsTab(
5470 x_failedDocsTab.COUNT + 1) :=
5471 l_invalidDocRec;
5472
5473 print_debuginfo(l_module_name, 'Failing doc '
5474 || p_allDocsTab(j).doc_id
5475 || ' because all docs in request must be failed.'
5476 );
5477
5478 /*
5479 * Once we fail a doc, we must add a corresponding
5480 * error message to the error table.
5481 */
5482 IBY_BUILD_UTILS_PKG.createErrorRecord(
5483 TRXN_TYPE_DOC,
5484 p_allDocsTab(j).doc_id,
5485 l_invalidDocRec.doc_status,
5486 p_allDocsTab(j).ca_id,
5487 p_allDocsTab(j).ca_doc_id1,
5488 p_allDocsTab(j).ca_doc_id2,
5489 p_allDocsTab(j).ca_doc_id3,
5490 p_allDocsTab(j).ca_doc_id4,
5491 p_allDocsTab(j).ca_doc_id5,
5492 p_allDocsTab(j).pp_tt_cd,
5493 l_doc_err_rec,
5494 x_errTokenTab,
5495 l_triggering_doc
5496 );
5497
5498 insertIntoErrorTable(l_doc_err_rec, x_docErrorTab,
5499 x_errTokenTab);
5500
5501 END IF; -- not already failed
5502
5503 END LOOP; -- for each doc in request
5504
5505 print_debuginfo(l_module_name, 'EXIT');
5506
5507 END failAllDocsForRequest;
5508 /*--------------------------------------------------------------------
5509 | NAME:
5510 | getXMLClob
5511 |
5512 | PURPOSE:
5513 | Performs a database query to get all failed documents for
5514 | the given payment request. These failed documents are put
5515 | into a XML structure and returned to the caller as a CLOB.
5516 |
5517 |
5518 | PARAMETERS:
5519 | IN
5520 |
5521 | OUT
5522 |
5523 |
5524 | RETURNS:
5525 |
5526 | NOTES:
5527 |
5528 |
5529 *---------------------------------------------------------------------*/
5530 FUNCTION getXMLClob(
5531 p_payreq_id IN VARCHAR2
5532 )
5533 RETURN CLOB
5534 IS
5535 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.getXMLClob';
5536 l_xml_clob CLOB := NULL;
5537
5538 l_ctx DBMS_XMLQuery.ctxType;
5539 l_sql VARCHAR2(2000);
5540 l_sqlcode NUMBER;
5541 l_sqlerrm VARCHAR2(300);
5542
5543 BEGIN
5544
5545 print_debuginfo(l_module_name, 'ENTER');
5546 /*
5547 * Select all docs that:
5548 * 1. Have the given pay req id
5549 * 2. Are not in 'documents_validated' status
5550 * 3. Were updated in the process of payment creation
5551 * (some docs might have failed earlier in document
5552 * validation flow. We don't want to pick them up).
5553 */
5554 l_sql := 'SELECT calling_app_id, '
5555 || 'calling_app_doc_unique_ref1, '
5556 || 'calling_app_doc_unique_ref2, '
5557 || 'calling_app_doc_unique_ref3, '
5558 || 'calling_app_doc_unique_ref4, '
5559 || 'calling_app_doc_unique_ref5, '
5560 || 'pay_proc_trxn_type_code '
5561 || 'FROM iby_docs_payable_all '
5562 || 'WHERE payment_service_request_id = :payreq_id '
5563 || 'AND document_status <> :doc_status';
5564
5565 l_ctx := DBMS_XMLQuery.newContext(l_sql);
5566 DBMS_XMLQuery.setBindValue(l_ctx, 'payreq_id', p_payreq_id);
5567 DBMS_XMLQuery.setBindValue(l_ctx, 'doc_status', DOC_STATUS_VALIDATED);
5568 DBMS_XMLQuery.useNullAttributeIndicator(l_ctx, TRUE);
5569
5570 /* raise an exception if no rows were found */
5571 DBMS_XMLQuery.setRaiseException(l_ctx, TRUE);
5572 DBMS_XMLQuery.setRaiseNoRowsException(l_ctx, TRUE);
5573 DBMS_XMLQuery.propagateOriginalException(l_ctx, TRUE);
5574
5575 l_xml_clob := DBMS_XMLQuery.getXML(l_ctx);
5576 DBMS_XMLQuery.closeContext(l_ctx);
5577
5578 print_debuginfo(l_module_name, 'EXIT');
5579
5580 RETURN l_xml_clob;
5581
5582 EXCEPTION
5583
5584 WHEN OTHERS THEN
5585 DBMS_XMLQuery.getExceptionContent(l_ctx, l_sqlcode, l_sqlerrm);
5586 print_debuginfo(l_module_name, 'SQL code: ' || l_sqlcode);
5587 print_debuginfo(l_module_name, 'SQL err msg: '|| l_sqlerrm);
5588
5589 /*
5590 * Do not raise exception if no rows found.
5591 * It means all docs are valid.
5592 * Return NULL clob to caller.
5593 *
5594 * 1403 = NO_DATA_FOUND
5595 *
5596 * Note: We are unable to explicitly catch the
5597 * NO_DATA_FOUND exception here because the caller
5598 * raises some other exception. So we have to check
5599 * value of the original error code instead.
5600 */
5601 IF (l_sqlcode = 1403) THEN
5602 print_debuginfo(l_module_name, 'No rows were returned for query;'
5603 || ' Returning null xml clob.');
5604 RETURN NULL;
5605 END IF;
5606
5607 print_debuginfo(l_module_name, 'Fatal: Exception when attempting '
5608 || 'to raise business event.', FND_LOG.LEVEL_UNEXPECTED);
5609 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
5610 FND_LOG.LEVEL_UNEXPECTED);
5611 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
5612 FND_LOG.LEVEL_UNEXPECTED);
5613
5614 /*
5615 * Propogate exception to caller.
5616 */
5617 RAISE;
5618
5619 END getXMLClob;
5620
5621 /*--------------------------------------------------------------------
5622 | NAME:
5623 | getRejectedDocs
5624 |
5625 | PURPOSE:
5626 | Performs a database query to get all failed documents for
5627 | the given payment request. These failed documents are put
5628 | into data structure and returned to the caller.
5629 |
5630 |
5631 | PARAMETERS:
5632 | IN
5633 |
5634 | OUT
5635 |
5636 |
5637 | RETURNS:
5638 |
5639 | NOTES:
5640 |
5641 |
5642 *---------------------------------------------------------------------*/
5643 PROCEDURE getRejectedDocs(
5644 p_payreq_id IN VARCHAR2,
5645 x_docIDTab IN OUT NOCOPY IBY_DISBURSE_UI_API_PUB_PKG.docPayIDTab,
5646 x_docStatusTab IN OUT NOCOPY IBY_DISBURSE_UI_API_PUB_PKG.docPayStatusTab
5647 )
5648 IS
5649 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.getRejectedDocs';
5650 l_rej_docs_list rejectedDocTabType;
5651
5652 /*
5653 * Cursor to get list of failed documents for a payment service
5654 * request.
5655 */
5656 CURSOR c_rejected_docs (p_payreq_id IBY_PAY_SERVICE_REQUESTS.
5657 payment_service_request_id%TYPE)
5658 IS
5659 SELECT
5660 doc.document_payable_id,
5661 doc.document_status
5662 FROM
5663 IBY_DOCS_PAYABLE_ALL doc
5664 WHERE
5665 doc.payment_service_request_id = p_payreq_id AND
5666 doc.document_status <> DOC_STATUS_VALIDATED
5667 ;
5668
5669 BEGIN
5670
5671 print_debuginfo(l_module_name, 'ENTER');
5672
5673 /*
5674 * Pick up all rejected documents for this payment request.
5675 */
5676 OPEN c_rejected_docs(p_payreq_id);
5677 FETCH c_rejected_docs BULK COLLECT INTO l_rej_docs_list;
5678 CLOSE c_rejected_docs;
5679
5680 /*
5681 * Separate out the document ids and the document statuses.
5682 * This is because the rejection API expects these as
5683 * separate arrays.
5684 */
5685 IF (l_rej_docs_list.COUNT > 0) THEN
5686
5687 FOR i IN l_rej_docs_list.FIRST .. l_rej_docs_list.LAST LOOP
5688 x_docIDTab(i) := l_rej_docs_list(i).doc_id;
5689 END LOOP;
5690
5691 FOR i IN l_rej_docs_list.FIRST .. l_rej_docs_list.LAST LOOP
5692 x_docStatusTab(i) := l_rej_docs_list(i).doc_status;
5693 END LOOP;
5694
5695 END IF;
5696
5697 print_debuginfo(l_module_name, 'EXIT');
5698
5699 END getRejectedDocs;
5700
5701 /*--------------------------------------------------------------------
5702 | NAME:
5703 | checkIfDocFailed
5704 |
5705 | PURPOSE:
5706 | Checks if a given document exists within a list of given
5707 | failed documents.
5708 |
5709 | PARAMETERS:
5710 | IN
5711 |
5712 | OUT
5713 |
5714 |
5715 | RETURNS:
5716 |
5717 | NOTES:
5718 |
5719 |
5720 *---------------------------------------------------------------------*/
5721 FUNCTION checkIfDocFailed(
5722 p_doc_id IN IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE,
5723 p_failedDocsTab IN docStatusTabType
5724 )
5725 RETURN BOOLEAN
5726 IS
5727 l_return_flag BOOLEAN := FALSE;
5728 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.checkIfDocFailed';
5729 l_print_var VARCHAR2(50) := 'false';
5730
5731 BEGIN
5732
5733 print_debuginfo(l_module_name, 'ENTER');
5734
5735 /*
5736 * If error documents table is empty, return false
5737 */
5738 IF (p_failedDocsTab.COUNT = 0) THEN
5739
5740 l_return_flag := FALSE;
5741
5742 print_debuginfo(l_module_name, 'Returning false as '
5743 || 'error docs list is empty');
5744 print_debuginfo(l_module_name, 'EXIT');
5745
5746 return l_return_flag;
5747
5748 END IF;
5749
5750 /*
5751 * If the given document exists in the list of
5752 * failed documents, it means that this document
5753 * has been failed.
5754 */
5755 FOR i in p_failedDocsTab.FIRST .. p_failedDocsTab.LAST LOOP
5756
5757 IF (p_failedDocsTab(i).doc_id = p_doc_id) THEN
5758 l_return_flag := TRUE;
5759 EXIT;
5760 END IF;
5761
5762 END LOOP;
5763
5764 IF (l_return_flag = TRUE) THEN
5765 l_print_var := 'true';
5766 END IF;
5767
5768 print_debuginfo(l_module_name, 'Returning flag as '
5769 || l_print_var
5770 || ' for document '
5771 || p_doc_id);
5772
5773 print_debuginfo(l_module_name, 'EXIT');
5774
5775 return l_return_flag;
5776
5777 END checkIfDocFailed;
5778
5779 /*--------------------------------------------------------------------
5780 | NAME:
5781 | checkIfAllDocsFailed
5782 |
5783 | PURPOSE:
5784 | Checks if all documents of the payment request have failed.
5785 | All documents have failed if each and every document within
5786 | the payment request is found to exist in the list of invalid
5787 | documents.
5788 |
5789 | PARAMETERS:
5790 | IN
5791 |
5792 | OUT
5793 |
5794 |
5795 | RETURNS:
5796 |
5797 | NOTES:
5798 |
5799 |
5800 *---------------------------------------------------------------------*/
5801 FUNCTION checkIfAllDocsFailed(
5802 p_allDocsTab IN docPayTabType,
5803 p_failedDocsTab IN docStatusTabType
5804 )
5805 RETURN BOOLEAN
5806 IS
5807 l_return_flag BOOLEAN := TRUE;
5808 l_check_flag BOOLEAN := FALSE;
5809 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.checkIfAllDocsFailed';
5810 l_print_var VARCHAR2(50) := 'false';
5811
5812 BEGIN
5813
5814 print_debuginfo(l_module_name, 'ENTER');
5815
5816 /*
5817 * If error documents table is empty, return false
5818 */
5819 IF (p_failedDocsTab.COUNT = 0) THEN
5820
5821 l_return_flag := FALSE;
5822
5823 print_debuginfo(l_module_name, 'Returning false as '
5824 || ' error docs list is empty');
5825 print_debuginfo(l_module_name, 'EXIT');
5826
5827 return l_return_flag;
5828
5829 END IF;
5830
5831 /*
5832 * If provided documents table is empty, return true
5833 */
5834 IF (p_allDocsTab.COUNT = 0) THEN
5835
5836 l_return_flag := TRUE;
5837
5838 print_debuginfo(l_module_name, 'Returning true as '
5839 || ' empty docs list has been provided');
5840 print_debuginfo(l_module_name, 'EXIT');
5841
5842 return l_return_flag;
5843
5844 END IF;
5845
5846 /*
5847 * Loop through all the documents in the request, checking
5848 * if doc has failed. If even a single doc has not failed,
5849 * we can return immediately that not all docs have failed
5850 */
5851 FOR i in p_allDocsTab.FIRST .. p_allDocsTab.LAST LOOP
5852
5853 l_check_flag := checkIfDocFailed(p_allDocsTab(i).doc_id,
5854 p_failedDocsTab);
5855
5856 IF (l_check_flag = FALSE) THEN
5857 l_return_flag := FALSE;
5858 EXIT;
5859 END IF;
5860
5861 END LOOP;
5862
5863 IF (l_return_flag = TRUE) THEN
5864 l_print_var := 'true';
5865 END IF;
5866
5867 print_debuginfo(l_module_name, 'Returning flag as '
5868 || l_print_var);
5869
5870 print_debuginfo(l_module_name, 'EXIT');
5871
5872 return l_return_flag;
5873
5874 END checkIfAllDocsFailed;
5875
5876 /*--------------------------------------------------------------------
5877 | NAME:
5878 | getDocRejLevelSysOption
5879 |
5880 | PURPOSE:
5881 | Gets the document rejection level system option.
5882 |
5883 | Possible values are:
5884 | REQUEST | DOCUMENT | PAYEE | NONE
5885 |
5886 | The handling of document validation failures is dependent
5887 | upon the rejection level setting.
5888 |
5889 | PARAMETERS:
5890 | IN
5891 |
5892 |
5893 | OUT
5894 |
5895 |
5896 | RETURNS:
5897 |
5898 | NOTES:
5899 |
5900 *---------------------------------------------------------------------*/
5901 FUNCTION getDocRejLevelSysOption RETURN VARCHAR2
5902 IS
5903 l_rejLevel VARCHAR2(200);
5904 l_sys_options_tab sysOptionsTabType;
5905 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
5906 '.getDocRejLevelSysOption';
5907 l_print_var NUMBER := -1;
5908
5909 CURSOR c_sys_options
5910 IS
5911 SELECT
5912 sysoptions.document_rejection_level_code
5913 FROM
5914 IBY_INTERNAL_PAYERS_ALL sysoptions
5915 WHERE
5916 sysoptions.org_id IS NULL
5917 ;
5918
5919 BEGIN
5920
5921 print_debuginfo(l_module_name, 'ENTER');
5922
5923 /*
5924 * Fetch the system options for the given org
5925 */
5926 OPEN c_sys_options;
5927 FETCH c_sys_options BULK COLLECT INTO l_sys_options_tab;
5928 CLOSE c_sys_options;
5929
5930 IF (l_sys_options_tab.COUNT = 0) THEN
5931
5932 /*
5933 * This means that the document rejection level
5934 * is not set at the enterprise level.
5935 *
5936 * Enterprise level rejection levels (i.e., with org
5937 * id set to null) are expected to be seeded.
5938 *
5939 * Raise an exception and abort processing.
5940 */
5941 print_debuginfo(l_module_name, 'Document rejection level '
5942 || 'system option is not set at '
5943 || 'enterprise level. It is mandatory to '
5944 || 'setup rejection levels at enterprise level. '
5945 || 'Raising exception.. ',
5946 FND_LOG.LEVEL_UNEXPECTED
5947 );
5948
5949 APP_EXCEPTION.RAISE_EXCEPTION;
5950
5951 ELSIF (l_sys_options_tab.COUNT <> 1) THEN
5952
5953 /*
5954 * This means that there are multiple document
5955 * rejection levels set at the enterprise level.
5956 * We don't know which one to use.
5957 *
5958 * Raise an exception and abort processing.
5959 */
5960 print_debuginfo(l_module_name, 'Multiple document rejection '
5961 || 'level system options are set at '
5962 || 'enterprise level. It is mandatory to '
5963 || 'setup only one document rejection level '
5964 || 'at enterprise level. '
5965 || 'Raising exception.. ',
5966 FND_LOG.LEVEL_UNEXPECTED
5967 );
5968
5969 APP_EXCEPTION.RAISE_EXCEPTION;
5970
5971 ELSE
5972
5973 /*
5974 * Return the retrieved enterprise level
5975 * document rejection level system option.
5976 */
5977 l_rejLevel := l_sys_options_tab(1).rej_level;
5978
5979 END IF; -- if l_sys_options_tab.COUNT = 0
5980
5981 print_debuginfo(l_module_name, 'Returning rejection level: '
5982 || l_rejLevel);
5983
5984 print_debuginfo(l_module_name, 'EXIT');
5985
5986 RETURN l_rejLevel;
5987
5988 EXCEPTION
5989 WHEN OTHERS THEN
5990
5991 /*
5992 * In case of an exception, return NULL
5993 */
5994 l_rejLevel := NULL;
5995 print_debuginfo(l_module_name, 'No document rejection level '
5996 || 'set up at enterprise level. '
5997 || 'Returning NULL.');
5998
5999 print_debuginfo(l_module_name, 'EXIT');
6000 RETURN l_rejLevel;
6001
6002 END getDocRejLevelSysOption;
6003
6004 /*--------------------------------------------------------------------
6005 | NAME:
6006 | validateProfileFromProfDrivers
6007 |
6008 | PURPOSE:
6009 | Checks if the given payment profile is valid for the given
6010 | (payment method, org, payment currency, internal
6011 | bank account) combination on the document.
6012 |
6013 | PARAMETERS:
6014 | IN
6015 |
6016 | OUT
6017 |
6018 |
6019 | RETURNS:
6020 |
6021 | NOTES:
6022 |
6023 |
6024 *---------------------------------------------------------------------*/
6025 FUNCTION validateProfileFromProfDrivers(
6026 p_profile_id IN IBY_DOCS_PAYABLE_ALL.payment_profile_id%TYPE,
6027 p_org_id IN IBY_DOCS_PAYABLE_ALL.org_id%TYPE,
6028 p_org_type IN IBY_DOCS_PAYABLE_ALL.org_type%TYPE,
6029 p_pmt_method_cd IN IBY_DOCS_PAYABLE_ALL.payment_method_code%TYPE,
6030 p_pmt_currency IN IBY_DOCS_PAYABLE_ALL.payment_currency_code%TYPE,
6031 p_int_bank_acct_id IN IBY_DOCS_PAYABLE_ALL.internal_bank_account_id%TYPE
6032 )
6033 RETURN BOOLEAN
6034 IS
6035 l_return_flag BOOLEAN := FALSE;
6036 l_module_name CONSTANT VARCHAR2(200)
6037 := G_PKG_NAME ||
6038 '.validateProfileFromProfDrivers';
6039
6040 l_profiles_tab IBY_BUILD_UTILS_PKG.pmtProfTabType;
6041
6042 BEGIN
6043
6044 print_debuginfo(l_module_name, 'ENTER');
6045
6046 print_debuginfo(l_module_name, 'Provided parameters are: '
6047 || 'Profile id: '
6048 || p_profile_id
6049 || ', org id: '
6050 || p_org_id
6051 || ', org type: '
6052 || p_org_type
6053 || ', payment method: '
6054 || p_pmt_method_cd
6055 || ', payment currency: '
6056 || p_pmt_currency
6057 || ', int bank acct id: '
6058 || p_int_bank_acct_id
6059 );
6060
6061 /*
6062 * Get the list of all payment profiles that
6063 * match the given list of profile drivers.
6064 */
6065 IBY_BUILD_UTILS_PKG.getProfListFromProfileDrivers(
6066 p_pmt_method_cd,
6067 p_org_id,
6068 p_org_type,
6069 p_pmt_currency,
6070 p_int_bank_acct_id,
6071 l_profiles_tab);
6072
6073 /*
6074 * If count is non-zero it means that at least one
6075 * profile matches the given set of drivers.
6076 *
6077 * Otherwise, it means that no profile matches the
6078 * given set of drivers; implying that the profile
6079 * on the document is invalid (so return FALSE).
6080 */
6081 IF (l_profiles_tab.COUNT = 0) THEN
6082
6083 l_return_flag := FALSE;
6084
6085 ELSE
6086
6087 /*
6088 * There are some profiles that match the
6089 * given set of profile drivers.
6090 *
6091 * Check if the given profile falls within
6092 * the list of profiles that we have derived.
6093 */
6094 FOR i IN l_profiles_tab.FIRST .. l_profiles_tab.LAST LOOP
6095
6096 IF (l_profiles_tab(i).profile_id = p_profile_id) THEN
6097
6098 /*
6099 * The given profile was found in the list
6100 * of derived profiles; therefore, the
6101 * provided profile is valid.
6102 */
6103 l_return_flag := TRUE;
6104 EXIT;
6105
6106 END IF;
6107
6108 END LOOP;
6109
6110 END IF;
6111
6112 print_debuginfo(l_module_name, 'EXIT');
6113
6114 RETURN l_return_flag;
6115
6116 END validateProfileFromProfDrivers;
6117
6118 /*--------------------------------------------------------------------
6119 | NAME:
6120 | checkProfileFormatCompat
6121 |
6122 | PURPOSE:
6123 |
6124 |
6125 | PARAMETERS:
6126 | IN
6127 |
6128 |
6129 | OUT
6130 |
6131 |
6132 | RETURNS:
6133 |
6134 | NOTES:
6135 |
6136 *---------------------------------------------------------------------*/
6137 FUNCTION checkProfileFormatCompat(
6138 p_doc_id IN IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE,
6139 p_payee_id IN IBY_DOCS_PAYABLE_ALL.ext_payee_id%TYPE,
6140 p_profile_id IN IBY_DOCS_PAYABLE_ALL.payment_profile_id%TYPE,
6141 p_payeeFormatTab IN payeeFormatTabType,
6142 p_profileFormatTab IN profileFormatTabType
6143 ) RETURN BOOLEAN
6144 IS
6145 l_return_flag BOOLEAN := FALSE;
6146 l_module_name CONSTANT VARCHAR2(200)
6147 := G_PKG_NAME || '.checkProfileFormatCompat';
6148
6149 l_payee_format_cd IBY_EXTERNAL_PAYEES_ALL.payment_format_code%TYPE;
6150
6151 BEGIN
6152
6153 print_debuginfo(l_module_name, 'ENTER');
6154
6155 print_debuginfo(l_module_name, 'Validating profile-format '
6156 || 'compatibility for document '
6157 || p_doc_id
6158 );
6159
6160 IF (p_payeeFormatTab.COUNT = 0 OR p_profileFormatTab.COUNT = 0) THEN
6161
6162 l_return_flag := TRUE;
6163
6164 print_debuginfo(l_module_name, 'Payee format list/profile format '
6165 || 'list not available. Cannot validate. Returning success.'
6166 );
6167
6168 print_debuginfo(l_module_name, 'EXIT');
6169
6170 RETURN l_return_flag;
6171
6172 END IF;
6173
6174 IF (p_profile_id IS NULL OR p_profile_id = -1) THEN
6175
6176 l_return_flag := TRUE;
6177
6178 print_debuginfo(l_module_name, 'Profile id not '
6179 || 'available. Cannot validate. Returning success.'
6180 );
6181
6182 print_debuginfo(l_module_name, 'EXIT');
6183
6184 RETURN l_return_flag;
6185
6186 END IF;
6187
6188
6189 IF (p_payee_id IS NULL OR p_payee_id = -1) THEN
6190
6191 l_return_flag := TRUE;
6192
6193 print_debuginfo(l_module_name, 'Payee id not '
6194 || 'available. Cannot validate. Returning success.'
6195 );
6196
6197 print_debuginfo(l_module_name, 'EXIT');
6198
6199 RETURN l_return_flag;
6200
6201 END IF;
6202
6203 /*
6204 * Pick up the default format for the payee on the
6205 * document.
6206 */
6207 /* Initialize */
6208 l_payee_format_cd := NULL;
6209
6210 FOR i IN p_payeeFormatTab.FIRST .. p_payeeFormatTab.LAST LOOP
6211
6212 IF (p_payeeFormatTab(i).payee_id = p_payee_id) THEN
6213
6214 l_payee_format_cd := p_payeeFormatTab(i).payment_format_cd;
6215
6216 print_debuginfo(l_module_name, 'Format: '
6217 || l_payee_format_cd
6218 || ' linked to payee '
6219 || p_payee_id);
6220
6221 END IF;
6222
6223 END LOOP;
6224
6225 IF (l_payee_format_cd IS NULL) THEN
6226
6227 print_debuginfo(l_module_name, 'No format was found '
6228 || 'linked to payee '
6229 || p_payee_id
6230 );
6231
6232 l_return_flag := TRUE;
6233
6234 print_debuginfo(l_module_name, 'Format cd not '
6235 || 'available. Cannot validate. Returning success.'
6236 );
6237
6238 print_debuginfo(l_module_name, 'EXIT');
6239
6240 RETURN l_return_flag;
6241
6242 END IF;
6243
6244 /*
6245 * Check if the format on the payee matches the format
6246 * on the profiles that are set up.
6247 */
6248 FOR i IN p_profileFormatTab.FIRST .. p_profileFormatTab.LAST LOOP
6249
6250 IF (p_profileFormatTab(i).profile_id = p_profile_id AND
6251 p_profileFormatTab(i).payment_format_cd = l_payee_format_cd)
6252 THEN
6253
6254 print_debuginfo(l_module_name, 'Profile: '
6255 || p_profile_id
6256 || ' is compatible with format '
6257 || l_payee_format_cd);
6258
6259 l_return_flag := TRUE;
6260
6261 print_debuginfo(l_module_name, 'EXIT');
6262
6263 RETURN l_return_flag;
6264
6265 END IF;
6266
6267 END LOOP;
6268
6269 /*
6270 * If we reached here, it means that no profile was
6271 * found matching the given format. This is an error.
6272 * Return validation failure result.
6273 */
6274 print_debuginfo(l_module_name, 'No profiles matched format '
6275 || l_payee_format_cd
6276 || ' for document '
6277 || p_doc_id
6278 || '. Profile '
6279 || p_profile_id
6280 || ' and format '
6281 || l_payee_format_cd
6282 || ' are not compatible.'
6283 );
6284
6285 l_return_flag := TRUE;
6286 print_debuginfo(l_module_name, 'EXIT');
6287
6288 RETURN l_return_flag;
6289
6290 END checkProfileFormatCompat;
6291
6292 END IBY_VALIDATIONSETS_PUB;