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