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