1 Package body IBE_PAYMENT_INT_PVT as
2 /* $Header: IBEVPINB.pls 120.16 2005/12/15 06:03:01 khiremat noship $ */
3 -- Start of Comments
4 -- Package name : IBE_Quote_Checkout_Pvt
5 -- Purpose :
6 -- NOTE :
7
8 -- End of Comments
9
10
11 l_true VARCHAR2(1) := FND_API.G_TRUE;
12 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBE_PAYMENT_INT_PVT';
13 G_FILE_NAME CONSTANT VARCHAR2(12) := 'IBEVPMTB.pls';
14 l_debugon VARCHAR2(1) := IBE_UTIL.G_DEBUGON;
15
16 procedure save_credit_card
17 (p_api_version IN Number
18 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
19 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
20 ,p_operation_code IN VARCHAR2
21 ,p_credit_card_id IN NUMBER
22 ,p_assignment_id IN NUMBER
23 ,p_currency_code IN VARCHAR2
24 ,p_credit_card_num IN VARCHAR2
25 ,p_card_holder_name IN VARCHAR2
26 ,p_exp_date IN DATE
27 ,p_credit_card_type_code IN VARCHAR2
28 ,p_party_id IN NUMBER
29 ,p_cust_id IN NUMBER
30 ,p_statement_address_id IN NUMBER := FND_API.G_MISS_NUM
31 ,x_credit_card_id OUT NOCOPY NUMBER
32 ,x_assignment_id OUT NOCOPY NUMBER
33 ,x_return_status OUT NOCOPY VARCHAR2
34 ,x_msg_count OUT NOCOPY NUMBER
35 ,x_msg_data OUT NOCOPY VARCHAR2 ) is
36
37
38 l_api_name CONSTANT VARCHAR2(30) := 'save_credit_card';
39 l_api_version CONSTANT NUMBER := 1.0;
40 l_trxn_date DATE := sysdate;
41 l_site_use_id NUMBER := NULL;
42 l_credit_card_id NUMBER;
43 new_credit_card_id NUMBER;
44 l_username VARCHAR2(100);
45 l_bank_account_uses_id NUMBER;
46 lx_response IBY_FNDCPT_COMMON_PUB.Result_Rec_Type;
47 l_payer IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
48 l_card_instrument IBY_FNDCPT_SETUP_PUB.CreditCard_Rec_Type;
49 l_PmtInstrument IBY_FNDCPT_SETUP_PUB.PmtInstrument_rec_type;
50 l_assignment_attr IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
51 lx_assign_id NUMBER;
52 l_order_of_preference NUMBER;
53 l_primary_card_present NUMBER;
54 l_location_party_id NUMBER;
55 l_location_id NUMBER;
56 l_location HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
57 l_party_site HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
58 lx_msg_data VARCHAR2(2100);
59 l_billing_address_id NUMBER;
60 l_oneclick_id NUMBER := FND_API.G_MISS_NUM;
61 l_enabled_flag VARCHAR2(1) := 'N';
62 l_oneclick_enabled VARCHAR2(1) := NVL(FND_PROFILE.VALUE('IBE_USE_ONE_CLICK'),'Y');
63
64 cursor c_check_address_owner(c_party_site_id NUMBER) is
65 select location_id, party_id
66 from hz_party_sites
67 where party_site_id = c_party_site_id;
68 Cursor c_get_oneclick_settings(c_party_id IN NUMBER,
69 c_cust_id IN NUMBER) is
70
71 select ord_oneclick_id, enabled_flag
72 from ibe_ord_oneclick
73 where party_id = c_party_id and cust_account_id = c_cust_id;
74
75 rec_check_address_owner c_check_address_owner%rowtype;
76
77
78 BEGIN
79 --IBE_UTIL.enable_debug();
80 IF (l_debugon = l_true) THEN
81 IBE_UTIL.debug('enter IBE_PAYMENT_INT_PVT.save_credit_card');
82 END IF;
83 -- standard start of API savepoint
84 SAVEPOINT save_credit_card;
85
86 IF NOT FND_API.compatible_api_call(l_api_version,
87 p_api_version,
88 l_api_name,
89 G_PKG_NAME)
90 THEN
91 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
92 END IF;
93 -- initialize message list if p_init_msg_list is set to TRUE.
94 IF FND_API.to_boolean(p_init_msg_list) THEN
95 FND_MSG_PUB.initialize;
96 END IF;
97 -- Initialize API return status to success
98 x_return_status := FND_API.G_RET_STS_SUCCESS;
99
100 IF (l_debugon = l_true) THEN
101 IBE_UTIL.debug('save_credit_card : Input parameters' );
102 IBE_UTIL.debug('save_credit_card : p_operation_code '||p_operation_code );
103 IBE_UTIL.debug('save_credit_card : p_credit_card_id '||p_credit_card_id );
104 IBE_UTIL.debug('save_credit_card : p_assignment_id '||p_assignment_id );
105 IBE_UTIL.debug('save_credit_card : p_currency_code '||p_currency_code );
106 IBE_UTIL.debug('save_credit_card : p_card_holder_name '||p_card_holder_name );
107 IBE_UTIL.debug('save_credit_card : p_exp_date '||p_exp_date );
108 IBE_UTIL.debug('save_credit_card : p_credit_card_type_code '||p_credit_card_type_code );
109 IBE_UTIL.debug('save_credit_card : p_party_id '||p_party_id );
110 IBE_UTIL.debug('save_credit_card : p_cust_id '||p_cust_id );
111 IBE_UTIL.debug('save_credit_card : p_statement_address_id '||p_statement_address_id );
112
113 END IF;
114
115 IF (l_debugon = l_true) THEN
116 IBE_UTIL.debug('call process_credit_card - setup recStructs');
117 END IF;
118
119 ---------- calling IBY api: START ----------------------------------------------
120 -- *. Need to make sure that IBY takes in Gmiss values
121 -- 1. CC Record
122 if (p_operation_code = 'CREATE') then
123 l_card_instrument.card_id := NULL;
124
125 else
126 l_card_instrument.card_id := p_credit_card_id;
127
128 /*CreditCard_rec_type IS RECORD
129 (
130 Card_Id NUMBER,
131 Owner_Id NUMBER,
132 Card_Holder_Name VARCHAR2(80),
133 Billing_Address_Id NUMBER,
134 Billing_Postal_Code VARCHAR2(50),
135 Billing_Address_Territory VARCHAR2(2),
136 Card_Number VARCHAR2(30),
137 Expiration_Date DATE,
138 Instrument_Type VARCHAR2(30),
139 PurchaseCard_Flag VARCHAR2(1),
140 PurchaseCard_SubType VARCHAR2(30),
141 Card_Issuer VARCHAR2(30),
142 FI_Name VARCHAR2(80),
143 Single_Use_Flag VARCHAR2(1),
144 Info_Only_Flag VARCHAR2(1),
145 Card_Purpose VARCHAR2(30),
146 Card_Description VARCHAR2(240),
147 Active_Flag VARCHAR2(1),
148 Inactive_Date DATE
149 );*/
150 end if;
151
152 l_card_instrument.owner_id := p_party_id;
153
154 FOR rec_check_address_owner in c_check_address_owner(p_statement_address_id) LOOP
155 l_location_id := rec_check_address_owner.location_id;
156 l_location_party_id := rec_check_address_owner.party_id;
157 EXIT when c_check_address_owner%NOTFOUND;
158 END LOOP;
159
160 IF (l_debugon = l_true) THEN
161 IBE_UTIL.debug('Save_Credit_card: l_location_id from cursor '||l_location_id);
162 IBE_UTIL.debug('Save_Credit_card:l_location_party_id from cursor '||l_location_party_id);
163 END IF;
164
165 IF (p_party_id <> l_location_party_id) THEN
166
167 IF (l_debugon = l_true) THEN
168 IBE_UTIL.debug('Save_Credit_card: Input party id and location party id do not match');
169 END IF;
170
171 l_location.location_id := l_location_id;
172 l_location.address_effective_date := sysdate;
173
174 l_party_site.party_id := p_party_id;
175 l_party_site.location_id := l_location_id;
176 l_party_site.status := 'A';
177 l_party_site.created_by_module := 'USER PROFILE';
178
179 IF (l_debugon = l_true) THEN
180 IBE_UTIL.debug('Save_Credit_card: Ready to call IBE_ADDRESS_V2PVT.create_address');
181 END IF;
182
183
184 /*IBE_ADDRESS_V2PVT.create_address(
185 p_api_version => 1.0
186 ,p_location => l_location
187 ,p_party_site => l_party_site
188 ,x_return_status => x_return_status
189 ,x_msg_count => x_msg_count
190 ,x_msg_data => x_msg_data
191 ,x_location_id => l_location_id
192 ,x_party_site_id => l_card_instrument.billing_address_id);*/
193
194 IBE_ADDRESS_V2PVT.copy_party_site (
195 p_api_version => 1.0
196 ,p_init_msg_list => FND_API.G_FALSE
197 ,p_commit => FND_API.G_FALSE
198 ,p_party_site => l_party_site
199 ,p_location => l_location
200 ,x_party_site_id => l_billing_address_id
201 ,x_return_status => x_return_status
202 ,x_msg_count => x_msg_count
203 ,x_msg_data => x_msg_data);
204
205 IF (l_debugon = l_true) THEN
206 IBE_UTIL.debug('Save_Credit_card: Done calling IBE_ADDRESS_V2PVT.create_address: x_return_status '||x_return_status);
207 END IF;
208
209 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
210 RAISE FND_API.G_EXC_ERROR;
211 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
213 END IF;
214
215
216 IF (l_debugon = l_true) THEN
217 IBE_UTIL.debug('Save_Credit_card: Done calling IBE_ADDRESS_V2PVT.create_address: No error');
218 IBE_UTIL.debug('Save_Credit_card: Done calling IBE_ADDRESS_V2PVT.create_address: new billing_address_id '||l_billing_address_id);
219 END IF;
220
221 l_card_instrument.billing_address_id := l_billing_address_id;
222
223 ELSE
224
225 l_card_instrument.billing_address_id := p_statement_address_id;
226 IF (l_debugon = l_true) THEN
227 IBE_UTIL.debug('Save_Credit_card: Party ids match');
228 END IF;
229
230 END IF;
231
232 l_card_instrument.card_number := p_credit_card_num;
233 l_card_instrument.expiration_date := p_exp_date;
234 l_card_instrument.instrument_type := 'CREDITCARD';
235 l_card_instrument.purchasecard_subtype := NULL;
236 l_card_instrument.card_issuer := p_credit_card_type_code;
237 l_card_instrument.Card_Holder_Name := p_card_holder_name;
238 l_card_instrument.single_use_flag := 'N';
239 --l_card_instrument.info_only_flag := <OPTIONAL>
240 --l_card_instrument.card_purpose := <OPTIONAL>
241 --l_card_instrument.card_description := <OPTIONAL>
242
243
244 if (p_operation_code = 'UPDATE') then
245 l_card_instrument.expiration_date := p_exp_date;
246 end if;
247
248 if (p_operation_code = 'DELETE') then
249 l_card_instrument.inactive_date := SYSDATE;
250 end if;
251
252 -- 2. Payers
253 l_payer.Payment_Function := 'CUSTOMER_PAYMENT';
254 l_payer.Party_Id := p_party_id;
255 --l_payer.Org_Type := <OPTIONAL>
256 --l_payer.Org_Id := <OPTIONAL>
257 --l_payer.Cust_Account_Id := <OPTIONAL>
258 --l_payer.Account_Site_Id := <OPTIONAL>
259
260 -- 3. Pmt Instruments
261 if (p_operation_code = 'CREATE' or p_operation_code = 'CREATE_AND_SET_PRIMARY') then
262 l_PmtInstrument.Instrument_Type :='CREDITCARD';
263 l_PmtInstrument.Instrument_Id := null;
264 end if;
265
266 -- 4. CC Assignment
267 if (p_operation_code = 'CREATE' or p_operation_code = 'CREATE_AND_SET_PRIMARY') then
268 l_assignment_attr.Assignment_Id := NULL;
269 l_assignment_attr.Instrument := l_PmtInstrument;
270 l_assignment_attr.Start_Date := sysdate;
271
272
273 select count(*) into l_primary_card_present
274 from IBY_FNDCPT_PAYER_ASSGN_INSTR_V
275 where party_id = p_party_id
276 and order_of_preference = 1
277 and cust_account_id is null
278 and org_id is null
279 and acct_site_use_id is null
280 and instrument_type = 'CREDITCARD'
281 and payment_function = 'CUSTOMER_PAYMENT'
282 and card_number is not null;
283
284 IF (l_primary_card_present >= 1) THEN
285
286 select nvl(max(order_of_preference),0)+1 into l_order_of_preference
287 from IBY_FNDCPT_PAYER_ASSGN_INSTR_V
288 where party_id = p_party_id
289 and cust_account_id is null
290 and org_id is null
291 and acct_site_use_id is null
292 and instrument_type = 'CREDITCARD'
293 and payment_function = 'CUSTOMER_PAYMENT'
294 and card_number is not null;
295
296 l_assignment_attr.Priority := l_order_of_preference;
297 ELSE
298 l_assignment_attr.Priority := 1;
299
300 END IF;
301
302
303 /* select nvl(max(order_of_preference),0)+1 into l_order_of_preference
304 from IBY_FNDCPT_PAYER_ASSGN_INSTR_V
305 where party_id = p_party_id;
306
307 l_assignment_attr.Priority := l_order_of_preference;*/
308
309 if ( p_operation_code = 'CREATE_AND_SET_PRIMARY') then
310 l_assignment_attr.Priority := 1;
311 end if;
312
313 else
314 l_assignment_attr.Assignment_Id := p_assignment_id;
315 end if;
316
317 if (p_operation_code = 'SETPRIMARY') then
318 l_assignment_attr.Priority := 1;
319 l_assignment_attr.assignment_id := p_assignment_id;
320
321 IF (l_debugon = l_true) THEN
322 IBE_UTIL.debug('SaveCC: Set primary opration: l_assignment_attr.assignment_id '||l_assignment_attr.assignment_id);
323 IBE_UTIL.debug('call IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment - before calling api');
324 END IF;
325
326
327 IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment (
328 p_api_version => p_api_version
329 ,p_init_msg_list => p_init_msg_list
330 ,p_commit => p_commit
331 ,x_return_status => x_return_status
332 ,x_msg_count => x_msg_count
333 ,x_msg_data => x_msg_data
334 ,p_payer => l_payer
335 ,p_assignment_attribs => l_assignment_attr
336 ,x_assign_id => lx_assign_id
337 ,x_response => lx_response );
338
339 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
340 RAISE FND_API.G_EXC_ERROR;
341 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
343 END IF;
344
345 IF (l_debugon = l_true) THEN
346 IBE_UTIL.debug('call IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment - after calling api');
347 END IF;
348
349 end if;
350
351 IF (l_debugon = l_true) THEN
352 IBE_UTIL.debug('call process_credit_card - before calling api');
353 END IF;
354
355 if (p_operation_code = 'CREATE_AND_SET_PRIMARY' or p_operation_code = 'CREATE' ) then
356
357 IBY_FNDCPT_SETUP_PUB.process_credit_card (
358 p_api_version => p_api_version
359 ,p_init_msg_list => p_init_msg_list
360 ,p_commit => p_commit
361 ,x_return_status => x_return_status
362 ,x_msg_count => x_msg_count
363 ,x_msg_data => x_msg_data
364 ,p_payer => l_payer
365 ,p_credit_card => l_card_instrument
366 ,p_assignment_attribs => l_assignment_attr
367 ,x_assign_id => lx_assign_id
368 ,x_response => lx_response );
369
370 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
371 IF (l_debugon = l_true) THEN
372 IBE_UTIL.debug('call process_credit_card - Expected error');
373 IBE_UTIL.debug('call process_credit_card - Expected error: '||lx_response.Result_Code);
374 END IF;
375
376 RAISE FND_API.G_EXC_ERROR;
377 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
378 IF (l_debugon = l_true) THEN
379 IBE_UTIL.debug('call process_credit_card - UnExpected error');
380 END IF;
381 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
382 END IF;
383 x_assignment_id := lx_assign_id;
384 end if;
385 if (p_operation_code = 'UPDATE' or p_operation_code = 'DELETE') then
386
387 IBY_FNDCPT_SETUP_PUB.Update_Card(
388 p_api_version => p_api_version
389 ,p_init_msg_list => p_init_msg_list
390 ,p_commit => p_commit
391 ,x_return_status => x_return_status
392 ,x_msg_count => x_msg_count
393 ,x_msg_data => x_msg_data
394 ,p_card_instrument => l_card_instrument
395 ,x_response => lx_response);
396
397
398 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
399 RAISE FND_API.G_EXC_ERROR;
400 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
401 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
402 END IF;
403 -- We need to call the Experss Checkout api to check if the Express Chkout CC is the same
404 -- as the one deleted. If so, disable the Express Chkout
405
406 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
407 ibe_util.debug('p_operation_code :'||p_operation_code);
408 ibe_util.debug('l_oneclick_enabled :'||l_oneclick_enabled);
409 ibe_util.debug('p_party_id :'||p_party_id);
410 ibe_util.debug('p_cust_id :'||p_cust_id);
411 END IF;
412
413 if l_oneclick_enabled = 'Y' and p_operation_code = 'DELETE' then
414
415 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
416 ibe_util.debug('Entering the loop :'||p_operation_code);
417 END IF;
418
419 OPEN c_get_oneclick_settings(p_party_id, p_cust_id);
420 FETCH c_get_oneclick_settings
421 INTO
422 l_oneclick_id,
423 l_enabled_flag;
424
425 if c_get_oneclick_settings%NOTFOUND then
426 l_oneclick_id := FND_API.g_miss_num;
427 end if;
428 CLOSE c_get_oneclick_settings;
429
430 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
431 ibe_util.debug('l_oneclick_id :'||l_oneclick_id);
432 ibe_util.debug('l_enabled_flag :'||l_enabled_flag);
433 END IF;
434
435 IF l_oneclick_id <> FND_API.g_miss_num and l_enabled_flag = 'Y' then
436 IF (l_debugon = l_true) THEN
437 IBE_UTIL.debug('call process_credit_card - Going to call ibe_ord_oneclick_pvt.Update_Settings()');
438 IBE_UTIL.debug('call process_credit_card - p_party_id ' ||p_party_id);
439 IBE_UTIL.debug('call process_credit_card - p_cust_id '||p_cust_id);
440 IBE_UTIL.debug('call process_credit_card - p_assignment_id'||l_assignment_attr.Assignment_Id);
441 END IF;
442
443 ibe_ord_oneclick_pvt.Update_Settings(
444 p_api_version => p_api_version
445 ,p_init_msg_list => p_init_msg_list
446 ,p_commit => p_commit
447 ,x_return_status => x_return_status
448 ,x_msg_count => x_msg_count
449 ,x_msg_data => x_msg_data
450 ,p_party_id => p_party_id
451 ,p_acct_id => p_cust_id
452 ,p_assignment_id => l_assignment_attr.Assignment_Id);
453
454 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
455 RAISE FND_API.G_EXC_ERROR;
456 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
458 END IF;
459 END IF;
460 END IF; --l_oneclick_enabled
461 end if;
462
463 IF FND_API.to_boolean(p_commit) THEN
464 commit;
465 END IF;
466
467 -- standard call to get message count and if count is 1, get message info
468 FND_MSG_PUB.count_and_get(
469 p_encoded => FND_API.G_FALSE,
470 p_count => x_msg_count,
471 p_data => x_msg_data );
472
473 EXCEPTION
474
475 WHEN FND_API.G_EXC_ERROR THEN
476 -- IBE_UTIL.enable_debug();
477 ROLLBACK TO save_credit_card;
478 IF (l_debugon = l_true) THEN
479 IBE_UTIL.debug('G_EXC_ERROR exception');
480 END IF;
481
482 x_return_status := FND_API.G_RET_STS_ERROR;
483 FND_MSG_PUB.Add;
484 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
485 p_data => x_msg_data,
486 p_encoded => 'F');
487
488 for k in 1..x_msg_count loop
489 lx_msg_data := fnd_msg_pub.get( p_msg_index => k,
490 p_encoded => 'F');
491 IF (l_debugon = l_true) THEN
492 IBE_UTIL.debug('Error msg: '||substr(lx_msg_data,1,240));
493 END IF;
494 end loop;
495
496 IF (l_debugon = l_true) THEN
497 IBE_UTIL.debug('G_EXC_ERROR exception');
498 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
499 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
500 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
501 IBE_UTIL.debug('error text : '|| SQLERRM);
502 END IF;
503 --IBE_UTIL.disable_debug();
504 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
505 --IBE_UTIL.enable_debug();
506 ROLLBACK TO save_credit_card;
507
508 IF (l_debugon = l_true) THEN
509 IBE_UTIL.debug('G_UNEXC_ERROR exception');
510 END IF;
511 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
512 FND_MSG_PUB.Add;
513 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
514 p_data => x_msg_data,
515 p_encoded => 'F');
516 IF (l_debugon = l_true) THEN
517 IBE_UTIL.debug('G_EXC_UNEXPECTED_ERROR exception');
518 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
519 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
520 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
521 IBE_UTIL.debug('error text : '|| SQLERRM);
522 END IF;
523 --IBE_UTIL.disable_debug();
524 WHEN OTHERS THEN
525 --IBE_UTIL.enable_debug();
526 ROLLBACK TO save_credit_card;
527 IF (l_debugon = l_true) THEN
528 IBE_UTIL.debug('Others exception');
529 END IF;
530
531 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
532 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
533 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
534 FND_MESSAGE.Set_Token('REASON', SQLERRM);
535 FND_MSG_PUB.Add;
536 FND_MSG_PUB.ADD;
537 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
538 p_data => x_msg_data,
539 p_encoded => 'F');
540 --bug 2617273
541 --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
542 x_return_status := FND_API.G_RET_STS_ERROR;
543 IF (l_debugon = l_true) THEN
544 IBE_UTIL.debug('outside -20001 error code : '|| to_char(SQLCODE));
545 IBE_UTIL.debug('outside -20001 error text : '|| SQLERRM);
546 END IF;
547 IF (l_debugon = l_true) THEN
548 IBE_UTIL.debug('OTHER exception');
549 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
550 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
551 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
552 IBE_UTIL.debug('error text : '|| SQLERRM);
553 END IF;
554 --IBE_UTIL.disable_debug();
555
556 END save_credit_card;
557
558 PROCEDURE check_Payment_channel_setups(
559 p_api_version IN Number
560 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
561 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
562 ,x_cvv2_setup OUT NOCOPY VARCHAR2
563 ,x_statement_address_setup OUT NOCOPY VARCHAR2
564 ,x_return_status OUT NOCOPY VARCHAR2
565 ,x_msg_count OUT NOCOPY NUMBER
566 ,x_msg_data OUT NOCOPY VARCHAR2 ) is
567
568 lx_channel_attrib_uses IBY_FNDCPT_SETUP_PUB.PmtChannel_AttribUses_rec_type;
569 l_api_name CONSTANT VARCHAR2(30) := 'save_credit_card';
570 l_api_version CONSTANT NUMBER := 1.0;
571 l_return_status VARCHAR2(2000);
572 l_cvv2_status VARCHAR2(1);
573 l_msg_count NUMBER(10);
574 l_msg_data VARCHAR2(2000);
575 l_result_rec_type IBY_FNDCPT_COMMON_PUB.Result_rec_type;
576 BEGIN
577 IF (l_debugon = l_true) THEN
578 IBE_UTIL.debug('check_Payment_channel_setups: Begin ');
579 END IF;
580
581 IBY_FNDCPT_SETUP_PUB.Get_Payment_Channel_Attribs(
582 p_api_version => l_api_version,
583 x_return_status => l_return_status,
584 x_msg_count => l_msg_count,
585 x_msg_data => l_msg_data,
586 p_channel_code => 'CREDIT_CARD',
587 x_channel_attrib_uses => lx_channel_attrib_uses,
588 x_response => l_result_rec_type);
589
590 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
591 RAISE FND_API.G_EXC_ERROR;
592 END IF;
593
594 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
595 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
596 END IF;
597 IF (l_debugon = l_true) THEN
598 IBE_UTIL.debug('check_Payment_channel_setups:lx_channel_attrib_uses.Instr_SecCode_Use '||lx_channel_attrib_uses.Instr_SecCode_Use);
599 END IF;
600
601 IF (l_debugon = l_true) THEN
602 IBE_UTIL.debug('check_Payment_channel_setups:lx_channel_attrib_uses.Instr_Billing_Address '||lx_channel_attrib_uses.Instr_Billing_Address);
603 END IF;
604
605
606 IF(lx_channel_attrib_uses.Instr_SecCode_Use = 'REQUIRED') then
607 x_cvv2_setup := FND_API.G_TRUE;
608
609 ELSIF(lx_channel_attrib_uses.Instr_SecCode_Use = 'OPTIONAL') then
610 x_cvv2_setup := FND_API.G_FALSE;
611 END IF;
612
613 IF (l_debugon = l_true) THEN
614 IBE_UTIL.debug('check_Payment_channel_setups: lx_channel_attrib_uses.Instr_SecCode_Use '||lx_channel_attrib_uses.Instr_SecCode_Use);
615 IBE_UTIL.debug('check_Payment_channel_setups: x_cvv2_status '||x_cvv2_setup);
616 END IF;
617
618 IF(lx_channel_attrib_uses.Instr_Billing_Address = 'REQUIRED') then
619 x_statement_address_setup := FND_API.G_TRUE;
620 ELSIF(lx_channel_attrib_uses.Instr_Billing_Address = 'OPTIONAL') then
621 x_statement_address_setup := FND_API.G_FALSE;
622 END IF;
623
624 IF (l_debugon = l_true) THEN
625 IBE_UTIL.debug('check_Payment_channel_setups: lx_channel_attrib_uses.Instr_Billing_Address '||lx_channel_attrib_uses.Instr_Billing_Address);
626 IBE_UTIL.debug('check_Payment_channel_setups: x_statement_address_setup '||x_statement_address_setup);
627 END IF;
628
629 EXCEPTION
630 WHEN FND_API.G_EXC_ERROR THEN
631 IF (l_debugon = l_true) THEN
632 IBE_UTIL.debug('G_EXC_ERROR exception');
633 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
634 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
635 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
636 IBE_UTIL.debug('error text : '|| SQLERRM);
637 END IF;
638 --IBE_UTIL.disable_debug();
639 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
640 IF (l_debugon = l_true) THEN
641 IBE_UTIL.debug('G_EXC_UNEXPECTED_ERROR exception');
642 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
643 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
644 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
645 IBE_UTIL.debug('error text : '|| SQLERRM);
646 END IF;
647 --IBE_UTIL.disable_debug();
648 WHEN OTHERS THEN
649 IF (l_debugon = l_true) THEN
650 IBE_UTIL.debug('outside -20001 error code : '|| to_char(SQLCODE));
651 IBE_UTIL.debug('outside -20001 error text : '|| SQLERRM);
652 END IF;
653 IF (l_debugon = l_true) THEN
654 IBE_UTIL.debug('OTHER exception');
655 IBE_UTIL.debug('x_msg_count ' || to_char(x_msg_count));
656 IBE_UTIL.debug('x_msg_data ' || x_msg_data);
657 IBE_UTIL.debug('error code : '|| to_char(SQLCODE));
658 IBE_UTIL.debug('error text : '|| SQLERRM);
659 END IF;
660 END check_Payment_channel_setups;
661
662 PROCEDURE print_debug_log(p_debug_str IN VARCHAR2) IS
663
664 BEGIN
665
666 FND_FILE.PUT_LINE(FND_FILE.LOG,p_debug_str);
667 IBE_UTIL.debug(p_debug_str);
668
669 END print_Debug_Log;
670
671 PROCEDURE print_output(p_message IN VARCHAR2) IS
672 BEGIN
673 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_message);
674 END print_Output;
675
676
677 PROCEDURE mig_exp_checkout_pay_setup(errbuf OUT NOCOPY VARCHAR2,
678 retcode OUT NOCOPY NUMBER,
679 p_debug_flag IN VARCHAR2,
680 p_commit_size IN NUMBER)
681
682
683 IS
684 l_status VARCHAR2(1);
685 l_msg_data VARCHAR2(2000);
686 l_counter NUMBER :=0;
687 l_iby_debug VARCHAR2(1);
688 l_assignment_id NUMBER(15);
689 l_instrument_id NUMBER(15);
690 l_instr_assignment_id NUMBER( 15);
691 l_oneclick_payment_id NUMBER(15);
692
693 CURSOR c_oneclick_data is
694 SELECT payment_id, party_id, cust_account_id, org_id
695 FROM ibe_ord_oneclick_all;
696
697 CURSOR c_assignment_data(c_pmt_id NUMBER,
698 c_party_id NUMBER,
699 c_org_id NUMBER,
700 c_cust_accnt_id NUMBER) is
701
702 SELECT instr_assignment_id
703 FROM IBY_FNDCPT_PAYER_ASSGN_INSTR_V
704 WHERE instr_assignment_id = c_pmt_id
705 AND party_id = c_party_id
706 AND org_id = c_org_id
707 AND cust_account_id = c_cust_accnt_id
708 AND acct_site_use_id IS NULL
709 AND instrument_type = 'CREDITCARD'
710 AND payment_function = 'CUSTOMER_PAYMENT';
711
712 CURSOR c_instrument_data(c_pmt_id NUMBER ) is
713 SELECT instrument_id
714 FROM IBY_UPG_INSTRUMENTS
715 WHERE bank_account_id = c_pmt_id
716 and rownum <2 ;
717
718 CURSOR c_assignment_for_instrument(c_instr_id NUMBER, c_party_id NUMBER, c_org_id NUMBER, c_cust_accnt_id NUMBER ) is
719 SELECT instr_assignment_id
720 FROM IBY_FNDCPT_PAYER_ASSGN_INSTR_V
721 WHERE instrument_id = c_instr_id
722 AND party_id = c_party_id
723 AND cust_account_id = c_cust_accnt_id
724 AND org_id = c_org_id
725 AND acct_site_use_id IS NULL;
726
727 rec_oneclick_data c_oneclick_data%rowtype;
728 rec_assignment_data c_assignment_data%rowtype;
729 rec_instrument_data c_instrument_data%rowtype;
730 rec_assignment_for_instrument c_assignment_for_instrument%rowtype;
731
732 BEGIN
733
734 /*IF p_debug_flag = 'Y' THEN
735 IBE_PAYMENT_INT_PVT.g_debug := p_debug_flag;
736
737 END IF;*/
738 l_iby_debug := p_debug_flag;
739 -- logging statements
740 IF l_iby_debug = 'T' THEN
741 print_debug_log('Parameter list:');
742 print_debug_log(' p_commit_size = '||p_commit_size);
743 print_debug_log(' start_time = ' ||to_char(sysdate,'DD-MON-RRRR HH24:MI:SS'));
744 END IF;
745 --l_msg_data populated with different event points
746
747 -- IBE_ORD_ONECLICK_ALL table currently stores the express checkout preferences. The preferred credit card is
748 -- stored in the column payment_id. Going forward this column will store the assignment_id.
749 --1. Cursor query to retrive the records in the one click table
750
751 FOR rec_oneclick_data in c_oneclick_data LOOP
752
753 --2. Loop on the cursor query above.
754 l_oneclick_payment_id := rec_oneclick_data.payment_id;
755 IF l_iby_debug = 't' THEN
756 print_debug_log('Dealing with dataset party_id: '||rec_oneclick_data.party_id);
757 print_debug_log('Dealing with dataset org_id: '||rec_oneclick_data.org_id);
758 print_debug_log('Dealing with dataset cust_account_id: '||rec_oneclick_data.cust_account_id);
759 END IF;
760
761 --Program should be re-runnable
762
763 --a) Check if there exists a record in the assignment table with the same value of payment_id
764 --for the above party, account and org. If so, then this is already migrated data
765 --and we should skip it
766 IF l_iby_debug = 'T' THEN
767 print_debug_log('Trying to see if we have an assignment already for the record in oneclick_all');
768 END IF;
769 FOR rec_assignment_data in c_assignment_data(rec_oneclick_data.payment_id,
770 rec_oneclick_data.party_id,
771 rec_oneclick_data.org_id,
772 rec_oneclick_data.cust_account_id) LOOP
773 l_assignment_id := rec_assignment_data.instr_assignment_id;
774 IF l_iby_debug = 'T' THEN
775 print_debug_log('Assignment already exists for the record in oneclick_all.Assignment_id is '||l_assignment_id);
776 END IF;
777 EXIT when c_assignment_data%NOTFOUND;
778 END LOOP;
779 -- if there is a record returned above, then skip the loop
780 --Otherwise
781 --b) we have to also look to get the corresponding (new) credit card id from the IBY schema
782 -- from the IBY Mapping table.
783
784 IF (l_assignment_id is null) THEN
785
786 IF l_iby_debug = 'T' THEN
787 print_debug_log('No assignment present for the record in oneclick_all');
788 END IF;
789
790 FOR rec_instrument_data in c_instrument_data(rec_oneclick_data.payment_id) LOOP
791 l_instrument_id := rec_instrument_data.instrument_id;
792 EXIT when c_instrument_data%NOTFOUND;
793 END LOOP;
794
795
796 --c) Next, we look at IBY_FNDCPT_PAYER_ASSGN_INSTR_V to get the corresponding assignment_id.
797 FOR rec_assignment_for_instrument in c_assignment_for_instrument(l_instrument_id,
798 rec_oneclick_data.party_id,
799 rec_oneclick_data.cust_account_id,
800 rec_oneclick_data.org_id) LOOP
801 l_instr_assignment_id := rec_assignment_for_instrument.instr_assignment_id;
802 EXIT when c_assignment_for_instrument%NOTFOUND;
803 END LOOP;
804 -- if we don't get any assignment_id from the above query, we will log it in the concurrent
805 -- program log
806
807 --d) We will store assignment_id value for the Express Checkout Settings going forward because:
808 -- we eventually have to pass the assignment_id to the ASO api's to create the Express
809 -- Checkout cart and, using cc_id, it's difficult to derive the assignment_id because one
810 -- cc_id might have many rows in the IBY_pmt_instr_uses_all table
811 -- because it might be assigned to the party or party-acct or party-org combinations.
812
813 -- log old and new values before the update
814 IF l_iby_debug = 'T' THEN
815 print_debug_log('Before updating IBE_ORD_ONECLICK_ALL');
816 print_debug_log('Original payment id before update: '||l_oneclick_payment_id);
817 print_debug_log('New payment id(actually assignment_id) after update: '||l_instr_assignment_id);
818 END IF;
819
820 UPDATE IBE_ORD_ONECLICK_ALL
821 SET PAYMENT_ID = l_instr_assignment_id
822 WHERE party_id = rec_oneclick_data.party_id
823 AND cust_account_id = rec_oneclick_data.cust_account_id
824 AND org_id = rec_oneclick_data.org_id;
825
826 END IF;
827 EXIT when c_oneclick_data%NOTFOUND;
828
829 END LOOP; --close c_oneclick_data
830
831 --commit for every 'x' records specified by the parameter p_commit_size
832 l_counter := nvl(l_counter,0) + 1;
833 IF (mod(l_counter,nvl(p_commit_size, 2000)) = 0) THEN
834 COMMIT;
835 END IF;
836
837 --Output success message
838 --print_output('<Success Message>');
839
840 retcode := 0;
841 errbuf := 'SUCCESS';
842
843 EXCEPTION
844 WHEN OTHERS THEN
845 IF l_iby_debug = 'T' THEN
846 print_debug_log('Exception occured');
847 print_debug_log(l_msg_data||' '||SQLCODE||'-'||SQLERRM);
848 END IF;
849 print_output(l_msg_data||' '||SQLCODE||'-'||SQLERRM);
850 retcode := 2;
851 errbuf := l_msg_data||' '||SQLCODE||'-'||SQLERRM;
852 END mig_exp_checkout_pay_setup;
853
854
855 PROCEDURE migrate_primary_CC(errbuf OUT NOCOPY VARCHAR2,
856 retcode OUT NOCOPY NUMBER,
857 p_debug_flag IN VARCHAR2,
858 p_commit_size IN NUMBER)
859
860 IS
861
862 CURSOR c_get_users_w_primary_cc_set IS
863 SELECT user_name, preference_value
864 FROM fnd_user_preferences
865 WHERE module_name = 'IBE'
866 AND preference_name = 'PRIMARY_CARD';
867
868 cursor c_check_assignment_id (c_instrument_id NUMBER, c_party_id NUMBER) is
869 SELECT INSTR_ASSIGNMENT_ID, order_of_preference
870 FROM IBY_FNDCPT_PAYER_ASSGN_INSTR_V
871 WHERE instrument_id = c_instrument_id
872 AND party_id = c_party_id
873 AND org_id IS NULL
874 AND cust_account_id IS NULL
875 AND acct_site_use_id IS NULL;
876
877 cursor c_find_instr_payment_use(c_assignment_id NUMBER) is
878 Select INSTRUMENT_PAYMENT_USE_ID
879 from IBY_PMT_INSTR_USES_ALL
880 where INSTRUMENT_PAYMENT_USE_ID = c_assignment_id;
881
882 cursor c_check_ext_payer_id (c_party_id NUMBER) is
883 select EXT_PAYER_ID
884 from IBY_EXTERNAL_PAYERS_ALL
885 where party_id = c_party_id
886 AND org_id IS NULL
887 AND cust_account_id IS NULL
888 AND acct_site_use_id IS NULL;
889
890
891 cursor c_find_fnd_user (c_user_name VARCHAR2) is
892 SELECT customer_id, person_party_id
893 FROM fnd_user
894 WHERE user_name = c_user_name;
895
896
897 l_status VARCHAR2(1);
898 l_msg_data VARCHAR2(2000);
899 l_counter NUMBER :=0;
900 l_instrument_id NUMBER(15);
901 l_instrument_assignment_id NUMBER(15);
902 l_order_of_preference NUMBER(15);
903 l_customer_id NUMBER(15);
904 l_person_party_id NUMBER(15);
905 l_party_to_use NUMBER(15);
906 l_ext_payer_id NUMBER(15);
907 l_ext_payer_id_verify NUMBER(15);
908 l_instrument_payment_use_id NUMBER(15);
909
910 rec_get_users_w_primary_cc_set c_get_users_w_primary_cc_set%rowtype;
911 rec_check_assignment_id c_check_assignment_id%rowtype;
912 rec_find_instr_payment_use c_find_instr_payment_use%rowtype;
913 rec_check_ext_payer_id c_check_ext_payer_id%rowtype;
914 rec_find_fnd_user c_find_fnd_user%rowtype;
915 --Define the global variable g_debug VARCHAR2(1) := 'N'
916
917 BEGIN
918 /*IF p_debug_flag = 'Y' THEN
919 IBE_PAYMENT_INT_PVT.g_debug := p_debug_flag;
920 END IF;*/
921
922 -- logging statements
923 IF p_debug_flag = 'T' THEN
924 print_debug_log('Parameter list:');
925 print_debug_log(' p_commit_size = '||p_commit_size);
926 print_debug_log(' start_time = '||to_char(sysdate,'DD-MON-RRRR HH24:MI:SS'));
927 END IF;
928 --l_msg_data populated with different event points
929 --1. Cursor query to retrieve all the iStore users who have a primary credit card setting
930
931 --2. Loop: on the users
932 FOR rec_get_users_w_primary_cc_set in c_get_users_w_primary_cc_set LOOP
933
934 --a) Get the equivalent identifier for the credit card from the IBY mapping table
935 -- (IBY_UPG_INSTRUMENTS)
936 SELECT instrument_id into l_instrument_id
937 FROM IBY_UPG_INSTRUMENTS
938 WHERE bank_account_id = rec_get_users_w_primary_cc_set.preference_value
939 and rownum < 2;
940
941 IF p_debug_flag = 'T' THEN
942 print_debug_log('Instrument id obtained from Upg_Instruments : '||l_instrument_id);
943 END IF;
944
945
946 --b) Get the party_id corresponding to the fnd_user using the following query
947 FOR rec_find_fnd_user in c_find_fnd_user(rec_get_users_w_primary_cc_set.user_name) LOOP
948
949 l_customer_id := rec_find_fnd_user.customer_id;
950 l_person_party_id := rec_find_fnd_user.person_party_id;
951 EXIT WHEN c_find_fnd_user%NOTFOUND;
952 END LOOP;
953
954 IF p_debug_flag = 'T' THEN
955 print_debug_log('CUstomer Id and person party id from FND USER : '||l_customer_id ||','||l_person_party_id);
956 END IF;
957
958
959 -- If the customer_id does not have any value, use the person_party_id value instead.
960 IF (l_customer_id is null) THEN
961 l_party_to_use := l_person_party_id;
962 IF p_debug_flag = 'T' THEN
963 print_debug_log('customer_id does not have any value, using the person_party_id');
964 END IF;
965 ELSE
966 IF p_debug_flag = 'T' THEN
967 print_debug_log('customer_id has a value,l_party_to_use: '||l_customer_id);
968 END IF;
969 l_party_to_use := l_customer_id;
970 END IF;
971
972 --c) next, use the new cc_id and the partyId to find the corresponding row in the
973 -- IBY_PMT_INSTR_USES_ALL table
974
975 FOR rec_check_assignment_id in c_check_assignment_id(l_instrument_id, l_party_to_use) LOOP
976 l_instrument_assignment_id := rec_check_assignment_id.instr_assignment_id;
977 l_order_of_preference := rec_check_assignment_id.order_of_preference;
978 print_debug_log('l_instrument_assignment_id '||l_instrument_assignment_id);
979 print_debug_log('l_order_of_preference '||l_order_of_preference);
980 EXIT WHEN c_check_assignment_id%NOTFOUND;
981 END LOOP;
982
983 --i) if this row exists and the order_of_preference is 1, DO NOT call the update routine
984 -- program should be re-runnable
985 --ii) if this row exists and the order_of_preference is not set to 1, update
986 --log the old and new values for the order_of_preference and the assignment_id
987 IF( (l_instrument_assignment_id is not null) and (l_order_of_preference <> 1)) THEN
988
989 IF p_debug_flag = 'T' THEN
990 print_debug_log('Order of preference is not 1 for instr assignment id '||l_instrument_assignment_id);
991 END IF;
992
993 FOR rec_find_instr_payment_use in c_find_instr_payment_use(l_instrument_assignment_id) LOOP
994
995 l_instrument_payment_use_id := rec_find_instr_payment_use.instrument_payment_use_id;
996 EXIT WHEN c_find_instr_payment_use%NOTFOUND;
997 END LOOP;
998
999 IF(l_instrument_payment_use_id is not null) THEN
1000
1001 UPDATE IBY_PMT_INSTR_USES_ALL
1002 SET order_of_preference = 1
1003 WHERE INSTRUMENT_PAYMENT_USE_ID = l_INSTRUMENT_PAYMENT_USE_ID;
1004
1005 IF p_debug_flag = 'T' THEN
1006 print_debug_log('Updated IBY_PMT_INSTR_USES_ALL');
1007 print_debug_log(' Old value of Order_of_preference = '||l_order_of_preference);
1008 print_debug_log(' New value of Order_of_preference = 1');
1009 print_debug_log(' INSTRUMENT_PAYMENT_USE_ID Record updated: '||l_instrument_assignment_id);
1010 END IF;
1011
1012 UPDATE iby_pmt_instr_uses_all
1013 SET order_of_preference = order_of_preference + 1,
1014 last_updated_by = fnd_global.user_id,
1015 last_update_date = SYSDATE,
1016 last_update_login = fnd_global.login_id,
1017 object_version_number = object_version_number + 1
1018 WHERE instrument_payment_use_id IN
1019 (
1020 SELECT instrument_payment_use_id
1021 FROM iby_pmt_instr_uses_all
1022 WHERE (ext_pmt_party_id = l_party_to_use)
1023 AND (payment_flow = 'FUNDS_CAPTURE')
1024 START WITH order_of_preference = l_order_of_preference
1025 AND (ext_pmt_party_id = l_party_to_use)
1026 AND (payment_flow = 'FUNDS_CAPTURE')
1027 CONNECT BY order_of_preference = PRIOR (order_of_preference + 1)
1028 AND (ext_pmt_party_id = PRIOR ext_pmt_party_id)
1029 AND (payment_flow = PRIOR payment_flow) );
1030
1031 ELSE
1032
1033 /*--iii) if this row does not exist, create an assignment for the partyId of the user
1034 -- with order_of_preference = 1 by directly inserting into IBY assignment table:
1035 -- IBY_PMT_INSTR_USES_ALL. A record might also need to be created in
1036 -- IBY_EXTERNAL_PAYERS_ALL table so that the corresponding identifer could be
1037 -- substitued in ext_pmt_party_id column of the iby_pmt_instr_uses all table.*/
1038 IF p_debug_flag = 'T' THEN
1039 print_debug_log('Going for direct inserts into IBY_EXTERNAL_PAYERS_ALL');
1040 END IF;
1041
1042 FOR rec_check_ext_payer_id in c_check_ext_payer_id(l_party_to_use) LOOP
1043
1044 l_EXT_PAYER_ID := rec_check_ext_payer_id.EXT_PAYER_ID;
1045 EXIT WHEN c_check_ext_payer_id%NOTFOUND;
1046 END LOOP;
1047
1048 IF (l_EXT_PAYER_ID is NULL ) THEN
1049
1050
1051 select IBY_EXTERNAL_PAYERS_ALL_S.Nextval into l_ext_payer_id
1052 from dual;
1053
1054 INSERT INTO IBY_EXTERNAL_PAYERS_ALL(
1055 EXT_PAYER_ID ,
1056 PAYMENT_FUNCTION ,
1057 PARTY_ID ,
1058 CREATED_BY ,
1059 CREATION_DATE ,
1060 LAST_UPDATED_BY ,
1061 LAST_UPDATE_DATE ,
1062 LAST_UPDATE_LOGIN ,
1063 OBJECT_VERSION_NUMBER )
1064
1065 VALUES(
1066 l_ext_payer_id ,
1067 'CUSTOMER_PAYMENT' ,
1068 l_party_to_use ,
1069 fnd_global.USER_ID ,
1070 SYSDATE ,
1071 fnd_global.USER_ID ,
1072 SYSDATE ,
1073 fnd_global.USER_ID ,
1074 1 );
1075
1076 Select ext_payer_id into l_ext_payer_id_verify
1077 from IBY_EXTERNAL_PAYERS_ALL
1078 where ext_payer_id = l_ext_payer_id;
1079
1080 IF p_debug_flag = 'T' THEN
1081 print_debug_log('Successfully inserted into IBY_EXTERNAL_PAYERS_ALL');
1082 print_debug_log('Record ID inserted : '||l_ext_payer_id_verify);
1083 END IF;
1084
1085
1086
1087 -- if the above query returns any value, use that in the insert to IBY_PMT_INSTR_USES_ALL
1088 -- as mentioned in (iii) above.
1089 --log the new values inserted
1090
1091 IF(l_ext_payer_id_verify is not null) THEN
1092
1093 IF p_debug_flag = 'T' THEN
1094 print_debug_log('Doing a direct insert into IBY_PMT_INSTR_USES_ALL');
1095 END IF;
1096
1097 INSERT INTO IBY_PMT_INSTR_USES_ALL(
1098 INSTRUMENT_PAYMENT_USE_ID ,
1099 PAYMENT_FLOW ,
1100 EXT_PMT_PARTY_ID ,
1101 INSTRUMENT_TYPE ,
1102 INSTRUMENT_ID ,
1103 PAYMENT_FUNCTION ,
1104 ORDER_OF_PREFERENCE ,
1105 CREATED_BY ,
1106 CREATION_DATE ,
1107 LAST_UPDATED_BY ,
1108 LAST_UPDATE_DATE ,
1109 LAST_UPDATE_LOGIN ,
1110 OBJECT_VERSION_NUMBER ,
1111 START_DATE )
1112
1113 VALUES(
1114 l_instrument_assignment_id,
1115 'FUNDS_CAPTURE' ,
1116 l_party_to_use ,
1117 'CREDITCARD' ,
1118 l_instrument_id ,
1119 'CUSTOMER_PAYMENT' ,
1120 1 ,
1121 fnd_global.USER_ID ,
1122 SYSDATE ,
1123 fnd_global.USER_ID ,
1124 SYSDATE ,
1125 fnd_global.USER_ID ,
1126 1 ,
1127 SYSDATE );
1128
1129 END IF; --l_ext_payer_id_verify is not null
1130
1131 IF p_debug_flag = 'T' THEN
1132 print_debug_log('Inserted a record in IBY_PMT_INSTR_USES_ALL');
1133 print_debug_log('Inserted a record for l_instrument_assignment_use_id '||l_instrument_assignment_id);
1134 print_debug_log('Inserted a record for l_instrument_id '||l_instrument_id);
1135 END IF;
1136 END IF; -- l_EXT_PAYER_ID
1137
1138 END IF; --l_instrument_payment_use_id is not null
1139
1140 END IF; --l_instrument_assignment_id is not null) and (l_order_of_preference <> 1
1141 --commit for every 'x' records specified by the parameter p_commit_size
1142 l_counter := nvl(l_counter,0) + 1;
1143 IF (mod(l_counter,nvl(p_commit_size, 2000)) = 0) THEN
1144 COMMIT;
1145 END IF;
1146
1147 EXIT WHEN c_get_users_w_primary_cc_set%NOTFOUND;
1148 END LOOP;
1149
1150
1151 --Output success message
1152 print_debug_log('Done with all processing . Returning a success status ');
1153
1154 retcode := 0;
1155 --errbuf := 'SUCCESS';
1156
1157 EXCEPTION
1158 WHEN OTHERS THEN
1159 IF p_debug_flag = 'T' THEN
1160 print_debug_log('Exception occured');
1161 print_debug_log(l_msg_data||' '||SQLCODE||'-'||SQLERRM);
1162 END IF;
1163 print_output(l_msg_data||' '||SQLCODE||'-'||SQLERRM);
1164 retcode := 2;
1165 errbuf := l_msg_data||' '||SQLCODE||'-'||SQLERRM;
1166
1167 END migrate_primary_CC;
1168
1169 PROCEDURE migrate_ibe_cc_data(
1170 p_cut_off_date date
1171 ,errbuf OUT NOCOPY VARCHAR2
1172 ,retcode OUT NOCOPY NUMBER) is
1173
1174 BEGIN
1175
1176
1177 print_debug_log('IBE_PAYMENT_INT_PVT.Migrate_ibe_cc_data: Begin');
1178
1179 print_debug_log('IBE_PAYMENT_INT_PVT.Migrate_ibe_cc_data: Calling mig_exp_checkout_pay_setup');
1180
1181
1182 mig_exp_checkout_pay_setup
1183 (errbuf => errbuf
1184 ,retcode => retcode
1185 ,p_debug_flag => FND_API.G_TRUE
1186 ,p_commit_size => 2000);
1187
1188 print_debug_log('IBE_PAYMENT_INT_PVT.Migrate_ibe_cc_data: Calling migrate_primary_CC');
1189
1190 migrate_primary_CC
1191 (errbuf => errbuf
1192 ,retcode => retcode
1193 ,p_debug_flag => FND_API.G_TRUE
1194 ,p_commit_size => 2000);
1195
1196 print_debug_log('migrate_ibe_cc_data: Done with all processing . Returning a success status ');
1197
1198 EXCEPTION
1199 WHEN OTHERS THEN
1200 print_debug_log('In the exception block. Need to get outta here!');
1201 print_debug_log(SQLCODE||'-'||SQLERRM);
1202 retcode := 2;
1203 errbuf := SQLCODE||'-'||SQLERRM;
1204 END migrate_ibe_cc_data;
1205
1206 END IBE_PAYMENT_INT_PVT ;
[Home] [Help]