[Home] [Help]
PACKAGE BODY: APPS.IBY_TRANSACTIONSET_PKG
Source
1 PACKAGE BODY iby_transactionSET_pkg AS
2 /*$Header: ibytxstb.pls 120.2.12010000.3 2009/04/04 00:44:55 svinjamu 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
313 INTO transaction_id
314 FROM iby_trxn_summaries_all
315 WHERE TangibleID = order_id_in
316 AND PayeeID = merchant_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,
445 last_updated_by = fnd_global.user_id,
446 last_update_login = fnd_global.login_id,
447 object_version_number = 1
448 WHERE TrxnMID = trxn_mid;
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
561 (subseq_auth_ind_in <> 1))
562 THEN
563 raise_application_error(-20000, 'IBY_20421#', FALSE);
564 --raise_application_error(-20421,'Invalid subsequent authorization indicator: '|| subseq_auth_ind_in);
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,
693 last_updated_by = fnd_global.user_id,
694 last_update_login = fnd_global.login_id,
695 object_version_number = 1
696 WHERE TrxnMID = trxn_mid;
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
809 SELECT iby_trxnsumm_mid_s.NEXTVAL
810 INTO trxn_mid
811 FROM dual;
812 IF ((transaction_id_in_out = '') OR
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,
948 last_update_date = sysdate,
949 last_updated_by = fnd_global.user_id,
950 last_update_login = fnd_global.login_id,
951 object_version_number = 1
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
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,
1076 trxn_type_in, req_type_in, time_in, l_tmid,
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 --Bug:8363526
1205 --Inserting new column settledate VALUE:sysdate
1206 INSERT INTO iby_batches_all
1207 (MBatchID, BatchID, org_id, MPayeeID, PayeeID, /* BatchCloseStatus,*/
1208 CURRENCYNameCode, BatchSales, BatchCredit, BatchTotal,
1209 NumTrxns, BatchOpenDate, BatchCloseDate, BatchStatus,
1210 BEPCode, BEPMessage, ErrorLocation, reqtype, reqdate,
1211 last_update_date, last_updated_by,
1212 creation_date, created_by,
1213 last_update_login, object_version_number,settledate)
1214 VALUES
1215 (l_mbatchid, batch_id_in, org_id_in, l_mpayeeid,
1216 merchant_id_in, /*close_status_in, */
1217 currency_in, sale_price_in, credit_price_in, sale_price_in - credit_price_in,
1218 sale_trxn_count_in + credit_trxn_count_in,
1219 open_date_in, close_date_in, status_in,
1220 vendor_code_in, vendor_message_in, error_location_in,
1221 req_type_in, sysdate,
1222 sysdate, fnd_global.user_id,
1223 sysdate, fnd_global.user_id,
1224 fnd_global.login_id, 1,sysdate);
1225
1226 ELSIF (num_trxns = 1)
1227 THEN
1228 -- Update batch status row
1229 UPDATE iby_batches_all
1230 SET /* BatchCloseStatus = close_status_in, */
1231
1232 reqtype = req_type_in,
1233 reqdate = sysdate,
1234 CurrencyNameCode = currency_in,
1235 BatchSales = sale_price_in,
1236 BatchCredit = credit_price_in,
1237 BatchTotal = sale_price_in - credit_price_in,
1238 NumTrxns = sale_trxn_count_in + credit_trxn_count_in,
1239 BatchOpenDate = open_date_in,
1240 BatchCloseDate = close_date_in,
1241 BatchStatus = status_in,
1242 BEPCode = vendor_code_in,
1243 BEPMessage = vendor_message_in,
1244 ErrorLocation = error_location_in,
1245 last_update_date = sysdate,
1246 last_updated_by = fnd_global.user_id,
1247 last_update_login = fnd_global.login_id,
1248 object_version_number = 1
1249 WHERE BatchID = batch_id_in
1250 AND PayeeID = merchant_id_in;
1251
1252 ELSE
1253 -- More than 1 trxn matched, so give error
1254 raise_application_error(-20000, 'IBY_20451#', FALSE);
1255 --raise_application_error(-20451,'Duplicate batch summaries match batch_id '||batch_id_in||' and merchant_id '||merchant_id_in);
1256 END IF;
1257 COMMIT;
1258 EXCEPTION
1259 WHEN OTHERS THEN
1260 err_msg := SUBSTR(SQLERRM, 1, 100);
1261 raise_application_error(-20000, 'IBY_20450#', FALSE);
1262 --raise_application_error(-20450,'Error while inserting/updating batch summary: '||err_msg);
1263 END insert_batch_status;
1264
1265 /* Inserts or updates the batch detail record upon the */
1266 /* closebatch or querybatch operations. */
1267 PROCEDURE insert_batch_txn
1268 (ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
1269 order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
1270 merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
1271 vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
1272 trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
1273 split_id_in IN iby_trxn_extended.SplitID%TYPE,
1274 payment_name_in IN iby_trxn_core.InstrName%TYPE,
1275 price_in IN iby_trxn_summaries_all.Amount%TYPE,
1276 currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
1277 time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
1278 status_in IN iby_trxn_summaries_all.Status%TYPE,
1279 set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
1280 prev_set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
1281 batch_id_in IN iby_trxn_summaries_all.batchID%TYPE,
1282 batch_seq_num_in IN iby_trxn_extended.BatchSeqNum%TYPE,
1283 batch_trxn_status_in IN iby_trxn_extended.BatchTrxnStatus%TYPE,
1284 card_BIN_in IN iby_trxn_extended.Cardbin%TYPE,
1285 terminal_id_in IN iby_trxn_extended.TerminalID%TYPE,
1286 vendor_code_in IN iby_trxn_summaries_all.BEPCode%TYPE,
1287 vendor_message_in IN iby_trxn_summaries_all.BEPMessage%TYPE,
1288 error_location_in IN iby_trxn_summaries_all.ErrorLocation%TYPE,
1289 split_id_in_out IN OUT NOCOPY iby_trxn_extended.SplitID%TYPE,
1290 org_id_in IN iby_trxn_summaries_all.org_id%TYPE)
1291
1292 IS
1293 num_trxns NUMBER := 0;
1294 err_msg VARCHAR2(100);
1295 trxn_mid NUMBER;
1296
1297 l_mpayeeid iby_payee.mpayeeid%type;
1298 l_mbatchid iby_batches_all.mbatchid%type;
1299 BEGIN
1300 -- Check for idempotency: see if transaction already
1301 -- exists
1302 SELECT count(*)
1303 INTO num_trxns
1304 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1305 WHERE extended.SETTrxnID = set_trxn_id_in
1306 AND summary.BEPID = vendor_id_in
1307 AND extended.TrxnMID = summary.TrxnMID;
1308 IF (num_trxns = 0)
1309 THEN
1310 -- generate trxn_id and transaction_id
1311 SELECT iby_trxnsumm_mid_s.NEXTVAL
1312 INTO trxn_mid
1313 FROM dual;
1314 -- SELECT iby_trxnsumm_trxnid_s.NEXTVAL
1315 -- INTO transaction_id
1316 -- FROM dual;
1317 -- transaction_id_in_out := transaction_id;
1318 -- No previous transaction, so get parent split_id
1319 find_parent_splitid(order_id_in, merchant_id_in,
1320 vendor_id_in, trxn_type_in,
1321 prev_set_trxn_id_in, split_id_in_out);
1322 -- Temporarily hardcoding since find_parent_splitid
1323 -- isn't working! Need TO FIX!
1324 IF (split_id_in_out IS NULL)
1325 THEN
1326 raise_application_error(-20000, 'IBY_20461#', FALSE);
1327 --raise_application_error(-20461,'Parent split_id of trxn in batch could not be found');
1328 -- split_id_in_out := '1';
1329 END IF;
1330 -- Insert new row
1331 -- generate TransactionID
1332
1333 iby_transactioncc_pkg.getMBatchID(batch_id_in, merchant_id_in,
1334 l_mbatchid);
1335 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
1336 INSERT INTO iby_trxn_summaries_all
1337 (TrxnMID, TangibleID,MPayeeID, PayeeID,BEPID,
1338 TrxntypeID, ECAPPID, org_id,
1339 Amount,CurrencyNameCode, UpdateDate,Status,MBatchID, BatchID,
1340 BEPCode, BEPMessage,Errorlocation,
1341 last_update_date, last_updated_by,
1342 creation_date, created_by,
1343 last_update_login, object_version_number,needsupdt)
1344 VALUES (trxn_mid, order_id_in, l_mpayeeid, merchant_id_in, vendor_id_in,
1345 trxn_type_in, ecapp_id_in,org_id_in,
1346 price_in, currency_in, time_in, status_in,
1347 l_mbatchid, batch_id_in,
1348 vendor_code_in, vendor_message_in, error_location_in,
1349 sysdate, fnd_global.user_id,
1350 sysdate, fnd_global.user_id,
1351 fnd_global.login_id, 1,'Y');
1352
1353 INSERT INTO iby_trxn_extended
1354 (TrxnMID, SplitID, SETTrxnID,
1355 BatchSeqNum, BatchTrxnStatus,
1356 Cardbin, TerminalID,
1357 last_update_date, last_updated_by, creation_date,
1358 created_by, last_update_login, object_version_number)
1359 VALUES
1360 (trxn_mid, '1', set_trxn_id_in,
1361 batch_seq_num_in, batch_trxn_status_in,
1362 card_bin_in, terminal_id_in,
1363 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
1364 fnd_global.login_id, 1);
1365
1366 ELSIF (num_trxns = 1)
1367 THEN
1368 -- Update current row
1369 SELECT summary.TrxnMID
1370 INTO trxn_mid
1371 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1372 WHERE extended.SETTrxnID = set_trxn_id_in
1373 AND summary.BEPID = vendor_id_in
1374 AND extended.TrxnMID = summary.TrxnMID;
1375
1376 UPDATE iby_trxn_extended
1377 SET BatchSeqNum = batch_seq_num_in,
1378 BatchTrxnStatus = batch_trxn_status_in,
1379 last_update_date = sysdate,
1380 last_updated_by = fnd_global.user_id,
1381 last_update_login = fnd_global.login_id,
1382 object_version_number = 1
1383 WHERE TrxnMID = trxn_mid;
1384
1385 -- Get split_id for output
1386 SELECT extended.SplitID
1387 INTO split_id_in_out
1388 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1389 WHERE extended.SETTrxnID = set_trxn_id_in
1390 AND summary.BEPID = vendor_id_in
1391 AND extended.TrxnMID = summary.TrxnMID;
1392 -- Update card BIN only if it's not null
1393 IF ((card_BIN_in <> '') AND
1394 (card_BIN_in IS NOT NULL))
1395 THEN
1396 SELECT summary.TrxnMID
1397 INTO trxn_mid
1398 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1399 WHERE extended.SETTrxnID = set_trxn_id_in
1400 AND summary.BEPID = vendor_id_in
1401 AND extended.TrxnMID = summary.TrxnMID;
1402
1403 UPDATE iby_trxn_extended
1404 SET Cardbin = card_BIN_in,
1405 last_update_date = sysdate,
1406 last_updated_by = fnd_global.user_id,
1407 last_update_login = fnd_global.login_id,
1408 object_version_number = 1
1409 WHERE TrxnMID = trxn_mid;
1410 END IF;
1411 -- Update terminal id only if it's not null
1412 IF ((terminal_id_in <> '') AND
1413 (terminal_id_in IS NOT NULL))
1414 THEN
1415 SELECT summary.TrxnMID
1416 INTO trxn_mid
1417 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1418 WHERE extended.SETTrxnID = set_trxn_id_in
1419 AND summary.BEPID = vendor_id_in
1420 AND extended.TrxnMID = summary.TrxnMID;
1421 UPDATE iby_trxn_extended
1422 SET TerminalID = terminal_id_in,
1423 last_update_date = sysdate,
1424 last_updated_by = fnd_global.user_id,
1425 last_update_login = fnd_global.login_id,
1426 object_version_number = 1
1427 WHERE TrxnMID = trxn_mid;
1428 END IF;
1429 -- Update error location only if it's not null
1430 IF ((error_location_in <> '') AND
1431 (error_location_in IS NOT NULL))
1432 THEN
1433 SELECT summary.TrxnMID
1434 INTO trxn_mid
1435 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1436 WHERE extended.SETTrxnID = set_trxn_id_in
1437 AND summary.BEPID = vendor_id_in
1438 AND extended.TrxnMID = summary.TrxnMID;
1439 UPDATE iby_trxn_summaries_all
1440 SET ErrorLocation = error_location_in,
1441 last_update_date = sysdate,
1442 updatedate = sysdate,
1443 last_updated_by = fnd_global.user_id,
1444 last_update_login = fnd_global.login_id,
1445 object_version_number = 1
1446 WHERE TrxnMID = trxn_mid;
1447 END IF;
1448 -- Update vendor code only if it's not null
1449 IF ((vendor_code_in <> '') AND
1450 (vendor_code_in IS NOT NULL))
1451 THEN
1452 SELECT summary.TrxnMID
1453 INTO trxn_mid
1454 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1455 WHERE extended.SETTrxnID = set_trxn_id_in
1456 AND summary.BEPID = vendor_id_in
1457 AND extended.TrxnMID = summary.TrxnMID;
1458 UPDATE iby_trxn_summaries_all
1459 SET BEPCode = vendor_code_in,
1460 last_update_date = sysdate,
1461 updatedate = sysdate,
1462 last_updated_by = fnd_global.user_id,
1463 last_update_login = fnd_global.login_id,
1464 object_version_number = 1
1465 WHERE TrxnMID = trxn_mid;
1466 END IF;
1467 -- Update vendor message only if it's not null
1468 IF ((vendor_message_in <> '') AND
1469 (vendor_message_in IS NOT NULL))
1470 THEN
1471 SELECT summary.TrxnMID
1472 INTO trxn_mid
1473 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1474 WHERE extended.SETTrxnID = set_trxn_id_in
1475 AND summary.BEPID = vendor_id_in
1476 AND extended.TrxnMID = summary.TrxnMID;
1477 UPDATE iby_trxn_summaries_all
1478 SET BEPMessage = vendor_message_in,
1479 last_update_date = sysdate,
1480 updatedate = sysdate,
1481 last_updated_by = fnd_global.user_id,
1482 last_update_login = fnd_global.login_id,
1483 object_version_number = 1
1484 WHERE TrxnMID = trxn_mid;
1485 END IF;
1486
1487 ELSE
1488 -- Error since too many matching rows
1489 raise_application_error(-20000, 'IBY_20201#', FALSE);
1490 --raise_application_error(-20201,'Duplicate rows in transactions_set table to update for batch information');
1491 END IF;
1492 COMMIT;
1493
1494 EXCEPTION
1495 WHEN OTHERS THEN
1496 err_msg := SUBSTR(SQLERRM, 1, 100);
1497 raise_application_error(-20000, 'IBY_20460#', FALSE);
1498 --raise_application_error(-20460,'Error while inserting/updating batch item: '||err_msg);
1499
1500 END insert_batch_txn;
1501
1502
1503 /* Inserts transaction record for transaction query operation */
1504 PROCEDURE insert_query_txn
1505 (ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
1506 order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
1507 merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
1508 vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
1509 trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
1510 price_in IN iby_trxn_summaries_all.Amount%TYPE,
1511 currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
1512 time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
1513 status_in IN iby_trxn_summaries_all.Status%TYPE,
1514 set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
1515 prev_set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
1516 ret_ref_num_in IN iby_trxn_core.ReferenceCode%TYPE,
1517 card_BIN_in IN OUT NOCOPY iby_trxn_extended.Cardbin%TYPE,
1518 terminal_id_in IN OUT NOCOPY iby_trxn_extended.TerminalID%TYPE,
1519 vendor_code_in IN OUT NOCOPY iby_trxn_summaries_all.BEPCode%TYPE,
1520 vendor_message_in IN OUT NOCOPY iby_trxn_summaries_all.BEPMessage%TYPE,
1521 error_location_in IN OUT NOCOPY iby_trxn_summaries_all.ErrorLocation%TYPE,
1522 split_id_in_out IN OUT NOCOPY iby_trxn_extended.SplitID%TYPE,
1523 transaction_id_in IN iby_trxn_summaries_all.TransactionID%TYPE,
1524 payment_method_in IN iby_trxn_summaries_all.PaymentMethodName%TYPE,
1525 org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
1526 req_type_in IN iby_trxn_summaries_all.reqtype%type)
1527 IS
1528 num_trxns NUMBER := 0;
1529 mid iby_trxn_extended.SplitID%TYPE;
1530 err_msg VARCHAR2(100);
1531 trxn_mid NUMBER;
1532
1533 l_mpayeeid iby_payee.mpayeeid%type;
1534 BEGIN
1535 -- NULL optional parameters
1536 IF (card_BIN_in = '')
1537 THEN
1538 card_BIN_in := null;
1539 END IF;
1540 IF (terminal_id_in = '')
1541 THEN
1542 terminal_id_in := null;
1543 END IF;
1544 IF (error_location_in = '')
1545 THEN
1546 error_location_in := null;
1547 END IF;
1548 IF (vendor_code_in = '')
1549 THEN
1550 vendor_code_in := null;
1551 END IF;
1552 IF (vendor_message_in = '')
1553 THEN
1554 vendor_message_in := null;
1555 END IF;
1556 -- Check if row is already in table using unique
1557 -- key of set_trxn_id and vendor_id
1558 SELECT COUNT(*)
1559 INTO num_trxns
1560 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1561 WHERE extended.SETTrxnID = set_trxn_id_in
1562 AND summary.BEPID = vendor_id_in
1563 AND extended.TrxnMID = summary.TrxnMID;
1564 IF (num_trxns = 0)
1565 THEN
1566 -- No previous transaction, so get
1567 -- split_id from parent, if
1568 -- it's there
1569 find_parent_splitid(order_id_in, merchant_id_in,
1570 vendor_id_in, trxn_type_in,
1571 prev_set_trxn_id_in, split_id_in_out);
1572 -- generate trxn_id and transaction_id
1573 SELECT iby_trxnsumm_mid_s.NEXTVAL
1574 INTO trxn_mid
1575 FROM dual;
1576 --SELECT iby_trxnsumm_trxnid_s.NEXTVAL
1577 -- INTO transaction_id
1578 -- FROM dual;
1579 --transaction_id_in_out := transaction_id;
1580
1581
1582 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
1583 INSERT INTO iby_trxn_summaries_all
1584 (TrxnMID, TransactionID, paymentMethodName,
1585 TangibleID,MPayeeID, PayeeID,BEPID,
1586 TrxntypeID, ECAPPID, org_id, ReqDate, ReqType,
1587 Amount,CurrencyNameCode, UpdateDate,Status,
1588 BEPCode, BEPMessage,Errorlocation,
1589 last_update_date, last_updated_by,
1590 creation_date, created_by,
1591 last_update_login, object_version_number,needsupdt)
1592 VALUES (trxn_mid, transaction_id_in, payment_method_in,
1593 order_id_in, l_mpayeeid, merchant_id_in, vendor_id_in,
1594 trxn_type_in, ecapp_id_in, org_id_in, time_in,req_type_in,
1595 price_in, currency_in, time_in, status_in,
1596 vendor_code_in, vendor_message_in, error_location_in,
1597 sysdate, fnd_global.user_id,
1598 sysdate, fnd_global.user_id,
1599 fnd_global.login_id, 1,'Y');
1600
1601 INSERT INTO iby_trxn_core
1602 (TrxnMID, ReferenceCode,
1603 last_update_date, last_updated_by, creation_date, created_by,
1604 last_update_login, object_version_number)
1605 VALUES (trxn_mid, ret_ref_num_in,
1606 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
1607 fnd_global.login_id, 1);
1608
1609 INSERT INTO iby_trxn_extended
1610 (TrxnMID, SplitID, SETTrxnID,
1611 -- BatchSeqNum,
1612 Cardbin, TerminalID,
1613 last_update_date, last_updated_by, creation_date, created_by,
1614 last_update_login, object_version_number)
1615 VALUES
1616 (trxn_mid,split_id_in_out, set_trxn_id_in,
1617 -- batch_seq_num_in,
1618 card_bin_in, terminal_id_in,
1619 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
1620 fnd_global.login_id, 1);
1621 ELSIF (num_trxns = 1)
1622 THEN
1623 -- Count number of successful transactions
1624 SELECT count(*)
1625 INTO num_trxns
1626 FROM iby_trxn_summaries_all summary, iby_trxn_extended extended
1627 WHERE extended.SETTrxnID = set_trxn_id_in
1628 AND summary.BEPID = vendor_id_in
1629 AND summary.Status = 0
1630 AND summary.TrxnMID = extended.TrxnMID;
1631 -- If transaction was successful, do nothing,
1632 -- else update the row if it was not successful.
1633 IF (num_trxns = 0)
1634 THEN
1635 SELECT summary.TrxnMID
1636 INTO trxn_mid
1637 FROM iby_trxn_summaries_all summary, iby_trxn_extended extended
1638 WHERE extended.SETTrxnID = set_trxn_id_in
1639 AND summary.BEPID = vendor_id_in
1640 AND summary.TrxnMID = extended.TrxnMID;
1641 UPDATE iby_trxn_summaries_all
1642 SET Amount = price_in,
1643 CurrencyNameCode = currency_in,
1644 UpdateDate = time_in,
1645 Status = status_in,
1646 ErrorLocation = error_location_in,
1647 BEPCode = vendor_code_in,
1648 BEPMessage = vendor_message_in,
1649 last_update_date = sysdate,
1650 last_updated_by = fnd_global.user_id,
1651 last_update_login = fnd_global.login_id,
1652 object_version_number = 1
1653 WHERE TrxnMID = trxn_mid;
1654
1655 UPDATE iby_trxn_core
1656 SET ReferenceCode = ret_ref_num_in,
1657 last_update_date = sysdate,
1658 last_updated_by = fnd_global.user_id,
1659 last_update_login = fnd_global.login_id,
1660 object_version_number = 1
1661 WHERE TrxnMID = trxn_mid;
1662
1663 UPDATE iby_trxn_extended
1664 SET Cardbin = card_bin_in,
1665 TerminalID = terminal_id_in,
1666 last_update_date = sysdate,
1667 last_updated_by = fnd_global.user_id,
1668 last_update_login = fnd_global.login_id,
1669 object_version_number = 1
1670 WHERE TrxnMID = trxn_mid;
1671 END IF;
1672 END IF;
1673
1674 commit;
1675 EXCEPTION
1676 WHEN OTHERS THEN
1677 err_msg := SUBSTR(SQLERRM, 1, 100);
1678 raise_application_error(-20000, 'IBY_20470#', FALSE);
1679 --raise_application_error(-20470,'Error while inserting/updating queried order: '||err_msg);
1680 END insert_query_txn;
1681
1682
1683 /* Internal procedure to get the split_id of the parent */
1684 /* transaction. */
1685 PROCEDURE find_parent_splitid
1686 (order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
1687 merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
1688 vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
1689 trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
1690 prev_set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
1691 split_id_in_out IN OUT NOCOPY iby_trxn_extended.SplitID%TYPE)
1692 IS
1693
1694 num_trxns NUMBER := 0;
1695 mid iby_trxn_extended.SplitID%TYPE;
1696 err_msg VARCHAR2(100);
1697
1698 BEGIN
1699 split_id_in_out := null;
1700 -- Find number of parents
1701 SELECT count(distinct extended.SplitID)
1702 INTO num_trxns
1703 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1704 WHERE extended.SETTrxnID = prev_set_trxn_id_in
1705 AND summary.BEPID = vendor_id_in
1706 AND extended.TrxnMID = summary.TrxnMID;
1707 IF (num_trxns = 0)
1708 THEN
1709 -- No parent found. Hard-code value to 1 (default value)
1710 split_id_in_out := '1';
1711 ELSIF (num_trxns = 1)
1712 THEN
1713 -- Even tho value is probably 1 (default), we'll make
1714 -- sure by getting it anyways
1715 SELECT distinct extended.SplitID
1716 INTO mid
1717 FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
1718 WHERE extended.SETTrxnID = prev_set_trxn_id_in
1719 AND summary.BEPID = vendor_id_in
1720 AND extended.TrxnMID = summary.TrxnMID;
1721 split_id_in_out := mid;
1722 ELSE
1723 -- This shouldn't happen, so raise an error
1724 raise_application_error(-20000, 'IBY_20481#', FALSE);
1725 --raise_application_error(-20481,'More than one split_id for the parent transaction with SET_ID: '||prev_set_trxn_id_in);
1726 END IF;
1727 EXCEPTION
1728
1729 WHEN OTHERS THEN
1730 err_msg := SUBSTR(SQLERRM, 1, 100);
1731 raise_application_error(-20000, 'IBY_20480#', FALSE);
1732 --raise_application_error(-20480,'Error while finding the split_id of the parent transaction: '||err_msg);
1733 END find_parent_splitid;
1734
1735
1736 END iby_transactionSET_pkg;
1737