[Home] [Help]
PACKAGE BODY: APPS.IBY_FNDCPT_SETUP_PUB
Source
1 PACKAGE BODY IBY_FNDCPT_SETUP_PUB AS
2 /*$Header: ibyfcstb.pls 120.63.12020000.8 2013/03/08 06:49:22 gmamidip ship $*/
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_FNDCPT_SETUP_PUB';
6
7 G_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'iby.plsql.IBY_FNDCPT_SETUP_PUB';
8 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
10
11
12 PROCEDURE print_debuginfo(
13 p_debug_text IN VARCHAR2,
14 p_level IN VARCHAR2,
15 p_module IN VARCHAR2
16 )
17 IS
18 PRAGMA AUTONOMOUS_TRANSACTION;
19
20 BEGIN
21
22 /*
23 * If FND_GLOBAL.conc_request_id is -1, it implies that
24 * this method has not been invoked via the concurrent
25 * manager. In that case, write to apps log else write
26 * to concurrent manager log file.
27 */
28 /*Remove this 2 lines after debugging*/
29 -- INSERT INTO TEMP_IBY_LOGS VALUES (p_module || ': '
30 -- || p_debug_text, sysdate);
31 -- commit;
32
33 IF (FND_GLOBAL.conc_request_id = -1) THEN
34
35 /*
36 * OPTION I:
37 * Write debug text to the common application log file.
38 */
39 IBY_DEBUG_PUB.add(
40 substr(RPAD(p_module,55) || ' : ' || p_debug_text, 0, 150),
41 FND_LOG.G_CURRENT_RUNTIME_LEVEL,
42 'iby.plsql.IBY_FNDCPT_SETUP_PUB'
43 );
44
45 /*
46 * OPTION II:
47 * Write debug text to DBMS output file.
48 */
49 --DBMS_OUTPUT.PUT_LINE(substr(RPAD(p_module,40)||' : '||
50 -- p_debug_text, 0, 150));
51
52 /*
53 * OPTION III:
54 * Write debug text to temporary table.
55 *
56 * Use this script to create a debug table.
57 * CREATE TABLE TEMP_IBY_LOGS(TEXT VARCHAR2(4000), TIME DATE);
58 */
59 /* uncomment these two lines for debugging */
60 --INSERT INTO TEMP_IBY_LOGS VALUES (p_module || ': '
61 -- || p_debug_text, sysdate);
62
63 --COMMIT;
64
65 ELSE
66
67 /*
68 * OPTION I:
69 * Write debug text to the concurrent manager log file.
70 */
71 FND_FILE.PUT_LINE(FND_FILE.LOG, p_module || ': ' || p_debug_text);
72
73 /*
74 * OPTION II:
75 * Write debug text to DBMS output file.
76 */
77 --DBMS_OUTPUT.PUT_LINE(substr(RPAD(p_module,40)||' : '||
78 -- p_debug_text, 0, 150));
79
80 /*
81 * OPTION III:
82 * Write debug text to temporary table.
83 *
84 * Use this script to create a debug table.
85 * CREATE TABLE TEMP_IBY_LOGS(TEXT VARCHAR2(4000), TIME DATE);
86 */
87 /* uncomment these two lines for debugging */
88 --INSERT INTO TEMP_IBY_LOGS VALUES (p_module || ': '
89 -- || p_debug_text, sysdate);
90
91 --COMMIT;
92
93 END IF;
94
95 END print_debuginfo;
96
97
98
99 FUNCTION Exists_Pmt_Channel(p_pmt_channel IN VARCHAR2)
100 RETURN BOOLEAN
101 IS
102 l_code VARCHAR2(30);
103 l_exists BOOLEAN;
104
105 CURSOR c_channel(ci_channel_code IN iby_fndcpt_pmt_chnnls_b.payment_channel_code%TYPE)
106 IS
107 SELECT payment_channel_code
108 FROM iby_fndcpt_pmt_chnnls_b
109 WHERE (payment_channel_code = ci_channel_code)
110 AND (NVL(inactive_date,SYSDATE-10)<SYSDATE);
111 BEGIN
112
113 IF (c_channel%ISOPEN) THEN
114 CLOSE c_channel;
115 END IF;
116 OPEN c_channel(p_pmt_channel);
117 FETCH c_channel INTO l_code;
118 l_exists := NOT c_channel%NOTFOUND;
119 CLOSE c_channel;
120
121 RETURN l_exists;
122
123 END Exists_Pmt_Channel;
124
125 FUNCTION Exists_Instr(p_instr IN PmtInstrument_rec_type)
126 RETURN BOOLEAN
127 IS
128
129 l_instr_count NUMBER := 0;
130
131 CURSOR c_creditcard(ci_instrid IN iby_creditcard.instrid%TYPE)
132 IS
133 SELECT COUNT(instrid)
134 FROM iby_creditcard
135 WHERE (instrid = ci_instrid);
136
137 CURSOR c_bankaccount
138 (ci_instrid IN iby_ext_bank_accounts.ext_bank_account_id%TYPE)
139 IS
140 SELECT COUNT(ext_bank_account_id)
141 FROM iby_ext_bank_accounts
142 WHERE (ext_bank_account_id = ci_instrid);
143
144 BEGIN
145
146 IF (c_creditcard%ISOPEN) THEN
147 CLOSE c_creditcard;
148 END IF;
149 IF (c_bankaccount%ISOPEN) THEN
150 CLOSE c_bankaccount;
151 END IF;
152
153 IF (p_instr.Instrument_Type = IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_CREDITCARD)
154 THEN
155 OPEN c_creditcard(p_instr.Instrument_Id);
156 FETCH c_creditcard INTO l_instr_count;
157 CLOSE c_creditcard;
158 ELSIF (p_instr.Instrument_Type = IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_BANKACCT)
159 THEN
160 OPEN c_bankaccount(p_instr.Instrument_Id);
161 FETCH c_bankaccount INTO l_instr_count;
162 CLOSE c_bankaccount;
163 END IF;
164
165 IF (l_instr_count < 1) THEN
166 RETURN FALSE;
167 ELSE
168 RETURN TRUE;
169 END IF;
170
171 END Exists_Instr;
172
173 -- Validates the billing address passed for a credit card instrument
174 FUNCTION Validate_CC_Billing
175 ( p_is_update IN VARCHAR2, p_creditcard IN CreditCard_rec_type )
176 RETURN BOOLEAN
177 IS
178
179 lx_return_status VARCHAR2(1);
180 lx_msg_count NUMBER;
181 lx_msg_data VARCHAR2(3000);
182 lx_result IBY_FNDCPT_COMMON_PUB.Result_rec_type;
183 lx_channel_attribs PmtChannel_AttribUses_rec_type;
184
185 l_addressid iby_creditcard.addressid%TYPE;
186 l_billing_zip iby_creditcard.billing_addr_postal_code%TYPE;
187 l_billing_terr iby_creditcard.bill_addr_territory_code%TYPE;
188
189 BEGIN
190
191 IF (p_creditcard.Info_Only_Flag = 'Y') THEN
192 RETURN TRUE;
193 END IF;
194
195 l_addressid := p_creditcard.Billing_Address_Id;
196 l_billing_zip := p_creditcard.Billing_Postal_Code;
197 l_billing_terr := p_creditcard.Billing_Address_Territory;
198
199 IF FND_API.to_Boolean(p_is_update) THEN
200 IF (l_addressid = FND_API.G_MISS_NUM) THEN
201 l_addressid := NULL;
202 ELSIF (l_addressid IS NULL) THEN
203 l_addressid := FND_API.G_MISS_NUM;
204 END IF;
205 IF (l_billing_zip = FND_API.G_MISS_CHAR) THEN
206 l_billing_zip := NULL;
207 ELSIF (l_billing_zip IS NULL) THEN
208 l_billing_zip := FND_API.G_MISS_CHAR;
209 END IF;
210 IF (l_billing_terr = FND_API.G_MISS_CHAR) THEN
211 l_billing_terr := NULL;
212 ELSIF (l_billing_terr IS NULL) THEN
213 l_billing_terr := FND_API.G_MISS_CHAR;
214 END IF;
215 END IF;
216
217 IF ( (NOT (l_addressid IS NULL OR l_addressid = FND_API.G_MISS_NUM))
218 AND
219 (NOT (l_billing_zip IS NULL OR l_billing_zip = FND_API.G_MISS_CHAR))
220 )
221 THEN
222 RETURN FALSE;
223 END IF;
224
225 IF ( (NOT (l_billing_zip IS NULL OR l_billing_zip = FND_API.G_MISS_CHAR))
226 AND (l_billing_terr IS NULL OR l_billing_terr = FND_API.G_MISS_CHAR)
227 )
228 THEN
229 RETURN FALSE;
230 ELSIF ( (NOT (l_billing_terr IS NULL OR l_billing_terr = FND_API.G_MISS_CHAR))
231
232 AND (l_billing_zip IS NULL OR l_billing_zip = FND_API.G_MISS_CHAR)
233 )
234 THEN
235 RETURN FALSE;
236 END IF;
237
238 Get_Payment_Channel_Attribs
239 (1.0, FND_API.G_FALSE, lx_return_status, lx_msg_count, lx_msg_data,
240 G_CHANNEL_CREDIT_CARD, lx_channel_attribs, lx_result);
241
242 IF ((lx_channel_attribs.Instr_Billing_Address = G_CHNNL_ATTRIB_USE_REQUIRED)
243 AND ((l_addressid IS NULL) AND (l_billing_zip IS NULL))
244 )
245 THEN
246 RETURN FALSE;
247 END IF;
248
249 IF ((lx_channel_attribs.Instr_Billing_Address = G_CHNNL_ATTRIB_USE_DISABLED)
250 AND ((NOT l_addressid IS NULL) OR (NOT l_billing_zip IS NULL))
251 )
252 THEN
253 RETURN FALSE;
254 END IF;
255
256 RETURN TRUE;
257 END Validate_CC_Billing;
258
259 PROCEDURE Get_Payer_Id
260 (
261 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
262 p_validation_level IN VARCHAR2,
263 x_payer_level OUT NOCOPY VARCHAR2,
264 x_payer_id OUT NOCOPY iby_external_payers_all.ext_payer_id%TYPE,
265 x_payer_attribs OUT NOCOPY PayerAttributes_rec_type
266 )
267 IS
268
269 CURSOR c_payer
270 (ci_pmt_function IN p_payer.Payment_Function%TYPE,
271 ci_party_id IN p_payer.Party_Id%TYPE,
272 ci_account_id IN p_payer.Cust_Account_Id%TYPE,
273 ci_site_id IN p_payer.Account_Site_Id%TYPE,
274 ci_org_type IN p_payer.Org_Type%TYPE,
275 ci_org_id IN p_payer.Org_Id%TYPE,
276 ci_payer_level IN VARCHAR2)
277 IS
278 SELECT ext_payer_id, bank_charge_bearer_code, dirdeb_instruction_code
279 FROM iby_external_payers_all
280 WHERE (payment_function = ci_pmt_function)
281 AND (party_id = ci_party_id)
282 AND ((cust_account_id = ci_account_id)
283 OR (cust_account_id IS NULL AND ci_account_id IS NULL))
284 AND ((org_type = ci_org_type AND org_id = ci_org_id)
285 OR (org_type IS NULL AND org_id IS NULL AND ci_org_type IS NULL AND ci_org_id IS NULL))
286 AND ((acct_site_use_id = ci_site_id)
287 OR (acct_site_use_id IS NULL AND ci_site_id IS NULL));
288
289 BEGIN
290
291 IF (c_payer%ISOPEN) THEN
292 CLOSE c_payer;
293 END IF;
294
295 x_payer_level :=
296 IBY_FNDCPT_COMMON_PUB.Validate_Payer(p_payer,p_validation_level);
297
298 IF (x_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
299 x_payer_id := NULL;
300 RETURN;
301 END IF;
302
303 OPEN c_payer(p_payer.Payment_Function, p_payer.Party_Id,
304 p_payer.Cust_Account_Id, p_payer.Account_Site_Id, p_payer.Org_Type,
305 p_payer.Org_Id, x_payer_level);
306 FETCH c_payer INTO x_payer_id, x_payer_attribs.Bank_Charge_Bearer,
307 x_payer_attribs.DirectDebit_BankInstruction;
308 IF c_payer%NOTFOUND THEN x_payer_id := NULL; END IF;
309 CLOSE c_payer;
310
311 END Get_Payer_Id;
312
313
314 PROCEDURE Set_Payer_Attributes
315 (
316 p_api_version IN NUMBER,
317 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
318 p_commit IN VARCHAR2 := FND_API.G_TRUE,
319 x_return_status OUT NOCOPY VARCHAR2,
320 x_msg_count OUT NOCOPY NUMBER,
321 x_msg_data OUT NOCOPY VARCHAR2,
322 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
323 p_payer_attributes IN PayerAttributes_rec_type,
324 x_payer_attribs_id OUT NOCOPY NUMBER,
325 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
326 )
327 IS
328 l_api_version CONSTANT NUMBER := 1.0;
329 l_module CONSTANT VARCHAR2(30) := 'Set_Payer_Attributes';
330 l_payer_level VARCHAR2(30);
331 l_payer_attribs PayerAttributes_rec_type;
332 l_prev_msg_count NUMBER;
333
334 BEGIN
335
336 IF NOT FND_API.Compatible_API_Call (l_api_version,
337 p_api_version,
338 l_module,
339 G_PKG_NAME)
340 THEN
341 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
342 debug_level => FND_LOG.LEVEL_ERROR,
343 module => G_DEBUG_MODULE || l_module);
344 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
345 FND_MSG_PUB.Add;
346 RAISE FND_API.G_EXC_ERROR;
347 END IF;
348
349 IF FND_API.to_Boolean( p_init_msg_list ) THEN
350 FND_MSG_PUB.initialize;
351 END IF;
352 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
353
354 Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
355 l_payer_level,x_payer_attribs_id,l_payer_attribs);
356
357 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
358 x_response.Result_Code := l_payer_level;
359 ELSIF (x_payer_attribs_id IS NULL) THEN
360
361 SELECT iby_external_payers_all_s.nextval
362 INTO x_payer_attribs_id
363 FROM dual;
364
365 INSERT INTO iby_external_payers_all
366 (ext_payer_id, payment_function, party_id, org_type, org_id,
367 cust_account_id, acct_site_use_id, bank_charge_bearer_code,
368 dirdeb_instruction_code, created_by, creation_date, last_updated_by,
369 last_update_date, last_update_login, object_version_number,
370 debit_advice_delivery_method, debit_advice_email, debit_advice_fax
371 )
372 VALUES
373 (x_payer_attribs_id, p_payer.Payment_Function,
374 p_payer.Party_Id, p_payer.Org_Type, p_payer.Org_Id,
375 p_payer.Cust_Account_Id, p_payer.Account_Site_Id,
376 p_payer_attributes.Bank_Charge_Bearer,
377 p_payer_attributes.DirectDebit_BankInstruction,
378 fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
379 fnd_global.login_id, 1 ,p_payer.Debit_Advice_Delivery_Method,
380 p_payer.Debit_Advice_Email, p_payer.Debit_Advice_Fax
381 );
382 ELSE
383 UPDATE iby_external_payers_all
384 SET
385 dirdeb_instruction_code =
386 DECODE(p_payer_attributes.DirectDebit_BankInstruction,
387 FND_API.G_MISS_CHAR,NULL, p_payer_attributes.DirectDebit_BankInstruction),
388 bank_charge_bearer_code =
389 DECODE(p_payer_attributes.Bank_Charge_Bearer,
390 FND_API.G_MISS_CHAR,NULL, p_payer_attributes.Bank_Charge_Bearer),
391 last_updated_by = fnd_global.user_id,
392 last_update_date = SYSDATE,
393 last_update_login = fnd_global.login_id,
394 object_version_number = object_version_number + 1,
395 debit_advice_delivery_method = p_payer.Debit_Advice_Delivery_Method,
396 debit_advice_email = p_payer.Debit_Advice_Email,
397 debit_advice_fax = p_payer.Debit_Advice_Fax
398 WHERE (ext_payer_id = x_payer_attribs_id);
399 END IF;
400
401 IF FND_API.To_Boolean(p_commit) THEN
402 COMMIT;
403 END IF;
404
405 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
406 iby_fndcpt_common_pub.Prepare_Result
407 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
408
409 EXCEPTION
410
411 WHEN FND_API.G_EXC_ERROR THEN
412
413 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
414 debug_level => FND_LOG.LEVEL_ERROR,
415 module => G_DEBUG_MODULE || l_module);
416 x_return_status := FND_API.G_RET_STS_ERROR ;
417 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
418 p_data => x_msg_data
419 );
420 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
421
422 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
423 debug_level => FND_LOG.LEVEL_UNEXPECTED,
424 module => G_DEBUG_MODULE || l_module);
425 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
426 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
427 p_data => x_msg_data
428 );
429 WHEN OTHERS THEN
430
431 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
432 debug_level => FND_LOG.LEVEL_UNEXPECTED,
433 module => G_DEBUG_MODULE || l_module);
434
435 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
436
437 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
438 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
439 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
440 END IF;
441
442 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
443 p_data => x_msg_data
444 );
445 END Set_Payer_Attributes;
446
447 PROCEDURE Get_Payer_Attributes
448 (
449 p_api_version IN NUMBER,
450 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
451 x_return_status OUT NOCOPY VARCHAR2,
452 x_msg_count OUT NOCOPY NUMBER,
453 x_msg_data OUT NOCOPY VARCHAR2,
454 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
455 x_payer_attributes OUT NOCOPY PayerAttributes_rec_type,
456 x_payer_attribs_id OUT NOCOPY NUMBER,
457 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
458 )
459 IS
460 l_api_version CONSTANT NUMBER := 1.0;
461 l_module CONSTANT VARCHAR2(30) := 'Get_Payer_Attributes';
462 l_payer_level VARCHAR2(30);
463 l_prev_msg_count NUMBER;
464
465 BEGIN
466
467 IF NOT FND_API.Compatible_API_Call (l_api_version,
468 p_api_version,
469 l_module,
470 G_PKG_NAME)
471 THEN
472 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
473 debug_level => FND_LOG.LEVEL_ERROR,
474 module => G_DEBUG_MODULE || l_module);
475 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
476 FND_MSG_PUB.Add;
477 RAISE FND_API.G_EXC_ERROR;
478 END IF;
479
480 IF FND_API.to_Boolean( p_init_msg_list ) THEN
481 FND_MSG_PUB.initialize;
482 END IF;
483 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
484
485 Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
486 l_payer_level,x_payer_attribs_id,x_payer_attributes);
487 IF (x_payer_attribs_id IS NULL) THEN
488 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
489 ELSE
490 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
491 END IF;
492
493 iby_fndcpt_common_pub.Prepare_Result
494 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
495
496 EXCEPTION
497
498 WHEN FND_API.G_EXC_ERROR THEN
499
500 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
501 debug_level => FND_LOG.LEVEL_ERROR,
502 module => G_DEBUG_MODULE || l_module);
503 x_return_status := FND_API.G_RET_STS_ERROR ;
504 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
505 p_data => x_msg_data
506 );
507 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
508
509 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
510 debug_level => FND_LOG.LEVEL_UNEXPECTED,
511 module => G_DEBUG_MODULE || l_module);
512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
513 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
514 p_data => x_msg_data
515 );
516 WHEN OTHERS THEN
517
518 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
519 debug_level => FND_LOG.LEVEL_UNEXPECTED,
520 module => G_DEBUG_MODULE || l_module);
521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
522 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
523 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_module);
524 END IF;
525
526 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
527 p_data => x_msg_data
528 );
529 END Get_Payer_Attributes;
530
531 PROCEDURE Get_Payment_Channel_Attribs
532 (
533 p_api_version IN NUMBER,
534 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
535 x_return_status OUT NOCOPY VARCHAR2,
536 x_msg_count OUT NOCOPY NUMBER,
537 x_msg_data OUT NOCOPY VARCHAR2,
538 p_channel_code IN VARCHAR2,
539 x_channel_attrib_uses OUT NOCOPY PmtChannel_AttribUses_rec_type,
540 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
541 )
542 IS
543 l_api_version CONSTANT NUMBER := 1.0;
544 l_module CONSTANT VARCHAR2(30) := 'Get_Payment_Channel_Attribs';
545 l_prev_msg_count NUMBER;
546
547 CURSOR c_appl_attribs
548 (ci_pmt_channel iby_fndcpt_pmt_chnnls_b.payment_channel_code%TYPE)
549 IS
550 SELECT NVL(isec.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
551 NVL(ibill.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
552 NVL(vaflag.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
553 NVL(vacode.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
554 NVL(vadate.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
555 NVL(ponum.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
556 NVL(poline.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
557 NVL(addinfo.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL)
558 FROM iby_fndcpt_pmt_chnnls_b pc, iby_pmt_mthd_attrib_appl isec,
559 iby_pmt_mthd_attrib_appl ibill, iby_pmt_mthd_attrib_appl vaflag,
560 iby_pmt_mthd_attrib_appl vacode, iby_pmt_mthd_attrib_appl vadate,
561 iby_pmt_mthd_attrib_appl ponum, iby_pmt_mthd_attrib_appl poline,
562 iby_pmt_mthd_attrib_appl addinfo
563 WHERE (pc.payment_channel_code = ci_pmt_channel)
564 -- instrument security
565 AND (pc.payment_channel_code = isec.payment_method_code(+))
566 AND (isec.payment_flow(+) = 'FUNDS_CAPTURE')
567 AND (isec.attribute_code(+) = 'INSTR_SECURITY_CODE')
568 -- instrument billing address
569 AND (pc.payment_channel_code = ibill.payment_method_code(+))
570 AND (ibill.attribute_code(+) = 'INSTR_BILLING_ADDRESS')
571 AND (ibill.payment_flow(+) = 'FUNDS_CAPTURE')
572 -- voice auth flag
573 AND (pc.payment_channel_code = vaflag.payment_method_code(+))
574 AND (vaflag.attribute_code(+) = 'VOICE_AUTH_FLAG')
575 AND (vaflag.payment_flow(+) = 'FUNDS_CAPTURE')
576 -- voice auth code
577 AND (pc.payment_channel_code = vacode.payment_method_code(+))
578 AND (vacode.attribute_code(+) = 'VOICE_AUTH_CODE')
579 AND (vacode.payment_flow(+) = 'FUNDS_CAPTURE')
580 -- voice auth date
581 AND (pc.payment_channel_code = vadate.payment_method_code(+))
582 AND (vadate.attribute_code(+) = 'VOICE_AUTH_DATE')
583 AND (vadate.payment_flow(+) = 'FUNDS_CAPTURE')
584 -- purcharse order number
585 AND (pc.payment_channel_code = ponum.payment_method_code(+))
586 AND (ponum.attribute_code(+) = 'PO_NUMBER')
587 AND (ponum.payment_flow(+) = 'FUNDS_CAPTURE')
588 -- purchase order line
589 AND (pc.payment_channel_code = poline.payment_method_code(+))
590 AND (poline.attribute_code(+) = 'PO_LINE_NUMBER')
591 AND (poline.payment_flow(+) = 'FUNDS_CAPTURE')
592 -- additional info
593 AND (pc.payment_channel_code = addinfo.payment_method_code(+))
594 AND (addinfo.attribute_code(+) = 'ADDITIONAL_INFO')
595 AND (addinfo.payment_flow(+) = 'FUNDS_CAPTURE');
596
597 BEGIN
598
599 IF (c_appl_attribs%ISOPEN) THEN CLOSE c_appl_attribs; END IF;
600
601 IF NOT FND_API.Compatible_API_Call (l_api_version,
602 p_api_version,
603 l_module,
604 G_PKG_NAME)
605 THEN
606 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
607 debug_level => FND_LOG.LEVEL_ERROR,
608 module => G_DEBUG_MODULE || l_module);
609 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
610 FND_MSG_PUB.Add;
611 RAISE FND_API.G_EXC_ERROR;
612 END IF;
613
614 IF FND_API.to_Boolean( p_init_msg_list ) THEN
615 FND_MSG_PUB.initialize;
616 END IF;
617 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
618
619 OPEN c_appl_attribs(p_channel_code);
620 FETCH c_appl_attribs INTO
621 x_channel_attrib_uses.Instr_SecCode_Use,
622 x_channel_attrib_uses.Instr_Billing_Address,
623 x_channel_attrib_uses.Instr_VoiceAuthFlag_Use,
624 x_channel_attrib_uses.Instr_VoiceAuthCode_Use,
625 x_channel_attrib_uses.Instr_VoiceAuthDate_Use,
626 x_channel_attrib_uses.PO_Number_Use,
627 x_channel_attrib_uses.PO_Line_Number_Use,
628 x_channel_attrib_uses.AddInfo_Use;
629
630 IF (c_appl_attribs%NOTFOUND) THEN
631 x_response.Result_Code := G_RC_INVALID_CHNNL;
632 ELSE
633 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
634 END IF;
635
636 CLOSE c_appl_attribs;
637
638 iby_fndcpt_common_pub.Prepare_Result
639 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
640
641 EXCEPTION
642
643 WHEN FND_API.G_EXC_ERROR THEN
644
645 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
646 debug_level => FND_LOG.LEVEL_ERROR,
647 module => G_DEBUG_MODULE || l_module);
648 x_return_status := FND_API.G_RET_STS_ERROR ;
649 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
650 p_data => x_msg_data
651 );
652 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
653
654 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
655 debug_level => FND_LOG.LEVEL_UNEXPECTED,
656 module => G_DEBUG_MODULE || l_module);
657 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
658 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
659 p_data => x_msg_data
660 );
661 WHEN OTHERS THEN
662
663 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
664 debug_level => FND_LOG.LEVEL_UNEXPECTED,
665 module => G_DEBUG_MODULE || l_module);
666
667 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
668
669 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
670 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
671 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
672 END IF;
673
674 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
675 p_data => x_msg_data
676 );
677 END Get_Payment_Channel_Attribs;
678
679 PROCEDURE Set_Payer_Default_Pmt_Channel
680 (
681 p_api_version IN NUMBER,
682 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
683 p_commit IN VARCHAR2 := FND_API.G_TRUE,
684 x_return_status OUT NOCOPY VARCHAR2,
685 x_msg_count OUT NOCOPY NUMBER,
686 x_msg_data OUT NOCOPY VARCHAR2,
687 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
688 p_channel_assignment IN PmtChannelAssignment_rec_type,
689 x_assignment_id OUT NOCOPY NUMBER,
690 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
691 )
692 IS
693 l_api_version CONSTANT NUMBER := 1.0;
694 l_module CONSTANT VARCHAR2(30) := 'Set_Payer_Default_Pmt_Channel';
695 l_payer_level VARCHAR2(30);
696 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
697 l_payer_attribs PayerAttributes_rec_type;
698
699 l_result IBY_FNDCPT_COMMON_PUB.Result_rec_type;
700 l_prev_msg_count NUMBER;
701
702 CURSOR c_chnnl_assign
703 (ci_payer_id IN iby_ext_party_pmt_mthds.ext_pmt_party_id%TYPE)
704 IS
705 SELECT ext_party_pmt_mthd_id
706 FROM iby_ext_party_pmt_mthds
707 WHERE (ext_pmt_party_id = ci_payer_id)
708 AND (payment_flow = G_PMT_FLOW_FNDCPT)
709 AND (primary_flag = 'Y')
710 AND (NVL(inactive_date,SYSDATE-10)<SYSDATE);
711 BEGIN
712
713 IF (c_chnnl_assign%ISOPEN) THEN
714 CLOSE c_chnnl_assign;
715 END IF;
716
717 IF NOT FND_API.Compatible_API_Call (l_api_version,
718 p_api_version,
719 l_module,
720 G_PKG_NAME)
721 THEN
722 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
723 debug_level => FND_LOG.LEVEL_ERROR,
724 module => G_DEBUG_MODULE || l_module);
725 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
726 FND_MSG_PUB.Add;
727 RAISE FND_API.G_EXC_ERROR;
728 END IF;
729
730 IF FND_API.to_Boolean( p_init_msg_list ) THEN
731 FND_MSG_PUB.initialize;
732 END IF;
733 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
734
735 Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,l_payer_level,
736 l_payer_id,l_payer_attribs);
737
738 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
739 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
740 ELSIF (NOT Exists_Pmt_Channel(p_channel_assignment.Pmt_Channel_Code)) THEN
741 x_response.Result_Code := G_RC_INVALID_CHNNL;
742 ELSE
743
744 SAVEPOINT Set_Payer_Default_Pmt_Channel;
745
746 IF (l_payer_id IS NULL) THEN
747 IBY_FNDCPT_SETUP_PUB.Set_Payer_Attributes
748 (
749 1.0,
750 FND_API.G_FALSE,
751 FND_API.G_FALSE,
752 x_return_status,
753 x_msg_count,
754 x_msg_data,
755 p_payer,
756 l_payer_attribs,
757 l_payer_id,
758 l_result
759 );
760
761 IF (l_result.Result_Code <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS) THEN
762 x_response := l_result;
763 RETURN;
764 END IF;
765 END IF;
766
767 OPEN c_chnnl_assign(l_payer_id);
768 FETCH c_chnnl_assign INTO x_assignment_id;
769 CLOSE c_chnnl_assign;
770 IF (x_assignment_id IS NULL) THEN
771
772 SELECT iby_ext_party_pmt_mthds_s.NEXTVAL
773 INTO x_assignment_id
774 FROM DUAL;
775
776 INSERT INTO iby_ext_party_pmt_mthds
777 (ext_party_pmt_mthd_id, payment_method_code, payment_flow,
778 ext_pmt_party_id, payment_function, primary_flag, inactive_date,
779 created_by, creation_date, last_updated_by, last_update_date,
780 last_update_login, object_version_number)
781 VALUES
782 (x_assignment_id, p_channel_assignment.Pmt_Channel_Code,
783 G_PMT_FLOW_FNDCPT, l_payer_id, p_payer.Payment_Function, 'Y',
784 p_channel_assignment.Inactive_Date, fnd_global.user_id, SYSDATE,
785 fnd_global.user_id, SYSDATE, fnd_global.login_id, 1);
786
787 ELSE
788
789 UPDATE iby_ext_party_pmt_mthds
790 SET inactive_date = p_channel_assignment.Inactive_Date,
791 payment_method_code =
792 NVL(p_channel_assignment.Pmt_Channel_code,payment_method_code),
793 last_updated_by = fnd_global.user_id,
794 last_update_date = SYSDATE,
795 last_update_login = fnd_global.login_id,
796 object_version_number = object_version_number + 1
797 WHERE ext_party_pmt_mthd_id = x_assignment_id;
798
799 END IF;
800
801 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
802 END IF;
803
804
805 iby_fndcpt_common_pub.Prepare_Result
806 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
807
808 IF FND_API.To_Boolean(p_commit) THEN
809 COMMIT;
810 END IF;
811
812 EXCEPTION
813
814 WHEN FND_API.G_EXC_ERROR THEN
815 ROLLBACK TO Set_Payer_Default_Pmt_Channel;
816 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
817 debug_level => FND_LOG.LEVEL_ERROR,
818 module => G_DEBUG_MODULE || l_module);
819 x_return_status := FND_API.G_RET_STS_ERROR ;
820 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
821 p_data => x_msg_data
822 );
823 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
824 ROLLBACK TO Set_Payer_Default_Pmt_Channel;
825 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
826 debug_level => FND_LOG.LEVEL_UNEXPECTED,
827 module => G_DEBUG_MODULE || l_module);
828 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
829 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
830 p_data => x_msg_data
831 );
832 WHEN OTHERS THEN
833 ROLLBACK TO Set_Payer_Default_Pmt_Channel;
834
835 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
836 debug_level => FND_LOG.LEVEL_UNEXPECTED,
837 module => G_DEBUG_MODULE || l_module);
838
839 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
840
841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
842 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
843 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
844 END IF;
845
846 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
847 p_data => x_msg_data
848 );
849 END Set_Payer_Default_Pmt_Channel;
850
851 PROCEDURE Get_Payer_Default_Pmt_Channel
852 (
853 p_api_version IN NUMBER,
854 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
855 x_return_status OUT NOCOPY VARCHAR2,
856 x_msg_count OUT NOCOPY NUMBER,
857 x_msg_data OUT NOCOPY VARCHAR2,
858 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
859 x_channel_assignment OUT NOCOPY PmtChannelAssignment_rec_type,
860 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
861 )
862 IS
863 l_api_version CONSTANT NUMBER := 1.0;
864 l_module CONSTANT VARCHAR2(30) := 'Get_Payer_Default_Pmt_Channel';
865 l_payer_level VARCHAR2(30);
866 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
867 l_payer_attribs PayerAttributes_rec_type;
868 l_prev_msg_count NUMBER;
869
870 CURSOR c_chnnl_assign
871 (ci_payer_id IN iby_ext_party_pmt_mthds.ext_pmt_party_id%TYPE)
872 IS
873 SELECT payment_method_code, primary_flag, inactive_date
874 FROM iby_ext_party_pmt_mthds
875 WHERE (ext_pmt_party_id = ci_payer_id)
876 AND (payment_flow = G_PMT_FLOW_FNDCPT)
877 AND (primary_flag = 'Y')
878 AND (NVL(inactive_date,SYSDATE-10)<SYSDATE);
879
880 BEGIN
881
882 IF (c_chnnl_assign%ISOPEN) THEN
883 CLOSE c_chnnl_assign;
884 END IF;
885
886 IF NOT FND_API.Compatible_API_Call (l_api_version,
887 p_api_version,
888 l_module,
889 G_PKG_NAME)
890 THEN
891 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
892 debug_level => FND_LOG.LEVEL_ERROR,
893 module => G_DEBUG_MODULE || l_module);
894 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
895 FND_MSG_PUB.Add;
896 RAISE FND_API.G_EXC_ERROR;
897 END IF;
898
899 IF FND_API.to_Boolean( p_init_msg_list ) THEN
900 FND_MSG_PUB.initialize;
901 END IF;
902 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
903
904 Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
905 l_payer_level,l_payer_id,l_payer_attribs);
906
907 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
908 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
909 ELSE
910 OPEN c_chnnl_assign(l_payer_id);
911 FETCH c_chnnl_assign INTO x_channel_assignment.Pmt_Channel_Code,
912 x_channel_assignment.Default_Flag, x_channel_assignment.Inactive_Date;
913 IF (c_chnnl_assign%NOTFOUND) THEN
914 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
915 ELSE
916 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
917 END IF;
918 CLOSE c_chnnl_assign;
919 END IF;
920
921 iby_fndcpt_common_pub.Prepare_Result
922 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
923
924 EXCEPTION
925
926 WHEN FND_API.G_EXC_ERROR THEN
927
928 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
929 debug_level => FND_LOG.LEVEL_ERROR,
930 module => G_DEBUG_MODULE || l_module);
931 x_return_status := FND_API.G_RET_STS_ERROR ;
932 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
933 p_data => x_msg_data
934 );
935 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
936
937 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
938 debug_level => FND_LOG.LEVEL_UNEXPECTED,
939 module => G_DEBUG_MODULE || l_module);
940 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
941 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
942 p_data => x_msg_data
943 );
944 WHEN OTHERS THEN
945
946 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
947 debug_level => FND_LOG.LEVEL_UNEXPECTED,
948 module => G_DEBUG_MODULE || l_module);
949
950 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
951
952 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
953 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
954 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
955 END IF;
956
957 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
958 p_data => x_msg_data
959 );
960 END Get_Payer_Default_Pmt_Channel;
961
962 PROCEDURE Get_Trxn_Appl_Pmt_Channels
963 (
964 p_api_version IN NUMBER,
965 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
966 x_return_status OUT NOCOPY VARCHAR2,
967 x_msg_count OUT NOCOPY NUMBER,
968 x_msg_data OUT NOCOPY VARCHAR2,
969 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
970 p_payer_equivalency IN VARCHAR2
971 := IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
972 p_conditions IN IBY_FNDCPT_COMMON_PUB.TrxnContext_rec_type,
973 p_result_limit IN IBY_FNDCPT_COMMON_PUB.ResultLimit_rec_type,
974 x_channels OUT NOCOPY PmtChannel_tbl_type,
975 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
976 )
977 IS
978 l_api_version CONSTANT NUMBER := 1.0;
979 l_module CONSTANT VARCHAR2(30) := 'Get_Trxn_Appl_Pmt_Channels';
980 l_payer_level VARCHAR2(30);
981 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
982 l_payer_attribs PayerAttributes_rec_type;
983 l_prev_msg_count NUMBER;
984
985 l_channel_count NUMBER;
986
987 -- currently do not use any transaction values for applicability;
988 -- all system channels are applicable that are not site-wide
989 -- deactivated (end-dated)
990 --
991 CURSOR c_trxn_channels
992 (ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
993 ci_payer_level IN VARCHAR2,
994 ci_payer_equiv IN VARCHAR2)
995 IS
996 SELECT c.payment_channel_code, c.instrument_type
997 FROM iby_ext_party_pmt_mthds pm, iby_fndcpt_pmt_chnnls_b c
998 WHERE (pm.payment_method_code = c.payment_channel_code)
999 AND (NVL(pm.inactive_date,SYSDATE-10)<SYSDATE)
1000 AND (NVL(c.inactive_date,SYSDATE-10)<SYSDATE)
1001 AND (pm.payment_flow = G_PMT_FLOW_FNDCPT)
1002 AND pm.ext_pmt_party_id IN
1003 (
1004 SELECT ext_payer_id
1005 FROM iby_external_payers_all
1006 WHERE (payment_function = ci_payer.Payment_Function)
1007 AND (party_id = ci_payer.Party_Id)
1008 AND (IBY_FNDCPT_COMMON_PUB.Compare_Payer
1009 (ci_payer.org_type, ci_payer.org_id,
1010 ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
1011 ci_payer_level,ci_payer_equiv,org_type,org_id,
1012 cust_account_id,acct_site_use_id) = 'T')
1013 );
1014 BEGIN
1015
1016 IF (c_trxn_channels%ISOPEN) THEN
1017 CLOSE c_trxn_channels;
1018 END IF;
1019
1020 IF NOT FND_API.Compatible_API_Call (l_api_version,
1021 p_api_version,
1022 l_module,
1023 G_PKG_NAME)
1024 THEN
1025 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
1026 debug_level => FND_LOG.LEVEL_ERROR,
1027 module => G_DEBUG_MODULE || l_module);
1028 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1029 FND_MSG_PUB.Add;
1030 RAISE FND_API.G_EXC_ERROR;
1031 END IF;
1032
1033 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1034 FND_MSG_PUB.initialize;
1035 END IF;
1036 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
1037
1038 Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
1039 l_payer_level,l_payer_id,l_payer_attribs);
1040
1041 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
1042 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
1043 ELSE
1044 l_channel_count := 0;
1045
1046 FOR channel_rec IN c_trxn_channels(p_payer,l_payer_level,
1047 p_payer_equivalency)
1048 LOOP
1049 l_channel_count := l_channel_count + 1;
1050 x_channels(l_channel_count).Pmt_Channel_Code :=
1051 channel_rec.payment_channel_code;
1052 x_channels(l_channel_count).Instrument_Type := channel_rec.instrument_type;
1053 END LOOP;
1054
1055 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
1056 END IF;
1057
1058 iby_fndcpt_common_pub.Prepare_Result
1059 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1060
1061 EXCEPTION
1062
1063 WHEN FND_API.G_EXC_ERROR THEN
1064
1065 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1066 debug_level => FND_LOG.LEVEL_ERROR,
1067 module => G_DEBUG_MODULE || l_module);
1068 x_return_status := FND_API.G_RET_STS_ERROR ;
1069 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1070 p_data => x_msg_data
1071 );
1072 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1073
1074 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1075 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1076 module => G_DEBUG_MODULE || l_module);
1077 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1078 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1079 p_data => x_msg_data
1080 );
1081 WHEN OTHERS THEN
1082
1083 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1084 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1085 module => G_DEBUG_MODULE || l_module);
1086
1087 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
1088
1089 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1090 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1091 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
1092 END IF;
1093
1094 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1095 p_data => x_msg_data
1096 );
1097 END Get_Trxn_Appl_Pmt_Channels;
1098
1099
1100 PROCEDURE Set_Payer_Instr_Assignment
1101 (
1102 p_api_version IN NUMBER,
1103 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1104 p_commit IN VARCHAR2 := FND_API.G_TRUE,
1105 x_return_status OUT NOCOPY VARCHAR2,
1106 x_msg_count OUT NOCOPY NUMBER,
1107 x_msg_data OUT NOCOPY VARCHAR2,
1108 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
1109 p_assignment_attribs IN PmtInstrAssignment_rec_type,
1110 x_assign_id OUT NOCOPY NUMBER,
1111 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1112 )
1113 IS
1114 l_api_version CONSTANT NUMBER := 1.0;
1115 l_module CONSTANT VARCHAR2(30) := 'Set_Payer_Instr_Assignment';
1116 l_payer_level VARCHAR2(30);
1117 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
1118 l_payer_attribs PayerAttributes_rec_type;
1119 l_prev_msg_count NUMBER;
1120
1121 l_result IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1122
1123 l_assign_id NUMBER;
1124 l_instr_id NUMBER;
1125 l_priority NUMBER;
1126 l_instrtype IBY_PMT_INSTR_USES_ALL.instrument_type%TYPE;
1127
1128 l_bnkacct_owner_cnt NUMBER;
1129
1130 -- for call to TCA hook
1131 l_last_update DATE;
1132 l_op_type VARCHAR2(1);
1133 l_parent_type VARCHAR2(50);
1134 l_parent_table VARCHAR2(50);
1135 l_parent_id NUMBER;
1136 l_party_type VARCHAR2(50);
1137 l_instr_type VARCHAR2(50);
1138
1139 -- lmallick (bugfix 8586083)
1140 -- Query the instrument_type as well, because this isn't passed to the API when the
1141 -- instrument assignment is passed.
1142 CURSOR c_instr_assignment
1143 (ci_assign_id IN iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE,
1144 ci_payer_id IN iby_pmt_instr_uses_all.ext_pmt_party_id%TYPE,
1145 ci_instr_type IN iby_pmt_instr_uses_all.instrument_type%TYPE,
1146 ci_instr_id IN iby_pmt_instr_uses_all.instrument_id%TYPE
1147 )
1148 IS
1149 SELECT instrument_payment_use_id, instrument_type
1150 FROM iby_pmt_instr_uses_all
1151 WHERE (payment_flow = G_PMT_FLOW_FNDCPT)
1152 -- [lmallick] - bug# 12570664
1153 -- This filter condition on payer_id should be fired always!
1154 -- Adding this as a mandatory filter condition and commenting
1155 -- out the condition within the 'OR' block.
1156 AND (ext_pmt_party_id = ci_payer_id)
1157 AND ( (instrument_payment_use_id = NVL(ci_assign_id,-1))
1158 OR (-- ext_pmt_party_id = ci_payer_id AND
1159 instrument_type = ci_instr_type
1160 AND instrument_id = ci_instr_id
1161 )
1162 );
1163
1164 -- [lmallick]: bug# 12570664
1165 -- New cursor to fetch the assignment attributes in case these
1166 -- were not set by the calling module.
1167 CURSOR c_assign_details
1168 (ci_assign_id IN iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE)
1169 IS
1170 SELECT instrument_id, instrument_type
1171 FROM iby_pmt_instr_uses_all
1172 WHERE instrument_payment_use_id = ci_assign_id;
1173
1174
1175 CURSOR c_bnkacct_owner
1176 (ci_party_id IN iby_pmt_instr_uses_all.ext_pmt_party_id%TYPE,
1177 ci_instr_id IN iby_pmt_instr_uses_all.instrument_id%TYPE
1178 )
1179 IS
1180 SELECT count(*)
1181 FROM IBY_ACCOUNT_OWNERS
1182 WHERE EXT_BANK_ACCOUNT_ID = ci_instr_id
1183 AND ACCOUNT_OWNER_PARTY_ID = ci_party_id;
1184
1185 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
1186 BEGIN
1187 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
1188
1189 iby_debug_pub.add('p_assignment_attribs.Assignment_Id = '|| p_assignment_attribs.Assignment_Id,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1190 iby_debug_pub.add('p_assignment_attribs.Priority = '|| p_assignment_attribs.Priority,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1191 iby_debug_pub.add('p_assignment_attribs.Instrument.Instrument_Id = '|| p_assignment_attribs.Instrument.Instrument_Id,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1192 iby_debug_pub.add('p_assignment_attribs.Instrument.Instrument_Type = '|| p_assignment_attribs.Instrument.Instrument_Type,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1193
1194
1195 IF (c_instr_assignment%ISOPEN) THEN CLOSE c_instr_assignment; END IF;
1196
1197 IF NOT FND_API.Compatible_API_Call (l_api_version,
1198 p_api_version,
1199 l_module,
1200 G_PKG_NAME)
1201 THEN
1202 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
1203 debug_level => FND_LOG.LEVEL_ERROR,
1204 module => G_DEBUG_MODULE || l_module);
1205 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1206 FND_MSG_PUB.Add;
1207 RAISE FND_API.G_EXC_ERROR;
1208 END IF;
1209
1210 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1211 FND_MSG_PUB.initialize;
1212 END IF;
1213 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
1214
1215 -- Bug# 8470581
1216 -- Do not allow an assignment if the payer party_id is not a joint
1217 -- account owner
1218 IF ((p_assignment_attribs.Assignment_Id IS NULL) AND
1219 (p_assignment_attribs.Instrument.Instrument_Type = 'BANKACCOUNT')) THEN
1220 IF(c_bnkacct_owner%ISOPEN) THEN CLOSE c_bnkacct_owner; END IF;
1221 OPEN c_bnkacct_owner(p_payer.Party_Id, p_assignment_attribs.Instrument.Instrument_Id);
1222 FETCH c_bnkacct_owner INTO l_bnkacct_owner_cnt;
1223
1224 IF (l_bnkacct_owner_cnt <= 0) THEN
1225 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
1226 RETURN;
1227 END IF;
1228 END IF;
1229
1230 Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
1231 l_payer_level,l_payer_id,l_payer_attribs);
1232
1233 iby_debug_pub.add('l_payer_id = '|| l_payer_id,iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
1234
1235
1236 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
1237 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
1238 --
1239 -- CHANGE?: does PL/SQL do logical short circuiting? If not then
1240 -- change the condition evaluations as the exists_instrument
1241 -- function is relatively expensive
1242 --
1243 ELSIF ( (p_assignment_attribs.Assignment_Id IS NULL) AND
1244 (NOT Exists_Instr(p_assignment_attribs.Instrument)) ) THEN
1245 x_response.Result_Code := G_RC_INVALID_INSTRUMENT;
1246 ELSE
1247 SAVEPOINT Set_Payer_Instr_Assignment;
1248 -- create the payer entity if it does not exist
1249 IF (l_payer_id IS NULL) THEN
1250 IBY_FNDCPT_SETUP_PUB.Set_Payer_Attributes
1251 (
1252 1.0,
1253 FND_API.G_FALSE,
1254 FND_API.G_FALSE,
1255 x_return_status,
1256 x_msg_count,
1257 x_msg_data,
1258 p_payer,
1259 l_payer_attribs,
1260 l_payer_id,
1261 l_result
1262 );
1263 IF (l_result.Result_Code <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS) THEN
1264 x_response := l_result;
1265 RETURN;
1266 END IF;
1267 END IF;
1268
1269
1270 -- for the combined query cursor, only 1 query condition should be used,
1271 -- either the assingment id or the (payer id, instr type, instr id)
1272 -- combination
1273 --
1274 IF (NOT p_assignment_attribs.Assignment_Id IS NULL) THEN
1275 l_assign_id := p_assignment_attribs.Assignment_Id;
1276
1277 -- [lmallick] - bug# 12570664
1278 -- fetch these details since these might not be set on
1279 -- the p_assignment_attribs record
1280 IF c_assign_details%ISOPEN THEN
1281 CLOSE c_assign_details;
1282 END IF;
1283 OPEN c_assign_details(p_assignment_attribs.Assignment_Id);
1284 FETCH c_assign_details INTO l_instr_id, l_instrtype;
1285 CLOSE c_assign_details;
1286
1287 ELSE
1288 l_instr_id := p_assignment_attribs.Instrument.Instrument_Id;
1289 l_instrtype := p_assignment_attribs.Instrument.Instrument_Type;
1290 END IF;
1291
1292 iby_debug_pub.add('**values passed to the cursor**',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1293 iby_debug_pub.add('l_assign_id = '|| l_assign_id,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1294 iby_debug_pub.add('l_instr_id = '|| l_instr_id,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1295 iby_debug_pub.add('l_instrtype = '|| l_instrtype,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1296 iby_debug_pub.add('**----**',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1297
1298 OPEN c_instr_assignment(l_assign_id,l_payer_id,
1299 l_instrtype,
1300 l_instr_id);
1301 FETCH c_instr_assignment INTO x_assign_id, l_instrtype;
1302
1303 IF (c_instr_assignment%NOTFOUND) THEN
1304 x_assign_id := NULL;
1305 l_instrtype := p_assignment_attribs.Instrument.Instrument_Type;
1306 END IF;
1307 CLOSE c_instr_assignment;
1308
1309
1310 iby_debug_pub.add('x_assign_id = '|| x_assign_id,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1311 iby_debug_pub.add('l_instrtype = '|| l_instrtype,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1312 -- assignment id passed is non-NULL but no instruments found
1313 IF ((x_assign_id IS NULL) AND (NOT l_assign_id IS NULL)) THEN
1314 x_response.Result_Code := G_RC_INVALID_INSTR_ASSIGN;
1315 iby_fndcpt_common_pub.Prepare_Result
1316 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1317 RETURN;
1318 END IF;
1319
1320 /** Bug 14489185
1321 * If priority is passed then we would honor the priority.
1322 * If priority is not passed then we would see create a bank account
1323 * with least of existing priorities
1324 * If no assingment exists, then priority would be 1
1325 */
1326
1327 SELECT NVL(MAX(order_of_preference),0)+1 INTO l_priority
1328 FROM iby_pmt_instr_uses_all instr
1329 WHERE instr.ext_pmt_party_id = l_payer_id
1330 AND instr.instrument_type = l_instrtype
1331 AND instr.payment_flow = G_PMT_FLOW_FNDCPT;
1332
1333 l_priority := GREATEST(NVL(p_assignment_attribs.Priority,l_priority),1);
1334
1335 -- only need to shift instrument priorities if this is a new instrument
1336 -- or if this is an update with a non-NULL priority
1337 IF (x_assign_id IS NULL) OR
1338 ((NOT x_assign_id IS NULL) AND (NOT p_assignment_attribs.Priority IS NULL))
1339 THEN
1340 --Changing update statement to update priority of elements of only a particular
1341 --instrument type instead of all instrument type.
1342 --Skipping execution with the expensive CONNECT BY clause
1343 --when p_assignment_attribs.Priority is NULL or 1,
1344 --also l_priority gets 1 when p_assignment_attribs.Priority is NULL
1345
1346 UPDATE iby_pmt_instr_uses_all
1347 SET order_of_preference = order_of_preference + 1,
1348 last_updated_by = fnd_global.user_id,
1349 last_update_date = trunc(SYSDATE),
1350 last_update_login = fnd_global.login_id,
1351 object_version_number = object_version_number + 1
1352 WHERE ext_pmt_party_id = l_payer_id
1353 AND payment_flow = G_PMT_FLOW_FNDCPT
1354 AND instrument_type = l_instrtype
1355 AND order_of_preference >= l_priority;
1356
1357
1358 iby_debug_pub.add('SQL%ROWCOUNT = '|| SQL%ROWCOUNT,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1359
1360 END IF;
1361
1362 l_last_update := SYSDATE;
1363
1364 IF (x_assign_id IS NULL) THEN
1365 SELECT iby_pmt_instr_uses_all_s.nextval
1366 INTO x_assign_id
1367 FROM DUAL;
1368
1369 INSERT INTO iby_pmt_instr_uses_all
1370 (instrument_payment_use_id, ext_pmt_party_id, instrument_type,
1371 instrument_id, payment_function, payment_flow, order_of_preference,
1372 debit_auth_flag, debit_auth_method, debit_auth_reference,
1373 debit_auth_begin, debit_auth_end, start_date, end_date,
1374 created_by, creation_date, last_updated_by, last_update_date,
1375 last_update_login, object_version_number)
1376 VALUES
1377 (x_assign_id, l_payer_id,
1378 p_assignment_attribs.Instrument.Instrument_Type,
1379 p_assignment_attribs.Instrument.Instrument_Id,
1380 p_payer.Payment_Function, G_PMT_FLOW_FNDCPT, l_priority,
1381 null, null, null, null, null,
1382 NVL(p_assignment_attribs.Start_Date,SYSDATE),
1383 p_assignment_attribs.End_Date,
1384 fnd_global.user_id, SYSDATE, fnd_global.user_id, l_last_update,
1385 fnd_global.login_id, 1);
1386
1387 l_op_type := 'I';
1388 ELSE
1389 UPDATE iby_pmt_instr_uses_all
1390 SET
1391 order_of_preference =
1392 NVL(p_assignment_attribs.Priority,order_of_preference),
1393 start_date = NVL(p_assignment_attribs.Start_Date,start_date),
1394 end_date = p_assignment_attribs.End_Date,
1395 last_updated_by = fnd_global.user_id,
1396 last_update_date = l_last_update,
1397 last_update_login = fnd_global.login_id,
1398 object_version_number = object_version_number + 1
1399 WHERE instrument_payment_use_id = x_assign_id;
1400
1401 l_op_type := 'U';
1402 END IF;
1403
1404 iby_debug_pub.add('begin HZ hook',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1405
1406 IF (NOT p_payer.Account_Site_Id IS NULL) THEN
1407 l_parent_type := 'CUST_ACCT_SITE_USE';
1408 l_parent_table := 'HZ_CUST_SITE_USES_ALL';
1409 l_parent_id := p_payer.Account_Site_Id;
1410 ELSIF (NOT p_payer.Cust_Account_Id IS NULL) THEN
1411 l_parent_type := 'CUST_ACCT';
1412 l_parent_table := 'HZ_CUST_ACCOUNTS';
1413 l_parent_id := p_payer.Cust_Account_Id;
1414 END IF;
1415
1416 SELECT instrument_type
1417 INTO l_instr_type
1418 FROM iby_pmt_instr_uses_all
1419 WHERE instrument_payment_use_id = x_assign_id;
1420
1421 IF (l_instr_type = 'BANKACCOUNT') THEN
1422 SELECT party_type INTO l_party_type
1423 FROM hz_parties WHERE party_id = p_payer.Party_Id;
1424
1425 HZ_BES_BO_TRACKING_PVT.Create_Bot
1426 (p_init_msg_list => fnd_api.g_false,
1427 p_child_bo_code => NULL,
1428 p_child_tbl_name => 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V',
1429 p_child_id => x_assign_id,
1430 p_child_opr_flag => l_op_type,
1431 p_child_update_dt => l_last_update,
1432 p_parent_bo_code => l_parent_type,
1433 p_parent_tbl_name => l_parent_table,
1434 p_parent_id => l_parent_id,
1435 p_parent_opr_flag => NULL,
1436 p_gparent_bo_code => l_party_type,
1437 p_gparent_tbl_name => 'HZ_PARTIES',
1438 p_gparent_id => p_payer.Party_Id,
1439 x_return_status => x_return_status,
1440 x_msg_count => x_msg_count,
1441 x_msg_data => x_msg_data
1442 );
1443 END IF;
1444
1445 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
1446 END IF;
1447
1448 IF FND_API.To_Boolean(p_commit) THEN
1449 COMMIT;
1450 END IF;
1451
1452 iby_fndcpt_common_pub.Prepare_Result
1453 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1454
1455 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
1456 EXCEPTION
1457
1458 WHEN FND_API.G_EXC_ERROR THEN
1459 ROLLBACK TO Set_Payer_Instr_Assignment;
1460 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1461 debug_level => FND_LOG.LEVEL_ERROR,
1462 module => G_DEBUG_MODULE || l_module);
1463 x_return_status := FND_API.G_RET_STS_ERROR ;
1464 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1465 p_data => x_msg_data
1466 );
1467 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1468 ROLLBACK TO Set_Payer_Instr_Assignment;
1469 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1470 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1471 module => G_DEBUG_MODULE || l_module);
1472 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1473 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1474 p_data => x_msg_data
1475 );
1476 WHEN OTHERS THEN
1477 ROLLBACK TO Set_Payer_Instr_Assignment;
1478 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1479 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1480 module => G_DEBUG_MODULE || l_module);
1481
1482 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
1483
1484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1485 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1486 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
1487 END IF;
1488
1489 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1490 p_data => x_msg_data
1491 );
1492 END Set_Payer_Instr_Assignment;
1493
1494
1495 PROCEDURE Get_Payer_Instr_Assignments
1496 (
1497 p_api_version IN NUMBER,
1498 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1499 x_return_status OUT NOCOPY VARCHAR2,
1500 x_msg_count OUT NOCOPY NUMBER,
1501 x_msg_data OUT NOCOPY VARCHAR2,
1502 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
1503 x_assignments OUT NOCOPY PmtInstrAssignment_tbl_type,
1504 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1505 )
1506 IS
1507 l_api_version CONSTANT NUMBER := 1.0;
1508 l_module CONSTANT VARCHAR2(30) := 'Get_Payer_Instr_Assignments';
1509 l_payer_level VARCHAR2(30);
1510 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
1511 l_payer_attribs PayerAttributes_rec_type;
1512 l_assign_count NUMBER := 0;
1513 l_prev_msg_count NUMBER;
1514
1515 CURSOR c_instr_assignments
1516 (ci_payer_id IN iby_pmt_instr_uses_all.ext_pmt_party_id%TYPE)
1517 IS
1518 SELECT instrument_payment_use_id, instrument_type, instrument_id,
1519 order_of_preference, start_date, end_date
1520 FROM iby_pmt_instr_uses_all
1521 WHERE (payment_flow = G_PMT_FLOW_FNDCPT)
1522 AND (ext_pmt_party_id = ci_payer_id);
1523
1524 BEGIN
1525
1526 IF (c_instr_assignments%ISOPEN) THEN
1527 CLOSE c_instr_assignments;
1528 END IF;
1529
1530 IF NOT FND_API.Compatible_API_Call (l_api_version,
1531 p_api_version,
1532 l_module,
1533 G_PKG_NAME)
1534 THEN
1535 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
1536 debug_level => FND_LOG.LEVEL_ERROR,
1537 module => G_DEBUG_MODULE || l_module);
1538 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1539 FND_MSG_PUB.Add;
1540 RAISE FND_API.G_EXC_ERROR;
1541 END IF;
1542
1543 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1544 FND_MSG_PUB.initialize;
1545 END IF;
1546 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
1547
1548 Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
1549 l_payer_level,l_payer_id,l_payer_attribs);
1550
1551 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
1552 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
1553 ELSE
1554 l_assign_count := 0;
1555 FOR assign_rec IN c_instr_assignments(l_payer_id) LOOP
1556 l_assign_count := l_assign_count + 1;
1557
1558 x_assignments(l_assign_count).Assignment_Id :=
1559 assign_rec.instrument_payment_use_id;
1560 x_assignments(l_assign_count).Instrument.Instrument_Type :=
1561 assign_rec.instrument_type;
1562 x_assignments(l_assign_count).Instrument.Instrument_Id :=
1563 assign_rec.instrument_id;
1564 x_assignments(l_assign_count).Priority := assign_rec.order_of_preference;
1565 x_assignments(l_assign_count).Start_Date := assign_rec.start_date;
1566 x_assignments(l_assign_count).End_Date := assign_rec.end_date;
1567 END LOOP;
1568
1569 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
1570
1571 END IF;
1572
1573 iby_fndcpt_common_pub.Prepare_Result
1574 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1575
1576 EXCEPTION
1577
1578 WHEN FND_API.G_EXC_ERROR THEN
1579
1580 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1581 debug_level => FND_LOG.LEVEL_ERROR,
1582 module => G_DEBUG_MODULE || l_module);
1583 x_return_status := FND_API.G_RET_STS_ERROR ;
1584 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1585 p_data => x_msg_data
1586 );
1587 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1588
1589 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1590 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1591 module => G_DEBUG_MODULE || l_module);
1592 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1593 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1594 p_data => x_msg_data
1595 );
1596 WHEN OTHERS THEN
1597
1598 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1599 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1600 module => G_DEBUG_MODULE || l_module);
1601
1602 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
1603
1604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1605 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1606 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
1607 END IF;
1608
1609 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1610 p_data => x_msg_data
1611 );
1612 END Get_Payer_Instr_Assignments;
1613
1614
1615 PROCEDURE Get_Payer_All_Instruments
1616 (
1617 p_api_version IN NUMBER,
1618 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1619 x_return_status OUT NOCOPY VARCHAR2,
1620 x_msg_count OUT NOCOPY NUMBER,
1621 x_msg_data OUT NOCOPY VARCHAR2,
1622 p_party_id IN NUMBER,
1623 x_instruments OUT NOCOPY PmtInstrument_tbl_type,
1624 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1625 )
1626 IS
1627 l_api_version CONSTANT NUMBER := 1.0;
1628 l_module CONSTANT VARCHAR2(30) := 'Get_Payer_All_Assignments';
1629 l_prev_msg_count NUMBER;
1630
1631 l_instr_count NUMBER := 0;
1632
1633 CURSOR c_instr_assignments
1634 (ci_party_id IN iby_external_payers_all.party_id%TYPE)
1635 IS
1636 SELECT DISTINCT u.instrument_type, u.instrument_id
1637 FROM iby_pmt_instr_uses_all u, iby_external_payers_all p
1638 WHERE (u.payment_flow = G_PMT_FLOW_FNDCPT)
1639 AND (u.ext_pmt_party_id = p.ext_payer_id)
1640 AND (p.party_id = ci_party_id);
1641
1642 BEGIN
1643
1644 IF (c_instr_assignments%ISOPEN) THEN
1645 CLOSE c_instr_assignments;
1646 END IF;
1647
1648 IF NOT FND_API.Compatible_API_Call (l_api_version,
1649 p_api_version,
1650 l_module,
1651 G_PKG_NAME)
1652 THEN
1653 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
1654 debug_level => FND_LOG.LEVEL_ERROR,
1655 module => G_DEBUG_MODULE || l_module);
1656 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1657 FND_MSG_PUB.Add;
1658 RAISE FND_API.G_EXC_ERROR;
1659 END IF;
1660
1661 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1662 FND_MSG_PUB.initialize;
1663 END IF;
1664 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
1665
1666 l_instr_count := 0;
1667 FOR assign_rec IN c_instr_assignments(p_party_id) LOOP
1668 l_instr_count := l_instr_count + 1;
1669
1670 x_instruments(l_instr_count).Instrument_Type :=
1671 assign_rec.instrument_type;
1672 x_instruments(l_instr_count).Instrument_Id :=
1673 assign_rec.instrument_id;
1674 END LOOP;
1675
1676 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
1677
1678 iby_fndcpt_common_pub.Prepare_Result
1679 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1680
1681 EXCEPTION
1682
1683 WHEN FND_API.G_EXC_ERROR THEN
1684
1685 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1686 debug_level => FND_LOG.LEVEL_ERROR,
1687 module => G_DEBUG_MODULE || l_module);
1688 x_return_status := FND_API.G_RET_STS_ERROR ;
1689 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1690 p_data => x_msg_data
1691 );
1692 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1693
1694 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1695 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1696 module => G_DEBUG_MODULE || l_module);
1697 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1698 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1699 p_data => x_msg_data
1700 );
1701 WHEN OTHERS THEN
1702
1703 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1704 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1705 module => G_DEBUG_MODULE || l_module);
1706
1707 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
1708
1709 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1710 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1711 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
1712 END IF;
1713
1714 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1715 p_data => x_msg_data
1716 );
1717
1718 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
1719 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1720 module => G_DEBUG_MODULE || l_module);
1721 iby_debug_pub.add(debug_msg => 'Exit Exception',
1722 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1723 module => G_DEBUG_MODULE || l_module);
1724 END Get_Payer_All_Instruments;
1725
1726
1727 PROCEDURE Get_Trxn_Appl_Instr_Assign
1728 (
1729 p_api_version IN NUMBER,
1730 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1731 x_return_status OUT NOCOPY VARCHAR2,
1732 x_msg_count OUT NOCOPY NUMBER,
1733 x_msg_data OUT NOCOPY VARCHAR2,
1734 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
1735 p_payer_equivalency IN VARCHAR2 :=
1736 IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
1737 p_conditions IN IBY_FNDCPT_COMMON_PUB.TrxnContext_rec_type,
1738 p_result_limit IN IBY_FNDCPT_COMMON_PUB.ResultLimit_rec_type,
1739 x_assignments OUT NOCOPY PmtInstrAssignment_tbl_type,
1740 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1741 )
1742 IS
1743 l_api_version CONSTANT NUMBER := 1.0;
1744 l_module CONSTANT VARCHAR2(30) := 'Get_Trxn_Appl_Instr_Assign';
1745 l_prev_msg_count NUMBER;
1746
1747 l_payer_level VARCHAR2(30);
1748 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
1749 l_payer_attribs PayerAttributes_rec_type;
1750
1751 l_assign_count NUMBER;
1752
1753 CURSOR c_instr_assigns
1754 (ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
1755 ci_payer_level IN VARCHAR2,
1756 ci_payer_equiv IN VARCHAR2,
1757 ci_instrument_type IN VARCHAR2)
1758 IS
1759
1760 SELECT X.INSTRUMENT_PAYMENT_USE_ID,
1761 X.INSTRUMENT_TYPE,
1762 X.INSTRUMENT_ID,
1763 X.ORDER_OF_PREFERENCE,
1764 X.START_DATE,
1765 X.END_DATE,
1766 X.ACCT_SITE_USE_ID,
1767 X.CUST_ACCOUNT_ID
1768 FROM
1769 (
1770 SELECT INSTRUMENT_PAYMENT_USE_ID,
1771 INSTRUMENT_TYPE,
1772 INSTRUMENT_ID,
1773 ORDER_OF_PREFERENCE,
1774 START_DATE,
1775 END_DATE,
1776 ACCT_SITE_USE_ID,
1777 CUST_ACCOUNT_ID,
1778 RANK() OVER (PARTITION BY INSTRUMENT_TYPE, INSTRUMENT_ID
1779 ORDER BY ACCT_SITE_USE_ID, CUST_ACCOUNT_ID,ORDER_OF_PREFERENCE,INSTRUMENT_PAYMENT_USE_ID) DUP_RANK
1780 FROM
1781 IBY_PMT_INSTR_USES_ALL inst,
1782 IBY_EXTERNAL_PAYERS_ALL payer
1783 WHERE
1784 (inst.PAYMENT_FLOW = G_PMT_FLOW_FNDCPT)
1785 AND inst.INSTRUMENT_TYPE = NVL(ci_instrument_type ,INSTRUMENT_TYPE)
1786 AND SYSDATE >= inst.START_DATE
1787 AND SYSDATE < NVL(inst.END_DATE, SYSDATE+1)
1788 AND (payer.PAYMENT_FUNCTION = ci_payer.Payment_Function)
1789 AND (payer.PARTY_ID = ci_payer.Party_Id )
1790 AND inst.EXT_PMT_PARTY_ID = payer.ext_payer_id
1791 and
1792 (IBY_FNDCPT_COMMON_PUB.COMPARE_PAYER (ci_payer.org_type, ci_payer.org_id,
1793 ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
1794 ci_payer_level,ci_payer_equiv ,payer.ORG_TYPE,payer.ORG_ID,
1795 payer.CUST_ACCOUNT_ID, payer.ACCT_SITE_USE_ID) = 'T')
1796 ) X,
1797 -- [lmallick]: bug# 12570664
1798 -- This API shouln't be returning the assignments that correspond
1799 -- to inactive instruments. Added the necessary filter conditions
1800 -- by joining with iby_creditcard and iby_ext_bank_accounts.
1801 iby_creditcard c,
1802 iby_ext_bank_accounts b
1803 WHERE X.DUP_RANK = 1
1804 AND c.instrid(+) = x.instrument_id
1805 AND b.ext_bank_account_id(+) = x.instrument_id
1806 AND NVL(c.inactive_date, sysdate+10) > sysdate
1807 AND NVL(b.start_date, sysdate-10) <= sysdate
1808 AND NVL(b.end_date, sysdate+10) > sysdate
1809 ORDER BY
1810 ACCT_SITE_USE_ID,
1811 CUST_ACCOUNT_ID,
1812 ORDER_OF_PREFERENCE;
1813
1814
1815 BEGIN
1816
1817 IF (c_instr_assigns%ISOPEN) THEN
1818 CLOSE c_instr_assigns;
1819 END IF;
1820
1821 IF NOT FND_API.Compatible_API_Call (l_api_version,
1822 p_api_version,
1823 l_module,
1824 G_PKG_NAME)
1825 THEN
1826 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
1827 debug_level => FND_LOG.LEVEL_ERROR,
1828 module => G_DEBUG_MODULE || l_module);
1829 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1830 FND_MSG_PUB.Add;
1831 RAISE FND_API.G_EXC_ERROR;
1832 END IF;
1833
1834 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1835 FND_MSG_PUB.initialize;
1836 END IF;
1837 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
1838
1839 Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
1840 l_payer_level,l_payer_id,l_payer_attribs);
1841
1842 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
1843 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
1844 ELSE
1845 l_assign_count := 1;
1846 FOR assign_rec IN c_instr_assigns(p_payer,l_payer_level,p_payer_equivalency,p_conditions.payment_instrtype)
1847 LOOP
1848 x_assignments(l_assign_count).Assignment_Id := assign_rec.instrument_payment_use_id;
1849 x_assignments(l_assign_count).Instrument.Instrument_Type := assign_rec.instrument_type;
1850 x_assignments(l_assign_count).Instrument.Instrument_Id := assign_rec.instrument_id;
1851 x_assignments(l_assign_count).Priority := assign_rec.order_of_preference;
1852 x_assignments(l_assign_count).Start_Date := assign_rec.start_date;
1853 x_assignments(l_assign_count).End_Date := assign_rec.end_date;
1854 l_assign_count := l_assign_count + 1;
1855
1856 EXIT WHEN p_result_limit.default_flag='Y';
1857 END LOOP;
1858
1859 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
1860 END IF;
1861
1862 iby_fndcpt_common_pub.Prepare_Result
1863 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1864
1865 EXCEPTION
1866
1867 WHEN FND_API.G_EXC_ERROR THEN
1868
1869 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1870 debug_level => FND_LOG.LEVEL_ERROR,
1871 module => G_DEBUG_MODULE || l_module);
1872 x_return_status := FND_API.G_RET_STS_ERROR ;
1873 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1874 p_data => x_msg_data
1875 );
1876 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1877
1878 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1879 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1880 module => G_DEBUG_MODULE || l_module);
1881 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1882 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1883 p_data => x_msg_data
1884 );
1885 WHEN OTHERS THEN
1886
1887 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1888 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1889 module => G_DEBUG_MODULE || l_module);
1890
1891 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
1892
1893 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1894 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1895 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
1896 END IF;
1897
1898 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1899 p_data => x_msg_data
1900 );
1901
1902 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
1903 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1904 module => G_DEBUG_MODULE || l_module);
1905 iby_debug_pub.add(debug_msg => 'Exit Exception',
1906 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1907 module => G_DEBUG_MODULE || l_module);
1908
1909 END Get_Trxn_Appl_Instr_Assign;
1910
1911 PROCEDURE Create_Card_Wrapper
1912 (p_commit IN VARCHAR2,
1913 p_owner_id IN iby_creditcard.card_owner_id%TYPE,
1914 p_holder_name IN iby_creditcard.chname%TYPE,
1915 p_billing_address_id IN iby_creditcard.addressid%TYPE,
1916 p_address_type IN VARCHAR2,
1917 p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
1918 p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
1919 p_card_number IN iby_creditcard.ccnumber%TYPE,
1920 p_expiry_date IN iby_creditcard.expirydate%TYPE,
1921 p_instr_type IN iby_creditcard.instrument_type%TYPE,
1922 p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
1923 p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
1924 p_issuer IN iby_creditcard.card_issuer_code%TYPE,
1925 p_fi_name IN iby_creditcard.finame%TYPE,
1926 p_single_use IN iby_creditcard.single_use_flag%TYPE,
1927 p_info_only IN iby_creditcard.information_only_flag%TYPE,
1928 p_purpose IN iby_creditcard.card_purpose%TYPE,
1929 p_desc IN iby_creditcard.description%TYPE,
1930 p_active_flag IN iby_creditcard.active_flag%TYPE,
1931 p_inactive_date IN iby_creditcard.inactive_date%TYPE,
1932 p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
1933 p_attribute_category IN iby_creditcard.attribute_category%TYPE,
1934 p_attribute1 IN iby_creditcard.attribute1%TYPE,
1935 p_attribute2 IN iby_creditcard.attribute2%TYPE,
1936 p_attribute3 IN iby_creditcard.attribute3%TYPE,
1937 p_attribute4 IN iby_creditcard.attribute4%TYPE,
1938 p_attribute5 IN iby_creditcard.attribute5%TYPE,
1939 p_attribute6 IN iby_creditcard.attribute6%TYPE,
1940 p_attribute7 IN iby_creditcard.attribute7%TYPE,
1941 p_attribute8 IN iby_creditcard.attribute8%TYPE,
1942 p_attribute9 IN iby_creditcard.attribute9%TYPE,
1943 p_attribute10 IN iby_creditcard.attribute10%TYPE,
1944 p_attribute11 IN iby_creditcard.attribute11%TYPE,
1945 p_attribute12 IN iby_creditcard.attribute12%TYPE,
1946 p_attribute13 IN iby_creditcard.attribute13%TYPE,
1947 p_attribute14 IN iby_creditcard.attribute14%TYPE,
1948 p_attribute15 IN iby_creditcard.attribute15%TYPE,
1949 p_attribute16 IN iby_creditcard.attribute16%TYPE,
1950 p_attribute17 IN iby_creditcard.attribute17%TYPE,
1951 p_attribute18 IN iby_creditcard.attribute18%TYPE,
1952 p_attribute19 IN iby_creditcard.attribute19%TYPE,
1953 p_attribute20 IN iby_creditcard.attribute20%TYPE,
1954 p_attribute21 IN iby_creditcard.attribute21%TYPE,
1955 p_attribute22 IN iby_creditcard.attribute22%TYPE,
1956 p_attribute23 IN iby_creditcard.attribute23%TYPE,
1957 p_attribute24 IN iby_creditcard.attribute24%TYPE,
1958 p_attribute25 IN iby_creditcard.attribute25%TYPE,
1959 p_attribute26 IN iby_creditcard.attribute26%TYPE,
1960 p_attribute27 IN iby_creditcard.attribute27%TYPE,
1961 p_attribute28 IN iby_creditcard.attribute28%TYPE,
1962 p_attribute29 IN iby_creditcard.attribute29%TYPE,
1963 p_attribute30 IN iby_creditcard.attribute30%TYPE,
1964 x_result_code OUT NOCOPY VARCHAR2,
1965 x_return_status OUT NOCOPY VARCHAR2,
1966 x_instr_id OUT NOCOPY iby_creditcard.instrid%TYPE
1967 )
1968 IS
1969 -- create a record type and populate it
1970 x_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1971 x_card_instrument CreditCard_rec_type;
1972 x_msg_count NUMBER;
1973 x_msg_data VARCHAR2(3000);
1974 Begin
1975 x_card_instrument.Owner_Id := p_owner_id;
1976 x_card_instrument.Card_Holder_Name := p_holder_name;
1977 x_card_instrument.Billing_Address_Id := p_billing_address_id;
1978 x_card_instrument.Address_Type := p_address_type;
1979 x_card_instrument.Billing_Postal_Code := p_billing_zip;
1980 x_card_instrument.Billing_Address_Territory := p_billing_country;
1981 x_card_instrument.Card_Number := p_card_number;
1982 x_card_instrument.Expiration_Date := p_expiry_date;
1983 x_card_instrument.Instrument_Type := p_instr_type;
1984 x_card_instrument.PurchaseCard_Flag := p_pcard_flag;
1985 x_card_instrument.PurchaseCard_SubType := p_pcard_type;
1986 x_card_instrument.FI_Name := p_fi_name;
1987 x_card_instrument.Single_Use_Flag := p_single_use;
1988 x_card_instrument.Info_Only_Flag := p_info_only;
1989 x_card_instrument.Card_Purpose := p_purpose;
1990 x_card_instrument.Card_Description := p_desc;
1991 x_card_instrument.Active_Flag := p_active_flag;
1992 x_card_instrument.Inactive_Date := p_inactive_date;
1993 x_card_instrument.card_issuer := p_issuer;
1994 x_card_instrument.attribute_category := p_attribute_category;
1995 x_card_instrument.attribute1 := p_attribute1;
1996 x_card_instrument.attribute2 := p_attribute2;
1997 x_card_instrument.attribute3 := p_attribute3;
1998 x_card_instrument.attribute4 := p_attribute4;
1999 x_card_instrument.attribute5 := p_attribute5;
2000 x_card_instrument.attribute6 := p_attribute6;
2001 x_card_instrument.attribute7 := p_attribute7;
2002 x_card_instrument.attribute8 := p_attribute8;
2003 x_card_instrument.attribute9 := p_attribute9;
2004 x_card_instrument.attribute10 := p_attribute10;
2005 x_card_instrument.attribute11 := p_attribute11;
2006 x_card_instrument.attribute12 := p_attribute12;
2007 x_card_instrument.attribute13 := p_attribute13;
2008 x_card_instrument.attribute14 := p_attribute14;
2009 x_card_instrument.attribute15 := p_attribute15;
2010 x_card_instrument.attribute16 := p_attribute16;
2011 x_card_instrument.attribute17 := p_attribute17;
2012 x_card_instrument.attribute18 := p_attribute18;
2013 x_card_instrument.attribute19 := p_attribute19;
2014 x_card_instrument.attribute20 := p_attribute20;
2015 x_card_instrument.attribute21 := p_attribute21;
2016 x_card_instrument.attribute22 := p_attribute22;
2017 x_card_instrument.attribute23 := p_attribute23;
2018 x_card_instrument.attribute24 := p_attribute24;
2019 x_card_instrument.attribute25 := p_attribute25;
2020 x_card_instrument.attribute26 := p_attribute26;
2021 x_card_instrument.attribute27 := p_attribute27;
2022 x_card_instrument.attribute28 := p_attribute28;
2023 x_card_instrument.attribute29 := p_attribute29;
2024 x_card_instrument.attribute30 := p_attribute30;
2025
2026
2027 -- call Create_Card
2028 Create_Card(1.0,
2029 FND_API.G_FALSE,
2030 p_commit,
2031 x_return_status,
2032 x_msg_count,
2033 x_msg_data,
2034 x_card_instrument,
2035 x_instr_id,
2036 x_response);
2037 -- Map things back
2038 x_result_code := x_response.Result_Code;
2039
2040 End Create_Card_Wrapper;
2041
2042
2043 PROCEDURE Create_Card
2044 (
2045 p_api_version IN NUMBER,
2046 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2047 p_commit IN VARCHAR2 := FND_API.G_TRUE,
2048 x_return_status OUT NOCOPY VARCHAR2,
2049 x_msg_count OUT NOCOPY NUMBER,
2050 x_msg_data OUT NOCOPY VARCHAR2,
2051 p_card_instrument IN CreditCard_rec_type,
2052 x_card_id OUT NOCOPY NUMBER,
2053 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
2054 )
2055 IS
2056
2057 l_api_version CONSTANT NUMBER := 1.0;
2058 l_module CONSTANT VARCHAR2(30) := 'Create_Card';
2059 l_prev_msg_count NUMBER;
2060
2061 lx_result_code VARCHAR2(30);
2062 lx_result IBY_FNDCPT_COMMON_PUB.Result_rec_type;
2063 lx_card_rec CreditCard_rec_type;
2064
2065 l_info_only iby_creditcard.information_only_flag%TYPE := NULL;
2066 l_sec_mode iby_sys_security_options.cc_encryption_mode%TYPE;
2067 l_cc_reg IBY_INSTRREG_PUB.CreditCardInstr_rec_type;
2068 l_instr_reg IBY_INSTRREG_PUB.PmtInstr_rec_type;
2069
2070 l_billing_site hz_party_site_uses.party_site_use_id%TYPE;
2071
2072 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
2073
2074 CURSOR c_sec_mode
2075 IS
2076 SELECT cc_encryption_mode
2077 FROM iby_sys_security_options;
2078
2079 BEGIN
2080 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
2081
2082 IF NOT FND_API.Compatible_API_Call (l_api_version,
2083 p_api_version,
2084 l_module,
2085 G_PKG_NAME)
2086 THEN
2087 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
2088 debug_level => FND_LOG.LEVEL_ERROR,
2089 module => G_DEBUG_MODULE || l_module);
2090 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
2091 FND_MSG_PUB.Add;
2092 RAISE FND_API.G_EXC_ERROR;
2093 END IF;
2094
2095 IF (c_sec_mode%ISOPEN) THEN CLOSE c_sec_mode; END IF;
2096
2097 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2098 FND_MSG_PUB.initialize;
2099 END IF;
2100 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
2101
2102 --SAVEPOINT Create_Card;
2103
2104 IBY_FNDCPT_SETUP_PUB.Card_Exists
2105 (
2106 1.0,
2107 FND_API.G_FALSE,
2108 x_return_status,
2109 x_msg_count,
2110 x_msg_data,
2111 p_card_instrument.Owner_Id,
2112 p_card_instrument.Card_Number,
2113 lx_card_rec,
2114 lx_result,
2115 NVL(p_card_instrument.Instrument_Type,IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_CREDITCARD)
2116 );
2117
2118 iby_debug_pub.add('fetched card id:='||lx_card_rec.Card_Id,
2119 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2120
2121 IF (lx_card_rec.Card_Id IS NULL) THEN
2122
2123 iby_debug_pub.add('p_card_instrument.Register_Invalid_Card: '|| p_card_instrument.Register_Invalid_Card,
2124 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2125
2126 -- validate billing address information
2127 IF (NOT Validate_CC_Billing(FND_API.G_FALSE,p_card_instrument)) THEN
2128 x_response.Result_Code := iby_creditcard_pkg.G_RC_INVALID_ADDRESS;
2129 iby_fndcpt_common_pub.Prepare_Result
2130 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2131 RETURN;
2132 END IF;
2133
2134 -- lmallick (bug# 8721435)
2135 -- These validations have been moved from iby_creditcard_pkg because the TCA
2136 -- data might not have been committed to the db before invoking the Create_card API
2137 iby_debug_pub.add('Starting address validation ..',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2138
2139 -- If Site use id is already provied then no need to call get_billing address
2140 iby_debug_pub.add('p_card_instrument.Address_Type = '||p_card_instrument.Address_Type,
2141 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2142 IF (p_card_instrument.Address_Type = IBY_CREDITCARD_PKG.G_PARTY_SITE_USE_ID) AND
2143 (NOT (p_card_instrument.Billing_Address_Id IS NULL)) THEN
2144 l_billing_site := p_card_instrument.Billing_Address_Id;
2145 ELSE
2146 IF (p_card_instrument.Billing_Address_Id = FND_API.G_MISS_NUM ) THEN
2147 l_billing_site := FND_API.G_MISS_NUM;
2148 ELSIF (NOT (p_card_instrument.Billing_Address_Id IS NULL)) THEN
2149 l_billing_site :=
2150 IBY_CREDITCARD_PKG.Get_Billing_Site(p_card_instrument.Billing_Address_Id,
2151 p_card_instrument.Owner_Id);
2152 IF (l_billing_site IS NULL) THEN
2153 x_response.Result_Code := IBY_CREDITCARD_PKG.G_RC_INVALID_ADDRESS;
2154 iby_debug_pub.add('Invalid Billing site.',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2155 iby_fndcpt_common_pub.Prepare_Result
2156 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2157 RETURN;
2158 END IF;
2159 END IF;
2160 END IF;
2161
2162 iby_debug_pub.add('l_billing_site = '||l_billing_site,
2163 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2164
2165 IF (NOT ( (p_card_instrument.Billing_Address_Territory IS NULL)
2166 OR (p_card_instrument.Billing_Address_Territory = FND_API.G_MISS_CHAR) )
2167 )
2168 THEN
2169 IF (NOT iby_utility_pvt.Validate_Territory(p_card_instrument.Billing_Address_Territory)) THEN
2170 x_response.Result_Code := IBY_CREDITCARD_PKG.G_RC_INVALID_ADDRESS;
2171 iby_debug_pub.add('Invalid Territory '|| p_card_instrument.Billing_Address_Territory,
2172 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2173 iby_fndcpt_common_pub.Prepare_Result
2174 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2175 RETURN;
2176 END IF;
2177 END IF;
2178
2179 IF (NOT p_card_instrument.Owner_Id IS NULL) THEN
2180 IF (NOT iby_utility_pvt.validate_party_id(p_card_instrument.Owner_Id)) THEN
2181 x_response.Result_Code := IBY_CREDITCARD_PKG.G_RC_INVALID_PARTY;
2182 iby_debug_pub.add('Invalid Owner party '||p_card_instrument.Owner_Id,
2183 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2184 iby_fndcpt_common_pub.Prepare_Result
2185 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2186 RETURN;
2187 END IF;
2188 END IF;
2189 -- End of Bug fix for 8721435 --
2190
2191 OPEN c_sec_mode;
2192 FETCH c_sec_mode INTO l_sec_mode;
2193 CLOSE c_sec_mode;
2194
2195 IF (l_sec_mode = iby_security_pkg.G_ENCRYPT_MODE_INSTANT) THEN
2196
2197 iby_debug_pub.add('online registration',
2198 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2199
2200 l_cc_reg.FIName := p_card_instrument.FI_Name;
2201 l_cc_reg.CC_Type := p_card_instrument.Card_Issuer;
2202 l_cc_reg.CC_Num := p_card_instrument.Card_Number;
2203 l_cc_reg.CC_ExpDate := p_card_instrument.Expiration_Date;
2204 l_cc_reg.Instrument_Type := NVL(p_card_instrument.Instrument_Type,IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_CREDITCARD);
2205 l_cc_reg.Owner_Id := p_card_instrument.Owner_Id;
2206 l_cc_reg.CC_HolderName := p_card_instrument.Card_Holder_Name;
2207 l_cc_reg.CC_Desc := p_card_instrument.Card_Description;
2208 l_cc_reg.Billing_Address_Id := l_billing_site;
2209 l_cc_reg.Billing_PostalCode := p_card_instrument.Billing_Postal_Code;
2210 l_cc_reg.Billing_Country := p_card_instrument.Billing_Address_Territory;
2211 l_cc_reg.Single_Use_Flag := p_card_instrument.Single_Use_Flag;
2212 l_cc_reg.Info_Only_Flag := p_card_instrument.Info_Only_Flag;
2213 l_cc_reg.Card_Purpose := p_card_instrument.Card_Purpose;
2214 l_cc_reg.CC_Desc := p_card_instrument.Card_Description;
2215 l_cc_reg.Active_Flag := p_card_instrument.Active_Flag;
2216 l_cc_reg.Inactive_Date := p_card_instrument.Inactive_Date;
2217
2218 -- lmallick
2219 -- New parameter introduced to allow registration of invalid credit cards
2220 -- This is currently used by the OIE product and its only this product that
2221 -- passes the value as 'Y'
2222 l_cc_reg.Register_Invalid_Card := p_card_instrument.Register_Invalid_Card;
2223
2224 l_instr_reg.CreditCardInstr := l_cc_reg;
2225 l_instr_reg.InstrumentType := IBY_INSTRREG_PUB.C_INSTRTYPE_CREDITCARD;
2226
2227 iby_debug_pub.add('before calling OraInstrAdd',
2228 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2229
2230 IBY_INSTRREG_PUB.OraInstrAdd
2231 (1.0, FND_API.G_FALSE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL,
2232 l_instr_reg, x_return_status, x_msg_count, x_msg_data,
2233 x_card_id, lx_result
2234 );
2235
2236 -- should not be a validation error at this point
2237 IF ((NVL(x_card_id,-1)<0))
2238 --OR (x_return_status <> FND_API.G_RET_STS_ERROR))
2239 THEN
2240 iby_debug_pub.add('instrument reg failed',
2241 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2242 iby_debug_pub.add('result code:=' || lx_result.Result_Code,
2243 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2244 IF (lx_result.Result_Code IS NULL) THEN
2245 x_response.Result_Code := 'COMMUNICATION_ERROR';
2246 --IBY_FNDCPT_COMMON_PUB.G_RC_GENERIC_SYS_ERROR;
2247 iby_fndcpt_common_pub.Prepare_Result
2248 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,
2249 x_response);
2250 ELSE
2251 x_response.Result_Code := lx_result.Result_Code;
2252
2253 iby_fndcpt_common_pub.Prepare_Result
2254 (IBY_INSTRREG_PUB.G_INTERFACE_CODE,lx_result.Result_Message,
2255 l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,
2256 x_response);
2257 END IF;
2258 RETURN;
2259 END IF;
2260 ELSE
2261 iby_debug_pub.add('database registration',
2262 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2263
2264 iby_creditcard_pkg.Create_Card
2265 (FND_API.G_FALSE,
2266 p_card_instrument.Owner_Id, p_card_instrument.Card_Holder_Name,
2267 l_billing_site,
2268 p_card_instrument.Address_Type,
2269 p_card_instrument.Billing_Postal_Code,
2270 p_card_instrument.Billing_Address_Territory,
2271 p_card_instrument.Card_Number, p_card_instrument.Expiration_Date,
2272 NVL(p_card_instrument.Instrument_Type,IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_CREDITCARD),
2273 p_card_instrument.PurchaseCard_Flag,
2274 p_card_instrument.PurchaseCard_SubType, p_card_instrument.Card_Issuer,
2275 p_card_instrument.FI_Name, p_card_instrument.Single_Use_Flag,
2276 p_card_instrument.Info_Only_Flag, p_card_instrument.Card_Purpose,
2277 p_card_instrument.Card_Description, p_card_instrument.Active_Flag,
2278 p_card_instrument.Inactive_Date, NULL,
2279 p_card_instrument.attribute_category,
2280 p_card_instrument.attribute1,
2281 p_card_instrument.attribute2,
2282 p_card_instrument.attribute3,
2283 p_card_instrument.attribute4,
2284 p_card_instrument.attribute5,
2285 p_card_instrument.attribute6,
2286 p_card_instrument.attribute7,
2287 p_card_instrument.attribute8,
2288 p_card_instrument.attribute9,
2289 p_card_instrument.attribute10,
2290 p_card_instrument.attribute11,
2291 p_card_instrument.attribute12,
2292 p_card_instrument.attribute13,
2293 p_card_instrument.attribute14,
2294 p_card_instrument.attribute15,
2295 p_card_instrument.attribute16,
2296 p_card_instrument.attribute17,
2297 p_card_instrument.attribute18,
2298 p_card_instrument.attribute19,
2299 p_card_instrument.attribute20,
2300 p_card_instrument.attribute21,
2301 p_card_instrument.attribute22,
2302 p_card_instrument.attribute23,
2303 p_card_instrument.attribute24,
2304 p_card_instrument.attribute25,
2305 p_card_instrument.attribute26,
2306 p_card_instrument.attribute27,
2307 p_card_instrument.attribute28,
2308 p_card_instrument.attribute29,
2309 p_card_instrument.attribute30,
2310 lx_result_code, x_card_id,
2311 p_card_instrument.Register_Invalid_Card,
2312 fnd_global.user_id,
2313 fnd_global.login_id
2314 );
2315 END IF;
2316
2317 ELSE
2318
2319 -- card cannot become info only once this flag is turned off
2320 IF (NOT p_card_instrument.Info_Only_Flag = 'Y') THEN
2321 l_info_only := p_card_instrument.Info_Only_Flag;
2322 END IF;
2323
2324 -- validate billing address information
2325 IF (NOT Validate_CC_Billing(FND_API.G_TRUE,p_card_instrument)) THEN
2326 x_response.Result_Code := iby_creditcard_pkg.G_RC_INVALID_ADDRESS;
2327 iby_fndcpt_common_pub.Prepare_Result
2328 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2329 RETURN;
2330 END IF;
2331 -- validate expiration date
2332 IF (TRUNC(p_card_instrument.Expiration_Date,'DD') < TRUNC(SYSDATE,'DD'))
2333 THEN
2334 x_response.Result_Code := iby_creditcard_pkg.G_RC_INVALID_CCEXPIRY;
2335 iby_fndcpt_common_pub.Prepare_Result
2336 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2337 RETURN;
2338 END IF;
2339
2340 iby_creditcard_pkg.Update_Card
2341 (FND_API.G_FALSE, lx_card_rec.Card_Id, p_card_instrument.Owner_Id,
2342 p_card_instrument.Card_Holder_Name,
2343 p_card_instrument.Billing_Address_Id,
2344 p_card_instrument.Address_Type,
2345 p_card_instrument.Billing_Postal_Code,
2346 p_card_instrument.Billing_Address_Territory,
2347 p_card_instrument.Expiration_Date, p_card_instrument.Instrument_Type,
2348 p_card_instrument.PurchaseCard_Flag, p_card_instrument.PurchaseCard_SubType,
2349 p_card_instrument.FI_Name, p_card_instrument.Single_Use_Flag,
2350 l_info_only, p_card_instrument.Card_Purpose,
2351 p_card_instrument.Card_Description, p_card_instrument.Active_Flag,
2352 NVL(p_card_instrument.Inactive_Date,FND_API.G_MISS_DATE),
2353 p_card_instrument.attribute_category,
2354 p_card_instrument.attribute1, p_card_instrument.attribute2,
2355 p_card_instrument.attribute3, p_card_instrument.attribute4,
2356 p_card_instrument.attribute5, p_card_instrument.attribute6,
2357 p_card_instrument.attribute7, p_card_instrument.attribute8,
2358 p_card_instrument.attribute9, p_card_instrument.attribute10,
2359 p_card_instrument.attribute11, p_card_instrument.attribute12,
2360 p_card_instrument.attribute13, p_card_instrument.attribute14,
2361 p_card_instrument.attribute15, p_card_instrument.attribute16,
2362 p_card_instrument.attribute17, p_card_instrument.attribute18,
2363 p_card_instrument.attribute19, p_card_instrument.attribute20,
2364 p_card_instrument.attribute21, p_card_instrument.attribute22,
2365 p_card_instrument.attribute23, p_card_instrument.attribute24,
2366 p_card_instrument.attribute25, p_card_instrument.attribute26,
2367 p_card_instrument.attribute27, p_card_instrument.attribute28,
2368 p_card_instrument.attribute29, p_card_instrument.attribute30,
2369 lx_result_code,
2370 null,null);
2371 x_card_id := lx_card_rec.Card_Id;
2372 END IF;
2373
2374 x_response.Result_Code := NVL(lx_result_code,IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS);
2375 iby_fndcpt_common_pub.Prepare_Result
2376 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2377
2378 IF FND_API.To_Boolean(p_commit) THEN
2379 COMMIT;
2380 END IF;
2381
2382 EXCEPTION
2383
2384 WHEN FND_API.G_EXC_ERROR THEN
2385 ROLLBACK TO Create_Card;
2386 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
2387 debug_level => FND_LOG.LEVEL_ERROR,
2388 module => G_DEBUG_MODULE || l_module);
2389 x_return_status := FND_API.G_RET_STS_ERROR ;
2390 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2391 p_data => x_msg_data
2392 );
2393 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2394 --ROLLBACK TO Create_Card;
2395 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
2396 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2397 module => G_DEBUG_MODULE || l_module);
2398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2399 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2400 p_data => x_msg_data
2401 );
2402 WHEN OTHERS THEN
2403 --ROLLBACK TO Create_Card;
2404 iby_debug_pub.add(debug_msg => 'In OTHERS Exception'||SQLERRM,
2405 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2406 module => G_DEBUG_MODULE || l_module);
2407
2408 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
2409
2410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2411 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2412 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
2413 END IF;
2414
2415 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2416 p_data => x_msg_data
2417 );
2418
2419 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
2420 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2421 module => G_DEBUG_MODULE || l_module);
2422 iby_debug_pub.add(debug_msg => 'Exit Exception',
2423 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2424 module => G_DEBUG_MODULE || l_module);
2425
2426 END Create_Card;
2427
2428 PROCEDURE Update_Card_Wrapper
2429 (
2430 p_commit IN VARCHAR2,
2431 p_instr_id IN iby_creditcard.instrid%TYPE,
2432 p_owner_id IN iby_creditcard.card_owner_id%TYPE,
2433 p_holder_name IN iby_creditcard.chname%TYPE,
2434 p_billing_address_id IN iby_creditcard.addressid%TYPE,
2435 p_address_type IN VARCHAR2,
2436 p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
2437 p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
2438 p_expiry_date IN iby_creditcard.expirydate%TYPE,
2439 p_instr_type IN iby_creditcard.instrument_type%TYPE,
2440 p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
2441 p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
2442 p_fi_name IN iby_creditcard.finame%TYPE,
2443 p_single_use IN iby_creditcard.single_use_flag%TYPE,
2444 p_info_only IN iby_creditcard.information_only_flag%TYPE,
2445 p_purpose IN iby_creditcard.card_purpose%TYPE,
2446 p_desc IN iby_creditcard.description%TYPE,
2447 p_active_flag IN iby_creditcard.active_flag%TYPE,
2448 p_inactive_date IN iby_creditcard.inactive_date%TYPE,
2449 p_attribute_category IN iby_creditcard.attribute_category%TYPE,
2450 p_attribute1 IN iby_creditcard.attribute1%TYPE,
2451 p_attribute2 IN iby_creditcard.attribute2%TYPE,
2452 p_attribute3 IN iby_creditcard.attribute3%TYPE,
2453 p_attribute4 IN iby_creditcard.attribute4%TYPE,
2454 p_attribute5 IN iby_creditcard.attribute5%TYPE,
2455 p_attribute6 IN iby_creditcard.attribute6%TYPE,
2456 p_attribute7 IN iby_creditcard.attribute7%TYPE,
2457 p_attribute8 IN iby_creditcard.attribute8%TYPE,
2458 p_attribute9 IN iby_creditcard.attribute9%TYPE,
2459 p_attribute10 IN iby_creditcard.attribute10%TYPE,
2460 p_attribute11 IN iby_creditcard.attribute11%TYPE,
2461 p_attribute12 IN iby_creditcard.attribute12%TYPE,
2462 p_attribute13 IN iby_creditcard.attribute13%TYPE,
2463 p_attribute14 IN iby_creditcard.attribute14%TYPE,
2464 p_attribute15 IN iby_creditcard.attribute15%TYPE,
2465 p_attribute16 IN iby_creditcard.attribute16%TYPE,
2466 p_attribute17 IN iby_creditcard.attribute17%TYPE,
2467 p_attribute18 IN iby_creditcard.attribute18%TYPE,
2468 p_attribute19 IN iby_creditcard.attribute19%TYPE,
2469 p_attribute20 IN iby_creditcard.attribute20%TYPE,
2470 p_attribute21 IN iby_creditcard.attribute21%TYPE,
2471 p_attribute22 IN iby_creditcard.attribute22%TYPE,
2472 p_attribute23 IN iby_creditcard.attribute23%TYPE,
2473 p_attribute24 IN iby_creditcard.attribute24%TYPE,
2474 p_attribute25 IN iby_creditcard.attribute25%TYPE,
2475 p_attribute26 IN iby_creditcard.attribute26%TYPE,
2476 p_attribute27 IN iby_creditcard.attribute27%TYPE,
2477 p_attribute28 IN iby_creditcard.attribute28%TYPE,
2478 p_attribute29 IN iby_creditcard.attribute29%TYPE,
2479 p_attribute30 IN iby_creditcard.attribute30%TYPE,
2480 x_result_code OUT NOCOPY VARCHAR2,
2481 x_return_status OUT NOCOPY VARCHAR2
2482 )
2483 IS
2484 -- create a record type and populate it
2485 x_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
2486 x_card_instrument CreditCard_rec_type;
2487 x_msg_count NUMBER;
2488 x_msg_data VARCHAR2(3000);
2489 Begin
2490 x_card_instrument.Card_Id := p_instr_id;
2491 x_card_instrument.Owner_Id := p_owner_id;
2492 x_card_instrument.Card_Holder_Name := p_holder_name;
2493 x_card_instrument.Billing_Address_Id := p_billing_address_id;
2494 x_card_instrument.Address_Type := p_address_type;
2495 x_card_instrument.Billing_Postal_Code := p_billing_zip;
2496 x_card_instrument.Billing_Address_Territory := p_billing_country;
2497 x_card_instrument.Expiration_Date := p_expiry_date;
2498 x_card_instrument.Instrument_Type := p_instr_type;
2499 x_card_instrument.PurchaseCard_Flag := p_pcard_flag;
2500 x_card_instrument.PurchaseCard_SubType := p_pcard_type;
2501 x_card_instrument.FI_Name := p_fi_name;
2502 x_card_instrument.Single_Use_Flag := p_single_use;
2503 x_card_instrument.Info_Only_Flag := p_info_only;
2504 x_card_instrument.Card_Purpose := p_purpose;
2505 x_card_instrument.Card_Description := p_desc;
2506 x_card_instrument.Active_Flag := p_active_flag;
2507 x_card_instrument.Inactive_Date := p_inactive_date;
2508 x_card_instrument.attribute_category := p_attribute_category;
2509 x_card_instrument.attribute1 := p_attribute1;
2510 x_card_instrument.attribute2 := p_attribute2;
2511 x_card_instrument.attribute3 := p_attribute3;
2512 x_card_instrument.attribute4 := p_attribute4;
2513 x_card_instrument.attribute5 := p_attribute5;
2514 x_card_instrument.attribute6 := p_attribute6;
2515 x_card_instrument.attribute7 := p_attribute7;
2516 x_card_instrument.attribute8 := p_attribute8;
2517 x_card_instrument.attribute9 := p_attribute9;
2518 x_card_instrument.attribute10 := p_attribute10;
2519 x_card_instrument.attribute11 := p_attribute11;
2520 x_card_instrument.attribute12 := p_attribute12;
2521 x_card_instrument.attribute13 := p_attribute13;
2522 x_card_instrument.attribute14 := p_attribute14;
2523 x_card_instrument.attribute15 := p_attribute15;
2524 x_card_instrument.attribute16 := p_attribute16;
2525 x_card_instrument.attribute17 := p_attribute17;
2526 x_card_instrument.attribute18 := p_attribute18;
2527 x_card_instrument.attribute19 := p_attribute19;
2528 x_card_instrument.attribute20 := p_attribute20;
2529 x_card_instrument.attribute21 := p_attribute21;
2530 x_card_instrument.attribute22 := p_attribute22;
2531 x_card_instrument.attribute23 := p_attribute23;
2532 x_card_instrument.attribute24 := p_attribute24;
2533 x_card_instrument.attribute25 := p_attribute25;
2534 x_card_instrument.attribute26 := p_attribute26;
2535 x_card_instrument.attribute27 := p_attribute27;
2536 x_card_instrument.attribute28 := p_attribute28;
2537 x_card_instrument.attribute29 := p_attribute29;
2538 x_card_instrument.attribute30 := p_attribute30;
2539
2540
2541 -- call Update_Card
2542 Update_Card(1.0,
2543 FND_API.G_FALSE,
2544 p_commit,
2545 x_return_status,
2546 x_msg_count,
2547 x_msg_data,
2548 x_card_instrument,
2549 x_response);
2550 -- Map things back
2551 x_result_code := x_response.Result_Code;
2552
2553 END Update_Card_Wrapper;
2554
2555 PROCEDURE Update_Card
2556 (
2557 p_api_version IN NUMBER,
2558 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2559 p_commit IN VARCHAR2 := FND_API.G_TRUE,
2560 x_return_status OUT NOCOPY VARCHAR2,
2561 x_msg_count OUT NOCOPY NUMBER,
2562 x_msg_data OUT NOCOPY VARCHAR2,
2563 p_card_instrument IN CreditCard_rec_type,
2564 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
2565 )
2566 IS
2567
2568 l_api_version CONSTANT NUMBER := 1.0;
2569 l_module CONSTANT VARCHAR2(30) := 'Update_Card';
2570 l_prev_msg_count NUMBER;
2571
2572 lx_result_code VARCHAR2(30);
2573
2574 l_info_only iby_creditcard.information_only_flag%TYPE := NULL;
2575
2576 BEGIN
2577 IF NOT FND_API.Compatible_API_Call (l_api_version,
2578 p_api_version,
2579 l_module,
2580 G_PKG_NAME)
2581 THEN
2582 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
2583 debug_level => FND_LOG.LEVEL_ERROR,
2584 module => G_DEBUG_MODULE || l_module);
2585 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
2586 FND_MSG_PUB.Add;
2587 RAISE FND_API.G_EXC_ERROR;
2588 END IF;
2589
2590 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2591 FND_MSG_PUB.initialize;
2592 END IF;
2593 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
2594
2595 SAVEPOINT Update_Card;
2596
2597 -- card cannot become info only once this flag is turned off
2598 IF (NOT p_card_instrument.Info_Only_Flag = 'Y') THEN
2599 l_info_only := p_card_instrument.Info_Only_Flag;
2600 END IF;
2601 -- validate billing address information
2602 IF (NOT Validate_CC_Billing(FND_API.G_TRUE,p_card_instrument)) THEN
2603 x_response.Result_Code := iby_creditcard_pkg.G_RC_INVALID_ADDRESS;
2604 iby_fndcpt_common_pub.Prepare_Result
2605 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2606 RETURN;
2607 END IF;
2608
2609 iby_creditcard_pkg.Update_Card
2610 (FND_API.G_FALSE, p_card_instrument.Card_Id, p_card_instrument.Owner_Id,
2611 p_card_instrument.Card_Holder_Name,
2612 p_card_instrument.Billing_Address_Id,
2613 p_card_instrument.Address_Type,
2614 p_card_instrument.Billing_Postal_Code,
2615 p_card_instrument.Billing_Address_Territory,
2616 p_card_instrument.Expiration_Date, p_card_instrument.Instrument_Type,
2617 p_card_instrument.PurchaseCard_Flag, p_card_instrument.PurchaseCard_SubType,
2618 p_card_instrument.FI_Name, p_card_instrument.Single_Use_Flag,
2619 l_info_only, p_card_instrument.Card_Purpose,
2620 p_card_instrument.Card_Description, p_card_instrument.Active_Flag,
2621 p_card_instrument.Inactive_Date,
2622 p_card_instrument.attribute_category,
2623 p_card_instrument.attribute1, p_card_instrument.attribute2,
2624 p_card_instrument.attribute3, p_card_instrument.attribute4,
2625 p_card_instrument.attribute5, p_card_instrument.attribute6,
2626 p_card_instrument.attribute7, p_card_instrument.attribute8,
2627 p_card_instrument.attribute9, p_card_instrument.attribute10,
2628 p_card_instrument.attribute11, p_card_instrument.attribute12,
2629 p_card_instrument.attribute13, p_card_instrument.attribute14,
2630 p_card_instrument.attribute15, p_card_instrument.attribute16,
2631 p_card_instrument.attribute17, p_card_instrument.attribute18,
2632 p_card_instrument.attribute19, p_card_instrument.attribute20,
2633 p_card_instrument.attribute21, p_card_instrument.attribute22,
2634 p_card_instrument.attribute23, p_card_instrument.attribute24,
2635 p_card_instrument.attribute25, p_card_instrument.attribute26,
2636 p_card_instrument.attribute27, p_card_instrument.attribute28,
2637 p_card_instrument.attribute29, p_card_instrument.attribute30,
2638 lx_result_code,
2639 p_card_instrument.Register_Invalid_Card,
2640 p_card_instrument.Card_Issuer);
2641
2642 x_response.Result_Code :=
2643 NVL(lx_result_code,IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS);
2644 iby_fndcpt_common_pub.Prepare_Result
2645 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2646
2647 IF FND_API.To_Boolean(p_commit) THEN
2648 COMMIT;
2649 END IF;
2650
2651 EXCEPTION
2652
2653 WHEN FND_API.G_EXC_ERROR THEN
2654 ROLLBACK TO Update_Card;
2655 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
2656 debug_level => FND_LOG.LEVEL_ERROR,
2657 module => G_DEBUG_MODULE || l_module);
2658 x_return_status := FND_API.G_RET_STS_ERROR ;
2659 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2660 p_data => x_msg_data
2661 );
2662 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2663 ROLLBACK TO Update_Card;
2664 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
2665 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2666 module => G_DEBUG_MODULE || l_module);
2667 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2668 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2669 p_data => x_msg_data
2670 );
2671 WHEN OTHERS THEN
2672 ROLLBACK TO Update_Card;
2673 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
2674 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2675 module => G_DEBUG_MODULE || l_module);
2676
2677 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
2678
2679 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2680 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2681 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
2682 END IF;
2683
2684 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2685 p_data => x_msg_data
2686 );
2687
2688 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
2689 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2690 module => G_DEBUG_MODULE || l_module);
2691 iby_debug_pub.add(debug_msg => 'Exit Exception',
2692 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2693 module => G_DEBUG_MODULE || l_module);
2694
2695 END Update_Card;
2696
2697 PROCEDURE Get_Card
2698 (
2699 p_api_version IN NUMBER,
2700 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2701 x_return_status OUT NOCOPY VARCHAR2,
2702 x_msg_count OUT NOCOPY NUMBER,
2703 x_msg_data OUT NOCOPY VARCHAR2,
2704 p_card_id NUMBER,
2705 x_card_instrument OUT NOCOPY CreditCard_rec_type,
2706 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
2707 )
2708 IS
2709 l_api_version CONSTANT NUMBER := 1.0;
2710 l_module CONSTANT VARCHAR2(30) := 'Get_Card';
2711 l_prev_msg_count NUMBER;
2712
2713 l_card_count NUMBER;
2714
2715 CURSOR c_card(ci_card_id IN iby_creditcard.instrid%TYPE)
2716 IS
2717 SELECT card_owner_id, chname, addressid, masked_cc_number,
2718 expirydate, DECODE(expirydate, null,expired_flag, decode(sign(expirydate-sysdate),-1,'Y','N')),
2719 instrument_type,purchasecard_subtype, card_issuer_code, finame, single_use_flag,
2720 information_only_flag, card_purpose, description, inactive_date
2721 FROM iby_creditcard
2722 WHERE (instrid = ci_card_id);
2723 BEGIN
2724 IF (c_card%ISOPEN) THEN
2725 CLOSE c_card;
2726 END IF;
2727
2728 IF NOT FND_API.Compatible_API_Call (l_api_version,
2729 p_api_version,
2730 l_module,
2731 G_PKG_NAME)
2732 THEN
2733 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
2734 debug_level => FND_LOG.LEVEL_ERROR,
2735 module => G_DEBUG_MODULE || l_module);
2736 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
2737 FND_MSG_PUB.Add;
2738 RAISE FND_API.G_EXC_ERROR;
2739 END IF;
2740
2741 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2742 FND_MSG_PUB.initialize;
2743 END IF;
2744 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
2745
2746 OPEN c_card(p_card_id);
2747 FETCH c_card INTO x_card_instrument.Owner_Id, x_card_instrument.Card_Holder_Name,
2748 x_card_instrument.Billing_Address_Id, x_card_instrument.Card_Number,
2749 x_card_instrument.Expiration_Date,
2750 x_card_instrument.Expired_Flag, x_card_instrument.Instrument_Type,
2751 x_card_instrument.Purchasecard_Subtype, x_card_instrument.Card_Issuer,
2752 x_card_instrument.FI_Name, x_card_instrument.Single_Use_Flag,
2753 x_card_instrument.Info_Only_Flag, x_card_instrument.Card_Purpose,
2754 x_card_instrument.Card_Description, x_card_instrument.Inactive_Date;
2755
2756 IF (c_card%NOTFOUND) THEN
2757 x_response.Result_Code := G_RC_INVALID_INSTRUMENT;
2758 ELSE
2759 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
2760 x_card_instrument.Card_Id := p_card_id;
2761 END IF;
2762
2763 iby_fndcpt_common_pub.Prepare_Result
2764 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2765
2766 EXCEPTION
2767
2768 WHEN FND_API.G_EXC_ERROR THEN
2769
2770 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
2771 debug_level => FND_LOG.LEVEL_ERROR,
2772 module => G_DEBUG_MODULE || l_module);
2773 x_return_status := FND_API.G_RET_STS_ERROR ;
2774 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2775 p_data => x_msg_data
2776 );
2777 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2778
2779 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
2780 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2781 module => G_DEBUG_MODULE || l_module);
2782 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2783 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2784 p_data => x_msg_data
2785 );
2786 WHEN OTHERS THEN
2787
2788 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
2789 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2790 module => G_DEBUG_MODULE || l_module);
2791
2792 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
2793
2794 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2795 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2796 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
2797 END IF;
2798
2799 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2800 p_data => x_msg_data
2801 );
2802
2803 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
2804 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2805 module => G_DEBUG_MODULE || l_module);
2806 iby_debug_pub.add(debug_msg => 'Exit Exception',
2807 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2808 module => G_DEBUG_MODULE || l_module);
2809 END;
2810
2811 PROCEDURE Card_Exists
2812 (
2813 p_api_version IN NUMBER,
2814 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2815 x_return_status OUT NOCOPY VARCHAR2,
2816 x_msg_count OUT NOCOPY NUMBER,
2817 x_msg_data OUT NOCOPY VARCHAR2,
2818 p_owner_id NUMBER,
2819 p_card_number VARCHAR2,
2820 x_card_instrument OUT NOCOPY CreditCard_rec_type,
2821 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type,
2822 p_card_instr_type VARCHAR2 DEFAULT NULL
2823 )
2824 IS
2825 l_api_version CONSTANT NUMBER := 1.0;
2826 l_module CONSTANT VARCHAR2(30) := 'Card_Exists';
2827 l_prev_msg_count NUMBER;
2828
2829 l_card_id iby_creditcard.instrid%TYPE;
2830 l_cc_hash1 iby_creditcard.cc_number_hash1%TYPE;
2831 l_cc_hash2 iby_creditcard.cc_number_hash2%TYPE;
2832 l_char_allowed VARCHAR2(1) := 'N';
2833 lx_return_status VARCHAR2(1);
2834 lx_msg_count NUMBER;
2835 lx_msg_data VARCHAR2(200);
2836 lx_cc_number iby_creditcard.ccnumber%TYPE;
2837 lx_result IBY_FNDCPT_COMMON_PUB.Result_rec_type;
2838
2839 CURSOR c_card
2840 (ci_cc_hash1 IN iby_creditcard.cc_number_hash1%TYPE,
2841 ci_cc_hash2 IN iby_creditcard.cc_number_hash2%TYPE,
2842 ci_card_owner IN iby_creditcard.card_owner_id%TYPE
2843 )
2844 IS
2845 SELECT instrid
2846 FROM iby_creditcard
2847 WHERE (cc_number_hash1 = ci_cc_hash1)
2848 AND (cc_number_hash2 = ci_cc_hash2)
2849 AND ( (card_owner_id = NVL(ci_card_owner,card_owner_id))
2850 OR (card_owner_id IS NULL AND ci_card_owner IS NULL) ); --Removed singleUseFlag validation to avoid duplicate singleusecard creation.
2851
2852 BEGIN
2853
2854 IF (c_card%ISOPEN) THEN
2855 CLOSE c_card;
2856 END IF;
2857
2858 IF NOT FND_API.Compatible_API_Call (l_api_version,
2859 p_api_version,
2860 l_module,
2861 G_PKG_NAME)
2862 THEN
2863 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
2864 debug_level => FND_LOG.LEVEL_ERROR,
2865 module => G_DEBUG_MODULE || l_module);
2866 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
2867 FND_MSG_PUB.Add;
2868 RAISE FND_API.G_EXC_ERROR;
2869 END IF;
2870
2871 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2872 FND_MSG_PUB.initialize;
2873 END IF;
2874 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
2875 IF (nvl(p_card_instr_type,IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_CREDITCARD ) = IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_PAYMENTCARD) THEN
2876 l_char_allowed := 'Y';
2877 END IF;
2878
2879 iby_cc_validate.StripCC
2880 (1.0, FND_API.G_FALSE, p_card_number,
2881 lx_return_status, lx_msg_count, lx_msg_data, lx_cc_number
2882 );
2883
2884 IF (lx_cc_number IS NULL) THEN
2885 x_response.Result_Code := iby_creditcard_pkg.G_RC_INVALID_CCNUMBER;
2886 iby_fndcpt_common_pub.Prepare_Result
2887 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2888 RETURN;
2889 END IF;
2890
2891 l_cc_hash1 := iby_security_pkg.get_hash(lx_cc_number,'F');
2892 l_cc_hash2 := iby_security_pkg.get_hash(lx_cc_number,'T');
2893
2894 OPEN c_card(l_cc_hash1,l_cc_hash2,p_owner_id);
2895 FETCH c_card INTO l_card_id;
2896 CLOSE c_card;
2897
2898 IF (l_card_id IS NULL) THEN
2899 x_response.Result_Code := G_RC_UNKNOWN_CARD;
2900 ELSE
2901 IBY_FNDCPT_SETUP_PUB.Get_Card
2902 (
2903 1.0,
2904 FND_API.G_FALSE,
2905 x_return_status,
2906 x_msg_count,
2907 x_msg_data,
2908 l_card_id,
2909 x_card_instrument,
2910 lx_result
2911 );
2912 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
2913 END IF;
2914 iby_fndcpt_common_pub.Prepare_Result
2915 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2916
2917 EXCEPTION
2918
2919 WHEN FND_API.G_EXC_ERROR THEN
2920
2921 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
2922 debug_level => FND_LOG.LEVEL_ERROR,
2923 module => G_DEBUG_MODULE || l_module);
2924 x_return_status := FND_API.G_RET_STS_ERROR ;
2925 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2926 p_data => x_msg_data
2927 );
2928 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2929
2930 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
2931 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2932 module => G_DEBUG_MODULE || l_module);
2933 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2934 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2935 p_data => x_msg_data
2936 );
2937 WHEN OTHERS THEN
2938
2939 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
2940 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2941 module => G_DEBUG_MODULE || l_module);
2942
2943 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
2944
2945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2946 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2947 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
2948 END IF;
2949
2950 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2951 p_data => x_msg_data
2952 );
2953
2954 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
2955 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2956 module => G_DEBUG_MODULE || l_module);
2957 iby_debug_pub.add(debug_msg => 'Exit Exception',
2958 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2959 module => G_DEBUG_MODULE || l_module);
2960 END Card_Exists;
2961
2962 PROCEDURE Process_Credit_Card
2963 (
2964 p_api_version IN NUMBER,
2965 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2966 p_commit IN VARCHAR2 := FND_API.G_TRUE,
2967 x_return_status OUT NOCOPY VARCHAR2,
2968 x_msg_count OUT NOCOPY NUMBER,
2969 x_msg_data OUT NOCOPY VARCHAR2,
2970 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
2971 p_credit_card IN CreditCard_rec_type,
2972 p_assignment_attribs IN PmtInstrAssignment_rec_type,
2973 x_assign_id OUT NOCOPY NUMBER,
2974 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
2975 )
2976 IS
2977 l_api_version CONSTANT NUMBER := 1.0;
2978 l_module CONSTANT VARCHAR2(30) := 'Process_Credit_Card';
2979 l_prev_msg_count NUMBER;
2980
2981 l_existing_msgs NUMBER;
2982 lx_return_status VARCHAR2(1);
2983 lx_msg_count NUMBER;
2984 lx_msg_data VARCHAR2(2000);
2985
2986 lx_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
2987 lx_assign_attribs PmtInstrAssignment_rec_type;
2988
2989 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
2990 BEGIN
2991 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
2992
2993 IF NOT FND_API.Compatible_API_Call (l_api_version,
2994 p_api_version,
2995 l_module,
2996 G_PKG_NAME)
2997 THEN
2998 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
2999 debug_level => FND_LOG.LEVEL_ERROR,
3000 module => G_DEBUG_MODULE || l_module);
3001 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
3002 FND_MSG_PUB.Add;
3003 RAISE FND_API.G_EXC_ERROR;
3004 END IF;
3005
3006 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3007 FND_MSG_PUB.initialize;
3008 END IF;
3009 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
3010
3011 l_existing_msgs := NVL(x_msg_count,0);
3012
3013 SAVEPOINT Process_Credit_Card;
3014
3015 lx_assign_attribs := p_assignment_attribs;
3016
3017 iby_debug_pub.add('create card',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3018
3019 Create_Card
3020 (1.0, FND_API.G_FALSE, FND_API.G_FALSE, lx_return_status, lx_msg_count,
3021 lx_msg_data, p_credit_card,
3022 lx_assign_attribs.Instrument.Instrument_Id,
3023 lx_response
3024 );
3025
3026 IF (lx_response.Result_Code <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS) THEN
3027 iby_debug_pub.add('rollback',iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
3028 ROLLBACK TO Process_Credit_Card;
3029 x_response := lx_response;
3030 ELSE
3031
3032 lx_assign_attribs.Instrument.Instrument_Type :=
3033 IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_CREDITCARD;
3034 Set_Payer_Instr_Assignment
3035 (1.0, FND_API.G_FALSE, FND_API.G_FALSE, x_return_status, x_msg_count,
3036 x_msg_data, p_payer, lx_assign_attribs, x_assign_id,
3037 x_response
3038 );
3039 END IF;
3040
3041 iby_fndcpt_common_pub.Prepare_Result
3042 (iby_fndcpt_common_pub.G_INTERFACE_CODE,x_response.Result_Message,
3043 l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
3044
3045 IF FND_API.To_Boolean(p_commit) THEN
3046 COMMIT;
3047 END IF;
3048
3049 EXCEPTION
3050
3051 WHEN FND_API.G_EXC_ERROR THEN
3052 ROLLBACK TO Process_Credit_Card;
3053 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
3054 debug_level => FND_LOG.LEVEL_ERROR,
3055 module => G_DEBUG_MODULE || l_module);
3056 x_return_status := FND_API.G_RET_STS_ERROR ;
3057 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3058 p_data => x_msg_data
3059 );
3060 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3061 ROLLBACK TO Process_Credit_Card;
3062 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
3063 debug_level => FND_LOG.LEVEL_UNEXPECTED,
3064 module => G_DEBUG_MODULE || l_module);
3065 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3066 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3067 p_data => x_msg_data
3068 );
3069 WHEN OTHERS THEN
3070 ROLLBACK TO Process_Credit_Card;
3071 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
3072 debug_level => FND_LOG.LEVEL_UNEXPECTED,
3073 module => G_DEBUG_MODULE || l_module);
3074
3075 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
3076
3077 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3078 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3079 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
3080 END IF;
3081
3082 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
3083 p_data => x_msg_data
3084 );
3085
3086 END Process_Credit_Card;
3087
3088 FUNCTION Get_Hash(p_number IN VARCHAR2, p_salt IN VARCHAR2) RETURN VARCHAR2
3089 IS
3090 lx_return_status VARCHAR2(1);
3091 lx_msg_count NUMBER;
3092 lx_msg_data VARCHAR2(200);
3093 lx_cc_number iby_creditcard.ccnumber%TYPE;
3094 BEGIN
3095 iby_cc_validate.StripCC
3096 (1.0, FND_API.G_FALSE, p_number,
3097 lx_return_status, lx_msg_count, lx_msg_data, lx_cc_number
3098 );
3099 RETURN iby_security_pkg.get_hash(lx_cc_number,p_salt);
3100 END Get_Hash;
3101
3102 FUNCTION Get_Hash(p_number IN VARCHAR2, p_salt IN VARCHAR2, p_site_salt IN VARCHAR2)
3103 RETURN VARCHAR2
3104 IS
3105 lx_return_status VARCHAR2(1);
3106 lx_msg_count NUMBER;
3107 lx_msg_data VARCHAR2(200);
3108 lx_cc_number iby_creditcard.ccnumber%TYPE;
3109 BEGIN
3110 iby_cc_validate.StripCC
3111 (1.0, FND_API.G_FALSE, p_number,
3112 lx_return_status, lx_msg_count, lx_msg_data, lx_cc_number
3113 );
3114 RETURN iby_security_pkg.get_hash(lx_cc_number,p_salt,p_site_salt);
3115 END Get_Hash;
3116
3117 PROCEDURE Get_Trxn_Payer_Attributes
3118 (
3119 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
3120 p_payer_equivalency IN VARCHAR2
3121 := IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
3122 x_payer_attributes OUT NOCOPY PayerAttributes_rec_type
3123 )
3124 IS
3125
3126 l_payer_level VARCHAR2(30);
3127 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
3128 l_payer_attribs IBY_FNDCPT_SETUP_PUB.PayerAttributes_rec_type;
3129
3130 CURSOR l_payer_attr_cur (
3131 ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
3132 ci_payer_level IN VARCHAR2,
3133 ci_payer_equiv IN VARCHAR2
3134 )
3135 IS
3136 SELECT bank_charge_bearer_code, dirdeb_instruction_code
3137 FROM iby_external_payers_all p
3138 WHERE p.party_id = ci_payer.Party_Id
3139 AND IBY_FNDCPT_COMMON_PUB.Compare_Payer
3140 (ci_payer.org_type, ci_payer.org_id,
3141 ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
3142 ci_payer_level,ci_payer_equiv,p.org_type,p.org_id,
3143 p.cust_account_id,p.acct_site_use_id) = 'T'
3144 ORDER BY p.acct_site_use_id, p.cust_account_id, p.org_id;
3145
3146 BEGIN
3147
3148 IBY_FNDCPT_SETUP_PUB.Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
3149 l_payer_level,l_payer_id,l_payer_attribs);
3150
3151 FOR l_payer_attr_rec in l_payer_attr_cur(p_payer,l_payer_level,p_payer_equivalency) LOOP
3152 IF (x_payer_attributes.Bank_Charge_Bearer is NULL) THEN
3153 x_payer_attributes.Bank_Charge_Bearer := l_payer_attr_rec.bank_charge_bearer_code;
3154 END IF;
3155
3156 IF (x_payer_attributes.DirectDebit_BankInstruction is NULL) THEN
3157 x_payer_attributes.DirectDebit_BankInstruction := l_payer_attr_rec.dirdeb_instruction_code;
3158 END IF;
3159 END LOOP;
3160
3161 END Get_Trxn_Payer_Attributes;
3162
3163
3164 --
3165 -- USE: Gets the card expiration status w.r.t an input date
3166 --
3167 --
3168 PROCEDURE Get_Card_Expiration_Status
3169 (p_instrid IN IBY_CREDITCARD.instrid%TYPE,
3170 p_input_date IN DATE,
3171 x_expired OUT NOCOPY VARCHAR2,
3172 x_result_code OUT NOCOPY VARCHAR2
3173 )
3174 IS
3175 l_msg_count NUMBER;
3176 l_msg_data VARCHAR2(300);
3177 l_return_status VARCHAR2(1);
3178 l_resp_rec IBY_INSTRREG_PUB.GetExpStatusResp_rec_type;
3179
3180 l_exp_sec_segment_id NUMBER;
3181 l_expiry_date DATE;
3182
3183 l_dbg_mod VARCHAR2(100) := 'iby.plsql.IBY_FNDCPT_SETUP_PUB' || '.' || 'Get_Expiration_Status';
3184 BEGIN
3185 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
3186 x_result_code := FND_API.G_RET_STS_SUCCESS;
3187
3188 SELECT expirydate, expiry_sec_segment_id
3189 INTO l_expiry_date, l_exp_sec_segment_id
3190 FROM iby_creditcard
3191 WHERE instrid = p_instrid;
3192
3193 IF ((l_expiry_date IS NULL) AND (l_exp_sec_segment_id IS NULL)) THEN
3194 RETURN;
3195 END IF;
3196
3197 IF(l_expiry_date IS NOT NULL)THEN
3198 IF (TRUNC(l_expiry_date,'DD') < TRUNC(p_input_date,'DD')) THEN
3199 x_expired := 'Y';
3200 ELSE
3201 x_expired := 'N';
3202 END IF;
3203 RETURN;
3204 END IF;
3205
3206 IBY_INSTRREG_PUB.Get_Expiration_Status(p_instrid,
3207 p_input_date,
3208 l_return_status,
3209 l_msg_count,
3210 l_msg_data,
3211 l_resp_rec
3212 );
3213 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
3214 iby_debug_pub.add('Error during http call out',iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
3215 x_result_code := FND_API.G_RET_STS_ERROR;
3216 RETURN;
3217 END IF;
3218 x_expired := l_resp_rec.Expired;
3219 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
3220 END Get_Card_Expiration_Status;
3221
3222 FUNCTION Get_Encryption_Patch_Level
3223 RETURN VARCHAR2
3224 IS
3225 enc_level VARCHAR2(30);
3226 BEGIN
3227 SELECT NVL(encryption_patch_level, G_ENC_PATCH_LEVEL_NORMAL)
3228 INTO enc_level
3229 FROM iby_sys_security_options;
3230
3231 RETURN enc_level;
3232 END Get_Encryption_Patch_Level;
3233
3234 --SEPA DD Project changes
3235 PROCEDURE Create_Debit_Authorization
3236 (p_debit_auth_id IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTHORIZATION_ID%TYPE,
3237 p_bank_use_id IN IBY_DEBIT_AUTHORIZATIONS.EXTERNAL_BANK_ACCOUNT_USE_ID%TYPE,
3238 p_auth_ref_number IN IBY_DEBIT_AUTHORIZATIONS.AUTHORIZATION_REFERENCE_NUMBER%TYPE,
3239 p_initial_debit_auth_id IN IBY_DEBIT_AUTHORIZATIONS.INITIAL_DEBIT_AUTHORIZATION_ID%TYPE,
3240 p_auth_rev_number IN IBY_DEBIT_AUTHORIZATIONS.AUTHORIZATION_REVISION_NUMBER%TYPE,
3241 p_payment_code IN IBY_DEBIT_AUTHORIZATIONS.PAYMENT_TYPE_CODE%TYPE,
3242 p_amend_readon_code IN IBY_DEBIT_AUTHORIZATIONS.AMENDMENT_REASON_CODE%TYPE,
3243 p_auth_sign_date IN IBY_DEBIT_AUTHORIZATIONS.AUTH_SIGN_DATE%TYPE,
3244 p_auth_cancel_date IN IBY_DEBIT_AUTHORIZATIONS.AUTH_CANCEL_DATE%TYPE,
3245 p_debit_auth_method IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_METHOD%TYPE,
3246 p_pre_notif_flag IN IBY_DEBIT_AUTHORIZATIONS.PRE_NOTIFICATION_REQUIRED_FLAG%TYPE,
3247 p_creditor_id IN IBY_DEBIT_AUTHORIZATIONS.CREDITOR_LEGAL_ENTITY_ID%TYPE,
3248 p_creditor_name IN IBY_DEBIT_AUTHORIZATIONS.CREDITOR_LE_NAME%TYPE,
3249 p_debit_auth_begin IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_BEGIN%TYPE,
3250 p_cust_addr_id IN IBY_DEBIT_AUTHORIZATIONS.CUST_ADDR_ID%TYPE,
3251 p_debit_auth_flag IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_FLAG%TYPE,
3252 p_debit_auth_ref IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_REFERENCE%TYPE,
3253 p_cust_id_code IN IBY_DEBIT_AUTHORIZATIONS.CUST_IDENTIFICATION_CODE%TYPE,
3254 p_creditor_identifer IN IBY_DEBIT_AUTHORIZATIONS.CREDITOR_IDENTIFIER%TYPE,
3255 p_debit_auth_end IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_END%TYPE,
3256 p_mandate_file IN IBY_DEBIT_AUTHORIZATIONS.MANDATE_FILE%TYPE,
3257 x_result OUT NOCOPY NUMBER)
3258 IS
3259
3260 l_module CONSTANT VARCHAR2(30) := 'Create_Debit_Authorization';
3261 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
3262
3263 BEGIN
3264 print_debuginfo('Enter',iby_debug_pub.G_LEVEL_PROCEDURE, l_dbg_mod);
3265 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3266 print_debuginfo('DEBIT_AUTHORIZATION_ID:'||
3267 p_debit_auth_id,iby_debug_pub.G_LEVEL_INFO, l_dbg_mod);
3268 print_debuginfo('EXTERNAL_BANK_ACCOUNT_USE_ID:'||
3269 p_bank_use_id,iby_debug_pub.G_LEVEL_INFO, l_dbg_mod);
3270 print_debuginfo('AUTHORIZATION_REFERENCE_NUMBER:'||
3271 p_auth_ref_number,iby_debug_pub.G_LEVEL_INFO, l_dbg_mod);
3272 print_debuginfo('INITIAL_DEBIT_AUTHORIZATION_ID:'||
3273 p_initial_debit_auth_id,iby_debug_pub.G_LEVEL_INFO, l_dbg_mod);
3274 print_debuginfo('AUTHORIZATION_REVISION_NUMBER:'||
3275 p_auth_rev_number,iby_debug_pub.G_LEVEL_INFO, l_dbg_mod);
3276 print_debuginfo('AUTH_SIGN_DATE:'||
3277 p_auth_sign_date,iby_debug_pub.G_LEVEL_INFO, l_dbg_mod);
3278 print_debuginfo('DEBIT_AUTH_BEGIN'||
3279 p_debit_auth_begin,iby_debug_pub.G_LEVEL_INFO, l_dbg_mod);
3280 END IF;
3281
3282 INSERT INTO IBY_DEBIT_AUTHORIZATIONS
3283 (DEBIT_AUTHORIZATION_ID,
3284 EXTERNAL_BANK_ACCOUNT_USE_ID, AUTHORIZATION_REFERENCE_NUMBER,
3285 INITIAL_DEBIT_AUTHORIZATION_ID, AUTHORIZATION_REVISION_NUMBER,
3286 PAYMENT_TYPE_CODE,AMENDMENT_REASON_CODE,
3287 AUTH_SIGN_DATE,AUTH_CANCEL_DATE,DEBIT_AUTH_METHOD,
3288 PRE_NOTIFICATION_REQUIRED_FLAG,CREDITOR_LEGAL_ENTITY_ID,
3289 CREDITOR_LE_NAME,DEBIT_AUTH_BEGIN,created_by,
3290 creation_date, last_updated_by, last_update_date,
3291 last_update_login, object_version_number,CUST_ADDR_ID,
3292 DEBIT_AUTH_FLAG,DEBIT_AUTH_REFERENCE,CUST_IDENTIFICATION_CODE,
3293 CREDITOR_IDENTIFIER,DEBIT_AUTH_END, CURR_REC_INDI,MANDATE_FILE)
3294
3295 VALUES
3296
3297 (p_debit_auth_id,
3298 p_bank_use_id, p_auth_ref_number,
3299 p_initial_debit_auth_id,p_auth_rev_number,
3300 p_payment_code,p_amend_readon_code,
3301 p_auth_sign_date,p_auth_cancel_date,p_debit_auth_method,
3302 p_pre_notif_flag,p_creditor_id,
3303 p_creditor_name,p_debit_auth_begin,fnd_global.user_id,
3304 SYSDATE, fnd_global.user_id, SYSDATE,
3305 fnd_global.login_id, 1,p_cust_addr_id,
3306 p_debit_auth_flag,p_debit_auth_ref,p_cust_id_code,
3307 p_creditor_identifer,p_debit_auth_end, 'Y', p_mandate_file);
3308 COMMIT;
3309 x_result:=1;
3310 print_debuginfo('x_result:'|| x_result,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3311 EXCEPTION
3312 WHEN OTHERS THEN
3313 x_result:=0;
3314 print_debuginfo('Exception occured while inserting the mandate:' ||
3315 sqlerrm,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3316 raise;
3317 print_debuginfo('End',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
3318 END Create_Debit_Authorization;
3319
3320 -- Procedure to update the existing Mandate
3321 PROCEDURE Update_Debit_Authorization
3322 (p_debit_auth_id IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTHORIZATION_ID%TYPE,
3323 p_bank_use_id IN IBY_DEBIT_AUTHORIZATIONS.EXTERNAL_BANK_ACCOUNT_USE_ID%TYPE,
3324 p_auth_ref_number IN IBY_DEBIT_AUTHORIZATIONS.AUTHORIZATION_REFERENCE_NUMBER%TYPE,
3325 p_initial_debit_auth_id IN IBY_DEBIT_AUTHORIZATIONS.INITIAL_DEBIT_AUTHORIZATION_ID%TYPE,
3326 p_auth_rev_number IN IBY_DEBIT_AUTHORIZATIONS.AUTHORIZATION_REVISION_NUMBER%TYPE,
3327 p_payment_code IN IBY_DEBIT_AUTHORIZATIONS.PAYMENT_TYPE_CODE%TYPE,
3328 p_amend_readon_code IN IBY_DEBIT_AUTHORIZATIONS.AMENDMENT_REASON_CODE%TYPE,
3329 p_auth_sign_date IN IBY_DEBIT_AUTHORIZATIONS.AUTH_SIGN_DATE%TYPE,
3330 p_auth_cancel_date IN IBY_DEBIT_AUTHORIZATIONS.AUTH_CANCEL_DATE%TYPE,
3331 p_debit_auth_method IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_METHOD%TYPE,
3332 p_pre_notif_flag IN IBY_DEBIT_AUTHORIZATIONS.PRE_NOTIFICATION_REQUIRED_FLAG%TYPE,
3333 p_creditor_id IN IBY_DEBIT_AUTHORIZATIONS.CREDITOR_LEGAL_ENTITY_ID%TYPE,
3334 p_creditor_name IN IBY_DEBIT_AUTHORIZATIONS.CREDITOR_LE_NAME%TYPE,
3335 p_debit_auth_begin IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_BEGIN%TYPE,
3336 p_cust_addr_id IN IBY_DEBIT_AUTHORIZATIONS.CUST_ADDR_ID%TYPE,
3337 p_debit_auth_flag IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_FLAG%TYPE,
3338 p_debit_auth_ref IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_REFERENCE%TYPE,
3339 p_cust_id_code IN IBY_DEBIT_AUTHORIZATIONS.CUST_IDENTIFICATION_CODE%TYPE,
3340 p_creditor_identifer IN IBY_DEBIT_AUTHORIZATIONS.CREDITOR_IDENTIFIER%TYPE,
3341 p_debit_auth_end IN IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_END%TYPE,
3342 p_mandate_file IN IBY_DEBIT_AUTHORIZATIONS.MANDATE_FILE%TYPE,
3343 x_result OUT NOCOPY NUMBER)
3344 IS
3345 l_module CONSTANT VARCHAR2(30) := 'Update_Debit_Authorization';
3346 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
3347 l_seq_number NUMBER;
3348
3349 l_debit_auth_flag IBY_DEBIT_AUTHORIZATIONS.DEBIT_AUTH_FLAG%TYPE;
3350 l_auth_ref_number IBY_DEBIT_AUTHORIZATIONS.AUTHORIZATION_REFERENCE_NUMBER%TYPE;
3351 l_creditor_name IBY_DEBIT_AUTHORIZATIONS.CREDITOR_LE_NAME%TYPE;
3352 l_creditor_identifer IBY_DEBIT_AUTHORIZATIONS.CREDITOR_IDENTIFIER%TYPE;
3353
3354
3355 --l_trxnmid NUMBER;
3356 --l_trxn_list TrxnId_tbl_type;
3357
3358 CURSOR trxn_ref(AUTH_ID IN IBY_TRXN_SUMMARIES_ALL.DEBIT_AUTHORIZATION_ID%TYPE) IS
3359 (SELECT TRXNMID FROM IBY_TRXN_SUMMARIES_ALL WHERE DEBIT_AUTHORIZATION_ID = AUTH_ID
3360 AND REQTYPE = 'ORAPMTBATCHREQ' AND TRXNTYPEID = 100 AND INSTRTYPE = 'BANKACCOUNT' AND STATUS = 100);
3361
3362
3363 BEGIN
3364
3365 IF (trxn_ref%ISOPEN) THEN
3366 CLOSE trxn_ref;
3367 END IF;
3368 print_debuginfo('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
3369
3370 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3371 print_debuginfo('p_debit_auth_flag:'||
3372 p_debit_auth_flag,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3373 print_debuginfo('p_creditor_name:'||
3374 p_creditor_name,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3375 print_debuginfo('p_creditor_identifer:'||
3376 p_creditor_identifer,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3377 END IF;
3378
3379 SELECT AUTHORIZATION_REFERENCE_NUMBER, CREDITOR_LE_NAME, CREDITOR_IDENTIFIER
3380 into l_auth_ref_number, l_creditor_name, l_creditor_identifer
3381 FROM IBY_DEBIT_AUTHORIZATIONS
3382 WHERE
3383 DEBIT_AUTHORIZATION_ID = p_debit_auth_id;
3384
3385 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3386 print_debuginfo('l_auth_ref_number:'||
3387 l_auth_ref_number,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3388 print_debuginfo('L_creditor_name:'||
3389 l_creditor_name,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3390 print_debuginfo('L_creditor_identifer:'||
3391 l_creditor_identifer,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3392 print_debuginfo('p_debit_auth_id:'||
3393 p_debit_auth_id,iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
3394 END IF;
3395 -- Bug# 9508632
3396 -- Comparing the unique auth ref number
3397 IF(p_auth_ref_number <>l_auth_ref_number OR
3398 p_creditor_name <> l_creditor_name OR
3399 p_creditor_identifer <> l_creditor_identifer)
3400 THEN
3401 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3402 print_debuginfo('Before Updating mandate',
3403 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3404 END IF;
3405
3406 UPDATE IBY_DEBIT_AUTHORIZATIONS SET DEBIT_AUTH_END = SYSDATE,
3407 CURR_REC_INDI = 'N'
3408 WHERE DEBIT_AUTHORIZATION_ID = p_debit_auth_id;
3409
3410 select IBY_DEBIT_AUTHORIZATIONS_S.nextval into l_seq_number from dual;
3411
3412 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3413 print_debuginfo('Creating the new Mandate:',
3414 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3415 END IF;
3416
3417 Create_Debit_Authorization(l_seq_number,
3418 p_bank_use_id,
3419 p_auth_ref_number,
3420 p_initial_debit_auth_id,
3421 p_auth_rev_number+1,
3422 p_payment_code,
3423 p_amend_readon_code,
3424 p_auth_sign_date,
3425 p_auth_cancel_date,
3426 p_debit_auth_method,
3427 p_pre_notif_flag,
3428 p_creditor_id,
3429 p_creditor_name,
3430 p_debit_auth_begin,
3431 p_cust_addr_id,
3432 p_debit_auth_flag,
3433 p_debit_auth_ref,
3434 p_cust_id_code,
3435 p_creditor_identifer,
3436 p_debit_auth_end,
3437 p_mandate_file,
3438 x_result);
3439
3440
3441 -- Update mandate to have the updated debit authorization id. .
3442
3443 FOR trxn_rec IN trxn_ref(p_debit_auth_id)
3444 LOOP
3445 UPDATE IBY_TRXN_SUMMARIES_ALL SET DEBIT_AUTHORIZATION_ID = l_seq_number where debit_authorization_id = p_debit_auth_id and trxnmid = trxn_rec.trxnmid;
3446
3447 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3448 print_debuginfo('Trxn Id : ' || trxn_rec.trxnmid || 'with debit-authorization_id = ' || p_debit_auth_id || 'updated with debit auth : ' || l_seq_number,
3449 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3450 END IF;
3451
3452 END LOOP;
3453 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3454 print_debuginfo('UPDATING THE TRANSACTION WITH THE NEW DEBIT_AUTHORIZATION_ID',
3455 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3456 END IF;
3457
3458
3459
3460
3461 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3462 print_debuginfo('After creating the new Mandate:',
3463 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3464 END IF;
3465
3466 ELSE
3467 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3468 print_debuginfo('Updating mandate12:',
3469 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3470 END IF;
3471
3472 UPDATE IBY_DEBIT_AUTHORIZATIONS
3473 SET
3474 EXTERNAL_BANK_ACCOUNT_USE_ID = p_bank_use_id,
3475 AUTHORIZATION_REFERENCE_NUMBER = p_auth_ref_number,
3476 INITIAL_DEBIT_AUTHORIZATION_ID = p_initial_debit_auth_id,
3477 AUTHORIZATION_REVISION_NUMBER = p_auth_rev_number,
3478 AMENDMENT_REASON_CODE = p_amend_readon_code,
3479 AUTH_SIGN_DATE = p_auth_sign_date,
3480 AUTH_CANCEL_DATE = p_auth_cancel_date,
3481 DEBIT_AUTH_METHOD = p_debit_auth_method,
3482 PRE_NOTIFICATION_REQUIRED_FLAG = p_pre_notif_flag,
3483 CREDITOR_LEGAL_ENTITY_ID = p_creditor_id,
3484 CREDITOR_LE_NAME = p_creditor_name,
3485 DEBIT_AUTH_BEGIN = p_debit_auth_begin,
3486 last_updated_by = fnd_global.user_id,
3487 last_update_date = SYSDATE ,
3488 last_update_login = fnd_global.user_id,
3489 object_version_number = object_version_number+1,
3490 CUST_ADDR_ID = p_cust_addr_id,
3491 DEBIT_AUTH_FLAG = p_debit_auth_flag ,DEBIT_AUTH_REFERENCE = p_debit_auth_ref,
3492 CUST_IDENTIFICATION_CODE = p_cust_id_code,
3493 CREDITOR_IDENTIFIER = p_creditor_identifer,DEBIT_AUTH_END = p_debit_auth_end,
3494 PAYMENT_TYPE_CODE = p_payment_code
3495 WHERE
3496 DEBIT_AUTHORIZATION_ID = p_debit_auth_id;
3497 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3498 print_debuginfo('Mandate has been updated:',
3499 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3500 END IF;
3501 END IF;
3502 COMMIT;
3503 x_result:=1;
3504 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3505 print_debuginfo('x_result:' || x_result,
3506 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3507 END IF;
3508 EXCEPTION
3509 WHEN OTHERS THEN
3510 x_result:=0;
3511 print_debuginfo('Exception occured while updating the mandate:' ||
3512 sqlerrm,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3513
3514 print_debuginfo('End:',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
3515 END;
3516
3517 END IBY_FNDCPT_SETUP_PUB;