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