[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,
3153:
3154: CURSOR c_fp_cpt_details IS
3155: SELECT contact_point_type, contact_point_purpose,primary_flag,primary_by_purpose
3156: FROM HZ_CONTACT_POINTS
3157: WHERE owner_table_name = 'HZ_PARTY_SITES'
3158: AND contact_point_id = p_from_id
3159: AND rownum=1;
3160:
3161:
3211: INTO l_exists
3212: FROM HZ_CONTACT_POINTS
3213: WHERE PRIMARY_BY_PURPOSE = 'Y'
3214: AND CONTACT_POINT_PURPOSE = l_contact_point_purpose
3215: AND OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
3216: AND CONTACT_POINT_TYPE = l_contact_point_type
3217: AND OWNER_TABLE_ID = p_to_fk_id
3218: AND ROWNUM = 1;
3219:
3228: SELECT 'Exists'
3229: INTO l_exists
3230: FROM HZ_CONTACT_POINTS
3231: WHERE PRIMARY_FLAG = 'Y'
3232: AND OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
3233: AND OWNER_TABLE_ID = p_to_fk_id
3234: AND CONTACT_POINT_TYPE = l_contact_point_type
3235: AND ROWNUM = 1;
3236: EXCEPTION
4320: SELECT 'Exists'
4321: INTO l_exists
4322: FROM HZ_CODE_ASSIGNMENTS
4323: WHERE PRIMARY_FLAG = 'Y'
4324: AND OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
4325: AND OWNER_TABLE_ID = p_to_fk_id
4326: AND CLASS_CATEGORY = l_class_category
4327: AND CONTENT_SOURCE_TYPE = l_cont_src
4328: AND ROWNUM = 1;
4475: l_msg_count NUMBER := 0;
4476:
4477: ----Start of Bug No: 3560167-------------------
4478: CURSOR c_party_id(p_party_site_id NUMBER) IS
4479: SELECT PARTY_ID FROM HZ_PARTY_SITES
4480: WHERE PARTY_SITE_ID = p_party_site_id
4481: AND ROWNUM =1;
4482:
4483: CURSOR c_prim_site_uses(p_party_id NUMBER,p_party_site_id NUMBER,p_site_use_type VARCHAR2,p_request_id NUMBER)
4483: CURSOR c_prim_site_uses(p_party_id NUMBER,p_party_site_id NUMBER,p_site_use_type VARCHAR2,p_request_id NUMBER)
4484: IS SELECT 1 FROM HZ_PARTY_SITE_USES SU
4485: WHERE SU.PARTY_SITE_ID IN (
4486: SELECT PS.PARTY_SITE_ID
4487: FROM HZ_PARTY_SITES PS
4488: WHERE PARTY_ID = p_party_id )
4489: AND SU.PARTY_SITE_ID <> p_party_site_id
4490: AND SU.SITE_USE_TYPE = p_site_use_type
4491: AND SU.PRIMARY_PER_TYPE = 'Y'
6113:
6114: CURSOR c_cont_source IS
6115: SELECT l.CONTENT_SOURCE_TYPE, l.last_update_date, ps.party_id,
6116: ps.identifying_address_flag, ps.party_site_number
6117: FROM HZ_LOCATIONS l, HZ_PARTY_SITES ps
6118: WHERE ps.party_site_id = p_from_id
6119: AND ps.location_id = l.location_id;
6120:
6121: CURSOR c_merge_to IS
6122: SELECT ps.party_id, loc.content_source_type,loc.country,
6123: loc.address1, loc.address2, loc.address3, loc.address4,
6124: loc.city, loc.postal_code, loc.state, loc.province,
6125: loc.county,loc.location_id
6126: FROM HZ_LOCATIONS loc, HZ_PARTY_SITES ps
6127: WHERE ps.party_site_id = x_to_id
6128: AND ps.location_id = loc.location_id;
6129: --bug 4569674
6130: /*CURSOR c_loc_assignments IS
6128: AND ps.location_id = loc.location_id;
6129: --bug 4569674
6130: /*CURSOR c_loc_assignments IS
6131: SELECT la.loc_id, la.org_id
6132: FROM HZ_LOC_ASSIGNMENTS la, HZ_PARTY_SITES ps
6133: WHERE ps.party_site_id = p_from_id
6134: AND la.location_id = ps.location_id
6135: AND la.org_id NOT IN ( SELECT DISTINCT la1.org_id
6136: FROM HZ_LOC_ASSIGNMENTS la1, HZ_PARTY_SITES ps1
6132: FROM HZ_LOC_ASSIGNMENTS la, HZ_PARTY_SITES ps
6133: WHERE ps.party_site_id = p_from_id
6134: AND la.location_id = ps.location_id
6135: AND la.org_id NOT IN ( SELECT DISTINCT la1.org_id
6136: FROM HZ_LOC_ASSIGNMENTS la1, HZ_PARTY_SITES ps1
6137: WHERE ps1.party_site_id = x_to_id
6138: AND la1.location_id = ps1.location_id
6139: );
6140: */
6139: );
6140: */
6141: CURSOR c_locations(x_party_site_id NUMBER) IS
6142: SELECT loc.location_id
6143: FROM HZ_PARTY_SITES ps,
6144: HZ_LOCATIONS loc
6145: WHERE ps.location_id = loc.location_id
6146: AND ps.party_site_id = x_party_site_id;
6147:
6146: AND ps.party_site_id = x_party_site_id;
6147:
6148: CURSOR c_from_party_type IS
6149: SELECT party_type
6150: FROM HZ_PARTY_SITES ps,
6151: HZ_PARTIES p
6152: WHERE ps.party_id = p.party_id
6153: AND ps.party_site_id = p_from_id;
6154:
6166: l_state HZ_LOCATIONS.STATE%TYPE;
6167: l_county HZ_LOCATIONS.COUNTY%TYPE;
6168: l_province HZ_LOCATIONS.PROVINCE%TYPE;
6169: l_postal HZ_LOCATIONS.POSTAL_CODE%TYPE;
6170: l_from_party_id HZ_PARTY_SITES.PARTY_ID%TYPE;
6171: l_to_party_id HZ_PARTY_SITES.PARTY_ID%TYPE;
6172: l_ident_flag HZ_PARTY_SITES.IDENTIFYING_ADDRESS_FLAG%TYPE;
6173: l_merge_to_id NUMBER;
6174: l_merge_to_loc_id NUMBER;
6167: l_county HZ_LOCATIONS.COUNTY%TYPE;
6168: l_province HZ_LOCATIONS.PROVINCE%TYPE;
6169: l_postal HZ_LOCATIONS.POSTAL_CODE%TYPE;
6170: l_from_party_id HZ_PARTY_SITES.PARTY_ID%TYPE;
6171: l_to_party_id HZ_PARTY_SITES.PARTY_ID%TYPE;
6172: l_ident_flag HZ_PARTY_SITES.IDENTIFYING_ADDRESS_FLAG%TYPE;
6173: l_merge_to_id NUMBER;
6174: l_merge_to_loc_id NUMBER;
6175: l_temp NUMBER;
6168: l_province HZ_LOCATIONS.PROVINCE%TYPE;
6169: l_postal HZ_LOCATIONS.POSTAL_CODE%TYPE;
6170: l_from_party_id HZ_PARTY_SITES.PARTY_ID%TYPE;
6171: l_to_party_id HZ_PARTY_SITES.PARTY_ID%TYPE;
6172: l_ident_flag HZ_PARTY_SITES.IDENTIFYING_ADDRESS_FLAG%TYPE;
6173: l_merge_to_id NUMBER;
6174: l_merge_to_loc_id NUMBER;
6175: l_temp NUMBER;
6176: l_temp1 NUMBER;
6185: l_msg_data VARCHAR2(2000);
6186: l_msg_count NUMBER := 0;
6187: l_profile_option VARCHAR2(1) := 'Y';
6188:
6189: l_ps_number HZ_PARTY_SITES.PARTY_SITE_NUMBER%TYPE;
6190: l_actual_cont_source VARCHAR2(30);
6191: --l_loc_id HZ_LOC_ASSIGNMENTS.LOC_ID%TYPE; bug 4569674
6192: --l_org_id HZ_LOC_ASSIGNMENTS.ORG_ID%TYPE;
6193: l_from_location_id HZ_LOCATIONS.LOCATION_ID%TYPE;
6193: l_from_location_id HZ_LOCATIONS.LOCATION_ID%TYPE;
6194: l_to_location_id HZ_LOCATIONS.LOCATION_ID%TYPE;
6195: l_from_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
6196: to_party_loc_id HZ_LOCATIONS.LOCATION_ID%TYPE;
6197: map_ps_id HZ_PARTY_SITES.PARTY_SITE_ID%TYPE;
6198: BEGIN
6199:
6200:
6201: OPEN c_cont_source;
6220:
6221: /* Check if there is a ps in the merge batch that has the same loc */
6222: SELECT ps2.location_id INTO to_party_loc_id
6223: FROM HZ_MERGE_PARTY_DETAILS mpd,
6224: HZ_PARTY_SITES ps1,
6225: HZ_PARTY_SITES ps2
6226: WHERE ps1.party_site_id = mpd.merge_from_entity_id
6227: AND ps2.party_site_id = mpd.merge_to_entity_id
6228: AND mpd.merge_from_entity_id <> nvl(mpd.merge_to_entity_id, -1)
6221: /* Check if there is a ps in the merge batch that has the same loc */
6222: SELECT ps2.location_id INTO to_party_loc_id
6223: FROM HZ_MERGE_PARTY_DETAILS mpd,
6224: HZ_PARTY_SITES ps1,
6225: HZ_PARTY_SITES ps2
6226: WHERE ps1.party_site_id = mpd.merge_from_entity_id
6227: AND ps2.party_site_id = mpd.merge_to_entity_id
6228: AND mpd.merge_from_entity_id <> nvl(mpd.merge_to_entity_id, -1)
6229: AND ps1.location_id = l_from_location_id
6238:
6239: BEGIN
6240: /* Check if to_party already has a ps which has the same loc */
6241: SELECT party_site_id INTO map_ps_id
6242: FROM HZ_PARTY_SITES
6243: WHERE party_id = p_to_fk_id
6244: AND location_id = to_party_loc_id
6245: AND rownum = 1;
6246:
6248: x_to_id := map_ps_id;
6249: EXCEPTION
6250: WHEN NO_DATA_FOUND THEN
6251: /* Transfer */
6252: UPDATE HZ_PARTY_SITES
6253: SET LOCATION_ID = to_party_loc_id
6254: WHERE PARTY_SITE_ID = p_from_id ;
6255: END;
6256: EXCEPTION
6284: OPEN c_locations(x_to_id);
6285: FETCH c_locations INTO l_to_location_id;
6286: CLOSE c_locations;
6287:
6288: UPDATE HZ_PARTY_SITES
6289: SET location_id = l_to_location_id
6290: WHERE party_site_id IN (
6291: SELECT psr.party_site_id
6292: FROM hz_parties p,
6289: SET location_id = l_to_location_id
6290: WHERE party_site_id IN (
6291: SELECT psr.party_site_id
6292: FROM hz_parties p,
6293: hz_party_sites psr,
6294: hz_party_sites pso,
6295: hz_relationships r
6296: WHERE pso.party_site_id = p_from_id
6297: AND r.object_id = pso.party_id
6290: WHERE party_site_id IN (
6291: SELECT psr.party_site_id
6292: FROM hz_parties p,
6293: hz_party_sites psr,
6294: hz_party_sites pso,
6295: hz_relationships r
6296: WHERE pso.party_site_id = p_from_id
6297: AND r.object_id = pso.party_id
6298: AND r.party_id = p.party_id
6327: WHERE party_id = l_to_party_id;
6328:
6329: END IF;
6330:
6331: UPDATE HZ_PARTY_SITES
6332: SET
6333: identifying_address_flag = 'Y'
6334: WHERE party_site_id = x_to_id;
6335:
6332: SET
6333: identifying_address_flag = 'Y'
6334: WHERE party_site_id = x_to_id;
6335:
6336: UPDATE HZ_PARTY_SITES
6337: SET
6338: identifying_address_flag = 'N'
6339: WHERE party_site_id = p_from_id;
6340:
6350: state = l_state,
6351: province = l_province,
6352: county = l_county
6353: WHERE party_id in
6354: (select ps.party_id from hz_party_sites ps,hz_relationships pr
6355: where ps.location_id = l_to_location_id
6356: and ps.identifying_address_flag(+)='Y'
6357: and ps.party_id = pr.party_id
6358: and pr.object_id=l_to_party_id);
6380:
6381: IF ((x_to_id <> FND_API.G_MISS_NUM AND
6382: x_to_id <> p_from_id) OR l_discard = 'Y') THEN
6383:
6384: UPDATE HZ_PARTY_SITES
6385: SET
6386: STATUS = 'M',
6387: last_update_date = hz_utility_pub.last_update_date,
6388: last_updated_by = hz_utility_pub.user_id,
6398: STATUS = 'I',
6399: last_update_date = hz_utility_pub.last_update_date,
6400: last_updated_by = hz_utility_pub.user_id,
6401: last_update_login = hz_utility_pub.last_update_login
6402: WHERE owner_table_id = p_from_id and owner_table_name='HZ_PARTY_SITES';
6403: */
6404: HZ_ORIG_SYSTEM_REF_PVT.remap_internal_identifier(
6405: p_init_msg_list => FND_API.G_FALSE,
6406: p_validation_level => FND_API.G_VALID_LEVEL_NONE,
6405: p_init_msg_list => FND_API.G_FALSE,
6406: p_validation_level => FND_API.G_VALID_LEVEL_NONE,
6407: p_old_owner_table_id => p_from_id,
6408: p_new_owner_table_id => x_to_id,
6409: p_owner_table_name =>'HZ_PARTY_SITES',
6410: p_orig_system => null,
6411: p_orig_system_reference => null,
6412: p_reason_code => 'MERGED',
6413: x_return_status => x_return_status,
6458:
6459: FOR I IN 1..100 LOOP
6460: BEGIN
6461: -- Update and set party_id = p_to_fk_id where pk = from_id
6462: UPDATE HZ_PARTY_SITES
6463: SET
6464: STATUS = 'M',
6465: party_site_number = l_party_site_number,
6466: last_update_date = hz_utility_pub.last_update_date,
6487: p_init_msg_list => FND_API.G_FALSE,
6488: p_validation_level => FND_API.G_VALID_LEVEL_NONE,
6489: p_old_owner_table_id => p_from_id,
6490: p_new_owner_table_id => x_to_id,
6491: p_owner_table_name =>'HZ_PARTY_SITES',
6492: p_orig_system => null,
6493: p_orig_system_reference => null,
6494: p_reason_code => 'MERGED',
6495: x_return_status => x_return_status,
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,
6555: SET merge_to_entity_id = x_to_id
6556: WHERE batch_party_id IN (select batch_party_id from hz_merge_parties
6557: where batch_id = p_batch_id)
6558: AND merge_to_entity_id = p_from_id
6559: AND entity_name = 'HZ_PARTY_SITES';
6560:
6561: SAVEPOINT party_site_sync;
6562: BEGIN
6563: HZ_STAGE_MAP_TRANSFORM.sync_single_party_site(x_to_id,'C');