[Home] [Help]
PACKAGE BODY: APPS.CS_PARTYMERGE_PKG
Source
1 PACKAGE BODY CS_PARTYMERGE_PKG AS
2 /* $Header: cssrpmnb.pls 120.2.12010000.2 2008/09/18 06:27:28 mmaiya ship $ */
3
4 -- Declare private package level variables
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_PARTYMERGE_PKG';
7 G_USER_ID CONSTANT NUMBER(15) := FND_GLOBAL.USER_ID;
8 G_LOGIN_ID CONSTANT NUMBER(15) := FND_GLOBAL.LOGIN_ID;
9 G_PROC_NAME CONSTANT VARCHAR2(30) := 'UPDATE_CS_DATA';
10 dbg_msg VARCHAR2(4000) ;
11
12 TYPE ROWID_TBL IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
13 TYPE NUMBER_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
14 TYPE VARCHAR2_30_TBL IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
15
16 PROCEDURE log(
17 message IN VARCHAR2,
18 newline IN BOOLEAN DEFAULT TRUE) IS
19 BEGIN
20
21 IF message = 'NEWLINE' THEN
22 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
23 ELSIF (newline) THEN
24 FND_FILE.put_line(fnd_file.log,message);
25 ELSE
26 FND_FILE.put(fnd_file.log,message);
27 END IF;
28 END log;
29
30 PROCEDURE UPDATE_CS_DATA
31 ( p_batch_id IN NUMBER,
32 p_request_id IN NUMBER,
33 x_return_status OUT NOCOPY VARCHAR2)
34 IS
35
36 -- Declare all the nested tables those will be used in the SR and SR charges transactions merge.
37
38 l_rowid_tbl ROWID_TBL;
39 l_incident_id_tbl NUMBER_TBL;
40 l_estimate_detail_id_tbl NUMBER_TBL;
41 l_customer_id_tbl NUMBER_TBL;
42 l_from_party_id_tbl NUMBER_TBL;
43 l_to_party_id_tbl NUMBER_TBL;
44 l_bill_to_party_id_tbl NUMBER_TBL;
45 l_from_bill_to_party_id_tbl NUMBER_TBL;
46 l_to_bill_to_party_id_tbl NUMBER_TBL;
47 l_ship_to_party_id_tbl NUMBER_TBL;
48 l_from_ship_to_party_id_tbl NUMBER_TBL;
49 l_to_ship_to_party_id_tbl NUMBER_TBL;
50 l_bill_to_contact_id_tbl NUMBER_TBL;
51 l_from_bill_to_contact_id_tbl NUMBER_TBL;
52 l_to_bill_to_contact_id_tbl NUMBER_TBL;
53 l_ship_to_contact_id_tbl NUMBER_TBL;
54 l_from_ship_to_contact_id_tbl NUMBER_TBL;
55 l_to_ship_to_contact_id_tbl NUMBER_TBL;
56 l_bill_to_site_id_tbl NUMBER_TBL;
57 l_from_bill_to_site_id_tbl NUMBER_TBL;
58 l_to_bill_to_site_id_tbl NUMBER_TBL;
59 l_ship_to_site_id_tbl NUMBER_TBL;
60 l_from_ship_to_site_id_tbl NUMBER_TBL;
61 l_to_ship_to_site_id_tbl NUMBER_TBL;
62 l_site_id_tbl NUMBER_TBL;
63 l_from_site_id_tbl NUMBER_TBL;
64 l_to_site_id_tbl NUMBER_TBL;
65 l_customer_site_id_tbl NUMBER_TBL;
66 l_from_customer_site_id_tbl NUMBER_TBL;
67 l_to_customer_site_id_tbl NUMBER_TBL;
68 l_install_site_id_tbl NUMBER_TBL;
69 l_from_install_site_id_tbl NUMBER_TBL;
70 l_to_install_site_id_tbl NUMBER_TBL;
71 l_install_site_use_id_tbl NUMBER_TBL;
72 l_from_install_site_use_id_tbl NUMBER_TBL;
73 l_to_install_site_use_id_tbl NUMBER_TBL;
74 l_incident_location_id_tbl NUMBER_TBL;
75 l_incident_location_type_tbl VARCHAR2_30_TBL;
76 l_from_inc_loc_id_tbl NUMBER_TBL;
77 l_to_incident_location_id_tbl NUMBER_TBL;
78 l_ship_to_site_use_id_tbl NUMBER_TBL;
79 l_from_ship_to_site_use_id_tbl NUMBER_TBL;
80 l_to_ship_to_site_use_id_tbl NUMBER_TBL;
81 l_bill_to_site_use_id_tbl NUMBER_TBL;
82 l_from_bill_to_site_use_id_tbl NUMBER_TBL;
83 l_to_bill_to_site_use_id_tbl NUMBER_TBL;
84 l_customer_phone_id_tbl NUMBER_TBL;
85 l_from_phone_id_tbl NUMBER_TBL;
86 l_to_phone_id_tbl NUMBER_TBL;
87 l_customer_email_id_tbl NUMBER_TBL;
88 l_from_email_id_tbl NUMBER_TBL;
89 l_to_email_id_tbl NUMBER_TBL;
90 l_from_parent_id_tbl NUMBER_TBL;
91 l_to_parent_id_tbl NUMBER_TBL;
92 l_last_update_program_code_tbl VARCHAR2_30_TBL;
93 l_batch_party_id_tbl NUMBER_TBL;
94 l_sr_contact_point_id_tbl NUMBER_TBL;
95 l_extension_id_tbl NUMBER_TBL;
96
97 -- Other local variables
98
99 l_audit_vals_rec CS_ServiceRequest_PVT.SR_AUDIT_REC_TYPE;
100 l_ext_attrs_tbl_new CS_SR_EXTATTRIBUTES_PVT.Ext_Attr_Audit_Tbl_Type;
101 l_ext_attrs_tbl_old CS_SR_EXTATTRIBUTES_PVT.Ext_Attr_Audit_Tbl_Type;
102 l_audit_id NUMBER;
103 l_last_fetch BOOLEAN := FALSE ;
104 l_return_status VARCHAR2(3);
105 l_to_id NUMBER;
106 l_count NUMBER;
107 x_msg_count NUMBER(15);
108 x_msg_data VARCHAR2(2000);
109 RESOURCE_BUSY EXCEPTION;
110 --PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
111
112 -- Cursor to get the impacted service requests
113
114 CURSOR C_Get_ServiceRequests IS
115 SELECT /*+ PARALLEL(i) */
116 i.rowid,
117 i.incident_id ,
118 i.customer_id ,
119 pc.from_entity_id from_party_id ,
120 pc.to_entity_id to_party_id ,
121 i.bill_to_party_id,
122 pbp.from_entity_id from_bill_to_party_id ,
123 pbp.to_entity_id to_bill_to_party_id,
124 i.ship_to_party_id,
125 psp.from_entity_id from_ship_to_party_id ,
126 psp.to_entity_id to_ship_to_party_id,
127 i.bill_to_contact_id ,
128 pbc.from_entity_id from_bill_to_contact_id ,
129 pbc.to_entity_id to_bill_to_contact_id ,
130 i.ship_to_contact_id ,
131 psc.from_entity_id from_ship_to_contact_id ,
132 psc.to_entity_id to_ship_to_contact_id ,
133 i.bill_to_site_id,
134 pbs.from_entity_id from_bill_to_site_id,
135 pbs.to_entity_id to_bill_to_site_id,
136 i.ship_to_site_id,
137 pss.from_entity_id from_ship_to_site_id,
138 pss.to_entity_id to_ship_to_site_id,
139 i.site_id ,
140 ps.from_entity_id from_site_id,
141 ps.to_entity_id to_site_id,
142 i.customer_site_id,
143 pcs.from_entity_id from_customer_site_id,
144 pcs.to_entity_id to_customer_site_id,
145 i.install_site_id,
146 pis.from_entity_id from_install_site_id,
147 pis.to_entity_id to_install_site_id,
148 i.install_site_use_id,
149 pisu.from_entity_id from_install_site_use_id,
150 pisu.to_entity_id to_install_site_use_id,
151 i.incident_location_id,
152 i.incident_location_type,
153 pils.from_entity_id from_incident_location_id,
154 pils.to_entity_id to_incident_location_id,
155 i.ship_to_site_use_id,
156 psu.from_entity_id from_ship_to_site_use_id,
157 psu.to_entity_id to_ship_to_site_use_id,
158 i.bill_to_site_use_id,
159 pbu.from_entity_id from_bill_to_site_use_id,
160 pbu.to_entity_id to_bill_to_site_use_id,
161 i.customer_phone_id,
162 pch.from_entity_id from_phone_id,
163 pch.to_entity_id to_phone_id ,
164 i.customer_email_id,
165 pce.from_entity_id from_email_id,
166 pce.to_entity_id to_email_id ,
167 i.last_update_program_code
168 FROM cs_incidents_all_B i,
169 hz_merge_party_log pc,
170 hz_merge_party_log pbp,
171 hz_merge_party_log psp,
172 hz_merge_party_log pbc,
173 hz_merge_party_log psc,
174 hz_merge_party_log pbs,
175 hz_merge_party_log pss,
176 hz_merge_party_log ps,
177 hz_merge_party_log pcs,
178 hz_merge_party_log pis,
179 hz_merge_party_log pisu,
180 hz_merge_party_log pils,
181 hz_merge_party_log psu,
182 hz_merge_party_log pbu,
183 hz_merge_party_log pch,
184 hz_merge_party_log pce
185 WHERE pc.request_id(+) = p_request_id
186 AND pc.merge_dict_id(+) = 1 -- Entity = HZ Party
187 AND pc.from_entity_id(+) = i.customer_id
188 AND pc.operation_type(+) = 'Merge'
189 AND pbp.merge_dict_id(+) = 1 -- Entity = HZ Party
190 AND pbp.request_id(+) = p_request_id
191 AND pbp.from_entity_id(+) = i.bill_to_party_id
192 AND pbp.operation_type(+) = 'Merge'
193 AND psp.merge_dict_id(+) = 1 -- Entity = HZ Party
194 AND psp.request_id(+) = p_request_id
195 AND psp.from_entity_id(+) = i.ship_to_party_id
196 AND psp.operation_type(+) = 'Merge'
197 AND pbc.merge_dict_id(+) = 1 -- Entity = HZ Party
198 AND pbc.request_id(+) = p_request_id
199 AND pbc.from_entity_id(+) = i.bill_to_contact_id
200 AND pbc.operation_type(+) = 'Merge'
201 AND psc.merge_dict_id(+) = 1 -- Entity = HZ Party
202 AND psc.request_id(+) = p_request_id
203 AND psc.from_entity_id(+) = i.ship_to_contact_id
204 AND psc.operation_type(+) = 'Merge'
205 AND pbs.merge_dict_id(+) = 3 -- Entity = HZ Party site
206 AND pbs.request_id(+) = p_request_id
207 AND pbs.from_entity_id(+) = i.bill_to_site_id
208 AND pbs.operation_type(+) = 'Merge'
209 AND pss.merge_dict_id(+) = 3 -- Entity = HZ Party site
210 AND pss.request_id(+) = p_request_id
211 AND pss.from_entity_id(+) = i.ship_to_site_id
212 AND pss.operation_type(+) = 'Merge'
213 AND ps.merge_dict_id(+) = 3 -- Entity = HZ Party site
214 AND ps.request_id(+) = p_request_id
215 AND ps.from_entity_id(+) = i.site_id
216 AND ps.operation_type(+) = 'Merge'
217 AND pcs.merge_dict_id(+) = 3 -- Entity = HZ Party site
218 AND pcs.request_id(+) = p_request_id
219 AND pcs.from_entity_id(+) = i.customer_site_id
220 AND pcs.operation_type(+) = 'Merge'
221 AND pis.merge_dict_id(+) = 3 -- Entity = HZ Party site
222 AND pis.request_id(+) = p_request_id
223 AND pis.from_entity_id(+) = i.install_site_id
224 AND pis.operation_type(+) = 'Merge'
225 AND pisu.merge_dict_id(+) = 3 -- Entity = HZ Party site
226 AND pisu.request_id(+) = p_request_id
227 AND pisu.from_entity_id(+) = i.install_site_use_id
228 AND pisu.operation_type(+) = 'Merge'
229 AND pils.merge_dict_id(+) = 3 -- Entity = HZ Party site
230 AND pils.request_id(+) = p_request_id
231 AND pils.from_entity_id(+) = i.incident_location_id
232 AND pils.operation_type(+) = 'Merge'
233 AND psu.merge_dict_id(+) = 19 -- Entity = HZ Party Site Use
234 AND psu.request_id(+) = p_request_id
235 AND psu.from_entity_id(+) = i.ship_to_site_use_id
236 AND psu.operation_type(+) = 'Merge'
237 AND pbu.merge_dict_id(+) = 19 -- Entity = HZ Party Site Use
238 AND pbu.request_id(+) = p_request_id
239 AND pbu.from_entity_id(+) = i.bill_to_site_use_id
240 AND pbu.operation_type(+) = 'Merge'
241 AND pch.merge_dict_id(+) = 4 -- Entity = HZ Contact Point
242 AND pch.request_id(+) = p_request_id
243 AND pch.from_entity_id(+) = i.customer_phone_id
244 AND pch.operation_type(+) = 'Merge'
245 AND pce.merge_dict_id(+) = 4 -- Entity = HZ Contact Point
246 AND pce.request_id(+) = p_request_id
247 AND pce.from_entity_id(+) = i.customer_email_id
248 AND pce.operation_type(+) = 'Merge'
249 AND (pc.from_entity_id is not null OR
250 pbp.from_entity_id is not null OR
251 psp.from_entity_id is not null OR
252 pbc.from_entity_id is not null OR
253 psc.from_entity_id is not null OR
254 pbs.from_entity_id is not null OR
255 pss.from_entity_id is not null OR
256 pcs.from_entity_id is not null OR
257 ps.from_entity_id is not null OR
258 pis.from_entity_id is not null OR
259 pisu.from_entity_id is not null OR
260 pils.from_entity_id is not null OR
261 psu.from_entity_id is not null OR
262 pbu.from_entity_id is not null OR
263 pch.from_entity_id is not null OR
264 pce.from_entity_id is not null
265 ) ;
266
267 -- Cursor to get the impacted SR charge transactions
268
269 CURSOR C_Get_Estimate_details IS
270 SELECT /*+ PARALLEL(c) */
271 c.rowid,
272 c.estimate_detail_id,
273 c.bill_to_party_id,
274 pbp.from_entity_id from_bill_to_party_id ,
275 pbp.to_entity_id to_bill_to_party_id,
276 c.ship_to_party_id,
277 psp.from_entity_id from_ship_to_party_id ,
278 psp.to_entity_id to_ship_to_party_id,
279 c.bill_to_contact_id ,
280 pbc.from_entity_id from_bill_to_contact_id ,
281 pbc.to_entity_id to_bill_to_contact_id ,
282 c.ship_to_contact_id ,
283 psc.from_entity_id from_ship_to_contact_id ,
284 psc.to_entity_id to_ship_to_contact_id ,
285 c.invoice_to_org_id,
286 pbs.from_entity_id from_bill_to_site_id,
287 pbs.to_entity_id to_bill_to_site_id,
288 c.ship_to_org_id,
289 pss.from_entity_id from_ship_to_site_id,
290 pss.to_entity_id to_ship_to_site_id
291 FROM cs_estimate_details c,
292 hz_merge_party_log pbp,
293 hz_merge_party_log psp,
294 hz_merge_party_log pbc,
295 hz_merge_party_log psc,
296 hz_merge_party_log pbs,
297 hz_merge_party_log pss
298 WHERE pbp.merge_dict_id(+) = 1 -- Entity = HZ Party
299 AND pbp.request_id(+) = p_request_id
300 AND pbp.from_entity_id(+) = c.bill_to_party_id
301 AND pbp.operation_type(+) = 'Merge'
302 AND psp.merge_dict_id(+) = 1 -- Entity = HZ Party
303 AND psp.request_id(+) = p_request_id
304 AND psp.from_entity_id(+) = c.ship_to_party_id
305 AND psp.operation_type(+) = 'Merge'
306 AND pbc.merge_dict_id(+) = 1 -- Entity = HZ Party
307 AND pbc.request_id(+) = p_request_id
308 AND pbc.from_entity_id(+) = c.bill_to_contact_id
309 AND pbc.operation_type(+) = 'Merge'
310 AND psc.merge_dict_id(+) = 1 -- Entity = HZ Party
311 AND psc.request_id(+) = p_request_id
312 AND psc.from_entity_id(+) = c.ship_to_contact_id
313 AND psc.operation_type(+) = 'Merge'
314 AND pbs.merge_dict_id(+) = 3 -- Entity = HZ Party Site
315 AND pbs.request_id(+) = p_request_id
316 AND pbs.from_entity_id(+) = c.invoice_to_org_id
317 --AND pbs.operation_type(+) = 'Merge' --bug 7310180
318 AND pss.merge_dict_id(+) = 3 -- Entity = HZ Party Site
319 AND pss.request_id(+) = p_request_id
320 AND pss.from_entity_id(+) = c.ship_to_org_id
321 --AND pss.operation_type(+) = 'Merge' --bug 7310180
322 AND (pbp.from_entity_id is not null OR
323 psp.from_entity_id is not null OR
324 pbc.from_entity_id is not null OR
325 psc.from_entity_id is not null OR
326 pbs.from_entity_id is not null OR
327 pss.from_entity_id is not null );
328
329 -- Cursor to get the merged parties from the TCA log tables.
330
331 CURSOR c_Get_Merged_Parties IS
332 SELECT batch_party_id ,
333 from_party_id ,
334 to_party_id
335 FROM hz_merge_parties
336 WHERE merge_reason_code <> 'SAME_PARTY_MERGE'
337 AND batch_id = p_batch_id;
338
339 -- Cursor to get impacted Contact point transactions due to party merge using TCA log table and CS_HZ_SR_Contact_Points table.
340
341 CURSOR C_get_contact_point_txns1 IS
342 SELECT p.from_entity_id ,
343 p.to_entity_id ,
344 p.batch_party_id,
345 cc.sr_contact_point_id
346 FROM hz_merge_party_log p,
347 cs_hz_sr_contact_points cc
348 WHERE p.merge_dict_id = 4
349 AND p.operation_type = 'Merge'
350 AND p.request_id = p_request_id
351 AND p.from_entity_id = cc.contact_point_id
352 AND cc.contact_type <> 'EMPLOYEE';
353
354 -- Cursor to get impacted Contact points data due to contact point merge using TCA log table and cs_hz_sr_contact_points table.
355
356 CURSOR c_Get_contact_point_txns2 IS
357 SELECT p.batch_party_id,
358 p.from_party_id ,
359 p.to_party_id ,
360 cc.sr_contact_point_id
361 FROM hz_merge_parties p,
362 cs_hz_sr_contact_points cc
363 WHERE p.merge_type <> 'SAME_PARTY_MERGE'
364 AND p.batch_id = p_batch_id
365 AND p.from_party_id = cc.party_id
366 AND cc.contact_type <> 'EMPLOYEE';
367
368 -- Cursor to get impacted ext attr records related to party role
369
370 CURSOR c_get_party_ext_attr_rec IS
371 SELECT ex.rowid,
372 ex.extension_id,
373 ex.party_id ,
374 p.from_party_id ,
375 p.to_party_id
376 FROM hz_merge_parties p,
377 cs_sr_contacts_ext ex
378 WHERE ex.party_id = p.from_party_id
379 AND p.batch_id = p_batch_id
380 AND p.merge_type <> 'SAME_PARTY_MERGE';
381
382 BEGIN
383 -- Call HZ Routine to populate the party merge log table
384
385
386 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
387 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
388 dbg_msg := ('In CS_PartyMerge_PKG.Update_CS_Data');
389 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
390 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
391 END IF;
392 END IF;
393 END IF;
394
395 arp_message.set_line('CS_PARTYMERGE_PKG.UPDATE_CS_DATA()+');
396
397 x_return_status := FND_API.G_RET_STS_SUCCESS;
398
399 -- Update service request transaction data and create a SR audit record for each service request that is updated.
400 ----------------------------------------------------------------------------------------------------------------
401
402 BEGIN
403 -- get all the impacted service request in a batch of 1000 service requests.
404
405
406 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
407 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
408 dbg_msg := ('Merging Service Request transactions');
409 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
410 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
411 END IF;
412 END IF;
413 END IF;
414 -- obtain lock on records to be updated.
415 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
416 arp_message.set_token('TABLE_NAME', 'CS_INCIDENTS_ALL_B', FALSE);
417
418 OPEN C_Get_ServiceRequests;
419 LOOP -- Loop for BULK SR processing in a batch of 1000
420
421 FETCH C_Get_ServiceRequests BULK COLLECT
422 INTO l_rowid_tbl,
423 l_incident_id_tbl ,
424 l_customer_id_tbl ,
425 l_from_party_id_tbl ,
426 l_to_party_id_tbl ,
427 l_bill_to_party_id_tbl,
428 l_from_bill_to_party_id_tbl ,
429 l_to_bill_to_party_id_tbl,
430 l_ship_to_party_id_tbl,
431
432 l_from_ship_to_party_id_tbl ,
433 l_to_ship_to_party_id_tbl,
434 l_bill_to_contact_id_tbl ,
435 l_from_bill_to_contact_id_tbl ,
436 l_to_bill_to_contact_id_tbl ,
437 l_ship_to_contact_id_tbl ,
438 l_from_ship_to_contact_id_tbl ,
439 l_to_ship_to_contact_id_tbl ,
440 l_bill_to_site_id_tbl,
441 l_from_bill_to_site_id_tbl,
442 l_to_bill_to_site_id_tbl,
443 l_ship_to_site_id_tbl,
444 l_from_ship_to_site_id_tbl,
445 l_to_ship_to_site_id_tbl,
446 l_site_id_tbl ,
447 l_from_site_id_tbl,
448 l_to_site_id_tbl,
449 l_customer_site_id_tbl,
450 l_from_customer_site_id_tbl,
451 l_to_customer_site_id_tbl,
452 l_install_site_id_tbl,
453 l_from_install_site_id_tbl,
454 l_to_install_site_id_tbl,
455 l_install_site_use_id_tbl,
456 l_from_install_site_use_id_tbl,
457 l_to_install_site_use_id_tbl,
458 l_incident_location_id_tbl,
459 l_incident_location_type_tbl,
460 l_from_inc_loc_id_tbl ,
461 l_to_incident_location_id_tbl,
462 l_ship_to_site_use_id_tbl,
463 l_from_ship_to_site_use_id_tbl,
464 l_to_ship_to_site_use_id_tbl,
465 l_bill_to_site_use_id_tbl,
466 l_from_bill_to_site_use_id_tbl,
467 l_to_bill_to_site_use_id_tbl,
468 l_customer_phone_id_tbl,
469 l_from_phone_id_tbl,
470 l_to_phone_id_tbl ,
471 l_customer_email_id_tbl,
472 l_from_email_id_tbl,
473 l_to_email_id_tbl ,
474 l_last_update_program_code_tbl
475 LIMIT 1000 ;
476
477 IF C_Get_ServiceRequests%NOTFOUND THEN
478 l_last_fetch := TRUE;
479 END IF;
480
481 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
482 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
483 dbg_msg := ('No of Service requests to be updated : '||C_Get_ServiceRequests%ROWCOUNT);
484 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
485 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
486 END IF;
487 END IF;
488 END IF;
489
490 IF l_rowid_tbl.count <> 0 AND NOT l_last_fetch THEN -- for last fetch
491 EXIT;
492 END IF;
493
494 -- update cs_incidents_all_b table
495
496 FORALL i IN 1..l_rowid_tbl.COUNT -- ..l_rowid_tbl.LAST
497
498 UPDATE cs_incidents_all_b i
499 SET i.customer_id = DECODE(i.customer_id,l_from_party_id_tbl(i),
500 l_to_party_id_tbl(i) , i.customer_id),
501 i.bill_to_party_id = DECODE(i.bill_to_party_id,l_from_bill_to_party_id_tbl(i),
502 l_to_bill_to_party_id_tbl(i),i.bill_to_party_id),
503 i.ship_to_party_id = DECODE(i.ship_to_party_id , l_from_ship_to_party_id_tbl(i) ,
504 l_to_ship_to_party_id_tbl(i) , i.ship_to_party_id),
505 i.bill_to_contact_id = DECODE(i.bill_to_contact_id , l_from_bill_to_contact_id_tbl(i) ,
506 l_to_bill_to_contact_id_tbl(i) , i.bill_to_contact_id),
507 i.ship_to_contact_id = DECODE(i.ship_to_contact_id , l_from_ship_to_contact_id_tbl(i) ,
508 l_to_ship_to_contact_id_tbl(i) , i.ship_to_contact_id),
509 i.bill_to_site_id = DECODE(i.bill_to_site_id , l_from_bill_to_site_id_tbl(i),
510 l_to_bill_to_site_id_tbl(i) , i.bill_to_site_id),
511 i.ship_to_site_id = DECODE(i.ship_to_site_id , l_from_ship_to_site_id_tbl(i),
512 l_to_ship_to_site_id_tbl(i) , i.ship_to_site_id),
513 i.site_id = DECODE(i.site_id , l_from_site_id_tbl(i) ,
514 l_to_site_id_tbl(i) , i.site_id),
515 i.customer_site_id = DECODE(i.customer_site_id , l_from_customer_site_id_tbl(i) ,
516 l_to_customer_site_id_tbl(i) , i.install_site_id),
517 i.install_site_id = DECODE(i.install_site_id , l_from_install_site_id_tbl(i) ,
518 l_to_install_site_id_tbl(i) , i.install_site_use_id ),
519 i.install_site_use_id = DECODE(i.install_site_use_id , l_from_install_site_use_id_tbl(i) ,
520 l_to_install_site_use_id_tbl(i) , i.install_site_use_id),
521 i.incident_location_id = DECODE(i.incident_location_type , 'HZ_PARTY_SITE',
522 DECODE (i.incident_location_id , l_from_inc_loc_id_tbl(i) ,
523 l_to_incident_location_id_tbl(i) , i.incident_location_id),
524
525
526 i.incident_location_id),
527 i.ship_to_site_use_id = DECODE(i.ship_to_site_use_id , l_from_ship_to_site_use_id_tbl(i) ,
528 l_to_ship_to_site_use_id_tbl(i) , i.ship_to_site_use_id),
529 i.bill_to_site_use_id = DECODE(i.bill_to_site_use_id , l_from_bill_to_site_use_id_tbl(i) ,
530 l_to_bill_to_site_use_id_tbl(i) , i.bill_to_site_use_id),
531 i.customer_phone_id = DECODE(i.customer_phone_id , l_from_phone_id_tbl(i) ,
532 l_to_phone_id_tbl(i) , i.customer_phone_id),
533 i.customer_email_id = DECODE(i.customer_email_id , l_from_email_id_tbl(i) ,
534 l_to_email_id_tbl(i) , i.customer_email_id),
535 object_version_number = object_version_number + 1,
536 incident_last_modified_date = sysdate ,
537 last_update_program_code = 'PARTY_MERGE',
538 last_update_date = SYSDATE,
539 last_updated_by = G_USER_ID,
540
541 last_update_login = G_LOGIN_ID
542 WHERE rowid = l_rowid_tbl(i);
543
544 l_count := sql%rowcount;
545
546 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
547 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
548 dbg_msg := ('No. of service requests updated : '||l_count);
549 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
550 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
551 END IF;
552 END IF;
553 END IF;
554
555 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
556 arp_message.set_token('NUM_ROWS', to_char(l_count) );
557
558 -- Create SR Audit record for each updated service request transaction
559
560 dbg_msg := ('Creating SR Audit record for each updated service request');
561
562 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
563 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
564 dbg_msg := ('Calling CS_PartyMerge_PKG.Update_CS_Data API ');
565 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
566 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
567 END IF;
568 END IF;
569 END IF;
570
571 FOR i IN 1..l_incident_id_tbl.COUNT
572 LOOP
573 -- Prepare SR audit record structure
574 CS_Servicerequest_UTIL.Prepare_Audit_Record
575 (p_api_version => 1,
576 p_request_id => l_incident_id_tbl(i),
577 x_return_status => l_return_status,
578 x_msg_count => x_msg_count,
579 x_msg_data => x_msg_data,
580 x_audit_vals_rec => l_audit_vals_rec);
581
582 IF l_return_status <> FND_API.G_RET_STS_ERROR Then
583
584 -- Populate the old values
585
586 IF l_audit_vals_rec.customer_id <> l_customer_id_tbl(i) THEN
587 l_audit_vals_rec.old_customer_id := l_customer_id_tbl(i);
588 END IF;
589
590 IF l_audit_vals_rec.bill_to_party_id <> l_bill_to_party_id_tbl(i) THEN
591 l_audit_vals_rec.old_bill_to_party_id := l_bill_to_party_id_tbl(i);
592 END IF;
593
594 IF l_audit_vals_rec.ship_to_party_id <> l_ship_to_party_id_tbl(i) THEN
595 l_audit_vals_rec.old_ship_to_party_id := l_ship_to_party_id_tbl(i);
596 END IF;
597
598 IF l_audit_vals_rec.bill_to_contact_id <> l_bill_to_contact_id_tbl(i) THEN
599 l_audit_vals_rec.old_bill_to_contact_id := l_bill_to_contact_id_tbl(i);
600 l_audit_vals_rec.change_bill_to_flag := 'Y';
601 END IF;
602
603 IF l_audit_vals_rec.ship_to_contact_id <> l_ship_to_contact_id_tbl(i) THEN
604 l_audit_vals_rec.old_ship_to_contact_id := l_ship_to_contact_id_tbl(i);
605 l_audit_vals_rec.change_ship_to_FLAG := 'Y';
606 END IF;
607
608 IF l_audit_vals_rec.bill_to_site_id <> l_bill_to_site_id_tbl(i) THEN
609 l_audit_vals_rec.old_bill_to_site_id := l_bill_to_site_id_tbl(i);
610 END IF;
611
612 IF l_audit_vals_rec.ship_to_site_id <> l_ship_to_site_id_tbl(i) THEN
613 l_audit_vals_rec.old_ship_to_site_id := l_ship_to_site_id_tbl(i);
614 END IF;
615
616 IF l_audit_vals_rec.site_id <> l_site_id_tbl(i) THEN
617 l_audit_vals_rec.old_site_id := l_site_id_tbl(i);
618 END IF;
619
620 IF l_audit_vals_rec.customer_site_id <> l_customer_site_id_tbl(i) THEN
621 l_audit_vals_rec.old_customer_site_id := l_customer_site_id_tbl(i);
622 END IF;
623
624 IF l_audit_vals_rec.install_site_id <> l_install_site_id_tbl(i) THEN
625 l_audit_vals_rec.old_install_site_id := l_install_site_id_tbl(i);
626 END IF;
627
628 IF l_audit_vals_rec.install_site_use_id <> l_install_site_use_id_tbl(i) THEN
629 l_audit_vals_rec.old_install_site_use_id := l_install_site_use_id_tbl(i);
630 END IF;
631
632 IF l_audit_vals_rec.bill_to_party_id <> l_bill_to_party_id_tbl(i) THEN
633 l_audit_vals_rec.old_bill_to_party_id := l_bill_to_party_id_tbl(i);
634 END IF;
635
636 IF l_audit_vals_rec.incident_location_type = 'HZ_PARTY_SITE' THEN
637 IF l_audit_vals_rec.incident_location_id <> l_incident_location_id_tbl(i) THEN
638 l_audit_vals_rec.old_incident_location_id := l_incident_location_id_tbl(i);
639 END IF ;
640 END IF;
641
642 IF l_audit_vals_rec.ship_to_site_use_id <> l_ship_to_site_use_id_tbl(i) THEN
643 l_audit_vals_rec.old_ship_to_site_use_id := l_ship_to_site_use_id_tbl(i);
644 END IF;
645
646 IF l_audit_vals_rec.customer_phone_id <> l_customer_phone_id_tbl(i) THEN
647 l_audit_vals_rec.old_customer_phone_id := l_customer_phone_id_tbl(i);
648 END IF;
649
650 IF l_audit_vals_rec.customer_email_id <> l_customer_email_id_tbl(i) THEN
651 l_audit_vals_rec.old_customer_email_id := l_customer_email_id_tbl(i);
652 END IF;
653 l_audit_vals_rec.old_last_update_program_code := l_last_update_program_code_tbl(i) ;
654 l_audit_vals_rec.last_update_program_code := 'PARTY_MERGE' ;
655 l_audit_vals_rec.updated_entity_code := 'SR_HEADER';
656 l_audit_vals_rec.updated_entity_id := l_incident_id_tbl(i);
657 l_audit_vals_rec.entity_activity_code := 'U' ;
658
659 END IF;
660
661 -- Call Create SR Audit API
662
663 CS_ServiceRequest_PVT.Create_Audit_Record
664 (p_api_version => 2.0,
665 x_return_status => l_return_status,
666 x_msg_count => x_msg_count,
667 x_msg_data => x_msg_data,
668 p_request_id => l_incident_id_tbl(i),
669 p_audit_id => NULL,
670 p_audit_vals_rec => l_audit_vals_rec,
671 p_user_id => G_USER_ID,
672 p_login_id => G_LOGIN_ID,
673 p_last_update_date => SYSDATE,
674 p_creation_date => SYSDATE,
675 p_comments => NULL,
676 x_audit_id => l_audit_id);
677
678 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
679 RAISE FND_API.G_EXC_ERROR;
680 END IF ;
681
682 END LOOP; -- End loop for SR auditing
683
684
685 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
686 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
687 dbg_msg := ('Created SR audit records for the SRs updated in a batch');
688 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
689 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
690 END IF;
691 END IF;
692 END IF;
693
694 IF l_last_fetch THEN
695 EXIT;
696 END IF ;
697
698 END LOOP; -- End lool for BULK SR processing in a batch of 1000
699 CLOSE C_Get_ServiceRequests;
700 EXCEPTION
701 WHEN FND_API.G_EXC_ERROR THEN
702 arp_message.set_line(g_pkg_name || '.' || g_proc_name || ': ' || sqlerrm);
703 x_return_status := FND_API.G_RET_STS_ERROR;
704 ROLLBACK;
705 RAISE;
706
707 WHEN resource_busy THEN
708 arp_message.set_line(g_pkg_name || '.' || g_proc_name || ': Could not obtain lock for records in table '||
709 'CS_INCIDENTS_ALL_B for columns referring HZ parties');
710 x_return_status := FND_API.G_RET_STS_ERROR;
711 ROLLBACK;
712 RAISE;
713
714 WHEN others THEN
715 arp_message.set_line(g_pkg_name || '.' || g_proc_name || ': ' || sqlerrm);
716 x_return_status := FND_API.G_RET_STS_ERROR;
717 ROLLBACK;
718 RAISE;
719 END ;
720
721
722
723 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
724 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
725 dbg_msg := ('Updating Service Request Transactions completed');
726 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
727 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
728 END IF;
729 END IF;
730 END IF;
731
732 -- Release the service request data in the memory
733
734 BEGIN
735
736 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
737 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
738 dbg_msg := ('Releasing memory');
739 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
740 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
741 END IF;
742 END IF;
743 END IF;
744
745 l_rowid_tbl.DELETE;
746 l_incident_id_tbl.DELETE;
747 l_estimate_detail_id_tbl.DELETE;
748 l_customer_id_tbl.DELETE;
749 l_from_party_id_tbl.DELETE;
750 l_to_party_id_tbl.DELETE;
751 l_bill_to_party_id_tbl.DELETE;
752 l_from_bill_to_party_id_tbl.DELETE;
753 l_to_bill_to_party_id_tbl.DELETE;
754 l_ship_to_party_id_tbl.DELETE;
755 l_from_ship_to_party_id_tbl.DELETE;
756 l_to_ship_to_party_id_tbl.DELETE;
757 l_bill_to_contact_id_tbl.DELETE;
758 l_from_bill_to_contact_id_tbl.DELETE;
759 l_to_bill_to_contact_id_tbl.DELETE;
760 l_ship_to_contact_id_tbl.DELETE;
761 l_from_ship_to_contact_id_tbl.DELETE;
762 l_to_ship_to_contact_id_tbl.DELETE;
763 l_bill_to_site_id_tbl.DELETE;
764 l_from_bill_to_site_id_tbl.DELETE;
765 l_to_bill_to_site_id_tbl.DELETE;
766 l_ship_to_site_id_tbl.DELETE;
767 l_from_ship_to_site_id_tbl.DELETE;
768 l_to_ship_to_site_id_tbl.DELETE;
769 l_site_id_tbl.DELETE;
770 l_from_site_id_tbl.DELETE;
771 l_to_site_id_tbl.DELETE;
772 l_customer_site_id_tbl.DELETE;
773 l_from_customer_site_id_tbl.DELETE;
774 l_to_customer_site_id_tbl.DELETE;
775 l_install_site_id_tbl.DELETE;
776 l_from_install_site_id_tbl.DELETE;
777 l_to_install_site_id_tbl.DELETE;
778 l_install_site_use_id_tbl.DELETE;
779 l_from_install_site_use_id_tbl.DELETE;
780 l_to_install_site_use_id_tbl.DELETE;
781 l_incident_location_id_tbl.DELETE;
782 l_incident_location_type_tbl.DELETE;
783 l_from_inc_loc_id_tbl.DELETE;
784 l_to_incident_location_id_tbl.DELETE;
785 l_ship_to_site_use_id_tbl.DELETE;
786 l_from_ship_to_site_use_id_tbl.DELETE;
787 l_to_ship_to_site_use_id_tbl.DELETE;
788 l_bill_to_site_use_id_tbl.DELETE;
789 l_from_bill_to_site_use_id_tbl.DELETE;
790 l_to_bill_to_site_use_id_tbl.DELETE;
791 l_customer_phone_id_tbl.DELETE;
792 l_from_phone_id_tbl.DELETE;
793 l_to_phone_id_tbl.DELETE;
794 l_customer_email_id_tbl.DELETE;
795 l_from_email_id_tbl.DELETE;
796 l_to_email_id_tbl.DELETE;
797 l_last_update_program_code_tbl.DELETE;
798 l_last_fetch := FALSE;
799 l_count := 0;
800
801
802 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
803 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
804 dbg_msg := ('Releasing memory completed');
805 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
806 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
807 END IF;
808 END IF;
809 END IF;
810 END;
811
812
813 -- Update SR charges transaction data
814 --------------------------------------------
815
816 BEGIN
817
818 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
819 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
820 dbg_msg := ('Updating SR Charges transactions');
821 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
822 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
823 END IF;
824 END IF;
825 END IF;
826
827 -- get all the impacted SR Charges transactions in a batch of 1000 .
828 -- obtain lock on records to be updated.
829 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
830 arp_message.set_token('TABLE_NAME', 'CS_ESTIMATE_DETAILS', FALSE);
831
832 OPEN C_Get_Estimate_details;
833 LOOP -- Loop for BULK SR charges processing in a batch of 1000
834 FETCH C_Get_Estimate_details BULK COLLECT
835 INTO l_rowid_tbl,
836 l_estimate_detail_id_tbl ,
837 l_bill_to_party_id_tbl,
838 l_from_bill_to_party_id_tbl ,
839 l_to_bill_to_party_id_tbl,
840 l_ship_to_party_id_tbl,
841 l_from_ship_to_party_id_tbl ,
842 l_to_ship_to_party_id_tbl,
843 l_bill_to_contact_id_tbl ,
844 l_from_bill_to_contact_id_tbl ,
845 l_to_bill_to_contact_id_tbl ,
846 l_ship_to_contact_id_tbl ,
847 l_from_ship_to_contact_id_tbl ,
848 l_to_ship_to_contact_id_tbl ,
849 l_bill_to_site_id_tbl,
850 l_from_bill_to_site_id_tbl,
851 l_to_bill_to_site_id_tbl,
852 l_ship_to_site_id_tbl,
853 l_from_ship_to_site_id_tbl,
854 l_to_ship_to_site_id_tbl
855 LIMIT 1000 ;
856
857 IF C_Get_Estimate_details%NOTFOUND THEN
858 l_last_fetch := TRUE;
859 END IF;
860
861 IF l_rowid_tbl.count <> 0 AND l_last_fetch = FALSE THEN -- for last fetch
862 EXIT;
863 END IF;
864
865 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
866 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
867 dbg_msg := ('No of SR Charge transactions to be updated in a batch : '||C_Get_Estimate_details%ROWCOUNT);
868 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
869 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
870 END IF;
871 END IF;
872 END IF;
873
874 -- update cs_estimate_details table
875
876 FORALL i IN 1..l_rowid_tbl.COUNT
877
878 UPDATE cs_estimate_details c
879 SET c.bill_to_party_id = DECODE(c.bill_to_party_id , l_from_bill_to_party_id_tbl(i) ,
880 l_to_bill_to_party_id_tbl(i) , c.bill_to_party_id),
881 c.ship_to_party_id = DECODE(c.ship_to_party_id , l_from_ship_to_party_id_tbl(i) ,
882 l_to_ship_to_party_id_tbl(i) , c.ship_to_party_id),
883 c.bill_to_contact_id = DECODE(c.bill_to_contact_id , l_from_bill_to_contact_id_tbl(i) ,
884 l_to_bill_to_contact_id_tbl(i) , c.bill_to_contact_id),
885 c.ship_to_contact_id = DECODE(c.ship_to_contact_id , l_from_ship_to_contact_id_tbl(i) ,
886 l_to_ship_to_contact_id_tbl(i) , c.ship_to_contact_id),
887 c.invoice_to_org_id = DECODE(c.invoice_to_org_id , l_from_bill_to_site_id_tbl(i),
888 l_to_bill_to_site_id_tbl(i) , c.invoice_to_org_id),
889 c.ship_to_org_id = DECODE(c.ship_to_org_id , l_from_ship_to_site_id_tbl(i),
890 l_to_ship_to_site_id_tbl(i) , c.ship_to_org_id),
891 object_version_number = object_version_number + 1,
892 last_update_date = SYSDATE,
893 last_updated_by = G_USER_ID,
894 last_update_login = G_LOGIN_ID
895 WHERE rowid = l_rowid_tbl(i);
896
897 l_count := sql%rowcount;
898
899 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
900 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
901 dbg_msg := ('No of SR Charge transactions updated : '||l_count);
902 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
903 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
904 END IF;
905 END IF;
906 END IF;
907
908 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
909 arp_message.set_token('NUM_ROWS', to_char(l_count) );
910
911 IF l_last_fetch THEN
912 EXIT;
913 END IF ;
914
915 END LOOP; -- End Loop for BULK SR charges processing in a batch of 1000
916
917 CLOSE C_Get_Estimate_details;
918
919 EXCEPTION
920 WHEN resource_busy THEN
921 arp_message.set_line(g_pkg_name || '.' || g_proc_name || ': Could not obtain lock for records in table '||
922 'CS_ESTIMATE_DETAILS for columns referring HZ entities');
923 x_return_status := FND_API.G_RET_STS_ERROR;
924 ROLLBACK;
925 RAISE;
926
927 WHEN others THEN
928 arp_message.set_line(g_pkg_name || '.' || g_proc_name || ': ' || sqlerrm);
929 x_return_status := FND_API.G_RET_STS_ERROR;
930
931 ROLLBACK;
932 RAISE;
933 END ;
934
935 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
936 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
937 dbg_msg := ('SR Charge transactions updated, Releasing memory');
938 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
939 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
940 END IF;
941 END IF;
942 END IF;
943
944 -- Release the service request data in the memory
945
946 BEGIN
947 l_rowid_tbl.DELETE;
948 l_estimate_detail_id_tbl.DELETE;
949 l_bill_to_party_id_tbl.DELETE;
950 l_from_bill_to_party_id_tbl.DELETE;
951 l_to_bill_to_party_id_tbl.DELETE;
952 l_ship_to_party_id_tbl.DELETE;
953 l_from_ship_to_party_id_tbl.DELETE;
954 l_to_ship_to_party_id_tbl.DELETE;
955 l_bill_to_contact_id_tbl.DELETE;
956 l_from_bill_to_contact_id_tbl.DELETE;
957 l_to_bill_to_contact_id_tbl.DELETE;
958 l_ship_to_contact_id_tbl.DELETE;
959 l_from_ship_to_contact_id_tbl.DELETE;
960 l_to_ship_to_contact_id_tbl.DELETE;
961 l_from_bill_to_site_id_tbl.DELETE;
962 l_to_bill_to_site_id_tbl.DELETE;
963 l_from_ship_to_site_id_tbl.DELETE;
964 l_to_ship_to_site_id_tbl.DELETE;
965 END;
966
967
968 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
969 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
970 dbg_msg := ('Releasing memory done');
971 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
972 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
973 END IF;
974 END IF;
975 END IF;
976
977 -- Update SR contact points transaction data
978 --------------------------------------------
979
980 BEGIN
981
982 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
983 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
984 dbg_msg := ('Updating SR Contact point transactions ');
985 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
986 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
987 END IF;
988 END IF;
989 END IF;
990
991 -- Merging party ids in contact points table.
992 -- get all the merged parties transactions in a batch of 1000 .
993
994 OPEN c_Get_contact_point_txns2;
995 LOOP -- Loop for BULK SR charges processing in a batch of 1000
996 FETCH c_Get_contact_point_txns2 BULK COLLECT
997 INTO l_batch_party_id_tbl ,
998 l_from_party_id_tbl ,
999 l_to_party_id_tbl,
1000 l_sr_contact_point_id_tbl
1001 LIMIT 1000;
1002
1003 IF c_Get_contact_point_txns2%NOTFOUND THEN
1004 l_last_fetch := TRUE;
1005 END IF;
1006
1007
1008 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1009 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
1010 dbg_msg := ('No. of SR Contact point transactions to be updated in a batch and impacted due to party merge : '||c_Get_contact_point_txns2%ROWCOUNT);
1011 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1012 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
1013 END IF;
1014 END IF;
1015 END IF;
1016
1017 IF l_from_party_id_tbl.count <> 0 AND l_last_fetch = FALSE THEN -- for last fetch
1018 EXIT;
1019 END IF ;
1020
1021 -- Call SR routine to update party id in cs_hz_sr_contact_points table.
1022
1023 FOR i IN 1..l_sr_contact_point_id_tbl.COUNT
1024 LOOP
1025 CS_SR_PARTY_MERGE_PKG.CS_CONTACTS_MERGE_PARTY
1026 ( p_entity_name => 'CS_HZ_SR_CONTACT_POINTS',
1027 p_from_id => l_sr_contact_point_id_tbl(i),
1028 x_to_id => l_to_id,
1029 p_from_fk_id => l_from_party_id_tbl(i),
1030 p_to_fk_id => l_to_party_id_tbl(i),
1031 p_parent_entity_name => 'HZ_PARTIES',
1032 p_batch_id => p_batch_id,
1033 p_batch_party_id => l_batch_party_id_tbl(i),
1034 x_return_status => l_return_status );
1035 END LOOP;
1036
1037 IF l_last_fetch THEN
1038 EXIT;
1039 END IF ;
1040
1041 END LOOP; -- End Loop for BULK SR charges processing in a batch of 1000
1042 CLOSE c_Get_contact_point_txns2;
1043
1044 -- Clear the memory
1045
1046 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1047 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
1048 dbg_msg := ('SR contact point ,impacted due to party merge, transactions updated, Releasing memory');
1049 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1050 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
1051 END IF;
1052 END IF;
1053 END IF;
1054
1055 l_from_party_id_tbl.DELETE;
1056 l_to_party_id_tbl.DELETE;
1057 l_batch_party_id_tbl.DELETE;
1058 l_sr_contact_point_id_tbl.DELETE;
1059
1060 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1061 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
1062 dbg_msg := ('Releasing memory done');
1063 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1064 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
1065 END IF;
1066 END IF;
1067 END IF;
1068
1069 -- Merging contact points
1070 -- get all the merged contact points transactions in a batch of 1000 .
1071
1072
1073 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1074 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
1075 dbg_msg := ('Updating SR contact point transactions.');
1076 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1077 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
1078 END IF;
1079 END IF;
1080 END IF;
1081
1082 OPEN c_Get_contact_point_txns1;
1083 LOOP -- Loop for BULK SR charges processing in a batch of 1000
1084 FETCH c_Get_contact_point_txns1 BULK COLLECT
1085 INTO l_from_email_id_tbl ,
1086 l_to_email_id_tbl ,
1087 l_batch_party_id_tbl ,
1088 l_sr_contact_point_id_tbl
1089 LIMIT 1000;
1090
1091 IF c_Get_contact_point_txns1%NOTFOUND THEN
1092 l_last_fetch := TRUE;
1093 END IF;
1094
1095
1096 IF l_from_email_id_tbl.count <> 0 AND l_last_fetch = FALSE THEN -- for last fetch
1097 EXIT;
1098 END IF ;
1099
1100 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1101 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
1102 dbg_msg := ('SR contact point transactions to be updated : '||c_Get_contact_point_txns1%ROWCOUNT);
1103 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1104 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
1105 END IF;
1106 END IF;
1107 END IF;
1108 -- update cs_estimate_details table
1109
1110 FOR i IN 1..l_sr_contact_point_id_tbl.COUNT
1111
1112 LOOP
1113 CS_SR_PARTY_MERGE_PKG.CS_CONTACTS_MERGE_CONT_POINTS
1114 ( p_entity_name => 'CS_HZ_SR_CONTACT_POINTS',
1115 p_from_id => l_sr_contact_point_id_tbl(i),
1116 x_to_id => l_to_id,
1117 p_from_fk_id => l_from_email_id_tbl(i),
1118 p_to_fk_id => l_to_email_id_tbl(i),
1119 p_parent_entity_name => 'HZ_CONTACT_POINTS',
1120 p_batch_id => p_batch_id,
1121 p_batch_party_id => l_batch_party_id_tbl(i),
1122 x_return_status => l_return_status );
1123 END LOOP;
1124
1125 IF l_last_fetch THEN
1126 EXIT;
1127 END IF ;
1128 END LOOP; -- End Loop for BULK SR charges processing in a batch of 1000
1129 CLOSE c_Get_contact_point_txns1;
1130
1131 -- Clear the memory
1132
1133 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1134 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
1135 dbg_msg := ('SR contact point ,impacted due to contact point merge, transactions updated, Releasing memory');
1136 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1137 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
1138 END IF;
1139 END IF;
1140 END IF;
1141
1142 l_from_email_id_tbl.DELETE;
1143 l_to_email_id_tbl.DELETE;
1144 l_batch_party_id_tbl.DELETE;
1145 l_sr_contact_point_id_tbl.DELETE;
1146
1147 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1148 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
1149 dbg_msg := ('Releasing memory done');
1150 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1151 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
1152 END IF;
1153 END IF;
1154 END IF;
1155
1156 EXCEPTION
1157 WHEN others THEN
1158 arp_message.set_line(g_pkg_name || '.' || g_proc_name || ': ' || sqlerrm);
1159 x_return_status := FND_API.G_RET_STS_ERROR;
1160 ROLLBACK;
1161 RAISE;
1162 END;
1163
1164 -- Update party id in SR Charges set up data
1165 --------------------------------------------
1166
1167 BEGIN
1168 -- get all merged parties
1169
1170 OPEN C_Get_merged_parties;
1171 LOOP -- Loop for BULK SR charges processing in a batch of 1000
1172 FETCH C_Get_merged_parties BULK COLLECT
1173 INTO l_from_party_id_tbl ,
1174 l_to_party_id_tbl,
1175 l_batch_party_id_tbl
1176 LIMIT 1000;
1177
1178 IF C_Get_merged_parties%NOTFOUND THEN
1179 l_last_fetch := TRUE;
1180 END IF;
1181
1182 IF l_from_party_id_tbl.count <> 0 AND l_last_fetch = FALSE THEN -- for last fetch
1183 EXIT;
1184 END IF ;
1185
1186 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1187 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
1188 dbg_msg := ('Calling SR Charges routine to update sub restriction setup. Parties merged : '||C_Get_merged_parties%ROWCOUNT);
1189 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1190 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
1191 END IF;
1192 END IF;
1193 END IF;
1194
1195 -- update cs_estimate_details table
1196 -- Call Charges party merge routine for sub restrictions.
1197
1198 FOR i IN 1..l_from_party_id_tbl.COUNT
1199
1200 LOOP
1201 CS_CH_PARTY_MERGE_PKG.CS_CHG_ALL_SETUP_PARTY
1202 (p_entity_name => 'CS_CHG_SUB_RESTRICTIONS',
1203 p_from_id => null,
1204 x_to_id => l_to_id,
1205 p_from_fk_id => l_from_party_id_tbl(i),
1206 p_to_fk_id => l_to_party_id_tbl(i),
1207 p_parent_entity_name => 'HZ_PARTIES',
1208 p_batch_id => p_batch_id,
1209 p_batch_party_id => l_batch_party_id_tbl(i),
1210 x_return_status => l_return_status );
1211
1212 END LOOP;
1213
1214 IF l_last_fetch THEN
1215 EXIT;
1216 END IF ;
1217
1218 END LOOP; -- End Loop for BULK SR charges processing in a batch of 1000
1219 CLOSE C_Get_merged_parties;
1220
1221 -- Clear the memory
1222
1223 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1224 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
1225 dbg_msg := ('SR charged sub restrictions data impacted due to party merge, transactions updated, Releasing memory');
1226 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1227 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
1228 END IF;
1229 END IF;
1230 END IF;
1231
1232 l_from_party_id_tbl.DELETE;
1233 l_to_party_id_tbl.DELETE;
1234 l_batch_party_id_tbl.DELETE;
1235
1236 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1237 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
1238 dbg_msg := ('Releasing memory done');
1239 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1240 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
1241 END IF;
1242 END IF;
1243 END IF;
1244
1245 EXCEPTION
1246 WHEN others THEN
1247 arp_message.set_line(g_pkg_name || '.' || g_proc_name || ': ' || sqlerrm);
1248
1249 x_return_status := FND_API.G_RET_STS_ERROR;
1250 ROLLBACK;
1251 RAISE;
1252 END ;
1253
1254 l_rowid_tbl.DELETE;
1255 l_incident_id_tbl.DELETE;
1256 l_estimate_detail_id_tbl.DELETE;
1257 l_customer_id_tbl.DELETE;
1258 l_from_party_id_tbl.DELETE;
1259 l_to_party_id_tbl.DELETE;
1260 l_bill_to_party_id_tbl.DELETE;
1261
1262 -- Update party id in SR Party Role Extensible attributes table
1263 ---------------------------------------------------------------
1264
1265 BEGIN
1266 -- get all impacted ext. attribute records.
1267
1268 OPEN c_get_party_ext_attr_rec;
1269 LOOP -- Loop for BULK SR charges processing in a batch of 1000
1270 FETCH c_get_party_ext_attr_rec BULK COLLECT
1271 INTO l_rowid_tbl,
1272 l_extension_id_tbl,
1273 l_customer_id_tbl,
1274 l_from_party_id_tbl ,
1275 l_to_party_id_tbl
1276 LIMIT 1000;
1277
1278 IF c_get_party_ext_attr_rec%NOTFOUND THEN
1279 l_last_fetch := TRUE;
1280 END IF;
1281
1282 IF l_rowid_tbl.count <> 0 AND l_last_fetch = FALSE THEN -- for last fetch
1283 EXIT;
1284 END IF ;
1285
1286 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1287 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
1288 dbg_msg := ('Updating extensible attributes for the party role. Parties merged : '||c_get_party_ext_attr_rec%ROWCOUNT);
1289 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1290 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
1291 END IF;
1292 END IF;
1293 END IF;
1294
1295 -- update cs_estimate_details table
1296 -- Call Charges party merge routine for sub restrictions.
1297
1298 FORALL i IN 1..l_rowid_tbl.COUNT
1299
1300 UPDATE cs_sr_contacts_ext
1301 SET party_id = DECODE(party_id,l_from_party_id_tbl(i),l_to_party_id_tbl(i),party_id)
1302 WHERE rowid = l_rowid_tbl(i);
1303
1304 l_count := sql%rowcount;
1305
1306 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1307 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
1308 dbg_msg := ('No. of Party role ext attribute records updated : '||l_count);
1309 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1310 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
1311 END IF;
1312 END IF;
1313 END IF;
1314
1315 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
1316 arp_message.set_token('NUM_ROWS', to_char(l_count) );
1317
1318 -- Create SR Audit record for each updated party role extensible attribute transaction
1319
1320 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1321 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
1322 dbg_msg := ('Creating SR Audit record for each updated party role ext. attr. record');
1323 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1324 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
1325 END IF;
1326 END IF;
1327 END IF;
1328
1329 FOR i IN 1..l_rowid_tbl.COUNT
1330 LOOP
1331 -- Prepare SR audit record structure
1332 CS_SR_EXTATTRIBUTES_PVT.Populate_Ext_Attr_Audit_Tbl
1333 ( P_EXTENSION_ID => l_extension_id_tbl(i)
1334 , X_EXT_ATTRS_TBL => l_ext_attrs_tbl_new
1335 , X_RETURN_STATUS => l_return_status
1336 , X_MSG_COUNT => x_msg_count
1337 , X_MSG_DATA => x_msg_data) ;
1338
1339 IF l_return_status = FND_API.G_RET_STS_SUCCESS Then
1340
1341 l_ext_attrs_tbl_old := l_ext_attrs_tbl_new ;
1342
1343 -- Populate the old values
1344
1345 FOR j IN 1..l_ext_attrs_tbl_old.COUNT
1346 LOOP
1347 l_ext_attrs_tbl_old(j).pk_column_2 := l_customer_id_tbl(i);
1348 END LOOP;
1349
1350 END IF;
1351
1352 -- Call Create SR Audit API
1353
1354 CS_SR_EXTATTRIBUTES_PVT.Create_Ext_Attr_Audit
1355 ( p_sr_ea_new_audit_rec_table => l_ext_attrs_tbl_new
1356 ,p_sr_ea_old_audit_rec_table => l_ext_attrs_tbl_old
1357 ,p_object_name => 'CS_PARTY_ROLE'
1358 ,p_modified_by => FND_GLOBAL.USER_ID
1359 ,p_modified_on => sysdate
1360 ,x_return_status => l_return_status
1361 ,x_msg_count => x_msg_count
1362 ,x_msg_data => x_msg_data);
1363
1364 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1365 RAISE FND_API.G_EXC_ERROR;
1366 END IF ;
1367
1368 END LOOP; -- End loop for SR auditing
1369
1370
1371 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1372 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
1373 dbg_msg := ('Created SR audit records for the SRs updated in a batch');
1374 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1375 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
1376 END IF;
1377 END IF;
1378 END IF;
1379
1380 IF l_last_fetch THEN
1381 EXIT;
1382 END IF ;
1383
1384 END LOOP; -- End Loop for BULK SR charges processing in a batch of 1000
1385 CLOSE c_get_party_ext_attr_rec;
1386
1387
1388 EXCEPTION
1389 WHEN others THEN
1390 arp_message.set_line(g_pkg_name || '.' || g_proc_name || ': ' || sqlerrm);
1391
1392 x_return_status := FND_API.G_RET_STS_ERROR;
1393 ROLLBACK;
1394 RAISE;
1395 END ;
1396
1397 -- Clear the memory
1398
1399 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1400 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
1401 dbg_msg := ('SR extensible attributes,associated with party role, data impacted due to party merge, transactions updated');
1402 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1403 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
1404 END IF;
1405 END IF;
1406 END IF;
1407
1408 l_rowid_tbl.DELETE;
1409 l_extension_id_tbl.DELETE;
1410 l_customer_id_tbl.DELETE;
1411 l_from_party_id_tbl.DELETE;
1412 l_to_party_id_tbl.DELETE;
1413
1414 IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
1415 IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data')) THEN
1416 dbg_msg := ('Releasing memory done');
1417 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1418 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_PartyMerge_PKG.Update_CS_Data', dbg_msg);
1419 END IF;
1420 END IF;
1421 END IF;
1422
1423 EXCEPTION
1424 WHEN resource_busy THEN
1425 arp_message.set_line(g_pkg_name || '.' || g_proc_name || ': Could not obtain lock for records in table '||
1426 'for columns referring HZ entities');
1427 x_return_status := FND_API.G_RET_STS_ERROR;
1428 ROLLBACK;
1429 RAISE;
1430
1431 WHEN others THEN
1432 arp_message.set_line(g_pkg_name || '.' || g_proc_name || ': ' || sqlerrm);
1433 x_return_status := FND_API.G_RET_STS_ERROR;
1434 ROLLBACK;
1435 RAISE;
1436
1437 END UPDATE_CS_DATA;
1438
1439 END CS_PARTYMERGE_PKG;