[Home] [Help]
1: PACKAGE BODY iby_creditcard_pkg AS
2: /*$Header: ibyccb.pls 120.84.12020000.2 2013/04/12 15:06:14 dhati ship $*/
3:
4: --Variable to store the supplemental cardholder data encryption flag
5: enc_supl_data iby_sys_security_options.encrypt_supplemental_card_data%TYPE;
153:
154: IF (NOT l_keyvalid IS NULL) THEN
155: SELECT count(instrid)
156: INTO l_encrypted_count
157: FROM iby_creditcard
158: WHERE (NVL(encrypted,'N')<>'N');
159:
160: IF (l_encrypted_count>0) THEN
161: raise_application_error(-20000,l_keyvalid, FALSE);
165: END check_key;
166:
167: PROCEDURE encrypt_chname
168: (p_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
169: p_chname IN iby_creditcard.chname%TYPE,
170: p_segment_id IN NUMBER,
171: x_segment_id OUT NOCOPY NUMBER,
172: x_masked_chname OUT NOCOPY iby_creditcard.chname%TYPE,
173: x_mask_setting OUT NOCOPY iby_sys_security_options.credit_card_mask_setting%TYPE,
168: (p_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
169: p_chname IN iby_creditcard.chname%TYPE,
170: p_segment_id IN NUMBER,
171: x_segment_id OUT NOCOPY NUMBER,
172: x_masked_chname OUT NOCOPY iby_creditcard.chname%TYPE,
173: x_mask_setting OUT NOCOPY iby_sys_security_options.credit_card_mask_setting%TYPE,
174: x_unmask_len OUT NOCOPY iby_sys_security_options.credit_card_unmask_len%TYPE
175: )
176: IS
173: x_mask_setting OUT NOCOPY iby_sys_security_options.credit_card_mask_setting%TYPE,
174: x_unmask_len OUT NOCOPY iby_sys_security_options.credit_card_unmask_len%TYPE
175: )
176: IS
177: l_dbg_mod VARCHAR2(100) := 'iby.plsql.IBY_CREDITCARD_PKG' || '.' || 'encrypt_chname';
178: BEGIN
179: IBY_SECURITY_PKG.encrypt_chname(p_sec_key,
180: p_chname,
181: p_segment_id,
186: END encrypt_chname;
187:
188: FUNCTION decrypt_chname
189: (p_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
190: p_instrid IN iby_creditcard.instrid%TYPE
191: ) RETURN iby_creditcard.chname%TYPE
192: IS
193: BEGIN
194: RETURN IBY_SECURITY_PKG.decrypt_chname(p_sec_key, p_instrid);
187:
188: FUNCTION decrypt_chname
189: (p_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
190: p_instrid IN iby_creditcard.instrid%TYPE
191: ) RETURN iby_creditcard.chname%TYPE
192: IS
193: BEGIN
194: RETURN IBY_SECURITY_PKG.decrypt_chname(p_sec_key, p_instrid);
195: END decrypt_chname;
197: -- USE: Saves card information to the credit card history table
198: --
199: PROCEDURE Archive_Card
200: (p_commit IN VARCHAR2,
201: p_instr_id IN iby_creditcard.instrid%TYPE,
202: x_history_id OUT NOCOPY iby_creditcard_h.card_history_change_id%TYPE
203: )
204: IS
205: BEGIN
198: --
199: PROCEDURE Archive_Card
200: (p_commit IN VARCHAR2,
201: p_instr_id IN iby_creditcard.instrid%TYPE,
202: x_history_id OUT NOCOPY iby_creditcard_h.card_history_change_id%TYPE
203: )
204: IS
205: BEGIN
206:
206:
207: /*
208: * Fix for bug 5256903 by rameshsh:
209: *
210: * The active_flag column is nullable in IBY_CREDITCARD
211: * but nor in IBY_CREDITCARD_H.
212: *
213: * If active_flag is not set for a particular credit card
214: * in IBY_CREDITCARD, default the value to 'Y', otherwise
207: /*
208: * Fix for bug 5256903 by rameshsh:
209: *
210: * The active_flag column is nullable in IBY_CREDITCARD
211: * but nor in IBY_CREDITCARD_H.
212: *
213: * If active_flag is not set for a particular credit card
214: * in IBY_CREDITCARD, default the value to 'Y', otherwise
215: * this method will throw a 'cannot insert NULL exception ..'
210: * The active_flag column is nullable in IBY_CREDITCARD
211: * but nor in IBY_CREDITCARD_H.
212: *
213: * If active_flag is not set for a particular credit card
214: * in IBY_CREDITCARD, default the value to 'Y', otherwise
215: * this method will throw a 'cannot insert NULL exception ..'
216: */
217: SELECT iby_creditcard_h_s.NEXTVAL INTO x_history_id FROM dual;
218: INSERT INTO iby_creditcard_h
213: * If active_flag is not set for a particular credit card
214: * in IBY_CREDITCARD, default the value to 'Y', otherwise
215: * this method will throw a 'cannot insert NULL exception ..'
216: */
217: SELECT iby_creditcard_h_s.NEXTVAL INTO x_history_id FROM dual;
218: INSERT INTO iby_creditcard_h
219: (card_history_change_id, instrid, expirydate, expiry_sec_segment_id,
220: addressid,
221: description, chname, chname_sec_segment_id, finame, security_group_id,
214: * in IBY_CREDITCARD, default the value to 'Y', otherwise
215: * this method will throw a 'cannot insert NULL exception ..'
216: */
217: SELECT iby_creditcard_h_s.NEXTVAL INTO x_history_id FROM dual;
218: INSERT INTO iby_creditcard_h
219: (card_history_change_id, instrid, expirydate, expiry_sec_segment_id,
220: addressid,
221: description, chname, chname_sec_segment_id, finame, security_group_id,
222: encrypted,
248: attribute26, attribute27, attribute28, attribute29, attribute30,
249: request_id, program_application_id, program_id, program_update_date,
250: fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
251: fnd_global.login_id, 1
252: FROM iby_creditcard
253: WHERE (instrid = p_instr_id);
254:
255: IF FND_API.To_Boolean(p_commit) THEN
256: COMMIT;
288: END IF;
289: END Get_Mask_Settings;
290:
291: FUNCTION Mask_Card_Number
292: (p_cc_number IN iby_creditcard.ccnumber%TYPE,
293: p_mask_option IN iby_creditcard.card_mask_setting%TYPE,
294: p_unmask_len IN iby_creditcard.card_unmask_length%TYPE
295: )
296: RETURN iby_creditcard.masked_cc_number%TYPE
289: END Get_Mask_Settings;
290:
291: FUNCTION Mask_Card_Number
292: (p_cc_number IN iby_creditcard.ccnumber%TYPE,
293: p_mask_option IN iby_creditcard.card_mask_setting%TYPE,
294: p_unmask_len IN iby_creditcard.card_unmask_length%TYPE
295: )
296: RETURN iby_creditcard.masked_cc_number%TYPE
297: IS
290:
291: FUNCTION Mask_Card_Number
292: (p_cc_number IN iby_creditcard.ccnumber%TYPE,
293: p_mask_option IN iby_creditcard.card_mask_setting%TYPE,
294: p_unmask_len IN iby_creditcard.card_unmask_length%TYPE
295: )
296: RETURN iby_creditcard.masked_cc_number%TYPE
297: IS
298: BEGIN
292: (p_cc_number IN iby_creditcard.ccnumber%TYPE,
293: p_mask_option IN iby_creditcard.card_mask_setting%TYPE,
294: p_unmask_len IN iby_creditcard.card_unmask_length%TYPE
295: )
296: RETURN iby_creditcard.masked_cc_number%TYPE
297: IS
298: BEGIN
299: RETURN iby_security_pkg.Mask_Data
300: (p_cc_number,p_mask_option,p_unmask_len,G_MASK_CHARACTER);
303: --
304: -- Return: The masked card number, usable for display purposes
305: --
306: PROCEDURE Mask_Card_Number
307: (p_cc_number IN iby_creditcard.ccnumber%TYPE,
308: x_masked_number OUT NOCOPY iby_creditcard.masked_cc_number%TYPE,
309: x_mask_setting OUT NOCOPY iby_sys_security_options.credit_card_mask_setting%TYPE,
310: x_unmask_len OUT NOCOPY iby_sys_security_options.credit_card_unmask_len%TYPE
311: )
304: -- Return: The masked card number, usable for display purposes
305: --
306: PROCEDURE Mask_Card_Number
307: (p_cc_number IN iby_creditcard.ccnumber%TYPE,
308: x_masked_number OUT NOCOPY iby_creditcard.masked_cc_number%TYPE,
309: x_mask_setting OUT NOCOPY iby_sys_security_options.credit_card_mask_setting%TYPE,
310: x_unmask_len OUT NOCOPY iby_sys_security_options.credit_card_unmask_len%TYPE
311: )
312: IS
315: x_masked_number :=
316: Mask_Card_Number(p_cc_number,x_mask_setting,x_unmask_len);
317: END Mask_Card_Number;
318:
319: FUNCTION Mask_Card_Number(p_cc_number IN iby_creditcard.ccnumber%TYPE)
320: RETURN iby_creditcard.masked_cc_number%TYPE
321: IS
322: lx_mask_option iby_creditcard.card_mask_setting%TYPE;
323: lx_mask_number iby_creditcard.masked_cc_number%TYPE;
316: Mask_Card_Number(p_cc_number,x_mask_setting,x_unmask_len);
317: END Mask_Card_Number;
318:
319: FUNCTION Mask_Card_Number(p_cc_number IN iby_creditcard.ccnumber%TYPE)
320: RETURN iby_creditcard.masked_cc_number%TYPE
321: IS
322: lx_mask_option iby_creditcard.card_mask_setting%TYPE;
323: lx_mask_number iby_creditcard.masked_cc_number%TYPE;
324: lx_unmask_len iby_sys_security_options.credit_card_unmask_len%TYPE;
318:
319: FUNCTION Mask_Card_Number(p_cc_number IN iby_creditcard.ccnumber%TYPE)
320: RETURN iby_creditcard.masked_cc_number%TYPE
321: IS
322: lx_mask_option iby_creditcard.card_mask_setting%TYPE;
323: lx_mask_number iby_creditcard.masked_cc_number%TYPE;
324: lx_unmask_len iby_sys_security_options.credit_card_unmask_len%TYPE;
325: BEGIN
326: Mask_Card_Number(p_cc_number,lx_mask_number,lx_mask_option,lx_unmask_len);
319: FUNCTION Mask_Card_Number(p_cc_number IN iby_creditcard.ccnumber%TYPE)
320: RETURN iby_creditcard.masked_cc_number%TYPE
321: IS
322: lx_mask_option iby_creditcard.card_mask_setting%TYPE;
323: lx_mask_number iby_creditcard.masked_cc_number%TYPE;
324: lx_unmask_len iby_sys_security_options.credit_card_unmask_len%TYPE;
325: BEGIN
326: Mask_Card_Number(p_cc_number,lx_mask_number,lx_mask_option,lx_unmask_len);
327: RETURN lx_mask_number;
329:
330:
331: PROCEDURE Create_Card
332: (p_commit IN VARCHAR2,
333: p_owner_id IN iby_creditcard.card_owner_id%TYPE,
334: p_holder_name IN iby_creditcard.chname%TYPE,
335: p_billing_address_id IN iby_creditcard.addressid%TYPE,
336: p_address_type IN VARCHAR2,
337: p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
330:
331: PROCEDURE Create_Card
332: (p_commit IN VARCHAR2,
333: p_owner_id IN iby_creditcard.card_owner_id%TYPE,
334: p_holder_name IN iby_creditcard.chname%TYPE,
335: p_billing_address_id IN iby_creditcard.addressid%TYPE,
336: p_address_type IN VARCHAR2,
337: p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
338: p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
331: PROCEDURE Create_Card
332: (p_commit IN VARCHAR2,
333: p_owner_id IN iby_creditcard.card_owner_id%TYPE,
334: p_holder_name IN iby_creditcard.chname%TYPE,
335: p_billing_address_id IN iby_creditcard.addressid%TYPE,
336: p_address_type IN VARCHAR2,
337: p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
338: p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
339: p_card_number IN iby_creditcard.ccnumber%TYPE,
333: p_owner_id IN iby_creditcard.card_owner_id%TYPE,
334: p_holder_name IN iby_creditcard.chname%TYPE,
335: p_billing_address_id IN iby_creditcard.addressid%TYPE,
336: p_address_type IN VARCHAR2,
337: p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
338: p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
339: p_card_number IN iby_creditcard.ccnumber%TYPE,
340: p_expiry_date IN iby_creditcard.expirydate%TYPE,
341: p_instr_type IN iby_creditcard.instrument_type%TYPE,
334: p_holder_name IN iby_creditcard.chname%TYPE,
335: p_billing_address_id IN iby_creditcard.addressid%TYPE,
336: p_address_type IN VARCHAR2,
337: p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
338: p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
339: p_card_number IN iby_creditcard.ccnumber%TYPE,
340: p_expiry_date IN iby_creditcard.expirydate%TYPE,
341: p_instr_type IN iby_creditcard.instrument_type%TYPE,
342: p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
335: p_billing_address_id IN iby_creditcard.addressid%TYPE,
336: p_address_type IN VARCHAR2,
337: p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
338: p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
339: p_card_number IN iby_creditcard.ccnumber%TYPE,
340: p_expiry_date IN iby_creditcard.expirydate%TYPE,
341: p_instr_type IN iby_creditcard.instrument_type%TYPE,
342: p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
343: p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
336: p_address_type IN VARCHAR2,
337: p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
338: p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
339: p_card_number IN iby_creditcard.ccnumber%TYPE,
340: p_expiry_date IN iby_creditcard.expirydate%TYPE,
341: p_instr_type IN iby_creditcard.instrument_type%TYPE,
342: p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
343: p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
344: p_issuer IN iby_creditcard.card_issuer_code%TYPE,
337: p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
338: p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
339: p_card_number IN iby_creditcard.ccnumber%TYPE,
340: p_expiry_date IN iby_creditcard.expirydate%TYPE,
341: p_instr_type IN iby_creditcard.instrument_type%TYPE,
342: p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
343: p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
344: p_issuer IN iby_creditcard.card_issuer_code%TYPE,
345: p_fi_name IN iby_creditcard.finame%TYPE,
338: p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
339: p_card_number IN iby_creditcard.ccnumber%TYPE,
340: p_expiry_date IN iby_creditcard.expirydate%TYPE,
341: p_instr_type IN iby_creditcard.instrument_type%TYPE,
342: p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
343: p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
344: p_issuer IN iby_creditcard.card_issuer_code%TYPE,
345: p_fi_name IN iby_creditcard.finame%TYPE,
346: p_single_use IN iby_creditcard.single_use_flag%TYPE,
339: p_card_number IN iby_creditcard.ccnumber%TYPE,
340: p_expiry_date IN iby_creditcard.expirydate%TYPE,
341: p_instr_type IN iby_creditcard.instrument_type%TYPE,
342: p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
343: p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
344: p_issuer IN iby_creditcard.card_issuer_code%TYPE,
345: p_fi_name IN iby_creditcard.finame%TYPE,
346: p_single_use IN iby_creditcard.single_use_flag%TYPE,
347: p_info_only IN iby_creditcard.information_only_flag%TYPE,
340: p_expiry_date IN iby_creditcard.expirydate%TYPE,
341: p_instr_type IN iby_creditcard.instrument_type%TYPE,
342: p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
343: p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
344: p_issuer IN iby_creditcard.card_issuer_code%TYPE,
345: p_fi_name IN iby_creditcard.finame%TYPE,
346: p_single_use IN iby_creditcard.single_use_flag%TYPE,
347: p_info_only IN iby_creditcard.information_only_flag%TYPE,
348: p_purpose IN iby_creditcard.card_purpose%TYPE,
341: p_instr_type IN iby_creditcard.instrument_type%TYPE,
342: p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
343: p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
344: p_issuer IN iby_creditcard.card_issuer_code%TYPE,
345: p_fi_name IN iby_creditcard.finame%TYPE,
346: p_single_use IN iby_creditcard.single_use_flag%TYPE,
347: p_info_only IN iby_creditcard.information_only_flag%TYPE,
348: p_purpose IN iby_creditcard.card_purpose%TYPE,
349: p_desc IN iby_creditcard.description%TYPE,
342: p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
343: p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
344: p_issuer IN iby_creditcard.card_issuer_code%TYPE,
345: p_fi_name IN iby_creditcard.finame%TYPE,
346: p_single_use IN iby_creditcard.single_use_flag%TYPE,
347: p_info_only IN iby_creditcard.information_only_flag%TYPE,
348: p_purpose IN iby_creditcard.card_purpose%TYPE,
349: p_desc IN iby_creditcard.description%TYPE,
350: p_active_flag IN iby_creditcard.active_flag%TYPE,
343: p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
344: p_issuer IN iby_creditcard.card_issuer_code%TYPE,
345: p_fi_name IN iby_creditcard.finame%TYPE,
346: p_single_use IN iby_creditcard.single_use_flag%TYPE,
347: p_info_only IN iby_creditcard.information_only_flag%TYPE,
348: p_purpose IN iby_creditcard.card_purpose%TYPE,
349: p_desc IN iby_creditcard.description%TYPE,
350: p_active_flag IN iby_creditcard.active_flag%TYPE,
351: p_inactive_date IN iby_creditcard.inactive_date%TYPE,
344: p_issuer IN iby_creditcard.card_issuer_code%TYPE,
345: p_fi_name IN iby_creditcard.finame%TYPE,
346: p_single_use IN iby_creditcard.single_use_flag%TYPE,
347: p_info_only IN iby_creditcard.information_only_flag%TYPE,
348: p_purpose IN iby_creditcard.card_purpose%TYPE,
349: p_desc IN iby_creditcard.description%TYPE,
350: p_active_flag IN iby_creditcard.active_flag%TYPE,
351: p_inactive_date IN iby_creditcard.inactive_date%TYPE,
352: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
345: p_fi_name IN iby_creditcard.finame%TYPE,
346: p_single_use IN iby_creditcard.single_use_flag%TYPE,
347: p_info_only IN iby_creditcard.information_only_flag%TYPE,
348: p_purpose IN iby_creditcard.card_purpose%TYPE,
349: p_desc IN iby_creditcard.description%TYPE,
350: p_active_flag IN iby_creditcard.active_flag%TYPE,
351: p_inactive_date IN iby_creditcard.inactive_date%TYPE,
352: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
353: p_attribute_category IN iby_creditcard.attribute_category%TYPE,
346: p_single_use IN iby_creditcard.single_use_flag%TYPE,
347: p_info_only IN iby_creditcard.information_only_flag%TYPE,
348: p_purpose IN iby_creditcard.card_purpose%TYPE,
349: p_desc IN iby_creditcard.description%TYPE,
350: p_active_flag IN iby_creditcard.active_flag%TYPE,
351: p_inactive_date IN iby_creditcard.inactive_date%TYPE,
352: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
353: p_attribute_category IN iby_creditcard.attribute_category%TYPE,
354: p_attribute1 IN iby_creditcard.attribute1%TYPE,
347: p_info_only IN iby_creditcard.information_only_flag%TYPE,
348: p_purpose IN iby_creditcard.card_purpose%TYPE,
349: p_desc IN iby_creditcard.description%TYPE,
350: p_active_flag IN iby_creditcard.active_flag%TYPE,
351: p_inactive_date IN iby_creditcard.inactive_date%TYPE,
352: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
353: p_attribute_category IN iby_creditcard.attribute_category%TYPE,
354: p_attribute1 IN iby_creditcard.attribute1%TYPE,
355: p_attribute2 IN iby_creditcard.attribute2%TYPE,
349: p_desc IN iby_creditcard.description%TYPE,
350: p_active_flag IN iby_creditcard.active_flag%TYPE,
351: p_inactive_date IN iby_creditcard.inactive_date%TYPE,
352: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
353: p_attribute_category IN iby_creditcard.attribute_category%TYPE,
354: p_attribute1 IN iby_creditcard.attribute1%TYPE,
355: p_attribute2 IN iby_creditcard.attribute2%TYPE,
356: p_attribute3 IN iby_creditcard.attribute3%TYPE,
357: p_attribute4 IN iby_creditcard.attribute4%TYPE,
350: p_active_flag IN iby_creditcard.active_flag%TYPE,
351: p_inactive_date IN iby_creditcard.inactive_date%TYPE,
352: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
353: p_attribute_category IN iby_creditcard.attribute_category%TYPE,
354: p_attribute1 IN iby_creditcard.attribute1%TYPE,
355: p_attribute2 IN iby_creditcard.attribute2%TYPE,
356: p_attribute3 IN iby_creditcard.attribute3%TYPE,
357: p_attribute4 IN iby_creditcard.attribute4%TYPE,
358: p_attribute5 IN iby_creditcard.attribute5%TYPE,
351: p_inactive_date IN iby_creditcard.inactive_date%TYPE,
352: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
353: p_attribute_category IN iby_creditcard.attribute_category%TYPE,
354: p_attribute1 IN iby_creditcard.attribute1%TYPE,
355: p_attribute2 IN iby_creditcard.attribute2%TYPE,
356: p_attribute3 IN iby_creditcard.attribute3%TYPE,
357: p_attribute4 IN iby_creditcard.attribute4%TYPE,
358: p_attribute5 IN iby_creditcard.attribute5%TYPE,
359: p_attribute6 IN iby_creditcard.attribute6%TYPE,
352: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
353: p_attribute_category IN iby_creditcard.attribute_category%TYPE,
354: p_attribute1 IN iby_creditcard.attribute1%TYPE,
355: p_attribute2 IN iby_creditcard.attribute2%TYPE,
356: p_attribute3 IN iby_creditcard.attribute3%TYPE,
357: p_attribute4 IN iby_creditcard.attribute4%TYPE,
358: p_attribute5 IN iby_creditcard.attribute5%TYPE,
359: p_attribute6 IN iby_creditcard.attribute6%TYPE,
360: p_attribute7 IN iby_creditcard.attribute7%TYPE,
353: p_attribute_category IN iby_creditcard.attribute_category%TYPE,
354: p_attribute1 IN iby_creditcard.attribute1%TYPE,
355: p_attribute2 IN iby_creditcard.attribute2%TYPE,
356: p_attribute3 IN iby_creditcard.attribute3%TYPE,
357: p_attribute4 IN iby_creditcard.attribute4%TYPE,
358: p_attribute5 IN iby_creditcard.attribute5%TYPE,
359: p_attribute6 IN iby_creditcard.attribute6%TYPE,
360: p_attribute7 IN iby_creditcard.attribute7%TYPE,
361: p_attribute8 IN iby_creditcard.attribute8%TYPE,
354: p_attribute1 IN iby_creditcard.attribute1%TYPE,
355: p_attribute2 IN iby_creditcard.attribute2%TYPE,
356: p_attribute3 IN iby_creditcard.attribute3%TYPE,
357: p_attribute4 IN iby_creditcard.attribute4%TYPE,
358: p_attribute5 IN iby_creditcard.attribute5%TYPE,
359: p_attribute6 IN iby_creditcard.attribute6%TYPE,
360: p_attribute7 IN iby_creditcard.attribute7%TYPE,
361: p_attribute8 IN iby_creditcard.attribute8%TYPE,
362: p_attribute9 IN iby_creditcard.attribute9%TYPE,
355: p_attribute2 IN iby_creditcard.attribute2%TYPE,
356: p_attribute3 IN iby_creditcard.attribute3%TYPE,
357: p_attribute4 IN iby_creditcard.attribute4%TYPE,
358: p_attribute5 IN iby_creditcard.attribute5%TYPE,
359: p_attribute6 IN iby_creditcard.attribute6%TYPE,
360: p_attribute7 IN iby_creditcard.attribute7%TYPE,
361: p_attribute8 IN iby_creditcard.attribute8%TYPE,
362: p_attribute9 IN iby_creditcard.attribute9%TYPE,
363: p_attribute10 IN iby_creditcard.attribute10%TYPE,
356: p_attribute3 IN iby_creditcard.attribute3%TYPE,
357: p_attribute4 IN iby_creditcard.attribute4%TYPE,
358: p_attribute5 IN iby_creditcard.attribute5%TYPE,
359: p_attribute6 IN iby_creditcard.attribute6%TYPE,
360: p_attribute7 IN iby_creditcard.attribute7%TYPE,
361: p_attribute8 IN iby_creditcard.attribute8%TYPE,
362: p_attribute9 IN iby_creditcard.attribute9%TYPE,
363: p_attribute10 IN iby_creditcard.attribute10%TYPE,
364: p_attribute11 IN iby_creditcard.attribute11%TYPE,
357: p_attribute4 IN iby_creditcard.attribute4%TYPE,
358: p_attribute5 IN iby_creditcard.attribute5%TYPE,
359: p_attribute6 IN iby_creditcard.attribute6%TYPE,
360: p_attribute7 IN iby_creditcard.attribute7%TYPE,
361: p_attribute8 IN iby_creditcard.attribute8%TYPE,
362: p_attribute9 IN iby_creditcard.attribute9%TYPE,
363: p_attribute10 IN iby_creditcard.attribute10%TYPE,
364: p_attribute11 IN iby_creditcard.attribute11%TYPE,
365: p_attribute12 IN iby_creditcard.attribute12%TYPE,
358: p_attribute5 IN iby_creditcard.attribute5%TYPE,
359: p_attribute6 IN iby_creditcard.attribute6%TYPE,
360: p_attribute7 IN iby_creditcard.attribute7%TYPE,
361: p_attribute8 IN iby_creditcard.attribute8%TYPE,
362: p_attribute9 IN iby_creditcard.attribute9%TYPE,
363: p_attribute10 IN iby_creditcard.attribute10%TYPE,
364: p_attribute11 IN iby_creditcard.attribute11%TYPE,
365: p_attribute12 IN iby_creditcard.attribute12%TYPE,
366: p_attribute13 IN iby_creditcard.attribute13%TYPE,
359: p_attribute6 IN iby_creditcard.attribute6%TYPE,
360: p_attribute7 IN iby_creditcard.attribute7%TYPE,
361: p_attribute8 IN iby_creditcard.attribute8%TYPE,
362: p_attribute9 IN iby_creditcard.attribute9%TYPE,
363: p_attribute10 IN iby_creditcard.attribute10%TYPE,
364: p_attribute11 IN iby_creditcard.attribute11%TYPE,
365: p_attribute12 IN iby_creditcard.attribute12%TYPE,
366: p_attribute13 IN iby_creditcard.attribute13%TYPE,
367: p_attribute14 IN iby_creditcard.attribute14%TYPE,
360: p_attribute7 IN iby_creditcard.attribute7%TYPE,
361: p_attribute8 IN iby_creditcard.attribute8%TYPE,
362: p_attribute9 IN iby_creditcard.attribute9%TYPE,
363: p_attribute10 IN iby_creditcard.attribute10%TYPE,
364: p_attribute11 IN iby_creditcard.attribute11%TYPE,
365: p_attribute12 IN iby_creditcard.attribute12%TYPE,
366: p_attribute13 IN iby_creditcard.attribute13%TYPE,
367: p_attribute14 IN iby_creditcard.attribute14%TYPE,
368: p_attribute15 IN iby_creditcard.attribute15%TYPE,
361: p_attribute8 IN iby_creditcard.attribute8%TYPE,
362: p_attribute9 IN iby_creditcard.attribute9%TYPE,
363: p_attribute10 IN iby_creditcard.attribute10%TYPE,
364: p_attribute11 IN iby_creditcard.attribute11%TYPE,
365: p_attribute12 IN iby_creditcard.attribute12%TYPE,
366: p_attribute13 IN iby_creditcard.attribute13%TYPE,
367: p_attribute14 IN iby_creditcard.attribute14%TYPE,
368: p_attribute15 IN iby_creditcard.attribute15%TYPE,
369: p_attribute16 IN iby_creditcard.attribute16%TYPE,
362: p_attribute9 IN iby_creditcard.attribute9%TYPE,
363: p_attribute10 IN iby_creditcard.attribute10%TYPE,
364: p_attribute11 IN iby_creditcard.attribute11%TYPE,
365: p_attribute12 IN iby_creditcard.attribute12%TYPE,
366: p_attribute13 IN iby_creditcard.attribute13%TYPE,
367: p_attribute14 IN iby_creditcard.attribute14%TYPE,
368: p_attribute15 IN iby_creditcard.attribute15%TYPE,
369: p_attribute16 IN iby_creditcard.attribute16%TYPE,
370: p_attribute17 IN iby_creditcard.attribute17%TYPE,
363: p_attribute10 IN iby_creditcard.attribute10%TYPE,
364: p_attribute11 IN iby_creditcard.attribute11%TYPE,
365: p_attribute12 IN iby_creditcard.attribute12%TYPE,
366: p_attribute13 IN iby_creditcard.attribute13%TYPE,
367: p_attribute14 IN iby_creditcard.attribute14%TYPE,
368: p_attribute15 IN iby_creditcard.attribute15%TYPE,
369: p_attribute16 IN iby_creditcard.attribute16%TYPE,
370: p_attribute17 IN iby_creditcard.attribute17%TYPE,
371: p_attribute18 IN iby_creditcard.attribute18%TYPE,
364: p_attribute11 IN iby_creditcard.attribute11%TYPE,
365: p_attribute12 IN iby_creditcard.attribute12%TYPE,
366: p_attribute13 IN iby_creditcard.attribute13%TYPE,
367: p_attribute14 IN iby_creditcard.attribute14%TYPE,
368: p_attribute15 IN iby_creditcard.attribute15%TYPE,
369: p_attribute16 IN iby_creditcard.attribute16%TYPE,
370: p_attribute17 IN iby_creditcard.attribute17%TYPE,
371: p_attribute18 IN iby_creditcard.attribute18%TYPE,
372: p_attribute19 IN iby_creditcard.attribute19%TYPE,
365: p_attribute12 IN iby_creditcard.attribute12%TYPE,
366: p_attribute13 IN iby_creditcard.attribute13%TYPE,
367: p_attribute14 IN iby_creditcard.attribute14%TYPE,
368: p_attribute15 IN iby_creditcard.attribute15%TYPE,
369: p_attribute16 IN iby_creditcard.attribute16%TYPE,
370: p_attribute17 IN iby_creditcard.attribute17%TYPE,
371: p_attribute18 IN iby_creditcard.attribute18%TYPE,
372: p_attribute19 IN iby_creditcard.attribute19%TYPE,
373: p_attribute20 IN iby_creditcard.attribute20%TYPE,
366: p_attribute13 IN iby_creditcard.attribute13%TYPE,
367: p_attribute14 IN iby_creditcard.attribute14%TYPE,
368: p_attribute15 IN iby_creditcard.attribute15%TYPE,
369: p_attribute16 IN iby_creditcard.attribute16%TYPE,
370: p_attribute17 IN iby_creditcard.attribute17%TYPE,
371: p_attribute18 IN iby_creditcard.attribute18%TYPE,
372: p_attribute19 IN iby_creditcard.attribute19%TYPE,
373: p_attribute20 IN iby_creditcard.attribute20%TYPE,
374: p_attribute21 IN iby_creditcard.attribute21%TYPE,
367: p_attribute14 IN iby_creditcard.attribute14%TYPE,
368: p_attribute15 IN iby_creditcard.attribute15%TYPE,
369: p_attribute16 IN iby_creditcard.attribute16%TYPE,
370: p_attribute17 IN iby_creditcard.attribute17%TYPE,
371: p_attribute18 IN iby_creditcard.attribute18%TYPE,
372: p_attribute19 IN iby_creditcard.attribute19%TYPE,
373: p_attribute20 IN iby_creditcard.attribute20%TYPE,
374: p_attribute21 IN iby_creditcard.attribute21%TYPE,
375: p_attribute22 IN iby_creditcard.attribute22%TYPE,
368: p_attribute15 IN iby_creditcard.attribute15%TYPE,
369: p_attribute16 IN iby_creditcard.attribute16%TYPE,
370: p_attribute17 IN iby_creditcard.attribute17%TYPE,
371: p_attribute18 IN iby_creditcard.attribute18%TYPE,
372: p_attribute19 IN iby_creditcard.attribute19%TYPE,
373: p_attribute20 IN iby_creditcard.attribute20%TYPE,
374: p_attribute21 IN iby_creditcard.attribute21%TYPE,
375: p_attribute22 IN iby_creditcard.attribute22%TYPE,
376: p_attribute23 IN iby_creditcard.attribute23%TYPE,
369: p_attribute16 IN iby_creditcard.attribute16%TYPE,
370: p_attribute17 IN iby_creditcard.attribute17%TYPE,
371: p_attribute18 IN iby_creditcard.attribute18%TYPE,
372: p_attribute19 IN iby_creditcard.attribute19%TYPE,
373: p_attribute20 IN iby_creditcard.attribute20%TYPE,
374: p_attribute21 IN iby_creditcard.attribute21%TYPE,
375: p_attribute22 IN iby_creditcard.attribute22%TYPE,
376: p_attribute23 IN iby_creditcard.attribute23%TYPE,
377: p_attribute24 IN iby_creditcard.attribute24%TYPE,
370: p_attribute17 IN iby_creditcard.attribute17%TYPE,
371: p_attribute18 IN iby_creditcard.attribute18%TYPE,
372: p_attribute19 IN iby_creditcard.attribute19%TYPE,
373: p_attribute20 IN iby_creditcard.attribute20%TYPE,
374: p_attribute21 IN iby_creditcard.attribute21%TYPE,
375: p_attribute22 IN iby_creditcard.attribute22%TYPE,
376: p_attribute23 IN iby_creditcard.attribute23%TYPE,
377: p_attribute24 IN iby_creditcard.attribute24%TYPE,
378: p_attribute25 IN iby_creditcard.attribute25%TYPE,
371: p_attribute18 IN iby_creditcard.attribute18%TYPE,
372: p_attribute19 IN iby_creditcard.attribute19%TYPE,
373: p_attribute20 IN iby_creditcard.attribute20%TYPE,
374: p_attribute21 IN iby_creditcard.attribute21%TYPE,
375: p_attribute22 IN iby_creditcard.attribute22%TYPE,
376: p_attribute23 IN iby_creditcard.attribute23%TYPE,
377: p_attribute24 IN iby_creditcard.attribute24%TYPE,
378: p_attribute25 IN iby_creditcard.attribute25%TYPE,
379: p_attribute26 IN iby_creditcard.attribute26%TYPE,
372: p_attribute19 IN iby_creditcard.attribute19%TYPE,
373: p_attribute20 IN iby_creditcard.attribute20%TYPE,
374: p_attribute21 IN iby_creditcard.attribute21%TYPE,
375: p_attribute22 IN iby_creditcard.attribute22%TYPE,
376: p_attribute23 IN iby_creditcard.attribute23%TYPE,
377: p_attribute24 IN iby_creditcard.attribute24%TYPE,
378: p_attribute25 IN iby_creditcard.attribute25%TYPE,
379: p_attribute26 IN iby_creditcard.attribute26%TYPE,
380: p_attribute27 IN iby_creditcard.attribute27%TYPE,
373: p_attribute20 IN iby_creditcard.attribute20%TYPE,
374: p_attribute21 IN iby_creditcard.attribute21%TYPE,
375: p_attribute22 IN iby_creditcard.attribute22%TYPE,
376: p_attribute23 IN iby_creditcard.attribute23%TYPE,
377: p_attribute24 IN iby_creditcard.attribute24%TYPE,
378: p_attribute25 IN iby_creditcard.attribute25%TYPE,
379: p_attribute26 IN iby_creditcard.attribute26%TYPE,
380: p_attribute27 IN iby_creditcard.attribute27%TYPE,
381: p_attribute28 IN iby_creditcard.attribute28%TYPE,
374: p_attribute21 IN iby_creditcard.attribute21%TYPE,
375: p_attribute22 IN iby_creditcard.attribute22%TYPE,
376: p_attribute23 IN iby_creditcard.attribute23%TYPE,
377: p_attribute24 IN iby_creditcard.attribute24%TYPE,
378: p_attribute25 IN iby_creditcard.attribute25%TYPE,
379: p_attribute26 IN iby_creditcard.attribute26%TYPE,
380: p_attribute27 IN iby_creditcard.attribute27%TYPE,
381: p_attribute28 IN iby_creditcard.attribute28%TYPE,
382: p_attribute29 IN iby_creditcard.attribute29%TYPE,
375: p_attribute22 IN iby_creditcard.attribute22%TYPE,
376: p_attribute23 IN iby_creditcard.attribute23%TYPE,
377: p_attribute24 IN iby_creditcard.attribute24%TYPE,
378: p_attribute25 IN iby_creditcard.attribute25%TYPE,
379: p_attribute26 IN iby_creditcard.attribute26%TYPE,
380: p_attribute27 IN iby_creditcard.attribute27%TYPE,
381: p_attribute28 IN iby_creditcard.attribute28%TYPE,
382: p_attribute29 IN iby_creditcard.attribute29%TYPE,
383: p_attribute30 IN iby_creditcard.attribute30%TYPE,
376: p_attribute23 IN iby_creditcard.attribute23%TYPE,
377: p_attribute24 IN iby_creditcard.attribute24%TYPE,
378: p_attribute25 IN iby_creditcard.attribute25%TYPE,
379: p_attribute26 IN iby_creditcard.attribute26%TYPE,
380: p_attribute27 IN iby_creditcard.attribute27%TYPE,
381: p_attribute28 IN iby_creditcard.attribute28%TYPE,
382: p_attribute29 IN iby_creditcard.attribute29%TYPE,
383: p_attribute30 IN iby_creditcard.attribute30%TYPE,
384: x_result_code OUT NOCOPY VARCHAR2,
377: p_attribute24 IN iby_creditcard.attribute24%TYPE,
378: p_attribute25 IN iby_creditcard.attribute25%TYPE,
379: p_attribute26 IN iby_creditcard.attribute26%TYPE,
380: p_attribute27 IN iby_creditcard.attribute27%TYPE,
381: p_attribute28 IN iby_creditcard.attribute28%TYPE,
382: p_attribute29 IN iby_creditcard.attribute29%TYPE,
383: p_attribute30 IN iby_creditcard.attribute30%TYPE,
384: x_result_code OUT NOCOPY VARCHAR2,
385: x_instr_id OUT NOCOPY iby_creditcard.instrid%TYPE,
378: p_attribute25 IN iby_creditcard.attribute25%TYPE,
379: p_attribute26 IN iby_creditcard.attribute26%TYPE,
380: p_attribute27 IN iby_creditcard.attribute27%TYPE,
381: p_attribute28 IN iby_creditcard.attribute28%TYPE,
382: p_attribute29 IN iby_creditcard.attribute29%TYPE,
383: p_attribute30 IN iby_creditcard.attribute30%TYPE,
384: x_result_code OUT NOCOPY VARCHAR2,
385: x_instr_id OUT NOCOPY iby_creditcard.instrid%TYPE,
386: p_allow_invalid_card IN VARCHAR2,
379: p_attribute26 IN iby_creditcard.attribute26%TYPE,
380: p_attribute27 IN iby_creditcard.attribute27%TYPE,
381: p_attribute28 IN iby_creditcard.attribute28%TYPE,
382: p_attribute29 IN iby_creditcard.attribute29%TYPE,
383: p_attribute30 IN iby_creditcard.attribute30%TYPE,
384: x_result_code OUT NOCOPY VARCHAR2,
385: x_instr_id OUT NOCOPY iby_creditcard.instrid%TYPE,
386: p_allow_invalid_card IN VARCHAR2,
387: p_user_id IN NUMBER,
381: p_attribute28 IN iby_creditcard.attribute28%TYPE,
382: p_attribute29 IN iby_creditcard.attribute29%TYPE,
383: p_attribute30 IN iby_creditcard.attribute30%TYPE,
384: x_result_code OUT NOCOPY VARCHAR2,
385: x_instr_id OUT NOCOPY iby_creditcard.instrid%TYPE,
386: p_allow_invalid_card IN VARCHAR2,
387: p_user_id IN NUMBER,
388: p_login_id IN NUMBER
389: )
392: lx_checksum_valid BOOLEAN := FALSE;
393: lx_return_status VARCHAR2(1);
394: lx_msg_count NUMBER;
395: lx_msg_data VARCHAR2(200);
396: lx_card_issuer iby_creditcard.card_issuer_code%TYPE;
397: lx_issuer_range iby_creditcard.cc_issuer_range_id%TYPE;
398: lx_card_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE;
399: lx_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE;
400: l_card_len iby_creditcard.cc_number_length%TYPE;
393: lx_return_status VARCHAR2(1);
394: lx_msg_count NUMBER;
395: lx_msg_data VARCHAR2(200);
396: lx_card_issuer iby_creditcard.card_issuer_code%TYPE;
397: lx_issuer_range iby_creditcard.cc_issuer_range_id%TYPE;
398: lx_card_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE;
399: lx_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE;
400: l_card_len iby_creditcard.cc_number_length%TYPE;
401:
395: lx_msg_data VARCHAR2(200);
396: lx_card_issuer iby_creditcard.card_issuer_code%TYPE;
397: lx_issuer_range iby_creditcard.cc_issuer_range_id%TYPE;
398: lx_card_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE;
399: lx_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE;
400: l_card_len iby_creditcard.cc_number_length%TYPE;
401:
402: lx_cc_number iby_creditcard.ccnumber%TYPE;
403: lx_cc_compressed iby_creditcard.ccnumber%TYPE;
396: lx_card_issuer iby_creditcard.card_issuer_code%TYPE;
397: lx_issuer_range iby_creditcard.cc_issuer_range_id%TYPE;
398: lx_card_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE;
399: lx_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE;
400: l_card_len iby_creditcard.cc_number_length%TYPE;
401:
402: lx_cc_number iby_creditcard.ccnumber%TYPE;
403: lx_cc_compressed iby_creditcard.ccnumber%TYPE;
404: lx_unmasked_digits iby_creditcard.ccnumber%TYPE;
398: lx_card_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE;
399: lx_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE;
400: l_card_len iby_creditcard.cc_number_length%TYPE;
401:
402: lx_cc_number iby_creditcard.ccnumber%TYPE;
403: lx_cc_compressed iby_creditcard.ccnumber%TYPE;
404: lx_unmasked_digits iby_creditcard.ccnumber%TYPE;
405: l_cc_ciphertext iby_security_segments.segment_cipher_text%TYPE;
406: l_encrypted iby_creditcard.encrypted%TYPE;
399: lx_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE;
400: l_card_len iby_creditcard.cc_number_length%TYPE;
401:
402: lx_cc_number iby_creditcard.ccnumber%TYPE;
403: lx_cc_compressed iby_creditcard.ccnumber%TYPE;
404: lx_unmasked_digits iby_creditcard.ccnumber%TYPE;
405: l_cc_ciphertext iby_security_segments.segment_cipher_text%TYPE;
406: l_encrypted iby_creditcard.encrypted%TYPE;
407: lx_masked_number iby_creditcard.masked_cc_number%TYPE;
400: l_card_len iby_creditcard.cc_number_length%TYPE;
401:
402: lx_cc_number iby_creditcard.ccnumber%TYPE;
403: lx_cc_compressed iby_creditcard.ccnumber%TYPE;
404: lx_unmasked_digits iby_creditcard.ccnumber%TYPE;
405: l_cc_ciphertext iby_security_segments.segment_cipher_text%TYPE;
406: l_encrypted iby_creditcard.encrypted%TYPE;
407: lx_masked_number iby_creditcard.masked_cc_number%TYPE;
408: lx_mask_option iby_creditcard.card_mask_setting%TYPE;
402: lx_cc_number iby_creditcard.ccnumber%TYPE;
403: lx_cc_compressed iby_creditcard.ccnumber%TYPE;
404: lx_unmasked_digits iby_creditcard.ccnumber%TYPE;
405: l_cc_ciphertext iby_security_segments.segment_cipher_text%TYPE;
406: l_encrypted iby_creditcard.encrypted%TYPE;
407: lx_masked_number iby_creditcard.masked_cc_number%TYPE;
408: lx_mask_option iby_creditcard.card_mask_setting%TYPE;
409: lx_unmask_len iby_creditcard.card_unmask_length%TYPE;
410: l_cc_hash1 iby_creditcard.cc_number_hash1%TYPE;
403: lx_cc_compressed iby_creditcard.ccnumber%TYPE;
404: lx_unmasked_digits iby_creditcard.ccnumber%TYPE;
405: l_cc_ciphertext iby_security_segments.segment_cipher_text%TYPE;
406: l_encrypted iby_creditcard.encrypted%TYPE;
407: lx_masked_number iby_creditcard.masked_cc_number%TYPE;
408: lx_mask_option iby_creditcard.card_mask_setting%TYPE;
409: lx_unmask_len iby_creditcard.card_unmask_length%TYPE;
410: l_cc_hash1 iby_creditcard.cc_number_hash1%TYPE;
411: l_cc_hash2 iby_creditcard.cc_number_hash2%TYPE;
404: lx_unmasked_digits iby_creditcard.ccnumber%TYPE;
405: l_cc_ciphertext iby_security_segments.segment_cipher_text%TYPE;
406: l_encrypted iby_creditcard.encrypted%TYPE;
407: lx_masked_number iby_creditcard.masked_cc_number%TYPE;
408: lx_mask_option iby_creditcard.card_mask_setting%TYPE;
409: lx_unmask_len iby_creditcard.card_unmask_length%TYPE;
410: l_cc_hash1 iby_creditcard.cc_number_hash1%TYPE;
411: l_cc_hash2 iby_creditcard.cc_number_hash2%TYPE;
412: lx_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
405: l_cc_ciphertext iby_security_segments.segment_cipher_text%TYPE;
406: l_encrypted iby_creditcard.encrypted%TYPE;
407: lx_masked_number iby_creditcard.masked_cc_number%TYPE;
408: lx_mask_option iby_creditcard.card_mask_setting%TYPE;
409: lx_unmask_len iby_creditcard.card_unmask_length%TYPE;
410: l_cc_hash1 iby_creditcard.cc_number_hash1%TYPE;
411: l_cc_hash2 iby_creditcard.cc_number_hash2%TYPE;
412: lx_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
413:
406: l_encrypted iby_creditcard.encrypted%TYPE;
407: lx_masked_number iby_creditcard.masked_cc_number%TYPE;
408: lx_mask_option iby_creditcard.card_mask_setting%TYPE;
409: lx_unmask_len iby_creditcard.card_unmask_length%TYPE;
410: l_cc_hash1 iby_creditcard.cc_number_hash1%TYPE;
411: l_cc_hash2 iby_creditcard.cc_number_hash2%TYPE;
412: lx_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
413:
414: l_expiry_date iby_creditcard.expirydate%TYPE;
407: lx_masked_number iby_creditcard.masked_cc_number%TYPE;
408: lx_mask_option iby_creditcard.card_mask_setting%TYPE;
409: lx_unmask_len iby_creditcard.card_unmask_length%TYPE;
410: l_cc_hash1 iby_creditcard.cc_number_hash1%TYPE;
411: l_cc_hash2 iby_creditcard.cc_number_hash2%TYPE;
412: lx_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
413:
414: l_expiry_date iby_creditcard.expirydate%TYPE;
415:
410: l_cc_hash1 iby_creditcard.cc_number_hash1%TYPE;
411: l_cc_hash2 iby_creditcard.cc_number_hash2%TYPE;
412: lx_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
413:
414: l_expiry_date iby_creditcard.expirydate%TYPE;
415:
416: --l_billing_site hz_party_site_uses.party_site_use_id%TYPE;-- will not use this variable any more
417:
418: -- variabled for CHNAME and EXPDATE encryption
436: l_allow_invalid_card VARCHAR2(1);
437:
438: CURSOR c_card
439: (ci_owner_id IN hz_parties.party_id%TYPE,
440: ci_hash1 IN iby_creditcard.cc_number_hash1%TYPE,
441: ci_hash2 IN iby_creditcard.cc_number_hash1%TYPE
442: )
443: IS
444: SELECT instrid
437:
438: CURSOR c_card
439: (ci_owner_id IN hz_parties.party_id%TYPE,
440: ci_hash1 IN iby_creditcard.cc_number_hash1%TYPE,
441: ci_hash2 IN iby_creditcard.cc_number_hash1%TYPE
442: )
443: IS
444: SELECT instrid
445: FROM iby_creditcard
441: ci_hash2 IN iby_creditcard.cc_number_hash1%TYPE
442: )
443: IS
444: SELECT instrid
445: FROM iby_creditcard
446: WHERE (cc_number_hash1 = ci_hash1)
447: AND (cc_number_hash2 = ci_hash2)
448: AND ( (NVL(card_owner_id,ci_owner_id) = NVL(ci_owner_id,card_owner_id))
449: OR (card_owner_id IS NULL AND ci_owner_id IS NULL)
680: -- l_chname_length := NVL(LENGTH(p_holder_name), 0);
681:
682: SELECT iby_instr_s.NEXTVAL INTO x_instr_id FROM DUAL;
683:
684: INSERT INTO iby_creditcard
685: (instrid, ccnumber, masked_cc_number,
686: card_mask_setting, card_unmask_length, cc_number_hash1, cc_number_hash2,
687: expirydate, expiry_sec_segment_id, expired_flag,
688: card_owner_id, chname, chname_sec_segment_id,
742: END Create_Card;
743:
744: PROCEDURE Update_Card
745: (p_commit IN VARCHAR2,
746: p_instr_id IN iby_creditcard.instrid%TYPE,
747: p_owner_id IN iby_creditcard.card_owner_id%TYPE,
748: p_holder_name IN iby_creditcard.chname%TYPE,
749: p_billing_address_id IN iby_creditcard.addressid%TYPE,
750: p_address_type IN VARCHAR2,
743:
744: PROCEDURE Update_Card
745: (p_commit IN VARCHAR2,
746: p_instr_id IN iby_creditcard.instrid%TYPE,
747: p_owner_id IN iby_creditcard.card_owner_id%TYPE,
748: p_holder_name IN iby_creditcard.chname%TYPE,
749: p_billing_address_id IN iby_creditcard.addressid%TYPE,
750: p_address_type IN VARCHAR2,
751: p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
744: PROCEDURE Update_Card
745: (p_commit IN VARCHAR2,
746: p_instr_id IN iby_creditcard.instrid%TYPE,
747: p_owner_id IN iby_creditcard.card_owner_id%TYPE,
748: p_holder_name IN iby_creditcard.chname%TYPE,
749: p_billing_address_id IN iby_creditcard.addressid%TYPE,
750: p_address_type IN VARCHAR2,
751: p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
752: p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
745: (p_commit IN VARCHAR2,
746: p_instr_id IN iby_creditcard.instrid%TYPE,
747: p_owner_id IN iby_creditcard.card_owner_id%TYPE,
748: p_holder_name IN iby_creditcard.chname%TYPE,
749: p_billing_address_id IN iby_creditcard.addressid%TYPE,
750: p_address_type IN VARCHAR2,
751: p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
752: p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
753: p_expiry_date IN iby_creditcard.expirydate%TYPE,
747: p_owner_id IN iby_creditcard.card_owner_id%TYPE,
748: p_holder_name IN iby_creditcard.chname%TYPE,
749: p_billing_address_id IN iby_creditcard.addressid%TYPE,
750: p_address_type IN VARCHAR2,
751: p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
752: p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
753: p_expiry_date IN iby_creditcard.expirydate%TYPE,
754: p_instr_type IN iby_creditcard.instrument_type%TYPE,
755: p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
748: p_holder_name IN iby_creditcard.chname%TYPE,
749: p_billing_address_id IN iby_creditcard.addressid%TYPE,
750: p_address_type IN VARCHAR2,
751: p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
752: p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
753: p_expiry_date IN iby_creditcard.expirydate%TYPE,
754: p_instr_type IN iby_creditcard.instrument_type%TYPE,
755: p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
756: p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
749: p_billing_address_id IN iby_creditcard.addressid%TYPE,
750: p_address_type IN VARCHAR2,
751: p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
752: p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
753: p_expiry_date IN iby_creditcard.expirydate%TYPE,
754: p_instr_type IN iby_creditcard.instrument_type%TYPE,
755: p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
756: p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
757: p_fi_name IN iby_creditcard.finame%TYPE,
750: p_address_type IN VARCHAR2,
751: p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
752: p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
753: p_expiry_date IN iby_creditcard.expirydate%TYPE,
754: p_instr_type IN iby_creditcard.instrument_type%TYPE,
755: p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
756: p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
757: p_fi_name IN iby_creditcard.finame%TYPE,
758: p_single_use IN iby_creditcard.single_use_flag%TYPE,
751: p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
752: p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
753: p_expiry_date IN iby_creditcard.expirydate%TYPE,
754: p_instr_type IN iby_creditcard.instrument_type%TYPE,
755: p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
756: p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
757: p_fi_name IN iby_creditcard.finame%TYPE,
758: p_single_use IN iby_creditcard.single_use_flag%TYPE,
759: p_info_only IN iby_creditcard.information_only_flag%TYPE,
752: p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
753: p_expiry_date IN iby_creditcard.expirydate%TYPE,
754: p_instr_type IN iby_creditcard.instrument_type%TYPE,
755: p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
756: p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
757: p_fi_name IN iby_creditcard.finame%TYPE,
758: p_single_use IN iby_creditcard.single_use_flag%TYPE,
759: p_info_only IN iby_creditcard.information_only_flag%TYPE,
760: p_purpose IN iby_creditcard.card_purpose%TYPE,
753: p_expiry_date IN iby_creditcard.expirydate%TYPE,
754: p_instr_type IN iby_creditcard.instrument_type%TYPE,
755: p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
756: p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
757: p_fi_name IN iby_creditcard.finame%TYPE,
758: p_single_use IN iby_creditcard.single_use_flag%TYPE,
759: p_info_only IN iby_creditcard.information_only_flag%TYPE,
760: p_purpose IN iby_creditcard.card_purpose%TYPE,
761: p_desc IN iby_creditcard.description%TYPE,
754: p_instr_type IN iby_creditcard.instrument_type%TYPE,
755: p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
756: p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
757: p_fi_name IN iby_creditcard.finame%TYPE,
758: p_single_use IN iby_creditcard.single_use_flag%TYPE,
759: p_info_only IN iby_creditcard.information_only_flag%TYPE,
760: p_purpose IN iby_creditcard.card_purpose%TYPE,
761: p_desc IN iby_creditcard.description%TYPE,
762: p_active_flag IN iby_creditcard.active_flag%TYPE,
755: p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
756: p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
757: p_fi_name IN iby_creditcard.finame%TYPE,
758: p_single_use IN iby_creditcard.single_use_flag%TYPE,
759: p_info_only IN iby_creditcard.information_only_flag%TYPE,
760: p_purpose IN iby_creditcard.card_purpose%TYPE,
761: p_desc IN iby_creditcard.description%TYPE,
762: p_active_flag IN iby_creditcard.active_flag%TYPE,
763: p_inactive_date IN iby_creditcard.inactive_date%TYPE,
756: p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
757: p_fi_name IN iby_creditcard.finame%TYPE,
758: p_single_use IN iby_creditcard.single_use_flag%TYPE,
759: p_info_only IN iby_creditcard.information_only_flag%TYPE,
760: p_purpose IN iby_creditcard.card_purpose%TYPE,
761: p_desc IN iby_creditcard.description%TYPE,
762: p_active_flag IN iby_creditcard.active_flag%TYPE,
763: p_inactive_date IN iby_creditcard.inactive_date%TYPE,
764: p_attribute_category IN iby_creditcard.attribute_category%TYPE,
757: p_fi_name IN iby_creditcard.finame%TYPE,
758: p_single_use IN iby_creditcard.single_use_flag%TYPE,
759: p_info_only IN iby_creditcard.information_only_flag%TYPE,
760: p_purpose IN iby_creditcard.card_purpose%TYPE,
761: p_desc IN iby_creditcard.description%TYPE,
762: p_active_flag IN iby_creditcard.active_flag%TYPE,
763: p_inactive_date IN iby_creditcard.inactive_date%TYPE,
764: p_attribute_category IN iby_creditcard.attribute_category%TYPE,
765: p_attribute1 IN iby_creditcard.attribute1%TYPE,
758: p_single_use IN iby_creditcard.single_use_flag%TYPE,
759: p_info_only IN iby_creditcard.information_only_flag%TYPE,
760: p_purpose IN iby_creditcard.card_purpose%TYPE,
761: p_desc IN iby_creditcard.description%TYPE,
762: p_active_flag IN iby_creditcard.active_flag%TYPE,
763: p_inactive_date IN iby_creditcard.inactive_date%TYPE,
764: p_attribute_category IN iby_creditcard.attribute_category%TYPE,
765: p_attribute1 IN iby_creditcard.attribute1%TYPE,
766: p_attribute2 IN iby_creditcard.attribute2%TYPE,
759: p_info_only IN iby_creditcard.information_only_flag%TYPE,
760: p_purpose IN iby_creditcard.card_purpose%TYPE,
761: p_desc IN iby_creditcard.description%TYPE,
762: p_active_flag IN iby_creditcard.active_flag%TYPE,
763: p_inactive_date IN iby_creditcard.inactive_date%TYPE,
764: p_attribute_category IN iby_creditcard.attribute_category%TYPE,
765: p_attribute1 IN iby_creditcard.attribute1%TYPE,
766: p_attribute2 IN iby_creditcard.attribute2%TYPE,
767: p_attribute3 IN iby_creditcard.attribute3%TYPE,
760: p_purpose IN iby_creditcard.card_purpose%TYPE,
761: p_desc IN iby_creditcard.description%TYPE,
762: p_active_flag IN iby_creditcard.active_flag%TYPE,
763: p_inactive_date IN iby_creditcard.inactive_date%TYPE,
764: p_attribute_category IN iby_creditcard.attribute_category%TYPE,
765: p_attribute1 IN iby_creditcard.attribute1%TYPE,
766: p_attribute2 IN iby_creditcard.attribute2%TYPE,
767: p_attribute3 IN iby_creditcard.attribute3%TYPE,
768: p_attribute4 IN iby_creditcard.attribute4%TYPE,
761: p_desc IN iby_creditcard.description%TYPE,
762: p_active_flag IN iby_creditcard.active_flag%TYPE,
763: p_inactive_date IN iby_creditcard.inactive_date%TYPE,
764: p_attribute_category IN iby_creditcard.attribute_category%TYPE,
765: p_attribute1 IN iby_creditcard.attribute1%TYPE,
766: p_attribute2 IN iby_creditcard.attribute2%TYPE,
767: p_attribute3 IN iby_creditcard.attribute3%TYPE,
768: p_attribute4 IN iby_creditcard.attribute4%TYPE,
769: p_attribute5 IN iby_creditcard.attribute5%TYPE,
762: p_active_flag IN iby_creditcard.active_flag%TYPE,
763: p_inactive_date IN iby_creditcard.inactive_date%TYPE,
764: p_attribute_category IN iby_creditcard.attribute_category%TYPE,
765: p_attribute1 IN iby_creditcard.attribute1%TYPE,
766: p_attribute2 IN iby_creditcard.attribute2%TYPE,
767: p_attribute3 IN iby_creditcard.attribute3%TYPE,
768: p_attribute4 IN iby_creditcard.attribute4%TYPE,
769: p_attribute5 IN iby_creditcard.attribute5%TYPE,
770: p_attribute6 IN iby_creditcard.attribute6%TYPE,
763: p_inactive_date IN iby_creditcard.inactive_date%TYPE,
764: p_attribute_category IN iby_creditcard.attribute_category%TYPE,
765: p_attribute1 IN iby_creditcard.attribute1%TYPE,
766: p_attribute2 IN iby_creditcard.attribute2%TYPE,
767: p_attribute3 IN iby_creditcard.attribute3%TYPE,
768: p_attribute4 IN iby_creditcard.attribute4%TYPE,
769: p_attribute5 IN iby_creditcard.attribute5%TYPE,
770: p_attribute6 IN iby_creditcard.attribute6%TYPE,
771: p_attribute7 IN iby_creditcard.attribute7%TYPE,
764: p_attribute_category IN iby_creditcard.attribute_category%TYPE,
765: p_attribute1 IN iby_creditcard.attribute1%TYPE,
766: p_attribute2 IN iby_creditcard.attribute2%TYPE,
767: p_attribute3 IN iby_creditcard.attribute3%TYPE,
768: p_attribute4 IN iby_creditcard.attribute4%TYPE,
769: p_attribute5 IN iby_creditcard.attribute5%TYPE,
770: p_attribute6 IN iby_creditcard.attribute6%TYPE,
771: p_attribute7 IN iby_creditcard.attribute7%TYPE,
772: p_attribute8 IN iby_creditcard.attribute8%TYPE,
765: p_attribute1 IN iby_creditcard.attribute1%TYPE,
766: p_attribute2 IN iby_creditcard.attribute2%TYPE,
767: p_attribute3 IN iby_creditcard.attribute3%TYPE,
768: p_attribute4 IN iby_creditcard.attribute4%TYPE,
769: p_attribute5 IN iby_creditcard.attribute5%TYPE,
770: p_attribute6 IN iby_creditcard.attribute6%TYPE,
771: p_attribute7 IN iby_creditcard.attribute7%TYPE,
772: p_attribute8 IN iby_creditcard.attribute8%TYPE,
773: p_attribute9 IN iby_creditcard.attribute9%TYPE,
766: p_attribute2 IN iby_creditcard.attribute2%TYPE,
767: p_attribute3 IN iby_creditcard.attribute3%TYPE,
768: p_attribute4 IN iby_creditcard.attribute4%TYPE,
769: p_attribute5 IN iby_creditcard.attribute5%TYPE,
770: p_attribute6 IN iby_creditcard.attribute6%TYPE,
771: p_attribute7 IN iby_creditcard.attribute7%TYPE,
772: p_attribute8 IN iby_creditcard.attribute8%TYPE,
773: p_attribute9 IN iby_creditcard.attribute9%TYPE,
774: p_attribute10 IN iby_creditcard.attribute10%TYPE,
767: p_attribute3 IN iby_creditcard.attribute3%TYPE,
768: p_attribute4 IN iby_creditcard.attribute4%TYPE,
769: p_attribute5 IN iby_creditcard.attribute5%TYPE,
770: p_attribute6 IN iby_creditcard.attribute6%TYPE,
771: p_attribute7 IN iby_creditcard.attribute7%TYPE,
772: p_attribute8 IN iby_creditcard.attribute8%TYPE,
773: p_attribute9 IN iby_creditcard.attribute9%TYPE,
774: p_attribute10 IN iby_creditcard.attribute10%TYPE,
775: p_attribute11 IN iby_creditcard.attribute11%TYPE,
768: p_attribute4 IN iby_creditcard.attribute4%TYPE,
769: p_attribute5 IN iby_creditcard.attribute5%TYPE,
770: p_attribute6 IN iby_creditcard.attribute6%TYPE,
771: p_attribute7 IN iby_creditcard.attribute7%TYPE,
772: p_attribute8 IN iby_creditcard.attribute8%TYPE,
773: p_attribute9 IN iby_creditcard.attribute9%TYPE,
774: p_attribute10 IN iby_creditcard.attribute10%TYPE,
775: p_attribute11 IN iby_creditcard.attribute11%TYPE,
776: p_attribute12 IN iby_creditcard.attribute12%TYPE,
769: p_attribute5 IN iby_creditcard.attribute5%TYPE,
770: p_attribute6 IN iby_creditcard.attribute6%TYPE,
771: p_attribute7 IN iby_creditcard.attribute7%TYPE,
772: p_attribute8 IN iby_creditcard.attribute8%TYPE,
773: p_attribute9 IN iby_creditcard.attribute9%TYPE,
774: p_attribute10 IN iby_creditcard.attribute10%TYPE,
775: p_attribute11 IN iby_creditcard.attribute11%TYPE,
776: p_attribute12 IN iby_creditcard.attribute12%TYPE,
777: p_attribute13 IN iby_creditcard.attribute13%TYPE,
770: p_attribute6 IN iby_creditcard.attribute6%TYPE,
771: p_attribute7 IN iby_creditcard.attribute7%TYPE,
772: p_attribute8 IN iby_creditcard.attribute8%TYPE,
773: p_attribute9 IN iby_creditcard.attribute9%TYPE,
774: p_attribute10 IN iby_creditcard.attribute10%TYPE,
775: p_attribute11 IN iby_creditcard.attribute11%TYPE,
776: p_attribute12 IN iby_creditcard.attribute12%TYPE,
777: p_attribute13 IN iby_creditcard.attribute13%TYPE,
778: p_attribute14 IN iby_creditcard.attribute14%TYPE,
771: p_attribute7 IN iby_creditcard.attribute7%TYPE,
772: p_attribute8 IN iby_creditcard.attribute8%TYPE,
773: p_attribute9 IN iby_creditcard.attribute9%TYPE,
774: p_attribute10 IN iby_creditcard.attribute10%TYPE,
775: p_attribute11 IN iby_creditcard.attribute11%TYPE,
776: p_attribute12 IN iby_creditcard.attribute12%TYPE,
777: p_attribute13 IN iby_creditcard.attribute13%TYPE,
778: p_attribute14 IN iby_creditcard.attribute14%TYPE,
779: p_attribute15 IN iby_creditcard.attribute15%TYPE,
772: p_attribute8 IN iby_creditcard.attribute8%TYPE,
773: p_attribute9 IN iby_creditcard.attribute9%TYPE,
774: p_attribute10 IN iby_creditcard.attribute10%TYPE,
775: p_attribute11 IN iby_creditcard.attribute11%TYPE,
776: p_attribute12 IN iby_creditcard.attribute12%TYPE,
777: p_attribute13 IN iby_creditcard.attribute13%TYPE,
778: p_attribute14 IN iby_creditcard.attribute14%TYPE,
779: p_attribute15 IN iby_creditcard.attribute15%TYPE,
780: p_attribute16 IN iby_creditcard.attribute16%TYPE,
773: p_attribute9 IN iby_creditcard.attribute9%TYPE,
774: p_attribute10 IN iby_creditcard.attribute10%TYPE,
775: p_attribute11 IN iby_creditcard.attribute11%TYPE,
776: p_attribute12 IN iby_creditcard.attribute12%TYPE,
777: p_attribute13 IN iby_creditcard.attribute13%TYPE,
778: p_attribute14 IN iby_creditcard.attribute14%TYPE,
779: p_attribute15 IN iby_creditcard.attribute15%TYPE,
780: p_attribute16 IN iby_creditcard.attribute16%TYPE,
781: p_attribute17 IN iby_creditcard.attribute17%TYPE,
774: p_attribute10 IN iby_creditcard.attribute10%TYPE,
775: p_attribute11 IN iby_creditcard.attribute11%TYPE,
776: p_attribute12 IN iby_creditcard.attribute12%TYPE,
777: p_attribute13 IN iby_creditcard.attribute13%TYPE,
778: p_attribute14 IN iby_creditcard.attribute14%TYPE,
779: p_attribute15 IN iby_creditcard.attribute15%TYPE,
780: p_attribute16 IN iby_creditcard.attribute16%TYPE,
781: p_attribute17 IN iby_creditcard.attribute17%TYPE,
782: p_attribute18 IN iby_creditcard.attribute18%TYPE,
775: p_attribute11 IN iby_creditcard.attribute11%TYPE,
776: p_attribute12 IN iby_creditcard.attribute12%TYPE,
777: p_attribute13 IN iby_creditcard.attribute13%TYPE,
778: p_attribute14 IN iby_creditcard.attribute14%TYPE,
779: p_attribute15 IN iby_creditcard.attribute15%TYPE,
780: p_attribute16 IN iby_creditcard.attribute16%TYPE,
781: p_attribute17 IN iby_creditcard.attribute17%TYPE,
782: p_attribute18 IN iby_creditcard.attribute18%TYPE,
783: p_attribute19 IN iby_creditcard.attribute19%TYPE,
776: p_attribute12 IN iby_creditcard.attribute12%TYPE,
777: p_attribute13 IN iby_creditcard.attribute13%TYPE,
778: p_attribute14 IN iby_creditcard.attribute14%TYPE,
779: p_attribute15 IN iby_creditcard.attribute15%TYPE,
780: p_attribute16 IN iby_creditcard.attribute16%TYPE,
781: p_attribute17 IN iby_creditcard.attribute17%TYPE,
782: p_attribute18 IN iby_creditcard.attribute18%TYPE,
783: p_attribute19 IN iby_creditcard.attribute19%TYPE,
784: p_attribute20 IN iby_creditcard.attribute20%TYPE,
777: p_attribute13 IN iby_creditcard.attribute13%TYPE,
778: p_attribute14 IN iby_creditcard.attribute14%TYPE,
779: p_attribute15 IN iby_creditcard.attribute15%TYPE,
780: p_attribute16 IN iby_creditcard.attribute16%TYPE,
781: p_attribute17 IN iby_creditcard.attribute17%TYPE,
782: p_attribute18 IN iby_creditcard.attribute18%TYPE,
783: p_attribute19 IN iby_creditcard.attribute19%TYPE,
784: p_attribute20 IN iby_creditcard.attribute20%TYPE,
785: p_attribute21 IN iby_creditcard.attribute21%TYPE,
778: p_attribute14 IN iby_creditcard.attribute14%TYPE,
779: p_attribute15 IN iby_creditcard.attribute15%TYPE,
780: p_attribute16 IN iby_creditcard.attribute16%TYPE,
781: p_attribute17 IN iby_creditcard.attribute17%TYPE,
782: p_attribute18 IN iby_creditcard.attribute18%TYPE,
783: p_attribute19 IN iby_creditcard.attribute19%TYPE,
784: p_attribute20 IN iby_creditcard.attribute20%TYPE,
785: p_attribute21 IN iby_creditcard.attribute21%TYPE,
786: p_attribute22 IN iby_creditcard.attribute22%TYPE,
779: p_attribute15 IN iby_creditcard.attribute15%TYPE,
780: p_attribute16 IN iby_creditcard.attribute16%TYPE,
781: p_attribute17 IN iby_creditcard.attribute17%TYPE,
782: p_attribute18 IN iby_creditcard.attribute18%TYPE,
783: p_attribute19 IN iby_creditcard.attribute19%TYPE,
784: p_attribute20 IN iby_creditcard.attribute20%TYPE,
785: p_attribute21 IN iby_creditcard.attribute21%TYPE,
786: p_attribute22 IN iby_creditcard.attribute22%TYPE,
787: p_attribute23 IN iby_creditcard.attribute23%TYPE,
780: p_attribute16 IN iby_creditcard.attribute16%TYPE,
781: p_attribute17 IN iby_creditcard.attribute17%TYPE,
782: p_attribute18 IN iby_creditcard.attribute18%TYPE,
783: p_attribute19 IN iby_creditcard.attribute19%TYPE,
784: p_attribute20 IN iby_creditcard.attribute20%TYPE,
785: p_attribute21 IN iby_creditcard.attribute21%TYPE,
786: p_attribute22 IN iby_creditcard.attribute22%TYPE,
787: p_attribute23 IN iby_creditcard.attribute23%TYPE,
788: p_attribute24 IN iby_creditcard.attribute24%TYPE,
781: p_attribute17 IN iby_creditcard.attribute17%TYPE,
782: p_attribute18 IN iby_creditcard.attribute18%TYPE,
783: p_attribute19 IN iby_creditcard.attribute19%TYPE,
784: p_attribute20 IN iby_creditcard.attribute20%TYPE,
785: p_attribute21 IN iby_creditcard.attribute21%TYPE,
786: p_attribute22 IN iby_creditcard.attribute22%TYPE,
787: p_attribute23 IN iby_creditcard.attribute23%TYPE,
788: p_attribute24 IN iby_creditcard.attribute24%TYPE,
789: p_attribute25 IN iby_creditcard.attribute25%TYPE,
782: p_attribute18 IN iby_creditcard.attribute18%TYPE,
783: p_attribute19 IN iby_creditcard.attribute19%TYPE,
784: p_attribute20 IN iby_creditcard.attribute20%TYPE,
785: p_attribute21 IN iby_creditcard.attribute21%TYPE,
786: p_attribute22 IN iby_creditcard.attribute22%TYPE,
787: p_attribute23 IN iby_creditcard.attribute23%TYPE,
788: p_attribute24 IN iby_creditcard.attribute24%TYPE,
789: p_attribute25 IN iby_creditcard.attribute25%TYPE,
790: p_attribute26 IN iby_creditcard.attribute26%TYPE,
783: p_attribute19 IN iby_creditcard.attribute19%TYPE,
784: p_attribute20 IN iby_creditcard.attribute20%TYPE,
785: p_attribute21 IN iby_creditcard.attribute21%TYPE,
786: p_attribute22 IN iby_creditcard.attribute22%TYPE,
787: p_attribute23 IN iby_creditcard.attribute23%TYPE,
788: p_attribute24 IN iby_creditcard.attribute24%TYPE,
789: p_attribute25 IN iby_creditcard.attribute25%TYPE,
790: p_attribute26 IN iby_creditcard.attribute26%TYPE,
791: p_attribute27 IN iby_creditcard.attribute27%TYPE,
784: p_attribute20 IN iby_creditcard.attribute20%TYPE,
785: p_attribute21 IN iby_creditcard.attribute21%TYPE,
786: p_attribute22 IN iby_creditcard.attribute22%TYPE,
787: p_attribute23 IN iby_creditcard.attribute23%TYPE,
788: p_attribute24 IN iby_creditcard.attribute24%TYPE,
789: p_attribute25 IN iby_creditcard.attribute25%TYPE,
790: p_attribute26 IN iby_creditcard.attribute26%TYPE,
791: p_attribute27 IN iby_creditcard.attribute27%TYPE,
792: p_attribute28 IN iby_creditcard.attribute28%TYPE,
785: p_attribute21 IN iby_creditcard.attribute21%TYPE,
786: p_attribute22 IN iby_creditcard.attribute22%TYPE,
787: p_attribute23 IN iby_creditcard.attribute23%TYPE,
788: p_attribute24 IN iby_creditcard.attribute24%TYPE,
789: p_attribute25 IN iby_creditcard.attribute25%TYPE,
790: p_attribute26 IN iby_creditcard.attribute26%TYPE,
791: p_attribute27 IN iby_creditcard.attribute27%TYPE,
792: p_attribute28 IN iby_creditcard.attribute28%TYPE,
793: p_attribute29 IN iby_creditcard.attribute29%TYPE,
786: p_attribute22 IN iby_creditcard.attribute22%TYPE,
787: p_attribute23 IN iby_creditcard.attribute23%TYPE,
788: p_attribute24 IN iby_creditcard.attribute24%TYPE,
789: p_attribute25 IN iby_creditcard.attribute25%TYPE,
790: p_attribute26 IN iby_creditcard.attribute26%TYPE,
791: p_attribute27 IN iby_creditcard.attribute27%TYPE,
792: p_attribute28 IN iby_creditcard.attribute28%TYPE,
793: p_attribute29 IN iby_creditcard.attribute29%TYPE,
794: p_attribute30 IN iby_creditcard.attribute30%TYPE,
787: p_attribute23 IN iby_creditcard.attribute23%TYPE,
788: p_attribute24 IN iby_creditcard.attribute24%TYPE,
789: p_attribute25 IN iby_creditcard.attribute25%TYPE,
790: p_attribute26 IN iby_creditcard.attribute26%TYPE,
791: p_attribute27 IN iby_creditcard.attribute27%TYPE,
792: p_attribute28 IN iby_creditcard.attribute28%TYPE,
793: p_attribute29 IN iby_creditcard.attribute29%TYPE,
794: p_attribute30 IN iby_creditcard.attribute30%TYPE,
795: x_result_code OUT NOCOPY VARCHAR2,
788: p_attribute24 IN iby_creditcard.attribute24%TYPE,
789: p_attribute25 IN iby_creditcard.attribute25%TYPE,
790: p_attribute26 IN iby_creditcard.attribute26%TYPE,
791: p_attribute27 IN iby_creditcard.attribute27%TYPE,
792: p_attribute28 IN iby_creditcard.attribute28%TYPE,
793: p_attribute29 IN iby_creditcard.attribute29%TYPE,
794: p_attribute30 IN iby_creditcard.attribute30%TYPE,
795: x_result_code OUT NOCOPY VARCHAR2,
796: p_allow_invalid_card IN VARCHAR2,
789: p_attribute25 IN iby_creditcard.attribute25%TYPE,
790: p_attribute26 IN iby_creditcard.attribute26%TYPE,
791: p_attribute27 IN iby_creditcard.attribute27%TYPE,
792: p_attribute28 IN iby_creditcard.attribute28%TYPE,
793: p_attribute29 IN iby_creditcard.attribute29%TYPE,
794: p_attribute30 IN iby_creditcard.attribute30%TYPE,
795: x_result_code OUT NOCOPY VARCHAR2,
796: p_allow_invalid_card IN VARCHAR2,
797: p_issuer IN iby_creditcard.card_issuer_code%TYPE
790: p_attribute26 IN iby_creditcard.attribute26%TYPE,
791: p_attribute27 IN iby_creditcard.attribute27%TYPE,
792: p_attribute28 IN iby_creditcard.attribute28%TYPE,
793: p_attribute29 IN iby_creditcard.attribute29%TYPE,
794: p_attribute30 IN iby_creditcard.attribute30%TYPE,
795: x_result_code OUT NOCOPY VARCHAR2,
796: p_allow_invalid_card IN VARCHAR2,
797: p_issuer IN iby_creditcard.card_issuer_code%TYPE
798: )
793: p_attribute29 IN iby_creditcard.attribute29%TYPE,
794: p_attribute30 IN iby_creditcard.attribute30%TYPE,
795: x_result_code OUT NOCOPY VARCHAR2,
796: p_allow_invalid_card IN VARCHAR2,
797: p_issuer IN iby_creditcard.card_issuer_code%TYPE
798: )
799: IS
800: l_history_id iby_creditcard_h.card_history_change_id%TYPE;
801: l_billing_site NUMBER;
796: p_allow_invalid_card IN VARCHAR2,
797: p_issuer IN iby_creditcard.card_issuer_code%TYPE
798: )
799: IS
800: l_history_id iby_creditcard_h.card_history_change_id%TYPE;
801: l_billing_site NUMBER;
802: l_expiry_date iby_creditcard.expirydate%TYPE;
803:
804: -- variabled for CHNAME and EXPDATE encryption
798: )
799: IS
800: l_history_id iby_creditcard_h.card_history_change_id%TYPE;
801: l_billing_site NUMBER;
802: l_expiry_date iby_creditcard.expirydate%TYPE;
803:
804: -- variabled for CHNAME and EXPDATE encryption
805: l_chname_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
806: l_expdate_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
806: l_expdate_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
807: l_chname VARCHAR2(100);
808: l_masked_chname VARCHAR2(100) := NULL;
809: l_expired VARCHAR2(1) := NULL;
810: l_chname_mask_setting iby_creditcard.chname_mask_setting%TYPE;
811: l_chname_unmask_len iby_creditcard.chname_unmask_length%TYPE;
812: l_chname_unmask_data iby_creditcard.chname%TYPE;
813:
814: l_msg_count NUMBER;
807: l_chname VARCHAR2(100);
808: l_masked_chname VARCHAR2(100) := NULL;
809: l_expired VARCHAR2(1) := NULL;
810: l_chname_mask_setting iby_creditcard.chname_mask_setting%TYPE;
811: l_chname_unmask_len iby_creditcard.chname_unmask_length%TYPE;
812: l_chname_unmask_data iby_creditcard.chname%TYPE;
813:
814: l_msg_count NUMBER;
815: l_msg_data VARCHAR2(300);
808: l_masked_chname VARCHAR2(100) := NULL;
809: l_expired VARCHAR2(1) := NULL;
810: l_chname_mask_setting iby_creditcard.chname_mask_setting%TYPE;
811: l_chname_unmask_len iby_creditcard.chname_unmask_length%TYPE;
812: l_chname_unmask_data iby_creditcard.chname%TYPE;
813:
814: l_msg_count NUMBER;
815: l_msg_data VARCHAR2(300);
816: l_return_status VARCHAR2(1);
825: -- Variables to be used when an invalid card is registered
826: l_invalid_flag VARCHAR2(1) := NULL;
827: l_invalidation_reason VARCHAR2(100) := NULL;
828: l_allow_invalid_card VARCHAR2(1);
829: l_new_issuer iby_creditcard.card_issuer_code%TYPE := NULL;
830:
831: l_dbg_mod VARCHAR2(100) := 'iby.plsql.IBY_CREDITCARD_PKG' || '.' || 'Update_Card';
832:
833: BEGIN
827: l_invalidation_reason VARCHAR2(100) := NULL;
828: l_allow_invalid_card VARCHAR2(1);
829: l_new_issuer iby_creditcard.card_issuer_code%TYPE := NULL;
830:
831: l_dbg_mod VARCHAR2(100) := 'iby.plsql.IBY_CREDITCARD_PKG' || '.' || 'Update_Card';
832:
833: BEGIN
834: iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
835: l_allow_invalid_card := NVL(p_allow_invalid_card, 'N');
909: SELECT encrypted, expiry_sec_segment_id, chname_sec_segment_id,
910: chname_mask_setting, chname_unmask_length
911: INTO l_encrypted, l_expdate_sec_segment_id, l_chname_sec_segment_id,
912: l_chname_mask_setting, l_chname_unmask_len
913: FROM iby_creditcard
914: WHERE instrid = p_instr_id;
915:
916: IF(iby_debug_pub.G_LEVEL_INFO >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
917: iby_debug_pub.add('expiry_sec_segment_id:'||l_expdate_sec_segment_id,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1009:
1010: Archive_Card(FND_API.G_FALSE,p_instr_id,l_history_id);
1011:
1012:
1013: UPDATE iby_creditcard
1014: SET chname = DECODE(l_chname, FND_API.G_MISS_CHAR,NULL, NULL,chname, l_masked_chname),
1015: chname_sec_segment_id = DECODE(l_chname, FND_API.G_MISS_CHAR,NULL,
1016: NULL,chname_sec_segment_id,l_chname_sec_segment_id),
1017: chname_mask_setting = DECODE(l_chname, FND_API.G_MISS_CHAR,NULL,
1089:
1090: l_new_issuer := p_issuer;
1091: check_card_validity(p_instr_id, l_new_issuer,
1092: l_invalid_flag, l_invalidation_reason);
1093: UPDATE iby_creditcard
1094: SET invalid_flag = l_invalid_flag,
1095: invalidation_reason = l_invalidation_reason,
1096: card_issuer_code = NVL(l_new_issuer, card_issuer_code)
1097: WHERE (instrid = p_instr_id);
1102: END Update_Card;
1103:
1104: PROCEDURE check_card_validity
1105: (
1106: p_instr_id IN iby_creditcard.instrid%TYPE,
1107: x_issuer IN OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1108: x_invalid_flag OUT NOCOPY iby_creditcard.invalid_flag%TYPE,
1109: x_invalidation_reason OUT NOCOPY iby_creditcard.invalidation_reason%TYPE
1110: )
1103:
1104: PROCEDURE check_card_validity
1105: (
1106: p_instr_id IN iby_creditcard.instrid%TYPE,
1107: x_issuer IN OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1108: x_invalid_flag OUT NOCOPY iby_creditcard.invalid_flag%TYPE,
1109: x_invalidation_reason OUT NOCOPY iby_creditcard.invalidation_reason%TYPE
1110: )
1111: IS
1104: PROCEDURE check_card_validity
1105: (
1106: p_instr_id IN iby_creditcard.instrid%TYPE,
1107: x_issuer IN OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1108: x_invalid_flag OUT NOCOPY iby_creditcard.invalid_flag%TYPE,
1109: x_invalidation_reason OUT NOCOPY iby_creditcard.invalidation_reason%TYPE
1110: )
1111: IS
1112: l_dbg_mod VARCHAR2(100) := 'iby.plsql.IBY_CREDITCARD_PKG' || '.' || 'check_card_validity';
1105: (
1106: p_instr_id IN iby_creditcard.instrid%TYPE,
1107: x_issuer IN OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1108: x_invalid_flag OUT NOCOPY iby_creditcard.invalid_flag%TYPE,
1109: x_invalidation_reason OUT NOCOPY iby_creditcard.invalidation_reason%TYPE
1110: )
1111: IS
1112: l_dbg_mod VARCHAR2(100) := 'iby.plsql.IBY_CREDITCARD_PKG' || '.' || 'check_card_validity';
1113: l_expired VARCHAR2(1) := NULL;
1108: x_invalid_flag OUT NOCOPY iby_creditcard.invalid_flag%TYPE,
1109: x_invalidation_reason OUT NOCOPY iby_creditcard.invalidation_reason%TYPE
1110: )
1111: IS
1112: l_dbg_mod VARCHAR2(100) := 'iby.plsql.IBY_CREDITCARD_PKG' || '.' || 'check_card_validity';
1113: l_expired VARCHAR2(1) := NULL;
1114: l_card_issuer1 iby_creditcard.card_issuer_code%TYPE;
1115: l_card_issuer2 iby_creditcard.card_issuer_code%TYPE;
1116: l_temp_issuer iby_creditcard.card_issuer_code%TYPE;
1110: )
1111: IS
1112: l_dbg_mod VARCHAR2(100) := 'iby.plsql.IBY_CREDITCARD_PKG' || '.' || 'check_card_validity';
1113: l_expired VARCHAR2(1) := NULL;
1114: l_card_issuer1 iby_creditcard.card_issuer_code%TYPE;
1115: l_card_issuer2 iby_creditcard.card_issuer_code%TYPE;
1116: l_temp_issuer iby_creditcard.card_issuer_code%TYPE;
1117:
1118: CURSOR c_card
1111: IS
1112: l_dbg_mod VARCHAR2(100) := 'iby.plsql.IBY_CREDITCARD_PKG' || '.' || 'check_card_validity';
1113: l_expired VARCHAR2(1) := NULL;
1114: l_card_issuer1 iby_creditcard.card_issuer_code%TYPE;
1115: l_card_issuer2 iby_creditcard.card_issuer_code%TYPE;
1116: l_temp_issuer iby_creditcard.card_issuer_code%TYPE;
1117:
1118: CURSOR c_card
1119: (ci_instrid IN iby_creditcard.instrid%TYPE)
1112: l_dbg_mod VARCHAR2(100) := 'iby.plsql.IBY_CREDITCARD_PKG' || '.' || 'check_card_validity';
1113: l_expired VARCHAR2(1) := NULL;
1114: l_card_issuer1 iby_creditcard.card_issuer_code%TYPE;
1115: l_card_issuer2 iby_creditcard.card_issuer_code%TYPE;
1116: l_temp_issuer iby_creditcard.card_issuer_code%TYPE;
1117:
1118: CURSOR c_card
1119: (ci_instrid IN iby_creditcard.instrid%TYPE)
1120: IS
1115: l_card_issuer2 iby_creditcard.card_issuer_code%TYPE;
1116: l_temp_issuer iby_creditcard.card_issuer_code%TYPE;
1117:
1118: CURSOR c_card
1119: (ci_instrid IN iby_creditcard.instrid%TYPE)
1120: IS
1121: SELECT c.invalid_flag, c.invalidation_reason,
1122: c.expired_flag,
1123: c.card_issuer_code, r.card_issuer_code
1120: IS
1121: SELECT c.invalid_flag, c.invalidation_reason,
1122: c.expired_flag,
1123: c.card_issuer_code, r.card_issuer_code
1124: FROM iby_creditcard c, iby_cc_issuer_ranges r
1125: WHERE instrid = ci_instrid
1126: AND c.cc_issuer_range_id = r.cc_issuer_range_id(+);
1127:
1128: BEGIN
1186: iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
1187: END check_card_validity;
1188:
1189: FUNCTION uncipher_ccnumber
1190: (p_instrid IN iby_creditcard.instrid%TYPE,
1191: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE
1192: )
1193: RETURN iby_creditcard.ccnumber%TYPE
1194: IS
1189: FUNCTION uncipher_ccnumber
1190: (p_instrid IN iby_creditcard.instrid%TYPE,
1191: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE
1192: )
1193: RETURN iby_creditcard.ccnumber%TYPE
1194: IS
1195: l_cc_number iby_creditcard.ccnumber%TYPE;
1196: l_masked_cc_number iby_creditcard.masked_cc_number%TYPE;
1197: l_segment_cipher iby_security_segments.segment_cipher_text%TYPE;
1191: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE
1192: )
1193: RETURN iby_creditcard.ccnumber%TYPE
1194: IS
1195: l_cc_number iby_creditcard.ccnumber%TYPE;
1196: l_masked_cc_number iby_creditcard.masked_cc_number%TYPE;
1197: l_segment_cipher iby_security_segments.segment_cipher_text%TYPE;
1198: l_card_len iby_creditcard.cc_number_length%TYPE;
1199: l_encrypted iby_creditcard.encrypted%TYPE;
1192: )
1193: RETURN iby_creditcard.ccnumber%TYPE
1194: IS
1195: l_cc_number iby_creditcard.ccnumber%TYPE;
1196: l_masked_cc_number iby_creditcard.masked_cc_number%TYPE;
1197: l_segment_cipher iby_security_segments.segment_cipher_text%TYPE;
1198: l_card_len iby_creditcard.cc_number_length%TYPE;
1199: l_encrypted iby_creditcard.encrypted%TYPE;
1200: l_cc_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE;
1194: IS
1195: l_cc_number iby_creditcard.ccnumber%TYPE;
1196: l_masked_cc_number iby_creditcard.masked_cc_number%TYPE;
1197: l_segment_cipher iby_security_segments.segment_cipher_text%TYPE;
1198: l_card_len iby_creditcard.cc_number_length%TYPE;
1199: l_encrypted iby_creditcard.encrypted%TYPE;
1200: l_cc_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE;
1201: l_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE;
1202: l_mask_option iby_creditcard.card_mask_setting%TYPE;
1195: l_cc_number iby_creditcard.ccnumber%TYPE;
1196: l_masked_cc_number iby_creditcard.masked_cc_number%TYPE;
1197: l_segment_cipher iby_security_segments.segment_cipher_text%TYPE;
1198: l_card_len iby_creditcard.cc_number_length%TYPE;
1199: l_encrypted iby_creditcard.encrypted%TYPE;
1200: l_cc_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE;
1201: l_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE;
1202: l_mask_option iby_creditcard.card_mask_setting%TYPE;
1203: l_unmask_len iby_creditcard.card_unmask_length%TYPE;
1197: l_segment_cipher iby_security_segments.segment_cipher_text%TYPE;
1198: l_card_len iby_creditcard.cc_number_length%TYPE;
1199: l_encrypted iby_creditcard.encrypted%TYPE;
1200: l_cc_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE;
1201: l_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE;
1202: l_mask_option iby_creditcard.card_mask_setting%TYPE;
1203: l_unmask_len iby_creditcard.card_unmask_length%TYPE;
1204: l_unmask_digits iby_creditcard.ccnumber%TYPE;
1205:
1198: l_card_len iby_creditcard.cc_number_length%TYPE;
1199: l_encrypted iby_creditcard.encrypted%TYPE;
1200: l_cc_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE;
1201: l_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE;
1202: l_mask_option iby_creditcard.card_mask_setting%TYPE;
1203: l_unmask_len iby_creditcard.card_unmask_length%TYPE;
1204: l_unmask_digits iby_creditcard.ccnumber%TYPE;
1205:
1206: l_subkey_cipher iby_sys_security_subkeys.subkey_cipher_text%TYPE;
1199: l_encrypted iby_creditcard.encrypted%TYPE;
1200: l_cc_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE;
1201: l_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE;
1202: l_mask_option iby_creditcard.card_mask_setting%TYPE;
1203: l_unmask_len iby_creditcard.card_unmask_length%TYPE;
1204: l_unmask_digits iby_creditcard.ccnumber%TYPE;
1205:
1206: l_subkey_cipher iby_sys_security_subkeys.subkey_cipher_text%TYPE;
1207: l_keyvalid VARCHAR2(100) := NULL;
1200: l_cc_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE;
1201: l_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE;
1202: l_mask_option iby_creditcard.card_mask_setting%TYPE;
1203: l_unmask_len iby_creditcard.card_unmask_length%TYPE;
1204: l_unmask_digits iby_creditcard.ccnumber%TYPE;
1205:
1206: l_subkey_cipher iby_sys_security_subkeys.subkey_cipher_text%TYPE;
1207: l_keyvalid VARCHAR2(100) := NULL;
1208:
1205:
1206: l_subkey_cipher iby_sys_security_subkeys.subkey_cipher_text%TYPE;
1207: l_keyvalid VARCHAR2(100) := NULL;
1208:
1209: CURSOR c_instr_num(ci_instrid iby_creditcard.instrid%TYPE)
1210: IS
1211: SELECT
1212: c.ccnumber, c.masked_cc_number, seg.segment_cipher_text,
1213: NVL(c.encrypted,'N'), k.subkey_cipher_text, r.card_number_prefix,
1213: NVL(c.encrypted,'N'), k.subkey_cipher_text, r.card_number_prefix,
1214: NVL(i.digit_check_flag,'N'), c.card_mask_setting, c.card_unmask_length,
1215: DECODE(encrypted, 'Y',c.ccnumber,'A',c.ccnumber, NULL),
1216: NVL(r.card_number_length,c.cc_number_length)
1217: FROM iby_creditcard c, iby_security_segments seg,
1218: iby_sys_security_subkeys k, iby_cc_issuer_ranges r,
1219: iby_creditcard_issuers_b i
1220: WHERE (instrid = ci_instrid)
1221: AND (c.cc_num_sec_segment_id = seg.sec_segment_id(+))
1215: DECODE(encrypted, 'Y',c.ccnumber,'A',c.ccnumber, NULL),
1216: NVL(r.card_number_length,c.cc_number_length)
1217: FROM iby_creditcard c, iby_security_segments seg,
1218: iby_sys_security_subkeys k, iby_cc_issuer_ranges r,
1219: iby_creditcard_issuers_b i
1220: WHERE (instrid = ci_instrid)
1221: AND (c.cc_num_sec_segment_id = seg.sec_segment_id(+))
1222: AND (seg.sec_subkey_id = k.sec_subkey_id(+))
1223: AND (c.cc_issuer_range_id = r.cc_issuer_range_id(+))
1256: l_unmask_len, l_unmask_digits);
1257: END uncipher_ccnumber;
1258:
1259: FUNCTION uncipher_ccnumber
1260: (p_cc_number IN iby_creditcard.ccnumber%TYPE,
1261: p_segment_cipher IN iby_security_segments.segment_cipher_text%TYPE,
1262: p_encrypted IN iby_creditcard.encrypted%TYPE,
1263: p_sys_key IN iby_security_pkg.DES3_KEY_TYPE,
1264: p_subkey_cipher IN iby_sys_security_subkeys.subkey_cipher_text%TYPE,
1258:
1259: FUNCTION uncipher_ccnumber
1260: (p_cc_number IN iby_creditcard.ccnumber%TYPE,
1261: p_segment_cipher IN iby_security_segments.segment_cipher_text%TYPE,
1262: p_encrypted IN iby_creditcard.encrypted%TYPE,
1263: p_sys_key IN iby_security_pkg.DES3_KEY_TYPE,
1264: p_subkey_cipher IN iby_sys_security_subkeys.subkey_cipher_text%TYPE,
1265: p_card_len IN iby_cc_issuer_ranges.card_number_length%TYPE,
1266: p_cc_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
1263: p_sys_key IN iby_security_pkg.DES3_KEY_TYPE,
1264: p_subkey_cipher IN iby_sys_security_subkeys.subkey_cipher_text%TYPE,
1265: p_card_len IN iby_cc_issuer_ranges.card_number_length%TYPE,
1266: p_cc_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
1267: p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
1268: p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
1269: p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE,
1270: p_unmask_digits IN iby_creditcard.masked_cc_number%TYPE
1271: )
1266: p_cc_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
1267: p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
1268: p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
1269: p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE,
1270: p_unmask_digits IN iby_creditcard.masked_cc_number%TYPE
1271: )
1272: RETURN iby_creditcard.ccnumber%TYPE
1273: IS
1274: BEGIN
1268: p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
1269: p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE,
1270: p_unmask_digits IN iby_creditcard.masked_cc_number%TYPE
1271: )
1272: RETURN iby_creditcard.ccnumber%TYPE
1273: IS
1274: BEGIN
1275: RETURN iby_security_pkg.uncipher_ccnumber
1276: (p_cc_number,
1286: p_unmask_digits);
1287: END uncipher_ccnumber;
1288:
1289: FUNCTION uncipher_ccnumber_ui_wrp
1290: (i_instrid IN iby_creditcard.instrid%TYPE,
1291: i_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE)
1292: RETURN iby_creditcard.ccnumber%TYPE
1293: IS
1294: BEGIN
1288:
1289: FUNCTION uncipher_ccnumber_ui_wrp
1290: (i_instrid IN iby_creditcard.instrid%TYPE,
1291: i_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE)
1292: RETURN iby_creditcard.ccnumber%TYPE
1293: IS
1294: BEGIN
1295: RETURN uncipher_ccnumber(i_instrid, i_sys_sec_key);
1296: EXCEPTION
1302: (p_commit IN VARCHAR2,
1303: p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
1304: )
1305: IS
1306: l_cc_number iby_creditcard.ccnumber%TYPE;
1307: l_mode iby_sys_security_options.cc_encryption_mode%TYPE;
1308: -- variabled for CHNAME and EXPDATE decryption
1309: l_chname VARCHAR2(80);
1310: l_str_exp_date VARCHAR2(20);
1319: r.card_number_prefix, i.digit_check_flag, c.card_mask_setting,
1320: c.card_unmask_length, c.ccnumber unmask_digits, c.cc_num_sec_segment_id,
1321: c.chname, c.chname_sec_segment_id,
1322: c.expirydate, c.expiry_sec_segment_id
1323: FROM iby_creditcard c, iby_creditcard_issuers_b i,
1324: iby_cc_issuer_ranges r, iby_sys_security_subkeys k,
1325: iby_security_segments seg
1326: WHERE (NVL(c.encrypted,'N') <> 'N')
1327: AND (c.card_issuer_code = i.card_issuer_code(+))
1364: l_chname := c_card_rec.chname;
1365: END IF;
1366:
1367:
1368: UPDATE iby_creditcard
1369: SET
1370: ccnumber = l_cc_number,
1371: encrypted = 'N',
1372: cc_num_sec_segment_id = NULL,
1399: p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
1400: )
1401: IS
1402: l_mode iby_sys_security_options.cc_encryption_mode%TYPE;
1403: lx_compress_cc iby_creditcard.ccnumber%TYPE;
1404: lx_mask_digits iby_creditcard.masked_cc_number%TYPE;
1405:
1406: l_subkey iby_security_pkg.DES3_KEY_TYPE;
1407: l_segment_id iby_security_segments.sec_segment_id%TYPE;
1400: )
1401: IS
1402: l_mode iby_sys_security_options.cc_encryption_mode%TYPE;
1403: lx_compress_cc iby_creditcard.ccnumber%TYPE;
1404: lx_mask_digits iby_creditcard.masked_cc_number%TYPE;
1405:
1406: l_subkey iby_security_pkg.DES3_KEY_TYPE;
1407: l_segment_id iby_security_segments.sec_segment_id%TYPE;
1408: l_cc_ciphertext iby_security_segments.segment_cipher_text%TYPE;
1408: l_cc_ciphertext iby_security_segments.segment_cipher_text%TYPE;
1409:
1410: -- variabled for CHNAME and EXPDATE encryption
1411: l_chname_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
1412: l_chname_mask_setting iby_creditcard.chname_mask_setting%TYPE;
1413: l_chname_unmask_len iby_creditcard.chname_unmask_length%TYPE;
1414: l_expdate_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
1415: l_masked_chname VARCHAR2(100) := NULL;
1416: l_exp_date DATE;
1409:
1410: -- variabled for CHNAME and EXPDATE encryption
1411: l_chname_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
1412: l_chname_mask_setting iby_creditcard.chname_mask_setting%TYPE;
1413: l_chname_unmask_len iby_creditcard.chname_unmask_length%TYPE;
1414: l_expdate_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
1415: l_masked_chname VARCHAR2(100) := NULL;
1416: l_exp_date DATE;
1417:
1418: l_expired_flag VARCHAR2(1);
1419: l_encrypted VARCHAR2(1);
1420: l_enc_supl_data VARCHAR2(1);
1421:
1422: l_dbg_mod VARCHAR2(100) := 'iby.plsql.IBY_CREDITCARD_PKG' || '.' || 'Encrypt_Instruments';
1423:
1424:
1425: CURSOR c_card
1426: IS
1426: IS
1427: SELECT c.instrid, c.ccnumber, c.cc_issuer_range_id,
1428: k.subkey_cipher_text, r.card_number_prefix, i.digit_check_flag,
1429: c.card_mask_setting, c.card_unmask_length, c.chname, c.expirydate
1430: FROM iby_creditcard c, iby_creditcard_issuers_b i,
1431: iby_cc_issuer_ranges r, iby_security_segments seg,
1432: iby_sys_security_subkeys k
1433: WHERE (NVL(c.encrypted,'N') = 'N')
1434: AND (c.card_issuer_code = i.card_issuer_code(+))
1511: ELSE
1512: l_expired_flag := null;
1513: END IF;
1514:
1515: UPDATE iby_creditcard
1516: SET
1517: ccnumber = NVL(lx_mask_digits,0),
1518: cc_num_sec_segment_id = l_segment_id,
1519: encrypted = l_encrypted,
1544: (p_commit IN VARCHAR2 := FND_API.G_TRUE,
1545: p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
1546: )
1547: IS
1548: l_cc_number iby_creditcard.ccnumber%TYPE;
1549: lx_compress_cc iby_creditcard.ccnumber%TYPE;
1550: lx_mask_digits iby_creditcard.ccnumber%TYPE;
1551: lx_mask_option iby_creditcard.card_mask_setting%TYPE;
1552: lx_unmask_len iby_creditcard.card_unmask_length%TYPE;
1545: p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
1546: )
1547: IS
1548: l_cc_number iby_creditcard.ccnumber%TYPE;
1549: lx_compress_cc iby_creditcard.ccnumber%TYPE;
1550: lx_mask_digits iby_creditcard.ccnumber%TYPE;
1551: lx_mask_option iby_creditcard.card_mask_setting%TYPE;
1552: lx_unmask_len iby_creditcard.card_unmask_length%TYPE;
1553: l_cc_ciphertext iby_security_segments.segment_cipher_text%TYPE;
1546: )
1547: IS
1548: l_cc_number iby_creditcard.ccnumber%TYPE;
1549: lx_compress_cc iby_creditcard.ccnumber%TYPE;
1550: lx_mask_digits iby_creditcard.ccnumber%TYPE;
1551: lx_mask_option iby_creditcard.card_mask_setting%TYPE;
1552: lx_unmask_len iby_creditcard.card_unmask_length%TYPE;
1553: l_cc_ciphertext iby_security_segments.segment_cipher_text%TYPE;
1554: lx_segment_id iby_security_segments.sec_segment_id%TYPE;
1547: IS
1548: l_cc_number iby_creditcard.ccnumber%TYPE;
1549: lx_compress_cc iby_creditcard.ccnumber%TYPE;
1550: lx_mask_digits iby_creditcard.ccnumber%TYPE;
1551: lx_mask_option iby_creditcard.card_mask_setting%TYPE;
1552: lx_unmask_len iby_creditcard.card_unmask_length%TYPE;
1553: l_cc_ciphertext iby_security_segments.segment_cipher_text%TYPE;
1554: lx_segment_id iby_security_segments.sec_segment_id%TYPE;
1555:
1548: l_cc_number iby_creditcard.ccnumber%TYPE;
1549: lx_compress_cc iby_creditcard.ccnumber%TYPE;
1550: lx_mask_digits iby_creditcard.ccnumber%TYPE;
1551: lx_mask_option iby_creditcard.card_mask_setting%TYPE;
1552: lx_unmask_len iby_creditcard.card_unmask_length%TYPE;
1553: l_cc_ciphertext iby_security_segments.segment_cipher_text%TYPE;
1554: lx_segment_id iby_security_segments.sec_segment_id%TYPE;
1555:
1556: l_chname iby_creditcard.chname%TYPE;
1552: lx_unmask_len iby_creditcard.card_unmask_length%TYPE;
1553: l_cc_ciphertext iby_security_segments.segment_cipher_text%TYPE;
1554: lx_segment_id iby_security_segments.sec_segment_id%TYPE;
1555:
1556: l_chname iby_creditcard.chname%TYPE;
1557: l_masked_chname iby_creditcard.chname%TYPE;
1558: l_chname_seg_id iby_creditcard.chname_sec_segment_id%TYPE;
1559: l_chname_mask_setting iby_creditcard.chname_mask_setting%TYPE;
1560: l_chname_unmask_len iby_creditcard.chname_unmask_length%TYPE;
1553: l_cc_ciphertext iby_security_segments.segment_cipher_text%TYPE;
1554: lx_segment_id iby_security_segments.sec_segment_id%TYPE;
1555:
1556: l_chname iby_creditcard.chname%TYPE;
1557: l_masked_chname iby_creditcard.chname%TYPE;
1558: l_chname_seg_id iby_creditcard.chname_sec_segment_id%TYPE;
1559: l_chname_mask_setting iby_creditcard.chname_mask_setting%TYPE;
1560: l_chname_unmask_len iby_creditcard.chname_unmask_length%TYPE;
1561:
1554: lx_segment_id iby_security_segments.sec_segment_id%TYPE;
1555:
1556: l_chname iby_creditcard.chname%TYPE;
1557: l_masked_chname iby_creditcard.chname%TYPE;
1558: l_chname_seg_id iby_creditcard.chname_sec_segment_id%TYPE;
1559: l_chname_mask_setting iby_creditcard.chname_mask_setting%TYPE;
1560: l_chname_unmask_len iby_creditcard.chname_unmask_length%TYPE;
1561:
1562: CURSOR c_card
1555:
1556: l_chname iby_creditcard.chname%TYPE;
1557: l_masked_chname iby_creditcard.chname%TYPE;
1558: l_chname_seg_id iby_creditcard.chname_sec_segment_id%TYPE;
1559: l_chname_mask_setting iby_creditcard.chname_mask_setting%TYPE;
1560: l_chname_unmask_len iby_creditcard.chname_unmask_length%TYPE;
1561:
1562: CURSOR c_card
1563: (ci_mask_option iby_creditcard.card_mask_setting%TYPE,
1556: l_chname iby_creditcard.chname%TYPE;
1557: l_masked_chname iby_creditcard.chname%TYPE;
1558: l_chname_seg_id iby_creditcard.chname_sec_segment_id%TYPE;
1559: l_chname_mask_setting iby_creditcard.chname_mask_setting%TYPE;
1560: l_chname_unmask_len iby_creditcard.chname_unmask_length%TYPE;
1561:
1562: CURSOR c_card
1563: (ci_mask_option iby_creditcard.card_mask_setting%TYPE,
1564: ci_unmask_len iby_creditcard.card_unmask_length%TYPE
1559: l_chname_mask_setting iby_creditcard.chname_mask_setting%TYPE;
1560: l_chname_unmask_len iby_creditcard.chname_unmask_length%TYPE;
1561:
1562: CURSOR c_card
1563: (ci_mask_option iby_creditcard.card_mask_setting%TYPE,
1564: ci_unmask_len iby_creditcard.card_unmask_length%TYPE
1565: )
1566: IS
1567: SELECT c.instrid, c.ccnumber, seg.segment_cipher_text,
1560: l_chname_unmask_len iby_creditcard.chname_unmask_length%TYPE;
1561:
1562: CURSOR c_card
1563: (ci_mask_option iby_creditcard.card_mask_setting%TYPE,
1564: ci_unmask_len iby_creditcard.card_unmask_length%TYPE
1565: )
1566: IS
1567: SELECT c.instrid, c.ccnumber, seg.segment_cipher_text,
1568: c.encrypted, k.subkey_cipher_text,
1570: r.card_number_prefix, i.digit_check_flag, c.card_mask_setting,
1571: c.card_unmask_length, c.ccnumber unmask_digits, seg.sec_segment_id,
1572: LENGTH(c.ccnumber) len, c.chname, c.chname_sec_segment_id,
1573: c.chname_mask_setting, c.chname_unmask_length
1574: FROM iby_creditcard c, iby_creditcard_issuers_b i,
1575: iby_cc_issuer_ranges r, iby_sys_security_subkeys k,
1576: iby_security_segments seg
1577: WHERE (c.card_issuer_code = i.card_issuer_code(+))
1578: AND (c.cc_issuer_range_id = r.cc_issuer_range_id(+))
1659: l_chname_mask_setting := null;
1660: l_chname_unmask_len := null;
1661: END IF;
1662:
1663: UPDATE iby_creditcard
1664: SET
1665: ccnumber =
1666: DECODE(encrypted,'Y',NVL(lx_mask_digits,'0'),'A',NVL(lx_mask_digits,'0'),ccnumber),
1667: masked_cc_number =
1685: END IF;
1686: END Remask_Instruments;
1687:
1688: PROCEDURE Compress_CC_Number
1689: (p_card_number IN iby_creditcard.ccnumber%TYPE,
1690: p_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
1691: p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
1692: p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
1693: p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE,
1687:
1688: PROCEDURE Compress_CC_Number
1689: (p_card_number IN iby_creditcard.ccnumber%TYPE,
1690: p_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
1691: p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
1692: p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
1693: p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE,
1694: x_compress_num OUT NOCOPY iby_creditcard.ccnumber%TYPE,
1695: x_unmask_digits OUT NOCOPY iby_creditcard.masked_cc_number%TYPE
1690: p_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
1691: p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
1692: p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
1693: p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE,
1694: x_compress_num OUT NOCOPY iby_creditcard.ccnumber%TYPE,
1695: x_unmask_digits OUT NOCOPY iby_creditcard.masked_cc_number%TYPE
1696: )
1697: IS
1698: BEGIN
1691: p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
1692: p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
1693: p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE,
1694: x_compress_num OUT NOCOPY iby_creditcard.ccnumber%TYPE,
1695: x_unmask_digits OUT NOCOPY iby_creditcard.masked_cc_number%TYPE
1696: )
1697: IS
1698: BEGIN
1699: IBY_SECURITY_PKG.Compress_CC_Number(p_card_number,
1705: x_unmask_digits);
1706: END Compress_CC_Number;
1707:
1708: FUNCTION Uncompress_CC_Number
1709: (p_card_number IN iby_creditcard.ccnumber%TYPE,
1710: p_card_length IN iby_creditcard.cc_number_length%TYPE,
1711: p_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
1712: p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
1713: p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
1706: END Compress_CC_Number;
1707:
1708: FUNCTION Uncompress_CC_Number
1709: (p_card_number IN iby_creditcard.ccnumber%TYPE,
1710: p_card_length IN iby_creditcard.cc_number_length%TYPE,
1711: p_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
1712: p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
1713: p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
1714: p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE,
1708: FUNCTION Uncompress_CC_Number
1709: (p_card_number IN iby_creditcard.ccnumber%TYPE,
1710: p_card_length IN iby_creditcard.cc_number_length%TYPE,
1711: p_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
1712: p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
1713: p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
1714: p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE,
1715: p_unmask_digits IN iby_creditcard.masked_cc_number%TYPE
1716: )
1711: p_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
1712: p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
1713: p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
1714: p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE,
1715: p_unmask_digits IN iby_creditcard.masked_cc_number%TYPE
1716: )
1717: RETURN iby_creditcard.ccnumber%TYPE
1718: IS
1719: BEGIN
1713: p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
1714: p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE,
1715: p_unmask_digits IN iby_creditcard.masked_cc_number%TYPE
1716: )
1717: RETURN iby_creditcard.ccnumber%TYPE
1718: IS
1719: BEGIN
1720: RETURN IBY_SECURITY_PKG.Uncompress_CC_Number(p_card_number,
1721: p_card_length,
1726: p_unmask_digits);
1727: END Uncompress_CC_Number;
1728:
1729: FUNCTION Get_Compressed_Len
1730: (p_card_length IN iby_creditcard.cc_number_length%TYPE,
1731: p_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
1732: p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
1733: p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
1734: p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE
1728:
1729: FUNCTION Get_Compressed_Len
1730: (p_card_length IN iby_creditcard.cc_number_length%TYPE,
1731: p_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
1732: p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
1733: p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
1734: p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE
1735: )
1736: RETURN NUMBER
1743: p_unmask_len);
1744: END Get_Compressed_Len;
1745:
1746: PROCEDURE Query_Card
1747: (p_card_id IN iby_creditcard.instrid%TYPE,
1748: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
1749: x_owner_id OUT NOCOPY iby_creditcard.card_owner_id%TYPE,
1750: x_holder_name OUT NOCOPY iby_creditcard.chname%TYPE,
1751: x_billing_address_id OUT NOCOPY iby_creditcard.addressid%TYPE,
1745:
1746: PROCEDURE Query_Card
1747: (p_card_id IN iby_creditcard.instrid%TYPE,
1748: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
1749: x_owner_id OUT NOCOPY iby_creditcard.card_owner_id%TYPE,
1750: x_holder_name OUT NOCOPY iby_creditcard.chname%TYPE,
1751: x_billing_address_id OUT NOCOPY iby_creditcard.addressid%TYPE,
1752: x_billing_address1 OUT NOCOPY hz_locations.address1%TYPE,
1753: x_billing_address2 OUT NOCOPY hz_locations.address2%TYPE,
1746: PROCEDURE Query_Card
1747: (p_card_id IN iby_creditcard.instrid%TYPE,
1748: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
1749: x_owner_id OUT NOCOPY iby_creditcard.card_owner_id%TYPE,
1750: x_holder_name OUT NOCOPY iby_creditcard.chname%TYPE,
1751: x_billing_address_id OUT NOCOPY iby_creditcard.addressid%TYPE,
1752: x_billing_address1 OUT NOCOPY hz_locations.address1%TYPE,
1753: x_billing_address2 OUT NOCOPY hz_locations.address2%TYPE,
1754: x_billing_address3 OUT NOCOPY hz_locations.address3%TYPE,
1747: (p_card_id IN iby_creditcard.instrid%TYPE,
1748: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
1749: x_owner_id OUT NOCOPY iby_creditcard.card_owner_id%TYPE,
1750: x_holder_name OUT NOCOPY iby_creditcard.chname%TYPE,
1751: x_billing_address_id OUT NOCOPY iby_creditcard.addressid%TYPE,
1752: x_billing_address1 OUT NOCOPY hz_locations.address1%TYPE,
1753: x_billing_address2 OUT NOCOPY hz_locations.address2%TYPE,
1754: x_billing_address3 OUT NOCOPY hz_locations.address3%TYPE,
1755: x_billing_city OUT NOCOPY hz_locations.city%TYPE,
1756: x_billing_county OUT NOCOPY hz_locations.county%TYPE,
1757: x_billing_state OUT NOCOPY hz_locations.state%TYPE,
1758: x_billing_zip OUT NOCOPY hz_locations.postal_code%TYPE,
1759: x_billing_country OUT NOCOPY hz_locations.country%TYPE,
1760: x_card_number OUT NOCOPY iby_creditcard.ccnumber%TYPE,
1761: x_expiry_date OUT NOCOPY iby_creditcard.expirydate%TYPE,
1762: x_instr_type OUT NOCOPY iby_creditcard.instrument_type%TYPE,
1763: x_pcard_flag OUT NOCOPY iby_creditcard.purchasecard_flag%TYPE,
1764: x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
1757: x_billing_state OUT NOCOPY hz_locations.state%TYPE,
1758: x_billing_zip OUT NOCOPY hz_locations.postal_code%TYPE,
1759: x_billing_country OUT NOCOPY hz_locations.country%TYPE,
1760: x_card_number OUT NOCOPY iby_creditcard.ccnumber%TYPE,
1761: x_expiry_date OUT NOCOPY iby_creditcard.expirydate%TYPE,
1762: x_instr_type OUT NOCOPY iby_creditcard.instrument_type%TYPE,
1763: x_pcard_flag OUT NOCOPY iby_creditcard.purchasecard_flag%TYPE,
1764: x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
1765: x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1758: x_billing_zip OUT NOCOPY hz_locations.postal_code%TYPE,
1759: x_billing_country OUT NOCOPY hz_locations.country%TYPE,
1760: x_card_number OUT NOCOPY iby_creditcard.ccnumber%TYPE,
1761: x_expiry_date OUT NOCOPY iby_creditcard.expirydate%TYPE,
1762: x_instr_type OUT NOCOPY iby_creditcard.instrument_type%TYPE,
1763: x_pcard_flag OUT NOCOPY iby_creditcard.purchasecard_flag%TYPE,
1764: x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
1765: x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1766: x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
1759: x_billing_country OUT NOCOPY hz_locations.country%TYPE,
1760: x_card_number OUT NOCOPY iby_creditcard.ccnumber%TYPE,
1761: x_expiry_date OUT NOCOPY iby_creditcard.expirydate%TYPE,
1762: x_instr_type OUT NOCOPY iby_creditcard.instrument_type%TYPE,
1763: x_pcard_flag OUT NOCOPY iby_creditcard.purchasecard_flag%TYPE,
1764: x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
1765: x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1766: x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
1767: x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
1760: x_card_number OUT NOCOPY iby_creditcard.ccnumber%TYPE,
1761: x_expiry_date OUT NOCOPY iby_creditcard.expirydate%TYPE,
1762: x_instr_type OUT NOCOPY iby_creditcard.instrument_type%TYPE,
1763: x_pcard_flag OUT NOCOPY iby_creditcard.purchasecard_flag%TYPE,
1764: x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
1765: x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1766: x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
1767: x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
1768: x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
1761: x_expiry_date OUT NOCOPY iby_creditcard.expirydate%TYPE,
1762: x_instr_type OUT NOCOPY iby_creditcard.instrument_type%TYPE,
1763: x_pcard_flag OUT NOCOPY iby_creditcard.purchasecard_flag%TYPE,
1764: x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
1765: x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1766: x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
1767: x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
1768: x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
1769: x_purpose OUT NOCOPY iby_creditcard.card_purpose%TYPE,
1762: x_instr_type OUT NOCOPY iby_creditcard.instrument_type%TYPE,
1763: x_pcard_flag OUT NOCOPY iby_creditcard.purchasecard_flag%TYPE,
1764: x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
1765: x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1766: x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
1767: x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
1768: x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
1769: x_purpose OUT NOCOPY iby_creditcard.card_purpose%TYPE,
1770: x_desc OUT NOCOPY iby_creditcard.description%TYPE,
1763: x_pcard_flag OUT NOCOPY iby_creditcard.purchasecard_flag%TYPE,
1764: x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
1765: x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1766: x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
1767: x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
1768: x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
1769: x_purpose OUT NOCOPY iby_creditcard.card_purpose%TYPE,
1770: x_desc OUT NOCOPY iby_creditcard.description%TYPE,
1771: x_active_flag OUT NOCOPY iby_creditcard.active_flag%TYPE,
1764: x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
1765: x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1766: x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
1767: x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
1768: x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
1769: x_purpose OUT NOCOPY iby_creditcard.card_purpose%TYPE,
1770: x_desc OUT NOCOPY iby_creditcard.description%TYPE,
1771: x_active_flag OUT NOCOPY iby_creditcard.active_flag%TYPE,
1772: x_inactive_date OUT NOCOPY iby_creditcard.inactive_date%TYPE,
1765: x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1766: x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
1767: x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
1768: x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
1769: x_purpose OUT NOCOPY iby_creditcard.card_purpose%TYPE,
1770: x_desc OUT NOCOPY iby_creditcard.description%TYPE,
1771: x_active_flag OUT NOCOPY iby_creditcard.active_flag%TYPE,
1772: x_inactive_date OUT NOCOPY iby_creditcard.inactive_date%TYPE,
1773: x_result_code OUT NOCOPY VARCHAR2
1766: x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
1767: x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
1768: x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
1769: x_purpose OUT NOCOPY iby_creditcard.card_purpose%TYPE,
1770: x_desc OUT NOCOPY iby_creditcard.description%TYPE,
1771: x_active_flag OUT NOCOPY iby_creditcard.active_flag%TYPE,
1772: x_inactive_date OUT NOCOPY iby_creditcard.inactive_date%TYPE,
1773: x_result_code OUT NOCOPY VARCHAR2
1774: )
1767: x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
1768: x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
1769: x_purpose OUT NOCOPY iby_creditcard.card_purpose%TYPE,
1770: x_desc OUT NOCOPY iby_creditcard.description%TYPE,
1771: x_active_flag OUT NOCOPY iby_creditcard.active_flag%TYPE,
1772: x_inactive_date OUT NOCOPY iby_creditcard.inactive_date%TYPE,
1773: x_result_code OUT NOCOPY VARCHAR2
1774: )
1775: IS
1768: x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
1769: x_purpose OUT NOCOPY iby_creditcard.card_purpose%TYPE,
1770: x_desc OUT NOCOPY iby_creditcard.description%TYPE,
1771: x_active_flag OUT NOCOPY iby_creditcard.active_flag%TYPE,
1772: x_inactive_date OUT NOCOPY iby_creditcard.inactive_date%TYPE,
1773: x_result_code OUT NOCOPY VARCHAR2
1774: )
1775: IS
1776: l_ccnum_ciphertxt iby_creditcard.ccnumber%TYPE;
1772: x_inactive_date OUT NOCOPY iby_creditcard.inactive_date%TYPE,
1773: x_result_code OUT NOCOPY VARCHAR2
1774: )
1775: IS
1776: l_ccnum_ciphertxt iby_creditcard.ccnumber%TYPE;
1777: l_encrypted iby_creditcard.encrypted%TYPE;
1778: l_err_code VARCHAR2(200);
1779: l_instr_found BOOLEAN;
1780:
1773: x_result_code OUT NOCOPY VARCHAR2
1774: )
1775: IS
1776: l_ccnum_ciphertxt iby_creditcard.ccnumber%TYPE;
1777: l_encrypted iby_creditcard.encrypted%TYPE;
1778: l_err_code VARCHAR2(200);
1779: l_instr_found BOOLEAN;
1780:
1781: -- variabled for CHNAME and EXPDATE decryption
1785: l_str_exp_date VARCHAR2(20);
1786: --l_exp_date DATE;
1787: --l_encrypted_date_format VARCHAR2(20);
1788:
1789: CURSOR c_creditcard(ci_instr_id iby_creditcard.instrid%TYPE)
1790: IS
1791: SELECT
1792: c.card_owner_id, c.chname, c.addressid,
1793: l.address1, l.address2, l.address3, l.city, l.county,
1797: c.single_use_flag, c.information_only_flag, c.card_purpose,
1798: c.description, c.active_flag, c.inactive_date,
1799: c.encrypted, c.expiry_sec_segment_id,
1800: c.chname_sec_segment_id
1801: FROM iby_creditcard c, hz_party_site_uses su, hz_party_sites s,
1802: hz_locations l
1803: WHERE (instrid = ci_instr_id)
1804: AND (c.addressid = su.party_site_use_id(+))
1805: AND (su.party_site_id = s.party_site_id(+))
1871: doesnot recognise the new oapfprovince paramter added.
1872: */
1873:
1874: PROCEDURE Query_Card
1875: (p_card_id IN iby_creditcard.instrid%TYPE,
1876: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
1877: x_owner_id OUT NOCOPY iby_creditcard.card_owner_id%TYPE,
1878: x_holder_name OUT NOCOPY iby_creditcard.chname%TYPE,
1879: x_billing_address_id OUT NOCOPY iby_creditcard.addressid%TYPE,
1873:
1874: PROCEDURE Query_Card
1875: (p_card_id IN iby_creditcard.instrid%TYPE,
1876: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
1877: x_owner_id OUT NOCOPY iby_creditcard.card_owner_id%TYPE,
1878: x_holder_name OUT NOCOPY iby_creditcard.chname%TYPE,
1879: x_billing_address_id OUT NOCOPY iby_creditcard.addressid%TYPE,
1880: x_billing_address1 OUT NOCOPY hz_locations.address1%TYPE,
1881: x_billing_address2 OUT NOCOPY hz_locations.address2%TYPE,
1874: PROCEDURE Query_Card
1875: (p_card_id IN iby_creditcard.instrid%TYPE,
1876: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
1877: x_owner_id OUT NOCOPY iby_creditcard.card_owner_id%TYPE,
1878: x_holder_name OUT NOCOPY iby_creditcard.chname%TYPE,
1879: x_billing_address_id OUT NOCOPY iby_creditcard.addressid%TYPE,
1880: x_billing_address1 OUT NOCOPY hz_locations.address1%TYPE,
1881: x_billing_address2 OUT NOCOPY hz_locations.address2%TYPE,
1882: x_billing_address3 OUT NOCOPY hz_locations.address3%TYPE,
1875: (p_card_id IN iby_creditcard.instrid%TYPE,
1876: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
1877: x_owner_id OUT NOCOPY iby_creditcard.card_owner_id%TYPE,
1878: x_holder_name OUT NOCOPY iby_creditcard.chname%TYPE,
1879: x_billing_address_id OUT NOCOPY iby_creditcard.addressid%TYPE,
1880: x_billing_address1 OUT NOCOPY hz_locations.address1%TYPE,
1881: x_billing_address2 OUT NOCOPY hz_locations.address2%TYPE,
1882: x_billing_address3 OUT NOCOPY hz_locations.address3%TYPE,
1883: x_billing_city OUT NOCOPY hz_locations.city%TYPE,
1884: x_billing_county OUT NOCOPY hz_locations.county%TYPE,
1885: x_billing_state OUT NOCOPY hz_locations.state%TYPE,
1886: x_billing_zip OUT NOCOPY hz_locations.postal_code%TYPE,
1887: x_billing_country OUT NOCOPY hz_locations.country%TYPE,
1888: x_card_number OUT NOCOPY iby_creditcard.ccnumber%TYPE,
1889: x_expiry_date OUT NOCOPY iby_creditcard.expirydate%TYPE,
1890: x_instr_type OUT NOCOPY iby_creditcard.instrument_type%TYPE,
1891: x_pcard_flag OUT NOCOPY iby_creditcard.purchasecard_flag%TYPE,
1892: x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
1885: x_billing_state OUT NOCOPY hz_locations.state%TYPE,
1886: x_billing_zip OUT NOCOPY hz_locations.postal_code%TYPE,
1887: x_billing_country OUT NOCOPY hz_locations.country%TYPE,
1888: x_card_number OUT NOCOPY iby_creditcard.ccnumber%TYPE,
1889: x_expiry_date OUT NOCOPY iby_creditcard.expirydate%TYPE,
1890: x_instr_type OUT NOCOPY iby_creditcard.instrument_type%TYPE,
1891: x_pcard_flag OUT NOCOPY iby_creditcard.purchasecard_flag%TYPE,
1892: x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
1893: x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1886: x_billing_zip OUT NOCOPY hz_locations.postal_code%TYPE,
1887: x_billing_country OUT NOCOPY hz_locations.country%TYPE,
1888: x_card_number OUT NOCOPY iby_creditcard.ccnumber%TYPE,
1889: x_expiry_date OUT NOCOPY iby_creditcard.expirydate%TYPE,
1890: x_instr_type OUT NOCOPY iby_creditcard.instrument_type%TYPE,
1891: x_pcard_flag OUT NOCOPY iby_creditcard.purchasecard_flag%TYPE,
1892: x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
1893: x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1894: x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
1887: x_billing_country OUT NOCOPY hz_locations.country%TYPE,
1888: x_card_number OUT NOCOPY iby_creditcard.ccnumber%TYPE,
1889: x_expiry_date OUT NOCOPY iby_creditcard.expirydate%TYPE,
1890: x_instr_type OUT NOCOPY iby_creditcard.instrument_type%TYPE,
1891: x_pcard_flag OUT NOCOPY iby_creditcard.purchasecard_flag%TYPE,
1892: x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
1893: x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1894: x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
1895: x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
1888: x_card_number OUT NOCOPY iby_creditcard.ccnumber%TYPE,
1889: x_expiry_date OUT NOCOPY iby_creditcard.expirydate%TYPE,
1890: x_instr_type OUT NOCOPY iby_creditcard.instrument_type%TYPE,
1891: x_pcard_flag OUT NOCOPY iby_creditcard.purchasecard_flag%TYPE,
1892: x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
1893: x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1894: x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
1895: x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
1896: x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
1889: x_expiry_date OUT NOCOPY iby_creditcard.expirydate%TYPE,
1890: x_instr_type OUT NOCOPY iby_creditcard.instrument_type%TYPE,
1891: x_pcard_flag OUT NOCOPY iby_creditcard.purchasecard_flag%TYPE,
1892: x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
1893: x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1894: x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
1895: x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
1896: x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
1897: x_purpose OUT NOCOPY iby_creditcard.card_purpose%TYPE,
1890: x_instr_type OUT NOCOPY iby_creditcard.instrument_type%TYPE,
1891: x_pcard_flag OUT NOCOPY iby_creditcard.purchasecard_flag%TYPE,
1892: x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
1893: x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1894: x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
1895: x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
1896: x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
1897: x_purpose OUT NOCOPY iby_creditcard.card_purpose%TYPE,
1898: x_desc OUT NOCOPY iby_creditcard.description%TYPE,
1891: x_pcard_flag OUT NOCOPY iby_creditcard.purchasecard_flag%TYPE,
1892: x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
1893: x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1894: x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
1895: x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
1896: x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
1897: x_purpose OUT NOCOPY iby_creditcard.card_purpose%TYPE,
1898: x_desc OUT NOCOPY iby_creditcard.description%TYPE,
1899: x_active_flag OUT NOCOPY iby_creditcard.active_flag%TYPE,
1892: x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
1893: x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1894: x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
1895: x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
1896: x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
1897: x_purpose OUT NOCOPY iby_creditcard.card_purpose%TYPE,
1898: x_desc OUT NOCOPY iby_creditcard.description%TYPE,
1899: x_active_flag OUT NOCOPY iby_creditcard.active_flag%TYPE,
1900: x_inactive_date OUT NOCOPY iby_creditcard.inactive_date%TYPE,
1893: x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1894: x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
1895: x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
1896: x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
1897: x_purpose OUT NOCOPY iby_creditcard.card_purpose%TYPE,
1898: x_desc OUT NOCOPY iby_creditcard.description%TYPE,
1899: x_active_flag OUT NOCOPY iby_creditcard.active_flag%TYPE,
1900: x_inactive_date OUT NOCOPY iby_creditcard.inactive_date%TYPE,
1901: x_result_code OUT NOCOPY VARCHAR2,
1894: x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
1895: x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
1896: x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
1897: x_purpose OUT NOCOPY iby_creditcard.card_purpose%TYPE,
1898: x_desc OUT NOCOPY iby_creditcard.description%TYPE,
1899: x_active_flag OUT NOCOPY iby_creditcard.active_flag%TYPE,
1900: x_inactive_date OUT NOCOPY iby_creditcard.inactive_date%TYPE,
1901: x_result_code OUT NOCOPY VARCHAR2,
1902: x_province OUT NOCOPY hz_locations.country%TYPE
1895: x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
1896: x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
1897: x_purpose OUT NOCOPY iby_creditcard.card_purpose%TYPE,
1898: x_desc OUT NOCOPY iby_creditcard.description%TYPE,
1899: x_active_flag OUT NOCOPY iby_creditcard.active_flag%TYPE,
1900: x_inactive_date OUT NOCOPY iby_creditcard.inactive_date%TYPE,
1901: x_result_code OUT NOCOPY VARCHAR2,
1902: x_province OUT NOCOPY hz_locations.country%TYPE
1903: )
1896: x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
1897: x_purpose OUT NOCOPY iby_creditcard.card_purpose%TYPE,
1898: x_desc OUT NOCOPY iby_creditcard.description%TYPE,
1899: x_active_flag OUT NOCOPY iby_creditcard.active_flag%TYPE,
1900: x_inactive_date OUT NOCOPY iby_creditcard.inactive_date%TYPE,
1901: x_result_code OUT NOCOPY VARCHAR2,
1902: x_province OUT NOCOPY hz_locations.country%TYPE
1903: )
1904: IS
1901: x_result_code OUT NOCOPY VARCHAR2,
1902: x_province OUT NOCOPY hz_locations.country%TYPE
1903: )
1904: IS
1905: l_ccnum_ciphertxt iby_creditcard.ccnumber%TYPE;
1906: l_encrypted iby_creditcard.encrypted%TYPE;
1907: l_err_code VARCHAR2(200);
1908: l_instr_found BOOLEAN;
1909:
1902: x_province OUT NOCOPY hz_locations.country%TYPE
1903: )
1904: IS
1905: l_ccnum_ciphertxt iby_creditcard.ccnumber%TYPE;
1906: l_encrypted iby_creditcard.encrypted%TYPE;
1907: l_err_code VARCHAR2(200);
1908: l_instr_found BOOLEAN;
1909:
1910: -- variabled for CHNAME and EXPDATE decryption
1914: l_str_exp_date VARCHAR2(20);
1915: --l_exp_date DATE;
1916: --l_encrypted_date_format VARCHAR2(20);
1917:
1918: CURSOR c_creditcard(ci_instr_id iby_creditcard.instrid%TYPE)
1919: IS
1920: SELECT
1921: c.card_owner_id, c.chname, c.addressid,
1922: l.address1, l.address2, l.address3, l.city, l.county,
1926: c.single_use_flag, c.information_only_flag, c.card_purpose,
1927: c.description, c.active_flag, c.inactive_date,
1928: c.encrypted, c.expiry_sec_segment_id,
1929: c.chname_sec_segment_id , l.province
1930: FROM iby_creditcard c, hz_party_site_uses su, hz_party_sites s,
1931: hz_locations l
1932: WHERE (instrid = ci_instr_id)
1933: AND (c.addressid = su.party_site_use_id(+))
1934: AND (su.party_site_id = s.party_site_id(+))
2005: x_err_code OUT NOCOPY VARCHAR2
2006: ) IS
2007:
2008: l_mode iby_sys_security_options.instr_sec_code_encryption_mode%TYPE;
2009: l_chnameSegmentId iby_creditcard.chname_sec_segment_id%type;
2010:
2011: BEGIN
2012: -- test_debug('Inside Encrypt_Card_Info.. ');
2013: l_mode := Get_CC_Encrypt_Mode;
2073: syskey_checked VARCHAR2(1) := 'N';
2074: cnt NUMBER := 0;
2075:
2076: l_api_name CONSTANT VARCHAR2(30) := 'Mark_Expired_Cards';
2077: l_dbg_mod VARCHAR2(100) := 'IBY_CREDITCARD_PKG' || '.' ||
2078: l_api_name;
2079:
2080: CURSOR c_card
2081: IS
2079:
2080: CURSOR c_card
2081: IS
2082: SELECT instrid, expirydate, expiry_sec_segment_id
2083: FROM iby_creditcard
2084: WHERE (NVL(expired_flag,'N') <> 'Y');
2085: BEGIN
2086: iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
2087: IF (c_card%ISOPEN) THEN CLOSE c_card; END IF;
2118: l_expired_flag := 'N';
2119: END IF;
2120: END IF;
2121:
2122: UPDATE iby_creditcard
2123: SET expired_flag = l_expired_flag
2124: WHERE (instrid = c_card_rec.instrid);
2125:
2126: -- This count variable is only for logging purposes
2143: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE
2144: )
2145: IS
2146: l_api_name CONSTANT VARCHAR2(30) := 'Upgrade_Encrypted_Instruments';
2147: l_dbg_mod VARCHAR2(100) := 'IBY_CREDITCARD_PKG' || '.' ||
2148: l_api_name;
2149:
2150: l_exp_segment_id NUMBER;
2151: l_expired_flag VARCHAR2(1);
2151: l_expired_flag VARCHAR2(1);
2152:
2153: l_chname_segment_id NUMBER;
2154: l_masked_chname VARCHAR2(100);
2155: l_chname_mask_setting iby_creditcard.chname_mask_setting%TYPE;
2156: l_chname_unmask_len iby_creditcard.chname_unmask_length%TYPE;
2157:
2158: no_cc NUMBER;
2159:
2152:
2153: l_chname_segment_id NUMBER;
2154: l_masked_chname VARCHAR2(100);
2155: l_chname_mask_setting iby_creditcard.chname_mask_setting%TYPE;
2156: l_chname_unmask_len iby_creditcard.chname_unmask_length%TYPE;
2157:
2158: no_cc NUMBER;
2159:
2160: CURSOR c_card
2160: CURSOR c_card
2161: IS
2162: SELECT instrid, expirydate, expiry_sec_segment_id,
2163: chname , chname_sec_segment_id
2164: FROM iby_creditcard
2165: WHERE (NVL(encrypted,'N') = 'Y')
2166: AND ((expirydate IS NOT NULL)
2167: OR
2168: ((chname IS NOT NULL) AND (chname_sec_segment_id IS NULL)));
2205: );
2206:
2207: END IF;
2208:
2209: UPDATE iby_creditcard
2210: SET
2211: encrypted = 'A',
2212: chname = nvl(l_masked_chname, chname),
2213: chname_sec_segment_id = l_chname_segment_id,
2236: END Upgrade_Encrypted_Instruments;
2237:
2238:
2239: PROCEDURE Check_CC_Expiry
2240: (p_instrid IN IBY_CREDITCARD.instrid%TYPE,
2241: p_input_date IN DATE,
2242: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
2243: x_expired OUT NOCOPY VARCHAR2
2244: )
2247: l_expiry_date DATE;
2248: BEGIN
2249: SELECT expirydate, expiry_sec_segment_id
2250: INTO l_expiry_date, l_exp_sec_segment_id
2251: FROM iby_creditcard
2252: WHERE instrid = p_instrid;
2253:
2254: IF ((l_expiry_date IS NULL) AND (l_exp_sec_segment_id IS NULL)) THEN
2255: RETURN;
2276: p_commit IN VARCHAR2
2277: )
2278: IS
2279:
2280: lx_cc_number iby_creditcard.ccnumber%TYPE;
2281: lx_return_status VARCHAR2(1);
2282: lx_msg_count NUMBER;
2283: lx_msg_data VARCHAR2(200);
2284:
2306: object_version_number, last_update_date
2307: FROM iby_irf_risky_instr
2308: FOR UPDATE;
2309:
2310: l_dbg_mod VARCHAR2(100) := 'iby.plsql.IBY_CREDITCARD_PKG' || '.' || 'Upgrade_Risky_Instruments';
2311:
2312: BEGIN
2313: fnd_file.put_line(fnd_file.log,l_dbg_mod||': Enter..');
2314: no_cc := 0;
2393: x_batch_size IN NUMBER,
2394: x_num_workers IN NUMBER
2395: )
2396: IS
2397: l_dbg_mod VARCHAR2(100) := 'iby.plsql.IBY_CREDITCARD_PKG' || '.' || 'Purge_Sensitive_Data';
2398: req_id VARCHAR2(30);
2399: plsql_block VARCHAR2(500);
2400: x_req_id NUMBER;
2401: l_call_status BOOLEAN;
2482:
2483:
2484: END Purge_Sensitive_Data;
2485:
2486: END iby_creditcard_pkg;