[Home] [Help]
PACKAGE BODY: APPS.IBY_PAYINSTR_PUB
Source
1 PACKAGE BODY IBY_PAYINSTR_PUB AS
2 /*$Header: ibypymib.pls 120.82.12010000.8 2009/02/11 11:22:24 visundar ship $*/
3
4 --
5 -- Declare global variables
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_PAYINSTR_PUB';
8
9 --
10 -- List of instruction statuses that are used / set in this
11 -- module (payment instruction creation flow).
12 --
13 INS_STATUS_CREATED CONSTANT VARCHAR2(100) := 'CREATED';
14 INS_STATUS_CREAT_ERROR CONSTANT VARCHAR2(100) := 'CREATION_ERROR';
15
16 --
17 -- List of request statuses that are used / set in this
18 -- module (payment instruction creation flow).
19 --
20 REQ_STATUS_PMTS_CREATED CONSTANT VARCHAR2(100) := 'PAYMENTS_CREATED';
21
22 --
23 -- List of payment statuses that are used / set in this
24 -- module (payment instruction creation flow).
25 --
26 PMT_STATUS_CREATED CONSTANT VARCHAR2(100) := 'CREATED';
27 PMT_STATUS_INSTR_VAL_FAIL CONSTANT VARCHAR2(100) := 'INSTRUCTION_FAILED_VAL';
28 PMT_STATUS_INSTR_CREATED CONSTANT VARCHAR2(100) := 'INSTRUCTION_CREATED';
29
30 --
31 -- List of document statuses that are used / set in this
32 -- module (payment instruction creation flow).
33 --
34 DOC_STATUS_PAY_CREATED CONSTANT VARCHAR2(100) := 'PAYMENT_CREATED';
35
36 -- Transaction types (for inserting into IBY_TRANSACTION_ERRORS table)
37 TRXN_TYPE_INSTR CONSTANT VARCHAR2(100) := 'PAYMENT_INSTRUCTION';
38
39 --
40 -- List of valid processing types on the payment profile.
41 --
42 P_TYPE_PRINTED CONSTANT VARCHAR2(100) := 'PRINTED';
43 P_TYPE_ELECTRONIC CONSTANT VARCHAR2(100) := 'ELECTRONIC';
44
45 -- Object types (for inserting into user access tables)
46 OBJECT_TYPE_INSTR CONSTANT VARCHAR2(100) := 'PAYMENT_INSTRUCTION';
47
48 --
49 -- Forward declarations
50 --
51 PROCEDURE print_debuginfo(
52 p_module IN VARCHAR2,
53 p_debug_text IN VARCHAR2,
54 p_debug_level IN VARCHAR2 DEFAULT FND_LOG.LEVEL_STATEMENT
55 );
56
57 /*--------------------------------------------------------------------
58 | NAME:
59 | createPaymentInstructions
60 |
61 | PURPOSE:
62 |
63 |
64 | PARAMETERS:
65 | IN
66 |
67 |
68 | OUT
69 |
70 |
71 | RETURNS:
72 |
73 | NOTES:
74 |
75 *---------------------------------------------------------------------*/
76 PROCEDURE createPaymentInstructions(
77
78 /*-- processing criteria --*/
79 p_processing_type IN IBY_PAYMENT_PROFILES.
80 processing_type%TYPE,
81 p_pmt_document_id IN CE_PAYMENT_DOCUMENTS.
82 payment_document_id%TYPE,
83 p_printer_name IN VARCHAR2,
84 p_print_now_flag IN VARCHAR2,
85 p_transmit_now_flag IN VARCHAR2,
86
87 /*-- user/admin assigned criteria --*/
88 p_admin_assigned_ref IN IBY_PAY_INSTRUCTIONS_ALL.
89 pay_admin_assigned_ref_code%TYPE,
90 p_comments IN IBY_PAY_INSTRUCTIONS_ALL.
91 comments%TYPE,
92
93 /*-- selection criteria --*/
94 p_payment_profile_id IN IBY_PAYMENTS_ALL.
95 payment_profile_id%TYPE,
96 p_calling_app_id IN IBY_PAY_SERVICE_REQUESTS.
97 calling_app_id%TYPE,
98 p_calling_app_payreq_cd IN IBY_PAY_SERVICE_REQUESTS.
99 call_app_pay_service_req_code
100 %TYPE,
101 p_payreq_id IN IBY_PAY_SERVICE_REQUESTS.
102 payment_service_request_id
103 %TYPE,
104 p_internal_bank_account_id IN IBY_PAYMENTS_ALL.
105 internal_bank_account_id%TYPE,
106 p_payment_currency IN IBY_PAYMENTS_ALL.
107 payment_currency_code%TYPE,
108 p_le_id IN IBY_PAYMENTS_ALL.
109 legal_entity_id%TYPE,
110 p_org_id IN IBY_PAYMENTS_ALL.org_id%TYPE,
111 p_org_type IN IBY_PAYMENTS_ALL.org_type%TYPE,
112 p_payment_from_date IN IBY_PAYMENTS_ALL.payment_date%TYPE,
113 p_payment_to_date IN IBY_PAYMENTS_ALL.payment_date%TYPE,
114
115 /*-- single payments / batch flow identifier --*/
116 p_single_pmt_flow_flag IN VARCHAR2 DEFAULT 'N',
117
118 /*-- out params --*/
119 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
120 x_return_status IN OUT NOCOPY VARCHAR2,
121 x_msg_count OUT NOCOPY NUMBER,
122 x_msg_data OUT NOCOPY VARCHAR2
123 )
124 IS
125
126 l_module_name VARCHAR2(200) := G_PKG_NAME
127 || '.createPaymentInstructions';
128 l_first_record VARCHAR2(1) := 'Y';
129 l_instr_id NUMBER(15) := 0;
130 l_pmt_date_flag VARCHAR2(1) := 'N';
131 l_pmts_in_instr_count NUMBER(15) := 0;
132 l_instr_amount NUMBER(15) := -1;
133 l_pmt_fx_amount NUMBER(15) := -1;
134 l_pmt_details_length NUMBER(15) := -1;
135 l_pmt_function_flag VARCHAR2(1) := 'N';
136 l_pmt_reason_flag VARCHAR2(1) := 'N';
137 l_pmt_curr_flag VARCHAR2(1) := 'N';
138 l_int_bank_acct_flag VARCHAR2(1) := 'N';
139 l_max_pmts_flag VARCHAR2(1) := 'N';
140 l_max_pmts_limit NUMBER(15) := 0;
141 l_payment_curr_flag VARCHAR2(1) := 'N';
142 l_le_flag VARCHAR2(1) := 'N';
143 l_org_flag VARCHAR2(1) := 'N';
144 l_max_amount_flag VARCHAR2(1) := 'N';
145 l_max_amount_limit NUMBER(15) := 0;
146 l_max_amount_curr VARCHAR2(10) := '';
147 l_exchg_rate_type VARCHAR2(30) := '';
148 l_rfc_flag VARCHAR2(1) := 'N';
149 l_pmt_method_flag VARCHAR2(1) :='N';
150 l_ppr_flag VARCHAR2(1) := 'N';
151
152 /*
153 * These are used in storing distinct pmt functions and orgs of
154 * a payment instruction into schema tables. This information
155 * is used by the UI in restricting user access.
156 */
157 l_pmtFxAccessTypesTab distinctPmtFxAccessTab;
158 l_orgAccessTypesTab distinctOrgAccessTab;
159
160 /* promissory note flag setting on instruction creation rules */
161 l_prom_note_flag VARCHAR2(1) := 'N';
162
163 l_ca_id NUMBER(15) := 0;
164
165 /*
166 * These two are related data structures. Each row in instrTabType
167 * PLSQL table is used in inserting a row into the IBY_PAY_INSTRUCTIONS_ALL
168 * table.
169 *
170 * Since the IBY_PAY_INSTRUCTIONS_ALL table does not contain a payment id,
171 * a separate data structure is needed to keep track of the payments
172 * that are part of a payment instruction. This information is tracked
173 * in the pmtsInInstrTabType table. The rows in pmtsInInstrTabType are
174 * used to update the rows in IBY_PAYMENTS_ALL table with payment instruction
175 * ids.
176 *
177 * l_instrTab l_pmtsInInstrTab
178 * (insert into IBY_PAY_INSTRUCTIONS_ALL) (update IBY_PAYMENTS_ALL)
179 * /--------------------------------------\ /-------------\
180 * |Payment |Payment|..|Instr |Payment|..| |Payment |Pmt |
181 * |Instr Id|Profile|..|Status |Count |..| |Instr Id|Id |
182 * | |Id |..| | |..| | | |
183 * |--------------------------------------| |-------------|
184 * | 4000 | 10| |CREATED| 3| | | 4000 | 501|
185 * | | | | | | | | 4000 | 504|
186 * | | | | | | | | 4000 | 505|
187 * |--------|-------|--|-------|-------|--| |--------|----|
188 * | 4001 | 12| |CREATED| 19| | | 4001 | 502|
189 * | | | | | | | | 4001 | 509|
190 * | | | | | | | | 4001 | 511|
191 * | | | | | | | | 4001 | 523|
192 * | | | | | | | | : | : |
193 * |--------|-------|--|-------|-------|--| |--------|----|
194 * | : | : | | : | : | | | : | : |
195 * \________|_______|__|_______|_______|__/ \________|____/
196 *
197 * Combining these two structures into one structure is messy
198 * because you cannot directly use the combined data structure for
199 * bulk updates.
200 */
201
202 l_pmtInstrRec IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE;
203 l_pmtInstrTab pmtInstrTabType;
204
205 l_pmtsInInstrRec pmtsInPmtInstrRecType;
206 l_pmtsInInstrTab pmtsInPmtInstrTabType;
207
208 l_instrGrpCriTab instrGroupCriteriaTabType;
209
210 /* holds the error messages against failed instructions */
211 l_docErrorTab IBY_VALIDATIONSETS_PUB.docErrorTabType;
212 l_errTokenTab IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType;
213
214 /* previous payment attributes */
215 prev_ca_id iby_pay_service_requests.calling_app_id%TYPE;
216 prev_pmt_id iby_payments_all.payment_id%TYPE;
217 prev_pmt_currency iby_payments_all.payment_currency_code%TYPE;
218 prev_pmt_amount iby_payments_all.payment_amount%TYPE;
219 prev_int_bank_acct_id iby_payments_all.
220 internal_bank_account_id%TYPE;
221 prev_org_id iby_payments_all.org_id%TYPE;
222 prev_org_type iby_payments_all.org_type%TYPE;
223 prev_le_id iby_payments_all.legal_entity_id%TYPE;
224 prev_profile_id iby_payments_all.payment_profile_id%TYPE;
225 prev_payment_date iby_payments_all.payment_date%TYPE;
226 prev_pmt_function iby_payments_all.payment_function%TYPE;
227 prev_pmt_reason_code iby_payments_all.payment_reason_code%TYPE;
228 prev_pmt_reason_commt iby_payments_all.payment_reason_comments%TYPE;
229 prev_prom_note_flag iby_payments_all.bill_payable_flag%TYPE;
230 prev_rfc_identifier hz_code_assignments.class_code%TYPE;
231 prev_pmt_method_code iby_payments_all.payment_method_code%TYPE;
232 prev_ppr_id iby_payments_all.payment_service_request_id%TYPE;
233
234 /* current payment attributes */
235 curr_ca_id iby_pay_service_requests.calling_app_id%TYPE;
236 curr_pmt_id iby_payments_all.payment_id%TYPE;
237 curr_pmt_currency iby_payments_all.payment_currency_code%TYPE;
238 curr_pmt_amount iby_payments_all.payment_amount%TYPE;
239 curr_int_bank_acct_id iby_payments_all.
240 internal_bank_account_id%TYPE;
241 curr_org_id iby_payments_all.org_id%TYPE;
242 curr_org_type iby_payments_all.org_type%TYPE;
243 curr_le_id iby_payments_all.legal_entity_id%TYPE;
244 curr_profile_id iby_payments_all.payment_profile_id%TYPE;
245 curr_payment_date iby_payments_all.payment_date%TYPE;
246 curr_pmt_function iby_payments_all.payment_function%TYPE;
247 curr_pmt_reason_code iby_payments_all.payment_reason_code%TYPE;
248 curr_pmt_reason_commt iby_payments_all.payment_reason_comments%TYPE;
249 curr_prom_note_flag iby_payments_all.bill_payable_flag%TYPE;
250 curr_rfc_identifier hz_code_assignments.class_code%TYPE;
251 curr_pmt_method_code iby_payments_all.payment_method_code%TYPE;
252 curr_ppr_id iby_payments_all.payment_service_request_id%TYPE;
253
254 l_sql_chunk VARCHAR2(3000);
255 l_cursor_stmt VARCHAR2(8000);
256
257 TYPE dyn_payments IS REF CURSOR;
258 l_pmts_cursor dyn_payments;
259
260 /* maps profile ids to system profile codes */
261 l_profile_map IBY_BUILD_UTILS_PKG.profileIdToCodeMapTabType;
262
263 l_date_pattern VARCHAR2(100) := 'YYYY/MM/DD HH24:MI:SS';
264
265 BEGIN
266 print_debuginfo(l_module_name, 'ENTER');
267
268 print_debuginfo(l_module_name, 'Calling app id: '
269 || NVL(TO_CHAR(p_calling_app_id), '<not provided>'));
270 print_debuginfo(l_module_name, 'Calling app payment request cd: '
271 || NVL(p_calling_app_payreq_cd, '<not provided>'));
272
273 print_debuginfo(l_module_name, 'Processing type: '
274 || NVL(p_processing_type, '<not provided>'));
275
276 print_debuginfo(l_module_name, 'Single payments flow flag: '
277 || NVL(p_single_pmt_flow_flag, '<not provided>'));
278
279 /*
280 * 'Processing Type' is a mandatory parameter. Validate
281 * that it is correctly provided. Valid values are
282 * 'PRINTED', 'ELECTRONIC'.
283 */
284 IF (p_processing_type IS NULL OR (p_processing_type <> P_TYPE_ELECTRONIC
285 AND p_processing_type <> P_TYPE_PRINTED)) THEN
286
287 print_debuginfo(l_module_name, 'Provided processing type: '
288 || p_processing_type
289 || ' is invalid. Aborting program ..',
290 FND_LOG.LEVEL_UNEXPECTED
291 );
292
293 FND_MESSAGE.SET_NAME('IBY', 'IBY_INS_MISSING_PROCESS_TYPE');
294 FND_MSG_PUB.ADD;
295
296 FND_MSG_PUB.COUNT_AND_GET(
297 p_count => x_msg_count,
298 p_data => x_msg_data
299 );
300
301 APP_EXCEPTION.RAISE_EXCEPTION;
302
303 END IF;
304
305
306 /* Org type must be provided if org id is provided */
307 IF (p_org_id IS NOT NULL and p_org_type IS NULL) THEN
308
309 print_debuginfo(l_module_name, 'Invalid selection '
310 || 'criteria provided; org id has been provided '
311 || 'but org type has not been provided.',
312 FND_LOG.LEVEL_UNEXPECTED
313 );
314
315 print_debuginfo(l_module_name, 'Payment instruction '
316 || 'creation will not proceed.',
317 FND_LOG.LEVEL_UNEXPECTED
318 );
319
320 FND_MESSAGE.SET_NAME('IBY', 'IBY_INS_MISSING_ORG_TYPE');
321 FND_MSG_PUB.ADD;
322
323 FND_MSG_PUB.COUNT_AND_GET(
324 p_count => x_msg_count,
325 p_data => x_msg_data
326 );
327
328 APP_EXCEPTION.RAISE_EXCEPTION;
329
330 END IF;
331
332
333 /*
334 * If processing type is 'PRINTED', then the following are mandatory:
335 * a. Payment document
336 * b. Print now flag
337 * c. Payment currency
338 */
339 IF (p_processing_type = P_TYPE_PRINTED) THEN
340
341 /* Payment document */
342 IF (p_pmt_document_id IS NULL) THEN
343
344 print_debuginfo(l_module_name, 'Payment document id is '
345 || 'mandatory for printed processing type. '
346 || 'Insufficient data. Aborting program ..',
347 FND_LOG.LEVEL_UNEXPECTED
348 );
349
350 FND_MESSAGE.SET_NAME('IBY', 'IBY_INS_MISSING_PMT_DOCUMENT');
351 FND_MSG_PUB.ADD;
352
353 FND_MSG_PUB.COUNT_AND_GET(
354 p_count => x_msg_count,
355 p_data => x_msg_data
356 );
357
358 APP_EXCEPTION.RAISE_EXCEPTION;
359
360 END IF;
361
362 /* Print now flag */
363 IF (p_print_now_flag IS NULL) THEN
364
365 print_debuginfo(l_module_name, 'Print immediate flag is '
366 || 'mandatory for printed processing type. '
367 || 'Insufficient data. Aborting program ..',
368 FND_LOG.LEVEL_UNEXPECTED
369 );
370
371 FND_MESSAGE.SET_NAME('IBY', 'IBY_INS_MISSING_PRINT_NOW_FLAG');
372 FND_MSG_PUB.ADD;
373
374 FND_MSG_PUB.COUNT_AND_GET(
375 p_count => x_msg_count,
376 p_data => x_msg_data
377 );
378
379 APP_EXCEPTION.RAISE_EXCEPTION;
380
381 ELSE
382
383 IF (UPPER(p_print_now_flag) = 'Y' AND
384 p_printer_name IS NULL) THEN
385
386 print_debuginfo(l_module_name, 'Printer name is '
387 || 'mandatory if print immediate flag is '
388 || 'set to "Y". Aborting program ..',
389 FND_LOG.LEVEL_UNEXPECTED
390 );
391
392 FND_MESSAGE.SET_NAME('IBY', 'IBY_INS_MISSING_PRINTER');
393 FND_MSG_PUB.ADD;
394
395 FND_MSG_PUB.COUNT_AND_GET(
396 p_count => x_msg_count,
397 p_data => x_msg_data
398 );
399
400 APP_EXCEPTION.RAISE_EXCEPTION;
401
402 END IF;
403
404 END IF;
405
406 /* Payment currency */
407 /*
408 * Do not validate for payment currency here.
409 * As per latest discussion with Omar and Lauren
410 * it is the responsibility of the iPayment UI
411 * to ensure that 'grouping by payment currency'
412 * is enforced when the processing type for a
413 * profile is PRINTED.
414 *
415 * This will mean that for any profile with processing
416 * type printed, grouping by payment currency will be
417 * triggered. Thus, the payment instructions created for
418 * profiles with processing type PRINTED will consist of
419 * payments of a single currency only; allowing these
420 * payment instructions to be printed on paper stock.
421 *
422 * In the PICP, 'grouping by payment currency' will
423 * continue to remain a user defined grouping rule, but
424 * the UI will enforce that is is always turned on for
425 * profiles of printed type.
426 */
427 /*------------------------------------------------------
428 IF (p_payment_currency IS NULL) THEN
429
430 print_debuginfo(l_module_name, 'Payment currency is '
431 || 'mandatory for printed processing type. '
432 || 'Insufficient data. Aborting program ..'
433 );
434
435 FND_MESSAGE.SET_NAME('IBY', 'IBY_INS_MISSING_PMT_CURRENCY');
436 FND_MSG_PUB.ADD;
437
438 FND_MSG_PUB.COUNT_AND_GET(
439 p_count => x_msg_count,
440 p_data => x_msg_data
441 );
442
443 APP_EXCEPTION.RAISE_EXCEPTION;
444
445 END IF;
446 ---------------------------------------------------------*/
447
448 END IF;
449
450 /*
451 * If processing type is 'ELECTRONIC', then the following are mandatory:
452 * a. Transmit now flag
453 */
454 IF (p_processing_type = P_TYPE_ELECTRONIC) THEN
455
456 IF (p_transmit_now_flag IS NULL) THEN
457
458 print_debuginfo(l_module_name, 'Transmit now flag is '
459 || 'mandatory for electronic processing type. '
460 || 'Insufficient data. Aborting program ..',
461 FND_LOG.LEVEL_UNEXPECTED
462 );
463
464 FND_MESSAGE.SET_NAME('IBY', 'IBY_INS_MISSING_TRANSMIT_NOW_FLAG');
465 FND_MSG_PUB.ADD;
466
467 FND_MSG_PUB.COUNT_AND_GET(
468 p_count => x_msg_count,
469 p_data => x_msg_data
470 );
471
472 APP_EXCEPTION.RAISE_EXCEPTION;
473
474 END IF;
475
476 END IF;
477
478 /*
479 * Build up the WHERE clause (of the SQL statement to pick up the
480 * available payments) in chunks; add only non-null selection
481 * parameters into the WHERE clause.
482 */
483 IF (p_calling_app_id IS NOT NULL) THEN
484 l_sql_chunk := l_sql_chunk
485 || ' AND prq.calling_app_id = '
486 || p_calling_app_id
487 ;
488 END IF;
489
490 IF (p_payreq_id IS NOT NULL) THEN
491 l_sql_chunk := l_sql_chunk
492 || ' AND prq.payment_service_request_id = '
493 || p_payreq_id
494 ;
495 END IF;
496
497 /*
498 * Build up payment date shunks of the form -
499 * TO_DATE('29-MAR-06', 'YYYY/MM/DD HH24:MI:SS')
500 */
501 IF (p_payment_from_date IS NOT NULL) THEN
502 l_sql_chunk := l_sql_chunk
503 || ' AND pmts.payment_date >= '
504 || 'TO_DATE('
505 || ''''
506 || p_payment_from_date
507 || ''''
508 || ', '
509 || ''''
510 || l_date_pattern
511 || ''''
512 || ')'
513 ;
514 END IF;
515
516 IF (p_payment_to_date IS NOT NULL) THEN
517 l_sql_chunk := l_sql_chunk
518 || ' AND pmts.payment_date <= '
519 || 'TO_DATE('
520 || ''''
521 || p_payment_to_date
522 || ''''
523 || ', '
524 || ''''
525 || l_date_pattern
526 || ''''
527 || ')'
528 ;
529 END IF;
530
531 IF (p_internal_bank_account_id IS NOT NULL) THEN
532 l_sql_chunk := l_sql_chunk
533 || ' AND pmts.internal_bank_account_id = '
534 || p_internal_bank_account_id;
535 END IF;
536
537 IF (p_payment_profile_id IS NOT NULL) THEN
538 l_sql_chunk := l_sql_chunk
539 || ' AND pmts.payment_profile_id = '
540 || p_payment_profile_id
541 ;
542 END IF;
543
544 IF (p_payment_currency IS NOT NULL) THEN
545 l_sql_chunk := l_sql_chunk
546 || ' AND pmts.payment_currency_code = '
547 || ''''
548 || p_payment_currency
549 || ''''
550 ;
551 END IF;
552
553 /*
554 * Org type and org id should always be given
555 * together as a pair.
556 */
557 IF (p_org_id IS NOT NULL AND p_org_type IS NOT NULL) THEN
558 l_sql_chunk := l_sql_chunk
559 || ' AND pmts.org_id = '
560 || p_org_id
561 || ' AND pmts.org_type = '
562 || ''''
563 || p_org_type
564 || ''''
565 ;
566
567 END IF;
568
569 IF (p_le_id IS NOT NULL) THEN
570 l_sql_chunk := l_sql_chunk
571 || ' AND pmts.legal_entity_id = '
572 || p_le_id
573 ;
574 END IF;
575
576 /* log the created chunk */
577 IF (l_sql_chunk IS NULL) THEN
578 print_debuginfo(l_module_name, 'l_sql_chunk is null. '
579 || 'All available payments will be picked up '
580 || 'for payment instruction creation.'
581 );
582 ELSE
583
584 /*
585 * Add a trailing space to the SQL chunk. This will
586 * ensure that there is no SQL typo caused by any
587 * subsequent appent to this chunk.
588 */
589 l_sql_chunk := l_sql_chunk || ' ';
590
591 print_debuginfo(l_module_name, 'l_sql_chunk: '
592 || l_sql_chunk);
593 END IF;
594
595 /*
596 * Cursor to pick up payment requests that match provided
597 * selection criteria:
598 *
599 * Some of these criteria can be null, as they are optional.
600 * In such a case, do not put that criteria in the WHERE
601 * clause (because then you will not get any payments).
602 * Instead, build up the SQL statement in chunks using
603 * only the non-null selection parameters, and use this
604 * chunk in the WHERE clause.
605 *
606 * We want to pick up all payments that match the provided
607 * selection criteria. If all selection criteria are set
608 * to 'null', it means that user wants to select all pending
609 * payments.
610 *
611 * Because the WHERE clause is dynamic, we have to use
612 * a dynamic cursor (REF CURSOR).
613 */
614
615 /*
616 * Important Technical Note:
617 * -------------------------
618 * This SELECT statement uses SKIP LOCKED syntax.
619 * This is an undocumented feature of Oracle, that
620 * will only select unlocked rows.
621 *
622 * When there are multiple instances of the payment
623 * instruction program running concurrently, we need to
624 * make sure that no two instances are operating on the
625 * same rows. So we lock the rows that each instance
626 * picks up by using SELECT .. FOR UPDATE syntax.
627 *
628 * Now, suppose the second instance of the payment instruction
629 * creation program is invoked concurrently, it will also
630 * attempt to pick up all rows that match the provided
631 * selection criteria. If even one row in the selection
632 * maps to a row that has already been picked up by the
633 * first instance of the payment instruction creation
634 * program, then the second instance has to wait till the
635 * first instance completes (because the rows were locked).
636 *
637 * We want the second instance to only pick up the rows that
638 * were not already selected by the first instance (i.e.,
639 * pick up only unlocked rows). This is accomplished by the
640 * SELECT .. FOR UPDATE SKIP LOCKED syntax.
641 *
642 * With this approach, it is possible to have multiple
643 * instances of the payment instruction creation running
644 * concurrently and each operating on it's own data.
645 *
646 * Though the SKIP LOCKED feature is undocumented, we have
647 * received permission from the performance team to
648 * use it.
649 */
650
651 /*
652 * Note I:
653 * Since this is a 'select for update', we cannot select
654 * from views. We have to use the underlying base tables
655 * instead. That's why we select from the base tables
656 * for the payment profile.
657 */
658
659 /*
660 * Note II:
661 * Debugging this select can be tricky. Sometimes the
662 * select will return no rows even when the matching rows
663 * are present in the table; this is because of the SKIP
664 * LOCKED syntax - it's going to skip any rows that are
665 * locked even if they match the selection criteria.
666 *
667 * To debug the select statement, comment out the skip
668 * locked syntax.
669 */
670
671 l_cursor_stmt :=
672 'SELECT '
673 || 'prq.call_app_pay_service_req_code, '
674 || 'prq.calling_app_id, '
675 || 'prq.payment_service_request_id, '
676 || 'pmts.payment_id, '
677 || 'pmts.internal_bank_account_id, '
678 || 'pmts.payment_profile_id, '
679 || 'pmts.org_id, '
680 || 'pmts.org_type, '
681 || 'pmts.legal_entity_id, '
682 || 'pmts.payment_currency_code, '
683 || 'pmts.payment_amount, '
684 || 'pmts.payment_date, '
685 || 'pmts.payment_function, '
686 || 'pmts.payment_reason_code, '
687 || 'pmts.payment_reason_comments, '
688 || 'NVL(LENGTH(pmts.payment_details), 0), '
689 || 'pmts.bill_payable_flag , '
690 || 'pmts.payment_service_request_id, '
691 || 'rfc_ca.class_code, '
692 || 'pmts.payment_method_code, '
693 || 'icr.group_by_payment_date, '
694 || 'icr.group_by_payment_currency, '
695 || 'icr.group_by_max_payments_flag, '
696 || 'icr.max_payments_per_instruction, '
697 || 'icr.group_by_internal_bank_account, '
698 || 'icr.group_by_max_instruction_flag, '
699 || 'icr.max_amount_per_instr_value, '
700 || 'icr.max_amount_per_instr_curr_code, '
701 || 'icr.max_amount_fx_rate_type, '
702 || 'icr.group_by_pay_service_request, '
703 || 'icr.group_by_legal_entity, '
704 || 'icr.group_by_organization, '
705 || 'icr.group_by_payment_function, '
706 || 'icr.group_by_payment_reason, '
707 || 'icr.group_by_bill_payable, '
708 || 'icr.group_by_pay_service_request, '
709 || 'icr.group_by_rfc , '
710 || 'icr.group_by_payment_method '
711 || 'FROM '
712 || 'IBY_PAYMENTS_SEC_V pmts, '
713 || 'IBY_INSTR_CREATION_RULES icr, '
714 || 'IBY_PAY_SERVICE_REQUESTS prq, '
715 || 'IBY_SYS_PMT_PROFILES_B sppf, '
716 || 'IBY_ACCT_PMT_PROFILES_B appf, '
717 || 'HZ_PARTIES branch_party, '
718 || 'HZ_CODE_ASSIGNMENTS rfc_ca, '
719 || 'CE_BANK_ACCOUNTS bank_accts '
720 || 'WHERE '
721 || 'pmts.payment_status = :pmt_status AND '
722 || 'sppf.processing_type = :processing_type AND '
723 || 'pmts.payment_service_request_id = '
724 || 'prq.payment_service_request_id AND '
725 || 'prq.payment_service_request_status = :req_status AND '
726 || 'pmts.payment_profile_id = appf.payment_profile_id AND '
727 || 'sppf.system_profile_code = '
728 || 'appf.system_profile_code(+) AND '
729 || 'appf.system_profile_code = icr.system_profile_code(+) AND '
730 || 'rfc_ca.owner_table_name(+) = :table_name AND '
731 || 'rfc_ca.class_category(+) = :category AND '
732 || 'rfc_ca.owner_table_id(+) = branch_party.party_id AND '
733 || 'branch_party.party_id = bank_accts.bank_branch_id AND '
734 || 'bank_accts.bank_account_id = '
735 || 'pmts.internal_bank_account_id '
736 || NVL (l_sql_chunk, 'AND 1=1 ')
737 || 'ORDER BY '
738 || 'pmts.payment_profile_id, ' -- |
739 || 'pmts.payment_date, ' -- |
740 || 'pmts.payment_currency_code, ' -- |
741 || 'pmts.internal_bank_account_id, ' -- | Ensure that the grouping
742 || 'pmts.legal_entity_id, ' -- | logic below follows the
743 || 'pmts.org_id, ' -- | same order as this
744 || 'pmts.org_type, ' -- | order by clause; else,
745 || 'pmts.payment_function, ' -- | more instructions will
746 || 'pmts.payment_reason_code, ' -- | be created than the
747 || 'pmts.payment_reason_comments, ' -- | absolute minimum.
748 || 'pmts.bill_payable_flag, ' -- |
749 || 'pmts.payment_service_request_id,' -- |
750 || 'rfc_ca.class_code, ' -- |
751 || 'pmts.payment_method_code ' -- |
752 || 'FOR UPDATE of pmts.payment_id, prq.payment_service_request_id SKIP LOCKED '
753 ;
754 /* Modified the for update clause for the bug 7261651*/
755
756 /*
757 * Print the cursor statement for debug purposes.
758 */
759 print_debuginfo(l_module_name, 'Dynamic cursor statement: ');
760 IBY_BUILD_UTILS_PKG.printWrappedString(l_cursor_stmt);
761
762 print_debuginfo(l_module_name, 'cursor bind parameters - ');
763 print_debuginfo(l_module_name, ':pmt_status = '
764 || PMT_STATUS_CREATED);
765 print_debuginfo(l_module_name, ':processing_type = '
766 || p_processing_type);
767 print_debuginfo(l_module_name, ':req_status = '
768 || REQ_STATUS_PMTS_CREATED);
769 print_debuginfo(l_module_name, ':table_name = '
770 || 'HZ_PARTIES');
771 print_debuginfo(l_module_name, ':category = '
772 || 'RFC_IDENTIFIER');
773
774 OPEN l_pmts_cursor FOR
775 l_cursor_stmt
776 USING
777 PMT_STATUS_CREATED,
778 p_processing_type,
779 REQ_STATUS_PMTS_CREATED,
780 'HZ_PARTIES',
781 'RFC_IDENTIFIER'
782 ;
783 FETCH l_pmts_cursor BULK COLLECT INTO l_instrGrpCriTab;
784 CLOSE l_pmts_cursor;
785
786 print_debuginfo(l_module_name, 'Number of payments matching '
787 || 'provided selection criteria: '
788 || l_instrGrpCriTab.COUNT
789 );
790
791 /*
792 * Exit if no payments were found.
793 */
794 IF (l_instrGrpCriTab.COUNT = 0) THEN
795 print_debuginfo(l_module_name, 'No payments were '
796 || 'retrieved from DB. Either no pending '
797 || 'payments exist, or one or more '
798 || 'of the selected tables were locked causing '
799 || 'the select to exit due to NOWAIT clause. '
800 || 'You might want to try payment instruction '
801 || 'creation again later.'
802 );
803
804 print_debuginfo(l_module_name, 'Exiting '
805 || 'payment instruction creation ..');
806
807 x_return_status := FND_API.G_RET_STS_SUCCESS;
808
809 print_debuginfo(l_module_name, 'EXIT');
810 RETURN;
811 END IF;
812
813 /*
814 * Loop through all the fetched payments, grouping them
815 * into payment instructions.
816 */
817 FOR i in l_instrGrpCriTab.FIRST .. l_instrGrpCriTab.LAST LOOP
818
819 curr_ca_id := l_instrGrpCriTab(i).calling_app_id;
820 curr_pmt_id := l_instrGrpCriTab(i).payment_id;
821 curr_int_bank_acct_id := l_instrGrpCriTab(i).int_bank_acct_id;
822 curr_profile_id := l_instrGrpCriTab(i).payment_profile_id;
823 curr_org_id := l_instrGrpCriTab(i).org_id;
824 curr_org_type := l_instrGrpCriTab(i).org_type;
825 curr_le_id := l_instrGrpCriTab(i).le_id;
826 curr_pmt_currency := l_instrGrpCriTab(i).payment_currency;
827 curr_pmt_amount := l_instrGrpCriTab(i).payment_amount;
828 curr_payment_date := l_instrGrpCriTab(i).payment_date;
829 curr_pmt_function := l_instrGrpCriTab(i).payment_function;
830 curr_pmt_reason_code := l_instrGrpCriTab(i).payment_reason_code;
831 curr_pmt_reason_commt := l_instrGrpCriTab(i).payment_reason_comments;
832 curr_prom_note_flag := l_instrGrpCriTab(i).pmt_prom_note_flag;
833 curr_ppr_id := l_instrGrpCriTab(i).ppr_id;
834 curr_rfc_identifier := l_instrGrpCriTab(i).rfc_identifier;
835 curr_pmt_method_code := l_instrGrpCriTab(i).payment_method_code;
836
837 print_debuginfo(l_module_name, 'curr_pmt_method_code :=' ||curr_pmt_method_code ||' and l_instrGrpCriTab('||i||').payment_method_code '||l_instrGrpCriTab(i).payment_method_code );
838 print_debuginfo(l_module_name, 'Payment_id :'||l_instrGrpCriTab(i).payment_id);
839 /* used in raising biz events */
840 l_ca_id := l_instrGrpCriTab(i).calling_app_id;
841
842 /* user defined grouping rule flags */
843 l_pmt_date_flag := l_instrGrpCriTab(i).pmt_date_flag;
844 l_pmt_curr_flag := l_instrGrpCriTab(i).pmt_curr_flag;
845 l_int_bank_acct_flag := l_instrGrpCriTab(i).int_bank_acct_flag;
846 l_le_flag := l_instrGrpCriTab(i).le_flag;
847 l_org_flag := l_instrGrpCriTab(i).org_flag;
848 l_max_pmts_flag := l_instrGrpCriTab(i).max_payments_flag;
849 l_max_pmts_limit := l_instrGrpCriTab(i).max_payments_limit;
850 l_max_amount_flag := l_instrGrpCriTab(i).max_amount_flag;
851 l_max_amount_limit := l_instrGrpCriTab(i).max_amount_limit;
852 l_max_amount_curr := l_instrGrpCriTab(i).max_amount_currency;
853 l_exchg_rate_type := l_instrGrpCriTab(i).max_amount_fx_rate_type;
854 l_pmt_details_length := l_instrGrpCriTab(i).pmt_details_length;
855 l_pmt_function_flag := l_instrGrpCriTab(i).pmt_function_flag;
856 l_pmt_reason_flag := l_instrGrpCriTab(i).pmt_reason_flag;
857 l_prom_note_flag := l_instrGrpCriTab(i).prom_note_flag;
858 l_ppr_flag := l_instrGrpCriTab(i).ppr_flag;
859 l_rfc_flag := l_instrGrpCriTab(i).rfc_flag;
860 l_pmt_method_flag := l_instrGrpCriTab(i).pmt_method_flag;
861 l_payment_curr_flag := l_instrGrpCriTab(i).pmt_curr_flag;
862
863 /*
864 * Log all the fetched payment fields
865 */
866 print_debuginfo(l_module_name,
867 'Fetched payment data for payment id: ' || curr_pmt_id
868 || ', calling app id: ' || curr_ca_id
869 || ', ppr id: ' || curr_ppr_id
870 || ', internal bank account: ' || curr_int_bank_acct_id
871 || ', profile: ' || curr_profile_id
872 || ', org: ' || curr_org_id
873 || ', org type: ' || curr_org_type
874 || ', payment currency: ' || curr_pmt_currency
875 || ', payment amount: ' || curr_pmt_amount
876 || ', payment date: ' || curr_payment_date
877 || ', payment function: ' || curr_pmt_function
878 || ', payment reason code: ' || curr_pmt_reason_code
879 || ', payment reason commts: ' || curr_pmt_reason_commt
880 || ', is pmt a prom note? ' || curr_prom_note_flag
881 || ', rfc identifier :' || curr_rfc_identifier
882 || ', payment method: ' || curr_pmt_method_code
883 );
884
885 print_debuginfo(l_module_name,
886 'Fetched payment data for payment id: ' || curr_pmt_id
887 || ', max pmts flag: ' || l_max_pmts_flag
888 || ', max pmts limit: ' || l_max_pmts_limit
889 || ', max amount flag: ' || l_max_amount_flag
890 || ', max amount curr: ' || l_max_amount_curr
891 || ', max amount limit: ' || l_max_amount_limit
892 || ', pmt function flag: ' || l_pmt_function_flag
893 || ', pmt reason flag: ' || l_pmt_reason_flag
894 || ', prom note flag: ' || l_prom_note_flag
895 || ', ppr flag: ' || l_ppr_flag
896 || ', rfc flag: ' || l_rfc_flag
897 || ', pmt curr flag: ' || l_payment_curr_flag
898 || ', pmt method flag: ' || l_pmt_method_flag
899 );
900
901 IF (l_first_record = 'Y') THEN
902 prev_pmt_id := curr_pmt_id;
903 prev_ca_id := curr_ca_id;
904 prev_int_bank_acct_id := curr_int_bank_acct_id;
905 prev_profile_id := curr_profile_id;
906 prev_org_id := curr_org_id;
907 prev_org_type := curr_org_type;
908 prev_pmt_currency := curr_pmt_currency;
909 prev_pmt_amount := curr_pmt_amount;
910 prev_payment_date := curr_payment_date;
911 prev_pmt_function := curr_pmt_function;
912 prev_pmt_reason_code := curr_pmt_reason_code;
913 prev_pmt_reason_commt := curr_pmt_reason_commt;
914 prev_prom_note_flag := curr_prom_note_flag;
915 prev_ppr_id := curr_ppr_id;
916 prev_rfc_identifier := curr_rfc_identifier;
917 prev_pmt_method_code := curr_pmt_method_code;
918 END IF;
919
920 IF (UPPER(l_max_amount_flag) = 'Y') THEN
921
922 l_pmt_fx_amount := getFxAmount(
923 curr_pmt_currency, /* IN: source currency */
924 l_max_amount_curr, /* IN: target currency */
925 curr_payment_date, /* IN: exchange rate date */
926 l_exchg_rate_type, /* IN: exchange rate type */
927 curr_pmt_amount /* IN: source amount */
928 );
929
930 print_debuginfo(l_module_name, 'Payment amount in fx '
931 || 'currency: '
932 || l_pmt_fx_amount
933 );
934
935 IF (l_pmt_fx_amount = -1) THEN
936
937 /*
938 * This means that an exception occured when we
939 * attempted currency conversion. We cannot
940 * proceed. Stop the program.
941 */
942
943 /*
944 * Error occurred in call to GL API to convert
945 * amount. Raise exception.
946 */
947 print_debuginfo(l_module_name, 'Currency conversion '
948 || 'from currency '
949 || curr_pmt_currency
950 || ' to currency '
951 || l_max_amount_curr
952 || ' with exchange rate date '
953 || curr_payment_date
954 || ' and exchange rate type '
955 || l_exchg_rate_type
956 || ' failed. '
957 || 'Raising exception.',
958 FND_LOG.LEVEL_UNEXPECTED
959 );
960
961 APP_EXCEPTION.RAISE_EXCEPTION;
962
963 END IF;
964
965 END IF;
966
967 /*
968 * We have just fetched a new payment from the selection.
969 * We will either insert this payment into a new instruction or
970 * we will be inserting this payment into the currently running
971 * payment instruction.
972 *
973 * In either case, we need to insert this pmt into an instruction.
974 * So pre-populate the instruction record with attributes of
975 * this payment. This is because the instruction takes on the
976 * attributes of it's constituent payments.
977 *
978 * Note: For user defined grouping rules, we will
979 * have to populate the payment attribute only if
980 * the user has turned on grouping by that attribute.
981 */
982
983 /* Only pre-fill hardcoded grouping rule attributes */
984 l_pmtInstrRec.payment_profile_id := curr_profile_id;
985
986 /*
987 * Pre-fill all user provided instruction attributes.
988 * These attributes are applicable to all payment
989 * instructions created as part of this processing run.
990 */
991
992 l_pmtInstrRec.comments := p_comments;
993 l_pmtInstrRec.pay_admin_assigned_ref_code := p_admin_assigned_ref;
994 l_pmtInstrRec.printer_name := p_printer_name;
995 l_pmtInstrRec.payment_document_id := p_pmt_document_id;
996
997 /*
998 * The 'print now' and 'transmit now' flags are not null
999 * columns in the database. However, they are optional
1000 * params in the API.
1001 *
1002 * Therefore, set these flags to a default value if not
1003 * provided (based on the processing type).
1004 */
1005 IF (p_processing_type = P_TYPE_PRINTED) THEN
1006 l_pmtInstrRec.print_instruction_immed_flag := p_print_now_flag;
1007 l_pmtInstrRec.transmit_instr_immed_flag := 'N';
1008 ELSE
1009 l_pmtInstrRec.print_instruction_immed_flag := 'N';
1010 l_pmtInstrRec.transmit_instr_immed_flag := p_transmit_now_flag;
1011 END IF;
1012
1013 /*
1014 * Pre-fill grouping rule attributes for user
1015 * selected grouping rules.
1016 *
1017 * It is necessary to pre-fill user defined grouping
1018 * attributes before the grouping rules are triggered
1019 * because we don't know which user defined grouping rules
1020 * are going to get triggered first, and once a rule is
1021 * triggered all rules below it are skipped. So it is too
1022 * late to populate grouping attributes within the grouping
1023 * rule itself.
1024 */
1025
1026 IF (l_int_bank_acct_flag = 'Y') THEN
1027 l_pmtInstrRec.internal_bank_account_id := curr_int_bank_acct_id;
1028 END IF;
1029
1030 IF (l_pmt_curr_flag = 'Y') THEN
1031 l_pmtInstrRec.payment_currency_code := curr_pmt_currency;
1032 END IF;
1033
1034 IF (l_le_flag = 'Y') THEN
1035 l_pmtInstrRec.legal_entity_id := curr_le_id;
1036 END IF;
1037
1038 IF (l_org_flag = 'Y') THEN
1039 l_pmtInstrRec.org_id := curr_org_id;
1040 l_pmtInstrRec.org_type := curr_org_type;
1041 END IF;
1042
1043 IF (l_pmt_date_flag = 'Y') THEN
1044 l_pmtInstrRec.payment_date := curr_payment_date;
1045 END IF;
1046
1047 IF (l_pmt_function_flag = 'Y') THEN
1048 l_pmtInstrRec.payment_function := curr_pmt_function;
1049 END IF;
1050
1051 IF (l_pmt_reason_flag = 'Y') THEN
1052 l_pmtInstrRec.payment_reason_code := curr_pmt_reason_code;
1053 l_pmtInstrRec.payment_reason_comments := curr_pmt_reason_commt;
1054 END IF;
1055
1056 IF (l_prom_note_flag = 'Y') THEN
1057 l_pmtInstrRec.bill_payable_flag := curr_prom_note_flag;
1058 END IF;
1059
1060 IF (l_ppr_flag = 'Y') THEN
1061 l_pmtInstrRec.payment_service_request_id := curr_ppr_id;
1062 END IF;
1063
1064 IF (l_rfc_flag = 'Y') THEN
1065 l_pmtInstrRec.rfc_identifier := curr_rfc_identifier;
1066 END IF;
1067 -- SEPA specific code
1068 IF (l_pmt_method_flag = 'Y') THEN
1069 l_pmtInstrRec.payment_method_code := curr_pmt_method_code;
1070 END IF;
1071
1072 /*
1073 * Pre-fill the payment record with the details
1074 * of the current payment.
1075 */
1076 l_pmtsInInstrRec.payment_id := curr_pmt_id;
1077 l_pmtsInInstrRec.payment_amount := curr_pmt_amount;
1078 l_pmtsInInstrRec.payment_currency := curr_pmt_currency;
1079 l_pmtsInInstrRec.profile_id := curr_profile_id;
1080 l_pmtsInInstrRec.processing_type := p_processing_type;
1081 l_pmtsInInstrRec.pmt_details_len := l_pmt_details_length;
1082
1083
1084 /*-- HARDCODED GROUPING RULES START HERE --*/
1085
1086 /*
1087 * Grouping Step 1: Payment Profile ID
1088 */
1089 IF (prev_profile_id <> curr_profile_id) THEN
1090
1091 print_debuginfo(l_module_name, 'Grouping by payment '
1092 || 'profile triggered for payment '
1093 || curr_pmt_id);
1094
1095 insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
1096 true, l_instr_id,
1097 l_pmtsInInstrTab, l_pmtsInInstrRec,
1098 l_pmts_in_instr_count, l_instr_amount);
1099
1100 GOTO label_finish_iteration;
1101 END IF;
1102
1103 /*-- USER DEFINED GROUPING RULES START HERE --*/
1104
1105 /*
1106 * Grouping Step 2: Payment Date
1107 */
1108 IF (l_pmt_date_flag = 'Y') THEN
1109 IF (prev_payment_date <> curr_payment_date) THEN
1110
1111 print_debuginfo(l_module_name, 'Grouping by '
1112 || 'payment date triggered for payment '
1113 || curr_pmt_id);
1114
1115 insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
1116 true, l_instr_id,
1117 l_pmtsInInstrTab, l_pmtsInInstrRec,
1118 l_pmts_in_instr_count, l_instr_amount);
1119
1120 GOTO label_finish_iteration;
1121 END IF;
1122 END IF;
1123
1124 /*
1125 * Grouping Step 3: Payment Currency
1126 */
1127 IF (l_payment_curr_flag = 'Y') THEN
1128 IF (prev_pmt_currency <> curr_pmt_currency) THEN
1129
1130 print_debuginfo(l_module_name, 'Grouping by payment '
1131 || 'currency triggered for payment '
1132 || curr_pmt_id);
1133
1134 insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
1135 true, l_instr_id,
1136 l_pmtsInInstrTab, l_pmtsInInstrRec,
1137 l_pmts_in_instr_count, l_instr_amount);
1138
1139 GOTO label_finish_iteration;
1140 END IF;
1141 END IF;
1142
1143 /*
1144 * Grouping Step 4: Internal Bank Account ID
1145 */
1146 IF (l_int_bank_acct_flag = 'Y') THEN
1147 IF (prev_int_bank_acct_id <> curr_int_bank_acct_id) THEN
1148
1149 print_debuginfo(l_module_name, 'Grouping by internal bank '
1150 || 'account triggered for payment '
1151 || curr_pmt_id);
1152
1153 insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
1154 true, l_instr_id,
1155 l_pmtsInInstrTab, l_pmtsInInstrRec,
1156 l_pmts_in_instr_count, l_instr_amount);
1157
1158 GOTO label_finish_iteration;
1159 END IF;
1160 END IF;
1161
1162 /*
1163 * Grouping Step 5: Legal Entity (LE)
1164 */
1165 IF (l_le_flag = 'Y') THEN
1166 IF (prev_le_id <> curr_le_id) THEN
1167
1168 print_debuginfo(l_module_name, 'Grouping by '
1169 || 'legal entity (LE) triggered for payment '
1170 || curr_pmt_id);
1171
1172 insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
1173 true, l_instr_id,
1174 l_pmtsInInstrTab, l_pmtsInInstrRec,
1175 l_pmts_in_instr_count, l_instr_amount);
1176
1177 GOTO label_finish_iteration;
1178 END IF;
1179 END IF;
1180
1181 /*
1182 * Grouping Step 6: Organization Id And Organization Type
1183 */
1184 IF (l_org_flag = 'Y') THEN
1185 IF (prev_org_id <> curr_org_id) OR
1186 (prev_org_type <> curr_org_type) THEN
1187
1188 print_debuginfo(l_module_name, 'Grouping by '
1189 || 'organization triggered for payment '
1190 || curr_pmt_id);
1191
1192 insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
1193 true, l_instr_id,
1194 l_pmtsInInstrTab, l_pmtsInInstrRec,
1195 l_pmts_in_instr_count, l_instr_amount);
1196
1197 GOTO label_finish_iteration;
1198 END IF;
1199 END IF;
1200
1201 /*
1202 * Grouping Step 7: Payment Function
1203 */
1204 IF (l_pmt_function_flag = 'Y') THEN
1205 IF (prev_pmt_function <> curr_pmt_function) THEN
1206
1207 print_debuginfo(l_module_name, 'Grouping by '
1208 || 'payment function triggered for payment '
1209 || curr_pmt_id);
1210
1211 insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
1212 true, l_instr_id,
1213 l_pmtsInInstrTab, l_pmtsInInstrRec,
1214 l_pmts_in_instr_count, l_instr_amount);
1215
1216 GOTO label_finish_iteration;
1217 END IF;
1218 END IF;
1219
1220 /*
1221 * Grouping Step 8: Payment Reason Code / Comments
1222 */
1223 IF (l_pmt_reason_flag = 'Y') THEN
1224 IF (NVL(prev_pmt_reason_code, 0) <>
1225 NVL(curr_pmt_reason_code, 0) OR
1226 NVL(prev_pmt_reason_commt, 0) <>
1227 NVL(curr_pmt_reason_commt, 0)) THEN
1228
1229 print_debuginfo(l_module_name, 'Grouping by '
1230 || 'payment reason code / comments triggered for payment '
1231 || curr_pmt_id);
1232
1233 insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
1234 true, l_instr_id,
1235 l_pmtsInInstrTab, l_pmtsInInstrRec,
1236 l_pmts_in_instr_count, l_instr_amount);
1237
1238 GOTO label_finish_iteration;
1239 END IF;
1240 END IF;
1241
1242 /*
1243 * Grouping Step 9: Promissory Note Flag
1244 */
1245 IF (l_prom_note_flag = 'Y') THEN
1246 IF (NVL(prev_prom_note_flag, 0) <> NVL(curr_prom_note_flag, 0))
1247 THEN
1248
1249 print_debuginfo(l_module_name, 'Grouping by '
1250 || 'promissory note flag triggered for payment '
1251 || curr_pmt_id);
1252
1253 insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
1254 true, l_instr_id,
1255 l_pmtsInInstrTab, l_pmtsInInstrRec,
1256 l_pmts_in_instr_count, l_instr_amount);
1257
1258 GOTO label_finish_iteration;
1259 END IF;
1260 END IF;
1261
1262 /*
1263 * Grouping Step 10: Payment Process Request
1264 */
1265 IF (l_ppr_flag = 'Y') THEN
1266 IF (prev_ppr_id <> curr_ppr_id)
1267 THEN
1268
1269 print_debuginfo(l_module_name, 'Grouping by '
1270 || 'payment process request triggered for payment '
1271 || curr_pmt_id);
1272
1273 insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
1274 true, l_instr_id,
1275 l_pmtsInInstrTab, l_pmtsInInstrRec,
1276 l_pmts_in_instr_count, l_instr_amount);
1277
1278 GOTO label_finish_iteration;
1279 END IF;
1280 END IF;
1281
1282 /*
1283 * Grouping Step 11: RFC Flag
1284 */
1285 IF (l_rfc_flag = 'Y') THEN
1286 IF (NVL(prev_rfc_identifier, 0) <> NVL(curr_rfc_identifier, 0))
1287 THEN
1288
1289 print_debuginfo(l_module_name, 'Grouping by '
1290 || 'rfc identifier triggered for payment '
1291 || curr_pmt_id);
1292
1293 insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
1294 true, l_instr_id,
1295 l_pmtsInInstrTab, l_pmtsInInstrRec,
1296 l_pmts_in_instr_count, l_instr_amount);
1297
1298 GOTO label_finish_iteration;
1299 END IF;
1300 END IF;
1301
1302 -- SEPA code
1303 IF (l_pmt_method_flag = 'Y') THEN
1304 IF (NVL(prev_pmt_method_code, 0) <> NVL(curr_pmt_method_code, 0))
1305 THEN
1306
1307 print_debuginfo(l_module_name, 'Grouping by '
1308 || 'payment method triggered for payment '
1309 || curr_pmt_id);
1310
1311 insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
1312 true, l_instr_id,
1313 l_pmtsInInstrTab, l_pmtsInInstrRec,
1314 l_pmts_in_instr_count, l_instr_amount);
1315
1316 GOTO label_finish_iteration;
1317 END IF;
1318 END IF;
1319
1320 /*
1321 * Grouping Step 12: Max Payments Per Instruction
1322 */
1323 IF (l_max_pmts_flag = 'Y') THEN
1324 IF (l_pmts_in_instr_count = l_max_pmts_limit) THEN
1325
1326 print_debuginfo(l_module_name, 'Grouping by '
1327 || 'max payments per instruction triggered '
1328 || 'for payment '
1329 || curr_pmt_id);
1330
1331 insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
1332 true, l_instr_id,
1333 l_pmtsInInstrTab, l_pmtsInInstrRec,
1334 l_pmts_in_instr_count, l_instr_amount);
1335
1336 GOTO label_finish_iteration;
1337 END IF;
1338 END IF;
1339
1340 /*
1341 * Grouping Step 13: Break Payments According To Max Amount
1342 * Per Instruction
1343 */
1344 IF (UPPER(l_max_amount_flag) = 'Y') THEN
1345 IF ((l_instr_amount + l_pmt_fx_amount)
1346 > l_max_amount_limit) THEN
1347
1348 print_debuginfo(l_module_name, 'Grouping by '
1349 || 'max total amount per instruction triggered '
1350 || 'for payment '
1351 || curr_pmt_id);
1352
1353 insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
1354 true, l_instr_id,
1355 l_pmtsInInstrTab, l_pmtsInInstrRec,
1356 l_pmts_in_instr_count, l_instr_amount);
1357
1358 GOTO label_finish_iteration;
1359 END IF;
1360 END IF;
1361
1362 /*
1363 * End Of Grouping:
1364 * If a document reaches here, it means that this document
1365 * is similar to the previous document as far a grouping
1366 * criteria is concerned.
1367 *
1368 * Add this document to the currently running payment.
1369 */
1370 print_debuginfo(l_module_name, 'No grouping rules '
1371 || 'were triggered for payment '
1372 || curr_pmt_id);
1373
1374 insertPmtIntoInstruction(l_pmtInstrRec, l_pmtInstrTab,
1375 false, l_instr_id,
1376 l_pmtsInInstrTab, l_pmtsInInstrRec,
1377 l_pmts_in_instr_count, l_instr_amount);
1378
1379 <<label_finish_iteration>>
1380
1381 /*
1382 * We just finished inserting a payment into an
1383 * instruction. Therefore, the instruction id
1384 * is available now.
1385 *
1386 * For each payment in this instruction, store the
1387 * payment function and org, if unique.
1388 *
1389 * This information will be used by the UI in
1390 * restricting user access.
1391 */
1392 deriveDistinctAccessTypsForIns(
1393 l_instr_id,
1394 curr_pmt_function,
1395 curr_org_id,
1396 curr_org_type,
1397 l_pmtFxAccessTypesTab,
1398 l_orgAccessTypesTab
1399 );
1400
1401 /*
1402 * Lastly, before going into the next iteration
1403 * of the loop copy all the current grouping criteria
1404 * into 'prev' fields so that we can compare these
1405 * fields with the next record.
1406 *
1407 * No need to copy the current values into the previous ones for
1408 * the first record because we have already done it at the beginning.
1409 */
1410 IF (l_first_record <> 'Y') THEN
1411 prev_pmt_id := curr_pmt_id;
1412 prev_ca_id := curr_ca_id;
1413 prev_int_bank_acct_id := curr_int_bank_acct_id;
1414 prev_profile_id := curr_profile_id;
1415 prev_org_id := curr_org_id;
1416 prev_org_type := curr_org_type;
1417 prev_pmt_currency := curr_pmt_currency;
1418 prev_pmt_amount := curr_pmt_amount;
1419 prev_payment_date := curr_payment_date;
1420 prev_pmt_function := curr_pmt_function;
1421 prev_pmt_reason_code := curr_pmt_reason_code;
1422 prev_pmt_reason_commt := curr_pmt_reason_commt;
1423 prev_prom_note_flag := curr_prom_note_flag;
1424 prev_rfc_identifier := curr_rfc_identifier;
1425 prev_ppr_id := curr_ppr_id;
1426 prev_pmt_method_code := curr_pmt_method_code;
1427 END IF;
1428
1429 /*
1430 * Remember to reset the first record flag before going
1431 * into the next iteration.
1432 */
1433 IF (l_first_record = 'Y') THEN
1434 l_first_record := 'N';
1435 END IF;
1436
1437 END LOOP;
1438
1439 print_debuginfo(l_module_name, 'Created '
1440 || l_pmtInstrTab.COUNT || ' payment instruction(s) from '
1441 || l_pmtsInInstrTab.COUNT || ' payments(s)'
1442 );
1443
1444 /*
1445 * Initialize the created instructions.
1446 */
1447 initializeInstrs(l_pmtInstrTab, p_single_pmt_flow_flag);
1448
1449 /*
1450 * Get the mapping between profile ids and system profile codes.
1451 *
1452 * Individual payment instructions contain the profile id
1453 * as an attribute. However, the config tables like
1454 * pmt instruction creation rules etc. contain settings
1455 * based on system profile codes.
1456 *
1457 * Therefore, we need this mapping for operations that
1458 * take place below.
1459 */
1460 IBY_BUILD_UTILS_PKG.getProfileMap(l_profile_map);
1461
1462 /* populate the document count associated with each payment */
1463 populateDocumentCount(l_pmtsInInstrTab);
1464
1465 /*
1466 * Payment instruction validations
1467 */
1468 performInstructionValidations(l_pmtInstrTab, l_pmtsInInstrTab,
1469 l_docErrorTab, l_errTokenTab);
1470
1471 /*
1472 * Payment instructions that require separate remittance advices
1473 * need to be identified and flagged.
1474 */
1475 flagSeparateRemitAdvcPayments(l_pmtInstrTab, l_pmtsInInstrTab,
1476 l_profile_map);
1477
1478 /*
1479 * All payment instructions for this run have been
1480 * created and stored in a PLSQL table. Now write these
1481 * payment instructions to the database.
1482 *
1483 * Similarly, update the payments table by providing a
1484 * payment instruction id to each selected payment.
1485 */
1486 performDBUpdates(l_pmtInstrTab, l_pmtsInInstrTab, l_docErrorTab,
1487 l_errTokenTab, l_profile_map, x_return_status);
1488
1489 /*
1490 * Insert the distinct payment functions and orgs that
1491 * were found in the created payment instructions. These
1492 * will be used for limiting UI access to users.
1493 */
1494 insertDistinctAccessTypsForIns(l_pmtFxAccessTypesTab,
1495 l_orgAccessTypesTab);
1496
1497 /*
1498 * Call separate remittance advice flow (F17) for any
1499 * payment instructions that require separate remittance
1500 * advice.
1501 */
1502
1503 /*
1504 * Finally, raise business events to inform the calling app
1505 * if any payment instructions have failed.
1506 *
1507 * Note: this should be the last call after database records
1508 * have been inserted / updated. This is because you cannot
1509 * 'rollback' a business event once raised.
1510 */
1511 raiseBizEvents(l_pmtInstrTab);
1512
1513 /*
1514 * Copy back the created payment instructions
1515 * to the out parameter.
1516 */
1517 x_pmtInstrTab := l_pmtInstrTab;
1518
1519 print_debuginfo(l_module_name, 'COUNT x_pmtInstrTab: '
1520 || x_pmtInstrTab.COUNT);
1521 print_debuginfo(l_module_name, 'COUNT l_pmtInstrTab: '
1522 || l_pmtInstrTab.COUNT);
1523
1524 print_debuginfo(l_module_name, 'EXIT');
1525
1526 END createPaymentInstructions;
1527
1528 /*--------------------------------------------------------------------
1529 | NAME:
1530 | recreatePaymentInstruction
1531 |
1532 | PURPOSE:
1533 |
1534 |
1535 | PARAMETERS:
1536 | IN
1537 | p_instruction_id
1538 | ID of payment instruction to be re-created.
1539 |
1540 | OUT
1541 | x_return_status
1542 | -1 to indicate failure
1543 | 0 to indicate success
1544 |
1545 | RETURNS:
1546 |
1547 | NOTES:
1548 |
1549 *---------------------------------------------------------------------*/
1550 PROCEDURE recreatePaymentInstruction(
1551 x_pmtInstrRec IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL%
1552 ROWTYPE,
1553 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
1554 docErrorTabType,
1555 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
1556 trxnErrTokenTabType,
1557 x_return_status IN OUT NOCOPY NUMBER)
1558 IS
1559
1560 l_return_status NUMBER;
1561
1562 l_doc_seq_flag BOOLEAN;
1563 l_pmt_ref_flag BOOLEAN;
1564
1565 l_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE;
1566 l_profile_id IBY_PAY_INSTRUCTIONS_ALL.payment_profile_id%TYPE;
1567
1568 /* maps profile ids to system profile codes */
1569 l_profile_map IBY_BUILD_UTILS_PKG.profileIdToCodeMapTabType;
1570
1571 l_sort_options_tab sortOptionsTabType;
1572 l_sort_pmt_tab sortedPmtTabType;
1573
1574 TYPE dyn_sort_payments IS REF CURSOR;
1575 l_sort_pmts_cursor dyn_sort_payments;
1576
1577 l_module_name VARCHAR2(200) := G_PKG_NAME || '.recreatePaymentInstruction';
1578
1579 BEGIN
1580
1581 print_debuginfo(l_module_name, 'ENTER');
1582
1583 l_instruction_id := x_pmtInstrRec.payment_instruction_id;
1584
1585 /*
1586 * Pick up all validation sets that were:
1587 * a. Applied on this payment instruction earlier
1588 * b. Caused this payment instruction to fail
1589 * c. Have 'do not apply error flag' set to 'N'
1590 * (i.e., non-overridden validations)
1591 *
1592 * These validations have to be re-applied on the
1593 * payment instruction.
1594 */
1595 reApplyPayInstrValidationSets(x_pmtInstrRec, x_docErrorTab, x_errTokenTab);
1596
1597 /*
1598 * Check if document sequence have been assigned for the
1599 * payments of this payment instruction. If not, we
1600 * will need to assign document seq numbers for all
1601 * payments of this payment instruction.
1602 *
1603 * Note: Document sequence numbers will either be assigned
1604 * to all payments of the instruction or to none at all.
1605 * It is not possible that only some of the payments of this
1606 * instruction have document sequence numbers and not all.
1607 *
1608 * This behavior is by design.
1609 */
1610 l_doc_seq_flag := checkIfDocSeqCompleted(l_instruction_id);
1611
1612 IF (l_doc_seq_flag = FALSE) THEN
1613
1614 print_debuginfo(l_module_name, 'Document sequencing yet to '
1615 || 'be completed for payment instruction '
1616 || l_instruction_id
1617 );
1618
1619 ELSE
1620
1621 print_debuginfo(l_module_name, 'Document sequencing already '
1622 || 'completed for payment instruction '
1623 || l_instruction_id
1624 );
1625
1626 END IF;
1627
1628 /*
1629 * Check if payment references have been assigned for the
1630 * payments of this payment instruction. If not, we will need
1631 * to assign payment references now.
1632 *
1633 * Note: Payment reference numbers will either be assigned
1634 * to all payments of the instruction or to none at all.
1635 * It is not possible that only some of the payments of this
1636 * instruction have payment reference numbers and not all.
1637 *
1638 * This behavior is by design.
1639 */
1640 l_pmt_ref_flag := checkIfPmtRefCompleted(l_instruction_id);
1641
1642 IF (l_pmt_ref_flag = FALSE) THEN
1643
1644 print_debuginfo(l_module_name, 'Payment references yet to '
1645 || 'be provided for payment instruction '
1646 || l_instruction_id
1647 );
1648
1649 ELSE
1650
1651 print_debuginfo(l_module_name, 'Payment references already '
1652 || 'provided for payment instruction '
1653 || l_instruction_id
1654 );
1655
1656 END IF;
1657
1658 /*
1659 * If document sequencing or payment referencing for
1660 * this payment instruction has not yet taken place,
1661 * do so now.
1662 */
1663 IF (l_doc_seq_flag = FALSE OR l_pmt_ref_flag = FALSE) THEN
1664
1665 /* get the profile id of this pmt instruction */
1666 l_profile_id := get_instruction_profile(l_instruction_id);
1667
1668 /*
1669 * Pick up the sorting criteria for attached to this profile
1670 * and put them into a PLSQL table.
1671 *
1672 * Our payment instruction will use the sorting criteria
1673 * from this table.
1674 */
1675 retrieveSortOptionForProfile(l_profile_id, l_sort_options_tab);
1676
1677 IF (l_sort_options_tab.COUNT = 0) THEN
1678
1679 print_debuginfo(l_module_name, 'No sorting options have been '
1680 || 'set up for the profile '
1681 || l_profile_id
1682 || ' linked to payment instruction '
1683 || l_instruction_id
1684 );
1685
1686 END IF;
1687
1688 /*
1689 * Get the mapping between profile ids and system profile codes.
1690 *
1691 * We need this mapping for operations that
1692 * take place below.
1693 */
1694 IBY_BUILD_UTILS_PKG.getProfileMap(l_profile_map);
1695
1696 /*
1697 * Get the payments of this instruction in sorted
1698 * order.
1699 */
1700 getSortedPmtsForInstr(
1701 l_instruction_id,
1702 l_profile_id,
1703 l_sort_options_tab,
1704 l_profile_map,
1705 l_sort_pmt_tab
1706 );
1707
1708 print_debuginfo(l_module_name, 'Number of payments picked '
1709 || 'up for instruction id '
1710 || l_instruction_id
1711 || ' is '
1712 || l_sort_pmt_tab.COUNT
1713 );
1714
1715 /*
1716 * This should not happen because a payment
1717 * instruction should contain at least one
1718 * payment.
1719 */
1720 IF (l_sort_pmt_tab.COUNT = 0) THEN
1721
1722 print_debuginfo(l_module_name, 'No payments were '
1723 || 'picked up for payment instruction '
1724 || l_instruction_id
1725 || '. Possible data corruption. Aborting '
1726 || 'program.',
1727 FND_LOG.LEVEL_UNEXPECTED
1728 );
1729
1730 APP_EXCEPTION.RAISE_EXCEPTION;
1731
1732 END IF;
1733
1734 IF (l_doc_seq_flag = FALSE) THEN
1735
1736 /*
1737 * Generate document sequence numbers for the
1738 * payments on this instruction.
1739 */
1740 /*
1741 * Fix for bug 5069407:
1742 *
1743 * Document sequencing log commented out.
1744 *
1745 */
1746 --performDocSequenceNumbering(l_sort_pmt_tab, x_docErrorTab,
1747 -- x_errTokenTab);
1748
1749 /*
1750 * Update the payments table with the generated
1751 * document sequence numbers.
1752 */
1753 updatePmtsWithSeqNum(l_sort_pmt_tab);
1754
1755 END IF;
1756
1757 IF (l_pmt_ref_flag = FALSE) THEN
1758
1759 /*
1760 * Generate payment reference numbers for the
1761 * payments on this instruction.
1762 */
1763 providePaymentReferences(l_sort_pmt_tab);
1764
1765 /*
1766 * Update the payments table with the generated
1767 * payment reference numbers.
1768 */
1769 updatePmtsWithPmtRef(l_sort_pmt_tab);
1770
1771 END IF;
1772
1773 END IF;
1774
1775 x_return_status := 0;
1776
1777 print_debuginfo(l_module_name, 'EXIT');
1778
1779 EXCEPTION
1780 WHEN OTHERS THEN
1781
1782 print_debuginfo(l_module_name, 'Exception occured when '
1783 || 're-creating payment instruction id '
1784 || l_instruction_id,
1785 FND_LOG.LEVEL_UNEXPECTED
1786 );
1787
1788 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
1789 FND_LOG.LEVEL_UNEXPECTED);
1790 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
1791 FND_LOG.LEVEL_UNEXPECTED);
1792
1793 print_debuginfo(l_module_name, 'EXIT');
1794
1795 /*
1796 * Propogate exception to caller.
1797 */
1798 RAISE;
1799
1800 END recreatePaymentInstruction;
1801
1802 /*--------------------------------------------------------------------
1803 | NAME:
1804 | insertPmtIntoInstruction
1805 |
1806 | PURPOSE:
1807 |
1808 |
1809 | PARAMETERS:
1810 | IN
1811 |
1812 |
1813 | OUT
1814 |
1815 |
1816 | RETURNS:
1817 |
1818 | NOTES:
1819 |
1820 *---------------------------------------------------------------------*/
1821 PROCEDURE insertPmtIntoInstruction(
1822 x_pmtInstrRec IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE,
1823 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
1824 p_newPmtInstrFlag IN BOOLEAN,
1825 x_currentPmtInstrId IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL.
1826 payment_instruction_id%TYPE,
1827 x_pmtsInPmtInstrTab IN OUT NOCOPY pmtsInPmtInstrTabType,
1828 x_pmtsInPmtInstrRec IN OUT NOCOPY pmtsInPmtInstrRecType,
1829 x_pmtsInPmtInstrCount IN OUT NOCOPY NUMBER,
1830 x_instrAmount IN OUT NOCOPY NUMBER
1831 )
1832 IS
1833 l_module_name VARCHAR2(200) := G_PKG_NAME || '.insertPmtIntoInstruction';
1834
1835 BEGIN
1836
1837 print_debuginfo(l_module_name, 'ENTER');
1838
1839 /*
1840 * GROUPING LOGIC IS IN IF-ELSE BLOCK BELOW:
1841 *
1842 * Irrespective of whether this document is part of
1843 * an existing payment or whether it should be part
1844 * of a new payment, ensure that the PLSQL payments
1845 * table is updated with the details of this document
1846 * within this if-else block.
1847 *
1848 * We need to do this each time we enter this procedure
1849 * because this might well be the last document in
1850 * in the payment request, and this procedure may
1851 * not be called again for this payment request. So
1852 * the PLSQL payments table should always be up-to-date
1853 * when it exits this procedure.
1854 */
1855 IF (p_newPmtInstrFlag = true) THEN
1856
1857 /*
1858 * This is a new payment; Get an id for this payment
1859 */
1860 getNextPaymentInstructionID(x_currentPmtInstrId);
1861
1862 /*
1863 * Create a new payment instruction record using the
1864 * incoming payment as a constituent, and insert this
1865 * record into the PLSQL payments table.
1866 */
1867 x_pmtInstrRec.payment_instruction_id := x_currentPmtInstrId;
1868
1869 x_pmtsInPmtInstrCount := 1;
1870 x_pmtInstrRec.payment_count := x_pmtsInPmtInstrCount;
1871
1872 x_instrAmount := x_pmtsInPmtInstrRec.payment_amount;
1873
1874 x_pmtInstrTab(x_pmtInstrTab.COUNT + 1) := x_pmtInstrRec;
1875
1876 print_debuginfo(l_module_name, 'Inserted payment: '
1877 || x_pmtsInPmtInstrRec.payment_id || ' into new payment '
1878 || 'instruction: '
1879 || x_currentPmtInstrId);
1880
1881 /*
1882 * Assign the payment instruction id of the new payment
1883 * instruction to this payment, and insert the payment
1884 * into the payments array.
1885 */
1886 x_pmtsInPmtInstrRec.pay_instr_id := x_pmtInstrRec.
1887 payment_instruction_id;
1888 x_pmtsInPmtInstrTab(x_pmtsInPmtInstrTab.COUNT + 1)
1889 := x_pmtsInPmtInstrRec;
1890
1891 ELSE
1892
1893 /*
1894 * This means we need to add the incoming payment to
1895 * the current payment instruction.
1896 */
1897
1898 /*
1899 * First check special case: Payments PLSQL table is empty
1900 *
1901 * If the PLSQL table for payments is empty, we have to
1902 * intitialize it by inserting a dummy record. This dummy
1903 * record will get overwritten below.
1904 */
1905 IF (x_pmtInstrTab.COUNT = 0) THEN
1906
1907 getNextPaymentInstructionID(x_currentPmtInstrId);
1908
1909 x_pmtInstrRec.payment_instruction_id := x_currentPmtInstrId;
1910
1911 x_pmtsInPmtInstrCount := 0;
1912 x_pmtInstrRec.payment_count := x_pmtsInPmtInstrCount;
1913
1914 /*
1915 * Insert the first record into the table. This
1916 * is a dummy record.
1917 */
1918 x_pmtInstrTab(x_pmtInstrTab.COUNT + 1) := x_pmtInstrRec;
1919
1920 print_debuginfo(l_module_name, 'Created a new payment '
1921 || 'instruction: '
1922 || x_currentPmtInstrId);
1923
1924 END IF;
1925
1926 /*
1927 * The incoming payment should be part of the current payment
1928 * instruction. So add the payment amount to the current payment
1929 * instruction record and increment the payment count for the
1930 * current payment instruction record.
1931 */
1932 x_pmtInstrRec.payment_instruction_id := x_currentPmtInstrId;
1933
1934 x_pmtsInPmtInstrCount := x_pmtsInPmtInstrCount + 1;
1935 x_pmtInstrRec.payment_count := x_pmtsInPmtInstrCount;
1936
1937 x_instrAmount := x_instrAmount + x_pmtsInPmtInstrRec.payment_amount;
1938
1939 /*-- uncomment for debugging --*/
1940 /*
1941 print_debuginfo(l_module_name, 'x_pmtsInPmtInstrCount: '
1942 || x_pmtsInPmtInstrCount);
1943
1944 print_debuginfo(l_module_name, 'Instruction id: '
1945 || x_pmtInstrRec.payment_instruction_id
1946 || ', instruction count: '
1947 || x_pmtInstrRec.payment_count
1948 );
1949 */
1950 /*-- end debug --*/
1951
1952 /*
1953 * Overwrite the current payment instruction record in the
1954 * PLSQL payment instructions table with the updated record.
1955 */
1956 x_pmtInstrTab(x_pmtInstrTab.COUNT) := x_pmtInstrRec;
1957
1958 print_debuginfo(l_module_name, 'Inserted payment: '
1959 || x_pmtsInPmtInstrRec.payment_id
1960 || ' into existing payment instruction: '
1961 || x_currentPmtInstrId);
1962
1963 /*
1964 * Assign the instruction id of the current payment
1965 * instruction to this payment, and insert the payment
1966 * into the payments array.
1967 */
1968 x_pmtsInPmtInstrRec.pay_instr_id := x_pmtInstrRec.
1969 payment_instruction_id;
1970 x_pmtsInPmtInstrTab(x_pmtsInPmtInstrTab.COUNT + 1)
1971 := x_pmtsInPmtInstrRec;
1972
1973 END IF;
1974
1975 print_debuginfo(l_module_name, 'EXIT');
1976
1977 END insertPmtIntoInstruction;
1978
1979 /*--------------------------------------------------------------------
1980 | NAME:
1981 | insertPaymentInstructions
1982 |
1983 | PURPOSE:
1984 |
1985 |
1986 | PARAMETERS:
1987 | IN
1988 |
1989 |
1990 | OUT
1991 |
1992 |
1993 | RETURNS:
1994 |
1995 | NOTES:
1996 |
1997 *---------------------------------------------------------------------*/
1998 PROCEDURE insertPaymentInstructions(
1999 p_payInstrTab IN pmtInstrTabType
2000 )
2001 IS
2002 l_module_name VARCHAR2(200) := G_PKG_NAME || '.insertPaymentInstructions';
2003
2004 TYPE t_payment_instruction_id IS TABLE OF
2005 IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE
2006 INDEX BY BINARY_INTEGER;
2007 TYPE t_payment_profile_id IS TABLE OF
2008 IBY_PAY_INSTRUCTIONS_ALL.payment_profile_id%TYPE
2009 INDEX BY BINARY_INTEGER;
2010 TYPE t_process_type IS TABLE OF
2011 IBY_PAY_INSTRUCTIONS_ALL.process_type%TYPE
2012 INDEX BY BINARY_INTEGER;
2013 TYPE t_payment_instruction_status IS TABLE OF
2014 IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_status%TYPE
2015 INDEX BY BINARY_INTEGER;
2016 TYPE t_payments_complete_code IS TABLE OF
2017 IBY_PAY_INSTRUCTIONS_ALL.payments_complete_code%TYPE
2018 INDEX BY BINARY_INTEGER;
2019 TYPE t_gen_sep_remit_advice_flag IS TABLE OF
2020 IBY_PAY_INSTRUCTIONS_ALL.generate_sep_remit_advice_flag%TYPE
2021 INDEX BY BINARY_INTEGER;
2022 TYPE t_remit_advice_created_flag IS TABLE OF
2023 IBY_PAY_INSTRUCTIONS_ALL.remittance_advice_created_flag%TYPE
2024 INDEX BY BINARY_INTEGER;
2025 TYPE t_regul_rpt_created_flag IS TABLE OF
2026 IBY_PAY_INSTRUCTIONS_ALL.regulatory_report_created_flag%TYPE
2027 INDEX BY BINARY_INTEGER;
2028 TYPE t_bill_payable_flag IS TABLE OF
2029 IBY_PAY_INSTRUCTIONS_ALL.bill_payable_flag%TYPE
2030 INDEX BY BINARY_INTEGER;
2031 TYPE t_legal_entity_id IS TABLE OF
2032 IBY_PAY_INSTRUCTIONS_ALL.legal_entity_id%TYPE
2033 INDEX BY BINARY_INTEGER;
2034 TYPE t_payment_count IS TABLE OF
2035 IBY_PAY_INSTRUCTIONS_ALL.payment_count%TYPE
2036 INDEX BY BINARY_INTEGER;
2037 TYPE t_pos_pay_file_created_flag IS TABLE OF
2038 IBY_PAY_INSTRUCTIONS_ALL.positive_pay_file_created_flag%TYPE
2039 INDEX BY BINARY_INTEGER;
2040 TYPE t_print_instruction_immed_flag IS TABLE OF
2041 IBY_PAY_INSTRUCTIONS_ALL.print_instruction_immed_flag%TYPE
2042 INDEX BY BINARY_INTEGER;
2043 TYPE t_transmit_instr_immed_flag IS TABLE OF
2044 IBY_PAY_INSTRUCTIONS_ALL.transmit_instr_immed_flag%TYPE
2045 INDEX BY BINARY_INTEGER;
2046 TYPE t_created_by IS TABLE OF
2047 IBY_PAY_INSTRUCTIONS_ALL.created_by%TYPE
2048 INDEX BY BINARY_INTEGER;
2049 TYPE t_creation_date IS TABLE OF
2050 IBY_PAY_INSTRUCTIONS_ALL.creation_date%TYPE
2051 INDEX BY BINARY_INTEGER;
2052 TYPE t_last_updated_by IS TABLE OF
2053 IBY_PAY_INSTRUCTIONS_ALL.last_updated_by%TYPE
2054 INDEX BY BINARY_INTEGER;
2055 TYPE t_last_update_date IS TABLE OF
2056 IBY_PAY_INSTRUCTIONS_ALL.last_update_date%TYPE
2057 INDEX BY BINARY_INTEGER;
2058 TYPE t_last_update_login IS TABLE OF
2059 IBY_PAY_INSTRUCTIONS_ALL.last_update_login%TYPE
2060 INDEX BY BINARY_INTEGER;
2061 TYPE t_object_version_number IS TABLE OF
2062 IBY_PAY_INSTRUCTIONS_ALL.object_version_number%TYPE
2063 INDEX BY BINARY_INTEGER;
2064 TYPE t_internal_bank_account_id IS TABLE OF
2065 IBY_PAY_INSTRUCTIONS_ALL.internal_bank_account_id%TYPE
2066 INDEX BY BINARY_INTEGER;
2067 TYPE t_pay_admin_assigned_ref_code IS TABLE OF
2068 IBY_PAY_INSTRUCTIONS_ALL.pay_admin_assigned_ref_code%TYPE
2069 INDEX BY BINARY_INTEGER;
2070 TYPE t_transmission_date IS TABLE OF
2071 IBY_PAY_INSTRUCTIONS_ALL.transmission_date%TYPE
2072 INDEX BY BINARY_INTEGER;
2073 TYPE t_acknowledgement_date IS TABLE OF
2074 IBY_PAY_INSTRUCTIONS_ALL.acknowledgement_date%TYPE
2075 INDEX BY BINARY_INTEGER;
2076 TYPE t_comments IS TABLE OF
2077 IBY_PAY_INSTRUCTIONS_ALL.comments%TYPE
2078 INDEX BY BINARY_INTEGER;
2079 TYPE t_bank_assigned_ref_code IS TABLE OF
2080 IBY_PAY_INSTRUCTIONS_ALL.bank_assigned_ref_code%TYPE
2081 INDEX BY BINARY_INTEGER;
2082 TYPE t_org_id IS TABLE OF
2083 IBY_PAY_INSTRUCTIONS_ALL.org_id%TYPE
2084 INDEX BY BINARY_INTEGER;
2085 TYPE t_org_type IS TABLE OF
2086 IBY_PAY_INSTRUCTIONS_ALL.org_type%TYPE
2087 INDEX BY BINARY_INTEGER;
2088 TYPE t_payment_date IS TABLE OF
2089 IBY_PAY_INSTRUCTIONS_ALL.payment_date%TYPE
2090 INDEX BY BINARY_INTEGER;
2091 TYPE t_payment_currency_code IS TABLE OF
2092 IBY_PAY_INSTRUCTIONS_ALL.payment_currency_code%TYPE
2093 INDEX BY BINARY_INTEGER;
2094 TYPE t_payment_service_request_id IS TABLE OF
2095 IBY_PAY_INSTRUCTIONS_ALL.payment_service_request_id%TYPE
2096 INDEX BY BINARY_INTEGER;
2097 TYPE t_payment_function IS TABLE OF
2098 IBY_PAY_INSTRUCTIONS_ALL.payment_function%TYPE
2099 INDEX BY BINARY_INTEGER;
2100 TYPE t_payment_reason_code IS TABLE OF
2101 IBY_PAY_INSTRUCTIONS_ALL.payment_reason_code%TYPE
2102 INDEX BY BINARY_INTEGER;
2103 TYPE t_rfc_identifier IS TABLE OF
2104 IBY_PAY_INSTRUCTIONS_ALL.rfc_identifier%TYPE
2105 INDEX BY BINARY_INTEGER;
2106 TYPE t_pmt_method_code IS TABLE OF
2107 IBY_PAY_INSTRUCTIONS_ALL.payment_method_code%TYPE
2108 INDEX BY BINARY_INTEGER;
2109 TYPE t_payment_reason_comments IS TABLE OF
2110 IBY_PAY_INSTRUCTIONS_ALL.payment_reason_comments%TYPE
2111 INDEX BY BINARY_INTEGER;
2112 TYPE t_payment_document_id IS TABLE OF
2113 IBY_PAY_INSTRUCTIONS_ALL.payment_document_id%TYPE
2114 INDEX BY BINARY_INTEGER;
2115 TYPE t_printer_name IS TABLE OF
2116 IBY_PAY_INSTRUCTIONS_ALL.printer_name%TYPE
2117 INDEX BY BINARY_INTEGER;
2118 TYPE t_attribute_category IS TABLE OF
2119 IBY_PAY_INSTRUCTIONS_ALL.attribute_category%TYPE
2120 INDEX BY BINARY_INTEGER;
2121 TYPE t_attribute1 IS TABLE OF
2122 IBY_PAY_INSTRUCTIONS_ALL.attribute1%TYPE
2123 INDEX BY BINARY_INTEGER;
2124 TYPE t_attribute2 IS TABLE OF
2125 IBY_PAY_INSTRUCTIONS_ALL.attribute2%TYPE
2126 INDEX BY BINARY_INTEGER;
2127 TYPE t_attribute3 IS TABLE OF
2128 IBY_PAY_INSTRUCTIONS_ALL.attribute3%TYPE
2129 INDEX BY BINARY_INTEGER;
2130 TYPE t_attribute4 IS TABLE OF
2131 IBY_PAY_INSTRUCTIONS_ALL.attribute4%TYPE
2132 INDEX BY BINARY_INTEGER;
2133 TYPE t_attribute5 IS TABLE OF
2134 IBY_PAY_INSTRUCTIONS_ALL.attribute5%TYPE
2135 INDEX BY BINARY_INTEGER;
2136 TYPE t_attribute6 IS TABLE OF
2137 IBY_PAY_INSTRUCTIONS_ALL.attribute6%TYPE
2138 INDEX BY BINARY_INTEGER;
2139 TYPE t_attribute7 IS TABLE OF
2140 IBY_PAY_INSTRUCTIONS_ALL.attribute7%TYPE
2141 INDEX BY BINARY_INTEGER;
2142 TYPE t_attribute8 IS TABLE OF
2143 IBY_PAY_INSTRUCTIONS_ALL.attribute8%TYPE
2144 INDEX BY BINARY_INTEGER;
2145 TYPE t_attribute9 IS TABLE OF
2146 IBY_PAY_INSTRUCTIONS_ALL.attribute9%TYPE
2147 INDEX BY BINARY_INTEGER;
2148 TYPE t_attribute10 IS TABLE OF
2149 IBY_PAY_INSTRUCTIONS_ALL.attribute10%TYPE
2150 INDEX BY BINARY_INTEGER;
2151 TYPE t_attribute11 IS TABLE OF
2152 IBY_PAY_INSTRUCTIONS_ALL.attribute11%TYPE
2153 INDEX BY BINARY_INTEGER;
2154 TYPE t_attribute12 IS TABLE OF
2155 IBY_PAY_INSTRUCTIONS_ALL.attribute12%TYPE
2156 INDEX BY BINARY_INTEGER;
2157 TYPE t_attribute13 IS TABLE OF
2158 IBY_PAY_INSTRUCTIONS_ALL.attribute13%TYPE
2159 INDEX BY BINARY_INTEGER;
2160 TYPE t_attribute14 IS TABLE OF
2161 IBY_PAY_INSTRUCTIONS_ALL.attribute14%TYPE
2162 INDEX BY BINARY_INTEGER;
2163 TYPE t_attribute15 IS TABLE OF
2164 IBY_PAY_INSTRUCTIONS_ALL.attribute15%TYPE
2165 INDEX BY BINARY_INTEGER;
2166
2167 l_payment_instruction_id t_payment_instruction_id;
2168 l_payment_profile_id t_payment_profile_id;
2169 l_process_type t_process_type;
2170 l_payment_instruction_status t_payment_instruction_status;
2171 l_payments_complete_code t_payments_complete_code;
2172 l_gen_sep_remit_advice_flag t_gen_sep_remit_advice_flag;
2173 l_remit_advice_created_flag t_remit_advice_created_flag;
2174 l_regul_rpt_created_flag t_regul_rpt_created_flag;
2175 l_bill_payable_flag t_bill_payable_flag;
2176 l_legal_entity_id t_legal_entity_id;
2177 l_payment_count t_payment_count;
2178 l_pos_pay_file_created_flag t_pos_pay_file_created_flag;
2179 l_print_instr_immed_flag t_print_instruction_immed_flag;
2180 l_transmit_instr_immed_flag t_transmit_instr_immed_flag;
2181 l_created_by t_created_by;
2182 l_creation_date t_creation_date;
2183 l_last_updated_by t_last_updated_by;
2184 l_last_update_date t_last_update_date;
2185 l_last_update_login t_last_update_login;
2186 l_object_version_number t_object_version_number;
2187 l_internal_bank_account_id t_internal_bank_account_id;
2188 l_pay_admin_assigned_ref_code t_pay_admin_assigned_ref_code;
2189 l_transmission_date t_transmission_date;
2190 l_acknowledgement_date t_acknowledgement_date;
2191 l_comments t_comments;
2192 l_bank_assigned_ref_code t_bank_assigned_ref_code;
2193 l_org_id t_org_id;
2194 l_org_type t_org_type;
2195 l_payment_date t_payment_date;
2196 l_payment_currency_code t_payment_currency_code;
2197 l_payment_service_request_id t_payment_service_request_id;
2198 l_payment_function t_payment_function;
2199 l_payment_reason_code t_payment_reason_code;
2200 l_rfc_identifier t_rfc_identifier;
2201 l_pmt_method_code t_pmt_method_code;
2202 l_payment_reason_comments t_payment_reason_comments;
2203 l_payment_document_id t_payment_document_id;
2204 l_printer_name t_printer_name;
2205 l_attribute_category t_attribute_category;
2206 l_attribute1 t_attribute1;
2207 l_attribute2 t_attribute2;
2208 l_attribute3 t_attribute3;
2209 l_attribute4 t_attribute4;
2210 l_attribute5 t_attribute5;
2211 l_attribute6 t_attribute6;
2212 l_attribute7 t_attribute7;
2213 l_attribute8 t_attribute8;
2214 l_attribute9 t_attribute9;
2215 l_attribute10 t_attribute10;
2216 l_attribute11 t_attribute11;
2217 l_attribute12 t_attribute12;
2218 l_attribute13 t_attribute13;
2219 l_attribute14 t_attribute14;
2220 l_attribute15 t_attribute15;
2221
2222 BEGIN
2223
2224 print_debuginfo(l_module_name, 'ENTER');
2225
2226 /* Normally, this shouldn't happen */
2227 IF (p_payInstrTab.COUNT = 0) THEN
2228 print_debuginfo(l_module_name, 'After grouping, no payment '
2229 || 'instructions were found to update '
2230 || 'IBY_PAY_INSTRUCTIONS_ALL table.'
2231 || ' Possible data corruption issue.');
2232 RETURN;
2233 END IF;
2234
2235 FOR i IN p_payInstrTab.FIRST..p_payInstrTab.LAST LOOP
2236
2237 l_payment_instruction_id(i)
2238 := p_payInstrTab(i).payment_instruction_id;
2239 l_payment_profile_id(i)
2240 := p_payInstrTab(i).payment_profile_id;
2241 l_process_type(i)
2242 := NVL(p_payInstrTab(i).process_type, 'STANDARD');
2243 l_payment_instruction_status(i)
2244 := NVL(p_payInstrTab(i).payment_instruction_status, 'CREATED');
2245 l_payments_complete_code(i)
2246 := NVL(p_payInstrTab(i).payments_complete_code, 'NO');
2247 l_gen_sep_remit_advice_flag(i)
2248 := NVL(p_payInstrTab(i).generate_sep_remit_advice_flag, 'N');
2249 l_remit_advice_created_flag(i)
2250 := NVL(p_payInstrTab(i).remittance_advice_created_flag, 'N');
2251 l_regul_rpt_created_flag(i)
2252 := NVL(p_payInstrTab(i).regulatory_report_created_flag, 'N');
2253 l_bill_payable_flag(i)
2254 := NVL(p_payInstrTab(i).bill_payable_flag, 'N');
2255 l_legal_entity_id(i)
2256 := p_payInstrTab(i).legal_entity_id;
2257 l_payment_count(i)
2258 := p_payInstrTab(i).payment_count;
2259 l_pos_pay_file_created_flag(i)
2260 := NVL(p_payInstrTab(i).positive_pay_file_created_flag, 'N');
2261 l_print_instr_immed_flag(i)
2262 := NVL(p_payInstrTab(i).print_instruction_immed_flag, 'N');
2263 l_transmit_instr_immed_flag(i)
2264 := NVL(p_payInstrTab(i).transmit_instr_immed_flag, 'N');
2265 l_created_by(i)
2266 := NVL(p_payInstrTab(i).created_by, fnd_global.user_id);
2267 l_creation_date(i)
2268 := NVL(p_payInstrTab(i).creation_date, sysdate);
2269 l_last_updated_by(i)
2270 := NVL(p_payInstrTab(i).last_updated_by, fnd_global.user_id);
2271 l_last_update_date(i)
2272 := NVL(p_payInstrTab(i).last_update_date, sysdate);
2273 l_last_update_login(i)
2274 := NVL(p_payInstrTab(i).last_update_login, fnd_global.user_id);
2275 l_object_version_number(i)
2276 := NVL(p_payInstrTab(i).object_version_number, 1);
2277 l_internal_bank_account_id(i)
2278 := p_payInstrTab(i).internal_bank_account_id;
2279 l_pay_admin_assigned_ref_code(i)
2280 := p_payInstrTab(i).pay_admin_assigned_ref_code;
2281 l_transmission_date(i)
2282 := p_payInstrTab(i).transmission_date;
2283 l_acknowledgement_date(i)
2284 := p_payInstrTab(i).acknowledgement_date;
2285 l_comments(i)
2286 := p_payInstrTab(i).comments;
2287 l_bank_assigned_ref_code(i)
2288 := p_payInstrTab(i).bank_assigned_ref_code;
2289 l_org_id(i)
2290 := p_payInstrTab(i).org_id;
2291 l_org_type(i)
2292 := p_payInstrTab(i).org_type;
2293 l_payment_date(i)
2294 := p_payInstrTab(i).payment_date;
2295 l_payment_currency_code(i)
2296 := p_payInstrTab(i).payment_currency_code;
2297 l_payment_service_request_id(i)
2298 := p_payInstrTab(i).payment_service_request_id;
2299 l_payment_function(i)
2300 := p_payInstrTab(i).payment_function;
2301 l_payment_reason_code(i)
2302 := p_payInstrTab(i).payment_reason_code;
2303 l_rfc_identifier(i)
2304 := p_payInstrTab(i).rfc_identifier;
2305 l_pmt_method_code(i)
2306 := p_payInstrTab(i).payment_method_code;
2307 l_payment_reason_comments(i)
2308 := p_payInstrTab(i).payment_reason_comments;
2309 l_payment_document_id(i)
2310 := p_payInstrTab(i).payment_document_id;
2311 l_printer_name(i)
2312 := p_payInstrTab(i).printer_name;
2313 l_attribute_category(i)
2314 := p_payInstrTab(i).attribute_category;
2315 l_attribute1(i)
2316 := p_payInstrTab(i).attribute1;
2317 l_attribute2(i)
2318 := p_payInstrTab(i).attribute2;
2319 l_attribute3(i)
2320 := p_payInstrTab(i).attribute3;
2321 l_attribute4(i)
2322 := p_payInstrTab(i).attribute4;
2323 l_attribute5(i)
2324 := p_payInstrTab(i).attribute5;
2325 l_attribute6(i)
2326 := p_payInstrTab(i).attribute6;
2327 l_attribute7(i)
2328 := p_payInstrTab(i).attribute7;
2329 l_attribute8(i)
2330 := p_payInstrTab(i).attribute8;
2331 l_attribute9(i)
2332 := p_payInstrTab(i).attribute9;
2333 l_attribute10(i)
2334 := p_payInstrTab(i).attribute10;
2335 l_attribute11(i)
2336 := p_payInstrTab(i).attribute11;
2337 l_attribute12(i)
2338 := p_payInstrTab(i).attribute12;
2339 l_attribute13(i)
2340 := p_payInstrTab(i).attribute13;
2341 l_attribute14(i)
2342 := p_payInstrTab(i).attribute14;
2343 l_attribute15(i)
2344 := p_payInstrTab(i).attribute15;
2345
2346 END LOOP;
2347
2348
2349 FORALL i in p_payInstrTab.FIRST..p_payInstrTab.LAST
2350 INSERT INTO IBY_PAY_INSTRUCTIONS_ALL
2351 (
2352 payment_instruction_id,
2353 payment_profile_id,
2354 process_type,
2355 payment_instruction_status,
2356 payments_complete_code,
2357 generate_sep_remit_advice_flag,
2358 remittance_advice_created_flag,
2359 regulatory_report_created_flag,
2360 bill_payable_flag,
2361 legal_entity_id,
2362 payment_count,
2363 positive_pay_file_created_flag,
2364 print_instruction_immed_flag,
2365 transmit_instr_immed_flag,
2366 created_by,
2367 creation_date,
2368 last_updated_by,
2369 last_update_date,
2370 last_update_login,
2371 object_version_number,
2372 internal_bank_account_id,
2373 pay_admin_assigned_ref_code,
2374 transmission_date,
2375 acknowledgement_date,
2376 comments,
2377 bank_assigned_ref_code,
2378 org_id,
2379 org_type,
2380 payment_date,
2381 payment_currency_code,
2382 payment_service_request_id,
2383 payment_function,
2384 payment_reason_code,
2385 rfc_identifier,
2386 payment_method_code,
2387 payment_reason_comments,
2388 payment_document_id,
2389 printer_name,
2390 attribute_category,
2391 attribute1,
2392 attribute2,
2393 attribute3,
2394 attribute4,
2395 attribute5,
2396 attribute6,
2397 attribute7,
2398 attribute8,
2399 attribute9,
2400 attribute10,
2401 attribute11,
2402 attribute12,
2403 attribute13,
2404 attribute14,
2405 attribute15
2406 )
2407 VALUES
2408 (
2409 l_payment_instruction_id(i),
2410 l_payment_profile_id(i),
2411 l_process_type(i),
2412 l_payment_instruction_status(i),
2413 l_payments_complete_code(i),
2414 l_gen_sep_remit_advice_flag(i),
2415 l_remit_advice_created_flag(i),
2416 l_regul_rpt_created_flag(i),
2417 l_bill_payable_flag(i),
2418 l_legal_entity_id(i),
2419 l_payment_count(i),
2420 l_pos_pay_file_created_flag(i),
2421 l_print_instr_immed_flag(i),
2422 l_transmit_instr_immed_flag(i),
2423 l_created_by(i),
2424 l_creation_date(i),
2425 l_last_updated_by(i),
2426 l_last_update_date(i),
2427 l_last_update_login(i),
2428 l_object_version_number(i),
2429 l_internal_bank_account_id(i),
2430 l_pay_admin_assigned_ref_code(i),
2431 l_transmission_date(i),
2432 l_acknowledgement_date(i),
2433 l_comments(i),
2434 l_bank_assigned_ref_code(i),
2435 l_org_id(i),
2436 l_org_type(i),
2437 l_payment_date(i),
2438 l_payment_currency_code(i),
2439 l_payment_service_request_id(i),
2440 l_payment_function(i),
2441 l_payment_reason_code(i),
2442 l_rfc_identifier(i),
2443 l_pmt_method_code(i),
2444 l_payment_reason_comments(i),
2445 l_payment_document_id(i),
2446 l_printer_name(i),
2447 l_attribute_category(i),
2448 l_attribute1(i),
2449 l_attribute2(i),
2450 l_attribute3(i),
2451 l_attribute4(i),
2452 l_attribute5(i),
2453 l_attribute6(i),
2454 l_attribute7(i),
2455 l_attribute8(i),
2456 l_attribute9(i),
2457 l_attribute10(i),
2458 l_attribute11(i),
2459 l_attribute12(i),
2460 l_attribute13(i),
2461 l_attribute14(i),
2462 l_attribute15(i)
2463 )
2464 ;
2465
2466 print_debuginfo(l_module_name, 'EXIT');
2467
2468 END insertPaymentInstructions;
2469
2470 /*--------------------------------------------------------------------
2471 | NAME:
2472 | updatePaymentInstructions
2473 |
2474 | PURPOSE:
2475 | Performs an update of all created instructions from PLSQL
2476 | table into IBY_PAY_INSTRUCTIONS_ALL table.
2477 |
2478 | The created instructions have already been inserted into
2479 | IBY_PAY_INSTRUCTIONS_ALL after grouping (and before validation).
2480 | So we only need to update certain fields of the instruction
2481 | that have been changed after the grouping was performed.
2482 |
2483 | PARAMETERS:
2484 | IN
2485 |
2486 |
2487 | OUT
2488 |
2489 |
2490 | RETURNS:
2491 |
2492 | NOTES:
2493 |
2494 *---------------------------------------------------------------------*/
2495 PROCEDURE updatePaymentInstructions(
2496 p_payInstrTab IN pmtInstrTabType
2497 )
2498 IS
2499 l_module_name VARCHAR2(200) := G_PKG_NAME || '.updatePaymentInstructions';
2500
2501 BEGIN
2502
2503 print_debuginfo(l_module_name, 'ENTER');
2504
2505 /* Normally, this shouldn't happen */
2506 IF (p_payInstrTab.COUNT = 0) THEN
2507 print_debuginfo(l_module_name, 'No payment instructions'
2508 || ' were found to update IBY_PAY_INSTRUCTIONS_ALL table.');
2509 RETURN;
2510 END IF;
2511
2512 FOR i in p_payInstrTab.FIRST..p_payInstrTab.LAST LOOP
2513
2514 UPDATE
2515 IBY_PAY_INSTRUCTIONS_ALL
2516 SET
2517 payment_instruction_status =
2518 p_payInstrTab(i).payment_instruction_status,
2519 generate_sep_remit_advice_flag =
2520 p_payInstrTab(i).generate_sep_remit_advice_flag
2521 WHERE
2522 payment_instruction_id = p_payInstrTab(i).payment_instruction_id
2523 ;
2524
2525 END LOOP;
2526
2527 print_debuginfo(l_module_name, 'EXIT');
2528
2529 END updatePaymentInstructions;
2530
2531 /*--------------------------------------------------------------------
2532 | NAME:
2533 | getNextPaymentInstructionID
2534 |
2535 | PURPOSE:
2536 |
2537 |
2538 | PARAMETERS:
2539 | IN
2540 |
2541 |
2542 | OUT
2543 |
2544 |
2545 | RETURNS:
2546 |
2547 | NOTES:
2548 |
2549 *---------------------------------------------------------------------*/
2550 PROCEDURE getNextPaymentInstructionID(
2551 x_pmtInstrID IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL.
2552 payment_instruction_id%TYPE
2553 )
2554 IS
2555
2556 BEGIN
2557
2558 SELECT
2559 IBY_PAY_INSTRUCTIONS_ALL_S.NEXTVAL
2560 INTO
2561 x_pmtInstrID
2562 FROM
2563 DUAL
2564 ;
2565
2566 END getNextPaymentInstructionID;
2567
2568 /*--------------------------------------------------------------------
2569 | NAME:
2570 | updatePmtsWithInstructionID
2571 |
2572 | PURPOSE:
2573 |
2574 |
2575 |
2576 | PARAMETERS:
2577 | IN
2578 |
2579 | OUT
2580 |
2581 |
2582 | RETURNS:
2583 |
2584 | NOTES:
2585 |
2586 *---------------------------------------------------------------------*/
2587 PROCEDURE updatePmtsWithInstructionID(
2588 p_pmtsInPayInstTab IN pmtsInPmtInstrTabType
2589 )
2590 IS
2591 l_module_name VARCHAR2(200) := G_PKG_NAME || '.updatePmtsWithInstructionID';
2592
2593 BEGIN
2594
2595 print_debuginfo(l_module_name, 'ENTER');
2596
2597 /* Normally, this should not happen */
2598 IF (p_pmtsInPayInstTab.COUNT = 0) THEN
2599 print_debuginfo(l_module_name, 'After grouping, no '
2600 || 'payments provided to update '
2601 || 'IBY_PAYMENTS_ALL table. Possible data '
2602 || 'corruption issue.');
2603 RETURN;
2604 END IF;
2605
2606 /*
2607 * Update the payments. We cannot use bulk update here
2608 * because the bulk update syntax does not allow us to
2609 * reference individual fields of the PL/SQL record.
2610 *
2611 * TBD: Is there any way to optimize this update?
2612 */
2613 FOR i in p_pmtsInPayInstTab.FIRST..p_pmtsInPayInstTab.LAST LOOP
2614
2615 print_debuginfo(l_module_name, 'Instruction: '
2616 || p_pmtsInPayInstTab(i).pay_instr_id || ', payment: '
2617 || p_pmtsInPayInstTab(i).payment_id);
2618
2619 UPDATE
2620 IBY_PAYMENTS_ALL
2621 SET
2622 payment_instruction_id = p_pmtsInPayInstTab(i).pay_instr_id,
2623 paper_document_number = p_pmtsInPayInstTab(i).check_number,
2624 payment_status = p_pmtsInPayInstTab(i).payment_status,
2625
2626 /*
2627 * Fix for bug 5467767:
2628 *
2629 * The payer abbreviated agency code and payer
2630 * federal employer number need to be populated
2631 * on the payment as these are required by some
2632 * formats.
2633 *
2634 * Populate them here because the functions that
2635 * retrieve these values need the payment instruction
2636 * id as an input param.
2637 */
2638 payer_abbreviated_agency_code =
2639 IBY_FD_EXTRACT_GEN_PVT.
2640 Get_Abbreviated_Agency_Code(
2641 p_pmtsInPayInstTab(i).pay_instr_id),
2642
2643 payer_federal_us_employer_id =
2644 IBY_FD_EXTRACT_GEN_PVT.
2645 Get_FEIN(
2646 p_pmtsInPayInstTab(i).pay_instr_id)
2647
2648 WHERE
2649 payment_id = p_pmtsInPayInstTab(i).payment_id
2650 ;
2651
2652 END LOOP;
2653
2654 print_debuginfo(l_module_name, 'EXIT');
2655
2656 END updatePmtsWithInstructionID;
2657
2658 /*--------------------------------------------------------------------
2659 | NAME:
2660 | getFxAmount
2661 |
2662 | PURPOSE:
2663 |
2664 |
2665 |
2666 | PARAMETERS:
2667 | IN
2668 |
2669 |
2670 | OUT
2671 |
2672 |
2673 | RETURNS:
2674 |
2675 | NOTES:
2676 | No exception will be raised in this function if the call to
2677 | GL API to convert the amount fails; Instead '-1' will be
2678 | for the amount. The caller should recognize this and raise
2679 | an exception.
2680 |
2681 *---------------------------------------------------------------------*/
2682 FUNCTION getFxAmount(
2683 p_source_currency IN VARCHAR2,
2684 p_target_currency IN VARCHAR2,
2685 p_exch_rate_date IN DATE,
2686 p_exch_rate_type IN VARCHAR2,
2687 p_source_amount IN NUMBER
2688 ) RETURN NUMBER
2689
2690 IS
2691 l_module_name VARCHAR2(200) := G_PKG_NAME || '.getFxAmount';
2692 l_fx_amount NUMBER(15);
2693
2694 BEGIN
2695
2696 /*
2697 * Cannot log anywhere within this function because that
2698 * violates pragma restrict_references!
2699 */
2700
2701 l_fx_amount := gl_currency_api.convert_amount(
2702 p_source_currency,
2703 p_target_currency,
2704 p_exch_rate_date,
2705 p_exch_rate_type,
2706 p_source_amount
2707 );
2708
2709 RETURN l_fx_amount;
2710
2711 EXCEPTION
2712
2713 WHEN OTHERS THEN
2714 /*
2715 * The GL convert_amount() API enforces pragma
2716 * restrict_references. So we cannot raise an
2717 * exception here.
2718 *
2719 * Instead log the exception and pass -1 as the
2720 * amount. The caller should recognize that -1
2721 * indicates an exception occured.
2722 */
2723 l_fx_amount := -1;
2724
2725 RETURN l_fx_amount;
2726
2727 END getFxAmount;
2728
2729
2730 /*--------------------------------------------------------------------
2731 | Name : createLogicalGroups
2732 |
2733 | Purpose : To create logical groups in payment instruction (for SEPA)
2734 |
2735 | Parameters:
2736 | IN:
2737 | x_pmtInstrTab -- Table of payment instruction
2738 |
2739 | OUT:
2740 | N/A
2741 |
2742 |
2743 |
2744 *-----------------------------------------------------------------------*/
2745 PROCEDURE createLogicalGroups(
2746 x_pmtInstrTab IN pmtInstrTabType
2747 )
2748 IS
2749
2750 l_grouping_mode varchar2(40);
2751 l_payment_profile_code IBY_ACCT_PMT_PROFILES_B.SYSTEM_PROFILE_CODE%TYPE;
2752 select_clause VARCHAR(4000);
2753 into_clause VARCHAR(4000);
2754 from_clause VARCHAR(4000);
2755 where_clause VARCHAR(4000);
2756 order_clause VARCHAR(4000);
2757
2758
2759 l_payment_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE;
2760 l_grp_cntr NUMBER;
2761 l_logical_group_reference IBY_PAYMENTS_ALL.LOGICAL_GROUP_REFERENCE%TYPE;
2762 l_profileRec IBY_SYS_PMT_PROFILES_B%ROWTYPE;
2763
2764 TYPE type_payment_id IS TABLE OF
2765 IBY_PAYMENTS_ALL.payment_id%TYPE
2766 INDEX BY BINARY_INTEGER;
2767 t_payment_id type_payment_id;
2768
2769 TYPE type_logical_group_reference IS TABLE OF
2770 IBY_PAYMENTS_ALL.logical_group_reference%TYPE
2771 INDEX BY BINARY_INTEGER;
2772 t_logical_group_reference type_logical_group_reference;
2773
2774 TYPE type_legal_entity_id IS TABLE OF
2775 iby_payments_all.legal_entity_id%TYPE
2776 INDEX BY BINARY_INTEGER;
2777 t_legal_entity_id type_legal_entity_id;
2778
2779 TYPE type_payment_currency_code IS TABLE OF
2780 iby_payments_all.payment_currency_code%TYPE
2781 INDEX BY BINARY_INTEGER;
2782 t_payment_currency_code type_payment_currency_code;
2783
2784 TYPE type_payment_method_code IS TABLE OF
2785 iby_payments_all.payment_method_code%TYPE
2786 INDEX BY BINARY_INTEGER;
2787 t_payment_method_code type_payment_method_code;
2788
2789 TYPE type_payment_date IS TABLE OF
2790 iby_payments_all.payment_date%TYPE
2791 INDEX BY BINARY_INTEGER;
2792 t_payment_date type_payment_date;
2793
2794 TYPE type_payment_reason_code IS TABLE OF
2795 iby_payments_all.payment_reason_code%TYPE
2796 INDEX BY BINARY_INTEGER;
2797 t_payment_reason_code type_payment_reason_code;
2798
2799 TYPE type_internal_bank_account_id IS TABLE OF
2800 iby_payments_all.internal_bank_account_id%TYPE
2801 INDEX BY BINARY_INTEGER;
2802 t_internal_bank_account_id type_internal_bank_account_id;
2803
2804 TYPE type_ext_payee_id IS TABLE OF
2805 iby_payments_all.ext_payee_id%TYPE
2806 INDEX BY BINARY_INTEGER;
2807 t_ext_payee_id type_ext_payee_id;
2808
2809 TYPE type_org_id IS TABLE OF
2810 iby_payments_all.org_id%TYPE
2811 INDEX BY BINARY_INTEGER;
2812 t_org_id type_org_id;
2813
2814 l_group_by_legal_entity iby_pmt_logical_grp_rules.group_by_legal_entity%TYPE;
2815 l_group_by_payment_method iby_pmt_logical_grp_rules.group_by_payment_method%TYPE;
2816 l_group_by_payment_date iby_pmt_logical_grp_rules.group_by_payment_date%TYPE;
2817 l_group_by_internal_bank_acct iby_pmt_logical_grp_rules.group_by_internal_bank_account%TYPE;
2818 l_group_by_operating_unit iby_pmt_logical_grp_rules.group_by_operating_unit%TYPE;
2819
2820 l_first_group_by VARCHAR2(1);
2821
2822 prev_legal_entity_id iby_payments_all.legal_entity_id%TYPE;
2823 prev_payment_method_code iby_payments_all.payment_method_code%TYPE;
2824 prev_payment_date iby_payments_all.payment_date%TYPE;
2825 prev_internal_bank_account_id iby_payments_all.internal_bank_account_id%TYPE;
2826 prev_ext_payee_id iby_payments_all.ext_payee_id%TYPE;
2827
2828
2829 l_module_name VARCHAR2(200) := G_PKG_NAME || '.createLogicalGroups';
2830
2831 BEGIN
2832
2833 print_debuginfo(l_module_name, 'ENTER');
2834
2835 FOR i in x_pmtInstrTab.FIRST .. x_pmtInstrTab.LAST LOOP
2836
2837 l_payment_instruction_id := x_pmtInstrTab(i).payment_instruction_id;
2838
2839 SELECT sysprf.logical_grouping_mode
2840 , sysprf.system_profile_code
2841 INTO l_grouping_mode
2842 , l_payment_profile_code
2843 FROM IBY_SYS_PMT_PROFILES_B sysprf
2844 , IBY_ACCT_PMT_PROFILES_B actprf
2845 WHERE actprf.PAYMENT_PROFILE_ID = x_pmtInstrTab(i).payment_profile_id
2846 AND actprf.SYSTEM_PROFILE_CODE = sysprf.system_profile_code;
2847
2848 print_debuginfo(l_module_name, 'Instruction: '
2849 || l_payment_instruction_id || ', Grouping Mode: '
2850 || l_grouping_mode);
2851 IF (l_grouping_mode IS NOT NULL) THEN
2852 IF l_grouping_mode = 'SNGL' THEN
2853
2854 SELECT PAYMENT_ID BULK COLLECT
2855 INTO t_payment_id
2856 FROM IBY_PAYMENTS_ALL
2857 WHERE PAYMENT_INSTRUCTION_ID = l_payment_instruction_id;
2858
2859 FOR j in t_payment_id.FIRST .. t_payment_id.LAST
2860 LOOP
2861 t_logical_group_reference(j) := l_payment_instruction_id||'_'||j;
2862 print_debuginfo(l_module_name, 'payment_id: '
2863 || t_payment_id(j) || ', logical_grp_ref: '
2864 || t_logical_group_reference(j));
2865
2866 END LOOP;
2867
2868 FORALL k IN t_payment_id.FIRST .. t_payment_id.LAST
2869 UPDATE IBY_PAYMENTS_ALL
2870 SET LOGICAL_GROUP_REFERENCE = t_logical_group_reference(k)
2871 WHERE payment_id = t_payment_id(k) ;
2872
2873 ELSIF l_grouping_mode = 'GRPD' THEN
2874
2875 l_logical_group_reference := l_payment_instruction_id||'_1';
2876
2877 print_debuginfo(l_module_name, 'logical_grp_ref: '
2878 || l_logical_group_reference );
2879
2880 UPDATE IBY_PAYMENTS_ALL
2881 SET logical_group_reference = l_logical_group_reference
2882 WHERE payment_instruction_id = l_payment_instruction_id;
2883
2884 ELSIF l_grouping_mode = 'MIXD' THEN
2885
2886 /* bug 8208986 - the previous values are made to hold '' so that the
2887 * first payment in an instruction
2888 * always has a new logical group id
2889 */
2890
2891 prev_legal_entity_id :='';
2892 prev_payment_method_code := '';
2893 prev_payment_date :='';
2894 prev_internal_bank_account_id :='';
2895
2896
2897 SELECT nvl(group_by_legal_entity, 'N'),
2898 nvl(group_by_payment_method, 'N'),
2899 nvl(group_by_payment_date, 'N'),
2900 nvl(group_by_internal_bank_account, 'N')
2901 INTO l_group_by_legal_entity,
2902 l_group_by_payment_method,
2903 l_group_by_payment_date,
2904 l_group_by_internal_bank_acct
2905 FROM IBY_PMT_LOGICAL_GRP_RULES
2906 WHERE SYSTEM_PROFILE_CODE = l_payment_profile_code;
2907
2908 from_clause :=' FROM IBY_PAYMENTS_ALL';
2909 order_clause := ' ORDER BY ';
2910
2911 l_first_group_by := 'Y';
2912
2913 IF l_group_by_legal_entity = 'Y' THEN
2914 order_clause := order_clause || ' legal_entity_id';
2915 l_first_group_by := 'N';
2916 END IF;
2917
2918
2919 IF l_group_by_payment_method = 'Y' THEN
2920 IF l_first_group_by = 'Y' THEN
2921 order_clause := order_clause || ' PAYMENT_METHOD_CODE';
2922 l_first_group_by := 'N';
2923 ELSE
2924 order_clause := order_clause || ' , PAYMENT_METHOD_CODE';
2925 END IF;
2926 END IF;
2927
2928 IF l_group_by_payment_date = 'Y' THEN
2929 IF l_first_group_by = 'Y' THEN
2930 order_clause := order_clause || ' PAYMENT_DATE';
2931 l_first_group_by := 'N';
2932 ELSE
2933 order_clause := order_clause || ' , PAYMENT_DATE';
2934 END IF;
2935 END IF;
2936
2937 IF l_group_by_internal_bank_acct = 'Y' THEN
2938 IF l_first_group_by = 'Y' THEN
2939 order_clause := order_clause || ' INTERNAL_BANK_ACCOUNT_ID';
2940 l_first_group_by := 'N';
2941 ELSE
2942 order_clause := order_clause || ' , INTERNAL_BANK_ACCOUNT_ID';
2943 END IF;
2944 END IF;
2945
2946
2947 where_clause := ' WHERE payment_instruction_id = ' || l_payment_instruction_id;
2948
2949 select_clause := 'SELECT PAYMENT_ID
2950 , legal_entity_id
2951 , PAYMENT_METHOD_CODE
2952 , PAYMENT_DATE
2953 , INTERNAL_BANK_ACCOUNT_ID'
2954 ;
2955
2956 print_debuginfo(l_module_name, 'select_clause: '
2957 || select_clause);
2958 print_debuginfo(l_module_name, 'from_clause: '
2959 || from_clause);
2960 print_debuginfo(l_module_name, 'where_clause: '
2961 || where_clause);
2962 print_debuginfo(l_module_name, 'order_clause: '
2963 || order_clause);
2964
2965 /*
2966 EXECUTE IMMEDIATE select_clause
2967 || into_clause
2968 || from_clause
2969 || where_clause
2970 || order_clause;
2971 */
2972
2973 EXECUTE IMMEDIATE select_clause
2974 || from_clause
2975 || where_clause
2976 || order_clause
2977 BULK COLLECT INTO t_payment_id
2978 , t_legal_entity_id
2979 , t_payment_method_code
2980 , t_payment_date
2981 , t_internal_bank_account_id
2982 ;
2983
2984 l_grp_cntr := 0;
2985
2986 FOR j in t_payment_id.FIRST .. t_payment_id.LAST
2987 LOOP
2988
2989 print_debuginfo(l_module_name, 'The values for current payment record: t_legal_entity_id:'||t_legal_entity_id(j) ||',t_payment_method_code:'
2990 ||t_payment_method_code(j)||',t_payment_date: '||t_payment_date(j)
2991 ||',t_internal_bank_account_id: '|| t_internal_bank_account_id(j)) ;
2992 print_debuginfo(l_module_name, 'The values for previous payment record: prev_legal_entity_id:' || prev_legal_entity_id ||',prev_payment_method_code:'
2993 || prev_payment_method_code ||',prev_payment_date:' || prev_payment_date
2994 ||',prev_internal_bank_account_id: '|| prev_internal_bank_account_id) ;
2995 IF t_legal_entity_id(j) = prev_legal_entity_id
2996 AND t_payment_method_code(j) = prev_payment_method_code
2997 AND t_payment_date(j) = prev_payment_date
2998 AND t_internal_bank_account_id(j) = prev_internal_bank_account_id
2999
3000 THEN
3001 t_logical_group_reference(j) := l_logical_group_reference;
3002 print_debuginfo(l_module_name, 'The prev and current payment have same values for grouping attributes. payment_id: '
3003 || t_payment_id(j) || ', logical_grp_ref: '
3004 || t_logical_group_reference(j));
3005
3006 ELSE
3007 prev_legal_entity_id := t_legal_entity_id(j);
3008 prev_payment_method_code := t_payment_method_code(j);
3009 prev_payment_date := t_payment_date(j);
3010 prev_internal_bank_account_id := t_internal_bank_account_id(j);
3011 l_grp_cntr := l_grp_cntr + 1;
3012 l_logical_group_reference := l_payment_instruction_id ||'_'|| l_grp_cntr;
3013 t_logical_group_reference(j) := l_logical_group_reference;
3014 print_debuginfo(l_module_name, ' The prev and current payment have different values for grouping attributes. payment_id: '
3015 || t_payment_id(j) || ', logical_grp_ref: '
3016 || t_logical_group_reference(j));
3017
3018 END IF;
3019 END LOOP;
3020
3021 FORALL j IN t_payment_id.FIRST .. t_payment_id.LAST
3022 UPDATE IBY_PAYMENTS_ALL
3023 SET logical_group_reference = t_logical_group_reference(j)
3024 WHERE payment_id = t_payment_id(j);
3025
3026 END IF;
3027 END IF;
3028 END LOOP;
3029
3030 END createLogicalGroups;
3031
3032
3033 /*--------------------------------------------------------------------
3034 | NAME:
3035 | performInstructionValidations
3036 |
3037 | PURPOSE:
3038 |
3039 |
3040 |
3041 | PARAMETERS:
3042 | IN
3043 |
3044 | OUT
3045 |
3046 |
3047 | RETURNS:
3048 |
3049 | NOTES:
3050 |
3051 *---------------------------------------------------------------------*/
3052 PROCEDURE performInstructionValidations(
3053 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
3054 x_pmtsInPmtInstrTab IN OUT NOCOPY pmtsInPmtInstrTabType,
3055 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
3056 docErrorTabType,
3057 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
3058 trxnErrTokenTabType
3059 )
3060 IS
3061
3062 l_module_name VARCHAR2(200) := G_PKG_NAME || '.performInstructionValidations';
3063
3064 BEGIN
3065
3066 print_debuginfo(l_module_name, 'ENTER');
3067
3068 /*--- uncomment for debugging ----*/
3069 /*
3070 FOR i in x_pmtInstrTab.FIRST..x_pmtInstrTab.LAST LOOP
3071 print_debuginfo(l_module_name, 'Instruction id: '
3072 || x_pmtInstrTab(i).instruction_id
3073 || ', payment count: '
3074 || x_pmtInstrTab(i).payment_count
3075 );
3076 END LOOP;
3077 */
3078 /*--- end debug ----*/
3079
3080 insertPaymentInstructions(x_pmtInstrTab);
3081
3082 updatePmtsWithInstructionID(x_pmtsInPmtInstrTab);
3083
3084 applyPayInstrValidationSets(x_pmtInstrTab, x_docErrorTab, x_errTokenTab);
3085
3086 /*------ This procedure creates logical groups under each payment instruction- SEPA --------*/
3087 createLogicalGroups(x_pmtInstrTab);
3088
3089 print_debuginfo(l_module_name, 'EXIT');
3090
3091 END performInstructionValidations;
3092
3093
3094 /*--------------------------------------------------------------------
3095 | NAME:
3096 | performDBUpdates
3097 |
3098 | PURPOSE:
3099 |
3100 |
3101 |
3102 |
3103 | PARAMETERS:
3104 | IN
3105 |
3106 |
3107 | OUT
3108 |
3109 |
3110 | RETURNS:
3111 |
3112 |
3113 | NOTES:
3114 |
3115 *---------------------------------------------------------------------*/
3116 PROCEDURE performDBUpdates(
3117 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
3118 x_pmtsInPmtInstrTab IN OUT NOCOPY pmtsInPmtInstrTabType,
3119 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
3120 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.
3121 trxnErrTokenTabType,
3122 p_profileMap IN IBY_BUILD_UTILS_PKG.profileIdToCodeMapTabType,
3123 x_return_status IN OUT NOCOPY VARCHAR2
3124 )
3125 IS
3126 l_module_name VARCHAR2(200) := G_PKG_NAME || '.performDBUpdates';
3127 l_sorted_pmts_tab sortedPmtTabType;
3128
3129 BEGIN
3130
3131 print_debuginfo(l_module_name, 'ENTER');
3132
3133 /*
3134 * Update the payments table by providing a instruction id to
3135 * each payment.
3136 */
3137 updatePmtsWithInstructionID(x_pmtsInPmtInstrTab);
3138
3139 /*
3140 * Get the payments for each payment instruction in sorted
3141 * order. This method will also assign payment references to
3142 * the sorted payments.
3143 */
3144 performSortedPaymentNumbering(x_pmtInstrTab, l_sorted_pmts_tab,
3145 p_profileMap, x_docErrorTab, x_errTokenTab);
3146
3147 /*
3148 * Update individual payments with their document sequence numbers
3149 * and payment references.
3150 */
3151 updatePmtsWithSeqNumPmtRef(l_sorted_pmts_tab);
3152
3153 /*
3154 * All payment instructions for this run have been
3155 * created and stored in a PLSQL table. These instructions
3156 * have already been written into the IBY_PAY_INSTRUCTIONS_ALL
3157 * table just before validations. Now update these
3158 * payment instructions with any additional information.
3159 *
3160 * E.g., the payment instruction could have been failed because
3161 * the document sequencing API call failed. Therefore, the
3162 * payment instruction status needs to be updated.
3163 */
3164 updatePaymentInstructions(x_pmtInstrTab);
3165
3166 /*
3167 * If any payment instructions/payments were failed, the
3168 * IBY_TRANSACTION_ERRORS table must be populated with the
3169 * corresponding error messages.
3170 */
3171 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N', x_docErrorTab,
3172 x_errTokenTab);
3173
3174 /* Pass back the return status to the caller */
3175 x_return_status := FND_API.G_RET_STS_SUCCESS;
3176
3177 print_debuginfo(l_module_name, 'EXIT');
3178
3179 EXCEPTION
3180
3181 WHEN OTHERS THEN
3182 print_debuginfo(l_module_name, 'Fatal: Exception when updating '
3183 || 'payment instruction/payment status after payment '
3184 || 'instruction creation. All changes will be rolled back.',
3185 FND_LOG.LEVEL_UNEXPECTED
3186 );
3187 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
3188 FND_LOG.LEVEL_UNEXPECTED);
3189 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
3190 FND_LOG.LEVEL_UNEXPECTED);
3191
3192 /*
3193 * Propogate exception to caller.
3194 */
3195 RAISE;
3196
3197 END performDBUpdates;
3198
3199 /*--------------------------------------------------------------------
3200 | NAME:
3201 | applyPayInstrValidationSets
3202 |
3203 | PURPOSE:
3204 |
3205 |
3206 |
3207 | PARAMETERS:
3208 | IN
3209 |
3210 | OUT
3211 |
3212 |
3213 | RETURNS:
3214 |
3215 | NOTES:
3216 |
3217 *---------------------------------------------------------------------*/
3218 PROCEDURE applyPayInstrValidationSets(
3219 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
3220 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
3221 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType
3222 )
3223 IS
3224
3225 l_module_name VARCHAR2(200) := G_PKG_NAME || '.applyPayInstrValidationSets';
3226 l_valSetsTab instructionValSetsTab;
3227 l_stmt VARCHAR2(200);
3228 l_result NUMBER := 0;
3229
3230 l_doc_err_rec IBY_TRANSACTION_ERRORS%ROWTYPE;
3231 l_error_code VARCHAR2(100);
3232 l_error_msg VARCHAR2(500);
3233
3234 /*
3235 * Pick up all validation sets applicable to a particular instruction.
3236 */
3237
3238 /*
3239 * Fix for bug 5041372:
3240 *
3241 * For instructions, the validation level code has been changed
3242 * from 'INSTRUCTION' to 'DISBURSEMENT_INSTRUCTION'.
3243 */
3244 CURSOR c_instr_val_sets(p_instr_id IBY_PAY_INSTRUCTIONS_ALL.
3245 payment_instruction_id%TYPE)
3246 IS
3247 SELECT DISTINCT
3248 val_asgn.validation_assignment_id,
3249 val.validation_set_code,
3250 val.validation_code_package,
3251 val.validation_code_entry_point
3252 FROM
3253 IBY_VALIDATION_SETS_VL val,
3254 IBY_VAL_ASSIGNMENTS val_asgn,
3255 IBY_PAY_INSTRUCTIONS_ALL pmt_instr,
3256 IBY_PAYMENT_PROFILES prof,
3257 IBY_TRANSMIT_CONFIGS_VL txconf,
3258 IBY_TRANSMIT_PROTOCOLS_VL txproto,
3259 IBY_PAYMENTS_SEC_V pmt
3260 WHERE
3261 pmt_instr.payment_instruction_id = p_instr_id
3262 AND pmt.payment_instruction_id = pmt_instr.payment_instruction_id
3263 AND val.validation_set_code = val_asgn.validation_set_code
3264 AND val.validation_level_code = 'DISBURSEMENT_INSTRUCTION'
3265 AND (val_asgn.val_assignment_entity_type = 'INTBANKACCOUNT'
3266 AND val_asgn.assignment_entity_id =
3267 pmt_instr.internal_bank_account_id
3268 OR val_asgn.val_assignment_entity_type = 'FORMAT'
3269 AND val_asgn.assignment_entity_id =
3270 prof.payment_format_code
3271 OR val_asgn.val_assignment_entity_type = 'BANK'
3272 AND val_asgn.assignment_entity_id =
3273 prof.bepid
3274 OR val_asgn.val_assignment_entity_type = 'TRANSPROTOCOL'
3275 AND val_asgn.assignment_entity_id =
3276 txconf.transmit_protocol_code
3277 OR val_asgn.val_assignment_entity_type = 'METHOD'
3278 AND val_asgn.assignment_entity_id =
3279 pmt.payment_method_code
3280 )
3281 AND pmt_instr.payment_profile_id = prof.payment_profile_id(+)
3282 AND prof.transmit_configuration_id = txconf.transmit_configuration_id(+)
3283 AND txconf.transmit_protocol_code = txproto.transmit_protocol_code(+)
3284 AND NVL(val_asgn.inactive_date, sysdate+1) > sysdate
3285 ;
3286
3287 BEGIN
3288
3289 print_debuginfo(l_module_name, 'ENTER');
3290
3291 print_debuginfo(l_module_name, 'Fresh validation scenario. '
3292 || 'All applicable validations will be applied.'
3293 );
3294
3295 FOR i in x_pmtInstrTab.FIRST .. x_pmtInstrTab.LAST LOOP
3296
3297 /*
3298 * Pick up the validation sets applicable to each
3299 * instruction.
3300 */
3301 OPEN c_instr_val_sets(x_pmtInstrTab(i).payment_instruction_id);
3302 FETCH c_instr_val_sets BULK COLLECT INTO l_valSetsTab;
3303 CLOSE c_instr_val_sets;
3304
3305 IF (l_valSetsTab.COUNT = 0) THEN
3306
3307 print_debuginfo(l_module_name, 'No validation sets were '
3308 || 'linked to instruction '
3309 || x_pmtInstrTab(i).payment_instruction_id
3310 || '. Skipping instruction '
3311 || 'validations for this instruction ..');
3312
3313 print_debuginfo(l_module_name, '+-------------------------+');
3314
3315 ELSE
3316
3317 /*
3318 * Invoke the validation sets applicable to this
3319 * payment instruction one-by-one.
3320 */
3321 executeValidationsForInstr(
3322 x_pmtInstrTab(i),
3323 l_valSetsTab,
3324 FALSE,
3325 x_docErrorTab,
3326 x_errTokenTab
3327 );
3328
3329 END IF; -- if count of val sets <> 0
3330
3331 END LOOP; -- for each payment instruction in request
3332
3333 print_debuginfo(l_module_name, 'EXIT');
3334
3335 END applyPayInstrValidationSets;
3336
3337 /*--------------------------------------------------------------------
3338 | NAME:
3339 | reApplyPayInstrValidationSets
3340 |
3341 | PURPOSE:
3342 |
3343 |
3344 |
3345 | PARAMETERS:
3346 | IN
3347 |
3348 | OUT
3349 |
3350 |
3351 | RETURNS:
3352 |
3353 | NOTES:
3354 |
3355 *---------------------------------------------------------------------*/
3356 PROCEDURE reApplyPayInstrValidationSets(
3357 x_pmtInstrRec IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE,
3358 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
3359 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType
3360 )
3361 IS
3362
3363 l_module_name VARCHAR2(200) := G_PKG_NAME ||
3364 '.reApplyPayInstrValidationSets';
3365 l_valSetsTab instructionValSetsTab;
3366 l_stmt VARCHAR2(200);
3367 l_result NUMBER := 0;
3368
3369 /*
3370 * Pick up all validation sets applicable to a particular instruction
3371 * that have not overridden by the payment administrator.
3372 */
3373 CURSOR c_retry_instrvalsets(p_instr_id IBY_PAY_INSTRUCTIONS_ALL.
3374 payment_instruction_id%TYPE)
3375 IS
3376 SELECT DISTINCT
3377 val_asgn.validation_assignment_id,
3378 val.validation_set_code,
3379 val.validation_code_package,
3380 val.validation_code_entry_point
3381 FROM
3382 IBY_VALIDATION_SETS_VL val,
3383 IBY_VAL_ASSIGNMENTS val_asgn,
3384 IBY_PAY_INSTRUCTIONS_ALL pmt_instr,
3385 IBY_PAYMENT_PROFILES prof,
3386 IBY_TRANSMIT_CONFIGS_VL txconf,
3387 IBY_TRANSMIT_PROTOCOLS_VL txproto,
3388 IBY_TRANSACTION_ERRORS txerrors
3389 WHERE
3390 pmt_instr.payment_instruction_id = p_instr_id
3391 AND val.validation_set_code = val_asgn.validation_set_code
3392 AND val.validation_level_code = 'DISBURSEMENT_INSTRUCTION'
3393 AND (val_asgn.val_assignment_entity_type = 'INTBANKACCOUNT'
3394 AND val_asgn.assignment_entity_id =
3395 pmt_instr.internal_bank_account_id
3396 OR val_asgn.val_assignment_entity_type = 'FORMAT'
3397 AND val_asgn.assignment_entity_id =
3398 prof.payment_format_code
3399 OR val_asgn.val_assignment_entity_type = 'BANK'
3400 AND val_asgn.assignment_entity_id =
3401 prof.bepid
3402 OR val_asgn.val_assignment_entity_type = 'TRANSPROTOCOL'
3403 AND val_asgn.assignment_entity_id =
3404 txconf.transmit_protocol_code
3405 )
3406 AND pmt_instr.payment_profile_id = prof.payment_profile_id(+)
3407 AND prof.transmit_configuration_id = txconf.transmit_configuration_id(+)
3408 AND txconf.transmit_protocol_code = txproto.transmit_protocol_code(+)
3409 AND NVL(val_asgn.inactive_date, sysdate+1) > sysdate
3410 /*
3411 * Fix for bug 5206725:
3412 *
3413 * The set of conditions below will filter out validation sets
3414 * that have already been overridden by the user.
3415 */
3416 AND txerrors.transaction_type = 'PAYMENT_INSTRUCTION'
3417 AND txerrors.transaction_id = p_instr_id
3418 AND txerrors.error_type = 'VALIDATION'
3419 AND txerrors.validation_set_code = val.validation_set_code
3420 AND txerrors.do_not_apply_error_flag = 'N'
3421 ;
3422
3423 BEGIN
3424
3425 print_debuginfo(l_module_name, 'ENTER');
3426
3427 print_debuginfo(l_module_name, 'Validation re-entry scenario. '
3428 || 'Only non-overridden validations will be applied.'
3429 );
3430
3431 /*
3432 * Pick up the validation sets applicable to each
3433 * instruction.
3434 */
3435 OPEN c_retry_instrvalsets(x_pmtInstrRec.payment_instruction_id);
3436 FETCH c_retry_instrvalsets BULK COLLECT INTO l_valSetsTab;
3437 CLOSE c_retry_instrvalsets;
3438
3439 IF (l_valSetsTab.COUNT = 0) THEN
3440
3441 print_debuginfo(l_module_name, 'No validation sets were '
3442 || 'linked to instruction '
3443 || x_pmtInstrRec.payment_instruction_id
3444 || '. Skipping instruction '
3445 || 'validations for this instruction ..');
3446
3447 print_debuginfo(l_module_name, '+-------------------------+');
3448
3449 ELSE
3450
3451 /*
3452 * Invoke the validation sets applicable to this
3453 * payment instruction one-by-one.
3454 */
3455 executeValidationsForInstr(
3456 x_pmtInstrRec,
3457 l_valSetsTab,
3458 TRUE,
3459 x_docErrorTab,
3460 x_errTokenTab
3461 );
3462
3463 END IF; -- if count of val sets <> 0
3464
3465 print_debuginfo(l_module_name, 'After re-applying validations '
3466 || 'error messages count: '
3467 || x_docErrorTab.COUNT
3468 );
3469
3470 /*
3471 * Update the payment instruction status if there
3472 * were no errors.
3473 */
3474 IF (x_docErrorTab.COUNT = 0) THEN
3475
3476 /*
3477 * When a payment instruction enters this method,
3478 * it will be in CREATION_ERROR status. If after validation,
3479 * we find that no error messages were generated, then
3480 * we should update the status of this payment instruction
3481 * to CREATED.
3482 */
3483 print_debuginfo(l_module_name, 'Error count is zero '
3484 || 'indicating that this payment instruction passed '
3485 || 'all validations. '
3486 || 'Setting instruction status to CREATED.'
3487 );
3488
3489 x_pmtInstrRec.payment_instruction_status := INS_STATUS_CREATED;
3490
3491 ELSE
3492
3493 /*
3494 * We use the error count in the re-validation
3495 * flow purely to determine whether the payment
3496 * instruction generated any errors when the
3497 * validation sets were applied.
3498 *
3499 * If we get an error count greater than 0, we know
3500 * that this pmt instruction generated errors, and we
3501 * will continue to keep its status as
3502 * CREATION_ERROR.
3503 *
3504 * However, make sure to delete the PLSQL table of
3505 * errors. These errors were generated by the validation
3506 * sets and would have already been written into the
3507 * IBY_TRANSACTION_ERRORS table.
3508 *
3509 * If we don't clear out these errors here, the PICP
3510 * will try to insert these errors again in
3511 * performDBUpdates(..) and will fail with a unique
3512 * constraint violation because the errors already
3513 * exist.
3514 */
3515 x_docErrorTab.DELETE;
3516
3517 print_debuginfo(l_module_name, 'Cleared out error messages '
3518 || 'in memory because these were already stored in the '
3519 || 'in the DB.'
3520 );
3521
3522 END IF;
3523
3524 print_debuginfo(l_module_name, 'EXIT');
3525
3526 END reApplyPayInstrValidationSets;
3527
3528 /*--------------------------------------------------------------------
3529 | NAME:
3530 | executeValidationsForInstr
3531 |
3532 | PURPOSE:
3533 | This method is called to apply validation sets onto
3534 | a payment instructions.
3535 |
3536 | This method is called during both fresh validations and
3537 | re-validations for a payment instruction.
3538 |
3539 | The p_isReval flag is used To differentiate between these two cases.
3540 |
3541 | PARAMETERS:
3542 | IN
3543 | p_valSetsTab - list of validation sets applicable to this
3544 | pmt instruction.
3545 |
3546 | p_isReval - flag indicating whether this is a first
3547 | attempt at validating the pmt instruction, or
3548 | a revalidation.
3549 |
3550 | TRUE = Re-validation
3551 | FALSE = Fresh validation
3552 |
3553 | OUT
3554 | x_pmtInstrRec - pmt instruction record containing the pmt
3555 | instruction id.
3556 |
3557 | x_docErrorTab -
3558 | List of errors generated by validation sets.
3559 | Only the count of this list is used to
3560 | check if the payment instruction had
3561 | any validation errors. This count checking
3562 | is only performed during re-validation flow.
3563 | This param is not relevant for fresh validations.
3564 |
3565 | x_errTokenTab -
3566 | List of error tokens associated with the
3567 | error messages.
3568 |
3569 | RETURNS:
3570 |
3571 | NOTES:
3572 |
3573 *---------------------------------------------------------------------*/
3574 PROCEDURE executeValidationsForInstr(
3575 x_pmtInstrRec IN OUT NOCOPY IBY_PAY_INSTRUCTIONS_ALL%ROWTYPE,
3576 p_valSetsTab IN instructionValSetsTab,
3577 p_isReval IN BOOLEAN,
3578 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
3579 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType
3580 )
3581 IS
3582
3583 l_module_name VARCHAR2(200) := G_PKG_NAME || '.executeValidationsForInstr';
3584
3585 l_stmt VARCHAR2(200);
3586 l_result NUMBER := 0;
3587
3588 l_doc_err_rec IBY_TRANSACTION_ERRORS%ROWTYPE;
3589 l_error_code VARCHAR2(100);
3590 l_error_msg VARCHAR2(500);
3591
3592 l_count NUMBER;
3593
3594 CURSOR c_instr_errors(p_instr_id NUMBER)
3595 IS
3596 SELECT
3597 *
3598 FROM
3599 IBY_TRANSACTION_ERRORS
3600 WHERE
3601 transaction_id = p_instr_id AND
3602 transaction_type = TRXN_TYPE_INSTR AND
3603 error_status <> 'INACTIVE'
3604 ;
3605
3606 BEGIN
3607
3608
3609 /*
3610 * Fix for bug 5440434:
3611 *
3612 * Before doing any validations, set any
3613 * existing validation error messages that
3614 * exist against this instruction to 'inactive'
3615 * status in the IBY_TRANSACTION_ERRORS table.
3616 *
3617 * Unless we do this, the old errors will
3618 * continue to show up against this instruction
3619 * in the IBY UI even if the instruction is validated
3620 * successfully this time round.
3621 */
3622
3623 /*
3624 * Only inactivate old errors if we are doing
3625 * re-validation. In a fresh validation scenario,
3626 * there are no existing errors to inactivate.
3627 */
3628 IF (p_isReval = TRUE) THEN
3629
3630 IBY_BUILD_UTILS_PKG.inactivateOldErrors(
3631 x_pmtInstrRec.payment_instruction_id,
3632 TRXN_TYPE_INSTR
3633 );
3634
3635 END IF;
3636
3637 FOR j in p_valSetsTab.FIRST .. p_valSetsTab.LAST LOOP
3638
3639 /*
3640 * Dynamically call the validation set applicable
3641 * to the current payment.
3642 */
3643 l_stmt := 'CALL '
3644 || p_valSetsTab(j).val_code_package
3645 || '.'
3646 || p_valSetsTab(j).val_code_entry_pt
3647 || '(:1,:2,:3,:4,:5)';
3648
3649 print_debuginfo(l_module_name, 'Executing ' || l_stmt);
3650
3651 EXECUTE IMMEDIATE (l_stmt) USING
3652 IN p_valSetsTab(j).val_assign_id,
3653 IN p_valSetsTab(j).val_set_id,
3654 IN x_pmtInstrRec.payment_instruction_id,
3655 IN 'N',
3656 OUT l_result;
3657
3658 print_debuginfo(l_module_name, 'Finished executing '
3659 || l_stmt);
3660
3661 print_debuginfo(l_module_name, 'Result: '
3662 || l_result);
3663
3664 /*
3665 * If instruction fails validation, then
3666 * set the status of the instruction to failed
3667 * validation.
3668 */
3669 IF (l_result <> 0) THEN
3670
3671 print_debuginfo(l_module_name, 'Payment instruction '
3672 || x_pmtInstrRec.payment_instruction_id
3673 || ' failed validation by validation set '
3674 || p_valSetsTab(j).val_code_package
3675 || '.'
3676 || p_valSetsTab(j).val_code_entry_pt
3677 );
3678
3679 x_pmtInstrRec.payment_instruction_status :=
3680 INS_STATUS_CREAT_ERROR;
3681
3682 /*
3683 * Fix for bug 5206701:
3684 *
3685 * No need to insert an error message here.
3686 * The payment instruction validation set
3687 * would have already inserted an error message
3688 * when it applied the validation on the
3689 * payment instruction.
3690 */
3691
3692 END IF; -- result <> 0
3693
3694 print_debuginfo(l_module_name, '+-------------------------+');
3695
3696 END LOOP; -- for each val set applicable to this instruction
3697
3698 /*
3699 * Fix for bug 5482490:
3700 *
3701 * Fetch all active error messages stored in
3702 * IBY_TRANSACTION_ERRORS for this payment instruction.
3703 *
3704 * The count of these error messages is used to determine
3705 * whether the payment instruction is valid or not.
3706 */
3707
3708 /*
3709 * Fix for bug 5515376:
3710 *
3711 * Only pick up the errors list if we are doing
3712 * a re-validation.
3713 *
3714 * In a fresh validations scenario, the errors list is
3715 * not used and should not be populated. If populated,
3716 * the PICP will use this errors list to insert into the
3717 * transaction errors table and will run into a primary
3718 * key violation error.
3719 */
3720 IF (p_isReval = TRUE) THEN
3721
3722 OPEN c_instr_errors(x_pmtInstrRec.payment_instruction_id);
3723 FETCH c_instr_errors BULK COLLECT INTO x_docErrorTab;
3724 CLOSE c_instr_errors;
3725
3726 print_debuginfo(l_module_name, '# error messages generated by '
3727 || 'validation sets for pmt instruction '
3728 || x_pmtInstrRec.payment_instruction_id
3729 || ': '
3730 || x_docErrorTab.COUNT
3731 );
3732
3733 END IF;
3734
3735 END executeValidationsForInstr;
3736
3737 /*--------------------------------------------------------------------
3738 | NAME:
3739 | raiseBizEvents
3740 |
3741 | PURPOSE:
3742 |
3743 |
3744 |
3745 | PARAMETERS:
3746 | IN
3747 |
3748 | OUT
3749 |
3750 |
3751 | RETURNS:
3752 |
3753 | NOTES:
3754 |
3755 *---------------------------------------------------------------------*/
3756 PROCEDURE raiseBizEvents(
3757 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType
3758 )
3759 IS
3760
3761 l_module_name VARCHAR2(200) := G_PKG_NAME || '.raiseBizEvents';
3762 l_xml_clob CLOB;
3763 l_rejection_level VARCHAR2(200);
3764 l_event_name VARCHAR2(200);
3765 l_event_key NUMBER;
3766 l_param_names JTF_VARCHAR2_TABLE_300;
3767 l_param_vals JTF_VARCHAR2_TABLE_300;
3768
3769 BEGIN
3770
3771 print_debuginfo(l_module_name, 'ENTER');
3772
3773 /*
3774 * These tables are used to pass event keys
3775 * to the business event.
3776 */
3777 l_param_names := JTF_VARCHAR2_TABLE_300();
3778 l_param_vals := JTF_VARCHAR2_TABLE_300();
3779
3780 /*
3781 * The event key uniquely identifies a specific
3782 * occurance of an event. Therefore, it should be
3783 * a sequence number.
3784 */
3785 SELECT
3786 IBY_EVENT_KEY_S.NEXTVAL
3787 INTO
3788 l_event_key
3789 FROM
3790 DUAL
3791 ;
3792
3793 /*
3794 * Raise a business event with the list of failed
3795 * payment instructions. This business event should
3796 * trigger a workflow in the calling app to allow user
3797 * to review and modify these payment instructions.
3798 */
3799 l_event_name :=
3800 'oracle.apps.iby.instructionprogram.validation'
3801 || '.notify_instruction_failure';
3802
3803 /*
3804 * All payment instructions that are not in 'created' status
3805 * are failed instructions. Raise a business event for any
3806 * such failed instruction [to inform the payment administrator
3807 * to review the error in the Creation Failure Handling Flow (F9)
3808 * and to take corrective action].
3809 *
3810 * Only negative business events are raised; If all payment
3811 * instructions are in 'created' status, no business event
3812 * will be raised.
3813 */
3814
3815 FOR i IN x_pmtInstrTab.FIRST .. x_pmtInstrTab.LAST LOOP
3816
3817 IF (x_pmtInstrTab(i).payment_instruction_status <>
3818 INS_STATUS_CREATED) THEN
3819
3820 print_debuginfo(l_module_name, 'Raising biz event '
3821 || l_event_name
3822 || ' for pay instruction '
3823 || x_pmtInstrTab(i).payment_instruction_id);
3824
3825 l_xml_clob := getXMLClob(x_pmtInstrTab(i).payment_instruction_id,
3826 INS_STATUS_CREATED);
3827
3828 IF (l_xml_clob IS NULL) THEN
3829
3830 print_debuginfo(l_module_name, 'Data inconsistency: '
3831 || 'Failed payment instruction exists in PLSQL '
3832 || 'table, but not database table (though database '
3833 || 'insert has occured). Aborting program ..',
3834 FND_LOG.LEVEL_UNEXPECTED
3835 );
3836
3837 APP_EXCEPTION.RAISE_EXCEPTION;
3838
3839 ELSE
3840
3841 IBY_BUILD_UTILS_PKG.printXMLClob(l_xml_clob);
3842
3843 /* No params to pass */
3844 l_param_names.EXTEND;
3845 l_param_vals.EXTEND;
3846
3847 iby_workflow_pvt.raise_biz_event(l_event_name, l_event_key,
3848 l_param_names, l_param_vals, l_xml_clob);
3849
3850 print_debuginfo(l_module_name, 'Raised biz event '
3851 || l_event_name || ' with key '
3852 || l_event_key || '.');
3853
3854 END IF; -- if xml clob is null
3855
3856
3857 ELSE
3858
3859 print_debuginfo(l_module_name, 'Not raising biz event '
3860 || l_event_name
3861 || ' for pay instr '
3862 || x_pmtInstrTab(i).payment_instruction_id
3863 || ' because instruction is valid.'
3864 );
3865
3866 END IF; -- if instruction status <> 'created'
3867
3868 END LOOP;
3869
3870 print_debuginfo(l_module_name, 'EXIT');
3871
3872 EXCEPTION
3873 WHEN OTHERS THEN
3874 print_debuginfo(l_module_name, 'Fatal: Exception when attempting '
3875 || 'to raise business event.', FND_LOG.LEVEL_UNEXPECTED);
3876 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
3877 FND_LOG.LEVEL_UNEXPECTED);
3878 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
3879 FND_LOG.LEVEL_UNEXPECTED);
3880
3881 /*
3882 * Propogate exception to caller.
3883 */
3884 RAISE;
3885
3886 END raiseBizEvents;
3887
3888 /*--------------------------------------------------------------------
3889 | NAME:
3890 | getXMLClob
3891 |
3892 | PURPOSE:
3893 |
3894 |
3895 |
3896 | PARAMETERS:
3897 | IN
3898 |
3899 | OUT
3900 |
3901 |
3902 | RETURNS:
3903 |
3904 | NOTES:
3905 |
3906 | XML generation from PLSQL is evolving rapidly.
3907 |
3908 | The code below uses DBMS_XMLQuery() to generate XML
3909 | from a SELECT statement.
3910 |
3911 | DBMS_XMLQuery() uses Java code internally, and is slow.
3912 |
3913 | Better ways to generate XML are:
3914 | 1. DBMS_XMLGEN
3915 | DBMS_XMLGEN is a built-in package in C. It is fast. However,
3916 | it is supported only in Oracle 9i and above.
3917 |
3918 | 2. SQLX
3919 | This is the new emerging standard for SQL -> XML.
3920 | It is both fast and easy. However, only Oracle 9i and
3921 | above.
3922 |
3923 *---------------------------------------------------------------------*/
3924 FUNCTION getXMLClob(
3925 p_pay_instruction_id IN VARCHAR2,
3926 p_instruction_status IN VARCHAR2
3927 )
3928 RETURN VARCHAR2
3929 IS
3930 l_module_name VARCHAR2(200) := G_PKG_NAME || '.getXMLClob';
3931 l_xml_clob CLOB := NULL;
3932
3933 l_ctx DBMS_XMLQuery.ctxType;
3934 l_sql VARCHAR2(2000);
3935 l_sqlcode NUMBER;
3936 l_sqlerrm VARCHAR2(300);
3937
3938 BEGIN
3939
3940 print_debuginfo(l_module_name, 'ENTER');
3941
3942 /*
3943 * Note:
3944 *
3945 * Replace DBMS_XMLQuery with DBMS_XMLGEN or SQLX
3946 * when Oracle 9i is minimum requirement in tech
3947 * stack (see notes above).
3948 */
3949
3950 /*
3951 * Select the given payment instruction from the database;
3952 * this is a redundant query only used to generate an
3953 * XML output.
3954 */
3955
3956 l_sql := 'SELECT payment_instruction_id '
3957 || 'FROM IBY_PAY_INSTRUCTIONS_ALL '
3958 || 'WHERE payment_instruction_id = :p_ins_id '
3959 || 'AND payment_instruction_status <> :p_ins_status';
3960
3961 l_ctx := DBMS_XMLQuery.newContext(l_sql);
3962 DBMS_XMLQuery.setBindValue(l_ctx, 'p_ins_id', p_pay_instruction_id);
3963 DBMS_XMLQuery.setBindValue(l_ctx, 'p_ins_status', p_instruction_status);
3964 DBMS_XMLQuery.useNullAttributeIndicator(l_ctx, TRUE);
3965
3966 /* raise an exception if no rows were found */
3967 DBMS_XMLQuery.setRaiseException(l_ctx, TRUE);
3968 DBMS_XMLQuery.setRaiseNoRowsException(l_ctx, TRUE);
3969 DBMS_XMLQuery.propagateOriginalException(l_ctx, TRUE);
3970
3971 l_xml_clob := DBMS_XMLQuery.getXML(l_ctx);
3972 DBMS_XMLQuery.closeContext(l_ctx);
3973
3974 print_debuginfo(l_module_name, 'EXIT');
3975
3976 RETURN l_xml_clob;
3977
3978 EXCEPTION
3979
3980 WHEN OTHERS THEN
3981
3982 DBMS_XMLQuery.getExceptionContent(l_ctx, l_sqlcode, l_sqlerrm);
3983 print_debuginfo(l_module_name, 'SQL code: ' || l_sqlcode);
3984 print_debuginfo(l_module_name, 'SQL err msg: '|| l_sqlerrm);
3985
3986 /*
3987 * Do not raise exception if no rows found.
3988 * It means all payments were successful.
3989 * return NULL clob to caller.
3990 *
3991 * 1403 = NO_DATA_FOUND
3992 *
3993 * Note: We are unable to explicitly catch the
3994 * NO_DATA_FOUND exception here because the caller
3995 * raises some other exception. So we have to check
3996 * value of the original error code instead.
3997 */
3998 IF (l_sqlcode = 1403) THEN
3999 print_debuginfo(l_module_name, 'No rows were returned for query;'
4000 || ' Returning null xml clob.');
4001 RETURN NULL;
4002 END IF;
4003
4004 print_debuginfo(l_module_name, 'Fatal: Exception when attempting '
4005 || 'to raise business event.', FND_LOG.LEVEL_UNEXPECTED);
4006 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
4007 FND_LOG.LEVEL_UNEXPECTED);
4008 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
4009 FND_LOG.LEVEL_UNEXPECTED);
4010
4011 /*
4012 * Propogate exception to caller.
4013 */
4014 RAISE;
4015
4016 END getXMLClob;
4017
4018 /*--------------------------------------------------------------------
4019 | NAME:
4020 | performSortedPaymentNumbering
4021 |
4022 | PURPOSE:
4023 |
4024 |
4025 |
4026 | PARAMETERS:
4027 | IN
4028 |
4029 |
4030 | OUT
4031 |
4032 |
4033 | RETURNS:
4034 |
4035 | NOTES:
4036 |
4037 *---------------------------------------------------------------------*/
4038 PROCEDURE performSortedPaymentNumbering(
4039 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
4040 x_sortedPmtTab IN OUT NOCOPY sortedPmtTabType,
4041 p_profileMap IN IBY_BUILD_UTILS_PKG.profileIdToCodeMapTabType,
4042 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
4043 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType
4044 )
4045 IS
4046
4047 l_module_name VARCHAR2(200) := G_PKG_NAME || '.performSortedPaymentNumbering';
4048
4049 l_sort_pmt_tab sortedPmtTabType;
4050
4051 l_sort_options_rec sortOptionsRecType;
4052 l_sort_options_tab sortOptionsTabType;
4053
4054 l_sql_chunk VARCHAR2(3000);
4055 l_cursor_stmt VARCHAR2(5000);
4056
4057 l_order_by VARCHAR2(200);
4058 l_sort_option VARCHAR2(2000);
4059
4060 TYPE dyn_sort_payments IS REF CURSOR;
4061 l_sort_pmts_cursor dyn_sort_payments;
4062
4063 BEGIN
4064
4065 print_debuginfo(l_module_name, 'ENTER');
4066
4067 /*
4068 * Pick up the sorting criteria for all profiles and put
4069 * them into a PLSQL table.
4070 *
4071 * Each payment instruction will use the sorting criteria
4072 * from this table (record matching its profile id).
4073 *
4074 * No need to validate the user provided sorting options
4075 * as the user will be selecting these from a lookup.
4076 */
4077 retrieveSortingOptions(x_pmtInstrTab, l_sort_options_tab);
4078
4079 IF (l_sort_options_tab.COUNT = 0) THEN
4080
4081 print_debuginfo(l_module_name, 'No sorting options have been '
4082 || 'set up for the profiles on any of the created '
4083 || 'payment instructions.'
4084 );
4085
4086 END IF;
4087
4088 /*
4089 * Loop through all the payment instructions.
4090 *
4091 * For each payment instruction:
4092 * a. Get the payments of the instruction in sorted order.
4093 * b. Sequence the sorted payments with unique document
4094 * sequence numbers.
4095 * c. Number the sorted payments with unique payment
4096 * references.
4097 */
4098 FOR i in x_pmtInstrTab.FIRST .. x_pmtInstrTab.LAST LOOP
4099
4100 /*
4101 * Perform payment sorting only for payment instructions
4102 * in 'CREATED' status.
4103 */
4104 IF (x_pmtInstrTab(i).payment_instruction_status = 'CREATED') THEN
4105
4106 getSortedPmtsForInstr(
4107 x_pmtInstrTab(i).payment_instruction_id,
4108 x_pmtInstrTab(i).payment_profile_id,
4109 l_sort_options_tab,
4110 p_profileMap,
4111 l_sort_pmt_tab
4112 );
4113
4114 print_debuginfo(l_module_name, 'Number of payments picked '
4115 || 'up for instruction id '
4116 || x_pmtInstrTab(i).payment_instruction_id
4117 || ' is '
4118 || l_sort_pmt_tab.COUNT
4119 );
4120
4121 /*
4122 * This should not happen because a payment
4123 * instruction should contain at least one
4124 * payment.
4125 */
4126 IF (l_sort_pmt_tab.COUNT = 0) THEN
4127
4128 print_debuginfo(l_module_name, 'No payments were '
4129 || 'picked up for payment instruction '
4130 || x_pmtInstrTab(i).payment_instruction_id
4131 || '. Possible data corruption. Aborting '
4132 || 'program.',
4133 FND_LOG.LEVEL_UNEXPECTED
4134 );
4135
4136 APP_EXCEPTION.RAISE_EXCEPTION;
4137
4138 END IF;
4139
4140 /*
4141 * STEP I:
4142 * Assign document sequence numbers to the payments of
4143 * this payment instruction.
4144 */
4145 /*
4146 * Fix for bug 5069407:
4147 *
4148 * Document sequencing log commented out.
4149 *
4150 */
4151 --performDocSequenceNumbering(l_sort_pmt_tab, x_docErrorTab,
4152 --x_errTokenTab);
4153
4154 /*
4155 * STEP II:
4156 * Assign payment references to the payments of this
4157 * payment instruction.
4158 */
4159 providePaymentReferences(l_sort_pmt_tab);
4160
4161 /*
4162 * Provide list of sorted payments to caller by adding the
4163 * sorted payments for the current payment instruction into
4164 * master list of sorted payments.
4165 */
4166 IF (l_sort_pmt_tab.COUNT <> 0) THEN
4167
4168 FOR sort_index IN l_sort_pmt_tab.FIRST ..
4169 l_sort_pmt_tab.LAST LOOP
4170
4171 x_sortedPmtTab(x_sortedPmtTab.COUNT + 1) :=
4172 l_sort_pmt_tab(sort_index);
4173
4174 END LOOP;
4175
4176 END IF;
4177
4178 /*
4179 * If there were any failures during document
4180 * sequencing or providing references to payments,
4181 * the corresponding payment instruction would
4182 * have been marked as failed.
4183 *
4184 * Copy back the failed payment instructions
4185 * into x_pmtInstrTab so that the status of these
4186 * failed payment instructions can be updated in
4187 * the database.
4188 */
4189 markFailedInstructions(x_pmtInstrTab, l_sort_pmt_tab);
4190
4191 ELSE
4192
4193 print_debuginfo(l_module_name, 'Payment instruction '
4194 || x_pmtInstrTab(i).payment_instruction_id
4195 || ' is in '
4196 || x_pmtInstrTab(i).payment_instruction_status
4197 || '. Payment reference numbering will not be '
4198 || 'performed for this instruction because it '
4199 || 'is not in *created* status.'
4200 );
4201
4202 END IF; -- if instr status = 'CREATED'
4203
4204 END LOOP; -- for each payment instruction
4205
4206 print_debuginfo(l_module_name, 'EXIT');
4207
4208 EXCEPTION
4209 WHEN OTHERS THEN
4210
4211 print_debuginfo(l_module_name, 'Exception occured when '
4212 || 'performing sorted payment numbering for '
4213 || 'payment instructions.',
4214 FND_LOG.LEVEL_UNEXPECTED
4215 );
4216
4217 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
4218 FND_LOG.LEVEL_UNEXPECTED);
4219 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
4220 FND_LOG.LEVEL_UNEXPECTED);
4221
4222 print_debuginfo(l_module_name, 'EXIT');
4223
4224 /*
4225 * Propogate exception to caller.
4226 */
4227 RAISE;
4228
4229 END performSortedPaymentNumbering;
4230
4231 /*--------------------------------------------------------------------
4232 | NAME:
4233 | buildOrderByFromSortOptions
4234 |
4235 | PURPOSE:
4236 |
4237 |
4238 |
4239 | PARAMETERS:
4240 | IN
4241 |
4242 |
4243 | OUT
4244 |
4245 |
4246 | RETURNS:
4247 |
4248 | NOTES:
4249 |
4250 *---------------------------------------------------------------------*/
4251 FUNCTION buildOrderByFromSortOptions(
4252 x_sortOptionsRec IN OUT NOCOPY sortOptionsRecType
4253 )
4254 RETURN VARCHAR2
4255 IS
4256
4257 l_sql_chunk VARCHAR2(3000) := NULL;
4258
4259 l_order_by VARCHAR2(200);
4260 l_sort_option VARCHAR2(2000);
4261
4262 l_module_name VARCHAR2(200) := G_PKG_NAME || '.buildOrderByFromSortOptions';
4263
4264 BEGIN
4265
4266 /*
4267 * Build up the ORDER BY clause (of the SQL statement to
4268 * pick up the available payments) in chunks; add only
4269 * non-null sorting parameters into the ORDER BY clause.
4270 *
4271 * Sort orders are specified as 'ASCENDING' and 'DESCENDING';
4272 * Convert them to 'ASC' and 'DESC' respectively.
4273 */
4274 IF (x_sortOptionsRec.pmt_profile_cd IS NOT NULL) THEN
4275
4276 /* initialize the sql string */
4277 l_sql_chunk := '';
4278
4279 /* Trim any leading or lagging spaces */
4280 x_sortOptionsRec.sort_option_1 :=
4281 ltrim(rtrim(x_sortOptionsRec.sort_option_1));
4282 x_sortOptionsRec.sort_option_2 :=
4283 ltrim(rtrim(x_sortOptionsRec.sort_option_2));
4284 x_sortOptionsRec.sort_option_3 :=
4285 ltrim(rtrim(x_sortOptionsRec.sort_option_3));
4286
4287 x_sortOptionsRec.sort_order_1 :=
4288 ltrim(rtrim(x_sortOptionsRec.sort_order_1));
4289 x_sortOptionsRec.sort_order_2 :=
4290 ltrim(rtrim(x_sortOptionsRec.sort_order_2));
4291 x_sortOptionsRec.sort_order_3 :=
4292 ltrim(rtrim(x_sortOptionsRec.sort_order_3));
4293
4294 /*
4295 * Rename the sort options (if necessary), so that they
4296 * match the column names of the appropriate tables.
4297 */
4298 renameSortOption(x_sortOptionsRec.sort_option_1);
4299 renameSortOption(x_sortOptionsRec.sort_option_2);
4300 renameSortOption(x_sortOptionsRec.sort_option_3);
4301
4302 /*
4303 * Form SQL statement 'ORDER BY' clause by appending
4304 * user specified sort options and sort orders.
4305 */
4306
4307 /* sort option 1 and sort order 1 */
4308 IF (x_sortOptionsRec.sort_option_1 IS NOT NULL OR
4309 x_sortOptionsRec.sort_option_1 <> '') THEN
4310
4311 l_order_by := '';
4312 l_sort_option := '';
4313
4314 /* use sort order only if sort option is provided */
4315 IF (x_sortOptionsRec.sort_order_1 IS NOT NULL OR
4316 x_sortOptionsRec.sort_order_1 <> '') THEN
4317
4318 l_order_by := REPLACE(x_sortOptionsRec.
4319 sort_order_1, 'ENDING');
4320
4321 END IF;
4322
4323 /*
4324 * Sorting by payment amount is a special case;
4325 * It should always be preceded by sorting by
4326 * payment currency.
4327 */
4328 IF (x_sortOptionsRec.sort_option_1 = 'pmt.payment_amount')
4329 THEN
4330
4331 l_sort_option :=
4332 'pmt.payment_currency_code '
4333 || l_order_by
4334 || ', '
4335 || x_sortOptionsRec.sort_option_1;
4336 ELSE
4337
4338 l_sort_option := x_sortOptionsRec.sort_option_1;
4339
4340 END IF;
4341
4342 l_sql_chunk := l_sql_chunk || l_sort_option;
4343 l_sql_chunk := l_sql_chunk || ' ' || l_order_by;
4344
4345 END IF; -- if sort option 1 not null
4346
4347 /* sort option 2 and sort order 2 */
4348 IF (x_sortOptionsRec.sort_option_2 IS NOT NULL OR
4349 x_sortOptionsRec.sort_option_2 <> '') THEN
4350
4351 l_order_by := '';
4352 l_sort_option := '';
4353
4354 /* use sort order only if sort option is provided */
4355 IF (x_sortOptionsRec.sort_order_2 IS NOT NULL OR
4356 x_sortOptionsRec.sort_order_2 <> '') THEN
4357
4358 l_order_by := REPLACE(x_sortOptionsRec.
4359 sort_order_2, 'ENDING');
4360
4361 END IF;
4362
4363 l_sort_option := x_sortOptionsRec.sort_option_2
4364 || ' ' || l_order_by;
4365
4366 /*
4367 * Sorting by payment amount is a special case;
4368 * It should always be preceded by sorting by
4369 * payment currency.
4370 */
4371 IF (x_sortOptionsRec.sort_option_2 = 'pmt.payment_amount')
4372 THEN
4373
4374 l_sort_option :=
4375 'pmt.payment_currency_code '
4376 || l_order_by
4377 || ', '
4378 || l_sort_option;
4379
4380 END IF;
4381
4382 IF (l_sql_chunk IS NULL) THEN
4383
4384 l_sql_chunk := l_sort_option;
4385
4386 ELSE
4387
4388 l_sql_chunk := l_sql_chunk || ', ' || l_sort_option;
4389
4390 END IF;
4391
4392 END IF; -- if sort option 2 not null
4393
4394 /* sort option 3 and sort order 3 */
4395 IF (x_sortOptionsRec.sort_option_3 IS NOT NULL OR
4396 x_sortOptionsRec.sort_option_3 <> '') THEN
4397
4398 l_order_by := '';
4399 l_sort_option := '';
4400
4401 /* use sort order only if sort option is provided */
4402 IF (x_sortOptionsRec.sort_order_3 IS NOT NULL OR
4403 x_sortOptionsRec.sort_order_3 <> '') THEN
4404
4405 l_order_by := REPLACE(x_sortOptionsRec.
4406 sort_order_3, 'ENDING');
4407
4408 END IF;
4409
4410 l_sort_option := x_sortOptionsRec.sort_option_3
4411 || ' ' || l_order_by;
4412
4413 /*
4414 * Sorting by payment amount is a special case;
4415 * It should always be preceded by sorting by
4416 * payment currency.
4417 */
4418 IF (x_sortOptionsRec.sort_option_3 = 'pmt.payment_amount')
4419 THEN
4420
4421 l_sort_option:=
4422 'pmt.payment_currency_code '
4423 || l_order_by
4424 || ', '
4425 || l_sort_option;
4426
4427 END IF;
4428
4429 IF (l_sql_chunk IS NULL) THEN
4430
4431 l_sql_chunk := l_sort_option;
4432
4433 ELSE
4434
4435 l_sql_chunk := l_sql_chunk || ', ' || l_sort_option;
4436
4437 END IF;
4438
4439 END IF; -- if sort option 3 not null
4440
4441 IF (l_sql_chunk IS NOT NULL) THEN
4442 l_sql_chunk := 'ORDER BY ' || l_sql_chunk;
4443 END IF;
4444
4445 END IF; -- x_sortOptionsRec.pmt_profile_cd IS NOT NULL
4446
4447 print_debuginfo(l_module_name, 'Formed order by clause : '
4448 || l_sql_chunk);
4449
4450 RETURN l_sql_chunk;
4451
4452 END buildOrderByFromSortOptions;
4453
4454 /*--------------------------------------------------------------------
4455 | NAME:
4456 | markFailedInstructions
4457 |
4458 | PURPOSE:
4459 |
4460 |
4461 |
4462 | PARAMETERS:
4463 | IN
4464 |
4465 |
4466 | OUT
4467 |
4468 |
4469 | RETURNS:
4470 |
4471 | NOTES:
4472 |
4473 *---------------------------------------------------------------------*/
4474 PROCEDURE markFailedInstructions(
4475 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
4476 p_sortedPmtTab IN sortedPmtTabType
4477 )
4478 IS
4479 BEGIN
4480
4481 IF (p_sortedPmtTab.COUNT = 0 OR x_pmtInstrTab.COUNT = 0) THEN
4482 RETURN;
4483 END IF;
4484
4485 FOR i IN p_sortedPmtTab.FIRST .. p_sortedPmtTab.LAST LOOP
4486
4487 IF (p_sortedPmtTab(i).instr_status <> INS_STATUS_CREATED)
4488 THEN
4489
4490 FOR j in x_pmtInstrTab.FIRST .. x_pmtInstrTab.LAST LOOP
4491
4492 IF (x_pmtInstrTab(j).payment_instruction_id =
4493 p_sortedPmtTab(i).instr_id) THEN
4494
4495 x_pmtInstrTab(j).payment_instruction_status :=
4496 p_sortedPmtTab(i).instr_status;
4497
4498 END IF;
4499
4500 END LOOP;
4501
4502 END IF;
4503
4504 END LOOP;
4505
4506 END markFailedInstructions;
4507
4508 /*--------------------------------------------------------------------
4509 | NAME:
4510 | performDocSequenceNumbering
4511 |
4512 | PURPOSE:
4513 |
4514 |
4515 |
4516 | PARAMETERS:
4517 | IN
4518 |
4519 |
4520 | OUT
4521 |
4522 |
4523 | RETURNS:
4524 |
4525 | NOTES:
4526 |
4527 *---------------------------------------------------------------------*/
4528 PROCEDURE performDocSequenceNumbering(
4529 x_sortedPmtTab IN OUT NOCOPY sortedPmtTabType,
4530 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
4531 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType
4532 )
4533 IS
4534
4535 l_module_name VARCHAR2(200) := G_PKG_NAME || '.performDocSequenceNumbering';
4536 l_seq_num NUMBER(38);
4537
4538 BEGIN
4539
4540 print_debuginfo(l_module_name, 'ENTER');
4541
4542 /*
4543 * STEP I:
4544 * Get the primary ledger linked to the LE on the
4545 * payment. The primary ledger id is the same as the
4546 * set of books id, and it is needed for the document
4547 * sequencing API.
4548 */
4549 getLedgerIdFromLEId(x_sortedPmtTab);
4550
4551 /*
4552 * STEP II:
4553 * Invoke the document sequencing API to assign
4554 * unique sequence numbers to the individual
4555 * payments of the instruction.
4556 */
4557 assignSequenceNumbers(x_sortedPmtTab, x_docErrorTab, x_errTokenTab);
4558
4559 print_debuginfo(l_module_name, 'EXIT');
4560
4561 END performDocSequenceNumbering;
4562
4563
4564 /*--------------------------------------------------------------------
4565 | NAME:
4566 | getLedgerIdFromLEId
4567 |
4568 | PURPOSE:
4569 | Returns the id of the primary ledger linked with the given LE.
4570 |
4571 |
4572 | PARAMETERS:
4573 | IN
4574 | p_le_id - id of the a particular legal entity.
4575 |
4576 | OUT
4577 |
4578 |
4579 | RETURNS:
4580 | ledger id - A positive value, if successful
4581 | -1, if unsuccessful
4582 | NOTES:
4583 |
4584 *---------------------------------------------------------------------*/
4585 PROCEDURE getLedgerIdFromLEId(
4586 x_sortedPmtTab IN OUT NOCOPY sortedPmtTabType
4587 )
4588
4589 IS
4590
4591 l_ret_val BOOLEAN := FALSE;
4592 l_ledger_list GL_MC_INFO.ledger_tbl_type := GL_MC_INFO.ledger_tbl_type();
4593 l_ledger_id NUMBER(15);
4594 l_le_id IBY_PAY_INSTRUCTIONS_ALL.legal_entity_id%TYPE;
4595
4596 l_module_name VARCHAR2(200) := G_PKG_NAME || '.getLedgerIdFromLEId';
4597
4598 BEGIN
4599
4600 FOR i IN x_sortedPmtTab.FIRST .. x_sortedPmtTab.LAST LOOP
4601
4602 IF (x_sortedPmtTab(i).doc_cat_code IS NOT NULL) THEN
4603
4604 print_debuginfo(l_module_name, 'Payment id '
4605 || x_sortedPmtTab(i).payment_id
4606 || ' has document category code '
4607 || x_sortedPmtTab(i).doc_cat_code
4608 || '. Attempting to get set-of-books id for this pmt.'
4609 );
4610
4611 BEGIN
4612
4613 /*
4614 * Initialize ledger list before each iteration.
4615 */
4616 l_ledger_list := GL_MC_INFO.ledger_tbl_type();
4617
4618 l_le_id := x_sortedPmtTab(i).le_id;
4619
4620 /*
4621 * A given LE can be linked to many types of ledgers:
4622 *
4623 * a. Primary Ledger [Only one possible for a given LE]
4624 * b. Secondary Ledger [Many possible for a given LE]
4625 * c. ALC Ledger [Many possible for a given LE]
4626 *
4627 * Therefore, it is possible to uniquely derive the id
4628 * of the primary ledger from the given LE id.
4629 */
4630
4631 /*
4632 * Get the primary ledger id for the given LE.
4633 * The ledger id was formerly known as the
4634 * set of books id.
4635 */
4636 l_ret_val := GL_MC_INFO.get_le_ledgers
4637 ( p_legal_entity_id => l_le_id
4638 , p_get_primary_flag => 'Y'
4639 , p_get_secondary_flag => 'N'
4640 , p_get_alc_flag => 'N'
4641 , x_ledger_list => l_ledger_list
4642 );
4643
4644 /*
4645 * Check if return status is a success.
4646 * Otherwise raise error.
4647 */
4648 IF (l_ret_val <> TRUE) THEN
4649
4650 print_debuginfo(l_module_name, 'Unable to get primary '
4651 || 'ledger for given legal entity: '
4652 || l_le_id
4653 );
4654
4655 l_ledger_id := -1;
4656
4657 ELSIF (l_ledger_list.COUNT = 0) THEN
4658
4659 print_debuginfo(l_module_name, 'No primary ledger '
4660 || 'was linked to given legal entity: '
4661 || l_le_id
4662 );
4663
4664 l_ledger_id := -1;
4665
4666 ELSE
4667
4668 print_debuginfo(l_module_name, 'Ledger count: '
4669 || l_ledger_list.COUNT);
4670
4671 /*
4672 * Only one ledger is expected to be returned.
4673 */
4674 l_ledger_id := l_ledger_list(1).ledger_id;
4675
4676 print_debuginfo(l_module_name, 'Got primary ledger id '
4677 || l_ledger_id
4678 || ' for le id '
4679 || l_le_id
4680 );
4681
4682 END IF;
4683
4684 /*
4685 * If we could not get the ledger id (i.e., set of
4686 * books id), we cannot do document sequencing.
4687 * Raise an exception.
4688 */
4689 IF (l_ledger_id = -1) THEN
4690
4691 print_debuginfo(l_module_name, 'Primary ledger '
4692 || 'linked to legal entity id '
4693 || l_le_id
4694 || ' was not found. Raising exception ..',
4695 FND_LOG.LEVEL_UNEXPECTED
4696 );
4697
4698 APP_EXCEPTION.RAISE_EXCEPTION;
4699
4700 ELSE
4701
4702 /* store the retrieved ledger id along with the payment */
4703 x_sortedPmtTab(i).ledger_id := l_ledger_id;
4704
4705 END IF;
4706
4707 EXCEPTION
4708 WHEN OTHERS THEN
4709
4710 print_debuginfo(l_module_name, 'Exception occured when '
4711 || 'attempting to get primary ledger for payment id '
4712 || x_sortedPmtTab(i).payment_id,
4713 FND_LOG.LEVEL_UNEXPECTED
4714 );
4715
4716 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
4717 FND_LOG.LEVEL_UNEXPECTED);
4718 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
4719 FND_LOG.LEVEL_UNEXPECTED);
4720
4721 /*
4722 * If we could not get the ledger id, then payment
4723 * document sequencing will fail. There is no point
4724 * in proceeding further.
4725 */
4726
4727 /*
4728 * Propogate exception to caller.
4729 */
4730 RAISE;
4731
4732 END;
4733
4734 ELSE
4735
4736 print_debuginfo(l_module_name, 'Payment id '
4737 || x_sortedPmtTab(i).payment_id
4738 || ' has no document category code. Skipping '
4739 || 'call to get set-of-books id.'
4740 );
4741
4742 END IF;
4743
4744 END LOOP;
4745
4746 END getLedgerIdFromLEId;
4747
4748 /*--------------------------------------------------------------------
4749 | NAME:
4750 | assignSequenceNumbers
4751 |
4752 | PURPOSE:
4753 |
4754 |
4755 | PARAMETERS:
4756 | IN
4757 |
4758 | OUT
4759 |
4760 |
4761 | RETURNS:
4762 |
4763 |
4764 | NOTES:
4765 |
4766 *---------------------------------------------------------------------*/
4767 PROCEDURE assignSequenceNumbers(
4768 x_sortedPmtTab IN OUT NOCOPY sortedPmtTabType,
4769 x_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
4770 x_errTokenTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.trxnErrTokenTabType
4771 )
4772
4773 IS
4774
4775 l_ret_val BOOLEAN := FALSE;
4776 l_seq_return NUMBER;
4777 l_sequence_number NUMBER(38) := NULL;
4778 l_sequence_id fnd_document_sequences.doc_sequence_id%TYPE;
4779 v_profVal VARCHAR2(40);
4780 l_module_name VARCHAR2(200) := G_PKG_NAME || '.assignSequenceNumbers';
4781 l_doc_err_rec IBY_TRANSACTION_ERRORS%ROWTYPE;
4782 l_error_code VARCHAR2(100);
4783 l_error_msg VARCHAR2(500);
4784
4785 BEGIN
4786
4787 /*
4788 * Print following profile options for debug purposes:
4789 * a. 'UNIQUE:SEQ_NUMBERS'
4790 * This should be set to 'A' (always used) or 'P'
4791 * (partially used) for sequence numbering to work.
4792 * E.g., FND_PROFILE.PUT( 'UNIQUE:SEQ_NUMBERS', 'P');
4793 *
4794 * b. 'USER_ID'
4795 * This should be set to any not null value.
4796 * E.g., FND_PROFILE.PUT( 'USER_ID', '1234');
4797 *
4798 * These profile options are used by the FND document sequencing
4799 * API so they must be set.
4800 */
4801 FND_PROFILE.GET( 'UNIQUE:SEQ_NUMBERS', v_profVal );
4802 print_debuginfo(l_module_name, 'Profile val for UNIQUE:SEQ_NUMBERS: '
4803 || v_profVal);
4804
4805 IF (v_profVal IS NULL OR (v_profVal <> 'A' AND v_profVal <> 'P')) THEN
4806
4807 print_debuginfo(l_module_name, 'Sequential numbering profile '
4808 || 'option is not set to A (always used) or P '
4809 || '(partially used). Sequence number generation will fail ..'
4810 );
4811
4812 END IF;
4813
4814 FND_PROFILE.GET( 'USER_ID', v_profVal);
4815 print_debuginfo(l_module_name, 'Profile val for USER_ID: '
4816 || v_profVal);
4817
4818
4819 FOR i IN x_sortedPmtTab.FIRST .. x_sortedPmtTab.LAST LOOP
4820
4821 IF (x_sortedPmtTab(i).doc_cat_code IS NOT NULL) THEN
4822
4823 print_debuginfo(l_module_name, 'Attempting to get doc sequence '
4824 || 'number for payment id '
4825 || x_sortedPmtTab(i).payment_id
4826 || ' with doc cat code '
4827 || x_sortedPmtTab(i).doc_cat_code
4828 );
4829
4830 BEGIN
4831
4832 /*
4833 * Next get sequence number by invoking the AOL FND document
4834 * sequencing API.
4835 */
4836 l_seq_return := FND_SEQNUM.get_seq_val
4837 ( app_id => x_sortedPmtTab(i).ca_id
4838 , cat_code => x_sortedPmtTab(i).doc_cat_code
4839 , sob_id => x_sortedPmtTab(i).ledger_id
4840 , met_code => 'A'
4841 , trx_date => x_sortedPmtTab(i).payment_date
4842 , seq_val => l_sequence_number
4843 , docseq_id => l_sequence_id
4844 );
4845
4846 /*
4847 * Check if return status is a success.
4848 * Otherwise raise error.
4849 */
4850 IF (l_seq_return <> FND_SEQNUM.SEQSUCC OR
4851 l_sequence_number IS NULL) THEN
4852
4853 APP_EXCEPTION.RAISE_EXCEPTION;
4854
4855 ELSE
4856
4857 print_debuginfo(l_module_name, 'Sequence number generation '
4858 || 'successful for payment id '
4859 || x_sortedPmtTab(i).payment_id
4860 || ', seq num: '
4861 || l_sequence_number
4862 || ', seq id: '
4863 || l_sequence_id
4864 );
4865
4866 /* Assign the retrieved sequence number to the payment */
4867 x_sortedPmtTab(i).sequence_number := l_sequence_number;
4868 x_sortedPmtTab(i).sequence_id := l_sequence_id;
4869
4870 END IF;
4871
4872 EXCEPTION
4873 WHEN OTHERS THEN
4874
4875 /*
4876 * If an exception occurs, try to log as much
4877 * details of the FND API call as possible.
4878 */
4879 print_debuginfo(l_module_name, 'Exception occured when '
4880 || 'invoking FND doc sequencing API. Parameters passed to '
4881 || 'FND API - '
4882 || 'application id: '
4883 || x_sortedPmtTab(i).ca_id
4884 || ', document category code: '
4885 || x_sortedPmtTab(i).doc_cat_code
4886 || ', ledger id: '
4887 || x_sortedPmtTab(i).ledger_id
4888 || ', method code: '
4889 || 'A'
4890 || ', payment date: '
4891 || x_sortedPmtTab(i).payment_date,
4892 FND_LOG.LEVEL_UNEXPECTED
4893 );
4894
4895 /*
4896 * Propogate the exception.
4897 */
4898 RAISE;
4899
4900 END;
4901
4902 ELSE
4903
4904 print_debuginfo(l_module_name, 'Doc sequence '
4905 || 'number generation skipped for payment id '
4906 || x_sortedPmtTab(i).payment_id
4907 || ' because it contains no doc cat code.'
4908 );
4909
4910 END IF;
4911
4912 END LOOP;
4913
4914 EXCEPTION
4915 WHEN OTHERS THEN
4916
4917 print_debuginfo(l_module_name, 'Exception occured when attempting '
4918 || 'to perform document sequencing for payment instructions.',
4919 FND_LOG.LEVEL_UNEXPECTED
4920 );
4921
4922 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
4923 FND_LOG.LEVEL_UNEXPECTED);
4924 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
4925 FND_LOG.LEVEL_UNEXPECTED);
4926
4927 /*
4928 * Propogate exception to caller.
4929 */
4930 RAISE;
4931
4932 END assignSequenceNumbers;
4933
4934 /*--------------------------------------------------------------------
4935 | NAME:
4936 | getSortOptionsForProfile
4937 |
4938 | PURPOSE:
4939 |
4940 |
4941 |
4942 | PARAMETERS:
4943 | IN
4944 |
4945 |
4946 | OUT
4947 |
4948 |
4949 | RETURNS:
4950 |
4951 | NOTES:
4952 |
4953 *---------------------------------------------------------------------*/
4954 FUNCTION getSortOptionsForProfile(
4955 p_sortOptionsTab IN sortOptionsTabType,
4956 p_profileMap IN IBY_BUILD_UTILS_PKG.profileIdToCodeMapTabType,
4957 p_profile_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_profile_id%TYPE
4958 )
4959 RETURN sortOptionsRecType
4960
4961 IS
4962 l_sort_options_rec sortOptionsRecType;
4963 l_profile_code IBY_PAYMENT_PROFILES.system_profile_code%TYPE;
4964
4965 l_module_name VARCHAR2(200) := G_PKG_NAME || '.getSortOptionsForProfile';
4966
4967 BEGIN
4968
4969 print_debuginfo(l_module_name, 'ENTER');
4970 print_debuginfo(l_module_name, 'Profile id: ' || p_profile_id);
4971
4972 IF (p_sortOptionsTab.COUNT = 0) THEN
4973
4974 print_debuginfo(l_module_name, 'No sort options have been provided '
4975 || '. Returning NULL ..');
4976
4977 print_debuginfo(l_module_name, 'EXIT');
4978
4979 RETURN NULL;
4980
4981 END IF;
4982
4983 /*
4984 * First match the given profile id to its corresponding
4985 * system profile code.
4986 */
4987 l_profile_code := IBY_BUILD_UTILS_PKG.getProfileCodeFromId(
4988 p_profile_id,
4989 p_profileMap
4990 );
4991
4992 FOR j IN p_sortOptionsTab.FIRST .. p_sortOptionsTab.LAST LOOP
4993
4994 /*
4995 * Get the sort options linked to this
4996 * system profile code.
4997 */
4998 IF (p_sortOptionsTab(j).pmt_profile_cd = l_profile_code) THEN
4999
5000 l_sort_options_rec := p_sortOptionsTab(j);
5001 EXIT;
5002
5003 END IF;
5004
5005 END LOOP;
5006
5007 print_debuginfo(l_module_name, 'EXIT');
5008
5009 RETURN l_sort_options_rec;
5010
5011 END getSortOptionsForProfile;
5012
5013 /*--------------------------------------------------------------------
5014 | NAME:
5015 | renameSortOption
5016 |
5017 | PURPOSE:
5018 |
5019 |
5020 |
5021 | PARAMETERS:
5022 | IN
5023 |
5024 |
5025 | OUT
5026 |
5027 |
5028 | RETURNS:
5029 |
5030 | NOTES:
5031 |
5032 *---------------------------------------------------------------------*/
5033 PROCEDURE renameSortOption(
5034 x_sortOption IN OUT NOCOPY VARCHAR2
5035 )
5036 IS
5037 l_module_name VARCHAR2(200) := G_PKG_NAME || '.renameSortOption';
5038 BEGIN
5039
5040 IF (x_sortOption IS NULL OR x_sortOption = '') THEN
5041
5042 print_debuginfo(l_module_name, 'Sort option is null. '
5043 || 'Exiting ..'
5044 );
5045 RETURN;
5046
5047 END IF;
5048
5049 /*
5050 * Map the user specified sort option to the form
5051 * <table alias>.<column name>.
5052 *
5053 * Table Aliases Table / View
5054 * ------------- --------------------
5055 * payee -> HZ_PARTIES
5056 * payee_location -> HZ_LOCATIONS
5057 * payee_bank -> IBY_EXT_BANK_ACCOUNTS_V
5058 * pmts -> IBY_PAYMENTS_ALL
5059 *
5060 * These aliased table names are used in
5061 * query to pick up the sorted payments
5062 * in getSortedPmtsForInstr().
5063 */
5064 CASE x_sortOption
5065
5066 WHEN 'PAYEE_NAME' THEN
5067 x_sortOption := 'UPPER(payee.party_name)';
5068
5069 WHEN 'PAYEE_NUMBER' THEN
5070 x_sortOption := 'payee.party_number';
5071
5072 WHEN 'POSTAL_CODE' THEN
5073 x_sortOption := 'payee_location.postal_code';
5074
5075 WHEN 'PAYEE_TAXPAYER_ID' THEN
5076 x_sortOption := 'payee.tax_reference';
5077
5078 WHEN 'PAYEE_BANK_BRANCH' THEN
5079 x_sortOption := 'payee_bank.branch_id';
5080
5081 WHEN 'PAYMENT_AMOUNT' THEN
5082 x_sortOption := 'pmt.payment_amount';
5083
5084 WHEN 'PAYMENT_FUNCTION' THEN
5085 x_sortOption := 'pmt.payment_function';
5086
5087 WHEN 'PAYMENT_DATE' THEN
5088 x_sortOption := 'pmt.payment_date';
5089
5090 WHEN 'PAYER_ORG_ID' THEN
5091 x_sortOption := 'pmt.org_id';
5092
5093 ELSE
5094 /* unknown sort option */
5095 print_debuginfo(l_module_name, 'Unknown sort option '
5096 || ''''
5097 || x_sortOption
5098 || ''''
5099 || ' provided. This could cause SQL exception '
5100 || 'when retrieving payments.'
5101 );
5102
5103 END CASE;
5104
5105 print_debuginfo(l_module_name, 'Sort option mapped to '
5106 || ''''
5107 || x_sortOption
5108 || ''''
5109 || '.'
5110 );
5111
5112 END renameSortOption;
5113
5114 /*--------------------------------------------------------------------
5115 | NAME:
5116 | providePaymentReferences
5117 |
5118 | PURPOSE:
5119 |
5120 |
5121 |
5122 | PARAMETERS:
5123 | IN
5124 |
5125 |
5126 | OUT
5127 |
5128 |
5129 | RETURNS:
5130 |
5131 | NOTES:
5132 | This procedure is implemented as an autonomus transaction
5133 | in order to serialize access to the payment references table.
5134 |
5135 *---------------------------------------------------------------------*/
5136 PROCEDURE providePaymentReferences(
5137 x_sortPmtsTab IN OUT NOCOPY sortedPmtTabType
5138 )
5139 IS
5140 PRAGMA AUTONOMOUS_TRANSACTION;
5141
5142 l_sorted_pmts_count NUMBER := 0;
5143 l_last_used_ref_num NUMBER := 0;
5144 l_anticipated_last_ref_num NUMBER := 0;
5145
5146 l_module_name VARCHAR2(200) := G_PKG_NAME
5147 || '.providePaymentReferences';
5148 BEGIN
5149
5150 print_debuginfo(l_module_name, 'ENTER');
5151
5152 l_sorted_pmts_count := x_sortPmtsTab.COUNT;
5153
5154 IF (l_sorted_pmts_count = 0) THEN
5155
5156 print_debuginfo(l_module_name, 'Number of sorted payments '
5157 || 'provided is zero. Exiting ..'
5158 );
5159
5160 RETURN;
5161
5162 END IF;
5163
5164 BEGIN
5165
5166 /*
5167 * Select the payment reference information from
5168 * the IBY_PAYMENT_REFERENCES table.
5169 */
5170 SELECT
5171 NVL(last_used_ref_number, -1)
5172 INTO
5173 l_last_used_ref_num
5174 FROM
5175 IBY_PAYMENT_REFERENCES
5176 FOR UPDATE
5177 ;
5178
5179 IF (l_last_used_ref_num = -1) THEN
5180
5181 print_debuginfo(l_module_name, 'Payment reference information '
5182 || 'not setup. Last used ref number: '
5183 || l_last_used_ref_num
5184 || '. Cannot continue. Aborting ..',
5185 FND_LOG.LEVEL_UNEXPECTED
5186 );
5187
5188 APP_EXCEPTION.RAISE_EXCEPTION;
5189
5190 END IF;
5191
5192 /*
5193 * Check if we have sufficient payment reference numbers
5194 * to perform the numbering.
5195 */
5196 l_anticipated_last_ref_num := l_last_used_ref_num +
5197 l_sorted_pmts_count;
5198
5199 /*
5200 * If we have sufficient number of payment references, update
5201 * the last used ref number and commit. So that other
5202 * concurrent instances, now get the updated last
5203 * used ref number.
5204 */
5205 UPDATE
5206 IBY_PAYMENT_REFERENCES
5207 SET
5208 last_used_ref_number = l_anticipated_last_ref_num
5209 ;
5210
5211 /*
5212 * Use the retrieved payment references to number
5213 * the sorted payments one-by-one.
5214 */
5215 FOR i IN x_sortPmtsTab.FIRST .. x_sortPmtsTab.LAST LOOP
5216
5217 l_last_used_ref_num := l_last_used_ref_num + 1;
5218 x_sortPmtsTab(i).payment_ref := l_last_used_ref_num;
5219
5220 END LOOP;
5221
5222 print_debuginfo(l_module_name, 'Finished assigning payment '
5223 || 'references to '
5224 || x_sortPmtsTab.COUNT
5225 || ' payment(s).'
5226 );
5227
5228 print_debuginfo(l_module_name, 'EXIT');
5229
5230 /*
5231 * End the autonomous transaction.
5232 */
5233 COMMIT;
5234
5235 EXCEPTION
5236 WHEN OTHERS THEN
5237
5238 print_debuginfo(l_module_name, 'Exception occured when '
5239 || 'providing payment references'
5240 );
5241 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
5242 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
5243
5244 /*
5245 * We either provide payment references to all the sorted
5246 * payments or to none at all.
5247 *
5248 * If we failed to provide payment references to any payment,
5249 * mark the corresponding payment instructions as failed.
5250 */
5251 FOR i IN x_sortPmtsTab.FIRST .. x_sortPmtsTab.LAST LOOP
5252
5253 print_debuginfo(l_module_name, 'Setting parent payment '
5254 || 'instruction '
5255 || x_sortPmtsTab(i).instr_id
5256 || 'of payment '
5257 || x_sortPmtsTab(i).payment_id
5258 || ' to failed status because payment references '
5259 || 'could not be provided.'
5260 );
5261
5262 /* just to be sure, initialize the pmt ref to an invalid value */
5263 x_sortPmtsTab(i).payment_ref := -1;
5264 x_sortPmtsTab(i).instr_status := INS_STATUS_CREAT_ERROR;
5265
5266 END LOOP;
5267
5268 /*
5269 * End autonomous transaction.
5270 */
5271 ROLLBACK;
5272
5273 END;
5274
5275 END providePaymentReferences;
5276
5277 /*--------------------------------------------------------------------
5278 | NAME:
5279 | populateDocumentCount
5280 |
5281 | PURPOSE:
5282 |
5283 |
5284 |
5285 | PARAMETERS:
5286 | IN
5287 |
5288 | OUT
5289 |
5290 |
5291 | RETURNS:
5292 |
5293 | NOTES:
5294 |
5295 *---------------------------------------------------------------------*/
5296 PROCEDURE populateDocumentCount(
5297 x_pmtsInPmtInstrTab IN OUT NOCOPY pmtsInPmtInstrTabType
5298 )
5299 IS
5300
5301 l_module_name VARCHAR2(200) := G_PKG_NAME || '.populateDocumentCount';
5302 l_docs_in_pmt_count docsInPmtCountTabType;
5303
5304 CURSOR c_document_count
5305 IS
5306 SELECT
5307 count(*),
5308 payment_id
5309 FROM
5310 IBY_DOCS_PAYABLE_ALL
5311 WHERE
5312 document_status = DOC_STATUS_PAY_CREATED
5313 GROUP BY
5314 payment_id
5315 ;
5316
5317 BEGIN
5318
5319 OPEN c_document_count;
5320 FETCH c_document_count BULK COLLECT INTO l_docs_in_pmt_count;
5321 CLOSE c_document_count;
5322
5323 IF (l_docs_in_pmt_count.COUNT = 0) THEN
5324
5325 /*
5326 * Normally this shouldn't happen.
5327 */
5328 print_debuginfo(l_module_name, 'No documents payable '
5329 || 'were found in ''payments created'' status '
5330 || 'though created payments exist. '
5331 || 'Possible data corruption. Aborting ..',
5332 FND_LOG.LEVEL_UNEXPECTED
5333 );
5334
5335 APP_EXCEPTION.RAISE_EXCEPTION;
5336
5337 END IF;
5338
5339 FOR i in x_pmtsInPmtInstrTab.FIRST .. x_pmtsInPmtInstrTab.LAST LOOP
5340
5341 FOR j in l_docs_in_pmt_count.FIRST .. l_docs_in_pmt_count.LAST LOOP
5342
5343 IF (x_pmtsInPmtInstrTab(i).payment_id = l_docs_in_pmt_count(j).
5344 payment_id) THEN
5345
5346 x_pmtsInPmtInstrTab(i).document_count :=
5347 l_docs_in_pmt_count(j).doc_count;
5348
5349 print_debuginfo(l_module_name, 'Num docs for payment: '
5350 || x_pmtsInPmtInstrTab(i).payment_id
5351 || ' is: '
5352 || x_pmtsInPmtInstrTab(i).document_count
5353 );
5354
5355 /* exit inner loop */
5356 EXIT;
5357
5358 END IF;
5359
5360 END LOOP;
5361
5362 END LOOP;
5363
5364 END populateDocumentCount;
5365
5366 /*--------------------------------------------------------------------
5367 | NAME:
5368 | flagSeparateRemitAdvcPayments
5369 |
5370 | PURPOSE:
5371 |
5372 |
5373 |
5374 | PARAMETERS:
5375 | IN
5376 |
5377 |
5378 | OUT
5379 |
5380 |
5381 | RETURNS:
5382 |
5383 | NOTES:
5384 |
5385 *---------------------------------------------------------------------*/
5386 PROCEDURE flagSeparateRemitAdvcPayments(
5387 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
5388 x_pmtsInPmtInstrTab IN OUT NOCOPY pmtsInPmtInstrTabType,
5389 p_profileMap IN IBY_BUILD_UTILS_PKG.profileIdToCodeMapTabType
5390 )
5391 IS
5392
5393 l_docs_limit NUMBER(15);
5394 l_pmt_details_len_limit NUMBER(15);
5395 l_module_name VARCHAR2(200) := G_PKG_NAME
5396 || '.flagSeparateRemitAdvcPayments';
5397 l_profile_code IBY_PAYMENT_PROFILES.system_profile_code%TYPE;
5398 BEGIN
5399
5400 print_debuginfo(l_module_name, 'ENTER');
5401
5402 FOR i in x_pmtsInPmtInstrTab.FIRST .. x_pmtsInPmtInstrTab.LAST LOOP
5403
5404 print_debuginfo(l_module_name, 'Payment id: '
5405 || x_pmtsInPmtInstrTab(i).payment_id
5406 || ', profile id: '
5407 || x_pmtsInPmtInstrTab(i).profile_id
5408 || ', pmt details length: '
5409 || x_pmtsInPmtInstrTab(i).pmt_details_len
5410 );
5411
5412 BEGIN
5413
5414 /*
5415 * Get the system profile code from the profile id.
5416 */
5417 l_profile_code := IBY_BUILD_UTILS_PKG.getProfileCodeFromId(
5418 x_pmtsInPmtInstrTab(i).profile_id,
5419 p_profileMap
5420 );
5421
5422 /* pick only those records that have a
5423 format attached.
5424 */
5425
5426 SELECT
5427 NVL(document_count_limit, -1),
5428 NVL(payment_details_length_limit, -1)
5429 INTO
5430 l_docs_limit,
5431 l_pmt_details_len_limit
5432 FROM
5433 IBY_REMIT_ADVICE_SETUP
5434 WHERE
5435 system_profile_code = l_profile_code
5436 AND remittance_advice_format_code IS NOT NULL
5437 ;
5438
5439 EXCEPTION
5440 WHEN OTHERS THEN
5441 print_debuginfo(l_module_name, 'Non-fatal: Exception occured '
5442 || 'when attempting to get remit advice setup for '
5443 || 'profile id: '
5444 || x_pmtsInPmtInstrTab(i).profile_id
5445 || ' which mapped to system profile code: '
5446 || l_profile_code
5447 );
5448 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
5449 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
5450
5451 print_debuginfo(l_module_name, 'Separate remittance advice '
5452 || 'flag will not be set for payments with profile '
5453 || x_pmtsInPmtInstrTab(i).profile_id
5454 );
5455
5456 /*
5457 * In case of an exception, re-initialize the remit
5458 * advice parameters.
5459 */
5460 l_docs_limit := -1;
5461 l_pmt_details_len_limit := -1;
5462
5463 GOTO label_next_payment;
5464 END;
5465
5466 /*
5467 * If we reached here, it means that remittance conditions
5468 * were setup for this profile.
5469 */
5470
5471 print_debuginfo(l_module_name, 'Retrieved from remit advice setup - '
5472 || 'documents limit: '
5473 || l_docs_limit
5474 || ', pmt details length limit: '
5475 || l_pmt_details_len_limit
5476 || ' for profile id '
5477 || x_pmtsInPmtInstrTab(i).profile_id
5478 );
5479
5480 /*
5481 * If user has linked a separate remittance advice for
5482 * this profile, but has not setup any remittance
5483 * conditions, it means that the user wants remittance
5484 * advice to be generated unconditonally for payments
5485 * with this profile.
5486 */
5487 IF (l_docs_limit = -1 AND l_pmt_details_len_limit = -1) THEN
5488
5489 print_debuginfo(l_module_name, 'Unconditionally '
5490 || 'setting remit advice flag to '
5491 || ''''
5492 || 'Y'
5493 || ''''
5494 || ' for payment '
5495 || x_pmtsInPmtInstrTab(i).payment_id
5496 || ' with profile id '
5497 || x_pmtsInPmtInstrTab(i).profile_id
5498 );
5499
5500 x_pmtsInPmtInstrTab(i).separate_remit_advice_flag := 'Y';
5501
5502 ELSE
5503
5504 /*
5505 * Conditional remittance advice creation.
5506 */
5507
5508 /* check if docs limit has been exceeded */
5509 IF (l_docs_limit <> -1) THEN
5510
5511 IF (x_pmtsInPmtInstrTab(i).document_count
5512 > l_docs_limit) THEN
5513
5514 print_debuginfo(l_module_name, 'Setting '
5515 || 'remit advice flag for payment '
5516 || x_pmtsInPmtInstrTab(i).payment_id
5517 || ' because document count '
5518 || x_pmtsInPmtInstrTab(i).document_count
5519 || ' exceeded document limit '
5520 || l_docs_limit
5521 );
5522
5523 x_pmtsInPmtInstrTab(i).separate_remit_advice_flag := 'Y';
5524
5525 END IF;
5526
5527 END IF;
5528
5529 /* check if payment details length exceeded */
5530 IF (l_pmt_details_len_limit <> -1) THEN
5531
5532 IF (x_pmtsInPmtInstrTab(i).pmt_details_len
5533 > l_pmt_details_len_limit) THEN
5534
5535 print_debuginfo(l_module_name, 'Setting '
5536 || 'remit advice flag for payment '
5537 || x_pmtsInPmtInstrTab(i).payment_id
5538 || ' because pmt details length '
5539 || x_pmtsInPmtInstrTab(i).pmt_details_len
5540 || ' exceeded payment details limit '
5541 || l_pmt_details_len_limit
5542 );
5543
5544 x_pmtsInPmtInstrTab(i).separate_remit_advice_flag := 'Y';
5545
5546 END IF;
5547
5548 END IF;
5549
5550 END IF;
5551
5552 /*
5553 * We have just finished processing one payment.
5554 * If remittance flag is set for payment, set the
5555 * generate_remit_advice_flag for the corresponding
5556 * payment instruction.
5557 *
5558 * The payment instruction will use this flag to
5559 * determine whether to call separate remittance advice
5560 * creation flow (F17) for a payment instruction at the
5561 * end of the payment instruction creation flow.
5562 */
5563 IF (x_pmtsInPmtInstrTab(i).separate_remit_advice_flag = 'Y') THEN
5564 setRemitFlagForPmtInstruction(
5565 x_pmtsInPmtInstrTab(i).pay_instr_id,
5566 x_pmtInstrTab
5567 );
5568 END IF;
5569
5570
5571 <<label_next_payment>>
5572 NULL; -- null needed because of label
5573
5574 END LOOP;
5575
5576 print_debuginfo(l_module_name, 'EXIT');
5577
5578 END flagSeparateRemitAdvcPayments;
5579
5580 /*--------------------------------------------------------------------
5581 | NAME:
5582 | setRemitFlagForPmtInstruction
5583 |
5584 | PURPOSE:
5585 |
5586 |
5587 |
5588 | PARAMETERS:
5589 | IN
5590 |
5591 |
5592 | OUT
5593 |
5594 |
5595 | RETURNS:
5596 |
5597 | NOTES:
5598 |
5599 *---------------------------------------------------------------------*/
5600 PROCEDURE setRemitFlagForPmtInstruction(
5601 p_pmtInstrId IN IBY_PAY_INSTRUCTIONS_ALL.
5602 payment_instruction_id%TYPE,
5603 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType
5604 )
5605 IS
5606
5607 BEGIN
5608
5609 FOR i in x_pmtInstrTab.FIRST .. x_pmtInstrTab.LAST LOOP
5610
5611 IF (x_pmtInstrTab(i).payment_instruction_id = p_pmtInstrId) THEN
5612 x_pmtInstrTab(i).generate_sep_remit_advice_flag := 'Y';
5613 END IF;
5614
5615 END LOOP;
5616
5617 END setRemitFlagForPmtInstruction;
5618
5619 /*--------------------------------------------------------------------
5620 | NAME:
5621 | retrieveSortingOptions
5622 |
5623 | PURPOSE:
5624 |
5625 |
5626 |
5627 | PARAMETERS:
5628 | IN
5629 |
5630 |
5631 | OUT
5632 |
5633 |
5634 | RETURNS:
5635 |
5636 | NOTES:
5637 |
5638 *---------------------------------------------------------------------*/
5639 PROCEDURE retrieveSortingOptions(
5640 p_pmtInstrTab IN pmtInstrTabType,
5641 x_sortOptionsTab IN OUT NOCOPY sortOptionsTabType
5642 )
5643 IS
5644
5645 l_module_name VARCHAR2(200) := G_PKG_NAME || '.retrieveSortingOptions';
5646
5647 /*
5648 * Cursor to pick up all available profiles with their sorting
5649 * options from the IBY_INSTR_CREATION_RULES table.
5650 */
5651 CURSOR c_sorting_options
5652 IS
5653 SELECT
5654 system_profile_code,
5655 sort_option_1,
5656 sort_order_1,
5657 sort_option_2,
5658 sort_order_2,
5659 sort_option_3,
5660 sort_order_3
5661 FROM
5662 IBY_INSTR_CREATION_RULES;
5663
5664 BEGIN
5665
5666 print_debuginfo(l_module_name, 'ENTER');
5667
5668 /*
5669 * Pick up sorting options for all available profiles.
5670 */
5671 OPEN c_sorting_options;
5672 FETCH c_sorting_options BULK COLLECT INTO x_sortOptionsTab;
5673 CLOSE c_sorting_options;
5674
5675 IF (x_sortOptionsTab.COUNT = 0) THEN
5676
5677 print_debuginfo(l_module_name, 'No records were found in '
5678 || 'IBY_INSTR_CREATION_RULES. Exiting .. '
5679 );
5680
5681 print_debuginfo(l_module_name, 'EXIT');
5682
5683 RETURN;
5684
5685 END IF;
5686
5687 print_debuginfo(l_module_name, 'EXIT');
5688
5689 END retrieveSortingOptions;
5690
5691 /*--------------------------------------------------------------------
5692 | NAME:
5693 | updatePmtsWithSeqNumPmtRef
5694 |
5695 | PURPOSE:
5696 |
5697 | PARAMETERS:
5698 | IN
5699 |
5700 |
5701 | OUT
5702 |
5703 |
5704 | RETURNS:
5705 |
5706 | NOTES:
5707 |
5708 *---------------------------------------------------------------------*/
5709 PROCEDURE updatePmtsWithSeqNumPmtRef(
5710 p_sortedPmtsTab IN sortedPmtTabType
5711 )
5712 IS
5713 BEGIN
5714
5715 IF (p_sortedPmtsTab.COUNT = 0) THEN
5716 RETURN;
5717 END IF;
5718
5719 FOR i IN p_sortedPmtsTab.FIRST .. p_sortedPmtsTab.LAST LOOP
5720
5721 UPDATE
5722 IBY_PAYMENTS_ALL
5723 SET
5724 payment_reference_number = p_sortedPmtsTab(i).payment_ref,
5725 document_category_code = p_sortedPmtsTab(i).doc_cat_code,
5726 document_sequence_id = p_sortedPmtsTab(i).sequence_id,
5727 document_sequence_value = p_sortedPmtsTab(i).sequence_number
5728 WHERE
5729 payment_id = p_sortedPmtsTab(i).payment_id
5730 ;
5731
5732 END LOOP;
5733
5734 END updatePmtsWithSeqNumPmtRef;
5735
5736 /*--------------------------------------------------------------------
5737 | NAME:
5738 | updatePmtsWithSeqNum
5739 |
5740 | PURPOSE:
5741 |
5742 | PARAMETERS:
5743 | IN
5744 |
5745 |
5746 | OUT
5747 |
5748 |
5749 | RETURNS:
5750 |
5751 | NOTES:
5752 |
5753 *---------------------------------------------------------------------*/
5754 PROCEDURE updatePmtsWithSeqNum(
5755 p_sortedPmtsTab IN sortedPmtTabType
5756 )
5757 IS
5758 BEGIN
5759
5760 IF (p_sortedPmtsTab.COUNT = 0) THEN
5761 RETURN;
5762 END IF;
5763
5764 FOR i IN p_sortedPmtsTab.FIRST .. p_sortedPmtsTab.LAST LOOP
5765
5766 UPDATE
5767 IBY_PAYMENTS_ALL
5768 SET
5769 document_category_code = p_sortedPmtsTab(i).doc_cat_code,
5770 document_sequence_id = p_sortedPmtsTab(i).sequence_id,
5771 document_sequence_value = p_sortedPmtsTab(i).sequence_number
5772 WHERE
5773 payment_id = p_sortedPmtsTab(i).payment_id
5774 ;
5775
5776 END LOOP;
5777
5778 END updatePmtsWithSeqNum;
5779
5780 /*--------------------------------------------------------------------
5781 | NAME:
5782 | updatePmtsWithPmtRef
5783 |
5784 | PURPOSE:
5785 |
5786 | PARAMETERS:
5787 | IN
5788 |
5789 |
5790 | OUT
5791 |
5792 |
5793 | RETURNS:
5794 |
5795 | NOTES:
5796 |
5797 *---------------------------------------------------------------------*/
5798 PROCEDURE updatePmtsWithPmtRef(
5799 p_sortedPmtsTab IN sortedPmtTabType
5800 )
5801 IS
5802 BEGIN
5803
5804 IF (p_sortedPmtsTab.COUNT = 0) THEN
5805 RETURN;
5806 END IF;
5807
5808 FOR i IN p_sortedPmtsTab.FIRST .. p_sortedPmtsTab.LAST LOOP
5809
5810 UPDATE
5811 IBY_PAYMENTS_ALL
5812 SET
5813 payment_reference_number = p_sortedPmtsTab(i).payment_ref
5814 WHERE
5815 payment_id = p_sortedPmtsTab(i).payment_id
5816 ;
5817
5818 END LOOP;
5819
5820 END updatePmtsWithPmtRef;
5821
5822 /*--------------------------------------------------------------------
5823 | NAME:
5824 | deriveDistinctAccessTypsForIns
5825 |
5826 | PURPOSE:
5827 |
5828 |
5829 | PARAMETERS:
5830 | IN
5831 |
5832 |
5833 | OUT
5834 |
5835 |
5836 | RETURNS:
5837 |
5838 | NOTES:
5839 |
5840 *---------------------------------------------------------------------*/
5841 PROCEDURE deriveDistinctAccessTypsForIns(
5842 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id
5843 %TYPE,
5844 p_pmt_function IN IBY_PAYMENTS_ALL.payment_function%TYPE,
5845 p_org_id IN IBY_PAYMENTS_ALL.org_id%TYPE,
5846 p_org_type IN IBY_PAYMENTS_ALL.org_type%TYPE,
5847 x_pmtFxAccessTypesTab IN OUT NOCOPY distinctPmtFxAccessTab,
5848 x_orgAccessTypesTab IN OUT NOCOPY distinctOrgAccessTab
5849 )
5850 IS
5851
5852 l_pmt_function_found BOOLEAN := FALSE;
5853 l_org_found BOOLEAN := FALSE;
5854 l_index NUMBER := -1;
5855
5856 l_module_name VARCHAR2(200) := G_PKG_NAME ||
5857 '.deriveDistinctAccessTypsForIns';
5858
5859 BEGIN
5860
5861 print_debuginfo(l_module_name, 'ENTER');
5862
5863 IF (x_pmtFxAccessTypesTab.COUNT <> 0) THEN
5864
5865 FOR i IN x_pmtFxAccessTypesTab.FIRST .. x_pmtFxAccessTypesTab.LAST LOOP
5866
5867 /* search for given payment function */
5868 /* for the given payment instruction id */
5869
5870 /*
5871 * Fix for bug 5331992:
5872 *
5873 * Search for the payment functions stored in
5874 * x_pmtFxAccessTypesTab for the given pmt
5875 * instruction id (not across all records in
5876 * in x_pmtFxAccessTypesTab)
5877 */
5878 IF (x_pmtFxAccessTypesTab(i).payment_function = p_pmt_function AND
5879 x_pmtFxAccessTypesTab(i).object_id = p_instruction_id)
5880 THEN
5881
5882 l_pmt_function_found := TRUE;
5883
5884 END IF;
5885
5886 END LOOP;
5887
5888 END IF;
5889
5890 IF (x_orgAccessTypesTab.COUNT <> 0) THEN
5891
5892 FOR i IN x_orgAccessTypesTab.FIRST .. x_orgAccessTypesTab.LAST LOOP
5893
5894 /* search for given org */
5895 /* for the given payment instruction id */
5896
5897 /*
5898 * Fix for bug 5331992:
5899 *
5900 * Search for the orgs stored in
5901 * x_orgAccessTypesTab for the given pmt
5902 * instruction id (not across all records in
5903 * in x_orgAccessTypesTab)
5904 */
5905 IF (x_orgAccessTypesTab(i).org_id = p_org_id AND
5906 x_orgAccessTypesTab(i).org_type = p_org_type AND
5907 x_orgAccessTypesTab(i).object_id = p_instruction_id) THEN
5908
5909 l_org_found := TRUE;
5910
5911 END IF;
5912
5913 END LOOP;
5914
5915 END IF;
5916
5917
5918 /* if payment function was not found, add to list */
5919 IF (l_pmt_function_found = FALSE) THEN
5920
5921 l_index := x_pmtFxAccessTypesTab.COUNT;
5922
5923 x_pmtFxAccessTypesTab(l_index + 1).
5924 payment_function := p_pmt_function;
5925
5926 print_debuginfo(l_module_name, 'Found distinct pmt function: '
5927 || p_pmt_function
5928 || ' for payment instruction '
5929 || p_instruction_id
5930 );
5931
5932 /*
5933 * These attributes can be hardcoded.
5934 */
5935 x_pmtFxAccessTypesTab(l_index + 1).object_id := p_instruction_id;
5936 x_pmtFxAccessTypesTab(l_index + 1).object_type :=
5937 OBJECT_TYPE_INSTR;
5938
5939 END IF;
5940
5941 /* if org was not found, add to list */
5942 IF (l_org_found = FALSE) THEN
5943
5944 l_index := x_orgAccessTypesTab.COUNT;
5945
5946 x_orgAccessTypesTab(l_index + 1).org_id := p_org_id;
5947 x_orgAccessTypesTab(l_index + 1).org_type := p_org_type;
5948
5949 print_debuginfo(l_module_name, 'Found distinct org id: '
5950 || p_org_id
5951 || ' with org type '
5952 || p_org_type
5953 || ' for payment instruction '
5954 || p_instruction_id
5955 );
5956
5957 /*
5958 * These attributes can be hardcoded.
5959 */
5960 x_orgAccessTypesTab(l_index + 1).object_id := p_instruction_id;
5961 x_orgAccessTypesTab(l_index + 1).object_type := OBJECT_TYPE_INSTR;
5962
5963 END IF;
5964
5965 print_debuginfo(l_module_name, 'EXIT');
5966
5967 END deriveDistinctAccessTypsForIns;
5968
5969 /*--------------------------------------------------------------------
5970 | NAME:
5971 | insertDistinctAccessTypsForIns
5972 |
5973 | PURPOSE:
5974 |
5975 |
5976 | PARAMETERS:
5977 | IN
5978 |
5979 |
5980 | OUT
5981 |
5982 |
5983 | RETURNS:
5984 |
5985 | NOTES:
5986 |
5987 *---------------------------------------------------------------------*/
5988 PROCEDURE insertDistinctAccessTypsForIns(
5989 p_pmtFxAccessTypesTab IN distinctPmtFxAccessTab,
5990 p_orgAccessTypesTab IN distinctOrgAccessTab
5991 )
5992 IS
5993
5994 TYPE t_object_id IS TABLE OF
5995 NUMBER(15)
5996 INDEX BY BINARY_INTEGER;
5997 TYPE t_object_type IS TABLE OF
5998 VARCHAR2(30)
5999 INDEX BY BINARY_INTEGER;
6000 TYPE t_payment_function IS TABLE OF
6001 VARCHAR2(30)
6002 INDEX BY BINARY_INTEGER;
6003 TYPE t_org_type IS TABLE OF
6004 VARCHAR2(30)
6005 INDEX BY BINARY_INTEGER;
6006 TYPE t_org_id IS TABLE OF
6007 NUMBER(15)
6008 INDEX BY BINARY_INTEGER;
6009
6010 l_object_id t_object_id;
6011 l_object_type t_object_type;
6012 l_payment_function t_payment_function;
6013 l_org_type t_org_type;
6014 l_org_id t_org_id;
6015
6016 BEGIN
6017
6018 IF (p_pmtFxAccessTypesTab.COUNT <> 0) THEN
6019
6020 FOR i IN p_pmtFxAccessTypesTab.FIRST .. p_pmtFxAccessTypesTab.LAST
6021 LOOP
6022
6023 l_object_id(i) := p_pmtFxAccessTypesTab(i).object_id;
6024 l_object_type(i) := p_pmtFxAccessTypesTab(i).object_type;
6025 l_payment_function(i) := p_pmtFxAccessTypesTab(i).payment_function;
6026
6027 END LOOP;
6028
6029 END IF;
6030
6031 IF (p_orgAccessTypesTab.COUNT <> 0) THEN
6032
6033 FOR i IN p_orgAccessTypesTab.FIRST .. p_orgAccessTypesTab.LAST
6034 LOOP
6035
6036 l_object_id(i) := p_orgAccessTypesTab(i).object_id;
6037 l_object_type(i) := p_orgAccessTypesTab(i).object_type;
6038 l_org_type(i) := p_orgAccessTypesTab(i).org_type;
6039 l_org_id(i) := p_orgAccessTypesTab(i).org_id;
6040
6041 END LOOP;
6042
6043 END IF;
6044
6045
6046 FORALL i in p_pmtFxAccessTypesTab.FIRST..p_pmtFxAccessTypesTab.LAST
6047 INSERT INTO IBY_PROCESS_FUNCTIONS
6048 (
6049 object_id,
6050 object_type,
6051 payment_function
6052 )
6053 VALUES
6054 (
6055 l_object_id(i),
6056 l_object_type(i),
6057 l_payment_function(i)
6058 )
6059 ;
6060
6061 FORALL j in p_orgAccessTypesTab.FIRST..p_orgAccessTypesTab.LAST
6062 INSERT INTO IBY_PROCESS_ORGS
6063 (
6064 object_id,
6065 object_type,
6066 org_id,
6067 org_type
6068 )
6069 VALUES
6070 (
6071 l_object_id(j),
6072 l_object_type(j),
6073 l_org_id(j),
6074 l_org_type(j)
6075 )
6076 ;
6077
6078 END insertDistinctAccessTypsForIns;
6079
6080 /*--------------------------------------------------------------------
6081 | NAME:
6082 | checkIfDocSeqCompleted
6083 |
6084 | PURPOSE:
6085 |
6086 |
6087 | PARAMETERS:
6088 | IN
6089 |
6090 | OUT
6091 |
6092 |
6093 | RETURNS:
6094 |
6095 | NOTES:
6096 |
6097 *---------------------------------------------------------------------*/
6098 FUNCTION checkIfDocSeqCompleted(
6099 p_instr_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE
6100 )
6101 RETURN BOOLEAN
6102 IS
6103
6104 l_ret_val BOOLEAN;
6105 l_seq_num NUMBER;
6106
6107 l_module_name VARCHAR2(200) := G_PKG_NAME || '.checkIfDocSeqCompleted';
6108
6109 BEGIN
6110
6111 print_debuginfo(l_module_name, 'ENTER');
6112 print_debuginfo(l_module_name, 'instr id = ' || p_instr_id);
6113
6114 /*
6115 * It is good enough to check one payment for this
6116 * payment instruction to see if document sequencing
6117 * has already taken place for this payment instruction.
6118 *
6119 * By design, either all the payments of this payment
6120 * instruction are assigned document sequence numbers
6121 * or none are.
6122 */
6123 SELECT
6124 document_sequence_value
6125 INTO
6126 l_seq_num
6127 FROM
6128 IBY_PAYMENTS_ALL
6129 WHERE
6130 payment_instruction_id = p_instr_id AND
6131 ROWNUM = 1
6132 ;
6133
6134 /*
6135 * Return appropriate flag.
6136 */
6137 IF (l_seq_num IS NULL) THEN
6138
6139 l_ret_val := FALSE;
6140 print_debuginfo(l_module_name, 'Returning FALSE');
6141
6142 ELSE
6143
6144 l_ret_val := TRUE;
6145 print_debuginfo(l_module_name, 'Returning TRUE');
6146
6147 END IF;
6148
6149 return l_ret_val;
6150
6151 print_debuginfo(l_module_name, 'EXIT');
6152
6153 EXCEPTION
6154 WHEN OTHERS THEN
6155
6156 print_debuginfo(l_module_name, 'Exception occured when '
6157 || 'checking whether document sequencing has been '
6158 || 'already completed for payment instruction '
6159 || p_instr_id,
6160 FND_LOG.LEVEL_UNEXPECTED
6161 );
6162
6163 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
6164 FND_LOG.LEVEL_UNEXPECTED);
6165 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
6166 FND_LOG.LEVEL_UNEXPECTED);
6167
6168 print_debuginfo(l_module_name, 'EXIT');
6169
6170 /*
6171 * Propogate exception to caller.
6172 */
6173 RAISE;
6174
6175 END checkIfDocSeqCompleted;
6176
6177 /*--------------------------------------------------------------------
6178 | NAME:
6179 | checkIfPmtRefCompleted
6180 |
6181 | PURPOSE:
6182 |
6183 |
6184 | PARAMETERS:
6185 | IN
6186 |
6187 | OUT
6188 |
6189 |
6190 | RETURNS:
6191 |
6192 | NOTES:
6193 |
6194 *---------------------------------------------------------------------*/
6195 FUNCTION checkIfPmtRefCompleted(
6196 p_instr_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE
6197 )
6198 RETURN BOOLEAN
6199 IS
6200
6201 l_ret_val BOOLEAN;
6202 l_pmt_ref NUMBER;
6203
6204 l_module_name VARCHAR2(200) := G_PKG_NAME || '.checkIfPmtRefCompleted';
6205
6206 BEGIN
6207
6208 print_debuginfo(l_module_name, 'ENTER');
6209
6210 /*
6211 * It is good enough to check one payment for this
6212 * payment instruction to see if payment referencing
6213 * has already taken place for this payment instruction.
6214 *
6215 * By design, either all the payments of this payment
6216 * instruction are assigned payment reference numbers
6217 * or none are.
6218 */
6219 SELECT
6220 payment_reference_number
6221 INTO
6222 l_pmt_ref
6223 FROM
6224 IBY_PAYMENTS_ALL
6225 WHERE
6226 payment_instruction_id = p_instr_id AND
6227 ROWNUM = 1
6228 ;
6229
6230 /*
6231 * Return appropriate flag.
6232 */
6233 IF (l_pmt_ref IS NULL) THEN
6234
6235 l_ret_val := FALSE;
6236 print_debuginfo(l_module_name, 'Returning FALSE');
6237
6238 ELSE
6239
6240 l_ret_val := TRUE;
6241 print_debuginfo(l_module_name, 'Returning TRUE');
6242
6243 END IF;
6244
6245 return l_ret_val;
6246
6247 print_debuginfo(l_module_name, 'EXIT');
6248
6249 END checkIfPmtRefCompleted;
6250
6251 /*--------------------------------------------------------------------
6252 | NAME:
6253 | getSortedPmtsForInstr
6254 |
6255 | PURPOSE:
6256 |
6257 |
6258 |
6259 | PARAMETERS:
6260 | IN
6261 |
6262 |
6263 | OUT
6264 |
6265 |
6266 | RETURNS:
6267 |
6268 | NOTES:
6269 |
6270 *---------------------------------------------------------------------*/
6271 PROCEDURE getSortedPmtsForInstr(
6272 p_instr_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
6273 p_profile_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_profile_id%TYPE,
6274 p_sortOptionsTab IN sortOptionsTabType,
6275 p_profileMap IN IBY_BUILD_UTILS_PKG.profileIdToCodeMapTabType,
6276 x_sortedPmtTab IN OUT NOCOPY sortedPmtTabType
6277 )
6278 IS
6279
6280 l_sort_options_rec sortOptionsRecType;
6281
6282 l_sql_chunk VARCHAR2(3000);
6283 l_cursor_stmt VARCHAR2(5000);
6284
6285 TYPE dyn_sort_payments IS REF CURSOR;
6286 l_sort_pmts_cursor dyn_sort_payments;
6287
6288 l_module_name VARCHAR2(200) := G_PKG_NAME || '.getSortedPmtsForInstr';
6289
6290
6291
6292 BEGIN
6293
6294 print_debuginfo(l_module_name, 'ENTER');
6295
6296 l_sort_options_rec := getSortOptionsForProfile(
6297 p_sortOptionsTab,
6298 p_profileMap,
6299 p_profile_id
6300 );
6301
6302 IF (l_sort_options_rec.pmt_profile_cd IS NULL) THEN
6303
6304 print_debuginfo(l_module_name, 'No sort options set '
6305 || 'up for payment profile '
6306 || p_profile_id
6307 || '. Payments of payment instruction '
6308 || p_instr_id
6309 || ' will *not* be sorted before numbering.'
6310 );
6311
6312 ELSE
6313
6314 print_debuginfo(l_module_name, 'Sort options set '
6315 || 'up for payment profile '
6316 || p_profile_id
6317 || '. Payments of payment instruction '
6318 || p_instr_id
6319 || ' will be sorted before numbering.'
6320 );
6321
6322 l_sql_chunk := buildOrderByFromSortOptions(
6323 l_sort_options_rec);
6324
6325 print_debuginfo(l_module_name, 'Formed order by clause: '
6326 || l_sql_chunk);
6327
6328 END IF;
6329
6330 /*
6331 * Pick up all payments for this payment instruction
6332 * after sorting them by the provided sort criteria (if
6333 * provided).
6334 *
6335 * Pad null values in the SELECT statement for the fields
6336 * that will be filled up later.
6337 */
6338
6339 /* Perf Bug 5872977 */
6340 IF l_sql_chunk is NOT NULL THEN
6341
6342 l_cursor_stmt :=
6343
6344 'SELECT '
6345 || 'pmt.payment_id, '
6346 || 'NVL (pmt.payment_reference_number, -1), '
6347 || 'pmt.payment_instruction_id, '
6348 || 'ins.payment_instruction_status, '
6349 || 'req.calling_app_id, '
6350 || 'pmt.legal_entity_id, '
6351 || 'pmt_mthds.document_category_code, '
6352 || 'pmt.payment_date, '
6353 || 'NULL, ' /* ledger id */
6354 || 'NULL, ' /* sequence number */
6355 || 'NULL ' /* sequence id */
6356 || 'FROM '
6357 || 'IBY_PAYMENTS_ALL pmt, '
6358 || 'IBY_PAY_SERVICE_REQUESTS req, '
6359 || 'IBY_PAYMENT_METHODS_VL pmt_mthds, '
6360 || 'IBY_PAY_INSTRUCTIONS_ALL ins, '
6361 || 'HZ_PARTIES payee, '
6362 || 'HZ_LOCATIONS payee_location, '
6363 || 'IBY_EXT_BANK_ACCOUNTS payee_bank ' -- Bug 5872977
6364 || 'WHERE '
6365 || ' pmt.payment_instruction_id = :instr_id '
6366 || 'AND pmt.payment_status = :pmt_status '
6367 || 'AND pmt.payment_instruction_id = '
6368 || ' ins.payment_instruction_id '
6369 || 'AND pmt.payment_service_request_id = '
6370 || ' req.payment_service_request_id '
6371 || 'AND pmt.payment_method_code = '
6372 || ' pmt_mthds.payment_method_code '
6373 || 'AND pmt.payee_party_id = payee.party_id '
6374 || 'AND pmt.remit_to_location_id = '
6375 || ' payee_location.location_id(+) '
6376 || 'AND pmt.external_bank_account_id = '
6377 || ' payee_bank.ext_bank_account_id(+) '
6378 || NVL (l_sql_chunk, 'AND 1=1 ')
6379 ;
6380 ELSE
6381 l_cursor_stmt :=
6382
6383 'SELECT '
6384 || 'pmt.payment_id, '
6385 || 'NVL (pmt.payment_reference_number, -1), '
6386 || 'pmt.payment_instruction_id, '
6387 || 'ins.payment_instruction_status, '
6388 || 'req.calling_app_id, '
6389 || 'pmt.legal_entity_id, '
6390 || 'pmt_mthds.document_category_code, '
6391 || 'pmt.payment_date, '
6392 || 'NULL, ' /* ledger id */
6393 || 'NULL, ' /* sequence number */
6394 || 'NULL ' /* sequence id */
6395 || 'FROM '
6396 || 'IBY_PAYMENTS_ALL pmt, '
6397 || 'IBY_PAY_SERVICE_REQUESTS req, '
6398 || 'IBY_PAYMENT_METHODS_VL pmt_mthds, '
6399 || 'IBY_PAY_INSTRUCTIONS_ALL ins, '
6400 || 'HZ_PARTIES payee '
6401 || 'WHERE '
6402 || ' pmt.payment_instruction_id = :instr_id '
6403 || 'AND pmt.payment_status = :pmt_status '
6404 || 'AND pmt.payment_instruction_id = '
6405 || ' ins.payment_instruction_id '
6406 || 'AND pmt.payment_service_request_id = '
6407 || ' req.payment_service_request_id '
6408 || 'AND pmt.payment_method_code = '
6409 || ' pmt_mthds.payment_method_code '
6410 || 'AND pmt.payee_party_id = payee.party_id '
6411 ;
6412 END IF;
6413
6414 /* Perf Bug 5872977 */
6415
6416 /*
6417 * Print the cursor statement for debug purposes.
6418 */
6419 print_debuginfo(l_module_name, 'Dynamic cursor statement: ');
6420 IBY_BUILD_UTILS_PKG.printWrappedString(l_cursor_stmt);
6421
6422 print_debuginfo(l_module_name, 'cursor bind parameters - ');
6423 print_debuginfo(l_module_name, ':instr_id = '
6424 || p_instr_id);
6425 print_debuginfo(l_module_name, ':pmt_status = '
6426 || PMT_STATUS_INSTR_CREATED);
6427
6428 OPEN l_sort_pmts_cursor FOR
6429 l_cursor_stmt
6430 USING
6431 p_instr_id,
6432 PMT_STATUS_INSTR_CREATED
6433 ;
6434
6435 FETCH l_sort_pmts_cursor BULK COLLECT INTO x_sortedPmtTab;
6436 CLOSE l_sort_pmts_cursor;
6437
6438 print_debuginfo(l_module_name, 'Number of payments picked '
6439 || 'up for instruction id '
6440 || p_instr_id
6441 || ' is '
6442 || x_sortedPmtTab.COUNT
6443 );
6444
6445 print_debuginfo(l_module_name, 'EXIT');
6446
6447 EXCEPTION
6448 WHEN OTHERS THEN
6449
6450 print_debuginfo(l_module_name, 'Exception occured when attempting '
6451 || 'to get payments in sorted order for payment instruction '
6452 || p_instr_id,
6453 FND_LOG.LEVEL_UNEXPECTED
6454 );
6455
6456 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE,
6457 FND_LOG.LEVEL_UNEXPECTED);
6458 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM,
6459 FND_LOG.LEVEL_UNEXPECTED);
6460
6461 /*
6462 * Propagate exception to caller.
6463 */
6464 APP_EXCEPTION.RAISE_EXCEPTION;
6465
6466 END getSortedPmtsForInstr;
6467
6468 /*--------------------------------------------------------------------
6469 | NAME:
6470 | dummyGLAPI
6471 |
6472 | PURPOSE:
6473 | Dummy method; to be used for testing purposes.
6474 |
6475 |
6476 | PARAMETERS:
6477 | IN
6478 |
6479 | OUT
6480 |
6481 |
6482 | RETURNS:
6483 |
6484 | NOTES:
6485 |
6486 *---------------------------------------------------------------------*/
6487 PROCEDURE dummyGLAPI(
6488 p_exch_date IN DATE,
6489 p_source_amount IN NUMBER,
6490 p_source_curr IN VARCHAR2,
6491 p_decl_curr IN VARCHAR2,
6492 p_decl_fx_rate_type IN VARCHAR2,
6493 x_decl_amount OUT NOCOPY NUMBER)
6494 IS
6495
6496 BEGIN
6497
6498 x_decl_amount := p_source_amount * 2;
6499
6500 END dummyGLAPI;
6501
6502 /*--------------------------------------------------------------------
6503 | NAME:
6504 | dummy_ruleFunction
6505 |
6506 | PURPOSE:
6507 | Dummy method; to be used for testing purposes.
6508 | You will need to register this function with an event
6509 | subscription for this function to be called.
6510 |
6511 | PARAMETERS:
6512 | IN
6513 |
6514 | OUT
6515 |
6516 |
6517 | RETURNS:
6518 |
6519 | NOTES:
6520 |
6521 *---------------------------------------------------------------------*/
6522 FUNCTION dummy_ruleFunction(
6523 p_subscription IN RAW,
6524 p_event IN OUT NOCOPY WF_EVENT_T
6525 )
6526 RETURN VARCHAR2
6527 IS
6528
6529 l_module_name VARCHAR2(200) := G_PKG_NAME || '.dummy_ruleFunction';
6530 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
6531 l_parameter_t wf_parameter_t:= wf_parameter_t(NULL, NULL);
6532 l_parameter_name l_parameter_t.name%TYPE;
6533 l_parameter_value l_parameter_t.value%TYPE;
6534 l_clob CLOB;
6535 i PLS_INTEGER;
6536
6537 BEGIN
6538
6539 print_debuginfo(l_module_name, 'ENTER');
6540
6541 l_parameter_list := p_event.getParameterList();
6542
6543 IF (l_parameter_list IS NOT NULL) THEN
6544 i := l_parameter_list.FIRST;
6545 WHILE (i <= l_parameter_list.LAST) LOOP
6546 l_parameter_name := NULL;
6547 l_parameter_value := NULL;
6548
6549 l_parameter_name := l_parameter_list(i).getName();
6550 l_parameter_value := l_parameter_list(i).getValue();
6551
6552 print_debuginfo(l_module_name, 'Param Name: '
6553 || l_parameter_name || ', Param Value: '
6554 || l_parameter_value
6555 );
6556
6557 i := l_parameter_list.NEXT(i);
6558 END LOOP;
6559 END IF;
6560
6561 l_clob := p_event.getEventData();
6562
6563 IF (l_clob IS NOT NULL) THEN
6564 print_debuginfo(l_module_name, 'Clob is not null');
6565 IBY_BUILD_UTILS_PKG.printXMLClob(l_clob);
6566 END IF;
6567
6568 print_debuginfo(l_module_name, 'EXIT');
6569
6570 RETURN 'SUCCESS';
6571
6572 END dummy_ruleFunction;
6573
6574 /*--------------------------------------------------------------------
6575 | NAME:
6576 | get_instruction_profile
6577 |
6578 |
6579 | PURPOSE:
6580 |
6581 |
6582 | PARAMETERS:
6583 | IN
6584 |
6585 |
6586 | OUT
6587 |
6588 |
6589 | RETURNS:
6590 |
6591 | NOTES:
6592 |
6593 *---------------------------------------------------------------------*/
6594 FUNCTION get_instruction_profile (
6595 l_pmt_instr_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE)
6596 RETURN NUMBER
6597 IS
6598
6599 l_pmt_instr_profile IBY_PAY_INSTRUCTIONS_ALL.payment_profile_id%TYPE;
6600 l_module_name VARCHAR2(200) := G_PKG_NAME || '.get_instruction_profile';
6601
6602 BEGIN
6603
6604 SELECT
6605 payment_profile_id
6606 INTO
6607 l_pmt_instr_profile
6608 FROM
6609 IBY_PAY_INSTRUCTIONS_ALL
6610 WHERE
6611 payment_instruction_id = l_pmt_instr_id
6612 ;
6613
6614 RETURN l_pmt_instr_profile;
6615
6616 EXCEPTION
6617 WHEN OTHERS THEN
6618 print_debuginfo(l_module_name, 'Exception occured when '
6619 || 'retrieving profile for '
6620 || 'payment instruction '
6621 || l_pmt_instr_id,
6622 FND_LOG.LEVEL_UNEXPECTED
6623 );
6624 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE,
6625 FND_LOG.LEVEL_UNEXPECTED);
6626 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM,
6627 FND_LOG.LEVEL_UNEXPECTED);
6628
6629 /*
6630 * Propogate exception to caller.
6631 */
6632 RAISE;
6633
6634 END get_instruction_profile;
6635
6636 /*--------------------------------------------------------------------
6637 | NAME:
6638 | retrieveSortOptionForProfile
6639 |
6640 | PURPOSE:
6641 |
6642 |
6643 |
6644 | PARAMETERS:
6645 | IN
6646 |
6647 |
6648 | OUT
6649 |
6650 |
6651 | RETURNS:
6652 |
6653 | NOTES:
6654 |
6655 *---------------------------------------------------------------------*/
6656 PROCEDURE retrieveSortOptionForProfile(
6657 p_profile_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_profile_id%TYPE,
6658 x_sortOptionsTab IN OUT NOCOPY sortOptionsTabType
6659 )
6660 IS
6661
6662 l_module_name VARCHAR2(200) := G_PKG_NAME || '.retrieveSortOptionForProfile';
6663
6664 /*
6665 * Cursor to pick up the sorting options for the given
6666 * profile from the IBY_INSTR_CREATION_RULES table.
6667 */
6668 CURSOR c_sorting_options
6669 IS
6670 SELECT
6671 icr.system_profile_code,
6672 icr.sort_option_1,
6673 icr.sort_order_1,
6674 icr.sort_option_2,
6675 icr.sort_order_2,
6676 icr.sort_option_3,
6677 icr.sort_order_3
6678 FROM
6679 IBY_INSTR_CREATION_RULES icr,
6680 IBY_PAYMENT_PROFILES prof
6681 WHERE
6682 prof.payment_profile_id = p_profile_id AND
6683 icr.system_profile_code = prof.system_profile_code
6684 ;
6685
6686 BEGIN
6687
6688 print_debuginfo(l_module_name, 'ENTER');
6689
6690 /*
6691 * Pick up sorting options for all available profiles.
6692 */
6693 OPEN c_sorting_options;
6694 FETCH c_sorting_options BULK COLLECT INTO x_sortOptionsTab;
6695 CLOSE c_sorting_options;
6696
6697 IF (x_sortOptionsTab.COUNT = 0) THEN
6698
6699 print_debuginfo(l_module_name, 'No records were found in '
6700 || 'IBY_INSTR_CREATION_RULES. Exiting .. '
6701 );
6702
6703 print_debuginfo(l_module_name, 'EXIT');
6704
6705 RETURN;
6706
6707 END IF;
6708
6709 print_debuginfo(l_module_name, 'EXIT');
6710
6711 END retrieveSortOptionForProfile;
6712
6713 /*--------------------------------------------------------------------
6714 | NAME:
6715 | initializeInstrs
6716 |
6717 | PURPOSE:
6718 |
6719 |
6720 | PARAMETERS:
6721 | IN
6722 |
6723 |
6724 | OUT
6725 |
6726 |
6727 | RETURNS:
6728 |
6729 | NOTES:
6730 |
6731 *---------------------------------------------------------------------*/
6732 PROCEDURE initializeInstrs(
6733 x_pmtInstrTab IN OUT NOCOPY pmtInstrTabType,
6734 p_single_pmt_flow_flag IN VARCHAR2
6735 )
6736 IS
6737
6738 l_module_name VARCHAR2(200) := G_PKG_NAME || '.initializeInstrs';
6739 l_process_type IBY_PAY_INSTRUCTIONS_ALL.process_type%TYPE;
6740 l_multi_pi_flag BOOLEAN := FALSE;
6741 l_instr_seq_num VARCHAR2(200);
6742
6743 BEGIN
6744
6745 print_debuginfo(l_module_name, 'ENTER');
6746
6747 IF (x_pmtInstrTab.COUNT = 0) THEN
6748 print_debuginfo(l_module_name, 'No instructions to initialize.');
6749 print_debuginfo(l_module_name, 'EXIT');
6750 RETURN;
6751 END IF;
6752
6753 IF (UPPER(p_single_pmt_flow_flag) = 'Y') THEN
6754 l_process_type := 'IMMEDIATE';
6755 ELSE
6756 l_process_type := 'STANDARD';
6757 END IF;
6758
6759 print_debuginfo(l_module_name, 'Process type of payment instruction(s) '
6760 || 'initialized to '
6761 || l_process_type
6762 );
6763
6764 /*
6765 * Determine whether multiple payment instructions
6766 * were created during this run. This will have an
6767 * impact on the way we set the admin assigned ref
6768 * on the payment instruction.
6769 */
6770 IF (x_pmtInstrTab.COUNT > 1) THEN
6771 l_multi_pi_flag := TRUE;
6772 ELSE
6773 l_multi_pi_flag := FALSE;
6774 END IF;
6775
6776 FOR i IN x_pmtInstrTab.FIRST .. x_pmtInstrTab.LAST LOOP
6777
6778 x_pmtInstrTab(i).process_type := l_process_type;
6779 x_pmtInstrTab(i).payment_instruction_status := 'CREATED';
6780 x_pmtInstrTab(i).payments_complete_code :=
6781 NVL(x_pmtInstrTab(i).payments_complete_code, 'NO');
6782 x_pmtInstrTab(i).generate_sep_remit_advice_flag :=
6783 NVL(x_pmtInstrTab(i).generate_sep_remit_advice_flag, 'N');
6784 x_pmtInstrTab(i).remittance_advice_created_flag :=
6785 NVL(x_pmtInstrTab(i).remittance_advice_created_flag, 'N');
6786 x_pmtInstrTab(i).regulatory_report_created_flag :=
6787 NVL(x_pmtInstrTab(i).regulatory_report_created_flag, 'N');
6788 x_pmtInstrTab(i).bill_payable_flag :=
6789 NVL(x_pmtInstrTab(i).bill_payable_flag, 'N');
6790 x_pmtInstrTab(i).positive_pay_file_created_flag :=
6791 NVL(x_pmtInstrTab(i).positive_pay_file_created_flag, 'N');
6792
6793 /*
6794 * Fix for bug 5023151:
6795 *
6796 * Do not overwrite the 'print now' or 'transmit now'
6797 * flags if they are already set.
6798 */
6799 x_pmtInstrTab(i).print_instruction_immed_flag :=
6800 NVL(x_pmtInstrTab(i).print_instruction_immed_flag, 'N');
6801 x_pmtInstrTab(i).transmit_instr_immed_flag :=
6802 NVL(x_pmtInstrTab(i).transmit_instr_immed_flag, 'N');
6803
6804 x_pmtInstrTab(i).created_by := fnd_global.user_id;
6805 x_pmtInstrTab(i).creation_date := sysdate;
6806 x_pmtInstrTab(i).last_updated_by := fnd_global.user_id;
6807 x_pmtInstrTab(i).last_update_login := fnd_global.user_id;
6808 x_pmtInstrTab(i).last_update_date := sysdate;
6809 x_pmtInstrTab(i).object_version_number := 1;
6810
6811 /*
6812 * Fix for bug 5337042:
6813 *
6814 * If there is more than one PI getting created
6815 * then append the pay_admin_assigned_ref with numbers
6816 */
6817 IF (l_multi_pi_flag = TRUE AND
6818 x_pmtInstrTab(i).pay_admin_assigned_ref_code IS NOT NULL) THEN
6819
6820 /*
6821 * Fix for bug 5479918:
6822 *
6823 * Remove space after the hyphen
6824 *
6825 * When multiple PIs are created use the following
6826 * format for the admin ref -
6827 *
6828 * LSBP-1
6829 * LSBP-2
6830 * LSBP-3
6831 *
6832 * :
6833 *
6834 * LSB-n
6835 *
6836 * Assuming that the user gave 'LSB' as the admin assigned ref
6837 * conc program param.
6838 */
6839 x_pmtInstrTab(i).pay_admin_assigned_ref_code :=
6840 x_pmtInstrTab(i).pay_admin_assigned_ref_code ||'-'|| i;
6841
6842 END IF;
6843
6844 /*
6845 * Fix for bug 5584658:
6846 *
6847 * Call the Federal API for payment instruction sequence
6848 * assignment. This API will detect if the installation is
6849 * a Federal install and return a sequence number (if possible).
6850 *
6851 * If the Federal API returns a sequence number assign this
6852 * value to the admin assigned ref field on the payment
6853 * instruction.
6854 *
6855 * If the Federal API returns null, then leave the present
6856 * value of the admin assigned ref on the pmt instruction
6857 * unchanged.
6858 */
6859 l_instr_seq_num := FV_FEDERAL_PAYMENT_FIELDS_PKG.
6860 get_pay_instr_seq_num(
6861 x_pmtInstrTab(i).org_id,
6862 x_pmtInstrTab(i).payment_reason_code
6863 );
6864
6865 print_debuginfo(l_module_name, 'For pmt instr id: '
6866 || x_pmtInstrTab(i).payment_instruction_id
6867 || ' with org id: '
6868 || x_pmtInstrTab(i).org_id
6869 || ' and payment reason code: '
6870 || x_pmtInstrTab(i).payment_reason_code
6871 || ', Federal Seq API returned seq #: '
6872 || l_instr_seq_num
6873 );
6874
6875 IF (l_instr_seq_num IS NOT NULL) THEN
6876 x_pmtInstrTab(i).pay_admin_assigned_ref_code := l_instr_seq_num;
6877 END IF;
6878
6879 END LOOP;
6880
6881 print_debuginfo(l_module_name, 'EXIT');
6882
6883 END initializeInstrs;
6884
6885
6886 /*--------------------------------------------------------------------
6887 | NAME:
6888 | print_debuginfo
6889 |
6890 | PURPOSE:
6891 | This procedure prints the debug message to the concurrent manager
6892 | log file.
6893 |
6894 | PARAMETERS:
6895 | IN
6896 | p_debug_text - The debug message to be printed
6897 |
6898 | OUT
6899 |
6900 |
6901 | RETURNS:
6902 |
6903 | NOTES:
6904 |
6905 *---------------------------------------------------------------------*/
6906 PROCEDURE print_debuginfo(
6907 p_module IN VARCHAR2,
6908 p_debug_text IN VARCHAR2,
6909 p_debug_level IN VARCHAR2 DEFAULT FND_LOG.LEVEL_STATEMENT
6910 )
6911 IS
6912 l_default_debug_level VARCHAR2(200) := FND_LOG.LEVEL_STATEMENT;
6913 BEGIN
6914
6915 /*
6916 * Set the debug level to the value passed in
6917 * (provided this value is not null).
6918 */
6919 IF (p_debug_level IS NOT NULL) THEN
6920 l_default_debug_level := p_debug_level;
6921 END IF;
6922
6923 /*
6924 * Write the debug message to the concurrent manager log file.
6925 */
6926 IF (l_default_debug_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6927 iby_build_utils_pkg.print_debuginfo(p_module, p_debug_text,
6928 p_debug_level);
6929 END IF;
6930
6931 END print_debuginfo;
6932
6933 END IBY_PAYINSTR_PUB;