[Home] [Help]
PACKAGE BODY: APPS.IBY_OFFLINEDB_PKG
Source
1 PACKAGE BODY iby_offlinedb_pkg AS
2 /*$Header: ibyofflb.pls 120.2.12010000.2 2008/07/29 05:51:15 sugottum ship $ */
3
4 /*
5 ** Function Name : isPayeeRegistered
6 ** Purpose : Checks if payee has specified bep key with the specified bep.
7 **
8 ** Parameters:
9 **
10 ** In : i_payeeid, i_bepid, i_bepkey
11 **
12 */
13 Function isPayeeRegistered(i_payeeid iby_bepkeys.ownerid%type, i_bepid iby_bepkeys.bepid%type, i_bepkey iby_bepkeys.key%type )
14 return boolean
15 is
16 l_cnt integer;
17 begin
18
19 select count(*) into l_cnt
20 from iby_bepkeys
21 where bepid = i_bepid
22 and ownerid = i_payeeid
23 and ownertype = 'PAYEE'
24 and key = i_bepkey;
25
26 return (l_cnt <> 0);
27 end;
28
29
30 /*
31 ** Procedure Name : schedulePC
32 ** Purpose : creates an entry for scheduling the CreditCard payments
33 ** in the iby_trxn_summaries_all and iby_trxn_core
34 ** tables. Returns the transactionid created
35 ** by the system.
36 */
37 procedure schedulePC( i_ecappid iby_trxn_summaries_all.ecappid%type,
38 i_payeeid iby_trxn_summaries_all.payeeid%type,
39 i_bepid iby_trxn_summaries_all.bepid%type,
40 i_bepkey iby_trxn_summaries_all.bepkey%type,
41 i_tangibleid iby_trxn_summaries_all.tangibleid%type,
42 i_reqtype iby_trxn_summaries_all.reqtype%type,
43 i_reqdate iby_trxn_summaries_all.reqdate%type,
44 i_pmtmethod iby_trxn_summaries_all.paymentmethodname%type,
45 i_desturl iby_trxn_summaries_all.desturl%type,
46 io_transactionid in out nocopy iby_trxn_summaries_all.transactionid%type,
47 i_amount iby_trxn_summaries_all.amount%type,
48 i_currency iby_trxn_summaries_all.currencynamecode%type,
49 i_trxntypeid iby_trxn_summaries_all.trxntypeid%type,
50 i_nlslang iby_trxn_summaries_all.Nlslang%type,
51 i_settledate iby_trxn_summaries_all.settledate%type,
52 i_authtype iby_trxn_core.authtype%type,
53 i_instrid iby_trxn_summaries_all.payerinstrid%type,
54 i_payerid iby_trxn_summaries_all.payerid%type,
55 i_org_id iby_trxn_summaries_all.org_id%type,
56
57 i_instrtype iby_trxn_summaries_all.instrtype%type,
58 i_billeracct iby_tangible.acctno%type,
59 i_refinfo iby_tangible.refinfo%type,
60 i_memo iby_tangible.memo%type,
61 i_voiceauthflag iby_trxn_core.voiceauthflag%type,
62 i_authcode iby_trxn_core.authcode%type,
63 i_ponum iby_trxn_core.ponumber%type,
64 i_taxamt iby_trxn_core.taxamount%type,
65 i_shipfromzip iby_trxn_core.shipfromzip%type,
66 i_shiptozip iby_trxn_core.shiptozip%type,
67 i_OrderMedium IN iby_tangible.Order_Medium%TYPE,
68 i_EftAuthMethod IN iby_tangible.Eft_Auth_Method%TYPE
69 )
70 IS
71 l_mid NUMBER;
72 l_tid NUMBER;
73 l_org_id NUMBER;
74 -- 0011 indicates PENDING status
75 l_status NUMBER := 0011;
76 l_tmid iby_trxn_summaries_all.mtangibleid%type;
77
78 l_instrid iby_trxn_summaries_all.payerinstrid%type;
79
80 l_mpayeeid iby_payee.mpayeeid%type;
81
82 BEGIN
83
84 -- First check if this request is not duplicate
85 if (requestExists(i_payeeid,i_tangibleid, i_reqtype, i_bepid )) then
86 raise_application_error(-20000, 'IBY_20604#', FALSE);
87 -- duplicated request
88 end if;
89
90 if (UPPER(i_reqtype) = 'ORAPMTREQ' OR
91 UPPER(i_reqtype) = 'ORAPMTCREDIT') then
92
93 if ( isPayeeRegistered(i_payeeid, i_bepid, i_bepkey ) = false ) then
94 -- make sure payee id is valid
95 raise_application_error(-20000, 'IBY_20605#', FALSE);
96 end if;
97
98 if (i_instrid IS NULL or (NOT instrExists(i_instrid, i_instrtype))) THEN
99 --reject invalid instrid
100 raise_application_error(-20000, 'IBY_20512#', FALSE);
101 END IF;
102
103 -- Get new transaction id
104 io_transactionid := iby_transactioncc_pkg.getTID(
105 i_payeeid, i_tangibleid);
106
107 iby_bill_pkg.createBill(i_tangibleid, i_amount, i_currency,
108 i_billeracct, i_refinfo, i_memo,
109 i_OrderMedium, i_EftAuthMethod, l_tmid);
110
111 l_instrid := i_instrid;
112 l_org_id := i_org_id;
113 ELSE
114 SELECT DISTINCT mtangibleid, payerinstrid into l_tmid, l_instrid
115 from iby_trxn_summaries_all
116 where transactionid = io_transactionid
117 --and status <> -99 and status <> 14;
118 and (status = 11 or status = 0);
119
120 -- getOrgId
121 l_org_id := iby_transactioncc_pkg.getOrgId(io_transactionid);
122 END IF;
123
124 -- Get the master transaction id sequence for all requests
125 SELECT iby_trxnsumm_mid_s.NEXTVAL
126 INTO l_mid
127 FROM dual;
128
129 -- insert the scheduled request in the summary and core tables
130 iby_accppmtmthd_pkg.getMPayeeId(i_payeeid, l_mpayeeid);
131 INSERT INTO iby_trxn_summaries_all
132 (TrxnMID, TransactionID, TangibleID,
133 MPayeeID, PayeeID,BEPID, bepkey, ECAppID, PaymentMethodName,
134 PayerID, PayerInstrID, Amount,CurrencyNameCode,
135 Status, TrxntypeID, SettleDate, ReqDate, ReqType, DestUrl, Nlslang,
136 mtangibleid, org_id, instrtype,
137 last_update_date, updatedate, last_updated_by, creation_date, created_by,
138 last_update_login, object_version_number,needsupdt)
139 VALUES
140 (l_mid, io_transactionid, i_tangibleid,
141 l_mpayeeid, i_payeeid, i_bepid, i_bepkey, i_ecappid, i_pmtmethod,
142 i_payerid, l_instrid, i_amount, i_currency,
143 l_status, i_trxntypeid, i_settledate, sysdate, i_reqtype, i_desturl,
144 i_nlslang, l_tmid, l_org_id, i_instrtype,
145 sysdate, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, fnd_global.login_id, 1,'Y');
146
147 -- insert the authtype into core table
148 INSERT INTO iby_trxn_core
149 (TrxnMID, Authtype, PONumber, TaxAmount, ShipFromZip, ShipToZip,
150 last_update_date, last_updated_by, creation_date, created_by,
151 last_update_login, object_version_number, AuthCode, VoiceAuthFlag)
152 VALUES
153 (l_mid, i_authtype, i_ponum, i_taxamt, i_shipfromzip, i_shiptozip,
154 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
155 fnd_global.login_id, 1, i_authcode, i_voiceauthflag);
156
157 commit;
158 END schedulePC;
159
160 /*
161 ** Procedure Name : scheduleCC
162 ** Purpose : creates an entry for scheduling the PurchaseCard payments
163 ** in the iby_trxn_summaries_all and iby_trxn_core
164 ** tables. Returns the transactionid created
165 ** by the system.
166 */
167 procedure scheduleCC( i_ecappid iby_trxn_summaries_all.ecappid%type,
168 i_payeeid iby_trxn_summaries_all.payeeid%type,
169 i_bepid iby_trxn_summaries_all.bepid%type,
170 i_bepkey iby_trxn_summaries_all.bepkey%type,
171 i_tangibleid iby_trxn_summaries_all.tangibleid%type,
172 i_reqtype iby_trxn_summaries_all.reqtype%type,
173 i_reqdate iby_trxn_summaries_all.reqdate%type,
174 i_pmtmethod iby_trxn_summaries_all.paymentmethodname%type,
175 i_desturl iby_trxn_summaries_all.desturl%type,
176 io_transactionid in out nocopy iby_trxn_summaries_all.transactionid%type,
177 i_amount iby_trxn_summaries_all.amount%type,
178 i_currency iby_trxn_summaries_all.currencynamecode%type,
179 i_trxntypeid iby_trxn_summaries_all.trxntypeid%type,
180 i_nlslang iby_trxn_summaries_all.Nlslang%type,
181 i_settledate iby_trxn_summaries_all.settledate%type,
182 i_authtype iby_trxn_core.authtype%type,
183 i_instrid iby_trxn_summaries_all.payerinstrid%type,
184 i_payerid iby_trxn_summaries_all.payerid%type,
185 i_org_id iby_trxn_summaries_all.org_id%type,
186
187 i_instrtype iby_trxn_summaries_all.instrtype%type,
188
189 i_billeracct iby_tangible.acctno%type,
190 i_refinfo iby_tangible.refinfo%type,
191 i_memo iby_tangible.memo%type,
192 i_voiceauthflag iby_trxn_core.voiceauthflag%type,
193 i_authcode iby_trxn_core.authcode%type,
194 i_OrderMedium IN iby_tangible.Order_Medium%TYPE,
195 i_EftAuthMethod IN iby_tangible.Eft_Auth_Method%TYPE
196 )
197 IS
198 BEGIN
199
200 SchedulePC(i_ecappid, i_payeeid, i_bepid, i_bepkey, i_tangibleid, i_reqtype,
201 i_reqdate, i_pmtmethod, i_desturl, io_transactionid, i_amount,
202 i_currency, i_trxntypeid, i_nlslang, i_settledate, i_authtype,
203 i_instrid, i_payerid, i_org_id, i_instrtype, i_billeracct,
204 i_refinfo, i_memo, i_voiceauthflag, i_authcode, null, null, null,
205 null, i_OrderMedium, i_EftAuthMethod);
206
207 END ScheduleCC;
208
209
210 /*
211 ** Procedure Name : scheduleCCCancel
212 ** Purpose : creates an entry for a CreditCard cancel request in the
213 ** iby_trxn_summaries_all table
214 */
215 procedure scheduleCCCancel(i_ecappid iby_batches_all.ecappid%type,
216 i_payeeid iby_batches_all.payeeid%type,
217 i_bepid iby_batches_all.bepid%type,
218 i_bepkey iby_batches_all.bepkey%type,
219 i_tangibleid iby_trxn_summaries_all.tangibleid%type,
220 i_reqtype iby_trxn_summaries_all.reqtype%type,
221 i_reqdate iby_trxn_summaries_all.reqdate%type,
222 i_pmtmethod iby_trxn_summaries_all.paymentmethodname%type,
223 i_transactionid in
224 iby_trxn_summaries_all.transactionid%type
225 ,
226 i_reqtype_tocancel iby_trxn_summaries_all.reqtype%type,
227 i_trxntypeid_tocancel in iby_trxn_summaries_all.trxntypeid%type)
228 IS
229 l_mid NUMBER;
230 l_status NUMBER := 0014;
231 l_tmid iby_trxn_summaries_all.mtangibleid%type;
232 l_instrid iby_trxn_summaries_all.payerinstrid%type;
233 l_mpayeeid iby_payee.mpayeeid%type;
234 BEGIN
235 -- Get the master transaction id sequence for all requests
236
237 SELECT iby_trxnsumm_mid_s.NEXTVAL
238 INTO l_mid
239 FROM dual;
240
241 select mtangibleid, payerinstrid into l_tmid, l_instrid
242 from iby_trxn_summaries_all
243 where transactionid = i_transactionid
244 and reqtype = i_reqtype_tocancel
245 and rownum < 2;
246
247 iby_accppmtmthd_pkg.getMPayeeId(i_payeeid, l_mpayeeid);
248
249 INSERT INTO iby_trxn_summaries_all
250 (TrxnMID, TransactionID, TangibleID,
251 MPayeeID, PayeeID,BEPID, bepkey, ECAppID, PaymentMethodName,
252 status, mtangibleid, trxntypeid,
253 reqtype, reqdate, payerinstrid,
254 last_update_date, updatedate, last_updated_by, creation_date, created_by,
255 last_update_login, object_version_number,needsupdt)
256 VALUES
257 (l_mid, i_transactionid, i_tangibleid,
258 l_mpayeeid, i_payeeid, i_bepid, i_bepkey, i_ecappid, i_pmtmethod,
259 l_status, l_tmid, i_trxntypeid_tocancel,
260 i_reqtype, sysdate, l_instrid,
261 sysdate, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
262 fnd_global.login_id, 1,'Y');
263
264 commit;
265
266 EXCEPTION
267 WHEN NO_DATA_FOUND THEN
268 raise_application_error(-20000, 'IBY_20300#', FALSE);
269 --raise_application_error(-20300,'Cannot insert cancel trxn into iby_trxn_summaries_all');
270
271 END scheduleCCCancel;
272
273
274 /*
275 ** Procedure Name : scheduleCCbatch
276 ** Purpose : creates an entry for scheduling the CreditCard batch requests
277 ** in the iby_batches_all table.
278 **
279 ** Parameters:
280 **
281 ** In : i_ecappid, i_payeeid, i_bepid, i_batchid,
282 ** i_reqtype, i_reqdate, i_pmtmethod, i_desturl,
283 ** i_nlslang, i_terminalid
284 **
285 */
286 procedure scheduleCCbatch(i_ecappid iby_batches_all.ecappid%type,
287 i_payeeid iby_batches_all.payeeid%type,
288 i_bepid iby_batches_all.bepid%type,
289 i_bepkey iby_batches_all.bepkey%type,
290 i_batchid iby_batches_all.batchid%type,
291 i_reqtype iby_batches_all.reqtype%type,
292 i_reqdate iby_batches_all.reqdate%type,
293 i_pmtmethod iby_batches_all.paymentmethodname%type,
294 i_desturl iby_batches_all.desturl%type,
295 i_nlslang iby_batches_all.nlslang%type,
296 i_terminalid iby_batches_all.terminalid%type,
297 i_schedDate iby_batches_all.batchopendate%type)
298 IS
299 -- 0011 indicates PENDING status
300 l_status NUMBER := 0011;
301 l_mpayeeid iby_payee.mpayeeid%type;
302 l_mbatchid iby_batches_all.mbatchid%type;
303
304 BEGIN
305 -- call procedure to check if this is duplicate request
306 if (batchExists(i_payeeid, i_batchid, i_reqtype)) then
307 raise_application_error(-20000, 'IBY_20604#', FALSE);
308 --raise_application_error(-20604, 'Duplicate Request ' , FALSE);
309 end if;
310 -- insert the request into the batch table
311
312 SELECT iby_batches_s.NEXTVAL
313 INTO l_mbatchid
314 FROM dual;
315
316 iby_accppmtmthd_pkg.getMPayeeId(i_payeeid, l_mpayeeid);
317 INSERT INTO iby_batches_all
318 (MBatchID, BatchID, MPayeeID, PayeeID, BEPID, bepkey, ECAppID,
319 PaymentMethodName, BatchStatus,
320 ReqType, ReqDate, DestUrl,
321 Nlslang, TerminalID, BatchOpenDate,
322 last_update_date, last_updated_by, creation_date, created_by,
323 last_update_login, object_version_number)
324
325 VALUES
326 (l_mbatchid, i_batchid, l_mpayeeid, i_payeeid, i_bepid, i_bepkey, i_ecappid,
327 i_pmtmethod, l_status,
328 i_reqtype, sysdate, i_desturl,
329 i_nlslang, i_terminalid, i_schedDate,
330 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
331 fnd_global.login_id, 1);
332
333 commit;
334 END scheduleCCbatch;
335
336
337 /*
338 ** Procedure Name : scheduleSET
339 ** Purpose : creates an entry for scheduling the SET CreditCard payments
340 ** in the iby_trxn_summaries_all and iby_trxn_extended
341 ** tables. Returns the transactionid created
342 ** by the system.
343 **
344 ** Parameters:
345 **
346 ** In : i_ecappid, io_transactionid i_amount, i_currency, i_reqdate,
347 ** i_reqtype, i_settledate, i_tangibleid, i_payeeid,
348 ** i_bepid, i_pmtmethod, i_desturl, i_instrid, i_payerid
349 ** Out : io_transactionid.
350 **
351 */
352 procedure scheduleSET( i_ecappid iby_trxn_summaries_all.ecappid%type,
353 i_payeeid iby_trxn_summaries_all.payeeid%type,
354 i_bepid iby_trxn_summaries_all.bepid%type,
355 i_tangibleid iby_trxn_summaries_all.tangibleid%type,
356 i_reqtype iby_trxn_summaries_all.reqtype%type,
357 i_reqdate iby_trxn_summaries_all.reqdate%type,
358 i_reqseq iby_trxn_summaries_all.reqseq%type,
359 i_pmtmethod iby_trxn_summaries_all.paymentmethodname%type,
360 i_desturl iby_trxn_summaries_all.desturl%type,
361 io_transactionid in out nocopy iby_trxn_summaries_all.transactionid%type,
362 i_amount iby_trxn_summaries_all.amount%type,
363 i_currency iby_trxn_summaries_all.currencynamecode%type,
364 i_trxntypeid iby_trxn_summaries_all.trxntypeid%type,
365 i_nlslang iby_trxn_summaries_all.Nlslang%type,
366 i_settledate iby_trxn_summaries_all.settledate%type,
367 i_authtype iby_trxn_core.authtype%type,
368 i_instrid iby_trxn_summaries_all.payerinstrid%type,
369 i_payerid iby_trxn_summaries_all.payerid%type,
370 recurringfreq iby_trxn_extended.RecurringFreq%type,
371 recurringexpdate iby_trxn_extended.RecurringExpDate%type,
372 destpostalcode iby_trxn_extended.DestPostalCode%type,
373 custrefnum iby_trxn_extended.CustRefNum%type,
374 localtaxprice iby_trxn_extended.LocalTaxPrice%type,
375 localtaxcurrency iby_trxn_extended.LocalTaxCurrency%type,
376 authprice iby_trxn_extended.AuthPrice%type,
377 authcurrency iby_trxn_extended.AuthCurrency%type,
378 splitshipment iby_trxn_extended.SplitShipment%type,
379 installtotaltrxns iby_trxn_extended.InstallTotalTrxns%type,
380 splitid iby_trxn_extended.SplitId%type,
381 batchid iby_trxn_summaries_all.BatchId%type,
382 batchseqnum iby_trxn_extended.BatchSeqNum%type,
383 terminalid iby_trxn_extended.TerminalId%type)
384 IS
385
386 l_mid NUMBER;
387 l_tid NUMBER;
388 -- 0011 indicates PENDING status
389 l_status NUMBER := 0011;
390
391 l_mpayeeid iby_payee.mpayeeid%type;
392 l_mbatchid iby_batches_all.mbatchid%type;
393 BEGIN
394 --if (upper(i_reqtype) = 'ORAPMTREQ') then
395 --if ( isPayeeRegistered(i_payeeid, i_bepid ) = false ) then
396 --raise_application_error(-20000, 'IBY_20605#', FALSE);
397 --raise_application_error(-20605,
398 -- 'Can not make Payment, Payee Not Registered With BEP',
399 -- FALSE);
400 --end if;
401 --end if;
402 -- First check if this request is not duplicate
403 if (requestExists(i_payeeid,i_tangibleid, i_reqtype,i_bepid)) then
404 raise_application_error(-20000, 'IBY_20604#', FALSE);
405 --raise_application_error(-20604, 'Duplicate Request ' , FALSE);
406 end if;
407
408 IF ((UPPER(i_reqtype) = 'ORAPMTREQ') OR
409 ( UPPER(i_reqtype) = 'ORAPMTCREDIT'))
410 THEN
411
412 if (i_instrid IS NULL or (NOT instrExists(i_instrid, 'CREDITCARD'))) THEN
413 --reject invalid instrid
414 raise_application_error(-20000, 'IBY_20512#', FALSE);
415 END IF;
416
417 -- Get the transaction id sequence also
418
419 l_tid := iby_transactioncc_pkg.getTID(i_payeeid, i_tangibleid);
420 --SELECT iby_trxnsumm_trxnid_s.NEXTVAL
421 --INTO l_tid
422 --FROM dual;
423 io_transactionid := l_tid;
424 END IF;
425 -- Get the master transaction id sequence for all requests
426
427 SELECT iby_trxnsumm_mid_s.NEXTVAL
428 INTO l_mid
429 FROM dual;
430
431 -- insert the scheduled request in the summary and core tables
432 iby_accppmtmthd_pkg.getMPayeeId(i_payeeid, l_mpayeeid);
433 iby_transactioncc_pkg.getMBatchId(batchid, i_payeeid, l_mbatchid);
434 INSERT INTO iby_trxn_summaries_all
435 (TrxnMID, TransactionID, TangibleID,
436 MPayeeID, PayeeID,BEPID, ECAppID, PaymentMethodName,
437 PayerID, PayerInstrID, Amount,CurrencyNameCode,
438 Status, TrxntypeID, SettleDate, ReqDate, ReqType, ReqSeq, DestUrl,
439 Nlslang, MBatchID, BatchId,
440 last_update_date, updatedate, last_updated_by, creation_date, created_by,
441 last_update_login, object_version_number,needsupdt)
442 VALUES
443 (l_mid, io_transactionid, i_tangibleid,
444 l_mpayeeid, i_payeeid, i_bepid, i_ecappid, i_pmtmethod,
445 i_payerid, i_instrid, i_amount, i_currency,
446 l_status, i_trxntypeid, i_settledate, sysdate, i_reqtype,
447 i_reqseq,i_desturl,
448 i_nlslang, l_mbatchid, batchid,
449 sysdate, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
450 fnd_global.login_id, 1,'Y');
451
452 INSERT INTO iby_trxn_extended
453 (TRXNMID, RecurringFreq, RecurringExpDate, DestPostalCode,
454 CustRefNum, LocalTaxPrice, LocalTaxCurrency,
455 AuthPrice, AuthCurrency, InstallTotalTrxns, SplitShipment, SplitId,
456 BatchSeqNum, TerminalId,
457 last_update_date, last_updated_by, creation_date, created_by,
458 last_update_login, object_version_number)
459 VALUES
460 (l_mid, recurringfreq,recurringexpdate,destpostalcode,
461 custrefnum, localtaxprice, localtaxcurrency,
462 authprice, authcurrency, installtotaltrxns, splitshipment, splitid,
463 batchseqnum, terminalid,
464 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
465 fnd_global.login_id, 1);
466
467 -- insert the authtype into core table
468 INSERT INTO iby_trxn_core
469 (TrxnMID, Authtype,
470 last_update_date, last_updated_by, creation_date, created_by,
471 last_update_login, object_version_number)
472 VALUES
473 (l_mid, i_authtype,
474 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
475 fnd_global.login_id, 1);
476
477 commit;
478 END scheduleSET;
479
480
481 /*
482 ** Procedure Name : scheduleSETCancel
483 ** Purpose : creates an entry for a SET CreditCard cancel request in the
484 ** iby_trxn_summaries_all table
485 */
486 /*********should no longer be used
487 procedure scheduleSETCancel(i_ecappid iby_batches_all.ecappid%type,
488 i_payeeid iby_batches_all.payeeid%type,
489 i_bepid iby_batches_all.bepid%type,
490 i_tangibleid iby_trxn_summaries_all.tangibleid%type,
491 i_reqtype iby_trxn_summaries_all.reqtype%type,
492 i_reqdate iby_trxn_summaries_all.reqdate%type,
493 i_pmtmethod iby_trxn_summaries_all.paymentmethodname%type,
494 i_transactionid in out nocopy iby_trxn_summaries_all.transactionid%type
495 )
496 IS
497 l_mid NUMBER;
498 l_status NUMBER := 0011;
499
500 l_mpayeeid iby_payee.mpayeeid%type;
501 BEGIN
502 -- Get the master transaction id sequence for all requests
503
504 SELECT iby_trxnsumm_mid_s.NEXTVAL
505 INTO l_mid
506 FROM dual;
507
508 iby_accppmtmthd_pkg.getMPayeeId(i_payeeid, l_mpayeeid);
509 INSERT INTO iby_trxn_summaries_all
510 (TrxnMID, TransactionID, TangibleID,
511 MPayeeID, PayeeID,BEPID, ECAppID, PaymentMethodName, status,
512 last_update_date, updatedate, last_updated_by, creation_date, created_by,
513 last_update_login, object_version_number,needsupdt)
514 VALUES
515 (l_mid, i_transactionid, i_tangibleid,
516 l_mpayeeid, i_payeeid, i_bepid, i_ecappid, i_pmtmethod, l_status,
517 sysdate, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
518 fnd_global.login_id, 1,'Y');
519
520 commit;
521 EXCEPTION
522 WHEN NO_DATA_FOUND THEN
523 raise_application_error(-20000, 'IBY_20300#', FALSE);
524 --raise_application_error(-20300,'Cannot insert cancel trxn into iby_trxn_summaries_all');
525
526
527 END scheduleSETCancel;
528 *********/
529
530
531 /*
532 ** Procedure Name : scheduleSETOther
533 ** Purpose : creates an entry for scheduling the SET payments
534 ** in the iby_trxn_summaries_all and iby_trxn_core and extended
535 ** tables. Returns the transactionid created
536 ** by the system.
537 **
538 ** This procedure is used by follow-on transactions (other than cancel) such as
539 ** capture, credit and subsequent auth
540 */
541 procedure scheduleSETOther( i_ecappid iby_trxn_summaries_all.ecappid%type,
542 i_payeeid iby_trxn_summaries_all.payeeid%type,
543 i_bepid iby_trxn_summaries_all.bepid%type,
544 i_tangibleid iby_trxn_summaries_all.tangibleid%type,
545 i_reqtype iby_trxn_summaries_all.reqtype%type,
546 i_reqdate iby_trxn_summaries_all.reqdate%type,
547 i_reqseq iby_trxn_summaries_all.reqseq%type,
548 i_pmtmethod iby_trxn_summaries_all.paymentmethodname%type,
549 i_desturl iby_trxn_summaries_all.desturl%type,
550 io_transactionid in out nocopy iby_trxn_summaries_all.transactionid%type,
551 i_amount iby_trxn_summaries_all.amount%type,
552 i_currency iby_trxn_summaries_all.currencynamecode%type,
553 i_trxntypeid iby_trxn_summaries_all.trxntypeid%type,
554 i_nlslang iby_trxn_summaries_all.Nlslang%type,
555 i_settledate iby_trxn_summaries_all.settledate%type,
556 i_authtype iby_trxn_core.authtype%type,
557 i_instrid iby_trxn_summaries_all.payerinstrid%type,
558 i_payerid iby_trxn_summaries_all.payerid%type,
559 splitid iby_trxn_extended.SplitId%type,
560 prevsplitid iby_trxn_extended.PrevSplitId%type,
561 subauthind iby_trxn_extended.SubAuthInd%type,
562 batchid iby_trxn_summaries_all.BatchId%type,
563 batchseqnum iby_trxn_extended.BatchSeqNum%type,
564 terminalid iby_trxn_extended.TerminalId%type)
565 IS
566
567 l_mid NUMBER;
568 l_tid NUMBER;
569 -- 0011 indicates PENDING status
570 l_status NUMBER := 0011;
571
572 l_mpayeeid iby_payee.mpayeeid%type;
573 l_mbatchid iby_batches_all.mbatchid%type;
574 BEGIN
575 -- First check if this request is not duplicate
576 if (requestExists(i_payeeid,i_tangibleid, i_reqtype,i_bepid)) then
577 raise_application_error(-20000, 'IBY_20604#', FALSE);
578 --raise_application_error(-20604, 'Duplicate Request ' , FALSE);
579 end if;
580
581 IF ((UPPER(i_reqtype) = 'ORAPMTREQ') OR
582 ( UPPER(i_reqtype) = 'ORAPMTCREDIT'))
583 THEN
584
585 if (i_instrid IS NULL or (NOT instrExists(i_instrid, 'CREDITCARD'))) THEN
586 --reject invalid instrid
587 raise_application_error(-20000, 'IBY_20512#', FALSE);
588 END IF;
589
590
591 -- Get the transaction id sequence also
592
593 l_tid := iby_transactioncc_pkg.getTID(i_payeeid, i_tangibleid);
594 --SELECT iby_trxnsumm_trxnid_s.NEXTVAL
595 --INTO l_tid
596 --FROM dual;
597 io_transactionid := l_tid;
598 END IF;
599 -- Get the master transaction id sequence for all requests
600
601 SELECT iby_trxnsumm_mid_s.NEXTVAL
602 INTO l_mid
603 FROM dual;
604 -- insert the scheduled request in the summary and core tables
605 iby_transactioncc_pkg.getMBatchId(batchid, i_payeeid, l_mbatchid);
606 iby_accppmtmthd_pkg.getMPayeeId(i_payeeid, l_mpayeeid);
607 INSERT INTO iby_trxn_summaries_all
608 (TrxnMID, TransactionID, TangibleID,
609 MPayeeID, PayeeID,BEPID, ECAppID, PaymentMethodName,
610 PayerID, PayerInstrID, Amount,CurrencyNameCode,
611 Status, TrxntypeID, SettleDate, ReqDate, ReqType, ReqSeq, DestUrl,
612 Nlslang, MBatchId, BatchId,
613 last_update_date, updatedate, last_updated_by, creation_date, created_by,
614 last_update_login, object_version_number,needsupdt)
615 VALUES
616 (l_mid, io_transactionid, i_tangibleid,
617 l_mpayeeid, i_payeeid, i_bepid, i_ecappid, i_pmtmethod,
618 i_payerid, i_instrid, i_amount, i_currency,
619 l_status, i_trxntypeid, i_settledate, sysdate, i_reqtype,
620 i_reqseq,i_desturl,
621 i_nlslang, l_mbatchid, batchid,
622 sysdate, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
623 fnd_global.login_id, 1,'Y');
624
625 INSERT INTO iby_trxn_extended
626 (TRXNMID, SplitId, PrevSplitId, SubAuthInd,
627 BatchSeqNum, TerminalId,
628 last_update_date, last_updated_by, creation_date, created_by,
629 last_update_login, object_version_number)
630 VALUES
631 (l_mid, splitid, prevsplitid, subauthind,
632 batchseqnum, terminalid,
633 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
634 fnd_global.login_id, 1);
635
636 -- insert the authtype into core table
637 INSERT INTO iby_trxn_core
638 (TrxnMID, Authtype,
639 last_update_date, last_updated_by, creation_date, created_by,
640 last_update_login, object_version_number)
641 VALUES
642 (l_mid, i_authtype,
643 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
644 fnd_global.login_id, 1);
645
646 commit;
647 END scheduleSETOther;
648 /*
649 ** Procedure Name : scheduleSETbatch
650 ** Purpose : creates an entry for scheduling the CreditCard batch requests
651 ** in the iby_batches_all table.
652 **
653 ** Parameters:
654 **
655 ** In : i_ecappid, i_payeeid, i_bepid, i_batchid,
656 ** i_reqtype, i_reqdate, i_pmtmethod,
657 ** i_nlslang, i_terminalid
658 **
659 */
660 procedure scheduleSETbatch(i_ecappid iby_batches_all.ecappid%type,
661 i_payeeid iby_batches_all.payeeid%type,
662 i_bepid iby_batches_all.bepid%type,
663 i_batchid iby_batches_all.batchid%type,
664 i_reqtype iby_batches_all.reqtype%type,
665 i_reqdate iby_batches_all.reqdate%type,
666 i_pmtmethod iby_batches_all.paymentmethodname%type,
667 i_desturl iby_batches_all.desturl%type,
668 i_nlslang iby_batches_all.nlslang%type,
669 i_terminalid iby_batches_all.terminalid%type)
670 IS
671
672 l_mpayeeid iby_payee.mpayeeid%type;
673 l_mbatchid iby_batches_all.mbatchid%type;
674 BEGIN
675 -- call procedure to check if this is duplicate request
676 if (batchExists(i_payeeid, i_batchid, i_reqtype)) then
677 raise_application_error(-20000, 'IBY_20604#', FALSE);
678 --raise_application_error(-20604, 'Duplicate Request ' , FALSE);
679 end if;
680
681 -- insert the request into the batch table
682 SELECT iby_batches_s.NEXTVAL
683 INTO l_mbatchid
684 FROM dual;
685 iby_accppmtmthd_pkg.getMPayeeId(i_payeeid, l_mpayeeid);
686 INSERT INTO iby_batches_all
687 (MBatchID, BatchID, MPayeeID, PayeeID, BEPID, ECAppID,
688 ReqType, ReqDate, DestUrl, PaymentMethodName,
689 Nlslang, TerminalID,
690 last_update_date, last_updated_by, creation_date, created_by,
691 last_update_login, object_version_number)
692 VALUES
693 (l_mbatchid, i_batchid, l_mpayeeid, i_payeeid, i_bepid, i_ecappid,
694 i_reqtype, sysdate, i_desturl, i_pmtmethod,
695 i_nlslang, i_terminalid,
696 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
697 fnd_global.login_id, 1);
698
699
700 commit;
701 END scheduleSETbatch;
702
703
704
705 FUNCTION requestExists(i_payeeid iby_trxn_summaries_all.payeeid%type,
706 i_tangibleid iby_trxn_summaries_all.tangibleid%type,
707 i_reqtype iby_trxn_summaries_all.reqtype%type,
708 i_bepid iby_trxn_summaries_all.bepid%type)
709 return boolean
710 is
711 l_cnt int;
712 BEGIN
713 /*
714 ** get the count of the rows with same request type, tangibleid and
715 ** payeeid. If count is more than 0, it means, request already exists
716 ** otherwise does not exist.
717 */
718 SELECT count(*)
719 INTO l_cnt
720 FROM iby_trxn_summaries_all
721 WHERE payeeId = i_payeeid
722 AND tangibleid = i_tangibleid
723 AND UPPER(reqType) = UPPER(i_reqtype)
724 AND bepid = i_bepid
725 --AND ( status = 11 or status = 0 );
726 AND STATUS IN ( 0, 11, 12, 13, 18);
727
728 if (l_cnt > 0) then
729 return true;
730 end if;
731
732 -- count is 0
733 -- check for capture case
734
735 if (i_reqtype = 'ORAPMTCAPTURE') then
736 SELECT count(*)
737 INTO l_cnt
738 FROM iby_trxn_summaries_all a, iby_trxn_core b
739 WHERE payeeId = i_payeeid
740 AND tangibleid = i_tangibleid
741 AND UPPER(reqType) = 'ORAPMTREQ'
742 AND bepid = i_bepid
743 AND a.trxnmid = b.trxnmid
744 AND b.authtype = 'AUTHANDCAPTURE'
745 AND ( status = 11); -- didn't need status 0
746
747 if (l_cnt > 0) then
748 return true;
749 end if;
750 end if;
751
752 return false;
753
754 END requestExists;
755
756
757 FUNCTION batchExists(i_payeeid iby_batches_all.payeeid%type,
758 i_batchid iby_batches_all.batchid%type,
759 i_reqtype iby_batches_all.reqtype%type)
760 return boolean
761 is
762 l_cnt int;
763 BEGIN
764 /*
765 ** get the count of the rows with same request type, tangibleid and
766 ** payeeid. If count is more than 0, it means, request already exists
767 ** otherwise does not exist.
768 */
769 SELECT count(*)
770 INTO l_cnt
771 FROM iby_batches_all
772 WHERE payeeId = i_payeeId
773 AND batchId = i_batchid
774 AND UPPER(reqType) = UPPER(i_reqType)
775 AND (batchstatus = 11 or batchstatus = 0);
776 if ( l_cnt = 0 ) then
777 return false;
778 else
779 return true;
780 end if;
781 END batchExists;
782
783
784 /*
785 ** Function: instrExists.
786 ** Purpose: Check if the specified instrid exists or not.
787 */
788 function instrExists(i_instrid in
789 iby_trxn_summaries_all.payerinstrid%type,
790 i_instrtype in iby_trxn_summaries_all.instrtype%type)
791
792 return boolean
793
794 IS
795 l_instrid iby_creditcard.instrid%type;
796 l_flag boolean := false;
797
798 cursor c_cc
799 (ci_instrid iby_creditcard.instrid%type)
800 is
801 SELECT instrid
802 FROM iby_creditcard_v
803 WHERE instrid = ci_instrid;
804
805
806 cursor c_pc
807 (ci_instrid iby_creditcard.instrid%type)
808 is
809 SELECT instrid
810 FROM iby_purchasecard_v
811 WHERE instrid = ci_instrid;
812
813 begin
814 if ( c_pc%isopen) then
815 close c_pc;
816 end if;
817
818 IF i_instrtype = 'CREDITCARD' THEN
819 if ( c_cc%isopen) then
820 close c_cc;
821 end if;
822 open c_cc( i_instrid);
823 fetch c_cc into l_instrid;
824 l_flag := c_cc%found;
825
826 close c_cc;
827 ELSE
828 if ( c_pc%isopen) then
829 close c_pc;
830 end if;
831 open c_pc( i_instrid);
832
833 fetch c_pc into l_instrid;
834 l_flag := c_pc%found;
835
836 close c_pc;
837 END IF;
838
839 return l_flag;
840 end instrExists;
841
842
843 END iby_offlinedb_pkg;