1 PACKAGE IBY_PAYGROUP_PUB AS
2 /*$Header: ibypaygs.pls 120.38.12010000.3 2008/12/02 20:57:26 pschalla ship $*/
3
4 --
5 -- This record corresponds to a row in the IBY_PAYMENTS_ALL table.
6 -- A PLSQL table of these records are created after applying
7 -- grouping rules and this table is used in bulk inserting
8 -- these payments into the IBY_PAYMENTS_ALL table
9 --
10 -- The record docsInPaymentRecType holds the documents
11 -- corresponding to this payment.
12 --
13 --
14 -- This PLSQL table corresponds to IBY_PAYMENTS_ALL and it
15 -- will be used in bulk updating the IBY_PAYMENTS_ALL table.
16 --
17 --TYPE paymentTabType IS TABLE OF paymentRecType
18 -- INDEX BY BINARY_INTEGER;
19 TYPE paymentTabType IS TABLE OF IBY_PAYMENTS_ALL%ROWTYPE
20 INDEX BY BINARY_INTEGER;
21
22 --
23 -- This PLSQL record of tables corresponds to IBY_PAYMENTS_ALL and it
24 -- will be used in bulk updating the IBY_PAYMENTS_ALL table.
25 --
26 TYPE t_payment_id IS TABLE OF
27 IBY_PAYMENTS_ALL.payment_id%TYPE
28 INDEX BY BINARY_INTEGER;
29 TYPE t_payment_method_code IS TABLE OF
30 IBY_PAYMENTS_ALL.payment_method_code%TYPE
31 INDEX BY BINARY_INTEGER;
32 TYPE t_payment_service_request_id IS TABLE OF
33 IBY_PAYMENTS_ALL.payment_service_request_id%TYPE
34 INDEX BY BINARY_INTEGER;
35 TYPE t_process_type IS TABLE OF
36 IBY_PAYMENTS_ALL.process_type%TYPE
37 INDEX BY BINARY_INTEGER;
38 TYPE t_payment_status IS TABLE OF
39 IBY_PAYMENTS_ALL.payment_status%TYPE
40 INDEX BY BINARY_INTEGER;
41 TYPE t_payments_complete_flag IS TABLE OF
42 IBY_PAYMENTS_ALL.payments_complete_flag%TYPE
43 INDEX BY BINARY_INTEGER;
44 TYPE t_payment_function IS TABLE OF
45 IBY_PAYMENTS_ALL.payment_function%TYPE
46 INDEX BY BINARY_INTEGER;
47 TYPE t_payment_amount IS TABLE OF
48 IBY_PAYMENTS_ALL.payment_amount%TYPE
49 INDEX BY BINARY_INTEGER;
50 TYPE t_payment_currency_code IS TABLE OF
51 IBY_PAYMENTS_ALL.payment_currency_code%TYPE
52 INDEX BY BINARY_INTEGER;
53 TYPE t_bill_payable_flag IS TABLE OF
54 IBY_PAYMENTS_ALL.bill_payable_flag%TYPE
55 INDEX BY BINARY_INTEGER;
56 TYPE t_exclusive_payment_flag IS TABLE OF
57 IBY_PAYMENTS_ALL.exclusive_payment_flag%TYPE
58 INDEX BY BINARY_INTEGER;
59 TYPE t_sep_remit_advice_req_flag IS TABLE OF
60 IBY_PAYMENTS_ALL.separate_remit_advice_req_flag%TYPE
61 INDEX BY BINARY_INTEGER;
62 TYPE t_internal_bank_account_id IS TABLE OF
63 IBY_PAYMENTS_ALL.internal_bank_account_id%TYPE
64 INDEX BY BINARY_INTEGER;
65 TYPE t_org_id IS TABLE OF
66 IBY_PAYMENTS_ALL.org_id%TYPE
67 INDEX BY BINARY_INTEGER;
68 TYPE t_org_type IS TABLE OF
69 IBY_PAYMENTS_ALL.org_type%TYPE
70 INDEX BY BINARY_INTEGER;
71 TYPE t_legal_entity_id IS TABLE OF
72 IBY_PAYMENTS_ALL.legal_entity_id%TYPE
73 INDEX BY BINARY_INTEGER;
74 TYPE t_declare_payment_flag IS TABLE OF
75 IBY_PAYMENTS_ALL.declare_payment_flag%TYPE
76 INDEX BY BINARY_INTEGER;
77 TYPE t_delivery_channel_code IS TABLE OF
78 IBY_PAYMENTS_ALL.delivery_channel_code%TYPE
79 INDEX BY BINARY_INTEGER;
80 TYPE t_ext_payee_id IS TABLE OF
81 IBY_PAYMENTS_ALL.ext_payee_id%TYPE
82 INDEX BY BINARY_INTEGER;
83 TYPE t_payment_instruction_id IS TABLE OF
84 IBY_PAYMENTS_ALL.payment_instruction_id%TYPE
85 INDEX BY BINARY_INTEGER;
86 TYPE t_payment_profile_id IS TABLE OF
87 IBY_PAYMENTS_ALL.payment_profile_id%TYPE
88 INDEX BY BINARY_INTEGER;
89 TYPE t_pregrouped_payment_flag IS TABLE OF
90 IBY_PAYMENTS_ALL.pregrouped_payment_flag%TYPE
91 INDEX BY BINARY_INTEGER;
92 TYPE t_stop_confirmed_flag IS TABLE OF
93 IBY_PAYMENTS_ALL.stop_confirmed_flag%TYPE
94 INDEX BY BINARY_INTEGER;
95 TYPE t_stop_released_flag IS TABLE OF
96 IBY_PAYMENTS_ALL.stop_released_flag%TYPE
97 INDEX BY BINARY_INTEGER;
98 TYPE t_stop_request_placed_flag IS TABLE OF
99 IBY_PAYMENTS_ALL.stop_request_placed_flag%TYPE
100 INDEX BY BINARY_INTEGER;
101 TYPE t_created_by IS TABLE OF
102 IBY_PAYMENTS_ALL.created_by%TYPE
103 INDEX BY BINARY_INTEGER;
104 TYPE t_creation_date IS TABLE OF
105 IBY_PAYMENTS_ALL.creation_date%TYPE
106 INDEX BY BINARY_INTEGER;
107 TYPE t_last_updated_by IS TABLE OF
108 IBY_PAYMENTS_ALL.last_updated_by%TYPE
109 INDEX BY BINARY_INTEGER;
110 TYPE t_last_update_login IS TABLE OF
111 IBY_PAYMENTS_ALL.last_update_login%TYPE
112 INDEX BY BINARY_INTEGER;
113 TYPE t_last_update_date IS TABLE OF
114 IBY_PAYMENTS_ALL.last_update_date%TYPE
115 INDEX BY BINARY_INTEGER;
116 TYPE t_object_version_number IS TABLE OF
117 IBY_PAYMENTS_ALL.object_version_number%TYPE
118 INDEX BY BINARY_INTEGER;
119 TYPE t_payee_party_id IS TABLE OF
120 IBY_PAYMENTS_ALL.payee_party_id%TYPE
121 INDEX BY BINARY_INTEGER;
122 TYPE t_party_site_id IS TABLE OF
123 IBY_PAYMENTS_ALL.party_site_id%TYPE
124 INDEX BY BINARY_INTEGER;
125 TYPE t_supplier_site_id IS TABLE OF
126 IBY_PAYMENTS_ALL.supplier_site_id%TYPE
127 INDEX BY BINARY_INTEGER;
128 TYPE t_payment_reason_code IS TABLE OF
129 IBY_PAYMENTS_ALL.payment_reason_code%TYPE
130 INDEX BY BINARY_INTEGER;
131 TYPE t_payment_reason_comments IS TABLE OF
132 IBY_PAYMENTS_ALL.payment_reason_comments%TYPE
133 INDEX BY BINARY_INTEGER;
134 TYPE t_payment_date IS TABLE OF
135 IBY_PAYMENTS_ALL.payment_date%TYPE
136 INDEX BY BINARY_INTEGER;
137 TYPE t_anticipated_value_date IS TABLE OF
138 IBY_PAYMENTS_ALL.anticipated_value_date%TYPE
139 INDEX BY BINARY_INTEGER;
140 TYPE t_declaration_amount IS TABLE OF
141 IBY_PAYMENTS_ALL.declaration_amount%TYPE
142 INDEX BY BINARY_INTEGER;
143 TYPE t_declaration_currency_code IS TABLE OF
144 IBY_PAYMENTS_ALL.declaration_currency_code%TYPE
145 INDEX BY BINARY_INTEGER;
146 TYPE t_discount_amount_taken IS TABLE OF
147 IBY_PAYMENTS_ALL.discount_amount_taken%TYPE
148 INDEX BY BINARY_INTEGER;
149 TYPE t_payment_details IS TABLE OF
150 IBY_PAYMENTS_ALL.payment_details%TYPE
151 INDEX BY BINARY_INTEGER;
152 TYPE t_bank_charge_bearer IS TABLE OF
153 IBY_PAYMENTS_ALL.bank_charge_bearer%TYPE
154 INDEX BY BINARY_INTEGER;
155 TYPE t_bank_charge_amount IS TABLE OF
156 IBY_PAYMENTS_ALL.bank_charge_amount%TYPE
157 INDEX BY BINARY_INTEGER;
158 TYPE t_settlement_priority IS TABLE OF
159 IBY_PAYMENTS_ALL.settlement_priority%TYPE
160 INDEX BY BINARY_INTEGER;
161 TYPE t_remittance_message1 IS TABLE OF
162 IBY_PAYMENTS_ALL.remittance_message1%TYPE
163 INDEX BY BINARY_INTEGER;
164 TYPE t_remittance_message2 IS TABLE OF
165 IBY_PAYMENTS_ALL.remittance_message2%TYPE
166 INDEX BY BINARY_INTEGER;
167 TYPE t_remittance_message3 IS TABLE OF
168 IBY_PAYMENTS_ALL.remittance_message3%TYPE
169 INDEX BY BINARY_INTEGER;
170 TYPE t_payment_reference_number IS TABLE OF
171 IBY_PAYMENTS_ALL.payment_reference_number%TYPE
172 INDEX BY BINARY_INTEGER;
173 TYPE t_paper_document_number IS TABLE OF
174 IBY_PAYMENTS_ALL.paper_document_number%TYPE
175 INDEX BY BINARY_INTEGER;
176 TYPE t_bank_assigned_ref_code IS TABLE OF
177 IBY_PAYMENTS_ALL.bank_assigned_ref_code%TYPE
178 INDEX BY BINARY_INTEGER;
179 TYPE t_external_bank_account_id IS TABLE OF
180 IBY_PAYMENTS_ALL.external_bank_account_id%TYPE
181 INDEX BY BINARY_INTEGER;
182 TYPE t_unique_remittance_identifier IS TABLE OF
183 IBY_PAYMENTS_ALL.unique_remittance_identifier%TYPE
184 INDEX BY BINARY_INTEGER;
185 TYPE t_uri_check_digit IS TABLE OF
186 IBY_PAYMENTS_ALL.uri_check_digit%TYPE
187 INDEX BY BINARY_INTEGER;
188 TYPE t_bank_instruction1_code IS TABLE OF
189 IBY_PAYMENTS_ALL.bank_instruction1_code%TYPE
190 INDEX BY BINARY_INTEGER;
191 TYPE t_bank_instruction2_code IS TABLE OF
192 IBY_PAYMENTS_ALL.bank_instruction2_code%TYPE
193 INDEX BY BINARY_INTEGER;
194 TYPE t_bank_instruction_details IS TABLE OF
195 IBY_PAYMENTS_ALL.bank_instruction_details%TYPE
196 INDEX BY BINARY_INTEGER;
197 TYPE t_payment_text_message1 IS TABLE OF
198 IBY_PAYMENTS_ALL.payment_text_message1%TYPE
199 INDEX BY BINARY_INTEGER;
200 TYPE t_payment_text_message2 IS TABLE OF
201 IBY_PAYMENTS_ALL.payment_text_message2%TYPE
202 INDEX BY BINARY_INTEGER;
203 TYPE t_payment_text_message3 IS TABLE OF
204 IBY_PAYMENTS_ALL.payment_text_message3%TYPE
205 INDEX BY BINARY_INTEGER;
206 TYPE t_maturity_date IS TABLE OF
207 IBY_PAYMENTS_ALL.maturity_date%TYPE
208 INDEX BY BINARY_INTEGER;
209 TYPE t_payment_due_date IS TABLE OF
210 IBY_PAYMENTS_ALL.payment_due_date%TYPE
211 INDEX BY BINARY_INTEGER;
212 TYPE t_document_category_code IS TABLE OF
213 IBY_PAYMENTS_ALL.document_category_code%TYPE
214 INDEX BY BINARY_INTEGER;
215 TYPE t_document_sequence_id IS TABLE OF
216 IBY_PAYMENTS_ALL.document_sequence_id%TYPE
217 INDEX BY BINARY_INTEGER;
218 TYPE t_document_sequence_value IS TABLE OF
219 IBY_PAYMENTS_ALL.document_sequence_value%TYPE
220 INDEX BY BINARY_INTEGER;
221 TYPE t_beneficiary_party IS TABLE OF
222 IBY_PAYMENTS_ALL.beneficiary_party%TYPE
223 INDEX BY BINARY_INTEGER;
224 TYPE t_stop_confirmed_by IS TABLE OF
225 IBY_PAYMENTS_ALL.stop_confirmed_by%TYPE
226 INDEX BY BINARY_INTEGER;
227 TYPE t_stop_confirm_date IS TABLE OF
228 IBY_PAYMENTS_ALL.stop_confirm_date%TYPE
229 INDEX BY BINARY_INTEGER;
230 TYPE t_stop_confirm_reason IS TABLE OF
231 IBY_PAYMENTS_ALL.stop_confirm_reason%TYPE
232 INDEX BY BINARY_INTEGER;
233 TYPE t_stop_confirm_reference IS TABLE OF
234 IBY_PAYMENTS_ALL.stop_confirm_reference%TYPE
235 INDEX BY BINARY_INTEGER;
236 TYPE t_stop_released_by IS TABLE OF
237 IBY_PAYMENTS_ALL.stop_released_by%TYPE
238 INDEX BY BINARY_INTEGER;
239 TYPE t_stop_release_date IS TABLE OF
240 IBY_PAYMENTS_ALL.stop_release_date%TYPE
241 INDEX BY BINARY_INTEGER;
242 TYPE t_stop_release_reason IS TABLE OF
243 IBY_PAYMENTS_ALL.stop_release_reason%TYPE
244 INDEX BY BINARY_INTEGER;
245 TYPE t_stop_release_reference IS TABLE OF
246 IBY_PAYMENTS_ALL.stop_release_reference%TYPE
247 INDEX BY BINARY_INTEGER;
248 TYPE t_stop_request_date IS TABLE OF
249 IBY_PAYMENTS_ALL.stop_request_date%TYPE
250 INDEX BY BINARY_INTEGER;
251 TYPE t_stop_request_placed_by IS TABLE OF
252 IBY_PAYMENTS_ALL.stop_request_placed_by%TYPE
253 INDEX BY BINARY_INTEGER;
254 TYPE t_stop_request_reason IS TABLE OF
255 IBY_PAYMENTS_ALL.stop_request_reason%TYPE
256 INDEX BY BINARY_INTEGER;
257 TYPE t_stop_request_reference IS TABLE OF
258 IBY_PAYMENTS_ALL.stop_request_reference%TYPE
259 INDEX BY BINARY_INTEGER;
260 TYPE t_voided_by IS TABLE OF
261 IBY_PAYMENTS_ALL.voided_by%TYPE
262 INDEX BY BINARY_INTEGER;
263 TYPE t_void_date IS TABLE OF
264 IBY_PAYMENTS_ALL.void_date%TYPE
265 INDEX BY BINARY_INTEGER;
266 TYPE t_void_reason IS TABLE OF
267 IBY_PAYMENTS_ALL.void_reason%TYPE
268 INDEX BY BINARY_INTEGER;
269 TYPE t_remit_to_location_id IS TABLE OF
270 IBY_PAYMENTS_ALL.remit_to_location_id%TYPE
271 INDEX BY BINARY_INTEGER;
272 TYPE t_completed_pmts_group_id IS TABLE OF
273 IBY_PAYMENTS_ALL.completed_pmts_group_id%TYPE
274 INDEX BY BINARY_INTEGER;
275 TYPE t_attribute_category IS TABLE OF
276 IBY_PAYMENTS_ALL.attribute_category%TYPE
277 INDEX BY BINARY_INTEGER;
278 TYPE t_attribute1 IS TABLE OF
279 IBY_PAYMENTS_ALL.attribute1%TYPE
280 INDEX BY BINARY_INTEGER;
281 TYPE t_attribute2 IS TABLE OF
282 IBY_PAYMENTS_ALL.attribute2%TYPE
283 INDEX BY BINARY_INTEGER;
284 TYPE t_attribute3 IS TABLE OF
285 IBY_PAYMENTS_ALL.attribute3%TYPE
286 INDEX BY BINARY_INTEGER;
287 TYPE t_attribute4 IS TABLE OF
288 IBY_PAYMENTS_ALL.attribute4%TYPE
289 INDEX BY BINARY_INTEGER;
290 TYPE t_attribute5 IS TABLE OF
291 IBY_PAYMENTS_ALL.attribute5%TYPE
292 INDEX BY BINARY_INTEGER;
293 TYPE t_attribute6 IS TABLE OF
294 IBY_PAYMENTS_ALL.attribute6%TYPE
295 INDEX BY BINARY_INTEGER;
296 TYPE t_attribute7 IS TABLE OF
297 IBY_PAYMENTS_ALL.attribute7%TYPE
298 INDEX BY BINARY_INTEGER;
299 TYPE t_attribute8 IS TABLE OF
300 IBY_PAYMENTS_ALL.attribute8%TYPE
301 INDEX BY BINARY_INTEGER;
302 TYPE t_attribute9 IS TABLE OF
303 IBY_PAYMENTS_ALL.attribute9%TYPE
304 INDEX BY BINARY_INTEGER;
305 TYPE t_attribute10 IS TABLE OF
306 IBY_PAYMENTS_ALL.attribute10%TYPE
307 INDEX BY BINARY_INTEGER;
308 TYPE t_attribute11 IS TABLE OF
309 IBY_PAYMENTS_ALL.attribute11%TYPE
310 INDEX BY BINARY_INTEGER;
311 TYPE t_attribute12 IS TABLE OF
312 IBY_PAYMENTS_ALL.attribute12%TYPE
313 INDEX BY BINARY_INTEGER;
314 TYPE t_attribute13 IS TABLE OF
315 IBY_PAYMENTS_ALL.attribute13%TYPE
316 INDEX BY BINARY_INTEGER;
317 TYPE t_attribute14 IS TABLE OF
318 IBY_PAYMENTS_ALL.attribute14%TYPE
319 INDEX BY BINARY_INTEGER;
320 TYPE t_attribute15 IS TABLE OF
321 IBY_PAYMENTS_ALL.attribute15%TYPE
322 INDEX BY BINARY_INTEGER;
323 TYPE t_ext_branch_number IS TABLE OF
327 IBY_PAYMENTS_ALL.ext_bank_number%TYPE
324 IBY_PAYMENTS_ALL.ext_branch_number%TYPE
325 INDEX BY BINARY_INTEGER;
326 TYPE t_ext_bank_number IS TABLE OF
328 INDEX BY BINARY_INTEGER;
329 TYPE t_ext_bank_account_name IS TABLE OF
330 IBY_PAYMENTS_ALL.ext_bank_account_name%TYPE
331 INDEX BY BINARY_INTEGER;
332 TYPE t_ext_bank_account_number IS TABLE OF
333 IBY_PAYMENTS_ALL.ext_bank_account_number%TYPE
334 INDEX BY BINARY_INTEGER;
335 TYPE t_ext_bank_account_type IS TABLE OF
336 IBY_PAYMENTS_ALL.ext_bank_account_type%TYPE
337 INDEX BY BINARY_INTEGER;
338 TYPE t_ext_bank_account_iban_number IS TABLE OF
339 IBY_PAYMENTS_ALL.ext_bank_account_iban_number%TYPE
340 INDEX BY BINARY_INTEGER;
341 TYPE t_payee_name IS TABLE OF
342 IBY_PAYMENTS_ALL.payee_name%TYPE
343 INDEX BY BINARY_INTEGER;
344 TYPE t_payee_address1 IS TABLE OF
345 IBY_PAYMENTS_ALL.payee_address1%TYPE
346 INDEX BY BINARY_INTEGER;
347 TYPE t_payee_address2 IS TABLE OF
348 IBY_PAYMENTS_ALL.payee_address2%TYPE
349 INDEX BY BINARY_INTEGER;
350 TYPE t_payee_address3 IS TABLE OF
351 IBY_PAYMENTS_ALL.payee_address3%TYPE
352 INDEX BY BINARY_INTEGER;
353 TYPE t_payee_address4 IS TABLE OF
354 IBY_PAYMENTS_ALL.payee_address4%TYPE
355 INDEX BY BINARY_INTEGER;
356 TYPE t_payee_city IS TABLE OF
357 IBY_PAYMENTS_ALL.payee_city%TYPE
358 INDEX BY BINARY_INTEGER;
359 TYPE t_payee_postal_code IS TABLE OF
360 IBY_PAYMENTS_ALL.payee_postal_code%TYPE
361 INDEX BY BINARY_INTEGER;
362 TYPE t_payee_state IS TABLE OF
363 IBY_PAYMENTS_ALL.payee_state%TYPE
364 INDEX BY BINARY_INTEGER;
365 TYPE t_payee_province IS TABLE OF
366 IBY_PAYMENTS_ALL.payee_province%TYPE
367 INDEX BY BINARY_INTEGER;
368 TYPE t_payee_county IS TABLE OF
369 IBY_PAYMENTS_ALL.payee_county%TYPE
370 INDEX BY BINARY_INTEGER;
371 TYPE t_payee_country IS TABLE OF
372 IBY_PAYMENTS_ALL.payee_country%TYPE
373 INDEX BY BINARY_INTEGER;
374 TYPE t_remit_advice_delivery_method IS TABLE OF
375 IBY_PAYMENTS_ALL.remit_advice_delivery_method%TYPE
376 INDEX BY BINARY_INTEGER;
377 TYPE t_remit_advice_email IS TABLE OF
378 IBY_PAYMENTS_ALL.remit_advice_email%TYPE
379 INDEX BY BINARY_INTEGER;
380 TYPE t_remit_advice_fax IS TABLE OF
381 IBY_PAYMENTS_ALL.remit_advice_fax%TYPE
382 INDEX BY BINARY_INTEGER;
383
384 /*TPP-Start*/
385 TYPE t_ext_inv_payee_id IS TABLE OF
386 IBY_PAYMENTS_ALL.ext_inv_payee_id%TYPE
387 INDEX BY BINARY_INTEGER;
388 TYPE t_inv_payee_party_id IS TABLE OF
389 IBY_PAYMENTS_ALL.inv_payee_party_id%TYPE
390 INDEX BY BINARY_INTEGER;
391 TYPE t_inv_party_site_id IS TABLE OF
392 IBY_PAYMENTS_ALL.inv_party_site_id%TYPE
393 INDEX BY BINARY_INTEGER;
394 TYPE t_inv_supplier_site_id IS TABLE OF
395 IBY_PAYMENTS_ALL.inv_supplier_site_id%TYPE
396 INDEX BY BINARY_INTEGER;
397 TYPE t_inv_beneficiary_party IS TABLE OF
398 IBY_PAYMENTS_ALL.inv_beneficiary_party%TYPE
399 INDEX BY BINARY_INTEGER;
400 TYPE t_inv_payee_name IS TABLE OF
401 IBY_PAYMENTS_ALL.inv_payee_name%TYPE
402 INDEX BY BINARY_INTEGER;
403 TYPE t_inv_payee_address1 IS TABLE OF
404 IBY_PAYMENTS_ALL.inv_payee_address1%TYPE
405 INDEX BY BINARY_INTEGER;
406 TYPE t_inv_payee_address2 IS TABLE OF
407 IBY_PAYMENTS_ALL.inv_payee_address2%TYPE
408 INDEX BY BINARY_INTEGER;
409 TYPE t_inv_payee_address3 IS TABLE OF
410 IBY_PAYMENTS_ALL.inv_payee_address3%TYPE
411 INDEX BY BINARY_INTEGER;
412 TYPE t_inv_payee_address4 IS TABLE OF
413 IBY_PAYMENTS_ALL.inv_payee_address4%TYPE
414 INDEX BY BINARY_INTEGER;
415 TYPE t_inv_payee_city IS TABLE OF
416 IBY_PAYMENTS_ALL.inv_payee_city%TYPE
417 INDEX BY BINARY_INTEGER;
418 TYPE t_inv_payee_postal_code IS TABLE OF
419 IBY_PAYMENTS_ALL.inv_payee_postal_code%TYPE
420 INDEX BY BINARY_INTEGER;
421 TYPE t_inv_payee_state IS TABLE OF
422 IBY_PAYMENTS_ALL.inv_payee_state%TYPE
423 INDEX BY BINARY_INTEGER;
424 TYPE t_inv_payee_province IS TABLE OF
425 IBY_PAYMENTS_ALL.inv_payee_province%TYPE
426 INDEX BY BINARY_INTEGER;
427 TYPE t_inv_payee_county IS TABLE OF
428 IBY_PAYMENTS_ALL.inv_payee_county%TYPE
429 INDEX BY BINARY_INTEGER;
430 TYPE t_inv_payee_country IS TABLE OF
431 IBY_PAYMENTS_ALL.inv_payee_country%TYPE
432 INDEX BY BINARY_INTEGER;
433 TYPE t_inv_payee_party_name IS TABLE OF
434 IBY_PAYMENTS_ALL.inv_payee_party_name%TYPE
435 INDEX BY BINARY_INTEGER;
436 TYPE t_inv_payee_le_reg_num IS TABLE OF
437 IBY_PAYMENTS_ALL.inv_payee_le_reg_num%TYPE
438 INDEX BY BINARY_INTEGER;
439 TYPE t_inv_payee_tax_reg_num IS TABLE OF
440 IBY_PAYMENTS_ALL.inv_payee_tax_reg_num%TYPE
441 INDEX BY BINARY_INTEGER;
442 TYPE t_inv_payee_address_concat IS TABLE OF
443 IBY_PAYMENTS_ALL.inv_payee_address_concat%TYPE
444 INDEX BY BINARY_INTEGER;
445 TYPE t_inv_beneficiary_name IS TABLE OF
446 IBY_PAYMENTS_ALL.inv_beneficiary_name%TYPE
447 INDEX BY BINARY_INTEGER;
448 TYPE t_inv_payee_party_number IS TABLE OF
452 IBY_PAYMENTS_ALL.inv_payee_alternate_name%TYPE
449 IBY_PAYMENTS_ALL.inv_payee_party_number%TYPE
450 INDEX BY BINARY_INTEGER;
451 TYPE t_inv_payee_alternate_name IS TABLE OF
453 INDEX BY BINARY_INTEGER;
454 TYPE t_inv_payee_site_alt_name IS TABLE OF
455 IBY_PAYMENTS_ALL.inv_payee_site_alt_name%TYPE
456 INDEX BY BINARY_INTEGER;
457 TYPE t_inv_payee_supplier_number IS TABLE OF
458 IBY_PAYMENTS_ALL.inv_payee_supplier_number%TYPE
459 INDEX BY BINARY_INTEGER;
460 TYPE t_inv_payee_first_party_ref IS TABLE OF
461 IBY_PAYMENTS_ALL.inv_payee_first_party_ref%TYPE
462 INDEX BY BINARY_INTEGER;
463 TYPE t_ext_acct_ownr_inv_prty_id IS TABLE OF
464 IBY_PAYMENTS_ALL.ext_bnk_acct_ownr_inv_prty_id%TYPE
465 INDEX BY BINARY_INTEGER;
466 TYPE t_ext_bnk_branch_inv_prty_id IS TABLE OF
467 IBY_PAYMENTS_ALL.ext_bnk_branch_inv_prty_id%TYPE
468 INDEX BY BINARY_INTEGER;
469 TYPE t_ext_acct_ownr_inv_prty_nme IS TABLE OF
470 IBY_PAYMENTS_ALL.ext_bnk_acct_ownr_inv_prty_nme%TYPE
471 INDEX BY BINARY_INTEGER;
472 TYPE t_inv_payee_party_attr_cat IS TABLE OF
473 IBY_PAYMENTS_ALL.inv_payee_party_attr_cat%TYPE
474 INDEX BY BINARY_INTEGER;
475 TYPE t_inv_payee_supplier_attr_cat IS TABLE OF
476 IBY_PAYMENTS_ALL.inv_payee_supplier_attr_cat%TYPE
477 INDEX BY BINARY_INTEGER;
478 TYPE t_inv_payee_site_attr_cat IS TABLE OF
479 IBY_PAYMENTS_ALL.inv_payee_spplr_site_attr_cat%TYPE
480 INDEX BY BINARY_INTEGER;
481 TYPE t_inv_payee_supplier_site_name IS TABLE OF
482 IBY_PAYMENTS_ALL.inv_payee_supplier_site_name%TYPE
483 INDEX BY BINARY_INTEGER;
484 TYPE t_inv_payee_spp_site_alt_name IS TABLE OF
485 IBY_PAYMENTS_ALL.inv_payee_spplr_site_alt_name%TYPE
486 INDEX BY BINARY_INTEGER;
487 TYPE t_inv_payee_supplier_id IS TABLE OF
488 IBY_PAYMENTS_ALL.inv_payee_supplier_id%TYPE
489 INDEX BY BINARY_INTEGER;
490 /*TPP-End*/
491
492 /*
493 * (Stealth) Fix for bug 5475920:
494 *
495 * Ensure that employee related fields are populated in the
496 * payment record.
497 */
498 TYPE t_address_source IS TABLE OF
499 IBY_PAYMENTS_ALL.address_source%TYPE
500 INDEX BY BINARY_INTEGER;
501
502 TYPE t_employee_address_code IS TABLE OF
503 IBY_PAYMENTS_ALL.employee_address_code%TYPE
504 INDEX BY BINARY_INTEGER;
505
506 TYPE t_employee_person_id IS TABLE OF
507 IBY_PAYMENTS_ALL.employee_person_id%TYPE
508 INDEX BY BINARY_INTEGER;
509
510 TYPE t_employee_address_id IS TABLE OF
511 IBY_PAYMENTS_ALL.employee_address_id%TYPE
512 INDEX BY BINARY_INTEGER;
513
514 TYPE t_employee_payment_flag IS TABLE OF
515 IBY_PAYMENTS_ALL.employee_payment_flag%TYPE
516 INDEX BY BINARY_INTEGER;
517
518 TYPE t_calling_app_id IS TABLE OF
519 IBY_HOOK_PAYMENTS_T.calling_app_id%TYPE
520 INDEX BY BINARY_INTEGER;
521
522 TYPE t_call_app_pay_service_req_cd IS TABLE OF
523 IBY_HOOK_PAYMENTS_T.call_app_pay_service_req_code%TYPE
524 INDEX BY BINARY_INTEGER;
525
526 TYPE t_dont_pay_flg IS TABLE OF
527 IBY_HOOK_DOCS_IN_PMT_T.dont_pay_flag%TYPE
528 INDEX BY BINARY_INTEGER;
529
530 TYPE t_dont_pay_reason_cd IS TABLE OF
531 IBY_HOOK_DOCS_IN_PMT_T.dont_pay_reason_code%TYPE
532 INDEX BY BINARY_INTEGER;
533
534 TYPE t_dont_pay_desc IS TABLE OF
535 IBY_HOOK_DOCS_IN_PMT_T.dont_pay_description%TYPE
536 INDEX BY BINARY_INTEGER;
537
538
539 TYPE payment_tab_type IS RECORD
540 (
541 payment_id t_payment_id,
542 payment_method_code t_payment_method_code,
543 payment_service_request_id t_payment_service_request_id,
544 process_type t_process_type,
545 payment_status t_payment_status,
546 payments_complete_flag t_payments_complete_flag,
547 payment_function t_payment_function,
548 payment_amount t_payment_amount,
549 payment_currency_code t_payment_currency_code,
550 bill_payable_flag t_bill_payable_flag,
551 exclusive_payment_flag t_exclusive_payment_flag,
552 sep_remit_advice_req_flag t_sep_remit_advice_req_flag,
553 internal_bank_account_id t_internal_bank_account_id,
554 org_id t_org_id,
555 org_type t_org_type,
556 legal_entity_id t_legal_entity_id,
557 declare_payment_flag t_declare_payment_flag,
558 delivery_channel_code t_delivery_channel_code,
559 ext_payee_id t_ext_payee_id,
560 payment_instruction_id t_payment_instruction_id,
561 payment_profile_id t_payment_profile_id,
562 pregrouped_payment_flag t_pregrouped_payment_flag,
563 stop_confirmed_flag t_stop_confirmed_flag,
564 stop_released_flag t_stop_released_flag,
565 stop_request_placed_flag t_stop_request_placed_flag,
569 last_update_login t_last_update_login,
566 created_by t_created_by,
567 creation_date t_creation_date,
568 last_updated_by t_last_updated_by,
570 last_update_date t_last_update_date,
571 object_version_number t_object_version_number,
572 payee_party_id t_payee_party_id,
573 party_site_id t_party_site_id,
574 supplier_site_id t_supplier_site_id,
575 payment_reason_code t_payment_reason_code,
576 payment_reason_comments t_payment_reason_comments,
577 payment_date t_payment_date,
578 anticipated_value_date t_anticipated_value_date,
579 declaration_amount t_declaration_amount,
580 declaration_currency_code t_declaration_currency_code,
581 discount_amount_taken t_discount_amount_taken,
582 payment_details t_payment_details,
583 bank_charge_bearer t_bank_charge_bearer,
584 bank_charge_amount t_bank_charge_amount,
585 settlement_priority t_settlement_priority,
586 remittance_message1 t_remittance_message1,
587 remittance_message2 t_remittance_message2,
588 remittance_message3 t_remittance_message3,
589 payment_reference_number t_payment_reference_number,
590 paper_document_number t_paper_document_number,
591 bank_assigned_ref_code t_bank_assigned_ref_code,
592 external_bank_account_id t_external_bank_account_id,
593 unique_remittance_identifier t_unique_remittance_identifier,
594 uri_check_digit t_uri_check_digit,
595 bank_instruction1_code t_bank_instruction1_code,
596 bank_instruction2_code t_bank_instruction2_code,
597 bank_instruction_details t_bank_instruction_details,
598 payment_text_message1 t_payment_text_message1,
599 payment_text_message2 t_payment_text_message2,
600 payment_text_message3 t_payment_text_message3,
601 maturity_date t_maturity_date,
602 payment_due_date t_payment_due_date,
603 document_category_code t_document_category_code,
604 document_sequence_id t_document_sequence_id,
605 document_sequence_value t_document_sequence_value,
606 beneficiary_party t_beneficiary_party,
607 stop_confirmed_by t_stop_confirmed_by,
608 stop_confirm_date t_stop_confirm_date,
609 stop_confirm_reason t_stop_confirm_reason,
610 stop_confirm_reference t_stop_confirm_reference,
611 stop_released_by t_stop_released_by,
612 stop_release_date t_stop_release_date,
613 stop_release_reason t_stop_release_reason,
614 stop_release_reference t_stop_release_reference,
615 stop_request_date t_stop_request_date,
616 stop_request_placed_by t_stop_request_placed_by,
617 stop_request_reason t_stop_request_reason,
618 stop_request_reference t_stop_request_reference,
619 voided_by t_voided_by,
620 void_date t_void_date,
621 void_reason t_void_reason,
622 remit_to_location_id t_remit_to_location_id,
623 completed_pmts_group_id t_completed_pmts_group_id,
624 attribute_category t_attribute_category,
625 attribute1 t_attribute1,
626 attribute2 t_attribute2,
627 attribute3 t_attribute3,
628 attribute4 t_attribute4,
629 attribute5 t_attribute5,
630 attribute6 t_attribute6,
631 attribute7 t_attribute7,
632 attribute8 t_attribute8,
633 attribute9 t_attribute9,
634 attribute10 t_attribute10,
635 attribute11 t_attribute11,
636 attribute12 t_attribute12,
637 attribute13 t_attribute13,
638 attribute14 t_attribute14,
639 attribute15 t_attribute15,
640 ext_branch_number t_ext_branch_number,
641 ext_bank_number t_ext_bank_number,
642 ext_bank_account_name t_ext_bank_account_name,
643 ext_bank_account_number t_ext_bank_account_number,
644 ext_bank_account_type t_ext_bank_account_type,
645 ext_bank_account_iban_number t_ext_bank_account_iban_number,
646 payee_name t_payee_name,
650 payee_address4 t_payee_address4,
647 payee_address1 t_payee_address1,
648 payee_address2 t_payee_address2,
649 payee_address3 t_payee_address3,
651 payee_city t_payee_city,
652 payee_postal_code t_payee_postal_code,
653 payee_state t_payee_state,
654 payee_province t_payee_province,
655 payee_county t_payee_county,
656 payee_country t_payee_country,
657 remit_advice_delivery_method t_remit_advice_delivery_method,
658 remit_advice_email t_remit_advice_email,
659 remit_advice_fax t_remit_advice_fax,
660 address_source t_address_source,
661 employee_address_code t_employee_address_code,
662 employee_person_id t_employee_person_id,
663 employee_address_id t_employee_address_id,
664 employee_payment_flag t_employee_payment_flag,
665 calling_app_id t_calling_app_id,
666 call_app_pay_service_req_cd t_call_app_pay_service_req_cd,
667 dont_pay_flg t_dont_pay_flg,
668 dont_pay_reason_cd t_dont_pay_reason_cd,
669 dont_pay_desc t_dont_pay_desc,
670 /*TPP-Start*/
671 ext_inv_payee_id t_ext_inv_payee_id ,
672 inv_payee_party_id t_inv_payee_party_id ,
673 inv_party_site_id t_inv_party_site_id ,
674 inv_supplier_site_id t_inv_supplier_site_id ,
675 inv_beneficiary_party t_inv_beneficiary_party ,
676 inv_payee_name t_inv_payee_name ,
677 inv_payee_address1 t_inv_payee_address1 ,
678 inv_payee_address2 t_inv_payee_address2 ,
679 inv_payee_address3 t_inv_payee_address3 ,
680 inv_payee_address4 t_inv_payee_address4 ,
681 inv_payee_city t_inv_payee_city ,
682 inv_payee_postal_code t_inv_payee_postal_code ,
683 inv_payee_state t_inv_payee_state ,
684 inv_payee_province t_inv_payee_province ,
685 inv_payee_county t_inv_payee_county ,
686 inv_payee_country t_inv_payee_country ,
687 inv_payee_party_name t_inv_payee_party_name ,
688 inv_payee_le_reg_num t_inv_payee_le_reg_num,
689 inv_payee_tax_reg_num t_inv_payee_tax_reg_num,
690 inv_payee_address_concat t_inv_payee_address_concat ,
691 inv_beneficiary_name t_inv_beneficiary_name ,
692 inv_payee_party_number t_inv_payee_party_number ,
693 inv_payee_alternate_name t_inv_payee_alternate_name ,
694 inv_payee_site_alt_name t_inv_payee_site_alt_name,
695 inv_payee_supplier_number t_inv_payee_supplier_number ,
696 inv_payee_first_party_ref t_inv_payee_first_party_ref,
697 ext_bnk_acct_ownr_inv_prty_id t_ext_acct_ownr_inv_prty_id,
698 ext_bnk_branch_inv_prty_id t_ext_bnk_branch_inv_prty_id,
699 ext_bnk_acct_ownr_inv_prty_nme t_ext_acct_ownr_inv_prty_nme,
700 inv_payee_party_attr_cat t_inv_payee_party_attr_cat,
701 inv_payee_supplier_attr_cat t_inv_payee_supplier_attr_cat,
702 inv_payee_spplr_site_attr_cat t_inv_payee_site_attr_cat,
703 inv_payee_supplier_site_name t_inv_payee_supplier_site_name,
704 inv_payee_spplr_site_alt_name t_inv_payee_spp_site_alt_name,
705 inv_payee_supplier_id t_inv_payee_supplier_id
706 /*TPP-End*/
707
708 );
709
710 paymentTab payment_tab_type;
711
712
713 -- Get Employee Full Name from HR tables
714 FUNCTION Get_Employee_Full_Name(p_person_id IN NUMBER, p_party_id IN NUMBER,p_party_name IN VARCHAR2)
715 RETURN VARCHAR2;
716
717
718 /*--------------------------------------------------------------------
719 | NAME:
720 |
721 | PURPOSE:
722 | This procedure is used to initialize global memory structure
723 | / Destroy the no longer used memory structure
724 |
725 | PARAMETERS:
726 |
727 | NONE
728 |
729 | RETURNS:
730 |
731 | NOTES:
732 |
733 *---------------------------------------------------------------------*/
734 PROCEDURE delete_paymentTab;
735
736 --
737 -- This data structure will be populated with the data
738 -- from paymentRecType record. This record will only store
739 -- success payments whereas paymentRecType can store both
740 -- successful and failed payments. A table hookPaymentRecType
741 -- records will be passed to the calling application via a
742 -- hook / callout. The calling application can adjust the
743 -- payment amount (and it's constituent document amounts)
744 -- for purposes like bank charge calculation, tax withholding etc.
745 --
746 -- The record hookDocsInPaymentRecType holds the documents
747 -- corresponding to this payment.
748 --
749 --
750 -- Table of successful payments to be passed to the
751 -- calling application via hook.
752 --
753 TYPE hookPaymentTabType IS TABLE OF IBY_HOOK_PAYMENTS_T%ROWTYPE
754 INDEX BY BINARY_INTEGER;
755
756 --
757 -- This record needs to be created because of the fact that
758 -- there is no document id column in IBY_PAYMENTS_ALL table. So
759 -- we cannot add the document id as a field in the paymentRecType
763 -- Therefore, we need a separate data structure to keep track
760 -- record (adding this field to the record will cause a syntax
761 -- error during the bulk update).
762 --
764 -- of the documents that are part of a payment. The docsInPaymentRecType
765 -- is that data structure. After all the grouping operations are
766 -- completed, the IBY_DOCS_PAYABLE_ALL table needs to be
767 -- updated to indicate the PAYMENT_ID for each document that
768 -- has been put into a payment. The docsInPaymentRecType
769 -- is used for this update.
770 --
771 -- The record paymentRecType holds the payments corresponding
772 -- to these documents.
773 --
774 TYPE docsInPaymentRecType IS RECORD (
775 payment_id
776 IBY_PAYMENTS_ALL.payment_id%TYPE,
777 document_id
778 IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE,
779 calling_app_id
780 IBY_DOCS_PAYABLE_ALL.calling_app_id%TYPE,
781 calling_app_doc_id1
782 IBY_DOCS_PAYABLE_ALL.calling_app_doc_unique_ref1%TYPE,
783 calling_app_doc_id2
784 IBY_DOCS_PAYABLE_ALL.calling_app_doc_unique_ref2%TYPE,
785 calling_app_doc_id3
786 IBY_DOCS_PAYABLE_ALL.calling_app_doc_unique_ref3%TYPE,
787 calling_app_doc_id4
788 IBY_DOCS_PAYABLE_ALL.calling_app_doc_unique_ref4%TYPE,
789 calling_app_doc_id5
790 IBY_DOCS_PAYABLE_ALL.calling_app_doc_unique_ref5%TYPE,
791 pay_proc_ttype_cd
792 IBY_DOCS_PAYABLE_ALL.pay_proc_trxn_type_code%TYPE,
793 document_amount
794 IBY_DOCS_PAYABLE_ALL.payment_amount%TYPE,
795 document_currency
796 IBY_DOCS_PAYABLE_ALL.payment_currency_code%TYPE,
797 payment_grp_num
798 IBY_DOCS_PAYABLE_ALL.payment_grouping_number%TYPE,
799 document_status
800 IBY_DOCS_PAYABLE_ALL.document_status%TYPE := 'PAYMENT_CREATED',
801 amount_withheld
802 IBY_DOCS_PAYABLE_ALL.amount_withheld%TYPE := 0,
803 pmt_due_date
804 IBY_DOCS_PAYABLE_ALL.payment_due_date%TYPE,
805 discount_date
806 IBY_DOCS_PAYABLE_ALL.discount_date%TYPE,
807 int_bank_acct_id
808 IBY_DOCS_PAYABLE_ALL.internal_bank_account_id%TYPE,
809 ext_payee_id
810 IBY_DOCS_PAYABLE_ALL.ext_payee_id%TYPE,
811 payee_id
812 IBY_DOCS_PAYABLE_ALL.payee_party_id%TYPE,
813 payee_site_id
814 IBY_DOCS_PAYABLE_ALL.party_site_id%TYPE,
815 supplier_site_id
816 IBY_DOCS_PAYABLE_ALL.supplier_site_id%TYPE,
817 org_id
818 IBY_DOCS_PAYABLE_ALL.org_id%TYPE,
819 org_type
820 IBY_DOCS_PAYABLE_ALL.org_type%TYPE,
821 ext_bank_acct_id
822 IBY_DOCS_PAYABLE_ALL.external_bank_account_id%TYPE,
823 pmt_curr_discount
824 IBY_DOCS_PAYABLE_ALL.payment_curr_discount_taken%TYPE,
825 delivery_channel
826 IBY_DOCS_PAYABLE_ALL.delivery_channel_code%TYPE,
827 unique_remit_id
828 IBY_DOCS_PAYABLE_ALL.unique_remittance_identifier%TYPE,
829 uri_ckdigits
830 IBY_DOCS_PAYABLE_ALL.uri_check_digit%TYPE,
831
832 /*TPP-Start*/
833 inv_payee_party_id IBY_DOCS_PAYABLE_ALL.inv_payee_party_id%TYPE,
834 inv_party_site_id IBY_DOCS_PAYABLE_ALL.inv_party_site_id%TYPE,
835 inv_supplier_site_id IBY_DOCS_PAYABLE_ALL.inv_supplier_site_id%TYPE,
836 inv_beneficiary_party IBY_DOCS_PAYABLE_ALL.inv_beneficiary_party%TYPE,
837 ext_inv_payee_id IBY_DOCS_PAYABLE_ALL.ext_inv_payee_id%TYPE,
838 relationship_id IBY_DOCS_PAYABLE_ALL.relationship_id%TYPE
839 /*TPP-End*/
840 );
841
842 --
843 -- Used to update of the IBY_DOCS_PAYABLE_ALL table.
844 --
845 TYPE docsInPaymentTabType IS TABLE OF docsInPaymentRecType
846 INDEX BY BINARY_INTEGER;
847
848 --
849 -- This record stores the count of successful documents
850 -- for a particular payment. It is useful in situations
851 -- like remittance advice creation where is is necessary
852 -- to know how many successful documents exist for a
853 -- particular payment.
854 --
855 TYPE successDocsCountPerPmtRec IS RECORD (
856 payment_id
857 IBY_PAYMENTS_ALL.payment_id%TYPE,
858 success_docs_count
859 NUMBER(15)
860 );
861
862 --
863 -- Table of success docs count per payment
864 --
865 TYPE successDocsCountPerPmtTab IS TABLE OF successDocsCountPerPmtRec
866 INDEX BY BINARY_INTEGER;
867
868 --
869 -- A record that stores a payment method code along with it's
870 -- maturity days offset.
871 --
872 TYPE pmtMethodMaturityDaysRec IS RECORD (
873 pmt_method_cd
874 IBY_PAYMENT_METHODS_VL.payment_method_code%TYPE,
875 maturity_offset_days
876 IBY_PAYMENT_METHODS_VL.maturity_date_offset_days%TYPE
877 );
878
879 --
880 -- Table of payment methods and their maturity offset days.
881 --
882 TYPE pmtMethodMaturityDaysTab IS TABLE OF pmtMethodMaturityDaysRec
883 INDEX BY BINARY_INTEGER;
884
885 --
886 -- List of documents to be passed to the calling
887 -- app via hook.
888 --
889 -- The record hookPaymentRecType holds the payments
890 -- corresponding to these documents.
894 TYPE hookDocsInPaymentTabType IS TABLE OF IBY_HOOK_DOCS_IN_PMT_T%ROWTYPE
891 --
892 -- Table of documents that will be exposed to the hook.
893 --
895 INDEX BY BINARY_INTEGER;
896
897 --
898 -- The document id table
899 --
900 TYPE docPayTabType IS TABLE OF IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE
901 INDEX BY BINARY_INTEGER;
902
903 --
904 -- This record stores all the document fields that are used in
905 -- as criteria for grouping a document into a payment. Each
906 -- of these fields will result in a grouping rule.
907 --
908 -- Some of the fields of this record are not used specifically
909 -- for grouping, but for raising business events etc.
910 -- e.g., the calling app pay req id
911 --
912 -- Some of the grouping criteria are user defined; these are
913 -- specified in the IBY_PMT_CREATION_RULES table. This record
914 -- contains placeholder for the user defined grouping fields
915 -- as well.
916 --
917 TYPE paymentGroupCriteriaType IS RECORD (
918 calling_app_payreq_cd
919 IBY_PAY_SERVICE_REQUESTS.call_app_pay_service_req_code%TYPE,
920 document_id
921 IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE,
922 calling_app_id
923 IBY_DOCS_PAYABLE_ALL.calling_app_id%TYPE,
924 calling_app_doc_id1
925 IBY_DOCS_PAYABLE_ALL.calling_app_doc_unique_ref1%TYPE,
926 calling_app_doc_id2
927 IBY_DOCS_PAYABLE_ALL.calling_app_doc_unique_ref2%TYPE,
928 calling_app_doc_id3
929 IBY_DOCS_PAYABLE_ALL.calling_app_doc_unique_ref3%TYPE,
930 calling_app_doc_id4
931 IBY_DOCS_PAYABLE_ALL.calling_app_doc_unique_ref4%TYPE,
932 calling_app_doc_id5
933 IBY_DOCS_PAYABLE_ALL.calling_app_doc_unique_ref5%TYPE,
934 pay_proc_ttype_cd
935 IBY_DOCS_PAYABLE_ALL.pay_proc_trxn_type_code%TYPE,
936 payment_grp_num
937 IBY_DOCS_PAYABLE_ALL.payment_grouping_number%TYPE,
938 payment_method_cd
939 IBY_DOCS_PAYABLE_ALL.payment_method_code%TYPE,
940 int_bank_acct_id
941 IBY_DOCS_PAYABLE_ALL.internal_bank_account_id%TYPE,
942 ext_bank_acct_id
943 IBY_DOCS_PAYABLE_ALL.external_bank_account_id%TYPE,
944 payment_profile_id
945 IBY_DOCS_PAYABLE_ALL.payment_profile_id%TYPE,
946 org_id
947 IBY_DOCS_PAYABLE_ALL.org_id%TYPE,
948 org_type
949 IBY_DOCS_PAYABLE_ALL.org_type%TYPE,
950 payment_function
951 IBY_DOCS_PAYABLE_ALL.payment_function%TYPE,
952 ext_payee_id
953 IBY_DOCS_PAYABLE_ALL.ext_payee_id%TYPE,
954 payee_party_id
955 IBY_DOCS_PAYABLE_ALL.payee_party_id%TYPE,
956 payee_party_site_id
957 IBY_DOCS_PAYABLE_ALL.party_site_id%TYPE,
958 supplier_site_id
959 IBY_DOCS_PAYABLE_ALL.supplier_site_id%TYPE,
960 remit_loc_id
961 IBY_DOCS_PAYABLE_ALL.remit_to_location_id%TYPE,
962 amount_withheld
963 IBY_DOCS_PAYABLE_ALL.amount_withheld%TYPE,
964 bank_inst1_code
965 IBY_EXTERNAL_PAYEES_ALL.bank_instruction1_code%TYPE,
966 bank_inst2_code
967 IBY_EXTERNAL_PAYEES_ALL.bank_instruction2_code%TYPE,
968 pmt_txt_msg1
969 IBY_EXTERNAL_PAYEES_ALL.payment_text_message1%TYPE,
970 pmt_txt_msg2
971 IBY_EXTERNAL_PAYEES_ALL.payment_text_message2%TYPE,
972 pmt_txt_msg3
973 IBY_EXTERNAL_PAYEES_ALL.payment_text_message3%TYPE,
974 payment_currency
975 IBY_DOCS_PAYABLE_ALL.payment_currency_code%TYPE,
976 payment_amount
977 IBY_DOCS_PAYABLE_ALL.payment_amount%TYPE,
978 payment_date
979 IBY_DOCS_PAYABLE_ALL.payment_date%TYPE,
980 pay_alone_flag
981 IBY_DOCS_PAYABLE_ALL.exclusive_payment_flag%TYPE,
982 bank_charge_bearer
983 IBY_DOCS_PAYABLE_ALL.bank_charge_bearer%TYPE,
984 delivery_channel
985 IBY_DOCS_PAYABLE_ALL.delivery_channel_code%TYPE,
986 settle_priority
987 IBY_DOCS_PAYABLE_ALL.settlement_priority%TYPE,
988 supplier_message1
989 IBY_DOCS_PAYABLE_ALL.remittance_message1%TYPE,
990 supplier_message2
991 IBY_DOCS_PAYABLE_ALL.remittance_message2%TYPE,
992 supplier_message3
993 IBY_DOCS_PAYABLE_ALL.remittance_message3%TYPE,
994 unique_remit_id
995 IBY_DOCS_PAYABLE_ALL.unique_remittance_identifier%TYPE,
996 uri_checkdigit
997 IBY_DOCS_PAYABLE_ALL.uri_check_digit%TYPE,
998 pmt_reason_code
999 IBY_DOCS_PAYABLE_ALL.payment_reason_code%TYPE,
1000 pmt_reason_comments
1001 IBY_DOCS_PAYABLE_ALL.payment_reason_comments%TYPE,
1002 pmt_due_date
1003 IBY_DOCS_PAYABLE_ALL.payment_due_date%TYPE,
1004 discount_date
1005 IBY_DOCS_PAYABLE_ALL.discount_date%TYPE,
1006 discount_amount
1007 IBY_DOCS_PAYABLE_ALL.payment_curr_discount_taken%TYPE,
1008 benef_party
1009 IBY_DOCS_PAYABLE_ALL.beneficiary_party%TYPE,
1010 addr_source
1011 IBY_DOCS_PAYABLE_ALL.address_source%TYPE,
1012 emp_addr_code
1013 IBY_DOCS_PAYABLE_ALL.employee_address_code%TYPE,
1014 emp_person_id
1015 IBY_DOCS_PAYABLE_ALL.employee_person_id%TYPE,
1016 emp_address_id
1017 IBY_DOCS_PAYABLE_ALL.employee_address_id%TYPE,
1018 emp_payment_flag
1022 bnk_chg_bearer_flag
1019 IBY_DOCS_PAYABLE_ALL.employee_payment_flag%TYPE,
1020 supplier_msg_flag
1021 IBY_PMT_CREATION_RULES.group_by_remittance_message%TYPE,
1023 IBY_PMT_CREATION_RULES.group_by_bank_charge_bearer%TYPE,
1024 delv_channel_flag
1025 IBY_PMT_CREATION_RULES.group_by_delivery_channel%TYPE,
1026 settle_priority_flag
1027 IBY_PMT_CREATION_RULES.group_by_settle_priority_flag%TYPE,
1028 pmt_details_flag
1029 IBY_PMT_CREATION_RULES.group_by_payment_details_flag%TYPE,
1030 pmt_details_length
1031 IBY_PMT_CREATION_RULES.payment_details_length_limit%TYPE,
1032 payment_details_formula
1033 IBY_PMT_CREATION_RULES.payment_details_formula%TYPE,
1034 max_documents_flag
1035 IBY_PMT_CREATION_RULES.group_by_max_documents_flag%TYPE,
1036 max_documents_limit
1037 IBY_PMT_CREATION_RULES.max_documents_per_payment%TYPE,
1038 unique_remit_id_flag
1039 IBY_PMT_CREATION_RULES.group_by_unique_remit_id_flag%TYPE,
1040 pmt_reason_flag
1041 IBY_PMT_CREATION_RULES.group_by_payment_reason%TYPE,
1042 pmt_due_date_flag
1043 IBY_PMT_CREATION_RULES.group_by_due_date_flag%TYPE,
1044 processing_type
1045 IBY_PAYMENT_PROFILES.processing_type%TYPE,
1046 decl_option
1047 IBY_PAYMENT_PROFILES.declaration_option%TYPE,
1048 decl_only_fx_flag
1049 IBY_PAYMENT_PROFILES.dcl_only_foreign_curr_pmt_flag%TYPE,
1050 decl_curr_fx_rate_type
1051 IBY_PAYMENT_PROFILES.declaration_curr_fx_rate_type%TYPE,
1052 decl_curr_code
1053 IBY_PAYMENT_PROFILES.declaration_currency_code%TYPE,
1054 decl_threshold_amount
1055 IBY_PAYMENT_PROFILES.declaration_threshold_amount%TYPE,
1056 max_payment_amount
1057 IBY_PAY_SERVICE_REQUESTS.maximum_payment_amount%TYPE,
1058 min_payment_amount
1059 IBY_PAY_SERVICE_REQUESTS.minimum_payment_amount%TYPE,
1060 allow_zero_pmts_flag
1061 IBY_PAY_SERVICE_REQUESTS.allow_zero_payments_flag%TYPE,
1062 support_prom_notes_flag
1063 IBY_PAYMENT_METHODS_VL.support_bills_payable_flag%TYPE
1064 );
1065
1066 --
1067 -- Table of payment grouping criteria.
1068 --
1069 TYPE paymentGroupCriteriaTabType IS TABLE OF paymentGroupCriteriaType
1070 INDEX BY BINARY_INTEGER;
1071
1072 --
1073 -- These are payment related criteria that are passed
1074 -- in as part of the payment request. Payments should
1075 -- be validated using these criteria in addition to
1076 -- the usual payment validation rules.
1077 --
1078 TYPE payReqImposedCriteria IS RECORD (
1079 max_pmt_amt_limit
1080 IBY_PAY_SERVICE_REQUESTS.maximum_payment_amount%TYPE,
1081 min_pmt_amt_limit
1082 IBY_PAY_SERVICE_REQUESTS.minimum_payment_amount%TYPE,
1083 allow_zero_pmts_flag
1084 IBY_PAY_SERVICE_REQUESTS.allow_zero_payments_flag%TYPE
1085 );
1086
1087 --
1088 -- This record stores one validation set applicable to
1089 -- a particular payment.
1090 --
1091 TYPE paymentValSetsRec IS RECORD (
1092 val_assign_id
1093 IBY_VAL_ASSIGNMENTS.validation_assignment_id%TYPE,
1094 val_assign_entity_type
1095 IBY_VAL_ASSIGNMENTS.val_assignment_entity_type%TYPE,
1096 val_set_name
1097 IBY_VALIDATION_SETS_VL.validation_set_display_name%TYPE,
1098 val_set_code
1099 IBY_VALIDATION_SETS_VL.validation_set_code%TYPE,
1100 val_code_pkg
1101 IBY_VALIDATION_SETS_VL.validation_code_package%TYPE,
1102 val_code_entry_pt
1103 IBY_VALIDATION_SETS_VL.validation_code_entry_point%TYPE
1104 );
1105
1106 --
1107 -- Table of validation sets applicable to a particular payment
1108 --
1109 TYPE paymentValSetsTab IS TABLE OF paymentValSetsRec
1110 INDEX BY BINARY_INTEGER;
1111
1112 --
1113 -- This record stores some elements of a row from
1114 -- the IBY_REMIT_ADVICE_SETUP table
1115 --
1116 TYPE remitAdviceRec IS RECORD (
1117 payment_profile_cd
1118 IBY_REMIT_ADVICE_SETUP.system_profile_code%TYPE,
1119 doc_count_limit
1120 IBY_REMIT_ADVICE_SETUP.document_count_limit%TYPE,
1121 pmt_details_len_limit
1122 IBY_REMIT_ADVICE_SETUP.payment_details_length_limit%TYPE
1123 );
1124
1125 --
1126 -- Table of remittance advice records
1127 --
1128 TYPE remitAdviceRecTab IS TABLE OF remitAdviceRec
1129 INDEX BY BINARY_INTEGER;
1130
1131 --
1132 -- System options record
1133 --
1134 TYPE sysOptionsRecType IS RECORD (
1135 rej_level IBY_INTERNAL_PAYERS_ALL.
1136 payment_rejection_level_code%TYPE,
1137 revw_flag IBY_INTERNAL_PAYERS_ALL.
1138 require_prop_pmts_review_flag%TYPE
1139 );
1140
1141 --
1142 -- System options table
1143 --
1144 TYPE sysOptionsTabType IS TABLE OF sysOptionsRecType
1145 INDEX BY BINARY_INTEGER;
1146
1147 --
1148 -- Table of payment ids.
1149 --
1150 TYPE pmtIdsTab IS TABLE OF IBY_PAYMENTS_ALL.payment_id%TYPE
1151 INDEX BY BINARY_INTEGER;
1152
1153 --
1154 -- Internal bank account with its corresponding legal entity id.
1155 --
1159 );
1156 TYPE bankAccountLERecType IS RECORD (
1157 int_bank_acct_id CE_BANK_ACCOUNTS.bank_account_id%TYPE,
1158 le_id IBY_PAYMENTS_ALL.legal_entity_id%TYPE
1160
1161 --
1162 -- Table of internal bank accounts each with its legal entity.
1163 --
1164 TYPE bankAccountLETabType IS TABLE OF bankAccountLERecType
1165 INDEX BY BINARY_INTEGER;
1166
1167 --
1168 -- Stores the denormalized data related to the payer, payee,
1169 -- payer bank and payee bank. This data is stamped onto
1170 -- the payment in IBY_PAYMENTS_ALL table for audit purposes.
1171 --
1172 TYPE paymentAuditRecType IS RECORD (
1173
1174 payment_id -- 01
1175 IBY_PAYMENTS_ALL.payment_id%TYPE,
1176
1177 /* PAYER RELATED */
1178 payer_party_number
1179 IBY_PAYMENTS_ALL.payer_party_number%TYPE,
1180 payer_party_site_name
1181 IBY_PAYMENTS_ALL.payer_party_site_name%TYPE,
1182 payer_legal_name
1183 IBY_PAYMENTS_ALL.payer_legal_entity_name%TYPE,
1184 payer_tax_reg_number
1185 IBY_PAYMENTS_ALL.payer_tax_registration_num%TYPE,
1186 payer_le_reg_number
1187 IBY_PAYMENTS_ALL.payer_le_registration_num%TYPE,
1188 payer_party_id
1189 IBY_PAYMENTS_ALL.payer_party_id%TYPE,
1190 payer_location_id
1191 IBY_PAYMENTS_ALL.payer_location_id%TYPE,
1192 payer_party_attr_cat
1193 IBY_PAYMENTS_ALL.payer_party_attr_category%TYPE,
1194 payer_le_attr_cat -- 10
1195 IBY_PAYMENTS_ALL.payer_le_attr_category%TYPE,
1196
1197 /* PAYER SPECIAL FIELDS */
1198 payer_abbrev_agency_code
1199 IBY_PAYMENTS_ALL.payer_abbreviated_agency_code%TYPE,
1200 payer_us_employer_id
1201 IBY_PAYMENTS_ALL.payer_federal_us_employer_id%TYPE,
1202
1203 /* PAYER BANK RELATED */
1204 payer_bank_name
1205 IBY_PAYMENTS_ALL.int_bank_name%TYPE,
1206 payer_bank_number
1207 IBY_PAYMENTS_ALL.int_bank_number%TYPE,
1208 payer_bank_branch_number
1209 IBY_PAYMENTS_ALL.int_bank_branch_number%TYPE,
1210 payer_bank_branch_name
1211 IBY_PAYMENTS_ALL.int_bank_branch_name%TYPE,
1212 payer_bank_swift_code
1213 IBY_PAYMENTS_ALL.int_eft_swift_code%TYPE,
1214 payer_bank_acct_num
1215 IBY_PAYMENTS_ALL.int_bank_account_number%TYPE,
1216 payer_bank_acct_name
1217 IBY_PAYMENTS_ALL.int_bank_account_name%TYPE,
1218 payer_bank_acct_iban -- 20
1219 IBY_PAYMENTS_ALL.int_bank_account_iban%TYPE,
1220 payer_bank_agency_loc_code
1221 IBY_PAYMENTS_ALL.int_bank_acct_agency_loc_code%TYPE,
1222 payer_bank_branch_party_id
1223 IBY_PAYMENTS_ALL.int_bank_branch_party_id%TYPE,
1224 payer_bank_alt_name
1225 IBY_PAYMENTS_ALL.int_bank_alt_name%TYPE,
1226 payer_bank_branch_alt_name
1227 IBY_PAYMENTS_ALL.int_bank_branch_alt_name%TYPE,
1228 payer_bank_alt_account_name
1229 IBY_PAYMENTS_ALL.int_bank_account_alt_name%TYPE,
1230 payer_bank_account_num_elec
1231 IBY_PAYMENTS_ALL.int_bank_account_num_elec%TYPE,
1232 payer_bank_branch_location_id
1233 IBY_PAYMENTS_ALL.int_bank_branch_location_id%TYPE,
1234 payer_bank_branch_eft_user_num
1235 IBY_PAYMENTS_ALL.int_bank_branch_eft_user_num%TYPE,
1236
1237
1238 /* PAYEE RELATED */
1239 payee_party_number
1240 IBY_PAYMENTS_ALL.payee_party_number%TYPE,
1241 payee_party_name -- 30
1242 IBY_PAYMENTS_ALL.payee_party_name%TYPE,
1243 payee_name
1244 IBY_PAYMENTS_ALL.payee_name%TYPE,
1245 payee_name_alternate
1246 IBY_PAYMENTS_ALL.payee_alternate_name%TYPE,
1247 payee_add_line_1
1248 IBY_PAYMENTS_ALL.payee_address1%TYPE,
1249 payee_add_line_2
1250 IBY_PAYMENTS_ALL.payee_address2%TYPE,
1251 payee_add_line_3
1252 IBY_PAYMENTS_ALL.payee_address3%TYPE,
1253 payee_add_line_4
1254 IBY_PAYMENTS_ALL.payee_address4%TYPE,
1255 payee_city
1256 IBY_PAYMENTS_ALL.payee_city%TYPE,
1257 payee_county
1258 IBY_PAYMENTS_ALL.payee_county%TYPE,
1259 payee_province
1260 IBY_PAYMENTS_ALL.payee_province%TYPE,
1261 payee_state -- 40
1262 IBY_PAYMENTS_ALL.payee_state%TYPE,
1263 payee_country
1264 IBY_PAYMENTS_ALL.payee_country%TYPE,
1265 payee_postal_code
1266 IBY_PAYMENTS_ALL.payee_postal_code%TYPE,
1267 payee_address_concat
1268 IBY_PAYMENTS_ALL.payee_address_concat%TYPE,
1269 beneficiary_name
1270 IBY_PAYMENTS_ALL.beneficiary_name%TYPE,
1271 payee_party_attr_cat
1272 IBY_PAYMENTS_ALL.payee_party_attr_category%TYPE,
1273 payee_supplier_site_attr_cat
1274 IBY_PAYMENTS_ALL.payee_spplr_site_attr_category%TYPE,
1275 payee_supplier_site_name
1276 IBY_PAYMENTS_ALL.payee_supplier_site_name%TYPE,
1277
1278 /* VENDOR RELATED */
1279 payee_site_name_alternate
1280 IBY_PAYMENTS_ALL.payee_site_alternate_name%TYPE,
1281 payee_supplier_number
1285 payee_supplier_attr_cat
1282 IBY_PAYMENTS_ALL.payee_supplier_number%TYPE,
1283 payee_first_party_ref -- 50
1284 IBY_PAYMENTS_ALL.payee_first_party_reference%TYPE,
1286 IBY_PAYMENTS_ALL.payee_supplier_attr_category%TYPE,
1287 payee_supplier_id
1288 IBY_PAYMENTS_ALL.payee_supplier_id%TYPE,
1289
1290 /* PAYEE SPECIAL FIELDS */
1291 payee_tax_reg_number
1292 IBY_PAYMENTS_ALL.payee_tax_registration_num%TYPE,
1293 payee_le_reg_number
1294 IBY_PAYMENTS_ALL.payee_le_registration_num%TYPE,
1295
1296 /* PAYEE BANK RELATED */
1297 payee_bank_name
1298 IBY_PAYMENTS_ALL.ext_bank_name%TYPE,
1299 payee_bank_number
1300 IBY_PAYMENTS_ALL.ext_bank_number%TYPE,
1301 payee_bank_branch_number
1302 IBY_PAYMENTS_ALL.ext_branch_number%TYPE,
1303 payee_bank_branch_name
1304 IBY_PAYMENTS_ALL.ext_bank_branch_name%TYPE,
1305 payee_bank_acct_number
1306 IBY_PAYMENTS_ALL.ext_bank_account_number%TYPE,
1307 payee_bank_acct_name -- 60
1308 IBY_PAYMENTS_ALL.ext_bank_account_name%TYPE,
1309 payee_bank_acct_iban
1310 IBY_PAYMENTS_ALL.ext_bank_account_iban_number%TYPE,
1311 payee_bank_swift_code
1312 IBY_PAYMENTS_ALL.ext_eft_swift_code%TYPE,
1313 payee_bank_acct_type
1314 IBY_PAYMENTS_ALL.ext_bank_account_type%TYPE,
1315 payee_bank_payment_factor_flag
1316 IBY_PAYMENTS_ALL.ext_bank_acct_pmt_factor_flag%TYPE,
1317 payee_bank_owner_party_id
1318 IBY_PAYMENTS_ALL.ext_bank_acct_owner_party_id%TYPE,
1319 payee_bank_branch_party_id
1320 IBY_PAYMENTS_ALL.ext_bank_branch_party_id%TYPE,
1321 payee_bank_name_alt
1322 IBY_PAYMENTS_ALL.ext_bank_alt_name%TYPE,
1323 payee_bank_branch_name_alt
1324 IBY_PAYMENTS_ALL.ext_bank_branch_alt_name%TYPE,
1325 payee_bank_alt_account_name
1326 IBY_PAYMENTS_ALL.ext_bank_account_alt_name%TYPE,
1327 payee_bank_electronic_acct_num -- 70
1328 IBY_PAYMENTS_ALL.ext_bank_account_num_elec%TYPE,
1329 payee_bank_branch_location_id
1330 IBY_PAYMENTS_ALL.ext_bank_branch_location_id%TYPE,
1331 payee_bank_acct_owner_name
1332 IBY_PAYMENTS_ALL.ext_bank_acct_owner_party_name%TYPE,
1333
1334
1335 /* REMITTANCE ADVICE RELATED */
1336 remit_advice_delivery_method
1337 IBY_PAYMENTS_ALL.remit_advice_delivery_method%TYPE,
1338 remit_advice_email
1339 IBY_PAYMENTS_ALL.remit_advice_email%TYPE,
1340 remit_advice_fax
1341 IBY_PAYMENTS_ALL.remit_advice_fax%TYPE,
1342
1343 /*
1344 * Fix for bug 5522421:
1345 *
1346 * sra_delivery_method is the same as
1347 * remit_advice_delivery_method and is
1348 * being obsoleted.
1349 */
1350 --remit_advice_delv_method
1351 -- IBY_PAYMENTS_ALL.sra_delivery_method%TYPE,
1352
1353 /* DELIVERY CHANNEL RELATED */
1354 delivery_channel_format
1355 IBY_PAYMENTS_ALL.delivery_channel_format_value%TYPE,
1356
1357 /* DECLARATION REPORT RELATED */
1358 decl_curr_fx_rate_type
1359 IBY_PAYMENTS_ALL.declaration_exch_rate_type%TYPE,
1360 declaration_format
1361 IBY_PAYMENTS_ALL.declaration_format%TYPE,
1362
1363 /* PROFILE RELATED */
1364 payment_acct_profile_name -- 80
1365 IBY_PAYMENTS_ALL.payment_profile_acct_name%TYPE,
1366 payment_sys_profile_name
1367 IBY_PAYMENTS_ALL.payment_profile_sys_name%TYPE,
1368
1369 /* PAYMENT REASON */
1370 payment_reason_format
1371 IBY_PAYMENTS_ALL.payment_reason_format_value%TYPE,
1372
1373 /* BANK INSTRUCTION */
1374 bank_instr1_format
1375 IBY_PAYMENTS_ALL.bank_instruction1_format_value%TYPE,
1376 bank_instr2_format
1377 IBY_PAYMENTS_ALL.bank_instruction2_format_value%TYPE,
1378
1379 /* ORG */
1380 org_name
1381 IBY_PAYMENTS_ALL.org_name%TYPE,
1382
1383 /* RFC */
1384 payer_bank_branch_rfc_id
1385 IBY_PAYMENTS_ALL.int_bank_branch_rfc_identifier%TYPE,
1386
1387 /* REQUEST */
1388 ppr_name
1389 IBY_PAYMENTS_ALL.payment_process_request_name%TYPE,
1390 source_product -- 88
1391 IBY_PAYMENTS_ALL.source_product%TYPE,
1392 /*TPP-Start*/
1393
1394 inv_payee_name
1395 IBY_PAYMENTS_ALL.inv_payee_name%TYPE,
1396 inv_payee_address1
1397 IBY_PAYMENTS_ALL.inv_payee_address1%TYPE,
1398 inv_payee_address2
1399 IBY_PAYMENTS_ALL.inv_payee_address2%TYPE,
1400 inv_payee_address3
1401 IBY_PAYMENTS_ALL.inv_payee_address3%TYPE,
1402 inv_payee_address4
1403 IBY_PAYMENTS_ALL.inv_payee_address4%TYPE,
1404 inv_payee_city
1405 IBY_PAYMENTS_ALL.inv_payee_city%TYPE,
1406 inv_payee_postal_code
1407 IBY_PAYMENTS_ALL.inv_payee_postal_code%TYPE,
1408 inv_payee_state
1409 IBY_PAYMENTS_ALL.inv_payee_state%TYPE,
1410 inv_payee_province
1411 IBY_PAYMENTS_ALL.inv_payee_province%TYPE,
1412 inv_payee_county
1413 IBY_PAYMENTS_ALL.inv_payee_county%TYPE,
1414 inv_payee_country
1418 inv_payee_le_reg_num
1415 IBY_PAYMENTS_ALL.inv_payee_country%TYPE,
1416 inv_payee_party_name
1417 IBY_PAYMENTS_ALL.inv_payee_party_name%TYPE,
1419 IBY_PAYMENTS_ALL.inv_payee_le_reg_num%TYPE,
1420 inv_payee_tax_reg_num
1421 IBY_PAYMENTS_ALL.inv_payee_tax_reg_num%TYPE,
1422 inv_payee_address_concat
1423 IBY_PAYMENTS_ALL.inv_payee_address_concat%TYPE,
1424 inv_beneficiary_name
1425 IBY_PAYMENTS_ALL.inv_beneficiary_name%TYPE,
1426 inv_payee_party_number
1427 IBY_PAYMENTS_ALL.inv_payee_party_number%TYPE,
1428 inv_payee_alternate_name
1429 IBY_PAYMENTS_ALL.inv_payee_alternate_name%TYPE,
1430 inv_payee_site_alt_name
1431 IBY_PAYMENTS_ALL.inv_payee_site_alt_name%TYPE,
1432 inv_payee_supplier_number
1433 IBY_PAYMENTS_ALL.inv_payee_supplier_number%TYPE,
1434 inv_payee_first_party_ref
1435 IBY_PAYMENTS_ALL.inv_payee_first_party_ref%TYPE,
1436 ext_bnk_acct_ownr_inv_prty_id
1437 IBY_PAYMENTS_ALL.ext_bnk_acct_ownr_inv_prty_id%TYPE,
1438 ext_bnk_branch_inv_prty_id
1439 IBY_PAYMENTS_ALL.ext_bnk_branch_inv_prty_id%TYPE,
1440 ext_bnk_acct_ownr_inv_prty_nme
1441 IBY_PAYMENTS_ALL.ext_bnk_acct_ownr_inv_prty_nme%TYPE,
1442 inv_payee_party_attr_cat
1443 IBY_PAYMENTS_ALL.inv_payee_party_attr_cat%TYPE,
1444 inv_payee_supplier_attr_cat
1445 IBY_PAYMENTS_ALL.inv_payee_supplier_attr_cat%TYPE,
1446 inv_payee_spplr_site_attr_cat
1447 IBY_PAYMENTS_ALL.inv_payee_spplr_site_attr_cat%TYPE,
1448 inv_payee_supplier_site_name
1449 IBY_PAYMENTS_ALL.inv_payee_supplier_site_name%TYPE,
1450 inv_payee_spplr_site_alt_name
1451 IBY_PAYMENTS_ALL.inv_payee_spplr_site_alt_name%TYPE,
1452 inv_payee_supplier_id
1453 IBY_PAYMENTS_ALL.inv_payee_supplier_id%TYPE
1454 /*TPP-End*/
1455
1456 );
1457
1458 --
1459 -- Table of internal bank accounts each with its legal entity.
1460 --
1461 TYPE paymentAuditTabType IS TABLE OF paymentAuditRecType
1462 INDEX BY BINARY_INTEGER;
1463
1464 --
1465 -- Central bank reporting record
1466 --
1467 TYPE centralBankReportRecType IS RECORD (
1468 payment_id IBY_PAYMENTS_ALL.
1469 payment_id%TYPE,
1470 decl_option IBY_PAYMENT_PROFILES.
1471 declaration_option%TYPE,
1472 decl_only_fx_flag IBY_PAYMENT_PROFILES.
1473 dcl_only_foreign_curr_pmt_flag%TYPE,
1474 decl_curr_fx_rate_type IBY_PAYMENT_PROFILES.
1475 declaration_curr_fx_rate_type%TYPE,
1476 decl_curr_code IBY_PAYMENT_PROFILES.
1477 declaration_currency_code%TYPE,
1478 decl_threshold_amount IBY_PAYMENT_PROFILES.
1479 declaration_threshold_amount%TYPE
1480 );
1481
1482 --
1483 -- Central bank reporting table
1484 --
1485 TYPE centralBankReportTabType IS TABLE OF centralBankReportRecType
1486 INDEX BY BINARY_INTEGER;
1487
1488 /*--------------------------------------------------------------------
1489 | NAME:
1490 | createPayments
1491 |
1492 | PURPOSE:
1493 |
1494 |
1495 |
1496 | PARAMETERS:
1497 | IN
1498 |
1499 |
1500 | OUT
1501 |
1502 |
1503 | RETURNS:
1504 |
1505 | NOTES:
1506 |
1507 *---------------------------------------------------------------------*/
1508 PROCEDURE createPayments(
1509 p_payment_request_id IN IBY_PAY_SERVICE_REQUESTS.
1510 payment_service_request_id%TYPE,
1511 p_pmt_rejection_level IN IBY_INTERNAL_PAYERS_ALL.
1512 payment_rejection_level_code%TYPE,
1513 p_review_proposed_pmts_flag IN IBY_INTERNAL_PAYERS_ALL.
1514 require_prop_pmts_review_flag%TYPE,
1515 x_return_status IN OUT NOCOPY VARCHAR2
1516 );
1517
1518 /*--------------------------------------------------------------------
1519 | NAME:
1520 | performDocumentGrouping
1521 |
1522 | PURPOSE:
1523 |
1524 |
1525 |
1526 | PARAMETERS:
1527 | IN
1528 |
1529 |
1530 | OUT
1531 |
1532 |
1533 | RETURNS:
1534 |
1535 | NOTES:
1536 |
1537 *---------------------------------------------------------------------*/
1538 PROCEDURE performDocumentGrouping(
1539 p_payment_request_id IN IBY_PAY_SERVICE_REQUESTS.
1540 payment_service_request_id%TYPE,
1541 x_paymentTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
1542 x_docsInPmtTab IN OUT NOCOPY IBY_PAYGROUP_PUB.
1543 docsInPaymentTabType,
1544 x_ca_id IN OUT NOCOPY IBY_PAY_SERVICE_REQUESTS.
1545 calling_app_id%TYPE,
1546 x_ca_payreq_cd IN OUT NOCOPY IBY_PAY_SERVICE_REQUESTS.
1547 call_app_pay_service_req_code
1548 %TYPE,
1549 x_payReqCriteria IN OUT NOCOPY IBY_PAYGROUP_PUB.
1550 payReqImposedCriteria
1551 -- ,x_cbrTab IN OUT NOCOPY IBY_PAYGROUP_PUB.
1552 -- centralBankReportTabType
1553 );
1554
1558 |
1555 /*--------------------------------------------------------------------
1556 | NAME:
1557 | insertDocIntoPayment
1559 | PURPOSE:
1560 |
1561 |
1562 | PARAMETERS:
1563 | IN
1564 |
1565 |
1566 | OUT
1567 |
1568 |
1569 | RETURNS:
1570 |
1571 | NOTES:
1572 |
1573 *---------------------------------------------------------------------*/
1574 PROCEDURE insertDocIntoPayment(
1575 x_paymentRec IN OUT NOCOPY IBY_PAYMENTS_ALL%ROWTYPE,
1576 x_paymentTab IN OUT NOCOPY paymentTabType,
1577 p_calcDocInfo IN VARCHAR2,
1578 p_newPaymentFlag IN BOOLEAN,
1579 x_currentPaymentId IN OUT NOCOPY IBY_PAYMENTS_ALL.payment_id%TYPE,
1580 x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType,
1581 x_docsInPmtRec IN OUT NOCOPY docsInPaymentRecType,
1582 x_docsInPmtCount IN OUT NOCOPY NUMBER
1583 );
1584
1585 /*--------------------------------------------------------------------
1586 | NAME:
1587 | insertPayments
1588 |
1589 | PURPOSE:
1590 |
1591 |
1592 |
1593 | PARAMETERS:
1594 | IN
1595 |
1596 |
1597 | OUT
1598 |
1599 |
1600 | RETURNS:
1601 |
1602 | NOTES:
1603 |
1604 *---------------------------------------------------------------------*/
1605 PROCEDURE insertPayments(
1606 p_paymentTab IN paymentTabType
1607 );
1608
1609 /*--------------------------------------------------------------------
1610 | NAME:
1611 | updatePayments
1612 |
1613 | PURPOSE:
1614 | Performs an update of all created payments from PLSQL
1615 | table into IBY_PAYMENTS_ALL table.
1616 |
1617 | The created payments have already been inserted into
1618 | IBY_PAYMENTS_ALL after grouping. So we only need to update
1619 | certain fields of the payment that have been changed
1620 | after the grouping was performed.
1621 |
1622 | PARAMETERS:
1623 | IN
1624 |
1625 |
1626 | OUT
1627 |
1628 |
1629 | RETURNS:
1630 |
1631 | NOTES:
1632 |
1633 *---------------------------------------------------------------------*/
1634 PROCEDURE updatePayments(
1635 p_paymentTab IN paymentTabType
1636 );
1637
1638 /*--------------------------------------------------------------------
1639 | NAME:
1640 | getNextPaymentID
1641 |
1642 | PURPOSE:
1643 |
1644 |
1645 |
1646 | PARAMETERS:
1647 | IN
1648 |
1649 |
1650 | OUT
1651 |
1652 |
1653 | RETURNS:
1654 |
1655 | NOTES:
1656 |
1657 *---------------------------------------------------------------------*/
1658 PROCEDURE getNextPaymentID(
1659 x_paymentID IN OUT NOCOPY IBY_PAYMENTS_ALL.payment_id%TYPE
1660 );
1661
1662 /*--------------------------------------------------------------------
1663 | NAME:
1664 | updateDocsWithPaymentID
1665 |
1666 | PURPOSE:
1667 |
1668 |
1669 |
1670 | PARAMETERS:
1671 | IN
1672 |
1673 |
1674 | OUT
1675 |
1676 |
1677 | RETURNS:
1678 |
1679 | NOTES:
1680 |
1681 *---------------------------------------------------------------------*/
1682 PROCEDURE updateDocsWithPaymentID(
1683 p_docsInPmtTab IN docsInPaymentTabType
1684 );
1685
1686 /*--------------------------------------------------------------------
1687 | NAME:
1688 | performDBUpdates
1689 |
1690 | PURPOSE:
1691 | This is the top level method that is called by the
1692 | payment creation program to:
1693 | 1. insert payments to DB
1694 | 2. update documents with payment id
1695 | 3. update status of payment request
1696 |
1697 | This method will read the 'rejection level' system option
1698 | and do updates accordingly.
1699 |
1700 | PARAMETERS:
1701 | IN
1702 |
1703 |
1704 | OUT
1705 |
1706 |
1707 | RETURNS:
1708 |
1709 | NOTES:
1710 |
1711 *---------------------------------------------------------------------*/
1712 PROCEDURE performDBUpdates(
1713 p_payreq_id IN IBY_PAY_SERVICE_REQUESTS.
1714 payment_service_request_id%type,
1715 p_rej_level IN VARCHAR2,
1716 p_review_pmts_flag IN VARCHAR2,
1717 x_paymentTab IN OUT NOCOPY paymentTabType,
1718 x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType,
1719 x_allPmtsSuccessFlag IN OUT NOCOPY BOOLEAN,
1720 x_allPmtsFailedFlag IN OUT NOCOPY BOOLEAN,
1721 x_return_status IN OUT NOCOPY VARCHAR2,
1722 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1723 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
1724 trxnErrTokenTabType
1725 );
1726
1727 /*--------------------------------------------------------------------
1728 | NAME:
1729 | performCentralBankReporting
1730 |
1731 | PURPOSE:
1732 |
1733 |
1734 |
1735 | PARAMETERS:
1736 | IN
1737 |
1738 |
1739 | OUT
1740 |
1741 |
1745 |
1742 | RETURNS:
1743 |
1744 | NOTES:
1746 *---------------------------------------------------------------------*/
1747 PROCEDURE performCentralBankReporting(
1748 x_paymentTab IN OUT NOCOPY paymentTabType,
1749 x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType
1750 -- ,p_cbrTab IN centralBankReportTabType
1751 );
1752
1753 /*--------------------------------------------------------------------
1754 | NAME:
1755 | flagSeparateRemitAdvicePmts
1756 |
1757 | PURPOSE:
1758 |
1759 |
1760 |
1761 | PARAMETERS:
1762 | IN
1763 |
1764 |
1765 | OUT
1766 |
1767 |
1768 | RETURNS:
1769 |
1770 | NOTES:
1771 |
1772 *---------------------------------------------------------------------*/
1773 PROCEDURE flagSeparateRemitAdvicePmts(
1774 x_paymentTab IN OUT NOCOPY paymentTabType,
1775 p_docsInPmtTab IN docsInPaymentTabType
1776 );
1777
1778 /*--------------------------------------------------------------------
1779 | NAME:
1780 | getNumSuccessDocsPerPayment
1781 |
1782 | PURPOSE:
1783 |
1784 |
1785 |
1786 | PARAMETERS:
1787 | IN
1788 |
1789 |
1790 | OUT
1791 |
1792 |
1793 | RETURNS:
1794 |
1795 | NOTES:
1796 |
1797 *---------------------------------------------------------------------*/
1798 PROCEDURE getNumSuccessDocsPerPayment(
1799 x_paymentTab IN OUT NOCOPY paymentTabType,
1800 p_docsInPmtTab IN docsInPaymentTabType,
1801 x_successDocCountTab IN OUT NOCOPY successDocsCountPerPmtTab
1802 );
1803
1804 /*--------------------------------------------------------------------
1805 | NAME:
1806 | getFxAmount()
1807 |
1808 | PURPOSE:
1809 | Calls GL API to get converted amount in foreign currency.
1810 |
1811 |
1812 | PARAMETERS:
1813 | IN
1814 |
1815 |
1816 | OUT
1817 |
1818 |
1819 | RETURNS:
1820 |
1821 | NOTES:
1822 |
1823 *---------------------------------------------------------------------*/
1824 FUNCTION getFxAmount(
1825 p_source_currency IN VARCHAR2,
1826 p_target_currency IN VARCHAR2,
1827 p_exch_rate_date IN DATE,
1828 p_exch_rate_type IN VARCHAR2,
1829 p_source_amount IN NUMBER
1830 )
1831 RETURN NUMBER;
1832
1833 /*
1834 * This pragma is needed because the GL API enforces it.
1835 */
1836 PRAGMA RESTRICT_REFERENCES(getFxAmount, WNDS, WNPS, RNPS);
1837
1838 /*--------------------------------------------------------------------
1839 | NAME:
1840 | performPaymentValidations
1841 |
1842 | PURPOSE:
1843 |
1844 |
1845 |
1846 | PARAMETERS:
1847 | IN
1848 |
1849 |
1850 | OUT
1851 |
1852 |
1853 | RETURNS:
1854 |
1855 | NOTES:
1856 |
1857 *---------------------------------------------------------------------*/
1858 PROCEDURE performPaymentValidations(
1859 x_paymentTab IN OUT NOCOPY paymentTabType,
1860 x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType,
1861 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1862 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType
1863 );
1864
1865 /*--------------------------------------------------------------------
1866 | NAME:
1867 | performPreHookProcess
1868 |
1869 | PURPOSE:
1870 |
1871 |
1872 |
1873 | PARAMETERS:
1874 | IN
1875 |
1876 | OUT
1877 |
1878 |
1879 | RETURNS:
1880 |
1881 | NOTES:
1882 |
1883 *---------------------------------------------------------------------*/
1884 PROCEDURE performPreHookProcess(
1885 p_cap_payreq_cd IN VARCHAR2,
1886 p_cap_id IN NUMBER,
1887 x_paymentTab IN OUT NOCOPY paymentTabType,
1888 x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType,
1889 x_hookPaymentTab IN OUT NOCOPY hookPaymentTabType,
1890 x_hookDocsInPmtTab IN OUT NOCOPY hookDocsInPaymentTabType
1891 );
1892
1893 /*--------------------------------------------------------------------
1894 | NAME:
1895 | performPostHookProcess
1896 |
1897 | PURPOSE:
1898 |
1899 |
1900 |
1901 | PARAMETERS:
1902 | IN
1903 |
1904 | OUT
1905 |
1906 |
1907 | RETURNS:
1908 |
1909 | NOTES:
1910 |
1911 *---------------------------------------------------------------------*/
1912 PROCEDURE performPostHookProcess(
1913 x_paymentTab IN OUT NOCOPY paymentTabType,
1914 x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType,
1915 x_hookPaymentTab IN OUT NOCOPY hookPaymentTabType,
1916 x_hookDocsInPmtTab IN OUT NOCOPY hookDocsInPaymentTabType,
1917 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1918 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
1919 trxnErrTokenTabType
1923 | NAME:
1920 );
1921
1922 /*--------------------------------------------------------------------
1924 | adjustSisterDocsAndPmts
1925 |
1926 | PURPOSE:
1927 |
1928 |
1929 |
1930 | PARAMETERS:
1931 | IN
1932 |
1933 | OUT
1934 |
1935 |
1936 | RETURNS:
1937 |
1938 | NOTES:
1939 |
1940 *---------------------------------------------------------------------*/
1941 PROCEDURE adjustSisterDocsAndPmts(
1942 x_paymentTab IN OUT NOCOPY paymentTabType,
1943 x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType,
1944 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1945 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
1946 trxnErrTokenTabType
1947 );
1948
1949 /*--------------------------------------------------------------------
1950 | NAME:
1951 | handleJapaneseBankCharges
1952 |
1953 | PURPOSE:
1954 |
1955 |
1956 |
1957 | PARAMETERS:
1958 | IN
1959 |
1960 | OUT
1961 |
1962 |
1963 | RETURNS:
1964 |
1965 | NOTES:
1966 |
1967 *---------------------------------------------------------------------*/
1968 PROCEDURE handleJapaneseBankCharges(
1969 p_cap_payreq_cd IN VARCHAR2,
1970 p_cap_id IN NUMBER,
1971 x_paymentTab IN OUT NOCOPY paymentTabType,
1972 x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType,
1973 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1974 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
1975 trxnErrTokenTabType
1976 );
1977
1978 /*--------------------------------------------------------------------
1979 | NAME:
1980 | performExtendedWitholding
1981 |
1982 | PURPOSE:
1983 |
1984 |
1985 |
1986 | PARAMETERS:
1987 | IN
1988 |
1989 | OUT
1990 |
1991 |
1992 | RETURNS:
1993 |
1994 | NOTES:
1995 |
1996 *---------------------------------------------------------------------*/
1997 PROCEDURE performExtendedWitholding(
1998 p_cap_payreq_cd IN VARCHAR2,
1999 p_cap_id IN NUMBER,
2000 x_paymentTab IN OUT NOCOPY paymentTabType,
2001 x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType,
2002 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
2003 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
2004 trxnErrTokenTabType
2005 );
2006
2007 /*--------------------------------------------------------------------
2008 | NAME:
2009 | getDocDetails
2010 |
2011 | PURPOSE:
2012 |
2013 |
2014 | PARAMETERS:
2015 | IN
2016 |
2017 |
2018 | OUT
2019 |
2020 |
2021 | RETURNS:
2022 |
2023 | NOTES:
2024 |
2025 *---------------------------------------------------------------------*/
2026 FUNCTION getDocDetails(
2027 p_documentID In NUMBER,
2028 p_pmtDetailsFormula IN VARCHAR2
2029 )
2030 RETURN VARCHAR2;
2031
2032 /*--------------------------------------------------------------------
2033 | NAME:
2034 | callHook
2035 |
2036 | PURPOSE:
2037 |
2038 |
2039 |
2040 | PARAMETERS:
2041 | IN
2042 |
2043 | OUT
2044 |
2045 |
2046 | RETURNS:
2047 |
2048 | NOTES:
2049 |
2050 *---------------------------------------------------------------------*/
2051 PROCEDURE callHook(
2052 p_payreq_id IN IBY_PAY_SERVICE_REQUESTS.
2053 payment_service_request_id%type
2054 );
2055
2056 /*--------------------------------------------------------------------
2057 | NAME:
2058 | getAdjustedPaymentData
2059 |
2060 | PURPOSE:
2061 |
2062 |
2063 | PARAMETERS:
2064 | IN
2065 |
2066 | OUT
2067 |
2068 |
2069 | RETURNS:
2070 |
2071 | NOTES:
2072 |
2073 *---------------------------------------------------------------------*/
2074 PROCEDURE getAdjustedPaymentData(
2075 x_hookPaymentTab IN OUT NOCOPY hookPaymentTabType,
2076 x_hookDocsInPmtTab IN OUT NOCOPY hookDocsInPaymentTabType
2077 );
2078
2079 /*--------------------------------------------------------------------
2080 | NAME:
2081 | performPayReqBasedValidations
2082 |
2083 | PURPOSE:
2084 |
2085 |
2086 |
2087 | PARAMETERS:
2088 | IN
2089 |
2090 | OUT
2091 |
2092 |
2093 | RETURNS:
2094 |
2095 | NOTES:
2096 |
2097 *---------------------------------------------------------------------*/
2098 PROCEDURE performPayReqBasedValidations(
2099 p_payReqCriteria IN payReqImposedCriteria,
2100 x_paymentTab IN OUT NOCOPY paymentTabType,
2101 x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType,
2105
2102 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
2103 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType
2104 );
2106 /*--------------------------------------------------------------------
2107 | NAME:
2108 | performPmtGrpNumberValidation
2109 |
2110 | PURPOSE:
2111 |
2112 |
2113 |
2114 | PARAMETERS:
2115 | IN
2116 |
2117 | OUT
2118 |
2119 |
2120 | RETURNS:
2121 |
2122 | NOTES:
2123 |
2124 *---------------------------------------------------------------------*/
2125 PROCEDURE performPmtGrpNumberValidation(
2126 x_paymentTab IN OUT NOCOPY paymentTabType,
2127 x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType,
2128 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
2129 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType
2130 );
2131
2132 /*--------------------------------------------------------------------
2133 | NAME:
2134 | failDocsOfPayment
2135 |
2136 | PURPOSE:
2137 | For a given payment id, this method sets all the documents of
2138 | that payment to 'failed' status in the PLSQL table of documents.
2139 |
2140 | PARAMETERS:
2141 | IN
2142 |
2143 | OUT
2144 |
2145 |
2146 | RETURNS:
2147 |
2148 | NOTES:
2149 |
2150 *---------------------------------------------------------------------*/
2151 PROCEDURE failDocsOfPayment(
2152 p_paymentId IN IBY_PAYMENTS_ALL.payment_id%TYPE,
2153 p_docStatus IN IBY_DOCS_PAYABLE_ALL.
2154 document_status%TYPE,
2155 x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType,
2156 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
2157 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType
2158 );
2159
2160 /*--------------------------------------------------------------------
2161 | NAME:
2162 | applyPaymentValidationSets
2163 |
2164 | PURPOSE:
2165 |
2166 |
2167 |
2168 | PARAMETERS:
2169 | IN
2170 |
2171 | OUT
2172 |
2173 |
2174 | RETURNS:
2175 |
2176 | NOTES:
2177 |
2178 *---------------------------------------------------------------------*/
2179 PROCEDURE applyPaymentValidationSets(
2180 x_paymentTab IN OUT NOCOPY paymentTabType,
2181 x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType,
2182 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
2183 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType
2184 );
2185
2186 /*--------------------------------------------------------------------
2187 | NAME:
2188 | raiseBizEvents
2189 |
2190 | PURPOSE:
2191 |
2192 |
2193 |
2194 | PARAMETERS:
2195 | IN
2196 |
2197 | OUT
2198 |
2199 |
2200 | RETURNS:
2201 |
2202 | NOTES:
2203 |
2204 *---------------------------------------------------------------------*/
2205 PROCEDURE raiseBizEvents(
2206 p_payreq_id IN VARCHAR2,
2207 p_cap_payreq_cd IN VARCHAR2,
2208 p_cap_id IN NUMBER,
2209 p_rej_level IN VARCHAR2,
2210 p_review_pmts_flag IN VARCHAR2,
2211 p_allPmtsSuccessFlag IN BOOLEAN,
2212 p_allPmtsFailedFlag IN BOOLEAN
2213 );
2214
2215 /*--------------------------------------------------------------------
2216 | NAME:
2217 | getXMLClob
2218 |
2219 | PURPOSE:
2220 |
2221 |
2222 |
2223 | PARAMETERS:
2224 | IN
2225 |
2226 | OUT
2227 |
2228 |
2229 | RETURNS:
2230 |
2231 | NOTES:
2232 |
2233 *---------------------------------------------------------------------*/
2234 FUNCTION getXMLClob(
2235 p_payreq_id IN VARCHAR2
2236 )
2237 RETURN CLOB;
2238
2239 /*--------------------------------------------------------------------
2240 | NAME:
2241 | getRejectedDocs
2242 |
2243 | PURPOSE:
2244 | Performs a database query to get all failed documents which
2245 | are part of payments created for the given payment request.
2246 | These failed documents are put into data structure and
2247 | returned to the caller.
2248 |
2249 |
2250 | PARAMETERS:
2251 | IN
2252 |
2253 | OUT
2254 |
2255 |
2256 | RETURNS:
2257 |
2258 | NOTES:
2259 |
2260 |
2261 *---------------------------------------------------------------------*/
2262 PROCEDURE getRejectedDocs(
2263 p_payreq_id IN VARCHAR2,
2264 x_docIDTab IN OUT NOCOPY IBY_DISBURSE_UI_API_PUB_PKG.docPayIDTab,
2265 x_docStatusTab IN OUT NOCOPY IBY_DISBURSE_UI_API_PUB_PKG.docPayStatusTab
2266 );
2267
2268 /*--------------------------------------------------------------------
2269 | NAME:
2270 | checkIfPmtAlreadyFailed
2271 |
2272 | PURPOSE:
2273 |
2274 |
2275 |
2279 | OUT
2276 | PARAMETERS:
2277 | IN
2278 |
2280 |
2281 |
2282 | RETURNS:
2283 |
2284 | NOTES:
2285 |
2286 *---------------------------------------------------------------------*/
2287 FUNCTION checkIfPmtAlreadyFailed(
2288 p_paymentId IN IBY_PAYMENTS_ALL.payment_id%TYPE,
2289 p_paymentTab IN paymentTabType
2290 )
2291 RETURN BOOLEAN;
2292
2293 /*--------------------------------------------------------------------
2294 | NAME:
2295 | checkIfPmtAlreadyAdded
2296 |
2297 | PURPOSE:
2298 | Checks if a payment has already been added to the list of
2299 | negative amount payments that qualify for credit memo adjustment.
2300 | If yes, this method returns TRUE; else, it return FALSE.
2301 |
2302 | PARAMETERS:
2303 | IN
2304 |
2305 | OUT
2306 |
2307 |
2308 | RETURNS:
2309 |
2310 | NOTES:
2311 |
2312 *---------------------------------------------------------------------*/
2313 FUNCTION checkIfPmtAlreadyAdded(
2314 p_paymentId IN IBY_PAYMENTS_ALL.payment_id%TYPE,
2315 p_paymentTab IN pmtIdsTab
2316 )
2317 RETURN BOOLEAN;
2318
2319 /*--------------------------------------------------------------------
2320 | NAME:
2321 | performCreditMemoHandling
2322 |
2323 | PURPOSE:
2324 |
2325 |
2326 |
2327 | PARAMETERS:
2328 | IN
2329 |
2330 | OUT
2331 |
2332 |
2333 | RETURNS:
2334 |
2335 | NOTES:
2336 |
2337 *---------------------------------------------------------------------*/
2338 PROCEDURE performCreditMemoHandling(
2339 x_paymentTab IN OUT NOCOPY paymentTabType,
2340 x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType
2341 );
2342
2343 /*--------------------------------------------------------------------
2344 | NAME:
2345 | adjustCreditMemosWithinPmt
2346 |
2347 | PURPOSE:
2348 |
2349 |
2350 |
2351 | PARAMETERS:
2352 | IN
2353 |
2354 | OUT
2355 |
2356 |
2357 | RETURNS:
2358 |
2359 | NOTES:
2360 |
2361 *---------------------------------------------------------------------*/
2362 PROCEDURE adjustCreditMemosWithinPmt(
2363 p_qualifyingPmtsTab IN pmtIdsTab,
2364 x_paymentTab IN OUT NOCOPY paymentTabType,
2365 x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType
2366 );
2367
2368 /*--------------------------------------------------------------------
2369 | NAME:
2370 | performMaturityDateCalculation
2371 |
2372 | PURPOSE:
2373 |
2374 |
2375 |
2376 | PARAMETERS:
2377 | IN
2378 |
2379 | OUT
2380 |
2381 |
2382 | RETURNS:
2383 |
2384 | NOTES:
2385 |
2386 *---------------------------------------------------------------------*/
2387 PROCEDURE performMaturityDateCalculation(
2388 x_paymentTab IN OUT NOCOPY paymentTabType,
2389 p_docsInPmtTab IN docsInPaymentTabType
2390 );
2391
2392 /*--------------------------------------------------------------------
2393 | NAME:
2394 | loadPmtMethodMaturityDays
2395 |
2396 | PURPOSE:
2397 |
2398 |
2399 |
2400 | PARAMETERS:
2401 | IN
2402 |
2403 | OUT
2404 |
2405 |
2406 | RETURNS:
2407 |
2408 | NOTES:
2409 |
2410 *---------------------------------------------------------------------*/
2411 PROCEDURE loadPmtMethodMaturityDays(
2412 x_maturityDaysTab IN OUT NOCOPY pmtMethodMaturityDaysTab
2413 );
2414
2415 /*--------------------------------------------------------------------
2416 | NAME:
2417 | getMaturityDaysForPmtMethod
2418 |
2419 | PURPOSE:
2420 |
2421 |
2422 |
2423 | PARAMETERS:
2424 | IN
2425 |
2426 | OUT
2427 |
2428 |
2429 | RETURNS:
2430 |
2431 | NOTES:
2432 |
2433 *---------------------------------------------------------------------*/
2434 FUNCTION getMaturityDaysForPmtMethod(
2435 p_pmt_method_code IN IBY_PAYMENTS_ALL.payment_method_code%TYPE,
2436 p_maturityDaysTab IN pmtMethodMaturityDaysTab
2437 ) RETURN NUMBER;
2438
2439 /*--------------------------------------------------------------------
2440 | NAME:
2441 | refreshDocAndPmtAmounts
2442 |
2443 | PURPOSE:
2444 |
2445 |
2446 |
2447 | PARAMETERS:
2448 | IN
2449 |
2450 | OUT
2451 |
2452 |
2453 | RETURNS:
2454 |
2455 | NOTES:
2456 |
2457 *---------------------------------------------------------------------*/
2458 PROCEDURE refreshDocAndPmtAmounts(
2459 p_adjustedPmtId IN IBY_PAYMENTS_ALL.payment_id%TYPE,
2460 p_adjustedNegDocsTab IN docsInPaymentTabType,
2461 x_paymentTab IN OUT NOCOPY paymentTabType,
2462 x_docsInPmtTab IN OUT NOCOPY docsInPaymentTabType
2463 );
2464
2465 /*--------------------------------------------------------------------
2466 | NAME:
2467 | getListOfQualifyingNegPmts
2471 |
2468 |
2469 | PURPOSE:
2470 |
2472 |
2473 | PARAMETERS:
2474 | IN
2475 |
2476 | OUT
2477 |
2478 |
2479 | RETURNS:
2480 |
2481 | NOTES:
2482 |
2483 *---------------------------------------------------------------------*/
2484 PROCEDURE getListOfQualifyingNegPmts(
2485 x_negPmtsTab IN OUT NOCOPY pmtIdsTab,
2486 p_paymentTab IN paymentTabType,
2487 p_docsInPmtTab IN docsInPaymentTabType
2488 );
2489
2490 /*--------------------------------------------------------------------
2491 | NAME:
2492 | dummyGLAPI
2493 |
2494 | PURPOSE:
2495 |
2496 |
2497 |
2498 | PARAMETERS:
2499 | IN
2500 |
2501 | OUT
2502 |
2503 |
2504 | RETURNS:
2505 |
2506 | NOTES:
2507 |
2508 *---------------------------------------------------------------------*/
2509 PROCEDURE dummyGLAPI(
2510 p_exch_date IN DATE,
2511 p_source_amount IN NUMBER,
2512 p_source_curr IN VARCHAR2,
2513 p_decl_curr IN VARCHAR2,
2514 p_decl_fx_rate_type IN VARCHAR2,
2515 x_decl_amount OUT NOCOPY NUMBER);
2516
2517 /*--------------------------------------------------------------------
2518 | NAME:
2519 | dummy_paymentsAdjustHook
2520 |
2521 | PURPOSE:
2522 |
2523 |
2524 |
2525 | PARAMETERS:
2526 | IN
2527 |
2528 | OUT
2529 |
2530 |
2531 | RETURNS:
2532 |
2533 | NOTES:
2534 |
2535 *---------------------------------------------------------------------*/
2536 PROCEDURE dummy_paymentsAdjustHook(
2537 x_paymentTab IN OUT NOCOPY hookPaymentTabType,
2538 x_docsInPmtTab IN OUT NOCOPY hookDocsInPaymentTabType
2539 );
2540
2541 /*--------------------------------------------------------------------
2542 | NAME:
2543 | dummy_ruleFunction
2544 |
2545 | PURPOSE:
2546 | Dummy method; to be used for testing purposes.
2547 |
2548 |
2549 | PARAMETERS:
2550 | IN
2551 |
2552 | OUT
2553 |
2554 |
2555 | RETURNS:
2556 |
2557 | NOTES:
2558 |
2559 *---------------------------------------------------------------------*/
2560 FUNCTION dummy_ruleFunction(
2561 p_subscription IN RAW,
2562 p_event IN OUT NOCOPY WF_EVENT_T
2563 )
2564 RETURN VARCHAR2;
2565
2566 /*--------------------------------------------------------------------
2567 | NAME:
2568 | printDocsInPmtTab
2569 |
2570 | PURPOSE:
2571 |
2572 |
2573 |
2574 | PARAMETERS:
2575 | IN
2576 |
2577 | OUT
2581 |
2578 |
2579 |
2580 | RETURNS:
2582 | NOTES:
2583 |
2584 *---------------------------------------------------------------------*/
2585 PROCEDURE printDocsInPmtTab(
2586 p_docsInPmtTab IN docsInPaymentTabType
2587 );
2588
2589 /*--------------------------------------------------------------------
2590 | NAME:
2591 | getPmtRejLevelSysOption
2592 |
2593 | PURPOSE:
2594 | Gets the payment rejection level system option.
2595 |
2596 | The handling of payment validation failures is dependent
2597 | upon the rejection level setting.
2598 |
2599 | PARAMETERS:
2600 | IN
2601 |
2602 |
2603 | OUT
2604 |
2605 |
2606 | RETURNS:
2607 |
2608 | NOTES:
2609 |
2610 *---------------------------------------------------------------------*/
2611 FUNCTION getPmtRejLevelSysOption RETURN VARCHAR2;
2612
2613 /*--------------------------------------------------------------------
2614 | NAME:
2615 | getReviewPmtsSysOption
2616 |
2617 | PURPOSE:
2618 | Gets the review payment flag system option.
2619 |
2620 | PARAMETERS:
2621 | IN
2622 |
2623 |
2624 | OUT
2625 |
2626 |
2627 | RETURNS:
2628 |
2629 | NOTES:
2630 |
2631 *---------------------------------------------------------------------*/
2632 FUNCTION getReviewPmtsSysOption RETURN VARCHAR2;
2633
2634 /*--------------------------------------------------------------------
2635 | NAME:
2636 | populateLEsOnPmts
2637 |
2638 | PURPOSE:
2639 |
2640 |
2641 | PARAMETERS:
2642 | IN
2643 |
2644 |
2645 | OUT
2646 |
2647 |
2648 | RETURNS:
2649 |
2650 | NOTES:
2651 |
2652 *---------------------------------------------------------------------*/
2653 PROCEDURE populateLEsOnPmts(
2654 x_paymentTab IN OUT NOCOPY paymentTabType,
2655 p_bankAccountLEs IN bankAccountLETabType
2656 );
2657
2658 /*--------------------------------------------------------------------
2659 | NAME:
2660 | initializePmts
2661 |
2662 | PURPOSE:
2663 |
2664 |
2665 | PARAMETERS:
2666 | IN
2667 |
2668 |
2669 | OUT
2670 |
2671 |
2672 | RETURNS:
2673 |
2674 | NOTES:
2675 |
2676 *---------------------------------------------------------------------*/
2677 PROCEDURE initializePmts(
2678 x_paymentTab IN OUT NOCOPY paymentTabType
2679 );
2680
2681 /*--------------------------------------------------------------------
2682 | NAME:
2683 | get_payreq_status
2684 |
2685 |
2686 | PURPOSE:
2687 |
2688 |
2689 | PARAMETERS:
2690 | IN
2691 |
2692 |
2693 | OUT
2694 |
2695 |
2696 | RETURNS:
2697 |
2698 | NOTES:
2702 l_payreq_id IN IBY_PAY_SERVICE_REQUESTS.payment_service_request_id%TYPE)
2699 |
2700 *---------------------------------------------------------------------*/
2701 FUNCTION get_payreq_status (
2703 RETURN VARCHAR2;
2704
2705 /*--------------------------------------------------------------------
2706 | NAME:
2707 | rebuildPayments
2708 |
2709 | PURPOSE:
2710 |
2711 |
2712 |
2713 | PARAMETERS:
2714 | IN
2715 |
2716 |
2717 | OUT
2718 |
2719 |
2720 | RETURNS:
2721 |
2722 | NOTES:
2723 |
2724 *---------------------------------------------------------------------*/
2725 PROCEDURE rebuildPayments(
2726 p_payment_request_id IN IBY_PAY_SERVICE_REQUESTS.
2727 payment_service_request_id%TYPE,
2728 x_paymentTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
2729 x_docsInPmtTab IN OUT NOCOPY IBY_PAYGROUP_PUB.
2730 docsInPaymentTabType,
2731 x_ca_id IN OUT NOCOPY IBY_PAY_SERVICE_REQUESTS.
2732 calling_app_id%TYPE,
2733 x_ca_payreq_cd IN OUT NOCOPY IBY_PAY_SERVICE_REQUESTS.
2734 call_app_pay_service_req_code
2735 %TYPE,
2736 x_payReqCriteria IN OUT NOCOPY IBY_PAYGROUP_PUB.
2737 payReqImposedCriteria
2738 -- ,x_cbrTab IN OUT NOCOPY IBY_PAYGROUP_PUB.
2739 -- centralBankReportTabType
2740 );
2741
2742 /*--------------------------------------------------------------------
2743 | NAME:
2744 | auditPaymentData
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 auditPaymentData(
2763 p_paymentTab IN paymentTabType
2764 );
2765
2766 /*--------------------------------------------------------------------
2767 | NAME:
2768 | insertAuditData
2769 |
2770 | PURPOSE:
2771 |
2772 |
2773 | PARAMETERS:
2774 | IN
2775 |
2776 |
2777 | OUT
2778 |
2779 |
2780 | RETURNS:
2781 |
2782 | NOTES:
2783 |
2784 *---------------------------------------------------------------------*/
2785 PROCEDURE insertAuditData(
2786 p_auditPmtTab IN paymentAuditTabType
2787 );
2788
2789 /*--------------------------------------------------------------------
2790 | NAME:
2791 | sweepCommonPmtAttributes
2792 |
2793 | PURPOSE:
2794 |
2795 |
2796 | PARAMETERS:
2797 | IN
2798 |
2799 |
2800 | OUT
2801 |
2802 |
2803 | RETURNS:
2804 |
2805 | NOTES:
2806 |
2807 *---------------------------------------------------------------------*/
2808 PROCEDURE sweepCommonPmtAttributes (
2809 x_paymentTab IN OUT NOCOPY IBY_PAYGROUP_PUB.paymentTabType,
2810 x_docsInPmtTab IN OUT NOCOPY IBY_PAYGROUP_PUB.docsInPaymentTabType
2811 );
2812
2813
2814 END IBY_PAYGROUP_PUB;
[Home] [Help]