[Home] [Help]
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;
2177: fetch get_dup_sets_info into l_merge_type;
2178: close get_dup_sets_info;
2179:
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 );
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: (
2223: batch_party_id
2224: ,entity_name
2225: ,merge_from_entity_id
2315: -- merge_type may be 'SAME_PARTY_MERGE'. It is because for master party sites in
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
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'
2350: and mpd.merge_from_entity_id = ps.party_site_id)
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
2362: and mp.batch_id = p_batch_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
2382: and mpd.entity_name = 'HZ_PARTY_SITES');
2382: and mpd.entity_name = 'HZ_PARTY_SITES');
2383:
2384: -- for cleanse single party
2385: -- need to check if sites is already mapped to other site
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
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
2395: and mpd.entity_name = 'HZ_PARTY_SITES';
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 ||')'
2569:
2570: -- if found any match, update the merge party details temp table
2571: IF (l_num_matches > 0) THEN
2572:
2573: UPDATE HZ_MERGE_PARTYDTLS_SUGG
2574: SET merge_to_entity_id = l_master_site
2575: , mandatory_merge = 'N'
2576: , last_update_date = hz_utility_v2pub.last_update_date
2577: , last_updated_by = hz_utility_v2pub.last_updated_by
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
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'
2592: AND mpd.batch_party_id = mps.batch_party_id
2593: AND mps.batch_id = p_batch_id
2594: AND NOT EXISTS
2595: ( SELECT 1
2596: FROM HZ_MERGE_PARTYDTLS_SUGG mpdi
2597: WHERE mpdi.batch_party_id = mpd.batch_party_id
2598: AND mpdi.merge_to_entity_id = matchps.party_site_id
2599: AND mpdi.merge_to_entity_id <> mpdi.merge_from_entity_id
2600: )
2689: CLOSE c_get_orig_system;
2690: END IF; /* c_get_orig_system%FOUND */
2691:
2692: IF l_flag_merge = 'Y' THEN
2693: INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
2694: (
2695: batch_party_id
2696: ,entity_name
2697: ,merge_from_entity_id
2755: -- Then all relationships retrieved will be based on subject_id = Oracle,
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
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
2787: and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS'
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
2824: and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS');
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
2834: and mpd.entity_name = 'HZ_PARTY_RELATIONSHIPS';
2948: if not(l_num_matches > 1 and l_rel_status = 'I')
2949: then
2950: -- end bug 5194384
2951:
2952: UPDATE HZ_MERGE_PARTYDTLS_SUGG
2953: SET merge_to_entity_id = l_reln_id
2954: , mandatory_merge = 'N'
2955: , last_update_date = hz_utility_v2pub.last_update_date
2956: , last_updated_by = hz_utility_v2pub.last_updated_by
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
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
2980: AND o.orig_system = rel.actual_content_source
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
3090: CLOSE c_get_orig_system_r;
3091: END IF; /* c_get_orig_system%FOUND */
3092:
3093: IF l_flag_merge = 'Y' THEN
3094: INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
3095: (
3096: batch_party_id
3097: ,entity_name
3098: ,merge_from_entity_id
3210:
3211: OPEN c_dup_to_ps(l_loc_id);
3212: FETCH c_dup_to_ps INTO l_dup_ps_id;
3213: IF c_dup_to_ps%FOUND THEN
3214: INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
3215: (
3216: batch_party_id
3217: ,entity_name
3218: ,merge_from_entity_id
3240: ,1
3241: );
3242: ELSE
3243: IF p_reln_parties = 'N' THEN
3244: INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
3245: (
3246: batch_party_id
3247: ,entity_name
3248: ,merge_from_entity_id
3269: ,hz_utility_v2pub.last_updated_by
3270: ,1
3271: );
3272: ELSE
3273: INSERT INTO HZ_MERGE_PARTYDTLS_SUGG
3274: (
3275: batch_party_id
3276: ,entity_name
3277: ,merge_from_entity_id
3435: ,hz_utility_v2pub.last_update_login
3436: ,hz_utility_v2pub.last_updated_by
3437: ,hz_utility_v2pub.last_update_date
3438: ,1
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
3558:
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
3583: -- copy mapping from suggested defaults table
3584: UPDATE HZ_MERGE_PARTY_DETAILS mpd
3585: SET mpd.merge_from_entity_id =
3586: ( SELECT merge_from_entity_id
3587: FROM HZ_MERGE_PARTYDTLS_SUGG mps
3588: WHERE mpd.batch_party_id = mps.batch_party_id
3589: AND mpd.merge_from_entity_id = mps.merge_from_entity_id
3590: AND mpd.entity_name = mps.entity_name ),
3591: mpd.mandatory_merge =
3589: AND mpd.merge_from_entity_id = mps.merge_from_entity_id
3590: AND mpd.entity_name = mps.entity_name ),
3591: mpd.mandatory_merge =
3592: ( SELECT mandatory_merge
3593: FROM HZ_MERGE_PARTYDTLS_SUGG mps
3594: WHERE mpd.batch_party_id = mps.batch_party_id
3595: AND mpd.merge_from_entity_id = mps.merge_from_entity_id
3596: AND mpd.entity_name = mps.entity_name ),
3597: mpd.last_update_login = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN ,
3653: ,hz_utility_v2pub.last_update_login
3654: ,hz_utility_v2pub.last_updated_by
3655: ,hz_utility_v2pub.last_update_date
3656: ,1
3657: FROM HZ_MERGE_PARTYDTLS_SUGG
3658: WHERE mandatory_merge = 'Y'
3659: AND entity_name = p_entity_name
3660: AND batch_party_id IN
3661: ( SELECT batch_party_id