[Home] [Help]
PACKAGE BODY: APPS.AS_OPP_MERGE_PKG
Source
1 PACKAGE BODY AS_OPP_MERGE_PKG as
2 /* $Header: asxpmopb.pls 120.2 2006/05/24 06:14:41 subabu ship $ */
3 --
4 -- NAME
5 -- AS_OPP_MERGE_PKG
6 --
7 -- HISTORY
8 -- 02/20/2001 XDING CREATED
9 --
10
11 G_API_NAME CONSTANT VARCHAR2(30):='AS_OPP_MERGE_PKG.OPP_MERGE';
12
13 PROCEDURE OPP_MERGE
14 ( p_entity_name IN VARCHAR2
15 ,p_from_id IN NUMBER
16 ,p_to_id IN OUT NOCOPY /* file.sql.39 change */ NUMBER
17 ,p_from_fk_id IN NUMBER
18 ,p_to_fk_id IN NUMBER
19 ,p_parent_entity_name IN VARCHAR2
20 ,p_batch_id IN NUMBER
21 ,p_batch_party_id IN NUMBER
22 -- ,p_request_id IN NUMBER
23 ,x_return_status IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
24 ) is
25 l_col_name VARCHAR2(60) ;
26 l_api_version_number CONSTANT NUMBER := 2.0;
27 l_merge_reason_code VARCHAR2(30);
28 BEGIN
29 x_return_status := FND_API.G_RET_STS_SUCCESS;
30
31 select merge_reason_code into l_merge_reason_code
32 from HZ_MERGE_BATCH
33 where batch_id = p_batch_id;
34
35
36 IF l_merge_reason_code = 'DUPLICATE' THEN
37
38 null;
39 ELSE
40 null;
41 END IF;
42
43 if p_from_fk_id = p_to_fk_id then
44 p_to_id := p_from_id;
45 return;
46 end if;
47
48 IF p_from_fk_id <> p_to_fk_id THEN
49 BEGIN
50 IF p_parent_entity_name = 'HZ_PARTIES' THEN
51
52 l_col_name := 'AS_LEADS_ALL.customer_id';
53
54 UPDATE AS_LEADS_ALL
55 set object_version_number = nvl(object_version_number,0) + 1, customer_id = p_to_fk_id,
56 last_update_date = hz_utility_pub.last_update_date,
57 last_updated_by = hz_utility_pub.user_id,
58 last_update_login = hz_utility_pub.last_update_login,
59 request_id = hz_utility_pub.request_id,
60 program_application_id = hz_utility_pub.program_application_id,
61 program_id = hz_utility_pub.program_id,
62 program_update_date = sysdate
63 where customer_id = p_from_fk_id;
64
65 l_col_name := 'AS_LEADS_ALL.close_competitor_id';
66
67 UPDATE AS_LEADS_ALL
68 set object_version_number = nvl(object_version_number,0) + 1, close_competitor_id = p_to_fk_id,
69 last_update_date = hz_utility_pub.last_update_date,
70 last_updated_by = hz_utility_pub.user_id,
71 last_update_login = hz_utility_pub.last_update_login,
72 request_id = hz_utility_pub.request_id,
73 program_application_id = hz_utility_pub.program_application_id,
74 program_id = hz_utility_pub.program_id,
75 program_update_date = sysdate
76 where close_competitor_id = p_from_fk_id;
77
78 /*
79 l_col_name := 'AS_LEADS_ALL.end_user_customer_id';
80
81 UPDATE AS_LEADS_ALL
82 set object_version_number = nvl(object_version_number,0) + 1, end_user_customer_id = p_to_fk_id,
83 last_update_date = hz_utility_pub.last_update_date,
84 last_updated_by = hz_utility_pub.user_id,
85 last_update_login = hz_utility_pub.last_update_login,
86 request_id = hz_utility_pub.request_id,
87 program_application_id = hz_utility_pub.program_application_id,
88 program_id = hz_utility_pub.program_id,
89 program_update_date = sysdate
90 where end_user_customer_id = p_from_fk_id;
91 */
92
93 l_col_name := 'AS_LEADS_ALL.incumbent_partner_party_id';
94
95 UPDATE AS_LEADS_ALL
96 set object_version_number = nvl(object_version_number,0) + 1, incumbent_partner_party_id = p_to_fk_id,
97 incumbent_partner_resource_id=( select resource_id
98 from jtf_rs_resource_extns
99 where category = 'PARTNER'
100 and source_id = p_to_fk_id),
101 last_update_date = hz_utility_pub.last_update_date,
102 last_updated_by = hz_utility_pub.user_id,
103 last_update_login = hz_utility_pub.last_update_login,
104 request_id = hz_utility_pub.request_id,
105 program_application_id = hz_utility_pub.program_application_id,
106 program_id = hz_utility_pub.program_id,
107 program_update_date = sysdate
108 where incumbent_partner_party_id = p_from_fk_id;
109
110 l_col_name := 'AS_LEAD_COMPETITORS.competitor_id';
111
112 UPDATE AS_LEAD_COMPETITORS
113 set object_version_number = nvl(object_version_number,0) + 1, competitor_id = p_to_fk_id,
114 last_update_date = hz_utility_pub.last_update_date,
115 last_updated_by = hz_utility_pub.user_id,
116 last_update_login = hz_utility_pub.last_update_login,
117 request_id = hz_utility_pub.request_id,
118 program_application_id = hz_utility_pub.program_application_id,
119 program_id = hz_utility_pub.program_id,
120 program_update_date = sysdate
121 where competitor_id = p_from_fk_id;
122
123 l_col_name := 'AS_LEAD_CONTACTS_ALL.contact_party_id';
124
125 UPDATE AS_LEAD_CONTACTS_ALL
126 set object_version_number = nvl(object_version_number,0) + 1, contact_party_id = p_to_fk_id,
127 last_update_date = hz_utility_pub.last_update_date,
128 last_updated_by = hz_utility_pub.user_id,
129 last_update_login = hz_utility_pub.last_update_login,
130 request_id = hz_utility_pub.request_id,
131 program_application_id = hz_utility_pub.program_application_id,
132 program_id = hz_utility_pub.program_id,
133 program_update_date = sysdate
134 where contact_party_id = p_from_fk_id;
135
136 l_col_name := 'AS_LEAD_CONTACTS_ALL.customer_id';
137
138 UPDATE AS_LEAD_CONTACTS_ALL
139 set object_version_number = nvl(object_version_number,0) + 1, customer_id = p_to_fk_id,
140 last_update_date = hz_utility_pub.last_update_date,
141 last_updated_by = hz_utility_pub.user_id,
142 last_update_login = hz_utility_pub.last_update_login,
143 request_id = hz_utility_pub.request_id,
144 program_application_id = hz_utility_pub.program_application_id,
145 program_id = hz_utility_pub.program_id,
146 program_update_date = sysdate
147 where customer_id = p_from_fk_id;
148
149 l_col_name := 'AS_SALES_CREDITS.partner_customer_id';
150
151 UPDATE AS_SALES_CREDITS
152 set object_version_number = nvl(object_version_number,0) + 1, partner_customer_id = p_to_fk_id,
153 last_update_date = hz_utility_pub.last_update_date,
154 last_updated_by = hz_utility_pub.user_id,
155 last_update_login = hz_utility_pub.last_update_login,
156 request_id = hz_utility_pub.request_id,
157 program_application_id = hz_utility_pub.program_application_id,
158 program_id = hz_utility_pub.program_id,
159 program_update_date = sysdate
160 where partner_customer_id = p_from_fk_id;
161
162 l_col_name := 'AS_SALES_CREDITS_DENORM.partner_customer_id';
163
164 UPDATE AS_SALES_CREDITS_DENORM
165 set object_version_number = nvl(object_version_number,0) + 1, partner_customer_id = p_to_fk_id,
166 last_update_date = hz_utility_pub.last_update_date,
167 last_updated_by = hz_utility_pub.user_id,
168 last_update_login = hz_utility_pub.last_update_login,
169 request_id = hz_utility_pub.request_id,
170 program_application_id = hz_utility_pub.program_application_id,
171 program_id = hz_utility_pub.program_id,
172 program_update_date = sysdate
173 where partner_customer_id = p_from_fk_id;
174
175 l_col_name := 'AS_SALES_CREDITS_DENORM.customer_id';
176
177 UPDATE AS_SALES_CREDITS_DENORM
178 set object_version_number = nvl(object_version_number,0) + 1, customer_id = p_to_fk_id,
179 last_update_date = hz_utility_pub.last_update_date,
180 last_updated_by = hz_utility_pub.user_id,
181 last_update_login = hz_utility_pub.last_update_login,
182 request_id = hz_utility_pub.request_id,
183 program_application_id = hz_utility_pub.program_application_id,
184 program_id = hz_utility_pub.program_id,
185 program_update_date = sysdate
186 where customer_id = p_from_fk_id;
187
188 -- add AS_LEADS_LOG.customer_id and AS_LEADS_LOG.close_competitor_id
189 -- add AS_SALES_CREDITS_DENORM.close_competitor_id
190
191 l_col_name := 'AS_LEADS_LOG.customer_id';
192
193 UPDATE AS_LEADS_LOG
194 set object_version_number = nvl(object_version_number,0) + 1, customer_id = p_to_fk_id
195 --last_update_date = hz_utility_pub.last_update_date,
196 --last_updated_by = hz_utility_pub.user_id,
197 --last_update_login = hz_utility_pub.last_update_login
198 --request_id = hz_utility_pub.request_id,
199 --program_application_id = hz_utility_pub.program_application_id,
200 --program_id = hz_utility_pub.program_id,
201 --program_update_date = sysdate
202 where customer_id = p_from_fk_id;
203
204 l_col_name := 'AS_LEADS_LOG.close_competitor_id';
205
206 UPDATE AS_LEADS_LOG
207 set object_version_number = nvl(object_version_number,0) + 1, close_competitor_id = p_to_fk_id
208 --last_update_date = hz_utility_pub.last_update_date,
209 --last_updated_by = hz_utility_pub.user_id,
210 --last_update_login = hz_utility_pub.last_update_login,
211 --request_id = hz_utility_pub.request_id,
212 --program_application_id = hz_utility_pub.program_application_id,
213 --program_id = hz_utility_pub.program_id,
214 --program_update_date = sysdate
215 where close_competitor_id = p_from_fk_id;
216
217 l_col_name := 'AS_SALES_CREDITS_DENORM.close_competitor_id';
218
219 UPDATE AS_SALES_CREDITS_DENORM
220 set object_version_number = nvl(object_version_number,0) + 1, close_competitor_id = p_to_fk_id,
221 last_update_date = hz_utility_pub.last_update_date,
222 last_updated_by = hz_utility_pub.user_id,
223 last_update_login = hz_utility_pub.last_update_login,
224 request_id = hz_utility_pub.request_id,
225 program_application_id = hz_utility_pub.program_application_id,
226 program_id = hz_utility_pub.program_id,
227 program_update_date = sysdate
228 where close_competitor_id = p_from_fk_id;
229
230
231 ELSIF p_parent_entity_name = 'HZ_PARTY_SITES' THEN
232
233 l_col_name := 'AS_LEADS_ALL.address_id';
234
235 UPDATE AS_LEADS_ALL
236 set object_version_number = nvl(object_version_number,0) + 1, address_id = p_to_fk_id,
237 last_update_date = hz_utility_pub.last_update_date,
238 last_updated_by = hz_utility_pub.user_id,
239 last_update_login = hz_utility_pub.last_update_login,
240 request_id = hz_utility_pub.request_id,
241 program_application_id = hz_utility_pub.program_application_id,
242 program_id = hz_utility_pub.program_id,
243 program_update_date = sysdate
244 where address_id = p_from_fk_id;
245
246 /*
247 l_col_name := 'AS_LEADS_ALL.end_user_address_id';
248
249 UPDATE AS_LEADS_ALL
250 set object_version_number = nvl(object_version_number,0) + 1, end_user_address_id = p_to_fk_id,
251 last_update_date = hz_utility_pub.last_update_date,
252 last_updated_by = hz_utility_pub.user_id,
253 last_update_login = hz_utility_pub.last_update_login,
254 request_id = hz_utility_pub.request_id,
255 program_application_id = hz_utility_pub.program_application_id,
256 program_id = hz_utility_pub.program_id,
257 program_update_date = sysdate
258 where end_user_address_id = p_from_fk_id;
259 */
260
261 l_col_name := 'AS_LEAD_CONTACTS_ALL.address_id';
262
263 UPDATE AS_LEAD_CONTACTS_ALL
264 set object_version_number = nvl(object_version_number,0) + 1, address_id = p_to_fk_id,
265 last_update_date = hz_utility_pub.last_update_date,
266 last_updated_by = hz_utility_pub.user_id,
267 last_update_login = hz_utility_pub.last_update_login,
268 request_id = hz_utility_pub.request_id,
269 program_application_id = hz_utility_pub.program_application_id,
270 program_id = hz_utility_pub.program_id,
271 program_update_date = sysdate
272 where address_id = p_from_fk_id;
273
274 l_col_name := 'AS_SALES_CREDITS.partner_address_id';
275
276 UPDATE AS_SALES_CREDITS
277 set object_version_number = nvl(object_version_number,0) + 1, partner_address_id = p_to_fk_id,
278 last_update_date = hz_utility_pub.last_update_date,
279 last_updated_by = hz_utility_pub.user_id,
280 last_update_login = hz_utility_pub.last_update_login,
281 request_id = hz_utility_pub.request_id,
282 program_application_id = hz_utility_pub.program_application_id,
283 program_id = hz_utility_pub.program_id,
284 program_update_date = sysdate
285 where partner_address_id = p_from_fk_id;
286
287 l_col_name := 'AS_SALES_CREDITS_DENORM.partner_address_id';
288
289 UPDATE AS_SALES_CREDITS_DENORM
290 set object_version_number = nvl(object_version_number,0) + 1, partner_address_id = p_to_fk_id,
291 last_update_date = hz_utility_pub.last_update_date,
292 last_updated_by = hz_utility_pub.user_id,
293 last_update_login = hz_utility_pub.last_update_login,
294 request_id = hz_utility_pub.request_id,
295 program_application_id = hz_utility_pub.program_application_id,
296 program_id = hz_utility_pub.program_id,
297 program_update_date = sysdate
298 where partner_address_id = p_from_fk_id;
299
300 l_col_name := 'AS_SALES_CREDITS_DENORM.address_id';
301
302 UPDATE AS_SALES_CREDITS_DENORM
303 set object_version_number = nvl(object_version_number,0) + 1, address_id = p_to_fk_id,
304 last_update_date = hz_utility_pub.last_update_date,
305 last_updated_by = hz_utility_pub.user_id,
306 last_update_login = hz_utility_pub.last_update_login,
307 request_id = hz_utility_pub.request_id,
308 program_application_id = hz_utility_pub.program_application_id,
309 program_id = hz_utility_pub.program_id,
310 program_update_date = sysdate
311 where address_id = p_from_fk_id;
312
313 -- add AS_LEADS_LOG.address_id
314
315 l_col_name := 'AS_LEADS_LOG.address_id';
316
317 UPDATE AS_LEADS_LOG
318 set object_version_number = nvl(object_version_number,0) + 1, address_id = p_to_fk_id
319 --last_update_date = hz_utility_pub.last_update_date,
320 --last_updated_by = hz_utility_pub.user_id,
321 --last_update_login = hz_utility_pub.last_update_login,
322 --request_id = hz_utility_pub.request_id,
323 --program_application_id = hz_utility_pub.program_application_id,
324 --program_id = hz_utility_pub.program_id,
325 --program_update_date = sysdate
326 where address_id = p_from_fk_id;
327
328 ELSIF p_parent_entity_name = 'HZ_ORG_CONTACTS' THEN
329
330
331 l_col_name := 'AS_LEAD_CONTACTS_ALL.contact_id';
332
333 UPDATE AS_LEAD_CONTACTS_ALL
334 set object_version_number = nvl(object_version_number,0) + 1, contact_id = p_to_fk_id,
335 last_update_date = hz_utility_pub.last_update_date,
336 last_updated_by = hz_utility_pub.user_id,
337 last_update_login = hz_utility_pub.last_update_login,
338 request_id = hz_utility_pub.request_id,
339 program_application_id = hz_utility_pub.program_application_id,
340 program_id = hz_utility_pub.program_id,
341 program_update_date = sysdate
342 where contact_id = p_from_fk_id;
343
344 ELSIF p_parent_entity_name = 'HZ_CONTACT_POINTS' THEN
345
346 l_col_name := 'AS_LEAD_CONTACTS_ALL.phone_id';
347
348 UPDATE AS_LEAD_CONTACTS_ALL
349 set object_version_number = nvl(object_version_number,0) + 1, phone_id = p_to_fk_id,
350 last_update_date = hz_utility_pub.last_update_date,
351 last_updated_by = hz_utility_pub.user_id,
352 last_update_login = hz_utility_pub.last_update_login,
353 request_id = hz_utility_pub.request_id,
354 program_application_id = hz_utility_pub.program_application_id,
355 program_id = hz_utility_pub.program_id,
356 program_update_date = sysdate
357 where phone_id = p_from_fk_id;
358
359
360 END IF;
361 EXCEPTION
362 WHEN OTHERS THEN
363 arp_message.set_line(g_api_name || ': ' || l_col_name || ': ' || sqlerrm);
364 x_return_status := FND_API.G_RET_STS_ERROR;
365 raise;
366 END;
367 END IF;
368
369 FND_FILE.PUT_LINE(FND_FILE.LOG, 'AS_OPP_MERGE_PKG.OPP_MERGE end : ' ||
370 to_char(sysdate,'DD-MON-YYYY HH24:MI'));
371
372 END OPP_MERGE;
373
374 END AS_OPP_MERGE_PKG;