[Home] [Help]
PACKAGE BODY: APPS.IBY_DISBURSE_SUBMIT_PUB_PKG
Source
1 PACKAGE BODY IBY_DISBURSE_SUBMIT_PUB_PKG AS
2 /*$Header: ibybildb.pls 120.79.12010000.4 2008/12/02 20:55:03 pschalla ship $*/
3
4 --
5 -- Declare global variables
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_DISBURSE_SUBMIT_PUB_PKG';
8
9 --
10 -- List of payment request statuses that are used in this
11 -- module.
12 --
13 REQ_STATUS_INSERTED CONSTANT VARCHAR2(100) := 'INSERTED';
14 REQ_STATUS_SUBMITTED CONSTANT VARCHAR2(100) := 'SUBMITTED';
15 REQ_STATUS_ASGN_COMPLETE CONSTANT VARCHAR2(100) := 'ASSIGNMENT_COMPLETE';
16 REQ_STATUS_VALIDATED CONSTANT VARCHAR2(100) := 'DOCUMENTS_VALIDATED';
17 REQ_STATUS_RETRY_DOC_VALID CONSTANT VARCHAR2(100) :=
18 'RETRY_DOCUMENT_VALIDATION';
19 REQ_STATUS_PEN_REV_DOC_VAL CONSTANT VARCHAR2(100) :=
20 'PENDING_REVIEW_DOC_VAL_ERRORS';
21 REQ_STATUS_PEN_REV_PMT_VAL CONSTANT VARCHAR2(100) :=
22 'PENDING_REVIEW_PMT_VAL_ERRORS';
23 REQ_STATUS_RETRY_PMT_CREAT CONSTANT VARCHAR2(100) := 'RETRY_PAYMENT_CREATION';
24 REQ_STATUS_PMT_CRTD CONSTANT VARCHAR2(100) := 'PAYMENTS_CREATED';
25 REQ_STATUS_USER_REVW CONSTANT VARCHAR2(100) := 'PENDING_REVIEW';
26
27 --
28 -- List of instruction statuses that are used / set in this
29 -- module.
30 --
31 INS_STATUS_CREATED CONSTANT VARCHAR2(100) := 'CREATED';
32
33 --
34 -- List of payment statuses that are used / set in this
35 -- module.
36 --
37 PAY_STATUS_CREATED CONSTANT VARCHAR2(100) := 'CREATED';
38 PAY_STATUS_MOD_BNK_ACC CONSTANT VARCHAR2(100) :=
39 'MODIFIED_PAYEE_BANK_ACCOUNT';
40 PAY_STATUS_MODIFIED CONSTANT VARCHAR2(100) := 'MODIFIED';
41
42 --
43 -- List of document statuses that are used / set in this
44 -- module.
45 --
46 DOC_STATUS_SUBMITTED CONSTANT VARCHAR2(100) := 'SUBMITTED';
47 DOC_STATUS_VALIDATED CONSTANT VARCHAR2(100) := 'VALIDATED';
48 DOC_STATUS_RDY_FOR_VAL CONSTANT VARCHAR2(100) := 'READY_FOR_VALIDATION';
49 DOC_STATUS_FAILED_VAL CONSTANT VARCHAR2(100) := 'FAILED_VALIDATION';
50
51
52 TYPE payee_id_tbl_type is table of NUMBER index by VARCHAR2(200);
53 l_payee_id_tbl payee_id_tbl_type;
54
55 /*--------------------------------------------------------------------
56 | NAME:
57 | submit_payment_process_request
58 |
59 | PURPOSE:
60 | This is the top level procedure of the build program; This
61 | procedure will run as a concurrent program.
62 |
63 | PARAMETERS:
64 | IN
65 |
66 | p_calling_app_id
67 | The 3-character product code of the calling application
68 | (e.g., '200' for Oracle Payables).
69 |
70 | p_calling_app_payreq_cd
71 | Id of the payment service request from the calling app's
72 | point of view. For a given calling app, this id should be
73 | unique; the build program will communicate back to the calling
74 | app using this payment request id.
75 |
76 | p_internal_bank_account_id
77 | The internal bank account to pay from. Normally, the
78 | internal bank account is a document level attribute.
79 | If provided, all the documents payable of this payment
80 | service request will be assigned with this internal bank
81 | account by default. Note that if the document payable already
82 | had an internal bank account, the request level internal bank
83 | account will be used to overwrite it.
84 |
85 | p_payment_profile_id
86 | Payment profile for this payment request. Normally, the
87 | payment profile is a document level attribute. If provided
88 | at the request level, then all the documents payable of
89 | this payment service request will be assigned with this
90 | payment profile (regardless of whether the documents already
91 | have their own profiles).
92 |
93 | p_allow_zero_payments_flag
94 | 'Y' / 'N' flag indicating whether zero value payments are allowed.
95 | If not set, this value will be defaulted to 'N'. This value
96 | will be used in validating the documents payable of this request.
97 |
98 | p_maximum_payment_amount
99 | Maximum allowed amount for a payment created from the documents
100 | payable of this request. Payments will be validated against this
101 | ceiling.
102 |
103 | p_minimum_payment_amount
104 | Minimum allowed amount for payment created from the documents
105 | payable of this request. Payments will be validated against this
106 | floor.
107 |
108 | p_document_rejection_level
109 | Document rejection level system option. The value of this system
110 | option determines how validation failures at the document level
111 | are handled. For example, if a single document fails for the
112 | request, then all the other documents of the request will be
113 | automatically failed if the rejection level is set to 'REQUEST'.
114 |
115 | p_payment_rejection_level
116 | Payment rejection level system option. The value of this system
117 | option determines how validation failures at the payment level
118 | are handled. For example, if a single payment fails validation
119 | then all the payments of the request will be automatically
120 | failed if the rejection level is set to 'REQUEST'.
121 |
122 | p_review_proposed_pmts_flag
123 | Review proposed payments flag. After payments are created, they
124 | could be automatically picked by the PICP and put into payment
125 | instructions. In case the user wants to review the created payments
126 | first before they are allowed to be put into payment instructions,
127 | then this flag must be set to 'Y'.
128 |
129 | p_create_instrs_flag
130 | 'Y' / 'N' flag indicating whether payment instruction creation
131 | should be invoked for this payment service request as soon the
132 | Build Program completes.
133 |
134 | p_args13 - p_args100
135 | These 88 parameters are mandatory for any stored procedure
136 | that is submitted from Oracle Forms as a concurrent request
137 | (to get the total number of args to the concurrent procedure
138 | to 100).
139 |
140 | OUT
141 |
142 | x_errbuf
143 | x_retcode
144 |
145 | These two are mandatory output paramaters for a concurrent
146 | program. They will store the error message and error code
147 | to indicate a successful/failed run of the concurrent request.
148 |
149 | RETURNS:
150 |
151 | NOTES:
152 |
153 *---------------------------------------------------------------------*/
154 PROCEDURE submit_payment_process_request(
155 x_errbuf OUT NOCOPY VARCHAR2,
156 x_retcode OUT NOCOPY VARCHAR2,
157 p_calling_app_id IN VARCHAR2,
158 p_calling_app_payreq_cd IN VARCHAR2,
159 p_internal_bank_account_id IN VARCHAR2 DEFAULT NULL,
160 p_payment_profile_id IN VARCHAR2 DEFAULT NULL,
161 p_allow_zero_payments_flag IN VARCHAR2 DEFAULT 'N',
162 p_maximum_payment_amount IN VARCHAR2 DEFAULT NULL,
163 p_minimum_payment_amount IN VARCHAR2 DEFAULT NULL,
164 p_document_rejection_level IN VARCHAR2 DEFAULT NULL,
165 p_payment_rejection_level IN VARCHAR2 DEFAULT NULL,
166 p_review_proposed_pmts_flag IN VARCHAR2 DEFAULT 'X',
167 p_create_instrs_flag IN VARCHAR2 DEFAULT 'N',
168 p_payment_document_id IN VARCHAR2 DEFAULT NULL,
169 p_attribute_category IN VARCHAR2 DEFAULT NULL, p_attribute1 IN VARCHAR2 DEFAULT NULL,
170 p_attribute2 IN VARCHAR2 DEFAULT NULL, p_attribute3 IN VARCHAR2 DEFAULT NULL,
171 p_attribute4 IN VARCHAR2 DEFAULT NULL, p_attribute5 IN VARCHAR2 DEFAULT NULL,
172 p_attribute6 IN VARCHAR2 DEFAULT NULL, p_attribute7 IN VARCHAR2 DEFAULT NULL,
173 p_attribute8 IN VARCHAR2 DEFAULT NULL, p_attribute9 IN VARCHAR2 DEFAULT NULL,
174 p_attribute10 IN VARCHAR2 DEFAULT NULL, p_attribute11 IN VARCHAR2 DEFAULT NULL,
175 p_attribute12 IN VARCHAR2 DEFAULT NULL, p_attribute13 IN VARCHAR2 DEFAULT NULL,
176 p_attribute14 IN VARCHAR2 DEFAULT NULL, p_attribute15 IN VARCHAR2 DEFAULT NULL,
177 p_arg30 IN VARCHAR2 DEFAULT NULL, p_arg31 IN VARCHAR2 DEFAULT NULL,
178 p_arg32 IN VARCHAR2 DEFAULT NULL, p_arg33 IN VARCHAR2 DEFAULT NULL,
179 p_arg34 IN VARCHAR2 DEFAULT NULL, p_arg35 IN VARCHAR2 DEFAULT NULL,
180 p_arg36 IN VARCHAR2 DEFAULT NULL, p_arg37 IN VARCHAR2 DEFAULT NULL,
181 p_arg38 IN VARCHAR2 DEFAULT NULL, p_arg39 IN VARCHAR2 DEFAULT NULL,
182 p_arg40 IN VARCHAR2 DEFAULT NULL, p_arg41 IN VARCHAR2 DEFAULT NULL,
183 p_arg42 IN VARCHAR2 DEFAULT NULL, p_arg43 IN VARCHAR2 DEFAULT NULL,
184 p_arg44 IN VARCHAR2 DEFAULT NULL, p_arg45 IN VARCHAR2 DEFAULT NULL,
185 p_arg46 IN VARCHAR2 DEFAULT NULL, p_arg47 IN VARCHAR2 DEFAULT NULL,
186 p_arg48 IN VARCHAR2 DEFAULT NULL, p_arg49 IN VARCHAR2 DEFAULT NULL,
187 p_arg50 IN VARCHAR2 DEFAULT NULL, p_arg51 IN VARCHAR2 DEFAULT NULL,
188 p_arg52 IN VARCHAR2 DEFAULT NULL, p_arg53 IN VARCHAR2 DEFAULT NULL,
189 p_arg54 IN VARCHAR2 DEFAULT NULL, p_arg55 IN VARCHAR2 DEFAULT NULL,
190 p_arg56 IN VARCHAR2 DEFAULT NULL, p_arg57 IN VARCHAR2 DEFAULT NULL,
191 p_arg58 IN VARCHAR2 DEFAULT NULL, p_arg59 IN VARCHAR2 DEFAULT NULL,
192 p_arg60 IN VARCHAR2 DEFAULT NULL, p_arg61 IN VARCHAR2 DEFAULT NULL,
193 p_arg62 IN VARCHAR2 DEFAULT NULL, p_arg63 IN VARCHAR2 DEFAULT NULL,
194 p_arg64 IN VARCHAR2 DEFAULT NULL, p_arg65 IN VARCHAR2 DEFAULT NULL,
195 p_arg66 IN VARCHAR2 DEFAULT NULL, p_arg67 IN VARCHAR2 DEFAULT NULL,
196 p_arg68 IN VARCHAR2 DEFAULT NULL, p_arg69 IN VARCHAR2 DEFAULT NULL,
197 p_arg70 IN VARCHAR2 DEFAULT NULL, p_arg71 IN VARCHAR2 DEFAULT NULL,
198 p_arg72 IN VARCHAR2 DEFAULT NULL, p_arg73 IN VARCHAR2 DEFAULT NULL,
199 p_arg74 IN VARCHAR2 DEFAULT NULL, p_arg75 IN VARCHAR2 DEFAULT NULL,
200 p_arg76 IN VARCHAR2 DEFAULT NULL, p_arg77 IN VARCHAR2 DEFAULT NULL,
201 p_arg78 IN VARCHAR2 DEFAULT NULL, p_arg79 IN VARCHAR2 DEFAULT NULL,
202 p_arg80 IN VARCHAR2 DEFAULT NULL, p_arg81 IN VARCHAR2 DEFAULT NULL,
203 p_arg82 IN VARCHAR2 DEFAULT NULL, p_arg83 IN VARCHAR2 DEFAULT NULL,
204 p_arg84 IN VARCHAR2 DEFAULT NULL, p_arg85 IN VARCHAR2 DEFAULT NULL,
205 p_arg86 IN VARCHAR2 DEFAULT NULL, p_arg87 IN VARCHAR2 DEFAULT NULL,
206 p_arg88 IN VARCHAR2 DEFAULT NULL, p_arg89 IN VARCHAR2 DEFAULT NULL,
207 p_arg90 IN VARCHAR2 DEFAULT NULL, p_arg91 IN VARCHAR2 DEFAULT NULL,
208 p_arg92 IN VARCHAR2 DEFAULT NULL, p_arg93 IN VARCHAR2 DEFAULT NULL,
209 p_arg94 IN VARCHAR2 DEFAULT NULL, p_arg95 IN VARCHAR2 DEFAULT NULL,
210 p_arg96 IN VARCHAR2 DEFAULT NULL, p_arg97 IN VARCHAR2 DEFAULT NULL,
211 p_arg98 IN VARCHAR2 DEFAULT NULL, p_arg99 IN VARCHAR2 DEFAULT NULL,
212 p_arg100 IN VARCHAR2 DEFAULT NULL
213 )
214 IS
215
216 l_return_status VARCHAR2 (100);
217 l_return_message VARCHAR2 (3000);
218 l_ret_status NUMBER;
219
220 l_msg_count NUMBER;
221 l_msg_data VARCHAR2(4000);
222
223 l_payreq_status VARCHAR2 (100);
224 l_payreq_id IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE;
225 l_req_id IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE;
226 l_is_duplicate BOOLEAN := TRUE;
227
228 l_profile_attribs profileProcessAttribs;
229 l_pmtInstrTab IBY_PAYINSTR_PUB.pmtInstrTabType;
230
231 l_profile_name VARCHAR2(2000);
232 l_flag BOOLEAN := FALSE;
233
234 /* hook related params */
235 l_pkg_name VARCHAR2(200);
236 l_callout_name VARCHAR2(500);
237 l_stmt VARCHAR2(1000);
238 l_error_code VARCHAR2(3000);
239 l_api_version CONSTANT NUMBER := 1.0;
240
241
242 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
243 '.submit_payment_process_request';
244
245 l_create_instrs_flag IBY_PAY_SERVICE_REQUESTS.
246 create_pmt_instructions_flag%TYPE;
247
248 l_payment_doc_id IBY_PAY_SERVICE_REQUESTS.payment_document_id%TYPE;
249
250 /*
251 * This flag indicates to the payment re-creation flow whether
252 * payments were just created in the payment creation flow.
253 *
254 * If this flag is set to TRUE, the payment re-creation flow
255 * will know that payments were just created, and so will not
256 * attempt to re-create the payments.
257 *
258 * If this flag is set to FALSE, the payment re-creation flow
259 * will know that the user has created the payments in a previous
260 * run, has reviewed them and is now attempting to move the payments
261 * forward in the process. In this case, the re-creation flow will
262 * process the payments.
263 */
264 l_pmt_current_session_flag BOOLEAN := FALSE;
265
266 /*
267 * This flag indicates to the document re-validation flow
268 * whether documents were just validated.
269 *
270 * If documents were just validated, the re-validation
271 * flow should not be triggered.
272 */
273 l_doc_current_session_flag BOOLEAN := FALSE;
274
275 /*
276 * Flag that indicates whether payment creation
277 * should be re-tried.
278 */
279 l_do_retry_flag BOOLEAN := FALSE;
280
281 /*
282 * Implementing the callout is optional for the calling app.
283 * If the calling app does not implement the hook, then
284 * the call to the hook will result in ORA-06576 error.
285 *
286 * There is no exception name associated with this code, so
287 * we create one called 'PROCEDURE_NOT_IMPLEMENTED'. If this
288 * exception occurs, it is not fatal: we log the error and
289 * proceed.
290 *
291 * If, on the other hand, the calling app implements the
292 * callout, but the callout throws an exception, it is fatal
293 * and we must abort the program (this will be caught
294 * in WHEN OTHERS block).
295 */
296 PROCEDURE_NOT_IMPLEMENTED EXCEPTION;
297 PRAGMA EXCEPTION_INIT(PROCEDURE_NOT_IMPLEMENTED, -6576);
298
299 BEGIN
300
301 print_debuginfo(l_module_name, 'ENTER');
302
303 print_debuginfo(l_module_name, 'Calling app id: ' || p_calling_app_id);
304 print_debuginfo(l_module_name, 'Calling app pay req cd: '
305 || p_calling_app_payreq_cd);
306
307 /*
308 * Check if parameters are correctly provided.
309 */
310 IF (UPPER(p_create_instrs_flag) = 'Y') THEN
311
312 /*
313 * Payment profile is mandatory if 'create instructions'
314 * flag is set to 'Y'.
315 */
316 IF (p_payment_profile_id IS NULL) THEN
317
318 print_debuginfo(l_module_name, 'Payment profile '
319 || 'is mandatory if ''create instructions flag'' is '
320 || 'set to ''Y''.'
321 );
322
323 print_debuginfo(l_module_name, 'Payment service request '
324 || 'cannot be processed further. '
325 || 'Exiting build program.');
326
327 x_errbuf := 'BUILD PROGRAM ERROR - PARAMETERS INVALID';
328 x_retcode := '-1';
329
330 FND_MESSAGE.SET_NAME('IBY', 'IBY_BUILD_INV_PARAMS');
331 FND_MSG_PUB.ADD;
332
333 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
334
335 print_debuginfo(l_module_name, 'EXIT');
336
337 RETURN;
338
339 END IF;
340
341 /*
342 * Get the default processing related attributes
343 * from the payment process profile.
344 *
345 * We need this to know the processing type of the
346 * profile (needed for creating payment instructions).
347 */
348 get_profile_process_attribs(
349 p_payment_profile_id,
350 l_profile_attribs
351 );
352
353 print_debuginfo(l_module_name, 'Profile ' || p_payment_profile_id
354 || ' has processing type '
355 || l_profile_attribs.processing_type
356 );
357
358 /*
359 * If p_create_instrs_flag = 'Y', it means that the PICP
360 * has to be invoked synchronously by the Build Program.
361 *
362 * In this case, either the payment document should be provided
363 * as an input param, or, a default payment document should
364 * be available on the profile.
365 *
366 * If both of these conditions are not met, fail the payment
367 * service request.
368 */
369
370 /*
371 * Fix for bug 4948884:
372 * Skip this check for ELECTRONIC processing type.
373 */
374 IF (l_profile_attribs.processing_type <> 'ELECTRONIC') THEN
375
376 IF (p_payment_document_id IS NULL) THEN
377
378 /*
379 * Check if a default payment document is associated with
380 * the provided profile. If not, we cannot create payment
381 * instructions automatically for this request.
382 */
383 checkIfDefaultPmtDocOnProfile (
384 p_payment_profile_id,
385 l_profile_name,
386 l_flag
387 );
388
389 IF (l_flag = FALSE) THEN
390
391 print_debuginfo(l_module_name, 'Payment profile '
392 || p_payment_profile_id
393 || ' with name '
394 || l_profile_name
395 || ' cannot be used when automatically '
396 || 'creating payment instructions because '
397 || 'it has no default payment document.'
398 );
399
400 print_debuginfo(l_module_name, 'RESOLUTION: Either '
401 || 'provide the payment document id explicitly '
402 || '(input param to build program), or, associate '
403 || 'a default payment document id with the profile '
404 || 'used.'
405 );
406
407 print_debuginfo(l_module_name, 'Payment service request '
408 || 'cannot be processed further. '
409 || 'Exiting build program.');
410
411 x_errbuf := 'BUILD PROGRAM ERROR - PARAMETERS INVALID';
412 x_retcode := '-1';
413
414 FND_MESSAGE.SET_NAME('IBY',
415 'IBY_BUILD_NO_DEFAULT_PMT_DOC');
416
417 FND_MESSAGE.SET_TOKEN('PROF_NAME',
418 l_profile_name,
419 FALSE);
420
421 FND_MSG_PUB.ADD;
422
423 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
424
425 print_debuginfo(l_module_name, 'EXIT');
426
427 RETURN;
428
429 END IF;
430
431 ELSE
432
433 print_debuginfo(l_module_name, 'Payment document id '
434 || p_payment_document_id
435 || ' has been provided as an input param. Skipping '
436 || 'check for default payment doc on profile.'
437 );
438
439 END IF; -- if p_payment_document_id is null
440
441 END IF; -- if processing type is not 'electronic'
442
443 END IF; -- if p_create_instrs_flag = 'Y'
444
445
446 /*
447 * If payment document id is provided at request level,
448 * ensure that the user has provided both the payment profile
449 * and the internal bank account at the request level also.
450 */
451 IF (p_payment_document_id IS NOT NULL) THEN
452
453 IF (p_payment_profile_id IS NULL OR
454 p_internal_bank_account_id IS NULL) THEN
455
456 print_debuginfo(l_module_name, 'Error: Payment document id '
457 || p_payment_document_id
458 || ' provided at request level. You must provide '
459 || '*both* payment profile and internal bank account '
460 || 'at request level, if you provide payment document id.'
461 );
462
463 print_debuginfo(l_module_name, 'RESOLUTION: Provide '
464 || 'both the payment profile id and the internal '
465 || 'bank account id as input params to build program '
466 || '(if you provide the payment document id as an '
467 || 'input param).'
468 );
469
470 print_debuginfo(l_module_name, 'Payment service request '
471 || 'cannot be processed further. '
472 || 'Exiting build program.');
473
474 x_errbuf := 'BUILD PROGRAM ERROR - PARAMETERS INVALID';
475 x_retcode := '-1';
476
477 FND_MESSAGE.SET_NAME('IBY',
478 'IBY_BUILD_MISS_PMT_DOC_REL_PAR');
479
480 FND_MSG_PUB.ADD;
481
482 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
483
484 print_debuginfo(l_module_name, 'EXIT');
485
486 RETURN;
487
488 END IF;
489
490 END IF;
491
492 print_debuginfo(l_module_name, '+--------------------------------------+');
493 print_debuginfo(l_module_name, '|STEP 1: Insert Payment Service Request|');
494 print_debuginfo(l_module_name, '+--------------------------------------+');
495
496 /*
497 * STEP 1:
498 *
499 * Insert payment request into IBY_PAY_SERVICE_REQUESTS
500 * table and generate payment request id.
501 */
502 BEGIN
503
504 /*
505 * First check whether this is a duplicate request.
506 *
507 * In the case a duplicate request, this function will
508 * return the previously generated payment request id.
509 *
510 * In the case of a new request, this function will
511 * return 0
512 */
513 l_payreq_id := checkIfDuplicate(p_calling_app_id,
514 p_calling_app_payreq_cd);
515
516 IF (l_payreq_id = 0) THEN
517 l_is_duplicate := FALSE;
518 END IF;
519
520 /*
521 * Insert the payment request only if it is not a duplicate.
522 */
523 IF (l_is_duplicate = FALSE) THEN
524 l_payreq_id := insert_payreq(
525 p_calling_app_id,
526 p_calling_app_payreq_cd,
527 p_internal_bank_account_id,
528 p_payment_profile_id,
529 p_allow_zero_payments_flag,
530 p_maximum_payment_amount,
531 p_minimum_payment_amount,
532 p_document_rejection_level,
533 p_payment_rejection_level,
534 p_review_proposed_pmts_flag,
535 p_create_instrs_flag,
536 p_payment_document_id,
537 p_attribute_category,
538 p_attribute1,
539 p_attribute2,
540 p_attribute3,
541 p_attribute4,
542 p_attribute5,
543 p_attribute6,
544 p_attribute7,
545 p_attribute8,
546 p_attribute9,
547 p_attribute10,
548 p_attribute11,
549 p_attribute12,
550 p_attribute13,
551 p_attribute14,
552 p_attribute15
553 );
554
555 IF (l_payreq_id = -1) THEN
556
557 print_debuginfo(l_module_name, 'Could not insert payment '
558 || 'service request for calling app id '
559 || p_calling_app_id
560 || ', calling app payment service request cd '
561 || p_calling_app_payreq_cd
562 );
563
564 print_debuginfo(l_module_name, 'Payment service request '
565 || 'cannot be processed further. '
566 || 'Exiting build program.');
567
568 /*
569 * Rollback any DB changes and exit.
570 */
571 ROLLBACK;
572
573 x_errbuf := 'BUILD PROGRAM ERROR - CANNOT INSERT '
574 || 'PAYMENT SERVICE REQUEST';
575 x_retcode := '-1';
576
577 FND_MESSAGE.SET_NAME('IBY', 'IBY_BUILD_REQ_INSERT_ERROR');
578 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
579
580 print_debuginfo(l_module_name, 'EXIT');
581
582 RETURN;
583
584 ELSE
585 /*
586 * Payment service request as successfully inserted
587 * into the DB. Commit at this point.
588 */
589 print_debuginfo(l_module_name, 'Payment service request '
590 || 'inserted successfully into the database. '
591 || 'Payment request id: '
592 || l_payreq_id);
593
594 COMMIT;
595
596 END IF;
597
598 ELSE
599 print_debuginfo(l_module_name, 'Payment service '
600 || 'request '
601 || p_calling_app_payreq_cd
602 || ' is a duplicate. Skipping insert of request '
603 );
604
605 END IF; -- if not duplicate
606
607 END;
608
609 print_debuginfo(l_module_name, '+------------------------+');
610 print_debuginfo(l_module_name, '|STEP 2: Insert Documents|');
611 print_debuginfo(l_module_name, '+------------------------+');
612
613 /*
614 * STEP 2:
615 *
616 * Insert the documents of this payment request into the
617 * IBY_DOCS_PAYABLE_ALL table.
618 */
619
620 BEGIN
621
622 /*
623 * Insert the payment request documents only if the
624 * request is not a duplicate.
625 */
626 IF (l_is_duplicate = FALSE) THEN
627 l_payreq_status := get_payreq_status(l_payreq_id);
628
629 IF (l_payreq_status = REQ_STATUS_INSERTED) THEN
630
631 --ebs_pga_mem('Before insert_payreq_documents');
632
633 l_ret_status := insert_payreq_documents(p_calling_app_id,
634 p_calling_app_payreq_cd,
635 l_payreq_id
636 );
637
638 --ebs_pga_mem('After insert_payreq_documents');
639
640 IF (l_ret_status = -1) THEN
641
642 print_debuginfo(l_module_name, 'Could not insert '
643 || 'documents payable for payment service '
644 || 'request. Calling app id '
645 || p_calling_app_id
646 || ', calling app payment service request cd '
647 || p_calling_app_payreq_cd
648 );
649
650 print_debuginfo(l_module_name, 'Payment service '
651 || 'request cannot be processed further. '
652 || 'Exiting build program.');
653
654 /*
655 * Rollback any DB changes and exit.
656 */
657 ROLLBACK;
658
659 x_errbuf := 'BUILD PROGRAM ERROR - CANNOT INSERT '
660 || 'DOCUMENTS FOR PAYMENT SERVICE REQUEST';
661 x_retcode := '-1';
662
663 FND_MESSAGE.SET_NAME('IBY', 'IBY_BUILD_DOC_INSERT_ERROR');
664 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
665
666 /*
667 * The payment request was possibly locked by the UI.
668 * Unlock it if possible.
669 */
670 IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity(
671 l_payreq_id ,
672 'PAYMENT_REQUEST',
673 l_return_status
674 );
675
676 print_debuginfo(l_module_name, 'EXIT');
677
678 RETURN;
679
680 ELSE
681
682 /*
683 * Payment service request documents successfully inserted
684 * into the DB. Commit at this point.
685 */
686 COMMIT;
687 END IF;
688
689 END IF;
690
691 ELSE
692 print_debuginfo(l_module_name, 'Payment service '
693 || 'request '
694 || p_calling_app_payreq_cd
695 || ' is a duplicate. Skipping insert of documents '
696 );
697
698
699 END IF; -- if not duplicate
700
701 END;
702
703 /*
704 * STEP 3:
705 *
706 * Call the build program functional flows one-by-one.
707 */
708
709 print_debuginfo(l_module_name, '+----------------------------------+');
710 print_debuginfo(l_module_name, '|STEP 3: Account/Profile Assignment|');
711 print_debuginfo(l_module_name, '+----------------------------------+');
712
713 /*
714 * F4 - Account Profile / Assignment Flow
715 *
716 * If the provided payment reqist is in 'submitted'
717 * status, assign default payment profiles/
718 * internal bank accounts to each document in the
719 * request, if the documents do not already have them.
720 */
721 BEGIN
722 l_payreq_status := get_payreq_status(l_payreq_id);
723
724 IF (l_payreq_status = REQ_STATUS_SUBMITTED) THEN
725 print_debuginfo(l_module_name, 'Found payment request '
726 || l_payreq_id
727 || ' in "submitted" status.'
728 );
729 /* 7492186 */
730 BEGIN
731 SELECT payment_service_request_id
732 INTO l_req_id
733 FROM iby_pay_service_requests
734 WHERE payment_service_request_id = l_payreq_id
735 AND payment_service_request_status = l_payreq_status
736 FOR UPDATE SKIP LOCKED;
737
738 print_debuginfo(l_module_name, 'aquired lock');
739
740 EXCEPTION
741 WHEN NO_DATA_FOUND
742 THEN
743 print_debuginfo(l_module_name, 'PPR is already locked by another request.'||
744 'This is a duplicate Build Request');
745 RETURN;
746 END;
747
748 print_debuginfo(l_module_name, 'Going to perform '
749 || 'assignments for payment req id: '
750 || l_payreq_id);
751
752 --ebs_pga_mem('Before performAssignments');
753
754 IBY_ASSIGN_PUB.performAssignments(
755 l_payreq_id,
756 l_return_status);
757
758 --ebs_pga_mem('After performAssignments');
759
760 print_debuginfo(l_module_name, 'Request status after '
761 || 'assignments: ' || l_return_status);
762
763 END IF;
764
765 /*
766 * If assignments were completed, then commit.
767 */
768
769 COMMIT;
770
771 EXCEPTION
772
773 WHEN OTHERS THEN
774 print_debuginfo(l_module_name, 'Exception occured when performing '
775 || 'assignments. Assignment flow will be aborted and no '
776 || 'assignments will be committed for payment request '
777 || l_payreq_id
778 );
779 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
780 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
781
782 ROLLBACK;
783
784 x_errbuf := 'BUILD PROGRAM ERROR - CANNOT COMPLETE '
785 || 'ACCOUNT/PROFILE ASSIGNMENTS';
786 x_retcode := '-1';
787
788 FND_MESSAGE.SET_NAME('IBY', 'IBY_BUILD_DOC_ASSIGN_ERROR');
789 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
790
791 /*
792 * The payment request was possibly locked by the UI.
793 * Unlock it if possible.
794 */
795 IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity(
796 l_payreq_id ,
797 'PAYMENT_REQUEST',
798 l_return_status
799 );
800
801 print_debuginfo(l_module_name, 'EXIT');
802
803 RETURN;
804
805 END;
806
807 print_debuginfo(l_module_name, '+---------------------------+');
808 print_debuginfo(l_module_name, '|STEP 4: Document Validation|');
809 print_debuginfo(l_module_name, '+---------------------------+');
810
811 /*
812 * F5 - Document Validation Flow (Part I)
813 *
814 * Check if the payment request is in 'ASSIGNMENT_COMPLETE' status;
815 * 'ASSIGNMENT_COMPLETE' indicates that the all the data elements
816 * required for building payments are present in the payment request.
817 *
818 * Validate the documents of such payment requests.
819 */
820 BEGIN
821
822 l_payreq_status := get_payreq_status(l_payreq_id);
823
824 IF (l_payreq_status = REQ_STATUS_ASGN_COMPLETE) THEN
825 print_debuginfo(l_module_name, 'Found payment request '
826 || l_payreq_id
827 || ' in "assignment complete" status.'
828 );
829 /* 7492186 */
830 BEGIN
831 SELECT payment_service_request_id
832 INTO l_req_id
833 FROM iby_pay_service_requests
834 WHERE payment_service_request_id = l_payreq_id
835 AND payment_service_request_status = l_payreq_status
836 FOR UPDATE SKIP LOCKED;
837
838 print_debuginfo(l_module_name, 'aquired lock');
839
840 EXCEPTION
841 WHEN NO_DATA_FOUND
842 THEN
843 print_debuginfo(l_module_name, 'PPR is already locked by another request.'||
844 'This is a duplicate Build Request');
845 RETURN;
846 END;
847
848 print_debuginfo(l_module_name, 'Going to validate documents '
849 || 'for payment request '
850 || l_payreq_id);
851
852 --ebs_pga_mem('Before applyDocumentValidationSets');
853
854 IBY_VALIDATIONSETS_PUB.applyDocumentValidationSets(
855 l_payreq_id,
856 p_document_rejection_level,
857 FALSE,
858 l_return_status);
859
860 --ebs_pga_mem('After applyDocumentValidationSets');
861
862 /*
863 * This flag is used by the document re-validation flow
864 * downstream to decide whether to re-validate documents
865 * or not.
866 *
867 * Without this flag, the document re-validation flow will
868 * attempt to re-validate documents that have just
869 * been validated.
870 *
871 * By using this flag we prevent the above situation.
872 */
873 l_doc_current_session_flag := TRUE;
874
875 print_debuginfo(l_module_name, 'Request status after '
876 || 'document validation: ' || l_return_status);
877
878 END IF;
879
880 /*
881 * If document validations were completed, then commit.
882 */
883 COMMIT;
884
885 EXCEPTION
886
887 WHEN OTHERS THEN
888 print_debuginfo(l_module_name, 'Exception occured when validating '
889 || 'documents. Document validation will be aborted and no '
890 || 'document statuses will be committed for payment request '
891 || l_payreq_id
892 );
893 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
894 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
895
896 ROLLBACK;
897
898 x_errbuf := 'BUILD PROGRAM ERROR - CANNOT VALIDATE '
899 || 'DOCUMENTS';
900 x_retcode := '-1';
901
902 FND_MESSAGE.SET_NAME('IBY', 'IBY_BUILD_DOC_VAL_ERROR');
903 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
904
905 /*
906 * The payment request was possibly locked by the UI.
907 * Unlock it if possible.
908 */
909 IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity(
910 l_payreq_id ,
911 'PAYMENT_REQUEST',
912 l_return_status
913 );
914
915 print_debuginfo(l_module_name, 'EXIT');
916
917 RETURN;
918
919 END;
920
921 print_debuginfo(l_module_name, '+------------------------------+');
922 print_debuginfo(l_module_name, '|STEP 5: Document Re-Validation|');
923 print_debuginfo(l_module_name, '+------------------------------+');
924
925 /*
926 * F5 - Document Validation Flow (Part II)
927 *
928 * Payment requests can re-enter the document validation
929 * flow after user review/modification (F7 flow).
930 *
931 * Re-validate the documents of the payment request if it
932 * is in 'retry document validation' status.
933 */
934 BEGIN
935
936 IF (l_doc_current_session_flag = TRUE) THEN
937 print_debuginfo(l_module_name, 'Current session flag: '
938 || 'true'
939 );
940 ELSE
941 print_debuginfo(l_module_name, 'Current session flag: '
942 || 'false'
943 );
944 END IF;
945
946 l_payreq_status := get_payreq_status(l_payreq_id);
947
948 IF (l_payreq_status = REQ_STATUS_RETRY_DOC_VALID) THEN
949
950 print_debuginfo(l_module_name, 'Found payment request '
951 || l_payreq_id
952 || ' in "retry document validation" status.'
953 );
954
955 /*
956 * Fix for bug 5395425:
957 *
958 * Change the document status to 'ready for validation'
959 * only if we are about to retry document
960 * validation.
961 */
962
963 /*
964 * When we re-enter the document validation flow,
965 * the document status could be 'failed validation'.
966 * Set the document status back to 'ready for validation'
967 * so that the validation flow treats these docs as
968 * fresh documents.
969 */
970 print_debuginfo(l_module_name, 'Going to change document '
971 || 'status to "ready for validation" for failed '
972 || 'documents of payment request '
973 || l_payreq_id);
974
975 UPDATE
976 IBY_DOCS_PAYABLE_ALL
977 SET
978 document_status = DOC_STATUS_RDY_FOR_VAL
979 WHERE
980 payment_service_request_id = l_payreq_id AND
981 document_status = DOC_STATUS_FAILED_VAL
982 ;
983
984 print_debuginfo(l_module_name, 'Document status changes '
985 || 'completed');
986
987 print_debuginfo(l_module_name, 'Going to re-validate documents '
988 || 'for payment request '
989 || l_payreq_id);
990
991 IBY_VALIDATIONSETS_PUB.applyDocumentValidationSets(
992 l_payreq_id,
993 p_document_rejection_level,
994 FALSE,
995 l_return_status);
996
997 print_debuginfo(l_module_name, 'Request status after document '
998 || 're-validation: ' || l_return_status);
999
1000 ELSIF (l_payreq_status = REQ_STATUS_PEN_REV_DOC_VAL AND
1001 l_doc_current_session_flag = FALSE) THEN
1002
1003 print_debuginfo(l_module_name, 'Found payment request '
1004 || l_payreq_id
1005 || ' in "pending review - document validation error" status.'
1006 );
1007
1008 print_debuginfo(l_module_name, 'Going to re-validate documents '
1009 || 'for payment request '
1010 || l_payreq_id);
1011
1012 IBY_VALIDATIONSETS_PUB.applyDocumentValidationSets(
1013 l_payreq_id,
1014 p_document_rejection_level,
1015 FALSE,
1016 l_return_status);
1017
1018 print_debuginfo(l_module_name, 'Request status after document '
1019 || 're-validation: ' || l_return_status);
1020
1021 END IF;
1022
1023
1024 /*
1025 * If document validations were completed, then commit.
1026 */
1027 COMMIT;
1028
1029 EXCEPTION
1030
1031 WHEN OTHERS THEN
1032 print_debuginfo(l_module_name, 'Exception occured when re-validating '
1033 || 'documents. Document validation will be aborted and no '
1034 || 'document statuses will be committed for payment request '
1035 || l_payreq_id
1036 );
1037 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
1038 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
1039
1040 ROLLBACK;
1041
1042 x_errbuf := 'BUILD PROGRAM ERROR - CANNOT COMPLETE '
1043 || 'DOCUMENT RE-VALIDATION';
1044 x_retcode := '-1';
1045
1046 FND_MESSAGE.SET_NAME('IBY', 'IBY_BUILD_DOC_REVAL_ERROR');
1047 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
1048
1049 /*
1050 * The payment request was possibly locked by the UI.
1051 * Unlock it if possible.
1052 */
1053 IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity(
1054 l_payreq_id ,
1055 'PAYMENT_REQUEST',
1056 l_return_status
1057 );
1058
1059 print_debuginfo(l_module_name, 'EXIT');
1060
1061 RETURN;
1062
1063 END;
1064
1065 print_debuginfo(l_module_name, '+------------------------+');
1066 print_debuginfo(l_module_name, '|STEP 6: Payment Creation|');
1067 print_debuginfo(l_module_name, '+------------------------+');
1068
1069 /*
1070 * F6 - Payment Creation Flow (Part I)
1071 *
1072 * If the payment request is in 'validated' status,
1073 * create payments from the documents of the requests.
1074 */
1075
1076 BEGIN
1077 l_payreq_status := get_payreq_status(l_payreq_id);
1078
1079 IF (l_payreq_status = REQ_STATUS_VALIDATED) THEN
1080 print_debuginfo(l_module_name, 'Found payment request '
1081 || l_payreq_id
1082 || ' in "validated" status.'
1083 );
1084 /* 7492186 */
1085 BEGIN
1086 SELECT payment_service_request_id
1087 INTO l_req_id
1088 FROM iby_pay_service_requests
1089 WHERE payment_service_request_id = l_payreq_id
1090 AND payment_service_request_status = l_payreq_status
1091 FOR UPDATE SKIP LOCKED;
1092
1093 print_debuginfo(l_module_name, 'aquired lock');
1094
1095 EXCEPTION
1096 WHEN NO_DATA_FOUND
1097 THEN
1098 print_debuginfo(l_module_name, 'PPR is already locked by another request.'||
1099 'This is a duplicate Build Request');
1100 RETURN;
1101 END;
1102
1103 print_debuginfo(l_module_name, 'Going to create payments '
1104 || 'for payment request '
1105 || l_payreq_id);
1106
1107 --ebs_pga_mem('Before createPayments');
1108
1109 IBY_PAYGROUP_PUB.createPayments(
1110 l_payreq_id,
1111 p_payment_rejection_level,
1112 p_review_proposed_pmts_flag,
1113 l_return_status);
1114
1115 --ebs_pga_mem('After createPayments');
1116
1117 /*
1118 * This flag is used by the payment re-creation flow
1119 * downstream to decide whether to re-create payments
1120 * or not.
1121 *
1122 * Without this flag, the payment re-creation flow will
1123 * immediately move payments that are in PENDING_REVIEW
1124 * status to CREATED status before the user has a chance
1125 * to review them.
1126 *
1127 * By using this flag we prevent the above situation.
1128 */
1129 l_pmt_current_session_flag := TRUE;
1130
1131 print_debuginfo(l_module_name, 'Request status after payment '
1132 || 'creation: ' || l_return_status);
1133
1134 END IF;
1135
1136 /*
1137 * If payment creation was completed, then commit.
1138 */
1139 COMMIT;
1140
1141 EXCEPTION
1142
1143 WHEN OTHERS THEN
1144 print_debuginfo(l_module_name, 'Exception occured when '
1145 || 'building payments. Payment creation will be '
1146 || 'aborted and no payments will be committed for '
1147 || 'payment request '
1148 || l_payreq_id
1149 );
1150 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
1151 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
1152
1153 ROLLBACK;
1154
1155 x_errbuf := 'BUILD PROGRAM ERROR - CANNOT CREATE PAYMENTS';
1156 x_retcode := '-1';
1157
1158 FND_MESSAGE.SET_NAME('IBY', 'IBY_BUILD_PMT_CREAT_ERROR');
1159 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
1160
1161 /*
1162 * The payment request was possibly locked by the UI.
1163 * Unlock it if possible.
1164 */
1165 IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity(
1166 l_payreq_id ,
1167 'PAYMENT_REQUEST',
1168 l_return_status
1169 );
1170
1171 print_debuginfo(l_module_name, 'EXIT');
1172
1173 RETURN;
1174
1175 END;
1176
1177 print_debuginfo(l_module_name, '+---------------------------+');
1178 print_debuginfo(l_module_name, '|STEP 7: Payment Re-Creation|');
1179 print_debuginfo(l_module_name, '+---------------------------+');
1180
1181 /*
1182 * F6 - Payment Creation Flow (Part II)
1183 *
1184 * Payment requests can re-enter the payment creation
1185 * flow after user review/modification (F7 flow).
1186 *
1187 * Re-create the payments of the payment requests if it
1188 * is in 'retry payment creation' status.
1189 */
1190
1191 BEGIN
1192
1193 IF (l_pmt_current_session_flag = TRUE) THEN
1194 print_debuginfo(l_module_name, 'Current session flag: '
1195 || 'true'
1196 );
1197 ELSE
1198 print_debuginfo(l_module_name, 'Current session flag: '
1199 || 'false'
1200 );
1201 END IF;
1202
1203 l_payreq_status := get_payreq_status(l_payreq_id);
1204
1205 IF (l_payreq_status = REQ_STATUS_USER_REVW) THEN
1206
1207 l_flag := checkIfPmtsInModifiedStatus(l_payreq_id);
1208
1209 /*
1210 * If payments have been modified (by dismissing
1211 * constituent documents, for example), then
1212 * the payments of the request need to be re-built.
1213 *
1214 * For this, the payment request status should be
1215 * set to 'RETRY_PAYMENT_CREATION'.
1216 *
1217 * Ideally, the UI should do this step, but because
1218 * of technical limitations of the UI, the status is
1219 * adjusted here.
1220 */
1221 IF (l_flag = TRUE) THEN
1222
1223 print_debuginfo(l_module_name, 'Payment request '
1224 || l_payreq_id
1225 || ' contains payments in "modified" status. '
1226 || 'Adjusting request status to '
1227 || REQ_STATUS_RETRY_PMT_CREAT
1228 );
1229
1230 /*
1231 * Update the request status in the database so that
1232 * it is visible to the payment creation module.
1233 */
1234 UPDATE
1235 IBY_PAY_SERVICE_REQUESTS
1236 SET
1237 payment_service_request_status =
1238 REQ_STATUS_RETRY_PMT_CREAT
1239 WHERE
1240 payment_service_request_id = l_payreq_id;
1241
1242 print_debuginfo(l_module_name, 'Status of payment request '
1243 || l_payreq_id
1244 || ' updated in DB. '
1245 );
1246
1247 l_payreq_status := REQ_STATUS_RETRY_PMT_CREAT;
1248
1249 END IF;
1250
1251 END IF;
1252
1253 /*
1254 * Fix for bug 5331527:
1255 *
1256 * PPRs in REQ_STATUS_PEN_REV_PMT_VAL need to be
1257 * re-validated.
1258 *
1259 * Therefore, call createPayments(..) for such PPRs.
1260 */
1261 IF (l_payreq_status = REQ_STATUS_RETRY_PMT_CREAT OR
1262 l_payreq_status = REQ_STATUS_PEN_REV_PMT_VAL) THEN
1263
1264 IF (l_payreq_status = REQ_STATUS_RETRY_PMT_CREAT) THEN
1265
1266 print_debuginfo(l_module_name, 'Found payment request '
1267 || l_payreq_id
1268 || ' in "retry payment creation" status.'
1269 );
1270
1271 /*
1272 * In the case of PPRs with status
1273 * REQ_STATUS_RETRY_PMT_CREAT, we can blindly go
1274 * ahead and retry the payment creation.
1275 */
1276 l_do_retry_flag := TRUE;
1277
1278 ELSE
1279
1280 /*
1281 * This means that the PPR contained payments with
1282 * validation failures.
1283 *
1284 * We need to run payment validations again.
1285 */
1286 print_debuginfo(l_module_name, 'Found payment request '
1287 || l_payreq_id
1288 || ' in "pending review - pmt validation errors" status.'
1289 );
1290
1291 /*
1292 * In the case of PPRs with status
1293 * REQ_STATUS_PEN_REV_PMT_VAL, we can retry the
1294 * payment creation only of the current session
1295 * flag is FALSE, otherwise, we will never be
1296 * giving the user a chance to review the payments
1297 * that have failed validation.
1298 */
1299 IF (l_pmt_current_session_flag = FALSE) THEN
1300 l_do_retry_flag := TRUE;
1301 ELSE
1302 l_do_retry_flag := FALSE;
1303 END IF;
1304
1305 END IF;
1306
1307 IF (l_do_retry_flag = TRUE) THEN
1308
1309 print_debuginfo(l_module_name, 'Going to retry payment '
1310 || 'creation for payment request: '
1311 || l_payreq_id);
1312
1313 IBY_PAYGROUP_PUB.createPayments(
1314 l_payreq_id,
1315 p_payment_rejection_level,
1316 p_review_proposed_pmts_flag,
1317 l_return_status);
1318
1319 print_debuginfo(l_module_name, 'Request status after retrying '
1320 || 'payment creation: ' || l_return_status);
1321
1322 END IF;
1323
1324 ELSIF (l_payreq_status = REQ_STATUS_USER_REVW AND
1325 l_pmt_current_session_flag = FALSE) THEN
1326
1327 print_debuginfo(l_module_name, 'Found payment request '
1328 || l_payreq_id
1329 || ' in "pending review" status.'
1330 );
1331
1332 print_debuginfo(l_module_name, 'Payment request '
1333 || l_payreq_id
1334 || ' will not be revalidated. Only request status '
1335 || 'will be set to "created".'
1336 );
1337
1338 /*
1339 * When the user reviews payments, she has an option
1340 * of changing the external (payee) bank account on
1341 * the payment.
1342 *
1343 * At this point, the UI will change the status of the
1344 * payment to "modified payee bank account".
1345 *
1346 * The Build Program does not re-validate payments that
1347 * have been reviewed by the user (because the review
1348 * process is considered a lightweight process that
1349 * does not require re-validation).
1350 *
1351 * Therefore, the status of the payment needs to be
1352 * changed back to "created" status so that the
1353 * PICP can pick up this payment for for further
1354 * processing.
1355 */
1356 l_flag := checkIfPmtsInModBankAccStatus(l_payreq_id);
1357
1358 /*
1359 * If payments have been modified (by dismissing
1360 * constituent documents, for example), then
1361 * the payments of the request need to be re-built.
1362 *
1363 * For this, the payment request status should be
1364 * set to 'RETRY_PAYMENT_CREATION'.
1365 *
1366 * Ideally, the UI should do this step, but because
1367 * of technical limitations of the UI, the status is
1368 * adjusted here.
1369 */
1370 IF (l_flag = TRUE) THEN
1371
1372 print_debuginfo(l_module_name, 'Payments in "modified bank '
1373 || 'account" status will be updated for request '
1374 || l_payreq_id
1375 );
1376
1377 UPDATE
1378 IBY_PAYMENTS_ALL
1379 SET
1380 payment_status = PAY_STATUS_CREATED
1381 WHERE
1382 payment_service_request_id = l_payreq_id AND
1383 payment_status = PAY_STATUS_MOD_BNK_ACC
1384 ;
1385
1386 print_debuginfo(l_module_name, 'Payment status changes '
1387 || 'completed');
1388
1389 ELSE
1390
1391 print_debuginfo(l_module_name, 'No payments in "modified bank '
1392 || 'account" status found for request '
1393 || l_payreq_id
1394 );
1395
1396 END IF;
1397
1398 /*
1399 * User has reviewed the payments in this request and
1400 * allowed it to proceed. No need to revalidate. Simply
1401 * change the request status to CREATED and exit.
1402 */
1403 UPDATE
1404 IBY_PAY_SERVICE_REQUESTS
1405 SET
1406 payment_service_request_status = REQ_STATUS_PMT_CRTD
1407 WHERE
1408 payment_service_request_id = l_payreq_id;
1409
1410 print_debuginfo(l_module_name, 'Updated status of'
1411 || ' payment request '
1412 || l_payreq_id
1413 || ' to "created"'
1414 );
1415
1416 END IF;
1417
1418 /*
1419 * If payment creation was completed, then commit.
1420 */
1421 COMMIT;
1422
1423 EXCEPTION
1424
1425 WHEN OTHERS THEN
1426 print_debuginfo(l_module_name, 'Exception occured when '
1427 || 're-building payments. Payment creation will be '
1428 || 'aborted and no payments will be committed for '
1429 || 'payment request '
1430 || l_payreq_id
1431 );
1432 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
1433 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
1434
1435 ROLLBACK;
1436
1437 x_errbuf := 'BUILD PROGRAM ERROR - CANNOT COMPLETE '
1438 || 'PAYMENT RE-CREATION';
1439 x_retcode := '-1';
1440
1441 FND_MESSAGE.SET_NAME('IBY', 'IBY_BUILD_PMT_RECREAT_ERROR');
1442 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
1443
1444 /*
1445 * The payment request was possibly locked by the UI.
1446 * Unlock it if possible.
1447 */
1448 IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity(
1449 l_payreq_id ,
1450 'PAYMENT_REQUEST',
1451 l_return_status
1452 );
1453
1454 print_debuginfo(l_module_name, 'EXIT');
1455
1456 RETURN;
1457
1458 END;
1459
1460 /*
1461 * If we reached here, it means that the build program
1462 * finished successfully. Set the response message to
1463 * 'success'.
1464 */
1465 x_errbuf := 'BUILD PROGRAM COMPLETED SUCCESSFULLY';
1466 x_retcode := '0';
1467
1468 l_payreq_status := get_payreq_status(l_payreq_id);
1469
1470 print_debuginfo(l_module_name, 'Final status of payment request '
1471 || l_payreq_id
1472 || ' (calling app pay req cd: '
1473 || p_calling_app_payreq_cd
1474 || ') before exiting build program is '
1475 || l_payreq_status
1476 );
1477
1478 /*
1479 * Launch payment process status report. This
1480 * report needs to launched at the end of the
1481 * Build Program cycle (Build Program cycle
1482 * is considered completed when payments are
1483 * created. Creation of payment instructions
1484 * is an auxiliary step).
1485 *
1486 * The PPR status report needs to be launched
1487 * based on the enterprise level settings.
1488 *
1489 * See bug 5363433 for details.
1490 */
1491 launchPPRStatusReport(l_payreq_id);
1492
1493 /*
1494 * If we reached here, it means that the Build Program has
1495 * completed processing the payment request. Check if payment
1496 * instructions have to be synchronously created from the payments
1497 * of this payment request.
1498 */
1499 print_debuginfo(l_module_name, '+-------------------------------+');
1500 print_debuginfo(l_module_name, '|STEP 8: Check PICP Kickoff Flag|');
1501 print_debuginfo(l_module_name, '+-------------------------------+');
1502
1503 /*
1504 * The create instructions flag would have been provided
1505 * as an input param to the build program.
1506 *
1507 * If this is a re-run of the build program (e.g., after
1508 * user has reviewed proposed payments, the create
1509 * instructions flag would not be again passed in; instead
1510 * we have to check the IBY_PAY_SERVICE_REQUESTS to get the
1511 * original value for this flag).
1512 *
1513 * Use the retrieved value to determine whether to kick off
1514 * the PICP (Fix for bug 4746624).
1515 */
1516 IF (p_create_instrs_flag = 'N') THEN
1517
1518 print_debuginfo(l_module_name, 'Picking up create '
1519 || 'instructions flag from payment request table ..'
1520 );
1521
1522 BEGIN
1523
1524 SELECT
1525 NVL(create_pmt_instructions_flag, 'N')
1526 INTO
1527 l_create_instrs_flag
1528 FROM
1529 IBY_PAY_SERVICE_REQUESTS
1530 WHERE
1531 PAYMENT_SERVICE_REQUEST_ID = l_payreq_id
1532 ;
1533
1534 print_debuginfo(l_module_name, 'Create instructions '
1535 || 'flag successfully retrieved.'
1536 );
1537
1538 EXCEPTION
1539
1540 WHEN OTHERS THEN
1541
1542 print_debuginfo(l_module_name, 'Non-Fatal: Exception when '
1543 || 'attempting to retrieve create instructions flag.');
1544 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
1545 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
1546
1547 print_debuginfo(l_module_name, 'Payment instruction creation '
1548 || 'will not be attempted.');
1549
1550 END;
1551
1552 ELSE
1553
1554 /*
1555 * Use the passed in value.
1556 */
1557 l_create_instrs_flag := p_create_instrs_flag;
1558
1559 print_debuginfo(l_module_name, 'Passed in create instructions '
1560 || 'flag parameter will be used.'
1561 );
1562
1563 END IF;
1564
1565 print_debuginfo(l_module_name, 'Value of create '
1566 || 'instructions flag: '
1567 || l_create_instrs_flag
1568 );
1569
1570 /*
1571 * The payment document id would have been provided
1572 * as an input param to the build program.
1573 *
1574 * If this is a re-run of the build program (e.g., after
1575 * user has reviewed proposed payments, the payment doc
1576 * id would not be again passed in; instead
1577 * we have to check the IBY_PAY_SERVICE_REQUESTS to get the
1578 * original value for this param).
1579 */
1580 IF (p_payment_document_id IS NULL) THEN
1581
1582 print_debuginfo(l_module_name, 'Picking up payment '
1583 || 'document id from payment request table ..'
1584 );
1585
1586 BEGIN
1587
1588 SELECT
1589 payment_document_id
1590 INTO
1591 l_payment_doc_id
1592 FROM
1593 IBY_PAY_SERVICE_REQUESTS
1594 WHERE
1595 PAYMENT_SERVICE_REQUEST_ID = l_payreq_id
1596 ;
1597
1598 print_debuginfo(l_module_name, 'Payment document '
1599 || 'id successfully retrieved.'
1600 );
1601
1602 EXCEPTION
1603
1604 WHEN OTHERS THEN
1605
1606 print_debuginfo(l_module_name, 'Non-Fatal: Exception when '
1607 || 'attempting to retrieve payment document id.');
1608 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
1609 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
1610
1611 END;
1612
1613 ELSE
1614
1615 /*
1616 * Use the passed in value.
1617 */
1618 l_payment_doc_id := p_payment_document_id;
1619
1620 print_debuginfo(l_module_name, 'Passed in payment document '
1621 || 'id parameter will be used.'
1622 );
1623
1624 END IF;
1625
1626 print_debuginfo(l_module_name, 'Value of payment '
1627 || 'document id: '
1628 || ''''
1629 || l_payment_doc_id
1630 || ''''
1631 );
1632
1633 /*
1634 * Call payment instruction creation routine
1635 * if payment instructions have to be created
1636 * synchronously after build.
1637 */
1638 IF (l_payreq_status <> REQ_STATUS_PMT_CRTD OR
1639 UPPER(l_create_instrs_flag) <> 'Y') THEN
1640
1641 print_debuginfo(l_module_name, 'Not attempting '
1642 || 'to create payment instructions ..'
1643 );
1644
1645 print_debuginfo(l_module_name, 'Final status of payment request '
1646 || l_payreq_id
1647 || ' is '
1648 || l_payreq_status
1649 );
1650
1651 /*
1652 * The payment request was possibly locked by the UI.
1653 * Unlock it if possible.
1654 */
1655 IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity(
1656 l_payreq_id ,
1657 'PAYMENT_REQUEST',
1658 l_return_status
1659 );
1660
1661 print_debuginfo(l_module_name, 'EXIT');
1662
1663 RETURN;
1664
1665 END IF;
1666
1667 print_debuginfo(l_module_name, 'Final status of payment request '
1668 || l_payreq_id
1669 || ' is '
1670 || l_payreq_status
1671 );
1672
1673 /*
1674 * If we reached here, it means that payment
1675 * instructions have to be created from the payments
1676 * of this payment request.
1677 */
1678 print_debuginfo(l_module_name, '+------------------------------------+');
1679 print_debuginfo(l_module_name, '|STEP 9: Payment Instruction Creation|');
1680 print_debuginfo(l_module_name, '+------------------------------------+');
1681
1682 BEGIN
1683
1684 print_debuginfo(l_module_name, 'Synchronously '
1685 || 'attempting to create payment instructions ..'
1686 );
1687
1688 /*
1689 * Get the default processing related attributes
1690 * from the payment process profile.
1691 */
1692 get_profile_process_attribs(
1693 p_payment_profile_id,
1694 l_profile_attribs
1695 );
1696
1697 print_debuginfo(l_module_name, 'Attributes of profile '
1698 || p_payment_profile_id
1699 || ' - '
1700 );
1701 print_debuginfo(l_module_name, 'Processing type: '
1702 || l_profile_attribs.processing_type
1703 );
1704 print_debuginfo(l_module_name, 'Payment document: '
1705 || l_profile_attribs.payment_doc_id
1706 );
1707 print_debuginfo(l_module_name, 'Printer name: '
1708 || l_profile_attribs.printer_name
1709 );
1710 print_debuginfo(l_module_name, 'Print now flag: '
1711 || l_profile_attribs.print_now_flag
1712 );
1713 print_debuginfo(l_module_name, 'Transmit now flag: '
1714 || l_profile_attribs.transmit_now_flag
1715 );
1716
1717 /*
1718 * Now, invoke payment instruction for this
1719 * payment request.
1720 */
1721
1722 --ebs_pga_mem('Before IBY_PAYINSTR_PUB.createPaymentInstructions');
1723
1724 IBY_PAYINSTR_PUB.createPaymentInstructions(
1725 l_profile_attribs.processing_type,
1726 NVL(l_payment_doc_id, l_profile_attribs.payment_doc_id),
1727 l_profile_attribs.printer_name,
1728 l_profile_attribs.print_now_flag,
1729 l_profile_attribs.transmit_now_flag,
1730 p_calling_app_payreq_cd, /* admin assigned ref */
1731 NULL, /* comments */
1732 p_payment_profile_id, /* payment profile id */
1733 p_calling_app_id,
1734 p_calling_app_payreq_cd,
1735 l_payreq_id,
1736 NULL,
1737 NULL,
1738 NULL,
1739 NULL,
1740 NULL,
1741 NULL,
1742 NULL,
1743 'N', /* single payments flow flag */
1744 l_pmtInstrTab,
1745 l_return_status,
1746 l_msg_count,
1747 l_msg_data
1748 );
1749
1750 --ebs_pga_mem('After IBY_PAYINSTR_PUB.createPaymentInstructions');
1751
1752 print_debuginfo(l_module_name, 'Return status of payment '
1753 || 'instruction creation: '
1754 || l_return_status
1755 );
1756
1757 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1758
1759 print_debuginfo(l_module_name, 'Raising exception '
1760 || 'because instruction creation '
1761 || 'did not complete successfully ..',
1762 FND_LOG.LEVEL_UNEXPECTED
1763 );
1764
1765 APP_EXCEPTION.RAISE_EXCEPTION;
1766
1767 END IF;
1768
1769
1770 /*
1771 * If payment instruction creation was completed, then commit.
1772 */
1773 COMMIT;
1774
1775 EXCEPTION
1776
1777 WHEN OTHERS THEN
1778
1779 print_debuginfo(l_module_name, 'Exception occured when '
1780 || 'creating payment instructions. Payment instruction '
1781 || 'creation will be aborted and no instructions will be '
1782 || 'committed for payment request '
1783 || l_payreq_id
1784 );
1785 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
1786 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
1787
1788 ROLLBACK;
1789
1790 x_errbuf := 'BUILD PROGRAM ERROR - CANNOT COMPLETE '
1791 || 'PAYMENT INSTRUCTION CREATION';
1792
1793 FND_MESSAGE.SET_NAME('IBY', 'IBY_BUILD_INS_CREAT_ERROR');
1794 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
1795
1796 /*
1797 * The payment request was possibly locked by the UI.
1798 * Unlock it if possible.
1799 */
1800 IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity(
1801 l_payreq_id ,
1802 'PAYMENT_REQUEST',
1803 l_return_status
1804 );
1805
1806 x_retcode := '-1';
1807 print_debuginfo(l_module_name, 'EXIT');
1808
1809 RETURN;
1810
1811 END;
1812
1813 /*
1814 * If we reached here, it means that payment instruction
1815 * creation was successful. Perform check numbering for the
1816 * payments of the instruction.
1817 */
1818 print_debuginfo(l_module_name, '+-------------------------+');
1819 print_debuginfo(l_module_name, '|STEP 10: Check Numbering |');
1820 print_debuginfo(l_module_name, '+-------------------------+');
1821
1822 BEGIN
1823
1824 /*
1825 * If we reached here, it means that the payment instruction
1826 * creation program finished successfully. Invoke
1827 * check numbering if we are building payment instructions
1828 * of processing type 'printed'.
1829 */
1830 IF (l_profile_attribs.processing_type = 'PRINTED') THEN
1831
1832 IF (l_pmtInstrTab.COUNT > 0) THEN
1833
1834 /*
1835 * Perform check numbering (paper document numbering)
1836 * for the first successful payment instruction. All
1837 * other payment instructions are to be moved to a
1838 * deferred status for later numbering.
1839 *
1840 * This is because the payment document (check stock)
1841 * is locked once it is used to number a payment
1842 * instruction. This lock will only be released after
1843 * the user has confirmed that the checks printed
1844 * correctly. So, there is no point in proceeding
1845 * with other payment instructions until the numbered
1846 * instruction has been confirmed by the user.
1847 */
1848 FOR i IN l_pmtInstrTab.FIRST .. l_pmtInstrTab.LAST LOOP
1849
1850 /*
1851 * Number only successful payment
1852 * instructions.
1853 */
1854 IF (l_pmtInstrTab(i).payment_instruction_status =
1855 INS_STATUS_CREATED) THEN
1856
1857 print_debuginfo(l_module_name, 'Invoking '
1858 || 'numbering for payment '
1859 || 'instruction '
1860 || l_pmtInstrTab(i).payment_instruction_id
1861 || ' with instruction status: '
1862 || l_pmtInstrTab(i).payment_instruction_status
1863 );
1864
1865 /*
1866 * Invoke check numbering for this payment
1867 * instruction.
1868 */
1869 IBY_CHECKNUMBER_PUB.performCheckNumbering(
1870 l_pmtInstrTab(i).payment_instruction_id,
1871
1872 /*
1873 * Use the provided payment document id
1874 * if available; else, use the payment
1875 * doc id associated with the profile.
1876 */
1877 NVL(
1878 l_payment_doc_id,
1879 l_profile_attribs.payment_doc_id
1880 ),
1881
1882 NULL,
1883 l_return_status,
1884 l_return_message,
1885 l_msg_count,
1886 l_msg_data
1887 );
1888
1889 print_debuginfo(l_module_name, 'After numbering, '
1890 || 'return status: '
1891 || l_return_status
1892 || ', and return message: '
1893 || l_return_message
1894 );
1895
1896 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1897
1898 print_debuginfo(l_module_name, 'Check '
1899 || 'numbering module returned failure '
1900 || 'response. Aborting.. ',
1901 FND_LOG.LEVEL_UNEXPECTED
1902 );
1903
1904 APP_EXCEPTION.RAISE_EXCEPTION;
1905
1906 END IF;
1907
1908 /*
1909 * The first successful payment instruction has now
1910 * been numbered.
1911 *
1912 * Invoke the set of post-payment instruction
1913 * creation programs that are responsible for
1914 * extracting, formatting and printing the
1915 * payment instruction data.
1916 */
1917 BEGIN
1918
1919 print_debuginfo(l_module_name, 'Invoking '
1920 || 'extract and format programs for '
1921 || 'instruction '
1922 || l_pmtInstrTab(i).payment_instruction_id
1923 );
1924
1925 IBY_FD_POST_PICP_PROGS_PVT.
1926 Run_Post_PI_Programs(
1927 l_pmtInstrTab(i).payment_instruction_id,
1928 'N'
1929 );
1930
1931 print_debuginfo(l_module_name, 'Extract '
1932 || 'and format operation completed.'
1933 );
1934
1935 EXCEPTION
1936 WHEN OTHERS THEN
1937
1938 print_debuginfo(l_module_name, 'Extract and '
1939 || 'format operation generated '
1940 || 'exception for payment instruction '
1941 || l_pmtInstrTab(i).payment_instruction_id
1942 );
1943
1944 print_debuginfo(l_module_name, 'SQL code: '
1945 || SQLCODE);
1946 print_debuginfo(l_module_name, 'SQL err msg: '
1947 || SQLERRM);
1948
1949 ROLLBACK;
1950
1951 x_errbuf := 'BUILD PROGRAM ERROR - '
1952 || 'EXTRACT/FORMAT OPERATION FAILED';
1953 x_retcode := '-1';
1954
1955 FND_MESSAGE.SET_NAME('IBY',
1956 'IBY_BUILD_BACKEND_ERROR');
1957 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
1958 FND_MESSAGE.GET);
1959
1960 /*
1961 * The payment request was possibly locked
1962 * by the UI. Unlock it if possible.
1963 */
1964 IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity(
1965 l_payreq_id ,
1966 'PAYMENT_REQUEST',
1967 l_return_status
1968 );
1969
1970 print_debuginfo(l_module_name, 'EXIT');
1971
1972 RETURN;
1973
1974 END;
1975
1976 /*
1977 * Move all other successful payment instructions
1978 * to deferred status (for later numbering).
1979 */
1980 IBY_BUILD_INSTRUCTIONS_PUB_PKG.
1981 moveInstrToDeferredStatus(
1982 l_pmtInstrTab,
1983 l_pmtInstrTab(i).payment_instruction_id
1984 );
1985
1986 /*
1987 * Once we have numbered and formatted the first
1988 * successful payment instruction, exit.
1989 */
1990 EXIT;
1991
1992 ELSE
1993
1994 print_debuginfo(l_module_name, 'Not invoking '
1995 || 'paper document numbering for payment '
1996 || 'instruction '
1997 || l_pmtInstrTab(i).payment_instruction_id
1998 || ', as it is in status '
1999 || l_pmtInstrTab(i).payment_instruction_status
2000 );
2001
2002 END IF;
2003
2004 END LOOP;
2005
2006 ELSE
2007
2008 print_debuginfo(l_module_name, 'Instruction count is '
2009 || 'zero. Skipping paper document numbering ..'
2010 );
2011
2012 END IF; -- if instruction count > 0
2013
2014 ELSE
2015
2016 print_debuginfo(l_module_name, 'Processing type is '
2017 || l_profile_attribs.processing_type
2018 || '. Numbering of paper documents skipped ..'
2019 );
2020
2021 /*
2022 * If we reached here, it means the processing type
2023 * is electronic.
2024 *
2025 * For electronic payment instructions, check numbering
2026 * not required. Instead, directly call the extracting
2027 * and formatting programs.
2028 */
2029
2030 IF (l_pmtInstrTab.COUNT > 0) THEN
2031
2032 /*
2033 * Loop through all the payment instructions one-by-one.
2034 *
2035 * Invoke extract and format for each successful payment
2036 * instruction.
2037 */
2038 FOR i IN l_pmtInstrTab.FIRST .. l_pmtInstrTab.LAST LOOP
2039
2040 /*
2041 * Call post-PICP programs only for successful
2042 * payment instructions.
2043 */
2044 IF (l_pmtInstrTab(i).payment_instruction_status =
2045 INS_STATUS_CREATED) THEN
2046
2047 /*
2048 * WITHHOLDING CERTIFICATES HOOK:
2049 *
2050 * Fix for bug 6706749:
2051 *
2052 * We need to invoke withholding certificates hook for
2053 * electronic payments. This is already being done
2054 * from printed payments since base R12. Invoking
2055 * withholding certificates for electronic payments
2056 * is new functionality that is addressed in this
2057 * fix.
2058 */
2059
2060 l_pkg_name := 'AP_AWT_CALLOUT_PKG';
2061 l_callout_name := l_pkg_name
2062 || '.'
2063 || 'zx_witholdingCertificatesHook';
2064
2065 print_debuginfo(l_module_name,
2066 'Attempting to call hook: '
2067 || l_callout_name, FND_LOG.LEVEL_UNEXPECTED);
2068
2069 l_stmt := 'CALL '|| l_callout_name
2070 || '(:1, :2, :3, :4, :5, :6, :7, :8)';
2071
2072
2073 BEGIN
2074
2075 EXECUTE IMMEDIATE
2076 (l_stmt)
2077 USING
2078 IN l_pmtInstrTab(i).payment_instruction_id,
2079 IN 'GENERATE',
2080 IN l_api_version,
2081 IN FND_API.G_FALSE,
2082 IN FND_API.G_FALSE,
2083 OUT l_return_status,
2084 OUT l_msg_count,
2085 OUT l_msg_data
2086 ;
2087
2088 print_debuginfo(l_module_name,
2089 'Finished invoking hook: '
2090 || l_callout_name
2091 || ', return status: '
2092 || l_return_status, FND_LOG.LEVEL_UNEXPECTED);
2093
2094 /*
2095 * If the called procedure did not return success,
2096 * raise an exception.
2097 */
2098 IF (l_return_status IS NULL OR
2099 l_return_status <> FND_API.G_RET_STS_SUCCESS)
2100 THEN
2101
2102 print_debuginfo(l_module_name,
2103 'Fatal: External app callout '''
2104 || l_callout_name
2105 || ''', returned failure status - '
2106 || l_return_status
2107 || '. Raising exception.',
2108 FND_LOG.LEVEL_UNEXPECTED);
2109
2110 APP_EXCEPTION.RAISE_EXCEPTION;
2111
2112 END IF;
2113
2114 EXCEPTION
2115
2116 WHEN PROCEDURE_NOT_IMPLEMENTED THEN
2117 print_debuginfo(l_module_name,
2118 'Callout "'
2119 || l_callout_name
2120 || '" not implemented by application - AP',
2121 FND_LOG.LEVEL_UNEXPECTED);
2122
2123 print_debuginfo(l_module_name,
2124 'Skipping hook call.');
2125
2126 WHEN OTHERS THEN
2127
2128 print_debuginfo(l_module_name,
2129 'Fatal: External app '
2130 || 'callout '''
2131 || l_callout_name
2132 || ''', generated exception.',
2133 FND_LOG.LEVEL_UNEXPECTED
2134 );
2135
2136 l_error_code := 'IBY_INS_AWT_CERT_HOOK_FAILED';
2137 FND_MESSAGE.set_name('IBY', l_error_code);
2138
2139 FND_MESSAGE.SET_TOKEN('CALLOUT',
2140 l_callout_name,
2141 FALSE);
2142 /*
2143 * Set the error message on the concurrent
2144 * program output file (to warn user that
2145 * the hook failed).
2146 */
2147 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
2148 FND_MESSAGE.GET);
2149
2150 /*
2151 * Set the message on the error stack
2152 * to display in UI (in case of direct
2153 * API call from UI).
2154 */
2155 FND_MSG_PUB.ADD;
2156
2157 /*
2158 * Propogate exception to caller.
2159 */
2160 RAISE;
2161
2162 END;
2163
2164 BEGIN
2165
2166 print_debuginfo(l_module_name, 'Invoking '
2167 || 'extract and format for payment '
2168 || 'instruction '
2169 || l_pmtInstrTab(i).payment_instruction_id
2170 || ' with instruction status: '
2171 || l_pmtInstrTab(i).payment_instruction_status
2172 );
2173
2174 IBY_FD_POST_PICP_PROGS_PVT.
2175 Run_Post_PI_Programs(
2176 l_pmtInstrTab(i).payment_instruction_id,
2177 'N'
2178 );
2179
2180 print_debuginfo(l_module_name, 'Extract '
2181 || 'and format operation completed.'
2182 );
2183
2184 EXCEPTION
2185 WHEN OTHERS THEN
2186
2187 print_debuginfo(l_module_name, 'Extract and '
2188 || 'format operation generated '
2189 || 'exception for payment instruction '
2190 || l_pmtInstrTab(i).payment_instruction_id
2191 );
2192
2193 print_debuginfo(l_module_name, 'SQL code: '
2194 || SQLCODE);
2195 print_debuginfo(l_module_name, 'SQL err msg: '
2196 || SQLERRM);
2197
2198 ROLLBACK;
2199
2200 x_errbuf := 'BUILD PROGRAM ERROR - '
2201 || 'EXTRACT/FORMAT OPERATION FAILED';
2202 x_retcode := '-1';
2203
2204 FND_MESSAGE.SET_NAME('IBY',
2205 'IBY_BUILD_BACKEND_ERROR');
2206 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
2207 FND_MESSAGE.GET);
2208
2209 /*
2210 * The payment request was possibly locked
2211 * by the UI. Unlock it if possible.
2212 */
2213 IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity(
2214 l_payreq_id ,
2215 'PAYMENT_REQUEST',
2216 l_return_status
2217 );
2218
2219 print_debuginfo(l_module_name, 'EXIT');
2220
2221 RETURN;
2222
2223 END;
2224
2225 ELSE
2226
2227 print_debuginfo(l_module_name, 'Not invoking '
2228 || 'extract and format for payment '
2229 || 'instruction '
2230 || l_pmtInstrTab(i).payment_instruction_id
2231 || ' because it is in status: '
2232 || l_pmtInstrTab(i).payment_instruction_status
2233 );
2234
2235 END IF;
2236
2237 END LOOP;
2238
2239 END IF; -- if count of instructions > 0
2240
2241 END IF; -- if processing type = 'printed'
2242
2243 /*
2244 * In case check numbering completes, perform a commit.
2245 */
2246 COMMIT;
2247
2248 EXCEPTION
2249
2250 WHEN OTHERS THEN
2251
2252 print_debuginfo(l_module_name, 'Exception occured when '
2253 || 'numbering checks of payment instructions. Check numbering '
2254 || 'will be aborted ..'
2255 );
2256 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
2257 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
2258
2259 ROLLBACK;
2260
2261 x_errbuf := 'BUILD PROGRAM ERROR - CANNOT COMPLETE '
2262 || 'CHECK NUMBERING';
2263 x_retcode := '-1';
2264
2265 FND_MESSAGE.SET_NAME('IBY', 'IBY_BUILD_NUMBERING_ERROR');
2266 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
2267
2268 /*
2269 * The payment request was possibly locked
2270 * by the UI. Unlock it if possible.
2271 */
2272 IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity(
2273 l_payreq_id ,
2274 'PAYMENT_REQUEST',
2275 l_return_status
2276 );
2277
2278 print_debuginfo(l_module_name, 'EXIT');
2279
2280 RETURN;
2281
2282 END;
2283
2284 /*
2285 * If we reached here, it means that the build program
2286 * finished successfully. Set the response message to
2287 * 'success'.
2288 */
2289 x_errbuf := 'BUILD PROGRAM COMPLETED SUCCESSFULLY';
2290 x_retcode := '0';
2291
2292 FND_MESSAGE.SET_NAME('IBY', 'IBY_BUILD_COMPLETED');
2293 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
2294
2295 /*
2296 * The payment request was possibly locked
2297 * by the UI. Unlock it if possible.
2298 */
2299 IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity(
2300 l_payreq_id ,
2301 'PAYMENT_REQUEST',
2302 l_return_status
2303 );
2304
2305 print_debuginfo(l_module_name, 'Build Program completed successfully. '
2306 || 'Exiting ..'
2307 );
2308
2309 print_debuginfo(l_module_name, 'EXIT');
2310
2311 END submit_payment_process_request;
2312
2313 /*--------------------------------------------------------------------
2314 | NAME:
2315 | get_payreq_list
2316 |
2317 |
2318 | PURPOSE:
2319 |
2320 |
2321 | PARAMETERS:
2322 | IN
2323 |
2324 |
2325 | OUT
2326 |
2327 |
2328 | RETURNS:
2329 |
2330 | NOTES:
2331 |
2332 *---------------------------------------------------------------------*/
2333 FUNCTION get_payreq_list (
2334 p_status IN IBY_PAY_SERVICE_REQUESTS.PAYMENT_SERVICE_REQUEST_STATUS%type)
2335 RETURN payreq_tbl_type
2336 IS
2337
2338 payreq_list payreq_tbl_type;
2339 l_module_name VARCHAR2(200) := G_PKG_NAME || '.get_payreq_list';
2340
2341 CURSOR c_pay_req_list (
2342 c_status IN IBY_PAY_SERVICE_REQUESTS.PAYMENT_SERVICE_REQUEST_STATUS%type)
2343 IS
2344 SELECT
2345 payment_service_request_id
2346 FROM
2347 IBY_PAY_SERVICE_REQUESTS
2348 WHERE
2349 payment_service_request_status=c_status;
2350
2351 BEGIN
2352
2353 OPEN c_pay_req_list(p_status);
2354 FETCH c_pay_req_list BULK COLLECT INTO payreq_list;
2355 CLOSE c_pay_req_list;
2356
2357 RETURN payreq_list;
2358
2359 EXCEPTION
2360 WHEN OTHERS THEN
2361 RETURN payreq_list;
2362
2363 END get_payreq_list;
2364
2365 /*--------------------------------------------------------------------
2366 | NAME:
2367 | get_payreq_status
2368 |
2369 |
2370 | PURPOSE:
2371 |
2372 |
2373 | PARAMETERS:
2374 | IN
2375 |
2376 |
2377 | OUT
2378 |
2379 |
2380 | RETURNS:
2381 |
2382 | NOTES:
2383 |
2384 *---------------------------------------------------------------------*/
2385 FUNCTION get_payreq_status (
2386 l_payreq_id IN IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE)
2387 RETURN VARCHAR2
2388 IS
2389
2390 l_payreq_status VARCHAR2(100);
2391 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
2392 '.get_payreq_status';
2393
2394 BEGIN
2395
2396 SELECT
2397 payment_service_request_status
2398 INTO
2399 l_payreq_status
2400 FROM
2401 IBY_PAY_SERVICE_REQUESTS
2402 WHERE
2403 payment_service_request_id = l_payreq_id;
2404
2405 RETURN l_payreq_status;
2406
2407 EXCEPTION
2408 WHEN OTHERS THEN
2409 print_debuginfo(l_module_name, 'Exception occured when '
2410 || 'retrieving payment request status for '
2411 || 'payment request '
2412 || l_payreq_id
2413 );
2414 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
2415 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
2416 print_debuginfo(l_module_name, 'Returning NULL for status');
2417 RETURN NULL;
2418
2419 END get_payreq_status;
2420
2421 /*--------------------------------------------------------------------
2422 | NAME:
2423 | checkIfPmtsInModifiedStatus
2424 |
2425 |
2426 | PURPOSE:
2427 |
2428 |
2429 | PARAMETERS:
2430 | IN
2431 |
2432 |
2433 | OUT
2434 |
2435 |
2436 | RETURNS:
2437 |
2438 | NOTES:
2439 |
2440 *---------------------------------------------------------------------*/
2441 FUNCTION checkIfPmtsInModifiedStatus(
2442 l_payreq_id IN IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE)
2443 RETURN BOOLEAN
2444 IS
2445
2446 l_ret_flag BOOLEAN := FALSE;
2447 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
2448 '.checkIfPmtsInModifiedStatus';
2449 l_test VARCHAR2(200);
2450
2451 BEGIN
2452
2453 SELECT
2454 'TRUE'
2455 INTO
2456 l_test
2457 FROM
2458 DUAL
2459 WHERE
2460 EXISTS
2461 (
2462 SELECT
2463 payment_id
2464 FROM
2465 IBY_PAYMENTS_ALL
2466 WHERE
2467 payment_service_request_id = l_payreq_id AND
2468 payment_status = PAY_STATUS_MODIFIED
2469 )
2470 ;
2471
2472 IF (l_test = 'TRUE') THEN
2473 l_ret_flag := TRUE;
2474 print_debuginfo(l_module_name, 'Returning flag as TRUE.');
2475 ELSE
2476 l_ret_flag := FALSE;
2477 print_debuginfo(l_module_name, 'Returning flag as FALSE.');
2478 END IF;
2479
2480 RETURN l_ret_flag;
2481
2482 EXCEPTION
2483 WHEN OTHERS THEN
2484
2485 print_debuginfo(l_module_name, 'Non-fatal: Exception occured when '
2486 || 'testing whether payments in MODIFIED status exist for '
2487 || 'request id '
2488 || l_payreq_id
2489 );
2490
2491 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
2492 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
2493
2494 print_debuginfo(l_module_name, 'Return flag will be defaulted to '
2495 || 'FALSE.'
2496 );
2497
2498 l_ret_flag := FALSE;
2499
2500 RETURN l_ret_flag;
2501
2502 END checkIfPmtsInModifiedStatus;
2503
2504 /*--------------------------------------------------------------------
2505 | NAME:
2506 | checkIfPmtsInModBankAccStatus
2507 |
2508 |
2509 | PURPOSE:
2510 |
2511 |
2512 | PARAMETERS:
2513 | IN
2514 |
2515 |
2516 | OUT
2517 |
2518 |
2519 | RETURNS:
2520 |
2521 | NOTES:
2522 |
2523 *---------------------------------------------------------------------*/
2524 FUNCTION checkIfPmtsInModBankAccStatus(
2525 l_payreq_id IN IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE)
2526 RETURN BOOLEAN
2527 IS
2528
2529 l_ret_flag BOOLEAN := FALSE;
2530 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
2531 '.checkIfPmtsInModBankAccStatus';
2532 l_test VARCHAR2(200);
2533
2534 BEGIN
2535
2536 SELECT
2537 'TRUE'
2538 INTO
2539 l_test
2540 FROM
2541 DUAL
2542 WHERE
2543 EXISTS
2544 (
2545 SELECT
2546 payment_id
2547 FROM
2548 IBY_PAYMENTS_ALL
2549 WHERE
2550 payment_service_request_id = l_payreq_id AND
2551 payment_status = PAY_STATUS_MOD_BNK_ACC
2552 )
2553 ;
2554
2555 IF (l_test = 'TRUE') THEN
2556 l_ret_flag := TRUE;
2557 print_debuginfo(l_module_name, 'Returning flag as TRUE.');
2558 ELSE
2559 l_ret_flag := FALSE;
2560 print_debuginfo(l_module_name, 'Returning flag as FALSE.');
2561 END IF;
2562
2563 RETURN l_ret_flag;
2564
2565 EXCEPTION
2566 WHEN OTHERS THEN
2567
2568 print_debuginfo(l_module_name, 'Exception occured when testing '
2569 || 'whether "modified bank account" payments exist for request id '
2570 || l_payreq_id
2571 );
2572
2573 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
2574 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
2575
2576 print_debuginfo(l_module_name, 'Return flag will be defaulted to '
2577 || 'FALSE.'
2578 );
2579
2580 l_ret_flag := FALSE;
2581
2582 RETURN l_ret_flag;
2583
2584 END checkIfPmtsInModBankAccStatus;
2585
2586 /*--------------------------------------------------------------------
2587 | NAME:
2588 | insert_payreq
2589 |
2590 |
2591 | PURPOSE:
2592 |
2593 |
2594 | PARAMETERS:
2595 | IN
2596 |
2597 |
2598 | OUT
2599 |
2600 |
2601 | RETURNS:
2602 |
2603 | NOTES:
2604 |
2605 *---------------------------------------------------------------------*/
2606 FUNCTION insert_payreq (
2607 p_calling_app_id IN IBY_PAY_SERVICE_REQUESTS.calling_app_id%TYPE,
2608 p_calling_app_payreq_cd IN IBY_PAY_SERVICE_REQUESTS.
2609 call_app_pay_service_req_code%TYPE,
2610 p_internal_bank_account_id
2611 IN IBY_PAY_SERVICE_REQUESTS.
2612 internal_bank_account_id%TYPE,
2613 p_payment_profile_id
2614 IN IBY_PAY_SERVICE_REQUESTS.
2615 payment_profile_id%TYPE,
2616 p_allow_zero_payments_flag
2617 IN IBY_PAY_SERVICE_REQUESTS.
2618 allow_zero_payments_flag%TYPE,
2619 p_maximum_payment_amount IN IBY_PAY_SERVICE_REQUESTS.
2620 maximum_payment_amount%TYPE,
2621 p_minimum_payment_amount IN IBY_PAY_SERVICE_REQUESTS.
2622 minimum_payment_amount%TYPE,
2623 p_doc_rej_level IN IBY_PAY_SERVICE_REQUESTS.
2624 document_rejection_level_code%TYPE,
2625 p_pmt_rej_level IN IBY_PAY_SERVICE_REQUESTS.
2626 payment_rejection_level_code%TYPE,
2627 p_revw_prop_pmts_flag IN IBY_PAY_SERVICE_REQUESTS.
2628 require_prop_pmts_review_flag%TYPE,
2629 p_create_instrs_flag IN IBY_PAY_SERVICE_REQUESTS.
2630 create_pmt_instructions_flag%TYPE,
2631 p_payment_document_id IN IBY_PAY_SERVICE_REQUESTS.
2632 payment_document_id%TYPE,
2633 p_attribute_category IN IBY_PAY_SERVICE_REQUESTS.
2634 attribute_category%TYPE,
2635 p_attribute1 IN IBY_PAY_SERVICE_REQUESTS.
2636 attribute1%TYPE,
2637 p_attribute2 IN IBY_PAY_SERVICE_REQUESTS.
2638 attribute2%TYPE,
2639 p_attribute3 IN IBY_PAY_SERVICE_REQUESTS.
2640 attribute3%TYPE,
2641 p_attribute4 IN IBY_PAY_SERVICE_REQUESTS.
2642 attribute4%TYPE,
2643 p_attribute5 IN IBY_PAY_SERVICE_REQUESTS.
2644 attribute5%TYPE,
2645 p_attribute6 IN IBY_PAY_SERVICE_REQUESTS.
2646 attribute6%TYPE,
2647 p_attribute7 IN IBY_PAY_SERVICE_REQUESTS.
2648 attribute7%TYPE,
2649 p_attribute8 IN IBY_PAY_SERVICE_REQUESTS.
2650 attribute8%TYPE,
2651 p_attribute9 IN IBY_PAY_SERVICE_REQUESTS.
2652 attribute9%TYPE,
2653 p_attribute10 IN IBY_PAY_SERVICE_REQUESTS.
2654 attribute10%TYPE,
2655 p_attribute11 IN IBY_PAY_SERVICE_REQUESTS.
2656 attribute11%TYPE,
2657 p_attribute12 IN IBY_PAY_SERVICE_REQUESTS.
2658 attribute12%TYPE,
2659 p_attribute13 IN IBY_PAY_SERVICE_REQUESTS.
2660 attribute13%TYPE,
2661 p_attribute14 IN IBY_PAY_SERVICE_REQUESTS.
2662 attribute14%TYPE,
2663 p_attribute15 IN IBY_PAY_SERVICE_REQUESTS.
2664 attribute15%TYPE
2665 )
2666 RETURN NUMBER
2667 IS
2668
2669 l_payreq_id IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE;
2670 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.insert_payreq';
2671
2672 BEGIN
2673
2674 print_debuginfo(l_module_name, 'ENTER');
2675
2676 l_payreq_id := getNextPayReqId();
2677
2678 print_debuginfo(l_module_name, 'Generated payment request id: '
2679 || l_payreq_id);
2680
2681 /*
2682 * Insert the payment request into IBY_PAY_SERVICE_REQUESTS
2683 * table. Supply defaults for values not provided by the
2684 * calling app.
2685 */
2686 INSERT INTO IBY_PAY_SERVICE_REQUESTS (
2687 CALLING_APP_ID,
2688 CREATED_BY,
2689 CREATION_DATE,
2690 LAST_UPDATED_BY,
2691 LAST_UPDATE_DATE,
2692 LAST_UPDATE_LOGIN,
2693 OBJECT_VERSION_NUMBER,
2694 CALL_APP_PAY_SERVICE_REQ_CODE,
2695 PAYMENT_SERVICE_REQUEST_STATUS,
2696 PAYMENT_SERVICE_REQUEST_ID,
2697 PROCESS_TYPE,
2698 ALLOW_ZERO_PAYMENTS_FLAG,
2699 MAXIMUM_PAYMENT_AMOUNT,
2700 MINIMUM_PAYMENT_AMOUNT,
2701 INTERNAL_BANK_ACCOUNT_ID,
2702 PAYMENT_PROFILE_ID,
2703 DOCUMENT_REJECTION_LEVEL_CODE,
2704 PAYMENT_REJECTION_LEVEL_CODE,
2705 REQUIRE_PROP_PMTS_REVIEW_FLAG,
2706 CREATE_PMT_INSTRUCTIONS_FLAG,
2707 PAYMENT_DOCUMENT_ID,
2708 ATTRIBUTE_CATEGORY,
2709 ATTRIBUTE1,
2710 ATTRIBUTE2,
2711 ATTRIBUTE3,
2712 ATTRIBUTE4,
2713 ATTRIBUTE5,
2714 ATTRIBUTE6,
2715 ATTRIBUTE7,
2716 ATTRIBUTE8,
2717 ATTRIBUTE9,
2718 ATTRIBUTE10,
2719 ATTRIBUTE11,
2720 ATTRIBUTE12,
2721 ATTRIBUTE13,
2722 ATTRIBUTE14,
2723 ATTRIBUTE15
2724 )
2725 VALUES(
2726 p_calling_app_id,
2727 fnd_global.user_id,
2728 sysdate,
2729 fnd_global.user_id,
2730 sysdate,
2731 fnd_global.login_id,
2732 1,
2733 p_calling_app_payreq_cd,
2734 REQ_STATUS_INSERTED,
2735 l_payreq_id,
2736 'STANDARD', -- hardcode to 'standard' in the build program
2737 DECODE(
2738 p_allow_zero_payments_flag, NULL, 'N', p_allow_zero_payments_flag
2739 ),
2740 p_maximum_payment_amount,
2741 p_minimum_payment_amount,
2742 p_internal_bank_account_id,
2743 p_payment_profile_id,
2744 p_doc_rej_level,
2745 p_pmt_rej_level,
2746 p_revw_prop_pmts_flag,
2747 p_create_instrs_flag,
2748 p_payment_document_id,
2749 p_attribute_category,
2750 p_attribute1,
2751 p_attribute2,
2752 p_attribute3,
2753 p_attribute4,
2754 p_attribute5,
2755 p_attribute6,
2756 p_attribute7,
2757 p_attribute8,
2758 p_attribute9,
2759 p_attribute10,
2760 p_attribute11,
2761 p_attribute12,
2762 p_attribute13,
2763 p_attribute14,
2764 p_attribute15
2765 );
2766
2767 print_debuginfo(l_module_name, 'EXIT');
2768 RETURN l_payreq_id;
2769
2770 EXCEPTION
2771 WHEN OTHERS THEN
2772 print_debuginfo(l_module_name, 'Exception occured when '
2773 || 'inserting payment request status for '
2774 || 'calling app id '
2775 || p_calling_app_id
2776 || ', calling app payment service request cd '
2777 || p_calling_app_payreq_cd
2778 );
2779 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
2780 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
2781
2782 l_payreq_id := -1;
2783 print_debuginfo(l_module_name, 'Returning -1 for pay req id');
2784 print_debuginfo(l_module_name, 'EXIT');
2785 RETURN l_payreq_id;
2786
2787 END insert_payreq;
2788
2789 /*--------------------------------------------------------------------
2790 | NAME:
2791 | checkIfDuplicate
2792 |
2793 | PURPOSE:
2794 |
2795 |
2796 | PARAMETERS:
2797 | IN
2798 |
2799 |
2800 | OUT
2801 |
2802 |
2803 | RETURNS:
2804 |
2805 | NOTES:
2806 |
2807 *---------------------------------------------------------------------*/
2808 FUNCTION checkIfDuplicate(
2809 p_calling_app_id IN IBY_PAY_SERVICE_REQUESTS.calling_app_id%TYPE,
2810 p_calling_app_payreq_cd IN IBY_PAY_SERVICE_REQUESTS.
2811 call_app_pay_service_req_code%TYPE
2812 )
2813 RETURN NUMBER
2814 IS
2815 l_payreq_id IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE;
2816 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.checkIfDuplicate';
2817 BEGIN
2818
2819 print_debuginfo(l_module_name, 'ENTER');
2820
2821 /*
2822 * Check if a payment request id exists for the
2823 * given calling app.
2824 */
2825 SELECT
2826 payment_service_request_id INTO l_payreq_id
2827 FROM
2828 IBY_PAY_SERVICE_REQUESTS
2829 WHERE
2830 calling_app_id = p_calling_app_id
2831 AND
2832 call_app_pay_service_req_code = p_calling_app_payreq_cd;
2833
2834 /*
2835 * If we found a row, then the request is a duplicate
2836 */
2837 print_debuginfo(l_module_name, 'Calling app payment request '
2838 || p_calling_app_payreq_cd
2839 || ' is a duplicate for calling app '
2840 || p_calling_app_id
2841 || '. Previously generated payment request id: '
2842 || l_payreq_id);
2843
2844 print_debuginfo(l_module_name, 'EXIT');
2845
2846 RETURN l_payreq_id;
2847
2848 EXCEPTION
2849
2850 WHEN NO_DATA_FOUND THEN
2851 /*
2852 * Means that this is a new payment request
2853 * for this calling app.
2854 */
2855 l_payreq_id := 0;
2856 print_debuginfo(l_module_name, 'This is a new '
2857 || 'payment request. Returning 0.'
2858 );
2859
2860 print_debuginfo(l_module_name, 'EXIT');
2861
2862 RETURN l_payreq_id;
2863
2864 WHEN OTHERS THEN
2865 print_debuginfo(l_module_name, 'Fatal: Exception when '
2866 || 'attempting to check whether provided calling '
2867 || 'app payment request '
2868 || p_calling_app_payreq_cd
2869 || ' is a duplicate. Aborting program ..',
2870 FND_LOG.LEVEL_UNEXPECTED
2871 );
2872 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
2873 FND_LOG.LEVEL_UNEXPECTED);
2874 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
2875 FND_LOG.LEVEL_UNEXPECTED);
2876 print_debuginfo(l_module_name, 'EXIT');
2877
2878 /*
2879 * Propogate exception to caller.
2880 */
2881 RAISE;
2882
2883 END checkIfDuplicate;
2884
2885 /*--------------------------------------------------------------------
2886 | NAME:
2887 | derivePayeeIdFromContext
2888 |
2889 | PURPOSE:
2890 |
2891 |
2892 | PARAMETERS:
2893 | IN
2894 |
2895 |
2896 | OUT
2897 |
2898 |
2899 | RETURNS:
2900 |
2901 | NOTES:
2902 |
2903 *---------------------------------------------------------------------*/
2904 FUNCTION derivePayeeIdFromContext(
2905 p_payee_party_id IN IBY_EXTERNAL_PAYEES_ALL.payee_party_id%TYPE,
2906 p_payee_party_site_id IN IBY_EXTERNAL_PAYEES_ALL.party_site_id%TYPE,
2907 p_supplier_site_id IN IBY_EXTERNAL_PAYEES_ALL.supplier_site_id%TYPE,
2908 p_org_id IN IBY_EXTERNAL_PAYEES_ALL.org_id%TYPE,
2909 p_org_type IN IBY_EXTERNAL_PAYEES_ALL.org_type%TYPE,
2910 p_pmt_function IN IBY_EXTERNAL_PAYEES_ALL.payment_function%TYPE
2911 )
2912 RETURN NUMBER
2913 IS
2914 l_payee_id IBY_EXTERNAL_PAYEES_ALL.ext_payee_id%TYPE;
2915 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
2916 '.derivePayeeIdFromContext';
2917
2918 l_payee_party_site_id IBY_EXTERNAL_PAYEES_ALL.party_site_id%TYPE;
2919
2920 l_payee_index VARCHAR2(200);
2921
2922
2923 BEGIN
2924
2925 print_debuginfo(l_module_name, 'ENTER');
2926
2927 print_debuginfo(l_module_name, 'Given payee context:'
2928 || ' payee party id '
2929 || p_payee_party_id
2930 || ', party site id '
2931 || p_payee_party_site_id
2932 || ', supplier site id '
2933 || p_supplier_site_id
2934 || ', org id '
2935 || p_org_id
2936 || ', org type '
2937 || p_org_type
2938 || ', pmt_function '
2939 || p_pmt_function
2940 );
2941
2942 /*
2943 * Adding Caching Logic to avoid database hits for the same input
2944 * combination.
2945 *
2946 */
2947 l_payee_index := to_char(p_payee_party_id)
2948 || '$$'
2949 || to_char(p_payee_party_site_id)
2950 || '$$'
2951 || to_char(p_supplier_site_id)
2952 || '$$'
2953 || to_char(p_org_id)
2954 || '$$'
2955 || p_org_type
2956 || '$$'
2957 || p_pmt_function;
2958
2959 IF (l_payee_id_tbl.EXISTS(l_payee_index)) THEN
2960 RETURN l_payee_id_tbl(l_payee_index);
2961 END IF;
2962
2963 /*
2964 * SPECIAL HANDLING FOR LOAN PAYMENTS:
2965 *
2966 * For loan payments, treat party site id as null
2967 * when performing external payee id match via
2968 * payee context.
2969 *
2970 * See bug 4700623.
2971 */
2972 l_payee_party_site_id := p_payee_party_site_id;
2973
2974 IF (p_pmt_function = 'LOANS_PAYMENTS') THEN
2975
2976 l_payee_party_site_id := NULL;
2977
2978 print_debuginfo(l_module_name, 'Special handling for loans payments; '
2979 || 'the payee party id has been set to null for context match.'
2980 );
2981
2982 END IF;
2983
2984 /*
2985 * Attempt to make an exact match for the given payee context;
2986 * if that doesn't work, attempt to match the partial payee
2987 * context.
2988 *
2989 * In every case, the 'payee party id' and 'payment function'
2990 * fields will always be part of the payee context that is
2991 * used to derive the external payee id.
2992 */
2993
2994 /*
2995 * ATTEMPT I: EXACT MATCH
2996 *
2997 * Check if a payee id exists for the given combination
2998 * of (payee party id, payee party site id, supplier site id,
2999 * org id and org type). These fields define a payee context.
3000 */
3001 BEGIN
3002
3003 SELECT
3004 ext_payee_id INTO l_payee_id
3005 FROM
3006 IBY_EXTERNAL_PAYEES_ALL
3007 WHERE
3008 payee_party_id = p_payee_party_id AND
3009 payment_function = p_pmt_function AND
3010 NVL(party_site_id, '0') = NVL(l_payee_party_site_id, '0') AND
3011 NVL(supplier_site_id, '0') = NVL(p_supplier_site_id, '0') AND
3012 NVL(org_id, '0') = NVL(p_org_id, '0') AND
3013 NVL(org_type, '0') = NVL(p_org_type, '0')
3014 ;
3015
3016 /*
3017 * If we found a row, then the given payee context was
3018 * exactly matched. Return the retrieved payee id.
3019 */
3020 print_debuginfo(l_module_name, 'Payee id '
3021 || l_payee_id
3022 || ' exactly matched given payee context.'
3023 || ' Caching before exiting.'
3024 );
3025
3026 l_payee_id_tbl(l_payee_index) := l_payee_id;
3027
3028 print_debuginfo(l_module_name, 'EXIT');
3029
3030 RETURN l_payee_id;
3031
3032 EXCEPTION
3033
3034 WHEN NO_DATA_FOUND THEN
3035 /*
3036 * Means that a payee could not be exactly
3037 * matched for the given payee context.
3038 */
3039 print_debuginfo(l_module_name, 'No Payee was '
3040 || ' exactly matched for the given payee context.'
3041 );
3042
3043 WHEN OTHERS THEN
3044 print_debuginfo(l_module_name, 'Fatal: Exception when '
3045 || 'attempting to perform exact match for given '
3046 || 'payee context.',
3047 FND_LOG.LEVEL_UNEXPECTED
3048 );
3049
3050 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
3051 FND_LOG.LEVEL_UNEXPECTED);
3052 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
3053 FND_LOG.LEVEL_UNEXPECTED);
3054 print_debuginfo(l_module_name, 'EXIT');
3055
3056 /*
3057 * Propogate exception to caller.
3058 */
3059 RAISE;
3060 END;
3061
3062 /*
3063 * ATTEMPT II: PARTIAL MATCH
3064 *
3065 * Check if a payee id exists for the combination
3066 * of (payee party id, payee party site id).
3067 * These fields define a partial payee context.
3068 */
3069 BEGIN
3070
3071 SELECT
3072 ext_payee_id INTO l_payee_id
3073 FROM
3074 IBY_EXTERNAL_PAYEES_ALL
3075 WHERE
3076 payee_party_id = p_payee_party_id AND
3077 payment_function = p_pmt_function AND
3078 NVL(party_site_id, '0') = NVL(l_payee_party_site_id, '0') AND
3079 supplier_site_id IS NULL AND
3080 org_id IS NULL AND
3081 org_type IS NULL
3082 ;
3083
3084 /*
3085 * If we found a row, then the given partial payee
3086 * context was matched. Return the retrieved payee id.
3087 */
3088 print_debuginfo(l_module_name, 'Payee id '
3089 || l_payee_id
3090 || ' matched given partial payee context '
3091 || ' Caching before exiting.'
3092 );
3093
3094 l_payee_id_tbl(l_payee_index) := l_payee_id;
3095
3096 print_debuginfo(l_module_name, 'EXIT');
3097
3098 RETURN l_payee_id;
3099
3100 EXCEPTION
3101
3102 WHEN NO_DATA_FOUND THEN
3103 /*
3104 * Means that a payee could not be matched
3105 * for the given partial payee context.
3106 */
3107 print_debuginfo(l_module_name, 'No Payee was '
3108 || 'matched for the given partial payee '
3109 || 'context of (payee party id, payment function '
3110 || 'payee party site id).'
3111 );
3112
3113 WHEN OTHERS THEN
3114 print_debuginfo(l_module_name, 'Fatal: Exception when '
3115 || 'attempting to perform match for given partial '
3116 || 'payee context of (payee party id, payment function '
3117 || ', payee party site id).',
3118 FND_LOG.LEVEL_UNEXPECTED
3119 );
3120
3121 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
3122 FND_LOG.LEVEL_UNEXPECTED);
3123 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
3124 FND_LOG.LEVEL_UNEXPECTED);
3125 print_debuginfo(l_module_name, 'EXIT');
3126
3127 /*
3128 * Propogate exception to caller.
3129 */
3130 RAISE;
3131 END;
3132
3133 /*
3134 * ATTEMPT III: PARTIAL MATCH
3135 *
3136 * Check if a payee id exists for the given
3137 * payee party id. This fields defines a partial
3138 * payee context.
3139 */
3140 BEGIN
3141
3142 SELECT
3143 ext_payee_id INTO l_payee_id
3144 FROM
3145 IBY_EXTERNAL_PAYEES_ALL
3146 WHERE
3147 payee_party_id = p_payee_party_id AND
3148 payment_function = p_pmt_function AND
3149 party_site_id IS NULL AND
3150 supplier_site_id IS NULL AND
3151 org_id IS NULL AND
3152 org_type IS NULL
3153 ;
3154
3155 /*
3156 * If we found a row, then the given partial payee
3157 * context was matched. Return the retrieved payee id.
3158 */
3159 print_debuginfo(l_module_name, 'Payee id '
3160 || l_payee_id
3161 || ' matched given partial payee context '
3162 || 'of (payee party id, payment function).'
3163 || ' Caching before exiting.'
3164 );
3165
3166 l_payee_id_tbl(l_payee_index) := l_payee_id;
3167
3168 print_debuginfo(l_module_name, 'EXIT');
3169
3170 RETURN l_payee_id;
3171
3172 EXCEPTION
3173
3174 WHEN NO_DATA_FOUND THEN
3175 /*
3176 * Means that a payee could not be matched
3177 * for the given partial payee context.
3178 */
3179 print_debuginfo(l_module_name, 'No Payee was '
3180 || 'matched for the given partial payee '
3181 || 'context of (payee party id, payment function).'
3182 );
3183
3184 WHEN OTHERS THEN
3185 print_debuginfo(l_module_name, 'Fatal: Exception when '
3186 || 'attempting to perform match for given partial '
3187 || 'payee context of (payee party id, payment function).',
3188 FND_LOG.LEVEL_UNEXPECTED
3189 );
3190
3191 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
3192 FND_LOG.LEVEL_UNEXPECTED);
3193 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
3194 FND_LOG.LEVEL_UNEXPECTED);
3195 print_debuginfo(l_module_name, 'EXIT');
3196
3197 /*
3198 * Propogate exception to caller.
3199 */
3200 RAISE;
3201 END;
3202
3203 /*
3204 * END OF ATTEMPTS:
3205 *
3206 * If we reached here it means that the payee id could not
3207 * be matched from the given payee context (both exact
3208 * match and partial match have failed).
3209 *
3210 * This means that we have received a document payable for
3211 * the payee id is unknown. This document therefore cannot
3212 * be paid; it has to be failed.
3213 *
3214 * Return -1 to indicate that a payee id was not found
3215 * for the given context.
3216 */
3217 print_debuginfo(l_module_name, 'No Payee was '
3218 || 'matched for the given payee context '
3219 || '(even partial context match failed). '
3220 || 'Returning -1 for the payee id.'
3221 );
3222
3223 l_payee_id := -1;
3224 print_debuginfo(l_module_name, 'EXIT');
3225
3226 RETURN l_payee_id;
3227
3228 END derivePayeeIdFromContext;
3229
3230 /*--------------------------------------------------------------------
3231 | NAME:
3232 | deriveExactPayeeIdFromContext
3233 |
3234 | PURPOSE:
3235 |
3236 |
3237 | PARAMETERS:
3238 | IN
3239 |
3240 |
3241 | OUT
3242 |
3243 |
3244 | RETURNS:
3245 |
3246 | NOTES:
3247 |
3248 *---------------------------------------------------------------------*/
3249 FUNCTION deriveExactPayeeIdFromContext(
3250 p_payee_party_id IN IBY_EXTERNAL_PAYEES_ALL.payee_party_id%TYPE,
3251 p_payee_party_site_id IN IBY_EXTERNAL_PAYEES_ALL.party_site_id%TYPE,
3252 p_supplier_site_id IN IBY_EXTERNAL_PAYEES_ALL.supplier_site_id%TYPE,
3253 p_org_id IN IBY_EXTERNAL_PAYEES_ALL.org_id%TYPE,
3254 p_org_type IN IBY_EXTERNAL_PAYEES_ALL.org_type%TYPE,
3255 p_pmt_function IN IBY_EXTERNAL_PAYEES_ALL.payment_function%TYPE
3256 )
3257 RETURN NUMBER
3258 IS
3259 l_payee_id IBY_EXTERNAL_PAYEES_ALL.ext_payee_id%TYPE;
3260 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
3261 '.deriveExactPayeeIdFromContext';
3262
3263 l_payee_party_site_id IBY_EXTERNAL_PAYEES_ALL.party_site_id%TYPE;
3264
3265 BEGIN
3266
3267 print_debuginfo(l_module_name, 'ENTER');
3268
3269 print_debuginfo(l_module_name, 'Given payee context:'
3270 || ' payee party id '
3271 || p_payee_party_id
3272 || ', party site id '
3273 || p_payee_party_site_id
3274 || ', supplier site id '
3275 || p_supplier_site_id
3276 || ', org id '
3277 || p_org_id
3278 || ', org type '
3279 || p_org_type
3280 || ', pmt_function '
3281 || p_pmt_function
3282 );
3283
3284 /*
3285 * SPECIAL HANDLING FOR LOAN PAYMENTS:
3286 *
3287 * For loan payments, treat party site id as null
3288 * when performing external payee id match via
3289 * payee context.
3290 *
3291 * See bug 4700623.
3292 */
3293 l_payee_party_site_id := p_payee_party_site_id;
3294
3295 IF (p_pmt_function = 'LOANS_PAYMENTS') THEN
3296
3297 l_payee_party_site_id := NULL;
3298
3299 print_debuginfo(l_module_name, 'Special handling for loans payments; '
3300 || 'the payee party id has been set to null for context match.'
3301 );
3302
3303 END IF;
3304
3305 /*
3306 * Attempt to make an exact match for the given payee context;
3307 * if that doesn't work, attempt to match the partial payee
3308 * context.
3309 *
3310 * In every case, the 'payee party id' and 'payment function'
3311 * fields will always be part of the payee context that is
3312 * used to derive the external payee id.
3313 */
3314
3315 /*
3316 * EXACT MATCH:
3317 *
3318 * Check if a payee id exists for the given combination
3319 * of (payee party id, payee party site id, supplier site id,
3320 * org id and org type). These fields define a payee context.
3321 */
3322 BEGIN
3323
3324 SELECT
3325 ext_payee_id INTO l_payee_id
3326 FROM
3327 IBY_EXTERNAL_PAYEES_ALL
3328 WHERE
3329 payee_party_id = p_payee_party_id AND
3330 payment_function = p_pmt_function AND
3331 NVL(party_site_id, '0') = NVL(l_payee_party_site_id, '0') AND
3332 NVL(supplier_site_id, '0') = NVL(p_supplier_site_id, '0') AND
3333 NVL(org_id, '0') = NVL(p_org_id, '0') AND
3334 NVL(org_type, '0') = NVL(p_org_type, '0')
3335 ;
3336
3337 /*
3338 * If we found a row, then the given payee context was
3339 * exactly matched. Return the retrieved payee id.
3340 */
3341 print_debuginfo(l_module_name, 'Payee id '
3342 || l_payee_id
3343 || ' exactly matched given payee context.'
3344 );
3345
3346 print_debuginfo(l_module_name, 'EXIT');
3347
3348 RETURN l_payee_id;
3349
3350 EXCEPTION
3351
3352 WHEN NO_DATA_FOUND THEN
3353 /*
3354 * Means that a payee could not be exactly
3355 * matched for the given payee context.
3356 */
3357 print_debuginfo(l_module_name, 'No Payee was '
3358 || ' exactly matched for the given payee context.'
3359 );
3360
3361 WHEN OTHERS THEN
3362 print_debuginfo(l_module_name, 'Fatal: Exception when '
3363 || 'attempting to perform exact match for given '
3364 || 'payee context.',
3365 FND_LOG.LEVEL_UNEXPECTED
3366 );
3367
3368 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
3369 FND_LOG.LEVEL_UNEXPECTED);
3370 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
3371 FND_LOG.LEVEL_UNEXPECTED);
3372 print_debuginfo(l_module_name, 'EXIT');
3373
3374 /*
3375 * Propogate exception to caller.
3376 */
3377 RAISE;
3378 END;
3379
3380 /*
3381 * END OF ATTEMPT:
3382 *
3383 * If we reached here it means that the payee id could not
3384 * be matched from the given payee context (exact
3385 * match failed).
3386 *
3387 * Return -1 to indicate that a payee id was not found
3388 * for the given context.
3389 */
3390 print_debuginfo(l_module_name, 'No Payee was '
3391 || 'matched for the given payee context. '
3392 || 'Returning -1 for the payee id.'
3393 );
3394
3395 l_payee_id := -1;
3396 print_debuginfo(l_module_name, 'EXIT');
3397
3398 RETURN l_payee_id;
3399
3400 END deriveExactPayeeIdFromContext;
3401
3402 /*--------------------------------------------------------------------
3403 | NAME:
3404 | getNextPayReqID
3405 |
3406 | PURPOSE:
3407 |
3408 |
3409 | PARAMETERS:
3410 | IN
3411 |
3412 |
3413 | OUT
3414 |
3415 |
3416 | RETURNS:
3417 |
3418 | NOTES:
3419 |
3420 *---------------------------------------------------------------------*/
3421 FUNCTION getNextPayReqID
3422 RETURN NUMBER
3423 IS
3424 l_payreq_id NUMBER(15);
3425
3426 BEGIN
3427
3428 SELECT IBY_PAY_SERVICE_REQUESTS_S.nextval INTO l_payreq_id
3429 FROM DUAL;
3430
3431 RETURN l_payreq_id;
3432
3433 END getNextPayReqID;
3434
3435 /*--------------------------------------------------------------------
3436 | NAME:
3437 | insert_payreq_documents
3438 |
3439 |
3440 | PURPOSE:
3441 |
3442 |
3443 | PARAMETERS:
3444 | IN
3445 |
3446 |
3447 | OUT
3448 |
3449 |
3450 | RETURNS:
3451 |
3452 | NOTES:
3453 |
3454 *---------------------------------------------------------------------*/
3455 FUNCTION insert_payreq_documents (
3456 p_calling_app_id IN IBY_PAY_SERVICE_REQUESTS.calling_app_id%TYPE,
3457 p_calling_app_payreq_cd IN IBY_PAY_SERVICE_REQUESTS.
3458 call_app_pay_service_req_code%TYPE,
3459 p_payreq_id IN IBY_PAY_SERVICE_REQUESTS.
3460 payment_service_request_id%TYPE
3461 )
3462 RETURN NUMBER
3463 IS
3464
3465 l_return_status NUMBER := -1;
3466 l_app_short_name VARCHAR2(200);
3467
3468 l_view_name VARCHAR2(200);
3469
3470 TYPE dyn_documents IS REF CURSOR;
3471 l_docs_cursor dyn_documents;
3472
3473 l_lines_view_name VARCHAR2(200);
3474
3475 /* payee conext generated internally based on supplied payee fields */
3476 l_payee_id IBY_EXTERNAL_PAYEES_ALL.ext_payee_id%TYPE;
3477
3478 l_pmtFxAccessTypesTab distinctPmtFxAccessTab;
3479 l_orgAccessTypesTab distinctOrgAccessTab;
3480
3481 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
3482 '.insert_payreq_documents';
3483
3484 l_trx_line_index BINARY_INTEGER;
3485 l_no_rec_in_ppr BOOLEAN;
3486 G_LINES_PER_FETCH CONSTANT NUMBER:= 1000;
3487
3488 BEGIN
3489
3490 print_debuginfo(l_module_name, 'ENTER');
3491
3492 /*
3493 * Get the shortname of the calling app from the calling
3494 * app id.
3495 */
3496 SELECT
3497 fnd.application_short_name
3498 INTO
3499 l_app_short_name
3500 FROM
3501 FND_APPLICATION fnd
3502 WHERE
3503 fnd.application_id = p_calling_app_id;
3504
3505 print_debuginfo(l_module_name, 'Calling app short name: '
3506 || l_app_short_name);
3507
3508 /*
3509 * For some applications, the application short name cannot
3510 * be directly used in forming the view name.
3511 *
3512 * Example, for AP, the application short name is 'SQLAP',
3513 * but the AP tables/views begin as 'AP_XXXX'. Therefore,
3514 * we will convert the application short names into table
3515 * prefixes here.
3516 */
3517 CASE l_app_short_name
3518 WHEN 'SQLAP' THEN
3519 l_app_short_name := 'AP';
3520 ELSE
3521 /* do nothing */
3522 NULL;
3523 END CASE;
3524
3525 print_debuginfo(l_module_name, 'Processed calling app short name: '
3526 || l_app_short_name);
3527
3528 /*
3529 * Dynamically form the view name.
3530 *
3531 * The view name is dependent upon the calling
3532 * app name and will be of the form
3533 * <calling app name>_DOCUMENTS_PAYABLE.
3534 */
3535 l_view_name := l_app_short_name || '_DOCUMENTS_PAYABLE';
3536
3537 /*
3538 * Read the documents for this payment service request
3539 * from the calling app's view. The calling app's view
3540 * will be prefixed with the application short name.
3541 */
3542 print_debuginfo(l_module_name, 'Going to fetch'
3543 || ' documents from ' || l_view_name
3544 || ' table using calling app id '
3545 || p_calling_app_id
3546 || ' and calling app pay req cd '
3547 || p_calling_app_payreq_cd
3548 || ' as keys');
3549
3550 l_no_rec_in_ppr := TRUE;
3551
3552 /* old select - for reference purposes */
3553 /*----------------------------------
3554 OPEN l_docs_cursor FOR
3555 'SELECT * FROM '
3556 || l_view_name
3557 || ' WHERE calling_app_id = :ca_id'
3558 || ' AND call_app_pay_service_req_code = :ca_payreq_cd'
3559 USING
3560 p_calling_app_id,
3561 p_calling_app_payreq_cd
3562 ;
3563 ------------------------------------*/
3564
3565 /*
3566 * Ensure that the order of the columns in this SELECT matches
3567 * exactly with the order of the columns of the template table
3568 * IBY_GEN_DOCS_PAYABLE.
3569 *
3570 * By using names columns in this SELECT (instead of select *),
3571 * we are making it possible for the external application to
3572 * have a slightly different column ordering that what is
3573 * present in IBY_GEN_DOCS_PAYABLE (otherwise, the ordering
3574 * becomes strict).
3575 */
3576 OPEN l_docs_cursor FOR
3577 'SELECT '
3578 || 'pay_proc_trxn_type_code, '
3579 || 'calling_app_id, '
3580 || 'calling_app_doc_unique_ref1, '
3581 || 'calling_app_doc_unique_ref2, '
3582 || 'calling_app_doc_unique_ref3, '
3583 || 'calling_app_doc_unique_ref4, '
3584 || 'calling_app_doc_unique_ref5, '
3585 || 'calling_app_doc_ref_number, '
3586 || 'call_app_pay_service_req_code, '
3587 || 'IBY_DOCS_PAYABLE_ALL_S.nextval, '
3588 || 'payment_function, '
3589 || 'payment_date, '
3590 || 'document_date, '
3591 || 'document_type, '
3592 || 'document_currency_code, '
3593 || 'document_amount, '
3594 || 'payment_currency_code, '
3595 || 'payment_amount, '
3596 || 'payment_method_code, '
3597 || 'exclusive_payment_flag, '
3598 || 'remit_payee_party_id, '
3599 || 'remit_party_site_id, '
3600 || 'remit_supplier_site_id, '
3601 || 'remit_beneficiary_party, '
3602 || 'legal_entity_id, '
3603 || 'org_id, '
3604 || 'org_type, '
3605 || 'allow_removing_document_flag, '
3606 || 'created_by, ' -- Ramesh, Why r we selecting this?
3607 || 'creation_date, '
3608 || 'last_updated_by, '
3609 || 'last_update_date, '
3610 || 'last_update_login, '
3611 || 'object_version_number, '
3612 || 'anticipated_value_date, '
3613 || 'po_number, '
3614 || 'document_description, '
3615 || 'document_currency_tax_amount, '
3616 || 'document_curr_charge_amount, '
3617 || 'amount_withheld, '
3618 || 'payment_curr_discount_taken, '
3619 || 'discount_date, '
3620 || 'payment_due_date, '
3621 || 'payment_profile_id, '
3622 || 'internal_bank_account_id, '
3623 || 'external_bank_account_id, '
3624 || 'bank_charge_bearer, '
3625 || 'interest_rate, '
3626 || 'payment_grouping_number, '
3627 || 'payment_reason_code, '
3628 || 'payment_reason_comments, '
3629 || 'settlement_priority, '
3630 || 'remittance_message1, '
3631 || 'remittance_message2, '
3632 || 'remittance_message3, '
3633 || 'unique_remittance_identifier, '
3634 || 'uri_check_digit, '
3635 || 'delivery_channel_code, '
3636 || 'payment_format_code, '
3637 || 'document_sequence_id, '
3638 || 'document_sequence_value, '
3639 || 'document_category_code, '
3640 || 'bank_assigned_ref_code, '
3641 || 'remit_to_location_id, '
3642 || 'attribute_category, '
3643 || 'attribute1, '
3644 || 'attribute2, '
3645 || 'attribute3, '
3646 || 'attribute4, '
3647 || 'attribute5, '
3648 || 'attribute6, '
3649 || 'attribute7, '
3650 || 'attribute8, '
3651 || 'attribute9, '
3652 || 'attribute10, '
3653 || 'attribute11, '
3654 || 'attribute12, '
3655 || 'attribute13, '
3656 || 'attribute14, '
3657 || 'attribute15, '
3658 || 'address_source, '
3659 || 'employee_address_code, '
3660 || 'employee_person_id, '
3661 || 'employee_payment_flag, '
3662 || 'employee_address_id, '
3663 || 'payee_party_id, '
3664 || 'party_site_id, '
3665 || 'supplier_site_id, '
3666 || 'beneficiary_party, '
3667 || 'relationship_id '
3668 || 'FROM '
3669 || l_view_name
3670 || ' WHERE calling_app_id = :ca_id'
3671 || ' AND call_app_pay_service_req_code = :ca_payreq_cd'
3672 USING
3673 p_calling_app_id,
3674 p_calling_app_payreq_cd
3675 ;
3676
3677 LOOP
3678
3679 delete_docspayTab;
3680
3681 FETCH l_docs_cursor BULK COLLECT INTO
3682 iby_disburse_submit_pub_pkg.docspayTab.pay_proc_trxn_type_code ,
3683 iby_disburse_submit_pub_pkg.docspayTab.calling_app_id ,
3684 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref1 ,
3685 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref2 ,
3686 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref3 ,
3687 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref4 ,
3688 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref5 ,
3689 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_ref_number ,
3690 iby_disburse_submit_pub_pkg.docspayTab.call_app_pay_service_req_code ,
3691 iby_disburse_submit_pub_pkg.docspayTab.document_payable_id ,
3692 iby_disburse_submit_pub_pkg.docspayTab.payment_function ,
3693 iby_disburse_submit_pub_pkg.docspayTab.payment_date ,
3694 iby_disburse_submit_pub_pkg.docspayTab.document_date ,
3695 iby_disburse_submit_pub_pkg.docspayTab.document_type ,
3696 iby_disburse_submit_pub_pkg.docspayTab.document_currency_code ,
3697 iby_disburse_submit_pub_pkg.docspayTab.document_amount ,
3698 iby_disburse_submit_pub_pkg.docspayTab.payment_currency_code ,
3699 iby_disburse_submit_pub_pkg.docspayTab.payment_amount ,
3700 iby_disburse_submit_pub_pkg.docspayTab.payment_method_code ,
3701 iby_disburse_submit_pub_pkg.docspayTab.exclusive_payment_flag ,
3702 iby_disburse_submit_pub_pkg.docspayTab.payee_party_id ,
3703 iby_disburse_submit_pub_pkg.docspayTab.party_site_id ,
3704 iby_disburse_submit_pub_pkg.docspayTab.supplier_site_id ,
3705 iby_disburse_submit_pub_pkg.docspayTab.beneficiary_party ,
3706 iby_disburse_submit_pub_pkg.docspayTab.legal_entity_id ,
3707 iby_disburse_submit_pub_pkg.docspayTab.org_id ,
3708 iby_disburse_submit_pub_pkg.docspayTab.org_type ,
3709 iby_disburse_submit_pub_pkg.docspayTab.allow_removing_document_flag ,
3710 iby_disburse_submit_pub_pkg.docspayTab.created_by ,
3711 iby_disburse_submit_pub_pkg.docspayTab.creation_date ,
3712 iby_disburse_submit_pub_pkg.docspayTab.last_updated_by ,
3713 iby_disburse_submit_pub_pkg.docspayTab.last_update_date ,
3714 iby_disburse_submit_pub_pkg.docspayTab.last_update_login ,
3715 iby_disburse_submit_pub_pkg.docspayTab.object_version_number ,
3716 iby_disburse_submit_pub_pkg.docspayTab.anticipated_value_date ,
3717 iby_disburse_submit_pub_pkg.docspayTab.po_number ,
3718 iby_disburse_submit_pub_pkg.docspayTab.document_description ,
3719 iby_disburse_submit_pub_pkg.docspayTab.document_currency_tax_amount ,
3720 iby_disburse_submit_pub_pkg.docspayTab.document_curr_charge_amount ,
3721 iby_disburse_submit_pub_pkg.docspayTab.amount_withheld ,
3722 iby_disburse_submit_pub_pkg.docspayTab.payment_curr_discount_taken ,
3723 iby_disburse_submit_pub_pkg.docspayTab.discount_date ,
3724 iby_disburse_submit_pub_pkg.docspayTab.payment_due_date ,
3725 iby_disburse_submit_pub_pkg.docspayTab.payment_profile_id ,
3726 iby_disburse_submit_pub_pkg.docspayTab.internal_bank_account_id ,
3727 iby_disburse_submit_pub_pkg.docspayTab.external_bank_account_id ,
3728 iby_disburse_submit_pub_pkg.docspayTab.bank_charge_bearer ,
3729 iby_disburse_submit_pub_pkg.docspayTab.interest_rate ,
3730 iby_disburse_submit_pub_pkg.docspayTab.payment_grouping_number ,
3731 iby_disburse_submit_pub_pkg.docspayTab.payment_reason_code ,
3732 iby_disburse_submit_pub_pkg.docspayTab.payment_reason_comments ,
3733 iby_disburse_submit_pub_pkg.docspayTab.settlement_priority ,
3734 iby_disburse_submit_pub_pkg.docspayTab.remittance_message1 ,
3735 iby_disburse_submit_pub_pkg.docspayTab.remittance_message2 ,
3736 iby_disburse_submit_pub_pkg.docspayTab.remittance_message3 ,
3737 iby_disburse_submit_pub_pkg.docspayTab.unique_remittance_identifier ,
3738 iby_disburse_submit_pub_pkg.docspayTab.uri_check_digit ,
3739 iby_disburse_submit_pub_pkg.docspayTab.delivery_channel_code ,
3740 iby_disburse_submit_pub_pkg.docspayTab.payment_format_code ,
3741 iby_disburse_submit_pub_pkg.docspayTab.document_sequence_id ,
3742 iby_disburse_submit_pub_pkg.docspayTab.document_sequence_value ,
3743 iby_disburse_submit_pub_pkg.docspayTab.document_category_code ,
3744 iby_disburse_submit_pub_pkg.docspayTab.bank_assigned_ref_code ,
3745 iby_disburse_submit_pub_pkg.docspayTab.remit_to_location_id ,
3746 iby_disburse_submit_pub_pkg.docspayTab.attribute_category ,
3747 iby_disburse_submit_pub_pkg.docspayTab.attribute1 ,
3748 iby_disburse_submit_pub_pkg.docspayTab.attribute2 ,
3749 iby_disburse_submit_pub_pkg.docspayTab.attribute3 ,
3750 iby_disburse_submit_pub_pkg.docspayTab.attribute4 ,
3751 iby_disburse_submit_pub_pkg.docspayTab.attribute5 ,
3752 iby_disburse_submit_pub_pkg.docspayTab.attribute6 ,
3753 iby_disburse_submit_pub_pkg.docspayTab.attribute7 ,
3754 iby_disburse_submit_pub_pkg.docspayTab.attribute8 ,
3755 iby_disburse_submit_pub_pkg.docspayTab.attribute9 ,
3756 iby_disburse_submit_pub_pkg.docspayTab.attribute10 ,
3757 iby_disburse_submit_pub_pkg.docspayTab.attribute11 ,
3758 iby_disburse_submit_pub_pkg.docspayTab.attribute12 ,
3759 iby_disburse_submit_pub_pkg.docspayTab.attribute13 ,
3760 iby_disburse_submit_pub_pkg.docspayTab.attribute14 ,
3761 iby_disburse_submit_pub_pkg.docspayTab.attribute15 ,
3762 iby_disburse_submit_pub_pkg.docspayTab.address_source ,
3763 iby_disburse_submit_pub_pkg.docspayTab.employee_address_code ,
3764 iby_disburse_submit_pub_pkg.docspayTab.employee_person_id ,
3765 iby_disburse_submit_pub_pkg.docspayTab.employee_payment_flag ,
3766 iby_disburse_submit_pub_pkg.docspayTab.employee_address_id ,
3767 /*TPP-Start*/
3768 iby_disburse_submit_pub_pkg.docspayTab.inv_payee_party_id ,
3769 iby_disburse_submit_pub_pkg.docspayTab.inv_party_site_id ,
3770 iby_disburse_submit_pub_pkg.docspayTab.inv_supplier_site_id ,
3771 iby_disburse_submit_pub_pkg.docspayTab.inv_beneficiary_party ,
3772 iby_disburse_submit_pub_pkg.docspayTab.relationship_id
3773 /*TPP-End*/
3774 LIMIT G_LINES_PER_FETCH;
3775
3776 FOR l_trx_line_index IN nvl(iby_disburse_submit_pub_pkg.docspayTab.pay_proc_trxn_type_code.FIRST,0) .. nvl(iby_disburse_submit_pub_pkg.docspayTab.pay_proc_trxn_type_code.LAST,-99)
3777 LOOP
3778 l_no_rec_in_ppr := FALSE;
3779
3780 print_debuginfo(l_module_name, 'Processed document payable id: '
3781 || to_char(iby_disburse_submit_pub_pkg.docspayTab.document_payable_id(l_trx_line_index)));
3782
3783 /*
3784 * Populate rest of the document attributes required in the table
3785 * IBY_DOCS_PAYABLE_ALL into a PLSQL record structure.
3786 * We will use this structure in doing a bulk insert into the
3787 * IBY_DOCS_PAYABLE_ALL table.
3788 */
3789 iby_disburse_submit_pub_pkg.docspayTab.payment_service_request_id(l_trx_line_index)
3790 := p_payreq_id;
3791 -- iby_disburse_submit_pub_pkg.docspayTab.document_payable_id
3792 -- := getNextDocumentPayableID();
3793 --
3794 -- Set document status ot 'submitted' when docs are first inserted
3795 -- into the IBY_DOCS_PAYABLE_ALL table.
3796 --
3797 iby_disburse_submit_pub_pkg.docspayTab.document_status(l_trx_line_index) := DOC_STATUS_SUBMITTED;
3798
3799 iby_disburse_submit_pub_pkg.docspayTab.exclusive_payment_flag(l_trx_line_index)
3800 := NVL(iby_disburse_submit_pub_pkg.docspayTab.exclusive_payment_flag(l_trx_line_index), 'N');
3801
3802 --
3803 -- From the given payee context (payee party id, payee party site
3804 -- id, supplier site id, org id and org type) on the document,
3805 -- derive the payee id from the IBY_EXTERNAL_PAYEES_ALL table.
3806 --
3807 l_payee_id := derivePayeeIdFromContext(
3808 iby_disburse_submit_pub_pkg.docspayTab.payee_party_id(l_trx_line_index),
3809 iby_disburse_submit_pub_pkg.docspayTab.party_site_id(l_trx_line_index),
3810 iby_disburse_submit_pub_pkg.docspayTab.supplier_site_id(l_trx_line_index),
3811 iby_disburse_submit_pub_pkg.docspayTab.org_id(l_trx_line_index),
3812 iby_disburse_submit_pub_pkg.docspayTab.org_type(l_trx_line_index),
3813 iby_disburse_submit_pub_pkg.docspayTab.payment_function(l_trx_line_index)
3814 );
3815
3816 -- Store the external payee id as an attribute of the document
3817 iby_disburse_submit_pub_pkg.docspayTab.ext_payee_id(l_trx_line_index) := l_payee_id;
3818
3819 l_payee_id := derivePayeeIdFromContext(
3820 iby_disburse_submit_pub_pkg.docspayTab.inv_payee_party_id(l_trx_line_index),
3821 iby_disburse_submit_pub_pkg.docspayTab.inv_party_site_id(l_trx_line_index),
3822 iby_disburse_submit_pub_pkg.docspayTab.inv_supplier_site_id(l_trx_line_index),
3823 iby_disburse_submit_pub_pkg.docspayTab.org_id(l_trx_line_index),
3824 iby_disburse_submit_pub_pkg.docspayTab.org_type(l_trx_line_index),
3825 iby_disburse_submit_pub_pkg.docspayTab.payment_function(l_trx_line_index)
3826 );
3827
3828 -- Store the external inv payee id as an attribute of the document
3829 iby_disburse_submit_pub_pkg.docspayTab.ext_inv_payee_id(l_trx_line_index) := l_payee_id;
3830
3831 iby_disburse_submit_pub_pkg.docspayTab.created_by(l_trx_line_index) := fnd_global.user_id;
3832 iby_disburse_submit_pub_pkg.docspayTab.creation_date(l_trx_line_index) := sysdate;
3833 iby_disburse_submit_pub_pkg.docspayTab.last_updated_by(l_trx_line_index) := fnd_global.user_id;
3834 iby_disburse_submit_pub_pkg.docspayTab.last_update_date(l_trx_line_index) := sysdate;
3835 iby_disburse_submit_pub_pkg.docspayTab.last_update_login(l_trx_line_index) := fnd_global.login_id;
3836 iby_disburse_submit_pub_pkg.docspayTab.object_version_number(l_trx_line_index) := 1;
3837
3838 iby_disburse_submit_pub_pkg.docspayTab.allow_removing_document_flag(l_trx_line_index)
3839 := NVL(iby_disburse_submit_pub_pkg.docspayTab.allow_removing_document_flag(l_trx_line_index),'Y');
3840
3841 /*
3842 * By default this flag will be set to 'Y'.
3843 * It can be changed by the UI.
3844 */
3845 iby_disburse_submit_pub_pkg.docspayTab.straight_through_flag(l_trx_line_index) := 'Y';
3846
3847 /*
3848 * For each document, store the payment funtion
3849 * and org if unique.
3850 */
3851 deriveDistinctAccessTypsForReq(
3852 p_payreq_id,
3853 iby_disburse_submit_pub_pkg.docspayTab.payment_function(l_trx_line_index),
3854 iby_disburse_submit_pub_pkg.docspayTab.org_id(l_trx_line_index),
3855 iby_disburse_submit_pub_pkg.docspayTab.org_type(l_trx_line_index),
3856 l_pmtFxAccessTypesTab,
3857 l_orgAccessTypesTab
3858 );
3859
3860 /*
3861 * Following columns are not selected in the above cursor
3862 * They need to be initialized, otherwise, "No Data Found" is encountered.
3863 */
3864 iby_disburse_submit_pub_pkg.docspayTab.payment_id(l_trx_line_index) := NULL;
3865 iby_disburse_submit_pub_pkg.docspayTab.formatting_payment_id(l_trx_line_index) := NULL;
3866 iby_disburse_submit_pub_pkg.docspayTab.completed_pmts_group_id(l_trx_line_index) := NULL;
3867 iby_disburse_submit_pub_pkg.docspayTab.rejected_docs_group_id(l_trx_line_index) := NULL;
3868
3869 END LOOP; -- for limited set of documents fetched
3870
3871 /*
3872 * Insert document info in IBY_DOCS_PAYABLE_ALL
3873 */
3874
3875 print_debuginfo(l_module_name, 'Before insert ' );
3876
3877 FOR l_trx_line_index IN nvl(iby_disburse_submit_pub_pkg.docspayTab.pay_proc_trxn_type_code.FIRST,0) .. nvl(iby_disburse_submit_pub_pkg.docspayTab.pay_proc_trxn_type_code.LAST,-99)
3878 LOOP
3879 print_debuginfo(l_module_name, '1: ' || iby_disburse_submit_pub_pkg.docspayTab.pay_proc_trxn_type_code(l_trx_line_index));
3880 print_debuginfo(l_module_name, '2: ' || iby_disburse_submit_pub_pkg.docspayTab.calling_app_id(l_trx_line_index));
3881 print_debuginfo(l_module_name, '3: ' || iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_ref_number(l_trx_line_index));
3882 print_debuginfo(l_module_name, '4: ' || iby_disburse_submit_pub_pkg.docspayTab.document_payable_id(l_trx_line_index));
3883 print_debuginfo(l_module_name, '5: ' || iby_disburse_submit_pub_pkg.docspayTab.payment_function(l_trx_line_index));
3884 print_debuginfo(l_module_name, '6: ' || iby_disburse_submit_pub_pkg.docspayTab.payment_date(l_trx_line_index));
3885 print_debuginfo(l_module_name, '7: ' || iby_disburse_submit_pub_pkg.docspayTab.document_date(l_trx_line_index));
3886 print_debuginfo(l_module_name, '8: ' || iby_disburse_submit_pub_pkg.docspayTab.document_type(l_trx_line_index));
3887 print_debuginfo(l_module_name, '9: ' || iby_disburse_submit_pub_pkg.docspayTab.document_status(l_trx_line_index));
3888 print_debuginfo(l_module_name, '10: ' || iby_disburse_submit_pub_pkg.docspayTab.document_currency_code(l_trx_line_index));
3889 print_debuginfo(l_module_name, '11: ' || iby_disburse_submit_pub_pkg.docspayTab.document_amount(l_trx_line_index));
3890 print_debuginfo(l_module_name, '12: ' || iby_disburse_submit_pub_pkg.docspayTab.payment_currency_code(l_trx_line_index));
3891 print_debuginfo(l_module_name, '13: ' || iby_disburse_submit_pub_pkg.docspayTab.payment_amount(l_trx_line_index));
3892 print_debuginfo(l_module_name, '14: ' || iby_disburse_submit_pub_pkg.docspayTab.payment_service_request_id(l_trx_line_index));
3893 print_debuginfo(l_module_name, '15: ' || iby_disburse_submit_pub_pkg.docspayTab.payment_method_code(l_trx_line_index));
3894 print_debuginfo(l_module_name, '16: ' || iby_disburse_submit_pub_pkg.docspayTab.exclusive_payment_flag(l_trx_line_index));
3895 print_debuginfo(l_module_name, '17: ' || iby_disburse_submit_pub_pkg.docspayTab.straight_through_flag(l_trx_line_index));
3896 print_debuginfo(l_module_name, '18: ' || iby_disburse_submit_pub_pkg.docspayTab.ext_payee_id(l_trx_line_index));
3897 print_debuginfo(l_module_name, '19: ' || iby_disburse_submit_pub_pkg.docspayTab.payee_party_id(l_trx_line_index));
3898 print_debuginfo(l_module_name, '20: ' || iby_disburse_submit_pub_pkg.docspayTab.legal_entity_id(l_trx_line_index));
3899 print_debuginfo(l_module_name, '21: ' || iby_disburse_submit_pub_pkg.docspayTab.org_id(l_trx_line_index));
3900 print_debuginfo(l_module_name, '22: ' || iby_disburse_submit_pub_pkg.docspayTab.allow_removing_document_flag(l_trx_line_index));
3901 print_debuginfo(l_module_name, '23: ' || iby_disburse_submit_pub_pkg.docspayTab.created_by(l_trx_line_index));
3902 print_debuginfo(l_module_name, '24: ' || iby_disburse_submit_pub_pkg.docspayTab.creation_date(l_trx_line_index));
3903 print_debuginfo(l_module_name, '25: ' || iby_disburse_submit_pub_pkg.docspayTab.last_updated_by(l_trx_line_index));
3904 print_debuginfo(l_module_name, '26: ' || iby_disburse_submit_pub_pkg.docspayTab.last_update_date(l_trx_line_index));
3905 print_debuginfo(l_module_name, '27: ' || iby_disburse_submit_pub_pkg.docspayTab.object_version_number(l_trx_line_index));
3906 print_debuginfo(l_module_name, '28: ' || iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref1(l_trx_line_index));
3907 print_debuginfo(l_module_name, '29: ' || iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref2(l_trx_line_index));
3908 print_debuginfo(l_module_name, '30: ' || iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref3(l_trx_line_index));
3909 print_debuginfo(l_module_name, '31: ' || iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref4(l_trx_line_index));
3910 print_debuginfo(l_module_name, '32: ' || iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref5(l_trx_line_index));
3911 print_debuginfo(l_module_name, '33: ' || iby_disburse_submit_pub_pkg.docspayTab.last_update_login(l_trx_line_index));
3912 print_debuginfo(l_module_name, '34: ' || iby_disburse_submit_pub_pkg.docspayTab.party_site_id(l_trx_line_index));
3913 print_debuginfo(l_module_name, '35: ' || iby_disburse_submit_pub_pkg.docspayTab.supplier_site_id(l_trx_line_index));
3914 print_debuginfo(l_module_name, '36: ' || iby_disburse_submit_pub_pkg.docspayTab.beneficiary_party(l_trx_line_index));
3915 print_debuginfo(l_module_name, '37: ' || iby_disburse_submit_pub_pkg.docspayTab.org_type(l_trx_line_index));
3916 print_debuginfo(l_module_name, '38: ' || iby_disburse_submit_pub_pkg.docspayTab.anticipated_value_date(l_trx_line_index));
3917 print_debuginfo(l_module_name, '39: ' || iby_disburse_submit_pub_pkg.docspayTab.po_number(l_trx_line_index));
3918 print_debuginfo(l_module_name, '40: ' || iby_disburse_submit_pub_pkg.docspayTab.document_description(l_trx_line_index));
3919 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.document_currency_tax_amount(l_trx_line_index));
3920 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.document_curr_charge_amount(l_trx_line_index));
3921 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.amount_withheld(l_trx_line_index));
3922 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.payment_curr_discount_taken(l_trx_line_index));
3923 print_debuginfo(l_module_name, '45: ' || iby_disburse_submit_pub_pkg.docspayTab.discount_date(l_trx_line_index));
3924 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.payment_due_date(l_trx_line_index));
3925 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.payment_profile_id(l_trx_line_index));
3926 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.payment_id(l_trx_line_index));
3927 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.formatting_payment_id(l_trx_line_index));
3928 print_debuginfo(l_module_name, '50: ' || iby_disburse_submit_pub_pkg.docspayTab.internal_bank_account_id(l_trx_line_index));
3929 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.external_bank_account_id(l_trx_line_index));
3930 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.bank_charge_bearer(l_trx_line_index));
3931 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.interest_rate(l_trx_line_index));
3932 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.payment_grouping_number(l_trx_line_index));
3933 print_debuginfo(l_module_name, '55: ' || iby_disburse_submit_pub_pkg.docspayTab.payment_reason_code(l_trx_line_index));
3934 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.payment_reason_comments(l_trx_line_index));
3935 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.settlement_priority(l_trx_line_index));
3936 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.remittance_message1(l_trx_line_index));
3937 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.remittance_message2(l_trx_line_index));
3938 print_debuginfo(l_module_name, '60: ' || iby_disburse_submit_pub_pkg.docspayTab.remittance_message3(l_trx_line_index));
3939 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.unique_remittance_identifier(l_trx_line_index));
3940 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.uri_check_digit(l_trx_line_index));
3941 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.delivery_channel_code(l_trx_line_index));
3942 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.payment_format_code(l_trx_line_index));
3943 print_debuginfo(l_module_name, '65: ' || iby_disburse_submit_pub_pkg.docspayTab.document_sequence_id(l_trx_line_index));
3944 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.document_sequence_value(l_trx_line_index));
3945 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.document_category_code(l_trx_line_index));
3946 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.bank_assigned_ref_code(l_trx_line_index));
3947 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.remit_to_location_id(l_trx_line_index));
3948 print_debuginfo(l_module_name, '70: ' || iby_disburse_submit_pub_pkg.docspayTab.completed_pmts_group_id(l_trx_line_index));
3949 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.rejected_docs_group_id(l_trx_line_index));
3950 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.attribute_category(l_trx_line_index));
3951 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.attribute1(l_trx_line_index));
3952 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.attribute2(l_trx_line_index));
3953 print_debuginfo(l_module_name, '75: ' || iby_disburse_submit_pub_pkg.docspayTab.attribute3(l_trx_line_index));
3954 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.attribute4(l_trx_line_index));
3955 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.attribute5(l_trx_line_index));
3956 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.attribute6(l_trx_line_index));
3957 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.attribute7(l_trx_line_index));
3958 print_debuginfo(l_module_name, '80: ' || iby_disburse_submit_pub_pkg.docspayTab.attribute8(l_trx_line_index));
3959 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.attribute9(l_trx_line_index));
3960 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.attribute10(l_trx_line_index));
3961 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.attribute11(l_trx_line_index));
3962 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.attribute12(l_trx_line_index));
3963 print_debuginfo(l_module_name, '85: ' || iby_disburse_submit_pub_pkg.docspayTab.attribute13(l_trx_line_index));
3964 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.attribute14(l_trx_line_index));
3965 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.attribute15(l_trx_line_index));
3966 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.address_source(l_trx_line_index));
3967 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.employee_address_code(l_trx_line_index));
3968 print_debuginfo(l_module_name, '90: ' || iby_disburse_submit_pub_pkg.docspayTab.employee_payment_flag(l_trx_line_index));
3969 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.employee_person_id(l_trx_line_index));
3970 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.employee_address_id(l_trx_line_index));
3971 print_debuginfo(l_module_name, '95: ' || iby_disburse_submit_pub_pkg.docspayTab.inv_payee_party_id(l_trx_line_index));
3972 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.inv_party_site_id(l_trx_line_index));
3973 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.inv_supplier_site_id(l_trx_line_index));
3974 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.inv_beneficiary_party(l_trx_line_index));
3975 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.ext_inv_payee_id(l_trx_line_index));
3976 print_debuginfo(l_module_name, '1' || iby_disburse_submit_pub_pkg.docspayTab.relationship_id(l_trx_line_index));
3977 END LOOP;
3978
3979 FORALL l_trx_line_index IN nvl(iby_disburse_submit_pub_pkg.docspayTab.pay_proc_trxn_type_code.FIRST,0) .. nvl(iby_disburse_submit_pub_pkg.docspayTab.pay_proc_trxn_type_code.LAST,-99)
3980 INSERT INTO IBY_DOCS_PAYABLE_ALL
3981 (
3982 pay_proc_trxn_type_code,
3983 calling_app_id,
3984 calling_app_doc_ref_number,
3985 document_payable_id,
3986 payment_function,
3987 payment_date,
3988 document_date,
3989 document_type,
3990 document_status,
3991 document_currency_code,
3992 document_amount,
3993 payment_currency_code,
3994 payment_amount,
3995 payment_service_request_id,
3996 payment_method_code,
3997 exclusive_payment_flag,
3998 straight_through_flag,
3999 ext_payee_id,
4000 payee_party_id,
4001 legal_entity_id,
4002 org_id,
4003 allow_removing_document_flag,
4004 created_by,
4005 creation_date,
4006 last_updated_by,
4007 last_update_date,
4008 object_version_number,
4009 calling_app_doc_unique_ref1,
4010 calling_app_doc_unique_ref2,
4011 calling_app_doc_unique_ref3,
4012 calling_app_doc_unique_ref4,
4013 calling_app_doc_unique_ref5,
4014 last_update_login,
4015 party_site_id,
4016 supplier_site_id,
4017 beneficiary_party,
4018 org_type,
4019 anticipated_value_date,
4020 po_number,
4021 document_description,
4022 document_currency_tax_amount,
4023 document_curr_charge_amount,
4024 amount_withheld,
4025 payment_curr_discount_taken,
4026 discount_date,
4027 payment_due_date,
4028 payment_profile_id,
4029 payment_id,
4030 formatting_payment_id,
4031 internal_bank_account_id,
4032 external_bank_account_id,
4033 bank_charge_bearer,
4034 interest_rate,
4035 payment_grouping_number,
4036 payment_reason_code,
4037 payment_reason_comments,
4038 settlement_priority,
4039 remittance_message1,
4040 remittance_message2,
4041 remittance_message3,
4042 unique_remittance_identifier,
4043 uri_check_digit,
4044 delivery_channel_code,
4045 payment_format_code,
4046 document_sequence_id,
4047 document_sequence_value,
4048 document_category_code,
4049 bank_assigned_ref_code,
4050 remit_to_location_id,
4051 completed_pmts_group_id,
4052 rejected_docs_group_id,
4053 attribute_category,
4054 attribute1,
4055 attribute2,
4056 attribute3,
4057 attribute4,
4058 attribute5,
4059 attribute6,
4060 attribute7,
4061 attribute8,
4062 attribute9,
4063 attribute10,
4064 attribute11,
4065 attribute12,
4066 attribute13,
4067 attribute14,
4068 attribute15,
4069 address_source,
4070 employee_address_code,
4071 employee_payment_flag,
4072 employee_person_id,
4073 employee_address_id,
4074 inv_payee_party_id,
4075 inv_party_site_id,
4076 inv_supplier_site_id,
4077 inv_beneficiary_party,
4078 ext_inv_payee_id,
4079 relationship_id
4080 )
4081 VALUES
4082 (
4083 iby_disburse_submit_pub_pkg.docspayTab.pay_proc_trxn_type_code(l_trx_line_index),
4084 iby_disburse_submit_pub_pkg.docspayTab.calling_app_id(l_trx_line_index),
4085 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_ref_number(l_trx_line_index),
4086 iby_disburse_submit_pub_pkg.docspayTab.document_payable_id(l_trx_line_index),
4087 iby_disburse_submit_pub_pkg.docspayTab.payment_function(l_trx_line_index),
4088 iby_disburse_submit_pub_pkg.docspayTab.payment_date(l_trx_line_index),
4089 iby_disburse_submit_pub_pkg.docspayTab.document_date(l_trx_line_index),
4090 iby_disburse_submit_pub_pkg.docspayTab.document_type(l_trx_line_index),
4091 iby_disburse_submit_pub_pkg.docspayTab.document_status(l_trx_line_index),
4092 iby_disburse_submit_pub_pkg.docspayTab.document_currency_code(l_trx_line_index),
4093 iby_disburse_submit_pub_pkg.docspayTab.document_amount(l_trx_line_index),
4094 iby_disburse_submit_pub_pkg.docspayTab.payment_currency_code(l_trx_line_index),
4095 iby_disburse_submit_pub_pkg.docspayTab.payment_amount(l_trx_line_index),
4096 iby_disburse_submit_pub_pkg.docspayTab.payment_service_request_id(l_trx_line_index),
4097 iby_disburse_submit_pub_pkg.docspayTab.payment_method_code(l_trx_line_index),
4098 iby_disburse_submit_pub_pkg.docspayTab.exclusive_payment_flag(l_trx_line_index),
4099 iby_disburse_submit_pub_pkg.docspayTab.straight_through_flag(l_trx_line_index),
4100 iby_disburse_submit_pub_pkg.docspayTab.ext_payee_id(l_trx_line_index),
4101 iby_disburse_submit_pub_pkg.docspayTab.payee_party_id(l_trx_line_index),
4102 iby_disburse_submit_pub_pkg.docspayTab.legal_entity_id(l_trx_line_index),
4103 iby_disburse_submit_pub_pkg.docspayTab.org_id(l_trx_line_index),
4104 iby_disburse_submit_pub_pkg.docspayTab.allow_removing_document_flag(l_trx_line_index),
4105 iby_disburse_submit_pub_pkg.docspayTab.created_by(l_trx_line_index),
4106 iby_disburse_submit_pub_pkg.docspayTab.creation_date(l_trx_line_index),
4107 iby_disburse_submit_pub_pkg.docspayTab.last_updated_by(l_trx_line_index),
4108 iby_disburse_submit_pub_pkg.docspayTab.last_update_date(l_trx_line_index),
4109 iby_disburse_submit_pub_pkg.docspayTab.object_version_number(l_trx_line_index),
4110 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref1(l_trx_line_index),
4111 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref2(l_trx_line_index),
4112 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref3(l_trx_line_index),
4113 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref4(l_trx_line_index),
4114 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref5(l_trx_line_index),
4115 iby_disburse_submit_pub_pkg.docspayTab.last_update_login(l_trx_line_index),
4116 iby_disburse_submit_pub_pkg.docspayTab.party_site_id(l_trx_line_index),
4117 iby_disburse_submit_pub_pkg.docspayTab.supplier_site_id(l_trx_line_index),
4118 iby_disburse_submit_pub_pkg.docspayTab.beneficiary_party(l_trx_line_index),
4119 iby_disburse_submit_pub_pkg.docspayTab.org_type(l_trx_line_index),
4120 iby_disburse_submit_pub_pkg.docspayTab.anticipated_value_date(l_trx_line_index),
4121 iby_disburse_submit_pub_pkg.docspayTab.po_number(l_trx_line_index),
4122 iby_disburse_submit_pub_pkg.docspayTab.document_description(l_trx_line_index),
4123 iby_disburse_submit_pub_pkg.docspayTab.document_currency_tax_amount(l_trx_line_index),
4124 iby_disburse_submit_pub_pkg.docspayTab.document_curr_charge_amount(l_trx_line_index),
4125 iby_disburse_submit_pub_pkg.docspayTab.amount_withheld(l_trx_line_index),
4126 iby_disburse_submit_pub_pkg.docspayTab.payment_curr_discount_taken(l_trx_line_index),
4127 iby_disburse_submit_pub_pkg.docspayTab.discount_date(l_trx_line_index),
4128 iby_disburse_submit_pub_pkg.docspayTab.payment_due_date(l_trx_line_index),
4129 iby_disburse_submit_pub_pkg.docspayTab.payment_profile_id(l_trx_line_index),
4130 iby_disburse_submit_pub_pkg.docspayTab.payment_id(l_trx_line_index),
4131 iby_disburse_submit_pub_pkg.docspayTab.formatting_payment_id(l_trx_line_index),
4132 iby_disburse_submit_pub_pkg.docspayTab.internal_bank_account_id(l_trx_line_index),
4133 iby_disburse_submit_pub_pkg.docspayTab.external_bank_account_id(l_trx_line_index),
4134 iby_disburse_submit_pub_pkg.docspayTab.bank_charge_bearer(l_trx_line_index),
4135 iby_disburse_submit_pub_pkg.docspayTab.interest_rate(l_trx_line_index),
4136 iby_disburse_submit_pub_pkg.docspayTab.payment_grouping_number(l_trx_line_index),
4137 iby_disburse_submit_pub_pkg.docspayTab.payment_reason_code(l_trx_line_index),
4138 iby_disburse_submit_pub_pkg.docspayTab.payment_reason_comments(l_trx_line_index),
4139 iby_disburse_submit_pub_pkg.docspayTab.settlement_priority(l_trx_line_index),
4140 iby_disburse_submit_pub_pkg.docspayTab.remittance_message1(l_trx_line_index),
4141 iby_disburse_submit_pub_pkg.docspayTab.remittance_message2(l_trx_line_index),
4142 iby_disburse_submit_pub_pkg.docspayTab.remittance_message3(l_trx_line_index),
4143 iby_disburse_submit_pub_pkg.docspayTab.unique_remittance_identifier(l_trx_line_index),
4144 iby_disburse_submit_pub_pkg.docspayTab.uri_check_digit(l_trx_line_index),
4145 iby_disburse_submit_pub_pkg.docspayTab.delivery_channel_code(l_trx_line_index),
4146 iby_disburse_submit_pub_pkg.docspayTab.payment_format_code(l_trx_line_index),
4147 iby_disburse_submit_pub_pkg.docspayTab.document_sequence_id(l_trx_line_index),
4148 iby_disburse_submit_pub_pkg.docspayTab.document_sequence_value(l_trx_line_index),
4149 iby_disburse_submit_pub_pkg.docspayTab.document_category_code(l_trx_line_index),
4150 iby_disburse_submit_pub_pkg.docspayTab.bank_assigned_ref_code(l_trx_line_index),
4151 iby_disburse_submit_pub_pkg.docspayTab.remit_to_location_id(l_trx_line_index),
4152 iby_disburse_submit_pub_pkg.docspayTab.completed_pmts_group_id(l_trx_line_index),
4153 iby_disburse_submit_pub_pkg.docspayTab.rejected_docs_group_id(l_trx_line_index),
4154 iby_disburse_submit_pub_pkg.docspayTab.attribute_category(l_trx_line_index),
4155 iby_disburse_submit_pub_pkg.docspayTab.attribute1(l_trx_line_index),
4156 iby_disburse_submit_pub_pkg.docspayTab.attribute2(l_trx_line_index),
4157 iby_disburse_submit_pub_pkg.docspayTab.attribute3(l_trx_line_index),
4158 iby_disburse_submit_pub_pkg.docspayTab.attribute4(l_trx_line_index),
4159 iby_disburse_submit_pub_pkg.docspayTab.attribute5(l_trx_line_index),
4160 iby_disburse_submit_pub_pkg.docspayTab.attribute6(l_trx_line_index),
4161 iby_disburse_submit_pub_pkg.docspayTab.attribute7(l_trx_line_index),
4162 iby_disburse_submit_pub_pkg.docspayTab.attribute8(l_trx_line_index),
4163 iby_disburse_submit_pub_pkg.docspayTab.attribute9(l_trx_line_index),
4164 iby_disburse_submit_pub_pkg.docspayTab.attribute10(l_trx_line_index),
4165 iby_disburse_submit_pub_pkg.docspayTab.attribute11(l_trx_line_index),
4166 iby_disburse_submit_pub_pkg.docspayTab.attribute12(l_trx_line_index),
4167 iby_disburse_submit_pub_pkg.docspayTab.attribute13(l_trx_line_index),
4168 iby_disburse_submit_pub_pkg.docspayTab.attribute14(l_trx_line_index),
4169 iby_disburse_submit_pub_pkg.docspayTab.attribute15(l_trx_line_index),
4170 iby_disburse_submit_pub_pkg.docspayTab.address_source(l_trx_line_index),
4171 iby_disburse_submit_pub_pkg.docspayTab.employee_address_code(l_trx_line_index),
4172 iby_disburse_submit_pub_pkg.docspayTab.employee_payment_flag(l_trx_line_index),
4173 iby_disburse_submit_pub_pkg.docspayTab.employee_person_id(l_trx_line_index),
4174 iby_disburse_submit_pub_pkg.docspayTab.employee_address_id(l_trx_line_index),
4175
4176 iby_disburse_submit_pub_pkg.docspayTab.inv_payee_party_id(l_trx_line_index) ,
4177 iby_disburse_submit_pub_pkg.docspayTab.inv_party_site_id(l_trx_line_index) ,
4178 iby_disburse_submit_pub_pkg.docspayTab.inv_supplier_site_id(l_trx_line_index) ,
4179 iby_disburse_submit_pub_pkg.docspayTab.inv_beneficiary_party(l_trx_line_index) ,
4180 iby_disburse_submit_pub_pkg.docspayTab.ext_inv_payee_id(l_trx_line_index) ,
4181 iby_disburse_submit_pub_pkg.docspayTab.relationship_id(l_trx_line_index)
4182 );
4183
4184 print_debuginfo(l_module_name, 'Finished inserting'
4185 || ' documents into IBY_DOCS_PAYABLE_ALL'
4186 || ' table');
4187
4188 EXIT WHEN l_docs_cursor%NOTFOUND;
4189
4190 END LOOP; -- for documents cursor
4191
4192 /*
4193 * If no documents were provided with the payment request,
4194 * there is no point in going further.
4195 *
4196 * A pament request with no documents payable is an invalid
4197 * request. Return error response.
4198 */
4199 IF (l_no_rec_in_ppr) THEN
4200
4201 print_debuginfo(l_module_name, 'Payment request '
4202 || 'did not contain any documents. Returning error ..'
4203 );
4204
4205 print_debuginfo(l_module_name, 'EXIT');
4206
4207 l_return_status := -1;
4208 CLOSE l_docs_cursor;
4209 RETURN l_return_status;
4210
4211 END IF;
4212
4213 delete_docspayTab;
4214
4215 CLOSE l_docs_cursor;
4216
4217 /*
4218 * Insert the distinct payment functions and orgs that
4219 * were found in the documents of this request. These
4220 * will be used for limiting UI access to users.
4221 */
4222 insertDistinctAccessTypsForReq(l_pmtFxAccessTypesTab,
4223 l_orgAccessTypesTab);
4224
4225 /*
4226 * If the documents were inserted successfully, update the
4227 * status of the payment request to 'submitted'.
4228 */
4229 UPDATE
4230 IBY_PAY_SERVICE_REQUESTS
4231 SET
4232 payment_service_request_status = REQ_STATUS_SUBMITTED
4233 WHERE
4234 payment_service_request_id = p_payreq_id;
4235
4236 print_debuginfo(l_module_name, 'Updated status of'
4237 || ' payment request '
4238 || p_payreq_id
4239 || ' to "submitted"'
4240 );
4241
4242 /*
4243 * If we reached here, it means that the document
4244 * insertion was successful. Set the return status
4245 * to success.
4246 */
4247 l_return_status := 0;
4248
4249 print_debuginfo(l_module_name, 'EXIT');
4250 RETURN l_return_status;
4251
4252 EXCEPTION
4253 WHEN OTHERS THEN
4254 print_debuginfo(l_module_name, 'Exception occured when '
4255 || 'attempting to insert documents for '
4256 || 'calling app id '
4257 || p_calling_app_id
4258 || ', calling app payment service request id '
4259 || p_calling_app_payreq_cd
4260 );
4261 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
4262 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
4263
4264 print_debuginfo(l_module_name, 'EXIT');
4265 l_return_status := -1;
4266 RETURN l_return_status;
4267
4268 END insert_payreq_documents;
4269
4270 /*--------------------------------------------------------------------
4271 | NAME:
4272 | getNextDocumentPayableID
4273 |
4274 | PURPOSE:
4275 |
4276 |
4277 | PARAMETERS:
4278 | IN
4279 |
4280 |
4281 | OUT
4282 |
4283 |
4284 | RETURNS:
4285 |
4286 | NOTES:
4287 |
4288 *---------------------------------------------------------------------*/
4289 FUNCTION getNextDocumentPayableID
4290 RETURN NUMBER
4291 IS
4292
4293 l_docPayID IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE;
4294
4295 BEGIN
4296
4297 SELECT IBY_DOCS_PAYABLE_ALL_S.nextval INTO l_docPayID
4298 FROM DUAL;
4299
4300 RETURN l_docPayID;
4301
4302 END getNextDocumentPayableID;
4303
4304 /*--------------------------------------------------------------------
4305 | NAME:
4306 | getNextDocumentPayableLineID
4307 |
4308 | PURPOSE:
4309 |
4310 |
4311 | PARAMETERS:
4312 | IN
4313 |
4314 |
4315 | OUT
4316 |
4317 |
4318 | RETURNS:
4319 |
4320 | NOTES:
4321 |
4322 *---------------------------------------------------------------------*/
4323 FUNCTION getNextDocumentPayableLineID
4324 RETURN NUMBER
4325 IS
4326
4327 l_docLineID IBY_DOCUMENT_LINES.document_payable_line_id%TYPE;
4328
4329 BEGIN
4330
4331 SELECT IBY_DOCUMENT_LINES_S.nextval INTO l_docLineID
4332 FROM DUAL;
4333
4334 RETURN l_docLineID;
4335
4336 END getNextDocumentPayableLineID;
4337
4338 /*--------------------------------------------------------------------
4339 | NAME:
4340 | deriveDistinctAccessTypsForReq
4341 |
4342 | PURPOSE:
4343 |
4344 |
4345 | PARAMETERS:
4346 | IN
4347 |
4348 |
4349 | OUT
4350 |
4351 |
4352 | RETURNS:
4353 |
4354 | NOTES:
4355 |
4356 *---------------------------------------------------------------------*/
4357 PROCEDURE deriveDistinctAccessTypsForReq(
4358 p_payreq_id IN IBY_DOCS_PAYABLE_ALL.payment_service_request_id
4359 %TYPE,
4360 p_pmt_function IN IBY_DOCS_PAYABLE_ALL.payment_function%TYPE,
4361 p_org_id IN IBY_DOCS_PAYABLE_ALL.org_id%TYPE,
4362 p_org_type IN IBY_DOCS_PAYABLE_ALL.org_type%TYPE,
4363 x_pmtFxAccessTypesTab IN OUT NOCOPY distinctPmtFxAccessTab,
4364 x_orgAccessTypesTab IN OUT NOCOPY distinctOrgAccessTab
4365 )
4366 IS
4367
4368 l_pmt_function_found BOOLEAN := FALSE;
4369 l_org_found BOOLEAN := FALSE;
4370 l_index NUMBER := -1;
4371
4372 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
4373 '.deriveDistinctAccessTypsForReq';
4374
4375 BEGIN
4376
4377 print_debuginfo(l_module_name, 'ENTER');
4378
4379 IF (x_pmtFxAccessTypesTab.COUNT <> 0) THEN
4380
4381 FOR i IN x_pmtFxAccessTypesTab.FIRST .. x_pmtFxAccessTypesTab.LAST LOOP
4382
4383 /* search for given payment function */
4384 IF (x_pmtFxAccessTypesTab(i).payment_function = p_pmt_function)
4385 THEN
4386 l_pmt_function_found := TRUE;
4387 END IF;
4388
4389 END LOOP;
4390
4391 END IF;
4392
4393 IF (x_orgAccessTypesTab.COUNT <> 0) THEN
4394
4395 FOR i IN x_orgAccessTypesTab.FIRST .. x_orgAccessTypesTab.LAST LOOP
4396
4397 /* search for given org */
4398 IF (x_orgAccessTypesTab(i).org_id = p_org_id AND
4399 x_orgAccessTypesTab(i).org_type = p_org_type) THEN
4400 l_org_found := TRUE;
4401 END IF;
4402
4403 END LOOP;
4404
4405 END IF;
4406
4407
4408 /* if payment function was not found, add to list */
4409 IF (l_pmt_function_found = FALSE) THEN
4410
4411 l_index := x_pmtFxAccessTypesTab.COUNT;
4412
4413 x_pmtFxAccessTypesTab(l_index + 1).
4414 payment_function := p_pmt_function;
4415
4416 print_debuginfo(l_module_name, 'Found distinct pmt function: '
4417 || p_pmt_function
4418 );
4419
4420 /*
4421 * These attributes can be hardcoded.
4422 */
4423 x_pmtFxAccessTypesTab(l_index + 1).object_id := p_payreq_id;
4424 x_pmtFxAccessTypesTab(l_index + 1).object_type := 'PAYMENT_REQUEST';
4425
4426 END IF;
4427
4428 /* if org was not found, add to list */
4429 IF (l_org_found = FALSE) THEN
4430
4431 l_index := x_orgAccessTypesTab.COUNT;
4432
4433 x_orgAccessTypesTab(l_index + 1).org_id := p_org_id;
4434 x_orgAccessTypesTab(l_index + 1).org_type := p_org_type;
4435
4436 print_debuginfo(l_module_name, 'Found distinct org id: '
4437 || p_org_id
4438 || ' with org type '
4439 || p_org_type
4440 );
4441
4442 /*
4443 * These attributes can be hardcoded.
4444 */
4445 x_orgAccessTypesTab(l_index + 1).object_id := p_payreq_id;
4446 x_orgAccessTypesTab(l_index + 1).object_type := 'PAYMENT_REQUEST';
4447
4448 END IF;
4449
4450 print_debuginfo(l_module_name, 'EXIT');
4451
4452 END deriveDistinctAccessTypsForReq;
4453
4454 /*--------------------------------------------------------------------
4455 | NAME:
4456 | insertDistinctAccessTypsForReq
4457 |
4458 | PURPOSE:
4459 |
4460 |
4461 | PARAMETERS:
4462 | IN
4463 |
4464 |
4465 | OUT
4466 |
4467 |
4468 | RETURNS:
4469 |
4470 | NOTES:
4471 |
4472 *---------------------------------------------------------------------*/
4473 PROCEDURE insertDistinctAccessTypsForReq(
4474 p_pmtFxAccessTypesTab IN distinctPmtFxAccessTab,
4475 p_orgAccessTypesTab IN distinctOrgAccessTab
4476 )
4477 IS
4478
4479 TYPE t_object_id IS TABLE OF
4480 NUMBER(15)
4481 INDEX BY BINARY_INTEGER;
4482 TYPE t_object_type IS TABLE OF
4483 VARCHAR2(30)
4484 INDEX BY BINARY_INTEGER;
4485 TYPE t_payment_function IS TABLE OF
4486 VARCHAR2(30)
4487 INDEX BY BINARY_INTEGER;
4488 TYPE t_org_type IS TABLE OF
4489 VARCHAR2(30)
4490 INDEX BY BINARY_INTEGER;
4491 TYPE t_org_id IS TABLE OF
4492 NUMBER(15)
4493 INDEX BY BINARY_INTEGER;
4494
4495 l_object_id t_object_id;
4496 l_object_type t_object_type;
4497 l_payment_function t_payment_function;
4498 l_org_type t_org_type;
4499 l_org_id t_org_id;
4500
4501 BEGIN
4502
4503 IF (p_pmtFxAccessTypesTab.COUNT <> 0) THEN
4504
4505 FOR i IN p_pmtFxAccessTypesTab.FIRST .. p_pmtFxAccessTypesTab.LAST
4506 LOOP
4507
4508 l_object_id(i) := p_pmtFxAccessTypesTab(i).object_id;
4509 l_object_type(i) := p_pmtFxAccessTypesTab(i).object_type;
4510 l_payment_function(i) := p_pmtFxAccessTypesTab(i).payment_function;
4511
4512 END LOOP;
4513
4514 END IF;
4515
4516 IF (p_orgAccessTypesTab.COUNT <> 0) THEN
4517
4518 FOR i IN p_orgAccessTypesTab.FIRST .. p_orgAccessTypesTab.LAST
4519 LOOP
4520
4521 l_object_id(i) := p_orgAccessTypesTab(i).object_id;
4522 l_object_type(i) := p_orgAccessTypesTab(i).object_type;
4523 l_org_type(i) := p_orgAccessTypesTab(i).org_type;
4524 l_org_id(i) := p_orgAccessTypesTab(i).org_id;
4525
4526 END LOOP;
4527
4528 END IF;
4529
4530 --FORALL i in p_pmtFxAccessTypesTab.FIRST..p_pmtFxAccessTypesTab.LAST
4531 -- INSERT INTO IBY_PROCESS_FUNCTIONS VALUES p_pmtFxAccessTypesTab(i);
4532
4533 FORALL i in nvl(p_pmtFxAccessTypesTab.FIRST,0) .. nvl(p_pmtFxAccessTypesTab.LAST,-99)
4534 INSERT INTO IBY_PROCESS_FUNCTIONS
4535 (
4536 object_id,
4537 object_type,
4538 payment_function
4539 )
4540 VALUES
4541 (
4542 l_object_id(i),
4543 l_object_type(i),
4544 l_payment_function(i)
4545 )
4546 ;
4547
4548 --FORALL j in p_orgAccessTypesTab.FIRST..p_orgAccessTypesTab.LAST
4549 -- INSERT INTO IBY_PROCESS_ORGS VALUES p_orgAccessTypesTab(j);
4550
4551 FORALL j in nvl(p_orgAccessTypesTab.FIRST,0) .. nvl(p_orgAccessTypesTab.LAST,-99)
4552 INSERT INTO IBY_PROCESS_ORGS
4553 (
4554 object_id,
4555 object_type,
4556 org_id,
4557 org_type
4558 )
4559 VALUES
4560 (
4561 l_object_id(j),
4562 l_object_type(j),
4563 l_org_id(j),
4564 l_org_type(j)
4565 )
4566 ;
4567
4568 END insertDistinctAccessTypsForReq;
4569
4570 /*--------------------------------------------------------------------
4571 | NAME:
4572 | get_profile_process_attribs
4573 |
4574 |
4575 | PURPOSE:
4576 |
4577 |
4578 | PARAMETERS:
4579 | IN
4580 |
4581 |
4582 | OUT
4583 |
4584 |
4585 | RETURNS:
4586 |
4587 | NOTES:
4588 |
4589 *---------------------------------------------------------------------*/
4590 PROCEDURE get_profile_process_attribs(
4591 p_profile_id IN IBY_PAYMENT_PROFILES.payment_profile_id%TYPE,
4592 x_profile_attribs IN OUT NOCOPY profileProcessAttribs
4593 )
4594 IS
4595
4596 l_module_name VARCHAR2(200) := G_PKG_NAME || '.get_profile_process_attribs';
4597
4598 BEGIN
4599
4600 print_debuginfo(l_module_name, 'ENTER');
4601
4602 /* Bug 5709596 */
4603 IF paymentProfilesTab.exists(p_profile_id) THEN
4604 x_profile_attribs.processing_type := paymentProfilesTab(p_profile_id).processing_type;
4605 x_profile_attribs.payment_doc_id := paymentProfilesTab(p_profile_id).default_payment_document_id;
4606 x_profile_attribs.printer_name := paymentProfilesTab(p_profile_id).default_printer;
4607 x_profile_attribs.print_now_flag := paymentProfilesTab(p_profile_id).print_instruction_immed_flag;
4608 x_profile_attribs.transmit_now_flag := paymentProfilesTab(p_profile_id).transmit_instr_immed_flag;
4609 ELSE
4610 set_profile_attribs(p_profile_id);
4611 x_profile_attribs.processing_type := paymentProfilesTab(p_profile_id).processing_type;
4612 x_profile_attribs.payment_doc_id := paymentProfilesTab(p_profile_id).default_payment_document_id;
4613 x_profile_attribs.printer_name := paymentProfilesTab(p_profile_id).default_printer;
4614 x_profile_attribs.print_now_flag := paymentProfilesTab(p_profile_id).print_instruction_immed_flag;
4615 x_profile_attribs.transmit_now_flag := paymentProfilesTab(p_profile_id).transmit_instr_immed_flag;
4616 END IF;
4617
4618 BEGIN
4619 SELECT
4620 cedoc.payment_document_id
4621 INTO
4622 x_profile_attribs.payment_doc_id
4623 FROM
4624 CE_PAYMENT_DOCUMENTS cedoc
4625 WHERE
4626 cedoc.payment_document_id = x_profile_attribs.payment_doc_id;
4627 EXCEPTION
4628 WHEN OTHERS THEN
4629 x_profile_attribs.payment_doc_id := null;
4630 END;
4631 /* Bug 5709596 */
4632
4633 print_debuginfo(l_module_name, 'EXIT');
4634
4635 EXCEPTION
4636 WHEN OTHERS THEN
4637
4638 print_debuginfo(l_module_name, 'Exception occured '
4639 || 'when attempting to get processing attributes '
4640 || 'for profile '
4641 || p_profile_id
4642 || '. Payment process likely to fail .. '
4643 );
4644
4645 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
4646 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
4647
4648 print_debuginfo(l_module_name, 'EXIT');
4649
4650 END get_profile_process_attribs;
4651
4652 /*--------------------------------------------------------------------
4653 | NAME:
4654 | checkIfDefaultPmtDocOnProfile
4655 |
4656 | PURPOSE:
4657 |
4658 |
4659 | PARAMETERS:
4660 | IN
4661 |
4662 |
4663 | OUT
4664 |
4665 |
4666 | RETURNS:
4667 |
4668 | NOTES:
4669 |
4670 *---------------------------------------------------------------------*/
4671 PROCEDURE checkIfDefaultPmtDocOnProfile (
4672 p_profile_id IN IBY_PAYMENT_PROFILES.payment_profile_id%TYPE,
4673 x_profile_name IN OUT NOCOPY IBY_PAYMENT_PROFILES.system_profile_name%TYPE,
4674 x_return_flag IN OUT NOCOPY BOOLEAN
4675 )
4676
4677 IS
4678
4679 l_default_pmt_doc_id IBY_PAYMENT_PROFILES.payment_profile_id%TYPE;
4680 l_module_name VARCHAR2(200) := G_PKG_NAME
4681 || '.checkIfDefaultPmtDocOnProfile';
4682
4683 BEGIN
4684
4685 print_debuginfo(l_module_name, 'ENTER');
4686
4687 BEGIN
4688
4689 /* Bug 5709596 */
4690 IF paymentProfilesTab.exists(p_profile_id) THEN
4691 l_default_pmt_doc_id := paymentProfilesTab(p_profile_id).default_payment_document_id;
4692 x_profile_name := paymentProfilesTab(p_profile_id).system_profile_name;
4693 ELSE
4694 set_profile_attribs(p_profile_id);
4695 l_default_pmt_doc_id := paymentProfilesTab(p_profile_id).default_payment_document_id;
4696 x_profile_name := paymentProfilesTab(p_profile_id).system_profile_name;
4697 END IF;
4698 /* Bug 5709596 */
4699
4700 print_debuginfo(l_module_name, 'Profile id '
4701 || p_profile_id
4702 || ' with name '
4703 || ''''
4704 || x_profile_name
4705 || ''''
4706 || ' has default payment doc id: '
4707 || l_default_pmt_doc_id
4708 );
4709
4710 IF (l_default_pmt_doc_id IS NOT NULL) THEN
4711 /*
4712 * If we reached here, it means that a default payment
4713 * document is associated with the provided payment
4714 * profile.
4715 *
4716 * So, return TRUE.
4717 */
4718 x_return_flag := TRUE;
4719 ELSE
4720 x_return_flag := FALSE;
4721 END IF;
4722
4723 EXCEPTION
4724 WHEN OTHERS THEN
4725
4726 /*
4727 * If any exceptions occur, return FALSE.
4728 */
4729 x_return_flag := FALSE;
4730
4731 print_debuginfo(l_module_name, 'Non-fatal: Exception occured '
4732 || 'when attempting to get default payment doc id '
4733 || 'for profile '
4734 || p_profile_id
4735 );
4736
4737 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
4738 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
4739
4740 END;
4741
4742 IF (x_return_flag = TRUE) THEN
4743 print_debuginfo(l_module_name, 'Returning TRUE');
4744 ELSE
4745 print_debuginfo(l_module_name, 'Returning FALSE');
4746 END IF;
4747
4748 print_debuginfo(l_module_name, 'EXIT');
4749
4750 END checkIfDefaultPmtDocOnProfile;
4751
4752 /*--------------------------------------------------------------------
4753 | NAME:
4754 | launchPPRStatusReport
4755 |
4756 | PURPOSE:
4757 |
4758 |
4759 | PARAMETERS:
4760 | IN
4761 |
4762 |
4763 | OUT
4764 |
4765 |
4766 | RETURNS:
4767 |
4768 | NOTES:
4769 |
4770 *---------------------------------------------------------------------*/
4771 PROCEDURE launchPPRStatusReport(
4772 p_payreq_id IN IBY_PAY_SERVICE_REQUESTS.
4773 payment_service_request_id%TYPE
4774 )
4775 IS
4776 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
4777 '.launchPPRStatusReport';
4778
4779 l_report_format IBY_INTERNAL_PAYERS_ALL.ppr_report_format%TYPE;
4780 l_report_flag IBY_INTERNAL_PAYERS_ALL.automatic_ppr_report_submit%TYPE;
4781
4782 l_app_short_name VARCHAR2(200);
4783 l_ca_payreq_cd IBY_PAY_SERVICE_REQUESTS.call_app_pay_service_req_code%TYPE;
4784 l_ca_id IBY_PAY_SERVICE_REQUESTS.calling_app_id%TYPE;
4785 l_conc_req_id NUMBER(15);
4786
4787 l_bool_val BOOLEAN; -- Bug 6411356
4788
4789 l_icx_numeric_characters VARCHAR2(30); -- Bug 6411356
4790
4791
4792 BEGIN
4793
4794 print_debuginfo(l_module_name, 'ENTER');
4795
4796 /*
4797 * Fetch the system options at the enterprise level
4798 * (i.e., where org id is null).
4799 */
4800 SELECT
4801 automatic_ppr_report_submit,
4802 ppr_report_format
4803 INTO
4804 l_report_flag,
4805 l_report_format
4806 FROM
4807 IBY_INTERNAL_PAYERS_ALL sysoptions
4808 WHERE
4809 sysoptions.org_id IS NULL
4810 ;
4811
4812 print_debuginfo(l_module_name, 'Enterprise level settings - '
4813 || 'Automatic ppr status report submit flag: '
4814 || l_report_flag
4815 || ', Report format: '
4816 || l_report_format
4817 );
4818
4819 IF (l_report_flag <> 'Y' OR l_report_format IS NULL) THEN
4820
4821 print_debuginfo(l_module_name, 'Not launching '
4822 || 'automatic ppr status report; both report '
4823 || 'flag and report format need to be set '
4824 || 'at enterprise level to launch this report.'
4825 );
4826
4827 print_debuginfo(l_module_name, 'EXIT');
4828
4829 RETURN;
4830
4831 END IF;
4832
4833 /*
4834 * If we reached here, it means that the user has specified
4835 * via enterprise level settings, the the payment process
4836 * status report needs to be launched for each PPR.
4837 */
4838
4839 /*
4840 * Get the application name of the calling app. This
4841 * will be used in the callout.
4842 */
4843 SELECT
4844 fnd.application_short_name
4845 INTO
4846 l_app_short_name
4847 FROM
4848 FND_APPLICATION fnd,
4849 IBY_PAY_SERVICE_REQUESTS req
4850 WHERE
4851 fnd.application_id = req.calling_app_id AND
4852 req.payment_service_request_id = p_payreq_id
4853 ;
4854
4855 print_debuginfo(l_module_name, 'Calling app short name: '
4856 || l_app_short_name
4857 );
4858
4859 /*
4860 * Get the calling application payreq code for the
4861 * given payment request id.
4862 */
4863 IBY_VALIDATIONSETS_PUB.getRequestAttributes(
4864 p_payreq_id, l_ca_payreq_cd, l_ca_id);
4865
4866 print_debuginfo(l_module_name, 'Calling app request code: '
4867 || l_ca_payreq_cd
4868 );
4869
4870 /*
4871 * Launch the report.
4872 */
4873
4874 /*
4875 * Fix for bug 5407120:
4876 *
4877 * Before kicking off reports / formats make sure that
4878 * the numeric characters delimiter is correctly set
4879 * by using FND_REQUEST.SET_OPTIONS(..).
4880 *
4881 * The argument provided to this method is based on
4882 * the lookup ICX_NUMERIC_CHARACTERS.
4883 *
4884 * Otherwise, the num delimiter would be picked up
4885 * based on NLS territory and could cause problems.
4886 *
4887 * E.g., $10000.52 would be displayed as $10.000,52 for
4888 * PL territory and this causes problems to XML publisher.
4889 */
4890 --Bug 6411356
4891 --below code added to set the current nls character setting
4892 --before submitting a child requests.
4893 fnd_profile.get('ICX_NUMERIC_CHARACTERS',l_icx_numeric_characters);
4894 l_bool_val := FND_REQUEST.SET_OPTIONS( numeric_characters => l_icx_numeric_characters);
4895
4896 l_conc_req_id := FND_REQUEST.SUBMIT_REQUEST(
4897 'IBY',
4898 'IBY_FD_PPR_STATUS_PRT',
4899 '',
4900 '',
4901 FALSE,
4902
4903 l_app_short_name,
4904 l_ca_payreq_cd,
4905 l_report_format,
4906 '',
4907
4908 '', '', '', '', '', '', '',
4909 '', '', '', '', '', '', '', '', '', '',
4910 '', '', '', '', '', '', '', '', '', '',
4911 '', '', '', '', '', '', '', '', '', '',
4912 '', '', '', '', '', '', '', '', '', '',
4913 '', '', '', '', '', '', '', '', '', '',
4914 '', '', '', '', '', '', '', '', '', '',
4915 '', '', '', '', '', '', '', '', '', '',
4916 '', '', '', '', '', '', '', '', '', '',
4917 '', '', '', '', '', '', ''
4918 );
4919
4920 IF (l_conc_req_id = 0) THEN
4921
4922 print_debuginfo(l_module_name, 'Concurrent program request failed.');
4923
4924 ELSE
4925
4926 print_debuginfo(l_module_name, 'The concurrent request was '
4927 || 'launched successfully. '
4928 || 'Check concurrent request id: '
4929 || to_char(l_conc_req_id)
4930 );
4931
4932 END IF;
4933
4934 print_debuginfo(l_module_name, 'EXIT');
4935
4936 EXCEPTION
4937 WHEN OTHERS THEN
4938
4939 /*
4940 * Treat this exception as non-fatal.
4941 *
4942 * It's not the end of the world if we couldn't
4943 * launch a report.
4944 */
4945 print_debuginfo(l_module_name, 'Non-Fatal: Exception occured '
4946 || 'when attempting to launch the automatic payment process '
4947 || 'request status report.'
4948 );
4949
4950 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
4951 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
4952
4953 print_debuginfo(l_module_name, 'EXIT');
4954
4955 END launchPPRStatusReport;
4956
4957 /*--------------------------------------------------------------------
4958 | NAME:
4959 | set_profile_attribs
4960 |
4961 |
4962 | PURPOSE:
4963 | Sets the attributes of the payment profile structure
4964 |
4965 | PARAMETERS:
4966 | IN
4967 |
4968 |
4969 | OUT
4970 |
4971 |
4972 | RETURNS:
4973 |
4974 | NOTES:
4975 |
4976 *---------------------------------------------------------------------*/
4977 PROCEDURE set_profile_attribs(
4978 p_profile_id IN IBY_PAYMENT_PROFILES.payment_profile_id%TYPE
4979 ) IS
4980 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
4981 '.set_profile_attribs';
4982 l_ppp_rec IBY_PAYMENT_PROFILES%rowtype;
4983 BEGIN
4984
4985 print_debuginfo(l_module_name, 'ENTER');
4986
4987 BEGIN
4988 SELECT *
4989 INTO l_ppp_rec
4990 FROM IBY_PAYMENT_PROFILES
4991 WHERE payment_profile_id = p_profile_id;
4992 EXCEPTION
4993 WHEN OTHERS THEN
4994 l_ppp_rec := null;
4995 END;
4996 paymentProfilesTab(p_profile_id) := l_ppp_rec;
4997
4998 print_debuginfo(l_module_name, 'EXIT');
4999
5000 END set_profile_attribs;
5001
5002
5003
5004 /*--------------------------------------------------------------------
5005 | NAME:
5006 |
5007 | PURPOSE:
5008 | This procedure is used to free up the memory used by
5009 | global memory structure
5010 |
5011 | PARAMETERS:
5012 |
5013 | NONE
5014 |
5015 | RETURNS:
5016 |
5017 | NOTES:
5018 |
5019 *---------------------------------------------------------------------*/
5020 PROCEDURE delete_docspayTab IS
5021 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
5022 '.delete_docspayTab';
5023 BEGIN
5024
5025 print_debuginfo(l_module_name, 'ENTER');
5026 iby_disburse_submit_pub_pkg.docspayTab.pay_proc_trxn_type_code.delete;
5027 iby_disburse_submit_pub_pkg.docspayTab.calling_app_id.delete;
5028 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_ref_number.delete;
5029 iby_disburse_submit_pub_pkg.docspayTab.call_app_pay_service_req_code.delete;
5030 iby_disburse_submit_pub_pkg.docspayTab.document_payable_id.delete;
5031 iby_disburse_submit_pub_pkg.docspayTab.payment_function.delete;
5032 iby_disburse_submit_pub_pkg.docspayTab.payment_date.delete;
5033 iby_disburse_submit_pub_pkg.docspayTab.document_date.delete;
5034 iby_disburse_submit_pub_pkg.docspayTab.document_type.delete;
5035 iby_disburse_submit_pub_pkg.docspayTab.document_status.delete;
5036 iby_disburse_submit_pub_pkg.docspayTab.document_currency_code.delete;
5037 iby_disburse_submit_pub_pkg.docspayTab.document_amount.delete;
5038 iby_disburse_submit_pub_pkg.docspayTab.payment_currency_code.delete;
5039 iby_disburse_submit_pub_pkg.docspayTab.payment_amount.delete;
5040 iby_disburse_submit_pub_pkg.docspayTab.payment_service_request_id.delete;
5041 iby_disburse_submit_pub_pkg.docspayTab.payment_method_code.delete;
5042 iby_disburse_submit_pub_pkg.docspayTab.exclusive_payment_flag.delete;
5043 iby_disburse_submit_pub_pkg.docspayTab.straight_through_flag.delete;
5044 iby_disburse_submit_pub_pkg.docspayTab.ext_payee_id.delete;
5045 iby_disburse_submit_pub_pkg.docspayTab.payee_party_id.delete;
5046 iby_disburse_submit_pub_pkg.docspayTab.legal_entity_id.delete;
5047 iby_disburse_submit_pub_pkg.docspayTab.org_id.delete;
5048 iby_disburse_submit_pub_pkg.docspayTab.allow_removing_document_flag.delete;
5049 iby_disburse_submit_pub_pkg.docspayTab.created_by.delete;
5050 iby_disburse_submit_pub_pkg.docspayTab.creation_date.delete;
5051 iby_disburse_submit_pub_pkg.docspayTab.last_updated_by.delete;
5052 iby_disburse_submit_pub_pkg.docspayTab.last_update_date.delete;
5053 iby_disburse_submit_pub_pkg.docspayTab.object_version_number.delete;
5054 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref1.delete;
5055 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref2.delete;
5056 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref3.delete;
5057 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref4.delete;
5058 iby_disburse_submit_pub_pkg.docspayTab.calling_app_doc_unique_ref5.delete;
5059 iby_disburse_submit_pub_pkg.docspayTab.last_update_login.delete;
5060 iby_disburse_submit_pub_pkg.docspayTab.party_site_id.delete;
5061 iby_disburse_submit_pub_pkg.docspayTab.supplier_site_id.delete;
5062 iby_disburse_submit_pub_pkg.docspayTab.beneficiary_party.delete;
5063 iby_disburse_submit_pub_pkg.docspayTab.org_type.delete;
5064 iby_disburse_submit_pub_pkg.docspayTab.anticipated_value_date.delete;
5065 iby_disburse_submit_pub_pkg.docspayTab.po_number.delete;
5066 iby_disburse_submit_pub_pkg.docspayTab.document_description.delete;
5067 iby_disburse_submit_pub_pkg.docspayTab.document_currency_tax_amount.delete;
5068 iby_disburse_submit_pub_pkg.docspayTab.document_curr_charge_amount.delete;
5069 iby_disburse_submit_pub_pkg.docspayTab.amount_withheld.delete;
5070 iby_disburse_submit_pub_pkg.docspayTab.payment_curr_discount_taken.delete;
5071 iby_disburse_submit_pub_pkg.docspayTab.discount_date.delete;
5072 iby_disburse_submit_pub_pkg.docspayTab.payment_due_date.delete;
5073 iby_disburse_submit_pub_pkg.docspayTab.payment_profile_id.delete;
5074 iby_disburse_submit_pub_pkg.docspayTab.payment_id.delete;
5075 iby_disburse_submit_pub_pkg.docspayTab.formatting_payment_id.delete;
5076 iby_disburse_submit_pub_pkg.docspayTab.internal_bank_account_id.delete;
5077 iby_disburse_submit_pub_pkg.docspayTab.external_bank_account_id.delete;
5078 iby_disburse_submit_pub_pkg.docspayTab.bank_charge_bearer.delete;
5079 iby_disburse_submit_pub_pkg.docspayTab.interest_rate.delete;
5080 iby_disburse_submit_pub_pkg.docspayTab.payment_grouping_number.delete;
5081 iby_disburse_submit_pub_pkg.docspayTab.payment_reason_code.delete;
5082 iby_disburse_submit_pub_pkg.docspayTab.payment_reason_comments.delete;
5083 iby_disburse_submit_pub_pkg.docspayTab.settlement_priority.delete;
5084 iby_disburse_submit_pub_pkg.docspayTab.remittance_message1.delete;
5085 iby_disburse_submit_pub_pkg.docspayTab.remittance_message2.delete;
5086 iby_disburse_submit_pub_pkg.docspayTab.remittance_message3.delete;
5087 iby_disburse_submit_pub_pkg.docspayTab.unique_remittance_identifier.delete;
5088 iby_disburse_submit_pub_pkg.docspayTab.uri_check_digit.delete;
5089 iby_disburse_submit_pub_pkg.docspayTab.delivery_channel_code.delete;
5090 iby_disburse_submit_pub_pkg.docspayTab.payment_format_code.delete;
5091 iby_disburse_submit_pub_pkg.docspayTab.document_sequence_id.delete;
5092 iby_disburse_submit_pub_pkg.docspayTab.document_sequence_value.delete;
5093 iby_disburse_submit_pub_pkg.docspayTab.document_category_code.delete;
5094 iby_disburse_submit_pub_pkg.docspayTab.bank_assigned_ref_code.delete;
5095 iby_disburse_submit_pub_pkg.docspayTab.remit_to_location_id.delete;
5096 iby_disburse_submit_pub_pkg.docspayTab.completed_pmts_group_id.delete;
5097 iby_disburse_submit_pub_pkg.docspayTab.rejected_docs_group_id.delete;
5098 iby_disburse_submit_pub_pkg.docspayTab.attribute_category.delete;
5099 iby_disburse_submit_pub_pkg.docspayTab.attribute1.delete;
5100 iby_disburse_submit_pub_pkg.docspayTab.attribute2.delete;
5101 iby_disburse_submit_pub_pkg.docspayTab.attribute3.delete;
5102 iby_disburse_submit_pub_pkg.docspayTab.attribute4.delete;
5103 iby_disburse_submit_pub_pkg.docspayTab.attribute5.delete;
5104 iby_disburse_submit_pub_pkg.docspayTab.attribute6.delete;
5105 iby_disburse_submit_pub_pkg.docspayTab.attribute7.delete;
5106 iby_disburse_submit_pub_pkg.docspayTab.attribute8.delete;
5107 iby_disburse_submit_pub_pkg.docspayTab.attribute9.delete;
5108 iby_disburse_submit_pub_pkg.docspayTab.attribute10.delete;
5109 iby_disburse_submit_pub_pkg.docspayTab.attribute11.delete;
5110 iby_disburse_submit_pub_pkg.docspayTab.attribute12.delete;
5111 iby_disburse_submit_pub_pkg.docspayTab.attribute13.delete;
5112 iby_disburse_submit_pub_pkg.docspayTab.attribute14.delete;
5113 iby_disburse_submit_pub_pkg.docspayTab.attribute15.delete;
5114 iby_disburse_submit_pub_pkg.docspayTab.address_source.delete;
5115 iby_disburse_submit_pub_pkg.docspayTab.employee_address_code.delete;
5116 iby_disburse_submit_pub_pkg.docspayTab.employee_payment_flag.delete;
5117 iby_disburse_submit_pub_pkg.docspayTab.employee_person_id.delete;
5118 iby_disburse_submit_pub_pkg.docspayTab.employee_address_id.delete;
5119 iby_disburse_submit_pub_pkg.docspayTab.bank_instruction1_code.delete;
5120 iby_disburse_submit_pub_pkg.docspayTab.bank_instruction2_code.delete;
5121 iby_disburse_submit_pub_pkg.docspayTab.payment_text_message1.delete;
5122 iby_disburse_submit_pub_pkg.docspayTab.payment_text_message2.delete;
5123 iby_disburse_submit_pub_pkg.docspayTab.payment_text_message3.delete;
5124 iby_disburse_submit_pub_pkg.docspayTab.group_by_remittance_message.delete;
5125 iby_disburse_submit_pub_pkg.docspayTab.group_by_bank_charge_bearer.delete;
5126 iby_disburse_submit_pub_pkg.docspayTab.group_by_delivery_channel.delete;
5127 iby_disburse_submit_pub_pkg.docspayTab.group_by_settle_priority_flag.delete;
5128 iby_disburse_submit_pub_pkg.docspayTab.group_by_payment_details_flag.delete;
5129 iby_disburse_submit_pub_pkg.docspayTab.payment_details_length_limit.delete;
5130 iby_disburse_submit_pub_pkg.docspayTab.payment_details_formula.delete;
5131 iby_disburse_submit_pub_pkg.docspayTab.group_by_max_documents_flag.delete;
5132 iby_disburse_submit_pub_pkg.docspayTab.max_documents_per_payment.delete;
5133 iby_disburse_submit_pub_pkg.docspayTab.group_by_unique_remit_id_flag.delete;
5134 iby_disburse_submit_pub_pkg.docspayTab.group_by_payment_reason.delete;
5135 iby_disburse_submit_pub_pkg.docspayTab.group_by_due_date_flag.delete;
5136 iby_disburse_submit_pub_pkg.docspayTab.processing_type.delete;
5137 iby_disburse_submit_pub_pkg.docspayTab.declaration_option.delete;
5138 iby_disburse_submit_pub_pkg.docspayTab.dcl_only_foreign_curr_pmt_flag.delete;
5139 iby_disburse_submit_pub_pkg.docspayTab.declaration_curr_fx_rate_type.delete;
5140 iby_disburse_submit_pub_pkg.docspayTab.declaration_currency_code.delete;
5141 iby_disburse_submit_pub_pkg.docspayTab.declaration_threshold_amount.delete;
5142 iby_disburse_submit_pub_pkg.docspayTab.maximum_payment_amount.delete;
5143 iby_disburse_submit_pub_pkg.docspayTab.minimum_payment_amount.delete;
5144 iby_disburse_submit_pub_pkg.docspayTab.allow_zero_payments_flag.delete;
5145 iby_disburse_submit_pub_pkg.docspayTab.support_bills_payable_flag.delete;
5146 iby_disburse_submit_pub_pkg.docspayTab.iba_legal_entity_id.delete;
5147 iby_disburse_submit_pub_pkg.docspayTab.int_bank_country_code.delete;
5148 iby_disburse_submit_pub_pkg.docspayTab.ext_bank_country_code.delete;
5149 iby_disburse_submit_pub_pkg.docspayTab.foreign_pmts_allowed_flag.delete;
5150 iby_disburse_submit_pub_pkg.docspayTab.inv_payee_party_id.delete;
5151 iby_disburse_submit_pub_pkg.docspayTab.inv_party_site_id.delete;
5152 iby_disburse_submit_pub_pkg.docspayTab.inv_supplier_site_id.delete;
5153 iby_disburse_submit_pub_pkg.docspayTab.inv_beneficiary_party.delete;
5154 iby_disburse_submit_pub_pkg.docspayTab.ext_inv_payee_id.delete;
5155 iby_disburse_submit_pub_pkg.docspayTab.relationship_id.delete;
5156 print_debuginfo(l_module_name, 'EXIT');
5157
5158 END delete_docspayTab;
5159
5160 /*--------------------------------------------------------------------
5161 | NAME:
5162 | print_debuginfo
5163 |
5164 | PURPOSE:
5165 |
5166 |
5167 | PARAMETERS:
5168 | IN
5169 |
5170 |
5171 | OUT
5172 |
5173 |
5174 | RETURNS:
5175 |
5176 | NOTES:
5177 |
5178 *---------------------------------------------------------------------*/
5179 PROCEDURE print_debuginfo(
5180 p_module IN VARCHAR2,
5181 p_debug_text IN VARCHAR2,
5182 p_debug_level IN VARCHAR2 DEFAULT FND_LOG.LEVEL_STATEMENT
5183 )
5184 IS
5185 l_default_debug_level VARCHAR2(200) := FND_LOG.LEVEL_STATEMENT;
5186 BEGIN
5187
5188 /*
5189 * Set the debug level to the value passed in
5190 * (provided this value is not null).
5191 */
5192 IF (p_debug_level IS NOT NULL) THEN
5193 l_default_debug_level := p_debug_level;
5194 END IF;
5195
5196
5197 /*
5198 * Write the debug message to the concurrent manager log file.
5199 */
5200 IF (l_default_debug_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5201 iby_build_utils_pkg.print_debuginfo(p_module, p_debug_text,
5202 p_debug_level);
5203 END IF;
5204
5205 END print_debuginfo;
5206
5207
5208 END IBY_DISBURSE_SUBMIT_PUB_PKG;