[Home] [Help]
293:
294: -----Cursor for grouping parties based on distinct merge_to party-----
295: CURSOR c_pre_merge(cp_batch_id NUMBER) IS
296: SELECT DISTINCT(to_party_id)
297: FROM HZ_MERGE_PARTIES mp, hz_parties p
298: WHERE batch_id = cp_batch_id
299: AND p.party_id = mp.from_party_id
300: AND p.party_type <> 'PARTY_RELATIONSHIP';
301:
300: AND p.party_type <> 'PARTY_RELATIONSHIP';
301:
302: CURSOR c_pre_merge_type(cp_batch_id NUMBER, cp_to_party_id NUMBER) IS
303: SELECT mp.merge_type
304: FROM HZ_MERGE_PARTIES mp, hz_parties p
305: WHERE mp.batch_id = cp_batch_id
306: AND mp.to_party_id = cp_to_party_id
307: AND p.party_id = mp.to_party_id
308: AND p.party_type <> 'PARTY_RELATIONSHIP'
311:
312: -----Cursor to get merge from-merge to pair parties and the type-----
313: CURSOR c_batch_details(cp_batch_id NUMBER,cp_to_party_id IN NUMBER) IS
314: SELECT batch_party_id, merge_type, from_party_id, to_party_id, merge_status,party_type, decode(op.actual_content_source, 'DNB', 'DNB', NULL)
315: FROM HZ_MERGE_PARTIES mp, HZ_PARTIES pt, hz_organization_profiles op
316: WHERE batch_id = cp_batch_id
317: AND pt.party_id = mp.from_party_id
318: AND op.party_id(+) = pt.party_id
319: AND op.actual_content_source(+) = 'DNB'
725: IF l_merge_type = 'PARTY_MERGE' THEN
726: outandlog('.... Merging Parties: From Party ID='||l_from_party_id ||
727: ',To Party ID='||l_to_party_id);
728:
729: -- Fetch the dictionary id for the HZ_PARTIES entity
730: OPEN c_dict_id(l_rule_set_name,'HZ_PARTIES');
731: FETCH c_dict_id INTO l_dict_id;
732: IF c_dict_id%NOTFOUND THEN
733: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
726: outandlog('.... Merging Parties: From Party ID='||l_from_party_id ||
727: ',To Party ID='||l_to_party_id);
728:
729: -- Fetch the dictionary id for the HZ_PARTIES entity
730: OPEN c_dict_id(l_rule_set_name,'HZ_PARTIES');
731: FETCH c_dict_id INTO l_dict_id;
732: IF c_dict_id%NOTFOUND THEN
733: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
734: FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_PARTIES');
730: OPEN c_dict_id(l_rule_set_name,'HZ_PARTIES');
731: FETCH c_dict_id INTO l_dict_id;
732: IF c_dict_id%NOTFOUND THEN
733: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
734: FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_PARTIES');
735: FND_MSG_PUB.ADD;
736: RAISE FND_API.G_EXC_ERROR;
737: END IF;
738: CLOSE c_dict_id;
749: END IF;
750:
751: -- Call the recursive merge procedure performing this merge
752: do_merge(p_batch_party_id =>l_batch_party_id,
753: p_entity_name =>'HZ_PARTIES',
754: p_par_entity_name =>NULL,
755: p_from_id =>l_from_party_id,
756: p_to_id =>l_to_party_id,
757: p_par_from_id =>NULL,
1022:
1023: SELECT decode(party_type,'PERSON','HZ_PERSON_PROFILES',
1024: 'ORGANIZATION','HZ_ORGANIZATION_PROFILES',
1025: 'HZ_ORGANIZATION_PROFILES') INTO l_party_type
1026: FROM HZ_PARTIES
1027: WHERE party_id=l_pre_merge_to_party_id;
1028:
1029:
1030: HZ_MERGE_ENTITY_ATTRI_PVT.do_profile_attribute_merge(
1724: l_subrec_str := l_subrec_str || ' AND ' || l_join_clause;
1725: END IF;
1726: --4500011
1727: IF (l_sub_entity_name = 'HZ_PARTY_RELATIONSHIPS') THEN
1728: l_subrec_str := l_subrec_str || ' AND ' || ' subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES'' AND directional_flag = ''F''';
1729: END IF;
1730:
1731: BEGIN
1732: OPEN c_sub_records FOR l_subrec_str using p_from_id;
2223: FETCH c_deleted_records INTO l_record_id, l_entity_name, l_pkcol;
2224:
2225: EXIT WHEN c_deleted_records%NOTFOUND;
2226:
2227: IF l_entity_name = 'HZ_PARTIES' OR
2228: l_entity_name = 'HZ_PARTY_SITES' OR
2229: l_entity_name = 'HZ_ORGANIZATION_PROFILES' OR
2230: l_entity_name = 'HZ_PERSON_PROFILES' OR
2231: l_entity_name = 'HZ_ORG_CONTACTS' OR
2236: l_pkcol || ' = :pk';
2237: ELSIF l_entity_name = 'HZ_PARTY_RELATIONSHIPS' THEN --4500011
2238: rec_delete := 'UPDATE HZ_RELATIONSHIPS' ||
2239: ' SET STATUS = ''D'' WHERE ' ||
2240: 'RELATIONSHIP_ID' || ' = :pk'||'AND subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES'' AND directional_flag = ''F''';
2241: ELSE
2242: -- Construct dynamic SQL query to fetch description
2243: rec_delete := 'DELETE FROM ' || l_entity_name ||
2244: ' WHERE ' || l_pkcol || ' = :pk';
2280:
2281: IF (p_entity_name = 'HZ_PARTY_RELATIONSHIPS') THEN
2282: rec_query := 'SELECT hz_merge_util.get_party_reln_description(relationship_id) FROM HZ_RELATIONSHIPS'
2283: || ' WHERE RELATIONSHIP_ID = '|| ':pk'
2284: || ' AND subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES'' AND directional_flag = ''F''';
2285:
2286: ELSE
2287: rec_query := 'SELECT ' || p_desc_col_name ||
2288: ' FROM ' || p_entity_name || ' WHERE ' ||
2957: p_from_id => l_merge_to_entity_id,
2958: x_to_id => l_new_party_site_id,
2959: p_par_from_id => l_from_party_id,
2960: p_par_to_id => p_to_party_id,
2961: p_parent_entity => 'HZ_PARTIES',
2962: p_batch_id => p_batch_id,
2963: p_batch_party_id=> l_batch_party_id,
2964: x_return_status => l_return_status);
2965:
3031: select subject_id , object_id
3032: into l_subject_id , l_object_id
3033: from HZ_RELATIONSHIPS --4500011
3034: where relationship_id = l_merge_to_entity_id
3035: and subject_table_name = 'HZ_PARTIES'
3036: and object_table_name = 'HZ_PARTIES'
3037: and directional_flag = 'F';
3038:
3039: if l_subject_id = l_from_party_id then
3032: into l_subject_id , l_object_id
3033: from HZ_RELATIONSHIPS --4500011
3034: where relationship_id = l_merge_to_entity_id
3035: and subject_table_name = 'HZ_PARTIES'
3036: and object_table_name = 'HZ_PARTIES'
3037: and directional_flag = 'F';
3038:
3039: if l_subject_id = l_from_party_id then
3040: l_proc_name := 'HZ_MERGE_PKG.party_reln_subject_merge';
3059: p_from_id => l_merge_to_entity_id,
3060: x_to_id => l_merge_to_entity_id,
3061: p_par_from_id => l_from_party_id,
3062: p_par_to_id => p_to_party_id,
3063: p_parent_entity => 'HZ_PARTIES',
3064: p_batch_id => p_batch_id,
3065: p_batch_party_id=> l_batch_party_id,
3066: x_return_status => l_return_status);
3067:
3376: ) IS
3377:
3378: CURSOR c_party_type(cp_party_id NUMBER) IS
3379: SELECT party_type
3380: FROM HZ_PARTIES
3381: WHERE party_id = cp_party_id;
3382:
3383: CURSOR c_duns IS
3384: SELECT duns_number_c, last_update_date, organization_profile_id,actual_content_source
3402: WHERE content_source_type = 'DNB'
3403: AND subject_id = p_from_id
3404: AND object_id = x_to_id
3405: AND RELATIONSHIP_CODE = 'HEADQUARTERS_OF'
3406: AND subject_table_name = 'HZ_PARTIES'
3407: AND object_table_name = 'HZ_PARTIES'
3408: AND directional_flag = 'F';
3409:
3410: l_from_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
3403: AND subject_id = p_from_id
3404: AND object_id = x_to_id
3405: AND RELATIONSHIP_CODE = 'HEADQUARTERS_OF'
3406: AND subject_table_name = 'HZ_PARTIES'
3407: AND object_table_name = 'HZ_PARTIES'
3408: AND directional_flag = 'F';
3409:
3410: l_from_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
3411: l_to_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
3406: AND subject_table_name = 'HZ_PARTIES'
3407: AND object_table_name = 'HZ_PARTIES'
3408: AND directional_flag = 'F';
3409:
3410: l_from_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
3411: l_to_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
3412: l_from_duns_number VARCHAR2(255);
3413: l_to_duns_number VARCHAR2(255);
3414: l_temp NUMBER;
3407: AND object_table_name = 'HZ_PARTIES'
3408: AND directional_flag = 'F';
3409:
3410: l_from_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
3411: l_to_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
3412: l_from_duns_number VARCHAR2(255);
3413: l_to_duns_number VARCHAR2(255);
3414: l_temp NUMBER;
3415:
3524: RETURN;
3525: END IF;
3526:
3527: SELECT object_version_number INTO l_obj_version_number
3528: FROM HZ_PARTIES
3529: WHERE party_id = x_to_id;
3530:
3531: l_organization_rec.party_rec.party_id := x_to_id;
3532: l_organization_rec.created_by_module := NULL;
3992: request_id = hz_utility_pub.request_id,
3993: program_application_id = hz_utility_pub.program_application_id,
3994: program_id = hz_utility_pub.program_id,
3995: program_update_date = sysdate
3996: WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
3997: AND OWNER_TABLE_ID =l_rel_to_party_id
3998: AND ORIG_SYSTEM = 'DNB'
3999: AND STATUS = 'A';
4000:
4034: request_id = hz_utility_pub.request_id,
4035: program_application_id = hz_utility_pub.program_application_id,
4036: program_id = hz_utility_pub.program_id,
4037: program_update_date = sysdate
4038: WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
4039: AND OWNER_TABLE_ID = FROM_REL.PARTY_ID
4040: AND ORIG_SYSTEM = 'DNB'
4041: AND STATUS = 'A';
4042: /* --Bug 4114254 UPDATE HZ_RELATIONSHIPS
4110:
4111: FOR FROM_CP IN (
4112: SELECT contact_point_id, phone_line_type, contact_point_type
4113: FROM HZ_CONTACT_POINTS
4114: WHERE owner_table_name = 'HZ_PARTIES'
4115: AND actual_content_source = 'DNB'
4116: AND nvl(status, 'A') = 'A'
4117: AND owner_table_id = p_from_id) LOOP
4118: case_new := 'FALSE';
4119: BEGIN
4120: BEGIN
4121: SELECT contact_point_id INTO l_to
4122: FROM HZ_CONTACT_POINTS
4123: WHERE owner_table_name = 'HZ_PARTIES'
4124: AND actual_content_source = 'DNB'
4125: AND nvl(phone_line_type,'X') = nvl(FROM_CP.phone_line_type,'X')--bug 5221273
4126: AND contact_point_type = FROM_CP.contact_point_type --bug 5221273
4127: AND nvl(status, 'A') = 'A'
4435: -- Handle DNB data in HZ_CODE_ASSIGNMENTS
4436: FOR FROM_CA IN (
4437: SELECT code_assignment_id, class_category, class_code
4438: FROM HZ_CODE_ASSIGNMENTS
4439: WHERE owner_table_name = 'HZ_PARTIES'
4440: AND content_source_type = 'DNB'
4441: AND nvl(status, 'A') = 'A'
4442: AND owner_table_id = p_from_id) LOOP
4443:
4443:
4444: BEGIN
4445: SELECT code_assignment_id INTO l_to
4446: FROM HZ_CODE_ASSIGNMENTS
4447: WHERE owner_table_name = 'HZ_PARTIES'
4448: AND content_source_type = 'DNB'
4449: AND nvl(status, 'A') = 'A'
4450: AND owner_table_id = x_to_id
4451: AND class_category = FROM_CA.class_category
4678:
4679: cursor org_c(l_dup_set_id in NUMBER)
4680: is
4681: select nvl(orgpf.internal_flag, 'N') internal_flag , hp.party_name
4682: from HZ_merge_parties dup, hz_parties hp, hz_organization_profiles orgpf
4683: where dup.batch_id = l_dup_set_id
4684: and (hp.party_id = dup.from_party_id or hp.party_id = dup.to_party_id)
4685: and hp.party_type = 'ORGANIZATION'
4686: and hp.party_id = orgpf.party_id(+)
4686: and hp.party_id = orgpf.party_id(+)
4687: and sysdate between orgpf.effective_start_date(+) and nvl(orgpf.effective_end_date(+),sysdate);
4688:
4689: /* select nvl(orgpf.internal_flag, 'N') internal_flag , hp.party_name
4690: from HZ_DUP_SET_PARTIES dup, hz_parties hp, hz_organization_profiles orgpf
4691: where dup.dup_set_id = l_dup_set_id
4692: and dup.dup_party_id = hp.party_id
4693: and hp.party_type = 'ORGANIZATION'
4694: and hp.party_id = orgpf.party_id(+)
4700:
4701: cursor person_c(l_dup_set_id in NUMBER)
4702: is
4703: select nvl(orgpf.internal_flag, 'N') internal_flag , hp.party_name
4704: from HZ_merge_parties dup, hz_parties hp, hz_person_profiles orgpf
4705: where dup.batch_id = l_dup_set_id
4706: and (hp.party_id = dup.from_party_id or hp.party_id = dup.to_party_id)
4707: and hp.party_type = 'PERSON'
4708: and hp.party_id = orgpf.party_id(+)
4708: and hp.party_id = orgpf.party_id(+)
4709: and sysdate between orgpf.effective_start_date(+) and nvl(orgpf.effective_end_date(+),sysdate);
4710:
4711: /* select nvl(orgpf.internal_flag, 'N') internal_flag , hp.party_name
4712: from HZ_DUP_SET_PARTIES dup, hz_parties hp, hz_person_profiles orgpf
4713: where dup.dup_set_id = l_dup_set_id
4714: and dup.dup_party_id = hp.party_id
4715: and hp.party_type = 'PERSON'
4716: and hp.party_id = orgpf.party_id(+)