The following lines contain the word 'select', 'insert', 'update' or 'delete':
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_PC_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 credit_card_mask_setting, credit_card_unmask_len
FROM iby_sys_security_options;
( p_is_update IN VARCHAR2, p_paymentcard IN PaymentCard_rec_type )
RETURN BOOLEAN
IS
lx_return_status VARCHAR2(1);
IF FND_API.to_Boolean(p_is_update) THEN
IF (l_addressid = FND_API.G_MISS_NUM) THEN
l_addressid := NULL;
SELECT instrid
FROM iby_paymentcard
WHERE (card_number = ci_card_number)
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_paymentcard_s.NEXTVAL INTO x_instr_id FROM DUAL;
INSERT INTO iby_paymentcard
(instrid, card_number, masked_card_number,
card_mask_setting, card_unmask_length,
expirydate, card_owner_id, chname,
addressid, billing_addr_postal_code, bill_addr_territory_code,
instrument_type, card_issuer_code, card_number_length,
description, finame,
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
)
VALUES
(x_instr_id, p_card_number, lx_masked_number,
lx_mask_option, lx_unmask_len,
l_expiry_date, p_owner_id, p_holder_name,
l_billing_site, p_billing_zip, p_billing_country,
p_instr_type, C_ISSUER_COMCHECK, l_card_len,
p_desc, p_fi_name,
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
);
PROCEDURE Update_Card
(p_commit IN VARCHAR2,
p_instr_id IN iby_paymentcard.instrid%TYPE,
p_owner_id IN iby_paymentcard.card_owner_id%TYPE,
p_holder_name IN iby_paymentcard.chname%TYPE,
p_billing_address_id IN iby_paymentcard.addressid%TYPE,
p_address_type IN VARCHAR2 := G_PARTY_SITE_ID,
p_billing_zip IN iby_paymentcard.billing_addr_postal_code%TYPE,
p_billing_country IN iby_paymentcard.bill_addr_territory_code%TYPE,
p_expiry_date IN iby_paymentcard.expirydate%TYPE,
p_instr_type IN iby_paymentcard.instrument_type%TYPE,
p_fi_name IN iby_paymentcard.finame%TYPE,
p_single_use IN iby_paymentcard.single_use_flag%TYPE,
p_info_only IN iby_paymentcard.information_only_flag%TYPE,
p_purpose IN iby_paymentcard.card_purpose%TYPE,
p_desc IN iby_paymentcard.description%TYPE,
p_active_flag IN iby_paymentcard.active_flag%TYPE,
p_inactive_date IN iby_paymentcard.inactive_date%TYPE,
p_attribute_category IN iby_paymentcard.attribute_category%TYPE,
p_attribute1 IN iby_paymentcard.attribute1%TYPE,
p_attribute2 IN iby_paymentcard.attribute2%TYPE,
p_attribute3 IN iby_paymentcard.attribute3%TYPE,
p_attribute4 IN iby_paymentcard.attribute4%TYPE,
p_attribute5 IN iby_paymentcard.attribute5%TYPE,
p_attribute6 IN iby_paymentcard.attribute6%TYPE,
p_attribute7 IN iby_paymentcard.attribute7%TYPE,
p_attribute8 IN iby_paymentcard.attribute8%TYPE,
p_attribute9 IN iby_paymentcard.attribute9%TYPE,
p_attribute10 IN iby_paymentcard.attribute10%TYPE,
p_attribute11 IN iby_paymentcard.attribute11%TYPE,
p_attribute12 IN iby_paymentcard.attribute12%TYPE,
p_attribute13 IN iby_paymentcard.attribute13%TYPE,
p_attribute14 IN iby_paymentcard.attribute14%TYPE,
p_attribute15 IN iby_paymentcard.attribute15%TYPE,
x_result_code OUT NOCOPY VARCHAR2
)
IS
l_billing_site NUMBER;
UPDATE iby_paymentcard
SET chname = DECODE(p_holder_name, FND_API.G_MISS_CHAR,NULL, NULL,chname, p_holder_name),
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),
instrument_type = NVL(p_instr_type, instrument_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
WHERE (instrid = p_instr_id);
END Update_Card;
Update_Card
(FND_API.G_FALSE, lx_card_rec.Card_Id, p_card_instrument.Owner_Id,
p_card_instrument.Card_Holder_Name,
p_card_instrument.Billing_Address_Id,
p_card_instrument.Address_Type,
p_card_instrument.Billing_Postal_Code,
p_card_instrument.Billing_Address_Territory,
p_card_instrument.Expiration_Date, p_card_instrument.Instrument_Type,
p_card_instrument.FI_Name, p_card_instrument.Single_Use_Flag,
l_info_only, p_card_instrument.Card_Purpose,
p_card_instrument.Card_Description, p_card_instrument.Active_Flag,
NVL(p_card_instrument.Inactive_Date,FND_API.G_MISS_DATE),
p_card_instrument.attribute_category,
p_card_instrument.attribute1, p_card_instrument.attribute2,
p_card_instrument.attribute3, p_card_instrument.attribute4,
p_card_instrument.attribute5, p_card_instrument.attribute6,
p_card_instrument.attribute7, p_card_instrument.attribute8,
p_card_instrument.attribute9, p_card_instrument.attribute10,
p_card_instrument.attribute11, p_card_instrument.attribute12,
p_card_instrument.attribute13, p_card_instrument.attribute14,
p_card_instrument.attribute15, lx_result_code);
PROCEDURE Update_Card
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_TRUE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_card_instrument IN PaymentCard_rec_type,
x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_module CONSTANT VARCHAR2(30) := 'Update_Card';
SAVEPOINT Update_Card;
Update_Card
(FND_API.G_FALSE, p_card_instrument.Card_Id, p_card_instrument.Owner_Id,
p_card_instrument.Card_Holder_Name,
p_card_instrument.Billing_Address_Id,
p_card_instrument.Address_Type,
p_card_instrument.Billing_Postal_Code,
p_card_instrument.Billing_Address_Territory,
p_card_instrument.Expiration_Date, p_card_instrument.Instrument_Type,
p_card_instrument.FI_Name, p_card_instrument.Single_Use_Flag,
l_info_only, p_card_instrument.Card_Purpose,
p_card_instrument.Card_Description, p_card_instrument.Active_Flag,
p_card_instrument.Inactive_Date,
p_card_instrument.attribute_category,
p_card_instrument.attribute1, p_card_instrument.attribute2,
p_card_instrument.attribute3, p_card_instrument.attribute4,
p_card_instrument.attribute5, p_card_instrument.attribute6,
p_card_instrument.attribute7, p_card_instrument.attribute8,
p_card_instrument.attribute9, p_card_instrument.attribute10,
p_card_instrument.attribute11, p_card_instrument.attribute12,
p_card_instrument.attribute13, p_card_instrument.attribute14,
p_card_instrument.attribute15,
lx_result_code);
ROLLBACK TO Update_Card;
ROLLBACK TO Update_Card;
ROLLBACK TO Update_Card;
END Update_Card;
SELECT c.instrid, c.card_number,
c.card_number_length card_len
FROM iby_paymentcard c
WHERE ( (NVL(card_unmask_length,-1) <> ci_unmask_len) OR
(NVL(card_mask_setting,' ') <> ci_mask_option)
);
UPDATE iby_paymentcard
SET
masked_card_number =
Mask_Card_Number(c_card_rec.card_number,lx_mask_option,lx_unmask_len),
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.card_number, c.masked_card_number, c.expirydate, c.instrument_type,
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
FROM iby_paymentcard 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
FROM iby_paymentcard
WHERE ( (card_owner_id = NVL(pi_card_owner,card_owner_id))
OR (card_owner_id IS NULL AND pi_card_owner IS NULL) )
AND (NVL(single_use_flag,'N')='N');
SELECT card_owner_id, chname, addressid, masked_card_number, expirydate,
instrument_type,
card_issuer_code, finame, single_use_flag,
information_only_flag, card_purpose, description, inactive_date
FROM iby_paymentcard
WHERE (instrid = pi_card_id);