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