1 PACKAGE BODY CS_CH_PARTY_MERGE_PKG AS
2 /* $Header: cschmpgb.pls 120.0 2006/02/09 16:38:32 spusegao noship $ */
3
4 -- Start of Comments
5 -- Package name : CS_CH_PARTY_MERGE_PKG
6 -- Purpose : Merges duplicate party_sites in Service tables. The
7 -- Service tables that need to be considered
8 -- are:
9 -- CS_ESTIMATE_DETAILS and CS_CHG_SUB_RESTRICTIONS.
10 --
11 -- History
12 -- MM-DD-YYYY NAME MODIFICATIONS
13 -- 11-20-2000 aseethep Created.
14 -- 10-22-2001 tkochend Removed commit statement
15 -- 11-08-2002 mviswana Added NOCOPY Functionality to file
16 -- 12-04-2002 tkochend Moved to correct driver phase
17 -- 05-04-2003 mviswana Added the new 11.5.9 TCA FK from cs_estimate_details
18 -- 06-04-2003 mviswana Changed the procedure to merge sites to follow TCA stds of using
19 -- p_from_fk_id
20 -- 08-12-2003 cnemalik For 11.5.10, added the new Bill To Customer Restriction in the
21 -- Auto Submission Restriction Table.
22 -- 04-28-2004 mviswana Added logic to check for duplicate active restrictions after the merge is done
23 -- Fix for Bug # 3599517
24 -- End of Comments
25
26 G_PROC_NAME CONSTANT VARCHAR2(30) := 'CS_CH_PARTY_MERGE_PKG';
27 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cschpmgb.pls';
28 G_USER_ID CONSTANT NUMBER(15) := FND_GLOBAL.USER_ID;
29 G_LOGIN_ID CONSTANT NUMBER(15) := FND_GLOBAL.CONC_LOGIN_ID;
30
31
32 TYPE ROWID_TBL IS TABLE OF ROWID
33 INDEX BY BINARY_INTEGER;
34
35
36 -- The following procedure merges CS_ESTIMATE_DETAILS columns:
37 -- bill_to_party_id
38 -- ship_to_party_id
39 -- bill_to_contact_id
40 -- ship_to_contact_id
41 -- The above columns are FKs to HZ_PARTIES.PARTY_ID
42
43 PROCEDURE CS_CHG_ALL_MERGE_PARTY (
44 p_entity_name IN VARCHAR2,
45 p_from_id IN NUMBER,
46 x_to_id OUT NOCOPY NUMBER,
47 p_from_fk_id IN NUMBER,
48 p_to_fk_id IN NUMBER,
49 p_parent_entity_name IN VARCHAR2,
50 p_batch_id IN NUMBER,
51 p_batch_party_id IN NUMBER,
52 x_return_status OUT NOCOPY VARCHAR2)
53
54 IS
55 -- cursor fetches all the records that need to be merged.
56 cursor c1 is
57 select rowid
58 from cs_estimate_details
59 where p_from_fk_id in (bill_to_contact_id, ship_to_contact_id,
60 bill_to_party_id, ship_to_party_id )
61 for update nowait;
62
63 l_rowid_tbl ROWID_TBL;
64
65 l_merge_reason_code VARCHAR2(30);
66 l_api_name VARCHAR2(30) := 'CS_CHG_ALL_MERGE_PARTY';
67 l_count NUMBER(10) := 0;
68
69 RESOURCE_BUSY EXCEPTION;
70 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
71
72 BEGIN
73
74 arp_message.set_line('CS_CH_PARTY_MERGE_PKG.CS_CH_ALL_MERGE_SITE_ID()+');
75
76 x_return_status := FND_API.G_RET_STS_SUCCESS;
77
78 select merge_reason_code
79 into l_merge_reason_code
80 from hz_merge_batch
81 where batch_id = p_batch_id;
82
83 if l_merge_reason_code = 'DUPLICATE' then
84 -- if reason code is duplicate then allow the party merge to happen without
85 -- any validations.
86 null;
87 else
88 -- if there are any validations to be done, include it in this section
89 null;
90 end if;
91
92 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
93 -- needs to be done. Set Merged To Id is same as Merged From Id and return
94
95 if p_from_fk_id = p_to_fk_id then
96 x_to_id := p_from_id;
97 return;
98 end if;
99
100 -- If the parent has changed(ie. Parent is getting merged) then transfer the
101 -- dependent record to the new parent. Before transferring check if a similar
102 -- dependent record exists on the new parent. If a duplicate exists then do
103 -- not transfer and return the id of the duplicate record as the Merged To Id
104
105 if p_from_fk_id <> p_to_fk_id then
106 begin
107 -- obtain lock on records to be updated.
108 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
109 arp_message.set_token('TABLE_NAME', 'CS_ESTIMATE_DETAILS', FALSE);
110
111 open c1;
112 fetch c1 bulk collect into l_rowid_tbl;
113 close c1;
114
115 -- if no records were found to be updated then stop and return to calling prg.
116 if l_rowid_tbl.count = 0 then
117 RETURN;
118 end if;
119
120 forall i in 1..l_rowid_tbl.count
121 update cs_estimate_details
122 set bill_to_contact_id = decode(bill_to_contact_id, p_from_fk_id, p_to_fk_id, bill_to_contact_id),
123 ship_to_contact_id = decode(ship_to_contact_id, p_from_fk_id, p_to_fk_id, ship_to_contact_id),
124 bill_to_party_id = decode(bill_to_party_id, p_from_fk_id, p_to_fk_id, bill_to_party_id),
125 ship_to_party_id = decode(ship_to_party_id, p_from_fk_id, p_to_fk_id, ship_to_party_id),
126 object_version_number = object_version_number + 1,
127 last_update_date = SYSDATE,
128 last_updated_by = G_USER_ID,
129 last_update_login = G_LOGIN_ID
130 where rowid = l_rowid_tbl(i);
131
132 l_count := sql%rowcount;
133
134 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
135 arp_message.set_token('NUM_ROWS', to_char(l_count) );
136
137 exception
138 when resource_busy then
139 arp_message.set_line(g_proc_name || '.' || l_api_name || '; Could not obtain lock for records in table ' ||
140 'CS_ESTIMATE_DETAILS for bill_to_party_id / ship_to_party_id ' ||
141 'bill_to_contact_id / ship_to_contact_id = ' || p_from_fk_id );
142 x_return_status := FND_API.G_RET_STS_ERROR;
143 raise;
144
145 when others then
146 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
147 x_return_status := FND_API.G_RET_STS_ERROR;
148 raise;
149 end;
150
151 end if; -- if p_from_fk_id <> p_to_fk_id
152
153 END CS_CHG_ALL_MERGE_PARTY ;
154
155
156 -- The following procedure merges CS_ESTIMATE_DETAILS columns:
157 -- invoice_to_org_id
158 -- ship_to_org_id
159
160 PROCEDURE CS_CHG_ALL_MERGE_SITE_ID(
161 p_entity_name IN VARCHAR2,
162 p_from_id IN NUMBER,
163 x_to_id OUT NOCOPY NUMBER,
164 p_from_fk_id IN NUMBER,
165 p_to_fk_id IN NUMBER,
166 p_parent_entity_name IN VARCHAR2,
167 p_batch_id IN NUMBER,
168 p_batch_party_id IN NUMBER,
169 x_return_status OUT NOCOPY VARCHAR2)
170
171 IS
172
173 -- Added for bug # 2983666
174 -- cursor fetches all the records that need to be merged.
175 cursor c1 is
176 select rowid
177 from cs_estimate_details
178 where p_from_fk_id in (invoice_to_org_id, ship_to_org_id)
179 for update nowait;
180
181 l_rowid_tbl ROWID_TBL;
182
183
184 l_merge_reason_code VARCHAR2(30);
185 l_api_name VARCHAR2(30) := 'CS_CH_ALL_MERGE_SITE_ID';
186 l_count NUMBER(10) := 0;
187
188 RESOURCE_BUSY EXCEPTION;
189 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
190
191 BEGIN
192 arp_message.set_line('CS_CH_PARTY_MERGE_PKG.CS_CH_ALL_MERGE_SITE_ID()+');
193
194 x_return_status := FND_API.G_RET_STS_SUCCESS;
195
196 select merge_reason_code
197 into l_merge_reason_code
198 from hz_merge_batch
199 where batch_id = p_batch_id;
200
201 if l_merge_reason_code = 'DUPLICATE' then
202 -- if reason code is duplicate then allow the party merge to happen without
203 -- any validations.
204 null;
205 else
206 -- if there are any validations to be done, include it in this section
207 null;
208 end if;
209
210 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
211 -- needs to be done. Set Merged To Id is same as Merged From Id and return
212
213 if p_from_fk_id = p_to_fk_id then
214 x_to_id := p_from_id;
215 return;
216 end if;
217
218 -- If the parent has changed(ie. Parent is getting merged) then transfer the
219 -- dependent record to the new parent. Before transferring check if a similar
220 -- dependent record exists on the new parent. If a duplicate exists then do
221 -- not transfer and return the id of the duplicate record as the Merged To Id
222
223
224 -- In the case of CS_ESTIMATE_DETAILS table, we store invoice_to_org_id
225 -- and ship_to_org_id which are forign keys to HZ_PARTY_SITES.PARTY_SITE_ID.
226 -- If the party who is tied to this site has been merged then, it is possible
227 -- that this site use id is being transferred under the new party or it
228 -- may have been deleted if its a duplicate party_site_use_id
229
230
231 if p_from_fk_id <> p_to_fk_id then
232 begin
233 -- obtain lock on records to be updated.
234 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
235 arp_message.set_token('TABLE_NAME', 'CS_ESTIMATE_DETAILS', FALSE);
236
237 open c1;
238 fetch c1 bulk collect into l_rowid_tbl;
239 close c1;
240
241 -- if no records were found to be updated then stop and return to calling prg.
242 if l_rowid_tbl.count = 0 then
243 RETURN;
244 end if;
245
246
247 -- Commented for bug # 2983666
248 /*
249
250 -- dbms_output.put_line('Beggining of Update');
251 update cs_estimate_details
252 set invoice_to_org_id = decode(invoice_to_org_id, p_from_fk_id, p_to_fk_id, invoice_to_org_id),
253 ship_to_org_id = decode(ship_to_org_id,p_from_fk_id,p_to_fk_id,ship_to_org_id),
254 last_update_date = SYSDATE,
255 last_updated_by = G_USER_ID,
256 last_update_login = G_LOGIN_ID
257 where estimate_detail_id = p_from_id;
258
259 */
260
261 -- Added for bug # 2983666
262 -- dbms_output.put_line('Beggining of Update');
263 forall i in 1..l_rowid_tbl.count
264 update cs_estimate_details
265 set invoice_to_org_id = decode(invoice_to_org_id, p_from_fk_id, p_to_fk_id, invoice_to_org_id),
266 ship_to_org_id = decode(ship_to_org_id,p_from_fk_id,p_to_fk_id,ship_to_org_id),
267 last_update_date = SYSDATE,
268 last_updated_by = G_USER_ID,
269 last_update_login = G_LOGIN_ID
270 where rowid = l_rowid_tbl(i);
271
272
273
274 l_count := sql%rowcount;
275
276 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
277 arp_message.set_token('NUM_ROWS', to_char(l_count) );
278
279 exception
280 when resource_busy then
281 arp_message.set_line(g_proc_name || '.' || l_api_name ||
282 '; Could not obtain lock for records in table ' ||
283 'CS_ESTIMATE_DETAILS for invoice_to_org_id/ship_to_org_id = ' || p_from_fk_id );
284 x_return_status := FND_API.G_RET_STS_ERROR;
285 raise;
286
287 when others then
288 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
289 x_return_status := FND_API.G_RET_STS_ERROR;
290 raise;
291 end;
292 end if;
293
294 END CS_CHG_ALL_MERGE_SITE_ID;
295
296 -- The following procedure merges CS_CHG_SUB_RESTRICTIONS columns:
297 -- value_object_id
298
299 PROCEDURE CS_CHG_ALL_SETUP_PARTY(
300 p_entity_name IN VARCHAR2,
301 p_from_id IN NUMBER,
302 x_to_id OUT NOCOPY NUMBER,
303 p_from_fk_id IN NUMBER,
304 p_to_fk_id IN NUMBER,
305 p_parent_entity_name IN VARCHAR2,
306 p_batch_id IN NUMBER,
307 p_batch_party_id IN NUMBER,
308 x_return_status OUT NOCOPY VARCHAR2)
309
310 IS
311 --when value_object_id is equal to the from_party_id and restriction type is bill_to_customer
312 --then process all rows.
313 --cursor fetches all the records that need to be merged.
314 cursor c1 is
315 select rowid
316 from cs_chg_sub_restrictions
317 where value_object_id = p_from_fk_id
318 and restriction_type = 'BILL_TO_CUSTOMER'
319 for update nowait;
320
321 --add cursor to check for active restrictions which are of p_to_fk_id
322 --Fix for Bug # 3599517
323 cursor c_active_restrictions is
324 select restriction_id, value_object_id
325 from cs_chg_sub_restrictions
326 where restriction_type = 'BILL_TO_CUSTOMER'
327 and end_date_active IS NULL;
328
329
330 l_rowid_tbl ROWID_TBL;
331 l_merge_reason_code VARCHAR2(30);
332 l_api_name VARCHAR2(30) := 'CS_CHG_ALL_SETUP_PARTY';
333 l_count NUMBER(10) := 0;
334 l_from_restriction_id NUMBER;
335 l_to_match_found VARCHAR2(1) := 'N';
336 l_from_match_found VARCHAR2(1) := 'N';
337
338 RESOURCE_BUSY EXCEPTION;
339 PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -0054);
340
341 BEGIN
342 arp_message.set_line('CS_CH_PARTY_MERGE_PKG.CS_CHG_ALL_SETUP_PARTY()+');
343
344 x_return_status := FND_API.G_RET_STS_SUCCESS;
345
346
347 select merge_reason_code
348 into l_merge_reason_code
349 from hz_merge_batch
350 where batch_id = p_batch_id;
351
352 if l_merge_reason_code = 'DUPLICATE' then
353 null;
354 else
355 -- if there are any validations to be done, include it in this section
359 if p_from_fk_id = p_to_fk_id then
356 null;
357 end if;
358
360 x_to_id := p_from_id;
361 return;
362 end if;
363
364 if p_from_fk_id <> p_to_fk_id then
365 begin
366 -- obtain lock on records to be updated.
367 arp_message.set_name('AR', 'AR_LOCKING_TABLE');
368 arp_message.set_token('TABLE_NAME', 'CS_CHG_SUB_RESTRICTIONS', FALSE);
369
370 open c1;
371 fetch c1 bulk collect into l_rowid_tbl;
372 close c1;
373
374 -- if no records were found to be updated then stop and return to calling prg.
375 if l_rowid_tbl.count = 0 then
376 RETURN;
377 end if;
378
379 --check for active restrictions of 'BILL_TO_CIUSTOMER' TYPE
380 --Fix for Bug # 3599517
381 for v_active_restrictions IN c_active_restrictions LOOP
382 IF v_active_restrictions.value_object_id = p_from_fk_id THEN
383 l_from_match_found := 'Y';
384 l_from_restriction_id := v_active_restrictions.restriction_id;
385 ELSIF v_active_restrictions.value_object_id = p_to_fk_id THEN
386 l_to_match_found := 'Y';
387 ELSE
388 null;
389 END IF;
390 EXIT WHEN l_from_match_found = 'Y' AND
391 l_to_match_found = 'Y';
392 END LOOP;
393
394 forall i in 1..l_rowid_tbl.count
395 update cs_chg_sub_restrictions
399 last_updated_by = G_USER_ID,
396 set value_object_id = decode(value_object_id, p_from_fk_id, p_to_fk_id,value_object_id),
397 object_version_number = object_version_number + 1,
398 last_update_date = SYSDATE,
400 last_update_login = G_LOGIN_ID
401 where rowid = l_rowid_tbl(i);
402
403 l_count := sql%rowcount;
404
405 arp_message.set_name('AR', 'AR_ROWS_UPDATED');
406 arp_message.set_token('NUM_ROWS', to_char(l_count) );
407
408 --go back and end date the active restriction which had p_from_fk_id merged to
409 --p_to_fk_id since there can only be one active restriction at any one point
410 --Fix for Bug # 3599517
411 IF l_to_match_found = 'Y' AND
412 l_from_match_found = 'Y' THEN
413 update cs_chg_sub_restrictions
414 set end_date_active = SYSDATE - 1
415 where restriction_id = l_from_restriction_id;
416 ELSE
417 null;
418 END IF;
419
420
421
422 exception
423 when resource_busy then
424 arp_message.set_line(g_proc_name || '.' || l_api_name || '; Could not obtain lock for records in table ' || 'CS_CHG_SUB_RESTRICTIONS for value_object_id' || 'bill_to_contact_id / ship_to_contact_id = ' || p_from_fk_id );
425 x_return_status := FND_API.G_RET_STS_ERROR;
426 raise;
427
428 when others then
429 arp_message.set_line(g_proc_name || '.' || l_api_name || ': ' || sqlerrm);
430 x_return_status := FND_API.G_RET_STS_ERROR;
431 raise;
432 end;
433
434 end if; -- if p_from_fk_id <> p_to_fk_id
435
436 END CS_CHG_ALL_SETUP_PARTY;
437
438
439
440
441 END CS_CH_PARTY_MERGE_PKG;
[Home] [Help]