[Home] [Help]
PACKAGE BODY: APPS.IBY_CREDITCARD_PKG
Source
1 PACKAGE BODY iby_creditcard_pkg AS
2 /*$Header: ibyccb.pls 120.23.12010000.19 2009/03/24 17:13:18 sugottum ship $*/
3
4
5 --
6 -- USE: Gets the credit card encryption mode setting
7 --
8 FUNCTION Get_CC_Encrypt_Mode
9 RETURN iby_sys_security_options.cc_encryption_mode%TYPE
10 IS
11 l_mode iby_sys_security_options.cc_encryption_mode%TYPE;
12 CURSOR c_encrypt_mode
13 IS
14 SELECT cc_encryption_mode
15 FROM iby_sys_security_options;
16 BEGIN
17 IF (c_encrypt_mode%ISOPEN) THEN CLOSE c_encrypt_mode; END IF;
18
19 OPEN c_encrypt_mode;
20 FETCH c_encrypt_mode INTO l_mode;
21 CLOSE c_encrypt_mode;
22
23 RETURN l_mode;
24 END Get_CC_Encrypt_Mode;
25
26 --
27 -- USE: Returns Y or N if other credit card attributes
28 -- e.g., chname and expirydate, are encrypted.
29 --
30 FUNCTION Other_CC_Attribs_Encrypted
31 RETURN VARCHAR2
32 IS
33 BEGIN
34 RETURN 'N';
35 END Other_CC_Attribs_Encrypted;
36
37 FUNCTION Get_Billing_Site
38 (p_party_site_id IN hz_party_sites.party_site_id%TYPE,
39 p_party_id IN hz_parties.party_id%TYPE
40 )
41 RETURN hz_party_site_uses.party_site_use_id%TYPE
42 IS
43 l_site_use_id hz_party_site_uses.party_site_use_id%TYPE;
44 l_site_id hz_party_sites.party_site_id%TYPE;
45 l_site_use_rec HZ_PARTY_SITE_V2PUB.Party_Site_Use_rec_type;
46 lx_return_status VARCHAR2(1);
47 lx_msg_count NUMBER;
48 lx_msg_data VARCHAR2(2000);
49
50 CURSOR c_site_use
51 (ci_party_site IN hz_party_sites.party_site_id%TYPE,
52 ci_party_id IN hz_parties.party_id%TYPE
53 )
54 IS
55 SELECT u.party_site_use_id
56 FROM hz_party_site_uses u, hz_party_sites s
57 WHERE (u.party_site_id = ci_party_site)
58 -- because of complexities in the payer model
59 -- do not require the site address to be owned by the card owner
60 --AND (s.party_id = NVL(ci_party_id,party_id))
61 AND (u.party_site_id = s.party_site_id)
62 AND (u.site_use_type = G_CC_BILLING_SITE_USE)
63 AND ( NVL(u.begin_date,SYSDATE-10) < SYSDATE)
64 AND ( NVL(u.end_date,SYSDATE+10) > SYSDATE);
65
66 CURSOR c_site
67 (ci_party_site hz_party_sites.party_site_id%TYPE,
68 ci_party_id IN hz_parties.party_id%TYPE
69 )
70 IS
71 SELECT party_site_id
72 FROM hz_party_sites
73 WHERE (party_site_id = ci_party_site)
74 -- because of complexities in the payer model
75 -- do not require the site address to be owned by the card owner
76 --AND (party_id = NVL(ci_party_id,party_id))
77 AND ( NVL(start_date_active,SYSDATE-10) < SYSDATE)
78 AND ( NVL(end_date_active,SYSDATE+10) > SYSDATE);
79 BEGIN
80 IF c_site_use%ISOPEN THEN CLOSE c_site_use; END IF;
81 IF c_site%ISOPEN THEN CLOSE c_site; END IF;
82
83 OPEN c_site_use(p_party_site_id,NULL);
84 FETCH c_site_use INTO l_site_use_id;
85 CLOSE c_site_use;
86
87 -- create a site use if it does not exist
88 IF (l_site_use_id IS NULL) THEN
89 OPEN c_site(p_party_site_id,p_party_id);
90 FETCH c_site INTO l_site_id;
91 CLOSE c_site;
92
93 IF (NOT l_site_id IS NULL) THEN
94 l_site_use_rec.party_site_id := l_site_id;
95 l_site_use_rec.application_id := 673;
96 l_site_use_rec.site_use_type := G_CC_BILLING_SITE_USE;
97 l_site_use_rec.created_by_module := 'TCA_V2_API';
98
99 HZ_PARTY_SITE_V2PUB.Create_Party_Site_Use
100 (FND_API.G_FALSE,l_site_use_rec,l_site_use_id,
101 lx_return_status,lx_msg_count,lx_msg_data
102 );
103 END IF;
104 END IF;
105
106 RETURN l_site_use_id;
107 END Get_Billing_Site;
108
109 --
110 -- Validates the given system key; an exception is thrown
111 -- if the key is invalid and there is encrypted card
112 -- number data in the instruments table
113 --
114 PROCEDURE check_key( p_sec_key IN VARCHAR2 )
115 IS
116 l_encrypted_count NUMBER := 0;
117 l_keyvalid VARCHAR2(100) := NULL;
118 BEGIN
119
120 iby_security_pkg.validate_sys_key(p_sec_key,l_keyvalid);
121
122 IF (NOT l_keyvalid IS NULL) THEN
123 SELECT count(instrid)
124 INTO l_encrypted_count
125 FROM iby_creditcard
126 WHERE (NVL(encrypted,'N')='Y');
127
128 IF (l_encrypted_count>0) THEN
129 raise_application_error(-20000,l_keyvalid, FALSE);
130 END IF;
131 END IF;
132
133 END check_key;
134
135 -- USE: Saves card information to the credit card history table
136 --
137 PROCEDURE Archive_Card
138 (p_commit IN VARCHAR2,
139 p_instr_id IN iby_creditcard.instrid%TYPE,
140 x_history_id OUT NOCOPY iby_creditcard_h.card_history_change_id%TYPE
141 )
142 IS
143 BEGIN
144
145 /*
146 * Fix for bug 5256903 by rameshsh:
147 *
148 * The active_flag column is nullable in IBY_CREDITCARD
149 * but nor in IBY_CREDITCARD_H.
150 *
151 * If active_flag is not set for a particular credit card
152 * in IBY_CREDITCARD, default the value to 'Y', otherwise
153 * this method will throw a 'cannot insert NULL exception ..'
154 */
155 SELECT iby_creditcard_h_s.NEXTVAL INTO x_history_id FROM dual;
156 INSERT INTO iby_creditcard_h
157 (card_history_change_id, instrid, expirydate, expiry_sec_segment_id,
158 addressid,
159 description, chname, chname_sec_segment_id, finame, security_group_id,
160 encrypted,
161 masked_cc_number, card_owner_id, instrument_type, purchasecard_flag,
162 purchasecard_subtype, card_issuer_code, single_use_flag,
163 information_only_flag, card_purpose, active_flag, inactive_date,
164 attribute_category, attribute1, attribute2, attribute3, attribute4,
165 attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
166 attribute11, attribute12, attribute13, attribute14, attribute15,
167 attribute16, attribute17, attribute18, attribute19, attribute20,
168 attribute21, attribute22, attribute23, attribute24, attribute25,
169 attribute26, attribute27, attribute28, attribute29, attribute30,
170 request_id, program_application_id, program_id, program_update_date,
171 created_by, creation_date, last_updated_by, last_update_date,
172 last_update_login, object_version_number
173 )
174 SELECT x_history_id, instrid, expirydate, expiry_sec_segment_id,
175 addressid,
176 description, chname, chname_sec_segment_id, finame, security_group_id,
177 encrypted,
178 masked_cc_number, card_owner_id, instrument_type, purchasecard_flag,
179 purchasecard_subtype, card_issuer_code, single_use_flag,
180 information_only_flag, card_purpose, NVL(active_flag, 'Y'), inactive_date,
181 attribute_category, attribute1, attribute2, attribute3, attribute4,
182 attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
183 attribute11, attribute12, attribute13, attribute14, attribute15,
184 attribute16, attribute17, attribute18, attribute19, attribute20,
185 attribute21, attribute22, attribute23, attribute24, attribute25,
186 attribute26, attribute27, attribute28, attribute29, attribute30,
187 request_id, program_application_id, program_id, program_update_date,
188 fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
189 fnd_global.login_id, 1
190 FROM iby_creditcard
191 WHERE (instrid = p_instr_id);
192
193 IF FND_API.To_Boolean(p_commit) THEN
194 COMMIT;
195 END IF;
196 END Archive_Card;
197
198 --
199 -- USE: Gets credit card mask settings
200 --
201 PROCEDURE Get_Mask_Settings
202 (x_mask_setting OUT NOCOPY iby_sys_security_options.credit_card_mask_setting%TYPE,
203 x_unmask_len OUT NOCOPY iby_sys_security_options.credit_card_unmask_len%TYPE
204 )
205 IS
206
207 CURSOR c_mask_setting
208 IS
209 SELECT credit_card_mask_setting, credit_card_unmask_len
210 FROM iby_sys_security_options;
211
212 BEGIN
213 x_mask_setting := iby_security_pkg.G_MASK_PREFIX;
214
215 IF (c_mask_setting%ISOPEN) THEN CLOSE c_mask_setting; END IF;
216
217 OPEN c_mask_setting;
218 FETCH c_mask_setting INTO x_mask_setting, x_unmask_len;
219 CLOSE c_mask_setting;
220
221 IF (x_mask_setting IS NULL) THEN
222 x_mask_setting := iby_security_pkg.G_MASK_PREFIX;
223 END IF;
224 IF (x_unmask_len IS NULL) THEN
225 x_unmask_len := G_DEF_UNMASK_LENGTH;
226 END IF;
227 END Get_Mask_Settings;
228
229 FUNCTION Mask_Card_Number
230 (p_cc_number IN iby_creditcard.ccnumber%TYPE,
231 p_mask_option IN iby_creditcard.card_mask_setting%TYPE,
232 p_unmask_len IN iby_creditcard.card_unmask_length%TYPE
233 )
234 RETURN iby_creditcard.masked_cc_number%TYPE
235 IS
236 BEGIN
237 RETURN iby_security_pkg.Mask_Data
238 (p_cc_number,p_mask_option,p_unmask_len,G_MASK_CHARACTER);
239 END Mask_Card_Number;
240
241 --
242 -- Return: The masked card number, usable for display purposes
243 --
244 PROCEDURE Mask_Card_Number
245 (p_cc_number IN iby_creditcard.ccnumber%TYPE,
246 x_masked_number OUT NOCOPY iby_creditcard.masked_cc_number%TYPE,
247 x_mask_setting OUT NOCOPY iby_sys_security_options.credit_card_mask_setting%TYPE,
248 x_unmask_len OUT NOCOPY iby_sys_security_options.credit_card_unmask_len%TYPE
249 )
250 IS
251 BEGIN
252 Get_Mask_Settings(x_mask_setting,x_unmask_len);
253 x_masked_number :=
254 Mask_Card_Number(p_cc_number,x_mask_setting,x_unmask_len);
255 END Mask_Card_Number;
256
257 FUNCTION Mask_Card_Number(p_cc_number IN iby_creditcard.ccnumber%TYPE)
258 RETURN iby_creditcard.masked_cc_number%TYPE
259 IS
260 lx_mask_option iby_creditcard.card_mask_setting%TYPE;
261 lx_mask_number iby_creditcard.masked_cc_number%TYPE;
262 lx_unmask_len iby_sys_security_options.credit_card_unmask_len%TYPE;
263 BEGIN
264 Mask_Card_Number(p_cc_number,lx_mask_number,lx_mask_option,lx_unmask_len);
265 RETURN lx_mask_number;
266 END Mask_Card_Number;
267
268
269 PROCEDURE Create_Card
270 (p_commit IN VARCHAR2,
271 p_owner_id IN iby_creditcard.card_owner_id%TYPE,
272 p_holder_name IN iby_creditcard.chname%TYPE,
273 p_billing_address_id IN iby_creditcard.addressid%TYPE,
274 p_address_type IN VARCHAR2,
275 p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
276 p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
277 p_card_number IN iby_creditcard.ccnumber%TYPE,
278 p_expiry_date IN iby_creditcard.expirydate%TYPE,
279 p_instr_type IN iby_creditcard.instrument_type%TYPE,
280 p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
281 p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
282 p_issuer IN iby_creditcard.card_issuer_code%TYPE,
283 p_fi_name IN iby_creditcard.finame%TYPE,
284 p_single_use IN iby_creditcard.single_use_flag%TYPE,
285 p_info_only IN iby_creditcard.information_only_flag%TYPE,
286 p_purpose IN iby_creditcard.card_purpose%TYPE,
287 p_desc IN iby_creditcard.description%TYPE,
288 p_active_flag IN iby_creditcard.active_flag%TYPE,
289 p_inactive_date IN iby_creditcard.inactive_date%TYPE,
290 p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
291 p_attribute_category IN iby_creditcard.attribute_category%TYPE,
292 p_attribute1 IN iby_creditcard.attribute1%TYPE,
293 p_attribute2 IN iby_creditcard.attribute2%TYPE,
294 p_attribute3 IN iby_creditcard.attribute3%TYPE,
295 p_attribute4 IN iby_creditcard.attribute4%TYPE,
296 p_attribute5 IN iby_creditcard.attribute5%TYPE,
297 p_attribute6 IN iby_creditcard.attribute6%TYPE,
298 p_attribute7 IN iby_creditcard.attribute7%TYPE,
299 p_attribute8 IN iby_creditcard.attribute8%TYPE,
300 p_attribute9 IN iby_creditcard.attribute9%TYPE,
301 p_attribute10 IN iby_creditcard.attribute10%TYPE,
302 p_attribute11 IN iby_creditcard.attribute11%TYPE,
303 p_attribute12 IN iby_creditcard.attribute12%TYPE,
304 p_attribute13 IN iby_creditcard.attribute13%TYPE,
305 p_attribute14 IN iby_creditcard.attribute14%TYPE,
306 p_attribute15 IN iby_creditcard.attribute15%TYPE,
307 p_attribute16 IN iby_creditcard.attribute16%TYPE,
308 p_attribute17 IN iby_creditcard.attribute17%TYPE,
309 p_attribute18 IN iby_creditcard.attribute18%TYPE,
310 p_attribute19 IN iby_creditcard.attribute19%TYPE,
311 p_attribute20 IN iby_creditcard.attribute20%TYPE,
312 p_attribute21 IN iby_creditcard.attribute21%TYPE,
313 p_attribute22 IN iby_creditcard.attribute22%TYPE,
314 p_attribute23 IN iby_creditcard.attribute23%TYPE,
315 p_attribute24 IN iby_creditcard.attribute24%TYPE,
316 p_attribute25 IN iby_creditcard.attribute25%TYPE,
317 p_attribute26 IN iby_creditcard.attribute26%TYPE,
318 p_attribute27 IN iby_creditcard.attribute27%TYPE,
319 p_attribute28 IN iby_creditcard.attribute28%TYPE,
320 p_attribute29 IN iby_creditcard.attribute29%TYPE,
321 p_attribute30 IN iby_creditcard.attribute30%TYPE,
322 x_result_code OUT NOCOPY VARCHAR2,
323 x_instr_id OUT NOCOPY iby_creditcard.instrid%TYPE
324 )
325 IS
326
327 lx_checksum_valid BOOLEAN := FALSE;
328 lx_return_status VARCHAR2(1);
329 lx_msg_count NUMBER;
330 lx_msg_data VARCHAR2(200);
331 lx_card_issuer iby_creditcard.card_issuer_code%TYPE;
332 lx_issuer_range iby_creditcard.cc_issuer_range_id%TYPE;
333 lx_card_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE;
334 lx_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE;
335 l_card_len iby_creditcard.cc_number_length%TYPE;
336
337 lx_cc_number iby_creditcard.ccnumber%TYPE;
338 lx_cc_compressed iby_creditcard.ccnumber%TYPE;
339 lx_unmasked_digits iby_creditcard.ccnumber%TYPE;
340 l_cc_ciphertext iby_security_segments.segment_cipher_text%TYPE;
341 l_encrypted iby_creditcard.encrypted%TYPE;
342 lx_masked_number iby_creditcard.masked_cc_number%TYPE;
343 lx_mask_option iby_creditcard.card_mask_setting%TYPE;
344 lx_unmask_len iby_creditcard.card_unmask_length%TYPE;
345 l_cc_hash1 iby_creditcard.cc_number_hash1%TYPE;
346 l_cc_hash2 iby_creditcard.cc_number_hash2%TYPE;
347 lx_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
348
349 l_expiry_date iby_creditcard.expirydate%TYPE;
350 l_billing_site hz_party_site_uses.party_site_use_id%TYPE;
351
352 -- variabled for CHNAME and EXPDATE encryption
353 l_chname_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
354 l_expdate_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
355 l_masked_chname VARCHAR2(100) := NULL;
356 -- l_masked_expdate VARCHAR2(20) := NULL;
357 -- l_encrypted_date_format VARCHAR2(20) := NULL;
358 -- l_chname_length NUMBER;
359 l_exp_date DATE;
360
361 l_subkey iby_sys_security_subkeys.subkey_cipher_text%TYPE;
362 l_subkey_id iby_sys_security_subkeys.sec_subkey_id%TYPE;
363 l_keyvalid VARCHAR2(100) := NULL;
364
365
366 CURSOR c_card
367 (ci_owner_id IN hz_parties.party_id%TYPE,
368 ci_hash1 IN iby_creditcard.cc_number_hash1%TYPE,
369 ci_hash2 IN iby_creditcard.cc_number_hash1%TYPE
370 )
371 IS
372 SELECT instrid
373 FROM iby_creditcard
374 WHERE (cc_number_hash1 = ci_hash1)
375 AND (cc_number_hash2 = ci_hash2)
376 AND ( (NVL(card_owner_id,ci_owner_id) = NVL(ci_owner_id,card_owner_id))
377 OR (card_owner_id IS NULL AND ci_owner_id IS NULL)
378 )
379 AND (NVL(single_use_flag,'N')='N');
380 BEGIN
381
382 IF (c_card%ISOPEN) THEN CLOSE c_card; END IF;
383
384 IF (p_card_number IS NULL ) THEN
385 x_result_code := G_RC_INVALID_CCNUMBER;
386 RETURN;
387 END IF;
388
389 -- expiration date may be null
390 IF (NOT p_expiry_date IS NULL) THEN
391 l_expiry_date := LAST_DAY(p_expiry_date);
392 IF (TRUNC(l_expiry_date,'DD') < TRUNC(SYSDATE,'DD')) THEN
393 x_result_code := G_RC_INVALID_CCEXPIRY;
394 RETURN;
395 END IF;
396 END IF;
397
398 IF (NOT p_pcard_type IS NULL) THEN
399 IF (iby_utility_pvt.check_lookup_val(p_pcard_type,G_LKUP_PCARD_TYPE))
400 THEN
401 x_result_code := G_RC_INVALID_PCARD_TYPE;
402 RETURN;
403 END IF;
404 END IF;
405
406 IF ( (NVL(p_instr_type,' ') <> G_LKUP_INSTR_TYPE_CC)
407 AND (NVL(p_instr_type,' ') <> G_LKUP_INSTR_TYPE_DC)
408 AND (NVL(p_instr_type,' ') <> G_LKUP_INSTR_TYPE_PC ))
409 THEN
410 x_result_code := G_RC_INVALID_INSTR_TYPE;
411 RETURN;
412 END IF;
413
414 iby_cc_validate.StripCC
415 (1.0, FND_API.G_FALSE, p_card_number,
416 lx_return_status, lx_msg_count, lx_msg_data, lx_cc_number
417 );
418
419 IF( (lx_cc_number IS NULL) OR
420 (lx_return_status IS NULL OR
421 lx_return_status <> FND_API.G_RET_STS_SUCCESS) )
422 THEN
423 x_result_code := G_RC_INVALID_CCNUMBER;
424 RETURN;
425 END IF;
426
427 iby_cc_validate.Get_CC_Issuer_Range
428 (lx_cc_number,lx_card_issuer,lx_issuer_range,lx_card_prefix,lx_digit_check);
429 --Bug# 8346420
430 --When the upstream product passes the information for card issuer then
431 --the below block would be executed. In case of "UNKNOWN" card types, the
432 --value is coming as empty, in that case we trim and compare. By doing that
433 --the below condition wouldn't be satisfied and the card will be registered.
434 IF(length(TRIM(p_issuer)) > 0) THEN
435 IF ( (NOT p_issuer IS NULL) AND (p_issuer <> lx_card_issuer) ) THEN
436 x_result_code := G_RC_INVALID_CARD_ISSUER;
437 RETURN;
438 END IF;
439 END IF;
440 IF (lx_digit_check = 'Y') THEN
441 IF ( MOD(iby_cc_validate.CheckCCDigits(lx_cc_number),10) <> 0 ) THEN
442 x_result_code := G_RC_INVALID_CCNUMBER;
443 RETURN;
444 END IF;
445 END IF;
446
447 -- necessary to decompress secured card instruments, but only if
448 -- no known issuer range matches
449 IF (lx_issuer_range IS NULL) THEN
450 l_card_len := LENGTH(lx_cc_number);
451 END IF;
452
453 Mask_Card_Number(lx_cc_number,lx_masked_number,lx_mask_option,lx_unmask_len);
454 l_cc_hash1 := iby_security_pkg.get_hash(lx_cc_number,'F');
455 -- get hash value for a salted version of the card number
456 l_cc_hash2 := iby_security_pkg.get_hash(lx_cc_number,'T');
457
458 -- Bug 5153265 start
459 -- If Site use id is already provied then no need to call get_billing address
460 IF (p_address_type = G_PARTY_SITE_USE_ID) AND (NOT (p_billing_address_id IS NULL)) THEN
461 l_billing_site := p_billing_address_id;
462 ELSE
463 IF (p_billing_address_id = FND_API.G_MISS_NUM ) THEN
464 l_billing_site := FND_API.G_MISS_NUM;
465 ELSIF (NOT (p_billing_address_id IS NULL)) THEN
466 l_billing_site := Get_Billing_Site(p_billing_address_id,p_owner_id);
467 IF (l_billing_site IS NULL) THEN
468 x_result_code := G_RC_INVALID_ADDRESS;
469 RETURN;
470 END IF;
471 END IF;
472 END IF;
473 -- Bug 5153265 end
474
475 IF (NOT ( (p_billing_country IS NULL)
476 OR (p_billing_country = FND_API.G_MISS_CHAR) )
477 )
478 THEN
479 IF (NOT iby_utility_pvt.Validate_Territory(p_billing_country)) THEN
480 x_result_code := G_RC_INVALID_ADDRESS;
481 RETURN;
482 END IF;
483 END IF;
484
485 IF (NOT p_owner_id IS NULL) THEN
486 IF (NOT iby_utility_pvt.validate_party_id(p_owner_id)) THEN
487 x_result_code := G_RC_INVALID_PARTY;
488 RETURN;
489 END IF;
490 END IF;
491
492 OPEN c_card(p_owner_id,l_cc_hash1,l_cc_hash2);
493 FETCH c_card INTO x_instr_id;
494 CLOSE c_card;
495
496 IF (NOT x_instr_id IS NULL) THEN RETURN; END IF;
497
498 IF (NOT p_sys_sec_key IS NULL) THEN
499 -- check the system key
500 iby_security_pkg.validate_sys_key(p_sys_sec_key,l_keyvalid);
501
502 IF (NOT l_keyvalid IS NULL) THEN
503 x_result_code := 'INVALID_SEC_KEY';
504 RETURN;
505 END IF;
506 l_encrypted := 'Y';
507
508 Compress_CC_Number
509 (lx_cc_number,lx_card_prefix,lx_digit_check,lx_mask_option,
510 lx_unmask_len,lx_cc_compressed,lx_unmasked_digits);
511
512 IF (NOT lx_cc_compressed IS NULL) THEN
513 l_cc_ciphertext :=
514 HEXTORAW(IBY_SECURITY_PKG.Encode_Number(lx_cc_compressed,TRUE));
515 IBY_SECURITY_PKG.Create_Segment
516 (FND_API.G_FALSE,l_cc_ciphertext,iby_security_pkg.G_ENCODING_NUMERIC,
517 p_sys_sec_key,lx_sec_segment_id);
518 END IF;
519
520 lx_cc_number := NVL(lx_unmasked_digits,'0');
521
522 --now need to encrypt the other card holder data
523 --i.e, CHNAME and EXPDATE for now.
524 IF(Other_CC_Attribs_Encrypted = 'Y') THEN
525 l_chname_sec_segment_id :=
526 IBY_SECURITY_PKG.encrypt_field_vals(p_holder_name,
527 p_sys_sec_key,
528 null,
529 'N'
530 );
531 l_expdate_sec_segment_id :=
532 IBY_SECURITY_PKG.encrypt_date_field(l_expiry_date,
533 p_sys_sec_key,
534 null,
535 'N'
536 );
537 l_masked_chname :=
538 IBY_SECURITY_PKG.Mask_Data(p_holder_name,
539 IBY_SECURITY_PKG.G_MASK_ALL,
540 0,
541 'X'
542 );
543
544
545 -- The actuall date column will hold a NULL value in this
546 -- case.
547 l_expiry_date := NULL;
548 ELSE
549 l_masked_chname := p_holder_name;
550 END IF;
551 ELSE
552 l_encrypted := 'N';
553 -- we use the same CHNAME column for storing the masked value
554 -- when encryption is enabled. So, make this value point to
555 -- the clear text when encryption is not enabled.
556 -- Also the expiry date column will hold the actual exp date
557 -- in this case.
558 l_masked_chname := p_holder_name;
559 END IF;
560 -- l_chname_length := NVL(LENGTH(p_holder_name), 0);
561
562 SELECT iby_instr_s.NEXTVAL INTO x_instr_id FROM DUAL;
563
564 INSERT INTO iby_creditcard
565 (instrid, ccnumber, masked_cc_number,
566 card_mask_setting, card_unmask_length, cc_number_hash1, cc_number_hash2,
567 expirydate, expiry_sec_segment_id,
568 card_owner_id, chname, chname_sec_segment_id,
569 addressid, billing_addr_postal_code, bill_addr_territory_code,
570 instrument_type, purchasecard_flag, purchasecard_subtype,
571 card_issuer_code, cc_issuer_range_id, cc_number_length,
572 description, finame, encrypted, cc_num_sec_segment_id,
573 single_use_flag, information_only_flag, card_purpose,
574 active_flag, inactive_date,
575 last_update_date, last_updated_by, creation_date,
576 created_by, object_version_number,
577 attribute_category,
578 attribute1,attribute2, attribute3,attribute4,attribute5,
579 attribute6,attribute7, attribute8,attribute9,attribute10,
580 attribute11,attribute12, attribute13,attribute14,attribute15,
581 attribute16,attribute17, attribute18,attribute19,attribute20,
582 attribute21,attribute22, attribute23,attribute24,attribute25,
583 attribute26,attribute27, attribute28,attribute29,attribute30
584 -- salt_version bug 6326624
585 )
586 VALUES
587 (x_instr_id, lx_cc_number, lx_masked_number,
588 lx_mask_option, lx_unmask_len, l_cc_hash1, l_cc_hash2,
589 l_expiry_date, l_expdate_sec_segment_id,
590 p_owner_id, l_masked_chname, l_chname_sec_segment_id,
591 l_billing_site, p_billing_zip, p_billing_country,
592 p_instr_type, NVL(p_pcard_flag,'N'), p_pcard_type,
593 lx_card_issuer, lx_issuer_range, l_card_len,
594 p_desc, p_fi_name, l_encrypted, lx_sec_segment_id,
595 NVL(p_single_use,'N'), NVL(p_info_only,'N'), p_purpose,
596 NVL(p_active_flag,'Y'), p_inactive_date,
597 sysdate, fnd_global.user_id, sysdate,
598 fnd_global.user_id, 1,
599 p_attribute_category,
600 p_attribute1,p_attribute2,p_attribute3,p_attribute4,p_attribute5,
601 p_attribute6,p_attribute7,p_attribute8,p_attribute9,p_attribute10,
602 p_attribute11,p_attribute12,p_attribute13,p_attribute14,p_attribute15,
603 p_attribute16,p_attribute17, p_attribute18,p_attribute19,p_attribute20,
604 p_attribute21,p_attribute22, p_attribute23,p_attribute24,p_attribute25,
605 p_attribute26,p_attribute27, p_attribute28,p_attribute29,p_attribute30
606 -- iby_security_pkg.get_salt_version bug 6326624
607 );
608
609 IF FND_API.To_Boolean(p_commit) THEN
610 COMMIT;
611 END IF;
612 END Create_Card;
613
614 PROCEDURE Update_Card
615 (p_commit IN VARCHAR2,
616 p_instr_id IN iby_creditcard.instrid%TYPE,
617 p_owner_id IN iby_creditcard.card_owner_id%TYPE,
618 p_holder_name IN iby_creditcard.chname%TYPE,
619 p_billing_address_id IN iby_creditcard.addressid%TYPE,
620 p_address_type IN VARCHAR2,
621 p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
622 p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
623 p_expiry_date IN iby_creditcard.expirydate%TYPE,
624 p_instr_type IN iby_creditcard.instrument_type%TYPE,
625 p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
626 p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
627 p_fi_name IN iby_creditcard.finame%TYPE,
628 p_single_use IN iby_creditcard.single_use_flag%TYPE,
629 p_info_only IN iby_creditcard.information_only_flag%TYPE,
630 p_purpose IN iby_creditcard.card_purpose%TYPE,
631 p_desc IN iby_creditcard.description%TYPE,
632 p_active_flag IN iby_creditcard.active_flag%TYPE,
633 p_inactive_date IN iby_creditcard.inactive_date%TYPE,
634 p_attribute_category IN iby_creditcard.attribute_category%TYPE,
635 p_attribute1 IN iby_creditcard.attribute1%TYPE,
636 p_attribute2 IN iby_creditcard.attribute2%TYPE,
637 p_attribute3 IN iby_creditcard.attribute3%TYPE,
638 p_attribute4 IN iby_creditcard.attribute4%TYPE,
639 p_attribute5 IN iby_creditcard.attribute5%TYPE,
640 p_attribute6 IN iby_creditcard.attribute6%TYPE,
641 p_attribute7 IN iby_creditcard.attribute7%TYPE,
642 p_attribute8 IN iby_creditcard.attribute8%TYPE,
643 p_attribute9 IN iby_creditcard.attribute9%TYPE,
644 p_attribute10 IN iby_creditcard.attribute10%TYPE,
645 p_attribute11 IN iby_creditcard.attribute11%TYPE,
646 p_attribute12 IN iby_creditcard.attribute12%TYPE,
647 p_attribute13 IN iby_creditcard.attribute13%TYPE,
648 p_attribute14 IN iby_creditcard.attribute14%TYPE,
649 p_attribute15 IN iby_creditcard.attribute15%TYPE,
650 p_attribute16 IN iby_creditcard.attribute16%TYPE,
651 p_attribute17 IN iby_creditcard.attribute17%TYPE,
652 p_attribute18 IN iby_creditcard.attribute18%TYPE,
653 p_attribute19 IN iby_creditcard.attribute19%TYPE,
654 p_attribute20 IN iby_creditcard.attribute20%TYPE,
655 p_attribute21 IN iby_creditcard.attribute21%TYPE,
656 p_attribute22 IN iby_creditcard.attribute22%TYPE,
657 p_attribute23 IN iby_creditcard.attribute23%TYPE,
658 p_attribute24 IN iby_creditcard.attribute24%TYPE,
659 p_attribute25 IN iby_creditcard.attribute25%TYPE,
660 p_attribute26 IN iby_creditcard.attribute26%TYPE,
661 p_attribute27 IN iby_creditcard.attribute27%TYPE,
662 p_attribute28 IN iby_creditcard.attribute28%TYPE,
663 p_attribute29 IN iby_creditcard.attribute29%TYPE,
664 p_attribute30 IN iby_creditcard.attribute30%TYPE,
665 x_result_code OUT NOCOPY VARCHAR2
666 )
667 IS
668 l_history_id iby_creditcard_h.card_history_change_id%TYPE;
669 l_billing_site NUMBER;
670 l_expiry_date iby_creditcard.expirydate%TYPE;
671
672 -- variabled for CHNAME and EXPDATE encryption
673 l_chname_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
674 l_expdate_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
675 l_masked_chname VARCHAR2(100) := NULL;
676
677 l_msg_count NUMBER;
678 l_msg_data VARCHAR2(300);
679 l_return_status VARCHAR2(1);
680 l_resp_rec IBY_INSTRREG_PUB.SecureCardInfoResp_rec_type;
681 l_sec_code VARCHAR2(10);
682
683 -- l_encrypted_date_format VARCHAR2(20) := NULL;
684 l_exp_date DATE;
685 l_encrypted VARCHAR2(1);
686 l_keyvalid VARCHAR2(100) := NULL;
687
688 BEGIN
689
690 IF (NOT p_pcard_type IS NULL) THEN
691 IF (iby_utility_pvt.check_lookup_val(p_pcard_type,G_LKUP_PCARD_TYPE)) THEN
692 x_result_code := G_RC_INVALID_PCARD_TYPE;
693 RETURN;
694 END IF;
695 END IF;
696
697 IF (NOT p_instr_type IS NULL) THEN
698 IF ( (p_instr_type <> G_LKUP_INSTR_TYPE_CC)
699 AND (p_instr_type <> G_LKUP_INSTR_TYPE_DC) )
700 THEN
701 x_result_code := G_RC_INVALID_INSTR_TYPE;
702 RETURN;
703 END IF;
704 END IF;
705 IF (NOT p_owner_id IS NULL) THEN
706 IF (NOT iby_utility_pvt.validate_party_id(p_owner_id)) THEN
707 x_result_code := G_RC_INVALID_PARTY;
708 RETURN;
709 END IF;
710 END IF;
711 -- Bug 5153265 start
712 -- If Site use id is already provied then no need to call get_billing address
713 IF (p_address_type = G_PARTY_SITE_USE_ID) AND (NOT (p_billing_address_id IS NULL)) THEN
714 l_billing_site := p_billing_address_id;
715 ELSE
716 IF (p_billing_address_id = FND_API.G_MISS_NUM ) THEN
717 l_billing_site := FND_API.G_MISS_NUM;
718 ELSIF (NOT (p_billing_address_id IS NULL)) THEN
719 l_billing_site := Get_Billing_Site(p_billing_address_id,p_owner_id);
720 IF (l_billing_site IS NULL) THEN
721 x_result_code := G_RC_INVALID_ADDRESS;
722 RETURN;
723 END IF;
724 END IF;
725 END IF;
726 -- Bug 5153265 end
727
728 IF (NOT ( (p_billing_country IS NULL)
729 OR (p_billing_country = FND_API.G_MISS_CHAR) )
730 )
731 THEN
732 IF (NOT iby_utility_pvt.Validate_Territory(p_billing_country)) THEN
733 x_result_code := G_RC_INVALID_ADDRESS;
734 RETURN;
735 END IF;
736 END IF;
737
738
739 -- Bug 5479785 (Panaraya)
740 -- Added check for expiry date on update
741 -- expiration date may be null
742 IF (NOT p_expiry_date IS NULL) THEN
743 l_expiry_date := LAST_DAY(p_expiry_date);
744 IF (TRUNC(l_expiry_date,'DD') < TRUNC(SYSDATE,'DD')) THEN
745 x_result_code := G_RC_INVALID_CCEXPIRY;
746 RETURN;
747 END IF;
748 END IF;
749
750 -- Get the encrypted flag value of the existing record
751 SELECT encrypted INTO l_encrypted FROM iby_creditcard
752 WHERE instrid = p_instr_id;
753
754 -- Need to encrypt the sensitive data only if the record was
755 -- previously encrypted and of course the encryption mode
756 -- shouldn't be NONE
757 IF (Get_CC_Encrypt_Mode <> IBY_SECURITY_PKG.G_ENCRYPT_MODE_NONE
758 AND l_encrypted = 'Y'
759 AND Other_CC_Attribs_Encrypted = 'Y') THEN
760
761 l_encrypted := 'Y';
762
763 IBY_INSTRREG_PUB.SecureCardInfo(l_expiry_date,
764 p_holder_name,
765 l_return_status,
766 l_msg_count,
767 l_msg_data,
768 l_resp_rec
769 );
770
771 l_chname_sec_segment_id := l_resp_rec.ChnameSegmentId;
772 l_expdate_sec_segment_id := l_resp_rec.ExpiryDateSegmentId;
773
774 l_masked_chname :=
775 IBY_SECURITY_PKG.Mask_Data(p_holder_name,
776 IBY_SECURITY_PKG.G_MASK_ALL,
777 0,
778 'X'
779 );
780
781 l_expiry_date := NULL;
782
783 ELSE
784 l_encrypted := 'N';
785 -- we use the same CHNAME column for storing the masked value
786 -- when encryption is enabled. So, make this value point to
787 -- the clear text when encryption is not enabled.
788 -- Also the expiry date column will hold the actual exp date
789 -- in this case.
790 l_masked_chname := p_holder_name;
791
792 END IF;
793
794 Archive_Card(FND_API.G_FALSE,p_instr_id,l_history_id);
795
796
797 UPDATE iby_creditcard
798 SET chname = DECODE(p_holder_name, FND_API.G_MISS_CHAR,NULL, NULL,chname, l_masked_chname),
799 chname_sec_segment_id = l_chname_sec_segment_id,
800 card_owner_id = NVL(card_owner_id,p_owner_id),
801 addressid = DECODE(l_billing_site, FND_API.G_MISS_NUM,NULL,
802 NULL,addressid, l_billing_site),
803 bill_addr_territory_code =
804 DECODE(p_billing_country, FND_API.G_MISS_CHAR,NULL,
805 NULL,bill_addr_territory_code, p_billing_country),
806 billing_addr_postal_code =
807 DECODE(p_billing_zip, FND_API.G_MISS_CHAR,NULL,
808 NULL,billing_addr_postal_code, p_billing_zip),
809 -- expirydate = NVL(p_expiry_date, expirydate),
810 expirydate = DECODE(p_expiry_date, NULL, expirydate, l_expiry_date),
811 expiry_sec_segment_id = DECODE(p_expiry_date, NULL, expiry_sec_segment_id,
812 l_expdate_sec_segment_id),
813 encrypted = l_encrypted,
814 instrument_type = NVL(p_instr_type, instrument_type),
815 purchasecard_flag = NVL(p_pcard_flag, purchasecard_flag),
816 purchasecard_subtype =
817 DECODE(p_pcard_type, FND_API.G_MISS_CHAR,NULL,
818 NULL,purchasecard_subtype, p_pcard_type),
819 finame = DECODE(p_fi_name, FND_API.G_MISS_CHAR,NULL, NULL,finame, p_fi_name),
820 single_use_flag = NVL(p_single_use, single_use_flag),
821 information_only_flag = NVL(p_info_only, information_only_flag),
822 card_purpose = DECODE(p_purpose, FND_API.G_MISS_CHAR,NULL, NULL,card_purpose, p_purpose),
823 description = DECODE(p_desc, FND_API.G_MISS_CHAR,NULL, NULL,description, p_desc),
824 active_flag = NVL(p_active_flag, active_flag),
825 inactive_date = DECODE(p_inactive_date, FND_API.G_MISS_DATE,NULL,
826 NULL,inactive_date, p_inactive_date),
827 object_version_number = object_version_number + 1,
828 last_update_date = sysdate,
829 last_updated_by = fnd_global.user_id,
830 last_update_login = fnd_global.login_id,
831 attribute_category = p_Attribute_category,
832 attribute1 = p_attribute1,
833 attribute2 = p_attribute2,
834 attribute3 = p_attribute3,
835 attribute4 = p_attribute4,
836 attribute5 = p_attribute5,
837 attribute6 = p_attribute6,
838 attribute7 = p_attribute7,
839 attribute8 = p_attribute8,
840 attribute9 = p_attribute9,
841 attribute10 = p_attribute10,
842 attribute11 = p_attribute11,
843 attribute12 = p_attribute12,
844 attribute13 = p_attribute13,
845 attribute14 = p_attribute14,
846 attribute15 = p_attribute15,
847 attribute16 = p_attribute16,
848 attribute17 = p_attribute17,
849 attribute18 = p_attribute18,
850 attribute19 = p_attribute19,
851 attribute20 = p_attribute20,
852 attribute21 = p_attribute21,
853 attribute22 = p_attribute22,
854 attribute23 = p_attribute23,
855 attribute24 = p_attribute24,
856 attribute25 = p_attribute25,
857 attribute26 = p_attribute26,
858 attribute27 = p_attribute27,
859 attribute28 = p_attribute28,
860 attribute29 = p_attribute29,
861 attribute30 = p_attribute30
862 WHERE (instrid = p_instr_id);
863
864 IF (SQL%NOTFOUND) THEN x_result_code := G_RC_INVALID_CARD_ID; END IF;
865
866 IF FND_API.To_Boolean(p_commit) THEN
867 COMMIT;
868 END IF;
869 END Update_Card;
870
871 FUNCTION uncipher_ccnumber
872 (p_instrid IN iby_creditcard.instrid%TYPE,
873 p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE
874 )
875 RETURN iby_creditcard.ccnumber%TYPE
876 IS
877 l_cc_number iby_creditcard.ccnumber%TYPE;
878 l_segment_cipher iby_security_segments.segment_cipher_text%TYPE;
879 l_card_len iby_creditcard.cc_number_length%TYPE;
880 l_encrypted iby_creditcard.encrypted%TYPE;
881 l_cc_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE;
882 l_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE;
883 l_mask_option iby_creditcard.card_mask_setting%TYPE;
884 l_unmask_len iby_creditcard.card_unmask_length%TYPE;
885 l_unmask_digits iby_creditcard.ccnumber%TYPE;
886
887 l_subkey_cipher iby_sys_security_subkeys.subkey_cipher_text%TYPE;
888 l_keyvalid VARCHAR2(100) := NULL;
889
890 CURSOR c_instr_num(ci_instrid iby_creditcard.instrid%TYPE)
891 IS
892 SELECT
893 c.ccnumber, seg.segment_cipher_text,
894 NVL(c.encrypted,'N'), k.subkey_cipher_text, r.card_number_prefix,
895 NVL(i.digit_check_flag,'N'), c.card_mask_setting, c.card_unmask_length,
896 DECODE(encrypted, 'Y',c.ccnumber, NULL),
897 NVL(r.card_number_length,c.cc_number_length)
898 FROM iby_creditcard c, iby_security_segments seg,
899 iby_sys_security_subkeys k, iby_cc_issuer_ranges r,
900 iby_creditcard_issuers_b i
901 WHERE (instrid = ci_instrid)
902 AND (c.cc_num_sec_segment_id = seg.sec_segment_id(+))
903 AND (seg.sec_subkey_id = k.sec_subkey_id(+))
904 AND (c.cc_issuer_range_id = r.cc_issuer_range_id(+))
905 AND (r.card_issuer_code = i.card_issuer_code(+));
906 BEGIN
907
908 IF (c_instr_num%ISOPEN) THEN CLOSE c_instr_num; END IF;
909
910 OPEN c_instr_num(p_instrid);
911 FETCH c_instr_num INTO l_cc_number, l_segment_cipher,
912 l_encrypted, l_subkey_cipher,
913 l_cc_prefix, l_digit_check, l_mask_option, l_unmask_len,
914 l_unmask_digits, l_card_len;
915 CLOSE c_instr_num;
916
917 IF (l_cc_number IS NULL) THEN
918 raise_application_error(-20000, 'IBY_20512#', FALSE);
919 END IF;
920
921 IF (l_encrypted = 'Y') THEN
922 iby_security_pkg.validate_sys_key(p_sys_sec_key,l_keyvalid);
923 IF (NOT l_keyvalid IS NULL) THEN
924 raise_application_error(-20000,'IBY_10008#INSTRID='||p_instrid, FALSE);
925 END IF;
926 END IF;
927
928 RETURN uncipher_ccnumber(l_cc_number, l_segment_cipher, l_encrypted,
929 p_sys_sec_key, l_subkey_cipher, l_card_len,
930 l_cc_prefix, l_digit_check, l_mask_option,
931 l_unmask_len, l_unmask_digits);
932 END uncipher_ccnumber;
933
934 FUNCTION uncipher_ccnumber
935 (p_cc_number IN iby_creditcard.ccnumber%TYPE,
936 p_segment_cipher IN iby_security_segments.segment_cipher_text%TYPE,
937 p_encrypted IN iby_creditcard.encrypted%TYPE,
938 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE,
939 p_subkey_cipher IN iby_sys_security_subkeys.subkey_cipher_text%TYPE,
940 p_card_len IN iby_cc_issuer_ranges.card_number_length%TYPE,
941 p_cc_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
942 p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
943 p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
944 p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE,
945 p_unmask_digits IN iby_creditcard.masked_cc_number%TYPE
946 )
947 RETURN iby_creditcard.ccnumber%TYPE
948 IS
949 l_sub_key iby_security_pkg.DES3_KEY_TYPE;
950 l_segment_raw iby_security_segments.segment_cipher_text%TYPE;
951 l_cc_number iby_creditcard.ccnumber%TYPE;
952 l_compress_len NUMBER;
953 BEGIN
954 l_cc_number := '';
955 l_compress_len := Get_Compressed_Len
956 (p_card_len,p_cc_prefix,p_digit_check,p_mask_setting,
957 p_unmask_len);
958
959 IF (p_encrypted = 'Y') THEN
960 IF (l_compress_len > 0) THEN
961 -- uncipher the subkey
962 l_sub_key :=
963 iby_security_pkg.get_sys_subkey(p_sys_key,p_subkey_cipher);
964
965 l_segment_raw :=
966 dbms_obfuscation_toolkit.des3decrypt
967 ( input => p_segment_cipher , key => l_sub_key,
968 which => dbms_obfuscation_toolkit.ThreeKeyMode
969 );
970
971 l_cc_number := iby_security_pkg.Decode_Number
972 (l_segment_raw,l_compress_len,TRUE);
973 END IF;
974
975 -- finally, uncompress the card number
976 RETURN Uncompress_CC_Number
977 (l_cc_number,p_card_len,p_cc_prefix,p_digit_check,p_mask_setting,
978 p_unmask_len,p_unmask_digits);
979 ELSE
980 RETURN p_cc_number;
981 END IF;
982 END uncipher_ccnumber;
983
984 FUNCTION uncipher_ccnumber_ui_wrp
985 (i_instrid IN iby_creditcard.instrid%TYPE,
986 i_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE)
987 RETURN iby_creditcard.ccnumber%TYPE
988 IS
989 BEGIN
990 RETURN uncipher_ccnumber(i_instrid, i_sys_sec_key);
991 EXCEPTION
992 WHEN OTHERS THEN
993 RETURN null;
994 END uncipher_ccnumber_ui_wrp;
995
996 PROCEDURE Decrypt_Instruments
997 (p_commit IN VARCHAR2,
998 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
999 )
1000 IS
1001 l_cc_number iby_creditcard.ccnumber%TYPE;
1002
1003 -- variabled for CHNAME and EXPDATE decryption
1004 l_chname VARCHAR2(80);
1005 l_str_exp_date VARCHAR2(20);
1006 l_exp_date DATE;
1007 -- l_encrypted_date_format VARCHAR2(20);
1008
1009 CURSOR c_card
1010 IS
1011 SELECT c.instrid, c.ccnumber, seg.segment_cipher_text,
1012 c.encrypted, k.subkey_cipher_text,
1013 NVL(r.card_number_length,c.cc_number_length) card_len,
1014 r.card_number_prefix, i.digit_check_flag, c.card_mask_setting,
1015 c.card_unmask_length, c.ccnumber unmask_digits, c.cc_num_sec_segment_id,
1016 c.chname, c.chname_sec_segment_id,
1017 c.expirydate, c.expiry_sec_segment_id
1018 FROM iby_creditcard c, iby_creditcard_issuers_b i,
1019 iby_cc_issuer_ranges r, iby_sys_security_subkeys k,
1020 iby_security_segments seg
1021 WHERE (NVL(c.encrypted,'N') = 'Y')
1022 AND (c.card_issuer_code = i.card_issuer_code(+))
1023 AND (c.cc_issuer_range_id = r.cc_issuer_range_id(+))
1024 AND (c.cc_num_sec_segment_id = seg.sec_segment_id(+))
1025 AND (seg.sec_subkey_id = k.sec_subkey_id(+));
1026 BEGIN
1027
1028 FOR c_card_rec IN c_card LOOP
1029
1030 l_cc_number :=
1031 uncipher_ccnumber
1032 (c_card_rec.ccnumber, c_card_rec.segment_cipher_text,
1033 c_card_rec.encrypted, p_sys_key, c_card_rec.subkey_cipher_text,
1034 c_card_rec.card_len, c_card_rec.card_number_prefix,
1035 c_card_rec.digit_check_flag, c_card_rec.card_mask_setting,
1036 c_card_rec.card_unmask_length, c_card_rec.unmask_digits
1037 );
1038
1039 IF (c_card_rec.expiry_sec_segment_id IS NOT NULL) THEN
1040 l_exp_date := IBY_SECURITY_PKG.decrypt_date_field
1041 (c_card_rec.expiry_sec_segment_id,
1042 p_sys_key
1043 );
1044 ELSE
1045 -- The exp date wasn't encrypted
1046 l_exp_date := c_card_rec.expirydate;
1047 END IF;
1048
1049 IF(c_card_rec.chname_sec_segment_id IS NOT NULL) THEN
1050 l_chname := IBY_SECURITY_PKG.decrypt_field_vals
1051 (c_card_rec.chname_sec_segment_id,
1052 p_sys_key
1053 );
1054 ELSE
1055 -- CHNAME wasn't encrypted
1056 l_chname := c_card_rec.chname;
1057 END IF;
1058
1059
1060 UPDATE iby_creditcard
1061 SET
1062 ccnumber = l_cc_number,
1063 encrypted = 'N',
1064 cc_num_sec_segment_id = NULL,
1065 expirydate = l_exp_date,
1066 expiry_sec_segment_id = NULL,
1067 chname = l_chname,
1068 chname_sec_segment_id = NULL,
1069 object_version_number = object_version_number + 1,
1070 last_update_date = sysdate,
1071 last_updated_by = fnd_global.user_id,
1072 last_update_login = fnd_global.login_id
1073 WHERE (instrid = c_card_rec.instrid);
1074
1075 DELETE iby_security_segments
1076 WHERE (sec_segment_id IN (c_card_rec.cc_num_sec_segment_id,
1077 c_card_rec.chname_sec_segment_id,
1078 c_card_rec.expiry_sec_segment_id)
1079 );
1080 END LOOP;
1081
1082 IF FND_API.to_Boolean( p_commit ) THEN
1083 COMMIT;
1084 END IF;
1085 END Decrypt_Instruments;
1086
1087 PROCEDURE Encrypt_Instruments
1088 (p_commit IN VARCHAR2,
1089 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
1090 )
1091 IS
1092 l_mode iby_sys_security_options.cc_encryption_mode%TYPE;
1093 lx_compress_cc iby_creditcard.ccnumber%TYPE;
1094 lx_mask_digits iby_creditcard.masked_cc_number%TYPE;
1095
1096 l_subkey iby_security_pkg.DES3_KEY_TYPE;
1097 l_segment_id iby_security_segments.sec_segment_id%TYPE;
1098 l_cc_ciphertext iby_security_segments.segment_cipher_text%TYPE;
1099
1100 -- variabled for CHNAME and EXPDATE encryption
1101 l_chname_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
1102 l_expdate_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
1103 l_masked_chname VARCHAR2(100) := NULL;
1104 l_exp_date DATE;
1105
1106
1107 CURSOR c_card
1108 IS
1109 SELECT c.instrid, c.ccnumber, c.cc_issuer_range_id,
1110 k.subkey_cipher_text, r.card_number_prefix, i.digit_check_flag,
1111 c.card_mask_setting, c.card_unmask_length, c.chname, c.expirydate
1112 FROM iby_creditcard c, iby_creditcard_issuers_b i,
1113 iby_cc_issuer_ranges r, iby_security_segments seg,
1114 iby_sys_security_subkeys k
1115 WHERE (NVL(c.encrypted,'N') = 'N')
1116 AND (c.card_issuer_code = i.card_issuer_code(+))
1117 AND (c.cc_issuer_range_id = r.cc_issuer_range_id(+))
1118 AND (c.cc_num_sec_segment_id = seg.sec_segment_id(+))
1119 AND (seg.sec_subkey_id = k.sec_subkey_id(+));
1120 BEGIN
1121
1122 l_mode := Get_CC_Encrypt_Mode();
1123 IF (l_mode = iby_security_pkg.G_ENCRYPT_MODE_NONE) THEN
1124 RETURN;
1125 END IF;
1126
1127 check_key(p_sys_key);
1128
1129 FOR c_card_rec IN c_card LOOP
1130 Compress_CC_Number
1131 (c_card_rec.ccnumber,
1132 c_card_rec.card_number_prefix, c_card_rec.digit_check_flag,
1133 c_card_rec.card_mask_setting, c_card_rec.card_unmask_length,
1134 lx_compress_cc, lx_mask_digits);
1135
1136 IF (NVL(LENGTH(lx_compress_cc),0) > 0) THEN
1137 l_cc_ciphertext :=
1138 HEXTORAW(IBY_SECURITY_PKG.Encode_Number(lx_compress_cc,TRUE));
1139
1140 IBY_SECURITY_PKG.Create_Segment
1141 (FND_API.G_FALSE,l_cc_ciphertext,iby_security_pkg.G_ENCODING_NUMERIC,
1142 p_sys_key,l_segment_id);
1143 ELSE
1144 l_segment_id := -1;
1145 END IF;
1146
1147
1148 --now need to encrypt the other card holder data
1149 --i.e, CHNAME and EXPDATE for now.
1150 IF(Other_CC_Attribs_Encrypted = 'Y') THEN
1151 l_chname_sec_segment_id :=
1152 IBY_SECURITY_PKG.encrypt_field_vals(c_card_rec.chname,
1153 p_sys_key,
1154 null,
1155 'N'
1156 );
1157 l_expdate_sec_segment_id :=
1158 IBY_SECURITY_PKG.encrypt_date_field(c_card_rec.expirydate,
1159 p_sys_key,
1160 null,
1161 'N'
1162 );
1163
1164 l_masked_chname :=
1165 IBY_SECURITY_PKG.Mask_Data(c_card_rec.chname,
1166 IBY_SECURITY_PKG.G_MASK_ALL,
1167 0,
1168 'X'
1169 );
1170 ELSE
1171 l_masked_chname := c_card_rec.chname;
1172 l_exp_date := c_card_rec.expirydate;
1173 END IF;
1174
1175
1176 UPDATE iby_creditcard
1177 SET
1178 ccnumber = NVL(lx_mask_digits,0),
1179 cc_num_sec_segment_id = l_segment_id,
1180 encrypted = 'Y',
1181 chname = l_masked_chname,
1182 chname_sec_segment_id = l_chname_sec_segment_id,
1183 expiry_sec_segment_id = l_expdate_sec_segment_id,
1184 --encrypted_date_format = l_encrypted_date_format,
1185 expirydate = l_exp_date,
1186 object_version_number = object_version_number + 1,
1187 last_update_date = sysdate,
1188 last_updated_by = fnd_global.user_id,
1189 last_update_login = fnd_global.login_id
1190 WHERE (instrid = c_card_rec.instrid);
1191 END LOOP;
1192
1193 IF FND_API.to_Boolean( p_commit ) THEN
1194 COMMIT;
1195 END IF;
1196
1197 END Encrypt_Instruments;
1198
1199 PROCEDURE Remask_Instruments
1200 (p_commit IN VARCHAR2 := FND_API.G_TRUE,
1201 p_sys_key IN iby_security_pkg.DES3_KEY_TYPE
1202 )
1203 IS
1204 l_cc_number iby_creditcard.ccnumber%TYPE;
1205 lx_compress_cc iby_creditcard.ccnumber%TYPE;
1206 lx_mask_digits iby_creditcard.ccnumber%TYPE;
1207 lx_mask_option iby_creditcard.card_mask_setting%TYPE;
1208 lx_unmask_len iby_creditcard.card_unmask_length%TYPE;
1209 l_cc_ciphertext iby_security_segments.segment_cipher_text%TYPE;
1210 lx_segment_id iby_security_segments.sec_segment_id%TYPE;
1211
1212 CURSOR c_card
1213 (ci_mask_option iby_creditcard.card_mask_setting%TYPE,
1214 ci_unmask_len iby_creditcard.card_unmask_length%TYPE
1215 )
1216 IS
1217 SELECT c.instrid, c.ccnumber, seg.segment_cipher_text,
1218 c.encrypted, k.subkey_cipher_text,
1219 NVL(r.card_number_length,c.cc_number_length) card_len,
1220 r.card_number_prefix, i.digit_check_flag, c.card_mask_setting,
1221 c.card_unmask_length, c.ccnumber unmask_digits, seg.sec_segment_id,
1222 LENGTH(c.ccnumber) len
1223 FROM iby_creditcard c, iby_creditcard_issuers_b i,
1224 iby_cc_issuer_ranges r, iby_sys_security_subkeys k,
1225 iby_security_segments seg
1226 WHERE (c.card_issuer_code = i.card_issuer_code(+))
1227 AND (c.cc_issuer_range_id = r.cc_issuer_range_id(+))
1228 AND (c.cc_num_sec_segment_id = seg.sec_segment_id(+))
1229 AND (seg.sec_subkey_id = k.sec_subkey_id(+))
1230 AND ( (NVL(card_unmask_length,-1) <> ci_unmask_len) OR
1231 (NVL(card_mask_setting,' ') <> ci_mask_option)
1232 );
1233 BEGIN
1234
1235 IF (c_card%ISOPEN) THEN CLOSE c_card; END IF;
1236
1237 check_key(p_sys_key);
1238
1239 Get_Mask_Settings(lx_mask_option,lx_unmask_len);
1240
1241 FOR c_card_rec IN c_card(lx_mask_option,lx_unmask_len) LOOP
1242 l_cc_number :=
1243 uncipher_ccnumber
1244 (c_card_rec.ccnumber, c_card_rec.segment_cipher_text,
1245 c_card_rec.encrypted, p_sys_key,
1246 c_card_rec.subkey_cipher_text, c_card_rec.card_len,
1247 c_card_rec.card_number_prefix, c_card_rec.digit_check_flag,
1248 c_card_rec.card_mask_setting, c_card_rec.card_unmask_length,
1249 c_card_rec.unmask_digits
1250 );
1251
1252 lx_segment_id := c_card_rec.sec_segment_id;
1253
1254 IF (c_card_rec.encrypted = 'Y') THEN
1255
1256 Compress_CC_Number
1257 (l_cc_number,c_card_rec.card_number_prefix,c_card_rec.digit_check_flag,
1258 lx_mask_option,lx_unmask_len,lx_compress_cc,lx_mask_digits);
1259
1260 --
1261 -- masking options may have resulted in no hidden digits; only
1262 -- update if there still exist card digits that are not exposed through
1263 -- the mask or card issuer range
1264 --
1265 IF (NVL(LENGTH(lx_compress_cc),0) > 0) THEN
1266 l_cc_ciphertext :=
1267 HEXTORAW(IBY_SECURITY_PKG.Encode_Number(lx_compress_cc,TRUE));
1268 IF (lx_segment_id IS NULL) THEN
1269 IBY_SECURITY_PKG.Create_Segment
1270 (FND_API.G_FALSE,l_cc_ciphertext,
1271 iby_security_pkg.G_ENCODING_NUMERIC,
1272 p_sys_key,lx_segment_id);
1273 ELSE
1274 IBY_SECURITY_PKG.Update_Segment
1275 (FND_API.G_FALSE,lx_segment_id,l_cc_ciphertext,
1276 iby_security_pkg.G_ENCODING_NUMERIC,
1277 p_sys_key,c_card_rec.subkey_cipher_text);
1278 END IF;
1279 ELSE
1280 DELETE iby_security_segments WHERE (sec_segment_id = lx_segment_id);
1281 END IF;
1282 END IF;
1283
1284 UPDATE iby_creditcard
1285 SET
1286 ccnumber = DECODE(encrypted, 'Y',NVL(lx_mask_digits,'0'), ccnumber),
1287 masked_cc_number =
1288 Mask_Card_Number(l_cc_number,lx_mask_option,lx_unmask_len),
1289 cc_num_sec_segment_id = lx_segment_id,
1290 card_mask_setting = lx_mask_option,
1291 card_unmask_length = lx_unmask_len,
1292 object_version_number = object_version_number + 1,
1293 last_update_date = sysdate,
1294 last_updated_by = fnd_global.user_id,
1295 last_update_login = fnd_global.login_id
1296 WHERE (instrid = c_card_rec.instrid);
1297 END LOOP;
1298
1299 IF FND_API.to_Boolean( p_commit ) THEN
1300 COMMIT;
1301 END IF;
1302 END Remask_Instruments;
1303
1304 PROCEDURE Compress_CC_Number
1305 (p_card_number IN iby_creditcard.ccnumber%TYPE,
1306 p_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
1307 p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
1308 p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
1309 p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE,
1310 x_compress_num OUT NOCOPY iby_creditcard.ccnumber%TYPE,
1311 x_unmask_digits OUT NOCOPY iby_creditcard.masked_cc_number%TYPE
1312 )
1313 IS
1314 l_prefix_index NUMBER;
1315 l_unmask_len NUMBER;
1316 l_substr_start NUMBER;
1317 l_substr_stop NUMBER;
1318 BEGIN
1319
1320 x_unmask_digits :=
1321 iby_security_pkg.Get_Unmasked_Data
1322 (p_card_number,p_mask_setting,p_unmask_len);
1323 l_unmask_len := NVL(LENGTH(x_unmask_digits),0);
1324
1325 -- all digits exposed; compressed number is trivial
1326 IF (l_unmask_len >= LENGTH(p_card_number)) THEN
1327 x_compress_num := NULL;
1328 RETURN;
1329 END IF;
1330
1331 IF ( (p_mask_setting = iby_security_pkg.G_MASK_POSTFIX)
1332 AND (p_unmask_len > NVL(LENGTH(p_prefix),0))
1333 )
1334 THEN
1335 l_substr_start := l_unmask_len + 1;
1336 ELSE
1337 l_substr_start := 1 + NVL(LENGTH(p_prefix),0);
1338 END IF;
1339
1340 IF (p_mask_setting = iby_security_pkg.G_MASK_PREFIX)
1341 AND (p_unmask_len>0)
1342 THEN
1343 l_substr_stop := GREATEST(LENGTH(p_card_number)-p_unmask_len,0);
1344 ELSIF (NVL(p_digit_check,'N') = 'Y') THEN
1345 l_substr_stop := LENGTH(p_card_number) - 1;
1346 ELSE
1347 l_substr_stop := LENGTH(p_card_number);
1348 END IF;
1349
1350 IF (l_substr_start < (l_substr_stop +1)) THEN
1351 x_compress_num := SUBSTR(p_card_number,l_substr_start,
1352 l_substr_stop - l_substr_start + 1);
1353 ELSE
1354 x_compress_num := NULL;
1355 END IF;
1356 END Compress_CC_Number;
1357
1358 FUNCTION Uncompress_CC_Number
1359 (p_card_number IN iby_creditcard.ccnumber%TYPE,
1360 p_card_length IN iby_creditcard.cc_number_length%TYPE,
1361 p_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
1362 p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
1363 p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
1364 p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE,
1365 p_unmask_digits IN iby_creditcard.masked_cc_number%TYPE
1366 )
1367 RETURN iby_creditcard.ccnumber%TYPE
1368 IS
1369 l_cc_num iby_creditcard.ccnumber%TYPE;
1370 l_mod_sum NUMBER;
1371 l_unmask_digits_len NUMBER;
1372 l_prefix_len NUMBER;
1373 l_prefix_use NUMBER;
1374 l_add_check_digit BOOLEAN;
1375 BEGIN
1376
1377 l_unmask_digits_len := NVL(LENGTH(p_unmask_digits),0);
1378 l_prefix_len := NVL(LENGTH(p_prefix),0);
1379
1380 IF (p_mask_setting = iby_security_pkg.G_MASK_NONE) THEN
1381 l_cc_num := p_unmask_digits;
1382 l_add_check_digit := FALSE;
1383 END IF;
1384
1385 -- note we assume p_card_number is null if all the digits
1386 -- are known through a combination of unmasked digits, prefix
1387 -- and check digit
1388
1389 IF (p_mask_setting = iby_security_pkg.G_MASK_ALL) THEN
1390 l_cc_num := NVL(p_prefix,'') || NVL(p_card_number,'');
1391 l_add_check_digit := (NVL(p_digit_check,'N') = 'Y');
1392 END IF;
1393
1394 IF (p_mask_setting = iby_security_pkg.G_MASK_POSTFIX) THEN
1395 IF (l_unmask_digits_len > l_prefix_len) THEN
1396 l_cc_num := p_unmask_digits;
1397 ELSE
1398 l_cc_num := p_prefix;
1399 END IF;
1400 l_cc_num := NVL(l_cc_num,'') || NVL(p_card_number,'');
1401 l_add_check_digit := (LENGTH(l_cc_num) < p_card_length);
1402 END IF;
1403
1404 IF (p_mask_setting = iby_security_pkg.G_MASK_PREFIX) THEN
1405 l_cc_num := NVL(p_card_number,'') || NVL(p_unmask_digits,'');
1406 l_add_check_digit := (l_unmask_digits_len < 1);
1407 l_prefix_use := p_card_length - NVL(LENGTH(l_cc_num),0);
1408 IF (l_add_check_digit) THEN l_prefix_use := l_prefix_use - 1; END IF;
1409 l_prefix_use := LEAST(l_prefix_use,l_prefix_len);
1410 IF (l_prefix_use > 0) THEN
1411 l_cc_num := NVL(SUBSTR(p_prefix,1,l_prefix_use),'') || l_cc_num;
1412 END IF;
1413 END IF;
1414
1415 IF (l_add_check_digit) THEN
1416 l_mod_sum := IBY_CC_VALIDATE.CheckCCDigits(l_cc_num||'0');
1417 l_cc_num := l_cc_num || TO_CHAR(MOD(10-l_mod_sum,10));
1418 END IF;
1419
1420 RETURN l_cc_num;
1421
1422 END Uncompress_CC_Number;
1423
1424 FUNCTION Get_Compressed_Len
1425 (p_card_length IN iby_creditcard.cc_number_length%TYPE,
1426 p_prefix IN iby_cc_issuer_ranges.card_number_prefix%TYPE,
1427 p_digit_check IN iby_creditcard_issuers_b.digit_check_flag%TYPE,
1428 p_mask_setting IN iby_sys_security_options.credit_card_mask_setting%TYPE,
1429 p_unmask_len IN iby_sys_security_options.credit_card_unmask_len%TYPE
1430 )
1431 RETURN NUMBER
1432 IS
1433 l_compress_len iby_creditcard.ccnumber%TYPE;
1434 l_prefix_len NUMBER;
1435 BEGIN
1436 l_compress_len := p_card_length;
1437 l_prefix_len := NVL(LENGTH(p_prefix),0);
1438
1439 IF (p_mask_setting = iby_security_pkg.G_MASK_PREFIX) THEN
1440
1441 IF (p_unmask_len>0) THEN
1442 l_compress_len := GREATEST(l_compress_len - p_unmask_len,0);
1443 ELSIF (p_digit_check = 'Y') THEN
1444 l_compress_len := l_compress_len - 1;
1445 END IF;
1446 l_compress_len := GREATEST(l_compress_len - l_prefix_len,0);
1447
1448 ELSIF (p_mask_setting = iby_security_pkg.G_MASK_POSTFIX) THEN
1449
1450 IF (NVL(p_unmask_len,0) > l_prefix_len) THEN
1451 l_compress_len := GREATEST(l_compress_len - p_unmask_len,0);
1452 ELSE
1453 l_compress_len := l_compress_len - l_prefix_len;
1454 END IF;
1455
1456 IF (p_digit_check = 'Y') THEN
1457 l_compress_len := GREATEST(l_compress_len - 1,0);
1458 END IF;
1459
1460 ELSIF (p_mask_setting = iby_security_pkg.G_MASK_NONE) THEN
1461
1462 l_compress_len := 0;
1463
1464 ELSIF (p_mask_setting = iby_security_pkg.G_MASK_ALL) THEN
1465
1466 l_compress_len := l_compress_len - l_prefix_len;
1467 IF (p_digit_check = 'Y') THEN
1468 l_compress_len := GREATEST(l_compress_len - 1,0);
1469 END IF;
1470
1471 END IF;
1472
1473 RETURN l_compress_len;
1474 END Get_Compressed_Len;
1475
1476 PROCEDURE Query_Card
1477 (p_card_id IN iby_creditcard.instrid%TYPE,
1478 p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
1479 x_owner_id OUT NOCOPY iby_creditcard.card_owner_id%TYPE,
1480 x_holder_name OUT NOCOPY iby_creditcard.chname%TYPE,
1481 x_billing_address_id OUT NOCOPY iby_creditcard.addressid%TYPE,
1482 x_billing_address1 OUT NOCOPY hz_locations.address1%TYPE,
1483 x_billing_address2 OUT NOCOPY hz_locations.address2%TYPE,
1484 x_billing_address3 OUT NOCOPY hz_locations.address3%TYPE,
1485 x_billing_city OUT NOCOPY hz_locations.city%TYPE,
1486 x_billing_county OUT NOCOPY hz_locations.county%TYPE,
1487 x_billing_state OUT NOCOPY hz_locations.state%TYPE,
1488 x_billing_zip OUT NOCOPY hz_locations.postal_code%TYPE,
1489 x_billing_country OUT NOCOPY hz_locations.country%TYPE,
1490 x_card_number OUT NOCOPY iby_creditcard.ccnumber%TYPE,
1491 x_expiry_date OUT NOCOPY iby_creditcard.expirydate%TYPE,
1492 x_instr_type OUT NOCOPY iby_creditcard.instrument_type%TYPE,
1493 x_pcard_flag OUT NOCOPY iby_creditcard.purchasecard_flag%TYPE,
1494 x_pcard_type OUT NOCOPY iby_creditcard.purchasecard_subtype%TYPE,
1495 x_issuer OUT NOCOPY iby_creditcard.card_issuer_code%TYPE,
1496 x_fi_name OUT NOCOPY iby_creditcard.finame%TYPE,
1497 x_single_use OUT NOCOPY iby_creditcard.single_use_flag%TYPE,
1498 x_info_only OUT NOCOPY iby_creditcard.information_only_flag%TYPE,
1499 x_purpose OUT NOCOPY iby_creditcard.card_purpose%TYPE,
1500 x_desc OUT NOCOPY iby_creditcard.description%TYPE,
1501 x_active_flag OUT NOCOPY iby_creditcard.active_flag%TYPE,
1502 x_inactive_date OUT NOCOPY iby_creditcard.inactive_date%TYPE,
1503 x_result_code OUT NOCOPY VARCHAR2
1504 )
1505 IS
1506 l_ccnum_ciphertxt iby_creditcard.ccnumber%TYPE;
1507 l_encrypted iby_creditcard.encrypted%TYPE;
1508 l_err_code VARCHAR2(200);
1509 l_instr_found BOOLEAN;
1510
1511 -- variabled for CHNAME and EXPDATE decryption
1512 l_expiry_sec_segment_id NUMBER;
1513 l_chname_sec_Segment_id NUMBER;
1514 --l_chname VARCHAR2(80);
1515 l_str_exp_date VARCHAR2(20);
1516 --l_exp_date DATE;
1517 --l_encrypted_date_format VARCHAR2(20);
1518
1519 CURSOR c_creditcard(ci_instr_id iby_creditcard.instrid%TYPE)
1520 IS
1521 SELECT
1522 c.card_owner_id, c.chname, c.addressid,
1523 l.address1, l.address2, l.address3, l.city, l.county,
1524 l.state, l.postal_code, l.country,
1525 c.ccnumber, c.expirydate, c.instrument_type, c.purchasecard_flag,
1526 c.purchasecard_subtype, c.card_issuer_code, c.finame,
1527 c.single_use_flag, c.information_only_flag, c.card_purpose,
1528 c.description, c.active_flag, c.inactive_date,
1529 c.encrypted, c.expiry_sec_segment_id,
1530 c.chname_sec_segment_id
1531 FROM iby_creditcard c, hz_party_site_uses su, hz_party_sites s,
1532 hz_locations l
1533 WHERE (instrid = ci_instr_id)
1534 AND (c.addressid = su.party_site_use_id(+))
1535 AND (su.party_site_id = s.party_site_id(+))
1536 AND (s.location_id = l.location_id(+));
1537
1538 BEGIN
1539
1540 IF( c_creditcard%ISOPEN ) THEN
1541 CLOSE c_creditcard;
1542 END IF;
1543
1544 IF (NOT p_sys_sec_key IS NULL) THEN
1545 iby_security_pkg.validate_sys_key(p_sys_sec_key,l_err_code);
1546 IF (NOT l_err_code IS NULL) THEN
1547 raise_application_error(-20000, l_err_code, FALSE);
1548 END IF;
1549 END IF;
1550
1551 OPEN c_creditcard(p_card_id);
1552 FETCH c_creditcard
1553 INTO x_owner_id, x_holder_name, x_billing_address_id,
1554 x_billing_address1, x_billing_address2, x_billing_address3,
1555 x_billing_city, x_billing_county, x_billing_state, x_billing_zip,
1556 x_billing_country, x_card_number, x_expiry_date, x_instr_type,
1557 x_pcard_flag, x_pcard_type, x_issuer, x_fi_name, x_single_use,
1558 x_info_only, x_purpose, x_desc, x_active_flag, x_inactive_date,
1559 l_encrypted, l_expiry_sec_segment_id,
1560 l_chname_sec_segment_id;
1561
1562 l_instr_found := (NOT c_creditcard%NOTFOUND);
1563 CLOSE c_creditcard;
1564
1565 IF (NOT l_instr_found) THEN
1566 raise_application_error(-20000,'IBY_20512', FALSE);
1567 END IF;
1568
1569 -- unencrypt/unencode instrument data
1570 --
1571 x_card_number := uncipher_ccnumber(p_card_id,p_sys_sec_key);
1572
1573 -- unencrypt card holder data if its encrypted
1574 IF (nvl(l_encrypted, 'N') = 'Y'
1575 AND Other_CC_Attribs_Encrypted = 'Y')
1576 THEN
1577 IF (l_expiry_sec_segment_id IS NOT NULL) THEN
1578 x_expiry_date := IBY_SECURITY_PKG.decrypt_date_field
1579 (l_expiry_sec_segment_id,
1580 p_sys_sec_key
1581 );
1582 END IF;
1583
1584 IF(l_chname_sec_segment_id IS NOT NULL) THEN
1585 x_holder_name := IBY_SECURITY_PKG.decrypt_field_vals
1586 (l_chname_sec_segment_id,
1587 p_sys_sec_key
1588 );
1589
1590 END IF;
1591 END IF;
1592
1593 END Query_Card;
1594
1595 PROCEDURE Encrypt_Card_Info
1596 (p_commit IN VARCHAR2 := FND_API.G_TRUE,
1597 p_sys_security_key IN iby_security_pkg.DES3_KEY_TYPE,
1598 p_expiry_date IN DATE,
1599 p_chname IN VARCHAR2,
1600 x_exp_segment_id OUT NOCOPY NUMBER,
1601 x_chname_segment_id OUT NOCOPY NUMBER,
1602 x_err_code OUT NOCOPY VARCHAR2
1603 ) IS
1604
1605 l_mode iby_sys_security_options.instr_sec_code_encryption_mode%TYPE;
1606
1607 BEGIN
1608 -- test_debug('Inside Encrypt_Card_Info.. ');
1609 l_mode := Get_CC_Encrypt_Mode;
1610 IF (l_mode = iby_security_pkg.G_ENCRYPT_MODE_NONE) THEN
1611 RETURN;
1612 END IF;
1613 iby_security_pkg.validate_sys_key(p_sys_security_key,x_err_code);
1614 -- test_debug('sysKey valid.. ');
1615 IF (NOT x_err_code IS NULL) THEN
1616 RETURN;
1617 END IF;
1618
1619 IF (NOT p_expiry_date IS NULL) THEN
1620 x_exp_segment_id := IBY_SECURITY_PKG.encrypt_date_field(p_expiry_date,
1621 p_sys_security_key,
1622 null,
1623 'N'
1624 );
1625 END IF;
1626
1627 IF (NOT p_chname IS NULL) THEN
1628 x_chname_segment_id := IBY_SECURITY_PKG.encrypt_field_vals(p_chname,
1629 p_sys_security_key,
1630 null,
1631 'N'
1632 );
1633 END IF;
1634
1635 IF FND_API.To_Boolean(p_commit) THEN
1636 COMMIT;
1637 END IF;
1638
1639
1640 END Encrypt_Card_Info;
1641
1642 PROCEDURE Mark_Expired_Cards
1643 (p_commit IN VARCHAR2 := FND_API.G_TRUE,
1644 p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE
1645 )
1646 IS
1647 l_expiry_date DATE;
1648 l_expired_flag VARCHAR2(1);
1649
1650 CURSOR c_card
1651 IS
1652 SELECT instrid, expiry_sec_segment_id
1653 FROM iby_creditcard
1654 WHERE (encrypted = 'Y')
1655 AND (expiry_sec_segment_id IS NOT NULL)
1656 AND (expired_flag <> 'Y');
1657 BEGIN
1658 IF (c_card%ISOPEN) THEN CLOSE c_card; END IF;
1659 check_key(p_sys_sec_key);
1660
1661 FOR c_card_rec IN c_card LOOP
1662 l_expiry_date := IBY_SECURITY_PKG.decrypt_date_field
1663 (c_card_rec.expiry_sec_segment_id,
1664 p_sys_sec_key
1665 );
1666 IF (TRUNC(l_expiry_date,'DD') < TRUNC(SYSDATE,'DD')) THEN
1667 l_expired_flag := 'Y';
1668 ELSE
1669 l_expired_flag := 'N';
1670 END IF;
1671
1672 UPDATE iby_creditcard
1673 SET expired_flag = l_expired_flag
1674 WHERE (instrid = c_card_rec.instrid);
1675
1676 END LOOP;
1677
1678 IF FND_API.to_Boolean( p_commit ) THEN
1679 COMMIT;
1680 END IF;
1681 END Mark_Expired_Cards;
1682
1683 END iby_creditcard_pkg;