[Home] [Help]
299: l_global_count number :=0;
300:
301: BEGIN
302:
303: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Headers()+' );
304:
305: IF (process_mode = 'LOCK') THEN
306:
307: -- try to open table for update, if it fails the exception will
306:
307: -- try to open table for update, if it fails the exception will
308: -- tell us that the merge was going to block
309:
310: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
311: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADERS_ALL', FALSE );
312:
313: open merge_headers;
314: close merge_headers;
307: -- try to open table for update, if it fails the exception will
308: -- tell us that the merge was going to block
309:
310: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
311: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADERS_ALL', FALSE );
312:
313: open merge_headers;
314: close merge_headers;
315:
404: old_hdr_end_cust_id_tab := hdr_end_cust_id_tab;
405:
406:
407: IF l_dbi_profile = 'Y' then
408: arp_message.set_line(' update_date_tab_count for hdr='||dbi_local_hdr_update_date_tab.COUNT);
409: IF dbi_local_hdr_update_date_tab.COUNT > 0 then
410:
411:
412: IF dbi_header_update_date_tab.COUNT > 0 then
471:
472:
473: end loop;
474: end if;
475: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Headers():3' );
476: --insert audit information for customer merge
477: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
478: IF hdr_header_id_tab.COUNT <> 0 THEN
479: forall i in hdr_header_id_tab.FIRST..hdr_header_id_tab.LAST
534: end if;
535: end if;
536:
537:
538: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
539: arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
540: IF hdr_header_id_tab.COUNT <> 0 THEN
541:
542: FORALL i in hdr_header_id_tab.FIRST..hdr_header_id_tab.LAST
535: end if;
536:
537:
538: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
539: arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
540: IF hdr_header_id_tab.COUNT <> 0 THEN
541:
542: FORALL i in hdr_header_id_tab.FIRST..hdr_header_id_tab.LAST
543: UPDATE OE_ORDER_HEADERS_ALL H
563: g_count := sql%rowcount;
564: ELSE
565: g_count := 0;
566: END IF;
567: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
568: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
569: -- Bug 7379750
570: IF l_run_parallel_query = 'Y' THEN
571: EXIT WHEN merge_headers_2%NOTFOUND;
564: ELSE
565: g_count := 0;
566: END IF;
567: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
568: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
569: -- Bug 7379750
570: IF l_run_parallel_query = 'Y' THEN
571: EXIT WHEN merge_headers_2%NOTFOUND;
572: ELSE
606: customer_site_id_tab.DELETE;
607: dbi_local_hdr_header_id_tab.DELETE;
608: dbi_local_hdr_update_date_tab.DELETE;
609:
610: arp_message.set_line(' header_id_tab_count for hdr='||dbi_header_header_id_tab.COUNT);
611: arp_message.set_line(' update_date_tab_count for hdr='||dbi_header_update_date_tab.COUNT);
612:
613: arp_message.set_line( ' END OE_CUST_MERGE.OE_Merge_Headers()-' );
614:
607: dbi_local_hdr_header_id_tab.DELETE;
608: dbi_local_hdr_update_date_tab.DELETE;
609:
610: arp_message.set_line(' header_id_tab_count for hdr='||dbi_header_header_id_tab.COUNT);
611: arp_message.set_line(' update_date_tab_count for hdr='||dbi_header_update_date_tab.COUNT);
612:
613: arp_message.set_line( ' END OE_CUST_MERGE.OE_Merge_Headers()-' );
614:
615:
609:
610: arp_message.set_line(' header_id_tab_count for hdr='||dbi_header_header_id_tab.COUNT);
611: arp_message.set_line(' update_date_tab_count for hdr='||dbi_header_update_date_tab.COUNT);
612:
613: arp_message.set_line( ' END OE_CUST_MERGE.OE_Merge_Headers()-' );
614:
615:
616: EXCEPTION
617: when others then
614:
615:
616: EXCEPTION
617: when others then
618: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Headers-' );
619: raise;
620:
621: END OE_Merge_Headers;
622:
876: customer_site_id_tab num_table_binary_int;
877:
878: BEGIN
879:
880: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_HEADER_History()+' );
881:
882: IF (process_mode = 'LOCK') THEN
883:
884: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
880: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_HEADER_History()+' );
881:
882: IF (process_mode = 'LOCK') THEN
883:
884: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
885: arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
886:
887: open merge_headers_history;
888: close merge_headers_history;
881:
882: IF (process_mode = 'LOCK') THEN
883:
884: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
885: arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
886:
887: open merge_headers_history;
888: close merge_headers_history;
889:
976: end loop;
977: end if;
978:
979:
980: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
981: arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
982:
983: IF hhst_header_id_tab.COUNT <> 0 THEN
984: FORALL i in hhst_header_id_tab.FIRST..hhst_header_id_tab.LAST
977: end if;
978:
979:
980: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
981: arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
982:
983: IF hhst_header_id_tab.COUNT <> 0 THEN
984: FORALL i in hhst_header_id_tab.FIRST..hhst_header_id_tab.LAST
985: UPDATE OE_ORDER_HEADER_HISTORY HHIST
1004: ELSE
1005: g_count := 0;
1006: END IF;
1007:
1008: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1009: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1010: -- Bug 7379750
1011: IF l_run_parallel_query = 'Y' THEN
1012: EXIT WHEN merge_headers_history_2%NOTFOUND;
1005: g_count := 0;
1006: END IF;
1007:
1008: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1009: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1010: -- Bug 7379750
1011: IF l_run_parallel_query = 'Y' THEN
1012: EXIT WHEN merge_headers_history_2%NOTFOUND;
1013: ELSE
1034:
1035: END IF;
1036: customer_id_tab.DELETE;
1037: customer_site_id_tab.DELETE;
1038: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_History()-' );
1039:
1040: EXCEPTION
1041: when others then
1042: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Header_History-' );
1038: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_History()-' );
1039:
1040: EXCEPTION
1041: when others then
1042: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Header_History-' );
1043: raise;
1044:
1045: END OE_MERGE_HEADER_HISTORY; -- 3196900
1046:
1124: and m.set_number = set_num)
1125: for update nowait;
1126:
1127: BEGIN
1128: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_History()+' );
1129:
1130: -- both customer and site level
1131:
1132: IF( process_mode = 'LOCK' ) THEN
1130: -- both customer and site level
1131:
1132: IF( process_mode = 'LOCK' ) THEN
1133:
1134: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1135: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1136:
1137: open c1;
1138: close c1;
1131:
1132: IF( process_mode = 'LOCK' ) THEN
1133:
1134: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1135: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1136:
1137: open c1;
1138: close c1;
1139:
1158: ELSE
1159:
1160:
1161: -- site level update
1162: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1163: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1164:
1165: UPDATE oe_order_header_history a
1166: set ship_to_org_id = (select distinct m.customer_site_id
1159:
1160:
1161: -- site level update
1162: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1163: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1164:
1165: UPDATE oe_order_header_history a
1166: set ship_to_org_id = (select distinct m.customer_site_id
1167: from ra_customer_merges m
1181: where m.process_flag = 'N'
1182: and m.request_id = req_id
1183: and m.set_number = set_num);
1184: g_count := sql%rowcount;
1185: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1186: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1187:
1188:
1189: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1182: and m.request_id = req_id
1183: and m.set_number = set_num);
1184: g_count := sql%rowcount;
1185: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1186: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1187:
1188:
1189: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1190: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1185: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1186: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1187:
1188:
1189: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1190: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1191:
1192: UPDATE oe_order_header_history a
1193: set invoice_to_org_id = (select distinct m.customer_site_id
1186: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1187:
1188:
1189: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1190: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1191:
1192: UPDATE oe_order_header_history a
1193: set invoice_to_org_id = (select distinct m.customer_site_id
1194: from ra_customer_merges m
1208: where m.process_flag = 'N'
1209: and m.request_id = req_id
1210: and m.set_number = set_num);
1211: g_count := sql%rowcount;
1212: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1213: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1214:
1215: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1216: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1209: and m.request_id = req_id
1210: and m.set_number = set_num);
1211: g_count := sql%rowcount;
1212: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1213: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1214:
1215: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1216: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1217:
1211: g_count := sql%rowcount;
1212: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1213: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1214:
1215: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1216: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1217:
1218: UPDATE oe_order_header_history a
1219: set deliver_to_org_id = (select distinct m.customer_site_id
1212: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1213: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1214:
1215: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1216: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1217:
1218: UPDATE oe_order_header_history a
1219: set deliver_to_org_id = (select distinct m.customer_site_id
1220: from ra_customer_merges m
1234: where m.process_flag = 'N'
1235: and m.request_id = req_id
1236: and m.set_number = set_num);
1237: g_count := sql%rowcount;
1238: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1239: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1240:
1241: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1242: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1235: and m.request_id = req_id
1236: and m.set_number = set_num);
1237: g_count := sql%rowcount;
1238: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1239: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1240:
1241: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1242: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1243:
1237: g_count := sql%rowcount;
1238: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1239: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1240:
1241: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1242: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1243:
1244:
1245: -- customer level update --
1238: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1239: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1240:
1241: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1242: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1243:
1244:
1245: -- customer level update --
1246:
1243:
1244:
1245: -- customer level update --
1246:
1247: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1248: arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
1249:
1250: UPDATE oe_order_header_history a
1251: set sold_to_org_id = (select distinct m.customer_id
1244:
1245: -- customer level update --
1246:
1247: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1248: arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
1249:
1250: UPDATE oe_order_header_history a
1251: set sold_to_org_id = (select distinct m.customer_id
1252: from ra_customer_merges m
1268: and m.set_number = set_num);
1269:
1270: g_count := sql%rowcount;
1271:
1272: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1273: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1274:
1275: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1276: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1269:
1270: g_count := sql%rowcount;
1271:
1272: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1273: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1274:
1275: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1276: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1277:
1271:
1272: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1273: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1274:
1275: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1276: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1277:
1278: UPDATE oe_order_header_history a
1279: set sold_to_site_use_id = (select distinct m.customer_id
1272: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1273: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1274:
1275: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1276: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1277:
1278: UPDATE oe_order_header_history a
1279: set sold_to_site_use_id = (select distinct m.customer_id
1280: from ra_customer_merges m
1296: and m.set_number = set_num);
1297:
1298: g_count := sql%rowcount;
1299:
1300: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1301: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1302:
1303: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1304: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1297:
1298: g_count := sql%rowcount;
1299:
1300: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1301: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1302:
1303: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1304: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1305:
1299:
1300: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1301: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1302:
1303: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1304: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1305:
1306: UPDATE oe_order_header_history a
1307: set end_customer_site_use_id = (select distinct m.customer_id
1300: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1301: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1302:
1303: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1304: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1305:
1306: UPDATE oe_order_header_history a
1307: set end_customer_site_use_id = (select distinct m.customer_id
1308: from ra_customer_merges m
1324: and m.set_number = set_num);
1325:
1326: g_count := sql%rowcount;
1327:
1328: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1329: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1330:
1331: -- customer level update --
1332:
1325:
1326: g_count := sql%rowcount;
1327:
1328: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1329: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1330:
1331: -- customer level update --
1332:
1333: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1329: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1330:
1331: -- customer level update --
1332:
1333: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1334: arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
1335:
1336: UPDATE oe_order_header_history a
1337: set end_customer_id = (select distinct m.customer_id
1330:
1331: -- customer level update --
1332:
1333: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1334: arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
1335:
1336: UPDATE oe_order_header_history a
1337: set end_customer_id = (select distinct m.customer_id
1338: from ra_customer_merges m
1354: and m.set_number = set_num);
1355:
1356: g_count := sql%rowcount;
1357:
1358: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1359: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1360:
1361: END IF;
1362:
1355:
1356: g_count := sql%rowcount;
1357:
1358: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1359: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1360:
1361: END IF;
1362:
1363: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_History()-' );
1359: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1360:
1361: END IF;
1362:
1363: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_History()-' );
1364:
1365: EXCEPTION
1366: When others then
1367: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Header_History-' );
1363: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_History()-' );
1364:
1365: EXCEPTION
1366: When others then
1367: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Header_History-' );
1368: raise;
1369:
1370: END OE_Merge_Header_History; */
1371:
1429:
1430:
1431:
1432: BEGIN
1433: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Headers_IFACE()+' );
1434:
1435: -- both customer and site level
1436:
1437: IF( process_mode = 'LOCK' ) THEN
1435: -- both customer and site level
1436:
1437: IF( process_mode = 'LOCK' ) THEN
1438:
1439: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1440: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1441:
1442: open c1;
1443: close c1;
1436:
1437: IF( process_mode = 'LOCK' ) THEN
1438:
1439: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1440: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1441:
1442: open c1;
1443: close c1;
1444:
1454:
1455: ELSE
1456:
1457: -- site level update
1458: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1459: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1460:
1461: UPDATE oe_headers_iface_all a
1462: set ship_to_org_id = (select distinct m.customer_site_id
1455: ELSE
1456:
1457: -- site level update
1458: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1459: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1460:
1461: UPDATE oe_headers_iface_all a
1462: set ship_to_org_id = (select distinct m.customer_site_id
1463: from ra_customer_merges m
1477: where m.process_flag = 'N'
1478: and m.request_id = req_id
1479: and m.set_number = set_num);
1480: g_count := sql%rowcount;
1481: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1482: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1483:
1484: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1485: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1478: and m.request_id = req_id
1479: and m.set_number = set_num);
1480: g_count := sql%rowcount;
1481: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1482: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1483:
1484: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1485: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1486:
1480: g_count := sql%rowcount;
1481: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1482: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1483:
1484: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1485: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1486:
1487: UPDATE oe_headers_iface_all a
1488: set invoice_to_org_id = (select distinct m.customer_site_id
1481: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1482: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1483:
1484: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1485: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1486:
1487: UPDATE oe_headers_iface_all a
1488: set invoice_to_org_id = (select distinct m.customer_site_id
1489: from ra_customer_merges m
1503: where m.process_flag = 'N'
1504: and m.request_id = req_id
1505: and m.set_number = set_num);
1506: g_count := sql%rowcount;
1507: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1508: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1509:
1510: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1511: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1504: and m.request_id = req_id
1505: and m.set_number = set_num);
1506: g_count := sql%rowcount;
1507: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1508: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1509:
1510: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1511: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1512:
1506: g_count := sql%rowcount;
1507: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1508: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1509:
1510: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1511: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1512:
1513: UPDATE oe_headers_iface_all a
1514: set deliver_to_org_id = (select distinct m.customer_site_id
1507: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1508: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1509:
1510: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1511: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1512:
1513: UPDATE oe_headers_iface_all a
1514: set deliver_to_org_id = (select distinct m.customer_site_id
1515: from ra_customer_merges m
1529: where m.process_flag = 'N'
1530: and m.request_id = req_id
1531: and m.set_number = set_num);
1532: g_count := sql%rowcount;
1533: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1534: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1535:
1536: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1537: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1530: and m.request_id = req_id
1531: and m.set_number = set_num);
1532: g_count := sql%rowcount;
1533: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1534: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1535:
1536: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1537: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1538:
1532: g_count := sql%rowcount;
1533: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1534: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1535:
1536: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1537: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1538:
1539:
1540: -- customer level update
1533: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1534: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1535:
1536: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1537: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1538:
1539:
1540: -- customer level update
1541: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1537: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1538:
1539:
1540: -- customer level update
1541: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1542: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1543:
1544: UPDATE oe_headers_iface_all a
1545: set sold_to_org_id = (select distinct m.customer_id
1538:
1539:
1540: -- customer level update
1541: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1542: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1543:
1544: UPDATE oe_headers_iface_all a
1545: set sold_to_org_id = (select distinct m.customer_id
1546: from ra_customer_merges m
1562: and m.set_number = set_num);
1563:
1564: g_count := sql%rowcount;
1565:
1566: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1567: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1568:
1569: END IF;
1570:
1563:
1564: g_count := sql%rowcount;
1565:
1566: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1567: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1568:
1569: END IF;
1570:
1571: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Headers_IFACE()-' );
1567: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1568:
1569: END IF;
1570:
1571: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Headers_IFACE()-' );
1572:
1573: EXCEPTION
1574: When others then
1575: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Headers_IFACE-' );
1571: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Headers_IFACE()-' );
1572:
1573: EXCEPTION
1574: When others then
1575: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Headers_IFACE-' );
1576: raise;
1577:
1578: END OE_Merge_Headers_IFACE;
1579:
1682:
1683: l_profile_val VARCHAR2(30);
1684:
1685: BEGIN
1686: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_ACKS()+' );
1687:
1688: /* both customer and site level */
1689:
1690: IF( process_mode = 'LOCK' ) THEN
1688: /* both customer and site level */
1689:
1690: IF( process_mode = 'LOCK' ) THEN
1691:
1692: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1693: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1694:
1695: open c1;
1696: close c1;
1689:
1690: IF( process_mode = 'LOCK' ) THEN
1691:
1692: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1693: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1694:
1695: open c1;
1696: close c1;
1697:
1757: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1758: end if;
1759:
1760: /* site level update */
1761: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1762: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1763:
1764: UPDATE OE_HEADER_ACKS a
1765: set ship_to_org_id = (select distinct m.customer_site_id
1758: end if;
1759:
1760: /* site level update */
1761: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1762: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1763:
1764: UPDATE OE_HEADER_ACKS a
1765: set ship_to_org_id = (select distinct m.customer_site_id
1766: from ra_customer_merges m
1781: and m.request_id = req_id
1782: and m.set_number = set_num)
1783: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1784: g_count := sql%rowcount;
1785: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1786: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1787:
1788: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1789: insert into HZ_CUSTOMER_MERGE_LOG (
1782: and m.set_number = set_num)
1783: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1784: g_count := sql%rowcount;
1785: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1786: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1787:
1788: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1789: insert into HZ_CUSTOMER_MERGE_LOG (
1790: MERGE_LOG_ID,
1823: and m.set_number = set_num
1824: and NVL(h.ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1825: end if;
1826:
1827: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1828: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1829:
1830: UPDATE OE_HEADER_ACKS a
1831: set invoice_to_org_id = (select distinct m.customer_site_id
1824: and NVL(h.ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1825: end if;
1826:
1827: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1828: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1829:
1830: UPDATE OE_HEADER_ACKS a
1831: set invoice_to_org_id = (select distinct m.customer_site_id
1832: from ra_customer_merges m
1847: and m.request_id = req_id
1848: and m.set_number = set_num)
1849: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1850: g_count := sql%rowcount;
1851: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1852: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1853:
1854: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1855: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1848: and m.set_number = set_num)
1849: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1850: g_count := sql%rowcount;
1851: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1852: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1853:
1854: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1855: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1856:
1850: g_count := sql%rowcount;
1851: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1852: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1853:
1854: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1855: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1856:
1857: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1858: insert into HZ_CUSTOMER_MERGE_LOG (
1851: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1852: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1853:
1854: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1855: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1856:
1857: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1858: insert into HZ_CUSTOMER_MERGE_LOG (
1859: MERGE_LOG_ID,
1913: and m.request_id = req_id
1914: and m.set_number = set_num)
1915: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1916: g_count := sql%rowcount;
1917: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1918: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1919:
1920: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1921: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1914: and m.set_number = set_num)
1915: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1916: g_count := sql%rowcount;
1917: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1918: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1919:
1920: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1921: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1922:
1916: g_count := sql%rowcount;
1917: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1918: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1919:
1920: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1921: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1922:
1923:
1924: /* customer level update */
1917: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1918: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1919:
1920: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1921: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1922:
1923:
1924: /* customer level update */
1925: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1921: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1922:
1923:
1924: /* customer level update */
1925: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1926: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1927:
1928: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1929: insert into HZ_CUSTOMER_MERGE_LOG (
1922:
1923:
1924: /* customer level update */
1925: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1926: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1927:
1928: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1929: insert into HZ_CUSTOMER_MERGE_LOG (
1930: MERGE_LOG_ID,
1988: and a.header_id in (select header_id from oe_order_headers);
1989:
1990: g_count := sql%rowcount;
1991:
1992: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1993: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1994:
1995: -- sold_to_site_use_id merge
1996:
1989:
1990: g_count := sql%rowcount;
1991:
1992: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1993: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1994:
1995: -- sold_to_site_use_id merge
1996:
1997: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1993: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1994:
1995: -- sold_to_site_use_id merge
1996:
1997: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1998: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1999:
2000: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
2001: insert into HZ_CUSTOMER_MERGE_LOG (
1994:
1995: -- sold_to_site_use_id merge
1996:
1997: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1998: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1999:
2000: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
2001: insert into HZ_CUSTOMER_MERGE_LOG (
2002: MERGE_LOG_ID,
2060: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
2061:
2062: g_count := sql%rowcount;
2063:
2064: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2065: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2066:
2067:
2068: -- end_customer_site_use_id merge
2061:
2062: g_count := sql%rowcount;
2063:
2064: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2065: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2066:
2067:
2068: -- end_customer_site_use_id merge
2069:
2066:
2067:
2068: -- end_customer_site_use_id merge
2069:
2070: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2071: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
2072:
2073: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
2074: insert into HZ_CUSTOMER_MERGE_LOG (
2067:
2068: -- end_customer_site_use_id merge
2069:
2070: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2071: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
2072:
2073: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
2074: insert into HZ_CUSTOMER_MERGE_LOG (
2075: MERGE_LOG_ID,
2135: NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
2136:
2137: g_count := sql%rowcount;
2138:
2139: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2140: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2141:
2142: /* customer level update */
2143: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2136:
2137: g_count := sql%rowcount;
2138:
2139: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2140: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2141:
2142: /* customer level update */
2143: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2144: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
2139: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2140: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2141:
2142: /* customer level update */
2143: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2144: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
2145:
2146: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
2147: insert into HZ_CUSTOMER_MERGE_LOG (
2140: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2141:
2142: /* customer level update */
2143: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2144: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
2145:
2146: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
2147: insert into HZ_CUSTOMER_MERGE_LOG (
2148: MERGE_LOG_ID,
2209: and a.header_id in (select header_id from oe_order_headers);
2210:
2211: g_count := sql%rowcount;
2212:
2213: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2214: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2215:
2216: END IF;
2217:
2210:
2211: g_count := sql%rowcount;
2212:
2213: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2214: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2215:
2216: END IF;
2217:
2218: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_ACKS()-' );
2214: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2215:
2216: END IF;
2217:
2218: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_ACKS()-' );
2219:
2220: EXCEPTION
2221: When others then
2222: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Header_ACKS-' );
2218: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_ACKS()-' );
2219:
2220: EXCEPTION
2221: When others then
2222: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Header_ACKS-' );
2223: raise;
2224:
2225: END OE_Merge_Header_ACKS;
2226:
2534: l_global_count number :=0;
2535:
2536: BEGIN
2537:
2538: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines()+' );
2539:
2540: IF (process_mode = 'LOCK') THEN
2541:
2542: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
2538: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines()+' );
2539:
2540: IF (process_mode = 'LOCK') THEN
2541:
2542: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
2543: arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
2544:
2545: open merge_lines;
2546: close merge_lines;
2539:
2540: IF (process_mode = 'LOCK') THEN
2541:
2542: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
2543: arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
2544:
2545: open merge_lines;
2546: close merge_lines;
2547:
2563:
2564: HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
2565: l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
2566:
2567: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines():1' );
2568: -- Bug 7379750
2569: IF l_run_parallel_query = 'Y' THEN
2570: OPEN merge_lines_2;
2571: ELSE
2610: LIMIT 20000;
2611: END IF;
2612:
2613:
2614: arp_message.set_line('local line_hdr_id_count='||dbi_local_line_header_id_tab.COUNT);
2615: arp_message.set_line('local line_line__id_count='||dbi_local_line_line_id_tab.COUNT);
2616: arp_message.set_line('local line_date_count='||dbi_local_line_update_date_tab.COUNT);
2617: arp_message.set_line('local line_status_count='||dbi_local_line_status_tab.COUNT);
2618: IF l_dbi_profile = 'Y' then
2611: END IF;
2612:
2613:
2614: arp_message.set_line('local line_hdr_id_count='||dbi_local_line_header_id_tab.COUNT);
2615: arp_message.set_line('local line_line__id_count='||dbi_local_line_line_id_tab.COUNT);
2616: arp_message.set_line('local line_date_count='||dbi_local_line_update_date_tab.COUNT);
2617: arp_message.set_line('local line_status_count='||dbi_local_line_status_tab.COUNT);
2618: IF l_dbi_profile = 'Y' then
2619: IF dbi_local_line_update_date_tab.COUNT > 0 then
2612:
2613:
2614: arp_message.set_line('local line_hdr_id_count='||dbi_local_line_header_id_tab.COUNT);
2615: arp_message.set_line('local line_line__id_count='||dbi_local_line_line_id_tab.COUNT);
2616: arp_message.set_line('local line_date_count='||dbi_local_line_update_date_tab.COUNT);
2617: arp_message.set_line('local line_status_count='||dbi_local_line_status_tab.COUNT);
2618: IF l_dbi_profile = 'Y' then
2619: IF dbi_local_line_update_date_tab.COUNT > 0 then
2620:
2613:
2614: arp_message.set_line('local line_hdr_id_count='||dbi_local_line_header_id_tab.COUNT);
2615: arp_message.set_line('local line_line__id_count='||dbi_local_line_line_id_tab.COUNT);
2616: arp_message.set_line('local line_date_count='||dbi_local_line_update_date_tab.COUNT);
2617: arp_message.set_line('local line_status_count='||dbi_local_line_status_tab.COUNT);
2618: IF l_dbi_profile = 'Y' then
2619: IF dbi_local_line_update_date_tab.COUNT > 0 then
2620:
2621:
2748:
2749: end if;
2750: end if;
2751:
2752: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2753: arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
2754: IF line_line_id_tab.COUNT <> 0 THEN
2755: FORALL i in line_line_id_tab.FIRST..line_line_id_tab.LAST
2756: UPDATE OE_ORDER_LINES_ALL L
2749: end if;
2750: end if;
2751:
2752: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2753: arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
2754: IF line_line_id_tab.COUNT <> 0 THEN
2755: FORALL i in line_line_id_tab.FIRST..line_line_id_tab.LAST
2756: UPDATE OE_ORDER_LINES_ALL L
2757: SET sold_to_org_id = decode(line_sold_to_org_id_tab(i),null,sold_to_org_id,line_sold_to_org_id_tab(i)),
2777:
2778: ELSE
2779: g_count := 0;
2780: END IF;
2781: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2782: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2783: -- Bug 7379750
2784: IF l_run_parallel_query = 'Y' THEN
2785: EXIT WHEN merge_lines_2%NOTFOUND;
2778: ELSE
2779: g_count := 0;
2780: END IF;
2781: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2782: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2783: -- Bug 7379750
2784: IF l_run_parallel_query = 'Y' THEN
2785: EXIT WHEN merge_lines_2%NOTFOUND;
2786: ELSE
2818: dbi_local_line_update_date_tab.DELETE;
2819: dbi_local_line_line_id_tab.DELETE;
2820: dbi_local_line_status_tab.DELETE;
2821:
2822: arp_message.set_line(' line_hdr_id_count='||dbi_line_header_id_tab.COUNT);
2823: arp_message.set_line(' line_line__id_count='||dbi_line_line_id_tab.COUNT);
2824: arp_message.set_line(' line_date_count='||dbi_line_update_date_tab.COUNT);
2825: arp_message.set_line(' line_status_count='||dbi_line_status_tab.COUNT);
2826: arp_message.set_line( ' END OE_CUST_MERGE.OE_Merge_Lines()-' );
2819: dbi_local_line_line_id_tab.DELETE;
2820: dbi_local_line_status_tab.DELETE;
2821:
2822: arp_message.set_line(' line_hdr_id_count='||dbi_line_header_id_tab.COUNT);
2823: arp_message.set_line(' line_line__id_count='||dbi_line_line_id_tab.COUNT);
2824: arp_message.set_line(' line_date_count='||dbi_line_update_date_tab.COUNT);
2825: arp_message.set_line(' line_status_count='||dbi_line_status_tab.COUNT);
2826: arp_message.set_line( ' END OE_CUST_MERGE.OE_Merge_Lines()-' );
2827:
2820: dbi_local_line_status_tab.DELETE;
2821:
2822: arp_message.set_line(' line_hdr_id_count='||dbi_line_header_id_tab.COUNT);
2823: arp_message.set_line(' line_line__id_count='||dbi_line_line_id_tab.COUNT);
2824: arp_message.set_line(' line_date_count='||dbi_line_update_date_tab.COUNT);
2825: arp_message.set_line(' line_status_count='||dbi_line_status_tab.COUNT);
2826: arp_message.set_line( ' END OE_CUST_MERGE.OE_Merge_Lines()-' );
2827:
2828: EXCEPTION
2821:
2822: arp_message.set_line(' line_hdr_id_count='||dbi_line_header_id_tab.COUNT);
2823: arp_message.set_line(' line_line__id_count='||dbi_line_line_id_tab.COUNT);
2824: arp_message.set_line(' line_date_count='||dbi_line_update_date_tab.COUNT);
2825: arp_message.set_line(' line_status_count='||dbi_line_status_tab.COUNT);
2826: arp_message.set_line( ' END OE_CUST_MERGE.OE_Merge_Lines()-' );
2827:
2828: EXCEPTION
2829: when others then
2822: arp_message.set_line(' line_hdr_id_count='||dbi_line_header_id_tab.COUNT);
2823: arp_message.set_line(' line_line__id_count='||dbi_line_line_id_tab.COUNT);
2824: arp_message.set_line(' line_date_count='||dbi_line_update_date_tab.COUNT);
2825: arp_message.set_line(' line_status_count='||dbi_line_status_tab.COUNT);
2826: arp_message.set_line( ' END OE_CUST_MERGE.OE_Merge_Lines()-' );
2827:
2828: EXCEPTION
2829: when others then
2830: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Lines-' );
2826: arp_message.set_line( ' END OE_CUST_MERGE.OE_Merge_Lines()-' );
2827:
2828: EXCEPTION
2829: when others then
2830: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Lines-' );
2831: raise;
2832:
2833: END OE_Merge_Lines;
2834:
3076: customer_site_id_tab num_table_binary_int;
3077:
3078: BEGIN
3079:
3080: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines_History()+' );
3081:
3082: IF (process_mode = 'LOCK') THEN
3083:
3084: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3080: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines_History()+' );
3081:
3082: IF (process_mode = 'LOCK') THEN
3083:
3084: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3085: arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
3086:
3087: open merge_lines_history;
3088: close merge_lines_history;
3081:
3082: IF (process_mode = 'LOCK') THEN
3083:
3084: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3085: arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
3086:
3087: open merge_lines_history;
3088: close merge_lines_history;
3089:
3177: end loop;
3178: end if;
3179:
3180:
3181: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3182: arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
3183:
3184: IF lhst_line_id_tab.COUNT <> 0 THEN
3185: FORALL i in lhst_line_id_tab.FIRST..lhst_line_id_tab.LAST
3178: end if;
3179:
3180:
3181: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3182: arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
3183:
3184: IF lhst_line_id_tab.COUNT <> 0 THEN
3185: FORALL i in lhst_line_id_tab.FIRST..lhst_line_id_tab.LAST
3186: UPDATE OE_ORDER_LINES_HISTORY LHIST
3205: ELSE
3206: g_count := 0;
3207: END IF;
3208:
3209: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3210: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3211: -- Bug 7379750
3212: IF l_run_parallel_query = 'Y' THEN
3213: EXIT WHEN merge_lines_history_2%NOTFOUND;
3206: g_count := 0;
3207: END IF;
3208:
3209: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3210: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3211: -- Bug 7379750
3212: IF l_run_parallel_query = 'Y' THEN
3213: EXIT WHEN merge_lines_history_2%NOTFOUND;
3214: ELSE
3234:
3235: END IF;
3236: customer_id_tab.DELETE;
3237: customer_site_id_tab.DELETE;
3238: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines_History()-' );
3239:
3240: EXCEPTION
3241: when others then
3242: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Lines_History-' );
3238: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines_History()-' );
3239:
3240: EXCEPTION
3241: when others then
3242: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Lines_History-' );
3243: raise;
3244:
3245: END OE_Merge_Lines_History;
3246:
3302: and m.set_number = set_num)
3303: for update nowait;
3304:
3305: BEGIN
3306: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines_IFACE()+' );
3307:
3308: -- both customer and site level
3309:
3310: IF( process_mode = 'LOCK' ) THEN
3308: -- both customer and site level
3309:
3310: IF( process_mode = 'LOCK' ) THEN
3311:
3312: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3313: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3314:
3315: open c1;
3316: close c1;
3309:
3310: IF( process_mode = 'LOCK' ) THEN
3311:
3312: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3313: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3314:
3315: open c1;
3316: close c1;
3317:
3325: close c4;
3326:
3327: ELSE
3328: -- site level update
3329: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3330: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3331:
3332: UPDATE oe_lines_iface_all a
3333: set ship_to_org_id = (select distinct m.customer_site_id
3326:
3327: ELSE
3328: -- site level update
3329: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3330: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3331:
3332: UPDATE oe_lines_iface_all a
3333: set ship_to_org_id = (select distinct m.customer_site_id
3334: from ra_customer_merges m
3348: where m.process_flag = 'N'
3349: and m.request_id = req_id
3350: and m.set_number = set_num);
3351: g_count := sql%rowcount;
3352: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3353: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3354:
3355: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3356: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3349: and m.request_id = req_id
3350: and m.set_number = set_num);
3351: g_count := sql%rowcount;
3352: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3353: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3354:
3355: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3356: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3357:
3351: g_count := sql%rowcount;
3352: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3353: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3354:
3355: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3356: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3357:
3358: UPDATE oe_lines_iface_all a
3359: set invoice_to_org_id = (select distinct m.customer_site_id
3352: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3353: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3354:
3355: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3356: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3357:
3358: UPDATE oe_lines_iface_all a
3359: set invoice_to_org_id = (select distinct m.customer_site_id
3360: from ra_customer_merges m
3374: where m.process_flag = 'N'
3375: and m.request_id = req_id
3376: and m.set_number = set_num);
3377: g_count := sql%rowcount;
3378: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3379: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3380:
3381: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3382: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3375: and m.request_id = req_id
3376: and m.set_number = set_num);
3377: g_count := sql%rowcount;
3378: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3379: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3380:
3381: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3382: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3383:
3377: g_count := sql%rowcount;
3378: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3379: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3380:
3381: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3382: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3383:
3384: UPDATE oe_lines_iface_all a
3385: set deliver_to_org_id = (select distinct m.customer_site_id
3378: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3379: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3380:
3381: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3382: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3383:
3384: UPDATE oe_lines_iface_all a
3385: set deliver_to_org_id = (select distinct m.customer_site_id
3386: from ra_customer_merges m
3400: where m.process_flag = 'N'
3401: and m.request_id = req_id
3402: and m.set_number = set_num);
3403: g_count := sql%rowcount;
3404: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3405: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3406:
3407: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3408: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3401: and m.request_id = req_id
3402: and m.set_number = set_num);
3403: g_count := sql%rowcount;
3404: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3405: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3406:
3407: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3408: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3409:
3403: g_count := sql%rowcount;
3404: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3405: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3406:
3407: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3408: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3409:
3410: -- customer level update
3411: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3404: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3405: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3406:
3407: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3408: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3409:
3410: -- customer level update
3411: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3412: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3407: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3408: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3409:
3410: -- customer level update
3411: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3412: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3413:
3414: UPDATE oe_lines_iface_all a
3415: set sold_to_org_id = (select distinct m.customer_id
3408: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3409:
3410: -- customer level update
3411: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3412: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3413:
3414: UPDATE oe_lines_iface_all a
3415: set sold_to_org_id = (select distinct m.customer_id
3416: from ra_customer_merges m
3432: and m.set_number = set_num);
3433:
3434: g_count := sql%rowcount;
3435:
3436: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3437: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3438:
3439: END IF;
3440:
3433:
3434: g_count := sql%rowcount;
3435:
3436: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3437: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3438:
3439: END IF;
3440:
3441: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines_IFACE()-' );
3437: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3438:
3439: END IF;
3440:
3441: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines_IFACE()-' );
3442:
3443: EXCEPTION
3444: When others then
3445: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Lines_IFACE-' );
3441: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines_IFACE()-' );
3442:
3443: EXCEPTION
3444: When others then
3445: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Lines_IFACE-' );
3446: raise;
3447:
3448: END OE_Merge_Lines_IFACE;
3449:
3551:
3552: l_profile_val VARCHAR2(30);
3553:
3554: BEGIN
3555: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Line_ACKS()+' );
3556:
3557: /* both customer and site level */
3558:
3559: IF( process_mode = 'LOCK' ) THEN
3557: /* both customer and site level */
3558:
3559: IF( process_mode = 'LOCK' ) THEN
3560:
3561: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3562: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3563:
3564: open c1;
3565: close c1;
3558:
3559: IF( process_mode = 'LOCK' ) THEN
3560:
3561: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3562: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3563:
3564: open c1;
3565: close c1;
3566:
3628: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3629: end if;
3630:
3631: /* site level update */
3632: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3633: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3634:
3635: UPDATE oe_line_acks a
3636: set ship_to_org_id = (select distinct m.customer_site_id
3629: end if;
3630:
3631: /* site level update */
3632: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3633: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3634:
3635: UPDATE oe_line_acks a
3636: set ship_to_org_id = (select distinct m.customer_site_id
3637: from ra_customer_merges m
3652: and m.request_id = req_id
3653: and m.set_number = set_num)
3654: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3655: g_count := sql%rowcount;
3656: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3657: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3658:
3659: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3660: insert into HZ_CUSTOMER_MERGE_LOG (
3653: and m.set_number = set_num)
3654: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3655: g_count := sql%rowcount;
3656: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3657: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3658:
3659: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3660: insert into HZ_CUSTOMER_MERGE_LOG (
3661: MERGE_LOG_ID,
3696: and m.set_number = set_num
3697: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3698: end if;
3699:
3700: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3701: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3702:
3703: UPDATE oe_line_acks a
3704: set invoice_to_org_id = (select distinct m.customer_site_id
3697: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3698: end if;
3699:
3700: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3701: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3702:
3703: UPDATE oe_line_acks a
3704: set invoice_to_org_id = (select distinct m.customer_site_id
3705: from ra_customer_merges m
3720: and m.request_id = req_id
3721: and m.set_number = set_num)
3722: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3723: g_count := sql%rowcount;
3724: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3725: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3726:
3727: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3728: insert into HZ_CUSTOMER_MERGE_LOG (
3721: and m.set_number = set_num)
3722: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3723: g_count := sql%rowcount;
3724: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3725: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3726:
3727: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3728: insert into HZ_CUSTOMER_MERGE_LOG (
3729: MERGE_LOG_ID,
3764: and m.set_number = set_num
3765: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3766: end if;
3767:
3768: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3769: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3770:
3771: UPDATE oe_line_acks a
3772: set deliver_to_org_id = (select distinct m.customer_site_id
3765: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3766: end if;
3767:
3768: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3769: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3770:
3771: UPDATE oe_line_acks a
3772: set deliver_to_org_id = (select distinct m.customer_site_id
3773: from ra_customer_merges m
3788: and m.request_id = req_id
3789: and m.set_number = set_num)
3790: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3791: g_count := sql%rowcount;
3792: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3793: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3794:
3795: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3796: insert into HZ_CUSTOMER_MERGE_LOG (
3789: and m.set_number = set_num)
3790: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3791: g_count := sql%rowcount;
3792: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3793: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3794:
3795: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3796: insert into HZ_CUSTOMER_MERGE_LOG (
3797: MERGE_LOG_ID,
3832: and m.set_number = set_num
3833: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3834: end if;
3835:
3836: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3837: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3838:
3839:
3840: UPDATE oe_line_acks a
3833: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3834: end if;
3835:
3836: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3837: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3838:
3839:
3840: UPDATE oe_line_acks a
3841: set intmed_ship_to_org_id = (select distinct m.customer_site_id
3858: and m.set_number = set_num)
3859: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3860: g_count := sql%rowcount;
3861:
3862: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3863: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3864:
3865: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3866: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3859: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3860: g_count := sql%rowcount;
3861:
3862: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3863: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3864:
3865: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3866: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3867:
3861:
3862: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3863: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3864:
3865: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3866: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3867:
3868:
3869: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3862: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3863: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3864:
3865: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3866: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3867:
3868:
3869: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3870: insert into HZ_CUSTOMER_MERGE_LOG (
3907: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3908: end if;
3909:
3910: /* customer level update */
3911: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3912: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3913:
3914: UPDATE oe_line_acks a
3915: set sold_to_org_id = (select distinct m.customer_id
3908: end if;
3909:
3910: /* customer level update */
3911: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3912: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3913:
3914: UPDATE oe_line_acks a
3915: set sold_to_org_id = (select distinct m.customer_id
3916: from ra_customer_merges m
3935: and a.line_id in (select line_id from oe_order_lines);
3936:
3937: g_count := sql%rowcount;
3938:
3939: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3940: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3941:
3942: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3943: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3936:
3937: g_count := sql%rowcount;
3938:
3939: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3940: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3941:
3942: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3943: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3944:
3938:
3939: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3940: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3941:
3942: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3943: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3944:
3945:
3946: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3939: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3940: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3941:
3942: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3943: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3944:
3945:
3946: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3947: insert into HZ_CUSTOMER_MERGE_LOG (
3984: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3985: end if;
3986:
3987: /* customer level update */
3988: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3989: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3990:
3991: UPDATE oe_line_acks a
3992: set end_customer_site_use_id = (select distinct m.customer_id
3985: end if;
3986:
3987: /* customer level update */
3988: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3989: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3990:
3991: UPDATE oe_line_acks a
3992: set end_customer_site_use_id = (select distinct m.customer_id
3993: from ra_customer_merges m
4010: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
4011:
4012: g_count := sql%rowcount;
4013:
4014: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4015: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4016:
4017: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4018: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
4011:
4012: g_count := sql%rowcount;
4013:
4014: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4015: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4016:
4017: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4018: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
4019:
4013:
4014: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4015: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4016:
4017: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4018: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
4019:
4020:
4021: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4014: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4015: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4016:
4017: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4018: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
4019:
4020:
4021: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4022: insert into HZ_CUSTOMER_MERGE_LOG (
4059: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
4060: end if;
4061:
4062: /* customer level update */
4063: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4064: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
4065:
4066: UPDATE oe_line_acks a
4067: set end_customer_id = (select distinct m.customer_id
4060: end if;
4061:
4062: /* customer level update */
4063: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4064: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
4065:
4066: UPDATE oe_line_acks a
4067: set end_customer_id = (select distinct m.customer_id
4068: from ra_customer_merges m
4087: and a.line_id in (select line_id from oe_order_lines);
4088:
4089: g_count := sql%rowcount;
4090:
4091: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4092: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4093:
4094: END IF;
4095:
4088:
4089: g_count := sql%rowcount;
4090:
4091: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4092: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4093:
4094: END IF;
4095:
4096: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Line_ACKS()-' );
4092: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4093:
4094: END IF;
4095:
4096: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Line_ACKS()-' );
4097:
4098: EXCEPTION
4099: When others then
4100: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Line_ACKS-' );
4096: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Line_ACKS()-' );
4097:
4098: EXCEPTION
4099: When others then
4100: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Line_ACKS-' );
4101: raise;
4102:
4103: END OE_Merge_Line_ACKS;
4104: -----------------------------------------------------------------
4122: sql_stmnt varchar2(200);
4123:
4124: BEGIN
4125:
4126: arp_message.set_line( 'OE_CUST_MERGE.Merge()+' );
4127: -- sql_stmnt := 'Alter Session set hash_area_size=61440000';
4128: -- EXECUTE IMMEDIATE sql_stmnt;
4129: --Changed for Bug#12313696 start
4130: --l_dbi_profile := NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'), 'Y');
4130: --l_dbi_profile := NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'), 'Y');
4131: l_dbi_profile := NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'), 'N');
4132: --Changed for Bug#12313696 end
4133:
4134: arp_message.set_line(' Dbi Profile='||l_dbi_profile);
4135: OE_CUST_MERGE.OE_Attachment_Merge (Req_Id, Set_Num, Process_Mode);
4136: OE_CUST_MERGE.OE_Defaulting_Merge (Req_Id, Set_Num, Process_Mode);
4137: OE_CUST_MERGE.OE_Hold_Merge (Req_Id, Set_Num, Process_Mode);
4138: OE_CUST_MERGE.OE_Constraints_Merge (Req_Id, Set_Num, Process_Mode);
4140: -- drop ship are po line location id and not site ids of accounts
4141: --OE_CUST_MERGE.OE_Drop_Ship_Merge (Req_Id, Set_Num, Process_Mode);
4142: OE_CUST_MERGE.OE_Ship_Tolerance_Merge (Req_Id, Set_Num, Process_Mode);
4143: OE_CUST_MERGE.OE_Order_Merge (Req_Id, Set_Num, Process_Mode);
4144: arp_message.set_line( 'OE_CUST_MERGE.Merge()-' );
4145:
4146: /* this part will be calling other internal procedures */
4147:
4148: EXCEPTION
4146: /* this part will be calling other internal procedures */
4147:
4148: EXCEPTION
4149: When others then
4150: arp_message.set_error( 'OE_CUST_MERGE.Merge-' );
4151: raise;
4152:
4153:
4154: END Merge;
4194:
4195: l_profile_val VARCHAR2(30);
4196:
4197: BEGIN
4198: arp_message.set_line( 'OE_CUST_MERGE.OE_Attachment_Merge()+' );
4199:
4200: /*-----------------------------+
4201: | OE_ATTACHMENTS_RULE_ELEMENTS|
4202: +-----------------------------*/
4203: /* both customer and site level */
4204:
4205: IF( process_mode = 'LOCK' ) THEN
4206:
4207: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
4208: arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
4209:
4210: open c1;
4211: close c1;
4204:
4205: IF( process_mode = 'LOCK' ) THEN
4206:
4207: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
4208: arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
4209:
4210: open c1;
4211: close c1;
4212:
4262:
4263: END IF;
4264:
4265: /* site level update */
4266: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4267: arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
4268:
4269: UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
4270: set (attribute_value) = (select distinct to_char(m.customer_site_id)
4263: END IF;
4264:
4265: /* site level update */
4266: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4267: arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
4268:
4269: UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
4270: set (attribute_value) = (select distinct to_char(m.customer_site_id)
4271: from ra_customer_merges m
4285: and attribute_code = 'SHIP_TO_ORG_ID';
4286:
4287: g_count := sql%rowcount;
4288:
4289: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4290: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4291:
4292:
4293: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4286:
4287: g_count := sql%rowcount;
4288:
4289: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4290: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4291:
4292:
4293: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4294: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4330:
4331: END IF;
4332:
4333: /* site level update */
4334: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4335: arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
4336:
4337: UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
4338: set (attribute_value) = (select distinct to_char(m.customer_site_id)
4331: END IF;
4332:
4333: /* site level update */
4334: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4335: arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
4336:
4337: UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
4338: set (attribute_value) = (select distinct to_char(m.customer_site_id)
4339: from ra_customer_merges m
4353: and attribute_code = 'INVOICE_TO_ORG_ID';
4354:
4355: g_count := sql%rowcount;
4356:
4357: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4358: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4359:
4360:
4361:
4354:
4355: g_count := sql%rowcount;
4356:
4357: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4358: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4359:
4360:
4361:
4362: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4399:
4400: END IF;
4401:
4402: /* customer level update */
4403: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4404: arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
4405:
4406: UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
4407: set (attribute_value) = (select distinct to_char(m.customer_id)
4400: END IF;
4401:
4402: /* customer level update */
4403: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4404: arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
4405:
4406: UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
4407: set (attribute_value) = (select distinct to_char(m.customer_id)
4408: from ra_customer_merges m
4423:
4424:
4425: g_count := sql%rowcount;
4426:
4427: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4428: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4429:
4430: END IF;
4431:
4424:
4425: g_count := sql%rowcount;
4426:
4427: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4428: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4429:
4430: END IF;
4431:
4432: arp_message.set_line( 'OE_CUST_MERGE.OE_Attachment_Merge()-' );
4428: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4429:
4430: END IF;
4431:
4432: arp_message.set_line( 'OE_CUST_MERGE.OE_Attachment_Merge()-' );
4433:
4434:
4435: EXCEPTION
4436: when others then
4433:
4434:
4435: EXCEPTION
4436: when others then
4437: arp_message.set_line( 'OE_CUST_MERGE.OE_Attachment_Merge' );
4438: raise;
4439:
4440:
4441: END OE_Attachment_Merge;
4536:
4537: l_profile_val VARCHAR2(30);
4538:
4539: BEGIN
4540: arp_message.set_line( 'OE_CUST_MERGE.OE_Defaulting_Merge()+' );
4541:
4542: /*-----------------------------+
4543: | OE_DEF_CONDN_ELEMS|
4544: +-----------------------------*/
4545: /* both customer and site level */
4546:
4547: IF( process_mode = 'LOCK' ) THEN
4548:
4549: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
4550: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4551:
4552:
4553: open c1;
4546:
4547: IF( process_mode = 'LOCK' ) THEN
4548:
4549: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
4550: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4551:
4552:
4553: open c1;
4554: close c1;
4619:
4620: END IF;
4621:
4622: /* site level update */
4623: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4624: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4625:
4626: UPDATE OE_DEF_CONDN_ELEMS a
4627: set value_string = (select distinct to_char(m.customer_site_id)
4620: END IF;
4621:
4622: /* site level update */
4623: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4624: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4625:
4626: UPDATE OE_DEF_CONDN_ELEMS a
4627: set value_string = (select distinct to_char(m.customer_site_id)
4628: from ra_customer_merges m
4643: and attribute_code = 'SHIP_TO_ORG_ID';
4644:
4645: g_count := sql%rowcount;
4646:
4647: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4648: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4649: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4650: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4651: MERGE_LOG_ID,
4644:
4645: g_count := sql%rowcount;
4646:
4647: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4648: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4649: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4650: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4651: MERGE_LOG_ID,
4652: TABLE_NAME,
4687: END IF;
4688:
4689:
4690: /* site level update */
4691: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4692: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4693:
4694: UPDATE OE_DEF_CONDN_ELEMS a
4695: set value_string = (select distinct to_char(m.customer_site_id)
4688:
4689:
4690: /* site level update */
4691: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4692: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4693:
4694: UPDATE OE_DEF_CONDN_ELEMS a
4695: set value_string = (select distinct to_char(m.customer_site_id)
4696: from ra_customer_merges m
4711: and attribute_code = 'INVOICE_TO_ORG_ID';
4712:
4713: g_count := sql%rowcount;
4714:
4715: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4716: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4717:
4718: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4719: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4712:
4713: g_count := sql%rowcount;
4714:
4715: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4716: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4717:
4718: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4719: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4720: MERGE_LOG_ID,
4755:
4756: END IF;
4757:
4758: /* site level update */
4759: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4760: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4761:
4762: UPDATE OE_DEF_CONDN_ELEMS a
4763: set value_string = (select distinct to_char(m.customer_site_id)
4756: END IF;
4757:
4758: /* site level update */
4759: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4760: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4761:
4762: UPDATE OE_DEF_CONDN_ELEMS a
4763: set value_string = (select distinct to_char(m.customer_site_id)
4764: from ra_customer_merges m
4779: and attribute_code = 'INTMED_SHIP_TO_ORG_ID';
4780:
4781: g_count := sql%rowcount;
4782:
4783: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4784: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4785:
4786: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4787: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4780:
4781: g_count := sql%rowcount;
4782:
4783: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4784: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4785:
4786: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4787: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4788: MERGE_LOG_ID,
4823:
4824: END IF;
4825:
4826: /* customer level update */
4827: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4828: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4829:
4830: UPDATE OE_DEF_CONDN_ELEMS a
4831: set value_string = (select distinct to_char(m.customer_id)
4824: END IF;
4825:
4826: /* customer level update */
4827: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4828: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4829:
4830: UPDATE OE_DEF_CONDN_ELEMS a
4831: set value_string = (select distinct to_char(m.customer_id)
4832: from ra_customer_merges m
4848:
4849:
4850: g_count := sql%rowcount;
4851:
4852: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4853: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4854:
4855:
4856: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4849:
4850: g_count := sql%rowcount;
4851:
4852: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4853: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4854:
4855:
4856: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4857: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4893:
4894: END IF;
4895:
4896: /* site level update */
4897: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4898: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
4899:
4900:
4901:
4894: END IF;
4895:
4896: /* site level update */
4897: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4898: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
4899:
4900:
4901:
4902: UPDATE OE_DEF_ATTR_DEF_RULES a
4919: and attribute_code = 'SHIP_TO_ORG_ID';
4920:
4921: g_count := sql%rowcount;
4922:
4923: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4924: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4925:
4926: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4927: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4920:
4921: g_count := sql%rowcount;
4922:
4923: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4924: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4925:
4926: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4927: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4928: MERGE_LOG_ID,
4963:
4964: END IF;
4965:
4966: /* site level update */
4967: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4968: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
4969:
4970: UPDATE OE_DEF_ATTR_DEF_RULES a
4971: set src_constant_value = (select distinct to_char(m.customer_site_id)
4964: END IF;
4965:
4966: /* site level update */
4967: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4968: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
4969:
4970: UPDATE OE_DEF_ATTR_DEF_RULES a
4971: set src_constant_value = (select distinct to_char(m.customer_site_id)
4972: from ra_customer_merges m
4987: and attribute_code = 'INVOICE_TO_ORG_ID';
4988:
4989: g_count := sql%rowcount;
4990:
4991: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4992: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4993:
4994: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4995: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4988:
4989: g_count := sql%rowcount;
4990:
4991: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4992: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4993:
4994: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4995: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4996: MERGE_LOG_ID,
5031:
5032: END IF;
5033:
5034: /* site level update */
5035: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5036: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
5037:
5038: UPDATE OE_DEF_ATTR_DEF_RULES a
5039: set src_constant_value = (select distinct to_char(m.customer_site_id)
5032: END IF;
5033:
5034: /* site level update */
5035: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5036: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
5037:
5038: UPDATE OE_DEF_ATTR_DEF_RULES a
5039: set src_constant_value = (select distinct to_char(m.customer_site_id)
5040: from ra_customer_merges m
5055: and attribute_code = 'INTMED_SHIP_TO_ORG_ID';
5056:
5057: g_count := sql%rowcount;
5058:
5059: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5060: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5061:
5062:
5063: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
5056:
5057: g_count := sql%rowcount;
5058:
5059: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5060: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5061:
5062:
5063: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
5064: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
5100:
5101: END IF;
5102:
5103: /* customer level update */
5104: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5105: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
5106:
5107: UPDATE OE_DEF_ATTR_DEF_RULES a
5108: set src_constant_value = (select distinct to_char(m.customer_id)
5101: END IF;
5102:
5103: /* customer level update */
5104: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5105: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
5106:
5107: UPDATE OE_DEF_ATTR_DEF_RULES a
5108: set src_constant_value = (select distinct to_char(m.customer_id)
5109: from ra_customer_merges m
5125:
5126:
5127: g_count := sql%rowcount;
5128:
5129: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5130: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5131:
5132:
5133:
5126:
5127: g_count := sql%rowcount;
5128:
5129: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5130: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5131:
5132:
5133:
5134: END IF;
5132:
5133:
5134: END IF;
5135:
5136: arp_message.set_line( 'OE_CUST_MERGE.OE_Defaulting_Merge()-' );
5137:
5138:
5139: EXCEPTION
5140: when others then
5137:
5138:
5139: EXCEPTION
5140: when others then
5141: arp_message.set_line( 'OE_CUST_MERGE.OE_Defaulting_Merge' );
5142: raise;
5143:
5144:
5145:
5196:
5197: l_profile_val VARCHAR2(30);
5198:
5199: BEGIN
5200: arp_message.set_line( 'OE_CUST_MERGE.Constraints_Merge()+' );
5201:
5202: /*-----------------------------+
5203: | oe_pc_vtmplt_cols|
5204: +-----------------------------*/
5205: /* both customer and site level */
5206:
5207: IF( process_mode = 'LOCK' ) THEN
5208:
5209: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5210: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
5211:
5212:
5213: open c1;
5206:
5207: IF( process_mode = 'LOCK' ) THEN
5208:
5209: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5210: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
5211:
5212:
5213: open c1;
5214: close c1;
5268:
5269: END IF;
5270:
5271: /* site level update */
5272: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5273: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
5274:
5275: UPDATE OE_PC_VTMPLT_COLS a
5276: set value_string = (select distinct to_char(m.customer_site_id)
5269: END IF;
5270:
5271: /* site level update */
5272: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5273: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
5274:
5275: UPDATE OE_PC_VTMPLT_COLS a
5276: set value_string = (select distinct to_char(m.customer_site_id)
5277: from ra_customer_merges m
5292: and column_name = 'SHIP_TO_ORG_ID';
5293:
5294: g_count := sql%rowcount;
5295:
5296: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5297: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5298:
5299: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
5300: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
5293:
5294: g_count := sql%rowcount;
5295:
5296: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5297: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5298:
5299: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
5300: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
5301: MERGE_LOG_ID,
5336:
5337: END IF;
5338:
5339: /* site level update */
5340: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5341: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
5342:
5343: UPDATE OE_PC_VTMPLT_COLS a
5344: set value_string = (select distinct to_char(m.customer_site_id)
5337: END IF;
5338:
5339: /* site level update */
5340: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5341: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
5342:
5343: UPDATE OE_PC_VTMPLT_COLS a
5344: set value_string = (select distinct to_char(m.customer_site_id)
5345: from ra_customer_merges m
5360: and column_name = 'INVOICE_TO_ORG_ID';
5361:
5362: g_count := sql%rowcount;
5363:
5364: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5365: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5366:
5367: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
5368: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
5361:
5362: g_count := sql%rowcount;
5363:
5364: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5365: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5366:
5367: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
5368: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
5369: MERGE_LOG_ID,
5404:
5405: END IF;
5406:
5407: /* site level update */
5408: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5409: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
5410:
5411: UPDATE OE_PC_VTMPLT_COLS a
5412: set value_string = (select distinct to_char(m.customer_site_id)
5405: END IF;
5406:
5407: /* site level update */
5408: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5409: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
5410:
5411: UPDATE OE_PC_VTMPLT_COLS a
5412: set value_string = (select distinct to_char(m.customer_site_id)
5413: from ra_customer_merges m
5428: and column_name = 'INTMED_SHIP_TO_ORG_ID';
5429:
5430: g_count := sql%rowcount;
5431:
5432: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5433: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5434:
5435:
5436: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
5429:
5430: g_count := sql%rowcount;
5431:
5432: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5433: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5434:
5435:
5436: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
5437: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
5473:
5474: END IF;
5475:
5476: /* customer level update */
5477: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5478: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
5479:
5480: UPDATE OE_PC_VTMPLT_COLS a
5481: set value_string = (select distinct to_char(m.customer_id)
5474: END IF;
5475:
5476: /* customer level update */
5477: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5478: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
5479:
5480: UPDATE OE_PC_VTMPLT_COLS a
5481: set value_string = (select distinct to_char(m.customer_id)
5482: from ra_customer_merges m
5498:
5499:
5500: g_count := sql%rowcount;
5501:
5502: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5503: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5504:
5505: END IF;
5506:
5499:
5500: g_count := sql%rowcount;
5501:
5502: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5503: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5504:
5505: END IF;
5506:
5507: arp_message.set_line( 'OE_CUST_MERGE.OE_Constraints_Merge()-' );
5503: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5504:
5505: END IF;
5506:
5507: arp_message.set_line( 'OE_CUST_MERGE.OE_Constraints_Merge()-' );
5508:
5509:
5510: EXCEPTION
5511: when others then
5508:
5509:
5510: EXCEPTION
5511: when others then
5512: arp_message.set_line( 'OE_CUST_MERGE.OE_Constraints_Merge' );
5513: raise;
5514:
5515: END OE_Constraints_Merge;
5516:
5559: l_profile_val VARCHAR2(30);
5560:
5561: BEGIN
5562:
5563: arp_message.set_line( 'OE_CUST_MERGE.OE_Hold_Merge()+' );
5564: /*-----------------------------+
5565: | OE_HOLD_SOURCES |
5566: +-----------------------------*/
5567: /* both customer and site level */
5567: /* both customer and site level */
5568:
5569: IF( process_mode = 'LOCK' ) THEN
5570:
5571: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5572: arp_message.set_token( 'TABLE_NAME', 'OE_HOLD_SOURCES', FALSE );
5573:
5574: open c1;
5575: close c1;
5568:
5569: IF( process_mode = 'LOCK' ) THEN
5570:
5571: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5572: arp_message.set_token( 'TABLE_NAME', 'OE_HOLD_SOURCES', FALSE );
5573:
5574: open c1;
5575: close c1;
5576:
5625:
5626: END IF;
5627:
5628: /* site level update */
5629: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5630: arp_message.set_token( 'TABLE_NAME', 'OE_HOLD_SOURCES', FALSE );
5631:
5632: UPDATE OE_HOLD_SOURCES a
5633: set (hold_entity_id) = (select distinct to_char(m.customer_site_id) --Bug 8866783
5626: END IF;
5627:
5628: /* site level update */
5629: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5630: arp_message.set_token( 'TABLE_NAME', 'OE_HOLD_SOURCES', FALSE );
5631:
5632: UPDATE OE_HOLD_SOURCES a
5633: set (hold_entity_id) = (select distinct to_char(m.customer_site_id) --Bug 8866783
5634: from ra_customer_merges m
5649: and hold_entity_code = 'S';
5650:
5651: g_count := sql%rowcount;
5652:
5653: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5654: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5655:
5656: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
5657: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
5650:
5651: g_count := sql%rowcount;
5652:
5653: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5654: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5655:
5656: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
5657: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
5658: MERGE_LOG_ID,
5693:
5694: END IF;
5695:
5696: /* site level update */
5697: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5698: arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
5699: oe_debug_pub.add('ER 3095805 Updating Bill To Site 1');
5700:
5701: UPDATE OE_HOLD_SOURCES a
5694: END IF;
5695:
5696: /* site level update */
5697: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5698: arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
5699: oe_debug_pub.add('ER 3095805 Updating Bill To Site 1');
5700:
5701: UPDATE OE_HOLD_SOURCES a
5702: set (hold_entity_id) = (select distinct to_char(m.customer_site_id) --Bug 8866783
5719: RETURNING hold_id,hold_entity_id,hold_release_id, released_flag --ER 3095805
5720: BULK COLLECT INTO l_hold_id,l_hold_entity_id, l_hold_release_id, l_released_flag; --ER 3095805
5721: g_count := sql%rowcount;
5722: oe_debug_pub.add('ER 3095805 After Updating Bill To Site '||g_count);
5723: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5724: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5725:
5726:
5727: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
5720: BULK COLLECT INTO l_hold_id,l_hold_entity_id, l_hold_release_id, l_released_flag; --ER 3095805
5721: g_count := sql%rowcount;
5722: oe_debug_pub.add('ER 3095805 After Updating Bill To Site '||g_count);
5723: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5724: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5725:
5726:
5727: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
5728: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
5764:
5765: END IF;
5766:
5767: /* customer level update */
5768: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5769: arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
5770: oe_debug_pub.add('ER 3095805 Updating Acc');
5771:
5772: UPDATE OE_HOLD_SOURCES a
5765: END IF;
5766:
5767: /* customer level update */
5768: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5769: arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
5770: oe_debug_pub.add('ER 3095805 Updating Acc');
5771:
5772: UPDATE OE_HOLD_SOURCES a
5773: set hold_entity_id = (select distinct to_char(m.customer_id) --Bug 8866783
5790: BULK COLLECT INTO l_hold_id1,l_hold_entity_id1, l_hold_release_id1, l_released_flag1; --ER 3095805
5791:
5792: g_count := sql%rowcount;
5793: oe_debug_pub.add('ER 3095805 After Updating Acc '||g_count);
5794: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5795: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5796:
5797: END IF;
5798:
5791:
5792: g_count := sql%rowcount;
5793: oe_debug_pub.add('ER 3095805 After Updating Acc '||g_count);
5794: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5795: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5796:
5797: END IF;
5798:
5799: arp_message.set_line( 'OE_CUST_MERGE.OE_Hold_Merge()-' );
5795: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5796:
5797: END IF;
5798:
5799: arp_message.set_line( 'OE_CUST_MERGE.OE_Hold_Merge()-' );
5800:
5801:
5802: EXCEPTION
5803: when others then
5800:
5801:
5802: EXCEPTION
5803: when others then
5804: arp_message.set_line( 'OE_CUST_MERGE.OE_Hold_Merge' );
5805: raise;
5806:
5807: END OE_Hold_Merge;
5808:
5823:
5824: l_profile_val VARCHAR2(30);
5825:
5826: BEGIN
5827: arp_message.set_line( 'OE_CUST_MERGE.OE_Drop_SHip_Merge()+' );
5828:
5829: /* both customer and site level */
5830:
5831: IF( process_mode = 'LOCK' ) THEN
5829: /* both customer and site level */
5830:
5831: IF( process_mode = 'LOCK' ) THEN
5832:
5833: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5834: arp_message.set_token( 'TABLE_NAME', 'OE_DROP_SHIP_SOURCES', FALSE );
5835:
5836: open c1;
5837: close c1;
5830:
5831: IF( process_mode = 'LOCK' ) THEN
5832:
5833: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5834: arp_message.set_token( 'TABLE_NAME', 'OE_DROP_SHIP_SOURCES', FALSE );
5835:
5836: open c1;
5837: close c1;
5838:
5880:
5881: END IF;
5882:
5883: /* site level update */
5884: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5885: arp_message.set_token( 'TABLE_NAME', 'OE_DROP_SHIP_SOURCES', FALSE );
5886:
5887: UPDATE OE_DROP_SHIP_SOURCES a
5888: set line_location_id = (select distinct m.customer_site_id
5881: END IF;
5882:
5883: /* site level update */
5884: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5885: arp_message.set_token( 'TABLE_NAME', 'OE_DROP_SHIP_SOURCES', FALSE );
5886:
5887: UPDATE OE_DROP_SHIP_SOURCES a
5888: set line_location_id = (select distinct m.customer_site_id
5889: from ra_customer_merges m
5899: where m.process_flag = 'N'
5900: and m.request_id = req_id
5901: and m.set_number = set_num);
5902: g_count := sql%rowcount;
5903: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5904: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5905:
5906: END IF;
5907:
5900: and m.request_id = req_id
5901: and m.set_number = set_num);
5902: g_count := sql%rowcount;
5903: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5904: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5905:
5906: END IF;
5907:
5908: arp_message.set_line( 'OE_CUST_MERGE.OE_Drop_SHip_Merge()-' );
5904: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5905:
5906: END IF;
5907:
5908: arp_message.set_line( 'OE_CUST_MERGE.OE_Drop_SHip_Merge()-' );
5909:
5910: EXCEPTION
5911: When others then
5912: arp_message.set_error( 'OE_CUST_MERGE.OE_Drop_SHip_Merge-' );
5908: arp_message.set_line( 'OE_CUST_MERGE.OE_Drop_SHip_Merge()-' );
5909:
5910: EXCEPTION
5911: When others then
5912: arp_message.set_error( 'OE_CUST_MERGE.OE_Drop_SHip_Merge-' );
5913: raise;
5914:
5915: END OE_Drop_Ship_Merge;
5916:
5942:
5943: l_profile_val VARCHAR2(30);
5944:
5945: BEGIN
5946: arp_message.set_line( 'OE_CUST_MERGE.OE_ship_tolerance_merge()+' );
5947:
5948: /* both customer and site level */
5949:
5950: IF( process_mode = 'LOCK' ) THEN
5948: /* both customer and site level */
5949:
5950: IF( process_mode = 'LOCK' ) THEN
5951:
5952: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5953: arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
5954:
5955: open c1;
5956: close c1;
5949:
5950: IF( process_mode = 'LOCK' ) THEN
5951:
5952: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5953: arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
5954:
5955: open c1;
5956: close c1;
5957:
6004:
6005: END IF;
6006:
6007: /* site level update */
6008: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6009: arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
6010:
6011: UPDATE OE_CUST_ITEM_SETTINGS a
6012: set site_use_id = (select distinct m.customer_site_id
6005: END IF;
6006:
6007: /* site level update */
6008: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6009: arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
6010:
6011: UPDATE OE_CUST_ITEM_SETTINGS a
6012: set site_use_id = (select distinct m.customer_site_id
6013: from ra_customer_merges m
6023: where m.process_flag = 'N'
6024: and m.request_id = req_id
6025: and m.set_number = set_num);
6026: g_count := sql%rowcount;
6027: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6028: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6029:
6030: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
6031: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
6024: and m.request_id = req_id
6025: and m.set_number = set_num);
6026: g_count := sql%rowcount;
6027: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6028: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6029:
6030: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
6031: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
6032: MERGE_LOG_ID,
6066:
6067: END IF;
6068:
6069: /* customer level update */
6070: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6071: arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
6072:
6073: UPDATE OE_CUST_ITEM_SETTINGS a
6074: set customer_id = (select distinct m.customer_id
6067: END IF;
6068:
6069: /* customer level update */
6070: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6071: arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
6072:
6073: UPDATE OE_CUST_ITEM_SETTINGS a
6074: set customer_id = (select distinct m.customer_id
6075: from ra_customer_merges m
6085: where m.process_flag = 'N'
6086: and m.request_id = req_id
6087: and m.set_number = set_num);
6088: g_count := sql%rowcount;
6089: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6090: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6091:
6092: END IF;
6093:
6086: and m.request_id = req_id
6087: and m.set_number = set_num);
6088: g_count := sql%rowcount;
6089: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6090: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6091:
6092: END IF;
6093:
6094: arp_message.set_line( 'OE_CUST_MERGE.OE_Ship_Tolerance_Merge()-' );
6090: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6091:
6092: END IF;
6093:
6094: arp_message.set_line( 'OE_CUST_MERGE.OE_Ship_Tolerance_Merge()-' );
6095:
6096: EXCEPTION
6097: When others then
6098: arp_message.set_error( 'OE_CUST_MERGE.OE_Ship_Tolerance_Merge-' );
6094: arp_message.set_line( 'OE_CUST_MERGE.OE_Ship_Tolerance_Merge()-' );
6095:
6096: EXCEPTION
6097: When others then
6098: arp_message.set_error( 'OE_CUST_MERGE.OE_Ship_Tolerance_Merge-' );
6099: raise;
6100:
6101: END OE_Ship_Tolerance_Merge;
6102:
6153: l_profile_val VARCHAR2(30);
6154:
6155: BEGIN
6156:
6157: arp_message.set_line( 'OE_CUST_MERGE.OE_Sets_Merge()+' );
6158:
6159: IF (process_mode = 'LOCK') THEN
6160:
6161: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
6157: arp_message.set_line( 'OE_CUST_MERGE.OE_Sets_Merge()+' );
6158:
6159: IF (process_mode = 'LOCK') THEN
6160:
6161: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
6162: arp_message.set_token( 'TABLE_NAME', 'oe_sets', FALSE );
6163:
6164: open sets_merge;
6165: close sets_merge;
6158:
6159: IF (process_mode = 'LOCK') THEN
6160:
6161: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
6162: arp_message.set_token( 'TABLE_NAME', 'oe_sets', FALSE );
6163:
6164: open sets_merge;
6165: close sets_merge;
6166:
6225: end if;
6226: end if;
6227:
6228:
6229: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6230: arp_message.set_token( 'TABLE_NAME', 'oe_sets', FALSE );
6231:
6232: IF sets_set_id_tab.COUNT <> 0 THEN
6233: FORALL i in sets_set_id_tab.FIRST..sets_set_id_tab.LAST
6226: end if;
6227:
6228:
6229: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6230: arp_message.set_token( 'TABLE_NAME', 'oe_sets', FALSE );
6231:
6232: IF sets_set_id_tab.COUNT <> 0 THEN
6233: FORALL i in sets_set_id_tab.FIRST..sets_set_id_tab.LAST
6234: UPDATE OE_SETS S
6243: ELSE
6244: g_count := 0;
6245: END IF;
6246:
6247: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6248: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6249: ---bug# 13786914
6250: IF l_run_parallel_query = 'Y' THEN
6251: EXIT WHEN sets_merge_2%NOTFOUND;
6244: g_count := 0;
6245: END IF;
6246:
6247: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6248: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6249: ---bug# 13786914
6250: IF l_run_parallel_query = 'Y' THEN
6251: EXIT WHEN sets_merge_2%NOTFOUND;
6252: ELSE
6264: CLOSE sets_merge_2_np;
6265: END IF;
6266:
6267: END IF;
6268: arp_message.set_line( 'OE_CUST_MERGE.OE_Sets_Merge()-' );
6269:
6270: EXCEPTION
6271: when others then
6272: arp_message.set_error( 'OE_CUST_MERGE.OE_Sets_Merge-' );
6268: arp_message.set_line( 'OE_CUST_MERGE.OE_Sets_Merge()-' );
6269:
6270: EXCEPTION
6271: when others then
6272: arp_message.set_error( 'OE_CUST_MERGE.OE_Sets_Merge-' );
6273: raise;
6274:
6275: END OE_Sets_merge;
6276:
6342: l_profile_val varchar2(30);
6343:
6344: BEGIN
6345:
6346: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Price_Adj_Attribs()+' );
6347: IF (process_mode = 'LOCK') THEN
6348:
6349: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
6350: arp_message.set_token( 'TABLE_NAME', 'oe_price_adj_attribs', FALSE );
6345:
6346: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Price_Adj_Attribs()+' );
6347: IF (process_mode = 'LOCK') THEN
6348:
6349: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
6350: arp_message.set_token( 'TABLE_NAME', 'oe_price_adj_attribs', FALSE );
6351:
6352: open c1;
6353: close c1;
6346: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Price_Adj_Attribs()+' );
6347: IF (process_mode = 'LOCK') THEN
6348:
6349: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
6350: arp_message.set_token( 'TABLE_NAME', 'oe_price_adj_attribs', FALSE );
6351:
6352: open c1;
6353: close c1;
6354:
6401:
6402: END IF;
6403: end if;
6404:
6405: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6406: arp_message.set_token( 'TABLE_NAME', 'oe_price_adj_attribs', FALSE );
6407:
6408: IF attrib_id_tab.COUNT <> 0 THEN
6409: FORALL i in attrib_id_tab.FIRST..attrib_id_tab.LAST
6402: END IF;
6403: end if;
6404:
6405: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6406: arp_message.set_token( 'TABLE_NAME', 'oe_price_adj_attribs', FALSE );
6407:
6408: IF attrib_id_tab.COUNT <> 0 THEN
6409: FORALL i in attrib_id_tab.FIRST..attrib_id_tab.LAST
6410: UPDATE OE_PRICE_ADJ_ATTRIBS S
6419: ELSE
6420: g_count := 0;
6421: END IF;
6422:
6423: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6424: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6425: EXIT WHEN c1_2%NOTFOUND;
6426:
6427: attrib_id_tab.DELETE;
6420: g_count := 0;
6421: END IF;
6422:
6423: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6424: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6425: EXIT WHEN c1_2%NOTFOUND;
6426:
6427: attrib_id_tab.DELETE;
6428: attrib_value_from_tab.DELETE;
6433:
6434: CLOSE c1_2;
6435:
6436: END IF;
6437: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Price_Adj_Attribs()-' );
6438:
6439: EXCEPTION
6440: when others then
6441: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Price_Adj_Attribs-' );
6437: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Price_Adj_Attribs()-' );
6438:
6439: EXCEPTION
6440: when others then
6441: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Price_Adj_Attribs-' );
6442: raise;
6443:
6444: END OE_Merge_Price_Adj_Attribs;
6445:
6639:
6640: l_profile_val VARCHAR2(30);
6641: BEGIN
6642:
6643: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blanket_Headers()+' );
6644:
6645: IF (process_mode = 'LOCK') THEN
6646:
6647: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
6643: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blanket_Headers()+' );
6644:
6645: IF (process_mode = 'LOCK') THEN
6646:
6647: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
6648: arp_message.set_token( 'TABLE_NAME', 'OE_BLANKET_HEADERS_ALL', FALSE );
6649:
6650: open merge_headers;
6651: close merge_headers;
6644:
6645: IF (process_mode = 'LOCK') THEN
6646:
6647: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
6648: arp_message.set_token( 'TABLE_NAME', 'OE_BLANKET_HEADERS_ALL', FALSE );
6649:
6650: open merge_headers;
6651: close merge_headers;
6652:
6789: end if;
6790: end if;
6791:
6792:
6793: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6794: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_all', FALSE );
6795:
6796: IF hdr_header_id_tab.COUNT <> 0 THEN
6797:
6790: end if;
6791:
6792:
6793: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6794: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_all', FALSE );
6795:
6796: IF hdr_header_id_tab.COUNT <> 0 THEN
6797:
6798: FORALL i in hdr_header_id_tab.FIRST..hdr_header_id_tab.LAST
6817: ELSE
6818: g_count := 0;
6819: END IF;
6820:
6821: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6822: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6823: -- Bug 7379750
6824: IF l_run_parallel_query = 'Y' THEN
6825: EXIT WHEN merge_headers_2%NOTFOUND;
6818: g_count := 0;
6819: END IF;
6820:
6821: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6822: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6823: -- Bug 7379750
6824: IF l_run_parallel_query = 'Y' THEN
6825: EXIT WHEN merge_headers_2%NOTFOUND;
6826: ELSE
6852:
6853: END IF;
6854: customer_id_tab.DELETE;
6855: customer_site_id_tab.DELETE;
6856: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blanket_Headers()-' );
6857:
6858: EXCEPTION
6859: when others then
6860: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Blanket_Headers-' );
6856: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blanket_Headers()-' );
6857:
6858: EXCEPTION
6859: when others then
6860: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Blanket_Headers-' );
6861: raise;
6862:
6863: END OE_Merge_Blanket_Headers;
6864:
6946:
6947: l_profile_val VARCHAR2(30);
6948:
6949: BEGIN
6950: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blkt_Hdrs_Hist()+' );
6951:
6952: -- both customer and site level
6953:
6954: IF( process_mode = 'LOCK' ) THEN
6952: -- both customer and site level
6953:
6954: IF( process_mode = 'LOCK' ) THEN
6955:
6956: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
6957: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
6958:
6959: open c1;
6960: close c1;
6953:
6954: IF( process_mode = 'LOCK' ) THEN
6955:
6956: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
6957: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
6958:
6959: open c1;
6960: close c1;
6961:
7016: and m.set_number = set_num;
7017: end if;
7018:
7019: -- site level update
7020: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7021: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
7022:
7023: UPDATE oe_blanket_headers_hist a
7024: set ship_to_org_id = (select distinct m.customer_site_id
7017: end if;
7018:
7019: -- site level update
7020: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7021: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
7022:
7023: UPDATE oe_blanket_headers_hist a
7024: set ship_to_org_id = (select distinct m.customer_site_id
7025: from ra_customer_merges m
7039: where m.process_flag = 'N'
7040: and m.request_id = req_id
7041: and m.set_number = set_num);
7042: g_count := sql%rowcount;
7043: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7044: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7045:
7046: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
7047: insert into HZ_CUSTOMER_MERGE_LOG (
7040: and m.request_id = req_id
7041: and m.set_number = set_num);
7042: g_count := sql%rowcount;
7043: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7044: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7045:
7046: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
7047: insert into HZ_CUSTOMER_MERGE_LOG (
7048: MERGE_LOG_ID,
7082: and m.request_id = req_id
7083: and m.set_number = set_num;
7084: end if;
7085:
7086: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7087: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
7088:
7089: UPDATE oe_blanket_headers_hist a
7090: set invoice_to_org_id = (select distinct m.customer_site_id
7083: and m.set_number = set_num;
7084: end if;
7085:
7086: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7087: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
7088:
7089: UPDATE oe_blanket_headers_hist a
7090: set invoice_to_org_id = (select distinct m.customer_site_id
7091: from ra_customer_merges m
7105: where m.process_flag = 'N'
7106: and m.request_id = req_id
7107: and m.set_number = set_num);
7108: g_count := sql%rowcount;
7109: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7110: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7111:
7112:
7113: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
7106: and m.request_id = req_id
7107: and m.set_number = set_num);
7108: g_count := sql%rowcount;
7109: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7110: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7111:
7112:
7113: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
7114: insert into HZ_CUSTOMER_MERGE_LOG (
7148: and m.process_flag = 'N'
7149: and m.request_id = req_id
7150: and m.set_number = set_num;
7151: end if;
7152: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7153: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
7154:
7155: UPDATE oe_blanket_headers_hist a
7156: set deliver_to_org_id = (select distinct m.customer_site_id
7149: and m.request_id = req_id
7150: and m.set_number = set_num;
7151: end if;
7152: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7153: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
7154:
7155: UPDATE oe_blanket_headers_hist a
7156: set deliver_to_org_id = (select distinct m.customer_site_id
7157: from ra_customer_merges m
7171: where m.process_flag = 'N'
7172: and m.request_id = req_id
7173: and m.set_number = set_num);
7174: g_count := sql%rowcount;
7175: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7176: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7177:
7178: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7179: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
7172: and m.request_id = req_id
7173: and m.set_number = set_num);
7174: g_count := sql%rowcount;
7175: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7176: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7177:
7178: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7179: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
7180:
7174: g_count := sql%rowcount;
7175: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7176: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7177:
7178: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7179: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
7180:
7181:
7182: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
7175: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7176: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7177:
7178: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7179: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
7180:
7181:
7182: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
7183: insert into HZ_CUSTOMER_MERGE_LOG (
7219: and m.set_number = set_num;
7220: end if;
7221: -- customer level update --
7222:
7223: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7224: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
7225:
7226: UPDATE oe_blanket_headers_hist a
7227: set sold_to_org_id = (select distinct m.customer_id
7220: end if;
7221: -- customer level update --
7222:
7223: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7224: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
7225:
7226: UPDATE oe_blanket_headers_hist a
7227: set sold_to_org_id = (select distinct m.customer_id
7228: from ra_customer_merges m
7244: and m.set_number = set_num);
7245:
7246: g_count := sql%rowcount;
7247:
7248: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7249: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7250:
7251: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7252: arp_message.set_token( 'TABLE_NAME', 'OE_BLANKET_HEADERS_HIST', FALSE );
7245:
7246: g_count := sql%rowcount;
7247:
7248: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7249: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7250:
7251: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7252: arp_message.set_token( 'TABLE_NAME', 'OE_BLANKET_HEADERS_HIST', FALSE );
7253:
7247:
7248: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7249: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7250:
7251: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7252: arp_message.set_token( 'TABLE_NAME', 'OE_BLANKET_HEADERS_HIST', FALSE );
7253:
7254:
7255: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
7248: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7249: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7250:
7251: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7252: arp_message.set_token( 'TABLE_NAME', 'OE_BLANKET_HEADERS_HIST', FALSE );
7253:
7254:
7255: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
7256: insert into HZ_CUSTOMER_MERGE_LOG (
7292: and m.set_number = set_num;
7293: end if;
7294: -- customer level update --
7295:
7296: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7297: arp_message.set_token( 'TABLE_NAME', 'OE_BLANKET_HEADERS_HIST', FALSE );
7298:
7299: UPDATE oe_blanket_headers_hist a
7300: set sold_to_site_use_id = (select distinct m.customer_id
7293: end if;
7294: -- customer level update --
7295:
7296: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7297: arp_message.set_token( 'TABLE_NAME', 'OE_BLANKET_HEADERS_HIST', FALSE );
7298:
7299: UPDATE oe_blanket_headers_hist a
7300: set sold_to_site_use_id = (select distinct m.customer_id
7301: from ra_customer_merges m
7317: and m.set_number = set_num);
7318:
7319: g_count := sql%rowcount;
7320:
7321: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7322: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7323:
7324: END IF;
7325:
7318:
7319: g_count := sql%rowcount;
7320:
7321: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7322: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7323:
7324: END IF;
7325:
7326: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blkt_Hdrs_Hist()-' );
7322: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7323:
7324: END IF;
7325:
7326: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blkt_Hdrs_Hist()-' );
7327:
7328: EXCEPTION
7329: When others then
7330: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Blkt_Hdrs_Hist-' );
7326: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blkt_Hdrs_Hist()-' );
7327:
7328: EXCEPTION
7329: When others then
7330: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Blkt_Hdrs_Hist-' );
7331: raise;
7332:
7333: END OE_Merge_Blkt_Hdrs_Hist;
7334:
7535: customer_site_id_tab num_table_binary_int;
7536:
7537: BEGIN
7538:
7539: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blanket_Lines()+' );
7540:
7541: IF (process_mode = 'LOCK') THEN
7542:
7543: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
7539: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blanket_Lines()+' );
7540:
7541: IF (process_mode = 'LOCK') THEN
7542:
7543: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
7544: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_lines_all', FALSE );
7545:
7546: open merge_lines;
7547: close merge_lines;
7540:
7541: IF (process_mode = 'LOCK') THEN
7542:
7543: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
7544: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_lines_all', FALSE );
7545:
7546: open merge_lines;
7547: close merge_lines;
7548:
7683:
7684: end if;
7685: end if;
7686:
7687: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7688: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_lines_all', FALSE );
7689:
7690: IF line_line_id_tab.COUNT <> 0 THEN
7691: FORALL i in line_line_id_tab.FIRST..line_line_id_tab.LAST
7684: end if;
7685: end if;
7686:
7687: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7688: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_lines_all', FALSE );
7689:
7690: IF line_line_id_tab.COUNT <> 0 THEN
7691: FORALL i in line_line_id_tab.FIRST..line_line_id_tab.LAST
7692: UPDATE OE_BLANKET_LINES_ALL L
7710: ELSE
7711: g_count := 0;
7712: END IF;
7713:
7714: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7715: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7716:
7717: -- Bug 7379750
7718: IF l_run_parallel_query = 'Y' THEN
7711: g_count := 0;
7712: END IF;
7713:
7714: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7715: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7716:
7717: -- Bug 7379750
7718: IF l_run_parallel_query = 'Y' THEN
7719: EXIT WHEN merge_lines_2%NOTFOUND;
7744: END IF;
7745: END IF;
7746: customer_id_tab.DELETE;
7747: customer_site_id_tab.DELETE;
7748: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blanket_Lines()-' );
7749:
7750: EXCEPTION
7751: when others then
7752: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Blanket_Lines-' );
7748: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blanket_Lines()-' );
7749:
7750: EXCEPTION
7751: when others then
7752: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Blanket_Lines-' );
7753: raise;
7754:
7755: END OE_Merge_Blanket_Lines;
7756:
7963: l_profile_val VARCHAR2(30);
7964:
7965: BEGIN
7966:
7967: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blkt_Lines_Hist()+' );
7968:
7969: IF (process_mode = 'LOCK') THEN
7970:
7971: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
7967: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blkt_Lines_Hist()+' );
7968:
7969: IF (process_mode = 'LOCK') THEN
7970:
7971: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
7972: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_lines_hist', FALSE );
7973:
7974: open merge_lines_history;
7975: close merge_lines_history;
7968:
7969: IF (process_mode = 'LOCK') THEN
7970:
7971: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
7972: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_lines_hist', FALSE );
7973:
7974: open merge_lines_history;
7975: close merge_lines_history;
7976:
8113:
8114: end if;
8115: end if;
8116:
8117: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
8118: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_lines_hist', FALSE );
8119:
8120: IF lhst_line_id_tab.COUNT <> 0 THEN
8121: FORALL i in lhst_line_id_tab.FIRST..lhst_line_id_tab.LAST
8114: end if;
8115: end if;
8116:
8117: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
8118: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_lines_hist', FALSE );
8119:
8120: IF lhst_line_id_tab.COUNT <> 0 THEN
8121: FORALL i in lhst_line_id_tab.FIRST..lhst_line_id_tab.LAST
8122: UPDATE OE_BLANKET_LINES_HIST LHIST
8139: ELSE
8140: g_count := 0;
8141: END IF;
8142:
8143: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
8144: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
8145:
8146: -- Bug 7379750
8147: IF l_run_parallel_query = 'Y' THEN
8140: g_count := 0;
8141: END IF;
8142:
8143: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
8144: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
8145:
8146: -- Bug 7379750
8147: IF l_run_parallel_query = 'Y' THEN
8148: EXIT WHEN merge_lines_history_2%NOTFOUND;
8177:
8178: END IF;
8179: customer_id_tab.DELETE;
8180: customer_site_id_tab.DELETE;
8181: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blkt_Lines_Hist()-' );
8182:
8183: EXCEPTION
8184: when others then
8185: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Blkt_Lines_Hist-' );
8181: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blkt_Lines_Hist()-' );
8182:
8183: EXCEPTION
8184: when others then
8185: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Blkt_Lines_Hist-' );
8186: raise;
8187:
8188: END OE_Merge_Blkt_Lines_Hist;
8189:
8214:
8215: l_profile_val VARCHAR2(30);
8216:
8217: BEGIN
8218: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_OI_Tracking()+' );
8219: IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL < '110510' THEN
8220: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_OI_Tracking()-' );
8221: Return;
8222: END IF;
8216:
8217: BEGIN
8218: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_OI_Tracking()+' );
8219: IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL < '110510' THEN
8220: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_OI_Tracking()-' );
8221: Return;
8222: END IF;
8223:
8224: -- both customer and site level
8224: -- both customer and site level
8225:
8226: IF( process_mode = 'LOCK' ) THEN
8227:
8228: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
8229: arp_message.set_token( 'TABLE_NAME', 'OE_EM_INFORMATION_ALL', FALSE );
8230:
8231: open c1;
8232: close c1;
8225:
8226: IF( process_mode = 'LOCK' ) THEN
8227:
8228: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
8229: arp_message.set_token( 'TABLE_NAME', 'OE_EM_INFORMATION_ALL', FALSE );
8230:
8231: open c1;
8232: close c1;
8233:
8238: l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
8239:
8240:
8241: -- customer level update
8242: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
8243: arp_message.set_token( 'TABLE_NAME', 'OE_EM_INFORMATION_ALL', FALSE );
8244:
8245: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
8246: insert into HZ_CUSTOMER_MERGE_LOG (
8239:
8240:
8241: -- customer level update
8242: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
8243: arp_message.set_token( 'TABLE_NAME', 'OE_EM_INFORMATION_ALL', FALSE );
8244:
8245: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
8246: insert into HZ_CUSTOMER_MERGE_LOG (
8247: MERGE_LOG_ID,
8306:
8307:
8308: g_count := sql%rowcount;
8309:
8310: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
8311: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
8312:
8313: END IF;
8314:
8307:
8308: g_count := sql%rowcount;
8309:
8310: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
8311: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
8312:
8313: END IF;
8314:
8315: arp_message.set_line( 'OE_CUST_MERGE. OE_Merge_OI_Tracking()-' );
8311: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
8312:
8313: END IF;
8314:
8315: arp_message.set_line( 'OE_CUST_MERGE. OE_Merge_OI_Tracking()-' );
8316:
8317: EXCEPTION
8318: When others then
8319: arp_message.set_error( 'OE_CUST_MERGE. OE_Merge_OI_Tracking-' );
8315: arp_message.set_line( 'OE_CUST_MERGE. OE_Merge_OI_Tracking()-' );
8316:
8317: EXCEPTION
8318: When others then
8319: arp_message.set_error( 'OE_CUST_MERGE. OE_Merge_OI_Tracking-' );
8320: raise;
8321:
8322: END OE_Merge_OI_Tracking;
8323:
8329: )
8330: IS
8331:
8332: BEGIN
8333: arp_message.set_line( 'OE_CUST_MERGE.OE_Order_Merge()+' );
8334:
8335: OE_Merge_Headers(Req_Id, Set_Num, Process_Mode);
8336: OE_Merge_Lines(Req_Id, Set_Num, Process_Mode);
8337: OE_Merge_Header_History(Req_Id, Set_Num, Process_Mode);
8345: OE_Merge_Blanket_Lines(Req_Id, Set_Num, Process_Mode);
8346: OE_Merge_Blkt_Hdrs_Hist(Req_Id, Set_Num, Process_Mode);
8347: OE_Merge_Blkt_Lines_Hist(Req_Id, Set_Num, Process_Mode);
8348: OE_Merge_OI_Tracking (Req_Id, Set_Num, Process_Mode);
8349: arp_message.set_line( 'calling dbi now...' );
8350: oe_update_dbi_log();
8351: OE_Update_Credit_Hold_Flag(); --ER 3095805
8352: arp_message.set_line( 'OE_CUST_MERGE.Order_Merge()-' );
8353:
8348: OE_Merge_OI_Tracking (Req_Id, Set_Num, Process_Mode);
8349: arp_message.set_line( 'calling dbi now...' );
8350: oe_update_dbi_log();
8351: OE_Update_Credit_Hold_Flag(); --ER 3095805
8352: arp_message.set_line( 'OE_CUST_MERGE.Order_Merge()-' );
8353:
8354: EXCEPTION
8355: When others then
8356: arp_message.set_error( 'OE_CUST_MERGE.OE_Order_Merge-' );
8352: arp_message.set_line( 'OE_CUST_MERGE.Order_Merge()-' );
8353:
8354: EXCEPTION
8355: When others then
8356: arp_message.set_error( 'OE_CUST_MERGE.OE_Order_Merge-' );
8357: raise;
8358:
8359: END OE_Order_Merge;
8360:
8388:
8389: BEGIN
8390:
8391: IF l_dbi_profile = 'Y' then
8392: arp_message.set_line(' inside update_dbi count hder_id_tab='||dbi_header_header_id_tab.COUNT);
8393: arp_message.set_line(' count lin hdr_id_tab='||dbi_line_header_id_tab.COUNT);
8394:
8395:
8396: IF dbi_header_header_id_tab.COUNT <> 0 then
8389: BEGIN
8390:
8391: IF l_dbi_profile = 'Y' then
8392: arp_message.set_line(' inside update_dbi count hder_id_tab='||dbi_header_header_id_tab.COUNT);
8393: arp_message.set_line(' count lin hdr_id_tab='||dbi_line_header_id_tab.COUNT);
8394:
8395:
8396: IF dbi_header_header_id_tab.COUNT <> 0 then
8397: FOR i in dbi_header_header_id_tab.FIRST..dbi_header_header_id_tab.LAST
8395:
8396: IF dbi_header_header_id_tab.COUNT <> 0 then
8397: FOR i in dbi_header_header_id_tab.FIRST..dbi_header_header_id_tab.LAST
8398: LOOP
8399: arp_message.set_line('header.header_id:'||dbi_header_header_id_tab(i));
8400: end loop;
8401: end if;
8402:
8403: IF dbi_line_header_id_tab.COUNT <> 0 then
8402:
8403: IF dbi_line_header_id_tab.COUNT <> 0 then
8404: FOR i in dbi_line_header_id_tab.FIRST..dbi_line_header_id_tab.LAST
8405: LOOP
8406: arp_message.set_line('line.header_id:'||dbi_line_header_id_tab(i));
8407: end loop;
8408: end if;
8409:
8410: IF dbi_line_line_id_tab.COUNT <> 0 then
8409:
8410: IF dbi_line_line_id_tab.COUNT <> 0 then
8411: FOR i in dbi_line_line_id_tab.FIRST..dbi_line_line_id_tab.LAST
8412: LOOP
8413: arp_message.set_line('line.line_id:'||dbi_line_line_id_tab(i));
8414: end loop;
8415: end if;
8416:
8417:
8418:
8419: -- Removing Those Line Ids for whose headers already exists from hdr merge
8420: IF dbi_line_header_id_tab.COUNT <> 0 then
8421:
8422: arp_message.set_line('count line_header_id_tab='||dbi_line_header_id_tab.COUNT);
8423: FOR i in dbi_line_header_id_tab.FIRST..dbi_line_header_id_tab.LAST
8424: LOOP
8425:
8426: arp_message.set_line('count header_id_tab='||dbi_header_id_tab.COUNT);
8422: arp_message.set_line('count line_header_id_tab='||dbi_line_header_id_tab.COUNT);
8423: FOR i in dbi_line_header_id_tab.FIRST..dbi_line_header_id_tab.LAST
8424: LOOP
8425:
8426: arp_message.set_line('count header_id_tab='||dbi_header_id_tab.COUNT);
8427: IF dbi_header_id_tab.EXISTS(dbi_line_header_id_tab(i)) THEN
8428: arp_message.set_line( 'DBI:matched line.header_id and header.header_id of '||dbi_line_header_id_tab(i)||', deleting');
8429: dbi_line_status_tab(i) := 'N';
8430: END IF;
8424: LOOP
8425:
8426: arp_message.set_line('count header_id_tab='||dbi_header_id_tab.COUNT);
8427: IF dbi_header_id_tab.EXISTS(dbi_line_header_id_tab(i)) THEN
8428: arp_message.set_line( 'DBI:matched line.header_id and header.header_id of '||dbi_line_header_id_tab(i)||', deleting');
8429: dbi_line_status_tab(i) := 'N';
8430: END IF;
8431:
8432: END LOOP;
8434:
8435: IF dbi_line_status_tab.COUNT <> 0 then
8436: FOR i in dbi_line_status_tab.FIRST..dbi_line_status_tab.LAST
8437: LOOP
8438: arp_message.set_line( 'status '||i|| '='||dbi_line_status_tab(i));
8439: END LOOP;
8440: END IF;
8441:
8442: OPEN c_set_of_books_id;
8471: WHERE l.header_id = dbi_header_header_id_tab(i)
8472: AND h.header_id = l.header_id
8473: AND h.booked_flag = 'Y';
8474:
8475: arp_message.set_line(' Hdr insert count='||sql%rowcount);
8476:
8477: END IF; -- because of hdr merge
8478:
8479:
8498: FROM OE_ORDER_LINES_ALL l
8499: WHERE l.line_id = dbi_line_line_id_tab(i)
8500: AND l.booked_flag = 'Y'
8501: AND dbi_line_status_tab(i) = 'Y';
8502: arp_message.set_line(' line insert count='||sql%rowcount);
8503:
8504: END IF; -- because of line merge
8505:
8506: END IF; -- if dbi is installed
8514: dbi_line_update_date_tab.DELETE;
8515: dbi_line_status_tab.DELETE;
8516: dbi_header_id_tab.DELETE;
8517:
8518: arp_message.set_line(' End Update DBI');
8519:
8520: EXCEPTION
8521: WHEN OTHERS THEN
8522: oe_debug_pub.add('when others oe_cust_merge.update_dbi_log'||SQLERRM||