1 PACKAGE BODY IBY_CC_VALIDATE AS
2 /* $Header: ibyccvlb.pls 120.5.12010000.6 2009/06/16 09:53:20 lyanamal ship $ */
3
4
5 -- *** Declaring global datatypes and variables ***
6
7
8 FUNCTION CheckCCDigits( p_cc_id IN VARCHAR2 ) RETURN NUMBER
9 AS
10
11 v_DigitSum INTEGER :=0;
12 v_CCDigit INTEGER :=0;
13 v_CCLen INTEGER :=0;
14 BEGIN
15 v_CCLen:=LENGTH(p_cc_id);
16
17 FOR v_Counter IN 1..v_CCLen LOOP
18
19 v_CCDigit := TO_NUMBER(SUBSTR(p_cc_id,v_CCLen-v_Counter+1,1));
20
21 -- every alternate digit beginning with the second one from the
22 -- right must be doubled and the resultant digits added together
23 --
24 IF MOD(v_Counter,2)=0 THEN
25 --
26 -- according to the algorithm, resulting digits must be
27 -- added together; only an issue for #'s >=5
28 IF v_CCDigit<5 THEN
29 v_CCDigit := v_CCDigit*2;
30 ELSE
31 -- this function just happens to fit the algorithm
32 -- "double x and then add its digits together" for
33 -- all x >=5 and <=9
34 --
35 v_CCDigit := 1+2*(v_CCDigit-5);
36 END IF;
37 END IF;
38 --DBMS_OUTPUT.PUT_LINE('digit value is : ' || v_CCDigit);
39 v_DigitSum := v_DigitSum + v_CCDigit;
40
41 END LOOP;
42
43 --DBMS_OUTPUT.PUT_LINE('digit sum is : ' || v_DigitSum);
44
45 RETURN MOD(v_DigitSum,10);
46
47 EXCEPTION
48 -- WHEN VALUE_ERROR THEN
49 WHEN OTHERS THEN
50 RETURN NULL;
51 END CheckCCDigits;
52
53 /*
54 ** This function returns the CC Type in String for the CC Type in
55 ** number passed. It maps the constant defined for CC Type in this
56 ** Package to ones in the LOOKUP Types for the type 'IBY_CARD_TYPES'.
57 */
58
59 FUNCTION getLookupCCType( p_cc_type IN NUMBER )
60 RETURN VARCHAR2
61 AS
62
63 BEGIN
64
65 IF( p_cc_type = 0 ) THEN
66 RETURN 'UNKNOWN';
67 ELSIF( p_cc_type = 1 ) THEN
68 RETURN 'MASTERCARD';
69 ELSIF( p_cc_type = 2 ) THEN
70 RETURN 'VISA';
71 ELSIF( p_cc_type = 3 ) THEN
72 RETURN 'AMEX';
73 ELSIF( p_cc_type = 4 ) THEN
74 RETURN 'DINERS';
75 ELSIF( p_cc_type = 5 ) THEN
76 RETURN 'DISCOVER';
77 ELSIF( p_cc_type = 6 ) THEN
78 RETURN 'ENROUTE';
79 ELSIF( p_cc_type = 7 ) THEN
80 RETURN 'JCB';
81 ELSE
82 RETURN NULL;
83 END IF;
84
85 EXCEPTION
86 WHEN OTHERS THEN
87 RETURN NULL;
88 END getLookupCCType;
89
90
91 FUNCTION getIssuerCCType( p_cc_issuer IN VARCHAR2 )
92 RETURN VARCHAR2
93 AS
94
95 BEGIN
96
97 IF( p_cc_issuer = 'UNKNOWN' ) THEN
98 RETURN 0;
99 ELSIF( p_cc_issuer = 'INVALID' ) THEN
100 RETURN c_InvalidCC;
101 ELSIF( p_cc_issuer = 'MASTERCARD' ) THEN
102 RETURN 1;
103 ELSIF( p_cc_issuer = 'VISA' ) THEN
104 RETURN 2;
105 ELSIF( p_cc_issuer = 'AMEX' ) THEN
106 RETURN 3;
107 ELSIF( p_cc_issuer = 'DINERS' ) THEN
108 RETURN 4;
109 ELSIF( p_cc_issuer = 'DISCOVER' ) THEN
110 RETURN 5;
111 ELSIF( p_cc_issuer = 'ENROUTE' ) THEN
112 RETURN 6;
113 ELSIF( p_cc_issuer = 'JCB' ) THEN
114 RETURN 7;
115 ELSE
116 RETURN NULL;
117 END IF;
118
119 END getIssuerCCType;
120
121 PROCEDURE StripCC ( p_api_version IN NUMBER,
122 p_init_msg_list IN VARCHAR2,
123 p_cc_id IN VARCHAR2,
124 p_fill_chars IN VARCHAR2,
125
126 x_return_status OUT NOCOPY VARCHAR2,
127 x_msg_count OUT NOCOPY NUMBER,
128 x_msg_data OUT NOCOPY VARCHAR2,
129 x_cc_id OUT NOCOPY VARCHAR2
130 ) AS
131
132 c_Digits CONSTANT VARCHAR2(10) :='0123456789';
133 v_StrippedCC VARCHAR2(100) :='';
134 v_CCChar CHAR;
135 v_FoundBadChar BOOLEAN :=FALSE;
136 BEGIN
137 FOR v_Counter IN 1..LENGTH(p_cc_id) LOOP
138 --
139 -- tests if a character is a digit
140 --
141 v_CCChar := SUBSTR(p_cc_id,v_Counter,1);
142
143 IF INSTR(c_Digits,v_CCChar)>0 THEN
144 v_StrippedCC := v_StrippedCC || v_CCChar;
145 ELSIF INSTR(p_fill_chars,v_CCChar)>0 THEN
146 NULL;
147 ELSE
148 -- an illegal character found in the string
149 --
150 v_FoundBadChar:=TRUE;
151 --DBMS_OUTPUT.PUT_LINE('bad char: ' || v_CCChar);
152 EXIT;
153 END IF;
154 END LOOP;
155
156 IF v_FoundBadChar THEN
157 -- !!
158 -- do something useful here!!
159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
160 x_cc_id := NULL;
161 ELSE
162 x_return_status := FND_API.G_RET_STS_SUCCESS;
163 x_cc_id := v_StrippedCC;
164 END IF;
165
166 --DBMS_OUTPUT.PUT_LINE('stripped value is: ' || v_StrippedCC);
167
168 EXCEPTION
169 WHEN OTHERS THEN
170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
171 x_cc_id := NULL;
172 END StripCC;
173
174 PROCEDURE StripCC ( p_api_version IN NUMBER,
175 p_init_msg_list IN VARCHAR2,
176 p_cc_id IN VARCHAR2,
177
178 x_return_status OUT NOCOPY VARCHAR2,
179 x_msg_count OUT NOCOPY NUMBER,
180 x_msg_data OUT NOCOPY VARCHAR2,
181 x_cc_id OUT NOCOPY VARCHAR2
182 ) AS
183
184 BEGIN
185
186 StripCC(p_api_version, p_init_msg_list, p_cc_id,
187 c_FillerChars, x_return_status, x_msg_count,
188 x_msg_data, x_cc_id );
189
190 END StripCC;
191
192 FUNCTION StripCC ( p_cc_id IN VARCHAR2, p_fill_chars IN VARCHAR2 )
193 RETURN VARCHAR2
194 IS
195 c_Digits CONSTANT VARCHAR2(10) :='0123456789';
196 v_StrippedCC VARCHAR2(100) := '';
197 v_CCChar CHAR;
198 v_FoundBadChar BOOLEAN :=FALSE;
199 l_DBUG_MOD VARCHAR2(100) :='IBY_CC_VALIDATE.Get_CC_Issuer_Range(6 params)';
200 BEGIN
201
202 iby_debug_pub.add('Enter',FND_LOG.LEVEL_STATEMENT,l_DBUG_MOD);
203
204 FOR v_Counter IN 1..LENGTH(p_cc_id) LOOP
205 --
206 -- tests if a character is a digit
207 --
208 v_CCChar := SUBSTR(p_cc_id,v_Counter,1);
209
210
211 IF INSTR(c_Digits,v_CCChar)>0 THEN
212 v_StrippedCC := v_StrippedCC || v_CCChar;
213 ELSIF INSTR(p_fill_chars,v_CCChar)>0 THEN
214 NULL;
215 ELSE
216 v_FoundBadChar:=TRUE;
217 EXIT;
218 END IF;
219 END LOOP;
220
221 IF v_FoundBadChar THEN
222 iby_debug_pub.add('Found bad char',FND_LOG.LEVEL_STATEMENT,l_DBUG_MOD);
223
224 RETURN NULL;
225 ELSE
226 RETURN v_StrippedCC;
227 END IF;
228 iby_debug_pub.add('Exit',FND_LOG.LEVEL_STATEMENT,l_DBUG_MOD);
229
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 iby_debug_pub.add('In Others Exception',FND_LOG.LEVEL_UNEXPECTED, l_DBUG_MOD);
234 iby_debug_pub.add('Exception code='||SQLCODE,FND_LOG.LEVEL_UNEXPECTED,l_DBUG_MOD);
235 iby_debug_pub.add('Exception message='||SQLERRM,FND_LOG.LEVEL_UNEXPECTED,l_DBUG_MOD);
236
237
238 END StripCC;
239
240
241 PROCEDURE GetCCType ( p_api_version IN NUMBER,
242 p_init_msg_list IN VARCHAR2,
243 p_cc_id IN VARCHAR2,
244
245 x_return_status OUT NOCOPY VARCHAR2,
246 x_msg_count OUT NOCOPY NUMBER,
247 x_msg_data OUT NOCOPY VARCHAR2,
248 x_cc_type OUT NOCOPY CCType
249 ) AS
250
251 v_Length INTEGER;
252 v_DigitsOk BOOLEAN;
253 lx_card_issuer iby_creditcard_issuers_b.card_issuer_code%TYPE;
254 lx_range_id iby_cc_issuer_ranges.cc_issuer_range_id%TYPE;
255 lx_card_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE;
256 lx_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE;
257
258 BEGIN
259 x_return_status := FND_API.G_RET_STS_SUCCESS;
260
261 IF (p_cc_id IS NULL) THEN
262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
263 x_cc_type:=c_InvalidCC;
264 RETURN;
265 END IF;
266
267 v_Length:=LENGTH(p_cc_id);
268
269 IF (v_Length <=0) THEN
270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
271 x_cc_type:=c_InvalidCC;
272 RETURN;
273 END IF;
274
275 Get_CC_Issuer_Range
276 (p_cc_id,lx_card_issuer,lx_range_id,lx_card_prefix,lx_digit_check);
277
278 IF (lx_digit_check = 'Y') THEN
279 v_DigitsOk:= CheckCCDigits(p_cc_id) = 0;
280 ELSE
281 v_DigitsOk:= TRUE;
282 END IF;
283
284 -- this means there were some non-digit characters
285 -- in the credit card number
286 --
287 IF StripCC(p_cc_id,c_FillerChars) IS NULL THEN
288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
289 x_cc_type:=c_InvalidCC;
290 RETURN;
291 END IF;
292
293 IF NOT v_DigitsOk THEN
294 x_cc_type:=c_InvalidCC;
295 ELSE
296 x_cc_type := getIssuerCCType(NVL(lx_card_issuer,'UNKNOWN'));
297 END IF;
298
299 EXCEPTION
300 WHEN OTHERS THEN
301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
302 x_cc_type:=c_InvalidCC;
303 END GetCCType;
304
305
306 /*
307 ** This is an overloaded function which returns, a boolean value -
308 ** indicating whether the card is valid or not, AND CC type (in VARCHAR2)
309 ** from the lookup types defined for type 'IBY_CARD_TYPES'
310 */
311
312 PROCEDURE ValidateCC (p_api_version IN NUMBER,
313 p_init_msg_list IN VARCHAR2,
314 p_cc_id IN VARCHAR2,
315 p_expr_date IN DATE,
316
317 x_return_status OUT NOCOPY VARCHAR2,
318 x_msg_count OUT NOCOPY NUMBER,
319 x_msg_data OUT NOCOPY VARCHAR2,
320 x_cc_valid OUT NOCOPY BOOLEAN,
321 x_cc_type OUT NOCOPY VARCHAR2
322 ) AS
323
324 v_CurrDate DATE := SYSDATE();
325 v_CardType CCType;
326 v_spread REAL;
327 BEGIN
328
329 -- expr date is moved to the last day of the month it's on as
330 -- most credit cards
331 --
332 v_spread:=MONTHS_BETWEEN(LAST_DAY(TRUNC(p_expr_date)),
333 TRUNC(v_CurrDate));
334 x_return_status := FND_API.G_RET_STS_SUCCESS;
335
336 --DBMS_OUTPUT.PUT_LINE('Difference in time: ' || v_spread);
337 IF v_spread >= 0 THEN
338
339 GetCCType(p_api_version, p_init_msg_list, p_cc_id,
340 x_return_status, x_msg_count, x_msg_data,
341 v_CardType );
342 x_cc_valid:=(v_CardType<>c_InvalidCC);
343 x_cc_type := getLookupCCType( v_CardType );
344
345 /* set the other out variables here */
346 ELSE
347
348 x_cc_valid:=FALSE;
349
350 /* set the other out variables here */
351 END IF;
352
353 EXCEPTION
354 WHEN OTHERS THEN
355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
356 x_cc_valid:=FALSE;
357 END ValidateCC;
358
359
360 /*
361 ** This is an overloaded function which returns, a boolean value -
362 ** indicating whether the card is valid or not.
363 */
364
365 PROCEDURE ValidateCC (p_api_version IN NUMBER,
366 p_init_msg_list IN VARCHAR2,
367 p_cc_id IN VARCHAR2,
368 p_expr_date IN DATE,
369
370 x_return_status OUT NOCOPY VARCHAR2,
371 x_msg_count OUT NOCOPY NUMBER,
372 x_msg_data OUT NOCOPY VARCHAR2,
373 x_cc_valid OUT NOCOPY BOOLEAN
374 ) AS
375
376 v_CC_Type VARCHAR2(80);
377
378 BEGIN
379
380 ValidateCC (p_api_version => p_api_version,
381 p_init_msg_list => p_init_msg_list,
382 p_cc_id => p_cc_id,
383 p_expr_date => p_expr_date,
384 x_return_status => x_return_status,
385 x_msg_count => x_msg_count,
386 x_msg_data => x_msg_data,
387 x_cc_valid => x_cc_valid,
388 x_cc_type => v_CC_Type
389 );
390
391 EXCEPTION
392 WHEN OTHERS THEN
393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
394 x_cc_valid:=FALSE;
395
396 END ValidateCC;
397
398 PROCEDURE Get_CC_Issuer_Range
399 (p_card_number IN iby_creditcard.ccnumber%TYPE,
400 x_card_issuer OUT NOCOPY iby_creditcard_issuers_b.card_issuer_code%TYPE,
401 x_issuer_range OUT NOCOPY iby_cc_issuer_ranges.cc_issuer_range_id%TYPE,
402 x_card_prefix OUT NOCOPY iby_cc_issuer_ranges.card_number_prefix%TYPE,
403 x_digit_check OUT NOCOPY iby_creditcard_issuers_b.digit_check_flag%TYPE
404 )
405 IS
406 l_cc_number iby_creditcard.ccnumber%TYPE;
407 l_length NUMBER;
408 l_DBUG_MOD VARCHAR2(100) :='IBY_CC_VALIDATE.Get_CC_Issuer_Range(5 params)';
409
410 BEGIN
411
412 iby_debug_pub.add('Enter',FND_LOG.LEVEL_STATEMENT,l_DBUG_MOD);
413
414 iby_debug_pub.add('Calling Get_CC_Issuer_Range (6 params)',FND_LOG.LEVEL_STATEMENT,l_DBUG_MOD);
415
416 Get_CC_Issuer_Range(p_card_number,x_card_issuer,x_issuer_range,x_card_prefix,x_digit_check,null);
417
418 iby_debug_pub.add('Exit',FND_LOG.LEVEL_STATEMENT,l_DBUG_MOD);
419
420 END Get_CC_Issuer_Range;
421 --Bug 8581161: FP:8352320
422 PROCEDURE Get_CC_Issuer_Range
423 (p_card_number IN iby_creditcard.ccnumber%TYPE,
424 x_card_issuer OUT NOCOPY iby_creditcard_issuers_b.card_issuer_code%TYPE,
425 x_issuer_range OUT NOCOPY iby_cc_issuer_ranges.cc_issuer_range_id%TYPE,
426 x_card_prefix OUT NOCOPY iby_cc_issuer_ranges.card_number_prefix%TYPE,
427 x_digit_check OUT NOCOPY iby_creditcard_issuers_b.digit_check_flag%TYPE,
428 p_card_type IN iby_creditcard.card_issuer_code%TYPE
429 )
430 IS
431 l_cc_number iby_creditcard.ccnumber%TYPE;
432 l_length NUMBER;
433 l_DBUG_MOD VARCHAR2(100) :='IBY_CC_VALIDATE.Get_CC_Issuer_Range(6 params)';
434
435 CURSOR c_range
436 (ci_card_number IN iby_creditcard.ccnumber%TYPE,
437 ci_card_len IN NUMBER,
438 ci_card_type IN iby_creditcard.card_issuer_code%TYPE)
439 IS
440 SELECT cc_issuer_range_id, r.card_issuer_code,
441 card_number_prefix, NVL(digit_check_flag,'N')
442 FROM iby_cc_issuer_ranges r, iby_creditcard_issuers_b i
443 WHERE (card_number_length = ci_card_len)
444 AND (INSTR(ci_card_number,card_number_prefix) = 1)
445 AND (r.card_issuer_code = i.card_issuer_code)
446 AND r.card_issuer_code = NVL(ci_card_type, r.card_issuer_code)
447 ORDER BY r.last_updated_by DESC,
448 r.last_update_date DESC;
449
450 BEGIN
451 iby_debug_pub.add('Enter',FND_LOG.LEVEL_STATEMENT,l_DBUG_MOD);
452
453 IF (c_range%ISOPEN) THEN CLOSE c_range; END IF;
454
455 l_cc_number :=
456 IBY_CC_VALIDATE.StripCC(p_card_number,IBY_CC_VALIDATE.c_FillerChars);
457
458 l_length := LENGTH(l_cc_number);
459
460
461 IF (l_length > 30) THEN
462 x_card_issuer := 'INVALID';
463 END IF;
464
465 /*
466 ** Removed all hard coded values.
467 ** We alwasy refer to DB to get the output values.
468 ** Bug# 8581161
469 */
470
471
472 OPEN c_range(l_cc_number,l_length,p_card_type);
473 FETCH c_range INTO x_issuer_range, x_card_issuer,
474 x_card_prefix, x_digit_check;
475 CLOSE c_range;
476
477 IF (x_card_issuer IS NULL) THEN
478 x_card_issuer := 'UNKNOWN';
479 x_digit_check := 'N';
480 END IF;
481
482 iby_debug_pub.add('Exit',FND_LOG.LEVEL_STATEMENT,l_DBUG_MOD);
483
484 EXCEPTION
485 WHEN OTHERS THEN
486 iby_debug_pub.add('In Others Exception',FND_LOG.LEVEL_UNEXPECTED, l_DBUG_MOD);
487 iby_debug_pub.add('Exception code='||SQLCODE,FND_LOG.LEVEL_UNEXPECTED,l_DBUG_MOD);
488 iby_debug_pub.add('Exception message='||SQLERRM,FND_LOG.LEVEL_UNEXPECTED,l_DBUG_MOD);
489
490
491
492 END Get_CC_Issuer_Range;
493
494 FUNCTION Get_CC_Issuer_Range
495 (p_card_number IN iby_creditcard.ccnumber%TYPE)
496 RETURN NUMBER
497 IS
498 lx_card_issuer iby_creditcard_issuers_b.card_issuer_code%TYPE;
499 lx_issuer_range iby_cc_issuer_ranges.cc_issuer_range_id%TYPE;
500 lx_card_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE;
501 lx_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE;
502 BEGIN
503 Get_CC_Issuer_Range(p_card_number,lx_card_issuer,lx_issuer_range,
504 lx_card_prefix,lx_digit_check);
505 RETURN lx_issuer_range;
506 END Get_CC_Issuer_Range;
507
508 END IBY_CC_VALIDATE;
509
[Home] [Help]