1 PACKAGE BODY IBY_CHECKNUMBER_PUB AS
2 /*$Header: ibyckprb.pls 120.64.12010000.3 2008/12/01 11:06:32 visundar ship $*/
3
4 --
5 -- Declare global variables
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_CHECKNUMBER_PUB';
8
9 --
10 -- List of instruction statuses that are used / set in this
11 -- module (payment instruction creation flow).
12 --
13 -- This module will only process payment instructions that are in
14 -- 'created' status.
15 --
16 -- If there were any problems when numbering the payment instructions
17 -- this module will set the status of the payment instruction to
18 -- 'printing deferred' status and the check printing module has to
19 -- be called again on this payment instruction.
20 --
21 -- This module will never set the status of a payment instruction to
22 -- 'creation error' because that can only be done by the PICP.
23 --
24 INS_STATUS_CREATED CONSTANT VARCHAR2(100) := 'CREATED';
25 INS_STATUS_PRINT_DEFR CONSTANT VARCHAR2(100) := 'PRINTING_DEFERRED';
26 INS_STATUS_READY_TO_PRINT CONSTANT VARCHAR2(100) :=
27 'CREATED_READY_FOR_PRINTING';
28 INS_STATUS_READY_TO_FORMAT CONSTANT VARCHAR2(100) :=
29 'CREATED_READY_FOR_FORMATTING';
30
31 --
32 -- List of payment statuses that are used / set in this
33 -- module (payment instruction creation flow).
34 --
35 PMT_STATUS_CREATED CONSTANT VARCHAR2(100) := 'CREATED';
36 PMT_STATUS_INS_CREATED CONSTANT VARCHAR2(100) := 'INSTRUCTION_CREATED';
37
38 --
39 -- List of document statuses that are used / set in this
40 -- module (payment instruction creation flow).
41 --
42 DOC_STATUS_PAY_CREATED CONSTANT VARCHAR2(100) := 'PAYMENT_CREATED';
43
44 -- Transaction types (for inserting into IBY_TRANSACTION_ERRORS table)
45 TRXN_TYPE_INSTR CONSTANT VARCHAR2(100) := 'INSTRUCTION';
46 TRXN_TYPE_PMT CONSTANT VARCHAR2(100) := 'PAYMENT';
47
48 --
49 -- List of process ciompletion statuses that are returned to
50 -- the caller after completing this flow.
51 --
52 PROCESS_STATUS_SUCCESS CONSTANT VARCHAR2(100) := 'SUCCESS';
53 PROCESS_STATUS_FAILED CONSTANT VARCHAR2(100) := 'FAILURE';
54
55 --
56 -- Forward declarations
57 --
58 PROCEDURE print_debuginfo(
59 p_module IN VARCHAR2,
60 p_debug_text IN VARCHAR2,
61 p_debug_level IN VARCHAR2 DEFAULT FND_LOG.LEVEL_STATEMENT
62 );
63
64 /*--------------------------------------------------------------------
65 | NAME:
66 | performCheckNumbering
67 |
68 | PURPOSE:
69 | Prints the given payment instruction on the provided paper stock
70 | (payment document).
71 |
72 |
73 | PARAMETERS:
74 | IN
75 |
76 |
77 | OUT
78 |
79 |
80 | RETURNS:
81 |
82 | NOTES:
83 |
84 |
85 *---------------------------------------------------------------------*/
86 PROCEDURE performCheckNumbering(
87 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.
88 payment_instruction_id%TYPE,
89 p_pmt_document_id IN CE_PAYMENT_DOCUMENTS.
90 payment_document_id%TYPE,
91 p_user_assgn_num IN IBY_PAYMENTS_ALL.
92 paper_document_number%TYPE,
93 x_return_status IN OUT NOCOPY VARCHAR2,
94 x_return_message IN OUT NOCOPY VARCHAR2,
95 x_msg_count IN OUT NOCOPY NUMBER,
96 x_msg_data IN OUT NOCOPY VARCHAR2
97 )
98 IS
99
100 l_module_name VARCHAR2(200) := G_PKG_NAME || '.performCheckNumbering';
101
102 l_ret_status NUMBER := -1;
103 l_ret_message VARCHAR2(2000);
104
105 l_pmtInstrRec IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE;
106
107 l_pmtsInInstrRec IBY_PAYINSTR_PUB.pmtsInPmtInstrRecType;
108 l_pmtsInInstrTab IBY_PAYINSTR_PUB.pmtsInpmtInstrTabType;
109
110 l_error_code VARCHAR2(500);
111
112 l_instruction_id NUMBER(15);
113
114 /*
115 * These two are used for holding dummy payments and dummy
116 * documents that are associated with setup and overflow
117 * documents.
118 */
119 l_dummy_pmts_tab IBY_PAYGROUP_PUB.paymentTabType;
120 l_dummy_docs_tab docsTabType;
121 l_overflow_docs_tab overflowDocsTabType;
122
123 l_instr_err_tab IBY_VALIDATIONSETS_PUB.docErrorTabType;
124 l_err_tokens_tab IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType;
125
126 l_pkg_name CONSTANT VARCHAR2(100) := 'AP_AWT_CALLOUT_PKG';
127
128 l_callout_name VARCHAR2(500);
129 l_stmt VARCHAR2(1000);
130
131 /* used in invocation of callout procedure */
132 l_api_version CONSTANT NUMBER := 1.0;
133 l_msg_count NUMBER;
134 l_msg_data VARCHAR2(2000);
135
136 /*
137 * Cursor to pick up the payments (and related fields)
138 * of a given payment instruction.
139 */
140 CURSOR c_pmts_in_instruction (p_instr_id IBY_PAY_INSTRUCTIONS_ALL.
141 payment_instruction_id%TYPE)
142 IS
143 SELECT
144 pmt.payment_id,
145 instr.payment_instruction_id,
146 pmt.payment_amount,
147 pmt.payment_currency_code,
148 pmt.payment_status,
149 pmt.payment_profile_id,
150 prof.processing_type,
151 -1, /* pmt_details_len */
152 -1, /* document_count */
153 pmt.separate_remit_advice_req_flag,
154 pmt.paper_document_number
155 FROM
156 IBY_PAYMENTS_ALL pmt,
157 IBY_PAY_INSTRUCTIONS_ALL instr,
158 IBY_PAYMENT_PROFILES prof
159 WHERE
160 instr.payment_instruction_id = p_instr_id AND
161 instr.payment_instruction_id = pmt.payment_instruction_id AND
162 instr.payment_profile_id = pmt.payment_profile_id AND
163 instr.payment_profile_id = prof.payment_profile_id AND
164 pmt.payment_status = PMT_STATUS_INS_CREATED
165
166 /*
167 * Fix for bug 5198523:
168 *
169 * Ordering the payments by payment reference number
170 * guarantees that the paper document numbers provided
171 * to these payments downstream also follow the same
172 * order.
173 */
174 ORDER BY pmt.payment_reference_number ASC
175 ;
176
177
178 /*
179 * Implementing the callout is optional for the calling app.
180 * If the calling app does not implement the hook, then
181 * the call to the hook will result in ORA-06576 error.
182 *
183 * There is no exception name associated with this code, so
184 * we create one called 'PROCEDURE_NOT_IMPLEMENTED'. If this
185 * exception occurs, it is not fatal: we log the error and
186 * proceed.
187 *
188 * If, on the other hand, the calling app implements the
189 * callout, but the callout throws an exception, it is fatal
190 * and we must abort the program (this will be caught
191 * in WHEN OTHERS block).
192 */
193 PROCEDURE_NOT_IMPLEMENTED EXCEPTION;
194 PRAGMA EXCEPTION_INIT(PROCEDURE_NOT_IMPLEMENTED, -6576);
195
196 BEGIN
197 print_debuginfo(l_module_name, 'ENTER');
198
199 print_debuginfo(l_module_name, 'Provided payment instruction id: '
200 || p_instruction_id);
201
202 /*
203 * Pick up all attributes of the given payment instruction
204 * from the IBY_PAY_INSTRUCTIONS_ALL table.
205 */
206 BEGIN
207
208 SELECT
209 payment_instruction_id,
210 payment_profile_id,
211 process_type,
212 payment_instruction_status,
213 payments_complete_code,
214 generate_sep_remit_advice_flag,
215 remittance_advice_created_flag,
216 regulatory_report_created_flag,
217 bill_payable_flag,
218 legal_entity_id,
219 payment_count,
220 positive_pay_file_created_flag,
221 print_instruction_immed_flag,
222 transmit_instr_immed_flag,
223 created_by,
224 creation_date,
225 last_updated_by,
226 last_update_date,
227 last_update_login,
228 object_version_number,
229 internal_bank_account_id,
230 pay_admin_assigned_ref_code,
231 transmission_date,
232 acknowledgement_date,
233 comments,
234 bank_assigned_ref_code,
235 org_id,
236 org_type,
237 payment_date,
238 payment_currency_code,
239 payment_service_request_id,
240 payment_function,
241 payment_reason_code,
242 rfc_identifier,
243 payment_reason_comments,
244 payment_document_id,
245 printer_name,
246 attribute_category,
247 attribute1,
248 attribute2,
249 attribute3,
250 attribute4,
251 attribute5,
252 attribute6,
253 attribute7,
254 attribute8,
255 attribute9,
256 attribute10,
257 attribute11,
258 attribute12,
259 attribute13,
260 attribute14,
261 attribute15
262 INTO
263 l_pmtInstrRec.payment_instruction_id,
264 l_pmtInstrRec.payment_profile_id,
265 l_pmtInstrRec.process_type,
266 l_pmtInstrRec.payment_instruction_status,
267 l_pmtInstrRec.payments_complete_code,
268 l_pmtInstrRec.generate_sep_remit_advice_flag,
269 l_pmtInstrRec.remittance_advice_created_flag,
270 l_pmtInstrRec.regulatory_report_created_flag,
271 l_pmtInstrRec.bill_payable_flag,
272 l_pmtInstrRec.legal_entity_id,
273 l_pmtInstrRec.payment_count,
274 l_pmtInstrRec.positive_pay_file_created_flag,
275 l_pmtInstrRec.print_instruction_immed_flag,
276 l_pmtInstrRec.transmit_instr_immed_flag,
277 l_pmtInstrRec.created_by,
278 l_pmtInstrRec.creation_date,
279 l_pmtInstrRec.last_updated_by,
280 l_pmtInstrRec.last_update_date,
281 l_pmtInstrRec.last_update_login,
282 l_pmtInstrRec.object_version_number,
283 l_pmtInstrRec.internal_bank_account_id,
284 l_pmtInstrRec.pay_admin_assigned_ref_code,
285 l_pmtInstrRec.transmission_date,
286 l_pmtInstrRec.acknowledgement_date,
287 l_pmtInstrRec.comments,
288 l_pmtInstrRec.bank_assigned_ref_code,
289 l_pmtInstrRec.org_id,
290 l_pmtInstrRec.org_type,
291 l_pmtInstrRec.payment_date,
292 l_pmtInstrRec.payment_currency_code,
293 l_pmtInstrRec.payment_service_request_id,
294 l_pmtInstrRec.payment_function,
295 l_pmtInstrRec.payment_reason_code,
296 l_pmtInstrRec.rfc_identifier,
297 l_pmtInstrRec.payment_reason_comments,
298 l_pmtInstrRec.payment_document_id,
299 l_pmtInstrRec.printer_name,
300 l_pmtInstrRec.attribute_category,
301 l_pmtInstrRec.attribute1,
302 l_pmtInstrRec.attribute2,
303 l_pmtInstrRec.attribute3,
304 l_pmtInstrRec.attribute4,
305 l_pmtInstrRec.attribute5,
306 l_pmtInstrRec.attribute6,
307 l_pmtInstrRec.attribute7,
308 l_pmtInstrRec.attribute8,
309 l_pmtInstrRec.attribute9,
310 l_pmtInstrRec.attribute10,
311 l_pmtInstrRec.attribute11,
312 l_pmtInstrRec.attribute12,
313 l_pmtInstrRec.attribute13,
314 l_pmtInstrRec.attribute14,
315 l_pmtInstrRec.attribute15
316 FROM
317 IBY_PAY_INSTRUCTIONS_ALL
318 WHERE
319 payment_instruction_id = p_instruction_id
320 ;
321
322 EXCEPTION
323
324 WHEN OTHERS THEN
325 /*
326 * This error condition will be handled below.
327 */
328 print_debuginfo(l_module_name, 'Exception occured when '
329 || 'retrieving details of payment instruction '
330 || p_instruction_id
331 );
332 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
333 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
334
335 END;
336
337 IF (l_pmtInstrRec.payment_instruction_id IS NULL) THEN
338
339 print_debuginfo(l_module_name, 'Provided payment instruction id '
340 || p_instruction_id
341 || ' not found in IBY_PAY_INSTRUCTIONS_ALL table. '
342 || 'Processing cannot continue. Aborting program.'
343 );
344
345 x_return_status := FND_API.G_RET_STS_ERROR;
346
347 l_error_code := 'IBY_INS_NOT_FOUND';
348 FND_MESSAGE.set_name('IBY', l_error_code);
349
350 FND_MESSAGE.SET_TOKEN('INS_ID',
351 p_instruction_id,
352 FALSE);
353
354 x_return_message := FND_MESSAGE.get;
355
356 print_debuginfo(l_module_name, 'EXIT');
357
358 RETURN;
359
360 END IF;
361
362 /*
363 * Only proceed if the payment instruction is in the
364 * 'created' status.
365 */
366 IF (l_pmtInstrRec.payment_instruction_status
367 <> INS_STATUS_CREATED AND
368 l_pmtInstrRec.payment_instruction_status
369 <> INS_STATUS_READY_TO_PRINT AND
370 l_pmtInstrRec.payment_instruction_status
371 <> INS_STATUS_READY_TO_FORMAT
372 ) THEN
373
374 print_debuginfo(l_module_name, 'Payment instruction id '
375 || p_instruction_id
376 || ' has status '
377 || l_pmtInstrRec.payment_instruction_status
378 || '. Payment instruction must be in '
379 || INS_STATUS_READY_TO_PRINT
380 || ' or '
381 || INS_STATUS_READY_TO_FORMAT
382 || ' for check numbering. '
383 || 'Processing cannot continue. Aborting program.'
384 );
385
386 x_return_status := FND_API.G_RET_STS_ERROR;
387
388 l_error_code := 'IBY_INS_NOT_NUM_STATUS';
389 FND_MESSAGE.set_name('IBY', l_error_code);
390
391 FND_MESSAGE.SET_TOKEN('INS_ID',
392 p_instruction_id,
393 FALSE);
394
395 FND_MESSAGE.SET_TOKEN('BAD_STATUS',
396 l_pmtInstrRec.payment_instruction_status,
397 FALSE);
398
399 FND_MESSAGE.SET_TOKEN('GOOD_STATUS1',
400 INS_STATUS_READY_TO_PRINT,
401 FALSE);
402
403 FND_MESSAGE.SET_TOKEN('GOOD_STATUS2',
404 INS_STATUS_READY_TO_FORMAT,
405 FALSE);
406
407 FND_MESSAGE.SET_TOKEN('GOOD_STATUS3',
408 INS_STATUS_CREATED,
409 FALSE);
410
411 x_return_message := FND_MESSAGE.get;
412
413 print_debuginfo(l_module_name, 'EXIT');
414
415 RETURN;
416
417 ELSE
418
419 /*
420 * If we reached here, it means that the given payment
421 * instruction is in an appropriate status for numbering.
422 *
423 * SPECIAL CASE:
424 *
425 * Check for a special case, where this payment instruction
426 * might have already been numbered earlier, but the format
427 * program crashed or raised an exception. In that case, the
428 * status of the payment instruction would not have been
429 * changed.
430 *
431 * Such a payment instruction would re-enter the check
432 * numbering flow, but we must not renumber this instruction
433 * because we have already numbered it.
434 *
435 * If the specified payment document is already locked by the
436 * given payment instruction, then we know that this payment
437 * instruction has already been numbered and is re-entering
438 * the check numbering flow.
439 *
440 * Simply return success and exit.
441 */
442 BEGIN
443
444 SELECT
445 ce.payment_instruction_id
446 INTO
447 l_instruction_id
448 FROM
449 CE_PAYMENT_DOCUMENTS ce
450 WHERE
451 ce.payment_document_id = p_pmt_document_id
452 ;
453
454 IF (l_instruction_id = p_instruction_id) THEN
455
456 print_debuginfo(l_module_name, 'Payment instruction '
457 || p_instruction_id
458 || ' has already been numbered. Returning '
459 || 'success response ..'
460 );
461
462 x_return_status := FND_API.G_RET_STS_SUCCESS;
463 x_return_message := 'SUCCESS';
464
465 print_debuginfo(l_module_name, 'EXIT');
466
467 RETURN;
468
469 ELSE
470
471 print_debuginfo(l_module_name, 'Payment instruction '
472 || p_instruction_id
473 || ' has not been previously numbered.'
474 );
475
476 END IF;
477
478
479 EXCEPTION
480 WHEN OTHERS THEN
481
482 print_debuginfo(l_module_name, 'Exception occured when '
483 || 'getting details of payment document id '
484 || p_pmt_document_id
485 || ' from CE_PAYMENT_DOCUMENTS table.',
486 FND_LOG.LEVEL_UNEXPECTED
487 );
488
489 print_debuginfo(l_module_name, 'SQL code: '
490 || SQLCODE, FND_LOG.LEVEL_UNEXPECTED);
491 print_debuginfo(l_module_name, 'SQL err msg: '
492 || SQLERRM, FND_LOG.LEVEL_UNEXPECTED);
493
494 /*
495 * Propogate exception to caller.
496 */
497 RAISE;
498
499 END;
500
501 END IF;
502
503 /*
504 * Pick up all payments of the given payment instruction
505 * from the IBY_PAYMENTS_ALL table.
506 */
507 OPEN c_pmts_in_instruction(p_instruction_id);
508 FETCH c_pmts_in_instruction BULK COLLECT INTO l_pmtsInInstrTab;
509 CLOSE c_pmts_in_instruction;
510
511 IF (l_pmtsInInstrTab.COUNT = 0) THEN
512
513 print_debuginfo(l_module_name, 'Cannot retrieve payments '
514 || 'and profile details for the provided payment '
515 || 'instruction id. Processing cannot continue. '
516 || 'Aborting program.'
517 );
518
519 x_return_status := FND_API.G_RET_STS_ERROR;
520
521 l_error_code := 'IBY_INS_PMTS_NOT_FOUND';
522 FND_MESSAGE.set_name('IBY', l_error_code);
523
524 FND_MESSAGE.SET_TOKEN('INS_ID',
525 p_instruction_id,
526 FALSE);
527
528 x_return_message := FND_MESSAGE.get;
529
530 print_debuginfo(l_module_name, 'EXIT');
531
532 RETURN;
533
534 END IF;
535
536 /* populate the document count associated with each payment */
537 populateDocumentCount(l_pmtsInInstrTab);
538
539 /*
540 * Calculate the number of setup and overflow documents for
541 * this payment instruction.
542 *
543 * Then provide all the payments with check numbers.
544 */
545 processPaperPayments(
546 p_pmt_document_id, p_user_assgn_num, l_pmtInstrRec,
547 l_pmtsInInstrTab, l_dummy_pmts_tab, l_dummy_docs_tab,
548 l_overflow_docs_tab, l_instr_err_tab, l_err_tokens_tab,
549 l_ret_status, l_ret_message, x_msg_count, x_msg_data);
550
551 /*
552 * Now, that we have completed check printing, update
553 * the payments of the payment instruction with check
554 * numbers.
555 *
556 * Insert the created dummy documents (setup and overflow
557 * documents) into the database.
558 *
559 * Finally, change the status of the payment instruction
560 * to 'submitted for printing'.
561 */
562 IF (l_ret_status = 0) THEN
563
564 performDBUpdates(p_instruction_id,
565 l_pmtsInInstrTab, l_dummy_pmts_tab,
566 l_dummy_docs_tab, l_overflow_docs_tab,
567 l_instr_err_tab, l_err_tokens_tab);
568
569 ELSE
570
571 print_debuginfo(l_module_name, 'Check numbering process '
572 || 'did not succeed. Raising exception ..',
573 FND_LOG.LEVEL_UNEXPECTED
574 );
575
576 /*
577 * Raise an exception.
578 */
579 APP_EXCEPTION.RAISE_EXCEPTION;
580
581 END IF;
582
583 /*
584 * If we reached here, it means that the payments of the
585 * payment instruction have been numbered successfully
586 * and that the status of the payment instruction is now
587 * 'CREATED_READY_FOR_PRINTING' | 'CREATED_READY_FOR_FORMATTING'.
588 *
589 * After check numbering has been completed, invoke the withholding
590 * certificates hook. This is relevant only for AP, but we will
591 * call this hook blindly and let AP figure out whether there
592 * are any payments of interest for them.
593 */
594 l_callout_name := l_pkg_name || '.' || 'zx_witholdingCertificatesHook';
595
596 print_debuginfo(l_module_name, 'Attempting to call hook: '
597 || l_callout_name);
598
599 l_stmt := 'CALL '|| l_callout_name || '(:1, :2, :3, :4, :5, :6, :7, :8)';
600
601 BEGIN
602
603 EXECUTE IMMEDIATE
604 (l_stmt)
605 USING
606 IN p_instruction_id,
607 IN 'GENERATE',
608 IN l_api_version,
609 IN FND_API.G_FALSE,
610 IN FND_API.G_FALSE,
611 OUT x_return_status,
612 OUT l_msg_count,
613 OUT l_msg_data
614 ;
615
616 /*
617 * If the called procedure did not return success,
618 * raise an exception.
619 */
620 IF (x_return_status IS NULL OR
621 x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
622
623 print_debuginfo(l_module_name, 'Fatal: External app callout '''
624 || l_callout_name
625 || ''', returned failure status - '
626 || x_return_status
627 || '. Raising exception.',
628 FND_LOG.LEVEL_UNEXPECTED
629 );
630
631 l_error_code := 'IBY_INS_AWT_CERT_HOOK_FAILED';
632 FND_MESSAGE.set_name('IBY', l_error_code);
633
634 l_ret_message := FND_MESSAGE.get;
635
636 APP_EXCEPTION.RAISE_EXCEPTION;
637
638 END IF;
639
640 EXCEPTION
641
642 WHEN PROCEDURE_NOT_IMPLEMENTED THEN
643 print_debuginfo(l_module_name, 'Callout "' || l_callout_name
644 || '" not implemented by calling app - AP'
645 );
646
647 print_debuginfo(l_module_name, 'Skipping hook call.');
648
649 WHEN OTHERS THEN
650 print_debuginfo(l_module_name, 'Fatal: External app callout '''
651 || l_callout_name
652 || ''', generated exception.',
653 FND_LOG.LEVEL_UNEXPECTED
654 );
655
656 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
657 FND_LOG.LEVEL_UNEXPECTED);
658 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
659 FND_LOG.LEVEL_UNEXPECTED);
660
661 /*
662 * Propogate exception to caller.
663 */
664 RAISE;
665 END;
666
667 x_return_status := FND_API.G_RET_STS_SUCCESS;
668 x_return_message := 'SUCCESS';
669
670 print_debuginfo(l_module_name, 'Returning success response ..');
671
672 print_debuginfo(l_module_name, 'EXIT');
673
674 EXCEPTION
675 WHEN OTHERS THEN
676 print_debuginfo(l_module_name, 'Exception occured when '
677 || 'performing payment numbering. Payment numbering '
678 || 'process will be aborted.'
679 );
680 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
681 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
682
683 x_return_status := FND_API.G_RET_STS_ERROR;
684 x_return_message := l_ret_message;
685
686 print_debuginfo(l_module_name, 'EXIT');
687
688 END performCheckNumbering;
689
690 /*--------------------------------------------------------------------
691 | NAME:
692 | populateDocumentCount
693 |
694 | PURPOSE:
695 |
696 |
697 |
698 | PARAMETERS:
699 | IN
700 |
701 | OUT
702 |
703 |
704 | RETURNS:
705 |
706 | NOTES:
707 |
708 *---------------------------------------------------------------------*/
709 PROCEDURE populateDocumentCount(
710 x_pmtsInPmtInstrTab IN OUT NOCOPY IBY_PAYINSTR_PUB.pmtsInpmtInstrTabType
711 )
712 IS
713
714 l_module_name VARCHAR2(200) := G_PKG_NAME || '.populateDocumentCount';
715 l_docs_in_pmt_count docsInPmtCountTabType;
716
717 CURSOR c_document_count
718 IS
719 SELECT
720 count(*),
721 payment_id
722 FROM
723 IBY_DOCS_PAYABLE_ALL
724 WHERE
725 document_status = DOC_STATUS_PAY_CREATED
726 GROUP BY
727 payment_id
728 ;
729
730 BEGIN
731
732 OPEN c_document_count;
733 FETCH c_document_count BULK COLLECT INTO l_docs_in_pmt_count;
734 CLOSE c_document_count;
735
736 IF (l_docs_in_pmt_count.COUNT = 0) THEN
737
738 /*
739 * Normally this shouldn't happen.
740 */
741 print_debuginfo(l_module_name, 'No documents payable '
742 || 'were found in ''payments created'' status '
743 || 'though created payments exist. '
744 || 'Possible data corruption. Aborting ..',
745 FND_LOG.LEVEL_UNEXPECTED
746 );
747
748 APP_EXCEPTION.RAISE_EXCEPTION;
749
750 END IF;
751
752 FOR i in x_pmtsInPmtInstrTab.FIRST .. x_pmtsInPmtInstrTab.LAST LOOP
753
754 FOR j in l_docs_in_pmt_count.FIRST .. l_docs_in_pmt_count.LAST LOOP
755
756 IF (x_pmtsInPmtInstrTab(i).payment_id = l_docs_in_pmt_count(j).
757 payment_id) THEN
758
759 x_pmtsInPmtInstrTab(i).document_count :=
760 l_docs_in_pmt_count(j).doc_count;
761
762 print_debuginfo(l_module_name, 'Num docs for payment: '
763 || x_pmtsInPmtInstrTab(i).payment_id
764 || ' is: '
765 || x_pmtsInPmtInstrTab(i).document_count
766 );
767
768 /* exit inner loop */
769 EXIT;
770
771 END IF;
772
773 END LOOP;
774
775 END LOOP;
776
777 END populateDocumentCount;
778
779 /*--------------------------------------------------------------------
780 | NAME:
781 | updatePaymentInstructions
782 |
783 | PURPOSE:
784 | Performs an update of all created instructions from PLSQL
785 | table into IBY_PAY_INSTRUCTIONS_ALL table.
786 |
787 | The created instructions have already been inserted into
788 | IBY_PAY_INSTRUCTIONS_ALL after grouping (and before validation).
789 | So we only need to update certain fields of the instruction
790 | that have been changed after the grouping was performed.
791 |
792 | PARAMETERS:
793 | IN
794 |
795 |
796 | OUT
797 |
798 |
799 | RETURNS:
800 |
801 | NOTES:
802 |
803 *---------------------------------------------------------------------*/
804 PROCEDURE updatePaymentInstructions(
805 p_payInstrTab IN IBY_PAYINSTR_PUB.pmtInstrTabType
806 )
807 IS
808 l_module_name VARCHAR2(200) := G_PKG_NAME || '.updatePaymentInstructions';
809
810 BEGIN
811
812 print_debuginfo(l_module_name, 'ENTER');
813
814 /* Normally, this shouldn't happen */
815 IF (p_payInstrTab.COUNT = 0) THEN
816 print_debuginfo(l_module_name, 'No payment instructions'
817 || ' were found to update IBY_PAY_INSTRUCTIONS_ALL table.');
818 RETURN;
819 END IF;
820
821 FOR i in p_payInstrTab.FIRST..p_payInstrTab.LAST LOOP
822 UPDATE
823 IBY_PAY_INSTRUCTIONS_ALL
824 SET
825 payment_instruction_status = p_payInstrTab(i).
826 payment_instruction_status
827 WHERE
828 payment_instruction_id = p_payInstrTab(i).payment_instruction_id;
829 END LOOP;
830
831 print_debuginfo(l_module_name, 'EXIT');
832
833 END updatePaymentInstructions;
834
835 /*--------------------------------------------------------------------
836 | NAME:
837 | insertPaperDocuments
838 |
839 | PURPOSE:
840 |
841 |
842 |
843 | PARAMETERS:
844 | IN
845 |
846 |
847 | OUT
848 |
849 |
850 | RETURNS:
851 |
852 | NOTES:
853 |
854 *---------------------------------------------------------------------*/
855 PROCEDURE insertPaperDocuments(
856 p_paperPmtsTab IN IBY_PAYGROUP_PUB.paymentTabType,
857 p_setupDocsTab IN docsTabType,
858 p_overflowDocsTab IN overflowDocsTabType
859 )
860 IS
861 l_setup_pmts_tab IBY_PAYGROUP_PUB.paymentTabType;
862 l_overflow_pmts_tab IBY_PAYGROUP_PUB.paymentTabType;
863
864 l_module_name VARCHAR2(200) := G_PKG_NAME || '.insertPaperDocuments';
865 BEGIN
866
867 print_debuginfo(l_module_name, 'ENTER');
868
869 IF (p_paperPmtsTab.COUNT = 0) THEN
870 print_debuginfo(l_module_name, 'No paper documents were '
871 || 'provided for insert. Exiting ..'
872 );
873 print_debuginfo(l_module_name, 'EXIT');
874 RETURN;
875 END IF;
876
877 /*
878 * Split the p_paperPmtsTab to get the setup payments
879 * and overflow payments into separate data structures.
880 */
881 splitPaymentsByType(p_paperPmtsTab, l_setup_pmts_tab,
882 l_overflow_pmts_tab);
883
884 /*
885 * Handle setup payments.
886 */
887 IF (l_setup_pmts_tab.COUNT <> 0) THEN
888
889 print_debuginfo(l_module_name, 'Inserting '
890 || l_setup_pmts_tab.COUNT
891 || ' setup payments.'
892 );
893
894 /* setup payments can be bulk inserted */
895 FORALL i in l_setup_pmts_tab.FIRST..l_setup_pmts_tab.LAST
896 INSERT INTO IBY_PAYMENTS_ALL VALUES l_setup_pmts_tab(i);
897
898 print_debuginfo(l_module_name, 'Inserting '
899 || p_setupDocsTab.COUNT
900 || ' setup documents.'
901 );
902
903 /* documents related to setup payments can be bulk inserted */
904 FORALL i in p_setupDocsTab.FIRST..p_setupDocsTab.LAST
905 INSERT INTO IBY_DOCS_PAYABLE_ALL VALUES p_setupDocsTab(i);
906
907 END IF;
908
909 /*
910 * Handle overflow payments.
911 */
912 IF (l_overflow_pmts_tab.COUNT <> 0) THEN
913
914 print_debuginfo(l_module_name, 'Inserting '
915 || l_overflow_pmts_tab.COUNT
916 || ' overflow payments.'
917 );
918
919 /* overflow payments can be bulk inserted */
920 FORALL i in l_overflow_pmts_tab.FIRST..l_overflow_pmts_tab.LAST
921 INSERT INTO IBY_PAYMENTS_ALL VALUES l_overflow_pmts_tab(i);
922
923
924 /*
925 * Fix for bug 6765314:
926 *
927 * The overflow payment is a dummy payment that is related
928 * to a real payment. Copy the important attributes of the
929 * real payment onto the original payment. This will ensure
930 * that when the overflow payment is printed, it contains
931 * the payee name, payer name etc.
932 *
933 * The external bank account id field on the overflow
934 * payment actually contains the payment id of the
935 * real payment. See KLUDGE in performSpecialPaperHandling()
936 * for more information on this.
937 */
938 print_debuginfo(l_module_name, 'Updating '
939 || 'overflow payments with attributes '
940 || 'from related real payments.'
941 );
942
943 FOR i in l_overflow_pmts_tab.FIRST..l_overflow_pmts_tab.LAST LOOP
944
945 UPDATE
946 iby_payments_all overflow
947 SET
948 (
949 overflow.payment_method_code,
950 overflow.internal_bank_account_id,
951 overflow.org_id,
952 overflow.org_type,
953 overflow.legal_entity_id,
954 overflow.delivery_channel_code,
955 overflow.ext_payee_id,
956 overflow.payment_profile_id,
957 overflow.payee_party_id,
958 overflow.party_site_id,
959 overflow.supplier_site_id,
960 overflow.payment_reason_code,
961 overflow.payment_reason_comments,
962 overflow.payment_date,
963 overflow.remittance_message1,
964 overflow.remittance_message2,
965 overflow.remittance_message3,
966 overflow.payment_due_date,
967 overflow.beneficiary_party,
968 overflow.remit_to_location_id,
969 overflow.payee_name,
970 overflow.payee_address1,
971 overflow.payee_address2,
972 overflow.payee_address3,
973 overflow.payee_address4,
974 overflow.payee_city,
975 overflow.payee_postal_code,
976 overflow.payee_state,
977 overflow.payee_province,
978 overflow.payee_county,
979 overflow.payee_country,
980 overflow.org_name,
981 overflow.payer_legal_entity_name,
982 overflow.payee_party_name,
983 overflow.payer_party_site_name,
984 overflow.payee_address_concat,
985 overflow.beneficiary_name,
986 overflow.payer_party_number,
987 overflow.payee_party_number,
988 overflow.payee_alternate_name,
989 overflow.payee_site_alternate_name,
990 overflow.payee_supplier_number,
991 overflow.payee_first_party_reference,
992 overflow.address_source,
993 overflow.employee_address_code,
994 overflow.employee_person_id,
995 overflow.employee_payment_flag,
996 overflow.employee_address_id,
997 overflow.payer_party_id,
998 overflow.payer_location_id,
999 overflow.payee_supplier_id,
1000 overflow.payee_supplier_site_name,
1001 overflow.payee_supplier_site_alt_name
1002 ) =
1003 (
1004 SELECT
1005 real.payment_method_code,
1006 real.internal_bank_account_id,
1007 real.org_id,
1008 real.org_type,
1009 real.legal_entity_id,
1010 real.delivery_channel_code,
1011 real.ext_payee_id,
1012 real.payment_profile_id,
1013 real.payee_party_id,
1014 real.party_site_id,
1015 real.supplier_site_id,
1016 real.payment_reason_code,
1017 real.payment_reason_comments,
1018 real.payment_date,
1019 real.remittance_message1,
1020 real.remittance_message2,
1021 real.remittance_message3,
1022 real.payment_due_date,
1023 real.beneficiary_party,
1024 real.remit_to_location_id,
1025 real.payee_name,
1026 real.payee_address1,
1027 real.payee_address2,
1028 real.payee_address3,
1029 real.payee_address4,
1030 real.payee_city,
1031 real.payee_postal_code,
1032 real.payee_state,
1033 real.payee_province,
1034 real.payee_county,
1035 real.payee_country,
1036 real.org_name,
1037 real.payer_legal_entity_name,
1038 real.payee_party_name,
1039 real.payer_party_site_name,
1040 real.payee_address_concat,
1041 real.beneficiary_name,
1042 real.payer_party_number,
1043 real.payee_party_number,
1044 real.payee_alternate_name,
1045 real.payee_site_alternate_name,
1046 real.payee_supplier_number,
1047 real.payee_first_party_reference,
1048 real.address_source,
1049 real.employee_address_code,
1050 real.employee_person_id,
1051 real.employee_payment_flag,
1052 real.employee_address_id,
1053 real.payer_party_id,
1054 real.payer_location_id,
1055 real.payee_supplier_id,
1056 real.payee_supplier_site_name,
1057 real.payee_supplier_site_alt_name
1058 FROM
1059 iby_payments_all real
1060 WHERE
1061 real.payment_id = overflow.external_bank_account_id
1062 )
1063 WHERE overflow.payment_id = l_overflow_pmts_tab(i).payment_id
1064 ;
1065 END LOOP;
1066
1067 print_debuginfo(l_module_name, 'Updating '
1068 || p_overflowDocsTab.COUNT
1069 || ' overflow documents.'
1070 );
1071
1072 /*
1073 * For overflow payments, the documents are already
1074 * available in the IBY_DOCS_PAYABLE_ALL table. The
1075 * formatting payment id on these documents needs
1076 * to be updated to account for the overflow payments
1077 * (1 overflow payment = 1 printed void check).
1078 */
1079 FOR i in p_overflowDocsTab.FIRST..p_overflowDocsTab.LAST LOOP
1080
1081 UPDATE
1082 IBY_DOCS_PAYABLE_ALL
1083 SET
1084 formatting_payment_id =
1085 p_overflowDocsTab(i).format_payment_id
1086 WHERE
1087 document_payable_id = p_overflowDocsTab(i).doc_id;
1088
1089 END LOOP;
1090
1091 END IF;
1092
1093 print_debuginfo(l_module_name, 'EXIT');
1094
1095 END insertPaperDocuments;
1096
1097 /*--------------------------------------------------------------------
1098 | NAME:
1099 | splitPaymentsByType
1100 |
1101 | PURPOSE:
1102 |
1103 |
1104 |
1105 | PARAMETERS:
1106 | IN
1107 |
1108 |
1109 | OUT
1110 |
1111 |
1112 | RETURNS:
1113 |
1114 | NOTES:
1115 |
1116 *---------------------------------------------------------------------*/
1117 PROCEDURE splitPaymentsByType(
1118 p_paperPmtsTab IN IBY_PAYGROUP_PUB.paymentTabType,
1119 x_setupPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
1120 x_overflowPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType
1121 )
1122 IS
1123
1124 l_module_name VARCHAR2(200) := G_PKG_NAME || '.splitPaymentsByType';
1125
1126 BEGIN
1127
1128 print_debuginfo(l_module_name, 'ENTER');
1129
1130 IF (p_paperPmtsTab.COUNT = 0) THEN
1131
1132 print_debuginfo(l_module_name, 'No payments to split. Exiting ..');
1133 print_debuginfo(l_module_name, 'EXIT');
1134 RETURN;
1135
1136 END IF;
1137
1138 FOR i in p_paperPmtsTab.FIRST..p_paperPmtsTab.LAST LOOP
1139
1140 /* setup payments */
1141 IF (p_paperPmtsTab(i).payment_status = 'VOID_BY_SETUP') THEN
1142
1143 x_setupPmtsTab(x_setupPmtsTab.COUNT + 1)
1144 := p_paperPmtsTab(i);
1145
1146 /* overflow payments */
1147 ELSIF (p_paperPmtsTab(i).payment_status = 'VOID_BY_OVERFLOW') THEN
1148
1149 x_overflowPmtsTab(x_overflowPmtsTab.COUNT + 1)
1150 := p_paperPmtsTab(i);
1151
1152 /* normally, shouldn't come here */
1153 ELSE
1154 print_debuginfo(l_module_name, 'Ignoring payment with '
1155 || 'status '
1156 || p_paperPmtsTab(i).payment_status
1157 );
1158 END IF;
1159
1160 END LOOP;
1161
1162 print_debuginfo(l_module_name, 'Created '
1163 || x_setupPmtsTab.COUNT
1164 || ' setup payments and '
1165 || x_overflowPmtsTab.COUNT
1166 || ' overflow payments from '
1167 || p_paperPmtsTab.COUNT
1168 || ' provided payments.'
1169 );
1170
1171 print_debuginfo(l_module_name, 'EXIT');
1172
1173 END splitPaymentsByType;
1174
1175 /*--------------------------------------------------------------------
1176 | NAME:
1177 | performDBUpdates
1178 |
1179 | PURPOSE:
1180 |
1181 |
1182 |
1183 | PARAMETERS:
1184 | IN
1185 |
1186 |
1187 | OUT
1188 |
1189 |
1190 | RETURNS:
1191 |
1192 |
1193 | NOTES:
1194 |
1195 *---------------------------------------------------------------------*/
1196 PROCEDURE performDBUpdates(
1197 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.
1198 payment_instruction_id%TYPE,
1199 x_pmtsInPmtInstrTab IN OUT NOCOPY IBY_PAYINSTR_PUB.pmtsInpmtInstrTabType,
1200 x_dummyPaperPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
1201 x_setupDocsTab IN OUT NOCOPY docsTabType,
1202 x_overflowDocsTab IN OUT NOCOPY overflowDocsTabType,
1203 x_insErrorsTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1204 x_insTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
1205 trxnErrTokenTabType
1206 )
1207 IS
1208 l_module_name VARCHAR2(200) := G_PKG_NAME || '.performDBUpdates';
1209
1210 BEGIN
1211
1212 print_debuginfo(l_module_name, 'ENTER');
1213
1214 /*
1215 * Update the payments table by providing a check number to
1216 * each payment.
1217 */
1218 updatePmtsWithCheckNumbers(x_pmtsInPmtInstrTab);
1219
1220 /*
1221 * Insert the setup and overflow documents that have been
1222 * created as part of paper payments handing.
1223 */
1224 insertPaperDocuments(x_dummyPaperPmtsTab, x_setupDocsTab,
1225 x_overflowDocsTab);
1226
1227 /*
1228 * Insert any payment instruction errors into
1229 * IBY_TRANSACTION_ERRORS table.
1230 */
1231 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N', x_insErrorsTab,
1232 x_insTokenTab);
1233
1234 print_debuginfo(l_module_name, 'EXIT');
1235
1236 EXCEPTION
1237
1238 WHEN OTHERS THEN
1239 print_debuginfo(l_module_name, 'Fatal: Exception when updating '
1240 || 'payment instruction/payment status after payment '
1241 || 'instruction creation. All changes will be rolled back.',
1242 FND_LOG.LEVEL_UNEXPECTED
1243 );
1244 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
1245 FND_LOG.LEVEL_UNEXPECTED);
1246 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
1247 FND_LOG.LEVEL_UNEXPECTED);
1248
1249 /*
1250 * Propogate exception to caller.
1251 */
1252 RAISE;
1253
1254 END performDBUpdates;
1255
1256 /*--------------------------------------------------------------------
1257 | NAME:
1258 | processPaperPayments
1259 |
1260 | PURPOSE:
1261 |
1262 |
1263 |
1264 | PARAMETERS:
1265 | IN
1266 |
1267 |
1268 | OUT
1269 |
1270 |
1271 | RETURNS:
1272 |
1273 | NOTES:
1274 |
1275 *---------------------------------------------------------------------*/
1276 PROCEDURE processPaperPayments(
1277 p_pmt_document_id IN CE_PAYMENT_DOCUMENTS.payment_document_id%TYPE,
1278 p_user_assgn_num IN IBY_PAYMENTS_ALL.paper_document_number%TYPE,
1279 x_pmtInstrRec IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE,
1280 x_pmtsInPmtInstrTab IN OUT NOCOPY IBY_PAYINSTR_PUB.pmtsInpmtInstrTabType,
1281 x_dummyPaperPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
1282 x_setupDocsTab IN OUT NOCOPY docsTabType,
1283 x_overflowDocsTab IN OUT NOCOPY overflowDocsTabType,
1284 x_instrErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1285 x_insTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
1286 trxnErrTokenTabType,
1287 x_return_status IN OUT NOCOPY NUMBER,
1288 x_return_message IN OUT NOCOPY VARCHAR2,
1289 x_msg_count IN OUT NOCOPY NUMBER,
1290 x_msg_data IN OUT NOCOPY VARCHAR2
1291 )
1292 IS
1293
1294 l_module_name VARCHAR2(200) := G_PKG_NAME
1295 || '.processPaperPayments';
1296 l_processing_type VARCHAR2(100); -- PRINTED | ELECTRONIC
1297 l_num_printed_docs NUMBER(15);
1298 l_paper_stock_rec paperStockRecType;
1299 l_paper_stocks_tab paperStocksTabType;
1300 l_docs_count NUMBER;
1301
1302 l_error_code VARCHAR2(3000);
1303
1304 l_paper_special_docs_rec paperPmtsSpecialDocsRecType;
1305 l_paper_special_docs_tab paperPmtsSpecialDocsTabType;
1306
1307 l_pmtsInPmtInstrRec IBY_PAYINSTR_PUB.pmtsInpmtInstrRecType;
1308
1309 CURSOR c_paper_stock (p_pmt_doc_id IN CE_PAYMENT_DOCUMENTS.
1310 payment_document_id%TYPE)
1311 IS
1312 SELECT
1313 payment_document_id,
1314 payment_document_name,
1315 NVL(number_of_setup_documents, 0),
1316 number_of_lines_per_remit_stub
1317 FROM
1318 CE_PAYMENT_DOCUMENTS
1319 WHERE
1320 payment_document_id = p_pmt_doc_id
1321 ;
1322
1323 BEGIN
1324
1325 print_debuginfo(l_module_name, 'ENTER');
1326
1327 OPEN c_paper_stock (p_pmt_document_id);
1328 FETCH c_paper_stock BULK COLLECT INTO l_paper_stocks_tab;
1329 CLOSE c_paper_stock;
1330
1331 /*
1332 * There should be exactly one payment document linked
1333 * to a particular payment document name.
1334 */
1335 IF (l_paper_stocks_tab.COUNT <> 1) THEN
1336
1337 print_debuginfo(l_module_name, 'Unable to get matching payment '
1338 || 'document from CE_PAYMENT_DOCUMENTS for payment '
1339 || 'document id: '
1340 || p_pmt_document_id
1341 || '. Number of payment documents retrieved: '
1342 || l_paper_stocks_tab.COUNT
1343 );
1344
1345 x_return_status := -1;
1346
1347 l_error_code := 'IBY_INS_NO_PMT_DOC';
1348 FND_MESSAGE.set_name('IBY', l_error_code);
1349
1350 FND_MESSAGE.SET_TOKEN('PMT_DOC_ID',
1351 p_pmt_document_id,
1352 FALSE);
1353
1354 x_return_message := FND_MESSAGE.get;
1355
1356 print_debuginfo(l_module_name, 'EXIT');
1357 RETURN;
1358
1359 END IF;
1360
1361 /*
1362 * Since, payment instructions are grouped by
1363 * profile id, they are implicitly grouped
1364 * by processing type as well.
1365 *
1366 * pmtsInpmtInstrTabType will have multiple records for
1367 * a payment instruction (one record for each payment
1368 * in the instruction). All these payments will have the
1369 * same profile id, and the same procesing type
1370 * (because only payments with a specific profile id wil
1371 * be grouped into a payment instruction).
1372 *
1373 * Therefore, in order to determine the processing type
1374 * of a payment instruction, we simply need to retrieve
1375 * the processing type from any payment of that
1376 * instruction. We do this by retrieving the processing
1377 * type of the first payment of that instruction.
1378 */
1379 l_pmtsInPmtInstrRec := x_pmtsInPmtInstrTab(x_pmtsInPmtInstrTab.FIRST);
1380 l_processing_type := l_pmtsInPmtInstrRec.processing_type;
1381
1382 print_debuginfo(l_module_name, 'For payment instruction '
1383 || x_pmtInstrRec.payment_instruction_id
1384 || ', profile id: '
1385 || x_pmtInstrRec.payment_profile_id
1386 || ', processing type: '
1387 || l_processing_type
1388 || ', # pmts: '
1389 || x_pmtInstrRec.payment_count
1390 );
1391
1392 /*
1393 * If the processing type for this instruction is set
1394 * to 'PAPER', it means that this payment instruction
1395 * will be physically printed onto paper.
1396 *
1397 * Perform paper payment specific processing such as
1398 * calculating setup and overflow documents.
1399 */
1400 IF (l_processing_type = 'PRINTED') THEN
1401
1402 /* Get the attributes of the paper stock */
1403 /*
1404 * There will only be one paper stock linked to
1405 * a particular payment document name, so we can
1406 * simply take the first record from the fetched
1407 * paper stocks table.
1408 */
1409 l_paper_stock_rec := l_paper_stocks_tab(1);
1410
1411 IF (l_paper_stock_rec.doc_id IS NOT NULL) THEN
1412
1413 print_debuginfo(l_module_name, 'Paper stock matching '
1414 || 'profile id '
1415 || x_pmtInstrRec.payment_profile_id
1416 || ' has following attributes - Name: '
1417 || l_paper_stock_rec.doc_name
1418 || ', num setup docs: '
1419 || l_paper_stock_rec.num_setup_docs
1420 || ', num lines per stub: '
1421 || l_paper_stock_rec.num_lines_per_stub
1422 );
1423
1424 ELSE
1425
1426 print_debuginfo(l_module_name, 'Fatal: No paper stock '
1427 || 'linked to profile id '
1428 || x_pmtInstrRec.payment_profile_id
1429 || '. Payment instruction creation cannot continue. '
1430 || 'Aborting program ..'
1431 );
1432
1433 x_return_status := -1;
1434
1435 l_error_code := 'IBY_INS_NO_PAPER_STOCK';
1436 FND_MESSAGE.set_name('IBY', l_error_code);
1437
1438 FND_MESSAGE.SET_TOKEN('INS_ID',
1439 x_pmtInstrRec.payment_instruction_id,
1440 FALSE);
1441
1442 x_return_message := FND_MESSAGE.get;
1443
1444 print_debuginfo(l_module_name, 'EXIT');
1445 RETURN;
1446
1447 END IF;
1448
1449 /*
1450 * If we reached here it means paper stock
1451 * attributes were retrieved successfully
1452 * for the profile on the instruction.
1453 */
1454
1455 /*
1456 * Loop through all the payments for this
1457 * payment instruction, processing them one-by-one.
1458 */
1459 FOR j in x_pmtsInPmtInstrTab.FIRST .. x_pmtsInPmtInstrTab.LAST
1460 LOOP
1461
1462 print_debuginfo(l_module_name, 'Processing payment '
1463 || x_pmtsInPmtInstrTab(j).payment_id
1464 || ' for instruction '
1465 || x_pmtInstrRec.payment_instruction_id
1466 || ' ..'
1467 );
1468
1469 l_docs_count := getDocumentCountForPayment(
1470 x_pmtsInPmtInstrTab(j).payment_id,
1471 x_pmtsInPmtInstrTab
1472 );
1473
1474 IF (l_docs_count <= 0) THEN
1475 /*
1476 * A successful payment must be linked to at least
1477 * least one successful document payable. If not
1478 * there is a data consistency issue.
1479 *
1480 * Raise an alert and abort the program.
1481 */
1482 print_debuginfo(l_module_name, 'Fatal: No successful '
1483 || 'docs were found for successful pmt with id '
1484 || x_pmtsInPmtInstrTab(j).payment_id
1485 || '. Possible data corruption. '
1486 || 'Aborting program ..'
1487 );
1488
1489 x_return_status := -1;
1490
1491 l_error_code := 'IBY_INS_PMT_NO_DOCS';
1492 FND_MESSAGE.set_name('IBY', l_error_code);
1493
1494 FND_MESSAGE.SET_TOKEN('PMT_ID',
1495 x_pmtsInPmtInstrTab(j).payment_id,
1496 FALSE);
1497
1498 FND_MESSAGE.SET_TOKEN('INS_ID',
1499 x_pmtInstrRec.payment_instruction_id,
1500 FALSE);
1501
1502 x_return_message := FND_MESSAGE.get;
1503
1504 print_debuginfo(l_module_name, 'EXIT');
1505 RETURN;
1506
1507 END IF;
1508
1509 l_num_printed_docs := getNumPrintedDocsByFormula(
1510 l_docs_count,
1511 l_paper_stock_rec.
1512 num_lines_per_stub
1513 );
1514
1515 print_debuginfo(l_module_name, 'For payment id '
1516 || x_pmtsInPmtInstrTab(j).payment_id
1517 || ', docs count on payment: '
1518 || l_docs_count
1519 || ', lines per stub on stock: '
1520 || l_paper_stock_rec.num_lines_per_stub
1521 || ', calculated number of printed docs: '
1522 || l_num_printed_docs);
1523
1524 /*
1525 * Add the details about the setup docs and
1526 * overflow docs for this payment into the
1527 * special docs table. This special docs table
1528 * will be used for inserting dummy payments
1529 * into the IBY_DOCS_PAYABLE_ALL and
1530 * IBY_PAYMENTS_ALL tables to account for setup
1531 * and overflow checks.
1532 */
1533 l_paper_special_docs_rec.payment_id :=
1534 x_pmtsInPmtInstrTab(j).payment_id;
1535
1536 l_paper_special_docs_rec.instruction_id :=
1537 x_pmtsInPmtInstrTab(j).pay_instr_id;
1538
1539 /*
1540 * Only one printed document per payment will be a
1541 * valid payment document. The rest of the documents
1542 * will be overflow documents that will be void and
1543 * are only used to store the document payable
1544 * ids.
1545 */
1546 l_paper_special_docs_rec.num_overflow_docs :=
1547 l_num_printed_docs - 1;
1548
1549 /*
1550 * Number of setup documents to print is
1551 * specified in the paper stock set up.
1552 */
1553 l_paper_special_docs_rec.num_setup_docs :=
1554 l_paper_stock_rec.num_setup_docs;
1555
1556 /*
1557 * Lines per stub in used in populating
1558 * dummy documents payable for this
1559 * payment.
1560 */
1561 l_paper_special_docs_rec.num_lines_per_stub :=
1562 l_paper_stock_rec.num_lines_per_stub;
1563
1564 /* add record to dummy docs table */
1565 l_paper_special_docs_tab(l_paper_special_docs_tab.COUNT
1566 + 1) := l_paper_special_docs_rec;
1567
1568 END LOOP; -- for each payment in this instruction
1569
1570 /*
1571 * If we reached here, it means that 'num overflow docs'
1572 * and 'num setup docs' has been populated for each
1573 * paper payment in the l_paper_special_docs_tab table.
1574 *
1575 * Use this information to create appropriate number
1576 * of dummy payments (to insert into IBY_PAYMENTS_ALL table)
1577 * and corresponding documents (to insert into
1578 * IBY_DOCS_PAYABLE_ALL table).
1579 */
1580 performSpecialPaperDocHandling(
1581 l_paper_special_docs_tab,
1582 x_dummyPaperPmtsTab,
1583 x_setupDocsTab,
1584 x_overflowDocsTab
1585 );
1586
1587 /*
1588 * After setup and overflow payments have been
1589 * handled, we can start assigning physical check
1590 * numbers to the paper payments.
1591 *
1592 * The 'paper special docs tab' holds real payments
1593 * that have to be printed onto paper; the 'paper pmts
1594 * tab' holds the dummy payments that also have to
1595 * printed onto paper. So both types of payments
1596 * need to be provided with check numbers.
1597 */
1598 assignCheckNumbers(
1599 x_pmtInstrRec,
1600 l_paper_stock_rec.doc_id,
1601 p_user_assgn_num,
1602 l_paper_special_docs_tab,
1603 x_dummyPaperPmtsTab,
1604 x_instrErrorTab,
1605 x_insTokenTab,
1606 x_return_message,
1607 x_return_status,
1608 x_msg_count,
1609 x_msg_data
1610 );
1611
1612 IF (x_return_status = -1) THEN
1613
1614 /*
1615 * Return back the x_return status and
1616 * x_return_message values that we received
1617 * from assignCheckNumbers() call.
1618 */
1619 print_debuginfo(l_module_name, 'Check numbering did '
1620 || 'not succeed.'
1621 );
1622
1623 print_debuginfo(l_module_name, 'EXIT');
1624 RETURN;
1625
1626 END IF;
1627
1628 /*
1629 * l_paper_special_docs_tab contains list of paper
1630 * payments that now have check number assigned.
1631 *
1632 * But this data structure is a local object. We
1633 * have to copy back the check numbers into
1634 * x_pmtsInPmtInstrTab which will be used to
1635 * finally update the database.
1636 */
1637 FOR m in l_paper_special_docs_tab.FIRST ..
1638 l_paper_special_docs_tab.LAST LOOP
1639
1640 FOR p in x_pmtsInPmtInstrTab.FIRST ..
1641 x_pmtsInPmtInstrTab.LAST LOOP
1642
1643 IF (l_paper_special_docs_tab(m).payment_id
1644 = x_pmtsInPmtInstrTab(p).payment_id) THEN
1645
1646 x_pmtsInPmtInstrTab(p).check_number
1647 := l_paper_special_docs_tab(m).check_number;
1648
1649 END IF;
1650
1651 END LOOP; -- for p in x_pmtsInPmtInstrTab
1652
1653 END LOOP; -- for m in l_paper_special_docs_tab
1654
1655 ELSIF (l_processing_type = 'ELECTRONIC') THEN
1656
1657 print_debuginfo(l_module_name, 'Provided processing '
1658 || 'type : '
1659 || l_processing_type
1660 || ' does not require printed '
1661 || 'documents. '
1662 || 'No processing will be performed.'
1663 );
1664
1665 ELSE
1666
1667 print_debuginfo(l_module_name, 'Unknown processing '
1668 || 'type provided: '
1669 || l_processing_type
1670 || '. Aborting program ..'
1671 );
1672
1673 x_return_status := -1;
1674
1675 l_error_code := 'IBY_INS_UNK_PROC_TYPE';
1676 FND_MESSAGE.set_name('IBY', l_error_code);
1677
1678 FND_MESSAGE.SET_TOKEN('UNK_PROC_TYPE',
1679 l_processing_type,
1680 FALSE);
1681
1682 FND_MESSAGE.SET_TOKEN('INS_ID',
1683 x_pmtInstrRec.payment_instruction_id,
1684 FALSE);
1685
1686 x_return_message := FND_MESSAGE.get;
1687
1688 print_debuginfo(l_module_name, 'EXIT');
1689 RETURN;
1690
1691 END IF; -- if processing_type = 'PRINTED'
1692
1693 /*
1694 * If we reached here, return success.
1695 */
1696 print_debuginfo(l_module_name, 'Returning success response ..');
1697 x_return_status := 0;
1698 print_debuginfo(l_module_name, 'EXIT');
1699
1700 END processPaperPayments;
1701
1702 /*--------------------------------------------------------------------
1703 | NAME:
1704 | getDocumentCountForPayment
1705 |
1706 | PURPOSE:
1707 |
1708 |
1709 |
1710 | PARAMETERS:
1711 | IN
1712 |
1713 | OUT
1714 |
1715 |
1716 | RETURNS:
1717 |
1718 | NOTES:
1719 |
1720 *---------------------------------------------------------------------*/
1721 FUNCTION getDocumentCountForPayment(
1722 p_payment_id IN IBY_PAYMENTS_ALL.payment_id%TYPE,
1723 p_pmtsInPmtInstrTab IN IBY_PAYINSTR_PUB.pmtsInpmtInstrTabType
1724 )
1725 RETURN NUMBER
1726 IS
1727 l_doc_count NUMBER := -1;
1728
1729 BEGIN
1730
1731 FOR i in p_pmtsInPmtInstrTab.FIRST .. p_pmtsInPmtInstrTab.LAST LOOP
1732
1733 IF (p_pmtsInPmtInstrTab(i).payment_id = p_payment_id) THEN
1734
1735 l_doc_count := p_pmtsInPmtInstrTab(i).document_count;
1736 EXIT;
1737
1738 END IF;
1739
1740 END LOOP;
1741
1742 RETURN l_doc_count;
1743
1744 END getDocumentCountForPayment;
1745
1746 /*--------------------------------------------------------------------
1747 | NAME:
1748 | assignCheckNumbers
1749 |
1750 | PURPOSE:
1751 |
1752 |
1753 |
1754 | PARAMETERS:
1755 | IN
1756 |
1757 |
1758 | OUT
1759 |
1760 |
1761 | RETURNS:
1762 |
1763 |
1764 | NOTES:
1765 | This method will perform a COMMIT.
1766 |
1767 *---------------------------------------------------------------------*/
1768 PROCEDURE assignCheckNumbers(
1769 x_pmtInstrRec IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE,
1770 p_payment_doc_id IN CE_PAYMENT_DOCUMENTS.payment_document_id%TYPE,
1771 p_user_assgn_num IN IBY_PAYMENTS_ALL.paper_document_number%TYPE,
1772 x_paperPmtsTab IN OUT NOCOPY paperPmtsSpecialDocsTabType,
1773 x_dummyPaperPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
1774 x_instrErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1775 x_insTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
1776 trxnErrTokenTabType,
1777 x_return_message IN OUT NOCOPY VARCHAR2,
1778 x_return_status IN OUT NOCOPY NUMBER,
1779 x_msg_count IN OUT NOCOPY NUMBER,
1780 x_msg_data IN OUT NOCOPY VARCHAR2
1781 )
1782 IS
1783
1784 l_paper_pmts_count NUMBER := 0;
1785 l_last_used_check_num NUMBER := 0;
1786 l_last_avail_check_num NUMBER := 0;
1787 l_physical_stock_count NUMBER := 0;
1788 l_anticipated_last_check_num NUMBER := 0;
1789
1790 l_pmt_doc_name VARCHAR2(200) := '';
1791 l_pmt_instr_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE;
1792
1793 l_error_code IBY_TRANSACTION_ERRORS.error_code%TYPE;
1794 l_instr_err_rec IBY_TRANSACTION_ERRORS%ROWTYPE;
1795 l_token_rec IBY_TRXN_ERROR_TOKENS%ROWTYPE;
1796
1797 l_send_to_file_flag VARCHAR2(1);
1798 l_instr_status IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_status%TYPE;
1799
1800 l_single_pmt_flag BOOLEAN;
1801 l_nos_avlbl_flag BOOLEAN;
1802 l_used_flag BOOLEAN;
1803
1804 l_module_name VARCHAR2(200) := G_PKG_NAME || '.assignCheckNumbers';
1805
1806 BEGIN
1807
1808 print_debuginfo(l_module_name, 'ENTER');
1809
1810 /*
1811 * Total number of paper payments is sum of real paper payments
1812 * and dummy paper payments.
1813 */
1814 l_paper_pmts_count := x_paperPmtsTab.COUNT + x_dummyPaperPmtsTab.COUNT;
1815
1816 /* should never come into if, but just in case */
1817 IF (l_paper_pmts_count = 0) THEN
1818
1819 /*
1820 * Shouldn't come here. This method was called because there
1821 * was atleast one payment instruction with processing type
1822 * 'PAPER'. This implies that there should be at least one
1823 * payment instruction with processing type 'PAPER'.
1824 *
1825 * If no such payment exists, about the program.
1826 */
1827 print_debuginfo(l_module_name, 'Total # of paper payments '
1828 || 'is 0. Possible data corruption. Aborting ..'
1829 );
1830
1831 print_debuginfo(l_module_name, 'EXIT');
1832 x_return_status := -1;
1833
1834 l_error_code := 'IBY_INS_PMTS_NOT_FOUND';
1835 FND_MESSAGE.set_name('IBY', l_error_code);
1836
1837 FND_MESSAGE.SET_TOKEN('INS_ID',
1838 x_pmtInstrRec.payment_instruction_id,
1839 FALSE);
1840
1841 FND_MSG_PUB.ADD;
1842
1843 FND_MSG_PUB.COUNT_AND_GET(
1844 p_count => x_msg_count,
1845 p_data => x_msg_data
1846 );
1847
1848 x_return_message := FND_MESSAGE.get;
1849
1850 RETURN;
1851
1852 ELSE
1853
1854 print_debuginfo(l_module_name, 'Total # of paper payments: '
1855 || l_paper_pmts_count
1856 );
1857
1858 END IF;
1859
1860 /*
1861 * When this printed payment instruction is formatted, the
1862 * output can be sent to any of two places:
1863 *
1864 * a. To the printer
1865 * b. To a file (for printing outside the Oracle system).
1866 *
1867 * The status of the payment instruction needs to be adjusted
1868 * as per this destination flag.
1869 */
1870 BEGIN
1871
1872 SELECT
1873 send_to_file_flag
1874 INTO
1875 l_send_to_file_flag
1876 FROM
1877 IBY_PAYMENT_PROFILES
1878 WHERE
1879 payment_profile_id = x_pmtInstrRec.payment_profile_id
1880 ;
1881
1882 EXCEPTION
1883 WHEN OTHERS THEN
1884
1885 print_debuginfo(l_module_name, 'Fatal: Exception when '
1886 || 'attempting to get "send to file flag" for payment '
1887 || 'instruction '
1888 || x_pmtInstrRec.payment_instruction_id
1889 || ' with payment profile id '
1890 || x_pmtInstrRec.payment_profile_id
1891 );
1892
1893 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
1894 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
1895
1896 print_debuginfo(l_module_name, 'Aborting numbering process ..');
1897
1898 x_return_status := -1;
1899
1900 l_error_code := 'IBY_INS_PROF_EXCEP';
1901 FND_MESSAGE.set_name('IBY', l_error_code);
1902
1903 FND_MESSAGE.SET_TOKEN('INS_ID',
1904 x_pmtInstrRec.payment_instruction_id,
1905 FALSE);
1906
1907 FND_MESSAGE.SET_TOKEN('PROF_ID',
1908 x_pmtInstrRec.payment_profile_id,
1909 FALSE);
1910
1911 FND_MSG_PUB.ADD;
1912
1913 FND_MSG_PUB.COUNT_AND_GET(
1914 p_count => x_msg_count,
1915 p_data => x_msg_data
1916 );
1917
1918 x_return_message := FND_MESSAGE.get;
1919
1920 print_debuginfo(l_module_name, 'EXIT');
1921
1922 RETURN;
1923
1924 END;
1925
1926 print_debuginfo(l_module_name, 'For payment instruction '
1927 || x_pmtInstrRec.payment_instruction_id
1928 || ' with payment profile id '
1929 || x_pmtInstrRec.payment_profile_id
1930 || ', send to file flag: '
1931 || l_send_to_file_flag
1932 );
1933
1934 /*
1935 * The payment instruction status is dependant upon
1936 * the 'send to file' flag. Preset the instruction
1937 * status appropriately.
1938 */
1939 IF (UPPER(l_send_to_file_flag) = 'Y') THEN
1940 l_instr_status := INS_STATUS_READY_TO_FORMAT;
1941 ELSE
1942 l_instr_status := INS_STATUS_READY_TO_PRINT;
1943 END IF;
1944
1945 /*
1946 * Check whether the provided payments list consists
1947 * of a single payment.
1948 */
1949 l_single_pmt_flag := isSinglePayment(x_paperPmtsTab);
1950
1951 /*
1952 * IMPORTANT NOTE:
1953 * Irrespective of whether this payment instruction
1954 * could be numbered successfully or not, we will
1955 * change the status of the payment instruction at
1956 * this point from 'CREATED' -> 'CREATED_READY_FOR_PRINTING'
1957 * or 'CREATED_READY_FOR_FORMATTING'.
1958 *
1959 * In case, the payments could be numbered successfully,
1960 * the new payment instruction status becomes a transient
1961 * status (because the format will immediately change the
1962 * payment status to 'FORMATTED').
1963 *
1964 * In case, the numbering failed for some reason, or some
1965 * other exception was thrown, the payment instruction
1966 * will remain in the new status. This will be sufficient
1967 * for the UI to recognize that the payment instruction
1968 * did not finish the numbering operation successfully.
1969 *
1970 * Therefore, the UI will call the check numering flow
1971 * again, and we will have another go at numbering this
1972 * payment instruction.
1973 */
1974
1975 /*
1976 * Blindly update the payment instruction status
1977 * (see above comment).
1978 */
1979 UPDATE
1980 IBY_PAY_INSTRUCTIONS_ALL
1981 SET
1982 payment_instruction_status = l_instr_status
1983 WHERE
1984 payment_instruction_id = x_pmtInstrRec.payment_instruction_id;
1985
1986 /*
1987 * For single payments, do not perform any commits because
1988 * single payments API is session based and only the caller
1989 * decides to commit / not commit.
1990 */
1991 IF (l_single_pmt_flag <> TRUE) THEN
1992
1993 /*
1994 * This commit is needed so that in case of any exceptions
1995 * in this method (e.g., payment document locked), the
1996 * payment instruction status is changed from 'CREATED'
1997 * to the next valid status.
1998 *
1999 * Payments in 'CREATED' status are not visible in the UI,
2000 * this is because 'CREATED' is a transient status.
2001 */
2002 COMMIT;
2003
2004 print_debuginfo(l_module_name, 'Payment instruction '
2005 || x_pmtInstrRec.payment_instruction_id
2006 || ' status committed to '
2007 || l_instr_status
2008 || ' before numbering.'
2009 );
2010
2011 END IF;
2012
2013 /*
2014 * Pull up the details of the paper stock, like the
2015 * last used check number and the last available
2016 * check number.
2017 *
2018 * Note: This SELECT will lock the underlying base
2019 * table IBY_PAYMENT_DOCUMENTS_B. We need to lock this
2020 * table because we need to update the last_document_number.
2021 */
2022 SELECT
2023 NVL(last_issued_document_number, 0),
2024 NVL(last_available_document_number, -1),
2025 payment_document_name,
2026 payment_instruction_id
2027 INTO
2028 l_last_used_check_num,
2029 l_last_avail_check_num,
2030 l_pmt_doc_name,
2031 l_pmt_instr_id
2032 FROM
2033 CE_PAYMENT_DOCUMENTS
2034 WHERE
2035 payment_document_id = p_payment_doc_id
2036 FOR UPDATE
2037 ;
2038
2039 IF (l_pmt_instr_id IS NOT NULL) THEN
2040 print_debuginfo(l_module_name, 'Payment document '
2041 || ''''
2042 || l_pmt_doc_name
2043 || ''''
2044 || ' with payment doc id '
2045 || p_payment_doc_id
2046 || ' has been locked by payment instruction '
2047 || l_pmt_instr_id,
2048 FND_LOG.LEVEL_UNEXPECTED
2049 );
2050
2051 print_debuginfo(l_module_name, 'Processing cannot continue '
2052 || 'because payment document is unavailable (locked).',
2053 FND_LOG.LEVEL_UNEXPECTED
2054 );
2055
2056 print_debuginfo(l_module_name, 'Changing the status of the '
2057 || 'payment instruction to '
2058 || l_instr_status
2059 );
2060
2061 /*
2062 * Fix for bug 5735030:
2063 *
2064 * Populate error message in output file so that
2065 * the user knows the cause of the failure even
2066 * if logging is turned off.
2067 */
2068 l_error_code := 'IBY_INS_PMT_DOC_LOCKED_DETAIL';
2069 FND_MESSAGE.SET_NAME('IBY', l_error_code);
2070
2071 FND_MESSAGE.SET_TOKEN('THIS_INS_NUM',
2072 x_pmtInstrRec.payment_instruction_id,
2073 FALSE);
2074
2075 FND_MESSAGE.SET_TOKEN('PREV_INS_NUM',
2076 l_pmt_instr_id,
2077 FALSE);
2078
2079 FND_MESSAGE.SET_TOKEN('DOC_NAME',
2080 l_pmt_doc_name,
2081 FALSE);
2082
2083 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
2084
2085 /*
2086 * Return failure status.
2087 */
2088 x_return_status := -1;
2089
2090 l_error_code := 'IBY_INS_PMT_DOC_LOCKED';
2091 FND_MESSAGE.set_name('IBY', l_error_code);
2092
2093 FND_MESSAGE.SET_TOKEN('INS_ID',
2094 l_pmt_instr_id,
2095 FALSE);
2096
2097 FND_MSG_PUB.ADD;
2098
2099 FND_MSG_PUB.COUNT_AND_GET(
2100 p_count => x_msg_count,
2101 p_data => x_msg_data
2102 );
2103
2104 x_return_message := FND_MESSAGE.get;
2105
2106 print_debuginfo(l_module_name, 'EXIT');
2107 RETURN;
2108
2109 END IF;
2110
2111 /*
2112 * Log warnings if there is any missing/incomplete information.
2113 */
2114 IF (l_last_avail_check_num = -1) THEN
2115
2116 print_debuginfo(l_module_name, 'Warning: payment document id '
2117 || p_payment_doc_id
2118 || ' has no last available document number set. '
2119 || 'Assuming that infinite number of paper documents '
2120 || 'can be printed for this payment document.'
2121 );
2122
2123 END IF;
2124
2125 IF (l_last_used_check_num = 0) THEN
2126
2127 print_debuginfo(l_module_name, 'Warning: payment document id '
2128 || p_payment_doc_id
2129 || ' has last used document number set to zero. '
2130 || 'Assuming that no paper documents have yet '
2131 || 'been printed for this payment document.'
2132 );
2133
2134 END IF;
2135
2136
2137 /*
2138 * If user has explicitly provided a start number for check
2139 * numbering, we have to use it in our numbering logic.
2140 * This will only happen for single payments.
2141 *
2142 * For Build Program invoked numbering, we will always start
2143 * from the last issued check number on the payment document + 1.
2144 */
2145 IF (p_user_assgn_num IS NULL) THEN
2146
2147 print_debuginfo(l_module_name, 'User has not explicitly '
2148 || 'provided a check number to start numbering from. '
2149 || 'Numbering will start from last issued check number '
2150 || 'on check stock.'
2151 );
2152
2153 ELSE
2154
2155 print_debuginfo(l_module_name, 'User has explicitly '
2156 || 'provided start number for numbering: '
2157 || p_user_assgn_num
2158 );
2159
2160 /*
2161 * The code below uses the variable 'l_last_used_check_num'
2162 * as the starting number for check numbering. The numbering
2163 * will begin from l_last_used_check_num + 1.
2164 *
2165 * If the user has explicitly provided a start number for
2166 * numbering, we need to adjust the l_last_used_check_num
2167 * value accordingly.
2168 */
2169 l_last_used_check_num := p_user_assgn_num - 1;
2170
2171 END IF;
2172
2173 /*
2174 * Check if enough paper documents are available to complete
2175 * this payment instruction.
2176 *
2177 * Perform this check only if a value has been provided
2178 * for the last available document number. If no value is
2179 * set assume that an infinite number of checks can be
2180 * printed for this paper stock (payment document).
2181 */
2182 IF (l_last_avail_check_num <> -1) THEN
2183
2184 /*
2185 * Check if enough paper documents are available to complete
2186 * this payment instruction.
2187 */
2188 l_physical_stock_count := l_last_avail_check_num
2189 - l_last_used_check_num;
2190
2191 print_debuginfo(l_module_name, 'Available paper stock = '
2192 || l_physical_stock_count
2193 || ' for payment document name '
2194 || ''''
2195 || l_pmt_doc_name
2196 || ''''
2197 );
2198
2199 IF (l_physical_stock_count < l_paper_pmts_count) THEN
2200
2201 /*
2202 * Not enough paper stock is available to print
2203 * the checks for this payment instruction.
2204 *
2205 * Set the status of the payment instruction to
2206 * failed.
2207 */
2208 print_debuginfo(l_module_name, 'Deferring payment '
2209 || 'instruction print '
2210 || x_pmtInstrRec.payment_instruction_id
2211 || ' because of insufficient paper stock.',
2212 FND_LOG.LEVEL_UNEXPECTED
2213 );
2214
2215 x_pmtInstrRec.payment_instruction_status := l_instr_status;
2216
2217 l_error_code := 'IBY_INS_INSUFFICIENT_PAY_DOCS';
2218
2219 FND_MESSAGE.set_name('IBY', l_error_code);
2220
2221 FND_MESSAGE.SET_TOKEN('NUM_AVAIL',
2222 l_physical_stock_count,
2223 FALSE);
2224
2225 l_token_rec.token_name := 'NUM_AVAIL';
2226 l_token_rec.token_value := l_physical_stock_count;
2227 x_insTokenTab(x_insTokenTab.COUNT + 1) := l_token_rec;
2228
2229 FND_MESSAGE.SET_TOKEN('NUM_REQD',
2230 l_paper_pmts_count,
2231 FALSE);
2232
2233 l_token_rec.token_name := 'NUM_REQD';
2234 l_token_rec.token_value := l_paper_pmts_count;
2235 x_insTokenTab(x_insTokenTab.COUNT + 1) := l_token_rec;
2236
2237 /*
2238 * Once we fail a pmt instruction, we must add a
2239 * corresponding error message to the errors table.
2240 */
2241 IBY_PAYINSTR_UTILS_PKG.createErrorRecord(
2242 x_pmtInstrRec.payment_instruction_id,
2243 x_pmtInstrRec.payment_instruction_status,
2244 l_error_code,
2245 FND_MESSAGE.get,
2246 'N',
2247 l_instr_err_rec
2248 );
2249
2250 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
2251 l_instr_err_rec,
2252 x_instrErrorTab,
2253 x_insTokenTab
2254 );
2255
2256 /* add error message to msg stack */
2257 FND_MSG_PUB.ADD;
2258
2259 FND_MSG_PUB.COUNT_AND_GET(
2260 p_count => x_msg_count,
2261 p_data => x_msg_data
2262 );
2263
2264 /* set error message to return to caller */
2265 x_return_message := FND_MESSAGE.get;
2266
2267 /*
2268 * Now, raise an exception. This will be caught
2269 * in the exception handler below and the changes
2270 * made to the DB in this transaction
2271 * will be rolled back.
2272 */
2273 APP_EXCEPTION.RAISE_EXCEPTION;
2274
2275 ELSE
2276
2277 print_debuginfo(l_module_name, 'Sufficient paper stock '
2278 || 'is available to print this instruction.'
2279 );
2280
2281 END IF;
2282
2283 END IF; -- l_last_avail_check_num <> -1
2284
2285 /*
2286 * If sufficient paper stock is available, we will be using
2287 * up the paper stock by assigning it to the available
2288 * paper payments. Therefore, update the last used paper
2289 * stock number in CE_PAYMENT_DOCUMENTS.
2290 *
2291 * That way if another instance of the payment instruction
2292 * creation program is operating concurrently, it will
2293 * be blocked by the SELECT .. FOR UPDATE statement in
2294 * this method.
2295 *
2296 */
2297 l_anticipated_last_check_num := l_last_used_check_num
2298 + l_paper_pmts_count;
2299
2300 /*
2301 * We will be printing the checks starting with
2302 * paper doc num 'l_last_used_check_num + 1' and
2303 * ending with paper doc num l_anticipated_last_check_num.
2304 *
2305 * Check whether all the paper doc numbers within this
2306 * range are available. We cannot have any gaps in the
2307 * numbering because checks have to be numbered
2308 * contiguously.
2309 */
2310 l_nos_avlbl_flag := isContigPaperNumAvlbl(
2311 p_payment_doc_id,
2312 l_last_used_check_num + 1,
2313 l_anticipated_last_check_num
2314 );
2315
2316 IF (l_nos_avlbl_flag = FALSE) THEN
2317
2318 print_debuginfo(l_module_name, 'Contiguous paper stock '
2319 || 'is not available for printing payment instruction '
2320 || x_pmtInstrRec.payment_instruction_id
2321 );
2322
2323 /*
2324 * Return failure status.
2325 */
2326 x_return_status := -1;
2327
2328 l_error_code := 'IBY_INS_NSF_CONTIG_NUM';
2329 FND_MESSAGE.set_name('IBY', l_error_code);
2330
2331 FND_MESSAGE.SET_TOKEN('NUM_PMT_DOCS',
2332 l_paper_pmts_count,
2333 FALSE);
2334
2335 FND_MSG_PUB.ADD;
2336
2337 FND_MSG_PUB.COUNT_AND_GET(
2338 p_count => x_msg_count,
2339 p_data => x_msg_data
2340 );
2341
2342 x_return_message := FND_MESSAGE.get;
2343
2344 print_debuginfo(l_module_name, 'EXIT');
2345 RETURN;
2346
2347 END IF;
2348
2349 /*
2350 * A paper document number (check number) is considered
2351 * unused if it is not present in IBY_USED_PAYMENT_DOCS
2352 * table.
2353 *
2354 * This logic will work when check numbering is invoked
2355 * from the Build Program. In this case, the numbering
2356 * logic always starts with the last issued doc number + 1
2357 * when assigning new check numbers. Therefore, the
2358 * check numbers will always be unique (unused).
2359 *
2360 * However, when check numbering is invoked for single
2361 * payments, the user is allowed to provide the start
2362 * number for check numbering. It is possible that
2363 * a payment has already been numbered with the user
2364 * provided start number, but this paper document may
2365 * not yet have been inserted into the IBY_USED_PAYMENT_DOCS
2366 * table (because the user has not yet confirmed the
2367 * payment).
2368 *
2369 * Therefore, for single payments, when the user provides
2370 * the start number for check numbering, we will have to
2371 * verify that the provided number is unused by checking
2372 * the paper document number on existing payments.
2373 */
2374 IF (p_user_assgn_num IS NOT NULL) THEN
2375
2376 l_used_flag := isPaperNosUsedOnExistPmt(
2377 p_payment_doc_id,
2378 l_last_used_check_num + 1,
2379 l_anticipated_last_check_num);
2380
2381 IF (l_used_flag = TRUE) THEN
2382
2383 print_debuginfo(l_module_name, 'Paper document number(s) '
2384 || 'generated after numbering are invalid (already used). '
2385 || 'User needs to provide a new start number or use '
2386 || 'the defaulted start number.'
2387 );
2388
2389 /*
2390 * Return failure status.
2391 */
2392 x_return_status := -1;
2393
2394 l_error_code := 'IBY_INS_ALREADY_USED_NUM';
2395 FND_MESSAGE.set_name('IBY', l_error_code);
2396
2397 FND_MSG_PUB.ADD;
2398
2399 FND_MSG_PUB.COUNT_AND_GET(
2400 p_count => x_msg_count,
2401 p_data => x_msg_data
2402 );
2403
2404 x_return_message := FND_MESSAGE.get;
2405
2406 print_debuginfo(l_module_name, 'EXIT');
2407 RETURN;
2408
2409 ELSE
2410
2411 print_debuginfo(l_module_name, 'Paper document number(s) '
2412 || 'generated after numbering are unused. '
2413 );
2414
2415 END IF;
2416
2417 END IF;
2418
2419 /*
2420 * For single payments, the payment document should
2421 * not be locked (see bug 4597718).
2422 */
2423 IF (l_single_pmt_flag = TRUE) THEN
2424
2425 print_debuginfo(l_module_name, 'This is a single payment. '
2426 || 'Payment document will not be locked ..'
2427 );
2428
2429 /*
2430 * Update the check stock to reflect the latest used
2431 * check number.
2432 */
2433 UPDATE
2434 CE_PAYMENT_DOCUMENTS
2435 SET
2436 last_issued_document_number = l_anticipated_last_check_num
2437 WHERE
2438 payment_document_id = p_payment_doc_id
2439 ;
2440
2441 ELSE
2442
2443 print_debuginfo(l_module_name, 'This is not a single payment. '
2444 || 'Payment document will be locked ..'
2445 );
2446
2447 /*
2448 * Update the check stock to reflect the latest used
2449 * check number, and lock the check stock.
2450 */
2451 UPDATE
2452 CE_PAYMENT_DOCUMENTS
2453 SET
2454 last_issued_document_number = l_anticipated_last_check_num,
2455 payment_instruction_id = x_pmtInstrRec.payment_instruction_id
2456 WHERE
2457 payment_document_id = p_payment_doc_id
2458 ;
2459
2460 END IF;
2461
2462 print_debuginfo(l_module_name, 'Finished updating the last '
2463 || 'available check number in CE_PAYMENT_DOCUMENTS. '
2464 || 'Current last check number: '
2465 || l_anticipated_last_check_num
2466 );
2467
2468 /* uncomment for debug purposes */
2469 --print_debuginfo(l_module_name, 'x_dummyPaperPmtsTab.COUNT: '
2470 -- || x_dummyPaperPmtsTab.COUNT);
2471 --print_debuginfo(l_module_name, 'x_paperPmtsTab.COUNT: '
2472 -- || x_paperPmtsTab.COUNT);
2473
2474 /*
2475 * Assign contiguous check numbers to the setup checks.
2476 * These are dummy checks that are printed at the
2477 * beginning of the payment instruction print run.
2478 */
2479 IF (x_dummyPaperPmtsTab.COUNT <> 0) THEN
2480
2481 FOR i in x_dummyPaperPmtsTab.FIRST .. x_dummyPaperPmtsTab.LAST LOOP
2482
2483 IF (x_dummyPaperPmtsTab(i).payment_status = 'VOID_BY_SETUP') THEN
2484
2485 l_last_used_check_num := l_last_used_check_num + 1;
2486 x_dummyPaperPmtsTab(i).paper_document_number
2487 := l_last_used_check_num;
2488
2489 END IF;
2490
2491 END LOOP; -- for all setup payments in x_dummyPaperPmtsTab
2492
2493 END IF;
2494
2495 /* handle real checks and overflow checks here */
2496 FOR i in x_paperPmtsTab.FIRST .. x_paperPmtsTab.LAST LOOP
2497
2498 /*
2499 * We must assign the check numbers in sequence
2500 * so that when a paper payment is printed, all the
2501 * paper documents for that payment have contiguous
2502 * number.
2503 */
2504
2505
2506 /*
2507 * If this payment has any overflow documents, then
2508 * some overflow payments would have been created.
2509 * Assign check numbers to these overflow payments
2510 * too.
2511 *
2512 * Overflow payments are created one-by-one and are
2513 * assigned incremental payment ids. So no need to sort
2514 * these overflow payments (they are already sorted).
2515 *
2516 * Simply find the overflow payments related to the real
2517 * payment and assign them with contiguous check numbers.
2518 */
2519 IF (x_dummyPaperPmtsTab.COUNT <> 0) THEN
2520
2521 FOR j in x_dummyPaperPmtsTab.FIRST .. x_dummyPaperPmtsTab.LAST LOOP
2522
2523 /*
2524 * The external_bank_account_id field actually contains
2525 * the original payment id (the payment that originally
2526 * contained the documents payable stored in the dummy
2527 * overflow payment).
2528 *
2529 * See KLUDGE in performSpecialPaperHandling()
2530 * method to see why this was done.
2531 */
2532 IF (x_dummyPaperPmtsTab(j).payment_status = 'VOID_BY_OVERFLOW')
2533 THEN
2534
2535 IF (x_dummyPaperPmtsTab(j).external_bank_account_id
2536 = x_paperPmtsTab(i).payment_id) THEN
2537
2538 l_last_used_check_num := l_last_used_check_num + 1;
2539 x_dummyPaperPmtsTab(j).paper_document_number
2540 := l_last_used_check_num;
2541
2542 END IF;
2543
2544 END IF;
2545
2546 END LOOP; -- for all overflow pmts in x_dummyPaperPmtsTab
2547
2548 END IF;
2549
2550 /* Bug 7252846 - priting of overflow and original payments must be such that, the overflow payments must be
2551 printed first, followed by original ones. In this case, the numbering should happen first for overflow payments
2552 followed by original ones */
2553
2554 /* assign check number to paper payment */
2555 l_last_used_check_num := l_last_used_check_num + 1;
2556 x_paperPmtsTab(i).check_number := l_last_used_check_num;
2557
2558
2559 END LOOP; -- for all pmts in x_paperPmtsTab
2560
2561 /*
2562 * Final check:
2563 *
2564 * If all paper payments (including real payments, setup payments
2565 * and overflow payments) have been assigned check numbers
2566 * correctly, then the number of check numbers used up should
2567 * match the total paper payments count.
2568 *
2569 * If the two don't match, it means that some check numbers were
2570 * unassigned, or multiply assigned. In either case, abort the
2571 * program. This check will reveal any bugs in this method.
2572 */
2573 IF (l_anticipated_last_check_num <> l_last_used_check_num) THEN
2574
2575 print_debuginfo(l_module_name, 'Check numbers were not '
2576 || 'properly assigned. '
2577 || 'Anticipated last used check number: '
2578 || l_anticipated_last_check_num
2579 || '. Actual last used check number: '
2580 || l_last_used_check_num
2581 || '. Deferring print for payment instruction '
2582 || x_pmtInstrRec.payment_instruction_id,
2583 FND_LOG.LEVEL_UNEXPECTED
2584 );
2585
2586 x_pmtInstrRec.payment_instruction_status := l_instr_status;
2587
2588 l_error_code := 'IBY_INS_NUMBERING_ERR_1';
2589
2590 FND_MESSAGE.set_name('IBY', l_error_code);
2591
2592 FND_MESSAGE.SET_TOKEN('NUM_CALC',
2593 l_anticipated_last_check_num,
2594 FALSE);
2595
2596 l_token_rec.token_name := 'NUM_CALC';
2597 l_token_rec.token_value := l_anticipated_last_check_num;
2598 x_insTokenTab(x_insTokenTab.COUNT + 1) := l_token_rec;
2599
2600 FND_MESSAGE.SET_TOKEN('NUM_ACTU',
2601 l_last_used_check_num,
2602 FALSE);
2603
2604 l_token_rec.token_name := 'NUM_ACTU';
2605 l_token_rec.token_value := l_last_used_check_num;
2606 x_insTokenTab(x_insTokenTab.COUNT + 1) := l_token_rec;
2607
2608 /*
2609 * Once we fail a pmt instruction, we must add a
2610 * corresponding error message to the errors table.
2611 */
2612 IBY_PAYINSTR_UTILS_PKG.createErrorRecord(
2613 x_pmtInstrRec.payment_instruction_id,
2614 x_pmtInstrRec.payment_instruction_status,
2615 l_error_code,
2616 FND_MESSAGE.get,
2617 'N',
2618 l_instr_err_rec
2619 );
2620
2621 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(
2622 l_instr_err_rec,
2623 x_instrErrorTab,
2624 x_insTokenTab
2625 );
2626
2627 /* add error msg to message stack */
2628 FND_MSG_PUB.ADD;
2629
2630 FND_MSG_PUB.COUNT_AND_GET(
2631 p_count => x_msg_count,
2632 p_data => x_msg_data
2633 );
2634
2635 /* set error message to return to caller */
2636 x_return_message := FND_MESSAGE.get;
2637
2638 APP_EXCEPTION.RAISE_EXCEPTION;
2639
2640 END IF;
2641
2642 /*
2643 * Return success status.
2644 */
2645 x_return_message := 'SUCCESS';
2646 x_return_status := 0;
2647
2648 print_debuginfo(l_module_name, 'EXIT');
2649
2650 EXCEPTION
2651 WHEN OTHERS THEN
2652
2653 print_debuginfo(l_module_name, 'Exception occured when '
2654 || 'performing document numbering. '
2655 );
2656 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
2657 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
2658
2659 /*
2660 * Rollback any DB changes made in this method.
2661 */
2662 ROLLBACK;
2663
2664 /*
2665 * Return error status to caller.
2666 * The error message would have already been set.
2667 */
2668 x_return_status := -1;
2669
2670 print_debuginfo(l_module_name, 'EXIT');
2671
2672 END assignCheckNumbers;
2673
2674 /*--------------------------------------------------------------------
2675 | NAME:
2676 | getNumPrintedDocsByFormula
2677 |
2678 | PURPOSE:
2679 |
2680 |
2681 | PARAMETERS:
2682 | IN
2683 |
2684 | OUT
2685 |
2686 |
2687 | RETURNS:
2688 |
2689 | NOTES:
2690 |
2691 *---------------------------------------------------------------------*/
2692 FUNCTION getNumPrintedDocsByFormula(
2693 p_num_docs_payable IN NUMBER,
2694 p_num_lines_per_stub IN NUMBER
2695 )
2696 RETURN NUMBER
2697 IS
2698
2699 l_num_printed_docs NUMBER := -1;
2700 l_carryover NUMBER := -1;
2701 l_extra_printed_doc NUMBER := -1;
2702
2703 BEGIN
2704
2705 /*
2706 * We will try to fit n docs payable into m stubs, where
2707 * p = number of lines per stub. The problem is to calculate
2708 * m, given n and p.
2709 *
2710 * Now, number of stubs (i.e., printed docs) will be at least n/p
2711 *
2712 * Therefore, m >= n/p
2713 *
2714 * If n/p division is not exact, it means that some docs payable
2715 * are left over. Now, the number of docs payable left over (i.e.,
2716 * the reminder) is necessarily less than p, because reminder is
2717 * always less than the divisor.
2718 *
2719 * So, the reminder docs payable will definitely fit into one stub.
2720 *
2721 * Therefore, m = n/p + r
2722 *
2723 * where r = 0, if the reminder is zero i.e., n mod p = 0
2724 * else r = 1, if the reminder is non-zero i.e., n mod p > 0
2725 */
2726
2727 l_carryover := MOD(p_num_docs_payable, p_num_lines_per_stub);
2728
2729 IF (l_carryover <> 0) THEN
2730 l_extra_printed_doc := 1;
2731 ELSE
2732 l_extra_printed_doc := 0;
2733 END IF;
2734
2735 l_num_printed_docs := FLOOR(p_num_docs_payable / p_num_lines_per_stub)
2736 + l_extra_printed_doc;
2737
2738 return l_num_printed_docs;
2739
2740 END getNumPrintedDocsByFormula;
2741
2742 /*--------------------------------------------------------------------
2743 | NAME:
2744 | performSpecialPaperDocHandling
2745 |
2746 | PURPOSE:
2747 |
2748 |
2749 |
2750 | PARAMETERS:
2751 | IN
2752 |
2753 |
2754 | OUT
2755 |
2756 |
2757 | RETURNS:
2758 |
2759 | NOTES:
2760 |
2761 *---------------------------------------------------------------------*/
2762 PROCEDURE performSpecialPaperDocHandling(
2763 x_paperSpecialTab IN OUT NOCOPY paperPmtsSpecialDocsTabType,
2764 x_dummyPaperPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
2765 x_setupDocsTab IN OUT NOCOPY docsTabType,
2766 x_overflowDocsTab IN OUT NOCOPY overflowDocsTabType
2767 )
2768 IS
2769 l_doc_rec iby_docs_payable_all%ROWTYPE;
2770 l_pmt_rec IBY_PAYMENTS_ALL%ROWTYPE;
2771
2772 l_payment_id IBY_PAYMENTS_ALL.payment_id%TYPE;
2773
2774 l_pmtsInPmtInstrRec IBY_PAYINSTR_PUB.pmtsInPmtInstrRecType;
2775
2776 --Bug 6486816
2777 -- Previously formatting_payment_id was updated for each document of payment but
2778 --after which x_overflowDocsTab was updates for next payment, losing the
2779 --previous values. So, storing all the overflow docs in a new table which will
2780 --be passed to calling function
2781
2782 x_overflowDocsTab_out overflowDocsTabType;
2783 l_begin_doc_index_outrec NUMBER := 1;
2784
2785 l_module_name VARCHAR2(200) := G_PKG_NAME
2786 || '.performSpecialPaperDocHandling';
2787 l_begin_doc_index NUMBER := 0;
2788 l_end_doc_index NUMBER := 0;
2789
2790 /* payments cursor */
2791 CURSOR c_document_data(p_payment_id IBY_DOCS_PAYABLE_ALL.payment_id%TYPE)
2792 IS
2793 SELECT
2794 document_payable_id,
2795 payment_id,
2796 payment_currency_code,
2797 payment_function,
2798 formatting_payment_id,
2799 org_id,
2800 org_type
2801 FROM
2802 IBY_DOCS_PAYABLE_ALL
2803 WHERE
2804 payment_id = p_payment_id
2805 ORDER BY
2806 document_payable_id ASC
2807 ;
2808
2809 BEGIN
2810
2811 print_debuginfo(l_module_name, 'ENTER');
2812
2813 /*
2814 * STEP 1:
2815 * Handle setup documents.
2816 *
2817 * Setup documents are dummy checks that are printed at the
2818 * beginning of a check print run. They are basically void checks
2819 * used to align the printer.
2820 *
2821 * Since each setup document is to be printed as a separate check
2822 * do the following:
2823 * a. Insert a dummy payment for each setup document that is to be
2824 * printed.
2825 * b. For each inserted dummy payment, insert as many dummy documents
2826 * payable as will fit onto the check stub of the setup document.
2827 * c. Add this dummy payment to the corresponding payment instruction.
2828 *
2829 * Note that setup checks are to be printed once per payment
2830 * instruction (not once per payment).
2831 */
2832 print_debuginfo(l_module_name, 'Performing setup document handling ..');
2833
2834 FOR i IN x_paperSpecialTab.FIRST .. x_paperSpecialTab.LAST LOOP
2835
2836 /*
2837 * Setup documents have to be generated once per payment
2838 * instruction. Generate setup docs for an instruction
2839 * only if the 'setup docs flag' is set to 'N'.
2840 */
2841 IF (x_paperSpecialTab(i).setup_docs_for_instr_finished <> 'Y') THEN
2842
2843 FOR j IN 1 .. x_paperSpecialTab(i).num_setup_docs LOOP
2844
2845 /*
2846 * Insert a dummy payment into IBY_PAYMENTS_ALL
2847 * table for each setup document.
2848 */
2849
2850 /*
2851 * This is a new payment; Get an id for this payment
2852 */
2853 IBY_PAYGROUP_PUB.getNextPaymentID(l_payment_id);
2854
2855 l_pmt_rec.payment_id := l_payment_id;
2856 l_pmt_rec.payment_profile_id := -1;
2857 l_pmt_rec.payment_status := 'VOID_BY_SETUP';
2858 l_pmt_rec.payment_amount := 0;
2859 l_pmt_rec.internal_bank_account_id := -1;
2860 l_pmt_rec.created_by := fnd_global.user_id;
2861 l_pmt_rec.creation_date := sysdate;
2862 l_pmt_rec.last_updated_by := fnd_global.user_id;
2863 l_pmt_rec.last_update_date := sysdate;
2864 l_pmt_rec.last_update_login := fnd_global.login_id;
2865 l_pmt_rec.object_version_number := 1;
2866 l_pmt_rec.ext_payee_id := -1;
2867 l_pmt_rec.payment_service_request_id := -1;
2868
2869 /*
2870 * Fix for bug 5336487:
2871 *
2872 * For setup checks, set the payment currency
2873 * to '-1'.
2874 */
2875 l_pmt_rec.payment_currency_code := '-1';
2876
2877 l_pmt_rec.org_id := -1;
2878 l_pmt_rec.org_type := 'OPERATING_UNIT';
2879 l_pmt_rec.legal_entity_id := -1;
2880
2881 /*
2882 * Fix for bug 5336487:
2883 *
2884 * For setup checks, set the payment function
2885 * to '-1'.
2886 */
2887 l_pmt_rec.payment_function := '-1';
2888
2889 l_pmt_rec.process_type := 'STANDARD';
2890 l_pmt_rec.payments_complete_flag := 'N';
2891 l_pmt_rec.bill_payable_flag := 'N';
2892 l_pmt_rec.exclusive_payment_flag := 'N';
2893 l_pmt_rec.declare_payment_flag := 'N';
2894 l_pmt_rec.pregrouped_payment_flag := 'N';
2895 l_pmt_rec.stop_confirmed_flag := 'N';
2896 l_pmt_rec.stop_released_flag := 'N';
2897 l_pmt_rec.stop_request_placed_flag := 'N';
2898 l_pmt_rec.separate_remit_advice_req_flag
2899 := 'N';
2900 l_pmt_rec.payment_method_code := '-1';
2901
2902 /*
2903 * Set the instruction id for this payment to the
2904 * currently running payment instruction id
2905 */
2906 l_pmt_rec.payment_instruction_id := x_paperSpecialTab(i).
2907 instruction_id;
2908
2909 /* add payment to PLSQL table of payments */
2910 /* this will be used to insert into IBY_PAMENTS table */
2911 x_dummyPaperPmtsTab(x_dummyPaperPmtsTab.COUNT + 1)
2912 := l_pmt_rec;
2913
2914 /*
2915 * No need to add this payment to the payment instructions
2916 * PLSQL table 'x_pmtsInPmtInstrTab'. This is because
2917 * setup payments are dummy payments that are handled
2918 * separately.
2919 */
2920
2921 /*
2922 * For each dummy payment, insert corresponding dummy
2923 * documents. Insert as many dummy documents as will
2924 * fit into the stub of the printed document.
2925 */
2926
2927 /*
2928 * Fix for bug 5642449:
2929 *
2930 * Only create dummy documents to stamp on the setup
2931 * check if we know the number of lines per stub.
2932 * Otherwise, simply create the setup checks without
2933 * populating the check stub with document ids (because
2934 * we do not know how many document ids will fit on the
2935 * check stub).
2936 */
2937 IF (x_paperSpecialTab(i).num_lines_per_stub IS NOT NULL AND
2938 x_paperSpecialTab(i).num_lines_per_stub > 0) THEN
2939
2940 FOR k IN 1 .. x_paperSpecialTab(i).num_lines_per_stub LOOP
2941
2942 /*
2943 * This is a new document; Get an id for this document
2944 */
2945 l_doc_rec.document_payable_id :=
2946 IBY_DISBURSE_SUBMIT_PUB_PKG.
2947 getNextDocumentPayableID();
2948
2949 /*
2950 * By default, set the formatting payment id to
2951 * payment id for each document
2952 */
2953 l_doc_rec.formatting_payment_id :=
2954 l_doc_rec.document_payable_id;
2955 l_doc_rec.calling_app_id := -1;
2956 l_doc_rec.document_type := 'INVOICE';
2957 l_doc_rec.document_status := 'VOID_BY_SETUP';
2958 l_doc_rec.payment_id := l_payment_id;
2959 l_doc_rec.payment_amount := -1;
2960 l_doc_rec.payment_method_code := -1;
2961 l_doc_rec.exclusive_payment_flag:= 'N';
2962 l_doc_rec.payee_party_id := -1;
2963 l_doc_rec.legal_entity_id := -1;
2964 l_doc_rec.created_by := fnd_global.user_id;
2965 l_doc_rec.creation_date := sysdate;
2966 l_doc_rec.last_updated_by := fnd_global.user_id;
2967 l_doc_rec.last_update_date := sysdate;
2968 l_doc_rec.last_update_login := fnd_global.login_id;
2969 l_doc_rec.object_version_number := 1;
2970
2971 /*
2972 * Fix for bug 5336487:
2973 *
2974 * For setup documents, set the payment currency
2975 * to '-1'.
2976 */
2977 l_doc_rec.document_currency_code:= '-1';
2978 l_doc_rec.payment_currency_code := '-1';
2979
2980 l_doc_rec.payment_service_request_id
2981 := -1;
2982 l_doc_rec.org_id := -1;
2983 l_doc_rec.org_type := 'OPERATING_UNIT';
2984 l_doc_rec.calling_app_doc_unique_ref1 := '-1';
2985 l_doc_rec.calling_app_doc_unique_ref2 := '-1';
2986 l_doc_rec.calling_app_doc_unique_ref3 := '-1';
2987 l_doc_rec.calling_app_doc_unique_ref4 := '-1';
2988 l_doc_rec.calling_app_doc_unique_ref5 := '-1';
2989 l_doc_rec.pay_proc_trxn_type_code
2990 := '-1';
2991
2992 /*
2993 * Fix for bug 5336487:
2994 *
2995 * For setup documents, set the payment function
2996 * to '-1'.
2997 */
2998 l_doc_rec.payment_function := '-1';
2999
3000 l_doc_rec.calling_app_doc_ref_number := -1;
3001 l_doc_rec.payment_date := sysdate;
3002 l_doc_rec.document_date := sysdate;
3003 l_doc_rec.document_amount := 0;
3004 l_doc_rec.straight_through_flag := 'Y';
3005 l_doc_rec.allow_removing_document_flag := 'N';
3006 l_doc_rec.ext_payee_id := -1;
3007
3008 /* add document to PLSQL table of documents */
3009 x_setupDocsTab(x_setupDocsTab.COUNT + 1) := l_doc_rec;
3010
3011 END LOOP; -- for num_lines_per_stub
3012
3013 ELSE
3014
3015 print_debuginfo(l_module_name, 'Setup checks will not '
3016 || 'contain invoice numbers because number of lines '
3017 || 'per stub is unknown.'
3018 );
3019
3020 END IF; -- if num lines per stub is not null
3021
3022 END LOOP; -- for num_setup_docs in payment
3023
3024 print_debuginfo(l_module_name, 'Added '
3025 || x_paperSpecialTab(i).num_setup_docs
3026 || ' setup payments with '
3027 || x_paperSpecialTab(i).num_lines_per_stub
3028 || ' dummy documents payable each, to payment instruction '
3029 || x_paperSpecialTab(i).instruction_id
3030 );
3031
3032 /*
3033 * Once we have finished generating setup documents for an
3034 * instruction, set the 'setup docs flag' to 'Y' for all
3035 * records with the same instruction id in the paperSpecialTab
3036 * PLSQL table. This is to prevent the code fro generating
3037 * setup docs more than once for a payment instruction.
3038 */
3039 updateSetupDocsFlagForInstr(x_paperSpecialTab(i).instruction_id,
3040 x_paperSpecialTab);
3041
3042 END IF; -- if setup_docs_for_instr_finished <> 'Y'
3043
3044 END LOOP; -- for each pmt in instruction with process type 'PAPER'
3045
3046 /*
3047 * STEP 2:
3048 * Handle overflow documents.
3049 *
3050 * The number of lines that can be printed onto a check stub
3051 * is limited by the physical dimenensions of the paper stock.
3052 * The number of lines per stub is available as an attribute of
3053 * the paper stock.
3054 *
3055 * In each line on the stub, we will print the id of the
3056 * documents payable that are paid by that check.
3057 * So one check, can accomodate 'n' documents payable where
3058 * 'n' is the number of lines per stub for the paper stock
3059 * of the check.
3060 *
3061 * If a payment has more documents payable lined to it than
3062 * will fit onto the check stub, do the following:
3063 *
3064 * a. Calculate the number of overflow checks that need to
3065 * be printed to accomodate the excess documents payable.
3066 * (this is already accomplished by the document numbering
3067 * flow).
3068 * b. For each overflow check, insert a dummy payment.
3069 * c. Add this dummy payment to the correspoding payment instruction.
3070 */
3071 print_debuginfo(l_module_name, 'Performing overflow document '
3072 || 'handling ..');
3073
3074 FOR i IN x_paperSpecialTab.FIRST .. x_paperSpecialTab.LAST LOOP
3075
3076 /*
3077 * Pull up all the documents associated with this payment id.
3078 * The format_payment_id of these documents will be changed
3079 * to handle overflows.
3080 *
3081 * Only do this if a payment has non-zero overflow documents.
3082 */
3083 IF (x_paperSpecialTab(i).num_overflow_docs IS NOT NULL AND
3084 x_paperSpecialTab(i).num_overflow_docs > 0) THEN
3085
3086 OPEN c_document_data(x_paperSpecialTab(i).payment_id);
3087 FETCH c_document_data BULK COLLECT INTO x_overflowDocsTab;
3088 CLOSE c_document_data;
3089
3090 print_debuginfo(l_module_name, 'Payment '
3091 || x_paperSpecialTab(i).payment_id
3092 || ' has overflow documents payable. '
3093 || x_paperSpecialTab(i).num_overflow_docs
3094 || ' overflow paper payments will be '
3095 || ' created for this payment.'
3096 );
3097
3098 END IF;
3099
3100 /* initialize indices on overflow docs array before entering loop */
3101 l_begin_doc_index := 1;
3102 l_end_doc_index := 0;
3103
3104 /*
3105 * If there are no overflow docs for a payment, that payment
3106 * will not enter this loop.
3107 */
3108
3109 /*
3110 * Fix for bug 5252629:
3111 *
3112 * If number of overflow docs is null for a payment, it means
3113 * that the user did not specify the number of lines per
3114 * stub for the check stock that the payment is meant to
3115 * be printed on.
3116 *
3117 * In this case we cannot do overflow handling. Handle this
3118 * situation gracefully by skipping overflow handling.
3119 */
3120 IF ( x_paperSpecialTab(i).num_overflow_docs IS NOT NULL) THEN
3121
3122 /* informative debug message */
3123 IF (x_paperSpecialTab(i).num_overflow_docs = 0) THEN
3124
3125 print_debuginfo(l_module_name, 'Overflow handling not '
3126 || 'required for payment instruction '
3127 || x_paperSpecialTab(i).instruction_id
3128 );
3129
3130 END IF;
3131
3132 FOR j IN 1 .. x_paperSpecialTab(i).num_overflow_docs LOOP
3133
3134 /*
3135 * Insert a dummy payment into IBY_PAYMENTS_ALL
3136 * table for each set of overflow documents.
3137 */
3138
3139 /*
3140 * This is a new payment; Get an id for this payment
3141 */
3142 IBY_PAYGROUP_PUB.getNextPaymentID(l_payment_id);
3143
3144 l_pmt_rec.payment_id := l_payment_id;
3145 l_pmt_rec.payment_profile_id := -1;
3146 l_pmt_rec.payment_status := 'VOID_BY_OVERFLOW';
3147 l_pmt_rec.payment_amount := 0;
3148 l_pmt_rec.internal_bank_account_id := -1;
3149 l_pmt_rec.created_by := fnd_global.user_id;
3150 l_pmt_rec.creation_date := sysdate;
3151 l_pmt_rec.last_updated_by := fnd_global.user_id;
3152 l_pmt_rec.last_update_date := sysdate;
3153 l_pmt_rec.last_update_login := fnd_global.login_id;
3154 l_pmt_rec.object_version_number := 1;
3155 l_pmt_rec.ext_payee_id := -1;
3156 l_pmt_rec.payment_service_request_id
3157 := -1;
3158
3159 /*
3160 * Fix for bug 5336487:
3161 *
3162 * For overflow documents, set the currency
3163 * of the documents to the currency of the
3164 * parent payment (instead of hardcoding to
3165 * a dummy value).
3166 *
3167 * Since grouping by payment curency is a
3168 * hardcoded grouping rule, we can use the
3169 * payment currency from any real document
3170 * of this payment.
3171 */
3172 l_pmt_rec.payment_currency_code := x_overflowDocsTab(1).
3173 pmt_currency;
3174
3175 /*
3176 * Fix for bug 5332172:
3177 *
3178 * Do not hardcode the org id to -1 because the UI
3179 * restricts the viewing of these payments by org.
3180 *
3181 * Instead, use the org id of one of the overflow
3182 * documents payable that will be printed on this
3183 * overflow payment.
3184 *
3185 * We can use any of the documents that will be
3186 * part of this payment because all documents
3187 * payable that are part of a payment are guaranteed
3188 * to have the same (org id, org type); this is
3189 * a hardcoded grouping rule.
3190 */
3191 l_pmt_rec.org_id := x_overflowDocsTab(1).
3192 org_id;
3193 l_pmt_rec.org_type := x_overflowDocsTab(1).
3194 org_type;
3195
3196 l_pmt_rec.legal_entity_id := -1;
3197
3198 /*
3199 * Fix for bug 5336487:
3200 *
3201 * For overflow payments, set the payment function
3202 * to the same as it's child documents.
3203 *
3204 * Since grouping by payment function is a hardcoded
3205 * grouping rule, we can pick up the payment
3206 * function from any of the documents and set it to
3207 * the payment.
3208 */
3209 l_pmt_rec.payment_function := x_overflowDocsTab(1).
3210 pmt_function;
3211
3212 l_pmt_rec.process_type := 'STANDARD';
3213 l_pmt_rec.payments_complete_flag := 'N';
3214 l_pmt_rec.bill_payable_flag := 'N';
3215 l_pmt_rec.exclusive_payment_flag := 'N';
3216 l_pmt_rec.declare_payment_flag := 'N';
3217 l_pmt_rec.pregrouped_payment_flag := 'N';
3218 l_pmt_rec.stop_confirmed_flag := 'N';
3219 l_pmt_rec.stop_released_flag := 'N';
3220 l_pmt_rec.stop_request_placed_flag := 'N';
3221 l_pmt_rec.separate_remit_advice_req_flag
3222 := 'N';
3223 l_pmt_rec.payment_method_code := '-1';
3224
3225 /*
3226 * KLUDGE:
3227 *
3228 * We are creating a dummy payment to store overflow
3229 * documents. These payments are actually part of a real
3230 * payment indicated by x_paperSpecialTab(i).payment_id.
3231 *
3232 * When it is time for check numbering, we want to number
3233 * the real payment and the dummy payments contiguously.
3234 * Therefore, we need to know what dummy payments are
3235 * related to which real payment.
3236 *
3237 * The l_pmt_rec structure cannot store the real payment
3238 * id because it is exactly mapped to a row in
3239 * IBY_PAYMENTS_ALL.
3240 *
3241 * Instead of creating a new data structure to hold
3242 * (real payment id, dummy payment id), we use an
3243 * optional field in the l_pmt_rec record to store the
3244 * real payment id.
3245 *
3246 * This is the 'payee party site id' field. Since this is
3247 * a dummy payment any value we provide for this field
3248 * does not really matter.
3249 *
3250 * This field will be accessed in assignCheckNumbers()
3251 * method to find out all dummy payments related to a
3252 * real payment.
3253 */
3254
3255 /*
3256 * UPDATE:
3257 * Use external_bank_account_id as the placeholder
3258 * for the related original payment id. We are trying to
3259 * use some field on the payment, that is relatively
3260 * useless, to store some extra information.
3261 *
3262 * The external bank account id is a good candidate
3263 * for this because this field is expected to be
3264 * not used for paper payments anyway. The payee
3265 * party site id field will be overwritten with data
3266 * from the original payment as part of fix for
3267 * bug 6765314.
3268 *
3269 * Even though, we really don't need this link between
3270 * the overflow payment and the original payment
3271 * outside of this package, it would be a good idea to
3272 * persist this information somehow so that we can
3273 * quickly answer the question "What is the original
3274 * payment that this overflow payment is related to?"
3275 *
3276 * Ideally we should have a specific column on the
3277 * payment to store this information, so that we don't
3278 * have these kludges.
3279 */
3280 l_pmt_rec.external_bank_account_id :=
3281 x_paperSpecialTab(i).payment_id;
3282
3283 /*
3284 * Set the instruction id for this payment to the
3285 * currently running payment instruction id
3286 */
3287 l_pmt_rec.payment_instruction_id := x_paperSpecialTab(i).
3288 instruction_id;
3289
3290 print_debuginfo(l_module_name, 'Creating dummy overflow '
3291 || ' payment '
3292 || l_payment_id
3293 || ' for payment '
3294 || x_paperSpecialTab(i).payment_id
3295 );
3296
3297 /* add payment to PLSQL table of payments */
3298 x_dummyPaperPmtsTab(x_dummyPaperPmtsTab.COUNT + 1)
3299 := l_pmt_rec;
3300
3301 /*
3302 * No need to add this payment to the payment instructions
3303 * PLSQL table 'x_pmtsInPmtInstrTab'. This is because
3304 * overflow payments are dummy payments that are handled
3305 * separately.
3306 */
3307
3308 /*
3309 * We have just created a dummy payment for overflow
3310 * purposes.
3311 *
3312 * Some documents will need to be updated so that their
3313 * formatting_payment_id is set to the id of the dummy
3314 * payment that we just created.
3315 *
3316 * Originally, all documents will have the same value
3317 * for payment_id and formatting_payment_id. When there
3318 * are overflow documents, then the following will happen:
3319 *
3320 * a. Documents that will fit into one stub will
3321 * contain payment_id same as original document
3322 * id.
3323 *
3324 * b. All documents that will not fit into the stub (a)
3325 * will have to printed on void checks. These will
3326 * be identified because for these documents the
3327 * formatting_payment_id <> the payment id.
3328 *
3329 * So at this point, we have to identify documents
3330 * for this payment that will not fit into the first
3331 * stub and change their formatting_payment_id to the one
3332 * that we generated for the dummy payment. We do this in
3333 * a loop to finish all excess documents.
3334 *
3335 * Example,
3336 * Payment 102 contains 10 documents.
3337 * Check stub contains space for 4 document lines.
3338 *
3339 * Therefore, number of printed checks will be
3340 * (4 docs) + (4 docs) + (2 docs) = 3 checks
3341 * [Void check] [Void check] [Real check]
3342 *
3343 * One check will be the real check, 2 checks are overflow.
3344 * We have already calculated the number of overflow
3345 * checks by the time we come here.
3346 *
3347 * We need to link the documents that will fit into the
3348 * overflow checks to the dummy payments (void checks)
3349 * that we just created.
3350 */
3351
3352 /*
3353 * Change the format_payment_id of 'n' documents of this
3354 * payment to the dummy payment id, where 'n' = num lines
3355 * that will fit on the stub. These 'n' documents
3356 * will be printed on a voided check.
3357 */
3358
3359 /*
3360 * Make sure that the end index does not exceed the
3361 * size of the docs array.
3362 */
3363
3364 /* need to subtract 1 because 'begin index' is 1 based */
3365 IF (l_begin_doc_index +
3366 (x_paperSpecialTab(i).num_lines_per_stub - 1)
3367 > x_overflowDocsTab.COUNT) THEN
3368
3369 l_end_doc_index := x_overflowDocsTab.COUNT;
3370
3371 ELSE
3372
3373 l_end_doc_index := l_begin_doc_index +
3374 (x_paperSpecialTab(i).
3375 num_lines_per_stub - 1);
3376
3377 END IF;
3378
3379 FOR k IN l_begin_doc_index .. l_end_doc_index LOOP
3380
3381 x_overflowDocsTab(k).format_payment_id := l_payment_id;
3382 -- Bug 6486816- copying the overfloW record into the out parameter
3383 x_overflowDocsTab_out(l_begin_doc_index_outrec) :=x_overflowDocsTab(k);
3384 print_debuginfo(l_module_name, 'For document '
3385 || x_overflowDocsTab(k).doc_id
3386 || ' with pmt id '
3387 || x_overflowDocsTab(k).payment_id
3388 || ' format pmt id set to '
3389 || x_overflowDocsTab(k).format_payment_id
3390 );
3391
3392 l_begin_doc_index := l_begin_doc_index + 1;
3393 l_begin_doc_index_outrec := l_begin_doc_index_outrec + 1;
3394 END LOOP;
3395
3396 END LOOP; -- for num_overflow_docs in payment
3397
3398 ELSE
3399
3400 print_debuginfo(l_module_name, 'Not creating any '
3401 || 'overflow payments for payment instruction '
3402 || x_paperSpecialTab(i).instruction_id
3403 || ' because num lines per stub is not specified '
3404 || 'for the associated payment document.'
3405 );
3406
3407 END IF; -- if num overflow docs is not null
3408
3409 IF (x_paperSpecialTab(i).num_overflow_docs IS NOT NULL AND
3410 x_paperSpecialTab(i).num_overflow_docs > 0) THEN
3411
3412 print_debuginfo(l_module_name, 'Added '
3413 || x_paperSpecialTab(i).num_overflow_docs
3414 || ' overflow payments to payment instruction '
3415 || x_paperSpecialTab(i).instruction_id
3416 );
3417
3418 END IF;
3419
3420 END LOOP; -- for each pmt in instruction with process type 'PAPER'
3421 -- Bug 6486816- copy the overflow documents table with all details to
3422 --the variable that is passed to calling function.
3423 x_overflowDocsTab := x_overflowDocsTab_out;
3424 IF (x_overflowDocsTab.COUNT > 0) THEN
3425
3426 FOR i IN x_overflowDocsTab.FIRST .. x_overflowDocsTab.LAST LOOP
3427
3428 print_debuginfo(l_module_name, 'Doc id '
3429 || x_overflowDocsTab(i).doc_id
3430 || ', payment id: '
3431 || x_overflowDocsTab(i).payment_id
3432 || ', format payment id: '
3433 || x_overflowDocsTab(i).format_payment_id
3434 );
3435
3436 END LOOP;
3437
3438 END IF;
3439
3440 print_debuginfo(l_module_name, 'EXIT');
3441
3442 END performSpecialPaperDocHandling;
3443
3444 /*--------------------------------------------------------------------
3445 | NAME:
3446 | updateSetupDocsFlagForInstr
3447 |
3448 | PURPOSE:
3449 |
3450 |
3451 |
3452 | PARAMETERS:
3453 | IN
3454 |
3455 |
3456 | OUT
3457 |
3458 |
3459 | RETURNS:
3460 |
3461 | NOTES:
3462 |
3463 *---------------------------------------------------------------------*/
3464 PROCEDURE updateSetupDocsFlagForInstr(
3465 p_instrId IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
3466 x_paperSpecialTab IN OUT NOCOPY paperPmtsSpecialDocsTabType
3467 )
3468 IS
3469 BEGIN
3470
3471 /*
3472 * Loop through all the paper payments table. If we find any
3473 * payment instruction that matches the given payment
3474 * instruction, set the 'set up docs' flag for the payment
3475 * instruction to 'Y' to indicate that setup documents
3476 * have already been generated for this payment instruction.
3477 */
3478 FOR i IN x_paperSpecialTab.FIRST .. x_paperSpecialTab.LAST LOOP
3479
3480 IF (x_paperSpecialTab(i).instruction_id = p_instrId) THEN
3481 x_paperSpecialTab(i).setup_docs_for_instr_finished := 'Y';
3482 END IF;
3483
3484 END LOOP;
3485
3486 END updateSetupDocsFlagForInstr;
3487
3488 /*--------------------------------------------------------------------
3489 | NAME:
3490 | updatePmtsWithCheckNumbers
3491 |
3492 | PURPOSE:
3493 |
3494 |
3495 |
3496 | PARAMETERS:
3497 | IN
3498 |
3499 | OUT
3500 |
3501 |
3502 | RETURNS:
3503 |
3504 | NOTES:
3505 |
3506 *---------------------------------------------------------------------*/
3507 PROCEDURE updatePmtsWithCheckNumbers(
3508 p_pmtsInPayInstTab IN IBY_PAYINSTR_PUB.pmtsInpmtInstrTabType
3509 )
3510 IS
3511 l_module_name VARCHAR2(200) := G_PKG_NAME || '.updatePmtsWithCheckNumbers';
3512
3513 BEGIN
3514
3515 print_debuginfo(l_module_name, 'ENTER');
3516
3517 /* Normally, this should not happen */
3518 IF (p_pmtsInPayInstTab.COUNT = 0) THEN
3519 print_debuginfo(l_module_name, 'No payments '
3520 || 'were provided to update '
3521 || 'IBY_PAYMENTS_ALL table. Possible data '
3522 || 'corruption issue.');
3523 RETURN;
3524 END IF;
3525
3526 /*
3527 * Update the payments. We cannot use bulk update here
3528 * because the bulk update syntax does not allow us to
3529 * reference individual fields of the PL/SQL record.
3530 *
3531 * TBD: Is there any way to optimize this update?
3532 */
3533 FOR i in p_pmtsInPayInstTab.FIRST..p_pmtsInPayInstTab.LAST LOOP
3534
3535 print_debuginfo(l_module_name, 'Instruction: '
3536 || p_pmtsInPayInstTab(i).pay_instr_id || ', payment: '
3537 || p_pmtsInPayInstTab(i).payment_id);
3538
3539 UPDATE
3540 IBY_PAYMENTS_ALL
3541 SET
3542 paper_document_number = p_pmtsInPayInstTab(i).check_number
3543 WHERE
3544 payment_id = p_pmtsInPayInstTab(i).payment_id
3545 AND payment_status = p_pmtsInPayInstTab(i).payment_status;
3546
3547 END LOOP;
3548
3549 print_debuginfo(l_module_name, 'EXIT');
3550
3551 END updatePmtsWithCheckNumbers;
3552
3553 /*--------------------------------------------------------------------
3554 | NAME:
3555 | isPaperDocNumUsed
3556 |
3557 | PURPOSE:
3558 |
3559 |
3560 |
3561 | PARAMETERS:
3562 | IN
3563 |
3564 | OUT
3565 |
3566 |
3567 | RETURNS:
3568 |
3569 | NOTES:
3570 |
3571 *---------------------------------------------------------------------*/
3572 PROCEDURE isPaperDocNumUsed(
3573 p_payment_doc_id IN IBY_USED_PAYMENT_DOCS.payment_document_id%TYPE,
3574 x_paper_doc_num IN IBY_USED_PAYMENT_DOCS.used_document_number%TYPE,
3575 x_return_status IN OUT NOCOPY VARCHAR2
3576 )
3577 IS
3578
3579 l_module_name VARCHAR2(200) := G_PKG_NAME || '.isPaperDocNumUsed';
3580 l_used_paper_doc_number NUMBER := 0;
3581
3582 BEGIN
3583
3584 print_debuginfo(l_module_name, 'ENTER');
3585
3586 /*
3587 * Check if this paper document number has already
3588 * been used.
3589 */
3590 BEGIN
3591
3592 SELECT
3593 used_document_number
3594 INTO
3595 l_used_paper_doc_number
3596 FROM
3597 IBY_USED_PAYMENT_DOCS
3598 WHERE
3599 payment_document_id = p_payment_doc_id AND
3600 used_document_number = x_paper_doc_num
3601 ;
3602
3603 EXCEPTION
3604
3605 WHEN NO_DATA_FOUND THEN
3606
3607 /* now rows means success */
3608 print_debuginfo(l_module_name, 'Paper document '
3609 || 'number '
3610 || x_paper_doc_num
3611 || ' is unused.'
3612 );
3613
3614 WHEN OTHERS THEN
3615
3616 print_debuginfo(l_module_name, 'Exception occured when '
3617 || 'attempting to get details of paper document '
3618 || x_paper_doc_num
3619 || ' from IBY_USED_PAYMENT_DOCS table.'
3620 );
3621
3622 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
3623 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
3624
3625 x_return_status := FND_API.G_RET_STS_ERROR;
3626
3627 print_debuginfo(l_module_name, 'Returning error response ..');
3628
3629 print_debuginfo(l_module_name, 'EXIT');
3630
3631 RETURN;
3632
3633 END;
3634
3635
3636 x_return_status := FND_API.G_RET_STS_SUCCESS;
3637
3638 print_debuginfo(l_module_name, 'Returning success response ..');
3639 print_debuginfo(l_module_name, 'EXIT');
3640
3641 END isPaperDocNumUsed;
3642
3643 /*--------------------------------------------------------------------
3644 | NAME:
3645 | isSinglePayment
3646 |
3647 | PURPOSE:
3648 | Determines whether the provided list of payments consists of a
3649 | single payment.
3650 |
3651 | If the following conditions are satisfied
3652 |
3653 | a. Only one payment is present in the provided list of payments
3654 | b. The payment service request of the provided payment
3655 | has process type 'IMMEDIATE'
3656 |
3657 | then the payment is considered to be a single payment.
3658 |
3659 | PARAMETERS:
3660 | IN
3661 | p_paperPmtsTab - PLSQL table of paper payments. Ensure that
3662 | this table only consists of real paper payments
3663 | (no dummy payments - setup or overflow payments
3664 | - should be included in this list).
3665 | OUT
3666 | NONE
3667 |
3668 | RETURNS:
3669 | BOOLEAN - TRUE, if the provided payments list consists of
3670 | a single payment
3671 | FALSE, otherwise
3672 |
3673 | NOTES:
3674 |
3675 *---------------------------------------------------------------------*/
3676 FUNCTION isSinglePayment(
3677 p_paperPmtsTab IN paperPmtsSpecialDocsTabType
3678 ) RETURN BOOLEAN
3679 IS
3680
3681 l_module_name VARCHAR2(200) := G_PKG_NAME || '.isSinglePayment';
3682
3683 l_retflag BOOLEAN;
3684 l_process_type IBY_PAY_SERVICE_REQUESTS.process_type%TYPE;
3685 l_pmt_rec paperPmtsSpecialDocsRecType;
3686
3687 BEGIN
3688
3689 print_debuginfo(l_module_name, 'ENTER');
3690
3691 IF (p_paperPmtsTab.COUNT = 0) THEN
3692
3693 print_debuginfo(l_module_name, 'Paper payments list is empty. '
3694 || 'Returning false ..'
3695 );
3696
3697 l_retflag := FALSE;
3698 print_debuginfo(l_module_name, 'EXIT');
3699
3700 RETURN l_retflag;
3701
3702 END IF;
3703
3704 IF (p_paperPmtsTab.COUNT <> 1) THEN
3705
3706 print_debuginfo(l_module_name, 'Paper payments count is more '
3707 || 'than 1. Returning false ..'
3708 );
3709
3710 l_retflag := FALSE;
3711 print_debuginfo(l_module_name, 'EXIT');
3712
3713 RETURN l_retflag;
3714
3715 END IF;
3716
3717 /*
3718 * If we reached here, it means that there is only one
3719 * paper payment in the provided payment array.
3720 *
3721 * Look up the process type of the payment service request
3722 * to confirm that the process type is IMMEDIATE.
3723 *
3724 * These two conditions are necessary and sufficient to
3725 * determine whether a payment is a single payment.
3726 */
3727 BEGIN
3728
3729 l_pmt_rec := p_paperPmtsTab(p_paperPmtsTab.FIRST);
3730
3731 print_debuginfo(l_module_name, 'Provided payment id is '
3732 || l_pmt_rec.payment_id
3733 );
3734
3735 SELECT
3736 req.process_type
3737 INTO
3738 l_process_type
3739 FROM
3740 IBY_PAY_SERVICE_REQUESTS req,
3741 IBY_PAYMENTS_ALL pmt
3742 WHERE
3743 req.payment_service_request_id =
3744 pmt.payment_service_request_id AND
3745 pmt.payment_id = l_pmt_rec.payment_id
3746 ;
3747
3748 print_debuginfo(l_module_name, 'Processing type of parent '
3749 || 'request is '
3750 || l_process_type
3751 );
3752
3753 /*
3754 * Set the return flag based on the processing type.
3755 */
3756 IF (l_process_type = 'IMMEDIATE') THEN
3757
3758 l_retflag := TRUE;
3759
3760 print_debuginfo(l_module_name, 'Setting return flag '
3761 || 'to true.'
3762 );
3763
3764 ELSE
3765
3766 l_retflag := FALSE;
3767
3768 print_debuginfo(l_module_name, 'Setting return flag '
3769 || 'to false because processing type is not IMMEDIATE.'
3770 );
3771
3772 END IF;
3773
3774 EXCEPTION
3775 WHEN OTHERS THEN
3776
3777 print_debuginfo(l_module_name, 'Fatal: Exception when attempting '
3778 || 'to get processing type for payment '
3779 || l_pmt_rec.payment_id,
3780 FND_LOG.LEVEL_UNEXPECTED
3781 );
3782
3783 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
3784 FND_LOG.LEVEL_UNEXPECTED);
3785 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
3786 FND_LOG.LEVEL_UNEXPECTED);
3787
3788 /*
3789 * Propogate exception to caller.
3790 */
3791 RAISE;
3792
3793 END;
3794
3795 print_debuginfo(l_module_name, 'EXIT');
3796 RETURN l_retflag;
3797
3798 END isSinglePayment;
3799
3800 /*--------------------------------------------------------------------
3801 | NAME:
3802 | isContigPaperNumAvlbl
3803 |
3804 | PURPOSE:
3805 | Checks whether the paper document numbers from the given start
3806 | number to the given end number are available contiguoulsy for
3807 | printing.
3808 |
3809 | For the purpose of printing checks, we should always presume
3810 | the the user is going to print checks on prenumbered check stock
3811 | on a tractor feed. Therefore, the check numbering should always
3812 | be contiguous.
3813 |
3814 | PARAMETERS:
3815 | IN
3816 |
3817 | OUT
3818 |
3819 |
3820 | RETURNS:
3821 |
3822 | NOTES:
3823 |
3824 *---------------------------------------------------------------------*/
3825 FUNCTION isContigPaperNumAvlbl(
3826 p_payment_doc_id IN CE_PAYMENT_DOCUMENTS.payment_document_id%TYPE,
3827 p_start_number IN IBY_PAYMENTS_ALL.paper_document_number%TYPE,
3828 p_end_number IN IBY_PAYMENTS_ALL.paper_document_number%TYPE
3829 ) RETURN BOOLEAN
3830 IS
3831
3832 l_module_name VARCHAR2(200) := G_PKG_NAME || '.isContigPaperNumAvlbl';
3833 l_retflag BOOLEAN;
3834 l_paper_doc_num IBY_PAYMENTS_ALL.paper_document_number%TYPE;
3835
3836 l_api_version CONSTANT NUMBER := 1.0;
3837 l_return_status VARCHAR2 (100);
3838
3839 l_msg_count NUMBER;
3840 l_msg_data VARCHAR2(3000);
3841
3842 BEGIN
3843
3844 print_debuginfo(l_module_name, 'ENTER');
3845
3846 print_debuginfo(l_module_name, 'Start #: '
3847 || p_start_number
3848 || ', end #: '
3849 || p_end_number
3850 || ', payment document: '
3851 || p_payment_doc_id
3852 );
3853
3854 IF (p_start_number IS NULL OR
3855 p_end_number IS NULL OR
3856 p_payment_doc_id IS NULL
3857 ) THEN
3858
3859 print_debuginfo(l_module_name, 'Provided params are invalid. '
3860 || 'Returning false.');
3861
3862 l_retflag := FALSE;
3863
3864 print_debuginfo(l_module_name, 'EXIT');
3865 RETURN l_retflag;
3866
3867 END IF;
3868
3869 /*
3870 * Validate the entire contiguous range of provided
3871 * paper document numbers. If even a single paper
3872 * document number fails validation, it means that
3873 * we do not have a contiguous rage of paper document
3874 * numbers available.
3875 */
3876 l_retflag := TRUE;
3877 FOR i IN 1 .. (p_end_number - p_start_number + 1) LOOP -- Bug 6922269
3878
3879 l_paper_doc_num := p_start_number + i - 1;
3880
3881 /*
3882 * Fix for bug 5327347
3883 *
3884 * Pass TRUE for the p_init_msg_list param
3885 * so that the FND message stack is
3886 * re-initialized and we don't show old
3887 * messages to the user.
3888 */
3889 IBY_DISBURSE_UI_API_PUB_PKG.validate_paper_doc_number(
3890 l_api_version,
3891 FND_API.G_TRUE,
3892 p_payment_doc_id,
3893 l_paper_doc_num,
3894 l_return_status,
3895 l_msg_count,
3896 l_msg_data,
3897 FND_API.G_FALSE
3898 );
3899
3900 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3901
3902 print_debuginfo(l_module_name, 'Paper document number '
3903 || l_paper_doc_num
3904 || ' failed validation.'
3905 );
3906
3907 l_retflag := FALSE;
3908
3909 END IF;
3910
3911 END LOOP;
3912
3913 IF (l_retflag = FALSE) THEN
3914
3915 /*
3916 * Fix for bug 5327347:
3917 *
3918 * If we reach here, it means that one of the paper documents
3919 * in the supplied range has already been used.
3920 *
3921 * This means that we will need to display to the
3922 * user a message like 'sufficient contiguous payment
3923 * documents are not available to print this instruction.'
3924 *
3925 * However, the message 'paper doc number # has already been
3926 * used' has been set in the FND message stack already
3927 * by the IBY_DISBURSE_UI_API_PUB_PKG.validate_paper_doc_number(..)
3928 * method.
3929 *
3930 * Clear this message from the stack as it is redundant.
3931 */
3932 FND_MSG_PUB.initialize;
3933
3934 print_debuginfo(l_module_name, 'Returning FALSE');
3935 ELSE
3936 print_debuginfo(l_module_name, 'Returning TRUE');
3937 END IF;
3938
3939 print_debuginfo(l_module_name, 'EXIT');
3940 RETURN l_retflag;
3941
3942 END isContigPaperNumAvlbl;
3943
3944 /*--------------------------------------------------------------------
3945 | NAME:
3946 | isPaperNosUsedOnExistPmt
3947 |
3948 | PURPOSE:
3949 | Checks whether the paper document numbers from the given start
3950 | number to the given end number are already used on any existing
3951 | payments.
3952 |
3953 | PARAMETERS:
3954 | IN
3955 |
3956 | OUT
3957 |
3958 |
3959 | RETURNS:
3960 |
3961 | NOTES:
3962 |
3963 *---------------------------------------------------------------------*/
3964 FUNCTION isPaperNosUsedOnExistPmt(
3965 p_payment_doc_id IN CE_PAYMENT_DOCUMENTS.payment_document_id%TYPE,
3966 p_start_number IN IBY_PAYMENTS_ALL.paper_document_number%TYPE,
3967 p_end_number IN IBY_PAYMENTS_ALL.paper_document_number%TYPE
3968 ) RETURN BOOLEAN
3969 IS
3970
3971 l_module_name VARCHAR2(200) := G_PKG_NAME || '.isPaperNosUsedOnExistPmt';
3972 l_retflag BOOLEAN;
3973
3974 l_paper_doc_num IBY_PAYMENTS_ALL.paper_document_number%TYPE;
3975 l_test_paper_doc_num IBY_PAYMENTS_ALL.paper_document_number%TYPE;
3976 l_test_pmt_id IBY_PAYMENTS_ALL.payment_id%TYPE;
3977
3978 BEGIN
3979
3980 print_debuginfo(l_module_name, 'ENTER');
3981
3982 print_debuginfo(l_module_name, 'Start #: '
3983 || p_start_number
3984 || ', end #: '
3985 || p_end_number
3986 || ', payment document: '
3987 || p_payment_doc_id
3988 );
3989
3990 IF (p_start_number IS NULL OR
3991 p_end_number IS NULL OR
3992 p_payment_doc_id IS NULL
3993 ) THEN
3994
3995 print_debuginfo(l_module_name, 'Provided params are invalid. '
3996 || 'Returning true.');
3997
3998 l_retflag := TRUE;
3999
4000 print_debuginfo(l_module_name, 'EXIT');
4001 RETURN l_retflag;
4002
4003 END IF;
4004
4005 /*
4006 * Validate the entire contiguous range of provided
4007 * paper document numbers. If even a single paper
4008 * document number has already been used on a payment,
4009 * it is an error and we will return TRUE.
4010 */
4011 l_retflag := FALSE;
4012 FOR i IN 1 .. (p_end_number - p_start_number + 1) LOOP -- Bug 6922269
4013
4014 l_paper_doc_num := p_start_number + i - 1;
4015
4016 BEGIN
4017
4018 SELECT
4019 pmt.paper_document_number,
4020 pmt.payment_id
4021 INTO
4022 l_test_paper_doc_num,
4023 l_test_pmt_id
4024 FROM
4025 IBY_PAYMENTS_ALL pmt,
4026 IBY_PAY_INSTRUCTIONS_ALL inst
4027 WHERE
4028 pmt.payment_instruction_id = inst.payment_instruction_id AND
4029 inst.payment_document_id = p_payment_doc_id AND
4030 pmt.paper_document_number = l_paper_doc_num
4031 ;
4032
4033 /*
4034 * If we reached here it means that we were able to
4035 * successfully retrieve a payment with the provided
4036 * paper document number. This implies that the
4037 * paper document number has already been used.
4038 */
4039 print_debuginfo(l_module_name, 'Paper document number '
4040 || l_test_paper_doc_num
4041 || ' has already been used for payment id '
4042 || l_test_pmt_id
4043 );
4044
4045 l_retflag := TRUE;
4046
4047 EXCEPTION
4048 WHEN OTHERS THEN
4049
4050 /*
4051 * Handle exceptions gracefully. Assume that an
4052 * exception means that no data was found i.e., paper
4053 * document number has not been used.
4054 */
4055 NULL;
4056
4057 END;
4058
4059 END LOOP;
4060
4061
4062 IF (l_retflag = FALSE) THEN
4063 print_debuginfo(l_module_name, 'Returning FALSE');
4064 ELSE
4065 print_debuginfo(l_module_name, 'Returning TRUE');
4066 END IF;
4067
4068 print_debuginfo(l_module_name, 'EXIT');
4069 RETURN l_retflag;
4070
4071 END isPaperNosUsedOnExistPmt;
4072
4073 /*--------------------------------------------------------------------
4074 | NAME:
4075 | print_debuginfo
4076 |
4077 | PURPOSE:
4078 | This procedure prints the debug message to the concurrent manager
4079 | log file.
4080 |
4081 | PARAMETERS:
4082 | IN
4083 | p_debug_text - The debug message to be printed
4084 |
4085 | OUT
4086 |
4087 |
4088 | RETURNS:
4089 |
4090 | NOTES:
4091 |
4092 *---------------------------------------------------------------------*/
4093 PROCEDURE print_debuginfo(
4094 p_module IN VARCHAR2,
4095 p_debug_text IN VARCHAR2,
4096 p_debug_level IN VARCHAR2 DEFAULT FND_LOG.LEVEL_STATEMENT
4097 )
4098 IS
4099 l_default_debug_level VARCHAR2(200) := FND_LOG.LEVEL_STATEMENT;
4100 BEGIN
4101
4102 /*
4103 * Set the debug level to the value passed in
4104 * (provided this value is not null).
4105 */
4106 IF (p_debug_level IS NOT NULL) THEN
4107 l_default_debug_level := p_debug_level;
4108 END IF;
4109
4110 /*
4111 * Write the debug message to the concurrent manager log file.
4112 */
4113 IF (l_default_debug_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4114 iby_build_utils_pkg.print_debuginfo(p_module, p_debug_text,
4115 p_debug_level);
4116 END IF;
4117
4118 END print_debuginfo;
4119
4120
4121 END IBY_CHECKNUMBER_PUB;
[Home] [Help]