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