[Home] [Help]
PACKAGE BODY: APPS.IBY_TRANSACTIONEFT_PKG
Source
1 PACKAGE BODY IBY_TRANSACTIONEFT_PKG AS
2 /*$Header: ibyteftb.pls 120.31.12010000.4 2008/12/16 10:05:22 jnallam ship $*/
3
4 --
5 -- Declare global variables
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_TRANSACTIONEFT_PKG';
8
9 /*
10 * The purpose of this procedure is to check if there is any open
11 * transaction which are due. If there is, it will insert a row into
12 * the iby_batches_all table to keep track of the batch status, and
13 * change the transactions status to other status. So, the open
14 * transactions will be sent as part of future batch close. Also, it
15 * will not allow any modification and cancellation to these transactions.
16 */
17 PROCEDURE createBatchCloseTrxns(
18 merch_batchid_in IN VARCHAR2,
19 merchant_id_in IN VARCHAR2,
20 vendor_id_in IN NUMBER,
21 vendor_key_in IN VARCHAR2,
22 newstatus_in IN NUMBER,
23 oldstatus_in IN NUMBER,
24 batchstate_in IN NUMBER,
25 settlement_date_in IN DATE,
26 req_type_in IN VARCHAR2,
27 numtrxns_out OUT NOCOPY NUMBER
28 )
29 IS
30
31 numrows NUMBER;
32 l_mpayeeid iby_payee.mpayeeid%type;
33 l_mbatchid iby_batches_all.mbatchid%type;
34
35 BEGIN
36
37 SELECT
38 COUNT(*)
39 INTO
40 numtrxns_out
41 FROM
42 iby_trxn_summaries_all
43 WHERE
44 status = oldstatus_in AND
45 payeeid = merchant_id_in AND
46 bepid = vendor_id_in AND
47 bepkey = vendor_key_in AND
48 batchid IS NULL AND
49 trunc(settledate) <= trunc(settlement_date_in) AND
50 instrtype = 'BANKACCOUNT';
51
52 /*
53 * If there isn't any open transactions, then exit.
54 */
55 IF (numtrxns_out > 0) THEN
56
57 SELECT
58 iby_batches_s.NEXTVAL
59 INTO
60 l_mbatchid
61 FROM
62 DUAL;
63
64 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
65
66 INSERT INTO iby_batches_all
67 (MBATCHID,
68 BATCHID,
69 MPAYEEID,
70 PAYEEID,
71 BEPID,
72 BEPKEY,
73 BATCHSTATUS,
74 BATCHSTATEID,
75 BATCHCLOSEDATE,
76 REQTYPE,
77 REQDATE,
78 LAST_UPDATE_DATE,
79 LAST_UPDATED_BY,
80 CREATION_DATE,
81 CREATED_BY,
82 LAST_UPDATE_LOGIN,
83 OBJECT_VERSION_NUMBER,
84 SENTCOUNTER,
85 SENTCOUNTERDAILY
86 )
87 VALUES
88 (
89 l_mbatchid,
90 merch_batchid_in,
91 l_mpayeeid,
92 merchant_id_in,
93 vendor_id_in,
94 vendor_key_in,
95 batchstate_in,
96 batchstate_in,
97 settlement_date_in,
98 req_type_in,
99 sysdate,
100 sysdate,
101 fnd_global.user_id,
102 sysdate,
103 fnd_global.user_id,
104 fnd_global.login_id,
105 0,
106 0,
107 0
108 );
109
110 UPDATE
111 IBY_TRXN_SUMMARIES_ALL
112 SET
113 status = newstatus_in,
114 batchid = merch_batchid_in,
115 mbatchid = l_mbatchid,
116 last_update_date = sysdate,
117 updatedate = sysdate,
118 last_updated_by = fnd_global.user_id,
119 object_version_number = object_version_number + 1
120 WHERE
121 status = oldstatus_in AND
122 payeeid = merchant_id_in AND
123 bepid = vendor_id_in AND
124 bepkey = vendor_key_in AND
125 batchid IS NULL AND
126 trunc(settledate) <= trunc(settlement_date_in) AND
127 instrtype = 'BANKACCOUNT'
128 ;
129
130 COMMIT;
131
132 END IF;
133
134 END createBatchCloseTrxns;
135
136 /*
137 * The purpose of this procedure is to check if there is any open
138 * transaction which are due. If there is, it will insert a row into
139 * the iby_batches_all table to keep track of the batch status, and
140 * change the transactions status to other status. So, the open
141 * transactions will be sent as part of future batch close. Also, it
142 * will not allow any modification and cancellation to these transactions.
143 */
144 PROCEDURE createBatchCloseTrxnsNew(
145 merch_batchid_in IN VARCHAR2,
146 profile_code_in IN iby_batches_all.
147 process_profile_code%TYPE,
148 merchant_id_in IN VARCHAR2,
149 vendor_id_in IN NUMBER,
150 vendor_key_in IN VARCHAR2,
151 newstatus_in IN NUMBER,
152 oldstatus_in IN NUMBER,
153 batchstate_in IN NUMBER,
154 settlement_date_in IN DATE,
155 req_type_in IN VARCHAR2,
156 instr_type_in IN iby_batches_all.
157 instrument_type%TYPE,
158 br_disputed_flag_in IN iby_batches_all.
159 br_disputed_flag%TYPE,
160 f_pmt_channel_in IN iby_trxn_summaries_all.
161 payment_channel_code%TYPE,
162 f_curr_in IN iby_trxn_summaries_all.
163 currencynamecode%TYPE,
164 f_settle_date IN iby_trxn_summaries_all.
165 settledate%TYPE,
166 f_due_date IN iby_trxn_summaries_all.
167 settlement_due_date%TYPE,
168 f_maturity_date IN iby_trxn_summaries_all.
169 br_maturity_date%TYPE,
170 f_instr_type IN iby_trxn_summaries_all.
171 instrtype%TYPE,
172 numtrxns_out OUT NOCOPY NUMBER,
173 mbatch_ids_out OUT NOCOPY JTF_NUMBER_TABLE,
174 batch_ids_out OUT NOCOPY JTF_VARCHAR2_TABLE_100
175 )
176 IS
177
178 numrows NUMBER;
179 l_mpayeeid iby_payee.mpayeeid%type;
180 l_mbatchid iby_batches_all.mbatchid%type;
181 l_module_name CONSTANT VARCHAR2(200) :=
182 G_PKG_NAME || '.createBatchCloseTrxnsNew';
183
184 l_batches_tab IBY_TRANSACTIONCC_PKG.batchAttrTabType;
185 l_trxns_in_batch_tab IBY_TRANSACTIONCC_PKG.trxnsInBatchTabType;
186
187 l_index NUMBER;
188
189 BEGIN
190
191 print_debuginfo(l_module_name, 'ENTER');
192
193 mbatch_ids_out := JTF_NUMBER_TABLE();
194 batch_ids_out := JTF_VARCHAR2_TABLE_100();
195
196 /*
197 * BEP and vendor related params.
198 */
199 print_debuginfo(l_module_name, 'vendor_id_in: '
200 || vendor_id_in);
201 print_debuginfo(l_module_name, 'vendor_key_in: '
202 || vendor_key_in);
203 print_debuginfo(l_module_name, 'merchant_id_in: '
204 || merchant_id_in);
205 print_debuginfo(l_module_name, 'req_type_in: '
206 || req_type_in);
207 print_debuginfo(l_module_name, 'profile_code_in: '
208 || profile_code_in);
209 print_debuginfo(l_module_name, 'settlement_date_in: '
210 || settlement_date_in);
211 print_debuginfo(l_module_name, 'oldstatus_in: '
212 || oldstatus_in);
213
214 SELECT
215 COUNT(*)
216 INTO
217 numtrxns_out
218 FROM
219 iby_trxn_summaries_all
220 WHERE
221 status = oldstatus_in AND
222 payeeid = merchant_id_in AND
223 bepid = vendor_id_in AND
224 bepkey = vendor_key_in AND
225 batchid IS NULL AND
226 trunc(nvl(settledate, sysdate)) <= trunc(nvl(settlement_date_in, sysdate-1)) AND
227 instrtype = 'BANKACCOUNT';
228
229 /*
230 * If there isn't any open transactions, then exit.
231 */
232 IF (numtrxns_out > 0) THEN
233
234 print_debuginfo(l_module_name, 'Invoking grouping ..');
235
236 /*
237 * Group all the transactions for this profile into
238 * batches as per the grouping attributes on the profile.
239 */
240 IBY_TRANSACTIONCC_PKG.performTransactionGrouping(
241 profile_code_in,
242 instr_type_in,
243 req_type_in,
244 f_pmt_channel_in,
245 f_curr_in,
246 f_settle_date,
247 f_due_date,
248 f_maturity_date,
249 f_instr_type,
250 l_batches_tab,
251 l_trxns_in_batch_tab
252 );
253
254 print_debuginfo(l_module_name, '# batches created: '
255 || l_batches_tab.COUNT);
256
257 print_debuginfo(l_module_name, '# transactions processed: '
258 || l_trxns_in_batch_tab.COUNT);
259
260 /*
261 * After grouping it is possible that multiple batches were
262 * created. Each batch will be a separate row in the
263 * IBY_BATCHES_ALL table with a unique mbatchid.
264 *
265 * The user may have provided a batch id (batch prefix), we will
266 * have to assign that batch id to each of the created batches.
267 *
268 * This batch id would be sent to the payment system. It therefore
269 * has to be unique. Therefore, we add a suffix to the user
270 * provided batch id to ensure that batches created after grouping
271 * have a unique batch id.
272 */
273 IF (l_batches_tab.COUNT > 0) THEN
274
275 l_index := 1;
276 FOR k IN l_batches_tab.FIRST .. l_batches_tab.LAST LOOP
277
278 /*
279 * Assign a unique batch id to each batch.
280 */
281 l_batches_tab(k).batch_id :=
282 merch_batchid_in ||'_'|| l_index;
283 l_index := l_index + 1;
284
285 END LOOP;
286
287 END IF;
288
289 /*
290 * After grouping, the transactions will be assigned a mbatch id.
291 * Assign them a batch id as well (based on the batch id
292 * corresponding to each mbatch id).
293 */
294 IF (l_trxns_in_batch_tab.COUNT > 0) THEN
295
296 FOR m IN l_trxns_in_batch_tab.FIRST ..
297 l_trxns_in_batch_tab.LAST LOOP
298
299 FOR k IN l_batches_tab.FIRST .. l_batches_tab.LAST LOOP
300
301 /*
302 * Find the mbatch id in the batches array
303 * corresponding to the mbatchid of this transaction.
304 */
305 IF (l_trxns_in_batch_tab(m).mbatch_id =
306 l_batches_tab(k).mbatch_id) THEN
307
308 /*
309 * Assign the batch id from the batches array
310 * to this transaction.
311 */
312 l_trxns_in_batch_tab(m).batch_id :=
313 l_batches_tab(k).batch_id;
314
315 END IF;
316
317 END LOOP;
318
319 END LOOP;
320
321 END IF;
322
323
324 /*
325
326 SELECT
327 iby_batches_s.NEXTVAL
328 INTO
329 l_mbatchid
330 FROM
331 DUAL;
332 */
333
334 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
335
336 IF (l_batches_tab.COUNT <> 0) THEN
337
338 FOR i IN l_batches_tab.FIRST .. l_batches_tab.LAST LOOP
339
340 print_debuginfo(l_module_name, 'Going to insert batch '
341 || l_batches_tab(i).mbatch_id);
342
343 INSERT INTO iby_batches_all
344 (MBATCHID,
345 BATCHID,
346 MPAYEEID,
347 PAYEEID,
348 BEPID,
349 BEPKEY,
350 BATCHSTATUS,
351 BATCHSTATEID,
352 BATCHCLOSEDATE,
353 REQTYPE,
354 REQDATE,
355 LAST_UPDATE_DATE,
356 LAST_UPDATED_BY,
357 CREATION_DATE,
358 CREATED_BY,
359 LAST_UPDATE_LOGIN,
360 OBJECT_VERSION_NUMBER,
361 SENTCOUNTER,
362 SENTCOUNTERDAILY,
363 PROCESS_PROFILE_CODE,
364 INSTRUMENT_TYPE,
365 BR_DISPUTED_FLAG,
366 CURRENCYNAMECODE,
367 PAYEEINSTRID,
368 LEGAL_ENTITY_ID,
369 ORG_ID,
370 ORG_TYPE,
371 SETTLEDATE
372 )
373 VALUES
374 (
375 l_batches_tab(i).mbatch_id,
376 merch_batchid_in || '_' || i,
377 l_mpayeeid,
378 merchant_id_in,
379 vendor_id_in,
380 l_batches_tab(i).bep_key,
381 batchstate_in,
382 batchstate_in,
383 settlement_date_in,
384 req_type_in,
385 sysdate,
386 sysdate,
387 fnd_global.user_id,
388 sysdate,
389 fnd_global.user_id,
390 fnd_global.login_id,
391 0,
392 0,
393 0,
394 l_batches_tab(i).profile_code,
395 instr_type_in,
396 br_disputed_flag_in,
397
398 /*
399 * Fix for bug 5614670:
400 *
401 * Populate the batch related attributes
402 * created after grouping in this
403 * insert.
404 */
405 l_batches_tab(i).curr_code,
406 l_batches_tab(i).int_bank_acct_id,
407 l_batches_tab(i).le_id,
408 l_batches_tab(i).org_id,
409 l_batches_tab(i).org_type,
410 l_batches_tab(i).settle_date
411 );
412
413 /*
414 * Store the created mbatchids in the output param
415 * to return to the caller.
416 */
417 mbatch_ids_out.EXTEND;
418 mbatch_ids_out(i) := l_batches_tab(i).mbatch_id;
419
420 /*
421 * Store the created batchids in the output param
422 * to return to the caller.
423 */
424 batch_ids_out.EXTEND;
425 batch_ids_out(i) := l_batches_tab(i).batch_id;
426
427 print_debuginfo(l_module_name, 'Finished insert batch '
428 || l_batches_tab(i).mbatch_id);
429
430 END LOOP;
431
432 END IF; -- if l_batches_tab.COUNT <> 0
433
434 IF (l_trxns_in_batch_tab.COUNT <> 0) THEN
435
436 FOR i IN l_trxns_in_batch_tab.FIRST .. l_trxns_in_batch_tab.LAST LOOP
437
438 print_debuginfo(l_module_name, 'Going to update transaction '
439 || l_trxns_in_batch_tab(i).trxn_id);
440
441 UPDATE
442 IBY_TRXN_SUMMARIES_ALL
443 SET
444 status = newstatus_in,
445 batchid = l_trxns_in_batch_tab(i).batch_id,
446 mbatchid = l_trxns_in_batch_tab(i).mbatch_id,
447 last_update_date = sysdate,
448 updatedate = sysdate,
449 last_updated_by = fnd_global.user_id,
450 object_version_number = object_version_number + 1
451 WHERE
452 transactionid = l_trxns_in_batch_tab(i).trxn_id AND
453 status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED
454 ;
455
456 print_debuginfo(l_module_name, 'Finished updating transaction'
457 || l_trxns_in_batch_tab(i).trxn_id);
458
459 END LOOP;
460
461 END IF; -- if l_trxns_in_batch_tab.COUNT <> 0
462
463 COMMIT;
464
465 ELSE
466
467 print_debuginfo(l_module_name, 'No open transactions; Exiting ..');
468
469 END IF;
470
471 print_debuginfo(l_module_name, 'mbatchids count: '
472 || mbatch_ids_out.COUNT);
473
474 print_debuginfo(l_module_name, 'EXIT');
475
476 END createBatchCloseTrxnsNew;
477
478 /*
479 * This is the overloaded form of the previous API. This takes an array of
480 * profile codes as input parameter (instead of a single one)
481 * The purpose of this procedure is to check if there is any open
482 * transaction which are due. If there is, it will insert a row into
483 * the iby_batches_all table to keep track of the batch status, and
484 * change the transactions status to other status. So, the open
485 * transactions will be sent as part of future batch close. Also, it
486 * will not allow any modification and cancellation to these transactions.
487 */
488 PROCEDURE createBatchCloseTrxnsNew(
489 merch_batchid_in IN VARCHAR2,
490 profile_code_array IN JTF_VARCHAR2_TABLE_100,
491 merchant_id_in IN VARCHAR2,
492 vendor_id_in IN NUMBER,
493 vendor_key_in IN VARCHAR2,
494 newstatus_in IN NUMBER,
495 oldstatus_in IN NUMBER,
496 batchstate_in IN NUMBER,
497 settlement_date_in IN DATE,
498 req_type_in IN VARCHAR2,
499 instr_type_in IN iby_batches_all.
500 instrument_type%TYPE,
501 br_disputed_flag_in IN iby_batches_all.
502 br_disputed_flag%TYPE,
503 f_pmt_channel_in IN iby_trxn_summaries_all.
504 payment_channel_code%TYPE,
505 f_curr_in IN iby_trxn_summaries_all.
506 currencynamecode%TYPE,
507 f_settle_date IN iby_trxn_summaries_all.
508 settledate%TYPE,
509 f_due_date IN iby_trxn_summaries_all.
510 settlement_due_date%TYPE,
511 f_maturity_date IN iby_trxn_summaries_all.
512 br_maturity_date%TYPE,
513 f_instr_type IN iby_trxn_summaries_all.
514 instrtype%TYPE,
515 numtrxns_out OUT NOCOPY NUMBER,
516 mbatch_ids_out OUT NOCOPY JTF_NUMBER_TABLE,
517 batch_ids_out OUT NOCOPY JTF_VARCHAR2_TABLE_100
518 )
519 IS
520
521 numrows NUMBER;
522 l_mpayeeid iby_payee.mpayeeid%type;
523 l_mbatchid iby_batches_all.mbatchid%type;
524 l_module_name CONSTANT VARCHAR2(200) :=
525 G_PKG_NAME || '.createBatchCloseTrxnsNew';
526
527 l_batches_tab IBY_TRANSACTIONCC_PKG.batchAttrTabType;
528 l_trxns_in_batch_tab IBY_TRANSACTIONCC_PKG.trxnsInBatchTabType;
529 numProfCodes NUMBER;
530 strProfCodes VARCHAR2(200);
531 l_cursor_stmt VARCHAR2(1000);
532 TYPE dyn_transactions IS REF CURSOR;
533 l_trxn_cursor dyn_transactions;
534
535 l_index NUMBER;
536
537 BEGIN
538
539 print_debuginfo(l_module_name, 'ENTER');
540
541 /* Form a comma separated string for the bepkeys */
542 numProfCodes := profile_code_array.count;
543 FOR i IN 1..(numProfCodes-1) LOOP
544 strProfCodes := strProfCodes||''''||profile_code_array(i)||''',';
545 END LOOP;
546 /* Append the last profile code without comma at the end */
547 strProfCodes := strProfCodes||''''||profile_code_array(numProfCodes)||'''';
548
549 mbatch_ids_out := JTF_NUMBER_TABLE();
550 batch_ids_out := JTF_VARCHAR2_TABLE_100();
551
552 /*
553 * BEP and vendor related params.
554 */
555 print_debuginfo(l_module_name, 'vendor_id_in: '
556 || vendor_id_in);
557 print_debuginfo(l_module_name, 'vendor_key_in: '
558 || vendor_key_in);
559 print_debuginfo(l_module_name, 'merchant_id_in: '
560 || merchant_id_in);
561 print_debuginfo(l_module_name, 'req_type_in: '
562 || req_type_in);
563 print_debuginfo(l_module_name, 'profile codes (as comma separated string): '
564 || strProfCodes);
565 print_debuginfo(l_module_name, 'settlement_date_in: '
566 || settlement_date_in);
567 print_debuginfo(l_module_name, 'oldstatus_in: '
568 || oldstatus_in);
569 /*
570 * We won't be using this cursor. Instead we will be using the reference
571 * cursor written below. The cursor fetches the transactions based on the
572 * profile codes rather than bepkey.
573 */
574
575 /* SELECT
576 COUNT(*)
577 INTO
578 numtrxns_out
579 FROM
580 iby_trxn_summaries_all
581 WHERE
582 status = oldstatus_in AND
583 payeeid = merchant_id_in AND
584 bepid = vendor_id_in AND
585 bepkey = vendor_key_in AND
586 batchid IS NULL AND
587 trunc(nvl(settledate, sysdate)) <= trunc(nvl(settlement_date_in, sysdate-1)) AND
588 instrtype = 'BANKACCOUNT';
589 */
590
591 l_cursor_stmt := ' SELECT COUNT(*) FROM '||
592 ' iby_trxn_summaries_all WHERE '||
593 ' status = '||oldstatus_in||' AND '||
594 ' payeeid = '''||merchant_id_in||''' AND '||
595 ' bepid = '||vendor_id_in||' AND '||
596 ' process_profile_code IN ('||strProfCodes||') AND '||
597 ' batchid IS NULL AND '||
598 ' trunc(nvl(settledate, sysdate)) <= trunc(nvl(to_date('''||settlement_date_in||'''), sysdate-1)) AND '||
599 ' instrtype = ''BANKACCOUNT'' '
600 ;
601
602 OPEN l_trxn_cursor FOR l_cursor_stmt;
603 FETCH l_trxn_cursor INTO numtrxns_out;
604 CLOSE l_trxn_cursor;
605 /*
606 * If there isn't any open transactions, then exit.
607 */
608 IF (numtrxns_out > 0) THEN
609
610 print_debuginfo(l_module_name, 'Invoking grouping ..');
611
612 /*
613 * Group all the transactions for this profile into
614 * batches as per the grouping attributes on the profile.
615 */
616 IBY_TRANSACTIONCC_PKG.performTransactionGrouping(
617 profile_code_array,
618 instr_type_in,
619 req_type_in,
620 f_pmt_channel_in,
621 f_curr_in,
622 f_settle_date,
623 f_due_date,
624 f_maturity_date,
625 f_instr_type,
626 l_batches_tab,
627 l_trxns_in_batch_tab
628 );
629
630 print_debuginfo(l_module_name, '# batches created: '
631 || l_batches_tab.COUNT);
632
633 print_debuginfo(l_module_name, '# transactions processed: '
634 || l_trxns_in_batch_tab.COUNT);
635
636 /*
637 * After grouping it is possible that multiple batches were
638 * created. Each batch will be a separate row in the
639 * IBY_BATCHES_ALL table with a unique mbatchid.
640 *
641 * The user may have provided a batch id (batch prefix), we will
642 * have to assign that batch id to each of the created batches.
643 *
644 * This batch id would be sent to the payment system. It therefore
645 * has to be unique. Therefore, we add a suffix to the user
646 * provided batch id to ensure that batches created after grouping
647 * have a unique batch id.
648 */
649 IF (l_batches_tab.COUNT > 0) THEN
650
651 l_index := 1;
652 FOR k IN l_batches_tab.FIRST .. l_batches_tab.LAST LOOP
653
654 /*
655 * Assign a unique batch id to each batch.
656 */
657 l_batches_tab(k).batch_id :=
658 merch_batchid_in ||'_'|| l_index;
659 l_index := l_index + 1;
660
661 END LOOP;
662
663 END IF;
664
665 /*
666 * After grouping, the transactions will be assigned a mbatch id.
667 * Assign them a batch id as well (based on the batch id
668 * corresponding to each mbatch id).
669 */
670 IF (l_trxns_in_batch_tab.COUNT > 0) THEN
671
672 FOR m IN l_trxns_in_batch_tab.FIRST ..
673 l_trxns_in_batch_tab.LAST LOOP
674
675 FOR k IN l_batches_tab.FIRST .. l_batches_tab.LAST LOOP
676
677 /*
678 * Find the mbatch id in the batches array
679 * corresponding to the mbatchid of this transaction.
680 */
681 IF (l_trxns_in_batch_tab(m).mbatch_id =
682 l_batches_tab(k).mbatch_id) THEN
683
684 /*
685 * Assign the batch id from the batches array
686 * to this transaction.
687 */
688 l_trxns_in_batch_tab(m).batch_id :=
689 l_batches_tab(k).batch_id;
690
691 END IF;
692
693 END LOOP;
694
695 END LOOP;
696
697 END IF;
698
699
700 /*
701
702 SELECT
703 iby_batches_s.NEXTVAL
704 INTO
705 l_mbatchid
706 FROM
707 DUAL;
708 */
709
710 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
711
712 IF (l_batches_tab.COUNT <> 0) THEN
713
714 FOR i IN l_batches_tab.FIRST .. l_batches_tab.LAST LOOP
715
716 print_debuginfo(l_module_name, 'Going to insert batch '
717 || l_batches_tab(i).mbatch_id);
718 /*
719 * Modified to insert null values for bepkey, currency and
720 * profile code columns since these could have multiple values
721 * for a batch.
722 */
723 INSERT INTO iby_batches_all
724 (MBATCHID,
725 BATCHID,
726 MPAYEEID,
727 PAYEEID,
728 BEPID,
729 BEPKEY,
730 BATCHSTATUS,
731 BATCHSTATEID,
732 BATCHCLOSEDATE,
733 REQTYPE,
734 REQDATE,
735 LAST_UPDATE_DATE,
736 LAST_UPDATED_BY,
737 CREATION_DATE,
738 CREATED_BY,
739 LAST_UPDATE_LOGIN,
740 OBJECT_VERSION_NUMBER,
741 SENTCOUNTER,
742 SENTCOUNTERDAILY,
743 PROCESS_PROFILE_CODE,
744 INSTRUMENT_TYPE,
745 BR_DISPUTED_FLAG,
746 CURRENCYNAMECODE,
747 PAYEEINSTRID,
748 LEGAL_ENTITY_ID,
749 ORG_ID,
750 ORG_TYPE,
751 SETTLEDATE
752 )
753 VALUES
754 (
755 l_batches_tab(i).mbatch_id,
756 merch_batchid_in || '_' || i,
757 l_mpayeeid,
758 merchant_id_in,
759 vendor_id_in,
760 null, -- l_batches_tab(i).bep_key
761 batchstate_in,
762 batchstate_in,
763 settlement_date_in,
764 req_type_in,
765 sysdate,
766 sysdate,
767 fnd_global.user_id,
768 sysdate,
769 fnd_global.user_id,
770 fnd_global.login_id,
771 0,
772 0,
773 0,
774 --l_batches_tab(i).profile_code
775 profile_code_array(1),
776 instr_type_in,
777 br_disputed_flag_in,
778
779 /*
780 * Fix for bug 5614670:
781 *
782 * Populate the batch related attributes
783 * created after grouping in this
784 * insert.
785 */
786 null, --l_batches_tab(i).curr_code
787 l_batches_tab(i).int_bank_acct_id,
788 l_batches_tab(i).le_id,
789 l_batches_tab(i).org_id,
790 l_batches_tab(i).org_type,
791 l_batches_tab(i).settle_date
792 );
793
794 /*
795 * Store the created mbatchids in the output param
796 * to return to the caller.
797 */
798 mbatch_ids_out.EXTEND;
799 mbatch_ids_out(i) := l_batches_tab(i).mbatch_id;
800
801 /*
802 * Store the created batchids in the output param
803 * to return to the caller.
804 */
805 batch_ids_out.EXTEND;
806 batch_ids_out(i) := l_batches_tab(i).batch_id;
807
808 print_debuginfo(l_module_name, 'Finished insert batch '
809 || l_batches_tab(i).mbatch_id);
810
811 END LOOP;
812
813 END IF; -- if l_batches_tab.COUNT <> 0
814
815 IF (l_trxns_in_batch_tab.COUNT <> 0) THEN
816
817 FOR i IN l_trxns_in_batch_tab.FIRST .. l_trxns_in_batch_tab.LAST LOOP
818
819 print_debuginfo(l_module_name, 'Going to update transaction '
820 || l_trxns_in_batch_tab(i).trxn_id);
821
822 UPDATE
823 IBY_TRXN_SUMMARIES_ALL
824 SET
825 status = newstatus_in,
826 batchid = l_trxns_in_batch_tab(i).batch_id,
827 mbatchid = l_trxns_in_batch_tab(i).mbatch_id,
828 last_update_date = sysdate,
829 updatedate = sysdate,
830 last_updated_by = fnd_global.user_id,
831 object_version_number = object_version_number + 1
832 WHERE
833 transactionid = l_trxns_in_batch_tab(i).trxn_id AND
834 status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED
835 ;
836
837 print_debuginfo(l_module_name, 'Finished updating transaction'
838 || l_trxns_in_batch_tab(i).trxn_id);
839
840 END LOOP;
841
842 END IF; -- if l_trxns_in_batch_tab.COUNT <> 0
843
844 COMMIT;
845
846 ELSE
847
848 print_debuginfo(l_module_name, 'No open transactions; Exiting ..');
849
850 END IF;
851
852 print_debuginfo(l_module_name, 'mbatchids count: '
853 || mbatch_ids_out.COUNT);
854
855 print_debuginfo(l_module_name, 'EXIT');
856
857 END createBatchCloseTrxnsNew;
858
859
860 /*Update the batch and transactions status and other infomations based on the
861 payeeid and batchid */
862 PROCEDURE updateBatchCloseTrxns(
863 merch_batchid_in IN VARCHAR2,
864 merchant_id_in IN VARCHAR2,
865 newstatus_in IN NUMBER,
866 batchstate_in IN NUMBER,
867 numtrxns_in IN NUMBER,
868 batchtotal_in IN NUMBER DEFAULT null,
869 salestotal_in IN NUMBER DEFAULT null,
870 credittotal_in IN NUMBER DEFAULT null,
871 time_in IN DATE,
872 vendor_code_in IN VARCHAR2,
873 vendor_message_in IN VARCHAR2
874 )
875 IS
876
877 BEGIN
878 -- reset the OBJECT VERSION NUMBER, since we not using it
879 -- the purpose of this, it is to keep track the EFT batchseq number
880
881 UPDATE iby_batches_all SET
882 SENTCOUNTERDAILY = 0
883 WHERE batchid = merch_batchid_in
884 AND payeeid = merchant_id_in
885 AND trunc(LAST_UPDATE_DATE) < trunc(sysdate);
886
887
888 UPDATE iby_batches_all SET
889 BATCHSTATUS = batchstate_in,
890 BATCHSTATEID = batchstate_in,
891 NUMTRXNS = numtrxns_in,
892
893 --
894 -- only change these values if the incoming values are
895 -- non-trivial
896 --
897 BATCHTOTAL = DECODE(NVL(batchtotal_in,''),'',batchtotal,batchtotal_in),
898 BATCHSALES = DECODE(NVL(salestotal_in,''),'',batchsales,salestotal_in),
899 BATCHCREDIT = DECODE(NVL(credittotal_in,''),'',batchcredit,credittotal_in),
900
901 BATCHCLOSEDATE = time_in,
902 BEPCODE = vendor_code_in,
903 BEPMESSAGE = vendor_message_in,
904 LAST_UPDATE_DATE = sysdate,
905 LAST_UPDATED_BY = fnd_global.user_id,
906 OBJECT_VERSION_NUMBER = Object_Version_Number + 1,
907 SENTCOUNTER = SENTCOUNTER + 1,
908 SENTCOUNTERDAILY = SENTCOUNTERDAILY + 1
909 WHERE batchid = merch_batchid_in
910 AND payeeid = merchant_id_in;
911
912 UPDATE iby_trxn_summaries_all
913 SET
914 STATUS = newstatus_in,
915 BEPCODE = vendor_code_in,
916 BEPMESSAGE = vendor_message_in,
917 LAST_UPDATE_DATE = sysdate,
918 UPDATEDATE = sysdate,
919 LAST_UPDATED_BY = fnd_global.user_id,
920 OBJECT_VERSION_NUMBER = object_version_number + 1
921 WHERE
922 -- 109 means STATUS_BATCH_TRANSITIONAL, 101 means STATUS_BATCH_COMM_ERROR, 120 means STATUS_BATCH_MAX_EXCEEDED
923 status in (109, 101, 120)
924 AND
925 batchid = merch_batchid_in
926 AND
927 payeeid = merchant_id_in
928 AND
929 instrtype = 'BANKACCOUNT';
930
931 COMMIT;
932
933 END updateBatchCloseTrxns;
934
935
936 /*Update the transactions status and other informations by passed the data in as array.*/
937 procedure updateTrxnResultStatus(i_merch_batchid IN VARCHAR2,
938 i_merchant_id IN VARCHAR2,
939 i_status_arr IN JTF_NUMBER_TABLE,
940 i_errCode_arr IN JTF_VARCHAR2_TABLE_100,
941 i_errMsg_arr IN JTF_VARCHAR2_TABLE_300,
942 i_tangibleId_arr IN JTF_VARCHAR2_TABLE_100,
943 o_status_arr OUT NOCOPY JTF_NUMBER_TABLE,
944 o_error_code OUT NOCOPY NUMBER,
945 o_error_msg OUT NOCOPY VARCHAR2
946 )
947
948 IS
949
950 l_index INTEGER;
951 c_FAIL NUMBER := -1;
952 c_SUCCESS NUMBER := 0;
953
954 BEGIN
955
956 o_status_arr := JTF_NUMBER_TABLE();
957 o_status_arr.extend( i_tangibleId_arr.count );
958
959 o_error_code := 0;
960
961 l_index := i_tangibleId_arr.first;
962
963 WHILE (TRUE) LOOP
964
965 o_status_arr( l_index ) := c_SUCCESS;
966
967 BEGIN -- Nested block begins
968
969 UPDATE iby_trxn_summaries_all
970 SET STATUS = i_status_arr( l_index ),
971 BEPCODE = i_errCode_arr( l_index ),
972 BEPMESSAGE = i_errMsg_arr( l_index ),
973 LAST_UPDATE_DATE = sysdate,
974 UPDATEDATE = sysdate,
975 LAST_UPDATED_BY = fnd_global.user_id
976 WHERE TANGIBLEID = i_tangibleId_arr( l_index )
977 AND batchid = i_merch_batchid
978 AND payeeid = i_merchant_id;
979
980 IF ( SQL%NOTFOUND ) THEN
981 o_status_arr( l_index ) := c_FAIL;
982 ROLLBACK;
983 ELSE
984 COMMIT;
985 END IF;
986
987 EXCEPTION
988 WHEN OTHERS THEN
989 o_status_arr( l_index ) := c_FAIL;
990 o_error_code := SQLCODE;
991 o_error_msg := SUBSTR(SQLERRM, 1, 200);
992
993 END; -- Nested block ends
994
995 EXIT WHEN ( i_tangibleId_arr.last = l_index );
996
997 l_index := i_tangibleId_arr.next( l_index );
998
999 END LOOP; --end of while loop
1000
1001 END updateTrxnResultStatus;
1002
1003 PROCEDURE insertEFTBatchTrxns(
1004 i_ecappid IN iby_trxn_summaries_all.ecappid%TYPE,
1005 i_payeeid IN iby_trxn_summaries_all.payeeid%TYPE,
1006 i_ecbatchid IN iby_trxn_summaries_all.ecbatchid%TYPE,
1007 i_bepid IN iby_trxn_summaries_all.bepid%TYPE,
1008 i_bepkey IN iby_trxn_summaries_all.bepkey%TYPE,
1009 i_pmtmethod IN iby_trxn_summaries_all.paymentmethodname%TYPE,
1010 i_reqtype IN iby_trxn_summaries_all.reqtype%TYPE,
1011 i_reqdate IN iby_trxn_summaries_all.reqdate%TYPE,
1012 i_payeeinstrid IN iby_trxn_summaries_all.payeeinstrid%TYPE,
1013 i_orgid IN iby_trxn_summaries_all.org_id%TYPE,
1014
1015 i_payerinstrid IN JTF_NUMBER_TABLE,
1016 i_amount IN JTF_NUMBER_TABLE,
1017 i_payerid IN JTF_VARCHAR2_TABLE_100,
1018 i_tangibleid IN JTF_VARCHAR2_TABLE_100,
1019 i_currency IN JTF_VARCHAR2_TABLE_100,
1020 i_refinfo IN JTF_VARCHAR2_TABLE_100,
1021 i_memo IN JTF_VARCHAR2_TABLE_100,
1022 i_ordermedium IN JTF_VARCHAR2_TABLE_100,
1023 i_eftauthmethod IN JTF_VARCHAR2_TABLE_100,
1024 i_instrsubtype IN JTF_VARCHAR2_TABLE_100,
1025 i_settledate IN JTF_DATE_TABLE,
1026 i_issuedate IN JTF_DATE_TABLE,
1027 i_customerref IN JTF_VARCHAR2_TABLE_100,
1028 o_trxnId OUT NOCOPY JTF_NUMBER_TABLE
1029 )
1030 IS
1031 l_mtangibleid JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1032 l_trxnmid JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1033 l_mpayeeid iby_payee.mpayeeid%TYPE;
1034 l_count NUMBER;
1035
1036 CURSOR c_trxnmid IS
1037 SELECT iby_trxnsumm_mid_s.nextval
1038 FROM DUAL;
1039
1040 CURSOR c_mtangibleid IS
1041 SELECT iby_tangible_s.nextval
1042 FROM DUAL;
1043
1044 BEGIN
1045 /**
1046 * Check if this EC batch is already been submitted
1047 * by the EC application.
1048 */
1049 SELECT count(*) INTO l_count
1050 FROM iby_trxn_summaries_all
1051 WHERE ecbatchid = i_ecbatchid
1052 AND ecappid = i_ecappid
1053 AND payeeid = i_payeeid;
1054
1055 IF(l_count > 0) THEN
1056 raise_application_error(-20000, 'IBY_20560#', FALSE);
1057 END IF;
1058
1059 iby_fipayments_pkg.checkInstrId(i_payeeinstrid);
1060
1061 IF (c_trxnmid%ISOPEN) THEN
1062 CLOSE c_trxnmid;
1063 END IF;
1064
1065 IF (c_mtangibleid%ISOPEN) THEN
1066 CLOSE c_mtangibleid;
1067 END IF;
1068
1069 l_mtangibleid.EXTEND(i_tangibleid.COUNT);
1070 l_trxnmid.EXTEND(i_tangibleid.COUNT);
1071
1072 o_trxnid := JTF_NUMBER_TABLE();
1073 o_trxnid.EXTEND(i_tangibleid.COUNT);
1074
1075 /**
1076 * Obtain the master payeeid for the given payee.
1077 */
1078 iby_accppmtmthd_pkg.getMPayeeId(i_payeeid, l_mpayeeid);
1079
1080 FOR j IN i_tangibleid.FIRST..i_tangibleid.LAST
1081 LOOP
1082 /**
1083 * Check if the payer has registered the instrument.
1084 */
1085 iby_fipayments_pkg.checkInstrId(i_payerinstrid(j));
1086
1087 /**
1088 * Check if there is already a request with same payee id
1089 * tangible id and request type.
1090 */
1091 IF (iby_fipayments_pkg.requestExists(i_payeeid, i_tangibleid(j), NULL, i_reqtype)) THEN
1092 raise_application_error(-20000, 'IBY_20560#', FALSE);
1093 END IF;
1094
1095 o_trxnid(j) := iby_transactioncc_pkg.getTID(i_payeeid, i_tangibleid(j));
1096
1097 OPEN c_trxnmid;
1098 FETCH c_trxnmid INTO l_trxnmid(j);
1099 CLOSE c_trxnmid;
1100
1101 OPEN c_mtangibleid;
1102 FETCH c_mtangibleid INTO l_mtangibleid(j);
1103 CLOSE c_mtangibleid;
1104
1105 END LOOP;
1106
1107 /**
1108 * Create tangible records in iby_tangible.
1109 */
1110 FOR j IN i_tangibleid.FIRST..i_tangibleid.LAST LOOP
1111
1112 /**
1113 * Check for duplicate tangible ids
1114 */
1115 select count(*) into l_count
1116 from iby_trxn_summaries_all s
1117 where payeeId = i_payeeId
1118 and tangibleid = i_tangibleId(j)
1119 and UPPER(reqType) = UPPER(i_reqType);
1120
1121 IF (l_count=0) THEN
1122
1123 INSERT INTO iby_tangible
1124 (
1125 mtangibleId, tangibleid, amount,
1126 currencyNameCode, refinfo, memo, issuedate,
1127 order_medium, eft_auth_method,
1128 last_update_date, last_updated_by,
1129 creation_date, created_by,
1130 last_update_login, object_version_number
1131 )
1132 VALUES
1133 (
1134 l_mtangibleid(j), i_tangibleid(j), i_amount(j),
1135 i_currency(j), i_refinfo(j), i_memo(j), i_issuedate(j),
1136 i_ordermedium(j), i_eftauthmethod(j),
1137 sysdate, fnd_global.user_id,
1138 sysdate, fnd_global.user_id,
1139 fnd_global.login_id, 1
1140 );
1141
1142 /**
1143 * Create transaction records in iby_trxn_summaries_all.
1144 */
1145 INSERT INTO iby_trxn_summaries_all
1146 (
1147 org_id, ecappid, mpayeeid, payeeid,
1148 bepid, bepkey, paymentMethodname,
1149 ecbatchid, trxnmid, transactionid, mtangibleId,
1150 tangibleid, payeeinstrid, payerid, payerinstrid,
1151 amount, currencyNameCode, reqdate,
1152 reqtype, status, settledate, instrtype, instrsubtype,
1153 settlement_customer_reference,
1154 last_update_date, updatedate, last_updated_by,
1155 creation_date, created_by,
1156 last_update_login, object_version_number,needsupdt
1157 )
1158 VALUES
1159 (
1160 i_orgid, i_ecappid, l_mpayeeid, i_payeeid,
1161 i_bepid, i_bepkey, i_pmtmethod, i_ecbatchid,
1162 l_trxnmid(j), o_trxnid(j), l_mtangibleid(j),
1163 i_tangibleid(j), i_payeeinstrid, i_payerid(j),
1164 i_payerinstrid(j), i_amount(j), i_currency(j),
1165 i_reqdate, i_reqtype, 100, i_settledate(j),
1166 'BANKACCOUNT', i_instrsubtype(j),
1167 i_customerref(j),
1168 sysdate, sysdate, fnd_global.user_id,
1169 sysdate, fnd_global.user_id,
1170 fnd_global.login_id, 1,'Y'
1171 );
1172
1173 ELSIF (l_count=1) THEN
1174
1175 /* If a duplicate request exists
1176 * it has to be in a failed status, as 'requestExists' has already
1177 * been checked, hence updating the duplicate failed request.
1178 */
1179
1180 UPDATE iby_tangible
1181 set mtangibleId = l_mtangibleid(j),
1182 amount = i_amount(j),
1183 currencyNameCode = i_currency(j),
1184 refinfo = i_refinfo(j),
1185 memo = i_memo(j),
1186 order_medium = i_ordermedium(j),
1187 eft_auth_method = i_eftauthmethod(j),
1188 issuedate = i_issuedate(j),
1189 last_update_date = sysdate,
1190 last_updated_by = fnd_global.user_id,
1191 creation_date = sysdate,
1192 created_by = fnd_global.user_id,
1193 last_update_login= fnd_global.login_id,
1194 object_version_number = 1
1195 where tangibleid = i_tangibleid(j);
1196
1197 UPDATE iby_trxn_summaries_all
1198 set org_id = i_orgid,
1199 ecappid = i_ecappid,
1200 mpayeeid = l_mpayeeid,
1201 payeeid = i_payeeid,
1202 bepid = i_bepid,
1203 bepkey = i_bepkey,
1204 paymentMethodname = i_pmtmethod,
1205 ecbatchid = i_ecbatchid,
1206 trxnmid = l_trxnmid(j),
1207 transactionid = o_trxnid(j),
1208 mtangibleId = l_mtangibleid(j),
1209 payeeinstrid = i_payeeinstrid,
1210 payerid = i_payerid(j),
1211 payerinstrid = i_payerinstrid(j),
1212 amount = i_amount(j),
1213 currencyNameCode = i_currency(j),
1214 reqdate = i_reqdate,
1215 reqtype = i_reqtype,
1216 status = 100,
1217 settledate = i_settledate(j),
1218 instrtype = 'BANKACCOUNT',
1219 instrsubtype = i_instrsubtype(j),
1220 settlement_customer_reference
1221 = i_customerref(j),
1222 bepcode = null,
1223 bepmessage = null,
1224 batchid = null,
1225 mbatchid = null,
1226 errorlocation = null,
1227 last_update_date = sysdate,
1228 updatedate = sysdate,
1229 last_updated_by = fnd_global.user_id,
1230 creation_date = sysdate,
1231 created_by = fnd_global.user_id,
1232 last_update_login = fnd_global.user_id,
1233 object_version_number = 1
1234
1235 where tangibleid = i_tangibleid(j);
1236 ELSE
1237 raise_application_error(-20000, 'IBY_20560#', FALSE);
1238
1239 END IF;
1240
1241 END LOOP;
1242
1243 COMMIT;
1244 END insertEFTBatchTrxns;
1245
1246 -------------------------------------------------------------------------
1247 -- This procedure inserts or update the verify transaction data into the
1248 -- database.
1249 -------------------------------------------------------------------------
1250
1251 PROCEDURE createEFTVerifyTrxn(
1252 i_ecappid IN iby_trxn_summaries_all.ecappid%TYPE,
1253 i_reqtype IN iby_trxn_summaries_all.reqtype%TYPE,
1254 i_bepid IN iby_trxn_summaries_all.bepid%TYPE,
1255 i_bepkey IN iby_trxn_summaries_all.bepkey%TYPE,
1256 i_payeeid IN iby_trxn_summaries_all.payeeid%TYPE,
1257 i_payeeinstrid IN iby_trxn_summaries_all.payeeinstrid%TYPE,
1258 i_tangibleid IN iby_trxn_summaries_all.tangibleid%TYPE,
1259 i_amount IN iby_trxn_summaries_all.amount%TYPE,
1260 i_currency IN iby_trxn_summaries_all.currencynamecode%TYPE,
1261 i_status IN iby_trxn_summaries_all.status%TYPE,
1262 i_refinfo IN iby_tangible.refinfo%TYPE,
1263 i_memo IN iby_tangible.memo%TYPE,
1264 i_acctno IN iby_tangible.acctno%TYPE,
1265 i_ordermedium IN iby_tangible.order_medium%TYPE,
1266 i_eftauthmethod IN iby_tangible.eft_auth_method%TYPE,
1267 i_orgid IN iby_trxn_summaries_all.org_id%TYPE,
1268 i_pmtmethod IN iby_trxn_summaries_all.paymentmethodname%TYPE,
1269 i_payerid IN iby_trxn_summaries_all.payerid%TYPE,
1270 i_instrtype IN iby_trxn_summaries_all.instrtype%TYPE,
1271 i_instrsubtype IN iby_trxn_summaries_all.instrsubtype%TYPE,
1272 i_payerinstrid IN iby_trxn_summaries_all.payerinstrid%TYPE,
1273 i_trxndate IN iby_trxn_summaries_all.updatedate%TYPE,
1274 i_trxntypeid IN iby_trxn_summaries_all.TrxntypeID%TYPE,
1275 i_bepcode IN iby_trxn_summaries_all.BEPCode%TYPE,
1276 i_bepmessage IN iby_trxn_summaries_all.BEPMessage%TYPE,
1277 i_errorlocation IN iby_trxn_summaries_all.errorlocation%TYPE,
1278 i_referenceCode IN iby_trxn_summaries_all.proc_reference_code%TYPE,
1279 o_trxnid OUT NOCOPY iby_trxn_summaries_all.transactionid%TYPE,
1280 i_orgtype IN iby_trxn_summaries_all.org_type%TYPE,
1281 i_pmtchannelcode IN iby_trxn_summaries_all.payment_channel_code%TYPE,
1282 i_factoredflag IN iby_trxn_summaries_all.factored_flag%TYPE,
1283 i_pmtinstrassignmentId IN iby_trxn_summaries_all.payer_instr_assignment_id%TYPE,
1284 i_process_profile_code IN iby_trxn_summaries_all.process_profile_code%TYPE,
1285 o_trxnmid OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE
1286 ) IS
1287
1288 l_mtangibleid iby_trxn_summaries_all.mtangibleid%TYPE;
1289 l_trxnmid iby_trxn_summaries_all.transactionid%TYPE;
1290
1291 -- new parameters for eft authorizations
1292 l_debit_auth_flag iby_trxn_summaries_all.debit_auth_flag%TYPE;
1293 l_debit_auth_method iby_trxn_summaries_all.debit_auth_method%TYPE;
1294 l_debit_auth_reference iby_trxn_summaries_all.debit_auth_reference%TYPE;
1295 l_payer_party_id iby_trxn_summaries_all.payer_party_id%TYPE;
1296 l_mpayeeid iby_payee.mpayeeid%TYPE;
1297 l_trxn_exists VARCHAR2(1);
1298
1299 l_payer_notif_flag iby_trxn_summaries_all.payer_notification_required%TYPE;
1300 l_bep_type iby_bepinfo.bep_type%TYPE;
1301
1302 CURSOR trxn_exists IS
1303 SELECT 'Y', trxnmid, mtangibleid
1304 FROM iby_trxn_summaries_all s
1305 WHERE payeeId = i_payeeId
1306 AND tangibleid = i_tangibleid
1307 AND UPPER(reqType) = UPPER(i_reqType)
1308 AND status <> '0'
1309 ORDER BY trxnmid desc;
1310 -- It will update the same transaction if not successfull
1311 -- of the same request type
1312
1313 CURSOR c_payer_notif_eft (i_user_fcpp_code iby_trxn_summaries_all.process_profile_code%TYPE) IS
1314 SELECT DECODE(payer_notification_format, null, 'N', 'Y')
1315 FROM iby_fndcpt_user_eft_pf_b up, iby_fndcpt_sys_eft_pf_b sp
1316 WHERE up.sys_eft_profile_code = sp.sys_eft_profile_code
1317 AND up.user_eft_profile_code = i_user_fcpp_code;
1318
1319 BEGIN
1320
1321 -- Check if payer has registered the instrument
1322
1323 IF ( NVL(i_payerinstrid,0) <> 0) THEN
1324 iby_fipayments_pkg.checkInstrId(i_payerinstrid);
1325 END IF;
1326
1327 -- Get the master payeeid for the given payee
1328 iby_accppmtmthd_pkg.getMPayeeId(i_payeeid, l_mpayeeid);
1329
1330 -- this function returns the existing transactionid from the iby_trxn_summaries_all
1331 -- table if one exist for the payeeid and tangibleid, or a new one from the DB
1332 -- sequence if none exists.
1333 o_trxnid := iby_transactioncc_pkg.getTID(i_payeeid, i_tangibleid);
1334
1335 -- Verify if transaction already exist and aget l_trxnmid so that the
1336 -- update is done using the PK
1337
1338 OPEN trxn_exists;
1339 FETCH trxn_exists INTO l_trxn_exists, l_trxnmid, l_mtangibleid;
1340 CLOSE trxn_exists;
1341
1342
1343 -- get the debit authrization values
1344 begin
1345 IF (i_pmtinstrassignmentId>0) then
1346 select debit_auth_flag,
1347 debit_auth_method,
1348 debit_auth_reference
1349 into l_debit_auth_flag,
1350 l_debit_auth_method,
1351 l_debit_auth_reference
1352 from iby_pmt_instr_uses_all
1353 where instrument_payment_use_id=i_pmtinstrassignmentId;
1354 END IF;
1355 exception
1356 WHEN NO_DATA_FOUND THEN
1357 null;
1358 end;
1359
1360
1361 -- get the payer party id
1362 -- get the payer_party_id if exists
1363 begin
1364 if(i_payerid is not NULL) then
1365 l_payer_party_id :=to_number(i_payerid);
1366 end if;
1367 exception
1368 when others then
1369 l_payer_party_id :=null;
1370 end;
1371
1372 -- get bep_type info
1373 BEGIN
1374 SELECT bep_type
1375 INTO l_bep_type
1376 FROM iby_bepinfo
1377 WHERE bepid = i_bepid;
1378 EXCEPTION
1379 WHEN others THEN NULL;
1380 END;
1381
1382 -- get payer notification flag for capture transactions
1383 -- for Gateway
1384 -- for processor transactions the flag is set during batch close
1385 -- only BANKACCOUNTS
1386 IF (i_trxntypeid IN (3,8,9,100) AND
1387 l_bep_type = 'GATEWAY') THEN
1388 -- only BANKACCOUNTS
1389 OPEN c_payer_notif_eft(i_process_profile_code);
1390 FETCH c_payer_notif_eft INTO l_payer_notif_flag;
1391 CLOSE c_payer_notif_eft;
1392
1393 END IF;
1394
1395 IF (NVL(l_trxn_exists, 'N') = 'N') THEN
1396 --Create an entry in iby_tangible table
1397 iby_bill_pkg.createBill(
1398 i_tangibleid, -- IN i_billId
1399 i_amount, -- IN i_billAmount
1400 i_currency, -- IN i_billCurDef
1401 i_acctno, -- IN i_billAcct
1402 i_refinfo, -- IN i_billRefInfo
1403 i_memo, -- IN i_billMemo
1404 i_ordermedium, -- IN i_billOrderMedium
1405 i_eftauthmethod, -- IN i_billEftAuthMethod
1406 l_mtangibleid); -- OUT io_mtangibleid
1407
1408
1409 -- Create transaction records in iby_trxn_summaries_all.
1410 INSERT INTO iby_trxn_summaries_all(
1411 trxnmid,
1412 org_id,
1413 ecappid,
1414 mpayeeid,
1415 payeeid,
1416 bepid,
1417 bepkey,
1418 paymentMethodname,
1419 transactionid,
1420 mtangibleId,
1421 tangibleid,
1422 payeeinstrid,
1423 payerid,
1424 payerinstrid,
1425 amount,
1426 currencyNameCode,
1427 reqtype,
1428 status,
1429 settledate,
1430 instrtype,
1431 instrsubtype,
1432 last_update_date,
1433 reqdate,
1434 updatedate,
1435 last_updated_by,
1436 creation_date,
1437 created_by,
1438 last_update_login,
1439 object_version_number,
1440 bepcode,
1441 bepmessage,
1442 errorlocation,
1443 trxntypeid,
1444 proc_reference_code,
1445 org_type,
1446 payment_channel_code,
1447 factored_flag,
1448 payer_instr_assignment_id,
1449 process_profile_code,
1450 payer_party_id,
1451 debit_auth_flag,
1452 debit_auth_method,
1453 debit_auth_reference,
1454 payer_notification_required,
1455 needsupdt
1456 ) VALUES (
1457 iby_trxnsumm_mid_s.NEXTVAL, -- trxnmid
1458 i_orgid, -- org_id
1459 i_ecappid, -- ecappid
1460 l_mpayeeid, -- mpayeeid
1461 i_payeeid, -- payeeid
1462 i_bepid, -- bepid
1463 i_bepkey, -- bepkey
1464 i_pmtmethod, -- paymentMethodname
1465 o_trxnid, -- transactionid
1466 l_mtangibleid, -- mtangibleId
1467 i_tangibleid, -- tangibleid
1468 i_payeeinstrid, -- payeeinstrid
1469 i_payerid, -- payerid
1470 i_payerinstrid, -- payerinstrid
1471 i_amount, -- amount
1472 i_currency, -- currencyNameCode
1473 i_reqtype, -- reqtype
1474 i_status, -- status
1475 null, -- settledate
1476 i_instrtype, -- instrtype
1477 i_instrsubtype, -- instrsubtype
1478 sysdate, -- last_update_date
1479 i_trxndate, -- reqdate
1480 i_trxndate, -- updatedate
1481 fnd_global.user_id, -- last_updated_by
1482 sysdate, -- creation_date
1483 fnd_global.user_id, -- created_by
1484 fnd_global.login_id, -- last_update_login
1485 1, -- object_version_number
1486 i_bepcode, -- bepcode
1487 i_bepmessage, -- bepmessage
1488 i_errorlocation, -- errorlocation
1489 i_trxntypeid, -- trxntypeid
1490 i_referencecode, -- reference code
1491 i_orgtype, -- org_type
1492 i_pmtchannelcode, -- payment_channel_code
1493 i_factoredflag, -- factored_flag
1494 i_pmtinstrassignmentId,
1495 i_process_profile_code,
1496 l_payer_party_id,
1497 l_debit_auth_flag,
1498 l_debit_auth_method,
1499 l_debit_auth_reference,
1500 DECODE(i_status, 0, l_payer_notif_flag, 'N'),
1501 'Y'
1502 ) RETURNING trxnmid INTO l_trxnmid;
1503
1504 ELSE
1505 -- A transaction is already created.
1506
1507 -- Update iby_tangible table
1508
1509 iby_bill_pkg.modBill(
1510 l_mtangibleid, -- IN i_mtangibleid
1511 i_tangibleid, -- IN i_billId
1512 i_amount, -- IN i_billAmount
1513 i_currency, -- IN i_billCurDef
1514 i_acctno, -- IN i_billAcct
1515 i_refinfo, -- IN i_billRefInfo
1516 i_memo, -- IN i_billMemo
1517 i_ordermedium, -- IN i_billOrderMedium
1518 i_eftauthmethod); -- IN i_billEftAuthMethod
1519
1520 UPDATE iby_trxn_summaries_all
1521 SET tangibleid = i_tangibleid,
1522 org_id = i_orgid,
1523 ecappid = i_ecappid,
1524 mpayeeid = l_mpayeeid,
1525 payeeid = i_payeeid,
1526 bepid = i_bepid,
1527 bepkey = i_bepkey,
1528 paymentMethodname = i_pmtmethod,
1529 transactionid = o_trxnid,
1530 mtangibleId = l_mtangibleid,
1531 payeeinstrid = i_payeeinstrid,
1532 payerid = i_payerid,
1533 payerinstrid = i_payerinstrid,
1534 amount = i_amount,
1535 currencyNameCode = i_currency,
1536 reqtype = i_reqtype,
1537 status = i_status,
1538 instrtype = i_instrtype,
1539 instrsubtype = i_instrsubtype,
1540 bepcode = i_bepcode,
1541 bepmessage = i_bepmessage,
1542 errorlocation = i_errorlocation,
1543 last_update_date = sysdate,
1544 reqdate = i_trxndate,
1545 updatedate = i_trxndate,
1546 last_updated_by = fnd_global.user_id,
1547 creation_date = sysdate,
1548 created_by = fnd_global.user_id,
1549 last_update_login = fnd_global.user_id,
1550 object_version_number = 1,
1551 -- trxntypeid = i_trxntypeid,
1552 proc_reference_code = i_referencecode,
1553 org_type = i_orgtype,
1554 payment_channel_code = i_pmtchannelcode,
1555 factored_flag = i_factoredflag,
1556 debit_auth_flag = l_debit_auth_flag,
1557 debit_auth_method = l_debit_auth_method,
1558 debit_auth_reference = l_debit_auth_reference,
1559 payer_instr_assignment_id= i_pmtinstrassignmentId,
1560 process_profile_code = i_process_profile_code,
1561 payer_party_id = l_payer_party_id,
1562 payer_notification_required = DECODE(i_status, 0, l_payer_notif_flag, 'N')
1563 WHERE trxnmid = l_trxnmid;
1564
1565
1566 END IF;
1567 o_trxnmid := l_trxnmid;
1568
1569 COMMIT;
1570
1571 EXCEPTION
1572 WHEN OTHERS THEN
1573
1574 IF (trxn_exists%ISOPEN ) THEN
1575 CLOSE trxn_exists;
1576 END IF;
1577 raise_application_error(-20000, 'IBY_20400#', FALSE);
1578
1579 END createEFTVerifyTrxn;
1580
1581 /*--------------------------------------------------------------------
1582 | NAME:
1583 | print_debuginfo
1584 |
1585 | PURPOSE:
1586 | This procedure prints the debug message to the concurrent manager
1587 | log file.
1588 |
1589 | PARAMETERS:
1590 | IN
1591 | p_debug_text - The debug message to be printed
1592 |
1593 | OUT
1594 |
1595 |
1596 | RETURNS:
1597 |
1598 | NOTES:
1599 |
1600 *---------------------------------------------------------------------*/
1601 PROCEDURE print_debuginfo(
1602 p_module IN VARCHAR2,
1603 p_debug_text IN VARCHAR2
1604 )
1605 IS
1606 PRAGMA AUTONOMOUS_TRANSACTION;
1607
1608 BEGIN
1609
1610 /*
1611 * If FND_GLOBAL.conc_request_id is -1, it implies that
1612 * this method has not been invoked via the concurrent
1613 * manager. In that case, write to apps log else write
1614 * to concurrent manager log file.
1615 */
1616 IF (FND_GLOBAL.conc_request_id = -1) THEN
1617
1618 /*
1619 * OPTION I:
1620 * Write debug text to the common application log file.
1621 */
1622 IBY_DEBUG_PUB.add(
1623 substr(RPAD(p_module,55) || ' : ' || p_debug_text, 0, 150),
1624 FND_LOG.G_CURRENT_RUNTIME_LEVEL,
1625 'iby.plsql.IBY_VALIDATIONSETS_PUB'
1626 );
1627
1628 /*
1629 * OPTION II:
1630 * Write debug text to DBMS output file.
1631 */
1632 --DBMS_OUTPUT.PUT_LINE(substr(RPAD(p_module,40)||' : '||
1633 -- p_debug_text, 0, 150));
1634
1635 /*
1636 * OPTION III:
1637 * Write debug text to temporary table.
1638 */
1639 /* uncomment these two lines for debugging */
1640 --INSERT INTO TEMP_IBY_LOGS VALUES (p_module || ': '
1641 -- || p_debug_text, sysdate);
1642
1643 --COMMIT;
1644
1645 ELSE
1646
1647 /*
1648 * OPTION I:
1649 * Write debug text to the concurrent manager log file.
1650 */
1651 FND_FILE.PUT_LINE(FND_FILE.LOG, p_module || ': ' || p_debug_text);
1652
1653 /*
1654 * OPTION II:
1655 * Write debug text to DBMS output file.
1656 */
1657 --DBMS_OUTPUT.PUT_LINE(substr(RPAD(p_module,40)||' : '||
1658 -- p_debug_text, 0, 150));
1659
1660 /*
1661 * OPTION III:
1662 * Write debug text to temporary table.
1663 */
1664 /* uncomment these two lines for debugging */
1665 --INSERT INTO TEMP_IBY_LOGS VALUES (p_module || ': '
1666 -- || p_debug_text, sysdate);
1667
1668 --COMMIT;
1669
1670 END IF;
1671
1672 END print_debuginfo;
1673
1674 END IBY_TRANSACTIONEFT_PKG;
1675