1 package body iby_fipayments_pkg as
2 /*$Header: ibyfipmb.pls 120.17.12010000.3 2008/12/18 00:28:22 svinjamu ship $*/
3
4
5 /*
6 * Procedure: checkInstrId
7 * Purpose: raise exception if instrid doesn't exist
8 *
9 *
10 */
11 procedure checkInstrId(i_instrid in iby_ext_bank_accounts_v.ext_bank_account_id%TYPE)
12 is
13 l_instrid iby_ext_bank_accounts_v.ext_bank_account_id%TYPE;
14
15 cursor c_bk(ci_instrid iby_ext_bank_accounts_v.ext_bank_account_id%TYPE)
16 is
17 SELECT ext_bank_account_id
18 FROM iby_ext_bank_accounts
19 WHERE ext_bank_account_id = ci_instrid;
20
21 begin
22 if ( c_bk%isopen) then
23 close c_bk;
24 end if;
25
26 open c_bk( i_instrid);
27 fetch c_bk into l_instrid;
28
29 if (c_bk%notfound) then
30 raise_application_error(-20000, 'IBY_20512#', FALSE);
31 end if;
32 close c_bk;
33
34 end checkInstrId;
35
36
37 /*
38 ** Procedure: createPayment.
39 ** Purpose: creates a payment for schedule.
40 ** Description: Check whether the payment request id is already sent or
41 ** not. if yes raise an error. Otherwise, check whether
42 ** user holds the specified instrument or not.
43 ** Check whether specified payee id exists or not.
44 ** If all the check are validated then create the
45 ** tangible, payment and request objects. Status of the payment
46 ** is marked as PENDING.
47 ** return the tid of the payments object.
48 */
49 procedure createPayment(i_ecappid in iby_trxn_summaries_all.ecappid%type,
50 i_payeeid in iby_trxn_summaries_all.payeeid%type,
51 i_instrid in iby_trxn_summaries_all.payerinstrid%type,
52 i_payerid in iby_trxn_summaries_all.payerid%type,
53 i_tangibleid in iby_trxn_summaries_all.tangibleid%type,
54 i_billamount in iby_trxn_summaries_all.amount%type,
55 i_billcurDef in iby_trxn_summaries_all.currencynamecode%type,
56 i_billAcct in iby_tangible.acctno%type,
57 i_billRefInfo in iby_tangible.refinfo%type,
58 i_billMemo in iby_tangible.memo%type,
59 i_billOrdermedium in iby_tangible.order_medium%TYPE,
60 i_billEftAuthMethod in iby_tangible.eft_auth_method%TYPE,
61 i_scheddate in iby_trxn_summaries_all.settledate%type,
62 i_reqtype in iby_trxn_summaries_all.reqtype%type,
63 i_reqdate in iby_trxn_summaries_all.reqdate%type,
64 i_bepid in iby_trxn_summaries_all.bepid%type,
65 i_bepkey in iby_trxn_summaries_all.bepkey%type,
66 i_pmtmethod in iby_trxn_summaries_all.paymentmethodname%type,
67 i_psreqId in iby_trxn_fi.psreqid%type,
68 i_nlslang in iby_trxn_summaries_all.nlslang%type,
69 i_splitId in iby_trxn_fi.splitId%type,
70 i_org_id in iby_trxn_summaries_all.org_id%type,
71 i_org_type in iby_trxn_summaries_all.org_type%type,
72 i_bnfcryinstrid in iby_trxn_summaries_all.payeeinstrid%type,
73 i_custacctid in iby_trxn_summaries_all.cust_account_id%type,
74 i_acctsiteid in iby_trxn_summaries_all.acct_site_id%type,
75 i_acctsiteuseid in iby_trxn_summaries_all.acct_site_use_id%type,
76 i_profilecode in iby_trxn_summaries_all.process_profile_code%type,
77 io_tid in out nocopy iby_trxn_summaries_all.transactionid%type)
78 is
79
80 l_mtangibleid iby_tangible.mtangibleid%type;
81 l_tmid iby_trxn_summaries_all.trxnmid%type;
82 l_old_tmid iby_trxn_summaries_all.trxnmid%type;
83 l_tid iby_trxn_summaries_all.transactionId%type;
84 l_mpayeeid iby_payee.mpayeeid%type;
85 l_reference_code iby_trxn_summaries_all.proc_reference_code%type;
86 l_reference_amount iby_trxn_summaries_all.amount%type;
87 l_status iby_trxn_summaries_all.status%type;
88 l_beptype iby_bepinfo.bep_type%type;
89 l_trxntypeid iby_trxn_summaries_all.trxntypeid%TYPE;
90 l_reqtype iby_trxn_summaries_all.reqtype%TYPE;
91
92 l_settle_cust_ref iby_trxn_summaries_all.
93 settlement_customer_reference%TYPE;
94 l_first_trx_flag iby_trxn_summaries_all.first_trxn_flag%TYPE;
95
96 l_cnt NUMBER := 0;
97
98 cursor c_tmid is
99 select iby_trxnsumm_mid_s.nextval
100 from dual;
101
102 cursor c_reference(ci_tangible_id iby_trxn_summaries_all.tangibleid%type) is
103 select proc_reference_code, amount
104 from iby_trxn_summaries_all
105 where tangibleid= ci_tangible_id
106 --and trxntypeid=20
107 --and status=0
108 order by trxnmid desc;
109
110 cursor c_settle_exists is
111 select trxnmid, mtangibleid
112 from iby_trxn_summaries_all
113 where tangibleid=i_tangibleid
114 and payeeid=i_payeeid
115 and bepid=i_bepid
116 and bepkey=i_bepkey
117 and trxntypeid is null
118 order by trxnmid desc;
119
120 begin
121
122 l_reqtype := i_reqtype;
123
124 -- for bills receivable
125 IF (NVL(i_instrid,0) <> 0) THEN
126 checkInstrId(i_instrid);
127 END IF;
128 if(i_bnfcryinstrid <> NULL) then
129 checkInstrId(i_bnfcryinstrid);
130 end if;
131
132 /*
133 ** call requestExist method to check whether request already exists
134 ** or not. if exists, then raise an application error.
135 */
136 if ( not requestExists(i_payeeid, i_tangibleid, i_splitId, i_reqtype) ) then
137 --open c_tid;
138 --fetch c_tid into l_tid;
139 --close c_tid;
140 l_tid := iby_transactioncc_pkg.getTID(i_payeeid, i_tangibleid);
141
142
143 /*
144 ** if cursor is already open then close it.
145 */
146 if ( c_tmid%isopen ) then
147 close c_tmid;
148 end if;
149 --if ( c_tid%isopen ) then
150 --close c_tid;
151 --end if;
152 /*
153 ** open the cursor to get the next available transaction id.
154 */
155 open c_tmid;
156 fetch c_tmid into l_tmid;
157 close c_tmid;
158 --open c_tid;
159 --fetch c_tid into l_tid;
160 --close c_tid;
161 /*
162 ** Make entry in the payments table.
163 */
164
165
166 iby_accppmtmthd_pkg.getMPayeeId(i_payeeid, l_mpayeeid);
167
168 /*
169 * Get the bep type based on the bep id.
170 * This is used to determine what value to
171 * insert for transaction status.
172 */
173 SELECT NVL(bep_type,iby_bepinfo_pkg.C_BEPTYPE_GATEWAY)
174 INTO l_beptype
175 FROM IBY_BEPINFO
176 WHERE (bepid=i_bepid);
177
178 IF (l_beptype = iby_bepinfo_pkg.C_BEPTYPE_GATEWAY) THEN
179 /*
180 * For gateways, pending = status 11
181 */
182 l_status := 11;
183 ELSE
184 /*
185 * For processors, pending = status 100
186 */
187 l_status := 100;
188 -- expected by new batch grouping program- bug # 6125789
189 l_trxntypeid := 100;
190 l_reqtype := 'ORAPMTEFTCLOSEBATCH';
191 END IF;
192
193 /* now get the reference code*/
194
195 if ( c_reference%isopen ) then
196 close c_reference;
197 end if;
198
199 open c_reference(i_tangibleid);
200 fetch c_reference into l_reference_code, l_reference_amount;
201 close c_reference;
202
203 /*
204 * Some direct debit formats require the debtors
205 * reference on the transaction. The debtors reference
206 * is the mandate signed between the merchant and the
207 * customer.
208 *
209 * AR does not hat a source for this field in FP.G.
210 * In order to minimize the impact, this field is picked
211 * up from HZ_CUST_ACCOUNTS.ACCOUNT_NAME.
212 */
213 BEGIN
214
215 IF (i_custacctid IS NOT NULL) THEN
216
217 SELECT
218 hz.account_name
219 INTO
220 l_settle_cust_ref
221 FROM
222 HZ_CUST_ACCOUNTS hz
223 WHERE
224 hz.cust_account_id = i_custacctid
225 ;
226
227 END IF;
228
229 EXCEPTION
230 WHEN OTHERS THEN
231
232 /*
233 * If no rows or found or if multiple rows
234 * are found, we will not be able to get the
235 * settlement reference.
236 *
237 * This is ok. This will be caught during the
238 * validation of the payment message, and the
239 * transaction will be failed.
240 *
241 * Handle the exception gracefully here.
242 */
243
244 NULL;
245
246 END;
247
248 /*
249 * Some payment systems (e.g., Citibank) need to
250 * know whether a transaction is a first direct debit
251 * for a particular payer.
252 *
253 * Determining whether this is the first direct debit
254 * by looking up the trxns table for this (payee, payer)
255 * combination and checking whether any direct debit
256 * transactions exist.
257 */
258 BEGIN
259
260 SELECT
261 COUNT(*)
262 INTO
263 l_cnt
264 FROM
265 IBY_TRXN_SUMMARIES_ALL trxn
266 WHERE
267 trxn.mpayeeid = l_mpayeeid AND
268 trxn.instrtype = 'BANKACCOUNT' AND
269 trxn.instrsubtype = 'ACH' AND
270 trxn.payerid = i_payerid
271 ;
272
273 /*
274 * If count of existing transactions for this
275 * payer is 0, this is the first direct debit
276 * for this payer.
277 */
278 IF (l_cnt = 0) THEN
279 l_first_trx_flag := 'Y';
280 ELSE
281 l_first_trx_flag := 'N';
282 END IF;
283
284 EXCEPTION
285 WHEN OTHERS THEN
286
287 /*
288 * Handle error situations gracefully.
289 * Assume that this is not the first debit
290 * for this payer.
291 */
292 l_first_trx_flag := 'N';
293
294 END;
295
296
297
298 /*
299 ** if cursor is already open then close it.
300 */
301 if ( c_settle_exists%isopen ) then
302 close c_settle_exists;
303 end if;
304
305 /*
306 ** open the cursor to get the next available transaction id.
307 */
308 open c_settle_exists;
309 fetch c_settle_exists into l_old_tmid, l_mtangibleid;
310
311
312 if(c_settle_exists%NOTFOUND) then
313
314 /*
315 ** call createBill procedure in iby_bill_pkg to create tangible in the
316 ** database.
317 */
318 -- the order_medium and eftauthmethod are not relevant for fi payments
319
320
321 iby_bill_pkg.createBill(i_tangibleid, i_billAmount,
322 i_billCurDef, i_billAcct,i_billRefInfo,
323 i_billMemo, i_billOrderMedium, i_billEftAuthMethod, l_mtangibleid);
324
325
326 insert into iby_trxn_summaries_all
327 ( trxnMId, transactionId, tangibleid,
328 mpayeeid, payeeid, payeeinstrid,
329 bepid, bepkey, ecappid, org_id, org_type,
330 paymentMethodname, payerid, payerinstrid,
331 amount, currencyNameCode, reqdate,
332 reqtype, status, settledate,
333 mtangibleId, nlslang, instrtype, instrsubtype,
334 last_update_date, updatedate, last_updated_by,
335 creation_date, created_by,
336 last_update_login, object_version_number,
337 proc_reference_code, proc_reference_amount,
338 cust_account_id, acct_site_id, acct_site_use_id,
339 settlement_customer_reference, first_trxn_flag,
340 process_profile_code, trxntypeid,needsupdt
341 )
342
343 values ( l_tmid, l_tid, i_tangibleid, l_mpayeeid,
344 i_payeeid, i_bnfcryinstrid, i_bepid,
345 i_bepkey, i_ecappid, i_org_id, i_org_type, i_pmtMethod,
346 i_payerid, i_instrid, i_billamount,
347 i_billcurDef, i_reqdate, l_reqtype,
348 l_status, i_scheddate, l_mtangibleid, i_nlslang,
349 'BANKACCOUNT', 'ACH',
350 sysdate, sysdate, fnd_global.user_id,
351 sysdate, fnd_global.user_id,
352 fnd_global.login_id, 1, l_reference_code, l_reference_amount,
353 i_custacctid, i_acctsiteid, i_acctsiteuseid,
354 l_settle_cust_ref, l_first_trx_flag, i_profilecode,
355 l_trxntypeid,'Y'
356 );
357
358 /*
359 ** After everything is successful then create an entry in the
360 ** iby_request table.
361 */
362 insert into iby_trxn_fi
363 (trxnMid, psreqid, splitId,
364 last_update_date, last_updated_by,
365 creation_date, created_by,
366 last_update_login, object_version_number)
367 values ( l_tmid, i_psreqid, i_splitId,
368 sysdate, fnd_global.user_id,
369 sysdate, fnd_global.user_id,
370 fnd_global.login_id, 1);
371 io_tid := l_tid;
372
373 else
374 -- Update iby_tangible table
375
376 iby_bill_pkg.modBill(
377 l_mtangibleid, -- IN i_mtangibleid
378 i_tangibleid, -- IN i_billId
379 i_billamount, -- IN i_billAmount
380 i_billCurDef, -- IN i_billCurDef
381 i_billAcct, -- IN i_billAcct
382 i_billRefInfo, -- IN i_billRefInfo
383 i_billMemo, -- IN i_billMemo
384 i_billOrderMedium, -- IN i_billOrderMedium
385 i_billEftAuthMethod); -- IN i_billEftAuthMethod
386
387
388 UPDATE iby_trxn_summaries_all
389 SET
390 org_id = i_org_id,
391 ecappid = i_ecappid,
392 payeeid = i_payeeid,
393 bepid = i_bepid,
394 bepkey = i_bepkey,
395 paymentMethodname = i_pmtmethod,
396 payerid = i_payerid,
397 payerinstrid = i_instrid,
398 amount = i_billamount,
399 currencyNameCode = i_billcurDef,
400 status = l_status,
401 cust_account_id = i_custacctid,
402 acct_site_id = i_acctsiteid,
403 acct_site_use_id = i_acctsiteuseid,
404 last_update_date = sysdate,
405 updatedate = sysdate,
406 last_updated_by = fnd_global.user_id,
407 creation_date = sysdate,
408 created_by = fnd_global.user_id,
409 last_update_login = fnd_global.user_id,
410 object_version_number = object_version_number+1,
411 settlement_customer_reference = l_settle_cust_ref,
412 first_trxn_flag = l_first_trx_flag
413 WHERE trxnmid = l_old_tmid;
414 end if;
415
416 close c_settle_exists;
417 io_tid := l_tid;
418 else
419 raise_application_error(-20000, 'IBY_20560#', FALSE);
420 --raise_application_error(-20560, 'Duplicate Request ' , FALSE);
421 end if;
422 commit;
423 end createPayment;
424
425
426 Function requestExists(i_tid iby_trxn_summaries_all.transactionId%type,
427 i_reqtype iby_trxn_summaries_all.reqtype%type)
428 return boolean
429 is
430 l_cnt int;
431 begin
432 /*
433 ** get the count of the rows with same request type, tangibleid and
434 ** payeeid. If count is more than 0, it means, request already exists
435 ** otherwise does not exist.
436 */
437 select count(*) into l_cnt
438 from iby_trxn_summaries_all s
439 where transactionId = i_tid
440 and reqType = i_reqType;
441
442 -- and status = 0
443
444 if ( l_cnt = 0 ) then
445 return false;
446 else
447 return true;
448 end if;
449 end requestExists;
450
451
452 Function requestExists(i_payeeid iby_trxn_summaries_all.payeeid%type,
453 i_tangibleid iby_trxn_summaries_all.tangibleid%type,
454 i_splitId in iby_trxn_fi.splitId%type,
455 i_reqtype iby_trxn_summaries_all.reqtype%type)
456 return boolean
457 is
458 l_cnt int;
459 begin
460 /*
461 ** get the count of the rows with same request type, tangibleid and
462 ** payeeid. If count is more than 0, it means, request already exists
463 ** otherwise does not exist.
464 */
465 select count(*) into l_cnt
466 from iby_trxn_summaries_all s
467 where payeeId = i_payeeId
468 and tangibleid = i_tangibleId
469 and UPPER(reqType) = UPPER(i_reqType)
470 and trxntypeid not in (20);
471 --and i_splitId in ( select splitId
472 --from iby_trxn_fi f
473 --where f.trxnmid = s.trxnmid);
474 -- The 'AND' condition for the splitId is removed as we do not use
475 -- splitId anymore.
476
477 if ( l_cnt = 0 ) then
478 return false;
479 else
480 return true;
481 end if;
482 end requestExists;
483 /*
484 ** Procedure: CanModify.
485 ** Purpose: Checks whether particular transaction specified
486 ** can be modified or not.
487 ** In Params: i_tid, transaction id.
488 ** out Params: boolean , true if it can be modified otherwise false.
489 */
490 function canModifyorCancel(i_tid in iby_trxn_summaries_all.transactionId%type)
491 return boolean
492 is
493 l_cnt int;
494 begin
495 select count(*) into l_cnt
496 from iby_trxn_summaries_all s
497 where transactionId = i_tid
498 and (( (reqtype = 'ORAPMTREQ' or reqtype = 'ORAPMTCREDIT') and (status <> -99 and status <> 100)) or
499 (reqtype = 'ORAPMTCANC'));
500 if ( l_cnt = 0 ) then
501 return true;
502 else
503 return false;
504 end if;
505 end canModifyOrCancel;
506
507 /*
508 ** Procedure: modifyPayment.
509 ** Purpose: modify the payment that matches the tid passed.
510 ** Description: Check whether the payment request id is already sent or
511 ** not. if yes raise an error. Otherwise, check whether
512 ** user holds the specified instrument or not.
513 ** Check whether specified payee id exists or not.
514 ** If all the check are validated then create the
515 ** tangible. Modify the payment object that payment id matched,
516 ** then create the request object.
517 */
518 procedure modifyPayment(i_ecappid in iby_ecapp.ecappid%type,
519 i_payeeid in iby_trxn_summaries_all.payeeid%type,
520 i_instrid in iby_trxn_summaries_all.payerinstrid%type,
521 i_payerid in iby_trxn_summaries_all.payerid%type,
522 i_tangibleid in iby_trxn_summaries_all.tangibleid%type,
523 i_billamount in iby_trxn_summaries_all.amount%type,
524 i_billcurDef in iby_trxn_summaries_all.currencynamecode%type,
525 i_billAcct in iby_tangible.acctno%type,
526 i_billRefInfo in iby_tangible.refinfo%type,
527 i_billMemo in iby_tangible.memo%type,
528 i_billOrdermedium in iby_tangible.order_medium%TYPE,
529 i_billEftAuthMethod in iby_tangible.eft_auth_method%TYPE,
530 i_scheddate in iby_trxn_summaries_all.settledate%type,
531 i_reqtype in iby_trxn_summaries_all.reqtype%type,
532 i_reqdate in iby_trxn_summaries_all.reqdate%type,
533 i_bepid in iby_trxn_summaries_all.bepid%type,
534 i_bepkey in iby_trxn_summaries_all.bepkey%type,
535 i_pmtmethod in iby_trxn_summaries_all.paymentmethodname%type,
536 i_psreqId in iby_trxn_fi.psreqid%type,
537 i_nlslang in iby_trxn_summaries_all.nlslang%type,
538 i_tid in iby_trxn_summaries_all.transactionid%type,
539 i_org_id in iby_trxn_summaries_all.org_id%type)
540 is
541 l_mtangibleid iby_tangible.mtangibleid%type;
542 l_tmid iby_trxn_summaries_all.trxnmid%type;
543 l_tangibleid iby_trxn_summaries_all.tangibleid%type;
544 l_reqtype iby_trxn_summaries_all.reqtype%type;
545
546 l_mpayeeid iby_payee.mpayeeid%type;
547
548 cursor c_tmid is
549 select iby_trxnsumm_mid_s.nextval
550 from dual;
551
552 cursor c_mtangibleid(ci_tid in iby_trxn_summaries_all.transactionid%type)
553 is
554 select mtangibleid , tangibleid
555 from iby_trxn_summaries_all
556 where transactionId = ci_tid
557 group by mtangibleid , tangibleid;
558 l_splitId iby_trxn_fi.splitId%type;
559 begin
560
561 IF (NVL(i_instrid,0) <> 0) THEN
562 checkInstrId(i_instrid);
563 END IF;
564 /*
565 ** call requestExist method to check whether request already exists
566 ** or not. if exists, then raise an application error.
567 */
568 if ( canModifyOrCancel(i_tid) ) then
569 /*
570 ** if payee account does not exists then raise an exception.
571 */
572 if ( not iby_payee_pkg.payeeExists( i_ecappid, i_payeeid) ) then
573 raise_application_error(-20000, 'IBY_20305#', FALSE);
574 --raise_application_error(-20305, 'Payee Not Found ', FALSE);
575 end if;
576 /*
577 ** call createBill procedure in iby_bill_pkg to create tangible in the
578 ** database.
579 */
580 if ( c_mtangibleid%isopen ) then
581 close c_mtangibleid;
582 end if;
583 open c_mtangibleid(i_tid);
584 fetch c_mtangibleid into l_mtangibleid, l_tangibleid;
585 close c_mtangibleid;
586 if ( l_tangibleid <> i_tangibleid ) then
587 raise_application_error(-20000, 'IBY_20561#', FALSE);
588 --raise_application_error(-20561, 'Tangible Id should Match',
589 --TRUE);
590 end if;
591 iby_bill_pkg.createBill(i_tangibleid, i_billAmount,
592 i_billCurDef, i_billAcct,i_billRefInfo,
593 i_billMemo, i_billOrdermedium,
594 i_billEftAuthMethod, l_mtangibleid);
595
596 /*
597 ** open the cursor to get the next available transaction id.
598 */
599 open c_tmid;
600 fetch c_tmid into l_tmid;
601 close c_tmid;
602
603 /*
604 ** get the reqtype
605 */
606 select reqtype into l_reqtype from iby_trxn_summaries_all
607 WHERE transactionId = i_tid and status = 100
608 and (reqtype = 'ORAPMTREQ' or reqtype = 'ORAPMTCREDIT');
609
610
611 /*
612 ** update the transactions table with the new data.
613 */
614 update iby_trxn_summaries_all
615 set status = -99,
616 last_update_date = sysdate,
617 updatedate = sysdate,
618 last_updated_by = fnd_global.user_id,
619 last_update_login = fnd_global.login_id,
620 object_version_number = 1
621 where transactionId = i_tid
622 and (reqtype = 'ORAPMTREQ' or reqtype = 'ORAPMTCREDIT')
623 and status = 100;
624
625
626 -- // pending requests...
627
628 select splitId into l_splitId
629 from iby_trxn_fi
630 where trxnmid in ( select trxnmid
631 from iby_trxn_summaries_all
632 where transactionId = i_tid)
633 and rownum < 2;
634 /*
635 ** Make entry in the payments table.
636 */
637 iby_accppmtmthd_pkg.getMPayeeId(i_payeeid, l_mpayeeid);
638 insert into iby_trxn_summaries_all
639 ( trxnMId, transactionId, tangibleid,
640 mpayeeid, payeeid, bepid, bepkey, ecappid,
641 org_id, paymentMethodname,
642 payerid, payerinstrid, amount,
643 currencyNameCode,
644 reqdate, reqtype, status, settledate,
645 mtangibleId, nlslang, instrtype,
646 last_update_date, updatedate, last_updated_by,
647 creation_date, created_by,
648 last_update_login, object_version_number,needsupdt)
649
650 values ( l_tmid, i_tid, i_tangibleid, l_mpayeeid, i_payeeid,
651 i_bepid, i_bepkey, i_ecappid, i_org_id, i_pmtMethod,
652 i_payerid, i_instrid,
653 i_billamount, i_billcurDef, i_reqdate,
654 l_reqtype, 100, i_scheddate, l_mtangibleid, i_nlslang,
655 'BANKACCOUNT',
656 sysdate, sysdate, fnd_global.user_id,
657 sysdate, fnd_global.user_id,
658 fnd_global.login_id, 1,'Y');
659
660 /*
661 ** After inserting an entry in transactions_summaries_all table,
662 ** make an entry in the iby_trxn_fi table, corresponding to
663 ** the transaction master id.
664 */
665 insert into iby_trxn_fi
666 (trxnMid, psreqid, splitId,
667 last_update_date, last_updated_by,
668 creation_date, created_by,
669 last_update_login, object_version_number)
670 values ( l_tmid, i_psreqid, l_splitId,
671 sysdate, fnd_global.user_id,
672 sysdate, fnd_global.user_id,
673 fnd_global.login_id, 1);
674 else
675 /*
676 ** if modification is not allowed then raise an exception.
677 */
678 --can't modify/cancel when request not pending
679 raise_application_error(-20000, 'IBY_41516#', FALSE);
680 --raise_application_error(-20000, 'IBY_20562#', FALSE);
681 --raise_application_error(-20562,'Modification can not be done', FALSE);
682 end if;
683
684 commit;
685 end modifyPayment;
686 /*
687 ** Procedure: deletePayment.
688 ** Purpose: Marks the payment whose id matches the tid passed as
689 ** 'CANCELLED'.
690 */
691 -- procedure deletePayment(i_ecappid in iby_ecapp.ecappid%type,
692 procedure deletePayment( i_tid in iby_trxn_summaries_all.transactionId%type )
693 is
694 l_old_tmid iby_trxn_summaries_all.trxnmid%type;
695 l_new_tmid iby_trxn_summaries_all.trxnmid%type;
696 l_tid iby_trxn_summaries_all.transactionId%type;
697 l_status iby_trxn_summaries_all.status%type;
698
699 cursor c_tmid is
700 select iby_trxnsumm_mid_s.nextval
701 from dual;
702
703 cursor getLatestTMID(ci_tid in iby_trxn_summaries_all.transactionId%type) is
704 select trxnmid , status
705 from iby_trxn_summaries_all s
706 where transactionid=ci_tid
707 and status = 100
708 -- // status for pending.
709 and (reqtype = 'ORAPMTREQ' or reqtype = 'ORAPMTCREDIT');
710
711 begin
712 /*
713 ** if request already exists, then raise an Exception.
714 */
715 --if ( requestExists(i_tid, 'ORAPMTCANC') ) then
716 -- raise_application_error(-20000, 'IBY_20563#', FALSE);
717 --raise_application_error(-20563, 'Payment is already cancelled', FALSE);
718 --end if;
719 /*
720 ** Check if the request can be cancelled or not. If not raise
721 ** error.
722 */
723 if ( canModifyOrCancel(i_tid) ) then
724 /*
725 ** make the earlier transaction entry as invlid, create another
726 ** entry with same information, but cahnge status to -1.
727 */
728 open getLatestTMID(i_tid);
729 fetch getLatestTMID into l_old_tmid, l_status;
730 if ( getLatestTMID%notfound ) then
731 close getLatestTMID;
732 raise_application_error(-20000, 'IBY_20564#', FALSE);
733 --raise_application_error(-20564,'No PmtReq request before.', FALSE);
734 end if;
735 close getLatestTMID;
736 /*
737 ** Get Master transaction Id, if cursor is already open then close it.
738 */
739 if ( c_tmid%isopen ) then
740 close c_tmid;
741 end if;
742 open c_tmid;
743 fetch c_tmid into l_new_tmid;
744 close c_tmid;
745 /*
746 ** update the transactions table with the new data.
747 */
748 update iby_trxn_summaries_all
749 set status = -99,
750 last_update_date = sysdate,
751 updatedate = sysdate,
752 last_updated_by = fnd_global.user_id,
753 last_update_login = fnd_global.login_id,
754 object_version_number = 1
755 where transactionId = i_tid
756 and (reqtype = 'ORAPMTREQ' or reqtype = 'ORAPMTCREDIT');
757
758 /*
759 ** Insert record, with values that of latest record and increment the
760 ** tid value by 1.
761 */
762 insert into iby_trxn_summaries_all
763 ( trxnMId, transactionId, tangibleid,
764 mpayeeid, payeeid, bepid, ecappid,
765 org_id, paymentMethodname,
766 payerid, payerinstrid,
767 amount, currencyNameCode,
768 reqdate, reqtype, status,
769 mtangibleId, nlslang, instrtype,
770 last_update_date, updatedate, last_updated_by,
771 creation_date, created_by,
772 last_update_login, object_version_number,needsupdt)
773
774 select l_new_tmid, transactionId, tangibleid,
775 mpayeeid, payeeid, bepid, ecappid,
776 org_id, paymentMethodname,
777 payerid, payerinstrid,
778 amount, currencyNameCode,
779 sysdate, 'ORAPMTCANC', 14,
780 mtangibleId, nlslang, 'BANKACCOUNT',
781 sysdate, sysdate, fnd_global.user_id,
782 sysdate, fnd_global.user_id,
783 fnd_global.login_id, 1,'Y'
784 from iby_trxn_summaries_all
785 where trxnmid = l_old_tmid;
786
787
788 insert into iby_trxn_fi
789 (trxnMid, psreqid, splitId,
790 last_update_date, last_updated_by,
791 creation_date, created_by,
792 last_update_login, object_version_number)
793
794 select l_new_tmid, psreqid, splitId,
795 sysdate, fnd_global.user_id,
796 sysdate, fnd_global.user_id,
797 fnd_global.login_id, 1
798 from iby_trxn_fi
799 where trxnmid = l_old_tmid;
800
801 else
802 -- can't modify/cancel when not pending
803 raise_application_error(-20000, 'IBY_41516#', FALSE);
804 --raise_application_error(-20000, 'IBY_20560#', FALSE);
805 --raise_application_error(-20560, 'Duplicate Request ', FALSE);
806 end if;
807 commit;
808 end deletePayment;
809
810
811 /*
812 * Procedure: setTrxnStatus
813 * Purpose: Modify the status of the transaction
814 *
815 */
816
817 procedure setTrxnStatus(i_tmid in iby_trxn_summaries_all.trxnmid%type,
818 i_status in iby_trxn_summaries_all.status%type)
819 is
820
821 begin
822
823 update iby_trxn_summaries_all
824 set status=i_status
825 where trxnmid=i_tmid;
826
827 commit;
828
829 exception
830 when others then
831 raise_application_error(-20000, 'IBY_20400#', FALSE);
832
833 end setTrxnStatus;
834
835 /*
836 ** Procedure: createReturnPayment.
837 ** Purpose: Create the payment whose id matches the tid passed as
838 ** 'ORAPMTRETURN'.
839 */
840
841 procedure createReturnPayment(
842 i_tid in iby_trxn_summaries_all.transactionId%type,
843 i_currencycode in iby_trxn_summaries_all.currencynamecode%type,
844 i_amount in iby_trxn_summaries_all.amount%type,
845 io_trxnmid in out nocopy iby_trxn_summaries_all.trxnmid%type
846 )
847 is
848 l_old_tmid iby_trxn_summaries_all.trxnmid%type;
849 l_new_tmid iby_trxn_summaries_all.trxnmid%type;
850 l_tid iby_trxn_summaries_all.transactionId%type;
851 l_status iby_trxn_summaries_all.status%type;
852 l_reference_code iby_trxn_summaries_all.proc_reference_code%type;
853 l_trxn_exists VARCHAR2(1);
854 cursor c_tmid is
855 select iby_trxnsumm_mid_s.nextval
856 from dual;
857
858 CURSOR trxn_exists IS
859 SELECT 'Y', trxnmid
860 FROM iby_trxn_summaries_all s
861 WHERE transactionid=i_tid
862 AND trxntypeid=5
863 AND status <> '0'
864 ORDER BY reqdate desc;
865
866 cursor getLatestTMID(ci_tid in iby_trxn_summaries_all.transactionId%type) is
867 select trxnmid , status, proc_reference_code
868 from iby_trxn_summaries_all s
869 where transactionid=ci_tid
870 and status in ( 0, 100)
871 -- // status for pending or success.
872 and (reqtype = 'ORAPMTREQ')
873 and trxntypeid is null;
874
875 begin
876
877 open getLatestTMID(i_tid);
878 fetch getLatestTMID into l_old_tmid, l_status, l_reference_code;
879 if ( getLatestTMID%notfound ) then
880 close getLatestTMID;
881 raise_application_error(-20000, 'IBY_20564#', FALSE);
882
883 end if;
884 close getLatestTMID;
885
886 /*
887 ** check return request exists or not
888 */
889 if ( trxn_exists%isopen ) then
890 close trxn_exists;
891 end if;
892 OPEN trxn_exists;
893 FETCH trxn_exists INTO l_trxn_exists, l_new_tmid;
894 CLOSE trxn_exists;
895
896
897 IF (NVL(l_trxn_exists, 'N') = 'Y') THEN
898
899 update iby_trxn_summaries_all
900 set currencynamecode=i_currencycode,
901 amount=i_amount,
902 proc_reference_code=l_reference_code,
903 last_update_date = sysdate,
904 updatedate = sysdate,
905 last_updated_by = fnd_global.user_id,
906 last_update_login = fnd_global.user_id,
907 object_version_number = 1
908 where trxnmid=l_new_tmid;
909 else
910
911 /*
912 ** Get Master transaction Id, if cursor is already open then close it.
913 */
914 if ( c_tmid%isopen ) then
915 close c_tmid;
916 end if;
917 open c_tmid;
918 fetch c_tmid into l_new_tmid;
919 close c_tmid;
920
921 /*
922 ** Insert record, with values that of latest record and increment the
923 ** tid value by 1.
924 */
925 insert into iby_trxn_summaries_all
926 ( trxnMId, transactionId, tangibleid,
927 mpayeeid, payeeid, bepid, ecappid,
928 org_id, paymentMethodname,
929 payerid, payerinstrid,
930 amount, currencyNameCode,
931 reqdate, reqtype, status,
932 mtangibleId, nlslang, instrtype,
933 last_update_date, updatedate, last_updated_by,
934 creation_date, created_by,
935 last_update_login, object_version_number,
936 proc_reference_code, proc_reference_amount, trxntypeid, bepkey,needsupdt
937 ,payment_channel_code,settledate,settlement_due_date)
938
939 select l_new_tmid, transactionId, tangibleid,
940 mpayeeid, payeeid, bepid, ecappid,
941 org_id, paymentMethodname,
942 payerid, payerinstrid,
943 i_amount, i_currencycode,
944 sysdate, 'ORAPMTRETURN', 9,
945 mtangibleId, nlslang, 'BANKACCOUNT',
946 sysdate, sysdate, fnd_global.user_id,
947 sysdate, fnd_global.user_id,
948 fnd_global.login_id, 1,
949 proc_reference_code, amount, 5, bepkey,'Y'
950 , payment_channel_code,settledate,settlement_due_date
951 from iby_trxn_summaries_all
952 where trxnmid = l_old_tmid;
953
954
955 insert into iby_trxn_fi
956 (trxnMid, psreqid, splitId,
957 last_update_date, last_updated_by,
958 creation_date, created_by,
959 last_update_login, object_version_number)
960
961 select l_new_tmid, psreqid, splitId,
962 sysdate, fnd_global.user_id,
963 sysdate, fnd_global.user_id,
964 fnd_global.login_id, 1
965 from iby_trxn_fi
966 where trxnmid = l_old_tmid;
967
968 end if;
969 io_trxnmid:=l_new_tmid;
970
971 commit;
972 end createReturnPayment;
973
974
975 /*
976 * Procedure: updateTrxn
977 * Purpose: Update the transaction status with the return results
978 *
979 */
980
981 procedure updateTrxn(
982 i_trxnmid in iby_trxn_summaries_all.trxnmid%type,
983 i_status in iby_trxn_summaries_all.status%type,
984 i_bepcode IN iby_trxn_summaries_all.BEPCode%TYPE,
985 i_bepmessage IN iby_trxn_summaries_all.BEPMessage%TYPE,
986 i_errorlocation IN iby_trxn_summaries_all.errorlocation%TYPE,
987 i_referenceCode IN iby_trxn_summaries_all.proc_reference_code%TYPE)
988 is
989
990 begin
991
992 UPDATE iby_trxn_summaries_all
993 SET
994 status = i_status,
995 bepcode = i_bepcode,
996 bepmessage = i_bepmessage,
997 errorlocation = i_errorlocation,
998 last_update_date = sysdate,
999 last_updated_by = fnd_global.user_id,
1000 last_update_login = fnd_global.user_id,
1001 object_version_number = 1,
1002 proc_reference_code = i_referencecode
1003 WHERE trxnmid = i_trxnmid;
1004
1005 commit;
1006
1007 exception
1008 when others then
1009 raise_application_error(-20000, 'IBY_20400#', FALSE);
1010
1011 end updateTrxn;
1012
1013 end iby_fipayments_pkg;
[Home] [Help]