APPS.IBY_HASH_REGENERATOR SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 47

		SELECT
		  b.instrid instrid,
		  iby_creditcard_pkg.uncipher_ccnumber
		  (
		   instrid,
		   p_sys_key
		  ) credit_card_number,
		  b.ccnumber credit_card_number2
		FROM
		  iby_creditcard b;

Line: 59

		SELECT
		  b.ext_bank_account_id ext_bank_account_id,
		  iby_ext_bankacct_pub.uncipher_bank_number
		  (
		   b.bank_account_num,
		   b.ba_num_sec_segment_id,
		   p_sys_key,
		   bak.subkey_cipher_text,
		   bas.segment_cipher_text,
		   bas.encoding_scheme,
		   b.ba_mask_setting,
		   b.ba_unmask_length
		  ) bank_account_number,
		  b.bank_account_num bank_account_number2,
		  iby_ext_bankacct_pub.Uncipher_Bank_Number
		  (
		   b.iban,
		   b.iban_sec_segment_id,
		   p_sys_key,
		   ibk.subkey_cipher_text,
		   ibs.segment_cipher_text,
		   ibs.encoding_scheme,
		   b.ba_mask_setting,
		   b.ba_unmask_length
		  ) iban,
		  b.iban iban2
		FROM
		  iby_ext_bank_accounts b,
		  iby_security_segments bas,
		  iby_sys_security_subkeys bak,
		  iby_security_segments ibs,
		  iby_sys_security_subkeys ibk
		WHERE
		      (b.ba_num_sec_segment_id  = bas.sec_segment_id(+))
		  AND (bas.sec_subkey_id        = bak.sec_subkey_id(+))
		  AND (b.iban_sec_segment_id    = ibs.sec_segment_id(+))
		  AND (ibs.sec_subkey_id        = ibk.sec_subkey_id(+));

Line: 99

		SELECT tx.trxnmid,
		  iby_transactioncc_pkg.unencrypt_instr_num (
		                          tx.instrnumber, NULL, NULL, p_sys_key, k.subkey_cipher_text,
					  tx.instrnum_sec_segment_id, seg.segment_cipher_text,
					  r.card_number_prefix, NVL(r.card_number_length,tx.instrnum_length),
					  i.digit_check_flag) credit_card_number,
		  tx.instrnumber credit_card_number2
		FROM iby_trxn_summaries_all tx,
		  iby_security_segments seg,
		  iby_sys_security_subkeys k,
		  iby_cc_issuer_ranges r,
		  iby_creditcard_issuers_b i
		WHERE (NOT instrnum_sec_segment_id IS NULL)
		AND (tx.instrnum_sec_segment_id     = seg.sec_segment_id(+))
		AND (seg.sec_subkey_id              = k.sec_subkey_id(+))
		AND (tx.cc_issuer_range_id          = r.cc_issuer_range_id(+))
		AND (tx.instrsubtype                = i.card_issuer_code(+))
		AND tx.instrtype                    = 'CREDITCARD'
		AND tx.instrnumber                 IS NOT NULL
		UNION
		SELECT tx.trxnmid,
		  iby_utility_pvt.decode64(instrnumber) credit_card_number,
		  instrnumber credit_card_number2
		FROM iby_trxn_summaries_all tx
		WHERE instrnum_sec_segment_id IS NULL
		AND tx.instrtype               = 'CREDITCARD'
		AND tx.instrnumber            IS NOT NULL;

Line: 128

		SELECT tx.trxnmid,
		  iby_utility_pvt.decode64(instrnumber) bank_account_number,
		  instrnumber bank_account_number2
		FROM iby_trxn_summaries_all tx
		WHERE instrnum_sec_segment_id IS NULL
		AND tx.instrtype               = 'BANKACCOUNT'
		AND tx.instrnumber            IS NOT NULL
		UNION
		SELECT tx.trxnmid trxnmid,
		  iby_ext_bankacct_pub.uncipher_bank_number (
		                          tx.instrnumber, tx.instrnum_sec_segment_id, p_sys_key, bak.subkey_cipher_text,
					  bas.segment_cipher_text, bas.encoding_scheme, NULL, NULL ) bank_account_number,
		  tx.instrnumber bank_account_number2
		FROM iby_trxn_summaries_all tx,
		  iby_security_segments bas,
		  iby_sys_security_subkeys bak
		WHERE (NOT instrnum_sec_segment_id IS NULL)
		AND tx.instrnum_sec_segment_id      = bas.sec_segment_id(+)
		AND bas.sec_subkey_id               = bak.sec_subkey_id(+)
		AND tx.instrtype                    = 'BANKACCOUNT'
		AND tx.instrnumber                 IS NOT NULL;

Line: 169

		UPDATE iby_sys_security_options SET system_salt_version = 2;

Line: 178

		--SELECT ccnumber,
		--  cc_number_hash1,
		--  iby_security_pkg.get_hash(ccnumber,   'F'),
		--  cc_number_hash2,
		--  iby_security_pkg.get_hash(ccnumber,   'T')
		--FROM iby_creditcard
		--WHERE ccnumber IS NOT NULL;

Line: 192

		UPDATE iby_creditcard
		SET cc_number_hash1 = NULL,
		  cc_number_hash2 = NULL;

Line: 204

				UPDATE iby_creditcard
				SET cc_number_hash1 = iby_security_pkg.get_hash(credit_card_rec.credit_card_number,   'F'),
				  cc_number_hash2 = iby_security_pkg.get_hash(credit_card_rec.credit_card_number,   'T'),
				  salt_version = 2
				WHERE instrid = credit_card_rec.instrid;

Line: 228

		--SELECT bank_account_num,
		--  bank_account_num_hash1,
		--  iby_security_pkg.get_hash(bank_account_num,   'F'),
		--  bank_account_num_hash2,
		--  iby_security_pkg.get_hash(bank_account_num,   'T')
		--FROM iby_ext_bank_accounts
		--WHERE bank_account_num IS NOT NULL;

Line: 236

		--SELECT iban,
		--  iban_hash1,
		--  iby_security_pkg.get_hash(iban,   'F'),
		--  iban_hash2,
		--  iby_security_pkg.get_hash(iban,   'T')
		--FROM iby_ext_bank_accounts
		--WHERE iban IS NOT NULL;

Line: 251

		UPDATE iby_ext_bank_accounts
		SET bank_account_num_hash1 = NULL,
		  bank_account_num_hash2 = NULL;

Line: 255

		UPDATE iby_ext_bank_accounts
		SET iban_hash1 = NULL,
		  iban_hash2 = NULL;

Line: 265

				UPDATE iby_ext_bank_accounts
				SET bank_account_num_hash1 = iby_security_pkg.get_hash(bank_acct_rec.bank_account_number,   'F'),
				  bank_account_num_hash2 = iby_security_pkg.get_hash(bank_acct_rec.bank_account_number,   'T'),
				  salt_version = 2
				WHERE ext_bank_account_id = bank_acct_rec.ext_bank_account_id;

Line: 277

				UPDATE iby_ext_bank_accounts
				SET iban_hash1 = iby_security_pkg.get_hash(bank_acct_rec.iban,   'F'),
				  iban_hash2 = iby_security_pkg.get_hash(bank_acct_rec.iban,   'T')
				WHERE ext_bank_account_id = bank_acct_rec.ext_bank_account_id;

Line: 296

		--SELECT instrnumber,
		--  instrnum_hash,
		--  iby_security_pkg.get_hash(instrnumber,   'F')
		--FROM iby_trxn_summaries_all
		--WHERE instrnumber IS NOT NULL;

Line: 304

          UPDATE iby_trxn_summaries_all
	     SET instrnum_hash = NULL;

Line: 313

		UPDATE iby_trxn_summaries_all
		SET instrnum_hash = NULL;

Line: 322

				UPDATE iby_trxn_summaries_all
				SET instrnum_hash = iby_security_pkg.get_hash(bank_acct_rec.bank_account_number,   'F'),
				salt_version = 2
				WHERE trxnmid = bank_acct_rec.trxnmid;

Line: 342

				UPDATE iby_trxn_summaries_all
				SET instrnum_hash = iby_security_pkg.get_hash(credit_card_rec.credit_card_number,   'F'),
				salt_version = 2
				WHERE trxnmid = credit_card_rec.trxnmid;

Line: 434

	  UPDATE iby_trxn_summaries_all SET instrnum_hash = NULL WHERE instrnum_hash IS NOT NULL;

Line: 435

	  iby_debug_pub.add('Records updated='||SQL%ROWCOUNT,iby_debug_pub.G_LEVEL_INFO,l_module_name);

Line: 493

	    SELECT instrid,
		   cc_number_hash1,
		   cc_number_hash2
	     FROM  iby_creditcard
	    WHERE  NVL(salt_version,1) = 2
	      AND  (rowid BETWEEN ci_start_rowid AND ci_end_rowid);

Line: 526

            ad_parallel_updates_pkg.initialize_rowid_range(
              ad_parallel_updates_pkg.ROWID_RANGE,
              l_table_owner,
              l_table_name,
              l_script_name,
              x_worker_id,
              x_num_workers,
              x_batch_size, 0);

Line: 535

            ad_parallel_updates_pkg.get_rowid_range(
              l_start_rowid,
              l_end_rowid,
              l_any_rows_to_process,
              x_batch_size,
              TRUE);

Line: 553

                  UPDATE iby_creditcard
                     SET cc_number_hash1 = IBY_SECURITY_PKG.Compute_New_Hash(l_ccnum_hash1_tbl(i),l_site_salt),
   		         cc_number_hash2 = IBY_SECURITY_PKG.Compute_New_Hash(l_ccnum_hash2_tbl(i),l_site_salt),
                         salt_version    = 3
                   WHERE instrid=l_instrid_tbl(i);

Line: 562

	      iby_debug_pub.add('No. of credit card records updated = '|| l_rows_processed,
	                             iby_debug_pub.G_LEVEL_INFO,l_module_name);

Line: 564

	      fnd_file.put_line(fnd_file.OUTPUT,'No. of credit card records updated = '|| l_rows_processed);

Line: 568

              ad_parallel_updates_pkg.processed_rowid_range(
                l_rows_processed,
                l_end_rowid);

Line: 574

              ad_parallel_updates_pkg.get_rowid_range(
                l_start_rowid,
                l_end_rowid,
                l_any_rows_to_process,
                x_batch_size,
                FALSE);

Line: 593

	  SELECT COUNT(1) INTO l_pending_cc FROM iby_creditcard WHERE NVL(salt_version,1) <> 3;

Line: 596

	    SELECT COUNT(1) INTO l_pending_ba FROM iby_ext_bank_accounts WHERE NVL(salt_version,1) <> 3;

Line: 599

	      UPDATE iby_sys_security_options SET system_salt_version=3;

Line: 602

	      iby_debug_pub.add('There are pending BA records to be processed. Skipping update of system_salt_version.'
	                               ,iby_debug_pub.G_LEVEL_INFO,l_module_name);

Line: 606

	      iby_debug_pub.add('There are pending CC records to be processed. Skipping update of system_salt_version.'
	                               ,iby_debug_pub.G_LEVEL_INFO,l_module_name);

Line: 709

	    SELECT ext_bank_account_id,
		   bank_account_num_hash1,
		   bank_account_num_hash2,
		   iban_hash1,
		   iban_hash2
	     FROM  iby_ext_bank_accounts
	    WHERE  NVL(salt_version,1) = 2
	      AND  (rowid BETWEEN ci_start_rowid AND ci_end_rowid);

Line: 743

            ad_parallel_updates_pkg.initialize_rowid_range(
              ad_parallel_updates_pkg.ROWID_RANGE,
              l_table_owner,
              l_table_name,
              l_script_name,
              x_worker_id,
              x_num_workers,
              x_batch_size, 0);

Line: 752

            ad_parallel_updates_pkg.get_rowid_range(
              l_start_rowid,
              l_end_rowid,
              l_any_rows_to_process,
              x_batch_size,
              TRUE);

Line: 771

                  UPDATE iby_ext_bank_accounts
                     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)),
   		         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)),
			 iban_hash1 = DECODE(l_iban_hash1_tbl(i),NULL,NULL,IBY_SECURITY_PKG.Compute_New_Hash(l_iban_hash1_tbl(i),l_site_salt)),
			 iban_hash2 = DECODE(l_iban_hash2_tbl(i),NULL,NULL,IBY_SECURITY_PKG.Compute_New_Hash(l_iban_hash2_tbl(i),l_site_salt)),
                         salt_version    = 3
                   WHERE ext_bank_account_id=l_instrid_tbl(i);

Line: 782

	      iby_debug_pub.add('No. of bank account records updated = '|| l_rows_processed,
	                             iby_debug_pub.G_LEVEL_INFO,l_module_name);

Line: 786

	      fnd_file.put_line(fnd_file.OUTPUT,'No. of bank account records updated = '|| l_rows_processed);

Line: 788

              ad_parallel_updates_pkg.processed_rowid_range(
                l_rows_processed,
                l_end_rowid);

Line: 794

              ad_parallel_updates_pkg.get_rowid_range(
                l_start_rowid,
                l_end_rowid,
                l_any_rows_to_process,
                x_batch_size,
                FALSE);

Line: 813

	  SELECT COUNT(1) INTO l_pending_cc FROM iby_creditcard WHERE NVL(salt_version,1) <> 3;

Line: 816

	    SELECT COUNT(1) INTO l_pending_ba FROM iby_ext_bank_accounts WHERE NVL(salt_version,1) <> 3;

Line: 819

	      UPDATE iby_sys_security_options SET system_salt_version=3;

Line: 822

	      iby_debug_pub.add('There are pending BA records to be processed. Skipping update of system_salt_version.'
	                               ,iby_debug_pub.G_LEVEL_INFO,l_module_name);

Line: 826

	      iby_debug_pub.add('There are pending CC records to be processed. Skipping update of system_salt_version.'
	                               ,iby_debug_pub.G_LEVEL_INFO,l_module_name);