[Home] [Help]
PACKAGE: APPS.IBY_PAYINSTR_PUB
Source
1 PACKAGE IBY_PAYINSTR_PUB AUTHID CURRENT_USER AS
2 /*$Header: ibypymis.pls 120.32 2011/05/12 11:12:21 pschalla ship $*/
3
4 /*
5 * This record corresponds to a row in the IBY_PAY_INSTRUCTIONS_ALL
6 * table.
7 *
8 * A PLSQL table of these records are created after applying
9 * grouping rules and this table is used in bulk inserting
10 * these payment instructions into the IBY_PAY_INSTRUCTIONS_ALL
11 * table.
12 *
13 * The record docsInPaymentRecType holds the payments
14 * corresponding to this payment instruction.
15 */
16 --
17 -- This PLSQL table corresponds to IBY_PAY_INSTRUCTIONS_ALL and it
18 -- will be used in bulk updating the IBY_PAY_INSTRUCTIONS_ALL table.
19 --
20 TYPE pmtInstrTabType IS TABLE OF IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE
21 INDEX BY BINARY_INTEGER;
22
23 --
24 -- This record needs to be created because of the fact that
25 -- there is no payment id column in IBY_PAY_INSTRUCTIONS_ALL table.
26 -- So we cannot add the payment id as a field in the pmtInstrRecType
27 -- record (adding this field to the record will cause a syntax
28 -- error during the bulk update).
29 --
30 -- Therefore, we need a separate data structure to keep track
31 -- of the payments that are part of a payment instruction. The
32 -- pmtsInpmtInstrRecType is that data structure.
33 -- After all the grouping operations are completed, the
34 -- IBY_PAYMENTS_ALL table needs to be updated to indicate the
35 -- PAYMENT_INSTRUCTION_ID for each payment that has been put
36 -- into a payment instruction. The pmtsInpmtInstrRecType
37 -- is used for this update.
38 --
39 -- The record pmtInstrRecType holds the payment instructions
40 -- corresponding to these payments.
41 --
42 TYPE pmtsInPmtInstrRecType IS RECORD (
43 payment_id
44 IBY_PAYMENTS_ALL.payment_id%TYPE,
45 pay_instr_id
46 IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
47 payment_amount
48 IBY_PAYMENTS_ALL.payment_amount%TYPE,
49 payment_currency
50 IBY_PAYMENTS_ALL.payment_currency_code%TYPE,
51 payment_status
52 IBY_PAYMENTS_ALL.payment_status%TYPE
53 := 'INSTRUCTION_CREATED',
54 profile_id
55 IBY_PAYMENTS_ALL.payment_profile_id%TYPE,
56 processing_type
57 IBY_PAYMENT_PROFILES.processing_type%TYPE,
58 pmt_details_len
59 NUMBER,
60 document_count
61 NUMBER,
62 separate_remit_advice_flag
63 IBY_PAYMENTS_ALL.separate_remit_advice_req_flag%TYPE := 'N',
64 check_number
65 IBY_PAYMENTS_ALL.paper_document_number%TYPE
66 );
67
68 --
69 -- Used to update of the IBY_PAYMENTS_ALL table.
70 --
71 TYPE pmtsInPmtInstrTabType IS TABLE OF pmtsInPmtInstrRecType
72 INDEX BY BINARY_INTEGER;
73
74 --
75 -- For check payments, we have to keep track of setup and
76 -- overflow documents. These are dummy documents that are
77 -- inserted into IBY_DOCS_PAYABLE_ALL table and linked to
78 -- a particular payment.
79 --
80 -- This data structure holds all payments that have processing
81 -- type set to 'PAPER' - i.e., payment payments.
82 --
83 TYPE paperPmtsSpecialDocsRecType IS RECORD (
84 payment_id IBY_PAYMENTS_ALL.payment_id%TYPE,
85 instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
86 num_setup_docs NUMBER,
87 num_overflow_docs NUMBER,
88
89 /*
90 * This has to be retrieved from the profile
91 * on the payment
92 */
93 num_lines_per_stub NUMBER,
94
95 /*
96 * Flag to indicate whether setup docs have
97 * already been generated for a particular
98 * instruction.
99 */
100 setup_docs_for_instr_finished VARCHAR2(1) := 'N',
101
102 /*
103 * Physical paper document number for this payment.
104 * This number is derived from the paper stock.
105 */
106 check_number NUMBER
107 );
108
109 --
110 -- Table of paperPmtsSpecialDocsRecType records.
111 --
112 TYPE paperPmtsSpecialDocsTabType IS TABLE OF paperPmtsSpecialDocsRecType
113 INDEX BY BINARY_INTEGER;
114
115 --
116 -- This record stores all the document fields that are used in
117 -- as criteria for grouping a document into a payment. Each
118 -- of these fields will result in a grouping rule.
119 --
120 -- Some of the fields of this record are not used specifically
121 -- for grouping, but for raising business events etc.
122 -- e.g., the calling app pay req id
123 --
124 -- Some of the grouping criteria are user defined; these are
125 -- specified in the IBY_PMT_CREATION_RULES table. This record
126 -- contains placeholder for the user defined grouping fields
127 -- as well.
128 --
129 TYPE instrGroupCriteriaType IS RECORD (
130 calling_app_payreq_cd
131 IBY_PAY_SERVICE_REQUESTS.call_app_pay_service_req_code%TYPE,
132 calling_app_id
133 IBY_PAY_SERVICE_REQUESTS.calling_app_id%TYPE,
134 pay_service_req_id
135 IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE,
136 payment_id
137 IBY_PAYMENTS_ALL.payment_id%TYPE,
138 int_bank_acct_id
139 IBY_PAYMENTS_ALL.internal_bank_account_id%TYPE,
140 payment_profile_id
141 IBY_PAYMENTS_ALL.payment_profile_id%TYPE,
142 org_id
143 IBY_PAYMENTS_ALL.org_id%TYPE,
144 org_type
148 payment_currency
145 IBY_PAYMENTS_ALL.org_type%TYPE,
146 le_id
147 IBY_PAYMENTS_ALL.legal_entity_id%TYPE,
149 IBY_PAYMENTS_ALL.payment_currency_code%TYPE,
150 payment_amount
151 IBY_PAYMENTS_ALL.payment_amount%TYPE,
152 payment_date
153 IBY_PAYMENTS_ALL.payment_date%TYPE,
154 payment_function
155 IBY_PAYMENTS_ALL.payment_function%TYPE,
156 payment_reason_code
157 IBY_PAYMENTS_ALL.payment_reason_code%TYPE,
158 payment_reason_comments
159 IBY_PAYMENTS_ALL.payment_reason_comments%TYPE,
160 pmt_details_length
161 NUMBER,
162 pmt_prom_note_flag
163 IBY_PAYMENTS_ALL.bill_payable_flag%TYPE,
164 ppr_id
165 IBY_PAYMENTS_ALL.payment_service_request_id%TYPE,
166 rfc_identifier
167 HZ_CODE_ASSIGNMENTS.class_code%TYPE,
168 payment_method_code
169 IBY_PAYMENTS_ALL.payment_method_code%TYPE,
170 pmt_date_flag
171 IBY_INSTR_CREATION_RULES.group_by_payment_date%TYPE,
172 pmt_curr_flag
173 IBY_INSTR_CREATION_RULES.group_by_payment_currency%TYPE,
174 max_payments_flag
175 IBY_INSTR_CREATION_RULES.group_by_max_payments_flag%TYPE,
176 max_payments_limit
177 IBY_INSTR_CREATION_RULES.max_payments_per_instruction%TYPE,
178 int_bank_acct_flag
179 IBY_INSTR_CREATION_RULES.group_by_internal_bank_account%TYPE,
180 max_amount_flag
181 IBY_INSTR_CREATION_RULES.group_by_max_instruction_flag%TYPE,
182 max_amount_limit
183 IBY_INSTR_CREATION_RULES.max_amount_per_instr_value%TYPE,
184 max_amount_currency
185 IBY_INSTR_CREATION_RULES.max_amount_per_instr_curr_code%TYPE,
186 max_amount_fx_rate_type
187 IBY_INSTR_CREATION_RULES.max_amount_fx_rate_type%TYPE,
188 pay_service_req_flag
189 IBY_INSTR_CREATION_RULES.group_by_pay_service_request%TYPE,
190 le_flag
191 IBY_INSTR_CREATION_RULES.group_by_legal_entity%TYPE,
192 org_flag
193 IBY_INSTR_CREATION_RULES.group_by_organization%TYPE,
194 pmt_function_flag
195 IBY_INSTR_CREATION_RULES.group_by_payment_function%TYPE,
196 pmt_reason_flag
197 IBY_INSTR_CREATION_RULES.group_by_payment_reason%TYPE,
198 prom_note_flag
199 IBY_INSTR_CREATION_RULES.group_by_bill_payable%TYPE,
200 ppr_flag
201 IBY_INSTR_CREATION_RULES.group_by_pay_service_request%TYPE,
202 rfc_flag
203 IBY_INSTR_CREATION_RULES.group_by_rfc%TYPE,
204 pmt_method_flag
205 IBY_INSTR_CREATION_RULES.group_by_payment_method%TYPE
206 );
207
208 --
209 -- Table of payment grouping criteria.
210 --
211 TYPE instrGroupCriteriaTabType IS TABLE OF instrGroupCriteriaType
212 INDEX BY BINARY_INTEGER;
213
214 --
215 -- This record stores one validation sets applicable to
216 -- a particular payment.
217 --
218 TYPE instructionValSetsRec IS RECORD (
219 val_assign_id
220 IBY_VAL_ASSIGNMENTS.validation_assignment_id%TYPE,
221 val_set_id
222 IBY_VALIDATION_SETS_VL.validation_set_code%TYPE,
223 val_code_package
224 IBY_VALIDATION_SETS_VL.validation_code_package%TYPE,
225 val_code_entry_pt
226 IBY_VALIDATION_SETS_VL.validation_code_entry_point%TYPE
227 );
228
229 --
230 -- Table of validation sets applicable to a particular payment
231 --
232 TYPE instructionValSetsTab IS TABLE OF instructionValSetsRec
233 INDEX BY BINARY_INTEGER;
234
235 --
236 -- Record to temporarily hold count of documents payable
237 -- under a payment.
238 --
239 TYPE docsInPmtCountRecType IS RECORD (
240 doc_count NUMBER,
241 payment_id IBY_PAYMENTS_ALL.
242 payment_id%TYPE
243 );
244
245 --
246 -- Temporary table to hold count of docs under a payment.
247 -- This information will be copied into pmtsInPmtInstrTabType
248 -- table for more permanent storage in the pay instr creation
249 -- program.
250 --
251 TYPE docsInPmtCountTabType IS TABLE OF docsInPmtCountRecType
252 INDEX BY BINARY_INTEGER;
253
254 --
255 -- PLSQL table of documents payable. This table is used to
256 -- insert dummy documents payable into IBY_DOCS_PAYABLE_ALL
257 -- (for handling setup and overflow documents).
258 --
259 TYPE docsTabType IS TABLE OF iby_docs_payable_all%ROWTYPE
260 INDEX BY BINARY_INTEGER;
261
262 --
263 -- Record that holds the formatting_payment_id for a particular
264 -- document.
265 --
266 -- If the document is an overflow document, the formatting_payment_id
267 -- will be different from the payment_id, otherwise, both these
268 -- will be the same for a particular document.
269 --
270 TYPE overflowDocsRecType IS RECORD (
271 doc_id IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE,
272 payment_id IBY_DOCS_PAYABLE_ALL.payment_id%TYPE,
273 format_payment_id IBY_DOCS_PAYABLE_ALL.formatting_payment_id%TYPE
274 );
275
276 --
277 -- Table of overflow doc recs.
278 --
279 TYPE overflowDocsTabType IS TABLE OF overflowDocsRecType
280 INDEX BY BINARY_INTEGER;
281
282 --
283 -- Record to hold a payment after sorting. After sorting,
284 -- each payment will be assigned a payment reference number.
285 --
286 TYPE sortedPmtRecType IS RECORD (
287 payment_id IBY_PAYMENTS_ALL.payment_id%TYPE,
288 payment_ref IBY_PAYMENTS_ALL.payment_reference_number%TYPE,
289 instr_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
290 instr_status IBY_PAY_INSTRUCTIONS_ALL.
291 payment_instruction_status%TYPE,
292 ca_id IBY_PAY_SERVICE_REQUESTS.calling_app_id%TYPE,
293 le_id IBY_PAYMENTS_ALL.legal_entity_id%TYPE,
294 doc_cat_code IBY_PAYMENT_METHODS_VL.document_category_code%TYPE,
295 payment_date IBY_PAYMENTS_ALL.payment_date%TYPE,
296 ledger_id GL_LEDGER_LE_V.ledger_id%TYPE := -1,
297 sequence_number IBY_PAYMENTS_ALL.document_sequence_value%TYPE := -1,
298 sequence_id FND_DOCUMENT_SEQUENCES.doc_sequence_id%TYPE := -1
299 );
300
301 --
302 -- Table of sorted payment records.
303 --
304 TYPE sortedPmtTabType IS TABLE OF sortedPmtRecType
305 INDEX BY BINARY_INTEGER;
306
307 --
308 -- Possible sort options along with the sort order for a single
309 -- payment profile.
310 --
311 TYPE sortOptionsRecType IS RECORD (
312 pmt_profile_cd IBY_INSTR_CREATION_RULES.system_profile_code%TYPE,
313 sort_option_1 IBY_INSTR_CREATION_RULES.sort_option_1%TYPE,
314 sort_order_1 IBY_INSTR_CREATION_RULES.sort_order_1%TYPE,
315 sort_option_2 IBY_INSTR_CREATION_RULES.sort_option_2%TYPE,
316 sort_order_2 IBY_INSTR_CREATION_RULES.sort_order_2%TYPE,
317 sort_option_3 IBY_INSTR_CREATION_RULES.sort_option_3%TYPE,
318 sort_order_3 IBY_INSTR_CREATION_RULES.sort_order_3%TYPE
319 );
320
321 --
322 -- Table of sort options across profiles.
323 --
324 TYPE sortOptionsTabType IS TABLE OF sortOptionsRecType
325 INDEX BY BINARY_INTEGER;
326
327 --
328 --
329 -- These two tables store the distinct payment
330 -- functions, and orgs that are present in a
331 -- payment instruction.
332 --
333 -- The disbursement UI uses the data in these tables to
334 -- restrict access to the user (depending upon the
335 -- users' payment function and organization).
336 --
337
338 --
339 -- Table of distinct access types.
340 --
341 TYPE distinctPmtFxAccessTab IS TABLE OF IBY_PROCESS_FUNCTIONS%ROWTYPE
342 INDEX BY BINARY_INTEGER;
343
344 TYPE distinctOrgAccessTab IS TABLE OF IBY_PROCESS_ORGS%ROWTYPE
345 INDEX BY BINARY_INTEGER;
346
347
348 /*--------------------------------------------------------------------
349 | NAME:
350 | createPaymentInstructions
351 |
352 | PURPOSE:
353 |
354 |
355 |
356 | PARAMETERS:
357 | IN
358 |
359 |
360 | OUT
361 |
362 |
363 | RETURNS:
364 |
365 | NOTES:
366 |
367 *---------------------------------------------------------------------*/
368 PROCEDURE createPaymentInstructions(
369
370 /*-- processing criteria --*/
371 p_processing_type IN IBY_PAYMENT_PROFILES.
372 processing_type%TYPE,
373 p_pmt_document_id IN CE_PAYMENT_DOCUMENTS.
374 payment_document_id%TYPE,
375 p_printer_name IN VARCHAR2,
376 p_print_now_flag IN VARCHAR2,
377 p_transmit_now_flag IN VARCHAR2,
378
379 /*-- user/admin assigned criteria --*/
380 p_admin_assigned_ref IN IBY_PAY_INSTRUCTIONS_ALL.
381 pay_admin_assigned_ref_code%TYPE,
382 p_comments IN IBY_PAY_INSTRUCTIONS_ALL.
383 comments%TYPE,
384
385 /*-- selection criteria --*/
386 p_payment_profile_id IN IBY_PAYMENTS_ALL.
387 payment_profile_id%TYPE,
388 p_calling_app_id IN IBY_PAY_SERVICE_REQUESTS.
389 calling_app_id%TYPE,
390 p_calling_app_payreq_cd IN IBY_PAY_SERVICE_REQUESTS.
391 call_app_pay_service_req_code
392 %TYPE,
393 p_payreq_id IN IBY_PAY_SERVICE_REQUESTS.
394 payment_service_request_id
395 %TYPE,
396 p_internal_bank_account_id IN IBY_PAYMENTS_ALL.
397 internal_bank_account_id%TYPE,
398 p_payment_currency IN IBY_PAYMENTS_ALL.
399 payment_currency_code%TYPE,
400 p_le_id IN IBY_PAYMENTS_ALL.
401 legal_entity_id%TYPE,
402 p_org_id IN IBY_PAYMENTS_ALL.org_id%TYPE,
403 p_org_type IN IBY_PAYMENTS_ALL.org_type%TYPE,
404 p_payment_from_date IN IBY_PAYMENTS_ALL.payment_date%TYPE,
405 p_payment_to_date IN IBY_PAYMENTS_ALL.payment_date%TYPE,
406
407 /*-- single payments / batch flow identifier --*/
408 p_single_pmt_flow_flag IN VARCHAR2 DEFAULT 'N',
409
410 /*-- out params --*/
411 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
412 x_return_status IN OUT NOCOPY VARCHAR2,
413 x_msg_count OUT NOCOPY NUMBER,
414 x_msg_data OUT NOCOPY VARCHAR2
415 );
416
417 /*--------------------------------------------------------------------
418 | NAME:
419 | recreatePaymentInstruction
420 |
421 | PURPOSE:
422 |
423 |
424 | PARAMETERS:
425 | IN
426 | p_instruction_id
427 | ID of payment instruction to be re-created.
428 |
429 | OUT
430 | x_return_status
431 | -1 to indicate failure
432 | 0 to indicate success
433 |
434 | RETURNS:
435 |
436 | NOTES:
437 |
438 *---------------------------------------------------------------------*/
439 PROCEDURE recreatePaymentInstruction(
440 x_pmtInstrRec IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL
441 %ROWTYPE,
442 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
443 docErrorTabType,
444 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
445 trxnErrTokenTabType,
446 x_return_status IN OUT NOCOPY NUMBER);
447
448
449 /*--------------------------------------------------------------------
450 | NAME:
451 | insertPmtIntoInstruction
452 |
453 | PURPOSE:
454 |
455 |
456 | PARAMETERS:
457 | IN
458 |
459 |
460 | OUT
461 |
462 |
463 | RETURNS:
464 |
465 | NOTES:
466 |
467 *---------------------------------------------------------------------*/
468 PROCEDURE insertPmtIntoInstruction(
469 x_pmtInstrRec IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL
470 %ROWTYPE,
471 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
472 p_newPmtInstrFlag IN BOOLEAN,
473 x_currentPmtInstrId IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL.
474 payment_instruction_id%TYPE,
475 x_pmtsInPmtInstrTab IN OUT NOCOPY pmtsInPmtInstrTabType,
476 x_pmtsInPmtInstrRec IN OUT NOCOPY pmtsInpmtInstrRecType,
477 x_pmtsInPmtInstrCount IN OUT NOCOPY NUMBER,
478 x_instrAmount IN OUT NOCOPY NUMBER
479 );
480
481 /*--------------------------------------------------------------------
482 | NAME:
483 | insertPaymentInstructions
484 |
485 | PURPOSE:
486 |
487 |
488 |
489 | PARAMETERS:
490 | IN
491 |
492 |
493 | OUT
494 |
495 |
496 | RETURNS:
497 |
498 | NOTES:
499 |
500 *---------------------------------------------------------------------*/
501 PROCEDURE insertPaymentInstructions(
502 p_payInstrTab IN pmtInstrTabType
503 );
504
505 /*--------------------------------------------------------------------
506 | NAME:
507 | updatePaymentInstructions
508 |
509 | PURPOSE:
510 | Performs an update of all created instructions from PLSQL
511 | table into IBY_PAY_INSTRUCTIONS_ALL table.
512 |
513 | The created instructions have already been inserted into
514 | IBY_PAY_INSTRUCTIONS_ALL after grouping. So we only need to
515 | update certain fields of the instruction that have been
516 | changed after the grouping was performed.
517 |
518 | PARAMETERS:
519 | IN
520 |
521 |
522 | OUT
523 |
524 |
525 | RETURNS:
526 |
527 | NOTES:
528 |
529 *---------------------------------------------------------------------*/
530 PROCEDURE updatePaymentInstructions(
531 p_payInstrTab IN pmtInstrTabType
532 );
533
534 /*--------------------------------------------------------------------
535 | NAME:
536 | getNextPaymentInstructionID
537 |
538 | PURPOSE:
539 |
540 |
541 |
542 | PARAMETERS:
543 | IN
544 |
545 |
546 | OUT
547 |
548 |
549 | RETURNS:
550 |
551 | NOTES:
552 |
553 *---------------------------------------------------------------------*/
554 PROCEDURE getNextPaymentInstructionID(
555 x_pmtInstrID IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL.
556 payment_instruction_id%TYPE
557 );
558
559 /*--------------------------------------------------------------------
560 | NAME:
561 | updatePmtsWithInstructionID
562 |
563 | PURPOSE:
564 |
565 |
566 |
567 | PARAMETERS:
568 | IN
569 |
570 |
571 | OUT
572 |
573 |
574 | RETURNS:
575 |
576 | NOTES:
577 |
578 *---------------------------------------------------------------------*/
579 PROCEDURE updatePmtsWithInstructionID(
580 p_pmtsInPayInstTab IN pmtsInPmtInstrTabType
581 );
582
583 /*--------------------------------------------------------------------
584 | NAME:
585 | performDBUpdates
586 |
587 | PURPOSE:
588 |
589 |
590 |
591 |
592 |
593 | PARAMETERS:
594 | IN
595 |
596 |
597 | OUT
598 |
599 |
600 | RETURNS:
601 |
602 | NOTES:
603 |
604 *---------------------------------------------------------------------*/
605 PROCEDURE performDBUpdates(
606 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
607 x_pmtsInPmtInstrTab IN OUT NOCOPY pmtsInPmtInstrTabType,
608 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
609 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
610 trxnErrTokenTabType,
611 p_profileMap IN IBY_BUILD_UTILS_PKG.profileIdToCodeMapTabType,
612 x_return_status IN OUT NOCOPY VARCHAR2
613 );
614
615 /*--------------------------------------------------------------------
616 | NAME:
617 | updatePmtsWithSeqNumPmtRef
618 |
619 | PURPOSE:
620 |
621 | PARAMETERS:
622 | IN
623 |
624 |
625 | OUT
626 |
627 |
628 | RETURNS:
629 |
630 | NOTES:
631 |
632 *---------------------------------------------------------------------*/
633 PROCEDURE updatePmtsWithSeqNumPmtRef(
634 p_sortedPmtsTab IN sortedPmtTabType
635 );
636
637 /*--------------------------------------------------------------------
638 | NAME:
639 | getFxAmount()
640 |
641 | PURPOSE:
642 | Calls GL API to get converted amount in foreign currency.
643 |
644 |
645 | PARAMETERS:
646 | IN
647 |
648 |
649 | OUT
650 |
651 |
652 | RETURNS:
653 |
654 | NOTES:
655 |
656 *---------------------------------------------------------------------*/
657 FUNCTION getFxAmount(
658 p_source_currency IN VARCHAR2,
659 p_target_currency IN VARCHAR2,
660 p_exch_rate_date IN DATE,
661 p_exch_rate_type IN VARCHAR2,
662 p_source_amount IN NUMBER
663 )
664 RETURN NUMBER;
665
666 /*
667 * This pragma is needed because the GL API enforces it.
668 */
669 PRAGMA RESTRICT_REFERENCES(getFxAmount, WNDS, WNPS, RNPS);
670
671 /*--------------------------------------------------------------------
672 | NAME:
673 | performInstructionValidations
674 |
675 | PURPOSE:
676 |
677 |
678 |
679 | PARAMETERS:
680 | IN
681 |
682 |
683 | OUT
684 |
685 |
686 | RETURNS:
687 |
688 | NOTES:
689 |
690 *---------------------------------------------------------------------*/
691 PROCEDURE performInstructionValidations(
692 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
693 x_pmtsInPmtInstrTab IN OUT NOCOPY pmtsInPmtInstrTabType,
694 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
695 docErrorTabType,
696 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
697 trxnErrTokenTabType
698 );
699
700 /*--------------------------------------------------------------------
701 | NAME:
702 | flagSeparateRemitAdvcPayments
703 |
704 | PURPOSE:
705 |
706 |
707 |
708 | PARAMETERS:
709 | IN
710 |
711 |
712 | OUT
713 |
714 |
715 | RETURNS:
716 |
717 | NOTES:
718 |
719 *---------------------------------------------------------------------*/
720 PROCEDURE flagSeparateRemitAdvcPayments(
721 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
722 x_pmtsInPmtInstrTab IN OUT NOCOPY pmtsInPmtInstrTabType,
723 p_profileMap IN IBY_BUILD_UTILS_PKG.profileIdToCodeMapTabType
724 );
725
726 /*--------------------------------------------------------------------
727 | NAME:
728 | setRemitFlagForPmtInstruction
729 |
730 | PURPOSE:
731 |
732 |
733 |
734 | PARAMETERS:
735 | IN
736 |
737 |
738 | OUT
739 |
740 |
741 | RETURNS:
742 |
743 | NOTES:
744 |
745 *---------------------------------------------------------------------*/
746 PROCEDURE setRemitFlagForPmtInstruction(
747 p_pmtInstrId IN IBY_PAY_INSTRUCTIONS_ALL.
748 payment_instruction_id%TYPE,
749 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType
750 );
751
752 /*--------------------------------------------------------------------
753 | NAME:
754 | applyPayInstrValidationSets
755 |
756 | PURPOSE:
757 |
758 |
759 |
760 | PARAMETERS:
761 | IN
762 |
763 | OUT
764 |
765 |
766 | RETURNS:
767 |
768 | NOTES:
769 |
770 *---------------------------------------------------------------------*/
771 PROCEDURE applyPayInstrValidationSets(
772 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
773 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
774 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType
775 );
776
777 /*--------------------------------------------------------------------
778 | NAME:
779 | reApplyPayInstrValidationSets
780 |
781 | PURPOSE:
782 |
783 |
784 |
785 | PARAMETERS:
786 | IN
787 |
788 | OUT
789 |
790 |
791 | RETURNS:
792 |
793 | NOTES:
794 |
795 *---------------------------------------------------------------------*/
796 PROCEDURE reApplyPayInstrValidationSets(
797 x_pmtInstrRec IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE,
798 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
799 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType
800 );
801
802 /*--------------------------------------------------------------------
803 | NAME:
804 | executeValidationsForInstr
805 |
806 | PURPOSE:
807 |
808 |
809 |
810 | PARAMETERS:
811 | IN
812 |
813 | OUT
814 |
815 |
816 | RETURNS:
817 |
818 | NOTES:
819 |
820 *---------------------------------------------------------------------*/
821 PROCEDURE executeValidationsForInstr(
822 x_pmtInstrRec IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE,
823 p_valSetsTab IN instructionValSetsTab,
824 p_isReval IN BOOLEAN,
825 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
826 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType
827 );
828
829 /*--------------------------------------------------------------------
830 | NAME:
831 | raiseBizEvents
832 |
833 | PURPOSE:
834 |
835 |
836 |
837 | PARAMETERS:
838 | IN
839 |
840 | OUT
841 |
842 |
843 | RETURNS:
844 |
845 | NOTES:
846 |
847 *---------------------------------------------------------------------*/
848 PROCEDURE raiseBizEvents(
849 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType
850 );
851
852 /*--------------------------------------------------------------------
853 | NAME:
854 | getXMLClob
855 |
856 | PURPOSE:
857 |
858 |
859 |
860 | PARAMETERS:
861 | IN
862 |
863 | OUT
864 |
865 |
866 | RETURNS:
867 |
868 | NOTES:
869 |
870 *---------------------------------------------------------------------*/
871 FUNCTION getXMLClob(
872 p_pay_instruction_id IN VARCHAR2,
873 p_instruction_status IN VARCHAR2
874 )
875 RETURN VARCHAR2;
876
877 /*--------------------------------------------------------------------
878 | NAME:
879 | populateDocumentCount
880 |
881 | PURPOSE:
882 |
883 |
884 |
885 | PARAMETERS:
886 | IN
887 |
888 | OUT
889 |
890 |
891 | RETURNS:
892 |
893 | NOTES:
894 |
895 *---------------------------------------------------------------------*/
896 PROCEDURE populateDocumentCount(
897 x_pmtsInPmtInstrTab IN OUT NOCOPY pmtsInPmtInstrTabType
898 );
899
900 /*--------------------------------------------------------------------
901 | NAME:
902 | performSortedPaymentNumbering
903 |
904 | PURPOSE:
905 |
906 |
907 |
908 | PARAMETERS:
909 | IN
910 |
911 |
912 | OUT
913 |
914 |
915 | RETURNS:
916 |
917 | NOTES:
918 |
919 *---------------------------------------------------------------------*/
920 PROCEDURE performSortedPaymentNumbering(
921 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
922 x_sortedPmtTab IN OUT NOCOPY sortedPmtTabType,
923 p_profileMap IN IBY_BUILD_UTILS_PKG.profileIdToCodeMapTabType,
924 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
925 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType
926 );
927
928 /*--------------------------------------------------------------------
929 | NAME:
930 | performDocSequenceNumbering
931 |
932 | PURPOSE:
933 |
934 |
935 |
936 | PARAMETERS:
937 | IN
938 |
939 |
940 | OUT
941 |
942 |
943 | RETURNS:
944 |
945 | NOTES:
946 |
947 *---------------------------------------------------------------------*/
948 PROCEDURE performDocSequenceNumbering(
949 x_sortedPmtTab IN OUT NOCOPY sortedPmtTabType,
950 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
951 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType
952 );
953
954 /*--------------------------------------------------------------------
955 | NAME:
956 | getLedgerIdFromLEId
957 |
958 | PURPOSE:
959 | Returns the id of the primary ledger linked with the given LE.
960 |
961 |
962 | PARAMETERS:
963 | IN
964 | p_le_id - id of the a particular legal entity.
965 |
966 | OUT
967 |
968 |
969 | RETURNS:
970 | ledger id - A positive value, if successful
971 | -1, if unsuccessful
972 | NOTES:
973 |
974 *---------------------------------------------------------------------*/
975 PROCEDURE getLedgerIdFromLEId(
976 x_sortedPmtTab IN OUT NOCOPY sortedPmtTabType
977 );
978
979 /*--------------------------------------------------------------------
980 | NAME:
981 | assignSequenceNumbers
982 |
983 | PURPOSE:
984 |
985 |
986 | PARAMETERS:
987 | IN
988 |
989 | OUT
990 |
991 |
992 | RETURNS:
993 |
994 |
995 | NOTES:
996 |
997 *---------------------------------------------------------------------*/
998 PROCEDURE assignSequenceNumbers(
999 x_sortedPmtTab IN OUT NOCOPY sortedPmtTabType,
1000 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1001 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType
1002 );
1003
1004 /*--------------------------------------------------------------------
1005 | NAME:
1006 | retrieveSortingOptions
1010 |
1007 |
1008 | PURPOSE:
1009 |
1011 |
1012 | PARAMETERS:
1013 | IN
1014 |
1015 |
1016 | OUT
1017 |
1018 |
1019 | RETURNS:
1020 |
1021 | NOTES:
1022 |
1023 *---------------------------------------------------------------------*/
1024 PROCEDURE retrieveSortingOptions(
1025 p_pmtInstrTab IN pmtInstrTabType,
1026 x_sortOptionsTab IN OUT NOCOPY sortOptionsTabType
1027 );
1028
1029 /*--------------------------------------------------------------------
1030 | NAME:
1031 | getSortOptionsForProfile
1032 |
1033 | PURPOSE:
1034 |
1035 |
1036 |
1037 | PARAMETERS:
1038 | IN
1039 |
1040 |
1041 | OUT
1042 |
1043 |
1044 | RETURNS:
1045 |
1046 | NOTES:
1047 |
1048 *---------------------------------------------------------------------*/
1049 FUNCTION getSortOptionsForProfile(
1050 p_sortOptionsTab IN sortOptionsTabType,
1051 p_profileMap IN IBY_BUILD_UTILS_PKG.profileIdToCodeMapTabType,
1052 p_profile_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_profile_id%TYPE
1053 )
1054 RETURN sortOptionsRecType;
1055
1056 /*--------------------------------------------------------------------
1057 | NAME:
1058 | buildOrderByFromSortOptions
1059 |
1060 | PURPOSE:
1061 |
1062 |
1063 |
1064 | PARAMETERS:
1065 | IN
1066 |
1067 |
1068 | OUT
1069 |
1070 |
1071 | RETURNS:
1072 |
1073 | NOTES:
1074 |
1075 *---------------------------------------------------------------------*/
1076 FUNCTION buildOrderByFromSortOptions(
1077 x_sortOptionsRec IN OUT NOCOPY sortOptionsRecType
1078 )
1079 RETURN VARCHAR2;
1080
1081
1082 /*--------------------------------------------------------------------
1083 | NAME:
1084 | renameSortOption
1085 |
1086 | PURPOSE:
1087 |
1088 |
1089 |
1090 | PARAMETERS:
1091 | IN
1092 |
1093 |
1094 | OUT
1095 |
1096 |
1097 | RETURNS:
1098 |
1099 | NOTES:
1100 |
1101 *---------------------------------------------------------------------*/
1102 PROCEDURE renameSortOption(
1103 x_sortOption IN OUT NOCOPY VARCHAR2
1104 );
1105
1106 /*--------------------------------------------------------------------
1107 | NAME:
1108 | providePaymentReferences
1109 |
1110 | PURPOSE:
1111 |
1112 |
1113 |
1114 | PARAMETERS:
1115 | IN
1116 |
1117 |
1118 | OUT
1119 |
1120 |
1121 | RETURNS:
1122 |
1123 | NOTES:
1124 |
1125 *---------------------------------------------------------------------*/
1126 PROCEDURE providePaymentReferences(
1127 x_sortPmtsTab IN OUT NOCOPY sortedPmtTabType
1128 );
1129
1130 /*--------------------------------------------------------------------
1131 | NAME:
1132 | dummyGLAPI
1133 |
1134 | PURPOSE:
1135 |
1136 |
1137 |
1138 | PARAMETERS:
1139 | IN
1140 |
1141 | OUT
1142 |
1143 |
1144 | RETURNS:
1145 |
1146 | NOTES:
1147 |
1148 *---------------------------------------------------------------------*/
1149 PROCEDURE dummyGLAPI(
1150 p_exch_date IN DATE,
1151 p_source_amount IN NUMBER,
1152 p_source_curr IN VARCHAR2,
1153 p_decl_curr IN VARCHAR2,
1154 p_decl_fx_rate_type IN VARCHAR2,
1155 x_decl_amount OUT NOCOPY NUMBER);
1156
1157 /*--------------------------------------------------------------------
1158 | NAME:
1159 | dummy_ruleFunction
1160 |
1161 | PURPOSE:
1162 | Dummy method; to be used for testing purposes.
1163 |
1164 |
1165 | PARAMETERS:
1166 | IN
1167 |
1168 | OUT
1169 |
1170 |
1171 | RETURNS:
1172 |
1173 | NOTES:
1174 |
1175 *---------------------------------------------------------------------*/
1176 FUNCTION dummy_ruleFunction(
1177 p_subscription IN RAW,
1178 p_event IN OUT NOCOPY WF_EVENT_T
1179 )
1180 RETURN VARCHAR2;
1181
1182 /*--------------------------------------------------------------------
1183 | NAME:
1184 | deriveDistinctAccessTypsForIns
1185 |
1186 | PURPOSE:
1187 |
1188 |
1189 | PARAMETERS:
1190 | IN
1191 |
1192 |
1193 | OUT
1194 |
1195 |
1196 | RETURNS:
1197 |
1198 | NOTES:
1199 |
1200 *---------------------------------------------------------------------*/
1201 PROCEDURE deriveDistinctAccessTypsForIns(
1202 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id
1203 %TYPE,
1204 p_pmt_function IN IBY_PAYMENTS_ALL.payment_function%TYPE,
1205 p_org_id IN IBY_PAYMENTS_ALL.org_id%TYPE,
1206 p_org_type IN IBY_PAYMENTS_ALL.org_type%TYPE,
1207 x_pmtFxAccessTypesTab IN OUT NOCOPY distinctPmtFxAccessTab,
1208 x_orgAccessTypesTab IN OUT NOCOPY distinctOrgAccessTab
1209 );
1210
1211 /*--------------------------------------------------------------------
1212 | NAME:
1213 | insertDistinctAccessTypsForIns
1214 |
1215 | PURPOSE:
1216 |
1217 |
1218 | PARAMETERS:
1219 | IN
1220 |
1221 |
1222 | OUT
1223 |
1224 |
1225 | RETURNS:
1226 |
1227 | NOTES:
1228 |
1229 *---------------------------------------------------------------------*/
1230 PROCEDURE insertDistinctAccessTypsForIns(
1231 p_pmtFxAccessTypesTab IN distinctPmtFxAccessTab,
1232 p_orgAccessTypesTab IN distinctOrgAccessTab
1233 );
1234
1235 /*--------------------------------------------------------------------
1236 | NAME:
1237 | markFailedInstructions
1238 |
1239 | PURPOSE:
1240 |
1241 |
1242 |
1243 | PARAMETERS:
1244 | IN
1245 |
1246 |
1247 | OUT
1248 |
1249 |
1250 | RETURNS:
1251 |
1252 | NOTES:
1253 |
1254 *---------------------------------------------------------------------*/
1255 PROCEDURE markFailedInstructions(
1256 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
1257 p_sortedPmtTab IN sortedPmtTabType
1258 );
1259
1260 /*--------------------------------------------------------------------
1261 | NAME:
1262 | initializeInstrs
1263 |
1264 | PURPOSE:
1265 |
1266 |
1267 | PARAMETERS:
1268 | IN
1269 |
1270 |
1271 | OUT
1272 |
1273 |
1274 | RETURNS:
1275 |
1276 | NOTES:
1277 |
1278 *---------------------------------------------------------------------*/
1279 PROCEDURE initializeInstrs(
1280 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
1281 p_single_pmt_flow_flag IN VARCHAR2
1282 );
1283
1284 /*--------------------------------------------------------------------
1285 | NAME:
1286 | checkIfDocSeqCompleted
1287 |
1288 | PURPOSE:
1289 |
1290 |
1291 | PARAMETERS:
1292 | IN
1293 |
1294 | OUT
1295 |
1296 |
1297 | RETURNS:
1298 |
1299 | NOTES:
1300 |
1301 *---------------------------------------------------------------------*/
1302 FUNCTION checkIfDocSeqCompleted(
1303 p_instr_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE
1304 )
1305 RETURN BOOLEAN;
1306
1307 /*--------------------------------------------------------------------
1308 | NAME:
1309 | checkIfPmtRefCompleted
1310 |
1311 | PURPOSE:
1312 |
1313 |
1314 | PARAMETERS:
1315 | IN
1316 |
1317 | OUT
1318 |
1319 |
1320 | RETURNS:
1321 |
1322 | NOTES:
1323 |
1324 *---------------------------------------------------------------------*/
1325 FUNCTION checkIfPmtRefCompleted(
1326 p_instr_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE
1327 )
1328 RETURN BOOLEAN;
1329
1330 /*--------------------------------------------------------------------
1331 | NAME:
1332 | getSortedPmtsForInstr
1333 |
1334 | PURPOSE:
1335 |
1336 |
1337 |
1338 | PARAMETERS:
1339 | IN
1340 |
1341 |
1342 | OUT
1343 |
1344 |
1345 | RETURNS:
1346 |
1347 | NOTES:
1348 |
1349 *---------------------------------------------------------------------*/
1350 PROCEDURE getSortedPmtsForInstr(
1351 p_instr_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
1352 p_profile_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_profile_id%TYPE,
1353 p_sortOptionsTab IN sortOptionsTabType,
1354 p_profileMap IN IBY_BUILD_UTILS_PKG.profileIdToCodeMapTabType,
1355 x_sortedPmtTab IN OUT NOCOPY sortedPmtTabType
1356 );
1357
1358 /*--------------------------------------------------------------------
1359 | NAME:
1360 | get_instruction_profile
1361 |
1362 |
1363 | PURPOSE:
1364 |
1365 |
1366 | PARAMETERS:
1367 | IN
1368 |
1369 |
1370 | OUT
1371 |
1372 |
1373 | RETURNS:
1374 |
1375 | NOTES:
1376 |
1377 *---------------------------------------------------------------------*/
1378 FUNCTION get_instruction_profile (
1379 l_pmt_instr_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE)
1380 RETURN NUMBER;
1381
1382 /*--------------------------------------------------------------------
1383 | NAME:
1384 | retrieveSortOptionForProfile
1385 |
1386 | PURPOSE:
1387 |
1388 |
1389 |
1390 | PARAMETERS:
1391 | IN
1392 |
1393 |
1394 | OUT
1395 |
1396 |
1397 | RETURNS:
1398 |
1399 | NOTES:
1400 |
1401 *---------------------------------------------------------------------*/
1402 PROCEDURE retrieveSortOptionForProfile(
1403 p_profile_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_profile_id%TYPE,
1404 x_sortOptionsTab IN OUT NOCOPY sortOptionsTabType
1405 );
1406
1407 /*--------------------------------------------------------------------
1408 | NAME:
1409 | updatePmtsWithSeqNum
1410 |
1411 | PURPOSE:
1412 |
1413 | PARAMETERS:
1414 | IN
1415 |
1416 |
1417 | OUT
1418 |
1419 |
1420 | RETURNS:
1421 |
1422 | NOTES:
1423 |
1424 *---------------------------------------------------------------------*/
1425 PROCEDURE updatePmtsWithSeqNum(
1426 p_sortedPmtsTab IN sortedPmtTabType
1427 );
1428
1429 /*--------------------------------------------------------------------
1430 | NAME:
1431 | updatePmtsWithPmtRef
1432 |
1433 | PURPOSE:
1434 |
1435 | PARAMETERS:
1436 | IN
1437 |
1438 |
1439 | OUT
1440 |
1441 |
1442 | RETURNS:
1443 |
1444 | NOTES:
1445 |
1446 *---------------------------------------------------------------------*/
1447 PROCEDURE updatePmtsWithPmtRef(
1448 p_sortedPmtsTab IN sortedPmtTabType
1449 );
1450
1451
1452 /*--------------------------------------------------------------------
1453 | NAME:
1454 | payment_completion_validation
1455 |
1456 | PURPOSE:
1457 |
1458 | PARAMETERS:
1459 | IN
1460 |
1461 |
1462 | OUT
1463 |
1464 |
1465 | RETURNS:
1466 |
1467 | NOTES:
1468 |
1469 *---------------------------------------------------------------------*/
1470 PROCEDURE payment_completion_validation(
1471 x_call_pmt_comp_validation IN OUT NOCOPY VARCHAR2
1472 );
1473
1474 END IBY_PAYINSTR_PUB;