[Home] [Help]
1: PACKAGE BODY ARP_CMERGE_MASTER as
2: /* $Header: ARHCMSTB.pls 120.27.12000000.5 2007/10/12 11:15:14 kguggila ship $ */
3:
4: --Global variable to catch the name of product raising exception
5: G_PRODUCT_RAISING_EXCEPTION varchar2(255) ;
232: BEGIN
233:
234: -- execute immediate 'alter session disable commit in procedure'; bug 3897822
235:
236: arp_message.set_line( 'ARP_CMERGE_MASTER.merge_products()+' );
237:
238:
239: g_excluded_apps := FND_PROFILE.VALUE('HZ_CMERGE_EXCLUDED_APPS');
240:
243: END IF;
244:
245: IF g_excluded_apps = 'ALL' THEN
246: status := 0;
247: arp_message.set_line( 'ARP_CMERGE_MASTER.merge_products()-' );
248: RETURN;
249: ELSE
250: g_excluded_apps := replace(g_excluded_apps,' ')||',';
251: END IF;
428: 'ARP_GENERIC_CMERGE', 'merge',
429: req_id, set_num, process_mode,'GENERIC');
430:
431:
432: arp_message.set_line( 'ARP_CMERGE_MASTER.merge_products()-' );
433:
434: -- execute immediate 'alter session enable commit in procedure'; bug 3897822
435:
436: EXCEPTION
572: IF instrb(g_excluded_apps,product_code||',') > 0 THEN
573: RETURN;
574: END IF;
575:
576: arp_message.set_line( 'ARP_CMERGE_MASTER.merge_product(' ||
577: package_name || ')+' );
578:
579: BEGIN
580: c := dbms_sql.open_cursor;
709: WHEN OTHERS THEN
710: -- G_PRODUCT_RAISING_EXCEPTION := product_code||'.'|| package_name || '.' || api_name ;
711: raise;
712:
713: arp_message.set_line( 'ARP_CMERGE_MASTER.merge_product(' || package_name || ')-' );
714:
715: END merge_product;
716:
717: /*===========================================================================+
774: l_schema VARCHAR2(255);
775: l_tmp VARCHAR2(2000);
776: BEGIN
777:
778: arp_message.set_line( 'ARP_CMERGE_MASTER.merge_crm_products()+' );
779:
780: --To minimize dependency, we need to call CRM's merge procedure
781: --dynanmically. First we need to check if table jtf_hooks_data
782: --exists, if yes, we need to see if there are crm's merge procedures
800: IF l_exist = 'Y' THEN
801:
802: cur_sql := 'SELECT hook_package, hook_api,product_code ' ||
803: 'FROM jtf_hooks_data '||
804: 'WHERE package_name = ''ARP_CMERGE_MASTER'' ' ||
805: 'AND api_name = ''MERGE_PRODUCTS'' ' ||
806: 'AND execute_flag = ''Y'' ' ||
807: 'ORDER BY execution_order ';
808:
830: CLOSE crm_products;
831:
832: END IF;
833:
834: arp_message.set_line( 'ARP_CMERGE_MASTER.merge_crm_products()-' );
835:
836: EXCEPTION
837:
838: WHEN OTHERS THEN
835:
836: EXCEPTION
837:
838: WHEN OTHERS THEN
839: arp_message.set_error( 'ARP_CMERGE_MASTER.merge_crm_products');
840:
841: IF crm_products%ISOPEN THEN
842: CLOSE crm_products;
843: END IF;
881: ) IS
882:
883: BEGIN
884:
885: arp_message.set_line( 'ARP_CMERGE_MASTER.merge_customers()+' );
886:
887: --merge accout site uses, sites, account etc.
888: arp_cmerge_arcus.merge(req_id, set_num, process_mode);
889:
891: arp_cmerge_arcpf.merge(req_id, set_num, process_mode);
892:
893: status := 0;
894:
895: arp_message.set_line( 'ARP_CMERGE_MASTER.merge_customers()-' );
896:
897: EXCEPTION
898:
899: WHEN OTHERS THEN
896:
897: EXCEPTION
898:
899: WHEN OTHERS THEN
900: arp_message.set_error( 'ARP_CMERGE_MASTER.merge_customers');
901: status := -1;
902:
903: END merge_customers;
904:
936: ) IS
937:
938: BEGIN
939:
940: arp_message.set_line( 'ARP_CMERGE_MASTER.delete_rows()+' );
941:
942: --delete customer tables
943: arp_cmerge_arcus.delete_rows( req_id, set_num );
944:
946: arp_cmerge_arcpf.delete_rows( req_id, set_num );
947:
948: status := 0;
949:
950: arp_message.set_line( 'ARP_CMERGE_MASTER.delete_rows()-' );
951:
952: EXCEPTION
953:
954: WHEN OTHERS THEN
951:
952: EXCEPTION
953:
954: WHEN OTHERS THEN
955: arp_message.set_error( 'ARP_CMERGE_MASTER.delete_rows');
956: status := -1;
957:
958: END delete_rows;
959:
1029: l_header_id_t customer_merge_header_id_tab;
1030: l_process_flag_t process_flag_tab;
1031: BEGIN
1032:
1033: arp_message.set_line( 'ARP_CMERGE_MASTER.mark_merge_rows()+' );
1034:
1035: /* >>>MODIFY: need to pick up terminated rows as well */
1036:
1037: /** N/A
1235: WHERE mh.process_flag = l_new_process_flag
1236: AND request_id = req_id
1237: AND mh.priority = p_priority); --3897822
1238:
1239: arp_message.set_line( 'ARP_CMERGE_MASTER.mark_merge_rows()-' );
1240:
1241: EXCEPTION
1242:
1243: WHEN OTHERS THEN
1240:
1241: EXCEPTION
1242:
1243: WHEN OTHERS THEN
1244: arp_message.set_error( 'ARP_CMERGE_MASTER.mark_merge_rows');
1245: RAISE;
1246:
1247: END mark_merge_rows;
1248:
1278: ) IS
1279:
1280: BEGIN
1281:
1282: arp_message.set_line( 'ARP_CMERGE_MASTER.add_request()+' );
1283:
1284: INSERT into ar_conc_process_requests
1285: (request_id, concurrent_program_name)
1286: VALUES
1285: (request_id, concurrent_program_name)
1286: VALUES
1287: (req_id, program_name);
1288:
1289: arp_message.set_line( 'ARP_CMERGE_MASTER.add_request()-' );
1290:
1291: EXCEPTION
1292:
1293: WHEN OTHERS THEN
1290:
1291: EXCEPTION
1292:
1293: WHEN OTHERS THEN
1294: arp_message.set_error( 'ARP_CMERGE_MASTER.add_request' );
1295: RAISE;
1296:
1297: END add_request;
1298:
1434: invalid_merges check_invalid_merges%ROWTYPE;
1435: error_msg VARCHAR2(240); -- Bug No: 3743818
1436: BEGIN
1437:
1438: arp_message.set_line( 'ARP_CMERGE_MASTER.validate_merges()+' );
1439:
1440: FOR missing_sites IN val_all_sites_merged LOOP
1441:
1442: --Mark Invalid Merges
1487: --End Of Bug No: 3743818
1488:
1489: END LOOP;
1490:
1491: arp_message.set_line( 'ARP_CMERGE_MASTER.validate_merges()-' );
1492:
1493: EXCEPTION
1494:
1495: WHEN OTHERS THEN
1492:
1493: EXCEPTION
1494:
1495: WHEN OTHERS THEN
1496: arp_message.set_error( 'ARP_CMERGE_MASTER.validate_merges' );
1497: RAISE;
1498:
1499: END validate_merges;
1500:
1545: v_last_set NUMBER := 1;
1546:
1547: BEGIN
1548:
1549: arp_message.set_line( 'ARP_CMERGE_MASTER.partiton_merge_data()+' );
1550:
1551: v_set_size := fnd_profile.value('AR_CMERGE_SET_SIZE');
1552:
1553: OPEN partition;
1585:
1586: last_set := v_last_set;
1587:
1588:
1589: arp_message.set_line( 'ARP_CMERGE_MASTER.partiton_merge_data()-' );
1590:
1591: EXCEPTION
1592:
1593: WHEN OTHERS THEN
1590:
1591: EXCEPTION
1592:
1593: WHEN OTHERS THEN
1594: arp_message.set_error( 'ARP_CMERGE_MASTER.partition_merge_date' );
1595: RAISE;
1596:
1597: END partiton_merge_data;
1598:
1648: l_count number;
1649:
1650: BEGIN
1651:
1652: arp_message.set_line( 'ARP_CMERGE_MASTER.unpartiton_merge_data()+' );
1653:
1654: v_set_size := 1001;
1655:
1656: OPEN partition;
1700:
1701: last_set := v_last_set;
1702:
1703:
1704: arp_message.set_line( 'ARP_CMERGE_MASTER.partiton_merge_data()-' );
1705:
1706: EXCEPTION
1707:
1708: WHEN OTHERS THEN
1705:
1706: EXCEPTION
1707:
1708: WHEN OTHERS THEN
1709: arp_message.set_error( 'ARP_CMERGE_MASTER.partition_merge_date' );
1710: RAISE;
1711:
1712: END unpartiton_merge_data;
1713: /*===========================================================================+
1748: ) IS
1749:
1750: BEGIN
1751:
1752: arp_message.set_line( 'ARP_CMERGE_MASTER.done_merge_rows()+' );
1753:
1754: --delete customer alternative names
1755: arp_cmerge_master.delete_customer_alt_names ( req_id , set_num ) ;
1756:
1751:
1752: arp_message.set_line( 'ARP_CMERGE_MASTER.done_merge_rows()+' );
1753:
1754: --delete customer alternative names
1755: arp_cmerge_master.delete_customer_alt_names ( req_id , set_num ) ;
1756:
1757: UPDATE ra_customer_merges
1758: SET process_flag = 'Y',
1759: last_update_date = sysdate,
1803:
1804: --Added for debug purpose, should be always commented out.
1805: --read_temporary_table;
1806:
1807: arp_message.set_line( 'ARP_CMERGE_MASTER.done_merge_rows()-' );
1808:
1809: EXCEPTION
1810:
1811: WHEN OTHERS THEN
1808:
1809: EXCEPTION
1810:
1811: WHEN OTHERS THEN
1812: arp_message.set_error( 'ARP_CMERGE_MASTER.done_merge_rows' );
1813: RAISE;
1814:
1815: END done_merge_rows;
1816:
1855: ) IS
1856:
1857: BEGIN
1858:
1859: --arp_message.set_line( 'ARP_CMERGE_MASTER.reset_merge_rows()+' );
1860:
1861: --If its database problem, set back status to 'SAVED'
1862: --Bug fix 2669389
1863: if (p_process_flag = 'SAVED') then
1966: AND m.set_number = set_num );
1967:
1968: end if;
1969:
1970: --arp_message.set_line( 'ARP_CMERGE_MASTER.reset_merge_rows()-' );
1971:
1972: EXCEPTION
1973:
1974: WHEN OTHERS THEN
1971:
1972: EXCEPTION
1973:
1974: WHEN OTHERS THEN
1975: --arp_message.set_error( 'ARP_CMERGE_MASTER.reset_merge_rows' );
1976: RAISE;
1977:
1978: END reset_merge_rows;
1979:
2013: ) IS
2014:
2015: BEGIN
2016:
2017: --arp_message.set_line( 'ARP_CMERGE_MASTER.clear_error_merge_rows()+' );
2018:
2019: --Bug 1518688: should not reset request_id to NULL.
2020: --Set process_flag = 'FAILED' indicate this is a failed merge, we need
2021: --to pick it up next time.
2064: WHERE mh.request_id = req_id
2065: AND mh.process_flag = 'FAILED' )
2066: AND process_flag in ('ERROR 1', 'ERROR 2');
2067:
2068: --arp_message.set_line( 'ARP_CMERGE_MASTER.clear_error_merge_rows()-' );
2069:
2070: EXCEPTION
2071:
2072: WHEN OTHERS THEN
2069:
2070: EXCEPTION
2071:
2072: WHEN OTHERS THEN
2073: --arp_message.set_error( 'ARP_CMERGE_MASTER.clear_error_merge_rows' );
2074: RAISE;
2075:
2076: END clear_error_merge_rows;
2077:
2105: ) IS
2106:
2107: BEGIN
2108:
2109: --arp_message.set_line( 'ARP_CMERGE_MASTER.remove_request()+' );
2110:
2111: DELETE FROM ar_conc_process_requests
2112: WHERE request_id = req_id;
2113:
2110:
2111: DELETE FROM ar_conc_process_requests
2112: WHERE request_id = req_id;
2113:
2114: --arp_message.set_line( 'ARP_CMERGE_MASTER.remove_request()-' );
2115:
2116: EXCEPTION
2117:
2118: WHEN OTHERS THEN
2115:
2116: EXCEPTION
2117:
2118: WHEN OTHERS THEN
2119: --arp_message.set_error( 'ARP_CMERGE_MASTER.remove_request' );
2120: RAISE;
2121:
2122: END remove_request;
2123:
2165: ORDER BY duplicate_id ;
2166:
2167: BEGIN
2168:
2169: arp_message.set_line( 'ARP_CMERGE_MASTER.alt_name_deletion()+' );
2170:
2171: -- Bug 2092530: Removed condition that checked the now-obsolete
2172: -- 'AR_ALT_NAME_SEARCH' profile option before executing the delete.
2173:
2204:
2205: END LOOP;
2206: CLOSE alt_names_deletion;
2207:
2208: arp_message.set_line( 'ARP_CMERGE_MASTER.alt_name_deletion()-' );
2209:
2210: EXCEPTION
2211:
2212: WHEN OTHERS THEN
2209:
2210: EXCEPTION
2211:
2212: WHEN OTHERS THEN
2213: arp_message.set_error( 'ARP_CMERGE_MASTER.delete_customer_alt_names' );
2214: RAISE;
2215:
2216: END delete_customer_alt_names;
2217:
2306: part_delete VARCHAR2 DEFAULT 'N') IS
2307:
2308: BEGIN
2309:
2310: arp_message.set_line( 'ARP_CMERGE_MASTER.Veto_Delete()+' );
2311:
2312: /*--Unset the delete_duplicate_flag in ra_customer_merges --*/
2313: UPDATE ra_customer_merges m
2314: SET delete_duplicate_flag = 'N'
2524: AND m.set_number = set_num);
2525:
2526: arp_message.set_line( SQL%ROWCOUNT || ' '|| 'Rows updated in RA_CUSTOMER_MERGE_HEADERS with veto reason :' ||' '|| veto_reason );
2527:
2528: arp_message.set_line( 'ARP_CMERGE_MASTER.Veto_Delete()-' );
2529:
2530: END veto_delete;
2531:
2532: --4230396
2539: FROM ra_customer_merge_headers
2540: WHERE request_id = p_req_id
2541: AND process_flag = 'Y';
2542: BEGIN
2543: arp_message.set_line( 'ARP_CMERGE_MASTER.raise_events() +');
2544:
2545: OPEN merges;
2546: LOOP
2547: FETCH merges into l_header_id;
2562: p_parameters => l_list );
2563: l_list.DELETE;
2564: END LOOP;
2565: CLOSE merges;
2566: arp_message.set_line( 'ARP_CMERGE_MASTER.raise_events() -');
2567: EXCEPTION
2568: WHEN OTHERS THEN
2569: arp_message.set_error( 'ARP_CMERGE_MASTER.raise_events' );
2570: RAISE;
2565: CLOSE merges;
2566: arp_message.set_line( 'ARP_CMERGE_MASTER.raise_events() -');
2567: EXCEPTION
2568: WHEN OTHERS THEN
2569: arp_message.set_error( 'ARP_CMERGE_MASTER.raise_events' );
2570: RAISE;
2571: END raise_events;
2572:
2573: END ARP_CMERGE_MASTER;
2569: arp_message.set_error( 'ARP_CMERGE_MASTER.raise_events' );
2570: RAISE;
2571: END raise_events;
2572:
2573: END ARP_CMERGE_MASTER;