[Home] [Help]
303:
304: -- Address
305: IF(fnd_profile.value('HZ_SHOW_SUGG_ADDR') = 'Y'
306: or (l_automerge_flag = 'Y' and l_addr_match_rule <>0 and l_addr_match_rule is not null )) THEN
307: apply_suggested_default(l_batch_id, 'HZ_PARTY_SITES', x_return_status, x_msg_count, x_msg_data);
308: END IF;
309: -- Relationship
310: IF(fnd_profile.value('HZ_SHOW_SUGG_RELN') = 'Y'
311: or (l_automerge_flag = 'Y' and l_reln_match_rule <>0 and l_reln_match_rule is not null)) THEN
451: --
452: -- ARGUMENTS
453: -- IN:
454: -- p_batch_party_id Batch_Party_id from the merge tables
455: -- p_entity Name of the entity HZ_PARTY_SITES,
456: -- HZ_PARTY_RELATIONSHIPS etc.
457: -- p_from_entity_id ID of the from record
458: -- p_to_entity_id ID of the to record
459:
518: l.postal_code,
519: ps.status,
520: ps.location_id --bug 4569674
521: FROM hz_locations l
522: , hz_party_sites ps
523: WHERE ps.party_site_id = cp_ps_id
524: AND ps.location_id = l.location_id;
525:
526: CURSOR c_cust_site_check IS
629: RAISE FND_API.G_EXC_ERROR;
630: END;
631:
632: ---Validation for locations/Party sites
633: IF p_entity = 'HZ_PARTY_SITES' THEN
634: IF p_to_entity_id IS NOT NULL THEN
635: OPEN c_cust_site_check;
636: Fetch c_cust_site_check into l_cust_sites;
637: IF c_cust_site_check %NOTFOUND THEN
887: --
888: -- ARGUMENTS
889: -- IN:
890: -- p_batch_party_id Batch_Party_id from the merge tables
891: -- p_entity Name of the entity HZ_PARTY_SITES,
892: -- HZ_RELATIONSHIPS etc.
893: -- p_from_entity_id ID of the from record
894: -- p_to_entity_id ID of the to record
895:
942:
943: ----------------3738622-------------------------------------
944: /*CURSOR c_loc_assignments(cp_from_ps_id NUMBER, cp_to_ps_id NUMBER) IS
945: SELECT la.loc_id, la.org_id
946: FROM HZ_LOC_ASSIGNMENTS la, HZ_PARTY_SITES ps
947: WHERE ps.party_site_id = cp_from_ps_id
948: AND la.location_id = ps.location_id
949: MINUS
950: SELECT la.loc_id, la.org_id
947: WHERE ps.party_site_id = cp_from_ps_id
948: AND la.location_id = ps.location_id
949: MINUS
950: SELECT la.loc_id, la.org_id
951: FROM HZ_LOC_ASSIGNMENTS la, HZ_PARTY_SITES ps
952: WHERE ps.party_site_id = cp_to_ps_id
953: AND la.location_id = ps.location_id;
954: */
955:
962: l.postal_code,
963: ps.status,
964: ps.location_id --bug 4569674
965: FROM hz_locations l
966: , hz_party_sites ps
967: WHERE ps.party_site_id = cp_ps_id
968: AND ps.location_id = l.location_id;
969:
970: CURSOR c_cust_site_check IS
1057:
1058:
1059:
1060: ---Validation for locations/Party sites
1061: IF p_entity = 'HZ_PARTY_SITES' THEN
1062:
1063: IF p_to_entity_id IS NOT NULL THEN
1064: -----------Bug No: 3738622---------------------------------
1065: /*OPEN c_loc_assignments(p_from_entity_id,p_to_entity_id);
1121: --check if the party sites are for the same party
1122: select ps1.party_id from_site_party_id ,
1123: ps2.party_id to_site_party_id
1124: into l_from_site_party_id, l_to_site_party_id
1125: from hz_party_sites ps1 , hz_party_sites ps2
1126: where ps1.party_site_id = p_from_entity_id
1127: and ps2.party_site_id = p_to_entity_id;
1128:
1129: --Both from and to party_site_id's should point to same party_id
1138: END IF;
1139: CLOSE c_cust_site_check;
1140: HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
1141: p_batch_party_id,
1142: 'HZ_PARTY_SITES',
1143: p_from_entity_id,
1144: p_to_entity_id,
1145: 'N',
1146: hz_utility_v2pub.created_by,
1151:
1152: ELSE
1153: IF l_map_dtl_count >0 THEN
1154: HZ_MERGE_PARTY_DETAILS_PKG.delete_row(
1155: p_batch_party_id, 'HZ_PARTY_SITES', p_from_entity_id);
1156: END IF;
1157: END IF;
1158:
1159: ELSIF p_entity = 'HZ_PARTY_RELATIONSHIPS' THEN
1662:
1663: --Cursor for inserting Party sites that are non-DNB
1664: CURSOR c_from_ps_loc(merge_type VARCHAR2) IS
1665: SELECT party_site_id, ps.location_id
1666: FROM HZ_PARTY_SITES ps
1667: WHERE ps.party_id = p_from_party_id
1668: AND (merge_type = 'S' OR ps.actual_content_source <>'DNB')--Bug No.4114254
1669: AND nvl(status, 'A') in ('A','I');
1670:
1672:
1673:
1674: CURSOR c_dup_to_ps(cp_loc_id NUMBER,merge_type VARCHAR2) IS
1675: SELECT party_site_id
1676: FROM HZ_PARTY_SITES ps
1677: WHERE ps.party_id = p_to_party_id
1678: AND ps.location_id = cp_loc_id
1679: AND (merge_type = 'S' OR ps.actual_content_source <>'DNB')--Bug No. 4114254
1680: AND nvl(status, 'A') in ('A','I');
1724: END IF;
1725:
1726: HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
1727: p_batch_party_id,
1728: 'HZ_PARTY_SITES',
1729: l_ps_id,
1730: l_to_entity_id,
1731: l_mandatory_merge,
1732: hz_utility_v2pub.created_by,
1738: l_to_entity_id := l_ps_id;
1739:
1740: HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
1741: p_batch_party_id,
1742: 'HZ_PARTY_SITES',
1743: l_ps_id,
1744: l_to_entity_id,
1745: 'N',
1746: hz_utility_v2pub.created_by,
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
2325: and ps.party_site_id = mpd.merge_from_entity_id
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
2325: and ps.party_site_id = mpd.merge_from_entity_id
2326: and mpd.entity_name = 'HZ_PARTY_SITES'
2327: and mpd.merge_to_entity_id = mpd.merge_from_entity_id
2328: order by ps.status,mp.merge_type desc; -- make sure to process active sites first;
2329:
2330: -- this is for cleanse single party, find out the batch_party_id
2336:
2337: -- this is for cleanse single party, get all sites which are not mandatory merge
2338: cursor not_mandatory_sites_sp(l_master_party NUMBER) is
2339: select party_site_id
2340: from HZ_PARTY_SITES ps
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'
2350: and mpd.merge_from_entity_id = ps.party_site_id)
2351: order by ps.status,decode(ps.actual_content_source,'DNB',1,2); -- make sure to process active sites first;
2352:
2353: -- for merge multiple parties
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
2363: and mpd.entity_name = 'HZ_PARTY_SITES';
2364:
2365: -- this is for cleanse single party
2366: -- since l_master_party is passed for the DQM search, we can assume that all matched party sites
2367: -- will be from the same l_master_party
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');
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
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';
2396:
2397: CURSOR c_get_matched_ps(l_search_context_id NUMBER) IS
2398: SELECT party_site_id
2399: FROM HZ_MATCHED_PARTY_SITES_GT
2404:
2405: --4114254
2406: CURSOR c_get_orig_system(p_party_site_id NUMBER) IS
2407: select o.orig_system
2408: from hz_party_sites ps, hz_orig_systems_b o
2409: where ps.party_site_id = p_party_site_id
2410: and o.orig_system = ps.actual_content_source
2411: and o.orig_system_type = 'PURCHASED';
2412:
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 ||')'
2485: ,p_match_type => 'OR'
2486: ,x_search_ctx_id => l_search_ctx_id
2487: ,x_num_matches => l_num_matches
2505: null;
2506: ELSE
2507: --4569674
2508: SELECT location_id into l_from_location_id
2509: FROM hz_party_sites
2510: WHERE party_site_id = l_temp_from_site;
2511:
2512: SELECT location_id into l_to_location_id
2513: FROM hz_party_sites
2509: FROM hz_party_sites
2510: WHERE party_site_id = l_temp_from_site;
2511:
2512: SELECT location_id into l_to_location_id
2513: FROM hz_party_sites
2514: WHERE party_site_id = l_master_site;
2515:
2516:
2517: ZX_MERGE_LOC_CHECK_PKG.CHECK_GNR(l_from_location_id,
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
2592: AND mpd.batch_party_id = mps.batch_party_id
2593: AND mps.batch_id = p_batch_id
2594: AND NOT EXISTS
2645: IF (check_mapped_sites_sp%NOTFOUND) THEN
2646:
2647: --4569674
2648: SELECT location_id into l_from_location_id
2649: FROM hz_party_sites
2650: WHERE party_site_id = l_merge_from_site;
2651:
2652: SELECT location_id into l_to_location_id
2653: FROM hz_party_sites
2649: FROM hz_party_sites
2650: WHERE party_site_id = l_merge_from_site;
2651:
2652: SELECT location_id into l_to_location_id
2653: FROM hz_party_sites
2654: WHERE party_site_id = l_master_site;
2655:
2656: ZX_MERGE_LOC_CHECK_PKG.CHECK_GNR(l_from_location_id,
2657: l_to_location_id,
2706: )
2707: VALUES
2708: (
2709: l_batch_party_id
2710: ,'HZ_PARTY_SITES'
2711: ,l_merge_from_site
2712: ,l_master_site
2713: ,'N'
2714: ,hz_utility_v2pub.created_by
3176:
3177: --Cursor for inserting Party sites that are non-DNB
3178: CURSOR c_from_ps_loc IS
3179: SELECT party_site_id, ps.location_id
3180: FROM HZ_PARTY_SITES ps
3181: WHERE ps.party_id = p_from_party_id
3182: AND ps.actual_content_source <>'DNB'
3183: AND nvl(status, 'A') in ('A','I');
3184:
3183: AND nvl(status, 'A') in ('A','I');
3184:
3185: CURSOR c_dup_to_ps(cp_loc_id NUMBER) IS
3186: SELECT party_site_id
3187: FROM HZ_PARTY_SITES ps
3188: WHERE ps.party_id = p_to_party_id
3189: AND ps.location_id = cp_loc_id
3190: AND ps.actual_content_source <>'DNB'
3191: AND nvl(status, 'A') in ('A','I');
3227: )
3228: VALUES
3229: (
3230: p_batch_party_id
3231: ,'HZ_PARTY_SITES'
3232: ,l_ps_id
3233: ,l_dup_ps_id
3234: ,l_mandatory_merge
3235: ,hz_utility_v2pub.created_by
3257: )
3258: VALUES
3259: (
3260: p_batch_party_id
3261: ,'HZ_PARTY_SITES'
3262: ,l_ps_id
3263: ,null
3264: ,'N'
3265: ,hz_utility_v2pub.created_by
3286: )
3287: VALUES
3288: (
3289: p_batch_party_id
3290: ,'HZ_PARTY_SITES'
3291: ,l_ps_id
3292: ,l_ps_id
3293: ,'N'
3294: ,hz_utility_v2pub.created_by
3370: --
3371: -- ARGUMENTS
3372: -- IN:
3373: -- p_batch_id ID of the merge batch
3374: -- p_entity_name HZ_PARTY_SITES - Addresses or HZ_RELATIONSHIPS - Relationships
3375: -- p_merge_type Merge type of the dup set
3376: --
3377: -- OUT:
3378: -- x_return_status Return status after the call. The status can
3527: --
3528: -- ARGUMENTS
3529: -- IN:
3530: -- p_batch_id ID of the merge batch
3531: -- p_entity_name HZ_PARTY_SITES - Addresses or HZ_RELATIONSHIPS - Relationships
3532: -- p_merge_type Merge type of the dup set
3533: --
3534: -- OUT:
3535: -- x_return_status Return status after the call. The status can
4180:
4181: -- remove all party sites mapping
4182: clear_suggested_default (
4183: p_batch_id => l_batch_id
4184: ,p_entity_name => 'HZ_PARTY_SITES'
4185: ,p_merge_type => l_merge_type
4186: ,x_return_status => x_return_status
4187: ,x_msg_count => x_msg_count
4188: ,x_msg_data => x_msg_data );
4247: --
4248: -- ARGUMENTS
4249: -- IN:
4250: -- p_merge_batch_id ID of the merge batch
4251: -- p_entity Name of the entity HZ_PARTY_SITES,
4252: -- HZ_RELATIONSHIPS etc.
4253: -- p_entity_id ID of the entity
4254: -- p_merge_type Merge type of the dup set
4255: --
4634: function get_addresses(p_to_site_id in number, get_from_addr_flag in varchar2) return varchar2
4635: is
4636: cursor get_to_addr_csr is
4637: select hz_format_pub.format_address(l.location_id,null,null,', ')
4638: from hz_party_sites ps, hz_locations l
4639: where ps.location_id = l.location_id
4640: and ps.party_site_id = p_to_site_id;
4641:
4642: cursor get_from_addr_csr is
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
4649: where p.batch_party_id = pd.batch_party_id
4650: and pd.entity_name = 'HZ_PARTY_SITES'
4646: hz_party_sites s,
4647: hz_locations l,
4648: hz_parties hp
4649: where p.batch_party_id = pd.batch_party_id
4650: and pd.entity_name = 'HZ_PARTY_SITES'
4651: and pd.merge_from_entity_id = s.party_site_id
4652: and s.location_id = l.location_id
4653: and hp.party_id = p.from_party_id
4654: and pd.merge_to_entity_id = p_to_site_id;
4684: function is_acct_site_merge_required(p_merge_batch_id in number) return varchar2 is
4685:
4686: cursor acct_site_merge_required_csr is
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
4696: and p1.batch_party_id = pd1.batch_party_id
4697: and pd1.entity_name = 'HZ_PARTY_SITES'
4698: and pd1.merge_from_entity_id = ps1.party_site_id
4699: and pd1.merge_from_entity_id <> pd1.merge_to_entity_id
4700: AND exists
4701: ( select 1 from hz_party_sites ps2,
4697: and pd1.entity_name = 'HZ_PARTY_SITES'
4698: and pd1.merge_from_entity_id = ps1.party_site_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
4708: and as2.org_id = as1.org_id
4709: and p2.batch_party_id = pd2.batch_party_id
4710: and pd2.entity_name = 'HZ_PARTY_SITES'
4711: and pd2.merge_to_entity_id = ps2.party_site_id
4712: and pd2.merge_from_entity_id <> pd2.merge_to_entity_id
4713: and rownum = 1);
4714: l_required varchar2(1);
4732:
4733:
4734: CURSOR check_site_merge_csr IS
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
4744: and p1.batch_party_id = pd1.batch_party_id
4745: and pd1.entity_name = 'HZ_PARTY_SITES'
4746: and pd1.merge_from_entity_id = ps1.party_site_id
4747: and pd1.merge_from_entity_id <> pd1.merge_to_entity_id
4748: AND exists
4749: ( select 1 from hz_party_sites ps2,
4745: and pd1.entity_name = 'HZ_PARTY_SITES'
4746: and pd1.merge_from_entity_id = ps1.party_site_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
4756: and as2.org_id = as1.org_id
4757: and p2.batch_party_id = pd2.batch_party_id
4758: and pd2.entity_name = 'HZ_PARTY_SITES'
4759: and pd2.merge_to_entity_id = ps2.party_site_id
4760: and pd2.merge_from_entity_id <> pd2.merge_to_entity_id
4761: and rownum = 1);
4762: