[Home] [Help]
PACKAGE BODY: APPS.PAP_CMERGE
Source
1 PACKAGE BODY PAP_CMERGE AS
2 -- $Header: PAPCMR3B.pls 120.2.12010000.4 2008/08/29 14:26:45 arbandyo ship $
3 /* */
4 --
5 -- function to retrieve new customer id for pa_project_customers table
6 -- using current customer id only.
7 --
8 FUNCTION RETRIEVE_CUSTOMER_ID( set_no IN NUMBER ) RETURN BOOLEAN IS
9 BEGIN
10 --
11 SELECT DISTINCT RACM.CUSTOMER_ID, RACM.DUPLICATE_ID INTO new_customer_id,
12 old_customer_id
13 FROM RA_CUSTOMER_MERGES RACM
14 WHERE RACM.DUPLICATE_ID = var_customer_id
15 AND RACM.PROCESS_FLAG = 'N'
16 AND RACM.SET_NUMBER = set_no;
17 --
18 RETURN FALSE;
19 EXCEPTION
20 WHEN NO_DATA_FOUND THEN
21 RETURN TRUE;
22 WHEN OTHERS THEN
23 RETURN TRUE;
24 END RETRIEVE_CUSTOMER_ID;
25 --
26 -- function to verify whether duplicate index record ( project_id + customer_id )
27 -- exist in pa_project_customers.
28 --
29 FUNCTION CHECK_DUPLICATE_INDEX RETURN BOOLEAN IS
30 BEGIN
31 SELECT CUSTOMER_BILL_SPLIT
32 INTO new_customer_bill_split
33 FROM PA_PROJECT_CUSTOMERS PC
34 WHERE PC.PROJECT_ID = var_project_id
35 AND PC.CUSTOMER_ID = new_customer_id;
36 RETURN FALSE;
37 EXCEPTION
38 WHEN NO_DATA_FOUND THEN
39 RETURN TRUE;
40 WHEN OTHERS THEN
41 RETURN TRUE;
42 END CHECK_DUPLICATE_INDEX;
43 --
44 -- this is the main procedure that updates all the necessary tables affecting Project
45 -- accounting.
46 --
47 PROCEDURE MERGE ( req_id IN NUMBER, set_no IN NUMBER, process_mode IN VARCHAR2 ) IS
48 --
49 /* CURSOR cursor_1 IS SELECT DISTINCT PA.PROJECT_ID, PA.CUSTOMER_ID, PA.BILL_TO_ADDRESS_ID,
50 PA.SHIP_TO_ADDRESS_ID,PA.BILL_TO_CUSTOMER_ID,PA.SHIP_TO_CUSTOMER_ID, DEFAULT_TOP_TASK_CUST_FLAG -- FP_M Changes
51 FROM pa_project_customers PA,RA_CUSTOMER_MERGES RACM
52 WHERE RACM.PROCESS_FLAG = 'N'
53 AND RACM.SET_NUMBER = set_no
54 AND (PA.CUSTOMER_ID=RACM.DUPLICATE_ID
55 OR PA.BILL_TO_CUSTOMER_ID=RACM.DUPLICATE_ID
56 OR PA.SHIP_TO_CUSTOMER_ID=RACM.DUPLICATE_ID )
57 Bug 3891382. Added the condition so that the cursor picks projects specific to the
58 org_id where customer merge has taken place.
59 AND EXISTS ( SELECT NULL FROM PA_PROJECTS
60 WHERE PROJECT_ID = PA.PROJECT_ID ); commented for 3938428*/
61
62 /* Modified cursor for bug 3938428*/
63 CURSOR cursor_1 IS SELECT PA.PROJECT_ID, PA.CUSTOMER_ID, PA.BILL_TO_ADDRESS_ID,
64 PA.SHIP_TO_ADDRESS_ID,PA.BILL_TO_CUSTOMER_ID,PA.SHIP_TO_CUSTOMER_ID , DEFAULT_TOP_TASK_CUST_FLAG -- FP_M Changes
65 FROM pa_project_customers PA
66 WHERE EXISTS (
67 SELECT 1 FROM RA_CUSTOMER_MERGES RACM
68 WHERE RACM.PROCESS_FLAG = 'N'
69 AND RACM.SET_NUMBER = set_no
70 AND RACM.request_id = req_id
71 AND (PA.CUSTOMER_ID=RACM.DUPLICATE_ID
72 OR PA.BILL_TO_CUSTOMER_ID=RACM.DUPLICATE_ID
73 OR PA.SHIP_TO_CUSTOMER_ID=RACM.DUPLICATE_ID ))
74 AND EXISTS ( SELECT NULL FROM PA_PROJECTS
75 WHERE PROJECT_ID = PA.PROJECT_ID );
76
77 /* Bug 3891382. Added the cursor to select the delete_duplicate_flag */
78 CURSOR cursor_2 IS SELECT DUPLICATE_ID,DELETE_DUPLICATE_FLAG,CUSTOMER_ID /* for bug 6732730*/
79 FROM RA_CUSTOMER_MERGES
80 WHERE PROCESS_FLAG = 'N'
81 AND SET_NUMBER = set_no;
82 --
83 CURSOR cursor_lock_1 IS
84 SELECT CUSTOMER_ID FROM pa_project_customers PC
85 WHERE
86 PC.CUSTOMER_ID IN ( SELECT DISTINCT RACM.DUPLICATE_ID
87 FROM RA_CUSTOMER_MERGES RACM
88 WHERE RACM.PROCESS_FLAG = 'N'
89 AND RACM.SET_NUMBER = set_no )
90 FOR UPDATE NOWAIT;
91 --
92 CURSOR cursor_lock_2 IS
93 SELECT SHIP_TO_ADDRESS_ID FROM pa_draft_invoice_items PDII
94 WHERE
95 PDII.SHIP_TO_ADDRESS_ID IN ( SELECT RACM.DUPLICATE_ADDRESS_ID
96 FROM RA_CUSTOMER_MERGES RACM
97 WHERE RACM.PROCESS_FLAG = 'N'
98 AND RACM.SET_NUMBER = set_no
99 AND RACM.CUSTOMER_SITE_CODE = 'SHIP_TO' )
100 FOR UPDATE NOWAIT;
101
102 CURSOR cursor_lock_3 IS
103 SELECT ADDRESS_ID FROM pa_tasks PT
104 WHERE
105 PT.ADDRESS_ID IN ( SELECT RACM.DUPLICATE_ADDRESS_ID
106 FROM RA_CUSTOMER_MERGES RACM
107 WHERE RACM.PROCESS_FLAG = 'N'
108 AND RACM.SET_NUMBER = set_no
109 AND RACM.CUSTOMER_SITE_CODE = 'SHIP_TO' )
110 FOR UPDATE NOWAIT;
111 --
112 CURSOR cursor_lock_4 IS
113 SELECT CUSTOMER_ID FROM pa_project_contacts PC
114 WHERE
115 PC.CUSTOMER_ID IN ( SELECT RACM.DUPLICATE_ID
116 FROM RA_CUSTOMER_MERGES RACM
117 WHERE RACM.PROCESS_FLAG = 'N'
118 AND RACM.SET_NUMBER = set_no )
119 FOR UPDATE NOWAIT;
120 --
121 CURSOR cursor_lock_5 IS
122 SELECT CUSTOMER_ID FROM pa_agreements PA
123 WHERE
124 PA.CUSTOMER_ID IN ( SELECT RACM.DUPLICATE_ID
125 FROM RA_CUSTOMER_MERGES RACM
126 WHERE RACM.PROCESS_FLAG = 'N'
127 AND RACM.SET_NUMBER = set_no )
128 FOR UPDATE NOWAIT;
129 --
130 CURSOR cursor_lock_6 IS
131 SELECT CUSTOMER_ID FROM pa_implementations PA -- bug 3891382
132 WHERE
133 PA.CUSTOMER_ID IN ( SELECT RACM.DUPLICATE_ID
134 FROM RA_CUSTOMER_MERGES RACM
135 WHERE RACM.PROCESS_FLAG = 'N'
136 AND RACM.SET_NUMBER = set_no )
137 FOR UPDATE NOWAIT;
138 --
139 CURSOR cursor_lock_7 IS
140 SELECT CUSTOMER_ID FROM pa_proj_retn_rules PA
141 WHERE
142 PA.CUSTOMER_ID IN ( SELECT RACM.DUPLICATE_ID
143 FROM RA_CUSTOMER_MERGES RACM
144 WHERE RACM.PROCESS_FLAG = 'N'
145 AND RACM.SET_NUMBER = set_no )
146 FOR UPDATE NOWAIT;
147 --
148 CURSOR cursor_lock_8 IS
149 SELECT CUSTOMER_ID FROM pa_proj_retn_bill_rules PA
150 WHERE
151 PA.CUSTOMER_ID IN ( SELECT RACM.DUPLICATE_ID
152 FROM RA_CUSTOMER_MERGES RACM
153 WHERE RACM.PROCESS_FLAG = 'N'
154 AND RACM.SET_NUMBER = set_no )
155 FOR UPDATE NOWAIT;
156 --
157 CURSOR cursor_lock_9 IS
158 SELECT CUSTOMER_ID FROM pa_summary_project_retn PA
159 WHERE
160 PA.CUSTOMER_ID IN ( SELECT RACM.DUPLICATE_ID
161 FROM RA_CUSTOMER_MERGES RACM
162 WHERE RACM.PROCESS_FLAG = 'N'
163 AND RACM.SET_NUMBER = set_no )
164 FOR UPDATE NOWAIT;
165 --
166 var_bill_to_address_id pa_project_customers.bill_to_address_id%TYPE;
167 var_ship_to_address_id pa_project_customers.ship_to_address_id%TYPE;
168 var_bill_to_cust_id pa_project_customers.bill_to_customer_id%TYPE;
169 var_ship_to_cust_id pa_project_customers.ship_to_customer_id%TYPE;
170 var_Default_Top_Task_Cust_Flag pa_project_customers.DEFAULT_TOP_TASK_CUST_FLAG%TYPE;
171 not_found_flag BOOLEAN;
172 records_locked EXCEPTION;
173 total_record_upd_count NUMBER := 0;
174 total_record_del_count NUMBER := 0;
175 PRAGMA EXCEPTION_INIT( records_locked, -00054 );
176 var_dup_id ra_customer_merges.duplicate_id%TYPE;
177 var_dup_flag ra_customer_merges.delete_duplicate_flag%TYPE;
178 var_count1 NUMBER := 0;
179 var_count2 NUMBER := 0;
180 /* Begin for Bug 6732730 */
181 var_cust_id ra_customer_merges.customer_id%TYPE; /* Modified to var_cust_id for bug 7341412 */
182 /* End for Bug 6732730 */
183
184 /* */
185 BEGIN
186 /* */
187 -- to indicate what package name is being executed.
188 --
189 -- update log file to indicate the module being executed.
190 --
191 ARP_MESSAGE.SET_LINE( 'PAP_CMERGE.MERGE()+' );
192 --
193 --Get the profile option for audit of customer merge
194 g_audit_profile :=NVL(FND_PROFILE.value('HZ_AUDIT_ACCT_MERGE'),'N');
195
196 -- Lock the rows for the current set_no to avoid indefinate wait
197 --
198 IF process_mode = 'LOCK' THEN
199 --
200 ARP_MESSAGE.SET_NAME( 'AR', 'AR_LOCKING_TABLE' );
201 ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_PROJECT_CUSTOMERS', FALSE );
202 --
203 open cursor_lock_1;
204 close cursor_lock_1;
205 --
206 ARP_MESSAGE.SET_NAME( 'AR', 'AR_LOCKING_TABLE' );
207 ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_DRAFT_INVOICE_ITEMS', FALSE );
208 --
209 open cursor_lock_2;
210 close cursor_lock_2;
211 --
212 ARP_MESSAGE.SET_NAME( 'AR', 'AR_LOCKING_TABLE' );
213 ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_TASKS', FALSE );
214 --
215 open cursor_lock_3;
216 close cursor_lock_3;
217 --
218 ARP_MESSAGE.SET_NAME( 'AR', 'AR_LOCKING_TABLE' );
219 ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_PROJECT_CONTACTS', FALSE );
220 --
221 open cursor_lock_4;
222 close cursor_lock_4;
223 --
224 ARP_MESSAGE.SET_NAME( 'AR', 'AR_LOCKING_TABLE' );
225 ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_AGREEMENTS', FALSE );
226 --
227 open cursor_lock_5;
228 close cursor_lock_5;
229 --
230 ARP_MESSAGE.SET_NAME( 'AR', 'AR_LOCKING_TABLE' );
231 ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_IMPLEMENTATIONS', FALSE );
232 --bug3891382
233 --
234 open cursor_lock_6;
235 close cursor_lock_6;
236 --
237 ARP_MESSAGE.SET_NAME( 'AR', 'AR_LOCKING_TABLE' );
238 ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_PROJ_RETN_RULES', FALSE );
239 --
240 open cursor_lock_7;
241 close cursor_lock_7;
242 --
243 ARP_MESSAGE.SET_NAME( 'AR', 'AR_LOCKING_TABLE' );
244 ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_PROJ_RETN_BILL_RULES', FALSE );
245 --
246 open cursor_lock_8;
247 close cursor_lock_8;
248 --
249 ARP_MESSAGE.SET_NAME( 'AR', 'AR_LOCKING_TABLE' );
250 ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_SUMMARY_PROJECT_RETN', FALSE );
251 --
252 open cursor_lock_9;
253 close cursor_lock_9;
254 --
255 GOTO done_locking;
256 --
257 END IF;
258 --
259 ARP_MESSAGE.SET_NAME( 'AR', 'AR_UPDATING_TABLE' );
260 ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_PROJECT_CUSTOMERS' );
261 --
262 /* Added for Bug 3891382. The logic prevents deletion of the customer being
263 merged, if the customer is having sites in single org unit and is being
264 referenced in other org units. */
265
266 OPEN cursor_2;
267
268 LOOP
269
270 FETCH cursor_2 INTO var_dup_id, var_dup_flag ,var_cust_id; /* for bug 6732730*/
271 /* Modified to var_cust_id for bug 7341412 */
272 EXIT WHEN cursor_2%NOTFOUND;
273
274 IF var_dup_flag = 'Y' and var_cust_id <> var_dup_id THEN /* for bug 6732730*/
275 /* Modified to var_cust_id for bug 7341412 */
276 SELECT count(*) INTO var_count1 FROM pa_project_customers
277 WHERE customer_id = var_dup_id
278 AND project_id NOT IN (
279 SELECT project_id FROM pa_projects);
280
281 SELECT count(*) INTO var_count2 FROM pa_implementations_all
282 WHERE customer_id = var_dup_id
283 AND org_id NOT IN (
284 SELECT DISTINCT org_id FROM pa_projects);
285
286 var_count1 := var_count1 + var_count2;
287
288 SELECT count(*) INTO var_count2 FROM pa_agreements_all
289 WHERE customer_id = var_dup_id
290 AND org_id NOT IN (
291 SELECT DISTINCT org_id FROM pa_projects);
292
293 var_count1 := var_count1 + var_count2;
294
295 SELECT count(*) INTO var_count2 FROM pa_project_contacts
296 WHERE customer_id = var_dup_id
297 AND project_id NOT IN (
298 SELECT project_id FROM pa_projects);
299
300 var_count1 := var_count1 + var_count2;
301
302 IF var_count1 > 0 THEN
303 ARP_CMERGE_MASTER.veto_delete(req_id,set_no,var_dup_id,'Customer
304 is referenced in other organizational units.');
305 END IF;
306 END IF;
307
308 END LOOP;
309
310 CLOSE cursor_2;
311
312 /* End of code for Bug 3891382 */
313
314 OPEN cursor_1;
315 --
316 --
317 -- this loop fetches each row from pa_project_customers table and verifies whether
318 -- duplicate index ( project_id + customer_id ) exist and if it does then it would
319 -- delete the duplicate index row and add the customer bill split to the updated
320 -- row so that sum of the customer bill split for all the customers for that project
321 -- equals 100%.
322 --
323 LOOP
324 --
325 -- fetch each row from pa_project_customers table.
326 --
327 FETCH cursor_1 INTO var_project_id, var_customer_id,
328 var_bill_to_address_id,
329 var_ship_to_address_id,
330 var_bill_to_cust_id,
331 var_ship_to_cust_id,
332 var_Default_Top_Task_Cust_Flag; -- FP_M Changes
333 --
334 EXIT WHEN cursor_1%NOTFOUND;
335 --
336 -- initialize the old and new customer id.
337 --
338 old_customer_id := 0;
339 new_customer_id := 0;
340 -- retrieve new and old customer id.
341 not_found_flag := RETRIEVE_CUSTOMER_ID( set_no );
342 --
343 -- if old and new customer id is equal which means that it's not
344 -- customer merge, it could be address or site use is merge.
345 --
346 IF old_customer_id <> new_customer_id THEN
347 not_found_flag := CHECK_DUPLICATE_INDEX;
348
349 SELECT CUSTOMER_BILL_SPLIT
350 INTO var_customer_bill_split
351 FROM PA_PROJECT_CUSTOMERS PC
352 WHERE PC.PROJECT_ID = var_project_id
353 AND PC.CUSTOMER_ID = old_customer_id;
354
355 /*Bug5462389*/
356 Update pa_tasks
357 Set Customer_ID = new_customer_id
358 Where Project_ID = var_project_id
359 And Customer_ID = old_customer_id;
360 ELSE
361 not_found_flag := TRUE;
362 END IF;
363 --
364 IF NOT not_found_flag THEN
365 /*Added for Tca audit */
366 IF g_audit_profile='Y' THEN
367
368 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
369 MERGE_LOG_ID,
370 TABLE_NAME,
371 MERGE_HEADER_ID,
372 PRIMARY_KEY1,
373 PRIMARY_KEY2,
374 ACTION_FLAG,
375 REQUEST_ID,
376 CREATED_BY,
377 CREATION_DATE,
378 LAST_UPDATE_LOGIN,
379 LAST_UPDATE_DATE,
380 LAST_UPDATED_BY,
381 DEL_COL1,
382 DEL_COL2,
383 DEL_COL3,
384 DEL_COL4,
385 DEL_COL5,
386 DEL_COL6,
387 DEL_COL7,
388 DEL_COL8,
389 DEL_COL9,
390 DEL_COL10,
391 DEL_COL11,
392 DEL_COL12,
393 DEL_COL13,
394 DEL_COL14,
395 DEL_COL15,
396 DEL_COL16,
397 DEL_COL17,
398 DEL_COL18,
399 DEL_COL19,
400 DEL_COL20,
401 DEL_COL21,
402 DEL_COL22,
403 DEL_COL23,
404 DEL_COL24 -- FP_M Changes
405 )
406 ( SELECT
407 HZ_CUSTOMER_MERGE_LOG_s.nextval,
408 'PA_PROJECT_CUSTOMERS',
409 RACM.CUSTOMER_MERGE_HEADER_ID,
410 var_project_id,
411 new_customer_id,
412 'D',
413 req_id,
414 hz_utility_pub.CREATED_BY,
415 hz_utility_pub.CREATION_DATE,
416 hz_utility_pub.LAST_UPDATE_LOGIN,
417 hz_utility_pub.LAST_UPDATE_DATE,
418 hz_utility_pub.LAST_UPDATED_BY,
419 PC.PROJECT_ID
420 ,PC.CUSTOMER_ID
421 ,PC.LAST_UPDATE_DATE
422 ,PC.LAST_UPDATED_BY
423 ,PC.CREATION_DATE
424 ,PC.CREATED_BY
425 ,PC.LAST_UPDATE_LOGIN
426 ,PC.PROJECT_RELATIONSHIP_CODE
427 ,PC.CUSTOMER_BILL_SPLIT
428 ,PC.BILL_TO_ADDRESS_ID
429 ,PC.SHIP_TO_ADDRESS_ID
430 ,PC.INV_CURRENCY_CODE
431 ,PC.INV_RATE_TYPE
432 ,PC.INV_RATE_DATE
433 ,PC.INV_EXCHANGE_RATE
434 ,PC.ALLOW_INV_USER_RATE_TYPE_FLAG
435 ,PC.BILL_ANOTHER_PROJECT_FLAG
436 ,PC.RECEIVER_TASK_ID
437 ,PC.RECORD_VERSION_NUMBER
438 ,PC.PROJECT_PARTY_ID
439 ,PC.RETENTION_LEVEL_CODE
440 ,PC.BILL_TO_CUSTOMER_ID
441 ,PC.SHIP_TO_CUSTOMER_ID
442 ,PC.Default_Top_Task_Cust_Flag -- FP_M Changes
443 FROM PA_PROJECT_CUSTOMERS pc,ra_customer_merges RACM
444 WHERE RACM.DUPLICATE_ID = var_customer_id
445 AND PC.PROJECT_ID=var_project_id
446 AND PC.CUSTOMER_ID = var_customer_id
447 AND RACM.PROCESS_FLAG = 'N'
448 AND RACM.SET_NUMBER = set_no
449 AND ROWNUM=1);
450 END IF;
451 /* End for tca audit*/
452 --
453 -- if duplicate index row exist then we need to delete this row
454 -- and update the customer bill split accordingly.
455 --
456 DELETE FROM pa_project_customers PC
457 WHERE PC.PROJECT_ID = var_project_id
458 AND PC.CUSTOMER_ID = var_customer_id;
459 --
460 total_record_del_count := total_record_del_count + 1;
461 --
462 /*Added for Tca audit */
463 IF g_audit_profile='Y' THEN
464
465 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
466 MERGE_LOG_ID,
467 TABLE_NAME,
468 MERGE_HEADER_ID,
469 PRIMARY_KEY1,
470 PRIMARY_KEY2,
471 NUM_COL4_ORIG,
472 NUM_COL4_NEW,
473 ACTION_FLAG,
474 REQUEST_ID,
475 CREATED_BY,
476 CREATION_DATE,
477 LAST_UPDATE_LOGIN,
478 LAST_UPDATE_DATE,
479 LAST_UPDATED_BY
480 )
481 ( SELECT
482 HZ_CUSTOMER_MERGE_LOG_s.nextval,
483 'PA_PROJECT_CUSTOMERS',
484 RACM.CUSTOMER_MERGE_HEADER_ID,
485 PC.project_id,
486 PC.customer_id,
487 PC.customer_bill_split,
488 PC.customer_bill_split+ var_customer_bill_split,
489 'U',
490 req_id,
491 hz_utility_pub.CREATED_BY,
492 hz_utility_pub.CREATION_DATE,
493 hz_utility_pub.LAST_UPDATE_LOGIN,
494 hz_utility_pub.LAST_UPDATE_DATE,
495 hz_utility_pub.LAST_UPDATED_BY
496 FROM PA_PROJECT_CUSTOMERS PC, ra_customer_merges RACM
497 WHERE PC.PROJECT_ID = var_project_id
498 AND PC.CUSTOMER_ID = new_customer_id
499 AND RACM.DUPLICATE_ID = var_customer_id
500 AND RACM.PROCESS_FLAG = 'N'
501 AND RACM.SET_NUMBER = set_no
502 AND ROWNUM=1);
503
504 END IF;
505 /* End of Tca audit*/
506
507 UPDATE pa_project_customers PC
508 SET PC.CUSTOMER_BILL_SPLIT = PC.CUSTOMER_BILL_SPLIT +
509 var_customer_bill_split
510 WHERE
511 PC.PROJECT_ID = var_project_id
512 AND
513 PC.CUSTOMER_ID = new_customer_id;
514 -- FP_M Changes
515 -- If the Top_Task customer flag is enabled then update
516 -- the Default_Top_task_Cust_Flag column also in Project Customers table
517 -- and
518 -- update the customer with new customer id in Project Tasks table
519 If var_Default_Top_Task_Cust_Flag = 'Y' then
520 UPDATE pa_project_customers PC
521 SET PC.Default_Top_Task_Cust_Flag = 'Y'
522 WHERE PC.PROJECT_ID = var_project_id
523 AND PC.CUSTOMER_ID = new_customer_id;
524 End IF; /* Added for bug 4218767*/
525
526 /*Bug5462389:moved the update on pa_tasks before the IF condition*/
527 -- End of FP_M Changes
528 ELSE
529 --
530 -- update pa_project_customers table for bill to address id, this is necessary
531 -- because if we update customer id also then we would not be able to update
532 -- ship to address id.
533 --
534
535 /*Added for tca audit*/
536 IF g_audit_profile='Y' THEN
537
538 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
539 MERGE_LOG_ID,
540 TABLE_NAME,
541 MERGE_HEADER_ID,
542 PRIMARY_KEY1,
543 PRIMARY_KEY2,
544 NUM_COL2_ORIG,
545 NUM_COL2_NEW,
546 ACTION_FLAG,
547 REQUEST_ID,
548 CREATED_BY,
549 CREATION_DATE,
550 LAST_UPDATE_LOGIN,
551 LAST_UPDATE_DATE,
552 LAST_UPDATED_BY)
553 ( SELECT
554 HZ_CUSTOMER_MERGE_LOG_s.nextval,
555 'PA_PROJECT_CUSTOMERS',
556 RACM.CUSTOMER_MERGE_HEADER_ID,
557 PC.project_id,
558 PC.customer_id,
559 PC.bill_to_address_id,
560 RACM.CUSTOMER_ADDRESS_ID,
561 'U',
562 req_id,
563 hz_utility_pub.CREATED_BY,
564 hz_utility_pub.CREATION_DATE,
565 hz_utility_pub.LAST_UPDATE_LOGIN,
566 hz_utility_pub.LAST_UPDATE_DATE,
567 hz_utility_pub.LAST_UPDATED_BY
568 FROM PA_PROJECT_CUSTOMERS PC, RA_CUSTOMER_MERGES RACM
569 WHERE RACM.DUPLICATE_ID = var_bill_to_cust_id /*CACR*/
570 AND RACM.DUPLICATE_ADDRESS_ID = PC.BILL_TO_ADDRESS_ID
571 AND RACM.PROCESS_FLAG = 'N'
572 AND RACM.SET_NUMBER = set_no
573 AND RACM.CUSTOMER_SITE_CODE = 'BILL_TO'
574 AND PC.PROJECT_ID = var_project_id
575 AND PC.CUSTOMER_ID = var_customer_id);
576
577 END IF;
578 /* End of TCA audit*/
579
580 UPDATE pa_project_customers PC
581 SET ( BILL_TO_ADDRESS_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ADDRESS_ID
582 FROM RA_CUSTOMER_MERGES RACM
583 WHERE RACM.DUPLICATE_ID = var_bill_to_cust_id /*CACR*/
584 AND RACM.DUPLICATE_ADDRESS_ID = PC.BILL_TO_ADDRESS_ID
585 AND RACM.PROCESS_FLAG = 'N'
586 AND RACM.SET_NUMBER = set_no
587 AND RACM.CUSTOMER_SITE_CODE = 'BILL_TO' ),
588 LAST_UPDATE_DATE = SYSDATE,
589 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
590 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
591 WHERE
592 PC.BILL_TO_ADDRESS_ID IN ( SELECT DISTINCT RACM.DUPLICATE_ADDRESS_ID
593 FROM RA_CUSTOMER_MERGES RACM
594 WHERE RACM.DUPLICATE_ID = var_bill_to_cust_id /*CACR*/
595 AND RACM.PROCESS_FLAG = 'N'
596 AND RACM.SET_NUMBER = set_no
597 AND RACM.CUSTOMER_SITE_CODE = 'BILL_TO' )
598 AND PC.PROJECT_ID = var_project_id
599 AND PC.CUSTOMER_ID = var_customer_id;
600 --
601 -- update pa_project_customers table for customer_id and ship_to_address_id.
602 --
603 IF g_audit_profile='Y' THEN
604
605 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
606 MERGE_LOG_ID,
607 TABLE_NAME,
608 MERGE_HEADER_ID,
609 PRIMARY_KEY1,
610 PRIMARY_KEY2,
611 NUM_COL3_ORIG,
612 NUM_COL3_NEW,
613 ACTION_FLAG,
614 REQUEST_ID,
615 CREATED_BY,
616 CREATION_DATE,
617 LAST_UPDATE_LOGIN,
618 LAST_UPDATE_DATE,
619 LAST_UPDATED_BY)
620 ( SELECT
621 HZ_CUSTOMER_MERGE_LOG_s.nextval,
622 'PA_PROJECT_CUSTOMERS',
623 RACM.CUSTOMER_MERGE_HEADER_ID,
624 PC.project_id,
625 PC.customer_id,
626 PC.ship_to_address_id,
627 RACM.CUSTOMER_ADDRESS_ID,
628 'U',
629 req_id,
630 hz_utility_pub.CREATED_BY,
631 hz_utility_pub.CREATION_DATE,
632 hz_utility_pub.LAST_UPDATE_LOGIN,
633 hz_utility_pub.LAST_UPDATE_DATE,
634 hz_utility_pub.LAST_UPDATED_BY
635 FROM PA_PROJECT_CUSTOMERS PC, RA_CUSTOMER_MERGES RACM
636 WHERE RACM.DUPLICATE_ID = var_ship_to_cust_id /*CACR*/
637 AND RACM.DUPLICATE_ADDRESS_ID = PC.SHIP_TO_ADDRESS_ID
638 AND RACM.PROCESS_FLAG = 'N'
639 AND RACM.SET_NUMBER = set_no
640 AND RACM.CUSTOMER_SITE_CODE = 'SHIP_TO'
641 AND PC.PROJECT_ID = var_project_id
642 AND PC.CUSTOMER_ID = var_customer_id);
643
644 END IF;
645
646 UPDATE pa_project_customers PC
647 SET ( SHIP_TO_ADDRESS_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ADDRESS_ID
648 FROM RA_CUSTOMER_MERGES RACM
649 WHERE RACM.DUPLICATE_ID = var_ship_to_cust_id /*CACR*/
650 AND RACM.DUPLICATE_ADDRESS_ID = PC.SHIP_TO_ADDRESS_ID
651 AND RACM.PROCESS_FLAG = 'N'
652 AND RACM.SET_NUMBER = set_no
653 AND RACM.CUSTOMER_SITE_CODE = 'SHIP_TO' ),
654 LAST_UPDATE_DATE = SYSDATE,
655 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
656 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
657 WHERE
658 PC.SHIP_TO_ADDRESS_ID IN ( SELECT RACM.DUPLICATE_ADDRESS_ID
659 FROM RA_CUSTOMER_MERGES RACM
660 WHERE RACM.DUPLICATE_ID = var_ship_to_cust_id /*CACR*/
661 AND RACM.PROCESS_FLAG = 'N'
662 AND RACM.SET_NUMBER = set_no
663 AND RACM.CUSTOMER_SITE_CODE = 'SHIP_TO' )
664 AND PC.PROJECT_ID = var_project_id
665 AND PC.CUSTOMER_ID = var_customer_id;
666
667 /*For customer account relation enhancement*/
668
669 IF g_audit_profile='Y' THEN
670
671 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
672 MERGE_LOG_ID,
673 TABLE_NAME,
674 MERGE_HEADER_ID,
675 PRIMARY_KEY1,
676 PRIMARY_KEY2,
677 NUM_COL5_ORIG,
678 NUM_COL5_NEW,
679 ACTION_FLAG,
680 REQUEST_ID,
681 CREATED_BY,
682 CREATION_DATE,
683 LAST_UPDATE_LOGIN,
684 LAST_UPDATE_DATE,
685 LAST_UPDATED_BY)
686 ( SELECT
687 HZ_CUSTOMER_MERGE_LOG_s.nextval,
688 'PA_PROJECT_CUSTOMERS',
689 RACM.CUSTOMER_MERGE_HEADER_ID,
690 PC.project_id,
691 PC.customer_id,
692 PC.bill_to_customer_id,
693 RACM.CUSTOMER_ID,
694 'U',
695 req_id,
696 hz_utility_pub.CREATED_BY,
697 hz_utility_pub.CREATION_DATE,
698 hz_utility_pub.LAST_UPDATE_LOGIN,
699 hz_utility_pub.LAST_UPDATE_DATE,
700 hz_utility_pub.LAST_UPDATED_BY
701 FROM PA_PROJECT_CUSTOMERS PC, RA_CUSTOMER_MERGES RACM
702 WHERE RACM.DUPLICATE_ID = var_bill_to_cust_id /*CACR*/
703 AND RACM.PROCESS_FLAG = 'N'
704 AND RACM.SET_NUMBER = set_no
705 AND PC.PROJECT_ID = var_project_id
706 AND PC.CUSTOMER_ID = var_customer_id
707 AND RACM.CUSTOMER_ID<>RACM.DUPLICATE_ID
708 AND ROWNUM=1);
709 END IF;
710
711 UPDATE pa_project_customers PC
712 SET ( BILL_TO_CUSTOMER_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ID
713 FROM RA_CUSTOMER_MERGES RACM
714 WHERE RACM.DUPLICATE_ID = var_bill_to_cust_id
715 AND RACM.PROCESS_FLAG = 'N'
716 AND RACM.SET_NUMBER = set_no ),
717 LAST_UPDATE_DATE = SYSDATE,
718 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
719 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
720 WHERE
721 EXISTS ( SELECT NULL FROM RA_CUSTOMER_MERGES RACM
722 WHERE RACM.DUPLICATE_ID = PC.BILL_TO_CUSTOMER_ID
723 AND RACM.PROCESS_FLAG = 'N'
724 AND RACM.SET_NUMBER = set_no )
725 AND PC.PROJECT_ID = var_project_id
726 AND PC.CUSTOMER_ID = var_customer_id;
727
728 IF g_audit_profile='Y' THEN
729
730 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
731 MERGE_LOG_ID,
732 TABLE_NAME,
733 MERGE_HEADER_ID,
734 PRIMARY_KEY1,
735 PRIMARY_KEY2,
736 NUM_COL6_ORIG,
737 NUM_COL6_NEW,
738 ACTION_FLAG,
739 REQUEST_ID,
740 CREATED_BY,
741 CREATION_DATE,
742 LAST_UPDATE_LOGIN,
743 LAST_UPDATE_DATE,
744 LAST_UPDATED_BY)
745 ( SELECT
746 HZ_CUSTOMER_MERGE_LOG_s.nextval,
747 'PA_PROJECT_CUSTOMERS',
748 RACM.CUSTOMER_MERGE_HEADER_ID,
749 PC.project_id,
750 PC.customer_id,
751 PC.ship_to_customer_id,
752 RACM.CUSTOMER_ID,
753 'U',
754 req_id,
755 hz_utility_pub.CREATED_BY,
756 hz_utility_pub.CREATION_DATE,
757 hz_utility_pub.LAST_UPDATE_LOGIN,
758 hz_utility_pub.LAST_UPDATE_DATE,
759 hz_utility_pub.LAST_UPDATED_BY
760 FROM PA_PROJECT_CUSTOMERS PC, RA_CUSTOMER_MERGES RACM
761 WHERE RACM.DUPLICATE_ID = var_ship_to_cust_id /*CACR*/
762 AND RACM.PROCESS_FLAG = 'N'
763 AND RACM.SET_NUMBER = set_no
764 AND PC.PROJECT_ID = var_project_id
765 AND PC.CUSTOMER_ID = var_customer_id
766 AND RACM.CUSTOMER_ID<>RACM.DUPLICATE_ID
767 AND ROWNUM=1);
768 END IF;
769
770 UPDATE pa_project_customers PC
771 SET ( SHIP_TO_CUSTOMER_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ID
772 FROM RA_CUSTOMER_MERGES RACM
773 WHERE RACM.DUPLICATE_ID = var_ship_to_cust_id
774 AND RACM.PROCESS_FLAG = 'N'
775 AND RACM.SET_NUMBER = set_no ),
776 LAST_UPDATE_DATE = SYSDATE,
777 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
778 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
779 WHERE
780 EXISTS ( SELECT NULL FROM RA_CUSTOMER_MERGES RACM
781 WHERE RACM.DUPLICATE_ID = PC.SHIP_TO_CUSTOMER_ID
782 AND RACM.PROCESS_FLAG = 'N'
783 AND RACM.SET_NUMBER = set_no )
784 AND PC.PROJECT_ID = var_project_id
785 AND PC.CUSTOMER_ID = var_customer_id;
786 --
787 -- update pa_project_customers for customer_id only, this is necessary because
788 -- if in the previous update if ship_to_address_id is null then customer_id
789 -- would not get updated.
790 --
791
792 IF g_audit_profile='Y' THEN
793
794 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
795 MERGE_LOG_ID,
796 TABLE_NAME,
797 MERGE_HEADER_ID,
798 PRIMARY_KEY1,
799 PRIMARY_KEY2,
800 NUM_COL1_ORIG,
801 NUM_COL1_NEW,
802 ACTION_FLAG,
803 REQUEST_ID,
804 CREATED_BY,
805 CREATION_DATE,
806 LAST_UPDATE_LOGIN,
807 LAST_UPDATE_DATE,
808 LAST_UPDATED_BY)
809 ( select HZ_CUSTOMER_MERGE_LOG_s.nextval,
810 'PA_PROJECT_CUSTOMERS',
811 RACM.CUSTOMER_MERGE_HEADER_ID,
812 PC.project_id,
813 RACM.CUSTOMER_ID,
814 PC.customer_id,
815 RACM.CUSTOMER_ID,
816 'U',
817 req_id,
818 hz_utility_pub.CREATED_BY,
819 hz_utility_pub.CREATION_DATE,
820 hz_utility_pub.LAST_UPDATE_LOGIN,
821 hz_utility_pub.LAST_UPDATE_DATE,
822 hz_utility_pub.LAST_UPDATED_BY
823 FROM PA_PROJECT_CUSTOMERS PC, ra_customer_merges RACM
824 WHERE RACM.DUPLICATE_ID = var_customer_id
825 AND RACM.PROCESS_FLAG = 'N'
826 AND RACM.SET_NUMBER = set_no
827 AND PC.PROJECT_ID = var_project_id
828 AND PC.CUSTOMER_ID = var_customer_id
829 AND RACM.CUSTOMER_ID<>RACM.DUPLICATE_ID
830 AND ROWNUM=1 );
831
832 END IF;
833 UPDATE pa_project_customers PC
834 SET ( CUSTOMER_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ID
835 FROM RA_CUSTOMER_MERGES RACM
836 WHERE RACM.DUPLICATE_ID = var_customer_id
837 AND RACM.PROCESS_FLAG = 'N'
838 AND RACM.SET_NUMBER = set_no ),
839 LAST_UPDATE_DATE = SYSDATE,
840 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
841 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
842 WHERE
843 EXISTS ( SELECT NULL FROM RA_CUSTOMER_MERGES RACM
844 WHERE RACM.DUPLICATE_ID = PC.CUSTOMER_ID
845 AND RACM.PROCESS_FLAG = 'N'
846 AND RACM.SET_NUMBER = set_no )
847 AND PC.PROJECT_ID = var_project_id
848 AND PC.CUSTOMER_ID = var_customer_id;
849 --
850 END IF;
851
852 total_record_upd_count := total_record_upd_count + 1;
853 --
854 END LOOP;
855 --
856 CLOSE cursor_1;
857 --
858 -- update log file to indicate the total records deleted.
859 --
860 ARP_MESSAGE.SET_NAME( 'AR', 'AR_ROWS_UPDATED' );
861 ARP_MESSAGE.SET_TOKEN( 'NUM_ROWS', TO_CHAR( total_record_upd_count ));
862 total_record_upd_count := 0;
863 --
864 -- update log file to indicate the total records updated.
865 --
866 ARP_MESSAGE.SET_NAME( 'AR', 'AR_ROWS_DELETED' );
867 ARP_MESSAGE.SET_TOKEN( 'NUM_ROWS', TO_CHAR( total_record_del_count ));
868 total_record_del_count := 0;
869 --
870 -- update log file to indicate the table being updated.
871 --
872 IF g_audit_profile='Y' THEN
873
874 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
875 MERGE_LOG_ID,
876 TABLE_NAME,
877 MERGE_HEADER_ID,
878 PRIMARY_KEY1,
879 PRIMARY_KEY2,
880 NUM_COL3_ORIG,
881 NUM_COL3_NEW,
882 ACTION_FLAG,
883 REQUEST_ID,
884 CREATED_BY,
885 CREATION_DATE,
886 LAST_UPDATE_LOGIN,
887 LAST_UPDATE_DATE,
888 LAST_UPDATED_BY
889 )
890 (SELECT
891 HZ_CUSTOMER_MERGE_LOG_S.nextval,
892 'PA_DRAFT_INVOICES_ALL',
893 RACM.CUSTOMER_MERGE_HEADER_ID,
894 PDI.PROJECT_ID,
895 PDI.DRAFT_INVOICE_NUM,
896 PDI.CUSTOMER_ID,
897 RACM.CUSTOMER_ID,
898 'U',
899 req_id,
900 hz_utility_pub.CREATED_BY,
901 hz_utility_pub.CREATION_DATE,
902 hz_utility_pub.LAST_UPDATE_LOGIN,
903 hz_utility_pub.LAST_UPDATE_DATE,
904 hz_utility_pub.LAST_UPDATED_BY
905 FROM PA_DRAFT_INVOICES PDI,(SELECT DISTINCT CUSTOMER_MERGE_HEADER_ID,
906 CUSTOMER_ID,
907 DUPLICATE_ID
908 FROM RA_CUSTOMER_MERGES
909 WHERE PROCESS_FLAG = 'N'
910 AND SET_NUMBER = set_no
911 AND CUSTOMER_ID<>DUPLICATE_ID ) RACM
912 WHERE
913 RACM.DUPLICATE_ID = PDI.CUSTOMER_ID
914 );
915
916 END IF;
917
918 UPDATE pa_draft_invoices PC -- bug 3891382
919 SET ( CUSTOMER_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ID
920 FROM RA_CUSTOMER_MERGES RACM
921 WHERE RACM.DUPLICATE_ID = PC.CUSTOMER_ID
922 AND RACM.PROCESS_FLAG = 'N'
923 AND RACM.SET_NUMBER = set_no ),
924 LAST_UPDATE_DATE = SYSDATE,
925 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
926 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
927 WHERE
928 PC.CUSTOMER_ID IN ( SELECT DUPLICATE_ID FROM RA_CUSTOMER_MERGES RACM
929 WHERE RACM.PROCESS_FLAG = 'N'
930 AND RACM.SET_NUMBER = set_no );
931
932 IF g_audit_profile='Y' THEN
933
934 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
935 MERGE_LOG_ID,
936 TABLE_NAME,
937 MERGE_HEADER_ID,
938 PRIMARY_KEY1,
939 PRIMARY_KEY2,
940 NUM_COL4_ORIG,
941 NUM_COL4_NEW,
942 ACTION_FLAG,
943 REQUEST_ID,
944 CREATED_BY,
945 CREATION_DATE,
946 LAST_UPDATE_LOGIN,
947 LAST_UPDATE_DATE,
948 LAST_UPDATED_BY
949 )
950 (SELECT
951 HZ_CUSTOMER_MERGE_LOG_S.nextval,
952 'PA_DRAFT_INVOICES_ALL',
953 RACM.CUSTOMER_MERGE_HEADER_ID,
954 PDI.PROJECT_ID,
955 PDI.DRAFT_INVOICE_NUM,
956 PDI.BILL_TO_CUSTOMER_ID,
957 RACM.CUSTOMER_ID,
958 'U',
959 req_id,
960 hz_utility_pub.CREATED_BY,
961 hz_utility_pub.CREATION_DATE,
962 hz_utility_pub.LAST_UPDATE_LOGIN,
963 hz_utility_pub.LAST_UPDATE_DATE,
964 hz_utility_pub.LAST_UPDATED_BY
965 FROM PA_DRAFT_INVOICES PDI,(SELECT DISTINCT CUSTOMER_MERGE_HEADER_ID,
966 CUSTOMER_ID,
967 DUPLICATE_ID
968 FROM RA_CUSTOMER_MERGES
969 WHERE PROCESS_FLAG = 'N'
970 AND SET_NUMBER = set_no
971 AND CUSTOMER_ID<>DUPLICATE_ID ) RACM
972 WHERE
973 RACM.DUPLICATE_ID = PDI.BILL_TO_CUSTOMER_ID
974 );
975
976 END IF;
977
978 UPDATE pa_draft_invoices PC -- bug 3891382
979 SET ( BILL_TO_CUSTOMER_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ID
980 FROM RA_CUSTOMER_MERGES RACM
981 WHERE RACM.DUPLICATE_ID = PC.BILL_TO_CUSTOMER_ID
982 AND RACM.PROCESS_FLAG = 'N'
983 AND RACM.SET_NUMBER = set_no ),
984 LAST_UPDATE_DATE = SYSDATE,
985 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
986 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
987 WHERE
988 PC.BILL_TO_CUSTOMER_ID IN ( SELECT DUPLICATE_ID FROM RA_CUSTOMER_MERGES RACM
989 WHERE RACM.PROCESS_FLAG = 'N'
990 AND RACM.SET_NUMBER = set_no );
991
992 IF g_audit_profile='Y' THEN
993
994 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
995 MERGE_LOG_ID,
996 TABLE_NAME,
997 MERGE_HEADER_ID,
998 PRIMARY_KEY1,
999 PRIMARY_KEY2,
1000 NUM_COL5_ORIG,
1001 NUM_COL5_NEW,
1002 ACTION_FLAG,
1003 REQUEST_ID,
1004 CREATED_BY,
1005 CREATION_DATE,
1006 LAST_UPDATE_LOGIN,
1007 LAST_UPDATE_DATE,
1008 LAST_UPDATED_BY
1009 )
1010 (SELECT
1011 HZ_CUSTOMER_MERGE_LOG_S.nextval,
1012 'PA_DRAFT_INVOICES_ALL',
1013 RACM.CUSTOMER_MERGE_HEADER_ID,
1014 PDI.PROJECT_ID,
1015 PDI.DRAFT_INVOICE_NUM,
1016 PDI.SHIP_TO_CUSTOMER_ID,
1017 RACM.CUSTOMER_ID,
1018 'U',
1019 req_id,
1020 hz_utility_pub.CREATED_BY,
1021 hz_utility_pub.CREATION_DATE,
1022 hz_utility_pub.LAST_UPDATE_LOGIN,
1023 hz_utility_pub.LAST_UPDATE_DATE,
1024 hz_utility_pub.LAST_UPDATED_BY
1025 FROM PA_DRAFT_INVOICES PDI,(SELECT DISTINCT CUSTOMER_MERGE_HEADER_ID,
1026 CUSTOMER_ID,
1027 DUPLICATE_ID
1028 FROM RA_CUSTOMER_MERGES
1029 WHERE PROCESS_FLAG = 'N'
1030 AND SET_NUMBER = set_no
1031 AND CUSTOMER_ID<>DUPLICATE_ID ) RACM
1032 WHERE
1033 RACM.DUPLICATE_ID = PDI.SHIP_TO_CUSTOMER_ID
1034 );
1035
1036 END IF;
1037
1038 UPDATE pa_draft_invoices PC -- bug 3891382
1039 SET ( SHIP_TO_CUSTOMER_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ID
1040 FROM RA_CUSTOMER_MERGES RACM
1041 WHERE RACM.DUPLICATE_ID = PC.SHIP_TO_CUSTOMER_ID
1042 AND RACM.PROCESS_FLAG = 'N'
1043 AND RACM.SET_NUMBER = set_no ),
1044 LAST_UPDATE_DATE = SYSDATE,
1045 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
1046 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
1047 WHERE
1048 PC.SHIP_TO_CUSTOMER_ID IN ( SELECT DUPLICATE_ID FROM RA_CUSTOMER_MERGES RACM
1049 WHERE RACM.PROCESS_FLAG = 'N'
1050 AND RACM.SET_NUMBER = set_no );
1051
1052
1053 IF g_audit_profile='Y' THEN
1054
1055 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1056 MERGE_LOG_ID,
1057 TABLE_NAME,
1058 MERGE_HEADER_ID,
1059 PRIMARY_KEY1,
1060 PRIMARY_KEY2,
1061 NUM_COL1_ORIG,
1062 NUM_COL1_NEW,
1063 ACTION_FLAG,
1064 REQUEST_ID,
1065 CREATED_BY,
1066 CREATION_DATE,
1067 LAST_UPDATE_LOGIN,
1068 LAST_UPDATE_DATE,
1069 LAST_UPDATED_BY
1070 )
1071 (SELECT
1072 HZ_CUSTOMER_MERGE_LOG_S.nextval,
1073 'PA_DRAFT_INVOICES_ALL',
1074 RACM.CUSTOMER_MERGE_HEADER_ID,
1075 PDI.PROJECT_ID,
1076 PDI.DRAFT_INVOICE_NUM,
1077 PDI.BILL_TO_ADDRESS_ID,
1078 RACM.CUSTOMER_ADDRESS_ID,
1079 'U',
1080 req_id,
1081 hz_utility_pub.CREATED_BY,
1082 hz_utility_pub.CREATION_DATE,
1083 hz_utility_pub.LAST_UPDATE_LOGIN,
1084 hz_utility_pub.LAST_UPDATE_DATE,
1085 hz_utility_pub.LAST_UPDATED_BY
1086 FROM PA_DRAFT_INVOICES PDI,RA_CUSTOMER_MERGES RACM
1087 WHERE
1088 RACM.DUPLICATE_ADDRESS_ID = PDI.BILL_TO_ADDRESS_ID
1089 AND RACM.PROCESS_FLAG = 'N'
1090 AND RACM.SET_NUMBER = set_no
1091 AND RACM.CUSTOMER_SITE_CODE = 'BILL_TO'
1092 );
1093
1094 END IF;
1095
1096 UPDATE pa_draft_invoices PDI -- bug 3891382
1097 SET ( BILL_TO_ADDRESS_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ADDRESS_ID
1098 FROM RA_CUSTOMER_MERGES RACM
1099 WHERE
1100 RACM.DUPLICATE_ADDRESS_ID = PDI.BILL_TO_ADDRESS_ID
1101 AND RACM.PROCESS_FLAG = 'N'
1102 AND RACM.SET_NUMBER = set_no
1103 AND RACM.CUSTOMER_SITE_CODE = 'BILL_TO' ),
1104 LAST_UPDATE_DATE = SYSDATE,
1105 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
1106 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
1107 WHERE
1108 PDI.BILL_TO_ADDRESS_ID IN ( SELECT RACM.DUPLICATE_ADDRESS_ID
1109 FROM RA_CUSTOMER_MERGES RACM
1110 WHERE RACM.PROCESS_FLAG = 'N'
1111 AND RACM.SET_NUMBER = set_no
1112 AND RACM.CUSTOMER_SITE_CODE = 'BILL_TO' );
1113
1114 IF g_audit_profile='Y' THEN
1115
1116 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1117 MERGE_LOG_ID,
1118 TABLE_NAME,
1119 MERGE_HEADER_ID,
1120 PRIMARY_KEY1,
1121 PRIMARY_KEY2,
1122 NUM_COL2_ORIG,
1123 NUM_COL2_NEW,
1124 ACTION_FLAG,
1125 REQUEST_ID,
1126 CREATED_BY,
1127 CREATION_DATE,
1128 LAST_UPDATE_LOGIN,
1129 LAST_UPDATE_DATE,
1130 LAST_UPDATED_BY
1131 )
1132 (SELECT
1133 HZ_CUSTOMER_MERGE_LOG_s.nextval,
1134 'PA_DRAFT_INVOICES_ALL',
1135 RACM.CUSTOMER_MERGE_HEADER_ID,
1136 PDI.PROJECT_ID,
1137 PDI.DRAFT_INVOICE_NUM,
1138 PDI.SHIP_TO_ADDRESS_ID,
1139 RACM.CUSTOMER_ADDRESS_ID,
1140 'U',
1141 req_id,
1142 hz_utility_pub.CREATED_BY,
1143 hz_utility_pub.CREATION_DATE,
1144 hz_utility_pub.LAST_UPDATE_LOGIN,
1145 hz_utility_pub.LAST_UPDATE_DATE,
1146 hz_utility_pub.LAST_UPDATED_BY
1147 FROM PA_DRAFT_INVOICES PDI,RA_CUSTOMER_MERGES RACM
1148 WHERE
1149 RACM.DUPLICATE_ADDRESS_ID = PDI.SHIP_TO_ADDRESS_ID
1150 AND RACM.PROCESS_FLAG = 'N'
1151 AND RACM.SET_NUMBER = set_no
1152 AND RACM.CUSTOMER_SITE_CODE = 'SHIP_TO'
1153 );
1154
1155 END IF;
1156
1157 UPDATE pa_draft_invoices PDI -- bug 3891382
1158 SET ( SHIP_TO_ADDRESS_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ADDRESS_ID
1159 FROM RA_CUSTOMER_MERGES RACM
1160 WHERE
1161 RACM.DUPLICATE_ADDRESS_ID = PDI.SHIP_TO_ADDRESS_ID
1162 AND RACM.PROCESS_FLAG = 'N'
1163 AND RACM.SET_NUMBER = set_no
1164 AND RACM.CUSTOMER_SITE_CODE = 'SHIP_TO' ),
1165 LAST_UPDATE_DATE = SYSDATE,
1166 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
1167 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
1168 WHERE
1169 PDI.SHIP_TO_ADDRESS_ID IN ( SELECT RACM.DUPLICATE_ADDRESS_ID
1170 FROM RA_CUSTOMER_MERGES RACM
1171 WHERE RACM.PROCESS_FLAG = 'N'
1172 AND RACM.SET_NUMBER = set_no
1173 AND RACM.CUSTOMER_SITE_CODE = 'SHIP_TO' );
1174
1175 ARP_MESSAGE.SET_NAME( 'AR', 'AR_UPDATING_TABLE' );
1176 ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_DRAFT_INVOICE_ITEMS' );
1177 --
1178 -- update pa_draft_invoice_items for ship_to_address_id only.
1179 --
1180 IF g_audit_profile='Y' THEN
1181
1182 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1183 MERGE_LOG_ID,
1184 TABLE_NAME,
1185 MERGE_HEADER_ID,
1186 PRIMARY_KEY1,
1187 PRIMARY_KEY2,
1188 PRIMARY_KEY3,
1189 NUM_COL1_ORIG,
1190 NUM_COL1_NEW,
1191 ACTION_FLAG,
1192 REQUEST_ID,
1193 CREATED_BY,
1194 CREATION_DATE,
1195 LAST_UPDATE_LOGIN,
1196 LAST_UPDATE_DATE,
1197 LAST_UPDATED_BY)
1198 ( select HZ_CUSTOMER_MERGE_LOG_s.nextval,
1199 'PA_DRAFT_INVOICE_ITEMS',
1200 RACM.CUSTOMER_MERGE_HEADER_ID,
1201 PDII.project_id,
1202 PDII.draft_invoice_num,
1203 PDII.line_num,
1204 PDII.ship_to_address_id,
1205 RACM.CUSTOMER_ADDRESS_ID,
1206 'U',
1207 req_id,
1208 hz_utility_pub.CREATED_BY,
1209 hz_utility_pub.CREATION_DATE,
1210 hz_utility_pub.LAST_UPDATE_LOGIN,
1211 hz_utility_pub.LAST_UPDATE_DATE,
1212 hz_utility_pub.LAST_UPDATED_BY
1213 FROM PA_DRAFT_INVOICE_ITEMS PDII, ra_customer_merges RACM
1214 WHERE RACM.DUPLICATE_ADDRESS_ID = PDII.SHIP_TO_ADDRESS_ID
1215 AND RACM.PROCESS_FLAG = 'N'
1216 AND RACM.SET_NUMBER = set_no
1217 AND RACM.CUSTOMER_SITE_CODE = 'SHIP_TO');
1218
1219 END IF;
1220
1221 UPDATE pa_draft_invoice_items PDII
1222 SET ( SHIP_TO_ADDRESS_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ADDRESS_ID
1223 FROM RA_CUSTOMER_MERGES RACM
1224 WHERE
1225 RACM.DUPLICATE_ADDRESS_ID = PDII.SHIP_TO_ADDRESS_ID
1226 AND RACM.PROCESS_FLAG = 'N'
1227 AND RACM.SET_NUMBER = set_no
1228 AND RACM.CUSTOMER_SITE_CODE = 'SHIP_TO' ),
1229 LAST_UPDATE_DATE = SYSDATE,
1230 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
1231 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
1232 WHERE
1233 PDII.SHIP_TO_ADDRESS_ID IN ( SELECT RACM.DUPLICATE_ADDRESS_ID
1234 FROM RA_CUSTOMER_MERGES RACM
1235 WHERE RACM.PROCESS_FLAG = 'N'
1236 AND RACM.SET_NUMBER = set_no
1237 AND RACM.CUSTOMER_SITE_CODE = 'SHIP_TO' );
1238 --
1239 -- update log file to indicate the total records being updated.
1240 --
1241 total_record_upd_count := SQL%ROWCOUNT;
1242 ARP_MESSAGE.SET_NAME( 'AR', 'AR_ROWS_UPDATED' );
1243 ARP_MESSAGE.SET_TOKEN( 'NUM_ROWS', TO_CHAR( total_record_upd_count ));
1244 total_record_upd_count := 0;
1245 --
1246 -- update log file to indicate the table being updated.
1247 --
1248 ARP_MESSAGE.SET_NAME( 'AR', 'AR_UPDATING_TABLE' );
1249 ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_TASKS' );
1250 --
1251 -- update pa_tasks for address_id ( ship address id ).
1252 --
1253 IF g_audit_profile='Y' THEN
1254
1255 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1256 MERGE_LOG_ID,
1257 TABLE_NAME,
1258 MERGE_HEADER_ID,
1259 PRIMARY_KEY_ID,
1260 NUM_COL1_ORIG,
1261 NUM_COL1_NEW,
1262 NUM_COL7_ORIG,
1263 NUM_COL7_NEW,
1264 ACTION_FLAG,
1265 REQUEST_ID,
1266 CREATED_BY,
1267 CREATION_DATE,
1268 LAST_UPDATE_LOGIN,
1269 LAST_UPDATE_DATE,
1270 LAST_UPDATED_BY)
1271 ( select HZ_CUSTOMER_MERGE_LOG_s.nextval,
1272 'PA_TASKS',
1273 RACM.CUSTOMER_MERGE_HEADER_ID,
1274 PT.TASK_ID,
1275 PT. address_id,
1276 RACM.CUSTOMER_ADDRESS_ID,
1277 PT.Customer_ID,
1278 New_customer_ID,
1279 'U',
1280 req_id,
1281 hz_utility_pub.CREATED_BY,
1282 hz_utility_pub.CREATION_DATE,
1283 hz_utility_pub.LAST_UPDATE_LOGIN,
1284 hz_utility_pub.LAST_UPDATE_DATE,
1285 hz_utility_pub.LAST_UPDATED_BY
1286 FROM PA_TASKS PT, ra_customer_merges RACM
1287 WHERE RACM.DUPLICATE_ADDRESS_ID = PT.ADDRESS_ID
1288 AND RACM.PROCESS_FLAG = 'N'
1289 AND RACM.SET_NUMBER = set_no
1290 AND RACM.CUSTOMER_SITE_CODE = 'SHIP_TO');
1291 END IF;
1292
1293 UPDATE pa_tasks PT
1294 SET ( ADDRESS_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ADDRESS_ID
1295 FROM RA_CUSTOMER_MERGES RACM
1296 WHERE RACM.DUPLICATE_ADDRESS_ID = PT.ADDRESS_ID
1297 AND RACM.PROCESS_FLAG = 'N'
1298 AND RACM.SET_NUMBER = set_no
1299 AND RACM.CUSTOMER_SITE_CODE = 'SHIP_TO' ),
1300 LAST_UPDATE_DATE = SYSDATE,
1301 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
1302 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
1303 WHERE
1304 PT.ADDRESS_ID IN ( SELECT RACM.DUPLICATE_ADDRESS_ID
1305 FROM RA_CUSTOMER_MERGES RACM
1306 WHERE RACM.PROCESS_FLAG = 'N'
1307 AND RACM.SET_NUMBER = set_no
1308 AND RACM.CUSTOMER_SITE_CODE = 'SHIP_TO' );
1309 --
1310 -- update log file to indicate the total records being updated.
1311 --
1312 total_record_upd_count := SQL%ROWCOUNT;
1313 ARP_MESSAGE.SET_NAME( 'AR', 'AR_ROWS_UPDATED' );
1314 ARP_MESSAGE.SET_TOKEN( 'NUM_ROWS', TO_CHAR( total_record_upd_count ));
1315 total_record_upd_count := 0;
1316 --
1317 -- update log file to indicate the table being updated. For bug# 1676538
1318 --
1319 ARP_MESSAGE.SET_NAME( 'AR', 'AR_UPDATING_TABLE' );
1320 ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_IMPLEMENTATIONS' );
1321 --
1322 -- update pa_implementations for customer_id (duplicate_id ).
1323 --
1324 IF g_audit_profile='Y' THEN
1325
1326 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1327 MERGE_LOG_ID,
1328 TABLE_NAME,
1329 MERGE_HEADER_ID,
1330 PRIMARY_KEY_ID,
1331 NUM_COL1_ORIG,
1332 NUM_COL1_NEW,
1333 ACTION_FLAG,
1334 REQUEST_ID,
1335 CREATED_BY,
1336 CREATION_DATE,
1337 LAST_UPDATE_LOGIN,
1338 LAST_UPDATE_DATE,
1339 LAST_UPDATED_BY)
1340 ( select HZ_CUSTOMER_MERGE_LOG_s.nextval,
1341 'PA_IMPLEMENTATIONS_ALL',
1342 RACM.CUSTOMER_MERGE_HEADER_ID,
1343 PI.ORG_ID,
1344 PI.CUSTOMER_ID,
1345 RACM.CUSTOMER_ID,
1346 'U',
1347 req_id,
1348 hz_utility_pub.CREATED_BY,
1349 hz_utility_pub.CREATION_DATE,
1350 hz_utility_pub.LAST_UPDATE_LOGIN,
1351 hz_utility_pub.LAST_UPDATE_DATE,
1352 hz_utility_pub.LAST_UPDATED_BY
1353 FROM PA_IMPLEMENTATIONS PI, (SELECT DISTINCT CUSTOMER_MERGE_HEADER_ID,
1354 CUSTOMER_ID,
1355 DUPLICATE_ID
1356 FROM RA_CUSTOMER_MERGES
1357 WHERE PROCESS_FLAG = 'N'
1358 AND SET_NUMBER = set_no
1359 AND CUSTOMER_ID<>DUPLICATE_ID ) RACM
1360 WHERE RACM.DUPLICATE_ID = PI.CUSTOMER_ID);
1361
1362 END IF;
1363
1364 UPDATE PA_IMPLEMENTATIONS PI -- Bug 3891382
1365 SET ( CUSTOMER_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ID
1366 FROM RA_CUSTOMER_MERGES RACM
1367 WHERE RACM.DUPLICATE_ID = PI.CUSTOMER_ID
1368 AND RACM.PROCESS_FLAG = 'N'
1369 AND RACM.SET_NUMBER = set_no ),
1370 LAST_UPDATE_DATE = SYSDATE,
1371 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
1372 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
1373 WHERE
1374 EXISTS ( SELECT NULL FROM RA_CUSTOMER_MERGES RACM
1375 WHERE RACM.DUPLICATE_ID = PI.CUSTOMER_ID
1376 AND RACM.PROCESS_FLAG = 'N'
1377 AND RACM.SET_NUMBER = set_no );
1378 --
1379 -- update log file to indicate the total records being updated.
1380 --
1381 total_record_upd_count := SQL%ROWCOUNT;
1382 ARP_MESSAGE.SET_NAME( 'AR', 'AR_ROWS_UPDATED' );
1383 ARP_MESSAGE.SET_TOKEN( 'NUM_ROWS', TO_CHAR( total_record_upd_count ));
1384 total_record_upd_count := 0;
1385
1386 /* Added by sbsivara for retention related tables */
1387 --
1388 -- update log file to indicate the table being updated. For bug# 1676538
1389 --
1390 ARP_MESSAGE.SET_NAME( 'AR', 'AR_UPDATING_TABLE' );
1391 ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_PROJ_RETN_RULES' );
1392 --
1393 -- update pa_proj_retn_rules for customer_id (duplicate_id ).
1394
1395 IF g_audit_profile='Y' THEN
1396
1397 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1398 MERGE_LOG_ID,
1399 TABLE_NAME,
1400 MERGE_HEADER_ID,
1401 PRIMARY_KEY_ID,
1402 NUM_COL1_ORIG,
1403 NUM_COL1_NEW,
1404 ACTION_FLAG,
1405 REQUEST_ID,
1406 CREATED_BY,
1407 CREATION_DATE,
1408 LAST_UPDATE_LOGIN,
1409 LAST_UPDATE_DATE,
1410 LAST_UPDATED_BY)
1411 ( select HZ_CUSTOMER_MERGE_LOG_s.nextval,
1412 'PA_PROJ_RETN_RULES',
1413 RACM.CUSTOMER_MERGE_HEADER_ID,
1414 PR.RETENTION_RULE_ID,
1415 PR.CUSTOMER_ID,
1416 RACM.CUSTOMER_ID,
1417 'U',
1418 req_id,
1419 hz_utility_pub.CREATED_BY,
1420 hz_utility_pub.CREATION_DATE,
1421 hz_utility_pub.LAST_UPDATE_LOGIN,
1422 hz_utility_pub.LAST_UPDATE_DATE,
1423 hz_utility_pub.LAST_UPDATED_BY
1424 FROM PA_PROJ_RETN_RULES PR, (SELECT DISTINCT CUSTOMER_MERGE_HEADER_ID,
1425 CUSTOMER_ID,
1426 DUPLICATE_ID FROM RA_CUSTOMER_MERGES
1427 WHERE PROCESS_FLAG = 'N'
1428 AND SET_NUMBER = set_no
1429 AND CUSTOMER_ID<>DUPLICATE_ID) RACM
1430 WHERE RACM.DUPLICATE_ID = PR.CUSTOMER_ID
1431 /* bug 3891382 */
1432 AND EXISTS ( SELECT NULL FROM PA_PROJECTS
1433 WHERE PROJECT_ID = PR.PROJECT_ID ));
1434 /* end 3891382 */
1435
1436
1437 END IF;
1438 --
1439 UPDATE PA_PROJ_RETN_RULES PR
1440 SET ( CUSTOMER_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ID
1441 FROM RA_CUSTOMER_MERGES RACM
1442 WHERE RACM.DUPLICATE_ID = PR.CUSTOMER_ID
1443 AND RACM.PROCESS_FLAG = 'N'
1444 AND RACM.SET_NUMBER = set_no ),
1445 LAST_UPDATE_DATE = SYSDATE,
1446 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID
1447 WHERE
1448 EXISTS ( SELECT NULL FROM RA_CUSTOMER_MERGES RACM
1449 WHERE RACM.DUPLICATE_ID = PR.CUSTOMER_ID
1450 AND RACM.PROCESS_FLAG = 'N'
1451 AND RACM.SET_NUMBER = set_no
1452 /* bug 3891382 */
1453 AND EXISTS ( SELECT NULL FROM PA_PROJECTS
1454 WHERE PROJECT_ID = PR.PROJECT_ID ));
1455 /* end 3891382 */
1456
1457 --
1458 -- update log file to indicate the total records being updated.
1459 --
1460 total_record_upd_count := SQL%ROWCOUNT;
1461 ARP_MESSAGE.SET_NAME( 'AR', 'AR_ROWS_UPDATED' );
1462 ARP_MESSAGE.SET_TOKEN( 'NUM_ROWS', TO_CHAR( total_record_upd_count ));
1463 total_record_upd_count := 0;
1464
1465 --
1466 -- update log file to indicate the table being updated. For bug# 1676538
1467 --
1468 ARP_MESSAGE.SET_NAME( 'AR', 'AR_UPDATING_TABLE' );
1469 ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_PROJ_RETN_BILL_RULES' );
1470 --
1471 -- update pa_proj_retn_bill_rules for customer_id (duplicate_id ).
1472 --
1473 IF g_audit_profile='Y' THEN
1474
1475 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1476 MERGE_LOG_ID,
1477 TABLE_NAME,
1478 MERGE_HEADER_ID,
1479 PRIMARY_KEY_ID,
1480 NUM_COL1_ORIG,
1481 NUM_COL1_NEW,
1482 ACTION_FLAG,
1483 REQUEST_ID,
1484 CREATED_BY,
1485 CREATION_DATE,
1486 LAST_UPDATE_LOGIN,
1487 LAST_UPDATE_DATE,
1488 LAST_UPDATED_BY)
1489 ( select HZ_CUSTOMER_MERGE_LOG_s.nextval,
1490 'PA_PROJ_RETN_BILL_RULES',
1491 RACM.CUSTOMER_MERGE_HEADER_ID,
1492 PB. RETN_BILLING_RULE_ID,
1493 PB.CUSTOMER_ID,
1494 RACM.CUSTOMER_ID,
1495 'U',
1496 req_id,
1497 hz_utility_pub.CREATED_BY,
1498 hz_utility_pub.CREATION_DATE,
1499 hz_utility_pub.LAST_UPDATE_LOGIN,
1500 hz_utility_pub.LAST_UPDATE_DATE,
1501 hz_utility_pub.LAST_UPDATED_BY
1502 FROM PA_PROJ_RETN_BILL_RULES PB,(SELECT DISTINCT CUSTOMER_MERGE_HEADER_ID,
1503 CUSTOMER_ID,
1504 DUPLICATE_ID FROM RA_CUSTOMER_MERGES
1505 WHERE PROCESS_FLAG ='N'
1506 AND SET_NUMBER = set_no
1507 AND CUSTOMER_ID<>DUPLICATE_ID) RACM
1508 WHERE RACM.DUPLICATE_ID = PB.CUSTOMER_ID
1509 /* bug 3891382 */
1510 AND EXISTS ( SELECT NULL FROM PA_PROJECTS
1511 WHERE PROJECT_ID = PB.PROJECT_ID ));
1512 /* end 3891382 */
1513
1514
1515 END IF;
1516
1517 UPDATE PA_PROJ_RETN_BILL_RULES PB
1518 SET ( CUSTOMER_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ID
1519 FROM RA_CUSTOMER_MERGES RACM
1520 WHERE RACM.DUPLICATE_ID = PB.CUSTOMER_ID
1521 AND RACM.PROCESS_FLAG = 'N'
1522 AND RACM.SET_NUMBER = set_no ),
1523 LAST_UPDATE_DATE = SYSDATE,
1524 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID
1525 WHERE
1526 EXISTS ( SELECT NULL FROM RA_CUSTOMER_MERGES RACM
1527 WHERE RACM.DUPLICATE_ID = PB.CUSTOMER_ID
1528 AND RACM.PROCESS_FLAG = 'N'
1529 AND RACM.SET_NUMBER = set_no
1530 /* bug 3891382 */
1531 AND EXISTS ( SELECT NULL FROM PA_PROJECTS
1532 WHERE PROJECT_ID = PB.PROJECT_ID ));
1533 /* end 3891382 */
1534
1535 --
1536 -- update log file to indicate the total records being updated.
1537 --
1538 total_record_upd_count := SQL%ROWCOUNT;
1539 ARP_MESSAGE.SET_NAME( 'AR', 'AR_ROWS_UPDATED' );
1540 ARP_MESSAGE.SET_TOKEN( 'NUM_ROWS', TO_CHAR( total_record_upd_count ));
1541 total_record_upd_count := 0;
1542
1543 --
1544 -- update log file to indicate the table being updated. For bug# 1676538
1545 --
1546 ARP_MESSAGE.SET_NAME( 'AR', 'AR_UPDATING_TABLE' );
1547 ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_SUMMARY_PROJECT_RETN' );
1548 --
1549 -- update pa_summary_project_retn for customer_id (duplicate_id ).
1550 --
1551 IF g_audit_profile='Y' THEN
1552
1553 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
1554 MERGE_LOG_ID,
1555 TABLE_NAME,
1556 MERGE_HEADER_ID,
1557 PRIMARY_KEY1,
1558 PRIMARY_KEY2,
1559 PRIMARY_KEY3,
1560 NUM_COL1_ORIG,
1561 NUM_COL1_NEW,
1562 ACTION_FLAG,
1563 REQUEST_ID,
1564 CREATED_BY,
1565 CREATION_DATE,
1566 LAST_UPDATE_LOGIN,
1567 LAST_UPDATE_DATE,
1568 LAST_UPDATED_BY)
1569 ( select HZ_CUSTOMER_MERGE_LOG_s.nextval,
1570 'PA_SUMMARY_PROJECT_RETN',
1571 RACM.CUSTOMER_MERGE_HEADER_ID,
1572 PS.PROJECT_ID,
1573 PS.TASK_ID,
1574 PS.AGREEMENT_ID,
1575 PS.CUSTOMER_ID,
1576 RACM.CUSTOMER_ID,
1577 'U',
1578 req_id,
1579 hz_utility_pub.CREATED_BY,
1580 hz_utility_pub.CREATION_DATE,
1581 hz_utility_pub.LAST_UPDATE_LOGIN,
1582 hz_utility_pub.LAST_UPDATE_DATE,
1583 hz_utility_pub.LAST_UPDATED_BY
1584 FROM PA_SUMMARY_PROJECT_RETN PS, (SELECT DISTINCT CUSTOMER_MERGE_HEADER_ID,
1585 CUSTOMER_ID,
1586 DUPLICATE_ID FROM RA_CUSTOMER_MERGES
1587 WHERE PROCESS_FLAG = 'N'
1588 AND SET_NUMBER = set_no
1589 AND DUPLICATE_ID<>CUSTOMER_ID)RACM
1590 WHERE RACM.DUPLICATE_ID = PS.CUSTOMER_ID
1591 /* bug 3891382 */
1592 AND EXISTS ( SELECT NULL FROM PA_PROJECTS
1593 WHERE PROJECT_ID = PS.PROJECT_ID ));
1594 /* end 3891382 */
1595
1596
1597 END IF;
1598 UPDATE PA_SUMMARY_PROJECT_RETN PS
1599 SET ( CUSTOMER_ID ) = ( SELECT DISTINCT RACM.CUSTOMER_ID
1600 FROM RA_CUSTOMER_MERGES RACM
1601 WHERE RACM.DUPLICATE_ID = PS.CUSTOMER_ID
1602 AND RACM.PROCESS_FLAG = 'N'
1603 AND RACM.SET_NUMBER = set_no ),
1604 LAST_UPDATE_DATE = SYSDATE,
1605 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID
1606 WHERE
1607 EXISTS ( SELECT NULL FROM RA_CUSTOMER_MERGES RACM
1608 WHERE RACM.DUPLICATE_ID = PS.CUSTOMER_ID
1609 AND RACM.PROCESS_FLAG = 'N'
1610 AND RACM.SET_NUMBER = set_no
1611 /* bug 3891382 */
1612 AND EXISTS ( SELECT NULL FROM PA_PROJECTS
1613 WHERE PROJECT_ID = PS.PROJECT_ID ));
1614 /* end 3891382 */
1615
1616 --
1617 -- update log file to indicate the total records being updated.
1618 --
1619 total_record_upd_count := SQL%ROWCOUNT;
1620 ARP_MESSAGE.SET_NAME( 'AR', 'AR_ROWS_UPDATED' );
1621 ARP_MESSAGE.SET_TOKEN( 'NUM_ROWS', TO_CHAR( total_record_upd_count ));
1622 total_record_upd_count := 0;
1623
1624 /* END Added by sbsivara for retention related tables */
1625 --
1626 -- update pa_agreements for customer_id only.
1627 --
1628 PAP_CMERGE_BB1.MERGE_PA_AGREEMENTS( req_id, set_no );
1629 --
1630 -- update pa_project_contacts for customer_id only.
1631 --
1632 PAP_CMERGE_BB2.MERGE_PA_PROJECT_CONTACTS( req_id, set_no );
1633 --
1634 --
1635 <<done_locking>>
1636 --
1637 -- update log file to indicate the successful exit of this module.
1638 --
1639 ARP_MESSAGE.SET_LINE( 'PAP_CMERGE.MERGE()-' );
1640 --
1641 --
1642 EXCEPTION
1643
1644 WHEN OTHERS THEN
1645 ARP_MESSAGE.SET_ERROR( 'PAP_CMERGE.MERGE' );
1646 RAISE;
1647
1648 END MERGE;
1649 --
1650 --
1651 /********************* End of Mission ***********************/
1652 END PAP_CMERGE;