[Home] [Help]
312:
313: -----Cursor for grouping parties based on distinct merge_to party-----
314: CURSOR c_pre_merge(cp_batch_id NUMBER) IS
315: SELECT DISTINCT(to_party_id)
316: FROM HZ_MERGE_PARTIES mp, hz_parties p
317: WHERE batch_id = cp_batch_id
318: AND p.party_id = mp.from_party_id
319: AND p.party_type <> 'PARTY_RELATIONSHIP';
320:
319: AND p.party_type <> 'PARTY_RELATIONSHIP';
320:
321: CURSOR c_pre_merge_type(cp_batch_id NUMBER, cp_to_party_id NUMBER) IS
322: SELECT mp.merge_type
323: FROM HZ_MERGE_PARTIES mp, hz_parties p
324: WHERE mp.batch_id = cp_batch_id
325: AND mp.to_party_id = cp_to_party_id
326: AND p.party_id = mp.to_party_id
327: AND p.party_type <> 'PARTY_RELATIONSHIP'
331: -----Cursor to get merge from-merge to pair parties and the type-----
332: CURSOR c_batch_details(cp_batch_id NUMBER,cp_to_party_id IN NUMBER) IS
333: SELECT batch_party_id, merge_type, from_party_id, to_party_id, merge_status,party_type,
334: decode(op.actual_content_source, 'DNB', 'DNB', NULL), merge_reason_code
335: FROM HZ_MERGE_PARTIES mp, HZ_PARTIES pt, hz_organization_profiles op
336: WHERE batch_id = cp_batch_id
337: AND pt.party_id = mp.from_party_id
338: AND op.party_id(+) = pt.party_id
339: AND op.actual_content_source(+) = 'DNB'
762: IF l_merge_type = 'PARTY_MERGE' THEN
763: outandlog('.... Merging Parties: From Party ID='||l_from_party_id ||
764: ',To Party ID='||l_to_party_id);
765:
766: -- Fetch the dictionary id for the HZ_PARTIES entity
767: OPEN c_dict_id(l_rule_set_name,'HZ_PARTIES');
768: FETCH c_dict_id INTO l_dict_id;
769: IF c_dict_id%NOTFOUND THEN
770: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
763: outandlog('.... Merging Parties: From Party ID='||l_from_party_id ||
764: ',To Party ID='||l_to_party_id);
765:
766: -- Fetch the dictionary id for the HZ_PARTIES entity
767: OPEN c_dict_id(l_rule_set_name,'HZ_PARTIES');
768: FETCH c_dict_id INTO l_dict_id;
769: IF c_dict_id%NOTFOUND THEN
770: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
771: FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_PARTIES');
767: OPEN c_dict_id(l_rule_set_name,'HZ_PARTIES');
768: FETCH c_dict_id INTO l_dict_id;
769: IF c_dict_id%NOTFOUND THEN
770: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
771: FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_PARTIES');
772: FND_MSG_PUB.ADD;
773: RAISE FND_API.G_EXC_ERROR;
774: END IF;
775: CLOSE c_dict_id;
786: END IF;
787:
788: -- Call the recursive merge procedure performing this merge
789: do_merge(p_batch_party_id =>l_batch_party_id,
790: p_entity_name =>'HZ_PARTIES',
791: p_par_entity_name =>NULL,
792: p_from_id =>l_from_party_id,
793: p_to_id =>l_to_party_id,
794: p_par_from_id =>NULL,
1059:
1060: SELECT decode(party_type,'PERSON','HZ_PERSON_PROFILES',
1061: 'ORGANIZATION','HZ_ORGANIZATION_PROFILES',
1062: 'HZ_ORGANIZATION_PROFILES') INTO l_party_type
1063: FROM HZ_PARTIES
1064: WHERE party_id=l_pre_merge_to_party_id;
1065:
1066:
1067: HZ_MERGE_ENTITY_ATTRI_PVT.do_profile_attribute_merge(
1764: l_subrec_str := l_subrec_str || ' AND ' || l_join_clause;
1765: END IF;
1766: --4500011
1767: IF (l_sub_entity_name = 'HZ_PARTY_RELATIONSHIPS') THEN
1768: l_subrec_str := l_subrec_str || ' AND ' || ' subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES'' AND directional_flag = ''F''';
1769: END IF;
1770:
1771: BEGIN
1772: OPEN c_sub_records FOR l_subrec_str using p_from_id;
2263: FETCH c_deleted_records INTO l_record_id, l_entity_name, l_pkcol;
2264:
2265: EXIT WHEN c_deleted_records%NOTFOUND;
2266:
2267: IF l_entity_name = 'HZ_PARTIES' OR
2268: l_entity_name = 'HZ_PARTY_SITES' OR
2269: l_entity_name = 'HZ_ORGANIZATION_PROFILES' OR
2270: l_entity_name = 'HZ_PERSON_PROFILES' OR
2271: l_entity_name = 'HZ_ORG_CONTACTS' OR
2276: l_pkcol || ' = :pk';
2277: ELSIF l_entity_name = 'HZ_PARTY_RELATIONSHIPS' THEN --4500011
2278: rec_delete := 'UPDATE HZ_RELATIONSHIPS' ||
2279: ' SET STATUS = ''D'' WHERE ' ||
2280: 'RELATIONSHIP_ID' || ' = :pk'||'AND subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES'' AND directional_flag = ''F''';
2281: ELSE
2282: -- Construct dynamic SQL query to fetch description
2283: rec_delete := 'DELETE FROM ' || l_entity_name ||
2284: ' WHERE ' || l_pkcol || ' = :pk';
2320:
2321: IF (p_entity_name = 'HZ_PARTY_RELATIONSHIPS') THEN
2322: rec_query := 'SELECT hz_merge_util.get_party_reln_description(relationship_id) FROM HZ_RELATIONSHIPS'
2323: || ' WHERE RELATIONSHIP_ID = '|| ':pk'
2324: || ' AND subject_table_name = ''HZ_PARTIES'' AND object_table_name = ''HZ_PARTIES'' AND directional_flag = ''F''';
2325:
2326: ELSE
2327: rec_query := 'SELECT ' || p_desc_col_name ||
2328: ' FROM ' || p_entity_name || ' WHERE ' ||
2997: p_from_id => l_merge_to_entity_id,
2998: x_to_id => l_new_party_site_id,
2999: p_par_from_id => l_from_party_id,
3000: p_par_to_id => p_to_party_id,
3001: p_parent_entity => 'HZ_PARTIES',
3002: p_batch_id => p_batch_id,
3003: p_batch_party_id=> l_batch_party_id,
3004: x_return_status => l_return_status);
3005:
3071: select subject_id , object_id
3072: into l_subject_id , l_object_id
3073: from HZ_RELATIONSHIPS --4500011
3074: where relationship_id = l_merge_to_entity_id
3075: and subject_table_name = 'HZ_PARTIES'
3076: and object_table_name = 'HZ_PARTIES'
3077: and directional_flag = 'F';
3078:
3079: if l_subject_id = l_from_party_id then
3072: into l_subject_id , l_object_id
3073: from HZ_RELATIONSHIPS --4500011
3074: where relationship_id = l_merge_to_entity_id
3075: and subject_table_name = 'HZ_PARTIES'
3076: and object_table_name = 'HZ_PARTIES'
3077: and directional_flag = 'F';
3078:
3079: if l_subject_id = l_from_party_id then
3080: l_proc_name := 'HZ_MERGE_PKG.party_reln_subject_merge';
3099: p_from_id => l_merge_to_entity_id,
3100: x_to_id => l_merge_to_entity_id,
3101: p_par_from_id => l_from_party_id,
3102: p_par_to_id => p_to_party_id,
3103: p_parent_entity => 'HZ_PARTIES',
3104: p_batch_id => p_batch_id,
3105: p_batch_party_id=> l_batch_party_id,
3106: x_return_status => l_return_status);
3107:
3416: ) IS
3417:
3418: CURSOR c_party_type(cp_party_id NUMBER) IS
3419: SELECT party_type
3420: FROM HZ_PARTIES
3421: WHERE party_id = cp_party_id;
3422:
3423: CURSOR c_duns IS
3424: SELECT duns_number_c, last_update_date, organization_profile_id,actual_content_source
3442: WHERE content_source_type = 'DNB'
3443: AND subject_id = p_from_id
3444: AND object_id = x_to_id
3445: AND RELATIONSHIP_CODE = 'HEADQUARTERS_OF'
3446: AND subject_table_name = 'HZ_PARTIES'
3447: AND object_table_name = 'HZ_PARTIES'
3448: AND directional_flag = 'F';
3449:
3450: l_from_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
3443: AND subject_id = p_from_id
3444: AND object_id = x_to_id
3445: AND RELATIONSHIP_CODE = 'HEADQUARTERS_OF'
3446: AND subject_table_name = 'HZ_PARTIES'
3447: AND object_table_name = 'HZ_PARTIES'
3448: AND directional_flag = 'F';
3449:
3450: l_from_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
3451: l_to_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
3446: AND subject_table_name = 'HZ_PARTIES'
3447: AND object_table_name = 'HZ_PARTIES'
3448: AND directional_flag = 'F';
3449:
3450: l_from_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
3451: l_to_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
3452: l_from_duns_number VARCHAR2(255);
3453: l_to_duns_number VARCHAR2(255);
3454: l_temp NUMBER;
3447: AND object_table_name = 'HZ_PARTIES'
3448: AND directional_flag = 'F';
3449:
3450: l_from_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
3451: l_to_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
3452: l_from_duns_number VARCHAR2(255);
3453: l_to_duns_number VARCHAR2(255);
3454: l_temp NUMBER;
3455:
3547: RETURN;
3548: END IF;
3549:
3550: SELECT object_version_number INTO l_obj_version_number
3551: FROM HZ_PARTIES
3552: WHERE party_id = x_to_id;
3553:
3554: l_organization_rec.party_rec.party_id := x_to_id;
3555: l_organization_rec.created_by_module := NULL;
3647: request_id = hz_utility_pub.request_id,
3648: program_application_id = hz_utility_pub.program_application_id,
3649: program_id = hz_utility_pub.program_id,
3650: program_update_date = sysdate
3651: WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
3652: AND OWNER_TABLE_ID = FROM_REL.PARTY_ID
3653: AND ORIG_SYSTEM = 'DNB'
3654: AND STATUS = 'A';
3655:
3652: AND OWNER_TABLE_ID = FROM_REL.PARTY_ID
3653: AND ORIG_SYSTEM = 'DNB'
3654: AND STATUS = 'A';
3655:
3656: UPDATE HZ_PARTIES
3657: SET status = 'M',
3658: last_update_date = hz_utility_pub.last_update_date,
3659: last_updated_by = hz_utility_pub.user_id,
3660: last_update_login = hz_utility_pub.last_update_login,
3672:
3673: IF FROM_REL.direction_code = 'P' THEN
3674: -- assign the subject to parent for hierarchy
3675: l_hierarchy_rec.parent_id := FROM_REL.subject_id;
3676: l_hierarchy_rec.parent_table_name := 'HZ_PARTIES';
3677: l_hierarchy_rec.parent_object_type := 'ORGANIZATION';
3678: l_hierarchy_rec.child_id := p_from_id;
3679: l_hierarchy_rec.child_table_name := 'HZ_PARTIES';
3680: l_hierarchy_rec.child_object_type := 'ORGANIZATION';
3675: l_hierarchy_rec.parent_id := FROM_REL.subject_id;
3676: l_hierarchy_rec.parent_table_name := 'HZ_PARTIES';
3677: l_hierarchy_rec.parent_object_type := 'ORGANIZATION';
3678: l_hierarchy_rec.child_id := p_from_id;
3679: l_hierarchy_rec.child_table_name := 'HZ_PARTIES';
3680: l_hierarchy_rec.child_object_type := 'ORGANIZATION';
3681: ELSIF FROM_REL.direction_code = 'C' THEN
3682: -- assign the object to parent
3683: l_hierarchy_rec.parent_id := p_from_id;
3683: l_hierarchy_rec.parent_id := p_from_id;
3684: l_hierarchy_rec.parent_table_name := 'HZ_PARTES';
3685: l_hierarchy_rec.parent_object_type := 'ORGANIZATION';
3686: l_hierarchy_rec.child_id := FROM_REL.subject_id;
3687: l_hierarchy_rec.child_table_name := 'HZ_PARTIES';
3688: l_hierarchy_rec.child_object_type := 'ORGANIZATION';
3689:
3690: END IF;
3691:
3708:
3709: FOR FROM_CP IN (
3710: SELECT contact_point_id, phone_line_type, contact_point_type
3711: FROM HZ_CONTACT_POINTS
3712: WHERE owner_table_name = 'HZ_PARTIES'
3713: AND actual_content_source = 'DNB'
3714: AND nvl(status, 'A') = 'A'
3715: AND owner_table_id = p_from_id) LOOP
3716: case_new := 'FALSE';
3717: BEGIN
3718: BEGIN
3719: SELECT contact_point_id INTO l_to
3720: FROM HZ_CONTACT_POINTS
3721: WHERE owner_table_name = 'HZ_PARTIES'
3722: AND actual_content_source = 'DNB'
3723: AND nvl(phone_line_type,'X') = nvl(FROM_CP.phone_line_type,'X')--bug 5221273
3724: AND contact_point_type = FROM_CP.contact_point_type --bug 5221273
3725: AND nvl(status, 'A') = 'A'
4033: -- Handle DNB data in HZ_CODE_ASSIGNMENTS
4034: FOR FROM_CA IN (
4035: SELECT code_assignment_id, class_category, class_code
4036: FROM HZ_CODE_ASSIGNMENTS
4037: WHERE owner_table_name = 'HZ_PARTIES'
4038: AND content_source_type = 'DNB'
4039: AND nvl(status, 'A') = 'A'
4040: AND owner_table_id = p_from_id) LOOP
4041:
4041:
4042: BEGIN
4043: SELECT code_assignment_id INTO l_to
4044: FROM HZ_CODE_ASSIGNMENTS
4045: WHERE owner_table_name = 'HZ_PARTIES'
4046: AND content_source_type = 'DNB'
4047: AND nvl(status, 'A') = 'A'
4048: AND owner_table_id = x_to_id
4049: AND class_category = FROM_CA.class_category
4276:
4277: cursor org_c(l_dup_set_id in NUMBER)
4278: is
4279: select nvl(orgpf.internal_flag, 'N') internal_flag , hp.party_name
4280: from HZ_merge_parties dup, hz_parties hp, hz_organization_profiles orgpf
4281: where dup.batch_id = l_dup_set_id
4282: and (hp.party_id = dup.from_party_id or hp.party_id = dup.to_party_id)
4283: and hp.party_type = 'ORGANIZATION'
4284: and hp.party_id = orgpf.party_id(+)
4284: and hp.party_id = orgpf.party_id(+)
4285: and sysdate between orgpf.effective_start_date(+) and nvl(orgpf.effective_end_date(+),sysdate);
4286:
4287: /* select nvl(orgpf.internal_flag, 'N') internal_flag , hp.party_name
4288: from HZ_DUP_SET_PARTIES dup, hz_parties hp, hz_organization_profiles orgpf
4289: where dup.dup_set_id = l_dup_set_id
4290: and dup.dup_party_id = hp.party_id
4291: and hp.party_type = 'ORGANIZATION'
4292: and hp.party_id = orgpf.party_id(+)
4298:
4299: cursor person_c(l_dup_set_id in NUMBER)
4300: is
4301: select nvl(orgpf.internal_flag, 'N') internal_flag , hp.party_name
4302: from HZ_merge_parties dup, hz_parties hp, hz_person_profiles orgpf
4303: where dup.batch_id = l_dup_set_id
4304: and (hp.party_id = dup.from_party_id or hp.party_id = dup.to_party_id)
4305: and hp.party_type = 'PERSON'
4306: and hp.party_id = orgpf.party_id(+)
4306: and hp.party_id = orgpf.party_id(+)
4307: and sysdate between orgpf.effective_start_date(+) and nvl(orgpf.effective_end_date(+),sysdate);
4308:
4309: /* select nvl(orgpf.internal_flag, 'N') internal_flag , hp.party_name
4310: from HZ_DUP_SET_PARTIES dup, hz_parties hp, hz_person_profiles orgpf
4311: where dup.dup_set_id = l_dup_set_id
4312: and dup.dup_party_id = hp.party_id
4313: and hp.party_type = 'PERSON'
4314: and hp.party_id = orgpf.party_id(+)