363: CURSOR c_batch_contact_points(cp_batch_party_id NUMBER) IS
364: SELECT merge_from_entity_id, merge_to_entity_id
365: FROM HZ_MERGE_PARTY_DETAILS
366: WHERE batch_party_id = cp_batch_party_id
367: AND ENTITY_NAME = 'HZ_CONTACT_POINTS'
368: AND merge_from_entity_id <> merge_to_entity_id;
369:
370: -----Cursor for the merge of Relationships/Contacts within the same party-----
371: CURSOR c_batch_relationships(cp_batch_party_id NUMBER) IS
446:
447: l_from_site_id HZ_MERGE_PARTIES.FROM_PARTY_ID%TYPE;
448: l_to_site_id HZ_MERGE_PARTIES.TO_PARTY_ID%TYPE;
449:
450: l_from_cp_id HZ_CONTACT_POINTS.CONTACT_POINT_ID%TYPE;
451: l_to_cp_id HZ_CONTACT_POINTS.CONTACT_POINT_ID%TYPE;
452:
453: l_from_rel_id HZ_RELATIONSHIPS.RELATIONSHIP_ID%TYPE;
454: l_to_rel_id HZ_RELATIONSHIPS.RELATIONSHIP_ID%TYPE;
447: l_from_site_id HZ_MERGE_PARTIES.FROM_PARTY_ID%TYPE;
448: l_to_site_id HZ_MERGE_PARTIES.TO_PARTY_ID%TYPE;
449:
450: l_from_cp_id HZ_CONTACT_POINTS.CONTACT_POINT_ID%TYPE;
451: l_to_cp_id HZ_CONTACT_POINTS.CONTACT_POINT_ID%TYPE;
452:
453: l_from_rel_id HZ_RELATIONSHIPS.RELATIONSHIP_ID%TYPE;
454: l_to_rel_id HZ_RELATIONSHIPS.RELATIONSHIP_ID%TYPE;
455:
851: CLOSE c_batch_party_sites;
852:
853: --if the prev. merge was success only then do the next step
854: if l_return_status = FND_API.G_RET_STS_SUCCESS then
855: OPEN c_dict_id(l_rule_set_name,'HZ_CONTACT_POINTS');
856: FETCH c_dict_id INTO l_dict_id;
857: IF c_dict_id%NOTFOUND THEN
858: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
859: FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_CONTACT_POINTS');
855: OPEN c_dict_id(l_rule_set_name,'HZ_CONTACT_POINTS');
856: FETCH c_dict_id INTO l_dict_id;
857: IF c_dict_id%NOTFOUND THEN
858: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
859: FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_CONTACT_POINTS');
860: FND_MSG_PUB.ADD;
861: RAISE FND_API.G_EXC_ERROR;
862: END IF;
863: CLOSE c_dict_id;
878:
879: -- Perform the Contact Points merge
880: do_merge(
881: p_batch_party_id =>l_batch_party_id,
882: p_entity_name =>'HZ_CONTACT_POINTS',
883: p_par_entity_name =>NULL,
884: p_from_id =>l_from_cp_id,
885: p_to_id =>l_to_cp_id,
886: p_par_from_id =>l_pre_merge_to_party_id, --5093366 passing party_id to do_merge for history
3702:
3703: END LOOP;
3704:
3705: -- *******************************************
3706: -- Handle DNB data in HZ_CONTACT_POINTS
3707:
3708:
3709: FOR FROM_CP IN (
3710: SELECT contact_point_id, phone_line_type, contact_point_type
3707:
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
3736: -- Merge to into from
3737: --If Exactly same then Merge
3738:
3739: BEGIN
3740: Select 'True' INTO case_new FROM HZ_CONTACT_POINTS
3741: WHERE contact_point_id = FROM_CP.contact_point_id
3742: AND (CONTACT_POINT_TYPE ||
3743: STATUS ||
3744: EDI_TRANSACTION_HANDLING ||
3791: PHONE_EXTENSION ||
3792: PHONE_LINE_TYPE ||
3793: TELEX_NUMBER ||
3794: WEB_TYPE
3795: FROM HZ_CONTACT_POINTS
3796: WHERE contact_point_id = l_to)
3797: AND nvl(EMAIL_ADDRESS,'NOEMAIL') = (
3798: SELECT nvl(EMAIL_ADDRESS,'NOEMAIL')
3799: FROM HZ_CONTACT_POINTS
3795: FROM HZ_CONTACT_POINTS
3796: WHERE contact_point_id = l_to)
3797: AND nvl(EMAIL_ADDRESS,'NOEMAIL') = (
3798: SELECT nvl(EMAIL_ADDRESS,'NOEMAIL')
3799: FROM HZ_CONTACT_POINTS
3800: WHERE contact_point_id = l_to)
3801: AND nvl(URL, 'NOURL') = (
3802: SELECT nvl(URL, 'NOURL')
3803: FROM HZ_CONTACT_POINTS
3799: FROM HZ_CONTACT_POINTS
3800: WHERE contact_point_id = l_to)
3801: AND nvl(URL, 'NOURL') = (
3802: SELECT nvl(URL, 'NOURL')
3803: FROM HZ_CONTACT_POINTS
3804: WHERE contact_point_id = l_to);
3805:
3806: EXCEPTION
3807: WHEN NO_DATA_FOUND THEN
3811:
3812: IF case_new = 'True' THEN
3813: HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
3814: p_batch_party_id,
3815: 'HZ_CONTACT_POINTS',
3816: l_to,
3817: FROM_CP.contact_point_id,
3818: 'T',
3819: hz_utility_pub.created_by,
3821: hz_utility_pub.last_update_login,
3822: hz_utility_pub.last_update_date,
3823: hz_utility_pub.last_updated_by);
3824: ELSE
3825: UPDATE HZ_CONTACT_POINTS
3826: SET actual_content_source = 'USER_ENTERED',
3827: last_update_date =hz_utility_pub.last_update_date,
3828: last_updated_by = hz_utility_pub.last_updated_by,
3829: last_update_login=hz_utility_pub.last_update_login,
3842: request_id = hz_utility_pub.request_id,
3843: program_application_id = hz_utility_pub.program_application_id,
3844: program_id = hz_utility_pub.program_id,
3845: program_update_date = sysdate
3846: WHERE OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
3847: AND OWNER_TABLE_ID = l_to
3848: AND ORIG_SYSTEM = 'DNB'
3849: AND STATUS = 'A';
3850: END IF;
3852: -- Merge from into to
3853: --Perform Exact Dup check
3854:
3855: BEGIN
3856: Select 'True' INTO case_new FROM HZ_CONTACT_POINTS
3857: WHERE contact_point_id = FROM_CP.contact_point_id
3858: AND
3859: CONTACT_POINT_TYPE ||
3860: STATUS ||
3908: PHONE_EXTENSION ||
3909: PHONE_LINE_TYPE ||
3910: TELEX_NUMBER ||
3911: WEB_TYPE
3912: FROM HZ_CONTACT_POINTS
3913: WHERE contact_point_id = l_to)
3914: AND nvl(EMAIL_ADDRESS,'NOEMAIL') = (
3915: SELECT nvl(EMAIL_ADDRESS,'NOEMAIL')
3916: FROM HZ_CONTACT_POINTS
3912: FROM HZ_CONTACT_POINTS
3913: WHERE contact_point_id = l_to)
3914: AND nvl(EMAIL_ADDRESS,'NOEMAIL') = (
3915: SELECT nvl(EMAIL_ADDRESS,'NOEMAIL')
3916: FROM HZ_CONTACT_POINTS
3917: WHERE contact_point_id = l_to)
3918: AND nvl(URL, 'NOURL') = (
3919: SELECT nvl(URL, 'NOURL')
3920: FROM HZ_CONTACT_POINTS
3916: FROM HZ_CONTACT_POINTS
3917: WHERE contact_point_id = l_to)
3918: AND nvl(URL, 'NOURL') = (
3919: SELECT nvl(URL, 'NOURL')
3920: FROM HZ_CONTACT_POINTS
3921: WHERE contact_point_id = l_to);
3922:
3923: EXCEPTION
3924: WHEN No_Data_Found THEN
3928:
3929: IF case_new = 'True' THEN --Populate HZ_MERGE_PARTY_DETAILS
3930: HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
3931: p_batch_party_id,
3932: 'HZ_CONTACT_POINTS',
3933: FROM_CP.contact_point_id,
3934: l_to,
3935: 'Y',
3936: hz_utility_pub.created_by,
3938: hz_utility_pub.last_update_login,
3939: hz_utility_pub.last_update_date,
3940: hz_utility_pub.last_updated_by);
3941: ELSE
3942: UPDATE HZ_CONTACT_POINTS
3943: SET actual_content_source = 'USER_ENTERED',
3944: last_update_date =hz_utility_pub.last_update_date,
3945: last_updated_by = hz_utility_pub.last_updated_by,
3946: last_update_login=hz_utility_pub.last_update_login,
3959: request_id = hz_utility_pub.request_id,
3960: program_application_id = hz_utility_pub.program_application_id,
3961: program_id = hz_utility_pub.program_id,
3962: program_update_date = sysdate
3963: WHERE OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
3964: AND OWNER_TABLE_ID =FROM_CP.contact_point_id
3965: AND ORIG_SYSTEM = 'DNB'
3966: AND STATUS = 'A';
3967: END IF;