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