[Home] [Help]
PACKAGE BODY: APPS.OKC_HZ_MERGE_PUB
Source
1 Package Body OKC_HZ_MERGE_PUB AS
2 /* $Header: OKCPMRGB.pls 120.0.12010000.2 2008/10/24 08:01:31 ssreekum ship $ */
3 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4 --
5 -- Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- Start of Comments
9 -- API Name :OKC_HZ_MERGE_PUB
10 -- Type :Public
11 -- Purpose :Manage customer and party merges
12 --
13 -- Modification History
14 -- 07-Dec-00 mconnors created
15 -- 10-May-02 rbhandar Modified Account merge logic. Accounts can be merged
16 -- under two different parties when a predefined set of
17 -- conditions were satisfied
18 -- 14-JAN-03 rbhandar Bug 2446945 - Auditing during account merge
19 -- 04-01-03 rbhandar Bug 2446945 Added who columns and action_flag to
20 -- hz_customer_merge_log insert statement
21 -- 29-MAY-04 amhanda Bug 3555739 Added code in party_merge,account_merge and
22 -- account_site_merge procedure to take care of 11.5.10 rule
23 -- migration changes for OKS
24 -- 01-NOV-04 who Bug 3950642 Added code to take care of site use merge for OKE
25 -- in the OKC_K_PARTY_ROLES tables.
26 -- 19-JAN-05 chkrishn Bug 4105272 Added insert statement to OKC_K_VERS_NUMBERS_H
27 -- in procedure PARTY_MERGE
28 -- 27-FEB-07 harchand Bug 6861077. Added the code to the union query of cursor c_account_count
29 -- NOTES
30 -- Merging Rules:
31 -- Account merges across parties, when the "duplicate" or source party
32 -- is referenced in a contract are not allowed. (This logic has been
33 -- modified. Merge is possible even if the source party is referenced
34 -- in a contract)
35 --
36 -- Merges where the duplicate party is not referenced in a contract are
37 -- processed (account, site, site use).
38 --
39 -- Account merges within the same party are processed (account, site,
40 -- site use).
41 --
42 -- Site merges in the same account are processed (site, site use).
43 --
44 -- When merging accounts, customer account ids are looked for in:
45 -- OKC_K_PARTY_ROLES
46 -- OKC_RULES
47 -- OKC_K_ITEMS
48 -- For customer site merges, cust_acct_site_ids are looked for in:
49 -- OKC_RULES
50 -- OKC_K_ITEMS
51 -- For customer site use merges, site_use_ids are looked for in:
52 -- OKC_RULES
53 -- OKC_K_ITEMS
54 -- OKC_K_PARTY_ROLES (bug 3950642)
55 --
56 -- JTF Objects:
57 -- The merge depends upon the proper usages being set for the JTF objects used
58 -- as party roles, rules, and items. These usages are as follows:
59 -- OKX_PARTY This object is based on a view which returns the
60 -- party_id as id1.
61 -- OKX_P_SITE This object is based on a view which returns
62 -- party_site_id as id1.
63 -- OKX_P_SITE_USE This object is based on a view which returns
64 -- party_site_use_id as id1.
65 -- OKX_ACCOUNT This object is based on a view which returns
66 -- cust_account_id as id1.
67 -- OKX_C_SITE This object is based on a view which returns
68 -- cust_acct_site_id as id1.
69 -- OKX_C_SITE_USE This object is based on a view which returns
70 -- site_use_id as id1.
71 -- The usages are how the merge determines which jtot_object_codes are candidates
72 -- for the different types of merges.
73 --
74 --
75 -- End of comments
76
77
78 -- Global constants
79 c_party CONSTANT VARCHAR2(20) := 'OKX_PARTY';
80 c_p_site CONSTANT VARCHAR2(20) := 'OKX_P_SITE';
81 c_p_site_use CONSTANT VARCHAR2(20) := 'OKX_P_SITE_USE';
82 c_account CONSTANT VARCHAR2(20) := 'OKX_ACCOUNT';
83 c_c_site CONSTANT VARCHAR2(20) := 'OKX_C_SITE';
84 c_c_site_use CONSTANT VARCHAR2(20) := 'OKX_C_SITE_USE';
85 -- New profile value for customer merge log purpose
86 l_profile_val VARCHAR2(30) := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
87
88 --
89 -- routine to lock tables when process mode = 'LOCK'
90 -- if table cannot be locked, goes back to caller as exception
91 PROCEDURE lock_tables (req_id IN NUMBER
92 ,set_number IN NUMBER) IS
93 --
94 -- cursors to lock tables
95 --
96 CURSOR c_lock_kpr(b_object_use VARCHAR2) IS
97 SELECT 1
98 FROM okc_k_party_roles_b kpr
99 WHERE kpr.jtot_object1_code IN (SELECT ojt.object_code
100 FROM jtf_objects_b ojt
101 ,jtf_object_usages oue
102 WHERE ojt.object_code = oue.object_code
103 AND oue.object_user_code = b_object_use
104 )
105 AND kpr.object1_id1 IN (SELECT to_char(cme.duplicate_id)
106 FROM ra_customer_merges cme
107 WHERE cme.process_flag = 'N'
108 AND cme.request_id = req_id
109 AND cme.set_number = set_number
110 )
111 FOR UPDATE NOWAIT;
112
113
114
115
116 CURSOR c_lock_rle1(b_object_use VARCHAR2) IS
117 SELECT 1
118 FROM okc_rules_b rle
119 WHERE rle.jtot_object1_code IN (SELECT ojt.object_code
120 FROM jtf_objects_b ojt
121 ,jtf_object_usages oue
122 WHERE ojt.object_code = oue.object_code
123 AND oue.object_user_code = b_object_use
124 )
125 AND rle.object1_id1 IN (SELECT to_char(cme.duplicate_id)
126 FROM ra_customer_merges cme
127 WHERE cme.process_flag = 'N'
128 AND cme.request_id = req_id
129 AND cme.set_number = set_number
130 )
131 FOR UPDATE NOWAIT;
132
133 CURSOR c_lock_rle2(b_object_use VARCHAR2) IS
134 SELECT 1
135 FROM okc_rules_b rle
136 WHERE rle.jtot_object2_code IN (SELECT ojt.object_code
137 FROM jtf_objects_b ojt
138 ,jtf_object_usages oue
139 WHERE ojt.object_code = oue.object_code
140 AND oue.object_user_code = b_object_use
141 )
142 AND rle.object2_id1 IN (SELECT to_char(cme.duplicate_id)
143 FROM ra_customer_merges cme
144 WHERE cme.process_flag = 'N'
145 AND cme.request_id = req_id
146 AND cme.set_number = set_number
147 )
148 FOR UPDATE NOWAIT;
149
150 CURSOR c_lock_rle3(b_object_use VARCHAR2) IS
151 SELECT 1
152 FROM okc_rules_b rle
153 WHERE rle.jtot_object3_code IN (SELECT ojt.object_code
154 FROM jtf_objects_b ojt
155 ,jtf_object_usages oue
156 WHERE ojt.object_code = oue.object_code
157 AND oue.object_user_code = b_object_use
158 )
159 AND rle.object3_id1 IN (SELECT to_char(cme.duplicate_id)
160 FROM ra_customer_merges cme
161 WHERE cme.process_flag = 'N'
162 AND cme.request_id = req_id
163 AND cme.set_number = set_number
164 )
165 FOR UPDATE NOWAIT;
166
167 CURSOR c_lock_cim(b_object_use VARCHAR2) IS
168 SELECT 1
169 FROM okc_k_items cim
170 WHERE cim.jtot_object1_code IN (SELECT ojt.object_code
171 FROM jtf_objects_b ojt
172 ,jtf_object_usages oue
173 WHERE ojt.object_code = oue.object_code
174 AND oue.object_user_code = b_object_use
175 )
176 AND cim.object1_id1 IN (SELECT to_char(cme.duplicate_id)
177 FROM ra_customer_merges cme
178 WHERE cme.process_flag = 'N'
179 AND cme.request_id = req_id
180 AND cme.set_number = set_number
181 )
182 FOR UPDATE NOWAIT;
183
184 BEGIN
185 arp_message.set_line('OKC_HZ_MERGE_PUB.LOCK_TABLES()+');
186
187 -- party roles for accounts
188 arp_message.set_name('AR','AR_LOCKING_TABLE');
189 arp_message.set_token('TABLE_NAME','OKC_K_PARTY_ROLES_B',FALSE);
190 arp_message.set_line('Locking for accounts');
191 open c_lock_kpr(c_account);
192 close c_lock_kpr;
193
194 arp_message.set_line('Locking for site uses');
195 open c_lock_kpr(c_c_site_use); -- added for bug 3950642
196 close c_lock_kpr;
197
198 -- rules for accounts
199 arp_message.set_name('AR','AR_LOCKING_TABLE');
200 arp_message.set_token('TABLE_NAME','OKC_RULES_B',FALSE);
201 arp_message.set_line('Locking for accounts');
202 open c_lock_rle1(c_account);
203 close c_lock_rle1;
204 open c_lock_rle2(c_account);
205 close c_lock_rle2;
206 open c_lock_rle3(c_account);
207 close c_lock_rle3;
208
209 -- rules for sites
210 arp_message.set_name('AR','AR_LOCKING_TABLE');
211 arp_message.set_token('TABLE_NAME','OKC_RULES_B',FALSE);
212 arp_message.set_line('Locking for sites');
213 open c_lock_rle1(c_c_site);
214 close c_lock_rle1;
215 open c_lock_rle2(c_c_site);
216 close c_lock_rle2;
217 open c_lock_rle3(c_c_site);
218 close c_lock_rle3;
219
220 -- rules for site uses
221 arp_message.set_name('AR','AR_LOCKING_TABLE');
222 arp_message.set_token('TABLE_NAME','OKC_RULES_B.OBJECT3_ID1',FALSE);
223 arp_message.set_line('Locking for site uses');
224 open c_lock_rle1(c_c_site_use);
225 close c_lock_rle1;
226 open c_lock_rle2(c_c_site_use);
227 close c_lock_rle2;
228 open c_lock_rle3(c_c_site_use);
229 close c_lock_rle3;
230
231 -- items for accounts (covered level in OKS)
232 arp_message.set_name('AR','AR_LOCKING_TABLE');
233 arp_message.set_token('TABLE_NAME','OKC_K_ITEMS',FALSE);
234 arp_message.set_line('Locking for accounts');
235 open c_lock_cim(c_account);
236 close c_lock_cim;
237
238 -- items for sites
239 arp_message.set_name('AR','AR_LOCKING_TABLE');
240 arp_message.set_token('TABLE_NAME','OKC_K_ITEMS',FALSE);
241 arp_message.set_line('Locking for sites');
242 open c_lock_cim(c_c_site);
243 close c_lock_cim;
244
245 -- items for site uses
246 arp_message.set_name('AR','AR_LOCKING_TABLE');
247 arp_message.set_token('TABLE_NAME','OKC_K_ITEMS',FALSE);
248 arp_message.set_line('Locking for site uses');
249 open c_lock_cim(c_c_site_use);
250 close c_lock_cim;
251
252 arp_message.set_line('OKC_HZ_MERGE_PUB.LOCK_TABLES()-');
253 EXCEPTION
254 WHEN TIMEOUT_ON_RESOURCE THEN
255 arp_message.set_line('Could not obtain lock for records');
256 raise;
257 END; -- lock_tables
258
259 --
260 -- Updating the contract tables in case the source party has a contract
261 -- The source party should have only one account and that account should
262 -- be the merged account. This is when merging two accounts under different
263 -- parties
264 --
265
266 PROCEDURE party_merge(req_id IN NUMBER
267 ,set_number IN NUMBER
268 ,l_source_party_id IN NUMBER
269 ,l_target_party_id IN NUMBER
270 ,l_duplicate_id IN NUMBER) IS
271
272 --
273 -- cursor to find if any contract is with the party of the
274 -- merged account
275 --
276 CURSOR c_cpr (b_party_id NUMBER) IS
277 SELECT kpr.dnz_chr_id
278 FROM okc_k_party_roles_b kpr
279 WHERE kpr.jtot_object1_code IN (SELECT ojt.object_code
280 FROM jtf_objects_b ojt
281 ,jtf_object_usages oue
282 WHERE ojt.object_code = oue.object_code
283 AND oue.object_user_code = c_party
284 )
285 AND kpr.object1_id1 = to_char(b_party_id)
286 ;
287
288 --
289 -- cursor to get all the acounts from rules for a particular
290 -- contract
291 -- Bug 3555739 modified the cursor to check for account info. in okc_k_lines_b and
292 -- okc_k_headers_b also
293
294 CURSOR c_rules (b_dnz_chr_id NUMBER) IS
295 select object1_id1, object2_id1, object3_id1
296 from okc_rules_b
297 where dnz_chr_id = b_dnz_chr_id
298 and rule_information_category IN ('BTO', 'STO')
299 union
300 select to_char(bill_to_site_use_id), to_char(ship_to_site_use_id), to_char(cust_acct_id)
301 from okc_k_headers_b
302 where id = b_dnz_chr_id
303 union
304 select to_char(bill_to_site_use_id), to_char(ship_to_site_use_id), to_char(cust_acct_id)
305 from okc_k_lines_b
306 where chr_id = b_dnz_chr_id ;
307
308 --
309 -- cursor to check whether the source party has more than one
310 -- account in the contract
311 -- Bug 3555739 modified the cursor to check for account info. in okc_k_lines_b and
312 -- okc_k_headers_b also
313 CURSOR c_account_count (b_chr_id NUMBER) IS
314 select count(*)
315 from (select okr1.object1_id1 from okc_rules_b okr1
316 where okr1.object1_id1 in (
317 select TO_CHAR(cust_account_id) from hz_cust_accounts where party_id = l_source_party_id
318 ) and okr1.dnz_chr_id = b_chr_id
319 and okr1.rule_information_category IN ('BTO', 'STO')
320 union
321 select okr2.object2_id1 from okc_rules_b okr2
322 where okr2.object2_id1 in (
323 select TO_CHAR(cust_account_id) from hz_cust_accounts where party_id = l_source_party_id
324 ) and okr2.dnz_chr_id = b_chr_id
325 and okr2.rule_information_category IN ('BTO', 'STO')
326 union
327 select okr3.object3_id1 from okc_rules_b okr3
328 where okr3.object3_id1 in (
329 select TO_CHAR(cust_account_id) from hz_cust_accounts where party_id = l_source_party_id
330 ) and okr3.dnz_chr_id = b_chr_id
331 and okr3.rule_information_category IN ('BTO', 'STO')
332 union
333 (select to_char(cust_account_id) from OKX_CUST_SITE_USES_V
334 where id1 IN (select bill_to_site_use_id
335 from okc_k_headers_b where id = b_chr_id)
336 and party_id = l_source_party_id
337 and SITE_USE_CODE = 'BILL_TO')
338 union
339 (select to_char(cust_account_id) from OKX_CUST_SITE_USES_V
340 where id1 IN (select ship_to_site_use_id
341 from okc_k_headers_b where id = b_chr_id )
342 and party_id =l_source_party_id
343 and SITE_USE_CODE = 'SHIP_TO')
344 union
345 (select to_char(cust_account_id) from OKX_CUST_SITE_USES_V
346 where id1 IN (select bill_to_site_use_id
347 from okc_k_lines_b where dnz_chr_id = b_chr_id)
348 and party_id = l_source_party_id
349 and SITE_USE_CODE = 'BILL_TO')
350 union
351 (select to_char(cust_account_id) from OKX_CUST_SITE_USES_V
352 where id1 IN (select ship_to_site_use_id
353 from okc_k_lines_b where dnz_chr_id = b_chr_id )
354 and party_id =l_source_party_id
355 and SITE_USE_CODE = 'SHIP_TO')
356 union
357 (select to_char(cust_account_id) from hz_cust_accounts
361 and party_id =l_source_party_id)
358 where cust_account_id IN (select cust_acct_id
359 from okc_k_lines_b where dnz_chr_id = b_chr_id )
360 -- For Bug# 6861077
362 union
363 (select to_char(cust_account_id) from hz_cust_accounts
364 where cust_account_id IN (select cust_acct_id
365 from okc_k_headers_b where id = b_chr_id )
366 -- Changes for Bug# 6861077 Ends
367 and party_id =l_source_party_id));
368
369 --
370 -- cursor to get the contract number and modifier for log purpose
371 --
372 CURSOR c_header_info (b_chr_id NUMBER) IS
373 SELECT contract_number, contract_number_modifier
374 FROM okc_k_headers_b
375 WHERE id=b_chr_id;
376
377 CURSOR c_party_id_log(b_chr_id NUMBER) IS
378 SELECT kpr.id
379 FROM okc_k_party_roles_b kpr
380 WHERE kpr.object1_id1 = l_target_party_id
381 AND kpr.dnz_chr_id = b_chr_id;
382
383 l_count NUMBER(10) := 0;
384 l_object_user_code VARCHAR2(20);
385 l_chr_id okc_k_party_roles_b.dnz_chr_id%type;
386 l_object1_id1 okc_rules_b.object1_id1%type;
387 l_object2_id1 okc_rules_b.object2_id1%type;
388 l_object3_id1 okc_rules_b.object3_id1%type;
389 l_account_count NUMBER(10) := 0;
390 l_contract_number okc_k_headers_b.contract_number%type;
391 l_contract_number_modifier okc_k_headers_b.contract_number_modifier%type;
392 l_status VARCHAR2(1);
393 l_error_msg VARCHAR2(2000);
394 l_log_party_id NUMBER;
395
396 l_merge_not_allowed_excp EXCEPTION;
397 BEGIN
398 arp_message.set_line('OKC_HZ_MERGE_PUB.PARTY_MERGE()+');
399 arp_message.set_line('At the begining of Party Merge procedure');
400
401 --
402 -- initialize the status to check whether any contract has more than one
403 -- account for the source party and one of the accounts is merged acount.
404 -- Merge should fail in the above scenario
405 --
406 l_status := 'Y';
407
408 OPEN c_cpr (l_source_party_id);
409 LOOP
410 FETCH c_cpr INTO l_chr_id;
411 EXIT WHEN c_cpr%NOTFOUND;
412 OPEN c_account_count(l_chr_id);
413 FETCH c_account_count INTO l_account_count;
414
415 IF (l_account_count > 1) THEN
416 OPEN c_rules(l_chr_id);
417 LOOP
418 FETCH c_rules INTO l_object1_id1, l_object2_id1, l_object3_id1;
419 EXIT WHEN c_rules%NOTFOUND;
420 IF ( (TO_CHAR(l_duplicate_id) = nvl(l_object1_id1,'*')) OR (TO_CHAR(l_duplicate_id) = nvl(l_object2_id1, '*')) OR
421 (TO_CHAR(l_duplicate_id) = nvl(l_object3_id1, '*')) ) THEN
422 l_status := 'N'; -- contract exists with more than one account for the source party
423 -- and one of the accounts is the merged account
424 OPEN c_header_info(l_chr_id);
425 FETCH c_header_info INTO l_contract_number, l_contract_number_modifier;
426 CLOSE c_header_info;
427 arp_message.set_line('Contract ' || l_contract_number || ' should be manually updated');
428
429 EXIT;
430 END IF;
431 END LOOP; -- cursor c_rules
432 CLOSE c_rules;
433 END IF;
434 CLOSE c_account_count;
435 END LOOP;
436 CLOSE c_cpr;
437
438 -- error message if any contract has more than one account and one of the
439 -- accounts is the merged account. Merge will fail.
440
441 IF l_status <> 'Y' THEN
442 RAISE l_merge_not_allowed_excp; -- do not allow merge
443 END IF;
444
445 OPEN c_cpr (l_source_party_id);
446 LOOP
447 FETCH c_cpr INTO l_chr_id;
448 EXIT WHEN c_cpr%NOTFOUND;
449 OPEN c_account_count(l_chr_id);
450 FETCH c_account_count INTO l_account_count;
451
452 IF (l_account_count = 1) THEN
453 OPEN c_rules(l_chr_id);
454 LOOP
455 FETCH c_rules INTO l_object1_id1, l_object2_id1, l_object3_id1;
456 EXIT WHEN c_rules%NOTFOUND;
457 IF ( (TO_CHAR(l_duplicate_id) = nvl(l_object1_id1,'*')) OR (TO_CHAR(l_duplicate_id) = nvl(l_object2_id1, '*')) OR
458 (TO_CHAR(l_duplicate_id) = nvl(l_object3_id1, '*')) ) THEN
459
460 -- updating OKC_K_PARTY_ROLES_B
461 UPDATE okc_k_party_roles_b kpr
462 SET kpr.object1_id1 = l_target_party_id
463 ,kpr.object_version_number = kpr.object_version_number + 1
464 ,kpr.last_update_date = SYSDATE
465 ,kpr.last_updated_by = arp_standard.profile.user_id
466 ,kpr.last_update_login = arp_standard.profile.last_update_login
467 WHERE kpr.object1_id1 = l_source_party_id
468 AND kpr.dnz_chr_id = l_chr_id;
469
470 l_count := sql%rowcount;
471 IF l_count > 0 THEN
472 OPEN c_header_info(l_chr_id);
473 FETCH c_header_info INTO l_contract_number, l_contract_number_modifier;
474 CLOSE c_header_info;
475 arp_message.set_line('Contract ' || l_contract_number || ' is updated');
476
477 OPEN c_party_id_log(l_chr_id);
481 -- Insert into log table
478 FETCH c_party_id_log INTO l_log_party_id;
479 CLOSE c_party_id_log;
480
482 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
483 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
484 MERGE_LOG_ID,
485 TABLE_NAME,
486 PRIMARY_KEY_ID1
487 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
488 'OKC_K_PARTY_ROLES_B',
489 l_log_party_id
490 FROM DUAL;
491
492 UPDATE HZ_CUSTOMER_MERGE_LOG hz
493 SET hz.MERGE_HEADER_ID = (SELECT distinct CUSTOMER_MERGE_HEADER_ID
494 FROM ra_customer_merges rcm
495 WHERE rcm.request_id = req_id
496 and rcm.set_number = set_number
497 and rcm.process_flag = 'N')
498 ,hz.VCHAR_COL1_ORIG = l_source_party_id
499 ,hz.VCHAR_COL1_NEW = l_target_party_id
500 ,hz.REQUEST_ID = req_id
501 ,hz.CREATED_BY = hz_utility_pub.CREATED_BY
502 ,hz.CREATION_DATE = hz_utility_pub.CREATION_DATE
503 ,hz.LAST_UPDATE_LOGIN = hz_utility_pub.LAST_UPDATE_LOGIN
504 ,hz.LAST_UPDATE_DATE = hz_utility_pub.LAST_UPDATE_DATE
505 ,hz.LAST_UPDATED_BY = hz_utility_pub.LAST_UPDATED_BY
506 ,hz.ACTION_FLAG = 'U'
507 WHERE hz.PRIMARY_KEY_ID1 = l_log_party_id;
508 END IF;
509 -- Fix for bug 4105272 Insert into okc_k_vers_numbers_h
510 INSERT INTO OKC_K_VERS_NUMBERS_H(
511 chr_id,
512 major_version,
513 minor_version,
514 object_version_number,
515 created_by,
516 creation_date,
517 last_updated_by,
518 last_update_date,
519 last_update_login)
520 (SELECT
521 chr_id,
522 major_version,
523 minor_version,
524 object_version_number,
525 created_by,
526 creation_date,
527 last_updated_by,
528 last_update_date,
529 last_update_login
530 FROM OKC_K_VERS_NUMBERS
531 WHERE chr_id = l_chr_id);
532 -- Updating okc_k_vers_numbers for Bug 3553330/3215231/3224957
533 UPDATE okc_k_vers_numbers ver
534 SET ver.minor_version = ver.minor_version + 1
535 ,ver.object_version_number = ver.object_version_number + 1
536 ,ver.last_update_date = SYSDATE
537 ,ver.last_updated_by = arp_standard.profile.user_id
538 ,ver.last_update_login = arp_standard.profile.last_update_login
539 WHERE chr_id = l_chr_id;
540 -- Updating okc_k_vers_numbers for Bug 3553330/3215231/3224957
541
542 END IF;
543
544 EXIT;
545 END IF;
546 END LOOP; -- cursor c_rules
547 CLOSE c_rules;
548 END IF;
549 CLOSE c_account_count;
550 END LOOP; -- cursor c_cpr
551 CLOSE c_cpr;
552
553 arp_message.set_line('At the end of Party Merge procedure');
554 arp_message.set_line('OKC_HZ_MERGE_PUB.PARTY_MERGE()-');
555
556 EXCEPTION
557 WHEN l_merge_not_allowed_excp THEN
558 arp_message.set_line('Contract exists for duplicate party with more than one account, merge cannot proceed');
559 arp_message.set_line('Please update the above mentioned contracts and run customer merge again');
560 arp_message.set_error('OKC_HZ_MERGE_PUB.MERGE_ACCOUNT');
561 RAISE;
562
563 WHEN others THEN
564 l_error_msg := substr(SQLERRM,1,70);
565 arp_message.set_error('OKC_HZ_MERGE_PUB.MERGE_ACCOUNT', l_error_msg);
566 RAISE;
567
568 END; -- party_merge
569
570
571 --
572 -- sub routine to merge accounts
573 -- exceptions are unhandled, sent back to caller
574 --
575 PROCEDURE account_merge(req_id IN NUMBER
576 ,set_number IN NUMBER) IS
577
578 l_count NUMBER;
579
580 BEGIN
581 arp_message.set_line('OKC_HZ_MERGE_PUB.ACCOUNT_MERGE()+');
582
583 -- contract party roles
584 -- Insert into log table
585 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
586 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
587 MERGE_LOG_ID,
588 TABLE_NAME,
589 MERGE_HEADER_ID,
590 PRIMARY_KEY_ID1,
591 VCHAR_COL1_ORIG,
592 VCHAR_COL1_NEW,
593 REQUEST_ID,
594 ACTION_FLAG,
595 CREATED_BY,
596 CREATION_DATE,
597 LAST_UPDATE_LOGIN,
598 LAST_UPDATE_DATE,
599 LAST_UPDATED_BY
600 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
601 'OKC_K_PARTY_ROLES_B',
602 CUSTOMER_MERGE_HEADER_ID,
603 kpr.ID,
604 kpr.object1_id1,
605 to_char(rcm.customer_id),
606 req_id,
610 hz_utility_pub.LAST_UPDATE_LOGIN,
607 'U',
608 hz_utility_pub.CREATED_BY,
609 hz_utility_pub.CREATION_DATE,
611 hz_utility_pub.LAST_UPDATE_DATE,
612 hz_utility_pub.LAST_UPDATED_BY
613 FROM OKC_K_PARTY_ROLES_B kpr, ra_customer_merges rcm
614 WHERE (
615 kpr.object1_id1 = to_char(rcm.duplicate_id)
616 ) AND rcm.process_flag = 'N'
617 AND rcm.request_id = req_id
618 AND rcm.set_number = set_number
619 AND kpr.object1_id1 IN (SELECT to_char(rcm.duplicate_id)
620 FROM ra_customer_merges rcm
621 WHERE rcm.process_flag = 'N'
622 AND rcm.request_id = req_id
623 AND rcm.set_number = set_number)
624 AND kpr.jtot_object1_code IN (SELECT ojt.object_code
625 FROM jtf_objects_b ojt
626 ,jtf_object_usages oue
627 WHERE ojt.object_code = oue.object_code
628 AND oue.object_user_code = c_account);
629 End If;
630
631 arp_message.set_name('AR','AR_UPDATING_TABLE');
632 arp_message.set_token('TABLE_NAME','OKC_K_PARTY_ROLES',FALSE);
633 UPDATE okc_k_party_roles_b kpr
634 SET kpr.object1_id1 = (SELECT DISTINCT to_char(rcm.customer_id)
635 FROM ra_customer_merges rcm
636 WHERE kpr.object1_id1 = rcm.duplicate_id
637 AND rcm.process_flag = 'N'
638 AND rcm.request_id = req_id
639 AND rcm.set_number = set_number)
640 ,kpr.object_version_number = kpr.object_version_number + 1
641 ,kpr.last_update_date = SYSDATE
642 ,kpr.last_updated_by = arp_standard.profile.user_id
643 ,kpr.last_update_login = arp_standard.profile.last_update_login
644 WHERE kpr.object1_id1 IN (SELECT to_char(rcm.duplicate_id)
645 FROM ra_customer_merges rcm
646 WHERE rcm.process_flag = 'N'
647 AND rcm.request_id = req_id
648 AND rcm.set_number = set_number)
649 AND kpr.jtot_object1_code IN (SELECT ojt.object_code
650 FROM jtf_objects_b ojt
651 ,jtf_object_usages oue
652 WHERE ojt.object_code = oue.object_code
653 AND oue.object_user_code = c_account)
654 ;
655 l_count := sql%rowcount;
656 arp_message.set_name('AR','AR_ROWS_UPDATED');
657 arp_message.set_token('NUM_ROWS',to_char(l_count));
658
659 -- Rules ID1
660 -- Insert into log table
661 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
662 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
663 MERGE_LOG_ID,
664 TABLE_NAME,
665 MERGE_HEADER_ID,
666 PRIMARY_KEY_ID1,
667 VCHAR_COL1_ORIG,
668 VCHAR_COL1_NEW,
669 REQUEST_ID,
670 ACTION_FLAG,
671 CREATED_BY,
672 CREATION_DATE,
673 LAST_UPDATE_LOGIN,
674 LAST_UPDATE_DATE,
675 LAST_UPDATED_BY
676 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
677 'OKC_RULES_B',
678 CUSTOMER_MERGE_HEADER_ID,
679 rle.ID,
680 rle.object1_id1,
681 to_char(rcm.customer_id),
682 req_id,
683 'U',
684 hz_utility_pub.CREATED_BY,
685 hz_utility_pub.CREATION_DATE,
686 hz_utility_pub.LAST_UPDATE_LOGIN,
687 hz_utility_pub.LAST_UPDATE_DATE,
688 hz_utility_pub.LAST_UPDATED_BY
689 FROM OKC_RULES_B rle, ra_customer_merges rcm
690 WHERE (
691 rle.object1_id1 = to_char(rcm.duplicate_id)
692 ) AND rcm.process_flag = 'N'
693 AND rcm.request_id = req_id
694 AND rcm.set_number = set_number
695 AND rle.object1_id1 IN (SELECT to_char(rcm.duplicate_id)
696 FROM ra_customer_merges rcm
697 WHERE rcm.process_flag = 'N'
698 AND rcm.request_id = req_id
699 AND rcm.set_number = set_number)
700 AND rle.jtot_object1_code IN (SELECT ojt.object_code
701 FROM jtf_objects_b ojt
702 ,jtf_object_usages oue
703 WHERE ojt.object_code = oue.object_code
704 AND oue.object_user_code = c_account);
705 End If;
706
707 arp_message.set_name('AR','AR_UPDATING_TABLE');
708 arp_message.set_token('TABLE_NAME','OKC_RULES_B.OBJECT1_ID1',FALSE);
709 UPDATE okc_rules_b rle
710 SET rle.object1_id1 = (SELECT DISTINCT to_char(rcm.customer_id)
711 FROM ra_customer_merges rcm
712 WHERE rle.object1_id1 = rcm.duplicate_id
713 AND rcm.process_flag = 'N'
714 AND rcm.request_id = req_id
715 AND rcm.set_number = set_number)
716 ,rle.object_version_number = rle.object_version_number + 1
717 ,rle.last_update_date = SYSDATE
721 FROM ra_customer_merges rcm
718 ,rle.last_updated_by = arp_standard.profile.user_id
719 ,rle.last_update_login = arp_standard.profile.last_update_login
720 WHERE rle.object1_id1 IN (SELECT to_char(rcm.duplicate_id)
722 WHERE rcm.process_flag = 'N'
723 AND rcm.request_id = req_id
724 AND rcm.set_number = set_number)
725 AND rle.jtot_object1_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 = c_account)
730 ;
731 l_count := sql%rowcount;
732 arp_message.set_name('AR','AR_ROWS_UPDATED');
733 arp_message.set_token('NUM_ROWS',to_char(l_count));
734
735
736 -- Rules ID2
737 -- Insert into log table
738 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
739 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
740 MERGE_LOG_ID,
741 TABLE_NAME,
742 MERGE_HEADER_ID,
743 PRIMARY_KEY_ID1,
744 VCHAR_COL1_ORIG,
745 VCHAR_COL1_NEW,
746 REQUEST_ID,
747 ACTION_FLAG,
748 CREATED_BY,
749 CREATION_DATE,
750 LAST_UPDATE_LOGIN,
751 LAST_UPDATE_DATE,
752 LAST_UPDATED_BY
753 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
754 'OKC_RULES_B',
755 CUSTOMER_MERGE_HEADER_ID,
756 rle.ID,
757 rle.object2_id1,
758 to_char(rcm.customer_id),
759 req_id,
760 'U',
761 hz_utility_pub.CREATED_BY,
762 hz_utility_pub.CREATION_DATE,
763 hz_utility_pub.LAST_UPDATE_LOGIN,
764 hz_utility_pub.LAST_UPDATE_DATE,
765 hz_utility_pub.LAST_UPDATED_BY
766 FROM OKC_RULES_B rle, ra_customer_merges rcm
767 WHERE (
768 rle.object2_id1 = to_char(rcm.duplicate_id)
769 ) AND rcm.process_flag = 'N'
770 AND rcm.request_id = req_id
771 AND rcm.set_number = set_number
772 AND rle.object2_id1 IN (SELECT to_char(rcm.duplicate_id)
773 FROM ra_customer_merges rcm
774 WHERE rcm.process_flag = 'N'
775 AND rcm.request_id = req_id
776 AND rcm.set_number = set_number)
777 AND rle.jtot_object2_code IN (SELECT ojt.object_code
778 FROM jtf_objects_b ojt
779 ,jtf_object_usages oue
780 WHERE ojt.object_code = oue.object_code
781 AND oue.object_user_code = c_account);
782 End If;
783
784 arp_message.set_name('AR','AR_UPDATING_TABLE');
785 arp_message.set_token('TABLE_NAME','OKC_RULES_B.OBJECT2_ID1',FALSE);
786 UPDATE okc_rules_b rle
787 SET rle.object2_id1 = (SELECT DISTINCT to_char(rcm.customer_id)
788 FROM ra_customer_merges rcm
789 WHERE rle.object2_id1 = rcm.duplicate_id
790 AND rcm.process_flag = 'N'
791 AND rcm.request_id = req_id
792 AND rcm.set_number = set_number)
793 ,rle.object_version_number = rle.object_version_number + 1
794 ,rle.last_update_date = SYSDATE
795 ,rle.last_updated_by = arp_standard.profile.user_id
796 ,rle.last_update_login = arp_standard.profile.last_update_login
797 WHERE rle.object2_id1 IN (SELECT to_char(rcm.duplicate_id)
798 FROM ra_customer_merges rcm
799 WHERE rcm.process_flag = 'N'
800 AND rcm.request_id = req_id
801 AND rcm.set_number = set_number)
802 AND rle.jtot_object2_code IN (SELECT ojt.object_code
803 FROM jtf_objects_b ojt
804 ,jtf_object_usages oue
805 WHERE ojt.object_code = oue.object_code
806 AND oue.object_user_code = c_account)
807 ;
808 l_count := sql%rowcount;
809 arp_message.set_name('AR','AR_ROWS_UPDATED');
810 arp_message.set_token('NUM_ROWS',to_char(l_count));
811
812
813 -- Rules ID3
814 -- Insert into log table
815 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
816 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
817 MERGE_LOG_ID,
818 TABLE_NAME,
819 MERGE_HEADER_ID,
820 PRIMARY_KEY_ID1,
821 VCHAR_COL1_ORIG,
822 VCHAR_COL1_NEW,
823 REQUEST_ID,
824 ACTION_FLAG,
825 CREATED_BY,
826 CREATION_DATE,
827 LAST_UPDATE_LOGIN,
828 LAST_UPDATE_DATE,
829 LAST_UPDATED_BY
830 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
831 'OKC_RULES_B',
832 CUSTOMER_MERGE_HEADER_ID,
833 rle.ID,
834 rle.object3_id1,
835 to_char(rcm.customer_id),
836 req_id,
837 'U',
838 hz_utility_pub.CREATED_BY,
839 hz_utility_pub.CREATION_DATE,
843 FROM OKC_RULES_B rle, ra_customer_merges rcm
840 hz_utility_pub.LAST_UPDATE_LOGIN,
841 hz_utility_pub.LAST_UPDATE_DATE,
842 hz_utility_pub.LAST_UPDATED_BY
844 WHERE (
845 rle.object3_id1 = to_char(rcm.duplicate_id)
846 ) AND rcm.process_flag = 'N'
847 AND rcm.request_id = req_id
848 AND rcm.set_number = set_number
849 AND rle.object3_id1 IN (SELECT to_char(rcm.duplicate_id)
850 FROM ra_customer_merges rcm
851 WHERE rcm.process_flag = 'N'
852 AND rcm.request_id = req_id
853 AND rcm.set_number = set_number)
854 AND rle.jtot_object3_code IN (SELECT ojt.object_code
855 FROM jtf_objects_b ojt
856 ,jtf_object_usages oue
857 WHERE ojt.object_code = oue.object_code
858 AND oue.object_user_code = c_account);
859 End If;
860
861 arp_message.set_name('AR','AR_UPDATING_TABLE');
862 arp_message.set_token('TABLE_NAME','OKC_RULES_B.OBJECT3_ID1',FALSE);
863 UPDATE okc_rules_b rle
864 SET rle.object3_id1 = (SELECT DISTINCT to_char(rcm.customer_id)
865 FROM ra_customer_merges rcm
866 WHERE rle.object3_id1 = rcm.duplicate_id
867 AND rcm.process_flag = 'N'
868 AND rcm.request_id = req_id
869 AND rcm.set_number = set_number)
870 ,rle.object_version_number = rle.object_version_number + 1
871 ,rle.last_update_date = SYSDATE
872 ,rle.last_updated_by = arp_standard.profile.user_id
873 ,rle.last_update_login = arp_standard.profile.last_update_login
874 WHERE rle.object3_id1 IN (SELECT to_char(rcm.duplicate_id)
875 FROM ra_customer_merges rcm
876 WHERE rcm.process_flag = 'N'
877 AND rcm.request_id = req_id
878 AND rcm.set_number = set_number)
879 AND rle.jtot_object3_code IN (SELECT ojt.object_code
880 FROM jtf_objects_b ojt
881 ,jtf_object_usages oue
882 WHERE ojt.object_code = oue.object_code
883 AND oue.object_user_code = c_account)
884 ;
885 l_count := sql%rowcount;
886 arp_message.set_name('AR','AR_ROWS_UPDATED');
887 arp_message.set_token('NUM_ROWS',to_char(l_count));
888
889 -- Start:Code added for Bug 3555739
890 -- Updating okc_k_headers_b
891 -- Cust_Acct_Id
892 -- Insert into log table
893 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
894 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
895 MERGE_LOG_ID,
896 TABLE_NAME,
897 MERGE_HEADER_ID,
898 PRIMARY_KEY_ID1,
899 VCHAR_COL1_ORIG,
900 VCHAR_COL1_NEW,
901 REQUEST_ID,
902 ACTION_FLAG,
903 CREATED_BY,
904 CREATION_DATE,
905 LAST_UPDATE_LOGIN,
906 LAST_UPDATE_DATE,
907 LAST_UPDATED_BY
908 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
909 'OKC_K_HEADERS_B',
910 CUSTOMER_MERGE_HEADER_ID,
911 okh.ID,
912 to_char(okh.cust_acct_id),
913 to_char(rcm.customer_id),
914 req_id,
915 'U',
916 hz_utility_pub.CREATED_BY,
917 hz_utility_pub.CREATION_DATE,
918 hz_utility_pub.LAST_UPDATE_LOGIN,
919 hz_utility_pub.LAST_UPDATE_DATE,
920 hz_utility_pub.LAST_UPDATED_BY
921 FROM OKC_K_HEADERS_B okh, ra_customer_merges rcm
922 WHERE (
923 okh.cust_acct_id = rcm.duplicate_id
924 ) AND rcm.process_flag = 'N'
925 AND rcm.request_id = req_id
926 AND rcm.set_number = set_number
927 AND okh.cust_acct_id IN (SELECT rcm.duplicate_id
928 FROM ra_customer_merges rcm
929 WHERE rcm.process_flag = 'N'
930 AND rcm.request_id = req_id
931 AND rcm.set_number = set_number);
932
933 End If;
934
935 arp_message.set_name('AR','AR_UPDATING_TABLE');
936 arp_message.set_token('TABLE_NAME','OKC_K_HEADERS_B.CUST_ACCT_ID',FALSE);
937
938 UPDATE okc_k_headers_b okh
939 SET okh.cust_acct_id = (SELECT DISTINCT (rcm.customer_id)
940 FROM ra_customer_merges rcm
941 WHERE okh.cust_acct_id = rcm.duplicate_id
942 AND rcm.process_flag = 'N'
943 AND rcm.request_id = req_id
944 AND rcm.set_number = set_number)
945 ,okh.object_version_number = okh.object_version_number + 1
946 ,okh.last_update_date = SYSDATE
947 ,okh.last_updated_by = arp_standard.profile.user_id
948 ,okh.last_update_login = arp_standard.profile.last_update_login
949 WHERE okh.cust_acct_id IN (SELECT DISTINCT (rcm.duplicate_id)
950 FROM ra_customer_merges rcm
951 WHERE rcm.process_flag = 'N'
952 AND rcm.request_id = req_id
956 arp_message.set_name('AR','AR_ROWS_UPDATED');
953 AND rcm.set_number = set_number);
954
955 l_count := sql%rowcount;
957 arp_message.set_token('NUM_ROWS',to_char(l_count));
958
959
960
961 -- Updating okc_k_lines_b
962 -- Cust_Acct_Id
963 -- Insert into log table
964 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
965 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
966 MERGE_LOG_ID,
967 TABLE_NAME,
968 MERGE_HEADER_ID,
969 PRIMARY_KEY_ID1,
970 VCHAR_COL1_ORIG,
971 VCHAR_COL1_NEW,
972 REQUEST_ID,
973 ACTION_FLAG,
974 CREATED_BY,
975 CREATION_DATE,
976 LAST_UPDATE_LOGIN,
977 LAST_UPDATE_DATE,
978 LAST_UPDATED_BY
979 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
980 'OKC_K_LINES_B',
981 CUSTOMER_MERGE_HEADER_ID,
982 okl.ID,
983 to_char(okl.cust_acct_id),
984 to_char(rcm.customer_id),
985 req_id,
986 'U',
987 hz_utility_pub.CREATED_BY,
988 hz_utility_pub.CREATION_DATE,
989 hz_utility_pub.LAST_UPDATE_LOGIN,
990 hz_utility_pub.LAST_UPDATE_DATE,
991 hz_utility_pub.LAST_UPDATED_BY
992 FROM OKC_K_LINES_B okl, ra_customer_merges rcm
993 WHERE (
994 okl.cust_acct_id = rcm.duplicate_id
995 ) AND rcm.process_flag = 'N'
996 AND rcm.request_id = req_id
997 AND rcm.set_number = set_number
998 AND okl.cust_acct_id IN (SELECT rcm.duplicate_id
999 FROM ra_customer_merges rcm
1000 WHERE rcm.process_flag = 'N'
1001 AND rcm.request_id = req_id
1002 AND rcm.set_number = set_number);
1003
1004 End If;
1005
1006 arp_message.set_name('AR','AR_UPDATING_TABLE');
1007 arp_message.set_token('TABLE_NAME','OKC_K_LINES_B.CUST_ACCT_ID',FALSE);
1008
1009 UPDATE okc_k_lines_b okl
1010 SET okl.cust_acct_id = (SELECT DISTINCT (rcm.customer_id)
1011 FROM ra_customer_merges rcm
1012 WHERE okl.cust_acct_id = rcm.duplicate_id
1013 AND rcm.process_flag = 'N'
1014 AND rcm.request_id = req_id
1015 AND rcm.set_number = set_number)
1016 ,okl.object_version_number = okl.object_version_number + 1
1017 ,okl.last_update_date = SYSDATE
1018 ,okl.last_updated_by = arp_standard.profile.user_id
1019 ,okl.last_update_login = arp_standard.profile.last_update_login
1020 WHERE okl.cust_acct_id IN (SELECT DISTINCT (rcm.duplicate_id)
1021 FROM ra_customer_merges rcm
1022 WHERE rcm.process_flag = 'N'
1023 AND rcm.request_id = req_id
1024 AND rcm.set_number = set_number);
1025
1026 l_count := sql%rowcount;
1027 arp_message.set_name('AR','AR_ROWS_UPDATED');
1028 arp_message.set_token('NUM_ROWS',to_char(l_count));
1029
1030 -- Updating okc_k_party_roles_b
1031 -- Cust_Acct_Id
1032 -- Insert into log table
1033 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1034 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1035 MERGE_LOG_ID,
1036 TABLE_NAME,
1037 MERGE_HEADER_ID,
1038 PRIMARY_KEY_ID1,
1039 VCHAR_COL1_ORIG,
1040 VCHAR_COL1_NEW,
1041 REQUEST_ID,
1042 ACTION_FLAG,
1043 CREATED_BY,
1044 CREATION_DATE,
1045 LAST_UPDATE_LOGIN,
1046 LAST_UPDATE_DATE,
1047 LAST_UPDATED_BY
1048 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
1049 'OKC_K_PARTY_ROLES_B',
1050 CUSTOMER_MERGE_HEADER_ID,
1051 okpr.ID,
1052 to_char(okpr.cust_acct_id),
1053 to_char(rcm.customer_id),
1054 req_id,
1055 'U',
1056 hz_utility_pub.CREATED_BY,
1057 hz_utility_pub.CREATION_DATE,
1058 hz_utility_pub.LAST_UPDATE_LOGIN,
1059 hz_utility_pub.LAST_UPDATE_DATE,
1060 hz_utility_pub.LAST_UPDATED_BY
1061 FROM OKC_K_PARTY_ROLES_B okpr, ra_customer_merges rcm
1062 WHERE (
1063 okpr.cust_acct_id = rcm.duplicate_id
1064 ) AND rcm.process_flag = 'N'
1065 AND rcm.request_id = req_id
1066 AND rcm.set_number = set_number
1067 AND okpr.cust_acct_id IN (SELECT rcm.duplicate_id
1068 FROM ra_customer_merges rcm
1069 WHERE rcm.process_flag = 'N'
1070 AND rcm.request_id = req_id
1071 AND rcm.set_number = set_number);
1072
1073 End If;
1074
1075 arp_message.set_name('AR','AR_UPDATING_TABLE');
1076 arp_message.set_token('TABLE_NAME','OKC_K_PARTY_ROLES_B.CUST_ACCT_ID',FALSE);
1077
1078 UPDATE okc_k_party_roles_b okpr
1079 SET okpr.cust_acct_id = (SELECT DISTINCT (rcm.customer_id)
1080 FROM ra_customer_merges rcm
1081 WHERE okpr.cust_acct_id = rcm.duplicate_id
1082 AND rcm.process_flag = 'N'
1083 AND rcm.request_id = req_id
1087 ,okpr.last_updated_by = arp_standard.profile.user_id
1084 AND rcm.set_number = set_number)
1085 ,okpr.object_version_number = okpr.object_version_number + 1
1086 ,okpr.last_update_date = SYSDATE
1088 ,okpr.last_update_login = arp_standard.profile.last_update_login
1089 WHERE okpr.cust_acct_id IN (SELECT DISTINCT (rcm.duplicate_id)
1090 FROM ra_customer_merges rcm
1091 WHERE rcm.process_flag = 'N'
1092 AND rcm.request_id = req_id
1093 AND rcm.set_number = set_number);
1094
1095 l_count := sql%rowcount;
1096 arp_message.set_name('AR','AR_ROWS_UPDATED');
1097 arp_message.set_token('NUM_ROWS',to_char(l_count));
1098
1099 -- End:Code added for Bug 3555739
1100
1101 -- contract items
1102 -- Insert into log table
1103 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1104 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1105 MERGE_LOG_ID,
1106 TABLE_NAME,
1107 MERGE_HEADER_ID,
1108 PRIMARY_KEY_ID1,
1109 VCHAR_COL1_ORIG,
1110 VCHAR_COL1_NEW,
1111 REQUEST_ID,
1112 ACTION_FLAG,
1113 CREATED_BY,
1114 CREATION_DATE,
1115 LAST_UPDATE_LOGIN,
1116 LAST_UPDATE_DATE,
1117 LAST_UPDATED_BY
1118 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
1119 'OKC_K_ITEMS',
1120 CUSTOMER_MERGE_HEADER_ID,
1121 cim.ID,
1122 cim.object1_id1,
1123 to_char(rcm.customer_id),
1124 req_id,
1125 'U',
1126 hz_utility_pub.CREATED_BY,
1127 hz_utility_pub.CREATION_DATE,
1128 hz_utility_pub.LAST_UPDATE_LOGIN,
1129 hz_utility_pub.LAST_UPDATE_DATE,
1130 hz_utility_pub.LAST_UPDATED_BY
1131 FROM OKC_K_ITEMS cim, ra_customer_merges rcm
1132 WHERE (
1133 cim.object1_id1 = to_char(rcm.duplicate_id)
1134 ) AND rcm.process_flag = 'N'
1135 AND rcm.request_id = req_id
1136 AND rcm.set_number = set_number
1137 AND cim.object1_id1 IN (SELECT to_char(rcm.duplicate_id)
1138 FROM ra_customer_merges rcm
1139 WHERE rcm.process_flag = 'N'
1140 AND rcm.request_id = req_id
1141 AND rcm.set_number = set_number)
1142 AND cim.jtot_object1_code IN (SELECT ojt.object_code
1143 FROM jtf_objects_b ojt
1144 ,jtf_object_usages oue
1145 WHERE ojt.object_code = oue.object_code
1146 AND oue.object_user_code = c_account);
1147 End If;
1148
1149 arp_message.set_name('AR','AR_UPDATING_TABLE');
1150 arp_message.set_token('TABLE_NAME','OKC_K_ITEMS',FALSE);
1151 UPDATE okc_k_items cim
1152 SET cim.object1_id1 = (SELECT DISTINCT to_char(rcm.customer_id)
1153 FROM ra_customer_merges rcm
1154 WHERE cim.object1_id1 = rcm.duplicate_id
1155 AND rcm.process_flag = 'N'
1156 AND rcm.request_id = req_id
1157 AND rcm.set_number = set_number)
1158 ,cim.object_version_number = cim.object_version_number + 1
1159 ,cim.last_update_date = SYSDATE
1160 ,cim.last_updated_by = arp_standard.profile.user_id
1161 ,cim.last_update_login = arp_standard.profile.last_update_login
1162 WHERE cim.object1_id1 IN (SELECT to_char(rcm.duplicate_id)
1163 FROM ra_customer_merges rcm
1164 WHERE rcm.process_flag = 'N'
1165 AND rcm.request_id = req_id
1166 AND rcm.set_number = set_number)
1167 AND cim.jtot_object1_code IN (SELECT ojt.object_code
1168 FROM jtf_objects_b ojt
1169 ,jtf_object_usages oue
1170 WHERE ojt.object_code = oue.object_code
1171 AND oue.object_user_code = c_account)
1172 ;
1173 l_count := sql%rowcount;
1174 arp_message.set_name('AR','AR_ROWS_UPDATED');
1175 arp_message.set_token('NUM_ROWS',to_char(l_count));
1176
1177 arp_message.set_line('OKC_HZ_MERGE_PUB.ACCOUNT_MERGE()-');
1178
1179
1180 END; -- account_merge
1181
1182 --
1183 -- sub routine to merge account sites and site uses
1184 -- exceptions are unhandled, sent back to caller
1185 --
1186 PROCEDURE account_site_merge (req_id IN NUMBER
1187 ,set_number IN NUMBER) IS
1188
1189 l_count NUMBER;
1190
1191 BEGIN
1192 arp_message.set_line('OKC_HZ_MERGE_PUB.ACCOUNT_SITE_MERGE()+');
1193 arp_message.set_line('Updating account sites');
1194 --
1195 -- Account Sites come first, then site uses
1196 --
1197 -- Account Sites in Rules. There are three ids in rules that could hold the site id
1198 --
1199
1200 -- Rules ID1
1201 -- Insert into log table
1202 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1203 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1204 MERGE_LOG_ID,
1205 TABLE_NAME,
1206 MERGE_HEADER_ID,
1207 PRIMARY_KEY_ID1,
1208 VCHAR_COL1_ORIG,
1212 CREATED_BY,
1209 VCHAR_COL1_NEW,
1210 REQUEST_ID,
1211 ACTION_FLAG,
1213 CREATION_DATE,
1214 LAST_UPDATE_LOGIN,
1215 LAST_UPDATE_DATE,
1216 LAST_UPDATED_BY
1217 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
1218 'OKC_RULES_B',
1219 CUSTOMER_MERGE_HEADER_ID,
1220 rle.ID,
1221 rle.object1_id1,
1222 to_char(customer_address_id),
1223 req_id,
1224 'U',
1225 hz_utility_pub.CREATED_BY,
1226 hz_utility_pub.CREATION_DATE,
1227 hz_utility_pub.LAST_UPDATE_LOGIN,
1228 hz_utility_pub.LAST_UPDATE_DATE,
1229 hz_utility_pub.LAST_UPDATED_BY
1230 FROM OKC_RULES_B rle, ra_customer_merges rcm
1231 WHERE (
1232 rle.object1_id1 = to_char(rcm.duplicate_address_id)
1233 ) AND rcm.process_flag = 'N'
1234 AND rcm.request_id = req_id
1235 AND rcm.set_number = set_number
1236 AND rle.object1_id1 IN (SELECT to_char(rcm.duplicate_address_id)
1237 FROM ra_customer_merges rcm
1238 WHERE rcm.process_flag = 'N'
1239 AND rcm.request_id = req_id
1240 AND rcm.set_number = set_number)
1241 AND rle.jtot_object1_code IN (SELECT ojt.object_code
1242 FROM jtf_objects_b ojt
1243 ,jtf_object_usages oue
1244 WHERE ojt.object_code = oue.object_code
1245 AND oue.object_user_code = c_c_site);
1246 End If;
1247
1248 arp_message.set_name('AR','AR_UPDATING_TABLE');
1249 arp_message.set_token('TABLE_NAME','OKC_RULES_B.OBJECT1_ID1',FALSE);
1250 UPDATE okc_rules_b rle
1251 SET rle.object1_id1 = (SELECT DISTINCT to_char(rcm.customer_address_id)
1252 FROM ra_customer_merges rcm
1253 WHERE rle.object1_id1 = rcm.duplicate_address_id
1254 AND rcm.process_flag = 'N'
1255 AND rcm.request_id = req_id
1256 AND rcm.set_number = set_number
1257 AND ROWNUM=1)
1258 ,rle.object_version_number = rle.object_version_number + 1
1259 ,rle.last_update_date = SYSDATE
1260 ,rle.last_updated_by = arp_standard.profile.user_id
1261 ,rle.last_update_login = arp_standard.profile.last_update_login
1262 WHERE rle.object1_id1 IN (SELECT to_char(rcm.duplicate_address_id)
1263 FROM ra_customer_merges rcm
1264 WHERE rcm.process_flag = 'N'
1265 AND rcm.request_id = req_id
1266 AND rcm.set_number = set_number)
1267 AND rle.jtot_object1_code IN (SELECT ojt.object_code
1268 FROM jtf_objects_b ojt
1269 ,jtf_object_usages oue
1270 WHERE ojt.object_code = oue.object_code
1271 AND oue.object_user_code = c_c_site)
1272 ;
1273 l_count := sql%rowcount;
1274 arp_message.set_name('AR','AR_ROWS_UPDATED');
1275 arp_message.set_token('NUM_ROWS',to_char(l_count));
1276
1277
1278 -- Rules ID2
1279 -- Insert into log table
1280 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1281 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1282 MERGE_LOG_ID,
1283 TABLE_NAME,
1284 MERGE_HEADER_ID,
1285 PRIMARY_KEY_ID1,
1286 VCHAR_COL1_ORIG,
1287 VCHAR_COL1_NEW,
1288 REQUEST_ID,
1289 ACTION_FLAG,
1290 CREATED_BY,
1291 CREATION_DATE,
1292 LAST_UPDATE_LOGIN,
1293 LAST_UPDATE_DATE,
1294 LAST_UPDATED_BY
1295 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
1296 'OKC_RULES_B',
1297 CUSTOMER_MERGE_HEADER_ID,
1298 rle.ID,
1299 rle.object2_id1,
1300 to_char(customer_address_id),
1301 req_id,
1302 'U',
1303 hz_utility_pub.CREATED_BY,
1304 hz_utility_pub.CREATION_DATE,
1305 hz_utility_pub.LAST_UPDATE_LOGIN,
1306 hz_utility_pub.LAST_UPDATE_DATE,
1307 hz_utility_pub.LAST_UPDATED_BY
1308 FROM OKC_RULES_B rle, ra_customer_merges rcm
1309 WHERE (
1310 rle.object2_id1 = to_char(rcm.duplicate_address_id)
1311 ) AND rcm.process_flag = 'N'
1312 AND rcm.request_id = req_id
1313 AND rcm.set_number = set_number
1314 AND rle.object2_id1 IN (SELECT to_char(rcm.duplicate_address_id)
1315 FROM ra_customer_merges rcm
1316 WHERE rcm.process_flag = 'N'
1317 AND rcm.request_id = req_id
1318 AND rcm.set_number = set_number)
1319 AND rle.jtot_object2_code IN (SELECT ojt.object_code
1320 FROM jtf_objects_b ojt
1321 ,jtf_object_usages oue
1322 WHERE ojt.object_code = oue.object_code
1323 AND oue.object_user_code = c_c_site);
1324 End If;
1325
1329 SET rle.object2_id1 = (SELECT DISTINCT to_char(rcm.customer_address_id)
1326 arp_message.set_name('AR','AR_UPDATING_TABLE');
1327 arp_message.set_token('TABLE_NAME','OKC_RULES_B.OBJECT2_ID1',FALSE);
1328 UPDATE okc_rules_b rle
1330 FROM ra_customer_merges rcm
1331 WHERE rle.object2_id1 = rcm.duplicate_address_id
1332 AND rcm.process_flag = 'N'
1333 AND rcm.request_id = req_id
1334 AND rcm.set_number = set_number
1335 AND ROWNUM=1)
1336 ,rle.object_version_number = rle.object_version_number + 1
1337 ,rle.last_update_date = SYSDATE
1338 ,rle.last_updated_by = arp_standard.profile.user_id
1339 ,rle.last_update_login = arp_standard.profile.last_update_login
1340 WHERE rle.object2_id1 IN (SELECT to_char(rcm.duplicate_address_id)
1341 FROM ra_customer_merges rcm
1342 WHERE rcm.process_flag = 'N'
1343 AND rcm.request_id = req_id
1344 AND rcm.set_number = set_number)
1345 AND rle.jtot_object2_code IN (SELECT ojt.object_code
1346 FROM jtf_objects_b ojt
1347 ,jtf_object_usages oue
1348 WHERE ojt.object_code = oue.object_code
1349 AND oue.object_user_code = c_c_site)
1350 ;
1351 l_count := sql%rowcount;
1352 arp_message.set_name('AR','AR_ROWS_UPDATED');
1353 arp_message.set_token('NUM_ROWS',to_char(l_count));
1354
1355 -- Rules ID3
1356 -- Insert into log table
1357 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1358 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1359 MERGE_LOG_ID,
1360 TABLE_NAME,
1361 MERGE_HEADER_ID,
1362 PRIMARY_KEY_ID1,
1363 VCHAR_COL1_ORIG,
1364 VCHAR_COL1_NEW,
1365 REQUEST_ID,
1366 ACTION_FLAG,
1367 CREATED_BY,
1368 CREATION_DATE,
1369 LAST_UPDATE_LOGIN,
1370 LAST_UPDATE_DATE,
1371 LAST_UPDATED_BY
1372 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
1373 'OKC_RULES_B',
1374 CUSTOMER_MERGE_HEADER_ID,
1375 rle.ID,
1376 rle.object3_id1,
1377 to_char(customer_address_id),
1378 req_id,
1379 'U',
1380 hz_utility_pub.CREATED_BY,
1381 hz_utility_pub.CREATION_DATE,
1382 hz_utility_pub.LAST_UPDATE_LOGIN,
1383 hz_utility_pub.LAST_UPDATE_DATE,
1384 hz_utility_pub.LAST_UPDATED_BY
1385 FROM OKC_RULES_B rle, ra_customer_merges rcm
1386 WHERE (
1387 rle.object3_id1 = to_char(rcm.duplicate_address_id)
1388 ) AND rcm.process_flag = 'N'
1389 AND rcm.request_id = req_id
1390 AND rcm.set_number = set_number
1391 AND rle.object3_id1 IN (SELECT to_char(rcm.duplicate_address_id)
1392 FROM ra_customer_merges rcm
1393 WHERE rcm.process_flag = 'N'
1394 AND rcm.request_id = req_id
1395 AND rcm.set_number = set_number)
1396 AND rle.jtot_object3_code IN (SELECT ojt.object_code
1397 FROM jtf_objects_b ojt
1398 ,jtf_object_usages oue
1399 WHERE ojt.object_code = oue.object_code
1400 AND oue.object_user_code = c_c_site);
1401 End If;
1402
1403 arp_message.set_name('AR','AR_UPDATING_TABLE');
1404 arp_message.set_token('TABLE_NAME','OKC_RULES_B.OBJECT3_ID1',FALSE);
1405 UPDATE okc_rules_b rle
1406 SET rle.object3_id1 = (SELECT DISTINCT to_char(rcm.customer_address_id)
1407 FROM ra_customer_merges rcm
1408 WHERE rle.object3_id1 = rcm.duplicate_address_id
1409 AND rcm.process_flag = 'N'
1410 AND rcm.request_id = req_id
1411 AND rcm.set_number = set_number
1412 AND ROWNUM=1)
1413 ,rle.object_version_number = rle.object_version_number + 1
1414 ,rle.last_update_date = SYSDATE
1415 ,rle.last_updated_by = arp_standard.profile.user_id
1416 ,rle.last_update_login = arp_standard.profile.last_update_login
1417 WHERE rle.object3_id1 IN (SELECT to_char(rcm.duplicate_address_id)
1418 FROM ra_customer_merges rcm
1419 WHERE rcm.process_flag = 'N'
1420 AND rcm.request_id = req_id
1421 AND rcm.set_number = set_number)
1422 AND rle.jtot_object3_code IN (SELECT ojt.object_code
1423 FROM jtf_objects_b ojt
1424 ,jtf_object_usages oue
1425 WHERE ojt.object_code = oue.object_code
1426 AND oue.object_user_code = c_c_site)
1427 ;
1428 l_count := sql%rowcount;
1429 arp_message.set_name('AR','AR_ROWS_UPDATED');
1430 arp_message.set_token('NUM_ROWS',to_char(l_count));
1431
1432
1433 --
1434 -- Account Sites in Items
1435 --
1436 -- Insert into log table
1440 TABLE_NAME,
1437 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1438 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1439 MERGE_LOG_ID,
1441 MERGE_HEADER_ID,
1442 PRIMARY_KEY_ID1,
1443 VCHAR_COL1_ORIG,
1444 VCHAR_COL1_NEW,
1445 REQUEST_ID,
1446 ACTION_FLAG,
1447 CREATED_BY,
1448 CREATION_DATE,
1449 LAST_UPDATE_LOGIN,
1450 LAST_UPDATE_DATE,
1451 LAST_UPDATED_BY
1452 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
1453 'OKC_K_ITEMS',
1454 CUSTOMER_MERGE_HEADER_ID,
1455 cim.ID,
1456 cim.object1_id1,
1457 to_char(rcm.customer_address_id),
1458 req_id,
1459 'U',
1460 hz_utility_pub.CREATED_BY,
1461 hz_utility_pub.CREATION_DATE,
1462 hz_utility_pub.LAST_UPDATE_LOGIN,
1463 hz_utility_pub.LAST_UPDATE_DATE,
1464 hz_utility_pub.LAST_UPDATED_BY
1465 FROM OKC_K_ITEMS cim, ra_customer_merges rcm
1466 WHERE (
1467 cim.object1_id1 = to_char(rcm.duplicate_address_id)
1468 ) AND rcm.process_flag = 'N'
1469 AND rcm.request_id = req_id
1470 AND rcm.set_number = set_number
1471 AND cim.object1_id1 IN (SELECT to_char(rcm.duplicate_address_id)
1472 FROM ra_customer_merges rcm
1473 WHERE rcm.process_flag = 'N'
1474 AND rcm.request_id = req_id
1475 AND rcm.set_number = set_number)
1476 AND cim.jtot_object1_code IN (SELECT ojt.object_code
1477 FROM jtf_objects_b ojt
1478 ,jtf_object_usages oue
1479 WHERE ojt.object_code = oue.object_code
1480 AND oue.object_user_code = c_c_site);
1481 End If;
1482
1483 arp_message.set_name('AR','AR_UPDATING_TABLE');
1484 arp_message.set_token('TABLE_NAME','OKC_K_ITEMS',FALSE);
1485 UPDATE okc_k_items cim
1486 SET cim.object1_id1 = (SELECT DISTINCT to_char(rcm.customer_address_id)
1487 FROM ra_customer_merges rcm
1488 WHERE cim.object1_id1 = rcm.duplicate_address_id
1489 AND rcm.process_flag = 'N'
1490 AND rcm.request_id = req_id
1491 AND rcm.set_number = set_number
1492 AND ROWNUM=1)
1493 ,cim.object_version_number = cim.object_version_number + 1
1494 ,cim.last_update_date = SYSDATE
1495 ,cim.last_updated_by = arp_standard.profile.user_id
1496 ,cim.last_update_login = arp_standard.profile.last_update_login
1497 WHERE cim.object1_id1 IN (SELECT to_char(rcm.duplicate_address_id)
1498 FROM ra_customer_merges rcm
1499 WHERE rcm.process_flag = 'N'
1500 AND rcm.request_id = req_id
1501 AND rcm.set_number = set_number)
1502 AND cim.jtot_object1_code IN (SELECT ojt.object_code
1503 FROM jtf_objects_b ojt
1504 ,jtf_object_usages oue
1505 WHERE ojt.object_code = oue.object_code
1506 AND oue.object_user_code = c_c_site)
1507 ;
1508 l_count := sql%rowcount;
1509 arp_message.set_name('AR','AR_ROWS_UPDATED');
1510 arp_message.set_token('NUM_ROWS',to_char(l_count));
1511
1512 --
1513 -- Account Site Uses
1514 --
1515
1516
1517 --
1518 -- Account Site Use in OKC_K_PARTY_ROLES_B (see Bug 3950642)
1519 -- OKE only uses the first object1_id1 to hold the site use id
1520 --
1521 --chkrishn 11/03/2004
1522 arp_message.set_line('Updating account site uses for OKE');
1523 -- Insert into log table
1524 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1525 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1526 MERGE_LOG_ID,
1527 TABLE_NAME,
1528 MERGE_HEADER_ID,
1529 PRIMARY_KEY_ID1,
1530 VCHAR_COL1_ORIG,
1531 VCHAR_COL1_NEW,
1532 REQUEST_ID,
1533 ACTION_FLAG,
1534 CREATED_BY,
1535 CREATION_DATE,
1536 LAST_UPDATE_LOGIN,
1537 LAST_UPDATE_DATE,
1538 LAST_UPDATED_BY
1539 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
1540 'OKC_K_PARTY_ROLES_B',
1541 CUSTOMER_MERGE_HEADER_ID,
1542 rle.ID,
1543 rle.object1_id1,
1544 to_char(rcm.customer_site_id),
1545 req_id,
1546 'U',
1547 hz_utility_pub.CREATED_BY,
1548 hz_utility_pub.CREATION_DATE,
1549 hz_utility_pub.LAST_UPDATE_LOGIN,
1550 hz_utility_pub.LAST_UPDATE_DATE,
1551 hz_utility_pub.LAST_UPDATED_BY
1552 FROM OKC_K_PARTY_ROLES_B rle, ra_customer_merges rcm
1553 WHERE (
1554 rle.object1_id1 = to_char(rcm.duplicate_site_id)
1555 ) AND rcm.process_flag = 'N'
1556 AND rcm.request_id = req_id
1557 AND rcm.set_number = set_number
1558 AND rle.object1_id1 IN (SELECT to_char(rcm.duplicate_site_id)
1559 FROM ra_customer_merges rcm
1563 AND rle.jtot_object1_code IN (SELECT ojt.object_code
1560 WHERE rcm.process_flag = 'N'
1561 AND rcm.request_id = req_id
1562 AND rcm.set_number = set_number)
1564 FROM jtf_objects_b ojt
1565 ,jtf_object_usages oue
1566 WHERE ojt.object_code = oue.object_code
1567 AND oue.object_user_code = c_c_site_use);
1568 End If;
1569
1570 --chkrishn 11/03/2004
1571
1572 arp_message.set_name('AR','AR_UPDATING_TABLE');
1573 arp_message.set_token('TABLE_NAME','OKC_K_PARTY_ROLES_B',FALSE);
1574
1575 UPDATE okc_k_party_roles_b rle
1576 SET rle.object1_id1 = (SELECT DISTINCT to_char(rcm.customer_site_id)
1577 FROM ra_customer_merges rcm
1578 WHERE rle.object1_id1 = rcm.duplicate_site_id
1579 AND rcm.process_flag = 'N'
1580 AND rcm.request_id = req_id
1581 AND rcm.set_number = set_number)
1582 ,rle.object_version_number = rle.object_version_number + 1
1583 ,rle.last_update_date = SYSDATE
1584 ,rle.last_updated_by = arp_standard.profile.user_id
1585 ,rle.last_update_login = arp_standard.profile.last_update_login
1586 WHERE rle.object1_id1 IN ( SELECT to_char(rcm.duplicate_site_id)
1587 FROM ra_customer_merges rcm
1588 WHERE rcm.process_flag = 'N'
1589 AND rcm.request_id = req_id
1590 AND rcm.set_number = set_number)
1591 AND rle.jtot_object1_code IN (SELECT ojt.object_code
1592 FROM jtf_objects_b ojt,jtf_object_usages oue
1593 WHERE ojt.object_code =oue.object_code
1594 AND oue.object_user_code = c_c_site_use)
1595 AND rle.dnz_chr_id in (select k_header_id from oke_k_headers);
1596
1597 --chkrishn 11/03/2004
1598 l_count := sql%rowcount;
1599 arp_message.set_name('AR','AR_ROWS_UPDATED');
1600 arp_message.set_token('NUM_ROWS',to_char(l_count));
1601
1602 --
1603 -- End of bug fix 3950642
1604 --
1605
1606
1607 --
1608 -- Account Sites Uses in Rules. There are three ids in rules that
1609 -- could hold the site use id
1610 --
1611
1612
1613 arp_message.set_line('Updating account site uses');
1614
1615 -- Rules ID1
1616 -- Insert into log table
1617 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1618 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1619 MERGE_LOG_ID,
1620 TABLE_NAME,
1621 MERGE_HEADER_ID,
1622 PRIMARY_KEY_ID1,
1623 VCHAR_COL1_ORIG,
1624 VCHAR_COL1_NEW,
1625 REQUEST_ID,
1626 ACTION_FLAG,
1627 CREATED_BY,
1628 CREATION_DATE,
1629 LAST_UPDATE_LOGIN,
1630 LAST_UPDATE_DATE,
1631 LAST_UPDATED_BY
1632 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
1633 'OKC_RULES_B',
1634 CUSTOMER_MERGE_HEADER_ID,
1635 rle.ID,
1636 rle.object1_id1,
1637 to_char(rcm.customer_site_id),
1638 req_id,
1639 'U',
1640 hz_utility_pub.CREATED_BY,
1641 hz_utility_pub.CREATION_DATE,
1642 hz_utility_pub.LAST_UPDATE_LOGIN,
1643 hz_utility_pub.LAST_UPDATE_DATE,
1644 hz_utility_pub.LAST_UPDATED_BY
1645 FROM OKC_RULES_B rle, ra_customer_merges rcm
1646 WHERE (
1647 rle.object1_id1 = to_char(rcm.duplicate_site_id)
1648 ) AND rcm.process_flag = 'N'
1649 AND rcm.request_id = req_id
1650 AND rcm.set_number = set_number
1651 AND rle.object1_id1 IN (SELECT to_char(rcm.duplicate_site_id)
1652 FROM ra_customer_merges rcm
1653 WHERE rcm.process_flag = 'N'
1654 AND rcm.request_id = req_id
1655 AND rcm.set_number = set_number)
1656 AND rle.jtot_object1_code IN (SELECT ojt.object_code
1657 FROM jtf_objects_b ojt
1658 ,jtf_object_usages oue
1659 WHERE ojt.object_code = oue.object_code
1660 AND oue.object_user_code = c_c_site_use);
1661 End If;
1662
1663 arp_message.set_name('AR','AR_UPDATING_TABLE');
1664 arp_message.set_token('TABLE_NAME','OKC_RULES_B.OBJECT1_ID1',FALSE);
1665 UPDATE okc_rules_b rle
1666 SET rle.object1_id1 = (SELECT DISTINCT to_char(rcm.customer_site_id)
1667 FROM ra_customer_merges rcm
1668 WHERE rle.object1_id1 = rcm.duplicate_site_id
1669 AND rcm.process_flag = 'N'
1670 AND rcm.request_id = req_id
1671 AND rcm.set_number = set_number)
1672 ,rle.object_version_number = rle.object_version_number + 1
1673 ,rle.last_update_date = SYSDATE
1674 ,rle.last_updated_by = arp_standard.profile.user_id
1675 ,rle.last_update_login = arp_standard.profile.last_update_login
1676 WHERE rle.object1_id1 IN (SELECT to_char(rcm.duplicate_site_id)
1677 FROM ra_customer_merges rcm
1681 AND rle.jtot_object1_code IN (SELECT ojt.object_code
1678 WHERE rcm.process_flag = 'N'
1679 AND rcm.request_id = req_id
1680 AND rcm.set_number = set_number)
1682 FROM jtf_objects_b ojt
1683 ,jtf_object_usages oue
1684 WHERE ojt.object_code = oue.object_code
1685 AND oue.object_user_code = c_c_site_use)
1686 ;
1687 l_count := sql%rowcount;
1688 arp_message.set_name('AR','AR_ROWS_UPDATED');
1689 arp_message.set_token('NUM_ROWS',to_char(l_count));
1690
1691 -- Rules ID2
1692 -- Insert into log table
1693 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1694 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1695 MERGE_LOG_ID,
1696 TABLE_NAME,
1697 MERGE_HEADER_ID,
1698 PRIMARY_KEY_ID1,
1699 VCHAR_COL1_ORIG,
1700 VCHAR_COL1_NEW,
1701 REQUEST_ID,
1702 ACTION_FLAG,
1703 CREATED_BY,
1704 CREATION_DATE,
1705 LAST_UPDATE_LOGIN,
1706 LAST_UPDATE_DATE,
1707 LAST_UPDATED_BY
1708 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
1709 'OKC_RULES_B',
1710 CUSTOMER_MERGE_HEADER_ID,
1711 rle.ID,
1712 rle.object2_id1,
1713 to_char(rcm.customer_site_id),
1714 req_id,
1715 'U',
1716 hz_utility_pub.CREATED_BY,
1717 hz_utility_pub.CREATION_DATE,
1718 hz_utility_pub.LAST_UPDATE_LOGIN,
1719 hz_utility_pub.LAST_UPDATE_DATE,
1720 hz_utility_pub.LAST_UPDATED_BY
1721 FROM OKC_RULES_B rle, ra_customer_merges rcm
1722 WHERE (
1723 rle.object2_id1 = to_char(rcm.duplicate_site_id)
1724 ) AND rcm.process_flag = 'N'
1725 AND rcm.request_id = req_id
1726 AND rcm.set_number = set_number
1727 AND rle.object2_id1 IN (SELECT to_char(rcm.duplicate_site_id)
1728 FROM ra_customer_merges rcm
1729 WHERE rcm.process_flag = 'N'
1730 AND rcm.request_id = req_id
1731 AND rcm.set_number = set_number)
1732 AND rle.jtot_object2_code IN (SELECT ojt.object_code
1733 FROM jtf_objects_b ojt
1734 ,jtf_object_usages oue
1735 WHERE ojt.object_code = oue.object_code
1736 AND oue.object_user_code = c_c_site_use);
1737 End If;
1738
1739 arp_message.set_name('AR','AR_UPDATING_TABLE');
1740 arp_message.set_token('TABLE_NAME','OKC_RULES_B.OBJECT2_ID1',FALSE);
1741 UPDATE okc_rules_b rle
1742 SET rle.object2_id1 = (SELECT DISTINCT to_char(rcm.customer_site_id)
1743 FROM ra_customer_merges rcm
1744 WHERE rle.object2_id1 = rcm.duplicate_site_id
1745 AND rcm.process_flag = 'N'
1746 AND rcm.request_id = req_id
1747 AND rcm.set_number = set_number)
1748 ,rle.object_version_number = rle.object_version_number + 1
1749 ,rle.last_update_date = SYSDATE
1750 ,rle.last_updated_by = arp_standard.profile.user_id
1751 ,rle.last_update_login = arp_standard.profile.last_update_login
1752 WHERE rle.object2_id1 IN (SELECT to_char(rcm.duplicate_site_id)
1753 FROM ra_customer_merges rcm
1754 WHERE rcm.process_flag = 'N'
1755 AND rcm.request_id = req_id
1756 AND rcm.set_number = set_number)
1757 AND rle.jtot_object2_code IN (SELECT ojt.object_code
1758 FROM jtf_objects_b ojt
1759 ,jtf_object_usages oue
1760 WHERE ojt.object_code = oue.object_code
1761 AND oue.object_user_code = c_c_site_use)
1762 ;
1763 l_count := sql%rowcount;
1764 arp_message.set_name('AR','AR_ROWS_UPDATED');
1765 arp_message.set_token('NUM_ROWS',to_char(l_count));
1766
1767
1768 -- Rules ID3
1769 -- Insert into log table
1770 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1771 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1772 MERGE_LOG_ID,
1773 TABLE_NAME,
1774 MERGE_HEADER_ID,
1775 PRIMARY_KEY_ID1,
1776 VCHAR_COL1_ORIG,
1777 VCHAR_COL1_NEW,
1778 REQUEST_ID,
1779 ACTION_FLAG,
1780 CREATED_BY,
1781 CREATION_DATE,
1782 LAST_UPDATE_LOGIN,
1783 LAST_UPDATE_DATE,
1784 LAST_UPDATED_BY
1785 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
1786 'OKC_RULES_B',
1787 CUSTOMER_MERGE_HEADER_ID,
1788 rle.ID,
1789 rle.object3_id1,
1790 to_char(rcm.customer_site_id),
1791 req_id,
1792 'U',
1793 hz_utility_pub.CREATED_BY,
1794 hz_utility_pub.CREATION_DATE,
1795 hz_utility_pub.LAST_UPDATE_LOGIN,
1796 hz_utility_pub.LAST_UPDATE_DATE,
1797 hz_utility_pub.LAST_UPDATED_BY
1798 FROM OKC_RULES_B rle, ra_customer_merges rcm
1799 WHERE (
1803 AND rcm.set_number = set_number
1800 rle.object3_id1 = to_char(rcm.duplicate_site_id)
1801 ) AND rcm.process_flag = 'N'
1802 AND rcm.request_id = req_id
1804 AND rle.object3_id1 IN (SELECT to_char(rcm.duplicate_site_id)
1805 FROM ra_customer_merges rcm
1806 WHERE rcm.process_flag = 'N'
1807 AND rcm.request_id = req_id
1808 AND rcm.set_number = set_number)
1809 AND rle.jtot_object3_code IN (SELECT ojt.object_code
1810 FROM jtf_objects_b ojt
1811 ,jtf_object_usages oue
1812 WHERE ojt.object_code = oue.object_code
1813 AND oue.object_user_code = c_c_site_use);
1814 End If;
1815
1816 arp_message.set_name('AR','AR_UPDATING_TABLE');
1817 arp_message.set_token('TABLE_NAME','OKC_RULES_B.OBJECT3_ID1',FALSE);
1818 UPDATE okc_rules_b rle
1819 SET rle.object3_id1 = (SELECT DISTINCT to_char(rcm.customer_site_id)
1820 FROM ra_customer_merges rcm
1821 WHERE rle.object3_id1 = rcm.duplicate_site_id
1822 AND rcm.process_flag = 'N'
1823 AND rcm.request_id = req_id
1824 AND rcm.set_number = set_number)
1825 ,rle.object_version_number = rle.object_version_number + 1
1826 ,rle.last_update_date = SYSDATE
1827 ,rle.last_updated_by = arp_standard.profile.user_id
1828 ,rle.last_update_login = arp_standard.profile.last_update_login
1829 WHERE rle.object3_id1 IN (SELECT to_char(rcm.duplicate_site_id)
1830 FROM ra_customer_merges rcm
1831 WHERE rcm.process_flag = 'N'
1832 AND rcm.request_id = req_id
1833 AND rcm.set_number = set_number)
1834 AND rle.jtot_object3_code IN (SELECT ojt.object_code
1835 FROM jtf_objects_b ojt
1836 ,jtf_object_usages oue
1837 WHERE ojt.object_code = oue.object_code
1838 AND oue.object_user_code = c_c_site_use)
1839 ;
1840 l_count := sql%rowcount;
1841 arp_message.set_name('AR','AR_ROWS_UPDATED');
1842 arp_message.set_token('NUM_ROWS',to_char(l_count));
1843
1844 -- Start:Code added for Bug 3555739
1845 -- Updating okc_k_headers_b
1846 -- Ship_to_site_use_id
1847 -- Insert into log table
1848 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1849 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1850 MERGE_LOG_ID,
1851 TABLE_NAME,
1852 MERGE_HEADER_ID,
1853 PRIMARY_KEY_ID1,
1854 VCHAR_COL1_ORIG,
1855 VCHAR_COL1_NEW,
1856 REQUEST_ID,
1857 ACTION_FLAG,
1858 CREATED_BY,
1859 CREATION_DATE,
1860 LAST_UPDATE_LOGIN,
1861 LAST_UPDATE_DATE,
1862 LAST_UPDATED_BY
1863 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
1864 'OKC_K_HEADERS_B',
1865 CUSTOMER_MERGE_HEADER_ID,
1866 okh.ID,
1867 to_char(okh.ship_to_site_use_id),
1868 to_char(rcm.customer_site_id),
1869 req_id,
1870 'U',
1871 hz_utility_pub.CREATED_BY,
1872 hz_utility_pub.CREATION_DATE,
1873 hz_utility_pub.LAST_UPDATE_LOGIN,
1874 hz_utility_pub.LAST_UPDATE_DATE,
1875 hz_utility_pub.LAST_UPDATED_BY
1876 FROM OKC_K_HEADERS_B okh, ra_customer_merges rcm
1877 WHERE (
1878 okh.ship_to_site_use_id = rcm.duplicate_site_id
1879 ) AND rcm.process_flag = 'N'
1880 AND rcm.request_id = req_id
1881 AND rcm.set_number = set_number
1882 AND okh.ship_to_site_use_id IN (SELECT rcm.duplicate_site_id
1883 FROM ra_customer_merges rcm
1884 WHERE rcm.process_flag = 'N'
1885 AND rcm.request_id = req_id
1886 AND rcm.set_number = set_number);
1887
1888 End If;
1889
1890 arp_message.set_name('AR','AR_UPDATING_TABLE');
1891 arp_message.set_token('TABLE_NAME','OKC_K_HEADERS_B.SHIP_TO_SITE_USE_ID',FALSE);
1892
1893 UPDATE okc_k_headers_b okh
1894 SET okh.ship_to_site_use_id = (SELECT DISTINCT (rcm.customer_site_id)
1895 FROM ra_customer_merges rcm
1896 WHERE okh.ship_to_site_use_id = rcm.duplicate_site_id
1897 AND rcm.process_flag = 'N'
1898 AND rcm.request_id = req_id
1899 AND rcm.set_number = set_number)
1900 ,okh.object_version_number = okh.object_version_number + 1
1901 ,okh.last_update_date = SYSDATE
1902 ,okh.last_updated_by = arp_standard.profile.user_id
1903 ,okh.last_update_login = arp_standard.profile.last_update_login
1904 WHERE okh.ship_to_site_use_id IN (SELECT DISTINCT (rcm.duplicate_site_id)
1905 FROM ra_customer_merges rcm
1906 WHERE rcm.process_flag = 'N'
1907 AND rcm.request_id = req_id
1908 AND rcm.set_number = set_number);
1909 l_count := sql%rowcount;
1913
1910 arp_message.set_name('AR','AR_ROWS_UPDATED');
1911 arp_message.set_token('NUM_ROWS',to_char(l_count));
1912
1914 -- Updating okc_k_headers_b
1915 -- Bill_to_site_use_id
1916 -- Insert into log table
1917 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1918 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1919 MERGE_LOG_ID,
1920 TABLE_NAME,
1921 MERGE_HEADER_ID,
1922 PRIMARY_KEY_ID1,
1923 VCHAR_COL1_ORIG,
1924 VCHAR_COL1_NEW,
1925 REQUEST_ID,
1926 ACTION_FLAG,
1927 CREATED_BY,
1928 CREATION_DATE,
1929 LAST_UPDATE_LOGIN,
1930 LAST_UPDATE_DATE,
1931 LAST_UPDATED_BY
1932 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
1933 'OKC_K_HEADERS_B',
1934 CUSTOMER_MERGE_HEADER_ID,
1935 okh.ID,
1936 to_char(okh.bill_to_site_use_id),
1937 to_char(rcm.customer_site_id),
1938 req_id,
1939 'U',
1940 hz_utility_pub.CREATED_BY,
1941 hz_utility_pub.CREATION_DATE,
1942 hz_utility_pub.LAST_UPDATE_LOGIN,
1943 hz_utility_pub.LAST_UPDATE_DATE,
1944 hz_utility_pub.LAST_UPDATED_BY
1945 FROM OKC_K_HEADERS_B okh, ra_customer_merges rcm
1946 WHERE (
1947 okh.bill_to_site_use_id = rcm.duplicate_site_id
1948 ) AND rcm.process_flag = 'N'
1949 AND rcm.request_id = req_id
1950 AND rcm.set_number = set_number
1951 AND okh.bill_to_site_use_id IN (SELECT rcm.duplicate_site_id
1952 FROM ra_customer_merges rcm
1953 WHERE rcm.process_flag = 'N'
1954 AND rcm.request_id = req_id
1955 AND rcm.set_number = set_number);
1956
1957 End If;
1958
1959 arp_message.set_name('AR','AR_UPDATING_TABLE');
1960 arp_message.set_token('TABLE_NAME','OKC_K_LINES_B.BILL_TO_SITE_USE_ID',FALSE);
1961
1962 UPDATE okc_k_headers_b okh
1963 SET okh.bill_to_site_use_id = (SELECT DISTINCT (rcm.customer_site_id)
1964 FROM ra_customer_merges rcm
1965 WHERE okh.bill_to_site_use_id = rcm.duplicate_site_id
1966 AND rcm.process_flag = 'N'
1967 AND rcm.request_id = req_id
1968 AND rcm.set_number = set_number)
1969 ,okh.object_version_number = okh.object_version_number + 1
1970 ,okh.last_update_date = SYSDATE
1971 ,okh.last_updated_by = arp_standard.profile.user_id
1972 ,okh.last_update_login = arp_standard.profile.last_update_login
1973 WHERE okh.bill_to_site_use_id IN (SELECT DISTINCT (rcm.duplicate_site_id)
1974 FROM ra_customer_merges rcm
1975 WHERE rcm.process_flag = 'N'
1976 AND rcm.request_id = req_id
1977 AND rcm.set_number = set_number);
1978 l_count := sql%rowcount;
1979 arp_message.set_name('AR','AR_ROWS_UPDATED');
1980 arp_message.set_token('NUM_ROWS',to_char(l_count));
1981
1982 -- Updating okc_k_lines_b
1983 -- Ship_to_site_use_id
1984 -- Insert into log table
1985 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1986 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1987 MERGE_LOG_ID,
1988 TABLE_NAME,
1989 MERGE_HEADER_ID,
1990 PRIMARY_KEY_ID1,
1991 VCHAR_COL1_ORIG,
1992 VCHAR_COL1_NEW,
1993 REQUEST_ID,
1994 ACTION_FLAG,
1995 CREATED_BY,
1996 CREATION_DATE,
1997 LAST_UPDATE_LOGIN,
1998 LAST_UPDATE_DATE,
1999 LAST_UPDATED_BY
2000 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
2001 'OKC_K_LINES_B',
2002 CUSTOMER_MERGE_HEADER_ID,
2003 okl.ID,
2004 to_char(okl.ship_to_site_use_id),
2005 to_char(rcm.customer_site_id),
2006 req_id,
2007 'U',
2008 hz_utility_pub.CREATED_BY,
2009 hz_utility_pub.CREATION_DATE,
2010 hz_utility_pub.LAST_UPDATE_LOGIN,
2011 hz_utility_pub.LAST_UPDATE_DATE,
2012 hz_utility_pub.LAST_UPDATED_BY
2013 FROM OKC_K_LINES_B okl, ra_customer_merges rcm
2014 WHERE (
2015 okl.ship_to_site_use_id = rcm.duplicate_site_id
2016 ) AND rcm.process_flag = 'N'
2017 AND rcm.request_id = req_id
2018 AND rcm.set_number = set_number
2019 AND okl.ship_to_site_use_id IN (SELECT rcm.duplicate_site_id
2020 FROM ra_customer_merges rcm
2021 WHERE rcm.process_flag = 'N'
2022 AND rcm.request_id = req_id
2023 AND rcm.set_number = set_number);
2024
2025 End If;
2026
2027 arp_message.set_name('AR','AR_UPDATING_TABLE');
2028 arp_message.set_token('TABLE_NAME','OKC_K_LINES_B.SHIP_TO_SITE_USE_ID',FALSE);
2029
2030 UPDATE okc_k_lines_b okl
2031 SET okl.ship_to_site_use_id = (SELECT DISTINCT (rcm.customer_site_id)
2032 FROM ra_customer_merges rcm
2033 WHERE okl.ship_to_site_use_id = rcm.duplicate_site_id
2034 AND rcm.process_flag = 'N'
2035 AND rcm.request_id = req_id
2039 ,okl.last_updated_by = arp_standard.profile.user_id
2036 AND rcm.set_number = set_number)
2037 ,okl.object_version_number = okl.object_version_number + 1
2038 ,okl.last_update_date = SYSDATE
2040 ,okl.last_update_login = arp_standard.profile.last_update_login
2041 WHERE okl.ship_to_site_use_id IN (SELECT DISTINCT (rcm.duplicate_site_id)
2042 FROM ra_customer_merges rcm
2043 WHERE rcm.process_flag = 'N'
2044 AND rcm.request_id = req_id
2045 AND rcm.set_number = set_number);
2046 l_count := sql%rowcount;
2047 arp_message.set_name('AR','AR_ROWS_UPDATED');
2048 arp_message.set_token('NUM_ROWS',to_char(l_count));
2049
2050
2051 -- Updating okc_k_lines_b
2052 -- Bill_to_site_use_id
2053 -- Insert into log table
2054 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
2055 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
2056 MERGE_LOG_ID,
2057 TABLE_NAME,
2058 MERGE_HEADER_ID,
2059 PRIMARY_KEY_ID1,
2060 VCHAR_COL1_ORIG,
2061 VCHAR_COL1_NEW,
2062 REQUEST_ID,
2063 ACTION_FLAG,
2064 CREATED_BY,
2065 CREATION_DATE,
2066 LAST_UPDATE_LOGIN,
2067 LAST_UPDATE_DATE,
2068 LAST_UPDATED_BY
2069 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
2070 'OKC_K_LINES_B',
2071 CUSTOMER_MERGE_HEADER_ID,
2072 okl.ID,
2073 to_char(okl.bill_to_site_use_id),
2074 to_char(rcm.customer_site_id),
2075 req_id,
2076 'U',
2077 hz_utility_pub.CREATED_BY,
2078 hz_utility_pub.CREATION_DATE,
2079 hz_utility_pub.LAST_UPDATE_LOGIN,
2080 hz_utility_pub.LAST_UPDATE_DATE,
2081 hz_utility_pub.LAST_UPDATED_BY
2082 FROM OKC_K_LINES_B okl, ra_customer_merges rcm
2083 WHERE (
2084 okl.bill_to_site_use_id = rcm.duplicate_site_id
2085 ) AND rcm.process_flag = 'N'
2086 AND rcm.request_id = req_id
2087 AND rcm.set_number = set_number
2088 AND okl.bill_to_site_use_id IN (SELECT rcm.duplicate_site_id
2089 FROM ra_customer_merges rcm
2090 WHERE rcm.process_flag = 'N'
2091 AND rcm.request_id = req_id
2092 AND rcm.set_number = set_number);
2093
2094 End If;
2095
2096 arp_message.set_name('AR','AR_UPDATING_TABLE');
2097 arp_message.set_token('TABLE_NAME','OKC_K_LINES_B.BILL_TO_SITE_USE_ID',FALSE);
2098
2099 UPDATE okc_k_lines_b okl
2100 SET okl.bill_to_site_use_id = (SELECT DISTINCT (rcm.customer_site_id)
2101 FROM ra_customer_merges rcm
2102 WHERE okl.bill_to_site_use_id = rcm.duplicate_site_id
2103 AND rcm.process_flag = 'N'
2104 AND rcm.request_id = req_id
2105 AND rcm.set_number = set_number)
2106 ,okl.object_version_number = okl.object_version_number + 1
2107 ,okl.last_update_date = SYSDATE
2108 ,okl.last_updated_by = arp_standard.profile.user_id
2109 ,okl.last_update_login = arp_standard.profile.last_update_login
2110 WHERE okl.bill_to_site_use_id IN (SELECT DISTINCT (rcm.duplicate_site_id)
2111 FROM ra_customer_merges rcm
2112 WHERE rcm.process_flag = 'N'
2113 AND rcm.request_id = req_id
2114 AND rcm.set_number = set_number);
2115 l_count := sql%rowcount;
2116 arp_message.set_name('AR','AR_ROWS_UPDATED');
2117 arp_message.set_token('NUM_ROWS',to_char(l_count));
2118
2119
2120 -- Updating okc_k_party_roles_b
2121 -- Bill_to_site_use_id
2122 -- Insert into log table
2123 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
2124 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
2125 MERGE_LOG_ID,
2126 TABLE_NAME,
2127 MERGE_HEADER_ID,
2128 PRIMARY_KEY_ID1,
2129 VCHAR_COL1_ORIG,
2130 VCHAR_COL1_NEW,
2131 REQUEST_ID,
2132 ACTION_FLAG,
2133 CREATED_BY,
2134 CREATION_DATE,
2135 LAST_UPDATE_LOGIN,
2136 LAST_UPDATE_DATE,
2137 LAST_UPDATED_BY
2138 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
2139 'OKC_K_PARTY_ROLES_B',
2140 CUSTOMER_MERGE_HEADER_ID,
2141 okpr.ID,
2142 to_char(okpr.bill_to_site_use_id),
2143 to_char(rcm.customer_site_id),
2144 req_id,
2145 'U',
2146 hz_utility_pub.CREATED_BY,
2147 hz_utility_pub.CREATION_DATE,
2148 hz_utility_pub.LAST_UPDATE_LOGIN,
2149 hz_utility_pub.LAST_UPDATE_DATE,
2150 hz_utility_pub.LAST_UPDATED_BY
2151 FROM OKC_K_PARTY_ROLES_B okpr, ra_customer_merges rcm
2152 WHERE (
2153 okpr.bill_to_site_use_id = rcm.duplicate_site_id
2154 ) AND rcm.process_flag = 'N'
2155 AND rcm.request_id = req_id
2156 AND rcm.set_number = set_number
2157 AND okpr.bill_to_site_use_id IN (SELECT rcm.duplicate_site_id
2158 FROM ra_customer_merges rcm
2159 WHERE rcm.process_flag = 'N'
2163 End If;
2160 AND rcm.request_id = req_id
2161 AND rcm.set_number = set_number);
2162
2164
2165 arp_message.set_name('AR','AR_UPDATING_TABLE');
2166 arp_message.set_token('TABLE_NAME','OKC_K_PARTY_ROLES_B.BILL_TO_SITE_USE_ID',FALSE);
2167
2168 UPDATE okc_k_party_roles_b okpr
2169 SET okpr.bill_to_site_use_id = (SELECT DISTINCT (rcm.customer_site_id)
2170 FROM ra_customer_merges rcm
2171 WHERE okpr.bill_to_site_use_id = rcm.duplicate_site_id
2172 AND rcm.process_flag = 'N'
2173 AND rcm.request_id = req_id
2174 AND rcm.set_number = set_number)
2175 ,okpr.object_version_number = okpr.object_version_number + 1
2176 ,okpr.last_update_date = SYSDATE
2177 ,okpr.last_updated_by = arp_standard.profile.user_id
2178 ,okpr.last_update_login = arp_standard.profile.last_update_login
2179 WHERE okpr.bill_to_site_use_id IN (SELECT DISTINCT (rcm.duplicate_site_id)
2180 FROM ra_customer_merges rcm
2181 WHERE rcm.process_flag = 'N'
2182 AND rcm.request_id = req_id
2183 AND rcm.set_number = set_number);
2184 l_count := sql%rowcount;
2185 arp_message.set_name('AR','AR_ROWS_UPDATED');
2186 arp_message.set_token('NUM_ROWS',to_char(l_count));
2187
2188 -- End:Code added for Bug 3555739
2189
2190 --
2191 -- Account Sites Uses in Items
2192 --
2193 -- Insert into log table
2194 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
2195 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
2196 MERGE_LOG_ID,
2197 TABLE_NAME,
2198 MERGE_HEADER_ID,
2199 PRIMARY_KEY_ID1,
2200 VCHAR_COL1_ORIG,
2201 VCHAR_COL1_NEW,
2202 REQUEST_ID,
2203 ACTION_FLAG,
2204 CREATED_BY,
2205 CREATION_DATE,
2206 LAST_UPDATE_LOGIN,
2207 LAST_UPDATE_DATE,
2208 LAST_UPDATED_BY
2209 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
2210 'OKC_K_ITEMS',
2211 CUSTOMER_MERGE_HEADER_ID,
2212 cim.ID,
2213 cim.object1_id1,
2214 to_char(rcm.customer_site_id),
2215 req_id,
2216 'U',
2217 hz_utility_pub.CREATED_BY,
2218 hz_utility_pub.CREATION_DATE,
2219 hz_utility_pub.LAST_UPDATE_LOGIN,
2220 hz_utility_pub.LAST_UPDATE_DATE,
2221 hz_utility_pub.LAST_UPDATED_BY
2222 FROM OKC_K_ITEMS cim, ra_customer_merges rcm
2223 WHERE (
2224 cim.object1_id1 = to_char(rcm.duplicate_site_id)
2225 ) AND rcm.process_flag = 'N'
2226 AND rcm.request_id = req_id
2227 AND rcm.set_number = set_number
2228 AND cim.object1_id1 IN (SELECT to_char(rcm.duplicate_site_id)
2229 FROM ra_customer_merges rcm
2230 WHERE rcm.process_flag = 'N'
2231 AND rcm.request_id = req_id
2232 AND rcm.set_number = set_number)
2233 AND cim.jtot_object1_code IN (SELECT ojt.object_code
2234 FROM jtf_objects_b ojt
2235 ,jtf_object_usages oue
2236 WHERE ojt.object_code = oue.object_code
2237 AND oue.object_user_code = c_c_site_use);
2238 End If;
2239
2240 arp_message.set_name('AR','AR_UPDATING_TABLE');
2241 arp_message.set_token('TABLE_NAME','OKC_K_ITEMS',FALSE);
2242 UPDATE okc_k_items cim
2243 SET cim.object1_id1 = (SELECT DISTINCT to_char(rcm.customer_site_id)
2244 FROM ra_customer_merges rcm
2245 WHERE cim.object1_id1 = rcm.duplicate_site_id
2246 AND rcm.process_flag = 'N'
2247 AND rcm.request_id = req_id
2248 AND rcm.set_number = set_number)
2249 ,cim.object_version_number = cim.object_version_number + 1
2250 ,cim.last_update_date = SYSDATE
2251 ,cim.last_updated_by = arp_standard.profile.user_id
2252 ,cim.last_update_login = arp_standard.profile.last_update_login
2253 WHERE cim.object1_id1 IN (SELECT to_char(rcm.duplicate_site_id)
2254 FROM ra_customer_merges rcm
2255 WHERE rcm.process_flag = 'N'
2256 AND rcm.request_id = req_id
2257 AND rcm.set_number = set_number)
2258 AND cim.jtot_object1_code IN (SELECT ojt.object_code
2259 FROM jtf_objects_b ojt
2260 ,jtf_object_usages oue
2261 WHERE ojt.object_code = oue.object_code
2262 AND oue.object_user_code = c_c_site_use)
2263 ;
2264 l_count := sql%rowcount;
2265 arp_message.set_name('AR','AR_ROWS_UPDATED');
2266 arp_message.set_token('NUM_ROWS',to_char(l_count));
2267
2268 arp_message.set_line('OKC_HZ_MERGE_PUB.ACCOUNT_SITE_MERGE()-');
2269
2270
2271 END; -- account_site_merge
2272
2273 --
2274 -- main account merge routine
2275 --
2276 PROCEDURE merge_account (req_id IN NUMBER
2277 ,set_number IN NUMBER
2281 -- cursor to get merge reason from merge header
2278 ,process_mode IN VARCHAR2) is
2279
2280 --
2282 -- to be used later
2283 --
2284 CURSOR c_reason IS
2285 SELECT cmh.merge_reason_code
2286 FROM ra_customer_merge_headers cmh
2287 ,ra_customer_merges cme
2288 WHERE cmh.customer_merge_header_id = cme.customer_merge_header_id
2289 AND cme.request_id = req_id
2290 AND cme.set_number = set_number
2291 AND cme.process_flag = 'N'
2292 ;
2293
2294 --
2295 -- cursor to determine if the merge is an account merge,
2296 -- or a site merge within the same account
2297 --
2298 CURSOR c_site_merge(b_request_id NUMBER, b_set_number NUMBER) IS
2299 SELECT customer_id, duplicate_id
2300 FROM ra_customer_merges cme
2301 WHERE cme.request_id = b_request_id
2302 AND cme.set_number = b_set_number
2303 AND cme.process_flag = 'N'
2304 ;
2305
2306 --
2307 -- cursor to find party id given the account id
2308 --
2309 CURSOR c_party_id (b_account_id NUMBER) IS
2310 SELECT party_id
2311 FROM hz_cust_accounts
2312 WHERE cust_account_id = b_account_id
2313 ;
2314 --
2315 -- cursor to find if any contract is with the party of the
2316 -- merged account
2317 --
2318 CURSOR c_cpr (b_party_id NUMBER) IS
2319 SELECT kpr.dnz_chr_id
2320 FROM okc_k_party_roles_b kpr
2321 WHERE kpr.jtot_object1_code IN (SELECT ojt.object_code
2322 FROM jtf_objects_b ojt
2323 ,jtf_object_usages oue
2324 WHERE ojt.object_code = oue.object_code
2325 AND oue.object_user_code = c_party
2326 )
2327 AND kpr.object1_id1 = to_char(b_party_id)
2328 ;
2329 --
2330 -- cursor to find if any contract references a party of the
2331 -- merged account in a contract line
2332 --
2333 CURSOR c_cim (b_party_id NUMBER) IS
2334 SELECT cim.dnz_chr_id
2335 FROM okc_k_items cim
2336 WHERE cim.jtot_object1_code IN (SELECT ojt.object_code
2337 FROM jtf_objects_b ojt
2338 ,jtf_object_usages oue
2339 WHERE ojt.object_code = oue.object_code
2340 AND oue.object_user_code = c_party
2341 )
2342 AND cim.object1_id1 = to_char(b_party_id)
2343 ;
2344 --
2345 -- local variables
2346 --
2347 l_merge_reason ra_customer_merge_headers.merge_reason_code%type;
2348 l_customer_id ra_customer_merge_headers.customer_id%type;
2349 l_duplicate_id ra_customer_merge_headers.duplicate_id%type;
2350 l_source_party_id hz_parties.party_id%type;
2351 l_target_party_id hz_parties.party_id%type;
2352 l_chr_id okc_k_party_roles_b.dnz_chr_id%type;
2353 l_error_msg VARCHAR2(2000);
2354
2355 l_merge_disallowed_excp EXCEPTION;
2356 l_no_data_found_excp EXCEPTION;
2357 l_lock_excp EXCEPTION;
2358
2359 BEGIN
2360 arp_message.set_line('OKC_HZ_MERGE_PUB.MERGE_ACCOUNT()+');
2361
2362 --
2363 -- check process mode. If LOCK, then just lock the tables
2364 --
2365 IF process_mode = 'LOCK' THEN
2366 lock_tables(req_id => req_id
2367 ,set_number => set_number);
2368 --
2369 -- that's it, exit
2370 --
2371 raise l_lock_excp;
2372 END IF;
2373
2374 --
2375 -- determine if account merge or site merge within account
2376 --
2377 OPEN c_site_merge(req_id, set_number);
2378 FETCH c_site_merge INTO l_customer_id, l_duplicate_id;
2379 IF c_site_merge%NOTFOUND THEN
2380 CLOSE c_site_merge;
2381 RAISE l_no_data_found_excp;
2382 END IF;
2383
2384 IF l_customer_id <> l_duplicate_id THEN -- this is an account merge
2385 --
2386 -- must first determine if accounts are merged within the same party
2387 -- so get the two party ids
2388 --
2389 OPEN c_party_id(l_duplicate_id);
2390 FETCH c_party_id INTO l_source_party_id;
2391 IF c_party_id%NOTFOUND THEN
2392 CLOSE c_party_id;
2393 RAISE l_no_data_found_excp;
2394 END IF;
2395 CLOSE c_party_id;
2396
2397 OPEN c_party_id(l_customer_id);
2398 FETCH c_party_id INTO l_target_party_id;
2399 IF c_party_id%NOTFOUND THEN
2400 CLOSE c_party_id;
2401 RAISE l_no_data_found_excp;
2402 END IF;
2403 CLOSE c_party_id;
2404
2405 IF l_source_party_id <> l_target_party_id THEN
2406 -- merge across parties, update party info if there is a contract for
2407 -- the source party
2408 OPEN c_cpr (l_source_party_id);
2409 FETCH c_cpr INTO l_chr_id;
2410 IF c_cpr%FOUND THEN
2411 party_merge(req_id => req_id
2412 ,set_number => set_number
2413 ,l_source_party_id => l_source_party_id
2414 ,l_target_party_id => l_target_party_id
2415 ,l_duplicate_id => l_duplicate_id );
2416
2417 --CLOSE c_cpr;
2418 --RAISE l_merge_disallowed_excp; -- do not allow merge
2419 END IF;
2420 CLOSE c_cpr;
2421 --
2422 -- Below code is commented on May 7 2002
2423 -- check to see if party is referenced in any line
2424 -- if so, disallow account merge
2425 --
2426 --OPEN c_cim (l_source_party_id);
2427 --FETCH c_cim INTO l_chr_id;
2428 --IF c_cim%FOUND THEN
2429 --CLOSE c_cim;
2430 --RAISE l_merge_disallowed_excp; -- do not allow merge
2431 --END IF;
2432 --CLOSE c_cim;
2433 --
2434 -- party is not used in a contract
2435 --
2436 END IF; -- l_source_party_id <> l_target_party_id
2437 --
2438 -- to get here, either the party ids are the same
2439 -- or the "duplicate" party is not a contract party
2440 -- either way, do the account merge
2441 account_merge(req_id => req_id
2442 ,set_number => set_number);
2443 account_site_merge(req_id => req_id
2444 ,set_number => set_number);
2445 ELSE -- customer ids the same, this is an account site merge
2446 account_site_merge(req_id => req_id
2447 ,set_number => set_number);
2448 END IF; -- if customer ids the same
2449
2450 arp_message.set_line('OKC_HZ_MERGE_PUB.MERGE_ACCOUNT()-');
2451
2452 EXCEPTION
2453 WHEN l_merge_disallowed_excp THEN
2454 arp_message.set_line('Contract exists for duplicate party, merge cannot proceed');
2455 arp_message.set_error('OKC_HZ_MERGE_PUB.MERGE_ACCOUNT');
2456 RAISE;
2457 WHEN l_no_data_found_excp THEN
2458 arp_message.set_line('No data found for merge information');
2459 arp_message.set_error('OKC_HZ_MERGE_PUB.MERGE_ACCOUNT');
2460 RAISE;
2461 WHEN l_lock_excp THEN -- normal exit after locking
2462 arp_message.set_line('OKC_HZ_MERGE_PUB.MERGE_ACCOUNT()-');
2463 WHEN others THEN
2464 l_error_msg := substr(SQLERRM,1,70);
2465 arp_message.set_error('OKC_HZ_MERGE_PUB.MERGE_ACCOUNT', l_error_msg);
2466 RAISE;
2467 END; -- merge_account
2468
2469 END; -- Package Body OKC_HZ_MERGE_PUB