[Home] [Help]
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;
2176: fetch get_dup_sets_info into l_merge_type;
2177: close get_dup_sets_info;
2178:
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 );
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: (
2222: batch_party_id
2223: ,entity_name
2224: ,merge_from_entity_id
2314: -- merge_type may be 'SAME_PARTY_MERGE'. It is because for master party sites in
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
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'
2349: and mpd.merge_from_entity_id = ps.party_site_id)
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
2361: and mp.batch_id = p_batch_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
2381: and mpd.entity_name = 'HZ_PARTY_SITES');
2381: and mpd.entity_name = 'HZ_PARTY_SITES');
2382:
2383: -- for cleanse single party
2384: -- need to check if sites is already mapped to other site
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
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
2394: and mpd.entity_name = 'HZ_PARTY_SITES';
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 ||')'
2568:
2569: -- if found any match, update the merge party details temp table
2570: IF (l_num_matches > 0) THEN
2571:
2572: UPDATE HZ_MERGE_PARTYDTLS_SUGG
2573: SET merge_to_entity_id = l_master_site
2574: , mandatory_merge = 'N'
2575: , last_update_date = hz_utility_v2pub.last_update_date
2576: , last_updated_by = hz_utility_v2pub.last_updated_by
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
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'
2591: AND mpd.batch_party_id = mps.batch_party_id
2592: AND mps.batch_id = p_batch_id
2593: AND NOT EXISTS
2594: ( SELECT 1
2595: FROM HZ_MERGE_PARTYDTLS_SUGG mpdi
2596: WHERE mpdi.batch_party_id = mpd.batch_party_id
2597: AND mpdi.merge_to_entity_id = matchps.party_site_id
2598: AND mpdi.merge_to_entity_id <> mpdi.merge_from_entity_id
2599: )
2688: CLOSE c_get_orig_system;
2689: END IF; /* c_get_orig_system%FOUND */
2690:
2691: IF l_flag_merge = 'Y' THEN
2692: INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
2693: (
2694: batch_party_id
2695: ,entity_name
2696: ,merge_from_entity_id
2754: -- Then all relationships retrieved will be based on subject_id = Oracle,
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
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
2785: and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS'
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
2822: and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS');
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
2832: and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS';
2946: if not(l_num_matches > 1 and l_rel_status = 'I')
2947: then
2948: -- end bug 5194384
2949:
2950: UPDATE HZ_MERGE_PARTYDTLS_SUGG
2951: SET merge_to_entity_id = l_reln_id
2952: , mandatory_merge = 'N'
2953: , last_update_date = hz_utility_v2pub.last_update_date
2954: , last_updated_by = hz_utility_v2pub.last_updated_by
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
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
2978: AND o.orig_system = rel.actual_content_source
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
3088: CLOSE c_get_orig_system_r;
3089: END IF; /* c_get_orig_system%FOUND */
3090:
3091: IF l_flag_merge = 'Y' THEN
3092: INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
3093: (
3094: batch_party_id
3095: ,entity_name
3096: ,merge_from_entity_id
3208:
3209: OPEN c_dup_to_ps(l_loc_id);
3210: FETCH c_dup_to_ps INTO l_dup_ps_id;
3211: IF c_dup_to_ps%FOUND THEN
3212: INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
3213: (
3214: batch_party_id
3215: ,entity_name
3216: ,merge_from_entity_id
3238: ,1
3239: );
3240: ELSE
3241: IF p_reln_parties = 'N' THEN
3242: INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
3243: (
3244: batch_party_id
3245: ,entity_name
3246: ,merge_from_entity_id
3267: ,hz_utility_v2pub.last_updated_by
3268: ,1
3269: );
3270: ELSE
3271: INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
3272: (
3273: batch_party_id
3274: ,entity_name
3275: ,merge_from_entity_id
3433: ,hz_utility_v2pub.last_update_login
3434: ,hz_utility_v2pub.last_updated_by
3435: ,hz_utility_v2pub.last_update_date
3436: ,1
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
3556:
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
3581: -- copy mapping from suggested defaults table
3582: UPDATE HZ_MERGE_PARTY_DETAILS mpd
3583: SET mpd.merge_from_entity_id =
3584: ( SELECT merge_from_entity_id
3585: FROM HZ_MERGE_PARTYDTLS_SUGG mps
3586: WHERE mpd.batch_party_id = mps.batch_party_id
3587: AND mpd.merge_from_entity_id = mps.merge_from_entity_id
3588: AND mpd.entity_name = mps.entity_name ),
3589: mpd.mandatory_merge =
3587: AND mpd.merge_from_entity_id = mps.merge_from_entity_id
3588: AND mpd.entity_name = mps.entity_name ),
3589: mpd.mandatory_merge =
3590: ( SELECT mandatory_merge
3591: FROM HZ_MERGE_PARTYDTLS_SUGG mps
3592: WHERE mpd.batch_party_id = mps.batch_party_id
3593: AND mpd.merge_from_entity_id = mps.merge_from_entity_id
3594: AND mpd.entity_name = mps.entity_name ),
3595: mpd.last_update_login = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN ,
3651: ,hz_utility_v2pub.last_update_login
3652: ,hz_utility_v2pub.last_updated_by
3653: ,hz_utility_v2pub.last_update_date
3654: ,1
3655: FROM HZ_MERGE_PARTYDTLS_SUGG
3656: WHERE mandatory_merge = 'Y'
3657: AND entity_name = p_entity_name
3658: AND batch_party_id IN
3659: ( SELECT batch_party_id