[Home] [Help]
PACKAGE: APPS.IBY_CHECKNUMBER_PUB
Source
4 --
1 PACKAGE IBY_CHECKNUMBER_PUB AUTHID CURRENT_USER AS
2 /*$Header: ibyckprs.pls 120.23.12010000.2 2009/07/28 12:10:58 asarada ship $*/
3
5 -- For check payments, we have to keep track of setup and
6 -- overflow documents. These are dummy documents that are
7 -- inserted into IBY_DOCS_PAYABLE_ALL table and linked to
8 -- a particular payment.
9 --
10 -- This data structure holds all payments that have processing
11 -- type set to 'PAPER' - i.e., payment payments.
12 --
13 TYPE paperPmtsSpecialDocsRecType IS RECORD (
14 payment_id IBY_PAYMENTS_ALL.payment_id%TYPE,
15 instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
16 num_setup_docs NUMBER,
17 num_overflow_docs NUMBER,
18
19 /*
20 * This has to be retrieved from the profile
21 * on the payment
22 */
23 num_lines_per_stub NUMBER,
24
25 /*
26 * Flag to indicate whether setup docs have
27 * already been generated for a particular
28 * instruction.
29 */
30 setup_docs_for_instr_finished VARCHAR2(1) := 'N',
31
32 /*
33 * Physical paper document number for this payment.
34 * This number is derived from the paper stock.
35 */
36 check_number NUMBER
37 );
38
39 --
40 -- Table of paperPmtsSpecialDocsRecType records.
41 --
42 TYPE paperPmtsSpecialDocsTabType IS TABLE OF paperPmtsSpecialDocsRecType
43 INDEX BY BINARY_INTEGER;
44
45 --
46 -- The attributes of a paper document (usually this
47 -- is check stock).
48 --
49 TYPE paperStockRecType IS RECORD (
50 doc_id CE_PAYMENT_DOCUMENTS.
51 payment_document_id%TYPE,
52 doc_name CE_PAYMENT_DOCUMENTS.
53 payment_document_name%TYPE,
54 num_setup_docs CE_PAYMENT_DOCUMENTS.
55 number_of_setup_documents%TYPE,
56 num_lines_per_stub CE_PAYMENT_DOCUMENTS.
57 number_of_lines_per_remit_stub%TYPE
58 );
59
60 --
61 -- Table of paper document attributes
62 --
63 TYPE paperStocksTabType IS TABLE OF paperStockRecType
64 INDEX BY BINARY_INTEGER;
65
66 --
67 -- Record to temporarily hold count of documents payable
68 -- under a payment.
69 --
70 TYPE docsInPmtCountRecType IS RECORD (
71 doc_count NUMBER,
72 payment_id IBY_PAYMENTS_ALL.
73 payment_id%TYPE
74 );
75
76 --
77 -- Temporary table to hold count of docs under a payment.
78 -- This information will be copied into pmtsInPmtInstrTabType
79 -- table for more permanent storage in the pay instr creation
80 -- program.
81 --
82 TYPE docsInPmtCountTabType IS TABLE OF docsInPmtCountRecType
83 INDEX BY BINARY_INTEGER;
84
85 --
86 -- PLSQL table of documents payable. This table is used to
87 -- insert dummy documents payable into IBY_DOCS_PAYABLE_ALL
88 -- (for handling setup and overflow documents).
89 --
90 TYPE docsTabType IS TABLE OF iby_docs_payable_all%ROWTYPE
91 INDEX BY BINARY_INTEGER;
92
93 --
94 -- Record that holds the formatting_payment_id for a particular
95 -- document.
96 --
97 -- If the document is an overflow document, the formatting_payment_id
98 -- will be different from the payment_id, otherwise, both these
99 -- will be the same for a particular document.
100 --
101 TYPE overflowDocsRecType IS RECORD (
102 doc_id IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE,
103 payment_id IBY_DOCS_PAYABLE_ALL.payment_id%TYPE,
104 pmt_currency IBY_DOCS_PAYABLE_ALL.payment_currency_code%TYPE,
105 pmt_function IBY_DOCS_PAYABLE_ALL.payment_function%TYPE,
106 format_payment_id IBY_DOCS_PAYABLE_ALL.formatting_payment_id%TYPE,
107 org_id IBY_DOCS_PAYABLE_ALL.org_id%TYPE,
108 org_type IBY_DOCS_PAYABLE_ALL.org_type%TYPE
109 );
110
111 --
112 -- Table of overflow doc recs.
113 --
114 TYPE overflowDocsTabType IS TABLE OF overflowDocsRecType
115 INDEX BY BINARY_INTEGER;
116
117 /*--------------------------------------------------------------------
118 | NAME:
119 | performCheckNumbering
120 |
121 | PURPOSE:
122 |
123 |
124 |
125 | PARAMETERS:
126 | IN
127 |
128 |
129 | OUT
130 |
131 |
132 | RETURNS:
133 |
134 | NOTES:
135 |
136 *---------------------------------------------------------------------*/
137 PROCEDURE performCheckNumbering(
138 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.
139 payment_instruction_id%TYPE,
140 p_pmt_document_id IN CE_PAYMENT_DOCUMENTS.
141 payment_document_id%TYPE,
142 p_user_assgn_num IN IBY_PAYMENTS_ALL.
143 paper_document_number%TYPE,
144 x_return_status IN OUT NOCOPY VARCHAR2,
145 x_return_message IN OUT NOCOPY VARCHAR2,
146 x_msg_count IN OUT NOCOPY NUMBER,
147 x_msg_data IN OUT NOCOPY VARCHAR2
148 );
149
150 /*--------------------------------------------------------------------
151 | NAME:
152 | populateDocumentCount
153 |
154 | PURPOSE:
155 |
156 |
157 |
158 | PARAMETERS:
159 | IN
160 |
161 | OUT
162 |
163 |
164 | RETURNS:
165 |
166 | NOTES:
167 |
168 *---------------------------------------------------------------------*/
169 PROCEDURE populateDocumentCount(
170 x_pmtsInPmtInstrTab IN OUT NOCOPY IBY_PAYINSTR_PUB.pmtsInpmtInstrTabType
171 );
172
173 /*--------------------------------------------------------------------
174 | NAME:
175 | updatePaymentInstructions
176 |
177 | PURPOSE:
178 | Performs an update of all created instructions from PLSQL
179 | table into IBY_PAY_INSTRUCTIONS_ALL table.
180 |
181 | The created instructions have already been inserted into
182 | IBY_PAY_INSTRUCTIONS_ALL after grouping. So we only need to
183 | update certain fields of the instruction that have been
184 | changed after the grouping was performed.
185 |
186 | PARAMETERS:
187 | IN
188 |
189 |
190 | OUT
191 |
192 |
193 | RETURNS:
194 |
195 | NOTES:
196 |
197 *---------------------------------------------------------------------*/
198 PROCEDURE updatePaymentInstructions(
199 p_payInstrTab IN IBY_PAYINSTR_PUB.pmtInstrTabType
200 );
201
202 /*--------------------------------------------------------------------
203 | NAME:
204 | performDBUpdates
205 |
206 | PURPOSE:
207 |
208 |
209 |
210 |
211 | PARAMETERS:
212 | IN
213 |
214 |
215 | OUT
216 |
217 |
218 | RETURNS:
219 |
220 | NOTES:
221 |
222 *---------------------------------------------------------------------*/
223 PROCEDURE performDBUpdates(
224 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.
225 payment_instruction_id%TYPE,
226 x_pmtsInPmtInstrTab IN OUT NOCOPY IBY_PAYINSTR_PUB.pmtsInpmtInstrTabType,
227 x_dummyPaperPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
228 x_setupDocsTab IN OUT NOCOPY docsTabType,
229 x_overflowDocsTab IN OUT NOCOPY overflowDocsTabType,
230 x_insErrorsTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
231 x_insTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
232 trxnErrTokenTabType
233 );
234
235 /*--------------------------------------------------------------------
236 | NAME:
237 | processPaperPayments
238 |
239 | PURPOSE:
240 |
241 |
242 |
243 | PARAMETERS:
244 | IN
245 |
246 |
247 | OUT
248 |
249 |
250 | RETURNS:
251 |
252 | NOTES:
253 |
254 *---------------------------------------------------------------------*/
255 PROCEDURE processPaperPayments(
256 p_pmt_document_id IN CE_PAYMENT_DOCUMENTS.payment_document_id%TYPE,
257 p_user_assgn_num IN IBY_PAYMENTS_ALL.paper_document_number%TYPE,
258 x_pmtInstrRec IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE,
259 x_pmtsInPmtInstrTab IN OUT NOCOPY IBY_PAYINSTR_PUB.pmtsInpmtInstrTabType,
260 x_dummyPaperPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
261 x_setupDocsTab IN OUT NOCOPY docsTabType,
262 x_overflowDocsTab IN OUT NOCOPY overflowDocsTabType,
263 x_instrErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
264 x_insTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
265 trxnErrTokenTabType,
266 x_return_status IN OUT NOCOPY NUMBER,
267 x_return_message IN OUT NOCOPY VARCHAR2,
268 x_msg_count IN OUT NOCOPY NUMBER,
269 x_msg_data IN OUT NOCOPY VARCHAR2
270 );
271
272 /*--------------------------------------------------------------------
273 | NAME:
274 | getNumPrintedDocsByFormula
275 |
276 | PURPOSE:
277 |
278 |
279 | PARAMETERS:
280 | IN
281 |
282 | OUT
283 |
284 |
285 | RETURNS:
286 |
287 | NOTES:
288 |
289 *---------------------------------------------------------------------*/
290 FUNCTION getNumPrintedDocsByFormula(
291 p_num_docs_payable IN NUMBER,
292 p_num_lines_per_stub IN NUMBER
293 )
294 RETURN NUMBER;
295
296 /*--------------------------------------------------------------------
297 | NAME:
301 |
298 | getDocumentCountForPayment
299 |
300 | PURPOSE:
302 |
303 |
304 | PARAMETERS:
305 | IN
306 |
307 | OUT
308 |
309 |
310 | RETURNS:
311 |
312 | NOTES:
313 |
314 *---------------------------------------------------------------------*/
315 FUNCTION getDocumentCountForPayment(
316 p_payment_id IN IBY_PAYMENTS_ALL.payment_id%TYPE,
317 p_pmtsInPmtInstrTab IN IBY_PAYINSTR_PUB.pmtsInpmtInstrTabType
318 )
319 RETURN NUMBER;
320
321 /*--------------------------------------------------------------------
322 | NAME:
323 | performSpecialPaperDocHandling
324 |
325 | PURPOSE:
326 |
327 |
328 |
329 | PARAMETERS:
330 | IN
331 |
332 |
333 | OUT
334 |
335 |
336 | RETURNS:
337 |
338 | NOTES:
339 |
340 *---------------------------------------------------------------------*/
341 PROCEDURE performSpecialPaperDocHandling(
342 x_paperSpecialTab IN OUT NOCOPY paperPmtsSpecialDocsTabType,
343 x_dummyPaperPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
344 x_setupDocsTab IN OUT NOCOPY docsTabType,
345 x_overflowDocsTab IN OUT NOCOPY overflowDocsTabType
346 );
347
348 /*--------------------------------------------------------------------
349 | NAME:
350 | updatePmtsWithCheckNumbers
351 |
352 | PURPOSE:
353 |
354 |
355 |
356 | PARAMETERS:
357 | IN
358 |
359 | OUT
360 |
361 |
362 | RETURNS:
363 |
364 | NOTES:
365 |
366 *---------------------------------------------------------------------*/
367 PROCEDURE updatePmtsWithCheckNumbers(
368 p_pmtsInPayInstTab IN IBY_PAYINSTR_PUB.pmtsInpmtInstrTabType
369 );
370
371 /*--------------------------------------------------------------------
372 | NAME:
373 | updateSetupDocsFlagForInstr
374 |
375 | PURPOSE:
376 |
377 |
378 |
379 | PARAMETERS:
380 | IN
381 |
382 |
383 | OUT
384 |
385 |
386 | RETURNS:
387 |
388 | NOTES:
389 |
390 *---------------------------------------------------------------------*/
391 PROCEDURE updateSetupDocsFlagForInstr(
392 p_instrId IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
393 x_paperSpecialTab IN OUT NOCOPY paperPmtsSpecialDocsTabType
394 );
395
396 /*--------------------------------------------------------------------
397 | NAME:
398 | insertPaperDocuments
399 |
400 | PURPOSE:
401 |
402 |
403 |
404 | PARAMETERS:
405 | IN
406 |
407 |
408 | OUT
409 |
410 |
411 | RETURNS:
412 |
413 | NOTES:
414 |
415 *---------------------------------------------------------------------*/
416 PROCEDURE insertPaperDocuments(
417 p_paperPmtsTab IN IBY_PAYGROUP_PUB.paymentTabType,
418 p_setupDocsTab IN docsTabType,
419 p_overflowDocsTab IN overflowDocsTabType
420 );
421
422 /*--------------------------------------------------------------------
423 | NAME:
424 | splitPaymentsByType
425 |
426 | PURPOSE:
427 |
428 |
429 |
430 | PARAMETERS:
431 | IN
432 |
433 |
434 | OUT
435 |
436 |
437 | RETURNS:
438 |
439 | NOTES:
440 |
441 *---------------------------------------------------------------------*/
442 PROCEDURE splitPaymentsByType(
443 p_paperPmtsTab IN IBY_PAYGROUP_PUB.paymentTabType,
444 x_setupPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
445 x_overflowPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType
446 );
447
448 /*--------------------------------------------------------------------
449 | NAME:
450 | assignCheckNumbers
451 |
452 | PURPOSE:
453 |
454 |
455 |
456 | PARAMETERS:
457 | IN
458 |
459 |
460 | OUT
461 |
462 |
463 | RETURNS:
464 |
465 | NOTES:
466 |
467 *---------------------------------------------------------------------*/
468 PROCEDURE assignCheckNumbers(
469 x_pmtInstrRec IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE,
470 p_payment_doc_id IN CE_PAYMENT_DOCUMENTS.payment_document_id%TYPE,
471 p_user_assgn_num IN IBY_PAYMENTS_ALL.paper_document_number%TYPE,
472 x_paperPmtsTab IN OUT NOCOPY paperPmtsSpecialDocsTabType,
473 x_dummyPaperPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
474 x_instrErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
475 x_insTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
476 trxnErrTokenTabType,
477 x_return_message IN OUT NOCOPY VARCHAR2,
478 x_return_status IN OUT NOCOPY NUMBER,
479 x_msg_count IN OUT NOCOPY NUMBER,
480 x_msg_data IN OUT NOCOPY VARCHAR2
481 );
482
483
484 /*--------------------------------------------------------------------
485 | NAME:
486 | assignElectronicCheckNumbers
487 |
488 | PURPOSE:
489 |
490 |
491 |
492 | PARAMETERS:
493 | IN
494 |
495 |
496 | OUT
497 |
498 |
499 | RETURNS:
500 |
501 | NOTES:
502 |
503 *---------------------------------------------------------------------*/
504 PROCEDURE assignElectronicCheckNumbers(
505 x_pmtInstrRec IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE,
506 p_payment_doc_id IN CE_PAYMENT_DOCUMENTS.payment_document_id%TYPE,
507 p_user_assgn_num IN IBY_PAYMENTS_ALL.paper_document_number%TYPE,
508 x_paperPmtsTab IN OUT NOCOPY paperPmtsSpecialDocsTabType,
509 x_dummyPaperPmtsTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
510 x_instrErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
511 x_insTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
512 trxnErrTokenTabType,
513 x_return_message IN OUT NOCOPY VARCHAR2,
514 x_return_status IN OUT NOCOPY NUMBER,
515 x_msg_count IN OUT NOCOPY NUMBER,
516 x_msg_data IN OUT NOCOPY VARCHAR2
517 );
518
519 /*--------------------------------------------------------------------
520 | NAME:
521 | isPaperDocNumUsed
522 |
523 | PURPOSE:
524 |
525 |
526 |
527 | PARAMETERS:
528 | IN
529 |
530 | OUT
531 |
532 |
533 | RETURNS:
534 |
535 | NOTES:
536 |
537 *---------------------------------------------------------------------*/
538 PROCEDURE isPaperDocNumUsed(
539 p_payment_doc_id IN IBY_USED_PAYMENT_DOCS.payment_document_id%TYPE,
540 x_paper_doc_num IN IBY_USED_PAYMENT_DOCS.used_document_number%TYPE,
541 x_return_status IN OUT NOCOPY VARCHAR2
542 );
543
544 /*--------------------------------------------------------------------
545 | NAME:
546 | isSinglePayment
547 |
548 | PURPOSE:
549 |
550 |
551 |
552 | PARAMETERS:
553 | IN
554 |
555 | OUT
556 |
557 |
558 | RETURNS:
559 |
560 | NOTES:
561 |
562 *---------------------------------------------------------------------*/
563 FUNCTION isSinglePayment(
564 p_paperPmtsTab IN paperPmtsSpecialDocsTabType
565 ) RETURN BOOLEAN;
566
567 /*--------------------------------------------------------------------
568 | NAME:
569 | isContigPaperNumAvlbl
570 |
571 | PURPOSE:
572 |
573 |
574 |
575 | PARAMETERS:
576 | IN
577 |
578 | OUT
579 |
580 |
581 | RETURNS:
582 |
583 | NOTES:
584 |
585 *---------------------------------------------------------------------*/
586 FUNCTION isContigPaperNumAvlbl(
587 p_payment_doc_id IN CE_PAYMENT_DOCUMENTS.payment_document_id%TYPE,
588 p_start_number IN IBY_PAYMENTS_ALL.paper_document_number%TYPE,
589 p_end_number IN IBY_PAYMENTS_ALL.paper_document_number%TYPE
590 ) RETURN BOOLEAN;
591
592 /*--------------------------------------------------------------------
593 | NAME:
594 | isPaperNosUsedOnExistPmt
595 |
596 | PURPOSE:
597 | Checks whether the paper document numbers from the given start
598 | number to the given end number are already used on any existing
599 | payments.
600 |
601 | PARAMETERS:
602 | IN
603 |
604 | OUT
605 |
606 |
607 | RETURNS:
608 |
609 | NOTES:
610 |
611 *---------------------------------------------------------------------*/
612 FUNCTION isPaperNosUsedOnExistPmt(
613 p_payment_doc_id IN CE_PAYMENT_DOCUMENTS.payment_document_id%TYPE,
614 p_start_number IN IBY_PAYMENTS_ALL.paper_document_number%TYPE,
615 p_end_number IN IBY_PAYMENTS_ALL.paper_document_number%TYPE
616 ) RETURN BOOLEAN;
617
618 END IBY_CHECKNUMBER_PUB;