[Home] [Help]
203: ELSE
204: l_merge_type2 := 'PARTY_MERGE';
205: END IF;
206:
207: -- call table-handler to insert the record in HZ_MERGE_PARTIES
208: HZ_MERGE_PARTIES_PKG.INSERT_ROW(
209: px_BATCH_PARTY_ID => l_batch_party_id,
210: p_batch_id => l_batch_id,
211: p_merge_type => l_merge_type2,
204: l_merge_type2 := 'PARTY_MERGE';
205: END IF;
206:
207: -- call table-handler to insert the record in HZ_MERGE_PARTIES
208: HZ_MERGE_PARTIES_PKG.INSERT_ROW(
209: px_BATCH_PARTY_ID => l_batch_party_id,
210: p_batch_id => l_batch_id,
211: p_merge_type => l_merge_type2,
212: p_from_party_id => l_merge_from,
264:
265: l_batch_party_id := null;
266: l_merge_from := l_merge_to;
267:
268: -- call table-handler to insert the record in HZ_MERGE_PARTIES
269: HZ_MERGE_PARTIES_PKG.INSERT_ROW(
270: px_BATCH_PARTY_ID => l_batch_party_id,
271: p_batch_id => l_batch_id,
272: p_merge_type => 'SAME_PARTY_MERGE',
265: l_batch_party_id := null;
266: l_merge_from := l_merge_to;
267:
268: -- call table-handler to insert the record in HZ_MERGE_PARTIES
269: HZ_MERGE_PARTIES_PKG.INSERT_ROW(
270: px_BATCH_PARTY_ID => l_batch_party_id,
271: p_batch_id => l_batch_id,
272: p_merge_type => 'SAME_PARTY_MERGE',
273: p_from_party_id => l_merge_from,
283: END IF;
284: if p_default_mapping = 'Y'
285: then
286: -- call suggested default to populate suggested default mapping
287: -- to temp table HZ_MERGE_PARTIES_SUGG and HZ_MERGE_PARTYDTLS_SUGG
288: suggested_defaults(l_batch_id, x_return_status, x_msg_count, x_msg_data);
289:
290: IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
291: ROLLBACK to create_merge_batch;
561: SAVEPOINT map_detail_record;
562:
563: SELECT distinct(batch_id)
564: INTO l_batch_id
565: FROM hz_merge_parties
566: WHERE batch_party_id = p_batch_party_id;
567:
568: IF (p_batch_party_id is null) OR (l_batch_id IS NULL )
569: OR (p_entity is null) OR (p_from_entity_id IS NULL) THEN
591: IF p_from_entity_id = l_merge_to THEN
592: /* Check if any entity is merged to p_from_entity_id */
593: SELECT count(1) INTO l_tmp
594: FROM hz_merge_party_details md1, hz_merge_party_details md2,
595: hz_merge_parties mp1, hz_merge_parties mp2
596: where md1.batch_party_id=p_batch_party_id
597: AND md1.batch_party_id = mp1.batch_party_id
598: AND mp1.batch_id = mp2.batch_id
599: AND md2.batch_party_id=mp2.batch_party_id
603:
604: /* Check if p_to_entity_id is merged to another entity */
605: SELECT count(1) INTO l_tmp2
606: FROM hz_merge_party_details md1, hz_merge_party_details md2,
607: hz_merge_parties mp1, hz_merge_parties mp2
608: where md1.batch_party_id=p_batch_party_id
609: AND md1.batch_party_id = mp1.batch_party_id
610: AND mp1.batch_id = mp2.batch_id
611: AND md2.batch_party_id=mp2.batch_party_id
684: l_to_rel_party_id :=
685: HZ_MERGE_UTIL.get_reln_party_id(p_to_entity_id);
686:
687: SELECT count(*) into l_rel_party_count
688: FROM hz_merge_parties
689: WHERE batch_id = l_batch_id
690: AND merge_type = 'PARTY_MERGE'
691: AND from_party_id = l_from_rel_party_id;
692:
690: AND merge_type = 'PARTY_MERGE'
691: AND from_party_id = l_from_rel_party_id;
692:
693: /* Clean up merge parties */
694: DELETE FROM HZ_MERGE_PARTIES
695: WHERE batch_id = l_batch_id
696: AND merge_type = 'PARTY_MERGE'
697: AND (from_party_id = l_from_rel_party_id
698: OR to_party_id = l_from_rel_party_id);
727:
728: IF l_from_rel_party_id IS NOT NULL AND
729: l_to_rel_party_id IS NOT NULL THEN
730:
731: HZ_MERGE_PARTIES_PKG.Insert_Row(
732: rel_batch_party_id,
733: l_BATCH_ID,
734: 'PARTY_MERGE',
735: l_from_rel_party_id,
754:
755: IF l_rel_party_count > 0 THEN
756:
757: select batch_party_id into rel_batch_party_id
758: from hz_merge_parties
759: where batch_id = l_batch_id
760: and merge_type = 'PARTY_MERGE'
761: and from_party_id = l_from_rel_party_id;
762:
759: where batch_id = l_batch_id
760: and merge_type = 'PARTY_MERGE'
761: and from_party_id = l_from_rel_party_id;
762:
763: HZ_MERGE_PARTIES_PKG.delete_Row(rel_batch_party_id);
764:
765: DELETE FROM hz_merge_party_details
766: WHERE batch_party_id = rel_batch_party_id;
767:
809: p_object_version_number := l_object_version_number;
810:
811: SELECT batch_id
812: INTO l_dup_set_id
813: FROM hz_merge_parties
814: WHERE batch_party_id = p_batch_party_id
815: AND ROWNUM = 1;
816:
817: UPDATE HZ_DUP_SETS
919: l_batch_id NUMBER;
920: l_from_party_id NUMBER;
921: l_to_party_id NUMBER;
922:
923: l_merge_type HZ_MERGE_PARTIES.MERGE_TYPE%TYPE;
924: l_status HZ_MERGE_BATCH.BATCH_STATUS%TYPE;
925: l_from_site_party_id NUMBER;
926: l_to_site_party_id NUMBER;
927: l_from_rel_type HZ_RELATIONSHIPS.RELATIONSHIP_TYPE%TYPE;
1013:
1014: --Get the batch_id and party_id for the same party merge
1015: SELECT DISTINCT batch_id, from_party_id, to_party_id ,merge_type
1016: INTO l_batch_id, l_from_party_id, l_to_party_id , l_merge_type
1017: FROM hz_merge_parties
1018: WHERE batch_party_id = p_batch_party_id;
1019:
1020: ---Check for valid batch id
1021: IF (p_batch_party_id is null) or (l_batch_id is null )
1197: l_to_rel_party_id :=
1198: HZ_MERGE_UTIL.get_reln_party_id(p_to_entity_id);
1199:
1200: SELECT count(1) INTO l_rel_party_count
1201: FROM hz_merge_parties
1202: WHERE batch_id = l_batch_id
1203: AND merge_type = 'PARTY_MERGE'
1204: AND from_party_id = l_from_rel_party_id;
1205:
1210:
1211: --Insert parties if the rel party is not present already
1212: IF l_rel_party_count = 0 THEN
1213:
1214: HZ_MERGE_PARTIES_PKG.Insert_Row(
1215: rel_batch_party_id,
1216: l_batch_id,
1217: 'PARTY_MERGE',
1218: l_from_rel_party_id,
1243: l_to_rel_party_id :=
1244: HZ_MERGE_UTIL.get_reln_party_id(p_to_entity_id);
1245:
1246: SELECT count(1) INTO l_rel_party_count
1247: FROM hz_merge_parties
1248: WHERE batch_id = l_batch_id
1249: AND merge_type = 'PARTY_MERGE'
1250: AND from_party_id = l_from_rel_party_id;
1251:
1252:
1253: IF l_rel_party_count > 0 THEN
1254:
1255: SELECT batch_party_id into rel_batch_party_id
1256: FROM hz_merge_parties
1257: WHERE batch_id = l_batch_id
1258: AND merge_type = 'PARTY_MERGE'
1259: AND from_party_id = l_from_rel_party_id;
1260:
1257: WHERE batch_id = l_batch_id
1258: AND merge_type = 'PARTY_MERGE'
1259: AND from_party_id = l_from_rel_party_id;
1260:
1261: HZ_MERGE_PARTIES_PKG.delete_Row(rel_batch_party_id);
1262:
1263: DELETE FROM hz_merge_party_details
1264: WHERE batch_party_id = rel_batch_party_id;
1265:
1274: SET STATUS = 'MAPPING',
1275: LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
1276: LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
1277: LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
1278: WHERE DUP_SET_ID = (SELECT batch_id FROM hz_merge_parties
1279: WHERE batch_party_id = p_batch_party_id
1280: AND ROWNUM = 1);
1281:
1282: UPDATE HZ_MERGE_BATCH
1283: SET batch_status = 'IN_PROCESS',
1284: LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
1285: LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
1286: LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY
1287: WHERE BATCH_ID = (SELECT batch_id FROM hz_merge_parties
1288: WHERE batch_party_id = p_batch_party_id
1289: AND ROWNUM = 1);
1290:
1291: -- standard call to get message count and if count is 1, get message info.
1618: l_from_reln_party_id := l_to_reln_party_id;
1619: l_to_reln_party_id := l_id;
1620: end if;
1621: END IF; --Bug6703948
1622: HZ_MERGE_PARTIES_PKG.Insert_Row(
1623: l_batch_party_id,
1624: p_BATCH_ID,
1625: 'PARTY_MERGE',
1626: l_from_reln_party_id,
1691: l_case VARCHAR2(1);
1692:
1693: BEGIN
1694: SELECT dset.merge_type INTO l_merge_type
1695: FROM HZ_DUP_SETS dset,HZ_MERGE_PARTIES mpar
1696: WHERE dset.dup_set_id = mpar.batch_id
1697: AND mpar.batch_party_id = p_batch_party_id;
1698:
1699: IF l_merge_type = 'SAME_PARTY_MERGE' THEN
1778: AND object_table_name = 'HZ_PARTIES'
1779: AND (merge_type ='S' OR actual_content_source <> 'DNB')--Bug No. 4114254
1780: AND not exists
1781: ( select 1
1782: from HZ_MERGE_PARTIES a, HZ_MERGE_PARTY_DETAILS b
1783: where a.batch_party_id = b.batch_party_id
1784: and b.merge_from_entity_id = r.relationship_id
1785: and b.entity_name = 'HZ_PARTY_RELATIONSHIPS'
1786: and a.batch_id = l_batch_id );
1806: AND object_table_name = 'HZ_PARTIES'
1807: AND (merge_type ='S' OR actual_content_source <> 'DNB') --Bug No. 4114254
1808: AND not exists --4651128
1809: ( select 1
1810: from HZ_MERGE_PARTIES a, HZ_MERGE_PARTY_DETAILS b
1811: where a.batch_party_id = b.batch_party_id
1812: and b.merge_from_entity_id = r.relationship_id
1813: and b.entity_name = 'HZ_PARTY_RELATIONSHIPS'
1814: and a.batch_id = c_batch_id );
1829: SELECT relationship_id, start_date, nvl(end_date,to_date('12/31/4712','MM/DD/YYYY'))
1830: FROM HZ_RELATIONSHIPS r
1831: WHERE
1832: relationship_id in (select distinct b.merge_to_entity_id
1833: from HZ_MERGE_PARTIES a, HZ_MERGE_PARTY_DETAILS b
1834: where a.batch_party_id = b.batch_party_id
1835: and b.entity_name = 'HZ_PARTY_RELATIONSHIPS'
1836: and a.batch_id = c_batch_id)
1837: AND subject_id = cp_subj_id
1851: SELECT relationship_id, start_date, nvl(end_date,to_date('12/31/4712','MM/DD/YYYY'))
1852: FROM HZ_RELATIONSHIPS r
1853: WHERE
1854: relationship_id in (select distinct b.merge_to_entity_id
1855: from HZ_MERGE_PARTIES a, HZ_MERGE_PARTY_DETAILS b
1856: where a.batch_party_id = b.batch_party_id
1857: and b.entity_name = 'HZ_PARTY_RELATIONSHIPS'
1858: and a.batch_id = c_batch_id)
1859: AND object_id = cp_obj_id
1909: l_batch_party_id NUMBER;
1910: l_mandatory_merge VARCHAR2(1);
1911: l_case VARCHAR2(1);
1912: l_merge_type VARCHAR2(2000);
1913: l_party_id HZ_MERGE_PARTIES.from_party_id%TYPE;
1914: l_temp_flag varchar2(1);--bug 4867151
1915: ---Bug No.5400786
1916: l_temp VARCHAR2(1);
1917: ----Bug No. 5400786
1936:
1937: BEGIN
1938: l_temp := 'N';
1939: SELECT dset.merge_type, mpar.batch_id INTO l_merge_type, l_batch_id
1940: FROM HZ_DUP_SETS dset,HZ_MERGE_PARTIES mpar
1941: WHERE dset.dup_set_id = mpar.batch_id
1942: AND mpar.batch_party_id = p_batch_party_id;
1943:
1944: IF l_merge_type = 'SAME_PARTY_MERGE' THEN
2179: -- clean up temporary table for suggested defaults
2180: DELETE FROM HZ_MERGE_PARTYDTLS_SUGG
2181: WHERE batch_party_id in
2182: ( SELECT batch_party_id
2183: FROM HZ_MERGE_PARTIES_SUGG
2184: WHERE batch_id = p_batch_id );
2185:
2186: DELETE FROM HZ_MERGE_PARTIES_SUGG
2187: WHERE batch_id = p_batch_id;
2182: ( SELECT batch_party_id
2183: FROM HZ_MERGE_PARTIES_SUGG
2184: WHERE batch_id = p_batch_id );
2185:
2186: DELETE FROM HZ_MERGE_PARTIES_SUGG
2187: WHERE batch_id = p_batch_id;
2188:
2189: INSERT INTO HZ_MERGE_PARTIES_SUGG
2190: (
2185:
2186: DELETE FROM HZ_MERGE_PARTIES_SUGG
2187: WHERE batch_id = p_batch_id;
2188:
2189: INSERT INTO HZ_MERGE_PARTIES_SUGG
2190: (
2191: batch_party_id
2192: ,batch_id
2193: ,merge_type
2213: ,creation_date
2214: ,last_update_login
2215: ,last_update_date
2216: ,last_updated_by
2217: FROM HZ_MERGE_PARTIES
2218: WHERE batch_id = p_batch_id;
2219:
2220: INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
2221: (
2245: ,object_version_number
2246: FROM HZ_MERGE_PARTY_DETAILS
2247: WHERE batch_party_id IN
2248: ( SELECT batch_party_id
2249: FROM HZ_MERGE_PARTIES_SUGG
2250: WHERE batch_id = p_batch_id );
2251:
2252: -- get match_rule_id from profile
2253:
2315: -- merge party details table, the merge_type is 'SAME_PARTY_MERGE'
2316: cursor not_mandatory_sites_mp is
2317: select merge_from_entity_id
2318: from HZ_MERGE_PARTYDTLS_SUGG mpd
2319: , HZ_MERGE_PARTIES_SUGG mp
2320: , hz_party_sites ps
2321: where mpd.batch_party_id = mp.batch_party_id
2322: and mp.batch_id = p_batch_id
2323: and ps.party_id = mp.from_party_id
2328:
2329: -- this is for cleanse single party, find out the batch_party_id
2330: cursor get_merge_party_id is
2331: select batch_party_id, from_party_id
2332: from HZ_MERGE_PARTIES_SUGG
2333: where batch_id = p_batch_id
2334: and merge_type = 'SAME_PARTY_MERGE';
2335:
2336: -- this is for cleanse single party, get all sites which are not mandatory merge
2340: where party_id = l_master_party
2341: and status in ('A','I')
2342: and not exists
2343: ( select 1
2344: from HZ_MERGE_PARTIES_SUGG mp
2345: , HZ_MERGE_PARTYDTLS_SUGG mpd
2346: where mp.batch_id = p_batch_id
2347: and mp.batch_party_id = mpd.batch_party_id
2348: and mpd.entity_name = 'HZ_PARTY_SITES'
2352: -- for merge multiple parties
2353: -- check if the site is already mapped to some other site
2354: cursor check_mapped_sites_mp(l_from_site_id NUMBER) is
2355: select 'X'
2356: from HZ_MERGE_PARTIES_SUGG mp
2357: , HZ_MERGE_PARTYDTLS_SUGG mpd
2358: where mpd.merge_from_entity_id = l_from_site_id
2359: and mpd.merge_from_entity_id <> mpd.merge_to_entity_id
2360: and mpd.batch_party_id = mp.batch_party_id
2372: where mps.search_context_id = l_search_ctx_id
2373: and mps.party_site_id <> l_master_site
2374: and not exists
2375: ( select 1
2376: from HZ_MERGE_PARTIES_SUGG mp
2377: , HZ_MERGE_PARTYDTLS_SUGG mpd
2378: where mpd.merge_to_entity_id = mps.party_site_id
2379: and mpd.batch_party_id = mp.batch_party_id
2380: and mp.batch_id = p_batch_id
2385: -- only mapped sites appear in HZ_MERGE_PARTYDTLS_SUGG, therefore if
2386: -- merge_from_entity_id = pass in site_id, then this site is mapped
2387: cursor check_mapped_sites_sp(l_from_site_id NUMBER) is
2388: select 'X'
2389: from HZ_MERGE_PARTIES_SUGG mp
2390: , HZ_MERGE_PARTYDTLS_SUGG mpd
2391: where mpd.merge_from_entity_id = l_from_site_id
2392: and mpd.batch_party_id = mp.batch_party_id
2393: and mp.batch_id = p_batch_id
2475: ,p_rule_id => p_rule_id
2476: ,p_party_site_id => l_master_site
2477: ,p_party_id => NULL
2478: ,p_restrict_sql => ' PARTY_SITE_ID IN (SELECT /*+ SELECTIVE_PS */ MERGE_FROM_ENTITY_ID' ||
2479: ' FROM HZ_MERGE_PARTYDTLS_SUGG mpd, HZ_MERGE_PARTIES_SUGG mp' ||
2480: ' WHERE mpd.MERGE_TO_ENTITY_ID = mpd.MERGE_FROM_ENTITY_ID' ||
2481: ' AND mpd.BATCH_PARTY_ID = mp.BATCH_PARTY_ID' ||
2482: ' AND mpd.ENTITY_NAME = ''HZ_PARTY_SITES''' ||
2483: ' AND mp.BATCH_ID = '|| p_batch_id ||')'
2576: , last_updated_by = hz_utility_v2pub.last_updated_by
2577: , last_update_login = hz_utility_v2pub.last_update_login
2578: WHERE batch_party_id IN
2579: ( SELECT batch_party_id
2580: FROM HZ_MERGE_PARTIES_SUGG
2581: WHERE batch_id = p_batch_id )
2582: AND merge_from_entity_id IN
2583: ( SELECT party_site_id
2584: FROM HZ_MATCHED_PARTY_SITES_GT matchps
2582: AND merge_from_entity_id IN
2583: ( SELECT party_site_id
2584: FROM HZ_MATCHED_PARTY_SITES_GT matchps
2585: , HZ_MERGE_PARTYDTLS_SUGG mpd
2586: , HZ_MERGE_PARTIES_SUGG mps
2587: WHERE matchps.search_context_id = l_search_ctx_id
2588: AND matchps.party_site_id = mpd.merge_from_entity_id
2589: AND mpd.entity_name = 'HZ_PARTY_SITES'
2590: AND mpd.merge_to_entity_id = mpd.merge_from_entity_id
2755: -- Oracle Corp and Oracle Inc
2756: cursor not_mandatory_reln_mp is
2757: select rel.relationship_id, rel.relationship_type, rel.relationship_code, rel.object_id, rel.subject_id, rel.subject_type, rel.object_type
2758: from HZ_MERGE_PARTYDTLS_SUGG mpd
2759: , HZ_MERGE_PARTIES_SUGG mp
2760: , HZ_RELATIONSHIPS rel
2761: where mpd.batch_party_id = mp.batch_party_id
2762: and mp.batch_id = p_batch_id
2763: and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS'
2776: from HZ_RELATIONSHIPS rel
2777: where subject_id = l_master_party_id
2778: and not exists
2779: ( select 1
2780: from HZ_MERGE_PARTIES_SUGG mp
2781: , HZ_MERGE_PARTYDTLS_SUGG mpd
2782: , HZ_RELATIONSHIPS rel2
2783: where mp.batch_id = p_batch_id
2784: and mp.batch_party_id = mpd.batch_party_id
2795: -- corresponding party sites to merge. Those batch_party_id will be different. Therefore, we need to filter
2796: -- by merge_type = 'SAME_PARTY_MERGE'
2797: cursor get_merge_party_id is
2798: select batch_party_id, from_party_id
2799: from HZ_MERGE_PARTIES_SUGG
2800: where batch_id = p_batch_id
2801: and merge_type = 'SAME_PARTY_MERGE';
2802:
2803: -- this is for cleanse single party, check if the matched parties is already mapped
2813: and rel.relationship_code = l_reln_code
2814: and rel.relationship_type = l_reln_type
2815: and not exists
2816: ( select 1
2817: from HZ_MERGE_PARTIES_SUGG mp
2818: , HZ_MERGE_PARTYDTLS_SUGG mpd
2819: where mpd.merge_to_entity_id = rel.relationship_id
2820: and mpd.batch_party_id = mp.batch_party_id
2821: and mp.batch_id = p_batch_id
2823:
2824: -- need to check if relationship is already mapped as merge_from_entity_id
2825: cursor check_mapped_reln(l_from_rel_id NUMBER) is
2826: select 'X'
2827: from HZ_MERGE_PARTIES_SUGG mp
2828: , HZ_MERGE_PARTYDTLS_SUGG mpd
2829: where mpd.merge_from_entity_id = l_from_rel_id
2830: and mpd.batch_party_id = mp.batch_party_id
2831: and mp.batch_id = p_batch_id
2954: , last_updated_by = hz_utility_v2pub.last_updated_by
2955: , last_update_login = hz_utility_v2pub.last_update_login
2956: WHERE batch_party_id in
2957: ( SELECT batch_party_id
2958: FROM HZ_MERGE_PARTIES_SUGG
2959: WHERE batch_id = p_batch_id )
2960: AND merge_from_entity_id IN
2961: ( SELECT rel.relationship_id
2962: FROM HZ_MATCHED_PARTIES_GT matchpty
2960: AND merge_from_entity_id IN
2961: ( SELECT rel.relationship_id
2962: FROM HZ_MATCHED_PARTIES_GT matchpty
2963: , HZ_MERGE_PARTYDTLS_SUGG mpd
2964: , HZ_MERGE_PARTIES_SUGG mps
2965: , HZ_RELATIONSHIPS rel
2966: , HZ_ORIG_SYSTEMS_B O
2967: WHERE matchpty.search_context_id = l_search_ctx_id
2968: AND matchpty.party_id = rel.object_id
2979: AND decode(o.orig_system_type,'PURCHASED',(decode(l_to_orig_system_type,'PURCHASED',(decode(o.orig_system,l_to_orig_system,1,0)),0)),1)= 1
2980: AND NOT EXISTS
2981: ( SELECT 1
2982: FROM HZ_MERGE_PARTYDTLS_SUGG mpdi,
2983: HZ_MERGE_PARTIES_SUGG mpsi
2984: WHERE mpdi.batch_party_id = mpsi.batch_party_id
2985: AND mpsi.batch_id = mps.batch_id
2986: AND mpdi.merge_to_entity_id = rel.relationship_id
2987: AND mpdi.merge_to_entity_id <> mpdi.merge_from_entity_id
2996: l_from_rel_party_id := hz_merge_util.get_reln_party_id(l_merge_from_reln_tbl(i));
2997: l_to_rel_party_id := hz_merge_util.get_reln_party_id(l_reln_id);
2998:
2999: select count(1) into l_rel_party_count
3000: from HZ_MERGE_PARTIES
3001: where batch_id = p_batch_id
3002: and merge_type = 'PARTY_MERGE'
3003: and merge_reason_code = 'DUPLICATE_RELN_PARTY'
3004: and from_party_id = l_from_rel_party_id;
3122: l_from_rel_party_id := hz_merge_util.get_reln_party_id(l_merge_from_reln);
3123: l_to_rel_party_id := hz_merge_util.get_reln_party_id(l_reln_id);
3124:
3125: select count(1) into l_rel_party_count
3126: from HZ_MERGE_PARTIES
3127: where batch_id = p_batch_id
3128: and merge_type = 'PARTY_MERGE'
3129: and merge_reason_code = 'DUPLICATE_RELN_PARTY'
3130: and from_party_id = l_from_rel_party_id;
3142: -- insert relationship party's sites record
3143: insert_sugg_reln_ps_details(l_from_rel_party_id
3144: ,l_to_rel_party_id
3145: ,l_reln_bpty_id, 'Y');
3146: end if; -- check if the relationship party record has been added to HZ_MERGE_PARTIES
3147: end if; -- check if there exist relationship party
3148:
3149: END IF;--l_merge_flag
3150: END IF; -- check_mapped_reln
3316: ) IS
3317:
3318: BEGIN
3319:
3320: select HZ_MERGE_PARTIES_S.nextval into x_batch_party_id
3321: from dual;
3322:
3323: INSERT INTO HZ_MERGE_PARTIES_SUGG
3324: (
3319:
3320: select HZ_MERGE_PARTIES_S.nextval into x_batch_party_id
3321: from dual;
3322:
3323: INSERT INTO HZ_MERGE_PARTIES_SUGG
3324: (
3325: BATCH_PARTY_ID
3326: ,BATCH_ID
3327: ,MERGE_TYPE
3403:
3404: DELETE FROM HZ_MERGE_PARTY_DETAILS
3405: WHERE BATCH_PARTY_ID IN
3406: ( SELECT BATCH_PARTY_ID
3407: FROM HZ_MERGE_PARTIES
3408: WHERE BATCH_ID = p_batch_id )
3409: AND ENTITY_NAME = p_entity_name;
3410:
3411: INSERT INTO HZ_MERGE_PARTY_DETAILS
3437: FROM HZ_MERGE_PARTYDTLS_SUGG
3438: WHERE entity_name = p_entity_name
3439: AND batch_party_id IN
3440: ( SELECT batch_party_id
3441: FROM HZ_MERGE_PARTIES_SUGG
3442: WHERE batch_id = p_batch_id );
3443:
3444: -- if entity is HZ_RELATIONSHIPS, insert DUPLICATE_RELN_PARTY record to
3445: -- HZ_MERGE_PARTIES table as well
3441: FROM HZ_MERGE_PARTIES_SUGG
3442: WHERE batch_id = p_batch_id );
3443:
3444: -- if entity is HZ_RELATIONSHIPS, insert DUPLICATE_RELN_PARTY record to
3445: -- HZ_MERGE_PARTIES table as well
3446:
3447: IF(p_entity_name = 'HZ_PARTY_RELATIONSHIPS') THEN
3448:
3449: DELETE FROM HZ_MERGE_PARTIES
3445: -- HZ_MERGE_PARTIES table as well
3446:
3447: IF(p_entity_name = 'HZ_PARTY_RELATIONSHIPS') THEN
3448:
3449: DELETE FROM HZ_MERGE_PARTIES
3450: WHERE batch_id = p_batch_id
3451: AND merge_reason_code = 'DUPLICATE_RELN_PARTY';
3452:
3453: INSERT INTO HZ_MERGE_PARTIES
3449: DELETE FROM HZ_MERGE_PARTIES
3450: WHERE batch_id = p_batch_id
3451: AND merge_reason_code = 'DUPLICATE_RELN_PARTY';
3452:
3453: INSERT INTO HZ_MERGE_PARTIES
3454: (
3455: batch_party_id
3456: ,batch_id
3457: ,merge_type
3477: ,creation_date
3478: ,last_update_login
3479: ,last_updated_by
3480: ,last_update_date
3481: FROM HZ_MERGE_PARTIES_SUGG mp
3482: WHERE mp.batch_id = p_batch_id
3483: AND mp.merge_reason_code = 'DUPLICATE_RELN_PARTY';
3484:
3485: END IF;
3557: CURSOR find_mand_reln IS
3558: SELECT HZ_MERGE_UTIL.get_reln_party_id(a.merge_from_entity_id)
3559: , HZ_MERGE_UTIL.get_reln_party_id(a.merge_from_entity_id)
3560: FROM HZ_MERGE_PARTYDTLS_SUGG a
3561: , HZ_MERGE_PARTIES_SUGG b
3562: WHERE b.batch_id = p_batch_id
3563: AND a.entity_name = 'HZ_PARTY_RELATIONSHIPS'
3564: AND a.batch_party_id = b.batch_party_id
3565: AND a.mandatory_merge = 'Y';
3598: mpd.object_version_number = nvl(mpd.object_version_number,1)+1
3599: WHERE entity_name = p_entity_name
3600: AND batch_party_id IN
3601: ( SELECT batch_party_id
3602: FROM HZ_MERGE_PARTIES_SUGG
3603: WHERE batch_id = p_batch_id );
3604:
3605:
3606:
3610: WHERE mandatory_merge <> 'Y'
3611: AND entity_name = p_entity_name
3612: AND batch_party_id IN
3613: ( SELECT batch_party_id
3614: FROM HZ_MERGE_PARTIES
3615: WHERE batch_id = p_batch_id );
3616:
3617:
3618: ELSIF(p_merge_type = 'SAME_PARTY_MERGE') THEN
3621: DELETE HZ_MERGE_PARTY_DETAILS
3622: WHERE entity_name = p_entity_name
3623: AND batch_party_id IN
3624: ( SELECT batch_party_id
3625: FROM HZ_MERGE_PARTIES
3626: WHERE batch_id = p_batch_id );
3627:
3628: -- only insert those mandatory merge mapping
3629: INSERT INTO HZ_MERGE_PARTY_DETAILS
3656: WHERE mandatory_merge = 'Y'
3657: AND entity_name = p_entity_name
3658: AND batch_party_id IN
3659: ( SELECT batch_party_id
3660: FROM HZ_MERGE_PARTIES_SUGG
3661: WHERE batch_id = p_batch_id );
3662:
3663: END IF;
3664:
3663: END IF;
3664:
3665: IF(p_entity_name = 'HZ_PARTY_RELATIONSHIPS') THEN
3666:
3667: -- remove all DUPLICATE_RELN_PARTY record in HZ_MERGE_PARTIES first
3668: -- then recreate them by finding out all relationship mandatory
3669: -- merge at HZ_MERGE_PARTY_DETAILS
3670:
3671:
3668: -- then recreate them by finding out all relationship mandatory
3669: -- merge at HZ_MERGE_PARTY_DETAILS
3670:
3671:
3672: DELETE FROM HZ_MERGE_PARTIES mp
3673: WHERE mp.batch_id = p_batch_id
3674: AND mp.merge_reason_code = 'DUPLICATE_RELN_PARTY';
3675:
3676:
3773: ) IS
3774:
3775: CURSOR get_reln_party_sp IS
3776: select to_rel.subject_id, count(1)
3777: from HZ_MERGE_PARTY_DETAILS mpd, HZ_MERGE_PARTIES mp
3778: , HZ_RELATIONSHIPS from_rel, HZ_RELATIONSHIPS to_rel
3779: , HZ_PARTIES from_pty, HZ_PARTIES to_pty
3780: where mpd.batch_party_id = mp.batch_party_id
3781: and mp.batch_id = p_batch_id
3793: group by to_rel.subject_id;
3794:
3795: CURSOR get_reln_party_mp IS
3796: select to_rel.subject_id, count(1)
3797: from HZ_MERGE_PARTY_DETAILS mpd, HZ_MERGE_PARTIES mp
3798: , HZ_RELATIONSHIPS from_rel, HZ_RELATIONSHIPS to_rel
3799: , HZ_PARTIES from_pty, HZ_PARTIES to_pty
3800: where mpd.batch_party_id = mp.batch_party_id
3801: and mpd.merge_from_entity_id <> mpd.merge_to_entity_id
3818: group by to_rel.subject_id;
3819:
3820: CURSOR get_reln_from_party_sp(l_to_party_id NUMBER) IS
3821: select from_rel.subject_id
3822: from HZ_MERGE_PARTY_DETAILS mpd, HZ_MERGE_PARTIES mp
3823: , HZ_RELATIONSHIPS from_rel, HZ_RELATIONSHIPS to_rel
3824: , HZ_PARTIES from_pty, HZ_PARTIES to_pty
3825: where mpd.batch_party_id = mp.batch_party_id
3826: and mp.batch_id = p_batch_id
3838: and to_rel.subject_id = to_pty.party_id;
3839:
3840: CURSOR get_reln_from_party_mp(l_to_party_id NUMBER) IS
3841: select from_rel.subject_id
3842: from HZ_MERGE_PARTY_DETAILS mpd, HZ_MERGE_PARTIES mp
3843: , HZ_RELATIONSHIPS from_rel, HZ_RELATIONSHIPS to_rel
3844: , HZ_PARTIES from_pty, HZ_PARTIES to_pty
3845: where mpd.batch_party_id = mp.batch_party_id
3846: and mpd.merge_from_entity_id <> mpd.merge_to_entity_id
4283: l_obj_ver_number NUMBER;
4284:
4285: CURSOR c_children(cp_merge_batch_id NUMBER, cp_entity VARCHAR2, cp_entity_id NUMBER) IS
4286: SELECT mpd.merge_from_entity_id, mpd.batch_party_id, mpd.object_version_number
4287: from hz_merge_parties mp, hz_merge_party_details mpd
4288: WHERE mp.batch_id=cp_merge_batch_id
4289: AND mpd.entity_name = p_entity
4290: AND mp.batch_party_id = mpd.batch_party_id
4291: AND mpd.merge_from_entity_id <> cp_entity_id
4400: function get_merge_batch_data_source(p_merge_batch_id in number) return varchar2 is
4401:
4402: cursor get_form_merge_batch_csr is
4403: /* select 'x'
4404: from hz_merge_parties mp
4405: where mp.batch_id = p_merge_batch_id
4406: and not exists ( select 'x'
4407: from hz_dup_set_parties dsp
4408: where dsp.dup_set_id = mp.batch_id
4451: and ds.dup_set_id = p_dup_set_id;
4452:
4453: cursor dset_overlap_req_party_csr is
4454: select distinct mp.batch_id,dsp.dup_party_id,ds.object_version_number
4455: from hz_merge_batch mb, hz_merge_parties mp,
4456: hz_dup_sets ds, hz_dup_set_parties dsp, hz_dup_batch db
4457: where mp.batch_id <> ds.dup_set_id
4458: and mb.batch_id = mp.batch_id
4459: and db.dup_batch_id = ds.dup_batch_id
4464: and ds.dup_set_id = p_dup_set_id;
4465:
4466: cursor batch_overlap_merged_party_csr is
4467: select distinct party.party_number
4468: from hz_parties party, hz_merge_parties mp, hz_merge_batch mb
4469: where (party.party_id = mp.from_party_id or party.party_id = mp.to_party_id)
4470: and party.status = 'M'
4471: and mp.batch_id = p_merge_batch_id
4472: and mb.batch_id = mp.batch_id
4479: where dup_set_id = p_merge_batch_id;
4480:
4481: cursor get_merged_rel_party_csr is
4482: select mp2.batch_party_id
4483: from hz_parties p1, hz_merge_parties mp2
4484: where p1.party_id = mp2.from_party_id
4485: and p1.status = 'M'
4486: and mp2.merge_reason_code = 'DUPLICATE_RELN_PARTY'
4487: and mp2.batch_id = p_merge_batch_id;
4585: exit when get_merged_rel_party_csr%NOTFOUND;
4586:
4587: if l_batch_party_id is not null
4588: then
4589: delete from hz_merge_parties where batch_party_id = l_batch_party_id;
4590: delete from hz_merge_party_details where batch_party_id = l_batch_party_id;
4591:
4592: end if;
4593: end loop;
4637: and ps.party_site_id = p_to_site_id;
4638:
4639: cursor get_from_addr_csr is
4640: select hz_format_pub.format_address(l.location_id,null,null,', ')
4641: from hz_merge_parties p,
4642: hz_merge_party_details pd,
4643: hz_party_sites s,
4644: hz_locations l,
4645: hz_parties hp
4684: SELECT 'Y'
4685: FROM hz_party_sites ps1,
4686: hz_cust_acct_sites_all as1,
4687: hz_cust_accounts ca1,
4688: hz_merge_parties p1,
4689: hz_merge_party_details pd1
4690: WHERE p1.batch_id = p_merge_batch_id
4691: AND ps1.party_site_id = as1.party_site_id
4692: and ca1.cust_account_id = as1.cust_account_id
4696: and pd1.merge_from_entity_id <> pd1.merge_to_entity_id
4697: AND exists
4698: ( select 1 from hz_party_sites ps2,
4699: hz_cust_acct_sites_all as2,
4700: hz_merge_parties p2,
4701: hz_merge_party_details pd2
4702: where p2.batch_id = p_merge_batch_id
4703: and ps2.party_site_id = as2.party_site_id
4704: and as2.cust_account_id = as1.cust_account_id
4732: SELECT distinct ca1.account_number, pd1.merge_to_entity_id
4733: FROM hz_party_sites ps1,
4734: hz_cust_acct_sites_all as1,
4735: hz_cust_accounts ca1,
4736: hz_merge_parties p1,
4737: hz_merge_party_details pd1
4738: WHERE p1.batch_id = p_merge_batch_id
4739: AND ps1.party_site_id = as1.party_site_id
4740: and ca1.cust_account_id = as1.cust_account_id
4744: and pd1.merge_from_entity_id <> pd1.merge_to_entity_id
4745: AND exists
4746: ( select 1 from hz_party_sites ps2,
4747: hz_cust_acct_sites_all as2,
4748: hz_merge_parties p2,
4749: hz_merge_party_details pd2
4750: where p2.batch_id = p_merge_batch_id
4751: and ps2.party_site_id = as2.party_site_id
4752: and as2.cust_account_id = as1.cust_account_id