1 PACKAGE BODY CSC_PARTY_MERGE_PKG AS
2 /* $Header: cscvmptb.pls 115.8 2004/04/28 08:09:51 bhroy ship $ */
3 -- Start of Comments
4 -- Package name : CSC_PARTY_MERGE_PKG
5 -- Purpose : Merges duplicate parties in Customer Care tables. The
6 -- Customer Care table that need to be considered for
7 -- Party Merge are:
8 -- CSC_CUSTOMERS, CSC_CUSTOMERS_AUDIT_HIST,
9 -- CSC_CUSTOMIZED_PLANS, CSC_CUST_PLANS,
10 -- CSC_CUST_PLANS_AUDIT
11 --
12 -- History
13 -- MM-DD-YYYY NAME MODIFICATIONS
14 -- 10-10-2000 dejoseph Created.
15 -- 10-25-2001 dejoseph Made the following corrections:
16 -- -- Replaced calls to arp_message with fnd_file.put_line
17 -- -- Removed logic to stop merge for CSC_CUSTOMERS
18 -- -- Added logic for CSC_CUSTOMERS to handle cases where
19 -- only the From or To party exists in the table.
20 -- -- Does not return an error status for any reason. Instead
21 -- prg. logs an error and returns control so that
22 -- execution can continue for the other products.
23 -- -- Included the dbdrv command for the auto db driver.
24 -- 10-31-2001 dejoseph Corrected the update statement in the condition where the
25 -- to party does not exist from 'where party_id = p_to_fk_id'
26 -- to 'where party_id = p_from_fk_id'.
27 -- Ref. Bug # 2090117.
28 -- 13-JUN-2003 bhroy Audit table was not entering correct data, Ref. Bug# 2919377
29 -- 26-JUN-2003 bhroy Corrected end date for transferred plans, Ref. Bug# 2919469
30 -- 03-FEB-2004 bhroy Corrected update of Critical Customer Audit History table, Ref. Bug# 3404893
31 -- 04-FEB-2004 bhroy Corrected Account transfer in case of Party merge, Ref. Bug# 3408084
32 -- 28-APR-2004 bhroy Corrected update of Critical Customer table, trunc(SYSDATE) Ref. Bug# 3589317
33 --
34 -- End of Comments
35 -- GLOBAL VARIABLE TO STORE REQUEST_ID and MERGE_REASON_CODE OF CURRENT MERGE BATCH
36 G_REQUEST_ID NUMBER(15) := TO_NUMBER(NULL);
37 G_MERGE_REASON_CODE VARCHAR2(30) := NULL;
38 G_MERGE_PLAN VARCHAR2(10) := CSC_CORE_UTILS_PVT.MERGE_PLAN;
39 G_TRANSFER_PLAN VARCHAR2(10) := CSC_CORE_UTILS_PVT.TRANSFER_PLAN;
40
41 PROCEDURE get_hz_merge_batch (
42 p_batch_id IN NUMBER )
43 IS
44 BEGIN
45 select request_id , merge_reason_code
46 into G_REQUEST_ID , G_MERGE_REASON_CODE
47 from hz_merge_batch
48 where batch_id = p_batch_id;
49
50 EXCEPTION
51 when others then
52 G_REQUEST_ID := TO_NUMBER(NULL);
53 G_MERGE_REASON_CODE := NULL;
54 END;
55
56 PROCEDURE CSC_CUSTOMERS_MERGE (
57 p_entity_name IN VARCHAR2,
58 p_from_id IN NUMBER,
59 x_to_id OUT NOCOPY NUMBER,
60 p_from_fk_id IN NUMBER,
61 p_to_fk_id IN NUMBER,
62 p_parent_entity_name IN VARCHAR2,
63 p_batch_id IN NUMBER,
64 p_batch_party_id IN NUMBER,
65 x_return_status OUT NOCOPY VARCHAR2)
66 IS
67 cursor c1 is
68 select 1
69 from csc_customers
70 where party_id = p_from_fk_id
71 for update nowait;
72
73 cursor get_from_pty is
74 select override_flag from_override_flag,
75 overridden_critical_flag from_overridden_critical_flag,
76 rowid from_rowid,
77 cust_account_id from_cust_account_id,
78 overridden_critical_flag from_overridden_critical_flag,
79 override_reason_code from_override_reason_code
80 from csc_customers
81 where party_id = p_from_fk_id;
82
83 cursor get_to_pty is
84 select override_flag to_override_flag,
85 overridden_critical_flag to_overridden_critical_flag
86 from csc_customers
87 where party_id = p_to_fk_id;
88
89 G_PROC_NAME CONSTANT VARCHAR2(30) := 'CSC_PARTY_MERGE_PKG';
90 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cscvmpts.pls';
91 G_USER_ID CONSTANT NUMBER(15) := FND_GLOBAL.USER_ID;
92 G_LOGIN_ID CONSTANT NUMBER(15) := FND_GLOBAL.CONC_LOGIN_ID;
93
94 l_api_name VARCHAR2(30) := 'CSC_CUSTOMERS_MERGE';
95 l_count NUMBER(10) := 0;
96 l_to_override_flag VARCHAR2(3);
97 l_from_override_flag VARCHAR2(3);
98 l_to_critical_flag VARCHAR2(3);
99 l_from_critical_flag VARCHAR2(3);
100 l_from_rowid VARCHAR2(2000);
101 l_from_cust_account_id NUMBER(15);
102 l_from_overridden_crit_flag VARCHAR2(3);
103 l_from_override_reason_code VARCHAR2(30);
104
105 RESOURCE_BUSY EXCEPTION;
106 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
107
108 g_mesg VARCHAR2(1000) := '';
109
110 BEGIN
111
112 g_mesg := 'CSC_PARTY_MERGE_PKG.CSC_CUSTOMERS_MERGE';
113 fnd_file.put_line(fnd_file.log, g_mesg);
114
115 x_return_status := CSC_CORE_UTILS_PVT.G_RET_STS_SUCCESS;
116
117 if (g_merge_reason_code is null) then
118 get_hz_merge_batch(
119 p_batch_id => p_batch_id);
120 end if;
121
122 if G_MERGE_REASON_CODE = 'DUPLICATE' then
123 -- if reason code is duplicate then allow the party merge to happen without
124 -- any validations.
125 null;
126 else
127 -- if there are any validations to be done, include it in this section
128 null;
129 end if;
130
131 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
132 -- needs to be done. Set Merged To Id is same as Merged From Id and return
133 if p_from_fk_id = p_to_fk_id then
134 x_to_id := p_from_id;
135 g_mesg := 'To and From Parties are the same. Merge not required.';
136 fnd_file.put_line(fnd_file.log, g_mesg);
137 return;
138 end if;
139
140 -- Please ignore the following comments. Parties in CSC_CUSTOMERS will be
141 -- allowed to merge always, ir-respective of the criticality of the From
142 -- or To party.
143 /* ignore comments section
144 -- The following are the scenarios that are to be considered when merging
145 -- parties in the CSC_CUSTOMERS table. If Party A is merging with Party B,
146 -- then these are the following scenarios and their merge outcomes.
147 -- (OSa => Over-ride state of party A) (values are off column OVERRIDE_FLAG)
148 -- Party A Party B Allow Merge or Not
149 -- ------------------------------------------------------------------------
150 -- Y N Do not allow if OSa='Critical', else allow
151 -- Y Y Allow if OSa=OSb or OSa IN (not critical, system determined)
152 -- else do not allow
153 -- N N Always allow
154 -- N Y Always allow
155 end ignore comments section */
156
157 open get_from_pty;
158 fetch get_from_pty into l_from_override_flag , l_from_critical_flag,
159 l_from_rowid , l_from_cust_account_id,
160 l_from_overridden_crit_flag , l_from_override_reason_code;
161
162 -- If the From party is not found in CSC_CUSTOMERS, then the merge process need not be
163 -- performed, coz there is no party to merge.
164
165 if ( get_from_pty%NOTFOUND ) then
166 close get_from_pty;
167 g_mesg := 'From party not defined in CSC_CUSTOMERS. Merge not required.';
168 fnd_file.put_line(fnd_file.log, g_mesg);
169 return;
170 end if;
171
172 close get_from_pty;
173
174 open get_to_pty;
175 fetch get_to_pty into l_to_override_flag , l_to_critical_flag;
176
177 -- if the To party does not exist in CSC_CUSTOMERS, then update the 'from' party_id
178 -- of CSC_CUSTOMERS to the 'to' party id and insert a record into the audit table
179 -- recording the operation.
180
181 if ( get_to_pty%NOTFOUND ) then
182 close get_to_pty;
183
184 begin
185 update csc_customers
186 set party_id = p_to_fk_id,
187 last_update_date = trunc(SYSDATE),
188 last_updated_by = G_USER_ID,
189 last_update_login = G_LOGIN_ID,
190 request_id = G_REQUEST_ID,
191 program_application_id = ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
192 program_id = ARP_STANDARD.PROFILE.PROGRAM_ID,
193 program_update_date = trunc(SYSDATE)
194 where party_id = p_from_fk_id;
195
196 insert into csc_customers_audit_hist (
197 cust_hist_id, party_id, last_update_date,
198 last_updated_by, last_update_login, creation_date,
199 created_by, changed_date, changed_by,
200 sys_det_critical_flag, override_flag, overridden_critical_flag,
201 override_reason_code, request_id,
202 program_application_id,
203 program_id, program_update_date)
204 values (
205 csc_customers_audit_hist_s.nextval, p_to_fk_id, sysdate,
206 g_user_id, g_login_id, sysdate,
207 g_user_id, sysdate, g_user_id,
208 'N', l_from_override_flag, l_from_overridden_crit_flag,
209 l_from_override_reason_code , G_REQUEST_ID,
210 ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
211 ARP_STANDARD.PROFILE.PROGRAM_ID, SYSDATE );
212
213 return;
214 exception
215 when others then
216 g_mesg := substr('To party does not exist; The following SQL error occured : '
217 || sqlerrm,1,1000);
218 fnd_file.put_line(fnd_file.log, g_mesg);
219 return;
220 end;
221 end if;
222
223 close get_to_pty;
224
225 /**** do not perform any check to stop the merge process. Merge in CSC_CUSTOMERS
226 will always happen.
227
228 IF ( ( ( l_from_override_flag = 'Y' and l_to_override_flag = 'N' )
229 AND ( l_from_critical_flag <> 'N' ) )
230 OR ( ( l_from_override_flag = 'Y' and l_to_override_flag = 'Y' )
231 AND ( ( l_from_critical_flag = l_to_critical_flag
232 or l_from_critical_flag = 'N' ) ) )
233 OR ( l_from_override_flag = 'N' and l_to_override_flag = 'N')
234 OR ( l_from_override_flag = 'N' and l_to_override_flag = 'Y' ) )
235 THEN
236 *****/
237 -- If the parent has changed(id. Parent is getting merged) then transfer the
238 -- dependent record to the new parent. Before transferring check if a similar
239 -- dependent record exists on the new parent. If a duplicate exists then do
240 -- not transfer and return the id of the duplicate record as the Merged To Id
241
242 -- In the case of the table CSC_CUSTOMERS there will not be a situation to
243 -- check for duplicates because, the column party_id, which is going to be
244 -- merged/transferred, itself is the primary key for the table.
245 -- Hence, we cannot update the party_id in this table. Instead, set the
246 -- party_status of the merge from party to 'M' (Merged); this record will
247 -- not be shown in the CSC_CUSTOMERS views.
248
249 if p_from_fk_id <> p_to_fk_id then
250 begin
251
252 open c1;
253 close c1;
254
255
256 csc_customers_pkg.update_row (
257 x_rowid => l_from_rowid,
258 x_party_id => p_from_fk_id,
259 x_cust_account_id => l_from_cust_account_id,
260 x_last_update_date => SYSDATE,
261 x_last_updated_by => G_USER_ID,
262 x_last_update_login => G_LOGIN_ID,
263 x_creation_date => SYSDATE, -- value used for audit table purposes
264 x_created_by => G_USER_ID, -- value used for audit table purposes
265 x_sys_det_critical_flag => 'N', -- value not changed in update stmt. in pkg
266 x_override_flag => l_from_override_flag,
267 x_overridden_critical_flag => l_from_overridden_crit_flag,
268 x_override_reason_code => l_from_override_reason_code,
269 p_party_status => 'M',
270 p_request_id => G_REQUEST_ID,
271 p_program_application_id => ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
272 p_program_id => ARP_STANDARD.PROFILE.PROGRAM_ID,
273 p_program_update_date => SYSDATE );
274
275 exception
276 when resource_busy then
277 -- x_return_status := CSC_CORE_UTILS_PVT.G_RET_STS_ERROR;
278 g_mesg := 'Could not obtain lock for records in table CSC_CUSTOMERS. Please '
279 || 'retry the Merge operation later.';
280 fnd_file.put_line(fnd_file.log, g_mesg);
281 --arp_message.set_line(g_proc_name || '.' || l_api_name ||
282 --'; Could not obtain lock for records in table ' ||
283 --'CSC_CUSTOMERS for party_id = ' || p_from_fk_id );
284 raise;
285
286 when others then
287 -- x_return_status := CSC_CORE_UTILS_PVT.G_RET_STS_ERROR;
288 g_mesg := substr(g_proc_name || '.' || l_api_name || ' : ' || sqlerrm,1,1000);
289 fnd_file.put_line(fnd_file.log, g_mesg);
290 --arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
291 raise;
292 end;
293 end if;
294 /***
295 ELSE
296 x_return_status := CSC_CORE_UTILS_PVT.G_RET_STS_ERROR;
297 g_mesg := 'Merge not allowed. Please check criticality of merging parties';
298 fnd_file.put_line(fnd_file.log, g_mesg);
299 --arp_message.set_line(g_proc_name || '.' || l_api_name ||
300 --'; Merge not allowed. Please check criticality of merging parties');
301 return;
302 END IF;
303 ***/
304 EXCEPTION
305 WHEN OTHERS THEN
306 g_mesg := substr(g_proc_name || '.' || l_api_name || ' : ' || sqlerrm,1,1000);
307 fnd_file.put_line(fnd_file.log, g_mesg);
308 raise;
309
310 END CSC_CUSTOMERS_MERGE;
311
312
313 PROCEDURE CSC_CUST_PLANS_MERGE (
314 p_entity_name IN VARCHAR2,
315 p_from_id IN NUMBER,
316 x_to_id OUT NOCOPY NUMBER,
317 p_from_fk_id IN NUMBER,
318 p_to_fk_id IN NUMBER,
319 p_parent_entity_name IN VARCHAR2,
320 p_batch_id IN NUMBER,
321 p_batch_party_id IN NUMBER,
322 x_return_status OUT NOCOPY VARCHAR2)
323 IS
324
325 cursor c1 is
326 select 1
327 from csc_cust_plans
328 where party_id = p_from_fk_id
329 for update nowait;
330
331 G_PROC_NAME CONSTANT VARCHAR2(30) := 'CSC_PARTY_MERGE_PKG';
332 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cscvmpts.pls';
333 G_USER_ID CONSTANT NUMBER(15) := FND_GLOBAL.USER_ID;
334 G_LOGIN_ID CONSTANT NUMBER(15) := FND_GLOBAL.CONC_LOGIN_ID;
335
336 l_api_name VARCHAR2(30) := 'CSC_CUST_PLANS_MERGE';
337 l_count NUMBER(10) := 0;
338 audit_count NUMBER(10) := 0;
339
340 RESOURCE_BUSY EXCEPTION;
341 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
342
343 g_mesg VARCHAR2(1000) := '';
344
345 BEGIN
346 --arp_message.set_line('CSC_PARTY_MERGE_PKG.CSC_CUST_PLANS_MERGE()+');
347 g_mesg := 'CSC_PARTY_MERGE_PKG.CSC_CUST_PLANS_MERGE';
348 fnd_file.put_line(fnd_file.log, g_mesg);
349
350 x_return_status := CSC_CORE_UTILS_PVT.G_RET_STS_SUCCESS;
351
352 if (g_merge_reason_code is null) then
353 get_hz_merge_batch(
354 p_batch_id => p_batch_id);
355 end if;
356
357 if G_MERGE_REASON_CODE = 'DUPLICATE' then
358 -- if reason code is duplicate then allow the party merge to happen without
359 -- any validations.
360 null;
361 else
362 -- if there are any validations to be done, include it in this section
363 null;
364 end if;
365
366 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
367 -- needs to be done. Set Merged To Id as same as Merged From Id and return
368
369 if p_from_fk_id = p_to_fk_id then
370 x_to_id := p_from_id;
371 g_mesg := 'To and From Parties are the same. Merge not required.';
372 fnd_file.put_line(fnd_file.log, g_mesg);
373 return;
374 end if;
375
376 -- If the parent has changed(id. Parent is getting merged) then transfer the
377 -- dependent record to the new parent. Before transferring check if a similar
378 -- dependent record exists on the new parent. If a duplicate exists then do
379 -- not transfer and return the id of the duplicate record as the Merged To Id
380
381 if p_from_fk_id <> p_to_fk_id then
382 open c1;
383 close c1;
384
385 -- NOTE : If update performance is bad...then consider acheiving the same
386 -- logic thru the use of cursors..updating records individually.
387 -- Perform transfer if duplicate plans do not exist between the TO and FROM
388 -- parties
389 -- Bug# 2919377, if plan is transfered then one record will be inserted for
390 -- p_to_fk_id with status transferred, one record will be inserted for
391 -- p_from_fk_id with status merged.Update the Audit table first in case of plan transfer.
392 insert into csc_cust_plans_audit (
393 plan_audit_id, plan_id, party_id, cust_account_id,
394 plan_status_code, request_id, creation_date,
395 created_by, last_update_date, last_updated_by,
396 last_update_login, program_application_id,
397 program_id, program_update_date, object_version_number )
398 select
399 csc_cust_plans_audit_s.nextval, plan_id, p_to_fk_id, cust_account_id,
400 G_TRANSFER_PLAN, G_REQUEST_ID, SYSDATE,
401 G_USER_ID, SYSDATE, G_USER_ID,
402 G_LOGIN_ID, ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
403 ARP_STANDARD.PROFILE.PROGRAM_ID, SYSDATE, 1
404 from csc_cust_plans
405 where party_id = p_from_fk_id
406 and cust_account_id is not null;
407 audit_count := sql%rowcount;
408
409 insert into csc_cust_plans_audit (
410 plan_audit_id, plan_id, party_id, cust_account_id,
411 plan_status_code, request_id, creation_date,
412 created_by, last_update_date, last_updated_by,
413 last_update_login, program_application_id,
414 program_id, program_update_date, object_version_number )
415 select
416 csc_cust_plans_audit_s.nextval, plan_id, p_to_fk_id, cust_account_id,
417 G_TRANSFER_PLAN, G_REQUEST_ID, SYSDATE,
418 G_USER_ID, SYSDATE, G_USER_ID,
419 G_LOGIN_ID, ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
420 ARP_STANDARD.PROFILE.PROGRAM_ID, SYSDATE, 1
421 from csc_cust_plans
422 where party_id = p_from_fk_id and cust_account_id is null
423 and plan_id not in ( select plan_id
424 from csc_cust_plans
425 where party_id = p_to_fk_id );
426 audit_count := audit_count+sql%rowcount;
427
428 insert into csc_cust_plans_audit (
429 plan_audit_id, plan_id, party_id, cust_account_id,
430 plan_status_code, request_id, creation_date,
431 created_by, last_update_date, last_updated_by,
432 last_update_login, program_application_id,
433 program_id, program_update_date, object_version_number )
434 select
435 csc_cust_plans_audit_s.nextval, plan_id, p_from_fk_id, cust_account_id,
436 G_MERGE_PLAN, G_REQUEST_ID, SYSDATE,
437 G_USER_ID, SYSDATE, G_USER_ID,
438 G_LOGIN_ID, ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
439 ARP_STANDARD.PROFILE.PROGRAM_ID, SYSDATE, 1
440 from csc_cust_plans
441 where party_id = p_from_fk_id
442 and cust_account_id is not null;
443 audit_count := audit_count+sql%rowcount;
444
445 insert into csc_cust_plans_audit (
446 plan_audit_id, plan_id, party_id, cust_account_id,
447 plan_status_code, request_id, creation_date,
448 created_by, last_update_date, last_updated_by,
449 last_update_login, program_application_id,
450 program_id, program_update_date, object_version_number )
451 select
452 csc_cust_plans_audit_s.nextval, plan_id, p_from_fk_id, cust_account_id,
453 G_MERGE_PLAN, G_REQUEST_ID, SYSDATE,
454 G_USER_ID, SYSDATE, G_USER_ID,
455 G_LOGIN_ID, ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
456 ARP_STANDARD.PROFILE.PROGRAM_ID, SYSDATE, 1
457 from csc_cust_plans
458 where party_id = p_from_fk_id and cust_account_id is null
459 and plan_id not in ( select plan_id
460 from csc_cust_plans
461 where party_id = p_to_fk_id );
462 audit_count := audit_count+sql%rowcount;
463
464 update csc_cust_plans
465 set party_id = p_to_fk_id,
466 plan_status_code = G_TRANSFER_PLAN,
467 request_id = G_REQUEST_ID,
468 -- end_date_active = SYSDATE,
469 last_update_date = SYSDATE,
470 last_updated_by = G_USER_ID,
471 last_update_login = G_LOGIN_ID,
472 program_application_id = ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
473 program_id = ARP_STANDARD.PROFILE.PROGRAM_ID,
474 program_update_date = SYSDATE,
475 object_version_number = object_version_number + 1
476 where party_id = p_from_fk_id
477 and cust_account_id is not null;
478 l_count := sql%rowcount;
479
480 update csc_cust_plans
481 set party_id = p_to_fk_id,
482 plan_status_code = G_TRANSFER_PLAN,
483 request_id = G_REQUEST_ID,
484 -- end_date_active = SYSDATE,
485 last_update_date = SYSDATE,
486 last_updated_by = G_USER_ID,
487 last_update_login = G_LOGIN_ID,
488 program_application_id = ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
489 program_id = ARP_STANDARD.PROFILE.PROGRAM_ID,
490 program_update_date = SYSDATE,
491 object_version_number = object_version_number + 1
492 where party_id = p_from_fk_id and cust_account_id is null
493 and plan_id not in ( select plan_id
494 from csc_cust_plans
495 where party_id = p_to_fk_id );
496
497 l_count := l_count+sql%rowcount;
498
499 g_mesg := 'Number of CSC_CUST_PLANS records transferred = ' || to_char(l_count) ;
500 fnd_file.put_line(fnd_file.log, g_mesg);
501
502 --arp_message.set_line('Number of CSC_CUST_PLANS records transferred = ' ||
503 --to_char(sql%rowcount) );
504
505
506 if ( ( l_count > 0 ) AND (audit_count > 0) ) then
507 g_mesg := 'Number of CSC_CUST_PLANS_AUDIT records inserted coresponding to the '
508 || 'CSC_CUST_PLANS records transferred = ' || to_char(audit_count) ;
509 fnd_file.put_line(fnd_file.log, g_mesg);
510 end if;
511
512 -- if ( l_count > 0 ) then
513 -- insert into csc_cust_plans_audit (
514 -- plan_audit_id, plan_id, party_id,
515 -- plan_status_code, request_id, creation_date,
516 -- created_by, last_update_date, last_updated_by,
517 -- last_update_login, program_application_id,
518 -- program_id, program_update_date, object_version_number )
519 -- select
520 -- csc_cust_plans_audit_s.nextval, plan_id, p_to_fk_id,
521 -- G_TRANSFER_PLAN, G_REQUEST_ID, SYSDATE,
522 -- G_USER_ID, SYSDATE, G_USER_ID,
523 -- G_LOGIN_ID, ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
524 -- ARP_STANDARD.PROFILE.PROGRAM_ID, SYSDATE, 1
525 -- from csc_cust_plans
526 -- where party_id = p_from_fk_id
527 -- and plan_id not in ( select plan_id
528 -- from csc_cust_plans
529 -- where party_id = p_to_fk_id );
530
531 -- g_mesg := 'Number of CSC_CUST_PLANS_AUDIT records inserted coresponding to the '
532 -- || 'CSC_CUST_PLANS records transferred = ' || to_char(sql%rowcount) ;
533 -- fnd_file.put_line(fnd_file.log, g_mesg);
534
535 --arp_message.set_line('Number of CSC_CUST_PLANS_AUDIT records inserted ' ||
536 --'coresponding to the CSC_CUST_PLANS records ' ||
537 --'transferred = ' || to_char(sql%rowcount) );
538
539 -- end if;
540
541 -- Perform merge if duplicate plans exist between the TO and FROM
542 -- parties
543 update csc_cust_plans
544 set plan_status_code = G_MERGE_PLAN,
545 end_date_active = SYSDATE,
546 request_id = G_REQUEST_ID,
547 last_update_date = SYSDATE,
548 last_updated_by = G_USER_ID,
549 last_update_login = G_LOGIN_ID,
550 program_application_id = ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
551 program_id = ARP_STANDARD.PROFILE.PROGRAM_ID,
552 program_update_date = SYSDATE,
553 object_version_number = object_version_number + 1
554 where party_id = p_from_fk_id
555 and plan_id in ( select plan_id
556 from csc_cust_plans
557 where party_id = p_to_fk_id );
558
559 l_count := sql%rowcount;
560
561 g_mesg := 'Number of CSC_CUST_PLANS records merged = ' || to_char(l_count) ;
562 fnd_file.put_line(fnd_file.log, g_mesg);
563
564 --arp_message.set_line('Number of CSC_CUST_PLANS records merged = ' ||
565 --to_char(sql%rowcount) );
566
567 -- Bug# 2919377, insert a record for FROM party with MERGE flag
568 if ( sql%rowcount > 0 ) then
569 insert into csc_cust_plans_audit (
570 plan_audit_id, plan_id, party_id,
571 plan_status_code, request_id, creation_date,
572 created_by, last_update_date, last_updated_by,
573 last_update_login, program_application_id,
574 program_id, program_update_date, object_version_number )
575 select
576 csc_cust_plans_audit_s.nextval, plan_id, p_from_fk_id,
577 G_MERGE_PLAN, G_REQUEST_ID, SYSDATE,
578 G_USER_ID, SYSDATE, G_USER_ID,
579 G_LOGIN_ID, ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
580 ARP_STANDARD.PROFILE.PROGRAM_ID, SYSDATE, 1
581 from csc_cust_plans
582 where party_id = p_from_fk_id
583 and plan_id in ( select plan_id
584 from csc_cust_plans
585 where party_id = p_to_fk_id );
586
587 g_mesg := 'Number of CSC_CUST_PLANS_AUDIT records inserted coresponding to the '
588 || 'CSC_CUST_PLANS records merged = ' || to_char(sql%rowcount) ;
589 fnd_file.put_line(fnd_file.log, g_mesg);
590 --arp_message.set_line('Number of CSC_CUST_PLANS_AUDIT records inserted ' ||
591 --'coresponding to the CSC_CUST_PLANS records ' ||
592 --'merged = ' || to_char(sql%rowcount) );
593 end if;
594 end if;
595
596 EXCEPTION
597 when RESOURCE_BUSY then
598 -- x_return_status := CSC_CORE_UTILS_PVT.G_RET_STS_ERROR;
599 g_mesg := substr(g_proc_name || '.' || l_api_name || '; Could not obtain lock for '
600 || 'records in table CSC_CUST_PLANS for party_id = ' || p_from_fk_id
601 || sqlerrm, 1, 1000);
602 fnd_file.put_line(fnd_file.log, g_mesg);
603
604 --arp_message.set_line(g_proc_name || '.' || l_api_name ||
605 --'; Could not obtain lock for records in table ' ||
606 --'CSC_CUST_PLANS for party_id = ' || p_from_fk_id );
607 raise;
608
609 when OTHERS then
610 --x_return_status := CSC_CORE_UTILS_PVT.G_RET_STS_UNEXP_ERROR;
611 g_mesg := substr(g_proc_name || '.' || l_api_name || ': ' || sqlerrm,1,1000);
612 fnd_file.put_line(fnd_file.log, g_mesg);
613
614 --arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
615 raise;
616
617 END CSC_CUST_PLANS_MERGE;
618
619
620 PROCEDURE CSC_CUSTOMIZED_PLANS_MERGE (
621 p_entity_name IN VARCHAR2,
622 p_from_id IN NUMBER,
623 x_to_id OUT NOCOPY NUMBER,
624 p_from_fk_id IN NUMBER,
625 p_to_fk_id IN NUMBER,
626 p_parent_entity_name IN VARCHAR2,
627 p_batch_id IN NUMBER,
628 p_batch_party_id IN NUMBER,
629 x_return_status OUT NOCOPY VARCHAR2)
630 IS
631
632 cursor c1 is
633 select 1
634 from csc_customized_plans
635 where party_id = p_from_fk_id
636 for update nowait;
637
638 G_PROC_NAME CONSTANT VARCHAR2(30) := 'CSC_PARTY_MERGE_PKG';
639 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cscvmpts.pls';
640 G_USER_ID CONSTANT NUMBER(15) := FND_GLOBAL.USER_ID;
641 G_LOGIN_ID CONSTANT NUMBER(15) := FND_GLOBAL.CONC_LOGIN_ID;
642
643 l_api_name VARCHAR2(30) := 'CSC_CUSTOMIZED_PLANS_MERGE';
644 l_count NUMBER(10) := 0;
645
646 RESOURCE_BUSY EXCEPTION;
647 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
648
649 g_mesg VARCHAR2(1000) := '';
650
651 BEGIN
652 --arp_message.set_line('CSC_PARTY_MERGE_PKG.CSC_CUSTOMIZED_PLANS_MERGE()+');
653 g_mesg := 'CSC_PARTY_MERGE_PKG.CSC_CUSTOMIZED_PLANS_MERGE';
654 fnd_file.put_line(fnd_file.log, g_mesg);
655
656 x_return_status := CSC_CORE_UTILS_PVT.G_RET_STS_SUCCESS;
657
658 if (g_merge_reason_code is null) then
659 get_hz_merge_batch(
660 p_batch_id => p_batch_id);
661 end if;
662
663 if G_MERGE_REASON_CODE = 'DUPLICATE' then
664 -- if reason code is duplicate then allow the party merge to happen without
665 -- any validations.
666 null;
667 else
668 -- if there are any validations to be done, include it in this section
669 null;
670 end if;
671
672 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
673 -- needs to be done. Set Merged To Id as same as Merged From Id and return
674
675 if p_from_fk_id = p_to_fk_id then
676 x_to_id := p_from_id;
677 g_mesg := 'To and From Parties are the same. Merge not required.';
678 fnd_file.put_line(fnd_file.log, g_mesg);
679 return;
680 end if;
681
682 -- If the parent has changed(id. Parent is getting merged) then transfer the
683 -- dependent record to the new parent. Before transferring check if a similar
684 -- dependent record exists on the new parent. If a duplicate exists then do
685 -- not transfer and return the id of the duplicate record as the Merged To Id
686
687 if p_from_fk_id <> p_to_fk_id then
688 -- obtain lock on records to be updated.
689 open c1;
690 close c1;
691
692 -- NOTE : If update performance is bad...then consider acheiving the same
693 -- logic thru the use of cursors..updating records individually.
694
695 -- Perform transfer if duplicate plans do not exist between the TO and FROM
696 -- parties
697
698 update csc_customized_plans
699 set party_id = p_to_fk_id,
700 request_id = G_REQUEST_ID,
701 program_application_id = ARP_STANDARD.PROFILE.PROGRAM_APPLICATION_ID,
702 program_id = ARP_STANDARD.PROFILE.PROGRAM_ID,
703 program_update_date = SYSDATE,
704 plan_status_code = G_TRANSFER_PLAN
705 where party_id = p_from_fk_id
706 and plan_id not in ( select plan_id
707 from csc_customized_plans
708 where party_id = p_to_fk_id );
709
710 l_count := sql%rowcount;
711
712 g_mesg := 'Number of CSC_CUSTOMIZED_PLANS records transferred = ' || to_char(l_count) ;
713 fnd_file.put_line(fnd_file.log, g_mesg);
714
715 --arp_message.set_line('Number of CSC_CUSTOMIZED_PLANS records transferred = ' ||
716 --to_char(sql%rowcount) );
717
718 -- Delete records if duplicate customized plans exist between the TO
719 -- and FROM parties.
720 -- The delete operation is being performed temperorily until some additional
721 -- columns are added to the CSC_CUSTOMIZED_PLANS table to denote the merge
722 -- or transfer operation.
723
724 delete from csc_customized_plans
725 where party_id = p_from_fk_id
726 and plan_id in ( select plan_id
727 from csc_customized_plans
728 where party_id = p_to_fk_id );
729
730 l_count := sql%rowcount;
731
732 g_mesg := 'Number of CSC_CUSTOMIZED_PLANS records deleted = ' || to_char(l_count);
733 fnd_file.put_line(fnd_file.log, g_mesg);
734
735 --arp_message.set_line('Number of CSC_CUSTOMIZED_PLANS records deleted = ' ||
736 --to_char(sql%rowcount) );
737
738 end if;
739
740 EXCEPTION
741 when RESOURCE_BUSY then
742 -- x_return_status := CSC_CORE_UTILS_PVT.G_RET_STS_ERROR;
743 g_mesg := g_proc_name || '.' || l_api_name || '; Could not obtain lock for '
744 || 'records in table CSC_CUSTOMIZED_PLANS for party_id = '
745 || p_from_fk_id;
746 fnd_file.put_line(fnd_file.log, g_mesg);
747
748 --arp_message.set_line(g_proc_name || '.' || l_api_name ||
749 --'; Could not obtain lock for records in table ' ||
750 --'CSC_CUSTOMIZED_PLANS for party_id = ' || p_from_fk_id );
751 raise;
752
753 when OTHERS then
754 -- x_return_status := CSC_CORE_UTILS_PVT.G_RET_STS_UNEXP_ERROR;
755 g_mesg := substr( g_proc_name || '.' || l_api_name || ': ' || sqlerrm, 1, 1000 );
756 fnd_file.put_line(fnd_file.log, g_mesg);
757 -- arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
758 raise;
759
760 END CSC_CUSTOMIZED_PLANS_MERGE;
761
762
763 END CSC_PARTY_MERGE_PKG;
[Home] [Help]