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