[Home] [Help]
PACKAGE BODY: APPS.IBY_PAYMENTCARD_PKG
Source
1 PACKAGE BODY iby_paymentcard_pkg AS
2 /*$Header: ibypmtcardb.pls 120.1.12010000.4 2009/01/20 13:30:44 lmallick noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_PAYMENTCARD_PKG';
5
6 G_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'iby.plsql.IBY_PAYMENTCARD_PKG';
7
8
9 FUNCTION Get_Billing_Site
10 (p_party_site_id IN hz_party_sites.party_site_id%TYPE,
11 p_party_id IN hz_parties.party_id%TYPE
12 )
13 RETURN hz_party_site_uses.party_site_use_id%TYPE
14 IS
15 l_site_use_id hz_party_site_uses.party_site_use_id%TYPE;
16 l_site_id hz_party_sites.party_site_id%TYPE;
17 l_site_use_rec HZ_PARTY_SITE_V2PUB.Party_Site_Use_rec_type;
18 lx_return_status VARCHAR2(1);
19 lx_msg_count NUMBER;
20 lx_msg_data VARCHAR2(2000);
21
22 CURSOR c_site_use
23 (ci_party_site IN hz_party_sites.party_site_id%TYPE,
24 ci_party_id IN hz_parties.party_id%TYPE
25 )
26 IS
27 SELECT u.party_site_use_id
28 FROM hz_party_site_uses u, hz_party_sites s
29 WHERE (u.party_site_id = ci_party_site)
30 -- because of complexities in the payer model
31 -- do not require the site address to be owned by the card owner
32 --AND (s.party_id = NVL(ci_party_id,party_id))
33 AND (u.party_site_id = s.party_site_id)
34 AND (u.site_use_type = G_PC_BILLING_SITE_USE)
35 AND ( NVL(u.begin_date,SYSDATE-10) < SYSDATE)
36 AND ( NVL(u.end_date,SYSDATE+10) > SYSDATE);
37
38 CURSOR c_site
39 (ci_party_site hz_party_sites.party_site_id%TYPE,
40 ci_party_id IN hz_parties.party_id%TYPE
41 )
42 IS
43 SELECT party_site_id
44 FROM hz_party_sites
45 WHERE (party_site_id = ci_party_site)
46 -- because of complexities in the payer model
47 -- do not require the site address to be owned by the card owner
48 --AND (party_id = NVL(ci_party_id,party_id))
49 AND ( NVL(start_date_active,SYSDATE-10) < SYSDATE)
50 AND ( NVL(end_date_active,SYSDATE+10) > SYSDATE);
51 BEGIN
52 IF c_site_use%ISOPEN THEN CLOSE c_site_use; END IF;
53 IF c_site%ISOPEN THEN CLOSE c_site; END IF;
54
55 OPEN c_site_use(p_party_site_id,NULL);
56 FETCH c_site_use INTO l_site_use_id;
57 CLOSE c_site_use;
58
59 -- create a site use if it does not exist
60 IF (l_site_use_id IS NULL) THEN
61 OPEN c_site(p_party_site_id,p_party_id);
62 FETCH c_site INTO l_site_id;
63 CLOSE c_site;
64
65 IF (NOT l_site_id IS NULL) THEN
66 l_site_use_rec.party_site_id := l_site_id;
67 l_site_use_rec.application_id := 673;
68 l_site_use_rec.site_use_type := G_PC_BILLING_SITE_USE;
69 l_site_use_rec.created_by_module := 'TCA_V2_API';
70
71 HZ_PARTY_SITE_V2PUB.Create_Party_Site_Use
72 (FND_API.G_FALSE,l_site_use_rec,l_site_use_id,
73 lx_return_status,lx_msg_count,lx_msg_data
74 );
75 END IF;
76 END IF;
77
78 RETURN l_site_use_id;
79 END Get_Billing_Site;
80
81
82 --
83 -- USE: Gets credit card mask settings
84 --
85 PROCEDURE Get_Mask_Settings
86 (x_mask_setting OUT NOCOPY iby_sys_security_options.credit_card_mask_setting%TYPE,
87 x_unmask_len OUT NOCOPY iby_sys_security_options.credit_card_unmask_len%TYPE
88 )
89 IS
90
91 CURSOR c_mask_setting
92 IS
93 SELECT credit_card_mask_setting, credit_card_unmask_len
94 FROM iby_sys_security_options;
95
96 BEGIN
97 x_mask_setting := iby_security_pkg.G_MASK_PREFIX;
98
99 IF (c_mask_setting%ISOPEN) THEN CLOSE c_mask_setting; END IF;
100
101 OPEN c_mask_setting;
102 FETCH c_mask_setting INTO x_mask_setting, x_unmask_len;
103 CLOSE c_mask_setting;
104
105 IF (x_mask_setting IS NULL) THEN
106 x_mask_setting := iby_security_pkg.G_MASK_PREFIX;
107 END IF;
108 IF (x_unmask_len IS NULL) THEN
109 x_unmask_len := G_DEF_UNMASK_LENGTH;
110 END IF;
111 END Get_Mask_Settings;
112
113 FUNCTION Mask_Card_Number
114 (p_card_number IN iby_paymentcard.card_number%TYPE,
115 p_mask_option IN iby_paymentcard.card_mask_setting%TYPE,
116 p_unmask_len IN iby_paymentcard.card_unmask_length%TYPE
117 )
118 RETURN iby_paymentcard.masked_card_number%TYPE
119 IS
120 BEGIN
121 RETURN iby_security_pkg.Mask_Data
122 (p_card_number,p_mask_option,p_unmask_len,G_MASK_CHARACTER);
123 END Mask_Card_Number;
124
125 --
126 -- Return: The masked card number, usable for display purposes
127 --
128 PROCEDURE Mask_Card_Number
129 (p_card_number IN iby_paymentcard.card_number%TYPE,
130 x_masked_number OUT NOCOPY iby_paymentcard.masked_card_number%TYPE,
131 x_mask_setting OUT NOCOPY iby_sys_security_options.credit_card_mask_setting%TYPE,
132 x_unmask_len OUT NOCOPY iby_sys_security_options.credit_card_unmask_len%TYPE
133 )
134 IS
135 BEGIN
136 Get_Mask_Settings(x_mask_setting,x_unmask_len);
137 x_masked_number :=
138 Mask_Card_Number(p_card_number,x_mask_setting,x_unmask_len);
139 END Mask_Card_Number;
140
141 FUNCTION Mask_Card_Number(p_card_number IN iby_paymentcard.card_number%TYPE)
142 RETURN iby_paymentcard.masked_card_number%TYPE
143 IS
144 lx_mask_option iby_paymentcard.card_mask_setting%TYPE;
145 lx_mask_number iby_paymentcard.masked_card_number%TYPE;
146 lx_unmask_len iby_sys_security_options.credit_card_unmask_len%TYPE;
150 END Mask_Card_Number;
147 BEGIN
148 Mask_Card_Number(p_card_number,lx_mask_number,lx_mask_option,lx_unmask_len);
149 RETURN lx_mask_number;
151
152 -- Validates the billing address passed for a payment card instrument
153 FUNCTION Validate_Card_Billing
154 ( p_is_update IN VARCHAR2, p_paymentcard IN PaymentCard_rec_type )
155 RETURN BOOLEAN
156 IS
157
158 lx_return_status VARCHAR2(1);
159 lx_msg_count NUMBER;
160 lx_msg_data VARCHAR2(3000);
161 lx_result IBY_FNDCPT_COMMON_PUB.Result_rec_type;
162
163 l_addressid iby_paymentcard.addressid%TYPE;
164 l_billing_zip iby_paymentcard.billing_addr_postal_code%TYPE;
165 l_billing_terr iby_paymentcard.bill_addr_territory_code%TYPE;
166
167 BEGIN
168
169 IF (p_paymentcard.Info_Only_Flag = 'Y') THEN
170 RETURN TRUE;
171 END IF;
172
173 l_addressid := p_paymentcard.Billing_Address_Id;
174 l_billing_zip := p_paymentcard.Billing_Postal_Code;
175 l_billing_terr := p_paymentcard.Billing_Address_Territory;
176
177 IF FND_API.to_Boolean(p_is_update) THEN
178 IF (l_addressid = FND_API.G_MISS_NUM) THEN
179 l_addressid := NULL;
180 ELSIF (l_addressid IS NULL) THEN
181 l_addressid := FND_API.G_MISS_NUM;
182 END IF;
183 IF (l_billing_zip = FND_API.G_MISS_CHAR) THEN
184 l_billing_zip := NULL;
185 ELSIF (l_billing_zip IS NULL) THEN
186 l_billing_zip := FND_API.G_MISS_CHAR;
187 END IF;
188 IF (l_billing_terr = FND_API.G_MISS_CHAR) THEN
189 l_billing_terr := NULL;
190 ELSIF (l_billing_terr IS NULL) THEN
191 l_billing_terr := FND_API.G_MISS_CHAR;
192 END IF;
193 END IF;
194
195 IF ( (NOT (l_addressid IS NULL OR l_addressid = FND_API.G_MISS_NUM))
196 AND
197 (NOT (l_billing_zip IS NULL OR l_billing_zip = FND_API.G_MISS_CHAR))
198 )
199 THEN
200 RETURN FALSE;
201 END IF;
202
203 IF ( (NOT (l_billing_zip IS NULL OR l_billing_zip = FND_API.G_MISS_CHAR))
204 AND (l_billing_terr IS NULL OR l_billing_terr = FND_API.G_MISS_CHAR)
205 )
206 THEN
207 RETURN FALSE;
208 ELSIF ( (NOT (l_billing_terr IS NULL OR l_billing_terr = FND_API.G_MISS_CHAR))
209
210 AND (l_billing_zip IS NULL OR l_billing_zip = FND_API.G_MISS_CHAR)
211 )
212 THEN
213 RETURN FALSE;
214 END IF;
215
216 RETURN TRUE;
217 END Validate_Card_Billing;
218
219
220
221 PROCEDURE Create_Card
222 (p_commit IN VARCHAR2,
223 p_owner_id IN iby_paymentcard.card_owner_id%TYPE,
224 p_holder_name IN iby_paymentcard.chname%TYPE,
225 p_billing_address_id IN iby_paymentcard.addressid%TYPE,
226 p_address_type IN VARCHAR2,
227 p_billing_zip IN iby_paymentcard.billing_addr_postal_code%TYPE,
228 p_billing_country IN iby_paymentcard.bill_addr_territory_code%TYPE,
229 p_card_number IN iby_paymentcard.card_number%TYPE,
230 p_expiry_date IN iby_paymentcard.expirydate%TYPE,
231 p_instr_type IN iby_paymentcard.instrument_type%TYPE,
232 p_issuer IN iby_paymentcard.card_issuer_code%TYPE,
233 p_fi_name IN iby_paymentcard.finame%TYPE,
234 p_single_use IN iby_paymentcard.single_use_flag%TYPE,
235 p_info_only IN iby_paymentcard.information_only_flag%TYPE,
236 p_purpose IN iby_paymentcard.card_purpose%TYPE,
237 p_desc IN iby_paymentcard.description%TYPE,
238 p_active_flag IN iby_paymentcard.active_flag%TYPE,
239 p_inactive_date IN iby_paymentcard.inactive_date%TYPE,
240 p_attribute_category IN iby_paymentcard.attribute_category%TYPE,
241 p_attribute1 IN iby_paymentcard.attribute1%TYPE,
242 p_attribute2 IN iby_paymentcard.attribute2%TYPE,
243 p_attribute3 IN iby_paymentcard.attribute3%TYPE,
244 p_attribute4 IN iby_paymentcard.attribute4%TYPE,
245 p_attribute5 IN iby_paymentcard.attribute5%TYPE,
246 p_attribute6 IN iby_paymentcard.attribute6%TYPE,
247 p_attribute7 IN iby_paymentcard.attribute7%TYPE,
248 p_attribute8 IN iby_paymentcard.attribute8%TYPE,
249 p_attribute9 IN iby_paymentcard.attribute9%TYPE,
250 p_attribute10 IN iby_paymentcard.attribute10%TYPE,
251 p_attribute11 IN iby_paymentcard.attribute11%TYPE,
252 p_attribute12 IN iby_paymentcard.attribute12%TYPE,
253 p_attribute13 IN iby_paymentcard.attribute13%TYPE,
254 p_attribute14 IN iby_paymentcard.attribute14%TYPE,
255 p_attribute15 IN iby_paymentcard.attribute15%TYPE,
256 x_result_code OUT NOCOPY VARCHAR2,
257 x_instr_id OUT NOCOPY iby_paymentcard.instrid%TYPE
258 )
259 IS
260
261 lx_return_status VARCHAR2(1);
262 lx_msg_count NUMBER;
263 lx_msg_data VARCHAR2(200);
264
265 l_card_len iby_paymentcard.card_number_length%TYPE;
266
267 lx_card_number iby_paymentcard.card_number%TYPE;
268 lx_unmasked_digits iby_paymentcard.card_number%TYPE;
269
270 lx_masked_number iby_paymentcard.masked_card_number%TYPE;
271 lx_mask_option iby_paymentcard.card_mask_setting%TYPE;
272 lx_unmask_len iby_paymentcard.card_unmask_length%TYPE;
273
274
275 l_expiry_date iby_paymentcard.expirydate%TYPE;
276 l_billing_site hz_party_site_uses.party_site_use_id%TYPE;
277
278
279 CURSOR c_card
280 (ci_owner_id IN hz_parties.party_id%TYPE,
281 ci_card_number IN iby_paymentcard.card_number%TYPE
282 )
283 IS
284 SELECT instrid
285 FROM iby_paymentcard
289 )
286 WHERE (card_number = ci_card_number)
287 AND ( (NVL(card_owner_id,ci_owner_id) = NVL(ci_owner_id,card_owner_id))
288 OR (card_owner_id IS NULL AND ci_owner_id IS NULL)
290 AND (NVL(single_use_flag,'N')='N');
291 BEGIN
292
293 IF (c_card%ISOPEN) THEN CLOSE c_card; END IF;
294
295 IF (p_card_number IS NULL ) THEN
296 x_result_code := G_RC_INVALID_CARD_NUMBER;
297 RETURN;
298 END IF;
299
300 -- expiration date may be null
301 IF (NOT p_expiry_date IS NULL) THEN
302 l_expiry_date := LAST_DAY(p_expiry_date);
303 IF (TRUNC(l_expiry_date,'DD') < TRUNC(SYSDATE,'DD')) THEN
304 x_result_code := G_RC_INVALID_CARD_EXPIRY;
305 RETURN;
306 END IF;
307 END IF;
308
309 IF ( (NVL(p_instr_type,' ') <> G_LKUP_INSTR_TYPE_PC))
310 THEN
311 x_result_code := G_RC_INVALID_INSTR_TYPE;
312 RETURN;
313 END IF;
314
315 -- Assign p_card_number directly to lx_card_number since there is
316 -- validation done on Payment cards. These could be potentially anything
317 lx_card_number := p_card_number;
318 Mask_Card_Number(lx_card_number,lx_masked_number,lx_mask_option,lx_unmask_len);
319
320 -- Bug 5153265 start
321 -- If Site use id is already provied then no need to call get_billing address
322 IF (p_address_type = G_PARTY_SITE_USE_ID) AND (NOT (p_billing_address_id IS NULL)) THEN
323 l_billing_site := p_billing_address_id;
324 ELSE
325 IF (p_billing_address_id = FND_API.G_MISS_NUM ) THEN
326 l_billing_site := FND_API.G_MISS_NUM;
327 ELSIF (NOT (p_billing_address_id IS NULL)) THEN
328 l_billing_site := Get_Billing_Site(p_billing_address_id,p_owner_id);
329 IF (l_billing_site IS NULL) THEN
330 x_result_code := G_RC_INVALID_ADDRESS;
331 RETURN;
332 END IF;
333 END IF;
334 END IF;
335 -- Bug 5153265 end
336
337 IF (NOT ( (p_billing_country IS NULL)
338 OR (p_billing_country = FND_API.G_MISS_CHAR) )
339 )
340 THEN
341 IF (NOT iby_utility_pvt.Validate_Territory(p_billing_country)) THEN
342 x_result_code := G_RC_INVALID_ADDRESS;
343 RETURN;
344 END IF;
345 END IF;
346
347 IF (NOT p_owner_id IS NULL) THEN
348 IF (NOT iby_utility_pvt.validate_party_id(p_owner_id)) THEN
349 x_result_code := G_RC_INVALID_PARTY;
350 RETURN;
351 END IF;
352 END IF;
353
354 OPEN c_card(p_owner_id, p_card_number);
355 FETCH c_card INTO x_instr_id;
356 CLOSE c_card;
357
358 IF (NOT x_instr_id IS NULL) THEN RETURN; END IF;
359
360 SELECT iby_paymentcard_s.NEXTVAL INTO x_instr_id FROM DUAL;
361
362 INSERT INTO iby_paymentcard
363 (instrid, card_number, masked_card_number,
364 card_mask_setting, card_unmask_length,
365 expirydate, card_owner_id, chname,
366 addressid, billing_addr_postal_code, bill_addr_territory_code,
367 instrument_type, card_issuer_code, card_number_length,
368 description, finame,
369 single_use_flag, information_only_flag, card_purpose,
370 active_flag, inactive_date,
371 last_update_date, last_updated_by, creation_date,
372 created_by, object_version_number,
373 attribute_category,
374 attribute1,attribute2, attribute3,attribute4,attribute5,
375 attribute6,attribute7, attribute8,attribute9,attribute10,
376 attribute11,attribute12, attribute13,attribute14,attribute15
377 )
378 VALUES
379 (x_instr_id, p_card_number, lx_masked_number,
380 lx_mask_option, lx_unmask_len,
381 l_expiry_date, p_owner_id, p_holder_name,
382 l_billing_site, p_billing_zip, p_billing_country,
383 p_instr_type, C_ISSUER_COMCHECK, l_card_len,
384 p_desc, p_fi_name,
385 NVL(p_single_use,'N'), NVL(p_info_only,'N'), p_purpose,
386 NVL(p_active_flag,'Y'), p_inactive_date,
387 sysdate, fnd_global.user_id, sysdate,
388 fnd_global.user_id, 1,
389 p_attribute_category,
390 p_attribute1,p_attribute2,p_attribute3,p_attribute4,p_attribute5,
391 p_attribute6,p_attribute7,p_attribute8,p_attribute9,p_attribute10,
392 p_attribute11,p_attribute12,p_attribute13,p_attribute14,p_attribute15
393 );
394
395 IF FND_API.To_Boolean(p_commit) THEN
396 COMMIT;
397 END IF;
398 END Create_Card;
399
400 PROCEDURE Update_Card
401 (p_commit IN VARCHAR2,
402 p_instr_id IN iby_paymentcard.instrid%TYPE,
403 p_owner_id IN iby_paymentcard.card_owner_id%TYPE,
404 p_holder_name IN iby_paymentcard.chname%TYPE,
405 p_billing_address_id IN iby_paymentcard.addressid%TYPE,
406 p_address_type IN VARCHAR2 := G_PARTY_SITE_ID,
407 p_billing_zip IN iby_paymentcard.billing_addr_postal_code%TYPE,
408 p_billing_country IN iby_paymentcard.bill_addr_territory_code%TYPE,
409 p_expiry_date IN iby_paymentcard.expirydate%TYPE,
410 p_instr_type IN iby_paymentcard.instrument_type%TYPE,
411 p_fi_name IN iby_paymentcard.finame%TYPE,
412 p_single_use IN iby_paymentcard.single_use_flag%TYPE,
413 p_info_only IN iby_paymentcard.information_only_flag%TYPE,
414 p_purpose IN iby_paymentcard.card_purpose%TYPE,
415 p_desc IN iby_paymentcard.description%TYPE,
416 p_active_flag IN iby_paymentcard.active_flag%TYPE,
417 p_inactive_date IN iby_paymentcard.inactive_date%TYPE,
418 p_attribute_category IN iby_paymentcard.attribute_category%TYPE,
419 p_attribute1 IN iby_paymentcard.attribute1%TYPE,
423 p_attribute5 IN iby_paymentcard.attribute5%TYPE,
420 p_attribute2 IN iby_paymentcard.attribute2%TYPE,
421 p_attribute3 IN iby_paymentcard.attribute3%TYPE,
422 p_attribute4 IN iby_paymentcard.attribute4%TYPE,
424 p_attribute6 IN iby_paymentcard.attribute6%TYPE,
425 p_attribute7 IN iby_paymentcard.attribute7%TYPE,
426 p_attribute8 IN iby_paymentcard.attribute8%TYPE,
427 p_attribute9 IN iby_paymentcard.attribute9%TYPE,
428 p_attribute10 IN iby_paymentcard.attribute10%TYPE,
429 p_attribute11 IN iby_paymentcard.attribute11%TYPE,
430 p_attribute12 IN iby_paymentcard.attribute12%TYPE,
431 p_attribute13 IN iby_paymentcard.attribute13%TYPE,
432 p_attribute14 IN iby_paymentcard.attribute14%TYPE,
433 p_attribute15 IN iby_paymentcard.attribute15%TYPE,
434 x_result_code OUT NOCOPY VARCHAR2
435 )
436 IS
437 l_billing_site NUMBER;
438 l_expiry_date iby_paymentcard.expirydate%TYPE;
439
440
441 l_msg_count NUMBER;
442 l_msg_data VARCHAR2(300);
443 l_return_status VARCHAR2(1);
444
445 l_sec_code VARCHAR2(10);
446
447 l_exp_date DATE;
448
449 BEGIN
450
451 IF (NOT p_instr_type IS NULL) THEN
452 IF (p_instr_type <> G_LKUP_INSTR_TYPE_PC)
453 THEN
454 x_result_code := G_RC_INVALID_INSTR_TYPE;
455 RETURN;
456 END IF;
457 END IF;
458
459 IF (NOT p_owner_id IS NULL) THEN
460 IF (NOT iby_utility_pvt.validate_party_id(p_owner_id)) THEN
461 x_result_code := G_RC_INVALID_PARTY;
462 RETURN;
463 END IF;
464 END IF;
465 -- Bug 5153265 start
466 -- If Site use id is already provied then no need to call get_billing address
467 IF (p_address_type = G_PARTY_SITE_USE_ID) AND (NOT (p_billing_address_id IS NULL)) THEN
468 l_billing_site := p_billing_address_id;
469 ELSE
470 IF (p_billing_address_id = FND_API.G_MISS_NUM ) THEN
471 l_billing_site := FND_API.G_MISS_NUM;
472 ELSIF (NOT (p_billing_address_id IS NULL)) THEN
473 l_billing_site := Get_Billing_Site(p_billing_address_id,p_owner_id);
474 IF (l_billing_site IS NULL) THEN
475 x_result_code := G_RC_INVALID_ADDRESS;
476 RETURN;
477 END IF;
478 END IF;
479 END IF;
480 -- Bug 5153265 end
481
482 IF (NOT ( (p_billing_country IS NULL)
483 OR (p_billing_country = FND_API.G_MISS_CHAR) )
484 )
485 THEN
486 IF (NOT iby_utility_pvt.Validate_Territory(p_billing_country)) THEN
487 x_result_code := G_RC_INVALID_ADDRESS;
488 RETURN;
489 END IF;
490 END IF;
491
492
493 -- Bug 5479785 (Panaraya)
494 -- Added check for expiry date on update
495 -- expiration date may be null
496 IF (NOT p_expiry_date IS NULL) THEN
497 l_expiry_date := LAST_DAY(p_expiry_date);
498 IF (TRUNC(l_expiry_date,'DD') < TRUNC(SYSDATE,'DD')) THEN
499 x_result_code := G_RC_INVALID_CARD_EXPIRY;
500 RETURN;
501 END IF;
502 END IF;
503
504 UPDATE iby_paymentcard
505 SET chname = DECODE(p_holder_name, FND_API.G_MISS_CHAR,NULL, NULL,chname, p_holder_name),
506
507 card_owner_id = NVL(card_owner_id,p_owner_id),
508 addressid = DECODE(l_billing_site, FND_API.G_MISS_NUM,NULL,
509 NULL,addressid, l_billing_site),
510 bill_addr_territory_code =
511 DECODE(p_billing_country, FND_API.G_MISS_CHAR,NULL,
512 NULL,bill_addr_territory_code, p_billing_country),
513 billing_addr_postal_code =
514 DECODE(p_billing_zip, FND_API.G_MISS_CHAR,NULL,
515 NULL,billing_addr_postal_code, p_billing_zip),
516 expirydate = NVL(p_expiry_date, expirydate),
517
518 instrument_type = NVL(p_instr_type, instrument_type),
519
520 finame = DECODE(p_fi_name, FND_API.G_MISS_CHAR,NULL, NULL,finame, p_fi_name),
521 single_use_flag = NVL(p_single_use, single_use_flag),
522 information_only_flag = NVL(p_info_only, information_only_flag),
523 card_purpose = DECODE(p_purpose, FND_API.G_MISS_CHAR,NULL, NULL,card_purpose, p_purpose),
524 description = DECODE(p_desc, FND_API.G_MISS_CHAR,NULL, NULL,description, p_desc),
525 active_flag = NVL(p_active_flag, active_flag),
526 inactive_date = DECODE(p_inactive_date, FND_API.G_MISS_DATE,NULL,
527 NULL,inactive_date, p_inactive_date),
528 object_version_number = object_version_number + 1,
529 last_update_date = sysdate,
530 last_updated_by = fnd_global.user_id,
531 last_update_login = fnd_global.login_id,
532 attribute_category = p_Attribute_category,
533 attribute1 = p_attribute1,
534 attribute2 = p_attribute2,
535 attribute3 = p_attribute3,
536 attribute4 = p_attribute4,
537 attribute5 = p_attribute5,
538 attribute6 = p_attribute6,
539 attribute7 = p_attribute7,
540 attribute8 = p_attribute8,
541 attribute9 = p_attribute9,
542 attribute10 = p_attribute10,
543 attribute11 = p_attribute11,
544 attribute12 = p_attribute12,
545 attribute13 = p_attribute13,
546 attribute14 = p_attribute14,
547 attribute15 = p_attribute15
548 WHERE (instrid = p_instr_id);
549
550 IF (SQL%NOTFOUND) THEN x_result_code := G_RC_INVALID_CARD_ID; END IF;
551
552 IF FND_API.To_Boolean(p_commit) THEN
553 COMMIT;
554 END IF;
555 END Update_Card;
556
557 PROCEDURE Create_Card
558 (
562 x_return_status OUT NOCOPY VARCHAR2,
559 p_api_version IN NUMBER,
560 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
561 p_commit IN VARCHAR2 := FND_API.G_TRUE,
563 x_msg_count OUT NOCOPY NUMBER,
564 x_msg_data OUT NOCOPY VARCHAR2,
565 p_card_instrument IN PaymentCard_rec_type,
566 x_card_id OUT NOCOPY NUMBER,
567 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
568 )
569 IS
570
571 l_api_version CONSTANT NUMBER := 1.0;
572 l_module CONSTANT VARCHAR2(30) := 'Create_Card';
573 l_prev_msg_count NUMBER;
574
575 lx_result_code VARCHAR2(30);
576 lx_result IBY_FNDCPT_COMMON_PUB.Result_rec_type;
577 lx_card_rec PaymentCard_rec_type;
578
579 l_info_only iby_paymentcard.information_only_flag%TYPE := NULL;
580
581 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
582
583
584 BEGIN
585 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
586
587 IF NOT FND_API.Compatible_API_Call (l_api_version,
588 p_api_version,
589 l_module,
590 G_PKG_NAME)
591 THEN
592 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
593 debug_level => FND_LOG.LEVEL_ERROR,
594 module => G_DEBUG_MODULE || l_module);
595 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
596 FND_MSG_PUB.Add;
597 RAISE FND_API.G_EXC_ERROR;
598 END IF;
599
600 IF FND_API.to_Boolean( p_init_msg_list ) THEN
601 FND_MSG_PUB.initialize;
602 END IF;
603 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
604
605 --SAVEPOINT Create_Card;
606
607 Card_Exists
608 (
609 1.0,
610 FND_API.G_FALSE,
611 x_return_status,
612 x_msg_count,
613 x_msg_data,
614 p_card_instrument.Owner_Id,
615 p_card_instrument.Card_Number,
616 lx_card_rec,
617 lx_result,
618 NVL(p_card_instrument.Instrument_Type, C_INSTRTYPE_PAYMENTCARD)
619 );
620
621 iby_debug_pub.add('fetched card id:='||lx_card_rec.Card_Id,
622 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
623
624 IF (lx_card_rec.Card_Id IS NULL) THEN
625
626 -- validate billing address information
627 IF (NOT Validate_Card_Billing(FND_API.G_FALSE,p_card_instrument)) THEN
628 x_response.Result_Code := G_RC_INVALID_ADDRESS;
629 iby_fndcpt_common_pub.Prepare_Result
630 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
631 RETURN;
632 END IF;
633
634 iby_debug_pub.add('creating new card',
635 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
636
637 Create_Card
638 (FND_API.G_FALSE,
639 p_card_instrument.Owner_Id, p_card_instrument.Card_Holder_Name,
640 p_card_instrument.Billing_Address_Id,
641 p_card_instrument.Address_Type,
642 p_card_instrument.Billing_Postal_Code,
643 p_card_instrument.Billing_Address_Territory,
644 p_card_instrument.Card_Number, p_card_instrument.Expiration_Date,
645 NVL(p_card_instrument.Instrument_Type, C_INSTRTYPE_PAYMENTCARD),
646 C_ISSUER_COMCHECK,
647 p_card_instrument.FI_Name, p_card_instrument.Single_Use_Flag,
648 p_card_instrument.Info_Only_Flag, p_card_instrument.Card_Purpose,
649 p_card_instrument.Card_Description, p_card_instrument.Active_Flag,
650 p_card_instrument.Inactive_Date,
651 p_card_instrument.attribute_category,
652 p_card_instrument.attribute1,
653 p_card_instrument.attribute2,
654 p_card_instrument.attribute3,
655 p_card_instrument.attribute4,
656 p_card_instrument.attribute5,
657 p_card_instrument.attribute6,
658 p_card_instrument.attribute7,
659 p_card_instrument.attribute8,
660 p_card_instrument.attribute9,
661 p_card_instrument.attribute10,
662 p_card_instrument.attribute11,
663 p_card_instrument.attribute12,
664 p_card_instrument.attribute13,
665 p_card_instrument.attribute14,
666 p_card_instrument.attribute15,
667 lx_result_code, x_card_id
668 );
669 ELSE
670
671 -- card cannot become info only once this flag is turned off
672 IF (NOT p_card_instrument.Info_Only_Flag = 'Y') THEN
673 l_info_only := p_card_instrument.Info_Only_Flag;
674 END IF;
675
676 -- validate billing address information
677 IF (NOT Validate_Card_Billing(FND_API.G_TRUE,p_card_instrument)) THEN
678 x_response.Result_Code := G_RC_INVALID_ADDRESS;
679 iby_fndcpt_common_pub.Prepare_Result
680 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
681 RETURN;
682 END IF;
683 -- validate expiration date
684 IF (TRUNC(p_card_instrument.Expiration_Date,'DD') < TRUNC(SYSDATE,'DD'))
685 THEN
686 x_response.Result_Code := G_RC_INVALID_CARD_EXPIRY;
687 iby_fndcpt_common_pub.Prepare_Result
688 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
689 RETURN;
690 END IF;
691
692 Update_Card
693 (FND_API.G_FALSE, lx_card_rec.Card_Id, p_card_instrument.Owner_Id,
697 p_card_instrument.Billing_Postal_Code,
694 p_card_instrument.Card_Holder_Name,
695 p_card_instrument.Billing_Address_Id,
696 p_card_instrument.Address_Type,
698 p_card_instrument.Billing_Address_Territory,
699 p_card_instrument.Expiration_Date, p_card_instrument.Instrument_Type,
700 p_card_instrument.FI_Name, p_card_instrument.Single_Use_Flag,
701 l_info_only, p_card_instrument.Card_Purpose,
702 p_card_instrument.Card_Description, p_card_instrument.Active_Flag,
703 NVL(p_card_instrument.Inactive_Date,FND_API.G_MISS_DATE),
704 p_card_instrument.attribute_category,
705 p_card_instrument.attribute1, p_card_instrument.attribute2,
706 p_card_instrument.attribute3, p_card_instrument.attribute4,
707 p_card_instrument.attribute5, p_card_instrument.attribute6,
708 p_card_instrument.attribute7, p_card_instrument.attribute8,
709 p_card_instrument.attribute9, p_card_instrument.attribute10,
710 p_card_instrument.attribute11, p_card_instrument.attribute12,
711 p_card_instrument.attribute13, p_card_instrument.attribute14,
712 p_card_instrument.attribute15, lx_result_code);
713 x_card_id := lx_card_rec.Card_Id;
714 END IF;
715
716 x_response.Result_Code := NVL(lx_result_code,IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS);
717 iby_fndcpt_common_pub.Prepare_Result
718 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
719
720 IF FND_API.To_Boolean(p_commit) THEN
721 COMMIT;
722 END IF;
723
724 EXCEPTION
725
726 WHEN FND_API.G_EXC_ERROR THEN
727 --ROLLBACK TO Create_Card;
728 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
729 debug_level => FND_LOG.LEVEL_ERROR,
730 module => G_DEBUG_MODULE || l_module);
731 x_return_status := FND_API.G_RET_STS_ERROR ;
732 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
733 p_data => x_msg_data
734 );
735 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
736 --ROLLBACK TO Create_Card;
737 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
738 debug_level => FND_LOG.LEVEL_UNEXPECTED,
739 module => G_DEBUG_MODULE || l_module);
740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
741 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
742 p_data => x_msg_data
743 );
744 WHEN OTHERS THEN
745 --ROLLBACK TO Create_Card;
746 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
747 debug_level => FND_LOG.LEVEL_UNEXPECTED,
748 module => G_DEBUG_MODULE || l_module);
749
750 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
751
752 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
753 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
754 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
755 END IF;
756
757 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
758 p_data => x_msg_data
759 );
760
761 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
762 debug_level => FND_LOG.LEVEL_UNEXPECTED,
763 module => G_DEBUG_MODULE || l_module);
764 iby_debug_pub.add(debug_msg => 'Exit Exception',
765 debug_level => FND_LOG.LEVEL_UNEXPECTED,
766 module => G_DEBUG_MODULE || l_module);
767
768 END Create_Card;
769
770 PROCEDURE Update_Card
771 (
772 p_api_version IN NUMBER,
773 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
774 p_commit IN VARCHAR2 := FND_API.G_TRUE,
775 x_return_status OUT NOCOPY VARCHAR2,
776 x_msg_count OUT NOCOPY NUMBER,
777 x_msg_data OUT NOCOPY VARCHAR2,
778 p_card_instrument IN PaymentCard_rec_type,
779 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
780 )
781 IS
782
783 l_api_version CONSTANT NUMBER := 1.0;
784 l_module CONSTANT VARCHAR2(30) := 'Update_Card';
785 l_prev_msg_count NUMBER;
786
787 lx_result_code VARCHAR2(30);
788
789 l_info_only iby_paymentcard.information_only_flag%TYPE := NULL;
790
791 BEGIN
792 IF NOT FND_API.Compatible_API_Call (l_api_version,
793 p_api_version,
794 l_module,
795 G_PKG_NAME)
796 THEN
797 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
798 debug_level => FND_LOG.LEVEL_ERROR,
799 module => G_DEBUG_MODULE || l_module);
800 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
801 FND_MSG_PUB.Add;
802 RAISE FND_API.G_EXC_ERROR;
803 END IF;
804
805 IF FND_API.to_Boolean( p_init_msg_list ) THEN
806 FND_MSG_PUB.initialize;
807 END IF;
808 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
809
810 SAVEPOINT Update_Card;
811
812 -- card cannot become info only once this flag is turned off
813 IF (NOT p_card_instrument.Info_Only_Flag = 'Y') THEN
814 l_info_only := p_card_instrument.Info_Only_Flag;
815 END IF;
816 -- validate billing address information
820 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
817 IF (NOT Validate_Card_Billing(FND_API.G_TRUE,p_card_instrument)) THEN
818 x_response.Result_Code := G_RC_INVALID_ADDRESS;
819 iby_fndcpt_common_pub.Prepare_Result
821 RETURN;
822 END IF;
823
824 Update_Card
825 (FND_API.G_FALSE, p_card_instrument.Card_Id, p_card_instrument.Owner_Id,
826 p_card_instrument.Card_Holder_Name,
827 p_card_instrument.Billing_Address_Id,
828 p_card_instrument.Address_Type,
829 p_card_instrument.Billing_Postal_Code,
830 p_card_instrument.Billing_Address_Territory,
831 p_card_instrument.Expiration_Date, p_card_instrument.Instrument_Type,
832 p_card_instrument.FI_Name, p_card_instrument.Single_Use_Flag,
833 l_info_only, p_card_instrument.Card_Purpose,
834 p_card_instrument.Card_Description, p_card_instrument.Active_Flag,
835 p_card_instrument.Inactive_Date,
836 p_card_instrument.attribute_category,
837 p_card_instrument.attribute1, p_card_instrument.attribute2,
838 p_card_instrument.attribute3, p_card_instrument.attribute4,
839 p_card_instrument.attribute5, p_card_instrument.attribute6,
840 p_card_instrument.attribute7, p_card_instrument.attribute8,
841 p_card_instrument.attribute9, p_card_instrument.attribute10,
842 p_card_instrument.attribute11, p_card_instrument.attribute12,
843 p_card_instrument.attribute13, p_card_instrument.attribute14,
844 p_card_instrument.attribute15,
845 lx_result_code);
846
847
848 x_response.Result_Code :=
849 NVL(lx_result_code,IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS);
850 iby_fndcpt_common_pub.Prepare_Result
851 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
852
853 IF FND_API.To_Boolean(p_commit) THEN
854 COMMIT;
855 END IF;
856
857 EXCEPTION
858
859 WHEN FND_API.G_EXC_ERROR THEN
860 ROLLBACK TO Update_Card;
861 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
862 debug_level => FND_LOG.LEVEL_ERROR,
863 module => G_DEBUG_MODULE || l_module);
864 x_return_status := FND_API.G_RET_STS_ERROR ;
865 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
866 p_data => x_msg_data
867 );
868 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
869 ROLLBACK TO Update_Card;
870 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
871 debug_level => FND_LOG.LEVEL_UNEXPECTED,
872 module => G_DEBUG_MODULE || l_module);
873 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
874 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
875 p_data => x_msg_data
876 );
877 WHEN OTHERS THEN
878 ROLLBACK TO Update_Card;
879 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
880 debug_level => FND_LOG.LEVEL_UNEXPECTED,
881 module => G_DEBUG_MODULE || l_module);
882
883 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
884
885 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
886 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
887 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
888 END IF;
889
890 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
891 p_data => x_msg_data
892 );
893
894 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
895 debug_level => FND_LOG.LEVEL_UNEXPECTED,
896 module => G_DEBUG_MODULE || l_module);
897 iby_debug_pub.add(debug_msg => 'Exit Exception',
898 debug_level => FND_LOG.LEVEL_UNEXPECTED,
899 module => G_DEBUG_MODULE || l_module);
900
901 END Update_Card;
902
903 PROCEDURE Remask_Instruments
904 (
905 p_commit IN VARCHAR2 := FND_API.G_TRUE
906 )
907 IS
908 l_card_number iby_paymentcard.card_number%TYPE;
909 lx_mask_digits iby_paymentcard.card_number%TYPE;
910 lx_mask_option iby_paymentcard.card_mask_setting%TYPE;
911 lx_unmask_len iby_paymentcard.card_unmask_length%TYPE;
912
913 CURSOR c_card
914 (ci_mask_option iby_paymentcard.card_mask_setting%TYPE,
915 ci_unmask_len iby_paymentcard.card_unmask_length%TYPE
916 )
917 IS
918 SELECT c.instrid, c.card_number,
919 c.card_number_length card_len
920 FROM iby_paymentcard c
921 WHERE ( (NVL(card_unmask_length,-1) <> ci_unmask_len) OR
922 (NVL(card_mask_setting,' ') <> ci_mask_option)
923 );
924 BEGIN
925
926 IF (c_card%ISOPEN) THEN CLOSE c_card; END IF;
927
928 Get_Mask_Settings(lx_mask_option,lx_unmask_len);
929
930 FOR c_card_rec IN c_card(lx_mask_option,lx_unmask_len) LOOP
931 UPDATE iby_paymentcard
932 SET
933 masked_card_number =
934 Mask_Card_Number(c_card_rec.card_number,lx_mask_option,lx_unmask_len),
935 card_mask_setting = lx_mask_option,
936 card_unmask_length = lx_unmask_len,
937 object_version_number = object_version_number + 1,
938 last_update_date = sysdate,
939 last_updated_by = fnd_global.user_id,
940 last_update_login = fnd_global.login_id
941 WHERE (instrid = c_card_rec.instrid);
942 END LOOP;
943
947 END Remask_Instruments;
944 IF FND_API.to_Boolean( p_commit ) THEN
945 COMMIT;
946 END IF;
948
949
950 PROCEDURE Query_Card
951 (p_card_id IN iby_paymentcard.instrid%TYPE,
952 x_owner_id OUT NOCOPY iby_paymentcard.card_owner_id%TYPE,
953 x_holder_name OUT NOCOPY iby_paymentcard.chname%TYPE,
954 x_billing_address_id OUT NOCOPY iby_paymentcard.addressid%TYPE,
955 x_billing_address1 OUT NOCOPY hz_locations.address1%TYPE,
956 x_billing_address2 OUT NOCOPY hz_locations.address2%TYPE,
957 x_billing_address3 OUT NOCOPY hz_locations.address3%TYPE,
958 x_billing_city OUT NOCOPY hz_locations.city%TYPE,
959 x_billing_county OUT NOCOPY hz_locations.county%TYPE,
960 x_billing_state OUT NOCOPY hz_locations.state%TYPE,
961 x_billing_zip OUT NOCOPY hz_locations.postal_code%TYPE,
965 x_expiry_date OUT NOCOPY iby_paymentcard.expirydate%TYPE,
962 x_billing_country OUT NOCOPY hz_locations.country%TYPE,
963 x_card_number OUT NOCOPY iby_paymentcard.card_number%TYPE,
964 x_masked_card_number OUT NOCOPY iby_paymentcard.card_number%TYPE,
966 x_instr_type OUT NOCOPY iby_paymentcard.instrument_type%TYPE,
967 x_issuer OUT NOCOPY iby_paymentcard.card_issuer_code%TYPE,
968 x_fi_name OUT NOCOPY iby_paymentcard.finame%TYPE,
969 x_single_use OUT NOCOPY iby_paymentcard.single_use_flag%TYPE,
970 x_info_only OUT NOCOPY iby_paymentcard.information_only_flag%TYPE,
971 x_purpose OUT NOCOPY iby_paymentcard.card_purpose%TYPE,
972 x_desc OUT NOCOPY iby_paymentcard.description%TYPE,
973 x_active_flag OUT NOCOPY iby_paymentcard.active_flag%TYPE,
974 x_inactive_date OUT NOCOPY iby_paymentcard.inactive_date%TYPE,
975 x_result_code OUT NOCOPY VARCHAR2
976 )
977 IS
978
979 l_err_code VARCHAR2(200);
980 l_instr_found BOOLEAN;
981
982 CURSOR c_paymentcard(ci_instr_id iby_paymentcard.instrid%TYPE)
983 IS
984 SELECT
985 c.card_owner_id, c.chname, c.addressid,
986 l.address1, l.address2, l.address3, l.city, l.county,
987 l.state, l.postal_code, l.country,
988 c.card_number, c.masked_card_number, c.expirydate, c.instrument_type,
989 c.card_issuer_code, c.finame,
990 c.single_use_flag, c.information_only_flag, c.card_purpose,
991 c.description, c.active_flag, c.inactive_date
992 FROM iby_paymentcard c, hz_party_site_uses su, hz_party_sites s,
993 hz_locations l
994 WHERE (instrid = ci_instr_id)
995 AND (c.addressid = su.party_site_use_id(+))
996 AND (su.party_site_id = s.party_site_id(+))
997 AND (s.location_id = l.location_id(+));
998
999 BEGIN
1000
1001 IF( c_paymentcard%ISOPEN ) THEN
1002 CLOSE c_paymentcard;
1003 END IF;
1004
1005 OPEN c_paymentcard(p_card_id);
1006 FETCH c_paymentcard
1007 INTO x_owner_id, x_holder_name, x_billing_address_id,
1008 x_billing_address1, x_billing_address2, x_billing_address3,
1009 x_billing_city, x_billing_county, x_billing_state, x_billing_zip,
1010 x_billing_country, x_card_number, x_masked_card_number, x_expiry_date, x_instr_type,
1011 x_issuer, x_fi_name, x_single_use,
1012 x_info_only, x_purpose, x_desc, x_active_flag, x_inactive_date;
1013
1014 l_instr_found := (NOT c_paymentcard%NOTFOUND);
1015 CLOSE c_paymentcard;
1016
1017 IF (NOT l_instr_found) THEN
1018 raise_application_error(-20000,'IBY_20512', FALSE);
1019 END IF;
1020
1021
1022 END Query_Card;
1023
1024 PROCEDURE Card_Exists
1025 (
1026 p_api_version IN NUMBER,
1027 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1028 x_return_status OUT NOCOPY VARCHAR2,
1029 x_msg_count OUT NOCOPY NUMBER,
1030 x_msg_data OUT NOCOPY VARCHAR2,
1031 p_owner_id NUMBER,
1032 p_card_number VARCHAR2,
1033 x_card_instrument OUT NOCOPY PaymentCard_rec_type,
1034 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type,
1035 p_card_instr_type IN VARCHAR2 DEFAULT NULL
1036 )
1037 IS
1038 l_api_version CONSTANT NUMBER := 1.0;
1039 l_module CONSTANT VARCHAR2(30) := 'Card_Exists';
1040 l_prev_msg_count NUMBER;
1041
1042 l_card_id iby_paymentcard.instrid%TYPE;
1043
1044 l_char_allowed VARCHAR2(1) := 'N';
1045 lx_return_status VARCHAR2(1);
1046 lx_msg_count NUMBER;
1047 lx_msg_data VARCHAR2(200);
1048 lx_card_number iby_paymentcard.card_number%TYPE;
1049 lx_result IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1050
1051 CURSOR p_card
1052 (pi_card_owner IN iby_paymentcard.card_owner_id%TYPE
1053 )
1054 IS
1055 SELECT instrid
1056 FROM iby_paymentcard
1057 WHERE ( (card_owner_id = NVL(pi_card_owner,card_owner_id))
1058 OR (card_owner_id IS NULL AND pi_card_owner IS NULL) )
1059 AND (NVL(single_use_flag,'N')='N');
1060 BEGIN
1064 END IF;
1061
1062 IF (p_card%ISOPEN) THEN
1063 CLOSE p_card;
1065
1066 IF NOT FND_API.Compatible_API_Call (l_api_version,
1067 p_api_version,
1068 l_module,
1069 G_PKG_NAME)
1070 THEN
1071 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
1072 debug_level => FND_LOG.LEVEL_ERROR,
1073 module => G_DEBUG_MODULE || l_module);
1074 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1075 FND_MSG_PUB.Add;
1076 RAISE FND_API.G_EXC_ERROR;
1077 END IF;
1078
1079 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1080 FND_MSG_PUB.initialize;
1081 END IF;
1082 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
1083
1084 IF (lx_card_number IS NULL) THEN
1085 x_response.Result_Code := G_RC_INVALID_CARD_NUMBER;
1086 iby_fndcpt_common_pub.Prepare_Result
1087 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1088 RETURN;
1089 END IF;
1090
1091 OPEN p_card(p_owner_id);
1092 FETCH p_card INTO l_card_id;
1093 CLOSE p_card;
1094
1095 Get_Card
1096 (
1097 1.0,
1098 FND_API.G_FALSE,
1099 x_return_status,
1100 x_msg_count,
1101 x_msg_data,
1102 l_card_id,
1103 x_card_instrument,
1104 lx_result
1105 );
1106 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
1107
1108 iby_fndcpt_common_pub.Prepare_Result
1109 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1110
1111 EXCEPTION
1112
1113 WHEN FND_API.G_EXC_ERROR THEN
1114
1115 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1116 debug_level => FND_LOG.LEVEL_ERROR,
1117 module => G_DEBUG_MODULE || l_module);
1118 x_return_status := FND_API.G_RET_STS_ERROR ;
1119 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1120 p_data => x_msg_data
1121 );
1122 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1123
1124 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1125 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1126 module => G_DEBUG_MODULE || l_module);
1127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1128 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1129 p_data => x_msg_data
1130 );
1131 WHEN OTHERS THEN
1132
1133 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1134 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1135 module => G_DEBUG_MODULE || l_module);
1136
1137 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
1138
1139 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1140 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1141 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
1142 END IF;
1143
1144 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1145 p_data => x_msg_data
1146 );
1147
1148 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
1149 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1150 module => G_DEBUG_MODULE || l_module);
1151 iby_debug_pub.add(debug_msg => 'Exit Exception',
1152 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1153 module => G_DEBUG_MODULE || l_module);
1154 END Card_Exists;
1155
1156 PROCEDURE Get_Card
1157 (
1158 p_api_version IN NUMBER,
1159 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1160 x_return_status OUT NOCOPY VARCHAR2,
1161 x_msg_count OUT NOCOPY NUMBER,
1162 x_msg_data OUT NOCOPY VARCHAR2,
1163 p_card_id NUMBER,
1164 x_card_instrument OUT NOCOPY PaymentCard_rec_type,
1165 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1166 )
1167 IS
1168 l_api_version CONSTANT NUMBER := 1.0;
1169 l_module CONSTANT VARCHAR2(30) := 'Get_Card';
1170 l_prev_msg_count NUMBER;
1171
1172 l_card_count NUMBER;
1173
1174 CURSOR p_card(pi_card_id IN iby_paymentcard.instrid%TYPE)
1175 IS
1176 SELECT card_owner_id, chname, addressid, masked_card_number, expirydate,
1177 instrument_type,
1178 card_issuer_code, finame, single_use_flag,
1179 information_only_flag, card_purpose, description, inactive_date
1180 FROM iby_paymentcard
1181 WHERE (instrid = pi_card_id);
1182 BEGIN
1183 IF (p_card%ISOPEN) THEN
1184 CLOSE p_card;
1185 END IF;
1186
1187 IF NOT FND_API.Compatible_API_Call (l_api_version,
1188 p_api_version,
1189 l_module,
1190 G_PKG_NAME)
1191 THEN
1192 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
1193 debug_level => FND_LOG.LEVEL_ERROR,
1194 module => G_DEBUG_MODULE || l_module);
1195 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1196 FND_MSG_PUB.Add;
1197 RAISE FND_API.G_EXC_ERROR;
1198 END IF;
1199
1200 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1201 FND_MSG_PUB.initialize;
1202 END IF;
1203 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
1204
1205 OPEN p_card(p_card_id);
1206 FETCH p_card INTO x_card_instrument.Owner_Id, x_card_instrument.Card_Holder_Name,
1207 x_card_instrument.Billing_Address_Id, x_card_instrument.Card_Number,
1208 x_card_instrument.Expiration_Date, x_card_instrument.Instrument_Type,
1209 x_card_instrument.Card_Issuer,
1210 x_card_instrument.FI_Name, x_card_instrument.Single_Use_Flag,
1211 x_card_instrument.Info_Only_Flag, x_card_instrument.Card_Purpose,
1212 x_card_instrument.Card_Description, x_card_instrument.Inactive_Date;
1213
1214 IF (p_card%NOTFOUND) THEN
1215 x_response.Result_Code := G_RC_INVALID_CARD_ID;
1216 ELSE
1217 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
1218 x_card_instrument.Card_Id := p_card_id;
1219 END IF;
1220
1221 iby_fndcpt_common_pub.Prepare_Result
1222 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1223
1224 EXCEPTION
1225
1226 WHEN FND_API.G_EXC_ERROR THEN
1227
1228 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1229 debug_level => FND_LOG.LEVEL_ERROR,
1230 module => G_DEBUG_MODULE || l_module);
1231 x_return_status := FND_API.G_RET_STS_ERROR ;
1232 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1233 p_data => x_msg_data
1234 );
1235 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1236
1237 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1238 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1239 module => G_DEBUG_MODULE || l_module);
1240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1241 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1242 p_data => x_msg_data
1243 );
1244 WHEN OTHERS THEN
1245
1246 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1247 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1248 module => G_DEBUG_MODULE || l_module);
1249
1250 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
1251
1252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1253 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1254 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
1255 END IF;
1256
1257 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1258 p_data => x_msg_data
1259 );
1260
1261 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
1262 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1263 module => G_DEBUG_MODULE || l_module);
1264 iby_debug_pub.add(debug_msg => 'Exit Exception',
1265 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1266 module => G_DEBUG_MODULE || l_module);
1267 END Get_Card;
1268
1269 END iby_paymentcard_pkg;