[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cc_encryption_mode
FROM iby_sys_security_options;
SELECT nvl(encrypt_supplemental_card_data, 'N')
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')<>'N');
* 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, expired_flag,
card_owner_id, chname, chname_sec_segment_id,
chname_mask_setting, chname_unmask_length,
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, last_update_login, 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,
invalid_flag, invalidation_reason,
salt_version
)
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, l_expired,
p_owner_id, l_masked_chname, l_chname_sec_segment_id,
l_chname_mask_setting, l_chname_unmask_len,
p_billing_address_id, 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, nvl(p_user_id, fnd_global.user_id), sysdate,
decode(p_user_id,-1,fnd_global.user_id,p_user_id),
decode(p_login_id,-1,fnd_global.login_id,p_login_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,
l_invalid_flag, l_invalidation_reason,
iby_security_pkg.get_salt_version
);
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,
p_allow_invalid_card IN VARCHAR2,
p_issuer IN iby_creditcard.card_issuer_code%TYPE
)
IS
l_history_id iby_creditcard_h.card_history_change_id%TYPE;
l_dbg_mod VARCHAR2(100) := 'iby.plsql.IBY_CREDITCARD_PKG' || '.' || 'Update_Card';
SELECT encrypted, expiry_sec_segment_id, chname_sec_segment_id,
chname_mask_setting, chname_unmask_length
INTO l_encrypted, l_expdate_sec_segment_id, l_chname_sec_segment_id,
l_chname_mask_setting, l_chname_unmask_len
FROM iby_creditcard
WHERE instrid = p_instr_id;
UPDATE iby_creditcard
SET chname = DECODE(l_chname, FND_API.G_MISS_CHAR,NULL, NULL,chname, l_masked_chname),
chname_sec_segment_id = DECODE(l_chname, FND_API.G_MISS_CHAR,NULL,
NULL,chname_sec_segment_id,l_chname_sec_segment_id),
chname_mask_setting = DECODE(l_chname, FND_API.G_MISS_CHAR,NULL,
NULL,chname_mask_setting,l_chname_mask_setting),
chname_unmask_length = DECODE(l_chname, FND_API.G_MISS_CHAR,NULL,
NULL,chname_unmask_length,l_chname_unmask_len),
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),
expired_flag = nvl(l_expired, expired_flag),
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 = DECODE(p_attribute1,FND_API.G_MISS_CHAR,NULL,NULL,attribute1, p_attribute1),
attribute2 = DECODE(p_attribute2,FND_API.G_MISS_CHAR,NULL,NULL,attribute2, p_attribute2),
attribute3 = DECODE(p_attribute3,FND_API.G_MISS_CHAR,NULL,NULL,attribute3, p_attribute3),
attribute4 = DECODE(p_attribute4,FND_API.G_MISS_CHAR,NULL,NULL,attribute4, p_attribute4),
attribute5 = DECODE(p_attribute5,FND_API.G_MISS_CHAR,NULL,NULL,attribute5, p_attribute5),
attribute6 = DECODE(p_attribute6,FND_API.G_MISS_CHAR,NULL,NULL,attribute6, p_attribute6),
attribute7 = DECODE(p_attribute7,FND_API.G_MISS_CHAR,NULL,NULL,attribute7, p_attribute7),
attribute8 = DECODE(p_attribute8,FND_API.G_MISS_CHAR,NULL,NULL,attribute8, p_attribute8),
attribute9 = DECODE(p_attribute9,FND_API.G_MISS_CHAR,NULL,NULL,attribute9, p_attribute9),
attribute10 = DECODE(p_attribute10,FND_API.G_MISS_CHAR,NULL,NULL,attribute10, p_attribute10),
attribute11 = DECODE(p_attribute11,FND_API.G_MISS_CHAR,NULL,NULL,attribute11, p_attribute11),
attribute12 = DECODE(p_attribute12,FND_API.G_MISS_CHAR,NULL,NULL,attribute12, p_attribute12),
attribute13 = DECODE(p_attribute13,FND_API.G_MISS_CHAR,NULL,NULL,attribute13, p_attribute13),
attribute14 = DECODE(p_attribute14,FND_API.G_MISS_CHAR,NULL,NULL,attribute14, p_attribute14),
attribute15 = DECODE(p_attribute15,FND_API.G_MISS_CHAR,NULL,NULL,attribute15, p_attribute15),
attribute16 = DECODE(p_attribute16,FND_API.G_MISS_CHAR,NULL,NULL,attribute16, p_attribute16),
attribute17 = DECODE(p_attribute17,FND_API.G_MISS_CHAR,NULL,NULL,attribute17, p_attribute17),
attribute18 = DECODE(p_attribute18,FND_API.G_MISS_CHAR,NULL,NULL,attribute18, p_attribute18),
attribute19 = DECODE(p_attribute19,FND_API.G_MISS_CHAR,NULL,NULL,attribute19, p_attribute19),
attribute20 = DECODE(p_attribute20,FND_API.G_MISS_CHAR,NULL,NULL,attribute20, p_attribute20),
attribute21 = DECODE(p_attribute21,FND_API.G_MISS_CHAR,NULL,NULL,attribute21, p_attribute21),
attribute22 = DECODE(p_attribute22,FND_API.G_MISS_CHAR,NULL,NULL,attribute22, p_attribute22),
attribute23 = DECODE(p_attribute23,FND_API.G_MISS_CHAR,NULL,NULL,attribute23, p_attribute23),
attribute24 = DECODE(p_attribute24,FND_API.G_MISS_CHAR,NULL,NULL,attribute24, p_attribute24),
attribute25 = DECODE(p_attribute25,FND_API.G_MISS_CHAR,NULL,NULL,attribute25, p_attribute25),
attribute26 = DECODE(p_attribute26,FND_API.G_MISS_CHAR,NULL,NULL,attribute26, p_attribute26),
attribute27 = DECODE(p_attribute27,FND_API.G_MISS_CHAR,NULL,NULL,attribute27, p_attribute27),
attribute28 = DECODE(p_attribute28,FND_API.G_MISS_CHAR,NULL,NULL,attribute28, p_attribute28),
attribute29 = DECODE(p_attribute29,FND_API.G_MISS_CHAR,NULL,NULL,attribute29, p_attribute29),
attribute30 = DECODE(p_attribute30,FND_API.G_MISS_CHAR,NULL,NULL,attribute30, p_attribute30)
WHERE (instrid = p_instr_id);
UPDATE iby_creditcard
SET invalid_flag = l_invalid_flag,
invalidation_reason = l_invalidation_reason,
card_issuer_code = NVL(l_new_issuer, card_issuer_code)
WHERE (instrid = p_instr_id);
END Update_Card;
SELECT c.invalid_flag, c.invalidation_reason,
c.expired_flag,
c.card_issuer_code, r.card_issuer_code
FROM iby_creditcard c, iby_cc_issuer_ranges r
WHERE instrid = ci_instrid
AND c.cc_issuer_range_id = r.cc_issuer_range_id(+);
SELECT
c.ccnumber, c.masked_cc_number, 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,'A',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') <> '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 = 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,
chname_mask_setting = NULL,
chname_unmask_length = 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 = l_encrypted,
chname = l_masked_chname,
chname_sec_segment_id = l_chname_sec_segment_id,
chname_mask_setting = l_chname_mask_setting,
chname_unmask_length = l_chname_unmask_len,
expiry_sec_segment_id = l_expdate_sec_segment_id,
expirydate = l_exp_date,
expired_flag = l_expired_flag,
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, c.chname, c.chname_sec_segment_id,
c.chname_mask_setting, c.chname_unmask_length
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'),'A',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,
chname = l_masked_chname,
chname_sec_segment_id = l_chname_seg_id,
chname_mask_setting = l_chname_mask_setting,
chname_unmask_length = l_chname_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
c.card_owner_id, c.chname, c.addressid,
l.address1, l.address2, l.address3, l.city, l.county,
nvl(l.state,l.province), 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 , l.province
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, expirydate, expiry_sec_segment_id
FROM iby_creditcard
WHERE (NVL(expired_flag,'N') <> 'Y');
UPDATE iby_creditcard
SET expired_flag = l_expired_flag
WHERE (instrid = c_card_rec.instrid);
iby_debug_pub.add('No. of records updated = '||cnt,
iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
SELECT instrid, expirydate, expiry_sec_segment_id,
chname , chname_sec_segment_id
FROM iby_creditcard
WHERE (NVL(encrypted,'N') = 'Y')
AND ((expirydate IS NOT NULL)
OR
((chname IS NOT NULL) AND (chname_sec_segment_id IS NULL)));
UPDATE iby_creditcard
SET
encrypted = 'A',
chname = nvl(l_masked_chname, chname),
chname_sec_segment_id = l_chname_segment_id,
chname_mask_setting = l_chname_mask_setting,
chname_unmask_length = l_chname_unmask_len,
expirydate = null,
expiry_sec_segment_id = l_exp_segment_id,
expired_flag = l_expired_flag,
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);
iby_debug_pub.add('No. of records updated = '|| no_cc,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
SELECT expirydate, expiry_sec_segment_id
INTO l_expiry_date, l_exp_sec_segment_id
FROM iby_creditcard
WHERE instrid = p_instrid;
SELECT payeeid, instrtype,
account_no, creditcard_no,
cc_number_hash1, cc_number_hash2,
acct_number_hash1, acct_number_hash2,
object_version_number, last_update_date
FROM iby_irf_risky_instr
FOR UPDATE;
UPDATE iby_irf_risky_instr
SET creditcard_no = NULL,
cc_number_hash1 = l_cc_hash1,
cc_number_hash2 = l_cc_hash2,
object_version_number = risky_instr_rec.object_version_number + 1,
last_update_date = SYSDATE
WHERE payeeid = risky_instr_rec.payeeid
AND instrtype = 'CREDITCARD'
AND creditcard_no = risky_instr_rec.creditcard_no;
--update the counter by 1
no_cc := no_cc + 1;
UPDATE iby_irf_risky_instr
SET account_no = NULL,
acct_number_hash1 = l_acct_no_hash1,
acct_number_hash2 = l_acct_no_hash2,
object_version_number = risky_instr_rec.object_version_number + 1,
last_update_date = SYSDATE
WHERE payeeid = risky_instr_rec.payeeid
AND instrtype = 'BANKACCOUNT'
AND account_no = risky_instr_rec.account_no;
--update the counter by 1
no_acct := no_acct + 1;
fnd_file.put_line(fnd_file.log,l_dbg_mod||': No. of cards updated: '|| no_cc);
fnd_file.put_line(fnd_file.log,l_dbg_mod||': No. of accounts updated: '|| no_acct);