[Home] [Help]
PACKAGE BODY: APPS.HZ_MERGE_UTIL
Source
1 PACKAGE BODY HZ_MERGE_UTIL AS
2 /* $Header: ARHMUTLB.pls 120.17 2006/02/13 10:06:46 rarajend noship $ */
3
4 PROCEDURE insert_party_site_details (
5 p_from_party_id IN NUMBER,
6 p_to_party_id IN NUMBER,
7 p_batch_party_id IN NUMBER,
8 p_CREATED_BY NUMBER,
9 p_CREATION_DATE DATE,
10 p_LAST_UPDATE_LOGIN NUMBER,
11 p_LAST_UPDATE_DATE DATE,
12 p_LAST_UPDATED_BY NUMBER) IS
13
14 CURSOR c_from_ps_loc IS
15 SELECT party_site_id, location_id FROM HZ_PARTY_SITES
16 WHERE party_id = p_from_party_id
17 AND nvl(status, 'A') = 'A'
18 AND actual_content_source <> 'DNB';
19
20 CURSOR c_dup_to_ps(cp_loc_id NUMBER) IS
21 SELECT party_site_id FROM HZ_PARTY_SITES
22 WHERE party_id = p_to_party_id
23 AND location_id = cp_loc_id
24 AND nvl(status, 'A') = 'A';
25
26 l_ps_id NUMBER;
27 l_loc_id NUMBER;
28 l_dup_ps_id NUMBER;
29 l_sqerr VARCHAR2(2000);
30 BEGIN
31
32 OPEN c_from_ps_loc;
33 LOOP
34 FETCH c_from_ps_loc INTO l_ps_id, l_loc_id;
35 EXIT WHEN c_from_ps_loc%NOTFOUND;
36 IF p_from_party_id <> p_to_party_id THEN
37 OPEN c_dup_to_ps(l_loc_id);
38 FETCH c_dup_to_ps INTO l_dup_ps_id;
39 IF c_dup_to_ps%FOUND THEN
40 HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
41 p_batch_party_id,
42 'HZ_PARTY_SITES',
43 l_ps_id,
44 l_dup_ps_id,
45 'Y',
46 p_created_by,
47 p_creation_Date,
48 p_last_update_login,
49 p_last_update_date,
50 p_last_updated_by);
51 ELSE
52 HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
53 p_batch_party_id,
54 'HZ_PARTY_SITES',
55 l_ps_id,
56 l_ps_id,
57 'N',
58 p_created_by,
59 p_creation_Date,
60 p_last_update_login,
61 p_last_update_date,
62 p_last_updated_by);
63 END IF;
64 CLOSE c_dup_to_ps;
65 END IF;
66 END LOOP;
67 CLOSE c_from_ps_loc;
68 EXCEPTION
69 WHEN OTHERS THEN
70 FND_MESSAGE.SET_NAME('AR','HZ_FORM_DUP_PROC_ERROR');
71 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
72 APP_EXCEPTION.RAISE_EXCEPTION;
73 END insert_party_site_details;
74
75 PROCEDURE insert_party_reln_details (
76 p_from_party_id IN NUMBER,
77 p_to_party_id IN NUMBER,
78 p_batch_party_id IN NUMBER,
79 p_CREATED_BY IN NUMBER,
80 p_CREATION_DATE IN DATE,
81 p_LAST_UPDATE_LOGIN IN NUMBER,
82 p_LAST_UPDATE_DATE IN DATE,
83 p_LAST_UPDATED_BY IN NUMBER
84 ) IS
85
86 CURSOR c_from_reln(l_batch_id NUMBER) IS
87 SELECT relationship_id, subject_id, object_id,
88 relationship_code, actual_content_source, start_date, nvl(end_date,to_date('12/31/4712','MM/DD/YYYY'))
89 FROM HZ_RELATIONSHIPS r
90 WHERE (subject_id = p_from_party_id
91 OR object_id = p_from_party_id)
92 AND nvl(status, 'A') IN ('A','I')
93 AND directional_flag = 'F'
94 AND subject_table_name = 'HZ_PARTIES'
95 AND object_table_name = 'HZ_PARTIES'
96 AND actual_content_source <> 'DNB';
97
98 CURSOR c_dup_sub_reln(
99 cp_party_rel_code VARCHAR2, cp_obj_id NUMBER,
100 cp_subj_id NUMBER, from_start_date date, from_end_date date,p_self_rel varchar2) --Bug No: 4609894
101 IS
102 SELECT relationship_id, start_date, nvl(end_date,to_date('12/31/4712','MM/DD/YYYY'))
103 FROM HZ_RELATIONSHIPS
104 WHERE ( (subject_id = cp_subj_id AND object_id = cp_obj_id)
105 OR (p_self_rel ='Y' and ( (subject_id = cp_subj_id AND object_id = cp_subj_id) -- (in case of P1-Supplier-P1 ,P2-Supplier-P2 and Merge P1 into P2)
106 OR (object_id = cp_subj_id AND subject_id = cp_obj_id) -- (in case of P1-Supplier-P1(Forward) , P2-Customer-P1(Forward) and Merge P1 into P2)
107 OR (subject_id = cp_subj_id AND object_id = p_from_party_id ) -- (in case of P1-Supplier-P2 ,P2-Supplier-P1 and Merge P1 into P2)
108 )
109 ) --Bug No: 4609894
110 )
111 AND relationship_code = cp_party_rel_code
112 --OR exists (select 1 from hz_relationship_types where relationship_type = cp_party_relationship_type
113 --and forward_code=backward_code))
114 AND ((start_date between from_start_date and from_end_date)
115 or (nvl(end_date,to_date('12/31/4712','MM/DD/YYYY')) between from_start_date and from_end_date)
116 or(start_date<from_start_date and nvl(end_date,to_date('12/31/4712','MM/DD/YYYY'))>from_end_date))
117 AND nvl(status, 'A') IN ('A','I')
118 AND subject_table_name = 'HZ_PARTIES'
119 AND object_table_name = 'HZ_PARTIES'
120 AND actual_content_source <> 'DNB';
121
122 --bug 4867151 start
123 CURSOR c_self_reln(rel_id NUMBER, bat_id NUMBER, to_id NUMBER) IS
124 select 'Y' from hz_relationships where relationship_id=rel_id
125 and (subject_id IN (p_from_party_id,p_to_party_id))
126 and (object_id IN (p_from_party_id,p_to_party_id))
127 AND directional_flag='F';
128 --bug 4867151 end
129
130
131 /* Commented out for BugNo:2940087 */
132 /*CURSOR c_dup_ob_reln(cp_party_relationship_type VARCHAR2, cp_subj_id NUMBER) IS
133 SELECT relationship_id
134 FROM HZ_RELATIONSHIPS
135 WHERE object_id = p_to_party_id
136 AND subject_id = cp_subj_id
137 AND relationship_code = cp_party_relationship_type
138 AND directional_flag = 'F'
139 AND subject_table_name = 'HZ_PARTIES'
140 AND object_table_name = 'HZ_PARTIES'
141 AND nvl(status, 'A') = 'A';
142 */
143
144 l_pr_id NUMBER;
145 l_dup_pr_id NUMBER;
146 l_dup_start_date HZ_RELATIONSHIPS.start_date%TYPE;
147 l_dup_end_date HZ_RELATIONSHIPS.end_date%TYPE;
148
149 l_subj_id NUMBER;
150 l_obj_id NUMBER;
151 l_reltype HZ_RELATIONSHIPS.relationship_code%TYPE;
152 l_relcode HZ_RELATIONSHIPS.relationship_code%TYPE;
153 l_contype HZ_RELATIONSHIPS.actual_content_source%TYPE;
154 l_start_date HZ_RELATIONSHIPS.start_date%TYPE;
155 l_end_date HZ_RELATIONSHIPS.end_date%TYPE;
156
157 l_batch_id NUMBER;
158 l_batch_party_id NUMBER;
159 l_mandatory_merge VARCHAR2(1);
160 l_self_rel varchar2(1); --Bug No: 4609894
161 l_temp_flag varchar2(1);--bug 4867151
162
163 BEGIN
164
165 IF p_from_party_id <> p_to_party_id THEN
166 SELECT batch_id INTO l_batch_id
167 FROM HZ_MERGE_PARTIES
168 WHERE batch_party_id = p_batch_party_id;
169
170 OPEN c_from_reln(l_batch_id);
171 LOOP
172 l_dup_pr_id := -1;
173
174 FETCH c_from_reln INTO l_pr_id, l_subj_id, l_obj_id, l_relcode,
175 l_contype, l_start_date, l_end_date;
176 EXIT WHEN c_from_reln%NOTFOUND;
177
178 IF l_contype <> 'DNB' THEN
179 l_self_rel := 'N'; --Bug No: 4609894
180 --if the from party is the subject in reln.
181 IF l_subj_id=p_from_party_id THEN
182 --Start of Bug No: 4609894
183 if(l_subj_id = l_obj_id OR l_obj_id = p_to_party_id) then
184 l_self_rel := 'Y';
185 end if;
186 --End of Bug No: 4609894
187 OPEN c_dup_sub_reln(l_relcode, l_obj_id, p_to_party_id, l_start_date, l_end_date,l_self_rel); --Bug No: 4609894
188 FETCH c_dup_sub_reln INTO l_dup_pr_id,l_dup_start_date,l_dup_end_date;
189 IF c_dup_sub_reln%NOTFOUND THEN
190 l_dup_pr_id := -1;
191 END IF;
192 CLOSE c_dup_sub_reln;
193
194 ELSIF l_obj_id=p_from_party_id THEN
195 -- Always pass 'N' for p_self as the l_subj_id and p_from_party_id will be same for self relationships.
196 OPEN c_dup_sub_reln(l_relcode, p_to_party_id, l_subj_id, l_start_date, l_end_date,'N'); --Bug No: 4609894
197 FETCH c_dup_sub_reln INTO l_dup_pr_id,l_dup_start_date,l_dup_end_date;
198 IF c_dup_sub_reln%NOTFOUND THEN
199 --Transfer
200 l_dup_pr_id := -1;
201 END IF;
202 CLOSE c_dup_sub_reln;
203
204 END IF;
205 END IF;
206 --bug 4867151 start
207 l_temp_flag := 'N';
208 OPEN c_self_reln(l_pr_id, l_batch_id, p_to_party_id);
209 FETCH c_self_reln INTO l_temp_flag;
210 CLOSE c_self_reln;
211 --bug 4867151 end
212
213 IF l_temp_flag<>'Y' THEN --bug 4867151
214
215 IF l_dup_pr_id <> -1 THEN
216 HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
217 p_batch_party_id,
218 'HZ_PARTY_RELATIONSHIPS',
219 l_pr_id,
220 l_dup_pr_id,
221 'Y',
222 p_created_by,
223 p_creation_Date,
224 p_last_update_login,
225 p_last_update_date,
226 p_last_updated_by);
227 ELSE
228 HZ_MERGE_PARTY_DETAILS_PKG.Insert_Row(
229 p_batch_party_id,
230 'HZ_PARTY_RELATIONSHIPS',
231 l_pr_id,
232 l_pr_id,
233 'N',
234 p_created_by,
235 p_creation_Date,
236 p_last_update_login,
237 p_last_update_date,
238 p_last_updated_by);
239 END IF;
240 END IF;--l_temp_flag-- bug 4867151
241 END LOOP;
242 CLOSE c_from_reln;
243 END IF;
244 EXCEPTION
245 WHEN FND_API.G_EXC_ERROR THEN
246 --APP_EXCEPTION.RAISE_EXCEPTION;
247 RAISE;
248 WHEN OTHERS THEN
249 FND_MESSAGE.SET_NAME('AR','HZ_FORM_DUP_PROC_ERROR');
250 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
251 APP_EXCEPTION.RAISE_EXCEPTION;
252 END insert_party_reln_details;
253
254 FUNCTION get_party_site_description(
255 p_party_site_id IN NUMBER)
256 RETURN VARCHAR2 IS
257
258 CURSOR c_get_desc IS
259 SELECT nvl(ps.party_site_name, ps.party_site_number) || '(Address: ' ||
260 l.address1 || ',' ||l.address2||','||l.address3||','||l.address4||','|| l.city ||
261 ',' || l.county || ','|| l.state || ', ' || l.province || ','||l.postal_code || ',' || l.country||')'
262 FROM HZ_PARTY_SITES ps, HZ_LOCATIONS l
263 WHERE ps.party_site_id = p_party_site_id
264 AND ps.location_id = l.location_id;
265
266 l_ps_address VARCHAR2(2000);
267
268 BEGIN
269
270 OPEN c_get_desc;
271 FETCH c_get_desc INTO l_ps_address;
272 IF c_get_desc%NOTFOUND THEN
273 CLOSE c_get_desc;
274 l_ps_address := to_char(p_party_site_id);
275 END IF;
276 CLOSE c_get_desc;
277
278 RETURN l_ps_address;
279
280 EXCEPTION
281 WHEN OTHERS THEN
282 RETURN to_char(p_party_site_id);
283 END get_party_site_description;
284
285 FUNCTION get_party_reln_description(
286 p_party_reln_id IN NUMBER)
287 RETURN VARCHAR2 IS
288
289 CURSOR c_get_desc IS
290 SELECT '"'||p1.party_name || '"->"' || p2.party_name||'"'
291 FROM HZ_RELATIONSHIPS pr, HZ_PARTIES p1, --4500011
292 HZ_PARTIES p2
293 WHERE p1.party_id = pr.object_id
294 AND p2.party_id = pr.subject_id
295 AND pr.relationship_id = p_party_reln_id
296 AND pr.subject_table_name = 'HZ_PARTIES'
297 AND pr.object_table_name = 'HZ_PARTIES'
298 AND pr.directional_flag = 'F';
299
300 l_pr_desc VARCHAR2(2000);
301
302 BEGIN
303
304 OPEN c_get_desc;
305 FETCH c_get_desc INTO l_pr_desc;
306 IF c_get_desc%NOTFOUND THEN
307 CLOSE c_get_desc;
308 l_pr_desc := to_char(p_party_reln_id);
309 END IF;
310 CLOSE c_get_desc;
311
312 RETURN l_pr_desc;
313
314 EXCEPTION
315 WHEN OTHERS THEN
316 RETURN to_char(p_party_reln_id);
317 END get_party_reln_description;
318
319 FUNCTION get_org_contact_description(
320 p_org_contact_id IN NUMBER)
321 RETURN VARCHAR2 IS
322
323 CURSOR c_get_desc IS
324 SELECT p1.party_name
325 FROM HZ_RELATIONSHIPS pr, HZ_ORG_CONTACTS oc, --4500011
326 HZ_PARTIES p1
327 WHERE p1.party_id = pr.subject_id
328 AND oc.party_relationship_id = pr.relationship_id
329 AND oc.org_contact_id = p_org_contact_id
330 AND pr.subject_table_name = 'HZ_PARTIES'
331 AND pr.object_table_name = 'HZ_PARTIES'
332 AND pr.directional_flag = 'F';
333
334 l_oc_desc VARCHAR2(2000);
335
336 BEGIN
337
338 OPEN c_get_desc;
339 FETCH c_get_desc INTO l_oc_desc;
340 IF c_get_desc%NOTFOUND THEN
341 CLOSE c_get_desc;
342 l_oc_desc := to_char(p_org_contact_id);
343 END IF;
344 CLOSE c_get_desc;
345
346 RETURN l_oc_desc;
347
348 EXCEPTION
349 WHEN OTHERS THEN
350 RETURN to_char(p_org_contact_id);
351 END get_org_contact_description;
352
353 FUNCTION get_org_contact_id(
354 p_party_relationship_id IN NUMBER)
355 RETURN NUMBER IS
356
357 CURSOR org_cont IS
358 SELECT org_contact_id
359 FROM hz_org_contacts
360 WHERE party_relationship_id = p_party_relationship_id;
361
362 l_org_cont_id NUMBER;
363
364 BEGIN
365 OPEN org_cont;
366 FETCH org_cont INTO l_org_cont_id;
367 IF org_cont%FOUND THEN
368 CLOSE org_cont;
369 RETURN l_org_cont_id;
370 ELSE
371 CLOSE org_cont;
372 RETURN NULL;
373 END IF;
374 END;
375
376 FUNCTION get_reln_party_id(
377 p_party_relationship_id IN NUMBER)
378 RETURN NUMBER IS
379
380 CURSOR reln_party IS
381 SELECT party_id
382 FROM hz_relationships --4500011
383 WHERE relationship_id = p_party_relationship_id
384 AND subject_table_name = 'HZ_PARTIES'
385 AND object_table_name = 'HZ_PARTIES'
386 AND directional_flag = 'F';
387
388 l_party_id NUMBER;
389
390 BEGIN
391 OPEN reln_party;
392 FETCH reln_party INTO l_party_id;
393 IF reln_party%FOUND THEN
394 CLOSE reln_party;
395 RETURN l_party_id;
396 ELSE
397 CLOSE reln_party;
398 RETURN NULL;
399 END IF;
400 END;
401 END HZ_MERGE_UTIL;