1 PACKAGE BODY arh_dqm_tree_helper AS
2 /*$Header: ARHDQMBB.pls 120.1 2005/06/16 21:10:54 jhuang noship $*/
3
4 FUNCTION ctxmax
5 RETURN NUMBER
6 IS
7 CURSOR c1 IS
8 SELECT MAX(search_context_id)
9 FROM hz_matched_parties_gt;
10 lret NUMBER;
11 BEGIN
12 OPEN c1;
13 FETCH c1 INTO lret;
14 IF c1%NOTFOUND THEN
15 lret := 0;
16 END IF;
17 CLOSE c1;
18 RETURN lret;
19 END;
20
21 PROCEDURE insert_add_party_site
22 (p_party_id IN NUMBER,
23 p_ctx_id IN NUMBER,
24 p_ps_id IN NUMBER)
25 IS
26 BEGIN
27 IF party_site_in_match (p_party_id , p_ctx_id , p_ps_id) = 'N' THEN
28 INSERT INTO hz_matched_party_sites_gt
29 (PARTY_ID ,PARTY_SITE_ID,SEARCH_CONTEXT_ID ,SCORE) VALUES
30 (p_party_id,p_ps_id ,p_ctx_id, 0);
31 END IF;
32 END;
33
34 PROCEDURE insert_add_contact
35 (p_party_id IN NUMBER,
36 p_ctx_id IN NUMBER,
37 p_contact_id IN NUMBER)
38 IS
39 BEGIN
40 IF contact_in_match (p_party_id , p_ctx_id , p_contact_id) = 'N' THEN
41 INSERT INTO hz_matched_contacts_gt
42 (PARTY_ID , ORG_CONTACT_ID, SEARCH_CONTEXT_ID, SCORE ) VALUES
43 (p_party_id, p_contact_id ,p_ctx_id, 0);
44 END IF;
45 END;
46
47 PROCEDURE insert_add_cpt
48 (p_party_id IN NUMBER,
49 p_ctx_id IN NUMBER,
50 p_cpt_id IN NUMBER)
51 IS
52 BEGIN
53 IF cpt_in_match (p_party_id , p_ctx_id , p_cpt_id) = 'N' THEN
54 INSERT INTO hz_matched_cpts_gt
55 (PARTY_ID , CONTACT_POINT_ID, SEARCH_CONTEXT_ID , SCORE) VALUES
56 (p_party_id, p_cpt_id , p_ctx_id, 0);
57 END IF;
58 END;
59
60 FUNCTION is_pty_object_of_rel
61 (p_party_id IN NUMBER,
62 p_rel_id IN NUMBER)
63 RETURN VARCHAR2
64 IS
65 CURSOR c1 IS
66 SELECT 'Y'
67 FROM hz_relationships
68 WHERE relationship_id = p_rel_id
69 AND object_table_name = 'HZ_PARTIES'
70 AND object_id = p_party_id
71 AND directional_flag = 'F';
72 ret VARCHAR2(1);
73 BEGIN
74 OPEN c1;
75 FETCH c1 INTO ret;
76 IF c1%NOTFOUND THEN
77 ret := 'N';
78 END IF;
79 CLOSE c1;
80 RETURN ret;
81 END;
82
83 FUNCTION is_site_of_pty
84 (p_party_id IN NUMBER,
85 p_ps_id IN NUMBER)
86 RETURN VARCHAR2
87 IS
88 CURSOR c1 IS
89 SELECT 'Y'
90 FROM hz_party_sites
91 WHERE party_site_id = p_ps_id
92 AND party_id = p_party_id;
93 ret VARCHAR2(1);
94 BEGIN
95 OPEN c1;
96 FETCH c1 INTO ret;
97 IF c1%NOTFOUND THEN
98 ret := 'N';
99 END IF;
100 CLOSE c1;
101 RETURN ret;
102 END;
103
104
105 FUNCTION cpt_in_match
106 ---------------------------------------------------
107 -- Return Y if p_cpt_id found in HZ_MATCHED_CPTS_GT
108 -- N otherwise
109 ---------------------------------------------------
110 (p_party_id IN NUMBER,
111 p_ctx_id IN NUMBER,
112 p_cpt_id IN NUMBER)
113 RETURN VARCHAR2
114 IS
115 CURSOR c_exist IS
116 SELECT 'Y'
117 FROM hz_matched_cpts_gt
118 WHERE party_id = p_party_id
119 AND search_context_id = p_ctx_id
120 AND contact_point_id = p_cpt_id;
121 ret VARCHAR2(1) := 'N';
122 BEGIN
123 OPEN c_exist;
124 FETCH c_exist INTO ret;
125 IF c_exist%NOTFOUND THEN
126 ret := 'N';
127 END IF;
128 CLOSE c_exist;
129 RETURN ret;
130 END;
131
132 FUNCTION contact_in_match
133 -------------------------------------------------------
134 -- Return Y if p_cpt_id found in HZ_MATCHED_CONTACTS_GT
135 -- N otherwise
136 -------------------------------------------------------
137 (p_party_id IN NUMBER,
138 p_ctx_id IN NUMBER,
139 p_contact_id IN NUMBER)
140 RETURN VARCHAR2
141 IS
142 CURSOR c_exist IS
143 SELECT 'Y'
144 FROM hz_matched_contacts_gt
145 WHERE party_id = p_party_id
146 AND search_context_id = p_ctx_id
147 AND org_contact_id = p_contact_id;
148 ret VARCHAR2(1) := 'N';
149 BEGIN
150 OPEN c_exist;
151 FETCH c_exist INTO ret;
152 IF c_exist%NOTFOUND THEN
153 ret := 'N';
154 END IF;
155 CLOSE c_exist;
156 RETURN ret;
157 END;
158
159 FUNCTION party_site_in_match
160 ----------------------------------------------------------
161 -- Return Y if p_cpt_id found in HZ_MATCHED_PARTY_SITES_GT
162 -- N otherwise
163 ----------------------------------------------------------
164 (p_party_id IN NUMBER,
165 p_ctx_id IN NUMBER,
166 p_ps_id IN NUMBER)
167 RETURN VARCHAR2
168 IS
169 CURSOR c_exist IS
170 SELECT 'Y'
171 FROM hz_matched_party_sites_gt
172 WHERE party_id = p_party_id
173 AND search_context_id = p_ctx_id
174 AND party_site_id = p_ps_id;
175 ret VARCHAR2(1) := 'N';
176 BEGIN
177 OPEN c_exist;
178 FETCH c_exist INTO ret;
179 IF c_exist%NOTFOUND THEN
180 ret := 'N';
181 END IF;
182 CLOSE c_exist;
183 RETURN ret;
184 END;
185
186
187 FUNCTION party_in_match
188 --------------------------------------------------------
189 -- Return Y if p_party_id found in HZ_MATCHED_PARTIES_GT
190 -- N otherwise
191 --------------------------------------------------------
192 (p_party_id IN NUMBER,
193 p_ctx_id IN NUMBER)
194 RETURN VARCHAR2
195 IS
196 CURSOR c_exist IS
197 SELECT 'Y'
198 FROM hz_matched_parties_gt
199 WHERE party_id = p_party_id
200 AND search_context_id = p_ctx_id;
201 ret VARCHAR2(1) := 'N';
202 BEGIN
203 OPEN c_exist;
204 FETCH c_exist INTO ret;
205 IF c_exist%NOTFOUND THEN
206 ret := 'N';
207 END IF;
208 CLOSE c_exist;
209 RETURN ret;
210 END;
211
212 FUNCTION contact_id_from_rel_id
213 -------------------------------------------------------
214 -- RETURN org_contact_id from a relationship_id
215 -- + x_party_site_id will return the site_id if the contact is at the site level
216 -- I x_party_site_id = -9999 otherwise
217 -- RETURN -9999 if the org_contact_id coud not be found
218 -------------------------------------------------------
219 ( p_rel_id IN NUMBER ,
220 x_ps_id IN OUT NOCOPY NUMBER )
221 RETURN NUMBER
222 IS
223 CURSOR c_org_contact IS
224 SELECT org_contact_id,
225 party_site_id
226 FROM hz_org_contacts
227 WHERE party_relationship_id = p_rel_id;
228 ret NUMBER;
229 BEGIN
230 OPEN c_org_contact;
231 FETCH c_org_contact INTO ret, x_ps_id;
232 IF c_org_contact%NOTFOUND THEN
233 ret := -9999;
234 x_ps_id := -9999;
235 ELSIF x_ps_id IS NULL THEN
236 x_ps_id := -9999;
237 END IF;
238 CLOSE c_org_contact;
239 RETURN ret;
240 END;
241
242 FUNCTION rel_id_betw_per_to_org
243 ----------------------------------------------------------------------
244 -- RETURN Relationship_id if the per_id is in relation with a party_id
245 -- + x_rel_code contains REL_CODE
246 -- RETURN -9999 otherwise
247 ----------------------------------------------------------------------
248 ( p_party_id IN NUMBER,
249 p_pers_id IN NUMBER,
250 x_rel_code IN OUT NOCOPY VARCHAR2)
251 RETURN NUMBER
252 IS
253 CURSOR c_exist IS
254 SELECT relationship_id,
255 relationship_code
256 FROM hz_relationships
257 WHERE subject_type = 'PERSON'
258 AND subject_table_name= 'HZ_PARTIES'
259 AND subject_id = p_pers_id
260 AND object_type = 'ORGANIZATION'
261 AND object_table_name = 'HZ_PARTIES'
262 AND object_id = p_party_id
263 AND directional_flag = 'F';
264 ret NUMBER := -9999;
265 BEGIN
266 OPEN c_exist;
267 FETCH c_exist INTO ret, x_rel_code;
268 IF c_exist%NOTFOUND THEN
269 ret := -9999;
270 END IF;
271 CLOSE c_exist;
272 RETURN ret;
273 END;
274
275 FUNCTION party_type
276 (p_party_id IN NUMBER)
277 RETURN VARCHAR2 IS
278 CURSOR c_type IS
279 SELECT party_type
280 FROM hz_parties
281 WHERE party_id = p_party_id;
282 ret VARCHAR2(30);
283 BEGIN
284 OPEN c_type;
285 FETCH c_type INTO ret;
286 CLOSE c_type;
287 RETURN ret;
288 END;
289
290 FUNCTION party_or_site_from_cpt
291 ---------------------------------------------------------------------------------------
292 -- RETURN party_site_id if contact_point at PS
293 -- + flag x_type to 'HZ_PARTY_SITES'
294 -- RETURN party_id if contact_point at HZ_PARTIES
295 -- + flag x_type to 'ORGANIZATION' if the party is type 'ORGANIZATION'
296 -- flag x_type to 'PARTY_RELATIONSHIP' if the party is type 'PARTY_RELATIONSHIP'
297 -- RETURN -9999 if contact_point not found
298 ---------------------------------------------------------------------------------------
299 (p_contact_point_id IN NUMBER,
300 x_type IN OUT NOCOPY VARCHAR2)
301 RETURN NUMBER
302 IS
303 CURSOR c_contact_point IS
304 SELECT owner_table_name,
305 owner_table_id
306 FROM hz_contact_points
307 WHERE contact_point_id = p_contact_point_id;
308 ret NUMBER;
309 BEGIN
310 OPEN c_contact_point;
311 FETCH c_contact_point INTO x_type, ret;
312 IF c_contact_point%NOTFOUND THEN
313 ret := -9999;
314 IF x_type = 'HZ_PARTIES' THEN
315 x_type := party_type(ret);
316 END IF;
317 END IF;
318 CLOSE c_contact_point;
319 RETURN ret;
320 END;
321
322 PROCEDURE relationship_treatment
323 (p_rel_id IN NUMBER,
324 p_pty_id IN NUMBER,
325 p_ctx_id IN NUMBER,
326 x_return_status OUT NOCOPY VARCHAR2,
327 x_msg_count OUT NOCOPY NUMBER,
328 x_msg_data OUT NOCOPY VARCHAR2)
329 IS
330 l_org_contact_id NUMBER;
331 l_ps_id NUMBER;
332 pty_out_rel EXCEPTION;
333 no_contact_for_rel EXCEPTION;
334 BEGIN
335 IF is_pty_object_of_rel(p_pty_id, p_rel_id) = 'N' THEN
336 RAISE pty_out_rel;
337 END IF;
338 l_org_contact_id := contact_id_from_rel_id( p_rel_id => p_rel_id,
339 x_ps_id => l_ps_id );
340 IF l_org_contact_id = -9999 THEN
341 RAISE no_contact_for_rel;
342 END IF;
343
344 insert_add_contact(p_party_id => p_pty_id,
345 p_ctx_id => p_ctx_id,
346 p_contact_id=> l_org_contact_id);
347
348 IF l_ps_id <> -9999 THEN
349 insert_add_party_site(p_party_id => p_pty_id,
350 p_ctx_id => p_ctx_id,
351 p_ps_id => l_ps_id);
352 END IF;
353 EXCEPTION
354 WHEN pty_out_rel THEN
355 fnd_message.set_name('AR', 'HZ_API_ERROR_PTY_OUT_REL');
356 fnd_message.set_token('PARTY_ID',TO_CHAR(p_pty_id));
357 fnd_message.set_token('RELATIONSHIP_ID',TO_CHAR(p_rel_id));
358 fnd_msg_pub.add;
359 x_return_status := fnd_api.G_RET_STS_ERROR;
360 fnd_msg_pub.Count_And_Get(
361 p_encoded => FND_API.G_FALSE,
362 p_count => x_msg_count,
363 p_data => x_msg_data);
364 WHEN no_contact_for_rel THEN
365 fnd_message.set_name('AR', 'HZ_API_ERROR_NO_CT_REL');
366 fnd_message.set_token('RELATIONSHIP_ID',TO_CHAR(p_rel_id));
367 fnd_msg_pub.add;
368 x_return_status := fnd_api.G_RET_STS_ERROR;
369 fnd_msg_pub.Count_And_Get(
370 p_encoded => FND_API.G_FALSE,
371 p_count => x_msg_count,
372 p_data => x_msg_data);
373 END;
374
375 PROCEDURE treatment_party_site
376 (p_ps_id IN NUMBER,
377 p_pty_id IN NUMBER,
378 p_ctx_id IN NUMBER,
379 x_return_status OUT NOCOPY VARCHAR2,
380 x_msg_count OUT NOCOPY NUMBER,
381 x_msg_data OUT NOCOPY VARCHAR2)
382 IS
383 site_not_for_pty EXCEPTION;
384 BEGIN
385 IF is_site_of_pty (p_pty_id , p_ps_id ) = 'N' THEN
386 RAISE site_not_for_pty;
387 END IF;
388 insert_add_party_site(p_party_id => p_pty_id,
389 p_ctx_id => p_ctx_id,
390 p_ps_id => p_ps_id);
391 EXCEPTION
392 WHEN site_not_for_pty THEN
393 fnd_message.set_name('AR', 'HZ_API_ERROR_SITE_NOT_PTY');
394 fnd_message.set_token('PARTY_ID',TO_CHAR(p_pty_id));
395 fnd_message.set_token('PARTY_SITE_ID',TO_CHAR(p_ps_id));
396 fnd_msg_pub.add;
397 x_return_status := fnd_api.G_RET_STS_ERROR;
398 fnd_msg_pub.Count_And_Get(
399 p_encoded => FND_API.G_FALSE,
400 p_count => x_msg_count,
401 p_data => x_msg_data);
402 END;
403
404 PROCEDURE cpt_treatment
405 (p_cpt_id IN NUMBER,
406 p_pty_id IN NUMBER,
407 p_ctx_id IN NUMBER,
408 x_return_status OUT NOCOPY VARCHAR2,
409 x_msg_count OUT NOCOPY NUMBER,
410 x_msg_data OUT NOCOPY VARCHAR2)
411 IS
412 l_id NUMBER;
413 l_rel_id NUMBER;
414 l_rel_code VARCHAR2(30);
415 l_type VARCHAR2(30);
416 cpt_pb EXCEPTION;
417 rel_pb EXCEPTION;
418 BEGIN
419 l_id := party_or_site_from_cpt(p_contact_point_id => p_cpt_id,
420 x_type => l_type);
421 IF l_id = -9999 THEN
422 RAISE cpt_pb;
423 END IF;
424
425 IF l_type IN ('PERSON', 'ORGANIZATION') THEN
426
427 IF l_id = p_pty_id THEN
428 NULL;
429 ELSE
430 l_rel_id := rel_id_betw_per_to_org( p_party_id => p_pty_id,
431 p_pers_id => l_id,
432 x_rel_code => l_rel_code);
433 IF l_rel_id = -9999 THEN
434 RAISE rel_pb;
435 END IF;
436 relationship_treatment(p_rel_id => l_rel_id,
437 p_pty_id => p_pty_id,
438 p_ctx_id => p_ctx_id,
442
439 x_return_status=> x_return_status,
440 x_msg_count=> x_msg_count,
441 x_msg_data => x_msg_data);
443 END IF;
444
445 ELSIF l_type = 'PARTY_RELATIONSHIP' THEN
446 relationship_treatment(p_rel_id => l_rel_id,
447 p_pty_id => p_pty_id,
448 p_ctx_id => p_ctx_id,
449 x_return_status=> x_return_status,
450 x_msg_count=> x_msg_count,
451 x_msg_data => x_msg_data);
452
453 ELSIF l_type = 'HZ_PARTY_SITES' THEN
454 treatment_party_site(p_ps_id => l_id,
455 p_pty_id => p_pty_id,
456 p_ctx_id => p_ctx_id,
457 x_return_status=> x_return_status,
458 x_msg_count=> x_msg_count,
459 x_msg_data => x_msg_data);
460
461 END IF;
462 EXCEPTION
463 WHEN cpt_pb THEN
464 fnd_message.set_name('AR', 'HZ_API_ERROR_CPT');
465 fnd_message.set_token('CONTACT_POINT_ID',TO_CHAR(p_cpt_id));
466 fnd_msg_pub.add;
467 x_return_status := fnd_api.G_RET_STS_ERROR;
468 fnd_msg_pub.Count_And_Get(
469 p_encoded => FND_API.G_FALSE,
470 p_count => x_msg_count,
471 p_data => x_msg_data);
472 WHEN rel_pb THEN
473 fnd_message.set_name('AR', 'HZ_API_ERROR_REL');
474 fnd_message.set_token('PARTY_ID',TO_CHAR(p_pty_id));
475 fnd_message.set_token('PARTY2_ID', TO_CHAR(l_id));
476 fnd_msg_pub.add;
477 x_return_status := fnd_api.G_RET_STS_ERROR;
478 fnd_msg_pub.Count_And_Get(
479 p_encoded => FND_API.G_FALSE,
480 p_count => x_msg_count,
481 p_data => x_msg_data);
482 END;
483
484 PROCEDURE contact_treatment
485 (p_contact_id IN NUMBER,
486 p_pty_id IN NUMBER,
487 p_ctx_id IN NUMBER,
488 x_return_status OUT NOCOPY VARCHAR2,
489 x_msg_count OUT NOCOPY NUMBER,
490 x_msg_data OUT NOCOPY VARCHAR2)
491 IS
492 CURSOR c1 IS
493 SELECT NVL(party_site_id,-9999)
494 FROM hz_org_contacts
495 WHERE org_contact_id = p_contact_id;
496 l_ps_id NUMBER;
497 BEGIN
498 OPEN c1;
499 FETCH c1 INTO l_ps_id;
500 IF c1%FOUND THEN
501 IF l_ps_id <> -9999 THEN
502 treatment_party_site(p_ps_id => l_ps_id,
503 p_pty_id => p_pty_id,
504 p_ctx_id => p_ctx_id,
505 x_return_status => x_return_status,
506 x_msg_count => x_msg_count,
507 x_msg_data => x_msg_data);
508
509 END IF;
510 END IF;
511 CLOSE c1;
512 END;
513
514 END;
[Home] [Help]