eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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;