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