[Home] [Help]
PACKAGE BODY: APPS.PAP_CMERGE_BB2
Source
1 PACKAGE BODY PAP_CMERGE_BB2 AS
2 -- $Header: PAPCMR2B.pls 120.1 2005/08/05 00:29:59 rgandhi noship $
3 --
4 -- function to retrieve new and old customer id's.
5 --
6 FUNCTION RETRIEVE_CUSTOMER_ID(
7 set_no IN NUMBER,
8 var_customer_id IN pa_project_contacts.customer_id%TYPE,
9 var_new_customer_id OUT NOCOPY ra_customer_merges.customer_id%TYPE,/*File.sql.39*/
10 var_old_customer_id OUT NOCOPY ra_customer_merges.customer_id%TYPE /*File.sql.39*/ )
11 --
12 RETURN BOOLEAN IS
13 BEGIN
14 --
15 var_new_customer_id := NULL;
16 var_old_customer_id := NULL;
17 --
18 SELECT DISTINCT RACM.CUSTOMER_ID, RACM.DUPLICATE_ID INTO
19 var_new_customer_id, var_old_customer_id
20 FROM RA_CUSTOMER_MERGES RACM
21 WHERE RACM.DUPLICATE_ID = var_customer_id
22 AND RACM.PROCESS_FLAG = 'N'
23 AND RACM.SET_NUMBER = set_no;
24 --
25 RETURN TRUE;
26 --
27 EXCEPTION
28 WHEN NO_DATA_FOUND THEN
29 RETURN FALSE;
30 WHEN OTHERS THEN
31 RETURN FALSE;
32 --
33 END RETRIEVE_CUSTOMER_ID;
34 --
35 FUNCTION CHECK_FOR_MUL_BILLING_CON(
36 var_project_id IN pa_project_contacts.project_id%TYPE,
37 var_new_customer_id IN pa_project_contacts.customer_id%TYPE )
38 --
39 RETURN BOOLEAN IS
40 --
41 dummy_project_id pa_project_contacts.project_id%TYPE;
42 dummy_customer_id pa_project_contacts.customer_id%TYPE;
43 dummy_contact_id pa_project_contacts.contact_id%TYPE;
44 dummy_proj_con_type_code pa_project_contacts.project_contact_type_code%TYPE;
45 --
46 BEGIN
47 --
48 SELECT DISTINCT PROJECT_ID, CUSTOMER_ID, CONTACT_ID,
49 PROJECT_CONTACT_TYPE_CODE
50 INTO dummy_project_id, dummy_customer_id, dummy_contact_id,
51 dummy_proj_con_type_code
52 FROM PA_PROJECT_CONTACTS PC
53 WHERE PC.PROJECT_ID = var_project_id
54 AND PC.CUSTOMER_ID = var_new_customer_id
55 AND PC.CONTACT_ID >= 0
56 AND PC.PROJECT_CONTACT_TYPE_CODE = 'BILLING';
57 --
58 RETURN TRUE;
59 --
60 EXCEPTION
61 --
62 WHEN NO_DATA_FOUND THEN
63 RETURN FALSE;
64 WHEN OTHERS THEN
65 RETURN FALSE;
66 --
67 END CHECK_FOR_MUL_BILLING_CON;
68 --
69 FUNCTION DELETE_BILLING_CONTACTS(
70 var_project_id IN pa_project_contacts.project_id%TYPE,
71 var_old_customer_id IN pa_project_contacts.customer_id%TYPE,
72 var_new_customer_id IN pa_project_contacts.customer_id%TYPE,
73 var_cust_merge_head_id IN ra_customer_merges.customer_merge_header_id%TYPE,
74 request_id IN NUMBER)
75 --
76
77 --
78 RETURN BOOLEAN IS
79 --
80 BEGIN
81 --
82 /* Added for Tca audit*/
83 IF pap_cmerge.g_audit_profile = 'Y' THEN
84 --
85 -- Inserting the data into HZ_CUSTOMER_MERGE_LOG table for
86 -- PA_PROJECT_CONTACTS table. All rows are inserted that are going
87 -- to be deleted for old customer.
88 --
89 /*DEL_COL11 added for customer account relation enhancement*/
90 INSERT INTO hz_customer_merge_log
91 ( MERGE_LOG_ID,
92 MERGE_HEADER_ID ,
93 REQUEST_ID,
94 TABLE_NAME,
95 PRIMARY_KEY1,
96 PRIMARY_KEY2,
97 PRIMARY_KEY3,
98 PRIMARY_KEY4,
99 ACTION_FLAG,
100 DEL_COL1,
101 DEL_COL2,
102 DEL_COL3,
103 DEL_COL4,
104 DEL_COL5,
105 DEL_COL6,
106 DEL_COL7,
107 DEL_COL8,
108 DEL_COL9,
109 DEL_COL10,
110 DEL_COL11,
111 CREATED_BY,
112 CREATION_DATE ,
113 LAST_UPDATED_BY ,
114 LAST_UPDATE_DATE,
115 LAST_UPDATE_LOGIN )
116 (select
117 HZ_CUSTOMER_MERGE_LOG_S.nextval,
118 var_cust_merge_head_id,
119 request_id,
120 'PA_PROJECT_CONTACTS',
121 var_project_id,
122 var_new_customer_id,
123 PC.contact_id,
124 PC.project_contact_type_code,
125 'D',
126 PC.PROJECT_ID,
127 PC.CUSTOMER_ID,
128 PC.CONTACT_ID,
129 PC.PROJECT_CONTACT_TYPE_CODE,
130 PC.LAST_UPDATE_DATE ,
131 PC.LAST_UPDATED_BY,
132 PC.CREATION_DATE,
133 PC.CREATED_BY,
134 PC.LAST_UPDATE_LOGIN ,
135 PC.RECORD_VERSION_NUMBER ,
136 PC.BILL_SHIP_CUSTOMER_ID,
137 hz_utility_pub.CREATED_BY,
138 hz_utility_pub.CREATION_DATE,
139 hz_utility_pub.LAST_UPDATE_LOGIN,
140 hz_utility_pub.LAST_UPDATE_DATE,
141 hz_utility_pub.LAST_UPDATED_BY
142 FROM PA_PROJECT_CONTACTS PC
143 WHERE PC.PROJECT_ID = var_project_id
144 AND PC.CUSTOMER_ID = var_old_customer_id
145 AND PC.CONTACT_ID >= 0
146 AND PC.PROJECT_CONTACT_TYPE_CODE = 'BILLING');
147 END IF;
148 /* End for Tca Audit */
149
150 DELETE PA_PROJECT_CONTACTS PC
151 WHERE PC.PROJECT_ID = var_project_id
152 AND PC.CUSTOMER_ID = var_old_customer_id
153 AND PC.CONTACT_ID >= 0
154 AND PC.PROJECT_CONTACT_TYPE_CODE = 'BILLING';
155 --
156 RETURN TRUE;
157 --
158 EXCEPTION
159 --
160 WHEN OTHERS THEN
161 RETURN FALSE;
162 --
163 END DELETE_BILLING_CONTACTS;
164 --
165 PROCEDURE MERGE_PA_PROJECT_CONTACTS ( req_id IN NUMBER, set_no IN NUMBER ) IS
166 /*CURSOR cursor_3 IS SELECT PROJECT_ID, CUSTOMER_ID FROM pa_project_contacts;*/
167
168 /*Cursor_3 fetches all those records for which the
169 primary customer has been merged.
170 Cursor_4 will fetch all those records for which
171 bill_ship_customer has been merged*/
172
173 CURSOR cursor_3 IS SELECT DISTINCT pc.project_id,
174 PC.CUSTOMER_ID,
175 PC.CONTACT_ID,
176 PC.PROJECT_CONTACT_TYPE_CODE,
177 RACM.CUSTOMER_ID,
178 RACM.CUSTOMER_MERGE_HEADER_ID,
179 PC.BILL_SHIP_CUSTOMER_ID /*For customer account relationship*/
180 FROM pa_project_contacts pc,ra_customer_merges RACM
181 WHERE RACM.DUPLICATE_ID = PC.CUSTOMER_ID
182 AND RACM.PROCESS_FLAG = 'N'
183 AND RACM.SET_NUMBER = set_no
184 AND RACM.CUSTOMER_ID <> RACM.DUPLICATE_ID
185 /* Bug 3891382. Added the condition so that the cursor picks projects specific to the
186 org_id where customer merge has taken place. */
187 AND EXISTS ( SELECT NULL FROM PA_PROJECTS
188 WHERE PROJECT_ID = PC.PROJECT_ID );
189
190 CURSOR cursor_4 IS SELECT DISTINCT pc.project_id,
191 PC.CUSTOMER_ID,
192 PC.CONTACT_ID,
193 PC.PROJECT_CONTACT_TYPE_CODE,
194 RACM.CUSTOMER_ID,
195 RACM.CUSTOMER_MERGE_HEADER_ID,
196 PC.BILL_SHIP_CUSTOMER_ID /*For customer account relationship*/
197 FROM pa_project_contacts pc,ra_customer_merges RACM
198 WHERE RACM.DUPLICATE_ID = PC.BILL_SHIP_CUSTOMER_ID
199 AND RACM.PROCESS_FLAG = 'N'
200 AND RACM.SET_NUMBER = set_no
201 AND RACM.CUSTOMER_ID <> RACM.DUPLICATE_ID
202 /* Bug 3891382. Added the condition so that the cursor picks projects specific to the
203 org_id where customer merge has taken place. */
204 AND EXISTS ( SELECT NULL FROM PA_PROJECTS
205 WHERE PROJECT_ID = PC.PROJECT_ID );
206
207 --
208
209 var_project_id pa_project_contacts.project_id%TYPE;
210 var_customer_id pa_project_contacts.customer_id%TYPE;
211 var_contact_id pa_project_contacts.contact_id%TYPE;
212 var_contact_type_code pa_project_contacts.project_contact_type_code%TYPE;
213 var_bill_ship_customer_id pa_project_contacts.bill_ship_customer_id%TYPE;/*Added for customer account*/
214 var_old_customer_id pa_project_contacts.customer_id%TYPE; /*uncommented for customer account relation*/
215 -- var_new_customer_id pa_project_contacts.customer_id%TYPE;
216 var_racm_customer_id ra_customer_merges.customer_id%TYPE;/*Added for TCA AUDIT */
217 var_cust_merge_header_id ra_customer_merges.CUSTOMER_MERGE_HEADER_ID%TYPE;/*Added for TCA AUDIT */
218
219 data_found BOOLEAN;
220 total_record_upd_count NUMBER := 0;
221 total_record_del_count NUMBER := 0;
222 --
223 BEGIN
224 --
225 -- update log file for entering this module successfully.
226 --
227 ARP_MESSAGE.SET_LINE( 'PAP_CMERGE_BB1.MERGE_PA_PROJECT_CONTACTS()+' );
228 --
229 -- update log file to indicate table name being updated.
230 --
231 ARP_MESSAGE.SET_NAME( 'AR', 'AR_UPDATING_TABLE' );
232 ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_PROJECT_CONTACTS' );
233 --
234 OPEN cursor_3;
235 --
236 LOOP
237 --
238 -- fetch each row from pa_project_contacts table.
239 --
240 FETCH cursor_3 INTO var_project_id,
241 var_customer_id,
242 var_contact_id,
243 var_contact_type_code,
244 var_racm_customer_id,
245 var_cust_merge_header_id,
246 var_bill_ship_customer_id;
247 --
248 EXIT WHEN cursor_3%NOTFOUND;
249 --
250 -- verify whether customer merge is necessary.
251 --
252 /*data_found := RETRIEVE_CUSTOMER_ID( set_no,
253 var_customer_id,
254 var_new_customer_id,
255 var_old_customer_id );
256 --
257 if data_found then Commented for TCA audit */
258 --
259 -- check for multiple billing contact.
260 --
261 data_found := CHECK_FOR_MUL_BILLING_CON( var_project_id,
262 var_racm_customer_id );
263 --
264 -- /*Following AND condition added for bug 2646936 */
265 if data_found then /*AND (var_new_customer_id <> var_old_customer_id ) then Commented for Tca audit */
266 --
267 -- delete the second billing contact since we need only one billing contact.
268 --
269 data_found := DELETE_BILLING_CONTACTS( var_project_id,
270 var_customer_id,
271 var_racm_customer_id,
272 var_cust_merge_header_id,
273 req_id);
274 --
275 if data_found then
276 --
277 total_record_del_count := total_record_del_count + 1;
278 --
279 end if;
280 --
281 end if;
282 /* Added for TCA audit */
283 -- Checking for the profile flag for audit.
284 --
285 IF pap_cmerge.g_audit_profile = 'Y' THEN
286 --
287 -- Inserting data into HZ_CUSTOMER_MERGE_LOG table for
288 -- PA_PROJECT_CONTACTS table the new and old contact id.
289 --
290 INSERT INTO hz_customer_merge_log
291 (
292 MERGE_LOG_ID,
293 MERGE_HEADER_ID,
294 REQUEST_ID,
295 TABLE_NAME,
296 PRIMARY_KEY1,
297 PRIMARY_KEY2,
298 PRIMARY_KEY3,
299 PRIMARY_KEY4,
300 NUM_COL1_ORIG,
301 NUM_COL1_NEW ,
302 ACTION_FLAG,
303 CREATED_BY,
304 CREATION_DATE ,
305 LAST_UPDATED_BY ,
306 LAST_UPDATE_DATE,
307 LAST_UPDATE_LOGIN )
308 (SELECT
309 HZ_CUSTOMER_MERGE_LOG_S.nextval,
310 var_cust_merge_header_id,
311 req_id,
312 'PA_PROJECT_CONTACTS',
313 var_project_id,
314 var_racm_customer_id,
315 PC.contact_id,
316 PC.project_contact_type_code,
317 var_customer_id,
318 var_racm_customer_id,
319 'U',
320 hz_utility_pub.CREATED_BY,
321 hz_utility_pub.CREATION_DATE,
322 hz_utility_pub.LAST_UPDATE_LOGIN,
323 hz_utility_pub.LAST_UPDATE_DATE,
324 hz_utility_pub.LAST_UPDATED_BY
325 FROM pa_project_contacts PC
326 WHERE PC.PROJECT_ID = var_project_id
327 AND PC.CUSTOMER_ID = var_customer_id);
328
329
330 END IF; -- end if of IF pap_cmerge.g_audit_profile = 'Y' THEN
331 --
332 /* end of Tca audit*/
333 --
334 -- update table pa_project_contacts with new customer_id.
335 --
336 UPDATE pa_project_contacts PC
337 SET CUSTOMER_ID = var_racm_customer_id,
338 LAST_UPDATE_DATE = SYSDATE,
339 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
340 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
341 WHERE
342 PC.PROJECT_ID = var_project_id
343 AND PC.CUSTOMER_ID = var_customer_id;
344 --
345 total_record_upd_count := total_record_upd_count + SQL%ROWCOUNT;
346 --
347 /*end if; Commented for Tca Audit */
348 var_customer_id :=var_racm_customer_id; /*Added for customer account */
349 --
350 END LOOP; /*FOR CURSOR_3*/
351
352 CLOSE cursor_3;
353 /*Done with the update of primary customer.The code to follow will take care
354 of the cases where the bill_ship_customer has been merged */
355 OPEN cursor_4;
356 --
357 LOOP
358 --
359 -- fetch each row from pa_project_contacts table.
360 --
361 FETCH cursor_4 INTO var_project_id,
362 var_customer_id,
363 var_contact_id,
364 var_contact_type_code,
365 var_racm_customer_id,
366 var_cust_merge_header_id,
367 var_bill_ship_customer_id;
368 --
369 EXIT WHEN cursor_4%NOTFOUND;
370
371 /* Added for customer account*/
372 /* data_found := RETRIEVE_CUSTOMER_ID( set_no,
373 var_bill_ship_customer_id,
374 var_racm_customer_id,
375 var_old_customer_id );
376 if data_found then
377
378 commenting out this code as the new bill_ship_customer_id will
379 be present in var_racm_customer_id fetched from cursor_4*/
380
381 IF pap_cmerge.g_audit_profile = 'Y' THEN
382 --
383 -- Inserting data into HZ_CUSTOMER_MERGE_LOG table for
384 -- PA_PROJECT_CONTACTS table the new and old contact id.
385 --
386 /*The new customer_id will be present in var_customer_id*/
387 INSERT INTO hz_customer_merge_log
388 (
389 MERGE_LOG_ID,
390 MERGE_HEADER_ID,
391 REQUEST_ID,
392 TABLE_NAME,
393 PRIMARY_KEY1,
394 PRIMARY_KEY2,
395 PRIMARY_KEY3,
396 PRIMARY_KEY4,
397 NUM_COL2_ORIG,
398 NUM_COL2_NEW ,
399 ACTION_FLAG,
400 CREATED_BY,
401 CREATION_DATE ,
402 LAST_UPDATED_BY ,
403 LAST_UPDATE_DATE,
404 LAST_UPDATE_LOGIN )
405 (SELECT
406 HZ_CUSTOMER_MERGE_LOG_S.nextval,
407 var_cust_merge_header_id,
408 req_id,
409 'PA_PROJECT_CONTACTS',
410 var_project_id,
411 var_customer_id,
412 PC.contact_id,
413 PC.project_contact_type_code,
414 var_bill_ship_customer_id,
415 var_racm_customer_id,
416 'U',
417 hz_utility_pub.CREATED_BY,
418 hz_utility_pub.CREATION_DATE,
419 hz_utility_pub.LAST_UPDATE_LOGIN,
420 hz_utility_pub.LAST_UPDATE_DATE,
421 hz_utility_pub.LAST_UPDATED_BY
422 FROM pa_project_contacts PC
423 WHERE PC.PROJECT_ID = var_project_id
424 AND PC.CUSTOMER_ID = var_customer_id
425 AND PC.BILL_SHIP_CUSTOMER_ID=var_bill_ship_customer_id);
426
427
428 END IF; -- end if of IF pap_cmerge.g_audit_profile = 'Y' THEN
429
430 UPDATE pa_project_contacts PC
431 SET BILL_SHIP_CUSTOMER_ID = var_racm_customer_id,
432 LAST_UPDATE_DATE = SYSDATE,
433 LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
434 LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
435 WHERE
436 PC.PROJECT_ID = var_project_id
437 AND PC.CUSTOMER_ID = var_customer_id
438 AND PC.BILL_SHIP_CUSTOMER_ID=var_bill_ship_customer_id;
439
440 total_record_upd_count := total_record_upd_count + SQL%ROWCOUNT;
441
442
443 END LOOP;
444
445 CLOSE cursor_4;
446 --
447 -- close the opened cursor.
448 --
449 /* CLOSE cursor_3; commented out for CACR*/
450 --
451 -- update log file for total records updated.
452 --
453 ARP_MESSAGE.SET_NAME( 'AR', 'AR_ROWS_UPDATED' );
454 ARP_MESSAGE.SET_TOKEN( 'NUM_ROWS', TO_CHAR( total_record_upd_count ));
455 total_record_upd_count := 0;
456 --
457 -- update log file for total records deleted.
458 --
459 ARP_MESSAGE.SET_NAME( 'AR', 'AR_ROWS_DELETED' );
460 ARP_MESSAGE.SET_TOKEN( 'NUM_ROWS', TO_CHAR( total_record_del_count ));
461 total_record_del_count := 0;
462 --
463 -- update log file for exiting this module successfully.
464 --
465 ARP_MESSAGE.SET_LINE( 'PAP_CMERGE_BB1.MERGE_PA_PROJECT_CONTACTS()-' );
466 --
467 EXCEPTION
468 --
469 WHEN OTHERS THEN
470 ARP_MESSAGE.SET_ERROR( 'PAP_CMERGE_BB1.MERGE_PA_PROJECT_CONTACTS' );
471 RAISE;
472
473 END MERGE_PA_PROJECT_CONTACTS;
474 --
475 END PAP_CMERGE_BB2;