1 PACKAGE BODY AS_TAP_MERGE_PKG as
2 /* $Header: asxpmteb.pls 120.1 2006/05/24 06:14:18 subabu noship $ */
3 --
4 -- NAME
5 -- AS_TAP_MERGE_PKG
6 --
7 -- HISTORY
8 --
9 G_PKG_NAME CONSTANT VARCHAR2(30):='AS_TAP_MERGE_PKG';
10
11 PROCEDURE ACCESS_MERGE
12 ( p_entity_name IN VARCHAR2
13 ,p_from_id IN NUMBER
14 ,p_to_id IN OUT NOCOPY NUMBER
15 ,p_from_fk_id IN NUMBER
16 ,p_to_fk_id IN NUMBER
17 ,p_parent_entity_name IN VARCHAR2
18 ,p_batch_id IN NUMBER
19 ,p_batch_party_id IN NUMBER
20 ,x_return_status IN OUT NOCOPY VARCHAR2)
21 IS
22 l_api_name CONSTANT VARCHAR2(30) := 'ACCESS_MERGE';
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
32 (b.address_id is null and a.address_id is null))
33 and (b.lead_id = a.lead_id or
34 (b.lead_id is null and a.lead_id is null))
35 and (b.org_id =a.org_id or
36 (b.org_id is null and a.org_id is null))
37 and (b.salesforce_id =a.salesforce_id or
38 (b.salesforce_id is null and a.salesforce_id is null))
39 and (b.sales_lead_id =a.sales_lead_id or
40 (b.sales_lead_id is null and a.sales_lead_id is null))
41 and (b.sales_group_id = a.sales_group_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
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))
55 and (b.org_id =c.org_id or
56 (b.org_id is null and c.org_id is null))
57 and (b.salesforce_id =c.salesforce_id or
58 (b.salesforce_id is null and c.salesforce_id is null))
59 and (b.sales_lead_id =c.sales_lead_id or
60 (b.sales_lead_id is null and c.sales_lead_id is null))
61 and (b.sales_group_id = c.sales_group_id or
62 (b.sales_group_id is null and c.sales_group_id is null)));
63
64 BEGIN
65 FND_FILE.PUT_LINE(FND_FILE.LOG,'AS_TAP_MERGE_PKG.ACCESS_MERGE start : '
66 ||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
67 FND_FILE.PUT_LINE(FND_FILE.LOG,'Entity: '||p_parent_entity_name);
68 FND_FILE.PUT_LINE(FND_FILE.LOG,'from_fk: '||p_from_fk_id);
69 FND_FILE.PUT_LINE(FND_FILE.LOG,'to_fk: '||p_to_fk_id);
70
71 x_return_status := FND_API.G_RET_STS_SUCCESS;
72
73 select merge_reason_code into l_merge_reason_code
74 from HZ_MERGE_BATCH
75 where batch_id = p_batch_id;
76
77 IF l_merge_reason_code = 'DUPLICATE' THEN
78 -- *********************************************************************
79 -- if reason code is duplicate then allow the party merge to happen
80 -- without any validations.
81 -- *********************************************************************
82 null;
83 ELSE
84 -- *********************************************************************
85 -- if there are any validations to be done, include it in this section
86 -- *********************************************************************
87 null;
88 END IF;
89
90 -- ************************************************************************
91 -- If the parent has NOT changed (ie. Parent getting transferred) then
92 -- nothing needs to be done. Set Merged To Id is same as Merged From Id
93 -- and return
94 -- ************************************************************************
95 if p_from_fk_id = p_to_fk_id then
96 p_to_id := p_from_id;
97 return;
98 end if;
99
100 -- ************************************************************************
101 -- If the parent has changed(ie. Parent is getting merged) then transfer
102 -- the dependent record to the new parent. Before transferring check if a
103 -- similar dependent record exists on the new parent. If a duplicate exists
104 -- then do not transfer and return the id of the duplicate record as the
105 -- Merged To Id
106 -- ************************************************************************
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 -- =========== ========== ==========
116 -- 1200 1100
117 -- 1300 1400
118 --
119 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
120 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
121 -- therefore, if changing 1200 to 1300 (customer_id)
122 -- and 1100 to 1400 (address_id), then it will cause unique
123 -- key violation assume that all other fields are the same
124 -- So, please check if you need to check for record duplication
125 -- ************************************************************************
126
127 IF p_from_fk_id <> p_to_fk_id THEN
128 BEGIN
129 IF p_parent_entity_name = 'HZ_PARTIES' THEN
130 FOR I in c_get_pm_access_id (p_from_fk_id, p_to_fk_id) LOOP
131 FND_FILE.PUT_LINE(FND_FILE.LOG,
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
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,
146 last_update_login = hz_utility_pub.last_update_login,
147 program_application_id=hz_utility_pub.program_application_id,
148 program_id = hz_utility_pub.program_id,
149 program_update_date = sysdate
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'
159 and source_id = p_to_fk_id),
160 last_update_date = hz_utility_pub.last_update_date,
161 last_updated_by = hz_utility_pub.user_id,
162 last_update_login = hz_utility_pub.last_update_login,
163 program_application_id=hz_utility_pub.program_application_id,
164 program_id = hz_utility_pub.program_id,
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,
174 program_application_id=hz_utility_pub.program_application_id,
175 program_id = hz_utility_pub.program_id,
176 program_update_date = sysdate
177 where partner_cont_party_id = p_from_fk_id;
178 -- end ffang 012204, bug 3395366
179
180 ELSIF p_parent_entity_name = 'HZ_PARTY_SITES' THEN -- merge party_site
181
182 -- delete duplicate records (which will violate unique constraint)
183 FOR I in c_get_psm_access_id (p_from_fk_id, p_to_fk_id) LOOP
184 FND_FILE.PUT_LINE(FND_FILE.LOG,
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,
203 last_update_login = hz_utility_pub.last_update_login,
204 program_application_id=hz_utility_pub.program_application_id,
205 program_id = hz_utility_pub.program_id,
206 program_update_date = sysdate
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,
216 program_application_id=hz_utility_pub.program_application_id,
217 program_id = hz_utility_pub.program_id,
218 program_update_date = sysdate
219 where partner_address_id = p_from_fk_id;
220 -- end ffang 012204, bug 3395366
221
222 END IF;
223 EXCEPTION
224 WHEN OTHERS THEN
225 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': '
226 ||p_parent_entity_name|| sqlerrm);
227 x_return_status := FND_API.G_RET_STS_ERROR;
228 raise;
229 END;
230 END IF;
231
232 FND_FILE.PUT_LINE(FND_FILE.LOG, 'AS_TAP_MERGE_PKG .ACCESS_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
233
234 end;
235
236 PROCEDURE TAP_MERGE
237 ( p_entity_name IN VARCHAR2
238 ,p_from_id IN NUMBER
239 ,p_to_id IN OUT NOCOPY NUMBER
240 ,p_from_fk_id IN NUMBER
241 ,p_to_fk_id IN NUMBER
242 ,p_parent_entity_name IN VARCHAR2
243 ,p_batch_id IN NUMBER
244 ,p_batch_party_id IN NUMBER
245 ,x_return_status IN OUT NOCOPY VARCHAR2
246 )is
247 l_api_name CONSTANT VARCHAR2(30) := 'TAP_MERGE';
248 l_api_version_number CONSTANT NUMBER := 2.0;
249 l_merge_reason_code VARCHAR2(30);
250 begin
251 FND_FILE.PUT_LINE(FND_FILE.LOG,'AS_TAP_MERGE_PKG .TAP_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
252
253 x_return_status := FND_API.G_RET_STS_SUCCESS;
254
255 select merge_reason_code into l_merge_reason_code
256 from HZ_MERGE_BATCH
257 where batch_id = p_batch_id;
258
259 IF l_merge_reason_code = 'DUPLICATE' THEN
260 -- ***************************************************************************
261 -- if reason code is duplicate then allow the party merge to happen without
262 -- any validations.
263 -- ***************************************************************************
264 null;
265 ELSE
266 -- ***************************************************************************
267 -- if there are any validations to be done, include it in this section
268 -- ***************************************************************************
269 null;
270 END IF;
271
272 -- ***************************************************************************
273 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
274 -- needs to be done. Set Merged To Id is same as Merged From Id and return
275 -- ***************************************************************************
276 if p_from_fk_id = p_to_fk_id then
277 p_to_id := p_from_id;
278 return;
279 end if;
280
281 -- ***************************************************************************
282 -- If the parent has changed(ie. Parent is getting merged) then transfer the
283 -- dependent record to the new parent. Before transferring check if a similar
284 -- dependent record exists on the new parent. If a duplicate exists then do
285 -- not transfer and return the id of the duplicate record as the Merged To Id
286 -- ***************************************************************************
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 -- =========== ========== ==========
296 -- 1200 1100
297 -- 1300 1400
298 --
299 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
300 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
301 -- therefore, if changing 1200 to 1300 (customer_id)
302 -- and 1100 to 1400 (address_id), then it will cause unique
303 -- key violation assume that all other fields are the same
304 -- So, please check if you need to check for record duplication
305 -- ***************************************************************************
306
307 IF p_from_fk_id <> p_to_fk_id THEN
308 BEGIN
309 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
310 delete from as_changed_accounts_all
311 where customer_id = p_to_fk_id;
312
313 UPDATE AS_CHANGED_ACCOUNTS_ALL
314 set object_version_number = nvl(object_version_number,0) + 1,
315 customer_id = p_to_fk_id,
316 last_update_date = hz_utility_pub.last_update_date,
317 last_updated_by = hz_utility_pub.user_id,
318 last_update_login = hz_utility_pub.last_update_login,
319 program_application_id = hz_utility_pub.program_application_id,
320 program_id = hz_utility_pub.program_id,
321 program_update_date = sysdate
322 where customer_id = p_from_fk_id;
323
324
325 ELSif p_parent_entity_name = 'HZ_PARTY_SITES' THEN -- merge party_site
326
327 delete from as_changed_accounts_all b
328 where b.address_id=p_from_fk_id
329 and b.customer_id in
330 (select customer_id
331 from as_changed_accounts_all c
332 where c.address_id=p_to_fk_id) ;
333
334
335 UPDATE AS_CHANGED_ACCOUNTS_ALL
336 set object_version_number = nvl(object_version_number,0) + 1,
337 address_id = p_to_fk_id,
338 last_update_date = hz_utility_pub.last_update_date,
339 last_updated_by = hz_utility_pub.user_id,
340 last_update_login = hz_utility_pub.last_update_login,
341 program_application_id = hz_utility_pub.program_application_id,
342 program_id = hz_utility_pub.program_id,
343 program_update_date = sysdate
344 where address_id = p_from_fk_id
345 and customer_id not in(select customer_id from as_changed_accounts_all
346 where address_id=p_to_fk_id) ;
347
348 END IF;
349 EXCEPTION
350 WHEN OTHERS THEN
351 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
352 x_return_status := FND_API.G_RET_STS_ERROR;
353 raise;
354 END;
355 END IF;
356
357 FND_FILE.PUT_LINE(FND_FILE.LOG,'AS_TAP_MERGE_PKG .TAP_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
358
359 end;
360 END AS_TAP_MERGE_PKG;
[Home] [Help]