[Home] [Help]
PACKAGE BODY: APPS.IBY_TRANSACTIONSET_PKG
Source
1 PACKAGE BODY iby_transactionSET_pkg AS
2 /*$Header: ibytxstb.pls 120.2.12010000.2 2008/07/29 05:53:23 sugottum ship $*/
3
4 /* This procedure would be used every time a SET INIT transaction */
5 /* occurred. Since INIT is not idempotent, this procedure only */
6 /* inserts a new row into the transactions table after the vendor */
7 /* application has returned. It does not perform any error checking */
8 /* to make sure that the row already exists since the programmer */
9 /* should have called queryset.listvendor to check beforehand. */
10 PROCEDURE insert_init_txn
11 (ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
12 req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
13 order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
14 merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
15 vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
16 trxn_type_in IN OUT NOCOPY iby_trxn_summaries_all.TrxntypeID%TYPE,
17 payment_name_in IN iby_trxn_core.InstrName%TYPE,
18 price_in IN iby_trxn_summaries_all.Amount%TYPE,
19 currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
20 time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
21 status_in IN iby_trxn_summaries_all.Status%TYPE,
22 transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
23 vendor_code_in IN iby_trxn_summaries_all.BEPCode%TYPE,
24 vendor_message_in IN iby_trxn_summaries_all.BEPMessage%TYPE,
25 error_location_in IN iby_trxn_summaries_all.ErrorLocation%TYPE,
26 billeracct_in IN iby_tangible.acctno%type,
27 refinfo_in IN iby_tangible.refinfo%type,
28 memo_in IN iby_tangible.memo%type,
29 order_medium_in IN iby_tangible.order_medium%TYPE,
30 eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
31 org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
32 payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
33 instrnum_in IN iby_trxn_summaries_all.instrnumber%type)
34 IS
35 num_trxns NUMBER := 0;
36 err_msg VARCHAR2(100);
37 trxn_mid NUMBER;
38 transaction_id NUMBER;
39 l_tmid iby_trxn_summaries_all.mtangibleid%type;
40 l_mpayeeid iby_payee.mpayeeid%type;
41
42 BEGIN
43 -- Check trxn_type
44 IF ((trxn_type_in = '') OR
45 (trxn_type_in IS NULL))
46 THEN
47 trxn_type_in := 0;
48 END IF;
49 -- Count number of previous transactions
50 SELECT count(*)
51 INTO num_trxns
52 FROM iby_trxn_summaries_all
53 WHERE TangibleID = order_id_in
54 AND UPPER(ReqType) = UPPER(req_type_in)
55 AND PayeeID = merchant_id_in;
56 IF (num_trxns = 0)
57 THEN
58 -- Everything is fine, insert into table
59 SELECT iby_trxnsumm_mid_s.NEXTVAL
60 INTO trxn_mid
61 FROM dual;
62 IF ((transaction_id_in_out = '') OR
63 (transaction_id_in_out = -1) OR
64 (transaction_id_in_out IS NULL))
65 THEN
66 SELECT iby_trxnsumm_trxnid_s.NEXTVAL
67 INTO transaction_id
68 FROM dual;
69 transaction_id_in_out := transaction_id;
70 ELSE
71 transaction_id := transaction_id_in_out;
72 END IF;
73
74
75 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
76
77
78 -- Create a new entry in iby_tangible table
79 iby_bill_pkg.createBill(order_id_in,price_in,currency_in,
80 billeracct_in,refinfo_in, memo_in,
81 order_medium_in, eft_auth_method_in,
82 l_tmid);
83
84 INSERT INTO iby_trxn_summaries_all
85 (TrxnMID, TransactionID,TrxntypeID, ECAPPID, org_id,
86 ReqType, ReqDate,
87 Amount,CurrencyNameCode, UpdateDate,Status,
88 TangibleID,MPayeeID, PayeeID,BEPID,MtangibleId,
89 BEPCode,BEPMessage,Errorlocation,
90 payerinstrid, instrnumber,
91 last_update_date, last_updated_by,
92 creation_date, created_by,
93 last_update_login, object_version_number,needsupdt)
94
95 VALUES (trxn_mid, transaction_id, trxn_type_in, ecapp_id_in,
96 org_id_in,
97 req_type_in, time_in,
98 price_in, currency_in, time_in, status_in,
99 order_id_in, l_mpayeeid, merchant_id_in, vendor_id_in,l_tmid,
100 vendor_code_in, vendor_message_in, error_location_in,
101 payerinstrid_in, instrnum_in,
102 sysdate, fnd_global.user_id,
103 sysdate, fnd_global.user_id,
104 fnd_global.login_id, 1,'Y');
105
106 INSERT INTO iby_trxn_extended
107 (TrxnMID, SplitID,
108 last_update_date, last_updated_by,
109 creation_date, created_by,
110 last_update_login, object_version_number)
111 VALUES (trxn_mid, '1',
112 sysdate, fnd_global.user_id,
113 sysdate, fnd_global.user_id,
114 fnd_global.login_id, 1);
115
116 ELSIF (num_trxns = 1)
117 THEN
118 -- One previous transaction, so update previous row
119 SELECT TrxnMID, TransactionID,MtangibleId
120 INTO trxn_mid, transaction_id_in_out,l_tmid
121 FROM iby_trxn_summaries_all
122 WHERE TangibleID = order_id_in
123 AND UPPER(ReqType) = UPPER(req_type_in)
124 AND PayeeID = merchant_id_in;
125
126 --Update iby_tangible table
127 iby_bill_pkg.modBill(l_tmid,order_id_in,price_in,currency_in,
128 billeracct_in,refinfo_in,memo_in,
129 order_medium_in, eft_auth_method_in);
130
131 UPDATE iby_trxn_summaries_all
132 SET Amount = price_in,
133 CurrencyNameCode = currency_in,
134 --ReqDate = time_in,
135 updatedate = time_in,
136 Status = status_in,
137 ErrorLocation = error_location_in,
138 BEPCode = vendor_code_in,
139 BEPMessage = vendor_message_in,
140
141 payerinstrid = payerinstrid_in,
142 instrnumber = instrnum_in,
143
144 last_update_date = sysdate,
145 last_updated_by = fnd_global.user_id,
146 last_update_login = fnd_global.login_id,
147 object_version_number = 1
148 WHERE TrxnMID = trxn_mid;
149
150 UPDATE iby_trxn_extended
151 SET SplitID = '1',
152 last_update_date = sysdate,
153 last_updated_by = fnd_global.user_id,
154 last_update_login = fnd_global.login_id,
155 object_version_number = 1
156 WHERE TrxnMID = trxn_mid;
157
158 ELSE
159 raise_application_error(-20000, 'IBY_20401#', FALSE);
160 --raise_application_error(-20401,'Duplicate invoice transactions for this order_id');
161 END IF;
162 COMMIT;
163 EXCEPTION
164
165 WHEN OTHERS THEN
166 err_msg := SUBSTR(SQLERRM, 1, 100);
167 raise_application_error(-20000, 'IBY_20400#', FALSE);
168 --raise_application_error(-20400,'Error while inserting/updating invoice transaction: '||err_msg);
169 END insert_init_txn;
170
171
172
173 /* This procedure would be used every time a SET SET */
174 /* transaction occurred. Since SET is idempotent, the procedure */
175 /* checks to see if the row already exists based upon order_id, */
176 /* merchant_id, trxn_type, and request_type. */
177 PROCEDURE insert_set_txn
178 (ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
179 req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
180 order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
181 merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
182 vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
183 trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
184 split_id_in IN iby_trxn_extended.SplitID%TYPE,
185 payment_name_in IN iby_trxn_core.InstrName%TYPE,
186 price_in IN iby_trxn_summaries_all.Amount%TYPE,
187 currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
188 time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
189 status_in IN iby_trxn_summaries_all.Status%TYPE,
190 authcode_in IN iby_trxn_core.AuthCode%TYPE,
191 capcode_in IN iby_trxn_core.OperationCode%TYPE,
192 completion_code_in IN iby_trxn_extended.CompletionCode%TYPE,
193 set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
194 batch_id_in IN iby_trxn_summaries_all.batchID%TYPE,
195 batch_seq_num_in IN iby_trxn_extended.BatchSeqNum%TYPE,
196 AVS_result_in IN iby_trxn_core.AVSCode%TYPE,
197 ret_ref_num_in IN iby_trxn_core.ReferenceCode%TYPE,
198 card_BIN_in IN iby_trxn_extended.Cardbin%TYPE,
199 terminal_id_in IN iby_trxn_extended.TerminalID%TYPE,
200 request_type_in IN iby_trxn_extended.SETReqType%TYPE,
201 subseq_auth_ind_in IN iby_trxn_extended.SubAuthInd%TYPE,
202 transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
203 payment_method_in IN iby_trxn_summaries_all.PAYMENTMETHODNAME%TYPE,
204 vendor_code_in IN iby_trxn_summaries_all.BEPCode%TYPE,
205 vendor_message_in IN iby_trxn_summaries_all.BEPMessage%TYPE,
206 error_location_in IN iby_trxn_summaries_all.ErrorLocation%TYPE,
207 billeracct_in IN iby_tangible.acctno%type ,
208 refinfo_in IN iby_tangible.refinfo%type,
209 memo_in IN iby_tangible.memo%type ,
210 order_medium_in IN iby_tangible.order_medium%TYPE,
211 eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
212 org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
213 payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
214 instrnum_in IN iby_trxn_summaries_all.instrnumber%type)
215 IS
216
217 num_trxns NUMBER;
218 counter NUMBER;
219 oper_code iby_trxn_core.OperationCode%TYPE;
220 err_msg VARCHAR2(100);
221 trxn_mid NUMBER;
222 transaction_id NUMBER;
223 l_tmid iby_trxn_summaries_all.mtangibleid%type;
224 l_order_id iby_trxn_summaries_all.tangibleid%type;
225
226 l_mpayeeid iby_payee.mpayeeid%type;
227 l_mbatchid iby_batches_all.mbatchid%type;
228 BEGIN
229 -- Initialize variables
230 num_trxns := 0;
231
232 -- Check request type values. We don't store Error PDU,
233 -- PINQ in database.
234 -- 0 = Error PDU, 1 = PINIT, 2 = PREQ, 3 = PINQ, 4 = SSL
235 IF ((request_type_in <> 0) AND
236 (request_type_in <> 1) AND
237 (request_type_in <> 2) AND
238 (request_type_in <> 3) AND
239 (request_type_in <> 4))
240 THEN
241 raise_application_error(-20000, 'IBY_20411#', FALSE);
242 --raise_application_error(-20411,'Invalid request type value returned from the payment system: '||request_type_in);
243 END IF;
244 -- Check transaction type to set operation_code
245 IF (trxn_type_in = 2)
246 THEN
247 -- Trxn is an AUTHONLY, so authcode is operation code
248 -- and there is no capcode
249 oper_code := authcode_in;
250 ELSIF (trxn_type_in = 3)
251 THEN
252 -- Trxn is an AUTHCAPTURE, so capcode is the operation_code.
253 -- If authcode is present, concatenate them so that the value
254 -- of operation_code is CAPCODE-AUTHCODE.
255 IF ((authcode_in = '') OR (authcode_in IS NULL))
256 THEN
257 oper_code := capcode_in;
258 ELSE
259 oper_code := capcode_in||'-'||authcode_in;
260 END IF;
261 ELSE
262 -- Incorrect transaction type
263 raise_application_error(-20000, 'IBY_20412#', FALSE);
264 --raise_application_error(-20412,'Invalid authorization type -- must be AUTHONLY 2 or AUTHCAPTURE 3');
265 END IF;
266 -- Check value of subseq_auth_ind
267 IF ((subseq_auth_ind_in <> 0) AND
268 (subseq_auth_ind_in <> 1))
269 THEN
270 raise_application_error(-20000, 'IBY_20413#', FALSE);
271 --raise_application_error(-20413,'Missing follow-on indicator');
272 END IF;
273 --Check for idempotency: see if transaction already
274 -- exists
275 SELECT count(*)
276 INTO num_trxns
277 FROM iby_trxn_summaries_all
278 WHERE TangibleID = order_id_in
279 AND UPPER(ReqType) = UPPER(req_type_in)
280 AND PayeeID = merchant_id_in;
281 IF (num_trxns = 0)
282 THEN
283
284
285 -- No previous transaction, so insert new row
286 -- generate trxn_mid, TransactionID
287 SELECT iby_trxnsumm_mid_s.NEXTVAL
288 INTO trxn_mid
289 FROM dual;
290
291
292 IF ((transaction_id_in_out = '') OR
293 (transaction_id_in_out IS NULL) OR
294 (transaction_id_in_out = '-1'))
295 THEN
296
297 SELECT count(*)
298 INTO counter
299 FROM iby_trxn_summaries_all
300 WHERE TangibleID = order_id_in
301 AND PayeeID = merchant_id_in;
302
303
304 IF (counter = 0)
305 THEN
306
307 SELECT iby_trxnsumm_trxnid_s.NEXTVAL
308 INTO transaction_id
309 FROM dual;
310
311 ELSE
312 SELECT DISTINCT TransactionId
316 AND PayeeID = merchant_id_in;
313 INTO transaction_id
314 FROM iby_trxn_summaries_all
315 WHERE TangibleID = order_id_in
317
318 END IF;
319
320 transaction_id_in_out := transaction_id;
321
322 -- create new tangible in such cases
323 iby_bill_pkg.createBill(order_id_in,price_in,currency_in,
324 billeracct_in,refinfo_in, memo_in,
325 order_medium_in, eft_auth_method_in,
326 l_tmid);
327 l_order_id := order_id_in;
328
329 ELSE
330 --tangible info should already exist, get them based on
331 --transactionid
332 SELECT DISTINCT mtangibleid, tangibleid
333 INTO l_tmid, l_order_id
334 FROM iby_trxn_summaries_all
335 WHERE transactionid = transaction_id_in_out;
336
337 transaction_id := transaction_id_in_out;
338 END IF;
339 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
340
341 INSERT INTO iby_trxn_summaries_all
342 (TrxnMID, TransactionID,TrxntypeID,
343 ECAPPID, org_id, ReqType, ReqDate, MtangibleId,
344 Amount,CurrencyNameCode, TangibleID,MPayeeID, PayeeID,BEPID,
345 BEPCode, BEPMessage,Errorlocation,PAYMENTMETHODNAME,status,
346 payerinstrid, instrnumber,
347 last_update_date, updatedate, last_updated_by,
348 creation_date, created_by,
349 last_update_login, object_version_number,needsupdt)
350 VALUES (trxn_mid, transaction_id, trxn_type_in,
351 ecapp_id_in, org_id_in, req_type_in, sysdate, l_tmid,
352 price_in, currency_in, l_order_id, l_mpayeeid,
353 merchant_id_in, vendor_id_in,
354 vendor_code_in, vendor_message_in, error_location_in,
355 payment_method_in,status_in,
356 payerinstrid_in, instrnum_in,
357 sysdate, sysdate, fnd_global.user_id,
358 sysdate, fnd_global.user_id,
359 fnd_global.login_id, 1,'Y');
360
361 INSERT INTO iby_trxn_core
362 (TrxnMID, OperationCode, AVSCode, ReferenceCode,
363 last_update_date, last_updated_by,
364 creation_date, created_by,
365 last_update_login, object_version_number)
366 VALUES (trxn_mid, oper_code, avs_result_in, ret_ref_num_in,
367 sysdate, fnd_global.user_id,
368 sysdate, fnd_global.user_id,
369 fnd_global.login_id, 1);
370
371 INSERT INTO iby_trxn_extended
372 (TrxnMID, SplitID, CompletionCode, SETTrxnID,
373 BatchSeqNum,
374 Cardbin, TerminalID, SETReqType, SubAuthInd,
375 last_update_date, last_updated_by,
376 creation_date, created_by,
377 last_update_login, object_version_number)
378 VALUES
379 (trxn_mid, '1', completion_code_in, set_trxn_id_in,
380 batch_seq_num_in,
381 card_bin_in, terminal_id_in, request_type_in, subseq_auth_ind_in,
382 sysdate, fnd_global.user_id,
383 sysdate, fnd_global.user_id,
384 fnd_global.login_id, 1);
385
386 ELSIF (num_trxns = 1)
387 THEN
388 -- One previous transaction, so update previous row
389 SELECT TrxnMID, TransactionID,MtangibleId
390 INTO trxn_mid, transaction_id_in_out,l_tmid
391 FROM iby_trxn_summaries_all
392 WHERE TangibleID = order_id_in
393 AND UPPER(ReqType) = UPPER(req_type_in)
394 AND PayeeID = merchant_id_in;
395
396 iby_transactioncc_pkg.getMBatchID(batch_id_in, merchant_id_in,
397 l_mbatchid);
398
399
400 IF (UPPER(req_type_in) = 'ORAPMTCREDIT') THEN
401 -- Update iby_tangible table
402 iby_bill_pkg.modBill(l_tmid,order_id_in,price_in,currency_in,
403 billeracct_in,refinfo_in,memo_in,
404 order_medium_in, eft_auth_method_in);
405 END IF;
406
407 UPDATE iby_trxn_summaries_all
408 SET Amount = price_in,
409 CurrencyNameCode = currency_in,
410 UpdateDate = time_in,
411 Status = status_in,
412 ErrorLocation = error_location_in,
413 BEPCode = vendor_code_in,
414 BEPMessage = vendor_message_in,
415 BatchID = batch_id_in,
416 MBatchID = l_mbatchid,
417 payerinstrid = payerinstrid_in,
418 instrnumber = instrnum_in,
419 last_update_date = sysdate,
420 last_updated_by = fnd_global.user_id,
421 last_update_login = fnd_global.login_id,
422 object_version_number = 1
423 WHERE TrxnMID = trxn_mid;
424
425 UPDATE iby_trxn_core
426 SET OperationCode = oper_code,
427 AvsCode = avs_result_in,
428 ReferenceCode = ret_ref_num_in,
429 last_update_date = sysdate,
430 last_updated_by = fnd_global.user_id,
431 last_update_login = fnd_global.login_id,
432 object_version_number = 1
433 WHERE TrxnMID = trxn_mid;
434
435 UPDATE iby_trxn_extended
436 SET SplitID = '1',
437 CompletionCode = completion_code_in,
438 SETTrxnID = set_trxn_id_in,
439 BatchSeqNum = batch_seq_num_in,
440 Cardbin = card_bin_in,
441 TerminalID = terminal_id_in,
442 SETReqType = request_type_in,
443 SubAuthInd = subseq_auth_ind_in,
444 last_update_date = sysdate,
448 WHERE TrxnMID = trxn_mid;
445 last_updated_by = fnd_global.user_id,
446 last_update_login = fnd_global.login_id,
447 object_version_number = 1
449
450 ELSE
451 -- More than one previous transaction, which is an
452 -- error
453 raise_application_error(-20000, 'IBY_20414#', FALSE);
454 --raise_application_error(-20414, 'Multiple matching authorization transactions');
455 END IF;
456
457 COMMIT;
458 EXCEPTION
459
460 WHEN OTHERS THEN
461 err_msg := SUBSTR(SQLERRM, 1, 100);
462 raise_application_error(-20000, 'IBY_20410#', FALSE);
463 --raise_application_error(-20410,'Error while inserting/updating authorization transaction: '||err_msg);
464
465 END insert_set_txn;
466
467 /* This procedure would be used every time a SET AUTH or AUTHREV */
468 /* transaction occurred. Since AUTH and AUTHREV are idempotent, the */
469 /* procedure checks to see if the row already exists based upon */
470 /* order_id, merchant_id, and split_id. */
471 PROCEDURE insert_auth_txn
472 (ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
473 req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
474 order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
475 merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
476 vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
477 trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
478 split_id_in IN iby_trxn_extended.SplitID%TYPE,
479 payment_name_in IN iby_trxn_summaries_all.PaymentMethodName%TYPE,
480 price_in IN iby_trxn_summaries_all.Amount%TYPE,
481 currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
482 time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
483 status_in IN iby_trxn_summaries_all.Status%TYPE,
484 authcode_in IN iby_trxn_core.AuthCode%TYPE,
485 capcode_in IN iby_trxn_core.OperationCode%TYPE,
486 completion_code_in IN iby_trxn_extended.CompletionCode%TYPE,
487 set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
488 batch_id_in IN OUT NOCOPY iby_trxn_summaries_all.batchID%TYPE,
489 batch_seq_num_in IN OUT NOCOPY iby_trxn_extended.BatchSeqNum%TYPE,
490 AVS_result_in IN OUT NOCOPY iby_trxn_core.AVSCode%TYPE,
491 ret_ref_num_in IN iby_trxn_core.ReferenceCode%TYPE,
492 card_BIN_in IN OUT NOCOPY iby_trxn_extended.Cardbin%TYPE,
493 terminal_id_in IN OUT NOCOPY iby_trxn_extended.TerminalID%TYPE,
494 subseq_auth_ind_in IN iby_trxn_extended.SubAuthInd%TYPE,
495 vendor_code_in IN OUT NOCOPY iby_trxn_summaries_all.BEPCode%TYPE,
496 vendor_message_in IN OUT NOCOPY iby_trxn_summaries_all.BEPMessage%TYPE,
497 error_location_in IN OUT NOCOPY iby_trxn_summaries_all.ErrorLocation%TYPE,
498 transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
499 payment_method_in IN
500 iby_trxn_summaries_all.PAYMENTMETHODNAME%TYPE,
501 billeracct_in IN iby_tangible.acctno%type,
502 refinfo_in IN iby_tangible.refinfo%type,
503 memo_in IN iby_tangible.memo%type,
504 order_medium_in IN iby_tangible.order_medium%TYPE,
505 eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
506 org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
507 payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
508 instrnum_in IN iby_trxn_summaries_all.instrnumber%type)
509 IS
510 num_trxns NUMBER := 0;
511 oper_code iby_trxn_core.OperationCode%TYPE;
512 err_msg VARCHAR2(100);
513 trxn_mid NUMBER;
514 transaction_id NUMBER;
515 l_tmid iby_trxn_summaries_all.mtangibleid%type;
516 l_mpayeeid iby_payee.mpayeeid%type;
517 l_mbatchid iby_batches_all.mbatchid%type;
518 BEGIN
519 -- NULL optional parameters if they are empty string
520 IF (batch_id_in = '')
521 THEN
522 batch_id_in := null;
523 END IF;
524 IF (batch_seq_num_in = '')
525 THEN
526 batch_seq_num_in := null;
527 END IF;
528 IF (card_BIN_in = '')
529 THEN
530 card_BIN_in := null;
531 END IF;
532 IF (terminal_id_in = '')
533 THEN
534 terminal_id_in := null;
535 END IF;
536 IF (error_location_in = '')
537 THEN
538 error_location_in := null;
539 END IF;
540 IF (vendor_code_in = '')
541 THEN
542 vendor_code_in := null;
543 END IF;
544 -- Set operation_code
545 IF ((capcode_in = '') OR (capcode_in IS NULL))
546 THEN
547 -- Since there is no capcode, trxn must be an AUTHONLY
548 oper_code := authcode_in;
549 ELSIF ((authcode_in = '') OR (authcode_in IS NULL))
550 THEN
551 -- There is capcode but no authcode, trxn is an
552 -- AUTHCAPTURE
553 oper_code := capcode_in;
554 ELSE
555 -- Both authcode and capcode exists, trxn is an
556 -- AUTHCAPTURE, so operation code must be CAPCODE-AUTHCODE
557 oper_code := capcode_in||'-'||authcode_in;
558 END IF;
559 -- Check value of subseq_auth_ind
560 IF ((subseq_auth_ind_in <> 0) AND
564 --raise_application_error(-20421,'Invalid subsequent authorization indicator: '|| subseq_auth_ind_in);
561 (subseq_auth_ind_in <> 1))
562 THEN
563 raise_application_error(-20000, 'IBY_20421#', FALSE);
565 END IF;
566 -- Check for idempotency: see if transaction already
567 -- exists
568 SELECT count(*)
569 INTO num_trxns
570 FROM iby_trxn_summaries_all
571 WHERE TangibleID = order_id_in
572 AND UPPER(ReqType) = UPPER(req_type_in)
573 AND PayeeID = merchant_id_in;
574 IF (num_trxns = 0)
575 THEN
576 -- No previous transaction, so insert new row
577 -- generate trxn_id and transaction_id
578 SELECT iby_trxnsumm_mid_s.NEXTVAL
579 INTO trxn_mid
580 FROM dual;
581 IF ((transaction_id_in_out = '') OR
582 (transaction_id_in_out IS NULL))
583 THEN
584 SELECT iby_trxnsumm_trxnid_s.NEXTVAL
585 INTO transaction_id
586 FROM dual;
587 transaction_id_in_out := transaction_id;
588 ELSE
589 transaction_id := transaction_id_in_out;
590 END IF;
591
592 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
593 iby_transactioncc_pkg.getMBatchID(batch_id_in, merchant_id_in,
594 l_mbatchid);
595 /*
596 ** Create an entry in iby_tangible table
597 */
598 iby_bill_pkg.createBill(order_id_in,price_in,currency_in,
599 billeracct_in,refinfo_in, memo_in,
600 order_medium_in, eft_auth_method_in,
601 l_tmid);
602
603 INSERT INTO iby_trxn_summaries_all
604 (TrxnMID, TangibleID,MPayeeID, PayeeID,BEPID, PaymentMethodName,
605 TransactionID,TrxntypeID, ECAPPID, org_id,
606 ReqType, ReqDate, MtangibleId,
607 Amount,CurrencyNameCode,
608 UpdateDate,Status,MBatchID, BatchID,
609 BEPCode, BEPMessage,Errorlocation,
610 payerinstrid, instrnumber,
611 last_update_date, last_updated_by,
612 creation_date, created_by,
613 last_update_login, object_version_number,needsupdt)
614 VALUES (trxn_mid, order_id_in, l_mpayeeid, merchant_id_in, vendor_id_in,
615 payment_method_in,
616 transaction_id, trxn_type_in, ecapp_id_in, org_id_in,
617 req_type_in, time_in, l_tmid,
618 price_in, currency_in, time_in, status_in, l_mbatchid,
619 batch_id_in,
620 vendor_code_in, vendor_message_in, error_location_in,
621 payerinstrid_in, instrnum_in,
622 sysdate, fnd_global.user_id,
623 sysdate, fnd_global.user_id,
624 fnd_global.login_id, 1,'Y');
625
626 INSERT INTO iby_trxn_core
627 (TrxnMID, OperationCode, AVSCode, ReferenceCode,
628 last_update_date, last_updated_by,
629 creation_date, created_by,
630 last_update_login, object_version_number)
631 VALUES (trxn_mid, oper_code, avs_result_in, ret_ref_num_in,
632 sysdate, fnd_global.user_id,
633 sysdate, fnd_global.user_id,
634 fnd_global.login_id, 1);
635
636 INSERT INTO iby_trxn_extended
637 (TrxnMID, SplitID, CompletionCode, SETTrxnID,
638 BatchSeqNum,
639 Cardbin, TerminalID, SubAuthInd,
640 last_update_date, last_updated_by,
641 creation_date, created_by,
642 last_update_login, object_version_number)
643 VALUES
644 (trxn_mid, split_id_in, completion_code_in, set_trxn_id_in,
645 batch_seq_num_in,
646 card_bin_in, terminal_id_in, subseq_auth_ind_in,
647 sysdate, fnd_global.user_id,
648 sysdate, fnd_global.user_id,
649 fnd_global.login_id, 1);
650
651 ELSIF (num_trxns = 1)
652 THEN
653 -- One previous transaction, so update previous row
654 SELECT TrxnMID, TransactionID,MtangibleId
655 INTO trxn_mid, transaction_id_in_out, l_tmid
656 FROM iby_trxn_summaries_all
657 WHERE TangibleID = order_id_in
658 AND UPPER(ReqType) = UPPER(req_type_in)
659 AND PayeeID = merchant_id_in;
660
661 iby_transactioncc_pkg.getMBatchID(batch_id_in, merchant_id_in,
662 l_mbatchid);
663
664 --Update iby_tangible table
665 iby_bill_pkg.modBill(l_tmid,order_id_in,price_in,currency_in,
666 billeracct_in,refinfo_in,memo_in,
667 order_medium_in, eft_auth_method_in);
668
669
670 UPDATE iby_trxn_summaries_all
671 SET Amount = price_in,
672 CurrencyNameCode = currency_in,
673 UpdateDate = time_in,
674 Status = status_in,
675 ErrorLocation = error_location_in,
676 BEPCode = vendor_code_in,
677 BEPMessage = vendor_message_in,
678 BatchID = batch_id_in,
679 MBatchID = l_mbatchid,
680 payerinstrid = payerinstrid_in,
681 instrnumber = instrnum_in,
682 last_update_date = sysdate,
683 last_updated_by = fnd_global.user_id,
684 last_update_login = fnd_global.login_id,
685 object_version_number = 1
686 WHERE TrxnMID = trxn_mid;
687
688 UPDATE iby_trxn_core
689 SET OperationCode = oper_code,
690 AvsCode = avs_result_in,
691 ReferenceCode = ret_ref_num_in,
692 last_update_date = sysdate,
696 WHERE TrxnMID = trxn_mid;
693 last_updated_by = fnd_global.user_id,
694 last_update_login = fnd_global.login_id,
695 object_version_number = 1
697
698 UPDATE iby_trxn_extended
699 SET SplitID = split_id_in,
700 CompletionCode = completion_code_in,
701 SETTrxnID = set_trxn_id_in,
702 BatchSeqNum = batch_seq_num_in,
703 Cardbin = card_bin_in,
704 TerminalID = terminal_id_in,
705 SubAuthInd = subseq_auth_ind_in,
706 last_update_date = sysdate,
707 last_updated_by = fnd_global.user_id,
708 last_update_login = fnd_global.login_id,
709 object_version_number = 1
710 WHERE TrxnMID = trxn_mid;
711
712 ELSE
713 -- More than one previous transaction, which is an
714 -- error
715 raise_application_error(-20000, 'IBY_20422#', FALSE);
716 --raise_application_error(-20422, 'Multiple matching subsequent auth transactions');
717 END IF;
718 COMMIT;
719 EXCEPTION
720
721 WHEN OTHERS THEN
722 err_msg := SUBSTR(SQLERRM, 1, 100);
723 raise_application_error(-20000, 'IBY_20420#', FALSE);
724 --raise_application_error(-20420,'Error while inserting/updating subsequent auth transaction: '||err_msg);
725 END insert_auth_txn;
726
727
728 /* Inserts a new row into the PS_TRANSACTIONS table. This method */
729 /* would be called every time a SET CAPTURE, CAPTUREREV, CREDIT, or */
730 /* CREDITREV operation is performed. */
731
732 PROCEDURE insert_other_txn
733 (ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
734 req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
735 order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
736 merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
737 vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
738 trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
739 split_id_in IN iby_trxn_extended.SplitID%TYPE,
740 payment_name_in IN iby_trxn_summaries_all.PAYMENTMETHODNAME%TYPE,
741 price_in IN iby_trxn_summaries_all.Amount%TYPE,
742 currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
743 time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
744 status_in IN iby_trxn_summaries_all.Status%TYPE,
745 operation_code_in IN iby_trxn_core.OperationCode%TYPE,
746 set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
747 batch_id_in IN OUT NOCOPY iby_trxn_summaries_all.batchID%TYPE,
748 batch_seq_num_in IN OUT NOCOPY iby_trxn_extended.BatchSeqNum%TYPE,
749 terminal_id_in IN OUT NOCOPY iby_trxn_extended.TerminalID%TYPE,
750 subseq_auth_ind_in IN iby_trxn_extended.SubAuthInd%TYPE,
751 vendor_code_in IN OUT NOCOPY iby_trxn_summaries_all.BEPCode%TYPE,
752 vendor_message_in IN OUT NOCOPY iby_trxn_summaries_all.BEPMessage%TYPE,
753 error_location_in IN OUT NOCOPY iby_trxn_summaries_all.ErrorLocation%TYPE,
754 transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
755 billeracct_in IN iby_tangible.acctno%type,
756 refinfo_in IN iby_tangible.refinfo%type,
757 memo_in IN iby_tangible.memo%type,
758 order_medium_in IN iby_tangible.order_medium%TYPE,
759 eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
760 org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
761 payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
762 instrnum_in IN iby_trxn_summaries_all.instrnumber%type)
763 IS
764 num_trxns NUMBER := 0;
765 counter NUMBER := 0;
766 err_msg VARCHAR2(100);
767 trxn_mid NUMBER;
768 transaction_id NUMBER;
769 l_tmid iby_trxn_summaries_all.mtangibleid%type;
770 l_order_id iby_trxn_summaries_all.tangibleid%type;
771 l_mpayeeid iby_payee.mpayeeid%type;
772 l_mbatchid iby_batches_all.mbatchid%type;
773 BEGIN
774 -- NULL optional parameters if they are empty string
775 IF (batch_id_in = '')
776 THEN
777 batch_id_in := null;
778 END IF;
779 IF (batch_seq_num_in = '')
780 THEN
781 batch_seq_num_in := null;
782 END IF;
783 IF (terminal_id_in = '')
784 THEN
785 terminal_id_in := null;
786 END IF;
787 IF (error_location_in = '')
788 THEN
789 error_location_in := null;
790 END IF;
791 IF (vendor_code_in = '')
792 THEN
793 vendor_code_in := null;
794 END IF;
795 -- Check for idempotency: see if transaction already
796 -- exists
797 SELECT count(*)
798 INTO num_trxns
799 FROM iby_trxn_summaries_all summary, iby_trxn_extended extended
800 WHERE summary.TangibleID = order_id_in
801 AND summary.PayeeID = merchant_id_in
802 AND extended.SplitID = split_id_in
803 AND UPPER(summary.ReqType) = UPPER(req_type_in)
804 AND summary.TrxnMID = extended.TrxnMID;
805 IF (num_trxns = 0)
806 THEN
807 -- No previous transaction, so insert new row
808 -- generate trxn_id and transaction_id
812 IF ((transaction_id_in_out = '') OR
809 SELECT iby_trxnsumm_mid_s.NEXTVAL
810 INTO trxn_mid
811 FROM dual;
813 (transaction_id_in_out IS NULL))
814 THEN
815
816 SELECT count(*)
817 INTO counter
818 FROM iby_trxn_summaries_all
819 WHERE TangibleID = order_id_in
820 AND PayeeID = merchant_id_in;
821
822 IF (counter = 0)
823 THEN
824
825 SELECT iby_trxnsumm_trxnid_s.NEXTVAL
826 INTO transaction_id
827 FROM dual;
828
829 ELSE
830 SELECT DISTINCT TransactionId
831 INTO transaction_id
832 FROM iby_trxn_summaries_all
833 WHERE TangibleID = order_id_in
834 AND PayeeID = merchant_id_in;
835
836 END IF;
837
838 transaction_id_in_out := transaction_id;
839 -- I suppose we need a new transactionid only for ORAPMTCREDIT
840 --Create an entry in iby_tangible table
841 iby_bill_pkg.createBill(order_id_in,price_in,currency_in,
842 billeracct_in,refinfo_in, memo_in,
843 order_medium_in, eft_auth_method_in,
844 l_tmid);
845 l_order_id := order_id_in;
846 ELSE
847 --tangible info should already exist, get them based on
848 --transactionid
849 SELECT DISTINCT mtangibleid, tangibleid
850 INTO l_tmid, l_order_id
851 FROM iby_trxn_summaries_all
852 WHERE transactionid = transaction_id_in_out;
853
854 transaction_id := transaction_id_in_out;
855 END IF;
856
857 iby_transactioncc_pkg.getMBatchID(batch_id_in, merchant_id_in,
858 l_mbatchid);
859 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
860
861
862
863 INSERT INTO iby_trxn_summaries_all
864 (TrxnMID, TangibleID,MPayeeID, PayeeID,BEPID,
865 TransactionID,TrxntypeID, ECAPPID, org_id, ReqType, ReqDate,
866 MtangibleId,
867 Amount,CurrencyNameCode, UpdateDate,Status,MBatchID, BatchID,
868 BEPCode, BEPMessage,Errorlocation,PaymentMethodName,
869
870 payerinstrid, instrnumber,
871 last_update_date, last_updated_by,
872 creation_date, created_by,
873 last_update_login, object_version_number,needsupdt)
874 VALUES (trxn_mid, l_order_id, l_mpayeeid, merchant_id_in, vendor_id_in,
875 transaction_id, trxn_type_in, ecapp_id_in, org_id_in,
876 req_type_in, time_in, l_tmid,
877 price_in, currency_in, time_in, status_in,
878 l_mbatchid, batch_id_in,
879 vendor_code_in, vendor_message_in, error_location_in,
880 payment_name_in,
881 payerinstrid_in, instrnum_in,
882 sysdate, fnd_global.user_id,
883 sysdate, fnd_global.user_id,
884 fnd_global.login_id, 1,'Y');
885
886 INSERT INTO iby_trxn_core
887 (TrxnMID, OperationCode,
888 last_update_date, last_updated_by,
889 creation_date, created_by,
890 last_update_login, object_version_number)
891
892 VALUES (trxn_mid, operation_code_in,
893 sysdate, fnd_global.user_id,
894 sysdate, fnd_global.user_id,
895 fnd_global.login_id, 1);
896
897
898 INSERT INTO iby_trxn_extended
899 (TrxnMID, SplitID, SETTrxnID,
900 BatchSeqNum,
901 TerminalID, SubAuthInd,
902 last_update_date, last_updated_by,
903 creation_date, created_by,
904 last_update_login, object_version_number)
905 VALUES
906 (trxn_mid, split_id_in, set_trxn_id_in,
907 batch_seq_num_in,
908 terminal_id_in, subseq_auth_ind_in,
909 sysdate, fnd_global.user_id,
910 sysdate, fnd_global.user_id,
911 fnd_global.login_id, 1);
912
913
914 ELSIF (num_trxns = 1)
915 THEN
916 -- One previous transaction, so update previous row
917 SELECT summary.TrxnMID, summary.TransactionID,MtangibleId
918 INTO trxn_mid, transaction_id_in_out,l_tmid
919 FROM iby_trxn_summaries_all summary, iby_trxn_extended extended
920 WHERE summary.TangibleID = order_id_in
921 AND summary.PayeeID = merchant_id_in
922 AND extended.SplitID = split_id_in
923 AND UPPER(summary.ReqType) = UPPER(req_type_in)
924 AND summary.TrxnMID = extended.TrxnMID;
925
926 iby_transactioncc_pkg.getMBatchID(batch_id_in, merchant_id_in,
927 l_mbatchid);
928
929 IF (UPPER(req_type_in) = 'ORAPMTCREDIT') THEN
930 -- Update iby_tangible table
931 iby_bill_pkg.modBill(l_tmid,order_id_in,price_in,currency_in,
932 billeracct_in,refinfo_in,memo_in,
933 order_medium_in, eft_auth_method_in);
934 END IF;
935
936 UPDATE iby_trxn_summaries_all
937 SET Amount = price_in,
938 CurrencyNameCode = currency_in,
939 UpdateDate = time_in,
940 Status = status_in,
941 ErrorLocation = error_location_in,
942 BEPCode = vendor_code_in,
943 BEPMessage = vendor_message_in,
944 BatchID = batch_id_in,
945 MBatchID = l_mbatchid,
946 payerinstrid = payerinstrid_in,
947 instrnumber = instrnum_in,
951 object_version_number = 1
948 last_update_date = sysdate,
949 last_updated_by = fnd_global.user_id,
950 last_update_login = fnd_global.login_id,
952 WHERE TrxnMID = trxn_mid;
953
954 UPDATE iby_trxn_core
955 SET OperationCode = operation_code_in,
956 last_update_date = sysdate,
957 last_updated_by = fnd_global.user_id,
958 last_update_login = fnd_global.login_id,
959 object_version_number = 1
960 WHERE TrxnMID = trxn_mid;
961
962 UPDATE iby_trxn_extended
963 SET SplitID = split_id_in,
964 SETTrxnID = set_trxn_id_in,
965 BatchSeqNum = batch_seq_num_in,
966 TerminalID = terminal_id_in,
967 SubAuthInd = subseq_auth_ind_in,
968 last_update_date = sysdate,
969 last_updated_by = fnd_global.user_id,
970 last_update_login = fnd_global.login_id,
971 object_version_number = 1
972 WHERE TrxnMID = trxn_mid;
973
974 ELSE
975 -- More than one previous transaction, which is an
976 -- error
977 raise_application_error(-20000, 'IBY_20431#', FALSE);
978 --raise_application_error(-20431, 'Multiple matching transactions of this transaction type: '||trxn_type_in);
979 END IF;
980 COMMIT;
981 EXCEPTION
982 WHEN OTHERS THEN
983 err_msg := SUBSTR(SQLERRM, 1, 100);
984 raise_application_error(-20000, 'IBY_20430#', FALSE);
985 --raise_application_error(-20430,'Error while inserting/updating transaction: '||err_msg);
986 END insert_other_txn;
987
988
989 /* Inserts or updates a row in the PS_TRXN_TABLE if the */
990 /* operation timed out from calling the vendor. */
991 PROCEDURE insert_timeout_txn
992 (ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
993 req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
994 order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
995 merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
996 vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
997 trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
998 split_id_in IN iby_trxn_extended.SplitID%TYPE,
999 payment_name_in IN iby_trxn_core.InstrName%TYPE,
1000 time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
1001 status_in IN iby_trxn_summaries_all.Status%TYPE,
1002 transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
1003 currency_in IN iby_tangible.currencynamecode%type,
1004 amount_in IN iby_transactions_v.amount%TYPE,
1005 billeracct_in IN iby_tangible.acctno%type,
1006 refinfo_in IN iby_tangible.refinfo%type,
1007 memo_in IN iby_tangible.memo%type,
1008 order_medium_in IN iby_tangible.order_medium%TYPE,
1009 eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
1010 org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
1011 payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
1012 instrnum_in IN iby_trxn_summaries_all.instrnumber%type)
1013 IS
1014 num_trxns NUMBER := 0;
1015 err_msg VARCHAR2(100);
1016 trxn_mid NUMBER;
1017 transaction_id NUMBER;
1018 l_tmid iby_trxn_summaries_all.mtangibleid%type;
1019 l_order_id iby_trxn_summaries_all.tangibleid%type;
1020 l_mpayeeid iby_payee.mpayeeid%type;
1021 BEGIN
1022 -- Count number of previous transactions
1023 SELECT count(*)
1024 INTO num_trxns
1025 FROM iby_trxn_summaries_all
1026 WHERE TangibleID = order_id_in
1027 AND UPPER(ReqType) = UPPER(req_type_in)
1028 AND PayeeID = merchant_id_in;
1029 IF (num_trxns = 0)
1030 THEN
1031 -- Insert transaction row
1032 -- generate trxn_id and transaction_id
1033 SELECT iby_trxnsumm_mid_s.NEXTVAL
1034 INTO trxn_mid
1035 FROM dual;
1036 SELECT iby_trxnsumm_trxnid_s.NEXTVAL
1037 INTO transaction_id
1038 FROM dual;
1039 transaction_id_in_out := transaction_id;
1040
1041
1042
1043 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
1044
1045 IF (UPPER(req_type_in) = 'ORAPMTREQ' or
1046 UPPER(req_type_in) = 'ORAPMTCREDIT') THEN
1047 -- Create an entry in iby_tangible table
1048 iby_bill_pkg.createBill(order_id_in,amount_in,currency_in,
1049 billeracct_in,refinfo_in, memo_in,
1050 order_medium_in, eft_auth_method_in,
1051 l_tmid);
1052 l_order_id := order_id_in;
1053 ELSE
1054 SELECT DISTINCT mtangibleid, tangibleid
1055 INTO l_tmid, l_order_id
1056 FROM iby_trxn_summaries_all
1057 WHERE transactionid = transaction_id_in_out;
1058
1059 transaction_id := transaction_id_in_out;
1060 END IF;
1061
1062 INSERT INTO iby_trxn_summaries_all
1063 (TrxnMID, TransactionID, ECAPPID, org_id,
1064 TangibleID, MPayeeID, PayeeID, BEPID, PaymentMethodName,
1065 TrxntypeID, ReqType, ReqDate, MtangibleId,
1066 UpdateDate,Status,
1067
1068 payerinstrid, instrnumber,
1069 last_update_date, last_updated_by,
1070 creation_date, created_by,
1071 last_update_login, object_version_number,needsupdt)
1072
1076 trxn_type_in, req_type_in, time_in, l_tmid,
1073 VALUES (trxn_mid, transaction_id, ecapp_id_in, org_id_in,
1074 l_order_id, l_mpayeeid, merchant_id_in, vendor_id_in,
1075 payment_name_in,
1077 time_in, status_in,
1078 payerinstrid_in, instrnum_in,
1079 sysdate, fnd_global.user_id,
1080 sysdate, fnd_global.user_id,
1081 fnd_global.login_id, 1,'Y');
1082
1083 INSERT INTO iby_trxn_extended
1084 (TrxnMID, SplitID,
1085 last_update_date, last_updated_by,
1086 creation_date, created_by,
1087 last_update_login, object_version_number)
1088 VALUES (trxn_mid, '1',
1089 sysdate, fnd_global.user_id,
1090 sysdate, fnd_global.user_id,
1091 fnd_global.login_id, 1);
1092
1093 INSERT INTO iby_trxn_core
1094 (TrxnMID, InstrName,
1095 last_update_date, last_updated_by,
1096 creation_date, created_by,
1097 last_update_login, object_version_number)
1098 VALUES (trxn_mid, payment_name_in,
1099 sysdate, fnd_global.user_id,
1100 sysdate, fnd_global.user_id,
1101 fnd_global.login_id, 1);
1102
1103 ELSIF (num_trxns = 1)
1104 THEN
1105 -- One previous transaction, so update previous row
1106 SELECT TrxnMID, MtangibleId
1107 INTO trxn_mid, l_tmid
1108 FROM iby_trxn_summaries_all
1109 WHERE TangibleID = order_id_in
1110 AND UPPER(ReqType) = UPPER(req_type_in)
1111 AND PayeeID = merchant_id_in;
1112
1113 IF (UPPER(req_type_in) = 'ORAPMTREQ' or
1114 UPPER(req_type_in) = 'ORAPMTCREDIT') THEN
1115 -- Update iby_tangible table
1116 iby_bill_pkg.modBill(l_tmid,order_id_in,amount_in,currency_in,
1117 billeracct_in,refinfo_in,memo_in,
1118 order_medium_in, eft_auth_method_in);
1119 END IF;
1120
1121
1122 UPDATE iby_trxn_summaries_all
1123 SET
1124 UpdateDate = time_in,
1125 --ReqDate = time_in,
1126 Status = status_in,
1127
1128 payerinstrid = payerinstrid_in,
1129 instrnumber = instrnum_in,
1130
1131 last_update_date = sysdate,
1132 last_updated_by = fnd_global.user_id,
1133 last_update_login = fnd_global.login_id,
1134 object_version_number = 1
1135 WHERE TrxnMID = trxn_mid;
1136
1137 UPDATE iby_trxn_extended
1138 SET SplitID = '1',
1139 last_update_date = sysdate,
1140 last_updated_by = fnd_global.user_id,
1141 last_update_login = fnd_global.login_id,
1142 object_version_number = 1
1143 WHERE TrxnMID = trxn_mid;
1144 ELSE
1145 raise_application_error(-20000, 'IBY_20401#', FALSE);
1146 --raise_application_error(-20401,'Duplicate transactions for this order_id');
1147 END IF;
1148
1149 commit;
1150 EXCEPTION
1151 WHEN OTHERS THEN
1152 err_msg := SUBSTR(SQLERRM, 1, 100);
1153 raise_application_error(-20000, 'IBY_20440#', FALSE);
1154 --raise_application_error(-20440,'Error while inserting/updating timeout transaction: '||err_msg);
1155
1156 END insert_timeout_txn;
1157 /* Inserts or updates a batch summary row into the PS_BATCH */
1158 /* table. This should happen for open or close batch operations. */
1159
1160 PROCEDURE insert_batch_status
1161 (batch_id_in IN iby_batches_all.BatchID%TYPE,
1162 merchant_id_in IN iby_batches_all.PayeeID%TYPE,
1163 bep_id_in IN iby_batches_all.BEPID%TYPE,
1164 /* vendor_suffix_in IN iby_batches_all.vendor_suffix%TYPE, */
1165 /* close_status_in IN iby_batches_all.BatchCloseStatus%TYPE, */
1166 currency_in IN iby_batches_all.CurrencyNameCode%TYPE,
1167 sale_price_in IN iby_batches_all.BatchSales%TYPE,
1168 credit_price_in IN iby_batches_all.BatchCredit%TYPE,
1169 trxn_count_in IN iby_batches_all.NumTrxns%TYPE,
1170 sale_trxn_count_in IN iby_batches_all.NumTrxns%TYPE,
1171 credit_trxn_count_in IN iby_batches_all.NumTrxns%TYPE,
1172 open_date_in IN iby_batches_all.BatchOpenDate%TYPE,
1173 close_date_in IN iby_batches_all.BatchCloseDate%TYPE,
1174 status_in IN iby_batches_all.BatchStatus%TYPE,
1175 vendor_code_in IN iby_batches_all.BEPCode%TYPE,
1176 vendor_message_in IN iby_batches_all.BEPMessage%TYPE,
1177 error_location_in IN iby_batches_all.ErrorLocation%TYPE,
1178 org_id_in IN iby_batches_all.org_id%TYPE,
1179 req_type_in IN iby_batches_all.reqtype%type)
1180 IS
1181 num_trxns NUMBER;
1182 err_msg VARCHAR2(100);
1183
1184 l_mbatchid iby_batches_all.mbatchid%type;
1185 l_mpayeeid iby_payee.mpayeeid%type;
1186 BEGIN
1187
1188 num_trxns := 0;
1189 -- Get number of existing batches that match
1190 SELECT count(*)
1191 INTO num_trxns
1192 FROM iby_batches_all
1193 WHERE BatchId = batch_id_in
1194 AND PayeeID = merchant_id_in;
1195 IF (num_trxns = 0)
1196 THEN
1197 -- Insert new batch status row
1198
1199 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
1200
1201 SELECT iby_batches_s.NEXTVAL
1202 INTO l_mbatchid
1203 FROM dual;
1204
1205 INSERT INTO iby_batches_all
1209 BEPCode, BEPMessage, ErrorLocation, reqtype, reqdate,
1206 (MBatchID, BatchID, org_id, MPayeeID, PayeeID, /* BatchCloseStatus,*/
1207 CURRENCYNameCode, BatchSales, BatchCredit, BatchTotal,
1208 NumTrxns, BatchOpenDate, BatchCloseDate, BatchStatus,
1210 last_update_date, last_updated_by,
1211 creation_date, created_by,
1212 last_update_login, object_version_number)
1213 VALUES
1214 (l_mbatchid, batch_id_in, org_id_in, l_mpayeeid,
1215 merchant_id_in, /*close_status_in, */
1216 currency_in, sale_price_in, credit_price_in, sale_price_in - credit_price_in,
1217 sale_trxn_count_in + credit_trxn_count_in,
1218 open_date_in, close_date_in, status_in,
1219 vendor_code_in, vendor_message_in, error_location_in,
1220 req_type_in, sysdate,
1221 sysdate, fnd_global.user_id,
1222 sysdate, fnd_global.user_id,
1223 fnd_global.login_id, 1);
1224
1225 ELSIF (num_trxns = 1)
1226 THEN
1227 -- Update batch status row
1228 UPDATE iby_batches_all
1229 SET /* BatchCloseStatus = close_status_in, */
1230
1231 reqtype = req_type_in,
1232 reqdate = sysdate,
1233 CurrencyNameCode = currency_in,
1234 BatchSales = sale_price_in,
1235 BatchCredit = credit_price_in,
1236 BatchTotal = sale_price_in - credit_price_in,
1237 NumTrxns = sale_trxn_count_in + credit_trxn_count_in,
1238 BatchOpenDate = open_date_in,
1239 BatchCloseDate = close_date_in,
1240 BatchStatus = status_in,
1241 BEPCode = vendor_code_in,
1242 BEPMessage = vendor_message_in,
1243 ErrorLocation = error_location_in,
1244 last_update_date = sysdate,
1245 last_updated_by = fnd_global.user_id,
1246 last_update_login = fnd_global.login_id,
1247 object_version_number = 1
1248 WHERE BatchID = batch_id_in
1249 AND PayeeID = merchant_id_in;
1250
1251 ELSE
1252 -- More than 1 trxn matched, so give error
1253 raise_application_error(-20000, 'IBY_20451#', FALSE);
1254 --raise_application_error(-20451,'Duplicate batch summaries match batch_id '||batch_id_in||' and merchant_id '||merchant_id_in);
1255 END IF;
1256 COMMIT;
1257 EXCEPTION
1258 WHEN OTHERS THEN
1259 err_msg := SUBSTR(SQLERRM, 1, 100);
1260 raise_application_error(-20000, 'IBY_20450#', FALSE);
1261 --raise_application_error(-20450,'Error while inserting/updating batch summary: '||err_msg);
1262 END insert_batch_status;
1263
1264 /* Inserts or updates the batch detail record upon the */
1265 /* closebatch or querybatch operations. */
1266 PROCEDURE insert_batch_txn
1267 (ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
1268 order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
1269 merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
1270 vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
1271 trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
1272 split_id_in IN iby_trxn_extended.SplitID%TYPE,
1273 payment_name_in IN iby_trxn_core.InstrName%TYPE,
1274 price_in IN iby_trxn_summaries_all.Amount%TYPE,
1275 currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
1276 time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
1277 status_in IN iby_trxn_summaries_all.Status%TYPE,
1278 set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
1279 prev_set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
1280 batch_id_in IN iby_trxn_summaries_all.batchID%TYPE,
1281 batch_seq_num_in IN iby_trxn_extended.BatchSeqNum%TYPE,
1282 batch_trxn_status_in IN iby_trxn_extended.BatchTrxnStatus%TYPE,
1283 card_BIN_in IN iby_trxn_extended.Cardbin%TYPE,
1284 terminal_id_in IN iby_trxn_extended.TerminalID%TYPE,
1285 vendor_code_in IN iby_trxn_summaries_all.BEPCode%TYPE,
1286 vendor_message_in IN iby_trxn_summaries_all.BEPMessage%TYPE,
1287 error_location_in IN iby_trxn_summaries_all.ErrorLocation%TYPE,
1288 split_id_in_out IN OUT NOCOPY iby_trxn_extended.SplitID%TYPE,
1289 org_id_in IN iby_trxn_summaries_all.org_id%TYPE)
1290
1291 IS
1292 num_trxns NUMBER := 0;
1293 err_msg VARCHAR2(100);
1294 trxn_mid NUMBER;
1295
1296 l_mpayeeid iby_payee.mpayeeid%type;
1297 l_mbatchid iby_batches_all.mbatchid%type;
1298 BEGIN
1299 -- Check for idempotency: see if transaction already
1300 -- exists
1301 SELECT count(*)
1302 INTO num_trxns
1303 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1304 WHERE extended.SETTrxnID = set_trxn_id_in
1305 AND summary.BEPID = vendor_id_in
1306 AND extended.TrxnMID = summary.TrxnMID;
1307 IF (num_trxns = 0)
1308 THEN
1309 -- generate trxn_id and transaction_id
1310 SELECT iby_trxnsumm_mid_s.NEXTVAL
1311 INTO trxn_mid
1312 FROM dual;
1313 -- SELECT iby_trxnsumm_trxnid_s.NEXTVAL
1314 -- INTO transaction_id
1315 -- FROM dual;
1316 -- transaction_id_in_out := transaction_id;
1317 -- No previous transaction, so get parent split_id
1318 find_parent_splitid(order_id_in, merchant_id_in,
1322 -- isn't working! Need TO FIX!
1319 vendor_id_in, trxn_type_in,
1320 prev_set_trxn_id_in, split_id_in_out);
1321 -- Temporarily hardcoding since find_parent_splitid
1323 IF (split_id_in_out IS NULL)
1324 THEN
1325 raise_application_error(-20000, 'IBY_20461#', FALSE);
1326 --raise_application_error(-20461,'Parent split_id of trxn in batch could not be found');
1327 -- split_id_in_out := '1';
1328 END IF;
1329 -- Insert new row
1330 -- generate TransactionID
1331
1332 iby_transactioncc_pkg.getMBatchID(batch_id_in, merchant_id_in,
1333 l_mbatchid);
1334 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
1335 INSERT INTO iby_trxn_summaries_all
1336 (TrxnMID, TangibleID,MPayeeID, PayeeID,BEPID,
1337 TrxntypeID, ECAPPID, org_id,
1338 Amount,CurrencyNameCode, UpdateDate,Status,MBatchID, BatchID,
1339 BEPCode, BEPMessage,Errorlocation,
1340 last_update_date, last_updated_by,
1341 creation_date, created_by,
1342 last_update_login, object_version_number,needsupdt)
1343 VALUES (trxn_mid, order_id_in, l_mpayeeid, merchant_id_in, vendor_id_in,
1344 trxn_type_in, ecapp_id_in,org_id_in,
1345 price_in, currency_in, time_in, status_in,
1346 l_mbatchid, batch_id_in,
1347 vendor_code_in, vendor_message_in, error_location_in,
1348 sysdate, fnd_global.user_id,
1349 sysdate, fnd_global.user_id,
1350 fnd_global.login_id, 1,'Y');
1351
1352 INSERT INTO iby_trxn_extended
1353 (TrxnMID, SplitID, SETTrxnID,
1354 BatchSeqNum, BatchTrxnStatus,
1355 Cardbin, TerminalID,
1356 last_update_date, last_updated_by, creation_date,
1357 created_by, last_update_login, object_version_number)
1358 VALUES
1359 (trxn_mid, '1', set_trxn_id_in,
1360 batch_seq_num_in, batch_trxn_status_in,
1361 card_bin_in, terminal_id_in,
1362 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
1363 fnd_global.login_id, 1);
1364
1365 ELSIF (num_trxns = 1)
1366 THEN
1367 -- Update current row
1368 SELECT summary.TrxnMID
1369 INTO trxn_mid
1370 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1371 WHERE extended.SETTrxnID = set_trxn_id_in
1372 AND summary.BEPID = vendor_id_in
1373 AND extended.TrxnMID = summary.TrxnMID;
1374
1375 UPDATE iby_trxn_extended
1376 SET BatchSeqNum = batch_seq_num_in,
1377 BatchTrxnStatus = batch_trxn_status_in,
1378 last_update_date = sysdate,
1379 last_updated_by = fnd_global.user_id,
1380 last_update_login = fnd_global.login_id,
1381 object_version_number = 1
1382 WHERE TrxnMID = trxn_mid;
1383
1384 -- Get split_id for output
1385 SELECT extended.SplitID
1386 INTO split_id_in_out
1387 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1388 WHERE extended.SETTrxnID = set_trxn_id_in
1389 AND summary.BEPID = vendor_id_in
1390 AND extended.TrxnMID = summary.TrxnMID;
1391 -- Update card BIN only if it's not null
1392 IF ((card_BIN_in <> '') AND
1393 (card_BIN_in IS NOT NULL))
1394 THEN
1395 SELECT summary.TrxnMID
1396 INTO trxn_mid
1397 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1398 WHERE extended.SETTrxnID = set_trxn_id_in
1399 AND summary.BEPID = vendor_id_in
1400 AND extended.TrxnMID = summary.TrxnMID;
1401
1402 UPDATE iby_trxn_extended
1403 SET Cardbin = card_BIN_in,
1404 last_update_date = sysdate,
1405 last_updated_by = fnd_global.user_id,
1406 last_update_login = fnd_global.login_id,
1407 object_version_number = 1
1408 WHERE TrxnMID = trxn_mid;
1409 END IF;
1410 -- Update terminal id only if it's not null
1411 IF ((terminal_id_in <> '') AND
1412 (terminal_id_in IS NOT NULL))
1413 THEN
1414 SELECT summary.TrxnMID
1415 INTO trxn_mid
1416 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1417 WHERE extended.SETTrxnID = set_trxn_id_in
1418 AND summary.BEPID = vendor_id_in
1419 AND extended.TrxnMID = summary.TrxnMID;
1420 UPDATE iby_trxn_extended
1421 SET TerminalID = terminal_id_in,
1422 last_update_date = sysdate,
1423 last_updated_by = fnd_global.user_id,
1424 last_update_login = fnd_global.login_id,
1425 object_version_number = 1
1426 WHERE TrxnMID = trxn_mid;
1427 END IF;
1428 -- Update error location only if it's not null
1429 IF ((error_location_in <> '') AND
1430 (error_location_in IS NOT NULL))
1431 THEN
1432 SELECT summary.TrxnMID
1433 INTO trxn_mid
1434 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1435 WHERE extended.SETTrxnID = set_trxn_id_in
1436 AND summary.BEPID = vendor_id_in
1437 AND extended.TrxnMID = summary.TrxnMID;
1438 UPDATE iby_trxn_summaries_all
1439 SET ErrorLocation = error_location_in,
1440 last_update_date = sysdate,
1441 updatedate = sysdate,
1442 last_updated_by = fnd_global.user_id,
1443 last_update_login = fnd_global.login_id,
1447 -- Update vendor code only if it's not null
1444 object_version_number = 1
1445 WHERE TrxnMID = trxn_mid;
1446 END IF;
1448 IF ((vendor_code_in <> '') AND
1449 (vendor_code_in IS NOT NULL))
1450 THEN
1451 SELECT summary.TrxnMID
1452 INTO trxn_mid
1453 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1454 WHERE extended.SETTrxnID = set_trxn_id_in
1455 AND summary.BEPID = vendor_id_in
1456 AND extended.TrxnMID = summary.TrxnMID;
1457 UPDATE iby_trxn_summaries_all
1458 SET BEPCode = vendor_code_in,
1459 last_update_date = sysdate,
1460 updatedate = sysdate,
1461 last_updated_by = fnd_global.user_id,
1462 last_update_login = fnd_global.login_id,
1463 object_version_number = 1
1464 WHERE TrxnMID = trxn_mid;
1465 END IF;
1466 -- Update vendor message only if it's not null
1467 IF ((vendor_message_in <> '') AND
1468 (vendor_message_in IS NOT NULL))
1469 THEN
1470 SELECT summary.TrxnMID
1471 INTO trxn_mid
1472 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1473 WHERE extended.SETTrxnID = set_trxn_id_in
1474 AND summary.BEPID = vendor_id_in
1475 AND extended.TrxnMID = summary.TrxnMID;
1476 UPDATE iby_trxn_summaries_all
1477 SET BEPMessage = vendor_message_in,
1478 last_update_date = sysdate,
1479 updatedate = sysdate,
1480 last_updated_by = fnd_global.user_id,
1481 last_update_login = fnd_global.login_id,
1482 object_version_number = 1
1483 WHERE TrxnMID = trxn_mid;
1484 END IF;
1485
1486 ELSE
1487 -- Error since too many matching rows
1488 raise_application_error(-20000, 'IBY_20201#', FALSE);
1489 --raise_application_error(-20201,'Duplicate rows in transactions_set table to update for batch information');
1490 END IF;
1491 COMMIT;
1492
1493 EXCEPTION
1494 WHEN OTHERS THEN
1495 err_msg := SUBSTR(SQLERRM, 1, 100);
1496 raise_application_error(-20000, 'IBY_20460#', FALSE);
1497 --raise_application_error(-20460,'Error while inserting/updating batch item: '||err_msg);
1498
1499 END insert_batch_txn;
1500
1501
1502 /* Inserts transaction record for transaction query operation */
1503 PROCEDURE insert_query_txn
1504 (ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
1505 order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
1506 merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
1507 vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
1508 trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
1509 price_in IN iby_trxn_summaries_all.Amount%TYPE,
1510 currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
1511 time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
1512 status_in IN iby_trxn_summaries_all.Status%TYPE,
1513 set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
1514 prev_set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
1515 ret_ref_num_in IN iby_trxn_core.ReferenceCode%TYPE,
1516 card_BIN_in IN OUT NOCOPY iby_trxn_extended.Cardbin%TYPE,
1517 terminal_id_in IN OUT NOCOPY iby_trxn_extended.TerminalID%TYPE,
1518 vendor_code_in IN OUT NOCOPY iby_trxn_summaries_all.BEPCode%TYPE,
1519 vendor_message_in IN OUT NOCOPY iby_trxn_summaries_all.BEPMessage%TYPE,
1520 error_location_in IN OUT NOCOPY iby_trxn_summaries_all.ErrorLocation%TYPE,
1521 split_id_in_out IN OUT NOCOPY iby_trxn_extended.SplitID%TYPE,
1522 transaction_id_in IN iby_trxn_summaries_all.TransactionID%TYPE,
1523 payment_method_in IN iby_trxn_summaries_all.PaymentMethodName%TYPE,
1524 org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
1525 req_type_in IN iby_trxn_summaries_all.reqtype%type)
1526 IS
1527 num_trxns NUMBER := 0;
1528 mid iby_trxn_extended.SplitID%TYPE;
1529 err_msg VARCHAR2(100);
1530 trxn_mid NUMBER;
1531
1532 l_mpayeeid iby_payee.mpayeeid%type;
1533 BEGIN
1534 -- NULL optional parameters
1535 IF (card_BIN_in = '')
1536 THEN
1537 card_BIN_in := null;
1538 END IF;
1539 IF (terminal_id_in = '')
1540 THEN
1541 terminal_id_in := null;
1542 END IF;
1543 IF (error_location_in = '')
1544 THEN
1545 error_location_in := null;
1546 END IF;
1547 IF (vendor_code_in = '')
1548 THEN
1549 vendor_code_in := null;
1550 END IF;
1551 IF (vendor_message_in = '')
1552 THEN
1553 vendor_message_in := null;
1554 END IF;
1555 -- Check if row is already in table using unique
1556 -- key of set_trxn_id and vendor_id
1557 SELECT COUNT(*)
1558 INTO num_trxns
1559 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1560 WHERE extended.SETTrxnID = set_trxn_id_in
1561 AND summary.BEPID = vendor_id_in
1562 AND extended.TrxnMID = summary.TrxnMID;
1563 IF (num_trxns = 0)
1564 THEN
1565 -- No previous transaction, so get
1566 -- split_id from parent, if
1570 prev_set_trxn_id_in, split_id_in_out);
1567 -- it's there
1568 find_parent_splitid(order_id_in, merchant_id_in,
1569 vendor_id_in, trxn_type_in,
1571 -- generate trxn_id and transaction_id
1572 SELECT iby_trxnsumm_mid_s.NEXTVAL
1573 INTO trxn_mid
1574 FROM dual;
1575 --SELECT iby_trxnsumm_trxnid_s.NEXTVAL
1576 -- INTO transaction_id
1577 -- FROM dual;
1578 --transaction_id_in_out := transaction_id;
1579
1580
1581 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
1582 INSERT INTO iby_trxn_summaries_all
1583 (TrxnMID, TransactionID, paymentMethodName,
1584 TangibleID,MPayeeID, PayeeID,BEPID,
1585 TrxntypeID, ECAPPID, org_id, ReqDate, ReqType,
1586 Amount,CurrencyNameCode, UpdateDate,Status,
1587 BEPCode, BEPMessage,Errorlocation,
1588 last_update_date, last_updated_by,
1589 creation_date, created_by,
1590 last_update_login, object_version_number,needsupdt)
1591 VALUES (trxn_mid, transaction_id_in, payment_method_in,
1592 order_id_in, l_mpayeeid, merchant_id_in, vendor_id_in,
1593 trxn_type_in, ecapp_id_in, org_id_in, time_in,req_type_in,
1594 price_in, currency_in, time_in, status_in,
1595 vendor_code_in, vendor_message_in, error_location_in,
1596 sysdate, fnd_global.user_id,
1597 sysdate, fnd_global.user_id,
1598 fnd_global.login_id, 1,'Y');
1599
1600 INSERT INTO iby_trxn_core
1601 (TrxnMID, ReferenceCode,
1602 last_update_date, last_updated_by, creation_date, created_by,
1603 last_update_login, object_version_number)
1604 VALUES (trxn_mid, ret_ref_num_in,
1605 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
1606 fnd_global.login_id, 1);
1607
1608 INSERT INTO iby_trxn_extended
1609 (TrxnMID, SplitID, SETTrxnID,
1610 -- BatchSeqNum,
1611 Cardbin, TerminalID,
1612 last_update_date, last_updated_by, creation_date, created_by,
1613 last_update_login, object_version_number)
1614 VALUES
1615 (trxn_mid,split_id_in_out, set_trxn_id_in,
1616 -- batch_seq_num_in,
1617 card_bin_in, terminal_id_in,
1618 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
1619 fnd_global.login_id, 1);
1620 ELSIF (num_trxns = 1)
1621 THEN
1622 -- Count number of successful transactions
1623 SELECT count(*)
1624 INTO num_trxns
1625 FROM iby_trxn_summaries_all summary, iby_trxn_extended extended
1626 WHERE extended.SETTrxnID = set_trxn_id_in
1627 AND summary.BEPID = vendor_id_in
1628 AND summary.Status = 0
1629 AND summary.TrxnMID = extended.TrxnMID;
1630 -- If transaction was successful, do nothing,
1631 -- else update the row if it was not successful.
1632 IF (num_trxns = 0)
1633 THEN
1634 SELECT summary.TrxnMID
1635 INTO trxn_mid
1636 FROM iby_trxn_summaries_all summary, iby_trxn_extended extended
1637 WHERE extended.SETTrxnID = set_trxn_id_in
1638 AND summary.BEPID = vendor_id_in
1639 AND summary.TrxnMID = extended.TrxnMID;
1640 UPDATE iby_trxn_summaries_all
1641 SET Amount = price_in,
1642 CurrencyNameCode = currency_in,
1643 UpdateDate = time_in,
1644 Status = status_in,
1645 ErrorLocation = error_location_in,
1646 BEPCode = vendor_code_in,
1647 BEPMessage = vendor_message_in,
1648 last_update_date = sysdate,
1649 last_updated_by = fnd_global.user_id,
1650 last_update_login = fnd_global.login_id,
1651 object_version_number = 1
1652 WHERE TrxnMID = trxn_mid;
1653
1654 UPDATE iby_trxn_core
1655 SET ReferenceCode = ret_ref_num_in,
1656 last_update_date = sysdate,
1657 last_updated_by = fnd_global.user_id,
1658 last_update_login = fnd_global.login_id,
1659 object_version_number = 1
1660 WHERE TrxnMID = trxn_mid;
1661
1662 UPDATE iby_trxn_extended
1663 SET Cardbin = card_bin_in,
1664 TerminalID = terminal_id_in,
1665 last_update_date = sysdate,
1666 last_updated_by = fnd_global.user_id,
1667 last_update_login = fnd_global.login_id,
1668 object_version_number = 1
1669 WHERE TrxnMID = trxn_mid;
1670 END IF;
1671 END IF;
1672
1673 commit;
1674 EXCEPTION
1675 WHEN OTHERS THEN
1676 err_msg := SUBSTR(SQLERRM, 1, 100);
1677 raise_application_error(-20000, 'IBY_20470#', FALSE);
1678 --raise_application_error(-20470,'Error while inserting/updating queried order: '||err_msg);
1679 END insert_query_txn;
1680
1681
1682 /* Internal procedure to get the split_id of the parent */
1683 /* transaction. */
1684 PROCEDURE find_parent_splitid
1685 (order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
1686 merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
1687 vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
1688 trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
1689 prev_set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
1690 split_id_in_out IN OUT NOCOPY iby_trxn_extended.SplitID%TYPE)
1691 IS
1692
1693 num_trxns NUMBER := 0;
1694 mid iby_trxn_extended.SplitID%TYPE;
1695 err_msg VARCHAR2(100);
1696
1697 BEGIN
1698 split_id_in_out := null;
1699 -- Find number of parents
1700 SELECT count(distinct extended.SplitID)
1701 INTO num_trxns
1702 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1703 WHERE extended.SETTrxnID = prev_set_trxn_id_in
1704 AND summary.BEPID = vendor_id_in
1705 AND extended.TrxnMID = summary.TrxnMID;
1706 IF (num_trxns = 0)
1707 THEN
1708 -- No parent found. Hard-code value to 1 (default value)
1709 split_id_in_out := '1';
1710 ELSIF (num_trxns = 1)
1711 THEN
1712 -- Even tho value is probably 1 (default), we'll make
1713 -- sure by getting it anyways
1714 SELECT distinct extended.SplitID
1715 INTO mid
1716 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1717 WHERE extended.SETTrxnID = prev_set_trxn_id_in
1718 AND summary.BEPID = vendor_id_in
1719 AND extended.TrxnMID = summary.TrxnMID;
1720 split_id_in_out := mid;
1721 ELSE
1722 -- This shouldn't happen, so raise an error
1723 raise_application_error(-20000, 'IBY_20481#', FALSE);
1724 --raise_application_error(-20481,'More than one split_id for the parent transaction with SET_ID: '||prev_set_trxn_id_in);
1725 END IF;
1726 EXCEPTION
1727
1728 WHEN OTHERS THEN
1729 err_msg := SUBSTR(SQLERRM, 1, 100);
1730 raise_application_error(-20000, 'IBY_20480#', FALSE);
1731 --raise_application_error(-20480,'Error while finding the split_id of the parent transaction: '||err_msg);
1732 END find_parent_splitid;
1733
1734
1735 END iby_transactionSET_pkg;
1736