[Home] [Help]
332: CURSOR c_batch_party_sites(cp_batch_party_id NUMBER) IS
333: SELECT merge_from_entity_id, merge_to_entity_id
334: FROM HZ_MERGE_PARTY_DETAILS
335: WHERE batch_party_id = cp_batch_party_id
336: AND ENTITY_NAME = 'HZ_PARTY_SITES'
337: AND merge_from_entity_id <> merge_to_entity_id;
338:
339: -----Cursor for the merge of Contact Points within the same party-----
340: CURSOR c_batch_contact_points(cp_batch_party_id NUMBER) IS
767:
768: ---For merging party sites/contact points/relationships within same party
769: ELSIF l_merge_type = 'SAME_PARTY_MERGE' THEN
770:
771: OPEN c_dict_id(l_rule_set_name,'HZ_PARTY_SITES');
772: FETCH c_dict_id INTO l_dict_id;
773: IF c_dict_id%NOTFOUND THEN
774: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
775: FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_PARTY_SITES');
771: OPEN c_dict_id(l_rule_set_name,'HZ_PARTY_SITES');
772: FETCH c_dict_id INTO l_dict_id;
773: IF c_dict_id%NOTFOUND THEN
774: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
775: FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_PARTY_SITES');
776: FND_MSG_PUB.ADD;
777: RAISE FND_API.G_EXC_ERROR;
778: END IF;
779: CLOSE c_dict_id;
792: g_num_sub_entities :=-1;
793:
794: -- Perform the party site merge within the same party
795: do_merge(p_batch_party_id =>l_batch_party_id,
796: p_entity_name =>'HZ_PARTY_SITES',
797: p_par_entity_name =>NULL,
798: p_from_id =>l_from_site_id,
799: p_to_id =>l_to_site_id,
800: p_par_from_id =>l_pre_merge_to_party_id, --5093366 passing party_id to do_merge for history
1351: OPEN c_dict_id(p_rule_set_name,p_entity_name);
1352: FETCH c_dict_id INTO l_dict_id, l_desc;
1353: IF c_dict_id%NOTFOUND THEN
1354: FND_MESSAGE.SET_NAME('AR', 'HZ_INVALID_DICT_ENTRY');
1355: FND_MESSAGE.SET_TOKEN('ENTITY' ,'HZ_PARTY_SITES');
1356: FND_MSG_PUB.ADD;
1357: RAISE FND_API.G_EXC_ERROR;
1358: END IF;
1359: CLOSE c_dict_id;
1530: l_to_rec_desc := get_record_desc(p_to_id,p_entity_name, l_pk_column_name,
1531: l_desc_column_name, x_return_status);
1532: IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1533:
1534: IF p_entity_name = 'HZ_PARTY_SITES' AND l_mand <> 'C' THEN
1535: BEGIN
1536: SELECT mandatory_merge INTO l_mand
1537: FROM hz_merge_party_details
1538: WHERE batch_party_id = p_batch_party_id
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
2232: l_entity_name = 'HZ_PARTY_USG_ASSIGNMENTS' --4307667
2843:
2844: CURSOR party_sites_for_pre_merge(cp_to_party_id IN NUMBER, cp_batch_id IN NUMBER) IS
2845: select merge_to_entity_id, p.from_party_id, p.batch_party_id
2846: from hz_merge_party_details d1, hz_merge_parties p
2847: where entity_name = 'HZ_PARTY_SITES'
2848: and p.batch_party_id = d1.batch_party_id
2849: and d1.batch_party_id IN ( select batch_party_id
2850: from hz_merge_parties
2851: where to_party_id = cp_to_party_id
2855: and exists ( --it should be a merge-to
2856: select 1 --for another mapping
2857: from hz_merge_party_details d2
2858: where d2.merge_to_entity_id = d1.merge_from_entity_id
2859: and d2.entity_name = 'HZ_PARTY_SITES'
2860: and batch_party_id IN ( select batch_party_id
2861: from hz_merge_parties
2862: where to_party_id = cp_to_party_id
2863: and to_party_id<>from_party_id
2898:
2899: l_batch_party_id HZ_MERGE_PARTIES.BATCH_PARTY_ID%TYPE;
2900: l_from_id HZ_MERGE_PARTIES.FROM_PARTY_ID%TYPE;
2901: l_merge_to_entity_id hz_merge_party_details.merge_to_entity_id%TYPE;
2902: l_new_party_site_id hz_party_sites.party_site_id%type;
2903: l_proc_name hz_merge_dictionary.procedure_name%type;
2904: l_subject_id HZ_RELATIONSHIPS.SUBJECT_ID%TYPE;
2905: l_object_id HZ_RELATIONSHIPS.OBJECT_ID%TYPE;
2906:
2934: pre_return_status := null;
2935: pre_log_padding := ' ';
2936:
2937:
2938: ----------Pre merge for HZ_PARTY_SITES ---------------
2939: OPEN party_sites_for_pre_merge(p_to_party_id , p_batch_id);
2940: LOOP
2941: l_return_status := FND_API.G_RET_STS_SUCCESS;
2942: FETCH party_sites_for_pre_merge INTO l_merge_to_entity_id, l_from_party_id, l_batch_party_id;
2951: l_from_rec_desc :=hz_merge_util.get_party_site_description(l_merge_to_entity_id);
2952:
2953: -- Execute the merge procedure for the entity
2954: exec_merge(
2955: p_entity_name => 'HZ_PARTY_SITES',
2956: p_proc_name => l_proc_name,
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,
2980: END IF;
2981:
2982: -- set the party sites status to A so that it is picked in merge that follows
2983: --the hz_merge_pkg.do_party_site_merge sets it to 'M' in pre-merge
2984: UPDATE HZ_PARTY_SITES
2985: SET
2986: STATUS = 'A',
2987: last_update_date = hz_utility_pub.last_update_date,
2988: last_updated_by = hz_utility_pub.user_id,
2998: set merge_to_entity_id = l_new_party_site_id
2999: where batch_party_id IN (select batch_party_id from hz_merge_parties
3000: where batch_id = p_batch_id)
3001: and merge_to_entity_id = l_merge_to_entity_id
3002: and entity_name = 'HZ_PARTY_SITES';
3003:
3004: UPDATE hz_merge_party_details
3005: set mandatory_merge = 'C'
3006: where batch_party_id IN (select batch_party_id from hz_merge_parties
3006: where batch_party_id IN (select batch_party_id from hz_merge_parties
3007: where batch_id = p_batch_id)
3008: and merge_from_entity_id = l_merge_to_entity_id
3009: and merge_to_entity_id = l_new_party_site_id
3010: and entity_name = 'HZ_PARTY_SITES';
3011:
3012: END IF;
3013: END LOOP;
3014:
3571:
3572:
3573:
3574: -- *******************************************
3575: -- Handle DNB data in HZ_PARTY_SITES/HZ_LOCATIONS
3576:
3577: -- Keep to's data.
3578: -- Inactivate the from's active party site.
3579: IF case2 OR (case3 and l_to_is_branch<>'Y') THEN
3576:
3577: -- Keep to's data.
3578: -- Inactivate the from's active party site.
3579: IF case2 OR (case3 and l_to_is_branch<>'Y') THEN
3580: UPDATE HZ_PARTY_SITES ps
3581: SET STATUS = 'I',
3582: end_date_active = trunc(SYSDATE-1),
3583: last_update_date = hz_utility_pub.last_update_date,
3584: last_updated_by = hz_utility_pub.user_id,
3593: END IF;
3594:
3595: FOR FROM_PS IN (
3596: SELECT party_site_id, ps.location_id, end_date_active
3597: FROM HZ_PARTY_SITES ps
3598: WHERE actual_content_source = 'DNB'
3599: AND ps.party_id = p_from_id) LOOP
3600:
3601:
3602: IF FROM_PS.end_date_active IS NULL THEN
3603: -- (Will only hold for case1 OR (case3 and l_to_is_branch = 'Y')
3604: BEGIN
3605: SELECT party_site_id, ps.location_id INTO l_to, l_to_loc_id
3606: FROM HZ_PARTY_SITES ps
3607: WHERE actual_content_source = 'DNB'
3608: AND ps.party_id = x_to_id
3609: AND end_date_active is null
3610: AND nvl(status,'A')='A';
3612: IF l_to_loc_id = FROM_PS.location_id THEN
3613: -- Merge to_ps to from_ps
3614: HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
3615: p_batch_party_id,
3616: 'HZ_PARTY_SITES',
3617: l_to,
3618: FROM_PS.party_site_id,
3619: 'T',
3620: hz_utility_pub.created_by,
3626: -- Do the exact comparison betn the from_dnb_ps and to_dnb ps.
3627: -- If exactly same: merge to into from
3628: BEGIN
3629: Select 'True' into case_new
3630: FROM HZ_PARTY_SITES ps,HZ_LOCATIONS l
3631: WHERE ps.party_site_id = FROM_PS.party_site_id
3632: AND ps.location_id = FROM_PS.location_id
3633: AND l.location_id = ps.location_id
3634: AND (ps.status IS NULL OR ps.status = 'A')
3661:
3662: IF case_new = 'True' THEN
3663: HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
3664: p_batch_party_id,
3665: 'HZ_PARTY_SITES',
3666: l_to,
3667: FROM_PS.party_site_id,
3668: 'T',
3669: hz_utility_pub.created_by,
3671: hz_utility_pub.last_update_login,
3672: hz_utility_pub.last_update_date,
3673: hz_utility_pub.last_updated_by);
3674: ELSE
3675: UPDATE HZ_PARTY_SITES
3676: SET actual_content_source = 'USER_ENTERED',
3677: last_update_date = hz_utility_pub.last_update_date,
3678: last_updated_by = hz_utility_pub.user_id,
3679: last_update_login = hz_utility_pub.last_update_login,
3692: request_id = hz_utility_pub.request_id,
3693: program_application_id = hz_utility_pub.program_application_id,
3694: program_id = hz_utility_pub.program_id,
3695: program_update_date = sysdate
3696: WHERE OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
3697: AND OWNER_TABLE_ID = l_to
3698: AND ORIG_SYSTEM = 'DNB'
3699: AND STATUS = 'A';
3700:
3701: END IF;
3702:
3703:
3704: -- Inactivate to_ps
3705: /*UPDATE HZ_PARTY_SITES
3706: SET status = 'I',
3707: end_date_active=trunc(SYSDATE-1),
3708: last_update_date = hz_utility_pub.last_update_date,
3709: last_updated_by = hz_utility_pub.user_id,
3724: ELSE
3725: BEGIN
3726: BEGIN
3727: SELECT party_site_id, ps.location_id,'True' INTO l_to, l_to_loc_id,case_new
3728: FROM HZ_PARTY_SITES ps
3729: WHERE actual_content_source = 'DNB'
3730: AND ps.party_id = x_to_id
3731: AND (ps.location_id = FROM_PS.location_id OR ('Y' = (Select 'Y'
3732: FROM HZ_PARTY_SITES ps1,HZ_LOCATIONS l
3728: FROM HZ_PARTY_SITES ps
3729: WHERE actual_content_source = 'DNB'
3730: AND ps.party_id = x_to_id
3731: AND (ps.location_id = FROM_PS.location_id OR ('Y' = (Select 'Y'
3732: FROM HZ_PARTY_SITES ps1,HZ_LOCATIONS l
3733: WHERE ps1.party_site_id = FROM_PS.party_site_id
3734: AND ps1.location_id = FROM_PS.location_id
3735: AND l.location_id = ps1.location_id
3736: --AND (ps1.status IS NULL OR ps1.status = 'A')
3766: IF case_new = 'True' THEN
3767: -- Merge from_ps with to_ps
3768: HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
3769: p_batch_party_id,
3770: 'HZ_PARTY_SITES',
3771: FROM_PS.party_site_id,
3772: l_to,
3773: 'Y',
3774: hz_utility_pub.created_by,
3777: hz_utility_pub.last_update_date,
3778: hz_utility_pub.last_updated_by);
3779: ELSE
3780: ---Activate the from party site(which we previously inactivated) and change the content source to USER_ENTERED.
3781: UPDATE HZ_PARTY_SITES
3782: SET status = 'A',
3783: end_date_active = NULL,
3784: last_update_date = hz_utility_pub.last_update_date,
3785: last_updated_by = hz_utility_pub.user_id,
3789: program_id = hz_utility_pub.program_id,
3790: program_update_date = sysdate
3791: WHERE request_id = hz_utility_pub.request_id;
3792:
3793: UPDATE HZ_PARTY_SITES
3794: SET actual_content_source = 'USER_ENTERED',
3795: last_update_date = hz_utility_pub.last_update_date,
3796: last_updated_by = hz_utility_pub.user_id,
3797: last_update_login = hz_utility_pub.last_update_login,
3811: request_id = hz_utility_pub.request_id,
3812: program_application_id = hz_utility_pub.program_application_id,
3813: program_id = hz_utility_pub.program_id,
3814: program_update_date = sysdate
3815: WHERE OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
3816: AND OWNER_TABLE_ID =FROM_PS.party_site_id
3817: AND ORIG_SYSTEM = 'DNB'
3818: AND STATUS = 'A';
3819: END IF;
4600:
4601: --Cursor for inserting Party sites that are non-DNB
4602: CURSOR c_from_ps_loc IS
4603: SELECT party_site_id, ps.location_id
4604: FROM HZ_PARTY_SITES ps
4605: WHERE ps.party_id = p_from_party_id
4606: AND ps.actual_content_source <> 'DNB'
4607: AND nvl(status, 'A') = 'A';
4608:
4607: AND nvl(status, 'A') = 'A';
4608:
4609: CURSOR c_dup_to_ps(cp_loc_id NUMBER) IS
4610: SELECT party_site_id
4611: FROM HZ_PARTY_SITES ps
4612: WHERE ps.party_id = p_to_party_id
4613: AND ps.location_id = cp_loc_id
4614: AND ps.actual_content_source <> 'DNB'
4615: AND nvl(status, 'A') = 'A';
4631:
4632: IF c_dup_to_ps%FOUND THEN
4633: HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
4634: p_batch_party_id,
4635: 'HZ_PARTY_SITES',
4636: l_ps_id,
4637: l_dup_ps_id,
4638: 'Y',
4639: hz_utility_pub.created_by,
4643: hz_utility_pub.last_updated_by);
4644: ELSE
4645: HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
4646: p_batch_party_id,
4647: 'HZ_PARTY_SITES',
4648: l_ps_id,
4649: l_ps_id,
4650: 'Y',
4651: hz_utility_pub.created_by,