[Home] [Help]
PACKAGE BODY: APPS.IBY_BUILD_INSTRUCTIONS_PUB_PKG
Source
1 PACKAGE BODY IBY_BUILD_INSTRUCTIONS_PUB_PKG AS
2 /*$Header: ibypayib.pls 120.55.12020000.3 2013/02/14 11:55:38 jnallam ship $*/
3
4 --
5 -- Declare global variables
6 --
7 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
8 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
9 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_BUILD_INSTRUCTIONS_PUB_PKG';
10
11 --
12 -- List of instruction statuses that are used / set in this
13 -- module (payment instruction creation flow).
14 --
15 INS_STATUS_CREATED CONSTANT VARCHAR2(100) := 'CREATED';
16 INS_STATUS_PRINT_DEFERRED CONSTANT VARCHAR2(100) := 'PRINTING_DEFERRED';
17 INSTR_STATUS_RETRY_CREAT CONSTANT VARCHAR2(100) := 'RETRY_CREATION';
18 INSTR_STATUS_CREAT_ERROR CONSTANT VARCHAR2(100) := 'CREATION_ERROR';
19 INS_STATUS_READY_TO_PRINT CONSTANT VARCHAR2(100) :=
20 'CREATED_READY_FOR_PRINTING';
21 INS_STATUS_READY_TO_FORMAT CONSTANT VARCHAR2(100) :=
22 'CREATED_READY_FOR_FORMATTING';
23
24
25 PROCEDURE Validate_and_Set_Syskey
26 (
27 p_sys_key IN iby_security_pkg.des3_key_type
28 )
29 IS
30 lx_err_code VARCHAR2(30);
31 BEGIN
32
33
34 IF (NOT p_sys_key IS NULL) THEN
35 iby_security_pkg.validate_sys_key(p_sys_key,lx_err_code);
36 IF (NOT lx_err_code IS NULL) THEN
37 raise_application_error(-20000,lx_err_code, FALSE);
38 END IF;
39 iby_utility_pvt.set_view_param('SYS_KEY',p_sys_key);
40 END IF;
41
42 END Validate_and_Set_Syskey;
43
44
45 /*--------------------------------------------------------------------
46 | NAME:
47 | build_pmt_instructions
48 |
49 | PURPOSE:
50 | This is the top level procedure of the payment instruction
51 | creation program; This procedure will run as a concurrent program.
52 |
53 | PARAMETERS:
54 | IN
55 |
56 | p_calling_app_id
57 | The 3-character product code of the calling application
58 |
59 | p_calling_app_payreq_cd
60 | Id of the payment service request from the calling app's
61 | point of view. For a given calling app, this id should be
62 | unique; the build program will communicate back to the calling
63 | app using this payment request id.
64 |
65 | p_internal_bank_account_id
66 | The internal bank account to pay from.
67 |
68 | p_payment_profile_id
69 | Payment profile
70 |
71 | p_allow_zero_payments_flag
72 | 'Y' / 'N' flag indicating whether zero value payments are allowed.
73 | If not set, this value will be defaulted to 'Y'.
74 |
75 | p_payment_date
76 | The payment date.
77 |
78 | p_anticipated_value_date
79 | The anticipated value date.
80 |
81 | p_args10 - p_args100
82 | These 91 parameters are mandatory for any stored procedure
83 | that is submitted from Oracle Forms as a concurrent request
84 | (to get the total number of args to the concurrent procedure
85 | to 100).
86 |
87 | OUT
88 |
89 | x_errbuf
90 | x_retcode
91 |
92 | These two are mandatory output paramaters for a concurrent
93 | program. They will store the error message and error code
94 | to indicate a successful/failed run of the concurrent request.
95 |
96 | RETURNS:
97 |
98 | NOTES:
99 |
100 *---------------------------------------------------------------------*/
101 PROCEDURE build_pmt_instructions(
102 x_errbuf OUT NOCOPY VARCHAR2,
103 x_retcode OUT NOCOPY VARCHAR2,
104
105 /*-- processing criteria --*/
106 p_processing_type IN VARCHAR2,
107 p_payment_profile_id IN VARCHAR2 DEFAULT NULL,
108 p_pmt_document_id IN VARCHAR2 DEFAULT NULL,
109 p_internal_bank_account_id IN VARCHAR2 DEFAULT NULL,
110 p_print_now_flag IN VARCHAR2 DEFAULT NULL,
111 p_printer_name IN VARCHAR2 DEFAULT NULL,
112 p_payment_currency IN VARCHAR2 DEFAULT NULL,
113 p_transmit_now_flag IN VARCHAR2 DEFAULT NULL,
114
115 /*-- user/admin assigned criteria --*/
116 p_admin_assigned_ref IN VARCHAR2 DEFAULT NULL,
117 p_comments IN VARCHAR2 DEFAULT NULL,
118
119 /*-- selection criteria --*/
120 p_calling_app_id IN VARCHAR2 DEFAULT NULL,
121 p_calling_app_payreq_cd IN VARCHAR2 DEFAULT NULL,
122 p_le_id IN VARCHAR2 DEFAULT NULL,
123 p_org_id IN VARCHAR2 DEFAULT NULL,
124 p_org_type IN VARCHAR2 DEFAULT NULL,
125 p_payment_from_date IN VARCHAR2 DEFAULT NULL,
126 p_payment_to_date IN VARCHAR2 DEFAULT NULL,
127
128 p_arg19 IN VARCHAR2 DEFAULT NULL,
129 p_arg20 IN VARCHAR2 DEFAULT NULL, p_arg21 IN VARCHAR2 DEFAULT NULL,
130 p_arg22 IN VARCHAR2 DEFAULT NULL, p_arg23 IN VARCHAR2 DEFAULT NULL,
131 p_arg24 IN VARCHAR2 DEFAULT NULL, p_arg25 IN VARCHAR2 DEFAULT NULL,
132 p_arg26 IN VARCHAR2 DEFAULT NULL, p_arg27 IN VARCHAR2 DEFAULT NULL,
133 p_arg28 IN VARCHAR2 DEFAULT NULL, p_arg29 IN VARCHAR2 DEFAULT NULL,
134 p_arg30 IN VARCHAR2 DEFAULT NULL, p_arg31 IN VARCHAR2 DEFAULT NULL,
135 p_arg32 IN VARCHAR2 DEFAULT NULL, p_arg33 IN VARCHAR2 DEFAULT NULL,
136 p_arg34 IN VARCHAR2 DEFAULT NULL, p_arg35 IN VARCHAR2 DEFAULT NULL,
137 p_arg36 IN VARCHAR2 DEFAULT NULL, p_arg37 IN VARCHAR2 DEFAULT NULL,
138 p_arg38 IN VARCHAR2 DEFAULT NULL, p_arg39 IN VARCHAR2 DEFAULT NULL,
139 p_arg40 IN VARCHAR2 DEFAULT NULL, p_arg41 IN VARCHAR2 DEFAULT NULL,
140 p_arg42 IN VARCHAR2 DEFAULT NULL, p_arg43 IN VARCHAR2 DEFAULT NULL,
141 p_arg44 IN VARCHAR2 DEFAULT NULL, p_arg45 IN VARCHAR2 DEFAULT NULL,
142 p_arg46 IN VARCHAR2 DEFAULT NULL, p_arg47 IN VARCHAR2 DEFAULT NULL,
143 p_arg48 IN VARCHAR2 DEFAULT NULL, p_arg49 IN VARCHAR2 DEFAULT NULL,
144 p_arg50 IN VARCHAR2 DEFAULT NULL, p_arg51 IN VARCHAR2 DEFAULT NULL,
145 p_arg52 IN VARCHAR2 DEFAULT NULL, p_arg53 IN VARCHAR2 DEFAULT NULL,
146 p_arg54 IN VARCHAR2 DEFAULT NULL, p_arg55 IN VARCHAR2 DEFAULT NULL,
147 p_arg56 IN VARCHAR2 DEFAULT NULL, p_arg57 IN VARCHAR2 DEFAULT NULL,
148 p_arg58 IN VARCHAR2 DEFAULT NULL, p_arg59 IN VARCHAR2 DEFAULT NULL,
149 p_arg60 IN VARCHAR2 DEFAULT NULL, p_arg61 IN VARCHAR2 DEFAULT NULL,
150 p_arg62 IN VARCHAR2 DEFAULT NULL, p_arg63 IN VARCHAR2 DEFAULT NULL,
151 p_arg64 IN VARCHAR2 DEFAULT NULL, p_arg65 IN VARCHAR2 DEFAULT NULL,
152 p_arg66 IN VARCHAR2 DEFAULT NULL, p_arg67 IN VARCHAR2 DEFAULT NULL,
153 p_arg68 IN VARCHAR2 DEFAULT NULL, p_arg69 IN VARCHAR2 DEFAULT NULL,
154 p_arg70 IN VARCHAR2 DEFAULT NULL, p_arg71 IN VARCHAR2 DEFAULT NULL,
155 p_arg72 IN VARCHAR2 DEFAULT NULL, p_arg73 IN VARCHAR2 DEFAULT NULL,
156 p_arg74 IN VARCHAR2 DEFAULT NULL, p_arg75 IN VARCHAR2 DEFAULT NULL,
157 p_arg76 IN VARCHAR2 DEFAULT NULL, p_arg77 IN VARCHAR2 DEFAULT NULL,
158 p_arg78 IN VARCHAR2 DEFAULT NULL, p_arg79 IN VARCHAR2 DEFAULT NULL,
159 p_arg80 IN VARCHAR2 DEFAULT NULL, p_arg81 IN VARCHAR2 DEFAULT NULL,
160 p_arg82 IN VARCHAR2 DEFAULT NULL, p_arg83 IN VARCHAR2 DEFAULT NULL,
161 p_arg84 IN VARCHAR2 DEFAULT NULL, p_arg85 IN VARCHAR2 DEFAULT NULL,
162 p_arg86 IN VARCHAR2 DEFAULT NULL, p_arg87 IN VARCHAR2 DEFAULT NULL,
163 p_arg88 IN VARCHAR2 DEFAULT NULL, p_arg89 IN VARCHAR2 DEFAULT NULL,
164 p_arg90 IN VARCHAR2 DEFAULT NULL, p_arg91 IN VARCHAR2 DEFAULT NULL,
165 p_arg92 IN VARCHAR2 DEFAULT NULL, p_arg93 IN VARCHAR2 DEFAULT NULL,
166 p_arg94 IN VARCHAR2 DEFAULT NULL, p_arg95 IN VARCHAR2 DEFAULT NULL,
167 p_arg96 IN VARCHAR2 DEFAULT NULL, p_arg97 IN VARCHAR2 DEFAULT NULL,
168 p_arg98 IN VARCHAR2 DEFAULT NULL, p_arg99 IN VARCHAR2 DEFAULT NULL,
169 p_arg100 IN VARCHAR2 DEFAULT NULL
170 )
171 IS
172
173 l_return_status VARCHAR2 (100);
174 l_return_message VARCHAR2 (3000);
175
176 l_msg_count NUMBER;
177 l_msg_data VARCHAR2(4000);
178
179 /* hook related params */
180 l_pkg_name VARCHAR2(200);
181 l_callout_name VARCHAR2(500);
182 l_stmt VARCHAR2(1000);
183 l_ret_status VARCHAR2(300);
184 l_error_code VARCHAR2(3000);
185 l_api_version CONSTANT NUMBER := 1.0;
186
187 l_payreq_id IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE;
188 l_pmtdoc_id IBY_PAY_SERVICE_REQUESTS.payment_document_id%TYPE; --Bug 11059189
189 l_int_bank_acct_id IBY_PAY_SERVICE_REQUESTS.internal_bank_account_id%TYPE; --Bug 16041562
190 l_module_name VARCHAR2(200) := G_PKG_NAME || '.build_payment_instructions';
191 l_not_provided CONSTANT VARCHAR2(100) := '<not provided>';
192
193 l_pmtInstrTab IBY_PAYINSTR_PUB.pmtInstrTabType;
194
195 /*
196 * Implementing the callout is optional for the calling app.
197 * If the calling app does not implement the hook, then
198 * the call to the hook will result in ORA-06576 error.
199 *
200 * There is no exception name associated with this code, so
201 * we create one called 'PROCEDURE_NOT_IMPLEMENTED'. If this
202 * exception occurs, it is not fatal: we log the error and
203 * proceed.
204 *
205 * If, on the other hand, the calling app implements the
206 * callout, but the callout throws an exception, it is fatal
207 * and we must abort the program (this will be caught
208 * in WHEN OTHERS block).
209 */
210 PROCEDURE_NOT_IMPLEMENTED EXCEPTION;
211 PRAGMA EXCEPTION_INIT(PROCEDURE_NOT_IMPLEMENTED, -6576);
212
213 BEGIN
214
215 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
216 print_debuginfo(l_module_name, 'ENTER');
217
218 END IF;
219 SAVEPOINT BEGIN_PMT_INST_PROCESSING;
220
221 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
222 print_debuginfo(l_module_name, '+--------------------------------------+');
223 print_debuginfo(l_module_name, '|STEP 1: Create Payment Instructions |');
224 print_debuginfo(l_module_name, '+--------------------------------------+');
225
226 END IF;
227 /*
228 * F8 - Payment Instruction Creation Flow (Part I)
229 *
230 * Find all payments that are in 'created' status
231 * and create payment instructions from such payments.
232 */
233
234 BEGIN
235
236 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
237 print_debuginfo(l_module_name, 'Provided selection criteria - ');
238
239 print_debuginfo(l_module_name, 'p_processing_type: '
240 || NVL(p_processing_type, l_not_provided));
241 print_debuginfo(l_module_name, 'p_payment_profile_id: '
242 || NVL(p_payment_profile_id, l_not_provided));
243 print_debuginfo(l_module_name, 'p_pmt_document_id: '
244 || NVL(p_pmt_document_id, l_not_provided));
245 print_debuginfo(l_module_name, 'p_internal_bank_account_id: '
246 || NVL(p_internal_bank_account_id, l_not_provided));
247 print_debuginfo(l_module_name, 'p_print_now_flag: '
248 || NVL(p_print_now_flag, l_not_provided));
249 print_debuginfo(l_module_name, 'p_printer_name: '
250 || NVL(p_printer_name, l_not_provided));
251 print_debuginfo(l_module_name, 'p_payment_currency: '
252 || NVL(p_payment_currency, l_not_provided));
253 print_debuginfo(l_module_name, 'p_transmit_now_flag: '
254 || NVL(p_transmit_now_flag, l_not_provided));
255 print_debuginfo(l_module_name, 'p_admin_assigned_ref: '
256 || NVL(p_admin_assigned_ref, l_not_provided));
257 print_debuginfo(l_module_name, 'p_comments: '
258 || NVL(p_comments, l_not_provided));
259 print_debuginfo(l_module_name, 'p_calling_app_id: '
260 || NVL(p_calling_app_id, l_not_provided));
261 print_debuginfo(l_module_name, 'p_calling_app_payreq_cd: '
262 || NVL(p_calling_app_payreq_cd, l_not_provided));
263 print_debuginfo(l_module_name, 'p_le_id: '
264 || NVL(p_le_id, l_not_provided));
265 print_debuginfo(l_module_name, 'p_org_id: '
266 || NVL(p_org_id, l_not_provided));
267 print_debuginfo(l_module_name, 'p_org_type: '
268 || NVL(p_org_type, l_not_provided));
269 print_debuginfo(l_module_name, 'p_payment_from_date: '
270 || NVL(p_payment_from_date, l_not_provided));
271 print_debuginfo(l_module_name, 'p_payment_to_date: '
272 || NVL(p_payment_to_date, l_not_provided));
273
274 END IF;
275 IF (p_calling_app_payreq_cd IS NOT NULL) THEN
276
277 IF (p_calling_app_id IS NULL) THEN
278
279 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
280 print_debuginfo(l_module_name, 'Invalid selection '
281 || 'criteria provided; calling app payment '
282 || 'service request id has been provided but '
283 || 'calling app id has not been provided.'
284 || 'For selection by payment service request, '
285 || 'the calling app id needs to be provided.'
286 );
287
288 print_debuginfo(l_module_name, 'Payment instruction '
289 || 'creation will not proceed.'
290 );
291
292 END IF;
293 x_errbuf := 'PAY INSTRUCTION PROGRAM ERROR - INVALID '
294 || 'SELECTION CRITERIA PROVIDED';
295 x_retcode := '-1';
296
297 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
298 print_debuginfo(l_module_name, 'EXIT');
299
300 END IF;
301 RETURN;
302
303 END IF;
304
305 l_payreq_id := get_payreq_id(p_calling_app_id,
306 p_calling_app_payreq_cd);
307 /*Bug 11059189 */
308 IF (p_processing_type = 'ELECTRONIC') THEN
309 /*Bug 16041562 */
310 print_debuginfo(l_module_name, 'Fetching Internal bank account ID');
311 l_int_bank_acct_id := get_int_bank_acct_id(l_payreq_id);
312 print_debuginfo(l_module_name, 'Internal bank account ID Fetched:' || l_int_bank_acct_id);
313
314 print_debuginfo(l_module_name, 'Fetching Payment Document ID');
315 l_pmtdoc_id := get_paydoc_id(l_payreq_id);
316 print_debuginfo(l_module_name, 'Payment Document ID Fetched:' || l_pmtdoc_id);
317 END IF;
318
319 END IF;
320
321 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
322 print_debuginfo(l_module_name, 'Invoking createPaymentInstructions()');
323
324 END IF;
325 /* Bug 11059189 */
326 IF (p_pmt_document_id IS NOT NULL) THEN
327 l_pmtdoc_id := p_pmt_document_id;
328 END IF;
329 /*Bug 16041562 */
330 IF (p_internal_bank_account_id IS NOT NULL) THEN
331 l_int_bank_acct_id := p_internal_bank_account_id;
332 END IF;
333
334 /*
335 * Invoke payment instruction creation logic.
336 */
337 IBY_PAYINSTR_PUB.createPaymentInstructions(
338 p_processing_type,
339 l_pmtdoc_id,
340 p_printer_name,
341 p_print_now_flag,
342 p_transmit_now_flag,
343 p_admin_assigned_ref,
344 p_comments,
345 p_payment_profile_id,
346 TO_NUMBER(p_calling_app_id),
347 p_calling_app_payreq_cd,
348 l_payreq_id,
349 TO_NUMBER(l_int_bank_acct_id),
350 p_payment_currency,
351 TO_NUMBER(p_le_id),
352 TO_NUMBER(p_org_id),
353 p_org_type,
354
355 /*
356 * Fix for bug 5129717:
357 *
358 * Provide the appropriate date pattern to
359 * convert date filter criteria into DATE
360 * datetypes.
361 */
362 TO_DATE(p_payment_from_date, 'YYYY/MM/DD HH24:MI:SS'),
363 TO_DATE(p_payment_to_date, 'YYYY/MM/DD HH24:MI:SS'),
364 'N', -- single payments flow flag
365 l_pmtInstrTab,
366 l_return_status,
367 l_msg_count,
368 l_msg_data
369 );
370
371 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
372 print_debuginfo(l_module_name, 'Status after payment '
373 || 'instruction creation: '
374 || l_return_status);
375
376 END IF;
377 /*
378 * If payment instruction creation was completed, then commit.
379 */
380 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
381 print_debuginfo(l_module_name, 'Payment instruction '
382 || 'creation completed successfully.');
383
384 print_debuginfo(l_module_name, 'Performing commit ..');
385 END IF;
386 COMMIT;
387
388 EXCEPTION
389
390 WHEN OTHERS THEN
391 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
392 print_debuginfo(l_module_name, 'Exception occured when '
393 || 'creating payment instructions. Payment instruction '
394 || 'creation will be aborted.'
395 );
396 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
397 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
398
399 END IF;
400 ROLLBACK TO SAVEPOINT BEGIN_PMT_INST_PROCESSING;
401
402 x_errbuf := 'PROGRAM ERROR - CANNOT CREATE PAYMENT INSTRUCTIONS';
403 x_retcode := '-1';
404 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
405 print_debuginfo(l_module_name, 'EXIT');
406
407 END IF;
408 RETURN;
409
410 END;
411
412
413 /*
414 * If we reached here, it means that the payment instruction
415 * creation program finished successfully. Invoke
416 * check numbering if we are building payment instructions
417 * of processing type 'printed'.
418 */
419 BEGIN
420
421 IF (p_processing_type = 'PRINTED' OR (p_processing_type = 'ELECTRONIC' and l_pmtdoc_id IS NOT NULL)) THEN
422
423 IF (l_pmtInstrTab.COUNT > 0) THEN
424
425 /*
426 * Perform check numbering (paper document numbering)
427 * for the first successful payment instruction. All
428 * other payment instructions are to be moved to a
429 * deferred status for later numbering.
430 *
431 * This is because the payment document (check stock)
432 * is locked once it is used to number a payment
433 * instruction. This lock will only be released after
434 * the user has confirmed that the checks printed
435 * correctly. So, there is no point in proceeding
436 * with other payment instructions until the numbered
437 * instruction has been confirmed by the user.
438 */
439 FOR i IN l_pmtInstrTab.FIRST .. l_pmtInstrTab.LAST LOOP
440
441 /*
442 * Number only successful payment
443 * instructions.
444 */
445 IF (l_pmtInstrTab(i).payment_instruction_status =
446 INS_STATUS_CREATED) THEN
447
448 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
449 print_debuginfo(l_module_name, 'Invoking '
450 || 'numbering for payment '
451 || 'instruction '
452 || l_pmtInstrTab(i).payment_instruction_id
453 || ' with instruction status: '
454 || l_pmtInstrTab(i).payment_instruction_status
455 );
456
457 END IF;
458 /*
459 * Invoke check numbering for this payment
460 * instruction.
461 */
462 IBY_CHECKNUMBER_PUB.performCheckNumbering(
463 l_pmtInstrTab(i).payment_instruction_id,
464 l_pmtdoc_id,
465 NULL,
466 l_return_status,
467 l_return_message,
468 l_msg_count,
469 l_msg_data
470 );
471
472 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
473 print_debuginfo(l_module_name, 'After numbering, '
474 || 'return status: '
475 || l_return_status
476 || ', and return message: '
477 || l_return_message
478 );
479
480 END IF;
481 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
482
483 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
484 print_debuginfo(l_module_name, 'Check '
485 || 'numbering module returned failure '
486 || 'response. Aborting.. ',
487 FND_LOG.LEVEL_UNEXPECTED
488 );
489
490 END IF;
491 APP_EXCEPTION.RAISE_EXCEPTION;
492
493 END IF;
494
495 /*
496 * The first successful payment instruction has now
497 * been numbered.
498 *
499 * Invoke the set of post-payment instruction
500 * creation programs that are responsible for
501 * extracting, formatting and printing the
502 * payment instruction data.
503 */
504 BEGIN
505
506 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
507 print_debuginfo(l_module_name, 'Invoking '
508 || 'extract and format programs for '
509 || 'instruction '
510 || l_pmtInstrTab(i).payment_instruction_id
511 );
512
513 END IF;
514 IBY_FD_POST_PICP_PROGS_PVT.
515 Run_Post_PI_Programs(
516 l_pmtInstrTab(i).payment_instruction_id,
517 'N'
518 );
519
520 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
521 print_debuginfo(l_module_name, 'Extract '
522 || 'and format operation completed.'
523 );
524
525 END IF;
526 EXCEPTION
527 WHEN OTHERS THEN
528
529 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
530 print_debuginfo(l_module_name, 'Extract and '
531 || 'format operation generated '
532 || 'exception for payment instruction '
533 || l_pmtInstrTab(i).payment_instruction_id,
534 FND_LOG.LEVEL_UNEXPECTED
535 );
536
537 print_debuginfo(l_module_name, 'SQL code: '
538 || SQLCODE, FND_LOG.LEVEL_UNEXPECTED);
539 print_debuginfo(l_module_name, 'SQL err msg: '
540 || SQLERRM, FND_LOG.LEVEL_UNEXPECTED);
541
542 END IF;
543 /*
544 * Propogate exception.
545 */
546 RAISE;
547
548 END;
549
550 /*
551 * Move all other successful payment instructions
552 * to deferred status (for later numbering).
553 */
554 moveInstrToDeferredStatus(l_pmtInstrTab,
555 l_pmtInstrTab(i).payment_instruction_id);
556
557 /*
558 * Once we have numbered and formatted the first
559 * successful payment instruction, exit.
560 */
561 EXIT;
562
563 ELSE
564
565 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
566 print_debuginfo(l_module_name, 'Not invoking '
567 || 'paper document numbering for payment '
568 || 'instruction '
569 || l_pmtInstrTab(i).payment_instruction_id
570 || ', as it is in status '
571 || l_pmtInstrTab(i).payment_instruction_status
572 );
573
574 END IF;
575 END IF;
576
577 END LOOP;
578
579 ELSE
580
581 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
582 print_debuginfo(l_module_name, 'Instruction count is '
583 || 'zero. Skipping paper document numbering ..'
584 );
588
585
586 END IF;
587 END IF; -- if instruction count > 0
589 ELSE
590
591 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
592 print_debuginfo(l_module_name, 'Processing type is '
593 || p_processing_type
594 || '. Numbering of paper documents skipped ..'
595 );
596
597 END IF;
598 /*
599 * If we reached here, it means the processing type
600 * is electronic.
601 *
602 * For electronic payment instructions, check numbering
603 * not required. Instead, directly call the extracting
604 * and formatting programs.
605 */
606
607 IF (l_pmtInstrTab.COUNT > 0) THEN
608
609 /*
610 * Loop through all the payment instructions one-by-one.
611 *
612 * Invoke extract and format for each successful payment
613 * instruction.
614 */
615 FOR i IN l_pmtInstrTab.FIRST .. l_pmtInstrTab.LAST LOOP
616
617 /*
618 * Call post-PICP programs only for successful
619 * payment instructions.
620 */
621 IF (l_pmtInstrTab(i).payment_instruction_status =
622 INS_STATUS_CREATED) THEN
623
624
625 /*
626 * WITHHOLDING CERTIFICATES HOOK:
627 *
628 * Fix for bug 6706749:
629 *
630 * We need to invoke withholding certificates hook for
631 * electronic payments. This is already being done
632 * from printed payments since base R12. Invoking
633 * withholding certificates for electronic payments
637
634 * is new functionality that is addressed in this
635 * fix.
636 */
638 l_pkg_name := 'AP_AWT_CALLOUT_PKG';
639 l_callout_name := l_pkg_name
640 || '.'
641 || 'zx_witholdingCertificatesHook';
642
643 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
644 print_debuginfo(l_module_name,
645 'Attempting to call hook: '
646 || l_callout_name, FND_LOG.LEVEL_UNEXPECTED);
647
648 END IF;
649 l_stmt := 'CALL '|| l_callout_name
650 || '(:1, :2, :3, :4, :5, :6, :7, :8)';
651
652 BEGIN
653
654 EXECUTE IMMEDIATE
655 (l_stmt)
656 USING
657 IN l_pmtInstrTab(i).payment_instruction_id,
658 IN 'GENERATE',
659 IN l_api_version,
660 IN FND_API.G_FALSE,
661 IN FND_API.G_FALSE,
662 OUT l_ret_status,
663 OUT l_msg_count,
664 OUT l_msg_data
665 ;
666
667 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
668 print_debuginfo(l_module_name,
669 'Finished invoking hook: '
670 || l_callout_name
671 || ', return status: '
672 || l_ret_status, FND_LOG.LEVEL_UNEXPECTED);
673
674 END IF;
675 /*
676 * If the called procedure did not return success,
677 * raise an exception.
678 */
679 IF (l_ret_status IS NULL OR
680 l_ret_status <> FND_API.G_RET_STS_SUCCESS)
681 THEN
682
683 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
684 print_debuginfo(l_module_name,
685 'Fatal: External app callout '''
686 || l_callout_name
687 || ''', returned failure status - '
688 || l_ret_status
689 || '. Raising exception.',
690 FND_LOG.LEVEL_UNEXPECTED);
691
692 END IF;
693 APP_EXCEPTION.RAISE_EXCEPTION;
694
695 END IF;
696
697 EXCEPTION
698
699 WHEN PROCEDURE_NOT_IMPLEMENTED THEN
700 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
701 print_debuginfo(l_module_name,
702 'Callout "'
703 || l_callout_name
704 || '" not implemented by application - AP',
705 FND_LOG.LEVEL_UNEXPECTED);
706
707 print_debuginfo(l_module_name,
708 'Skipping hook call.');
709
710 END IF;
711 WHEN OTHERS THEN
712
713 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
714 print_debuginfo(l_module_name,
715 'Fatal: External app '
716 || 'callout '''
717 || l_callout_name
718 || ''', generated exception.',
719 FND_LOG.LEVEL_UNEXPECTED
720 );
721
722 END IF;
723 l_error_code := 'IBY_INS_AWT_CERT_HOOK_FAILED';
724 FND_MESSAGE.set_name('IBY', l_error_code);
725
726 FND_MESSAGE.SET_TOKEN('CALLOUT',
727 l_callout_name,
728 FALSE);
729 /*
730 * Set the error message on the concurrent
731 * program output file (to warn user that
732 * the hook failed).
733 */
734 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
735 FND_MESSAGE.GET);
736
737 /*
738 * Set the message on the error stack
739 * to display in UI (in case of direct
740 * API call from UI).
741 */
742 FND_MSG_PUB.ADD;
743
744 /*
745 * Propogate exception to caller.
746 */
747 RAISE;
748
752 BEGIN
749 END;
750
751
753
754 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
755 print_debuginfo(l_module_name, 'Invoking '
756 || 'extract and format for payment '
757 || 'instruction '
758 || l_pmtInstrTab(i).payment_instruction_id
759 || ' with instruction status: '
760 || l_pmtInstrTab(i).payment_instruction_status
761 );
762
763 END IF;
764 IBY_FD_POST_PICP_PROGS_PVT.
765 Run_Post_PI_Programs(
766 l_pmtInstrTab(i).payment_instruction_id,
767 'N'
768 );
769
770 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
771 print_debuginfo(l_module_name, 'Extract '
772 || 'and format operation completed.'
773 );
774
775 END IF;
776 EXCEPTION
777 WHEN OTHERS THEN
778
779 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
780 print_debuginfo(l_module_name, 'Extract and '
781 || 'format operation generated '
782 || 'exception for payment instruction '
783 || l_pmtInstrTab(i).payment_instruction_id,
784 FND_LOG.LEVEL_UNEXPECTED
785 );
786
787 print_debuginfo(l_module_name, 'SQL code: '
788 || SQLCODE, FND_LOG.LEVEL_UNEXPECTED);
789 print_debuginfo(l_module_name, 'SQL err msg: '
790 || SQLERRM, FND_LOG.LEVEL_UNEXPECTED);
791
792 END IF;
793 /*
794 * Propogate exception.
795 */
796 RAISE;
797
798 END;
799
800 ELSE
801
802 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
803 print_debuginfo(l_module_name, 'Not invoking '
804 || 'extract and format for payment '
805 || 'instruction '
806 || l_pmtInstrTab(i).payment_instruction_id
807 || ' because it is in status: '
808 || l_pmtInstrTab(i).payment_instruction_status
809 );
810
811 END IF;
812 END IF;
813
814 END LOOP;
815
816 END IF; -- if count of instructions > 0
817
818 END IF; -- processing type = 'printed'
819
820 /*
821 * In case check numbering completes, perform a commit.
822 */
823 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
824 print_debuginfo(l_module_name, 'Performing commit ..');
825 END IF;
826 COMMIT;
827
828 EXCEPTION
829
830 WHEN OTHERS THEN
831
832 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
833 print_debuginfo(l_module_name, 'Exception occured when performing '
834 || 'check numbering / invoking extract and format. '
835 || 'Processing aborted ..'
836 );
837 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
838 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
839
840 END IF;
841 x_errbuf := 'PROGRAM ERROR - CANNOT PERFORM '
842 || 'CHECK NUMBERING';
843 x_retcode := '-1';
844 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
845 print_debuginfo(l_module_name, 'EXIT');
846
847 END IF;
848 RETURN;
849
850 END;
851
852 /*
853 * If we reached here, it means that the payment instruction
854 * creation program finished successfully. Set the response
855 * message to 'success'.
856 */
857 x_errbuf := 'PAYMENT INSTRUCTION CREATION PROGRAM COMPLETED SUCCESSFULLY';
858 x_retcode := '0';
859
860 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
861 print_debuginfo(l_module_name, 'EXIT');
862
863 END IF;
864 END build_pmt_instructions;
865
866 /*--------------------------------------------------------------------
867 | NAME:
868 | rebuild_pmt_instruction
869 |
870 |
871 | PURPOSE:
872 |
873 |
874 | PARAMETERS:
875 | IN
876 |
877 |
878 | OUT
879 |
880 |
881 | RETURNS:
882 |
883 | NOTES:
884 |
885 *---------------------------------------------------------------------*/
886 PROCEDURE rebuild_pmt_instruction(
887 x_errbuf OUT NOCOPY VARCHAR2,
888 x_retcode OUT NOCOPY VARCHAR2,
889 p_pmt_instr_id IN VARCHAR2,
890 p_sys_key IN VARCHAR2 DEFAULT NULL, p_arg5 IN VARCHAR2 DEFAULT NULL,
891 p_arg6 IN VARCHAR2 DEFAULT NULL, p_arg7 IN VARCHAR2 DEFAULT NULL,
895 p_arg14 IN VARCHAR2 DEFAULT NULL, p_arg15 IN VARCHAR2 DEFAULT NULL,
892 p_arg8 IN VARCHAR2 DEFAULT NULL, p_arg9 IN VARCHAR2 DEFAULT NULL,
893 p_arg10 IN VARCHAR2 DEFAULT NULL, p_arg11 IN VARCHAR2 DEFAULT NULL,
894 p_arg12 IN VARCHAR2 DEFAULT NULL, p_arg13 IN VARCHAR2 DEFAULT NULL,
896 p_arg16 IN VARCHAR2 DEFAULT NULL, p_arg17 IN VARCHAR2 DEFAULT NULL,
897 p_arg18 IN VARCHAR2 DEFAULT NULL, p_arg19 IN VARCHAR2 DEFAULT NULL,
898 p_arg20 IN VARCHAR2 DEFAULT NULL, p_arg21 IN VARCHAR2 DEFAULT NULL,
899 p_arg22 IN VARCHAR2 DEFAULT NULL, p_arg23 IN VARCHAR2 DEFAULT NULL,
900 p_arg24 IN VARCHAR2 DEFAULT NULL, p_arg25 IN VARCHAR2 DEFAULT NULL,
901 p_arg26 IN VARCHAR2 DEFAULT NULL, p_arg27 IN VARCHAR2 DEFAULT NULL,
902 p_arg28 IN VARCHAR2 DEFAULT NULL, p_arg29 IN VARCHAR2 DEFAULT NULL,
903 p_arg30 IN VARCHAR2 DEFAULT NULL, p_arg31 IN VARCHAR2 DEFAULT NULL,
904 p_arg32 IN VARCHAR2 DEFAULT NULL, p_arg33 IN VARCHAR2 DEFAULT NULL,
905 p_arg34 IN VARCHAR2 DEFAULT NULL, p_arg35 IN VARCHAR2 DEFAULT NULL,
906 p_arg36 IN VARCHAR2 DEFAULT NULL, p_arg37 IN VARCHAR2 DEFAULT NULL,
907 p_arg38 IN VARCHAR2 DEFAULT NULL, p_arg39 IN VARCHAR2 DEFAULT NULL,
908 p_arg40 IN VARCHAR2 DEFAULT NULL, p_arg41 IN VARCHAR2 DEFAULT NULL,
909 p_arg42 IN VARCHAR2 DEFAULT NULL, p_arg43 IN VARCHAR2 DEFAULT NULL,
910 p_arg44 IN VARCHAR2 DEFAULT NULL, p_arg45 IN VARCHAR2 DEFAULT NULL,
911 p_arg46 IN VARCHAR2 DEFAULT NULL, p_arg47 IN VARCHAR2 DEFAULT NULL,
912 p_arg48 IN VARCHAR2 DEFAULT NULL, p_arg49 IN VARCHAR2 DEFAULT NULL,
913 p_arg50 IN VARCHAR2 DEFAULT NULL, p_arg51 IN VARCHAR2 DEFAULT NULL,
914 p_arg52 IN VARCHAR2 DEFAULT NULL, p_arg53 IN VARCHAR2 DEFAULT NULL,
915 p_arg54 IN VARCHAR2 DEFAULT NULL, p_arg55 IN VARCHAR2 DEFAULT NULL,
916 p_arg56 IN VARCHAR2 DEFAULT NULL, p_arg57 IN VARCHAR2 DEFAULT NULL,
917 p_arg58 IN VARCHAR2 DEFAULT NULL, p_arg59 IN VARCHAR2 DEFAULT NULL,
918 p_arg60 IN VARCHAR2 DEFAULT NULL, p_arg61 IN VARCHAR2 DEFAULT NULL,
919 p_arg62 IN VARCHAR2 DEFAULT NULL, p_arg63 IN VARCHAR2 DEFAULT NULL,
920 p_arg64 IN VARCHAR2 DEFAULT NULL, p_arg65 IN VARCHAR2 DEFAULT NULL,
921 p_arg66 IN VARCHAR2 DEFAULT NULL, p_arg67 IN VARCHAR2 DEFAULT NULL,
922 p_arg68 IN VARCHAR2 DEFAULT NULL, p_arg69 IN VARCHAR2 DEFAULT NULL,
923 p_arg70 IN VARCHAR2 DEFAULT NULL, p_arg71 IN VARCHAR2 DEFAULT NULL,
924 p_arg72 IN VARCHAR2 DEFAULT NULL, p_arg73 IN VARCHAR2 DEFAULT NULL,
925 p_arg74 IN VARCHAR2 DEFAULT NULL, p_arg75 IN VARCHAR2 DEFAULT NULL,
926 p_arg76 IN VARCHAR2 DEFAULT NULL, p_arg77 IN VARCHAR2 DEFAULT NULL,
927 p_arg78 IN VARCHAR2 DEFAULT NULL, p_arg79 IN VARCHAR2 DEFAULT NULL,
928 p_arg80 IN VARCHAR2 DEFAULT NULL, p_arg81 IN VARCHAR2 DEFAULT NULL,
929 p_arg82 IN VARCHAR2 DEFAULT NULL, p_arg83 IN VARCHAR2 DEFAULT NULL,
930 p_arg84 IN VARCHAR2 DEFAULT NULL, p_arg85 IN VARCHAR2 DEFAULT NULL,
931 p_arg86 IN VARCHAR2 DEFAULT NULL, p_arg87 IN VARCHAR2 DEFAULT NULL,
932 p_arg88 IN VARCHAR2 DEFAULT NULL, p_arg89 IN VARCHAR2 DEFAULT NULL,
933 p_arg90 IN VARCHAR2 DEFAULT NULL, p_arg91 IN VARCHAR2 DEFAULT NULL,
934 p_arg92 IN VARCHAR2 DEFAULT NULL, p_arg93 IN VARCHAR2 DEFAULT NULL,
935 p_arg94 IN VARCHAR2 DEFAULT NULL, p_arg95 IN VARCHAR2 DEFAULT NULL,
936 p_arg96 IN VARCHAR2 DEFAULT NULL, p_arg97 IN VARCHAR2 DEFAULT NULL,
937 p_arg98 IN VARCHAR2 DEFAULT NULL, p_arg99 IN VARCHAR2 DEFAULT NULL,
938 p_arg100 IN VARCHAR2 DEFAULT NULL
939 )
940 IS
941 l_return_status NUMBER;
942 l_ret_status VARCHAR2 (100);
943 l_return_message VARCHAR2 (3000);
944
945 l_instr_status IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_status%TYPE;
946 l_instr_rec IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE;
947 l_instr_tab IBY_PAYINSTR_PUB.pmtInstrTabType;
948
949 l_doc_error_tab IBY_VALIDATIONSETS_PUB.docErrorTabType;
950 l_doc_token_tab IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType;
951
952 l_profile_attribs IBY_DISBURSE_SUBMIT_PUB_PKG.profileProcessAttribs;
953
954 l_msg_count NUMBER;
955 l_msg_data VARCHAR2(4000);
956
957 l_module_name VARCHAR2(200) := G_PKG_NAME || '.rebuild_pmt_instruction';
958
959 BEGIN
960
961 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
962 print_debuginfo(l_module_name, 'ENTER');
963 END IF;
964
965 Validate_and_Set_Syskey(HEXTORAW(p_sys_key));
966
967 SAVEPOINT BEGIN_REBUILD;
968
969 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
970
971 print_debuginfo(l_module_name, 'Provided payment instruction id: '
972 || p_pmt_instr_id);
973
974 END IF;
975 BEGIN
976
977 /*
978 * Retrieve the attributes of the provided payment instruction.
979 */
980 l_instr_rec := get_instruction_attributes(p_pmt_instr_id);
981
982 IF (l_instr_rec.payment_instruction_status IS NULL) THEN
983
984 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
985 print_debuginfo(l_module_name, 'Attributes of payment '
986 || 'instruction id: '
987 || p_pmt_instr_id
988 || ' could not be retrieved. Aborting rebuild ..',
989 FND_LOG.LEVEL_UNEXPECTED
990 );
991
992 END IF;
993 APP_EXCEPTION.RAISE_EXCEPTION;
994
995 END IF;
996
997 /*
998 * Check the status of the provided payment instruction.
999 */
1000 l_instr_status := l_instr_rec.payment_instruction_status;
1001
1002 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1003 print_debuginfo(l_module_name, 'Payment instruction status is: '
1004 || l_instr_status);
1005
1006 END IF;
1007 IF (l_instr_status <> INSTR_STATUS_RETRY_CREAT AND
1008 l_instr_status <> INSTR_STATUS_CREAT_ERROR) THEN
1009
1010 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1011 print_debuginfo(l_module_name, 'Payment instruction '
1012 || 'should be in '
1013 || INSTR_STATUS_RETRY_CREAT
1014 || ' or '
1015 || INSTR_STATUS_CREAT_ERROR
1016 || ' status for rebuilding. '
1017 || 'Skipping payment instruction rebuild ..'
1018 );
1019
1020 END IF;
1021 ELSE
1022
1023 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1024 print_debuginfo(l_module_name,
1025 'Invoking recreatePaymentInstruction()');
1026
1027 END IF;
1028 IBY_PAYINSTR_PUB.recreatePaymentInstruction(
1029 l_instr_rec,
1030 l_doc_error_tab,
1031 l_doc_token_tab,
1032 l_return_status);
1033
1034 /*
1035 * The provided payment instruction has been revalidated
1036 * and (possibly) document sequencing and payment referencing
1037 * has occured for the payments of this instruction.
1038 * Therefore, the payment instruction status needs to be updated.
1039 */
1040 l_instr_tab(l_instr_tab.COUNT + 1) := l_instr_rec;
1041 IBY_PAYINSTR_PUB.updatePaymentInstructions(l_instr_tab);
1042
1043 /*
1044 * If the payment instructions wwas again failed, the
1045 * IBY_TRANSACTION_ERRORS table must be populated with the
1046 * corresponding error messages.
1047 */
1048 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',
1049 l_doc_error_tab, l_doc_token_tab);
1050
1051 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1052 print_debuginfo(l_module_name, 'Status after payment '
1053 || 'instruction rebuild: '
1054 || l_return_status);
1055
1056 END IF;
1057 END IF;
1058
1059 /*
1060 * After rebuilding completes, perform a commit.
1061 */
1062 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1063 print_debuginfo(l_module_name, 'Performing commit ..');
1064 END IF;
1065 COMMIT;
1066
1067 EXCEPTION
1068 WHEN OTHERS THEN
1069
1070 /*
1071 * In the case of an exception rollback all the
1072 * database changes and return failure response.
1073 */
1074 ROLLBACK TO SAVEPOINT BEGIN_REBUILD;
1075
1076 x_errbuf := 'PAY INSTRUCTION REBUILD ERROR';
1077 x_retcode := '-1';
1078
1079 /*
1080 * The payment instruction was possibly locked by the UI.
1081 * Unlock it if possible.
1082 */
1083 IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity(
1084 p_pmt_instr_id,
1085 'PAYMENT_INSTRUCTION',
1086 l_ret_status
1087 );
1088
1089 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1090 print_debuginfo(l_module_name, 'EXIT');
1091
1092 END IF;
1093 RETURN;
1094
1095 END;
1096
1097
1098 /*
1099 * Get the default processing related attributes
1100 * from the payment process profile on the given
1101 * payment instruction. These attributes like the
1102 * processing type etc. will be used in check
1103 * numbering and formatting calls.
1104 */
1105 IBY_DISBURSE_SUBMIT_PUB_PKG.get_profile_process_attribs(
1106 l_instr_rec.payment_profile_id,
1107 l_profile_attribs
1108 );
1109
1110 /*
1111 * If we reached here, it means that the payment instruction
1112 * re-creation program finished successfully. Invoke
1113 * check numbering if we are building payment instructions
1114 * of processing type 'printed'.
1115 */
1116 BEGIN
1117
1118 IF (l_profile_attribs.processing_type = 'PRINTED') THEN
1119
1120 IF (l_instr_tab.COUNT > 0) THEN
1121
1122 /*
1123 * Perform check numbering (paper document numbering)
1124 * for the first successful payment instruction. All
1125 * other payment instructions are to be moved to a
1126 * deferred status for later numbering.
1127 *
1128 * This is because the payment document (check stock)
1129 * is locked once it is used to number a payment
1130 * instruction. This lock will only be released after
1131 * the user has confirmed that the checks printed
1132 * correctly. So, there is no point in proceeding
1133 * with other payment instructions until the numbered
1134 * instruction has been confirmed by the user.
1135 */
1136 FOR i IN l_instr_tab.FIRST .. l_instr_tab.LAST LOOP
1137
1138 /*
1139 * Number only successful payment
1140 * instructions.
1141 */
1142 IF (l_instr_tab(i).payment_instruction_status =
1143 INS_STATUS_CREATED) THEN
1144
1145 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1146 print_debuginfo(l_module_name, 'Invoking '
1147 || 'numbering for payment '
1148 || 'instruction '
1149 || l_instr_tab(i).payment_instruction_id
1150 || ' with instruction status: '
1151 || l_instr_tab(i).payment_instruction_status
1152 );
1153
1154 END IF;
1155 /*
1156 * Invoke check numbering for this payment
1157 * instruction.
1158 */
1159
1160 /*
1161 * Fix for bug 5206725:
1162 *
1163 * Do not use the payment document on the profile
1164 * for numbering. This is an optional attribute
1165 * on the profile that may or may not be present.
1166 *
1167 * Since, this is a rebuild scenario, the pmt
1168 * document id would have already been provided
1169 * to the payment instruction in the first attempt.
1170 *
1171 * This value is stored as a payment instruction
1172 * attribute. Re-use the stored value.
1173 */
1174 IBY_CHECKNUMBER_PUB.performCheckNumbering(
1175 l_instr_tab(i).payment_instruction_id,
1176 l_instr_rec.payment_document_id,
1177 NULL,
1178 l_ret_status,
1179 l_return_message,
1180 l_msg_count,
1181 l_msg_data
1182 );
1183
1184 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1185 print_debuginfo(l_module_name, 'After numbering, '
1186 || 'return status: '
1187 || l_ret_status
1188 || ', and return message: '
1189 || l_return_message
1190 );
1191
1192 END IF;
1193 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
1194
1195 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1196 print_debuginfo(l_module_name, 'Check '
1197 || 'numbering module returned failure '
1198 || 'response. Aborting.. ',
1199 FND_LOG.LEVEL_UNEXPECTED
1200 );
1201
1202 END IF;
1203 APP_EXCEPTION.RAISE_EXCEPTION;
1204
1205 END IF;
1206
1207 /*
1208 * The first successful payment instruction has now
1209 * been numbered.
1210 *
1211 * Invoke the set of post-payment instruction
1212 * creation programs that are responsible for
1213 * extracting, formatting and printing the
1214 * payment instruction data.
1215 */
1216 BEGIN
1217
1218 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1219 print_debuginfo(l_module_name, 'Invoking '
1220 || 'extract and format programs for '
1221 || 'instruction '
1222 || l_instr_tab(i).payment_instruction_id
1223 );
1224
1225 END IF;
1226 IBY_FD_POST_PICP_PROGS_PVT.
1227 Run_Post_PI_Programs(
1228 l_instr_tab(i).payment_instruction_id,
1229 'N'
1230 );
1231
1232 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1233 print_debuginfo(l_module_name, 'Extract '
1234 || 'and format operation completed.'
1235 );
1236
1237 END IF;
1238 EXCEPTION
1239 WHEN OTHERS THEN
1240
1241 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1242 print_debuginfo(l_module_name, 'Extract and '
1243 || 'format operation generated '
1244 || 'exception for payment instruction '
1245 || l_instr_tab(i).payment_instruction_id,
1246 FND_LOG.LEVEL_UNEXPECTED
1247 );
1248
1249 print_debuginfo(l_module_name, 'SQL code: '
1250 || SQLCODE, FND_LOG.LEVEL_UNEXPECTED);
1251 print_debuginfo(l_module_name, 'SQL err msg: '
1252 || SQLERRM, FND_LOG.LEVEL_UNEXPECTED);
1253
1254 END IF;
1255 /*
1256 * Propogate exception.
1257 */
1258 RAISE;
1259
1260 END;
1261
1262 /*
1263 * Move all other successful payment instructions
1264 * to deferred status (for later numbering).
1265 */
1266 moveInstrToDeferredStatus(l_instr_tab,
1267 l_instr_tab(i).payment_instruction_id);
1268
1269 /*
1270 * Once we have numbered and formatted the first
1271 * successful payment instruction, exit.
1272 */
1273 EXIT;
1274
1275 ELSE
1276
1277 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1278 print_debuginfo(l_module_name, 'Not invoking '
1279 || 'paper document numbering for payment '
1280 || 'instruction '
1281 || l_instr_tab(i).payment_instruction_id
1282 || ', as it is in status '
1283 || l_instr_tab(i).payment_instruction_status
1284 );
1285
1286 END IF;
1287 END IF;
1288
1289 END LOOP;
1290
1291 ELSE
1292
1293 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1294 print_debuginfo(l_module_name, 'Instruction count is '
1295 || 'zero. Skipping paper document numbering ..'
1296 );
1297
1298 END IF;
1299 END IF; -- if instruction count > 0
1300
1301 ELSIF (l_profile_attribs.processing_type = 'ELECTRONIC' AND
1302 l_instr_rec.payment_document_id IS NOT NULL) THEN
1303
1304 IF (l_instr_tab.COUNT > 0) THEN
1305
1306 /*
1307 * Perform check numbering (paper document numbering)
1308 * for the first successful payment instruction. All
1309 * other payment instructions are to be moved to a
1310 * deferred status for later numbering.
1311 *
1312 * This is because the payment document (check stock)
1313 * is locked once it is used to number a payment
1314 * instruction. This lock will only be released after
1315 * the user has confirmed that the checks printed
1316 * correctly. So, there is no point in proceeding
1317 * with other payment instructions until the numbered
1318 * instruction has been confirmed by the user.
1319 */
1320 FOR i IN l_instr_tab.FIRST .. l_instr_tab.LAST LOOP
1321
1322 /*
1323 * Number only successful payment
1324 * instructions.
1325 */
1326 IF (l_instr_tab(i).payment_instruction_status =
1327 INS_STATUS_CREATED) THEN
1328
1329 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1330 print_debuginfo(l_module_name, 'Invoking '
1331 || 'numbering for payment '
1332 || 'instruction '
1333 || l_instr_tab(i).payment_instruction_id
1334 || ' with instruction status: '
1335 || l_instr_tab(i).payment_instruction_status
1336 );
1337
1338 END IF;
1339 /*
1340 * Invoke check numbering for this payment
1341 * instruction.
1342 */
1343
1344 /*
1345 * Fix for bug 5206725:
1346 *
1347 * Do not use the payment document on the profile
1348 * for numbering. This is an optional attribute
1349 * on the profile that may or may not be present.
1350 *
1351 * Since, this is a rebuild scenario, the pmt
1352 * document id would have already been provided
1353 * to the payment instruction in the first attempt.
1354 *
1355 * This value is stored as a payment instruction
1356 * attribute. Re-use the stored value.
1357 */
1358 IBY_CHECKNUMBER_PUB.performCheckNumbering(
1359 l_instr_tab(i).payment_instruction_id,
1360 l_instr_rec.payment_document_id,
1361 NULL,
1362 l_ret_status,
1363 l_return_message,
1364 l_msg_count,
1365 l_msg_data
1366 );
1367
1368 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1369 print_debuginfo(l_module_name, 'After numbering, '
1370 || 'return status: '
1371 || l_ret_status
1372 || ', and return message: '
1373 || l_return_message
1374 );
1375
1376 END IF;
1377 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
1378
1379 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1380 print_debuginfo(l_module_name, 'Check '
1381 || 'numbering module returned failure '
1382 || 'response. Aborting.. ',
1383 FND_LOG.LEVEL_UNEXPECTED
1384 );
1385
1386 END IF;
1387 APP_EXCEPTION.RAISE_EXCEPTION;
1388
1389 END IF;
1390
1391 /*
1392 * The first successful payment instruction has now
1393 * been numbered.
1394 *
1395 * Invoke the set of post-payment instruction
1396 * creation programs that are responsible for
1397 * extracting, formatting and printing the
1398 * payment instruction data.
1399 */
1400 BEGIN
1401
1402 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1403 print_debuginfo(l_module_name, 'Invoking '
1404 || 'extract and format programs for '
1405 || 'instruction '
1406 || l_instr_tab(i).payment_instruction_id
1407 );
1408
1409 END IF;
1410 IBY_FD_POST_PICP_PROGS_PVT.
1411 Run_Post_PI_Programs(
1412 l_instr_tab(i).payment_instruction_id,
1413 'N'
1414 );
1415
1416 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1417 print_debuginfo(l_module_name, 'Extract '
1418 || 'and format operation completed.'
1419 );
1420
1421 END IF;
1422 EXCEPTION
1423 WHEN OTHERS THEN
1424
1425 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1426 print_debuginfo(l_module_name, 'Extract and '
1427 || 'format operation generated '
1428 || 'exception for payment instruction '
1429 || l_instr_tab(i).payment_instruction_id,
1430 FND_LOG.LEVEL_UNEXPECTED
1431 );
1432
1433 print_debuginfo(l_module_name, 'SQL code: '
1434 || SQLCODE, FND_LOG.LEVEL_UNEXPECTED);
1435 print_debuginfo(l_module_name, 'SQL err msg: '
1436 || SQLERRM, FND_LOG.LEVEL_UNEXPECTED);
1437
1438 END IF;
1439 /*
1440 * Propogate exception.
1441 */
1442 RAISE;
1443
1444 END;
1445
1446 /*
1447 * Move all other successful payment instructions
1448 * to deferred status (for later numbering).
1449 */
1450 moveInstrToDeferredStatus(l_instr_tab,
1451 l_instr_tab(i).payment_instruction_id);
1452
1453 /*
1454 * Once we have numbered and formatted the first
1455 * successful payment instruction, exit.
1456 */
1457 EXIT;
1458
1459 ELSE
1460
1461 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1462 print_debuginfo(l_module_name, 'Not invoking '
1463 || 'paper document numbering for payment '
1464 || 'instruction '
1465 || l_instr_tab(i).payment_instruction_id
1466 || ', as it is in status '
1467 || l_instr_tab(i).payment_instruction_status
1468 );
1469
1470 END IF;
1471 END IF;
1472
1473 END LOOP;
1474
1475 ELSE
1476
1477 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1478 print_debuginfo(l_module_name, 'Instruction count is '
1479 || 'zero. Skipping paper document numbering ..'
1480 );
1481
1482 END IF;
1483 END IF; -- if instruction count > 0
1484
1485 ELSE
1486
1487 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1488 print_debuginfo(l_module_name, 'Processing type is '
1489 || l_profile_attribs.processing_type
1490 || '. Numbering of paper documents skipped ..'
1491 );
1492
1493 END IF;
1494 /*
1495 * If we reached here, it means the processing type
1496 * is electronic.
1497 *
1498 * For electronic payment instructions, check numbering
1499 * not required. Instead, directly call the extracting
1500 * and formatting programs.
1501 */
1502
1503 IF (l_instr_tab.COUNT > 0) THEN
1504
1505 /*
1506 * Loop through all the payment instructions one-by-one.
1507 *
1508 * Invoke extract and format for each successful payment
1509 * instruction.
1510 */
1511 FOR i IN l_instr_tab.FIRST .. l_instr_tab.LAST LOOP
1512
1513 /*
1514 * Call post-PICP programs only for successful
1515 * payment instructions.
1516 */
1517 IF (l_instr_tab(i).payment_instruction_status =
1518 INS_STATUS_CREATED) THEN
1519
1520 BEGIN
1521
1522 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1523 print_debuginfo(l_module_name, 'Invoking '
1524 || 'extract and format for payment '
1525 || 'instruction '
1526 || l_instr_tab(i).payment_instruction_id
1527 || ' with instruction status: '
1528 || l_instr_tab(i).payment_instruction_status
1529 );
1530
1531 END IF;
1532 IBY_FD_POST_PICP_PROGS_PVT.
1533 Run_Post_PI_Programs(
1534 l_instr_tab(i).payment_instruction_id,
1535 'N'
1536 );
1537
1538 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1539 print_debuginfo(l_module_name, 'Extract '
1540 || 'and format operation completed.'
1541 );
1542
1543 END IF;
1544 EXCEPTION
1545 WHEN OTHERS THEN
1546
1547 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1548 print_debuginfo(l_module_name, 'Extract and '
1549 || 'format operation generated '
1550 || 'exception for payment instruction '
1551 || l_instr_tab(i).payment_instruction_id,
1552 FND_LOG.LEVEL_UNEXPECTED
1553 );
1554
1555 print_debuginfo(l_module_name, 'SQL code: '
1556 || SQLCODE, FND_LOG.LEVEL_UNEXPECTED);
1557 print_debuginfo(l_module_name, 'SQL err msg: '
1558 || SQLERRM, FND_LOG.LEVEL_UNEXPECTED);
1559
1560 END IF;
1561 /*
1562 * Propogate exception.
1563 */
1564 RAISE;
1565
1566 END;
1567
1568 ELSE
1569
1570 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1571 print_debuginfo(l_module_name, 'Not invoking '
1572 || 'extract and format for payment '
1573 || 'instruction '
1574 || l_instr_tab(i).payment_instruction_id
1575 || ' because it is in status: '
1576 || l_instr_tab(i).payment_instruction_status
1577 );
1578
1579 END IF;
1580 END IF;
1581
1582 END LOOP;
1583
1584 END IF; -- if count of instructions > 0
1585
1586 END IF; -- processing type = 'printed'
1587
1588 /*
1589 * In case check numbering completes, perform a commit.
1590 */
1591 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1592 print_debuginfo(l_module_name, 'Performing commit ..');
1593 END IF;
1594 COMMIT;
1595
1596 EXCEPTION
1597
1598 WHEN OTHERS THEN
1599
1600 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1601 print_debuginfo(l_module_name, 'Exception occured when performing '
1602 || 'check numbering / invoking extract and format. '
1603 || 'Processing aborted ..'
1604 );
1605 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
1606 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
1607
1608 END IF;
1609 x_errbuf := 'PROGRAM ERROR - CANNOT PERFORM '
1610 || 'CHECK NUMBERING';
1611 x_retcode := '-1';
1612
1613 /*
1614 * The payment instruction was possibly locked by the UI.
1615 * Unlock it if possible.
1616 */
1617 IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity(
1618 p_pmt_instr_id,
1619 'PAYMENT_INSTRUCTION',
1620 l_ret_status
1621 );
1622
1623 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1624 print_debuginfo(l_module_name, 'EXIT');
1625
1626 END IF;
1627 RETURN;
1628
1629 END;
1630
1631 /*
1632 * If we reached here, it means that the payment instruction
1633 * creation program finished successfully. Set the response
1634 * message to 'success'.
1635 */
1636 x_errbuf := 'PAYMENT INSTRUCTION RE-CREATION PROGRAM COMPLETED '
1637 || 'SUCCESSFULLY';
1638 x_retcode := '0';
1639
1640 /*
1641 * The payment instruction was possibly locked by the UI.
1642 * Unlock it if possible.
1643 */
1644 IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity(
1645 p_pmt_instr_id,
1646 'PAYMENT_INSTRUCTION',
1647 l_ret_status
1648 );
1649
1650 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1651 print_debuginfo(l_module_name, 'EXIT');
1652
1653
1654 END IF;
1655 END rebuild_pmt_instruction;
1656
1657 /*--------------------------------------------------------------------
1658 | NAME:
1659 | moveInstrToDeferredStatus
1660 |
1661 |
1662 | PURPOSE:
1663 | This method will set the status of a given payment instructions
1664 | to 'CREATED_READY_FOR_PRINTING' | 'CREATED_READY_FOR_FORMATTING'
1665 | status depending upon the payment profile on the instruction.
1666 |
1667 | In case the second argument is not null, the status of that
1668 | particular payment instruction will not be touched by this method.
1669 |
1670 | PARAMETERS:
1671 | IN
1672 |
1673 |
1674 | OUT
1675 |
1676 |
1677 | RETURNS:
1678 |
1679 | NOTES:
1680 |
1681 *---------------------------------------------------------------------*/
1682 PROCEDURE moveInstrToDeferredStatus(
1683 p_pmtInstrTab IN OUT NOCOPY IBY_PAYINSTR_PUB.pmtInstrTabType,
1684 p_instr_to_skip IN IBY_PAY_INSTRUCTIONS_ALL.
1685 payment_instruction_id%TYPE DEFAULT NULL
1686 )
1687 IS
1688 l_module_name VARCHAR2(200) := G_PKG_NAME || '.moveInstrToDeferredStatus';
1689
1690 l_send_to_file_flag VARCHAR2(1);
1691 l_processing_type IBY_PAYMENT_PROFILES.PROCESSING_TYPE%TYPE;
1692 l_instr_status IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_status%TYPE;
1693
1694 BEGIN
1695
1696 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1697 print_debuginfo(l_module_name, 'ENTER');
1698
1699 END IF;
1700 IF (p_pmtInstrTab.COUNT = 0) THEN
1701
1702 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1703 print_debuginfo(l_module_name, 'List of provided payment '
1704 || 'instructions is empty. Exiting .. '
1705 );
1706
1707 print_debuginfo(l_module_name, 'EXIT');
1708 END IF;
1709 RETURN;
1710
1711 END IF;
1712
1713 FOR i in p_pmtInstrTab.FIRST..p_pmtInstrTab.LAST LOOP
1714
1715 /*
1716 * Set the status of all payment instructions in the given
1717 * list to 'printing deferred' except for the specified
1718 * payment instruction which is to be skipped (because it
1719 * has been printed).
1720 */
1721
1722 IF (p_instr_to_skip IS NULL OR p_pmtInstrTab(i).payment_instruction_id
1723 <> p_instr_to_skip) THEN
1724
1725 /*
1726 * The payment instruction status is dependant upon
1727 * the 'send to file' flag. Pre-set the instruction
1728 * status appropriately.
1729 */
1730 SELECT
1731 send_to_file_flag, processing_type
1732 INTO
1733 l_send_to_file_flag, l_processing_type
1734 FROM
1735 IBY_PAYMENT_PROFILES
1736 WHERE
1737 payment_profile_id = p_pmtInstrTab(i).payment_profile_id
1738 ;
1739 IF (l_processing_type = 'PRINTED') THEN
1740
1741
1742 IF (UPPER(l_send_to_file_flag) = 'Y') THEN
1743 l_instr_status := INS_STATUS_READY_TO_FORMAT;
1744 ELSE
1745 l_instr_status := INS_STATUS_READY_TO_PRINT;
1746 END IF;
1747
1748
1749 UPDATE
1750 IBY_PAY_INSTRUCTIONS_ALL
1751 SET
1752 payment_instruction_status = l_instr_status
1753 WHERE
1754 payment_instruction_id = p_pmtInstrTab(i).
1755 payment_instruction_id;
1756
1757 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1758 print_debuginfo(l_module_name, 'Updated payment '
1759 || 'instruction '
1760 || p_pmtInstrTab(i).payment_instruction_id
1761 || ' to '
1762 || l_instr_status
1763 || ' status.'
1764 );
1765
1766 END IF;
1767 ELSE
1768 l_instr_status := INS_STATUS_CREATED;
1769 UPDATE
1770 IBY_PAY_INSTRUCTIONS_ALL
1771 SET
1772 payment_instruction_status = l_instr_status
1773 WHERE
1774 payment_instruction_id = p_pmtInstrTab(i).
1775 payment_instruction_id;
1776
1777 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1778 print_debuginfo(l_module_name, 'Updated payment '
1779 || 'instruction '
1780 || p_pmtInstrTab(i).payment_instruction_id
1781 || ' to '
1782 || l_instr_status
1783 || ' status.'
1784 );
1785
1786 END IF;
1787 END IF;
1788 ELSE
1789
1790 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1791 print_debuginfo(l_module_name, 'Skipping payment '
1792 || 'instruction '
1793 || p_pmtInstrTab(i).payment_instruction_id
1794 || ' ..'
1795 );
1796
1797 END IF;
1798 END IF;
1799
1800 END LOOP;
1801
1802 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1803 print_debuginfo(l_module_name, 'EXIT');
1804
1805 END IF;
1806 END moveInstrToDeferredStatus;
1807
1808 /*--------------------------------------------------------------------
1809 | NAME:
1810 | get_payreq_id
1811 |
1812 |
1813 | PURPOSE:
1814 |
1815 |
1816 | PARAMETERS:
1817 | IN
1818 |
1819 |
1820 | OUT
1821 |
1822 |
1823 | RETURNS:
1824 |
1825 | NOTES:
1826 |
1827 *---------------------------------------------------------------------*/
1828 FUNCTION get_payreq_id (
1829 l_ca_id IN IBY_PAY_SERVICE_REQUESTS.calling_app_id%TYPE,
1830 l_ca_payreq_cd IN IBY_PAY_SERVICE_REQUESTS.
1831 call_app_pay_service_req_code%TYPE
1832 )
1833 RETURN IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE
1834 IS
1835
1836 l_payreq_id NUMBER := -1;
1837 l_module_name VARCHAR2(200) := G_PKG_NAME || '.get_payreq_id';
1838
1839 BEGIN
1840
1841 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1842 print_debuginfo(l_module_name, 'ENTER');
1843
1844 END IF;
1845 SELECT
1846 payment_service_request_id
1847 INTO
1848 l_payreq_id
1849 FROM
1850 IBY_PAY_SERVICE_REQUESTS
1851 WHERE
1852 calling_app_id = l_ca_id
1853 AND
1854 call_app_pay_service_req_code = l_ca_payreq_cd;
1855
1856 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1857 print_debuginfo(l_module_name, 'EXIT');
1858 END IF;
1859 RETURN l_payreq_id;
1860
1861 EXCEPTION
1862 WHEN OTHERS THEN
1863 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1864 print_debuginfo(l_module_name, 'Exception occured when '
1865 || 'retrieving payment request id for '
1866 || 'calling application '
1867 || l_ca_id
1868 || ' with calling app payment request cd '
1869 || l_ca_payreq_cd
1870 );
1871 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
1872 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
1873 print_debuginfo(l_module_name, 'Returning -1 for payreq id');
1874
1875 END IF;
1876 l_payreq_id := -1;
1877 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1878 print_debuginfo(l_module_name, 'EXIT');
1879 END IF;
1880 RETURN l_payreq_id;
1881
1882 END get_payreq_id;
1883
1884 /*--------------------------------------------------------------------
1885 | NAME:
1886 | get_paydoc_id
1887 |
1888 |
1889 | PURPOSE:
1890 |Introduced this in Bug 11059189
1891 | This Function is used to get Payment Document Id given at PPR Level
1892 | PARAMETERS:
1893 | IN
1894 |
1895 |
1896 | OUT
1897 |
1898 |
1899 | RETURNS:
1900 |
1901 | NOTES:
1902 |
1903 *---------------------------------------------------------------------*/
1904 FUNCTION get_paydoc_id (
1905 l_payreq_id IN IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE
1906 )
1907 RETURN IBY_PAY_SERVICE_REQUESTS.payment_document_id%TYPE
1908 IS
1909
1910 l_paydoc_id NUMBER := NULL;
1911 l_module_name VARCHAR2(200) := G_PKG_NAME || '.get_paydoc_id';
1912
1913 BEGIN
1914
1915 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1916 print_debuginfo(l_module_name, 'ENTER');
1917
1918 END IF;
1919 SELECT
1920 payment_document_id
1921 INTO
1922 l_paydoc_id
1923 FROM
1924 IBY_PAY_SERVICE_REQUESTS
1925 WHERE
1929 print_debuginfo(l_module_name, 'Payment Document ID Fetched:'||l_paydoc_id);
1926 payment_service_request_id = l_payreq_id;
1927
1928 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1930 print_debuginfo(l_module_name, 'EXIT');
1931 END IF;
1932 RETURN l_paydoc_id;
1933
1934 EXCEPTION
1935 WHEN OTHERS THEN
1936 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1937 print_debuginfo(l_module_name, 'Exception occured when '
1938 || 'retrieving payment document id for '
1939 || ' Payment Service Request ID:'
1940 || l_payreq_id
1941 );
1942 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
1943 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
1944 print_debuginfo(l_module_name, 'Returning NULL for Payment Document id');
1945
1946 END IF;
1947 l_paydoc_id := NULL;
1948 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1949 print_debuginfo(l_module_name, 'EXIT');
1950 END IF;
1951 RETURN l_paydoc_id;
1952
1953 END get_paydoc_id;
1954
1955 /*--------------------------------------------------------------------
1956 | NAME:
1957 | get_int_bank_acct_id
1958 |
1959 |
1960 | PURPOSE:
1961 |Introduced this in Bug 16041562
1962 | This Function is used to get Internal bank account Id given at
1963 | PPR Level
1964 | PARAMETERS:
1965 | IN
1966 |
1967 |
1968 | OUT
1969 |
1970 |
1971 | RETURNS:
1972 |
1973 | NOTES:
1974 |
1975 *---------------------------------------------------------------------*/
1976 FUNCTION get_int_bank_acct_id (
1977 l_payreq_id IN IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE
1978 )
1979 RETURN IBY_PAY_SERVICE_REQUESTS.internal_bank_account_id%TYPE
1980 IS
1981
1982 l_int_bank_acct_id NUMBER := NULL;
1983 l_module_name VARCHAR2(200) := G_PKG_NAME || '.get_int_bank_acct_id';
1984
1985 BEGIN
1986
1987 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1988 print_debuginfo(l_module_name, 'ENTER');
1989
1990 END IF;
1991 SELECT
1992 internal_bank_account_id
1993 INTO
1994 l_int_bank_acct_id
1995 FROM
1996 IBY_PAY_SERVICE_REQUESTS
1997 WHERE
1998 payment_service_request_id = l_payreq_id;
1999
2000 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2001 print_debuginfo(l_module_name, 'Internal bank account ID Fetched:'||l_int_bank_acct_id);
2002 print_debuginfo(l_module_name, 'EXIT');
2003 END IF;
2004 RETURN l_int_bank_acct_id;
2005
2006 EXCEPTION
2007 WHEN OTHERS THEN
2008 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2009 print_debuginfo(l_module_name, 'Exception occured when '
2010 || 'retrieving internal bank account id for '
2011 || ' Payment Service Request ID:'
2012 || l_payreq_id
2013 );
2014 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
2015 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
2016 print_debuginfo(l_module_name, 'Returning NULL for Internal bank account ID');
2017
2018 END IF;
2019 l_int_bank_acct_id := NULL;
2020 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2021 print_debuginfo(l_module_name, 'EXIT');
2022 END IF;
2023 RETURN l_int_bank_acct_id;
2024
2025 END get_int_bank_acct_id;
2026
2027 /*--------------------------------------------------------------------
2028 | NAME:
2029 | get_instruction_attributes
2030 |
2031 |
2032 | PURPOSE:
2033 |
2034 |
2035 | PARAMETERS:
2036 | IN
2037 |
2038 |
2039 | OUT
2040 |
2041 |
2042 | RETURNS:
2043 |
2044 | NOTES:
2045 |
2046 *---------------------------------------------------------------------*/
2047 FUNCTION get_instruction_attributes(
2048 l_pmt_instr_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE)
2049 RETURN IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE
2050 IS
2051
2052 l_module_name VARCHAR2(200) := G_PKG_NAME ||
2053 '.get_instruction_attributes';
2054
2055 l_instr_rec IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE;
2056
2057 BEGIN
2058
2059 SELECT
2060 payment_instruction_id,
2061 payment_profile_id,
2062 process_type,
2063 payment_instruction_status,
2064 payments_complete_code,
2065 generate_sep_remit_advice_flag,
2066 remittance_advice_created_flag,
2067 regulatory_report_created_flag,
2068 bill_payable_flag,
2069 legal_entity_id,
2070 payment_count,
2071 positive_pay_file_created_flag,
2072 print_instruction_immed_flag,
2073 transmit_instr_immed_flag,
2074 created_by,
2075 creation_date,
2076 last_updated_by,
2077 last_update_date,
2078 last_update_login,
2079 object_version_number,
2080 internal_bank_account_id,
2081 pay_admin_assigned_ref_code,
2082 transmission_date,
2083 acknowledgement_date,
2084 comments,
2085 bank_assigned_ref_code,
2086 org_id,
2087 org_type,
2088 payment_date,
2089 payment_currency_code,
2090 payment_service_request_id,
2091 payment_function,
2092 payment_reason_code,
2096 printer_name,
2093 rfc_identifier,
2094 payment_reason_comments,
2095 payment_document_id,
2097 attribute_category,
2098 attribute1,
2099 attribute2,
2100 attribute3,
2101 attribute4,
2102 attribute5,
2103 attribute6,
2104 attribute7,
2105 attribute8,
2106 attribute9,
2107 attribute10,
2108 attribute11,
2109 attribute12,
2110 attribute13,
2111 attribute14,
2112 attribute15
2113 INTO
2114 l_instr_rec.payment_instruction_id,
2115 l_instr_rec.payment_profile_id,
2116 l_instr_rec.process_type,
2117 l_instr_rec.payment_instruction_status,
2118 l_instr_rec.payments_complete_code,
2119 l_instr_rec.generate_sep_remit_advice_flag,
2120 l_instr_rec.remittance_advice_created_flag,
2121 l_instr_rec.regulatory_report_created_flag,
2122 l_instr_rec.bill_payable_flag,
2123 l_instr_rec.legal_entity_id,
2124 l_instr_rec.payment_count,
2125 l_instr_rec.positive_pay_file_created_flag,
2126 l_instr_rec.print_instruction_immed_flag,
2127 l_instr_rec.transmit_instr_immed_flag,
2128 l_instr_rec.created_by,
2129 l_instr_rec.creation_date,
2130 l_instr_rec.last_updated_by,
2131 l_instr_rec.last_update_date,
2132 l_instr_rec.last_update_login,
2133 l_instr_rec.object_version_number,
2134 l_instr_rec.internal_bank_account_id,
2135 l_instr_rec.pay_admin_assigned_ref_code,
2136 l_instr_rec.transmission_date,
2137 l_instr_rec.acknowledgement_date,
2138 l_instr_rec.comments,
2139 l_instr_rec.bank_assigned_ref_code,
2140 l_instr_rec.org_id,
2141 l_instr_rec.org_type,
2142 l_instr_rec.payment_date,
2143 l_instr_rec.payment_currency_code,
2144 l_instr_rec.payment_service_request_id,
2145 l_instr_rec.payment_function,
2146 l_instr_rec.payment_reason_code,
2147 l_instr_rec.rfc_identifier,
2148 l_instr_rec.payment_reason_comments,
2149 l_instr_rec.payment_document_id,
2150 l_instr_rec.printer_name,
2151 l_instr_rec.attribute_category,
2152 l_instr_rec.attribute1,
2153 l_instr_rec.attribute2,
2154 l_instr_rec.attribute3,
2155 l_instr_rec.attribute4,
2156 l_instr_rec.attribute5,
2157 l_instr_rec.attribute6,
2158 l_instr_rec.attribute7,
2159 l_instr_rec.attribute8,
2160 l_instr_rec.attribute9,
2161 l_instr_rec.attribute10,
2162 l_instr_rec.attribute11,
2163 l_instr_rec.attribute12,
2164 l_instr_rec.attribute13,
2165 l_instr_rec.attribute14,
2166 l_instr_rec.attribute15
2167 FROM
2168 IBY_PAY_INSTRUCTIONS_ALL
2169 WHERE
2170 payment_instruction_id = l_pmt_instr_id
2171 ;
2172
2173
2174 RETURN l_instr_rec;
2175
2176 EXCEPTION
2177 WHEN OTHERS THEN
2178
2179 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2180 print_debuginfo(l_module_name, 'Exception occured when '
2181 || 'retrieving payment instruction attributes for '
2182 || 'payment instruction '
2183 || l_pmt_instr_id
2184 );
2185 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
2186 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
2187 print_debuginfo(l_module_name, 'Returning NULL.');
2188
2189 END IF;
2190 RETURN NULL;
2191
2192 END get_instruction_attributes;
2193
2194 /*--------------------------------------------------------------------
2195 | NAME:
2196 | print_debuginfo
2197 |
2198 | PURPOSE:
2199 |
2200 |
2201 | PARAMETERS:
2202 | IN
2203 |
2204 |
2205 | OUT
2206 |
2207 |
2208 | RETURNS:
2209 |
2210 | NOTES:
2211 |
2212 *---------------------------------------------------------------------*/
2213 PROCEDURE print_debuginfo(
2214 p_module IN VARCHAR2,
2215 p_debug_text IN VARCHAR2,
2216 p_debug_level IN VARCHAR2 DEFAULT FND_LOG.LEVEL_STATEMENT
2217 )
2218 IS
2219 l_default_debug_level VARCHAR2(200) := FND_LOG.LEVEL_STATEMENT;
2220 BEGIN
2221
2222 IF (p_debug_level IS NOT NULL) THEN
2223 l_default_debug_level := p_debug_level;
2224 END IF;
2225
2226 /*
2227 * Write the debug message to the concurrent manager log file.
2228 */
2229 IF (l_default_debug_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2230 iby_build_utils_pkg.print_debuginfo(p_module, p_debug_text,
2231 p_debug_level);
2232 END IF;
2233
2234 END print_debuginfo;
2235
2236 /*--------------------------------------------------------------------
2237 | NAME:
2238 | build_electronic_instructions
2239 |
2240 | PURPOSE:
2241 | Concurrent program wrapper for creating electronic payment
2242 | instructions.
2243 |
2244 | PARAMETERS:
2245 | IN
2246 |
2247 |
2248 | OUT
2249 |
2250 |
2251 | RETURNS:
2252 |
2253 | NOTES:
2254 |
2255 *---------------------------------------------------------------------*/
2256 PROCEDURE build_electronic_instructions(
2257 x_errbuf OUT NOCOPY VARCHAR2,
2258 x_retcode OUT NOCOPY VARCHAR2,
2259
2260 /*-- processing criteria --*/
2261 p_processing_type IN VARCHAR2,
2262
2263 /*-- user/admin assigned criteria --*/
2264 p_admin_assigned_ref IN VARCHAR2 DEFAULT NULL,
2265 p_comments IN VARCHAR2 DEFAULT NULL,
2266
2267 /*-- selection criteria --*/
2268 p_payment_profile_id IN VARCHAR2 DEFAULT NULL,
2269 p_payment_currency IN VARCHAR2 DEFAULT NULL,
2270 p_internal_bank_account_id IN VARCHAR2 DEFAULT NULL,
2271 p_payment_document_id IN VARCHAR2 DEFAULT NULL, --Bug 11059189
2272 p_calling_app_id IN VARCHAR2 DEFAULT NULL,
2273 p_calling_app_payreq_cd IN VARCHAR2 DEFAULT NULL,
2274 p_le_id IN VARCHAR2 DEFAULT NULL,
2275 p_org_type IN VARCHAR2 DEFAULT NULL,
2276 p_org_id IN VARCHAR2 DEFAULT NULL,
2277 p_payment_from_date IN VARCHAR2 DEFAULT NULL,
2278 p_payment_to_date IN VARCHAR2 DEFAULT NULL,
2279 p_transmit_now_flag IN VARCHAR2 DEFAULT NULL,
2280 p_sys_key IN VARCHAR2 DEFAULT NULL, p_arg17 IN VARCHAR2 DEFAULT NULL,
2281 p_arg18 IN VARCHAR2 DEFAULT NULL, p_arg19 IN VARCHAR2 DEFAULT NULL,
2282 p_arg20 IN VARCHAR2 DEFAULT NULL, p_arg21 IN VARCHAR2 DEFAULT NULL,
2283 p_arg22 IN VARCHAR2 DEFAULT NULL, p_arg23 IN VARCHAR2 DEFAULT NULL,
2284 p_arg24 IN VARCHAR2 DEFAULT NULL, p_arg25 IN VARCHAR2 DEFAULT NULL,
2285 p_arg26 IN VARCHAR2 DEFAULT NULL, p_arg27 IN VARCHAR2 DEFAULT NULL,
2286 p_arg28 IN VARCHAR2 DEFAULT NULL, p_arg29 IN VARCHAR2 DEFAULT NULL,
2287 p_arg30 IN VARCHAR2 DEFAULT NULL, p_arg31 IN VARCHAR2 DEFAULT NULL,
2288 p_arg32 IN VARCHAR2 DEFAULT NULL, p_arg33 IN VARCHAR2 DEFAULT NULL,
2289 p_arg34 IN VARCHAR2 DEFAULT NULL, p_arg35 IN VARCHAR2 DEFAULT NULL,
2290 p_arg36 IN VARCHAR2 DEFAULT NULL, p_arg37 IN VARCHAR2 DEFAULT NULL,
2291 p_arg38 IN VARCHAR2 DEFAULT NULL, p_arg39 IN VARCHAR2 DEFAULT NULL,
2292 p_arg40 IN VARCHAR2 DEFAULT NULL, p_arg41 IN VARCHAR2 DEFAULT NULL,
2293 p_arg42 IN VARCHAR2 DEFAULT NULL, p_arg43 IN VARCHAR2 DEFAULT NULL,
2294 p_arg44 IN VARCHAR2 DEFAULT NULL, p_arg45 IN VARCHAR2 DEFAULT NULL,
2295 p_arg46 IN VARCHAR2 DEFAULT NULL, p_arg47 IN VARCHAR2 DEFAULT NULL,
2296 p_arg48 IN VARCHAR2 DEFAULT NULL, p_arg49 IN VARCHAR2 DEFAULT NULL,
2297 p_arg50 IN VARCHAR2 DEFAULT NULL, p_arg51 IN VARCHAR2 DEFAULT NULL,
2298 p_arg52 IN VARCHAR2 DEFAULT NULL, p_arg53 IN VARCHAR2 DEFAULT NULL,
2299 p_arg54 IN VARCHAR2 DEFAULT NULL, p_arg55 IN VARCHAR2 DEFAULT NULL,
2300 p_arg56 IN VARCHAR2 DEFAULT NULL, p_arg57 IN VARCHAR2 DEFAULT NULL,
2301 p_arg58 IN VARCHAR2 DEFAULT NULL, p_arg59 IN VARCHAR2 DEFAULT NULL,
2302 p_arg60 IN VARCHAR2 DEFAULT NULL, p_arg61 IN VARCHAR2 DEFAULT NULL,
2303 p_arg62 IN VARCHAR2 DEFAULT NULL, p_arg63 IN VARCHAR2 DEFAULT NULL,
2304 p_arg64 IN VARCHAR2 DEFAULT NULL, p_arg65 IN VARCHAR2 DEFAULT NULL,
2305 p_arg66 IN VARCHAR2 DEFAULT NULL, p_arg67 IN VARCHAR2 DEFAULT NULL,
2306 p_arg68 IN VARCHAR2 DEFAULT NULL, p_arg69 IN VARCHAR2 DEFAULT NULL,
2307 p_arg70 IN VARCHAR2 DEFAULT NULL, p_arg71 IN VARCHAR2 DEFAULT NULL,
2308 p_arg72 IN VARCHAR2 DEFAULT NULL, p_arg73 IN VARCHAR2 DEFAULT NULL,
2309 p_arg74 IN VARCHAR2 DEFAULT NULL, p_arg75 IN VARCHAR2 DEFAULT NULL,
2310 p_arg76 IN VARCHAR2 DEFAULT NULL, p_arg77 IN VARCHAR2 DEFAULT NULL,
2311 p_arg78 IN VARCHAR2 DEFAULT NULL, p_arg79 IN VARCHAR2 DEFAULT NULL,
2312 p_arg80 IN VARCHAR2 DEFAULT NULL, p_arg81 IN VARCHAR2 DEFAULT NULL,
2313 p_arg82 IN VARCHAR2 DEFAULT NULL, p_arg83 IN VARCHAR2 DEFAULT NULL,
2314 p_arg84 IN VARCHAR2 DEFAULT NULL, p_arg85 IN VARCHAR2 DEFAULT NULL,
2315 p_arg86 IN VARCHAR2 DEFAULT NULL, p_arg87 IN VARCHAR2 DEFAULT NULL,
2316 p_arg88 IN VARCHAR2 DEFAULT NULL, p_arg89 IN VARCHAR2 DEFAULT NULL,
2317 p_arg90 IN VARCHAR2 DEFAULT NULL, p_arg91 IN VARCHAR2 DEFAULT NULL,
2318 p_arg92 IN VARCHAR2 DEFAULT NULL, p_arg93 IN VARCHAR2 DEFAULT NULL,
2319 p_arg94 IN VARCHAR2 DEFAULT NULL, p_arg95 IN VARCHAR2 DEFAULT NULL,
2320 p_arg96 IN VARCHAR2 DEFAULT NULL, p_arg97 IN VARCHAR2 DEFAULT NULL,
2321 p_arg98 IN VARCHAR2 DEFAULT NULL, p_arg99 IN VARCHAR2 DEFAULT NULL,
2322 p_arg100 IN VARCHAR2 DEFAULT NULL
2323 )
2324 IS
2325 l_module_name VARCHAR2(200) := G_PKG_NAME
2326 || '.build_electronic_instructions';
2327 BEGIN
2328
2329
2330 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2331 print_debuginfo(l_module_name, 'ENTER');
2332 END IF;
2333
2334 Validate_and_Set_Syskey(HEXTORAW(p_sys_key));
2335
2336 build_pmt_instructions(
2337 x_errbuf,
2338 x_retcode,
2339
2340 /*-- processing criteria --*/
2344 p_internal_bank_account_id,
2341 p_processing_type,
2342 p_payment_profile_id,
2343 p_payment_document_id, -- p_pmt_document_id
2345 NULL, -- p_print_now_flag
2346 NULL, -- p_printer_name
2347 p_payment_currency,
2348 p_transmit_now_flag,
2349
2350 /*-- user/admin assigned criteria --*/
2351 p_admin_assigned_ref,
2352 p_comments,
2353
2354 /*-- selection criteria --*/
2355 p_calling_app_id,
2356 p_calling_app_payreq_cd,
2357 p_le_id,
2358 p_org_id,
2359 p_org_type,
2360 p_payment_from_date,
2361 p_payment_to_date
2362 );
2363
2364 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2365 print_debuginfo(l_module_name, 'EXIT');
2366
2367 END IF;
2368 END build_electronic_instructions;
2369
2370 /*--------------------------------------------------------------------
2371 | NAME:
2372 | build_printed_instructions
2373 |
2374 | PURPOSE:
2375 | Concurrent program wrapper for creating printed payment
2376 | instructions.
2377 |
2378 | PARAMETERS:
2379 | IN
2380 |
2381 |
2382 | OUT
2383 |
2384 |
2385 | RETURNS:
2386 |
2387 | NOTES:
2388 |
2389 *---------------------------------------------------------------------*/
2390 PROCEDURE build_printed_instructions(
2391 x_errbuf OUT NOCOPY VARCHAR2,
2392 x_retcode OUT NOCOPY VARCHAR2,
2393
2394 /*-- processing criteria --*/
2395 p_processing_type IN VARCHAR2,
2396
2397 /*-- user/admin assigned criteria --*/
2398 p_admin_assigned_ref IN VARCHAR2 DEFAULT NULL,
2399 p_comments IN VARCHAR2 DEFAULT NULL,
2400
2401 /*-- selection criteria --*/
2402 p_payment_profile_id IN VARCHAR2 DEFAULT NULL,
2403 p_payment_currency IN VARCHAR2 DEFAULT NULL,
2404 p_internal_bank_account_id IN VARCHAR2 DEFAULT NULL,
2405 p_pmt_document_id IN VARCHAR2 DEFAULT NULL,
2406 p_calling_app_id IN VARCHAR2 DEFAULT NULL,
2407 p_calling_app_payreq_cd IN VARCHAR2 DEFAULT NULL,
2408 p_le_id IN VARCHAR2 DEFAULT NULL,
2409 p_org_type IN VARCHAR2 DEFAULT NULL,
2410 p_org_id IN VARCHAR2 DEFAULT NULL,
2411 p_payment_from_date IN VARCHAR2 DEFAULT NULL,
2412 p_payment_to_date IN VARCHAR2 DEFAULT NULL,
2413 p_print_now_flag IN VARCHAR2 DEFAULT NULL,
2414 p_printer_name IN VARCHAR2 DEFAULT NULL,
2415
2416 p_sys_key IN VARCHAR2 DEFAULT NULL, p_arg19 IN VARCHAR2 DEFAULT NULL,
2417 p_arg20 IN VARCHAR2 DEFAULT NULL, p_arg21 IN VARCHAR2 DEFAULT NULL,
2418 p_arg22 IN VARCHAR2 DEFAULT NULL, p_arg23 IN VARCHAR2 DEFAULT NULL,
2419 p_arg24 IN VARCHAR2 DEFAULT NULL, p_arg25 IN VARCHAR2 DEFAULT NULL,
2420 p_arg26 IN VARCHAR2 DEFAULT NULL, p_arg27 IN VARCHAR2 DEFAULT NULL,
2421 p_arg28 IN VARCHAR2 DEFAULT NULL, p_arg29 IN VARCHAR2 DEFAULT NULL,
2422 p_arg30 IN VARCHAR2 DEFAULT NULL, p_arg31 IN VARCHAR2 DEFAULT NULL,
2423 p_arg32 IN VARCHAR2 DEFAULT NULL, p_arg33 IN VARCHAR2 DEFAULT NULL,
2424 p_arg34 IN VARCHAR2 DEFAULT NULL, p_arg35 IN VARCHAR2 DEFAULT NULL,
2425 p_arg36 IN VARCHAR2 DEFAULT NULL, p_arg37 IN VARCHAR2 DEFAULT NULL,
2426 p_arg38 IN VARCHAR2 DEFAULT NULL, p_arg39 IN VARCHAR2 DEFAULT NULL,
2427 p_arg40 IN VARCHAR2 DEFAULT NULL, p_arg41 IN VARCHAR2 DEFAULT NULL,
2428 p_arg42 IN VARCHAR2 DEFAULT NULL, p_arg43 IN VARCHAR2 DEFAULT NULL,
2429 p_arg44 IN VARCHAR2 DEFAULT NULL, p_arg45 IN VARCHAR2 DEFAULT NULL,
2430 p_arg46 IN VARCHAR2 DEFAULT NULL, p_arg47 IN VARCHAR2 DEFAULT NULL,
2431 p_arg48 IN VARCHAR2 DEFAULT NULL, p_arg49 IN VARCHAR2 DEFAULT NULL,
2432 p_arg50 IN VARCHAR2 DEFAULT NULL, p_arg51 IN VARCHAR2 DEFAULT NULL,
2433 p_arg52 IN VARCHAR2 DEFAULT NULL, p_arg53 IN VARCHAR2 DEFAULT NULL,
2434 p_arg54 IN VARCHAR2 DEFAULT NULL, p_arg55 IN VARCHAR2 DEFAULT NULL,
2435 p_arg56 IN VARCHAR2 DEFAULT NULL, p_arg57 IN VARCHAR2 DEFAULT NULL,
2436 p_arg58 IN VARCHAR2 DEFAULT NULL, p_arg59 IN VARCHAR2 DEFAULT NULL,
2437 p_arg60 IN VARCHAR2 DEFAULT NULL, p_arg61 IN VARCHAR2 DEFAULT NULL,
2438 p_arg62 IN VARCHAR2 DEFAULT NULL, p_arg63 IN VARCHAR2 DEFAULT NULL,
2439 p_arg64 IN VARCHAR2 DEFAULT NULL, p_arg65 IN VARCHAR2 DEFAULT NULL,
2440 p_arg66 IN VARCHAR2 DEFAULT NULL, p_arg67 IN VARCHAR2 DEFAULT NULL,
2441 p_arg68 IN VARCHAR2 DEFAULT NULL, p_arg69 IN VARCHAR2 DEFAULT NULL,
2442 p_arg70 IN VARCHAR2 DEFAULT NULL, p_arg71 IN VARCHAR2 DEFAULT NULL,
2443 p_arg72 IN VARCHAR2 DEFAULT NULL, p_arg73 IN VARCHAR2 DEFAULT NULL,
2444 p_arg74 IN VARCHAR2 DEFAULT NULL, p_arg75 IN VARCHAR2 DEFAULT NULL,
2445 p_arg76 IN VARCHAR2 DEFAULT NULL, p_arg77 IN VARCHAR2 DEFAULT NULL,
2446 p_arg78 IN VARCHAR2 DEFAULT NULL, p_arg79 IN VARCHAR2 DEFAULT NULL,
2447 p_arg80 IN VARCHAR2 DEFAULT NULL, p_arg81 IN VARCHAR2 DEFAULT NULL,
2448 p_arg82 IN VARCHAR2 DEFAULT NULL, p_arg83 IN VARCHAR2 DEFAULT NULL,
2449 p_arg84 IN VARCHAR2 DEFAULT NULL, p_arg85 IN VARCHAR2 DEFAULT NULL,
2450 p_arg86 IN VARCHAR2 DEFAULT NULL, p_arg87 IN VARCHAR2 DEFAULT NULL,
2451 p_arg88 IN VARCHAR2 DEFAULT NULL, p_arg89 IN VARCHAR2 DEFAULT NULL,
2452 p_arg90 IN VARCHAR2 DEFAULT NULL, p_arg91 IN VARCHAR2 DEFAULT NULL,
2453 p_arg92 IN VARCHAR2 DEFAULT NULL, p_arg93 IN VARCHAR2 DEFAULT NULL,
2454 p_arg94 IN VARCHAR2 DEFAULT NULL, p_arg95 IN VARCHAR2 DEFAULT NULL,
2455 p_arg96 IN VARCHAR2 DEFAULT NULL, p_arg97 IN VARCHAR2 DEFAULT NULL,
2456 p_arg98 IN VARCHAR2 DEFAULT NULL, p_arg99 IN VARCHAR2 DEFAULT NULL,
2457 p_arg100 IN VARCHAR2 DEFAULT NULL
2458 )
2459 IS
2460 l_module_name VARCHAR2(200) := G_PKG_NAME
2461 || '.build_printed_instructions';
2462 BEGIN
2463
2464
2465
2466 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2467 print_debuginfo(l_module_name, 'ENTER');
2468 END IF;
2469
2470 Validate_and_Set_Syskey(HEXTORAW(p_sys_key));
2471
2472 build_pmt_instructions(
2473 x_errbuf,
2474 x_retcode,
2475
2476 /*-- processing criteria --*/
2477 p_processing_type,
2478 p_payment_profile_id,
2479 p_pmt_document_id,
2480 p_internal_bank_account_id,
2481 p_print_now_flag,
2482 p_printer_name,
2483 p_payment_currency,
2484 NULL, -- p_transmit_now_flag
2485
2486 /*-- user/admin assigned criteria --*/
2487 p_admin_assigned_ref,
2488 p_comments,
2489
2490 /*-- selection criteria --*/
2491 p_calling_app_id,
2492 p_calling_app_payreq_cd,
2493 p_le_id,
2494 p_org_id,
2495 p_org_type,
2496 p_payment_from_date,
2497 p_payment_to_date
2498 );
2499
2500 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2501 print_debuginfo(l_module_name, 'EXIT');
2502
2503 END IF;
2504 END build_printed_instructions;
2505
2506 END IBY_BUILD_INSTRUCTIONS_PUB_PKG;