1 PACKAGE BODY IBY_SINGPAY_PUB AS
2 /*$Header: ibypsinb.pls 120.36.12020000.2 2013/02/25 10:52:40 kchavali ship $*/
3
4 --
5 -- Declare global variables
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_SINGPAY_PUB';
8 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
10 --
11 -- List of document statuses that are used / set in this
12 -- module (payment creation flow).
13 --
14 DOC_STATUS_VALIDATED CONSTANT VARCHAR2(100) := 'VALIDATED';
15 DOC_STATUS_PAY_CREATED CONSTANT VARCHAR2(100) := 'PAYMENTS_CREATED';
16 DOC_STATUS_CA_FAILED CONSTANT VARCHAR2(100) := 'FAILED_BY_CALLING_APP';
17 DOC_STATUS_RELN_FAIL CONSTANT VARCHAR2(100) := 'FAILED_BY_RELATED_DOCUMENT';
18 DOC_STATUS_PAY_VAL_FAIL CONSTANT VARCHAR2(100) := 'PAYMENT_FAILED_VALIDATION';
19 DOC_STATUS_FAIL_BY_REJLVL CONSTANT VARCHAR2(100)
20 := 'FAILED_BY_REJECTION_LEVEL';
21
22 --
23 -- List of payment statuses that are used / set in this
24 -- module (payment creation flow).
25 --
26 PAY_STATUS_REJECTED CONSTANT VARCHAR2(100) := 'REJECTED';
27 PAY_STATUS_FAIL_VALID CONSTANT VARCHAR2(100) := 'FAILED_VALIDATION';
28 PAY_STATUS_CREATED CONSTANT VARCHAR2(100) := 'CREATED';
29 PAY_STATUS_CA_FAILED CONSTANT VARCHAR2(100) := 'FAILED_BY_CALLING_APP';
30 PAY_STATUS_FAIL_BY_REJLVL CONSTANT VARCHAR2(100)
31 := 'FAILED_BY_REJECTION_LEVEL';
32
33 --
34 -- List of payment request statuses that are set in this
35 -- module (payment creation flow).
36 --
37 REQ_STATUS_PAY_CRTD CONSTANT VARCHAR2(100) := 'PAYMENTS_CREATED';
38 REQ_STATUS_FAIL_PAY_CR CONSTANT VARCHAR2(100) := 'FAILED_PAYMENT_VALIDATION';
39 REQ_STATUS_USER_REVW CONSTANT VARCHAR2(100) := 'PENDING_REVIEW';
40 REQ_STATUS_USER_REVW_ERR CONSTANT VARCHAR2(100)
41 := 'PENDING_REVIEW_PMT_VAL_ERRORS';
42
43 --
44 -- List of rejection level system options that are possible for
45 -- this module (payment creation flow).
46 --
47 REJ_LVL_REQUEST CONSTANT VARCHAR2(100) := 'REQUEST';
48 REJ_LVL_PAYMENT CONSTANT VARCHAR2(100) := 'PAYMENT';
49 REJ_LVL_NONE CONSTANT VARCHAR2(100) := 'NONE';
50
51 -- Transaction types (for inserting into IBY_TRANSACTION_ERRORS table)
52 TRXN_TYPE_DOC CONSTANT VARCHAR2(100) := 'DOCUMENT_PAYABLE';
53 TRXN_TYPE_PMT CONSTANT VARCHAR2(100) := 'PAYMENT';
54
55 /*--------------------------------------------------------------------
56 | NAME:
57 | createPayments
58 |
59 | PURPOSE:
60 | Entry point for payment creation flow. All payment grouping
61 | rules are handled within this method.
62 |
63 | This method is very similar to createPayments(..) in the
64 | IBY_PAYGROUP_PUB package. That package is meant for normal
65 | build program flow, whereas this package is meant for single
66 | payments flow.
67 |
68 | PARAMETERS:
69 | IN
70 |
71 |
72 | OUT
73 |
74 |
75 | RETURNS:
76 |
77 | NOTES:
78 |
79 *---------------------------------------------------------------------*/
80 PROCEDURE createPayments(
81 p_payment_request_id IN IBY_PAY_SERVICE_REQUESTS.
82 payment_service_request_id%TYPE,
83 p_pmt_rejection_level IN IBY_INTERNAL_PAYERS_ALL.
84 payment_rejection_level_code%TYPE,
85 p_review_proposed_pmts_flag IN IBY_INTERNAL_PAYERS_ALL.
86 require_prop_pmts_review_flag%TYPE,
87 p_override_complete_point IN VARCHAR2,
88 p_bill_payable_flag IN VARCHAR2,
89 p_maturity_date IN DATE,
90 x_return_status IN OUT NOCOPY VARCHAR2)
91 IS
92
93 l_module_name VARCHAR2(200) := G_PKG_NAME || '.createPayments';
94
95 /* these are related to central bank reporting */
96 l_decl_option VARCHAR2(100) := '';
97 l_decl_only_fx_flag VARCHAR2(1) := '';
98 l_decl_curr_fx_rate_type VARCHAR2(255) := '';
99 l_decl_curr_code VARCHAR2(10) := '';
100 l_decl_threshold_amount NUMBER(15) := 0;
101
102 /* Bug 5709596 */
103 -- l_cbrTab IBY_PAYGROUP_PUB.centralBankReportTabType;
104
105 l_ca_payreq_cd VARCHAR2(255) := '';
106 l_ca_id NUMBER(15) := 0;
107 l_all_pmts_success_flag BOOLEAN := FALSE;
108 l_all_pmts_failed_flag BOOLEAN := FALSE;
109
110 /* rejection level system options */
111 l_rejection_level VARCHAR2(200);
112 l_review_pmts_flag VARCHAR2(1) := 'N';
113
114 l_paymentTab IBY_PAYGROUP_PUB.paymentTabType;
115 l_docsInPmtTab IBY_PAYGROUP_PUB.docsInPaymentTabType;
116
117 /* these two are passed to calling app via hook */
118 l_hookPaymentTab IBY_PAYGROUP_PUB.hookPaymentTabType;
119 l_hookDocsInPmtTab IBY_PAYGROUP_PUB.hookDocsInPaymentTabType;
120
121 /* holds the error messages against failed documents */
122 l_docErrorTab IBY_VALIDATIONSETS_PUB.docErrorTabType;
123 l_docTokenTab IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType;
124
125 /* payment request imposed limits on payments */
126 /* not used but needs to be passed into performDocumentGrouping() method */
127 l_payReqCriteria IBY_PAYGROUP_PUB.payReqImposedCriteria;
128
129 BEGIN
130
131 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
132 print_debuginfo(l_module_name, 'ENTER');
133
134 print_debuginfo(l_module_name, 'Payment Request Id : '||
135 p_payment_request_id);
136
137 END IF;
138 /*
139 * Group the documents of the payment request into payments.
140 */
141 IBY_PAYGROUP_PUB.performDocumentGrouping(p_payment_request_id
142 , l_paymentTab
143 , l_docsInPmtTab
144 , l_ca_id
145 , l_ca_payreq_cd
146 , l_payReqCriteria
147 -- , l_cbrTab
148 );
149
150 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
151 print_debuginfo(l_module_name, 'After grouping '
152 || l_paymentTab.COUNT || ' payment(s) from '
153 || l_docsInPmtTab.COUNT || ' document(s) for payment request '
154 || p_payment_request_id || ' were created.'
155 );
156
157 END IF;
158 /*
159 * IMPORTANT CHECK FOR SINGLE PAYMENTS:
160 *
161 * After hardcoded grouping not more than one payment should be
162 * created from the given documents for the single payment flow.
163 *
164 * This is because the calling app is expected to have performed
165 * the hardcoded grouping before the single payment API is invoked.
166 *
167 * If we have created more than one payment from the given document,
168 * it is an error and return failure.
169 */
170 IF (l_paymentTab.COUNT <> 1) THEN
171
172 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
173 print_debuginfo(l_module_name, 'Application of hardcoded '
174 || 'grouping rules did not generate exactly one payment. '
175 || 'Only one payment can be created for single payments. '
176 || 'Payment creation failed.'
177 );
178
179 END IF;
180 x_return_status := FND_API.G_RET_STS_ERROR;
181
182 APP_EXCEPTION.RAISE_EXCEPTION;
183
184 END IF;
185
186 /*
187 * Fix for bug 5357948:
188 *
189 * Search for common attributes of the documents
190 * of each payment, and set the corresponding
191 * attribute on the parent payment itself if
192 * such a common attribute is found.
193 */
194 IBY_PAYGROUP_PUB.sweepCommonPmtAttributes(l_paymentTab, l_docsInPmtTab);
195
196 /*
197 * Set the default attributes for the created payments.
198 */
199 initializePmts(l_paymentTab);
200
201 /*
202 * Handle credit memos
203 */
204
205 /*
206 * Update: Credit memo handling is now performed at the calling
207 * application itself.
208 * - rameshsh, 3/29/2005
209 */
210 --printDocsInPmtTab(l_docsInPmtTab);
211 --performCreditMemoHandling(l_paymentTab, l_docsInPmtTab);
212 --printDocsInPmtTab(l_docsInPmtTab);
213
214 /*
215 * Maturity date calculation
216 *
217 * For bills payable
218 * a.k.a promissory notes
219 * a.k.a future dated payments
220 */
221
222 /*
223 * Fix for bug 5334222:
224 *
225 * There is no need to calculate the maturity date for
226 * single payments. The maturity date for single payments
227 * is expected to be passed in.
228 *
229 * Simply assign the passed in maturity date to the
230 * created payment in case it is a bills payable.
231 */
232 IF (UPPER(p_bill_payable_flag) = 'Y') THEN
233
234 /*
235 * We can use index 1 to access the payment because
236 * only one payment is expected to be created
237 * (this being a single payment).
238 */
239 l_paymentTab(1).maturity_date := p_maturity_date;
240
241 /*
242 * Fix for bug 5334222:
243 *
244 * Set the bills payable flag to 'Y' whenever the
245 * maturity date is set.
246 */
247 l_paymentTab(1).bill_payable_flag := 'Y';
248
249 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
250 print_debuginfo(l_module_name, 'Payment '
251 || l_paymentTab(1).payment_id
252 || ' is a bills payable; Maturity date '
253 || ' set to '
254 || l_paymentTab(1).maturity_date
255 );
256
257 END IF;
258 ELSE
259
260 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
261 print_debuginfo(l_module_name, 'Not setting maturity '
262 || 'date for payment '
263 || l_paymentTab(1).payment_id
264 || ' as it is not a bill payable.'
265 );
266
267 END IF;
268 END IF;
269
270 /*
271 * Fix for bug 5935493:
272 *
273 * Payment validations are dependent upon finding the
274 * payments in the IBY_PAYMENTS_ALL table. Therefore, insert
275 * the payments from the PLSQL table into the
276 * IBY_PAYMENTS_ALL table.
277 *
278 * Central bank reporting could be implemented via a hook
279 * that is expecting payments to be populated in
280 * IBY_PAYMENTS_ALL table. Therefore, payments need to
281 * inserted before performing central bank reporting as well.
282 */
283 IBY_PAYGROUP_PUB.insertPayments(l_paymentTab);
284
285 /*
286 * Fix for bug 5972585:
287 *
288 * Update the documents payable that are part of the created
289 * payments with payment ids.
290 *
291 * This is normally done in performDBUpdates(..) call at
292 * the end of this method. However, some customers might
293 * want to do custom validations on documents that are
294 * part of the created payments. Therefore, update the
295 * documents with payment ids before the payment validation
296 * call.
297 *
298 * We do this even before central bank reporting because
299 * again the customer could potentially want to retrieve
300 * documents that are part of the created payments in the
301 * central bank reporting hook.
302 */
303 IBY_PAYGROUP_PUB.updateDocsWithPaymentID(l_docsInPmtTab);
304
305 /*
306 * Fix for bug 5972585:
307 *
308 * Update the payments table with audit data before
309 * calling central bank reporting or payment validations.
310 *
311 * This is because the customer could have implemented
312 * custom central bank reporting or custom payment
313 * validations that could depend upon the denormalized
314 * payment attributes being present in the the payments
315 * table.
316 */
317
318 /*
319 * Fix for bug 5511781:
320 *
321 * Along with the payment we insert the audit data for the
322 * payment as well. These are denormalized data from payment
323 * related tables like payee, payer, payee bank, payer bank
324 * etc.
325 *
326 * This information is also used by the extract and format
327 * logic.
328 */
329 --IBY_PAYGROUP_PUB.auditPaymentData(l_paymentTab);
330
331 /*
332 * Fix for bug 5337671:
333 *
334 * Perform central bank reporting prior to calling
335 * payment validations.
336 *
337 * This is because some validations are dependent upon
338 * whether the payment has the 'declare payment flag'
339 * set to 'Y'.
340 */
341
342 /*
343 * Perform declarations / central bank reporting.
344 */
345 IBY_PAYGROUP_PUB.performCentralBankReporting(
346 l_paymentTab
347 , l_docsInPmtTab
348 -- , l_cbrTab
349 );
350
351 /*
352 * Fix for bug 5935493:
353 *
354 * After central bank reporting is completed,
355 * the declare payments flag could be set on some/all
356 * payments by the hook. Therefore, we need to
357 * update the payments in IBY_PAYMENTS_ALL table
358 * so that this flag is accessible for validation.
359 */
360 IBY_PAYGROUP_PUB.updatePayments(l_paymentTab);
361
362
363 /*
364 * Payment validations
365 */
366 --IBY_PAYGROUP_PUB.applyPaymentValidationSets(p_payment_request_id,l_paymentTab,
367 -- l_docsInPmtTab, l_docErrorTab, l_docTokenTab);
368
369
370 /* Validation check for negative payment amounts
371 * Added for the Bug 7344352
372 */
373 negativePmtAmountCheck(l_paymentTab,
374 l_docsInPmtTab, l_docErrorTab, l_docTokenTab);
375
376 /*
377 * Payment grouping number validation.
378 */
379
380 /*
381 * Commented out because of performance impact
382 * - Ramesh, Nov 17 2006.
383 */
384 --IBY_PAYGROUP_PUB.performPmtGrpNumberValidation(l_paymentTab,
385 -- l_docsInPmtTab, l_docErrorTab, l_docTokenTab);
386
387 /*
388 * Payment validation might have failed some payments (and by
389 * cascade, the documents that were part of these payments).
390 *
391 * Make sure to fail related documents whenever documents are
392 * failed.
393 */
394 IBY_PAYGROUP_PUB.adjustSisterDocsAndPmts(l_paymentTab,
395 l_docsInPmtTab, l_docErrorTab, l_docTokenTab);
396
397 /*
398 * Call post-payment creation hook. This hook will pass
399 * the created payments to the calling application for
400 * approval/adjustment.
401 *
402 * The adjusted payments are read back and inserted into
403 * IBY_PAYMENTS_LL table.
404 *
405 * This is a general hook that is called for all other
406 * products except AP. For AP special hooks are called
407 * below.
408 */
409 IF (l_ca_id <> 200) THEN
410
411 /*
412 * Only successful payments are passed to be passed to
413 * to the calling application via the hook / callout.
414 *
415 * From the existing list of all payments, create new data
416 * structures that only store successful payments. This
417 * 'success only' list of payments will be passed to the
418 * calling application.
419 *
420 * This method writes the payment data to global temp tables.
421 */
422 IBY_PAYGROUP_PUB.performPreHookProcess(
423 l_ca_payreq_cd, l_ca_id, l_paymentTab,
424 l_docsInPmtTab, l_hookPaymentTab,
425 l_hookDocsInPmtTab);
426
427 /*
428 * Hook to call external application for implementation of the
429 * following functionality:
430 *
431 * 1. Bank charge calculation
432 * 2. Tax withtholding
433 *
434 * Any other miscellaneous correction of payment/document data
435 * is also allowed in the hook.
436 */
437 IBY_PAYGROUP_PUB.callHook(p_payment_request_id);
438
439 /*
440 * The external app may decide not to pay a document(s)
441 * within a payment, or may decide not to make a payment(s).
442 * In such cases, the external app will set the 'don't pay flag'
443 * and provide a 'don't pay reason' at the document / payment
444 * level (as appropriate) in the provided data structures.
445 *
446 * If a document(s) is marked as don't pay, then we must
447 * adjust the payment amount appropriately.
448 *
449 * Also, some documents are related via 'payment grouping number'.
450 * All documents that are related must be failed and their
451 * constituent payment amounts must be adjusted
452 *
453 * This method reads the payment data from global temp tables.
454 */
455 IBY_PAYGROUP_PUB.performPostHookProcess(
456 l_paymentTab, l_docsInPmtTab,
457 l_hookPaymentTab, l_hookDocsInPmtTab,
458 l_docErrorTab, l_docTokenTab);
459
460 END IF; -- if calling product <> AP
461
462
463 /*
464 * SPECIAL HOOKS FOR AP:
465 *
466 * NOTE:
467 * -----
468 * + Extended withholding hook should not be invoked for
469 * single payments.
470 *
471 * + Japanese bank charges hook should not be invoked for
472 * single payments.
473 */
474 IF (l_ca_id = 200) THEN
475 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
476 print_debuginfo(l_module_name, 'Not invoking any AP hooks.');
477 END IF;
478 END IF;
479
480 /*
481 * Flag payments that require separate remittance
482 * advice.
483 */
484 IBY_PAYGROUP_PUB.flagSeparateRemitAdvicePmts(l_paymentTab,
485 l_docsInPmtTab);
486
487 /*
488 * Get the rejection level system option and pass
489 * it to subsequent methods.
490 */
491
492 /*
493 * For single payments, there is no concept of
494 * payment rejection level system option. In the
495 * single payments flow, only one payment will be
496 * created, so if the payment fails, the request
497 * fails.
498 *
499 * So for single payments, the payment rejection
500 * level is implictly 'REQUEST'.
501 */
502 l_rejection_level := 'REQUEST';
503
504 /*
505 * There is no question of reviewing the payment for
506 * single payments. Review payments flag is only
507 * applicable to standard payments built by the
508 * Build Program.
509 *
510 * Hardcode the review payments flag to 'N' for single
511 * payments.
512 */
513 l_review_pmts_flag := 'N';
514
515 /*
516 * All payments for this payment request have been
517 * created and stored in a PLSQL table. Now write these
518 * payments to the database.
519 *
520 * Similarly, update the documents table by providing a
521 * payment id to each document.
522 */
523 performDBUpdates(p_payment_request_id, l_rejection_level,
524 l_review_pmts_flag, p_override_complete_point,
525 l_paymentTab, l_docsInPmtTab, l_all_pmts_success_flag,
526 l_all_pmts_failed_flag, x_return_status,
527 l_docErrorTab, l_docTokenTab
528 );
529
530 /*
531 * NOTE:
532 *
533 * Do not raise business events / invoke callouts for
534 * single payments. This is because Single Payments API
535 * is a synchronous API and we do not commit any data if
536 * there are validation failures.
537 */
538
539 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
540 print_debuginfo(l_module_name, 'EXIT');
541
542 END IF;
543 END createPayments;
544
545 /*--------------------------------------------------------------------
546 | NAME:
547 | performDBUpdates
548 |
549 | PURPOSE:
550 | This is the top level method that is called by the
551 | payment creation program to:
552 | 1. insert payments to DB
553 | 2. update documents with payment id
554 | 3. update status of payment request
555 |
556 | This method will read the 'rejection level' system option
557 | and do updates accordingly.
558 |
559 | PARAMETERS:
560 | IN
561 |
562 |
563 | OUT
564 |
565 |
566 | RETURNS:
567 |
568 |
569 | NOTES:
570 |
571 *---------------------------------------------------------------------*/
572 PROCEDURE performDBUpdates(
573 p_payreq_id IN IBY_PAY_SERVICE_REQUESTS.
574 payment_service_request_id%type,
575 p_rej_level IN VARCHAR2,
576 p_review_pmts_flag IN VARCHAR2,
577 p_override_compl_pt IN VARCHAR2,
578 x_paymentTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
579 x_docsInPmtTab IN OUT NOCOPY IBY_PAYGROUP_PUB.docsInPaymentTabType,
580 x_allPmtsSuccessFlag IN OUT NOCOPY BOOLEAN,
581 x_allPmtsFailedFlag IN OUT NOCOPY BOOLEAN,
582 x_return_status IN OUT NOCOPY VARCHAR2,
583 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
584 docErrorTabType,
585 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
586 trxnErrTokenTabType
587 )
588 IS
589 l_module_name VARCHAR2(200) := G_PKG_NAME || '.performDBUpdates';
590 l_allsuccess_flag BOOLEAN := TRUE;
591 l_allfailed_flag BOOLEAN := TRUE;
592 l_request_status VARCHAR2(200);
593
594 --l_doc_err_rec IBY_VALIDATIONSETS_PUB.docErrorRecType;
595 l_doc_err_rec IBY_TRANSACTION_ERRORS%ROWTYPE;
596
597 l_triggering_pmt_id IBY_PAYMENTS_ALL.payment_reference_number%TYPE; --AWT Enh 16296267
598
599 BEGIN
600
601 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
602 print_debuginfo(l_module_name, 'ENTER');
603
604 END IF;
605 /*
606 * Print the rejection level system option
607 */
608 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
609 print_debuginfo(l_module_name, 'Rejection level system option set to: '
610 || p_rej_level);
611
612 END IF;
613 /*
614 * Find out whether all the payments within this
615 * payment request have 'success' status. This
616 * information is used below.
617 */
618 FOR i in x_paymentTab.FIRST .. x_paymentTab.LAST LOOP
619 IF (x_paymentTab(i).payment_status <> PAY_STATUS_CREATED) THEN
620 l_triggering_pmt_id := x_paymentTab(i).payment_reference_number; -- AWT Enh 16296267
621 l_allsuccess_flag := FALSE;
622 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
623 print_debuginfo(l_module_name, 'At least one payment has '
624 || 'failed validation.');
625 END IF;
626 EXIT WHEN (1=1);
627 END IF;
628 END LOOP;
629
630 /*
631 * Check if all payments have failed for this
632 * payment request. This information is used below.
633 */
634 FOR i in x_paymentTab.FIRST .. x_paymentTab.LAST LOOP
635 IF (x_paymentTab(i).payment_status = PAY_STATUS_CREATED) THEN
636 l_allfailed_flag := FALSE;
637 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
638 print_debuginfo(l_module_name, 'At least one payment has '
639 || 'has been successfully validated.');
640 END IF;
641 EXIT WHEN (1=1);
642 END IF;
643 END LOOP;
644
645 /*
646 * Update the status of the payments/documents
647 * as per the rejection level (if necessary).
648 */
649 IF (p_rej_level = REJ_LVL_REQUEST) THEN
650
651 IF (l_allsuccess_flag = FALSE) THEN
652 /*
653 * This means that at least one payment in this
654 * payment request has failed.
655 *
656 * For 'request' rejection level:
657 * If any payment in the request fails validation,
658 * the entire payment request should be rejected;
659 * So fail all payments in this payment request.
660 */
661 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
662 print_debuginfo(l_module_name, 'Failing all payments and '
663 || 'documents for payment request '
664 || p_payreq_id);
665 END IF;
666 FOR i in x_paymentTab.FIRST .. x_paymentTab.LAST LOOP
667
668 IF (x_paymentTab(i).payment_status = PAY_STATUS_CREATED) THEN
669
670 x_paymentTab(i).payment_status :=
671 PAY_STATUS_FAIL_BY_REJLVL;
672
673 /*
674 * Once we fail a payment, we need to create
675 * an error record and insert this record
676 * into the errors table.
677 */
678 IBY_BUILD_UTILS_PKG.createErrorRecord(
679 TRXN_TYPE_PMT,
680 x_paymentTab(i).payment_id,
681 x_paymentTab(i).payment_status,
682 NULL,
683 x_paymentTab(i).payment_id,
684 NULL,
685 NULL,
686 NULL,
687 NULL,
688 NULL,
689 l_doc_err_rec,
690 x_errTokenTab,
691 l_triggering_pmt_id
692 );
693
694 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
695 l_doc_err_rec, x_docErrorTab, x_errTokenTab);
696
697 IBY_PAYGROUP_PUB.failDocsOfPayment(
698 x_paymentTab(i).payment_id,
699 DOC_STATUS_PAY_VAL_FAIL,
700 x_docsInPmtTab, x_docErrorTab,
701 x_errTokenTab);
702
703 END IF;
704
705 END LOOP;
706
707 /* set the status of the payment request to failed */
708 l_request_status := REQ_STATUS_FAIL_PAY_CR;
709
710 ELSE
711
712 /*
713 * For single payments, ignore the review proposed
714 * payments flag and blindly set the request status
715 * to 'payments created'.
716 */
717
718 /* set the status of the payment request to pmts created */
719 l_request_status := REQ_STATUS_PAY_CRTD;
720
721 END IF;
722
723 ELSIF (p_rej_level = REJ_LVL_PAYMENT) THEN
724
725 /*
726 * Check if all payments in the request have failed.
727 */
728 IF (l_allfailed_flag = TRUE) THEN
729
730 l_request_status := REQ_STATUS_FAIL_PAY_CR;
731
732 ELSE
733
734 /*
735 * At least one payment in the request was
736 * successful.
737 */
738
739 /*
740 * For single payments, ignore the review proposed
741 * payments flag and blindly set the payment
742 * status to 'payments created'.
743 */
744
745 /* set the status of the payment request to pmts created */
746 l_request_status := REQ_STATUS_PAY_CRTD;
747
748 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
749 print_debuginfo(l_module_name, 'Review proposed payments '
750 || 'flag has not been set. Setting status of successful '
751 || 'request to created.');
752
753 END IF;
754 END IF;
755
756 ELSIF (p_rej_level = REJ_LVL_NONE) THEN
757
758 /*
759 * For single payments, ignore the review proposed
760 * payments flag and the user review errors flag.
761 */
762 IF (l_allfailed_flag = TRUE) THEN
763 l_request_status := REQ_STATUS_FAIL_PAY_CR;
764 ELSE
765 l_request_status := REQ_STATUS_PAY_CRTD;
766 END IF;
767
768 ELSE
769
770 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
771 print_debuginfo(l_module_name, 'Unknown rejection level: '
772 || p_rej_level
773 || '. Aborting payment creation ..' );
774
775 END IF;
776 APP_EXCEPTION.RAISE_EXCEPTION;
777
778 END IF;
779
780 /*
781 * If this single payment has been created successfully,
782 * and the override completion point flag is set, then set
783 * the payment completed flag to 'yes'.
784 */
785 IF (x_allPmtsSuccessFlag = TRUE) THEN
786 IF (UPPER(p_override_compl_pt) = 'Y') THEN
787 FOR i IN x_paymentTab.FIRST .. x_paymentTab.LAST LOOP
788 x_paymentTab(i).payments_complete_flag := 'Y';
789 END LOOP;
790 END IF;
791 END IF;
792
793 /*
794 * All payments for this payment request have been
795 * created and stored in a PLSQL table. Now write these
796 * payments to the database.
797 */
798 IBY_PAYGROUP_PUB.updatePayments(x_paymentTab);
799
800 /*
801 * Update the documents table by providing a payment id to
802 * each document.
803 */
804 IBY_PAYGROUP_PUB.updateDocsWithPaymentID(x_docsInPmtTab);
805
806 /*
807 * If any payments/documents were failed, the IBY_TRANSACTION_
808 * ERRORS table must be populated with the corresponding error
809 * messages.
810 */
811 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N', x_docErrorTab,
812 x_errTokenTab);
813
814 /*
815 * Update the status of the payment request.
816 */
817 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
818 print_debuginfo(l_module_name, 'Updating status of payment request '
819 || p_payreq_id || ' to ' || l_request_status || '.');
820
821 END IF;
822 UPDATE
823 IBY_PAY_SERVICE_REQUESTS
824 SET
825 payment_service_request_status = l_request_status
826 WHERE
827 payment_service_request_id = p_payreq_id
828 ;
829
830 /* Pass back the request status to the caller */
831 x_return_status := l_request_status;
832
833 /*
834 * Pass the 'all payments success' and 'all payments
835 * failed' flags back to the caller.
836 *
837 * These flag will be used in raising business events.
838 */
839 x_allPmtsSuccessFlag := l_allsuccess_flag;
840 x_allPmtsFailedFlag := l_allfailed_flag;
841
842 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
843 print_debuginfo(l_module_name, 'EXIT');
844
845 END IF;
846 EXCEPTION
847
848 WHEN OTHERS THEN
849 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
850 print_debuginfo(l_module_name, 'Fatal: Exception when updating '
851 || 'payment request/payment/document status after payment '
852 || 'creation. All changes will be rolled back. Payment request '
853 || 'id is ' || p_payreq_id);
854 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
855 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
856
857 END IF;
858 /*
859 * Propogate exception to caller.
860 */
861 RAISE;
862
863 END performDBUpdates;
864
865 /*--------------------------------------------------------------------
866 | NAME:
867 | initializePmts
868 |
869 | PURPOSE:
870 | Sets the default attributes for a created payment such as
871 | payment status, process type etc.
872 |
873 | PARAMETERS:
874 | IN
875 |
876 |
877 | OUT
878 |
879 |
880 | RETURNS:
881 |
882 | NOTES:
883 |
884 *---------------------------------------------------------------------*/
885 PROCEDURE initializePmts(
886 x_paymentTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType
887 )
888 IS
889 BEGIN
890
891 IF (x_paymentTab.COUNT = 0) THEN
892 RETURN;
893 END IF;
894
895 FOR i IN x_paymentTab.FIRST .. x_paymentTab.LAST LOOP
896
897 x_paymentTab(i).payment_status := 'CREATED';
898 x_paymentTab(i).process_type := 'IMMEDIATE';
899 x_paymentTab(i).payments_complete_flag := 'N';
900 x_paymentTab(i).bill_payable_flag := 'N';
901 x_paymentTab(i).exclusive_payment_flag := 'N';
902 x_paymentTab(i).separate_remit_advice_req_flag := 'N';
903 x_paymentTab(i).declare_payment_flag := 'N';
904 x_paymentTab(i).pregrouped_payment_flag := 'N';
905 x_paymentTab(i).stop_confirmed_flag := 'N';
906 x_paymentTab(i).stop_released_flag := 'N';
907 x_paymentTab(i).stop_request_placed_flag := 'N';
908 x_paymentTab(i).created_by := fnd_global.user_id;
909 x_paymentTab(i).creation_date := sysdate;
910 x_paymentTab(i).last_updated_by := fnd_global.user_id;
911 x_paymentTab(i).last_update_login := fnd_global.user_id;
912 x_paymentTab(i).last_update_date := sysdate;
913 x_paymentTab(i).object_version_number := 1;
914
915 END LOOP;
916
917 END initializePmts;
918
919
920 /*--------------------------------------------------------------------
921 | NAME:
922 | negativePmtAmountCheck
923 |
924 | PURPOSE: Validation to check that payment amount is not a negative
925 |value
926 |
927 | PARAMETERS:
928 | IN
929 |
930 |
931 | OUT
932 |
933 |
934 | RETURNS:
935 |
936 | NOTES: Added for the bug 7344352
937 |
938 *---------------------------------------------------------------------*/
939 PROCEDURE negativePmtAmountCheck(
940 x_paymentTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
941 x_docsInPmtTab IN OUT NOCOPY IBY_PAYGROUP_PUB.docsInPaymentTabType,
942 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
943 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType
944 )
945 IS
946 l_module_name VARCHAR2(200) := G_PKG_NAME || '.negativePmtAmountCheck';
947 l_doc_err_rec IBY_TRANSACTION_ERRORS%ROWTYPE;
948 l_error_code VARCHAR2(100);
949 l_error_msg VARCHAR2(500);
950 l_token_rec IBY_TRXN_ERROR_TOKENS%ROWTYPE;
951 BEGIN
952 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
953 print_debuginfo(l_module_name, 'ENTER');
954
955 END IF;
956 FOR i in x_paymentTab.FIRST .. x_paymentTab.LAST LOOP
957
958 IF (x_paymentTab(i).payment_status = PAY_STATUS_CREATED) THEN
959
960 IF (x_paymentTab(i).payment_amount < 0) THEN
961
962 x_paymentTab(i).payment_status
963 := PAY_STATUS_REJECTED;
964
965 print_debuginfo(l_module_name, 'Failed payment '
966 || x_paymentTab(i).payment_id
967 || ' because payment amount '
968 || x_paymentTab(i).payment_amount
969 ||' is less than zero'
970 );
971
972 l_error_code := 'IBY_PMT_NEGATIVE_AMT';
973 FND_MESSAGE.set_name('IBY', l_error_code);
974
975 FND_MESSAGE.SET_TOKEN('PMT_AMOUNT',
976 x_paymentTab(i).payment_amount,
977 FALSE);
978
979 l_token_rec.token_name := 'PMT_AMOUNT';
980 l_token_rec.token_value := x_paymentTab(i).payment_amount;
981 x_errTokenTab(x_errTokenTab.COUNT + 1) := l_token_rec;
982
983 /*
984 * Once we fail a payment, we need to create
985 * an error record and insert this record
986 * into the errors table.
987 */
988 IBY_BUILD_UTILS_PKG.createPmtErrorRecord(
989 x_paymentTab(i).payment_id,
990 x_paymentTab(i).payment_status,
991 l_error_code,
992 FND_MESSAGE.get,
993 l_doc_err_rec
994 );
995
996 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
997 l_doc_err_rec, x_docErrorTab, x_errTokenTab);
998
999 /* fail the docs of this payment */
1000 IBY_PAYGROUP_PUB.failDocsOfPayment(x_paymentTab(i).payment_id,
1001 DOC_STATUS_PAY_VAL_FAIL, x_docsInPmtTab,
1002 x_docErrorTab, x_errTokenTab);
1003
1004 END IF;
1005 END IF;
1006 END LOOP;
1007 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1008 print_debuginfo(l_module_name, 'EXIT');
1009 END IF;
1010 END negativePmtAmountCheck;
1011
1012
1013
1014
1015 /*--------------------------------------------------------------------
1016 | NAME:
1017 | print_debuginfo
1018 |
1019 | PURPOSE:
1020 | This procedure prints the debug message to the concurrent manager
1021 | log file.
1022 |
1023 | PARAMETERS:
1024 | IN
1025 | p_debug_text - The debug message to be printed
1026 |
1027 | OUT
1028 |
1029 |
1030 | RETURNS:
1031 |
1032 | NOTES:
1033 |
1034 *---------------------------------------------------------------------*/
1035 PROCEDURE print_debuginfo(p_module IN VARCHAR2,
1036 p_debug_text IN VARCHAR2)
1037 IS
1038
1039 BEGIN
1040
1041 /*
1042 * Write the debug message to the concurrent manager log file.
1043 */
1044 iby_build_utils_pkg.print_debuginfo(p_module, p_debug_text);
1045
1046 END print_debuginfo;
1047
1048
1049 END IBY_SINGPAY_PUB;
[Home] [Help]