[Home] [Help]
PACKAGE BODY: APPS.IBY_CREDITCARD_PKG
Source
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;
6
7 --
8 -- USE: Gets the credit card encryption mode setting
9 --
10 FUNCTION Get_CC_Encrypt_Mode
11 RETURN iby_sys_security_options.cc_encryption_mode%TYPE
12 IS
13 l_mode iby_sys_security_options.cc_encryption_mode%TYPE;
14 CURSOR c_encrypt_mode
15 IS
16 SELECT cc_encryption_mode
17 FROM iby_sys_security_options;
18 BEGIN
19 IF (c_encrypt_mode%ISOPEN) THEN CLOSE c_encrypt_mode; END IF;
20
21 OPEN c_encrypt_mode;
22 FETCH c_encrypt_mode INTO l_mode;
23 CLOSE c_encrypt_mode;
24
25 RETURN l_mode;
26 END Get_CC_Encrypt_Mode;
27
28 --
29 -- USE: Returns Y or N if the supplemental cardholder data
30 -- e.g., chname and expirydate, are encrypted.
31 --
32 FUNCTION Other_CC_Attribs_Encrypted
33 RETURN VARCHAR2
34 IS
35 l_enc_suppl_data VARCHAR2(1);
36
37 CURSOR c_sec
38 IS
39 SELECT nvl(encrypt_supplemental_card_data, 'N')
40 FROM iby_sys_security_options;
41 BEGIN
42
43 -- No need to cache the value as it may cause synchronization issues.
44 -- IF (enc_supl_data IS NOT NULL) THEN
45 -- RETURN enc_supl_data;
46 -- END IF;
47
48 IF (c_sec%ISOPEN) THEN CLOSE c_sec; END IF;
49
50 OPEN c_sec;
51 FETCH c_sec INTO l_enc_suppl_data;
52 CLOSE c_sec;
53
54 RETURN l_enc_suppl_data;
55 END Other_CC_Attribs_Encrypted;
56
57 FUNCTION isNumber (p_input varchar2)
58 RETURN VARCHAR2
59 IS
60 l_number NUMBER;
61 BEGIN
62 l_number := p_input;
63 RETURN 'Y';
64 EXCEPTION
65 WHEN OTHERS THEN
66 RETURN 'N';
67 END isNumber;
68
69 FUNCTION Get_Billing_Site
70 (p_party_site_id IN hz_party_sites.party_site_id%TYPE,
71 p_party_id IN hz_parties.party_id%TYPE
72 )
73 RETURN hz_party_site_uses.party_site_use_id%TYPE
74 IS
75 l_site_use_id hz_party_site_uses.party_site_use_id%TYPE;
76 l_site_id hz_party_sites.party_site_id%TYPE;
77 l_site_use_rec HZ_PARTY_SITE_V2PUB.Party_Site_Use_rec_type;
78 lx_return_status VARCHAR2(1);
79 lx_msg_count NUMBER;
80 lx_msg_data VARCHAR2(2000);
81
82 CURSOR c_site_use
83 (ci_party_site IN hz_party_sites.party_site_id%TYPE,
84 ci_party_id IN hz_parties.party_id%TYPE
85 )
86 IS
87 SELECT u.party_site_use_id
88 FROM hz_party_site_uses u, hz_party_sites s
89 WHERE (u.party_site_id = ci_party_site)
90 -- because of complexities in the payer model
91 -- do not require the site address to be owned by the card owner
92 --AND (s.party_id = NVL(ci_party_id,party_id))
93 AND (u.party_site_id = s.party_site_id)
94 AND (u.site_use_type = G_CC_BILLING_SITE_USE)
95 AND ( NVL(u.begin_date,SYSDATE-10) < SYSDATE)
96 AND ( NVL(u.end_date,SYSDATE+10) > SYSDATE);
97
98 CURSOR c_site
99 (ci_party_site hz_party_sites.party_site_id%TYPE,
100 ci_party_id IN hz_parties.party_id%TYPE
101 )
102 IS
103 SELECT party_site_id
104 FROM hz_party_sites
105 WHERE (party_site_id = ci_party_site)
106 -- because of complexities in the payer model
107 -- do not require the site address to be owned by the card owner
108 --AND (party_id = NVL(ci_party_id,party_id))
109 AND ( NVL(start_date_active,SYSDATE-10) < SYSDATE)
110 AND ( NVL(end_date_active,SYSDATE+10) > SYSDATE);
111 BEGIN
112 IF c_site_use%ISOPEN THEN CLOSE c_site_use; END IF;
113 IF c_site%ISOPEN THEN CLOSE c_site; END IF;
114
115 OPEN c_site_use(p_party_site_id,NULL);
116 FETCH c_site_use INTO l_site_use_id;
117 CLOSE c_site_use;
118
119 -- create a site use if it does not exist
120 IF (l_site_use_id IS NULL) THEN
121 OPEN c_site(p_party_site_id,p_party_id);
122 FETCH c_site INTO l_site_id;
123 CLOSE c_site;
124
125 IF (NOT l_site_id IS NULL) THEN
126 l_site_use_rec.party_site_id := l_site_id;
127 l_site_use_rec.application_id := 673;
128 l_site_use_rec.site_use_type := G_CC_BILLING_SITE_USE;
129 l_site_use_rec.created_by_module := 'TCA_V2_API';
130
131 HZ_PARTY_SITE_V2PUB.Create_Party_Site_Use
132 (FND_API.G_FALSE,l_site_use_rec,l_site_use_id,
133 lx_return_status,lx_msg_count,lx_msg_data
134 );
135 END IF;
136 END IF;
137
138 RETURN l_site_use_id;
139 END Get_Billing_Site;
140
141 --
142 -- Validates the given system key; an exception is thrown
143 -- if the key is invalid and there is encrypted card
144 -- number data in the instruments table
145 --
146 PROCEDURE check_key( p_sec_key IN VARCHAR2 )
147 IS
148 l_encrypted_count NUMBER := 0;
149 l_keyvalid VARCHAR2(100) := NULL;
150 BEGIN
151
152 iby_security_pkg.validate_sys_key(p_sec_key,l_keyvalid);
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);
162 END IF;
163 END IF;
164
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,
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,
182 x_segment_id,
183 x_masked_chname,
184 x_mask_setting,
185 x_unmask_len);
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);
195 END decrypt_chname;
196
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
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
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,
223 masked_cc_number, card_owner_id, instrument_type, purchasecard_flag,
224 purchasecard_subtype, card_issuer_code, single_use_flag,
225 information_only_flag, card_purpose, active_flag, inactive_date,
226 attribute_category, attribute1, attribute2, attribute3, attribute4,
227 attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
228 attribute11, attribute12, attribute13, attribute14, attribute15,
229 attribute16, attribute17, attribute18, attribute19, attribute20,
230 attribute21, attribute22, attribute23, attribute24, attribute25,
231 attribute26, attribute27, attribute28, attribute29, attribute30,
232 request_id, program_application_id, program_id, program_update_date,
233 created_by, creation_date, last_updated_by, last_update_date,
234 last_update_login, object_version_number
235 )
236 SELECT x_history_id, instrid, expirydate, expiry_sec_segment_id,
237 addressid,
238 description, chname, chname_sec_segment_id, finame, security_group_id,
239 encrypted,
240 masked_cc_number, card_owner_id, instrument_type, purchasecard_flag,
241 purchasecard_subtype, card_issuer_code, single_use_flag,
242 information_only_flag, card_purpose, NVL(active_flag, 'Y'), inactive_date,
243 attribute_category, attribute1, attribute2, attribute3, attribute4,
244 attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
245 attribute11, attribute12, attribute13, attribute14, attribute15,
246 attribute16, attribute17, attribute18, attribute19, attribute20,
247 attribute21, attribute22, attribute23, attribute24, attribute25,
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;
257 END IF;
258 END Archive_Card;
259
260 --
261 -- USE: Gets credit card mask settings
262 --
263 PROCEDURE Get_Mask_Settings
264 (x_mask_setting OUT NOCOPY iby_sys_security_options.credit_card_mask_setting%TYPE,
265 x_unmask_len OUT NOCOPY iby_sys_security_options.credit_card_unmask_len%TYPE
266 )
267 IS
268
269 CURSOR c_mask_setting
270 IS
271 SELECT credit_card_mask_setting, credit_card_unmask_len
272 FROM iby_sys_security_options;
273
274 BEGIN
275 x_mask_setting := iby_security_pkg.G_MASK_PREFIX;
276
277 IF (c_mask_setting%ISOPEN) THEN CLOSE c_mask_setting; END IF;
278
279 OPEN c_mask_setting;
280 FETCH c_mask_setting INTO x_mask_setting, x_unmask_len;
281 CLOSE c_mask_setting;
282
283 IF (x_mask_setting IS NULL) THEN
284 x_mask_setting := iby_security_pkg.G_MASK_PREFIX;
285 END IF;
286 IF (x_unmask_len IS NULL) THEN
287 x_unmask_len := G_DEF_UNMASK_LENGTH;
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
297 IS
298 BEGIN
299 RETURN iby_security_pkg.Mask_Data
300 (p_cc_number,p_mask_option,p_unmask_len,G_MASK_CHARACTER);
301 END Mask_Card_Number;
302
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 )
312 IS
313 BEGIN
314 Get_Mask_Settings(x_mask_setting,x_unmask_len);
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;
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;
328 END Mask_Card_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,
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,
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,
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,
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,
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,
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 )
390 IS
391
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;
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;
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
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
419 l_chname_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
420 l_expdate_sec_segment_id iby_security_segments.sec_segment_id%TYPE;
421 l_chname VARCHAR2(100);
422 l_masked_chname VARCHAR2(100) := NULL;
423 l_chname_mask_setting iby_sys_security_options.credit_card_mask_setting%TYPE;
424 l_chname_unmask_len iby_sys_security_options.credit_card_unmask_len%TYPE;
425 l_exp_date DATE;
426 l_expired VARCHAR2(1) := NULL;
427
428 l_subkey iby_sys_security_subkeys.subkey_cipher_text%TYPE;
429 l_subkey_id iby_sys_security_subkeys.sec_subkey_id%TYPE;
430 l_keyvalid VARCHAR2(100) := NULL;
431
432 -- Variables to be used when an invalid card is registered
433 l_invalid_flag VARCHAR2(1) := NULL;
434 l_invalidation_reason VARCHAR2(100) := NULL;
435 l_isAlphaNumericCard VARCHAR2(1) := 'N';
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
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)
450 )
451 AND (NVL(single_use_flag,'N')='N');
452 BEGIN
453
454 l_allow_invalid_card := NVL(p_allow_invalid_card, 'N');
455
456 IF (c_card%ISOPEN) THEN CLOSE c_card; END IF;
457
458 IF (p_card_number IS NULL ) THEN
459 x_result_code := G_RC_INVALID_CCNUMBER;
460 RETURN;
461 END IF;
462
463 -- expiration date may be null
464 IF (NOT p_expiry_date IS NULL) THEN
465 l_expiry_date := LAST_DAY(p_expiry_date);
466 IF (TRUNC(l_expiry_date,'DD') < TRUNC(SYSDATE,'DD')) THEN
467 x_result_code := G_RC_INVALID_CCEXPIRY;
468 --Keeping this assignment as we may allow registration of expired
469 --credit cards in future.
470 l_expired := 'Y';
471 IF (nvl(l_invalid_flag, 'N') = 'N') THEN
472 l_invalid_flag := 'Y';
473 l_invalidation_reason := G_RC_INVALID_CCEXPIRY;
474 END IF;
475 IF (l_allow_invalid_card <> 'Y') THEN
476 RETURN;
477 END IF;
478 ELSE
479 l_expired := 'N';
480 END IF;
481 END IF;
482
483 IF (NOT p_pcard_type IS NULL) THEN
484 IF (iby_utility_pvt.check_lookup_val(p_pcard_type,G_LKUP_PCARD_TYPE))
485 THEN
486 x_result_code := G_RC_INVALID_PCARD_TYPE;
487 RETURN;
488 END IF;
489 END IF;
490
491 IF ( (NVL(p_instr_type,' ') <> G_LKUP_INSTR_TYPE_CC)
492 AND (NVL(p_instr_type,' ') <> G_LKUP_INSTR_TYPE_DC)
493 AND (NVL(p_instr_type,' ') <> G_LKUP_INSTR_TYPE_PC ))
494 THEN
495 x_result_code := G_RC_INVALID_INSTR_TYPE;
496 RETURN;
497 END IF;
498
499 iby_cc_validate.StripCC
500 (1.0, FND_API.G_FALSE, p_card_number,
501 lx_return_status, lx_msg_count, lx_msg_data, lx_cc_number
502 );
503
504 IF( (lx_cc_number IS NULL) OR
505 (lx_return_status IS NULL OR
506 lx_return_status <> FND_API.G_RET_STS_SUCCESS) )
507 THEN
508 x_result_code := G_RC_INVALID_CCNUMBER;
509 RETURN;
510 END IF;
511
512 iby_cc_validate.Get_CC_Issuer_Range
513 (lx_cc_number,lx_card_issuer,lx_issuer_range,lx_card_prefix,lx_digit_check);
514 --Bug# 8346420
515 --When the upstream product passes the information for card issuer then
516 --the below block would be executed. In case of "UNKNOWN" card types, the
517 --value is coming as empty, in that case we trim and compare. By doing that
518 --the below condition wouldn't be satisfied and the card will be registered.
519 --
520 -- [lmallick] - If p_issuer is UNKNOWN then honour the internally derived
521 -- issuer range.
522 IF(length(TRIM(p_issuer)) > 0 AND p_issuer <> 'UNKNOWN') THEN
523 IF ( (NOT p_issuer IS NULL) AND (p_issuer <> lx_card_issuer) ) THEN
524 x_result_code := G_RC_INVALID_CARD_ISSUER;
525 IF (nvl(l_invalid_flag, 'N') = 'N') THEN
526 l_invalid_flag := 'Y';
527 l_invalidation_reason := G_RC_INVALID_CARD_ISSUER;
528 END IF;
529 --Since the card issuer is already invalid, no need to
530 --perform digit check
531 lx_digit_check := 'N';
532 lx_card_issuer := p_issuer;
533
534 IF (l_allow_invalid_card <> 'Y') THEN
535 RETURN;
536 END IF;
537 END IF;
538 END IF;
539
540 IF (lx_digit_check = 'Y') THEN
541 IF ( MOD(iby_cc_validate.CheckCCDigits(lx_cc_number),10) <> 0 ) THEN
542 x_result_code := G_RC_INVALID_CCNUMBER;
543 IF (nvl(l_invalid_flag, 'N') = 'N') THEN
544 l_invalid_flag := 'Y';
545 l_invalidation_reason := G_RC_INVALID_CCNUMBER;
546 END IF;
547 IF (l_allow_invalid_card <> 'Y') THEN
548 RETURN;
549 END IF;
550 END IF;
551 END IF;
552
553 -- necessary to decompress secured card instruments, but only if
554 -- no known issuer range matches
555 IF (lx_issuer_range IS NULL) THEN
556 l_card_len := LENGTH(lx_cc_number);
557 END IF;
558
559 Mask_Card_Number(lx_cc_number,lx_masked_number,lx_mask_option,lx_unmask_len);
560 l_cc_hash1 := iby_security_pkg.get_hash(lx_cc_number,'F');
561 -- get hash value for a salted version of the card number
562 l_cc_hash2 := iby_security_pkg.get_hash(lx_cc_number,'T');
563
564 -- [lmallick]
565 -- do not perform the TCA entity validation here. Instead, trust
566 -- the data passed here
567
568 -- Bug 5153265 start
569 -- If Site use id is already provied then no need to call get_billing address
570 /*IF (p_address_type = G_PARTY_SITE_USE_ID) AND (NOT (p_billing_address_id IS NULL)) THEN
571 l_billing_site := p_billing_address_id;
572 ELSE
573 IF (p_billing_address_id = FND_API.G_MISS_NUM ) THEN
574 l_billing_site := FND_API.G_MISS_NUM;
575 ELSIF (NOT (p_billing_address_id IS NULL)) THEN
576 l_billing_site := Get_Billing_Site(p_billing_address_id,p_owner_id);
577 IF (l_billing_site IS NULL) THEN
578 x_result_code := G_RC_INVALID_ADDRESS;
579 RETURN;
580 END IF;
581 END IF;
582 END IF;
583 -- Bug 5153265 end
584
585
586 IF (NOT ( (p_billing_country IS NULL)
587 OR (p_billing_country = FND_API.G_MISS_CHAR) )
588 )
589 THEN
590 IF (NOT iby_utility_pvt.Validate_Territory(p_billing_country)) THEN
591 x_result_code := G_RC_INVALID_ADDRESS;
592 RETURN;
593 END IF;
594 END IF;
595
596 IF (NOT p_owner_id IS NULL) THEN
597 IF (NOT iby_utility_pvt.validate_party_id(p_owner_id)) THEN
598 x_result_code := G_RC_INVALID_PARTY;
599 RETURN;
600 END IF;
601 END IF;*/
602
603 OPEN c_card(p_owner_id,l_cc_hash1,l_cc_hash2);
604 FETCH c_card INTO x_instr_id;
605 CLOSE c_card;
606
607 IF (NOT x_instr_id IS NULL) THEN RETURN; END IF;
608
609 IF (NOT p_sys_sec_key IS NULL) THEN
610 -- check the system key
611 iby_security_pkg.validate_sys_key(p_sys_sec_key,l_keyvalid);
612
613 IF (NOT l_keyvalid IS NULL) THEN
614 x_result_code := 'INVALID_SEC_KEY';
615 RETURN;
616 END IF;
617 l_encrypted := 'Y';
618
619 Compress_CC_Number
620 (lx_cc_number,lx_card_prefix,lx_digit_check,lx_mask_option,
621 lx_unmask_len,lx_cc_compressed,lx_unmasked_digits);
622
623 IF (NOT lx_cc_compressed IS NULL) THEN
624 l_cc_ciphertext :=
625 HEXTORAW(IBY_SECURITY_PKG.Encode_Number(lx_cc_compressed,TRUE));
626 IBY_SECURITY_PKG.Create_Segment
627 (FND_API.G_FALSE,l_cc_ciphertext,iby_security_pkg.G_ENCODING_NUMERIC,
628 p_sys_sec_key,lx_sec_segment_id);
629 END IF;
630 lx_cc_number := NVL(lx_unmasked_digits,'0');
631
632 l_chname := p_holder_name;
633 -- Do not allow a chname containing a mask character(*)
634 IF(INSTR(l_chname, '*') <> 0)THEN
635 l_chname := null;
636 END IF;
637
638 --now need to encrypt the supplemental cardholder data
639 --i.e, CHNAME and EXPDATE for now.
640 IF(Other_CC_Attribs_Encrypted = 'Y') THEN
641 -- l_chname_sec_segment_id :=
642 -- IBY_SECURITY_PKG.encrypt_field_vals(p_holder_name,
643 -- p_sys_sec_key,
644 -- null,
645 -- 'N'
646 -- );
647 l_encrypted := 'A';
648 l_expdate_sec_segment_id :=
649 IBY_SECURITY_PKG.encrypt_date_field(l_expiry_date,
650 p_sys_sec_key,
651 null,
652 'N'
653 );
654 Encrypt_Chname
655 (p_sys_sec_key,
656 l_chname,
657 null,
658 l_chname_sec_segment_id,
659 l_masked_chname,
660 l_chname_mask_setting,
661 l_chname_unmask_len
662 );
663
664
665 -- The actuall date column will hold a NULL value in this
666 -- case.
667 l_expiry_date := NULL;
668 ELSE
669 l_masked_chname := p_holder_name;
670 END IF;
671 ELSE
672 --l_encrypted := 'N';
673 -- we use the same CHNAME column for storing the masked value
674 -- when encryption is enabled. So, make this value point to
675 -- the clear text when encryption is not enabled.
676 -- Also the expiry date column will hold the actual exp date
677 -- in this case.
678 l_masked_chname := p_holder_name;
679 END IF;
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,
689 chname_mask_setting, chname_unmask_length,
690 addressid, billing_addr_postal_code, bill_addr_territory_code,
691 instrument_type, purchasecard_flag, purchasecard_subtype,
692 card_issuer_code, cc_issuer_range_id, cc_number_length,
693 description, finame, encrypted, cc_num_sec_segment_id,
694 single_use_flag, information_only_flag, card_purpose,
695 active_flag, inactive_date,
696 last_update_date, last_updated_by, creation_date,
697 created_by, last_update_login, object_version_number,
698 attribute_category,
699 attribute1,attribute2, attribute3,attribute4,attribute5,
700 attribute6,attribute7, attribute8,attribute9,attribute10,
701 attribute11,attribute12, attribute13,attribute14,attribute15,
702 attribute16,attribute17, attribute18,attribute19,attribute20,
703 attribute21,attribute22, attribute23,attribute24,attribute25,
704 attribute26,attribute27, attribute28,attribute29,attribute30,
705 invalid_flag, invalidation_reason,
706 salt_version
707 )
708 VALUES
709 (x_instr_id, lx_cc_number, lx_masked_number,
710 lx_mask_option, lx_unmask_len, l_cc_hash1, l_cc_hash2,
711 l_expiry_date, l_expdate_sec_segment_id, l_expired,
712 p_owner_id, l_masked_chname, l_chname_sec_segment_id,
713 l_chname_mask_setting, l_chname_unmask_len,
714 p_billing_address_id, p_billing_zip, p_billing_country,
715 p_instr_type, NVL(p_pcard_flag,'N'), p_pcard_type,
716 lx_card_issuer, lx_issuer_range, l_card_len,
717 p_desc, p_fi_name, l_encrypted, lx_sec_segment_id,
718 NVL(p_single_use,'N'), NVL(p_info_only,'N'), p_purpose,
719 NVL(p_active_flag,'Y'), p_inactive_date,
720 sysdate, nvl(p_user_id, fnd_global.user_id), sysdate,
721 decode(p_user_id,-1,fnd_global.user_id,p_user_id),
722 decode(p_login_id,-1,fnd_global.login_id,p_login_id), 1,
723 p_attribute_category,
724 p_attribute1,p_attribute2,p_attribute3,p_attribute4,p_attribute5,
725 p_attribute6,p_attribute7,p_attribute8,p_attribute9,p_attribute10,
726 p_attribute11,p_attribute12,p_attribute13,p_attribute14,p_attribute15,
727 p_attribute16,p_attribute17, p_attribute18,p_attribute19,p_attribute20,
728 p_attribute21,p_attribute22, p_attribute23,p_attribute24,p_attribute25,
729 p_attribute26,p_attribute27, p_attribute28,p_attribute29,p_attribute30,
730 l_invalid_flag, l_invalidation_reason,
731 iby_security_pkg.get_salt_version
732 );
733
734 -- Reached upto this point implies that the registration has succeeded
735 -- clear the error codes that might have got assigned to the x_result_code parameter
736 -- during invalid credit card registration
737 x_result_code := NULL;
738
739 IF FND_API.To_Boolean(p_commit) THEN
740 COMMIT;
741 END IF;
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,
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,
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,
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,
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,
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,
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
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;
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);
816 l_return_status VARCHAR2(1);
817 l_resp_rec IBY_INSTRREG_PUB.SecureCardInfoResp_rec_type;
818 l_sec_code VARCHAR2(10);
819
820 -- l_encrypted_date_format VARCHAR2(20) := NULL;
821 l_exp_date DATE;
822 l_encrypted VARCHAR2(1);
823 l_keyvalid VARCHAR2(100) := NULL;
824
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
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');
836 IF (NOT p_pcard_type IS NULL) THEN
837 IF (iby_utility_pvt.check_lookup_val(p_pcard_type,G_LKUP_PCARD_TYPE)) THEN
838 x_result_code := G_RC_INVALID_PCARD_TYPE;
839 RETURN;
840 END IF;
841 END IF;
842
843 IF (NOT p_instr_type IS NULL) THEN
844 IF ( (p_instr_type <> G_LKUP_INSTR_TYPE_CC)
845 AND (p_instr_type <> G_LKUP_INSTR_TYPE_DC) )
846 THEN
847 x_result_code := G_RC_INVALID_INSTR_TYPE;
848 RETURN;
849 END IF;
850 END IF;
851 IF (NOT p_owner_id IS NULL) THEN
852 IF (NOT iby_utility_pvt.validate_party_id(p_owner_id)) THEN
853 x_result_code := G_RC_INVALID_PARTY;
854 RETURN;
855 END IF;
856 END IF;
857 -- Bug 5153265 start
858 -- If Site use id is already provied then no need to call get_billing address
859 IF (p_address_type = G_PARTY_SITE_USE_ID) AND (NOT (p_billing_address_id IS NULL)) THEN
860 l_billing_site := p_billing_address_id;
861 ELSE
862 IF (p_billing_address_id = FND_API.G_MISS_NUM ) THEN
863 l_billing_site := FND_API.G_MISS_NUM;
864 ELSIF (NOT (p_billing_address_id IS NULL)) THEN
865 l_billing_site := Get_Billing_Site(p_billing_address_id,p_owner_id);
866 IF (l_billing_site IS NULL) THEN
867 x_result_code := G_RC_INVALID_ADDRESS;
868 RETURN;
869 END IF;
870 END IF;
871 END IF;
872 -- Bug 5153265 end
873
874 IF (NOT ( (p_billing_country IS NULL)
875 OR (p_billing_country = FND_API.G_MISS_CHAR) )
876 )
877 THEN
878 IF (NOT iby_utility_pvt.Validate_Territory(p_billing_country)) THEN
879 x_result_code := G_RC_INVALID_ADDRESS;
880 RETURN;
881 END IF;
882 END IF;
883
884 -- To be removed
885 /* iby_debug_pub.add('expiry date passed as:'||p_expiry_date,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
886 iby_debug_pub.add('holder name:'||p_holder_name,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
887 IF(p_holder_name = FND_API.G_MISS_CHAR)THEN
888 iby_debug_pub.add('holder name is FND_API.G_MISS_CHAR.',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
889 END IF;*/
890 ---------------------
891
892 -- Bug 5479785 (Panaraya)
893 -- Added check for expiry date on update
894 -- expiration date may be null
895 IF (NOT p_expiry_date IS NULL) THEN
896 l_expiry_date := LAST_DAY(p_expiry_date);
897 IF (TRUNC(l_expiry_date,'DD') < TRUNC(SYSDATE,'DD')) THEN
898 x_result_code := G_RC_INVALID_CCEXPIRY;
899 l_expired := 'Y';
900 IF (l_allow_invalid_card <> 'Y') THEN
901 RETURN;
902 END IF;
903 ELSE
904 l_expired := 'N';
905 END IF;
906 END IF;
907
908 -- Get the encrypted flag value of the existing record
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);
918 iby_debug_pub.add('chname_sec_segment_id:'||l_chname_sec_segment_id,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
919 iby_debug_pub.add('chname_unmask_length:'||l_chname_unmask_len,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
920 END IF;
921
922 -- If a masked chname is passed then ignore this for update
923 l_chname := p_holder_name;
924 IF(INSTR(l_chname, '*') <> 0)THEN
925 l_chname := null;
926 END IF;
927
928 -- Need to encrypt the sensitive data only if the record was
929 -- previously encrypted and of course the encryption mode
930 -- shouldn't be NONE
931 IF (Get_CC_Encrypt_Mode <> IBY_SECURITY_PKG.G_ENCRYPT_MODE_NONE
932 AND l_encrypted = 'A'
933 --AND Other_CC_Attribs_Encrypted = 'Y'
934 ) THEN
935
936 --Get_Mask_Settings(l_chname_mask_setting, l_chname_unmask_len);
937
938 --If p_holder_name is null then do not update the chname
939 --(and do not pass unnecessary values in the http request)
940 IF (p_holder_name IS NULL) THEN
941 l_chname_sec_segment_id := null;
942 l_chname_mask_setting := null;
943 l_chname_unmask_len := null;
944 END IF;
945
946 IF(l_expiry_date IS NULL) THEN
947 l_expdate_sec_segment_id := null;
948 END IF;
949
950 /*
951 IF (p_holder_name = FND_API.G_MISS_CHAR) THEN
952 iby_debug_pub.add('chname passed: G_MISS_CHAR',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
953 END IF;
954 iby_debug_pub.add('chname passed:'||p_holder_name,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
955 */
956
957 -- No need for the http request if the expiry date and chname
958 -- are not expected to be updated
959 IF((l_expiry_date IS NOT NULL) OR
960 ((p_holder_name IS NOT NULL) AND (p_holder_name <> FND_API.G_MISS_CHAR))
961 ) THEN
962
963 IBY_INSTRREG_PUB.SecureCardInfo(l_expiry_date,
964 l_expdate_sec_segment_id,
965 l_chname,
966 l_chname_sec_segment_id,
967 l_chname_mask_setting,
968 l_chname_unmask_len,
969 l_return_status,
970 l_msg_count,
971 l_msg_data,
972 l_resp_rec
973 );
974 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
975 iby_debug_pub.add('Error during http call out',iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
976 x_result_code := FND_API.G_RET_STS_ERROR;
977 RETURN;
978 END IF;
979
980 l_chname_sec_segment_id := l_resp_rec.ChnameSegmentId;
981 l_expdate_sec_segment_id := l_resp_rec.ExpiryDateSegmentId;
982 l_masked_chname := l_resp_rec.MaskedChname;
983 l_chname_mask_setting := l_resp_rec.ChnameMaskSetting;
984 l_chname_unmask_len := l_resp_rec.ChnameUnmaskLength;
985
986 l_expiry_date := NULL;
987
988 IF(iby_debug_pub.G_LEVEL_INFO >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
989 iby_debug_pub.add('l_masked_chname(2):'||l_masked_chname,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
990 iby_debug_pub.add('expiry_sec_segment_id(2):'||l_chname_sec_segment_id,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
991 iby_debug_pub.add('chname_sec_segment_id(2):'||l_expdate_sec_segment_id,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
992 iby_debug_pub.add('chname_unmask_length(2):'||l_chname_unmask_len,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
993 END IF;
994
995 ELSE
996 iby_debug_pub.add('Skipping http callout..',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
997 END IF;
998
999 ELSE
1000 --l_encrypted := 'N';
1001 -- we use the same CHNAME column for storing the masked value
1002 -- when encryption is enabled. So, make this value point to
1003 -- the clear text when encryption is not enabled.
1004 -- Also the expiry date column will hold the actual exp date
1005 -- in this case.
1006 l_masked_chname := l_chname;
1007
1008 END IF;
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,
1018 NULL,chname_mask_setting,l_chname_mask_setting),
1019 chname_unmask_length = DECODE(l_chname, FND_API.G_MISS_CHAR,NULL,
1020 NULL,chname_unmask_length,l_chname_unmask_len),
1021 card_owner_id = NVL(card_owner_id,p_owner_id),
1022 addressid = DECODE(l_billing_site, FND_API.G_MISS_NUM,NULL,
1023 NULL,addressid, l_billing_site),
1024 bill_addr_territory_code =
1025 DECODE(p_billing_country, FND_API.G_MISS_CHAR,NULL,
1026 NULL,bill_addr_territory_code, p_billing_country),
1027 billing_addr_postal_code =
1028 DECODE(p_billing_zip, FND_API.G_MISS_CHAR,NULL,
1029 NULL,billing_addr_postal_code, p_billing_zip),
1030 -- expirydate = NVL(p_expiry_date, expirydate),
1031 expirydate = DECODE(p_expiry_date, NULL, expirydate, l_expiry_date),
1032 expiry_sec_segment_id = DECODE(p_expiry_date, NULL, expiry_sec_segment_id,
1033 l_expdate_sec_segment_id),
1034 expired_flag = nvl(l_expired, expired_flag),
1035 encrypted = l_encrypted,
1036 instrument_type = NVL(p_instr_type, instrument_type),
1037 purchasecard_flag = NVL(p_pcard_flag, purchasecard_flag),
1038 purchasecard_subtype =
1039 DECODE(p_pcard_type, FND_API.G_MISS_CHAR,NULL,
1040 NULL,purchasecard_subtype, p_pcard_type),
1041 finame = DECODE(p_fi_name, FND_API.G_MISS_CHAR,NULL, NULL,finame, p_fi_name),
1042 single_use_flag = NVL(p_single_use, single_use_flag),
1043 information_only_flag = NVL(p_info_only, information_only_flag),
1044 card_purpose = DECODE(p_purpose, FND_API.G_MISS_CHAR,NULL, NULL,card_purpose, p_purpose),
1045 description = DECODE(p_desc, FND_API.G_MISS_CHAR,NULL, NULL,description, p_desc),
1046 active_flag = NVL(p_active_flag, active_flag),
1047 inactive_date = DECODE(p_inactive_date, FND_API.G_MISS_DATE,NULL,
1048 NULL,inactive_date, p_inactive_date),
1049 object_version_number = object_version_number + 1,
1050 last_update_date = sysdate,
1051 last_updated_by = fnd_global.user_id,
1052 last_update_login = fnd_global.login_id,
1053 attribute_category = p_Attribute_category,
1054 attribute1 = DECODE(p_attribute1,FND_API.G_MISS_CHAR,NULL,NULL,attribute1, p_attribute1),
1055 attribute2 = DECODE(p_attribute2,FND_API.G_MISS_CHAR,NULL,NULL,attribute2, p_attribute2),
1056 attribute3 = DECODE(p_attribute3,FND_API.G_MISS_CHAR,NULL,NULL,attribute3, p_attribute3),
1057 attribute4 = DECODE(p_attribute4,FND_API.G_MISS_CHAR,NULL,NULL,attribute4, p_attribute4),
1058 attribute5 = DECODE(p_attribute5,FND_API.G_MISS_CHAR,NULL,NULL,attribute5, p_attribute5),
1059 attribute6 = DECODE(p_attribute6,FND_API.G_MISS_CHAR,NULL,NULL,attribute6, p_attribute6),
1060 attribute7 = DECODE(p_attribute7,FND_API.G_MISS_CHAR,NULL,NULL,attribute7, p_attribute7),
1061 attribute8 = DECODE(p_attribute8,FND_API.G_MISS_CHAR,NULL,NULL,attribute8, p_attribute8),
1062 attribute9 = DECODE(p_attribute9,FND_API.G_MISS_CHAR,NULL,NULL,attribute9, p_attribute9),
1063 attribute10 = DECODE(p_attribute10,FND_API.G_MISS_CHAR,NULL,NULL,attribute10, p_attribute10),
1064 attribute11 = DECODE(p_attribute11,FND_API.G_MISS_CHAR,NULL,NULL,attribute11, p_attribute11),
1065 attribute12 = DECODE(p_attribute12,FND_API.G_MISS_CHAR,NULL,NULL,attribute12, p_attribute12),
1066 attribute13 = DECODE(p_attribute13,FND_API.G_MISS_CHAR,NULL,NULL,attribute13, p_attribute13),
1067 attribute14 = DECODE(p_attribute14,FND_API.G_MISS_CHAR,NULL,NULL,attribute14, p_attribute14),
1068 attribute15 = DECODE(p_attribute15,FND_API.G_MISS_CHAR,NULL,NULL,attribute15, p_attribute15),
1069 attribute16 = DECODE(p_attribute16,FND_API.G_MISS_CHAR,NULL,NULL,attribute16, p_attribute16),
1070 attribute17 = DECODE(p_attribute17,FND_API.G_MISS_CHAR,NULL,NULL,attribute17, p_attribute17),
1071 attribute18 = DECODE(p_attribute18,FND_API.G_MISS_CHAR,NULL,NULL,attribute18, p_attribute18),
1072 attribute19 = DECODE(p_attribute19,FND_API.G_MISS_CHAR,NULL,NULL,attribute19, p_attribute19),
1073 attribute20 = DECODE(p_attribute20,FND_API.G_MISS_CHAR,NULL,NULL,attribute20, p_attribute20),
1074 attribute21 = DECODE(p_attribute21,FND_API.G_MISS_CHAR,NULL,NULL,attribute21, p_attribute21),
1075 attribute22 = DECODE(p_attribute22,FND_API.G_MISS_CHAR,NULL,NULL,attribute22, p_attribute22),
1076 attribute23 = DECODE(p_attribute23,FND_API.G_MISS_CHAR,NULL,NULL,attribute23, p_attribute23),
1077 attribute24 = DECODE(p_attribute24,FND_API.G_MISS_CHAR,NULL,NULL,attribute24, p_attribute24),
1078 attribute25 = DECODE(p_attribute25,FND_API.G_MISS_CHAR,NULL,NULL,attribute25, p_attribute25),
1079 attribute26 = DECODE(p_attribute26,FND_API.G_MISS_CHAR,NULL,NULL,attribute26, p_attribute26),
1080 attribute27 = DECODE(p_attribute27,FND_API.G_MISS_CHAR,NULL,NULL,attribute27, p_attribute27),
1081 attribute28 = DECODE(p_attribute28,FND_API.G_MISS_CHAR,NULL,NULL,attribute28, p_attribute28),
1082 attribute29 = DECODE(p_attribute29,FND_API.G_MISS_CHAR,NULL,NULL,attribute29, p_attribute29),
1083 attribute30 = DECODE(p_attribute30,FND_API.G_MISS_CHAR,NULL,NULL,attribute30, p_attribute30)
1084 WHERE (instrid = p_instr_id);
1085
1086 IF (SQL%NOTFOUND) THEN x_result_code := G_RC_INVALID_CARD_ID;
1087 ELSE x_result_code := null;
1088 END IF;
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);
1098
1099 IF FND_API.To_Boolean(p_commit) THEN
1100 COMMIT;
1101 END IF;
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 )
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)
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
1129 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
1130
1131 -- Hold the newly supplied issuer code in a temporary variable
1132 -- and initialize the out parameter to NULL
1133 l_temp_issuer := x_issuer;
1134 x_issuer := NULL;
1135
1136 IF (c_card%ISOPEN) THEN CLOSE c_card; END IF;
1137
1138 OPEN c_card(p_instr_id);
1139 FETCH c_card INTO x_invalid_flag, x_invalidation_reason,
1140 l_expired, l_card_issuer1, l_card_issuer2;
1141 CLOSE c_card;
1142
1143 iby_debug_pub.add('old invalid_flag:'||x_invalid_flag,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1144 iby_debug_pub.add('old invalidation_reason:'||x_invalidation_reason,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1145
1146 -- Card number is immutable. So, an invalid card number cannot be corrected.
1147 IF(NVL(x_invalidation_reason, 'VALID') = G_RC_INVALID_CCNUMBER)THEN
1148 iby_debug_pub.add('existing record is invalid due to '||x_invalidation_reason,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1149 RETURN;
1150 END IF ;
1151
1152 -- Re-set the values to null assuming that the record is valid
1153 x_invalid_flag := NULL;
1154 x_invalidation_reason := NULL;
1155
1156 iby_debug_pub.add('validating card expiry',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1157 IF (NVL(l_expired,'N') = 'Y')THEN
1158 iby_debug_pub.add('card has invalid expiry!',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1159 x_invalid_flag := 'Y';
1160 x_invalidation_reason := G_RC_INVALID_CCEXPIRY;
1161 RETURN;
1162 END IF ;
1163
1164 iby_debug_pub.add('validating card issuer',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1165 IF (NVL(l_card_issuer1,'UNKNOWN') <> NVL(l_card_issuer2, 'UNKNOWN'))THEN
1166 iby_debug_pub.add('card has invalid issuer!',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1167
1168 -- Check if a new issuer_code is supplied to the API
1169 -- If it is supplied then does it make the record valid
1170 IF (l_temp_issuer IS NOT NULL AND l_temp_issuer <> FND_API.G_MISS_CHAR) THEN
1171 IF (l_temp_issuer = NVL(l_card_issuer2, 'UNKNOWN'))THEN
1172 iby_debug_pub.add('New card issuer makes the record valid.',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1173 x_issuer := l_temp_issuer;
1174 ELSE
1175 x_invalid_flag := 'Y';
1176 x_invalidation_reason := G_RC_INVALID_CARD_ISSUER;
1177 RETURN;
1178 END IF ;
1179 ELSE
1180 x_invalid_flag := 'Y';
1181 x_invalidation_reason := G_RC_INVALID_CARD_ISSUER;
1182 RETURN;
1183 END IF ;
1184 END IF ;
1185
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
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;
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
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,
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(+))
1222 AND (seg.sec_subkey_id = k.sec_subkey_id(+))
1223 AND (c.cc_issuer_range_id = r.cc_issuer_range_id(+))
1224 AND (r.card_issuer_code = i.card_issuer_code(+));
1225 BEGIN
1226
1227 IF (c_instr_num%ISOPEN) THEN CLOSE c_instr_num; END IF;
1228
1229 OPEN c_instr_num(p_instrid);
1230 FETCH c_instr_num INTO l_cc_number, l_masked_cc_number,
1231 l_segment_cipher, l_encrypted, l_subkey_cipher,
1232 l_cc_prefix, l_digit_check, l_mask_option, l_unmask_len,
1233 l_unmask_digits, l_card_len;
1234 CLOSE c_instr_num;
1235
1236 IF (l_cc_number IS NULL) THEN
1237 raise_application_error(-20000, 'IBY_20512#', FALSE);
1238 END IF;
1239
1240 -- [lmallick] - 11876396
1241 -- For extract debugging
1242 IF (iby_utility_pvt.debug_mode_for_extract_enabled = 'Y') THEN
1243 RETURN l_masked_cc_number;
1244 END IF ;
1245
1246 IF (l_encrypted = 'Y' OR l_encrypted = 'A') THEN
1247 iby_security_pkg.validate_sys_key(p_sys_sec_key,l_keyvalid);
1248 IF (NOT l_keyvalid IS NULL) THEN
1249 raise_application_error(-20000,'IBY_10008#INSTRID='||p_instrid, FALSE);
1250 END IF;
1251 END IF;
1252
1253 RETURN uncipher_ccnumber(l_cc_number, l_segment_cipher, l_encrypted,
1254 p_sys_sec_key, l_subkey_cipher, l_card_len,
1255 l_cc_prefix, l_digit_check, l_mask_option,
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,
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 )
1272 RETURN iby_creditcard.ccnumber%TYPE
1273 IS
1274 BEGIN
1275 RETURN iby_security_pkg.uncipher_ccnumber
1276 (p_cc_number,
1277 p_segment_cipher,
1278 p_encrypted,
1279 p_sys_key,
1280 p_subkey_cipher,
1281 p_card_len,
1282 p_cc_prefix,
1283 p_digit_check,
1284 p_mask_setting,
1285 p_unmask_len,
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
1295 RETURN uncipher_ccnumber(i_instrid, i_sys_sec_key);
1296 EXCEPTION
1297 WHEN OTHERS THEN
1298 RETURN null;
1299 END uncipher_ccnumber_ui_wrp;
1300
1301 PROCEDURE Decrypt_Instruments
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);
1311 l_exp_date DATE;
1312 -- l_encrypted_date_format VARCHAR2(20);
1313
1314 CURSOR c_card
1315 IS
1316 SELECT c.instrid, c.ccnumber, seg.segment_cipher_text,
1317 c.encrypted, k.subkey_cipher_text,
1318 NVL(r.card_number_length,c.cc_number_length) card_len,
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(+))
1328 AND (c.cc_issuer_range_id = r.cc_issuer_range_id(+))
1329 AND (c.cc_num_sec_segment_id = seg.sec_segment_id(+))
1330 AND (seg.sec_subkey_id = k.sec_subkey_id(+));
1331 BEGIN
1332
1333 --checking the encryption mode
1334 l_mode := Get_CC_Encrypt_Mode();
1335 IF (NOT (l_mode = iby_security_pkg.G_ENCRYPT_MODE_NONE)) THEN
1336 RETURN;
1337 END IF;
1338
1339 FOR c_card_rec IN c_card LOOP
1340
1341 l_cc_number :=
1342 uncipher_ccnumber
1343 (c_card_rec.ccnumber, c_card_rec.segment_cipher_text,
1344 c_card_rec.encrypted, p_sys_key, c_card_rec.subkey_cipher_text,
1345 c_card_rec.card_len, c_card_rec.card_number_prefix,
1346 c_card_rec.digit_check_flag, c_card_rec.card_mask_setting,
1347 c_card_rec.card_unmask_length, c_card_rec.unmask_digits
1348 );
1349
1350 IF (c_card_rec.expiry_sec_segment_id IS NOT NULL) THEN
1351 l_exp_date := IBY_SECURITY_PKG.decrypt_date_field
1352 (c_card_rec.expiry_sec_segment_id,
1353 p_sys_key
1354 );
1355 ELSE
1356 -- The exp date wasn't encrypted
1357 l_exp_date := c_card_rec.expirydate;
1358 END IF;
1359
1360 IF(c_card_rec.chname_sec_segment_id IS NOT NULL) THEN
1361 l_chname := decrypt_chname(p_sys_key, c_card_rec.instrid);
1362 ELSE
1363 -- CHNAME wasn't encrypted
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,
1373 expirydate = l_exp_date,
1374 expiry_sec_segment_id = NULL,
1375 chname = l_chname,
1376 chname_sec_segment_id = NULL,
1377 chname_mask_setting = NULL,
1378 chname_unmask_length = NULL,
1379 object_version_number = object_version_number + 1,
1380 last_update_date = sysdate,
1381 last_updated_by = fnd_global.user_id,
1382 last_update_login = fnd_global.login_id
1383 WHERE (instrid = c_card_rec.instrid);
1384
1385 DELETE iby_security_segments
1386 WHERE (sec_segment_id IN (c_card_rec.cc_num_sec_segment_id,
1387 c_card_rec.chname_sec_segment_id,
1388 c_card_rec.expiry_sec_segment_id)
1389 );
1390 END LOOP;
1391
1392 IF FND_API.to_Boolean( p_commit ) THEN
1393 COMMIT;
1394 END IF;
1395 END Decrypt_Instruments;
1396
1397 PROCEDURE Encrypt_Instruments
1398 (p_commit IN VARCHAR2,
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;
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;
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
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(+))
1435 AND (c.cc_issuer_range_id = r.cc_issuer_range_id(+))
1436 AND (c.cc_num_sec_segment_id = seg.sec_segment_id(+))
1437 AND (seg.sec_subkey_id = k.sec_subkey_id(+));
1438 BEGIN
1439
1440 l_mode := Get_CC_Encrypt_Mode();
1441 l_enc_supl_data := Other_CC_Attribs_Encrypted;
1442 IF (l_mode = iby_security_pkg.G_ENCRYPT_MODE_NONE) THEN
1443 RETURN;
1444 END IF;
1445
1446 check_key(p_sys_key);
1447
1448 FOR c_card_rec IN c_card LOOP
1449 -- [lmallick]: bug# 12414698
1450 -- Invoke the encryption API only if the card# is numeric, or else
1451 -- the API might thow an exception resulting in abrupt termination
1452 -- of the program.
1453 IF('Y' = IBY_UTILITY_PVT.isNumeric(c_card_rec.ccnumber)) THEN
1454 Compress_CC_Number
1455 (c_card_rec.ccnumber,
1456 c_card_rec.card_number_prefix, c_card_rec.digit_check_flag,
1457 c_card_rec.card_mask_setting, c_card_rec.card_unmask_length,
1458 lx_compress_cc, lx_mask_digits);
1459
1460 IF (NVL(LENGTH(lx_compress_cc),0) > 0) THEN
1461 l_cc_ciphertext :=
1462 HEXTORAW(IBY_SECURITY_PKG.Encode_Number(lx_compress_cc,TRUE));
1463
1464 IBY_SECURITY_PKG.Create_Segment
1465 (FND_API.G_FALSE,l_cc_ciphertext,iby_security_pkg.G_ENCODING_NUMERIC,
1466 p_sys_key,l_segment_id);
1467 ELSE
1468 l_segment_id := -1;
1469 END IF;
1470
1471
1472 --now need to encrypt the other card holder data
1473 --i.e, CHNAME and EXPDATE for now.
1474 IF(l_enc_supl_data = 'Y') THEN
1475 l_encrypted := 'A';
1476 --l_chname_sec_segment_id :=
1477 -- IBY_SECURITY_PKG.encrypt_field_vals(c_card_rec.chname,
1478 -- p_sys_key,
1479 -- null,
1480 -- 'N'
1481 -- );
1482 l_expdate_sec_segment_id :=
1483 IBY_SECURITY_PKG.encrypt_date_field(c_card_rec.expirydate,
1484 p_sys_key,
1485 null,
1486 'N'
1487 );
1488 encrypt_chname(p_sys_key,
1489 c_card_rec.chname,
1490 null,
1491 l_chname_sec_segment_id,
1492 l_masked_chname,
1493 l_chname_mask_setting,
1494 l_chname_unmask_len
1495 );
1496
1497 ELSE
1498 l_encrypted := 'Y';
1499 l_masked_chname := c_card_rec.chname;
1500 l_exp_date := c_card_rec.expirydate;
1501 END IF;
1502
1503 -- Since the expiry dates would also be encrypted, update the
1504 -- expired_flag column for these records.
1505 IF (c_card_rec.expirydate IS NOT NULL) THEN
1506 IF (TRUNC(c_card_rec.expirydate,'DD') < TRUNC(SYSDATE,'DD')) THEN
1507 l_expired_flag := 'Y';
1508 ELSE
1509 l_expired_flag := 'N';
1510 END IF;
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,
1520 chname = l_masked_chname,
1521 chname_sec_segment_id = l_chname_sec_segment_id,
1522 chname_mask_setting = l_chname_mask_setting,
1523 chname_unmask_length = l_chname_unmask_len,
1524 expiry_sec_segment_id = l_expdate_sec_segment_id,
1525 expirydate = l_exp_date,
1526 expired_flag = l_expired_flag,
1527 object_version_number = object_version_number + 1,
1528 last_update_date = sysdate,
1529 last_updated_by = fnd_global.user_id,
1530 last_update_login = fnd_global.login_id
1531 WHERE (instrid = c_card_rec.instrid);
1532 ELSE
1533 iby_debug_pub.add('Could not encrypt the record ' || c_card_rec.instrid,IBY_DEBUG_PUB.G_LEVEL_INFO,l_dbg_mod);
1534 END IF;
1535 END LOOP;
1536
1537 IF FND_API.to_Boolean( p_commit ) THEN
1538 COMMIT;
1539 END IF;
1540
1541 END Encrypt_Instruments;
1542
1543 PROCEDURE Remask_Instruments
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;
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
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,
1569 NVL(r.card_number_length,c.cc_number_length) card_len,
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(+))
1579 AND (c.cc_num_sec_segment_id = seg.sec_segment_id(+))
1580 AND (seg.sec_subkey_id = k.sec_subkey_id(+))
1581 AND ( (NVL(card_unmask_length,-1) <> ci_unmask_len) OR
1582 (NVL(card_mask_setting,' ') <> ci_mask_option)
1583 );
1584 BEGIN
1585
1586 IF (c_card%ISOPEN) THEN CLOSE c_card; END IF;
1587
1588 check_key(p_sys_key);
1589
1590 Get_Mask_Settings(lx_mask_option,lx_unmask_len);
1591
1592 FOR c_card_rec IN c_card(lx_mask_option,lx_unmask_len) LOOP
1593 l_cc_number :=
1594 uncipher_ccnumber
1595 (c_card_rec.ccnumber, c_card_rec.segment_cipher_text,
1596 c_card_rec.encrypted, p_sys_key,
1597 c_card_rec.subkey_cipher_text, c_card_rec.card_len,
1598 c_card_rec.card_number_prefix, c_card_rec.digit_check_flag,
1599 c_card_rec.card_mask_setting, c_card_rec.card_unmask_length,
1600 c_card_rec.unmask_digits
1601 );
1602
1603 l_chname := decrypt_chname(p_sys_key, c_card_rec.instrid);
1604
1605 lx_segment_id := c_card_rec.sec_segment_id;
1606
1607 IF (nvl(c_card_rec.encrypted,'N') <> 'N') THEN
1608
1609 Compress_CC_Number
1610 (l_cc_number,c_card_rec.card_number_prefix,c_card_rec.digit_check_flag,
1611 lx_mask_option,lx_unmask_len,lx_compress_cc,lx_mask_digits);
1612
1613 --
1614 -- masking options may have resulted in no hidden digits; only
1615 -- update if there still exist card digits that are not exposed through
1616 -- the mask or card issuer range
1617 --
1618 IF (NVL(LENGTH(lx_compress_cc),0) > 0) THEN
1619 l_cc_ciphertext :=
1620 HEXTORAW(IBY_SECURITY_PKG.Encode_Number(lx_compress_cc,TRUE));
1621 IF (lx_segment_id IS NULL) THEN
1622 IBY_SECURITY_PKG.Create_Segment
1623 (FND_API.G_FALSE,l_cc_ciphertext,
1624 iby_security_pkg.G_ENCODING_NUMERIC,
1625 p_sys_key,lx_segment_id);
1626 ELSE
1627 IBY_SECURITY_PKG.Update_Segment
1628 (FND_API.G_FALSE,lx_segment_id,l_cc_ciphertext,
1629 iby_security_pkg.G_ENCODING_NUMERIC,
1630 p_sys_key,c_card_rec.subkey_cipher_text);
1631 END IF;
1632 ELSE
1633 DELETE iby_security_segments WHERE (sec_segment_id = lx_segment_id);
1634 END IF;
1635
1636 IF (c_card_rec.encrypted = 'A') THEN
1637 -- Re-encryption of the card holder name will result in encryption
1638 -- with the modified mask settings
1639 Encrypt_Chname
1640 (p_sys_key,
1641 l_chname,
1642 c_card_rec.chname_sec_segment_id,
1643 l_chname_seg_id,
1644 l_masked_chname,
1645 l_chname_mask_setting,
1646 l_chname_unmask_len
1647 );
1648 ELSE -- c_card_rec.encrypted = 'Y'
1649 -- Unlike ccnumber, the chname is masked only when the record is
1650 -- encrypted. When not encrypted, this would store the unmasked value
1651 l_masked_chname := l_chname;
1652 l_chname_seg_id := null;
1653 l_chname_mask_setting := null;
1654 l_chname_unmask_len := null;
1655 END IF;
1656 ELSE
1657 l_masked_chname := l_chname;
1658 l_chname_seg_id := null;
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 =
1668 Mask_Card_Number(l_cc_number,lx_mask_option,lx_unmask_len),
1669 cc_num_sec_segment_id = lx_segment_id,
1670 card_mask_setting = lx_mask_option,
1671 card_unmask_length = lx_unmask_len,
1672 chname = l_masked_chname,
1673 chname_sec_segment_id = l_chname_seg_id,
1674 chname_mask_setting = l_chname_mask_setting,
1675 chname_unmask_length = l_chname_unmask_len,
1676 object_version_number = object_version_number + 1,
1677 last_update_date = sysdate,
1678 last_updated_by = fnd_global.user_id,
1679 last_update_login = fnd_global.login_id
1680 WHERE (instrid = c_card_rec.instrid);
1681 END LOOP;
1682
1683 IF FND_API.to_Boolean( p_commit ) THEN
1684 COMMIT;
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,
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,
1700 p_prefix,
1701 p_digit_check,
1702 p_mask_setting,
1703 p_unmask_len,
1704 x_compress_num,
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,
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,
1722 p_prefix,
1723 p_digit_check,
1724 p_mask_setting,
1725 p_unmask_len,
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
1735 )
1736 RETURN NUMBER
1737 IS
1738 BEGIN
1739 RETURN IBY_SECURITY_PKG.Get_Compressed_Len(p_card_length,
1740 p_prefix,
1741 p_digit_check,
1742 p_mask_setting,
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,
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,
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
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
1782 l_expiry_sec_segment_id NUMBER;
1783 l_chname_sec_Segment_id NUMBER;
1784 --l_chname VARCHAR2(80);
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,
1794 l.state, l.postal_code, l.country,
1795 c.ccnumber, c.expirydate, c.instrument_type, c.purchasecard_flag,
1796 c.purchasecard_subtype, c.card_issuer_code, c.finame,
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(+))
1806 AND (s.location_id = l.location_id(+));
1807
1808 BEGIN
1809
1810 IF( c_creditcard%ISOPEN ) THEN
1811 CLOSE c_creditcard;
1812 END IF;
1813
1814 IF (NOT p_sys_sec_key IS NULL) THEN
1815 iby_security_pkg.validate_sys_key(p_sys_sec_key,l_err_code);
1816 IF (NOT l_err_code IS NULL) THEN
1817 raise_application_error(-20000, l_err_code, FALSE);
1818 END IF;
1819 END IF;
1820
1821 OPEN c_creditcard(p_card_id);
1822 FETCH c_creditcard
1823 INTO x_owner_id, x_holder_name, x_billing_address_id,
1824 x_billing_address1, x_billing_address2, x_billing_address3,
1825 x_billing_city, x_billing_county, x_billing_state, x_billing_zip,
1826 x_billing_country, x_card_number, x_expiry_date, x_instr_type,
1827 x_pcard_flag, x_pcard_type, x_issuer, x_fi_name, x_single_use,
1828 x_info_only, x_purpose, x_desc, x_active_flag, x_inactive_date,
1829 l_encrypted, l_expiry_sec_segment_id,
1830 l_chname_sec_segment_id;
1831
1832 l_instr_found := (NOT c_creditcard%NOTFOUND);
1833 CLOSE c_creditcard;
1834
1835 IF (NOT l_instr_found) THEN
1836 raise_application_error(-20000,'IBY_20512', FALSE);
1837 END IF;
1838
1839 -- unencrypt/unencode instrument data
1840 --
1841 x_card_number := uncipher_ccnumber(p_card_id,p_sys_sec_key);
1842
1843 -- unencrypt card holder data if its encrypted
1844 IF (nvl(l_encrypted, 'N') = 'A'
1845 AND Other_CC_Attribs_Encrypted = 'Y')
1846 THEN
1847 IF (l_expiry_sec_segment_id IS NOT NULL) THEN
1848 x_expiry_date := IBY_SECURITY_PKG.decrypt_date_field
1849 (l_expiry_sec_segment_id,
1850 p_sys_sec_key
1851 );
1852 END IF;
1853
1854 IF(l_chname_sec_segment_id IS NOT NULL) THEN
1855 x_holder_name := decrypt_chname(p_sys_sec_key, p_card_id);
1856
1857 END IF;
1858 END IF;
1859
1860 END Query_Card;
1861
1862
1863 /*
1864 Bug 9735830: CANADIAN ORDER FAILING CC AUTH WITH INVALID/MISSING BILL STATE
1865 Adding an overloaded procedure for Query_Card.
1866 This will return an additional o/p parameter for the province.
1867 Province is needed for the authorization of canadian address and will be
1868 retrieved from hz_locations table.
1869 Bug 13003193:BILLING ADDRESS VALIDATION FAILURE FOR STATE CODE ON CANADA CUSTOMERS
1870 The state is loaded with state/province as provided as the payment system
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,
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,
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,
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
1911 l_expiry_sec_segment_id NUMBER;
1912 l_chname_sec_Segment_id NUMBER;
1913 --l_chname VARCHAR2(80);
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,
1923 nvl(l.state,l.province), l.postal_code, l.country,
1924 c.ccnumber, c.expirydate, c.instrument_type, c.purchasecard_flag,
1925 c.purchasecard_subtype, c.card_issuer_code, c.finame,
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(+))
1935 AND (s.location_id = l.location_id(+));
1936
1937 BEGIN
1938
1939 IF( c_creditcard%ISOPEN ) THEN
1940 CLOSE c_creditcard;
1941 END IF;
1942
1943 IF (NOT p_sys_sec_key IS NULL) THEN
1944 iby_security_pkg.validate_sys_key(p_sys_sec_key,l_err_code);
1945 IF (NOT l_err_code IS NULL) THEN
1946 raise_application_error(-20000, l_err_code, FALSE);
1947 END IF;
1948 END IF;
1949
1950 OPEN c_creditcard(p_card_id);
1951 FETCH c_creditcard
1952 INTO x_owner_id, x_holder_name, x_billing_address_id,
1953 x_billing_address1, x_billing_address2, x_billing_address3,
1954 x_billing_city, x_billing_county, x_billing_state, x_billing_zip,
1955 x_billing_country, x_card_number, x_expiry_date, x_instr_type,
1956 x_pcard_flag, x_pcard_type, x_issuer, x_fi_name, x_single_use,
1957 x_info_only, x_purpose, x_desc, x_active_flag, x_inactive_date,
1958 l_encrypted, l_expiry_sec_segment_id,
1959 l_chname_sec_segment_id, x_province;
1960
1961 l_instr_found := (NOT c_creditcard%NOTFOUND);
1962 CLOSE c_creditcard;
1963
1964 IF (NOT l_instr_found) THEN
1965 raise_application_error(-20000,'IBY_20512', FALSE);
1966 END IF;
1967
1968 -- unencrypt/unencode instrument data
1969 --
1970 x_card_number := uncipher_ccnumber(p_card_id,p_sys_sec_key);
1971
1972 -- unencrypt card holder data if its encrypted
1973 IF (nvl(l_encrypted, 'N') = 'A'
1974 AND Other_CC_Attribs_Encrypted = 'Y')
1975 THEN
1976 IF (l_expiry_sec_segment_id IS NOT NULL) THEN
1977 x_expiry_date := IBY_SECURITY_PKG.decrypt_date_field
1978 (l_expiry_sec_segment_id,
1979 p_sys_sec_key
1980 );
1981 END IF;
1982
1983 IF(l_chname_sec_segment_id IS NOT NULL) THEN
1984 x_holder_name := decrypt_chname(p_sys_sec_key, p_card_id);
1985
1986 END IF;
1987 END IF;
1988
1989 END Query_Card;
1990
1991 PROCEDURE Encrypt_Card_Info
1992 (p_commit IN VARCHAR2 := FND_API.G_TRUE,
1993 p_sys_security_key IN iby_security_pkg.DES3_KEY_TYPE,
1994 p_expiry_date IN DATE,
1995 p_expSegmentId IN NUMBER,
1996 p_chname IN VARCHAR2,
1997 p_chnameSegmentId IN NUMBER,
1998 p_chnameMaskSetting IN VARCHAR2,
1999 p_chnameUnmaskLen IN NUMBER,
2000 x_exp_segment_id OUT NOCOPY NUMBER,
2001 x_masked_chname OUT NOCOPY VARCHAR2,
2002 x_chname_segment_id OUT NOCOPY NUMBER,
2003 x_chnameMaskSetting OUT NOCOPY VARCHAR2,
2004 x_chnameUnmaskLen OUT NOCOPY NUMBER,
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;
2014 IF (l_mode = iby_security_pkg.G_ENCRYPT_MODE_NONE) THEN
2015 RETURN;
2016 END IF;
2017 iby_security_pkg.validate_sys_key(p_sys_security_key,x_err_code);
2018 -- test_debug('sysKey valid.. ');
2019 IF (NOT x_err_code IS NULL) THEN
2020 RETURN;
2021 END IF;
2022
2023 IF (NOT p_expiry_date IS NULL) THEN
2024 x_exp_segment_id := IBY_SECURITY_PKG.encrypt_date_field(p_expiry_date,
2025 p_sys_security_key,
2026 p_expSegmentId,
2027 'N'
2028 );
2029 END IF;
2030
2031 IF ((NOT p_chname IS NULL) AND (INSTR(p_chname, '*') = 0)) THEN
2032
2033 -- the chname_sec_segment_id could be -1. In that case, pass null
2034 -- value for the segment id to the encryption API so that a new segment
2035 -- is created(if required) for the chname
2036 IF (p_chnameSegmentId = -1) THEN
2037 l_chnameSegmentId := null;
2038 ELSE
2039 l_chnameSegmentId := p_chnameSegmentId;
2040 END IF;
2041
2042 encrypt_chname
2043 (p_sys_security_key,
2044 p_chname,
2045 l_chnameSegmentId,
2046 x_chname_segment_id,
2047 x_masked_chname,
2048 x_chnameMaskSetting,
2049 x_chnameUnmaskLen
2050 );
2051 ELSE
2052 x_masked_chname := null;
2053 x_chname_segment_id := p_chnameSegmentId;
2054 x_chnameMaskSetting := p_chnameMaskSetting;
2055 x_chnameUnmaskLen := p_chnameUnmaskLen;
2056 END IF;
2057
2058 IF FND_API.To_Boolean(p_commit) THEN
2059 COMMIT;
2060 END IF;
2061
2062
2063 END Encrypt_Card_Info;
2064
2065 PROCEDURE Mark_Expired_Cards
2066 (p_commit IN VARCHAR2 := FND_API.G_TRUE,
2067 p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE
2068 )
2069 IS
2070 l_expiry_date DATE;
2071 l_expired_flag VARCHAR2(1);
2072
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
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;
2088 --check_key(p_sys_sec_key);
2089
2090 FOR c_card_rec IN c_card LOOP
2091 IF(c_card_rec.expiry_sec_segment_id IS NOT NULL) THEN
2092 -- Verify the syskey only if there is atleast one
2093 -- encrypted record.
2094 -- Also we require the syskey to be checked only once
2095 IF(syskey_checked = 'N') THEN
2096 iby_debug_pub.add('At least one encrypted record. Verifying syskey..',
2097 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2098 check_key(p_sys_sec_key);
2099 syskey_checked := 'Y';
2100 END IF;
2101
2102 l_expiry_date := IBY_SECURITY_PKG.decrypt_date_field
2103 (c_card_rec.expiry_sec_segment_id,
2104 p_sys_sec_key
2105 );
2106 ELSE
2107 l_expiry_date := c_card_rec.expirydate;
2108 END IF;
2109
2110 -- expirydate could be null for some records. Lets keep the
2111 -- expired_flag as NULL in such cases.
2112 IF(l_expiry_date IS NULL) THEN
2113 l_expired_flag := NULL;
2114 ELSE
2115 IF (TRUNC(l_expiry_date,'DD') < TRUNC(SYSDATE,'DD')) THEN
2116 l_expired_flag := 'Y';
2117 ELSE
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
2127 cnt := cnt + 1;
2128
2129 END LOOP;
2130 iby_debug_pub.add('No. of records updated = '||cnt,
2131 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2132
2133 IF FND_API.to_Boolean( p_commit ) THEN
2134 COMMIT;
2135 END IF;
2136
2137 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
2138
2139 END Mark_Expired_Cards;
2140
2141 PROCEDURE Upgrade_Encrypted_Instruments
2142 (p_commit IN VARCHAR2 := FND_API.G_TRUE,
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);
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
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)));
2169 BEGIN
2170 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
2171
2172 --No point in running this program when the system security setup doesn't allow
2173 -- this.
2174 IF(Other_CC_Attribs_Encrypted = 'N') THEN
2175 iby_debug_pub.add('The system security options do not allow data to be upgraded. Aborting..'
2176 ,iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
2177 RETURN;
2178 END IF;
2179 IF (c_card%ISOPEN) THEN CLOSE c_card; END IF;
2180 check_key(p_sys_sec_key);
2181 no_cc := 0;
2182 FOR c_card_rec IN c_card LOOP
2183 IF(c_card_rec.expirydate IS NOT NULL) THEN
2184 l_exp_segment_id := IBY_SECURITY_PKG.encrypt_date_field(c_card_rec.expirydate,
2185 p_sys_sec_key,
2186 null,
2187 'N'
2188 );
2189 IF (TRUNC(c_card_rec.expirydate,'DD') < TRUNC(SYSDATE,'DD')) THEN
2190 l_expired_flag := 'Y';
2191 ELSE
2192 l_expired_flag := 'N';
2193 END IF;
2194 END IF;
2195
2196 IF((c_card_rec.chname IS NOT NULL) AND (c_card_rec.chname_sec_segment_id IS NULL)) THEN
2197 encrypt_chname
2198 (p_sys_sec_key,
2199 c_card_rec.chname,
2200 null,
2201 l_chname_segment_id,
2202 l_masked_chname,
2203 l_chname_mask_setting,
2204 l_chname_unmask_len
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,
2214 chname_mask_setting = l_chname_mask_setting,
2215 chname_unmask_length = l_chname_unmask_len,
2216 expirydate = null,
2217 expiry_sec_segment_id = l_exp_segment_id,
2218 expired_flag = l_expired_flag,
2219 object_version_number = object_version_number + 1,
2220 last_update_date = sysdate,
2221 last_updated_by = fnd_global.user_id,
2222 last_update_login = fnd_global.login_id
2223 WHERE (instrid = c_card_rec.instrid);
2224 no_cc := no_cc + 1;
2225 -- flush the variables before iterating into the next record
2226 l_masked_chname := null;
2227 l_chname_segment_id := null;
2228 l_chname_mask_setting := null;
2229 l_chname_unmask_len := null;
2230 l_exp_segment_id := null;
2231 l_expired_flag := null;
2232
2233 END LOOP;
2234 iby_debug_pub.add('No. of records updated = '|| no_cc,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2235 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
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 )
2245 IS
2246 l_exp_sec_segment_id NUMBER;
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;
2256 END IF;
2257
2258 IF (l_exp_sec_segment_id IS NOT NULL) THEN
2259 check_key(p_sys_sec_key);
2260 l_expiry_date := IBY_SECURITY_PKG.decrypt_date_field
2261 (l_exp_sec_segment_id,
2262 p_sys_sec_key
2263 );
2264 END IF;
2265
2266 IF (TRUNC(l_expiry_date,'DD') < TRUNC(p_input_date,'DD')) THEN
2267 x_expired := 'Y';
2268 ELSE
2269 x_expired := 'N';
2270 END IF;
2271
2272 END Check_CC_Expiry;
2273
2274 PROCEDURE Upgrade_Risky_Instruments
2275 (
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
2285 --variables to store the creditcard number hash values
2286 l_cc_hash1 iby_irf_risky_instr.cc_number_hash1%TYPE;
2287 l_cc_hash2 iby_irf_risky_instr.cc_number_hash2%TYPE;
2288
2289 --variables to store the account number hash values
2290 l_acct_no_hash1 iby_irf_risky_instr.acct_number_hash1%TYPE;
2291 l_acct_no_hash2 iby_irf_risky_instr.acct_number_hash2%TYPE;
2292
2293 --Variables to store the number of cerditcard numbers
2294 --or account numbers that are updated
2295 no_cc NUMBER;
2296 no_acct NUMBER;
2297
2298 -- Cursor will fetch all the records from IBY_IRF_RISKY_INSTR
2299 -- and lock them for UPDATE
2300
2301 CURSOR get_risky_instruments IS
2302 SELECT payeeid, instrtype,
2303 account_no, creditcard_no,
2304 cc_number_hash1, cc_number_hash2,
2305 acct_number_hash1, acct_number_hash2,
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;
2315 no_acct := 0;
2316 --Get each risky instrument
2317 FOR risky_instr_rec IN get_risky_instruments
2318 LOOP
2319 --After the PABP fixes, the creditcard column is
2320 --supposed to be NULL. If this is not null, then
2321 --select this one for upgrade.
2322 --
2323 --NOTE: If later on, we decide to store a masked
2324 --card number in creditcard_no column, then the
2325 --script should be modified accordingly.
2326 IF (risky_instr_rec.creditcard_no IS NOT NULL)
2327 THEN
2328 IBY_CC_VALIDATE.StripCC
2329 (1.0, FND_API.G_FALSE, risky_instr_rec.creditcard_no,
2330 IBY_CC_VALIDATE.c_FillerChars, lx_return_status,
2331 lx_msg_count, lx_msg_data, lx_cc_number
2332 );
2333 -- Get hash values of the credit number
2334 l_cc_hash1 := iby_security_pkg.get_hash
2335 (lx_cc_number,FND_API.G_FALSE);
2336 l_cc_hash2 := iby_security_pkg.get_hash
2337 (lx_cc_number,FND_API.G_TRUE);
2338
2339 UPDATE iby_irf_risky_instr
2340 SET creditcard_no = NULL,
2341 cc_number_hash1 = l_cc_hash1,
2342 cc_number_hash2 = l_cc_hash2,
2343 object_version_number = risky_instr_rec.object_version_number + 1,
2344 last_update_date = SYSDATE
2345 WHERE payeeid = risky_instr_rec.payeeid
2346 AND instrtype = 'CREDITCARD'
2347 AND creditcard_no = risky_instr_rec.creditcard_no;
2348 --update the counter by 1
2349 no_cc := no_cc + 1;
2350
2351 ELSIF (risky_instr_rec.account_no IS NOT NULL) THEN
2352 -- Get the hash values of the account number
2353 l_acct_no_hash1 := iby_security_pkg.get_hash
2354 (risky_instr_rec.account_no,FND_API.G_FALSE);
2355 l_acct_no_hash2 := iby_security_pkg.get_hash
2356 (risky_instr_rec.account_no,FND_API.G_TRUE);
2357
2358 UPDATE iby_irf_risky_instr
2359 SET account_no = NULL,
2360 acct_number_hash1 = l_acct_no_hash1,
2361 acct_number_hash2 = l_acct_no_hash2,
2362 object_version_number = risky_instr_rec.object_version_number + 1,
2363 last_update_date = SYSDATE
2364 WHERE payeeid = risky_instr_rec.payeeid
2365 AND instrtype = 'BANKACCOUNT'
2366 AND account_no = risky_instr_rec.account_no;
2367 --update the counter by 1
2368 no_acct := no_acct + 1;
2369
2370 END IF;
2371
2372 END LOOP;
2373 -- DBMS_OUTPUT.PUT_LINE('complete..: ');
2374 -- DBMS_OUTPUT.PUT_LINE('No. of cards updated: '|| no_cc);
2375 -- DBMS_OUTPUT.PUT_LINE('No. of accounts updated: '|| no_acct);
2376 fnd_file.put_line(fnd_file.log,l_dbg_mod||': No. of cards updated: '|| no_cc);
2377 fnd_file.put_line(fnd_file.log,l_dbg_mod||': No. of accounts updated: '|| no_acct);
2378 IF FND_API.to_Boolean( p_commit ) THEN
2379 COMMIT;
2380 END IF;
2381 fnd_file.put_line(fnd_file.log,l_dbg_mod||': Exit.');
2382 EXCEPTION
2383 WHEN others THEN
2384 -- DBMS_OUTPUT.PUT_LINE('SQLCODE is: ' || SQLCODE);
2385 -- DBMS_OUTPUT.PUT_LINE('SQLERRM is: ' || sqlerrm);
2386 ROLLBACK;
2387 fnd_file.put_line(fnd_file.log,l_dbg_mod||': Exception thrown: '|| SQLERRM);
2388 END Upgrade_Risky_Instruments;
2389
2390 PROCEDURE Purge_Sensitive_Data
2391 (x_errbuf OUT NOCOPY VARCHAR2,
2392 x_retcode OUT NOCOPY VARCHAR2,
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;
2402 l_phase VARCHAR2(30);
2403 l_status VARCHAR2(30);
2404 l_dev_phase VARCHAR2(30);
2405 l_dev_status VARCHAR2(30);
2406 l_message VARCHAR2(500);
2407 BEGIN
2408 fnd_file.put_line(fnd_file.log,l_dbg_mod||': Enter.');
2409 req_id := fnd_global.CONC_REQUEST_ID;
2410 BEGIN
2411 fnd_file.put_line(fnd_file.log,l_dbg_mod||': Invoking APXCCUPGMGR. Timestamp: '|| TO_CHAR(systimestamp,'SSSSS.FF'));
2412 BEGIN
2413 x_req_id := fnd_request.submit_request(
2414 APPLICATION=>'SQLAP',
2415 PROGRAM=>'APXCCUPGMGR',
2416 DESCRIPTION=>null,
2417 SUB_REQUEST=>FALSE,
2418 ARGUMENT1=>x_batch_size,
2419 ARGUMENT2=>x_num_workers,
2420 ARGUMENT3=>req_id);
2421
2422 -- OIE has the requirement that the 2nd CP(APXCCUPGMGR) be triggered only after
2423 -- the 1st CP (APXCCUPGMGR) completes.
2424 -- Need to commit before waiting for the CP completion
2425 COMMIT;
2426 END;
2427 fnd_file.put_line(fnd_file.log,l_dbg_mod||': Request_id of the APXCCUPGMGR instance = '||x_req_id||'. Timestamp: '|| TO_CHAR(systimestamp,'SSSSS.FF'));
2428 l_call_status := fnd_concurrent.wait_for_request(x_req_id,60,0,l_phase,l_status,l_dev_phase,l_dev_status,l_message);
2429
2430 fnd_file.put_line(fnd_file.log,l_dbg_mod||': Invoking APXCCTRXUPG. Timestamp: '|| TO_CHAR(systimestamp,'SSSSS.FF'));
2431 BEGIN
2432 x_req_id := fnd_request.submit_request(
2433 APPLICATION=>'SQLAP',
2434 PROGRAM=>'APXCCTRXUPGMGR',
2435 DESCRIPTION=>null,
2436 SUB_REQUEST=>FALSE,
2437 ARGUMENT1=>x_batch_size,
2438 ARGUMENT2=>x_num_workers,
2439 ARGUMENT3=>req_id);
2440 END;
2441 fnd_file.put_line(fnd_file.log,l_dbg_mod||': Request_id of the APXCCTRXUPGMGR instance = '||x_req_id||'. Timestamp: '|| TO_CHAR(systimestamp,'SSSSS.FF'));
2442
2443 EXCEPTION
2444 WHEN OTHERS THEN
2445 fnd_file.put_line(fnd_file.log,l_dbg_mod||': Exception thrown during OIE module execution! '|| SQLERRM);
2446 END;
2447
2448 -- Invoke the AP Upgrade routine
2449 -- Signature of the AP routine is as follows
2450 -- AP_CARDS_PKG.UPG_HISTORICAL_TRANSACTIONS
2451 -- (errbuf => x_errbuf,
2452 -- retcode => x_retcode
2453 -- );
2454 BEGIN
2455 fnd_file.put_line(fnd_file.log,l_dbg_mod||': Invoking AP_CARDS_PKG.UPG_HISTORICAL_TRANSACTIONS. Timestamp: '||TO_CHAR(systimestamp,'SSSSS.FF'));
2456 plsql_block := 'CALL AP_CARDS_PKG.UPG_HISTORICAL_TRANSACTIONS(:1,:2)';
2457 EXECUTE IMMEDIATE plsql_block USING OUT x_errbuf, OUT x_retcode;
2458 fnd_file.put_line(fnd_file.log,l_dbg_mod||': Return status frm AP_CARDS_PKG.UPG_HISTORICAL_TRANSACTIONS: '||x_retcode||'. Timestamp: '||TO_CHAR(systimestamp,'SSSSS.FF'));
2459 EXCEPTION
2460 WHEN OTHERS THEN
2461 fnd_file.put_line(fnd_file.log,l_dbg_mod||': Exception thrown during AP_CARDS_PKG.UPG_HISTORICAL_TRANSACTIONS invocation. '|| SQLERRM);
2462 END;
2463
2464 -- Invoke ASO Upgrade routine
2465 -- Signature of the AP routine is as follows
2466 -- ASO_PAYMENT_INT.PURGE_ASO_PAYMENTS_DATA
2467 BEGIN
2468 fnd_file.put_line(fnd_file.log,l_dbg_mod||': Invoking ASO_PAYMENT_INT.PURGE_ASO_PAYMENTS_DATA. Timestamp: '||TO_CHAR(systimestamp,'SSSSS.FF'));
2469 plsql_block := 'CALL ASO_PAYMENT_INT.PURGE_ASO_PAYMENTS_DATA()';
2470 EXECUTE IMMEDIATE plsql_block;
2471 fnd_file.put_line(fnd_file.log,l_dbg_mod||': Return status frm ASO_PAYMENT_INT.PURGE_ASO_PAYMENTS_DATA: '||x_retcode||'. Timestamp: '||TO_CHAR(systimestamp,'SSSSS.FF'));
2472 EXCEPTION
2473 WHEN OTHERS THEN
2474 fnd_file.put_line(fnd_file.log,l_dbg_mod||': Exception thrown during ASO_PAYMENT_INT.PURGE_ASO_PAYMENTS_DATA invocation. '|| SQLERRM);
2475 END;
2476
2477 -- Upgrade the data in IBY_IRF_RISKY_INSTR
2478 fnd_file.put_line(fnd_file.log,l_dbg_mod||': Upgrading Data in IBY_IRF_RISKY_INSTR. Timestamp: '||TO_CHAR(systimestamp,'SSSSS.FF'));
2479 Upgrade_Risky_Instruments('T');
2480 fnd_file.put_line(fnd_file.log,l_dbg_mod||': Finshed Upgrading Data in IBY_IRF_RISKY_INSTR. Timestamp: '||TO_CHAR(systimestamp,'SSSSS.FF'));
2481 fnd_file.put_line(fnd_file.log,l_dbg_mod||': Exit.');
2482
2483
2484 END Purge_Sensitive_Data;
2485
2486 END iby_creditcard_pkg;