[Home] [Help]
PACKAGE BODY: APPS.IBY_HASH_REGENERATOR
Source
1 PACKAGE BODY IBY_HASH_REGENERATOR AS
2 /*$Header: ibyhgenb.pls 120.8 2011/11/09 06:44:10 lmallick noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_HASH_REGENERATOR';
5
6
7 PROCEDURE print_debuginfo
8 (
9 p_module IN VARCHAR2,
10 p_debug_text IN VARCHAR2
11 )
12 IS
13 BEGIN
14
15 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
16 iby_debug_pub.add(p_debug_text,iby_debug_pub.G_LEVEL_INFO,p_module);
17 -- FND_FILE.PUT_LINE(FND_FILE.LOG, p_module || ': ' || p_debug_text);
18 END IF;
19
20 END;
21
22 PROCEDURE Regenerate_Hash
23 (
24 p_creditcards_flag IN VARCHAR2,
25 p_bankaccounts_flag IN VARCHAR2,
26 p_transactions_flag IN VARCHAR2,
27 p_sys_key IN iby_view_parameters_gt.value%TYPE,
28 p_force_create IN VARCHAR2
29 )
30 IS
31
32 l_count1 NUMBER;
33 l_count2 NUMBER;
34 l_count3 NUMBER;
35 l_cc_id iby_creditcard.instrid%TYPE;
36 l_cc_num iby_creditcard.ccnumber%TYPE;
37
38 l_ba_id iby_ext_bank_accounts.ext_bank_account_id%TYPE;
39 l_ba_num iby_ext_bank_accounts.bank_account_num%TYPE;
40
41 l_sys_key iby_view_parameters_gt.value%TYPE;
42 l_force_create VARCHAR2(1);
43
44 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Regenerate_Hash';
45
46 CURSOR get_credit_cards(p_sys_key iby_view_parameters_gt.value%TYPE) IS
47 SELECT
48 b.instrid instrid,
49 iby_creditcard_pkg.uncipher_ccnumber
50 (
51 instrid,
52 p_sys_key
53 ) credit_card_number,
54 b.ccnumber credit_card_number2
55 FROM
56 iby_creditcard b;
57
58 CURSOR get_bank_accounts(p_sys_key iby_view_parameters_gt.value%TYPE) IS
59 SELECT
60 b.ext_bank_account_id ext_bank_account_id,
61 iby_ext_bankacct_pub.uncipher_bank_number
62 (
63 b.bank_account_num,
64 b.ba_num_sec_segment_id,
65 p_sys_key,
66 bak.subkey_cipher_text,
67 bas.segment_cipher_text,
68 bas.encoding_scheme,
69 b.ba_mask_setting,
70 b.ba_unmask_length
71 ) bank_account_number,
72 b.bank_account_num bank_account_number2,
73 iby_ext_bankacct_pub.Uncipher_Bank_Number
74 (
75 b.iban,
76 b.iban_sec_segment_id,
77 p_sys_key,
78 ibk.subkey_cipher_text,
79 ibs.segment_cipher_text,
80 ibs.encoding_scheme,
81 b.ba_mask_setting,
82 b.ba_unmask_length
83 ) iban,
84 b.iban iban2
85 FROM
86 iby_ext_bank_accounts b,
87 iby_security_segments bas,
88 iby_sys_security_subkeys bak,
89 iby_security_segments ibs,
90 iby_sys_security_subkeys ibk
91 WHERE
92 (b.ba_num_sec_segment_id = bas.sec_segment_id(+))
93 AND (bas.sec_subkey_id = bak.sec_subkey_id(+))
94 AND (b.iban_sec_segment_id = ibs.sec_segment_id(+))
95 AND (ibs.sec_subkey_id = ibk.sec_subkey_id(+));
96
97
98 CURSOR get_txn_credit_cards(p_sys_key iby_view_parameters_gt.value%TYPE) IS
99 SELECT tx.trxnmid,
100 iby_transactioncc_pkg.unencrypt_instr_num (
101 tx.instrnumber, NULL, NULL, p_sys_key, k.subkey_cipher_text,
102 tx.instrnum_sec_segment_id, seg.segment_cipher_text,
103 r.card_number_prefix, NVL(r.card_number_length,tx.instrnum_length),
104 i.digit_check_flag) credit_card_number,
105 tx.instrnumber credit_card_number2
106 FROM iby_trxn_summaries_all tx,
107 iby_security_segments seg,
108 iby_sys_security_subkeys k,
109 iby_cc_issuer_ranges r,
110 iby_creditcard_issuers_b i
111 WHERE (NOT instrnum_sec_segment_id IS NULL)
112 AND (tx.instrnum_sec_segment_id = seg.sec_segment_id(+))
113 AND (seg.sec_subkey_id = k.sec_subkey_id(+))
114 AND (tx.cc_issuer_range_id = r.cc_issuer_range_id(+))
115 AND (tx.instrsubtype = i.card_issuer_code(+))
116 AND tx.instrtype = 'CREDITCARD'
117 AND tx.instrnumber IS NOT NULL
118 UNION
119 SELECT tx.trxnmid,
120 iby_utility_pvt.decode64(instrnumber) credit_card_number,
121 instrnumber credit_card_number2
122 FROM iby_trxn_summaries_all tx
123 WHERE instrnum_sec_segment_id IS NULL
124 AND tx.instrtype = 'CREDITCARD'
125 AND tx.instrnumber IS NOT NULL;
126
127 CURSOR get_txn_bank_accounts(p_sys_key iby_view_parameters_gt.value%TYPE) IS
128 SELECT tx.trxnmid,
129 iby_utility_pvt.decode64(instrnumber) bank_account_number,
130 instrnumber bank_account_number2
131 FROM iby_trxn_summaries_all tx
132 WHERE instrnum_sec_segment_id IS NULL
133 AND tx.instrtype = 'BANKACCOUNT'
134 AND tx.instrnumber IS NOT NULL
135 UNION
136 SELECT tx.trxnmid trxnmid,
137 iby_ext_bankacct_pub.uncipher_bank_number (
138 tx.instrnumber, tx.instrnum_sec_segment_id, p_sys_key, bak.subkey_cipher_text,
139 bas.segment_cipher_text, bas.encoding_scheme, NULL, NULL ) bank_account_number,
140 tx.instrnumber bank_account_number2
141 FROM iby_trxn_summaries_all tx,
142 iby_security_segments bas,
143 iby_sys_security_subkeys bak
144 WHERE (NOT instrnum_sec_segment_id IS NULL)
145 AND tx.instrnum_sec_segment_id = bas.sec_segment_id(+)
146 AND bas.sec_subkey_id = bak.sec_subkey_id(+)
147 AND tx.instrtype = 'BANKACCOUNT'
148 AND tx.instrnumber IS NOT NULL;
149
150
151 BEGIN
152 print_debuginfo(l_module_name, 'Enter');
153
154 l_force_create := NVL(p_force_create, 'N');
155 print_debuginfo(l_module_name, 'l_force_create = '|| l_force_create);
156
157 print_debuginfo(l_module_name, 'Verfying version of existing hash..');
158 IF(l_force_create <> 'Y' AND IBY_SECURITY_PKG.get_salt_version > 1)THEN
159 print_debuginfo(l_module_name, 'Raw hash already generated. Exiting!');
160 RETURN;
161 END IF ;
162
163 print_debuginfo(l_module_name, 'Initiating hash regeneration processes ');
164
165 --l_sys_key := iby_utility_pvt.get_view_param('SYS_KEY');
166 l_sys_key := p_sys_key;
167 --print_debuginfo(l_module_name, 'System Key : ' || l_sys_key);
168
169 UPDATE iby_sys_security_options SET system_salt_version = 2;
170
171
172 ----------------------------------------------------------------------------------------
173 --TABLE_NAME COLUMN_NAME COLUMN_NAME_REF
174 ----------------------------------------------------------------------------------------
175 --IBY_CREDITCARD CC_NUMBER_HASH1 CCNUMBER
176 --IBY_CREDITCARD CC_NUMBER_HASH2 CCNUMBER
177 ----------------------------------------------------------------------------------------
178 --SELECT ccnumber,
179 -- cc_number_hash1,
180 -- iby_security_pkg.get_hash(ccnumber, 'F'),
181 -- cc_number_hash2,
182 -- iby_security_pkg.get_hash(ccnumber, 'T')
183 --FROM iby_creditcard
184 --WHERE ccnumber IS NOT NULL;
185
186 IF (p_creditcards_flag = 'Y') THEN
187
188 print_debuginfo(l_module_name, 'Regenerating hash for credit cards ...');
189 l_count1 := 0 ;
190 l_count2 := 0 ;
191
192 UPDATE iby_creditcard
193 SET cc_number_hash1 = NULL,
194 cc_number_hash2 = NULL;
195
196 FOR credit_card_rec IN get_credit_cards(l_sys_key)
197 LOOP
198
199 l_count1 := l_count1 + 1 ;
200 IF (credit_card_rec.credit_card_number IS NOT NULL)
201 THEN
202
203 l_count2 := l_count2 + 1 ;
204 UPDATE iby_creditcard
205 SET cc_number_hash1 = iby_security_pkg.get_hash(credit_card_rec.credit_card_number, 'F'),
206 cc_number_hash2 = iby_security_pkg.get_hash(credit_card_rec.credit_card_number, 'T'),
207 salt_version = 2
208 WHERE instrid = credit_card_rec.instrid;
209
210 END IF;
211
212 END LOOP;
213
214 print_debuginfo(l_module_name, ' CC Hash regeneration complete for '||l_count2||'/'||l_count1||' credit cards');
215
216
217 END IF;
218
219
220 ----------------------------------------------------------------------------------------
221 --TABLE_NAME COLUMN_NAME COLUMN_NAME_REF
222 ----------------------------------------------------------------------------------------
223 --IBY_EXT_BANK_ACCOUNTS BANK_ACCOUNT_NUM_HASH1 BANK_ACCOUNT_NUM
224 --IBY_EXT_BANK_ACCOUNTS BANK_ACCOUNT_NUM_HASH2 BANK_ACCOUNT_NUM
225 --IBY_EXT_BANK_ACCOUNTS IBAN_HASH1 IBAN
226 --IBY_EXT_BANK_ACCOUNTS IBAN_HASH2 IBAN
227 ----------------------------------------------------------------------------------------
228 --SELECT bank_account_num,
229 -- bank_account_num_hash1,
230 -- iby_security_pkg.get_hash(bank_account_num, 'F'),
231 -- bank_account_num_hash2,
232 -- iby_security_pkg.get_hash(bank_account_num, 'T')
233 --FROM iby_ext_bank_accounts
234 --WHERE bank_account_num IS NOT NULL;
235
236 --SELECT iban,
237 -- iban_hash1,
238 -- iby_security_pkg.get_hash(iban, 'F'),
239 -- iban_hash2,
240 -- iby_security_pkg.get_hash(iban, 'T')
241 --FROM iby_ext_bank_accounts
242 --WHERE iban IS NOT NULL;
243
244 IF (p_bankaccounts_flag = 'Y') THEN
245
246 print_debuginfo(l_module_name, 'Regenerating hash for bank accounts ...');
247 l_count1 := 0 ;
248 l_count2 := 0 ;
249 l_count3 := 0 ;
250
251 UPDATE iby_ext_bank_accounts
252 SET bank_account_num_hash1 = NULL,
253 bank_account_num_hash2 = NULL;
254
255 UPDATE iby_ext_bank_accounts
256 SET iban_hash1 = NULL,
257 iban_hash2 = NULL;
258
259 FOR bank_acct_rec IN get_bank_accounts(l_sys_key)
260 LOOP
261 l_count1 := l_count1 + 1 ;
262 IF (bank_acct_rec.bank_account_number IS NOT NULL)
263 THEN
264 l_count2 := l_count2 + 1 ;
265 UPDATE iby_ext_bank_accounts
266 SET bank_account_num_hash1 = iby_security_pkg.get_hash(bank_acct_rec.bank_account_number, 'F'),
267 bank_account_num_hash2 = iby_security_pkg.get_hash(bank_acct_rec.bank_account_number, 'T'),
268 salt_version = 2
269 WHERE ext_bank_account_id = bank_acct_rec.ext_bank_account_id;
270
271 END IF;
272
273 IF (bank_acct_rec.iban IS NOT NULL)
274 THEN
275
276 l_count3 := l_count3 + 1 ;
277 UPDATE iby_ext_bank_accounts
278 SET iban_hash1 = iby_security_pkg.get_hash(bank_acct_rec.iban, 'F'),
279 iban_hash2 = iby_security_pkg.get_hash(bank_acct_rec.iban, 'T')
280 WHERE ext_bank_account_id = bank_acct_rec.ext_bank_account_id;
281
282 END IF;
283
284 END LOOP;
285
286 print_debuginfo(l_module_name, ' BA Hash regeneration complete for '||l_count2||'/'||l_count1||' bank accounts');
287 print_debuginfo(l_module_name, ' IBAN Hash regeneration complete for '||l_count3||'/'||l_count1||' bank accounts');
288 END IF;
289
290
291 ----------------------------------------------------------------------------------------
292 --TABLE_NAME COLUMN_NAME COLUMN_NAME_REF
293 ----------------------------------------------------------------------------------------
294 --IBY_TRXN_SUMMARIES_ALL INSTRNUM_HASH INSTRNUMBER
295 ----------------------------------------------------------------------------------------
296 --SELECT instrnumber,
297 -- instrnum_hash,
298 -- iby_security_pkg.get_hash(instrnumber, 'F')
299 --FROM iby_trxn_summaries_all
300 --WHERE instrnumber IS NOT NULL;
301
302 IF (p_transactions_flag = 'Y') THEN
303 print_debuginfo(l_module_name, 'Clearing hash from Transaction table..');
304 UPDATE iby_trxn_summaries_all
305 SET instrnum_hash = NULL;
306
307
308 /* print_debuginfo(l_module_name, 'Regenerating hash for transactions ...');
309 l_count1 := 0 ;
310 l_count2 := 0 ;
311 l_count3 := 0 ;
312
313 UPDATE iby_trxn_summaries_all
314 SET instrnum_hash = NULL;
315
316 FOR bank_acct_rec IN get_txn_bank_accounts(l_sys_key)
317 LOOP
318 l_count1 := l_count1 + 1 ;
319 IF (bank_acct_rec.bank_account_number IS NOT NULL AND bank_acct_rec.bank_account_number2 IS NOT NULL)
320 THEN
321 l_count2 := l_count2 + 1 ;
322 UPDATE iby_trxn_summaries_all
323 SET instrnum_hash = iby_security_pkg.get_hash(bank_acct_rec.bank_account_number, 'F'),
324 salt_version = 2
325 WHERE trxnmid = bank_acct_rec.trxnmid;
326
327 END IF;
328
329 END LOOP;
330
331 print_debuginfo(l_module_name, ' TXN Hash regeneration complete for '||l_count2||'/'||l_count1||' transactions with instrtype BA');
332 l_count1 := 0;
333
334 FOR credit_card_rec IN get_txn_credit_cards(l_sys_key)
335 LOOP
336
337 l_count1 := l_count1 + 1 ;
338 IF (credit_card_rec.credit_card_number IS NOT NULL AND credit_card_rec.credit_card_number2 IS NOT NULL)
339 THEN
340
341 l_count3 := l_count3 + 1 ;
342 UPDATE iby_trxn_summaries_all
343 SET instrnum_hash = iby_security_pkg.get_hash(credit_card_rec.credit_card_number, 'F'),
344 salt_version = 2
345 WHERE trxnmid = credit_card_rec.trxnmid;
346
347 END IF;
348
349 END LOOP;
350
351
352 print_debuginfo(l_module_name, ' TXN Hash regeneration complete for '||l_count3||'/'||l_count1||' transactions with instrtype CC');
353 */
354 END IF;
355
356 print_debuginfo(l_module_name, 'Hash regeneration completed successfully !' );
357
358 print_debuginfo(l_module_name, 'Exit');
359
360
361 COMMIT;
362
363 EXCEPTION
364 WHEN OTHERS THEN
365
366 print_debuginfo(l_module_name,'ERROR: Exception occured when invoking terminate instruction API');
367 print_debuginfo(l_module_name,'SQLCODE is: ' || SQLCODE);
368 print_debuginfo(l_module_name,'SQLERRM is: ' || SQLERRM);
369 ROLLBACK;
370
371 END Regenerate_Hash;
372
373 PROCEDURE Regenerate_Hash
374 (
375 p_creditcards_flag IN VARCHAR2,
376 p_bankaccounts_flag IN VARCHAR2,
377 p_transactions_flag IN VARCHAR2,
378 p_sys_key IN iby_view_parameters_gt.value%TYPE
379 )
380 IS
381 BEGIN
382 Regenerate_Hash
383 (
384 p_creditcards_flag,
385 p_bankaccounts_flag,
386 p_transactions_flag,
387 p_sys_key,
388 'N'
389 );
390 END Regenerate_Hash;
391
392
393 PROCEDURE Upgrade_CC_Hash_Mgr
394 (x_errbuf OUT NOCOPY VARCHAR2,
395 x_retcode OUT NOCOPY VARCHAR2,
396 x_batch_size IN NUMBER,
397 x_num_workers IN NUMBER
398 )
399 IS
400 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Upgrade_Hash_Data_Mgr';
401 l_system_salt_version NUMBER;
402 BEGIN
403 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_INFO,l_module_name);
404
405 l_system_salt_version := IBY_SECURITY_PKG.Get_Salt_Version;
406 iby_debug_pub.add('Version of existing hash: '||l_system_salt_version,iby_debug_pub.G_LEVEL_INFO,l_module_name);
407
408 IF (l_system_salt_version =3) THEN
409 iby_debug_pub.add('Latest version of hash data already present. Exiting..'
410 ,iby_debug_pub.G_LEVEL_INFO,l_module_name);
411 fnd_file.put_line(fnd_file.OUTPUT,'Latest version of hash data already present. Exiting..');
412 RETURN;
413 END IF;
414
415 IF (l_system_salt_version <> 2) THEN
416 iby_debug_pub.add('Please submit ''Re-Generate Payments Hash Data'' CP first and then submit the Upgrade program. Exiting..'
417 ,iby_debug_pub.G_LEVEL_INFO,l_module_name);
418 fnd_file.put_line(fnd_file.OUTPUT,'Existing hash data is incompatible. Please '||
419 'submit ''Re-Generate Payments Hash Data'' CP first and then submit the Upgrade program again.');
420 RETURN;
421 END IF;
422
423 AD_CONC_UTILS_PKG.submit_subrequests
424 (X_errbuf => x_errbuf,
425 X_retcode => x_retcode,
426 X_WorkerConc_app_shortname => 'IBY',
427 X_WorkerConc_progname => 'IBY_CC_HASH_UPG_WKR',
428 X_batch_size => x_batch_size,
429 X_Num_Workers => x_num_workers
430 );
431
432 iby_debug_pub.add('Submitted Workers..',iby_debug_pub.G_LEVEL_INFO,l_module_name);
433 iby_debug_pub.add('Removing redundant hash data from iby_trxn_summaries_all..',iby_debug_pub.G_LEVEL_INFO,l_module_name);
434 UPDATE iby_trxn_summaries_all SET instrnum_hash = NULL WHERE instrnum_hash IS NOT NULL;
435 iby_debug_pub.add('Records updated='||SQL%ROWCOUNT,iby_debug_pub.G_LEVEL_INFO,l_module_name);
436 COMMIT ;
437
438 EXCEPTION
439
440 WHEN OTHERS THEN
441 iby_debug_pub.add('ERROR CODE:='||sqlcode,iby_debug_pub.G_LEVEL_INFO,l_module_name);
442 iby_debug_pub.add('ERROR:='||sqlerrm,iby_debug_pub.G_LEVEL_INFO,l_module_name);
443 END Upgrade_CC_Hash_Mgr;
444
445 PROCEDURE Upgrade_CC_Hash_Wkr
446 (x_errbuf OUT NOCOPY VARCHAR2,
447 x_retcode OUT NOCOPY VARCHAR2,
448 x_batch_size IN NUMBER,
449 x_worker_id IN NUMBER,
450 x_num_workers IN NUMBER
451 )
452 IS
453 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Upgrade_Hash_Data_Wkr';
454
455 -- types
456 TYPE Num15Tab IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
457 TYPE Char64Tab IS TABLE OF VARCHAR2(64) INDEX BY BINARY_INTEGER;
458
459
460 -- ad parallel parameters
461 l_table_owner VARCHAR2(30);
462 l_batch_size NUMBER;
463 l_worker_id NUMBER;
464 l_num_workers NUMBER;
465 l_any_rows_to_process BOOLEAN;
466 l_table_name VARCHAR2(30) := 'IBY_CREDITCARD';
467 l_script_name VARCHAR2(30);
468 l_product VARCHAR2(10) := 'IBY';
469 l_status VARCHAR2(30);
470 l_industry VARCHAR2(30);
471 l_retstatus BOOLEAN;
472
473 l_start_rowid ROWID;
474 l_end_rowid ROWID;
475 l_rows_processed NUMBER;
476
477 -- bulk tables
478 l_instrid_tbl Num15Tab;
479 l_ccnum_hash1_tbl Char64Tab;
480 l_ccnum_hash2_tbl Char64Tab;
481
482 l_site_salt RAW(128);
483 l_pending_cc NUMBER;
484 l_pending_ba NUMBER;
485
486 t1 TIMESTAMP;
487 t2 TIMESTAMP;
488
489
490 CURSOR c_cards
491 (ci_start_rowid IN ROWID, ci_end_rowid IN ROWID)
492 IS
493 SELECT instrid,
494 cc_number_hash1,
495 cc_number_hash2
496 FROM iby_creditcard
497 WHERE NVL(salt_version,1) = 2
498 AND (rowid BETWEEN ci_start_rowid AND ci_end_rowid);
499 BEGIN
500 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_INFO,l_module_name);
501 l_num_workers := NVL(x_num_workers,1);
502 l_worker_id := NVL(x_worker_id,1);
503 l_batch_size := NVL(x_batch_size,10000);
504
505 l_script_name := 'CC_HASH_UPG_'||TO_CHAR(SYSDATE,'DDMMYYHH24MISS');
506
507 --
508 -- get schema name of the table for ROWID range processing
509 --
510 l_retstatus := fnd_installation.get_app_info
511 (l_product, l_status, l_industry, l_table_owner);
512
513 IF ((l_retstatus = FALSE) OR (l_table_owner is null)) THEN
514 raise_application_error(-20001,
515 'Cannot get schema name for product : '||l_product);
516 END IF;
517
518 iby_debug_pub.add('x_worker_id : '||x_worker_id,iby_debug_pub.G_LEVEL_INFO,l_module_name);
519
520 x_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
521
522 -- cache site salt value
523 l_site_salt := IBY_SECURITY_PKG.Get_Site_Salt;
524
525 BEGIN
526 ad_parallel_updates_pkg.initialize_rowid_range(
527 ad_parallel_updates_pkg.ROWID_RANGE,
528 l_table_owner,
529 l_table_name,
530 l_script_name,
531 x_worker_id,
532 x_num_workers,
533 x_batch_size, 0);
534
535 ad_parallel_updates_pkg.get_rowid_range(
536 l_start_rowid,
537 l_end_rowid,
538 l_any_rows_to_process,
539 x_batch_size,
540 TRUE);
541
542 WHILE (l_any_rows_to_process = TRUE) LOOP
543 -- fetch credit card hash data
544 OPEN c_cards(l_start_rowid, l_end_rowid);
545 FETCH c_cards BULK COLLECT INTO
546 l_instrid_tbl, l_ccnum_hash1_tbl, l_ccnum_hash2_tbl;
547 CLOSE c_cards;
548
549 iby_debug_pub.add('rows fetched:='||l_instrid_tbl.COUNT,iby_debug_pub.G_LEVEL_INFO,l_module_name);
550 t1 := systimestamp;
551 IF (l_instrid_tbl.count>0) THEN
552 FORALL i IN l_instrid_tbl.first..l_instrid_tbl.last
553 UPDATE iby_creditcard
554 SET cc_number_hash1 = IBY_SECURITY_PKG.Compute_New_Hash(l_ccnum_hash1_tbl(i),l_site_salt),
555 cc_number_hash2 = IBY_SECURITY_PKG.Compute_New_Hash(l_ccnum_hash2_tbl(i),l_site_salt),
556 salt_version = 3
557 WHERE instrid=l_instrid_tbl(i);
558 END IF;
559
560 l_rows_processed := SQL%ROWCOUNT;
561 t2 := systimestamp;
562 iby_debug_pub.add('No. of credit card records updated = '|| l_rows_processed,
563 iby_debug_pub.G_LEVEL_INFO,l_module_name);
564 fnd_file.put_line(fnd_file.OUTPUT,'No. of credit card records updated = '|| l_rows_processed);
565 iby_debug_pub.add('Time taken = '|| (t2-t1),
566 iby_debug_pub.G_LEVEL_INFO,l_module_name);
567
568 ad_parallel_updates_pkg.processed_rowid_range(
569 l_rows_processed,
570 l_end_rowid);
571
572 COMMIT;
573
574 ad_parallel_updates_pkg.get_rowid_range(
575 l_start_rowid,
576 l_end_rowid,
577 l_any_rows_to_process,
578 x_batch_size,
579 FALSE);
580 END LOOP;
581 EXCEPTION
582 WHEN OTHERS THEN
583 iby_debug_pub.add('ERROR CODE:='||sqlcode,iby_debug_pub.G_LEVEL_INFO,l_module_name);
584 iby_debug_pub.add('ERROR MESSAGE:='||SUBSTR(sqlerrm,1,150),iby_debug_pub.G_LEVEL_INFO,l_module_name);
585 x_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
586 x_errbuf := SQLERRM;
587 END;
588 iby_debug_pub.add('Worker Done',iby_debug_pub.G_LEVEL_INFO,l_module_name);
589
590 -- If entire data has been upgarded then turn on the system switch to use
591 -- new hashing logic
592 iby_debug_pub.add('Verifying if any data is pending before setting system switch',iby_debug_pub.G_LEVEL_INFO,l_module_name);
593 SELECT COUNT(1) INTO l_pending_cc FROM iby_creditcard WHERE NVL(salt_version,1) <> 3;
594 IF(l_pending_cc = 0) THEN
595 iby_debug_pub.add('All CC hashes generated. Verifying BA..',iby_debug_pub.G_LEVEL_INFO,l_module_name);
596 SELECT COUNT(1) INTO l_pending_ba FROM iby_ext_bank_accounts WHERE NVL(salt_version,1) <> 3;
597 IF(l_pending_ba = 0) THEN
598 iby_debug_pub.add('All BA records re-hashed. Updating system_salt_version..',iby_debug_pub.G_LEVEL_INFO,l_module_name);
599 UPDATE iby_sys_security_options SET system_salt_version=3;
600 COMMIT;
601 ELSE
602 iby_debug_pub.add('There are pending BA records to be processed. Skipping update of system_salt_version.'
603 ,iby_debug_pub.G_LEVEL_INFO,l_module_name);
604 END IF ;
605 ELSE
606 iby_debug_pub.add('There are pending CC records to be processed. Skipping update of system_salt_version.'
607 ,iby_debug_pub.G_LEVEL_INFO,l_module_name);
608 END IF ;
609 x_retcode := NVL(x_retcode,AD_CONC_UTILS_PKG.CONC_SUCCESS);
610 END Upgrade_CC_Hash_Wkr;
611
612
613 PROCEDURE Upgrade_BA_Hash_Mgr
614 (x_errbuf OUT NOCOPY VARCHAR2,
615 x_retcode OUT NOCOPY VARCHAR2,
616 x_batch_size IN NUMBER,
617 x_num_workers IN NUMBER
618 )
619 IS
620 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Upgrade_Hash_Data_Mgr';
621 l_system_salt_version NUMBER;
622 BEGIN
623 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_INFO,l_module_name);
624
625 l_system_salt_version := IBY_SECURITY_PKG.Get_Salt_Version;
626 iby_debug_pub.add('Version of existing hash: '||l_system_salt_version,iby_debug_pub.G_LEVEL_INFO,l_module_name);
627
628 IF (l_system_salt_version =3) THEN
629 iby_debug_pub.add('Latest version of hash data already present. Exiting..'
630 ,iby_debug_pub.G_LEVEL_INFO,l_module_name);
631 fnd_file.put_line(fnd_file.OUTPUT,l_module_name||'=>Latest version of hash data already present. Exiting..');
632 RETURN;
633 END IF;
634
635 IF (l_system_salt_version <> 2) THEN
636 iby_debug_pub.add('Please submit ''Re-Generate Payments Hash Data'' CP first and then submit the Upgrade program. Exiting..'
637 ,iby_debug_pub.G_LEVEL_INFO,l_module_name);
638 fnd_file.put_line(fnd_file.OUTPUT,l_module_name||'=>Existing hash data is incompatible. Please '||
639 'submit ''Re-Generate Payments Hash Data'' CP first and then submit the Upgrade program again.');
640 RETURN;
641 END IF;
642
643 AD_CONC_UTILS_PKG.submit_subrequests
644 (X_errbuf => x_errbuf,
645 X_retcode => x_retcode,
646 X_WorkerConc_app_shortname => 'IBY',
647 X_WorkerConc_progname => 'IBY_BA_HASH_UPG_WKR',
648 X_batch_size => x_batch_size,
649 X_Num_Workers => x_num_workers
650 );
651
652 iby_debug_pub.add('Submitted Workers..',iby_debug_pub.G_LEVEL_INFO,l_module_name);
653
654 EXCEPTION
655 WHEN OTHERS THEN
656 iby_debug_pub.add('ERROR CODE:='||sqlcode,iby_debug_pub.G_LEVEL_INFO,l_module_name);
657 iby_debug_pub.add('ERROR:='||sqlerrm,iby_debug_pub.G_LEVEL_INFO,l_module_name);
658 END Upgrade_BA_Hash_Mgr;
659
660 PROCEDURE Upgrade_BA_Hash_Wkr
661 (x_errbuf OUT NOCOPY VARCHAR2,
662 x_retcode OUT NOCOPY VARCHAR2,
663 x_batch_size IN NUMBER,
664 x_worker_id IN NUMBER,
665 x_num_workers IN NUMBER
666 )
667 IS
668 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Upgrade_Hash_Data_Wkr';
669
670 -- types
671 TYPE Num15Tab IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
672 TYPE Char64Tab IS TABLE OF VARCHAR2(64) INDEX BY BINARY_INTEGER;
673
674
675 -- ad parallel parameters
676 l_table_owner VARCHAR2(30);
677 l_batch_size NUMBER;
678 l_worker_id NUMBER;
679 l_num_workers NUMBER;
680 l_any_rows_to_process BOOLEAN;
681 l_table_name VARCHAR2(30) := 'IBY_EXT_BANK_ACCOUNTS';
682 l_script_name VARCHAR2(30);
683 l_product VARCHAR2(10) := 'IBY';
684 l_status VARCHAR2(30);
685 l_industry VARCHAR2(30);
686 l_retstatus BOOLEAN;
687
688 l_start_rowid ROWID;
689 l_end_rowid ROWID;
690 l_rows_processed NUMBER;
691
692 -- bulk tables
693 l_instrid_tbl Num15Tab;
694 l_banum_hash1_tbl Char64Tab;
695 l_banum_hash2_tbl Char64Tab;
696 l_iban_hash1_tbl Char64Tab;
697 l_iban_hash2_tbl Char64Tab;
698
699 l_site_salt RAW(128);
700 l_pending_cc NUMBER;
701 l_pending_ba NUMBER;
702
703 t1 TIMESTAMP;
704 t2 TIMESTAMP;
705
706 CURSOR c_bank_accts
707 (ci_start_rowid IN ROWID, ci_end_rowid IN ROWID)
708 IS
709 SELECT ext_bank_account_id,
710 bank_account_num_hash1,
711 bank_account_num_hash2,
712 iban_hash1,
713 iban_hash2
714 FROM iby_ext_bank_accounts
715 WHERE NVL(salt_version,1) = 2
716 AND (rowid BETWEEN ci_start_rowid AND ci_end_rowid);
717 BEGIN
718 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_INFO,l_module_name);
719 l_num_workers := NVL(x_num_workers,1);
720 l_worker_id := NVL(x_worker_id,1);
721 l_batch_size := NVL(x_batch_size,10000);
722 l_script_name := 'BA_HASH_UPG_'||TO_CHAR(SYSDATE,'DDMMYYHH24MISS');
723
724 --
725 -- get schema name of the table for ROWID range processing
726 --
727 l_retstatus := fnd_installation.get_app_info
728 (l_product, l_status, l_industry, l_table_owner);
729
730 IF ((l_retstatus = FALSE) OR (l_table_owner is null)) THEN
731 raise_application_error(-20001,
732 'Cannot get schema name for product : '||l_product);
733 END IF;
734
735 iby_debug_pub.add('x_worker_id : '||x_worker_id,iby_debug_pub.G_LEVEL_INFO,l_module_name);
736
737 x_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
738
739 -- cache site salt value
740 l_site_salt := IBY_SECURITY_PKG.Get_Site_Salt;
741
742 BEGIN
743 ad_parallel_updates_pkg.initialize_rowid_range(
744 ad_parallel_updates_pkg.ROWID_RANGE,
745 l_table_owner,
746 l_table_name,
747 l_script_name,
748 x_worker_id,
749 x_num_workers,
750 x_batch_size, 0);
751
752 ad_parallel_updates_pkg.get_rowid_range(
753 l_start_rowid,
754 l_end_rowid,
755 l_any_rows_to_process,
756 x_batch_size,
757 TRUE);
758
759 WHILE (l_any_rows_to_process = TRUE) LOOP
760 -- fetch credit card hash data
761 OPEN c_bank_accts(l_start_rowid, l_end_rowid);
762 FETCH c_bank_accts BULK COLLECT INTO
763 l_instrid_tbl, l_banum_hash1_tbl, l_banum_hash2_tbl,
764 l_iban_hash1_tbl, l_iban_hash2_tbl;
765 CLOSE c_bank_accts;
766
767 iby_debug_pub.add('rows fetched:='||l_instrid_tbl.COUNT,iby_debug_pub.G_LEVEL_INFO,l_module_name);
768 t1 := systimestamp;
769 IF (l_instrid_tbl.COUNT>0) THEN
770 FORALL i IN l_instrid_tbl.first..l_instrid_tbl.last
771 UPDATE iby_ext_bank_accounts
772 SET bank_account_num_hash1 = DECODE(l_banum_hash1_tbl(i),NULL,NULL,IBY_SECURITY_PKG.Compute_New_Hash(l_banum_hash1_tbl(i),l_site_salt)),
773 bank_account_num_hash2 = DECODE(l_banum_hash2_tbl(i),NULL,NULL,IBY_SECURITY_PKG.Compute_New_Hash(l_banum_hash2_tbl(i),l_site_salt)),
774 iban_hash1 = DECODE(l_iban_hash1_tbl(i),NULL,NULL,IBY_SECURITY_PKG.Compute_New_Hash(l_iban_hash1_tbl(i),l_site_salt)),
775 iban_hash2 = DECODE(l_iban_hash2_tbl(i),NULL,NULL,IBY_SECURITY_PKG.Compute_New_Hash(l_iban_hash2_tbl(i),l_site_salt)),
776 salt_version = 3
777 WHERE ext_bank_account_id=l_instrid_tbl(i);
778 END IF;
779
780 l_rows_processed := SQL%ROWCOUNT;
781 t2 := systimestamp;
782 iby_debug_pub.add('No. of bank account records updated = '|| l_rows_processed,
783 iby_debug_pub.G_LEVEL_INFO,l_module_name);
784 iby_debug_pub.add('Time taken = '|| (t2-t1),
785 iby_debug_pub.G_LEVEL_INFO,l_module_name);
786 fnd_file.put_line(fnd_file.OUTPUT,'No. of bank account records updated = '|| l_rows_processed);
787
788 ad_parallel_updates_pkg.processed_rowid_range(
789 l_rows_processed,
790 l_end_rowid);
791
792 COMMIT;
793
794 ad_parallel_updates_pkg.get_rowid_range(
795 l_start_rowid,
796 l_end_rowid,
797 l_any_rows_to_process,
798 x_batch_size,
799 FALSE);
800 END LOOP;
801 EXCEPTION
802 WHEN OTHERS THEN
803 iby_debug_pub.add('ERROR CODE:='||sqlcode,iby_debug_pub.G_LEVEL_INFO,l_module_name);
804 iby_debug_pub.add('ERROR MESSAGE:='||SUBSTR(sqlerrm,1,150),iby_debug_pub.G_LEVEL_INFO,l_module_name);
805 x_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
806 x_errbuf := SQLERRM;
807 END;
808 iby_debug_pub.add('Worker Done',iby_debug_pub.G_LEVEL_INFO,l_module_name);
809
810 -- If entire data has been upgarded then turn on the system switch to use
811 -- new hashing logic
812 iby_debug_pub.add('Verifying if any data is pending before setting system switch',iby_debug_pub.G_LEVEL_INFO,l_module_name);
813 SELECT COUNT(1) INTO l_pending_cc FROM iby_creditcard WHERE NVL(salt_version,1) <> 3;
814 IF(l_pending_cc = 0) THEN
815 iby_debug_pub.add('All CC hashes generated. Verifying BA..',iby_debug_pub.G_LEVEL_INFO,l_module_name);
816 SELECT COUNT(1) INTO l_pending_ba FROM iby_ext_bank_accounts WHERE NVL(salt_version,1) <> 3;
817 IF(l_pending_ba = 0) THEN
818 iby_debug_pub.add('All BA records re-hashed. Updating system_salt_version..',iby_debug_pub.G_LEVEL_INFO,l_module_name);
819 UPDATE iby_sys_security_options SET system_salt_version=3;
820 COMMIT;
821 ELSE
822 iby_debug_pub.add('There are pending BA records to be processed. Skipping update of system_salt_version.'
823 ,iby_debug_pub.G_LEVEL_INFO,l_module_name);
824 END IF ;
825 ELSE
826 iby_debug_pub.add('There are pending CC records to be processed. Skipping update of system_salt_version.'
827 ,iby_debug_pub.G_LEVEL_INFO,l_module_name);
828 END IF ;
829 x_retcode := NVL(x_retcode,AD_CONC_UTILS_PKG.CONC_SUCCESS);
830 END Upgrade_BA_Hash_Wkr;
831
832
833 END IBY_HASH_REGENERATOR;