[Home] [Help]
PACKAGE BODY: APPS.IBY_TRANSACTIONCC_PKG
Source
1 PACKAGE BODY iby_transactioncc_pkg AS
2 /*$Header: ibytxccb.pls 120.59.12010000.14 2009/01/19 13:05:00 lmallick ship $*/
3
4 --
5 -- Declare global variables
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_TRANSACTIONCC_PKG';
8
9 --
10 -- USE: Validates the current open batch for the given (payee,bep,bep key)
11 -- combination. This involves making sure: 1) the batch contains
12 -- at least one trxn, 2) a payee security key is present if the
13 -- batch contains encrypted trxns, 3) the batch contains trxns of
14 -- only a single currency
15 --
16 PROCEDURE validate_open_batch
17 (
18 p_bep_id IN iby_trxn_summaries_all.bepid%TYPE,
19 p_mbatch_id IN iby_batches_all.mbatchid%TYPE,
20 p_sec_key_on IN VARCHAR2,
21 x_trxn_count OUT NOCOPY iby_batches_all.numtrxns%TYPE,
22 x_batch_currency OUT NOCOPY iby_batches_all.currencynamecode%TYPE
23 )
24 IS
25 l_sec_trxn_count NUMBER;
26 l_batch_currency iby_trxn_summaries_all.currencynamecode%TYPE;
27
28 l_call_string VARCHAR2(1000);
29 l_call_params JTF_VARCHAR2_TABLE_200 := JTF_VARCHAR2_TABLE_200();
30 l_return_status VARCHAR2(10);
31 l_msg_count NUMBER;
32 l_msg_data VARCHAR2(5000);
33
34 CURSOR c_currencycodes(ci_mbatch_id IN iby_batches_all.mbatchid%TYPE)
35 IS
36 SELECT ts.currencynamecode
37 FROM iby_batches_all ba, iby_trxn_summaries_all ts
38 WHERE (ba.mbatchid = ci_mbatch_id)
39 AND (ba.payeeid = ts.payeeid)
40 AND (ba.batchid = ts.batchid)
41 GROUP BY ts.currencynamecode;
42
43 CURSOR c_valsets(ci_bep_id iby_trxn_summaries_all.bepid%TYPE)
44 IS
45 SELECT validation_code_package, validation_code_entry_point
46 FROM iby_validation_sets_b vs, iby_fndcpt_sys_cc_pf_b pf,
47 iby_val_assignments va
48 WHERE (vs.validation_code_language = 'PLSQL')
49 AND (vs.validation_level_code = 'INSTRUCTION' )
50 AND (pf.payment_system_id = ci_bep_id)
51 AND (pf.settlement_format_code = va.assignment_entity_id)
52 AND (va.val_assignment_entity_type = 'FORMAT')
53 AND (va.validation_set_code = vs.validation_set_code)
54 AND (NVL(va.inactive_date,SYSDATE-100) < SYSDATE);
55
56 BEGIN
57
58 IF (c_currencycodes%ISOPEN) THEN
59 CLOSE c_currencycodes;
60 END IF;
61
62 --
63 -- first check if any encrypted trxns exist in the batch;
64 -- if so, then the security key must be present for the batch
65 -- close to continue
66 --
67 SELECT COUNT(transactionid)
68 INTO l_sec_trxn_count
69 FROM iby_batches_all ba, iby_trxn_summaries_all ts
70 WHERE (ba.mbatchid = p_mbatch_id)
71 AND (ba.payeeid = ts.payeeid)
72 AND (ba.batchid = ts.batchid)
73 AND (NOT sub_key_id IS NULL);
74
75 IF ( (l_sec_trxn_count>0) AND
76 (p_sec_key_on<>iby_utility_pvt.C_API_YES) ) THEN
77 raise_application_error(-20000,'IBY_10002',FALSE);
78 END IF;
79
80 print_debuginfo('validate_ob', 'p_mbatch_id = ' || p_mbatch_id);
81
82 SELECT COUNT(transactionid)
83 INTO x_trxn_count
84 FROM iby_batches_all ba, iby_trxn_summaries_all ts
85 WHERE (ba.mbatchid = p_mbatch_id)
86 AND (ba.payeeid = ts.payeeid)
87 AND (ba.batchid = ts.batchid);
88 --
89 -- batch cannot be empty
90 --
91 IF (x_trxn_count<1) THEN
92 raise_application_error(-20000,'IBY_50314',FALSE);
93 END IF;
94 /* Multiple currencies may be allowed in the same batch*/
95 -- OPEN c_currencycodes(p_mbatch_id);
96
97 -- FETCH c_currencycodes INTO x_batch_currency;
98 -- FETCH c_currencycodes INTO l_batch_currency;
99 --
100 -- 2nd successful fetch indicates multiple currencies are in the batch
101 --
102 -- IF (NOT c_currencycodes%NOTFOUND) THEN
103 -- CLOSE c_currencycodes;
104 -- raise_application_error(-20000,'IBY_20213',FALSE);
105 -- ELSE
106 -- CLOSE c_currencycodes;
107 -- END IF;
108
109 -- perform payment format specific validations
110 --
111 l_call_params.extend(6);
112 l_call_params(1) := '1';
113 l_call_params(2) := '''' || FND_API.G_TRUE || '''';
114 l_call_params(3) := TO_CHAR(p_mbatch_id);
115 l_call_params(4) := '';
116 l_call_params(5) := '';
117 l_call_params(6) := '';
118
119 FOR cp IN c_valsets(p_bep_id) LOOP
120 l_call_string :=
121 iby_utility_pvt.get_call_exec(cp.validation_code_package,
122 cp.validation_code_entry_point,
123 l_call_params);
124 EXECUTE IMMEDIATE l_call_string USING
125 OUT l_return_status,
126 OUT l_msg_count,
127 OUT l_msg_data;
128
129 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
130 raise_application_error(-20000,
131 'IBY_20220#ERRMSG=' || fnd_msg_pub.get(p_msg_index => 1,p_encoded => FND_API.G_FALSE),
132 FALSE);
133 END IF;
134 END LOOP;
135
136 END validate_open_batch;
137
138 PROCEDURE prepare_instr_data
139 (p_commit IN VARCHAR2,
140 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE,
141 p_instrnum IN iby_trxn_summaries_all.instrnumber%TYPE,
142 p_instrtype IN iby_trxn_summaries_all.instrtype%TYPE,
143 x_instrnum OUT NOCOPY iby_trxn_summaries_all.instrnumber%TYPE,
144 x_instr_subtype OUT NOCOPY iby_trxn_summaries_all.instrsubtype%TYPE,
145 x_instr_hash OUT NOCOPY iby_trxn_summaries_all.instrnum_hash%TYPE,
146 x_range_id OUT NOCOPY iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE,
147 x_instr_len OUT NOCOPY iby_trxn_summaries_all.instrnum_length%TYPE,
148 x_segment_id OUT NOCOPY iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE
149 )
150 IS
151 lx_instrnum iby_trxn_summaries_all.instrnumber%TYPE;
152 lx_unmask_digits iby_trxn_summaries_all.instrnumber%TYPE;
153 lx_cc_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE;
154 lx_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE;
155
156 l_segment_cipher iby_security_segments.segment_cipher_text%TYPE;
157 lx_subkey_id iby_sys_security_subkeys.sec_subkey_id%TYPE;
158 lx_subkey iby_sys_security_subkeys.subkey_cipher_text%TYPE;
159 BEGIN
160 IF (p_instrnum IS NULL) THEN RETURN; END IF;
161
162 x_instrnum := iby_utility_pvt.encode64(p_instrnum);
163
164 IF (NOT p_instrtype IN ('CREDITCARD','PURCHASECARD','PINLESSDEBITCARD'))
165 THEN
166 RETURN;
167 END IF;
168
169 iby_cc_validate.Get_CC_Issuer_Range
170 (p_instrnum,x_instr_subtype,x_range_id,lx_cc_prefix,lx_digit_check);
171 x_instr_hash := iby_security_pkg.get_hash(p_instrnum,FND_API.G_FALSE);
172
173 IF (x_range_id IS NULL) THEN
174 x_instr_len := LENGTH(p_instrnum);
175 x_instr_subtype := 'UNKNOWN';
176 END IF;
177
178 IF (IBY_CREDITCARD_PKG.Get_CC_Encrypt_Mode() <>
179 IBY_SECURITY_PKG.G_ENCRYPT_MODE_NONE)
180 THEN
181 -- mask the instrument number
182 x_instrnum := LPAD('X',LENGTH(p_instrnum),'X');
183 -- assuming the number has already been validate- i.e. check digit
184 -- verified
185 iby_creditcard_pkg.Compress_CC_Number
186 (p_instrnum,lx_cc_prefix,lx_digit_check,IBY_SECURITY_PKG.G_MASK_ALL,0,
187 lx_instrnum,lx_unmask_digits);
188
189 IF (LENGTH(lx_instrnum) > 0) THEN
190 l_segment_cipher :=
191 HEXTORAW(IBY_SECURITY_PKG.Encode_Number(lx_instrnum,TRUE));
192 IBY_SECURITY_PKG.Get_Sys_Subkey
193 (FND_API.G_FALSE,p_sys_key,'Y',lx_subkey_id,lx_subkey);
194
195 l_segment_cipher :=
196 DBMS_OBFUSCATION_TOOLKIT.des3encrypt
197 ( input => l_segment_cipher, key => lx_subkey,
198 which => dbms_obfuscation_toolkit.ThreeKeyMode
199 );
200
201 SELECT iby_security_segments_s.NEXTVAL
202 INTO x_segment_id
203 FROM DUAL;
204
205 INSERT INTO iby_security_segments
206 (sec_segment_id, segment_cipher_text, sec_subkey_id, encoding_scheme,
207 created_by, creation_date, last_updated_by, last_update_date,
208 last_update_login, object_version_number
209 )
210 VALUES
211 (x_segment_id, l_segment_cipher, lx_subkey_id, 'NUMERIC',
212 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate,
213 fnd_global.login_id, 1
214 );
215 ELSE
216 -- indicative of PCI encryption
217 x_segment_id := -1;
218 END IF;
219 END IF;
220
221 IF FND_API.to_Boolean(p_commit) THEN
222 COMMIT;
223 END IF;
224 END prepare_instr_data;
225
226 --
227 -- USE: inserts transactional extensibility data
228 --
229 PROCEDURE insert_extensibility
230 (
231 p_trxnmid IN iby_trxn_summaries_all.trxnmid%TYPE,
232 p_commit IN VARCHAR2,
233 p_extend_names IN JTF_VARCHAR2_TABLE_100,
234 p_extend_vals IN JTF_VARCHAR2_TABLE_200
235 )
236 IS
237 BEGIN
238
239 IF (p_extend_names IS NULL) THEN
240 RETURN;
241 END IF;
242
243
244 FOR i IN p_extend_names.FIRST..p_extend_names.LAST LOOP
245 INSERT INTO iby_trxn_extensibility
246 (trxn_extend_id,trxnmid,extend_name,extend_value,created_by,
247 creation_date,last_updated_by,last_update_date,last_update_login,
248 object_version_number)
249 VALUES
250 (iby_trxn_extensibility_s.NEXTVAL,
251 p_trxnmid,p_extend_names(i),p_extend_vals(i),
252 fnd_global.user_id,sysdate,fnd_global.user_id,sysdate,
253 fnd_global.login_id,1);
254 END LOOP;
255
256 IF (p_commit = 'Y') THEN
257 COMMIT;
258 END IF;
259 END insert_extensibility;
260
261 /* Inserts a new row into the IBY_TRXN_SUMMARIES_ALL table. This method */
262 /* would be called every time a MIPP authorize operation is performed. */
263
264 PROCEDURE insert_auth_txn
265 (
266 ecapp_id_in IN iby_trxn_summaries_all.ecappid%TYPE,
267 req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
268 order_id_in IN iby_transactions_v.order_id%TYPE,
269 merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
270 vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
271 vendor_key_in IN iby_transactions_v.bepkey%TYPE,
272 amount_in IN iby_transactions_v.amount%TYPE,
273 currency_in IN iby_transactions_v.currency%TYPE,
274 status_in IN iby_transactions_v.status%TYPE,
275 time_in IN iby_transactions_v.time%TYPE,
276 payment_name_in IN iby_transactions_v.payment_name%TYPE,
277 payment_type_in IN iby_transactions_v.payment_type%TYPE,
278 trxn_type_in IN iby_transactions_v.trxn_type%TYPE,
279 authcode_in IN iby_transactions_v.authcode%TYPE,
280 referencecode_in IN iby_transactions_v.referencecode%TYPE,
281 AVScode_in IN iby_transactions_v.AVScode%TYPE,
282 acquirer_in IN iby_transactions_v.acquirer%TYPE,
283 Auxmsg_in IN iby_transactions_v.Auxmsg%TYPE,
284 vendor_code_in IN iby_transactions_v.vendor_code%TYPE,
285 vendor_message_in IN iby_transactions_v.vendor_message%TYPE,
286 error_location_in IN iby_transactions_v.error_location%TYPE,
287 trace_number_in IN iby_transactions_v.TraceNumber%TYPE,
288 org_id_in IN iby_trxn_summaries_all.org_id%type,
289 billeracct_in IN iby_tangible.acctno%type,
290 refinfo_in IN iby_tangible.refinfo%type,
291 memo_in IN iby_tangible.memo%type,
292 order_medium_in IN iby_tangible.order_medium%TYPE,
293 eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
294 payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
295 instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
296 payerid_in IN iby_trxn_summaries_all.payerid%type,
297 instrtype_in IN iby_trxn_summaries_all.instrType%type,
298 cvv2result_in IN iby_trxn_core.CVV2Result%type,
299 master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
300 subkey_seed_in IN RAW,
301 trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
302 dateofvoiceauth_in IN iby_trxn_core.date_of_voice_authorization%TYPE,
303 instr_expirydate_in IN iby_trxn_core.instr_expirydate%TYPE,
304 instr_sec_val_in IN VARCHAR2,
305 card_subtype_in IN iby_trxn_core.card_subtype_code%TYPE,
306 card_data_level_in IN iby_trxn_core.card_data_level%TYPE,
307 instr_owner_name_in IN iby_trxn_core.instr_owner_name%TYPE,
308 instr_address_line1_in IN iby_trxn_core.instr_owner_address_line1%TYPE,
309 instr_address_line2_in IN iby_trxn_core.instr_owner_address_line2%TYPE,
310 instr_address_line3_in IN iby_trxn_core.instr_owner_address_line3%TYPE,
311 instr_city_in IN iby_trxn_core.instr_owner_city%TYPE,
312 instr_state_in IN iby_trxn_core.instr_owner_state_province%TYPE,
313 instr_country_in IN iby_trxn_core.instr_owner_country%TYPE,
314 instr_postalcode_in IN iby_trxn_core.instr_owner_postalcode%TYPE,
315 instr_phonenumber_in IN iby_trxn_core.instr_owner_phone%TYPE,
316 instr_email_in IN iby_trxn_core.instr_owner_email%TYPE,
317 pos_reader_cap_in IN iby_trxn_core.pos_reader_capability_code%TYPE,
318 pos_entry_method_in IN iby_trxn_core.pos_entry_method_code%TYPE,
319 pos_card_id_method_in IN iby_trxn_core.pos_id_method_code%TYPE,
320 pos_auth_source_in IN iby_trxn_core.pos_auth_source_code%TYPE,
321 reader_data_in IN iby_trxn_core.reader_data%TYPE,
322 extend_names_in IN JTF_VARCHAR2_TABLE_100,
323 extend_vals_in IN JTF_VARCHAR2_TABLE_200,
324 debit_network_code_in IN iby_trxn_core.debit_network_code%TYPE,
325 surcharge_amount_in IN iby_trxn_core.surcharge_amount%TYPE,
326 proc_tracenumber_in IN iby_trxn_core.proc_tracenumber%TYPE,
327 transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
328 transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
329 org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
330 payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
331 factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
332 process_profile_code_in IN iby_trxn_summaries_all.process_profile_code%TYPE,
333 sub_key_id_in IN iby_trxn_summaries_all.sub_key_id%TYPE,
334 voiceAuthFlag_in IN iby_trxn_core.voiceauthflag%TYPE
335 )
336 IS
337
338 l_num_trxns NUMBER := 0;
339 l_trxn_mid NUMBER;
340 l_transaction_id NUMBER;
341 l_tmid iby_trxn_summaries_all.mtangibleid%type;
342 l_mpayeeid iby_payee.mpayeeid%type;
343
344 l_return_status VARCHAR2(1);
345 l_msg_count NUMBER;
346 l_msg_data VARCHAR2(200);
347 l_checksum_valid BOOLEAN := FALSE; -- whether the card number is valid.
348
349 l_cc_type VARCHAR2(80);
350 lx_cc_hash iby_trxn_summaries_all.instrnum_hash%TYPE;
351 lx_range_id iby_cc_issuer_ranges.cc_issuer_range_id%TYPE;
352 lx_instr_len iby_trxn_summaries_all.instrnum_length%TYPE;
353 lx_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
354 l_old_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
355
356 l_instrnum iby_trxn_summaries_all.instrnumber%type;
357 l_expirydate iby_trxn_core.instr_expirydate%type;
358
359 l_pos_txn iby_trxn_core.pos_trxn_flag%TYPE;
360 l_payer_party_id iby_trxn_summaries_all.payer_party_id%type;
361
362 l_voiceauth_flag iby_trxn_core.voiceauthflag%type;
363 l_sub_key_id iby_trxn_summaries_all.sub_key_id%TYPE;
364
365 -- variables for CHNAME and EXPDATE encryption
366 l_chname_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
367 l_expdate_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
368 l_masked_chname VARCHAR2(100) := NULL;
369 -- l_encrypted_date_format VARCHAR2(20);
370 l_encrypted VARCHAR2(1) := 'N';
371
372 BEGIN
373
374 l_num_trxns := getNumPendingTrxns(merchant_id_in,order_id_in,req_type_in);
375
376 prepare_instr_data
377 (FND_API.G_FALSE,master_key_in,instrnum_in,instrType_in,l_instrnum,
378 l_cc_type,lx_cc_hash,lx_range_id,lx_instr_len,lx_segment_id);
379
380
381 --
382 -- NOTE: for all subsequent data encryptions, make sure that the
383 -- parameter to increment the subkey is set to 'N' so that
384 -- all encrypted data for the trxn uses the same key!!
385 -- else data will NOT DECRYPT CORRECTLY!!
386 --
387 l_expirydate := instr_expirydate_in;
388
389
390
391 -- PABP Fixes
392 -- card holder name and instrument expiry are also considered to be
393 -- sensitive. We need to encrypt those before inserting/updating the
394 -- record in IBY_TRXN_CORE
395
396 IF ((IBY_CREDITCARD_PKG.Get_CC_Encrypt_Mode() <>
397 IBY_SECURITY_PKG.G_ENCRYPT_MODE_NONE)
398 AND ( IBY_CREDITCARD_PKG.Other_CC_Attribs_Encrypted = 'Y'))
399 THEN
400 l_chname_sec_segment_id :=
401 IBY_SECURITY_PKG.encrypt_field_vals(instr_owner_name_in,
402 master_key_in,
403 null,
404 'N'
405 );
406 l_expdate_sec_segment_id :=
407 IBY_SECURITY_PKG.encrypt_date_field(l_expirydate,
408 master_key_in,
409 null,
410 'N'
411 );
412
413 l_masked_chname :=
414 IBY_SECURITY_PKG.Mask_Data(instr_owner_name_in,
415 IBY_SECURITY_PKG.G_MASK_ALL,
416 0,
417 'X'
418 );
419 l_encrypted := 'Y';
420 l_expirydate := NULL;
421 ELSE
422 l_masked_chname := instr_owner_name_in;
423 l_encrypted := 'N';
424
425 END IF;
426
427 IF ((pos_reader_cap_in IS NULL)
428 AND (pos_entry_method_in IS NULL)
429 AND (pos_card_id_method_in IS NULL)
430 AND (pos_auth_source_in IS NULL)
431 AND (reader_data_in IS NULL)
432 )
433 THEN
434 l_pos_txn := 'N';
435 ELSE
436 l_pos_txn := 'Y';
437 END IF;
438
439 IF (l_num_trxns = 0) THEN
440 -- new auth request, insert into table
441 SELECT iby_trxnsumm_mid_s.NEXTVAL
442 INTO l_trxn_mid
443 FROM dual;
444
445 -- get the payer_party_id if exists
446 begin
447 if(payerid_in is not NULL) then
448 l_payer_party_id :=to_number(payerid_in);
449 end if;
450 exception
451 when others then
452 select card_owner_id
453 into l_payer_party_id
454 from iby_creditcard
455 where instrid=payerinstrid_in;
456 end;
457
458 l_transaction_id := getTID(merchant_id_in, order_id_in);
459
460 transaction_id_out := l_transaction_id;
461 transaction_mid_out := l_trxn_mid;
462
463 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
464
465 --Create an entry in iby_tangible table
466 iby_bill_pkg.createBill(order_id_in,amount_in,currency_in,
467 billeracct_in,refinfo_in, memo_in,
468 order_medium_in, eft_auth_method_in, l_tmid);
469 --test_debug('subkeyid passed as: '|| sub_key_id_in);
470 INSERT INTO iby_trxn_summaries_all
471 (TrxnMID, TransactionID,TrxntypeID, ReqType, ReqDate,
472 Amount,CurrencyNameCode, UpdateDate,Status, PaymentMethodName,
473 TangibleID,MPayeeID, PayeeID,BEPID,bepKey,mtangibleid,
474 BEPCode,BEPMessage,Errorlocation,ecappid,org_id,
475 payerinstrid, instrnumber, payerid, instrType,
476
477 last_update_date,last_updated_by,creation_date, created_by,
478 last_update_login,object_version_number,instrsubtype,trxnref,
479 org_type, payment_channel_code, factored_flag,
480 cc_issuer_range_id, instrnum_hash, instrnum_length,
481 instrnum_sec_segment_id, payer_party_id, process_profile_code,
482 salt_version,needsupdt,sub_key_id)
483 VALUES (l_trxn_mid, l_transaction_id, trxn_type_in, req_type_in,
484 sysdate,
485 amount_in, currency_in, time_in, status_in, payment_type_in,
486 order_id_in, l_mpayeeid, merchant_id_in, vendor_id_in,
487 vendor_key_in, l_tmid, vendor_code_in, vendor_message_in,
488 error_location_in, ecapp_id_in, org_id_in,
489 payerinstrid_in, l_instrnum, payerid_in, instrType_in,
490 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
491 fnd_global.login_id, 1, l_cc_type, trxnref_in,
492 org_type_in, payment_channel_code_in, factored_flag_in,
493 lx_range_id, lx_cc_hash, lx_instr_len, lx_segment_id,
494 l_payer_party_id, process_profile_code_in,
495 iby_security_pkg.get_salt_version,'Y',sub_key_id_in);
496
497
498 /*
499 * Fix for bug 5190504:
500 *
501 * Set the voice auth flag in iby_trxn_core to 'Y'
502 * in case, the voice auth date is not null.
503 */
504 -- IF (dateofvoiceauth_in IS NOT NULL) THEN
505 -- l_voiceauth_flag := 'Y';
506 -- ELSE
507 -- l_voiceauth_flag := 'N';
508 -- END IF;
509
510 /*
511 * The above logic will not set the voiceAuthFlag if the
512 * voice auth date is NULL.
513 * The voiceAuthFlag is now received by this API as an
514 * input parameter.
515 */
516 l_voiceauth_flag := voiceAuthFlag_in;
517
518 INSERT INTO iby_trxn_core (
519 TrxnMID, AuthCode, date_of_voice_authorization, voiceauthflag,
520 ReferenceCode, TraceNumber,AVSCode, CVV2Result, Acquirer,
521 Auxmsg, InstrName,
522 Instr_Expirydate, expiry_sec_segment_id,
523 Card_Subtype_Code, Card_Data_Level,
524 Instr_Owner_Name, chname_sec_segment_id, encrypted,
525 Instr_Owner_Address_Line1, Instr_Owner_Address_Line2,
526 Instr_Owner_Address_Line3, Instr_Owner_City, Instr_Owner_State_Province,
527 Instr_Owner_Country, Instr_Owner_PostalCode, Instr_Owner_Phone,
528 Instr_Owner_Email,
529 POS_Reader_Capability_Code, POS_Entry_Method_Code,
530 POS_Id_Method_Code, POS_Auth_Source_Code, Reader_Data, POS_Trxn_Flag,
531 debit_network_code, surcharge_amount, proc_tracenumber,
532 last_update_date, last_updated_by,
533 creation_date, created_by, last_update_login, object_version_number
534 ) VALUES (
535 l_trxn_mid, authcode_in, dateofvoiceauth_in, l_voiceauth_flag,
536 referencecode_in, trace_number_in, AVScode_in, cvv2result_in,
537 acquirer_in, Auxmsg_in, payment_name_in,
538 l_expirydate, l_expdate_sec_segment_id,
539 card_subtype_in, card_data_level_in,
540 l_masked_chname, l_chname_sec_segment_id, l_encrypted,
541 instr_address_line1_in, instr_address_line2_in, instr_address_line3_in,
542 instr_city_in, instr_state_in, instr_country_in, instr_postalcode_in,
543 instr_phonenumber_in, instr_email_in,
544 pos_reader_cap_in, pos_entry_method_in, pos_card_id_method_in,
545 pos_auth_source_in, reader_data_in, l_pos_txn,debit_network_code_in, surcharge_amount_in, proc_tracenumber_in,
546 sysdate,fnd_global.user_id,
547 sysdate,fnd_global.user_id,fnd_global.login_id,1
548 );
549
550 -- probably a superflous call since the first insert is
551 -- to log the transaction before it is sent to the payment system
552 insert_extensibility(l_trxn_mid,'N',extend_names_in,extend_vals_in);
553
554 --test_debug('insertion complete..');
555
556 ELSE
557 --(l_num_trxns = 1)
558 -- One previous PENDING transaction, so update previous row
559 SELECT TrxnMID, TransactionID, Mtangibleid, instrnum_sec_segment_id, sub_key_id
560 INTO l_trxn_mid, transaction_id_out, l_tmid, l_old_segment_id, l_sub_key_id
561 FROM iby_trxn_summaries_all
562 WHERE (TangibleID = order_id_in)
563 AND (UPPER(ReqType) = UPPER(req_type_in))
564 AND (PayeeID = merchant_id_in)
565 AND (status IN (11,9));
566
567 transaction_mid_out := l_trxn_mid;
568
569 --Re-use the previous subkey for a retry case
570 -- sub_key_id_in := l_sub_key_id;
571
572 -- Update iby_tangible table
573 iby_bill_pkg.modBill(l_tmid,order_id_in,amount_in,currency_in,
574 billeracct_in,refinfo_in,memo_in,
575 order_medium_in, eft_auth_method_in);
576
577
578 UPDATE iby_trxn_summaries_all
579 SET BEPID = vendor_id_in,
580 bepKey = vendor_key_in,
581 Amount = amount_in,
582 -- amount, bepid is updated as the request can come in
583 -- from another online
584 TrxntypeID = trxn_type_in,
585 CurrencyNameCode = currency_in,
586 UpdateDate = time_in,
587 Status = status_in,
588 ErrorLocation = error_location_in,
589 BEPCode = vendor_code_in,
590 BEPMessage = vendor_message_in,
591 instrType = instrType,
592
593 -- we don't update payerinstrid and org_id here
594 -- as it may overwrite previous payerinstrid, org_id
595 -- (from offline scheduling)
596 -- in case this request comes in from scheduler
597
598 -- could be a problem if this request comes in from
599 -- another online, w/ a different payment instrment
600 -- for a previous failed trxn, regardless, the
601 --'instrnumber' will always be correct
602
603 --org_id = org_id_in,
604 --payerinstrid = payerinstrid_in,
605 -- same for org_type
606
607 PaymentMethodName = NVL(payment_type_in,PaymentMethodName),
608 instrnumber = l_instrnum,
609 instrnum_hash = lx_cc_hash,
610 instrnum_length = lx_instr_len,
611 cc_issuer_range_id = lx_range_id,
612 instrnum_sec_segment_id = lx_segment_id,
613 trxnref = trxnref_in,
614 last_update_date = sysdate,
615 last_updated_by = fnd_global.user_id,
616 creation_date = sysdate,
617 created_by = fnd_global.user_id,
618 object_version_number = object_version_number + 1,
619 payment_channel_code = payment_channel_code_in,
620 factored_flag = factored_flag_in
621 WHERE TrxnMID = l_trxn_mid;
622
623 DELETE iby_security_segments WHERE sec_segment_id = l_old_segment_id;
624
625 UPDATE iby_trxn_core
626 SET AuthCode = authcode_in,
627 date_of_voice_authorization = dateofvoiceauth_in,
628 --voiceauthflag = DECODE(dateofvoiceauth_in, NULL, 'N', 'Y'),
629 voiceauthflag = voiceAuthFlag_in,
630 AvsCode = AVScode_in,
631 CVV2Result = cvv2result_in,
632 ReferenceCode = referencecode_in,
633 Acquirer = acquirer_in,
634 Auxmsg = Auxmsg_in,
635 TraceNumber = trace_number_in,
636 InstrName = NVL(payment_name_in,InstrName),
637 encrypted = l_encrypted,
638 Instr_Expirydate = l_expirydate,
639 expiry_sec_segment_id = l_expdate_sec_segment_id,
640 Card_Subtype_Code = card_subtype_in,
641 Card_Data_Level = card_data_level_in,
642 Instr_Owner_Name = l_masked_chname,
643 chname_sec_segment_id = l_chname_sec_segment_id,
644 Instr_Owner_Address_Line1 = instr_address_line1_in,
645 Instr_Owner_Address_Line2 = instr_address_line2_in,
646 Instr_Owner_Address_Line3 = instr_address_line3_in,
647 Instr_Owner_City = instr_city_in,
648 Instr_Owner_State_Province = instr_state_in,
649 Instr_Owner_Country = instr_country_in,
650 Instr_Owner_PostalCode = instr_postalcode_in,
651 Instr_Owner_Phone = instr_phonenumber_in,
652 Instr_Owner_Email = instr_email_in,
653 POS_Reader_Capability_Code = pos_reader_cap_in,
654 POS_Entry_Method_Code = pos_entry_method_in,
655 POS_Id_Method_Code = pos_card_id_method_in,
656 POS_Auth_Source_Code = pos_auth_source_in,
657 Reader_Data = reader_data_in,
658 POS_Trxn_Flag = l_pos_txn,
659 debit_network_code = debit_network_code_in,
660 surcharge_amount = surcharge_amount_in,
661 proc_tracenumber = proc_tracenumber_in,
662 last_update_date = sysdate,
663 last_updated_by = fnd_global.user_id,
664 creation_date = sysdate,
665 created_by = fnd_global.user_id,
666 object_version_number = object_version_number + 1
667 WHERE TrxnMID = l_trxn_mid;
668
669 insert_extensibility(l_trxn_mid,'N',extend_names_in,extend_vals_in);
670 END IF;
671
672 COMMIT;
673 END insert_auth_txn;
674
675 /* Inserts a new row into the IBY_TRXN_SUMMARIES table. This method */
676 /* would be called every time a capture, credit, return, or void */
677
678 /* operation is performed. */
679
680 PROCEDURE insert_other_txn
681 ( ecapp_id_in IN iby_trxn_summaries_all.ecappid%TYPE,
682 req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
683 order_id_in IN iby_transactions_v.order_id%TYPE,
684 merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
685 vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
686 vendor_key_in IN iby_transactions_v.bepkey%TYPE,
687 status_in IN iby_transactions_v.status%TYPE,
688 time_in IN iby_transactions_v.time%TYPE,
689 payment_type_in IN iby_transactions_v.payment_type%TYPE,
690 payment_name_in IN iby_transactions_v.payment_name%TYPE,
691 trxn_type_in IN iby_transactions_v.trxn_type%TYPE,
692 amount_in IN iby_transactions_v.amount%TYPE,
693 currency_in IN iby_transactions_v.currency%TYPE,
694 referencecode_in IN iby_transactions_v.referencecode%TYPE,
695 vendor_code_in IN iby_transactions_v.vendor_code%TYPE,
696 vendor_message_in IN iby_transactions_v.vendor_message%TYPE,
697 error_location_in IN iby_transactions_v.error_location%TYPE,
698 trace_number_in IN iby_transactions_v.TraceNumber%TYPE,
699 org_id_in IN iby_trxn_summaries_all.org_id%type,
700 billeracct_in IN iby_tangible.acctno%type,
701 refinfo_in IN iby_tangible.refinfo%type,
702 memo_in IN iby_tangible.memo%type,
703 order_medium_in IN iby_tangible.order_medium%TYPE,
704 eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
705 payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
706 instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
707 payerid_in IN iby_trxn_summaries_all.payerid%type,
708 master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
709 subkey_seed_in IN RAW,
710 trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
711 instr_expirydate_in IN iby_trxn_core.instr_expirydate%TYPE,
712 card_subtype_in IN iby_trxn_core.card_subtype_code%TYPE,
713 instr_owner_name_in IN iby_trxn_core.instr_owner_name%TYPE,
714 instr_address_line1_in IN iby_trxn_core.instr_owner_address_line1%TYPE,
715 instr_address_line2_in IN iby_trxn_core.instr_owner_address_line2%TYPE,
716 instr_address_line3_in IN iby_trxn_core.instr_owner_address_line3%TYPE,
717 instr_city_in IN iby_trxn_core.instr_owner_city%TYPE,
718 instr_state_in IN iby_trxn_core.instr_owner_state_province%TYPE,
719 instr_country_in IN iby_trxn_core.instr_owner_country%TYPE,
720 instr_postalcode_in IN iby_trxn_core.instr_owner_postalcode%TYPE,
721 instr_phonenumber_in IN iby_trxn_core.instr_owner_phone%TYPE,
722 instr_email_in IN iby_trxn_core.instr_owner_email%TYPE,
723 extend_names_in IN JTF_VARCHAR2_TABLE_100,
724 extend_vals_in IN JTF_VARCHAR2_TABLE_200,
725 transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
726 transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
727 org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
728 payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
729 factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
730 settlement_date_in IN iby_trxn_summaries_all.settledate%TYPE,
731 settlement_due_date_in IN iby_trxn_summaries_all.settlement_due_date%TYPE
732 )
733 IS
734
735 l_num_trxns NUMBER := 0;
736 l_trxn_mid NUMBER;
737 transaction_id NUMBER;
738 l_tmid iby_trxn_summaries_all.mtangibleid%type;
739 l_order_id iby_trxn_summaries_all.tangibleid%type;
740 l_mpayeeid iby_payee.mpayeeid%type;
741 l_org_id NUMBER;
742 l_target_trxn_type iby_trxn_summaries_all.trxntypeid%TYPE := -1;
743 l_instrtype iby_trxn_summaries_all.instrtype%type;
744 l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
745
746 lx_cc_hash iby_trxn_summaries_all.instrnum_hash%TYPE;
747 lx_range_id iby_cc_issuer_ranges.cc_issuer_range_id%TYPE;
748 lx_instr_len iby_trxn_summaries_all.instrnum_length%TYPE;
749 lx_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
750 l_old_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
751
752 l_instrnum iby_trxn_summaries_all.instrnumber%TYPE;
753 l_expirydate iby_trxn_core.instr_expirydate%type;
754
755 l_cursor_empty BOOLEAN;
756 l_process_profile_code iby_trxn_summaries_all.process_profile_code%TYPE;
757 l_payer_party_id iby_trxn_summaries_all.payer_party_id%TYPE;
758 l_pmt_chnl_code iby_trxn_summaries_all.payment_channel_code%TYPE;
759 l_module_name CONSTANT VARCHAR2(200) := 'IBY_TRANSACTIONCC_PKG.insert_other_txn';
760
761 CURSOR c_followon_info(ci_trxnid iby_trxn_summaries_all.transactionid%TYPE)
762 IS
763 SELECT mtangibleid, tangibleid, instrType, instrsubtype,
764 process_profile_code, payer_party_id, payment_channel_code
765 FROM iby_trxn_summaries_all
766 WHERE (transactionid = ci_trxnid)
767 --
768 -- only consider succeeded ones here
769 -- b/c different mtangibleid may get created in case of failed
770 -- auth
771 --
772 -- status 100 is equivalent to 0
773 --
774 AND (status IN (0,100))
775 --
776 -- sort by trxnmid as lowest value indicates the
777 -- first trxn for this order, which is most likely
778 -- to have all information
779 --
780 ORDER BY trxnmid ASC;
781
782 BEGIN
783 print_debuginfo(l_module_name, 'ENTER');
784 l_instrsubtype := NULL;
785 l_num_trxns := getNumPendingTrxns(merchant_id_in, order_id_in,
786 req_type_in);
787 print_debuginfo(l_module_name, 'Number of trxns::'||l_num_trxns);
788
789 IF (c_followon_info%ISOPEN) THEN
790 CLOSE c_followon_info;
791 END IF;
792
793 IF (l_num_trxns = 0) THEN
794 print_debuginfo(l_module_name, 'INSERTING TRANSACTION ROW');
795 -- Insert transaction row
796 SELECT iby_trxnsumm_mid_s.NEXTVAL
797 INTO l_trxn_mid
798 FROM dual;
799
800 transaction_mid_out := l_trxn_mid;
801
802 -- For OraPmtCredit we need to
803
804 -- 1) return transactionid
805 -- 2) Create an entry in iby_tangible table
806 IF UPPER(req_type_in) = 'ORAPMTCREDIT'
807 THEN
808
809 transaction_id := getTID(merchant_id_in, order_id_in);
810
811 transaction_id_in_out := transaction_id;
812
813 iby_bill_pkg.createBill(order_id_in,amount_in,currency_in,
814 billeracct_in,refinfo_in, memo_in,
815 order_medium_in, eft_auth_method_in, l_tmid);
816
817 l_org_id := org_id_in;
818 l_order_id := order_id_in;
819
820 --
821 -- NOTE: for all subsequent data encryptions, make sure that the
822 -- parameter to increment the subkey is set to 'N' so that
823 -- all encrypted data for the trxn uses the same key!!
824 -- else data will NOT DECRYPT CORRECTLY!!
825 --
826 l_expirydate := instr_expirydate_in;
827
828 ELSE
829 -- follow on trxns
830 --tangible info should already exist, get them based on
831 --transactionid
832 --
833 OPEN c_followon_info(transaction_id_in_out);
834 FETCH c_followon_info INTO l_tmid, l_order_id, l_instrtype, l_instrsubtype,
835 l_process_profile_code, l_payer_party_id, l_pmt_chnl_code;
836 l_cursor_empty := c_followon_info%NOTFOUND;
837 CLOSE c_followon_info;
838 --
839 -- not likely to occur, but making this assumption could lead to
840 -- a tricky error later
841 --
842 IF (l_cursor_empty) THEN
843 raise_application_error(-20000, 'IBY_20528#', FALSE);
844 END IF;
845
846 l_org_id := getOrgId(transaction_id_in_out);
847
848 END IF;
849
850 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
851
852 IF (l_instrsubtype is NULL) THEN
853 l_instrsubtype := payment_name_in;
854 END IF;
855
856 prepare_instr_data
857 (FND_API.G_FALSE,master_key_in,
858 instrnum_in,l_instrtype,l_instrnum,l_instrsubtype,
859 lx_cc_hash,lx_range_id,lx_instr_len,lx_segment_id);
860 l_instrsubtype := NVL(l_instrsubtype,payment_name_in);
861
862 print_debuginfo(l_module_name, 'payment channel code passed='||payment_channel_code_in);
863 print_debuginfo(l_module_name, 'payment channel code passed='||payment_channel_code_in);
864 print_debuginfo(l_module_name, 'settledate='||settlement_date_in);
865 print_debuginfo(l_module_name, 'settlement_due_date='||settlement_due_date_in);
866
867 INSERT INTO iby_trxn_summaries_all
868
869 (TrxnMID, TransactionID,TrxntypeID, ReqType, ReqDate,
870 Amount,CurrencyNameCode, UpdateDate,Status, PaymentMethodName,
871 TangibleID,MPayeeID, PayeeID,BEPID,bepKey, MtangibleId,
872 BEPCode,BEPMessage,Errorlocation,ecappid,org_id,
873 payerinstrid, instrnumber, payerid,
874 last_update_date,last_updated_by,creation_date,created_by,
875 last_update_login,object_version_number,instrType,instrsubtype,trxnref, org_type, payment_channel_code, factored_flag,
876 instrnum_hash, instrnum_length, cc_issuer_range_id,
877 instrnum_sec_segment_id, payer_party_id, process_profile_code,
878 salt_version,needsupdt, settledate, settlement_due_date)
879 VALUES (l_trxn_mid, transaction_id_in_out, trxn_type_in,
880 req_type_in, sysdate,
881 amount_in, currency_in, time_in, status_in, payment_type_in,
882 l_order_id, l_mpayeeid, merchant_id_in, vendor_id_in,
883 vendor_key_in, l_tmid, vendor_code_in, vendor_message_in,
884 error_location_in, ecapp_id_in, l_org_id,
885 payerinstrid_in, l_instrnum, payerid_in,
886 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
887 fnd_global.login_id, 1, l_instrtype, l_instrsubtype, trxnref_in,
888 org_type_in, nvl(payment_channel_code_in, l_pmt_chnl_code), factored_flag_in,
889 lx_cc_hash, lx_instr_len, lx_range_id, lx_segment_id,
890 l_payer_party_id, l_process_profile_code,
891 iby_security_pkg.get_salt_version,'Y', settlement_date_in, settlement_due_date_in
892 );
893
894 INSERT INTO iby_trxn_core
895 (TrxnMID, ReferenceCode, TraceNumber, InstrName,
896 Instr_Expirydate, Card_Subtype_Code,
897 Instr_Owner_Name, Instr_Owner_Address_Line1,
898 Instr_Owner_Address_Line2, Instr_Owner_Address_Line3,
899 Instr_Owner_City, Instr_Owner_State_Province, Instr_Owner_Country,
900 Instr_Owner_PostalCode, Instr_Owner_Phone, Instr_Owner_Email,
901 last_update_date,last_updated_by,creation_date,created_by,
902 last_update_login,object_version_number)
903 VALUES
904 (l_trxn_mid, referencecode_in, trace_number_in, payment_name_in,
905 l_expirydate, card_subtype_in,
906 instr_owner_name_in, instr_address_line1_in, instr_address_line2_in,
907 instr_address_line3_in, instr_city_in, instr_state_in,
908 instr_country_in, instr_postalcode_in, instr_phonenumber_in,
909 instr_email_in,
910 sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
911 fnd_global.login_id,1);
912
913 insert_extensibility(l_trxn_mid,'N',extend_names_in,extend_vals_in);
914
915 ELSIF (l_num_trxns = 1) THEN
916 -- One previous transaction, so update previous row
917
918 SELECT TrxnMID,Mtangibleid,transactionid, instrnum_sec_segment_id
919 INTO l_trxn_mid,l_tmid,transaction_id_in_out, l_old_segment_id
920 FROM iby_trxn_summaries_all
921 WHERE TangibleID = order_id_in
922 AND UPPER(ReqType) = UPPER(req_type_in)
923 AND PayeeID = merchant_id_in
924 AND Status IN (9,11);
925
926 transaction_mid_out := l_trxn_mid;
927
928 IF (UPPER(req_type_in) = 'ORAPMTCREDIT') THEN
929 --Update iby_tangible table
930 iby_bill_pkg.modBill(l_tmid,order_id_in,amount_in,currency_in,
931 billeracct_in,refinfo_in,memo_in,
932 order_medium_in, eft_auth_method_in);
933
934 -- do not update 'payerinstrid, org_id' here, same reason
935 -- as shown in 'auth'
936
937 l_expirydate := instr_expirydate_in;
938 ELSE
939
940 OPEN c_followon_info(transaction_id_in_out);
941 FETCH c_followon_info INTO l_tmid, l_order_id, l_instrtype, l_instrsubtype,
942 l_process_profile_code, l_payer_party_id, l_pmt_chnl_code;
943 l_cursor_empty := c_followon_info%NOTFOUND;
944 CLOSE c_followon_info;
945 IF (l_cursor_empty) THEN
946 raise_application_error(-20000, 'IBY_20528#', FALSE);
947 END IF;
948
949 END IF;
950
951
952 UPDATE iby_trxn_summaries_all
953 SET BEPID = vendor_id_in,
954 bepKey = vendor_key_in,
955 Amount = amount_in,
956 TrxntypeID = trxn_type_in,
957 CurrencyNameCode = currency_in,
958 UpdateDate = time_in,
959 Status = status_in,
960 ErrorLocation = error_location_in,
961 BEPCode = vendor_code_in,
962 BEPMessage = vendor_message_in,
963 --payerinstrid = payerinstrid_in,
964 PaymentMethodName = NVL(payment_type_in,PaymentMethodName),
965 instrtype = NVL(l_instrtype,instrtype),
966 instrsubtype = NVL(l_instrsubtype,instrsubtype),
967 instrnumber = l_instrnum,
968 instrnum_hash = lx_cc_hash,
969 instrnum_length = lx_instr_len,
970 cc_issuer_range_id = lx_range_id,
971 instrnum_sec_segment_id = lx_segment_id,
972 trxnref = trxnref_in,
973 Last_Update_Date = sysdate,
974 Last_Updated_by = fnd_global.user_id,
975 Creation_Date = sysdate,
976 Created_By = fnd_global.user_id,
977 Object_Version_Number = object_version_number + 1
978 WHERE TrxnMID = l_trxn_mid;
979
980 DELETE iby_security_segments WHERE sec_segment_id = l_old_segment_id;
981
982 UPDATE iby_trxn_core
983 SET ReferenceCode = referencecode_in,
984 TraceNumber = trace_number_in,
985 InstrName = NVL(payment_name_in,InstrName),
986 Instr_Expirydate = instr_expirydate_in,
987 Card_Subtype_Code = card_subtype_in,
988 Instr_Owner_Name = instr_owner_name_in,
989 Instr_Owner_Address_Line1 = instr_address_line1_in,
990 Instr_Owner_Address_Line2 = instr_address_line2_in,
991 Instr_Owner_Address_Line3 = instr_address_line3_in,
992 Instr_Owner_City = instr_city_in,
993 Instr_Owner_State_Province = instr_state_in,
994 Instr_Owner_Country = instr_country_in,
995 Instr_Owner_PostalCode = instr_postalcode_in,
996 Instr_Owner_Phone = instr_phonenumber_in,
997 Instr_Owner_Email = instr_email_in,
998 Last_Update_Date = sysdate,
999 Last_Updated_by = fnd_global.user_id,
1000 Creation_Date = sysdate,
1001 Created_By = fnd_global.user_id,
1002 Object_Version_Number = object_version_number + 1
1003 WHERE TrxnMID = l_trxn_mid;
1004
1005 insert_extensibility(l_trxn_mid,'N',extend_names_in,extend_vals_in);
1006
1007 ELSE
1008 -- will never run into this block
1009 -- More than one previous transaction, which is an
1010 -- error
1011 raise_application_error(-20000, 'IBY_20422#', FALSE);
1012 --raise_application_error(-20422, 'Multiple matching other transactions');
1013
1014
1015 END IF;
1016
1017
1018 -- for voids mark the target trxn as cancelled
1019 IF req_type_in='ORAPMTVOID' THEN
1020
1021 -- get the targe trxn type
1022 --
1023 IF trxn_type_in = 4 THEN
1024 -- auth only
1025 l_target_trxn_type := 2;
1026
1027 ELSIF trxn_type_in = 7 THEN
1028 -- auth capture
1029 l_target_trxn_type := 3;
1030
1031 ELSIF trxn_type_in = 13 THEN
1032 -- capture
1033 l_target_trxn_type := 8;
1034
1035 ELSIF trxn_type_in = 14 THEN
1036 -- mark capture
1037 l_target_trxn_type := 9;
1038
1039 ELSIF trxn_type_in = 17 THEN
1040 -- return
1041 l_target_trxn_type := 5;
1042
1043 ELSIF trxn_type_in = 18 THEN
1044 -- mark return
1045 l_target_trxn_type := 10;
1046
1047 ELSIF trxn_type_in = 19 THEN
1048 -- credit
1049 l_target_trxn_type := 11;
1050
1051 END IF;
1052
1053 UPDATE iby_trxn_summaries_all
1054 SET
1055 -- CHANGE: UPDATE STATUS FOR VOIDED GATEWAY TRXNS
1056 --
1057 -- currently only change the status for processor
1058 -- batched trxns; in the future should probably
1059 -- update for gateway trxns as well
1060 --
1061 status=decode(status,100,114,status),
1062 last_update_date = sysdate,
1063 updatedate = sysdate,
1064 last_updated_by = fnd_global.user_id,
1065 object_version_number = object_version_number + 1
1066 WHERE (transactionid=transaction_id_in_out) AND (trxntypeid=l_target_trxn_type);
1067 END IF;
1068
1069 COMMIT;
1070
1071
1072 END insert_other_txn;
1073
1074
1075 /* Inserts a row into the iby_transaction table if auth, capture, */
1076 /* return, credit, and void timeout */
1077
1078 PROCEDURE insert_timeout_txn
1079 (req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
1080 order_id_in IN iby_transactions_v.order_id%TYPE,
1081
1082 merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
1083 vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
1084 vendor_key_in IN iby_transactions_v.bepkey%TYPE,
1085 ecapp_id_in IN iby_trxn_summaries_all.ecappid%TYPE,
1086 time_in IN iby_transactions_v.time%TYPE,
1087
1088
1089 status_in IN iby_transactions_v.status%TYPE,
1090 org_id_in IN iby_trxn_summaries_all.org_id%type,
1091 amount_in IN iby_tangible.amount%type,
1092 currency_in IN iby_tangible.currencynamecode%type,
1093 billeracct_in IN iby_tangible.acctno%type,
1094 refinfo_in IN iby_tangible.refinfo%type,
1095 memo_in IN iby_tangible.memo%type,
1096 order_medium_in IN iby_tangible.order_medium%TYPE,
1097 eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
1098 payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
1099 instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
1100 payerid_in IN iby_trxn_summaries_all.payerid%type,
1101 instrtype_in IN iby_trxn_summaries_all.instrType%type,
1102 master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
1103 subkey_seed_in IN RAW,
1104 trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
1105 transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
1106 transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
1107 trxntypeid_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
1108 org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
1109 payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
1110 factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE
1111 )
1112
1113 IS
1114
1115 l_num_trxns NUMBER := 0;
1116 l_trxn_mid NUMBER;
1117 transaction_id NUMBER;
1118 l_tmid iby_trxn_summaries_all.mtangibleid%type;
1119 l_order_id iby_trxn_summaries_all.tangibleid%type;
1120 l_mpayeeid iby_payee.mpayeeid%type;
1121 l_org_id NUMBER;
1122 l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
1123
1124 l_return_status VARCHAR2(1);
1125 l_msg_count NUMBER;
1126 l_msg_data VARCHAR2(200);
1127 l_checksum_valid BOOLEAN := FALSE; -- To check whether the card number is valid.
1128 l_cc_type VARCHAR2(80);
1129
1130 l_instrnum iby_trxn_summaries_all.instrnumber%TYPE;
1131 l_subkey_id iby_payee_subkeys.payee_subkey_id%TYPE;
1132
1133 BEGIN
1134
1135 -- Count number of previous PENDING transactions
1136
1137 l_num_trxns := getNumPendingTrxns(merchant_id_in, order_id_in,
1138 req_type_in);
1139
1140 IF (l_num_trxns = 0)
1141 THEN
1142 -- Everything is fine, insert into table
1143 SELECT iby_trxnsumm_mid_s.NEXTVAL
1144 INTO l_trxn_mid
1145 FROM dual;
1146
1147 transaction_id := getTID(merchant_id_in, order_id_in);
1148
1149 transaction_id_out := transaction_id;
1150 transaction_mid_out := l_trxn_mid;
1151
1152 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
1153
1154 -- Create an entry in iby_tangible table
1155 IF ((UPPER(req_type_in) = 'ORAPMTCREDIT') OR
1156
1157 (UPPER(req_type_in) = 'ORAPMTREQ')) THEN
1158 iby_bill_pkg.createBill(order_id_in,amount_in,currency_in,
1159 billeracct_in,refinfo_in, memo_in,
1160 order_medium_in, eft_auth_method_in, l_tmid);
1161
1162 l_order_id := order_id_in;
1163 l_org_id := org_id_in;
1164 ELSE
1165 --tangible info should already exist, get them based on
1166 --transactionid
1167 SELECT DISTINCT mtangibleid, tangibleid
1168 INTO l_tmid, l_order_id
1169 FROM iby_trxn_summaries_all
1170 WHERE transactionid = transaction_id_out
1171 --
1172 -- 100 is equivalent to 0
1173 --
1174 AND (status IN (0,100));
1175
1176 -- input org_id is null, check previous orgid
1177 l_org_id := getOrgId(transaction_id_out);
1178 END IF;
1179
1180 iby_cc_validate.ValidateCC(1.0,FND_API.G_FALSE,instrnum_in,SYSDATE(),l_return_status,l_msg_count,l_msg_data,l_checksum_valid,l_cc_type);
1181 IF (l_cc_type is NULL) THEN
1182 iby_cc_validate.ValidateCC(1.0,FND_API.G_FALSE,instrnum_in,SYSDATE(),l_return_status,l_msg_count,l_msg_data,l_checksum_valid,l_cc_type);
1183 END IF;
1184 /*
1185 prepare_instr_data(ecapp_id_in,merchant_id_in,master_key_in,
1186 instrnum_in,subkey_seed_in,FND_API.G_TRUE,l_instrnum,l_subkey_id);
1187 */
1188 INSERT INTO iby_trxn_summaries_all
1189 (TrxnMID, TransactionID, ReqType, ReqDate,
1190 UpdateDate,Status, Amount, CurrencyNameCode,
1191 TangibleID,MPayeeID, PayeeID,BEPID,bepKey, ECAppID,org_id,mtangibleid,
1192 payerinstrid, instrnumber, sub_key_id, payerid, instrType,
1193 last_update_date,last_updated_by,creation_date,created_by,
1194 last_update_login,object_version_number,instrsubtype,TrxnTypeID,trxnref,
1195 org_type, payment_channel_code, factored_flag,needsupdt)
1196 VALUES (l_trxn_mid, transaction_id_out, req_type_in, time_in,
1197 time_in, status_in, amount_in, currency_in,
1198 l_order_id, l_mpayeeid, merchant_id_in, vendor_id_in,
1199 vendor_key_in, ecapp_id_in, l_org_id,l_tmid,
1200 payerinstrid_in, l_instrnum, l_subkey_id, payerid_in, instrType_in,
1201 sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
1202 fnd_global.login_id,1,l_cc_type,trxntypeid_in,trxnref_in,
1203 org_type_in, payment_channel_code_in, factored_flag_in,'Y');
1204
1205
1206 INSERT INTO iby_trxn_core
1207 (TrxnMID,
1208 last_update_date,last_updated_by,creation_date,created_by,
1209 last_update_login,object_version_number)
1210 VALUES (l_trxn_mid,
1211 sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.login_id,1);
1212
1213
1214 ELSIF (l_num_trxns = 1) THEN
1215 -- One previous transaction, so update previous row
1216 SELECT TrxnMID, TransactionID, MtangibleId
1217 INTO l_trxn_mid, transaction_id_out, l_tmid
1218
1219 FROM iby_trxn_summaries_all
1220 WHERE TangibleID = order_id_in
1221 AND UPPER(ReqType) = UPPER(req_type_in)
1222 AND PayeeID = merchant_id_in
1223 AND Status IN (9,11);
1224
1225 transaction_mid_out := l_trxn_mid;
1226
1227 IF ((UPPER(req_type_in) = 'ORAPMTCREDIT') OR
1228 (UPPER(req_type_in) = 'ORAPMTREQ')) THEN
1229 -- Update iby_tangible table
1230 iby_bill_pkg.modBill(l_tmid,order_id_in,amount_in,currency_in,
1231 billeracct_in,refinfo_in,memo_in,
1232 order_medium_in, eft_auth_method_in);
1233
1234 END IF;
1235
1236 UPDATE iby_trxn_summaries_all
1237 SET UpdateDate = time_in,
1238 Status = status_in,
1239 BEPID = vendor_id_in,
1240 bepKey = vendor_key_in,
1241 ECAppID = ecapp_id_in,
1242 -- not updating payerinstrid, org_id, org_type for the same reason
1243 --payerinstrid = payerinstrid_in,
1244 instrnumber = l_instrnum,
1245 sub_key_id = l_subkey_id,
1246 instrType = instrType_in,
1247 trxnref = trxnref_in,
1248
1249 Last_Update_Date = sysdate,
1250 Last_Updated_by = fnd_global.user_id,
1251 Creation_Date = sysdate,
1252 Created_By = fnd_global.user_id,
1253 Object_Version_Number = object_version_number + 1,
1254 payment_channel_code = payment_channel_code_in,
1255 factored_flag = factored_flag_in
1256 WHERE TrxnMID = l_trxn_mid;
1257
1258 ELSE
1259
1260 -- will never run into this block
1261 -- More than one previous transaction, which is an
1262 -- error
1263 raise_application_error(-20000, 'IBY_20422#', FALSE);
1264 --raise_application_error(-20422, 'Multiple matching timeout transactions');
1265
1266 END IF;
1267
1268 COMMIT;
1269
1270 END insert_timeout_txn;
1271
1272
1273 /* Inserts a row about batch status into iby_batches_all. This will */
1274 /* be called for link error, timeout error or other batch status */
1275
1276 PROCEDURE insert_batch_status
1277 (merch_batchid_in IN iby_batches_all.batchid%TYPE,
1278 merchant_id_in IN iby_batches_all.payeeid%TYPE,
1279 vendor_id_in IN iby_batches_all.bepid%TYPE,
1280 vendor_key_in IN iby_batches_all.bepkey%TYPE,
1281 pmt_type_in IN iby_batches_all.paymentmethodname%TYPE,
1282 status_in IN iby_batches_all.batchstatus%TYPE,
1283 time_in IN iby_batches_all.batchclosedate%TYPE,
1284 viby_batchid_in IN iby_batches_all.vpsbatchid%TYPE ,
1285 currency_in IN iby_batches_all.currencynamecode%TYPE,
1286 numtrxns_in IN iby_batches_all.NumTrxns%TYPE,
1287 batchstate_in IN iby_batches_all.BatchStateid%TYPE,
1288 batchtotal_in IN iby_batches_all.BatchTotal%TYPE,
1289 saleamount_in IN iby_batches_all.BatchSales%TYPE,
1290 cramount_in IN iby_batches_all.BatchCredit%TYPE,
1291 gwid_in IN iby_batches_all.GWBatchID%TYPE,
1292 vendor_code_in IN iby_batches_all.BEPcode%TYPE,
1293 vendor_message_in IN iby_batches_all.BEPmessage%TYPE,
1294 error_location_in IN iby_batches_all.errorlocation%TYPE,
1295 terminal_id_in IN iby_batches_all.TerminalId%TYPE,
1296 acquirer_id_in IN iby_batches_all.Acquirer%TYPE,
1297 org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
1298 req_type_in IN iby_batches_all.reqtype%TYPE,
1299 sec_key_present_in IN VARCHAR2,
1300 mbatchid_out OUT NOCOPY iby_batches_all.mbatchid%type
1301 )
1302 IS
1303
1304 numrows NUMBER;
1305 l_mpayeeid iby_payee.mpayeeid%type;
1306 l_mbatchid iby_batches_all.mbatchid%type;
1307 l_beptype iby_bepinfo.bep_type%TYPE;
1308 l_trxncount iby_batches_all.numtrxns%TYPE;
1309 l_batchcurr iby_batches_all.currencynamecode%TYPE;
1310 l_pinlessdebitcard CONSTANT VARCHAR2(100) :='PINLESSDEBITCARD';
1311 BEGIN
1312
1313 -- First check if a row already exists for this batch status
1314
1315 SELECT COUNT(*)
1316 INTO numrows
1317 FROM iby_batches_all
1318 WHERE batchid = merch_batchid_in
1319 AND payeeid = merchant_id_in;
1320
1321 -- insert batch status into iby_batches_all
1322
1323 IF numrows = 0
1324
1325 THEN
1326 --
1327 -- need to lock trxn summaries table to ensure that
1328 -- trxns which have not been validated do not sneak into
1329 -- the batch; gap between call to validate_open_batch
1330 -- and update of IBY_TRXN_SUMMARIES_ALL has been shown
1331 -- to be vulnerable to race conditions even under moderate
1332 -- concurrency loads
1333 --
1334 LOCK TABLE iby_batches_all, iby_trxn_summaries_all IN EXCLUSIVE MODE;
1335
1336 SELECT iby_batches_s.NEXTVAL
1337 INTO l_mbatchid
1338 FROM dual;
1339
1340 mbatchid_out := l_mbatchid;
1341
1342 l_batchcurr := currency_in;
1343 l_trxncount := numtrxns_in;
1344
1345 SELECT NVL(bep_type,iby_bepinfo_pkg.C_BEPTYPE_GATEWAY)
1346 INTO l_beptype
1347 FROM iby_bepinfo
1348 WHERE (bepid=vendor_id_in);
1349
1350 --
1351 -- if the bep is a processor, then we create a batch
1352 --
1353 IF ( (l_beptype = iby_bepinfo_pkg.C_BEPTYPE_PROCESSOR) AND
1354 ((req_type_in = iby_transactioncc_pkg.C_REQTYPE_BATCHCLOSE) OR
1355 (req_type_in = iby_transactioncc_pkg.C_REQTYPE_PDC_BATCHCLOSE)))
1356 THEN
1357
1358 --
1359 -- associate all trxns in the current open batch
1360 -- with the bathc id of the batch close
1361 --
1362 UPDATE iby_trxn_summaries_all
1363 SET
1364 status = iby_transactioncc_pkg.C_STATUS_BATCH_PENDING,
1365 batchid = merch_batchid_in,
1366 mbatchid = l_mbatchid,
1367 last_update_date = sysdate,
1368 updatedate = sysdate,
1369 last_updated_by = fnd_global.user_id,
1370 object_version_number = object_version_number + 1
1371 WHERE (bepid = vendor_id_in)
1372 AND (bepkey = vendor_key_in)
1373 AND (payeeid = merchant_id_in)
1374 AND (status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED)
1375 AND ((instrtype IN (iby_creditcard_pkg.C_INSTRTYPE_CCARD,
1376 iby_creditcard_pkg.C_INSTRTYPE_PCARD)
1377 AND
1378 (req_type_in = iby_transactioncc_pkg.C_REQTYPE_BATCHCLOSE))
1379 OR
1380 (instrtype IN (l_pinlessdebitcard)
1381 AND
1382 (req_type_in = iby_transactioncc_pkg.C_REQTYPE_PDC_BATCHCLOSE))
1383 OR
1384 instrtype IS NULL)
1385 AND (batchid IS NULL);
1386
1387 END IF;
1388
1389 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
1390
1391 INSERT INTO iby_batches_all
1392 (MBATCHID, BATCHID, MPAYEEID, PAYEEID, BEPID, BEPKEY, PAYMENTMETHODNAME,
1393 BATCHSTATUS, BATCHCLOSEDATE, VPSBATCHID, CURRENCYNAMECODE,
1394 NUMTRXNS, BATCHSTATEID, BATCHTOTAL, BATCHSALES, BATCHCREDIT,
1395 GWBATCHID, BEPCODE, BEPMESSAGE, ERRORLOCATION,
1396
1397 TerminalId, Acquirer,reqtype, reqdate,
1398 last_update_date,last_updated_by,creation_date,created_by,
1399 last_update_login, object_version_number)
1400 VALUES
1401 ( l_mbatchid, merch_batchid_in, l_mpayeeid, merchant_id_in, vendor_id_in,
1402 vendor_key_in, pmt_type_in, status_in, time_in, viby_batchid_in,
1403 '', 0, batchstate_in, batchtotal_in, saleamount_in,
1404 cramount_in, gwid_in, vendor_code_in, vendor_message_in,
1405 error_location_in, terminal_id_in, Acquirer_id_in,req_type_in, sysdate,
1406 sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.login_id,1);
1407
1408 validate_open_batch(vendor_id_in, l_mbatchid, sec_key_present_in,
1409 l_trxncount, l_batchcurr);
1410
1411 UPDATE iby_batches_all
1412 SET CURRENCYNAMECODE = l_batchcurr,
1413 NUMTRXNS = l_trxncount
1414 WHERE mbatchid = l_mbatchid;
1415
1416 ELSIF (numrows = 1)
1417 THEN
1418 l_trxncount := numtrxns_in;
1419 IF (l_trxncount<1) THEN
1420 l_trxncount := NULL;
1421 END IF;
1422 -- One previous transaction, so update previous row
1423 UPDATE iby_batches_all
1424 SET PAYMENTMETHODNAME = pmt_type_in,
1425 BATCHSTATUS = status_in,
1426 BATCHCLOSEDATE = time_in,
1427 CURRENCYNAMECODE = NVL(currency_in,CURRENCYNAMECODE),
1428 NUMTRXNS = NVL(l_trxncount,NUMTRXNS),
1429 BATCHSTATEID = batchstate_in,
1430 BATCHTOTAL = batchtotal_in,
1431 BATCHSALES = saleamount_in,
1432 BATCHCREDIT = cramount_in,
1433 GWBATCHID = gwid_in,
1434 BEPCODE = vendor_code_in,
1435 BEPMESSAGE = vendor_message_in,
1436 ERRORLOCATION = error_location_in,
1437 Last_Update_Date = sysdate,
1438 Last_Updated_by = fnd_global.user_id,
1439
1440 -- Do not update creation timestamp
1441 -- when updating records: Bug 3128675
1442 --Creation_Date = sysdate,
1443 --Created_By = fnd_global.user_id,
1444
1445 Object_Version_Number = Object_Version_Number + 1
1446
1447 WHERE batchid = merch_batchid_in
1448 AND payeeid = merchant_id_in;
1449
1450 IF ((req_type_in = 'ORAPMTCLOSEBATCH') OR
1451 (req_type_in = 'ORAPMTPDCCLOSEBATCH') ) THEN
1452 -- we don't update the following for querybatch
1453 UPDATE iby_batches_all
1454 SET VPSBATCHID = viby_batchid_in,
1455 reqtype = req_type_in,
1456 reqdate = sysdate
1457 WHERE batchid = merch_batchid_in
1458 AND payeeid = merchant_id_in;
1459 END IF;
1460
1461 SELECT mbatchid
1462 INTO mbatchid_out
1463 FROM iby_batches_all
1464 WHERE batchid = merch_batchid_in
1465 AND payeeid = merchant_id_in;
1466
1467 ELSE
1468 -- will never run into this block
1469 -- More than one pending transaction, which is an
1470 -- error
1471 raise_application_error(-20000, 'IBY_20422#', FALSE);
1472 END IF;
1473
1474 COMMIT;
1475
1476 EXCEPTION
1477 WHEN OTHERS THEN
1478 ROLLBACK;
1479 --
1480 -- rethrow any internally generated exception
1481 --
1482 --raise_application_error(SQLCODE, SQLERRM, FALSE);
1483 RAISE;
1484 END insert_batch_status;
1485
1486 /*--------------------------------------------------------------------
1487 | NAME:
1488 | insert_batch_status_new
1489 |
1490 | PURPOSE:
1491 |
1492 |
1493 |
1494 | PARAMETERS:
1495 | IN
1496 |
1497 |
1498 | OUT
1499 |
1500 |
1501 | RETURNS:
1502 |
1503 | NOTES:
1504 |
1505 *---------------------------------------------------------------------*/
1506 PROCEDURE insert_batch_status_new
1507 (
1508 merch_batchid_in IN iby_batches_all.batchid%TYPE,
1509 profile_code_in IN iby_batches_all.process_profile_code%TYPE,
1510 merchant_id_in IN iby_batches_all.payeeid%TYPE,
1511 vendor_id_in IN iby_batches_all.bepid%TYPE,
1512 vendor_key_in IN iby_batches_all.bepkey%TYPE,
1513 pmt_type_in IN iby_batches_all.paymentmethodname%TYPE,
1514 status_in IN iby_batches_all.batchstatus%TYPE,
1515 time_in IN iby_batches_all.batchclosedate%TYPE,
1516 viby_batchid_in IN iby_batches_all.vpsbatchid%TYPE ,
1517 currency_in IN iby_batches_all.currencynamecode%TYPE,
1518 numtrxns_in IN iby_batches_all.NumTrxns%TYPE,
1519 batchstate_in IN iby_batches_all.BatchStateid%TYPE,
1520 batchtotal_in IN iby_batches_all.BatchTotal%TYPE,
1521 saleamount_in IN iby_batches_all.BatchSales%TYPE,
1522 cramount_in IN iby_batches_all.BatchCredit%TYPE,
1523 gwid_in IN iby_batches_all.GWBatchID%TYPE,
1524 vendor_code_in IN iby_batches_all.BEPcode%TYPE,
1525 vendor_message_in IN iby_batches_all.BEPmessage%TYPE,
1526 error_location_in IN iby_batches_all.errorlocation%TYPE,
1527 terminal_id_in IN iby_batches_all.TerminalId%TYPE,
1528 acquirer_id_in IN iby_batches_all.Acquirer%TYPE,
1529 org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
1530 req_type_in IN iby_batches_all.reqtype%TYPE,
1531 sec_key_present_in IN VARCHAR2,
1532 acct_profile_in IN iby_batches_all.process_profile_code%TYPE,
1533 instr_type_in IN iby_batches_all.instrument_type%TYPE,
1534 br_disputed_flag_in IN iby_batches_all.br_disputed_flag%TYPE,
1535 f_pmt_channel_in IN iby_trxn_summaries_all.
1536 payment_channel_code%TYPE,
1537 f_curr_in IN iby_trxn_summaries_all.
1538 currencynamecode%TYPE,
1539 f_settle_date IN iby_trxn_summaries_all.
1540 settledate%TYPE,
1541 f_due_date IN iby_trxn_summaries_all.
1542 settlement_due_date%TYPE,
1543 f_maturity_date IN iby_trxn_summaries_all.
1544 br_maturity_date%TYPE,
1545 f_instr_type IN iby_trxn_summaries_all.
1546 instrtype%TYPE,
1547 mbatch_ids_out OUT NOCOPY JTF_NUMBER_TABLE,
1548 batch_ids_out OUT NOCOPY JTF_VARCHAR2_TABLE_100
1549 )
1550 IS
1551
1552 numrows NUMBER;
1553
1554 l_mpayeeid iby_payee.mpayeeid%type;
1555 l_mbatchid iby_batches_all.mbatchid%type;
1556 l_beptype iby_bepinfo.bep_type%TYPE;
1557 l_trxncount iby_batches_all.numtrxns%TYPE;
1558 l_batchcurr iby_batches_all.currencynamecode%TYPE;
1559
1560 l_pinlessdebitcard CONSTANT VARCHAR2(100) :='PINLESSDEBITCARD';
1561
1562 l_batches_tab batchAttrTabType;
1563 l_trxns_in_batch_tab trxnsInBatchTabType;
1564 l_mbatch_ids_out mBatchIdsTab;
1565
1566 l_index NUMBER;
1567
1568 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
1569 '.insert_batch_status_new';
1570
1571 /*
1572 * Cursor to pick up all existing mbatchids for
1573 * a given (batch id, payee id, profile code)
1574 * combination.
1575 */
1576 CURSOR c_mbatch_ids (batch_id IBY_BATCHES_ALL.batchid%TYPE,
1577 payee_id IBY_BATCHES_ALL.payeeid%TYPE,
1578 profile_cd IBY_BATCHES_ALL.process_profile_code%TYPE
1579 )
1580 IS
1581 SELECT
1582 mbatchid
1583 FROM
1584 IBY_BATCHES_ALL
1585 WHERE
1586 batchid = batch_id AND
1587 payeeid = payee_id AND
1588 process_profile_code = profile_cd
1589 ;
1590
1591 BEGIN
1592
1593 print_debuginfo(l_module_name, 'ENTER');
1594
1595 mbatch_ids_out := JTF_NUMBER_TABLE();
1596 batch_ids_out := JTF_VARCHAR2_TABLE_100();
1597
1598 /* First check if a row already exists for this batch status */
1599 SELECT
1600 COUNT(*)
1601 INTO
1602 numrows
1603 FROM
1604 IBY_BATCHES_ALL
1605 WHERE
1606 batchid = merch_batchid_in AND
1607 payeeid = merchant_id_in
1608 ;
1609
1610 /*
1611 * If row does not exist, then insert batch status into iby_batches_all
1612 */
1613 IF numrows = 0 THEN
1614
1615 print_debuginfo(l_module_name, 'num rows is zero');
1616
1617 --
1618 -- need to lock trxn summaries table to ensure that
1619 -- trxns which have not been validated do not sneak into
1620 -- the batch; gap between call to validate_open_batch
1621 -- and update of IBY_TRXN_SUMMARIES_ALL has been shown
1622 -- to be vulnerable to race conditions even under moderate
1623 -- concurrency loads
1624 --
1625 LOCK TABLE iby_batches_all, iby_trxn_summaries_all IN EXCLUSIVE MODE;
1626
1627 --SELECT iby_batches_s.NEXTVAL
1628 -- INTO l_mbatchid
1629 --FROM dual;
1630
1631 --mbatchid_out := l_mbatchid;
1632
1633 l_batchcurr := currency_in;
1634 l_trxncount := numtrxns_in;
1635
1636 SELECT
1637 NVL(bep_type,iby_bepinfo_pkg.C_BEPTYPE_GATEWAY)
1638 INTO
1639 l_beptype
1640 FROM
1641 IBY_BEPINFO
1642 WHERE
1643 (bepid=vendor_id_in)
1644 ;
1645
1646 print_debuginfo(l_module_name, 'STEP 1');
1647
1648 --
1649 -- if the bep is a processor, then we create a batch
1650 --
1651 IF (
1652 (l_beptype = iby_bepinfo_pkg.C_BEPTYPE_PROCESSOR) AND
1653 (
1654 (req_type_in = iby_transactioncc_pkg.C_REQTYPE_BATCHCLOSE) OR
1655 (req_type_in = iby_transactioncc_pkg.C_REQTYPE_PDC_BATCHCLOSE)
1656 )
1657 ) THEN
1658
1659 print_debuginfo(l_module_name, 'Invoking grouping ..');
1660
1661 /*
1662 * Group all the transactions for this profile into
1663 * batches as per the grouping attributes on the profile.
1664 */
1665 performTransactionGrouping(
1666 profile_code_in,
1667 instr_type_in,
1668 req_type_in,
1669 f_pmt_channel_in,
1670 f_curr_in,
1671 f_settle_date,
1672 f_due_date,
1673 f_maturity_date,
1674 f_instr_type,
1675 l_batches_tab,
1676 l_trxns_in_batch_tab
1677 );
1678
1679 print_debuginfo(l_module_name, '# batches created: '
1680 || l_batches_tab.COUNT);
1681
1682 print_debuginfo(l_module_name, '# transactions processed: '
1683 || l_trxns_in_batch_tab.COUNT);
1684
1685 /*
1686 * After grouping it is possible that multiple batches were
1687 * created. Each batch will be a separate row in the
1688 * IBY_BATCHES_ALL table with a unique mbatchid.
1689 *
1690 * The user may have provided a batch id (batch prefix), we will
1691 * have to assign that batch id to each of the created batches.
1692 *
1693 * This batch id would be sent to the payment system. It therefore
1694 * has to be unique. Therefore, we add a suffix to the user
1695 * provided batch id to ensure that batches created after grouping
1696 * have a unique batch id.
1697 */
1698 IF (l_batches_tab.COUNT > 0) THEN
1699
1700 l_index := 1;
1701 FOR k IN l_batches_tab.FIRST .. l_batches_tab.LAST LOOP
1702
1703 /*
1704 * Assign a unique batch id to each batch.
1705 */
1706 l_batches_tab(k).batch_id :=
1707 merch_batchid_in ||'_'|| l_index;
1708 l_index := l_index + 1;
1709
1710 END LOOP;
1711
1712 END IF;
1713
1714 /*
1715 * After grouping, the transactions will be assigned a mbatch id.
1716 * Assign them a batch id as well (based on the batch id
1717 * corresponding to each mbatch id).
1718 */
1719 IF (l_trxns_in_batch_tab.COUNT > 0) THEN
1720
1721 FOR m IN l_trxns_in_batch_tab.FIRST ..
1722 l_trxns_in_batch_tab.LAST LOOP
1723
1724 FOR k IN l_batches_tab.FIRST .. l_batches_tab.LAST LOOP
1725
1726 /*
1727 * Find the mbatch id in the batches array
1728 * corresponding to the mbatchid of this transaction.
1729 */
1730 IF (l_trxns_in_batch_tab(m).mbatch_id =
1731 l_batches_tab(k).mbatch_id) THEN
1732
1733 /*
1734 * Assign the batch id from the batches array
1735 * to this transaction.
1736 */
1737 l_trxns_in_batch_tab(m).batch_id :=
1738 l_batches_tab(k).batch_id;
1739
1740 END IF;
1741
1742 END LOOP;
1743
1744 END LOOP;
1745
1746 END IF;
1747
1748 /*
1749 * BEP and vendor related params.
1750 */
1751 print_debuginfo(l_module_name, 'vendor_id_in: '
1752 || vendor_id_in);
1753 print_debuginfo(l_module_name, 'vendor_key_in: '
1754 || vendor_key_in);
1755 print_debuginfo(l_module_name, 'merchant_id_in: '
1756 || merchant_id_in);
1757 print_debuginfo(l_module_name, 'req_type_in: '
1758 || req_type_in);
1759
1760 --
1761 -- associate all trxns in the current open batch
1762 -- with the batch id of the batch close
1763 --
1764 IF (l_trxns_in_batch_tab.COUNT <> 0) THEN
1765
1766 FOR i IN l_trxns_in_batch_tab.FIRST ..
1767 l_trxns_in_batch_tab.LAST LOOP
1768
1769 /*
1770 * This SQL statement has been replaced by
1771 * the SQL update statement (below). It is kept
1772 * here for documentation purposes.
1773 */
1774 /*------------------------------------------
1775 UPDATE
1776 IBY_TRXN_SUMMARIES_ALL
1777 SET
1778 status = iby_transactioncc_pkg.C_STATUS_BATCH_PENDING,
1779 batchid = merch_batchid_in
1780 || '_' || i,
1781 mbatchid = l_trxns_in_batch_tab(i).mbatch_id,
1782 last_update_date = sysdate,
1783 updatedate = sysdate,
1784 last_updated_by = fnd_global.user_id,
1785 object_version_number = object_version_number + 1
1786 WHERE
1787 (bepid = vendor_id_in) AND
1788 (bepkey = vendor_key_in) AND
1789 (payeeid = merchant_id_in) AND
1790 (status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED)i
1791 AND
1792 (
1793 (instrtype IN
1794 (iby_creditcard_pkg.C_INSTRTYPE_CCARD,
1795 iby_creditcard_pkg.C_INSTRTYPE_PCARD) AND
1796 (req_type_in =
1797 iby_transactioncc_pkg.C_REQTYPE_BATCHCLOSE))
1798 OR
1799 (instrtype IN (l_pinlessdebitcard) AND
1800 (req_type_in = iby_transactioncc_pkg.
1801 C_REQTYPE_PDC_BATCHCLOSE)
1802 )
1803 OR
1804 instrtype IS NULL
1805 )
1806 AND
1807 (batchid IS NULL);
1808 -------------------------------------------*/
1809
1810 print_debuginfo(l_module_name, 'Going to update '
1811 || 'transaction ' || l_trxns_in_batch_tab(i).trxn_id);
1812
1813 UPDATE
1814 IBY_TRXN_SUMMARIES_ALL
1815 SET
1816 status = iby_transactioncc_pkg.
1817 C_STATUS_BATCH_PENDING,
1818 batchid = l_trxns_in_batch_tab(i).
1819 batch_id,
1820 mbatchid = l_trxns_in_batch_tab(i).
1821 mbatch_id,
1822 last_update_date = sysdate,
1823 updatedate = sysdate,
1824 last_updated_by = fnd_global.user_id,
1825 object_version_number = object_version_number + 1
1826 WHERE
1827 transactionid = l_trxns_in_batch_tab(i).trxn_id AND
1828 status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED
1829 ;
1830
1831 print_debuginfo(l_module_name, 'Finished updating '
1832 || 'transaction'
1833 || l_trxns_in_batch_tab(i).trxn_id
1834 );
1835
1836 END LOOP;
1837
1838 END IF; -- if trxn count <> 0
1839
1840 END IF; -- if bep type = PROCESSOR
1841
1842 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
1843
1844 IF (l_batches_tab.COUNT <> 0) THEN
1845
1846 FOR i IN l_batches_tab.FIRST .. l_batches_tab.LAST LOOP
1847
1848 print_debuginfo(l_module_name, 'Going to insert batch '
1849 || l_batches_tab(i).mbatch_id);
1850
1851 INSERT INTO
1852 iby_batches_all
1853 (
1854 MBATCHID,
1855 BATCHID,
1856 MPAYEEID,
1857 PAYEEID,
1858 BEPID,
1859 BEPKEY,
1860 PAYMENTMETHODNAME,
1861 BATCHSTATUS,
1862 BATCHCLOSEDATE,
1863 VPSBATCHID,
1864 CURRENCYNAMECODE,
1865 NUMTRXNS,
1866 BATCHSTATEID,
1867 BATCHTOTAL,
1868 BATCHSALES,
1869 BATCHCREDIT,
1870 GWBATCHID,
1871 BEPCODE,
1872 BEPMESSAGE,
1873 ERRORLOCATION,
1874 TERMINALID,
1875 ACQUIRER,
1876 REQTYPE,
1877 REQDATE,
1878 PROCESS_PROFILE_CODE,
1879 INSTRUMENT_TYPE,
1880 BR_DISPUTED_FLAG,
1881 LAST_UPDATE_DATE,
1882 LAST_UPDATED_BY,
1883 CREATION_DATE,
1884 CREATED_BY,
1885 LAST_UPDATE_LOGIN,
1886 OBJECT_VERSION_NUMBER,
1887 PAYEEINSTRID,
1888 LEGAL_ENTITY_ID,
1889 ORG_ID,
1890 ORG_TYPE,
1891 SETTLEDATE
1892 )
1893 VALUES
1894 (
1895 l_batches_tab(i).mbatch_id,
1896 merch_batchid_in || '_' || i,
1897 l_mpayeeid,
1898 merchant_id_in,
1899 vendor_id_in,
1900 l_batches_tab(i).bep_key,
1901 pmt_type_in,
1902 status_in,
1903 time_in,
1904 viby_batchid_in,
1905 l_batches_tab(i).curr_code,
1906 0,
1907 batchstate_in,
1908 batchtotal_in,
1909 saleamount_in,
1910 cramount_in,
1911 gwid_in,
1912 vendor_code_in,
1913 vendor_message_in,
1914 error_location_in,
1915 terminal_id_in,
1916 Acquirer_id_in,
1917 req_type_in,
1918 sysdate,
1919 l_batches_tab(i).profile_code,
1920 instr_type_in,
1921 br_disputed_flag_in,
1922 sysdate,
1923 fnd_global.user_id,
1924 sysdate,
1925 fnd_global.user_id,
1926 fnd_global.login_id,
1927 1,
1928 l_batches_tab(i).int_bank_acct_id,
1929 l_batches_tab(i).le_id,
1930 l_batches_tab(i).org_id,
1931 l_batches_tab(i).org_type,
1932 l_batches_tab(i).settle_date
1933 );
1934
1935 print_debuginfo(l_module_name, 'finished insert '
1936 || 'for batch id '
1937 || l_batches_tab(i).mbatch_id
1938 );
1939
1940 validate_open_batch(
1941 vendor_id_in,
1942 l_batches_tab(i).mbatch_id,
1943 sec_key_present_in,
1944 l_trxncount,
1945 l_batchcurr);
1946
1947 UPDATE
1948 IBY_BATCHES_ALL
1949 SET
1950 currencynamecode = l_batchcurr,
1951 numtrxns = l_trxncount
1952 WHERE
1953 mbatchid = l_batches_tab(i).mbatch_id
1954 ;
1955
1956 /*
1957 * Store the created mbatchids in the output param
1958 * to return to the caller.
1959 */
1960 mbatch_ids_out.EXTEND;
1961 mbatch_ids_out(i) := l_batches_tab(i).mbatch_id;
1962
1963 /*
1964 * Store the created batchids in the output param
1965 * to return to the caller.
1966 */
1967 batch_ids_out.EXTEND;
1968 batch_ids_out(i) := l_batches_tab(i).batch_id;
1969
1970 END LOOP;
1971
1972 END IF; -- if l_batches_tab.COUNT <> 0
1973
1974 ELSIF (numrows = 1) THEN
1975
1976 l_trxncount := numtrxns_in;
1977
1978 IF (l_trxncount<1) THEN
1979 l_trxncount := NULL;
1980 END IF;
1981
1982 /* One previous transaction, so update previous row */
1983 UPDATE
1984 IBY_BATCHES_ALL
1985 SET
1986 PAYMENTMETHODNAME = pmt_type_in,
1987 BATCHSTATUS = status_in,
1988 BATCHCLOSEDATE = time_in,
1989 CURRENCYNAMECODE = NVL(currency_in,CURRENCYNAMECODE),
1990 NUMTRXNS = NVL(l_trxncount,NUMTRXNS),
1991 BATCHSTATEID = batchstate_in,
1992 BATCHTOTAL = batchtotal_in,
1993 BATCHSALES = saleamount_in,
1994 BATCHCREDIT = cramount_in,
1995 GWBATCHID = gwid_in,
1996 BEPCODE = vendor_code_in,
1997 BEPMESSAGE = vendor_message_in,
1998 ERRORLOCATION = error_location_in,
1999 LAST_UPDATE_DATE = sysdate,
2000 LAST_UPDATED_BY = fnd_global.user_id,
2001
2002 -- Do not update creation timestamp
2003 -- when updating records: Bug 3128675
2004 --CREATION_DATE = sysdate,
2005 --CREATED_BY = fnd_global.user_id,
2006
2007 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
2008 WHERE
2009 batchid = merch_batchid_in AND
2010 payeeid = merchant_id_in
2011 ;
2012
2013 IF ((req_type_in = 'ORAPMTCLOSEBATCH') OR
2014 (req_type_in = 'ORAPMTPDCCLOSEBATCH') ) THEN
2015
2016 -- we don't update the following for querybatch
2017 UPDATE
2018 iby_batches_all
2019 SET
2020 VPSBATCHID = viby_batchid_in,
2021 reqtype = req_type_in,
2022 reqdate = sysdate
2023 WHERE
2024 batchid = merch_batchid_in AND
2025 payeeid = merchant_id_in;
2026
2027 END IF;
2028
2029 --SELECT mbatchid
2030 --INTO mbatchid_out
2031 --FROM iby_batches_all
2032 --WHERE batchid = merch_batchid_in
2033 --AND payeeid = merchant_id_in;
2034
2035 /*
2036 * Pick up all mbatchids for the given (batch id, merchant id,
2037 * account profile) combination.
2038 *
2039 * Since this is a retry, and retry is only applicable to a
2040 * specific batch, we should be getting only one mbatchid.
2041 */
2042 OPEN c_mbatch_ids (merch_batchid_in, merchant_id_in, profile_code_in);
2043 FETCH c_mbatch_ids BULK COLLECT INTO l_mbatch_ids_out;
2044 CLOSE c_mbatch_ids;
2045
2046 IF (l_mbatch_ids_out.COUNT <> 0) THEN
2047
2048 FOR i IN l_mbatch_ids_out.FIRST .. l_mbatch_ids_out.LAST LOOP
2049
2050 /*
2051 * In the retry scenario, the user will provide
2052 * the batch id to retry explicitly. So in the
2053 * retry case, we will returning only one batch id
2054 * and one mbatch id.
2055 */
2056
2057 mbatch_ids_out.EXTEND;
2058 mbatch_ids_out(i) := l_mbatch_ids_out(i);
2059
2060 batch_ids_out.EXTEND;
2061 batch_ids_out(i) := merch_batchid_in;
2062
2063 END LOOP;
2064
2065 END IF;
2066
2067 ELSE
2068
2069 -- will never run into this block
2070 -- More than one pending transaction, which is an
2071 -- error
2072 raise_application_error(-20000, 'IBY_20422#', FALSE);
2073
2074 END IF;
2075
2076 print_debuginfo(l_module_name, 'mbatchids out count: '
2077 || mbatch_ids_out.COUNT);
2078
2079 COMMIT;
2080
2081 EXCEPTION
2082
2083 WHEN OTHERS THEN
2084 ROLLBACK;
2085 --
2086 -- rethrow any internally generated exception
2087 --
2088 --raise_application_error(SQLCODE, SQLERRM, FALSE);
2089 RAISE;
2090
2091 END insert_batch_status_new;
2092
2093
2094 /*--------------------------------------------------------------------
2095 | NAME:
2096 | insert_batch_status_new
2097 |
2098 | PURPOSE:
2099 | This is an Overloaded API of the previous one. This one
2100 | takes an Array of Account FC profiles instead of a single on.
2101 | This virtually means that we are accepting multiple bep keys in the API.
2102 | THis will turn on the feature where we will have multiple divisions per
2103 | Settlement Batch file.
2104 |
2105 | PARAMETERS:
2106 | IN
2107 |
2108 |
2109 | OUT
2110 |
2111 |
2112 | RETURNS:
2113 |
2114 | NOTES:
2115 |
2116 *---------------------------------------------------------------------*/
2117 PROCEDURE insert_batch_status_new
2118 (
2119 merch_batchid_in IN iby_batches_all.batchid%TYPE,
2120 profile_code_array IN JTF_VARCHAR2_TABLE_100,
2121 merchant_id_in IN iby_batches_all.payeeid%TYPE,
2122 vendor_id_in IN iby_batches_all.bepid%TYPE,
2123 vendor_key_in IN iby_batches_all.bepkey%TYPE,
2124 pmt_type_in IN iby_batches_all.paymentmethodname%TYPE,
2125 status_in IN iby_batches_all.batchstatus%TYPE,
2126 time_in IN iby_batches_all.batchclosedate%TYPE,
2127 viby_batchid_in IN iby_batches_all.vpsbatchid%TYPE ,
2128 currency_in IN iby_batches_all.currencynamecode%TYPE,
2129 numtrxns_in IN iby_batches_all.NumTrxns%TYPE,
2130 batchstate_in IN iby_batches_all.BatchStateid%TYPE,
2131 batchtotal_in IN iby_batches_all.BatchTotal%TYPE,
2132 saleamount_in IN iby_batches_all.BatchSales%TYPE,
2133 cramount_in IN iby_batches_all.BatchCredit%TYPE,
2134 gwid_in IN iby_batches_all.GWBatchID%TYPE,
2135 vendor_code_in IN iby_batches_all.BEPcode%TYPE,
2136 vendor_message_in IN iby_batches_all.BEPmessage%TYPE,
2137 error_location_in IN iby_batches_all.errorlocation%TYPE,
2138 terminal_id_in IN iby_batches_all.TerminalId%TYPE,
2139 acquirer_id_in IN iby_batches_all.Acquirer%TYPE,
2140 org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
2141 req_type_in IN iby_batches_all.reqtype%TYPE,
2142 sec_key_present_in IN VARCHAR2,
2143 acct_profile_in IN iby_batches_all.process_profile_code%TYPE,
2144 instr_type_in IN iby_batches_all.instrument_type%TYPE,
2145 br_disputed_flag_in IN iby_batches_all.br_disputed_flag%TYPE,
2146 f_pmt_channel_in IN iby_trxn_summaries_all.
2147 payment_channel_code%TYPE,
2148 f_curr_in IN iby_trxn_summaries_all.
2149 currencynamecode%TYPE,
2150 f_settle_date IN iby_trxn_summaries_all.
2151 settledate%TYPE,
2152 f_due_date IN iby_trxn_summaries_all.
2153 settlement_due_date%TYPE,
2154 f_maturity_date IN iby_trxn_summaries_all.
2155 br_maturity_date%TYPE,
2156 f_instr_type IN iby_trxn_summaries_all.
2157 instrtype%TYPE,
2158 mbatch_ids_out OUT NOCOPY JTF_NUMBER_TABLE,
2159 batch_ids_out OUT NOCOPY JTF_VARCHAR2_TABLE_100
2160 )
2161 IS
2162
2163 numrows NUMBER;
2164
2165 l_mpayeeid iby_payee.mpayeeid%type;
2166 l_mbatchid iby_batches_all.mbatchid%type;
2167 l_beptype iby_bepinfo.bep_type%TYPE;
2168 l_trxncount iby_batches_all.numtrxns%TYPE;
2169 l_batchcurr iby_batches_all.currencynamecode%TYPE;
2170 -- profile_code_in iby_batches_all.process_profile_code%TYPE;
2171 numProfiles NUMBER;
2172 strProfCodes VARCHAR2(200);
2173
2174 l_pinlessdebitcard CONSTANT VARCHAR2(100) :='PINLESSDEBITCARD';
2175
2176 l_batches_tab batchAttrTabType;
2177 l_trxns_in_batch_tab trxnsInBatchTabType;
2178 l_mbatch_ids_out mBatchIdsTab;
2179
2180 l_index NUMBER;
2181
2182 l_cursor_stmt VARCHAR2(1000);
2183 TYPE dyn_batches IS REF CURSOR;
2184 l_batch_cursor dyn_batches;
2185
2186 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
2187 '.insert_batch_status_new';
2188
2189 /*
2190 * Cursor to pick up all existing mbatchids for
2191 * a given (batch id, payee id, profile code)
2192 * combination.
2193 */
2194 -- CURSOR c_mbatch_ids (batch_id IBY_BATCHES_ALL.batchid%TYPE,
2195 -- payee_id IBY_BATCHES_ALL.payeeid%TYPE,
2196 -- strProfiles VARCHAR2
2197 -- )
2198 -- IS
2199 -- SELECT
2200 -- mbatchid
2201 -- FROM
2202 -- IBY_BATCHES_ALL
2203 -- WHERE
2204 -- batchid = batch_id AND
2205 -- payeeid = payee_id AND
2206 -- process_profile_code IN (strProfiles)
2207 -- ;
2208
2209 BEGIN
2210
2211 print_debuginfo(l_module_name, 'ENTER: overloaded API.');
2212
2213 mbatch_ids_out := JTF_NUMBER_TABLE();
2214 batch_ids_out := JTF_VARCHAR2_TABLE_100();
2215
2216 /* Form a comma separated string for the profile codes */
2217 numProfiles := profile_code_array.count;
2218 FOR i IN 1..(numProfiles-1) LOOP
2219 strProfCodes := strProfCodes||''''||profile_code_array(i)||''',';
2220 END LOOP;
2221 /* Append the last profile code without comma at the end */
2222 strProfCodes := strProfCodes||''''||profile_code_array(numProfiles)||'''';
2223 print_debuginfo(l_module_name, 'Comma Separated string of profile codes: '|| strProfCodes);
2224
2225 /*
2226 * Form the dynamic reference cursor to pick up
2227 * all existing mbatchids for a given (batchid,payeeid and
2228 * a string of profile codes)
2229 */
2230 l_cursor_stmt := ' SELECT mbatchid FROM '||
2231 ' IBY_BATCHES_ALL WHERE '||
2232 ' batchid = '''||merch_batchid_in||''' AND '||
2233 ' payeeid = '''||merchant_id_in||''' AND '||
2234 ' process_profile_code IN ('||strProfCodes||') '
2235 ;
2236
2237
2238 /* First check if a row already exists for this batch status */
2239 SELECT
2240 COUNT(*)
2241 INTO
2242 numrows
2243 FROM
2244 IBY_BATCHES_ALL
2245 WHERE
2246 batchid = merch_batchid_in AND
2247 payeeid = merchant_id_in
2248 ;
2249
2250 /*
2251 * If row does not exist, then insert batch status into iby_batches_all
2252 */
2253 IF numrows = 0 THEN
2254
2255 print_debuginfo(l_module_name, 'num rows is zero');
2256
2257 --
2258 -- need to lock trxn summaries table to ensure that
2259 -- trxns which have not been validated do not sneak into
2260 -- the batch; gap between call to validate_open_batch
2261 -- and update of IBY_TRXN_SUMMARIES_ALL has been shown
2262 -- to be vulnerable to race conditions even under moderate
2263 -- concurrency loads
2264 --
2265 LOCK TABLE iby_batches_all, iby_trxn_summaries_all IN EXCLUSIVE MODE;
2266
2267 --SELECT iby_batches_s.NEXTVAL
2268 -- INTO l_mbatchid
2269 --FROM dual;
2270
2271 --mbatchid_out := l_mbatchid;
2272
2273 l_batchcurr := currency_in;-- should be made NULL
2274 l_trxncount := numtrxns_in;
2275
2276 SELECT
2277 NVL(bep_type,iby_bepinfo_pkg.C_BEPTYPE_GATEWAY)
2278 INTO
2279 l_beptype
2280 FROM
2281 IBY_BEPINFO
2282 WHERE
2283 (bepid=vendor_id_in)
2284 ;
2285
2286 print_debuginfo(l_module_name, 'STEP 1');
2287
2288 --
2289 -- if the bep is a processor, then we create a batch
2290 --
2291 IF (
2292 (l_beptype = iby_bepinfo_pkg.C_BEPTYPE_PROCESSOR) AND
2293 (
2294 (req_type_in = iby_transactioncc_pkg.C_REQTYPE_BATCHCLOSE) OR
2295 (req_type_in = iby_transactioncc_pkg.C_REQTYPE_PDC_BATCHCLOSE)
2296 )
2297 ) THEN
2298
2299 print_debuginfo(l_module_name, 'Invoking grouping ..');
2300
2301 /*
2302 * Group all the transactions for this profile into
2303 * batches as per the grouping attributes on the profile.
2304 */
2305 performTransactionGrouping(
2306 profile_code_array,
2307 instr_type_in,
2308 req_type_in,
2309 f_pmt_channel_in,
2310 f_curr_in,
2311 f_settle_date,
2312 f_due_date,
2313 f_maturity_date,
2314 f_instr_type,
2315 l_batches_tab,
2316 l_trxns_in_batch_tab
2317 );
2318
2319 print_debuginfo(l_module_name, '# batches created: '
2320 || l_batches_tab.COUNT);
2321
2322 print_debuginfo(l_module_name, '# transactions processed: '
2323 || l_trxns_in_batch_tab.COUNT);
2324
2325 /*
2326 * After grouping it is possible that multiple batches were
2327 * created. Each batch will be a separate row in the
2328 * IBY_BATCHES_ALL table with a unique mbatchid.
2329 *
2330 * The user may have provided a batch id (batch prefix), we will
2331 * have to assign that batch id to each of the created batches.
2332 *
2333 * This batch id would be sent to the payment system. It therefore
2334 * has to be unique. Therefore, we add a suffix to the user
2335 * provided batch id to ensure that batches created after grouping
2336 * have a unique batch id.
2337 */
2338 IF (l_batches_tab.COUNT > 0) THEN
2339
2340 l_index := 1;
2341 FOR k IN l_batches_tab.FIRST .. l_batches_tab.LAST LOOP
2342
2343 /*
2344 * Assign a unique batch id to each batch.
2345 */
2346 l_batches_tab(k).batch_id :=
2347 merch_batchid_in ||'_'|| l_index;
2348 l_index := l_index + 1;
2349
2350 END LOOP;
2351
2352 END IF;
2353
2354 /*
2355 * After grouping, the transactions will be assigned a mbatch id.
2356 * Assign them a batch id as well (based on the batch id
2357 * corresponding to each mbatch id).
2358 */
2359 IF (l_trxns_in_batch_tab.COUNT > 0) THEN
2360
2361 FOR m IN l_trxns_in_batch_tab.FIRST ..
2362 l_trxns_in_batch_tab.LAST LOOP
2363
2364 FOR k IN l_batches_tab.FIRST .. l_batches_tab.LAST LOOP
2365
2366 /*
2367 * Find the mbatch id in the batches array
2368 * corresponding to the mbatchid of this transaction.
2369 */
2370 IF (l_trxns_in_batch_tab(m).mbatch_id =
2371 l_batches_tab(k).mbatch_id) THEN
2372
2373 /*
2374 * Assign the batch id from the batches array
2375 * to this transaction.
2376 */
2377 l_trxns_in_batch_tab(m).batch_id :=
2378 l_batches_tab(k).batch_id;
2379
2380 END IF;
2381
2382 END LOOP;
2383
2384 END LOOP;
2385
2386 END IF;
2387
2388 /*
2389 * BEP and vendor related params.
2390 */
2391 print_debuginfo(l_module_name, 'vendor_id_in: '
2392 || vendor_id_in);
2393 /* need to change since mulitiple keys could be present here*/
2394 print_debuginfo(l_module_name, 'vendor_key_in: '
2395 || vendor_key_in);
2396 print_debuginfo(l_module_name, 'merchant_id_in: '
2397 || merchant_id_in);
2398 print_debuginfo(l_module_name, 'req_type_in: '
2399 || req_type_in);
2400
2401 --
2402 -- associate all trxns in the current open batch
2403 -- with the batch id of the batch close
2404 --
2405 IF (l_trxns_in_batch_tab.COUNT <> 0) THEN
2406
2407 FOR i IN l_trxns_in_batch_tab.FIRST ..
2408 l_trxns_in_batch_tab.LAST LOOP
2409
2410 /*
2411 * This SQL statement has been replaced by
2412 * the SQL update statement (below). It is kept
2413 * here for documentation purposes.
2414 */
2415 /*------------------------------------------
2416 UPDATE
2417 IBY_TRXN_SUMMARIES_ALL
2418 SET
2419 status = iby_transactioncc_pkg.C_STATUS_BATCH_PENDING,
2420 batchid = merch_batchid_in
2421 || '_' || i,
2422 mbatchid = l_trxns_in_batch_tab(i).mbatch_id,
2423 last_update_date = sysdate,
2424 updatedate = sysdate,
2425 last_updated_by = fnd_global.user_id,
2426 object_version_number = object_version_number + 1
2427 WHERE
2428 (bepid = vendor_id_in) AND
2429 (bepkey = vendor_key_in) AND
2430 (payeeid = merchant_id_in) AND
2431 (status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED)i
2432 AND
2433 (
2434 (instrtype IN
2435 (iby_creditcard_pkg.C_INSTRTYPE_CCARD,
2436 iby_creditcard_pkg.C_INSTRTYPE_PCARD) AND
2437 (req_type_in =
2438 iby_transactioncc_pkg.C_REQTYPE_BATCHCLOSE))
2439 OR
2440 (instrtype IN (l_pinlessdebitcard) AND
2441 (req_type_in = iby_transactioncc_pkg.
2442 C_REQTYPE_PDC_BATCHCLOSE)
2443 )
2444 OR
2445 instrtype IS NULL
2446 )
2447 AND
2448 (batchid IS NULL);
2449 -------------------------------------------*/
2450
2451 print_debuginfo(l_module_name, 'Going to update '
2452 || 'transaction ' || l_trxns_in_batch_tab(i).trxn_id);
2453
2454 UPDATE
2455 IBY_TRXN_SUMMARIES_ALL
2456 SET
2457 status = iby_transactioncc_pkg.
2458 C_STATUS_BATCH_PENDING,
2459 batchid = l_trxns_in_batch_tab(i).
2460 batch_id,
2461 mbatchid = l_trxns_in_batch_tab(i).
2462 mbatch_id,
2463 last_update_date = sysdate,
2464 updatedate = sysdate,
2465 last_updated_by = fnd_global.user_id,
2466 object_version_number = object_version_number + 1
2467 WHERE
2468 transactionid = l_trxns_in_batch_tab(i).trxn_id AND
2469 status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED
2470 ;
2471
2472 print_debuginfo(l_module_name, 'Finished updating '
2473 || 'transaction'
2474 || l_trxns_in_batch_tab(i).trxn_id
2475 );
2476
2477 END LOOP;
2478
2479 END IF; -- if trxn count <> 0
2480
2481 END IF; -- if bep type = PROCESSOR
2482
2483 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
2484
2485 IF (l_batches_tab.COUNT <> 0) THEN
2486
2487 FOR i IN l_batches_tab.FIRST .. l_batches_tab.LAST LOOP
2488
2489 print_debuginfo(l_module_name, 'Going to insert batch '
2490 || l_batches_tab(i).mbatch_id);
2491
2492 INSERT INTO
2493 iby_batches_all
2494 (
2495 MBATCHID,
2496 BATCHID,
2497 MPAYEEID,
2498 PAYEEID,
2499 BEPID,
2500 BEPKEY,
2501 PAYMENTMETHODNAME,
2502 BATCHSTATUS,
2503 BATCHCLOSEDATE,
2504 VPSBATCHID,
2505 CURRENCYNAMECODE,
2506 NUMTRXNS,
2507 BATCHSTATEID,
2508 BATCHTOTAL,
2509 BATCHSALES,
2510 BATCHCREDIT,
2511 GWBATCHID,
2512 BEPCODE,
2513 BEPMESSAGE,
2514 ERRORLOCATION,
2515 TERMINALID,
2516 ACQUIRER,
2517 REQTYPE,
2518 REQDATE,
2519 PROCESS_PROFILE_CODE,
2520 INSTRUMENT_TYPE,
2521 BR_DISPUTED_FLAG,
2522 LAST_UPDATE_DATE,
2523 LAST_UPDATED_BY,
2524 CREATION_DATE,
2525 CREATED_BY,
2526 LAST_UPDATE_LOGIN,
2527 OBJECT_VERSION_NUMBER,
2528 PAYEEINSTRID,
2529 LEGAL_ENTITY_ID,
2530 ORG_ID,
2531 ORG_TYPE,
2532 SETTLEDATE
2533 )
2534 VALUES
2535 (
2536 l_batches_tab(i).mbatch_id,
2537 merch_batchid_in || '_' || i,
2538 l_mpayeeid,
2539 merchant_id_in,
2540 vendor_id_in,
2541 l_batches_tab(i).bep_key,-- should be made NULL
2542 pmt_type_in,
2543 status_in,
2544 time_in,
2545 viby_batchid_in,
2546 l_batches_tab(i).curr_code,-- should be made NULL
2547 0,
2548 batchstate_in,
2549 batchtotal_in,
2550 saleamount_in,
2551 cramount_in,
2552 gwid_in,
2553 vendor_code_in,
2554 vendor_message_in,
2555 error_location_in,
2556 terminal_id_in,
2557 Acquirer_id_in,
2558 req_type_in,
2559 sysdate,
2560 -- l_batches_tab(i).profile_code,-- should be made NULL
2561 profile_code_array(1),
2562 instr_type_in,
2563 br_disputed_flag_in,
2564 sysdate,
2565 fnd_global.user_id,
2566 sysdate,
2567 fnd_global.user_id,
2568 fnd_global.login_id,
2569 1,
2570 l_batches_tab(i).int_bank_acct_id,
2571 l_batches_tab(i).le_id,
2572 l_batches_tab(i).org_id,
2573 l_batches_tab(i).org_type,
2574 l_batches_tab(i).settle_date
2575 );
2576
2577 print_debuginfo(l_module_name, 'finished insert '
2578 || 'for batch id '
2579 || l_batches_tab(i).mbatch_id
2580 );
2581
2582 validate_open_batch(
2583 vendor_id_in,
2584 l_batches_tab(i).mbatch_id,
2585 sec_key_present_in,
2586 l_trxncount,
2587 l_batchcurr);
2588
2589 UPDATE
2590 IBY_BATCHES_ALL
2591 SET
2592 currencynamecode = l_batchcurr,-- should be made NULL
2593 numtrxns = l_trxncount
2594 WHERE
2595 mbatchid = l_batches_tab(i).mbatch_id
2596 ;
2597
2598 /*
2599 * Store the created mbatchids in the output param
2600 * to return to the caller.
2601 */
2602 mbatch_ids_out.EXTEND;
2603 mbatch_ids_out(i) := l_batches_tab(i).mbatch_id;
2604
2605 /*
2606 * Store the created batchids in the output param
2607 * to return to the caller.
2608 */
2609 batch_ids_out.EXTEND;
2610 batch_ids_out(i) := l_batches_tab(i).batch_id;
2611
2612 END LOOP;
2613
2614 END IF; -- if l_batches_tab.COUNT <> 0
2615
2616 ELSIF (numrows = 1) THEN
2617
2618 l_trxncount := numtrxns_in;
2619
2620 IF (l_trxncount<1) THEN
2621 l_trxncount := NULL;
2622 END IF;
2623
2624 /* One previous transaction, so update previous row */
2625 UPDATE
2626 IBY_BATCHES_ALL
2627 SET
2628 PAYMENTMETHODNAME = pmt_type_in,
2629 BATCHSTATUS = status_in,
2630 BATCHCLOSEDATE = time_in,
2631 CURRENCYNAMECODE = NVL(currency_in,CURRENCYNAMECODE),-- should be made NULL
2632 NUMTRXNS = NVL(l_trxncount,NUMTRXNS),
2633 BATCHSTATEID = batchstate_in,
2634 BATCHTOTAL = batchtotal_in,
2635 BATCHSALES = saleamount_in,
2636 BATCHCREDIT = cramount_in,
2637 GWBATCHID = gwid_in,
2638 BEPCODE = vendor_code_in,
2639 BEPMESSAGE = vendor_message_in,
2640 ERRORLOCATION = error_location_in,
2641 LAST_UPDATE_DATE = sysdate,
2642 LAST_UPDATED_BY = fnd_global.user_id,
2643
2644 -- Do not update creation timestamp
2645 -- when updating records: Bug 3128675
2646 --CREATION_DATE = sysdate,
2647 --CREATED_BY = fnd_global.user_id,
2648
2649 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
2650 WHERE
2651 batchid = merch_batchid_in AND
2652 payeeid = merchant_id_in
2653 ;
2654
2655 IF ((req_type_in = 'ORAPMTCLOSEBATCH') OR
2656 (req_type_in = 'ORAPMTPDCCLOSEBATCH') ) THEN
2657
2658 -- we don't update the following for querybatch
2659 UPDATE
2660 iby_batches_all
2661 SET
2662 VPSBATCHID = viby_batchid_in,
2663 reqtype = req_type_in,
2664 reqdate = sysdate
2665 WHERE
2666 batchid = merch_batchid_in AND
2667 payeeid = merchant_id_in;
2668
2669 END IF;
2670
2671 --SELECT mbatchid
2672 --INTO mbatchid_out
2673 --FROM iby_batches_all
2674 --WHERE batchid = merch_batchid_in
2675 --AND payeeid = merchant_id_in;
2676
2677 /*
2678 * Pick up all mbatchids for the given (batch id, merchant id,
2679 * account profile) combination.
2680 *
2681 * Since this is a retry, and retry is only applicable to a
2682 * specific batch, we should be getting only one mbatchid.
2683 */
2684 -- OPEN c_mbatch_ids (merch_batchid_in, merchant_id_in, strProfCodes);
2685 -- FETCH c_mbatch_ids BULK COLLECT INTO l_mbatch_ids_out;
2686 -- CLOSE c_mbatch_ids;
2687
2688 OPEN l_batch_cursor FOR l_cursor_stmt;
2689 FETCH l_batch_cursor BULK COLLECT INTO l_mbatch_ids_out;
2690 CLOSE l_batch_cursor;
2691
2692 IF (l_mbatch_ids_out.COUNT <> 0) THEN
2693
2694 FOR i IN l_mbatch_ids_out.FIRST .. l_mbatch_ids_out.LAST LOOP
2695
2696 /*
2697 * In the retry scenario, the user will provide
2698 * the batch id to retry explicitly. So in the
2699 * retry case, we will returning only one batch id
2700 * and one mbatch id.
2701 */
2702
2703 mbatch_ids_out.EXTEND;
2704 mbatch_ids_out(i) := l_mbatch_ids_out(i);
2705
2706 batch_ids_out.EXTEND;
2707 batch_ids_out(i) := merch_batchid_in;
2708
2709 END LOOP;
2710
2711 END IF;
2712
2713 ELSE
2714
2715 -- will never run into this block
2716 -- More than one pending transaction, which is an
2717 -- error
2718 raise_application_error(-20000, 'IBY_20422#', FALSE);
2719
2720 END IF;
2721
2722 print_debuginfo(l_module_name, 'mbatchids out count: '
2723 || mbatch_ids_out.COUNT);
2724
2725 COMMIT;
2726
2727 EXCEPTION
2728
2729 WHEN OTHERS THEN
2730 ROLLBACK;
2731 --
2732 -- rethrow any internally generated exception
2733 --
2734 --raise_application_error(SQLCODE, SQLERRM, FALSE);
2735 RAISE;
2736
2737 END insert_batch_status_new;
2738
2739
2740
2741 /*--------------------------------------------------------------------
2742 | NAME:
2743 | performTransactionGrouping
2744 |
2745 | PURPOSE:
2746 |
2747 |
2748 |
2749 | PARAMETERS:
2750 | IN
2751 |
2752 |
2753 | OUT
2754 |
2755 |
2756 | RETURNS:
2757 |
2758 | NOTES:
2759 |
2760 *---------------------------------------------------------------------*/
2761 PROCEDURE performTransactionGrouping(
2762 p_profile_code IN IBY_FNDCPT_USER_CC_PF_B.
2763 user_cc_profile_code%TYPE,
2764 instr_type IN IBY_TRXN_SUMMARIES_ALL.
2765 instrtype%TYPE,
2766 req_type IN IBY_BATCHES_ALL.
2767 reqtype%TYPE,
2768 f_pmt_channel_in IN IBY_TRXN_SUMMARIES_ALL.
2769 payment_channel_code%TYPE,
2770 f_curr_in IN IBY_TRXN_SUMMARIES_ALL.
2771 currencynamecode%TYPE,
2772 f_settle_date IN IBY_TRXN_SUMMARIES_ALL.
2773 settledate%TYPE,
2774 f_due_date IN IBY_TRXN_SUMMARIES_ALL.
2775 settlement_due_date%TYPE,
2776 f_maturity_date IN IBY_TRXN_SUMMARIES_ALL.
2777 br_maturity_date%TYPE,
2778 f_instr_type IN IBY_TRXN_SUMMARIES_ALL.
2779 instrtype%TYPE,
2780 x_batchTab IN OUT NOCOPY IBY_TRANSACTIONCC_PKG.
2781 batchAttrTabType,
2782 x_trxnsInBatchTab IN OUT NOCOPY IBY_TRANSACTIONCC_PKG.
2783 trxnsInBatchTabType
2784 )
2785 IS
2786 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
2787 '.performTransactionGrouping';
2788
2789 l_sql_str VARCHAR2(5000);
2790 l_cursor_stmt VARCHAR2(8000);
2791
2792 l_first_record VARCHAR2(1) := 'Y';
2793
2794 /* user defined grouping rule flags */
2795 l_org_flag VARCHAR2(1) := 'N';
2796 l_le_flag VARCHAR2(1) := 'N';
2797 l_int_bnk_flag VARCHAR2(1) := 'N';
2798 l_curr_flag VARCHAR2(1) := 'N';
2799 l_settle_date_flag VARCHAR2(1) := 'N';
2800
2801 /* user defined limits */
2802 l_max_trxn_limit NUMBER(15) := 0;
2803 l_fx_rate_type VARCHAR2(255) := '';
2804 l_fx_curr_code VARCHAR2(10) := '';
2805 l_max_amount_limit NUMBER(15) := 0;
2806
2807 /*
2808 * NOTE:
2809 *
2810 * IBY_BATCHES_ALL.batchid = user generated batch id
2811 * IBY_BATCHES_ALL.mbatchid = system generated batch id
2812 *
2813 * If batch close is invoked by the user, the batchid will
2814 * be a user defined string (should be unique).
2815 *
2816 * If batch close is invoked by the scheduler, the batch is
2817 * be a sequence number (iby_batchid_s.nextval).
2818 *
2819 * mbatchid will always be a sequence number (iby_batchid_s.nextval).
2820 *
2821 * In the new architecture, multiple mbatchids can be generated
2822 * for a single batchid (based on user defined grouping rules).
2823 */
2824 l_mbatch_id IBY_BATCHES_ALL.mbatchid%TYPE;
2825 l_batch_total NUMBER(15) := 0;
2826 l_trxns_in_batch_count NUMBER(15) := 0;
2827
2828 l_trx_fx_amount NUMBER(15) := 0;
2829
2830 /*
2831 * Used to substitute null values in date comparisons.
2832 * It is assumed that not document payable would ever
2833 * have a year 1100 date.
2834 */
2835 l_impossible_date DATE := TO_DATE('01/01/1100 10:25:55',
2836 'MM/DD/YYYY HH24:MI:SS');
2837
2838 /*
2839 * These two are related data structures. Each row in batchAttrTabType
2840 * PLSQL table is used in inserting a row into the IBY_BATCHES_ALL
2841 * table.
2842 *
2843 * A separate data structure is needed to keep track of the transactions
2844 * that are part of a batch. This information is tracked in the
2845 * trxnsInBatchTabType table. The rows in trxnsInBatchTabType are
2846 * used to update the rows in IBY_TRXN_SUMMARIES_ALL table with
2847 * batch ids.
2848 *
2849 * l_batchTab l_trxnsInBatchTab
2850 * (insert into IBY_BATCHES_ALL) (update IBY_TRXN_SUMMARIES_ALL)
2851 * /-------------------------------------\ /------------\
2852 * |MBatch |Profile|..|Curr |Org |..| |MBatch |Trx |
2853 * |Id |Code |..|Code |Id |..| |Id |Id |
2854 * | | |..| | |..| | | |
2855 * |-------------------------------------| |------------|
2856 * | 4000| 10| | USD| 204| | | 4000| 501|
2857 * | | | | | | | | 4000| 504|
2858 * | | | | | | | | 4000| 505|
2859 * |-------|-------|--|-------|-------|--| |-------|----|
2860 * | 4001| 11| | -- | 342| | | 4001| 502|
2861 * | | | | | | | | 4001| 509|
2862 * | | | | | | | | 4001| 511|
2863 * | | | | | | | | 4001| 523|
2864 * | | | | | | | | : | : |
2865 * |-------|-------|--|-------|-------|--| |-------|----|
2866 * | : | : | | : | : | | | : | : |
2867 * \_______|_______|__|_______|_______|__/ \_______|____/
2868 *
2869 */
2870
2871 l_batchRec IBY_TRANSACTIONCC_PKG.batchAttrRecType;
2872 l_trxnsInBatchTab IBY_TRANSACTIONCC_PKG.trxnsInBatchTabType;
2873
2874 l_trxnsInBatchRec IBY_TRANSACTIONCC_PKG.trxnsInBatchRecType;
2875 l_batchTab IBY_TRANSACTIONCC_PKG.batchAttrTabType;
2876
2877 l_trxnGrpCriTab IBY_TRANSACTIONCC_PKG.trxnGroupCriteriaTabType;
2878
2879 l_pinlessdebitcard CONSTANT VARCHAR2(100) :='PINLESSDEBITCARD';
2880 l_bankaccount CONSTANT VARCHAR2(100) :='BANKACCOUNT';
2881
2882 /* previous transaction attributes */
2883 prev_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
2884 prev_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
2885 prev_trxn_amount iby_trxn_summaries_all.amount%TYPE;
2886 prev_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
2887 prev_org_id iby_trxn_summaries_all.org_id%TYPE;
2888 prev_org_type iby_trxn_summaries_all.org_type%TYPE;
2889 prev_settle_date iby_trxn_summaries_all.settledate%TYPE;
2890 prev_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
2891 prev_bep_key iby_trxn_summaries_all.bepkey%TYPE;
2892 prev_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
2893
2894 /* current transaction attributes */
2895 curr_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
2896 curr_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
2897 curr_trxn_amount iby_trxn_summaries_all.amount%TYPE;
2898 curr_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
2899 curr_org_id iby_trxn_summaries_all.org_id%TYPE;
2900 curr_org_type iby_trxn_summaries_all.org_type%TYPE;
2901 curr_settle_date iby_trxn_summaries_all.settledate%TYPE;
2902 curr_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
2903 curr_bep_key iby_trxn_summaries_all.bepkey%TYPE;
2904 curr_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
2905 l_user_pf_table_name VARCHAR2(100);
2906 l_sys_pf_table_name VARCHAR2(100);
2907 l_user_pf_column_name VARCHAR2(100);
2908 l_sys_pf_column_name VARCHAR2(100);
2909
2910 l_numeric_char_mask VARCHAR2(100);
2911
2912 TYPE dyn_transactions IS REF CURSOR;
2913 l_trxn_cursor dyn_transactions;
2914
2915 /*
2916 * This cursor up will pick up all valid transactions for
2917 * the specified payment profile. The select statement will
2918 * order the transactions based on grouping criteria.
2919 *
2920 * Important Note:
2921 *
2922 * Always ensure that there is a corresponding order by
2923 * clause for each grouping criterion that you wish to use.
2924 * This is required in order to create minimum possible
2925 * batches from a given set of transactions.
2926 *
2927 * Note 2: The sample sql is not right as the base table for
2928 * process profile is different
2929 * the dynamic sql is changed according to that
2930 */
2931 CURSOR c_transactions (
2932 p_profile_code VARCHAR2,
2933 p_instr_type VARCHAR2,
2934 p_req_type VARCHAR2
2935 )
2936 IS
2937 SELECT
2938 txn.transactionid,
2939 txn.process_profile_code,
2940 txn.bepkey,
2941 txn.org_id,
2942 txn.org_type,
2943 txn.currencynamecode,
2944 txn.amount,
2945 txn.legal_entity_id,
2946 txn.payeeinstrid,
2947 txn.settledate,
2948 sys_prof.group_by_org,
2949 sys_prof.group_by_legal_entity,
2950 sys_prof.group_by_int_bank_account,
2951 sys_prof.group_by_settlement_curr,
2952 sys_prof.group_by_settlement_date,
2953 sys_prof.limit_by_amt_curr,
2954 sys_prof.limit_by_exch_rate_type,
2955 sys_prof.limit_by_total_amt,
2956 sys_prof.limit_by_settlement_num
2957 FROM
2958 IBY_TRXN_SUMMARIES_ALL txn,
2959 IBY_FNDCPT_USER_CC_PF_B user_prof,
2960 IBY_FNDCPT_SYS_CC_PF_B sys_prof
2961 WHERE
2962 user_prof.user_cc_profile_code = p_profile_code AND
2963 txn.process_profile_code = user_prof.user_cc_profile_code AND
2964 sys_prof.sys_cc_profile_code = user_prof.sys_cc_profile_code AND
2965 txn.status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED AND
2966 (
2967 /*
2968 * This clause will pick up credit card / purchase card
2969 * transactions.
2970 */
2971 (
2972 p_instr_type IN
2973 (
2974 iby_creditcard_pkg.C_INSTRTYPE_CCARD,
2975 iby_creditcard_pkg.C_INSTRTYPE_PCARD
2976 )
2977 AND
2978 (
2979 txn.reqtype IN
2980 (
2981 iby_transactioncc_pkg.C_REQTYPE_BATCHCLOSE,
2982 iby_transactioncc_pkg.C_REQTYPE_CAPTURE,
2983 iby_transactioncc_pkg.C_REQTYPE_CREDIT,
2984 iby_transactioncc_pkg.C_REQTYPE_RETURN
2985 )
2986 )
2987 AND
2988 (
2989 txn.instrtype IN
2990 (
2991 iby_creditcard_pkg.C_INSTRTYPE_CCARD,
2992 iby_creditcard_pkg.C_INSTRTYPE_PCARD
2993 )
2994 )
2995 )
2996
2997 /*
2998 * This clause will pick up pinless debit card
2999 * transactions.
3000 */
3001 OR
3002 (
3003 p_instr_type IN
3004 (
3005 l_pinlessdebitcard
3006 )
3007 AND
3008 (
3009 txn.reqtype IN
3010 (
3011 iby_transactioncc_pkg.C_REQTYPE_PDC_BATCHCLOSE,
3012 iby_transactioncc_pkg.C_REQTYPE_REQUEST
3013 )
3014 )
3015 AND
3016 (
3017 txn.instrtype IN
3018 (
3019 l_pinlessdebitcard
3020 )
3021 )
3022 )
3023
3024 /*
3025 * This clause will pick up bank account transactions
3026 * transactions.
3027 */
3028 OR
3029 (
3030 p_instr_type IN
3031 (
3032 l_bankaccount
3033 )
3034 AND
3035 (
3036 txn.reqtype IN
3037 (
3038 iby_transactioncc_pkg.C_REQTYPE_EFT_BATCHCLOSE,
3039 iby_transactioncc_pkg.C_REQTYPE_BATCHREQ
3040 )
3041 )
3042 AND
3043 (
3044 txn.instrtype IN
3045 (
3046 l_bankaccount
3047 )
3048 )
3049
3050 /*
3051 * Fix for bug 5442922:
3052 *
3053 * For bank account instruments, the auth / verify
3054 * transaction will have trantypeid 20; The
3055 * capture transaction will have trxntypeid 100.
3056 *
3057 * Since we are picking up only capture transactions
3058 * here, explicitly specify the trxntypeid in the
3059 * WHERE clause. Otherwise, auths are also picked
3060 * up and put into the batch.
3061 */
3062 AND
3063 (
3064 /*
3065 * This trxn type 100 maps to
3066 * IBY_FNDCPT_TRXN_PUB.BA_CAPTURE_TRXNTYPE
3067 */
3068 txn.trxntypeid = 100
3069 )
3070 )
3071
3072 /*
3073 * This clause will pick up any transaction which does not
3074 * have an instrument type. This looks dangerous to me but
3075 * kept for backward compatibility - Ramesh
3076 */
3077 OR
3078 (
3079 txn.instrtype IS NULL
3080 )
3081 ) AND
3082 txn.batchid IS NULL AND
3083 /*
3084 * Fix for bug 5632947:
3085 *
3086 * Join with CE_SECURITY_PROFILES_V for MOAC compliance.
3087 */
3088 ((txn.org_id IS NULL) OR
3089 ((txn.org_id IS NOT NULL) AND
3090 (txn.org_id, txn.org_type) IN
3091 (SELECT
3092 ce.organization_id,
3093 ce.organization_type
3094 FROM
3095 ce_security_profiles_v ce
3096 )))
3097 ORDER BY
3098 txn.process_profile_code, --
3099 txn.bepkey, -- Ensure that the
3100 txn.org_id, -- grouping rules below
3101 txn.org_type, -- follow this same
3102 txn.legal_entity_id, -- order (necessary
3103 txn.payeeinstrid, -- for creating minimum
3104 txn.currencynamecode, -- number of batches)
3105 txn.settledate --
3106 ;
3107
3108
3109 BEGIN
3110
3111 print_debuginfo(l_module_name, 'ENTER');
3112
3113 print_debuginfo(l_module_name, 'Payment Profile Cd: '||
3114 p_profile_code);
3115 print_debuginfo(l_module_name, 'Instrument Type: ' ||
3116 instr_type);
3117 print_debuginfo(l_module_name, 'Request Type: ' ||
3118 req_type);
3119
3120 /*
3121 * Filter params.
3122 */
3123 print_debuginfo(l_module_name, 'f_pmt_channel_in: '
3124 || f_pmt_channel_in);
3125 print_debuginfo(l_module_name, 'f_curr_in: '
3126 || f_curr_in);
3127 print_debuginfo(l_module_name, 'f_settle_date: '
3128 || f_settle_date);
3129 print_debuginfo(l_module_name, 'f_due_date: '
3130 || f_due_date);
3131 print_debuginfo(l_module_name, 'f_maturity_date: '
3132 || f_maturity_date);
3133 print_debuginfo(l_module_name, 'f_instr_type: '
3134 || f_instr_type);
3135
3136 /*
3137 * Fix for bug 5407120:
3138 *
3139 * Before we do anything, alter the session to set the numeric
3140 * character mask. This is because of XML publisher limitation -
3141 * it cannot handle numbers like '230,56' which is the European
3142 * representation of '230.56'.
3143 *
3144 * Therefore, we explicitly set the numeric character mask at the
3145 * beginning of this routine and revert back to the default
3146 * setting at the end of this method.
3147 */
3148 BEGIN
3149
3150 SELECT
3151 value
3152 INTO
3153 l_numeric_char_mask
3154 FROM
3155 V$NLS_PARAMETERS
3156 WHERE
3157 parameter='NLS_NUMERIC_CHARACTERS'
3158 ;
3159
3160 print_debuginfo(l_module_name, 'Current numeric char mask: '
3161 || l_numeric_char_mask
3162 );
3163
3164 EXCEPTION
3165 WHEN OTHERS THEN
3166 print_debuginfo(l_module_name, 'Non-Fatal: Exception occured '
3167 || 'when attempting to retrieve numeric character mask.'
3168 );
3169
3170 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
3171 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
3172 END;
3173
3174 /*
3175 * Now alter the session, to force the NLS numeric character
3176 * decimal indicator to be a '.'.
3177 */
3178 BEGIN
3179
3180 EXECUTE IMMEDIATE
3181 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ".,"'
3182 ;
3183
3184 EXCEPTION
3185 WHEN OTHERS THEN
3186 print_debuginfo(l_module_name, 'Non-Fatal: Exception occured '
3187 || 'when attempting to later session to set '
3188 || 'numeric character mask.'
3189 );
3190
3191 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
3192 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
3193 END;
3194
3195 /*
3196 * Dynamically form SQL string to limit the rows that
3197 * are picked up for the batch close operation.
3198 *
3199 * Use the supplied filter parameters to form the SQL
3200 * string.
3201 */
3202 IF (f_pmt_channel_in IS NOT NULL) THEN
3203 l_sql_str:= l_sql_str||'AND nvl(payment_channel_code'''||f_pmt_channel_in||''') = '
3204 || '''' || f_pmt_channel_in || '''';
3205 END IF;
3206
3207 IF (f_curr_in IS NOT NULL) THEN
3208 l_sql_str := l_sql_str || ' AND currencynamecode = '
3209 || '''' || f_curr_in || '''';
3210 END IF;
3211
3212 IF (f_settle_date IS NOT NULL) THEN
3213 l_sql_str := l_sql_str || ' AND nvl(settledate,'''||f_settle_date||''') <= '
3214 || '''' || f_settle_date || '''';
3215 END IF;
3216
3217 IF (f_due_date IS NOT NULL) THEN
3218 l_sql_str := l_sql_str || ' AND settlement_due_date <= '
3219 || '''' || f_due_date || '''';
3220 END IF;
3221
3222 IF (f_maturity_date IS NOT NULL) THEN
3223 l_sql_str := l_sql_str || ' AND br_maturity_date <= '
3224 || '''' || f_maturity_date || '''';
3225 END IF;
3226
3227 IF (f_instr_type IS NOT NULL) THEN
3228 l_sql_str := l_sql_str || ' AND nvl(instrtype,'''||f_instr_type||''') = '
3229 || '''' || f_instr_type || '''';
3230 END IF;
3231
3232 print_debuginfo(l_module_name, 'Dynamic SQL snippet: '
3233 || l_sql_str);
3234
3235 /* determine the process profile table and column */
3236
3237 l_user_pf_table_name :='IBY_FNDCPT_USER_CC_PF_B';
3238 l_sys_pf_table_name :='IBY_FNDCPT_SYS_CC_PF_B';
3239 l_user_pf_column_name :='USER_CC_PROFILE_CODE';
3240 l_sys_pf_column_name :='SYS_CC_PROFILE_CODE';
3241 IF (instr_type IS NOT NULL) THEN
3242 if(instr_type =l_bankaccount) THEN
3243 l_user_pf_table_name :='IBY_FNDCPT_USER_EFT_PF_B';
3244 l_sys_pf_table_name :='IBY_FNDCPT_SYS_EFT_PF_B';
3245 l_user_pf_column_name :='USER_EFT_PROFILE_CODE';
3246 l_sys_pf_column_name :='SYS_EFT_PROFILE_CODE';
3247 ELSIF (instr_type =l_pinlessdebitcard) THEN
3248 l_user_pf_table_name :='IBY_FNDCPT_USER_DC_PF_B';
3249 l_sys_pf_table_name :='IBY_FNDCPT_SYS_DC_PF_B';
3250 l_user_pf_column_name :='USER_DC_PROFILE_CODE';
3251 l_sys_pf_column_name :='SYS_DC_PROFILE_CODE';
3252 END IF;
3253
3254 END IF;
3255 /*
3256 * The cursor below is the same as the cursor c_transactions
3257 * defined at the beginning of this method.
3258 *
3259 * We cannot directly use c_transactions because we need to use
3260 * the provided filter params to form a dynamic where clause.
3261 *
3262 * For this reason, this cursor has been made into a dynamic cursor.
3263 * c_transactions is kept for documentation / debugging purposes
3264 * but is not used.
3265 */
3266 l_cursor_stmt :=
3267 'SELECT '
3268 || 'txn.transactionid, '
3269 || 'txn.process_profile_code, '
3270 || 'txn.bepkey, '
3271 || 'txn.org_id, '
3272 || 'txn.org_type, '
3273 || 'txn.currencynamecode, '
3274 || 'txn.amount, '
3275 || 'txn.legal_entity_id, '
3276 || 'txn.payeeinstrid, '
3277 || 'txn.settledate, '
3278 || 'sys_prof.group_by_org, '
3279 || 'sys_prof.group_by_legal_entity, '
3280 || 'sys_prof.group_by_int_bank_account, '
3281 || 'sys_prof.group_by_settlement_curr, '
3282 || 'sys_prof.group_by_settlement_date, '
3283 || 'sys_prof.limit_by_amt_curr, '
3284 || 'sys_prof.limit_by_exch_rate_type, '
3285 || 'sys_prof.limit_by_total_amt, '
3286 || 'sys_prof.limit_by_settlement_num '
3287 || 'FROM '
3288 || 'IBY_TRXN_SUMMARIES_ALL txn, '
3289 || l_user_pf_table_name || ' user_prof, '
3290 || l_sys_pf_table_name || ' sys_prof '
3291 || 'WHERE '
3292 || 'user_prof.'||l_user_pf_column_name||' = :profile_code AND '
3293 || 'txn.process_profile_code = user_prof.'||l_user_pf_column_name||' AND '
3294 || 'sys_prof.' ||l_sys_pf_column_name||' = user_prof. '||l_sys_pf_column_name || ' AND '
3295 || 'txn.status = :open_batch AND '
3296 || '( '
3297 /*
3298 * This clause will pick up credit card / purchase card
3299 * transactions.
3300 */
3301 || '( '
3302 || ':A IN (:C1, :C2) AND (txn.reqtype IN (:T1A, :T1B, :T1C, :T1D)) AND '
3303 || '(txn.instrtype IN (:C3, :C4)) '
3304 || ') '
3305
3306 /*
3307 * This clause will pick up pinless debit card
3308 * transactions.
3309 */
3310 || 'OR '
3311 || '( '
3312 || ':C IN (:P1) AND (txn.reqtype IN (:T2A, :T2B)) AND '
3313 || '(txn.instrtype IN (:P2)) '
3314 || ') '
3315
3316 /*
3317 * This clause will pick up bank account transactions
3318 * transactions.
3319 */
3320
3321 || 'OR '
3322 || '( '
3323 || ':E IN (:B1) AND (txn.reqtype IN (:T3A, :T3B, :T3C)) AND '
3324 || '(txn.instrtype IN (:B2)) '
3325
3326
3327 /*
3328 * Fix for bug 5442922:
3329 *
3330 * For bank account instruments, the auth / verify
3331 * transaction will have trantypeid 20; The
3332 * capture transaction will have trxntypeid 100.
3333 *
3334 * Since we are picking up only capture transactions
3335 * here, explicitly specify the trxntypeid in the
3336 * WHERE clause. Otherwise, auths are also picked
3337 * up and put into the batch.
3338 */
3339 || 'AND '
3340 || '( '
3341 /*
3342 * This trxn type 100 maps to
3343 * IBY_FNDCPT_TRXN_PUB.BA_CAPTURE_TRXNTYPE
3344 */
3345 || 'txn.trxntypeid = 100 '
3346 || ') '
3347
3348 || ') '
3349
3350 /*
3351 * This clause will pick up any transaction which does not
3352 * have an instrument type. This looks dangerous to me but
3353 * kept for backward compatibility - Ramesh
3354 */
3355 || 'OR '
3356 || '( '
3357 || 'txn.instrtype IS NULL '
3358 || ') '
3359 || ') AND '
3360 || 'txn.batchid IS NULL AND '
3361 /*
3362 * Fix for bug 5632947:
3363 *
3364 * Join with CE_SECURITY_PROFILES_V for MOAC compliance.
3365 */
3366 || '((txn.org_id IS NULL) OR '
3367 || '((txn.org_id IS NOT NULL) AND '
3368 || '(txn.org_id, txn.org_type) IN '
3369 || ' (SELECT '
3370 || ' ce.organization_id, '
3371 || ' ce.organization_type '
3372 || ' FROM '
3373 || ' ce_security_profiles_v ce '
3374 || ' ))) '
3375 || NVL (l_sql_str, 'AND 1=1 ')
3376 || 'ORDER BY '
3377 || 'txn.process_profile_code, ' --
3378 || 'txn.bepkey, ' -- Ensure that the
3379 || 'txn.org_id, ' -- grouping rules below
3380 || 'txn.org_type, ' -- follow this same
3381 || 'txn.legal_entity_id, ' -- order (necessary
3382 || 'txn.payeeinstrid, ' -- for creating minimum
3383 || 'txn.currencynamecode, ' -- number of batches)
3384 || 'txn.settledate ' --
3385 ;
3386
3387 OPEN l_trxn_cursor FOR
3388 l_cursor_stmt
3389 USING
3390 p_profile_code, /* profile_code */
3391 iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED, /* open_batch */
3392 instr_type, /* A */
3393 iby_creditcard_pkg.C_INSTRTYPE_CCARD, /* C1 */
3394 iby_creditcard_pkg.C_INSTRTYPE_PCARD, /* C2 */
3395 iby_transactioncc_pkg.C_REQTYPE_BATCHCLOSE, /* T1A */
3396 iby_transactioncc_pkg.C_REQTYPE_CAPTURE, /* T1B */
3397 iby_transactioncc_pkg.C_REQTYPE_CREDIT, /* T1C */
3398 iby_transactioncc_pkg.C_REQTYPE_RETURN, /* T1D */
3399 iby_creditcard_pkg.C_INSTRTYPE_CCARD, /* C3 */
3400 iby_creditcard_pkg.C_INSTRTYPE_PCARD, /* C4 */
3401 instr_type, /* C */
3402 l_pinlessdebitcard, /* P1 */
3403 iby_transactioncc_pkg.C_REQTYPE_PDC_BATCHCLOSE, /* T2A */
3404 iby_transactioncc_pkg.C_REQTYPE_REQUEST, /* T2B */
3405 l_pinlessdebitcard, /* P2 */
3406 instr_type, /* E */
3407 l_bankaccount, /* B1 */
3408 iby_transactioncc_pkg.C_REQTYPE_EFT_BATCHCLOSE, /* T3A */
3409 iby_transactioncc_pkg.C_REQTYPE_BATCHREQ, /* T3B */
3410 iby_transactioncc_pkg.C_REQTYPE_REQUEST, /* T3C */
3411 l_bankaccount /* B2 */
3412 ;
3413 FETCH l_trxn_cursor BULK COLLECT INTO l_trxnGrpCriTab;
3414 CLOSE l_trxn_cursor;
3415
3416 /*
3417 * Exit if no documents were found.
3418 */
3419 IF (l_trxnGrpCriTab.COUNT = 0) THEN
3420 print_debuginfo(l_module_name, 'No transactions were '
3421 || 'retrieved from DB for profile '
3422 || p_profile_code
3423 || '. Exiting transaction grouping ..');
3424
3425 print_debuginfo(l_module_name, 'EXIT');
3426 RETURN;
3427 ELSE
3428 print_debuginfo(l_module_name, '# valid transactions '
3429 || 'retrieved from DB for profile '
3430 || p_profile_code
3431 || ' = '
3432 || l_trxnGrpCriTab.COUNT);
3433 END IF;
3434
3435 /*
3436 * Loop through all the fetched documents, grouping them
3437 * into payments.
3438 */
3439 FOR i in l_trxnGrpCriTab.FIRST .. l_trxnGrpCriTab.LAST LOOP
3440
3441 curr_trxn_id := l_trxnGrpCriTab(i).trxn_id;
3442 curr_profile_cd := l_trxnGrpCriTab(i).process_profile_code;
3443 curr_int_bank_acct_id := l_trxnGrpCriTab(i).int_bank_acct_id;
3444 curr_bep_key := l_trxnGrpCriTab(i).bep_key;
3445 curr_org_id := l_trxnGrpCriTab(i).org_id;
3446 curr_org_type := l_trxnGrpCriTab(i).org_type;
3447 curr_trxn_currency := l_trxnGrpCriTab(i).curr_code;
3448 curr_trxn_amount := l_trxnGrpCriTab(i).amount;
3449 curr_le_id := l_trxnGrpCriTab(i).legal_entity_id;
3450 curr_settle_date := l_trxnGrpCriTab(i).settle_date;
3451
3452 l_org_flag := l_trxnGrpCriTab(i).group_by_org;
3453 l_le_flag := l_trxnGrpCriTab(i).group_by_le;
3454 l_int_bnk_flag := l_trxnGrpCriTab(i).group_by_int_bank_acct;
3455 l_curr_flag := l_trxnGrpCriTab(i).group_by_curr;
3456 l_settle_date_flag := l_trxnGrpCriTab(i).group_by_settle_date;
3457
3458 l_max_trxn_limit := l_trxnGrpCriTab(i).num_trxns_limit;
3459
3460 l_fx_rate_type := l_trxnGrpCriTab(i).fx_rate_type;
3461 l_fx_curr_code := l_trxnGrpCriTab(i).max_amt_curr;
3462 l_max_amount_limit := l_trxnGrpCriTab(i).max_amt_limit;
3463
3464 /*
3465 * Log all the fetched document fields
3466 */
3467 print_debuginfo(l_module_name,
3468 'Fetched data for transaction:' || curr_trxn_id
3469 || ', internal bank account: ' || curr_int_bank_acct_id
3470 || ', profile: ' || curr_profile_cd
3471 || ', bep key: ' || curr_bep_key
3472 || ', org: ' || curr_org_id
3473 || ', org type: ' || curr_org_type
3474 || ', le: ' || curr_le_id
3475 || ', currency: ' || curr_trxn_currency
3476 || ', amount: ' || curr_trxn_amount
3477 || ', settle date: ' || curr_settle_date
3478 );
3479
3480 print_debuginfo(l_module_name,
3481 'Fetched data for transaction:' || curr_trxn_id
3482 || ', org flag: ' || l_org_flag
3483 || ', le flag: ' || l_le_flag
3484 || ', int bank acct flag: ' || l_int_bnk_flag
3485 || ', currency flag: ' || l_curr_flag
3486 || ', settle date flag: ' || l_settle_date_flag
3487 || ', max trxns limit: ' || l_max_trxn_limit
3488 || ', max amount limit: ' || l_max_amount_limit
3489 || ', exch rate: ' || l_fx_rate_type
3490 || ', exch currency: ' || l_fx_curr_code
3491 );
3492
3493 IF (l_first_record = 'Y') THEN
3494 prev_trxn_id := curr_trxn_id;
3495 prev_int_bank_acct_id := curr_int_bank_acct_id;
3496 prev_profile_cd := curr_profile_cd;
3497 prev_org_id := curr_org_id;
3498 prev_org_type := curr_org_type;
3499 prev_le_id := curr_le_id;
3500 prev_bep_key := curr_bep_key;
3501 prev_trxn_currency := curr_trxn_currency;
3502 prev_trxn_amount := curr_trxn_amount;
3503 prev_settle_date := curr_settle_date;
3504 END IF;
3505
3506 /*
3507 * We have just fetched a new transaction for this profile.
3508 * We will either insert this transaction into a new batch or
3509 * we will be inserting this transaction into the currently running
3510 * batch.
3511 *
3512 * In either case, we need to insert this trxn into a batch.
3513 * So pre-populate the batch record with attributes of
3514 * this document. This is because the batch takes on the
3515 * attributes of its constituent transactions.
3516 *
3517 * Note: For user defined grouping rules, we will
3518 * have to populate the batch attributes only if
3519 * the user has turned on grouping by that attribute.
3520 */
3521
3522 /* Only pre-fill hardcoded grouping rule attributes */
3523 l_batchRec.profile_code := curr_profile_cd;
3524 l_batchRec.bep_key := curr_bep_key;
3525
3526 /*
3527 * Pre-fill grouping rule attributes for user defined
3528 * grouping rules (that are enabled by the user).
3529 *
3530 * It is necessary to pre-fill user defined grouping
3531 * attributes before the grouping rules are triggered
3532 * because we don't know which user defined grouping rules
3533 * are going to get triggered first, and once a rule is
3534 * triggered all rules below it are skipped. So it is too
3535 * late to populate grouping attributes within the grouping
3536 * rule itself.
3537 */
3538 IF (l_org_flag = 'Y') THEN
3539 l_batchRec.org_id := curr_org_id;
3540 l_batchRec.org_type := curr_org_type;
3541 END IF;
3542
3543 IF (l_le_flag = 'Y') THEN
3544 l_batchRec.le_id := curr_le_id;
3545 END IF;
3546
3547 IF (l_int_bnk_flag = 'Y') THEN
3548 l_batchRec.int_bank_acct_id := curr_int_bank_acct_id;
3549 END IF;
3550
3551 IF (l_curr_flag = 'Y') THEN
3552 l_batchRec.curr_code := curr_trxn_currency;
3553 END IF;
3554
3555 IF (l_settle_date_flag = 'Y') THEN
3556 l_batchRec.settle_date := curr_settle_date;
3557 END IF;
3558
3559 /*
3560 * Pre-fill the document record with the details
3561 * of the current document.
3562 */
3563 l_trxnsInBatchRec.trxn_id := curr_trxn_id;
3564
3565 /*-- HARDCODED GROUPING RULES START HERE --*/
3566
3567 /*
3568 * Grouping Step 1: Payment Profile Code
3569 */
3570 IF (prev_profile_cd <> curr_profile_cd) THEN
3571
3572 print_debuginfo(l_module_name, 'Grouping by payment '
3573 || 'profile triggered for transaction '
3574 || curr_trxn_id);
3575
3576 insertTrxnIntoBatch(l_batchRec, l_batchTab,
3577 true, l_mbatch_id, l_trxnsInBatchTab,
3578 l_trxnsInBatchRec, l_trxns_in_batch_count);
3579
3580 GOTO label_finish_iteration;
3581
3582 END IF;
3583
3584 /*
3585 * Grouping Step 2: Payment System Account (Bep Key)
3586 */
3587 IF (prev_bep_key <> curr_bep_key) THEN
3588
3589 print_debuginfo(l_module_name, 'Grouping by payment '
3590 || 'system account triggered for transaction '
3591 || curr_trxn_id);
3592
3593 insertTrxnIntoBatch(l_batchRec, l_batchTab,
3594 true, l_mbatch_id, l_trxnsInBatchTab,
3595 l_trxnsInBatchRec, l_trxns_in_batch_count);
3596
3597 GOTO label_finish_iteration;
3598
3599 END IF;
3600
3601 /*-- USER DEFINED GROUPING RULES START HERE --*/
3602
3603 /*
3604 * Grouping Step 3: Organization ID And Organization Type
3605 */
3606 IF (l_org_flag = 'Y') THEN
3607
3608 IF (prev_org_id <> curr_org_id) OR
3609 (NVL(prev_org_type, 0) <> NVL(curr_org_type, 0)) THEN
3610
3611 print_debuginfo(l_module_name, 'Grouping by organization '
3612 || 'id/type triggered for transaction '
3613 || curr_trxn_id);
3614
3615 insertTrxnIntoBatch(l_batchRec, l_batchTab,
3616 true, l_mbatch_id, l_trxnsInBatchTab,
3617 l_trxnsInBatchRec, l_trxns_in_batch_count);
3618
3619 GOTO label_finish_iteration;
3620
3621 END IF;
3622
3623 END IF;
3624
3625 /*
3626 * Grouping Step 3: Legal Entity ID
3627 */
3628 IF (l_le_flag = 'Y') THEN
3629
3630 IF (prev_le_id <> curr_le_id) THEN
3631
3632 print_debuginfo(l_module_name, 'Grouping by legal '
3633 || 'entity triggered for transaction '
3634 || curr_trxn_id);
3635
3636 insertTrxnIntoBatch(l_batchRec, l_batchTab,
3637 true, l_mbatch_id, l_trxnsInBatchTab,
3638 l_trxnsInBatchRec, l_trxns_in_batch_count);
3639
3640 GOTO label_finish_iteration;
3641
3642 END IF;
3643
3644 END IF;
3645
3646 /*
3647 * Grouping Step 4: Internal Bank Account ID
3648 */
3649 IF (l_int_bnk_flag = 'Y') THEN
3650
3651 IF (prev_int_bank_acct_id <> curr_int_bank_acct_id) THEN
3652
3653 print_debuginfo(l_module_name, 'Grouping by internal bank '
3654 || 'account triggered for transaction '
3655 || curr_trxn_id);
3656
3657 insertTrxnIntoBatch(l_batchRec, l_batchTab,
3658 true, l_mbatch_id, l_trxnsInBatchTab,
3659 l_trxnsInBatchRec, l_trxns_in_batch_count);
3660
3661 GOTO label_finish_iteration;
3662
3663 END IF;
3664
3665 END IF;
3666
3667 /*
3668 * Grouping Step 5: Settlement Currency
3669 */
3670 IF (l_curr_flag = 'Y') THEN
3671
3672 IF (prev_trxn_currency <> curr_trxn_currency) THEN
3673
3674 print_debuginfo(l_module_name, 'Grouping by settlement '
3675 || 'currency triggered for transaction '
3676 || curr_trxn_id);
3677
3678 insertTrxnIntoBatch(l_batchRec, l_batchTab,
3679 true, l_mbatch_id, l_trxnsInBatchTab,
3680 l_trxnsInBatchRec, l_trxns_in_batch_count);
3681
3682 GOTO label_finish_iteration;
3683
3684 END IF;
3685
3686 END IF;
3687
3688 /*
3689 * Grouping Step 6: Settlement Date
3690 */
3691 IF (l_settle_date_flag = 'Y') THEN
3692
3693 IF (prev_settle_date <> curr_settle_date) THEN
3694
3695 print_debuginfo(l_module_name, 'Grouping by settlement '
3696 || 'date triggered for transaction '
3697 || curr_trxn_id);
3698
3699 insertTrxnIntoBatch(l_batchRec, l_batchTab,
3700 true, l_mbatch_id, l_trxnsInBatchTab,
3701 l_trxnsInBatchRec, l_trxns_in_batch_count);
3702
3703 GOTO label_finish_iteration;
3704
3705 END IF;
3706
3707 END IF;
3708
3709 /*
3710 * Grouping Step 7: Max Transactions Per Batch
3711 */
3712 IF (l_max_trxn_limit IS NOT NULL) THEN
3713
3714 IF (l_trxns_in_batch_count = l_max_trxn_limit) THEN
3715
3716 print_debuginfo(l_module_name, 'Grouping by '
3717 || 'max trxns per batch triggered for transaction '
3718 || curr_trxn_id);
3719
3720 insertTrxnIntoBatch(l_batchRec, l_batchTab,
3721 true, l_mbatch_id, l_trxnsInBatchTab,
3722 l_trxnsInBatchRec, l_trxns_in_batch_count);
3723
3724 GOTO label_finish_iteration;
3725
3726 END IF;
3727
3728 END IF;
3729
3730 /*
3731 * Grouping Step 8: Max Amount Per Batch
3732 */
3733 IF (l_max_amount_limit IS NOT NULL) THEN
3734
3735 IF (l_batch_total + l_trx_fx_amount > l_max_amount_limit) THEN
3736
3737 print_debuginfo(l_module_name, 'Grouping by '
3738 || 'max batch amount triggered by transaction '
3739 || curr_trxn_id);
3740
3741 insertTrxnIntoBatch(l_batchRec, l_batchTab,
3742 true, l_mbatch_id, l_trxnsInBatchTab,
3743 l_trxnsInBatchRec, l_trxns_in_batch_count);
3744
3745 GOTO label_finish_iteration;
3746
3747 END IF;
3748
3749 END IF;
3750
3751 /*
3752 * End Of Grouping:
3753 * If a transaction reaches here, it means that this transaction
3754 * is similar to the previous transaction as far a grouping
3755 * criteria is concerned.
3756 *
3757 * Add this transaction to the currently running batch.
3758 */
3759 print_debuginfo(l_module_name, 'No grouping rules '
3760 || 'were triggered for transaction '
3761 || curr_trxn_id);
3762
3763 insertTrxnIntoBatch(l_batchRec, l_batchTab,
3764 false, l_mbatch_id, l_trxnsInBatchTab,
3765 l_trxnsInBatchRec, l_trxns_in_batch_count);
3766
3767
3768 <<label_finish_iteration>>
3769
3770 /*
3771 * Lastly, before going into the next iteration
3772 * of the loop copy all the current grouping criteria
3773 * into 'prev' fields so that we can compare these
3774 * fields with the next record.
3775 *
3776 * No need to copy the current values into the previous ones for
3777 * the first record because we have already done it at the beginning.
3778 */
3779 IF (l_first_record <> 'Y') THEN
3780 prev_trxn_id := curr_trxn_id;
3781 prev_profile_cd := curr_profile_cd;
3782 prev_int_bank_acct_id := curr_int_bank_acct_id;
3783 prev_bep_key := curr_bep_key;
3784 prev_org_id := curr_org_id;
3785 prev_org_type := curr_org_type;
3786 prev_trxn_currency := curr_trxn_currency;
3787 prev_trxn_amount := curr_trxn_amount;
3788 prev_le_id := curr_le_id;
3789 prev_settle_date := curr_settle_date;
3790 END IF;
3791
3792 /*
3793 * Remember to reset the first record flag before going
3794 * into the next iteration.
3795 */
3796 IF (l_first_record = 'Y') THEN
3797 l_first_record := 'N';
3798 END IF;
3799
3800 print_debuginfo(l_module_name, '+----------------------------------+');
3801
3802 END LOOP;
3803
3804 print_debuginfo(l_module_name, 'Created '
3805 || l_batchTab.COUNT || ' batch(s) from '
3806 || l_trxnsInBatchTab.COUNT || ' transaction(s) for profile '
3807 || p_profile_code || '.');
3808
3809 /*
3810 * Finally, return the batches created by grouping to the caller.
3811 */
3812 x_batchTab := l_batchTab;
3813 x_trxnsInBatchTab := l_trxnsInBatchTab;
3814
3815
3816 /*
3817 * Fix for bug 5407120:
3818 *
3819 * Revert back thenumeric character mask to its original
3820 * setting. See begininning of this methods for comments
3821 * regarding this issue.
3822 *
3823 */
3824 BEGIN
3825
3826 IF (l_numeric_char_mask IS NOT NULL) THEN
3827
3828 EXECUTE IMMEDIATE
3829 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '
3830 || '"'
3831 || l_numeric_char_mask
3832 || '"'
3833 ;
3834
3835 print_debuginfo(l_module_name, 'Reverted numeric char mask to: '
3836 || l_numeric_char_mask
3837 );
3838
3839 END IF;
3840
3841 EXCEPTION
3842 WHEN OTHERS THEN
3843 print_debuginfo(l_module_name, 'Non-Fatal: Exception occured '
3844 || 'when attempting to revert numeric character mask.'
3845 );
3846
3847 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
3848 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
3849 END;
3850
3851 print_debuginfo(l_module_name, 'EXIT');
3852
3853 END performTransactionGrouping;
3854
3855
3856 /*--------------------------------------------------------------------
3857 | NAME:
3858 | performTransactionGrouping
3859 |
3860 | PURPOSE:
3861 | This is the Overloaded API for the earlier one. This will be invoked
3862 | by the corresponding overloaded procedure insert_batch_ststus_new.
3863 | This one also takes an array of User profile codes instead of one.
3864 |
3865 | PARAMETERS:
3866 | IN
3867 |
3868 |
3869 | OUT
3870 |
3871 |
3872 | RETURNS:
3873 |
3874 | NOTES:
3875 |
3876 *---------------------------------------------------------------------*/
3877 PROCEDURE performTransactionGrouping(
3878 profile_code_array IN JTF_VARCHAR2_TABLE_100,
3879 instr_type IN IBY_TRXN_SUMMARIES_ALL.
3880 instrtype%TYPE,
3881 req_type IN IBY_BATCHES_ALL.
3882 reqtype%TYPE,
3883 f_pmt_channel_in IN IBY_TRXN_SUMMARIES_ALL.
3884 payment_channel_code%TYPE,
3885 f_curr_in IN IBY_TRXN_SUMMARIES_ALL.
3886 currencynamecode%TYPE,
3887 f_settle_date IN IBY_TRXN_SUMMARIES_ALL.
3888 settledate%TYPE,
3889 f_due_date IN IBY_TRXN_SUMMARIES_ALL.
3890 settlement_due_date%TYPE,
3891 f_maturity_date IN IBY_TRXN_SUMMARIES_ALL.
3892 br_maturity_date%TYPE,
3893 f_instr_type IN IBY_TRXN_SUMMARIES_ALL.
3894 instrtype%TYPE,
3895 x_batchTab IN OUT NOCOPY IBY_TRANSACTIONCC_PKG.
3896 batchAttrTabType,
3897 x_trxnsInBatchTab IN OUT NOCOPY IBY_TRANSACTIONCC_PKG.
3898 trxnsInBatchTabType
3899 )
3900 IS
3901 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
3902 '.performTransactionGrouping';
3903
3904 l_sql_str VARCHAR2(5000);
3905 l_cursor_stmt VARCHAR2(8000);
3906
3907 l_first_record VARCHAR2(1) := 'Y';
3908
3909 /* user defined grouping rule flags */
3910 l_org_flag VARCHAR2(1) := 'N';
3911 l_le_flag VARCHAR2(1) := 'N';
3912 l_int_bnk_flag VARCHAR2(1) := 'N';
3913 l_curr_flag VARCHAR2(1) := 'N';
3914 l_settle_date_flag VARCHAR2(1) := 'N';
3915
3916 /* user defined limits */
3917 l_max_trxn_limit NUMBER(15) := 0;
3918 l_fx_rate_type VARCHAR2(255) := '';
3919 l_fx_curr_code VARCHAR2(10) := '';
3920 l_max_amount_limit NUMBER(15) := 0;
3921
3922 /*
3923 * NOTE:
3924 *
3925 * IBY_BATCHES_ALL.batchid = user generated batch id
3926 * IBY_BATCHES_ALL.mbatchid = system generated batch id
3927 *
3928 * If batch close is invoked by the user, the batchid will
3929 * be a user defined string (should be unique).
3930 *
3931 * If batch close is invoked by the scheduler, the batch is
3932 * be a sequence number (iby_batchid_s.nextval).
3933 *
3934 * mbatchid will always be a sequence number (iby_batchid_s.nextval).
3935 *
3936 * In the new architecture, multiple mbatchids can be generated
3937 * for a single batchid (based on user defined grouping rules).
3938 */
3939 l_mbatch_id IBY_BATCHES_ALL.mbatchid%TYPE;
3940 l_batch_total NUMBER(15) := 0;
3941 l_trxns_in_batch_count NUMBER(15) := 0;
3942
3943 l_trx_fx_amount NUMBER(15) := 0;
3944
3945 /*
3946 * Used to substitute null values in date comparisons.
3947 * It is assumed that not document payable would ever
3948 * have a year 1100 date.
3949 */
3950 l_impossible_date DATE := TO_DATE('01/01/1100 10:25:55',
3951 'MM/DD/YYYY HH24:MI:SS');
3952
3953 /*
3954 * These two are related data structures. Each row in batchAttrTabType
3955 * PLSQL table is used in inserting a row into the IBY_BATCHES_ALL
3956 * table.
3957 *
3958 * A separate data structure is needed to keep track of the transactions
3959 * that are part of a batch. This information is tracked in the
3960 * trxnsInBatchTabType table. The rows in trxnsInBatchTabType are
3961 * used to update the rows in IBY_TRXN_SUMMARIES_ALL table with
3962 * batch ids.
3963 *
3964 * l_batchTab l_trxnsInBatchTab
3965 * (insert into IBY_BATCHES_ALL) (update IBY_TRXN_SUMMARIES_ALL)
3966 * /-------------------------------------\ /------------\
3967 * |MBatch |Profile|..|Curr |Org |..| |MBatch |Trx |
3968 * |Id |Code |..|Code |Id |..| |Id |Id |
3969 * | | |..| | |..| | | |
3970 * |-------------------------------------| |------------|
3971 * | 4000| 10| | USD| 204| | | 4000| 501|
3972 * | | | | | | | | 4000| 504|
3973 * | | | | | | | | 4000| 505|
3974 * |-------|-------|--|-------|-------|--| |-------|----|
3975 * | 4001| 11| | -- | 342| | | 4001| 502|
3976 * | | | | | | | | 4001| 509|
3977 * | | | | | | | | 4001| 511|
3978 * | | | | | | | | 4001| 523|
3979 * | | | | | | | | : | : |
3980 * |-------|-------|--|-------|-------|--| |-------|----|
3981 * | : | : | | : | : | | | : | : |
3982 * \_______|_______|__|_______|_______|__/ \_______|____/
3983 *
3984 */
3985
3986 l_batchRec IBY_TRANSACTIONCC_PKG.batchAttrRecType;
3987 l_trxnsInBatchTab IBY_TRANSACTIONCC_PKG.trxnsInBatchTabType;
3988
3989 l_trxnsInBatchRec IBY_TRANSACTIONCC_PKG.trxnsInBatchRecType;
3990 l_batchTab IBY_TRANSACTIONCC_PKG.batchAttrTabType;
3991
3992 l_trxnGrpCriTab IBY_TRANSACTIONCC_PKG.trxnGroupCriteriaTabType;
3993
3994 l_pinlessdebitcard CONSTANT VARCHAR2(100) :='PINLESSDEBITCARD';
3995 l_bankaccount CONSTANT VARCHAR2(100) :='BANKACCOUNT';
3996
3997 /* previous transaction attributes */
3998 prev_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
3999 prev_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
4000 prev_trxn_amount iby_trxn_summaries_all.amount%TYPE;
4001 prev_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
4002 prev_org_id iby_trxn_summaries_all.org_id%TYPE;
4003 prev_org_type iby_trxn_summaries_all.org_type%TYPE;
4004 prev_settle_date iby_trxn_summaries_all.settledate%TYPE;
4005 prev_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
4006 prev_bep_key iby_trxn_summaries_all.bepkey%TYPE;
4007 prev_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
4008
4009 /* current transaction attributes */
4010 curr_trxn_id iby_trxn_summaries_all.transactionid%TYPE;
4011 curr_trxn_currency iby_trxn_summaries_all.currencynamecode%TYPE;
4012 curr_trxn_amount iby_trxn_summaries_all.amount%TYPE;
4013 curr_int_bank_acct_id iby_trxn_summaries_all.payeeinstrid%TYPE;
4014 curr_org_id iby_trxn_summaries_all.org_id%TYPE;
4015 curr_org_type iby_trxn_summaries_all.org_type%TYPE;
4016 curr_settle_date iby_trxn_summaries_all.settledate%TYPE;
4017 curr_le_id iby_trxn_summaries_all.legal_entity_id%TYPE;
4018 curr_bep_key iby_trxn_summaries_all.bepkey%TYPE;
4019 curr_profile_cd iby_trxn_summaries_all.process_profile_code%TYPE;
4020 l_user_pf_table_name VARCHAR2(100);
4021 l_sys_pf_table_name VARCHAR2(100);
4022 l_user_pf_column_name VARCHAR2(100);
4023 l_sys_pf_column_name VARCHAR2(100);
4024
4025 l_numeric_char_mask VARCHAR2(100);
4026
4027 TYPE dyn_transactions IS REF CURSOR;
4028 l_trxn_cursor dyn_transactions;
4029
4030 strProfCodes VARCHAR2(200);
4031 numProfiles NUMBER;
4032
4033 /*
4034 * This cursor up will pick up all valid transactions for
4035 * the specified payment profile. The select statement will
4036 * order the transactions based on grouping criteria.
4037 *
4038 * Important Note:
4039 *
4040 * Always ensure that there is a corresponding order by
4041 * clause for each grouping criterion that you wish to use.
4042 * This is required in order to create minimum possible
4043 * batches from a given set of transactions.
4044 *
4045 * Note 2: The sample sql is not right as the base table for
4046 * process profile is different
4047 * the dynamic sql is changed according to that
4048 */
4049 CURSOR c_transactions (
4050 strProfiles VARCHAR2,
4051 p_instr_type VARCHAR2,
4052 p_req_type VARCHAR2
4053 )
4054 IS
4055 SELECT
4056 txn.transactionid,
4057 txn.process_profile_code,
4058 txn.bepkey,
4059 txn.org_id,
4060 txn.org_type,
4061 txn.currencynamecode,
4062 txn.amount,
4063 txn.legal_entity_id,
4064 txn.payeeinstrid,
4065 txn.settledate,
4066 sys_prof.group_by_org,
4067 sys_prof.group_by_legal_entity,
4068 sys_prof.group_by_int_bank_account,
4069 sys_prof.group_by_settlement_curr,
4070 sys_prof.group_by_settlement_date,
4071 sys_prof.limit_by_amt_curr,
4072 sys_prof.limit_by_exch_rate_type,
4073 sys_prof.limit_by_total_amt,
4074 sys_prof.limit_by_settlement_num
4075 FROM
4076 IBY_TRXN_SUMMARIES_ALL txn,
4077 IBY_FNDCPT_USER_CC_PF_B user_prof,
4078 IBY_FNDCPT_SYS_CC_PF_B sys_prof
4079 WHERE
4080 user_prof.user_cc_profile_code IN (strProfiles) AND
4081 txn.process_profile_code = user_prof.user_cc_profile_code AND
4082 sys_prof.sys_cc_profile_code = user_prof.sys_cc_profile_code AND
4083 txn.status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED AND
4084 (
4085 /*
4086 * This clause will pick up credit card / purchase card
4087 * transactions.
4088 */
4089 (
4090 p_instr_type IN
4091 (
4092 iby_creditcard_pkg.C_INSTRTYPE_CCARD,
4093 iby_creditcard_pkg.C_INSTRTYPE_PCARD
4094 )
4095 AND
4096 (
4097 txn.reqtype IN
4098 (
4099 iby_transactioncc_pkg.C_REQTYPE_BATCHCLOSE,
4100 iby_transactioncc_pkg.C_REQTYPE_CAPTURE,
4101 iby_transactioncc_pkg.C_REQTYPE_CREDIT,
4102 iby_transactioncc_pkg.C_REQTYPE_RETURN
4103 )
4104 )
4105 AND
4106 (
4107 txn.instrtype IN
4108 (
4109 iby_creditcard_pkg.C_INSTRTYPE_CCARD,
4110 iby_creditcard_pkg.C_INSTRTYPE_PCARD
4111 )
4112 )
4113 )
4114
4115 /*
4116 * This clause will pick up pinless debit card
4117 * transactions.
4118 */
4119 OR
4120 (
4121 p_instr_type IN
4122 (
4123 l_pinlessdebitcard
4124 )
4125 AND
4126 (
4127 txn.reqtype IN
4128 (
4129 iby_transactioncc_pkg.C_REQTYPE_PDC_BATCHCLOSE,
4130 iby_transactioncc_pkg.C_REQTYPE_REQUEST
4131 )
4132 )
4133 AND
4134 (
4135 txn.instrtype IN
4136 (
4137 l_pinlessdebitcard
4138 )
4139 )
4140 )
4141
4142 /*
4143 * This clause will pick up bank account transactions
4144 * transactions.
4145 */
4146 OR
4147 (
4148 p_instr_type IN
4149 (
4150 l_bankaccount
4151 )
4152 AND
4153 (
4154 txn.reqtype IN
4155 (
4156 iby_transactioncc_pkg.C_REQTYPE_EFT_BATCHCLOSE,
4157 iby_transactioncc_pkg.C_REQTYPE_BATCHREQ
4158 )
4159 )
4160 AND
4161 (
4162 txn.instrtype IN
4163 (
4164 l_bankaccount
4165 )
4166 )
4167
4168 /*
4169 * Fix for bug 5442922:
4170 *
4171 * For bank account instruments, the auth / verify
4172 * transaction will have trantypeid 20; The
4173 * capture transaction will have trxntypeid 100.
4174 *
4175 * Since we are picking up only capture transactions
4176 * here, explicitly specify the trxntypeid in the
4177 * WHERE clause. Otherwise, auths are also picked
4178 * up and put into the batch.
4179 */
4180 AND
4181 (
4182 /*
4183 * This trxn type 100 maps to
4184 * IBY_FNDCPT_TRXN_PUB.BA_CAPTURE_TRXNTYPE
4185 */
4186 txn.trxntypeid = 100
4187 )
4188 )
4189
4190 /*
4191 * This clause will pick up any transaction which does not
4192 * have an instrument type. This looks dangerous to me but
4193 * kept for backward compatibility - Ramesh
4194 */
4195 OR
4196 (
4197 txn.instrtype IS NULL
4198 )
4199 ) AND
4200 txn.batchid IS NULL AND
4201 /*
4202 * Fix for bug 5632947:
4203 *
4204 * Join with CE_SECURITY_PROFILES_V for MOAC compliance.
4205 */
4206 ((txn.org_id IS NULL) OR
4207 ((txn.org_id IS NOT NULL) AND
4208 (txn.org_id, txn.org_type) IN
4209 (SELECT
4210 ce.organization_id,
4211 ce.organization_type
4212 FROM
4213 ce_security_profiles_v ce
4214 )))
4215 ORDER BY
4216 txn.process_profile_code, --
4217 txn.bepkey, -- Ensure that the
4218 txn.org_id, -- grouping rules below
4219 txn.org_type, -- follow this same
4220 txn.legal_entity_id, -- order (necessary
4221 txn.payeeinstrid, -- for creating minimum
4222 txn.currencynamecode, -- number of batches)
4223 txn.settledate --
4224 ;
4225
4226
4227 BEGIN
4228
4229 print_debuginfo(l_module_name, 'ENTER: Overloaded API.');
4230
4231 -- print_debuginfo(l_module_name, 'Payment Profile Cd: '||
4232 -- p_profile_code);
4233 print_debuginfo(l_module_name, 'Payment Profile Cd: '||
4234 strProfCodes);
4235 print_debuginfo(l_module_name, 'Instrument Type: ' ||
4236 instr_type);
4237 print_debuginfo(l_module_name, 'Request Type: ' ||
4238 req_type);
4239
4240 /*
4241 * Filter params.
4242 */
4243 print_debuginfo(l_module_name, 'f_pmt_channel_in: '
4244 || f_pmt_channel_in);
4245 print_debuginfo(l_module_name, 'f_curr_in: '
4246 || f_curr_in);
4247 print_debuginfo(l_module_name, 'f_settle_date: '
4248 || f_settle_date);
4249 print_debuginfo(l_module_name, 'f_due_date: '
4250 || f_due_date);
4251 print_debuginfo(l_module_name, 'f_maturity_date: '
4252 || f_maturity_date);
4253 print_debuginfo(l_module_name, 'f_instr_type: '
4254 || f_instr_type);
4255
4256 /*
4257 * Fix for bug 5407120:
4258 *
4259 * Before we do anything, alter the session to set the numeric
4260 * character mask. This is because of XML publisher limitation -
4261 * it cannot handle numbers like '230,56' which is the European
4262 * representation of '230.56'.
4263 *
4264 * Therefore, we explicitly set the numeric character mask at the
4265 * beginning of this routine and revert back to the default
4266 * setting at the end of this method.
4267 */
4268 BEGIN
4269
4270 SELECT
4271 value
4272 INTO
4273 l_numeric_char_mask
4274 FROM
4275 V$NLS_PARAMETERS
4276 WHERE
4277 parameter='NLS_NUMERIC_CHARACTERS'
4278 ;
4279
4280 print_debuginfo(l_module_name, 'Current numeric char mask: '
4281 || l_numeric_char_mask
4282 );
4283
4284 EXCEPTION
4285 WHEN OTHERS THEN
4286 print_debuginfo(l_module_name, 'Non-Fatal: Exception occured '
4287 || 'when attempting to retrieve numeric character mask.'
4288 );
4289
4290 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
4291 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
4292 END;
4293
4294 /*
4295 * Now alter the session, to force the NLS numeric character
4296 * decimal indicator to be a '.'.
4297 */
4298 BEGIN
4299
4300 EXECUTE IMMEDIATE
4301 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ".,"'
4302 ;
4303
4304 EXCEPTION
4305 WHEN OTHERS THEN
4306 print_debuginfo(l_module_name, 'Non-Fatal: Exception occured '
4307 || 'when attempting to later session to set '
4308 || 'numeric character mask.'
4309 );
4310
4311 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
4312 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
4313 END;
4314
4315 /*
4316 * Dynamically form SQL string to limit the rows that
4317 * are picked up for the batch close operation.
4318 *
4319 * Use the supplied filter parameters to form the SQL
4320 * string.
4321 */
4322 IF (f_pmt_channel_in IS NOT NULL) THEN
4323 l_sql_str := l_sql_str || ' AND payment_channel_code = '
4324 || '''' || f_pmt_channel_in || '''';
4325 END IF;
4326
4327 IF (f_curr_in IS NOT NULL) THEN
4328 l_sql_str := l_sql_str || ' AND currencynamecode = '
4329 || '''' || f_curr_in || '''';
4330 END IF;
4331
4332 IF (f_settle_date IS NOT NULL) THEN
4333 l_sql_str := l_sql_str || ' AND settledate <= '
4334 || '''' || f_settle_date || '''';
4335 END IF;
4336
4337 IF (f_due_date IS NOT NULL) THEN
4338 l_sql_str := l_sql_str || ' AND settlement_due_date <= '
4339 || '''' || f_due_date || '''';
4340 END IF;
4341
4342 IF (f_maturity_date IS NOT NULL) THEN
4343 l_sql_str := l_sql_str || ' AND br_maturity_date <= '
4344 || '''' || f_maturity_date || '''';
4345 END IF;
4346
4347 IF (f_instr_type IS NOT NULL) THEN
4348 l_sql_str := l_sql_str || ' AND instrtype = '
4349 || '''' || f_instr_type || '''';
4350 END IF;
4351
4352 print_debuginfo(l_module_name, 'Dynamic SQL snippet: '
4353 || l_sql_str);
4354
4355 /* determine the process profile table and column */
4356
4357 l_user_pf_table_name :='IBY_FNDCPT_USER_CC_PF_B';
4358 l_sys_pf_table_name :='IBY_FNDCPT_SYS_CC_PF_B';
4359 l_user_pf_column_name :='USER_CC_PROFILE_CODE';
4360 l_sys_pf_column_name :='SYS_CC_PROFILE_CODE';
4361 IF (instr_type IS NOT NULL) THEN
4362 if(instr_type =l_bankaccount) THEN
4363 l_user_pf_table_name :='IBY_FNDCPT_USER_EFT_PF_B';
4364 l_sys_pf_table_name :='IBY_FNDCPT_SYS_EFT_PF_B';
4365 l_user_pf_column_name :='USER_EFT_PROFILE_CODE';
4366 l_sys_pf_column_name :='SYS_EFT_PROFILE_CODE';
4367 ELSIF (instr_type =l_pinlessdebitcard) THEN
4368 l_user_pf_table_name :='IBY_FNDCPT_USER_DC_PF_B';
4369 l_sys_pf_table_name :='IBY_FNDCPT_SYS_DC_PF_B';
4370 l_user_pf_column_name :='USER_DC_PROFILE_CODE';
4371 l_sys_pf_column_name :='SYS_DC_PROFILE_CODE';
4372 END IF;
4373
4374 END IF;
4375
4376 /* Form a comma separated string for the profile codes */
4377 numProfiles := profile_code_array.count;
4378 FOR i IN 1..(numProfiles-1) LOOP
4379 strProfCodes := strProfCodes||''''||profile_code_array(i)||''',';
4380 END LOOP;
4381 /* Append the last profile code without comma at the end */
4382 strProfCodes := strProfCodes||''''||profile_code_array(numProfiles)||'''';
4383
4384 /*
4385 * The cursor below is the same as the cursor c_transactions
4386 * defined at the beginning of this method.
4387 *
4388 * We cannot directly use c_transactions because we need to use
4389 * the provided filter params to form a dynamic where clause.
4390 *
4391 * For this reason, this cursor has been made into a dynamic cursor.
4392 * c_transactions is kept for documentation / debugging purposes
4393 * but is not used.
4394 */
4395 l_cursor_stmt :=
4396 'SELECT '
4397 || 'txn.transactionid, '
4398 || 'txn.process_profile_code, '
4399 || 'txn.bepkey, '
4400 || 'txn.org_id, '
4401 || 'txn.org_type, '
4402 || 'txn.currencynamecode, '
4403 || 'txn.amount, '
4404 || 'txn.legal_entity_id, '
4405 || 'txn.payeeinstrid, '
4406 || 'txn.settledate, '
4407 || 'sys_prof.group_by_org, '
4408 || 'sys_prof.group_by_legal_entity, '
4409 || 'sys_prof.group_by_int_bank_account, '
4410 || 'sys_prof.group_by_settlement_curr, '
4411 || 'sys_prof.group_by_settlement_date, '
4412 || 'sys_prof.limit_by_amt_curr, '
4413 || 'sys_prof.limit_by_exch_rate_type, '
4414 || 'sys_prof.limit_by_total_amt, '
4415 || 'sys_prof.limit_by_settlement_num '
4416 || 'FROM '
4417 || 'IBY_TRXN_SUMMARIES_ALL txn, '
4418 || l_user_pf_table_name || ' user_prof, '
4419 || l_sys_pf_table_name || ' sys_prof '
4420 || 'WHERE '
4421 || 'user_prof.'||l_user_pf_column_name||' IN ('||strProfCodes||') AND '
4422 || 'txn.process_profile_code = user_prof.'||l_user_pf_column_name||' AND '
4423 || 'sys_prof.' ||l_sys_pf_column_name||' = user_prof. '||l_sys_pf_column_name || ' AND '
4424 || 'txn.status = :open_batch AND '
4425 || '( '
4426 /*
4427 * This clause will pick up credit card / purchase card
4428 * transactions.
4429 */
4430 || '( '
4431 || ':A IN (:C1, :C2) AND (txn.reqtype IN (:T1A, :T1B, :T1C, :T1D, :T1E)) AND '
4432 || '(txn.instrtype IN (:C3, :C4)) '
4433 || ') '
4434
4435 /*
4436 * This clause will pick up pinless debit card
4437 * transactions.
4438 */
4439 || 'OR '
4440 || '( '
4441 || ':C IN (:P1) AND (txn.reqtype IN (:T2A, :T2B)) AND '
4442 || '(txn.instrtype IN (:P2)) '
4443 || ') '
4444
4445 /*
4446 * This clause will pick up bank account transactions
4447 * transactions.
4448 */
4449
4450 || 'OR '
4451 || '( '
4452 || ':E IN (:B1) AND (txn.reqtype IN (:T3A, :T3B, :T3C)) AND '
4453 || '(txn.instrtype IN (:B2)) '
4454
4455
4456 /*
4457 * Fix for bug 5442922:
4458 *
4459 * For bank account instruments, the auth / verify
4460 * transaction will have trantypeid 20; The
4461 * capture transaction will have trxntypeid 100.
4462 *
4463 * Since we are picking up only capture transactions
4464 * here, explicitly specify the trxntypeid in the
4465 * WHERE clause. Otherwise, auths are also picked
4466 * up and put into the batch.
4467 */
4468 || 'AND '
4469 || '( '
4470 /*
4471 * This trxn type 100 maps to
4472 * IBY_FNDCPT_TRXN_PUB.BA_CAPTURE_TRXNTYPE
4473 */
4474 || 'txn.trxntypeid = 100 '
4475 || ') '
4476
4477 || ') '
4478
4479 /*
4480 * This clause will pick up any transaction which does not
4481 * have an instrument type. This looks dangerous to me but
4482 * kept for backward compatibility - Ramesh
4483 */
4484 || 'OR '
4485 || '( '
4486 || 'txn.instrtype IS NULL '
4487 || ') '
4488 || ') AND '
4489 || 'txn.batchid IS NULL AND '
4490 /*
4491 * Fix for bug 5632947:
4492 *
4493 * Join with CE_SECURITY_PROFILES_V for MOAC compliance.
4494 */
4495 || '((txn.org_id IS NULL) OR '
4496 || '((txn.org_id IS NOT NULL) AND '
4497 || '(txn.org_id, txn.org_type) IN '
4498 || ' (SELECT '
4499 || ' ce.organization_id, '
4500 || ' ce.organization_type '
4501 || ' FROM '
4502 || ' ce_security_profiles_v ce '
4503 || ' ))) '
4504 || NVL (l_sql_str, 'AND 1=1 ')
4505 || 'ORDER BY '
4506 || 'txn.process_profile_code, ' --
4507 || 'txn.bepkey, ' -- Ensure that the
4508 || 'txn.org_id, ' -- grouping rules below
4509 || 'txn.org_type, ' -- follow this same
4510 || 'txn.legal_entity_id, ' -- order (necessary
4511 || 'txn.payeeinstrid, ' -- for creating minimum
4512 || 'txn.currencynamecode, ' -- number of batches)
4513 || 'txn.settledate ' --
4514 ;
4515
4516 OPEN l_trxn_cursor FOR
4517 l_cursor_stmt
4518 USING
4519 -- comment out this one as we have already put this value in a comma separated string
4520 --p_profile_code, /* profile_code */
4521 iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED, /* open_batch */
4522 instr_type, /* A */
4523 iby_creditcard_pkg.C_INSTRTYPE_CCARD, /* C1 */
4524 iby_creditcard_pkg.C_INSTRTYPE_PCARD, /* C2 */
4525 iby_transactioncc_pkg.C_REQTYPE_BATCHCLOSE, /* T1A */
4526 iby_transactioncc_pkg.C_REQTYPE_CAPTURE, /* T1B */
4527 iby_transactioncc_pkg.C_REQTYPE_CREDIT, /* T1C */
4528 iby_transactioncc_pkg.C_REQTYPE_RETURN, /* T1D */
4529 iby_transactioncc_pkg.C_REQTYPE_REQUEST, /* T1E */
4530 iby_creditcard_pkg.C_INSTRTYPE_CCARD, /* C3 */
4531 iby_creditcard_pkg.C_INSTRTYPE_PCARD, /* C4 */
4532 instr_type, /* C */
4533 l_pinlessdebitcard, /* P1 */
4534 iby_transactioncc_pkg.C_REQTYPE_PDC_BATCHCLOSE, /* T2A */
4535 iby_transactioncc_pkg.C_REQTYPE_REQUEST, /* T2B */
4536 l_pinlessdebitcard, /* P2 */
4537 instr_type, /* E */
4538 l_bankaccount, /* B1 */
4539 iby_transactioncc_pkg.C_REQTYPE_EFT_BATCHCLOSE, /* T3A */
4540 iby_transactioncc_pkg.C_REQTYPE_BATCHREQ, /* T3B */
4541 iby_transactioncc_pkg.C_REQTYPE_REQUEST, /* T3C */
4542 l_bankaccount /* B2 */
4543 ;
4544 FETCH l_trxn_cursor BULK COLLECT INTO l_trxnGrpCriTab;
4545 CLOSE l_trxn_cursor;
4546
4547 /*
4548 * Exit if no documents were found.
4549 */
4550 IF (l_trxnGrpCriTab.COUNT = 0) THEN
4551 print_debuginfo(l_module_name, 'No transactions were '
4552 || 'retrieved from DB for profile '
4553 || strProfCodes
4554 || '. Exiting transaction grouping ..');
4555
4556 print_debuginfo(l_module_name, 'EXIT');
4557 RETURN;
4558 ELSE
4559 print_debuginfo(l_module_name, '# valid transactions '
4560 || 'retrieved from DB for profile '
4561 || strProfCodes
4562 || ' = '
4563 || l_trxnGrpCriTab.COUNT);
4564 END IF;
4565
4566 /*
4567 * Loop through all the fetched documents, grouping them
4568 * into payments.
4569 */
4570 FOR i in l_trxnGrpCriTab.FIRST .. l_trxnGrpCriTab.LAST LOOP
4571
4572 curr_trxn_id := l_trxnGrpCriTab(i).trxn_id;
4573 curr_profile_cd := l_trxnGrpCriTab(i).process_profile_code;
4574 curr_int_bank_acct_id := l_trxnGrpCriTab(i).int_bank_acct_id;
4575 curr_bep_key := l_trxnGrpCriTab(i).bep_key;
4576 curr_org_id := l_trxnGrpCriTab(i).org_id;
4577 curr_org_type := l_trxnGrpCriTab(i).org_type;
4578 curr_trxn_currency := l_trxnGrpCriTab(i).curr_code;
4579 curr_trxn_amount := l_trxnGrpCriTab(i).amount;
4580 curr_le_id := l_trxnGrpCriTab(i).legal_entity_id;
4581 curr_settle_date := l_trxnGrpCriTab(i).settle_date;
4582
4583 l_org_flag := l_trxnGrpCriTab(i).group_by_org;
4584 l_le_flag := l_trxnGrpCriTab(i).group_by_le;
4585 l_int_bnk_flag := l_trxnGrpCriTab(i).group_by_int_bank_acct;
4586 l_curr_flag := l_trxnGrpCriTab(i).group_by_curr;
4587 l_settle_date_flag := l_trxnGrpCriTab(i).group_by_settle_date;
4588
4589 l_max_trxn_limit := l_trxnGrpCriTab(i).num_trxns_limit;
4590
4591 l_fx_rate_type := l_trxnGrpCriTab(i).fx_rate_type;
4592 l_fx_curr_code := l_trxnGrpCriTab(i).max_amt_curr;
4593 l_max_amount_limit := l_trxnGrpCriTab(i).max_amt_limit;
4594
4595 /*
4596 * Log all the fetched document fields
4597 */
4598 print_debuginfo(l_module_name,
4599 'Fetched data for transaction:' || curr_trxn_id
4600 || ', internal bank account: ' || curr_int_bank_acct_id
4601 || ', profile: ' || curr_profile_cd
4602 || ', bep key: ' || curr_bep_key
4603 || ', org: ' || curr_org_id
4604 || ', org type: ' || curr_org_type
4605 || ', le: ' || curr_le_id
4606 || ', currency: ' || curr_trxn_currency
4607 || ', amount: ' || curr_trxn_amount
4608 || ', settle date: ' || curr_settle_date
4609 );
4610
4611 print_debuginfo(l_module_name,
4612 'Fetched data for transaction:' || curr_trxn_id
4613 || ', org flag: ' || l_org_flag
4614 || ', le flag: ' || l_le_flag
4615 || ', int bank acct flag: ' || l_int_bnk_flag
4616 || ', currency flag: ' || l_curr_flag
4617 || ', settle date flag: ' || l_settle_date_flag
4618 || ', max trxns limit: ' || l_max_trxn_limit
4619 || ', max amount limit: ' || l_max_amount_limit
4620 || ', exch rate: ' || l_fx_rate_type
4621 || ', exch currency: ' || l_fx_curr_code
4622 );
4623
4624 IF (l_first_record = 'Y') THEN
4625 prev_trxn_id := curr_trxn_id;
4626 prev_int_bank_acct_id := curr_int_bank_acct_id;
4627 prev_profile_cd := curr_profile_cd;
4628 prev_org_id := curr_org_id;
4629 prev_org_type := curr_org_type;
4630 prev_le_id := curr_le_id;
4631 prev_bep_key := curr_bep_key;
4632 prev_trxn_currency := curr_trxn_currency;
4633 prev_trxn_amount := curr_trxn_amount;
4634 prev_settle_date := curr_settle_date;
4635 END IF;
4636
4637 /*
4638 * We have just fetched a new transaction for this profile.
4639 * We will either insert this transaction into a new batch or
4640 * we will be inserting this transaction into the currently running
4641 * batch.
4642 *
4643 * In either case, we need to insert this trxn into a batch.
4644 * So pre-populate the batch record with attributes of
4645 * this document. This is because the batch takes on the
4646 * attributes of its constituent transactions.
4647 *
4648 * Note: For user defined grouping rules, we will
4649 * have to populate the batch attributes only if
4650 * the user has turned on grouping by that attribute.
4651 */
4652
4653 /* Only pre-fill hardcoded grouping rule attributes */
4654 /* Commenting out this one as, we no more have profile code and currency
4655 as hard coded grouping rules
4656 */
4657 --l_batchRec.profile_code := curr_profile_cd;
4658 --l_batchRec.bep_key := curr_bep_key;
4659
4660 /*
4661 * Pre-fill grouping rule attributes for user defined
4662 * grouping rules (that are enabled by the user).
4663 *
4664 * It is necessary to pre-fill user defined grouping
4665 * attributes before the grouping rules are triggered
4666 * because we don't know which user defined grouping rules
4667 * are going to get triggered first, and once a rule is
4668 * triggered all rules below it are skipped. So it is too
4669 * late to populate grouping attributes within the grouping
4670 * rule itself.
4671 */
4672 IF (l_org_flag = 'Y') THEN
4673 l_batchRec.org_id := curr_org_id;
4674 l_batchRec.org_type := curr_org_type;
4675 END IF;
4676
4677 IF (l_le_flag = 'Y') THEN
4678 l_batchRec.le_id := curr_le_id;
4679 END IF;
4680
4681 IF (l_int_bnk_flag = 'Y') THEN
4682 l_batchRec.int_bank_acct_id := curr_int_bank_acct_id;
4683 END IF;
4684
4685 IF (l_curr_flag = 'Y') THEN
4686 l_batchRec.curr_code := curr_trxn_currency;
4687 END IF;
4688
4689 IF (l_settle_date_flag = 'Y') THEN
4690 l_batchRec.settle_date := curr_settle_date;
4691 END IF;
4692
4693 /*
4694 * Pre-fill the document record with the details
4695 * of the current document.
4696 */
4697 l_trxnsInBatchRec.trxn_id := curr_trxn_id;
4698
4699 /*-- HARDCODED GROUPING RULES START HERE --*/
4700 /* Commenting out this one as, we no more have profile code and currency
4701 as hard coded grouping rules
4702 */
4703
4704 /*
4705 * Grouping Step 1: Payment Profile Code
4706 */
4707 /* IF (prev_profile_cd <> curr_profile_cd) THEN
4708
4709 print_debuginfo(l_module_name, 'Grouping by payment '
4710 || 'profile triggered for transaction '
4711 || curr_trxn_id);
4712
4713 insertTrxnIntoBatch(l_batchRec, l_batchTab,
4714 true, l_mbatch_id, l_trxnsInBatchTab,
4715 l_trxnsInBatchRec, l_trxns_in_batch_count);
4716
4717 GOTO label_finish_iteration;
4718
4719 END IF;
4720 */
4721
4722 /*
4723 * Grouping Step 2: Payment System Account (Bep Key)
4724 */
4725 /*
4726 IF (prev_bep_key <> curr_bep_key) THEN
4727
4728 print_debuginfo(l_module_name, 'Grouping by payment '
4729 || 'system account triggered for transaction '
4730 || curr_trxn_id);
4731
4732 insertTrxnIntoBatch(l_batchRec, l_batchTab,
4733 true, l_mbatch_id, l_trxnsInBatchTab,
4734 l_trxnsInBatchRec, l_trxns_in_batch_count);
4735
4736 GOTO label_finish_iteration;
4737
4738 END IF;
4739 */
4740
4741 /*-- USER DEFINED GROUPING RULES START HERE --*/
4742
4743 /*
4744 * Grouping Step 3: Organization ID And Organization Type
4745 */
4746 IF (l_org_flag = 'Y') THEN
4747
4748 IF (prev_org_id <> curr_org_id) OR
4749 (NVL(prev_org_type, 0) <> NVL(curr_org_type, 0)) THEN
4750
4751 print_debuginfo(l_module_name, 'Grouping by organization '
4752 || 'id/type triggered for transaction '
4753 || curr_trxn_id);
4754
4755 insertTrxnIntoBatch(l_batchRec, l_batchTab,
4756 true, l_mbatch_id, l_trxnsInBatchTab,
4757 l_trxnsInBatchRec, l_trxns_in_batch_count);
4758
4759 GOTO label_finish_iteration;
4760
4761 END IF;
4762
4763 END IF;
4764
4765 /*
4766 * Grouping Step 3: Legal Entity ID
4767 */
4768 IF (l_le_flag = 'Y') THEN
4769
4770 IF (prev_le_id <> curr_le_id) THEN
4771
4772 print_debuginfo(l_module_name, 'Grouping by legal '
4773 || 'entity triggered for transaction '
4774 || curr_trxn_id);
4775
4776 insertTrxnIntoBatch(l_batchRec, l_batchTab,
4777 true, l_mbatch_id, l_trxnsInBatchTab,
4778 l_trxnsInBatchRec, l_trxns_in_batch_count);
4779
4780 GOTO label_finish_iteration;
4781
4782 END IF;
4783
4784 END IF;
4785
4786 /*
4787 * Grouping Step 4: Internal Bank Account ID
4788 */
4789 IF (l_int_bnk_flag = 'Y') THEN
4790
4791 IF (prev_int_bank_acct_id <> curr_int_bank_acct_id) THEN
4792
4793 print_debuginfo(l_module_name, 'Grouping by internal bank '
4794 || 'account triggered for transaction '
4795 || curr_trxn_id);
4796
4797 insertTrxnIntoBatch(l_batchRec, l_batchTab,
4798 true, l_mbatch_id, l_trxnsInBatchTab,
4799 l_trxnsInBatchRec, l_trxns_in_batch_count);
4800
4801 GOTO label_finish_iteration;
4802
4803 END IF;
4804
4805 END IF;
4806
4807 /*
4808 * Grouping Step 5: Settlement Currency
4809 */
4810 IF (l_curr_flag = 'Y') THEN
4811
4812 IF (prev_trxn_currency <> curr_trxn_currency) THEN
4813
4814 print_debuginfo(l_module_name, 'Grouping by settlement '
4815 || 'currency triggered for transaction '
4816 || curr_trxn_id);
4817
4818 insertTrxnIntoBatch(l_batchRec, l_batchTab,
4819 true, l_mbatch_id, l_trxnsInBatchTab,
4820 l_trxnsInBatchRec, l_trxns_in_batch_count);
4821
4822 GOTO label_finish_iteration;
4823
4824 END IF;
4825
4826 END IF;
4827
4828 /*
4829 * Grouping Step 6: Settlement Date
4830 */
4831 IF (l_settle_date_flag = 'Y') THEN
4832
4833 IF (prev_settle_date <> curr_settle_date) THEN
4834
4835 print_debuginfo(l_module_name, 'Grouping by settlement '
4836 || 'date triggered for transaction '
4837 || curr_trxn_id);
4838
4839 insertTrxnIntoBatch(l_batchRec, l_batchTab,
4840 true, l_mbatch_id, l_trxnsInBatchTab,
4841 l_trxnsInBatchRec, l_trxns_in_batch_count);
4842
4843 GOTO label_finish_iteration;
4844
4845 END IF;
4846
4847 END IF;
4848
4849 /*
4850 * Grouping Step 7: Max Transactions Per Batch
4851 */
4852 IF (l_max_trxn_limit IS NOT NULL) THEN
4853
4854 IF (l_trxns_in_batch_count = l_max_trxn_limit) THEN
4855
4856 print_debuginfo(l_module_name, 'Grouping by '
4857 || 'max trxns per batch triggered for transaction '
4858 || curr_trxn_id);
4859
4860 insertTrxnIntoBatch(l_batchRec, l_batchTab,
4861 true, l_mbatch_id, l_trxnsInBatchTab,
4862 l_trxnsInBatchRec, l_trxns_in_batch_count);
4863
4864 GOTO label_finish_iteration;
4865
4866 END IF;
4867
4868 END IF;
4869
4870 /*
4871 * Grouping Step 8: Max Amount Per Batch
4872 */
4873 IF (l_max_amount_limit IS NOT NULL) THEN
4874
4875 IF (l_batch_total + l_trx_fx_amount > l_max_amount_limit) THEN
4876
4877 print_debuginfo(l_module_name, 'Grouping by '
4878 || 'max batch amount triggered by transaction '
4879 || curr_trxn_id);
4880
4881 insertTrxnIntoBatch(l_batchRec, l_batchTab,
4882 true, l_mbatch_id, l_trxnsInBatchTab,
4883 l_trxnsInBatchRec, l_trxns_in_batch_count);
4884
4885 GOTO label_finish_iteration;
4886
4887 END IF;
4888
4889 END IF;
4890
4891 /*
4892 * End Of Grouping:
4893 * If a transaction reaches here, it means that this transaction
4894 * is similar to the previous transaction as far a grouping
4895 * criteria is concerned.
4896 *
4897 * Add this transaction to the currently running batch.
4898 */
4899 print_debuginfo(l_module_name, 'No grouping rules '
4900 || 'were triggered for transaction '
4901 || curr_trxn_id);
4902
4903 insertTrxnIntoBatch(l_batchRec, l_batchTab,
4904 false, l_mbatch_id, l_trxnsInBatchTab,
4905 l_trxnsInBatchRec, l_trxns_in_batch_count);
4906
4907
4908 <<label_finish_iteration>>
4909
4910 /*
4911 * Lastly, before going into the next iteration
4912 * of the loop copy all the current grouping criteria
4913 * into 'prev' fields so that we can compare these
4914 * fields with the next record.
4915 *
4916 * No need to copy the current values into the previous ones for
4917 * the first record because we have already done it at the beginning.
4918 */
4919 IF (l_first_record <> 'Y') THEN
4920 prev_trxn_id := curr_trxn_id;
4921 prev_profile_cd := curr_profile_cd;
4922 prev_int_bank_acct_id := curr_int_bank_acct_id;
4923 prev_bep_key := curr_bep_key;
4924 prev_org_id := curr_org_id;
4925 prev_org_type := curr_org_type;
4926 prev_trxn_currency := curr_trxn_currency;
4927 prev_trxn_amount := curr_trxn_amount;
4928 prev_le_id := curr_le_id;
4929 prev_settle_date := curr_settle_date;
4930 END IF;
4931
4932 /*
4933 * Remember to reset the first record flag before going
4934 * into the next iteration.
4935 */
4936 IF (l_first_record = 'Y') THEN
4937 l_first_record := 'N';
4938 END IF;
4939
4940 print_debuginfo(l_module_name, '+----------------------------------+');
4941
4942 END LOOP;
4943
4944 print_debuginfo(l_module_name, 'Created '
4945 || l_batchTab.COUNT || ' batch(s) from '
4946 || l_trxnsInBatchTab.COUNT || ' transaction(s) for profile '
4947 || 'removed this value for the time being' || '.');
4948
4949 /*
4950 * Finally, return the batches created by grouping to the caller.
4951 */
4952 x_batchTab := l_batchTab;
4953 x_trxnsInBatchTab := l_trxnsInBatchTab;
4954
4955
4956 /*
4957 * Fix for bug 5407120:
4958 *
4959 * Revert back thenumeric character mask to its original
4960 * setting. See begininning of this methods for comments
4961 * regarding this issue.
4962 *
4963 */
4964 BEGIN
4965
4966 IF (l_numeric_char_mask IS NOT NULL) THEN
4967
4968 EXECUTE IMMEDIATE
4969 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '
4970 || '"'
4971 || l_numeric_char_mask
4972 || '"'
4973 ;
4974
4975 print_debuginfo(l_module_name, 'Reverted numeric char mask to: '
4976 || l_numeric_char_mask
4977 );
4978
4979 END IF;
4980
4981 EXCEPTION
4982 WHEN OTHERS THEN
4983 print_debuginfo(l_module_name, 'Non-Fatal: Exception occured '
4984 || 'when attempting to revert numeric character mask.'
4985 );
4986
4987 print_debuginfo(l_module_name, 'SQL code: ' || SQLCODE);
4988 print_debuginfo(l_module_name, 'SQL err msg: '|| SQLERRM);
4989 END;
4990
4991 print_debuginfo(l_module_name, 'EXIT');
4992
4993 END performTransactionGrouping;
4994
4995
4996 /*--------------------------------------------------------------------
4997 | NAME:
4998 | insertTrxnIntoBatch
4999 |
5000 | PURPOSE:
5001 | Inserts a given transaction into a currently running batch
5002 | or into a new batch as per given flag.
5003 |
5004 | This method is called by every grouping rule to add
5005 | a given transaction into a current batch/new batch.
5006 |
5007 | PARAMETERS:
5008 | IN
5009 |
5010 |
5011 | OUT
5012 |
5013 |
5014 | RETURNS:
5015 |
5016 | NOTES:
5017 |
5018 *---------------------------------------------------------------------*/
5019 PROCEDURE insertTrxnIntoBatch(
5020 x_batchRec IN OUT NOCOPY batchAttrRecType,
5021 x_batchTab IN OUT NOCOPY batchAttrTabType,
5022 p_newBatchFlag IN BOOLEAN,
5023 x_currentBatchId IN OUT NOCOPY IBY_BATCHES_ALL.batchid%TYPE,
5024 x_trxnsInBatchTab IN OUT NOCOPY trxnsInBatchTabType,
5025 x_trxnsInBatchRec IN OUT NOCOPY trxnsInBatchRecType,
5026 x_trxnsInBatchCount IN OUT NOCOPY NUMBER
5027 )
5028 IS
5029 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
5030 '.insertTrxnIntoBatch';
5031
5032 BEGIN
5033
5034 print_debuginfo(l_module_name, 'ENTER');
5035
5036 /*
5037 * GROUPING LOGIC IS IN IF-ELSE BLOCK BELOW:
5038 *
5039 * Irrespective of whether this transaction is part of
5040 * an existing batch or whether it should be part
5041 * of a new batch, ensure that the PLSQL batches
5042 * table is updated with the details of this transaction
5043 * within this if-else block.
5044 *
5045 * We need to do this each time we enter this procedure
5046 * because this might well be the last transaction in
5047 * in the provided profile, and this procedure may
5048 * not be called again for this profile. So
5049 * the PLSQL batches table should always be up-to-date
5050 * when it exits this procedure.
5051 */
5052
5053 IF (p_newBatchFlag = true) THEN
5054
5055 /*
5056 * This is a new batch; Get an id for this batch
5057 */
5058 getNextBatchId(x_currentBatchId);
5059
5060 /*
5061 * Create a new batch record using the incoming
5062 * transaction as a constituent, and insert this record
5063 * into the PLSQL batches table.
5064 */
5065 x_batchRec.mbatch_id := x_currentBatchId;
5066 x_trxnsInBatchCount := 1;
5067
5068 x_batchTab(x_batchTab.COUNT + 1) := x_batchRec;
5069
5070 print_debuginfo(l_module_name,' Inserted transaction : '
5071 || x_trxnsInBatchRec.trxn_id || ' into new batch: '
5072 || x_currentBatchId);
5073
5074 /*
5075 * Assign the batch id of the new batch to this
5076 * trxn, and insert the trxn into the trxns array.
5077 */
5078 x_trxnsInBatchRec.mbatch_id := x_batchRec.mbatch_id;
5079 x_trxnsInBatchTab(x_trxnsInBatchTab.COUNT + 1) := x_trxnsInBatchRec;
5080
5081 ELSE
5082
5083 /*
5084 * This means we need to add the incoming transaction to
5085 * the current batch.
5086 */
5087
5088 /*
5089 * First check special case: Payments PLSQL table is empty
5090 *
5091 * If the PLSQL table for batches is empty, we have to
5092 * initialize it by inserting a dummy record. This dummy
5093 * record will get overwritten below.
5094 */
5095 IF (x_batchTab.COUNT = 0) THEN
5096
5097 getNextBatchId(x_currentBatchId);
5098
5099 x_batchRec.mbatch_id := x_currentBatchId;
5100 x_trxnsInBatchCount := 0;
5101
5102 /*
5103 * Insert the first record into the table. This
5104 * is a dummy record.
5105 */
5106 x_batchTab(x_batchTab.COUNT + 1) := x_batchRec;
5107
5108 print_debuginfo(l_module_name, 'Created a new batch: '
5109 || x_currentBatchId);
5110
5111 END IF;
5112
5113 /*
5114 * The incoming transaction should be part of the current batch.
5115 * So add the document amount to the current payment
5116 * record and increment the document count for the current
5117 * payment record.
5118 */
5119 x_batchRec.mbatch_id := x_currentBatchId;
5120 x_trxnsInBatchCount := x_trxnsInBatchCount + 1;
5121
5122 --x_batchRec.payment_amount :=
5123 -- x_batchRec.payment_amount + x_trxnsInBatchRec.document_amount;
5124
5125 /*
5126 * Overwrite the current batch record in the
5127 * PLSQL batches table with the updated record.
5128 */
5129 x_batchTab(x_batchTab.COUNT) := x_batchRec;
5130
5131 print_debuginfo(l_module_name, 'Inserted transaction: '
5132 || x_trxnsInBatchRec.trxn_id || ' into existing batch: '
5133 || x_currentBatchId);
5134
5135 /*
5136 * Assign the batch id of the current batch to this
5137 * transaction, and insert the trxn into the trxns array.
5138 */
5139 x_trxnsInBatchRec.mbatch_id := x_batchRec.mbatch_id;
5140 x_trxnsInBatchTab(x_trxnsInBatchTab.COUNT + 1) := x_trxnsInBatchRec;
5141
5142 END IF;
5143
5144 print_debuginfo(l_module_name, 'EXIT');
5145
5146 END insertTrxnIntoBatch;
5147
5148 /*--------------------------------------------------------------------
5149 | NAME:
5150 | print_debuginfo
5151 |
5152 | PURPOSE:
5153 | This procedure prints the debug message to the concurrent manager
5154 | log file.
5155 |
5156 | PARAMETERS:
5157 | IN
5158 | p_debug_text - The debug message to be printed
5159 |
5160 | OUT
5161 |
5162 |
5163 | RETURNS:
5164 |
5165 | NOTES:
5166 |
5167 *---------------------------------------------------------------------*/
5168 PROCEDURE print_debuginfo(
5169 p_module IN VARCHAR2,
5170 p_debug_text IN VARCHAR2
5171 )
5172 IS
5173 PRAGMA AUTONOMOUS_TRANSACTION;
5174
5175 BEGIN
5176
5177 /*
5178 * If FND_GLOBAL.conc_request_id is -1, it implies that
5179 * this method has not been invoked via the concurrent
5180 * manager. In that case, write to apps log else write
5181 * to concurrent manager log file.
5182 */
5183 /*Remove this 2 lines after debugging*/
5184 -- INSERT INTO TEMP_IBY_LOGS VALUES (p_module || ': '
5185 -- || p_debug_text, sysdate);
5186 -- commit;
5187
5188 IF (FND_GLOBAL.conc_request_id = -1) THEN
5189
5190 /*
5191 * OPTION I:
5192 * Write debug text to the common application log file.
5193 */
5194 IBY_DEBUG_PUB.add(
5195 substr(RPAD(p_module,55) || ' : ' || p_debug_text, 0, 150),
5196 FND_LOG.G_CURRENT_RUNTIME_LEVEL,
5197 'iby.plsql.IBY_VALIDATIONSETS_PUB'
5198 );
5199
5200 /*
5201 * OPTION II:
5202 * Write debug text to DBMS output file.
5203 */
5204 --DBMS_OUTPUT.PUT_LINE(substr(RPAD(p_module,40)||' : '||
5205 -- p_debug_text, 0, 150));
5206
5207 /*
5208 * OPTION III:
5209 * Write debug text to temporary table.
5210 *
5211 * Use this script to create a debug table.
5212 * CREATE TABLE TEMP_IBY_LOGS(TEXT VARCHAR2(4000), TIME DATE);
5213 */
5214 /* uncomment these two lines for debugging */
5215 --INSERT INTO TEMP_IBY_LOGS VALUES (p_module || ': '
5216 -- || p_debug_text, sysdate);
5217
5218 --COMMIT;
5219
5220 ELSE
5221
5222 /*
5223 * OPTION I:
5224 * Write debug text to the concurrent manager log file.
5225 */
5226 FND_FILE.PUT_LINE(FND_FILE.LOG, p_module || ': ' || p_debug_text);
5227
5228 /*
5229 * OPTION II:
5230 * Write debug text to DBMS output file.
5231 */
5232 --DBMS_OUTPUT.PUT_LINE(substr(RPAD(p_module,40)||' : '||
5233 -- p_debug_text, 0, 150));
5234
5235 /*
5236 * OPTION III:
5237 * Write debug text to temporary table.
5238 *
5239 * Use this script to create a debug table.
5240 * CREATE TABLE TEMP_IBY_LOGS(TEXT VARCHAR2(4000), TIME DATE);
5241 */
5242 /* uncomment these two lines for debugging */
5243 --INSERT INTO TEMP_IBY_LOGS VALUES (p_module || ': '
5244 -- || p_debug_text, sysdate);
5245
5246 --COMMIT;
5247
5248 END IF;
5249
5250 END print_debuginfo;
5251
5252 /*--------------------------------------------------------------------
5253 | NAME:
5254 | getNextBatchId
5255 |
5256 | PURPOSE:
5257 | Returns the next batch id from a sequence. These ids are
5258 | used to uniquely number batches.
5259 |
5260 | PARAMETERS:
5261 | IN
5262 |
5263 |
5264 | OUT
5265 |
5266 |
5267 | RETURNS:
5268 |
5269 | NOTES:
5270 |
5271 *---------------------------------------------------------------------*/
5272 PROCEDURE getNextBatchId(
5273 x_batchID IN OUT NOCOPY IBY_BATCHES_ALL.batchid%TYPE
5274 )
5275 IS
5276
5277 BEGIN
5278
5279 SELECT IBY_BATCHID_S.NEXTVAL INTO x_batchID
5280 FROM DUAL;
5281
5282 END getNextBatchId;
5283
5284 -- Checks if a row already exists in transactions table
5285 -- based on the values passed. This function is called by
5286 -- for query batch and query transaction operations to check
5287 -- before inserting a row.
5288
5289 FUNCTION checkrows
5290 (order_id_in IN iby_transactions_v.order_id%TYPE,
5291 merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
5292 vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
5293
5294 status_in IN iby_transactions_v.status%TYPE,
5295 trxn_type_in IN iby_transactions_v.trxn_type%TYPE)
5296 RETURN number
5297
5298 IS
5299
5300 l_numrows NUMBER := 0;
5301 l_needupdate boolean;
5302 l_tx1 iby_transactions_v.trxn_type%TYPE;
5303 l_tx2 iby_transactions_v.trxn_type%TYPE;
5304
5305 CURSOR c_getNumTrxns(tx1 NUMBER,
5306 tx2 NUMBER DEFAULT NULL)
5307 IS
5308 SELECT count(*)
5309 FROM iby_transactions_v
5310 WHERE order_Id = order_id_in
5311 AND merchant_id = merchant_id_in
5312 AND vendor_id = vendor_id_in
5313 AND status = status_in
5314 AND trxn_type = tx1;
5315 --AND trxn_type IN (tx1, tx2);
5316 --AND trxn_type IS NOT NULL; -- add this for 'other operations' case
5317
5318 BEGIN
5319 IF (c_getNumTrxns%isopen) THEN
5320 close c_getNumTrxns;
5321 END IF;
5322
5323 -- no longer distinguish different trxntypeid returned by
5324 -- Cybercash, considering 'closeBatch' effect
5325 /*
5326 IF (trxn_type_in = 8 or trxn_type_in = 9) THEN
5327 -- capture operations
5328 l_needupdate := true;
5329 open c_getNumTrxns(8, 9);
5330
5331 ELSIF (trxn_type_in = 5 or trxn_type_in = 10) THEN
5332 -- return operations
5333 l_needupdate := true;
5334 l_tx1 := 5;
5335 l_tx2 := 10;
5336 open c_getNumTrxns(5, 10);
5337 ELSIF (trxn_type_in = 13 or trxn_type_in = 14) THEN
5338 -- void capture
5339 l_needupdate := true;
5340 l_tx1 := 13;
5341 l_tx2 := 14;
5342 open c_getNumTrxns(13, 14);
5343 ELSIF (trxn_type_in = 17 or trxn_type_in = 18) THEN
5344 -- void return
5345 l_needupdate := true;
5346 l_tx1 := 17;
5347 l_tx2 := 18;
5348 open c_getNumTrxns(17, 18);
5349 ELSE
5350 -- other operations
5351 l_needupdate := false;
5352 open c_getNumTrxns(trxn_type_in);
5353 END IF;
5354
5355 */
5356
5357 open c_getNumTrxns(trxn_type_in);
5358
5359 FETCH c_getNumTrxns INTO l_numrows;
5360 /* IF (l_numrows > 0 AND l_needupdate) THEN
5361 UPDATE iby_trxn_summaries_all
5362 SET trxntypeid = trxn_type_in
5363 WHERE tangibleid = order_id_in
5364 AND payeeid = merchant_id_in
5365 AND bepid = vendor_id_in
5366 AND status = status_in
5367 AND trxntypeid IN (l_tx1, l_tx2);
5368 END IF; */
5369
5370 CLOSE c_getNumTrxns;
5371
5372 RETURN l_numrows;
5373
5374 END checkrows;
5375
5376
5377 /* Inserts the transaction record for the closebatch operation */
5378
5379 PROCEDURE insert_batch_txn
5380 (ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
5381
5382 order_id_in IN iby_transactions_v.order_id%TYPE,
5383 merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
5384 merch_batchid_in IN iby_transactions_v.MerchBatchID%TYPE,
5385 vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
5386 vendor_key_in IN iby_transactions_v.bepkey%TYPE,
5387 status_in IN iby_transactions_v.status%TYPE,
5388 time_in IN iby_transactions_v.time%TYPE,
5389 trxn_type_in IN iby_transactions_v.trxn_type%TYPE,
5390 vendor_code_in IN iby_transactions_v.vendor_code%TYPE,
5391 vendor_message_in IN iby_transactions_v.vendor_message%TYPE,
5392 error_location_in IN iby_transactions_v.error_location%TYPE,
5393
5394 trace_number_in IN iby_transactions_v.TraceNumber%TYPE,
5395 org_id_in IN iby_trxn_summaries_all.org_id%type,
5396 transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE)
5397
5398 IS
5399
5400 num_rows NUMBER;
5401 l_trxn_mid NUMBER;
5402 transaction_id NUMBER;
5403
5404 l_mpayeeid iby_payee.mpayeeid%type;
5405 l_mbatchid iby_batches_all.mbatchid%type;
5406 l_mtangibleid iby_tangible.mtangibleid%type;
5407
5408 l_prev_trxn_count number;
5409 l_reqtype iby_trxn_summaries_all.reqtype%type;
5410 l_instrtype iby_trxn_summaries_all.instrtype%type;
5411 l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
5412 BEGIN
5413
5414 -- Update the existing row for this order id with merchant batch id
5415 -- Only the transaction types auth,authcapture,return,capture,markcapture
5416 -- and markreturn are taken into account.
5417
5418 -- capture/markedcapture
5419 -- mark the authonly, authcapture, capture and Markcapture with the batchid
5420 IF (trxn_type_in = 8 OR trxn_type_in = 9) THEN
5421 l_reqtype := 'ORAPMTCAPTURE';
5422 --dbms_output.put_line('position 1');
5423 getMBatchId(merch_batchid_in, merchant_id_in, l_mbatchid);
5424 UPDATE iby_trxn_summaries_all
5425 SET BatchID = merch_batchid_in,
5426 MBatchID = l_mbatchid,
5427 last_update_date=sysdate,
5428 updatedate = sysdate,
5429 last_updated_by = fnd_global.user_id,
5430 creation_date = sysdate,
5431 created_by = fnd_global.user_id,
5432 object_version_number = object_version_number + 1
5433 WHERE TangibleID = order_id_in
5434 AND PayeeID = merchant_id_in
5435 AND TrxntypeID IN (2, 3, 8,9)
5436 AND Status = 0;
5437
5438 -- For return/credit transaction type, mark the return, MarkReturn
5439 -- transaction type with batchid
5440 ELSIF (trxn_type_in = 5 OR trxn_type_in = 10) THEN
5441 l_reqtype := 'ORAPMTRETURN';
5442 --dbms_output.put_line('position 2');
5443 getMBatchId(merch_batchid_in, merchant_id_in, l_mbatchid);
5444
5445 UPDATE iby_trxn_summaries_all
5446
5447 SET BatchID = merch_batchid_in,
5448 MBatchID = l_mbatchid,
5449 last_update_date=sysdate,
5450 updatedate = sysdate,
5451 last_updated_by = fnd_global.user_id,
5452 creation_date = sysdate,
5453 created_by = fnd_global.user_id,
5454 object_version_number = object_version_number + 1
5455 WHERE TangibleID = order_id_in
5456 AND PayeeID = merchant_id_in
5457 AND TrxntypeID IN (5, 10)
5458 AND Status = 0;
5459
5460 END IF;
5461
5462 num_rows := checkrows(order_id_in, merchant_id_in, vendor_id_in,
5463 status_in, trxn_type_in);
5464
5465 --dbms_output.put_line('position 3 ' || num_rows);
5466
5467
5468 IF num_rows = 0 THEN
5469 -- Now insert a new row for this transaction in the batch
5470 -- Get the transaction id first
5471 SELECT count(*)
5472 INTO l_prev_trxn_count
5473 FROM iby_trxn_summaries_all
5474 WHERE tangibleid = order_id_in AND
5475 payeeid = merchant_id_in;
5476
5477 IF (l_prev_trxn_count = 0) THEN
5478 -- this happens when previous trxn wasn't recorded in
5479 -- payment server, e.g, Aalok/Jonathan's testing --jjwu
5480 -- what about mtangibleid ???
5481 transaction_id_out := getTID(merchant_id_in, order_id_in);
5482 l_mtangibleid := -1;
5483 ELSE
5484 SELECT DISTINCT transactionid, mtangibleid, instrtype, instrsubtype
5485 INTO transaction_id_out, l_mtangibleid, l_instrtype, l_instrsubtype
5486 FROM iby_trxn_summaries_all
5487 WHERE tangibleid = order_id_in AND
5488 payeeid = merchant_id_in
5489 AND status = 0;
5490 END IF;
5491
5492 --dbms_output.put_line('position 4');
5493 -- Get the master transaction id for this record
5494
5495 SELECT iby_trxnsumm_mid_s.NEXTVAL
5496 INTO l_trxn_mid
5497 FROM dual;
5498
5499 --dbms_output.put_line('position 5');
5500
5501 getMBatchId(merch_batchid_in, merchant_id_in, l_mbatchid);
5502 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
5503
5504
5505 -- this insertion from close/query batch is problematic
5506 -- 1) there is no corresponding entry in ibY_trxn_core table
5507 -- 2) mtangible id might not be correct, there might not be
5508 -- an entry in iby_tangible table
5509 -- 3) currency, amount is missing
5510 -- regardless, this should be a rare case, it exists because
5511 -- 1) Cybercash doesn't return 'trxntypeid' for capture/return
5512 -- accurately
5513 -- 2) during testing, there are trxns submitted directly to bep
5514 -- (not through iPayment)
5515 -- 3) during testing, requests submitted to Cybercash regular
5516 -- and Cybercash SSL are considered different, but during close
5517 -- batch, they are mixed.
5518 INSERT INTO iby_trxn_summaries_all
5519 (TrxnMID, TransactionID,TangibleID,MPayeeID, PayeeID,BEPID, bepKey,
5520 ECAppID,org_id, Status, UpdateDate,TrxnTypeID, MBatchID, BatchID,
5521 BEPCode,BEPMessage,Errorlocation,
5522 ReqType, ReqDate, mtangibleid,
5523 last_update_date,last_updated_by,creation_date,created_by,
5524
5525 last_update_login,object_version_number,instrType,instrsubtype,needsupdt)
5526 VALUES (l_trxn_mid, transaction_id_out,
5527 order_id_in, l_mpayeeid, merchant_id_in, vendor_id_in,
5528 vendor_key_in, ecapp_id_in, org_id_in, status_in, time_in,
5529 trxn_type_in, l_mbatchid, merch_batchid_in,
5530 vendor_code_in, vendor_message_in, error_location_in,
5531 l_reqtype, sysdate, l_mtangibleid,
5532 sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
5533 fnd_global.login_id,1,l_instrtype,l_instrsubtype,'Y');
5534 --dbms_output.put_line('position 6');
5535 ELSE
5536
5537 -- retrieve existing tid out
5538 SELECT distinct(transactionid)
5539 INTO transaction_id_out
5540 FROM iby_trxn_summaries_all
5541 WHERE tangibleid = order_id_in
5542 AND payeeid = merchant_id_in
5543 AND bepid = vendor_id_in
5544 AND trxntypeid = trxn_type_in
5545 AND status = status_in;
5546
5547 END IF;
5548
5549 COMMIT;
5550 END insert_batch_txn;
5551
5552
5553 /* Inserts transaction record for transaction query operation */
5554
5555 PROCEDURE insert_query_txn
5556 (transaction_id_in IN iby_trxn_summaries_all.TransactionID%TYPE,
5557 order_id_in IN iby_transactions_v.order_id%TYPE,
5558 merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
5559 vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
5560 vendor_key_in IN iby_transactions_v.bepkey%TYPE,
5561 status_in IN iby_transactions_v.status%TYPE,
5562 time_in IN iby_transactions_v.time%TYPE DEFAULT sysdate,
5563 trxn_type_in IN iby_transactions_v.trxn_type%TYPE,
5564 amount_in IN iby_transactions_v.amount%TYPE DEFAULT NULL,
5565 currency_in IN iby_transactions_v.currency%TYPE DEFAULT NULL,
5566 payment_name_in IN iby_transactions_v.payment_name%TYPE DEFAULT NULL,
5567 authcode_in IN iby_transactions_v.authcode%TYPE DEFAULT NULL,
5568 referencecode_in IN iby_transactions_v.referencecode%TYPE DEFAULT NULL,
5569 avscode_in IN iby_transactions_v.AVScode%TYPE DEFAULT NULL,
5570 acquirer_in IN iby_transactions_v.acquirer%TYPE DEFAULT NULL,
5571 auxmsg_in IN iby_transactions_v.Auxmsg%TYPE DEFAULT NULL,
5572 vendor_code_in IN iby_transactions_v.vendor_code%TYPE DEFAULT NULL,
5573 vendor_message_in IN iby_transactions_v.vendor_message%TYPE DEFAULT NULL,
5574 error_location_in IN iby_transactions_v.error_location%TYPE DEFAULT NULL,
5575 trace_number_in IN iby_transactions_v.TraceNumber%TYPE DEFAULT NULL,
5576 org_id_in IN iby_trxn_summaries_all.org_id%type DEFAULT NULL,
5577 ecappid_in IN iby_ecapp.ecappid%type,
5578 req_type_in IN iby_trxn_summaries_all.reqtype%type)
5579 IS
5580
5581 num_rows NUMBER;
5582 l_trxn_mid NUMBER;
5583 l_mpayeeid iby_payee.mpayeeid%type;
5584 l_mtangibleid iby_trxn_summaries_all.mtangibleid%type;
5585 l_cnt number;
5586 l_instrtype iby_trxn_summaries_all.instrtype%type;
5587 l_instrsubtype iby_trxn_summaries_all.instrsubtype%type;
5588 l_trxnref iby_trxn_summaries_all.trxnref%type;
5589
5590 CURSOR c_trxnmid(
5591 ci_trxnid iby_trxn_summaries_all.TransactionID%TYPE,
5592 ci_merchid iby_trxn_summaries_all.PayeeId%TYPE,
5593 --ci_trxntype iby_trxn_summaries_all.TrxnTypeId%TYPE,
5594 ci_reqtype iby_trxn_summaries_all.ReqType%TYPE,
5595 ci_status iby_trxn_summaries_all.Status%TYPE
5596 )
5597 IS
5598 SELECT trxnmid
5599 FROM iby_trxn_summaries_all
5600 WHERE (status = ci_status)
5601 AND (payeeid = ci_merchid)
5602 AND (transactionid = ci_trxnid)
5603 AND (reqtype = ci_reqtype);
5604
5605 CURSOR c_order_info
5606 (
5607 ci_orderid iby_transactions_v.order_id%TYPE,
5608 ci_merchid iby_transactions_v.merchant_id%TYPE
5609 )
5610 IS
5611 SELECT mtangibleid, instrtype, instrsubtype, trxnref
5612 FROM iby_trxn_summaries_all
5613 WHERE (tangibleid = ci_orderid)
5614 AND (payeeid = ci_merchid)
5615 AND (mtangibleid <> -1)
5616 ORDER BY reqdate DESC;
5617
5618 BEGIN
5619
5620 IF (c_trxnmid%ISOPEN) THEN
5621 CLOSE c_trxnmid;
5622 END IF;
5623
5624 IF (c_order_info%ISOPEN) THEN
5625 CLOSE c_order_info;
5626 END IF;
5627
5628 -- find any transitional trxns and update them
5629 --
5630 OPEN c_trxnmid(transaction_id_in,merchant_id_in,req_type_in,9);
5631 FETCH c_trxnmid INTO l_trxn_mid;
5632
5633 IF (c_trxnmid%NOTFOUND) THEN
5634 CLOSE c_trxnmid;
5635 ELSE
5636 -- a transitional trxn (cut off in mid-process)
5637 -- exists ; update it instead of adding a new
5638 -- row
5639 CLOSE c_trxnmid;
5640
5641 UPDATE iby_trxn_summaries_all
5642 SET ReqDate = NVL(time_in,reqdate),
5643 --Amount = amount_in,
5644 --CurrencyNameCode = currency_in,
5645 UpdateDate = NVL(time_in,updatedate),
5646 Status = status_in,
5647 BEPCode = vendor_code_in,
5648 BEPMessage = vendor_message_in,
5649 Errorlocation = error_location_in,
5650 instrType = NVL(l_instrtype,instrType),
5651 instrsubtype = NVL(l_instrsubtype,instrsubtype),
5652 --org_id = org_id_in,
5653 last_update_date = sysdate,
5654 last_updated_by = fnd_global.user_id,
5655 last_update_login = fnd_global.login_id,
5656 object_version_number = object_version_number+1
5657 WHERE (trxnmid = l_trxn_mid);
5658
5659 UPDATE iby_trxn_core
5660 SET AuthCode = authcode_in,
5661 ReferenceCode = referencecode_in,
5662 AVSCode = avscode_in,
5663 Acquirer = acquirer_in,
5664 Auxmsg = auxmsg_in,
5665 TraceNumber = trace_number_in,
5666 InstrName = NVL(payment_name_in,instrname),
5667 last_update_date = sysdate,
5668 last_updated_by = fnd_global.user_id,
5669 last_update_login = fnd_global.login_id,
5670 object_version_number = object_version_number+1
5671 WHERE (trxnmid = l_trxn_mid);
5672
5673 COMMIT;
5674 RETURN;
5675 END IF;
5676
5677 -- Check if row is already in table using unique
5678 -- key of order_id, merchant_id, vendor_id, transaction_type
5679 -- and status. We dont want to update those
5680 -- rows which have non-zero status. We would like to save
5681 -- them for history
5682
5683 -- collate differnt trxn types as certain gateways may be returning
5684 -- codes different from the value stored in trnxtypeid
5685 --
5686 IF (trxn_type_in = 8 or trxn_type_in = 9) THEN
5687 num_rows := checkrows(order_id_in,merchant_id_in,vendor_id_in,status_in,8);
5688 num_rows := num_rows + checkrows(order_id_in,merchant_id_in,vendor_id_in,status_in,9);
5689 ELSIF (trxn_type_in = 5 or trxn_type_in = 10) THEN
5690 num_rows := checkrows(order_id_in,merchant_id_in,vendor_id_in,status_in,5);
5691 num_rows := num_rows + checkrows(order_id_in,merchant_id_in,vendor_id_in,status_in,10);
5692 ELSIF (trxn_type_in = 13 or trxn_type_in = 14) THEN
5693 num_rows := checkrows(order_id_in,merchant_id_in,vendor_id_in,status_in,13);
5694 num_rows := num_rows + checkrows(order_id_in,merchant_id_in,vendor_id_in,status_in,14);
5695 ELSIF (trxn_type_in = 17 or trxn_type_in = 18) THEN
5696 num_rows := checkrows(order_id_in,merchant_id_in,vendor_id_in,status_in,17);
5697 num_rows := num_rows + checkrows(order_id_in,merchant_id_in,vendor_id_in,status_in,18);
5698 ELSE
5699 num_rows := num_rows + checkrows(order_id_in,merchant_id_in,vendor_id_in,status_in,trxn_type_in);
5700 END IF;
5701
5702 -- If no record found insert this row otherwise no need to update
5703 IF num_rows = 0
5704 THEN
5705
5706 SELECT iby_trxnsumm_mid_s.NEXTVAL
5707 INTO l_trxn_mid
5708 FROM dual;
5709
5710 iby_accppmtmthd_pkg.getMPayeeId(merchant_id_in, l_mpayeeid);
5711
5712
5713 -- try to get mtangibleid
5714 select count(*) into l_cnt from iby_trxn_summaries_all
5715 where tangibleid = order_id_in and
5716 payeeid = merchant_id_in and
5717 mtangibleid <> -1;
5718
5719 -- no -1:
5720 -- see insert_batch_txn, we want to ignore trxns done directly
5721 -- on BEP, w/o going through iPayment
5722 -- and other unknown cases
5723
5724 if (l_cnt > 0) then
5725 OPEN c_order_info(order_id_in,merchant_id_in);
5726 FETCH c_order_info INTO l_mtangibleid, l_instrtype, l_instrsubtype,
5727 l_trxnref;
5728 CLOSE c_order_info;
5729 else
5730 -- I can not get mtangibleid from the iby_tangible table
5731 -- because it is missing the payee info
5732 l_mtangibleid := -1;
5733 end if;
5734
5735 INSERT INTO iby_trxn_summaries_all
5736 (ECAppID, TrxnMID, TransactionID,TrxntypeID, ReqDate, ReqType,
5737 Amount,CurrencyNameCode, UpdateDate,Status,
5738 TangibleID,MPayeeID, PayeeID,BEPID, bepKey, MTangibleID,
5739 BEPCode,BEPMessage,Errorlocation, org_id,
5740 last_update_date,last_updated_by,creation_date,created_by,
5741 last_update_login,object_version_number,instrType,instrsubtype,trxnref,needsupdt)
5742 VALUES (ecappid_in, l_trxn_mid, transaction_id_in, trxn_type_in,
5743 time_in, req_type_in,
5744 amount_in, currency_in, time_in, status_in,
5745 order_id_in, l_mpayeeid, merchant_id_in, vendor_id_in,
5746 vendor_key_in, l_mtangibleid,
5747 vendor_code_in, vendor_message_in, error_location_in, org_id_in,
5748 sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
5749 fnd_global.login_id,1,l_instrtype,l_instrsubtype,l_trxnref,'Y');
5750
5751 INSERT INTO iby_trxn_core
5752 (TrxnMID, AuthCode, ReferenceCode, AVSCode, Acquirer, Auxmsg,
5753 TraceNumber, InstrName,
5754 last_update_date,last_updated_by,creation_date,created_by,last_update_login,
5755
5756 object_version_number)
5757 VALUES (l_trxn_mid, authcode_in,referencecode_in,avscode_in, acquirer_in,
5758 auxmsg_in, trace_number_in, payment_name_in,
5759
5760 sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.login_id,1);
5761
5762 END IF;
5763
5764 COMMIT;
5765
5766
5767 END insert_query_txn;
5768
5769
5770 /* updates the statuses of trxns saved in a batch */
5771 PROCEDURE updateBatchedTrxns
5772 (
5773 payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
5774 bepid_in IN iby_trxn_summaries_all.bepid%TYPE,
5775 bepkey_in IN iby_trxn_summaries_all.bepkey%TYPE,
5776 oldstatus_in IN iby_trxn_summaries_all.status%TYPE,
5777 newstatus_in IN iby_trxn_summaries_all.status%TYPE,
5778 oldbatchid_in IN iby_trxn_summaries_all.batchid%TYPE,
5779 newbatchid_in IN iby_trxn_summaries_all.batchid%TYPE
5780 )
5781 IS
5782 l_mbatchid iby_trxn_summaries_all.mbatchid%TYPE;
5783 BEGIN
5784
5785 BEGIN
5786 getMBatchId(oldbatchid_in, payeeid_in, l_mbatchid);
5787 --
5788 -- catch exception in the case where the batch id has not been
5789 -- stored yet in IBY_BATCHES_ALL ; this can happen when we
5790 -- want to change the status of transitional trxns
5791 --
5792 EXCEPTION WHEN others THEN
5793 l_mbatchid := NULL;
5794 END;
5795
5796 UPDATE iby_trxn_summaries_all
5797 SET
5798 status = newstatus_in,
5799 batchid = newbatchid_in,
5800 mbatchid = l_mbatchid,
5801 last_update_date = sysdate,
5802 updatedate = sysdate,
5803 last_updated_by = fnd_global.user_id,
5804 object_version_number = object_version_number + 1
5805 WHERE (bepid = bepid_in)
5806 AND (bepkey = bepkey_in)
5807 AND (payeeid = payeeid_in)
5808 AND (status = oldstatus_in)
5809 AND ((instrtype='CREDITCARD') OR (instrtype='PURCHASECARD') OR (instrtype IS NULL))
5810 AND ((batchid IS NULL AND oldbatchid_IN IS NULL) OR (batchid = oldbatchid_in));
5811
5812 COMMIT;
5813
5814 END updateBatchedTrxns;
5815
5816
5817 /* Regular version of this method. */
5818 PROCEDURE updateBatchQueryTrxn
5819 (
5820 payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
5821 orderid_in IN iby_trxn_summaries_all.batchid%TYPE,
5822 trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
5823 batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
5824 status_in IN iby_trxn_summaries_all.status%TYPE,
5825 bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE,
5826 bep_msg_in IN iby_trxn_summaries_all.bepmessage%TYPE,
5827 error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
5828 trxnid_out OUT NOCOPY iby_trxn_summaries_all.transactionid%TYPE
5829 )
5830 IS
5831 BEGIN
5832
5833 UPDATE
5834 iby_trxn_summaries_all
5835
5836 SET
5837 status=status_in,
5838 --
5839 -- only change these values if they have non-trivial values
5840 --
5841 bepcode=DECODE(NVL(bep_code_in,''), '',bepcode, bep_code_in),
5842 bepmessage=DECODE(NVL(bep_msg_in,''), '',bepmessage, bep_msg_in),
5843 errorlocation=DECODE(NVL(error_loc_in,''), '',errorlocation, error_loc_in),
5844 last_update_date = sysdate,
5845 last_updated_by = fnd_global.user_id,
5846 object_version_number = object_version_number + 1
5847
5848 WHERE
5849
5850 --
5851 -- Where clause modified to support
5852 -- returns (transaction type 5):
5853 -- Returns(5) and credits(11) are to be
5854 -- treated as equivalents because in the
5855 -- processor model we send return transactions
5856 -- as credits. When we query for the status of
5857 -- such a credit, we must remember to update the
5858 -- status of the original return.
5859 --
5860
5861 (payeeid = payeeid_in) AND
5862 (tangibleid = orderid_in) AND
5863 (trxntypeid IN (trxn_type_in, 5)) AND
5864 (batchid = batchid_in);
5865
5866 IF (SQL%NOTFOUND) THEN
5867 trxnid_out := -1;
5868 ELSE
5869 trxnid_out := getTID(payeeid_in, orderid_in);
5870 END IF;
5871
5872 COMMIT;
5873
5874 END updateBatchQueryTrxn;
5875
5876
5877 /* auth version of the method. */
5878 PROCEDURE updateBatchQueryTrxn
5879 (
5880 payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
5881 orderid_in IN iby_trxn_summaries_all.batchid%TYPE,
5882 trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
5883 batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
5884 status_in IN iby_trxn_summaries_all.status%TYPE,
5885 bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE,
5886 bep_msg_in IN iby_trxn_summaries_all.bepmessage%TYPE,
5887 error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
5888 authcode_in IN iby_trxn_core.authcode%TYPE,
5889 avscode_in IN iby_trxn_core.avscode%TYPE,
5890 cvv2result_in IN iby_trxn_core.cvv2result%TYPE,
5891 trxnid_out OUT NOCOPY iby_trxn_summaries_all.transactionid%TYPE
5892 )
5893 IS
5894 l_trxnmid iby_trxn_summaries_all.trxnmid%TYPE;
5895 BEGIN
5896
5897 updateBatchQueryTrxn(payeeid_in,orderid_in,trxn_type_in,batchid_in,status_in,bep_code_in,bep_msg_in,error_loc_in,trxnid_out);
5898
5899 SELECT
5900 trxnmid
5901 INTO
5902 l_trxnmid
5903 FROM
5904 iby_trxn_summaries_all
5905 WHERE
5906
5907 --
5908 -- Where clause modified to support
5909 -- returns (transaction type 5):
5910 -- Returns(5) and credits(11) are to be
5911 -- treated as equivalents because in the
5912 -- processor model we send return transactions
5913 -- as credits. When we query for the status of
5914 -- such a credit, we must remember to update the
5915 -- status of the original return.
5916 --
5917
5918 (transactionid = trxnid_out) AND
5919 (payeeid = payeeid_in) AND
5920 (trxntypeid IN (trxn_type_in, 5));
5921
5922 IF (SQL%NOTFOUND) THEN
5923 RETURN;
5924 END IF;
5925
5926 UPDATE
5927 iby_trxn_core
5928 SET
5929 authcode=authcode_in,
5930 cvv2result=cvv2result_in,
5931 avscode=avscode_in
5932 WHERE
5933 (trxnmid=l_trxnmid);
5934
5935 COMMIT;
5936
5937 END updateBatchQueryTrxn;
5938
5939 PROCEDURE Update_Batch
5940 (
5941 ecapp_id_in IN iby_batches_all.ecappid%TYPE,
5942 payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
5943 batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
5944 batch_status_in IN iby_batches_all.batchstatus%TYPE,
5945 batch_total_in IN iby_batches_all.batchtotal%TYPE,
5946 sale_amount_in IN iby_batches_all.batchsales%TYPE,
5947 credit_amount_in IN iby_batches_all.batchcredit%TYPE,
5948 bep_code_in IN iby_batches_all.bepcode%TYPE,
5949 bep_message_in IN iby_batches_all.bepmessage%TYPE,
5950 error_location_in IN iby_batches_all.errorlocation%TYPE,
5951 ack_type_in IN VARCHAR2,
5952 trxn_orderid_in IN JTF_VARCHAR2_TABLE_100,
5953 trxn_reqtype_in IN JTF_VARCHAR2_TABLE_100,
5954 trxn_status_in IN JTF_VARCHAR2_TABLE_100,
5955 trxn_bep_code_in IN JTF_VARCHAR2_TABLE_100,
5956 trxn_bep_msg_in IN JTF_VARCHAR2_TABLE_100,
5957 trxn_error_loc_in IN JTF_VARCHAR2_TABLE_100,
5958 trxn_authcode_in IN JTF_VARCHAR2_TABLE_100,
5959 trxn_avscode_in IN JTF_VARCHAR2_TABLE_100,
5960 trxn_cvv2result_in IN JTF_VARCHAR2_TABLE_100,
5961 trxn_tracenumber IN JTF_VARCHAR2_TABLE_100
5962 )
5963 IS
5964 l_tmid iby_trxn_summaries_all.trxnmid%TYPE;
5965 l_tid iby_trxn_summaries_all.transactionid%TYPE;
5966 l_count NUMBER := 0;
5967
5968 CURSOR c_tmid
5969 (ci_payeeid iby_trxn_summaries_all.payeeid%TYPE,
5970 ci_orderid iby_trxn_summaries_all.tangibleid%TYPE,
5971 ci_reqtype iby_trxn_summaries_all.reqtype%TYPE,
5972 ci_mbatchid iby_trxn_summaries_all.mbatchid%TYPE
5973 )
5974 IS
5975 SELECT trxnmid, transactionid
5976 FROM iby_trxn_summaries_all
5977 WHERE (payeeid = ci_payeeid)
5978 AND (tangibleid = ci_orderid)
5979 -- ack cannot distinguish between credits and returns
5980 AND (DECODE(reqtype, 'ORAPMTRETURN','ORAPMTCREDIT', reqtype) =
5981 ci_reqtype)
5982 AND (status = iby_transactioncc_pkg.C_STATUS_BATCH_PENDING)
5983 AND (mbatchid = ci_mbatchid);
5984
5985 CURSOR c_tracenumber
5986 (ci_tracenumber iby_trxn_core.tracenumber%TYPE)
5987
5988 IS
5989 SELECT ibc.trxnmid, ibs.transactionid
5990 FROM iby_trxn_summaries_all ibs,
5991 iby_trxn_core ibc
5992 WHERE ibs.trxnmid=ibc.trxnmid
5993 AND ibc.tracenumber=ci_tracenumber;
5994
5995 BEGIN
5996
5997 IF (c_tmid%ISOPEN) THEN CLOSE c_tmid; END IF;
5998 IF (c_tracenumber%ISOPEN) THEN CLOSE c_tracenumber; END IF;
5999
6000 UPDATE iby_batches_all
6001 SET batchstatus = batch_status_in,
6002 batchtotal = batch_total_in,
6003 batchsales = sale_amount_in,
6004 batchcredit = credit_amount_in,
6005 bepcode = bep_code_in,
6006 bepmessage = bep_message_in,
6007 errorlocation = error_location_in,
6008 last_update_date = sysdate,
6009 last_updated_by = fnd_global.user_id,
6010 object_version_number = object_version_number + 1
6011 WHERE (payeeid = payeeid_in)
6012 AND (mbatchid = batchid_in);
6013
6014 -- trxn status data probably not known
6015 -- immediately after batch close
6016 --
6017 IF (trxn_status_in.count=0) THEN
6018 COMMIT;
6019 RETURN;
6020 END IF;
6021
6022
6023 FOR i IN trxn_status_in.FIRST .. trxn_status_in.LAST LOOP
6024
6025 if(NVL(trxn_tracenumber(i), 0)<>0) THEN
6026
6027
6028 OPEN c_tracenumber(trxn_tracenumber(i));
6029 FETCH c_tracenumber into l_tmid, l_tid;
6030
6031 ELSE
6032
6033 OPEN c_tmid(payeeid_in,trxn_orderid_in(i),trxn_reqtype_in(i),batchid_in);
6034 FETCH c_tmid INTO l_tmid, l_tid;
6035
6036 END IF;
6037
6038
6039 --trxnid_out.extend(1);
6040 l_count := l_count +1;
6041
6042 IF (l_tmid IS NOT NULL) THEN
6043 UPDATE iby_trxn_summaries_all
6044 SET status = TO_NUMBER(trxn_status_in(i)),
6045 bepcode = trxn_bep_code_in(i),
6046 bepmessage = trxn_bep_msg_in(i),
6047 errorlocation = TO_NUMBER(trxn_error_loc_in(i)),
6048 last_update_date = sysdate,
6049 last_updated_by = fnd_global.user_id,
6050 object_version_number = object_version_number + 1
6051 WHERE (trxnmid = l_tmid);
6052
6053 UPDATE iby_trxn_core
6054 SET authcode = trxn_authcode_in(i),
6055 avscode = trxn_avscode_in(i),
6056 cvv2result = trxn_cvv2result_in(i),
6057 last_update_date = sysdate,
6058 last_updated_by = fnd_global.user_id,
6059 object_version_number = object_version_number + 1
6060 WHERE (trxnmid = l_tmid);
6061
6062 --trxnid_out(i) := l_tid;
6063 ELSE
6064
6065 --trxnid_out(i) := -1;
6066 null;
6067 END IF;
6068
6069 IF (c_tmid%ISOPEN) THEN
6070 CLOSE c_tmid;
6071 END IF;
6072
6073 IF (c_tracenumber%ISOPEN) THEN
6074 CLOSE c_tracenumber;
6075 END IF;
6076
6077
6078 END LOOP;
6079
6080 -- for some acknowledgements missing transactions are
6081 -- implicitly assumed to have succeeded or failed
6082 --
6083 IF ((ack_type_in = 'P') OR (ack_type_in = 'N')) THEN
6084 UPDATE iby_trxn_summaries_all
6085 SET
6086 status = DECODE(ack_type_in,
6087 'P',C_STATUS_BEP_FAIL,
6088 'N',C_STATUS_SUCCESS,
6089 C_STATUS_OPEN_BATCHED),
6090 last_update_date = sysdate,
6091 last_updated_by = fnd_global.user_id,
6092 object_version_number = object_version_number + 1
6093 WHERE (payeeid = payeeid_in)
6094 AND (batchid = batchid_in)
6095 AND (status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED);
6096 END IF;
6097
6098 COMMIT;
6099 END Update_Batch;
6100
6101
6102 /*
6103 ** Procedure: getMBatchId
6104 ** Purpose: retrieve mBatchid from iby_Batch table based on Batchid
6105 */
6106
6107
6108 Procedure getMBatchId(i_Batchid in iby_Batches_all.Batchid%type,
6109 i_Payeeid in iby_Batches_all.Payeeid%type,
6110 o_mBatchid out nocopy iby_Batches_all.mBatchid%type)
6111 is
6112 cursor c_get_mBatchid(ci_Batchid iby_Batches_All.Batchid%type,
6113 ci_PayeeId iby_batches_all.PayeeID%type)
6114 is
6115 SELECT mBatchid from iby_Batches_All
6116 WHERE Batchid = ci_Batchid
6117 AND PayeeID = ci_PayeeID;
6118 BEGIN
6119
6120 IF (c_get_mbatchid%isopen) THEN
6121 close c_get_mBatchid;
6122 END IF;
6123
6124 open c_get_mBatchid(i_Batchid, i_payeeid);
6125
6126 fetch c_get_mBatchid into o_mBatchid;
6127 if ( c_get_mBatchid%notfound ) then
6128 raise_application_error(-20000, 'IBY_20211#', FALSE);
6129 --raise_application_error(-20211, 'Batchid not found', FALSE);
6130 end if;
6131
6132 close c_get_mBatchid;
6133 END;
6134
6135
6136 /*
6137 for QueryTrxn where there will be no orgid inserted
6138 try to see if there is already some valid org id stored,
6139 if so, use the non-null one first
6140 */
6141 Function getOrgId(i_tid in iby_trxn_summaries_all.transactionid%type)
6142 return number
6143 IS
6144 l_org_id NUMBER := NULL;
6145 l_count NUMBER;
6146
6147 cursor c_getNonNullOrgId(ci_tid
6148 iby_trxn_summaries_all.transactionid%type)
6149
6150 is
6151 SELECT DISTINCT org_id
6152 FROM iby_trxn_summaries_all
6153 WHERE transactionid = i_tid
6154 AND status <> -99 AND status <> 14 -- ignore cancelled trxns
6155 AND org_id IS NOT NULL;
6156
6157 BEGIN
6158
6159 IF (c_getNonNullOrgId%isopen) THEN
6160 close c_getNonNullOrgId;
6161 END IF;
6162
6163 open c_getNonNullOrgId(i_tid);
6164 Fetch c_getNonNullOrgId INTO l_org_id;
6165 -- if not found, l_org_id will remain null
6166 close c_getNonNullOrgId;
6167
6168 return l_org_id;
6169 END;
6170
6171
6172 /* Count number of previous PENDING transactions, ignoring the
6173 cancelled ones
6174 */
6175
6176 Function getNumPendingTrxns(i_payeeid in iby_payee.payeeid%type,
6177 i_tangibleid in iby_tangible.tangibleid%type,
6178 i_reqtype in iby_trxn_summaries_all.reqtype%type)
6179 return number
6180
6181 IS
6182
6183 l_num_trxns number;
6184
6185 BEGIN
6186
6187 SELECT count(*)
6188 INTO l_num_trxns
6189 FROM iby_trxn_summaries_all
6190 WHERE TangibleID = i_tangibleid
6191 AND UPPER(ReqType) = UPPER(i_reqtype)
6192 AND PayeeID = i_payeeid
6193 AND (status IN (11,9));
6194
6195 IF (l_num_trxns > 1) THEN
6196 -- should never run into this block
6197 raise_application_error(-20000, 'IBY_20422#', FALSE);
6198 END IF;
6199
6200 return l_num_trxns;
6201 END;
6202
6203
6204 /* get TID based on orderid */
6205 Function getTID(i_payeeid in iby_payee.payeeid%type,
6206 i_tangibleid in iby_tangible.tangibleid%type)
6207 return number
6208
6209 IS
6210
6211 l_tid number;
6212 cursor c_tid(ci_payeeid in iby_payee.payeeid%type,
6213 ci_tangibleid in iby_tangible.tangibleid%type)
6214 is
6215 SELECT distinct transactionid
6216 FROM iby_trxn_summaries_all
6217 WHERE tangibleid = ci_tangibleid
6218 AND payeeid = ci_payeeid;
6219
6220 BEGIN
6221 if (c_tid%isopen) then
6222 close c_tid;
6223 end if;
6224
6225 open c_tid(i_payeeid, i_tangibleid);
6226 fetch c_tid into l_tid;
6227 if (c_tid%notfound) then
6228 SELECT iby_trxnsumm_trxnid_s.NEXTVAL
6229 INTO l_tid
6230 FROM dual;
6231 end if;
6232
6233 close c_tid;
6234
6235 return l_tid;
6236
6237 END getTID;
6238
6239
6240 /* get TID based on orderid, payeeid (if unique) */
6241 Function getTIDUniqueCheck(i_payeeid in iby_payee.payeeid%type,
6242 i_tangibleid in iby_tangible.tangibleid%type)
6243 return number
6244
6245 IS
6246
6247 l_tid number;
6248 cursor c_tid(ci_payeeid in iby_payee.payeeid%type,
6249 ci_tangibleid in iby_tangible.tangibleid%type)
6250 is
6251 SELECT distinct transactionid
6252 FROM iby_trxn_summaries_all
6253 WHERE tangibleid = ci_tangibleid
6254 AND payeeid = ci_payeeid;
6255
6256 BEGIN
6257 if (c_tid%isopen) then
6258 close c_tid;
6259 end if;
6260
6261 open c_tid(i_payeeid, i_tangibleid);
6262 fetch c_tid into l_tid;
6263
6264 -- If no exception was thrown it means
6265 -- that we found a record in the cursor
6266 -- that matches the given criteria (which
6267 -- further means that there already exists
6268 -- a transaction id for this (orderid, payeeid)
6269 -- combination). So return -1 to indicate that
6270 -- this transaction is non-unique.
6271 l_tid := -1;
6272
6273 if (c_tid%notfound) then
6274 SELECT iby_trxnsumm_trxnid_s.NEXTVAL
6275 INTO l_tid
6276 FROM dual;
6277 end if;
6278
6279 close c_tid;
6280
6281 return l_tid;
6282
6283 END getTIDUniqueCheck;
6284
6285 FUNCTION unencrypt_instr_num
6286 (p_instrnum IN iby_trxn_summaries_all.instrnumber%TYPE,
6287 p_payee_key IN iby_security_pkg.des3_key_type,
6288 p_payee_subkey_cipher IN iby_payee_subkeys.subkey_cipher_text%TYPE,
6289 p_sys_key IN RAW,
6290 p_sys_subkey_cipher IN iby_sys_security_subkeys.subkey_cipher_text%TYPE,
6291 p_segment_id IN iby_security_segments.sec_segment_id%TYPE,
6292 p_segment_cipher IN iby_security_segments.segment_cipher_text%TYPE,
6293 p_card_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
6294 p_card_len IN iby_cc_issuer_ranges.card_number_length%TYPE,
6295 p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE
6296 )
6297 RETURN iby_trxn_summaries_all.instrnumber%TYPE
6298 IS
6299 l_subkey_cleartxt iby_security_pkg.des3_key_type;
6300 l_subkey_raw RAW(24);
6301 l_number iby_trxn_summaries_all.instrnumber%TYPE;
6302 l_number_len NUMBER;
6303 BEGIN
6304
6305 -- handle 2 cases: not encrypted, PCI-encrypted
6306
6307 --
6308 -- PCI-encrypted data from 11i
6309 --
6310 IF (NOT p_segment_id IS NULL) THEN
6311 l_subkey_raw :=
6312 dbms_obfuscation_toolkit.des3decrypt
6313 ( input => p_sys_subkey_cipher , key => p_sys_key,
6314 which => dbms_obfuscation_toolkit.ThreeKeyMode
6315 );
6316 l_number :=
6317 dbms_obfuscation_toolkit.des3decrypt
6318 ( input => p_segment_cipher , key => l_subkey_raw,
6319 which => dbms_obfuscation_toolkit.ThreeKeyMode
6320 );
6321
6322 l_number_len := p_card_len - NVL(LENGTH(p_card_prefix),0);
6323 IF (p_digit_check = 'Y') THEN
6324 l_number_len := l_number_len - 1;
6325 END IF;
6326 -- if the rest of the number is in the unmasked digit, don't bother
6327 -- decompressing
6328 IF ( l_number_len > 0 ) THEN
6329 l_number :=
6330 IBY_SECURITY_PKG.Decode_Number(l_number,l_number_len,TRUE);
6331 ELSE
6332 l_number := '';
6333 END IF;
6334
6335 RETURN
6336 IBY_CREDITCARD_PKG.Uncompress_CC_Number
6337 (l_number,l_number_len,p_card_prefix,p_digit_check,
6338 iby_security_pkg.G_MASK_ALL,0,'');
6339 ELSE
6340 RETURN iby_utility_pvt.decode64(p_instrnum);
6341 END IF;
6342 END unencrypt_instr_num;
6343
6344 PROCEDURE unencrypt_instr_num
6345 (trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
6346 master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
6347 instr_num_out OUT NOCOPY iby_trxn_summaries_all.instrnumber%TYPE
6348 )
6349 IS
6350 l_subkey_cipher iby_payee_subkeys.subkey_cipher_text%TYPE;
6351 l_cipher_instrnum iby_trxn_summaries_all.instrnumber%TYPE;
6352 l_segment_id iby_trxn_summaries_all.instrnum_sec_segment_id%TYPE;
6353 l_segment_cipher iby_security_segments.segment_cipher_text%TYPE;
6354 l_sys_subkey_cipher iby_sys_security_subkeys.subkey_cipher_text%TYPE;
6355 l_card_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE;
6356 l_card_length iby_cc_issuer_ranges.card_number_length%TYPE;
6357 l_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE;
6358
6359 CURSOR c_instr_num(ci_trxnmid iby_trxn_summaries_all.trxnmid%TYPE)
6360 IS
6361 SELECT NULL, tx.instrnumber,
6362 tx.instrnum_sec_segment_id, k.subkey_cipher_text,
6363 seg.segment_cipher_text, r.card_number_prefix,
6364 NVL(r.card_number_length,tx.instrnum_length), i.digit_check_flag
6365 FROM iby_trxn_summaries_all tx,
6366 iby_security_segments seg, iby_sys_security_subkeys k,
6367 iby_cc_issuer_ranges r, iby_creditcard_issuers_b i
6368 WHERE (tx.trxnmid = ci_trxnmid)
6369 AND (tx.instrnum_sec_segment_id = seg.sec_segment_id(+))
6370 AND (seg.sec_subkey_id = k.sec_subkey_id(+))
6371 AND (tx.cc_issuer_range_id = r.cc_issuer_range_id(+))
6372 AND (tx.instrsubtype = i.card_issuer_code(+));
6373 BEGIN
6374
6375 IF (c_instr_num%ISOPEN) THEN close c_instr_num; END IF;
6376
6377 OPEN c_instr_num(trxnmid_in);
6378 FETCH c_instr_num
6379 INTO l_subkey_cipher, l_cipher_instrnum, l_segment_id, l_sys_subkey_cipher,
6380 l_segment_cipher, l_card_prefix, l_card_length, l_digit_check;
6381
6382 IF (c_instr_num%NOTFOUND) THEN
6383 CLOSE c_instr_num;
6384 raise_application_error(-20000, 'IBY_204463#', FALSE);
6385 ELSE
6386 CLOSE c_instr_num;
6387 END IF;
6388 instr_num_out :=
6389 unencrypt_instr_num
6390 (l_cipher_instrnum, NULL, l_subkey_cipher, master_key_in,
6391 l_sys_subkey_cipher, l_segment_id, l_segment_cipher, l_card_prefix,
6392 l_card_length, l_digit_check);
6393 END unencrypt_instr_num;
6394
6395
6396 FUNCTION unencrypt_instr_num
6397 (
6398 trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
6399 master_key_in IN iby_security_pkg.DES3_KEY_TYPE
6400 )
6401 RETURN iby_trxn_summaries_all.instrnumber%TYPE
6402 IS
6403 l_instrnum iby_trxn_summaries_all.instrnumber%TYPE;
6404 BEGIN
6405 unencrypt_instr_num(trxnmid_in,master_key_in,l_instrnum);
6406 RETURN l_instrnum;
6407 END unencrypt_instr_num;
6408
6409 FUNCTION unencrypt_instr_num_ui_wrp
6410 (
6411 trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
6412 master_key_in IN iby_security_pkg.DES3_KEY_TYPE
6413 )
6414 RETURN iby_trxn_summaries_all.instrnumber%TYPE
6415 IS
6416 l_instrnum iby_trxn_summaries_all.instrnumber%TYPE;
6417 BEGIN
6418 l_instrnum := unencrypt_instr_num(trxnmid_in,master_key_in);
6419 RETURN l_instrnum;
6420
6421 EXCEPTION
6422 WHEN OTHERS THEN
6423 return null;
6424
6425 END unencrypt_instr_num_ui_wrp;
6426
6427 PROCEDURE Encrypt_CC_Data
6428 (p_sys_key IN IBY_SECURITY_PKG.DES3_KEY_TYPE, x_err_code OUT NOCOPY VARCHAR2)
6429 IS
6430 -- types
6431 TYPE Num15Tab IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
6432 TYPE Char60Tab IS TABLE OF VARCHAR2(60) INDEX BY BINARY_INTEGER;
6433
6434 -- bulk tables
6435 l_trxnmid_tbl Num15Tab;
6436 l_maskedcc_tbl Char60Tab;
6437 l_issuer_tbl Char60Tab;
6438 l_cchash_tbl Char60Tab;
6439 l_rangeid_tbl Num15Tab;
6440 l_instrlen_tbl Num15Tab;
6441 l_segmentid_tbl Num15Tab;
6442
6443 l_index NUMBER;
6444
6445 -- variables for CHNAME and EXPDATE encryption
6446 l_chname_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
6447 l_expdate_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
6448 l_masked_chname VARCHAR2(100) := NULL;
6449 l_encrypted VARCHAR2(1) := 'N';
6450
6451 CURSOR c_tx_ccnumber
6452 IS
6453 SELECT /*+ rowid(tx) */
6454 tx.trxnmid,
6455 iby_utility_pvt.decode64(instrnumber) ccnum,
6456 tx.instrtype
6457 FROM iby_trxn_summaries_all tx
6458 WHERE
6459 (NOT instrnumber IS NULL)
6460 AND (DECODE(instrtype, 'PINLESSDEBITCARD','CREDITCARD',
6461 'PURCHASECARD','CREDITCARD',
6462 -- instrument type will be NULL for credit card credit trxns
6463 NULL,DECODE(reqtype,
6464 'ORAPMTCREDIT','CREDITCARD',
6465 NULL),
6466 instrtype) = 'CREDITCARD')
6467 AND (instrnum_sec_segment_id IS NULL);
6468
6469
6470 -- The below cursor will fetch all un-encrypted rows from
6471 -- IBY_TRXN_CORE table. Though we could probably have merged this
6472 -- cursor with the earlier one, we maintain a separate one since
6473 -- there could be records in this table that are un-encrypted but the
6474 -- corresponding records in iby_trxn_summaries_all are encrypted.
6475 CURSOR c_trxn_core
6476 IS
6477 SELECT
6478 tx.trxnmid tmid,
6479 core.instr_expirydate expdate,
6480 core.instr_owner_name chname
6481 FROM iby_trxn_summaries_all tx,
6482 iby_trxn_core core
6483 WHERE
6484 (DECODE(tx.instrtype, 'PINLESSDEBITCARD','CREDITCARD',
6485 'PURCHASECARD','CREDITCARD',
6486 -- instrument type will be NULL for credit card credit trxns
6487 NULL,DECODE(tx.reqtype,
6488 'ORAPMTCREDIT','CREDITCARD',
6489 NULL),
6490 tx.instrtype) = 'CREDITCARD')
6491 AND tx.trxnmid = core.trxnmid
6492 AND NVL(core.encrypted, 'N') = 'N';
6493
6494
6495 BEGIN
6496
6497 IF (iby_creditcard_pkg.Get_CC_Encrypt_Mode() =
6498 iby_security_pkg.G_ENCRYPT_MODE_NONE)
6499 THEN
6500 RETURN;
6501 END IF;
6502 iby_security_pkg.validate_sys_key(p_sys_key,x_err_code);
6503 IF (NOT x_err_code IS NULL) THEN
6504 RETURN;
6505 END IF;
6506
6507 l_index := 1;
6508
6509 FOR txn_rec IN c_tx_ccnumber LOOP
6510 IBY_TRANSACTIONCC_PKG.prepare_instr_data
6511 (FND_API.G_FALSE,
6512 p_sys_key,
6513 txn_rec.ccnum,
6514 txn_rec.instrtype,
6515 l_maskedcc_tbl(l_index),
6516 l_issuer_tbl(l_index),
6517 l_cchash_tbl(l_index),
6518 l_rangeid_tbl(l_index),
6519 l_instrlen_tbl(l_index),
6520 l_segmentid_tbl(l_index)
6521 );
6522 l_trxnmid_tbl(l_index) := txn_rec.trxnmid;
6523
6524 IF (l_index=1000) THEN
6525 FORALL i IN l_trxnmid_tbl.first..l_trxnmid_tbl.last
6526 UPDATE iby_trxn_summaries_all
6527 SET
6528 instrnumber = l_maskedcc_tbl(i),
6529 instrnum_hash = l_cchash_tbl(i),
6530 cc_issuer_range_id = l_rangeid_tbl(i),
6531 instrnum_length = l_instrlen_tbl(i),
6532 instrnum_sec_segment_id = l_segmentid_tbl(i)
6533 WHERE trxnmid=l_trxnmid_tbl(i);
6534
6535 COMMIT;
6536
6537 l_index := 1;
6538 l_maskedcc_tbl.delete;
6539 l_issuer_tbl.delete;
6540 l_cchash_tbl.delete;
6541 l_rangeid_tbl.delete;
6542 l_instrlen_tbl.delete;
6543 l_segmentid_tbl.delete;
6544 l_trxnmid_tbl.delete;
6545 ELSE
6546 l_index := l_index + 1;
6547 END IF;
6548 END LOOP;
6549
6550 IF (l_trxnmid_tbl.COUNT>0) THEN
6551 FORALL i IN l_trxnmid_tbl.first..l_trxnmid_tbl.last
6552 UPDATE iby_trxn_summaries_all
6553 SET
6554 instrnumber = l_maskedcc_tbl(i),
6555 instrnum_hash = l_cchash_tbl(i),
6556 cc_issuer_range_id = l_rangeid_tbl(i),
6557 instrnum_length = l_instrlen_tbl(i),
6558 instrnum_sec_segment_id = l_segmentid_tbl(i)
6559 WHERE trxnmid=l_trxnmid_tbl(i);
6560
6561 COMMIT;
6562 END IF;
6563
6564 -- Loop thru the iby_trxn_core table only if the other card attributes
6565 -- present here need to be secured
6566 IF( IBY_CREDITCARD_PKG.Other_CC_Attribs_Encrypted = 'Y') THEN
6567 FOR core_rec IN c_trxn_core LOOP
6568 l_chname_sec_segment_id :=
6569 IBY_SECURITY_PKG.encrypt_field_vals(core_rec.chname,
6570 p_sys_key,
6571 null,
6572 'N'
6573 );
6574 l_expdate_sec_segment_id :=
6575 IBY_SECURITY_PKG.encrypt_date_field(core_rec.expdate,
6576 p_sys_key,
6577 null,
6578 'N'
6579 );
6580 l_masked_chname :=
6581 IBY_SECURITY_PKG.Mask_Data(core_rec.chname,
6582 IBY_SECURITY_PKG.G_MASK_ALL,
6583 0,
6584 'X'
6585 );
6586 l_encrypted := 'Y';
6587
6588 UPDATE iby_trxn_core SET
6589 encrypted = 'Y',
6590 instr_owner_name = l_masked_chname,
6591 chname_sec_segment_id = l_chname_sec_segment_id,
6592 instr_expirydate = NULL,
6593 expiry_sec_segment_id = l_expdate_sec_segment_id,
6594 object_version_number = object_version_number + 1,
6595 last_update_date = sysdate,
6596 last_updated_by = fnd_global.user_id,
6597 last_update_login = fnd_global.login_id
6598 WHERE (trxnmid = core_rec.tmid);
6599
6600 END LOOP;
6601 END IF;
6602
6603 COMMIT;
6604
6605 END Encrypt_CC_Data;
6606
6607 PROCEDURE Decrypt_CC_Data
6608 (p_sys_key IN IBY_SECURITY_PKG.DES3_KEY_TYPE, x_err_code OUT NOCOPY VARCHAR2)
6609 IS
6610 -- types
6611 TYPE Num15Tab IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
6612 TYPE Char60Tab IS TABLE OF VARCHAR2(60) INDEX BY BINARY_INTEGER;
6613
6614 -- bulk tables
6615 l_trxnmid_tbl Num15Tab;
6616 l_instrnum_tbl Char60Tab;
6617 l_segmentid_tbl Num15Tab;
6618
6619 l_index NUMBER;
6620
6621 -- variabled for CHNAME and EXPDATE decryption
6622 l_chname VARCHAR2(80);
6623 l_str_exp_date VARCHAR2(20);
6624 l_exp_date DATE;
6625
6626 CURSOR c_tx_ccnumber(ci_sys_key IN IBY_SECURITY_PKG.DES3_KEY_TYPE)
6627 IS
6628 SELECT /*+ rowid(tx) */
6629 tx.trxnmid,
6630 iby_transactioncc_pkg.unencrypt_instr_num
6631 (tx.instrnumber, NULL, NULL, ci_sys_key,
6632 k.subkey_cipher_text, tx.instrnum_sec_segment_id,
6633 seg.segment_cipher_text, r.card_number_prefix,
6634 NVL(r.card_number_length,tx.instrnum_length),
6635 i.digit_check_flag) ccnum,
6636 instrnum_sec_segment_id
6637 FROM iby_trxn_summaries_all tx,
6638 iby_security_segments seg, iby_sys_security_subkeys k,
6639 iby_cc_issuer_ranges r, iby_creditcard_issuers_b i
6640 WHERE
6641 /*
6642 (NOT instrnumber IS NULL)
6643 AND (DECODE(instrtype, 'PINLESSDEBITCARD','CREDITCARD',
6644 'PURCHASECARD','CREDITCARD',
6645 -- instrument type will be NULL for credit card credit trxns
6646 NULL,DECODE(reqtype,
6647 'ORAPMTCREDIT','CREDITCARD',
6648 NULL),
6649 instrtype) = 'CREDITCARD')
6650 */
6651 (NOT instrnum_sec_segment_id IS NULL)
6652 AND (tx.instrnum_sec_segment_id = seg.sec_segment_id(+))
6653 AND (seg.sec_subkey_id = k.sec_subkey_id(+))
6654 AND (tx.cc_issuer_range_id = r.cc_issuer_range_id(+))
6655 AND (tx.instrsubtype = i.card_issuer_code(+));
6656
6657 CURSOR c_trxn_core
6658 IS
6659 SELECT
6660 trxnmid,
6661 instr_expirydate,
6662 expiry_sec_segment_id,
6663 instr_owner_name,
6664 chname_sec_segment_id
6665 FROM iby_trxn_core
6666 WHERE NVL(encrypted, 'N') = 'Y';
6667
6668 BEGIN
6669 IF (iby_creditcard_pkg.Get_CC_Encrypt_Mode() =
6670 iby_security_pkg.G_ENCRYPT_MODE_NONE)
6671 THEN
6672 RETURN;
6673 END IF;
6674 iby_security_pkg.validate_sys_key(p_sys_key,x_err_code);
6675 IF (NOT x_err_code IS NULL) THEN
6676 RETURN;
6677 END IF;
6678
6679 l_index := 1;
6680
6681 FOR txn_rec IN c_tx_ccnumber(p_sys_key) LOOP
6682
6683 l_trxnmid_tbl(l_index) := txn_rec.trxnmid;
6684 l_instrnum_tbl(l_index) := iby_utility_pvt.encode64(txn_rec.ccnum);
6685 l_segmentid_tbl(l_index) := txn_rec.instrnum_sec_segment_id;
6686
6687 IF (l_index=1000) THEN
6688 FORALL i IN l_trxnmid_tbl.first..l_trxnmid_tbl.last
6689 UPDATE iby_trxn_summaries_all
6690 SET
6691 instrnumber = l_instrnum_tbl(i),
6692 instrnum_sec_segment_id = NULL
6693 WHERE trxnmid=l_trxnmid_tbl(i);
6694
6695 FORALL i IN l_segmentid_tbl.first..l_segmentid_tbl.last
6696 DELETE iby_security_segments
6697 WHERE sec_segment_id = l_segmentid_tbl(i);
6698
6699 COMMIT;
6700
6701 l_index := 1;
6702 l_segmentid_tbl.delete;
6703 l_instrnum_tbl.delete;
6704 l_trxnmid_tbl.delete;
6705 ELSE
6706 l_index := l_index + 1;
6707 END IF;
6708 END LOOP;
6709
6710 IF (l_trxnmid_tbl.COUNT>0) THEN
6711 FORALL i IN l_trxnmid_tbl.first..l_trxnmid_tbl.last
6712 UPDATE iby_trxn_summaries_all
6713 SET
6714 instrnumber = l_instrnum_tbl(i),
6715 instrnum_sec_segment_id = NULL
6716 WHERE trxnmid=l_trxnmid_tbl(i);
6717
6718 FORALL i IN l_segmentid_tbl.first..l_segmentid_tbl.last
6719 DELETE iby_security_segments
6720 WHERE sec_segment_id = l_segmentid_tbl(i);
6721
6722 COMMIT;
6723 END IF;
6724
6725 IF( IBY_CREDITCARD_PKG.Other_CC_Attribs_Encrypted = 'Y') THEN
6726 FOR core_rec IN c_trxn_core LOOP
6727 IF (core_rec.expiry_sec_segment_id IS NOT NULL) THEN
6728 l_exp_date := IBY_SECURITY_PKG.decrypt_date_field
6729 (core_rec.expiry_sec_segment_id,
6730 p_sys_key
6731 );
6732
6733 ELSE
6734 -- The exp date wasn't encrypted
6735 l_exp_date := core_rec.instr_expirydate;
6736 END IF;
6737
6738 IF(core_rec.chname_sec_segment_id IS NOT NULL) THEN
6739 l_chname := IBY_SECURITY_PKG.decrypt_field_vals
6740 (core_rec.chname_sec_segment_id,
6741 p_sys_key
6742 );
6743 ELSE
6744 -- CHNAME wasn't encrypted
6745 l_chname := core_rec.instr_owner_name;
6746 END IF;
6747
6748 UPDATE iby_trxn_core SET
6749 encrypted = 'N',
6750 instr_owner_name = l_chname,
6751 chname_sec_segment_id = NULL,
6752 instr_expirydate = l_exp_date,
6753 expiry_sec_segment_id = NULL,
6754 object_version_number = object_version_number + 1,
6755 last_update_date = sysdate,
6756 last_updated_by = fnd_global.user_id,
6757 last_update_login = fnd_global.login_id
6758 WHERE (trxnmid = core_rec.trxnmid);
6759
6760 END LOOP;
6761 END IF;
6762
6763 COMMIT;
6764
6765 END Decrypt_CC_Data;
6766
6767 PROCEDURE check_batch_size
6768 (
6769 ecappid_in IN iby_trxn_summaries_all.ecappid%TYPE,
6770 payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
6771 bepid_in IN iby_trxn_summaries_all.bepid%TYPE,
6772 bepkey_in IN iby_trxn_summaries_all.bepkey%TYPE,
6773 orgid_in IN iby_batches_all.org_id%TYPE,
6774 seckey_present_in IN VARCHAR2,
6775 trxncount_out OUT NOCOPY NUMBER,
6776 batchid_out OUT NOCOPY iby_batches_all.batchid%TYPE
6777 )
6778 IS
6779
6780 l_max_batch_size iby_bepinfo.max_batch_size%TYPE;
6781 l_mbatch_id iby_batches_all.mbatchid%TYPE;
6782
6783 CURSOR c_trxn_count
6784 (
6785 ci_ecappid iby_trxn_summaries_all.ecappid%TYPE,
6786 ci_payeeid iby_trxn_summaries_all.payeeid%TYPE,
6787 ci_bepid iby_trxn_summaries_all.bepid%TYPE,
6788 ci_bepkey iby_trxn_summaries_all.bepkey%TYPE
6789 ) IS
6790 SELECT count(transactionid)
6791 FROM iby_trxn_summaries_all
6792 WHERE (ci_bepid=bepid)
6793 AND (ci_payeeid=payeeid)
6794 AND (ci_bepkey=bepkey)
6795 --
6796 -- can have multiple trxns from a
6797 -- different ecapp's for a single payee account;
6798 -- don't bother until ecapp scoping is seriously
6799 -- supported by IBY
6800 --
6801 --AND (ci_ecappid=ecappid)
6802 AND (status=iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED)
6803 AND (batchid IS NULL)
6804 AND (instrtype IN (iby_creditcard_pkg.C_INSTRTYPE_CCARD,
6805 iby_creditcard_pkg.C_INSTRTYPE_PCARD)
6806 OR instrtype IS NULL);
6807
6808 CURSOR c_max_bsize(ci_bepid iby_trxn_summaries_all.bepid%TYPE) IS
6809 SELECT NVL(max_batch_size,-1)
6810 FROM iby_bepinfo
6811 WHERE (ci_bepid=bepid);
6812
6813 BEGIN
6814 IF (c_trxn_count%ISOPEN) THEN
6815 CLOSE c_trxn_count;
6816 END IF;
6817 IF (c_max_bsize%ISOPEN) THEN
6818 CLOSE c_max_bsize;
6819 END IF;
6820
6821 OPEN c_max_bsize(bepid_in);
6822 FETCH c_max_bsize INTO l_max_batch_size;
6823
6824 --
6825 -- nothing found; bep id must be bad
6826 --
6827 IF (c_max_bsize%NOTFOUND) THEN
6828 CLOSE c_max_bsize;
6829 raise_application_error(-20000, 'IBY_40201', FALSE);
6830 END IF;
6831
6832 CLOSE c_max_bsize;
6833
6834 OPEN c_trxn_count(ecappid_in,payeeid_in,bepid_in,bepkey_in);
6835 FETCH c_trxn_count INTO trxncount_out;
6836
6837 IF (c_trxn_count%NOTFOUND) THEN
6838 trxncount_out := 0;
6839 END IF;
6840
6841 CLOSE c_trxn_count;
6842
6843 IF (l_max_batch_size>0) THEN
6844 IF (l_max_batch_size<=trxncount_out) THEN
6845 --
6846 -- lock required to ensure only 1 batch close occurs after
6847 -- the maximum size is surpassed among all competing concurrent
6848 -- threads; lock both tables (even though iby_batches_all
6849 -- is sufficient) so as to ensure no deadlock can happen
6850 -- later
6851 --
6852 LOCK TABLE iby_batches_all, iby_trxn_summaries_all
6853 IN EXCLUSIVE MODE;
6854
6855 -- check batch size once more to ensure another thread has
6856 -- not closed it between the last check and possession of
6857 -- the table lock; necessary to prevent tiny batches of size
6858 -- 0, 1, etc. from being created
6859 --
6860 OPEN c_trxn_count(ecappid_in,payeeid_in,bepid_in,bepkey_in);
6861 FETCH c_trxn_count INTO trxncount_out;
6862
6863 IF (c_trxn_count%NOTFOUND) THEN
6864 trxncount_out := 0;
6865 END IF;
6866
6867 IF (l_max_batch_size>trxncount_out) THEN
6868 -- relinquish table locks
6869 ROLLBACK;
6870 RETURN;
6871 END IF;
6872
6873 SELECT to_char(IBY_BATCHID_S.nextval)
6874 INTO batchid_out
6875 FROM dual;
6876 --
6877 -- define the batch; note that this method
6878 -- commits data, so no need for a commit
6879 -- statement to follow
6880 --
6881 iby_transactioncc_pkg.insert_batch_status
6882 (
6883 batchid_out,
6884 payeeid_in,
6885 bepid_in,
6886 bepkey_in,
6887 iby_creditcard_pkg.C_INSTRTYPE_CCARD,
6888 iby_transactioncc_pkg.C_STATUS_COMMUNICATION_ERROR,
6889 SYSDATE,
6890 '',
6891 '',
6892 trxncount_out,
6893 iby_transactioncc_pkg.C_STATUS_COMMUNICATION_ERROR,
6894 0,
6895 0,
6896 0,
6897 '',
6898 '',
6899 '',
6900 '',
6901 '',
6902 '',
6903 orgid_in,
6904 'ORAPMTCLOSEBATCH',
6905 seckey_present_in,
6906 l_mbatch_id
6907 );
6908
6909 END IF;
6910 END IF;
6911
6912 COMMIT;
6913
6914 EXCEPTION
6915 --
6916 -- make sure to release the table lock
6917 --
6918 WHEN OTHERS THEN
6919 ROLLBACK;
6920 raise_application_error(SQLCODE, SQLERRM, FALSE);
6921 END check_batch_size;
6922
6923 /*--------------------------------------------------------------------
6924 | NAME:
6925 | Update_Payer_Notif_Batch
6926 |
6927 | PURPOSE:
6928 | This procedure updates the payer_notification_required flag for
6929 | all the transactions in a batch.
6930 |
6931 | PARAMETERS:
6932 | IN
6933 | p_debug_text - The debug message to be printed
6934 |
6935 | OUT
6936 |
6937 |
6938 | RETURNS:
6939 |
6940 | NOTES:
6941 |
6942 *---------------------------------------------------------------------*/
6943 PROCEDURE Update_Payer_Notif_Batch(
6944 mbatchid_in IN iby_batches_all.mbatchid%TYPE
6945 ) IS
6946
6947 l_process_profile iby_batches_all.process_profile_code%TYPE;
6948 l_payer_notif_flag VARCHAR2(1);
6949 l_instrument_type iby_batches_all.instrument_type%TYPE;
6950
6951
6952 CURSOR c_payer_notif_cc (c_user_profile iby_fndcpt_user_cc_pf_b.user_cc_profile_code%TYPE) IS
6953 SELECT DECODE(payer_notification_format, null, 'N', 'Y')
6954 FROM iby_fndcpt_user_cc_pf_b up, iby_fndcpt_sys_cc_pf_b sp
6955 WHERE up.sys_cc_profile_code = sp.sys_cc_profile_code
6956 AND up.user_cc_profile_code = c_user_profile;
6957
6958 CURSOR c_payer_notif_dc (c_user_profile iby_fndcpt_user_dc_pf_b.user_dc_profile_code%TYPE) IS
6959 SELECT DECODE(payer_notification_format, null, 'N', 'Y')
6960 FROM iby_fndcpt_user_dc_pf_b up, iby_fndcpt_sys_dc_pf_b sp,
6961 iby_batches_all b
6962 WHERE up.sys_dc_profile_code = sp.sys_dc_profile_code
6963 AND up.user_dc_profile_code =c_user_profile;
6964
6965 CURSOR c_payer_notif_eft (c_user_profile iby_fndcpt_user_eft_pf_b.user_eft_profile_code%TYPE) IS
6966 SELECT DECODE(payer_notification_format, null, 'N', 'Y')
6967 FROM iby_fndcpt_user_eft_pf_b up, iby_fndcpt_sys_eft_pf_b sp,
6968 iby_batches_all b
6969 WHERE up.sys_eft_profile_code = sp.sys_eft_profile_code
6970 AND up.user_eft_profile_code = c_user_profile;
6971
6972 CURSOR c_instr_type(i_mbatchid iby_batches_all.mbatchid%TYPE) IS
6973 SELECT instrument_type, process_profile_code
6974 FROM iby_batches_all
6975 WHERE mbatchid = i_mbatchid;
6976
6977 BEGIN
6978
6979 IF (c_instr_type%ISOPEN) THEN CLOSE c_instr_type; END IF;
6980 IF (c_payer_notif_cc%ISOPEN) THEN CLOSE c_payer_notif_cc; END IF;
6981 IF (c_payer_notif_dc%ISOPEN) THEN CLOSE c_payer_notif_dc; END IF;
6982 IF (c_payer_notif_eft%ISOPEN) THEN CLOSE c_payer_notif_eft; END IF;
6983
6984 OPEN c_instr_type(mbatchid_in);
6985 FETCH c_instr_type INTO l_instrument_type, l_process_profile;
6986 CLOSE c_instr_type;
6987
6988 -- get the payer_notification depending on the instrument_type
6989 -- from the different FCPP
6990 IF l_instrument_type = 'DEBITCARD' THEN
6991 OPEN c_payer_notif_dc(l_process_profile);
6992 FETCH c_payer_notif_dc INTO l_payer_notif_flag;
6993 CLOSE c_payer_notif_dc;
6994
6995 ELSIF l_instrument_type = 'BANKACCOUNT' THEN
6996 OPEN c_payer_notif_eft(l_process_profile);
6997 FETCH c_payer_notif_eft INTO l_payer_notif_flag;
6998 CLOSE c_payer_notif_eft;
6999
7000 ELSE
7001 OPEN c_payer_notif_cc(l_process_profile);
7002 FETCH c_payer_notif_cc INTO l_payer_notif_flag;
7003 CLOSE c_payer_notif_cc;
7004
7005 END IF;
7006
7007 -- set the payer_notification_required flag to yes
7008 -- if the payer_notification_format is defined for the FCPP
7009 -- at batch and trxn level and if the batch is successfull.
7010 -- Only for settlement trxn
7011 UPDATE iby_batches_all
7012 SET
7013 last_update_date = sysdate,
7014 last_updated_by = fnd_global.user_id,
7015 object_version_number = object_version_number + 1,
7016 payer_notification_required = l_payer_notif_flag
7017 WHERE (mbatchid = mbatchid_in);
7018
7019 -- the update will update only settlement trxn.
7020 -- authcapture, capture and markcapture
7021 UPDATE iby_trxn_summaries_all
7022 SET payer_notification_required = l_payer_notif_flag,
7023 last_update_date=sysdate,
7024 last_updated_by = fnd_global.user_id,
7025 object_version_number = object_version_number + 1
7026 WHERE mbatchid = mbatchid_in
7027 AND TrxntypeID IN (3,8,9,100);
7028
7029 END Update_Payer_Notif_Batch;
7030
7031 END iby_transactioncc_pkg;