[Home] [Help]
23: l_api_version_number CONSTANT NUMBER := 2.0;
24: l_merge_reason_code VARCHAR2(30);
25:
26: CURSOR c_get_pm_access_id (c_from_fk_id NUMBER, c_to_fk_id NUMBER) IS
27: select access_id from as_accesses_all a
28: where customer_id= c_from_fk_id
29: and exists (select null from as_accesses_all b
30: where customer_id =c_to_fk_id
31: and (b.address_id = a.address_id or
25:
26: CURSOR c_get_pm_access_id (c_from_fk_id NUMBER, c_to_fk_id NUMBER) IS
27: select access_id from as_accesses_all a
28: where customer_id= c_from_fk_id
29: and exists (select null from as_accesses_all b
30: where customer_id =c_to_fk_id
31: and (b.address_id = a.address_id or
32: (b.address_id is null and a.address_id is null))
33: and (b.lead_id = a.lead_id or
42: (b.sales_group_id is null and a.sales_group_id is null)));
43:
44: CURSOR c_get_psm_access_id (c_from_fk_id NUMBER, c_to_fk_id NUMBER) IS
45: select access_id
46: from as_accesses_all b
47: where b.address_id = c_from_fk_id
48: and b.customer_id in
49: (select customer_id
50: from as_accesses_all c
46: from as_accesses_all b
47: where b.address_id = c_from_fk_id
48: and b.customer_id in
49: (select customer_id
50: from as_accesses_all c
51: where (c.address_id = c_to_fk_id or
52: (b.address_id is null and c.address_id is null))
53: and (b.lead_id = c.lead_id or
54: (b.lead_id is null and c.lead_id is null))
107:
108: -- ************************************************************************
109: -- Add your own logic if you need to take care of the following cases
110: -- Check the if record duplicate if change party_id from merge-from
111: -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
112: -- situation
113: --
114: -- customer_id address_id contact_id
115: -- =========== ========== ==========
132: 'Deleting PARTY access_id: '||I.access_id);
133: DELETE FROM as_territory_accesses
134: WHERE access_id = I.access_id;
135:
136: DELETE FROM as_accesses_all
137: WHERE access_id = I.access_id;
138: END LOOP;
139:
140: -- merge party
137: WHERE access_id = I.access_id;
138: END LOOP;
139:
140: -- merge party
141: UPDATE AS_ACCESSES_ALL
142: set object_version_number = nvl(object_version_number,0) + 1,
143: customer_id = p_to_fk_id,
144: last_update_date = hz_utility_pub.last_update_date,
145: last_updated_by = hz_utility_pub.user_id,
150: where customer_id = p_from_fk_id;
151:
152: -- ffang 012204, bug 3395366, party merge for partners
153: -- party merge for partner_customer_id
154: UPDATE AS_ACCESSES_ALL
155: set partner_customer_id = p_to_fk_id,
156: salesforce_id=( select resource_id
157: from jtf_rs_resource_extns
158: where category = 'PARTNER'
165: program_update_date = sysdate
166: where partner_customer_id = p_from_fk_id;
167:
168: -- party merge for partner_cont_party_id
169: UPDATE AS_ACCESSES_ALL
170: set partner_cont_party_id = p_to_fk_id,
171: last_update_date = hz_utility_pub.last_update_date,
172: last_updated_by = hz_utility_pub.user_id,
173: last_update_login = hz_utility_pub.last_update_login,
185: 'Deleting PARTY SITE access_id: '||I.access_id);
186: DELETE FROM as_territory_accesses
187: WHERE access_id = I.access_id;
188:
189: DELETE FROM as_accesses_all
190: WHERE access_id = I.access_id;
191: END LOOP;
192:
193: -- The following statement will not update records if parties are merged
194: -- before party sites. As per TCA design docs, party sites are always
195: -- merged before parties. The subquery is to prevent updating new customer.
196:
197: -- merge party site
198: UPDATE AS_ACCESSES_ALL
199: set object_version_number = nvl(object_version_number,0) + 1,
200: address_id = p_to_fk_id,
201: last_update_date = hz_utility_pub.last_update_date,
202: last_updated_by = hz_utility_pub.user_id,
207: where address_id = p_from_fk_id;
208:
209: -- ffang 012204, bug 3395366, party merge for partners
210: -- party merge for partner_address_id
211: UPDATE AS_ACCESSES_ALL
212: set partner_address_id = p_to_fk_id,
213: last_update_date = hz_utility_pub.last_update_date,
214: last_updated_by = hz_utility_pub.user_id,
215: last_update_login = hz_utility_pub.last_update_login,
287:
288: -- ***************************************************************************
289: -- Add your own logic if you need to take care of the following cases
290: -- Check the if record duplicate if change party_id from merge-from
291: -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
292: -- situation
293: --
294: -- customer_id address_id contact_id
295: -- =========== ========== ==========