1 Package BODY AR_CMGT_PARTY_MERGE AS
2 /* $Header: ARCMGPMB.pls 120.1.12010000.2 2008/09/08 08:36:07 rviriyal ship $ */
3
4 PROCEDURE CREDIT_REQUEST_MERGE (
5 p_entity_name IN VARCHAR2,
6 p_from_id IN NUMBER,
7 x_to_id OUT NOCOPY NUMBER,
8 p_from_fk_id IN NUMBER,
9 p_to_fk_id IN NUMBER,
10 p_parent_entity_name IN VARCHAR2,
11 p_batch_id IN NUMBER,
12 p_batch_party_id IN NUMBER,
13 x_return_status OUT NOCOPY VARCHAR2) AS
14
15 l_merge_reason_code hz_merge_batch.merge_reason_code%type;
16
17 Begin
18 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start Party Merge for AR_CMGT_CREDIT_REQUEST(+)');
19 x_return_status := FND_API.G_RET_STS_SUCCESS;
20
21 /* Check the Merge reason. If Merge Reason is 'Duplicate Record'
22 then no validation is performed. */
23
24 select merge_reason_code
25 into l_merge_reason_code
26 from hz_merge_batch
27 where batch_id = p_batch_id;
28
29 FND_FILE.PUT_LINE(FND_FILE.LOG,'Merge Reason Code '||l_merge_reason_code);
30
31 if l_merge_reason_code = 'DUPLICATE' then
32 null;
33 end if;
34
35
36 /* Perform the Merge Operation. */
37
38 /* If the Parent has NOT changed(i.e. Parent getting transferred)
39 then nothing needs to be done.
40 Set Merged To Id is the same as Merged From Id and return */
41
42 if p_from_FK_id = p_to_FK_id then
43 x_to_id := p_from_id;
44 return;
45 end if;
46
47
48 /* If the Parent has changed(i.e. Parent is getting merged),
49 then transfer the dependent record to the new parent.
50 Before transferring check if a similar dependent record
51 exists on the new parent . If a duplicate exists then do not
52 transfer and return the id of the duplicate record as the Merged To Id.*/
53
54 if p_from_FK_id <> p_to_FK_id then
55 if p_parent_entity_name = 'HZ_PARTIES' then
56 UPDATE ar_cmgt_credit_requests
57 set party_id = p_To_FK_id
58 WHERE party_id = p_from_fk_id;
59 end if;
60 end if;
61 FND_FILE.PUT_LINE(FND_FILE.LOG,'End Party Merge for AR_CMGT_CREDIT_REQUEST(-)');
62 Exception
63 when others then
64 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
65 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
66 FND_MSG_PUB.ADD;
67 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
68 End;
69
70 PROCEDURE CONTACT_MERGE (
71 p_entity_name IN VARCHAR2,
72 p_from_id IN NUMBER,
73 x_to_id OUT NOCOPY NUMBER,
74 p_from_fk_id IN NUMBER,
75 p_to_fk_id IN NUMBER,
76 p_parent_entity_name IN VARCHAR2,
77 p_batch_id IN NUMBER,
78 p_batch_party_id IN NUMBER,
79 x_return_status OUT NOCOPY VARCHAR2) AS
80
81 l_merge_reason_code hz_merge_batch.merge_reason_code%type;
82
83 Begin
84 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start Contact Merge for AR_CMGT_CREDIT_REQUEST(+)');
85
86 x_return_status := FND_API.G_RET_STS_SUCCESS;
87
88 /* Check the Merge reason. If Merge Reason is 'Duplicate Record'
89 then no validation is performed. */
90
91 select merge_reason_code
92 into l_merge_reason_code
93 from hz_merge_batch
94 where batch_id = p_batch_id;
95
96 if l_merge_reason_code = 'DUPLICATE' then
97 null;
98 end if;
99
100
101 /* Perform the Merge Operation. */
102
103 /* If the Parent has NOT changed(i.e. Parent getting transferred)
104 then nothing needs to be done.
105 Set Merged To Id is the same as Merged From Id and return */
106
107 if p_from_FK_id = p_to_FK_id then
108 x_to_id := p_from_id;
109 return;
110 end if;
111
112
113 /* If the Parent has changed(i.e. Parent is getting merged),
114 then transfer the dependent record to the new parent.
115 Before transferring check if a similar dependent record
116 exists on the new parent . If a duplicate exists then do not
117 transfer and return the id of the duplicate record as the Merged To Id.*/
118
119 if p_from_FK_id <> p_to_FK_id then
120 if p_parent_entity_name = 'HZ_PARTIES' then
121 UPDATE ar_cmgt_credit_requests
122 set contact_party_id = p_To_FK_id
123 WHERE contact_party_id = p_from_fk_id
124 AND contact_party_id IS NOT NULL;
125 end if;
126 end if;
127 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start Contact Merge for AR_CMGT_CREDIT_REQUEST(-)');
128 Exception
129 when others then
130 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
131 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
132 FND_MSG_PUB.ADD;
133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
134 End;
135
136 PROCEDURE CASE_FOLDER_MERGE (
137 p_entity_name IN VARCHAR2,
138 p_from_id IN NUMBER,
139 x_to_id OUT NOCOPY NUMBER,
140 p_from_fk_id IN NUMBER,
141 p_to_fk_id IN NUMBER,
142 p_parent_entity_name IN VARCHAR2,
143 p_batch_id IN NUMBER,
144 p_batch_party_id IN NUMBER,
145 x_return_status OUT NOCOPY VARCHAR2) AS
146
147 l_merge_reason_code hz_merge_batch.merge_reason_code%type;
148
149 Begin
150 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start Party Merge for AR_CMGT_CASE_FOLDERS(+)');
151 x_return_status := FND_API.G_RET_STS_SUCCESS;
152
153 /* Check the Merge reason. If Merge Reason is 'Duplicate Record'
154 then no validation is performed. */
155
156 select merge_reason_code
157 into l_merge_reason_code
158 from hz_merge_batch
159 where batch_id = p_batch_id;
160
161 if l_merge_reason_code = 'DUPLICATE' then
162 null;
163 end if;
164
165
166 /* Perform the Merge Operation. */
167
168 /* If the Parent has NOT changed(i.e. Parent getting transferred)
169 then nothing needs to be done.
170 Set Merged To Id is the same as Merged From Id and return */
171
172 if p_from_FK_id = p_to_FK_id then
173 x_to_id := p_from_id;
174 return;
175 end if;
176
177
178 /* If the Parent has changed(i.e. Parent is getting merged),
179 then transfer the dependent record to the new parent.
180 Before transferring check if a similar dependent record
181 exists on the new parent . If a duplicate exists then do not
182 transfer and return the id of the duplicate record as the Merged To Id.*/
183
184 if p_from_FK_id <> p_to_FK_id then
185 if p_parent_entity_name = 'HZ_PARTIES' then
186 -- First Delete the data record in case of
187 -- merge otherwise it will create
188 -- a duplicate record bug 7370428
189 DELETE from ar_cmgt_cf_dtls
190 WHERE case_folder_id IN (
191 SELECT case_folder_id
192 from ar_cmgt_case_folders
193 WHERE party_id = p_from_fk_id
194 AND type = 'DATA' );
195
196 DELETE from ar_cmgt_case_folders
197 WHERE party_id = p_from_fk_id
198 AND type = 'DATA';
199
200 UPDATE ar_cmgt_case_folders
201 set party_id = p_To_FK_id
202 WHERE party_id = p_from_fk_id;
203 end if;
204 end if;
205 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start Party Merge for AR_CMGT_CASE_FOLDERS(-)');
206 Exception
207 when others then
208 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
209 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
210 FND_MSG_PUB.ADD;
211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
212 End;
213
214
215 PROCEDURE MERGE_CT_CALLS_INFO(
216 p_entity_name IN VARCHAR2,
217 p_from_id IN NUMBER,
218 x_to_id OUT NOCOPY NUMBER,
219 p_from_fk_id IN NUMBER,
220 p_to_fk_id IN NUMBER,
221 p_parent_entity_name IN VARCHAR2,
222 p_batch_id IN NUMBER,
223 p_batch_party_id IN NUMBER,
224 x_return_status OUT NOCOPY VARCHAR2) IS
225
226 l_merge_reason_code VARCHAR2(30);
227
228 Cursor c_duplicate Is
229 select merge_reason_code
230 from hz_merge_batch
231 where batch_id = p_batch_id;
232
233
234 BEGIN
235
236 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start Party Merge for AR_CUSTOMER_CALLS_ALL(+)');
237
238
239 x_return_status := FND_API.G_RET_STS_SUCCESS;
240
241 open c_duplicate;
242 fetch c_duplicate into l_merge_reason_code;
243 close c_duplicate;
244
245 if l_merge_reason_code <> 'DUPLICATE' then
246
247
248
249 -- if there are any validations to be done, include it in this section
250 -- if reason code is duplicate then allow the party merge to happen without
251 -- any validations.
252
253 null;
254
255 end if;
256
257 -- perform the merge operation
258
259 -- if the parent has NOT changed(i.e. child getting transferred) then nothing
260 -- needs to be done. Set merged to id (x_to_id) the same as merged from id and return
261
262 if p_from_fk_id = p_to_fk_id then
263
264 x_to_id := p_from_id;
265 return;
266
267 end if;
268
269
270 -- If the parent has changed(ie. Parent is getting merged) then transfer the
271 -- dependent record to the new parent.
272 -- For AR_CUSTOMER_CALLS_ALL table, if party_id 1000 got merged to party_id 2000
273 -- then, we have to update all records with customer_id = 1000 to 2000
274
275 if p_from_fk_id <> p_to_fk_id then
276
277
278 UPDATE AR_CUSTOMER_CALLS_ALL
279 SET phone_id = p_to_fk_id,
280 last_update_date = hz_utility_pub.last_update_date,
281 last_updated_by = hz_utility_pub.user_id,
282 last_update_login = hz_utility_pub.last_update_login
283 WHERE
284 phone_id = p_from_fk_id;
285
286 end if;
287
288 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start Party Merge for AR_CUSTOMER_CALLS_ALL(-)');
289
290 exception
291 when others then
292
293 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
294 fnd_message.set_token('ERROR' ,SQLERRM);
295 fnd_msg_pub.add;
296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
297
298
299 END MERGE_CT_CALLS_INFO;
300
301
302 PROCEDURE MERGE_CT_CALL_TOPICS_INFO(
303 p_entity_name IN VARCHAR2,
304 p_from_id IN NUMBER,
305 x_to_id OUT NOCOPY NUMBER,
306 p_from_fk_id IN NUMBER,
307 p_to_fk_id IN NUMBER,
308 p_parent_entity_name IN VARCHAR2,
309 p_batch_id IN NUMBER,
310 p_batch_party_id IN NUMBER,
311 x_return_status OUT NOCOPY VARCHAR2) IS
312
313 l_merge_reason_code VARCHAR2(30);
314
315 Cursor c_duplicate Is
316 select merge_reason_code
317 from hz_merge_batch
318 where batch_id = p_batch_id;
319
320
321 BEGIN
322
323 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start Party Merge for AR_CUSTOMER_CALL_TOPICS_ALL(+)');
324
325 x_return_status := FND_API.G_RET_STS_SUCCESS;
326
327 open c_duplicate;
328 fetch c_duplicate into l_merge_reason_code;
329 close c_duplicate;
330
331 if l_merge_reason_code <> 'DUPLICATE' then
332
333 -- if there are any validations to be done, include it in this section
334 -- if reason code is duplicate then allow the party merge to happen without
335 -- any validations.
336
337 null;
338
339 end if;
340
341 -- perform the merge operation
342
343 -- if the parent has NOT changed(i.e. child getting transferred) then nothing
344 -- needs to be done. Set merged to id (x_to_id) the same as merged from id and return
345
346 if p_from_fk_id = p_to_fk_id then
347
348 x_to_id := p_from_id;
349 return;
350
351 end if;
352
353
354 -- If the parent has changed(ie. Parent is getting merged) then transfer the
355 -- dependent record to the new parent.
356 -- For AR_CUSTOMER_CALLS_ALL table, if party_id 1000 got merged to party_id 2000
357 -- then, we have to update all records with customer_id = 1000 to 2000
358
359 if p_from_fk_id <> p_to_fk_id then
360
361
362 UPDATE AR_CUSTOMER_CALL_TOPICS_ALL
363 SET phone_id = p_to_fk_id,
364 last_update_date = hz_utility_pub.last_update_date,
365 last_updated_by = hz_utility_pub.user_id,
366 last_update_login = hz_utility_pub.last_update_login
367 WHERE
368 phone_id = p_from_fk_id;
369
370 end if;
371
372 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start Party Merge for AR_CUSTOMER_CALL_TOPICS_ALL(-)');
373
374 exception
375 when others then
376
377 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
378 fnd_message.set_token('ERROR' ,SQLERRM);
379 fnd_msg_pub.add;
380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
381
382
383 END MERGE_CT_CALL_TOPICS_INFO;
384
385
386
387 END;
[Home] [Help]