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