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