1 PACKAGE BODY IBY_CHECKNUMBER_PUB AS
2 /*$Header: ibyckprb.pls 120.85.12020000.4 2013/02/20 09:55:57 sgogula ship $*/
3
4 --
5 -- Declare global variables
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_CHECKNUMBER_PUB';
8 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
10
11 --
12 -- List of instruction statuses that are used / set in this
13 -- module (payment instruction creation flow).
14 --
15 -- This module will only process payment instructions that are in
16 -- 'created' status.
17 --
18 -- If there were any problems when numbering the payment instructions
19 -- this module will set the status of the payment instruction to
20 -- 'printing deferred' status and the check printing module has to
21 -- be called again on this payment instruction.
22 --
23 -- This module will never set the status of a payment instruction to
24 -- 'creation error' because that can only be done by the PICP.
25 --
26 INS_STATUS_CREATED CONSTANT VARCHAR2(100) := 'CREATED';
27 INS_STATUS_PRINT_DEFR CONSTANT VARCHAR2(100) := 'PRINTING_DEFERRED';
28 INS_STATUS_READY_TO_PRINT CONSTANT VARCHAR2(100) :=
29 'CREATED_READY_FOR_PRINTING';
30 INS_STATUS_READY_TO_FORMAT CONSTANT VARCHAR2(100) :=
31 'CREATED_READY_FOR_FORMATTING';
32
33 --
34 -- List of payment statuses that are used / set in this
35 -- module (payment instruction creation flow).
36 --
37 PMT_STATUS_CREATED CONSTANT VARCHAR2(100) := 'CREATED';
38 PMT_STATUS_INS_CREATED CONSTANT VARCHAR2(100) := 'INSTRUCTION_CREATED';
39
40 --
41 -- List of document statuses that are used / set in this
42 -- module (payment instruction creation flow).
43 --
44 DOC_STATUS_PAY_CREATED CONSTANT VARCHAR2(100) := 'PAYMENT_CREATED';
45
46 -- Transaction types (for inserting into IBY_TRANSACTION_ERRORS table)
47 TRXN_TYPE_INSTR CONSTANT VARCHAR2(100) := 'INSTRUCTION';
48 TRXN_TYPE_PMT CONSTANT VARCHAR2(100) := 'PAYMENT';
49
50 --
51 -- List of process ciompletion statuses that are returned to
52 -- the caller after completing this flow.
53 --
54 PROCESS_STATUS_SUCCESS CONSTANT VARCHAR2(100) := 'SUCCESS';
55 PROCESS_STATUS_FAILED CONSTANT VARCHAR2(100) := 'FAILURE';
56 /*
57 * List of process types.
58 */
59 PROCESS_TYPE_IMMEDIATE CONSTANT VARCHAR2(100) := 'IMMEDIATE';
60 PROCESS_TYPE_STANDARD CONSTANT VARCHAR2(100) := 'STANDARD';
61 PROCESS_TYPE_MANUAL CONSTANT VARCHAR2(100) := 'MANUAL';
62
63 --
64 -- Forward declarations
65 --
66 PROCEDURE print_debuginfo(
67 p_module IN VARCHAR2,
68 p_debug_text IN VARCHAR2,
69 p_debug_level IN VARCHAR2 DEFAULT FND_LOG.LEVEL_STATEMENT
70 );
71
72 /*--------------------------------------------------------------------
73 | NAME:
74 | performCheckNumbering
75 |
76 | PURPOSE:
77 | Prints the given payment instruction on the provided paper stock
78 | (payment document).
79 |
80 |
81 | PARAMETERS:
82 | IN
83 |
84 |
85 | OUT
86 |
87 |
88 | RETURNS:
89 |
90 | NOTES:
91 |
92 |
93 *---------------------------------------------------------------------*/
94 PROCEDURE performCheckNumbering(
95 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.
96 payment_instruction_id%TYPE,
97 p_pmt_document_id IN CE_PAYMENT_DOCUMENTS.
98 payment_document_id%TYPE,
99 p_user_assgn_num IN IBY_PAYMENTS_ALL.
100 paper_document_number%TYPE,
101 x_return_status IN OUT NOCOPY VARCHAR2,
102 x_return_message IN OUT NOCOPY VARCHAR2,
103 x_msg_count IN OUT NOCOPY NUMBER,
104 x_msg_data IN OUT NOCOPY VARCHAR2
105 )
106 IS
107
108 l_module_name VARCHAR2(200) := G_PKG_NAME || '.performCheckNumbering';
109
110 l_ret_status NUMBER := -1;
111 l_ret_message VARCHAR2(2000);
112
113 l_pmtInstrRec IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE;
114
115 l_pmtsInInstrRec IBY_PAYINSTR_PUB.pmtsInPmtInstrRecType;
116 l_pmtsInInstrTab IBY_PAYINSTR_PUB.pmtsInpmtInstrTabType;
117
118 l_error_code VARCHAR2(500);
119
120 l_instruction_id NUMBER(15);
121
122 /*
123 * These two are used for holding dummy payments and dummy
124 * documents that are associated with setup and overflow
125 * documents.
126 */
127 l_dummy_pmts_tab IBY_PAYGROUP_PUB.paymentTabType;
128 l_dummy_docs_tab docsTabType;
129 l_overflow_docs_tab overflowDocsTabType;
130
131 l_instr_err_tab IBY_VALIDATIONSETS_PUB.docErrorTabType;
132 l_err_tokens_tab IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType;
133
134 l_pkg_name CONSTANT VARCHAR2(100) := 'AP_AWT_CALLOUT_PKG';
135
136 l_callout_name VARCHAR2(500);
137 l_stmt VARCHAR2(1000);
138
139 /* used in invocation of callout procedure */
140 l_api_version CONSTANT NUMBER := 1.0;
141 l_msg_count NUMBER;
142 l_msg_data VARCHAR2(2000);
143
144 /*
145 * Cursor to pick up the payments (and related fields)
146 * of a given payment instruction.
147 */
148 CURSOR c_pmts_in_instruction (p_instr_id IBY_PAY_INSTRUCTIONS_ALL.
149 payment_instruction_id%TYPE)
150 IS
151 SELECT
152 pmt.payment_id,
153 instr.payment_instruction_id,
154 pmt.payment_amount,
155 pmt.payment_currency_code,
156 pmt.payment_status,
157 pmt.payment_profile_id,
158 prof.processing_type,
159 -1, /* pmt_details_len */
160 -1, /* document_count */
161 pmt.separate_remit_advice_req_flag,
162 pmt.paper_document_number
163 FROM
164 IBY_PAYMENTS_ALL pmt,
165 IBY_PAY_INSTRUCTIONS_ALL instr,
166 IBY_PAYMENT_PROFILES prof
167 WHERE
168 instr.payment_instruction_id = p_instr_id AND
169 instr.payment_instruction_id = pmt.payment_instruction_id AND
170 instr.payment_profile_id = pmt.payment_profile_id AND
171 instr.payment_profile_id = prof.payment_profile_id AND
172 pmt.payment_status = PMT_STATUS_INS_CREATED
173
174 /*
175 * Fix for bug 5198523:
176 *
177 * Ordering the payments by payment reference number
178 * guarantees that the paper document numbers provided
179 * to these payments downstream also follow the same
180 * order.
181 */
182 ORDER BY pmt.payment_reference_number ASC
183 ;
184
185
186 /*
187 * Implementing the callout is optional for the calling app.
188 * If the calling app does not implement the hook, then
189 * the call to the hook will result in ORA-06576 error.
190 *
191 * There is no exception name associated with this code, so
192 * we create one called 'PROCEDURE_NOT_IMPLEMENTED'. If this
193 * exception occurs, it is not fatal: we log the error and
194 * proceed.
195 *
196 * If, on the other hand, the calling app implements the
197 * callout, but the callout throws an exception, it is fatal
198 * and we must abort the program (this will be caught
199 * in WHEN OTHERS block).
200 */
201 PROCEDURE_NOT_IMPLEMENTED EXCEPTION;
202 PRAGMA EXCEPTION_INIT(PROCEDURE_NOT_IMPLEMENTED, -6576);
203
204 BEGIN
205 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
206 print_debuginfo(l_module_name, 'ENTER');
207
208 print_debuginfo(l_module_name, 'Provided payment instruction id: '
209 || p_instruction_id);
210
211 END IF;
212 /*
213 * Pick up all attributes of the given payment instruction
214 * from the IBY_PAY_INSTRUCTIONS_ALL table.
215 */
216 BEGIN
217
218 SELECT
219 payment_instruction_id,
220 payment_profile_id,
221 process_type,
222 payment_instruction_status,
223 payments_complete_code,
224 generate_sep_remit_advice_flag,
225 remittance_advice_created_flag,
226 regulatory_report_created_flag,
227 bill_payable_flag,
228 legal_entity_id,
229 payment_count,
230 positive_pay_file_created_flag,
231 print_instruction_immed_flag,
232 transmit_instr_immed_flag,
233 created_by,
234 creation_date,
235 last_updated_by,
236 last_update_date,
237 last_update_login,
238 object_version_number,
239 internal_bank_account_id,
240 pay_admin_assigned_ref_code,
241 transmission_date,
242 acknowledgement_date,
243 comments,
244 bank_assigned_ref_code,
245 org_id,
246 org_type,
247 payment_date,
248 payment_currency_code,
249 payment_service_request_id,
250 payment_function,
251 payment_reason_code,
252 rfc_identifier,
253 payment_reason_comments,
254 payment_document_id,
255 printer_name,
256 attribute_category,
257 attribute1,
258 attribute2,
259 attribute3,
260 attribute4,
261 attribute5,
262 attribute6,
263 attribute7,
264 attribute8,
265 attribute9,
266 attribute10,
267 attribute11,
268 attribute12,
269 attribute13,
270 attribute14,
271 attribute15
272 INTO
273 l_pmtInstrRec.payment_instruction_id,
274 l_pmtInstrRec.payment_profile_id,
275 l_pmtInstrRec.process_type,
276 l_pmtInstrRec.payment_instruction_status,
277 l_pmtInstrRec.payments_complete_code,
278 l_pmtInstrRec.generate_sep_remit_advice_flag,
279 l_pmtInstrRec.remittance_advice_created_flag,
280 l_pmtInstrRec.regulatory_report_created_flag,
281 l_pmtInstrRec.bill_payable_flag,
282 l_pmtInstrRec.legal_entity_id,
283 l_pmtInstrRec.payment_count,
284 l_pmtInstrRec.positive_pay_file_created_flag,
285 l_pmtInstrRec.print_instruction_immed_flag,
286 l_pmtInstrRec.transmit_instr_immed_flag,
287 l_pmtInstrRec.created_by,
288 l_pmtInstrRec.creation_date,
289 l_pmtInstrRec.last_updated_by,
290 l_pmtInstrRec.last_update_date,
291 l_pmtInstrRec.last_update_login,
292 l_pmtInstrRec.object_version_number,
293 l_pmtInstrRec.internal_bank_account_id,
294 l_pmtInstrRec.pay_admin_assigned_ref_code,
295 l_pmtInstrRec.transmission_date,
296 l_pmtInstrRec.acknowledgement_date,
297 l_pmtInstrRec.comments,
298 l_pmtInstrRec.bank_assigned_ref_code,
299 l_pmtInstrRec.org_id,
300 l_pmtInstrRec.org_type,
301 l_pmtInstrRec.payment_date,
302 l_pmtInstrRec.payment_currency_code,
303 l_pmtInstrRec.payment_service_request_id,
304 l_pmtInstrRec.payment_function,
305 l_pmtInstrRec.payment_reason_code,
306 l_pmtInstrRec.rfc_identifier,
307 l_pmtInstrRec.payment_reason_comments,
308 l_pmtInstrRec.payment_document_id,
309 l_pmtInstrRec.printer_name,
310 l_pmtInstrRec.attribute_category,
311 l_pmtInstrRec.attribute1,
312 l_pmtInstrRec.attribute2,
313 l_pmtInstrRec.attribute3,
314 l_pmtInstrRec.attribute4,
315 l_pmtInstrRec.attribute5,
316 l_pmtInstrRec.attribute6,
317 l_pmtInstrRec.attribute7,
318 l_pmtInstrRec.attribute8,
319 l_pmtInstrRec.attribute9,
320 l_pmtInstrRec.attribute10,
321 l_pmtInstrRec.attribute11,
322 l_pmtInstrRec.attribute12,
323 l_pmtInstrRec.attribute13,
324 l_pmtInstrRec.attribute14,
325 l_pmtInstrRec.attribute15
326 FROM
327 IBY_PAY_INSTRUCTIONS_ALL
328 WHERE
329 payment_instruction_id = p_instruction_id
330 ;
331
332 EXCEPTION
333
334 WHEN OTHERS THEN
335 /*
336 * This error condition will be handled below.
337 */
338 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
339 print_debuginfo(l_module_name, 'Exception occured when '
340 || 'retrieving details of payment instruction '
341 || p_instruction_id
342 );
343 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
344 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
345
346 END IF;
347 END;
348
349 IF (l_pmtInstrRec.payment_instruction_id IS NULL) THEN
350
351 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
352 print_debuginfo(l_module_name, 'Provided payment instruction id '
353 || p_instruction_id
354 || ' not found in IBY_PAY_INSTRUCTIONS_ALL table. '
355 || 'Processing cannot continue. Aborting program.'
356 );
357
358 END IF;
359 x_return_status := FND_API.G_RET_STS_ERROR;
360
361 l_error_code := 'IBY_INS_NOT_FOUND';
362 FND_MESSAGE.set_name('IBY', l_error_code);
363
364 FND_MESSAGE.SET_TOKEN('INS_ID',
365 p_instruction_id,
366 FALSE);
367
368 x_return_message := FND_MESSAGE.get;
369
370 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
371 print_debuginfo(l_module_name, 'EXIT');
372
373 END IF;
374 RETURN;
375
376 END IF;
377
378 /*
379 * Only proceed if the payment instruction is in the
380 * 'created' status.
381 */
382 IF (l_pmtInstrRec.payment_instruction_status
383 <> INS_STATUS_CREATED AND
384 l_pmtInstrRec.payment_instruction_status
385 <> INS_STATUS_READY_TO_PRINT AND
386 l_pmtInstrRec.payment_instruction_status
387 <> INS_STATUS_READY_TO_FORMAT
388 ) THEN
389
390 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
391 print_debuginfo(l_module_name, 'Payment instruction id '
392 || p_instruction_id
393 || ' has status '
394 || l_pmtInstrRec.payment_instruction_status
395 || '. Payment instruction must be in '
396 || INS_STATUS_READY_TO_PRINT
397 || ' or '
398 || INS_STATUS_READY_TO_FORMAT
399 || ' for check numbering. '
400 || 'Processing cannot continue. Aborting program.'
401 );
402
403 END IF;
404 x_return_status := FND_API.G_RET_STS_ERROR;
405
406 l_error_code := 'IBY_INS_NOT_NUM_STATUS';
407 FND_MESSAGE.set_name('IBY', l_error_code);
408
409 FND_MESSAGE.SET_TOKEN('INS_ID',
410 p_instruction_id,
411 FALSE);
412
413 FND_MESSAGE.SET_TOKEN('BAD_STATUS',
414 l_pmtInstrRec.payment_instruction_status,
415 FALSE);
416
417 FND_MESSAGE.SET_TOKEN('GOOD_STATUS1',
418 INS_STATUS_READY_TO_PRINT,
419 FALSE);
420
421 FND_MESSAGE.SET_TOKEN('GOOD_STATUS2',
422 INS_STATUS_READY_TO_FORMAT,
423 FALSE);
424
425 FND_MESSAGE.SET_TOKEN('GOOD_STATUS3',
426 INS_STATUS_CREATED,
427 FALSE);
428
429 x_return_message := FND_MESSAGE.get;
430
431 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
432 print_debuginfo(l_module_name, 'EXIT');
433
434 END IF;
435 RETURN;
436
437 ELSE
438
439 /*
440 * If we reached here, it means that the given payment
441 * instruction is in an appropriate status for numbering.
442 *
443 * SPECIAL CASE:
444 *
445 * Check for a special case, where this payment instruction
446 * might have already been numbered earlier, but the format
447 * program crashed or raised an exception. In that case, the
448 * status of the payment instruction would not have been
449 * changed.
450 *
451 * Such a payment instruction would re-enter the check
452 * numbering flow, but we must not renumber this instruction
453 * because we have already numbered it.
454 *
455 * If the specified payment document is already locked by the
456 * given payment instruction, then we know that this payment
457 * instruction has already been numbered and is re-entering
458 * the check numbering flow.
459 *
460 * Simply return success and exit.
461 */
462 BEGIN
463
464 SELECT
465 ce.payment_instruction_id
466 INTO
467 l_instruction_id
468 FROM
469 CE_PAYMENT_DOCUMENTS ce
470 WHERE
471 ce.payment_document_id = p_pmt_document_id
472 ;
473
474 IF (l_instruction_id = p_instruction_id) THEN
475
476 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
477 print_debuginfo(l_module_name, 'Payment instruction '
478 || p_instruction_id
479 || ' has already been numbered. Returning '
480 || 'success response ..'
481 );
482
483 END IF;
484 x_return_status := FND_API.G_RET_STS_SUCCESS;
485 x_return_message := 'SUCCESS';
486
487 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
488 print_debuginfo(l_module_name, 'EXIT');
489
490 END IF;
491 RETURN;
492
493 ELSE
494
495 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
496 print_debuginfo(l_module_name, 'Payment instruction '
497 || p_instruction_id
498 || ' has not been previously numbered.'
499 );
500
501 END IF;
502 END IF;
503
504
505 EXCEPTION
506 WHEN OTHERS THEN
507
508
509 print_debuginfo(l_module_name, 'Exception occured when '
510 || 'getting details of payment document id '
511 || p_pmt_document_id
512 || ' from CE_PAYMENT_DOCUMENTS table.',
513 FND_LOG.LEVEL_UNEXPECTED
514 );
515
516 print_debuginfo(l_module_name, 'SQL code: '
517 || SQLCODE, FND_LOG.LEVEL_UNEXPECTED);
518 print_debuginfo(l_module_name, 'SQL err msg: '
519 || SQLERRM, FND_LOG.LEVEL_UNEXPECTED);
520
521
522 /*
523 * Propogate exception to caller.
524 */
525 RAISE;
526
527 END;
528
529 END IF;
530
531 /*
532 * Pick up all payments of the given payment instruction
533 * from the IBY_PAYMENTS_ALL table.
534 */
535 OPEN c_pmts_in_instruction(p_instruction_id);
536 FETCH c_pmts_in_instruction BULK COLLECT INTO l_pmtsInInstrTab;
537 CLOSE c_pmts_in_instruction;
538
539 IF (l_pmtsInInstrTab.COUNT = 0) THEN
540
541 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
542 print_debuginfo(l_module_name, 'Cannot retrieve payments '
543 || 'and profile details for the provided payment '
544 || 'instruction id. Processing cannot continue. '
545 || 'Aborting program.'
546 );
547
548 END IF;
549 x_return_status := FND_API.G_RET_STS_ERROR;
550
551 l_error_code := 'IBY_INS_PMTS_NOT_FOUND';
552 FND_MESSAGE.set_name('IBY', l_error_code);
553
554 FND_MESSAGE.SET_TOKEN('INS_ID',
555 p_instruction_id,
556 FALSE);
557
558 x_return_message := FND_MESSAGE.get;
559
560 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
561 print_debuginfo(l_module_name, 'EXIT');
562
563 END IF;
564 RETURN;
565
566 END IF;
567
568 /* populate the document count associated with each payment */
569 /* removed the call to populateDocumentCount, alternatively in
570 * processPaperPayments() API, the count of document is got through
571 * a query on iby_docs_payable_all - AtT Build pmts perf issue
572 */
573 -- populateDocumentCount(l_pmtsInInstrTab);
574
575 /*
576 * Calculate the number of setup and overflow documents for
577 * this payment instruction.
578 *
579 * Then provide all the payments with check numbers.
580 */
581 processPaperPayments(
582 p_pmt_document_id, p_user_assgn_num, l_pmtInstrRec,
583 l_pmtsInInstrTab, l_dummy_pmts_tab, l_dummy_docs_tab,
584 l_overflow_docs_tab, l_instr_err_tab, l_err_tokens_tab,
585 l_ret_status, l_ret_message, x_msg_count, x_msg_data);
586
587 /*
588 * Now, that we have completed check printing, update
589 * the payments of the payment instruction with check
590 * numbers.
591 *
592 * Insert the created dummy documents (setup and overflow
593 * documents) into the database.
594 *
595 * Finally, change the status of the payment instruction
596 * to 'submitted for printing'.
597 */
598 IF (l_ret_status = 0) THEN
599
600 performDBUpdates(p_instruction_id,
601 l_pmtsInInstrTab, l_dummy_pmts_tab,
602 l_dummy_docs_tab, l_overflow_docs_tab,
603 l_instr_err_tab, l_err_tokens_tab);
604
605 ELSE
606
607
608 print_debuginfo(l_module_name, 'Check numbering process '
609 || 'did not succeed. Raising exception ..',
610 FND_LOG.LEVEL_UNEXPECTED
611 );
612
613
614 /*
615 * Raise an exception.
616 */
617 APP_EXCEPTION.RAISE_EXCEPTION;
618
619 END IF;
620
621 /*
622 * If we reached here, it means that the payments of the
623 * payment instruction have been numbered successfully
624 * and that the status of the payment instruction is now
625 * 'CREATED_READY_FOR_PRINTING' | 'CREATED_READY_FOR_FORMATTING'.
626 *
627 * After check numbering has been completed, invoke the withholding
628 * certificates hook. This is relevant only for AP, but we will
629 * call this hook blindly and let AP figure out whether there
630 * are any payments of interest for them.
631 */
632 l_callout_name := l_pkg_name || '.' || 'zx_witholdingCertificatesHook';
633
634 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
635 print_debuginfo(l_module_name, 'Attempting to call hook: '
636 || l_callout_name);
637
638 END IF;
639 l_stmt := 'CALL '|| l_callout_name || '(:1, :2, :3, :4, :5, :6, :7, :8)';
640
641 BEGIN
642
643 EXECUTE IMMEDIATE
644 (l_stmt)
645 USING
646 IN p_instruction_id,
647 IN 'GENERATE',
648 IN l_api_version,
649 IN FND_API.G_FALSE,
650 IN FND_API.G_FALSE,
651 OUT x_return_status,
652 OUT l_msg_count,
653 OUT l_msg_data
654 ;
655
656 /*
657 * If the called procedure did not return success,
658 * raise an exception.
659 */
660 IF (x_return_status IS NULL OR
661 x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
662
663
664 print_debuginfo(l_module_name, 'Fatal: External app callout '''
665 || l_callout_name
666 || ''', returned failure status - '
667 || x_return_status
668 || '. Raising exception.',
669 FND_LOG.LEVEL_UNEXPECTED
670 );
671
672
673 l_error_code := 'IBY_INS_AWT_CERT_HOOK_FAILED';
674 FND_MESSAGE.set_name('IBY', l_error_code);
675
676 l_ret_message := FND_MESSAGE.get;
677
678 APP_EXCEPTION.RAISE_EXCEPTION;
679
680 END IF;
681
682 EXCEPTION
683
684 WHEN PROCEDURE_NOT_IMPLEMENTED THEN
685 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
686 print_debuginfo(l_module_name, 'Callout "' || l_callout_name
687 || '" not implemented by calling app - AP'
688 );
689
690 print_debuginfo(l_module_name, 'Skipping hook call.');
691
692 END IF;
693 WHEN OTHERS THEN
694
695 print_debuginfo(l_module_name, 'Fatal: External app callout '''
696 || l_callout_name
697 || ''', generated exception.',
698 FND_LOG.LEVEL_UNEXPECTED
699 );
700
701 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
702 FND_LOG.LEVEL_UNEXPECTED);
703 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
704 FND_LOG.LEVEL_UNEXPECTED);
705
706
707 /*
708 * Propogate exception to caller.
709 */
710 RAISE;
711 END;
712
713 x_return_status := FND_API.G_RET_STS_SUCCESS;
714 x_return_message := 'SUCCESS';
715
716 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
717 print_debuginfo(l_module_name, 'Returning success response ..');
718
719 print_debuginfo(l_module_name, 'EXIT');
720
721 END IF;
722 EXCEPTION
723 WHEN OTHERS THEN
724 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
725 print_debuginfo(l_module_name, 'Exception occured when '
726 || 'performing payment numbering. Payment numbering '
727 || 'process will be aborted.'
728 );
729 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
730 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
731
732 END IF;
733 x_return_status := FND_API.G_RET_STS_ERROR;
734 x_return_message := l_ret_message;
735
736 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
737 print_debuginfo(l_module_name, 'EXIT');
738
739 END IF;
740 END performCheckNumbering;
741
742 /*--------------------------------------------------------------------
743 | NAME:
744 | populateDocumentCount
745 |
746 | PURPOSE:
747 |
748 |
749 |
750 | PARAMETERS:
751 | IN
752 |
753 | OUT
754 |
755 |
756 | RETURNS:
757 |
758 | NOTES:
759 |
760 *---------------------------------------------------------------------*/
761 PROCEDURE populateDocumentCount(
762 x_pmtsInPmtInstrTab IN OUT NOCOPY IBY_PAYINSTR_PUB.pmtsInpmtInstrTabType
763 )
764 IS
765
766 l_module_name VARCHAR2(200) := G_PKG_NAME || '.populateDocumentCount';
767 l_docs_in_pmt_count docsInPmtCountTabType;
768
769 CURSOR c_document_count(pmt_instruction_id Number)
770 IS
771 SELECT
772 count(*),
773 DOCS.payment_id
774 FROM
775 IBY_DOCS_PAYABLE_ALL DOCS, IBY_PAYMENTS_ALL PMTS
776 WHERE
777 PMTS.PAYMENT_INSTRUCTION_ID = pmt_instruction_id
778 AND PMTS.PAYMENT_ID = DOCS.PAYMENT_ID
779 AND DOCS.document_status = DOC_STATUS_PAY_CREATED
780
781 GROUP BY
782 DOCS.payment_id
783 ;
784
785 BEGIN
786
787 OPEN c_document_count(x_pmtsInPmtInstrTab(x_pmtsInPmtInstrTab.FIRST).pay_instr_id);
788 FETCH c_document_count BULK COLLECT INTO l_docs_in_pmt_count;
789 CLOSE c_document_count;
790
791 IF (l_docs_in_pmt_count.COUNT = 0) THEN
792
793 /*
794 * Normally this shouldn't happen.
795 */
796
797 print_debuginfo(l_module_name, 'No documents payable '
798 || 'were found in ''payments created'' status '
799 || 'though created payments exist. '
800 || 'Possible data corruption. Aborting ..',
801 FND_LOG.LEVEL_UNEXPECTED
802 );
803
804
805 APP_EXCEPTION.RAISE_EXCEPTION;
806
807 END IF;
808
809 FOR i in x_pmtsInPmtInstrTab.FIRST .. x_pmtsInPmtInstrTab.LAST LOOP
810
811 FOR j in l_docs_in_pmt_count.FIRST .. l_docs_in_pmt_count.LAST LOOP
812
813 IF (x_pmtsInPmtInstrTab(i).payment_id = l_docs_in_pmt_count(j).
814 payment_id) THEN
815
816 x_pmtsInPmtInstrTab(i).document_count :=
817 l_docs_in_pmt_count(j).doc_count;
818
819 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
820 print_debuginfo(l_module_name, 'Num docs for payment: '
821 || x_pmtsInPmtInstrTab(i).payment_id
822 || ' is: '
823 || x_pmtsInPmtInstrTab(i).document_count
824 );
825
826 END IF;
827 /* exit inner loop */
828 EXIT;
829
830 END IF;
831
832 END LOOP;
833
834 END LOOP;
835
836 END populateDocumentCount;
837
838 /*--------------------------------------------------------------------
839 | NAME:
840 | updatePaymentInstructions
841 |
842 | PURPOSE:
843 | Performs an update of all created instructions from PLSQL
844 | table into IBY_PAY_INSTRUCTIONS_ALL table.
845 |
846 | The created instructions have already been inserted into
847 | IBY_PAY_INSTRUCTIONS_ALL after grouping (and before validation).
848 | So we only need to update certain fields of the instruction
849 | that have been changed after the grouping was performed.
850 |
851 | PARAMETERS:
852 | IN
853 |
854 |
855 | OUT
856 |
857 |
858 | RETURNS:
859 |
860 | NOTES:
861 |
862 *---------------------------------------------------------------------*/
863 PROCEDURE updatePaymentInstructions(
864 p_payInstrTab IN IBY_PAYINSTR_PUB.pmtInstrTabType
865 )
866 IS
867 l_module_name VARCHAR2(200) := G_PKG_NAME || '.updatePaymentInstructions';
868
869 BEGIN
870
871 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
872 print_debuginfo(l_module_name, 'ENTER');
873
874 END IF;
875 /* Normally, this shouldn't happen */
876 IF (p_payInstrTab.COUNT = 0) THEN
877 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
878 print_debuginfo(l_module_name, 'No payment instructions'
879 || ' were found to update IBY_PAY_INSTRUCTIONS_ALL table.');
880 END IF;
881 RETURN;
882 END IF;
883
884 FOR i in p_payInstrTab.FIRST..p_payInstrTab.LAST LOOP
885 UPDATE
886 IBY_PAY_INSTRUCTIONS_ALL
887 SET
888 payment_instruction_status = p_payInstrTab(i).
889 payment_instruction_status
890 WHERE
891 payment_instruction_id = p_payInstrTab(i).payment_instruction_id;
892 END LOOP;
893
894 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
895 print_debuginfo(l_module_name, 'EXIT');
896
897 END IF;
898 END updatePaymentInstructions;
899
900 /*--------------------------------------------------------------------
901 | NAME:
902 | insertPaperDocuments
903 |
904 | PURPOSE:
905 |
906 |
907 |
908 | PARAMETERS:
909 | IN
910 |
911 |
912 | OUT
913 |
914 |
915 | RETURNS:
916 |
917 | NOTES:
918 |
919 *---------------------------------------------------------------------*/
920 PROCEDURE insertPaperDocuments(
921 p_paperPmtsTab IN IBY_PAYGROUP_PUB.paymentTabType,
922 p_setupDocsTab IN docsTabType,
923 p_overflowDocsTab IN overflowDocsTabType
924 )
925 IS
926 l_setup_pmts_tab IBY_PAYGROUP_PUB.paymentTabType;
927 l_overflow_pmts_tab IBY_PAYGROUP_PUB.paymentTabType;
928
929 l_module_name VARCHAR2(200) := G_PKG_NAME || '.insertPaperDocuments';
930 BEGIN
931
932 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
933 print_debuginfo(l_module_name, 'ENTER');
934
935 END IF;
936 IF (p_paperPmtsTab.COUNT = 0) THEN
937 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
938 print_debuginfo(l_module_name, 'No paper documents were '
939 || 'provided for insert. Exiting ..'
940 );
941 print_debuginfo(l_module_name, 'EXIT');
942 END IF;
943 RETURN;
944 END IF;
945
946 /*
947 * Split the p_paperPmtsTab to get the setup payments
948 * and overflow payments into separate data structures.
949 */
950 splitPaymentsByType(p_paperPmtsTab, l_setup_pmts_tab,
951 l_overflow_pmts_tab);
952
953 /*
954 * Handle setup payments.
955 */
956 IF (l_setup_pmts_tab.COUNT <> 0) THEN
957
958 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
959 print_debuginfo(l_module_name, 'Inserting '
960 || l_setup_pmts_tab.COUNT
961 || ' setup payments.'
962 );
963
964 END IF;
965 /* setup payments can be bulk inserted */
966 FORALL i in l_setup_pmts_tab.FIRST..l_setup_pmts_tab.LAST
967 INSERT INTO IBY_PAYMENTS_ALL VALUES l_setup_pmts_tab(i);
968
969 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
970 print_debuginfo(l_module_name, 'Inserting '
971 || p_setupDocsTab.COUNT
972 || ' setup documents.'
973 );
974
975 END IF;
976 /* documents related to setup payments can be bulk inserted */
977 FORALL i in p_setupDocsTab.FIRST..p_setupDocsTab.LAST
978 INSERT INTO IBY_DOCS_PAYABLE_ALL VALUES p_setupDocsTab(i);
979
980 END IF;
981
982 /*
983 * Handle overflow payments.
984 */
985 IF (l_overflow_pmts_tab.COUNT <> 0) THEN
986
987 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
988 print_debuginfo(l_module_name, 'Inserting '
989 || l_overflow_pmts_tab.COUNT
990 || ' overflow payments.'
991 );
992
993 END IF;
994 /* overflow payments can be bulk inserted */
995 FORALL i in l_overflow_pmts_tab.FIRST..l_overflow_pmts_tab.LAST
996 INSERT INTO IBY_PAYMENTS_ALL VALUES l_overflow_pmts_tab(i);
997
998
999 /*
1000 * Fix for bug 6765314:
1001 *
1002 * The overflow payment is a dummy payment that is related
1003 * to a real payment. Copy the important attributes of the
1004 * real payment onto the original payment. This will ensure
1005 * that when the overflow payment is printed, it contains
1006 * the payee name, payer name etc.
1007 *
1008 * The external bank account id field on the overflow
1009 * payment actually contains the payment id of the
1010 * real payment. See KLUDGE in performSpecialPaperHandling()
1011 * for more information on this.
1012 */
1013 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1014 print_debuginfo(l_module_name, 'Updating '
1015 || 'overflow payments with attributes '
1016 || 'from related real payments.'
1017 );
1018
1019 END IF;
1020 FOR i in l_overflow_pmts_tab.FIRST..l_overflow_pmts_tab.LAST LOOP
1021
1022 UPDATE
1023 iby_payments_all overflow
1024 SET
1025 (
1026 overflow.payment_method_code,
1027 overflow.internal_bank_account_id,
1028 overflow.org_id,
1029 overflow.org_type,
1030 overflow.legal_entity_id,
1031 overflow.delivery_channel_code,
1032 overflow.ext_payee_id,
1033 overflow.payment_profile_id,
1034 overflow.payee_party_id,
1035 overflow.party_site_id,
1036 overflow.supplier_site_id,
1037 overflow.payment_reason_code,
1038 overflow.payment_reason_comments,
1039 overflow.payment_date,
1040 overflow.remittance_message1,
1041 overflow.remittance_message2,
1042 overflow.remittance_message3,
1043 overflow.payment_due_date,
1044 overflow.beneficiary_party,
1045 overflow.remit_to_location_id,
1046 overflow.payee_name,
1047 overflow.payee_address1,
1048 overflow.payee_address2,
1049 overflow.payee_address3,
1050 overflow.payee_address4,
1051 overflow.payee_city,
1052 overflow.payee_postal_code,
1053 overflow.payee_state,
1054 overflow.payee_province,
1055 overflow.payee_county,
1056 overflow.payee_country,
1057 overflow.org_name,
1058 overflow.payer_legal_entity_name,
1059 overflow.payee_party_name,
1060 overflow.payer_party_site_name,
1061 overflow.payee_address_concat,
1062 overflow.beneficiary_name,
1063 overflow.payer_party_number,
1064 overflow.payee_party_number,
1065 overflow.payee_alternate_name,
1066 overflow.payee_site_alternate_name,
1067 overflow.payee_supplier_number,
1068 overflow.payee_first_party_reference,
1069 overflow.address_source,
1070 overflow.employee_address_code,
1071 overflow.employee_person_id,
1072 overflow.employee_payment_flag,
1073 overflow.employee_address_id,
1074 overflow.payer_party_id,
1075 overflow.payer_location_id,
1076 overflow.payee_supplier_id,
1077 overflow.payee_supplier_site_name,
1078 overflow.payee_supplier_site_alt_name
1079 ) =
1080 (
1081 SELECT
1082 real.payment_method_code,
1083 real.internal_bank_account_id,
1084 real.org_id,
1085 real.org_type,
1086 real.legal_entity_id,
1087 real.delivery_channel_code,
1088 real.ext_payee_id,
1089 real.payment_profile_id,
1090 real.payee_party_id,
1091 real.party_site_id,
1092 real.supplier_site_id,
1093 real.payment_reason_code,
1094 real.payment_reason_comments,
1095 real.payment_date,
1096 real.remittance_message1,
1097 real.remittance_message2,
1098 real.remittance_message3,
1099 real.payment_due_date,
1100 real.beneficiary_party,
1101 real.remit_to_location_id,
1102 real.payee_name,
1103 real.payee_address1,
1104 real.payee_address2,
1105 real.payee_address3,
1106 real.payee_address4,
1107 real.payee_city,
1108 real.payee_postal_code,
1109 real.payee_state,
1110 real.payee_province,
1111 real.payee_county,
1112 real.payee_country,
1113 real.org_name,
1114 real.payer_legal_entity_name,
1115 real.payee_party_name,
1116 real.payer_party_site_name,
1117 real.payee_address_concat,
1118 real.beneficiary_name,
1119 real.payer_party_number,
1120 real.payee_party_number,
1121 real.payee_alternate_name,
1122 real.payee_site_alternate_name,
1123 real.payee_supplier_number,
1124 real.payee_first_party_reference,
1125 real.address_source,
1126 real.employee_address_code,
1127 real.employee_person_id,
1128 real.employee_payment_flag,
1129 real.employee_address_id,
1130 real.payer_party_id,
1131 real.payer_location_id,
1132 real.payee_supplier_id,
1133 real.payee_supplier_site_name,
1134 real.payee_supplier_site_alt_name
1135 FROM
1136 iby_payments_all real
1137 WHERE
1138 real.payment_id = overflow.external_bank_account_id
1139 )
1140 WHERE overflow.payment_id = l_overflow_pmts_tab(i).payment_id
1141 ;
1142 END LOOP;
1143
1144 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1145 print_debuginfo(l_module_name, 'Updating '
1146 || p_overflowDocsTab.COUNT
1147 || ' overflow documents.'
1148 );
1149
1150 END IF;
1151 /*
1152 * For overflow payments, the documents are already
1153 * available in the IBY_DOCS_PAYABLE_ALL table. The
1154 * formatting payment id on these documents needs
1155 * to be updated to account for the overflow payments
1156 * (1 overflow payment = 1 printed void check).
1157 */
1158 FOR i in p_overflowDocsTab.FIRST..p_overflowDocsTab.LAST LOOP
1159
1160 UPDATE
1161 IBY_DOCS_PAYABLE_ALL
1162 SET
1163 formatting_payment_id =
1164 p_overflowDocsTab(i).format_payment_id
1165 WHERE
1166 document_payable_id = p_overflowDocsTab(i).doc_id;
1167
1168 END LOOP;
1169
1170 END IF;
1171
1172 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1173 print_debuginfo(l_module_name, 'EXIT');
1174
1175 END IF;
1176 END insertPaperDocuments;
1177
1178 /*--------------------------------------------------------------------
1179 | NAME:
1180 | splitPaymentsByType
1181 |
1182 | PURPOSE:
1183 |
1184 |
1185 |
1186 | PARAMETERS:
1187 | IN
1188 |
1189 |
1190 | OUT
1191 |
1192 |
1193 | RETURNS:
1194 |
1195 | NOTES:
1196 |
1197 *---------------------------------------------------------------------*/
1198 PROCEDURE splitPaymentsByType(
1199 p_paperPmtsTab IN IBY_PAYGROUP_PUB.paymentTabType,
1200 x_setupPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
1201 x_overflowPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType
1202 )
1203 IS
1204
1205 l_module_name VARCHAR2(200) := G_PKG_NAME || '.splitPaymentsByType';
1206
1207 BEGIN
1208
1209 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1210 print_debuginfo(l_module_name, 'ENTER');
1211
1212 END IF;
1213 IF (p_paperPmtsTab.COUNT = 0) THEN
1214
1215 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1216 print_debuginfo(l_module_name, 'No payments to split. Exiting ..');
1217 print_debuginfo(l_module_name, 'EXIT');
1218 END IF;
1219 RETURN;
1220
1221 END IF;
1222
1223 FOR i in p_paperPmtsTab.FIRST..p_paperPmtsTab.LAST LOOP
1224
1225 /* setup payments */
1226 IF (p_paperPmtsTab(i).payment_status = 'VOID_BY_SETUP') THEN
1227
1228 x_setupPmtsTab(x_setupPmtsTab.COUNT + 1)
1229 := p_paperPmtsTab(i);
1230
1231 /* overflow payments */
1232 ELSIF (p_paperPmtsTab(i).payment_status = 'VOID_BY_OVERFLOW') THEN
1233
1234 x_overflowPmtsTab(x_overflowPmtsTab.COUNT + 1)
1235 := p_paperPmtsTab(i);
1236
1237 /* normally, shouldn't come here */
1238 ELSE
1239 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1240 print_debuginfo(l_module_name, 'Ignoring payment with '
1241 || 'status '
1242 || p_paperPmtsTab(i).payment_status
1243 );
1244 END IF;
1245 END IF;
1246
1247 END LOOP;
1248
1249 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1250 print_debuginfo(l_module_name, 'Created '
1251 || x_setupPmtsTab.COUNT
1252 || ' setup payments and '
1253 || x_overflowPmtsTab.COUNT
1254 || ' overflow payments from '
1255 || p_paperPmtsTab.COUNT
1256 || ' provided payments.'
1257 );
1258
1259 print_debuginfo(l_module_name, 'EXIT');
1260
1261 END IF;
1262 END splitPaymentsByType;
1263
1264 /*--------------------------------------------------------------------
1265 | NAME:
1266 | performDBUpdates
1267 |
1268 | PURPOSE:
1269 |
1270 |
1271 |
1272 | PARAMETERS:
1273 | IN
1274 |
1275 |
1276 | OUT
1277 |
1278 |
1279 | RETURNS:
1280 |
1281 |
1282 | NOTES:
1283 |
1284 *---------------------------------------------------------------------*/
1285 PROCEDURE performDBUpdates(
1286 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.
1287 payment_instruction_id%TYPE,
1288 x_pmtsInPmtInstrTab IN OUT NOCOPY IBY_PAYINSTR_PUB.pmtsInpmtInstrTabType,
1289 x_dummyPaperPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
1290 x_setupDocsTab IN OUT NOCOPY docsTabType,
1291 x_overflowDocsTab IN OUT NOCOPY overflowDocsTabType,
1292 x_insErrorsTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1293 x_insTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
1294 trxnErrTokenTabType
1295 )
1296 IS
1297 l_module_name VARCHAR2(200) := G_PKG_NAME || '.performDBUpdates';
1298
1299 BEGIN
1300
1301 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1302 print_debuginfo(l_module_name, 'ENTER');
1303
1304 END IF;
1305 /*
1306 * Update the payments table by providing a check number to
1307 * each payment.
1308 */
1309 updatePmtsWithCheckNumbers(x_pmtsInPmtInstrTab);
1310
1311 /*
1312 * Insert the setup and overflow documents that have been
1313 * created as part of paper payments handing.
1314 */
1315 insertPaperDocuments(x_dummyPaperPmtsTab, x_setupDocsTab,
1316 x_overflowDocsTab);
1317
1318 /*
1319 * Insert any payment instruction errors into
1320 * IBY_TRANSACTION_ERRORS table.
1321 */
1322 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N', x_insErrorsTab,
1323 x_insTokenTab);
1324
1325 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1326 print_debuginfo(l_module_name, 'EXIT');
1327
1328 END IF;
1329 EXCEPTION
1330
1331 WHEN OTHERS THEN
1332
1333 print_debuginfo(l_module_name, 'Fatal: Exception when updating '
1334 || 'payment instruction/payment status after payment '
1335 || 'instruction creation. All changes will be rolled back.',
1336 FND_LOG.LEVEL_UNEXPECTED
1337 );
1338 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
1339 FND_LOG.LEVEL_UNEXPECTED);
1340 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
1341 FND_LOG.LEVEL_UNEXPECTED);
1342
1343
1344 /*
1345 * Propogate exception to caller.
1346 */
1347 RAISE;
1348
1349 END performDBUpdates;
1350
1351 /*--------------------------------------------------------------------
1352 | NAME:
1353 | processPaperPayments
1354 |
1355 | PURPOSE:
1356 |
1357 |
1358 |
1359 | PARAMETERS:
1360 | IN
1361 |
1362 |
1363 | OUT
1364 |
1365 |
1366 | RETURNS:
1367 |
1368 | NOTES:
1369 |
1370 *---------------------------------------------------------------------*/
1371 PROCEDURE processPaperPayments(
1372 p_pmt_document_id IN CE_PAYMENT_DOCUMENTS.payment_document_id%TYPE,
1373 p_user_assgn_num IN IBY_PAYMENTS_ALL.paper_document_number%TYPE,
1374 x_pmtInstrRec IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE,
1375 x_pmtsInPmtInstrTab IN OUT NOCOPY IBY_PAYINSTR_PUB.pmtsInpmtInstrTabType,
1376 x_dummyPaperPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
1377 x_setupDocsTab IN OUT NOCOPY docsTabType,
1378 x_overflowDocsTab IN OUT NOCOPY overflowDocsTabType,
1379 x_instrErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1380 x_insTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
1381 trxnErrTokenTabType,
1382 x_return_status IN OUT NOCOPY NUMBER,
1383 x_return_message IN OUT NOCOPY VARCHAR2,
1384 x_msg_count IN OUT NOCOPY NUMBER,
1385 x_msg_data IN OUT NOCOPY VARCHAR2
1386 )
1387 IS
1388
1389 l_module_name VARCHAR2(200) := G_PKG_NAME
1390 || '.processPaperPayments';
1391 l_processing_type VARCHAR2(100); -- PRINTED | ELECTRONIC
1392 l_num_printed_docs NUMBER(15);
1393 l_paper_stock_rec paperStockRecType;
1394 l_paper_stocks_tab paperStocksTabType;
1395 l_docs_count NUMBER;
1396
1397 l_error_code VARCHAR2(3000);
1398
1399 l_paper_special_docs_rec paperPmtsSpecialDocsRecType;
1400 l_paper_special_docs_tab paperPmtsSpecialDocsTabType;
1401
1402 l_pmtsInPmtInstrRec IBY_PAYINSTR_PUB.pmtsInpmtInstrRecType;
1403
1404 CURSOR c_paper_stock (p_pmt_doc_id IN CE_PAYMENT_DOCUMENTS.
1405 payment_document_id%TYPE)
1406 IS
1407 SELECT
1408 payment_document_id,
1409 payment_document_name,
1410 NVL(number_of_setup_documents, 0),
1411 DECODE(attached_remittance_stub_flag,'Y',number_of_lines_per_remit_stub,NULL) number_of_lines_per_remit_stub
1412 FROM
1413 CE_PAYMENT_DOCUMENTS
1414 WHERE
1415 payment_document_id = p_pmt_doc_id
1416 ;
1417
1418 BEGIN
1419
1420 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1421 print_debuginfo(l_module_name, 'ENTER');
1422
1423 END IF;
1424 OPEN c_paper_stock (p_pmt_document_id);
1425 FETCH c_paper_stock BULK COLLECT INTO l_paper_stocks_tab;
1426 CLOSE c_paper_stock;
1427
1428 /*
1429 * There should be exactly one payment document linked
1430 * to a particular payment document name.
1431 */
1432 IF (l_paper_stocks_tab.COUNT <> 1) THEN
1433
1434 --IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1435 print_debuginfo(l_module_name, 'Unable to get matching payment '
1436 || 'document from CE_PAYMENT_DOCUMENTS for payment '
1437 || 'document id: '
1438 || p_pmt_document_id
1439 || '. Number of payment documents retrieved: '
1440 || l_paper_stocks_tab.COUNT
1441 );
1442
1443 --END IF;
1444 x_return_status := -1;
1445
1446 l_error_code := 'IBY_INS_NO_PMT_DOC';
1447 FND_MESSAGE.set_name('IBY', l_error_code);
1448
1449 FND_MESSAGE.SET_TOKEN('PMT_DOC_ID',
1450 p_pmt_document_id,
1451 FALSE);
1452
1453 x_return_message := FND_MESSAGE.get;
1454
1455 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1456 print_debuginfo(l_module_name, 'EXIT');
1457 END IF;
1458 RETURN;
1459
1460 END IF;
1461
1462 /*
1463 * Since, payment instructions are grouped by
1464 * profile id, they are implicitly grouped
1465 * by processing type as well.
1466 *
1467 * pmtsInpmtInstrTabType will have multiple records for
1468 * a payment instruction (one record for each payment
1469 * in the instruction). All these payments will have the
1470 * same profile id, and the same procesing type
1471 * (because only payments with a specific profile id wil
1472 * be grouped into a payment instruction).
1473 *
1474 * Therefore, in order to determine the processing type
1475 * of a payment instruction, we simply need to retrieve
1476 * the processing type from any payment of that
1477 * instruction. We do this by retrieving the processing
1478 * type of the first payment of that instruction.
1479 */
1480 l_pmtsInPmtInstrRec := x_pmtsInPmtInstrTab(x_pmtsInPmtInstrTab.FIRST);
1481 l_processing_type := l_pmtsInPmtInstrRec.processing_type;
1482
1483 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1484 print_debuginfo(l_module_name, 'For payment instruction '
1485 || x_pmtInstrRec.payment_instruction_id
1486 || ', profile id: '
1487 || x_pmtInstrRec.payment_profile_id
1488 || ', processing type: '
1489 || l_processing_type
1490 || ', # pmts: '
1491 || x_pmtInstrRec.payment_count
1492 );
1493
1494 END IF;
1495 /*
1496 * If the processing type for this instruction is set
1497 * to 'PAPER', it means that this payment instruction
1498 * will be physically printed onto paper.
1499 *
1500 * Perform paper payment specific processing such as
1501 * calculating setup and overflow documents.
1502 */
1503 IF (l_processing_type = 'PRINTED') THEN
1504
1505 /* Get the attributes of the paper stock */
1506 /*
1507 * There will only be one paper stock linked to
1508 * a particular payment document name, so we can
1509 * simply take the first record from the fetched
1510 * paper stocks table.
1511 */
1512 l_paper_stock_rec := l_paper_stocks_tab(1);
1513
1514 IF (l_paper_stock_rec.doc_id IS NOT NULL) THEN
1515
1516 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1517 print_debuginfo(l_module_name, 'Paper stock matching '
1518 || 'profile id '
1519 || x_pmtInstrRec.payment_profile_id
1520 || ' has following attributes - Name: '
1521 || l_paper_stock_rec.doc_name
1522 || ', num setup docs: '
1523 || l_paper_stock_rec.num_setup_docs
1524 || ', num lines per stub: '
1525 || l_paper_stock_rec.num_lines_per_stub
1526 );
1527
1528 END IF;
1529 ELSE
1530
1531 --IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1532 print_debuginfo(l_module_name, 'Fatal: No paper stock '
1533 || 'linked to profile id '
1534 || x_pmtInstrRec.payment_profile_id
1535 || '. Payment instruction creation cannot continue. '
1536 || 'Aborting program ..'
1537 );
1538
1539 --END IF;
1540 x_return_status := -1;
1541
1542 l_error_code := 'IBY_INS_NO_PAPER_STOCK';
1543 FND_MESSAGE.set_name('IBY', l_error_code);
1544
1545 FND_MESSAGE.SET_TOKEN('INS_ID',
1546 x_pmtInstrRec.payment_instruction_id,
1547 FALSE);
1548
1549 x_return_message := FND_MESSAGE.get;
1550
1551 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1552 print_debuginfo(l_module_name, 'EXIT');
1553 END IF;
1554 RETURN;
1555
1556 END IF;
1557
1558 /*
1559 * If we reached here it means paper stock
1560 * attributes were retrieved successfully
1561 * for the profile on the instruction.
1562 */
1563
1564 /*
1565 * Loop through all the payments for this
1566 * payment instruction, processing them one-by-one.
1567 */
1568 FOR j in x_pmtsInPmtInstrTab.FIRST .. x_pmtsInPmtInstrTab.LAST
1569 LOOP
1570
1571 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1572 print_debuginfo(l_module_name, 'Processing payment '
1573 || x_pmtsInPmtInstrTab(j).payment_id
1574 || ' for instruction '
1575 || x_pmtInstrRec.payment_instruction_id
1576 || ' ..'
1577 );
1578 END IF;
1579 /* Removing the call to getDocumentCount, rather accessing
1580 * IBY_DOCS_PAYABLE_ALL table to get the count of the
1581 * documents
1582 */
1583 /* l_docs_count := getDocumentCountForPayment(
1584 x_pmtsInPmtInstrTab(j).payment_id,
1585 x_pmtsInPmtInstrTab
1586 );
1587 */
1588
1589 SELECT
1590 COUNT(*)
1591 INTO
1592 x_pmtsInPmtInstrTab(j).document_count
1593 FROM
1594 IBY_DOCS_PAYABLE_ALL
1595 WHERE
1596 payment_id = x_pmtsInPmtInstrTab(j).payment_id
1597 AND
1598 document_status = DOC_STATUS_PAY_CREATED;
1599
1600
1601 l_docs_count := x_pmtsInPmtInstrTab(j).document_count;
1602 IF (l_docs_count <= 0) THEN
1603 /*
1604 * A successful payment must be linked to at least
1605 * least one successful document payable. If not
1606 * there is a data consistency issue.
1607 *
1608 * Raise an alert and abort the program.
1609 */
1610 --IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1611 print_debuginfo(l_module_name, 'Fatal: No successful '
1612 || 'docs were found for successful pmt with id '
1613 || x_pmtsInPmtInstrTab(j).payment_id
1614 || '. Possible data corruption. '
1615 || 'Aborting program ..'
1616 );
1617
1618 --END IF;
1619 x_return_status := -1;
1620
1621 l_error_code := 'IBY_INS_PMT_NO_DOCS';
1622 FND_MESSAGE.set_name('IBY', l_error_code);
1623
1624 FND_MESSAGE.SET_TOKEN('PMT_ID',
1625 x_pmtsInPmtInstrTab(j).payment_id,
1626 FALSE);
1627
1628 FND_MESSAGE.SET_TOKEN('INS_ID',
1629 x_pmtInstrRec.payment_instruction_id,
1630 FALSE);
1631
1632 x_return_message := FND_MESSAGE.get;
1633
1634 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1635 print_debuginfo(l_module_name, 'EXIT');
1636 END IF;
1637 RETURN;
1638
1639 END IF;
1640
1641 l_num_printed_docs := getNumPrintedDocsByFormula(
1642 l_docs_count,
1643 l_paper_stock_rec.
1644 num_lines_per_stub
1645 );
1646
1647 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1648 print_debuginfo(l_module_name, 'For payment id '
1649 || x_pmtsInPmtInstrTab(j).payment_id
1650 || ', docs count on payment: '
1651 || l_docs_count
1652 || ', lines per stub on stock: '
1653 || l_paper_stock_rec.num_lines_per_stub
1654 || ', calculated number of printed docs: '
1655 || l_num_printed_docs);
1656
1657 END IF;
1658 /*
1659 * Add the details about the setup docs and
1660 * overflow docs for this payment into the
1661 * special docs table. This special docs table
1662 * will be used for inserting dummy payments
1663 * into the IBY_DOCS_PAYABLE_ALL and
1664 * IBY_PAYMENTS_ALL tables to account for setup
1665 * and overflow checks.
1666 */
1667 l_paper_special_docs_rec.payment_id :=
1668 x_pmtsInPmtInstrTab(j).payment_id;
1669
1670 l_paper_special_docs_rec.instruction_id :=
1671 x_pmtsInPmtInstrTab(j).pay_instr_id;
1672
1673 /*
1674 * Only one printed document per payment will be a
1675 * valid payment document. The rest of the documents
1676 * will be overflow documents that will be void and
1677 * are only used to store the document payable
1678 * ids.
1679 */
1680 l_paper_special_docs_rec.num_overflow_docs :=
1681 l_num_printed_docs - 1;
1682
1683 /*
1684 * Number of setup documents to print is
1685 * specified in the paper stock set up.
1686 */
1687 /*
1688 * Set up Docs are not considered for quick payments
1689 * Defaulting the set up docs to '0'
1690 */
1691
1692 IF (x_pmtInstrRec.process_type = PROCESS_TYPE_IMMEDIATE ) THEN
1693 l_paper_special_docs_rec.num_setup_docs := 0;
1694 ELSE
1695 l_paper_special_docs_rec.num_setup_docs := l_paper_stock_rec.num_setup_docs;
1696 END IF;
1697
1698 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1699 print_debuginfo(l_module_name, 'Set Up docs : ' || l_paper_special_docs_rec.num_setup_docs );
1700 END IF;
1701
1702 /*
1703 * Lines per stub in used in populating
1704 * dummy documents payable for this
1705 * payment.
1706 */
1707 l_paper_special_docs_rec.num_lines_per_stub :=
1708 l_paper_stock_rec.num_lines_per_stub;
1709
1710 /* add record to dummy docs table */
1711 l_paper_special_docs_tab(l_paper_special_docs_tab.COUNT
1712 + 1) := l_paper_special_docs_rec;
1713 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1714 print_debuginfo(l_module_name, 'Processing type of parent '
1715 || 'request is '
1716 || x_pmtInstrRec.process_type
1717 );
1718
1719 END IF;
1720
1721 IF (x_pmtInstrRec.process_type = PROCESS_TYPE_IMMEDIATE AND l_paper_special_docs_rec.num_overflow_docs > 0) THEN
1722 /*
1723 * Single payments should not result in void by overflow checks
1724 * Raise an alert and abort the program.
1725 */
1726 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1727 print_debuginfo(l_module_name, 'Fatal: Void by overflow checks resulted for quick payment with payment ID'
1728 || x_pmtsInPmtInstrTab(j).payment_id
1729 || '. Aborting program ..'
1730 );
1731
1732 END IF;
1733 x_return_status := -1;
1734 l_error_code := 'IBY_PAY_EXCEEDS_INV_PER_STUB';
1735 FND_MESSAGE.set_name('IBY', l_error_code);
1736 FND_MESSAGE.SET_TOKEN('STUB', l_paper_stock_rec.num_lines_per_stub, FALSE);
1737 x_return_message := FND_MESSAGE.get;
1738 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1739 print_debuginfo(l_module_name, 'EXIT');
1740 END IF;
1741 RETURN;
1742 END IF;
1743
1744 END LOOP; -- for each payment in this instruction
1745
1746 /*
1747 * If we reached here, it means that 'num overflow docs'
1748 * and 'num setup docs' has been populated for each
1749 * paper payment in the l_paper_special_docs_tab table.
1750 *
1751 * Use this information to create appropriate number
1752 * of dummy payments (to insert into IBY_PAYMENTS_ALL table)
1753 * and corresponding documents (to insert into
1754 * IBY_DOCS_PAYABLE_ALL table).
1755 */
1756 performSpecialPaperDocHandling(
1757 l_paper_special_docs_tab,
1758 x_dummyPaperPmtsTab,
1759 x_setupDocsTab,
1760 x_overflowDocsTab
1761 );
1762
1763 /*
1764 * After setup and overflow payments have been
1765 * handled, we can start assigning physical check
1766 * numbers to the paper payments.
1767 *
1768 * The 'paper special docs tab' holds real payments
1769 * that have to be printed onto paper; the 'paper pmts
1770 * tab' holds the dummy payments that also have to
1771 * printed onto paper. So both types of payments
1772 * need to be provided with check numbers.
1773 */
1774 assignCheckNumbers(
1775 x_pmtInstrRec,
1776 l_paper_stock_rec.doc_id,
1777 p_user_assgn_num,
1778 l_paper_special_docs_tab,
1779 x_dummyPaperPmtsTab,
1780 x_instrErrorTab,
1781 x_insTokenTab,
1782 x_return_message,
1783 x_return_status,
1784 x_msg_count,
1785 x_msg_data
1786 );
1787
1788 IF (x_return_status = -1) THEN
1789
1790 /*
1791 * Return back the x_return status and
1792 * x_return_message values that we received
1793 * from assignCheckNumbers() call.
1794 */
1795 --IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1796 print_debuginfo(l_module_name, 'Check numbering did '
1797 || 'not succeed.'
1798 );
1799 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1800 print_debuginfo(l_module_name, 'EXIT');
1801 END IF;
1802 RETURN;
1803
1804 END IF;
1805
1806 /*
1807 * l_paper_special_docs_tab contains list of paper
1808 * payments that now have check number assigned.
1809 *
1810 * But this data structure is a local object. We
1811 * have to copy back the check numbers into
1812 * x_pmtsInPmtInstrTab which will be used to
1813 * finally update the database.
1814 */
1815
1816 /* 16366300: POOR PEFORMANCE OF IBY PAYMENT BUILD PROCESS (IBYBUILD) - Start */
1817
1818 FOR p in x_pmtsInPmtInstrTab.FIRST ..
1819 x_pmtsInPmtInstrTab.LAST LOOP
1820
1821 FOR m in l_paper_special_docs_tab.FIRST ..
1822 l_paper_special_docs_tab.LAST LOOP
1823
1824 IF (l_paper_special_docs_tab(m).payment_id
1825 = x_pmtsInPmtInstrTab(p).payment_id) THEN
1826
1827 x_pmtsInPmtInstrTab(p).check_number
1828 := l_paper_special_docs_tab(m).check_number;
1829
1830 l_paper_special_docs_tab.DELETE(m);
1831 /* Delete this record as its updated,no need to have it in the next time we loop */
1832 EXIT; /* exit out the loop since check_number is unique. */
1833
1834 END IF;
1835
1836 END LOOP; -- for m in l_paper_special_docs_tab
1837
1838 END LOOP; -- for p in x_pmtsInPmtInstrTab
1839
1840 /* 16366300: POOR PEFORMANCE OF IBY PAYMENT BUILD PROCESS (IBYBUILD) - End */
1841
1842 ELSIF (l_processing_type = 'ELECTRONIC') THEN
1843
1844 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1845 print_debuginfo(l_module_name, 'Provided processing '
1846 || 'type : '
1847 || l_processing_type
1848 || ' does not require printed '
1849 || 'documents. '
1850 || 'No processing will be performed.'
1851 );
1852 End IF;
1853 /*
1854 * There will only be one paper stock linked to
1855 * a particular payment document name, so we can
1856 * simply take the first record from the fetched
1857 * paper stocks table.
1858 */
1859 l_paper_stock_rec := l_paper_stocks_tab(1);
1860
1861 IF (l_paper_stock_rec.doc_id IS NOT NULL) THEN
1862
1863 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1864 print_debuginfo(l_module_name, 'Paper stock matching '
1865 || 'profile id '
1866 || x_pmtInstrRec.payment_profile_id
1867 || ' has following attributes - Name: '
1868 || l_paper_stock_rec.doc_name
1869 || ', num setup docs: '
1870 || l_paper_stock_rec.num_setup_docs
1871 || ', num lines per stub: '
1872 || l_paper_stock_rec.num_lines_per_stub
1873 );
1874 END IF;
1875
1876 ELSE
1877
1878 --IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1879 print_debuginfo(l_module_name, 'Fatal: No paper stock '
1880 || 'linked to profile id '
1881 || x_pmtInstrRec.payment_profile_id
1882 || '. Payment instruction creation cannot continue. '
1883 || 'Aborting program ..'
1884 );
1885 --END IF;
1886
1887 x_return_status := -1;
1888
1889 l_error_code := 'IBY_INS_NO_PAPER_STOCK';
1890 FND_MESSAGE.set_name('IBY', l_error_code);
1891
1892 FND_MESSAGE.SET_TOKEN('INS_ID',
1893 x_pmtInstrRec.payment_instruction_id,
1894 FALSE);
1895
1896 x_return_message := FND_MESSAGE.get;
1897
1898 print_debuginfo(l_module_name, 'EXIT');
1899 RETURN;
1900
1901 END IF;
1902
1903 /*
1904 * If we reached here it means paper stock
1905 * attributes were retrieved successfully
1906 * for the profile on the instruction.
1907 */
1908
1909 /*
1910 * Loop through all the payments for this
1911 * payment instruction, processing them one-by-one.
1912 */
1913 FOR j in x_pmtsInPmtInstrTab.FIRST .. x_pmtsInPmtInstrTab.LAST
1914 LOOP
1915
1916 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1917 print_debuginfo(l_module_name, 'Processing payment '
1918 || x_pmtsInPmtInstrTab(j).payment_id
1919 || ' for instruction '
1920 || x_pmtInstrRec.payment_instruction_id
1921 || ' ..'
1922 );
1923 END IF;
1924 /* Removing the call to getDocumentCount, rather accessing
1925 * IBY_DOCS_PAYABLE_ALL table to get the count of the
1926 * documents
1927 */
1928 /* l_docs_count := getDocumentCountForPayment(
1929 x_pmtsInPmtInstrTab(j).payment_id,
1930 x_pmtsInPmtInstrTab
1931 );
1932 */
1933
1934 SELECT
1935 COUNT(*)
1936 INTO
1937 x_pmtsInPmtInstrTab(j).document_count
1938 FROM
1939 IBY_DOCS_PAYABLE_ALL
1940 WHERE
1941 payment_id = x_pmtsInPmtInstrTab(j).payment_id
1942 AND
1943 document_status = DOC_STATUS_PAY_CREATED;
1944
1945
1946 l_docs_count := x_pmtsInPmtInstrTab(j).document_count;
1947 IF (l_docs_count <= 0) THEN
1948 /*
1949 * A successful payment must be linked to at least
1950 * least one successful document payable. If not
1951 * there is a data consistency issue.
1952 *
1953 * Raise an alert and abort the program.
1954 */
1955 --IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1956 print_debuginfo(l_module_name, 'Fatal: No successful '
1957 || 'docs were found for successful pmt with id '
1958 || x_pmtsInPmtInstrTab(j).payment_id
1959 || '. Possible data corruption. '
1960 || 'Aborting program ..'
1961 );
1962 --END IF;
1963
1964 x_return_status := -1;
1965
1966 l_error_code := 'IBY_INS_PMT_NO_DOCS';
1967 FND_MESSAGE.set_name('IBY', l_error_code);
1968
1969 FND_MESSAGE.SET_TOKEN('PMT_ID',
1970 x_pmtsInPmtInstrTab(j).payment_id,
1971 FALSE);
1972
1973 FND_MESSAGE.SET_TOKEN('INS_ID',
1974 x_pmtInstrRec.payment_instruction_id,
1975 FALSE);
1976
1977 x_return_message := FND_MESSAGE.get;
1978
1979 print_debuginfo(l_module_name, 'EXIT');
1980 RETURN;
1981
1982 END IF;
1983
1984 l_num_printed_docs := getNumPrintedDocsByFormula(
1985 l_docs_count,
1986 l_paper_stock_rec.
1987 num_lines_per_stub
1988 );
1989
1990 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1991 print_debuginfo(l_module_name, 'For payment id '
1992 || x_pmtsInPmtInstrTab(j).payment_id
1993 || ', docs count on payment: '
1994 || l_docs_count
1995 || ', lines per stub on stock: '
1996 || l_paper_stock_rec.num_lines_per_stub
1997 || ', calculated number of printed docs: '
1998 || l_num_printed_docs);
1999 END IF;
2000
2001 /*
2002 * Add the details about the setup docs and
2003 * overflow docs for this payment into the
2004 * special docs table. This special docs table
2005 * will be used for inserting dummy payments
2006 * into the IBY_DOCS_PAYABLE_ALL and
2007 * IBY_PAYMENTS_ALL tables to account for setup
2008 * and overflow checks.
2009 */
2010 l_paper_special_docs_rec.payment_id :=
2011 x_pmtsInPmtInstrTab(j).payment_id;
2012
2013 l_paper_special_docs_rec.instruction_id :=
2014 x_pmtsInPmtInstrTab(j).pay_instr_id;
2015
2016 /*
2017 * Only one printed document per payment will be a
2018 * valid payment document. The rest of the documents
2019 * will be overflow documents that will be void and
2020 * are only used to store the document payable
2021 * ids.
2022 */
2023 l_paper_special_docs_rec.num_overflow_docs :=
2024 l_num_printed_docs - 1;
2025
2026 /*
2027 * Number of setup documents to print is
2028 * specified in the paper stock set up.
2029 */
2030 l_paper_special_docs_rec.num_setup_docs :=
2031 l_paper_stock_rec.num_setup_docs;
2032
2033 /*
2034 * Lines per stub in used in populating
2035 * dummy documents payable for this
2036 * payment.
2037 */
2038 l_paper_special_docs_rec.num_lines_per_stub :=
2039 l_paper_stock_rec.num_lines_per_stub;
2040
2041 /* add record to dummy docs table */
2042 l_paper_special_docs_tab(l_paper_special_docs_tab.COUNT
2043 + 1) := l_paper_special_docs_rec;
2044
2045 END LOOP; -- for each payment in this instruction
2046
2047 /*
2048 * If we reached here, it means that 'num overflow docs'
2049 * and 'num setup docs' has been populated for each
2050 * paper payment in the l_paper_special_docs_tab table.
2051 *
2052 * Use this information to create appropriate number
2053 * of dummy payments (to insert into IBY_PAYMENTS_ALL table)
2054 * and corresponding documents (to insert into
2055 * IBY_DOCS_PAYABLE_ALL table).
2056 */
2057 /*Special Paper Doc Handling is not required for Electronic type of payments*/
2058 /*performSpecialPaperDocHandling(
2059 l_paper_special_docs_tab,
2060 x_dummyPaperPmtsTab,
2061 x_setupDocsTab,
2062 x_overflowDocsTab
2063 );*/
2064
2065 /*
2066 * After setup and overflow payments have been
2067 * handled, we can start assigning physical check
2068 * numbers to the paper payments.
2069 *
2070 * The 'paper special docs tab' holds real payments
2071 * that have to be printed onto paper; the 'paper pmts
2072 * tab' holds the dummy payments that also have to
2073 * printed onto paper. So both types of payments
2074 * need to be provided with check numbers.
2075 */
2076 assignElectronicCheckNumbers(
2077 x_pmtInstrRec,
2078 l_paper_stock_rec.doc_id,
2079 p_user_assgn_num,
2080 l_paper_special_docs_tab,
2081 x_dummyPaperPmtsTab,
2082 x_instrErrorTab,
2083 x_insTokenTab,
2084 x_return_message,
2085 x_return_status,
2086 x_msg_count,
2087 x_msg_data
2088 );
2089
2090 IF (x_return_status = -1) THEN
2091
2092 /*
2093 * Return back the x_return status and
2094 * x_return_message values that we received
2095 * from assignCheckNumbers() call.
2096 */
2097 --IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2098 print_debuginfo(l_module_name, 'Check numbering did '
2099 || 'not succeed.'
2100 );
2101 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2102 print_debuginfo(l_module_name, 'EXIT');
2103 END IF;
2104 RETURN;
2105
2106 END IF;
2107
2108 /*
2109 * l_paper_special_docs_tab contains list of paper
2110 * payments that now have check number assigned.
2111 *
2112 * But this data structure is a local object. We
2113 * have to copy back the check numbers into
2114 * x_pmtsInPmtInstrTab which will be used to
2115 * finally update the database.
2116 */
2117 /* 16366300: POOR PEFORMANCE OF IBY PAYMENT BUILD PROCESS (IBYBUILD) - Start */
2118
2119 FOR p in x_pmtsInPmtInstrTab.FIRST ..
2120 x_pmtsInPmtInstrTab.LAST LOOP
2121
2122 FOR m in l_paper_special_docs_tab.FIRST ..
2123 l_paper_special_docs_tab.LAST LOOP
2124
2125 IF (l_paper_special_docs_tab(m).payment_id
2126 = x_pmtsInPmtInstrTab(p).payment_id) THEN
2127
2128 x_pmtsInPmtInstrTab(p).check_number
2129 := l_paper_special_docs_tab(m).check_number;
2130
2131 l_paper_special_docs_tab.DELETE(m);
2132 /* Delete this record as its updated,no need to have it in the next time we loop */
2133 EXIT; /* exit out the loop since check_number is unique. */
2134
2135 END IF;
2136
2137 END LOOP; -- for m in l_paper_special_docs_tab
2138
2139 END LOOP; -- for p in x_pmtsInPmtInstrTab
2140
2141 /* 16366300: POOR PEFORMANCE OF IBY PAYMENT BUILD PROCESS (IBYBUILD) - End */
2142
2143
2144 ELSE
2145
2146 --IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2147 print_debuginfo(l_module_name, 'Unknown processing '
2148 || 'type provided: '
2149 || l_processing_type
2150 || '. Aborting program ..'
2151 );
2152
2153 --END IF;
2154 x_return_status := -1;
2155
2156 l_error_code := 'IBY_INS_UNK_PROC_TYPE';
2157 FND_MESSAGE.set_name('IBY', l_error_code);
2158
2159 FND_MESSAGE.SET_TOKEN('UNK_PROC_TYPE',
2160 l_processing_type,
2161 FALSE);
2162
2163 FND_MESSAGE.SET_TOKEN('INS_ID',
2164 x_pmtInstrRec.payment_instruction_id,
2165 FALSE);
2166
2167 x_return_message := FND_MESSAGE.get;
2168
2169 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2170 print_debuginfo(l_module_name, 'EXIT');
2171 END IF;
2172 RETURN;
2173
2174 END IF; -- if processing_type = 'PRINTED'
2175
2176 /*
2177 * If we reached here, return success.
2178 */
2179 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2180 print_debuginfo(l_module_name, 'Returning success response ..');
2181 END IF;
2182 x_return_status := 0;
2183 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2184 print_debuginfo(l_module_name, 'EXIT');
2185
2186 END IF;
2187 END processPaperPayments;
2188
2189 /*--------------------------------------------------------------------
2190 | NAME:
2191 | getDocumentCountForPayment
2192 |
2193 | PURPOSE:
2194 |
2195 |
2196 |
2197 | PARAMETERS:
2198 | IN
2199 |
2200 | OUT
2201 |
2202 |
2203 | RETURNS:
2204 |
2205 | NOTES:
2206 |
2207 *---------------------------------------------------------------------*/
2208 FUNCTION getDocumentCountForPayment(
2209 p_payment_id IN IBY_PAYMENTS_ALL.payment_id%TYPE,
2210 p_pmtsInPmtInstrTab IN IBY_PAYINSTR_PUB.pmtsInpmtInstrTabType
2211 )
2212 RETURN NUMBER
2213 IS
2214 l_doc_count NUMBER := -1;
2215
2216 BEGIN
2217
2218 FOR i in p_pmtsInPmtInstrTab.FIRST .. p_pmtsInPmtInstrTab.LAST LOOP
2219
2220 IF (p_pmtsInPmtInstrTab(i).payment_id = p_payment_id) THEN
2221
2222 l_doc_count := p_pmtsInPmtInstrTab(i).document_count;
2223 EXIT;
2224
2225 END IF;
2226
2227 END LOOP;
2228
2229 RETURN l_doc_count;
2230
2231 END getDocumentCountForPayment;
2232
2233 /*--------------------------------------------------------------------
2234 | NAME:
2235 | assignCheckNumbers
2236 |
2237 | PURPOSE:
2238 |
2239 |
2240 |
2241 | PARAMETERS:
2242 | IN
2243 |
2244 |
2245 | OUT
2246 |
2247 |
2248 | RETURNS:
2249 |
2250 |
2251 | NOTES:
2252 | This method will perform a COMMIT.
2253 |
2254 *---------------------------------------------------------------------*/
2255 PROCEDURE assignCheckNumbers(
2256 x_pmtInstrRec IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE,
2257 p_payment_doc_id IN CE_PAYMENT_DOCUMENTS.payment_document_id%TYPE,
2258 p_user_assgn_num IN IBY_PAYMENTS_ALL.paper_document_number%TYPE,
2259 x_paperPmtsTab IN OUT NOCOPY paperPmtsSpecialDocsTabType,
2260 x_dummyPaperPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
2261 x_instrErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
2262 x_insTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
2263 trxnErrTokenTabType,
2264 x_return_message IN OUT NOCOPY VARCHAR2,
2265 x_return_status IN OUT NOCOPY NUMBER,
2266 x_msg_count IN OUT NOCOPY NUMBER,
2267 x_msg_data IN OUT NOCOPY VARCHAR2
2268 )
2269 IS
2270
2271 l_paper_pmts_count NUMBER := 0;
2272 l_last_used_check_num NUMBER := 0;
2273 l_last_avail_check_num NUMBER := 0;
2274 l_physical_stock_count NUMBER := 0;
2275 l_anticipated_last_check_num NUMBER := 0;
2276
2277 l_pmt_doc_name VARCHAR2(200) := '';
2278 l_pmt_instr_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE;
2279
2280 l_error_code IBY_TRANSACTION_ERRORS.error_code%TYPE;
2281 l_instr_err_rec IBY_TRANSACTION_ERRORS%ROWTYPE;
2282 l_token_rec IBY_TRXN_ERROR_TOKENS%ROWTYPE;
2283
2284 l_send_to_file_flag VARCHAR2(1);
2285 l_instr_status IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_status%TYPE;
2286
2287 l_single_pmt_flag BOOLEAN;
2288 l_nos_avlbl_flag BOOLEAN;
2289 l_used_flag BOOLEAN;
2290
2291 l_module_name VARCHAR2(200) := G_PKG_NAME || '.assignCheckNumbers';
2292
2293 BEGIN
2294
2295 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2296 print_debuginfo(l_module_name, 'ENTER');
2297
2298 END IF;
2299 /*
2300 * Total number of paper payments is sum of real paper payments
2301 * and dummy paper payments.
2302 */
2303 l_paper_pmts_count := x_paperPmtsTab.COUNT + x_dummyPaperPmtsTab.COUNT;
2304
2305 /* should never come into if, but just in case */
2306 IF (l_paper_pmts_count = 0) THEN
2307
2308 /*
2309 * Shouldn't come here. This method was called because there
2310 * was atleast one payment instruction with processing type
2311 * 'PAPER'. This implies that there should be at least one
2312 * payment instruction with processing type 'PAPER'.
2313 *
2314 * If no such payment exists, about the program.
2315 */
2316 --IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2317 print_debuginfo(l_module_name, 'Total # of paper payments '
2318 || 'is 0. Possible data corruption. Aborting ..'
2319 );
2320
2321 print_debuginfo(l_module_name, 'EXIT');
2322 --END IF;
2323 x_return_status := -1;
2324
2325 l_error_code := 'IBY_INS_PMTS_NOT_FOUND';
2326 FND_MESSAGE.set_name('IBY', l_error_code);
2327
2328 FND_MESSAGE.SET_TOKEN('INS_ID',
2329 x_pmtInstrRec.payment_instruction_id,
2330 FALSE);
2331
2332 FND_MSG_PUB.ADD;
2333
2334 FND_MSG_PUB.COUNT_AND_GET(
2335 p_count => x_msg_count,
2336 p_data => x_msg_data
2337 );
2338
2339 x_return_message := FND_MESSAGE.get;
2340
2341 RETURN;
2342
2343 ELSE
2344
2345 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2346 print_debuginfo(l_module_name, 'Total # of paper payments: '
2347 || l_paper_pmts_count
2348 );
2349
2350 END IF;
2351 END IF;
2352
2353 /*
2354 * When this printed payment instruction is formatted, the
2355 * output can be sent to any of two places:
2356 *
2357 * a. To the printer
2358 * b. To a file (for printing outside the Oracle system).
2359 *
2360 * The status of the payment instruction needs to be adjusted
2361 * as per this destination flag.
2362 */
2363 BEGIN
2364
2365 SELECT
2366 send_to_file_flag
2367 INTO
2368 l_send_to_file_flag
2369 FROM
2370 IBY_PAYMENT_PROFILES
2371 WHERE
2372 payment_profile_id = x_pmtInstrRec.payment_profile_id
2373 ;
2374
2375 EXCEPTION
2376 WHEN OTHERS THEN
2377
2378 --IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2379 print_debuginfo(l_module_name, 'Fatal: Exception when '
2380 || 'attempting to get "send to file flag" for payment '
2381 || 'instruction '
2382 || x_pmtInstrRec.payment_instruction_id
2383 || ' with payment profile id '
2384 || x_pmtInstrRec.payment_profile_id
2385 );
2386
2387 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
2388 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
2389
2390 print_debuginfo(l_module_name, 'Aborting numbering process ..');
2391
2392 --END IF;
2393 x_return_status := -1;
2394
2395 l_error_code := 'IBY_INS_PROF_EXCEP';
2396 FND_MESSAGE.set_name('IBY', l_error_code);
2397
2398 FND_MESSAGE.SET_TOKEN('INS_ID',
2399 x_pmtInstrRec.payment_instruction_id,
2400 FALSE);
2401
2402 FND_MESSAGE.SET_TOKEN('PROF_ID',
2403 x_pmtInstrRec.payment_profile_id,
2404 FALSE);
2405
2406 FND_MSG_PUB.ADD;
2407
2408 FND_MSG_PUB.COUNT_AND_GET(
2409 p_count => x_msg_count,
2410 p_data => x_msg_data
2411 );
2412
2413 x_return_message := FND_MESSAGE.get;
2414
2415 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2416 print_debuginfo(l_module_name, 'EXIT');
2417
2418 END IF;
2419 RETURN;
2420
2421 END;
2422
2423 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2424 print_debuginfo(l_module_name, 'For payment instruction '
2425 || x_pmtInstrRec.payment_instruction_id
2426 || ' with payment profile id '
2427 || x_pmtInstrRec.payment_profile_id
2428 || ', send to file flag: '
2429 || l_send_to_file_flag
2430 );
2431
2432 END IF;
2433 /*
2434 * The payment instruction status is dependant upon
2435 * the 'send to file' flag. Preset the instruction
2436 * status appropriately.
2437 */
2438 IF (UPPER(l_send_to_file_flag) = 'Y') THEN
2439 l_instr_status := INS_STATUS_READY_TO_FORMAT;
2440 ELSE
2441 l_instr_status := INS_STATUS_READY_TO_PRINT;
2442 END IF;
2443
2444 /*
2445 * Check whether the provided payments list consists
2446 * of a single payment.
2447 */
2448 l_single_pmt_flag := isSinglePayment(x_paperPmtsTab);
2449
2450 /*
2451 * IMPORTANT NOTE:
2452 * Irrespective of whether this payment instruction
2453 * could be numbered successfully or not, we will
2454 * change the status of the payment instruction at
2455 * this point from 'CREATED' -> 'CREATED_READY_FOR_PRINTING'
2456 * or 'CREATED_READY_FOR_FORMATTING'.
2457 *
2458 * In case, the payments could be numbered successfully,
2459 * the new payment instruction status becomes a transient
2460 * status (because the format will immediately change the
2461 * payment status to 'FORMATTED').
2462 *
2463 * In case, the numbering failed for some reason, or some
2464 * other exception was thrown, the payment instruction
2465 * will remain in the new status. This will be sufficient
2466 * for the UI to recognize that the payment instruction
2467 * did not finish the numbering operation successfully.
2468 *
2469 * Therefore, the UI will call the check numering flow
2470 * again, and we will have another go at numbering this
2471 * payment instruction.
2472 */
2473
2474 /*
2475 * Blindly update the payment instruction status
2476 * (see above comment).
2477 */
2478 UPDATE
2479 IBY_PAY_INSTRUCTIONS_ALL
2480 SET
2481 payment_instruction_status = l_instr_status
2482 WHERE
2483 payment_instruction_id = x_pmtInstrRec.payment_instruction_id;
2484
2485 /*
2486 * For single payments, do not perform any commits because
2487 * single payments API is session based and only the caller
2488 * decides to commit / not commit.
2489 */
2490 IF (l_single_pmt_flag <> TRUE) THEN
2491
2492 /*
2493 * This commit is needed so that in case of any exceptions
2494 * in this method (e.g., payment document locked), the
2495 * payment instruction status is changed from 'CREATED'
2496 * to the next valid status.
2497 *
2498 * Payments in 'CREATED' status are not visible in the UI,
2499 * this is because 'CREATED' is a transient status.
2500 */
2501 COMMIT;
2502
2503 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2504 print_debuginfo(l_module_name, 'Payment instruction '
2505 || x_pmtInstrRec.payment_instruction_id
2506 || ' status committed to '
2507 || l_instr_status
2508 || ' before numbering.'
2509 );
2510
2511 END IF;
2512 END IF;
2513
2514 /*
2515 * Pull up the details of the paper stock, like the
2516 * last used check number and the last available
2517 * check number.
2518 *
2519 * Note: This SELECT will lock the underlying base
2520 * table IBY_PAYMENT_DOCUMENTS_B. We need to lock this
2521 * table because we need to update the last_document_number.
2522 */
2523 SELECT
2524 payment_document_name,
2525 payment_instruction_id
2526 INTO
2527 l_pmt_doc_name,
2528 l_pmt_instr_id
2529 FROM
2530 CE_PAYMENT_DOCUMENTS
2531 WHERE
2532 payment_document_id = p_payment_doc_id
2533 ;
2534
2535
2536 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2537 print_debuginfo(l_module_name, 'Got the payment document name');
2538
2539 END IF;
2540 /*
2541 *
2542 * Pull up the details of the paper stock, like the
2543 * last used check number and the last available
2544 * check number.
2545 *
2546 * Note: This SELECT will lock the underlying base
2547 * table IBY_PAYMENT_DOCUMENTS_B. We need to lock this
2548 * table because we need to update the last_document_number.
2549 *
2550 * If document is already locked for single payment,
2551 * NO_DATA_FOUND exception would be thrown.
2552 * Bug - 7499044
2553 */
2554 BEGIN
2555 SELECT
2556 NVL(last_issued_document_number, 0),
2557 NVL(last_available_document_number, -1),
2558 payment_document_name,
2559 payment_instruction_id
2560 INTO
2561 l_last_used_check_num,
2562 l_last_avail_check_num,
2563 l_pmt_doc_name,
2564 l_pmt_instr_id
2565 FROM
2566 CE_PAYMENT_DOCUMENTS
2567 WHERE
2568 payment_document_id = p_payment_doc_id
2569 FOR UPDATE SKIP LOCKED
2570 ;
2571 EXCEPTION
2572 WHEN NO_DATA_FOUND THEN
2573 --IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2574 print_debuginfo(l_module_name, 'Payment document '
2575 || ''''
2576 || l_pmt_doc_name
2577 || ''''
2578 || ' with payment doc id '
2579 || p_payment_doc_id
2580 || ' has been locked from payments workbench ',
2581 FND_LOG.LEVEL_UNEXPECTED
2582 );
2583
2584 print_debuginfo(l_module_name, 'Processing cannot continue '
2585 || 'because payment document is unavailable (locked).',
2586 FND_LOG.LEVEL_UNEXPECTED
2587 );
2588
2589 print_debuginfo(l_module_name, 'Changing the status of the '
2590 || 'payment instruction to '
2591 || l_instr_status
2592 );
2593
2594 --END IF;
2595 /*
2596 * Fix for bug 5735030:
2597 *
2598 * Populate error message in output file so that
2599 * the user knows the cause of the failure even
2600 * if logging is turned off.
2601 */
2602 l_error_code := 'IBY_PMT_DOC_SING_LOCKED';
2603 FND_MESSAGE.SET_NAME('IBY', l_error_code);
2604
2605 FND_MESSAGE.SET_TOKEN('DOC_NAME',
2606 l_pmt_doc_name,
2607 FALSE);
2608
2609 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
2610
2611 /*
2612 * Return failure status.
2613 */
2614 x_return_status := -1;
2615
2616 l_error_code := 'IBY_PMT_DOC_SING_LOCKED';
2617 FND_MESSAGE.set_name('IBY', l_error_code);
2618
2619 FND_MESSAGE.SET_TOKEN('DOC_NAME',
2620 l_pmt_doc_name,
2621 FALSE);
2622
2623 FND_MSG_PUB.ADD;
2624
2625 FND_MSG_PUB.COUNT_AND_GET(
2626 p_count => x_msg_count,
2627 p_data => x_msg_data
2628 );
2629
2630 x_return_message := FND_MESSAGE.get;
2631
2632 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2633 print_debuginfo(l_module_name, 'EXIT');
2634 END IF;
2635 RETURN;
2636
2637
2638 END;
2639 IF (l_pmt_instr_id IS NOT NULL) THEN
2640 --IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2641 print_debuginfo(l_module_name, 'Payment document '
2642 || ''''
2643 || l_pmt_doc_name
2644 || ''''
2645 || ' with payment doc id '
2646 || p_payment_doc_id
2647 || ' has been locked by payment instruction '
2648 || l_pmt_instr_id,
2649 FND_LOG.LEVEL_UNEXPECTED
2650 );
2651
2652 print_debuginfo(l_module_name, 'Processing cannot continue '
2653 || 'because payment document is unavailable (locked).',
2654 FND_LOG.LEVEL_UNEXPECTED
2655 );
2656
2657 print_debuginfo(l_module_name, 'Changing the status of the '
2658 || 'payment instruction to '
2659 || l_instr_status
2660 );
2661
2662 --END IF;
2663 /*
2664 * Fix for bug 5735030:
2665 *
2666 * Populate error message in output file so that
2667 * the user knows the cause of the failure even
2668 * if logging is turned off.
2669 */
2670 l_error_code := 'IBY_INS_PMT_DOC_LOCKED_DETAIL';
2671 FND_MESSAGE.SET_NAME('IBY', l_error_code);
2672
2673 FND_MESSAGE.SET_TOKEN('THIS_INS_NUM',
2674 x_pmtInstrRec.payment_instruction_id,
2675 FALSE);
2676
2677 FND_MESSAGE.SET_TOKEN('PREV_INS_NUM',
2678 l_pmt_instr_id,
2679 FALSE);
2680
2681 FND_MESSAGE.SET_TOKEN('DOC_NAME',
2682 l_pmt_doc_name,
2683 FALSE);
2684
2685 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
2686
2687 /*
2688 * Return failure status.
2689 */
2690 x_return_status := -1;
2691
2692 l_error_code := 'IBY_INS_PMT_DOC_LOCKED';
2693 FND_MESSAGE.set_name('IBY', l_error_code);
2694
2695 FND_MESSAGE.SET_TOKEN('INS_ID',
2696 l_pmt_instr_id,
2697 FALSE);
2698
2699 FND_MSG_PUB.ADD;
2700
2701 FND_MSG_PUB.COUNT_AND_GET(
2702 p_count => x_msg_count,
2703 p_data => x_msg_data
2704 );
2705
2706 x_return_message := FND_MESSAGE.get;
2707
2708 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2709 print_debuginfo(l_module_name, 'EXIT');
2710 END IF;
2711 RETURN;
2712
2713 END IF;
2714
2715 /*
2716 * Log warnings if there is any missing/incomplete information.
2717 */
2718 IF (l_last_avail_check_num = -1) THEN
2719
2720 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2721 print_debuginfo(l_module_name, 'Warning: payment document id '
2722 || p_payment_doc_id
2723 || ' has no last available document number set. '
2724 || 'Assuming that infinite number of paper documents '
2725 || 'can be printed for this payment document.'
2726 );
2727
2728 END IF;
2729 END IF;
2730
2731 IF (l_last_used_check_num = 0) THEN
2732
2733 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2734 print_debuginfo(l_module_name, 'Warning: payment document id '
2735 || p_payment_doc_id
2736 || ' has last used document number set to zero. '
2737 || 'Assuming that no paper documents have yet '
2738 || 'been printed for this payment document.'
2739 );
2740
2741 END IF;
2742 END IF;
2743
2744
2745 /*
2746 * If user has explicitly provided a start number for check
2747 * numbering, we have to use it in our numbering logic.
2748 * This will only happen for single payments.
2749 *
2750 * For Build Program invoked numbering, we will always start
2751 * from the last issued check number on the payment document + 1.
2752 */
2753 IF (p_user_assgn_num IS NULL) THEN
2754
2755 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2756 print_debuginfo(l_module_name, 'User has not explicitly '
2757 || 'provided a check number to start numbering from. '
2758 || 'Numbering will start from last issued check number '
2759 || 'on check stock.'
2760 );
2761
2762 END IF;
2763 ELSE
2764
2765 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2766 print_debuginfo(l_module_name, 'User has explicitly '
2767 || 'provided start number for numbering: '
2768 || p_user_assgn_num
2769 );
2770
2771 END IF;
2772 /*
2773 * The code below uses the variable 'l_last_used_check_num'
2774 * as the starting number for check numbering. The numbering
2775 * will begin from l_last_used_check_num + 1.
2776 *
2777 * If the user has explicitly provided a start number for
2778 * numbering, we need to adjust the l_last_used_check_num
2779 * value accordingly.
2780 */
2781 l_last_used_check_num := p_user_assgn_num - 1;
2782
2783 END IF;
2784
2785 /*
2786 * Check if enough paper documents are available to complete
2787 * this payment instruction.
2788 *
2789 * Perform this check only if a value has been provided
2790 * for the last available document number. If no value is
2791 * set assume that an infinite number of checks can be
2792 * printed for this paper stock (payment document).
2793 */
2794 IF (l_last_avail_check_num <> -1) THEN
2795
2796 /*
2797 * Check if enough paper documents are available to complete
2798 * this payment instruction.
2799 */
2800 l_physical_stock_count := l_last_avail_check_num
2801 - l_last_used_check_num;
2802
2803 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2804 print_debuginfo(l_module_name, 'Available paper stock = '
2805 || l_physical_stock_count
2806 || ' for payment document name '
2807 || ''''
2808 || l_pmt_doc_name
2809 || ''''
2810 );
2811
2812 END IF;
2813 IF (l_physical_stock_count < l_paper_pmts_count) THEN
2814
2815 /*
2816 * Not enough paper stock is available to print
2817 * the checks for this payment instruction.
2818 *
2819 * Set the status of the payment instruction to
2820 * failed.
2821 */
2822
2823 print_debuginfo(l_module_name, 'Deferring payment '
2824 || 'instruction print '
2825 || x_pmtInstrRec.payment_instruction_id
2826 || ' because of insufficient paper stock.',
2827 FND_LOG.LEVEL_UNEXPECTED
2828 );
2829
2830
2831 x_pmtInstrRec.payment_instruction_status := l_instr_status;
2832
2833 l_error_code := 'IBY_INS_INSUFFICIENT_PAY_DOCS';
2834
2835 FND_MESSAGE.set_name('IBY', l_error_code);
2836
2837 FND_MESSAGE.SET_TOKEN('NUM_AVAIL',
2838 l_physical_stock_count,
2839 FALSE);
2840
2841 l_token_rec.token_name := 'NUM_AVAIL';
2842 l_token_rec.token_value := l_physical_stock_count;
2843 x_insTokenTab(x_insTokenTab.COUNT + 1) := l_token_rec;
2844
2845 FND_MESSAGE.SET_TOKEN('NUM_REQD',
2846 l_paper_pmts_count,
2847 FALSE);
2848
2849 l_token_rec.token_name := 'NUM_REQD';
2850 l_token_rec.token_value := l_paper_pmts_count;
2851 x_insTokenTab(x_insTokenTab.COUNT + 1) := l_token_rec;
2852
2853 /*
2854 * Once we fail a pmt instruction, we must add a
2855 * corresponding error message to the errors table.
2856 */
2857 IBY_PAYINSTR_UTILS_PKG.createErrorRecord(
2858 x_pmtInstrRec.payment_instruction_id,
2859 x_pmtInstrRec.payment_instruction_status,
2860 l_error_code,
2861 FND_MESSAGE.get,
2862 'N',
2863 l_instr_err_rec
2864 );
2865
2866 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
2867 l_instr_err_rec,
2868 x_instrErrorTab,
2869 x_insTokenTab
2870 );
2871
2872 /* add error message to msg stack */
2873 FND_MSG_PUB.ADD;
2874
2875 FND_MSG_PUB.COUNT_AND_GET(
2876 p_count => x_msg_count,
2877 p_data => x_msg_data
2878 );
2879
2880 /* set error message to return to caller */
2881 x_return_message := FND_MESSAGE.get;
2882
2883 /*
2884 * Now, raise an exception. This will be caught
2885 * in the exception handler below and the changes
2886 * made to the DB in this transaction
2887 * will be rolled back.
2888 */
2889 APP_EXCEPTION.RAISE_EXCEPTION;
2890
2891 ELSE
2892
2893 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2894 print_debuginfo(l_module_name, 'Sufficient paper stock '
2895 || 'is available to print this instruction.'
2896 );
2897
2898 END IF;
2899 END IF;
2900
2901 END IF; -- l_last_avail_check_num <> -1
2902
2903 /*
2904 * If sufficient paper stock is available, we will be using
2905 * up the paper stock by assigning it to the available
2906 * paper payments. Therefore, update the last used paper
2907 * stock number in CE_PAYMENT_DOCUMENTS.
2908 *
2909 * That way if another instance of the payment instruction
2910 * creation program is operating concurrently, it will
2911 * be blocked by the SELECT .. FOR UPDATE statement in
2912 * this method.
2913 *
2914 */
2915 l_anticipated_last_check_num := l_last_used_check_num
2916 + l_paper_pmts_count;
2917
2918 /*
2919 * We will be printing the checks starting with
2920 * paper doc num 'l_last_used_check_num + 1' and
2921 * ending with paper doc num l_anticipated_last_check_num.
2922 *
2923 * Check whether all the paper doc numbers within this
2924 * range are available. We cannot have any gaps in the
2925 * numbering because checks have to be numbered
2926 * contiguously.
2927 */
2928 l_nos_avlbl_flag := isContigPaperNumAvlbl(
2929 p_payment_doc_id,
2930 l_last_used_check_num + 1,
2931 l_anticipated_last_check_num
2932 );
2933
2934 IF (l_nos_avlbl_flag = FALSE) THEN
2935
2936 --IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2937 print_debuginfo(l_module_name, 'Contiguous paper stock '
2938 || 'is not available for printing payment instruction '
2939 || x_pmtInstrRec.payment_instruction_id
2940 );
2941
2942 --END IF;
2943 /*
2944 * Return failure status.
2945 */
2946 x_return_status := -1;
2947
2948 l_error_code := 'IBY_INS_NSF_CONTIG_NUM';
2949 FND_MESSAGE.set_name('IBY', l_error_code);
2950
2951 FND_MESSAGE.SET_TOKEN('NUM_PMT_DOCS',
2952 l_paper_pmts_count,
2953 FALSE);
2954
2955 FND_MSG_PUB.ADD;
2956
2957 FND_MSG_PUB.COUNT_AND_GET(
2958 p_count => x_msg_count,
2959 p_data => x_msg_data
2960 );
2961
2962 x_return_message := FND_MESSAGE.get;
2963
2964 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2965 print_debuginfo(l_module_name, 'EXIT');
2966 END IF;
2967 RETURN;
2968
2969 END IF;
2970
2971 /*
2972 * A paper document number (check number) is considered
2973 * unused if it is not present in IBY_USED_PAYMENT_DOCS
2974 * table.
2975 *
2976 * This logic will work when check numbering is invoked
2977 * from the Build Program. In this case, the numbering
2978 * logic always starts with the last issued doc number + 1
2979 * when assigning new check numbers. Therefore, the
2980 * check numbers will always be unique (unused).
2981 *
2982 * However, when check numbering is invoked for single
2983 * payments, the user is allowed to provide the start
2984 * number for check numbering. It is possible that
2985 * a payment has already been numbered with the user
2986 * provided start number, but this paper document may
2987 * not yet have been inserted into the IBY_USED_PAYMENT_DOCS
2988 * table (because the user has not yet confirmed the
2989 * payment).
2990 *
2991 * Therefore, for single payments, when the user provides
2992 * the start number for check numbering, we will have to
2993 * verify that the provided number is unused by checking
2994 * the paper document number on existing payments.
2995 */
2996 IF (p_user_assgn_num IS NOT NULL) THEN
2997
2998 l_used_flag := isPaperNosUsedOnExistPmt(
2999 p_payment_doc_id,
3000 l_last_used_check_num + 1,
3001 l_anticipated_last_check_num);
3002
3003 IF (l_used_flag = TRUE) THEN
3004
3005 --IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3006 print_debuginfo(l_module_name, 'Paper document number(s) '
3007 || 'generated after numbering are invalid (already used). '
3008 || 'User needs to provide a new start number or use '
3009 || 'the defaulted start number.'
3010 );
3011
3012 --END IF;
3013 /*
3014 * Return failure status.
3015 */
3016 x_return_status := -1;
3017
3018 l_error_code := 'IBY_INS_ALREADY_USED_NUM';
3019 FND_MESSAGE.set_name('IBY', l_error_code);
3020
3021 FND_MSG_PUB.ADD;
3022
3023 FND_MSG_PUB.COUNT_AND_GET(
3024 p_count => x_msg_count,
3025 p_data => x_msg_data
3026 );
3027
3028 x_return_message := FND_MESSAGE.get;
3029
3030 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3031 print_debuginfo(l_module_name, 'EXIT');
3032 END IF;
3033 RETURN;
3034
3035 ELSE
3036
3037 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3038 print_debuginfo(l_module_name, 'Paper document number(s) '
3039 || 'generated after numbering are unused. '
3040 );
3041
3042 END IF;
3043 END IF;
3044
3045 END IF;
3046
3047 /*
3048 * For single payments, the payment document should
3049 * not be locked (see bug 4597718).
3050 */
3051 IF (l_single_pmt_flag = TRUE) THEN
3052
3053 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3054 print_debuginfo(l_module_name, 'This is a single payment. '
3055 || 'Payment document will not be locked ..'
3056 );
3057
3058 END IF;
3059 /*
3060 * Update the check stock to reflect the latest used
3061 * check number.
3062 */
3063 UPDATE
3064 CE_PAYMENT_DOCUMENTS
3065 SET
3066 last_issued_document_number = l_anticipated_last_check_num
3067 WHERE
3068 payment_document_id = p_payment_doc_id
3069 AND
3070 last_issued_document_number < l_anticipated_last_check_num
3071 ;
3072
3073 ELSE
3074
3075 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3076 print_debuginfo(l_module_name, 'This is not a single payment. '
3077 || 'Payment document will be locked ..'
3078 );
3079
3080 END IF;
3081 /*
3082 * Update the check stock to reflect the latest used
3083 * check number, and lock the check stock.
3084 */
3085 UPDATE
3086 CE_PAYMENT_DOCUMENTS
3087 SET
3088 last_issued_document_number = l_anticipated_last_check_num,
3089 payment_instruction_id = x_pmtInstrRec.payment_instruction_id
3090 WHERE
3091 payment_document_id = p_payment_doc_id
3092
3093 ;
3094
3095 END IF;
3096
3097 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3098 print_debuginfo(l_module_name, 'Finished updating the last '
3099 || 'available check number in CE_PAYMENT_DOCUMENTS. '
3100 || 'Current last check number: '
3101 || l_anticipated_last_check_num
3102 );
3103
3104 END IF;
3105 /* uncomment for debug purposes */
3106 --print_debuginfo(l_module_name, 'x_dummyPaperPmtsTab.COUNT: '
3107 -- || x_dummyPaperPmtsTab.COUNT);
3108 --print_debuginfo(l_module_name, 'x_paperPmtsTab.COUNT: '
3109 -- || x_paperPmtsTab.COUNT);
3110
3111 /*
3112 * Assign contiguous check numbers to the setup checks.
3113 * These are dummy checks that are printed at the
3114 * beginning of the payment instruction print run.
3115 */
3116 IF (x_dummyPaperPmtsTab.COUNT <> 0) THEN
3117
3118 FOR i in x_dummyPaperPmtsTab.FIRST .. x_dummyPaperPmtsTab.LAST LOOP
3119
3120 IF (x_dummyPaperPmtsTab(i).payment_status = 'VOID_BY_SETUP') THEN
3121
3122 l_last_used_check_num := l_last_used_check_num + 1;
3123 x_dummyPaperPmtsTab(i).paper_document_number
3124 := l_last_used_check_num;
3125
3126 END IF;
3127
3128 END LOOP; -- for all setup payments in x_dummyPaperPmtsTab
3129
3130 END IF;
3131
3132 /* handle real checks and overflow checks here */
3133 FOR i in x_paperPmtsTab.FIRST .. x_paperPmtsTab.LAST LOOP
3134
3135 /*
3136 * We must assign the check numbers in sequence
3137 * so that when a paper payment is printed, all the
3138 * paper documents for that payment have contiguous
3139 * number.
3140 */
3141
3142
3143 /*
3144 * If this payment has any overflow documents, then
3145 * some overflow payments would have been created.
3146 * Assign check numbers to these overflow payments
3147 * too.
3148 *
3149 * Overflow payments are created one-by-one and are
3150 * assigned incremental payment ids. So no need to sort
3151 * these overflow payments (they are already sorted).
3152 *
3153 * Simply find the overflow payments related to the real
3154 * payment and assign them with contiguous check numbers.
3155 */
3156 IF (x_dummyPaperPmtsTab.COUNT <> 0) THEN
3157
3158 FOR j in x_dummyPaperPmtsTab.FIRST .. x_dummyPaperPmtsTab.LAST LOOP
3159
3160 /*
3161 * The external_bank_account_id field actually contains
3162 * the original payment id (the payment that originally
3163 * contained the documents payable stored in the dummy
3164 * overflow payment).
3165 *
3166 * See KLUDGE in performSpecialPaperHandling()
3167 * method to see why this was done.
3168 */
3169 IF (x_dummyPaperPmtsTab(j).payment_status = 'VOID_BY_OVERFLOW')
3170 THEN
3171
3172 IF (x_dummyPaperPmtsTab(j).external_bank_account_id
3173 = x_paperPmtsTab(i).payment_id) THEN
3174
3175 l_last_used_check_num := l_last_used_check_num + 1;
3176 x_dummyPaperPmtsTab(j).paper_document_number
3177 := l_last_used_check_num;
3178
3179 END IF;
3180
3181 END IF;
3182
3183 END LOOP; -- for all overflow pmts in x_dummyPaperPmtsTab
3184
3185 END IF;
3186
3187 /* Bug 7252846 - priting of overflow and original payments must be such that, the overflow payments must be
3188 printed first, followed by original ones. In this case, the numbering should happen first for overflow payments
3189 followed by original ones */
3190
3191 /* assign check number to paper payment */
3192 l_last_used_check_num := l_last_used_check_num + 1;
3193 x_paperPmtsTab(i).check_number := l_last_used_check_num;
3194
3195
3196 END LOOP; -- for all pmts in x_paperPmtsTab
3197
3198 /*
3199 * Final check:
3200 *
3201 * If all paper payments (including real payments, setup payments
3202 * and overflow payments) have been assigned check numbers
3203 * correctly, then the number of check numbers used up should
3204 * match the total paper payments count.
3205 *
3206 * If the two don't match, it means that some check numbers were
3207 * unassigned, or multiply assigned. In either case, abort the
3208 * program. This check will reveal any bugs in this method.
3209 */
3210 IF (l_anticipated_last_check_num <> l_last_used_check_num) THEN
3211
3212
3213 print_debuginfo(l_module_name, 'Check numbers were not '
3214 || 'properly assigned. '
3215 || 'Anticipated last used check number: '
3216 || l_anticipated_last_check_num
3217 || '. Actual last used check number: '
3218 || l_last_used_check_num
3219 || '. Deferring print for payment instruction '
3220 || x_pmtInstrRec.payment_instruction_id,
3221 FND_LOG.LEVEL_UNEXPECTED
3222 );
3223
3224
3225 x_pmtInstrRec.payment_instruction_status := l_instr_status;
3226
3227 l_error_code := 'IBY_INS_NUMBERING_ERR_1';
3228
3229 FND_MESSAGE.set_name('IBY', l_error_code);
3230
3231 FND_MESSAGE.SET_TOKEN('NUM_CALC',
3232 l_anticipated_last_check_num,
3233 FALSE);
3234
3235 l_token_rec.token_name := 'NUM_CALC';
3236 l_token_rec.token_value := l_anticipated_last_check_num;
3237 x_insTokenTab(x_insTokenTab.COUNT + 1) := l_token_rec;
3238
3239 FND_MESSAGE.SET_TOKEN('NUM_ACTU',
3240 l_last_used_check_num,
3241 FALSE);
3242
3243 l_token_rec.token_name := 'NUM_ACTU';
3244 l_token_rec.token_value := l_last_used_check_num;
3245 x_insTokenTab(x_insTokenTab.COUNT + 1) := l_token_rec;
3246
3247 /*
3248 * Once we fail a pmt instruction, we must add a
3249 * corresponding error message to the errors table.
3250 */
3251 IBY_PAYINSTR_UTILS_PKG.createErrorRecord(
3252 x_pmtInstrRec.payment_instruction_id,
3253 x_pmtInstrRec.payment_instruction_status,
3254 l_error_code,
3255 FND_MESSAGE.get,
3256 'N',
3257 l_instr_err_rec
3258 );
3259
3260 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
3261 l_instr_err_rec,
3262 x_instrErrorTab,
3263 x_insTokenTab
3264 );
3265
3266 /* add error msg to message stack */
3267 FND_MSG_PUB.ADD;
3268
3269 FND_MSG_PUB.COUNT_AND_GET(
3270 p_count => x_msg_count,
3271 p_data => x_msg_data
3272 );
3273
3274 /* set error message to return to caller */
3275 x_return_message := FND_MESSAGE.get;
3276
3277 APP_EXCEPTION.RAISE_EXCEPTION;
3278
3279 END IF;
3280
3281 /*
3282 * Return success status.
3283 */
3284 x_return_message := 'SUCCESS';
3285 x_return_status := 0;
3286
3287 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3288 print_debuginfo(l_module_name, 'EXIT');
3289
3290 END IF;
3291 EXCEPTION
3292 WHEN OTHERS THEN
3293
3294 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3295 print_debuginfo(l_module_name, 'Exception occured when '
3296 || 'performing document numbering. '
3297 );
3298 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
3299 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
3300
3301 END IF;
3302 /*
3303 * Rollback any DB changes made in this method.
3304 */
3305 ROLLBACK;
3306
3307 /*
3308 * Return error status to caller.
3309 * The error message would have already been set.
3310 */
3311 x_return_status := -1;
3312
3313 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3314 print_debuginfo(l_module_name, 'EXIT');
3315
3316 END IF;
3317 END assignCheckNumbers;
3318
3319 /*--------------------------------------------------------------------
3320 | NAME:
3321 | getNumPrintedDocsByFormula
3322 |
3323 | PURPOSE:
3324 |
3325 |
3326 | PARAMETERS:
3327 | IN
3328 |
3329 | OUT
3330 |
3331 |
3332 | RETURNS:
3333 |
3334 | NOTES:
3335 |
3336 *---------------------------------------------------------------------*/
3337 FUNCTION getNumPrintedDocsByFormula(
3338 p_num_docs_payable IN NUMBER,
3339 p_num_lines_per_stub IN NUMBER
3340 )
3341 RETURN NUMBER
3342 IS
3343
3344 l_num_printed_docs NUMBER := -1;
3345 l_carryover NUMBER := -1;
3346 l_extra_printed_doc NUMBER := -1;
3347
3348 BEGIN
3349
3350 /*
3351 * We will try to fit n docs payable into m stubs, where
3352 * p = number of lines per stub. The problem is to calculate
3353 * m, given n and p.
3354 *
3355 * Now, number of stubs (i.e., printed docs) will be at least n/p
3356 *
3357 * Therefore, m >= n/p
3358 *
3359 * If n/p division is not exact, it means that some docs payable
3360 * are left over. Now, the number of docs payable left over (i.e.,
3361 * the reminder) is necessarily less than p, because reminder is
3362 * always less than the divisor.
3363 *
3364 * So, the reminder docs payable will definitely fit into one stub.
3365 *
3366 * Therefore, m = n/p + r
3367 *
3368 * where r = 0, if the reminder is zero i.e., n mod p = 0
3369 * else r = 1, if the reminder is non-zero i.e., n mod p > 0
3370 */
3371
3372 l_carryover := MOD(p_num_docs_payable, p_num_lines_per_stub);
3373
3374 IF (l_carryover <> 0) THEN
3375 l_extra_printed_doc := 1;
3376 ELSE
3377 l_extra_printed_doc := 0;
3378 END IF;
3379
3380 l_num_printed_docs := FLOOR(p_num_docs_payable / p_num_lines_per_stub)
3381 + l_extra_printed_doc;
3382
3383 return l_num_printed_docs;
3384
3385 END getNumPrintedDocsByFormula;
3386
3387 /*--------------------------------------------------------------------
3388 | NAME:
3389 | performSpecialPaperDocHandling
3390 |
3391 | PURPOSE:
3392 |
3393 |
3394 |
3395 | PARAMETERS:
3396 | IN
3397 |
3398 |
3399 | OUT
3400 |
3401 |
3402 | RETURNS:
3403 |
3404 | NOTES:
3405 |
3406 *---------------------------------------------------------------------*/
3407 PROCEDURE performSpecialPaperDocHandling(
3408 x_paperSpecialTab IN OUT NOCOPY paperPmtsSpecialDocsTabType,
3409 x_dummyPaperPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
3410 x_setupDocsTab IN OUT NOCOPY docsTabType,
3411 x_overflowDocsTab IN OUT NOCOPY overflowDocsTabType
3412 )
3413 IS
3414 l_doc_rec iby_docs_payable_all%ROWTYPE;
3415 l_pmt_rec IBY_PAYMENTS_ALL%ROWTYPE;
3416
3417 l_payment_id IBY_PAYMENTS_ALL.payment_id%TYPE;
3418
3419 l_pmtsInPmtInstrRec IBY_PAYINSTR_PUB.pmtsInPmtInstrRecType;
3420
3421 --Bug 6486816
3422 -- Previously formatting_payment_id was updated for each document of payment but
3423 --after which x_overflowDocsTab was updates for next payment, losing the
3424 --previous values. So, storing all the overflow docs in a new table which will
3425 --be passed to calling function
3426
3427 x_overflowDocsTab_out overflowDocsTabType;
3428 l_begin_doc_index_outrec NUMBER := 1;
3429
3430 l_module_name VARCHAR2(200) := G_PKG_NAME
3431 || '.performSpecialPaperDocHandling';
3432 l_begin_doc_index NUMBER := 0;
3433 l_end_doc_index NUMBER := 0;
3434
3435 /* payments cursor */
3436 CURSOR c_document_data(p_payment_id IBY_DOCS_PAYABLE_ALL.payment_id%TYPE)
3437 IS
3438 SELECT
3439 document_payable_id,
3440 payment_id,
3441 payment_currency_code,
3442 payment_function,
3443 formatting_payment_id,
3444 org_id,
3445 org_type
3446 FROM
3447 IBY_DOCS_PAYABLE_ALL
3448 WHERE
3449 payment_id = p_payment_id
3450 ORDER BY
3451 document_payable_id ASC
3452 ;
3453
3454 BEGIN
3455
3456 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3457 print_debuginfo(l_module_name, 'ENTER');
3458
3459 END IF;
3460 /*
3461 * STEP 1:
3462 * Handle setup documents.
3463 *
3464 * Setup documents are dummy checks that are printed at the
3465 * beginning of a check print run. They are basically void checks
3466 * used to align the printer.
3467 *
3468 * Since each setup document is to be printed as a separate check
3469 * do the following:
3470 * a. Insert a dummy payment for each setup document that is to be
3471 * printed.
3472 * b. For each inserted dummy payment, insert as many dummy documents
3473 * payable as will fit onto the check stub of the setup document.
3474 * c. Add this dummy payment to the corresponding payment instruction.
3475 *
3476 * Note that setup checks are to be printed once per payment
3477 * instruction (not once per payment).
3478 */
3479 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3480 print_debuginfo(l_module_name, 'Performing setup document handling ..');
3481
3482 END IF;
3483 FOR i IN x_paperSpecialTab.FIRST .. x_paperSpecialTab.LAST LOOP
3484
3485 /*
3486 * Setup documents have to be generated once per payment
3487 * instruction. Generate setup docs for an instruction
3488 * only if the 'setup docs flag' is set to 'N'.
3489 */
3490 IF (x_paperSpecialTab(i).setup_docs_for_instr_finished <> 'Y') THEN
3491
3492 FOR j IN 1 .. x_paperSpecialTab(i).num_setup_docs LOOP
3493
3494 /*
3495 * Insert a dummy payment into IBY_PAYMENTS_ALL
3496 * table for each setup document.
3497 */
3498
3499 /*
3500 * This is a new payment; Get an id for this payment
3501 */
3502 IBY_PAYGROUP_PUB.getNextPaymentID(l_payment_id);
3503
3504 l_pmt_rec.payment_id := l_payment_id;
3505 l_pmt_rec.payment_profile_id := -1;
3506 l_pmt_rec.payment_status := 'VOID_BY_SETUP';
3507 l_pmt_rec.payment_amount := 0;
3508 l_pmt_rec.internal_bank_account_id := -1;
3509 l_pmt_rec.created_by := fnd_global.user_id;
3510 l_pmt_rec.creation_date := sysdate;
3511 l_pmt_rec.last_updated_by := fnd_global.user_id;
3512 l_pmt_rec.last_update_date := sysdate;
3513 l_pmt_rec.last_update_login := fnd_global.login_id;
3514 l_pmt_rec.object_version_number := 1;
3515 l_pmt_rec.ext_payee_id := -1;
3516 l_pmt_rec.payment_service_request_id := -1;
3517
3518 /*
3519 * Fix for bug 5336487:
3520 *
3521 * For setup checks, set the payment currency
3522 * to '-1'.
3523 */
3524 l_pmt_rec.payment_currency_code := '-1';
3525
3526 l_pmt_rec.org_id := -1;
3527 l_pmt_rec.org_type := 'OPERATING_UNIT';
3528 l_pmt_rec.legal_entity_id := -1;
3529
3530 /*
3531 * Fix for bug 5336487:
3532 *
3533 * For setup checks, set the payment function
3534 * to '-1'.
3535 */
3536 l_pmt_rec.payment_function := '-1';
3537
3538 l_pmt_rec.process_type := 'STANDARD';
3539 l_pmt_rec.payments_complete_flag := 'N';
3540 l_pmt_rec.bill_payable_flag := 'N';
3541 l_pmt_rec.exclusive_payment_flag := 'N';
3542 l_pmt_rec.declare_payment_flag := 'N';
3543 l_pmt_rec.pregrouped_payment_flag := 'N';
3544 l_pmt_rec.stop_confirmed_flag := 'N';
3545 l_pmt_rec.stop_released_flag := 'N';
3546 l_pmt_rec.stop_request_placed_flag := 'N';
3547 l_pmt_rec.separate_remit_advice_req_flag
3548 := 'N';
3549 l_pmt_rec.payment_method_code := '-1';
3550
3551 /*
3552 * Set the instruction id for this payment to the
3553 * currently running payment instruction id
3554 */
3555 l_pmt_rec.payment_instruction_id := x_paperSpecialTab(i).
3556 instruction_id;
3557
3558 /* add payment to PLSQL table of payments */
3559 /* this will be used to insert into IBY_PAMENTS table */
3560 x_dummyPaperPmtsTab(x_dummyPaperPmtsTab.COUNT + 1)
3561 := l_pmt_rec;
3562
3563 /*
3564 * No need to add this payment to the payment instructions
3565 * PLSQL table 'x_pmtsInPmtInstrTab'. This is because
3566 * setup payments are dummy payments that are handled
3567 * separately.
3568 */
3569
3570 /*
3571 * For each dummy payment, insert corresponding dummy
3572 * documents. Insert as many dummy documents as will
3573 * fit into the stub of the printed document.
3574 */
3575
3576 /*
3577 * Fix for bug 5642449:
3578 *
3579 * Only create dummy documents to stamp on the setup
3580 * check if we know the number of lines per stub.
3581 * Otherwise, simply create the setup checks without
3582 * populating the check stub with document ids (because
3583 * we do not know how many document ids will fit on the
3584 * check stub).
3585 */
3586 IF (x_paperSpecialTab(i).num_lines_per_stub IS NOT NULL AND
3587 x_paperSpecialTab(i).num_lines_per_stub > 0) THEN
3588
3589 FOR k IN 1 .. x_paperSpecialTab(i).num_lines_per_stub LOOP
3590
3591 /*
3592 * This is a new document; Get an id for this document
3593 */
3594 l_doc_rec.document_payable_id :=
3595 IBY_DISBURSE_SUBMIT_PUB_PKG.
3596 getNextDocumentPayableID();
3597
3598 /*
3599 * By default, set the formatting payment id to
3600 * payment id for each document
3601 */
3602 l_doc_rec.formatting_payment_id :=
3603 l_payment_id;
3604 l_doc_rec.calling_app_id := -1;
3605 l_doc_rec.document_type := 'INVOICE';
3606 l_doc_rec.document_status := 'VOID_BY_SETUP';
3607 l_doc_rec.payment_id := l_payment_id;
3608 l_doc_rec.payment_amount := -1;
3609 l_doc_rec.payment_method_code := -1;
3610 l_doc_rec.exclusive_payment_flag:= 'N';
3611 l_doc_rec.payee_party_id := -1;
3612 l_doc_rec.legal_entity_id := -1;
3613 l_doc_rec.created_by := fnd_global.user_id;
3614 l_doc_rec.creation_date := sysdate;
3615 l_doc_rec.last_updated_by := fnd_global.user_id;
3616 l_doc_rec.last_update_date := sysdate;
3617 l_doc_rec.last_update_login := fnd_global.login_id;
3618 l_doc_rec.object_version_number := 1;
3619
3620 /*
3621 * Fix for bug 5336487:
3622 *
3623 * For setup documents, set the payment currency
3624 * to '-1'.
3625 */
3626 l_doc_rec.document_currency_code:= '-1';
3627 l_doc_rec.payment_currency_code := '-1';
3628
3629 l_doc_rec.payment_service_request_id
3630 := -1;
3631 l_doc_rec.org_id := -1;
3632 l_doc_rec.org_type := 'OPERATING_UNIT';
3633 l_doc_rec.calling_app_doc_unique_ref1 := '-1';
3634 l_doc_rec.calling_app_doc_unique_ref2 := '-1';
3635 l_doc_rec.calling_app_doc_unique_ref3 := '-1';
3636 l_doc_rec.calling_app_doc_unique_ref4 := '-1';
3637 l_doc_rec.calling_app_doc_unique_ref5 := '-1';
3638 l_doc_rec.pay_proc_trxn_type_code
3639 := '-1';
3640
3641 /*
3642 * Fix for bug 5336487:
3643 *
3644 * For setup documents, set the payment function
3645 * to '-1'.
3646 */
3647 l_doc_rec.payment_function := '-1';
3648
3649 l_doc_rec.calling_app_doc_ref_number := -1;
3650 l_doc_rec.payment_date := sysdate;
3651 l_doc_rec.document_date := sysdate;
3652 l_doc_rec.document_amount := 0;
3653 l_doc_rec.straight_through_flag := 'Y';
3654 l_doc_rec.allow_removing_document_flag := 'N';
3655 l_doc_rec.ext_payee_id := -1;
3656
3657 /* add document to PLSQL table of documents */
3658 x_setupDocsTab(x_setupDocsTab.COUNT + 1) := l_doc_rec;
3659
3660 END LOOP; -- for num_lines_per_stub
3661
3662 ELSE
3663
3664 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3665 print_debuginfo(l_module_name, 'Setup checks will not '
3666 || 'contain invoice numbers because number of lines '
3667 || 'per stub is unknown.'
3668 );
3669
3670 END IF;
3671 END IF; -- if num lines per stub is not null
3672
3673 END LOOP; -- for num_setup_docs in payment
3674
3675 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3676 print_debuginfo(l_module_name, 'Added '
3677 || x_paperSpecialTab(i).num_setup_docs
3678 || ' setup payments with '
3679 || x_paperSpecialTab(i).num_lines_per_stub
3680 || ' dummy documents payable each, to payment instruction '
3681 || x_paperSpecialTab(i).instruction_id
3682 );
3683
3684 END IF;
3685 /*
3686 * Once we have finished generating setup documents for an
3687 * instruction, set the 'setup docs flag' to 'Y' for all
3688 * records with the same instruction id in the paperSpecialTab
3689 * PLSQL table. This is to prevent the code fro generating
3690 * setup docs more than once for a payment instruction.
3691 */
3692 updateSetupDocsFlagForInstr(x_paperSpecialTab(i).instruction_id,
3693 x_paperSpecialTab);
3694
3695 END IF; -- if setup_docs_for_instr_finished <> 'Y'
3696
3697 END LOOP; -- for each pmt in instruction with process type 'PAPER'
3698
3699 /*
3700 * STEP 2:
3701 * Handle overflow documents.
3702 *
3703 * The number of lines that can be printed onto a check stub
3704 * is limited by the physical dimenensions of the paper stock.
3705 * The number of lines per stub is available as an attribute of
3706 * the paper stock.
3707 *
3708 * In each line on the stub, we will print the id of the
3709 * documents payable that are paid by that check.
3710 * So one check, can accomodate 'n' documents payable where
3711 * 'n' is the number of lines per stub for the paper stock
3712 * of the check.
3713 *
3714 * If a payment has more documents payable lined to it than
3715 * will fit onto the check stub, do the following:
3716 *
3717 * a. Calculate the number of overflow checks that need to
3718 * be printed to accomodate the excess documents payable.
3719 * (this is already accomplished by the document numbering
3720 * flow).
3721 * b. For each overflow check, insert a dummy payment.
3722 * c. Add this dummy payment to the correspoding payment instruction.
3723 */
3724 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3725 print_debuginfo(l_module_name, 'Performing overflow document '
3726 || 'handling ..');
3727
3728 END IF;
3729 FOR i IN x_paperSpecialTab.FIRST .. x_paperSpecialTab.LAST LOOP
3730
3731 /*
3732 * Pull up all the documents associated with this payment id.
3733 * The format_payment_id of these documents will be changed
3734 * to handle overflows.
3735 *
3736 * Only do this if a payment has non-zero overflow documents.
3737 */
3738 IF (x_paperSpecialTab(i).num_overflow_docs IS NOT NULL AND
3739 x_paperSpecialTab(i).num_overflow_docs > 0) THEN
3740
3741 OPEN c_document_data(x_paperSpecialTab(i).payment_id);
3742 FETCH c_document_data BULK COLLECT INTO x_overflowDocsTab;
3743 CLOSE c_document_data;
3744
3745 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3746 print_debuginfo(l_module_name, 'Payment '
3747 || x_paperSpecialTab(i).payment_id
3748 || ' has overflow documents payable. '
3749 || x_paperSpecialTab(i).num_overflow_docs
3750 || ' overflow paper payments will be '
3751 || ' created for this payment.'
3752 );
3753
3754 END IF;
3755 END IF;
3756
3757 /* initialize indices on overflow docs array before entering loop */
3758 l_begin_doc_index := 1;
3759 l_end_doc_index := 0;
3760
3761 /*
3762 * If there are no overflow docs for a payment, that payment
3763 * will not enter this loop.
3764 */
3765
3766 /*
3767 * Fix for bug 5252629:
3768 *
3769 * If number of overflow docs is null for a payment, it means
3770 * that the user did not specify the number of lines per
3771 * stub for the check stock that the payment is meant to
3772 * be printed on.
3773 *
3774 * In this case we cannot do overflow handling. Handle this
3775 * situation gracefully by skipping overflow handling.
3776 */
3777 IF ( x_paperSpecialTab(i).num_overflow_docs IS NOT NULL) THEN
3778
3779 /* informative debug message */
3780 IF (x_paperSpecialTab(i).num_overflow_docs = 0) THEN
3781
3782 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3783 print_debuginfo(l_module_name, 'Overflow handling not '
3784 || 'required for payment instruction '
3785 || x_paperSpecialTab(i).instruction_id
3786 );
3787
3788 END IF;
3789 END IF;
3790
3791 FOR j IN 1 .. x_paperSpecialTab(i).num_overflow_docs LOOP
3792
3793 /*
3794 * Insert a dummy payment into IBY_PAYMENTS_ALL
3795 * table for each set of overflow documents.
3796 */
3797
3798 /*
3799 * This is a new payment; Get an id for this payment
3800 */
3801 IBY_PAYGROUP_PUB.getNextPaymentID(l_payment_id);
3802
3803 l_pmt_rec.payment_id := l_payment_id;
3804 l_pmt_rec.payment_profile_id := -1;
3805 l_pmt_rec.payment_status := 'VOID_BY_OVERFLOW';
3806 l_pmt_rec.payment_amount := 0;
3807 l_pmt_rec.internal_bank_account_id := -1;
3808 l_pmt_rec.created_by := fnd_global.user_id;
3809 l_pmt_rec.creation_date := sysdate;
3810 l_pmt_rec.last_updated_by := fnd_global.user_id;
3811 l_pmt_rec.last_update_date := sysdate;
3812 l_pmt_rec.last_update_login := fnd_global.login_id;
3813 l_pmt_rec.object_version_number := 1;
3814 l_pmt_rec.ext_payee_id := -1;
3815 l_pmt_rec.payment_service_request_id
3816 := -1;
3817
3818 /*
3819 * Fix for bug 5336487:
3820 *
3821 * For overflow documents, set the currency
3822 * of the documents to the currency of the
3823 * parent payment (instead of hardcoding to
3824 * a dummy value).
3825 *
3826 * Since grouping by payment curency is a
3827 * hardcoded grouping rule, we can use the
3828 * payment currency from any real document
3829 * of this payment.
3830 */
3831 l_pmt_rec.payment_currency_code := x_overflowDocsTab(1).
3832 pmt_currency;
3833
3834 /*
3835 * Fix for bug 5332172:
3836 *
3837 * Do not hardcode the org id to -1 because the UI
3838 * restricts the viewing of these payments by org.
3839 *
3840 * Instead, use the org id of one of the overflow
3841 * documents payable that will be printed on this
3842 * overflow payment.
3843 *
3844 * We can use any of the documents that will be
3845 * part of this payment because all documents
3846 * payable that are part of a payment are guaranteed
3847 * to have the same (org id, org type); this is
3848 * a hardcoded grouping rule.
3849 */
3850 l_pmt_rec.org_id := x_overflowDocsTab(1).
3851 org_id;
3852 l_pmt_rec.org_type := x_overflowDocsTab(1).
3853 org_type;
3854
3855 l_pmt_rec.legal_entity_id := -1;
3856
3857 /*
3858 * Fix for bug 5336487:
3859 *
3860 * For overflow payments, set the payment function
3861 * to the same as it's child documents.
3862 *
3863 * Since grouping by payment function is a hardcoded
3864 * grouping rule, we can pick up the payment
3865 * function from any of the documents and set it to
3866 * the payment.
3867 */
3868 l_pmt_rec.payment_function := x_overflowDocsTab(1).
3869 pmt_function;
3870
3871 l_pmt_rec.process_type := 'STANDARD';
3872 l_pmt_rec.payments_complete_flag := 'N';
3873 l_pmt_rec.bill_payable_flag := 'N';
3874 l_pmt_rec.exclusive_payment_flag := 'N';
3875 l_pmt_rec.declare_payment_flag := 'N';
3876 l_pmt_rec.pregrouped_payment_flag := 'N';
3877 l_pmt_rec.stop_confirmed_flag := 'N';
3878 l_pmt_rec.stop_released_flag := 'N';
3879 l_pmt_rec.stop_request_placed_flag := 'N';
3880 l_pmt_rec.separate_remit_advice_req_flag
3881 := 'N';
3882 l_pmt_rec.payment_method_code := '-1';
3883
3884 /*
3885 * KLUDGE:
3886 *
3887 * We are creating a dummy payment to store overflow
3888 * documents. These payments are actually part of a real
3889 * payment indicated by x_paperSpecialTab(i).payment_id.
3890 *
3891 * When it is time for check numbering, we want to number
3892 * the real payment and the dummy payments contiguously.
3893 * Therefore, we need to know what dummy payments are
3894 * related to which real payment.
3895 *
3896 * The l_pmt_rec structure cannot store the real payment
3897 * id because it is exactly mapped to a row in
3898 * IBY_PAYMENTS_ALL.
3899 *
3900 * Instead of creating a new data structure to hold
3901 * (real payment id, dummy payment id), we use an
3902 * optional field in the l_pmt_rec record to store the
3903 * real payment id.
3904 *
3905 * This is the 'payee party site id' field. Since this is
3906 * a dummy payment any value we provide for this field
3907 * does not really matter.
3908 *
3909 * This field will be accessed in assignCheckNumbers()
3910 * method to find out all dummy payments related to a
3911 * real payment.
3912 */
3913
3914 /*
3915 * UPDATE:
3916 * Use external_bank_account_id as the placeholder
3917 * for the related original payment id. We are trying to
3918 * use some field on the payment, that is relatively
3919 * useless, to store some extra information.
3920 *
3921 * The external bank account id is a good candidate
3922 * for this because this field is expected to be
3923 * not used for paper payments anyway. The payee
3924 * party site id field will be overwritten with data
3925 * from the original payment as part of fix for
3926 * bug 6765314.
3927 *
3928 * Even though, we really don't need this link between
3929 * the overflow payment and the original payment
3930 * outside of this package, it would be a good idea to
3931 * persist this information somehow so that we can
3932 * quickly answer the question "What is the original
3933 * payment that this overflow payment is related to?"
3934 *
3935 * Ideally we should have a specific column on the
3936 * payment to store this information, so that we don't
3937 * have these kludges.
3938 */
3939 l_pmt_rec.external_bank_account_id :=
3940 x_paperSpecialTab(i).payment_id;
3941
3942 /*
3943 * Set the instruction id for this payment to the
3944 * currently running payment instruction id
3945 */
3946 l_pmt_rec.payment_instruction_id := x_paperSpecialTab(i).
3947 instruction_id;
3948
3949 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3950 print_debuginfo(l_module_name, 'Creating dummy overflow '
3951 || ' payment '
3952 || l_payment_id
3953 || ' for payment '
3954 || x_paperSpecialTab(i).payment_id
3955 );
3956
3957 END IF;
3958 /* add payment to PLSQL table of payments */
3959 x_dummyPaperPmtsTab(x_dummyPaperPmtsTab.COUNT + 1)
3960 := l_pmt_rec;
3961
3962 /*
3963 * No need to add this payment to the payment instructions
3964 * PLSQL table 'x_pmtsInPmtInstrTab'. This is because
3965 * overflow payments are dummy payments that are handled
3966 * separately.
3967 */
3968
3969 /*
3970 * We have just created a dummy payment for overflow
3971 * purposes.
3972 *
3973 * Some documents will need to be updated so that their
3974 * formatting_payment_id is set to the id of the dummy
3975 * payment that we just created.
3976 *
3977 * Originally, all documents will have the same value
3978 * for payment_id and formatting_payment_id. When there
3979 * are overflow documents, then the following will happen:
3980 *
3981 * a. Documents that will fit into one stub will
3982 * contain payment_id same as original document
3983 * id.
3984 *
3985 * b. All documents that will not fit into the stub (a)
3986 * will have to printed on void checks. These will
3987 * be identified because for these documents the
3988 * formatting_payment_id <> the payment id.
3989 *
3990 * So at this point, we have to identify documents
3991 * for this payment that will not fit into the first
3992 * stub and change their formatting_payment_id to the one
3993 * that we generated for the dummy payment. We do this in
3994 * a loop to finish all excess documents.
3995 *
3996 * Example,
3997 * Payment 102 contains 10 documents.
3998 * Check stub contains space for 4 document lines.
3999 *
4000 * Therefore, number of printed checks will be
4001 * (4 docs) + (4 docs) + (2 docs) = 3 checks
4002 * [Void check] [Void check] [Real check]
4003 *
4004 * One check will be the real check, 2 checks are overflow.
4005 * We have already calculated the number of overflow
4006 * checks by the time we come here.
4007 *
4008 * We need to link the documents that will fit into the
4009 * overflow checks to the dummy payments (void checks)
4010 * that we just created.
4011 */
4012
4013 /*
4014 * Change the format_payment_id of 'n' documents of this
4015 * payment to the dummy payment id, where 'n' = num lines
4016 * that will fit on the stub. These 'n' documents
4017 * will be printed on a voided check.
4018 */
4019
4020 /*
4021 * Make sure that the end index does not exceed the
4022 * size of the docs array.
4023 */
4024
4025 /* need to subtract 1 because 'begin index' is 1 based */
4026 IF (l_begin_doc_index +
4027 (x_paperSpecialTab(i).num_lines_per_stub - 1)
4028 > x_overflowDocsTab.COUNT) THEN
4029
4030 l_end_doc_index := x_overflowDocsTab.COUNT;
4031
4032 ELSE
4033
4034 l_end_doc_index := l_begin_doc_index +
4035 (x_paperSpecialTab(i).
4036 num_lines_per_stub - 1);
4037
4038 END IF;
4039
4040 FOR k IN l_begin_doc_index .. l_end_doc_index LOOP
4041
4042 x_overflowDocsTab(k).format_payment_id := l_payment_id;
4043 -- Bug 6486816- copying the overfloW record into the out parameter
4044 x_overflowDocsTab_out(l_begin_doc_index_outrec) :=x_overflowDocsTab(k);
4045 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4046 print_debuginfo(l_module_name, 'For document '
4047 || x_overflowDocsTab(k).doc_id
4048 || ' with pmt id '
4049 || x_overflowDocsTab(k).payment_id
4050 || ' format pmt id set to '
4051 || x_overflowDocsTab(k).format_payment_id
4052 );
4053
4054 END IF;
4055 l_begin_doc_index := l_begin_doc_index + 1;
4056 l_begin_doc_index_outrec := l_begin_doc_index_outrec + 1;
4057 END LOOP;
4058
4059 END LOOP; -- for num_overflow_docs in payment
4060
4061 ELSE
4062
4063 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4064 print_debuginfo(l_module_name, 'Not creating any '
4065 || 'overflow payments for payment instruction '
4066 || x_paperSpecialTab(i).instruction_id
4067 || ' because num lines per stub is not specified '
4068 || 'for the associated payment document.'
4069 );
4070
4071 END IF;
4072 END IF; -- if num overflow docs is not null
4073
4074 IF (x_paperSpecialTab(i).num_overflow_docs IS NOT NULL AND
4075 x_paperSpecialTab(i).num_overflow_docs > 0) THEN
4076
4077 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4078 print_debuginfo(l_module_name, 'Added '
4079 || x_paperSpecialTab(i).num_overflow_docs
4080 || ' overflow payments to payment instruction '
4081 || x_paperSpecialTab(i).instruction_id
4082 );
4083
4084 END IF;
4085 END IF;
4086
4087 END LOOP; -- for each pmt in instruction with process type 'PAPER'
4088 -- Bug 6486816- copy the overflow documents table with all details to
4089 --the variable that is passed to calling function.
4090 x_overflowDocsTab := x_overflowDocsTab_out;
4091 IF (x_overflowDocsTab.COUNT > 0) THEN
4092
4093 FOR i IN x_overflowDocsTab.FIRST .. x_overflowDocsTab.LAST LOOP
4094
4095 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4096 print_debuginfo(l_module_name, 'Doc id '
4097 || x_overflowDocsTab(i).doc_id
4098 || ', payment id: '
4099 || x_overflowDocsTab(i).payment_id
4100 || ', format payment id: '
4101 || x_overflowDocsTab(i).format_payment_id
4102 );
4103
4104 END IF;
4105 END LOOP;
4106
4107 END IF;
4108
4109 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4110 print_debuginfo(l_module_name, 'EXIT');
4111
4112 END IF;
4113 END performSpecialPaperDocHandling;
4114
4115 /*--------------------------------------------------------------------
4116 | NAME:
4117 | updateSetupDocsFlagForInstr
4118 |
4119 | PURPOSE:
4120 |
4121 |
4122 |
4123 | PARAMETERS:
4124 | IN
4125 |
4126 |
4127 | OUT
4128 |
4129 |
4130 | RETURNS:
4131 |
4132 | NOTES:
4133 |
4134 *---------------------------------------------------------------------*/
4135 PROCEDURE updateSetupDocsFlagForInstr(
4136 p_instrId IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
4137 x_paperSpecialTab IN OUT NOCOPY paperPmtsSpecialDocsTabType
4138 )
4139 IS
4140 BEGIN
4141
4142 /*
4143 * Loop through all the paper payments table. If we find any
4144 * payment instruction that matches the given payment
4145 * instruction, set the 'set up docs' flag for the payment
4146 * instruction to 'Y' to indicate that setup documents
4147 * have already been generated for this payment instruction.
4148 */
4149 FOR i IN x_paperSpecialTab.FIRST .. x_paperSpecialTab.LAST LOOP
4150
4151 IF (x_paperSpecialTab(i).instruction_id = p_instrId) THEN
4152 x_paperSpecialTab(i).setup_docs_for_instr_finished := 'Y';
4153 END IF;
4154
4155 END LOOP;
4156
4157 END updateSetupDocsFlagForInstr;
4158
4159 /*--------------------------------------------------------------------
4160 | NAME:
4161 | updatePmtsWithCheckNumbers
4162 |
4163 | PURPOSE:
4164 |
4165 |
4166 |
4167 | PARAMETERS:
4168 | IN
4169 |
4170 | OUT
4171 |
4172 |
4173 | RETURNS:
4174 |
4175 | NOTES:
4176 |
4177 *---------------------------------------------------------------------*/
4178 PROCEDURE updatePmtsWithCheckNumbers(
4179 p_pmtsInPayInstTab IN IBY_PAYINSTR_PUB.pmtsInpmtInstrTabType
4180 )
4181 IS
4182 l_module_name VARCHAR2(200) := G_PKG_NAME || '.updatePmtsWithCheckNumbers';
4183
4184 BEGIN
4185
4186 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4187 print_debuginfo(l_module_name, 'ENTER');
4188
4189 END IF;
4190 /* Normally, this should not happen */
4191 IF (p_pmtsInPayInstTab.COUNT = 0) THEN
4192 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4193 print_debuginfo(l_module_name, 'No payments '
4194 || 'were provided to update '
4195 || 'IBY_PAYMENTS_ALL table. Possible data '
4196 || 'corruption issue.');
4197 END IF;
4198 RETURN;
4199 END IF;
4200
4201 /*
4202 * Update the payments. We cannot use bulk update here
4203 * because the bulk update syntax does not allow us to
4204 * reference individual fields of the PL/SQL record.
4205 *
4206 * TBD: Is there any way to optimize this update?
4207 */
4208 FOR i in p_pmtsInPayInstTab.FIRST..p_pmtsInPayInstTab.LAST LOOP
4209
4210 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4211 print_debuginfo(l_module_name, 'Instruction: '
4212 || p_pmtsInPayInstTab(i).pay_instr_id || ', payment: '
4213 || p_pmtsInPayInstTab(i).payment_id);
4214
4215 END IF;
4216 UPDATE
4217 IBY_PAYMENTS_ALL
4218 SET
4219 paper_document_number = p_pmtsInPayInstTab(i).check_number
4220 WHERE
4221 payment_id = p_pmtsInPayInstTab(i).payment_id
4222 AND payment_status = p_pmtsInPayInstTab(i).payment_status;
4223
4224 END LOOP;
4225
4226 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4227 print_debuginfo(l_module_name, 'EXIT');
4228
4229 END IF;
4230 END updatePmtsWithCheckNumbers;
4231
4232 /*--------------------------------------------------------------------
4233 | NAME:
4234 | isPaperDocNumUsed
4235 |
4236 | PURPOSE:
4237 |
4238 |
4239 |
4240 | PARAMETERS:
4241 | IN
4242 |
4243 | OUT
4244 |
4245 |
4246 | RETURNS:
4247 |
4248 | NOTES:
4249 |
4250 *---------------------------------------------------------------------*/
4251 PROCEDURE isPaperDocNumUsed(
4252 p_payment_doc_id IN IBY_USED_PAYMENT_DOCS.payment_document_id%TYPE,
4253 x_paper_doc_num IN IBY_USED_PAYMENT_DOCS.used_document_number%TYPE,
4254 x_return_status IN OUT NOCOPY VARCHAR2
4255 )
4256 IS
4257
4258 l_module_name VARCHAR2(200) := G_PKG_NAME || '.isPaperDocNumUsed';
4259 l_used_paper_doc_number NUMBER := 0;
4260
4261 BEGIN
4262
4263 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4264 print_debuginfo(l_module_name, 'ENTER');
4265
4266 END IF;
4267 /*
4268 * Check if this paper document number has already
4269 * been used.
4270 */
4271 BEGIN
4272
4273 SELECT
4274 used_document_number
4275 INTO
4276 l_used_paper_doc_number
4277 FROM
4278 IBY_USED_PAYMENT_DOCS
4279 WHERE
4280 payment_document_id = p_payment_doc_id AND
4281 used_document_number = x_paper_doc_num
4282 ;
4283
4284 EXCEPTION
4285
4286 WHEN NO_DATA_FOUND THEN
4287
4288 /* now rows means success */
4289 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4290 print_debuginfo(l_module_name, 'Paper document '
4291 || 'number '
4292 || x_paper_doc_num
4293 || ' is unused.'
4294 );
4295
4296 END IF;
4297 WHEN OTHERS THEN
4298
4299 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4300 print_debuginfo(l_module_name, 'Exception occured when '
4301 || 'attempting to get details of paper document '
4302 || x_paper_doc_num
4303 || ' from IBY_USED_PAYMENT_DOCS table.'
4304 );
4305
4306 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
4307 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
4308
4309 END IF;
4310 x_return_status := FND_API.G_RET_STS_ERROR;
4311
4312 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4313 print_debuginfo(l_module_name, 'Returning error response ..');
4314
4315 print_debuginfo(l_module_name, 'EXIT');
4316
4317 END IF;
4318 RETURN;
4319
4320 END;
4321
4322
4323 x_return_status := FND_API.G_RET_STS_SUCCESS;
4324
4325 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4326 print_debuginfo(l_module_name, 'Returning success response ..');
4327 print_debuginfo(l_module_name, 'EXIT');
4328
4329 END IF;
4330 END isPaperDocNumUsed;
4331
4332 /*--------------------------------------------------------------------
4333 | NAME:
4334 | isSinglePayment
4335 |
4336 | PURPOSE:
4337 | Determines whether the provided list of payments consists of a
4338 | single payment.
4339 |
4340 | If the following conditions are satisfied
4341 |
4342 | a. Only one payment is present in the provided list of payments
4343 | b. The payment service request of the provided payment
4344 | has process type 'IMMEDIATE'
4345 |
4346 | then the payment is considered to be a single payment.
4347 |
4348 | PARAMETERS:
4349 | IN
4350 | p_paperPmtsTab - PLSQL table of paper payments. Ensure that
4351 | this table only consists of real paper payments
4352 | (no dummy payments - setup or overflow payments
4353 | - should be included in this list).
4354 | OUT
4355 | NONE
4356 |
4357 | RETURNS:
4358 | BOOLEAN - TRUE, if the provided payments list consists of
4359 | a single payment
4360 | FALSE, otherwise
4361 |
4362 | NOTES:
4363 |
4364 *---------------------------------------------------------------------*/
4365 FUNCTION isSinglePayment(
4366 p_paperPmtsTab IN paperPmtsSpecialDocsTabType
4367 ) RETURN BOOLEAN
4368 IS
4369
4370 l_module_name VARCHAR2(200) := G_PKG_NAME || '.isSinglePayment';
4371
4372 l_retflag BOOLEAN;
4373 l_process_type IBY_PAY_SERVICE_REQUESTS.process_type%TYPE;
4374 l_pmt_rec paperPmtsSpecialDocsRecType;
4375
4376 BEGIN
4377
4378 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4379 print_debuginfo(l_module_name, 'ENTER');
4380
4381 END IF;
4382 IF (p_paperPmtsTab.COUNT = 0) THEN
4383
4384 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4385 print_debuginfo(l_module_name, 'Paper payments list is empty. '
4386 || 'Returning false ..'
4387 );
4388
4389 END IF;
4390 l_retflag := FALSE;
4391 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4392 print_debuginfo(l_module_name, 'EXIT');
4393
4394 END IF;
4395 RETURN l_retflag;
4396
4397 END IF;
4398
4399 IF (p_paperPmtsTab.COUNT <> 1) THEN
4400
4401 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4402 print_debuginfo(l_module_name, 'Paper payments count is more '
4403 || 'than 1. Returning false ..'
4404 );
4405
4406 END IF;
4407 l_retflag := FALSE;
4408 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4409 print_debuginfo(l_module_name, 'EXIT');
4410
4411 END IF;
4412 RETURN l_retflag;
4413
4414 END IF;
4415
4416 /*
4417 * If we reached here, it means that there is only one
4418 * paper payment in the provided payment array.
4419 *
4420 * Look up the process type of the payment service request
4421 * to confirm that the process type is IMMEDIATE.
4422 *
4423 * These two conditions are necessary and sufficient to
4424 * determine whether a payment is a single payment.
4425 */
4426 BEGIN
4427
4428 l_pmt_rec := p_paperPmtsTab(p_paperPmtsTab.FIRST);
4429
4430 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4431 print_debuginfo(l_module_name, 'Provided payment id is '
4432 || l_pmt_rec.payment_id
4433 );
4434
4435 END IF;
4436 SELECT
4437 req.process_type
4438 INTO
4439 l_process_type
4440 FROM
4441 IBY_PAY_SERVICE_REQUESTS req,
4442 IBY_PAYMENTS_ALL pmt
4443 WHERE
4444 req.payment_service_request_id =
4445 pmt.payment_service_request_id AND
4446 pmt.payment_id = l_pmt_rec.payment_id
4447 ;
4448
4449 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4450 print_debuginfo(l_module_name, 'Processing type of parent '
4451 || 'request is '
4452 || l_process_type
4453 );
4454
4455 END IF;
4456 /*
4457 * Set the return flag based on the processing type.
4458 */
4459 IF (l_process_type = 'IMMEDIATE') THEN
4460
4461 l_retflag := TRUE;
4462
4463 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4464 print_debuginfo(l_module_name, 'Setting return flag '
4465 || 'to true.'
4466 );
4467
4468 END IF;
4469 ELSE
4470
4471 l_retflag := FALSE;
4472
4473 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4474 print_debuginfo(l_module_name, 'Setting return flag '
4475 || 'to false because processing type is not IMMEDIATE.'
4476 );
4477
4478 END IF;
4479 END IF;
4480
4481 EXCEPTION
4482 WHEN OTHERS THEN
4483
4484
4485 print_debuginfo(l_module_name, 'Fatal: Exception when attempting '
4486 || 'to get processing type for payment '
4487 || l_pmt_rec.payment_id,
4488 FND_LOG.LEVEL_UNEXPECTED
4489 );
4490
4491 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
4492 FND_LOG.LEVEL_UNEXPECTED);
4493 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
4494 FND_LOG.LEVEL_UNEXPECTED);
4495
4496
4497 /*
4498 * Propogate exception to caller.
4499 */
4500 RAISE;
4501
4502 END;
4503
4504 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4505 print_debuginfo(l_module_name, 'EXIT');
4506 END IF;
4507 RETURN l_retflag;
4508
4509 END isSinglePayment;
4510
4511 /*--------------------------------------------------------------------
4512 | NAME:
4513 | isContigPaperNumAvlbl
4514 |
4515 |
4516 | PURPOSE:
4517 | Checks whether the paper document numbers from the given start
4518 | number to the given end number are available contiguoulsy for
4519 | printing.
4520 |
4521 | For the purpose of printing checks, we should always presume
4522 | the the user is going to print checks on prenumbered check stock
4523 | on a tractor feed. Therefore, the check numbering should always
4524 | be contiguous.
4525 |
4526 | PARAMETERS:
4527 | IN
4528 |
4529 | OUT
4530 |
4531 |
4532 | RETURNS:
4533 |
4534 | NOTES:
4535 |
4536 *---------------------------------------------------------------------*/
4537 FUNCTION isContigPaperNumAvlbl(
4538 p_payment_doc_id IN CE_PAYMENT_DOCUMENTS.payment_document_id%TYPE,
4539 p_start_number IN IBY_PAYMENTS_ALL.paper_document_number%TYPE,
4540 p_end_number IN IBY_PAYMENTS_ALL.paper_document_number%TYPE
4541 ) RETURN BOOLEAN
4542 IS
4543
4544 l_module_name VARCHAR2(200) := G_PKG_NAME || '.isContigPaperNumAvlbl';
4545 l_retflag BOOLEAN;
4546 l_paper_doc_num IBY_PAYMENTS_ALL.paper_document_number%TYPE;
4547
4548 l_api_version CONSTANT NUMBER := 1.0;
4549 l_return_status VARCHAR2 (100);
4550
4551 l_msg_count NUMBER;
4552 l_msg_data VARCHAR2(3000);
4553 l_test VARCHAR2(200) :='TRUE';
4554
4555 l_last_issued_doc_num NUMBER;
4556 l_last_avail_doc_num NUMBER;
4557
4558 BEGIN
4559
4560 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4561 print_debuginfo(l_module_name, 'ENTER');
4562
4563 print_debuginfo(l_module_name, 'Start #: '
4564 || p_start_number
4565 || ', end #: '
4566 || p_end_number
4567 || ', payment document: '
4568 || p_payment_doc_id
4569 );
4570
4571 END IF;
4572 IF (p_start_number IS NULL OR
4573 p_end_number IS NULL OR
4574 p_payment_doc_id IS NULL
4575 ) THEN
4576
4577 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4578 print_debuginfo(l_module_name, 'Provided params are invalid. '
4579 || 'Returning false.');
4580
4581 END IF;
4582 l_retflag := FALSE;
4583
4584 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4585 print_debuginfo(l_module_name, 'EXIT');
4586 END IF;
4587 RETURN l_retflag;
4588
4589 END IF;
4590
4591 /*
4592 * Validate the entire contiguous range of provided
4593 * paper document numbers. If even a single paper
4594 * document number fails validation, it means that
4595 * we do not have a contiguous rage of paper document
4596 * numbers available.
4597 */
4598 l_retflag := TRUE;
4599
4600 /*
4601 * Performance Fix:
4602 *
4603 * This method has been re-written so that it does not loop
4604 * through every paper document number within the range validating
4605 * one-by-one.
4606 *
4607 * Now, it simply executes two SQL statements to verify whether
4608 * the given print range is valid.
4609 */
4610
4611 BEGIN
4612
4613 /*
4614 * First check against CE_PAYMENT_DOCUMENTS table whether
4615 * the start_number is greater than the last_issued_document_number
4616 * and the end_number is less than / equal to the last_available
4617 * _document_number.
4618 *
4619 * Two, special situations are possible here:
4620 * 1. last_issued_document_number = 0, this means that check has never
4621 * been used so far. In this case, we should allow any start_number.
4622 *
4623 * 2. last_available_document_number is null, this means that there
4624 * is no upper limit for the check numbers. In this case, we
4625 * allow any end_number.
4626 *
4627 *
4628 * Bug 8968846: Start number need not be validated on the last issued
4629 * document number. For the batch flow, start number is generated based
4630 * on the last issued document number.
4631 * In case of single payment flow, user can choose any unused document
4632 * number which is less than last issued document number.
4633 *
4634 * So to process the flow smoothly in both cases, validation on the
4635 * start number is removed.
4636 */
4637
4638
4639 SELECT
4640 pmt_doc.last_issued_document_number,
4641 pmt_doc.last_available_document_number
4642 INTO
4643 l_last_issued_doc_num,
4644 l_last_avail_doc_num
4645 FROM
4646 CE_PAYMENT_DOCUMENTS pmt_doc
4647 WHERE
4648 pmt_doc.payment_document_id = p_payment_doc_id ;
4649
4650
4651 EXCEPTION
4652 WHEN OTHERS THEN
4653
4654 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4655 print_debuginfo(l_module_name, 'Exception occured when checking for '
4656 || 'provided check range against CE_PAYMENT_DOCUMENTS table.');
4657 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
4658 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
4659
4660 END IF;
4661 l_retflag := FALSE;
4662
4663 END;
4664
4665 /* Bug 8968846:
4666 * Logic for validating the start number and end number
4667 * is moved here
4668 */
4669 IF (nvl(l_last_avail_doc_num, p_end_number) < p_end_number) THEN
4670
4671 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4672 print_debuginfo(l_module_name, 'Last available '
4673 || 'paper document number is '
4674 || l_last_avail_doc_num
4675 );
4676
4677 print_debuginfo(l_module_name, ' Last paper document number '
4678 || p_end_number
4679 || ' is above last available document number '
4680 || l_last_avail_doc_num
4681 );
4682 END IF;
4683
4684 FND_MESSAGE.set_name('IBY', 'IBY_DOC_NUM_ABOVE_ALLOWED');
4685
4686 FND_MESSAGE.SET_TOKEN('LAST_AVAILABLE_DOC_NUM',
4687 l_last_avail_doc_num,
4688 FALSE);
4689
4690 FND_MSG_PUB.ADD;
4691
4692 l_retflag := FALSE;
4693 l_test := 'FALSE';
4694 END IF;
4695
4696
4697
4698 IF (l_test = 'TRUE') THEN
4699
4700 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4701 print_debuginfo(l_module_name, 'Provided print range is valid in '
4702 || 'CE_PAYMENT_DOCUMENTS table. Verifying if any document within '
4703 || 'is already USED / SPOILED ..'
4704 );
4705
4706 END IF;
4707 BEGIN
4708
4709 /*
4710 * Next check if any USED / SPOILED documents already exist
4711 * for this payment document in the provided check
4712 * range.
4713 *
4714 * If such a USED / SPOILED document already exists, then
4715 * we cannot use this range of numbers for printing.
4716 */
4717 SELECT
4718 'TRUE'
4719 INTO
4720 l_test
4721 FROM
4722 DUAL
4723 WHERE EXISTS
4724 (
4725 SELECT
4726 used_document_number
4727 FROM
4728 IBY_USED_PAYMENT_DOCS
4729 WHERE
4730 payment_document_id = p_payment_doc_id AND
4731 document_use <> 'SKIPPED' AND
4732 used_document_number >= p_start_number AND
4733 used_document_number <= p_end_number
4734 )
4735 ;
4736
4737 IF (l_test = 'TRUE') THEN
4738
4739 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4740 print_debuginfo(l_module_name, 'Provided print range is not valid because '
4741 || 'some documents with this range are already USED / SPOILED. '
4742 || 'You cannot use this print range.'
4743 );
4744
4745 END IF;
4746 l_retflag := FALSE;
4747
4748 ELSE
4749
4750 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4751 print_debuginfo(l_module_name, 'Provided print range is valid because '
4752 || 'no documents with this range are already USED / SPOILED. '
4753 || 'You can use this print range.'
4754 );
4755
4756 END IF;
4757 l_retflag := TRUE;
4758
4759 END IF;
4760
4761
4762 EXCEPTION
4763 WHEN NO_DATA_FOUND THEN
4764 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4765 print_debuginfo(l_module_name, 'No data was found occured when checking if any '
4766 || 'USED / SPOILED documents exist in provided check range.');
4767 print_debuginfo(l_module_name, 'This means no SPOILED / USED checks '
4768 || 'exist for the given check range. Range is valid.');
4769 END IF;
4770 l_retflag := TRUE;
4771
4772 WHEN OTHERS THEN
4773
4774 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4775 print_debuginfo(l_module_name, 'Exception occured when checking if any '
4776 || 'USED / SPOILED documents exist in provided check range.');
4777 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
4778 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
4779
4780 END IF;
4781 l_retflag := FALSE;
4782
4783 END;
4784
4785 ELSE
4786
4787 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4788 print_debuginfo(l_module_name, 'Provided print range is not valid in '
4789 || 'CE_PAYMENT_DOCUMENTS table. You cannot use this print range.'
4790 );
4791
4792 END IF;
4793 l_retflag := FALSE;
4794
4795 END IF;
4796
4797
4798 IF (l_retflag = FALSE) THEN
4799
4800 /*
4801 * Fix for bug 5327347:
4802 *
4803 * If we reach here, it means that one of the paper documents
4804 * in the supplied range has already been used.
4805 *
4806 * This means that we will need to display to the
4807 * user a message like 'sufficient contiguous payment
4808 * documents are not available to print this instruction.'
4809 *
4810 * However, the message 'paper doc number # has already been
4811 * used' has been set in the FND message stack already
4812 * by the IBY_DISBURSE_UI_API_PUB_PKG.validate_paper_doc_number(..)
4813 * method.
4814 *
4815 * Clear this message from the stack as it is redundant.
4816 */
4817 FND_MSG_PUB.initialize;
4818
4819 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4820 print_debuginfo(l_module_name, 'Returning FALSE');
4821 END IF;
4822 ELSE
4823 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4824 print_debuginfo(l_module_name, 'Returning TRUE');
4825 END IF;
4826 END IF;
4827
4828 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4829 print_debuginfo(l_module_name, 'EXIT');
4830 END IF;
4831 RETURN l_retflag;
4832
4833 END isContigPaperNumAvlbl;
4834
4835 /*--------------------------------------------------------------------
4836 | NAME:
4837 | isPaperNosUsedOnExistPmt
4838 |
4839 | PURPOSE:
4840 | Checks whether the paper document numbers from the given start
4841 | number to the given end number are already used on any existing
4842 | payments.
4843 |
4844 | PARAMETERS:
4845 | IN
4846 |
4847 | OUT
4848 |
4849 |
4850 | RETURNS:
4851 |
4852 | NOTES:
4853 |
4854 *---------------------------------------------------------------------*/
4855 FUNCTION isPaperNosUsedOnExistPmt(
4856 p_payment_doc_id IN CE_PAYMENT_DOCUMENTS.payment_document_id%TYPE,
4857 p_start_number IN IBY_PAYMENTS_ALL.paper_document_number%TYPE,
4858 p_end_number IN IBY_PAYMENTS_ALL.paper_document_number%TYPE
4859 ) RETURN BOOLEAN
4860 IS
4861
4862 l_module_name VARCHAR2(200) := G_PKG_NAME || '.isPaperNosUsedOnExistPmt';
4863 l_retflag BOOLEAN;
4864
4865 l_paper_doc_num IBY_PAYMENTS_ALL.paper_document_number%TYPE;
4866 l_test_paper_doc_num IBY_PAYMENTS_ALL.paper_document_number%TYPE;
4867 l_test_pmt_id IBY_PAYMENTS_ALL.payment_id%TYPE;
4868
4869 BEGIN
4870
4871 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4872 print_debuginfo(l_module_name, 'ENTER');
4873
4874 print_debuginfo(l_module_name, 'Start #: '
4875 || p_start_number
4876 || ', end #: '
4877 || p_end_number
4878 || ', payment document: '
4879 || p_payment_doc_id
4880 );
4881
4882 END IF;
4883 IF (p_start_number IS NULL OR
4884 p_end_number IS NULL OR
4885 p_payment_doc_id IS NULL
4886 ) THEN
4887
4888 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4889 print_debuginfo(l_module_name, 'Provided params are invalid. '
4890 || 'Returning true.');
4891
4892 END IF;
4893 l_retflag := TRUE;
4894
4895 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4896 print_debuginfo(l_module_name, 'EXIT');
4897 END IF;
4898 RETURN l_retflag;
4899
4900 END IF;
4901
4902 /*
4903 * Validate the entire contiguous range of provided
4904 * paper document numbers. If even a single paper
4905 * document number has already been used on a payment,
4906 * it is an error and we will return TRUE.
4907 */
4908 l_retflag := FALSE;
4909 FOR i IN 1 .. (p_end_number - p_start_number + 1) LOOP -- Bug 6922269
4910
4911 l_paper_doc_num := p_start_number + i - 1;
4912
4913 BEGIN
4914
4915 SELECT
4916 pmt.paper_document_number,
4917 pmt.payment_id
4918 INTO
4919 l_test_paper_doc_num,
4920 l_test_pmt_id
4921 FROM
4922 IBY_PAYMENTS_ALL pmt,
4923 IBY_PAY_INSTRUCTIONS_ALL inst
4924 WHERE
4925 pmt.payment_instruction_id = inst.payment_instruction_id AND
4926 inst.payment_document_id = p_payment_doc_id AND
4927 pmt.paper_document_number = l_paper_doc_num
4928 ;
4929
4930 /*
4931 * If we reached here it means that we were able to
4932 * successfully retrieve a payment with the provided
4933 * paper document number. This implies that the
4934 * paper document number has already been used.
4935 */
4936 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4937 print_debuginfo(l_module_name, 'Paper document number '
4938 || l_test_paper_doc_num
4939 || ' has already been used for payment id '
4940 || l_test_pmt_id
4941 );
4942
4943 END IF;
4944 l_retflag := TRUE;
4945
4946 EXCEPTION
4947 WHEN OTHERS THEN
4948
4949 /*
4950 * Handle exceptions gracefully. Assume that an
4951 * exception means that no data was found i.e., paper
4952 * document number has not been used.
4953 */
4954 NULL;
4955
4956 END;
4957
4958 END LOOP;
4959
4960
4961 IF (l_retflag = FALSE) THEN
4962 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4963 print_debuginfo(l_module_name, 'Returning FALSE');
4964 END IF;
4965 ELSE
4966 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4967 print_debuginfo(l_module_name, 'Returning TRUE');
4968 END IF;
4969 END IF;
4970
4971 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4972 print_debuginfo(l_module_name, 'EXIT');
4973 END IF;
4974 RETURN l_retflag;
4975
4976 END isPaperNosUsedOnExistPmt;
4977
4978 /*--------------------------------------------------------------------
4979 | NAME:
4980 | print_debuginfo
4981 |
4982 | PURPOSE:
4983 | This procedure prints the debug message to the concurrent manager
4984 | log file.
4985 |
4986 | PARAMETERS:
4987 | IN
4988 | p_debug_text - The debug message to be printed
4989 |
4990 | OUT
4991 |
4992 |
4993 | RETURNS:
4994 |
4995 | NOTES:
4996 |
4997 *---------------------------------------------------------------------*/
4998 PROCEDURE print_debuginfo(
4999 p_module IN VARCHAR2,
5000 p_debug_text IN VARCHAR2,
5001 p_debug_level IN VARCHAR2 DEFAULT FND_LOG.LEVEL_STATEMENT
5002 )
5003 IS
5004 l_default_debug_level VARCHAR2(200) := FND_LOG.LEVEL_STATEMENT;
5005 BEGIN
5006
5007 /*
5008 * Set the debug level to the value passed in
5009 * (provided this value is not null).
5010 */
5011 IF (p_debug_level IS NOT NULL) THEN
5012 l_default_debug_level := p_debug_level;
5013 END IF;
5014
5015 /*
5016 * Write the debug message to the concurrent manager log file.
5017 */
5018 --IF (l_default_debug_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5019
5020 iby_debug_pub.log(debug_msg => p_module||':'||p_debug_text,debug_level => p_debug_level, module => p_module);
5021
5022 --END IF;
5023
5024 END print_debuginfo;
5025
5026 /*--------------------------------------------------------------------
5027 | NAME:
5028 | assignElectronicCheckNumbers
5029 |
5030 | PURPOSE:
5031 |
5032 |
5033 |
5034 | PARAMETERS:
5035 | IN
5036 |
5037 |
5038 | OUT
5039 |
5040 |
5041 | RETURNS:
5042 |
5043 |
5044 | NOTES:
5045 | This method will perform a COMMIT.
5046 |
5047 *---------------------------------------------------------------------*/
5048 PROCEDURE assignElectronicCheckNumbers(
5049 x_pmtInstrRec IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE,
5050 p_payment_doc_id IN CE_PAYMENT_DOCUMENTS.payment_document_id%TYPE,
5051 p_user_assgn_num IN IBY_PAYMENTS_ALL.paper_document_number%TYPE,
5052 x_paperPmtsTab IN OUT NOCOPY paperPmtsSpecialDocsTabType,
5053 x_dummyPaperPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
5054 x_instrErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
5055 x_insTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
5056 trxnErrTokenTabType,
5057 x_return_message IN OUT NOCOPY VARCHAR2,
5058 x_return_status IN OUT NOCOPY NUMBER,
5059 x_msg_count IN OUT NOCOPY NUMBER,
5060 x_msg_data IN OUT NOCOPY VARCHAR2
5061 )
5062 IS
5063
5064 l_paper_pmts_count NUMBER := 0;
5065 l_last_used_check_num NUMBER := 0;
5066 l_last_avail_check_num NUMBER := 0;
5067 l_physical_stock_count NUMBER := 0;
5068 l_anticipated_last_check_num NUMBER := 0;
5069
5070 l_pmt_doc_name VARCHAR2(200) := '';
5071 l_pmt_instr_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE;
5072
5073 l_error_code IBY_TRANSACTION_ERRORS.error_code%TYPE;
5074 l_instr_err_rec IBY_TRANSACTION_ERRORS%ROWTYPE;
5075 l_token_rec IBY_TRXN_ERROR_TOKENS%ROWTYPE;
5076
5077 l_send_to_file_flag VARCHAR2(1);
5078 l_instr_status IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_status%TYPE;
5079
5080 l_single_pmt_flag BOOLEAN;
5081 l_nos_avlbl_flag BOOLEAN;
5082 l_used_flag BOOLEAN;
5083
5084 l_module_name VARCHAR2(200) := G_PKG_NAME || '.assignCheckNumbers';
5085
5086 BEGIN
5087
5088 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5089 print_debuginfo(l_module_name, 'ENTER');
5090 END IF;
5091
5092 l_paper_pmts_count := x_paperPmtsTab.COUNT;
5093
5094 /* should never come into if, but just in case */
5095 IF (l_paper_pmts_count = 0) THEN
5096
5097 /*
5098 * Shouldn't come here. This method was called because there
5099 * was atleast one payment instruction with processing type
5100 * 'PAPER'. This implies that there should be at least one
5101 * payment instruction with processing type 'PAPER'.
5102 *
5103 * If no such payment exists, about the program.
5104 */
5105 --IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5106 print_debuginfo(l_module_name, 'Total # of Electronic payments '
5107 || 'is 0. Possible data corruption. Aborting ..'
5108 );
5109
5110 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5111 print_debuginfo(l_module_name, 'EXIT');
5112 END IF;
5113 x_return_status := -1;
5114
5115 l_error_code := 'IBY_INS_PMTS_NOT_FOUND';
5116 FND_MESSAGE.set_name('IBY', l_error_code);
5117
5118 FND_MESSAGE.SET_TOKEN('INS_ID',
5119 x_pmtInstrRec.payment_instruction_id,
5120 FALSE);
5121
5122 FND_MSG_PUB.ADD;
5123
5124 FND_MSG_PUB.COUNT_AND_GET(
5125 p_count => x_msg_count,
5126 p_data => x_msg_data
5127 );
5128
5129 x_return_message := FND_MESSAGE.get;
5130
5131 RETURN;
5132 ELSE
5133 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5134 print_debuginfo(l_module_name, 'Total # of Electronic payments: '
5135 || l_paper_pmts_count
5136 );
5137 END IF;
5138
5139 END IF;
5140
5141 /*
5142 * Check whether the provided payments list consists
5143 * of a single payment.
5144 */
5145 l_single_pmt_flag := isSinglePayment(x_paperPmtsTab);
5146
5147 /*
5148 * For single payments, do not perform any commits because
5149 * single payments API is session based and only the caller
5150 * decides to commit / not commit.
5151 */
5152 IF (l_single_pmt_flag <> TRUE) THEN
5153
5154 /*
5155 * This commit is needed so that in case of any exceptions
5156 * in this method (e.g., payment document locked), the
5157 * payment instruction status is changed from 'CREATED'
5158 * to the next valid status.
5159 *
5160 * Payments in 'CREATED' status are not visible in the UI,
5161 * this is because 'CREATED' is a transient status.
5162 */
5163 COMMIT;
5164
5165 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5166 print_debuginfo(l_module_name, 'Payment instruction '
5167 || x_pmtInstrRec.payment_instruction_id
5168 || ' status committed to '
5169 || l_instr_status
5170 || ' before numbering.'
5171 );
5172 END IF;
5173
5174 END IF;
5175
5176 /*
5177 * Pull up the details of the paper stock, like the
5178 * last used check number and the last available
5179 * check number.
5180 *
5181 * Note: This SELECT will lock the underlying base
5182 * table IBY_PAYMENT_DOCUMENTS_B. We need to lock this
5183 * table because we need to update the last_document_number.
5184 */
5185 SELECT
5186 payment_document_name,
5187 payment_instruction_id
5188 INTO
5189 l_pmt_doc_name,
5190 l_pmt_instr_id
5191 FROM
5192 CE_PAYMENT_DOCUMENTS
5193 WHERE
5194 payment_document_id = p_payment_doc_id
5195 ;
5196
5197
5198 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5199 print_debuginfo(l_module_name, 'Got the payment document name');
5200 END IF;
5201
5202 /*
5203 *
5204 * Pull up the details of the paper stock, like the
5205 * last used check number and the last available
5206 * check number.
5207 *
5208 * Note: This SELECT will lock the underlying base
5209 * table IBY_PAYMENT_DOCUMENTS_B. We need to lock this
5210 * table because we need to update the last_document_number.
5211 *
5212 * If document is already locked for single payment,
5213 * NO_DATA_FOUND exception would be thrown.
5214 * Bug - 7499044
5215 */
5216 BEGIN
5217 SELECT
5218 NVL(last_issued_document_number, 0),
5219 NVL(last_available_document_number, -1),
5220 payment_document_name,
5221 payment_instruction_id
5222 INTO
5223 l_last_used_check_num,
5224 l_last_avail_check_num,
5225 l_pmt_doc_name,
5226 l_pmt_instr_id
5227 FROM
5228 CE_PAYMENT_DOCUMENTS
5229 WHERE
5230 payment_document_id = p_payment_doc_id
5231 FOR UPDATE SKIP LOCKED
5232 ;
5233 EXCEPTION
5234 WHEN NO_DATA_FOUND THEN
5235 print_debuginfo(l_module_name, 'Payment document '
5236 || ''''
5237 || l_pmt_doc_name
5238 || ''''
5239 || ' with payment doc id '
5240 || p_payment_doc_id
5241 || ' has been locked from payments workbench ',
5242 FND_LOG.LEVEL_UNEXPECTED
5243 );
5244
5245 --IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5246 print_debuginfo(l_module_name, 'Processing cannot continue '
5247 || 'because payment document is unavailable (locked).',
5248 FND_LOG.LEVEL_UNEXPECTED
5249 );
5250
5251
5252 print_debuginfo(l_module_name, 'Changing the status of the '
5253 || 'payment instruction to '
5254 || l_instr_status
5255 );
5256 --END IF;
5257 /*
5258 * Fix for bug 5735030:
5259 *
5260 * Populate error message in output file so that
5261 * the user knows the cause of the failure even
5262 * if logging is turned off.
5263 */
5264 l_error_code := 'IBY_PMT_DOC_SING_LOCKED';
5265 FND_MESSAGE.SET_NAME('IBY', l_error_code);
5266
5267 FND_MESSAGE.SET_TOKEN('DOC_NAME',
5268 l_pmt_doc_name,
5269 FALSE);
5270
5271 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
5272
5273 /*
5274 * Return failure status.
5275 */
5276 x_return_status := -1;
5277
5278 l_error_code := 'IBY_PMT_DOC_SING_LOCKED';
5279 FND_MESSAGE.set_name('IBY', l_error_code);
5280
5281 FND_MESSAGE.SET_TOKEN('DOC_NAME',
5282 l_pmt_doc_name,
5283 FALSE);
5284
5285 FND_MSG_PUB.ADD;
5286
5287 FND_MSG_PUB.COUNT_AND_GET(
5288 p_count => x_msg_count,
5289 p_data => x_msg_data
5290 );
5291
5292 x_return_message := FND_MESSAGE.get;
5293
5294 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5295 print_debuginfo(l_module_name, 'EXIT');
5296 END IF;
5297 RETURN;
5298
5299
5300 END;
5301 IF (l_pmt_instr_id IS NOT NULL) THEN
5302 --IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5303 print_debuginfo(l_module_name, 'Payment document '
5304 || ''''
5305 || l_pmt_doc_name
5306 || ''''
5307 || ' with payment doc id '
5308 || p_payment_doc_id
5309 || ' has been locked by payment instruction '
5310 || l_pmt_instr_id,
5311 FND_LOG.LEVEL_UNEXPECTED
5312 );
5313
5314
5315 print_debuginfo(l_module_name, 'Processing cannot continue '
5316 || 'because payment document is unavailable (locked).',
5317 FND_LOG.LEVEL_UNEXPECTED
5318 );
5319
5320 print_debuginfo(l_module_name, 'Changing the status of the '
5321 || 'payment instruction to '
5322 || l_instr_status
5323 );
5324 --END IF;
5325
5326 /*
5327 * Fix for bug 5735030:
5328 *
5329 * Populate error message in output file so that
5330 * the user knows the cause of the failure even
5331 * if logging is turned off.
5332 */
5333 l_error_code := 'IBY_INS_PMT_DOC_LOCKED_DETAIL';
5334 FND_MESSAGE.SET_NAME('IBY', l_error_code);
5335
5336 FND_MESSAGE.SET_TOKEN('THIS_INS_NUM',
5337 x_pmtInstrRec.payment_instruction_id,
5338 FALSE);
5339
5340 FND_MESSAGE.SET_TOKEN('PREV_INS_NUM',
5341 l_pmt_instr_id,
5342 FALSE);
5343
5344 FND_MESSAGE.SET_TOKEN('DOC_NAME',
5345 l_pmt_doc_name,
5346 FALSE);
5347
5348 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
5349
5350 /*
5351 * Return failure status.
5352 */
5353 x_return_status := -1;
5354
5355 l_error_code := 'IBY_INS_PMT_DOC_LOCKED';
5356 FND_MESSAGE.set_name('IBY', l_error_code);
5357
5358 FND_MESSAGE.SET_TOKEN('INS_ID',
5359 l_pmt_instr_id,
5360 FALSE);
5361
5362 FND_MSG_PUB.ADD;
5363
5364 FND_MSG_PUB.COUNT_AND_GET(
5365 p_count => x_msg_count,
5366 p_data => x_msg_data
5367 );
5368
5369 x_return_message := FND_MESSAGE.get;
5370
5371 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5372 print_debuginfo(l_module_name, 'EXIT');
5373 END IF;
5374 RETURN;
5375
5376 END IF;
5377
5378 /*
5379 * Log warnings if there is any missing/incomplete information.
5380 */
5381 IF (l_last_avail_check_num = -1) THEN
5382
5383 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5384 print_debuginfo(l_module_name, 'Warning: payment document id '
5385 || p_payment_doc_id
5386 || ' has no last available document number set. '
5387 || 'Assuming that infinite number of paper documents '
5388 || 'can be printed for this payment document.'
5389 );
5390 END IF;
5391
5392 END IF;
5393
5394 IF (l_last_used_check_num = 0) THEN
5395
5396 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5397 print_debuginfo(l_module_name, 'Warning: payment document id '
5398 || p_payment_doc_id
5399 || ' has last used document number set to zero. '
5400 || 'Assuming that no paper documents have yet '
5401 || 'been printed for this payment document.'
5402 );
5403 END IF;
5404
5405 END IF;
5406
5407
5408 /*
5409 * If user has explicitly provided a start number for check
5410 * numbering, we have to use it in our numbering logic.
5411 * This will only happen for single payments.
5412 *
5413 * For Build Program invoked numbering, we will always start
5414 * from the last issued check number on the payment document + 1.
5415 */
5416 IF (p_user_assgn_num IS NULL) THEN
5417
5418 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5419 print_debuginfo(l_module_name, 'User has not explicitly '
5420 || 'provided a check number to start numbering from. '
5421 || 'Numbering will start from last issued check number '
5422 || 'on check stock.'
5423 );
5424 END IF;
5425
5426 ELSE
5427
5428 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5429 print_debuginfo(l_module_name, 'User has explicitly '
5430 || 'provided start number for numbering: '
5431 || p_user_assgn_num
5432 );
5433 END IF;
5434
5435 /*
5436 * The code below uses the variable 'l_last_used_check_num'
5437 * as the starting number for check numbering. The numbering
5438 * will begin from l_last_used_check_num + 1.
5439 *
5440 * If the user has explicitly provided a start number for
5441 * numbering, we need to adjust the l_last_used_check_num
5442 * value accordingly.
5443 */
5444 l_last_used_check_num := p_user_assgn_num - 1;
5445
5446 END IF;
5447
5448 /*
5449 * Check if enough paper documents are available to complete
5450 * this payment instruction.
5451 *
5452 * Perform this check only if a value has been provided
5453 * for the last available document number. If no value is
5454 * set assume that an infinite number of checks can be
5455 * printed for this paper stock (payment document).
5456 */
5457 IF (l_last_avail_check_num <> -1) THEN
5458
5459 /*
5460 * Check if enough paper documents are available to complete
5461 * this payment instruction.
5462 */
5463 l_physical_stock_count := l_last_avail_check_num
5464 - l_last_used_check_num;
5465
5466 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5467 print_debuginfo(l_module_name, 'Available paper stock = '
5468 || l_physical_stock_count
5469 || ' for payment document name '
5470 || ''''
5471 || l_pmt_doc_name
5472 || ''''
5473 );
5474 END IF;
5475
5476 IF (l_physical_stock_count < l_paper_pmts_count) THEN
5477
5478 /*
5479 * Not enough paper stock is available to print
5480 * the checks for this payment instruction.
5481 *
5482 * Set the status of the payment instruction to
5483 * failed.
5484 */
5485
5486 print_debuginfo(l_module_name, 'Deferring payment '
5487 || 'instruction print '
5488 || x_pmtInstrRec.payment_instruction_id
5489 || ' because of insufficient paper stock.',
5490 FND_LOG.LEVEL_UNEXPECTED
5491 );
5492
5493
5494 x_pmtInstrRec.payment_instruction_status := l_instr_status;
5495
5496 l_error_code := 'IBY_INS_INSUFFICIENT_PAY_DOCS';
5497
5498 FND_MESSAGE.set_name('IBY', l_error_code);
5499
5500 FND_MESSAGE.SET_TOKEN('NUM_AVAIL',
5501 l_physical_stock_count,
5502 FALSE);
5503
5504 l_token_rec.token_name := 'NUM_AVAIL';
5505 l_token_rec.token_value := l_physical_stock_count;
5506 x_insTokenTab(x_insTokenTab.COUNT + 1) := l_token_rec;
5507
5508 FND_MESSAGE.SET_TOKEN('NUM_REQD',
5509 l_paper_pmts_count,
5510 FALSE);
5511
5512 l_token_rec.token_name := 'NUM_REQD';
5513 l_token_rec.token_value := l_paper_pmts_count;
5514 x_insTokenTab(x_insTokenTab.COUNT + 1) := l_token_rec;
5515
5516 /*
5517 * Once we fail a pmt instruction, we must add a
5518 * corresponding error message to the errors table.
5519 */
5520 IBY_PAYINSTR_UTILS_PKG.createErrorRecord(
5521 x_pmtInstrRec.payment_instruction_id,
5522 x_pmtInstrRec.payment_instruction_status,
5523 l_error_code,
5524 FND_MESSAGE.get,
5525 'N',
5526 l_instr_err_rec
5527 );
5528
5529 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
5530 l_instr_err_rec,
5531 x_instrErrorTab,
5532 x_insTokenTab
5533 );
5534
5535 /* add error message to msg stack */
5536 FND_MSG_PUB.ADD;
5537
5538 FND_MSG_PUB.COUNT_AND_GET(
5539 p_count => x_msg_count,
5540 p_data => x_msg_data
5541 );
5542
5543 /* set error message to return to caller */
5544 x_return_message := FND_MESSAGE.get;
5545
5546 /*
5547 * Now, raise an exception. This will be caught
5548 * in the exception handler below and the changes
5549 * made to the DB in this transaction
5550 * will be rolled back.
5551 */
5552 APP_EXCEPTION.RAISE_EXCEPTION;
5553
5554 ELSE
5555
5556 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5557 print_debuginfo(l_module_name, 'Sufficient paper stock '
5558 || 'is available to print this instruction.'
5559 );
5560 END IF;
5561
5562 END IF;
5563
5564 END IF; -- l_last_avail_check_num <> -1
5565
5566 /*
5567 * If sufficient paper stock is available, we will be using
5568 * up the paper stock by assigning it to the available
5569 * paper payments. Therefore, update the last used paper
5570 * stock number in CE_PAYMENT_DOCUMENTS.
5571 *
5572 * That way if another instance of the payment instruction
5573 * creation program is operating concurrently, it will
5574 * be blocked by the SELECT .. FOR UPDATE statement in
5575 * this method.
5576 *
5577 */
5578 l_anticipated_last_check_num := l_last_used_check_num
5579 + l_paper_pmts_count;
5580
5581 /*
5582 * We will be printing the checks starting with
5583 * paper doc num 'l_last_used_check_num + 1' and
5584 * ending with paper doc num l_anticipated_last_check_num.
5585 *
5586 * Check whether all the paper doc numbers within this
5587 * range are available. We cannot have any gaps in the
5588 * numbering because checks have to be numbered
5589 * contiguously.
5590 */
5591 l_nos_avlbl_flag := isContigPaperNumAvlbl(
5592 p_payment_doc_id,
5593 l_last_used_check_num + 1,
5594 l_anticipated_last_check_num
5595 );
5596
5597 IF (l_nos_avlbl_flag = FALSE) THEN
5598
5599 --IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5600 print_debuginfo(l_module_name, 'Contiguous paper stock '
5601 || 'is not available for printing payment instruction '
5602 || x_pmtInstrRec.payment_instruction_id
5603 );
5604 --END IF;
5605
5606 /*
5607 * Return failure status.
5608 */
5609 x_return_status := -1;
5610
5611 l_error_code := 'IBY_INS_NSF_CONTIG_NUM';
5612 FND_MESSAGE.set_name('IBY', l_error_code);
5613
5614 FND_MESSAGE.SET_TOKEN('NUM_PMT_DOCS',
5615 l_paper_pmts_count,
5616 FALSE);
5617
5618 FND_MSG_PUB.ADD;
5619
5620 FND_MSG_PUB.COUNT_AND_GET(
5621 p_count => x_msg_count,
5622 p_data => x_msg_data
5623 );
5624
5625 x_return_message := FND_MESSAGE.get;
5626
5627 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5628 print_debuginfo(l_module_name, 'EXIT');
5629 END IF;
5630 RETURN;
5631
5632 END IF;
5633
5634 /*
5635 * A paper document number (check number) is considered
5636 * unused if it is not present in IBY_USED_PAYMENT_DOCS
5637 * table.
5638 *
5639 * This logic will work when check numbering is invoked
5640 * from the Build Program. In this case, the numbering
5641 * logic always starts with the last issued doc number + 1
5642 * when assigning new check numbers. Therefore, the
5643 * check numbers will always be unique (unused).
5644 *
5645 * However, when check numbering is invoked for single
5646 * payments, the user is allowed to provide the start
5647 * number for check numbering. It is possible that
5648 * a payment has already been numbered with the user
5649 * provided start number, but this paper document may
5650 * not yet have been inserted into the IBY_USED_PAYMENT_DOCS
5651 * table (because the user has not yet confirmed the
5652 * payment).
5653 *
5654 * Therefore, for single payments, when the user provides
5655 * the start number for check numbering, we will have to
5656 * verify that the provided number is unused by checking
5657 * the paper document number on existing payments.
5658 */
5659 IF (p_user_assgn_num IS NOT NULL) THEN
5660
5661 l_used_flag := isPaperNosUsedOnExistPmt(
5662 p_payment_doc_id,
5663 l_last_used_check_num + 1,
5664 l_anticipated_last_check_num);
5665
5666 IF (l_used_flag = TRUE) THEN
5667
5668 -- IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5669 print_debuginfo(l_module_name, 'Paper document number(s) '
5670 || 'generated after numbering are invalid (already used). '
5671 || 'User needs to provide a new start number or use '
5672 || 'the defaulted start number.'
5673 );
5674 -- END IF;
5675
5676 /*
5677 * Return failure status.
5678 */
5679 x_return_status := -1;
5680
5681 l_error_code := 'IBY_INS_ALREADY_USED_NUM';
5682 FND_MESSAGE.set_name('IBY', l_error_code);
5683
5684 FND_MSG_PUB.ADD;
5685
5686 FND_MSG_PUB.COUNT_AND_GET(
5687 p_count => x_msg_count,
5688 p_data => x_msg_data
5689 );
5690
5691 x_return_message := FND_MESSAGE.get;
5692
5693 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5694 print_debuginfo(l_module_name, 'EXIT');
5695 END IF;
5696 RETURN;
5697
5698 ELSE
5699
5700 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5701 print_debuginfo(l_module_name, 'Paper document number(s) '
5702 || 'generated after numbering are unused. '
5703 );
5704 END IF;
5705
5706 END IF;
5707
5708 END IF;
5709
5710 /*
5711 * For single payments, the payment document should
5712 * not be locked (see bug 4597718).
5713 */
5714 IF (l_single_pmt_flag = TRUE) THEN
5715
5716 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5717 print_debuginfo(l_module_name, 'This is a single payment. '
5718 || 'Payment document will not be locked ..'
5719 );
5720 END IF;
5721
5722 /*
5723 * Update the check stock to reflect the latest used
5724 * check number.
5725 */
5726 UPDATE
5727 CE_PAYMENT_DOCUMENTS
5728 SET
5729 last_issued_document_number = l_anticipated_last_check_num
5730 WHERE
5731 payment_document_id = p_payment_doc_id
5732 AND
5733 last_issued_document_number < l_anticipated_last_check_num
5734 ;
5735
5736 ELSE
5737
5738 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5739 print_debuginfo(l_module_name, 'This is not a single payment. '
5740 || 'Payment document will be locked ..'
5741 );
5742 END IF;
5743
5744 /*
5745 * Update the check stock to reflect the latest used
5746 * check number, and lock the check stock.
5747 */
5748 UPDATE
5749 CE_PAYMENT_DOCUMENTS
5750 SET
5751 last_issued_document_number = l_anticipated_last_check_num,
5752 payment_instruction_id = x_pmtInstrRec.payment_instruction_id
5753 WHERE
5754 payment_document_id = p_payment_doc_id
5755 ;
5756
5757 END IF;
5758
5759 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5760 print_debuginfo(l_module_name, 'Finished updating the last '
5761 || 'available check number in CE_PAYMENT_DOCUMENTS. '
5762 || 'Current last check number: '
5763 || l_anticipated_last_check_num
5764 );
5765 END IF;
5766
5767 /* uncomment for debug purposes */
5768 --print_debuginfo(l_module_name, 'x_dummyPaperPmtsTab.COUNT: '
5769 -- || x_dummyPaperPmtsTab.COUNT);
5770 --print_debuginfo(l_module_name, 'x_paperPmtsTab.COUNT: '
5771 -- || x_paperPmtsTab.COUNT);
5772
5773
5774 /*
5775 * Commenting below code as we do not have setup
5776 * or overflow documents for Electronic Processing type
5777 * of payments.
5778 */
5779 /*
5780 * Assign contiguous check numbers to the setup checks.
5781 * These are dummy checks that are printed at the
5782 * beginning of the payment instruction print run.
5783 */
5784 /*IF (x_dummyPaperPmtsTab.COUNT <> 0) THEN
5785
5786 FOR i in x_dummyPaperPmtsTab.FIRST .. x_dummyPaperPmtsTab.LAST LOOP
5787
5788 IF (x_dummyPaperPmtsTab(i).payment_status = 'VOID_BY_SETUP') THEN
5789
5790 l_last_used_check_num := l_last_used_check_num + 1;
5791 x_dummyPaperPmtsTab(i).paper_document_number
5792 := l_last_used_check_num;
5793
5794 END IF;
5795
5796 END LOOP; -- for all setup payments in x_dummyPaperPmtsTab
5797
5798 END IF;*/
5799
5800 /* assign check number to paper payment */
5801 FOR i in x_paperPmtsTab.FIRST .. x_paperPmtsTab.LAST LOOP
5802
5803 l_last_used_check_num := l_last_used_check_num + 1;
5804 x_paperPmtsTab(i).check_number := l_last_used_check_num;
5805
5806 END LOOP; -- for all pmts in x_paperPmtsTab
5807
5808 /*
5809 * Final check:
5810 *
5811 * If all paper payments (including real payments, setup payments
5812 * and overflow payments) have been assigned check numbers
5813 * correctly, then the number of check numbers used up should
5814 * match the total paper payments count.
5815 *
5816 * If the two don't match, it means that some check numbers were
5817 * unassigned, or multiply assigned. In either case, abort the
5818 * program. This check will reveal any bugs in this method.
5819 */
5820 IF (l_anticipated_last_check_num <> l_last_used_check_num) THEN
5821
5822
5823 print_debuginfo(l_module_name, 'Check numbers were not '
5824 || 'properly assigned. '
5825 || 'Anticipated last used check number: '
5826 || l_anticipated_last_check_num
5827 || '. Actual last used check number: '
5828 || l_last_used_check_num
5829 || '. Deferring print for payment instruction '
5830 || x_pmtInstrRec.payment_instruction_id,
5831 FND_LOG.LEVEL_UNEXPECTED
5832 );
5833
5834
5835 x_pmtInstrRec.payment_instruction_status := l_instr_status;
5836
5837 l_error_code := 'IBY_INS_NUMBERING_ERR_1';
5838
5839 FND_MESSAGE.set_name('IBY', l_error_code);
5840
5841 FND_MESSAGE.SET_TOKEN('NUM_CALC',
5842 l_anticipated_last_check_num,
5843 FALSE);
5844
5845 l_token_rec.token_name := 'NUM_CALC';
5846 l_token_rec.token_value := l_anticipated_last_check_num;
5847 x_insTokenTab(x_insTokenTab.COUNT + 1) := l_token_rec;
5848
5849 FND_MESSAGE.SET_TOKEN('NUM_ACTU',
5850 l_last_used_check_num,
5851 FALSE);
5852
5853 l_token_rec.token_name := 'NUM_ACTU';
5854 l_token_rec.token_value := l_last_used_check_num;
5855 x_insTokenTab(x_insTokenTab.COUNT + 1) := l_token_rec;
5856
5857 /*
5858 * Once we fail a pmt instruction, we must add a
5859 * corresponding error message to the errors table.
5860 */
5861 IBY_PAYINSTR_UTILS_PKG.createErrorRecord(
5862 x_pmtInstrRec.payment_instruction_id,
5863 x_pmtInstrRec.payment_instruction_status,
5864 l_error_code,
5865 FND_MESSAGE.get,
5866 'N',
5867 l_instr_err_rec
5868 );
5869
5870 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
5871 l_instr_err_rec,
5872 x_instrErrorTab,
5873 x_insTokenTab
5874 );
5875
5876 /* add error msg to message stack */
5877 FND_MSG_PUB.ADD;
5878
5879 FND_MSG_PUB.COUNT_AND_GET(
5880 p_count => x_msg_count,
5881 p_data => x_msg_data
5882 );
5883
5884 /* set error message to return to caller */
5885 x_return_message := FND_MESSAGE.get;
5886
5887 APP_EXCEPTION.RAISE_EXCEPTION;
5888
5889 END IF;
5890
5891 /*
5892 * Return success status.
5893 */
5894 x_return_message := 'SUCCESS';
5895 x_return_status := 0;
5896
5897 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5898 print_debuginfo(l_module_name, 'EXIT');
5899 END IF;
5900
5901 EXCEPTION
5902 WHEN OTHERS THEN
5903
5904 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5905 print_debuginfo(l_module_name, 'Exception occured when '
5906 || 'performing document numbering. '
5907 );
5908 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
5909 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
5910 END IF;
5911 /*
5912 * Rollback any DB changes made in this method.
5913 */
5914 ROLLBACK;
5915
5916 /*
5917 * Return error status to caller.
5918 * The error message would have already been set.
5919 */
5920 x_return_status := -1;
5921
5922 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5923 print_debuginfo(l_module_name, 'EXIT');
5924 END IF;
5925
5926 END assignElectronicCheckNumbers;
5927
5928
5929 END IBY_CHECKNUMBER_PUB;
[Home] [Help]