[Home] [Help]
PACKAGE BODY: APPS.OE_PAYMENT_DATA_MIGRATION_UTIL
Source
1 PACKAGE BODY OE_Payment_Data_Migration_Util AS
2 -- $Header: OEXUPDMB.pls 120.15.12010000.2 2008/08/04 15:04:23 amallik ship $
3 --+=======================================================================+
4 --| Copyright (c) 2000 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| OEXUPDMB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Package Spec of OE_Payment_Data_Migration_Util |
13 --| This package body contains some utility procedures for handling |
14 --| payment data migration of an closed order to oracle payment. |
15 --| |
16 --| PROCEDURE LIST |
17 --| Migrate_Data_MGR |
18 --| Migrate_Data_WKR |
19 --| Purge_Data_MGR |
20 --| Purge_Data_WKR |
21 --| |
22 --| HISTORY |
23 --| JUN-25-2005 Initial creation |
24 --+=======================================================================+
25
26 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Payment_Data_Migration_Util';
27
28 --6757060
29 Function Strip_Non_Numeric_Char(
30 p_credit_card_num IN iby_ext_bank_accounts_v.bank_account_number%TYPE
31 )
32 RETURN VARCHAR2
33 IS
34 p_stripped_cc_num iby_ext_bank_accounts_v.bank_account_number%TYPE;
35 BEGIN
36
37 IF p_credit_card_num IS NOT NULL THEN
38 ARP_EXT_BANK_PKG.strip_white_spaces(p_credit_card_num,p_stripped_cc_num);
39 RETURN p_stripped_cc_num;
40 ELSE
41 RETURN NULL;
42 END IF;
43
44 EXCEPTION
45 when OTHERS then
46 raise;
47 END Strip_Non_Numeric_Char;
48 --6757060
49
50 PROCEDURE Migrate_Data_MGR
51 ( X_errbuf OUT NOCOPY VARCHAR2,
52 X_retcode OUT NOCOPY VARCHAR2,
53 X_batch_size IN NUMBER,
54 X_Num_Workers IN NUMBER
55 )
56 IS
57 l_product VARCHAR2(30) := 'ONT' ;
58 BEGIN
59 AD_CONC_UTILS_PKG.submit_subrequests(
60 X_errbuf => X_errbuf,
61 X_retcode => X_retcode,
62 X_WorkerConc_app_shortname => l_product,
63 X_workerConc_progname => 'ONTIBYFC_WKR',
64 X_batch_size => X_batch_size,
65 X_Num_Workers => X_Num_Workers) ;
66 END Migrate_Data_MGR ;
67
68 PROCEDURE Migrate_Data_WKR
69 ( X_errbuf OUT NOCOPY VARCHAR2,
70 X_retcode OUT NOCOPY VARCHAR2,
71 X_batch_size IN NUMBER,
72 X_Worker_Id IN NUMBER,
73 X_Num_Workers IN NUMBER
74 )
75 IS
76 TYPE HEADER_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
77 TYPE LINE_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
78 TYPE PAYMENT_NUMBER IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
79 TYPE TANGIBLEID IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER ;
80 TYPE INSTR_ASSIGNMENT_ID IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER ;
81 TYPE EXT_PARTY_ID IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER ;
82 TYPE TRXN_ENTITY_ID IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER ;
83
84 header_id_tab header_id ;
85 line_id_tab line_id ;
86 payment_number_tab payment_number ;
87 tangibleid_tab tangibleid ;
88 instr_assignment_id_tab instr_assignment_id ;
89 ext_party_id_tab ext_party_id ;
90 trxn_entity_id_tab trxn_entity_id ;
91
92 l_table_owner VARCHAR2(30) ;
93 l_batch_size VARCHAR2(30) ;
94 l_worker_id NUMBER ;
95 l_num_workers NUMBER ;
96 l_any_rows_to_process BOOLEAN ;
97
98 l_table_name VARCHAR2(30) ;
99 l_product VARCHAR2(30) := 'ONT' ;
100 l_script_name VARCHAR2(30) := 'OEXUPDMB.pls' ;
101
102 l_start_rowid ROWID ;
103 l_end_rowid ROWID ;
104 l_cutoff_date DATE;
105 l_rows_processed NUMBER ;
106 l_user_id NUMBER := NVL(fnd_global.user_id, -1) ;
107
108 l_status VARCHAR2(30) ;
109 l_industry VARCHAR2(30) ;
110 l_retstatus BOOLEAN ;
111
112 l_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS ;
113 l_msg_count NUMBER := 0 ;
114 l_msg_data VARCHAR2(2000) ;
115
116 l_process_total1 NUMBER := 0 ;
117 l_process_total2 NUMBER := 0 ;
118
119 l_error_total NUMBER := 0 ;
120
121 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level ;
122
123 -- Cursors that query all transactions needed to be migrated
124 -- since all credit cards for closed orders would have already created bank account id
125 -- in ap_bank_accounts, so we can get the bank_account_id first, then get the instrument
126 -- assignment id from IBY_UPG_INSTRUMENTS. With the instrument assignemen id, we can directly
127 -- create and insert a new record in Oracle Payments table IBY_FNDCPT_TX_EXTENSIONS.
128
129 -- Define a cursor payments_cur1 to select the payment data that only exist in oe_order_headers_all
130 -- but not in oe_payments for closed orders
131
132 CURSOR payments_cur1 (p_start_rowid ROWID, p_end_rowid ROWID, l_cutoff_date DATE) is
133 SELECT /*+ LEADING (OOH) */ ooh.header_id,
134 null line_id, -- line_id
135 OE_Default_Header_Payment.get_payment_number(ooh.header_id),
136 ita.tangibleid,
137 uba.instr_assignment_id,
138 uba.ext_party_id,
139 iby_fndcpt_tx_extensions_s.nextval -- the new transaction extension ID
140 FROM oe_order_headers_all ooh,
141 hz_cust_site_uses_all su,
142 hz_cust_acct_sites_all cas,
143 ap_bank_accounts_all ba,
144 ap_bank_account_uses_all bau,
145 iby_trans_all_v ita,
146 iby_upg_instruments uba
147 WHERE ooh.open_flag = 'N'
148 AND ooh.payment_type_code = 'CREDIT_CARD'
149 AND ooh.ordered_date >= l_cutoff_date
150 AND NOT EXISTS ( SELECT 'Y'
151 FROM oe_payments op
152 WHERE op.header_id = ooh.header_id )
153 AND ita.authcode(+) = ooh.credit_card_approval_code
154 AND ita.updatedate(+) = ooh.credit_card_approval_date
155 AND ita.reqtype(+) = 'ORAPMTREQ'
156 AND ita.status(+) = 0
157 AND ita.refinfo(+) = to_char(ooh.header_id) --6713227
158 AND su.site_use_id = ooh.invoice_to_org_id
159 AND su.site_use_code = 'BILL_TO'
160 AND su.cust_acct_site_id = cas.cust_acct_site_id
161 AND su.org_id = cas.org_id
162 AND uba.cust_account_id = cas.cust_account_id
163 AND uba.acct_site_use_id = su.site_use_id
164 AND uba.payment_function = 'CUSTOMER_PAYMENT'
165 AND uba.instrument_type = 'CREDITCARD'
166 AND ba.bank_account_id = uba.bank_account_id
167 AND ba.bank_account_num = OE_Payment_Data_Migration_Util.Strip_Non_Numeric_Char(ooh.credit_card_number) --6757060
168 AND ba.bank_branch_id = 1
169 AND ba.account_type = 'EXTERNAL'
170 AND ba.bank_account_id = bau.external_bank_account_id
171 AND bau.customer_site_use_id = ooh.invoice_to_org_id
172 AND ooh.rowid BETWEEN p_start_rowid AND p_end_rowid ;
173
174 CURSOR payments_cur1_sec (p_start_rowid ROWID, p_end_rowid ROWID, l_cutoff_date DATE) is
175 SELECT /*+ LEADING (OOH) */ ooh.header_id,
176 null line_id, -- line_id
177 OE_Default_Header_Payment.get_payment_number(ooh.header_id),
178 ita.tangibleid,
179 uba.instr_assignment_id,
180 uba.ext_party_id,
181 iby_fndcpt_tx_extensions_s.nextval -- the new transaction extension ID
182 FROM oe_order_headers_all ooh,
183 hz_cust_site_uses_all su,
184 hz_cust_acct_sites_all cas,
185 iby_trans_all_v ita,
186 iby_upg_instruments uba,
187 iby_security_segments seg,
188 iby_creditcard cc
189 WHERE ooh.open_flag = 'N'
190 AND ooh.payment_type_code = 'CREDIT_CARD'
191 AND ooh.ordered_date >= l_cutoff_date
192 AND NOT EXISTS ( SELECT 'Y'
193 FROM oe_payments op
194 WHERE op.header_id = ooh.header_id )
195 AND ita.authcode(+) = ooh.credit_card_approval_code
196 AND ita.updatedate(+) = ooh.credit_card_approval_date
197 AND ita.reqtype(+) = 'ORAPMTREQ'
198 AND ita.status(+) = 0
199 AND ita.refinfo(+) = to_char(ooh.header_id) --6713227
200 AND su.site_use_id = ooh.invoice_to_org_id
201 AND su.site_use_code = 'BILL_TO'
202 AND su.cust_acct_site_id = cas.cust_acct_site_id
203 AND su.org_id = cas.org_id
204 AND uba.cust_account_id = cas.cust_account_id
205 AND uba.acct_site_use_id = su.site_use_id
206 AND uba.payment_function = 'CUSTOMER_PAYMENT'
207 AND uba.instrument_type = 'CREDITCARD'
208 AND IBY_CC_SECURITY_PUB.get_segment_id(OE_Payment_Data_Migration_Util.Strip_Non_Numeric_Char(ooh.credit_card_number))
209 = seg.sec_segment_id --6757060
210 AND (seg.cc_number_hash1 = cc.cc_number_hash1
211 AND seg.cc_number_hash2 = cc.cc_number_hash2)
212 AND cc.instrid = uba.instrument_id
213 AND ooh.rowid BETWEEN p_start_rowid AND p_end_rowid ;
214
215 -- Define another cursor payments_cur2 to select payment data that only exist in oe_payments
216 -- but not in oe_order_headers_all
217
218 CURSOR payments_cur2 (p_start_rowid ROWID, p_end_rowid ROWID, l_cutoff_date DATE) is
219 SELECT /*+ LEADING (OP) */ op.header_id,
220 op.line_id,
221 op.payment_number,
222 ita.tangibleid,
223 uba.instr_assignment_id,
224 uba.ext_party_id,
225 iby_fndcpt_tx_extensions_s.nextval -- the new transaction extension ID
226 FROM oe_order_headers_all ooh,
227 hz_cust_site_uses_all su,
228 hz_cust_acct_sites_all cas,
229 ap_bank_accounts_all ba,
230 ap_bank_account_uses_all bau,
231 iby_trans_all_v ita,
232 iby_upg_instruments uba,
233 oe_payments op
234 WHERE ooh.open_flag = 'N'
235 AND ooh.ordered_date >= l_cutoff_date
236 AND ooh.header_id = op.header_id
237 AND op.trxn_extension_id is null
238 AND ita.authcode(+) = op.credit_card_approval_code
239 AND ita.updatedate(+) = op.credit_card_approval_date
240 AND ita.reqtype(+) = 'ORAPMTREQ'
241 AND ita.status(+) = 0
242 AND ita.refinfo(+) = to_char(op.header_id) --6713227
243 AND su.site_use_id = ooh.invoice_to_org_id
244 AND su.site_use_code = 'BILL_TO'
245 AND su.cust_acct_site_id = cas.cust_acct_site_id
246 AND su.org_id = cas.org_id
247 AND uba.cust_account_id = cas.cust_account_id
248 AND uba.acct_site_use_id = su.site_use_id
249 AND uba.payment_function = 'CUSTOMER_PAYMENT'
250 AND uba.instrument_type = 'CREDITCARD'
251 AND op.payment_type_code = 'CREDIT_CARD'
252 AND op.credit_card_number IS NOT NULL
253 AND ba.bank_account_id = uba.bank_account_id
254 AND ba.bank_account_num = OE_Payment_Data_Migration_Util.Strip_Non_Numeric_Char(op.credit_card_number) --6757060
255 AND ba.bank_branch_id = 1
256 AND ba.account_type = 'EXTERNAL'
257 AND ba.bank_account_id = bau.external_bank_account_id
258 AND bau.customer_site_use_id = ooh.invoice_to_org_id
259 AND op.rowid BETWEEN p_start_rowid AND p_end_rowid ;
260
261 CURSOR payments_cur2_sec (p_start_rowid ROWID, p_end_rowid ROWID, l_cutoff_date DATE) is
262 SELECT /*+ LEADING (OP) */ op.header_id,
263 op.line_id,
264 op.payment_number,
265 ita.tangibleid,
266 uba.instr_assignment_id,
267 uba.ext_party_id,
268 iby_fndcpt_tx_extensions_s.nextval -- the new transaction extension ID
269 FROM oe_order_headers_all ooh,
270 hz_cust_site_uses_all su,
271 hz_cust_acct_sites_all cas,
272 iby_trans_all_v ita,
273 iby_upg_instruments uba,
274 oe_payments op,
275 iby_security_segments seg,
276 iby_creditcard cc
277 WHERE ooh.open_flag = 'N'
278 AND ooh.ordered_date >= l_cutoff_date
279 AND ooh.header_id = op.header_id
280 AND op.trxn_extension_id is null
281 AND ita.authcode(+) = op.credit_card_approval_code
282 AND ita.updatedate(+) = op.credit_card_approval_date
283 AND ita.reqtype(+) = 'ORAPMTREQ'
284 AND ita.status(+) = 0
285 AND ita.refinfo(+) = to_char(op.header_id) --6713227
286 AND su.site_use_id = ooh.invoice_to_org_id
287 AND su.site_use_code = 'BILL_TO'
288 AND su.cust_acct_site_id = cas.cust_acct_site_id
289 AND su.org_id = cas.org_id
290 AND uba.cust_account_id = cas.cust_account_id
291 AND uba.acct_site_use_id = su.site_use_id
292 AND uba.payment_function = 'CUSTOMER_PAYMENT'
293 AND uba.instrument_type = 'CREDITCARD'
294 AND op.payment_type_code = 'CREDIT_CARD'
295 AND op.credit_card_number IS NOT NULL
296 AND IBY_CC_SECURITY_PUB.get_segment_id(OE_Payment_Data_Migration_Util.Strip_Non_Numeric_Char(op.credit_card_number))
297 = seg.sec_segment_id --6757060
298 AND (seg.cc_number_hash1 = cc.cc_number_hash1
299 AND seg.cc_number_hash2 = cc.cc_number_hash2)
300 AND cc.instrid = uba.instrument_id
301 AND op.rowid BETWEEN p_start_rowid AND p_end_rowid ;
302
303 -- Define another cursor payments_cur3 to select payment data with ACH and Direct Debit payment types
304 -- that only exist in oe_payments but not in oe_order_headers_all, for these payment types,
305 -- we only need join with iby_upg_assignments directly to get the instrument assignment id and
306 -- then directly insert into iby_trxn_extensions table.
307
308 CURSOR payments_cur3 (p_start_rowid ROWID, p_end_rowid ROWID, l_cutoff_date DATE) is
309 SELECT /*+ LEADING (OP) */ op.header_id,
310 op.line_id,
311 op.payment_number,
312 uba.instr_assignment_id,
313 uba.ext_party_id,
314 iby_fndcpt_tx_extensions_s.nextval -- the new transaction extension ID
315 FROM oe_order_headers_all ooh,
316 hz_cust_site_uses_all su,
317 hz_cust_acct_sites_all cas,
318 iby_upg_instruments uba,
319 oe_payments op
320 WHERE ooh.open_flag = 'N'
321 AND ooh.ordered_date >= l_cutoff_date
322 AND ooh.header_id = op.header_id
323 AND op.trxn_extension_id is null
324 AND op.payment_type_code IN ('DIRECT_DEBIT', 'ACH')
325 AND su.site_use_id = ooh.invoice_to_org_id
326 AND su.site_use_code = 'BILL_TO'
327 AND su.cust_acct_site_id = cas.cust_acct_site_id
328 AND su.org_id = cas.org_id
329 AND uba.cust_account_id = cas.cust_account_id
330 AND uba.acct_site_use_id = su.site_use_id
331 AND uba.payment_function = 'CUSTOMER_PAYMENT'
332 AND op.payment_trx_id = uba.bank_account_id
333 AND uba.instrument_type = 'BANKACCOUNT'
334 AND op.rowid BETWEEN p_start_rowid AND p_end_rowid ;
335
336 BEGIN
337 --
338 -- get schema name of the table for ROWID range processing
339 --
340 l_retstatus := fnd_installation.get_app_info(l_product, l_status, l_industry, l_table_owner) ;
341
342 IF ((l_retstatus = FALSE)
343 OR
344 (l_table_owner is null))
345 THEN
346 raise_application_error(-20001,
347 'Cannot get schema name for product : ' || l_product) ;
348 END IF ;
349
350 -----------------------------------------------------------
351 -- Log Output file
352 -----------------------------------------------------------
353 fnd_file.put_line(FND_FILE.OUTPUT, '');
354 fnd_file.put_line(FND_FILE.OUTPUT, 'Migrate Payment Data for Closed Orders to Oracle Payment - Concurrent Program');
355 fnd_file.put_line(FND_FILE.OUTPUT, '');
356 fnd_file.put_line(FND_FILE.OUTPUT, 'Concurrent Program Parameters');
357 fnd_file.put_line(FND_FILE.OUTPUT, 'Batch Size : '|| X_batch_size);
358 fnd_file.put_line(FND_FILE.OUTPUT, 'Number of Threads : '|| X_Num_Workers);
359
360 l_cutoff_date := NVL(fnd_profile.value('IBY_CREDITCARD_CUTOFF_DATE'), to_date('01/01/1000', 'DD/MM/YYYY')) ;
361
362 -- migrate data in oe_oe_order_headers_all and not exist in oe_payments
363 BEGIN
364 -----------------------------------------------------------
365 -- Fetching records from OE_ORDER_HEADERS_ALL table
366 -----------------------------------------------------------
367 l_table_name := 'OE_ORDER_HEADERS_ALL' ;
368
369 ad_parallel_updates_pkg.delete_update_information(
370 0,
371 l_table_owner,
372 l_table_name,
373 l_script_name ) ;
374
375 ad_parallel_updates_pkg.initialize_rowid_range(
376 ad_parallel_updates_pkg.ROWID_RANGE,
377 l_table_owner,
378 l_table_name,
379 l_script_name,
380 X_worker_id,
381 X_num_workers,
382 X_batch_size, 0) ;
383
384 ad_parallel_updates_pkg.get_rowid_range(
385 l_start_rowid,
386 l_end_rowid,
387 l_any_rows_to_process,
388 X_batch_size,
389 TRUE) ;
390
391 fnd_file.put_line(FND_FILE.OUTPUT, '');
392 fnd_file.put_line(FND_FILE.OUTPUT, 'Process starting from OE_ORDER_HEADERS_ALL table');
393
394 IF l_debug_level > 0 THEN
395 oe_debug_pub.add('') ;
396 oe_debug_pub.add('AD parallel details : ') ;
397 oe_debug_pub.add('') ;
398 oe_debug_pub.add('Table owner : ' || l_table_owner) ;
399 oe_debug_pub.add('Table name : ' || l_table_name) ;
400 oe_debug_pub.add('Batch Size : ' || X_batch_size) ;
401 oe_debug_pub.add('Worker ID : ' || X_worker_id) ;
402 oe_debug_pub.add('No of Worker : ' || X_num_workers) ;
403 oe_debug_pub.add('Cut off Date : ' || l_cutoff_date) ;
404 END IF ;
405
406 l_process_total1 := 0 ;
407
408 l_error_total := 0 ;
409
410 WHILE (l_any_rows_to_process = TRUE) LOOP
411 header_id_tab.delete ;
412 line_id_tab.delete ;
413 payment_number_tab.delete ;
414 tangibleid_tab.delete ;
415 instr_assignment_id_tab.delete ;
416 ext_party_id_tab.delete ;
417 trxn_entity_id_tab.delete ;
418
419 BEGIN --6757060
420 IF NOT iby_cc_security_pub.encryption_enabled() THEN
421 OPEN payments_cur1(l_start_rowid, l_end_rowid, l_cutoff_date) ;
422
423 FETCH payments_cur1 BULK COLLECT INTO
424 header_id_tab,
425 line_id_tab,
426 payment_number_tab,
427 tangibleid_tab,
428 instr_assignment_id_tab,
429 ext_party_id_tab,
430 trxn_entity_id_tab; --6757060
431 --6757060 Limit X_batch_size ;
432
433 CLOSE payments_cur1 ;
434 ELSE
435 OPEN payments_cur1_sec(l_start_rowid, l_end_rowid, l_cutoff_date) ;
436
437 FETCH payments_cur1_sec BULK COLLECT INTO
438 header_id_tab,
439 line_id_tab,
440 payment_number_tab,
441 tangibleid_tab,
442 instr_assignment_id_tab,
443 ext_party_id_tab,
444 trxn_entity_id_tab; --6757060
445 --6757060 Limit X_batch_size ;
446
447 CLOSE payments_cur1_sec ;
448 END IF ;
449
450 -- oe_debug_pub.add('Number of Records selected in payments_cur1 : ' || trxn_entity_id_tab.count) ;
451
452 IF trxn_entity_id_tab.count > 0 THEN
453 -- insert the transactions into IBY transaction extension table
454 FORALL k in trxn_entity_id_tab.FIRST..trxn_entity_id_tab.LAST SAVE EXCEPTIONS
455 INSERT INTO IBY_FNDCPT_TX_EXTENSIONS
456 (trxn_extension_id,
457 payment_channel_code,
458 instr_assignment_id,
459 ext_payer_id,
460 order_id,
461 po_number,
462 trxn_ref_number1,
463 trxn_ref_number2,
464 additional_info,
465 tangibleid,
466 origin_application_id,
467 encrypted,
468 created_by,
469 creation_date,
470 last_updated_by,
471 last_update_date,
472 last_update_login,
473 object_version_number)
474 VALUES
475 (trxn_entity_id_tab(k),
476 'CREDIT_CARD',
477 instr_assignment_id_tab(k),
478 ext_party_id_tab(k),
479 header_id_tab(k),
480 NULL,
481 line_id_tab(k),
482 payment_number_tab(k),
483 NULL,
484 tangibleid_tab(k),
485 660,
486 'N',
487 l_user_id,
488 sysdate,
489 l_user_id,
490 sysdate,
491 l_user_id,
492 1) ;
493
494 FORALL i in trxn_entity_id_tab.FIRST..trxn_entity_id_tab.LAST SAVE EXCEPTIONS
495 INSERT INTO OE_PAYMENTS
496 (trxn_extension_id,
497 payment_level_code,
498 payment_number,
499 header_id,
500 line_id,
501 payment_type_code,
502 payment_collection_event, --6700026
503 creation_date,
504 created_by,
505 last_update_date,
506 last_updated_by
507 )
508 VALUES
509 (trxn_entity_id_tab(i),
510 'ORDER',
511 payment_number_tab(i),
512 header_id_tab(i),
513 line_id_tab(i),
514 'CREDIT_CARD',
515 'INVOICE', --6700026
516 sysdate,
517 1,
518 sysdate,
519 1) ;
520
521 l_rows_processed := SQL%ROWCOUNT ;
522
523 l_process_total1 := l_process_total1 + l_rows_processed ;
524 END IF ;
525
526 --6757060
527 EXCEPTION
528 WHEN OTHERS THEN
529 IF payments_cur1%ISOPEN THEN
530 CLOSE payments_cur1 ;
531 END IF;
532
533 IF payments_cur1_sec%ISOPEN THEN
534 CLOSE payments_cur1_sec ;
535 END IF;
536 l_error_total := SQL%BULK_EXCEPTIONS.COUNT ;
537
538 FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
539 fnd_file.put_line(FND_FILE.OUTPUT,
540 'Inner Block :Error occurred during iteration ' ||
541 SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
542 ' Oracle error is ' ||
543 SQL%BULK_EXCEPTIONS(j).ERROR_CODE );
544
545 fnd_file.put_line(FND_FILE.OUTPUT, 'Inner Block :upgrading failing for data from oe_order_headers_all for Header ID ' || header_id_tab(j));
546 END LOOP;
547 END ;
548 --6757060
549
550 ad_parallel_updates_pkg.processed_rowid_range
551 (l_rows_processed,
552 l_end_rowid) ;
553
554 COMMIT ;
555
556 ad_parallel_updates_pkg.get_rowid_range
557 (l_start_rowid,
558 l_end_rowid,
559 l_any_rows_to_process,
560 X_batch_size,
561 FALSE) ;
562 END LOOP ;
563
564 IF l_debug_level > 0 THEN
565 oe_debug_pub.add('Total No of records processed successfully : ' || l_process_total1) ;
566 END IF;
567
568 fnd_file.put_line(FND_FILE.OUTPUT, 'Process ending from OE_ORDER_HEADERS_ALL table');
569 EXCEPTION
570 WHEN NO_DATA_FOUND THEN
571
572 IF l_debug_level > 0 THEN
573 oe_debug_pub.add('') ;
574 oe_debug_pub.add('No record found from OE_ORDER_HEADERS_ALL table for Worker Id : ' || X_worker_id) ;
575 END IF;
576
577 fnd_file.put_line(FND_FILE.OUTPUT, 'No record found from OE_ORDER_HEADERS_ALL table for Worker Id : ' || X_worker_id) ;
578 WHEN OTHERS THEN
579 l_error_total := SQL%BULK_EXCEPTIONS.COUNT ;
580
581 FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
582 fnd_file.put_line(FND_FILE.OUTPUT,
583 'Error occurred during iteration ' ||
584 SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
585 ' Oracle error is ' ||
586 SQL%BULK_EXCEPTIONS(j).ERROR_CODE );
587
588 fnd_file.put_line(FND_FILE.OUTPUT,
589 'upgrading failing for data from oe_order_headers_all for Header ID ' ||
590 header_id_tab(j));
591 END LOOP;
592 END ;
593
594 IF l_debug_level > 0 THEN
595 oe_debug_pub.add('Total No of records errored in oe_order_headers_all : ' || l_error_total) ;
596 END IF;
597
598 -- Migrate data in oe_payments for Credit Card payment types.
599 BEGIN
600 -----------------------------------------------------------
601 -- Fetching records from OE_PAYMENTS table
602 -----------------------------------------------------------
603 l_table_name := 'OE_PAYMENTS' ;
604
605 ad_parallel_updates_pkg.delete_update_information(
606 0,
607 l_table_owner,
608 l_table_name,
609 l_script_name ) ;
610
611 ad_parallel_updates_pkg.initialize_rowid_range(
612 ad_parallel_updates_pkg.ROWID_RANGE,
613 l_table_owner,
614 l_table_name,
615 l_script_name,
616 X_worker_id,
617 X_num_workers,
618 X_batch_size, 0) ;
619
620 ad_parallel_updates_pkg.get_rowid_range(
621 l_start_rowid,
622 l_end_rowid,
623 l_any_rows_to_process,
624 X_batch_size,
625 TRUE) ;
626
627 fnd_file.put_line(FND_FILE.OUTPUT, '');
628 fnd_file.put_line(FND_FILE.OUTPUT, 'Process starting from OE_PAYMENTS table');
629
630 IF l_debug_level > 0 THEN
631 oe_debug_pub.add('') ;
632 oe_debug_pub.add('AD parallel details : ') ;
633 oe_debug_pub.add('') ;
634 oe_debug_pub.add('Table owner : ' || l_table_owner) ;
635 oe_debug_pub.add('Table name : ' || l_table_name) ;
636 oe_debug_pub.add('Batch Size : ' || X_batch_size) ;
637 oe_debug_pub.add('Worker ID : ' || X_worker_id) ;
638 oe_debug_pub.add('No of Worker : ' || X_num_workers) ;
639 oe_debug_pub.add('Cut off Date : ' || l_cutoff_date) ;
640 END IF ;
641
642 l_process_total1 := 0 ;
643
644 l_error_total := 0 ;
645
646 WHILE (l_any_rows_to_process = TRUE) LOOP
647 header_id_tab.delete ;
648 line_id_tab.delete ;
649 payment_number_tab.delete ;
650 tangibleid_tab.delete ;
651 instr_assignment_id_tab.delete ;
652 ext_party_id_tab.delete ;
653 trxn_entity_id_tab.delete ;
654
655 IF NOT iby_cc_security_pub.encryption_enabled() THEN
656 OPEN payments_cur2(l_start_rowid, l_end_rowid, l_cutoff_date) ;
657
658 FETCH payments_cur2 BULK COLLECT INTO
659 header_id_tab,
660 line_id_tab,
661 payment_number_tab,
662 tangibleid_tab,
663 instr_assignment_id_tab,
664 ext_party_id_tab,
665 trxn_entity_id_tab
666 Limit X_batch_size ;
667
668 CLOSE payments_cur2 ;
669 ELSE
670 OPEN payments_cur2_sec(l_start_rowid, l_end_rowid, l_cutoff_date) ;
671
672 FETCH payments_cur2_sec BULK COLLECT INTO
673 header_id_tab,
674 line_id_tab,
675 payment_number_tab,
676 tangibleid_tab,
677 instr_assignment_id_tab,
678 ext_party_id_tab,
679 trxn_entity_id_tab
680 Limit X_batch_size ;
681
682 CLOSE payments_cur2_sec ;
683 END IF ;
684
685 -- oe_debug_pub.add('Number of Records selected in payments_cur2 : ' || trxn_entity_id_tab.count) ;
686
687 IF trxn_entity_id_tab.count > 0 THEN
688 -- insert the transactions into IBY transaction extension table
689 FORALL k in trxn_entity_id_tab.FIRST..trxn_entity_id_tab.LAST SAVE EXCEPTIONS
690 INSERT INTO IBY_FNDCPT_TX_EXTENSIONS
691 (trxn_extension_id,
692 payment_channel_code,
693 instr_assignment_id,
694 ext_payer_id,
695 order_id,
696 po_number,
697 trxn_ref_number1,
698 trxn_ref_number2,
699 additional_info,
700 tangibleid,
701 origin_application_id,
702 encrypted,
703 created_by,
704 creation_date,
705 last_updated_by,
706 last_update_date,
707 last_update_login,
708 object_version_number)
709 VALUES
710 (trxn_entity_id_tab(k),
711 'CREDIT_CARD',
712 instr_assignment_id_tab(k),
713 ext_party_id_tab(k),
714 header_id_tab(k),
715 NULL,
716 line_id_tab(k),
717 payment_number_tab(k),
718 NULL,
719 tangibleid_tab(k),
720 660,
721 'N',
722 l_user_id,
723 sysdate,
724 l_user_id,
725 sysdate,
726 l_user_id,
727 1) ;
728
729 FORALL i in trxn_entity_id_tab.FIRST..trxn_entity_id_tab.LAST SAVE EXCEPTIONS
730 UPDATE OE_PAYMENTS
731 SET trxn_extension_id = trxn_entity_id_tab(i),
732 last_update_date = sysdate,
733 last_updated_by = l_user_id,
734 last_update_login = l_user_id
735 WHERE header_id = header_id_tab(i)
736 AND NVL(line_id,-99) = NVL(line_id_tab(i),-99)
737 AND payment_number = payment_number_tab(i) ;
738
739 l_rows_processed := SQL%ROWCOUNT ;
740
741 l_process_total1 := l_process_total1 + l_rows_processed ;
742 END IF ;
743
744 ad_parallel_updates_pkg.processed_rowid_range
745 (l_rows_processed,
746 l_end_rowid) ;
747
748 COMMIT ;
749
750 ad_parallel_updates_pkg.get_rowid_range
751 (l_start_rowid,
752 l_end_rowid,
753 l_any_rows_to_process,
754 X_batch_size,
755 FALSE) ;
756 END LOOP ;
757
758 IF l_debug_level > 0 THEN
759 oe_debug_pub.add('Total No of records processed successfully : ' || l_process_total1) ;
760 END IF;
761
762 fnd_file.put_line(FND_FILE.OUTPUT, 'Process ending from OE_PAYMENTS table');
763 EXCEPTION
764 WHEN NO_DATA_FOUND THEN
765 IF l_debug_level > 0 THEN
766 oe_debug_pub.add('') ;
767 oe_debug_pub.add('No record found from OE_PAYMENTS table for Worker Id : ' || X_worker_id) ;
768 END IF;
769
770 fnd_file.put_line(FND_FILE.OUTPUT, 'No record found from OE_PAYMENTS table for Worker Id : ' || X_worker_id) ;
771 WHEN OTHERS THEN
772 l_error_total := SQL%BULK_EXCEPTIONS.COUNT ;
773
774 FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
775 fnd_file.put_line(FND_FILE.OUTPUT,
776 'Error occurred during iteration ' ||
777 SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
778 ' Oracle error is ' ||
779 SQL%BULK_EXCEPTIONS(j).ERROR_CODE );
780
781 fnd_file.put_line(FND_FILE.OUTPUT,
782 'upgrade failing in oe_payments for credit card for Header ID ' ||
783 header_id_tab(j));
784 END LOOP;
785 END ;
786
787 IF l_debug_level > 0 THEN
788 oe_debug_pub.add('Total No of records errored in oe_payments : ' || l_error_total) ;
789 END IF;
790
791 -- migrate data in oe_payments for ACH and Direct Debit payment types.
792 BEGIN
793 -----------------------------------------------------------
794 -- Fetching records from OE_PAYMENTS table
795 -----------------------------------------------------------
796 l_table_name := 'OE_PAYMENTS' ;
797
798 ad_parallel_updates_pkg.delete_update_information(
799 0,
800 l_table_owner,
801 l_table_name,
802 l_script_name ) ;
803
804 ad_parallel_updates_pkg.initialize_rowid_range(
805 ad_parallel_updates_pkg.ROWID_RANGE,
806 l_table_owner,
807 l_table_name,
808 l_script_name,
809 X_worker_id,
810 X_num_workers,
811 X_batch_size, 0) ;
812
813 ad_parallel_updates_pkg.get_rowid_range(
814 l_start_rowid,
815 l_end_rowid,
816 l_any_rows_to_process,
817 X_batch_size,
818 TRUE) ;
819
820 fnd_file.put_line(FND_FILE.OUTPUT, '');
821 fnd_file.put_line(FND_FILE.OUTPUT, 'Process starting from OE_PAYMENTS table');
822
823 IF l_debug_level > 0 THEN
824 oe_debug_pub.add('') ;
825 oe_debug_pub.add('AD parallel details : ') ;
826 oe_debug_pub.add('') ;
827 oe_debug_pub.add('Table owner : ' || l_table_owner) ;
828 oe_debug_pub.add('Table name : ' || l_table_name) ;
829 oe_debug_pub.add('Batch Size : ' || X_batch_size) ;
830 oe_debug_pub.add('Worker ID : ' || X_worker_id) ;
831 oe_debug_pub.add('No of Worker : ' || X_num_workers) ;
832 oe_debug_pub.add('Cut off Date : ' || l_cutoff_date) ;
833 END IF ;
834
835 l_process_total1 := 0 ;
836
837 l_error_total := 0 ;
838
839 WHILE (l_any_rows_to_process = TRUE) LOOP
840 header_id_tab.delete ;
841 line_id_tab.delete ;
842 payment_number_tab.delete ;
843 instr_assignment_id_tab.delete ;
844 ext_party_id_tab.delete ;
845 trxn_entity_id_tab.delete ;
846
847 OPEN payments_cur3(l_start_rowid, l_end_rowid, l_cutoff_date) ;
848
849 FETCH payments_cur3 BULK COLLECT INTO
850 header_id_tab,
851 line_id_tab,
852 payment_number_tab,
853 instr_assignment_id_tab,
854 ext_party_id_tab,
855 trxn_entity_id_tab
856 Limit X_batch_size ;
857
858 CLOSE payments_cur3 ;
859
860 -- oe_debug_pub.add('Number of Records selected in payments_cur3 is : ' || trxn_entity_id_tab.count) ;
861
862 IF trxn_entity_id_tab.count > 0 THEN
863 -- insert the transactions into IBY transaction extension table
864 FORALL k in trxn_entity_id_tab.FIRST..trxn_entity_id_tab.LAST SAVE EXCEPTIONS
865 INSERT INTO IBY_FNDCPT_TX_EXTENSIONS
866 (trxn_extension_id,
867 payment_channel_code,
868 instr_assignment_id,
869 ext_payer_id,
870 order_id,
871 po_number,
872 trxn_ref_number1,
873 trxn_ref_number2,
874 additional_info,
875 origin_application_id,
876 encrypted,
877 created_by,
878 creation_date,
879 last_updated_by,
880 last_update_date,
881 last_update_login,
882 object_version_number)
883 VALUES
884 (trxn_entity_id_tab(k),
885 'BANK_ACCT_XFER',
886 instr_assignment_id_tab(k),
887 ext_party_id_tab(k),
888 header_id_tab(k),
889 NULL,
890 line_id_tab(k),
891 payment_number_tab(k),
892 NULL,
893 660,
894 'N',
895 l_user_id,
896 sysdate,
897 l_user_id,
898 sysdate,
899 l_user_id,
900 1) ;
901
902 FORALL i in trxn_entity_id_tab.FIRST..trxn_entity_id_tab.LAST SAVE EXCEPTIONS
903 UPDATE OE_PAYMENTS
904 SET trxn_extension_id = trxn_entity_id_tab(i),
905 last_update_date = sysdate,
906 last_updated_by = l_user_id,
907 last_update_login = l_user_id
908 WHERE header_id = header_id_tab(i)
909 AND NVL(line_id,-99) = NVL(line_id_tab(i),-99)
910 AND payment_number = payment_number_tab(i) ;
911
912 l_rows_processed := SQL%ROWCOUNT ;
913
914 l_process_total1 := l_process_total1 + l_rows_processed ;
915 END IF ;
916
917 ad_parallel_updates_pkg.processed_rowid_range
918 (l_rows_processed,
919 l_end_rowid) ;
920
921 COMMIT ;
922
923 ad_parallel_updates_pkg.get_rowid_range
924 (l_start_rowid,
925 l_end_rowid,
926 l_any_rows_to_process,
927 X_batch_size,
928 FALSE) ;
929 END LOOP ;
930
931 IF l_debug_level > 0 THEN
932 oe_debug_pub.add('Total No of records processed successfully : ' || l_process_total1) ;
933 END IF;
934
935 fnd_file.put_line(FND_FILE.OUTPUT, 'Process ending from OE_PAYMENTS table');
936
937 EXCEPTION
938 WHEN NO_DATA_FOUND THEN
939 IF l_debug_level > 0 THEN
940 oe_debug_pub.add('') ;
941 oe_debug_pub.add('No record found from OE_PAYMENTS table for Worker Id : ' || X_worker_id) ;
942 END IF;
943
944 fnd_file.put_line(FND_FILE.OUTPUT, 'No record found from OE_PAYMENTS table for Worker Id : ' || X_worker_id) ;
945 WHEN OTHERS THEN
946 l_error_total := SQL%BULK_EXCEPTIONS.COUNT ;
947
948 FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
949 fnd_file.put_line(FND_FILE.OUTPUT,
950 'Error occurred during iteration ' ||
951 SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
952 ' Oracle error is ' ||
953 SQL%BULK_EXCEPTIONS(j).ERROR_CODE );
954
955 fnd_file.put_line(FND_FILE.OUTPUT,
956 'upgrade failing in oe_payments for credit card for Header ID ' || header_id_tab(j));
957 END LOOP;
958 END ;
959
960 IF l_debug_level > 0 THEN
961 oe_debug_pub.add('Total No of records errored in oe_payments : ' || l_error_total) ;
962 END IF;
963
964 COMMIT ;
965
966 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
967
968 EXCEPTION
969 WHEN FND_API.G_EXC_ERROR THEN
970 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL ;
971
972 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
973 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Migrate_Data_WKR');
974 END IF;
975
976 FND_FILE.PUT_LINE(FND_FILE.LOG,'Concurrent Request Error : '||substr(sqlerrm,1,200));
977 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
978 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL ;
979
980 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
981 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Migrate_Data_WKR');
982 END IF;
983
984 FND_FILE.PUT_LINE(FND_FILE.LOG,'Concurrent Request Error : '||substr(sqlerrm,1,200));
985 WHEN OTHERS THEN
986 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL ;
987
988 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
989 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Migrate_Data_WKR');
990 END IF;
991
992 FND_FILE.PUT_LINE(FND_FILE.LOG,'Concurrent Request Error : '||substr(sqlerrm,1,200));
993 END Migrate_Data_WKR ;
994
995 PROCEDURE Purge_Data_MGR
996 ( X_errbuf OUT NOCOPY VARCHAR2,
997 X_retcode OUT NOCOPY VARCHAR2,
998 X_batch_size IN NUMBER,
999 X_Num_Workers IN NUMBER
1000 )
1001 IS
1002 l_product VARCHAR2(30) := 'ONT' ;
1003 BEGIN
1004 AD_CONC_UTILS_PKG.submit_subrequests(
1005 X_errbuf => X_errbuf,
1006 X_retcode => X_retcode,
1007 X_WorkerConc_app_shortname => l_product,
1008 X_workerConc_progname => 'ONTIBYCN_WKR',
1009 X_batch_size => X_batch_size,
1010 X_Num_Workers => X_Num_Workers) ;
1011 END Purge_Data_MGR ;
1012
1013 PROCEDURE Purge_Data_WKR
1014 ( X_errbuf OUT NOCOPY VARCHAR2,
1015 X_retcode OUT NOCOPY VARCHAR2,
1016 X_batch_size IN NUMBER,
1017 X_Worker_Id IN NUMBER,
1018 X_Num_Workers IN NUMBER
1019 )
1020 IS
1021 TYPE HEADER_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
1022 TYPE ROW_ID IS TABLE OF ROWID INDEX BY BINARY_INTEGER ;
1023
1024 header_id_tab header_id ;
1025 row_id_tab row_id ;
1026
1027 l_table_owner VARCHAR2(30) ;
1028 l_batch_size VARCHAR2(30) ;
1029 l_worker_id NUMBER ;
1030 l_num_workers NUMBER ;
1031 l_any_rows_to_process BOOLEAN ;
1032
1033 l_table_name VARCHAR2(30) ;
1034 l_product VARCHAR2(30) := 'ONT' ;
1035 l_script_name VARCHAR2(30) := 'OEXUPDMB.pls' ;
1036
1037 l_start_rowid ROWID ;
1038 l_end_rowid ROWID ;
1039 l_cutoff_date DATE;
1040 l_rows_processed NUMBER ;
1041 l_user_id NUMBER := NVL(fnd_global.user_id, -1) ;
1042
1043 l_status VARCHAR2(30) ;
1044 l_industry VARCHAR2(30) ;
1045 l_retstatus BOOLEAN ;
1046
1047 l_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS ;
1048 l_msg_count NUMBER := 0 ;
1049 l_msg_data VARCHAR2(2000) ;
1050
1051 l_exists_header VARCHAR2(1) := 'N' ;
1052 l_exists_payment VARCHAR2(1) := 'N' ;
1053 l_exists_history VARCHAR2(1) := 'N' ;
1054
1055 l_error_total1 NUMBER := 0 ;
1056
1057 l_process_total1 NUMBER := 0 ;
1058
1059 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level ;
1060
1061 -- Define a cursor oe_payments_cur to select header_id from oe_payments where
1062 -- payment type code in ('CREDIT_CARD', 'ACH', 'DIRECT_DEBIT'), for the given IN parameters
1063
1064 CURSOR oe_payments_cur (p_start_rowid ROWID, p_end_rowid ROWID) is
1065 SELECT /*+ LEADING (OP) */ op.header_id,
1066 op.rowid
1067 FROM oe_payments op
1068 WHERE ((op.payment_type_code = 'CREDIT_CARD' AND op.credit_card_number IS NOT NULL)
1069 OR (op.payment_type_code IN ('ACH', 'DIRECT_DEBIT') AND op.payment_trx_id IS NOT NULL))
1070 AND op.trxn_extension_id IS NOT NULL
1071 AND op.rowid BETWEEN p_start_rowid AND p_end_rowid ;
1072
1073 -- Define another cursor header_payments_cur to select header_id from oe_order_headers_all,
1074 -- where payment type = 'CREDIT_CARD' for the given IN parameters
1075
1076 CURSOR header_payments_cur (p_start_rowid ROWID, p_end_rowid ROWID) is
1077 SELECT /*+ LEADING (OOH) */ ooh.header_id,
1078 ooh.rowid
1079 FROM oe_order_headers_all ooh
1080 WHERE (ooh.payment_type_code = 'CREDIT_CARD' AND ooh.credit_card_number IS NOT NULL)
1081 AND ooh.rowid BETWEEN p_start_rowid AND p_end_rowid ;
1082
1083 -- cursor to select from oe_order_header_history
1084 CURSOR hist_payments_cur (p_start_rowid ROWID, p_end_rowid ROWID) is
1085 SELECT oohh.header_id, oohh.rowid
1086 FROM oe_order_header_history oohh
1087 WHERE oohh.payment_type_code = 'CREDIT_CARD'
1088 AND oohh.credit_card_number is not null
1089 AND oohh.credit_card_number <> '****'
1090 AND oohh.instrument_id is not null
1091 AND oohh.rowid BETWEEN p_start_rowid AND p_end_rowid;
1092
1093 BEGIN
1094 --
1095 -- get schema name of the table for ROWID range processing
1096 --
1097 l_retstatus := fnd_installation.get_app_info(l_product, l_status, l_industry, l_table_owner) ;
1098
1099 IF ((l_retstatus = FALSE)
1100 OR
1101 (l_table_owner is null))
1102 THEN
1103 raise_application_error(-20001,
1104 'Cannot get schema name for product : ' || l_product) ;
1105 END IF ;
1106
1107 -----------------------------------------------------------
1108 -- Log Output file
1109 -----------------------------------------------------------
1110 fnd_file.put_line(FND_FILE.OUTPUT, '');
1111 fnd_file.put_line(FND_FILE.OUTPUT, 'Purge Secured Payment Data - Concurrent Program');
1112 fnd_file.put_line(FND_FILE.OUTPUT, '');
1113 fnd_file.put_line(FND_FILE.OUTPUT, 'Concurrent Program Parameters');
1114 fnd_file.put_line(FND_FILE.OUTPUT, 'Batch Size : '|| X_batch_size);
1115 fnd_file.put_line(FND_FILE.OUTPUT, 'Number of Threads : '|| X_Num_Workers);
1116
1117 BEGIN
1118 -----------------------------------------------------------
1119 -- Fetching records from OE_PAYMENTS table
1120 -----------------------------------------------------------
1121 l_table_name := 'OE_PAYMENTS' ;
1122
1123 ad_parallel_updates_pkg.delete_update_information(
1124 0,
1125 l_table_owner,
1126 l_table_name,
1127 l_script_name ) ;
1128
1129 ad_parallel_updates_pkg.initialize_rowid_range(
1130 ad_parallel_updates_pkg.ROWID_RANGE,
1131 l_table_owner,
1132 l_table_name,
1133 l_script_name,
1134 X_worker_id,
1135 X_num_workers,
1136 X_batch_size, 0) ;
1137
1138 ad_parallel_updates_pkg.get_rowid_range(
1139 l_start_rowid,
1140 l_end_rowid,
1141 l_any_rows_to_process,
1142 X_batch_size,
1143 TRUE) ;
1144
1145 BEGIN
1146 SELECT 'Y'
1147 INTO l_exists_payment
1148 FROM oe_payments
1149 WHERE ((payment_type_code = 'CREDIT_CARD' AND credit_card_number IS NOT NULL)
1150 OR (payment_type_code IN ('ACH', 'DIRECT_DEBIT') AND payment_trx_id IS NOT NULL))
1151 AND trxn_extension_id IS NULL
1152 AND ROWNUM = 1 ;
1153 EXCEPTION
1154 WHEN NO_DATA_FOUND THEN
1155 IF l_debug_level > 0 THEN
1156 oe_debug_pub.add('') ;
1157 oe_debug_pub.add('No record found from OE_PAYMENTS table for Worker Id : ' || X_worker_id) ;
1158 END IF;
1159
1160 fnd_file.put_line(FND_FILE.OUTPUT, 'No record found from OE_PAYMENTS table for Worker Id : ' || X_worker_id) ;
1161 END;
1162
1163 BEGIN
1164 SELECT 'Y'
1165 INTO l_exists_header
1166 FROM oe_order_headers_all ooh,
1167 oe_payments op
1168 WHERE op.payment_type_code = 'CREDIT_CARD'
1169 AND op.credit_card_number IS NOT NULL
1170 AND ooh.header_id = op.header_id
1171 AND op.trxn_extension_id IS NULL
1172 AND ROWNUM = 1 ;
1173 EXCEPTION
1174 WHEN NO_DATA_FOUND THEN
1175 IF l_debug_level > 0 THEN
1176 oe_debug_pub.add('') ;
1177 oe_debug_pub.add('No record found from OE_ORDER_HEADERS_ALL table for Worker Id : ' || X_worker_id) ;
1178 END IF;
1179
1180 fnd_file.put_line(FND_FILE.OUTPUT, 'No record found from OE_ORDER_HEADERS_ALL table for Worker Id : ' || X_worker_id) ;
1181 END;
1182
1183 BEGIN
1184 SELECT 'Y'
1185 INTO l_exists_history
1186 FROM oe_order_header_history
1187 WHERE payment_type_code = 'CREDIT_CARD'
1188 AND instrument_id IS NULL
1189 AND ROWNUM = 1 ;
1190 EXCEPTION
1191 WHEN NO_DATA_FOUND THEN
1192 IF l_debug_level > 0 THEN
1193 oe_debug_pub.add('') ;
1194 oe_debug_pub.add('No record found from OE_ORDER_HEADER_HISTORY table for Worker Id : ' || X_worker_id) ;
1195 END IF;
1196
1197 fnd_file.put_line(FND_FILE.OUTPUT, 'No record found from OE_ORDER_HEADER_HISTORY table for Worker Id : ' || X_worker_id) ;
1198 END;
1199
1200 IF l_exists_header = 'Y' OR l_exists_payment = 'Y' OR l_exists_history = 'Y' THEN
1201 IF l_debug_level > 0 THEN
1202 oe_debug_pub.add('') ;
1203 oe_debug_pub.add('Data NOT migrated from OE_ORDER_HEADERS_ALL table (Y/N) : ' || l_exists_header) ;
1204 oe_debug_pub.add('Data NOT migrated from OE_PAYMENTS table (Y/N) : ' || l_exists_payment) ;
1205 oe_debug_pub.add('Data NOT migrated from OE_ORDER_HEADER_HISTORY table (Y/N) : ' || l_exists_history) ;
1206 oe_debug_pub.add('Please migrate the data before proceeding to purge secured payment data') ;
1207 END IF;
1208
1209 fnd_file.put_line(FND_FILE.OUTPUT, 'Data NOT migrated from OE_ORDER_HEADERS_ALL table (Y/N) : ' || l_exists_header) ;
1210 fnd_file.put_line(FND_FILE.OUTPUT, 'Data NOT migrated from OE_PAYMENTS table (Y/N) : ' || l_exists_payment) ;
1211 fnd_file.put_line(FND_FILE.OUTPUT, 'Data NOT migrated from OE_ORDER_HEADER_HISTORY table (Y/N) : ' || l_exists_history) ;
1212 fnd_file.put_line(FND_FILE.OUTPUT, 'Please migrate the data before proceeding to purge secured payment data') ;
1213
1214 RETURN ;
1215 END IF ;
1216
1217 fnd_file.put_line(FND_FILE.OUTPUT, '');
1218 fnd_file.put_line(FND_FILE.OUTPUT, 'Process starting from OE_PAYMENTS table');
1219
1220 IF l_debug_level > 0 THEN
1221 oe_debug_pub.add('') ;
1222 oe_debug_pub.add('AD parallel details : ') ;
1223 oe_debug_pub.add('') ;
1224 oe_debug_pub.add('Table owner : ' || l_table_owner) ;
1225 oe_debug_pub.add('Table name : ' || l_table_name) ;
1226 oe_debug_pub.add('Batch Size : ' || X_batch_size) ;
1227 oe_debug_pub.add('Worker ID : ' || X_worker_id) ;
1228 oe_debug_pub.add('No of Worker : ' || X_num_workers) ;
1229 END IF ;
1230
1231 l_error_total1 := 0 ;
1232
1233 l_process_total1 := 0 ;
1234
1235 WHILE (l_any_rows_to_process = TRUE) LOOP
1236 header_id_tab.delete ;
1237 row_id_tab.delete ;
1238
1239 OPEN oe_payments_cur(l_start_rowid, l_end_rowid) ;
1240
1241 FETCH oe_payments_cur BULK COLLECT INTO
1242 header_id_tab, row_id_tab
1243 LIMIT X_batch_size ;
1244
1245 CLOSE oe_payments_cur ;
1246
1247 -- oe_debug_pub.add('Number of Records : ' || header_id_tab.count) ;
1248
1249 IF header_id_tab.count > 0 THEN
1250 BEGIN
1251 FORALL i in header_id_tab.FIRST..header_id_tab.LAST SAVE EXCEPTIONS
1252 UPDATE oe_payments
1253 SET credit_card_number = null,
1254 credit_card_holder_name = null,
1255 credit_card_expiration_date = null,
1256 credit_card_code = null,
1257 credit_card_approval_code = null,
1258 credit_card_approval_date = null,
1259 tangible_id = null,
1260 payment_trx_id = null,
1261 last_update_date = sysdate,
1262 last_updated_by = l_user_id,
1263 last_update_login = l_user_id
1264 WHERE rowid = row_id_tab(i) ;
1265 EXCEPTION
1266 WHEN OTHERS THEN
1267 l_error_total1 := SQL%BULK_EXCEPTIONS.COUNT ;
1268
1269 FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
1270 fnd_file.put_line(FND_FILE.OUTPUT,
1271 'Error occurred during iteration ' ||
1272 SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
1273 ' Oracle error is ' ||
1274 SQL%BULK_EXCEPTIONS(j).ERROR_CODE );
1275
1276 fnd_file.put_line(FND_FILE.OUTPUT,
1277 'Update failing at OE_PAYMENTS from OE_PAYMENTS_CUR for Header ID ' ||
1278 header_id_tab(j));
1279 END LOOP;
1280 END;
1281
1282 l_rows_processed := SQL%ROWCOUNT ;
1283
1284 l_process_total1 := l_process_total1 + l_rows_processed ;
1285 END IF ;
1286
1287 ad_parallel_updates_pkg.processed_rowid_range
1288 (l_rows_processed,
1289 l_end_rowid) ;
1290
1291 COMMIT ;
1292
1293 ad_parallel_updates_pkg.get_rowid_range
1294 (l_start_rowid,
1295 l_end_rowid,
1296 l_any_rows_to_process,
1297 X_batch_size,
1298 FALSE) ;
1299 END LOOP ;
1300
1301 IF l_debug_level > 0 THEN
1302 oe_debug_pub.add('Total No of records processed successfully : ' || l_process_total1) ;
1303 oe_debug_pub.add('Total No of records errored in OE_PAYMENTS : ' || l_error_total1) ;
1304 END IF;
1305
1306 fnd_file.put_line(FND_FILE.OUTPUT, 'Process ending from OE_PAYMENTS table');
1307
1308 EXCEPTION
1309 WHEN NO_DATA_FOUND THEN
1310 IF l_debug_level > 0 THEN
1311 oe_debug_pub.add('') ;
1312 oe_debug_pub.add('No record found from OE_PAYMENTS table for Worker Id : ' || X_worker_id) ;
1313 END IF;
1314
1315 fnd_file.put_line(FND_FILE.OUTPUT, 'No record found from OE_PAYMENTS table for Worker Id : ' || X_worker_id) ;
1316 END ;
1317
1318 BEGIN
1319 -----------------------------------------------------------
1320 -- Fetching records from OE_ORDER_HEADERS_ALL table
1321 -----------------------------------------------------------
1322 l_table_name := 'OE_ORDER_HEADERS_ALL' ;
1323
1324 ad_parallel_updates_pkg.delete_update_information(
1325 0,
1326 l_table_owner,
1327 l_table_name,
1328 l_script_name ) ;
1329
1330 ad_parallel_updates_pkg.initialize_rowid_range(
1331 ad_parallel_updates_pkg.ROWID_RANGE,
1332 l_table_owner,
1333 l_table_name,
1334 l_script_name,
1335 X_worker_id,
1336 X_num_workers,
1337 X_batch_size, 0) ;
1338
1339 ad_parallel_updates_pkg.get_rowid_range(
1340 l_start_rowid,
1341 l_end_rowid,
1342 l_any_rows_to_process,
1343 X_batch_size,
1344 TRUE) ;
1345
1346 fnd_file.put_line(FND_FILE.OUTPUT, '');
1347 fnd_file.put_line(FND_FILE.OUTPUT, 'Process starting from OE_ORDER_HEADERS_ALL table');
1348
1349 IF l_debug_level > 0 THEN
1350 oe_debug_pub.add('') ;
1351 oe_debug_pub.add('AD parallel details : ') ;
1352 oe_debug_pub.add('') ;
1353 oe_debug_pub.add('Table owner : ' || l_table_owner) ;
1354 oe_debug_pub.add('Table name : ' || l_table_name) ;
1355 oe_debug_pub.add('Batch Size : ' || X_batch_size) ;
1356 oe_debug_pub.add('Worker ID : ' || X_worker_id) ;
1357 oe_debug_pub.add('No of Worker : ' || X_num_workers) ;
1358 END IF ;
1359
1360 l_error_total1 := 0 ;
1361
1362 l_process_total1 := 0 ;
1363
1364 WHILE (l_any_rows_to_process = TRUE) LOOP
1365 header_id_tab.delete ;
1366 row_id_tab.delete ;
1367
1368 Open header_payments_cur(l_start_rowid, l_end_rowid) ;
1369
1370 FETCH header_payments_cur BULK COLLECT INTO
1371 header_id_tab, row_id_tab
1372 LIMIT X_batch_size ;
1373
1374 CLOSE header_payments_cur ;
1375
1376 -- oe_debug_pub.add('Number of Records : ' || header_id_tab.count) ;
1377
1378 IF header_id_tab.count > 0 THEN
1379 BEGIN
1380 FORALL i in header_id_tab.FIRST..header_id_tab.LAST SAVE EXCEPTIONS
1381 UPDATE oe_order_headers_all
1382 SET credit_card_number = null,
1383 credit_card_holder_name = null,
1384 credit_card_expiration_date = null,
1385 credit_card_code = null,
1386 credit_card_approval_code = null,
1387 credit_card_approval_date = null,
1388 last_update_date = sysdate,
1389 last_updated_by = l_user_id,
1390 last_update_login = l_user_id
1391 WHERE rowid = row_id_tab(i) ;
1392 EXCEPTION
1393 WHEN OTHERS THEN
1394 l_error_total1 := SQL%BULK_EXCEPTIONS.COUNT ;
1395
1396 FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
1397 fnd_file.put_line(FND_FILE.OUTPUT,
1398 'Error occurred during iteration ' ||
1399 SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
1400 ' Oracle error is ' ||
1401 SQL%BULK_EXCEPTIONS(j).ERROR_CODE );
1402
1403 fnd_file.put_line(FND_FILE.OUTPUT,
1404 'Update failing at OE_ORDER_HEADERS_ALL from HEADER_PAYMENTS_CUR ' ||
1405 header_id_tab(j));
1406 END LOOP;
1407 END;
1408
1409 l_rows_processed := SQL%ROWCOUNT ;
1410
1411 l_process_total1 := l_process_total1 + l_rows_processed ;
1412 END IF ;
1413
1414 ad_parallel_updates_pkg.processed_rowid_range
1415 (l_rows_processed,
1416 l_end_rowid) ;
1417
1418 COMMIT ;
1419
1420 ad_parallel_updates_pkg.get_rowid_range
1421 (l_start_rowid,
1422 l_end_rowid,
1423 l_any_rows_to_process,
1424 X_batch_size,
1425 FALSE) ;
1426 END LOOP ;
1427
1428 IF l_debug_level > 0 THEN
1429 oe_debug_pub.add('Total No of records processed successfully : ' || l_process_total1) ;
1430 oe_debug_pub.add('Total No of records errored in OE_ORDER_HEADERS_ALL : ' || l_error_total1) ;
1431 END IF;
1432
1433 fnd_file.put_line(FND_FILE.OUTPUT, 'Process ending from OE_ORDER_HEADERS_ALL table');
1434
1435 EXCEPTION
1436 WHEN NO_DATA_FOUND THEN
1437 IF l_debug_level > 0 THEN
1438 oe_debug_pub.add('') ;
1439 oe_debug_pub.add('No record found from OE_ORDER_HEADERS_ALL table for Worker Id : ' || X_worker_id) ;
1440 END IF;
1441
1442 fnd_file.put_line(FND_FILE.OUTPUT, 'No record found from OE_ORDER_HEADERS_ALL table for Worker Id : ' || X_worker_id) ;
1443 END ;
1444
1445
1446 -- start processing oe_order_header_history
1447 BEGIN
1448 -----------------------------------------------------------
1449 -- Fetching records from OE_ORDER_HEADERS_HISTORY table
1450 -----------------------------------------------------------
1451 l_table_name := 'OE_ORDER_HEADER_HISTORY' ;
1452
1453 ad_parallel_updates_pkg.delete_update_information(
1454 0,
1455 l_table_owner,
1456 l_table_name,
1457 l_script_name ) ;
1458
1459 ad_parallel_updates_pkg.initialize_rowid_range(
1460 ad_parallel_updates_pkg.ROWID_RANGE,
1461 l_table_owner,
1462 l_table_name,
1463 l_script_name,
1464 X_worker_id,
1465 X_num_workers,
1466 X_batch_size, 0) ;
1467
1468 ad_parallel_updates_pkg.get_rowid_range(
1469 l_start_rowid,
1470 l_end_rowid,
1471 l_any_rows_to_process,
1472 X_batch_size,
1473 TRUE) ;
1474
1475 fnd_file.put_line(FND_FILE.OUTPUT, '');
1476 fnd_file.put_line(FND_FILE.OUTPUT, 'Process starting from OE_ORDER_HEADER_HISTORY table');
1477
1478 IF l_debug_level > 0 THEN
1479 oe_debug_pub.add('') ;
1480 oe_debug_pub.add('AD parallel details : ') ;
1481 oe_debug_pub.add('') ;
1482 oe_debug_pub.add('Table owner : ' || l_table_owner) ;
1483 oe_debug_pub.add('Table name : ' || l_table_name) ;
1484 oe_debug_pub.add('Batch Size : ' || X_batch_size) ;
1485 oe_debug_pub.add('Worker ID : ' || X_worker_id) ;
1486 oe_debug_pub.add('No of Worker : ' || X_num_workers) ;
1487 END IF ;
1488
1489 l_error_total1 := 0 ;
1490
1491 l_process_total1 := 0 ;
1492
1493 WHILE (l_any_rows_to_process = TRUE) LOOP
1494 header_id_tab.delete ;
1495 row_id_tab.delete ;
1496
1497 Open hist_payments_cur(l_start_rowid, l_end_rowid) ;
1498
1499 FETCH hist_payments_cur BULK COLLECT INTO
1500 header_id_tab, row_id_tab
1501 LIMIT X_batch_size ;
1502
1503 CLOSE hist_payments_cur ;
1504
1505 -- oe_debug_pub.add('Number of Records : ' || header_id_tab.count) ;
1506
1507 IF row_id_tab.count > 0 THEN
1508 BEGIN
1509 FORALL i in row_id_tab.FIRST..row_id_tab.LAST SAVE EXCEPTIONS
1510 UPDATE oe_order_header_history
1511 SET credit_card_number = '****',
1512 credit_card_holder_name = '****',
1513 credit_card_expiration_date = sysdate,
1514 credit_card_code = '****',
1515 last_update_date = sysdate,
1516 last_updated_by = l_user_id,
1517 last_update_login = l_user_id
1518 WHERE rowid = row_id_tab(i) ;
1519 EXCEPTION
1520 WHEN OTHERS THEN
1521 l_error_total1 := SQL%BULK_EXCEPTIONS.COUNT ;
1522
1523 FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
1524 fnd_file.put_line(FND_FILE.OUTPUT,
1525 'Error occurred during iteration ' ||
1526 SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
1527 ' Oracle error is ' ||
1528 SQL%BULK_EXCEPTIONS(j).ERROR_CODE );
1529
1530 fnd_file.put_line(FND_FILE.OUTPUT,
1531 'Update failing at OE_ORDER_HEADER_HISTORY from HIST_PAYMENTS_CUR ' ||
1532 header_id_tab(j));
1533 END LOOP;
1534 END;
1535
1536 l_rows_processed := SQL%ROWCOUNT ;
1537
1538 l_process_total1 := l_process_total1 + l_rows_processed ;
1539 END IF ;
1540
1541 ad_parallel_updates_pkg.processed_rowid_range
1542 (l_rows_processed,
1543 l_end_rowid) ;
1544
1545 COMMIT ;
1546
1547 ad_parallel_updates_pkg.get_rowid_range
1548 (l_start_rowid,
1549 l_end_rowid,
1550 l_any_rows_to_process,
1551 X_batch_size,
1552 FALSE) ;
1553 END LOOP ;
1554
1555 IF l_debug_level > 0 THEN
1556 oe_debug_pub.add('Total No of records processed successfully : ' || l_process_total1) ;
1557 oe_debug_pub.add('Total No of records errored in OE_ORDER_HEADER_HISTORY : ' || l_error_total1) ;
1558 END IF;
1559
1560 fnd_file.put_line(FND_FILE.OUTPUT, 'Process ending from OE_ORDER_HEADER_HISTORY table');
1561
1562 EXCEPTION
1563 WHEN NO_DATA_FOUND THEN
1564 IF l_debug_level > 0 THEN
1565 oe_debug_pub.add('') ;
1566 oe_debug_pub.add('No record found from OE_ORDER_HEADER_HISTORY table for Worker Id : ' || X_worker_id) ;
1567 END IF;
1568
1569 fnd_file.put_line(FND_FILE.OUTPUT, 'No record found from OE_ORDER_HEADER_HISTORY table for Worker Id : ' || X_worker_id) ;
1570 END ;
1571 -- end of processsing oe_order_header_history
1572
1573 COMMIT ;
1574
1575 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
1576
1577 EXCEPTION
1578 WHEN FND_API.G_EXC_ERROR THEN
1579 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL ;
1580
1581 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1582 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Purge_Data_WKR');
1583 END IF;
1584
1585 FND_FILE.PUT_LINE(FND_FILE.LOG,'Concurrent Request Error : '||substr(sqlerrm,1,200));
1586 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1587 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL ;
1588
1589 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1590 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Purge_Data_WKR');
1591 END IF;
1592
1593 FND_FILE.PUT_LINE(FND_FILE.LOG,'Concurrent Request Error : '||substr(sqlerrm,1,200));
1594 WHEN OTHERS THEN
1595 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL ;
1596
1597 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1598 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Purge_Data_WKR');
1599 END IF;
1600
1601 FND_FILE.PUT_LINE(FND_FILE.LOG,'Concurrent Request Error : '||substr(sqlerrm,1,200));
1602 END Purge_Data_WKR ;
1603
1604 END OE_Payment_Data_Migration_Util ;