[Home] [Help]
741:
742: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
743: p_to_fk_id, p_par_entity_name,
744: 'HZ_MERGE_PKG.org_contact_merge',
745: 'HZ_ORG_CONTACTS','HZ_PARTY_SITES',
746: 'ORG_CONTACT_ID', 'PARTY_SITE_ID',x_return_status);
747:
748: IF (x_return_status =FND_API.G_RET_STS_SUCCESS) THEN
749: do_org_contact_transfer(p_from_id,l_to_id, p_from_fk_id,
874: IF p_from_fk_id <> FND_API.G_MISS_NUM THEN
875: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
876: p_to_fk_id, p_par_entity_name,
877: 'HZ_MERGE_PKG.party_site_merge',
878: 'HZ_PARTY_SITES','HZ_PARTIES',
879: 'PARTY_SITE_ID', 'PARTY_ID',x_return_status);
880: ELSE
881: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
882: p_to_fk_id, p_par_entity_name,
880: ELSE
881: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
882: p_to_fk_id, p_par_entity_name,
883: 'HZ_MERGE_PKG.party_site_merge',
884: 'HZ_PARTY_SITES',NULL, 'PARTY_SITE_ID',
885: NULL,x_return_status);
886: END IF;
887:
888: IF (x_return_status =FND_API.G_RET_STS_SUCCESS) THEN
1100:
1101: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
1102: p_to_fk_id, p_par_entity_name,
1103: 'HZ_MERGE_PKG.cust_account_site_merge',
1104: 'HZ_CUST_ACCT_SITES_ALL','HZ_PARTY_SITES',
1105: 'CUST_ACCT_SITE_ID', 'PARTY_SITE_ID',x_return_status);
1106:
1107: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
1108: l_dup_exists := HZ_MERGE_DUP_CHECK.check_cust_account_site_dup(
2120:
2121: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
2122: p_to_fk_id, p_par_entity_name,
2123: 'HZ_MERGE_PKG.code_assignment_merge',
2124: 'HZ_CODE_ASSIGNMENTS','HZ_PARTY_SITES',
2125: 'CODE_ASSIGNMENT_ID', 'PARTY_SITE_ID',x_return_status);
2126:
2127: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
2128: l_dup_exists := HZ_MERGE_DUP_CHECK.check_code_assignment_dup(
2126:
2127: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
2128: l_dup_exists := HZ_MERGE_DUP_CHECK.check_code_assignment_dup(
2129: p_from_id,l_to_id, p_from_fk_id, p_to_fk_id,
2130: 'HZ_PARTY_SITES', x_return_status);
2131: END IF;
2132:
2133: IF (x_return_status =FND_API.G_RET_STS_SUCCESS) THEN
2134: do_code_assignment_transfer2(p_from_id,l_to_id, p_from_fk_id, p_to_fk_id,
2316:
2317: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
2318: p_to_fk_id, p_par_entity_name,
2319: 'HZ_MERGE_PKG.party_site_use_merge',
2320: 'HZ_PARTY_SITE_USES','HZ_PARTY_SITES',
2321: 'PARTY_SITE_USE_ID', 'PARTY_SITE_ID',x_return_status);
2322:
2323: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
2324: l_dup_exists := HZ_MERGE_DUP_CHECK.check_party_site_use_dup(
2367:
2368: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
2369: p_to_fk_id, p_par_entity_name,
2370: 'HZ_MERGE_PKG.contact_point_merge2',
2371: 'HZ_CONTACT_POINTS','HZ_PARTY_SITES',
2372: 'CONTACT_POINT_ID', 'PARTY_SITE_ID',x_return_status);
2373:
2374: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
2375: l_dup_exists := HZ_MERGE_DUP_CHECK.check_contact_point_dup(
2373:
2374: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
2375: l_dup_exists := HZ_MERGE_DUP_CHECK.check_contact_point_dup(
2376: p_from_id,l_to_id, p_from_fk_id, p_to_fk_id,
2377: 'HZ_PARTY_SITES', x_return_status);
2378: END IF;
2379:
2380: IF (x_return_status =FND_API.G_RET_STS_SUCCESS) THEN
2381: do_contact_point_transfer2(p_from_id, l_to_id, p_from_fk_id, p_to_fk_id,
2537:
2538: check_params(p_entity_name, p_from_id, l_to_id, p_from_fk_id,
2539: p_to_fk_id, p_par_entity_name,
2540: 'HZ_MERGE_PKG.contact_preference_merge2',
2541: 'HZ_CONTACT_PREFERENCES','HZ_PARTY_SITES',
2542: 'CONTACT_PREFERENCE_ID', 'PARTY_SITE_ID',x_return_status);
2543:
2544: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
2545: l_dup_exists := HZ_MERGE_DUP_CHECK.check_contact_preference_dup(
2543:
2544: IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND l_to_id = FND_API.G_MISS_NUM) THEN
2545: l_dup_exists := HZ_MERGE_DUP_CHECK.check_contact_preference_dup(
2546: p_from_id,l_to_id, p_from_fk_id, p_to_fk_id,
2547: 'HZ_PARTY_SITES',x_return_status);
2548: END IF;
2549:
2550: IF (x_return_status =FND_API.G_RET_STS_SUCCESS) THEN
2551: do_contact_pref_transfer(p_from_id,l_to_id, p_from_fk_id, p_to_fk_id,
3154:
3155: CURSOR c_fp_cpt_details IS
3156: SELECT contact_point_type, contact_point_purpose,primary_flag,primary_by_purpose
3157: FROM HZ_CONTACT_POINTS
3158: WHERE owner_table_name = 'HZ_PARTY_SITES'
3159: AND contact_point_id = p_from_id
3160: AND rownum=1;
3161:
3162:
3213: INTO l_exists
3214: FROM HZ_CONTACT_POINTS
3215: WHERE PRIMARY_BY_PURPOSE = 'Y'
3216: AND CONTACT_POINT_PURPOSE = l_contact_point_purpose
3217: AND OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
3218: AND CONTACT_POINT_TYPE = l_contact_point_type
3219: AND OWNER_TABLE_ID = p_to_fk_id
3220: AND ROWNUM = 1;
3221:
3230: SELECT 'Exists'
3231: INTO l_exists
3232: FROM HZ_CONTACT_POINTS
3233: WHERE PRIMARY_FLAG = 'Y'
3234: AND OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
3235: AND OWNER_TABLE_ID = p_to_fk_id
3236: AND CONTACT_POINT_TYPE = l_contact_point_type
3237: AND ROWNUM = 1;
3238: EXCEPTION
4322: SELECT 'Exists'
4323: INTO l_exists
4324: FROM HZ_CODE_ASSIGNMENTS
4325: WHERE PRIMARY_FLAG = 'Y'
4326: AND OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
4327: AND OWNER_TABLE_ID = p_to_fk_id
4328: AND CLASS_CATEGORY = l_class_category
4329: AND CONTENT_SOURCE_TYPE = l_cont_src
4330: AND ROWNUM = 1;
4477: l_msg_count NUMBER := 0;
4478:
4479: ----Start of Bug No: 3560167-------------------
4480: CURSOR c_party_id(p_party_site_id NUMBER) IS
4481: SELECT PARTY_ID FROM HZ_PARTY_SITES
4482: WHERE PARTY_SITE_ID = p_party_site_id
4483: AND ROWNUM =1;
4484:
4485: CURSOR c_prim_site_uses(p_party_id NUMBER,p_party_site_id NUMBER,p_site_use_type VARCHAR2,p_request_id NUMBER)
4485: CURSOR c_prim_site_uses(p_party_id NUMBER,p_party_site_id NUMBER,p_site_use_type VARCHAR2,p_request_id NUMBER)
4486: IS SELECT 1, request_id FROM HZ_PARTY_SITE_USES SU
4487: WHERE SU.PARTY_SITE_ID IN (
4488: SELECT PS.PARTY_SITE_ID
4489: FROM HZ_PARTY_SITES PS
4490: WHERE PARTY_ID = p_party_id )
4491: AND SU.PARTY_SITE_ID <> p_party_site_id
4492: AND SU.PARTY_SITE_ID <> p_from_fk_id
4493: AND SU.SITE_USE_TYPE = p_site_use_type
6243:
6244: CURSOR c_cont_source IS
6245: SELECT l.CONTENT_SOURCE_TYPE, l.last_update_date, ps.party_id,
6246: ps.identifying_address_flag, ps.party_site_number
6247: FROM HZ_LOCATIONS l, HZ_PARTY_SITES ps
6248: WHERE ps.party_site_id = p_from_id
6249: AND ps.location_id = l.location_id;
6250:
6251: CURSOR c_merge_to IS
6252: SELECT ps.party_id, loc.content_source_type,loc.country,
6253: loc.address1, loc.address2, loc.address3, loc.address4,
6254: loc.city, loc.postal_code, loc.state, loc.province,
6255: loc.county,loc.location_id
6256: FROM HZ_LOCATIONS loc, HZ_PARTY_SITES ps
6257: WHERE ps.party_site_id = x_to_id
6258: AND ps.location_id = loc.location_id;
6259: --bug 4569674
6260: /*CURSOR c_loc_assignments IS
6258: AND ps.location_id = loc.location_id;
6259: --bug 4569674
6260: /*CURSOR c_loc_assignments IS
6261: SELECT la.loc_id, la.org_id
6262: FROM HZ_LOC_ASSIGNMENTS la, HZ_PARTY_SITES ps
6263: WHERE ps.party_site_id = p_from_id
6264: AND la.location_id = ps.location_id
6265: AND la.org_id NOT IN ( SELECT DISTINCT la1.org_id
6266: FROM HZ_LOC_ASSIGNMENTS la1, HZ_PARTY_SITES ps1
6262: FROM HZ_LOC_ASSIGNMENTS la, HZ_PARTY_SITES ps
6263: WHERE ps.party_site_id = p_from_id
6264: AND la.location_id = ps.location_id
6265: AND la.org_id NOT IN ( SELECT DISTINCT la1.org_id
6266: FROM HZ_LOC_ASSIGNMENTS la1, HZ_PARTY_SITES ps1
6267: WHERE ps1.party_site_id = x_to_id
6268: AND la1.location_id = ps1.location_id
6269: );
6270: */
6269: );
6270: */
6271: CURSOR c_locations(x_party_site_id NUMBER) IS
6272: SELECT loc.location_id
6273: FROM HZ_PARTY_SITES ps,
6274: HZ_LOCATIONS loc
6275: WHERE ps.location_id = loc.location_id
6276: AND ps.party_site_id = x_party_site_id;
6277:
6276: AND ps.party_site_id = x_party_site_id;
6277:
6278: CURSOR c_from_party_type IS
6279: SELECT party_type
6280: FROM HZ_PARTY_SITES ps,
6281: HZ_PARTIES p
6282: WHERE ps.party_id = p.party_id
6283: AND ps.party_site_id = p_from_id;
6284:
6296: l_state HZ_LOCATIONS.STATE%TYPE;
6297: l_county HZ_LOCATIONS.COUNTY%TYPE;
6298: l_province HZ_LOCATIONS.PROVINCE%TYPE;
6299: l_postal HZ_LOCATIONS.POSTAL_CODE%TYPE;
6300: l_from_party_id HZ_PARTY_SITES.PARTY_ID%TYPE;
6301: l_to_party_id HZ_PARTY_SITES.PARTY_ID%TYPE;
6302: l_ident_flag HZ_PARTY_SITES.IDENTIFYING_ADDRESS_FLAG%TYPE;
6303: l_merge_to_id NUMBER;
6304: l_merge_to_loc_id NUMBER;
6297: l_county HZ_LOCATIONS.COUNTY%TYPE;
6298: l_province HZ_LOCATIONS.PROVINCE%TYPE;
6299: l_postal HZ_LOCATIONS.POSTAL_CODE%TYPE;
6300: l_from_party_id HZ_PARTY_SITES.PARTY_ID%TYPE;
6301: l_to_party_id HZ_PARTY_SITES.PARTY_ID%TYPE;
6302: l_ident_flag HZ_PARTY_SITES.IDENTIFYING_ADDRESS_FLAG%TYPE;
6303: l_merge_to_id NUMBER;
6304: l_merge_to_loc_id NUMBER;
6305: l_temp NUMBER;
6298: l_province HZ_LOCATIONS.PROVINCE%TYPE;
6299: l_postal HZ_LOCATIONS.POSTAL_CODE%TYPE;
6300: l_from_party_id HZ_PARTY_SITES.PARTY_ID%TYPE;
6301: l_to_party_id HZ_PARTY_SITES.PARTY_ID%TYPE;
6302: l_ident_flag HZ_PARTY_SITES.IDENTIFYING_ADDRESS_FLAG%TYPE;
6303: l_merge_to_id NUMBER;
6304: l_merge_to_loc_id NUMBER;
6305: l_temp NUMBER;
6306: l_temp1 NUMBER;
6315: l_msg_data VARCHAR2(2000);
6316: l_msg_count NUMBER := 0;
6317: l_profile_option VARCHAR2(1) := 'Y';
6318:
6319: l_ps_number HZ_PARTY_SITES.PARTY_SITE_NUMBER%TYPE;
6320: l_actual_cont_source VARCHAR2(30);
6321: --l_loc_id HZ_LOC_ASSIGNMENTS.LOC_ID%TYPE; bug 4569674
6322: --l_org_id HZ_LOC_ASSIGNMENTS.ORG_ID%TYPE;
6323: l_from_location_id HZ_LOCATIONS.LOCATION_ID%TYPE;
6323: l_from_location_id HZ_LOCATIONS.LOCATION_ID%TYPE;
6324: l_to_location_id HZ_LOCATIONS.LOCATION_ID%TYPE;
6325: l_from_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
6326: to_party_loc_id HZ_LOCATIONS.LOCATION_ID%TYPE;
6327: map_ps_id HZ_PARTY_SITES.PARTY_SITE_ID%TYPE;
6328: BEGIN
6329:
6330:
6331: OPEN c_cont_source;
6350:
6351: /* Check if there is a ps in the merge batch that has the same loc */
6352: SELECT ps2.location_id INTO to_party_loc_id
6353: FROM HZ_MERGE_PARTY_DETAILS mpd,
6354: HZ_PARTY_SITES ps1,
6355: HZ_PARTY_SITES ps2
6356: WHERE ps1.party_site_id = mpd.merge_from_entity_id
6357: AND ps2.party_site_id = mpd.merge_to_entity_id
6358: AND mpd.merge_from_entity_id <> nvl(mpd.merge_to_entity_id, -1)
6351: /* Check if there is a ps in the merge batch that has the same loc */
6352: SELECT ps2.location_id INTO to_party_loc_id
6353: FROM HZ_MERGE_PARTY_DETAILS mpd,
6354: HZ_PARTY_SITES ps1,
6355: HZ_PARTY_SITES ps2
6356: WHERE ps1.party_site_id = mpd.merge_from_entity_id
6357: AND ps2.party_site_id = mpd.merge_to_entity_id
6358: AND mpd.merge_from_entity_id <> nvl(mpd.merge_to_entity_id, -1)
6359: AND ps1.location_id = l_from_location_id
6368:
6369: BEGIN
6370: /* Check if to_party already has a ps which has the same loc */
6371: SELECT party_site_id INTO map_ps_id
6372: FROM HZ_PARTY_SITES
6373: WHERE party_id = p_to_fk_id
6374: AND location_id = to_party_loc_id
6375: AND rownum = 1;
6376:
6378: x_to_id := map_ps_id;
6379: EXCEPTION
6380: WHEN NO_DATA_FOUND THEN
6381: /* Transfer */
6382: UPDATE HZ_PARTY_SITES
6383: SET LOCATION_ID = to_party_loc_id
6384: WHERE PARTY_SITE_ID = p_from_id ;
6385: END;
6386: EXCEPTION
6414: OPEN c_locations(x_to_id);
6415: FETCH c_locations INTO l_to_location_id;
6416: CLOSE c_locations;
6417:
6418: UPDATE HZ_PARTY_SITES
6419: SET location_id = l_to_location_id
6420: WHERE party_site_id IN (
6421: SELECT psr.party_site_id
6422: FROM hz_parties p,
6419: SET location_id = l_to_location_id
6420: WHERE party_site_id IN (
6421: SELECT psr.party_site_id
6422: FROM hz_parties p,
6423: hz_party_sites psr,
6424: hz_party_sites pso,
6425: hz_relationships r
6426: WHERE pso.party_site_id = p_from_id
6427: AND r.object_id = pso.party_id
6420: WHERE party_site_id IN (
6421: SELECT psr.party_site_id
6422: FROM hz_parties p,
6423: hz_party_sites psr,
6424: hz_party_sites pso,
6425: hz_relationships r
6426: WHERE pso.party_site_id = p_from_id
6427: AND r.object_id = pso.party_id
6428: AND r.party_id = p.party_id
6457: WHERE party_id = l_to_party_id;
6458:
6459: END IF;
6460:
6461: UPDATE HZ_PARTY_SITES
6462: SET
6463: identifying_address_flag = 'Y'
6464: WHERE party_site_id = x_to_id;
6465:
6462: SET
6463: identifying_address_flag = 'Y'
6464: WHERE party_site_id = x_to_id;
6465:
6466: UPDATE HZ_PARTY_SITES
6467: SET
6468: identifying_address_flag = 'N'
6469: WHERE party_site_id = p_from_id;
6470:
6480: state = l_state,
6481: province = l_province,
6482: county = l_county
6483: WHERE party_id in
6484: (select ps.party_id from hz_party_sites ps,hz_relationships pr
6485: where ps.location_id = l_to_location_id
6486: and ps.identifying_address_flag(+)='Y'
6487: and ps.party_id = pr.party_id
6488: and pr.object_id=l_to_party_id);
6510:
6511: IF ((x_to_id <> FND_API.G_MISS_NUM AND
6512: x_to_id <> p_from_id) OR l_discard = 'Y') THEN
6513:
6514: UPDATE HZ_PARTY_SITES
6515: SET
6516: STATUS = 'M',
6517: last_update_date = hz_utility_pub.last_update_date,
6518: last_updated_by = hz_utility_pub.user_id,
6528: STATUS = 'I',
6529: last_update_date = hz_utility_pub.last_update_date,
6530: last_updated_by = hz_utility_pub.user_id,
6531: last_update_login = hz_utility_pub.last_update_login
6532: WHERE owner_table_id = p_from_id and owner_table_name='HZ_PARTY_SITES';
6533: */
6534: HZ_ORIG_SYSTEM_REF_PVT.remap_internal_identifier(
6535: p_init_msg_list => FND_API.G_FALSE,
6536: p_validation_level => FND_API.G_VALID_LEVEL_NONE,
6535: p_init_msg_list => FND_API.G_FALSE,
6536: p_validation_level => FND_API.G_VALID_LEVEL_NONE,
6537: p_old_owner_table_id => p_from_id,
6538: p_new_owner_table_id => x_to_id,
6539: p_owner_table_name =>'HZ_PARTY_SITES',
6540: p_orig_system => null,
6541: p_orig_system_reference => null,
6542: p_reason_code => 'MERGED',
6543: x_return_status => x_return_status,
6588:
6589: FOR I IN 1..100 LOOP
6590: BEGIN
6591: -- Update and set party_id = p_to_fk_id where pk = from_id
6592: UPDATE HZ_PARTY_SITES
6593: SET
6594: STATUS = 'M',
6595: party_site_number = l_party_site_number,
6596: last_update_date = hz_utility_pub.last_update_date,
6617: p_init_msg_list => FND_API.G_FALSE,
6618: p_validation_level => FND_API.G_VALID_LEVEL_NONE,
6619: p_old_owner_table_id => p_from_id,
6620: p_new_owner_table_id => x_to_id,
6621: p_owner_table_name =>'HZ_PARTY_SITES',
6622: p_orig_system => null,
6623: p_orig_system_reference => null,
6624: p_reason_code => 'MERGED',
6625: x_return_status => x_return_status,
6665: p_init_msg_list => FND_API.G_FALSE,
6666: p_validation_level => FND_API.G_VALID_LEVEL_NONE,
6667: p_old_owner_table_id => p_from_id,
6668: p_new_owner_table_id => x_to_id,
6669: p_owner_table_name =>'HZ_PARTY_SITES',
6670: p_orig_system => null,
6671: p_orig_system_reference => null,
6672: p_reason_code => 'MERGED',
6673: x_return_status => x_return_status,
6685: SET merge_to_entity_id = x_to_id
6686: WHERE batch_party_id IN (select batch_party_id from hz_merge_parties
6687: where batch_id = p_batch_id)
6688: AND merge_to_entity_id = p_from_id
6689: AND entity_name = 'HZ_PARTY_SITES';
6690:
6691: SAVEPOINT party_site_sync;
6692: BEGIN
6693: hz_dqm_sync.sync_party_site(x_to_id,'C'); --Bug 13810656