1 PACKAGE BODY IBY_CC_VALIDATE AS
2 /* $Header: ibyccvlb.pls 120.5.12010000.3 2008/11/24 14:20:24 cjain 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 PROCEDURE StripCC ( p_api_version IN NUMBER,
92 p_init_msg_list IN VARCHAR2,
93 p_cc_id IN VARCHAR2,
94 p_fill_chars IN VARCHAR2,
95
96 x_return_status OUT NOCOPY VARCHAR2,
97 x_msg_count OUT NOCOPY NUMBER,
98 x_msg_data OUT NOCOPY VARCHAR2,
99 x_cc_id OUT NOCOPY VARCHAR2,
100 p_char_allowed IN VARCHAR2 DEFAULT 'N'
101 ) AS
102
103 c_Digits CONSTANT VARCHAR2(10) :='0123456789';
104 c_chars CONSTANT VARCHAR2(52) :='ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
105 v_StrippedCC VARCHAR2(100) :='';
106 v_CCChar CHAR;
107 v_FoundBadChar BOOLEAN :=FALSE;
108 BEGIN
109 FOR v_Counter IN 1..LENGTH(p_cc_id) LOOP
110 --
111 -- tests if a character is a digit
112 --
113 v_CCChar := SUBSTR(p_cc_id,v_Counter,1);
114
115 IF INSTR(c_Digits,v_CCChar)>0 THEN
116 v_StrippedCC := v_StrippedCC || v_CCChar;
117 ELSIF INSTR(p_fill_chars,v_CCChar)>0 THEN
118 NULL;
119 ELSIF INSTR(c_chars,v_CCChar)>0 THEN
120 v_StrippedCC := v_StrippedCC || v_CCChar;
121 ELSE
122 -- an illegal character found in the string
123 --
124 v_FoundBadChar:=TRUE;
125 --DBMS_OUTPUT.PUT_LINE('bad char: ' || v_CCChar);
126 EXIT;
127 END IF;
128 END LOOP;
129
130 IF v_FoundBadChar THEN
131 -- !!
132 -- do something useful here!!
133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
134 x_cc_id := NULL;
135 ELSE
136 x_return_status := FND_API.G_RET_STS_SUCCESS;
137 x_cc_id := v_StrippedCC;
138 END IF;
139
140 --DBMS_OUTPUT.PUT_LINE('stripped value is: ' || v_StrippedCC);
141
142 EXCEPTION
143 WHEN OTHERS THEN
144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
145 x_cc_id := NULL;
146 END StripCC;
147
148 PROCEDURE StripCC ( p_api_version IN NUMBER,
149 p_init_msg_list IN VARCHAR2,
150 p_cc_id IN VARCHAR2,
151
152 x_return_status OUT NOCOPY VARCHAR2,
153 x_msg_count OUT NOCOPY NUMBER,
154 x_msg_data OUT NOCOPY VARCHAR2,
155 x_cc_id OUT NOCOPY VARCHAR2,
156 p_char_allowed IN VARCHAR2 DEFAULT 'N'
157 ) AS
158
159 BEGIN
160
161 StripCC(p_api_version, p_init_msg_list, p_cc_id,
162 c_FillerChars, x_return_status, x_msg_count,
163 x_msg_data, x_cc_id, p_char_allowed );
164
165 END StripCC;
166
167 PROCEDURE GetCCType ( p_api_version IN NUMBER,
168 p_init_msg_list IN VARCHAR2,
169 p_cc_id IN VARCHAR2,
170
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_msg_count OUT NOCOPY NUMBER,
173 x_msg_data OUT NOCOPY VARCHAR2,
174 x_cc_type OUT NOCOPY CCType
175 ) AS
176
177 -- constants the lengths of various credit cards
178
179 c_MC_LEN CONSTANT INTEGER := 16;
180 c_VISA_LEN1 CONSTANT INTEGER := 13;
181 c_VISA_LEN2 CONSTANT INTEGER := 16;
182 c_AMEX_LEN CONSTANT INTEGER := 15;
183 c_DINERS_LEN CONSTANT INTEGER := 14;
184 c_DISCOVER_LEN CONSTANT INTEGER := 16;
185 c_ENROUTE_LEN CONSTANT INTEGER := 15;
186 c_JCB_LEN1 CONSTANT INTEGER := 16;
187 c_JCB_LEN2 CONSTANT INTEGER := 15;
188
189 -- constants for credit card prefixes
190
191 c_VISA_PREFIX CONSTANT VARCHAR(4) := '4';
192
193 c_MC_PREFIX1 CONSTANT VARCHAR(4) := '51';
194 c_MC_PREFIX2 CONSTANT VARCHAR(4) := '52';
195 c_MC_PREFIX3 CONSTANT VARCHAR(4) := '53';
196 c_MC_PREFIX4 CONSTANT VARCHAR(4) := '54';
197 c_MC_PREFIX5 CONSTANT VARCHAR(4) := '55';
198
199 c_DINERS_PREFIX1 CONSTANT VARCHAR(4) := '300';
200 c_DINERS_PREFIX2 CONSTANT VARCHAR(4) := '301';
201 c_DINERS_PREFIX3 CONSTANT VARCHAR(4) := '302';
202 c_DINERS_PREFIX4 CONSTANT VARCHAR(4) := '303';
203 c_DINERS_PREFIX5 CONSTANT VARCHAR(4) := '304';
204 c_DINERS_PREFIX6 CONSTANT VARCHAR(4) := '305';
205 c_DINERS_PREFIX7 CONSTANT VARCHAR(4) := '36';
206 c_DINERS_PREFIX8 CONSTANT VARCHAR(4) := '38';
207
208 c_AMEX_PREFIX1 CONSTANT VARCHAR(4) := '34';
209 c_AMEX_PREFIX2 CONSTANT VARCHAR(4) := '37';
210
211 c_DISCOVER_PREFIX CONSTANT VARCHAR(4) := '6011';
212
213 c_ENROUTE_PREFIX1 CONSTANT VARCHAR(4) := '2014';
214 c_ENROUTE_PREFIX2 CONSTANT VARCHAR(4) := '2149';
215
216 c_JCB_PREFIX1 CONSTANT VARCHAR(4) := '3';
217 c_JCB_PREFIX2 CONSTANT VARCHAR(4) := '2131';
218 c_JCB_PREFIX3 CONSTANT VARCHAR(4) := '1800';
219
220 v_Length INTEGER;
221 v_DigitsOk BOOLEAN;
222 BEGIN
223 x_return_status := FND_API.G_RET_STS_SUCCESS;
224
225 IF (p_cc_id IS NULL) THEN
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
227 x_cc_type:=c_InvalidCC;
228 RETURN;
229 END IF;
230
231 --DBMS_OUTPUT.PUT_LINE('cc length is: ' || v_Length);
232
233 v_Length:=LENGTH(p_cc_id);
234
235 IF (v_Length <=0) THEN
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
237 x_cc_type:=c_InvalidCC;
238 RETURN;
239 END IF;
240
241 IF (v_Length=c_VISA_LEN1 OR v_Length=c_VISA_LEN2) AND
242 INSTR(p_cc_id,c_VISA_PREFIX)=1 THEN
243 x_cc_type:=c_VisaCC;
244 ELSIF v_Length=c_MC_LEN AND ( INSTR(p_cc_id,c_MC_PREFIX1)=1 OR
245 INSTR(p_cc_id,c_MC_PREFIX2)=1 OR
246 INSTR(p_cc_id,c_MC_PREFIX3)=1 OR
247 INSTR(p_cc_id,c_MC_PREFIX4)=1 OR
248 INSTR(p_cc_id,c_MC_PREFIX5)=1 ) THEN
249 x_cc_type:=c_McCC;
250 ELSIF v_Length=c_AMEX_LEN AND ( INSTR(p_cc_id,c_AMEX_PREFIX1)=1
251 OR INSTR(p_cc_id,c_AMEX_PREFIX2)=1 ) THEN
252 x_cc_type:=c_AmexCC;
253 ELSIF v_Length=c_DISCOVER_LEN AND
254 INSTR(p_cc_id,c_DISCOVER_PREFIX)=1 THEN
255 x_cc_type:=c_DiscoverCC;
256 ELSIF v_Length=c_DINERS_LEN AND
257 (INSTR(p_cc_id,c_DINERS_PREFIX1)=1 OR
258 INSTR(p_cc_id,c_DINERS_PREFIX2)=1 OR
259 INSTR(p_cc_id,c_DINERS_PREFIX3)=1 OR
260 INSTR(p_cc_id,c_DINERS_PREFIX4)=1 OR
261 INSTR(p_cc_id,c_DINERS_PREFIX5)=1 OR
262 INSTR(p_cc_id,c_DINERS_PREFIX6)=1 OR
263 INSTR(p_cc_id,c_DINERS_PREFIX7)=1 OR
264 INSTR(p_cc_id,c_DINERS_PREFIX8)=1 ) THEN
265 x_cc_type:=c_DClubCC;
266 ELSIF v_Length=c_ENROUTE_LEN AND
267 (INSTR(p_cc_id,c_ENROUTE_PREFIX1)=1 OR
268 INSTR(p_cc_id,c_ENROUTE_PREFIX2)=1) THEN
269 x_cc_type:=c_EnrouteCC;
270 ELSIF (v_Length=c_JCB_LEN1) AND
271 INSTR(p_cc_id,c_JCB_PREFIX1)=1 THEN
272 x_cc_type:=c_JCBCC;
273 ELSIF (v_Length=c_JCB_LEN2) AND
274 (INSTR(p_cc_id,c_JCB_PREFIX2)=1 OR
275 INSTR(p_cc_id,c_JCB_PREFIX3)=1) THEN
276 x_cc_type:=c_JCBCC;
277 ELSE
278 x_cc_type:=c_UnknownCC;
279 END IF;
280
281 --DBMS_OUTPUT.PUT_LINE('seems like : ' || x_cc_type);
282
283 v_DigitsOk:= CheckCCDigits(p_cc_id) = 0;
284
285 -- this means there were some non-digit characters
286 -- in the credit card number
287 --
288 IF v_DigitsOk IS NULL THEN
289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
290 x_cc_type:=c_InvalidCC;
291 RETURN;
292 END IF;
293
294 IF x_cc_type<>c_UnknownCC AND x_cc_type<>c_EnrouteCC THEN
295 IF NOT v_DigitsOk THEN
296 x_cc_type:=c_InvalidCC;
297 END IF;
298 END IF;
299
300 --DBMS_OUTPUT.PUT_LINE('cctype is : ' || x_cc_type);
301
302 EXCEPTION
303 WHEN OTHERS THEN
304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
305 x_cc_type:=c_InvalidCC;
306 END GetCCType;
307
308
309 /*
310 ** This is an overloaded function which returns, a boolean value -
311 ** indicating whether the card is valid or not, AND CC type (in VARCHAR2)
312 ** from the lookup types defined for type 'IBY_CARD_TYPES'
313 */
314
315 PROCEDURE ValidateCC (p_api_version IN NUMBER,
316 p_init_msg_list IN VARCHAR2,
317 p_cc_id IN VARCHAR2,
318 p_expr_date IN DATE,
319
320 x_return_status OUT NOCOPY VARCHAR2,
321 x_msg_count OUT NOCOPY NUMBER,
322 x_msg_data OUT NOCOPY VARCHAR2,
323 x_cc_valid OUT NOCOPY BOOLEAN,
324 x_cc_type OUT NOCOPY VARCHAR2
325 ) AS
326
327 v_CurrDate DATE := SYSDATE();
328 v_CardType CCType;
329 v_spread REAL;
330 BEGIN
331
332 -- expr date is moved to the last day of the month it's on as
333 -- most credit cards
334 --
335 v_spread:=MONTHS_BETWEEN(LAST_DAY(TRUNC(p_expr_date)),
336 TRUNC(v_CurrDate));
337 x_return_status := FND_API.G_RET_STS_SUCCESS;
338
339 --DBMS_OUTPUT.PUT_LINE('Difference in time: ' || v_spread);
340 IF v_spread >= 0 THEN
341
342 GetCCType(p_api_version, p_init_msg_list, p_cc_id,
343 x_return_status, x_msg_count, x_msg_data,
344 v_CardType );
345 x_cc_valid:=(v_CardType<>c_InvalidCC);
346 x_cc_type := getLookupCCType( v_CardType );
347
348 /* set the other out variables here */
349 ELSE
350
351 x_cc_valid:=FALSE;
352
353 /* set the other out variables here */
354 END IF;
355
356 EXCEPTION
357 WHEN OTHERS THEN
358 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
359 x_cc_valid:=FALSE;
360 END ValidateCC;
361
362
363 /*
364 ** This is an overloaded function which returns, a boolean value -
365 ** indicating whether the card is valid or not.
366 */
367
368 PROCEDURE ValidateCC (p_api_version IN NUMBER,
369 p_init_msg_list IN VARCHAR2,
370 p_cc_id IN VARCHAR2,
371 p_expr_date IN DATE,
372
373 x_return_status OUT NOCOPY VARCHAR2,
374 x_msg_count OUT NOCOPY NUMBER,
375 x_msg_data OUT NOCOPY VARCHAR2,
376 x_cc_valid OUT NOCOPY BOOLEAN
377 ) AS
378
379 v_CC_Type VARCHAR2(80);
380
381 BEGIN
382
383 ValidateCC (p_api_version => p_api_version,
384 p_init_msg_list => p_init_msg_list,
385 p_cc_id => p_cc_id,
386 p_expr_date => p_expr_date,
387 x_return_status => x_return_status,
388 x_msg_count => x_msg_count,
389 x_msg_data => x_msg_data,
390 x_cc_valid => x_cc_valid,
391 x_cc_type => v_CC_Type
392 );
393
394 EXCEPTION
395 WHEN OTHERS THEN
396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
397 x_cc_valid:=FALSE;
398
399 END ValidateCC;
400
401 PROCEDURE Get_CC_Issuer_Range
402 (p_card_number IN iby_creditcard.ccnumber%TYPE,
403 x_card_issuer OUT NOCOPY iby_creditcard_issuers_b.card_issuer_code%TYPE,
404 x_issuer_range OUT NOCOPY iby_cc_issuer_ranges.cc_issuer_range_id%TYPE,
405 x_card_prefix OUT NOCOPY iby_cc_issuer_ranges.card_number_prefix%TYPE,
406 x_digit_check OUT NOCOPY iby_creditcard_issuers_b.digit_check_flag%TYPE
407 )
408 IS
409 CURSOR c_range
410 (ci_card_number IN iby_creditcard.ccnumber%TYPE,
411 ci_card_len IN NUMBER)
412 IS
413 SELECT cc_issuer_range_id, r.card_issuer_code,
414 card_number_prefix, NVL(digit_check_flag,'N')
415 FROM iby_cc_issuer_ranges r, iby_creditcard_issuers_b i
416 WHERE (card_number_length = ci_card_len)
417 AND (INSTR(ci_card_number,card_number_prefix) = 1)
418 AND (r.card_issuer_code = i.card_issuer_code);
419 BEGIN
420 IF (c_range%ISOPEN) THEN CLOSE c_range; END IF;
421
422 OPEN c_range(p_card_number,LENGTH(p_card_number));
423 FETCH c_range INTO x_issuer_range, x_card_issuer,
424 x_card_prefix, x_digit_check;
425 CLOSE c_range;
426
427 IF (x_card_issuer IS NULL) THEN
428 x_card_issuer := G_CARD_TYPE_UNKNOWN;
429 x_digit_check := 'N';
430 END IF;
431
432 END Get_CC_Issuer_Range;
433
434 FUNCTION Get_CC_Issuer_Range
435 (p_card_number IN iby_creditcard.ccnumber%TYPE)
436 RETURN NUMBER
437 IS
438 lx_card_issuer iby_creditcard_issuers_b.card_issuer_code%TYPE;
439 lx_issuer_range iby_cc_issuer_ranges.cc_issuer_range_id%TYPE;
440 lx_card_prefix iby_cc_issuer_ranges.card_number_prefix%TYPE;
441 lx_digit_check iby_creditcard_issuers_b.digit_check_flag%TYPE;
442 BEGIN
443 Get_CC_Issuer_Range(p_card_number,lx_card_issuer,lx_issuer_range,
444 lx_card_prefix,lx_digit_check);
445 RETURN lx_issuer_range;
446 END Get_CC_Issuer_Range;
447
448 END IBY_CC_VALIDATE;
449
[Home] [Help]