1 PACKAGE BODY AS_SALES_LEADS_MERGE_PKG as
2 /* $Header: asxpmslb.pls 115.6 2003/02/11 22:27:59 solin ship $ */
3 --
4 -- NAME
5 -- AS_SALES_LEADS_MERGE_PKG
6 --
7 -- HISTORY
8 -- 02/20/2001 SOLIN CREATED
9 --
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):='AS_SALES_LEADS_MERGE_PKG';
12
13 PROCEDURE SALES_LEAD_MERGE
14 ( p_entity_name IN VARCHAR2
15 ,p_from_id IN NUMBER
16 ,p_to_id IN OUT NOCOPY 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 ,x_return_status IN OUT NOCOPY VARCHAR2
23 ) is
24 l_api_name CONSTANT VARCHAR2(30) := 'SALES_LEAD_MERGE';
25 l_api_version_number CONSTANT NUMBER := 2.0;
26 l_merge_reason_code VARCHAR2(30);
27 BEGIN
28 FND_FILE.PUT_LINE(FND_FILE.LOG,'AS_SALES_LEADS_MERGE_PKG.SALES_LEAD_MERGE starts : ' || to_char(sysdate,'DD-MON-YYYY HH24:MI'));
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 IF l_merge_reason_code = 'DUPLICATE' THEN
36 -- ***************************************************************************
37 -- if reason code is duplicate then allow the party merge to happen without
38 -- any validations.
39 -- ***************************************************************************
40 null;
41 ELSE
42 -- ***************************************************************************
43 -- if there are any validations to be done, include it in this section
44 -- ***************************************************************************
45 null;
46 END IF;
47
48 -- ***************************************************************************
49 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
50 -- needs to be done. Set Merged To Id is same as Merged From Id and return
51 -- ***************************************************************************
52 if p_from_fk_id = p_to_fk_id then
53 p_to_id := p_from_id;
54 return;
55 end if;
56
57 -- ***************************************************************************
58 -- If the parent has changed(ie. Parent is getting merged) then transfer the
59 -- dependent record to the new parent. Before transferring check if a similar
60 -- dependent record exists on the new parent. If a duplicate exists then do
61 -- not transfer and return the id of the duplicate record as the Merged To Id
62 -- ***************************************************************************
63
64 -- ***************************************************************************
65 -- Add your own logic if you need to take care of the following cases
66 -- Check the if record duplicate if change party_id from merge-from
67 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
68 -- situation
69 --
70 -- customer_id address_id contact_id
71 -- =========== ========== ==========
72 -- 1200 1100
73 -- 1300 1400
74 --
75 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
76 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
77 -- therefore, if changing 1200 to 1300 (customer_id)
78 -- and 1100 to 1400 (address_id), then it will cause unique
79 -- key violation assume that all other fields are the same
80 -- So, please check if you need to check for record duplication
81 -- ***************************************************************************
82
83 IF p_from_fk_id <> p_to_fk_id THEN
84 BEGIN
85 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
86 UPDATE AS_SALES_LEADS
87 set customer_id = p_to_fk_id,
88 last_update_date = hz_utility_pub.last_update_date,
89 last_updated_by = hz_utility_pub.user_id,
90 last_update_login = hz_utility_pub.last_update_login,
91 request_id = hz_utility_pub.request_id,
92 program_application_id = hz_utility_pub.program_application_id,
93 program_id = hz_utility_pub.program_id,
94 program_update_date = sysdate
95 where customer_id = p_from_fk_id;
96
97 UPDATE AS_SALES_LEADS
98 set incumbent_partner_party_id = p_to_fk_id,
99 last_update_date = hz_utility_pub.last_update_date,
100 last_updated_by = hz_utility_pub.user_id,
101 last_update_login = hz_utility_pub.last_update_login,
102 request_id = hz_utility_pub.request_id,
103 program_application_id = hz_utility_pub.program_application_id,
104 program_id = hz_utility_pub.program_id,
105 program_update_date = sysdate
106 where incumbent_partner_party_id = p_from_fk_id;
107
108 UPDATE AS_SALES_LEADS
109 set primary_contact_party_id = p_to_fk_id,
110 last_update_date = hz_utility_pub.last_update_date,
111 last_updated_by = hz_utility_pub.user_id,
112 last_update_login = hz_utility_pub.last_update_login,
113 request_id = hz_utility_pub.request_id,
114 program_application_id = hz_utility_pub.program_application_id,
115 program_id = hz_utility_pub.program_id,
116 program_update_date = sysdate
117 where primary_contact_party_id = p_from_fk_id;
118
119 UPDATE AS_SALES_LEADS
120 set primary_cnt_person_party_id = p_to_fk_id,
121 last_update_date = hz_utility_pub.last_update_date,
122 last_updated_by = hz_utility_pub.user_id,
123 last_update_login = hz_utility_pub.last_update_login,
124 request_id = hz_utility_pub.request_id,
125 program_application_id = hz_utility_pub.program_application_id,
126 program_id = hz_utility_pub.program_id,
127 program_update_date = sysdate
128 where primary_cnt_person_party_id = p_from_fk_id;
129
130 UPDATE AS_SALES_LEADS
131 set referred_by = p_to_fk_id,
132 last_update_date = hz_utility_pub.last_update_date,
133 last_updated_by = hz_utility_pub.user_id,
134 last_update_login = hz_utility_pub.last_update_login,
135 request_id = hz_utility_pub.request_id,
136 program_application_id = hz_utility_pub.program_application_id,
137 program_id = hz_utility_pub.program_id,
138 program_update_date = sysdate
139 where referred_by = p_from_fk_id;
140 ELSIF p_parent_entity_name = 'HZ_PARTY_SITES' THEN -- merge party_site
141 UPDATE AS_SALES_LEADS
142 set address_id = p_to_fk_id,
143 last_update_date = hz_utility_pub.last_update_date,
144 last_updated_by = hz_utility_pub.user_id,
145 last_update_login = hz_utility_pub.last_update_login,
146 request_id = hz_utility_pub.request_id,
147 program_application_id = hz_utility_pub.program_application_id,
148 program_id = hz_utility_pub.program_id,
149 program_update_date = sysdate
150 where address_id = p_from_fk_id;
151 ELSIF p_parent_entity_name = 'HZ_CONTACT_POINTS' THEN -- merge contact_points
152 UPDATE AS_SALES_LEADS
153 set primary_contact_phone_id = p_to_fk_id,
154 last_update_date = hz_utility_pub.last_update_date,
155 last_updated_by = hz_utility_pub.user_id,
156 last_update_login = hz_utility_pub.last_update_login,
157 request_id = hz_utility_pub.request_id,
158 program_application_id = hz_utility_pub.program_application_id,
159 program_id = hz_utility_pub.program_id,
160 program_update_date = sysdate
161 where primary_contact_phone_id = p_from_fk_id;
162 END IF;
163 EXCEPTION
164 WHEN OTHERS THEN
165 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
166 x_return_status := FND_API.G_RET_STS_ERROR;
167 raise;
168 END;
169 END IF;
170
171 FND_FILE.PUT_LINE(FND_FILE.LOG,'AS_SALES_LEADS_MERGE_PKG.SALES_LEAD_MERGE end : ' || to_char(sysdate,'DD-MON-YYYY HH24:MI'));
172
173 END SALES_LEAD_MERGE;
174
175 PROCEDURE LEAD_CONTACT_MERGE
176 ( p_entity_name IN VARCHAR2
177 ,p_from_id IN NUMBER
178 ,p_to_id IN OUT NOCOPY NUMBER
179 ,p_from_fk_id IN NUMBER
180 ,p_to_fk_id IN NUMBER
181 ,p_parent_entity_name IN VARCHAR2
182 ,p_batch_id IN NUMBER
183 ,p_batch_party_id IN NUMBER
184 ,x_return_status IN OUT NOCOPY VARCHAR2
185 ) is
186 l_api_name CONSTANT VARCHAR2(30) := 'LEAD_CONTACT_MERGE';
187 l_api_version_number CONSTANT NUMBER := 2.0;
188 l_merge_reason_code VARCHAR2(30);
189 BEGIN
190 FND_FILE.PUT_LINE(FND_FILE.LOG,'AS_SALES_LEADS_MERGE_PKG.LEAD_CONTACT_MERGE starts : ' || to_char(sysdate,'DD-MON-YYYY HH24:MI'));
191 x_return_status := FND_API.G_RET_STS_SUCCESS;
192
193 select merge_reason_code into l_merge_reason_code
194 from HZ_MERGE_BATCH
195 where batch_id = p_batch_id;
196
197 IF l_merge_reason_code = 'DUPLICATE' THEN
198 -- ***************************************************************************
199 -- if reason code is duplicate then allow the party merge to happen without
200 -- any validations.
201 -- ***************************************************************************
202 null;
203 ELSE
204 -- ***************************************************************************
205 -- if there are any validations to be done, include it in this section
206 -- ***************************************************************************
207 null;
208 END IF;
209
210 -- ***************************************************************************
211 -- If the parent has NOT changed (ie. Parent getting transferred) then nothing
212 -- needs to be done. Set Merged To Id is same as Merged From Id and return
213 -- ***************************************************************************
214 if p_from_fk_id = p_to_fk_id then
215 p_to_id := p_from_id;
216 return;
217 end if;
218
219 -- ***************************************************************************
220 -- If the parent has changed(ie. Parent is getting merged) then transfer the
221 -- dependent record to the new parent. Before transferring check if a similar
222 -- dependent record exists on the new parent. If a duplicate exists then do
223 -- not transfer and return the id of the duplicate record as the Merged To Id
224 -- ***************************************************************************
225
226 -- ***************************************************************************
227 -- Add your own logic if you need to take care of the following cases
228 -- Check the if record duplicate if change party_id from merge-from
229 -- to merge-to id. E.g. : in AS_ACCESSES_ALL, if you have the following
230 -- situation
231 --
232 -- customer_id address_id contact_id
233 -- =========== ========== ==========
234 -- 1200 1100
235 -- 1300 1400
236 --
237 -- if p_from_fk_id = 1200, p_to_fk_id = 1300 for customer_id
238 -- p_from_fk_id = 1100, p_to_fk_id = 1400 for address_id
239 -- therefore, if changing 1200 to 1300 (customer_id)
240 -- and 1100 to 1400 (address_id), then it will cause unique
241 -- key violation assume that all other fields are the same
242 -- So, please check if you need to check for record duplication
243 -- ***************************************************************************
244
245 IF p_from_fk_id <> p_to_fk_id THEN
246 BEGIN
247 IF p_parent_entity_name = 'HZ_PARTIES' THEN -- merge party
248 UPDATE AS_SALES_LEAD_CONTACTS
249 set customer_id = p_to_fk_id,
250 last_update_date = hz_utility_pub.last_update_date,
251 last_updated_by = hz_utility_pub.user_id,
252 last_update_login = hz_utility_pub.last_update_login,
253 request_id = hz_utility_pub.request_id,
254 program_application_id = hz_utility_pub.program_application_id,
255 program_id = hz_utility_pub.program_id,
256 program_update_date = sysdate
257 where customer_id = p_from_fk_id;
258
259 UPDATE AS_SALES_LEAD_CONTACTS
260 set contact_party_id = p_to_fk_id,
261 last_update_date = hz_utility_pub.last_update_date,
262 last_updated_by = hz_utility_pub.user_id,
263 last_update_login = hz_utility_pub.last_update_login,
264 request_id = hz_utility_pub.request_id,
265 program_application_id = hz_utility_pub.program_application_id,
266 program_id = hz_utility_pub.program_id,
267 program_update_date = sysdate
268 where contact_party_id = p_from_fk_id;
269 ELSIF p_parent_entity_name = 'HZ_PARTY_SITES' THEN -- merge party_site
270 UPDATE AS_SALES_LEAD_CONTACTS
271 set address_id = p_to_fk_id,
272 last_update_date = hz_utility_pub.last_update_date,
273 last_updated_by = hz_utility_pub.user_id,
274 last_update_login = hz_utility_pub.last_update_login,
275 request_id = hz_utility_pub.request_id,
276 program_application_id = hz_utility_pub.program_application_id,
277 program_id = hz_utility_pub.program_id,
278 program_update_date = sysdate
279 where address_id = p_from_fk_id;
280 ELSIF p_parent_entity_name = 'HZ_ORG_CONTACTS' THEN -- merge org_contact
281 UPDATE AS_SALES_LEAD_CONTACTS
282 set contact_id = p_to_fk_id,
283 last_update_date = hz_utility_pub.last_update_date,
284 last_updated_by = hz_utility_pub.user_id,
285 last_update_login = hz_utility_pub.last_update_login,
286 request_id = hz_utility_pub.request_id,
287 program_application_id = hz_utility_pub.program_application_id,
288 program_id = hz_utility_pub.program_id,
289 program_update_date = sysdate
290 where contact_id = p_from_fk_id;
291 ELSIF p_parent_entity_name = 'HZ_CONTACT_POINTS' THEN -- merge contact_points
292 UPDATE AS_SALES_LEAD_CONTACTS
293 set phone_id = p_to_fk_id,
294 last_update_date = hz_utility_pub.last_update_date,
295 last_updated_by = hz_utility_pub.user_id,
296 last_update_login = hz_utility_pub.last_update_login,
297 request_id = hz_utility_pub.request_id,
298 program_application_id = hz_utility_pub.program_application_id,
299 program_id = hz_utility_pub.program_id,
300 program_update_date = sysdate
301 where phone_id = p_from_fk_id;
302 END IF;
303 EXCEPTION
304 WHEN OTHERS THEN
305 arp_message.set_line(g_pkg_name || '.' || l_api_name || ': ' || sqlerrm);
306 x_return_status := FND_API.G_RET_STS_ERROR;
307 raise;
308 END;
309 END IF;
310
311 FND_FILE.PUT_LINE(FND_FILE.LOG,'AS_SALES_LEADS_MERGE_PKG.LEAD_CONTACT_MERGE end : ' || to_char(sysdate,'DD-MON-YYYY HH24:MI'));
312
313 END LEAD_CONTACT_MERGE;
314
315
316 END AS_SALES_LEADS_MERGE_PKG;
[Home] [Help]