1 Package Body OKC_PARTY_MERGE_PUB AS
2 /* $Header: OKCPPMGB.pls 120.2.12010000.2 2008/10/24 08:01:43 ssreekum ship $ */
3 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4
5 -- Start of Comments
6 -- API Name :OKC_PARTY_MERGE_PUB
7 -- Type :Public
8 -- Purpose :Manage Party merges
9 --
10 -- NOTES
11 -- Merging Rules:
12 -- Account merges across parties, when the "duplicate" or source party
13 -- is referenced in a contract are not allowed.
14 --
15 -- Merges where the duplicate party is not referenced in a contract are
16 -- processed (account, site, site use).
17 --
18 -- Account merges within the same party are processed (account, site,
19 -- site use).
20 --
21 -- Site merges in the same account are processed (site, site use).
22 --
23 -- When merging Party ids are looked for in:
24 -- OKC_K_PARTY_ROLES
25 -- OKC_RULES
26 -- OKC_K_ITEMS
27 -- OKC_CONTACTS
28 -- For customer site merges, cust_acct_site_ids are looked for in:
29 -- OKC_K_PARTY_ROLES
30 -- OKC_RULES
31 -- OKC_K_ITEMS
32 -- OKC_CONTACTS
33 -- For customer site use merges, site_use_ids are looked for in:
34 -- OKC_K_PARTY_ROLES
35 -- OKC_RULES
36 -- OKC_K_ITEMS
37 -- OKC_CONTACTS
38 --
39 -- JTF Objects:
40 -- The merge depends upon the proper usages being set for the JTF objects used
41 -- as party roles, rules, and items. These usages are as follows:
42 -- OKX_PARTY This object is based on a view which returns the
43 -- party_id as id1.
44 -- OKX_P_SITE This object is based on a view which returns
45 -- party_site_id as id1.
46 -- OKX_P_SITE_USE This object is based on a view which returns
47 -- party_site_use_id as id1.
48 --
49 -- To be defined in JTF: This code is define under OKX_PCONTACT
50 --
51 -- OKX_CONTACTS This object is based on a view which returns
52 -- site_use_id as id1.
53 --
54 -- Following JTF object usages are only applicable if its a Customer Merge
55 --
56 -- OKX_ACCOUNT This object is based on a view which returns
57 -- cust_account_id as id1.
58 -- OKX_C_SITE This object is based on a view which returns
59 -- cust_acct_site_id as id1.
60 -- OKX_C_SITE_USE This object is based on a view which returns
61 -- site_use_id as id1.
62 -- The usages are how the merge determines which jtot_object_codes are candidates
63 -- for the different types of merges.
64 --
65 --
66 -- End of comments
67
68
69 -- Global constants
70 c_party CONSTANT VARCHAR2(20) := 'OKX_PARTY'; -- HZ_PARTIES
71 c_p_site CONSTANT VARCHAR2(20) := 'OKX_P_SITE'; -- HZ_PARTY_SITES
72 c_p_site_use CONSTANT VARCHAR2(20) := 'OKX_P_SITE_USE'; -- HZ_PARTY_SITE_USES
73 --
74 c_contact CONSTANT VARCHAR2(20) := 'OKX_CONTACTS'; -- HZ_PARTIES
75 --
76 -- c_account CONSTANT VARCHAR2(20) := 'OKX_ACCOUNT'; -- HZ_CUST_ACCOUNTS
77 -- c_c_site CONSTANT VARCHAR2(20) := 'OKX_C_SITE'; -- HZ_CUST_ACCT_SITES_ALL
78 -- c_c_site_use CONSTANT VARCHAR2(20) := 'OKX_C_SITE_USE'; -- HZ_CUST_SITE_USES_ALL
79 --
80 G_PROC_NAME CONSTANT VARCHAR2(30) := 'OKC_PARTY_MERGE_PUB';
81 G_USER_ID CONSTANT NUMBER(15) := FND_GLOBAL.USER_ID;
82 G_LOGIN_ID CONSTANT NUMBER(15) := FND_GLOBAL.LOGIN_ID;
83 -- Parameters:
84 -- IN - All IN parameters are REQUIRED.
85 -- p_entity_name VARCHAR2 - Name of the entity that is being merged
86 -- p_from_id NUMBER - Id of the record that is being merged
87 -- p_from_fk_id NUMBER - Id of the Old Parent
88 -- p_to_fk_id NUMBER - Id of the New Parent
89 -- p_parent_entity_name VARCHAR2 - Parent entity name
90 -- p_batch_id NUMBER - Id of the Batch
91 -- p_batch_party_id NUMBER - Id of the batch and party record
92 -- OUT:
93 -- x_to_id NUMBER - Id of the record under the new parent
94 -- that its merged to
95 -- x_return_status VARCHAR2 - Return the status of the procedure
96
97 --
98 -- Merge Procedure for OKC_K_PARTY_ROLES_B
99 --
100 PROCEDURE OKC_CPL_MERGE_PARTY (
101 p_entity_name IN VARCHAR2,
102 p_from_id IN NUMBER,
103 x_to_id OUT NOCOPY NUMBER,
104 p_from_fk_id IN NUMBER,
105 p_to_fk_id IN NUMBER,
106 p_parent_entity_name IN VARCHAR2,
107 p_batch_id IN NUMBER,
108 p_batch_party_id IN NUMBER,
109 x_return_status OUT NOCOPY VARCHAR2)
110 IS
111 --
112 l_merge_reason_code VARCHAR2(30);
113 l_api_name VARCHAR2(30) := 'OKC_CPL_MERGE_PARTY';
114 l_count NUMBER(10) := 0;
115 l_object_user_code VARCHAR2(20);
116 l_return_status VARCHAR2(1); -- Bug 2949149
117
118 --npalepu added on 10-feb-2006 for bug # 5005475
119 cursor l_get_cpl_ids_csr is
120 select role1.id from_cpl_id, role2.id to_cpl_id
121 from okc_k_party_roles_b role1,okc_k_party_roles_b role2
122 where role1.cle_id = role2.cle_id
123 and role1.rle_code = role2.rle_code
124 and role1.jtot_object1_code = role2.jtot_object1_code
125 and role1.jtot_object1_code IN (SELECT ojt.object_code
126 FROM jtf_objects_b ojt
127 ,jtf_object_usages oue
128 WHERE ojt.object_code = oue.object_code
129 AND oue.object_user_code = l_object_user_code)
130 and role1.object1_id1 = p_from_fk_id
131 and role2.object1_id1 = p_to_fk_id;
132
133 TYPE l_CPL_ID IS TABLE OF okc_k_party_roles_b.id%TYPE INDEX BY BINARY_INTEGER;
134 l_from_cpl_id l_CPL_ID;
135 l_to_cpl_id l_CPL_ID;
136 --end npalepu
137
138 BEGIN
139 --
140 fnd_file.put_line(fnd_file.log, 'OKC_PARTY_MERGE_PKG.OKC_CPL_MERGE_PARTY');
141
142 fnd_file.put_line(fnd_file.log, '****** PARAMETERS ****** ');
143 fnd_file.put_line(fnd_file.log, 'p_entity_name : '||p_entity_name);
144 fnd_file.put_line(fnd_file.log, 'p_from_id : '||p_from_id);
145 fnd_file.put_line(fnd_file.log, 'p_from_fk_id : '||p_from_fk_id);
146 fnd_file.put_line(fnd_file.log, 'p_to_fk_id : '||p_to_fk_id);
147 fnd_file.put_line(fnd_file.log, 'p_parent_entity_name : '||p_parent_entity_name);
148 fnd_file.put_line(fnd_file.log, 'p_batch_id : '||p_batch_id);
149 fnd_file.put_line(fnd_file.log, 'p_batch_party_id : '||p_batch_party_id);
150 --
151 arp_message.set_line('OKC_PARTY_MERGE_PKG.OKC_CPL_MERGE_PARTY()+');
152
153 x_return_status := FND_API.G_RET_STS_SUCCESS;
154
155 --
156 --
157 if p_parent_entity_name = 'HZ_PARTIES' then
158 l_object_user_code := c_party;
159 end if;
160 --
161
162 fnd_file.put_line(fnd_file.log, 'l_object_user_code : '||l_object_user_code);
163
164
165 --
166 select merge_reason_code
167 into l_merge_reason_code
168 from hz_merge_batch
169 where batch_id = p_batch_id;
170
171 if l_merge_reason_code = 'DUPLICATE' then
172 -- if reason code is duplicate then allow the party merge to happen without
173 -- any validations.
174 null;
175 else
176 -- if there are any validations to be done, include it in this section
177 null;
178 end if;
179
180 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
181 -- needs to be done. Set Merged To Id is same as Merged From Id and return
182
183 if p_from_fk_id = p_to_fk_id then
184 x_to_id := p_from_id;
185 return;
186 end if;
187
188 -- If the parent has changed(ie. Parent is getting merged) then transfer the
189 -- dependent record to the new parent. Before transferring check if a similar
190 -- dependent record exists on the new parent. If a duplicate exists then do
191 -- not transfer and return the id of the duplicate record as the Merged To Id
192
193 if p_from_fk_id <> p_to_fk_id then
194 --
195 begin
196
197 arp_message.set_name('AR','AR_UPDATING_TABLE');
198 arp_message.set_token('TABLE_NAME','OKC_K_PARTY_ROLES',FALSE);
199 fnd_file.put_line(fnd_file.log, 'Updating Table okc_k_party_roles_b');
200 --
201 --
202 -- Fix for bug 4105272 Insert into okc_k_vers_numbers_h
203 INSERT INTO OKC_K_VERS_NUMBERS_H(
204 chr_id,
205 major_version,
206 minor_version,
207 object_version_number,
208 created_by,
209 creation_date,
210 last_updated_by,
211 last_update_date,
212 last_update_login)
213 (SELECT
214 chr_id,
215 major_version,
216 minor_version,
217 object_version_number,
218 created_by,
219 creation_date,
220 last_updated_by,
221 last_update_date,
222 last_update_login
223 FROM OKC_K_VERS_NUMBERS
224 WHERE chr_id IN (select dnz_chr_id
225 from okc_k_party_roles_b kpr
226 where kpr.object1_id1 = to_char(p_from_fk_id)
227 AND kpr.jtot_object1_code IN
228 (SELECT ojt.object_code
229 FROM jtf_objects_b ojt
230 ,jtf_object_usages oue
231 WHERE ojt.object_code = oue.object_code
232 AND oue.object_user_code = l_object_user_code)));
233 --
234 UPDATE okc_k_vers_numbers ver
235 SET ver.minor_version = ver.minor_version + 1
236 ,ver.object_version_number = ver.object_version_number + 1
237 ,ver.last_update_date = SYSDATE
238 ,ver.last_updated_by = arp_standard.profile.user_id
239 ,ver.last_update_login = arp_standard.profile.last_update_login
240 WHERE chr_id IN (select dnz_chr_id
241 from okc_k_party_roles_b kpr
242 where kpr.object1_id1 = to_char(p_from_fk_id) -- added for Bug 3611998
243 AND kpr.jtot_object1_code IN (SELECT ojt.object_code
244 FROM jtf_objects_b ojt
245 ,jtf_object_usages oue
246 WHERE ojt.object_code = oue.object_code
247 AND oue.object_user_code = l_object_user_code));
248
249 --npalepu added on 10-feb-2006 for bug # 5005475
250 l_count := sql%rowcount;
251
252 fnd_file.put_line(fnd_file.log, 'No of Rows Updated : '||l_count);
253 arp_message.set_name('AR','AR_ROWS_UPDATED');
254 arp_message.set_token('NUM_ROWS',to_char(l_count));
255
256 OPEN l_get_cpl_ids_csr;
257 LOOP
258 FETCH l_get_cpl_ids_csr BULK COLLECT INTO l_from_cpl_id, l_to_cpl_id LIMIT 1000;
259
260 -- nechatur for bug#5378426 added on 9/7/06
261 EXIT WHEN l_from_cpl_id.COUNT <= 0 ;
262 -- end nechatur
263
264 fnd_file.put_line(fnd_file.log, 'Updating Table okc_contacts');
265
266 FORALL i IN l_from_cpl_id.FIRST .. l_from_cpl_id.LAST
267 UPDATE okc_contacts cntc
268 SET cntc.cpl_id = l_to_cpl_id(i)
269 ,cntc.object_version_number = cntc.object_version_number + 1
270 ,cntc.last_update_date = SYSDATE
271 ,cntc.last_updated_by = arp_standard.profile.user_id
272 ,cntc.last_update_login = arp_standard.profile.last_update_login
273 WHERE cntc.cpl_id = l_from_cpl_id(i);
274
275 l_count := sql%rowcount;
276 fnd_file.put_line(fnd_file.log, 'No of Rows Updated : '||l_count);
277 arp_message.set_name('AR','AR_ROWS_UPDATED');
278 arp_message.set_token('NUM_ROWS',to_char(l_count));
279
280 fnd_file.put_line(fnd_file.log, 'Deleting Table OKC_K_PARTY_ROLES_TL');
281 -- Delete party_roles_tl table to handle related party merge Bug # 4529376
282
283 FORALL i IN l_from_cpl_id.FIRST .. l_from_cpl_id.LAST
284 DELETE from okc_k_party_roles_tl rtl
285 WHERE rtl.id = l_from_cpl_id(i);
286
287 l_count := sql%rowcount;
288 fnd_file.put_line(fnd_file.log, 'No of Rows Deleted : '||l_count);
289
290 fnd_file.put_line(fnd_file.log, 'Deleting Table OKC_K_PARTY_ROLES');
291 -- Delete party_roles table to handle related party merge Bug # 4529376
292
293 FORALL i IN l_from_cpl_id.FIRST .. l_from_cpl_id.LAST
294 DELETE FROM okc_k_party_roles_B role1
295 WHERE role1.id = l_from_cpl_id(i);
296
297 l_count := sql%rowcount;
298 fnd_file.put_line(fnd_file.log, 'No of Rows Deleted : '||l_count);
299
300 --nechatur added on 9/7/2006 for bug # 5378426
301 l_from_cpl_id.DELETE;
302 l_to_cpl_id.DELETE;
303 --end nechatur
304
305 Exit When l_get_cpl_ids_csr%NOTFound;
306
307 END LOOP;
308 CLOSE l_get_cpl_ids_csr;
309 --end npalepu
310 --
311 --
312 fnd_file.put_line(fnd_file.log, 'Updating Table okc_k_party_roles_b');
313 UPDATE okc_k_party_roles_b kpr
314 SET kpr.object1_id1 = p_to_fk_id
315 ,kpr.object_version_number = kpr.object_version_number + 1
316 ,kpr.last_update_date = SYSDATE
317 ,kpr.last_updated_by = arp_standard.profile.user_id
318 ,kpr.last_update_login = arp_standard.profile.last_update_login
319 WHERE kpr.object1_id1 = to_char(p_from_fk_id)
320 AND kpr.jtot_object1_code IN (SELECT ojt.object_code
321 FROM jtf_objects_b ojt
322 ,jtf_object_usages oue
323 WHERE ojt.object_code = oue.object_code
324 AND oue.object_user_code = l_object_user_code) ;
325
326 l_count := sql%rowcount;
327 fnd_file.put_line(fnd_file.log, 'No of Rows Updated : '||l_count);
328 --
329 -- Bug 2949149 calling OKS procedure OKS_UPDATE_CONTRACT to update the short description
330 --
331 fnd_file.put_line(fnd_file.log, 'Before call to OKC_OKS_PUB.OKS_UPDATE_CONTRACT ');
332 OKC_OKS_PUB.OKS_UPDATE_CONTRACT(p_from_id => p_from_fk_id,
333 p_to_id => p_to_fk_id,
334 x_return_status => l_return_status);
335
336 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
337 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
338 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
339 RAISE OKC_API.G_EXCEPTION_ERROR;
340 END IF;
341
342 --
343 -- Following where clause removed after update from TCA (02/09/2001)
344 --
345 -- WHERE kpr.object1_id1 = p_from_fk_id
346 -- AND kpr.jtot_object1_code IN (SELECT ojt.object_code
347 -- FROM jtf_objects_b ojt
348 -- ,jtf_object_usages oue
349 -- WHERE ojt.object_code = oue.object_code
350 -- AND oue.object_user_code = l_object_user_code)
351 --
352 -- Reason: TCA calling routine will pass on the p_from_id as a parameter that holds the
353 -- primary key of entity being updated based on the WHERE clause defined in the
354 -- Party Merge Dictionary
355 --
356 l_count := sql%rowcount;
357
358 fnd_file.put_line(fnd_file.log, 'No of Rows Updated : '||l_count);
359 arp_message.set_name('AR','AR_ROWS_UPDATED');
360 arp_message.set_token('NUM_ROWS',to_char(l_count));
361 --
362 exception
363 when OKC_API.G_EXCEPTION_ERROR THEN -- Bug 2949149
364 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
365 fnd_file.put_line(fnd_file.log, g_proc_name||'.'||l_api_name||':'||sqlerrm);
366 x_return_status := FND_API.G_RET_STS_ERROR;
367 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN -- Bug 2949149
368 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
369 fnd_file.put_line(fnd_file.log, g_proc_name||'.'||l_api_name||':'||sqlerrm);
370 x_return_status := FND_API.G_RET_STS_ERROR;
371 when others then
372 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
373 --
374 fnd_file.put_line(fnd_file.log,(g_proc_name || '.' || l_api_name ||
375 'OKC_K_PARTY_ROLES = ' ||l_object_user_code||'-'|| p_from_id));
376 --
377 fnd_file.put_line(fnd_file.log, g_proc_name||'.'||l_api_name||':'||sqlerrm);
378 x_return_status := FND_API.G_RET_STS_ERROR;
379 end;
380 end if;
381 END OKC_CPL_MERGE_PARTY;
382 --
383 -- Merge Procedure for OKC_RULES_B (OBJECT1_CODE)
384 --
385 PROCEDURE OKC_RUL_MERGE_PARTY_ID1 (
386 p_entity_name IN VARCHAR2,
387 p_from_id IN NUMBER,
388 x_to_id OUT NOCOPY NUMBER,
389 p_from_fk_id IN NUMBER,
390 p_to_fk_id IN NUMBER,
391 p_parent_entity_name IN VARCHAR2,
392 p_batch_id IN NUMBER,
393 p_batch_party_id IN NUMBER,
394 x_return_status OUT NOCOPY VARCHAR2)
395 IS
396 --
397 l_merge_reason_code VARCHAR2(30);
398 l_api_name VARCHAR2(30) := 'OKC_RUL_MERGE_PARTY_ID1';
399 l_count NUMBER(10) := 0;
400 l_object_user_code VARCHAR2(20);
401 --
402 --
403 BEGIN
404 --
405 fnd_file.put_line(fnd_file.log, 'OKC_PARTY_MERGE_PKG.OKC_RUL_MERGE_PARTY_ID1');
406
407 fnd_file.put_line(fnd_file.log, '****** PARAMETERS ****** ');
408 fnd_file.put_line(fnd_file.log, 'p_entity_name : '||p_entity_name);
409 fnd_file.put_line(fnd_file.log, 'p_from_id : '||p_from_id);
410 fnd_file.put_line(fnd_file.log, 'p_from_fk_id : '||p_from_fk_id);
411 fnd_file.put_line(fnd_file.log, 'p_to_fk_id : '||p_to_fk_id);
412 fnd_file.put_line(fnd_file.log, 'p_parent_entity_name : '||p_parent_entity_name);
413 fnd_file.put_line(fnd_file.log, 'p_batch_id : '||p_batch_id);
414 fnd_file.put_line(fnd_file.log, 'p_batch_party_id : '||p_batch_party_id);
415 --
416 arp_message.set_line('OKC_PARTY_MERGE_PKG.OKC_RUL_MERGE_PARTY_ID1()+');
417
418 x_return_status := FND_API.G_RET_STS_SUCCESS;
419
420 --
421 --
422 if p_parent_entity_name = 'HZ_PARTIES' then l_object_user_code := c_party;
423 elsif p_parent_entity_name = 'HZ_PARTY_SITE_USES' then l_object_user_code := c_p_site_use;
424 elsif p_parent_entity_name = 'HZ_PARTY_SITES' then l_object_user_code := c_p_site;
425 end if;
426 --
427
428 fnd_file.put_line(fnd_file.log, 'l_object_user_code : '||l_object_user_code);
429
430
431 --
432 select merge_reason_code
433 into l_merge_reason_code
434 from hz_merge_batch
435 where batch_id = p_batch_id;
436
437 if l_merge_reason_code = 'DUPLICATE' then
438 -- if reason code is duplicate then allow the party merge to happen without
439 -- any validations.
440 null;
441 else
442 -- if there are any validations to be done, include it in this section
443 null;
444 end if;
445
446 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
447 -- needs to be done. Set Merged To Id is same as Merged From Id and return
448
449 if p_from_fk_id = p_to_fk_id then
450 x_to_id := p_from_id;
451 return;
452 end if;
453
454 -- If the parent has changed(ie. Parent is getting merged) then transfer the
455 -- dependent record to the new parent. Before transferring check if a similar
456 -- dependent record exists on the new parent. If a duplicate exists then do
457 -- not transfer and return the id of the duplicate record as the Merged To Id
458
459 if p_from_fk_id <> p_to_fk_id then
460 begin
461 -- Rules ID1
462 arp_message.set_name('AR','AR_UPDATING_TABLE');
463 arp_message.set_token('TABLE_NAME','OKC_RULES_B.OBJECT1_ID1',FALSE);
464 fnd_file.put_line(fnd_file.log, 'Updating Table okc_rules_b');
465 --
466 UPDATE okc_rules_b rle
467 SET rle.object1_id1 = p_to_fk_id
468 ,rle.object_version_number = rle.object_version_number + 1
469 ,rle.last_update_date = SYSDATE
470 ,rle.last_updated_by = arp_standard.profile.user_id
471 ,rle.last_update_login = arp_standard.profile.last_update_login
472 -- WHERE rle.object1_id1 = p_from_fk_id
473 WHERE rle.object1_id1 = to_char(p_from_fk_id) -- for Bug# 6896186
474 AND rle.jtot_object1_code IN (SELECT ojt.object_code
475 FROM jtf_objects_b ojt
476 ,jtf_object_usages oue
477 WHERE ojt.object_code = oue.object_code
478 AND oue.object_user_code = l_object_user_code)
479 ;
480 --
481 l_count := sql%rowcount;
482
483 fnd_file.put_line(fnd_file.log, 'No of Rows Updated : '||l_count);
484 arp_message.set_name('AR','AR_ROWS_UPDATED');
485 arp_message.set_token('NUM_ROWS',to_char(l_count));
486 --
487 --
488 exception
489 when others then
490 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
491 --
492 fnd_file.put_line(fnd_file.log,(g_proc_name || '.' || l_api_name ||
493 'OKC_K_PARTY_ROLES for = ' ||l_object_user_code||'-'|| p_from_id));
494 --
495 fnd_file.put_line(fnd_file.log, g_proc_name||'.'||l_api_name||':'||sqlerrm);
496 x_return_status := FND_API.G_RET_STS_ERROR;
497 end;
498 end if;
499 END OKC_RUL_MERGE_PARTY_ID1;
500 --
501 --
502 -- Merge Procedure for OKC_RULES_B (OBJECT2_CODE)
503 --
504 PROCEDURE OKC_RUL_MERGE_PARTY_ID2 (
505 p_entity_name IN VARCHAR2,
506 p_from_id IN NUMBER,
507 x_to_id OUT NOCOPY NUMBER,
508 p_from_fk_id IN NUMBER,
509 p_to_fk_id IN NUMBER,
510 p_parent_entity_name IN VARCHAR2,
511 p_batch_id IN NUMBER,
512 p_batch_party_id IN NUMBER,
513 x_return_status OUT NOCOPY VARCHAR2)
514 IS
515 --
516 l_merge_reason_code VARCHAR2(30);
517 l_api_name VARCHAR2(30) := 'OKC_RUL_MERGE_PARTY_ID2';
518 l_count NUMBER(10) := 0;
519 l_object_user_code VARCHAR2(20);
520 --
521 --
522 BEGIN
523 --
524 fnd_file.put_line(fnd_file.log, 'OKC_PARTY_MERGE_PKG.OKC_RUL_MERGE_PARTY_ID2');
525
526 fnd_file.put_line(fnd_file.log, '****** PARAMETERS ****** ');
527 fnd_file.put_line(fnd_file.log, 'p_entity_name : '||p_entity_name);
528 fnd_file.put_line(fnd_file.log, 'p_from_id : '||p_from_id);
529 fnd_file.put_line(fnd_file.log, 'p_from_fk_id : '||p_from_fk_id);
530 fnd_file.put_line(fnd_file.log, 'p_to_fk_id : '||p_to_fk_id);
531 fnd_file.put_line(fnd_file.log, 'p_parent_entity_name : '||p_parent_entity_name);
532 fnd_file.put_line(fnd_file.log, 'p_batch_id : '||p_batch_id);
533 fnd_file.put_line(fnd_file.log, 'p_batch_party_id : '||p_batch_party_id);
534 --
535 arp_message.set_line('OKC_PARTY_MERGE_PKG.OKC_RUL_MERGE_PARTY_ID2()+');
536
537 x_return_status := FND_API.G_RET_STS_SUCCESS;
538
539 --
540 --
541 if p_parent_entity_name = 'HZ_PARTIES' then l_object_user_code := c_party;
542 elsif p_parent_entity_name = 'HZ_PARTY_SITE_USES' then l_object_user_code := c_p_site_use;
543 elsif p_parent_entity_name = 'HZ_PARTY_SITES' then l_object_user_code := c_p_site;
544 end if;
545 --
546
547 fnd_file.put_line(fnd_file.log, 'l_object_user_code : '||l_object_user_code);
548
549
550 --
551 select merge_reason_code
552 into l_merge_reason_code
553 from hz_merge_batch
554 where batch_id = p_batch_id;
555
556 if l_merge_reason_code = 'DUPLICATE' then
557 -- if reason code is duplicate then allow the party merge to happen without
558 -- any validations.
559 null;
560 else
561 -- if there are any validations to be done, include it in this section
562 null;
563 end if;
564
565 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
566 -- needs to be done. Set Merged To Id is same as Merged From Id and return
567
568 if p_from_fk_id = p_to_fk_id then
569 x_to_id := p_from_id;
570 return;
571 end if;
572
573 -- If the parent has changed(ie. Parent is getting merged) then transfer the
574 -- dependent record to the new parent. Before transferring check if a similar
575 -- dependent record exists on the new parent. If a duplicate exists then do
576 -- not transfer and return the id of the duplicate record as the Merged To Id
577
578 if p_from_fk_id <> p_to_fk_id then
579 begin
580 -- Rules ID1
581 arp_message.set_name('AR','AR_UPDATING_TABLE');
582 arp_message.set_token('TABLE_NAME','OKC_RULES_B.OBJECT1_ID1',FALSE);
583 fnd_file.put_line(fnd_file.log, 'Updating Table okc_rules_b');
584 --
585 UPDATE okc_rules_b rle
586 SET rle.object2_id1 = p_to_fk_id
587 ,rle.object_version_number = rle.object_version_number + 1
588 ,rle.last_update_date = SYSDATE
589 ,rle.last_updated_by = arp_standard.profile.user_id
590 ,rle.last_update_login = arp_standard.profile.last_update_login
591 -- WHERE rle.object2_id1 = p_from_fk_id
592 WHERE rle.object2_id1 = to_char(p_from_fk_id) -- For Bug# 6896186
593 AND rle.jtot_object2_code IN (SELECT ojt.object_code
594 FROM jtf_objects_b ojt
595 ,jtf_object_usages oue
596 WHERE ojt.object_code = oue.object_code
597 AND oue.object_user_code = l_object_user_code)
598 ;
599 --
600 l_count := sql%rowcount;
601
602 fnd_file.put_line(fnd_file.log, 'No of Rows Updated : '||l_count);
603 arp_message.set_name('AR','AR_ROWS_UPDATED');
604 arp_message.set_token('NUM_ROWS',to_char(l_count));
605 --
606 --
607 exception
608 when others then
609 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
610 --
611 fnd_file.put_line(fnd_file.log,(g_proc_name || '.' || l_api_name ||
612 'OKC_K_PARTY_ROLES for = ' ||l_object_user_code||'-'|| p_from_id));
613 --
614 fnd_file.put_line(fnd_file.log, g_proc_name||'.'||l_api_name||':'||sqlerrm);
615 x_return_status := FND_API.G_RET_STS_ERROR;
616 end;
617 end if;
618 END OKC_RUL_MERGE_PARTY_ID2;
619 --
620 -- Merge Procedure for OKC_RULES_B (OBJECT3_CODE)
621 --
622 PROCEDURE OKC_RUL_MERGE_PARTY_ID3 (
623 p_entity_name IN VARCHAR2,
624 p_from_id IN NUMBER,
625 x_to_id OUT NOCOPY NUMBER,
626 p_from_fk_id IN NUMBER,
627 p_to_fk_id IN NUMBER,
628 p_parent_entity_name IN VARCHAR2,
629 p_batch_id IN NUMBER,
630 p_batch_party_id IN NUMBER,
631 x_return_status OUT NOCOPY VARCHAR2)
632 IS
633 --
634 l_merge_reason_code VARCHAR2(30);
635 l_api_name VARCHAR2(30) := 'OKC_RUL_MERGE_PARTY_ID3';
636 l_count NUMBER(10) := 0;
637 l_object_user_code VARCHAR2(20);
638 --
639 --
640 BEGIN
641 --
642 fnd_file.put_line(fnd_file.log, 'OKC_PARTY_MERGE_PKG.OKC_RUL_MERGE_PARTY_ID3');
643
644 fnd_file.put_line(fnd_file.log, '****** PARAMETERS ****** ');
645 fnd_file.put_line(fnd_file.log, 'p_entity_name : '||p_entity_name);
646 fnd_file.put_line(fnd_file.log, 'p_from_id : '||p_from_id);
647 fnd_file.put_line(fnd_file.log, 'p_from_fk_id : '||p_from_fk_id);
648 fnd_file.put_line(fnd_file.log, 'p_to_fk_id : '||p_to_fk_id);
649 fnd_file.put_line(fnd_file.log, 'p_parent_entity_name : '||p_parent_entity_name);
650 fnd_file.put_line(fnd_file.log, 'p_batch_id : '||p_batch_id);
651 fnd_file.put_line(fnd_file.log, 'p_batch_party_id : '||p_batch_party_id);
652 --
653 arp_message.set_line('OKC_PARTY_MERGE_PKG.OKC_RUL_MERGE_PARTY_ID3()+');
654
655 x_return_status := FND_API.G_RET_STS_SUCCESS;
656 --
657 --
658 if p_parent_entity_name = 'HZ_PARTIES' then l_object_user_code := c_party;
659 elsif p_parent_entity_name = 'HZ_PARTY_SITE_USES' then l_object_user_code := c_p_site_use;
660 elsif p_parent_entity_name = 'HZ_PARTY_SITES' then l_object_user_code := c_p_site;
661 end if;
662 --
663
664 fnd_file.put_line(fnd_file.log, 'l_object_user_code : '||l_object_user_code);
665
666
667 --
668 select merge_reason_code
669 into l_merge_reason_code
670 from hz_merge_batch
671 where batch_id = p_batch_id;
672
673 if l_merge_reason_code = 'DUPLICATE' then
674 -- if reason code is duplicate then allow the party merge to happen without
675 -- any validations.
676 null;
677 else
678 -- if there are any validations to be done, include it in this section
679 null;
680 end if;
681
682 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
683 -- needs to be done. Set Merged To Id is same as Merged From Id and return
684
685 if p_from_fk_id = p_to_fk_id then
686 x_to_id := p_from_id;
687 return;
688 end if;
689
690 -- If the parent has changed(ie. Parent is getting merged) then transfer the
691 -- dependent record to the new parent. Before transferring check if a similar
692 -- dependent record exists on the new parent. If a duplicate exists then do
693 -- not transfer and return the id of the duplicate record as the Merged To Id
694
695 if p_from_fk_id <> p_to_fk_id then
696 begin
697 -- Rules ID3
698 arp_message.set_name('AR','AR_UPDATING_TABLE');
699 arp_message.set_token('TABLE_NAME','OKC_RULES_B.OBJECT3_ID1',FALSE);
700 fnd_file.put_line(fnd_file.log, 'Updating Table okc_rules_b');
701 --
702 UPDATE okc_rules_b rle
703 SET rle.object3_id1 = p_to_fk_id
704 ,rle.object_version_number = rle.object_version_number + 1
705 ,rle.last_update_date = SYSDATE
706 ,rle.last_updated_by = arp_standard.profile.user_id
707 ,rle.last_update_login = arp_standard.profile.last_update_login
708 -- WHERE rle.object3_id1 = p_from_fk_id
709 WHERE rle.object3_id1 = to_char(p_from_fk_id) -- For Bug# 6896186
710 AND rle.jtot_object3_code IN (SELECT ojt.object_code
711 FROM jtf_objects_b ojt
712 ,jtf_object_usages oue
713 WHERE ojt.object_code = oue.object_code
714 AND oue.object_user_code = l_object_user_code)
715 ;
716
717 --
718 l_count := sql%rowcount;
719
720 fnd_file.put_line(fnd_file.log, 'No of Rows Updated : '||l_count);
721 arp_message.set_name('AR','AR_ROWS_UPDATED');
722 arp_message.set_token('NUM_ROWS',to_char(l_count));
723 --
724 --
725 exception
726 when others then
727 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
728 --
729 fnd_file.put_line(fnd_file.log,(g_proc_name || '.' || l_api_name ||
730 'OKC_K_PARTY_ROLES for = ' ||l_object_user_code||'-'|| p_from_id));
731 --
732 fnd_file.put_line(fnd_file.log, g_proc_name||'.'||l_api_name||':'||sqlerrm);
733 x_return_status := FND_API.G_RET_STS_ERROR;
734 end;
735 end if;
736 END OKC_RUL_MERGE_PARTY_ID3;
737 --
738 -- Merge Procedure for OKC_K_ITEMS
739 --
740 PROCEDURE OKC_CIM_MERGE_PARTY (
741 p_entity_name IN VARCHAR2,
742 p_from_id IN NUMBER,
743 x_to_id OUT NOCOPY NUMBER,
744 p_from_fk_id IN NUMBER,
745 p_to_fk_id IN NUMBER,
746 p_parent_entity_name IN VARCHAR2,
747 p_batch_id IN NUMBER,
748 p_batch_party_id IN NUMBER,
749 x_return_status OUT NOCOPY VARCHAR2)
750 IS
751 --
752 l_merge_reason_code VARCHAR2(30);
753 l_api_name VARCHAR2(30) := 'OKC_CIM_MERGE_PARTY';
754 l_count NUMBER(10) := 0;
755 l_object_user_code VARCHAR2(20);
756 --
757 BEGIN
758 --
759 fnd_file.put_line(fnd_file.log, 'OKC_PARTY_MERGE_PKG.OKC_CIM_MERGE_PARTY');
760
761 fnd_file.put_line(fnd_file.log, '****** PARAMETERS ****** ');
762 fnd_file.put_line(fnd_file.log, 'p_entity_name : '||p_entity_name);
763 fnd_file.put_line(fnd_file.log, 'p_from_id : '||p_from_id);
764 fnd_file.put_line(fnd_file.log, 'p_from_fk_id : '||p_from_fk_id);
765 fnd_file.put_line(fnd_file.log, 'p_to_fk_id : '||p_to_fk_id);
766 fnd_file.put_line(fnd_file.log, 'p_parent_entity_name : '||p_parent_entity_name);
767 fnd_file.put_line(fnd_file.log, 'p_batch_id : '||p_batch_id);
768 fnd_file.put_line(fnd_file.log, 'p_batch_party_id : '||p_batch_party_id);
769 --
770 arp_message.set_line('OKC_PARTY_MERGE_PKG.OKC_CIM_MERGE_PARTY()+');
771
772 x_return_status := FND_API.G_RET_STS_SUCCESS;
773
774 --
775 --
776 if p_parent_entity_name = 'HZ_PARTIES' then l_object_user_code := c_party;
777 elsif p_parent_entity_name = 'HZ_PARTY_SITE_USES' then l_object_user_code := c_p_site_use;
778 elsif p_parent_entity_name = 'HZ_PARTY_SITES' then l_object_user_code := c_p_site;
779 end if;
780 --
781
782 fnd_file.put_line(fnd_file.log, 'l_object_user_code : '||l_object_user_code);
783
784
785 --
786 select merge_reason_code
787 into l_merge_reason_code
788 from hz_merge_batch
789 where batch_id = p_batch_id;
790
791 if l_merge_reason_code = 'DUPLICATE' then
792 -- if reason code is duplicate then allow the party merge to happen without
793 -- any validations.
794 null;
795 else
796 -- if there are any validations to be done, include it in this section
797 null;
798 end if;
799
800 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
801 -- needs to be done. Set Merged To Id is same as Merged From Id and return
802
803 if p_from_fk_id = p_to_fk_id then
804 x_to_id := p_from_id;
805 return;
806 end if;
807
808 -- If the parent has changed(ie. Parent is getting merged) then transfer the
809 -- dependent record to the new parent. Before transferring check if a similar
810 -- dependent record exists on the new parent. If a duplicate exists then do
811 -- not transfer and return the id of the duplicate record as the Merged To Id
812
813 if p_from_fk_id <> p_to_fk_id then
814 begin
815 arp_message.set_name('AR','AR_UPDATING_TABLE');
816 arp_message.set_token('TABLE_NAME','OKC_K_ITEMS',FALSE);
817 fnd_file.put_line(fnd_file.log, 'Updating Table okc_k_items');
818 --
819 --
820 UPDATE okc_k_items cim
821 SET cim.object1_id1 = p_to_fk_id
822 ,cim.object_version_number = cim.object_version_number + 1
823 ,cim.last_update_date = SYSDATE
824 ,cim.last_updated_by = arp_standard.profile.user_id
825 ,cim.last_update_login = arp_standard.profile.last_update_login
826 WHERE cim.object1_id1 = p_from_fk_id
827 AND cim.jtot_object1_code IN (SELECT ojt.object_code
828 FROM jtf_objects_b ojt
829 ,jtf_object_usages oue
830 WHERE ojt.object_code = oue.object_code
831 AND oue.object_user_code = l_object_user_code)
832 ;
833 --
834 --
835 l_count := sql%rowcount;
836
837 fnd_file.put_line(fnd_file.log, 'No of Rows Updated : '||l_count);
838 arp_message.set_name('AR','AR_ROWS_UPDATED');
839 arp_message.set_token('NUM_ROWS',to_char(l_count));
840 --
841 exception
842 when others then
843 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
844 --
845 fnd_file.put_line(fnd_file.log,(g_proc_name || '.' || l_api_name ||
846 'OKC_K_PARTY_ROLES for = ' ||l_object_user_code||'-'|| p_from_id));
847 --
848 fnd_file.put_line(fnd_file.log, g_proc_name||'.'||l_api_name||':'||sqlerrm);
849 x_return_status := FND_API.G_RET_STS_ERROR;
850 end;
851 end if;
852 END OKC_CIM_MERGE_PARTY;
853 --
854 -- Merge procedure for OKC_CONTACTS
855 --
856 PROCEDURE OKC_CTC_MERGE_PARTY (
857 p_entity_name IN VARCHAR2,
858 p_from_id IN NUMBER,
859 x_to_id OUT NOCOPY NUMBER,
860 p_from_fk_id IN NUMBER,
861 p_to_fk_id IN NUMBER,
862 p_parent_entity_name IN VARCHAR2,
863 p_batch_id IN NUMBER,
864 p_batch_party_id IN NUMBER,
865 x_return_status OUT NOCOPY VARCHAR2)
866 IS
867 --
868 l_merge_reason_code VARCHAR2(30);
869 l_api_name VARCHAR2(30) := 'OKC_CTC_MERGE_PARTY';
870 l_count NUMBER(10) := 0;
871 l_object_user_code VARCHAR2(20);
872 --
873 BEGIN
874 --
875 fnd_file.put_line(fnd_file.log, 'OKC_PARTY_MERGE_PKG.OKC_CTC_MERGE_PARTY');
876
877 fnd_file.put_line(fnd_file.log, '****** PARAMETERS ****** ');
878 fnd_file.put_line(fnd_file.log, 'p_entity_name : '||p_entity_name);
879 fnd_file.put_line(fnd_file.log, 'p_from_id : '||p_from_id);
880 fnd_file.put_line(fnd_file.log, 'p_from_fk_id : '||p_from_fk_id);
881 fnd_file.put_line(fnd_file.log, 'p_to_fk_id : '||p_to_fk_id);
882 fnd_file.put_line(fnd_file.log, 'p_parent_entity_name : '||p_parent_entity_name);
883 fnd_file.put_line(fnd_file.log, 'p_batch_id : '||p_batch_id);
884 fnd_file.put_line(fnd_file.log, 'p_batch_party_id : '||p_batch_party_id);
885 --
886 arp_message.set_line('OKC_PARTY_MERGE_PKG.OKC_CTC_MERGE_PARTY()+');
887
888 x_return_status := FND_API.G_RET_STS_SUCCESS;
889
890 --
891 --
892 if p_parent_entity_name = 'HZ_PARTIES' then l_object_user_code := c_contact;
893 end if;
894 --
895
896 fnd_file.put_line(fnd_file.log, 'l_object_user_code : '||l_object_user_code);
897
898
899 --
900 select merge_reason_code
901 into l_merge_reason_code
902 from hz_merge_batch
903 where batch_id = p_batch_id;
904
905 if l_merge_reason_code = 'DUPLICATE' then
906 -- if reason code is duplicate then allow the party merge to happen without
907 -- any validations.
908 null;
909 else
910 -- if there are any validations to be done, include it in this section
911 null;
912 end if;
913
914 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
915 -- needs to be done. Set Merged To Id is same as Merged From Id and return
916
917 if p_from_fk_id = p_to_fk_id then
918 x_to_id := p_from_id;
919 return;
920 end if;
921
922 -- If the parent has changed(ie. Parent is getting merged) then transfer the
923 -- dependent record to the new parent. Before transferring check if a similar
924 -- dependent record exists on the new parent. If a duplicate exists then do
925 -- not transfer and return the id of the duplicate record as the Merged To Id
926
927 if p_from_fk_id <> p_to_fk_id then
928 begin
929 arp_message.set_name('AR','AR_UPDATING_TABLE');
930 arp_message.set_token('TABLE_NAME','OKC_CONTACTS',FALSE);
931 fnd_file.put_line(fnd_file.log, 'Updating Table okc_contacts');
932 --
933 --
934 UPDATE okc_contacts ctc
935 SET ctc.object1_id1 = p_to_fk_id
936 ,ctc.object_version_number = ctc.object_version_number + 1
937 ,ctc.last_update_date = SYSDATE
938 ,ctc.last_updated_by = arp_standard.profile.user_id
939 ,ctc.last_update_login = arp_standard.profile.last_update_login
940 WHERE ctc.object1_id1 = to_char(p_from_fk_id)
941 AND ctc.jtot_object1_code IN (SELECT ojt.object_code
942 FROM jtf_objects_b ojt
943 ,jtf_object_usages oue
944 WHERE ojt.object_code = oue.object_code
945 AND oue.object_user_code = l_object_user_code)
946 ;
947 --
948 -- Following where clause removed after update from TCA (02/09/2001)
949 --
950 --WHERE ctc.object1_id1 = p_from_fk_id
951 --AND ctc.jtot_object1_code IN (SELECT ojt.object_code
952 -- FROM jtf_objects_b ojt
953 -- ,jtf_object_usages oue
954 -- WHERE ojt.object_code = oue.object_code
955 -- AND oue.object_user_code = l_object_user_code)
956 --
957 l_count := sql%rowcount;
958
959 fnd_file.put_line(fnd_file.log, 'No of Rows Updated : '||l_count);
960 arp_message.set_name('AR','AR_ROWS_UPDATED');
961 arp_message.set_token('NUM_ROWS',to_char(l_count));
962 --
963 exception
964 when others then
965 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
966 --
967 fnd_file.put_line(fnd_file.log,(g_proc_name || '.' || l_api_name ||
968 'OKC_K_PARTY_ROLES for = ' ||l_object_user_code||'-'|| p_from_id));
969 --
970 fnd_file.put_line(fnd_file.log, g_proc_name||'.'||l_api_name||':'||sqlerrm);
971 x_return_status := FND_API.G_RET_STS_ERROR;
972 end;
973 end if;
974 END OKC_CTC_MERGE_PARTY;
975 --
976 --
977 END; -- Package Body OKC_PARTY_MERGE_PUB
[Home] [Help]