1 PACKAGE BODY AS_TAP_MERGE_PKG as
2 /* $Header: asxpmteb.pls 120.1.12010000.4 2010/03/09 12:26:12 sariff ship $ */
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
25
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);
26 -- added for bug 6431278/6455932
27 salesforce_id_null EXCEPTION;
28 PRAGMA EXCEPTION_INIT (salesforce_id_null, -01407);
29
30 CURSOR c_get_pm_access_id (c_from_fk_id NUMBER, c_to_fk_id NUMBER) IS
31 select access_id from as_accesses_all a
32 where customer_id= c_from_fk_id
33 and exists (select null from as_accesses_all b
34 where customer_id =c_to_fk_id
35 and (b.lead_id = a.lead_id or
36 (b.lead_id is null and a.lead_id is null))
37 and (b.org_id =a.org_id or
38 (b.org_id is null and a.org_id is null))
39 and (b.salesforce_id =a.salesforce_id or
40 (b.salesforce_id is null and a.salesforce_id is null))
41 and (b.sales_lead_id =a.sales_lead_id or
42 (b.sales_lead_id is null and a.sales_lead_id is null))
43 and (b.sales_group_id = a.sales_group_id or
44 (b.sales_group_id is null and a.sales_group_id is null)));
45
46 CURSOR c_get_psm_access_id (c_from_fk_id NUMBER, c_to_fk_id NUMBER) IS
47 select access_id
48 from as_accesses_all b
49 where b.address_id = c_from_fk_id
50 and b.customer_id in
51 (select customer_id
52 from as_accesses_all c
53 where (c.address_id = c_to_fk_id or
54 (b.address_id is null and c.address_id is null))
55 and (b.lead_id = c.lead_id or
56 (b.lead_id is null and c.lead_id is null))
57 and (b.org_id =c.org_id or
58 (b.org_id is null and c.org_id is null))
59 and (b.salesforce_id =c.salesforce_id or
60 (b.salesforce_id is null and c.salesforce_id is null))
61 and (b.sales_lead_id =c.sales_lead_id or
62 (b.sales_lead_id is null and c.sales_lead_id is null))
63 and (b.sales_group_id = c.sales_group_id or
64 (b.sales_group_id is null and c.sales_group_id is null)));
65
66 BEGIN
67 FND_FILE.PUT_LINE(FND_FILE.LOG,'AS_TAP_MERGE_PKG.ACCESS_MERGE start : '
68 ||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
69 FND_FILE.PUT_LINE(FND_FILE.LOG,'Entity: '||p_parent_entity_name);
70 FND_FILE.PUT_LINE(FND_FILE.LOG,'from_fk: '||p_from_fk_id);
71 FND_FILE.PUT_LINE(FND_FILE.LOG,'to_fk: '||p_to_fk_id);
72
73 x_return_status := FND_API.G_RET_STS_SUCCESS;
74
75 select merge_reason_code into l_merge_reason_code
76 from HZ_MERGE_BATCH
77 where batch_id = p_batch_id;
78
79 IF l_merge_reason_code = 'DUPLICATE' THEN
80 -- *********************************************************************
81 -- if reason code is duplicate then allow the party merge to happen
82 -- without any validations.
83 -- *********************************************************************
84 null;
85 ELSE
86 -- *********************************************************************
87 -- if there are any validations to be done, include it in this section
88 -- *********************************************************************
89 null;
90 END IF;
91
92 -- ************************************************************************
93 -- If the parent has NOT changed (ie. Parent getting transferred) then
94 -- nothing needs to be done. Set Merged To Id is same as Merged From Id
95 -- and return
96 -- ************************************************************************
97 if p_from_fk_id = p_to_fk_id then
98 p_to_id := p_from_id;
99 return;
100 end if;
101
102 -- ************************************************************************
103 -- If the parent has changed(ie. Parent is getting merged) then transfer
104 -- the dependent record to the new parent. Before transferring check if a
105 -- similar dependent record exists on the new parent. If a duplicate exists
106 -- then do not transfer and return the id of the duplicate record as the
107 -- Merged To Id
108 -- ************************************************************************
109
110 -- ************************************************************************
111 -- Add your own logic if you need to take care of the following cases
112 -- Check the if record duplicate if change party_id from merge-from
113 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
114 -- situation
115 --
116 -- customer_id address_id contact_id
117 -- =========== ========== ==========
118 -- 1200 1100
119 -- 1300 1400
120 --
121 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
122 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
123 -- therefore, if changing 1200 to 1300 (customer_id)
124 -- and 1100 to 1400 (address_id), then it will cause unique
125 -- key violation assume that all other fields are the same
126 -- So, please check if you need to check for record duplication
127 -- ************************************************************************
128
129 IF p_from_fk_id <> p_to_fk_id THEN
130 BEGIN
131 IF p_parent_entity_name = 'HZ_PARTIES' THEN
132 FOR I in c_get_pm_access_id (p_from_fk_id, p_to_fk_id) LOOP
133 FND_FILE.PUT_LINE(FND_FILE.LOG,
134 'Deleting PARTY access_id: '||I.access_id);
138 DELETE FROM as_accesses_all
135 DELETE FROM as_territory_accesses
136 WHERE access_id = I.access_id;
137
139 WHERE access_id = I.access_id;
140 END LOOP;
141
142 -- merge party
143 UPDATE AS_ACCESSES_ALL
144 set object_version_number = nvl(object_version_number,0) + 1,
145 customer_id = p_to_fk_id,
146 last_update_date = hz_utility_pub.last_update_date,
147 last_updated_by = hz_utility_pub.user_id,
148 last_update_login = hz_utility_pub.last_update_login,
149 program_application_id=hz_utility_pub.program_application_id,
150 program_id = hz_utility_pub.program_id,
151 program_update_date = sysdate
152 where customer_id = p_from_fk_id;
153
154 -- ffang 012204, bug 3395366, party merge for partners
155 -- party merge for partner_customer_id
156 -- Exception clause is added for bug 6431278/6455932
157 Begin
158 UPDATE AS_ACCESSES_ALL
159 set partner_customer_id = p_to_fk_id,
160 salesforce_id=( select resource_id
161 from jtf_rs_resource_extns
162 where category = 'PARTNER'
163 and source_id = p_to_fk_id),
164 last_update_date = hz_utility_pub.last_update_date,
165 last_updated_by = hz_utility_pub.user_id,
166 last_update_login = hz_utility_pub.last_update_login,
167 program_application_id=hz_utility_pub.program_application_id,
168 program_id = hz_utility_pub.program_id,
169 program_update_date = sysdate
170 where partner_customer_id = p_from_fk_id;
171 Exception
172 when salesforce_id_null then
173 FND_FILE.PUT_LINE(FND_FILE.LOG,'At exception: '||sqlerrm);
174 UPDATE AS_ACCESSES_ALL
175 set partner_customer_id = p_to_fk_id,
176 last_update_date = hz_utility_pub.last_update_date,
177 last_updated_by = hz_utility_pub.user_id,
178 last_update_login = hz_utility_pub.last_update_login,
179 program_application_id=hz_utility_pub.program_application_id,
180 program_id = hz_utility_pub.program_id,
181 program_update_date = sysdate
182 where partner_customer_id = p_from_fk_id;
183 End;
184
185 -- party merge for partner_cont_party_id
186 UPDATE AS_ACCESSES_ALL
187 set partner_cont_party_id = p_to_fk_id,
188 last_update_date = hz_utility_pub.last_update_date,
189 last_updated_by = hz_utility_pub.user_id,
190 last_update_login = hz_utility_pub.last_update_login,
191 program_application_id=hz_utility_pub.program_application_id,
192 program_id = hz_utility_pub.program_id,
193 program_update_date = sysdate
194 where partner_cont_party_id = p_from_fk_id;
195 -- end ffang 012204, bug 3395366
196
197 ELSIF p_parent_entity_name = 'HZ_PARTY_SITES' THEN -- merge party_site
198
199 -- delete duplicate records (which will violate unique constraint)
200 FOR I in c_get_psm_access_id (p_from_fk_id, p_to_fk_id) LOOP
201 FND_FILE.PUT_LINE(FND_FILE.LOG,
202 'Deleting PARTY SITE access_id: '||I.access_id);
203 DELETE FROM as_territory_accesses
204 WHERE access_id = I.access_id;
205
206 DELETE FROM as_accesses_all
207 WHERE access_id = I.access_id;
208 END LOOP;
209
210 -- The following statement will not update records if parties are merged
211 -- before party sites. As per TCA design docs, party sites are always
212 -- merged before parties. The subquery is to prevent updating new customer.
213
214 -- merge party site
215 UPDATE AS_ACCESSES_ALL
216 set object_version_number = nvl(object_version_number,0) + 1,
217 address_id = p_to_fk_id,
218 last_update_date = hz_utility_pub.last_update_date,
219 last_updated_by = hz_utility_pub.user_id,
220 last_update_login = hz_utility_pub.last_update_login,
221 program_application_id=hz_utility_pub.program_application_id,
222 program_id = hz_utility_pub.program_id,
223 program_update_date = sysdate
224 where address_id = p_from_fk_id;
225
226 -- ffang 012204, bug 3395366, party merge for partners
227 -- party merge for partner_address_id
228 UPDATE AS_ACCESSES_ALL
229 set partner_address_id = p_to_fk_id,
230 last_update_date = hz_utility_pub.last_update_date,
231 last_updated_by = hz_utility_pub.user_id,
232 last_update_login = hz_utility_pub.last_update_login,
233 program_application_id=hz_utility_pub.program_application_id,
234 program_id = hz_utility_pub.program_id,
235 program_update_date = sysdate
236 where partner_address_id = p_from_fk_id;
237 -- end ffang 012204, bug 3395366
238
239 END IF;
240 EXCEPTION
241 WHEN OTHERS THEN
242 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': '
243 ||p_parent_entity_name|| sqlerrm);
244 x_return_status := FND_API.G_RET_STS_ERROR;
245 raise;
246 END;
247 END IF;
248
249 FND_FILE.PUT_LINE(FND_FILE.LOG, 'AS_TAP_MERGE_PKG .ACCESS_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
250
251 end;
255 ,p_from_id IN NUMBER
252
253 PROCEDURE TAP_MERGE
254 ( p_entity_name IN VARCHAR2
256 ,p_to_id IN OUT NOCOPY NUMBER
257 ,p_from_fk_id IN NUMBER
258 ,p_to_fk_id IN NUMBER
259 ,p_parent_entity_name IN VARCHAR2
260 ,p_batch_id IN NUMBER
261 ,p_batch_party_id IN NUMBER
262 ,x_return_status IN OUT NOCOPY VARCHAR2
263 )is
264 l_api_name CONSTANT VARCHAR2(30) := 'TAP_MERGE';
265 l_api_version_number CONSTANT NUMBER := 2.0;
266 l_merge_reason_code VARCHAR2(30);
267 begin
268 FND_FILE.PUT_LINE(FND_FILE.LOG,'AS_TAP_MERGE_PKG .TAP_MERGE start : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
269
270 x_return_status := FND_API.G_RET_STS_SUCCESS;
271
272 select merge_reason_code into l_merge_reason_code
273 from HZ_MERGE_BATCH
274 where batch_id = p_batch_id;
275
276 IF l_merge_reason_code = 'DUPLICATE' THEN
277 -- ***************************************************************************
278 -- if reason code is duplicate then allow the party merge to happen without
279 -- any validations.
280 -- ***************************************************************************
281 null;
282 ELSE
283 -- ***************************************************************************
284 -- if there are any validations to be done, include it in this section
285 -- ***************************************************************************
286 null;
287 END IF;
288
289 -- ***************************************************************************
290 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
291 -- needs to be done. Set Merged To Id is same as Merged From Id and return
292 -- ***************************************************************************
293 if p_from_fk_id = p_to_fk_id then
294 p_to_id := p_from_id;
295 return;
296 end if;
297
298 -- ***************************************************************************
299 -- If the parent has changed(ie. Parent is getting merged) then transfer the
300 -- dependent record to the new parent. Before transferring check if a similar
301 -- dependent record exists on the new parent. If a duplicate exists then do
302 -- not transfer and return the id of the duplicate record as the Merged To Id
303 -- ***************************************************************************
304
305 -- ***************************************************************************
306 -- Add your own logic if you need to take care of the following cases
307 -- Check the if record duplicate if change party_id from merge-from
308 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
309 -- situation
310 --
311 -- customer_id address_id contact_id
312 -- =========== ========== ==========
313 -- 1200 1100
314 -- 1300 1400
315 --
316 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
317 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
318 -- therefore, if changing 1200 to 1300 (customer_id)
319 -- and 1100 to 1400 (address_id), then it will cause unique
320 -- key violation assume that all other fields are the same
321 -- So, please check if you need to check for record duplication
322 -- ***************************************************************************
323
324 IF p_from_fk_id <> p_to_fk_id THEN
325 BEGIN
326 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
327 delete from as_changed_accounts_all
328 where customer_id = p_to_fk_id;
329
330 UPDATE AS_CHANGED_ACCOUNTS_ALL
331 set object_version_number = nvl(object_version_number,0) + 1,
332 customer_id = p_to_fk_id,
333 last_update_date = hz_utility_pub.last_update_date,
334 last_updated_by = hz_utility_pub.user_id,
335 last_update_login = hz_utility_pub.last_update_login,
336 program_application_id = hz_utility_pub.program_application_id,
337 program_id = hz_utility_pub.program_id,
338 program_update_date = sysdate
339 where customer_id = p_from_fk_id;
340
341
342 ELSif p_parent_entity_name = 'HZ_PARTY_SITES' THEN -- merge party_site
343
344 delete from as_changed_accounts_all b
345 where b.address_id=p_from_fk_id
346 and b.customer_id in
347 (select customer_id
348 from as_changed_accounts_all c
349 where c.address_id=p_to_fk_id) ;
350
351
352 UPDATE AS_CHANGED_ACCOUNTS_ALL
353 set object_version_number = nvl(object_version_number,0) + 1,
354 address_id = p_to_fk_id,
355 last_update_date = hz_utility_pub.last_update_date,
356 last_updated_by = hz_utility_pub.user_id,
357 last_update_login = hz_utility_pub.last_update_login,
358 program_application_id = hz_utility_pub.program_application_id,
359 program_id = hz_utility_pub.program_id,
360 program_update_date = sysdate
361 where address_id = p_from_fk_id
362 and customer_id not in(select customer_id from as_changed_accounts_all
363 where address_id=p_to_fk_id) ;
364
365 END IF;
366 EXCEPTION
367 WHEN OTHERS THEN
368 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
369 x_return_status := FND_API.G_RET_STS_ERROR;
370 raise;
371 END;
372 END IF;
373
374 FND_FILE.PUT_LINE(FND_FILE.LOG,'AS_TAP_MERGE_PKG .TAP_MERGE end : '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));
375
376 end;
377 END AS_TAP_MERGE_PKG;
[Home] [Help]