1 PACKAGE BODY arh_dqm_cust_helper AS
2 /*$Header: ARHDQMAB.pls 115.4 2002/05/31 18:34:16 pkm ship $*/
3
4 FUNCTION Is_cust_role_rel_dqm_pty
5 ( p_ctx_id IN NUMBER,
6 p_cust_account_role_id IN NUMBER,
7 p_status IN VARCHAR2)
8 RETURN VARCHAR2
9 IS
10 ret VARCHAR2(1);
11 CURSOR c_exist IS
12 SELECT 'Y'
13 FROM hz_cust_account_roles car,
14 hz_cust_accounts ca,
15 hz_matched_parties_gt mp
16 WHERE car.cust_account_role_id = p_cust_account_role_id
17 AND car.cust_account_id = ca.cust_account_id
18 AND DECODE(p_status,'ALL','ALL',NVL(car.status,'A'))= p_status
19 AND ca.party_id = mp.party_id
20 AND mp.search_context_id = p_ctx_id;
21 BEGIN
22 OPEN c_exist;
23 FETCH c_exist INTO ret;
24 IF c_exist%NOTFOUND THEN
25 ret := 'N';
26 END IF;
27 CLOSE c_exist;
28 RETURN ret;
29 END;
30
31 FUNCTION is_cust_acct_in_pty_gt
32 ------------------------------------------------------------------------
33 -- Return Y if the P_CUST_ACCOUNT_ID is already in HZ_MATCHED_PARTIES_GT
34 -- Otherwise N
35 ------------------------------------------------------------------------
36 ( p_ctx_id IN NUMBER,
37 p_cust_account_id IN NUMBER,
38 p_status IN VARCHAR2)
39 RETURN VARCHAR2
40 IS
41 CURSOR c1 IS
42 SELECT 'Y'
43 FROM hz_matched_parties_gt a,
44 hz_cust_accounts b
45 WHERE a.search_context_id = p_ctx_id
46 AND -a.party_id = p_cust_account_id
47 AND b.cust_account_id = p_cust_account_id
48 AND DECODE(p_status, 'ALL', 'ALL', NVL(b.status,'A')) = p_status
49 AND a.score < 0;
50 ret VARCHAR2(1);
51 BEGIN
52 OPEN c1;
53 FETCH c1 INTO ret;
54 IF c1%NOTFOUND THEN
55 ret := 'N';
56 END IF;
57 CLOSE c1;
58 RETURN ret;
59 END;
60
61 FUNCTION is_cust_role_in_ct_gt
62 ---------------------------------------------------------------------------------------
63 -- Return Y if the P_CUST_ACCOUNR_ROLE_ID is already inserted in HZ_MATCHED_CONTACTS_GT
64 -- Otherwise N
65 ---------------------------------------------------------------------------------------
66 ( p_ctx_id IN NUMBER,
67 p_cust_account_role_id IN NUMBER,
68 p_cust_account_id IN NUMBER,
69 p_status IN VARCHAR2)
70 RETURN VARCHAR2
71 IS
72 CURSOR c1 IS
73 SELECT 'Y'
74 FROM hz_matched_contacts_gt a,
75 hz_cust_account_roles b
76 WHERE a.search_context_id = p_ctx_id
77 AND -a.party_id = p_cust_account_id
78 AND -a.org_contact_id = p_cust_account_role_id
79 AND b.cust_account_role_id = p_cust_account_role_id
80 AND DECODE(p_status,'ALL','ALL',NVL(b.status,'A')) = p_status
81 AND a.score < 0;
82 ret VARCHAR2(1);
83 BEGIN
84 OPEN c1;
85 FETCH c1 INTO ret;
86 IF c1%NOTFOUND THEN
87 ret := 'N';
88 END IF;
89 CLOSE c1;
90 RETURN ret;
91 END;
92
93 FUNCTION Is_acct_site_in_ps_gt
94 ---------------------------------------------------------------------------------------
95 -- Return Y if the P_CUST_ACCT_SITE_ID is already inserted in HZ_MATCHED_PARTY_SITES_GT
96 -- Otherwise N
97 ---------------------------------------------------------------------------------------
98 ( p_ctx_id IN NUMBER,
99 p_cust_acct_site_id IN NUMBER,
100 p_cust_account_id IN NUMBER,
101 p_cur_all IN VARCHAR2,
102 p_status IN VARCHAR2)
103 RETURN VARCHAR2
104 IS
105 CURSOR c1 IS
106 SELECT 'Y'
107 FROM hz_matched_party_sites_gt a,
108 hz_cust_acct_sites b
109 WHERE a.search_context_id = p_ctx_id
110 AND -a.party_id = p_cust_account_id
111 AND -a.party_site_id = p_cust_acct_site_id
112 AND b.cust_account_id = p_cust_account_id
113 AND b.cust_acct_site_id = p_cust_acct_site_id
114 AND DECODE(p_status,'ALL','ALL',NVL(b.status,'A')) = p_status
115 AND a.score < 0;
116
117 CURSOR c2 IS
118 SELECT 'Y'
119 FROM hz_matched_party_sites_gt a,
120 hz_cust_acct_sites_all b
121 WHERE a.search_context_id = p_ctx_id
122 AND -a.party_id = p_cust_account_id
123 AND -a.party_site_id = p_cust_acct_site_id
124 AND b.cust_account_id = p_cust_account_id
125 AND b.cust_acct_site_id = p_cust_acct_site_id
126 AND DECODE(p_status,'ALL','ALL',NVL(b.status,'A')) = p_status
127 AND a.score < 0;
128 ret VARCHAR2(1);
129 BEGIN
130 IF p_cur_all = 'CUR' THEN
131 OPEN c1;
132 FETCH c1 INTO ret;
133 IF c1%NOTFOUND THEN
134 ret := 'N';
135 END IF;
136 CLOSE c1;
137 ELSIF p_cur_all = 'ALL' THEN
138 OPEN c2;
139 FETCH c2 INTO ret;
140 IF c2%NOTFOUND THEN
141 ret := 'N';
142 END IF;
143 CLOSE c2;
144 END IF;
145 RETURN ret;
146 END;
147
148 FUNCTION is_as_rel_dqm_pty
149 --------------------------------------------------------------------------------------------
150 -- Return Y if the P_CUST_ACCT_SITE_D is associated with a party_id in HZ_MATCHED_PARTIES_GT
151 -- Otherwise N
152 --------------------------------------------------------------------------------------------
153 ( p_ctx_id IN NUMBER,
154 p_cust_account_id IN NUMBER,
155 p_cust_acct_site_id IN NUMBER,
156 p_cur_all IN VARCHAR2,
157 p_status IN VARCHAR2)
158 RETURN VARCHAR2
159 IS
160 CURSOR c1 IS
161 SELECT 'Y'
162 FROM hz_cust_acct_sites a,
163 hz_party_sites c,
164 hz_matched_parties_gt d,
165 hz_parties e
166 WHERE a.cust_account_id = p_cust_account_id
167 AND a.cust_acct_site_id = p_cust_acct_site_id
168 AND DECODE(p_status,'ALL','ALL',NVL(a.status,'A')) = p_status
169 AND a.party_site_id = c.party_site_id
170 AND DECODE(p_status,'ALL','ALL',NVL(c.status,'A')) = p_status
171 AND c.party_id = d.party_id
172 AND NVL(d.score,0) >= 0
173 AND d.search_context_id = p_ctx_id
174 AND d.party_id = e.party_id
175 AND DECODE(p_status,'ALL','ALL',NVL(e.status,'A')) = p_status;
176
177 CURSOR c2 IS
178 SELECT 'Y'
179 FROM hz_cust_acct_sites_all a,
180 hz_party_sites c,
181 hz_matched_parties_gt d,
182 hz_parties e
183 WHERE a.cust_account_id = p_cust_account_id
184 AND a.cust_acct_site_id = p_cust_acct_site_id
185 AND DECODE(p_status,'ALL','ALL',NVL(a.status,'A')) = p_status
186 AND a.party_site_id = c.party_site_id
187 AND DECODE(p_status,'ALL','ALL',NVL(c.status,'A')) = p_status
188 AND c.party_id = d.party_id
189 AND NVL(d.score,0) >= 0
190 AND d.search_context_id = p_ctx_id
191 AND d.party_id = e.party_id
192 AND DECODE(p_status,'ALL','ALL',NVL(e.status,'A')) = p_status;
193
194 ret VARCHAR2(1);
195 BEGIN
196 IF p_cur_all = 'CUR' THEN
197 OPEN c1;
198 FETCH c1 INTO ret;
199 IF c1%NOTFOUND THEN
200 ret := 'N';
201 END IF;
202 CLOSE c1;
203 ELSIF p_cur_all = 'ALL' THEN
204 OPEN c2;
205 FETCH c2 INTO ret;
206 IF c2%NOTFOUND THEN
207 ret := 'N';
208 END IF;
209 CLOSE c2;
210 END IF;
211 RETURN ret;
212 END;
213
214 FUNCTION score_of_rel_ps
215 -------------------------------------------------------------------------------------------------------
216 -- Return the score of the party_site related to a cust_acct_site in HZ_MATCHED_PARTY_SITES_GT if found
217 -- Otherwise -99999
218 -------------------------------------------------------------------------------------------------------
219 ( p_ctx_id IN NUMBER,
220 p_cust_acct_site_id IN NUMBER,
221 p_cur_all IN VARCHAR2,
222 p_status IN VARCHAR2)
223 RETURN NUMBER
224 IS
225 CURSOR c1 IS
226 SELECT NVL(b.score,0)
227 FROM hz_cust_acct_sites a,
228 hz_matched_party_sites_gt b,
229 hz_party_sites c
230 WHERE a.cust_acct_site_id = p_cust_acct_site_id
231 AND DECODE(p_status,'ALL','ALL',NVL(a.status,'A')) = p_status
232 AND a.party_site_id = b.party_site_id
233 AND b.search_context_id = p_ctx_id
234 AND NVL(b.score,0) >= 0
235 AND b.party_site_id = c.party_site_id
236 AND DECODE(p_status,'ALL','ALL',NVL(c.status,'A')) = p_status;
237
238 CURSOR c2 IS
242 hz_party_sites c
239 SELECT NVL(b.score,0)
240 FROM hz_cust_acct_sites_all a,
241 hz_matched_party_sites_gt b,
243 WHERE a.cust_acct_site_id = p_cust_acct_site_id
244 AND DECODE(p_status,'ALL','ALL',NVL(a.status,'A')) = p_status
245 AND a.party_site_id = b.party_site_id
246 AND b.search_context_id = p_ctx_id
247 AND NVL(b.score,0) >= 0
248 AND b.party_site_id = c.party_site_id
249 AND DECODE(p_status,'ALL','ALL',NVL(c.status,'A')) = p_status;
250
251 ret NUMBER;
252 BEGIN
253 IF p_cur_all = 'CUR' THEN
254 OPEN c1;
255 FETCH c1 INTO ret;
256 IF c1%NOTFOUND THEN
257 ret := -99999;
258 END IF;
259 CLOSE c1;
260 ELSIF p_cur_all = 'ALL' THEN
261 OPEN c2;
262 FETCH c2 INTO ret;
263 IF c2%NOTFOUND THEN
264 ret := -99999;
265 END IF;
266 CLOSE c2;
267 END IF;
268 RETURN ret;
269 END;
270
271 PROCEDURE ins_as_in_ps_gt
272 -------------------------------------------------------------------------
273 -- Insert in CUST_ACCT_SITE_ID in HZ_MATCHED_PARTY_SITES_GT
274 -- If 1) the cust_acct_site_id is related to a matched party
275 -- 2) the cust_acct_site_id is not yet in HZ_MATCHED_PARTY_SITES_GT
276 -------------------------------------------------------------------------
277 -- CUST_ACCOUNT_ID CUST_ACCT_SITE_ID -PSscore(-1) SEARCH_CONTEXT_ID
278 -------------------------------------------------------------------------
282 p_cur_all IN VARCHAR2,
279 ( p_ctx_id IN NUMBER,
280 p_cust_account_id IN NUMBER,
281 p_cust_acct_site_id IN NUMBER,
283 p_status IN VARCHAR2)
284 IS
285 lyn VARCHAR2(1);
286 l_ps_score NUMBER;
287 BEGIN
288 lyn := is_as_rel_dqm_pty(p_ctx_id => p_ctx_id,
289 p_cust_account_id => p_cust_account_id,
293
290 p_cust_acct_site_id => p_cust_acct_site_id,
291 p_cur_all => p_cur_all,
292 p_status => p_status);
294 IF lyn = 'Y' THEN
295 lyn := Is_acct_site_in_ps_gt(p_ctx_id => p_ctx_id,
296 p_cust_acct_site_id => p_cust_acct_site_id,
297 p_cust_account_id => p_cust_account_id,
298 p_cur_all => p_cur_all,
299 p_status => p_status);
300 IF lyn = 'N' THEN
301
302 l_ps_score := score_of_rel_ps( p_ctx_id => p_ctx_id,
303 p_cust_acct_site_id => p_cust_acct_site_id,
304 p_cur_all => p_cur_all,
305 p_status => p_status);
306 IF l_ps_score > 0 THEN
307 l_ps_score := -l_ps_score;
308 ELSE
309 l_ps_score := -1;
310 END IF;
311
312 INSERT INTO hz_matched_party_sites_gt
313 ( PARTY_ID , PARTY_SITE_ID ,SCORE , SEARCH_CONTEXT_ID ) VALUES
314 ( -p_cust_account_id, -p_cust_acct_site_id ,l_ps_score , p_ctx_id );
315
316 END IF;
317
318 END IF;
319
320 END;
321
322 --HYU
323 PROCEDURE car_oc_treatment
324 ( p_ctx_id IN NUMBER,
325 p_cur_all IN VARCHAR2,
326 p_status IN VARCHAR2)
327 IS
328 CURSOR c1 IS
329 SELECT party_id,
330 org_contact_id,
331 score
332 FROM hz_matched_contacts_gt
333 WHERE search_context_id = p_ctx_id
334 AND score >= 0;
335 lrec c1%ROWTYPE;
336 BEGIN
337 OPEN c1;
338 LOOP
339 FETCH c1 INTO lrec;
340 EXIT WHEN c1%NOTFOUND;
341 ins_ca_car_in_gt( p_ctx_id => p_ctx_id,
342 p_org_contact_id => lrec.org_contact_id,
343 p_cur_all => p_cur_all,
344 p_status => p_status);
345 END LOOP;
346 CLOSE c1;
347 END;
348
349 FUNCTION is_as_rel_ps_gt
350 ( p_ctx_id IN NUMBER,
354 RETURN VARCHAR2
351 p_acct_site_id IN NUMBER,
352 p_cur_all IN VARCHAR2,
353 p_status IN VARCHAR2)
355 IS
356 CURSOR c1 IS
357 SELECT 'Y'
358 FROM hz_matched_party_sites_gt a,
359 hz_cust_acct_sites b
360 WHERE a.search_context_id = p_ctx_id
361 AND NVL(a.score,0) >= 0
362 AND a.party_site_id = b.party_site_id
363 AND b.cust_acct_site_id = p_acct_site_id
364 AND DECODE(p_status, 'ALL','ALL', NVL(b.status,'A')) = p_status;
365
366 CURSOR c2 IS
367 SELECT 'Y'
368 FROM hz_matched_party_sites_gt a,
369 hz_cust_acct_sites_all b
370 WHERE a.search_context_id = p_ctx_id
371 AND NVL(a.score,0) >= 0
372 AND a.party_site_id = b.party_site_id
373 AND b.cust_acct_site_id = p_acct_site_id
374 AND DECODE(p_status, 'ALL','ALL', NVL(b.status,'A')) = p_status;
375
376 ret VARCHAR2(1);
377 BEGIN
378 IF p_cur_all = 'CUR' THEN
379 OPEN c1;
380 FETCH c1 INTO ret;
381 IF c1%NOTFOUND THEN
382 ret := 'N';
383 END IF;
384 CLOSE c1;
385 ELSIF p_cur_all = 'ALL' THEN
386 OPEN c2;
387 FETCH c2 INTO ret;
388 IF c2%NOTFOUND THEN
389 ret := 'N';
390 END IF;
391 CLOSE c2;
392 END IF;
393 RETURN ret;
394 END;
395
396 PROCEDURE ins_ca_car_in_gt
397 -------------------------------------------------------------------
398 -- Treatement for HZ_CUST_ACCOUNT_ROLES
399 -------------------------------------------------------------------
400 ( p_ctx_id IN NUMBER,
401 p_org_contact_id IN NUMBER,
402 p_cur_all IN VARCHAR2,
403 p_status IN VARCHAR2)
404 IS
405 CURSOR c1 IS
406 SELECT a.score ct_score,
407 f.score pty_score,
408 d.cust_account_role_id,
409 d.cust_account_id,
410 d.cust_acct_site_id
411 FROM hz_matched_contacts_gt a,
412 hz_org_contacts b,
413 hz_relationships c,
414 hz_cust_account_roles d,
415 hz_cust_accounts e,
416 hz_matched_parties_gt f,
417 hz_parties g
418 WHERE a.search_context_id = p_ctx_id
419 AND a.org_contact_id = p_org_contact_id
420 AND NVL(a.score,0) >= 0
421 AND a.org_contact_id = b.org_contact_id
422 AND DECODE(p_status,'ALL','ALL',NVL(b.status,'A')) = p_status
423 AND b.party_relationship_id = c.relationship_id
424 AND c.directional_flag = 'F'
425 AND c.party_id = d.party_id
426 AND DECODE(p_status,'ALL','ALL',NVL(d.status,'A')) = p_status
427 AND d.cust_account_id = e.cust_account_id
428 AND DECODE(p_status,'ALL','ALL',NVL(e.status,'A')) = p_status
429 AND e.party_id = f.party_id
430 AND f.search_context_id = p_ctx_id
431 AND NVL(f.score,0) >= 0
432 AND f.party_id = a.party_id
433 AND f.party_id = g.party_id
434 AND DECODE(p_status,'ALL','ALL', NVL(g.status,'A'))= p_status;
435 lrec c1%ROWTYPE;
436 lyn VARCHAR2(1);
437 l_score VARCHAR2(30);
438 BEGIN
439 OPEN c1;
440 FETCH c1 INTO lrec;
441 IF c1%NOTFOUND THEN
442 NULL;
443 ELSE
444 ------------------------------------------------------
445 --{ Insert Cust Account Role in HZ_MATCHED_CONTACTS_GT
446 ------------------------------------------------------
447 lyn := is_cust_role_in_ct_gt( p_ctx_id => p_ctx_id,
448 p_cust_account_role_id => lrec.cust_account_role_id,
449 p_cust_account_id => lrec.cust_account_id,
450 p_status => p_status);
451 IF lyn = 'N' THEN
452
453 IF lrec.cust_acct_site_id IS NULL THEN
454
455 IF lrec.ct_score IS NULL THEN
456 l_score := -1;
457 ELSE
458 l_score := -lrec.ct_score;
459 END IF;
460 INSERT INTO hz_matched_contacts_gt
461 ( PARTY_ID , ORG_CONTACT_ID , SCORE , SEARCH_CONTEXT_ID) VALUES
462 ( -lrec.cust_account_id, -lrec.cust_account_role_id, l_score , p_ctx_id );
463
464 ------------------------------------------------
468 p_cust_account_id => lrec.cust_account_id,
465 --{ C Insert Cust Account in HZ_MATCHED_PARTIES_GT
466 ------------------------------------------------
467 lyn := is_cust_acct_in_pty_gt( p_ctx_id => p_ctx_id,
469 p_status => p_status);
470 IF lyn = 'N' THEN
471 IF lrec.pty_score IS NULL THEN
472 l_score := -1;
473 ELSE
474 l_score := -lrec.pty_score;
475 END IF;
476 INSERT INTO hz_matched_parties_gt
477 ( PARTY_ID , SCORE , SEARCH_CONTEXT_ID ) VALUES
478 ( -lrec.cust_account_id, l_score , p_ctx_id );
479 END IF;
480 --}
481
482 ELSIF lrec.cust_acct_site_id IS NOT NULL THEN
483 -- otherwise check if the cust_acct_site_id belongs is related to one of the party_site in gt
484 -- if y then INSERT cust_account_role + B + C
485 --HYU
486 lyn := is_as_rel_ps_gt(p_ctx_id => p_ctx_id,
487 p_acct_site_id => lrec.cust_acct_site_id,
488 p_cur_all => p_cur_all,
489 p_status => p_status);
490 IF lyn = 'Y' THEN
491 IF lrec.ct_score IS NULL THEN
492 l_score := -1;
493 ELSE
494 l_score := -lrec.ct_score;
495 END IF;
496 INSERT INTO hz_matched_contacts_gt
497 ( PARTY_ID , ORG_CONTACT_ID , SCORE , SEARCH_CONTEXT_ID) VALUES
498 ( -lrec.cust_account_id, -lrec.cust_account_role_id, l_score , p_ctx_id );
499
500 ---------------------------------------------------------
501 --{ B Insert Cust_Acct_site_id in HZ_MATCHED_PARTY_SITES_GT
502 ---------------------------------------------------------
503 ins_as_in_ps_gt( p_ctx_id => p_ctx_id,
504 p_cust_account_id => lrec.cust_account_id,
505 p_cust_acct_site_id => lrec.cust_acct_site_id,
506 p_cur_all => p_cur_all,
507 p_status => p_status);
508 --}
509
510 ------------------------------------------------
511 --{ C Insert Cust Account in HZ_MATCHED_PARTIES_GT
512 ------------------------------------------------
513 lyn := is_cust_acct_in_pty_gt( p_ctx_id => p_ctx_id,
514 p_cust_account_id => lrec.cust_account_id,
515 p_status => p_status);
516 IF lyn = 'N' THEN
517 IF lrec.pty_score IS NULL THEN
518 l_score := -1;
519 ELSE
520 l_score := -lrec.pty_score;
521 END IF;
522 INSERT INTO hz_matched_parties_gt
523 ( PARTY_ID , SCORE , SEARCH_CONTEXT_ID ) VALUES
524 ( -lrec.cust_account_id, l_score , p_ctx_id );
525
526 END IF;
527 --}
528 END IF;
529 -- HYU Replacement }
530 END IF;
531 END IF;
532 END IF;
533 END;
534
535 --HYU
536
537 FUNCTION score_rel_party
538 ( p_ctx_id IN NUMBER,
539 p_cust_account_id IN NUMBER,
540 p_status IN VARCHAR2)
541 RETURN NUMBER
542 IS
543 CURSOR c1 IS
544 SELECT NVL(score,0)
545 FROM hz_cust_accounts a,
546 hz_matched_parties_gt b
547 WHERE a.cust_account_id = p_cust_account_id
548 AND b.search_context_id = p_ctx_id
549 AND a.party_id = b.party_id
550 AND NVL(b.score,0) >= 0
551 AND DECODE(p_status,'ALL','ALL',NVL(a.status,'A')) = p_status;
552 ret NUMBER;
553 BEGIN
554 OPEN c1;
555 FETCH c1 INTO ret;
556 IF c1%NOTFOUND THEN
557 ret := -99999;
558 END IF;
559 CLOSE c1;
560 RETURN ret;
561 END;
562
563
564 PROCEDURE find_as_rel_ps
565 -----------------------------------------------------------------------------------------------
566 -- INSERT all the CUST_ACCT_SITE_ID related to the P_PARTY_SITE_ID in HZ_MATCHED_PARTY_SITES_GT
570 p_party_site_id IN NUMBER,
567 -- If necesary insert also the CUST_ACCOUNT_ID related in HZ_MATCHED_PARTIES_GT
568 -----------------------------------------------------------------------------------------------
569 ( p_ctx_id IN NUMBER,
571 p_score IN NUMBER,
572 p_cur_all IN VARCHAR2,
573 p_status IN VARCHAR2)
574 IS
575 TYPE nrc IS REF CURSOR;
576 cv nrc;
577 l_cust_acct_site_id NUMBER;
578 l_cust_account_id NUMBER;
579 lyn VARCHAR2(1);
580 l_score NUMBER;
581 BEGIN
582 IF p_cur_all = 'CUR' THEN
583 OPEN cv FOR
584 SELECT cust_acct_site_id,
585 cust_account_id
586 FROM hz_cust_acct_sites
587 WHERE party_site_id = p_party_site_id
588 AND DECODE(p_status,'ALL','ALL',NVL(status,'A')) = p_status;
589 ELSIF p_cur_all = 'ALL' THEN
590 OPEN cv FOR
591 SELECT cust_acct_site_id,
592 cust_account_id
593 FROM hz_cust_acct_sites_all
594 WHERE party_site_id = p_party_site_id
595 AND DECODE(p_status,'ALL','ALL',NVL(status,'A')) = p_status;
596 END IF;
597
598 IF cv%ISOPEN THEN
599 LOOP
600 FETCH cv INTO l_cust_acct_site_id, l_cust_account_id;
601 EXIT WHEN cv%NOTFOUND;
602
603 --{ Treatment of acct_site
604 lyn := is_as_rel_dqm_pty( p_ctx_id => p_ctx_id,
605 p_cust_account_id => l_cust_account_id,
606 p_cust_acct_site_id => l_cust_acct_site_id,
607 p_cur_all => p_cur_all,
608 p_status => p_status);
609 IF lyn = 'Y' THEN
610 IF p_score = 0 THEN
611 l_score := -1;
612 ELSE
613 l_score := -p_score;
614 END IF;
615 INSERT INTO hz_matched_party_sites_gt
616 ( PARTY_ID , PARTY_SITE_ID ,SCORE , SEARCH_CONTEXT_ID ) VALUES
617 ( -l_cust_account_id , -l_cust_acct_site_id ,l_score , p_ctx_id );
618 END IF;
619 --}
620
621 --{ Treatment of cust_account
622 lyn := is_cust_acct_in_pty_gt( p_ctx_id => p_ctx_id,
623 p_cust_account_id => l_cust_account_id,
624 p_status => p_status);
625 IF lyn = 'N' THEN
626 l_score := score_rel_party( p_ctx_id => p_ctx_id,
627 p_cust_account_id => l_cust_account_id,
628 p_status => p_status);
629 IF l_score <> -99999 THEN
630 IF l_score > 0 THEN
631 l_score := -l_score;
632 ELSE
633 l_score := -1;
634 END IF;
635 INSERT INTO hz_matched_parties_gt
636 ( PARTY_ID , SCORE , SEARCH_CONTEXT_ID ) VALUES
640 --}
637 ( -l_cust_account_id , l_score , p_ctx_id );
638 END IF;
639 END IF;
641 END LOOP;
642 CLOSE cv;
643 END IF;
644 END;
645
646
647 FUNCTION is_as_rel_ps_in_ps_gt
648 ------------------------------------------------------------------------------------------------------
649 -- Return Y if the CUST_ACCT_SITE_ID related to the P_PARTY_SITE_ID exist in HZ_MATCHED_PARTY_SITES_GT
650 ------------------------------------------------------------------------------------------------------
651 ( p_ctx_id IN NUMBER,
652 p_party_site_id IN NUMBER,
653 p_cur_all IN VARCHAR2,
654 p_status IN VARCHAR2)
655 RETURN VARCHAR2
656 IS
657 CURSOR c1 IS
658 SELECT 'Y'
659 FROM hz_cust_acct_sites b,
660 hz_matched_party_sites_gt c
661 WHERE b.party_site_id = p_party_site_id
662 AND b.cust_acct_site_id = -c.party_site_id
663 AND c.search_context_id = p_ctx_id
664 AND c.score < 0
665 AND DECODE(p_status,'ALL','ALL',NVL(b.status,'A')) = p_status;
666
667 CURSOR c2 IS
668 SELECT 'Y'
669 FROM hz_cust_acct_sites_all b,
670 hz_matched_party_sites_gt c
671 WHERE b.party_site_id = p_party_site_id
672 AND b.cust_acct_site_id = -c.party_site_id
673 AND c.search_context_id = p_ctx_id
674 AND c.score < 0
675 AND DECODE(p_status,'ALL','ALL',NVL(b.status,'A')) = p_status;
676
677 ret VARCHAR2(1);
678 BEGIN
679 IF p_cur_all = 'CUR' THEN
680 OPEN c1;
681 FETCH c1 INTO ret;
682 IF c1%NOTFOUND THEN
683 ret := 'N';
684 END IF;
685 CLOSE c1;
686 ELSIF p_cur_all = 'ALL' THEN
687 OPEN c2;
688 FETCH c2 INTO ret;
689 IF c2%NOTFOUND THEN
690 ret := 'N';
691 END IF;
692 CLOSE c2;
693 END IF;
694 RETURN ret;
695 END;
696
697 FUNCTION is_any_oc_rel_ps
698 ( p_ctx_id IN NUMBER,
699 p_ps_id IN NUMBER,
700 p_status IN VARCHAR2)
701 RETURN VARCHAR2
702 IS
703 CURSOR c1 IS
704 SELECT 'Y'
705 FROM hz_matched_contacts_gt a,
706 hz_org_contacts b
707 WHERE a.search_context_id = p_ctx_id
708 AND NVL(a.score,0) >= 0
709 AND a.org_contact_id = b.org_contact_id
710 AND b.party_site_id = p_ps_id
711 AND DECODE(p_status,'ALL','ALL',NVL(p_status,'A'))= p_status;
712 ret VARCHAR2(1);
713 BEGIN
714 OPEN c1;
715 FETCH c1 INTO ret;
716 IF c1%NOTFOUND THEN
717 ret := 'N';
718 END IF;
719 CLOSE c1;
720 RETURN ret;
721 END;
722
723 FUNCTION is_ps_rel_cpt_gt
724 ( p_ctx_id IN NUMBER,
725 p_ps_id IN NUMBER,
726 p_status IN VARCHAR2)
727 RETURN VARCHAR2
728 IS
729 CURSOR c1 IS
730 SELECT 'Y'
731 FROM hz_matched_cpts_gt a,
732 hz_contact_points b
733 WHERE a.search_context_id = p_ctx_id
734 AND a.contact_point_id = b.contact_point_id
735 AND b.owner_table_name = 'HZ_PARTY_SITES'
736 AND b.owner_table_id = p_ps_id
737 AND DECODE(p_status,'ALL','ALL',NVL(b.status,'A')) = p_status;
738 ret VARCHAR2(1);
739 BEGIN
740 OPEN c1;
741 FETCH c1 INTO ret;
742 IF c1%NOTFOUND THEN
743 ret := 'N';
744 END IF;
745 CLOSE c1;
746 RETURN ret;
747 END;
748
749 PROCEDURE as_ps_treatment
750 ------------------------------------------------------------------------
751 -- Cust Account Site / Party Site treatment in HZ_MATCHED_PARTY_SITES_GT
752 ------------------------------------------------------------------------
753 ( p_ctx_id IN NUMBER ,
754 p_cur_all IN VARCHAR2,
755 p_status IN VARCHAR2 )
756 IS
757 CURSOR c1 IS
758 SELECT a.party_site_id,
759 NVL(a.score,0) score
760 FROM hz_matched_party_sites_gt a
761 WHERE a.search_context_id = p_ctx_id
762 AND NVL(a.score,0) >= 0;
763
764 lrec c1%ROWTYPE;
765 lyn VARCHAR2(1);
766
767 BEGIN
768 OPEN c1;
769 LOOP
770 FETCH c1 INTO lrec;
771 EXIT WHEN c1%NOTFOUND;
772 /*
773 insert into hyu_matched_party_sites_gt
774 (PARTY_ID ,
775 PARTY_SITE_ID ,
776 SCORE ,
777 SEARCH_CONTEXT_ID ) values
778 ('9999',
779 lrec.party_site_id,
780 lrec.score,
781 p_ctx_id);
782 */
783 lyn := is_as_rel_ps_in_ps_gt( p_ctx_id => p_ctx_id,
784 p_party_site_id => lrec.party_site_id,
785 p_cur_all => p_cur_all,
786 p_status => p_status);
787 /*
788 insert into hyu_err
789 (COL1,
790 COL2,
791 COLN) values
792 (lrec.party_site_id,
793 lyn||' - '||p_cur_all||' - '||p_status,
794 p_ctx_id);
795 */
796
797
798 IF lyn = 'Y' THEN
799 NULL;
800 ELSE
801 IF lrec.score >= 0 THEN
802
803 --HYU 130202
804 lyn := is_ps_rel_cpt_gt( p_ctx_id => p_ctx_id,
805 p_ps_id => lrec.party_site_id,
806 p_status => p_status);
810 p_score => lrec.score,
807 IF lyn = 'Y' THEN
808 find_as_rel_ps( p_ctx_id => p_ctx_id,
809 p_party_site_id => lrec.party_site_id,
811 p_cur_all => p_cur_all,
812 p_status => p_status);
813 ELSE
814 lyn := is_any_oc_rel_ps( p_ctx_id => p_ctx_id,
815 p_ps_id => lrec.party_site_id,
816 p_status => p_status);
817 IF lyn = 'N' THEN
818 find_as_rel_ps( p_ctx_id => p_ctx_id,
819 p_party_site_id => lrec.party_site_id,
820 p_score => lrec.score,
821 p_cur_all => p_cur_all,
822 p_status => p_status);
823 END IF;
824 END IF;
825 END IF;
826 END IF;
827 END LOOP;
828 CLOSE c1;
829 END;
830
831
832 FUNCTION is_any_data_rel_pty
833 ( p_ctx_id IN NUMBER,
834 p_party_id IN NUMBER,
835 p_status IN VARCHAR2)
836 RETURN VARCHAR2
837 IS
838 CURSOR c1 IS
839 SELECT 'Y'
840 FROM hz_matched_party_sites_gt
841 WHERE search_context_id = p_ctx_id
842 AND NVL(score,0) >= 0
843 AND party_id = p_party_id;
844
845 CURSOR c2 IS
846 SELECT 'Y'
847 FROM hz_matched_contacts_gt
848 WHERE search_context_id = p_ctx_id
849 AND NVL(score,0) >= 0
850 AND party_id = p_party_id;
851 /*
852 CURSOR c3 IS
853 SELECT 'Y'
854 FROM hz_matched_cpts_gt
855 WHERE search_context_id = p_ctx_id
856 AND NVL(score,0) >= 0
857 AND party_id = p_party_id;
858 */
859 ret VARCHAR2(1);
860 BEGIN
861 OPEN c1;
862 FETCH c1 INTO ret;
863 IF c1%NOTFOUND THEN
864 OPEN c2;
865 FETCH c2 INTO ret;
866 IF c2%NOTFOUND THEN
867 ret := 'N';
868 /*
869 OPEN c3;
870 FETCH c3 INTO ret;
871 IF c3%NOTFOUND THEN
872 ret := 'N';
873 END IF;
874 CLOSE c3;
875 */
876 END IF;
877 CLOSE c2;
878 END IF;
879 CLOSE c1;
880 RETURN ret;
881 END;
882
883 FUNCTION is_pty_rel_cpt_gt
884 ( p_ctx_id IN NUMBER,
885 p_pty_id IN NUMBER,
886 p_status IN VARCHAR2)
887 RETURN VARCHAR2
888 IS
889 CURSOR c1 IS
890 SELECT 'Y'
891 FROM hz_matched_cpts_gt a,
892 hz_contact_points b
893 WHERE a.search_context_id = p_ctx_id
894 AND a.contact_point_id = b.contact_point_id
895 AND b.owner_table_name = 'HZ_PARTIES'
896 AND b.owner_table_id = p_pty_id
897 AND DECODE(p_status,'ALL','ALL',NVL(b.status,'A')) = p_status;
898 ret VARCHAR2(1);
899 BEGIN
900 OPEN c1;
901 FETCH c1 INTO ret;
902 IF c1%NOTFOUND THEN
903 ret := 'N';
904 END IF;
905 CLOSE c1;
906 RETURN ret;
907 END;
908
909 PROCEDURE ac_pty_treatment
910 -------------------------------------------------------
911 -- Account / Party Treatement in HZ_MATCHED_PARTIES_GT
912 -------------------------------------------------------
913 ( p_ctx_id IN NUMBER,
914 p_cur_all IN VARCHAR2,
915 p_status IN VARCHAR2)
916 IS
917 CURSOR c1 IS
918 SELECT party_id,
919 NVL(score,0) score
920 FROM hz_matched_parties_gt
921 WHERE NVL(score,0) >= 0
922 AND search_context_id = p_ctx_id;
923 lrec c1%ROWTYPE;
924 lyn VARCHAR2(1);
925 BEGIN
926 OPEN c1;
927 LOOP
928 FETCH c1 INTO lrec;
929 EXIT WHEN c1%NOTFOUND;
930 lyn := is_ac_rel_pty_in_p_gt( p_ctx_id => p_ctx_id,
931 p_party_id => lrec.party_id,
932 p_status => p_status);
933
934 IF lyn = 'Y' THEN
935 NULL;
936 ELSE
937 IF lrec.score >= 0 THEN
938
939 lyn := is_pty_rel_cpt_gt( p_ctx_id => p_ctx_id,
940 p_pty_id => lrec.party_id,
941 p_status => p_status);
942 IF lyn = 'Y' THEN
943 find_all_account_for_party( p_ctx_id => p_ctx_id,
944 p_party_id => lrec.party_id,
945 p_score => lrec.score,
946 p_cur_all => p_cur_all,
947 p_status => p_status);
948 ELSE
949 lyn := is_any_data_rel_pty( p_ctx_id => p_ctx_id,
950 p_party_id => lrec.party_id,
951 p_status => p_status);
952 IF lyn = 'N' THEN
953 find_all_account_for_party( p_ctx_id => p_ctx_id,
954 p_party_id => lrec.party_id,
955 p_score => lrec.score,
956 p_cur_all => p_cur_all,
957 p_status => p_status);
958 END IF;
959 END IF;
960 END IF;
961 END IF;
962 END LOOP;
963 CLOSE c1;
964 END;
965
966
967 FUNCTION is_any_cs_in_cur_org
968 (p_cust_account_id IN NUMBER,
969 p_status IN VARCHAR2)
970 RETURN VARCHAR2
971 IS
972 CURSOR c1 IS
973 SELECT 'Y'
974 FROM hz_cust_acct_sites
975 WHERE cust_account_id = p_cust_account_id
979 OPEN c1;
976 AND DECODE(p_status, 'ALL','ALL',NVL(status,'A')) = p_status;
977 lyn VARCHAR2(1);
978 BEGIN
980 FETCH c1 INTO lyn;
981 IF c1%NOTFOUND THEN
982 lyn := 'N';
983 END IF;
984 CLOSE c1;
985 RETURN lyn;
986 END;
987
988 PROCEDURE find_all_account_for_party
989 -------------------------------------------------------------------------
990 -- INSERT all_cust_account related to P_PARTY_ID in HZ_MATCHED_PARTIES_GT
991 -------------------------------------------------------------------------
992 ( p_ctx_id IN NUMBER,
993 p_party_id IN NUMBER,
994 p_score IN NUMBER,
995 p_cur_all IN VARCHAR2,
996 p_status IN VARCHAR2)
997 IS
998 CURSOR c1 IS
999 SELECT cust_account_id
1000 FROM hz_cust_accounts
1001 WHERE party_id = p_party_id
1002 AND DECODE(p_status, 'ALL','ALL',NVL(status,'A')) = p_status;
1003 lrec c1%ROWTYPE;
1004 l_score NUMBER;
1005 BEGIN
1006 OPEN c1;
1007 LOOP
1008 FETCH c1 INTO lrec;
1009 EXIT WHEN c1%NOTFOUND;
1010 IF p_score > 0 THEN
1011 l_score := -p_score;
1012 ELSE
1013 l_score := -1;
1014 END IF;
1015 IF p_cur_all = 'ALL' THEN
1016 INSERT INTO hz_matched_parties_gt
1017 ( PARTY_ID , SCORE , SEARCH_CONTEXT_ID ) VALUES
1018 ( -lrec.cust_account_id, l_score , p_ctx_id );
1019 ELSIF p_cur_all = 'CUR' THEN
1020 IF is_any_cs_in_cur_org(lrec.cust_account_id,p_status) = 'Y' THEN
1021 INSERT INTO hz_matched_parties_gt
1022 ( PARTY_ID , SCORE , SEARCH_CONTEXT_ID ) VALUES
1023 ( -lrec.cust_account_id, l_score , p_ctx_id );
1024 END IF;
1025 END IF;
1026 END LOOP;
1027 CLOSE c1;
1028 END;
1029
1030
1031 FUNCTION is_ac_rel_pty_in_p_gt
1032 ------------------------------------------------------------------------------------------------
1033 -- RETURN Y if the P_PARTY_ID has at least one CUST_ACCT_ID related to it in HZ_MATCHED_PARIES_GT
1034 -- Otherwise N
1035 ------------------------------------------------------------------------------------------------
1036 ( p_ctx_id IN NUMBER,
1037 p_party_id IN NUMBER,
1038 p_status IN VARCHAR2)
1039 RETURN VARCHAR2
1040 IS
1041 CURSOR c1 IS
1042 SELECT 'Y'
1043 FROM hz_cust_accounts a,
1044 hz_matched_parties_gt b
1045 WHERE a.party_id = p_party_id
1046 AND a.cust_account_id = -b.party_id
1047 AND b.search_context_id = p_ctx_id
1048 AND b.score < 0
1049 AND DECODE(p_status, 'ALL','ALL',NVL(a.status,'A')) = p_status;
1050 ret VARCHAR2(1);
1051 BEGIN
1052 OPEN c1;
1053 FETCH c1 INTO ret;
1054 IF c1%NOTFOUND THEN
1055 ret := 'N';
1056 END IF;
1057 CLOSE c1;
1058 RETURN ret;
1059 END;
1060
1061 END;
[Home] [Help]