[Home] [Help]
17: -- only a single currency
18: --
19: PROCEDURE validate_open_batch
20: (
21: p_bep_id IN iby_trxn_summaries_all.bepid%TYPE,
22: p_mbatch_id IN iby_batches_all.mbatchid%TYPE,
23: p_sec_key_on IN VARCHAR2,
24: x_trxn_count OUT NOCOPY iby_batches_all.numtrxns%TYPE,
25: x_batch_currency OUT NOCOPY iby_batches_all.currencynamecode%TYPE
25: x_batch_currency OUT NOCOPY iby_batches_all.currencynamecode%TYPE
26: )
27: IS
28: l_sec_trxn_count NUMBER;
29: l_batch_currency iby_trxn_summaries_all.currencynamecode%TYPE;
30:
31: l_call_string VARCHAR2(1000);
32: l_call_params JTF_VARCHAR2_TABLE_200 := JTF_VARCHAR2_TABLE_200();
33: l_return_status VARCHAR2(10);
36:
37: CURSOR c_currencycodes(ci_mbatch_id IN iby_batches_all.mbatchid%TYPE)
38: IS
39: SELECT ts.currencynamecode
40: FROM iby_batches_all ba, iby_trxn_summaries_all ts
41: WHERE (ba.mbatchid = ci_mbatch_id)
42: AND (ba.payeeid = ts.payeeid)
43: AND (ba.batchid = ts.batchid)
44: GROUP BY ts.currencynamecode;
42: AND (ba.payeeid = ts.payeeid)
43: AND (ba.batchid = ts.batchid)
44: GROUP BY ts.currencynamecode;
45:
46: CURSOR c_valsets(ci_bep_id iby_trxn_summaries_all.bepid%TYPE)
47: IS
48: SELECT validation_code_package, validation_code_entry_point
49: FROM iby_validation_sets_b vs, iby_fndcpt_sys_cc_pf_b pf,
50: iby_val_assignments va
68: -- close to continue
69: --
70: SELECT COUNT(transactionid)
71: INTO l_sec_trxn_count
72: FROM iby_batches_all ba, iby_trxn_summaries_all ts
73: WHERE (ba.mbatchid = p_mbatch_id)
74: AND (ba.payeeid = ts.payeeid)
75: AND (ba.batchid = ts.batchid)
76: AND (NOT sub_key_id IS NULL);
85:
86: END IF;
87: SELECT COUNT(transactionid)
88: INTO x_trxn_count
89: FROM iby_batches_all ba, iby_trxn_summaries_all ts
90: WHERE (ba.mbatchid = p_mbatch_id)
91: AND (ba.payeeid = ts.payeeid)
92: AND (ba.batchid = ts.batchid);
93: --
142:
143: PROCEDURE prepare_instr_data
144: (p_commit IN VARCHAR2,
145: p_sys_key IN iby_security_pkg.DES3_KEY_TYPE,
146: p_instrnum IN iby_trxn_summaries_all.instrnumber%TYPE,
147: p_instrtype IN iby_trxn_summaries_all.instrtype%TYPE,
148: x_instrnum OUT NOCOPY iby_trxn_summaries_all.instrnumber%TYPE,
149: x_instr_subtype OUT NOCOPY iby_trxn_summaries_all.instrsubtype%TYPE,
150: x_instr_hash OUT NOCOPY iby_trxn_summaries_all.instrnum_hash%TYPE,
143: PROCEDURE prepare_instr_data
144: (p_commit IN VARCHAR2,
145: p_sys_key IN iby_security_pkg.DES3_KEY_TYPE,
146: p_instrnum IN iby_trxn_summaries_all.instrnumber%TYPE,
147: p_instrtype IN iby_trxn_summaries_all.instrtype%TYPE,
148: x_instrnum OUT NOCOPY iby_trxn_summaries_all.instrnumber%TYPE,
149: x_instr_subtype OUT NOCOPY iby_trxn_summaries_all.instrsubtype%TYPE,
150: x_instr_hash OUT NOCOPY iby_trxn_summaries_all.instrnum_hash%TYPE,
151: x_range_id OUT NOCOPY iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE,
144: (p_commit IN VARCHAR2,
145: p_sys_key IN iby_security_pkg.DES3_KEY_TYPE,
146: p_instrnum IN iby_trxn_summaries_all.instrnumber%TYPE,
147: p_instrtype IN iby_trxn_summaries_all.instrtype%TYPE,
148: x_instrnum OUT NOCOPY iby_trxn_summaries_all.instrnumber%TYPE,
149: x_instr_subtype OUT NOCOPY iby_trxn_summaries_all.instrsubtype%TYPE,
150: x_instr_hash OUT NOCOPY iby_trxn_summaries_all.instrnum_hash%TYPE,
151: x_range_id OUT NOCOPY iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE,
152: x_instr_len OUT NOCOPY iby_trxn_summaries_all.instrnum_length%TYPE,
145: p_sys_key IN iby_security_pkg.DES3_KEY_TYPE,
146: p_instrnum IN iby_trxn_summaries_all.instrnumber%TYPE,
147: p_instrtype IN iby_trxn_summaries_all.instrtype%TYPE,
148: x_instrnum OUT NOCOPY iby_trxn_summaries_all.instrnumber%TYPE,
149: x_instr_subtype OUT NOCOPY iby_trxn_summaries_all.instrsubtype%TYPE,
150: x_instr_hash OUT NOCOPY iby_trxn_summaries_all.instrnum_hash%TYPE,
151: x_range_id OUT NOCOPY iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE,
152: x_instr_len OUT NOCOPY iby_trxn_summaries_all.instrnum_length%TYPE,
153: x_segment_id OUT NOCOPY iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE
146: p_instrnum IN iby_trxn_summaries_all.instrnumber%TYPE,
147: p_instrtype IN iby_trxn_summaries_all.instrtype%TYPE,
148: x_instrnum OUT NOCOPY iby_trxn_summaries_all.instrnumber%TYPE,
149: x_instr_subtype OUT NOCOPY iby_trxn_summaries_all.instrsubtype%TYPE,
150: x_instr_hash OUT NOCOPY iby_trxn_summaries_all.instrnum_hash%TYPE,
151: x_range_id OUT NOCOPY iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE,
152: x_instr_len OUT NOCOPY iby_trxn_summaries_all.instrnum_length%TYPE,
153: x_segment_id OUT NOCOPY iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE
154: )
147: p_instrtype IN iby_trxn_summaries_all.instrtype%TYPE,
148: x_instrnum OUT NOCOPY iby_trxn_summaries_all.instrnumber%TYPE,
149: x_instr_subtype OUT NOCOPY iby_trxn_summaries_all.instrsubtype%TYPE,
150: x_instr_hash OUT NOCOPY iby_trxn_summaries_all.instrnum_hash%TYPE,
151: x_range_id OUT NOCOPY iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE,
152: x_instr_len OUT NOCOPY iby_trxn_summaries_all.instrnum_length%TYPE,
153: x_segment_id OUT NOCOPY iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE
154: )
155: IS
148: x_instrnum OUT NOCOPY iby_trxn_summaries_all.instrnumber%TYPE,
149: x_instr_subtype OUT NOCOPY iby_trxn_summaries_all.instrsubtype%TYPE,
150: x_instr_hash OUT NOCOPY iby_trxn_summaries_all.instrnum_hash%TYPE,
151: x_range_id OUT NOCOPY iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE,
152: x_instr_len OUT NOCOPY iby_trxn_summaries_all.instrnum_length%TYPE,
153: x_segment_id OUT NOCOPY iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE
154: )
155: IS
156: BEGIN
149: x_instr_subtype OUT NOCOPY iby_trxn_summaries_all.instrsubtype%TYPE,
150: x_instr_hash OUT NOCOPY iby_trxn_summaries_all.instrnum_hash%TYPE,
151: x_range_id OUT NOCOPY iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE,
152: x_instr_len OUT NOCOPY iby_trxn_summaries_all.instrnum_length%TYPE,
153: x_segment_id OUT NOCOPY iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE
154: )
155: IS
156: BEGIN
157: IBY_SECURITY_PKG.prepare_instr_data
171: -- USE: inserts transactional extensibility data
172: --
173: PROCEDURE insert_extensibility
174: (
175: p_trxnmid IN iby_trxn_summaries_all.trxnmid%TYPE,
176: p_commit IN VARCHAR2,
177: p_extend_names IN JTF_VARCHAR2_TABLE_100,
178: p_extend_vals IN JTF_VARCHAR2_TABLE_200
179: )
201: COMMIT;
202: END IF;
203: END insert_extensibility;
204:
205: /* Inserts a new row into the IBY_TRXN_SUMMARIES_ALL table. This method */
206: /* would be called every time a MIPP authorize operation is performed. */
207:
208: PROCEDURE insert_auth_txn
209: (
206: /* would be called every time a MIPP authorize operation is performed. */
207:
208: PROCEDURE insert_auth_txn
209: (
210: ecapp_id_in IN iby_trxn_summaries_all.ecappid%TYPE,
211: req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
212: order_id_in IN iby_transactions_v.order_id%TYPE,
213: merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
214: vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
207:
208: PROCEDURE insert_auth_txn
209: (
210: ecapp_id_in IN iby_trxn_summaries_all.ecappid%TYPE,
211: req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
212: order_id_in IN iby_transactions_v.order_id%TYPE,
213: merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
214: vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
215: vendor_key_in IN iby_transactions_v.bepkey%TYPE,
228: vendor_code_in IN iby_transactions_v.vendor_code%TYPE,
229: vendor_message_in IN iby_transactions_v.vendor_message%TYPE,
230: error_location_in IN iby_transactions_v.error_location%TYPE,
231: trace_number_in IN iby_transactions_v.TraceNumber%TYPE,
232: org_id_in IN iby_trxn_summaries_all.org_id%type,
233: billeracct_in IN iby_tangible.acctno%type,
234: refinfo_in IN iby_tangible.refinfo%type,
235: memo_in IN iby_tangible.memo%type,
236: order_medium_in IN iby_tangible.order_medium%TYPE,
234: refinfo_in IN iby_tangible.refinfo%type,
235: memo_in IN iby_tangible.memo%type,
236: order_medium_in IN iby_tangible.order_medium%TYPE,
237: eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
238: payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
239: instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
240: payerid_in IN iby_trxn_summaries_all.payerid%type,
241: instrtype_in IN iby_trxn_summaries_all.instrType%type,
242: cvv2result_in IN iby_trxn_core.CVV2Result%type,
235: memo_in IN iby_tangible.memo%type,
236: order_medium_in IN iby_tangible.order_medium%TYPE,
237: eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
238: payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
239: instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
240: payerid_in IN iby_trxn_summaries_all.payerid%type,
241: instrtype_in IN iby_trxn_summaries_all.instrType%type,
242: cvv2result_in IN iby_trxn_core.CVV2Result%type,
243: master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
236: order_medium_in IN iby_tangible.order_medium%TYPE,
237: eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
238: payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
239: instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
240: payerid_in IN iby_trxn_summaries_all.payerid%type,
241: instrtype_in IN iby_trxn_summaries_all.instrType%type,
242: cvv2result_in IN iby_trxn_core.CVV2Result%type,
243: master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
244: subkey_seed_in IN RAW,
237: eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
238: payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
239: instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
240: payerid_in IN iby_trxn_summaries_all.payerid%type,
241: instrtype_in IN iby_trxn_summaries_all.instrType%type,
242: cvv2result_in IN iby_trxn_core.CVV2Result%type,
243: master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
244: subkey_seed_in IN RAW,
245: trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
241: instrtype_in IN iby_trxn_summaries_all.instrType%type,
242: cvv2result_in IN iby_trxn_core.CVV2Result%type,
243: master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
244: subkey_seed_in IN RAW,
245: trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
246: dateofvoiceauth_in IN iby_trxn_core.date_of_voice_authorization%TYPE,
247: instr_expirydate_in IN iby_trxn_core.instr_expirydate%TYPE,
248: instr_sec_val_in IN VARCHAR2,
249: card_subtype_in IN iby_trxn_core.card_subtype_code%TYPE,
267: extend_vals_in IN JTF_VARCHAR2_TABLE_200,
268: debit_network_code_in IN iby_trxn_core.debit_network_code%TYPE,
269: surcharge_amount_in IN iby_trxn_core.surcharge_amount%TYPE,
270: proc_tracenumber_in IN iby_trxn_core.proc_tracenumber%TYPE,
271: transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
272: transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
273: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
274: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
275: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
268: debit_network_code_in IN iby_trxn_core.debit_network_code%TYPE,
269: surcharge_amount_in IN iby_trxn_core.surcharge_amount%TYPE,
270: proc_tracenumber_in IN iby_trxn_core.proc_tracenumber%TYPE,
271: transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
272: transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
273: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
274: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
275: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
276: process_profile_code_in IN iby_trxn_summaries_all.process_profile_code%TYPE,
269: surcharge_amount_in IN iby_trxn_core.surcharge_amount%TYPE,
270: proc_tracenumber_in IN iby_trxn_core.proc_tracenumber%TYPE,
271: transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
272: transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
273: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
274: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
275: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
276: process_profile_code_in IN iby_trxn_summaries_all.process_profile_code%TYPE,
277: sub_key_id_in IN iby_trxn_summaries_all.sub_key_id%TYPE,
270: proc_tracenumber_in IN iby_trxn_core.proc_tracenumber%TYPE,
271: transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
272: transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
273: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
274: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
275: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
276: process_profile_code_in IN iby_trxn_summaries_all.process_profile_code%TYPE,
277: sub_key_id_in IN iby_trxn_summaries_all.sub_key_id%TYPE,
278: voiceAuthFlag_in IN iby_trxn_core.voiceauthflag%TYPE,
271: transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
272: transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
273: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
274: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
275: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
276: process_profile_code_in IN iby_trxn_summaries_all.process_profile_code%TYPE,
277: sub_key_id_in IN iby_trxn_summaries_all.sub_key_id%TYPE,
278: voiceAuthFlag_in IN iby_trxn_core.voiceauthflag%TYPE,
279: reauth_trxnid_in IN iby_trxn_summaries_all.TransactionID%TYPE
272: transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
273: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
274: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
275: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
276: process_profile_code_in IN iby_trxn_summaries_all.process_profile_code%TYPE,
277: sub_key_id_in IN iby_trxn_summaries_all.sub_key_id%TYPE,
278: voiceAuthFlag_in IN iby_trxn_core.voiceauthflag%TYPE,
279: reauth_trxnid_in IN iby_trxn_summaries_all.TransactionID%TYPE
280: )
273: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
274: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
275: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
276: process_profile_code_in IN iby_trxn_summaries_all.process_profile_code%TYPE,
277: sub_key_id_in IN iby_trxn_summaries_all.sub_key_id%TYPE,
278: voiceAuthFlag_in IN iby_trxn_core.voiceauthflag%TYPE,
279: reauth_trxnid_in IN iby_trxn_summaries_all.TransactionID%TYPE
280: )
281: IS
275: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
276: process_profile_code_in IN iby_trxn_summaries_all.process_profile_code%TYPE,
277: sub_key_id_in IN iby_trxn_summaries_all.sub_key_id%TYPE,
278: voiceAuthFlag_in IN iby_trxn_core.voiceauthflag%TYPE,
279: reauth_trxnid_in IN iby_trxn_summaries_all.TransactionID%TYPE
280: )
281: IS
282:
283: l_num_trxns NUMBER := 0;
282:
283: l_num_trxns NUMBER := 0;
284: l_trxn_mid NUMBER;
285: l_transaction_id NUMBER;
286: l_tmid iby_trxn_summaries_all.mtangibleid%type;
287: l_mpayeeid iby_payee.mpayeeid%type;
288:
289: l_return_status VARCHAR2(1);
290: l_msg_count NUMBER;
291: l_msg_data VARCHAR2(200);
292: l_checksum_valid BOOLEAN := FALSE; -- whether the card number is valid.
293:
294: l_cc_type VARCHAR2(80);
295: lx_cc_hash iby_trxn_summaries_all.instrnum_hash%TYPE;
296: lx_range_id iby_cc_issuer_ranges.cc_issuer_range_id%TYPE;
297: lx_instr_len iby_trxn_summaries_all.instrnum_length%TYPE;
298: lx_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
299: l_old_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
293:
294: l_cc_type VARCHAR2(80);
295: lx_cc_hash iby_trxn_summaries_all.instrnum_hash%TYPE;
296: lx_range_id iby_cc_issuer_ranges.cc_issuer_range_id%TYPE;
297: lx_instr_len iby_trxn_summaries_all.instrnum_length%TYPE;
298: lx_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
299: l_old_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
300:
301: l_instrnum iby_trxn_summaries_all.instrnumber%type;
294: l_cc_type VARCHAR2(80);
295: lx_cc_hash iby_trxn_summaries_all.instrnum_hash%TYPE;
296: lx_range_id iby_cc_issuer_ranges.cc_issuer_range_id%TYPE;
297: lx_instr_len iby_trxn_summaries_all.instrnum_length%TYPE;
298: lx_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
299: l_old_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
300:
301: l_instrnum iby_trxn_summaries_all.instrnumber%type;
302: l_expirydate iby_trxn_core.instr_expirydate%type;
295: lx_cc_hash iby_trxn_summaries_all.instrnum_hash%TYPE;
296: lx_range_id iby_cc_issuer_ranges.cc_issuer_range_id%TYPE;
297: lx_instr_len iby_trxn_summaries_all.instrnum_length%TYPE;
298: lx_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
299: l_old_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
300:
301: l_instrnum iby_trxn_summaries_all.instrnumber%type;
302: l_expirydate iby_trxn_core.instr_expirydate%type;
303:
297: lx_instr_len iby_trxn_summaries_all.instrnum_length%TYPE;
298: lx_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
299: l_old_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
300:
301: l_instrnum iby_trxn_summaries_all.instrnumber%type;
302: l_expirydate iby_trxn_core.instr_expirydate%type;
303:
304: l_pos_txn iby_trxn_core.pos_trxn_flag%TYPE;
305: l_payer_party_id iby_trxn_summaries_all.payer_party_id%type;
301: l_instrnum iby_trxn_summaries_all.instrnumber%type;
302: l_expirydate iby_trxn_core.instr_expirydate%type;
303:
304: l_pos_txn iby_trxn_core.pos_trxn_flag%TYPE;
305: l_payer_party_id iby_trxn_summaries_all.payer_party_id%type;
306:
307: l_voiceauth_flag iby_trxn_core.voiceauthflag%type;
308: l_sub_key_id iby_trxn_summaries_all.sub_key_id%TYPE;
309:
304: l_pos_txn iby_trxn_core.pos_trxn_flag%TYPE;
305: l_payer_party_id iby_trxn_summaries_all.payer_party_id%type;
306:
307: l_voiceauth_flag iby_trxn_core.voiceauthflag%type;
308: l_sub_key_id iby_trxn_summaries_all.sub_key_id%TYPE;
309:
310: -- variables for CHNAME and EXPDATE encryption
311: l_chname_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
312: l_expdate_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
416: iby_bill_pkg.createBill(order_id_in,amount_in,currency_in,
417: billeracct_in,refinfo_in, memo_in,
418: order_medium_in, eft_auth_method_in, l_tmid);
419: --test_debug('subkeyid passed as: '|| sub_key_id_in);
420: INSERT INTO iby_trxn_summaries_all
421: (TrxnMID, TransactionID,TrxntypeID, ReqType, ReqDate,
422: Amount,CurrencyNameCode, UpdateDate,Status, PaymentMethodName,
423: TangibleID,MPayeeID, PayeeID,BEPID,bepKey,mtangibleid,
424: BEPCode,BEPMessage,Errorlocation,ecappid,org_id,
507: --(l_num_trxns = 1)
508: -- One previous PENDING transaction, so update previous row
509: SELECT TrxnMID, TransactionID, Mtangibleid, instrnum_sec_segment_id, sub_key_id
510: INTO l_trxn_mid, transaction_id_out, l_tmid, l_old_segment_id, l_sub_key_id
511: FROM iby_trxn_summaries_all
512: WHERE (TangibleID = order_id_in)
513: AND (UPPER(ReqType) = UPPER(req_type_in))
514: AND (PayeeID = merchant_id_in)
515: AND (status IN (11,9));
524: billeracct_in,refinfo_in,memo_in,
525: order_medium_in, eft_auth_method_in);
526:
527:
528: UPDATE iby_trxn_summaries_all
529: SET BEPID = vendor_id_in,
530: bepKey = vendor_key_in,
531: Amount = amount_in,
532: -- amount, bepid is updated as the request can come in
622: COMMIT;
623: END insert_auth_txn;
624:
625: PROCEDURE insert_rev_result
626: ( trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
627: trxnid_in IN iby_trxn_summaries_all.transactionid%TYPE,
628: status_in IN iby_trxn_summaries_all.status%TYPE,
629: trxntype_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
630: revised_amt_in IN iby_trxn_summaries_all.amount%TYPE,
623: END insert_auth_txn;
624:
625: PROCEDURE insert_rev_result
626: ( trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
627: trxnid_in IN iby_trxn_summaries_all.transactionid%TYPE,
628: status_in IN iby_trxn_summaries_all.status%TYPE,
629: trxntype_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
630: revised_amt_in IN iby_trxn_summaries_all.amount%TYPE,
631: referencecode_in IN iby_transactions_v.referencecode%TYPE DEFAULT NULL,
624:
625: PROCEDURE insert_rev_result
626: ( trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
627: trxnid_in IN iby_trxn_summaries_all.transactionid%TYPE,
628: status_in IN iby_trxn_summaries_all.status%TYPE,
629: trxntype_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
630: revised_amt_in IN iby_trxn_summaries_all.amount%TYPE,
631: referencecode_in IN iby_transactions_v.referencecode%TYPE DEFAULT NULL,
632: AVScode_in IN iby_transactions_v.AVScode%TYPE DEFAULT NULL,
625: PROCEDURE insert_rev_result
626: ( trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
627: trxnid_in IN iby_trxn_summaries_all.transactionid%TYPE,
628: status_in IN iby_trxn_summaries_all.status%TYPE,
629: trxntype_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
630: revised_amt_in IN iby_trxn_summaries_all.amount%TYPE,
631: referencecode_in IN iby_transactions_v.referencecode%TYPE DEFAULT NULL,
632: AVScode_in IN iby_transactions_v.AVScode%TYPE DEFAULT NULL,
633: acquirer_in IN iby_transactions_v.acquirer%TYPE DEFAULT NULL,
626: ( trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
627: trxnid_in IN iby_trxn_summaries_all.transactionid%TYPE,
628: status_in IN iby_trxn_summaries_all.status%TYPE,
629: trxntype_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
630: revised_amt_in IN iby_trxn_summaries_all.amount%TYPE,
631: referencecode_in IN iby_transactions_v.referencecode%TYPE DEFAULT NULL,
632: AVScode_in IN iby_transactions_v.AVScode%TYPE DEFAULT NULL,
633: acquirer_in IN iby_transactions_v.acquirer%TYPE DEFAULT NULL,
634: Auxmsg_in IN iby_transactions_v.Auxmsg%TYPE DEFAULT NULL,
632: AVScode_in IN iby_transactions_v.AVScode%TYPE DEFAULT NULL,
633: acquirer_in IN iby_transactions_v.acquirer%TYPE DEFAULT NULL,
634: Auxmsg_in IN iby_transactions_v.Auxmsg%TYPE DEFAULT NULL,
635: cvv2result_in IN iby_trxn_core.CVV2Result%type,
636: bepcode_in IN iby_trxn_summaries_all.bepcode%TYPE,
637: bepmessage_in IN iby_trxn_summaries_all.bepmessage%TYPE,
638: error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
639: tracenumber_in IN iby_trxn_core.tracenumber%TYPE,
640: authcode_in IN iby_trxn_core.authcode%TYPE,
633: acquirer_in IN iby_transactions_v.acquirer%TYPE DEFAULT NULL,
634: Auxmsg_in IN iby_transactions_v.Auxmsg%TYPE DEFAULT NULL,
635: cvv2result_in IN iby_trxn_core.CVV2Result%type,
636: bepcode_in IN iby_trxn_summaries_all.bepcode%TYPE,
637: bepmessage_in IN iby_trxn_summaries_all.bepmessage%TYPE,
638: error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
639: tracenumber_in IN iby_trxn_core.tracenumber%TYPE,
640: authcode_in IN iby_trxn_core.authcode%TYPE,
641: extend_names_in IN JTF_VARCHAR2_TABLE_100,
634: Auxmsg_in IN iby_transactions_v.Auxmsg%TYPE DEFAULT NULL,
635: cvv2result_in IN iby_trxn_core.CVV2Result%type,
636: bepcode_in IN iby_trxn_summaries_all.bepcode%TYPE,
637: bepmessage_in IN iby_trxn_summaries_all.bepmessage%TYPE,
638: error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
639: tracenumber_in IN iby_trxn_core.tracenumber%TYPE,
640: authcode_in IN iby_trxn_core.authcode%TYPE,
641: extend_names_in IN JTF_VARCHAR2_TABLE_100,
642: extend_vals_in IN JTF_VARCHAR2_TABLE_200
646: l_old_auth_trxnmid NUMBER;
647: l_new_auth_trxnmid NUMBER;
648: l_cursor_empty BOOLEAN;
649:
650: CURSOR c_auth (ci_transactionid iby_trxn_summaries_all.transactionid%TYPE)
651: IS
652: SELECT trxnmid FROM iby_trxn_summaries_all
653: WHERE transactionid = ci_transactionid
654: AND reqtype = 'ORAPMTREQ'
648: l_cursor_empty BOOLEAN;
649:
650: CURSOR c_auth (ci_transactionid iby_trxn_summaries_all.transactionid%TYPE)
651: IS
652: SELECT trxnmid FROM iby_trxn_summaries_all
653: WHERE transactionid = ci_transactionid
654: AND reqtype = 'ORAPMTREQ'
655: AND trxntypeid = 2
656: AND status = 0;
661: -- In case a any extensibility attribute is missing(not echoed back) in the
662: -- reversal response then this attribute value is defaulted to that fetched
663: -- in the original authorization.
664: --
665: CURSOR c_extend (ci_trxnmid_old iby_trxn_summaries_all.trxnmid%TYPE,
666: ci_trxnmid_new iby_trxn_summaries_all.trxnmid%TYPE)
667: IS
668: SELECT old_ext.extend_name extend_name,
669: nvl(new_ext.EXTEND_VALUE, old_ext.extend_value) extend_value
662: -- reversal response then this attribute value is defaulted to that fetched
663: -- in the original authorization.
664: --
665: CURSOR c_extend (ci_trxnmid_old iby_trxn_summaries_all.trxnmid%TYPE,
666: ci_trxnmid_new iby_trxn_summaries_all.trxnmid%TYPE)
667: IS
668: SELECT old_ext.extend_name extend_name,
669: nvl(new_ext.EXTEND_VALUE, old_ext.extend_value) extend_value
670: FROM IBY_TRXN_EXTENSIBILITY OLD_EXT,
674: and old_ext.extend_name=new_ext.extend_name(+);
675:
676: BEGIN
677: iby_debug_pub.add('Enter',IBY_DEBUG_PUB.G_LEVEL_PROCEDURE,l_dbg_mod);
678: UPDATE iby_trxn_summaries_all
679: SET status = status_in,
680: bepcode = bepcode_in,
681: bepmessage = bepmessage_in,
682: UpdateDate = SYSDATE,
699: FETCH c_auth INTO l_old_auth_trxnmid;
700: l_cursor_empty := c_auth%NOTFOUND;
701: CLOSE c_auth;
702:
703: UPDATE iby_trxn_summaries_all
704: SET status = trxntype_in,
705: Last_Update_Date = sysdate,
706: Last_Updated_by = fnd_global.user_id,
707: Object_Version_Number = object_version_number + 1
726: SELECT iby_trxnsumm_mid_s.NEXTVAL
727: INTO l_new_auth_trxnmid
728: FROM dual;
729:
730: INSERT INTO iby_trxn_summaries_all
731: (TrxnMID, TransactionID,TrxntypeID, ReqType, ReqDate,
732: Amount,CurrencyNameCode, UpdateDate,Status, PaymentMethodName,
733: TangibleID,MPayeeID, PayeeID,BEPID,bepKey,mtangibleid,
734: BEPCode,BEPMessage,Errorlocation,ecappid,org_id,
749: org_type, payment_channel_code, factored_flag,
750: cc_issuer_range_id, instrnum_hash, instrnum_length,
751: instrnum_sec_segment_id, payer_party_id, process_profile_code,
752: salt_version,needsupdt,sub_key_id,initiator_extension_id
753: FROM iby_trxn_summaries_all
754: WHERE trxnmid = l_old_auth_trxnmid);
755:
756: INSERT INTO iby_trxn_core (
757: TrxnMID, AuthCode, date_of_voice_authorization, voiceauthflag,
818:
819: /* operation is performed. */
820:
821: PROCEDURE insert_other_txn
822: ( ecapp_id_in IN iby_trxn_summaries_all.ecappid%TYPE,
823: req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
824: order_id_in IN iby_transactions_v.order_id%TYPE,
825: merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
826: vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
819: /* operation is performed. */
820:
821: PROCEDURE insert_other_txn
822: ( ecapp_id_in IN iby_trxn_summaries_all.ecappid%TYPE,
823: req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
824: order_id_in IN iby_transactions_v.order_id%TYPE,
825: merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
826: vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
827: vendor_key_in IN iby_transactions_v.bepkey%TYPE,
836: vendor_code_in IN iby_transactions_v.vendor_code%TYPE,
837: vendor_message_in IN iby_transactions_v.vendor_message%TYPE,
838: error_location_in IN iby_transactions_v.error_location%TYPE,
839: trace_number_in IN iby_transactions_v.TraceNumber%TYPE,
840: org_id_in IN iby_trxn_summaries_all.org_id%type,
841: billeracct_in IN iby_tangible.acctno%type,
842: refinfo_in IN iby_tangible.refinfo%type,
843: memo_in IN iby_tangible.memo%type,
844: order_medium_in IN iby_tangible.order_medium%TYPE,
842: refinfo_in IN iby_tangible.refinfo%type,
843: memo_in IN iby_tangible.memo%type,
844: order_medium_in IN iby_tangible.order_medium%TYPE,
845: eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
846: payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
847: instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
848: payerid_in IN iby_trxn_summaries_all.payerid%type,
849: master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
850: subkey_seed_in IN RAW,
843: memo_in IN iby_tangible.memo%type,
844: order_medium_in IN iby_tangible.order_medium%TYPE,
845: eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
846: payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
847: instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
848: payerid_in IN iby_trxn_summaries_all.payerid%type,
849: master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
850: subkey_seed_in IN RAW,
851: trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
844: order_medium_in IN iby_tangible.order_medium%TYPE,
845: eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
846: payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
847: instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
848: payerid_in IN iby_trxn_summaries_all.payerid%type,
849: master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
850: subkey_seed_in IN RAW,
851: trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
852: instr_expirydate_in IN iby_trxn_core.instr_expirydate%TYPE,
847: instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
848: payerid_in IN iby_trxn_summaries_all.payerid%type,
849: master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
850: subkey_seed_in IN RAW,
851: trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
852: instr_expirydate_in IN iby_trxn_core.instr_expirydate%TYPE,
853: card_subtype_in IN iby_trxn_core.card_subtype_code%TYPE,
854: instr_owner_name_in IN iby_trxn_core.instr_owner_name%TYPE,
855: instr_address_line1_in IN iby_trxn_core.instr_owner_address_line1%TYPE,
862: instr_phonenumber_in IN iby_trxn_core.instr_owner_phone%TYPE,
863: instr_email_in IN iby_trxn_core.instr_owner_email%TYPE,
864: extend_names_in IN JTF_VARCHAR2_TABLE_100,
865: extend_vals_in IN JTF_VARCHAR2_TABLE_200,
866: transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
867: transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
868: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
869: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
870: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
863: instr_email_in IN iby_trxn_core.instr_owner_email%TYPE,
864: extend_names_in IN JTF_VARCHAR2_TABLE_100,
865: extend_vals_in IN JTF_VARCHAR2_TABLE_200,
866: transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
867: transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
868: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
869: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
870: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
871: settlement_date_in IN iby_trxn_summaries_all.settledate%TYPE,
864: extend_names_in IN JTF_VARCHAR2_TABLE_100,
865: extend_vals_in IN JTF_VARCHAR2_TABLE_200,
866: transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
867: transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
868: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
869: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
870: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
871: settlement_date_in IN iby_trxn_summaries_all.settledate%TYPE,
872: settlement_due_date_in IN iby_trxn_summaries_all.settlement_due_date%TYPE,
865: extend_vals_in IN JTF_VARCHAR2_TABLE_200,
866: transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
867: transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
868: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
869: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
870: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
871: settlement_date_in IN iby_trxn_summaries_all.settledate%TYPE,
872: settlement_due_date_in IN iby_trxn_summaries_all.settlement_due_date%TYPE,
873: process_profile_code_in IN iby_trxn_summaries_all.process_profile_code%TYPE,
866: transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
867: transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
868: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
869: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
870: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
871: settlement_date_in IN iby_trxn_summaries_all.settledate%TYPE,
872: settlement_due_date_in IN iby_trxn_summaries_all.settlement_due_date%TYPE,
873: process_profile_code_in IN iby_trxn_summaries_all.process_profile_code%TYPE,
874: instrtype_in IN iby_trxn_summaries_all.instrtype%TYPE
867: transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
868: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
869: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
870: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
871: settlement_date_in IN iby_trxn_summaries_all.settledate%TYPE,
872: settlement_due_date_in IN iby_trxn_summaries_all.settlement_due_date%TYPE,
873: process_profile_code_in IN iby_trxn_summaries_all.process_profile_code%TYPE,
874: instrtype_in IN iby_trxn_summaries_all.instrtype%TYPE
875: )
868: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
869: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
870: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
871: settlement_date_in IN iby_trxn_summaries_all.settledate%TYPE,
872: settlement_due_date_in IN iby_trxn_summaries_all.settlement_due_date%TYPE,
873: process_profile_code_in IN iby_trxn_summaries_all.process_profile_code%TYPE,
874: instrtype_in IN iby_trxn_summaries_all.instrtype%TYPE
875: )
876: IS
869: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
870: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
871: settlement_date_in IN iby_trxn_summaries_all.settledate%TYPE,
872: settlement_due_date_in IN iby_trxn_summaries_all.settlement_due_date%TYPE,
873: process_profile_code_in IN iby_trxn_summaries_all.process_profile_code%TYPE,
874: instrtype_in IN iby_trxn_summaries_all.instrtype%TYPE
875: )
876: IS
877:
870: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
871: settlement_date_in IN iby_trxn_summaries_all.settledate%TYPE,
872: settlement_due_date_in IN iby_trxn_summaries_all.settlement_due_date%TYPE,
873: process_profile_code_in IN iby_trxn_summaries_all.process_profile_code%TYPE,
874: instrtype_in IN iby_trxn_summaries_all.instrtype%TYPE
875: )
876: IS
877:
878: l_num_trxns NUMBER := 0;
877:
878: l_num_trxns NUMBER := 0;
879: l_trxn_mid NUMBER;
880: transaction_id NUMBER;
881: l_tmid iby_trxn_summaries_all.mtangibleid%type;
882: l_order_id iby_trxn_summaries_all.tangibleid%type;
883: l_mpayeeid iby_payee.mpayeeid%type;
884: l_org_id NUMBER;
885: l_target_trxn_type iby_trxn_summaries_all.trxntypeid%TYPE := -1;
878: l_num_trxns NUMBER := 0;
879: l_trxn_mid NUMBER;
880: transaction_id NUMBER;
881: l_tmid iby_trxn_summaries_all.mtangibleid%type;
882: l_order_id iby_trxn_summaries_all.tangibleid%type;
883: l_mpayeeid iby_payee.mpayeeid%type;
884: l_org_id NUMBER;
885: l_target_trxn_type iby_trxn_summaries_all.trxntypeid%TYPE := -1;
886: l_instrtype iby_trxn_summaries_all.instrtype%type;
881: l_tmid iby_trxn_summaries_all.mtangibleid%type;
882: l_order_id iby_trxn_summaries_all.tangibleid%type;
883: l_mpayeeid iby_payee.mpayeeid%type;
884: l_org_id NUMBER;
885: l_target_trxn_type iby_trxn_summaries_all.trxntypeid%TYPE := -1;
886: l_instrtype iby_trxn_summaries_all.instrtype%type;
887: l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
888:
889: lx_cc_hash iby_trxn_summaries_all.instrnum_hash%TYPE;
882: l_order_id iby_trxn_summaries_all.tangibleid%type;
883: l_mpayeeid iby_payee.mpayeeid%type;
884: l_org_id NUMBER;
885: l_target_trxn_type iby_trxn_summaries_all.trxntypeid%TYPE := -1;
886: l_instrtype iby_trxn_summaries_all.instrtype%type;
887: l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
888:
889: lx_cc_hash iby_trxn_summaries_all.instrnum_hash%TYPE;
890: lx_range_id iby_cc_issuer_ranges.cc_issuer_range_id%TYPE;
883: l_mpayeeid iby_payee.mpayeeid%type;
884: l_org_id NUMBER;
885: l_target_trxn_type iby_trxn_summaries_all.trxntypeid%TYPE := -1;
886: l_instrtype iby_trxn_summaries_all.instrtype%type;
887: l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
888:
889: lx_cc_hash iby_trxn_summaries_all.instrnum_hash%TYPE;
890: lx_range_id iby_cc_issuer_ranges.cc_issuer_range_id%TYPE;
891: lx_instr_len iby_trxn_summaries_all.instrnum_length%TYPE;
885: l_target_trxn_type iby_trxn_summaries_all.trxntypeid%TYPE := -1;
886: l_instrtype iby_trxn_summaries_all.instrtype%type;
887: l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
888:
889: lx_cc_hash iby_trxn_summaries_all.instrnum_hash%TYPE;
890: lx_range_id iby_cc_issuer_ranges.cc_issuer_range_id%TYPE;
891: lx_instr_len iby_trxn_summaries_all.instrnum_length%TYPE;
892: lx_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
893: l_old_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
887: l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
888:
889: lx_cc_hash iby_trxn_summaries_all.instrnum_hash%TYPE;
890: lx_range_id iby_cc_issuer_ranges.cc_issuer_range_id%TYPE;
891: lx_instr_len iby_trxn_summaries_all.instrnum_length%TYPE;
892: lx_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
893: l_old_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
894:
895: l_instrnum iby_trxn_summaries_all.instrnumber%TYPE;
888:
889: lx_cc_hash iby_trxn_summaries_all.instrnum_hash%TYPE;
890: lx_range_id iby_cc_issuer_ranges.cc_issuer_range_id%TYPE;
891: lx_instr_len iby_trxn_summaries_all.instrnum_length%TYPE;
892: lx_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
893: l_old_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
894:
895: l_instrnum iby_trxn_summaries_all.instrnumber%TYPE;
896: l_expirydate iby_trxn_core.instr_expirydate%type;
889: lx_cc_hash iby_trxn_summaries_all.instrnum_hash%TYPE;
890: lx_range_id iby_cc_issuer_ranges.cc_issuer_range_id%TYPE;
891: lx_instr_len iby_trxn_summaries_all.instrnum_length%TYPE;
892: lx_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
893: l_old_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
894:
895: l_instrnum iby_trxn_summaries_all.instrnumber%TYPE;
896: l_expirydate iby_trxn_core.instr_expirydate%type;
897:
891: lx_instr_len iby_trxn_summaries_all.instrnum_length%TYPE;
892: lx_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
893: l_old_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
894:
895: l_instrnum iby_trxn_summaries_all.instrnumber%TYPE;
896: l_expirydate iby_trxn_core.instr_expirydate%type;
897:
898: l_cursor_empty BOOLEAN;
899: l_process_profile_code iby_trxn_summaries_all.process_profile_code%TYPE;
895: l_instrnum iby_trxn_summaries_all.instrnumber%TYPE;
896: l_expirydate iby_trxn_core.instr_expirydate%type;
897:
898: l_cursor_empty BOOLEAN;
899: l_process_profile_code iby_trxn_summaries_all.process_profile_code%TYPE;
900: l_payer_party_id iby_trxn_summaries_all.payer_party_id%TYPE;
901: l_pmt_chnl_code iby_trxn_summaries_all.payment_channel_code%TYPE;
902: l_module_name CONSTANT VARCHAR2(200) := 'IBY_TRANSACTIONCC_PKG.insert_other_txn';
903: l_subtype iby_trxn_summaries_all.instrsubtype%type;
896: l_expirydate iby_trxn_core.instr_expirydate%type;
897:
898: l_cursor_empty BOOLEAN;
899: l_process_profile_code iby_trxn_summaries_all.process_profile_code%TYPE;
900: l_payer_party_id iby_trxn_summaries_all.payer_party_id%TYPE;
901: l_pmt_chnl_code iby_trxn_summaries_all.payment_channel_code%TYPE;
902: l_module_name CONSTANT VARCHAR2(200) := 'IBY_TRANSACTIONCC_PKG.insert_other_txn';
903: l_subtype iby_trxn_summaries_all.instrsubtype%type;
904:
897:
898: l_cursor_empty BOOLEAN;
899: l_process_profile_code iby_trxn_summaries_all.process_profile_code%TYPE;
900: l_payer_party_id iby_trxn_summaries_all.payer_party_id%TYPE;
901: l_pmt_chnl_code iby_trxn_summaries_all.payment_channel_code%TYPE;
902: l_module_name CONSTANT VARCHAR2(200) := 'IBY_TRANSACTIONCC_PKG.insert_other_txn';
903: l_subtype iby_trxn_summaries_all.instrsubtype%type;
904:
905: l_currency iby_transactions_v.currency%TYPE;
899: l_process_profile_code iby_trxn_summaries_all.process_profile_code%TYPE;
900: l_payer_party_id iby_trxn_summaries_all.payer_party_id%TYPE;
901: l_pmt_chnl_code iby_trxn_summaries_all.payment_channel_code%TYPE;
902: l_module_name CONSTANT VARCHAR2(200) := 'IBY_TRANSACTIONCC_PKG.insert_other_txn';
903: l_subtype iby_trxn_summaries_all.instrsubtype%type;
904:
905: l_currency iby_transactions_v.currency%TYPE;
906: l_payerid iby_trxn_summaries_all.payerid%TYPE;
907: l_payerinstrid iby_trxn_summaries_all.payerinstrid%TYPE;
902: l_module_name CONSTANT VARCHAR2(200) := 'IBY_TRANSACTIONCC_PKG.insert_other_txn';
903: l_subtype iby_trxn_summaries_all.instrsubtype%type;
904:
905: l_currency iby_transactions_v.currency%TYPE;
906: l_payerid iby_trxn_summaries_all.payerid%TYPE;
907: l_payerinstrid iby_trxn_summaries_all.payerinstrid%TYPE;
908:
909: CURSOR c_followon_info(ci_trxnid iby_trxn_summaries_all.transactionid%TYPE)
910: IS
903: l_subtype iby_trxn_summaries_all.instrsubtype%type;
904:
905: l_currency iby_transactions_v.currency%TYPE;
906: l_payerid iby_trxn_summaries_all.payerid%TYPE;
907: l_payerinstrid iby_trxn_summaries_all.payerinstrid%TYPE;
908:
909: CURSOR c_followon_info(ci_trxnid iby_trxn_summaries_all.transactionid%TYPE)
910: IS
911: SELECT mtangibleid, tangibleid, instrType, instrsubtype,
905: l_currency iby_transactions_v.currency%TYPE;
906: l_payerid iby_trxn_summaries_all.payerid%TYPE;
907: l_payerinstrid iby_trxn_summaries_all.payerinstrid%TYPE;
908:
909: CURSOR c_followon_info(ci_trxnid iby_trxn_summaries_all.transactionid%TYPE)
910: IS
911: SELECT mtangibleid, tangibleid, instrType, instrsubtype,
912: process_profile_code, payer_party_id, payment_channel_code,
913: currencynamecode, payerid, payerinstrid
910: IS
911: SELECT mtangibleid, tangibleid, instrType, instrsubtype,
912: process_profile_code, payer_party_id, payment_channel_code,
913: currencynamecode, payerid, payerinstrid
914: FROM iby_trxn_summaries_all
915: WHERE (transactionid = ci_trxnid)
916: -- ignore the reversal trxntypes
917: AND (trxntypeid NOT IN (22,23))
918: --
1063: print_debuginfo(l_module_name, 'settledate='||settlement_date_in);
1064: print_debuginfo(l_module_name, 'settlement_due_date='||settlement_due_date_in);
1065:
1066: END IF;
1067: INSERT INTO iby_trxn_summaries_all
1068:
1069: (TrxnMID, TransactionID,TrxntypeID, ReqType, ReqDate,
1070: Amount,CurrencyNameCode, UpdateDate,Status, PaymentMethodName,
1071: TangibleID,MPayeeID, PayeeID,BEPID,bepKey, MtangibleId,
1116: -- One previous transaction, so update previous row
1117:
1118: SELECT TrxnMID,Mtangibleid,transactionid, instrnum_sec_segment_id
1119: INTO l_trxn_mid,l_tmid,transaction_id_in_out, l_old_segment_id
1120: FROM iby_trxn_summaries_all
1121: WHERE TangibleID = order_id_in
1122: AND UPPER(ReqType) = UPPER(req_type_in)
1123: AND PayeeID = merchant_id_in
1124: AND Status IN (9,11);
1149:
1150: END IF;
1151: -- Bug:14007884 - added paymentchannelcode.
1152:
1153: UPDATE iby_trxn_summaries_all
1154: SET BEPID = vendor_id_in,
1155: bepKey = vendor_key_in,
1156: Amount = amount_in,
1157: TrxntypeID = trxn_type_in,
1251: l_target_trxn_type := 11;
1252:
1253: END IF;
1254:
1255: UPDATE iby_trxn_summaries_all
1256: SET
1257: -- CHANGE: UPDATE STATUS FOR VOIDED GATEWAY TRXNS
1258: --
1259: -- currently only change the status for processor
1277: /* Inserts a row into the iby_transaction table if auth, capture, */
1278: /* return, credit, and void timeout */
1279:
1280: PROCEDURE insert_timeout_txn
1281: (req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
1282: order_id_in IN iby_transactions_v.order_id%TYPE,
1283:
1284: merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
1285: vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
1283:
1284: merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
1285: vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
1286: vendor_key_in IN iby_transactions_v.bepkey%TYPE,
1287: ecapp_id_in IN iby_trxn_summaries_all.ecappid%TYPE,
1288: time_in IN iby_transactions_v.time%TYPE,
1289:
1290:
1291: status_in IN iby_transactions_v.status%TYPE,
1288: time_in IN iby_transactions_v.time%TYPE,
1289:
1290:
1291: status_in IN iby_transactions_v.status%TYPE,
1292: org_id_in IN iby_trxn_summaries_all.org_id%type,
1293: amount_in IN iby_tangible.amount%type,
1294: currency_in IN iby_tangible.currencynamecode%type,
1295: billeracct_in IN iby_tangible.acctno%type,
1296: refinfo_in IN iby_tangible.refinfo%type,
1296: refinfo_in IN iby_tangible.refinfo%type,
1297: memo_in IN iby_tangible.memo%type,
1298: order_medium_in IN iby_tangible.order_medium%TYPE,
1299: eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
1300: payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
1301: instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
1302: payerid_in IN iby_trxn_summaries_all.payerid%type,
1303: instrtype_in IN iby_trxn_summaries_all.instrType%type,
1304: master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
1297: memo_in IN iby_tangible.memo%type,
1298: order_medium_in IN iby_tangible.order_medium%TYPE,
1299: eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
1300: payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
1301: instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
1302: payerid_in IN iby_trxn_summaries_all.payerid%type,
1303: instrtype_in IN iby_trxn_summaries_all.instrType%type,
1304: master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
1305: subkey_seed_in IN RAW,
1298: order_medium_in IN iby_tangible.order_medium%TYPE,
1299: eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
1300: payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
1301: instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
1302: payerid_in IN iby_trxn_summaries_all.payerid%type,
1303: instrtype_in IN iby_trxn_summaries_all.instrType%type,
1304: master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
1305: subkey_seed_in IN RAW,
1306: trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
1299: eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
1300: payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
1301: instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
1302: payerid_in IN iby_trxn_summaries_all.payerid%type,
1303: instrtype_in IN iby_trxn_summaries_all.instrType%type,
1304: master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
1305: subkey_seed_in IN RAW,
1306: trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
1307: transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
1302: payerid_in IN iby_trxn_summaries_all.payerid%type,
1303: instrtype_in IN iby_trxn_summaries_all.instrType%type,
1304: master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
1305: subkey_seed_in IN RAW,
1306: trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
1307: transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
1308: transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
1309: trxntypeid_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
1310: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
1303: instrtype_in IN iby_trxn_summaries_all.instrType%type,
1304: master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
1305: subkey_seed_in IN RAW,
1306: trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
1307: transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
1308: transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
1309: trxntypeid_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
1310: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
1311: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
1304: master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
1305: subkey_seed_in IN RAW,
1306: trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
1307: transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
1308: transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
1309: trxntypeid_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
1310: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
1311: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
1312: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
1305: subkey_seed_in IN RAW,
1306: trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
1307: transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
1308: transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
1309: trxntypeid_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
1310: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
1311: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
1312: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
1313: reauth_transaction_id IN iby_trxn_summaries_all.TransactionID%TYPE
1306: trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
1307: transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
1308: transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
1309: trxntypeid_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
1310: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
1311: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
1312: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
1313: reauth_transaction_id IN iby_trxn_summaries_all.TransactionID%TYPE
1314: )
1307: transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
1308: transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
1309: trxntypeid_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
1310: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
1311: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
1312: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
1313: reauth_transaction_id IN iby_trxn_summaries_all.TransactionID%TYPE
1314: )
1315:
1308: transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
1309: trxntypeid_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
1310: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
1311: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
1312: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
1313: reauth_transaction_id IN iby_trxn_summaries_all.TransactionID%TYPE
1314: )
1315:
1316: IS
1309: trxntypeid_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
1310: org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
1311: payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
1312: factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
1313: reauth_transaction_id IN iby_trxn_summaries_all.TransactionID%TYPE
1314: )
1315:
1316: IS
1317:
1317:
1318: l_num_trxns NUMBER := 0;
1319: l_trxn_mid NUMBER;
1320: transaction_id NUMBER;
1321: l_tmid iby_trxn_summaries_all.mtangibleid%type;
1322: l_order_id iby_trxn_summaries_all.tangibleid%type;
1323: l_mpayeeid iby_payee.mpayeeid%type;
1324: l_org_id NUMBER;
1325: l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
1318: l_num_trxns NUMBER := 0;
1319: l_trxn_mid NUMBER;
1320: transaction_id NUMBER;
1321: l_tmid iby_trxn_summaries_all.mtangibleid%type;
1322: l_order_id iby_trxn_summaries_all.tangibleid%type;
1323: l_mpayeeid iby_payee.mpayeeid%type;
1324: l_org_id NUMBER;
1325: l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
1326:
1321: l_tmid iby_trxn_summaries_all.mtangibleid%type;
1322: l_order_id iby_trxn_summaries_all.tangibleid%type;
1323: l_mpayeeid iby_payee.mpayeeid%type;
1324: l_org_id NUMBER;
1325: l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
1326:
1327: l_return_status VARCHAR2(1);
1328: l_msg_count NUMBER;
1329: l_msg_data VARCHAR2(200);
1329: l_msg_data VARCHAR2(200);
1330: l_checksum_valid BOOLEAN := FALSE; -- To check whether the card number is valid.
1331: l_cc_type VARCHAR2(80);
1332:
1333: l_instrnum iby_trxn_summaries_all.instrnumber%TYPE;
1334: l_subkey_id iby_payee_subkeys.payee_subkey_id%TYPE;
1335:
1336: BEGIN
1337:
1371: --tangible info should already exist, get them based on
1372: --transactionid
1373: SELECT DISTINCT mtangibleid, tangibleid
1374: INTO l_tmid, l_order_id
1375: FROM iby_trxn_summaries_all
1376: WHERE transactionid = transaction_id_out
1377: --
1378: -- 100 is equivalent to 0
1379: --
1391: /*
1392: prepare_instr_data(ecapp_id_in,merchant_id_in,master_key_in,
1393: instrnum_in,subkey_seed_in,FND_API.G_TRUE,l_instrnum,l_subkey_id);
1394: */
1395: INSERT INTO iby_trxn_summaries_all
1396: (TrxnMID, TransactionID, ReqType, ReqDate,
1397: UpdateDate,Status, Amount, CurrencyNameCode,
1398: TangibleID,MPayeeID, PayeeID,BEPID,bepKey, ECAppID,org_id,mtangibleid,
1399: payerinstrid, instrnumber, sub_key_id, payerid, instrType,
1422: -- One previous transaction, so update previous row
1423: SELECT TrxnMID, TransactionID, MtangibleId
1424: INTO l_trxn_mid, transaction_id_out, l_tmid
1425:
1426: FROM iby_trxn_summaries_all
1427: WHERE TangibleID = order_id_in
1428: AND UPPER(ReqType) = UPPER(req_type_in)
1429: AND PayeeID = merchant_id_in
1430: AND Status IN (9,11);
1439: order_medium_in, eft_auth_method_in);
1440:
1441: END IF;
1442:
1443: UPDATE iby_trxn_summaries_all
1444: SET UpdateDate = time_in,
1445: Status = status_in,
1446: BEPID = vendor_id_in,
1447: bepKey = vendor_key_in,
1500: vendor_message_in IN iby_batches_all.BEPmessage%TYPE,
1501: error_location_in IN iby_batches_all.errorlocation%TYPE,
1502: terminal_id_in IN iby_batches_all.TerminalId%TYPE,
1503: acquirer_id_in IN iby_batches_all.Acquirer%TYPE,
1504: org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
1505: req_type_in IN iby_batches_all.reqtype%TYPE,
1506: sec_key_present_in IN VARCHAR2,
1507: mbatchid_out OUT NOCOPY iby_batches_all.mbatchid%type
1508: )
1533: --
1534: -- need to lock trxn summaries table to ensure that
1535: -- trxns which have not been validated do not sneak into
1536: -- the batch; gap between call to validate_open_batch
1537: -- and update of IBY_TRXN_SUMMARIES_ALL has been shown
1538: -- to be vulnerable to race conditions even under moderate
1539: -- concurrency loads
1540: --
1541: --LOCK TABLE iby_batches_all, iby_trxn_summaries_all IN EXCLUSIVE MODE;
1537: -- and update of IBY_TRXN_SUMMARIES_ALL has been shown
1538: -- to be vulnerable to race conditions even under moderate
1539: -- concurrency loads
1540: --
1541: --LOCK TABLE iby_batches_all, iby_trxn_summaries_all IN EXCLUSIVE MODE;
1542:
1543: SELECT iby_batches_s.NEXTVAL
1544: INTO l_mbatchid
1545: FROM dual;
1565: --
1566: -- associate all trxns in the current open batch
1567: -- with the bathc id of the batch close
1568: --
1569: UPDATE iby_trxn_summaries_all
1570: SET
1571: status = iby_transactioncc_pkg.C_STATUS_BATCH_PENDING,
1572: batchid = merch_batchid_in,
1573: mbatchid = l_mbatchid,
1733: vendor_message_in IN iby_batches_all.BEPmessage%TYPE,
1734: error_location_in IN iby_batches_all.errorlocation%TYPE,
1735: terminal_id_in IN iby_batches_all.TerminalId%TYPE,
1736: acquirer_id_in IN iby_batches_all.Acquirer%TYPE,
1737: org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
1738: req_type_in IN iby_batches_all.reqtype%TYPE,
1739: sec_key_present_in IN VARCHAR2,
1740: acct_profile_in IN iby_batches_all.process_profile_code%TYPE,
1741: instr_type_in IN iby_batches_all.instrument_type%TYPE,
1739: sec_key_present_in IN VARCHAR2,
1740: acct_profile_in IN iby_batches_all.process_profile_code%TYPE,
1741: instr_type_in IN iby_batches_all.instrument_type%TYPE,
1742: br_disputed_flag_in IN iby_batches_all.br_disputed_flag%TYPE,
1743: f_pmt_channel_in IN iby_trxn_summaries_all.
1744: payment_channel_code%TYPE,
1745: f_curr_in IN iby_trxn_summaries_all.
1746: currencynamecode%TYPE,
1747: f_settle_date IN iby_trxn_summaries_all.
1741: instr_type_in IN iby_batches_all.instrument_type%TYPE,
1742: br_disputed_flag_in IN iby_batches_all.br_disputed_flag%TYPE,
1743: f_pmt_channel_in IN iby_trxn_summaries_all.
1744: payment_channel_code%TYPE,
1745: f_curr_in IN iby_trxn_summaries_all.
1746: currencynamecode%TYPE,
1747: f_settle_date IN iby_trxn_summaries_all.
1748: settledate%TYPE,
1749: f_due_date IN iby_trxn_summaries_all.
1743: f_pmt_channel_in IN iby_trxn_summaries_all.
1744: payment_channel_code%TYPE,
1745: f_curr_in IN iby_trxn_summaries_all.
1746: currencynamecode%TYPE,
1747: f_settle_date IN iby_trxn_summaries_all.
1748: settledate%TYPE,
1749: f_due_date IN iby_trxn_summaries_all.
1750: settlement_due_date%TYPE,
1751: f_maturity_date IN iby_trxn_summaries_all.
1745: f_curr_in IN iby_trxn_summaries_all.
1746: currencynamecode%TYPE,
1747: f_settle_date IN iby_trxn_summaries_all.
1748: settledate%TYPE,
1749: f_due_date IN iby_trxn_summaries_all.
1750: settlement_due_date%TYPE,
1751: f_maturity_date IN iby_trxn_summaries_all.
1752: br_maturity_date%TYPE,
1753: f_instr_type IN iby_trxn_summaries_all.
1747: f_settle_date IN iby_trxn_summaries_all.
1748: settledate%TYPE,
1749: f_due_date IN iby_trxn_summaries_all.
1750: settlement_due_date%TYPE,
1751: f_maturity_date IN iby_trxn_summaries_all.
1752: br_maturity_date%TYPE,
1753: f_instr_type IN iby_trxn_summaries_all.
1754: instrtype%TYPE,
1755: mbatch_ids_out OUT NOCOPY JTF_NUMBER_TABLE,
1749: f_due_date IN iby_trxn_summaries_all.
1750: settlement_due_date%TYPE,
1751: f_maturity_date IN iby_trxn_summaries_all.
1752: br_maturity_date%TYPE,
1753: f_instr_type IN iby_trxn_summaries_all.
1754: instrtype%TYPE,
1755: mbatch_ids_out OUT NOCOPY JTF_NUMBER_TABLE,
1756: batch_ids_out OUT NOCOPY JTF_VARCHAR2_TABLE_100
1757: )
1829: --
1830: -- need to lock trxn summaries table to ensure that
1831: -- trxns which have not been validated do not sneak into
1832: -- the batch; gap between call to validate_open_batch
1833: -- and update of IBY_TRXN_SUMMARIES_ALL has been shown
1834: -- to be vulnerable to race conditions even under moderate
1835: -- concurrency loads
1836: --
1837: --LOCK TABLE iby_batches_all, iby_trxn_summaries_all IN EXCLUSIVE MODE;
1833: -- and update of IBY_TRXN_SUMMARIES_ALL has been shown
1834: -- to be vulnerable to race conditions even under moderate
1835: -- concurrency loads
1836: --
1837: --LOCK TABLE iby_batches_all, iby_trxn_summaries_all IN EXCLUSIVE MODE;
1838:
1839: --SELECT iby_batches_s.NEXTVAL
1840: -- INTO l_mbatchid
1841: --FROM dual;
1992: * here for documentation purposes.
1993: */
1994: /*------------------------------------------
1995: UPDATE
1996: IBY_TRXN_SUMMARIES_ALL
1997: SET
1998: status = iby_transactioncc_pkg.C_STATUS_BATCH_PENDING,
1999: batchid = merch_batchid_in
2000: || '_' || i,
2032: || 'transaction ' || l_trxns_in_batch_tab(i).trxn_id);
2033:
2034: END IF;
2035: UPDATE
2036: IBY_TRXN_SUMMARIES_ALL
2037: SET
2038: status = iby_transactioncc_pkg.
2039: C_STATUS_BATCH_PENDING,
2040: batchid = l_trxns_in_batch_tab(i).
2366: vendor_message_in IN iby_batches_all.BEPmessage%TYPE,
2367: error_location_in IN iby_batches_all.errorlocation%TYPE,
2368: terminal_id_in IN iby_batches_all.TerminalId%TYPE,
2369: acquirer_id_in IN iby_batches_all.Acquirer%TYPE,
2370: org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
2371: req_type_in IN iby_batches_all.reqtype%TYPE,
2372: sec_key_present_in IN VARCHAR2,
2373: acct_profile_in IN iby_batches_all.process_profile_code%TYPE,
2374: instr_type_in IN iby_batches_all.instrument_type%TYPE,
2372: sec_key_present_in IN VARCHAR2,
2373: acct_profile_in IN iby_batches_all.process_profile_code%TYPE,
2374: instr_type_in IN iby_batches_all.instrument_type%TYPE,
2375: br_disputed_flag_in IN iby_batches_all.br_disputed_flag%TYPE,
2376: f_pmt_channel_in IN iby_trxn_summaries_all.
2377: payment_channel_code%TYPE,
2378: f_curr_in IN iby_trxn_summaries_all.
2379: currencynamecode%TYPE,
2380: f_settle_date IN iby_trxn_summaries_all.
2374: instr_type_in IN iby_batches_all.instrument_type%TYPE,
2375: br_disputed_flag_in IN iby_batches_all.br_disputed_flag%TYPE,
2376: f_pmt_channel_in IN iby_trxn_summaries_all.
2377: payment_channel_code%TYPE,
2378: f_curr_in IN iby_trxn_summaries_all.
2379: currencynamecode%TYPE,
2380: f_settle_date IN iby_trxn_summaries_all.
2381: settledate%TYPE,
2382: f_due_date IN iby_trxn_summaries_all.
2376: f_pmt_channel_in IN iby_trxn_summaries_all.
2377: payment_channel_code%TYPE,
2378: f_curr_in IN iby_trxn_summaries_all.
2379: currencynamecode%TYPE,
2380: f_settle_date IN iby_trxn_summaries_all.
2381: settledate%TYPE,
2382: f_due_date IN iby_trxn_summaries_all.
2383: settlement_due_date%TYPE,
2384: f_maturity_date IN iby_trxn_summaries_all.
2378: f_curr_in IN iby_trxn_summaries_all.
2379: currencynamecode%TYPE,
2380: f_settle_date IN iby_trxn_summaries_all.
2381: settledate%TYPE,
2382: f_due_date IN iby_trxn_summaries_all.
2383: settlement_due_date%TYPE,
2384: f_maturity_date IN iby_trxn_summaries_all.
2385: br_maturity_date%TYPE,
2386: f_instr_type IN iby_trxn_summaries_all.
2380: f_settle_date IN iby_trxn_summaries_all.
2381: settledate%TYPE,
2382: f_due_date IN iby_trxn_summaries_all.
2383: settlement_due_date%TYPE,
2384: f_maturity_date IN iby_trxn_summaries_all.
2385: br_maturity_date%TYPE,
2386: f_instr_type IN iby_trxn_summaries_all.
2387: instrtype%TYPE,
2388: mbatch_ids_out OUT NOCOPY JTF_NUMBER_TABLE,
2382: f_due_date IN iby_trxn_summaries_all.
2383: settlement_due_date%TYPE,
2384: f_maturity_date IN iby_trxn_summaries_all.
2385: br_maturity_date%TYPE,
2386: f_instr_type IN iby_trxn_summaries_all.
2387: instrtype%TYPE,
2388: mbatch_ids_out OUT NOCOPY JTF_NUMBER_TABLE,
2389: batch_ids_out OUT NOCOPY JTF_VARCHAR2_TABLE_100
2390: )
2493: --
2494: -- need to lock trxn summaries table to ensure that
2495: -- trxns which have not been validated do not sneak into
2496: -- the batch; gap between call to validate_open_batch
2497: -- and update of IBY_TRXN_SUMMARIES_ALL has been shown
2498: -- to be vulnerable to race conditions even under moderate
2499: -- concurrency loads
2500: --
2501: --LOCK TABLE iby_batches_all, iby_trxn_summaries_all IN EXCLUSIVE MODE;
2497: -- and update of IBY_TRXN_SUMMARIES_ALL has been shown
2498: -- to be vulnerable to race conditions even under moderate
2499: -- concurrency loads
2500: --
2501: --LOCK TABLE iby_batches_all, iby_trxn_summaries_all IN EXCLUSIVE MODE;
2502:
2503: --SELECT iby_batches_s.NEXTVAL
2504: -- INTO l_mbatchid
2505: --FROM dual;
2654: * here for documentation purposes.
2655: */
2656: /*------------------------------------------
2657: UPDATE
2658: IBY_TRXN_SUMMARIES_ALL
2659: SET
2660: status = iby_transactioncc_pkg.C_STATUS_BATCH_PENDING,
2661: batchid = merch_batchid_in
2662: || '_' || i,
2695:
2696: --END IF;
2697:
2698: /*UPDATE
2699: IBY_TRXN_SUMMARIES_ALL
2700: SET
2701: status = iby_transactioncc_pkg.
2702: C_STATUS_BATCH_PENDING,
2703: batchid = l_trxns_in_batch_tab(i).
3026: *---------------------------------------------------------------------*/
3027: PROCEDURE performTransactionGrouping(
3028: p_profile_code IN IBY_FNDCPT_USER_CC_PF_B.
3029: user_cc_profile_code%TYPE,
3030: instr_type IN IBY_TRXN_SUMMARIES_ALL.
3031: instrtype%TYPE,
3032: req_type IN IBY_BATCHES_ALL.
3033: reqtype%TYPE,
3034: f_pmt_channel_in IN IBY_TRXN_SUMMARIES_ALL.
3030: instr_type IN IBY_TRXN_SUMMARIES_ALL.
3031: instrtype%TYPE,
3032: req_type IN IBY_BATCHES_ALL.
3033: reqtype%TYPE,
3034: f_pmt_channel_in IN IBY_TRXN_SUMMARIES_ALL.
3035: payment_channel_code%TYPE,
3036: f_curr_in IN IBY_TRXN_SUMMARIES_ALL.
3037: currencynamecode%TYPE,
3038: f_settle_date IN IBY_TRXN_SUMMARIES_ALL.
3032: req_type IN IBY_BATCHES_ALL.
3033: reqtype%TYPE,
3034: f_pmt_channel_in IN IBY_TRXN_SUMMARIES_ALL.
3035: payment_channel_code%TYPE,
3036: f_curr_in IN IBY_TRXN_SUMMARIES_ALL.
3037: currencynamecode%TYPE,
3038: f_settle_date IN IBY_TRXN_SUMMARIES_ALL.
3039: settledate%TYPE,
3040: f_due_date IN IBY_TRXN_SUMMARIES_ALL.
3034: f_pmt_channel_in IN IBY_TRXN_SUMMARIES_ALL.
3035: payment_channel_code%TYPE,
3036: f_curr_in IN IBY_TRXN_SUMMARIES_ALL.
3037: currencynamecode%TYPE,
3038: f_settle_date IN IBY_TRXN_SUMMARIES_ALL.
3039: settledate%TYPE,
3040: f_due_date IN IBY_TRXN_SUMMARIES_ALL.
3041: settlement_due_date%TYPE,
3042: f_maturity_date IN IBY_TRXN_SUMMARIES_ALL.
3036: f_curr_in IN IBY_TRXN_SUMMARIES_ALL.
3037: currencynamecode%TYPE,
3038: f_settle_date IN IBY_TRXN_SUMMARIES_ALL.
3039: settledate%TYPE,
3040: f_due_date IN IBY_TRXN_SUMMARIES_ALL.
3041: settlement_due_date%TYPE,
3042: f_maturity_date IN IBY_TRXN_SUMMARIES_ALL.
3043: br_maturity_date%TYPE,
3044: f_instr_type IN IBY_TRXN_SUMMARIES_ALL.
3038: f_settle_date IN IBY_TRXN_SUMMARIES_ALL.
3039: settledate%TYPE,
3040: f_due_date IN IBY_TRXN_SUMMARIES_ALL.
3041: settlement_due_date%TYPE,
3042: f_maturity_date IN IBY_TRXN_SUMMARIES_ALL.
3043: br_maturity_date%TYPE,
3044: f_instr_type IN IBY_TRXN_SUMMARIES_ALL.
3045: instrtype%TYPE,
3046: x_batchTab IN OUT NOCOPY IBY_TRANSACTIONCC_PKG.
3040: f_due_date IN IBY_TRXN_SUMMARIES_ALL.
3041: settlement_due_date%TYPE,
3042: f_maturity_date IN IBY_TRXN_SUMMARIES_ALL.
3043: br_maturity_date%TYPE,
3044: f_instr_type IN IBY_TRXN_SUMMARIES_ALL.
3045: instrtype%TYPE,
3046: x_batchTab IN OUT NOCOPY IBY_TRANSACTIONCC_PKG.
3047: batchAttrTabType,
3048: x_trxnsInBatchTab IN OUT NOCOPY IBY_TRANSACTIONCC_PKG.
3108: *
3109: * A separate data structure is needed to keep track of the transactions
3110: * that are part of a batch. This information is tracked in the
3111: * trxnsInBatchTabType table. The rows in trxnsInBatchTabType are
3112: * used to update the rows in IBY_TRXN_SUMMARIES_ALL table with
3113: * batch ids.
3114: *
3115: * l_batchTab l_trxnsInBatchTab
3116: * (insert into IBY_BATCHES_ALL) (update IBY_TRXN_SUMMARIES_ALL)
3112: * used to update the rows in IBY_TRXN_SUMMARIES_ALL table with
3113: * batch ids.
3114: *
3115: * l_batchTab l_trxnsInBatchTab
3116: * (insert into IBY_BATCHES_ALL) (update IBY_TRXN_SUMMARIES_ALL)
3117: * /-------------------------------------\ /------------\
3118: * |MBatch |Profile|..|Curr |Org |..| |MBatch |Trx |
3119: * |Id |Code |..|Code |Id |..| |Id |Id |
3120: * | | |..| | |..| | | |
3145: l_pinlessdebitcard CONSTANT VARCHAR2(100) :='PINLESSDEBITCARD';
3146: l_bankaccount CONSTANT VARCHAR2(100) :='BANKACCOUNT';
3147:
3148: /* previous transaction attributes */
3149: prev_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
3150: prev_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
3151: prev_trxn_amount iby_trxn_summaries_all.amount%TYPE;
3152: prev_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
3153: prev_org_id iby_trxn_summaries_all.org_id%TYPE;
3146: l_bankaccount CONSTANT VARCHAR2(100) :='BANKACCOUNT';
3147:
3148: /* previous transaction attributes */
3149: prev_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
3150: prev_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
3151: prev_trxn_amount iby_trxn_summaries_all.amount%TYPE;
3152: prev_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
3153: prev_org_id iby_trxn_summaries_all.org_id%TYPE;
3154: prev_org_type iby_trxn_summaries_all.org_type%TYPE;
3147:
3148: /* previous transaction attributes */
3149: prev_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
3150: prev_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
3151: prev_trxn_amount iby_trxn_summaries_all.amount%TYPE;
3152: prev_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
3153: prev_org_id iby_trxn_summaries_all.org_id%TYPE;
3154: prev_org_type iby_trxn_summaries_all.org_type%TYPE;
3155: prev_settle_date iby_trxn_summaries_all.settledate%TYPE;
3148: /* previous transaction attributes */
3149: prev_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
3150: prev_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
3151: prev_trxn_amount iby_trxn_summaries_all.amount%TYPE;
3152: prev_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
3153: prev_org_id iby_trxn_summaries_all.org_id%TYPE;
3154: prev_org_type iby_trxn_summaries_all.org_type%TYPE;
3155: prev_settle_date iby_trxn_summaries_all.settledate%TYPE;
3156: prev_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
3149: prev_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
3150: prev_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
3151: prev_trxn_amount iby_trxn_summaries_all.amount%TYPE;
3152: prev_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
3153: prev_org_id iby_trxn_summaries_all.org_id%TYPE;
3154: prev_org_type iby_trxn_summaries_all.org_type%TYPE;
3155: prev_settle_date iby_trxn_summaries_all.settledate%TYPE;
3156: prev_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
3157: prev_bep_key iby_trxn_summaries_all.bepkey%TYPE;
3150: prev_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
3151: prev_trxn_amount iby_trxn_summaries_all.amount%TYPE;
3152: prev_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
3153: prev_org_id iby_trxn_summaries_all.org_id%TYPE;
3154: prev_org_type iby_trxn_summaries_all.org_type%TYPE;
3155: prev_settle_date iby_trxn_summaries_all.settledate%TYPE;
3156: prev_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
3157: prev_bep_key iby_trxn_summaries_all.bepkey%TYPE;
3158: prev_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
3151: prev_trxn_amount iby_trxn_summaries_all.amount%TYPE;
3152: prev_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
3153: prev_org_id iby_trxn_summaries_all.org_id%TYPE;
3154: prev_org_type iby_trxn_summaries_all.org_type%TYPE;
3155: prev_settle_date iby_trxn_summaries_all.settledate%TYPE;
3156: prev_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
3157: prev_bep_key iby_trxn_summaries_all.bepkey%TYPE;
3158: prev_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
3159:
3152: prev_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
3153: prev_org_id iby_trxn_summaries_all.org_id%TYPE;
3154: prev_org_type iby_trxn_summaries_all.org_type%TYPE;
3155: prev_settle_date iby_trxn_summaries_all.settledate%TYPE;
3156: prev_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
3157: prev_bep_key iby_trxn_summaries_all.bepkey%TYPE;
3158: prev_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
3159:
3160: /* current transaction attributes */
3153: prev_org_id iby_trxn_summaries_all.org_id%TYPE;
3154: prev_org_type iby_trxn_summaries_all.org_type%TYPE;
3155: prev_settle_date iby_trxn_summaries_all.settledate%TYPE;
3156: prev_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
3157: prev_bep_key iby_trxn_summaries_all.bepkey%TYPE;
3158: prev_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
3159:
3160: /* current transaction attributes */
3161: curr_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
3154: prev_org_type iby_trxn_summaries_all.org_type%TYPE;
3155: prev_settle_date iby_trxn_summaries_all.settledate%TYPE;
3156: prev_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
3157: prev_bep_key iby_trxn_summaries_all.bepkey%TYPE;
3158: prev_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
3159:
3160: /* current transaction attributes */
3161: curr_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
3162: curr_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
3157: prev_bep_key iby_trxn_summaries_all.bepkey%TYPE;
3158: prev_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
3159:
3160: /* current transaction attributes */
3161: curr_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
3162: curr_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
3163: curr_trxn_amount iby_trxn_summaries_all.amount%TYPE;
3164: curr_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
3165: curr_org_id iby_trxn_summaries_all.org_id%TYPE;
3158: prev_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
3159:
3160: /* current transaction attributes */
3161: curr_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
3162: curr_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
3163: curr_trxn_amount iby_trxn_summaries_all.amount%TYPE;
3164: curr_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
3165: curr_org_id iby_trxn_summaries_all.org_id%TYPE;
3166: curr_org_type iby_trxn_summaries_all.org_type%TYPE;
3159:
3160: /* current transaction attributes */
3161: curr_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
3162: curr_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
3163: curr_trxn_amount iby_trxn_summaries_all.amount%TYPE;
3164: curr_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
3165: curr_org_id iby_trxn_summaries_all.org_id%TYPE;
3166: curr_org_type iby_trxn_summaries_all.org_type%TYPE;
3167: curr_settle_date iby_trxn_summaries_all.settledate%TYPE;
3160: /* current transaction attributes */
3161: curr_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
3162: curr_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
3163: curr_trxn_amount iby_trxn_summaries_all.amount%TYPE;
3164: curr_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
3165: curr_org_id iby_trxn_summaries_all.org_id%TYPE;
3166: curr_org_type iby_trxn_summaries_all.org_type%TYPE;
3167: curr_settle_date iby_trxn_summaries_all.settledate%TYPE;
3168: curr_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
3161: curr_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
3162: curr_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
3163: curr_trxn_amount iby_trxn_summaries_all.amount%TYPE;
3164: curr_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
3165: curr_org_id iby_trxn_summaries_all.org_id%TYPE;
3166: curr_org_type iby_trxn_summaries_all.org_type%TYPE;
3167: curr_settle_date iby_trxn_summaries_all.settledate%TYPE;
3168: curr_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
3169: curr_bep_key iby_trxn_summaries_all.bepkey%TYPE;
3162: curr_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
3163: curr_trxn_amount iby_trxn_summaries_all.amount%TYPE;
3164: curr_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
3165: curr_org_id iby_trxn_summaries_all.org_id%TYPE;
3166: curr_org_type iby_trxn_summaries_all.org_type%TYPE;
3167: curr_settle_date iby_trxn_summaries_all.settledate%TYPE;
3168: curr_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
3169: curr_bep_key iby_trxn_summaries_all.bepkey%TYPE;
3170: curr_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
3163: curr_trxn_amount iby_trxn_summaries_all.amount%TYPE;
3164: curr_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
3165: curr_org_id iby_trxn_summaries_all.org_id%TYPE;
3166: curr_org_type iby_trxn_summaries_all.org_type%TYPE;
3167: curr_settle_date iby_trxn_summaries_all.settledate%TYPE;
3168: curr_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
3169: curr_bep_key iby_trxn_summaries_all.bepkey%TYPE;
3170: curr_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
3171: l_user_pf_table_name VARCHAR2(100);
3164: curr_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
3165: curr_org_id iby_trxn_summaries_all.org_id%TYPE;
3166: curr_org_type iby_trxn_summaries_all.org_type%TYPE;
3167: curr_settle_date iby_trxn_summaries_all.settledate%TYPE;
3168: curr_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
3169: curr_bep_key iby_trxn_summaries_all.bepkey%TYPE;
3170: curr_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
3171: l_user_pf_table_name VARCHAR2(100);
3172: l_sys_pf_table_name VARCHAR2(100);
3165: curr_org_id iby_trxn_summaries_all.org_id%TYPE;
3166: curr_org_type iby_trxn_summaries_all.org_type%TYPE;
3167: curr_settle_date iby_trxn_summaries_all.settledate%TYPE;
3168: curr_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
3169: curr_bep_key iby_trxn_summaries_all.bepkey%TYPE;
3170: curr_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
3171: l_user_pf_table_name VARCHAR2(100);
3172: l_sys_pf_table_name VARCHAR2(100);
3173: l_user_pf_column_name VARCHAR2(100);
3166: curr_org_type iby_trxn_summaries_all.org_type%TYPE;
3167: curr_settle_date iby_trxn_summaries_all.settledate%TYPE;
3168: curr_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
3169: curr_bep_key iby_trxn_summaries_all.bepkey%TYPE;
3170: curr_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
3171: l_user_pf_table_name VARCHAR2(100);
3172: l_sys_pf_table_name VARCHAR2(100);
3173: l_user_pf_column_name VARCHAR2(100);
3174: l_sys_pf_column_name VARCHAR2(100);
3220: sys_prof.limit_by_exch_rate_type,
3221: sys_prof.limit_by_total_amt,
3222: sys_prof.limit_by_settlement_num
3223: FROM
3224: IBY_TRXN_SUMMARIES_ALL txn,
3225: IBY_FNDCPT_USER_CC_PF_B user_prof,
3226: IBY_FNDCPT_SYS_CC_PF_B sys_prof
3227: WHERE
3228: user_prof.user_cc_profile_code = p_profile_code AND
3562: || 'sys_prof.limit_by_exch_rate_type, '
3563: || 'sys_prof.limit_by_total_amt, '
3564: || 'sys_prof.limit_by_settlement_num '
3565: || 'FROM '
3566: || 'IBY_TRXN_SUMMARIES_ALL txn, '
3567: || l_user_pf_table_name || ' user_prof, '
3568: || l_sys_pf_table_name || ' sys_prof '
3569: || 'WHERE '
3570: || 'user_prof.'||l_user_pf_column_name||' = :profile_code AND '
4189: |
4190: *---------------------------------------------------------------------*/
4191: PROCEDURE performTransactionGrouping(
4192: profile_code_array IN JTF_VARCHAR2_TABLE_100,
4193: instr_type IN IBY_TRXN_SUMMARIES_ALL.
4194: instrtype%TYPE,
4195: req_type IN IBY_BATCHES_ALL.
4196: reqtype%TYPE,
4197: f_pmt_channel_in IN IBY_TRXN_SUMMARIES_ALL.
4193: instr_type IN IBY_TRXN_SUMMARIES_ALL.
4194: instrtype%TYPE,
4195: req_type IN IBY_BATCHES_ALL.
4196: reqtype%TYPE,
4197: f_pmt_channel_in IN IBY_TRXN_SUMMARIES_ALL.
4198: payment_channel_code%TYPE,
4199: f_curr_in IN IBY_TRXN_SUMMARIES_ALL.
4200: currencynamecode%TYPE,
4201: f_settle_date IN IBY_TRXN_SUMMARIES_ALL.
4195: req_type IN IBY_BATCHES_ALL.
4196: reqtype%TYPE,
4197: f_pmt_channel_in IN IBY_TRXN_SUMMARIES_ALL.
4198: payment_channel_code%TYPE,
4199: f_curr_in IN IBY_TRXN_SUMMARIES_ALL.
4200: currencynamecode%TYPE,
4201: f_settle_date IN IBY_TRXN_SUMMARIES_ALL.
4202: settledate%TYPE,
4203: f_due_date IN IBY_TRXN_SUMMARIES_ALL.
4197: f_pmt_channel_in IN IBY_TRXN_SUMMARIES_ALL.
4198: payment_channel_code%TYPE,
4199: f_curr_in IN IBY_TRXN_SUMMARIES_ALL.
4200: currencynamecode%TYPE,
4201: f_settle_date IN IBY_TRXN_SUMMARIES_ALL.
4202: settledate%TYPE,
4203: f_due_date IN IBY_TRXN_SUMMARIES_ALL.
4204: settlement_due_date%TYPE,
4205: f_maturity_date IN IBY_TRXN_SUMMARIES_ALL.
4199: f_curr_in IN IBY_TRXN_SUMMARIES_ALL.
4200: currencynamecode%TYPE,
4201: f_settle_date IN IBY_TRXN_SUMMARIES_ALL.
4202: settledate%TYPE,
4203: f_due_date IN IBY_TRXN_SUMMARIES_ALL.
4204: settlement_due_date%TYPE,
4205: f_maturity_date IN IBY_TRXN_SUMMARIES_ALL.
4206: br_maturity_date%TYPE,
4207: f_instr_type IN IBY_TRXN_SUMMARIES_ALL.
4201: f_settle_date IN IBY_TRXN_SUMMARIES_ALL.
4202: settledate%TYPE,
4203: f_due_date IN IBY_TRXN_SUMMARIES_ALL.
4204: settlement_due_date%TYPE,
4205: f_maturity_date IN IBY_TRXN_SUMMARIES_ALL.
4206: br_maturity_date%TYPE,
4207: f_instr_type IN IBY_TRXN_SUMMARIES_ALL.
4208: instrtype%TYPE,
4209: merch_batchid_in IN iby_batches_all.batchid%TYPE,
4203: f_due_date IN IBY_TRXN_SUMMARIES_ALL.
4204: settlement_due_date%TYPE,
4205: f_maturity_date IN IBY_TRXN_SUMMARIES_ALL.
4206: br_maturity_date%TYPE,
4207: f_instr_type IN IBY_TRXN_SUMMARIES_ALL.
4208: instrtype%TYPE,
4209: merch_batchid_in IN iby_batches_all.batchid%TYPE,
4210:
4211: x_batchTab IN OUT NOCOPY IBY_TRANSACTIONCC_PKG.
4273: *
4274: * A separate data structure is needed to keep track of the transactions
4275: * that are part of a batch. This information is tracked in the
4276: * trxnsInBatchTabType table. The rows in trxnsInBatchTabType are
4277: * used to update the rows in IBY_TRXN_SUMMARIES_ALL table with
4278: * batch ids.
4279: *
4280: * l_batchTab l_trxnsInBatchTab
4281: * (insert into IBY_BATCHES_ALL) (update IBY_TRXN_SUMMARIES_ALL)
4277: * used to update the rows in IBY_TRXN_SUMMARIES_ALL table with
4278: * batch ids.
4279: *
4280: * l_batchTab l_trxnsInBatchTab
4281: * (insert into IBY_BATCHES_ALL) (update IBY_TRXN_SUMMARIES_ALL)
4282: * /-------------------------------------\ /------------\
4283: * |MBatch |Profile|..|Curr |Org |..| |MBatch |Trx |
4284: * |Id |Code |..|Code |Id |..| |Id |Id |
4285: * | | |..| | |..| | | |
4312: l_pinlessdebitcard CONSTANT VARCHAR2(100) :='PINLESSDEBITCARD';
4313: l_bankaccount CONSTANT VARCHAR2(100) :='BANKACCOUNT';
4314:
4315: /* previous transaction attributes */
4316: prev_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
4317: prev_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
4318: prev_trxn_amount iby_trxn_summaries_all.amount%TYPE;
4319: prev_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
4320: prev_org_id iby_trxn_summaries_all.org_id%TYPE;
4313: l_bankaccount CONSTANT VARCHAR2(100) :='BANKACCOUNT';
4314:
4315: /* previous transaction attributes */
4316: prev_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
4317: prev_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
4318: prev_trxn_amount iby_trxn_summaries_all.amount%TYPE;
4319: prev_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
4320: prev_org_id iby_trxn_summaries_all.org_id%TYPE;
4321: prev_org_type iby_trxn_summaries_all.org_type%TYPE;
4314:
4315: /* previous transaction attributes */
4316: prev_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
4317: prev_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
4318: prev_trxn_amount iby_trxn_summaries_all.amount%TYPE;
4319: prev_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
4320: prev_org_id iby_trxn_summaries_all.org_id%TYPE;
4321: prev_org_type iby_trxn_summaries_all.org_type%TYPE;
4322: prev_settle_date iby_trxn_summaries_all.settledate%TYPE;
4315: /* previous transaction attributes */
4316: prev_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
4317: prev_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
4318: prev_trxn_amount iby_trxn_summaries_all.amount%TYPE;
4319: prev_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
4320: prev_org_id iby_trxn_summaries_all.org_id%TYPE;
4321: prev_org_type iby_trxn_summaries_all.org_type%TYPE;
4322: prev_settle_date iby_trxn_summaries_all.settledate%TYPE;
4323: prev_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
4316: prev_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
4317: prev_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
4318: prev_trxn_amount iby_trxn_summaries_all.amount%TYPE;
4319: prev_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
4320: prev_org_id iby_trxn_summaries_all.org_id%TYPE;
4321: prev_org_type iby_trxn_summaries_all.org_type%TYPE;
4322: prev_settle_date iby_trxn_summaries_all.settledate%TYPE;
4323: prev_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
4324: prev_bep_key iby_trxn_summaries_all.bepkey%TYPE;
4317: prev_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
4318: prev_trxn_amount iby_trxn_summaries_all.amount%TYPE;
4319: prev_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
4320: prev_org_id iby_trxn_summaries_all.org_id%TYPE;
4321: prev_org_type iby_trxn_summaries_all.org_type%TYPE;
4322: prev_settle_date iby_trxn_summaries_all.settledate%TYPE;
4323: prev_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
4324: prev_bep_key iby_trxn_summaries_all.bepkey%TYPE;
4325: prev_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
4318: prev_trxn_amount iby_trxn_summaries_all.amount%TYPE;
4319: prev_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
4320: prev_org_id iby_trxn_summaries_all.org_id%TYPE;
4321: prev_org_type iby_trxn_summaries_all.org_type%TYPE;
4322: prev_settle_date iby_trxn_summaries_all.settledate%TYPE;
4323: prev_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
4324: prev_bep_key iby_trxn_summaries_all.bepkey%TYPE;
4325: prev_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
4326:
4319: prev_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
4320: prev_org_id iby_trxn_summaries_all.org_id%TYPE;
4321: prev_org_type iby_trxn_summaries_all.org_type%TYPE;
4322: prev_settle_date iby_trxn_summaries_all.settledate%TYPE;
4323: prev_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
4324: prev_bep_key iby_trxn_summaries_all.bepkey%TYPE;
4325: prev_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
4326:
4327: /* current transaction attributes */
4320: prev_org_id iby_trxn_summaries_all.org_id%TYPE;
4321: prev_org_type iby_trxn_summaries_all.org_type%TYPE;
4322: prev_settle_date iby_trxn_summaries_all.settledate%TYPE;
4323: prev_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
4324: prev_bep_key iby_trxn_summaries_all.bepkey%TYPE;
4325: prev_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
4326:
4327: /* current transaction attributes */
4328: curr_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
4321: prev_org_type iby_trxn_summaries_all.org_type%TYPE;
4322: prev_settle_date iby_trxn_summaries_all.settledate%TYPE;
4323: prev_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
4324: prev_bep_key iby_trxn_summaries_all.bepkey%TYPE;
4325: prev_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
4326:
4327: /* current transaction attributes */
4328: curr_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
4329: curr_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
4324: prev_bep_key iby_trxn_summaries_all.bepkey%TYPE;
4325: prev_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
4326:
4327: /* current transaction attributes */
4328: curr_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
4329: curr_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
4330: curr_trxn_amount iby_trxn_summaries_all.amount%TYPE;
4331: curr_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
4332: curr_org_id iby_trxn_summaries_all.org_id%TYPE;
4325: prev_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
4326:
4327: /* current transaction attributes */
4328: curr_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
4329: curr_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
4330: curr_trxn_amount iby_trxn_summaries_all.amount%TYPE;
4331: curr_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
4332: curr_org_id iby_trxn_summaries_all.org_id%TYPE;
4333: curr_org_type iby_trxn_summaries_all.org_type%TYPE;
4326:
4327: /* current transaction attributes */
4328: curr_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
4329: curr_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
4330: curr_trxn_amount iby_trxn_summaries_all.amount%TYPE;
4331: curr_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
4332: curr_org_id iby_trxn_summaries_all.org_id%TYPE;
4333: curr_org_type iby_trxn_summaries_all.org_type%TYPE;
4334: curr_settle_date iby_trxn_summaries_all.settledate%TYPE;
4327: /* current transaction attributes */
4328: curr_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
4329: curr_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
4330: curr_trxn_amount iby_trxn_summaries_all.amount%TYPE;
4331: curr_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
4332: curr_org_id iby_trxn_summaries_all.org_id%TYPE;
4333: curr_org_type iby_trxn_summaries_all.org_type%TYPE;
4334: curr_settle_date iby_trxn_summaries_all.settledate%TYPE;
4335: curr_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
4328: curr_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
4329: curr_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
4330: curr_trxn_amount iby_trxn_summaries_all.amount%TYPE;
4331: curr_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
4332: curr_org_id iby_trxn_summaries_all.org_id%TYPE;
4333: curr_org_type iby_trxn_summaries_all.org_type%TYPE;
4334: curr_settle_date iby_trxn_summaries_all.settledate%TYPE;
4335: curr_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
4336: curr_bep_key iby_trxn_summaries_all.bepkey%TYPE;
4329: curr_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
4330: curr_trxn_amount iby_trxn_summaries_all.amount%TYPE;
4331: curr_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
4332: curr_org_id iby_trxn_summaries_all.org_id%TYPE;
4333: curr_org_type iby_trxn_summaries_all.org_type%TYPE;
4334: curr_settle_date iby_trxn_summaries_all.settledate%TYPE;
4335: curr_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
4336: curr_bep_key iby_trxn_summaries_all.bepkey%TYPE;
4337: curr_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
4330: curr_trxn_amount iby_trxn_summaries_all.amount%TYPE;
4331: curr_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
4332: curr_org_id iby_trxn_summaries_all.org_id%TYPE;
4333: curr_org_type iby_trxn_summaries_all.org_type%TYPE;
4334: curr_settle_date iby_trxn_summaries_all.settledate%TYPE;
4335: curr_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
4336: curr_bep_key iby_trxn_summaries_all.bepkey%TYPE;
4337: curr_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
4338: l_user_pf_table_name VARCHAR2(100);
4331: curr_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
4332: curr_org_id iby_trxn_summaries_all.org_id%TYPE;
4333: curr_org_type iby_trxn_summaries_all.org_type%TYPE;
4334: curr_settle_date iby_trxn_summaries_all.settledate%TYPE;
4335: curr_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
4336: curr_bep_key iby_trxn_summaries_all.bepkey%TYPE;
4337: curr_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
4338: l_user_pf_table_name VARCHAR2(100);
4339: l_sys_pf_table_name VARCHAR2(100);
4332: curr_org_id iby_trxn_summaries_all.org_id%TYPE;
4333: curr_org_type iby_trxn_summaries_all.org_type%TYPE;
4334: curr_settle_date iby_trxn_summaries_all.settledate%TYPE;
4335: curr_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
4336: curr_bep_key iby_trxn_summaries_all.bepkey%TYPE;
4337: curr_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
4338: l_user_pf_table_name VARCHAR2(100);
4339: l_sys_pf_table_name VARCHAR2(100);
4340: l_user_pf_column_name VARCHAR2(100);
4333: curr_org_type iby_trxn_summaries_all.org_type%TYPE;
4334: curr_settle_date iby_trxn_summaries_all.settledate%TYPE;
4335: curr_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
4336: curr_bep_key iby_trxn_summaries_all.bepkey%TYPE;
4337: curr_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
4338: l_user_pf_table_name VARCHAR2(100);
4339: l_sys_pf_table_name VARCHAR2(100);
4340: l_user_pf_column_name VARCHAR2(100);
4341: l_sys_pf_column_name VARCHAR2(100);
4393: sys_prof.limit_by_exch_rate_type,
4394: sys_prof.limit_by_total_amt,
4395: sys_prof.limit_by_settlement_num
4396: FROM
4397: IBY_TRXN_SUMMARIES_ALL txn,
4398: IBY_FNDCPT_USER_CC_PF_B user_prof,
4399: IBY_FNDCPT_SYS_CC_PF_B sys_prof
4400: WHERE
4401: user_prof.user_cc_profile_code IN (strProfiles) AND
4761: || 'sys_prof.limit_by_exch_rate_type, '
4762: || 'sys_prof.limit_by_total_amt, '
4763: || 'sys_prof.limit_by_settlement_num '
4764: || 'FROM '
4765: || 'IBY_TRXN_SUMMARIES_ALL txn, '
4766: || l_user_pf_table_name || ' user_prof, '
4767: || l_sys_pf_table_name || ' sys_prof '
4768: || 'WHERE '
4769: || 'user_prof.'||l_user_pf_column_name||' IN ('||strProfCodes||') AND '
5383:
5384: -- Bulk Update
5385: FORALL i in trxnTab.transactionid.FIRST .. trxnTab.transactionid.LAST
5386: /* Bug 9958732 : Used INDEX hint to force the index */
5387: UPDATE /*+ INDEX(IBY_TRXN_SUMMARIES_ALL IBY_TRXN_SUMM_TRANS_ID_N1) */
5388: IBY_TRXN_SUMMARIES_ALL
5389: SET
5390: status = iby_transactioncc_pkg.
5391: C_STATUS_BATCH_PENDING,
5384: -- Bulk Update
5385: FORALL i in trxnTab.transactionid.FIRST .. trxnTab.transactionid.LAST
5386: /* Bug 9958732 : Used INDEX hint to force the index */
5387: UPDATE /*+ INDEX(IBY_TRXN_SUMMARIES_ALL IBY_TRXN_SUMM_TRANS_ID_N1) */
5388: IBY_TRXN_SUMMARIES_ALL
5389: SET
5390: status = iby_transactioncc_pkg.
5391: C_STATUS_BATCH_PENDING,
5392: batchid = trxnTab.batchid(i),
5859: open c_getNumTrxns(trxn_type_in);
5860:
5861: FETCH c_getNumTrxns INTO l_numrows;
5862: /* IF (l_numrows > 0 AND l_needupdate) THEN
5863: UPDATE iby_trxn_summaries_all
5864: SET trxntypeid = trxn_type_in
5865: WHERE tangibleid = order_id_in
5866: AND payeeid = merchant_id_in
5867: AND bepid = vendor_id_in
5878:
5879: /* Inserts the transaction record for the closebatch operation */
5880:
5881: PROCEDURE insert_batch_txn
5882: (ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
5883:
5884: order_id_in IN iby_transactions_v.order_id%TYPE,
5885: merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
5886: merch_batchid_in IN iby_transactions_v.MerchBatchID%TYPE,
5893: vendor_message_in IN iby_transactions_v.vendor_message%TYPE,
5894: error_location_in IN iby_transactions_v.error_location%TYPE,
5895:
5896: trace_number_in IN iby_transactions_v.TraceNumber%TYPE,
5897: org_id_in IN iby_trxn_summaries_all.org_id%type,
5898: transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE)
5899:
5900: IS
5901:
5894: error_location_in IN iby_transactions_v.error_location%TYPE,
5895:
5896: trace_number_in IN iby_transactions_v.TraceNumber%TYPE,
5897: org_id_in IN iby_trxn_summaries_all.org_id%type,
5898: transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE)
5899:
5900: IS
5901:
5902: num_rows NUMBER;
5907: l_mbatchid iby_batches_all.mbatchid%type;
5908: l_mtangibleid iby_tangible.mtangibleid%type;
5909:
5910: l_prev_trxn_count number;
5911: l_reqtype iby_trxn_summaries_all.reqtype%type;
5912: l_instrtype iby_trxn_summaries_all.instrtype%type;
5913: l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
5914: BEGIN
5915:
5908: l_mtangibleid iby_tangible.mtangibleid%type;
5909:
5910: l_prev_trxn_count number;
5911: l_reqtype iby_trxn_summaries_all.reqtype%type;
5912: l_instrtype iby_trxn_summaries_all.instrtype%type;
5913: l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
5914: BEGIN
5915:
5916: -- Update the existing row for this order id with merchant batch id
5909:
5910: l_prev_trxn_count number;
5911: l_reqtype iby_trxn_summaries_all.reqtype%type;
5912: l_instrtype iby_trxn_summaries_all.instrtype%type;
5913: l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
5914: BEGIN
5915:
5916: -- Update the existing row for this order id with merchant batch id
5917: -- Only the transaction types auth,authcapture,return,capture,markcapture
5922: IF (trxn_type_in = 8 OR trxn_type_in = 9) THEN
5923: l_reqtype := 'ORAPMTCAPTURE';
5924: --dbms_output.put_line('position 1');
5925: getMBatchId(merch_batchid_in, merchant_id_in, l_mbatchid);
5926: UPDATE iby_trxn_summaries_all
5927: SET BatchID = merch_batchid_in,
5928: MBatchID = l_mbatchid,
5929: last_update_date=sysdate,
5930: updatedate = sysdate,
5943: l_reqtype := 'ORAPMTRETURN';
5944: --dbms_output.put_line('position 2');
5945: getMBatchId(merch_batchid_in, merchant_id_in, l_mbatchid);
5946:
5947: UPDATE iby_trxn_summaries_all
5948:
5949: SET BatchID = merch_batchid_in,
5950: MBatchID = l_mbatchid,
5951: last_update_date=sysdate,
5971: -- Now insert a new row for this transaction in the batch
5972: -- Get the transaction id first
5973: SELECT count(*)
5974: INTO l_prev_trxn_count
5975: FROM iby_trxn_summaries_all
5976: WHERE tangibleid = order_id_in AND
5977: payeeid = merchant_id_in;
5978:
5979: IF (l_prev_trxn_count = 0) THEN
5984: l_mtangibleid := -1;
5985: ELSE
5986: SELECT DISTINCT transactionid, mtangibleid, instrtype, instrsubtype
5987: INTO transaction_id_out, l_mtangibleid, l_instrtype, l_instrsubtype
5988: FROM iby_trxn_summaries_all
5989: WHERE tangibleid = order_id_in AND
5990: payeeid = merchant_id_in
5991: AND status = 0;
5992: END IF;
6016: -- (not through iPayment)
6017: -- 3) during testing, requests submitted to Cybercash regular
6018: -- and Cybercash SSL are considered different, but during close
6019: -- batch, they are mixed.
6020: INSERT INTO iby_trxn_summaries_all
6021: (TrxnMID, TransactionID,TangibleID,MPayeeID, PayeeID,BEPID, bepKey,
6022: ECAppID,org_id, Status, UpdateDate,TrxnTypeID, MBatchID, BatchID,
6023: BEPCode,BEPMessage,Errorlocation,
6024: ReqType, ReqDate, mtangibleid,
6038:
6039: -- retrieve existing tid out
6040: SELECT distinct(transactionid)
6041: INTO transaction_id_out
6042: FROM iby_trxn_summaries_all
6043: WHERE tangibleid = order_id_in
6044: AND payeeid = merchant_id_in
6045: AND bepid = vendor_id_in
6046: AND trxntypeid = trxn_type_in
6054:
6055: /* Inserts transaction record for transaction query operation */
6056:
6057: PROCEDURE insert_query_txn
6058: (transaction_id_in IN iby_trxn_summaries_all.TransactionID%TYPE,
6059: order_id_in IN iby_transactions_v.order_id%TYPE,
6060: merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
6061: vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
6062: vendor_key_in IN iby_transactions_v.bepkey%TYPE,
6074: vendor_code_in IN iby_transactions_v.vendor_code%TYPE DEFAULT NULL,
6075: vendor_message_in IN iby_transactions_v.vendor_message%TYPE DEFAULT NULL,
6076: error_location_in IN iby_transactions_v.error_location%TYPE DEFAULT NULL,
6077: trace_number_in IN iby_transactions_v.TraceNumber%TYPE DEFAULT NULL,
6078: org_id_in IN iby_trxn_summaries_all.org_id%type DEFAULT NULL,
6079: ecappid_in IN iby_ecapp.ecappid%type,
6080: req_type_in IN iby_trxn_summaries_all.reqtype%type)
6081: IS
6082:
6076: error_location_in IN iby_transactions_v.error_location%TYPE DEFAULT NULL,
6077: trace_number_in IN iby_transactions_v.TraceNumber%TYPE DEFAULT NULL,
6078: org_id_in IN iby_trxn_summaries_all.org_id%type DEFAULT NULL,
6079: ecappid_in IN iby_ecapp.ecappid%type,
6080: req_type_in IN iby_trxn_summaries_all.reqtype%type)
6081: IS
6082:
6083: num_rows NUMBER;
6084: l_trxn_mid NUMBER;
6082:
6083: num_rows NUMBER;
6084: l_trxn_mid NUMBER;
6085: l_mpayeeid iby_payee.mpayeeid%type;
6086: l_mtangibleid iby_trxn_summaries_all.mtangibleid%type;
6087: l_cnt number;
6088: l_instrtype iby_trxn_summaries_all.instrtype%type;
6089: l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
6090: l_trxnref iby_trxn_summaries_all.trxnref%type;
6084: l_trxn_mid NUMBER;
6085: l_mpayeeid iby_payee.mpayeeid%type;
6086: l_mtangibleid iby_trxn_summaries_all.mtangibleid%type;
6087: l_cnt number;
6088: l_instrtype iby_trxn_summaries_all.instrtype%type;
6089: l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
6090: l_trxnref iby_trxn_summaries_all.trxnref%type;
6091:
6092: CURSOR c_trxnmid(
6085: l_mpayeeid iby_payee.mpayeeid%type;
6086: l_mtangibleid iby_trxn_summaries_all.mtangibleid%type;
6087: l_cnt number;
6088: l_instrtype iby_trxn_summaries_all.instrtype%type;
6089: l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
6090: l_trxnref iby_trxn_summaries_all.trxnref%type;
6091:
6092: CURSOR c_trxnmid(
6093: ci_trxnid iby_trxn_summaries_all.TransactionID%TYPE,
6086: l_mtangibleid iby_trxn_summaries_all.mtangibleid%type;
6087: l_cnt number;
6088: l_instrtype iby_trxn_summaries_all.instrtype%type;
6089: l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
6090: l_trxnref iby_trxn_summaries_all.trxnref%type;
6091:
6092: CURSOR c_trxnmid(
6093: ci_trxnid iby_trxn_summaries_all.TransactionID%TYPE,
6094: ci_merchid iby_trxn_summaries_all.PayeeId%TYPE,
6089: l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
6090: l_trxnref iby_trxn_summaries_all.trxnref%type;
6091:
6092: CURSOR c_trxnmid(
6093: ci_trxnid iby_trxn_summaries_all.TransactionID%TYPE,
6094: ci_merchid iby_trxn_summaries_all.PayeeId%TYPE,
6095: --ci_trxntype iby_trxn_summaries_all.TrxnTypeId%TYPE,
6096: ci_reqtype iby_trxn_summaries_all.ReqType%TYPE,
6097: ci_status iby_trxn_summaries_all.Status%TYPE
6090: l_trxnref iby_trxn_summaries_all.trxnref%type;
6091:
6092: CURSOR c_trxnmid(
6093: ci_trxnid iby_trxn_summaries_all.TransactionID%TYPE,
6094: ci_merchid iby_trxn_summaries_all.PayeeId%TYPE,
6095: --ci_trxntype iby_trxn_summaries_all.TrxnTypeId%TYPE,
6096: ci_reqtype iby_trxn_summaries_all.ReqType%TYPE,
6097: ci_status iby_trxn_summaries_all.Status%TYPE
6098: )
6091:
6092: CURSOR c_trxnmid(
6093: ci_trxnid iby_trxn_summaries_all.TransactionID%TYPE,
6094: ci_merchid iby_trxn_summaries_all.PayeeId%TYPE,
6095: --ci_trxntype iby_trxn_summaries_all.TrxnTypeId%TYPE,
6096: ci_reqtype iby_trxn_summaries_all.ReqType%TYPE,
6097: ci_status iby_trxn_summaries_all.Status%TYPE
6098: )
6099: IS
6092: CURSOR c_trxnmid(
6093: ci_trxnid iby_trxn_summaries_all.TransactionID%TYPE,
6094: ci_merchid iby_trxn_summaries_all.PayeeId%TYPE,
6095: --ci_trxntype iby_trxn_summaries_all.TrxnTypeId%TYPE,
6096: ci_reqtype iby_trxn_summaries_all.ReqType%TYPE,
6097: ci_status iby_trxn_summaries_all.Status%TYPE
6098: )
6099: IS
6100: SELECT trxnmid
6093: ci_trxnid iby_trxn_summaries_all.TransactionID%TYPE,
6094: ci_merchid iby_trxn_summaries_all.PayeeId%TYPE,
6095: --ci_trxntype iby_trxn_summaries_all.TrxnTypeId%TYPE,
6096: ci_reqtype iby_trxn_summaries_all.ReqType%TYPE,
6097: ci_status iby_trxn_summaries_all.Status%TYPE
6098: )
6099: IS
6100: SELECT trxnmid
6101: FROM iby_trxn_summaries_all
6097: ci_status iby_trxn_summaries_all.Status%TYPE
6098: )
6099: IS
6100: SELECT trxnmid
6101: FROM iby_trxn_summaries_all
6102: WHERE (status = ci_status)
6103: AND (payeeid = ci_merchid)
6104: AND (transactionid = ci_trxnid)
6105: AND (reqtype = ci_reqtype);
6110: ci_merchid iby_transactions_v.merchant_id%TYPE
6111: )
6112: IS
6113: SELECT mtangibleid, instrtype, instrsubtype, trxnref
6114: FROM iby_trxn_summaries_all
6115: WHERE (tangibleid = ci_orderid)
6116: AND (payeeid = ci_merchid)
6117: AND (mtangibleid <> -1)
6118: ORDER BY reqdate DESC;
6139: -- exists ; update it instead of adding a new
6140: -- row
6141: CLOSE c_trxnmid;
6142:
6143: UPDATE iby_trxn_summaries_all
6144: SET ReqDate = NVL(time_in,reqdate),
6145: --Amount = amount_in,
6146: --CurrencyNameCode = currency_in,
6147: UpdateDate = NVL(time_in,updatedate),
6212: iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
6213:
6214:
6215: -- try to get mtangibleid
6216: select count(*) into l_cnt from iby_trxn_summaries_all
6217: where tangibleid = order_id_in and
6218: payeeid = merchant_id_in and
6219: mtangibleid <> -1;
6220:
6233: -- because it is missing the payee info
6234: l_mtangibleid := -1;
6235: end if;
6236:
6237: INSERT INTO iby_trxn_summaries_all
6238: (ECAppID, TrxnMID, TransactionID,TrxntypeID, ReqDate, ReqType,
6239: Amount,CurrencyNameCode, UpdateDate,Status,
6240: TangibleID,MPayeeID, PayeeID,BEPID, bepKey, MTangibleID,
6241: BEPCode,BEPMessage,Errorlocation, org_id,
6271:
6272: /* updates the statuses of trxns saved in a batch */
6273: PROCEDURE updateBatchedTrxns
6274: (
6275: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
6276: bepid_in IN iby_trxn_summaries_all.bepid%TYPE,
6277: bepkey_in IN iby_trxn_summaries_all.bepkey%TYPE,
6278: oldstatus_in IN iby_trxn_summaries_all.status%TYPE,
6279: newstatus_in IN iby_trxn_summaries_all.status%TYPE,
6272: /* updates the statuses of trxns saved in a batch */
6273: PROCEDURE updateBatchedTrxns
6274: (
6275: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
6276: bepid_in IN iby_trxn_summaries_all.bepid%TYPE,
6277: bepkey_in IN iby_trxn_summaries_all.bepkey%TYPE,
6278: oldstatus_in IN iby_trxn_summaries_all.status%TYPE,
6279: newstatus_in IN iby_trxn_summaries_all.status%TYPE,
6280: oldbatchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6273: PROCEDURE updateBatchedTrxns
6274: (
6275: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
6276: bepid_in IN iby_trxn_summaries_all.bepid%TYPE,
6277: bepkey_in IN iby_trxn_summaries_all.bepkey%TYPE,
6278: oldstatus_in IN iby_trxn_summaries_all.status%TYPE,
6279: newstatus_in IN iby_trxn_summaries_all.status%TYPE,
6280: oldbatchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6281: newbatchid_in IN iby_trxn_summaries_all.batchid%TYPE
6274: (
6275: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
6276: bepid_in IN iby_trxn_summaries_all.bepid%TYPE,
6277: bepkey_in IN iby_trxn_summaries_all.bepkey%TYPE,
6278: oldstatus_in IN iby_trxn_summaries_all.status%TYPE,
6279: newstatus_in IN iby_trxn_summaries_all.status%TYPE,
6280: oldbatchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6281: newbatchid_in IN iby_trxn_summaries_all.batchid%TYPE
6282: )
6275: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
6276: bepid_in IN iby_trxn_summaries_all.bepid%TYPE,
6277: bepkey_in IN iby_trxn_summaries_all.bepkey%TYPE,
6278: oldstatus_in IN iby_trxn_summaries_all.status%TYPE,
6279: newstatus_in IN iby_trxn_summaries_all.status%TYPE,
6280: oldbatchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6281: newbatchid_in IN iby_trxn_summaries_all.batchid%TYPE
6282: )
6283: IS
6276: bepid_in IN iby_trxn_summaries_all.bepid%TYPE,
6277: bepkey_in IN iby_trxn_summaries_all.bepkey%TYPE,
6278: oldstatus_in IN iby_trxn_summaries_all.status%TYPE,
6279: newstatus_in IN iby_trxn_summaries_all.status%TYPE,
6280: oldbatchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6281: newbatchid_in IN iby_trxn_summaries_all.batchid%TYPE
6282: )
6283: IS
6284: l_mbatchid iby_trxn_summaries_all.mbatchid%TYPE;
6277: bepkey_in IN iby_trxn_summaries_all.bepkey%TYPE,
6278: oldstatus_in IN iby_trxn_summaries_all.status%TYPE,
6279: newstatus_in IN iby_trxn_summaries_all.status%TYPE,
6280: oldbatchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6281: newbatchid_in IN iby_trxn_summaries_all.batchid%TYPE
6282: )
6283: IS
6284: l_mbatchid iby_trxn_summaries_all.mbatchid%TYPE;
6285: BEGIN
6280: oldbatchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6281: newbatchid_in IN iby_trxn_summaries_all.batchid%TYPE
6282: )
6283: IS
6284: l_mbatchid iby_trxn_summaries_all.mbatchid%TYPE;
6285: BEGIN
6286:
6287: BEGIN
6288: getMBatchId(oldbatchid_in, payeeid_in, l_mbatchid);
6294: EXCEPTION WHEN others THEN
6295: l_mbatchid := NULL;
6296: END;
6297:
6298: UPDATE iby_trxn_summaries_all
6299: SET
6300: status = newstatus_in,
6301: batchid = newbatchid_in,
6302: mbatchid = l_mbatchid,
6318:
6319: /* Regular version of this method. */
6320: PROCEDURE updateBatchQueryTrxn
6321: (
6322: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
6323: orderid_in IN iby_trxn_summaries_all.batchid%TYPE,
6324: trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
6325: batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6326: status_in IN iby_trxn_summaries_all.status%TYPE,
6319: /* Regular version of this method. */
6320: PROCEDURE updateBatchQueryTrxn
6321: (
6322: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
6323: orderid_in IN iby_trxn_summaries_all.batchid%TYPE,
6324: trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
6325: batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6326: status_in IN iby_trxn_summaries_all.status%TYPE,
6327: bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE,
6320: PROCEDURE updateBatchQueryTrxn
6321: (
6322: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
6323: orderid_in IN iby_trxn_summaries_all.batchid%TYPE,
6324: trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
6325: batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6326: status_in IN iby_trxn_summaries_all.status%TYPE,
6327: bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE,
6328: bep_msg_in IN iby_trxn_summaries_all.bepmessage%TYPE,
6321: (
6322: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
6323: orderid_in IN iby_trxn_summaries_all.batchid%TYPE,
6324: trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
6325: batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6326: status_in IN iby_trxn_summaries_all.status%TYPE,
6327: bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE,
6328: bep_msg_in IN iby_trxn_summaries_all.bepmessage%TYPE,
6329: error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
6322: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
6323: orderid_in IN iby_trxn_summaries_all.batchid%TYPE,
6324: trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
6325: batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6326: status_in IN iby_trxn_summaries_all.status%TYPE,
6327: bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE,
6328: bep_msg_in IN iby_trxn_summaries_all.bepmessage%TYPE,
6329: error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
6330: trxnid_out OUT NOCOPY iby_trxn_summaries_all.transactionid%TYPE
6323: orderid_in IN iby_trxn_summaries_all.batchid%TYPE,
6324: trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
6325: batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6326: status_in IN iby_trxn_summaries_all.status%TYPE,
6327: bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE,
6328: bep_msg_in IN iby_trxn_summaries_all.bepmessage%TYPE,
6329: error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
6330: trxnid_out OUT NOCOPY iby_trxn_summaries_all.transactionid%TYPE
6331: )
6324: trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
6325: batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6326: status_in IN iby_trxn_summaries_all.status%TYPE,
6327: bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE,
6328: bep_msg_in IN iby_trxn_summaries_all.bepmessage%TYPE,
6329: error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
6330: trxnid_out OUT NOCOPY iby_trxn_summaries_all.transactionid%TYPE
6331: )
6332: IS
6325: batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6326: status_in IN iby_trxn_summaries_all.status%TYPE,
6327: bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE,
6328: bep_msg_in IN iby_trxn_summaries_all.bepmessage%TYPE,
6329: error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
6330: trxnid_out OUT NOCOPY iby_trxn_summaries_all.transactionid%TYPE
6331: )
6332: IS
6333: BEGIN
6326: status_in IN iby_trxn_summaries_all.status%TYPE,
6327: bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE,
6328: bep_msg_in IN iby_trxn_summaries_all.bepmessage%TYPE,
6329: error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
6330: trxnid_out OUT NOCOPY iby_trxn_summaries_all.transactionid%TYPE
6331: )
6332: IS
6333: BEGIN
6334:
6332: IS
6333: BEGIN
6334:
6335: UPDATE
6336: iby_trxn_summaries_all
6337:
6338: SET
6339: status=status_in,
6340: --
6378:
6379: /* auth version of the method. */
6380: PROCEDURE updateBatchQueryTrxn
6381: (
6382: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
6383: orderid_in IN iby_trxn_summaries_all.batchid%TYPE,
6384: trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
6385: batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6386: status_in IN iby_trxn_summaries_all.status%TYPE,
6379: /* auth version of the method. */
6380: PROCEDURE updateBatchQueryTrxn
6381: (
6382: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
6383: orderid_in IN iby_trxn_summaries_all.batchid%TYPE,
6384: trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
6385: batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6386: status_in IN iby_trxn_summaries_all.status%TYPE,
6387: bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE,
6380: PROCEDURE updateBatchQueryTrxn
6381: (
6382: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
6383: orderid_in IN iby_trxn_summaries_all.batchid%TYPE,
6384: trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
6385: batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6386: status_in IN iby_trxn_summaries_all.status%TYPE,
6387: bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE,
6388: bep_msg_in IN iby_trxn_summaries_all.bepmessage%TYPE,
6381: (
6382: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
6383: orderid_in IN iby_trxn_summaries_all.batchid%TYPE,
6384: trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
6385: batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6386: status_in IN iby_trxn_summaries_all.status%TYPE,
6387: bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE,
6388: bep_msg_in IN iby_trxn_summaries_all.bepmessage%TYPE,
6389: error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
6382: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
6383: orderid_in IN iby_trxn_summaries_all.batchid%TYPE,
6384: trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
6385: batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6386: status_in IN iby_trxn_summaries_all.status%TYPE,
6387: bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE,
6388: bep_msg_in IN iby_trxn_summaries_all.bepmessage%TYPE,
6389: error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
6390: authcode_in IN iby_trxn_core.authcode%TYPE,
6383: orderid_in IN iby_trxn_summaries_all.batchid%TYPE,
6384: trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
6385: batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6386: status_in IN iby_trxn_summaries_all.status%TYPE,
6387: bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE,
6388: bep_msg_in IN iby_trxn_summaries_all.bepmessage%TYPE,
6389: error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
6390: authcode_in IN iby_trxn_core.authcode%TYPE,
6391: avscode_in IN iby_trxn_core.avscode%TYPE,
6384: trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
6385: batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6386: status_in IN iby_trxn_summaries_all.status%TYPE,
6387: bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE,
6388: bep_msg_in IN iby_trxn_summaries_all.bepmessage%TYPE,
6389: error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
6390: authcode_in IN iby_trxn_core.authcode%TYPE,
6391: avscode_in IN iby_trxn_core.avscode%TYPE,
6392: cvv2result_in IN iby_trxn_core.cvv2result%TYPE,
6385: batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6386: status_in IN iby_trxn_summaries_all.status%TYPE,
6387: bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE,
6388: bep_msg_in IN iby_trxn_summaries_all.bepmessage%TYPE,
6389: error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
6390: authcode_in IN iby_trxn_core.authcode%TYPE,
6391: avscode_in IN iby_trxn_core.avscode%TYPE,
6392: cvv2result_in IN iby_trxn_core.cvv2result%TYPE,
6393: trxnid_out OUT NOCOPY iby_trxn_summaries_all.transactionid%TYPE
6389: error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
6390: authcode_in IN iby_trxn_core.authcode%TYPE,
6391: avscode_in IN iby_trxn_core.avscode%TYPE,
6392: cvv2result_in IN iby_trxn_core.cvv2result%TYPE,
6393: trxnid_out OUT NOCOPY iby_trxn_summaries_all.transactionid%TYPE
6394: )
6395: IS
6396: l_trxnmid iby_trxn_summaries_all.trxnmid%TYPE;
6397: BEGIN
6392: cvv2result_in IN iby_trxn_core.cvv2result%TYPE,
6393: trxnid_out OUT NOCOPY iby_trxn_summaries_all.transactionid%TYPE
6394: )
6395: IS
6396: l_trxnmid iby_trxn_summaries_all.trxnmid%TYPE;
6397: BEGIN
6398:
6399: updateBatchQueryTrxn(payeeid_in,orderid_in,trxn_type_in,batchid_in,status_in,bep_code_in,bep_msg_in,error_loc_in,trxnid_out);
6400:
6402: trxnmid
6403: INTO
6404: l_trxnmid
6405: FROM
6406: iby_trxn_summaries_all
6407: WHERE
6408:
6409: --
6410: -- Where clause modified to support
6440:
6441: PROCEDURE Update_Batch
6442: (
6443: ecapp_id_in IN iby_batches_all.ecappid%TYPE,
6444: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
6445: batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6446: batch_status_in IN iby_batches_all.batchstatus%TYPE,
6447: batch_total_in IN iby_batches_all.batchtotal%TYPE,
6448: sale_amount_in IN iby_batches_all.batchsales%TYPE,
6441: PROCEDURE Update_Batch
6442: (
6443: ecapp_id_in IN iby_batches_all.ecappid%TYPE,
6444: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
6445: batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
6446: batch_status_in IN iby_batches_all.batchstatus%TYPE,
6447: batch_total_in IN iby_batches_all.batchtotal%TYPE,
6448: sale_amount_in IN iby_batches_all.batchsales%TYPE,
6449: credit_amount_in IN iby_batches_all.batchcredit%TYPE,
6462: trxn_cvv2result_in IN JTF_VARCHAR2_TABLE_100,
6463: trxn_tracenumber IN JTF_VARCHAR2_TABLE_100
6464: )
6465: IS
6466: l_tmid iby_trxn_summaries_all.trxnmid%TYPE;
6467: l_tid iby_trxn_summaries_all.transactionid%TYPE;
6468: l_needsupdt iby_trxn_summaries_all.needsupdt%TYPE;
6469: l_count NUMBER := 0;
6470: l_module VARCHAR2(100) := G_DEBUG_MODULE || '.Update_Batch';
6463: trxn_tracenumber IN JTF_VARCHAR2_TABLE_100
6464: )
6465: IS
6466: l_tmid iby_trxn_summaries_all.trxnmid%TYPE;
6467: l_tid iby_trxn_summaries_all.transactionid%TYPE;
6468: l_needsupdt iby_trxn_summaries_all.needsupdt%TYPE;
6469: l_count NUMBER := 0;
6470: l_module VARCHAR2(100) := G_DEBUG_MODULE || '.Update_Batch';
6471:
6464: )
6465: IS
6466: l_tmid iby_trxn_summaries_all.trxnmid%TYPE;
6467: l_tid iby_trxn_summaries_all.transactionid%TYPE;
6468: l_needsupdt iby_trxn_summaries_all.needsupdt%TYPE;
6469: l_count NUMBER := 0;
6470: l_module VARCHAR2(100) := G_DEBUG_MODULE || '.Update_Batch';
6471:
6472: CURSOR c_tmid
6469: l_count NUMBER := 0;
6470: l_module VARCHAR2(100) := G_DEBUG_MODULE || '.Update_Batch';
6471:
6472: CURSOR c_tmid
6473: (ci_payeeid iby_trxn_summaries_all.payeeid%TYPE,
6474: ci_orderid iby_trxn_summaries_all.tangibleid%TYPE,
6475: ci_reqtype iby_trxn_summaries_all.reqtype%TYPE,
6476: ci_mbatchid iby_trxn_summaries_all.mbatchid%TYPE
6477: )
6470: l_module VARCHAR2(100) := G_DEBUG_MODULE || '.Update_Batch';
6471:
6472: CURSOR c_tmid
6473: (ci_payeeid iby_trxn_summaries_all.payeeid%TYPE,
6474: ci_orderid iby_trxn_summaries_all.tangibleid%TYPE,
6475: ci_reqtype iby_trxn_summaries_all.reqtype%TYPE,
6476: ci_mbatchid iby_trxn_summaries_all.mbatchid%TYPE
6477: )
6478: IS
6471:
6472: CURSOR c_tmid
6473: (ci_payeeid iby_trxn_summaries_all.payeeid%TYPE,
6474: ci_orderid iby_trxn_summaries_all.tangibleid%TYPE,
6475: ci_reqtype iby_trxn_summaries_all.reqtype%TYPE,
6476: ci_mbatchid iby_trxn_summaries_all.mbatchid%TYPE
6477: )
6478: IS
6479: SELECT trxnmid, transactionid,needsupdt
6472: CURSOR c_tmid
6473: (ci_payeeid iby_trxn_summaries_all.payeeid%TYPE,
6474: ci_orderid iby_trxn_summaries_all.tangibleid%TYPE,
6475: ci_reqtype iby_trxn_summaries_all.reqtype%TYPE,
6476: ci_mbatchid iby_trxn_summaries_all.mbatchid%TYPE
6477: )
6478: IS
6479: SELECT trxnmid, transactionid,needsupdt
6480: FROM iby_trxn_summaries_all
6476: ci_mbatchid iby_trxn_summaries_all.mbatchid%TYPE
6477: )
6478: IS
6479: SELECT trxnmid, transactionid,needsupdt
6480: FROM iby_trxn_summaries_all
6481: WHERE (payeeid = ci_payeeid)
6482: AND (tangibleid = ci_orderid)
6483: -- ack cannot distinguish between credits and returns
6484: AND (DECODE(reqtype, 'ORAPMTRETURN','ORAPMTCREDIT', reqtype) =
6503: -- Bug# 12385267
6504: -- Inluded trxn type for RETURN and CREDIT
6505: CURSOR c_tracenumber
6506: (ci_tracenumber iby_trxn_core.tracenumber%TYPE,
6507: ci_mbatchid iby_trxn_summaries_all.mbatchid%TYPE
6508: ) IS
6509: SELECT ibs.trxnmid, ibs.transactionid, ibs.needsupdt
6510: FROM iby_trxn_summaries_all ibs,
6511: iby_trxn_summaries_all orig_trxn,
6506: (ci_tracenumber iby_trxn_core.tracenumber%TYPE,
6507: ci_mbatchid iby_trxn_summaries_all.mbatchid%TYPE
6508: ) IS
6509: SELECT ibs.trxnmid, ibs.transactionid, ibs.needsupdt
6510: FROM iby_trxn_summaries_all ibs,
6511: iby_trxn_summaries_all orig_trxn,
6512: iby_trxn_core ibc
6513: WHERE ibs.transactionid = orig_trxn.transactionid
6514: AND ibs.trxntypeid IN(8, 5, 11)
6507: ci_mbatchid iby_trxn_summaries_all.mbatchid%TYPE
6508: ) IS
6509: SELECT ibs.trxnmid, ibs.transactionid, ibs.needsupdt
6510: FROM iby_trxn_summaries_all ibs,
6511: iby_trxn_summaries_all orig_trxn,
6512: iby_trxn_core ibc
6513: WHERE ibs.transactionid = orig_trxn.transactionid
6514: AND ibs.trxntypeid IN(8, 5, 11)
6515: AND orig_trxn.trxnmid=ibc.trxnmid
6586: IF (l_tmid IS NOT NULL) THEN
6587: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
6588: print_debuginfo(l_module,'l_tmid is not null. Updating iby_trxn tables.');
6589: END IF;
6590: UPDATE iby_trxn_summaries_all
6591: SET status = TO_NUMBER(trxn_status_in(i)),
6592: bepcode = trxn_bep_code_in(i),
6593: bepmessage = trxn_bep_msg_in(i),
6594: errorlocation = TO_NUMBER(trxn_error_loc_in(i)),
6635: IF ((ack_type_in = 'P') OR (ack_type_in = 'N')) THEN
6636: IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
6637: print_debuginfo(l_module,'Updating transaction status:');
6638: END IF;
6639: UPDATE iby_trxn_summaries_all
6640: SET
6641: status = DECODE(ack_type_in,
6642: 'P',C_STATUS_BEP_FAIL,
6643: 'N',C_STATUS_SUCCESS,
6694: for QueryTrxn where there will be no orgid inserted
6695: try to see if there is already some valid org id stored,
6696: if so, use the non-null one first
6697: */
6698: Function getOrgId(i_tid in iby_trxn_summaries_all.transactionid%type)
6699: return number
6700: IS
6701: l_org_id NUMBER := NULL;
6702: l_count NUMBER;
6701: l_org_id NUMBER := NULL;
6702: l_count NUMBER;
6703:
6704: cursor c_getNonNullOrgId(ci_tid
6705: iby_trxn_summaries_all.transactionid%type)
6706:
6707: is
6708: SELECT DISTINCT org_id
6709: FROM iby_trxn_summaries_all
6705: iby_trxn_summaries_all.transactionid%type)
6706:
6707: is
6708: SELECT DISTINCT org_id
6709: FROM iby_trxn_summaries_all
6710: WHERE transactionid = i_tid
6711: AND status <> -99 AND status <> 14 -- ignore cancelled trxns
6712: AND org_id IS NOT NULL;
6713:
6731: */
6732:
6733: Function getNumPendingTrxns(i_payeeid in iby_payee.payeeid%type,
6734: i_tangibleid in iby_tangible.tangibleid%type,
6735: i_reqtype in iby_trxn_summaries_all.reqtype%type)
6736: return number
6737:
6738: IS
6739:
6742: BEGIN
6743:
6744: SELECT count(*)
6745: INTO l_num_trxns
6746: FROM iby_trxn_summaries_all
6747: WHERE TangibleID = i_tangibleid
6748: AND UPPER(ReqType) = UPPER(i_reqtype)
6749: AND PayeeID = i_payeeid
6750: AND (status IN (11,9));
6769: cursor c_tid(ci_payeeid in iby_payee.payeeid%type,
6770: ci_tangibleid in iby_tangible.tangibleid%type)
6771: is
6772: SELECT distinct transactionid
6773: FROM iby_trxn_summaries_all
6774: WHERE tangibleid = ci_tangibleid
6775: AND payeeid = ci_payeeid;
6776:
6777: BEGIN
6805: cursor c_tid(ci_payeeid in iby_payee.payeeid%type,
6806: ci_tangibleid in iby_tangible.tangibleid%type)
6807: is
6808: SELECT distinct transactionid
6809: FROM iby_trxn_summaries_all
6810: WHERE tangibleid = ci_tangibleid
6811: AND payeeid = ci_payeeid;
6812:
6813: BEGIN
6839:
6840: END getTIDUniqueCheck;
6841:
6842: FUNCTION unencrypt_instr_num
6843: (p_instrnum IN iby_trxn_summaries_all.instrnumber%TYPE,
6844: p_payee_key IN iby_security_pkg.des3_key_type,
6845: p_payee_subkey_cipher IN iby_payee_subkeys.subkey_cipher_text%TYPE,
6846: p_sys_key IN RAW,
6847: p_sys_subkey_cipher IN iby_sys_security_subkeys.subkey_cipher_text%TYPE,
6850: p_card_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
6851: p_card_len IN iby_cc_issuer_ranges.card_number_length%TYPE,
6852: p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE
6853: )
6854: RETURN iby_trxn_summaries_all.instrnumber%TYPE
6855: IS
6856: BEGIN
6857: RETURN IBY_SECURITY_PKG.unencrypt_instr_num
6858: (p_instrnum,
6867: p_digit_check);
6868: END unencrypt_instr_num;
6869:
6870: PROCEDURE unencrypt_instr_num
6871: (trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
6872: master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
6873: instr_num_out OUT NOCOPY iby_trxn_summaries_all.instrnumber%TYPE
6874: )
6875: IS
6869:
6870: PROCEDURE unencrypt_instr_num
6871: (trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
6872: master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
6873: instr_num_out OUT NOCOPY iby_trxn_summaries_all.instrnumber%TYPE
6874: )
6875: IS
6876: BEGIN
6877: IBY_SECURITY_PKG.unencrypt_instr_num
6882:
6883:
6884: FUNCTION unencrypt_instr_num
6885: (
6886: trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
6887: master_key_in IN iby_security_pkg.DES3_KEY_TYPE
6888: )
6889: RETURN iby_trxn_summaries_all.instrnumber%TYPE
6890: IS
6885: (
6886: trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
6887: master_key_in IN iby_security_pkg.DES3_KEY_TYPE
6888: )
6889: RETURN iby_trxn_summaries_all.instrnumber%TYPE
6890: IS
6891: l_instrnum iby_trxn_summaries_all.instrnumber%TYPE;
6892: BEGIN
6893: unencrypt_instr_num(trxnmid_in,master_key_in,l_instrnum);
6887: master_key_in IN iby_security_pkg.DES3_KEY_TYPE
6888: )
6889: RETURN iby_trxn_summaries_all.instrnumber%TYPE
6890: IS
6891: l_instrnum iby_trxn_summaries_all.instrnumber%TYPE;
6892: BEGIN
6893: unencrypt_instr_num(trxnmid_in,master_key_in,l_instrnum);
6894: RETURN l_instrnum;
6895: END unencrypt_instr_num;
6895: END unencrypt_instr_num;
6896:
6897: FUNCTION unencrypt_instr_num_ui_wrp
6898: (
6899: trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
6900: master_key_in IN iby_security_pkg.DES3_KEY_TYPE
6901: )
6902: RETURN iby_trxn_summaries_all.instrnumber%TYPE
6903: IS
6898: (
6899: trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
6900: master_key_in IN iby_security_pkg.DES3_KEY_TYPE
6901: )
6902: RETURN iby_trxn_summaries_all.instrnumber%TYPE
6903: IS
6904: l_instrnum iby_trxn_summaries_all.instrnumber%TYPE;
6905: BEGIN
6906: l_instrnum := unencrypt_instr_num(trxnmid_in,master_key_in);
6900: master_key_in IN iby_security_pkg.DES3_KEY_TYPE
6901: )
6902: RETURN iby_trxn_summaries_all.instrnumber%TYPE
6903: IS
6904: l_instrnum iby_trxn_summaries_all.instrnumber%TYPE;
6905: BEGIN
6906: l_instrnum := unencrypt_instr_num(trxnmid_in,master_key_in);
6907: RETURN l_instrnum;
6908:
6941: SELECT /*+ rowid(tx) */
6942: tx.trxnmid,
6943: iby_utility_pvt.decode64(instrnumber) ccnum,
6944: tx.instrtype
6945: FROM iby_trxn_summaries_all tx
6946: WHERE
6947: (NOT instrnumber IS NULL)
6948: AND (DECODE(instrtype, 'PINLESSDEBITCARD','CREDITCARD',
6949: 'PURCHASECARD','CREDITCARD',
6958: -- The below cursor will fetch all un-encrypted rows from
6959: -- IBY_TRXN_CORE table. Though we could probably have merged this
6960: -- cursor with the earlier one, we maintain a separate one since
6961: -- there could be records in this table that are un-encrypted but the
6962: -- corresponding records in iby_trxn_summaries_all are encrypted.
6963: CURSOR c_trxn_core
6964: IS
6965: SELECT
6966: tx.trxnmid tmid,
6965: SELECT
6966: tx.trxnmid tmid,
6967: core.instr_expirydate expdate,
6968: core.instr_owner_name chname
6969: FROM iby_trxn_summaries_all tx,
6970: iby_trxn_core core
6971: WHERE
6972: (DECODE(tx.instrtype, 'PINLESSDEBITCARD','CREDITCARD',
6973: 'PURCHASECARD','CREDITCARD',
7015: l_trxnmid_tbl(l_index) := txn_rec.trxnmid;
7016:
7017: IF (l_index=1000) THEN
7018: FORALL i IN l_trxnmid_tbl.first..l_trxnmid_tbl.last
7019: UPDATE iby_trxn_summaries_all
7020: SET
7021: instrnumber = l_maskedcc_tbl(i),
7022: instrnum_hash = l_cchash_tbl(i),
7023: cc_issuer_range_id = l_rangeid_tbl(i),
7044: END LOOP;
7045:
7046: IF (l_trxnmid_tbl.COUNT>0) THEN
7047: FORALL i IN l_trxnmid_tbl.first..l_trxnmid_tbl.last
7048: UPDATE iby_trxn_summaries_all
7049: SET
7050: instrnumber = l_maskedcc_tbl(i),
7051: instrnum_hash = l_cchash_tbl(i),
7052: cc_issuer_range_id = l_rangeid_tbl(i),
7130: seg.segment_cipher_text, r.card_number_prefix,
7131: NVL(r.card_number_length,tx.instrnum_length),
7132: i.digit_check_flag) ccnum,
7133: instrnum_sec_segment_id
7134: FROM iby_trxn_summaries_all tx,
7135: iby_security_segments seg, iby_sys_security_subkeys k,
7136: iby_cc_issuer_ranges r, iby_creditcard_issuers_b i
7137: WHERE
7138: /*
7182: l_segmentid_tbl(l_index) := txn_rec.instrnum_sec_segment_id;
7183:
7184: IF (l_index=1000) THEN
7185: FORALL i IN l_trxnmid_tbl.first..l_trxnmid_tbl.last
7186: UPDATE iby_trxn_summaries_all
7187: SET
7188: instrnumber = l_instrnum_tbl(i),
7189: instrnum_sec_segment_id = NULL
7190: WHERE trxnmid=l_trxnmid_tbl(i);
7205: END LOOP;
7206:
7207: IF (l_trxnmid_tbl.COUNT>0) THEN
7208: FORALL i IN l_trxnmid_tbl.first..l_trxnmid_tbl.last
7209: UPDATE iby_trxn_summaries_all
7210: SET
7211: instrnumber = l_instrnum_tbl(i),
7212: instrnum_sec_segment_id = NULL
7213: WHERE trxnmid=l_trxnmid_tbl(i);
7262: END Decrypt_CC_Data;
7263:
7264: PROCEDURE check_batch_size
7265: (
7266: ecappid_in IN iby_trxn_summaries_all.ecappid%TYPE,
7267: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
7268: bepid_in IN iby_trxn_summaries_all.bepid%TYPE,
7269: bepkey_in IN iby_trxn_summaries_all.bepkey%TYPE,
7270: orgid_in IN iby_batches_all.org_id%TYPE,
7263:
7264: PROCEDURE check_batch_size
7265: (
7266: ecappid_in IN iby_trxn_summaries_all.ecappid%TYPE,
7267: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
7268: bepid_in IN iby_trxn_summaries_all.bepid%TYPE,
7269: bepkey_in IN iby_trxn_summaries_all.bepkey%TYPE,
7270: orgid_in IN iby_batches_all.org_id%TYPE,
7271: seckey_present_in IN VARCHAR2,
7264: PROCEDURE check_batch_size
7265: (
7266: ecappid_in IN iby_trxn_summaries_all.ecappid%TYPE,
7267: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
7268: bepid_in IN iby_trxn_summaries_all.bepid%TYPE,
7269: bepkey_in IN iby_trxn_summaries_all.bepkey%TYPE,
7270: orgid_in IN iby_batches_all.org_id%TYPE,
7271: seckey_present_in IN VARCHAR2,
7272: trxncount_out OUT NOCOPY NUMBER,
7265: (
7266: ecappid_in IN iby_trxn_summaries_all.ecappid%TYPE,
7267: payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
7268: bepid_in IN iby_trxn_summaries_all.bepid%TYPE,
7269: bepkey_in IN iby_trxn_summaries_all.bepkey%TYPE,
7270: orgid_in IN iby_batches_all.org_id%TYPE,
7271: seckey_present_in IN VARCHAR2,
7272: trxncount_out OUT NOCOPY NUMBER,
7273: batchid_out OUT NOCOPY iby_batches_all.batchid%TYPE
7278: l_mbatch_id iby_batches_all.mbatchid%TYPE;
7279:
7280: CURSOR c_trxn_count
7281: (
7282: ci_ecappid iby_trxn_summaries_all.ecappid%TYPE,
7283: ci_payeeid iby_trxn_summaries_all.payeeid%TYPE,
7284: ci_bepid iby_trxn_summaries_all.bepid%TYPE,
7285: ci_bepkey iby_trxn_summaries_all.bepkey%TYPE
7286: ) IS
7279:
7280: CURSOR c_trxn_count
7281: (
7282: ci_ecappid iby_trxn_summaries_all.ecappid%TYPE,
7283: ci_payeeid iby_trxn_summaries_all.payeeid%TYPE,
7284: ci_bepid iby_trxn_summaries_all.bepid%TYPE,
7285: ci_bepkey iby_trxn_summaries_all.bepkey%TYPE
7286: ) IS
7287: SELECT count(transactionid)
7280: CURSOR c_trxn_count
7281: (
7282: ci_ecappid iby_trxn_summaries_all.ecappid%TYPE,
7283: ci_payeeid iby_trxn_summaries_all.payeeid%TYPE,
7284: ci_bepid iby_trxn_summaries_all.bepid%TYPE,
7285: ci_bepkey iby_trxn_summaries_all.bepkey%TYPE
7286: ) IS
7287: SELECT count(transactionid)
7288: FROM iby_trxn_summaries_all
7281: (
7282: ci_ecappid iby_trxn_summaries_all.ecappid%TYPE,
7283: ci_payeeid iby_trxn_summaries_all.payeeid%TYPE,
7284: ci_bepid iby_trxn_summaries_all.bepid%TYPE,
7285: ci_bepkey iby_trxn_summaries_all.bepkey%TYPE
7286: ) IS
7287: SELECT count(transactionid)
7288: FROM iby_trxn_summaries_all
7289: WHERE (ci_bepid=bepid)
7284: ci_bepid iby_trxn_summaries_all.bepid%TYPE,
7285: ci_bepkey iby_trxn_summaries_all.bepkey%TYPE
7286: ) IS
7287: SELECT count(transactionid)
7288: FROM iby_trxn_summaries_all
7289: WHERE (ci_bepid=bepid)
7290: AND (ci_payeeid=payeeid)
7291: AND (ci_bepkey=bepkey)
7292: --
7301: AND (instrtype IN (iby_creditcard_pkg.C_INSTRTYPE_CCARD,
7302: iby_creditcard_pkg.C_INSTRTYPE_PCARD)
7303: OR instrtype IS NULL);
7304:
7305: CURSOR c_max_bsize(ci_bepid iby_trxn_summaries_all.bepid%TYPE) IS
7306: SELECT NVL(max_batch_size,-1)
7307: FROM iby_bepinfo
7308: WHERE (ci_bepid=bepid);
7309:
7345: -- threads; lock both tables (even though iby_batches_all
7346: -- is sufficient) so as to ensure no deadlock can happen
7347: -- later
7348: --
7349: --LOCK TABLE iby_batches_all, iby_trxn_summaries_all
7350: --IN EXCLUSIVE MODE;
7351:
7352: -- check batch size once more to ensure another thread has
7353: -- not closed it between the last check and possession of
7514: WHERE (mbatchid = mbatchid_in);
7515:
7516: -- the update will update only settlement trxn.
7517: -- authcapture, capture and markcapture
7518: UPDATE iby_trxn_summaries_all
7519: SET payer_notification_required = l_payer_notif_flag,
7520: last_update_date=sysdate,
7521: last_updated_by = fnd_global.user_id,
7522: object_version_number = object_version_number + 1