[Home] [Help]
PACKAGE BODY: APPS.IBY_DISBURSE_SINGLE_PMT_PKG
Source
1 PACKAGE BODY IBY_DISBURSE_SINGLE_PMT_PKG AS
2 /*$Header: ibysingb.pls 120.55.12010000.2 2008/08/15 21:49:37 visundar ship $*/
3
4 --
5 -- Declare global variables
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_DISBURSE_SINGLE_PMT_PKG';
8
9 --
10 -- List of instruction statuses that are used / set in this
11 -- module.
12 --
13 INS_STATUS_CREATED CONSTANT VARCHAR2(100) := 'CREATED';
14
15 --
16 -- List of payment request statuses that are used in this
17 -- module.
18 --
19 REQ_STATUS_INSERTED CONSTANT VARCHAR2(100) := 'INSERTED';
20 REQ_STATUS_SUBMITTED CONSTANT VARCHAR2(100) := 'SUBMITTED';
21 REQ_STATUS_ASGN_COMPLETE CONSTANT VARCHAR2(100) := 'ASSIGNMENT_COMPLETE';
22 REQ_STATUS_VALIDATED CONSTANT VARCHAR2(100) := 'DOCUMENTS_VALIDATED';
23 REQ_STATUS_RETRY_DOC_VALID CONSTANT VARCHAR2(100) :=
24 'RETRY_DOCUMENT_VALIDATION';
25 REQ_STATUS_PAY_CRTD CONSTANT VARCHAR2(100) := 'PAYMENTS_CREATED';
26 REQ_STATUS_RETRY_PMT_CREAT CONSTANT VARCHAR2(100) := 'RETRY_PAYMENT_CREATION';
27
28 --
29 -- List of payment statuses that are used / set in this
30 -- module.
31 --
32 PMT_STATUS_INS_CREATED CONSTANT VARCHAR2(100) := 'INSTRUCTION_CREATED';
33 PMT_STATUS_SETUP CONSTANT VARCHAR2(100) := 'VOID_BY_SETUP';
34 PMT_STATUS_OVERFLOW CONSTANT VARCHAR2(100) := 'VOID_BY_OVERFLOW';
35 PMT_STATUS_FAIL_VALID CONSTANT VARCHAR2(100) := 'FAILED_VALIDATION';
36 PMT_STATUS_ISSUED CONSTANT VARCHAR2(100) := 'ISSUED';
37 PMT_STATUS_FORMATTED CONSTANT VARCHAR2(100) := 'FORMATTED';
38
39 --
40 -- List of document statuses that are used / set in this
41 -- module.
42 --
43 DOC_STATUS_SUBMITTED CONSTANT VARCHAR2(100) := 'SUBMITTED';
44
45 --
46 -- List of rejection level system options that are used in
47 -- this module.
48 --
49 REJ_LVL_REQUEST CONSTANT VARCHAR2(100) := 'REQUEST';
50
51 -- Transaction types (for selection from IBY_TRANSACTION_ERRORS table)
52 TRXN_TYPE_DOC CONSTANT VARCHAR2(100) := 'DOCUMENT_PAYABLE';
53 TRXN_TYPE_PMT CONSTANT VARCHAR2(100) := 'PAYMENT';
54 TRXN_TYPE_INS CONSTANT VARCHAR2(100) := 'INSTRUCTION';
55
56 /*
57 * Paper document usage reasons.
58 */
59 DOC_USE_ISSUED CONSTANT VARCHAR2(100) := 'ISSUED';
60
61 /*--------------------------------------------------------------------
62 | NAME:
63 | submit_single_payment
64 |
65 | PURPOSE:
66 | Entry point for the single payment API; This procedure will return
67 | a success/failure response back to the caller synchronously.
68 |
69 | It is the callers responsibility to perform a COMMIT in case of
70 | success (rollback in case of failure). This API will not perform
71 | a commit.
72 |
73 | Functional blocks of this API include -
74 | - validate the provided document payable
75 | - create a payment
76 | - validate the payment
77 | - create a payment instruction
78 | - validate the payment instruction
79 | - invoke extract/format for the created instruction
80 | - mark the payment complete
81 |
82 | Single payments are automatically marked complete by this API.
83 | That is the understanding with the calling application as well.
84 |
85 | For this reason, single payments should not be again marked complete
86 | by the user (this is handled in the iPayment UI).
87 |
88 | Note 1: it is the calling applications responsibility to ensure
89 | that all the hardcoded grouping rules are applied on the documents
90 | payable *before* the single payment API is invoked. This is to
91 | ensure that the single payment API does not generate multiple
92 | payments after grouping the provided documents payable.
93 |
94 | If such a situation arises, the single payment API will fail the
95 | request.
96 |
97 | Note 2: Only one payment will be created by the single payment API
98 | regardless of the number of provided documents payable (this is
99 | ensured because of Note 1).
100 |
101 | Since only one payment is created, it also follows that the single
102 | payment API will only create a single payment instruction with this
103 | payment.
104 |
105 | PARAMETERS:
106 | IN
107 |
108 | p_api_version
109 |
110 | p_init_msg_list
111 |
112 | p_calling_app_id
113 | The 3-character product code of the calling application. Example,
114 | '200' for Oracle Payables.
115 |
116 | p_calling_app_payreq_cd
117 | Id of the payment service request from the calling app's
118 | point of view. For a given calling app, this id should be
119 | unique.
120 |
121 | p_is_manual_payment_flag
122 | Specifies whether this payment is a manual payment or
123 | a quick payment. Manual payments are payments made outside
124 | of Oracle Payments that need to be recorded. Manual
125 | payments do not undergo any validation, the given payment is
126 | simply inserted into IBY tables.
127 |
128 | p_payment_function
129 | Payment function. Used in setting the payee context.
130 |
131 | p_internal_bank_account_id
132 | The internal bank account to pay from.
133 |
134 | p_pay_process_profile_id
135 | Payment process profile. The payment profile drives how this
136 | payment is processed in IBY.
137 |
138 | p_payment_method_cd
139 | The payment method.
140 |
141 | p_legal_entity_id
142 | Legal entity.
143 |
144 | p_organization_id
145 | Org id. Used in setting the payee context.
146 |
147 | p_organization_type
148 | Org type. Used in setting the payee context.
149 |
150 | p_payment_date
151 | The payment date.
152 | Currently not used.
153 |
154 | p_payment_amount
155 | The payment amount.
156 |
157 | p_payment_currency
158 | The payment currency.
159 |
160 | p_payee_party_id
161 | Payee party id. Used in setting the payee context.
162 |
163 | p_payee_party_site_id
164 | Payee party site id. Used in setting the payee context.
165 |
166 | p_supplier_site_id
167 | Supplier site id. Used in setting the payee context.
168 |
169 | p_payee_bank_account_id
170 | Payee bank account id. Only relevant for electronic single payments.
171 | Currently not used.
172 |
173 | p_override_pmt_complete_pt
174 | Override completion point flag. If this flag is set to 'Y', IBY
175 | will immediately mark the single payment as completed without
176 | waiting for the pre-set completion event.
177 |
178 | p_bill_payable_flag
179 | Indicates whether this payment is a future-dated payment.
180 | Currently not used.
181 |
182 | p_anticipated_value_date
183 | Anticipated value date.
184 | Currently not used.
185 |
186 | p_maturity_date
187 | Payment maturity date/
188 | Required parameter if the payment is a future-dated payment.
189 | Currently not used.
190 |
191 | p_payment_document_id
192 | The paper document (check stock) to be used for numbering and
193 | printing of the payment. Only relevant for printed payments.
194 | If not provided, this value will be derived from the payment
195 | process profile.
196 |
197 | p_paper_document_number
198 | The number of the paper document (check number). Only relevant
199 | for printed single payments. If this value is not provided
200 | the next available paper document number will be used.
201 |
202 | p_printer_name
203 | Printer name is required if the payment needs to be printed
204 | immediately.
205 |
206 | p_print_immediate_flag
207 | Whether to print the payment immediately. If set to N, user
208 | will have to initiate printing from the IBY UI.
209 |
210 | p_transmit_immediate_flag
211 | Flag indicating whether this payment needs to be transmitted
212 | to the bank immediately upon formatting. Only relevant for
213 | electronic payments. If this param is set to N, user will have
214 | to initiate transmission from the IBY UI.
215 |
216 | p_payee_address_line1 .. p_payee_address_line4
217 | Payee address lines. If payee address information is
218 | provided as API params, then these would be used to create
219 | the payment. If not provided, the payment would be stamped
220 | with the address information derived from payee party site id.
221 |
222 | p_payee_address_city
223 | Payee city.
224 |
225 | p_payee_address_county
226 | Payee county.
227 |
228 | p_payee_address_state
229 | Payee state.
230 |
231 | p_payee_address_zip
232 | Payee postal code.
233 |
234 | p_payee_address_country
235 | Payee country.
236 |
237 | p_attribute_category
238 | Descriptive flex fields category.
239 | Currently not used.
240 |
241 | p_attribute1 .. p_attribute15
242 | Descriptive flex field attributes.
243 | Currently not used.
244 |
245 | OUT
246 |
247 | x_num_printed_docs
248 | Total number of printed documents generated after numbering.
249 | This will include the actual single payment [1 document] plus
250 | any setup and overflow documents.
251 |
252 | x_payment_id
253 | Payment id of the actual single payment. This value maps to
254 | IBY_PAYMENTS_ALL.payment_id.
255 |
256 | x_paper_doc_num
257 | Paper document number of the actual single payment. This could be
258 | a check number, for example.
259 |
260 | x_pmt_ref_num
261 | Payment reference number stamped by IBY on the actual single
262 | payment. Use this payment reference number when interacting with
263 | third parties e.g., banks.
264 |
265 | x_return_status
266 | Return status of the API.
267 |
268 | S - Success
269 | E - Error / failure
270 | U - Unexpected / system error
271 |
272 | x_error_ids_tab
273 | List of validation error ids that map to
274 | IBY_TRANSACTION_ERRORS.transaction_error_id. Use these
275 | error ids to look up this table for list of validation errors.
276 |
277 | This parameter is only relevant when the return status is E.
278 |
279 | x_msg_count
280 | Generated FND messages count.
281 |
282 | x_msg_data
283 | Generated FND messages. This param is only relevant in case
284 | the return status is U. Unwind the message stack to see list
285 | of exceptions / system errors.
286 |
287 | RETURNS:
288 |
289 | NOTES:
290 |
291 *---------------------------------------------------------------------*/
292 PROCEDURE submit_single_payment(
293 p_api_version IN NUMBER,
294 p_init_msg_list IN VARCHAR2,
295 p_calling_app_id IN NUMBER,
296 p_calling_app_payreq_cd IN VARCHAR2,
297 p_is_manual_payment_flag IN VARCHAR2,
298 p_payment_function IN VARCHAR2,
299 p_internal_bank_account_id IN NUMBER,
300 p_pay_process_profile_id IN NUMBER,
301 p_payment_method_cd IN VARCHAR2,
302 p_legal_entity_id IN NUMBER,
303 p_organization_id IN NUMBER,
304 p_organization_type IN VARCHAR2,
305 p_payment_date IN DATE,
306 p_payment_amount IN NUMBER,
307 p_payment_currency IN VARCHAR2,
308 p_payee_party_id IN NUMBER,
309 p_payee_party_site_id IN NUMBER DEFAULT NULL,
310 p_supplier_site_id IN NUMBER DEFAULT NULL,
311 p_payee_bank_account_id IN NUMBER,
312 p_override_pmt_complete_pt IN VARCHAR2,
313 p_bill_payable_flag IN VARCHAR2,
314 p_anticipated_value_date IN DATE DEFAULT NULL,
315 p_maturity_date IN DATE,
316 p_payment_document_id IN NUMBER,
317 p_paper_document_number IN NUMBER,
318 p_printer_name IN VARCHAR2,
319 p_print_immediate_flag IN VARCHAR2,
320 p_transmit_immediate_flag IN VARCHAR2,
321 p_payee_address_line1 IN VARCHAR2 DEFAULT NULL,
322 p_payee_address_line2 IN VARCHAR2 DEFAULT NULL,
323 p_payee_address_line3 IN VARCHAR2 DEFAULT NULL,
324 p_payee_address_line4 IN VARCHAR2 DEFAULT NULL,
325 p_payee_address_city IN VARCHAR2 DEFAULT NULL,
326 p_payee_address_county IN VARCHAR2 DEFAULT NULL,
327 p_payee_address_state IN VARCHAR2 DEFAULT NULL,
328 p_payee_address_zip IN VARCHAR2 DEFAULT NULL,
329 p_payee_address_country IN VARCHAR2 DEFAULT NULL,
330 p_attribute_category IN VARCHAR2 DEFAULT NULL,
331 p_attribute1 IN VARCHAR2 DEFAULT NULL,
332 p_attribute2 IN VARCHAR2 DEFAULT NULL,
333 p_attribute3 IN VARCHAR2 DEFAULT NULL,
334 p_attribute4 IN VARCHAR2 DEFAULT NULL,
335 p_attribute5 IN VARCHAR2 DEFAULT NULL,
336 p_attribute6 IN VARCHAR2 DEFAULT NULL,
337 p_attribute7 IN VARCHAR2 DEFAULT NULL,
338 p_attribute8 IN VARCHAR2 DEFAULT NULL,
339 p_attribute9 IN VARCHAR2 DEFAULT NULL,
340 p_attribute10 IN VARCHAR2 DEFAULT NULL,
341 p_attribute11 IN VARCHAR2 DEFAULT NULL,
342 p_attribute12 IN VARCHAR2 DEFAULT NULL,
343 p_attribute13 IN VARCHAR2 DEFAULT NULL,
344 p_attribute14 IN VARCHAR2 DEFAULT NULL,
345 p_attribute15 IN VARCHAR2 DEFAULT NULL,
346 x_num_printed_docs OUT NOCOPY NUMBER,
347 x_payment_id OUT NOCOPY NUMBER,
348 x_paper_doc_num OUT NOCOPY NUMBER,
349 x_pmt_ref_num OUT NOCOPY NUMBER,
350 x_return_status OUT NOCOPY VARCHAR2,
351 x_error_ids_tab OUT NOCOPY trxnErrorIdsTab,
352 x_msg_count OUT NOCOPY NUMBER,
353 x_msg_data OUT NOCOPY VARCHAR2
354 )
355 IS
356 l_return_status VARCHAR2 (100);
357 l_return_message VARCHAR2 (3000);
358 l_ret_status NUMBER;
359
360 l_payreq_status VARCHAR2 (100);
361 l_payreq_id IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE;
362 l_is_duplicate BOOLEAN := TRUE;
363 l_module_name VARCHAR2(200) := G_PKG_NAME ||
364 '.submit_single_payment';
365
366 l_api_version CONSTANT NUMBER := 1.0;
367 l_api_name CONSTANT VARCHAR2(30) := 'submit_single_payment';
368
369 l_errbuf VARCHAR2(5000);
370 l_retcode VARCHAR2(2000);
371
372 l_msg_count NUMBER;
373 l_msg_data VARCHAR2(4000);
374
375 /* hook related params */
376 l_pkg_name VARCHAR2(200);
377 l_callout_name VARCHAR2(500);
378 l_stmt VARCHAR2(1000);
379 l_error_code VARCHAR2(3000);
380
381 /* used in validating provided paper doc number */
382 l_paper_doc_num NUMBER;
383 l_last_issued_paper_doc_num NUMBER;
384 l_next_avlbl_paper_doc_num NUMBER;
385
386 /*
387 * If a single document in the request fails validation,
388 * fail the entire request.
389 */
390 l_document_rejection_level VARCHAR2(100) := REJ_LVL_REQUEST;
391 l_payment_rejection_level VARCHAR2(100) := REJ_LVL_REQUEST;
392
393 /* since the user has created the payment, there is no need to review it */
394 l_review_proposed_pmts_flag VARCHAR2(1) := 'N';
395
396 /* these are used in payment instruction creation */
397 l_profile_attribs IBY_DISBURSE_SUBMIT_PUB_PKG.profileProcessAttribs;
398 l_pmtInstrTab IBY_PAYINSTR_PUB.pmtInstrTabType;
399
400 /* used to record manual payments */
401 l_pmt_rec IBY_PAYMENTS_ALL%ROWTYPE;
402 l_pmts_tab IBY_PAYGROUP_PUB.paymentTabType;
403 l_payment_id IBY_PAYMENTS_ALL.payment_id%TYPE;
404
405 /* used to store access types for manual payment */
406 l_process_func_rec IBY_PROCESS_FUNCTIONS%ROWTYPE;
407 l_process_org_rec IBY_PROCESS_ORGS%ROWTYPE;
408
409 /* used to store transaction error ids that are returned to caller */
410 l_trxnErrorIdsTab trxnErrorIdsTab;
411
412 /* stores the created payment instruction */
413 l_pmtInstrRec IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE;
414
415 /* transmit immediately flag - for electronic payments */
416 l_transmit_now_flag VARCHAR2(1);
417
418 l_msg_index_out NUMBER;
419
420 /*
421 * Cursor to pick up all document errors that
422 * were generated during the processing of this single
423 * payment.
424 */
425 CURSOR c_doc_errors_list (p_payreq_id NUMBER)
426 IS
427 SELECT
428 err.transaction_error_id
429 FROM
430 IBY_TRANSACTION_ERRORS err,
431 IBY_DOCS_PAYABLE_ALL doc,
432 IBY_PAY_SERVICE_REQUESTS prq
433 WHERE
434 err.transaction_id = doc.document_payable_id AND
435 err.transaction_type = TRXN_TYPE_DOC AND
436 doc.payment_service_request_id = prq.payment_service_request_id AND
437 prq.payment_service_request_id = p_payreq_id
438 ;
439
440 /*
441 * Cursor to pick up all payment errors that
442 * were generated during the processing of this single
443 * payment.
444 */
445 CURSOR c_pmt_errors_list (p_payreq_id NUMBER)
446 IS
447 SELECT
448 err.transaction_error_id
449 FROM
450 IBY_TRANSACTION_ERRORS err,
451 IBY_PAYMENTS_ALL pmt,
452 IBY_PAY_SERVICE_REQUESTS prq
453 WHERE
454 err.transaction_id = pmt.payment_id AND
455 err.transaction_type = TRXN_TYPE_PMT AND
456 pmt.payment_service_request_id = prq.payment_service_request_id AND
457 prq.payment_service_request_id = p_payreq_id
458 ;
459
460 /*
461 * Cursor to pick up all payment instruction errors that
462 * were generated during the processing of this single
463 * payment.
464 */
465 CURSOR c_pmtinstr_errors_list (p_payreq_id NUMBER)
466 IS
467 SELECT
468 err.transaction_error_id
469 FROM
470 IBY_TRANSACTION_ERRORS err,
471 IBY_PAYMENTS_ALL pmt,
472 IBY_PAY_INSTRUCTIONS_ALL ins,
473 IBY_PAY_SERVICE_REQUESTS prq
474 WHERE
475 err.transaction_id = ins.payment_instruction_id AND
476 err.transaction_type = TRXN_TYPE_INS AND
477 pmt.payment_service_request_id = prq.payment_service_request_id AND
478 pmt.payment_instruction_id = ins.payment_instruction_id AND
479 prq.payment_service_request_id = p_payreq_id
480 ;
481
482 /*
483 * Implementing the callout is optional for the calling app.
484 * If the calling app does not implement the hook, then
485 * the call to the hook will result in ORA-06576 error.
486 *
487 * There is no exception name associated with this code, so
488 * we create one called 'PROCEDURE_NOT_IMPLEMENTED'. If this
489 * exception occurs, it is not fatal: we log the error and
490 * proceed.
491 *
492 * If, on the other hand, the calling app implements the
493 * callout, but the callout throws an exception, it is fatal
494 * and we must abort the program (this will be caught
495 * in WHEN OTHERS block).
496 */
497 PROCEDURE_NOT_IMPLEMENTED EXCEPTION;
498 PRAGMA EXCEPTION_INIT(PROCEDURE_NOT_IMPLEMENTED, -6576);
499
500 BEGIN
501
502 print_debuginfo(l_module_name, 'ENTER');
503
504 print_debuginfo(l_module_name, 'Calling app id: ' || p_calling_app_id);
505 print_debuginfo(l_module_name, 'Calling app pay req cd: '
506 || p_calling_app_payreq_cd);
507
508 /* standard call to check for api compatibility */
509 IF NOT FND_API.Compatible_API_Call(
510 l_api_version,
511 p_api_version,
512 l_api_name,
513 G_PKG_NAME) THEN
514
515 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
516 FND_MSG_PUB.Add;
517
518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
519
520 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
521
522 END IF;
523
524 /* initialize message list if p_init_msg_list is set to TRUE. */
525 IF FND_API.to_Boolean(p_init_msg_list) THEN
526 FND_MSG_PUB.initialize;
527 END IF;
528
529 /* initialize API return status to success */
530 x_return_status := FND_API.G_RET_STS_SUCCESS;
531
532 /*
533 * Check if parameters are correctly provided.
534 */
535
536 /*
537 * Maturity date is mandatory in case bill payable flag
538 * is set to 'Y'.
539 */
540 IF (UPPER(p_bill_payable_flag) = 'Y' AND p_maturity_date IS NULL) THEN
541
542 print_debuginfo(l_module_name, 'Maturity date is '
543 || 'mandatory if ''bill payable flag'' is set to '
544 || '''Y''.'
545 );
546
547 print_debuginfo(l_module_name, 'Single payment request '
548 || 'cannot be processed further. '
549 || 'Exiting ..');
550
551 /*
552 * Return error status and exit.
553 */
554 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
555
556 FND_MESSAGE.SET_NAME('IBY', 'IBY_MATURITY_DATE_REQD');
557 FND_MSG_PUB.ADD;
558
559 FND_MSG_PUB.COUNT_AND_GET(
560 p_count => x_msg_count,
561 p_data => x_msg_data
562 );
563
564 print_debuginfo(l_module_name, 'EXIT');
565
566 RETURN;
567
568 END IF;
569
570 print_debuginfo(l_module_name, '+--------------------------------------+');
571 print_debuginfo(l_module_name, '|STEP 1: Insert Payment Service Request|');
572 print_debuginfo(l_module_name, '+--------------------------------------+');
573
574 /*
575 * STEP 1:
576 *
577 * Insert payment request into IBY_PAY_SERVICE_REQUESTS
578 * table and generate payment request id.
579 */
580 BEGIN
581
582 /*
583 * First check whether this is a duplicate request.
584 *
585 * In the case a duplicate request, this function will
586 * return the previously generated payment request id.
587 *
588 * In the case of a new request, this function will
589 * return 0
590 */
591 l_payreq_id := IBY_DISBURSE_SUBMIT_PUB_PKG.
592 checkIfDuplicate(
593 p_calling_app_id,
594 p_calling_app_payreq_cd);
595
596 IF (l_payreq_id = 0) THEN
597 l_is_duplicate := FALSE;
598 END IF;
599
600 /*
601 * Insert the payment request only if it is not a duplicate.
602 */
603 IF (l_is_duplicate = FALSE) THEN
604 l_payreq_id := insert_payreq(
605 p_calling_app_id,
606 p_calling_app_payreq_cd,
607 p_internal_bank_account_id,
608 p_pay_process_profile_id,
609 p_is_manual_payment_flag
610 );
611
612 IF (l_payreq_id = -1) THEN
613
614 print_debuginfo(l_module_name, 'Could not insert payment '
615 || 'service request for calling app id '
616 || p_calling_app_id
617 || ', calling app payment service request cd '
618 || p_calling_app_payreq_cd
619 );
620
621 print_debuginfo(l_module_name, 'Single payment request '
622 || 'cannot be processed further. '
623 || 'Exiting ..');
624
625 /*
626 * Return error status and exit.
627 */
628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
629
630 FND_MESSAGE.SET_NAME('IBY', 'IBY_SINGPAY_INSERT_FAILED');
631 FND_MSG_PUB.ADD;
632
633 FND_MSG_PUB.COUNT_AND_GET(
634 p_count => x_msg_count,
635 p_data => x_msg_data
636 );
637
638 print_debuginfo(l_module_name, 'EXIT');
639
640 RETURN;
641
642 ELSE
643
644 /*
645 * Payment service request as successfully inserted
646 * into the DB. Commit at this point.
647 */
648 print_debuginfo(l_module_name, 'Payment service request '
649 || 'inserted successfully into the database.'
650 || 'Payment request id: '
651 || l_payreq_id);
652
653 END IF;
654
655 ELSE
656 print_debuginfo(l_module_name, 'Payment service '
657 || 'request '
658 || p_calling_app_payreq_cd
659 || ' is a duplicate. Skipping insert of request '
660 );
661
662 END IF; -- if not duplicate
663
664 END;
665
666
667 /*
668 * Check whether this is a manual payment. Manual
669 * payments are payments that have been directly
670 * from AP and simply needs to be recorded in IBY.
671 *
672 * Manual payments follow a special payment processing
673 * logic - no validations are necessary and no need
674 * to insert documents payable of the manual payment.
675 */
676 IF (UPPER(p_is_manual_payment_flag) = 'Y') THEN
677
678 print_debuginfo(l_module_name, 'This is a manual payment.');
679
680 /*
681 * Fix for bug 5237833:
682 *
683 * Manual payments can be electronic payments as
684 * well. So do not blindly validate the paper
685 * document number.
686 *
687 * If the processing type for the provided profile
688 * is ELECTRONIC, skip paper document number validation.
689 */
690
691 /*
692 * Get the processing type for the provided payment
693 * profile. This will determine whether this manual
694 * payment is electronic or printed.
695 */
696 IBY_DISBURSE_SUBMIT_PUB_PKG.get_profile_process_attribs(
697 p_pay_process_profile_id,
698 l_profile_attribs
699 );
700
701 /*
702 * Validate the paper document number only for 'printed'
703 * processing type.
704 */
705
706 IF (l_profile_attribs.processing_type = 'PRINTED') THEN
707
708 l_paper_doc_num := p_paper_document_number;
709 print_debuginfo(l_module_name, 'Provided '
710 || 'paper document number: '
711 || l_paper_doc_num
712 );
713
714 /*
715 * Call paper document number validation API.
716 */
717 IBY_DISBURSE_UI_API_PUB_PKG.validate_paper_doc_number(
718 l_api_version,
719 FND_API.G_FALSE,
720 p_payment_document_id,
721 l_paper_doc_num,
722 l_return_status,
723 x_msg_count,
724 x_msg_data
725 );
726
727 print_debuginfo(l_module_name, 'Return status after paper '
728 || 'document number validation: '
729 || l_return_status
730 );
731
732 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
733
734 print_debuginfo(l_module_name, 'Paper document number '
735 || l_paper_doc_num
736 || ' failed validation.'
737 );
738
739 print_debuginfo(l_module_name, 'Manual payment will '
740 || 'not be inserted into database. Returning failure '
741 || 'response.'
742 );
743
744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
745
746 print_debuginfo(l_module_name, 'EXIT');
747
748 RETURN;
749
750 ELSE
751
752 print_debuginfo(l_module_name, 'Paper document number '
753 || l_paper_doc_num
754 || ' passed validation.'
755 );
756
757 END IF;
758
759 /*
760 * If we reached here, it means that the provided paper
761 * document number is valid.
762 *
763 * Mark the provided paper document number as used so that
764 * it will not be available again.
765 */
766
767 /*
768 * STEP A:
769 *
770 * Update the last issued document number in
771 * CE_PAYMENT_DOCUMENTS table if the user
772 * provided paper document number is greater
773 * than the existing last issued paper doc num.
774 *
775 * Calling the validate_paper_doc_number(..) with
776 * a null value will provide us the next available
777 * paper doc number. By subtracting 1 from this value
778 * we get the last issued paper doc number.
779 */
780 print_debuginfo(l_module_name, 'Determining whether to update '
781 || 'last issued paper doc num for provided check stock ..'
782 );
783
784 IBY_DISBURSE_UI_API_PUB_PKG.validate_paper_doc_number(
785 l_api_version,
786 FND_API.G_FALSE,
787 p_payment_document_id,
788 l_next_avlbl_paper_doc_num,
789 l_return_status,
790 x_msg_count,
791 x_msg_data
792 );
793
794 l_last_issued_paper_doc_num := l_next_avlbl_paper_doc_num - 1;
795
796 IF (l_paper_doc_num > l_last_issued_paper_doc_num) THEN
797
798 print_debuginfo(l_module_name, 'Provided paper doc number '
799 || l_paper_doc_num
800 || ' is greater than the last issued paper doc num '
801 || l_last_issued_paper_doc_num
802 );
803
804
805 /*
806 * Update the check stock to reflect the latest used
807 * check number.
808 */
809 UPDATE
810 CE_PAYMENT_DOCUMENTS
811 SET
812 last_issued_document_number = l_paper_doc_num
813 WHERE
814 payment_document_id = p_payment_document_id
815 ;
816
817 print_debuginfo(l_module_name, 'Updated CE_PAYMENT_DOCUMENTS '
818 || 'table to use '
819 || l_paper_doc_num
820 || ' as last issued paper document number.'
821 );
822
823 ELSE
824
825 print_debuginfo(l_module_name, 'Provided paper doc number '
826 || l_paper_doc_num
827 || ' is less than the last issued paper doc num '
828 || l_last_issued_paper_doc_num
829 );
830
831 print_debuginfo(l_module_name, 'Last issued paper doc num '
832 || ' will not be updated.'
833 );
834
835 END IF;
836
837
838 /*
839 * STEP B:
840 *
841 * The IBY_USED_PAYMENT_DOCS table contains
842 * all the used paper document numbers.
843 *
844 * Insert the used document number into the used
845 * payment documents table.
846 */
847
848 print_debuginfo(l_module_name, 'Inserting paper document '
849 || 'number '
850 || l_paper_doc_num
851 || ' into IBY_USED_PAYMENT_DOCS table.'
852 );
853
854 INSERT INTO IBY_USED_PAYMENT_DOCS (
855 PAYMENT_DOCUMENT_ID,
856 USED_DOCUMENT_NUMBER,
857 DATE_USED,
858 DOCUMENT_USE,
859 CREATED_BY,
860 CREATION_DATE,
861 LAST_UPDATED_BY,
862 LAST_UPDATE_DATE,
863 LAST_UPDATE_LOGIN,
864 OBJECT_VERSION_NUMBER
865 )
866 VALUES (
867 p_payment_document_id,
868 l_paper_doc_num,
869 sysdate,
870 DOC_USE_ISSUED,
871 fnd_global.user_id,
872 sysdate,
873 fnd_global.user_id,
874 sysdate,
875 fnd_global.login_id,
876 1
877 );
878
879 ELSE
880
881 print_debuginfo(l_module_name, 'This is an electronic '
882 || 'manual payment. '
883 );
884
885 END IF; -- if processing type is PRINTED
886
887 print_debuginfo(l_module_name, 'Invoking payment recording logic ..');
888
889 IBY_PAYGROUP_PUB.getNextPaymentID(l_payment_id);
890
891 l_pmt_rec.payment_id := l_payment_id;
892
893 /*
894 * Fix for bug 4956141:
895 *
896 * Provide payment reference number for manual payments.
897 */
898 l_pmt_rec.
899 payment_reference_number := provide_pmt_reference_num();
900
901 IF (l_pmt_rec.payment_reference_number = -1) THEN
902
903 print_debuginfo(l_module_name, 'Unable to provide payment '
904 || 'reference for manual payment.'
905 );
906
907 print_debuginfo(l_module_name, 'Manual payment will '
908 || 'not be inserted into database. Returning failure '
909 || 'response.'
910 );
911
912 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
913
914 print_debuginfo(l_module_name, 'EXIT');
915
916 RETURN;
917
918 END IF;
919
920 l_pmt_rec.payment_method_code := p_payment_method_cd;
921 l_pmt_rec.payment_service_request_id := l_payreq_id;
922 l_pmt_rec.process_type := 'MANUAL';
923
924 /*
925 * The final status of the manual payment is dependant
926 * upon whether it is an printed or an electronic
927 * payment.
928 */
929 IF (l_profile_attribs.processing_type = 'PRINTED') THEN
930 l_pmt_rec.payment_status := PMT_STATUS_ISSUED;
931 ELSE
932 l_pmt_rec.payment_status := PMT_STATUS_FORMATTED;
933 END IF;
934
935 l_pmt_rec.payment_function := p_payment_function;
936 l_pmt_rec.payment_amount := p_payment_amount;
937 l_pmt_rec.payment_currency_code:= p_payment_currency;
938 l_pmt_rec.internal_bank_account_id := p_internal_bank_account_id;
939 l_pmt_rec.org_id := p_organization_id;
940 l_pmt_rec.org_type := p_organization_type;
941 l_pmt_rec.legal_entity_id := p_legal_entity_id;
942 l_pmt_rec.payments_complete_flag := 'Y';
943 l_pmt_rec.ext_payee_id := IBY_DISBURSE_SUBMIT_PUB_PKG.
944 derivePayeeIdFromContext(
945 p_payee_party_id,
946 p_payee_party_site_id,
947 p_supplier_site_id,
948 p_organization_id,
949 p_organization_type,
950 p_payment_function
951 );
952 l_pmt_rec.payee_party_id := p_payee_party_id;
953 l_pmt_rec.party_site_id := p_payee_party_site_id;
954 l_pmt_rec.supplier_site_id := p_supplier_site_id;
955 l_pmt_rec.payment_profile_id := p_pay_process_profile_id;
956 l_pmt_rec.payment_date := p_payment_date;
957 l_pmt_rec.anticipated_value_date := p_anticipated_value_date;
958
959 /*
960 * Fix for bug 5727990:
961 *
962 * If the payment type is electronic then set the
963 * paper document number to null (instead of -1).
964 */
965 IF (l_profile_attribs.processing_type = 'ELECTRONIC') THEN
966 l_pmt_rec.paper_document_number := NULL;
967 END IF;
968
969 l_pmt_rec.maturity_date := p_maturity_date;
970 l_pmt_rec.bill_payable_flag := p_bill_payable_flag;
971 l_pmt_rec.attribute_category := p_attribute_category;
972 l_pmt_rec.attribute1 := p_attribute1;
973 l_pmt_rec.attribute2 := p_attribute2;
974 l_pmt_rec.attribute3 := p_attribute3;
975 l_pmt_rec.attribute4 := p_attribute4;
976 l_pmt_rec.attribute5 := p_attribute5;
977 l_pmt_rec.attribute6 := p_attribute6;
978 l_pmt_rec.attribute7 := p_attribute7;
979 l_pmt_rec.attribute8 := p_attribute8;
980 l_pmt_rec.attribute9 := p_attribute9;
981 l_pmt_rec.attribute10 := p_attribute10;
982 l_pmt_rec.attribute11 := p_attribute11;
983 l_pmt_rec.attribute12 := p_attribute12;
984 l_pmt_rec.attribute13 := p_attribute13;
985 l_pmt_rec.attribute14 := p_attribute14;
986 l_pmt_rec.attribute15 := p_attribute15;
987
988 l_pmts_tab(l_pmts_tab.COUNT) := l_pmt_rec;
989
990 /*
991 * Insert manual payment into IBY_PAYMENTS_ALL
992 * table.
993 */
994 IBY_PAYGROUP_PUB.insertPayments(l_pmts_tab);
995
996 /*
997 * Fix for bug 5727990:
998 *
999 * Invoke auditPaymentData(..) to populate bank
1000 * related information on the payment record.
1001 */
1002 IBY_PAYGROUP_PUB.auditPaymentData(l_pmts_tab);
1003
1004 /*
1005 * Fix for bug 5337475:
1006 *
1007 * Derive the process org and process funtion from the
1008 * payment on this request and insert them as process
1009 * functions and process orgs associated with this
1010 * request.
1011 *
1012 * If this is not done, the UI will not allow the user
1013 * to see the manual payment.
1014 */
1015 /* process function for this manual payment */
1016 l_process_func_rec.payment_function := p_payment_function;
1017 l_process_func_rec.object_id := l_payreq_id;
1018 l_process_func_rec.object_type := 'PAYMENT_REQUEST';
1019
1020 INSERT INTO IBY_PROCESS_FUNCTIONS
1021 (
1022 object_id,
1023 object_type,
1024 payment_function
1025 )
1026 VALUES
1027 (
1028 l_process_func_rec.object_id,
1029 l_process_func_rec.object_type,
1030 l_process_func_rec.payment_function
1031 )
1032 ;
1033
1034 /* process org for this manual payment */
1035 l_process_org_rec.org_id := p_organization_id;
1036 l_process_org_rec.org_type := p_organization_type;
1037 l_process_org_rec.object_id := l_payreq_id;
1038 l_process_org_rec.object_type := 'PAYMENT_REQUEST';
1039
1040 INSERT INTO IBY_PROCESS_ORGS
1041 (
1042 object_id,
1043 object_type,
1044 org_id,
1045 org_type
1046 )
1047 VALUES
1048 (
1049 l_process_org_rec.object_id,
1050 l_process_org_rec.object_type,
1051 l_process_org_rec.org_id,
1052 l_process_org_rec.org_type
1053 )
1054 ;
1055
1056 print_debuginfo(l_module_name, 'Finished inserting '
1057 || 'access types for this manual payment ..'
1058 );
1059
1060 /*
1061 * If the payment has been recorded, return success.
1062 *
1063 * We will not do a commit here. It is the callers
1064 * responsibility to commit.
1065 */
1066 print_debuginfo(l_module_name, 'Manual payment '
1067 || 'has been recorded in IBY with payment id '
1068 || l_payment_id
1069 || ' [pmt reference number = '
1070 || l_pmt_rec.payment_reference_number
1071 || ']'
1072 );
1073
1074 /* return back the payment id to the caller */
1075 x_payment_id := l_payment_id;
1076
1077 /* Bug 7330978 - return back the payment reference number to the caller */
1078 x_pmt_ref_num := l_pmt_rec.payment_reference_number;
1079
1080 x_return_status := FND_API.G_RET_STS_SUCCESS;
1081
1082 /*
1083 * Log all the params that we are passing back
1084 * to the caller.
1085 */
1086 print_debuginfo(l_module_name,
1087 'List of params passed back to caller - ');
1088 print_debuginfo(l_module_name, 'x_num_printed_docs = '
1089 || x_num_printed_docs);
1090 print_debuginfo(l_module_name, 'x_payment_id = '
1091 || x_payment_id);
1092 print_debuginfo(l_module_name, 'x_paper_doc_num = '
1093 || x_paper_doc_num);
1094 print_debuginfo(l_module_name, 'x_pmt_ref_num = '
1095 || x_pmt_ref_num);
1096 print_debuginfo(l_module_name, 'x_return_status = '
1097 || x_return_status);
1098
1099 print_debuginfo(l_module_name, 'EXIT');
1100
1101 RETURN;
1102
1103 END IF; -- if manual payment
1104
1105
1106 print_debuginfo(l_module_name, '+------------------------+');
1107 print_debuginfo(l_module_name, '|STEP 2: Insert Documents|');
1108 print_debuginfo(l_module_name, '+------------------------+');
1109
1110 /*
1111 * STEP 2:
1112 *
1113 * Insert the documents of this payment request into the
1114 * IBY_DOCS_PAYABLE_ALL table.
1115 */
1116
1117 BEGIN
1118
1119 /*
1120 * Insert the payment request documents only if the
1121 * request is not a duplicate.
1122 */
1123 IF (l_is_duplicate = FALSE) THEN
1124 l_payreq_status := IBY_DISBURSE_SUBMIT_PUB_PKG.
1125 get_payreq_status(l_payreq_id);
1126
1127 IF (l_payreq_status = REQ_STATUS_INSERTED) THEN
1128
1129 l_ret_status := IBY_DISBURSE_SUBMIT_PUB_PKG.
1130 insert_payreq_documents(
1131 p_calling_app_id,
1132 p_calling_app_payreq_cd,
1133 l_payreq_id
1134 );
1135
1136 IF (l_ret_status = -1) THEN
1137
1138 print_debuginfo(l_module_name, 'Could not insert '
1139 || 'documents payable for payment service '
1140 || 'request. Calling app id '
1141 || p_calling_app_id
1142 || ', calling app payment service request cd '
1143 || p_calling_app_payreq_cd
1144 );
1145
1146 print_debuginfo(l_module_name, 'Single payment '
1147 || 'request cannot be processed further. '
1148 || 'Exiting ..');
1149
1150 /* store error ids in output param */
1151 retrieve_transaction_errors(
1152 l_payreq_id,
1153 x_error_ids_tab
1154 );
1155
1156 /*
1157 * Return error status and exit.
1158 */
1159 FND_MESSAGE.SET_NAME('IBY', 'IBY_SINGPAY_DOCS_FAILED');
1160 FND_MSG_PUB.ADD;
1161
1162 FND_MSG_PUB.COUNT_AND_GET(
1163 p_count => x_msg_count,
1164 p_data => x_msg_data
1165 );
1166
1167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1168 print_debuginfo(l_module_name, 'EXIT');
1169
1170 RETURN;
1171
1172 END IF;
1173
1174 END IF;
1175
1176 ELSE
1177 print_debuginfo(l_module_name, 'Payment service '
1178 || 'request '
1179 || p_calling_app_payreq_cd
1180 || ' is a duplicate. Skipping insert of documents '
1181 );
1182
1183
1184 END IF; -- if not duplicate
1185
1186 END;
1187
1188 /*
1189 * STEP 3:
1190 *
1191 * Call the build program functional flows one-by-one.
1192 */
1193
1194 print_debuginfo(l_module_name, '+-----------------------------------+');
1195 print_debuginfo(l_module_name, '|STEP 3A: Account/Profile Assignment|');
1196 print_debuginfo(l_module_name, '+-----------------------------------+');
1197
1198 /*
1199 * F4 - Account Profile / Assignment Flow
1200 *
1201 * Check if the payment requests is in 'submitted'
1202 * status, and assign default payment profiles/
1203 * internal bank accounts to each document in the
1204 * request, if the documents do not already have them.
1205 */
1206 BEGIN
1207 l_payreq_status := IBY_DISBURSE_SUBMIT_PUB_PKG.
1208 get_payreq_status(l_payreq_id);
1209
1210 IF (l_payreq_status = REQ_STATUS_SUBMITTED) THEN
1211 print_debuginfo(l_module_name, 'Found payment request '
1212 || l_payreq_id
1213 || ' in "submitted" status.'
1214 );
1215
1216 print_debuginfo(l_module_name, 'Going to perform '
1217 || 'assignments for payment req id: '
1218 || l_payreq_id);
1219
1220 IBY_ASSIGN_PUB.performAssignments(
1221 l_payreq_id,
1222 l_return_status);
1223
1224 print_debuginfo(l_module_name, 'Request status after '
1225 || 'assignments: ' || l_return_status);
1226
1227 END IF;
1228
1229 IF (l_return_status <> REQ_STATUS_ASGN_COMPLETE) THEN
1230
1231 /*
1232 * If assignments for single payment request are not
1233 * complete, set the return status to an error status.
1234 */
1235 print_debuginfo(l_module_name, 'Setting return status of API to '
1236 || FND_API.G_RET_STS_ERROR
1237 || ' because assignments were incomplete.'
1238 );
1239
1240 /* store error ids in output param */
1241 retrieve_transaction_errors(
1242 l_payreq_id,
1243 x_error_ids_tab
1244 );
1245
1246 /*
1247 * Return error status and exit.
1248 */
1249 print_debuginfo(l_module_name, 'Returning error response .. ');
1250 x_return_status := FND_API.G_RET_STS_ERROR;
1251 print_debuginfo(l_module_name, 'EXIT');
1252
1253 RETURN;
1254
1255 END IF;
1256
1257 EXCEPTION
1258
1259 WHEN OTHERS THEN
1260 print_debuginfo(l_module_name, 'Exception occured when performing '
1261 || 'assignments. Assignment flow will be aborted and no '
1262 || 'assignments will be committed for payment request '
1263 || l_payreq_id
1264 );
1265 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
1266 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
1267
1268 /* store error ids in output param */
1269 retrieve_transaction_errors(
1270 l_payreq_id,
1271 x_error_ids_tab
1272 );
1273
1274 /*
1275 * Return error status and exit.
1276 */
1277 FND_MESSAGE.SET_NAME('IBY', 'IBY_ASSIGNMENTS_FAILED');
1278 FND_MSG_PUB.ADD;
1279
1280 FND_MSG_PUB.COUNT_AND_GET(
1281 p_count => x_msg_count,
1282 p_data => x_msg_data
1283 );
1284
1285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1286 print_debuginfo(l_module_name, 'EXIT');
1287
1288 RETURN;
1289
1290 END;
1291
1292 print_debuginfo(l_module_name, '+----------------------------+');
1293 print_debuginfo(l_module_name, '|STEP 3B: Document Validation|');
1294 print_debuginfo(l_module_name, '+----------------------------+');
1295
1296 /*
1297 * F5 - Document Validation Flow (Part I)
1298 *
1299 * Check if the payment request is in 'ASSIGNMENT_COMPLETE' status.
1300 * 'ASSIGNMENT_COMPLETE' indicates that the all the data elements
1301 * required for building payments are present in the payment request.
1302 *
1303 * Validate the documents of thsi payment request.
1304 */
1305 BEGIN
1306
1307 l_payreq_status := IBY_DISBURSE_SUBMIT_PUB_PKG.
1308 get_payreq_status(l_payreq_id);
1309
1310 IF (l_payreq_status = REQ_STATUS_ASGN_COMPLETE) THEN
1311 print_debuginfo(l_module_name, 'Found payment request '
1312 || l_payreq_id
1313 || ' in "assignment complete" status.'
1314 );
1315
1316 print_debuginfo(l_module_name, 'Going to validate documents '
1317 || 'for payment request '
1318 || l_payreq_id);
1319
1320 IBY_VALIDATIONSETS_PUB.applyDocumentValidationSets(
1321 l_payreq_id,
1322 l_document_rejection_level,
1323 TRUE,
1324 l_return_status);
1325
1326 print_debuginfo(l_module_name, 'Request status after '
1327 || 'document validation: ' || l_return_status);
1328
1329 END IF;
1330
1331 IF (l_return_status <> REQ_STATUS_VALIDATED) THEN
1332
1333 /*
1334 * If documents of the single payment request were not
1335 * successfully validated set the return status to an
1336 * error status.
1337 */
1338 print_debuginfo(l_module_name, 'Setting return status of API to '
1339 || FND_API.G_RET_STS_ERROR
1340 || ' because document validations failed.'
1341 );
1342
1343 /* store error ids in output param */
1344 retrieve_transaction_errors(
1345 l_payreq_id,
1346 x_error_ids_tab
1347 );
1348
1349 /*
1350 * Return error status and exit.
1351 */
1352 print_debuginfo(l_module_name, 'Returning error response .. ');
1353 x_return_status := FND_API.G_RET_STS_ERROR;
1354 print_debuginfo(l_module_name, 'EXIT');
1355
1356 RETURN;
1357
1358 END IF;
1359
1360 EXCEPTION
1361
1362 WHEN OTHERS THEN
1363 print_debuginfo(l_module_name, 'Exception occured when validating '
1364 || 'documents. Document validation will be aborted and no '
1365 || 'document statuses will be committed for payment request '
1366 || l_payreq_id
1367 );
1368 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
1369 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
1370
1371 /* store error ids in output param */
1372 retrieve_transaction_errors(
1373 l_payreq_id,
1374 x_error_ids_tab
1375 );
1376
1377 /*
1378 * Return error status and exit.
1379 */
1380
1381 FND_MESSAGE.SET_NAME('IBY', 'IBY_DOC_VALIDATION_FAILED');
1382 FND_MSG_PUB.ADD;
1383
1384 FND_MSG_PUB.COUNT_AND_GET(
1385 p_count => x_msg_count,
1386 p_data => x_msg_data
1387 );
1388
1389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1390 print_debuginfo(l_module_name, 'EXIT');
1391
1392 RETURN;
1393
1394 END;
1395
1396 print_debuginfo(l_module_name, '+-------------------------+');
1397 print_debuginfo(l_module_name, '|STEP 3D: Payment Creation|');
1398 print_debuginfo(l_module_name, '+-------------------------+');
1399
1400 /*
1401 * F6 - Payment Creation Flow (Part I)
1402 *
1403 * Find all payment requests that are in 'validated' status
1404 * and create payments from the documents of such requests.
1405 */
1406
1407 BEGIN
1408 l_payreq_status := IBY_DISBURSE_SUBMIT_PUB_PKG.
1409 get_payreq_status(l_payreq_id);
1410
1411 IF (l_payreq_status = REQ_STATUS_VALIDATED) THEN
1412 print_debuginfo(l_module_name, 'Found payment request '
1413 || l_payreq_id
1414 || ' in "validated" status.'
1415 );
1416
1417 print_debuginfo(l_module_name, 'Going to create payments '
1418 || 'for payment request '
1419 || l_payreq_id);
1420
1421 IBY_SINGPAY_PUB.createPayments(
1422 l_payreq_id,
1423 l_payment_rejection_level,
1424 l_review_proposed_pmts_flag,
1425 p_override_pmt_complete_pt,
1426 p_bill_payable_flag,
1427 p_maturity_date,
1428 l_return_status);
1429
1430 print_debuginfo(l_module_name, 'Request status after payment '
1431 || 'creation: ' || l_return_status);
1432
1433 END IF;
1434
1435 IF (l_return_status <> REQ_STATUS_PAY_CRTD) THEN
1436
1437 /*
1438 * If documents of the single payment request were not
1439 * successfully grouped into a payment set the return
1440 * status to an error status.
1441 */
1442 print_debuginfo(l_module_name, 'Setting return status of API to '
1443 || FND_API.G_RET_STS_ERROR
1444 || ' because payment creation failed.'
1445 );
1446
1447 /* store error ids in output param */
1448 retrieve_transaction_errors(
1449 l_payreq_id,
1450 x_error_ids_tab
1451 );
1452
1453 /*
1454 * Return error status and exit.
1455 */
1456 print_debuginfo(l_module_name, 'Returning error response .. ');
1457 x_return_status := FND_API.G_RET_STS_ERROR;
1458 print_debuginfo(l_module_name, 'EXIT');
1459
1460 RETURN;
1461
1462 END IF;
1463
1464 EXCEPTION
1465
1466 WHEN OTHERS THEN
1467 print_debuginfo(l_module_name, 'Exception occured when '
1468 || 'building payments. Payment creation will be '
1469 || 'aborted and no payments will be committed for '
1470 || 'payment request '
1471 || l_payreq_id
1472 );
1473 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
1474 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
1475
1476 /* store error ids in output param */
1477 retrieve_transaction_errors(
1478 l_payreq_id,
1479 x_error_ids_tab
1480 );
1481
1482 /*
1483 * Return error status and exit.
1484 */
1485
1486 FND_MESSAGE.SET_NAME('IBY', 'IBY_PMT_CREATION_FAILED');
1487 FND_MSG_PUB.ADD;
1488
1489 FND_MSG_PUB.COUNT_AND_GET(
1490 p_count => x_msg_count,
1491 p_data => x_msg_data
1492 );
1493
1494 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1495 print_debuginfo(l_module_name, 'EXIT');
1496
1497 RETURN;
1498
1499 END;
1500
1501 /*
1502 * If we reached here, it means that the build program
1503 * finished successfully. Set the response message to
1504 * 'success'.
1505 */
1506 x_return_status := FND_API.G_RET_STS_SUCCESS;
1507
1508 l_payreq_status := IBY_DISBURSE_SUBMIT_PUB_PKG.
1509 get_payreq_status(l_payreq_id);
1510
1511 print_debuginfo(l_module_name, 'Final status of payment request '
1512 || l_payreq_id
1513 || ' (calling app pay req cd: '
1514 || p_calling_app_payreq_cd
1515 || ') before payment instruction creation is '
1516 || l_payreq_status
1517 );
1518
1519 /*
1520 * If we reached here, it means that payment creation was
1521 * successful. Invoke payment instruction creation.
1522 */
1523
1524 /*
1525 * Get the processing type for the provided payment
1526 * profile. This is needed for payment instruction
1527 * creation.
1528 */
1529 IBY_DISBURSE_SUBMIT_PUB_PKG.get_profile_process_attribs(
1530 p_pay_process_profile_id,
1531 l_profile_attribs
1532 );
1533
1534 /*
1535 * Before attempting to create payment instructions
1536 * check if the provided paper document number is
1537 * valid. If not, abort processing here.
1538 *
1539 * Fix for bug 5060974:
1540 *
1541 * Validate the paper document number only for 'printed'
1542 * processing type.
1543 */
1544
1545 IF (l_profile_attribs.processing_type = 'PRINTED') THEN
1546
1547 l_paper_doc_num := p_paper_document_number;
1548
1549 IBY_DISBURSE_UI_API_PUB_PKG.validate_paper_doc_number(
1550 l_api_version,
1551 FND_API.G_FALSE,
1552 p_payment_document_id,
1553 l_paper_doc_num,
1554 l_return_status,
1555 x_msg_count,
1556 x_msg_data,
1557 FND_API.G_FALSE
1558 );
1559
1560 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1561
1562 print_debuginfo(l_module_name, 'Paper document number '
1563 || l_paper_doc_num
1564 || ' failed validation. Aborting ..'
1565 );
1566
1567 APP_EXCEPTION.RAISE_EXCEPTION;
1568
1569 ELSE
1570
1571 print_debuginfo(l_module_name, 'Paper document number '
1572 || l_paper_doc_num
1573 || ' passed validation.'
1574 );
1575
1576 END IF;
1577
1578 ELSE
1579
1580 print_debuginfo(l_module_name, 'Not validating paper document '
1581 || 'number because processing type for profile '
1582 || p_pay_process_profile_id
1583 || ' is ELECTRONIC.'
1584 );
1585
1586 END IF; -- if processing type is 'PRINTED'
1587
1588 /*
1589 * If processing type is electronic, then we need to pass
1590 * the 'transmit now flag' with a valid value.
1591 *
1592 * If the caller has not provided a value for this flag
1593 * default the value based on the profile.
1594 */
1595 l_transmit_now_flag := p_transmit_immediate_flag;
1596
1597 IF (l_profile_attribs.processing_type = 'ELECTRONIC') THEN
1598
1599 IF (p_transmit_immediate_flag IS NULL) THEN
1600
1601 l_transmit_now_flag := l_profile_attribs.transmit_now_flag;
1602
1603 END IF;
1604
1605 END IF;
1606
1607 print_debuginfo(l_module_name, 'Invoking payment instruction '
1608 || 'creation using processing type: '
1609 || l_profile_attribs.processing_type
1610 );
1611
1612 /*
1613 * Now, create a payment instruction for the
1614 * inserted payment.
1615 */
1616 IBY_PAYINSTR_PUB.createPaymentInstructions(
1617 l_profile_attribs.processing_type,
1618 p_payment_document_id,
1619 p_printer_name,
1620 p_print_immediate_flag,
1621 l_transmit_now_flag,
1622 NULL, /* admin assigned ref */
1623 NULL, /* comments */
1624 NULL, /* pmt profile id */
1625 p_calling_app_id,
1626 p_calling_app_payreq_cd,
1627 l_payreq_id,
1628 p_internal_bank_account_id,
1629 p_payment_currency,
1630 p_legal_entity_id,
1631 p_organization_id,
1632 p_organization_type,
1633 NULL,
1634 NULL,
1635 'Y', /* single payments flow flag */
1636 l_pmtInstrTab,
1637 l_return_status,
1638 x_msg_count,
1639 x_msg_data
1640 );
1641
1642 print_debuginfo(l_module_name, 'Return status of payment '
1643 || 'instruction creation: '
1644 || l_return_status
1645 );
1646
1647 --IBY_BUILD_INSTRUCTIONS_PUB_PKG.build_pmt_instructions(
1648 -- l_errbuf,
1649 -- l_retcode,
1650 -- l_profile_attribs.processing_type,
1651 -- p_payment_document_id,
1652 -- p_print_immediate_flag,
1653 -- p_printer_name,
1654 -- NULL, /* transmit now flag */
1655 -- NULL, /* admin assigned ref */
1656 -- NULL, /* comments */
1657 -- NULL, /* pmt profile id */
1658 -- p_internal_bank_account_id,
1659 -- p_calling_app_id,
1660 -- p_calling_app_payreq_cd,
1661 -- p_payment_currency,
1662 -- p_legal_entity_id,
1663 -- p_organization_id,
1664 -- p_organization_type,
1665 -- NULL,
1666 -- NULL
1667 -- );
1668
1669 --print_debuginfo(l_module_name, 'Return code after payment '
1670 -- || 'instruction creation: '
1671 -- || l_retcode
1672 -- );
1673
1674 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1675
1676 /*
1677 * If the payment instruction was not successfully
1678 * created set the return status to an error status.
1679 */
1680 FND_MESSAGE.SET_NAME('IBY', 'IBY_INS_CREATION_FAILED');
1681 FND_MSG_PUB.ADD;
1682
1683 FND_MSG_PUB.COUNT_AND_GET(
1684 p_count => x_msg_count,
1685 p_data => x_msg_data
1686 );
1687
1688 /* store error ids in output param */
1689 retrieve_transaction_errors(
1690 l_payreq_id,
1691 x_error_ids_tab
1692 );
1693
1694 print_debuginfo(l_module_name, 'Returning error response '
1695 || 'because payment instruction creation failed .. '
1696 );
1697
1698 /*
1699 * Return error status and exit.
1700 */
1701 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1702 print_debuginfo(l_module_name, 'EXIT');
1703
1704 RETURN;
1705
1706 END IF;
1707
1708 /*
1709 * Since we are processing a single payment, only one
1710 * payment instruction is expected to be created.
1711 * Store the created payment instruction in the l_pmtInstrRec
1712 * record structure.
1713 */
1714 IF (l_pmtInstrTab.COUNT <> 1) THEN
1715
1716 /*
1717 * If multiple payment instruction instructions
1718 * were created, it is an error. Set the return
1719 * status to an error status.
1720 */
1721 FND_MESSAGE.SET_NAME('IBY', 'IBY_INS_CREATION_FAILED');
1722 FND_MSG_PUB.ADD;
1723
1724 FND_MSG_PUB.COUNT_AND_GET(
1725 p_count => x_msg_count,
1726 p_data => x_msg_data
1727 );
1728
1729 /* store error ids in output param */
1730 retrieve_transaction_errors(
1731 l_payreq_id,
1732 x_error_ids_tab
1733 );
1734
1735 print_debuginfo(l_module_name, 'Returning error response '
1736 || 'because multiple payment instructions were created .. '
1737 );
1738
1739 /*
1740 * Return error status and exit.
1741 */
1742 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1743 print_debuginfo(l_module_name, 'EXIT');
1744
1745 RETURN;
1746
1747 ELSE
1748
1749 print_debuginfo(l_module_name, 'Only one payment '
1750 || 'instruction was created as expected.'
1751 );
1752
1753 l_pmtInstrRec := l_pmtInstrTab(1);
1754
1755 END IF;
1756
1757 /*
1758 * If we reached here, it means that the payment instruction
1759 * creation program finished successfully. Invoke
1760 * check numbering if we are building payment instructions
1761 * of processing type 'printed'.
1762 */
1763 IF (l_profile_attribs.processing_type = 'PRINTED') THEN
1764
1765 /*
1766 * Invoke check numbering for the created payment
1767 * instruction.
1768 */
1769 IBY_CHECKNUMBER_PUB.performCheckNumbering(
1770 l_pmtInstrRec.payment_instruction_id,
1771 p_payment_document_id,
1772 p_paper_document_number,
1773 l_return_status,
1774 l_return_message,
1775 x_msg_count,
1776 x_msg_data
1777 );
1778
1779 print_debuginfo(l_module_name, 'After numbering, '
1780 || 'return status: '
1781 || l_return_status
1782 || ', and return message: '
1783 || l_return_message
1784 );
1785
1786 /*
1787 * If check numbering did not succeed return with an
1788 * error.
1789 */
1790 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1791
1792 /*
1793 * Fix for bug 5327347:
1794 *
1795 * Only populate the FND message stack if we
1796 * do not already have a message for the user.
1797 *
1798 * Otherwise, the user gets innundated with error
1799 * messages.
1800 */
1801 IF (FND_MSG_PUB.COUNT_MSG = 0) THEN
1802
1803 FND_MESSAGE.SET_NAME('IBY', 'IBY_INS_NUMBERING_ERROR');
1804 FND_MSG_PUB.ADD;
1805
1806 FND_MSG_PUB.COUNT_AND_GET(
1807 p_count => x_msg_count,
1808 p_data => x_msg_data
1809 );
1810
1811 END IF;
1812
1813 /* store error ids in output param */
1814 retrieve_transaction_errors(
1815 l_payreq_id,
1816 x_error_ids_tab
1817 );
1818
1819 print_debuginfo(l_module_name, 'Returning error response '
1820 || 'because check numbering failed .. '
1821 );
1822
1823 /*
1824 * Return error status and exit.
1825 */
1826 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1827 print_debuginfo(l_module_name, 'EXIT');
1828
1829 RETURN;
1830
1831 END IF;
1832
1833 ELSE
1834
1835 print_debuginfo(l_module_name, 'Processing type is '
1836 || l_profile_attribs.processing_type
1837 || '. Numbering of paper documents skipped ..'
1838 );
1839
1840 /*
1841 * WITHHOLDING HOOK:
1842 *
1843 * Fix for bug 6706749:
1844 *
1845 * If we reached here, it means the processing type
1846 * is electronic.
1847 *
1848 * For electronic payment instructions, check numbering
1849 * not required. Instead, directly call the extracting
1850 * and formatting programs.
1851 *
1852 * However, just before calling the extract/format program,
1853 * we need to invoke the withholding certificates hook.
1854 * For printed payments, this is already being done
1855 * when the checks are numbered. For electronic payments,
1856 * we need to explicitly add this call.
1857 */
1858
1859 IF (l_pmtInstrRec.payment_instruction_status =
1860 INS_STATUS_CREATED) THEN
1861
1862 l_pkg_name := 'AP_AWT_CALLOUT_PKG';
1863 l_callout_name := l_pkg_name
1864 || '.'
1865 || 'zx_witholdingCertificatesHook';
1866
1867 print_debuginfo(l_module_name,
1868 'Attempting to call hook: '
1869 || l_callout_name
1870 );
1871
1872 l_stmt := 'CALL '|| l_callout_name
1873 || '(:1, :2, :3, :4, :5, :6, :7, :8)';
1874
1875 BEGIN
1876
1877 EXECUTE IMMEDIATE
1878 (l_stmt)
1879 USING
1880 IN l_pmtInstrRec.payment_instruction_id,
1881 IN 'GENERATE',
1882 IN l_api_version,
1883 IN FND_API.G_FALSE,
1884 IN FND_API.G_FALSE,
1885 OUT l_return_status,
1886 OUT l_msg_count,
1887 OUT l_msg_data
1888 ;
1889
1890 print_debuginfo(l_module_name,
1891 'Finished invoking hook: '
1892 || l_callout_name
1893 || ', return status: '
1894 || l_return_status
1895 );
1896
1897 /*
1898 * If the called procedure did not return success,
1899 * raise an exception.
1900 */
1901 IF (l_return_status IS NULL OR
1902 l_return_status <> FND_API.G_RET_STS_SUCCESS)
1903 THEN
1904
1905 print_debuginfo(l_module_name,
1906 'Fatal: External app callout '''
1907 || l_callout_name
1908 || ''', returned failure status - '
1909 || l_return_status
1910 || '. Raising exception.'
1911 );
1912
1913 APP_EXCEPTION.RAISE_EXCEPTION;
1914
1915 END IF;
1916
1917
1918 EXCEPTION
1919
1920 WHEN PROCEDURE_NOT_IMPLEMENTED THEN
1921 print_debuginfo(l_module_name,
1922 'Callout "'
1923 || l_callout_name
1924 || '" not implemented by application - AP'
1925 );
1926
1927 print_debuginfo(l_module_name,
1928 'Skipping hook call.');
1929
1930 WHEN OTHERS THEN
1931
1932 print_debuginfo(l_module_name,
1933 'Fatal: External app '
1934 || 'callout '''
1935 || l_callout_name
1936 || ''', generated exception.'
1937 );
1938
1939 l_error_code := 'IBY_INS_AWT_CERT_HOOK_FAILED';
1940 FND_MESSAGE.set_name('IBY', l_error_code);
1941
1942 FND_MESSAGE.SET_TOKEN('CALLOUT',
1943 l_callout_name,
1944 FALSE);
1945
1946 /*
1947 * Set the message on the error stack
1948 * to display in UI (in case of direct
1949 * API call from UI).
1950 */
1951 FND_MSG_PUB.ADD;
1952
1953 FND_MSG_PUB.COUNT_AND_GET(
1954 p_count => x_msg_count,
1955 p_data => x_msg_data
1956 );
1957
1958 print_debuginfo(l_module_name, 'Raising exception '
1959 || 'because withholding certificates hook '
1960 || 'returned an error .. '
1961 );
1962
1963 RAISE;
1964
1965 END;
1966
1967 END IF; -- if instruction status = 'created'
1968
1969 END IF; -- if processing type = 'printed'
1970
1971 /*
1972 * If we reached here, it means that the payments of the
1973 * payment instruction were numbered successfully (in the
1974 * case of printed payments).
1975 *
1976 * Populate the API output params such as the payment reference
1977 * number, paper document number etc. with the data from the
1978 * created single payment.
1979 */
1980 populateOutParams(
1981 l_payreq_id,
1982 l_profile_attribs.processing_type,
1983 x_num_printed_docs,
1984 x_payment_id,
1985 x_paper_doc_num,
1986 x_pmt_ref_num
1987 );
1988
1989 /*
1990 * Invoke the set of post-payment instruction
1991 * creation programs that are responsible for
1992 * extracting, formatting and printing the
1993 * payment instruction data.
1994 */
1995 BEGIN
1996
1997 print_debuginfo(l_module_name, 'Invoking '
1998 || 'extract and format programs for '
1999 || 'instruction '
2000 || l_pmtInstrRec.payment_instruction_id
2001 );
2002
2003 IBY_FD_POST_PICP_PROGS_PVT.
2004 Run_Post_PI_Programs(
2005 l_pmtInstrRec.payment_instruction_id,
2006 'N'
2007 );
2008
2009 print_debuginfo(l_module_name, 'Extract '
2010 || 'and format operation completed.'
2011 );
2012
2013 EXCEPTION
2014 WHEN OTHERS THEN
2015
2016 print_debuginfo(l_module_name, 'Extract and '
2017 || 'format operation generated '
2018 || 'exception for payment instruction '
2019 || l_pmtInstrRec.payment_instruction_id
2020 );
2021
2022 print_debuginfo(l_module_name, 'SQL code: '
2023 || SQLCODE);
2024 print_debuginfo(l_module_name, 'SQL err msg: '
2025 || SQLERRM);
2026
2027 FND_MESSAGE.SET_NAME('IBY', 'IBY_INS_BACKEND_ERROR');
2028
2029 FND_MESSAGE.SET_TOKEN('INS_ID',
2030 l_pmtInstrRec.payment_instruction_id,
2031 FALSE);
2032
2033 FND_MSG_PUB.ADD;
2034
2035 FND_MSG_PUB.COUNT_AND_GET(
2036 p_count => x_msg_count,
2037 p_data => x_msg_data
2038 );
2039
2040 /* store error ids in output param */
2041 retrieve_transaction_errors(
2042 l_payreq_id,
2043 x_error_ids_tab
2044 );
2045
2046 print_debuginfo(l_module_name, 'Returning error response '
2047 || 'because extract and format operation failed for '
2048 || 'created payment instruction .. '
2049 );
2050
2051 /*
2052 * Return error status and exit.
2053 */
2054 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2055 print_debuginfo(l_module_name, 'EXIT');
2056
2057 RETURN;
2058
2059 END;
2060
2061 /*
2062 * If we reached here, it means that the single payment
2063 * request has been processed successfully.
2064 *
2065 * Finalize the instruction status (mark all payments of
2066 * instruction as completed) and return success response.
2067 */
2068
2069 /*
2070 * The finalize API is only meant for printed payment
2071 * instructions. For electronic payment instructions,
2072 * call the mark payments complete API directly.
2073 */
2074 IF (l_profile_attribs.processing_type = 'PRINTED') THEN
2075
2076 print_debuginfo(l_module_name, 'Finalizing printed pmt instruction '
2077 || 'and pmt statuses ..');
2078
2079 IBY_DISBURSE_UI_API_PUB_PKG.finalize_instr_print_status(
2080 l_pmtInstrRec.payment_instruction_id,
2081 x_return_status
2082 );
2083
2084 ELSE
2085
2086 print_debuginfo(l_module_name, 'Finalizing electronic pmt instruction '
2087 || 'and pmt statuses ..');
2088
2089 IBY_DISBURSE_UI_API_PUB_PKG.finalize_electronic_instr(
2090 l_pmtInstrRec.payment_instruction_id,
2091 x_return_status
2092 );
2093
2094 END IF;
2095
2096 print_debuginfo(l_module_name, 'Return status after finalize call '
2097 || x_return_status);
2098
2099 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2100 print_debuginfo(l_module_name, 'Returning success response ..');
2101 ELSE
2102 print_debuginfo(l_module_name, 'Returning error response: '
2103 || x_return_status);
2104
2105 print_debuginfo(l_module_name, 'Unable to finalize payment '
2106 || 'instruction. Aborting ..'
2107 );
2108
2109 /*
2110 * Return error status and exit.
2111 */
2112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2113
2114 FND_MESSAGE.SET_NAME('IBY', 'IBY_INS_FINALIZE_STATUS_FAIL');
2115 FND_MESSAGE.SET_TOKEN('INS_ID',
2116 l_pmtInstrRec.payment_instruction_id,
2117 FALSE);
2118 FND_MSG_PUB.ADD;
2119
2120 FND_MSG_PUB.COUNT_AND_GET(
2121 p_count => x_msg_count,
2122 p_data => x_msg_data
2123 );
2124
2125 print_debuginfo(l_module_name, 'EXIT');
2126
2127 RETURN;
2128
2129 END IF;
2130
2131 print_debuginfo(l_module_name, 'List of params passed back to caller - ');
2132 print_debuginfo(l_module_name, 'x_num_printed_docs = '
2133 || x_num_printed_docs);
2134 print_debuginfo(l_module_name, 'x_payment_id = '
2135 || x_payment_id);
2136 print_debuginfo(l_module_name, 'x_paper_doc_num = '
2137 || x_paper_doc_num);
2138 print_debuginfo(l_module_name, 'x_pmt_ref_num = '
2139 || x_pmt_ref_num);
2140 print_debuginfo(l_module_name, 'x_return_status = '
2141 || x_return_status);
2142
2143 print_debuginfo(l_module_name, 'EXIT');
2144
2145 EXCEPTION
2146
2147 WHEN OTHERS THEN
2148 print_debuginfo(l_module_name, 'Exception occured when '
2149 || 'processing single payment. Single payment creation will '
2150 || 'be aborted and no records will be committed for '
2151 || 'payment request '
2152 || l_payreq_id
2153 );
2154 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
2155 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
2156
2157 /* store error ids in output param */
2158 retrieve_transaction_errors(
2159 l_payreq_id,
2160 x_error_ids_tab
2161 );
2162
2163 /*
2164 * Return error status and exit.
2165 */
2166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2167 print_debuginfo(l_module_name, 'EXIT');
2168
2169 RETURN;
2170
2171 END submit_single_payment;
2172
2173 /*--------------------------------------------------------------------
2174 | NAME:
2175 | insert_payreq
2176 |
2177 |
2178 | PURPOSE:
2179 |
2180 |
2181 | PARAMETERS:
2182 | IN
2183 |
2184 |
2185 | OUT
2186 |
2187 |
2188 | RETURNS:
2189 |
2190 | NOTES:
2191 |
2192 *---------------------------------------------------------------------*/
2193 FUNCTION insert_payreq (
2194 p_calling_app_id IN IBY_PAY_SERVICE_REQUESTS.calling_app_id%TYPE,
2195 p_calling_app_payreq_cd IN IBY_PAY_SERVICE_REQUESTS.
2196 call_app_pay_service_req_code%TYPE,
2197 p_internal_bank_account_id
2198 IN IBY_PAY_SERVICE_REQUESTS.
2199 internal_bank_account_id%TYPE,
2200 p_pay_process_profile_id
2201 IN IBY_PAY_SERVICE_REQUESTS.
2202 payment_profile_id%TYPE,
2203 p_is_manual_payment_flag IN VARCHAR2
2204 )
2205 RETURN NUMBER
2206 IS
2207
2208 l_payreq_id IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE;
2209 l_module_name VARCHAR2(200) := G_PKG_NAME || '.insert_payreq';
2210
2211 BEGIN
2212
2213 print_debuginfo(l_module_name, 'ENTER');
2214
2215 l_payreq_id := IBY_DISBURSE_SUBMIT_PUB_PKG.getNextPayReqId();
2216
2217 print_debuginfo(l_module_name, 'Generated payment request id: '
2218 || l_payreq_id);
2219
2220 /*
2221 * Insert the payment request into IBY_PAY_SERVICE_REQUESTS
2222 * table. Supply defaults for values not provided by the
2223 * calling app.
2224 */
2225 INSERT INTO IBY_PAY_SERVICE_REQUESTS (
2226 CALLING_APP_ID,
2227 CREATED_BY,
2228 CREATION_DATE,
2229 LAST_UPDATED_BY,
2230 LAST_UPDATE_DATE,
2231 LAST_UPDATE_LOGIN,
2232 OBJECT_VERSION_NUMBER,
2233 CALL_APP_PAY_SERVICE_REQ_CODE,
2234 PAYMENT_SERVICE_REQUEST_STATUS,
2235 PAYMENT_SERVICE_REQUEST_ID,
2236 PROCESS_TYPE,
2237 INTERNAL_BANK_ACCOUNT_ID,
2238 PAYMENT_PROFILE_ID,
2239 ALLOW_ZERO_PAYMENTS_FLAG
2240 )
2241 VALUES(
2242 p_calling_app_id,
2243 fnd_global.user_id,
2244 sysdate,
2245 fnd_global.user_id,
2246 sysdate,
2247 fnd_global.login_id,
2248 1,
2249 p_calling_app_payreq_cd,
2250 REQ_STATUS_INSERTED,
2251 l_payreq_id,
2252 DECODE(p_is_manual_payment_flag, 'Y', 'MANUAL', 'IMMEDIATE'),
2253 p_internal_bank_account_id,
2254 p_pay_process_profile_id,
2255 'Y'
2256 );
2257
2258 print_debuginfo(l_module_name, 'EXIT');
2259 RETURN l_payreq_id;
2260
2261 EXCEPTION
2262 WHEN OTHERS THEN
2263 print_debuginfo(l_module_name, 'Exception occured when '
2264 || 'inserting payment request status for '
2265 || 'calling app id '
2266 || p_calling_app_id
2267 || ', calling app payment service request cd '
2268 || p_calling_app_payreq_cd
2269 );
2270 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
2271 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
2272
2273 l_payreq_id := -1;
2274 print_debuginfo(l_module_name, 'Returning -1 for pay req id');
2275 print_debuginfo(l_module_name, 'EXIT');
2276 RETURN l_payreq_id;
2277
2278 END insert_payreq;
2279
2280 /*--------------------------------------------------------------------
2281 | NAME:
2282 | populateOutParams
2283 |
2284 | PURPOSE:
2285 |
2286 |
2287 | PARAMETERS:
2288 | IN
2289 |
2290 |
2291 | OUT
2292 |
2293 |
2294 | RETURNS:
2295 |
2296 | NOTES:
2297 | This method is only meant for single payments flow.
2298 |
2299 *---------------------------------------------------------------------*/
2300 PROCEDURE populateOutParams(
2301 p_payreq_id IN IBY_PAY_SERVICE_REQUESTS.
2302 payment_service_request_id%TYPE,
2303 p_processing_type IN IBY_PAYMENT_PROFILES.processing_type%TYPE,
2304 x_num_printed_docs OUT NOCOPY NUMBER,
2305 x_payment_id OUT NOCOPY IBY_PAYMENTS_ALL.
2306 payment_id%TYPE,
2307 x_paper_doc_num OUT NOCOPY IBY_PAYMENTS_ALL.
2308 paper_document_number%TYPE,
2309 x_pmt_ref_num OUT NOCOPY IBY_PAYMENTS_ALL.
2310 payment_reference_number%TYPE
2311 )
2312 IS
2313
2314 l_module_name VARCHAR2(200) := G_PKG_NAME || '.populateOutParams';
2315
2316 BEGIN
2317
2318 print_debuginfo(l_module_name, 'ENTER');
2319
2320 print_debuginfo(l_module_name, 'Provided params - pay req id: '
2321 || p_payreq_id
2322 || ', processing type: '
2323 || p_processing_type
2324 );
2325
2326 IF (p_processing_type = 'ELECTRONIC') THEN
2327
2328 /*
2329 * Fix for bug 5249885:
2330 *
2331 * Since AP displays the paper document number
2332 * in the UI, it is better to pass this value
2333 * as null (display blank) rather than -1.
2334 */
2335 x_paper_doc_num := NULL;
2336 x_num_printed_docs := -1;
2337 x_pmt_ref_num := -1;
2338 x_payment_id := -1;
2339
2340 BEGIN
2341
2342 /*
2343 * Get the payment reference number of the created
2344 * payment. We expect only one payment to be created
2345 * because this is an electronic single payment (so,
2346 * no setup and overflow payments will be created).
2347 */
2348
2349 /*
2350 * Fix for bug 5179465:
2351 *
2352 * The final status for electronic single payments
2353 * will be FORMATTED.
2354 *
2355 * Use this status when quering for the attributes
2356 * of the single payment.
2357 */
2358
2359 /*
2360 *
2361 * Fix for bug 5225777:
2362 *
2363 * The populateOutParams(..) method is called before
2364 * the payment status is set to FORMATTED by the
2365 * finalize_electronic_instr(..) method. Therefore,
2366 * is is safest to check for both the FORMATTED and
2367 * the INSTRUCTION_CREATED statuses for payments.
2368 */
2369 SELECT
2370 pmt.payment_id,
2371 pmt.payment_reference_number
2372 INTO
2373 x_payment_id,
2374 x_pmt_ref_num
2375 FROM
2376 IBY_PAYMENTS_ALL pmt
2377 WHERE
2378 pmt.payment_service_request_id = p_payreq_id AND
2379 pmt.payment_status IN
2380 (
2381 PMT_STATUS_FORMATTED,
2382 PMT_STATUS_INS_CREATED
2383 )
2384 ;
2385
2386 print_debuginfo(l_module_name, 'For payment request '
2387 || p_payreq_id
2388 || ' an electronic single payment with '
2389 || 'payment id '
2390 || x_payment_id
2391 || ' and payment reference number '
2392 || x_pmt_ref_num
2393 || ' has been created.'
2394 );
2395
2396 EXCEPTION
2397
2398 WHEN OTHERS THEN
2399
2400 print_debuginfo(l_module_name, 'Exception occured when '
2401 || 'retrieving payment reference number for '
2402 || 'single payment with pay req id '
2403 || p_payreq_id
2404 );
2405
2406 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
2407 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
2408
2409 APP_EXCEPTION.RAISE_EXCEPTION;
2410
2411 END;
2412
2413 ELSIF (p_processing_type = 'PRINTED') THEN
2414
2415 x_paper_doc_num := -1;
2416 x_num_printed_docs := -1;
2417 x_pmt_ref_num := -1;
2418 x_payment_id := -1;
2419
2420 BEGIN
2421
2422 /*
2423 * Get the payment reference number and paper document
2424 * number of the created payment. Multiple payments can
2425 * created because this is a printed single payment (so,
2426 * setup and overflow payments will be created).
2427 *
2428 * The setup and overflow payments will have status
2429 * VOID_BY_SETUP and VOID_BY_OVERFLOW respectively.
2430 * By selecting the payment with status
2431 * INSTRUCTION_CREATED we will be picking up only
2432 * the actual payment.
2433 */
2434 SELECT
2435 pmt.payment_id,
2436 pmt.payment_reference_number,
2437 pmt.paper_document_number
2438 INTO
2439 x_payment_id,
2440 x_pmt_ref_num,
2441 x_paper_doc_num
2442 FROM
2443 IBY_PAYMENTS_ALL pmt
2444 WHERE
2445 pmt.payment_service_request_id = p_payreq_id AND
2446 pmt.payment_status = PMT_STATUS_INS_CREATED
2447 ;
2448
2449 /*
2450 * Get the count of the paper documents that are
2451 * needed to print the created single payment.
2452 *
2453 * This count will include setup payments + overflow
2454 * payments + actual single payment.
2455 */
2456 SELECT
2457 count(*)
2458 INTO
2459 x_num_printed_docs
2460 FROM
2461 IBY_PAYMENTS_ALL
2462 WHERE
2463 payment_service_request_id = p_payreq_id AND
2464 payment_status IN (
2465 PMT_STATUS_INS_CREATED,
2466 PMT_STATUS_SETUP,
2467 PMT_STATUS_OVERFLOW)
2468 ;
2469
2470 print_debuginfo(l_module_name, 'For payment request '
2471 || p_payreq_id
2472 || ' a printed single payment with '
2473 || 'payment id '
2474 || x_payment_id
2475 || ' and payment reference number '
2476 || x_pmt_ref_num
2477 || ' has been created.'
2478 );
2479
2480 print_debuginfo(l_module_name, 'Paper document number '
2481 || 'of created single payment is '
2482 || x_paper_doc_num
2483 || '.'
2484 );
2485
2486 EXCEPTION
2487
2488 WHEN OTHERS THEN
2489
2490 print_debuginfo(l_module_name, 'Exception occured when '
2491 || 'retrieving payment reference number for '
2492 || 'single payment with pay req id '
2493 || p_payreq_id
2494 );
2495
2496 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
2497 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
2498
2499 APP_EXCEPTION.RAISE_EXCEPTION;
2500
2501 END;
2502
2503 ELSE
2504
2505 print_debuginfo(l_module_name, 'Unknown processing type: '
2506 || p_processing_type
2507 || ' has been provided. Cannot proceed. Aborting ..'
2508 );
2509
2510 APP_EXCEPTION.RAISE_EXCEPTION;
2511
2512 END IF;
2513
2514 print_debuginfo(l_module_name, 'EXIT');
2515
2516 END populateOutParams;
2517
2518 /*--------------------------------------------------------------------
2519 | NAME:
2520 | retrieve_transaction_errors
2521 |
2522 |
2523 | PURPOSE:
2524 |
2525 |
2526 | PARAMETERS:
2527 | IN
2528 |
2529 |
2530 | OUT
2531 |
2532 |
2533 | RETURNS:
2534 |
2535 | NOTES:
2536 |
2537 *---------------------------------------------------------------------*/
2538 PROCEDURE retrieve_transaction_errors(
2539 p_payreq_id IN IBY_PAY_SERVICE_REQUESTS.
2540 payment_service_request_id%TYPE,
2541 x_trxnErrorIdsTab IN OUT NOCOPY trxnErrorIdsTab
2542 )
2543 IS
2544
2545 /*
2546 * Cursor to pick up all document errors that
2547 * were generated during the processing of this single
2548 * payment.
2549 */
2550 CURSOR c_doc_errors_list (p_payreq_id NUMBER)
2551 IS
2552 SELECT
2553 err.transaction_error_id
2554 FROM
2555 IBY_TRANSACTION_ERRORS err,
2556 IBY_DOCS_PAYABLE_ALL doc,
2557 IBY_PAY_SERVICE_REQUESTS prq
2558 WHERE
2559 err.transaction_id = doc.document_payable_id AND
2560 err.transaction_type = TRXN_TYPE_DOC AND
2561 doc.payment_service_request_id = prq.payment_service_request_id AND
2562 prq.payment_service_request_id = p_payreq_id
2563 ;
2564
2565 /*
2566 * Cursor to pick up all payment errors that
2567 * were generated during the processing of this single
2568 * payment.
2569 */
2570 CURSOR c_pmt_errors_list (p_payreq_id NUMBER)
2571 IS
2572 SELECT
2573 err.transaction_error_id
2574 FROM
2575 IBY_TRANSACTION_ERRORS err,
2576 IBY_PAYMENTS_ALL pmt,
2577 IBY_PAY_SERVICE_REQUESTS prq
2578 WHERE
2579 err.transaction_id = pmt.payment_id AND
2580 err.transaction_type = TRXN_TYPE_PMT AND
2581 pmt.payment_service_request_id = prq.payment_service_request_id AND
2582 prq.payment_service_request_id = p_payreq_id
2583 ;
2584
2585 /*
2586 * Cursor to pick up all payment instruction errors that
2587 * were generated during the processing of this single
2588 * payment.
2589 */
2590 CURSOR c_pmtinstr_errors_list (p_payreq_id NUMBER)
2591 IS
2592 SELECT
2593 err.transaction_error_id
2594 FROM
2595 IBY_TRANSACTION_ERRORS err,
2596 IBY_PAYMENTS_ALL pmt,
2597 IBY_PAY_INSTRUCTIONS_ALL ins,
2598 IBY_PAY_SERVICE_REQUESTS prq
2599 WHERE
2600 err.transaction_id = ins.payment_instruction_id AND
2601 err.transaction_type = TRXN_TYPE_INS AND
2602 pmt.payment_service_request_id = prq.payment_service_request_id AND
2603 pmt.payment_instruction_id = ins.payment_instruction_id AND
2604 prq.payment_service_request_id = p_payreq_id
2605 ;
2606
2607 l_trxnErrorIdsTab trxnErrorIdsTab;
2608 l_module_name VARCHAR2(200) := G_PKG_NAME ||
2609 '.retrieve_transaction_errors';
2610
2611 BEGIN
2612
2613 print_debuginfo(l_module_name, 'ENTER');
2614 print_debuginfo(l_module_name, 'Payment request id: ' || p_payreq_id);
2615
2616 /*
2617 * Pick up all the documents errors for the single payment.
2618 */
2619 print_debuginfo(l_module_name, 'Retrieving document errors ..');
2620 OPEN c_doc_errors_list(p_payreq_id);
2621 FETCH c_doc_errors_list BULK COLLECT INTO l_trxnErrorIdsTab;
2622 CLOSE c_doc_errors_list;
2623
2624 IF (l_trxnErrorIdsTab.COUNT <> 0) THEN
2625
2626 FOR i in l_trxnErrorIdsTab.FIRST .. l_trxnErrorIdsTab.LAST LOOP
2627
2628 print_debuginfo(l_module_name, 'Trxn error id for doc: '
2629 || l_trxnErrorIdsTab(i).trxn_error_id
2630 );
2631
2632 x_trxnErrorIdsTab(x_trxnErrorIdsTab.COUNT + 1) :=
2633 l_trxnErrorIdsTab(i);
2634
2635 END LOOP;
2636
2637 ELSE
2638
2639 print_debuginfo(l_module_name, 'No document errors were '
2640 || 'generated.');
2641
2642 END IF;
2643
2644 /*
2645 * Pick up all the payment errors for the single payment.
2646 */
2647 print_debuginfo(l_module_name, 'Retrieving payment errors ..');
2648 OPEN c_pmt_errors_list(p_payreq_id);
2649 FETCH c_pmt_errors_list BULK COLLECT INTO l_trxnErrorIdsTab;
2650 CLOSE c_pmt_errors_list;
2651
2652 IF (l_trxnErrorIdsTab.COUNT <> 0) THEN
2653
2654 FOR i in l_trxnErrorIdsTab.FIRST .. l_trxnErrorIdsTab.LAST LOOP
2655
2656 print_debuginfo(l_module_name, 'Trxn error id for pmt: '
2657 || l_trxnErrorIdsTab(i).trxn_error_id);
2658
2659 x_trxnErrorIdsTab(x_trxnErrorIdsTab.COUNT + 1) :=
2660 l_trxnErrorIdsTab(i);
2661
2662 END LOOP;
2663
2664 ELSE
2665
2666 print_debuginfo(l_module_name, 'No payment errors were '
2667 || 'generated.');
2668
2669 END IF;
2670
2671 /*
2672 * Pick up all the payment instruction errors for the single payment.
2673 */
2674 print_debuginfo(l_module_name, 'Retrieving payment instruction errors ..');
2675 OPEN c_pmtinstr_errors_list(p_payreq_id);
2676 FETCH c_pmtinstr_errors_list BULK COLLECT INTO l_trxnErrorIdsTab;
2677 CLOSE c_pmtinstr_errors_list;
2678
2679 IF (l_trxnErrorIdsTab.COUNT <> 0) THEN
2680
2681 FOR i in l_trxnErrorIdsTab.FIRST .. l_trxnErrorIdsTab.LAST LOOP
2682
2683 print_debuginfo(l_module_name, 'Trxn error id for ins: '
2684 || l_trxnErrorIdsTab(i).trxn_error_id);
2685
2686 x_trxnErrorIdsTab(x_trxnErrorIdsTab.COUNT + 1) :=
2687 l_trxnErrorIdsTab(i);
2688
2689 END LOOP;
2690
2691 ELSE
2692
2693 print_debuginfo(l_module_name, 'No payment instruction errors '
2694 || 'were generated.');
2695
2696 END IF;
2697
2698 print_debuginfo(l_module_name, 'Transaction error list populated '
2699 || 'with '
2700 || x_trxnErrorIdsTab.COUNT
2701 || ' errors.'
2702 );
2703
2704 print_debuginfo(l_module_name, 'EXIT');
2705
2706 EXCEPTION
2707
2708 /*
2709 * This method is called whenever there are errors in the
2710 * processing cycle. This method may itself generate
2711 * exception, in which case the user will not get any
2712 * useful error messages.
2713 *
2714 * Any exceptions generated by this method should be handled
2715 * gracefully, so that the single payment process returns
2716 * with an appropriate error status.
2717 */
2718 WHEN OTHERS THEN
2719
2720 print_debuginfo(l_module_name, 'Exception occured when '
2721 || 'retrieving transaction error messages. '
2722 );
2723 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
2724 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
2725
2726 print_debuginfo(l_module_name, 'Handling exception gracefully '
2727 || 'to allow process to complete ..'
2728 );
2729
2730 END retrieve_transaction_errors;
2731
2732 /*--------------------------------------------------------------------
2733 | NAME:
2734 | provide_pmt_reference_num
2735 |
2736 |
2737 | PURPOSE:
2738 |
2739 |
2740 | PARAMETERS:
2741 | IN
2742 |
2743 |
2744 | OUT
2745 |
2746 |
2747 | RETURNS:
2748 |
2749 | NOTES:
2750 |
2751 *---------------------------------------------------------------------*/
2752 FUNCTION provide_pmt_reference_num
2753 RETURN NUMBER
2754 IS
2755 PRAGMA AUTONOMOUS_TRANSACTION;
2756
2757 l_module_name VARCHAR2(200) := G_PKG_NAME
2758 || '.provide_pmt_reference_num';
2759 l_ret_val NUMBER(15);
2760
2761 l_last_used_ref_num NUMBER := 0;
2762 l_anticipated_last_ref_num NUMBER := 0;
2763
2764
2765 BEGIN
2766
2767 print_debuginfo(l_module_name, 'ENTER');
2768
2769 /*
2770 * Select the payment reference information from
2771 * the IBY_PAYMENT_REFERENCES table.
2772 */
2773 SELECT
2774 NVL(last_used_ref_number, -1)
2775 INTO
2776 l_last_used_ref_num
2777 FROM
2778 IBY_PAYMENT_REFERENCES
2779 FOR UPDATE
2780 ;
2781
2782 IF (l_last_used_ref_num = -1) THEN
2783
2784 print_debuginfo(l_module_name, 'Payment reference information '
2785 || 'not setup. Last used ref number: '
2786 || l_last_used_ref_num
2787 || '. Cannot continue. Aborting ..'
2788 );
2789
2790 APP_EXCEPTION.RAISE_EXCEPTION;
2791
2792 END IF;
2793
2794 /*
2795 * Since we are numbering one payment, add one to the
2796 * last used ref number.
2797 */
2798 l_anticipated_last_ref_num := l_last_used_ref_num + 1;
2799 l_ret_val := l_anticipated_last_ref_num;
2800
2801 /*
2802 * Update the last used ref number and commit. So that
2803 * other concurrent instances, now get the updated last
2804 * used ref number.
2805 */
2806 UPDATE
2807 IBY_PAYMENT_REFERENCES
2808 SET
2809 last_used_ref_number = l_anticipated_last_ref_num;
2810
2811 /*
2812 * End the autonomous transaction.
2813 */
2814 COMMIT;
2815
2816 print_debuginfo(l_module_name, 'Payment reference returned: '
2817 || l_ret_val);
2818 print_debuginfo(l_module_name, 'EXIT');
2819
2820 RETURN l_ret_val;
2821
2822 EXCEPTION
2823 WHEN OTHERS THEN
2824
2825 print_debuginfo(l_module_name, 'Exception occured when '
2826 || 'providing payment reference'
2827 );
2828 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
2829 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
2830
2831 /*
2832 * End autonomous transaction.
2833 */
2834 ROLLBACK;
2835
2836 l_ret_val := -1;
2837
2838 print_debuginfo(l_module_name, 'Payment reference returned: '
2839 || l_ret_val);
2840 print_debuginfo(l_module_name, 'EXIT');
2841
2842 RETURN l_ret_val;
2843
2844 END provide_pmt_reference_num;
2845
2846
2847 /*--------------------------------------------------------------------
2848 | NAME:
2849 | print_debuginfo
2850 |
2851 | PURPOSE:
2852 |
2853 |
2854 | PARAMETERS:
2855 | IN
2856 |
2857 |
2858 | OUT
2859 |
2860 |
2861 | RETURNS:
2862 |
2863 | NOTES:
2864 |
2865 *---------------------------------------------------------------------*/
2866 PROCEDURE print_debuginfo(p_module IN VARCHAR2,
2867 p_debug_text IN VARCHAR2)
2868 IS
2869
2870 BEGIN
2871
2872 /*
2873 * Write the debug message to the concurrent manager log file.
2874 */
2875 iby_build_utils_pkg.print_debuginfo(p_module, p_debug_text);
2876
2877 END print_debuginfo;
2878
2879 END IBY_DISBURSE_SINGLE_PMT_PKG;