The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cc_encryption_mode
FROM iby_sys_security_options;
SELECT u.party_site_use_id
FROM hz_party_site_uses u, hz_party_sites s
WHERE (u.party_site_id = ci_party_site)
-- because of complexities in the payer model
-- do not require the site address to be owned by the card owner
--AND (s.party_id = NVL(ci_party_id,party_id))
AND (u.party_site_id = s.party_site_id)
AND (u.site_use_type = G_CC_BILLING_SITE_USE)
AND ( NVL(u.begin_date,SYSDATE-10) < SYSDATE)
AND ( NVL(u.end_date,SYSDATE+10) > SYSDATE);
SELECT party_site_id
FROM hz_party_sites
WHERE (party_site_id = ci_party_site)
-- because of complexities in the payer model
-- do not require the site address to be owned by the card owner
--AND (party_id = NVL(ci_party_id,party_id))
AND ( NVL(start_date_active,SYSDATE-10) < SYSDATE)
AND ( NVL(end_date_active,SYSDATE+10) > SYSDATE);
SELECT count(instrid)
INTO l_encrypted_count
FROM iby_creditcard
WHERE (NVL(encrypted,'N')='Y');
* this method will throw a 'cannot insert NULL exception ..'
*/
SELECT iby_creditcard_h_s.NEXTVAL INTO x_history_id FROM dual;
INSERT INTO iby_creditcard_h
(card_history_change_id, instrid, expirydate, expiry_sec_segment_id,
addressid,
description, chname, chname_sec_segment_id, finame, security_group_id,
encrypted,
masked_cc_number, card_owner_id, instrument_type, purchasecard_flag,
purchasecard_subtype, card_issuer_code, single_use_flag,
information_only_flag, card_purpose, active_flag, inactive_date,
attribute_category, attribute1, attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14, attribute15,
attribute16, attribute17, attribute18, attribute19, attribute20,
attribute21, attribute22, attribute23, attribute24, attribute25,
attribute26, attribute27, attribute28, attribute29, attribute30,
request_id, program_application_id, program_id, program_update_date,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, object_version_number
)
SELECT x_history_id, instrid, expirydate, expiry_sec_segment_id,
addressid,
description, chname, chname_sec_segment_id, finame, security_group_id,
encrypted,
masked_cc_number, card_owner_id, instrument_type, purchasecard_flag,
purchasecard_subtype, card_issuer_code, single_use_flag,
information_only_flag, card_purpose, NVL(active_flag, 'Y'), inactive_date,
attribute_category, attribute1, attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14, attribute15,
attribute16, attribute17, attribute18, attribute19, attribute20,
attribute21, attribute22, attribute23, attribute24, attribute25,
attribute26, attribute27, attribute28, attribute29, attribute30,
request_id, program_application_id, program_id, program_update_date,
fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.login_id, 1
FROM iby_creditcard
WHERE (instrid = p_instr_id);
SELECT credit_card_mask_setting, credit_card_unmask_len
FROM iby_sys_security_options;
SELECT instrid
FROM iby_creditcard
WHERE (cc_number_hash1 = ci_hash1)
AND (cc_number_hash2 = ci_hash2)
AND ( (NVL(card_owner_id,ci_owner_id) = NVL(ci_owner_id,card_owner_id))
OR (card_owner_id IS NULL AND ci_owner_id IS NULL)
)
AND (NVL(single_use_flag,'N')='N');
SELECT iby_instr_s.NEXTVAL INTO x_instr_id FROM DUAL;
INSERT INTO iby_creditcard
(instrid, ccnumber, masked_cc_number,
card_mask_setting, card_unmask_length, cc_number_hash1, cc_number_hash2,
expirydate, expiry_sec_segment_id,
card_owner_id, chname, chname_sec_segment_id,
addressid, billing_addr_postal_code, bill_addr_territory_code,
instrument_type, purchasecard_flag, purchasecard_subtype,
card_issuer_code, cc_issuer_range_id, cc_number_length,
description, finame, encrypted, cc_num_sec_segment_id,
single_use_flag, information_only_flag, card_purpose,
active_flag, inactive_date,
last_update_date, last_updated_by, creation_date,
created_by, object_version_number,
attribute_category,
attribute1,attribute2, attribute3,attribute4,attribute5,
attribute6,attribute7, attribute8,attribute9,attribute10,
attribute11,attribute12, attribute13,attribute14,attribute15,
attribute16,attribute17, attribute18,attribute19,attribute20,
attribute21,attribute22, attribute23,attribute24,attribute25,
attribute26,attribute27, attribute28,attribute29,attribute30
-- salt_version bug 6326624
)
VALUES
(x_instr_id, lx_cc_number, lx_masked_number,
lx_mask_option, lx_unmask_len, l_cc_hash1, l_cc_hash2,
l_expiry_date, l_expdate_sec_segment_id,
p_owner_id, l_masked_chname, l_chname_sec_segment_id,
l_billing_site, p_billing_zip, p_billing_country,
p_instr_type, NVL(p_pcard_flag,'N'), p_pcard_type,
lx_card_issuer, lx_issuer_range, l_card_len,
p_desc, p_fi_name, l_encrypted, lx_sec_segment_id,
NVL(p_single_use,'N'), NVL(p_info_only,'N'), p_purpose,
NVL(p_active_flag,'Y'), p_inactive_date,
sysdate, fnd_global.user_id, sysdate,
fnd_global.user_id, 1,
p_attribute_category,
p_attribute1,p_attribute2,p_attribute3,p_attribute4,p_attribute5,
p_attribute6,p_attribute7,p_attribute8,p_attribute9,p_attribute10,
p_attribute11,p_attribute12,p_attribute13,p_attribute14,p_attribute15,
p_attribute16,p_attribute17, p_attribute18,p_attribute19,p_attribute20,
p_attribute21,p_attribute22, p_attribute23,p_attribute24,p_attribute25,
p_attribute26,p_attribute27, p_attribute28,p_attribute29,p_attribute30
-- iby_security_pkg.get_salt_version bug 6326624
);
PROCEDURE Update_Card
(p_commit IN VARCHAR2,
p_instr_id IN iby_creditcard.instrid%TYPE,
p_owner_id IN iby_creditcard.card_owner_id%TYPE,
p_holder_name IN iby_creditcard.chname%TYPE,
p_billing_address_id IN iby_creditcard.addressid%TYPE,
p_address_type IN VARCHAR2,
p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
p_expiry_date IN iby_creditcard.expirydate%TYPE,
p_instr_type IN iby_creditcard.instrument_type%TYPE,
p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
p_fi_name IN iby_creditcard.finame%TYPE,
p_single_use IN iby_creditcard.single_use_flag%TYPE,
p_info_only IN iby_creditcard.information_only_flag%TYPE,
p_purpose IN iby_creditcard.card_purpose%TYPE,
p_desc IN iby_creditcard.description%TYPE,
p_active_flag IN iby_creditcard.active_flag%TYPE,
p_inactive_date IN iby_creditcard.inactive_date%TYPE,
p_attribute_category IN iby_creditcard.attribute_category%TYPE,
p_attribute1 IN iby_creditcard.attribute1%TYPE,
p_attribute2 IN iby_creditcard.attribute2%TYPE,
p_attribute3 IN iby_creditcard.attribute3%TYPE,
p_attribute4 IN iby_creditcard.attribute4%TYPE,
p_attribute5 IN iby_creditcard.attribute5%TYPE,
p_attribute6 IN iby_creditcard.attribute6%TYPE,
p_attribute7 IN iby_creditcard.attribute7%TYPE,
p_attribute8 IN iby_creditcard.attribute8%TYPE,
p_attribute9 IN iby_creditcard.attribute9%TYPE,
p_attribute10 IN iby_creditcard.attribute10%TYPE,
p_attribute11 IN iby_creditcard.attribute11%TYPE,
p_attribute12 IN iby_creditcard.attribute12%TYPE,
p_attribute13 IN iby_creditcard.attribute13%TYPE,
p_attribute14 IN iby_creditcard.attribute14%TYPE,
p_attribute15 IN iby_creditcard.attribute15%TYPE,
p_attribute16 IN iby_creditcard.attribute16%TYPE,
p_attribute17 IN iby_creditcard.attribute17%TYPE,
p_attribute18 IN iby_creditcard.attribute18%TYPE,
p_attribute19 IN iby_creditcard.attribute19%TYPE,
p_attribute20 IN iby_creditcard.attribute20%TYPE,
p_attribute21 IN iby_creditcard.attribute21%TYPE,
p_attribute22 IN iby_creditcard.attribute22%TYPE,
p_attribute23 IN iby_creditcard.attribute23%TYPE,
p_attribute24 IN iby_creditcard.attribute24%TYPE,
p_attribute25 IN iby_creditcard.attribute25%TYPE,
p_attribute26 IN iby_creditcard.attribute26%TYPE,
p_attribute27 IN iby_creditcard.attribute27%TYPE,
p_attribute28 IN iby_creditcard.attribute28%TYPE,
p_attribute29 IN iby_creditcard.attribute29%TYPE,
p_attribute30 IN iby_creditcard.attribute30%TYPE,
x_result_code OUT NOCOPY VARCHAR2
)
IS
l_history_id iby_creditcard_h.card_history_change_id%TYPE;
SELECT encrypted INTO l_encrypted FROM iby_creditcard
WHERE instrid = p_instr_id;
UPDATE iby_creditcard
SET chname = DECODE(p_holder_name, FND_API.G_MISS_CHAR,NULL, NULL,chname, l_masked_chname),
chname_sec_segment_id = l_chname_sec_segment_id,
card_owner_id = NVL(card_owner_id,p_owner_id),
addressid = DECODE(l_billing_site, FND_API.G_MISS_NUM,NULL,
NULL,addressid, l_billing_site),
bill_addr_territory_code =
DECODE(p_billing_country, FND_API.G_MISS_CHAR,NULL,
NULL,bill_addr_territory_code, p_billing_country),
billing_addr_postal_code =
DECODE(p_billing_zip, FND_API.G_MISS_CHAR,NULL,
NULL,billing_addr_postal_code, p_billing_zip),
-- expirydate = NVL(p_expiry_date, expirydate),
expirydate = DECODE(p_expiry_date, NULL, expirydate, l_expiry_date),
expiry_sec_segment_id = DECODE(p_expiry_date, NULL, expiry_sec_segment_id,
l_expdate_sec_segment_id),
encrypted = l_encrypted,
instrument_type = NVL(p_instr_type, instrument_type),
purchasecard_flag = NVL(p_pcard_flag, purchasecard_flag),
purchasecard_subtype =
DECODE(p_pcard_type, FND_API.G_MISS_CHAR,NULL,
NULL,purchasecard_subtype, p_pcard_type),
finame = DECODE(p_fi_name, FND_API.G_MISS_CHAR,NULL, NULL,finame, p_fi_name),
single_use_flag = NVL(p_single_use, single_use_flag),
information_only_flag = NVL(p_info_only, information_only_flag),
card_purpose = DECODE(p_purpose, FND_API.G_MISS_CHAR,NULL, NULL,card_purpose, p_purpose),
description = DECODE(p_desc, FND_API.G_MISS_CHAR,NULL, NULL,description, p_desc),
active_flag = NVL(p_active_flag, active_flag),
inactive_date = DECODE(p_inactive_date, FND_API.G_MISS_DATE,NULL,
NULL,inactive_date, p_inactive_date),
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
attribute_category = p_Attribute_category,
attribute1 = p_attribute1,
attribute2 = p_attribute2,
attribute3 = p_attribute3,
attribute4 = p_attribute4,
attribute5 = p_attribute5,
attribute6 = p_attribute6,
attribute7 = p_attribute7,
attribute8 = p_attribute8,
attribute9 = p_attribute9,
attribute10 = p_attribute10,
attribute11 = p_attribute11,
attribute12 = p_attribute12,
attribute13 = p_attribute13,
attribute14 = p_attribute14,
attribute15 = p_attribute15,
attribute16 = p_attribute16,
attribute17 = p_attribute17,
attribute18 = p_attribute18,
attribute19 = p_attribute19,
attribute20 = p_attribute20,
attribute21 = p_attribute21,
attribute22 = p_attribute22,
attribute23 = p_attribute23,
attribute24 = p_attribute24,
attribute25 = p_attribute25,
attribute26 = p_attribute26,
attribute27 = p_attribute27,
attribute28 = p_attribute28,
attribute29 = p_attribute29,
attribute30 = p_attribute30
WHERE (instrid = p_instr_id);
END Update_Card;
SELECT
c.ccnumber, seg.segment_cipher_text,
NVL(c.encrypted,'N'), k.subkey_cipher_text, r.card_number_prefix,
NVL(i.digit_check_flag,'N'), c.card_mask_setting, c.card_unmask_length,
DECODE(encrypted, 'Y',c.ccnumber, NULL),
NVL(r.card_number_length,c.cc_number_length)
FROM iby_creditcard c, iby_security_segments seg,
iby_sys_security_subkeys k, iby_cc_issuer_ranges r,
iby_creditcard_issuers_b i
WHERE (instrid = ci_instrid)
AND (c.cc_num_sec_segment_id = seg.sec_segment_id(+))
AND (seg.sec_subkey_id = k.sec_subkey_id(+))
AND (c.cc_issuer_range_id = r.cc_issuer_range_id(+))
AND (r.card_issuer_code = i.card_issuer_code(+));
SELECT c.instrid, c.ccnumber, seg.segment_cipher_text,
c.encrypted, k.subkey_cipher_text,
NVL(r.card_number_length,c.cc_number_length) card_len,
r.card_number_prefix, i.digit_check_flag, c.card_mask_setting,
c.card_unmask_length, c.ccnumber unmask_digits, c.cc_num_sec_segment_id,
c.chname, c.chname_sec_segment_id,
c.expirydate, c.expiry_sec_segment_id
FROM iby_creditcard c, iby_creditcard_issuers_b i,
iby_cc_issuer_ranges r, iby_sys_security_subkeys k,
iby_security_segments seg
WHERE (NVL(c.encrypted,'N') = 'Y')
AND (c.card_issuer_code = i.card_issuer_code(+))
AND (c.cc_issuer_range_id = r.cc_issuer_range_id(+))
AND (c.cc_num_sec_segment_id = seg.sec_segment_id(+))
AND (seg.sec_subkey_id = k.sec_subkey_id(+));
UPDATE iby_creditcard
SET
ccnumber = l_cc_number,
encrypted = 'N',
cc_num_sec_segment_id = NULL,
expirydate = l_exp_date,
expiry_sec_segment_id = NULL,
chname = l_chname,
chname_sec_segment_id = NULL,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE (instrid = c_card_rec.instrid);
DELETE iby_security_segments
WHERE (sec_segment_id IN (c_card_rec.cc_num_sec_segment_id,
c_card_rec.chname_sec_segment_id,
c_card_rec.expiry_sec_segment_id)
);
SELECT c.instrid, c.ccnumber, c.cc_issuer_range_id,
k.subkey_cipher_text, r.card_number_prefix, i.digit_check_flag,
c.card_mask_setting, c.card_unmask_length, c.chname, c.expirydate
FROM iby_creditcard c, iby_creditcard_issuers_b i,
iby_cc_issuer_ranges r, iby_security_segments seg,
iby_sys_security_subkeys k
WHERE (NVL(c.encrypted,'N') = 'N')
AND (c.card_issuer_code = i.card_issuer_code(+))
AND (c.cc_issuer_range_id = r.cc_issuer_range_id(+))
AND (c.cc_num_sec_segment_id = seg.sec_segment_id(+))
AND (seg.sec_subkey_id = k.sec_subkey_id(+));
UPDATE iby_creditcard
SET
ccnumber = NVL(lx_mask_digits,0),
cc_num_sec_segment_id = l_segment_id,
encrypted = 'Y',
chname = l_masked_chname,
chname_sec_segment_id = l_chname_sec_segment_id,
expiry_sec_segment_id = l_expdate_sec_segment_id,
--encrypted_date_format = l_encrypted_date_format,
expirydate = l_exp_date,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE (instrid = c_card_rec.instrid);
SELECT c.instrid, c.ccnumber, seg.segment_cipher_text,
c.encrypted, k.subkey_cipher_text,
NVL(r.card_number_length,c.cc_number_length) card_len,
r.card_number_prefix, i.digit_check_flag, c.card_mask_setting,
c.card_unmask_length, c.ccnumber unmask_digits, seg.sec_segment_id,
LENGTH(c.ccnumber) len
FROM iby_creditcard c, iby_creditcard_issuers_b i,
iby_cc_issuer_ranges r, iby_sys_security_subkeys k,
iby_security_segments seg
WHERE (c.card_issuer_code = i.card_issuer_code(+))
AND (c.cc_issuer_range_id = r.cc_issuer_range_id(+))
AND (c.cc_num_sec_segment_id = seg.sec_segment_id(+))
AND (seg.sec_subkey_id = k.sec_subkey_id(+))
AND ( (NVL(card_unmask_length,-1) <> ci_unmask_len) OR
(NVL(card_mask_setting,' ') <> ci_mask_option)
);
IBY_SECURITY_PKG.Update_Segment
(FND_API.G_FALSE,lx_segment_id,l_cc_ciphertext,
iby_security_pkg.G_ENCODING_NUMERIC,
p_sys_key,c_card_rec.subkey_cipher_text);
DELETE iby_security_segments WHERE (sec_segment_id = lx_segment_id);
UPDATE iby_creditcard
SET
ccnumber = DECODE(encrypted, 'Y',NVL(lx_mask_digits,'0'), ccnumber),
masked_cc_number =
Mask_Card_Number(l_cc_number,lx_mask_option,lx_unmask_len),
cc_num_sec_segment_id = lx_segment_id,
card_mask_setting = lx_mask_option,
card_unmask_length = lx_unmask_len,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE (instrid = c_card_rec.instrid);
SELECT
c.card_owner_id, c.chname, c.addressid,
l.address1, l.address2, l.address3, l.city, l.county,
l.state, l.postal_code, l.country,
c.ccnumber, c.expirydate, c.instrument_type, c.purchasecard_flag,
c.purchasecard_subtype, c.card_issuer_code, c.finame,
c.single_use_flag, c.information_only_flag, c.card_purpose,
c.description, c.active_flag, c.inactive_date,
c.encrypted, c.expiry_sec_segment_id,
c.chname_sec_segment_id
FROM iby_creditcard c, hz_party_site_uses su, hz_party_sites s,
hz_locations l
WHERE (instrid = ci_instr_id)
AND (c.addressid = su.party_site_use_id(+))
AND (su.party_site_id = s.party_site_id(+))
AND (s.location_id = l.location_id(+));
SELECT instrid, expiry_sec_segment_id
FROM iby_creditcard
WHERE (encrypted = 'Y')
AND (expiry_sec_segment_id IS NOT NULL)
AND (expired_flag <> 'Y');
UPDATE iby_creditcard
SET expired_flag = l_expired_flag
WHERE (instrid = c_card_rec.instrid);