[Home] [Help]
PACKAGE BODY: APPS.ARH_DQM_TREE
Source
1 PACKAGE BODY arh_dqm_tree AS
2 /*$Header: ARHDQMTB.pls 120.4 2005/10/30 04:19:14 appldev noship $*/
3
4 ico_person VARCHAR2(30) := 'afperson';
5 ico_party VARCHAR2(30) := 'inv_item';
6 ico_account VARCHAR2(30) := 'cscquicm';
7 ico_site VARCHAR2(30) := 'asaddr';
8 ico_email VARCHAR2(30) := 'afsend';
9 ico_phone VARCHAR2(30) := 'asphone';
10 ico_primary VARCHAR2(30) := 'afdispt';
11 ico_web VARCHAR2(30) := 'aftrans';
12 ico_tlx VARCHAR2(30) := 'cscscrip';
13
14
15
16 FUNCTION stypstr
17 ( l IN VARCHAR2)
18 RETURN VARCHAR2 IS
19 pos NUMBER;
20 ret VARCHAR2(2000);
21 BEGIN
22 pos := INSTR(l, '*$#*');
23 IF pos > 0 THEN
24 ret := SUBSTR(l, pos + 4);
25 ELSE
26 ret := l;
27 END IF;
28 RETURN ret;
29 END;
30
31 FUNCTION atypstr
32 ( l IN VARCHAR2,
33 typ IN VARCHAR2)
34 RETURN VARCHAR2 IS
35 BEGIN
36 RETURN typ||'*$#*'||l;
37 END;
38
39 FUNCTION typostr
40 ( l IN VARCHAR2)
41 RETURN VARCHAR2 IS
42 pos NUMBER;
43 ret VARCHAR2(2000);
44 BEGIN
45 pos := INSTR(l, '*$#*');
46 IF pos > 0 THEN
47 ret := SUBSTR(l,1,pos-1);
48 ELSE
49 ret := NULL;
50 END IF;
51 RETURN ret;
52 END;
53
54 FUNCTION r_score
55 ( p_score NUMBER,
56 p_disp_percent VARCHAR2 DEFAULT 'Y')
57 RETURN VARCHAR2
58 IS
59 ret VARCHAR2(30);
60 BEGIN
61 IF p_score > 0 THEN
62 IF p_disp_percent = 'Y' THEN
63 ret := ' ('||to_char(p_score)||'%)';
64 ELSE
65 ret := ' ('||to_char(p_score)||')';
66 END IF;
67 ELSIF p_score < 0 THEN
68 IF p_disp_percent = 'Y' THEN
69 ret := ' ('||to_char(-p_score)||'%)';
70 ELSE
71 ret := ' ('||to_char(-p_score)||')';
72 END IF;
73 ELSE
74 ret := '';
75 END IF;
76 RETURN ret;
77 END;
78
79 FUNCTION AddList
80 ( p_dsplist1 dsplist,
81 p_dsplist2 dsplist)
82 RETURN dsplist
83 IS
84 l_dsplist dsplist;
85 i NUMBER;
86 j NUMBER;
87 BEGIN
88 l_dsplist := erase_list;
89 i := p_dsplist1.COUNT;
90 j := p_dsplist2.COUNT;
91 IF i = 0 AND j = 0 THEN
92 RETURN l_dsplist;
93 ELSIF i = 0 THEN
94 l_dsplist := p_dsplist2;
95 ELSIF j = 0 THEN
96 l_dsplist := p_dsplist1;
97 ELSE
98 l_dsplist := p_dsplist1;
99 IF j > 0 THEN
100 FOR k IN 1..j LOOP
101 l_dsplist(i+k) := p_dsplist2(k);
102 END LOOP;
103 END IF;
104 END IF;
105 RETURN l_dsplist;
106 END;
107
108 FUNCTION dsplist_for_parties
109 (p_ctx_id IN NUMBER,
110 p_status IN VARCHAR2 DEFAULT 'ALL',
111 p_disp_percent IN VARCHAR2 DEFAULT 'Y')
112 RETURN dsplist
113 IS
114 CURSOR c_party_matched (i_ctx_id NUMBER, i_status IN VARCHAR2) IS
115 SELECT p.party_name,
116 p.party_number,
117 p.party_id,
118 s.score
119 FROM hz_matched_parties_gt s,
120 hz_parties p
121 WHERE s.party_id = p.party_id
122 AND s.search_context_id = i_ctx_id
123 AND NVL(s.score,0) >= 0
124 -- AND DECODE(i_status,'ALL','ALL',NVL(p.status,'A'))= i_status
125 ORDER BY s.score desc;
126 l_rec c_party_matched%ROWTYPE;
127 i NUMBER;
128 j NUMBER;
129 init_depth NUMBER;
130 init_ndata VARCHAR2(4000);
131 l_dsplist dsplist;
132 tp_dsplist dsplist;
133 resultlist dsplist;
134 -- for error usage
135 tmp_var VARCHAR2(2000);
136 tmp_var1 VARCHAR2(2000);
137 already_exp VARCHAR2(1) := 'N';
138 l_score VARCHAR2(30);
139 BEGIN
140 i := 0;
141 j := 0;
142 init_depth := 0;
143 init_ndata := NULL;
144
145 OPEN c_party_matched(p_ctx_id, p_status);
146 LOOP
147 FETCH c_party_matched INTO l_rec;
148 EXIT WHEN c_party_matched%NOTFOUND;
149 i := i + 1;
150 IF i <= 5 THEN
151 l_dsplist(i).state := 1;
152 ELSE
153 l_dsplist(i).state := -1;
154 END IF;
155 l_dsplist(i).depth := init_depth + 1;
156 l_dsplist(i).label := nvl(l_rec.party_name||r_score(l_rec.score,p_disp_percent),'NULL');
157 l_dsplist(i).icon := ico_party;
158 l_dsplist(i).ndata := atypstr('PARTY_ID:'||to_char(l_rec.party_id)||'@','PARTY');
159 resultlist(resultlist.COUNT + 1) := l_dsplist(i);
160
161 tp_dsplist := erase_list;
162 tp_dsplist := Add_site_party(p_ctx_id,
163 l_rec.party_id,
164 p_status,
165 l_dsplist(i));
166 IF tp_dsplist.COUNT > 0 THEN
167 resultlist := AddList( resultlist, tp_dsplist);
168 END IF;
169
170 tp_dsplist := erase_list;
171 tp_dsplist := Add_Contact_party(p_ctx_id,
172 l_rec.party_id,
173 p_status,
174 l_dsplist(i));
175 IF tp_dsplist.COUNT > 0 THEN
176 resultlist := AddList( resultlist, tp_dsplist);
177 END IF;
178
179 tp_dsplist := erase_list;
180 tp_dsplist := Add_Ctp_to_Party(p_ctx_id => p_ctx_id,
181 p_party_id => l_rec.party_id,
182 p_status => p_status,
183 p_dsprec => l_dsplist(i));
184
185 IF tp_dsplist.COUNT > 0 THEN
186 resultlist := AddList( resultlist, tp_dsplist);
187 END IF;
188
189 END LOOP;
190 CLOSE c_party_matched;
191 RETURN resultlist;
192 END;
193
194 FUNCTION Add_site_party
195 (p_ctx_id IN NUMBER,
196 p_party_id IN NUMBER,
197 p_status IN VARCHAR2 DEFAULT 'ALL',
198 p_dsprec IN dsprec)
199 RETURN dsplist
200 IS
201 CURSOR c_site_to_party (i_ctx_id NUMBER,
202 i_party_id NUMBER,
203 i_status VARCHAR2,
204 prof_status VARCHAR2) IS
205 SELECT gs.score,
206 gs.party_site_id,
207 l.address1,
208 l.city,
209 l.state,
210 l.postal_code,
211 s.identifying_address_flag
212 FROM hz_matched_party_sites_gt gs,
213 hz_party_sites s,
214 hz_locations l
215 WHERE gs.search_context_id = i_ctx_id
216 AND gs.party_id = i_party_id
217 AND NVL(gs.score,0) >= 0
218 AND gs.party_site_id = s.party_site_id
219 AND s.location_id = l.location_id
220 AND DECODE(prof_status,'Y','A',NVL(s.status,'A')) = NVL(s.status,'A');
221 -- AND DECODE(i_status,'ALL','ALL',NVL(s.status,'A')) = i_status;
222 l_rec c_site_to_party%ROWTYPE;
223 i NUMBER;
224 l_dsplist dsplist;
225 init_depth NUMBER;
226 init_ndata VARCHAR2(2000);
227 tp_dsplist dsplist;
228 resultlist dsplist;
229 l_score VARCHAR2(30);
230 -- for error usage
231 tmp_var VARCHAR2(2000);
232 tmp_var1 VARCHAR2(2000);
233 --BUG#3666792
234 l_site_status VARCHAR2(10);
235 BEGIN
236 l_site_status := NVL(fnd_profile.value('HZ_SHOW_ONLY_ACTIVE_ADDRESSES'),'N');
237
238 i := 0;
239 init_depth := p_dsprec.depth;
240 init_ndata := p_dsprec.ndata;
241
242 OPEN c_site_to_party(p_ctx_id, p_party_id, p_status, l_site_status);
243 LOOP
244 FETCH c_site_to_party INTO l_rec;
245 EXIT WHEN c_site_to_party%NOTFOUND;
246 i := i + 1;
247 l_dsplist(i).state := 0;
248 l_dsplist(i).depth := init_depth + 1;
249 l_dsplist(i).label := nvl(l_rec.address1||' - '||l_rec.city||' '||l_rec.state||' '||l_rec.postal_code,'NULL');
250
251 IF l_rec.identifying_address_flag = 'Y' THEN
252 l_dsplist(i).icon := ico_primary;
253 ELSE
254 l_dsplist(i).icon := ico_site;
255 END IF;
256
257 l_dsplist(i).ndata := atypstr(stypstr(p_dsprec.ndata)||'PARTY_SITE_ID:'||to_char(l_rec.party_site_id)||'@','PARTY_SITE');
258 resultlist(resultlist.COUNT + 1) := l_dsplist(i);
259
260 tp_dsplist := erase_list;
261 tp_dsplist := Add_Contact_to_site(p_ctx_id,
262 p_party_id,
263 l_rec.party_site_id,
264 p_status,
265 l_dsplist(i));
266 IF tp_dsplist.COUNT > 0 THEN
267 resultlist(resultlist.COUNT).state := 1;
268 resultlist := AddList( resultlist, tp_dsplist);
269 END IF;
270
271 --HYU Manque Add Contact Point to Party Site
272 tp_dsplist := erase_list;
273 tp_dsplist := Add_Ctp_to_Party_Site(p_ctx_id,
274 p_party_id,
275 l_rec.party_site_id,
276 p_status,
277 l_dsplist(i));
278 IF tp_dsplist.COUNT > 0 THEN
279 resultlist(resultlist.COUNT).state := 1;
280 resultlist := AddList( resultlist, tp_dsplist);
281 END IF;
282
283 END LOOP;
284 CLOSE c_site_to_party;
285 RETURN resultlist;
286 END;
287
288 FUNCTION Add_Contact_party
289 (p_ctx_id IN NUMBER,
290 p_party_id IN NUMBER,
291 p_status IN VARCHAR2 DEFAULT 'ALL',
292 p_dsprec IN dsprec)
293 RETURN dsplist
294 IS
295 cursor c_contact_to_party (i_ctx_id IN NUMBER, i_party_id IN NUMBER, i_status IN VARCHAR2) is
296 select a.score,
297 o.org_contact_id,
298 r.party_id rel_pty_id,
299 r.relationship_code rel_code,
300 p.party_id person_id,
301 p.party_name,
302 p.party_number
303 from hz_matched_contacts_gt a,
304 hz_org_contacts o,
305 hz_relationships r,
306 hz_parties p
307 where a.search_context_id = i_ctx_id
308 and a.party_id = i_party_id
309 and a.org_contact_id = o.org_contact_id
310 and nvl(a.score,0) >= 0
311 and o.party_site_id IS NULL
312 and o.party_relationship_id = r.relationship_id
313 and r.directional_flag = 'B'
314 and r.object_id = p.party_id;
315 -- and DECODE(p_status,'ALL','ALL',NVL(o.status,'A')) = p_status;
316 l_rec c_contact_to_party%ROWTYPE;
317 i NUMBER;
318 l_dsplist dsplist;
319 init_depth NUMBER;
320 init_ndata VARCHAR2(2000);
321 tp_dsplist dsplist;
322 resultlist dsplist;
323 -- for error usage
324 tmp_var VARCHAR2(2000);
325 tmp_var1 VARCHAR2(2000);
326 BEGIN
327 i := 0;
328 init_depth := p_dsprec.depth;
329 init_ndata := p_dsprec.ndata;
330 OPEN c_contact_to_party(p_ctx_id, p_party_id, p_status);
331 LOOP
332 FETCH c_contact_to_party INTO l_rec;
333 EXIT WHEN c_contact_to_party%NOTFOUND;
334 i := i + 1;
335 l_dsplist(i).state := 0;
336 l_dsplist(i).depth := init_depth + 1;
337 l_dsplist(i).label := nvl(l_rec.party_name,'NULL');
338 l_dsplist(i).icon := ico_person;
339 l_dsplist(i).ndata := atypstr(stypstr(init_ndata)||'ORG_CONTACT_ID:'||to_char(l_rec.org_contact_id)||'@REL_PTY_ID:'||to_char(l_rec.rel_pty_id)||'@PERSON_ID:'||to_char(l_rec.person_id)||'@','ORG_CONTACT');
340 resultlist(resultlist.COUNT + 1) := l_dsplist(i);
341 tp_dsplist := erase_list;
342 tp_dsplist := Add_Ctp_to_Party(p_ctx_id,
343 p_party_id,
344 l_rec.rel_pty_id,
345 p_status,
346 l_dsplist(i));
347 IF tp_dsplist.COUNT > 0 THEN
348 resultlist(resultlist.COUNT).state := 1;
349 resultlist := AddList( resultlist, tp_dsplist);
350 ELSE
351 --HYU 2103
352 resultlist(resultlist.COUNT).state := 0;
353 END IF;
354 END LOOP;
355 CLOSE c_contact_to_party;
356 RETURN resultlist;
357 END;
358
359 FUNCTION Add_Contact_to_site
360 ( p_ctx_id NUMBER,
361 p_party_id NUMBER,
362 p_party_site_id NUMBER,
363 p_status IN VARCHAR2 DEFAULT 'ALL',
364 p_dsprec dsprec)
365 RETURN dsplist
366 IS
367 CURSOR c_contact_to_site( i_ctx_id NUMBER,
368 i_party_id NUMBER,
369 i_party_site_id NUMBER,
370 i_status VARCHAR2) IS
371 SELECT gc.org_contact_id ,
372 gc.score ,
373 p.party_name ,
374 p.party_number ,
375 p.party_id person_id,
376 r.party_id rel_pty_id,
377 r.relationship_code rel_code
378 FROM hz_matched_contacts_gt gc,
379 hz_org_contacts c,
380 hz_relationships r,
381 hz_parties p
382 WHERE gc.search_context_id = i_ctx_id
383 AND gc.party_id = i_party_id
384 AND gc.org_contact_id = c.org_contact_id
385 AND NVL(gc.score,0) >= 0
386 AND c.party_site_id = i_party_site_id
387 AND c.party_relationship_id = r.relationship_id
388 AND r.directional_flag = 'B'
389 AND r.object_id = p.party_id;
390 -- AND DECODE(i_status, 'ALL', 'ALL', NVL(c.status,'A')) = i_status;
391 l_rec c_contact_to_site%ROWTYPE;
392 i NUMBER;
393 l_dsplist dsplist;
394 init_depth NUMBER;
395 init_ndata VARCHAR2(2000);
396 tp_dsplist dsplist;
397 resultlist dsplist;
398 l_score VARCHAR2(30);
399 -- for error usage
400 tmp_var VARCHAR2(2000);
401 tmp_var1 VARCHAR2(2000);
402 BEGIN
403 i := 0;
404 init_depth := p_dsprec.depth;
405 init_ndata := p_dsprec.ndata;
406 OPEN c_contact_to_site(p_ctx_id, p_party_id, p_party_site_id, p_status);
407 LOOP
408 FETCH c_contact_to_site INTO l_rec;
409 EXIT WHEN c_contact_to_site%NOTFOUND;
410 i := i + 1;
411 l_dsplist(i).state := 0;
412 l_dsplist(i).depth := init_depth + 1;
413 l_dsplist(i).label := nvl(l_rec.party_name,'NULL');
414 l_dsplist(i).icon := ico_person;
415 l_dsplist(i).ndata := atypstr(stypstr(init_ndata)||'ORG_CONTACT_ID:'||to_char(l_rec.org_contact_id)||'@PERSON_ID:'||to_char(l_rec.person_id)||'@REL_PTY_ID:'||to_char(l_rec.rel_pty_id)||'@','ORG_CONTACT');
416 resultlist(resultlist.COUNT + 1) := l_dsplist(i);
417 tp_dsplist := erase_list;
418
419 tp_dsplist := Add_Ctp_to_Party(p_ctx_id,
420 p_party_id,
421 l_rec.rel_pty_id,
422 p_status,
423 l_dsplist(i));
424
425 IF tp_dsplist.COUNT > 0 THEN
426 resultlist(resultlist.COUNT).state := 1;
427 resultlist := AddList( resultlist, tp_dsplist);
428 END IF;
429 END LOOP;
430 CLOSE c_contact_to_site;
431 RETURN resultlist;
432 END;
433
434 FUNCTION Add_Ctp_to_Party
435 (p_ctx_id IN NUMBER,
436 p_party_id IN NUMBER,
437 p_rel_pty_id IN NUMBER DEFAULT NULL,
438 p_status IN VARCHAR2 DEFAULT 'ALL',
439 p_dsprec IN dsprec)
440 RETURN dsplist
441 IS
442 CURSOR c_ctp_to_party(i_ctx_id NUMBER,
443 i_party_id NUMBER,
444 i_rel_pty_id NUMBER,
445 i_status VARCHAR2) IS
446 SELECT ctpt.contact_point_type,
450 'PHONE', DECODE(ctpt.phone_area_code,NULL,NULL,ctpt.phone_area_code||'-')||ctpt.phone_number,
447 ctpt.contact_point_id,
448 gcpt.score,
449 decode(ctpt.contact_point_type, 'EMAIL', ctpt.email_address,
451 'TELEX', ctpt.telex_number,
452 'WEB', ctpt.url ) ad
453 FROM hz_matched_cpts_gt gcpt,
454 hz_contact_points ctpt
455 WHERE gcpt.search_context_id = i_ctx_id
456 AND gcpt.party_id = i_party_id
457 AND NVL(gcpt.score,0) >= 0
458 AND ctpt.owner_table_name = 'HZ_PARTIES'
459 AND ctpt.owner_table_id = i_rel_pty_id
460 AND gcpt.contact_point_id = ctpt.contact_point_id;
461 -- AND DECODE(i_status, 'ALL', 'ALL', NVL(ctpt.status,'A')) = i_status;
462
463 CURSOR c_ctp_to_party_dir(i_ctx_id NUMBER,
464 i_party_id NUMBER,
465 i_status VARCHAR2) IS
466 SELECT ctpt.contact_point_type,
467 ctpt.contact_point_id,
468 gcpt.score,
469 decode(ctpt.contact_point_type, 'EMAIL', ctpt.email_address,
470 'PHONE', DECODE(ctpt.phone_area_code,NULL,NULL,ctpt.phone_area_code||'-')||ctpt.phone_number,
471 'TLX' , ctpt.telex_number,
472 'WEB' , ctpt.url ) ad
473 FROM hz_matched_cpts_gt gcpt,
474 hz_contact_points ctpt
475 WHERE gcpt.search_context_id = i_ctx_id
476 AND gcpt.party_id = i_party_id
477 AND NVL(gcpt.score,0) >= 0
478 AND ctpt.owner_table_name = 'HZ_PARTIES'
479 AND ctpt.owner_table_id = i_party_id
480 AND gcpt.contact_point_id = ctpt.contact_point_id;
481 -- AND DECODE(i_status, 'ALL', 'ALL', NVL(ctpt.status,'A')) = i_status;
482
483 l_rec c_ctp_to_party%ROWTYPE;
484 i NUMBER;
485 l_dsplist dsplist;
486 init_depth NUMBER;
487 init_ndata VARCHAR2(2000);
488 l_score VARCHAR2(30);
489 BEGIN
490 i := 0;
491 init_depth := p_dsprec.depth;
492 init_ndata := p_dsprec.ndata;
493 IF p_rel_pty_id IS NOT NULL THEN
494 OPEN c_ctp_to_party(p_ctx_id , p_party_id, p_rel_pty_id, p_status);
495 LOOP
496 FETCH c_ctp_to_party INTO l_rec;
497 EXIT WHEN c_ctp_to_party%NOTFOUND;
498 i := i + 1;
499 l_dsplist(i).state := 0;
500 l_dsplist(i).depth := init_depth + 1;
501 l_dsplist(i).label := nvl(l_rec.ad,'NULL');
502
503 IF l_rec.contact_point_type = 'EMAIL' THEN l_dsplist(i).icon := ico_email;
504 ELSIF l_rec.contact_point_type = 'PHONE' THEN l_dsplist(i).icon := ico_phone;
505 ELSIF l_rec.contact_point_type = 'TLX' THEN l_dsplist(i).icon := ico_tlx;
506 ELSIF l_rec.contact_point_type = 'WEB' THEN l_dsplist(i).icon := ico_web;
507 END IF;
508
509 l_dsplist(i).ndata := atypstr(stypstr(init_ndata)||'CONTACT_POINT_ID:'||TO_CHAR(l_rec.contact_point_id)||'@','CONTACT_POINT');
510 END LOOP;
511 CLOSE c_ctp_to_party;
512 ELSIF p_rel_pty_id IS NULL THEN
513 OPEN c_ctp_to_party_dir(p_ctx_id , p_party_id, p_status);
514 LOOP
515 FETCH c_ctp_to_party_dir INTO l_rec;
516 EXIT WHEN c_ctp_to_party_dir%NOTFOUND;
517 i := i + 1;
518 l_dsplist(i).state := 0;
519 l_dsplist(i).depth := init_depth + 1;
520 l_dsplist(i).label := nvl(l_rec.ad,'NULL');
521
522 IF l_rec.contact_point_type = 'EMAIL' THEN l_dsplist(i).icon := ico_email;
523 ELSIF l_rec.contact_point_type = 'PHONE' THEN l_dsplist(i).icon := ico_phone;
524 ELSIF l_rec.contact_point_type = 'TLX' THEN l_dsplist(i).icon := ico_tlx;
525 ELSIF l_rec.contact_point_type = 'WEB' THEN l_dsplist(i).icon := ico_web;
526 END IF;
527
528 l_dsplist(i).ndata := atypstr(stypstr(init_ndata)||'CONTACT_POINT_ID:'||TO_CHAR(l_rec.contact_point_id)||'@','CONTACT_POINT');
529 END LOOP;
530 CLOSE c_ctp_to_party_dir;
531 END IF;
532 RETURN l_dsplist;
533 END;
534
535 --HYU
536 FUNCTION Add_Ctp_to_Party_Site
537 (p_ctx_id IN NUMBER,
538 p_party_id IN NUMBER,
539 p_party_site_id IN NUMBER,
540 p_status IN VARCHAR2 DEFAULT 'ALL',
541 p_dsprec IN dsprec)
542 RETURN dsplist
543 IS
544 CURSOR c1(i_ctx_id NUMBER,
545 i_party_id NUMBER,
546 i_pty_site_id NUMBER,
547 i_status VARCHAR2) IS
548 SELECT ctpt.contact_point_type,
549 ctpt.contact_point_id,
550 gcpt.score,
551 decode(ctpt.contact_point_type, 'EMAIL', ctpt.email_address,
552 'PHONE', DECODE(ctpt.phone_area_code,NULL,NULL,ctpt.phone_area_code||'-')||ctpt.phone_number,
553 'TELEX', ctpt.telex_number,
554 'WEB', ctpt.url ) ad
555 FROM hz_matched_cpts_gt gcpt,
556 hz_contact_points ctpt
557 WHERE gcpt.search_context_id = i_ctx_id
558 AND gcpt.party_id = i_party_id
559 AND NVL(gcpt.score,0) >= 0
560 AND ctpt.owner_table_name = 'HZ_PARTY_SITES'
561 AND ctpt.owner_table_id = i_pty_site_id
562 AND gcpt.contact_point_id = ctpt.contact_point_id;
563 -- AND DECODE(i_status, 'ALL', 'ALL', NVL(ctpt.status,'A')) = i_status;
564 l_rec c1%ROWTYPE;
565 i NUMBER;
569 l_score VARCHAR2(30);
566 l_dsplist dsplist;
567 init_depth NUMBER;
568 init_ndata VARCHAR2(2000);
570 BEGIN
571 i := 0;
572 init_depth := p_dsprec.depth;
573 init_ndata := p_dsprec.ndata;
574 OPEN c1(p_ctx_id, p_party_id, p_party_site_id, p_status);
575 LOOP
576 FETCH c1 INTO l_rec;
577 EXIT WHEN c1%NOTFOUND;
578 i := i + 1;
579 l_dsplist(i).state := 0;
580 l_dsplist(i).depth := init_depth + 1;
581 l_dsplist(i).label := nvl(l_rec.ad,'NULL');
582
583 IF l_rec.contact_point_type = 'EMAIL' THEN l_dsplist(i).icon := ico_email;
584 ELSIF l_rec.contact_point_type = 'PHONE' THEN l_dsplist(i).icon := ico_phone;
585 ELSIF l_rec.contact_point_type = 'TLX' THEN l_dsplist(i).icon := ico_tlx;
586 ELSIF l_rec.contact_point_type = 'WEB' THEN l_dsplist(i).icon := ico_web;
587 END IF;
588
589 l_dsplist(i).ndata := atypstr(stypstr(init_ndata)||'CONTACT_POINT_ID:'||TO_CHAR(l_rec.contact_point_id)||'@','CONTACT_POINT');
590 END LOOP;
591 CLOSE c1;
592 RETURN l_dsplist;
593 END;
594
595
596
597 ----------090202
598 FUNCTION dsp_for_party_pty_accts
599 (p_ctx_id IN NUMBER,
600 p_cur_all IN VARCHAR2 DEFAULT 'ALL',
601 p_status IN VARCHAR2 DEFAULT 'ALL',
602 p_disp_percent IN VARCHAR2 DEFAULT 'Y')
603 RETURN dsplist
604 IS
605 CURSOR c_party_matched (i_ctx_id NUMBER, i_status VARCHAR2) IS
606 SELECT distinct p.party_name,
607 p.party_number,
608 p.party_id,
609 s.score
610 FROM hz_matched_parties_gt s,
611 hz_parties p
612 /*hz_matched_parties_gt ca1 Bug2678267 Commented to avoid cartesian join pblm */
613 WHERE s.party_id = p.party_id
614 AND s.search_context_id = i_ctx_id
615 AND NVL(s.score,0) >= 0
616 -- AND DECODE(i_status, 'ALL', 'ALL', NVL(p.status,'A')) = i_status
617 ORDER BY s.score desc;
618
619 l_rec c_party_matched%ROWTYPE;
620 i NUMBER;
621 j NUMBER;
622 init_depth NUMBER;
623 init_ndata VARCHAR2(4000);
624 l_dsplist dsplist;
625 tp_dsplist dsplist;
626 resultlist dsplist;
627 -- for error usage
628 tmp_var VARCHAR2(2000);
629 tmp_var1 VARCHAR2(2000);
630 already_exp VARCHAR2(1) := 'N';
631 l_score VARCHAR2(30);
632
633 BEGIN
634 i := 0;
635 j := 0;
636 init_depth := 0;
637 init_ndata := NULL;
638
639 OPEN c_party_matched(p_ctx_id, p_status);
640 LOOP
641 FETCH c_party_matched INTO l_rec;
642 EXIT WHEN c_party_matched%NOTFOUND;
643 i := i + 1;
644 IF i <= 5 THEN
645 l_dsplist(i).state := 1;
646 ELSE
647 l_dsplist(i).state := -1;
648 END IF;
649 l_dsplist(i).depth := init_depth + 1;
650 l_dsplist(i).label := nvl(l_rec.party_name||r_score(l_rec.score,p_disp_percent),'NULL');
651 l_dsplist(i).icon := ico_party;
652 l_dsplist(i).ndata := atypstr('PARTY_ID:'||to_char(l_rec.party_id)||'@','PARTY');
653 resultlist(resultlist.COUNT + 1) := l_dsplist(i);
654 tp_dsplist := erase_list;
655 tp_dsplist := add_acct_party(p_ctx_id,
656 l_rec.party_id,
657 p_cur_all,
658 p_status,
659 l_dsplist(i));
660 IF tp_dsplist.COUNT > 0 THEN
661 resultlist := AddList( resultlist, tp_dsplist);
662 --{HYU
663 ELSE
664 l_dsplist(i).state := 0;
665 --}
666 END IF;
667 END LOOP;
668 CLOSE c_party_matched;
669 RETURN resultlist;
670 END;
671
672
673 FUNCTION dsp_for_party_accts
674 (p_ctx_id IN NUMBER,
675 p_cur_all IN VARCHAR2 DEFAULT 'ALL',
676 p_status IN VARCHAR2 DEFAULT 'ALL',
677 p_disp_percent IN VARCHAR2 DEFAULT 'Y')
678 RETURN dsplist
679 IS
680 CURSOR c_party_matched (i_ctx_id NUMBER, i_status VARCHAR2) IS
681 SELECT distinct p.party_name,
682 p.party_number,
683 p.party_id,
684 s.score
685 FROM hz_matched_parties_gt s,
686 hz_parties p,
687 hz_matched_parties_gt ca1,
688 hz_cust_accounts ca2
689 WHERE s.party_id = p.party_id
690 AND s.search_context_id = i_ctx_id
691 AND NVL(s.score,0) >= 0
692 -- AND DECODE(i_status, 'ALL', 'ALL', NVL(p.status,'A')) = i_status
693 AND s.party_id = ca2.party_id
694 AND ca1.score < 0
695 AND ca1.search_context_id = i_ctx_id
696 AND ca2.cust_account_id = -ca1.party_id
697 AND DECODE(i_status, 'ALL', 'ALL', NVL(ca2.status,'A')) = i_status
698 ORDER BY s.score desc;
699
700 l_rec c_party_matched%ROWTYPE;
701 i NUMBER;
702 j NUMBER;
703 init_depth NUMBER;
704 init_ndata VARCHAR2(4000);
705 l_dsplist dsplist;
706 tp_dsplist dsplist;
707 resultlist dsplist;
708 -- for error usage
709 tmp_var VARCHAR2(2000);
710 tmp_var1 VARCHAR2(2000);
711 already_exp VARCHAR2(1) := 'N';
712 l_score VARCHAR2(30);
713
714 BEGIN
715 i := 0;
716 j := 0;
717 init_depth := 0;
718 init_ndata := NULL;
719
720 OPEN c_party_matched(p_ctx_id, p_status);
721 LOOP
725 IF i <= 5 THEN
722 FETCH c_party_matched INTO l_rec;
723 EXIT WHEN c_party_matched%NOTFOUND;
724 i := i + 1;
726 l_dsplist(i).state := 1;
727 ELSE
728 l_dsplist(i).state := -1;
729 END IF;
730 l_dsplist(i).depth := init_depth + 1;
731 l_dsplist(i).label := nvl(l_rec.party_name||r_score(l_rec.score,p_disp_percent),'NULL');
732 l_dsplist(i).icon := ico_party;
733 l_dsplist(i).ndata := atypstr('PARTY_ID:'||to_char(l_rec.party_id)||'@','PARTY');
734 resultlist(resultlist.COUNT + 1) := l_dsplist(i);
735 tp_dsplist := erase_list;
736 tp_dsplist := add_acct_party(p_ctx_id,
737 l_rec.party_id,
738 p_cur_all,
739 p_status,
740 l_dsplist(i));
741 IF tp_dsplist.COUNT > 0 THEN
742 resultlist := AddList( resultlist, tp_dsplist);
743 END IF;
744 END LOOP;
745 CLOSE c_party_matched;
746 RETURN resultlist;
747 END;
748
749
750 ---
751
752 FUNCTION add_acct_party
753 (p_ctx_id IN NUMBER,
754 p_party_id IN NUMBER,
755 p_cur_all IN VARCHAR2 DEFAULT 'ALL',
756 p_status IN VARCHAR2 DEFAULT 'ALL',
757 p_dsprec IN dsprec)
758 RETURN dsplist
759 IS
760
761 --HYU
762 CURSOR c_custaccts(i_ctx_id NUMBER,
763 i_party_id NUMBER,
764 i_status VARCHAR2) IS
765 SELECT distinct a.cust_account_id,
766 a.account_number,
767 a.customer_class_code,
768 a.account_name,
769 p.party_name,
770 p.party_number,
771 p.party_id,
772 s.score
773 FROM hz_cust_accounts a,
774 hz_matched_parties_gt s,
775 hz_parties p
776 WHERE a.party_id = i_party_id
777 AND a.cust_account_id = -s.party_id
778 AND s.score < 0
779 AND s.search_context_id = i_ctx_id
780 AND a.party_id = p.party_id
781 AND DECODE(i_status, 'ALL', 'ALL', NVL(a.status,'A')) = i_status
782 ORDER BY s.score asc;
783
784 l_rec c_custaccts%ROWTYPE;
785 i NUMBER;
786 j NUMBER;
787 init_depth NUMBER;
788 init_ndata VARCHAR2(4000);
789 l_dsplist dsplist;
790 tp_dsplist dsplist;
791 resultlist dsplist;
792 -- for error usage
793 tmp_var VARCHAR2(2000);
794 tmp_var1 VARCHAR2(2000);
795 already_exp VARCHAR2(1) := 'N';
796 l_score VARCHAR2(30);
797
798 BEGIN
799 i := 0;
800 init_depth := p_dsprec.depth;
801 init_ndata := p_dsprec.ndata;
802
803 OPEN c_custaccts(p_ctx_id, p_party_id, p_status);
804 LOOP
805 FETCH c_custaccts INTO l_rec;
806 EXIT WHEN c_custaccts%NOTFOUND;
807 i := i + 1;
808
809 l_dsplist(i).depth := init_depth + 1;
810
811 IF l_rec.account_name IS NULL THEN
812 l_dsplist(i).label :=nvl(l_rec.party_name||'-'||l_rec.account_number,'NULL');
813 ELSE
814 l_dsplist(i).label :=nvl(l_rec.account_name||'-'||l_rec.account_number,'NULL');
815 END IF;
816 --HYU{
817 l_dsplist(i).state := 1;
818 j := i;
819 --}
820 l_dsplist(i).icon := ico_account;
821 l_dsplist(i).ndata := atypstr(stypstr(init_ndata)||'CUST_ACCOUNT_ID:'||to_char(l_rec.cust_account_id)||'@','ACCOUNT');
822 resultlist(resultlist.COUNT + 1) := l_dsplist(i);
823 --HYU
824 tp_dsplist := erase_list;
825 tp_dsplist := Add_site_acct(p_ctx_id,
826 p_party_id,
827 l_rec.cust_account_id,
828 p_cur_all,
829 p_status,
830 l_dsplist(i));
831 IF tp_dsplist.COUNT > 0 THEN
832 l_dsplist(i).state := 1;
833 resultlist := AddList( resultlist, tp_dsplist);
834 --{HYU 1903
835 ELSE
836 l_dsplist(j).state := 0;
837 --}
838 END IF;
839
840 tp_dsplist := erase_list;
841 tp_dsplist := Add_Contact_Acct(p_ctx_id,
842 p_party_id,
843 l_rec.cust_account_id,
844 p_status,
845 l_dsplist(i));
846
847 IF tp_dsplist.COUNT > 0 THEN
848 resultlist := AddList( resultlist, tp_dsplist);
849 END IF;
850 --HYU
851 tp_dsplist := erase_list;
852 tp_dsplist := Add_Ctp_to_Party(p_ctx_id => p_ctx_id,
853 p_party_id=> l_rec.party_id,
854 p_status => p_status,
855 p_dsprec => l_dsplist(i));
856 IF tp_dsplist.COUNT > 0 THEN
857 resultlist(resultlist.COUNT).state := 1;
858 resultlist := AddList( resultlist, tp_dsplist);
859 ELSE
860 --HYU2103
861 resultlist(resultlist.COUNT).state := 0;
862 END IF;
863
864 END LOOP;
865 CLOSE c_custaccts;
866 RETURN resultlist;
867 END;
868
869
870 FUNCTION Add_site_acct
871 (p_ctx_id IN NUMBER,
872 p_party_id IN NUMBER,
873 p_cust_acct_id IN NUMBER,
874 p_cur_all IN VARCHAR2 DEFAULT 'ALL',
875 p_status IN VARCHAR2 DEFAULT 'ALL',
876 p_dsprec IN dsprec)
877 RETURN dsplist
878 IS
879 TYPE nrc IS REF CURSOR;
880 cv nrc;
881 l_score_n NUMBER;
885 l_cust_acct_site_id NUMBER;
882 l_party_id NUMBER;
883 l_party_site_id NUMBER;
884 l_cust_account_id NUMBER;
886 l_address1 VARCHAR2(240);
887 l_city VARCHAR2(60);
888 l_state VARCHAR2(60);
889 l_postal_code VARCHAR2(60);
890 l_iden_address_flag VARCHAR2(30);
891 i NUMBER;
892 l_dsplist dsplist;
893 init_depth NUMBER;
894 init_ndata VARCHAR2(2000);
895 tp_dsplist dsplist;
896 resultlist dsplist;
897 l_score VARCHAR2(30);
898 -- for error usage
899 tmp_var VARCHAR2(2000);
900 tmp_var1 VARCHAR2(2000);
901 --BUG#3666792
902 l_active_site VARCHAR2(10);
903 BEGIN
904 i := 0;
905 init_depth := p_dsprec.depth;
906 init_ndata := p_dsprec.ndata;
907
908 l_active_site := NVL(fnd_profile.value('HZ_SHOW_ONLY_ACTIVE_ADDRESSES'),'N');
909
910 IF p_cur_all = 'ALL' THEN
911
912 OPEN cv FOR
913 SELECT distinct gs.score,
914 ps.party_id,
915 ps.party_site_id,
916 a.cust_account_id,
917 a.cust_acct_site_id,
918 l.address1,
919 l.city,
920 l.state,
921 l.postal_code,
922 ps.identifying_address_flag
923 FROM hz_matched_party_sites_gt gs,
924 hz_cust_acct_sites_all a,
925 hz_party_sites ps,
926 hz_locations l
927 WHERE gs.search_context_id = p_ctx_id
928 AND gs.score < 0
929 AND -gs.party_id = p_cust_acct_id
930 AND -gs.party_site_id = a.cust_acct_site_id
931 AND a.party_site_id = ps.party_site_id
932 AND ps.location_id = l.location_id
933 AND DECODE(l_active_site, 'Y', 'A', NVL(a.status,'A')) = NVL(a.status,'A');
934 -- AND DECODE(p_status,'ALL','ALL',NVL(a.status,'A')) = p_status;
935
936 ELSIF p_cur_all = 'CUR' THEN
937 OPEN cv FOR
938 SELECT distinct gs.score,
939 ps.party_id,
940 ps.party_site_id,
941 a.cust_account_id,
942 a.cust_acct_site_id,
943 l.address1,
944 l.city,
945 l.state,
946 l.postal_code,
947 ps.identifying_address_flag
948 FROM hz_matched_party_sites_gt gs,
949 hz_cust_acct_sites a,
950 hz_party_sites ps,
951 hz_locations l
952 WHERE gs.search_context_id = p_ctx_id
953 AND gs.score < 0
954 AND -gs.party_id = p_cust_acct_id
955 AND -gs.party_site_id = a.cust_acct_site_id
956 AND a.party_site_id = ps.party_site_id
957 AND ps.location_id = l.location_id
958 AND DECODE(l_active_site, 'Y', 'A', NVL(a.status,'A')) = NVL(a.status,'A');
959 -- AND DECODE(p_status,'ALL','ALL',NVL(a.status,'A')) = p_status;
960
961 END IF;
962
963 IF cv%ISOPEN THEN
964 LOOP
965 FETCH cv INTO l_score_n ,
966 l_party_id ,
967 l_party_site_id ,
968 l_cust_account_id ,
969 l_cust_acct_site_id,
970 l_address1 ,
971 l_city ,
972 l_state ,
973 l_postal_code ,
974 l_iden_address_flag;
975 EXIT WHEN cv%NOTFOUND;
976 i := i + 1;
977 l_dsplist(i).state := 0;
978 l_dsplist(i).depth := init_depth + 1;
979 l_dsplist(i).label := nvl(l_address1||' - '||l_city||' '||l_state||' '||l_postal_code,'NULL');
980 -- l_dsplist(i).icon := ico_site;
981
982 IF l_iden_address_flag = 'Y' THEN
983 l_dsplist(i).icon := ico_primary;
984 ELSE
985 l_dsplist(i).icon := ico_site;
986 END IF;
987
988 l_dsplist(i).ndata := atypstr(stypstr(init_ndata)||'PARTY_SITE_ID:'||to_char(l_party_site_id)||'@ACCT_SITE_ID:'||TO_CHAR(l_cust_acct_site_id)||'@','CUST_ACCT_SITE');
989 resultlist(resultlist.COUNT + 1) := l_dsplist(i);
990
991 tp_dsplist := erase_list;
992 tp_dsplist := Add_Contact_to_acct_site(p_ctx_id,
993 p_party_id,
994 l_cust_account_id,
995 l_cust_acct_site_id,
996 p_cur_all,
997 p_status,
998 l_dsplist(i));
999
1000 IF tp_dsplist.COUNT > 0 THEN
1001 resultlist(resultlist.COUNT).state := 1;
1002 resultlist := AddList( resultlist, tp_dsplist);
1003 END IF;
1004 --HYU
1005 tp_dsplist := erase_list;
1006 tp_dsplist := Add_Ctp_to_Party_Site(p_ctx_id,
1007 p_party_id,
1008 l_party_site_id,
1009 p_status,
1010 l_dsplist(i));
1011 IF tp_dsplist.COUNT > 0 THEN
1012 resultlist(resultlist.COUNT).state := 1;
1016 END LOOP;
1013 resultlist := AddList( resultlist, tp_dsplist);
1014 END IF;
1015
1017 CLOSE cv;
1018 END IF;
1019 RETURN resultlist;
1020 END;
1021
1022
1023 --HYU
1024
1025
1026 FUNCTION Add_Contact_Acct
1027 (p_ctx_id IN NUMBER,
1028 p_party_id IN NUMBER,
1029 p_cust_acct_id IN NUMBER,
1030 p_status IN VARCHAR2 DEFAULT 'ALL',
1031 p_dsprec IN dsprec)
1032 RETURN dsplist
1033 IS
1034 --HYU
1035 cursor c_contact_to_acct (i_ctx_id IN NUMBER, i_cust_acct_id IN NUMBER) is
1036 select distinct a.score,
1037 r.party_id rel_pty_id,
1038 r.relationship_code rel_code,
1039 ro.cust_account_role_id,
1040 p.party_id person_id,
1041 p.party_name,
1042 p.party_number,
1043 o.org_contact_id
1044 from hz_matched_contacts_gt a,
1045 hz_relationships r,
1046 hz_parties p,
1047 hz_cust_account_roles ro,
1048 hz_org_contacts o
1049 where a.search_context_id = i_ctx_id
1050 and a.score < 0
1051 and -a.party_id = i_cust_acct_id
1052 and -a.org_contact_id = ro.cust_account_role_id
1053 and ro.party_id = r.party_id
1054 and r.relationship_id = o.party_relationship_id
1055 and r.directional_flag = 'B'
1056 and r.object_id = p.party_id
1057 and ro.cust_acct_site_id IS NULL;
1058 -- and DECODE(p_status,'ALL','ALL', NVL(ro.status,'A')) = p_status;
1059
1060 l_rec c_contact_to_acct%ROWTYPE;
1061 i NUMBER;
1062 l_dsplist dsplist;
1063 init_depth NUMBER;
1064 init_ndata VARCHAR2(2000);
1065 tp_dsplist dsplist;
1066 resultlist dsplist;
1067 -- for error usage
1068 tmp_var VARCHAR2(2000);
1069 tmp_var1 VARCHAR2(2000);
1070
1071 BEGIN
1072 i := 0;
1073 init_depth := p_dsprec.depth;
1074 init_ndata := p_dsprec.ndata;
1075
1076 OPEN c_contact_to_acct(p_ctx_id,
1077 p_cust_acct_id);
1078 LOOP
1079 FETCH c_contact_to_acct INTO l_rec;
1080 EXIT WHEN c_contact_to_acct%NOTFOUND;
1081 i := i + 1;
1082 l_dsplist(i).state := 0;
1083 l_dsplist(i).depth := init_depth + 1;
1084 l_dsplist(i).label := nvl(l_rec.party_name,'NULL');
1085 l_dsplist(i).icon := ico_person;
1086 l_dsplist(i).ndata := atypstr(stypstr(init_ndata)||
1087 'CUST_ACCOUNT_ROLE_ID:'||
1088 to_char(l_rec.cust_account_role_id)||
1089 '@ORG_CONTACT_ID:'||to_char(l_rec.org_contact_id)||
1090 '@REL_PTY_ID:'||to_char(l_rec.rel_pty_id)||
1091 '@PERSON_ID:'||to_char(l_rec.person_id)||'@','CUST_ACCOUNT_ROLE');
1092 resultlist(resultlist.COUNT + 1) := l_dsplist(i);
1093 tp_dsplist := erase_list;
1094 tp_dsplist := Add_Ctp_to_Party(p_ctx_id,
1095 p_party_id,
1096 l_rec.rel_pty_id,
1097 p_status,
1098 l_dsplist(i));
1099 IF tp_dsplist.COUNT > 0 THEN
1100 resultlist(resultlist.COUNT).state := 1;
1101 resultlist := AddList( resultlist, tp_dsplist);
1102 END IF;
1103
1104 END LOOP;
1105 CLOSE c_contact_to_acct;
1106 RETURN resultlist;
1107
1108 END;
1109
1110
1111 FUNCTION Add_Contact_to_acct_site
1112 ( p_ctx_id IN NUMBER,
1113 p_party_id IN NUMBER,
1114 p_cust_acct_id IN NUMBER,
1115 p_acct_site_id IN NUMBER,
1116 p_cur_all IN VARCHAR2,
1117 p_status IN VARCHAR2 DEFAULT 'ALL',
1118 p_dsprec IN dsprec)
1119 RETURN dsplist
1120 IS
1121 CURSOR c_contact_to_site( i_ctx_id NUMBER,
1122 i_cust_acct_id NUMBER,
1123 i_acct_site_id NUMBER,
1124 i_status VARCHAR2) IS
1125 SELECT distinct gc.score ,
1126 p.party_name ,
1127 p.party_number ,
1128 p.party_id person_id,
1129 ro.cust_account_role_id ,
1130 r.party_id rel_pty_id,
1131 r.relationship_code rel_code ,
1132 o.org_contact_id
1133 FROM hz_matched_contacts_gt gc,
1134 hz_relationships r,
1135 hz_parties p,
1136 hz_cust_account_roles ro,
1137 hz_cust_acct_sites asite,
1138 hz_org_contacts o
1139 WHERE gc.search_context_id = i_ctx_id
1140 AND gc.score < 0
1141 AND -gc.party_id = i_cust_acct_id
1142 AND -gc.org_contact_id = ro.cust_account_role_id
1143 AND ro.cust_acct_site_id = i_acct_site_id
1144 AND ro.party_id = r.party_id
1145 AND r.directional_flag = 'B'
1146 AND r.object_id = p.party_id
1147 AND asite.cust_acct_site_id = ro.cust_acct_site_id
1148 AND asite.party_site_id = o.party_site_id
1149 AND o.party_relationship_id = r.relationship_id;
1150 -- AND DECODE(i_status,'ALL','ALL',NVL(ro.status,'A')) = i_status;
1151
1152
1153 CURSOR c_contact_to_site2( i_ctx_id NUMBER,
1154 i_cust_acct_id NUMBER,
1155 i_acct_site_id NUMBER,
1156 i_status VARCHAR2) IS
1160 p.party_id person_id,
1157 SELECT distinct gc.score ,
1158 p.party_name ,
1159 p.party_number ,
1161 ro.cust_account_role_id ,
1162 r.party_id rel_pty_id,
1163 r.relationship_code rel_code ,
1164 o.org_contact_id
1165 FROM hz_matched_contacts_gt gc,
1166 hz_relationships r,
1167 hz_parties p,
1168 hz_cust_account_roles ro,
1169 hz_cust_acct_sites_all asite,
1170 hz_org_contacts o
1171 WHERE gc.search_context_id = i_ctx_id
1172 AND gc.score < 0
1173 AND -gc.party_id = i_cust_acct_id
1174 AND -gc.org_contact_id = ro.cust_account_role_id
1175 AND ro.cust_acct_site_id = i_acct_site_id
1176 AND ro.party_id = r.party_id
1177 AND r.directional_flag = 'B'
1178 AND r.object_id = p.party_id
1179 AND asite.cust_acct_site_id = ro.cust_acct_site_id
1180 AND asite.party_site_id = o.party_site_id
1181 AND o.party_relationship_id = r.relationship_id;
1182 -- AND DECODE(i_status,'ALL','ALL',NVL(ro.status,'A')) = i_status;
1183
1184 l_rec c_contact_to_site%ROWTYPE;
1185 l_rec2 c_contact_to_site2%ROWTYPE;
1186 i NUMBER;
1187 l_dsplist dsplist;
1188 init_depth NUMBER;
1189 init_ndata VARCHAR2(2000);
1190 tp_dsplist dsplist;
1191 resultlist dsplist;
1192 l_score VARCHAR2(30);
1193 -- for error usage
1194 tmp_var VARCHAR2(2000);
1195 tmp_var1 VARCHAR2(2000);
1196 BEGIN
1197 i := 0;
1198 init_depth := p_dsprec.depth;
1199 init_ndata := p_dsprec.ndata;
1200
1201 IF p_cur_all = 'CUR' THEN
1202 OPEN c_contact_to_site(p_ctx_id, p_cust_acct_id, p_acct_site_id, p_status);
1203 LOOP
1204 FETCH c_contact_to_site INTO l_rec;
1205 EXIT WHEN c_contact_to_site%NOTFOUND;
1206 i := i + 1;
1207 l_dsplist(i).state := 0;
1208 l_dsplist(i).depth := init_depth + 1;
1209 l_dsplist(i).label := nvl(l_rec.party_name,'NULL');
1210 l_dsplist(i).icon := ico_person;
1211 l_dsplist(i).ndata := atypstr(stypstr(init_ndata)||
1212 'CUST_ACCOUNT_ROLE_ID:'||to_char(l_rec.cust_account_role_id)||
1213 '@ORG_CONTACT_ID:'||to_char(l_rec.org_contact_id)||
1214 '@PERSON_ID:'||to_char(l_rec.person_id)||
1215 '@REL_PTY_ID:'||to_char(l_rec.rel_pty_id)||'@','CUST_ACCOUNT_ROLE');
1216 resultlist(resultlist.COUNT + 1) := l_dsplist(i);
1217
1218 tp_dsplist := erase_list;
1219 tp_dsplist := Add_Ctp_to_Party(p_ctx_id,
1220 p_party_id,
1221 l_rec.rel_pty_id,
1222 p_status,
1223 l_dsplist(i));
1224 IF tp_dsplist.COUNT > 0 THEN
1225 resultlist(resultlist.COUNT).state := 1;
1226 resultlist := AddList( resultlist, tp_dsplist);
1227 END IF;
1228
1229 END LOOP;
1230 CLOSE c_contact_to_site;
1231 RETURN resultlist;
1232 ELSIF p_cur_all = 'ALL' THEN
1233 OPEN c_contact_to_site2(p_ctx_id, p_cust_acct_id, p_acct_site_id, p_status);
1234 LOOP
1235 FETCH c_contact_to_site2 INTO l_rec2;
1236 EXIT WHEN c_contact_to_site2%NOTFOUND;
1237 i := i + 1;
1238 l_dsplist(i).state := 0;
1239 l_dsplist(i).depth := init_depth + 1;
1240 l_dsplist(i).label := nvl(l_rec2.party_name,'NULL');
1241 l_dsplist(i).icon := ico_person;
1242 l_dsplist(i).ndata := atypstr(stypstr(init_ndata)||'CUST_ACCOUNT_ROLE_ID:'||
1243 to_char(l_rec2.cust_account_role_id)||
1244 '@ORG_CONTACT_ID:'||to_char(l_rec2.org_contact_id)||
1245 '@PERSON_ID:'||to_char(l_rec2.person_id)||'@REL_PTY_ID:'||to_char(l_rec2.rel_pty_id)||
1246 '@','CUST_ACCOUNT_ROLE');
1247 resultlist(resultlist.COUNT + 1) := l_dsplist(i);
1248
1249 tp_dsplist := erase_list;
1250 tp_dsplist := Add_Ctp_to_Party(p_ctx_id,
1251 p_party_id,
1252 l_rec2.rel_pty_id,
1253 p_status,
1254 l_dsplist(i));
1255 IF tp_dsplist.COUNT > 0 THEN
1256 resultlist(resultlist.COUNT).state := 1;
1257 resultlist := AddList( resultlist, tp_dsplist);
1258 END IF;
1259
1260 END LOOP;
1261 CLOSE c_contact_to_site2;
1262 RETURN resultlist;
1263 END IF;
1264 END;
1265
1266 END;