4 -- Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA
1 Package Body OKS_HZ_MERGE_PUB AS
2 /* $Header: OKSPMRGB.pls 120.0 2005/05/25 18:02:52 appldev noship $ */
3 --
5 -- All rights reserved.
6 --
7 -- Start of Comments
8 -- API Name :OKS_HZ_MERGE_PUB
9 -- Type :Public
10 -- Purpose :Manage customer and party merges
11 --
12 -- Modification History
13 -- 13-Dec-00 mconnors created
17 -- 17-Aug-04 chkrishn OKS_QUALIFIERS update for bug 3816822
14 -- 24-JUl-02 chkrishn included logging for customer merge
15 -- 22-May-03 chkrishn uptook tca logging changes for customer merge
16 -- 18-Mar-04 chkrishn included quote_to_site_id update for rules rearchitecture
18 --
19 -- NOTES
20 -- Merging Rules:
21 -- OKS will not allow an account merge across parties when the source account
22 -- (the duplicate) is referenced in OKS_BILLING_PROFILES_B. To do so will invalidate
23 -- the party - account relationship in this table.
24 --
25 -- OKS will allow an account merge in other cases.
26 --
27 -- When merging accounts, customer account ids are looked for in:
28 -- OKS_BILLING_PROFILES_B
29 --
30 -- When merging sites, customer site use ids are looked for in:
31 -- OKS_BILLING_PROFILES_B
32 --
33 -- JTF Objects:
34 -- The merge depends upon the proper usages being set for the JTF objects used
35 -- to represent parties, party site, accounts and account sites.
36 -- These usages are as follows:
37 -- OKX_PARTY This object is based on a view which returns the
38 -- party_id as id1.
39 -- OKX_P_SITE This object is based on a view which returns
40 -- party_site_id as id1.
41 -- OKX_P_SITE_USE This object is based on a view which returns
42 -- party_site_use_id as id1.
43 -- OKX_ACCOUNT This object is based on a view which returns
44 -- cust_account_id as id1.
45 -- OKX_C_SITE This object is based on a view which returns
46 -- cust_acct_site_id as id1.
47 -- OKX_C_SITE_USE This object is based on a view which returns
48 -- site_use_id as id1.
49 -- The usages are how the merge determines which jtot_object_codes are candidates
50 -- for the different types of merges.
51 --
52 --
53 -- End of comments
54
55
56 -- Global constants
57 c_party CONSTANT VARCHAR2(20) := 'OKX_PARTY';
58 c_p_site CONSTANT VARCHAR2(20) := 'OKX_P_SITE';
59 c_p_site_use CONSTANT VARCHAR2(20) := 'OKX_P_SITE_USE';
60 c_account CONSTANT VARCHAR2(20) := 'OKX_ACCOUNT';
61 c_c_site CONSTANT VARCHAR2(20) := 'OKX_C_SITE';
62 c_c_site_use CONSTANT VARCHAR2(20) := 'OKX_C_SITE_USE';
63
64 --
65 -- routine to lock tables when process mode = 'LOCK'
66 -- if table cannot be locked, goes back to caller as exception
67 PROCEDURE lock_tables (req_id IN NUMBER
68 ,set_number IN NUMBER) IS
69 --
70 -- cursors to lock tables
71 --
72 CURSOR c_lock_bpe IS
73 SELECT 1
74 FROM oks_billing_profiles_b bpe
75 WHERE bpe.dependent_cust_acct_id1 IN (SELECT cme.duplicate_id
76 FROM ra_customer_merges cme
77 WHERE cme.process_flag = 'N'
78 AND cme.request_id = req_id
79 AND cme.set_number = set_number
80 )
81 FOR UPDATE NOWAIT;
82
83 BEGIN
84 arp_message.set_line('OKS_HZ_MERGE_PUB.LOCK_TABLES()+');
85
86 -- billing profiles
87 arp_message.set_name('AR','AR_LOCKING_TABLE');
88 arp_message.set_token('TABLE_NAME','OKS_BILLING_PROFILES_B',FALSE);
89 open c_lock_bpe;
90 close c_lock_bpe;
91
92 arp_message.set_line('OKS_HZ_MERGE_PUB.LOCK_TABLES()-');
93
94 END; -- lock_tables
95
96 --
97 -- sub routine to merge accounts
98 -- exceptions are unhandled, sent back to caller
99 --
100 PROCEDURE account_merge(req_id IN NUMBER
101 ,set_number IN NUMBER) IS
102
103 l_count NUMBER;
104
105
106 --CK add logging
107 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
108 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
109 INDEX BY BINARY_INTEGER;
110 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
111
112 TYPE ID_LIST_TYPE IS TABLE OF
113 OKS_BILLING_PROFILES_B.ID%TYPE
114 INDEX BY BINARY_INTEGER;
115 PRIMARY_KEY_ID1_LIST ID_LIST_TYPE;
116
117 TYPE DEP_CUST_ACCT_ID1_LIST_TYPE IS TABLE OF
118 OKS_BILLING_PROFILES_B.DEPENDENT_CUST_ACCT_ID1%TYPE
119 INDEX BY BINARY_INTEGER;
120 NUM_COL1_ORIG_LIST DEP_CUST_ACCT_ID1_LIST_TYPE ;
121 NUM_COL1_NEW_LIST DEP_CUST_ACCT_ID1_LIST_TYPE ;
122
123 TYPE OBJECT_VER_NUMBER_LIST_TYPE IS TABLE OF
124 OKS_BILLING_PROFILES_B.OBJECT_VERSION_NUMBER%TYPE
125 INDEX BY BINARY_INTEGER;
126 NUM_COL2_ORIG_LIST OBJECT_VER_NUMBER_LIST_TYPE ;
127 NUM_COL2_NEW_LIST OBJECT_VER_NUMBER_LIST_TYPE ;
128
129 l_profile_val VARCHAR2(30);
130 CURSOR merged_records IS
131 SELECT distinct CUSTOMER_MERGE_HEADER_ID
132 ,ID
133 ,DEPENDENT_CUST_ACCT_ID1
134 ,OBJECT_VERSION_NUMBER
135 FROM OKS_BILLING_PROFILES_B yt, ra_customer_merges m
136 WHERE (
137 yt.DEPENDENT_CUST_ACCT_ID1 = m.DUPLICATE_ID
138 OR yt.OBJECT_VERSION_NUMBER = m.DUPLICATE_SITE_ID
139 ) AND m.process_flag = 'N'
140 AND m.request_id = req_id
141 AND m.set_number = set_number;
142 l_last_fetch BOOLEAN := FALSE;
143 --
144
145 BEGIN
146 --CK new code with logging
147 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
148 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','OKS_BILLING_PROFILES_B',FALSE);
152 open merged_records;
149 HZ_ACCT_MERGE_UTIL.load_set(set_number, req_id);
150 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
151
153 LOOP
154 FETCH merged_records BULK COLLECT INTO
155 MERGE_HEADER_ID_LIST
156 , PRIMARY_KEY_ID1_LIST
157 , NUM_COL1_ORIG_LIST
158 , NUM_COL2_ORIG_LIST
159 limit 1000;
160 IF merged_records%NOTFOUND THEN
161 l_last_fetch := TRUE;
162 END IF;
163 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
164 exit;
165 END IF;
166 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
167 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
168 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
169 END LOOP;
170 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
171 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
172 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
173 MERGE_LOG_ID,
174 TABLE_NAME,
175 MERGE_HEADER_ID,
176 PRIMARY_KEY_ID1,
177 NUM_COL1_ORIG,
178 NUM_COL1_NEW,
179 NUM_COL2_ORIG,
180 NUM_COL2_NEW,
181 ACTION_FLAG,
182 REQUEST_ID,
183 CREATED_BY,
184 CREATION_DATE,
185 LAST_UPDATE_LOGIN,
186 LAST_UPDATE_DATE,
187 LAST_UPDATED_BY
188 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
189 'OKS_BILLING_PROFILES_B',
190 MERGE_HEADER_ID_LIST(I),
191 PRIMARY_KEY_ID1_LIST(I),
192 NUM_COL1_ORIG_LIST(I),
193 NUM_COL1_NEW_LIST(I),
194 NUM_COL2_ORIG_LIST(I),
195 NUM_COL2_NEW_LIST(I),
196 'U',
197 req_id,
198 hz_utility_pub.CREATED_BY,
199 hz_utility_pub.CREATION_DATE,
200 hz_utility_pub.LAST_UPDATE_LOGIN,
201 hz_utility_pub.LAST_UPDATE_DATE,
202 hz_utility_pub.LAST_UPDATED_BY
203 );
204 END IF;
205 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
206 UPDATE OKS_BILLING_PROFILES_B yt SET
207 DEPENDENT_CUST_ACCT_ID1=NUM_COL1_NEW_LIST(I)
208 ,OBJECT_VERSION_NUMBER=NUM_COL2_NEW_LIST(I)
209 , LAST_UPDATE_DATE=SYSDATE
210 , last_updated_by=arp_standard.profile.user_id
211 , last_update_login=arp_standard.profile.last_update_login
212 WHERE ID=PRIMARY_KEY_ID1_LIST(I)
213 ;
214 l_count := l_count + SQL%ROWCOUNT;
215 IF l_last_fetch THEN
216 EXIT;
217 END IF;
218 END LOOP;
219
220 arp_message.set_name('AR','AR_ROWS_UPDATED');
221 arp_message.set_token('NUM_ROWS',to_char(l_count));
222 -- billing profile
223 /*CK old code
224 arp_message.set_name('AR','AR_UPDATING_TABLE');
225 arp_message.set_token('TABLE_NAME','OKS_BILLING_PROFILES_B',FALSE);
226 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
227 MERGE_LOG_ID,
228 MERGE_HEADER_ID,
229 TABLE_NAME,
230 PRIMARY_KEY_ID,
231 NUM_COL1_ORIG,
232 NUM_COL1_NEW,
233 NUM_COL2_ORIG,
234 NUM_COL2_NEW,
235 REQUEST_ID
236 ) SELECT HZ_CUSTOMER_MERGE_LOG_s.nextval,
237 CUSTOMER_MERGE_HEADER_ID,
238 'OKS_BILLING_PROFILES_B',
239 ID,
240 DEPENDENT_CUST_ACCT_ID1,
241 decode(yt.DEPENDENT_CUST_ACCT_ID1,m.DUPLICATE_ID,m.CUSTOMER_ID,yt.DEPENDENT_CUST_ACCT_ID1),
242 OBJECT_VERSION_NUMBER,
243 OBJECT_VERSION_NUMBER,
244 request_id
245 FROM OKS_BILLING_PROFILES_B yt, ra_customer_merges m
246 WHERE (
247 yt.DEPENDENT_CUST_ACCT_ID1 = m.DUPLICATE_ID
248 -- OR yt.BILL_TO_ADDRESS_ID1 = m.DUPLICATE_SITE_ID
249 ) AND m.process_flag = 'N'
250 AND m.request_id = req_id
251 AND m.set_number = set_number;
252
253 UPDATE OKS_BILLING_PROFILES_B yt SET (
254 DEPENDENT_CUST_ACCT_ID1, OBJECT_VERSION_NUMBER) = (
255 SELECT NUM_COL1_NEW, NUM_COL2_NEW
256 FROM HZ_CUSTOMER_MERGE_LOG l
257 WHERE l.REQUEST_ID = req_id
258 AND l.TABLE_NAME = 'OKS_BILLING_PROFILES_B'
259 AND l.PRIMARY_KEY_ID = ID
260 AND DEPENDENT_CUST_ACCT_ID1 = NUM_COL1_ORIG
261 and rownum <2
262 )
263 , LAST_UPDATE_DATE=SYSDATE
264 , last_updated_by=arp_standard.profile.user_id
265 , last_update_login=arp_standard.profile.last_update_login
266 WHERE (ID) in (
267 SELECT PRIMARY_KEY_ID
268 FROM HZ_CUSTOMER_MERGE_LOG l1, RA_CUSTOMER_MERGES h
269 WHERE h.CUSTOMER_MERGE_HEADER_ID = l1.MERGE_HEADER_ID
270 AND l1.TABLE_NAME = 'OKS_BILLING_PROFILES_B'
271 AND l1.REQUEST_ID = req_id
272 AND h.set_number = set_number);
273 l_count := SQL%ROWCOUNT;
274
275 arp_message.set_name('AR','AR_ROWS_UPDATED');
276 arp_message.set_token('NUM_ROWS',to_char(l_count));
277 arp_message.set_line('OKS_HZ_MERGE_PUB.ACCOUNT_MERGE()-');CK old code*/
278 END; -- account_merge
279
280 --
281 -- sub routine to merge account sites and site uses
282 -- exceptions are unhandled, sent back to caller
283 --
284 PROCEDURE account_site_merge (req_id IN NUMBER
285 ,set_number IN NUMBER) IS
286
287 l_count NUMBER;
288 --CK logging
289 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
290 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
291 INDEX BY BINARY_INTEGER;
292 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
296 PRIMARY_KEY_ID1_LIST ID_LIST_TYPE;
293 TYPE ID_LIST_TYPE IS TABLE OF
294 OKS_BILLING_PROFILES_B.ID%TYPE
295 INDEX BY BINARY_INTEGER;
297
298 TYPE BILL_TO_ADDRESS_ID1_LIST_TYPE IS TABLE OF
299 OKS_BILLING_PROFILES_B.BILL_TO_ADDRESS_ID1%TYPE
300 INDEX BY BINARY_INTEGER;
301
302 NUM_COL3_ORIG_LIST BILL_TO_ADDRESS_ID1_LIST_TYPE;
303 NUM_COL3_NEW_LIST BILL_TO_ADDRESS_ID1_LIST_TYPE;
304
305 TYPE OBJECT_VERSION_NUM_LIST_TYPE IS TABLE OF
306 OKS_BILLING_PROFILES_B.OBJECT_VERSION_NUMBER%TYPE
307 INDEX BY BINARY_INTEGER;
308 NUM_COL4_ORIG_LIST OBJECT_VERSION_NUM_LIST_TYPE;
309 NUM_COL4_NEW_LIST OBJECT_VERSION_NUM_LIST_TYPE;
310
311 --03/15/2004
312 MERGE_HEADER_ID_LIST_QUOTE MERGE_HEADER_ID_LIST_TYPE;
313 PRIMARY_KEY_ID1_LIST_QUOTE ID_LIST_TYPE;
314 TYPE QUOTE_TO_SITE_ID_LIST_TYPE IS TABLE OF
315 OKS_K_HEADERS_B.QUOTE_TO_SITE_ID%TYPE
316 INDEX BY BINARY_INTEGER;
317 NUM_COL1_ORIG_LIST_QUOTE QUOTE_TO_SITE_ID_LIST_TYPE;
318 NUM_COL1_NEW_LIST_QUOTE QUOTE_TO_SITE_ID_LIST_TYPE;
319 TYPE OBJECT_VERSION_NUM_LIST_TYPE_Q IS TABLE OF
320 OKS_K_HEADERS_B.OBJECT_VERSION_NUMBER%TYPE
321 INDEX BY BINARY_INTEGER;
322 NUM_COL2_ORIG_LIST_QUOTE OBJECT_VERSION_NUM_LIST_TYPE_Q;
323 NUM_COL2_NEW_LIST_QUOTE OBJECT_VERSION_NUM_LIST_TYPE_Q;
324
325 --03/15/2004
326 l_profile_val VARCHAR2(30);
327 CURSOR merged_records IS
328 SELECT distinct CUSTOMER_MERGE_HEADER_ID
329 ,ID
330 ,BILL_TO_ADDRESS_ID1
331 ,OBJECT_VERSION_NUMBER
332 FROM OKS_BILLING_PROFILES_B yt, ra_customer_merges m
333 WHERE (
334 yt.BILL_TO_ADDRESS_ID1 = m.DUPLICATE_SITE_ID
335 ) AND m.process_flag = 'N'
336 AND m.request_id = req_id
337 AND m.set_number = set_number;
338 l_last_fetch BOOLEAN := FALSE;
339
340 --03/15/2004
341 CURSOR merged_records_quote IS
342 SELECT distinct CUSTOMER_MERGE_HEADER_ID
343 ,ID
344 ,QUOTE_TO_SITE_ID
345 ,OBJECT_VERSION_NUMBER
346 FROM OKS_K_HEADERS_B hdr, ra_customer_merges m
347 WHERE (
348 hdr.QUOTE_TO_SITE_ID = m.DUPLICATE_ADDRESS_ID
349 ) AND m.process_flag = 'N'
350 AND m.request_id = req_id
351 AND m.set_number = set_number;
352 --03/15/2004
353 --CK
354
355 BEGIN
356 --ck new code with logging
357 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
358 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','OKS_BILLING_PROFILES_B',FALSE);
359 HZ_ACCT_MERGE_UTIL.load_set(set_number, req_id);
360 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
361 open merged_records;
362 LOOP
363 FETCH merged_records BULK COLLECT INTO
364 MERGE_HEADER_ID_LIST
365 , PRIMARY_KEY_ID1_LIST
366 , NUM_COL3_ORIG_LIST
367 , NUM_COL4_ORIG_LIST
368 limit 1000;
369 IF merged_records%NOTFOUND THEN
370 l_last_fetch := TRUE;
371 END IF;
372 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
373 exit;
374 END IF;
375 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
376 NUM_COL3_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL3_ORIG_LIST(I));
377 NUM_COL4_NEW_LIST(I) := NUM_COL4_ORIG_LIST(I);
378 END LOOP;
379 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
380 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
381 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
382 MERGE_LOG_ID,
383 TABLE_NAME,
384 MERGE_HEADER_ID,
385 PRIMARY_KEY_ID1,
386 NUM_COL3_ORIG,
387 NUM_COL3_NEW,
388 NUM_COL4_ORIG,
389 NUM_COL4_NEW,
390 ACTION_FLAG,
391 REQUEST_ID,
392 CREATED_BY,
393 CREATION_DATE,
394 LAST_UPDATE_LOGIN,
395 LAST_UPDATE_DATE,
396 LAST_UPDATED_BY
397 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
398 'OKS_BILLING_PROFILES_B',
399 MERGE_HEADER_ID_LIST(I),
400 PRIMARY_KEY_ID1_LIST(I),
401 NUM_COL3_ORIG_LIST(I),
402 NUM_COL3_NEW_LIST(I),
403 NUM_COL4_ORIG_LIST(I),
404 NUM_COL4_NEW_LIST(I),
405 'U',
406 req_id,
407 hz_utility_pub.CREATED_BY,
408 hz_utility_pub.CREATION_DATE,
409 hz_utility_pub.LAST_UPDATE_LOGIN,
410 hz_utility_pub.LAST_UPDATE_DATE,
411 hz_utility_pub.LAST_UPDATED_BY
412 );
413
414 END IF;
415 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
416 UPDATE OKS_BILLING_PROFILES_B yt SET
417 BILL_TO_ADDRESS_ID1=NUM_COL3_NEW_LIST(I)
418 ,OBJECT_VERSION_NUMBER=NUM_COL4_NEW_LIST(I)
419 , LAST_UPDATE_DATE=SYSDATE
420 , last_updated_by=arp_standard.profile.user_id
421 , last_update_login=arp_standard.profile.last_update_login
422 WHERE ID=PRIMARY_KEY_ID1_LIST(I)
423 ;
424
425 --l_count := l_count + SQL%ROWCOUNT;
426 l_count := SQL%ROWCOUNT;
427 IF l_last_fetch THEN
428 EXIT;
429 END IF;
430 END LOOP;
431 arp_message.set_name('AR','AR_ROWS_UPDATED');
432 arp_message.set_token('NUM_ROWS',to_char(l_count));
433
434 --03/15/2004 added code to update quote_to_site_id
435 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
436 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','OKS_K_HEADERS_B',FALSE);
437 open merged_records_quote;
438 LOOP
442 , NUM_COL1_ORIG_LIST_QUOTE
439 FETCH merged_records_quote BULK COLLECT INTO
440 MERGE_HEADER_ID_LIST_QUOTE
441 , PRIMARY_KEY_ID1_LIST_QUOTE
443 , NUM_COL2_ORIG_LIST_QUOTE
444 limit 1000;
445 IF merged_records_quote%NOTFOUND THEN
446 l_last_fetch := TRUE;
447 END IF;
448 IF MERGE_HEADER_ID_LIST_QUOTE.COUNT = 0 and l_last_fetch then
449 exit;
450 END IF;
451 FOR I in 1..MERGE_HEADER_ID_LIST_QUOTE.COUNT LOOP
452 -- NUM_COL1_NEW_LIST_QUOTE(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL1_ORIG_LIST_QUOTE(I));
453 NUM_COL1_NEW_LIST_QUOTE(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE(NUM_COL1_ORIG_LIST_QUOTE(I));
454 NUM_COL2_NEW_LIST_QUOTE(I) := NUM_COL2_ORIG_LIST_QUOTE(I);
455 END LOOP;
456 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
457 FORALL I in 1..MERGE_HEADER_ID_LIST_QUOTE.COUNT
458 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
459 MERGE_LOG_ID,
460 TABLE_NAME,
461 MERGE_HEADER_ID,
462 PRIMARY_KEY_ID1,
463 NUM_COL1_ORIG,
464 NUM_COL1_NEW,
465 NUM_COL2_ORIG,
466 NUM_COL2_NEW,
467 ACTION_FLAG,
468 REQUEST_ID,
469 CREATED_BY,
470 CREATION_DATE,
471 LAST_UPDATE_LOGIN,
472 LAST_UPDATE_DATE,
473 LAST_UPDATED_BY
474 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
475 'OKS_K_HEADERS_B',
476 MERGE_HEADER_ID_LIST_QUOTE(I),
477 PRIMARY_KEY_ID1_LIST_QUOTE(I),
478 NUM_COL1_ORIG_LIST_QUOTE(I),
479 NUM_COL1_NEW_LIST_QUOTE(I),
480 NUM_COL2_ORIG_LIST_QUOTE(I),
481 NUM_COL2_NEW_LIST_QUOTE(I),
482 'U',
483 req_id,
484 hz_utility_pub.CREATED_BY,
485 hz_utility_pub.CREATION_DATE,
486 hz_utility_pub.LAST_UPDATE_LOGIN,
487 hz_utility_pub.LAST_UPDATE_DATE,
488 hz_utility_pub.LAST_UPDATED_BY
489 );
490
491 END IF;
492
493 FORALL I in 1..MERGE_HEADER_ID_LIST_QUOTE.COUNT
494 UPDATE OKS_K_HEADERS_B yt SET
495 QUOTE_TO_SITE_ID=NUM_COL1_NEW_LIST_QUOTE(I)
496 ,OBJECT_VERSION_NUMBER=NUM_COL2_NEW_LIST_QUOTE(I)
497 , LAST_UPDATE_DATE=SYSDATE
498 , last_updated_by=arp_standard.profile.user_id
499 , last_update_login=arp_standard.profile.last_update_login
500 WHERE ID=PRIMARY_KEY_ID1_LIST_QUOTE(I);
501
502 l_count := SQL%ROWCOUNT;
503 arp_message.set_name('AR','AR_ROWS_UPDATED');
504 arp_message.set_token('NUM_ROWS',to_char(l_count));
505
506 IF l_last_fetch THEN
507 EXIT;
508 END IF;
509 END LOOP;
510
511 --Added for updating OKS_QUALIFIERS table during account merge
512 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
513 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','OKS_QUALIFIERS',FALSE);
514
515 OKS_QP_INT_PVT.QUALIFIER_ACCOUNT_MERGE
516 (req_id =>req_id,
517 set_num =>set_number);
518
519 END; -- account_site_merge
520
521 --
522 -- main account merge routine
523 --
524 PROCEDURE merge_account (req_id IN NUMBER
525 ,set_number IN NUMBER
526 ,process_mode IN VARCHAR2) is
527
528 --
529 -- cursor to get merge reason from merge header
530 -- to be used later
531 --
532 CURSOR c_reason IS
533 SELECT cmh.merge_reason_code
534 FROM ra_customer_merge_headers cmh
535 ,ra_customer_merges cme
536 WHERE cmh.customer_merge_header_id = cme.customer_merge_header_id
537 AND cme.request_id = req_id
538 AND cme.set_number = set_number
539 AND cme.process_flag = 'N'
540 ;
541
542 --
543 -- cursor to determine if the merge is an account merge,
544 -- or a site merge within the same account
545 --
546 CURSOR c_site_merge(b_request_id NUMBER, b_set_number NUMBER) IS
547 SELECT customer_id, duplicate_id
548 FROM ra_customer_merges cme
549 WHERE cme.request_id = req_id
550 AND cme.set_number = set_number
551 AND cme.process_flag = 'N'
552 ;
553
554 --
555 -- cursort to find party id given the account id
556 --
557 CURSOR c_party_id (b_account_id NUMBER) IS
558 SELECT party_id
559 FROM hz_cust_accounts
560 WHERE cust_account_id = b_account_id
561 ;
562 --
563 -- cursor to find if any contract is with the party of the
564 -- merged account
565 --
566 CURSOR c_bpe (b_party_id NUMBER) IS
567 SELECT 1
568 FROM oks_billing_profiles_b bpe
569 WHERE bpe.owned_party_id1 = b_party_id
570 ;
571 --
572 -- local variables
573 --
574 l_merge_reason ra_customer_merge_headers.merge_reason_code%type;
575 l_customer_id ra_customer_merge_headers.customer_id%type;
576 l_duplicate_id ra_customer_merge_headers.duplicate_id%type;
577 l_source_party_id hz_parties.party_id%type;
578 l_target_party_id hz_parties.party_id%type;
579 l_temp NUMBER;
580 l_error_msg VARCHAR2(2000);
581
582 l_merge_disallowed_excp EXCEPTION;
583 l_no_data_found_excp EXCEPTION;
584 l_lock_excp EXCEPTION;
585
586 BEGIN
587 arp_message.set_line('OKS_HZ_MERGE_PUB.MERGE_ACCOUNT()+');
588
589 --
590 -- check process mode. If LOCK, then just lock the tables
591 --
592 IF process_mode = 'LOCK' THEN
596 -- that's it, exit
593 lock_tables(req_id => req_id
594 ,set_number => set_number);
595 --
597 --
598 raise l_lock_excp;
599 END IF;
600
601 --
602 -- determine if account merge or site merge within account
603 --
604 OPEN c_site_merge(req_id, set_number);
605 FETCH c_site_merge INTO l_customer_id, l_duplicate_id;
606 IF c_site_merge%NOTFOUND THEN
607 CLOSE c_site_merge;
608 RAISE l_no_data_found_excp;
609 END IF;
610
611 IF l_customer_id <> l_duplicate_id THEN -- this is an account merge
612 --
613 -- must first determine if accounts are merged within the same party
614 -- so get the two party ids
615 --
616 OPEN c_party_id(l_duplicate_id);
617 FETCH c_party_id INTO l_source_party_id;
618 IF c_party_id%NOTFOUND THEN
619 CLOSE c_party_id;
620 RAISE l_no_data_found_excp;
621 END IF;
622 CLOSE c_party_id;
623
624 OPEN c_party_id(l_customer_id);
625 FETCH c_party_id INTO l_target_party_id;
626 IF c_party_id%NOTFOUND THEN
627 CLOSE c_party_id;
628 RAISE l_no_data_found_excp;
629 END IF;
630 CLOSE c_party_id;
631
632 IF l_source_party_id <> l_target_party_id THEN
633 -- merge across parties, disallow if the party has a billing profile
634 OPEN c_bpe(l_source_party_id);
635 FETCH c_bpe INTO l_temp;
636 IF c_bpe%FOUND THEN
637 CLOSE c_bpe;
638 RAISE l_merge_disallowed_excp; -- do not allow merge
639 END IF;
640 CLOSE c_bpe;
641 --
642 -- party is not used in a billing profile
643 --
644 END IF; -- l_source_party_id <> l_target_party_id
645 --
646 -- to get here, either the party ids are the same
647 -- or the "duplicate" party is not in a billing profile
648 -- either way, do the account merge
649 account_merge(req_id => req_id
650 ,set_number => set_number);
651 account_site_merge(req_id => req_id
652 ,set_number => set_number);
653 ELSE -- customer ids the same, this is an account site merge
654 account_site_merge(req_id => req_id
655 ,set_number => set_number);
656 END IF; -- if customer ids the same
657
658 arp_message.set_line('OKS_HZ_MERGE_PUB.MERGE_ACCOUNT()-');
659
660 EXCEPTION
661 WHEN l_merge_disallowed_excp THEN
662 -- arp_message.set_line('Billing Profile exists for duplicate party, merge cannot proceed');
663 arp_message.set_line('Cannot Merge Customer Accounts owned by different parties. Please run Party Merge first and then run Customer Merge');
664 arp_message.set_error('OKS_HZ_MERGE_PUB.MERGE_ACCOUNT');
665 RAISE;
666 WHEN l_no_data_found_excp THEN
667 arp_message.set_line('No data found for merge information');
668 arp_message.set_error('OKS_HZ_MERGE_PUB.MERGE_ACCOUNT');
669 RAISE;
670 WHEN l_lock_excp THEN -- normal exit after locking
671 arp_message.set_line('OKS_HZ_MERGE_PUB.MERGE_ACCOUNT()-');
672 WHEN others THEN
673 l_error_msg := substr(SQLERRM,1,70);
674 arp_message.set_error('OKS_HZ_MERGE_PUB.MERGE_ACCOUNT', l_error_msg);
675 RAISE;
676 END; -- merge_account
677
678 END; -- Package Body OKS_HZ_MERGE_PUB
[Home] [Help]