eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OE_PAYMENT_DATA_MIGRATION_UTIL dependencies on OE_ORDER_HEADERS_ALL

Line 129: -- Define a cursor payments_cur1 to select the payment data that only exist in oe_order_headers_all

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,

Line 140: FROM oe_order_headers_all ooh,

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,

Line 182: FROM oe_order_headers_all ooh,

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,

Line 216: -- but not in oe_order_headers_all

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,

Line 226: FROM oe_order_headers_all ooh,

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,

Line 269: FROM oe_order_headers_all ooh,

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,

Line 304: -- that only exist in oe_payments but not in oe_order_headers_all, for these payment types,

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

Line 315: FROM oe_order_headers_all ooh,

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

Line 362: -- migrate data in oe_oe_order_headers_all and not exist in oe_payments

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: -----------------------------------------------------------

Line 365: -- Fetching records from OE_ORDER_HEADERS_ALL table

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(

Line 367: l_table_name := 'OE_ORDER_HEADERS_ALL' ;

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,

Line 392: fnd_file.put_line(FND_FILE.OUTPUT, 'Process starting from OE_ORDER_HEADERS_ALL table');

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 : ') ;

Line 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));

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:

Line 568: fnd_file.put_line(FND_FILE.OUTPUT, 'Process ending from OE_ORDER_HEADERS_ALL table');

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

Line 574: oe_debug_pub.add('No record found from OE_ORDER_HEADERS_ALL table for Worker Id : ' || X_worker_id) ;

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

Line 577: fnd_file.put_line(FND_FILE.OUTPUT, 'No record found from OE_ORDER_HEADERS_ALL table for Worker Id : ' || X_worker_id) ;

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

Line 589: 'upgrading failing for data from oe_order_headers_all for Header ID ' ||

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:

Line 595: oe_debug_pub.add('Total No of records errored in oe_order_headers_all : ' || l_error_total) ;

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

Line 1073: -- Define another cursor header_payments_cur to select header_id from oe_order_headers_all,

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,

Line 1079: FROM oe_order_headers_all ooh

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

Line 1166: FROM oe_order_headers_all ooh,

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

Line 1177: oe_debug_pub.add('No record found from OE_ORDER_HEADERS_ALL table for Worker Id : ' || X_worker_id) ;

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;

Line 1180: fnd_file.put_line(FND_FILE.OUTPUT, 'No record found from OE_ORDER_HEADERS_ALL table for Worker Id : ' || X_worker_id) ;

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'

Line 1203: oe_debug_pub.add('Data NOT migrated from OE_ORDER_HEADERS_ALL table (Y/N) : ' || l_exists_header) ;

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;

Line 1209: fnd_file.put_line(FND_FILE.OUTPUT, 'Data NOT migrated from OE_ORDER_HEADERS_ALL table (Y/N) : ' || l_exists_header) ;

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:

Line 1320: -- Fetching records from OE_ORDER_HEADERS_ALL table

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(

Line 1322: l_table_name := 'OE_ORDER_HEADERS_ALL' ;

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,

Line 1347: fnd_file.put_line(FND_FILE.OUTPUT, 'Process starting from OE_ORDER_HEADERS_ALL table');

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 : ') ;

Line 1381: UPDATE oe_order_headers_all

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,

Line 1404: 'Update failing at OE_ORDER_HEADERS_ALL from HEADER_PAYMENTS_CUR ' ||

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:

Line 1430: oe_debug_pub.add('Total No of records errored in OE_ORDER_HEADERS_ALL : ' || l_error_total1) ;

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:

Line 1433: fnd_file.put_line(FND_FILE.OUTPUT, 'Process ending from OE_ORDER_HEADERS_ALL table');

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

Line 1439: oe_debug_pub.add('No record found from OE_ORDER_HEADERS_ALL table for Worker Id : ' || X_worker_id) ;

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 ;

Line 1442: fnd_file.put_line(FND_FILE.OUTPUT, 'No record found from OE_ORDER_HEADERS_ALL table for Worker Id : ' || X_worker_id) ;

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