[Home] [Help]
PACKAGE BODY: APPS.IBY_TRANSACTIONEFT_PKG
Source
1 PACKAGE BODY IBY_TRANSACTIONEFT_PKG AS
2 /*$Header: ibyteftb.pls 120.46.12020000.8 2013/03/26 11:03:45 gmamidip ship $*/
3
4 --
5 -- Declare global variables
6 --
7 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
8 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
9 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_TRANSACTIONEFT_PKG';
10
11 /*--------------------------------------------------------------------
12 | Name : createLogicalGroups
13 |
14 | Purpose : To create logical groups in Funds Capture Instruction (for SEPA)
15 |
16 | Parameters:
17 | IN:
18 | x_batches_tab -- Table of Funds Capture instruction
19 |
20 | OUT:
21 | N/A
22 |
23 |
24 |
25 *-----------------------------------------------------------------------*/
26 PROCEDURE createLogicalGroups(
27 x_batches_tab IN IBY_TRANSACTIONCC_PKG.batchAttrTabType
28 )
29 IS
30
31 select_clause VARCHAR(4000);
32 into_clause VARCHAR(4000);
33 from_clause VARCHAR(4000);
34 where_clause VARCHAR(4000);
35 order_clause VARCHAR(4000);
36
37 l_grouping_mode varchar2(40);
38 l_grp_cntr NUMBER;
39
40 l_mbatch_id IBY_BATCHES_ALL.mbatchid%TYPE;
41 l_logical_group_reference iby_trxn_summaries_all.logical_group_reference%TYPE;
42 l_module_name VARCHAR2(200) := G_PKG_NAME || '.createLogicalGroups';
43
44 prev_org_id iby_trxn_summaries_all.org_id%TYPE;
45 prev_legal_entity_id iby_trxn_summaries_all.legal_entity_id%TYPE;
46 prev_payeeinstrid iby_trxn_summaries_all.payeeinstrid%TYPE;
47 prev_currencynamecode iby_trxn_summaries_all.currencynamecode%TYPE;
48 prev_settledate iby_trxn_summaries_all.settledate%TYPE;
49 prev_category_purpose iby_trxn_summaries_all.category_purpose%TYPE;
50 prev_seq_type iby_trxn_summaries_all.seq_type%TYPE;
51 prev_service_level iby_trxn_summaries_all.service_level%TYPE;
52 prev_localinstr iby_trxn_summaries_all.localinstr%TYPE;
53 prev_bank_charge_bearer_code iby_trxn_summaries_all.bank_charge_bearer_code%TYPE;
54
55
56 TYPE type_trxnmid IS TABLE OF
57 IBY_TRXN_SUMMARIES_ALL.trxnmid%TYPE
58 INDEX BY BINARY_INTEGER;
59 t_trxnmid type_trxnmid;
60
61 TYPE type_org_id IS TABLE OF
62 IBY_TRXN_SUMMARIES_ALL.org_id%TYPE
63 INDEX BY BINARY_INTEGER;
64 t_org_id type_org_id;
65
66 TYPE type_legal_entity_id IS TABLE OF
67 IBY_TRXN_SUMMARIES_ALL.legal_entity_id%TYPE
68 INDEX BY BINARY_INTEGER;
69 t_legal_entity_id type_legal_entity_id;
70
71 TYPE type_payeeinstrid IS TABLE OF
72 IBY_TRXN_SUMMARIES_ALL.payeeinstrid%TYPE
73 INDEX BY BINARY_INTEGER;
74 t_payeeinstrid type_payeeinstrid;
75
76 TYPE type_currencynamecode IS TABLE OF
77 IBY_TRXN_SUMMARIES_ALL.currencynamecode%TYPE
78 INDEX BY BINARY_INTEGER;
79 t_currencynamecode type_currencynamecode;
80
81 TYPE type_settledate IS TABLE OF
82 IBY_TRXN_SUMMARIES_ALL.settledate%TYPE
83 INDEX BY BINARY_INTEGER;
84 t_settledate type_settledate;
85
86 TYPE type_category_purpose IS TABLE OF
87 IBY_TRXN_SUMMARIES_ALL.category_purpose%TYPE
88 INDEX BY BINARY_INTEGER;
89 t_category_purpose type_category_purpose;
90
91 TYPE type_seq_type IS TABLE OF
92 IBY_TRXN_SUMMARIES_ALL.seq_type%TYPE
93 INDEX BY BINARY_INTEGER;
94 t_seq_type type_seq_type;
95
96 TYPE type_service_level IS TABLE OF
97 IBY_TRXN_SUMMARIES_ALL.service_level%TYPE
98 INDEX BY BINARY_INTEGER;
99 t_service_level type_service_level;
100
101 TYPE type_localinstr IS TABLE OF
102 IBY_TRXN_SUMMARIES_ALL.localinstr%TYPE
103 INDEX BY BINARY_INTEGER;
104 t_localinstr type_localinstr;
105
106 TYPE type_bank_charge_bearer_code IS TABLE OF
107 IBY_TRXN_SUMMARIES_ALL.bank_charge_bearer_code%TYPE
108 INDEX BY BINARY_INTEGER;
109 t_bank_charge_bearer_code type_bank_charge_bearer_code;
110
111 TYPE type_logical_group_reference IS TABLE OF
112 IBY_TRXN_SUMMARIES_ALL.logical_group_reference%TYPE
113 INDEX BY BINARY_INTEGER;
114 t_logical_group_reference type_logical_group_reference;
115
116 BEGIN
117
118 print_debuginfo(l_module_name, 'ENTER');
119
120 /* Only 'MIXD' grouping mode is supported now */
121 l_grouping_mode := 'MIXD';
122
123 --14666403 - Null check on batches created to avoid pl/sql invalid numeric value error.
124 IF (x_batches_tab.COUNT = 0) THEN
125 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
126 print_debuginfo(l_module_name,'No batches were created.');
127 print_debuginfo(l_module_name, 'EXIT');
128 END IF;
129 RETURN;
130 ELSE
131 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
132 print_debuginfo(l_module_name, 'Number of batches created:' || x_batches_tab.COUNT);
133 END IF;
134 END IF;
135 FOR i in x_batches_tab.FIRST .. x_batches_tab.LAST LOOP
136
137 l_mbatch_id := x_batches_tab(i).mbatch_id;
138
139 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
140 print_debuginfo(l_module_name, 'Instruction: '
141 || l_mbatch_id || ', Grouping Mode: '
142 || l_grouping_mode);
143 END IF;
144
145 IF (l_grouping_mode IS NOT NULL) THEN
146 IF l_grouping_mode = 'MIXD' THEN
147
148 /* The previous values are made to hold '' so that the
149 * first transaction in a batch
150 * always has a new logical group id
151 */
152
153 prev_org_id :='';
154 prev_legal_entity_id :='';
155 prev_payeeinstrid :='';
156 prev_currencynamecode :='';
157 prev_settledate :='';
158 prev_category_purpose :='';
159 prev_seq_type :='';
160 prev_service_level :='';
161 prev_localinstr :='';
162 prev_bank_charge_bearer_code :='';
163
164 from_clause :=' FROM IBY_TRXN_SUMMARIES_ALL';
165 where_clause := ' WHERE mbatchid = ' || l_mbatch_id;
166
167 order_clause := ' ORDER BY ';
168 order_clause := order_clause || ' org_id , legal_entity_id, payeeinstrid, currencynamecode, settledate, ';
169 order_clause := order_clause || ' category_purpose , seq_type, service_level, localinstr, bank_charge_bearer_code ';
170
171 select_clause := 'SELECT TRXNMID
172 , org_id
173 , legal_entity_id
174 , payeeinstrid
175 , currencynamecode
176 , settledate
177 , category_purpose
178 , seq_type
179 , service_level
180 , localinstr
181 , bank_charge_bearer_code'
182 ;
183
184 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
185 print_debuginfo(l_module_name, 'select_clause: '
186 || select_clause);
187 print_debuginfo(l_module_name, 'from_clause: '
188 || from_clause);
189 print_debuginfo(l_module_name, 'where_clause: '
190 || where_clause);
191 print_debuginfo(l_module_name, 'order_clause: '
192 || order_clause);
193 END IF;
194
195
196 EXECUTE IMMEDIATE select_clause
197 || from_clause
198 || where_clause
199 || order_clause
200 BULK COLLECT INTO t_trxnmid
201 ,t_org_id
202 ,t_legal_entity_id
203 ,t_payeeinstrid
204 ,t_currencynamecode
205 ,t_settledate
206 ,t_category_purpose
207 ,t_seq_type
208 ,t_service_level
209 ,t_localinstr
210 ,t_bank_charge_bearer_code
211 ;
212
213 l_grp_cntr := 0;
214
215 FOR j in t_trxnmid.FIRST .. t_trxnmid.LAST
216 LOOP
217
218 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
219
220 print_debuginfo(l_module_name, 'current record: t_trxnmid'|| t_trxnmid(j) || ',t_org_id'
221 || t_org_id(j)|| ',t_legal_entity_id '|| t_legal_entity_id(j)
222 || ',t_payeeinstrid ' || t_payeeinstrid(j)
223 || ',t_currencynamecode ' || t_currencynamecode(j)
224 || ',t_settledate ' || t_settledate(j)
225 || ',t_category_purpose ' || t_category_purpose(j)
226 || ',t_seq_type ' || t_seq_type(j)
227 || ',t_service_level ' || t_service_level(j)
228 || ',t_localinstr ' || t_localinstr(j)
229 || ',t_bank_charge_bearer_code ' || t_bank_charge_bearer_code(j)
230 ) ;
231
232 print_debuginfo(l_module_name, 'previous record: prev_org_id '|| prev_org_id
233 || ',prev_legal_entity_id '|| prev_legal_entity_id
234 || ',prev_payeeinstrid ' || prev_payeeinstrid
235 || ',prev_currencynamecode ' || prev_currencynamecode
236 || ',prev_settledate ' || prev_settledate
237 || ',prev_category_purpose ' || prev_category_purpose
238 || ',prev_seq_type ' || prev_seq_type
239 || ',prev_service_level ' || prev_service_level
240 || ',prev_localinstr ' || prev_localinstr
241 || ',prev_bank_charge_bearer_code ' || prev_bank_charge_bearer_code
242 ) ;
243
244 END IF;
245
246 IF t_org_id(j) = prev_org_id
247 AND t_legal_entity_id(j) = prev_legal_entity_id
248 AND t_payeeinstrid(j) = prev_payeeinstrid
249 AND t_currencynamecode(j) = prev_currencynamecode
250 AND t_settledate(j) = prev_settledate
251 AND t_category_purpose(j) = prev_category_purpose
252 AND t_seq_type(j) = prev_seq_type
253 AND t_service_level(j) = prev_service_level
254 AND t_localinstr(j) = prev_localinstr
255 AND t_bank_charge_bearer_code(j) = prev_bank_charge_bearer_code
256
257 THEN
258 t_logical_group_reference(j) := l_logical_group_reference;
259 print_debuginfo(l_module_name, 'The prev and current trxns have same grouping attributes. trxnmid: '
260 || t_trxnmid(j) || ', logical_grp_ref: '
261 || t_logical_group_reference(j));
262
263 ELSE
264 prev_org_id := t_org_id(j);
265 prev_legal_entity_id := t_legal_entity_id(j);
266 prev_payeeinstrid := t_payeeinstrid(j);
267 prev_currencynamecode := t_currencynamecode(j);
268 prev_settledate := t_settledate(j);
269 prev_category_purpose := t_category_purpose(j);
270 prev_seq_type := t_seq_type(j);
271 prev_service_level := t_service_level(j);
272 prev_localinstr := t_localinstr(j);
273 prev_bank_charge_bearer_code := t_bank_charge_bearer_code(j);
274
275 l_grp_cntr := l_grp_cntr + 1;
276 l_logical_group_reference := l_mbatch_id ||'_'|| l_grp_cntr;
277 t_logical_group_reference(j) := l_logical_group_reference;
278 print_debuginfo(l_module_name, ' The prev and current trxn have diff grouping attributes. trxnmid: '
279 || t_trxnmid(j) || ', logical_grp_ref: '
280 || t_logical_group_reference(j));
281
282 END IF;
283 END LOOP;
284
285 FORALL j IN t_trxnmid.FIRST .. t_trxnmid.LAST
286 UPDATE IBY_TRXN_SUMMARIES_ALL
287 SET logical_group_reference = t_logical_group_reference(j)
288 WHERE trxnmid = t_trxnmid(j);
289
290 END IF;
291 END IF;
292 END LOOP;
293
294 END createLogicalGroups;
295
296 /*
297 * The purpose of this procedure is to check if there is any open
298 * transaction which are due. If there is, it will insert a row into
299 * the iby_batches_all table to keep track of the batch status, and
300 * change the transactions status to other status. So, the open
301 * transactions will be sent as part of future batch close. Also, it
302 * will not allow any modification and cancellation to these transactions.
303 */
304 PROCEDURE createBatchCloseTrxns(
305 merch_batchid_in IN VARCHAR2,
306 merchant_id_in IN VARCHAR2,
307 vendor_id_in IN NUMBER,
308 vendor_key_in IN VARCHAR2,
309 newstatus_in IN NUMBER,
310 oldstatus_in IN NUMBER,
311 batchstate_in IN NUMBER,
312 settlement_date_in IN DATE,
313 req_type_in IN VARCHAR2,
314 numtrxns_out OUT NOCOPY NUMBER
315 )
316 IS
317
318 numrows NUMBER;
319 l_mpayeeid iby_payee.mpayeeid%type;
320 l_mbatchid iby_batches_all.mbatchid%type;
321
322 BEGIN
323
324 SELECT
325 COUNT(*)
326 INTO
327 numtrxns_out
328 FROM
329 iby_trxn_summaries_all
330 WHERE
331 status = oldstatus_in AND
332 payeeid = merchant_id_in AND
333 bepid = vendor_id_in AND
334 bepkey = vendor_key_in AND
335 batchid IS NULL AND
336 trunc(settledate) <= trunc(settlement_date_in) AND
337 instrtype = 'BANKACCOUNT';
338
339 /*
340 * If there isn't any open transactions, then exit.
341 */
342 IF (numtrxns_out > 0) THEN
343
344 SELECT
345 iby_batches_s.NEXTVAL
346 INTO
347 l_mbatchid
348 FROM
349 DUAL;
350
351 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
352 -- Bug:8363526 : Inserting new column settledate VALUE:sysdate
353 INSERT INTO iby_batches_all
354 (MBATCHID,
355 BATCHID,
356 MPAYEEID,
357 PAYEEID,
358 BEPID,
359 BEPKEY,
360 BATCHSTATUS,
361 BATCHSTATEID,
362 BATCHCLOSEDATE,
363 REQTYPE,
364 REQDATE,
365 LAST_UPDATE_DATE,
366 LAST_UPDATED_BY,
367 CREATION_DATE,
368 CREATED_BY,
369 LAST_UPDATE_LOGIN,
370 OBJECT_VERSION_NUMBER,
371 SENTCOUNTER,
372 SENTCOUNTERDAILY
373 ,settledate )
374 VALUES
375 (
376 l_mbatchid,
377 merch_batchid_in,
378 l_mpayeeid,
379 merchant_id_in,
380 vendor_id_in,
381 vendor_key_in,
382 batchstate_in,
383 batchstate_in,
384 settlement_date_in,
385 req_type_in,
386 sysdate,
387 sysdate,
388 fnd_global.user_id,
389 sysdate,
390 fnd_global.user_id,
391 fnd_global.login_id,
392 0,
393 0,
394 0
395 ,sysdate);
396
397 UPDATE
398 IBY_TRXN_SUMMARIES_ALL
399 SET
400 status = newstatus_in,
401 batchid = merch_batchid_in,
402 mbatchid = l_mbatchid,
403 last_update_date = sysdate,
404 updatedate = sysdate,
405 last_updated_by = fnd_global.user_id,
406 object_version_number = object_version_number + 1
407 WHERE
408 status = oldstatus_in AND
409 payeeid = merchant_id_in AND
410 bepid = vendor_id_in AND
411 bepkey = vendor_key_in AND
412 batchid IS NULL AND
413 trunc(settledate) <= trunc(settlement_date_in) AND
414 instrtype = 'BANKACCOUNT'
415 ;
416
417 COMMIT;
418
419 END IF;
420
421 END createBatchCloseTrxns;
422
423 /*
424 * The purpose of this procedure is to check if there is any open
425 * transaction which are due. If there is, it will insert a row into
426 * the iby_batches_all table to keep track of the batch status, and
427 * change the transactions status to other status. So, the open
428 * transactions will be sent as part of future batch close. Also, it
429 * will not allow any modification and cancellation to these transactions.
430 */
431 PROCEDURE createBatchCloseTrxnsNew(
432 merch_batchid_in IN VARCHAR2,
433 profile_code_in IN iby_batches_all.
434 process_profile_code%TYPE,
435 merchant_id_in IN VARCHAR2,
436 vendor_id_in IN NUMBER,
437 vendor_key_in IN VARCHAR2,
438 newstatus_in IN NUMBER,
439 oldstatus_in IN NUMBER,
440 batchstate_in IN NUMBER,
441 settlement_date_in IN DATE,
442 req_type_in IN VARCHAR2,
443 instr_type_in IN iby_batches_all.
444 instrument_type%TYPE,
445 br_disputed_flag_in IN iby_batches_all.
446 br_disputed_flag%TYPE,
447 f_pmt_channel_in IN iby_trxn_summaries_all.
448 payment_channel_code%TYPE,
449 f_curr_in IN iby_trxn_summaries_all.
450 currencynamecode%TYPE,
451 f_settle_date IN iby_trxn_summaries_all.
452 settledate%TYPE,
453 f_due_date IN iby_trxn_summaries_all.
454 settlement_due_date%TYPE,
455 f_maturity_date IN iby_trxn_summaries_all.
456 br_maturity_date%TYPE,
457 f_instr_type IN iby_trxn_summaries_all.
458 instrtype%TYPE,
459 numtrxns_out OUT NOCOPY NUMBER,
460 mbatch_ids_out OUT NOCOPY JTF_NUMBER_TABLE,
461 batch_ids_out OUT NOCOPY JTF_VARCHAR2_TABLE_100
462 )
463 IS
464
465 numrows NUMBER;
466 l_mpayeeid iby_payee.mpayeeid%type;
467 l_mbatchid iby_batches_all.mbatchid%type;
468 l_module_name CONSTANT VARCHAR2(200) :=
469 G_PKG_NAME || '.createBatchCloseTrxnsNew';
470
471 l_batches_tab IBY_TRANSACTIONCC_PKG.batchAttrTabType;
472 l_trxns_in_batch_tab IBY_TRANSACTIONCC_PKG.trxnsInBatchTabType;
473
474 l_index NUMBER;
475
476 l_system_prof_code iby_fndcpt_sys_eft_pf_b.funds_xfer_format_code%type;
477
478 BEGIN
479
480 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
481 print_debuginfo(l_module_name, 'ENTER');
482
483 END IF;
484 mbatch_ids_out := JTF_NUMBER_TABLE();
485 batch_ids_out := JTF_VARCHAR2_TABLE_100();
486
487 /*
488 * BEP and vendor related params.
489 */
490 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
491 print_debuginfo(l_module_name, 'vendor_id_in: '
492 || vendor_id_in);
493 print_debuginfo(l_module_name, 'vendor_key_in: '
494 || vendor_key_in);
495 print_debuginfo(l_module_name, 'merchant_id_in: '
496 || merchant_id_in);
497 print_debuginfo(l_module_name, 'req_type_in: '
498 || req_type_in);
499 print_debuginfo(l_module_name, 'profile_code_in: '
500 || profile_code_in);
501 print_debuginfo(l_module_name, 'settlement_date_in: '
502 || settlement_date_in);
503 print_debuginfo(l_module_name, 'oldstatus_in: '
504 || oldstatus_in);
505
506 END IF;
507 SELECT
508 COUNT(*)
509 INTO
510 numtrxns_out
511 FROM
512 iby_trxn_summaries_all
513 WHERE
514 status = oldstatus_in AND
515 payeeid = merchant_id_in AND
516 bepid = vendor_id_in AND
517 bepkey = vendor_key_in AND
518 batchid IS NULL AND
519 trunc(nvl(settledate, sysdate)) <= trunc(nvl(settlement_date_in, sysdate-1)) AND
520 instrtype = 'BANKACCOUNT';
521
522 /*
523 * If there isn't any open transactions, then exit.
524 */
525 IF (numtrxns_out > 0) THEN
526
527 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
528 print_debuginfo(l_module_name, 'Invoking grouping ..');
529
530 END IF;
531 /*
532 * Group all the transactions for this profile into
533 * batches as per the grouping attributes on the profile.
534 */
535 IBY_TRANSACTIONCC_PKG.performTransactionGrouping(
536 instr_type_in,
537 req_type_in,
538 f_pmt_channel_in,
539 f_curr_in,
540 f_settle_date,
541 f_due_date,
542 f_maturity_date,
543 f_instr_type,
544 merch_batchid_in,
545 l_batches_tab,
546 l_trxns_in_batch_tab
547 );
548
549 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
550 print_debuginfo(l_module_name, '# batches created: '
551 || l_batches_tab.COUNT);
552
553 print_debuginfo(l_module_name, '# transactions processed: '
554 || l_trxns_in_batch_tab.COUNT);
555
556 END IF;
557 /*
558 * After grouping it is possible that multiple batches were
559 * created. Each batch will be a separate row in the
560 * IBY_BATCHES_ALL table with a unique mbatchid.
561 *
562 * The user may have provided a batch id (batch prefix), we will
563 * have to assign that batch id to each of the created batches.
564 *
565 * This batch id would be sent to the payment system. It therefore
566 * has to be unique. Therefore, we add a suffix to the user
567 * provided batch id to ensure that batches created after grouping
568 * have a unique batch id.
569 */
570 IF (l_batches_tab.COUNT > 0) THEN
571
572 l_index := 1;
573 FOR k IN l_batches_tab.FIRST .. l_batches_tab.LAST LOOP
574
575 /*
576 * Assign a unique batch id to each batch.
577 */
578 l_batches_tab(k).batch_id :=
579 merch_batchid_in ||'_'|| l_index;
580 l_index := l_index + 1;
581
582 END LOOP;
583
584 END IF;
585
586 /* Perform the logical grouping of transactions for SEPA
587 Chk the format based on the profile code
588 If it contains SEPA, then create the logical grouping.
589 */
590
591 SELECT sp.funds_xfer_format_code
592 INTO l_system_prof_code
593 FROM iby_fndcpt_user_eft_pf_b up
594 ,iby_fndcpt_sys_eft_pf_b sp
595 WHERE up.sys_eft_profile_code = sp.sys_eft_profile_code
596 AND up.user_eft_profile_code = profile_code_in;
597
598 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
599 print_debuginfo(l_module_name, 'Fetched Format ' || l_system_prof_code );
600 END IF;
601
602 IF (instr( l_system_prof_code, 'SEPA') >0)
603 THEN
604 createLogicalGroups(l_batches_tab);
605 END IF;
606
607 /* Logical grouping of transactions for SEPA - END */
608
609 /*
610 * After grouping, the transactions will be assigned a mbatch id.
611 * Assign them a batch id as well (based on the batch id
612 * corresponding to each mbatch id).
613 */
614 IF (l_trxns_in_batch_tab.COUNT > 0) THEN
615
616 FOR m IN l_trxns_in_batch_tab.FIRST ..
617 l_trxns_in_batch_tab.LAST LOOP
618
619 FOR k IN l_batches_tab.FIRST .. l_batches_tab.LAST LOOP
620
621 /*
622 * Find the mbatch id in the batches array
623 * corresponding to the mbatchid of this transaction.
624 */
625 IF (l_trxns_in_batch_tab(m).mbatch_id =
626 l_batches_tab(k).mbatch_id) THEN
627
628 /*
629 * Assign the batch id from the batches array
630 * to this transaction.
631 */
632 l_trxns_in_batch_tab(m).batch_id :=
633 l_batches_tab(k).batch_id;
634
635 END IF;
636
637 END LOOP;
638
639 END LOOP;
640
641 END IF;
642
643
644 /*
645
646 SELECT
647 iby_batches_s.NEXTVAL
648 INTO
649 l_mbatchid
650 FROM
651 DUAL;
652 */
653
654 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
655
656 IF (l_batches_tab.COUNT <> 0) THEN
657
658 FOR i IN l_batches_tab.FIRST .. l_batches_tab.LAST LOOP
659
660 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
661 print_debuginfo(l_module_name, 'Going to insert batch '
662 || l_batches_tab(i).mbatch_id);
663
664 END IF;
665 INSERT INTO iby_batches_all
666 (MBATCHID,
667 BATCHID,
668 MPAYEEID,
669 PAYEEID,
670 BEPID,
671 BEPKEY,
672 BATCHSTATUS,
673 BATCHSTATEID,
674 BATCHCLOSEDATE,
675 REQTYPE,
676 REQDATE,
677 LAST_UPDATE_DATE,
678 LAST_UPDATED_BY,
679 CREATION_DATE,
680 CREATED_BY,
681 LAST_UPDATE_LOGIN,
682 OBJECT_VERSION_NUMBER,
683 SENTCOUNTER,
684 SENTCOUNTERDAILY,
685 PROCESS_PROFILE_CODE,
686 INSTRUMENT_TYPE,
687 BR_DISPUTED_FLAG,
688 CURRENCYNAMECODE,
689 PAYEEINSTRID,
690 LEGAL_ENTITY_ID,
691 ORG_ID,
692 ORG_TYPE,
693 SETTLEDATE
694 )
695 VALUES
696 (
697 l_batches_tab(i).mbatch_id,
698 merch_batchid_in || '_' || i,
699 l_mpayeeid,
700 merchant_id_in,
701 vendor_id_in,
702 l_batches_tab(i).bep_key,
703 batchstate_in,
704 batchstate_in,
705 settlement_date_in,
706 req_type_in,
707 sysdate,
708 sysdate,
709 fnd_global.user_id,
710 sysdate,
711 fnd_global.user_id,
712 fnd_global.login_id,
713 0,
714 0,
715 0,
716 l_batches_tab(i).profile_code,
717 instr_type_in,
718 br_disputed_flag_in,
719
720 /*
721 * Fix for bug 5614670:
722 *
723 * Populate the batch related attributes
724 * created after grouping in this
725 * insert.
726 */
727 l_batches_tab(i).curr_code,
728 l_batches_tab(i).int_bank_acct_id,
729 l_batches_tab(i).le_id,
730 l_batches_tab(i).org_id,
731 l_batches_tab(i).org_type,
732 l_batches_tab(i).settle_date
733 );
734
735
736 /* Bug 14794152: ACTIVE DEBIT AUTHORIZATION VALIDATION DONE FOR NON-SEPA SETTLEMENT BATCHES
737 sgogula - Format level validations are to be done only for SEPA as of now.
738 In future if more than one format supports validations then
739 validate_open_batch should be modified accordingly to fetch the
740 format level validations.
741 */
742 --13949511-Reverting 1794152: Validations to be fired based on format irrespective of SEPA
743
744
745 --IF (instr( l_system_prof_code, 'SEPA') >0)
746 -- THEN
747 print_debuginfo(l_module_name, 'validate_open_batch '
748 || vendor_id_in );
749
750 print_debuginfo(l_module_name, 'validate_open_batch '
751 || l_batches_tab(i).mbatch_id);
752
753 validate_open_batch(
754 vendor_id_in,
755 l_batches_tab(i).mbatch_id,l_system_prof_code);
756
757 -- END IF;
758
759 /* Bug 14794152: End of Changes */
760
761
762 /*
763 * Store the created mbatchids in the output param
764 * to return to the caller.
765 */
766 mbatch_ids_out.EXTEND;
767 mbatch_ids_out(i) := l_batches_tab(i).mbatch_id;
768
769 /*
770 * Store the created batchids in the output param
771 * to return to the caller.
772 */
773 batch_ids_out.EXTEND;
774 batch_ids_out(i) := l_batches_tab(i).batch_id;
775
776 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
777 print_debuginfo(l_module_name, 'Finished insert batch '
778 || l_batches_tab(i).mbatch_id);
779
780 END IF;
781 END LOOP;
782
783 END IF; -- if l_batches_tab.COUNT <> 0
784
785 IF (l_trxns_in_batch_tab.COUNT <> 0) THEN
786
787 FOR i IN l_trxns_in_batch_tab.FIRST .. l_trxns_in_batch_tab.LAST LOOP
788
789 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
790 print_debuginfo(l_module_name, 'Going to update transaction '
791 || l_trxns_in_batch_tab(i).trxn_id);
792
793 END IF;
794 UPDATE
795 IBY_TRXN_SUMMARIES_ALL
796 SET
797 status = newstatus_in,
798 batchid = l_trxns_in_batch_tab(i).batch_id,
799 mbatchid = l_trxns_in_batch_tab(i).mbatch_id,
800 last_update_date = sysdate,
801 updatedate = sysdate,
802 last_updated_by = fnd_global.user_id,
803 object_version_number = object_version_number + 1
804 WHERE
805 transactionid = l_trxns_in_batch_tab(i).trxn_id AND
806 status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED
807 ;
808
809 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
810 print_debuginfo(l_module_name, 'Finished updating transaction'
811 || l_trxns_in_batch_tab(i).trxn_id);
812
813 END IF;
814 END LOOP;
815
816 END IF; -- if l_trxns_in_batch_tab.COUNT <> 0
817
818 COMMIT;
819
820 ELSE
821
822 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
823 print_debuginfo(l_module_name, 'No open transactions; Exiting ..');
824
825 END IF;
826 END IF;
827
828 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
829 print_debuginfo(l_module_name, 'mbatchids count: '
830 || mbatch_ids_out.COUNT);
831
832 print_debuginfo(l_module_name, 'EXIT');
833
834 END IF;
835 END createBatchCloseTrxnsNew;
836
837 /*
838 * This is the overloaded form of the previous API. This takes an array of
839 * profile codes as input parameter (instead of a single one)
840 * The purpose of this procedure is to check if there is any open
841 * transaction which are due. If there is, it will insert a row into
842 * the iby_batches_all table to keep track of the batch status, and
843 * change the transactions status to other status. So, the open
844 * transactions will be sent as part of future batch close. Also, it
845 * will not allow any modification and cancellation to these transactions.
846 */
847 PROCEDURE createBatchCloseTrxnsNew(
848 merch_batchid_in IN VARCHAR2,
849 profile_code_array IN JTF_VARCHAR2_TABLE_100,
850 merchant_id_in IN VARCHAR2,
851 vendor_id_in IN NUMBER,
852 vendor_key_in IN VARCHAR2,
853 newstatus_in IN NUMBER,
854 oldstatus_in IN NUMBER,
855 batchstate_in IN NUMBER,
856 settlement_date_in IN DATE,
857 req_type_in IN VARCHAR2,
858 instr_type_in IN iby_batches_all.
859 instrument_type%TYPE,
860 br_disputed_flag_in IN iby_batches_all.
861 br_disputed_flag%TYPE,
862 f_pmt_channel_in IN iby_trxn_summaries_all.
863 payment_channel_code%TYPE,
864 f_curr_in IN iby_trxn_summaries_all.
865 currencynamecode%TYPE,
866 f_settle_date IN iby_trxn_summaries_all.
867 settledate%TYPE,
868 f_due_date IN iby_trxn_summaries_all.
869 settlement_due_date%TYPE,
870 f_maturity_date IN iby_trxn_summaries_all.
871 br_maturity_date%TYPE,
872 f_instr_type IN iby_trxn_summaries_all.
873 instrtype%TYPE,
874 numtrxns_out OUT NOCOPY NUMBER,
875 mbatch_ids_out OUT NOCOPY JTF_NUMBER_TABLE,
876 batch_ids_out OUT NOCOPY JTF_VARCHAR2_TABLE_100
877 )
878 IS
879
880 numrows NUMBER;
881 l_mpayeeid iby_payee.mpayeeid%type;
882 l_mbatchid iby_batches_all.mbatchid%type;
883 l_module_name CONSTANT VARCHAR2(200) :=
884 G_PKG_NAME || '.createBatchCloseTrxnsNew2';
885
886 l_batches_tab IBY_TRANSACTIONCC_PKG.batchAttrTabType;
887 l_trxns_in_batch_tab IBY_TRANSACTIONCC_PKG.trxnsInBatchTabType;
888 numProfCodes NUMBER;
889 strProfCodes VARCHAR2(200);
890 l_cursor_stmt VARCHAR2(1000);
891 TYPE dyn_transactions IS REF CURSOR;
892 l_trxn_cursor dyn_transactions;
893
894 l_index NUMBER;
895 l_system_prof_code iby_fndcpt_sys_eft_pf_b.funds_xfer_format_code%type;
896
897 BEGIN
898
899 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
900 print_debuginfo(l_module_name, 'ENTER');
901
902 END IF;
903 /* Form a comma separated string for the bepkeys */
904 numProfCodes := profile_code_array.count;
905 FOR i IN 1..(numProfCodes-1) LOOP
906 strProfCodes := strProfCodes||''''||profile_code_array(i)||''',';
907 END LOOP;
908 /* Append the last profile code without comma at the end */
909 strProfCodes := strProfCodes||''''||profile_code_array(numProfCodes)||'''';
910
911 mbatch_ids_out := JTF_NUMBER_TABLE();
912 batch_ids_out := JTF_VARCHAR2_TABLE_100();
913
914 /*
915 * BEP and vendor related params.
916 */
917 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
918 print_debuginfo(l_module_name, 'vendor_id_in: '
919 || vendor_id_in);
920 print_debuginfo(l_module_name, 'vendor_key_in: '
921 || vendor_key_in);
922 print_debuginfo(l_module_name, 'merchant_id_in: '
923 || merchant_id_in);
924 print_debuginfo(l_module_name, 'req_type_in: '
925 || req_type_in);
926 print_debuginfo(l_module_name, 'profile codes (as comma separated string): '
927 || strProfCodes);
928 print_debuginfo(l_module_name, 'settlement_date_in: '
929 || settlement_date_in);
930 print_debuginfo(l_module_name, 'oldstatus_in: '
931 || oldstatus_in);
932 END IF;
933 /*
934 * We won't be using this cursor. Instead we will be using the reference
935 * cursor written below. The cursor fetches the transactions based on the
936 * profile codes rather than bepkey.
937 */
938
939 /* SELECT
940 COUNT(*)
941 INTO
942 numtrxns_out
943 FROM
944 iby_trxn_summaries_all
945 WHERE
946 status = oldstatus_in AND
947 payeeid = merchant_id_in AND
948 bepid = vendor_id_in AND
949 bepkey = vendor_key_in AND
950 batchid IS NULL AND
951 trunc(nvl(settledate, sysdate)) <= trunc(nvl(settlement_date_in, sysdate-1)) AND
952 instrtype = 'BANKACCOUNT';
953 */
954
955 l_cursor_stmt := ' SELECT COUNT(*) FROM '||
956 ' iby_trxn_summaries_all WHERE '||
957 ' status = '||oldstatus_in||' AND '||
958 ' payeeid = '''||merchant_id_in||''' AND '||
959 ' bepid = '||vendor_id_in||' AND '||
960 ' process_profile_code IN ('||strProfCodes||') AND '||
961 ' batchid IS NULL AND '||
962 -- bug 8238335
963 -- ' trunc(nvl(settledate, sysdate)) <= trunc(nvl(to_date('''||settlement_date_in||'''), sysdate-1)) AND '||
964 ' instrtype = ''BANKACCOUNT'' '
965 ;
966
967 OPEN l_trxn_cursor FOR l_cursor_stmt;
968 FETCH l_trxn_cursor INTO numtrxns_out;
969 CLOSE l_trxn_cursor;
970 /*
971 * If there isn't any open transactions, then exit.
972 */
973 IF (numtrxns_out > 0) THEN
974
975 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
976 print_debuginfo(l_module_name, 'Invoking grouping ..');
977
978 END IF;
979 /*
980 * Group all the transactions for this profile into
981 * batches as per the grouping attributes on the profile.
982 */
983 IBY_TRANSACTIONCC_PKG.performTransactionGrouping(
984 profile_code_array,
985 instr_type_in,
986 req_type_in,
987 f_pmt_channel_in,
988 f_curr_in,
989 f_settle_date,
990 f_due_date,
991 f_maturity_date,
992 f_instr_type,
993 merch_batchid_in,
994 l_batches_tab,
995 l_trxns_in_batch_tab
996 );
997
998 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
999 print_debuginfo(l_module_name, '# batches created: '
1000 || l_batches_tab.COUNT);
1001
1002 print_debuginfo(l_module_name, '# transactions processed: '
1003 || l_trxns_in_batch_tab.COUNT);
1004
1005 END IF;
1006 /*
1007 * After grouping it is possible that multiple batches were
1008 * created. Each batch will be a separate row in the
1009 * IBY_BATCHES_ALL table with a unique mbatchid.
1010 *
1011 * The user may have provided a batch id (batch prefix), we will
1012 * have to assign that batch id to each of the created batches.
1013 *
1014 * This batch id would be sent to the payment system. It therefore
1015 * has to be unique. Therefore, we add a suffix to the user
1016 * provided batch id to ensure that batches created after grouping
1017 * have a unique batch id.
1018 */
1019 IF (l_batches_tab.COUNT > 0) THEN
1020
1021 l_index := 1;
1022 FOR k IN l_batches_tab.FIRST .. l_batches_tab.LAST LOOP
1023
1024 /*
1025 * Assign a unique batch id to each batch.
1026 */
1027 l_batches_tab(k).batch_id :=
1028 merch_batchid_in ||'_'|| l_index;
1029 l_index := l_index + 1;
1030
1031 END LOOP;
1032
1033 END IF;
1034
1035 /* Perform the logical grouping of transactions for SEPA
1036 Chk the format based on the profile code
1037 If it contains SEPA, then create the logical grouping.
1038 */
1039
1040 SELECT sp.funds_xfer_format_code
1041 INTO l_system_prof_code
1042 FROM iby_fndcpt_user_eft_pf_b up
1043 ,iby_fndcpt_sys_eft_pf_b sp
1044 WHERE up.sys_eft_profile_code = sp.sys_eft_profile_code
1045 AND up.user_eft_profile_code = profile_code_array(1);
1046
1047 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1048 print_debuginfo(l_module_name, 'Fetched Format ' || l_system_prof_code );
1049 END IF;
1050
1051 IF (instr( l_system_prof_code, 'SEPA') >0)
1052 THEN
1053 createLogicalGroups(l_batches_tab);
1054 END IF;
1055
1056 /* Logical grouping of transactions for SEPA - END */
1057
1058
1059 /*
1060 * After grouping, the transactions will be assigned a mbatch id.
1061 * Assign them a batch id as well (based on the batch id
1062 * corresponding to each mbatch id).
1063 */
1064 IF (l_trxns_in_batch_tab.COUNT > 0) THEN
1065
1066 FOR m IN l_trxns_in_batch_tab.FIRST ..
1067 l_trxns_in_batch_tab.LAST LOOP
1068
1069 FOR k IN l_batches_tab.FIRST .. l_batches_tab.LAST LOOP
1070
1071 /*
1072 * Find the mbatch id in the batches array
1073 * corresponding to the mbatchid of this transaction.
1074 */
1075 IF (l_trxns_in_batch_tab(m).mbatch_id =
1076 l_batches_tab(k).mbatch_id) THEN
1077
1078 /*
1079 * Assign the batch id from the batches array
1080 * to this transaction.
1081 */
1082 l_trxns_in_batch_tab(m).batch_id :=
1083 l_batches_tab(k).batch_id;
1084 l_batches_tab(k).trxn_count :=
1085 nvl(l_batches_tab(k).trxn_count,0) + 1;
1086
1087 print_debuginfo(l_module_name, 'Assigned trxn count to batches rec '
1088 || l_batches_tab(k).trxn_count );
1089
1090 END IF;
1091
1092 END LOOP;
1093
1094 END LOOP;
1095
1096 END IF;
1097
1098
1099 /*
1100
1101 SELECT
1102 iby_batches_s.NEXTVAL
1103 INTO
1104 l_mbatchid
1105 FROM
1106 DUAL;
1107 */
1108
1109 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
1110
1111 IF (l_batches_tab.COUNT <> 0) THEN
1112
1113 FOR i IN l_batches_tab.FIRST .. l_batches_tab.LAST LOOP
1114
1115 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1116 print_debuginfo(l_module_name, 'Going to insert batch '
1117 || l_batches_tab(i).mbatch_id);
1118 END IF;
1119 /*
1120 * Modified to insert null values for bepkey, currency and
1121 * profile code columns since these could have multiple values
1122 * for a batch.
1123 */
1124
1125 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1126 print_debuginfo(l_module_name, 'l_batches_tab(i).trxn_count '
1127 || l_batches_tab(i).trxn_count );
1128 END IF;
1129
1130 INSERT INTO iby_batches_all
1131 (MBATCHID,
1132 BATCHID,
1133 MPAYEEID,
1134 PAYEEID,
1135 BEPID,
1136 BEPKEY,
1137 BATCHSTATUS,
1138 BATCHSTATEID,
1139 BATCHCLOSEDATE,
1140 REQTYPE,
1141 REQDATE,
1142 LAST_UPDATE_DATE,
1143 LAST_UPDATED_BY,
1144 CREATION_DATE,
1145 CREATED_BY,
1146 LAST_UPDATE_LOGIN,
1147 OBJECT_VERSION_NUMBER,
1148 SENTCOUNTER,
1149 SENTCOUNTERDAILY,
1150 PROCESS_PROFILE_CODE,
1151 INSTRUMENT_TYPE,
1152 BR_DISPUTED_FLAG,
1153 CURRENCYNAMECODE,
1154 PAYEEINSTRID,
1155 LEGAL_ENTITY_ID,
1156 ORG_ID,
1157 ORG_TYPE,
1158 SETTLEDATE,
1159 NUMTRXNS
1160 )
1161 VALUES
1162 (
1163 l_batches_tab(i).mbatch_id,
1164 merch_batchid_in || '_' || i,
1165 l_mpayeeid,
1166 merchant_id_in,
1167 vendor_id_in,
1168 null, -- l_batches_tab(i).bep_key
1169 batchstate_in,
1170 batchstate_in,
1171 settlement_date_in,
1172 req_type_in,
1173 sysdate,
1174 sysdate,
1175 fnd_global.user_id,
1176 sysdate,
1177 fnd_global.user_id,
1178 fnd_global.login_id,
1179 0,
1180 0,
1181 0,
1182 --l_batches_tab(i).profile_code
1183 profile_code_array(1),
1184 instr_type_in,
1185 br_disputed_flag_in,
1186
1187 /*
1188 * Fix for bug 5614670:
1189 *
1190 * Populate the batch related attributes
1191 * created after grouping in this
1192 * insert.
1193 */
1194 l_batches_tab(i).curr_code,
1195 l_batches_tab(i).int_bank_acct_id,
1196 l_batches_tab(i).le_id,
1197 l_batches_tab(i).org_id,
1198 l_batches_tab(i).org_type,
1199 l_batches_tab(i).settle_date,
1200 l_batches_tab(i).trxn_count
1201 );
1202
1203 /* Bug 14794152: ACTIVE DEBIT AUTHORIZATION VALIDATION DONE FOR NON-SEPA SETTLEMENT BATCHES
1204 sgogula - Format level validations are to be done only for SEPA as of now.
1205 In future if more than one format supports validations then
1206 validate_open_batch should be modified accordingly to fetch the
1207 format level validations.
1208 */
1209 /*13949511-Reverting 1794152: Validations to be fired based on format irrespective of SEPA*/
1210
1211 -- IF (instr( l_system_prof_code, 'SEPA') >0)
1212 -- THEN
1213 print_debuginfo(l_module_name, 'validate_open_batch '
1214 || vendor_id_in );
1215
1216 print_debuginfo(l_module_name, 'validate_open_batch '
1217 || l_batches_tab(i).mbatch_id);
1218
1219 validate_open_batch(
1220 vendor_id_in,
1221 l_batches_tab(i).mbatch_id,l_system_prof_code);
1222 -- END IF;
1223
1224 /* Bug 14794152: End of Changes */
1225
1226
1227 /*
1228 * Store the created mbatchids in the output param
1229 * to return to the caller.
1230 */
1231 mbatch_ids_out.EXTEND;
1232 mbatch_ids_out(i) := l_batches_tab(i).mbatch_id;
1233
1234 /*
1235 * Store the created batchids in the output param
1236 * to return to the caller.
1237 */
1238 batch_ids_out.EXTEND;
1239 batch_ids_out(i) := l_batches_tab(i).batch_id;
1240
1241 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1242 print_debuginfo(l_module_name, 'Finished insert batch '
1243 || l_batches_tab(i).mbatch_id);
1244
1245 END IF;
1246 END LOOP;
1247
1248 END IF; -- if l_batches_tab.COUNT <> 0
1249
1250 IF (l_trxns_in_batch_tab.COUNT <> 0) THEN
1251
1252 FOR i IN l_trxns_in_batch_tab.FIRST .. l_trxns_in_batch_tab.LAST LOOP
1253
1254 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1255 print_debuginfo(l_module_name, 'Going to update transaction '
1256 || l_trxns_in_batch_tab(i).trxn_id);
1257
1258 END IF;
1259 UPDATE
1260 IBY_TRXN_SUMMARIES_ALL
1261 SET
1262 status = newstatus_in,
1263 batchid = l_trxns_in_batch_tab(i).batch_id,
1264 mbatchid = l_trxns_in_batch_tab(i).mbatch_id,
1265 last_update_date = sysdate,
1266 updatedate = sysdate,
1267 last_updated_by = fnd_global.user_id,
1268 object_version_number = object_version_number + 1
1269 WHERE
1270 transactionid = l_trxns_in_batch_tab(i).trxn_id AND
1271 status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED
1272 ;
1273
1274 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1275 print_debuginfo(l_module_name, 'Finished updating transaction'
1276 || l_trxns_in_batch_tab(i).trxn_id);
1277
1278 END IF;
1279 END LOOP;
1280
1281 END IF; -- if l_trxns_in_batch_tab.COUNT <> 0
1282
1283 COMMIT;
1284
1285 ELSE
1286
1287 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1288 print_debuginfo(l_module_name, 'No open transactions; Exiting ..');
1289
1290 END IF;
1291 END IF;
1292
1293 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1294 print_debuginfo(l_module_name, 'mbatchids count: '
1295 || mbatch_ids_out.COUNT);
1296
1297 print_debuginfo(l_module_name, 'EXIT');
1298
1299 END IF;
1300 END createBatchCloseTrxnsNew;
1301
1302
1303 /*Update the batch and transactions status and other infomations based on the
1304 payeeid and batchid */
1305 PROCEDURE updateBatchCloseTrxns(
1306 merch_batchid_in IN VARCHAR2,
1307 merchant_id_in IN VARCHAR2,
1308 newstatus_in IN NUMBER,
1309 batchstate_in IN NUMBER,
1310 numtrxns_in IN NUMBER,
1311 batchtotal_in IN NUMBER DEFAULT null,
1312 salestotal_in IN NUMBER DEFAULT null,
1313 credittotal_in IN NUMBER DEFAULT null,
1314 time_in IN DATE,
1315 vendor_code_in IN VARCHAR2,
1316 vendor_message_in IN VARCHAR2
1317 )
1318 IS
1319
1320 BEGIN
1321 -- reset the OBJECT VERSION NUMBER, since we not using it
1322 -- the purpose of this, it is to keep track the EFT batchseq number
1323
1324 UPDATE iby_batches_all SET
1325 SENTCOUNTERDAILY = 0
1326 WHERE batchid = merch_batchid_in
1327 AND payeeid = merchant_id_in
1328 AND trunc(LAST_UPDATE_DATE) < trunc(sysdate);
1329
1330
1331 UPDATE iby_batches_all SET
1332 BATCHSTATUS = batchstate_in,
1333 BATCHSTATEID = batchstate_in,
1334 NUMTRXNS = numtrxns_in,
1335
1336 --
1337 -- only change these values if the incoming values are
1338 -- non-trivial
1339 --
1340 BATCHTOTAL = DECODE(NVL(batchtotal_in,''),'',batchtotal,batchtotal_in),
1341 BATCHSALES = DECODE(NVL(salestotal_in,''),'',batchsales,salestotal_in),
1342 BATCHCREDIT = DECODE(NVL(credittotal_in,''),'',batchcredit,credittotal_in),
1343
1344 BATCHCLOSEDATE = time_in,
1345 BEPCODE = vendor_code_in,
1346 BEPMESSAGE = vendor_message_in,
1347 LAST_UPDATE_DATE = sysdate,
1348 LAST_UPDATED_BY = fnd_global.user_id,
1349 OBJECT_VERSION_NUMBER = Object_Version_Number + 1,
1350 SENTCOUNTER = SENTCOUNTER + 1,
1351 SENTCOUNTERDAILY = SENTCOUNTERDAILY + 1
1352 WHERE batchid = merch_batchid_in
1353 AND payeeid = merchant_id_in;
1354
1355 UPDATE iby_trxn_summaries_all
1356 SET
1357 STATUS = newstatus_in,
1358 BEPCODE = vendor_code_in,
1359 BEPMESSAGE = vendor_message_in,
1360 LAST_UPDATE_DATE = sysdate,
1361 UPDATEDATE = sysdate,
1362 LAST_UPDATED_BY = fnd_global.user_id,
1363 OBJECT_VERSION_NUMBER = object_version_number + 1
1364 WHERE
1365 -- 109 means STATUS_BATCH_TRANSITIONAL, 101 means STATUS_BATCH_COMM_ERROR, 120 means STATUS_BATCH_MAX_EXCEEDED
1366 status in (109, 101, 120)
1367 AND
1368 batchid = merch_batchid_in
1369 AND
1370 payeeid = merchant_id_in
1371 AND
1372 instrtype = 'BANKACCOUNT';
1373
1374 COMMIT;
1375
1376 END updateBatchCloseTrxns;
1377
1378
1379 /*Update the transactions status and other informations by passed the data in as array.*/
1380 procedure updateTrxnResultStatus(i_merch_batchid IN VARCHAR2,
1381 i_merchant_id IN VARCHAR2,
1382 i_status_arr IN JTF_NUMBER_TABLE,
1383 i_errCode_arr IN JTF_VARCHAR2_TABLE_100,
1384 i_errMsg_arr IN JTF_VARCHAR2_TABLE_300,
1385 i_tangibleId_arr IN JTF_VARCHAR2_TABLE_100,
1386 o_status_arr OUT NOCOPY JTF_NUMBER_TABLE,
1387 o_error_code OUT NOCOPY NUMBER,
1388 o_error_msg OUT NOCOPY VARCHAR2
1389 )
1390
1391 IS
1392
1393 l_index INTEGER;
1394 c_FAIL NUMBER := -1;
1395 c_SUCCESS NUMBER := 0;
1396
1397 BEGIN
1398
1399 o_status_arr := JTF_NUMBER_TABLE();
1400 o_status_arr.extend( i_tangibleId_arr.count );
1401
1402 o_error_code := 0;
1403
1404 l_index := i_tangibleId_arr.first;
1405
1406 WHILE (TRUE) LOOP
1407
1408 o_status_arr( l_index ) := c_SUCCESS;
1409
1410 BEGIN -- Nested block begins
1411
1412 UPDATE iby_trxn_summaries_all
1413 SET STATUS = i_status_arr( l_index ),
1414 BEPCODE = i_errCode_arr( l_index ),
1415 BEPMESSAGE = i_errMsg_arr( l_index ),
1416 LAST_UPDATE_DATE = sysdate,
1417 UPDATEDATE = sysdate,
1418 LAST_UPDATED_BY = fnd_global.user_id
1419 WHERE TANGIBLEID = i_tangibleId_arr( l_index )
1420 AND batchid = i_merch_batchid
1421 AND payeeid = i_merchant_id;
1422
1423 IF ( SQL%NOTFOUND ) THEN
1424 o_status_arr( l_index ) := c_FAIL;
1425 ROLLBACK;
1426 ELSE
1427 COMMIT;
1428 END IF;
1429
1430 EXCEPTION
1431 WHEN OTHERS THEN
1432 o_status_arr( l_index ) := c_FAIL;
1433 o_error_code := SQLCODE;
1434 o_error_msg := SUBSTR(SQLERRM, 1, 200);
1435
1436 END; -- Nested block ends
1437
1438 EXIT WHEN ( i_tangibleId_arr.last = l_index );
1439
1440 l_index := i_tangibleId_arr.next( l_index );
1441
1442 END LOOP; --end of while loop
1443
1444 END updateTrxnResultStatus;
1445
1446 PROCEDURE insertEFTBatchTrxns(
1447 i_ecappid IN iby_trxn_summaries_all.ecappid%TYPE,
1448 i_payeeid IN iby_trxn_summaries_all.payeeid%TYPE,
1449 i_ecbatchid IN iby_trxn_summaries_all.ecbatchid%TYPE,
1450 i_bepid IN iby_trxn_summaries_all.bepid%TYPE,
1451 i_bepkey IN iby_trxn_summaries_all.bepkey%TYPE,
1452 i_pmtmethod IN iby_trxn_summaries_all.paymentmethodname%TYPE,
1453 i_reqtype IN iby_trxn_summaries_all.reqtype%TYPE,
1454 i_reqdate IN iby_trxn_summaries_all.reqdate%TYPE,
1455 i_payeeinstrid IN iby_trxn_summaries_all.payeeinstrid%TYPE,
1456 i_orgid IN iby_trxn_summaries_all.org_id%TYPE,
1457
1458 i_payerinstrid IN JTF_NUMBER_TABLE,
1459 i_amount IN JTF_NUMBER_TABLE,
1460 i_payerid IN JTF_VARCHAR2_TABLE_100,
1461 i_tangibleid IN JTF_VARCHAR2_TABLE_100,
1462 i_currency IN JTF_VARCHAR2_TABLE_100,
1463 i_refinfo IN JTF_VARCHAR2_TABLE_100,
1464 i_memo IN JTF_VARCHAR2_TABLE_100,
1465 i_ordermedium IN JTF_VARCHAR2_TABLE_100,
1466 i_eftauthmethod IN JTF_VARCHAR2_TABLE_100,
1467 i_instrsubtype IN JTF_VARCHAR2_TABLE_100,
1468 i_settledate IN JTF_DATE_TABLE,
1469 i_issuedate IN JTF_DATE_TABLE,
1470 i_customerref IN JTF_VARCHAR2_TABLE_100,
1471 o_trxnId OUT NOCOPY JTF_NUMBER_TABLE
1472 )
1473 IS
1474 l_mtangibleid JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1475 l_trxnmid JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1476 l_mpayeeid iby_payee.mpayeeid%TYPE;
1477 l_count NUMBER;
1478
1479 CURSOR c_trxnmid IS
1480 SELECT iby_trxnsumm_mid_s.nextval
1481 FROM DUAL;
1482
1483 CURSOR c_mtangibleid IS
1484 SELECT iby_tangible_s.nextval
1485 FROM DUAL;
1486
1487 BEGIN
1488 /**
1489 * Check if this EC batch is already been submitted
1490 * by the EC application.
1491 */
1492 SELECT count(*) INTO l_count
1493 FROM iby_trxn_summaries_all
1494 WHERE ecbatchid = i_ecbatchid
1495 AND ecappid = i_ecappid
1496 AND payeeid = i_payeeid;
1497
1498 IF(l_count > 0) THEN
1499 raise_application_error(-20000, 'IBY_20560#', FALSE);
1500 END IF;
1501
1502 iby_fipayments_pkg.checkInstrId(i_payeeinstrid);
1503
1504 IF (c_trxnmid%ISOPEN) THEN
1505 CLOSE c_trxnmid;
1506 END IF;
1507
1508 IF (c_mtangibleid%ISOPEN) THEN
1509 CLOSE c_mtangibleid;
1510 END IF;
1511
1512 l_mtangibleid.EXTEND(i_tangibleid.COUNT);
1513 l_trxnmid.EXTEND(i_tangibleid.COUNT);
1514
1515 o_trxnid := JTF_NUMBER_TABLE();
1516 o_trxnid.EXTEND(i_tangibleid.COUNT);
1517
1518 /**
1519 * Obtain the master payeeid for the given payee.
1520 */
1521 iby_accppmtmthd_pkg.getMPayeeId(i_payeeid, l_mpayeeid);
1522
1523 FOR j IN i_tangibleid.FIRST..i_tangibleid.LAST
1524 LOOP
1525 /**
1526 * Check if the payer has registered the instrument.
1527 */
1528 iby_fipayments_pkg.checkInstrId(i_payerinstrid(j));
1529
1530 /**
1531 * Check if there is already a request with same payee id
1532 * tangible id and request type.
1533 */
1534 IF (iby_fipayments_pkg.requestExists(i_payeeid, i_tangibleid(j), NULL, i_reqtype)) THEN
1535 raise_application_error(-20000, 'IBY_20560#', FALSE);
1536 END IF;
1537
1538 o_trxnid(j) := iby_transactioncc_pkg.getTID(i_payeeid, i_tangibleid(j));
1539
1540 OPEN c_trxnmid;
1541 FETCH c_trxnmid INTO l_trxnmid(j);
1542 CLOSE c_trxnmid;
1543
1544 OPEN c_mtangibleid;
1545 FETCH c_mtangibleid INTO l_mtangibleid(j);
1546 CLOSE c_mtangibleid;
1547
1548 END LOOP;
1549
1550 /**
1551 * Create tangible records in iby_tangible.
1552 */
1553 FOR j IN i_tangibleid.FIRST..i_tangibleid.LAST LOOP
1554
1555 /**
1556 * Check for duplicate tangible ids
1557 */
1558 select count(*) into l_count
1559 from iby_trxn_summaries_all s
1560 where payeeId = i_payeeId
1561 and tangibleid = i_tangibleId(j)
1562 and UPPER(reqType) = UPPER(i_reqType);
1563
1564 IF (l_count=0) THEN
1565
1566 INSERT INTO iby_tangible
1567 (
1568 mtangibleId, tangibleid, amount,
1569 currencyNameCode, refinfo, memo, issuedate,
1570 order_medium, eft_auth_method,
1571 last_update_date, last_updated_by,
1572 creation_date, created_by,
1573 last_update_login, object_version_number
1574 )
1575 VALUES
1576 (
1577 l_mtangibleid(j), i_tangibleid(j), i_amount(j),
1578 i_currency(j), i_refinfo(j), i_memo(j), i_issuedate(j),
1579 i_ordermedium(j), i_eftauthmethod(j),
1580 sysdate, fnd_global.user_id,
1581 sysdate, fnd_global.user_id,
1582 fnd_global.login_id, 1
1583 );
1584
1585 /**
1586 * Create transaction records in iby_trxn_summaries_all.
1587 */
1588 INSERT INTO iby_trxn_summaries_all
1589 (
1590 org_id, ecappid, mpayeeid, payeeid,
1591 bepid, bepkey, paymentMethodname,
1592 ecbatchid, trxnmid, transactionid, mtangibleId,
1593 tangibleid, payeeinstrid, payerid, payerinstrid,
1594 amount, currencyNameCode, reqdate,
1595 reqtype, status, settledate, instrtype, instrsubtype,
1596 settlement_customer_reference,
1597 last_update_date, updatedate, last_updated_by,
1598 creation_date, created_by,
1599 last_update_login, object_version_number,needsupdt
1600 )
1601 VALUES
1602 (
1603 i_orgid, i_ecappid, l_mpayeeid, i_payeeid,
1604 i_bepid, i_bepkey, i_pmtmethod, i_ecbatchid,
1605 l_trxnmid(j), o_trxnid(j), l_mtangibleid(j),
1606 i_tangibleid(j), i_payeeinstrid, i_payerid(j),
1607 i_payerinstrid(j), i_amount(j), i_currency(j),
1608 i_reqdate, i_reqtype, 100, i_settledate(j),
1609 'BANKACCOUNT', i_instrsubtype(j),
1610 i_customerref(j),
1611 sysdate, sysdate, fnd_global.user_id,
1612 sysdate, fnd_global.user_id,
1613 fnd_global.login_id, 1,'Y'
1614 );
1615
1616 ELSIF (l_count=1) THEN
1617
1618 /* If a duplicate request exists
1619 * it has to be in a failed status, as 'requestExists' has already
1620 * been checked, hence updating the duplicate failed request.
1621 */
1622
1623 UPDATE iby_tangible
1624 set mtangibleId = l_mtangibleid(j),
1625 amount = i_amount(j),
1626 currencyNameCode = i_currency(j),
1627 refinfo = i_refinfo(j),
1628 memo = i_memo(j),
1629 order_medium = i_ordermedium(j),
1630 eft_auth_method = i_eftauthmethod(j),
1631 issuedate = i_issuedate(j),
1632 last_update_date = sysdate,
1633 last_updated_by = fnd_global.user_id,
1634 creation_date = sysdate,
1635 created_by = fnd_global.user_id,
1636 last_update_login= fnd_global.login_id,
1637 object_version_number = 1
1638 where tangibleid = i_tangibleid(j);
1639
1640 UPDATE iby_trxn_summaries_all
1641 set org_id = i_orgid,
1642 ecappid = i_ecappid,
1643 mpayeeid = l_mpayeeid,
1644 payeeid = i_payeeid,
1645 bepid = i_bepid,
1646 bepkey = i_bepkey,
1647 paymentMethodname = i_pmtmethod,
1648 ecbatchid = i_ecbatchid,
1649 trxnmid = l_trxnmid(j),
1650 transactionid = o_trxnid(j),
1651 mtangibleId = l_mtangibleid(j),
1652 payeeinstrid = i_payeeinstrid,
1653 payerid = i_payerid(j),
1654 payerinstrid = i_payerinstrid(j),
1655 amount = i_amount(j),
1656 currencyNameCode = i_currency(j),
1657 reqdate = i_reqdate,
1658 reqtype = i_reqtype,
1659 status = 100,
1660 settledate = i_settledate(j),
1661 instrtype = 'BANKACCOUNT',
1662 instrsubtype = i_instrsubtype(j),
1663 settlement_customer_reference
1664 = i_customerref(j),
1665 bepcode = null,
1666 bepmessage = null,
1667 batchid = null,
1668 mbatchid = null,
1669 errorlocation = null,
1670 last_update_date = sysdate,
1671 updatedate = sysdate,
1672 last_updated_by = fnd_global.user_id,
1673 creation_date = sysdate,
1674 created_by = fnd_global.user_id,
1675 last_update_login = fnd_global.user_id,
1676 object_version_number = 1
1677
1678 where tangibleid = i_tangibleid(j);
1679 ELSE
1680 raise_application_error(-20000, 'IBY_20560#', FALSE);
1681
1682 END IF;
1683
1684 END LOOP;
1685
1686 COMMIT;
1687 END insertEFTBatchTrxns;
1688
1689 -------------------------------------------------------------------------
1690 -- This procedure inserts or update the verify transaction data into the
1691 -- database.
1692 -------------------------------------------------------------------------
1693
1694 PROCEDURE createEFTVerifyTrxn(
1695 i_ecappid IN iby_trxn_summaries_all.ecappid%TYPE,
1696 i_reqtype IN iby_trxn_summaries_all.reqtype%TYPE,
1697 i_bepid IN iby_trxn_summaries_all.bepid%TYPE,
1698 i_bepkey IN iby_trxn_summaries_all.bepkey%TYPE,
1699 i_payeeid IN iby_trxn_summaries_all.payeeid%TYPE,
1700 i_payeeinstrid IN iby_trxn_summaries_all.payeeinstrid%TYPE,
1701 i_tangibleid IN iby_trxn_summaries_all.tangibleid%TYPE,
1702 i_amount IN iby_trxn_summaries_all.amount%TYPE,
1703 i_currency IN iby_trxn_summaries_all.currencynamecode%TYPE,
1704 i_status IN iby_trxn_summaries_all.status%TYPE,
1705 i_refinfo IN iby_tangible.refinfo%TYPE,
1706 i_memo IN iby_tangible.memo%TYPE,
1707 i_acctno IN iby_tangible.acctno%TYPE,
1708 i_ordermedium IN iby_tangible.order_medium%TYPE,
1709 i_eftauthmethod IN iby_tangible.eft_auth_method%TYPE,
1710 i_orgid IN iby_trxn_summaries_all.org_id%TYPE,
1711 i_pmtmethod IN iby_trxn_summaries_all.paymentmethodname%TYPE,
1712 i_payerid IN iby_trxn_summaries_all.payerid%TYPE,
1713 i_instrtype IN iby_trxn_summaries_all.instrtype%TYPE,
1714 i_instrsubtype IN iby_trxn_summaries_all.instrsubtype%TYPE,
1715 i_payerinstrid IN iby_trxn_summaries_all.payerinstrid%TYPE,
1716 i_trxndate IN iby_trxn_summaries_all.updatedate%TYPE,
1717 i_trxntypeid IN iby_trxn_summaries_all.TrxntypeID%TYPE,
1718 i_bepcode IN iby_trxn_summaries_all.BEPCode%TYPE,
1719 i_bepmessage IN iby_trxn_summaries_all.BEPMessage%TYPE,
1720 i_errorlocation IN iby_trxn_summaries_all.errorlocation%TYPE,
1721 i_referenceCode IN iby_trxn_summaries_all.proc_reference_code%TYPE,
1722 o_trxnid OUT NOCOPY iby_trxn_summaries_all.transactionid%TYPE,
1723 i_orgtype IN iby_trxn_summaries_all.org_type%TYPE,
1724 i_pmtchannelcode IN iby_trxn_summaries_all.payment_channel_code%TYPE,
1725 i_factoredflag IN iby_trxn_summaries_all.factored_flag%TYPE,
1726 i_pmtinstrassignmentId IN iby_trxn_summaries_all.payer_instr_assignment_id%TYPE,
1727 i_process_profile_code IN iby_trxn_summaries_all.process_profile_code%TYPE,
1728 o_trxnmid OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE
1729 ) IS
1730
1731 l_mtangibleid iby_trxn_summaries_all.mtangibleid%TYPE;
1732 l_trxnmid iby_trxn_summaries_all.transactionid%TYPE;
1733
1734 -- new parameters for eft authorizations
1735 l_debit_auth_flag iby_trxn_summaries_all.debit_auth_flag%TYPE;
1736 l_debit_auth_method iby_trxn_summaries_all.debit_auth_method%TYPE;
1737 l_debit_auth_reference iby_trxn_summaries_all.debit_auth_reference%TYPE;
1738 l_payer_party_id iby_trxn_summaries_all.payer_party_id%TYPE;
1739 l_mpayeeid iby_payee.mpayeeid%TYPE;
1740 l_trxn_exists VARCHAR2(1);
1741
1742 l_payer_notif_flag iby_trxn_summaries_all.payer_notification_required%TYPE;
1743 l_bep_type iby_bepinfo.bep_type%TYPE;
1744
1745 CURSOR trxn_exists IS
1746 SELECT 'Y', trxnmid, mtangibleid
1747 FROM iby_trxn_summaries_all s
1748 WHERE payeeId = i_payeeId
1749 AND tangibleid = i_tangibleid
1750 AND UPPER(reqType) = UPPER(i_reqType)
1751 AND status <> '0'
1752 ORDER BY trxnmid desc;
1753 -- It will update the same transaction if not successfull
1754 -- of the same request type
1755
1756 CURSOR c_payer_notif_eft (i_user_fcpp_code iby_trxn_summaries_all.process_profile_code%TYPE) IS
1757 SELECT DECODE(payer_notification_format, null, 'N', 'Y')
1758 FROM iby_fndcpt_user_eft_pf_b up, iby_fndcpt_sys_eft_pf_b sp
1759 WHERE up.sys_eft_profile_code = sp.sys_eft_profile_code
1760 AND up.user_eft_profile_code = i_user_fcpp_code;
1761
1762 BEGIN
1763
1764 -- Check if payer has registered the instrument
1765
1766 IF ( NVL(i_payerinstrid,0) <> 0) THEN
1767 iby_fipayments_pkg.checkInstrId(i_payerinstrid);
1768 END IF;
1769
1770 -- Get the master payeeid for the given payee
1771 iby_accppmtmthd_pkg.getMPayeeId(i_payeeid, l_mpayeeid);
1772
1773 -- this function returns the existing transactionid from the iby_trxn_summaries_all
1774 -- table if one exist for the payeeid and tangibleid, or a new one from the DB
1775 -- sequence if none exists.
1776 o_trxnid := iby_transactioncc_pkg.getTID(i_payeeid, i_tangibleid);
1777
1778 -- Verify if transaction already exist and aget l_trxnmid so that the
1779 -- update is done using the PK
1780
1781 OPEN trxn_exists;
1782 FETCH trxn_exists INTO l_trxn_exists, l_trxnmid, l_mtangibleid;
1783 CLOSE trxn_exists;
1784
1785
1786 -- get the debit authrization values
1787 begin
1788 IF (i_pmtinstrassignmentId>0) then
1789 select debit_auth_flag,
1790 debit_auth_method,
1791 debit_auth_reference
1792 into l_debit_auth_flag,
1793 l_debit_auth_method,
1794 l_debit_auth_reference
1795 from iby_pmt_instr_uses_all
1796 where instrument_payment_use_id=i_pmtinstrassignmentId;
1797 END IF;
1798 exception
1799 WHEN NO_DATA_FOUND THEN
1800 null;
1801 end;
1802
1803
1804 -- get the payer party id
1805 -- get the payer_party_id if exists
1806 begin
1807 if(i_payerid is not NULL) then
1808 l_payer_party_id :=to_number(i_payerid);
1809 end if;
1810 exception
1811 when others then
1812 l_payer_party_id :=null;
1813 end;
1814
1815 -- get bep_type info
1816 BEGIN
1817 SELECT bep_type
1818 INTO l_bep_type
1819 FROM iby_bepinfo
1820 WHERE bepid = i_bepid;
1821 EXCEPTION
1822 WHEN others THEN NULL;
1823 END;
1824
1825 -- get payer notification flag for capture transactions
1826 -- for Gateway
1827 -- for processor transactions the flag is set during batch close
1828 -- only BANKACCOUNTS
1829 IF (i_trxntypeid IN (3,8,9,100) AND
1830 l_bep_type = 'GATEWAY') THEN
1831 -- only BANKACCOUNTS
1832 OPEN c_payer_notif_eft(i_process_profile_code);
1833 FETCH c_payer_notif_eft INTO l_payer_notif_flag;
1834 CLOSE c_payer_notif_eft;
1835
1836 END IF;
1837
1838 IF (NVL(l_trxn_exists, 'N') = 'N') THEN
1839 --Create an entry in iby_tangible table
1840 iby_bill_pkg.createBill(
1841 i_tangibleid, -- IN i_billId
1842 i_amount, -- IN i_billAmount
1843 i_currency, -- IN i_billCurDef
1844 i_acctno, -- IN i_billAcct
1845 i_refinfo, -- IN i_billRefInfo
1846 i_memo, -- IN i_billMemo
1847 i_ordermedium, -- IN i_billOrderMedium
1848 i_eftauthmethod, -- IN i_billEftAuthMethod
1849 l_mtangibleid); -- OUT io_mtangibleid
1850
1851
1852 -- Create transaction records in iby_trxn_summaries_all.
1853 INSERT INTO iby_trxn_summaries_all(
1854 trxnmid,
1855 org_id,
1856 ecappid,
1857 mpayeeid,
1858 payeeid,
1859 bepid,
1860 bepkey,
1861 paymentMethodname,
1862 transactionid,
1863 mtangibleId,
1864 tangibleid,
1865 payeeinstrid,
1866 payerid,
1867 payerinstrid,
1868 amount,
1869 currencyNameCode,
1870 reqtype,
1871 status,
1872 settledate,
1873 instrtype,
1874 instrsubtype,
1875 last_update_date,
1876 reqdate,
1877 updatedate,
1878 last_updated_by,
1879 creation_date,
1880 created_by,
1881 last_update_login,
1882 object_version_number,
1883 bepcode,
1884 bepmessage,
1885 errorlocation,
1886 trxntypeid,
1887 proc_reference_code,
1888 org_type,
1889 payment_channel_code,
1890 factored_flag,
1891 payer_instr_assignment_id,
1892 process_profile_code,
1893 payer_party_id,
1894 debit_auth_flag,
1895 debit_auth_method,
1896 debit_auth_reference,
1897 payer_notification_required,
1898 needsupdt
1899 ) VALUES (
1900 iby_trxnsumm_mid_s.NEXTVAL, -- trxnmid
1901 i_orgid, -- org_id
1902 i_ecappid, -- ecappid
1903 l_mpayeeid, -- mpayeeid
1904 i_payeeid, -- payeeid
1905 i_bepid, -- bepid
1906 i_bepkey, -- bepkey
1907 i_pmtmethod, -- paymentMethodname
1908 o_trxnid, -- transactionid
1909 l_mtangibleid, -- mtangibleId
1910 i_tangibleid, -- tangibleid
1911 i_payeeinstrid, -- payeeinstrid
1912 i_payerid, -- payerid
1913 i_payerinstrid, -- payerinstrid
1914 i_amount, -- amount
1915 i_currency, -- currencyNameCode
1916 i_reqtype, -- reqtype
1917 i_status, -- status
1918 null, -- settledate
1919 i_instrtype, -- instrtype
1920 i_instrsubtype, -- instrsubtype
1921 sysdate, -- last_update_date
1922 i_trxndate, -- reqdate
1923 i_trxndate, -- updatedate
1924 fnd_global.user_id, -- last_updated_by
1925 sysdate, -- creation_date
1926 fnd_global.user_id, -- created_by
1927 fnd_global.login_id, -- last_update_login
1928 1, -- object_version_number
1929 i_bepcode, -- bepcode
1930 i_bepmessage, -- bepmessage
1931 i_errorlocation, -- errorlocation
1932 i_trxntypeid, -- trxntypeid
1933 i_referencecode, -- reference code
1934 i_orgtype, -- org_type
1935 i_pmtchannelcode, -- payment_channel_code
1936 i_factoredflag, -- factored_flag
1937 i_pmtinstrassignmentId,
1938 i_process_profile_code,
1939 l_payer_party_id,
1940 l_debit_auth_flag,
1941 l_debit_auth_method,
1942 l_debit_auth_reference,
1943 DECODE(i_status, 0, l_payer_notif_flag, 'N'),
1944 'Y'
1945 ) RETURNING trxnmid INTO l_trxnmid;
1946
1947 ELSE
1948 -- A transaction is already created.
1949
1950 -- Update iby_tangible table
1951
1952 iby_bill_pkg.modBill(
1953 l_mtangibleid, -- IN i_mtangibleid
1954 i_tangibleid, -- IN i_billId
1955 i_amount, -- IN i_billAmount
1956 i_currency, -- IN i_billCurDef
1957 i_acctno, -- IN i_billAcct
1958 i_refinfo, -- IN i_billRefInfo
1959 i_memo, -- IN i_billMemo
1960 i_ordermedium, -- IN i_billOrderMedium
1961 i_eftauthmethod); -- IN i_billEftAuthMethod
1962
1963 UPDATE iby_trxn_summaries_all
1964 SET tangibleid = i_tangibleid,
1965 org_id = i_orgid,
1966 ecappid = i_ecappid,
1967 mpayeeid = l_mpayeeid,
1968 payeeid = i_payeeid,
1969 bepid = i_bepid,
1970 bepkey = i_bepkey,
1971 paymentMethodname = i_pmtmethod,
1972 transactionid = o_trxnid,
1973 mtangibleId = l_mtangibleid,
1974 payeeinstrid = i_payeeinstrid,
1975 payerid = i_payerid,
1976 payerinstrid = i_payerinstrid,
1977 amount = i_amount,
1978 currencyNameCode = i_currency,
1979 reqtype = i_reqtype,
1980 status = i_status,
1981 instrtype = i_instrtype,
1982 instrsubtype = i_instrsubtype,
1983 bepcode = i_bepcode,
1984 bepmessage = i_bepmessage,
1985 errorlocation = i_errorlocation,
1986 last_update_date = sysdate,
1987 reqdate = i_trxndate,
1988 updatedate = i_trxndate,
1989 last_updated_by = fnd_global.user_id,
1990 creation_date = sysdate,
1991 created_by = fnd_global.user_id,
1992 last_update_login = fnd_global.user_id,
1993 object_version_number = 1,
1994 -- trxntypeid = i_trxntypeid,
1995 proc_reference_code = i_referencecode,
1996 org_type = i_orgtype,
1997 payment_channel_code = i_pmtchannelcode,
1998 factored_flag = i_factoredflag,
1999 debit_auth_flag = l_debit_auth_flag,
2000 debit_auth_method = l_debit_auth_method,
2001 debit_auth_reference = l_debit_auth_reference,
2002 payer_instr_assignment_id= i_pmtinstrassignmentId,
2003 process_profile_code = i_process_profile_code,
2004 payer_party_id = l_payer_party_id,
2005 payer_notification_required = DECODE(i_status, 0, l_payer_notif_flag, 'N')
2006 WHERE trxnmid = l_trxnmid;
2007
2008
2009 END IF;
2010 o_trxnmid := l_trxnmid;
2011
2012 COMMIT;
2013
2014 EXCEPTION
2015 WHEN OTHERS THEN
2016
2017 IF (trxn_exists%ISOPEN ) THEN
2018 CLOSE trxn_exists;
2019 END IF;
2020 raise_application_error(-20000, 'IBY_20400#', FALSE);
2021
2022 END createEFTVerifyTrxn;
2023
2024 /*--------------------------------------------------------------------
2025 | NAME:
2026 | print_debuginfo
2027 |
2028 | PURPOSE:
2029 | This procedure prints the debug message to the concurrent manager
2030 | log file.
2031 |
2032 | PARAMETERS:
2033 | IN
2034 | p_debug_text - The debug message to be printed
2035 |
2036 | OUT
2037 |
2038 |
2039 | RETURNS:
2040 |
2041 | NOTES:
2042 |
2043 *---------------------------------------------------------------------*/
2044 PROCEDURE print_debuginfo(
2045 p_module IN VARCHAR2,
2046 p_debug_text IN VARCHAR2
2047 )
2048 IS
2049 PRAGMA AUTONOMOUS_TRANSACTION;
2050
2051 BEGIN
2052
2053 /*
2054 * If FND_GLOBAL.conc_request_id is -1, it implies that
2055 * this method has not been invoked via the concurrent
2056 * manager. In that case, write to apps log else write
2057 * to concurrent manager log file.
2058 */
2059 IF (FND_GLOBAL.conc_request_id = -1) THEN
2060
2061 /*
2062 * OPTION I:
2063 * Write debug text to the common application log file.
2064 */
2065 IBY_DEBUG_PUB.add(
2066 substr(RPAD(p_module,55) || ' : ' || p_debug_text, 0, 150),
2067 FND_LOG.G_CURRENT_RUNTIME_LEVEL,
2068 'iby.plsql.IBY_VALIDATIONSETS_PUB'
2069 );
2070
2071 /*
2072 * OPTION II:
2073 * Write debug text to DBMS output file.
2074 */
2075 --DBMS_OUTPUT.PUT_LINE(substr(RPAD(p_module,40)||' : '||
2076 -- p_debug_text, 0, 150));
2077
2078 /*
2079 * OPTION III:
2080 * Write debug text to temporary table.
2081 */
2082 /* uncomment these two lines for debugging */
2083 --INSERT INTO TEMP_IBY_LOGS VALUES (p_module || ': '
2084 -- || p_debug_text, sysdate);
2085
2086 --COMMIT;
2087
2088 ELSE
2089
2090 /*
2091 * OPTION I:
2092 * Write debug text to the concurrent manager log file.
2093 */
2094 FND_FILE.PUT_LINE(FND_FILE.LOG, p_module || ': ' || p_debug_text);
2095
2096 /*
2097 * OPTION II:
2098 * Write debug text to DBMS output file.
2099 */
2100 --DBMS_OUTPUT.PUT_LINE(substr(RPAD(p_module,40)||' : '||
2101 -- p_debug_text, 0, 150));
2102
2103 /*
2104 * OPTION III:
2105 * Write debug text to temporary table.
2106 */
2107 /* uncomment these two lines for debugging */
2108 --INSERT INTO TEMP_IBY_LOGS VALUES (p_module || ': '
2109 -- || p_debug_text, sysdate);
2110
2111 --COMMIT;
2112
2113 END IF;
2114
2115 END print_debuginfo;
2116
2117
2118 PROCEDURE validate_open_batch
2119 (
2120 p_bep_id IN iby_trxn_summaries_all.bepid%TYPE,
2121 p_mbatch_id IN iby_batches_all.mbatchid%TYPE,
2122 p_funds_xfer_format_code IN iby_fndcpt_sys_eft_pf_b.funds_xfer_format_code%TYPE
2123 )
2124 IS
2125
2126 l_call_string VARCHAR2(1000);
2127 l_call_params JTF_VARCHAR2_TABLE_200 := JTF_VARCHAR2_TABLE_200();
2128 l_return_status VARCHAR2(10);
2129 l_msg_count NUMBER;
2130 l_msg_data VARCHAR2(5000);
2131 l_trxn_count NUMBER;
2132 l_module_name VARCHAR2(200) := G_PKG_NAME || '.validate_open_batch';
2133
2134 CURSOR c_valsets
2135 IS
2136 SELECT distinct validation_code_package, validation_code_entry_point
2137 FROM iby_validation_sets_b vs, iby_fndcpt_sys_eft_pf_b pf,
2138 iby_val_assignments va
2139 WHERE (vs.validation_code_language = 'PLSQL')
2140 AND (vs.validation_level_code = 'INSTRUCTION' )
2141 -- AND (pf.payment_system_id = ci_bep_id)
2142 AND (pf.funds_xfer_format_code = va.assignment_entity_id)
2143 AND (va.val_assignment_entity_type = 'FORMAT')
2144 AND (va.validation_set_code = vs.validation_set_code)
2145 AND (va.inactive_date IS NULL)
2146 AND (pf.inactive_date IS NULL)
2147 and (pf.funds_xfer_format_code = p_funds_xfer_format_code);
2148
2149
2150
2151
2152 BEGIN
2153
2154 --
2155 -- first check if any encrypted trxns exist in the batch;
2156 -- if so, then the security key must be present for the batch
2157 -- close to continue
2158 --
2159
2160 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2161 print_debuginfo(l_module_name, 'p_mbatch_id = ' || p_mbatch_id);
2162 END IF;
2163
2164 SELECT COUNT(transactionid)
2165 INTO l_trxn_count
2166 FROM iby_batches_all ba, iby_trxn_summaries_all ts
2167 WHERE (ba.mbatchid = p_mbatch_id)
2168 AND (ba.payeeid = ts.payeeid)
2169 AND (ba.batchid = ts.batchid);
2170 --
2171 -- batch cannot be empty
2172 --
2173 IF (l_trxn_count < 1) THEN
2174 raise_application_error(-20000,'IBY_50314',FALSE);
2175 END IF;
2176
2177 l_call_params.extend(6);
2178 l_call_params(1) := '1';
2179 l_call_params(2) := '''' || FND_API.G_TRUE || '''';
2180 l_call_params(3) := TO_CHAR(p_mbatch_id);
2181 l_call_params(4) := '';
2182 l_call_params(5) := '';
2183 l_call_params(6) := '';
2184
2185 FOR cp IN c_valsets LOOP
2186
2187 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2188 print_debuginfo(l_module_name, 'validation_code_package = ' || cp.validation_code_package);
2189 print_debuginfo(l_module_name, 'validation_code_entry_point = ' || cp.validation_code_entry_point);
2190 END IF;
2191
2192 l_call_string :=iby_utility_pvt.get_call_exec(cp.validation_code_package,
2193 cp.validation_code_entry_point,
2194 l_call_params);
2195 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2196 print_debuginfo(l_module_name, 'l_call_string = ' || l_call_string);
2197 END IF;
2198 EXECUTE IMMEDIATE l_call_string USING
2199 OUT l_return_status,
2200 OUT l_msg_count,
2201 OUT l_msg_data;
2202
2203 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2204 raise_application_error(-20000,
2205 'IBY_20220#ERRMSG=' || fnd_msg_pub.get(p_msg_index => 1,p_encoded => FND_API.G_FALSE),
2206 FALSE);
2207 END IF;
2208 END LOOP;
2209
2210 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2211 print_debuginfo(l_module_name, 'Exiting ... ');
2212 END IF;
2213
2214 END validate_open_batch;
2215
2216
2217
2218 END IBY_TRANSACTIONEFT_PKG;
2219