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