[Home] [Help]
PACKAGE BODY: APPS.ASO_PAYMENT_DATA_MIGRATION_PVT
Source
1 PACKAGE BODY ASO_PAYMENT_DATA_MIGRATION_PVT as
2 /* $Header: asovpdmb.pls 120.1 2006/07/31 21:06:17 skulkarn noship $ */
3 -- Start of Comments
4 -- FILENAME
5 -- asovmpdb.pls
6 --
7 -- DESCRIPTION
8 -- Package body of Aso_Payment_Data_Migration_Pvt
9 --
10 -- PROCEDURE LIST
11 -- Migrate_Credit_Card_Data
12 --
13 -- HISTORY
14 -- SEPT-07-2005 Initial Creation
15 --
16 -- End of Comments
17
18
19 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ASO_PAYMENT_DATA_MIGRATION_PVT';
20 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asovmpdb.pls';
21
22
23 PROCEDURE Migrate_Credit_Card_Data_Mgr
24 (
25 x_errbuf OUT NOCOPY VARCHAR2,
26 x_retcode OUT NOCOPY NUMBER,
27 X_batch_size IN NUMBER := 1000,
28 X_Num_Workers IN NUMBER := 5
29 )
30 is
31 l_product VARCHAR2(30) := 'ASO' ;
32
33 Begin
34 --***********************************************************
35 -- Log concurrent program Output
36 --***********************************************************
37 fnd_file.put_line(FND_FILE.OUTPUT, '');
38 fnd_file.put_line(FND_FILE.OUTPUT, 'Migrate ASO Credit Card Payment Data to Oracle Payment - Concurrent Program Manager');
39 fnd_file.put_line(FND_FILE.OUTPUT, '');
40 fnd_file.put_line(FND_FILE.OUTPUT, 'Concurrent Program Parameters');
41 fnd_file.put_line(FND_FILE.OUTPUT, 'Batch Size: X_batch_size: '|| X_batch_size);
42 fnd_file.put_line(FND_FILE.OUTPUT, 'Number of Threads: X_Num_Workers: '|| X_Num_Workers);
43
44 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
45
46 IF aso_debug_pub.g_debug_flag = 'Y' THEN
47 aso_debug_pub.add('****** Start of Migrate_Credit_Card_Data_Mgr API ******', 1, 'Y');
48 aso_debug_pub.add('Migrate_Credit_Card_Data_Mgr: X_batch_size: '|| X_batch_size, 1, 'Y');
49 aso_debug_pub.add('Migrate_Credit_Card_Data_Mgr: X_Num_Workers: '|| X_Num_Workers, 1, 'Y');
50 end if;
51
52 AD_CONC_UTILS_PKG.submit_subrequests(
53 X_errbuf => X_errbuf,
54 X_retcode => X_retcode,
55 X_WorkerConc_app_shortname => l_product,
56 X_workerConc_progname => 'ASOCCCONPWKR',
57 X_batch_size => X_batch_size,
58 X_Num_Workers => X_Num_Workers) ;
59
60 IF aso_debug_pub.g_debug_flag = 'Y' THEN
61 aso_debug_pub.add('****** End of Migrate_Credit_Card_Data_Mgr API ******', 1, 'Y');
62 end if;
63
64 End Migrate_Credit_Card_Data_Mgr;
65
66
67
68
69 PROCEDURE Migrate_Credit_Card_Data_Wkr
70 (
71 x_errbuf OUT NOCOPY VARCHAR2,
72 x_retcode OUT NOCOPY NUMBER,
73 X_batch_size IN NUMBER,
74 X_Worker_Id IN NUMBER,
75 X_Num_Workers IN NUMBER
76 )
77 is
78
79 TYPE Num15Tab IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
80 TYPE Char1Tab IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
81 TYPE Char30Tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
82 TYPE Char80Tab IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
83 TYPE Char100Tab IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
84 TYPE Char150Tab IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
85 TYPE Char255Tab IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
86 TYPE DateTab IS TABLE OF Date INDEX BY BINARY_INTEGER;
87
88 l_table_owner VARCHAR2(30);
89 l_any_rows_to_process BOOLEAN;
90
91 l_status VARCHAR2(30) ;
92 l_industry VARCHAR2(30) ;
93 l_retstatus BOOLEAN ;
94 l_product VARCHAR2(30) := 'ASO' ;
95
96 l_table_name VARCHAR2(30) := 'ASO_PAYMENTS';
97 l_script_name VARCHAR2(100) := 'asovpdmb'||to_char(sysdate,'ddmonyyyyhhmiss')||'.pls';
98
99 l_start_rowid ROWID;
100 l_end_rowid ROWID;
101 l_rows_processed NUMBER;
102
103 l_index NUMBER;
104 l_user_id NUMBER;
105
106 l_encryption_enabled BOOLEAN;
107
108 payment_id_tab Num15Tab;
109 payment_ref_number_tab Char30Tab; -- Changed
110 credit_card_code_tab char80Tab;
111 cc_holder_name_tab Char80Tab;
112 cc_expiration_date_tab Char30Tab;
113 order_id_tab Char100Tab;-- Changed
114 trxn_ref_number1_tab Char100Tab;
115 party_id_tab Num15Tab;
116 trxn_extension_id_tab Num15Tab;
117 instrument_id_tab Num15Tab;
118 ext_payer_id_tab Num15Tab;
119 create_payer_flag_tab Char1Tab;--Changed
120 instr_assignment_id_tab Num15Tab;
121 cc_number_hash1_tab Char30Tab;
122 cc_number_hash2_tab Char30Tab;
123 cc_issuer_range_id_tab Num15Tab;
124 sec_segment_id_tab Num15Tab;
125
126 cc_number_length_tab Num15Tab;
127 cc_unmask_digits_tab Char30Tab;
128 masked_cc_number_tab Char100Tab;
129 cc_org_id Num15Tab;
130 cc_cust_account_id Num15Tab;
131 cc_cust_account_id Num15Tab;
132 l_error_flag varchar2(1) := 'N';
133
134
135 /*
136 Cursor that queries all transactions needed to be migrated
137
138 Note: 1. This table is out-joined with the table IBY_EXTERNAL_PAYERS_ALL so that
139 the party contexts that are not in the external payer table can be identified.
140
141 2. The credit card number needs to be numeric.
142 */
143
144 cursor encrypted_credit_card_cur(p_start_rowid ROWID, p_end_rowid ROWID) is
145 SELECT pmt.payment_id,
146 translate(pmt.payment_ref_number,'0: -_', '0') payment_ref_number,
147 pmt.credit_card_code,
148 pmt.credit_card_holder_name,
149 pmt.credit_card_expiration_date,
150 to_char(pmt.payment_id) ||'-'|| hdr.quote_number, --order_id
151 to_char(hdr.quote_header_id) trxn_ref_number1,
152 nvl(hdr.invoice_to_cust_party_id, hdr.cust_party_id), --party_id
153 IBY_FNDCPT_TX_EXTENSIONS_S.nextval, --trxn_extension_id
154 IBY_INSTR_S.nextval,
155 DECODE(PAYER.EXT_PAYER_ID, null, IBY_EXTERNAL_PAYERS_ALL_S.nextval, PAYER.EXT_PAYER_ID),
156 DECODE(PAYER.EXT_PAYER_ID, null,'Y', 'N'), -- this flag determines whether we should create new external payer
157 IBY_PMT_INSTR_USES_ALL_S.nextval, -- the new instrument use id
158 sec.cc_number_hash1,
159 sec.cc_number_hash2,
160 sec.cc_issuer_range_id,
161 sec.sec_segment_id,
162 sec.cc_number_length,
163 sec.cc_unmask_digits,
164 lpad(sec.cc_unmask_digits, nvl(range.card_number_length, length(pmt.payment_ref_number)), 'X') masked_cc_number
165
166 FROM aso_payments pmt,
167 iby_external_payers_all payer,
168 aso_quote_headers_all hdr,
169 iby_security_segments sec,
170 iby_cc_issuer_ranges range
171
172 WHERE pmt.quote_header_id = hdr.quote_header_id
173 and pmt.payment_type_code = 'CREDIT_CARD'
174 and pmt.payment_ref_number is not null
175 and nvl(hdr.invoice_to_cust_party_id, hdr.cust_party_id) = payer.party_id (+)
176 and payer.cust_account_id is null
177 and payer.acct_site_use_id is null
178 and payer.org_id is null
179 --and 'OPERATING_UNIT' = payer.org_type(+)
180 and 'CUSTOMER_PAYMENT' = payer.payment_function(+)
181 and pmt.rowid between p_start_rowid and p_end_rowid
182 and pmt.trxn_extension_id is null
183 and sec.sec_segment_id = IBY_CC_SECURITY_PUB.get_segment_id(pmt.payment_ref_number)
184 and sec.cc_issuer_range_id = range.cc_issuer_range_id (+);
185
186
187
188 cursor unencrypted_credit_card_cur(p_start_rowid ROWID, p_end_rowid ROWID) is
189 SELECT pmt.payment_id,
190 pmt.payment_ref_number,
191 pmt.credit_card_code,
192 pmt.credit_card_holder_name,
193 pmt.credit_card_expiration_date,
194 to_char(pmt.payment_id) ||'-'|| hdr.quote_number, --order_id
195 to_char(hdr.quote_header_id) trxn_ref_number1,
196 nvl(hdr.invoice_to_cust_party_id, hdr.cust_party_id), --party_id
197 iby_fndcpt_tx_extensions_s.nextval, --trxn_extension_id
198 iby_instr_s.nextval,
199 decode(payer.ext_payer_id, null, iby_external_payers_all_s.nextval, payer.ext_payer_id),
200 decode(payer.ext_payer_id, null,'Y', 'N'), -- this flag determines whether we should create new external payer
201 iby_pmt_instr_uses_all_s.nextval, -- the new instrument use id
202 iby_fndcpt_setup_pub.get_hash(pmt.payment_ref_number, fnd_api.g_false) cc_number_hash1,
203 iby_fndcpt_setup_pub.get_hash(pmt.payment_ref_number, fnd_api.g_true) cc_number_hash2,
204 iby_cc_validate.get_cc_issuer_range(pmt.payment_ref_number) cc_issuer_range_id,
205 --null sec_segment_id,
206 decode(iby_cc_validate.get_cc_issuer_range(pmt.payment_ref_number), null,length(pmt.payment_ref_number), null) cc_number_length,
207 substr(pmt.payment_ref_number,greatest(-4,-length(pmt.payment_ref_number))) cc_unmask_digits,
208 lpad(substr(pmt.payment_ref_number, greatest(-4,-length(pmt.payment_ref_number))), length(pmt.payment_ref_number), 'X' ) masked_cc_number
209
210 FROM aso_payments pmt,
211 iby_external_payers_all payer,
212 aso_quote_headers_all hdr
213
214 WHERE pmt.quote_header_id = hdr.quote_header_id
215 and pmt.payment_type_code = 'CREDIT_CARD'
216 and pmt.payment_ref_number is not null
217 and nvl(hdr.invoice_to_cust_party_id, hdr.cust_party_id) = payer.party_id (+)
218 and payer.cust_account_id is null
219 and payer.acct_site_use_id is null
220 and payer.org_id is null
221 --and 'OPERATING_UNIT' = payer.org_type(+)
222 and 'CUSTOMER_PAYMENT' = payer.payment_function(+)
223 and pmt.rowid between p_start_rowid and p_end_rowid
224 and pmt.trxn_extension_id is null;
225
226
227 Begin
228 --***********************************************************
229 -- Log concurrent program Output
230 --***********************************************************
231 fnd_file.put_line(FND_FILE.OUTPUT, 'Modified Program');
232 fnd_file.put_line(FND_FILE.OUTPUT, 'Migrate ASO Credit Card Payment Data to Oracle Payment - Concurrent Program');
233 fnd_file.put_line(FND_FILE.OUTPUT, '');
234 fnd_file.put_line(FND_FILE.OUTPUT, 'Concurrent Program Parameters');
235 fnd_file.put_line(FND_FILE.OUTPUT, 'Batch Size : '|| X_batch_size);
236 fnd_file.put_line(FND_FILE.OUTPUT, 'Number of Threads : '|| X_Num_Workers);
237
238 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
239
240 IF aso_debug_pub.g_debug_flag = 'Y' THEN
241 aso_debug_pub.add('****** Start of Migrate_Credit_Card_Data API ******', 1, 'Y');
242 END IF;
243
244 l_user_id := NVL(fnd_global.user_id, -1);
245
246 if aso_debug_pub.g_debug_flag = 'Y' then
247 aso_debug_pub.add('Migrate_Credit_Card_Data: l_user_id: '|| l_user_id, 1, 'Y');
248 aso_debug_pub.add('Migrate_Credit_Card_Data: l_table_owner: '|| l_table_owner, 1, 'Y');
249 aso_debug_pub.add('Migrate_Credit_Card_Data: l_table_name: '|| l_table_name, 1, 'Y');
250 aso_debug_pub.add('Migrate_Credit_Card_Data: l_script_name: '|| l_script_name, 1, 'Y');
251 aso_debug_pub.add('Migrate_Credit_Card_Data: x_worker_id: '|| x_worker_id, 1, 'Y');
252 aso_debug_pub.add('Migrate_Credit_Card_Data: x_num_workers: '|| x_num_workers, 1, 'Y');
253 aso_debug_pub.add('Migrate_Credit_Card_Data: x_batch_size: '|| x_batch_size, 1, 'Y');
254 end if;
255
256 --
257 -- get schema name of the table for ROWID range processing
258 --
259 l_retstatus := fnd_installation.get_app_info(l_product, l_status, l_industry, l_table_owner) ;
260
261 if aso_debug_pub.g_debug_flag = 'Y' then
262 --aso_debug_pub.add('Migrate_Credit_Card_Data_Wkr: l_retstatus: '|| l_retstatus, 1, 'Y');
263 aso_debug_pub.add('Migrate_Credit_Card_Data_Wkr: l_table_owner: '|| l_table_owner, 1, 'Y');
264 end if;
265
266 ad_parallel_updates_pkg.initialize_rowid_range( ad_parallel_updates_pkg.ROWID_RANGE,
267 l_table_owner,
268 l_table_name,
269 l_script_name,
270 x_worker_id,
271 x_num_workers,
272 x_batch_size,
273 0
274 );
275
276 ad_parallel_updates_pkg.get_rowid_range( l_start_rowid,
277 l_end_rowid,
278 l_any_rows_to_process,
279 x_batch_size,
280 TRUE
281 );
282
283 if aso_debug_pub.g_debug_flag = 'Y' then
284 aso_debug_pub.add('Migrate_Credit_Card_Data: l_start_rowid: '|| l_start_rowid, 1, 'Y');
285 aso_debug_pub.add('Migrate_Credit_Card_Data: l_end_rowid: '|| l_end_rowid, 1, 'Y');
286 --aso_debug_pub.add('Migrate_Credit_Card_Data: l_any_rows_to_process: '|| l_any_rows_to_process, 1, 'Y');
287 aso_debug_pub.add('Migrate_Credit_Card_Data: x_batch_size: '|| x_batch_size, 1, 'Y');
288 end if;
289
290 --Check if 11i cc encryption is enabled or not
291 l_encryption_enabled := iby_cc_security_pub.encryption_enabled();
292
293 --if aso_debug_pub.g_debug_flag = 'Y' then
294 --aso_debug_pub.add('Migrate_Credit_Card_Data: l_encryption_enabled: '|| l_encryption_enabled, 1, 'Y');
295 --end if;
296
297
298 WHILE (l_any_rows_to_process = TRUE) LOOP
299
300
301 IF l_encryption_enabled THEN
302
303 OPEN encrypted_credit_card_cur(l_start_rowid, l_end_rowid);
304
305 -- Fetch the transactions
306 FETCH encrypted_credit_card_cur
307 BULK COLLECT INTO payment_id_tab,
308 payment_ref_number_tab,
309 credit_card_code_tab,
310 cc_holder_name_tab,
311 cc_expiration_date_tab,
312 order_id_tab,
313 trxn_ref_number1_tab,
314 party_id_tab,
315 trxn_extension_id_tab,
316 instrument_id_tab,
317 ext_payer_id_tab,
318 create_payer_flag_tab,
319 instr_assignment_id_tab,
320 cc_number_hash1_tab,
321 cc_number_hash2_tab,
322 cc_issuer_range_id_tab,
323 sec_segment_id_tab,
324 cc_number_length_tab,
325 cc_unmask_digits_tab,
326 masked_cc_number_tab;
327
328 CLOSE encrypted_credit_card_cur;
329
330 aso_debug_pub.add('Before for loop payment_id_tab.count: '|| payment_id_tab.count );
331 fnd_file.put_line(FND_FILE.OUTPUT, 'Before for loop payment_id_tab.count: '|| payment_id_tab.count );
332
333 IF payment_id_tab.count > 0 THEN
334
335 FOR i in payment_id_tab.first..payment_id_tab.last LOOP
336
337 if aso_debug_pub.g_debug_flag = 'Y' then
338 -- new debug messages
339 aso_debug_pub.add('******************************************');
340 aso_debug_pub.add('payment_id_tab('||i||'): '|| payment_id_tab(i) );
341 aso_debug_pub.add('payment_ref_number_tab('||i||'): '|| payment_ref_number_tab(i) );
342 aso_debug_pub.add('credit_card_code_tab('||i||'): '|| credit_card_code_tab(i) );
343 aso_debug_pub.add('cc_expiration_date_tab('||i||') '|| cc_expiration_date_tab(i) );
344 aso_debug_pub.add('cc_holder_name_tab('||i||'): '|| cc_holder_name_tab(i) );
345 aso_debug_pub.add('cc_expiration_date_tab('||i||'): '|| cc_expiration_date_tab(i) );
346 aso_debug_pub.add('order_id_tab('||i||'): '|| order_id_tab(i) );
347 aso_debug_pub.add('trxn_ref_number1_tab('||i||'): '|| trxn_ref_number1_tab(i) );
348 aso_debug_pub.add('party_id_tab('||i||'): '|| party_id_tab(i) );
349 aso_debug_pub.add('trxn_extension_id_tab('||i||'): '|| trxn_extension_id_tab(i) );
350 aso_debug_pub.add('instrument_id_tab('||i||'): '|| instrument_id_tab(i));
351 aso_debug_pub.add('create_payer_flag_tab('||i||'): '|| create_payer_flag_tab(i) );
352 aso_debug_pub.add('instr_assignment_id_tab: '|| instr_assignment_id_tab(i) );
353 aso_debug_pub.add('cc_number_hash1_tab('||i||'): '|| cc_number_hash1_tab(i) );
354 aso_debug_pub.add('cc_number_hash2_tab('||i||'): '|| cc_number_hash2_tab(i) );
355 aso_debug_pub.add('cc_issuer_range_id_tab('||i||'): '|| cc_issuer_range_id_tab(i) );
356 aso_debug_pub.add('sec_segment_id_tab('||i||'): '|| sec_segment_id_tab(i) );
357 aso_debug_pub.add('cc_number_length_tab('||i||'): '|| cc_number_length_tab(i) );
358 aso_debug_pub.add('cc_unmask_digits_tab('||i||'): '|| cc_unmask_digits_tab(i) );
359 aso_debug_pub.add('masked_cc_number_tab('||i||'): '|| masked_cc_number_tab(i) );
360
361 aso_debug_pub.add('******************************************');
362 END IF;
363 END LOOP;
364 if aso_debug_pub.g_debug_flag = 'Y' then
365 aso_debug_pub.add('After for loop payment_id_tab.count: '|| payment_id_tab.count );
366 end if;
367
368
369 BEGIN -- begin1
370
371 fnd_file.put_line(FND_FILE.OUTPUT, 'Before insering into table IBY_CREDITCARD');
372
373 -- create new credit cards with single use only
374 FORALL i IN payment_id_tab.first..payment_id_tab.last SAVE EXCEPTIONS
375
376 INSERT INTO IBY_CREDITCARD( CARD_OWNER_ID,
377 INSTRUMENT_TYPE,
378 PURCHASECARD_FLAG,
379 CARD_ISSUER_CODE,
380 ACTIVE_FLAG,
381 SINGLE_USE_FLAG,
382 EXPIRYDATE,
383 CHNAME,
384 CCNUMBER,
385 INSTRID,
386 CREATED_BY,
387 CREATION_DATE,
388 LAST_UPDATED_BY,
389 LAST_UPDATE_DATE,
390 LAST_UPDATE_LOGIN,
391 ENCRYPTED,
392 CC_NUMBER_HASH1,
393 CC_NUMBER_HASH2,
394 CC_ISSUER_RANGE_ID,
395 --SEC_SEGMENT_ID_TAB, --getting expression is of wrong type error, so commenting out, but needs to verify it latter on
396 CARD_MASK_SETTING,
397 CARD_UNMASK_LENGTH,
398 CC_NUMBER_LENGTH,
399 MASKED_CC_NUMBER,
400 --SEC_SUBKEY_ID,
401 OBJECT_VERSION_NUMBER
402 )
403 VALUES ( party_id_tab(i),
404 'CREDITCARD',
405 'N',
406 credit_card_code_tab(i),
407 'Y',
408 'Y',
409 cc_expiration_date_tab(i),
410 cc_holder_name_tab(i),
411 decode(sec_segment_id_tab(i), null,payment_ref_number_tab(i), cc_unmask_digits_tab(i)),
412 instrument_id_tab(i),
413 l_user_id,
414 sysdate,
415 l_user_id,
416 sysdate,
417 l_user_id,
418 decode(SEC_SEGMENT_ID_TAB(i),null,'N','Y'),
419 cc_number_hash1_tab(i),
420 cc_number_hash2_tab(i),
421 cc_issuer_range_id_tab(i),
422 --sec_segment_id_tab(i),--getting expression is of wrong type error, so commenting out, but needs to verify it latter on
423 'DISPLAY_LAST',
424 4,
425 cc_number_length_tab(i),
426 masked_cc_number_tab(i),
427 1
428 );
429 fnd_file.put_line(FND_FILE.OUTPUT, 'After insering into table IBY_CREDITCARD');
430
431 EXCEPTION
432
433 WHEN OTHERS THEN
434 l_error_flag := 'Y';
435
436 FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
437
438 fnd_file.put_line(FND_FILE.OUTPUT, 'Error occurred during iteration ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
439 ' Oracle error is ' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE );
440
441 fnd_file.put_line(FND_FILE.OUTPUT, 'Insert failing at IBY_CREDITCARD for Payment ID ' || payment_id_tab(j));
442 END LOOP;
443
444 END; --end begin1
445
446
447 -- Now insert into the instrument use table
448 BEGIN -- begin2
449 fnd_file.put_line(FND_FILE.OUTPUT, 'Before insering into table IBY_PMT_INSTR_USES_ALL ');
450
451 FORALL i IN payment_id_tab.first .. payment_id_tab.last SAVE EXCEPTIONS
452
453 INSERT INTO IBY_PMT_INSTR_USES_ALL( INSTRUMENT_PAYMENT_USE_ID,
454 EXT_PMT_PARTY_ID,
455 INSTRUMENT_TYPE,
456 INSTRUMENT_ID,
457 PAYMENT_FUNCTION,
458 ORDER_OF_PREFERENCE,
459 START_DATE,
460 CREATED_BY,
461 CREATION_DATE,
462 LAST_UPDATED_BY,
463 LAST_UPDATE_DATE,
464 LAST_UPDATE_LOGIN,
465 OBJECT_VERSION_NUMBER,
466 payment_flow
467 )
468
469 SELECT instr_assignment_id_tab(i),
470 ext_payer_id_tab(i),
471 'CREDITCARD',
472 instrument_id_tab(i),
473 'CUSTOMER_PAYMENT',
474 1,
475 sysdate,
476 l_user_id,
477 sysdate,
478 l_user_id,
479 sysdate,
480 l_user_id,
481 1,
482 'FUNDS_CAPTURE'
483
484 FROM IBY_EXTERNAL_PAYERS_ALL
485
486 -- Note: For products that do not use all the party context columns, it is mandatory to
487 -- add NOT NULL clause for those columns.
488
489 WHERE payment_function = 'CUSTOMER_PAYMENT'
490 and party_id = party_id_tab(i)
491 and org_type is null
492 and org_id is null
493 and cust_account_id is null
494 and acct_site_use_id is null
495 and rownum = 1;
496
497
498 fnd_file.put_line(FND_FILE.OUTPUT, 'After insering into table IBY_PMT_INSTR_USES_ALL ');
499
500 EXCEPTION
501
502 WHEN OTHERS THEN
503 l_error_flag := 'Y';
504
505 FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
506
507 fnd_file.put_line(FND_FILE.OUTPUT, 'Error occurred during iteration ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX || ' Oracle error is ' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE );
508
509 fnd_file.put_line(FND_FILE.OUTPUT, 'Insert failing at IBY_PMT_INSTR_USES_ALL for Payment Id: ' || payment_id_tab(j));
510 fnd_file.put_line(FND_FILE.OUTPUT, 'Insert failing at IBY_PMT_INSTR_USES_ALL for Instrument Assignment Id: ' || instr_assignment_id_tab(j));
511 END LOOP;
512
513 END; --end2
514
515 -- insert the transactions into IBY transaction extension table
516
517 BEGIN --begin3
518
519 fnd_file.put_line(FND_FILE.OUTPUT, 'Before insering into table IBY_FNDCPT_TX_EXTENSIONS');
520
521 FORALL i IN payment_id_tab.first .. payment_id_tab.last SAVE EXCEPTIONS
522
523 INSERT INTO IBY_FNDCPT_TX_EXTENSIONS( TRXN_EXTENSION_ID,
524 PAYMENT_CHANNEL_CODE,
525 INSTR_ASSIGNMENT_ID,
526 ORDER_ID,
527 PO_NUMBER,
528 TRXN_REF_NUMBER1,
529 TRXN_REF_NUMBER2,
530 ADDITIONAL_INFO,
531 TANGIBLEID,
532 CREATED_BY,
533 CREATION_DATE,
534 LAST_UPDATED_BY,
535 LAST_UPDATE_DATE,
536 LAST_UPDATE_LOGIN,
537 OBJECT_VERSION_NUMBER,
538 encrypted,
539 origin_application_id
540 )
541
542 VALUES( trxn_extension_id_tab(i),
543 'CREDIT_CARD',
544 instr_assignment_id_tab(i),
545 order_id_tab(i),
546 null,
547 trxn_ref_number1_tab(i),
548 null,
549 null,
550 null,
551 l_user_id,
552 sysdate,
553 l_user_id,
554 sysdate,
555 l_user_id,
556 1,
557 'Y',
558 679
559 );
560
561 fnd_file.put_line(FND_FILE.OUTPUT, 'After insering into table IBY_FNDCPT_TX_EXTENSIONS');
562
563 EXCEPTION
564
565 WHEN OTHERS THEN
566 l_error_flag := 'Y';
567
568 FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
569
570 fnd_file.put_line(FND_FILE.OUTPUT, 'Error occurred during iteration ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX || ' Oracle error is ' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE );
571
572 fnd_file.put_line(FND_FILE.OUTPUT, 'Insert failing at IBY_PMT_INSTR_USES_ALL for trxn_ref_number1_tab(j): ' || trxn_ref_number1_tab(j));
573 END LOOP;
574
575 END; --end3
576
577 END IF; -- END IF FOR THE payment_id_tab.count
578
579 ELSE ---l_encryption_enabled is false
580
581 fnd_file.put_line(FND_FILE.OUTPUT, 'l_encryption_enabled is false');
582
583
584 OPEN unencrypted_credit_card_cur(l_start_rowid, l_end_rowid);
585 -- Fetch the transactions
586 FETCH unencrypted_credit_card_cur
587 BULK COLLECT INTO payment_id_tab,
588 payment_ref_number_tab,
589 credit_card_code_tab,
590 cc_holder_name_tab,
591 cc_expiration_date_tab,
592 order_id_tab,
593 trxn_ref_number1_tab,
594 party_id_tab,
595 trxn_extension_id_tab,
596 instrument_id_tab,
597 ext_payer_id_tab,
598 create_payer_flag_tab,
599 instr_assignment_id_tab,
600 cc_number_hash1_tab,
601 cc_number_hash2_tab,
602 cc_issuer_range_id_tab,
603 --sec_segment_id_tab,
604 cc_number_length_tab,
605 cc_unmask_digits_tab,
606 masked_cc_number_tab;
607
608 CLOSE unencrypted_credit_card_cur;
609
610 aso_debug_pub.add('Before for loop payment_id_tab.count: '|| payment_id_tab.count );
611 fnd_file.put_line(FND_FILE.OUTPUT, 'Before for loop payment_id_tab.count: '|| payment_id_tab.count );
612
613 IF payment_id_tab.count > 0 THEN
614
615 FOR i in payment_id_tab.first..payment_id_tab.last LOOP
616
617 if aso_debug_pub.g_debug_flag = 'Y' then
618 -- new debug messages
619 aso_debug_pub.add('******************************************');
620 aso_debug_pub.add('payment_id_tab('||i||'): '|| payment_id_tab(i) );
621 aso_debug_pub.add('payment_ref_number_tab('||i||'): '|| payment_ref_number_tab(i) );
622 aso_debug_pub.add('credit_card_code_tab('||i||'): '|| credit_card_code_tab(i) );
623 aso_debug_pub.add('cc_holder_name_tab('||i||'): '|| cc_holder_name_tab(i) );
624 aso_debug_pub.add('cc_expiration_date_tab('||i||') '|| cc_expiration_date_tab(i) );
625 aso_debug_pub.add('order_id_tab('||i||'): '|| order_id_tab(i) );
626 aso_debug_pub.add('trxn_ref_number1_tab('||i||'): '|| trxn_ref_number1_tab(i) );
627 aso_debug_pub.add('party_id_tab('||i||'): '|| party_id_tab(i) );
628 aso_debug_pub.add('trxn_extension_id_tab('||i||'): '|| trxn_extension_id_tab(i) );
629 aso_debug_pub.add('instrument_id_tab('||i||'): '|| instrument_id_tab(i));
630 aso_debug_pub.add('ext_payer_id_tab('||i||'): '|| ext_payer_id_tab(i));
631 aso_debug_pub.add('create_payer_flag_tab('||i||'): '|| create_payer_flag_tab(i) );
632 aso_debug_pub.add('instr_assignment_id_tab: '|| instr_assignment_id_tab(i) );
633 aso_debug_pub.add('cc_number_hash1_tab('||i||'): '|| cc_number_hash1_tab(i) );
634 aso_debug_pub.add('cc_number_hash2_tab('||i||'): '|| cc_number_hash2_tab(i) );
635 aso_debug_pub.add('cc_issuer_range_id_tab('||i||'): '|| cc_issuer_range_id_tab(i) );
636 --aso_debug_pub.add('sec_segment_id_tab('||i||'): '|| sec_segment_id_tab(i) );
637 aso_debug_pub.add('cc_number_length_tab('||i||'): '|| cc_number_length_tab(i) );
638 aso_debug_pub.add('cc_unmask_digits_tab('||i||'): '|| cc_unmask_digits_tab(i) );
639 aso_debug_pub.add('masked_cc_number_tab('||i||'): '|| masked_cc_number_tab(i) );
640 aso_debug_pub.add('******************************************');
641 END IF;
642 END LOOP;
643
644 BEGIN --begin4
645 -- create new credit cards with single use only
646
647 fnd_file.put_line(FND_FILE.OUTPUT, 'Before insering into table IBY_CREDITCARD');
648
649 FORALL i IN payment_id_tab.first..payment_id_tab.last SAVE EXCEPTIONS
650
651 INSERT INTO IBY_CREDITCARD( CARD_OWNER_ID,
652 INSTRUMENT_TYPE,
653 PURCHASECARD_FLAG,
654 CARD_ISSUER_CODE,
655 ACTIVE_FLAG,
656 SINGLE_USE_FLAG,
657 EXPIRYDATE,
658 CHNAME,
659 CCNUMBER,
660 INSTRID,
661 CREATED_BY,
662 CREATION_DATE,
663 LAST_UPDATED_BY,
664 LAST_UPDATE_DATE,
665 LAST_UPDATE_LOGIN,
666 ENCRYPTED,
667 CC_NUMBER_HASH1,
668 CC_NUMBER_HASH2,
669 CC_ISSUER_RANGE_ID,
670 --SEC_SEGMENT_ID_TAB,
671 CARD_MASK_SETTING,
672 CARD_UNMASK_LENGTH,
673 CC_NUMBER_LENGTH,
674 MASKED_CC_NUMBER,
675 --SEC_SUBKEY_ID,
676 OBJECT_VERSION_NUMBER
677 )
678 VALUES ( party_id_tab(i),
679 'CREDITCARD',
680 'N',
681 credit_card_code_tab(i),
682 'Y',
683 'Y',
684 cc_expiration_date_tab(i),
685 cc_holder_name_tab(i),
686 --decode(sec_segment_id_tab(i), null,payment_ref_number_tab(i), cc_unmask_digits_tab(i)),
687 payment_ref_number_tab(i),
688 instrument_id_tab(i),
689 l_user_id,
690 sysdate,
691 l_user_id,
692 sysdate,
693 l_user_id,
694 --decode(SEC_SEGMENT_ID_TAB(i), NULL,'N','Y'),
695 'N',
696 cc_number_hash1_tab(i),
697 cc_number_hash2_tab(i),
698 cc_issuer_range_id_tab(i),
699 --sec_segment_id_tab(i),
700 'DISPLAY_LAST',
701 4,
702 cc_number_length_tab(i),
703 masked_cc_number_tab(i),
704 1
705 );
706
707 fnd_file.put_line(FND_FILE.OUTPUT, 'After insering into table IBY_CREDITCARD');
708
709 EXCEPTION
710
711 WHEN OTHERS THEN
712 l_error_flag := 'Y';
713
714 FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
715
716 fnd_file.put_line(FND_FILE.OUTPUT, 'Error occurred during iteration ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
717 ' Oracle error is ' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE );
718
719 fnd_file.put_line(FND_FILE.OUTPUT, 'Insert failing at IBY_CREDITCARD for Payment ID ' || payment_id_tab(j));
720 END LOOP;
721
722 END; --end4
723
724
725 -- Now insert into the instrument use table
726 BEGIN --begin5
727
728 fnd_file.put_line(FND_FILE.OUTPUT, 'Before insering into table IBY_PMT_INSTR_USES_ALL');
729
730 FORALL i IN payment_id_tab.first .. payment_id_tab.last SAVE EXCEPTIONS
731
732 INSERT INTO IBY_PMT_INSTR_USES_ALL( INSTRUMENT_PAYMENT_USE_ID,
733 EXT_PMT_PARTY_ID,
734 INSTRUMENT_TYPE,
735 INSTRUMENT_ID,
736 PAYMENT_FUNCTION,
737 ORDER_OF_PREFERENCE,
738 START_DATE,
739 CREATED_BY,
740 CREATION_DATE,
741 LAST_UPDATED_BY,
742 LAST_UPDATE_DATE,
743 LAST_UPDATE_LOGIN,
744 OBJECT_VERSION_NUMBER,
745 payment_flow
746 )
747
748 SELECT instr_assignment_id_tab(i),
749 ext_payer_id_tab(i),
750 'CREDITCARD',
751 instrument_id_tab(i),
752 'CUSTOMER_PAYMENT',
753 1,
754 sysdate,
755 l_user_id,
756 sysdate,
757 l_user_id,
758 sysdate,
759 l_user_id,
760 1,
761 'FUNDS_CAPTURE'
762
763 FROM IBY_EXTERNAL_PAYERS_ALL
764
765 -- Note: For products that do not use all the party context columns, it is mandatory to
766 -- add NOT NULL clause for those columns.
767
768 WHERE payment_function = 'CUSTOMER_PAYMENT'
769 and party_id = party_id_tab(i)
770 and org_type is null
771 and org_id is null
772 and cust_account_id is null
773 and acct_site_use_id is null
774 and rownum = 1;
775
776 fnd_file.put_line(FND_FILE.OUTPUT, 'After insering into table IBY_PMT_INSTR_USES_ALL');
777
778 EXCEPTION
779
780 WHEN OTHERS THEN
781 l_error_flag := 'Y';
782
783 FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
784
785 fnd_file.put_line(FND_FILE.OUTPUT, 'Error occurred during iteration ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX || ' Oracle error is ' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE );
786
787 fnd_file.put_line(FND_FILE.OUTPUT, 'Insert failing at IBY_PMT_INSTR_USES_ALL for Payment Id: ' || payment_id_tab(j));
788 fnd_file.put_line(FND_FILE.OUTPUT, 'Insert failing at IBY_PMT_INSTR_USES_ALL for Instrument Assignment Id: ' || instr_assignment_id_tab(j));
789 END LOOP;
790
791 END; --end5
792
793
794 -- insert the transactions into IBY transaction extension table
795
796 BEGIN -- begin6
797
798 fnd_file.put_line(FND_FILE.OUTPUT, 'Before insering into table IBY_FNDCPT_TX_EXTENSIONS');
799
800 FORALL i IN payment_id_tab.first .. payment_id_tab.last SAVE EXCEPTIONS
801
802 INSERT INTO IBY_FNDCPT_TX_EXTENSIONS( TRXN_EXTENSION_ID,
803 PAYMENT_CHANNEL_CODE,
804 INSTR_ASSIGNMENT_ID,
805 ORDER_ID,
806 PO_NUMBER,
807 TRXN_REF_NUMBER1,
808 TRXN_REF_NUMBER2,
809 ADDITIONAL_INFO,
810 TANGIBLEID,
811 CREATED_BY,
812 CREATION_DATE,
813 LAST_UPDATED_BY,
814 LAST_UPDATE_DATE,
815 LAST_UPDATE_LOGIN,
816 OBJECT_VERSION_NUMBER,
817 encrypted,
818 origin_application_id
819 )
820
821 VALUES( trxn_extension_id_tab(i),
822 'CREDIT_CARD',
823 instr_assignment_id_tab(i),
824 order_id_tab(i),
825 null,
826 trxn_ref_number1_tab(i),
827 null,
828 null,
829 null,
830 l_user_id,
831 sysdate,
832 l_user_id,
833 sysdate,
834 l_user_id,
835 1,
836 'Y',
837 679
838 );
839
840 fnd_file.put_line(FND_FILE.OUTPUT, 'After insering into table IBY_FNDCPT_TX_EXTENSIONS');
841
842 EXCEPTION
843
844 WHEN OTHERS THEN
845 l_error_flag := 'Y';
846
847 FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
848
849 fnd_file.put_line(FND_FILE.OUTPUT, 'Error occurred during iteration ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX || ' Oracle error is ' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE );
850
851 fnd_file.put_line(FND_FILE.OUTPUT, 'Insert failing at IBY_PMT_INSTR_USES_ALL for trxn_ref_number1_tab(j): ' || trxn_ref_number1_tab(j));
852 END LOOP;
853
854 END; --end6
855 END IF; -- for payment table count
856 END IF; ---end if for l_encryption_enabled
857
858
859
860 -- update the foreign key relationship
861
862 BEGIN -- begin7
863
864 IF (l_error_flag = 'N' and payment_id_tab.count > 0 ) THEN
865
866 fnd_file.put_line(FND_FILE.OUTPUT, 'Before updating the aso_payments table');
867
868 FORALL i IN payment_id_tab.first..payment_id_tab.last SAVE EXCEPTIONS
869
870 UPDATE aso_payments a
871 SET a.TRXN_EXTENSION_ID = TRXN_EXTENSION_ID_TAB(i),
872 a.CREDIT_CARD_APPROVAL_CODE = NULL,
873 a.CREDIT_CARD_APPROVAL_DATE = NULL,
874 a.CREDIT_CARD_CODE = NULL,
875 a.CREDIT_CARD_EXPIRATION_DATE = NULL,
876 a.CREDIT_CARD_HOLDER_NAME = NULL,
877 a.PAYMENT_REF_NUMBER = NULL
878 WHERE a.payment_id = payment_id_tab(i);
879
880 fnd_file.put_line(FND_FILE.OUTPUT, 'After updating the aso_payments table');
881
882 ELSE
883 IF payment_id_tab.count = 0 THEN
884 fnd_file.put_line(FND_FILE.OUTPUT,'No payment tbl records to update');
885 elsif l_error_flag = 'Y' then
886 fnd_file.put_line(FND_FILE.OUTPUT,'Did not update aso_payments table because of errors');
887 end if;
888 END IF;
889
890
891 EXCEPTION
892
893 WHEN OTHERS THEN
894
895 FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
896
897 fnd_file.put_line(FND_FILE.OUTPUT, 'Error occurred during iteration ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX || ' Oracle error is ' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE );
898
899 fnd_file.put_line(FND_FILE.OUTPUT, 'Update failing at asopayments for payment_id_tab(j): ' || payment_id_tab(j));
900 END LOOP;
901
902 --l_rows_processed := SQL%ROWCOUNT;
903
904 -- ad_parallel_updates_pkg.processed_rowid_range( l_rows_processed, l_end_rowid);
905 END;
906 --end7
907
908 l_rows_processed := SQL%ROWCOUNT;
909 ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,l_end_rowid);
910
911 IF (l_error_flag = 'N') THEN
912 COMMIT;
913 fnd_file.put_line(FND_FILE.OUTPUT, '*** Commiting the changes ****** ');
914 END IF;
915 --
916 -- get new range of rowids
917 --
918
919 ad_parallel_updates_pkg.get_rowid_range( l_start_rowid,
920 l_end_rowid,
921 l_any_rows_to_process,
922 x_batch_size,
923 FALSE
924 );
925
926
927
928 END LOOP; -- loop for l_any_rows_to_process
929
930
931 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
932
933
934 -- handle any exception if necessary
935
936 EXCEPTION
937 WHEN OTHERS THEN
938 aso_debug_pub.add('Inside Exception',1,'Y' );
939 fnd_file.put_line(FND_FILE.OUTPUT, 'Inside the outermost exception block');
940 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
941 raise;
942 End Migrate_Credit_Card_Data_Wkr;
943
944
945 END Aso_Payment_Data_Migration_Pvt;