28: -- ==========================================================================================
29:
30: TYPE vlisttype IS TABLE of VARCHAR2(255) INDEX BY BINARY_INTEGER ;
31: call_order vlisttype;
32: call_max_score HZ_PARTY_SEARCH.IDList;
33: call_type vlisttype;
34: g_party_stage_rec HZ_PARTY_STAGE.party_stage_rec_type;
35: g_party_site_stage_list HZ_PARTY_STAGE.party_site_stage_list;
36: g_contact_stage_list HZ_PARTY_STAGE.contact_stage_list;
34: g_party_stage_rec HZ_PARTY_STAGE.party_stage_rec_type;
35: g_party_site_stage_list HZ_PARTY_STAGE.party_site_stage_list;
36: g_contact_stage_list HZ_PARTY_STAGE.contact_stage_list;
37: g_contact_pt_stage_list HZ_PARTY_STAGE.contact_pt_stage_list;
38: g_mappings HZ_PARTY_SEARCH.IDList;
39: g_max_id NUMBER:=2000000000;
40: g_other_party_level_attribs BOOLEAN;
41:
42: g_debug_count NUMBER := 0;
348: PROCEDURE INSERT_PARTY_SCORE (
349: p_party_id IN NUMBER
350: ,p_record_id IN NUMBER
351: ,p_search_ctx_id IN NUMBER
352: ,p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
353: ,p_stage_rec IN HZ_PARTY_STAGE.party_stage_rec_type
354: ,p_table_TX39 VARCHAR2
355: ,p_table_TX41 VARCHAR2
356: ,p_table_TX44 VARCHAR2
442: PROCEDURE INSERT_PARTY_SITES_SCORE (
443: p_party_id IN NUMBER
444: ,p_record_id IN NUMBER
445: ,p_search_ctx_id IN NUMBER
446: ,p_search_rec IN HZ_PARTY_SEARCH.party_site_search_rec_type
447: ,p_stage_rec IN HZ_PARTY_STAGE.party_site_stage_rec_type
448: ,p_table_TX3 VARCHAR2
449: ,p_idx IN NUMBER) IS
450: l_current_score NUMBER:=0;
484: PROCEDURE INSERT_CONTACTS_SCORE (
485: p_party_id IN NUMBER
486: ,p_record_id IN NUMBER
487: ,p_search_ctx_id IN NUMBER
488: ,p_search_rec IN HZ_PARTY_SEARCH.contact_search_rec_type
489: ,p_stage_rec IN HZ_PARTY_STAGE.contact_stage_rec_type
490: ,p_idx IN NUMBER) IS
491: l_current_score NUMBER:=0;
492: l_score NUMBER;
500: PROCEDURE INSERT_CONTACT_POINTS_SCORE (
501: p_party_id IN NUMBER
502: ,p_record_id IN NUMBER
503: ,p_search_ctx_id IN NUMBER
504: ,p_search_rec IN HZ_PARTY_SEARCH.contact_point_search_rec_type
505: ,p_stage_rec IN HZ_PARTY_STAGE.contact_pt_stage_rec_type
506: ,p_idx IN NUMBER) IS
507: l_current_score NUMBER:=0;
508: l_score NUMBER;
519: for the PARTY Entity
520: ************************************************/
521:
522: PROCEDURE POP_PARTY_COND_REC(
523: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
524: ) IS
525: BEGIN
526: NULL ;
527: EXCEPTION
539: for the PARTY_SITES Entity
540: ************************************************/
541:
542: PROCEDURE POP_PARTY_SITES_COND_REC(
543: p_search_list IN HZ_PARTY_SEARCH.party_site_list
544: ) IS
545: BEGIN
546: NULL ;
547: EXCEPTION
559: for the CONTACTS Entity
560: ************************************************/
561:
562: PROCEDURE POP_CONTACTS_COND_REC(
563: p_search_list IN HZ_PARTY_SEARCH.contact_list
564: ) IS
565: BEGIN
566: NULL ;
567: EXCEPTION
579: for the CONTACT_POINTS Entity
580: ************************************************/
581:
582: PROCEDURE POP_CONTACT_POINTS_COND_REC(
583: p_search_list IN HZ_PARTY_SEARCH.contact_point_list
584: ) IS
585: BEGIN
586: NULL ;
587: EXCEPTION
593: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
594: END;
595:
596: PROCEDURE init_score_context (
597: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
598: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
599: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
600: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
601: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
594: END;
595:
596: PROCEDURE init_score_context (
597: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
598: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
599: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
600: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
601: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
602: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
595:
596: PROCEDURE init_score_context (
597: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
598: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
599: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
600: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
601: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
602: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
603: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
596: PROCEDURE init_score_context (
597: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
598: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
599: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
600: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
601: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
602: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
603: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
604: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
597: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
598: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
599: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
600: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
601: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
602: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
603: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
604: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
605: ) IS
598: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
599: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
600: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
601: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
602: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
603: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
604: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
605: ) IS
606: l_dummy NUMBER;
599: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
600: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
601: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
602: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
603: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
604: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
605: ) IS
606: l_dummy NUMBER;
607: BEGIN
600: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
601: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
602: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
603: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
604: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
605: ) IS
606: l_dummy NUMBER;
607: BEGIN
608: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
620: MAP_CONTACT_POINT_REC(FALSE,p_contact_point_list, l_dummy, g_contact_pt_stage_list);
621:
622: END;
623: FUNCTION init_search(
624: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
625: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
626: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
627: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
628: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
621:
622: END;
623: FUNCTION init_search(
624: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
625: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
626: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
627: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
628: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
629: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
622: END;
623: FUNCTION init_search(
624: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
625: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
626: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
627: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
628: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
629: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
630: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
623: FUNCTION init_search(
624: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
625: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
626: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
627: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
628: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
629: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
630: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
631: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
624: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
625: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
626: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
627: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
628: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
629: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
630: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
631: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
632: p_match_type IN VARCHAR2,
625: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
626: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
627: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
628: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
629: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
630: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
631: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
632: p_match_type IN VARCHAR2,
633: x_party_max_score OUT NUMBER,
626: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
627: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
628: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
629: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
630: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
631: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
632: p_match_type IN VARCHAR2,
633: x_party_max_score OUT NUMBER,
634: x_ps_max_score OUT NUMBER,
627: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
628: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
629: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
630: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
631: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
632: p_match_type IN VARCHAR2,
633: x_party_max_score OUT NUMBER,
634: x_ps_max_score OUT NUMBER,
635: x_contact_max_score OUT NUMBER,
639: l_ps_entered_max_score NUMBER:=0;
640: l_ct_entered_max_score NUMBER:=0;
641: l_cpt_entered_max_score NUMBER:=0;
642: vlist vlisttype;
643: maxscore HZ_PARTY_SEARCH.IDList;
644: l_name VARCHAR2(200);
645: l_idx NUMBER;
646: l_num NUMBER;
647: total NUMBER;
1970: p_party_id NUMBER,
1971: p_party_site_id NUMBER,
1972: p_org_contact_id NUMBER,
1973: p_contact_point_id NUMBER)
1974: RETURN HZ_PARTY_SEARCH.score_rec IS
1975: l_score_rec HZ_PARTY_SEARCH.score_rec;
1976: BEGIN
1977: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1978: hz_utility_v2pub.debug(p_message=>'-----------------',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1971: p_party_site_id NUMBER,
1972: p_org_contact_id NUMBER,
1973: p_contact_point_id NUMBER)
1974: RETURN HZ_PARTY_SEARCH.score_rec IS
1975: l_score_rec HZ_PARTY_SEARCH.score_rec;
1976: BEGIN
1977: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1978: hz_utility_v2pub.debug(p_message=>'-----------------',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1979: hz_utility_v2pub.debug(p_message=>'calling the function get_new_score_rec to set the l_score_rec structure',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2003: **********************************************************/
2004:
2005: PROCEDURE find_parties (
2006: p_rule_id IN NUMBER,
2007: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2008: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2009: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2010: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2011: p_restrict_sql IN VARCHAR2,
2004:
2005: PROCEDURE find_parties (
2006: p_rule_id IN NUMBER,
2007: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2008: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2009: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2010: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2011: p_restrict_sql IN VARCHAR2,
2012: p_match_type IN VARCHAR2,
2005: PROCEDURE find_parties (
2006: p_rule_id IN NUMBER,
2007: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2008: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2009: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2010: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2011: p_restrict_sql IN VARCHAR2,
2012: p_match_type IN VARCHAR2,
2013: p_search_merged IN VARCHAR2,
2006: p_rule_id IN NUMBER,
2007: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2008: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2009: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2010: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2011: p_restrict_sql IN VARCHAR2,
2012: p_match_type IN VARCHAR2,
2013: p_search_merged IN VARCHAR2,
2014: p_dup_party_id IN NUMBER,
2042: l_TX39 VARCHAR2(2000);
2043: l_TX41 VARCHAR2(2000);
2044: l_TX44 VARCHAR2(2000);
2045: l_TX3 VARCHAR2(2000);
2046: H_SCORES HZ_PARTY_SEARCH.score_list;
2047: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2048: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2049:
2050: l_score NUMBER;
2043: l_TX41 VARCHAR2(2000);
2044: l_TX44 VARCHAR2(2000);
2045: l_TX3 VARCHAR2(2000);
2046: H_SCORES HZ_PARTY_SEARCH.score_list;
2047: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2048: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2049:
2050: l_score NUMBER;
2051: l_idx NUMBER;
2044: l_TX44 VARCHAR2(2000);
2045: l_TX3 VARCHAR2(2000);
2046: H_SCORES HZ_PARTY_SEARCH.score_list;
2047: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2048: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2049:
2050: l_score NUMBER;
2051: l_idx NUMBER;
2052: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
2236: /** Private procedure to acquire and score at party site level ***/
2237: PROCEDURE eval_party_site_level(p_party_site_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
2238: l_party_id_idx NUMBER:=1;
2239: l_ctx_id NUMBER;
2240: h_ps_id HZ_PARTY_SEARCH.IDList;
2241: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2242: h_ps_score HZ_PARTY_SEARCH.IDList;
2243: detcnt NUMBER := 1;
2244: BEGIN
2237: PROCEDURE eval_party_site_level(p_party_site_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
2238: l_party_id_idx NUMBER:=1;
2239: l_ctx_id NUMBER;
2240: h_ps_id HZ_PARTY_SEARCH.IDList;
2241: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2242: h_ps_score HZ_PARTY_SEARCH.IDList;
2243: detcnt NUMBER := 1;
2244: BEGIN
2245: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2238: l_party_id_idx NUMBER:=1;
2239: l_ctx_id NUMBER;
2240: h_ps_id HZ_PARTY_SEARCH.IDList;
2241: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2242: h_ps_score HZ_PARTY_SEARCH.IDList;
2243: detcnt NUMBER := 1;
2244: BEGIN
2245: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2246: hz_utility_v2pub.debug(p_message=>'-----------------',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2358: /** Private procedure to acquire and score at contact point level ***/
2359: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
2360: l_party_id_idx NUMBER:=1;
2361: l_ctx_id NUMBER;
2362: h_ct_id HZ_PARTY_SEARCH.IDList;
2363: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2364: h_ct_score HZ_PARTY_SEARCH.IDList;
2365: detcnt NUMBER := 1;
2366: BEGIN
2359: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
2360: l_party_id_idx NUMBER:=1;
2361: l_ctx_id NUMBER;
2362: h_ct_id HZ_PARTY_SEARCH.IDList;
2363: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2364: h_ct_score HZ_PARTY_SEARCH.IDList;
2365: detcnt NUMBER := 1;
2366: BEGIN
2367: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2360: l_party_id_idx NUMBER:=1;
2361: l_ctx_id NUMBER;
2362: h_ct_id HZ_PARTY_SEARCH.IDList;
2363: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2364: h_ct_score HZ_PARTY_SEARCH.IDList;
2365: detcnt NUMBER := 1;
2366: BEGIN
2367: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2368: hz_utility_v2pub.debug(p_message=>'-----------------',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2467: /** Private procedure to acquire and score at contact point level ***/
2468: PROCEDURE eval_cpt_level(p_contact_pt_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER, p_ins_details VARCHAR2,p_emax_score NUMBER) IS
2469: l_party_id_idx NUMBER:=1;
2470: l_ctx_id NUMBER;
2471: h_cpt_id HZ_PARTY_SEARCH.IDList;
2472: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2473: h_cpt_score HZ_PARTY_SEARCH.IDList;
2474: detcnt NUMBER := 1;
2475: l_cpt_flag VARCHAR2(1) := 'N';
2468: PROCEDURE eval_cpt_level(p_contact_pt_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER, p_ins_details VARCHAR2,p_emax_score NUMBER) IS
2469: l_party_id_idx NUMBER:=1;
2470: l_ctx_id NUMBER;
2471: h_cpt_id HZ_PARTY_SEARCH.IDList;
2472: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2473: h_cpt_score HZ_PARTY_SEARCH.IDList;
2474: detcnt NUMBER := 1;
2475: l_cpt_flag VARCHAR2(1) := 'N';
2476: is_a_match VARCHAR2(1) := 'Y';
2469: l_party_id_idx NUMBER:=1;
2470: l_ctx_id NUMBER;
2471: h_cpt_id HZ_PARTY_SEARCH.IDList;
2472: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2473: h_cpt_score HZ_PARTY_SEARCH.IDList;
2474: detcnt NUMBER := 1;
2475: l_cpt_flag VARCHAR2(1) := 'N';
2476: is_a_match VARCHAR2(1) := 'Y';
2477: BEGIN
2918: END find_parties;
2919:
2920: PROCEDURE find_persons (
2921: p_rule_id IN NUMBER,
2922: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2923: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2924: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2925: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2926: p_restrict_sql IN VARCHAR2,
2919:
2920: PROCEDURE find_persons (
2921: p_rule_id IN NUMBER,
2922: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2923: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2924: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2925: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2926: p_restrict_sql IN VARCHAR2,
2927: p_match_type IN VARCHAR2,
2920: PROCEDURE find_persons (
2921: p_rule_id IN NUMBER,
2922: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2923: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2924: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2925: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2926: p_restrict_sql IN VARCHAR2,
2927: p_match_type IN VARCHAR2,
2928: p_search_merged IN VARCHAR2,
2921: p_rule_id IN NUMBER,
2922: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2923: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2924: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2925: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2926: p_restrict_sql IN VARCHAR2,
2927: p_match_type IN VARCHAR2,
2928: p_search_merged IN VARCHAR2,
2929: p_dup_party_id IN NUMBER,
2957: l_TX39 VARCHAR2(2000);
2958: l_TX41 VARCHAR2(2000);
2959: l_TX44 VARCHAR2(2000);
2960: l_TX3 VARCHAR2(2000);
2961: H_SCORES HZ_PARTY_SEARCH.score_list;
2962: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2963: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2964:
2965: l_score NUMBER;
2958: l_TX41 VARCHAR2(2000);
2959: l_TX44 VARCHAR2(2000);
2960: l_TX3 VARCHAR2(2000);
2961: H_SCORES HZ_PARTY_SEARCH.score_list;
2962: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2963: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2964:
2965: l_score NUMBER;
2966: l_idx NUMBER;
2959: l_TX44 VARCHAR2(2000);
2960: l_TX3 VARCHAR2(2000);
2961: H_SCORES HZ_PARTY_SEARCH.score_list;
2962: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2963: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2964:
2965: l_score NUMBER;
2966: l_idx NUMBER;
2967: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
3199: /** Private procedure to acquire and score at party site level ***/
3200: PROCEDURE eval_party_site_level(p_party_site_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3201: l_party_id_idx NUMBER:=1;
3202: l_ctx_id NUMBER;
3203: h_ps_id HZ_PARTY_SEARCH.IDList;
3204: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3205: h_ps_score HZ_PARTY_SEARCH.IDList;
3206: detcnt NUMBER := 1;
3207: l_person_id NUMBER;
3200: PROCEDURE eval_party_site_level(p_party_site_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3201: l_party_id_idx NUMBER:=1;
3202: l_ctx_id NUMBER;
3203: h_ps_id HZ_PARTY_SEARCH.IDList;
3204: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3205: h_ps_score HZ_PARTY_SEARCH.IDList;
3206: detcnt NUMBER := 1;
3207: l_person_id NUMBER;
3208: BEGIN
3201: l_party_id_idx NUMBER:=1;
3202: l_ctx_id NUMBER;
3203: h_ps_id HZ_PARTY_SEARCH.IDList;
3204: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3205: h_ps_score HZ_PARTY_SEARCH.IDList;
3206: detcnt NUMBER := 1;
3207: l_person_id NUMBER;
3208: BEGIN
3209: SAVEPOINT eval_start;
3298: /** Private procedure to acquire and score at party site level ***/
3299: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3300: l_party_id_idx NUMBER:=1;
3301: l_ctx_id NUMBER;
3302: h_ct_id HZ_PARTY_SEARCH.IDList;
3303: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3304: h_ct_score HZ_PARTY_SEARCH.IDList;
3305: detcnt NUMBER := 1;
3306: l_person_id NUMBER;
3299: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3300: l_party_id_idx NUMBER:=1;
3301: l_ctx_id NUMBER;
3302: h_ct_id HZ_PARTY_SEARCH.IDList;
3303: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3304: h_ct_score HZ_PARTY_SEARCH.IDList;
3305: detcnt NUMBER := 1;
3306: l_person_id NUMBER;
3307: BEGIN
3300: l_party_id_idx NUMBER:=1;
3301: l_ctx_id NUMBER;
3302: h_ct_id HZ_PARTY_SEARCH.IDList;
3303: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3304: h_ct_score HZ_PARTY_SEARCH.IDList;
3305: detcnt NUMBER := 1;
3306: l_person_id NUMBER;
3307: BEGIN
3308: SAVEPOINT eval_start;
3356: /** Private procedure to acquire and score at contact point level ***/
3357: PROCEDURE eval_cpt_level(p_contact_pt_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER, p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3358: l_party_id_idx NUMBER:=1;
3359: l_ctx_id NUMBER;
3360: h_cpt_id HZ_PARTY_SEARCH.IDList;
3361: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3362: h_cpt_score HZ_PARTY_SEARCH.IDList;
3363: detcnt NUMBER := 1;
3364: l_person_id NUMBER;
3357: PROCEDURE eval_cpt_level(p_contact_pt_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER, p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3358: l_party_id_idx NUMBER:=1;
3359: l_ctx_id NUMBER;
3360: h_cpt_id HZ_PARTY_SEARCH.IDList;
3361: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3362: h_cpt_score HZ_PARTY_SEARCH.IDList;
3363: detcnt NUMBER := 1;
3364: l_person_id NUMBER;
3365: is_a_match VARCHAR2(1) := 'Y';
3358: l_party_id_idx NUMBER:=1;
3359: l_ctx_id NUMBER;
3360: h_cpt_id HZ_PARTY_SEARCH.IDList;
3361: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3362: h_cpt_score HZ_PARTY_SEARCH.IDList;
3363: detcnt NUMBER := 1;
3364: l_person_id NUMBER;
3365: is_a_match VARCHAR2(1) := 'Y';
3366: l_cpt_flag VARCHAR2(1) := 'N';
3700: END find_persons;
3701:
3702: PROCEDURE find_persons (
3703: p_rule_id IN NUMBER,
3704: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3705: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3706: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3707: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3708: p_restrict_sql IN VARCHAR2,
3701:
3702: PROCEDURE find_persons (
3703: p_rule_id IN NUMBER,
3704: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3705: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3706: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3707: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3708: p_restrict_sql IN VARCHAR2,
3709: p_match_type IN VARCHAR2,
3702: PROCEDURE find_persons (
3703: p_rule_id IN NUMBER,
3704: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3705: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3706: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3707: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3708: p_restrict_sql IN VARCHAR2,
3709: p_match_type IN VARCHAR2,
3710: p_search_merged IN VARCHAR2,
3703: p_rule_id IN NUMBER,
3704: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3705: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3706: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3707: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3708: p_restrict_sql IN VARCHAR2,
3709: p_match_type IN VARCHAR2,
3710: p_search_merged IN VARCHAR2,
3711: p_ins_details IN VARCHAR2,
3717: find_persons(p_rule_id,p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list,p_restrict_sql,p_match_type,p_search_merged,null,null,null,p_ins_details,x_search_ctx_id,x_num_matches);
3718: END;
3719: PROCEDURE find_party_details (
3720: p_rule_id IN NUMBER,
3721: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3722: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3723: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3724: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3725: p_restrict_sql IN VARCHAR2,
3718: END;
3719: PROCEDURE find_party_details (
3720: p_rule_id IN NUMBER,
3721: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3722: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3723: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3724: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3725: p_restrict_sql IN VARCHAR2,
3726: p_match_type IN VARCHAR2,
3719: PROCEDURE find_party_details (
3720: p_rule_id IN NUMBER,
3721: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3722: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3723: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3724: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3725: p_restrict_sql IN VARCHAR2,
3726: p_match_type IN VARCHAR2,
3727: p_search_merged IN VARCHAR2,
3720: p_rule_id IN NUMBER,
3721: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3722: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3723: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3724: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3725: p_restrict_sql IN VARCHAR2,
3726: p_match_type IN VARCHAR2,
3727: p_search_merged IN VARCHAR2,
3728: x_search_ctx_id OUT NUMBER,
3777: x_dup_set_id OUT NUMBER,
3778: x_search_ctx_id OUT NUMBER,
3779: x_num_matches OUT NUMBER
3780: ) IS
3781: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3782: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3783: l_contact_list HZ_PARTY_SEARCH.contact_list;
3784: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3785: l_match_idx NUMBER;
3778: x_search_ctx_id OUT NUMBER,
3779: x_num_matches OUT NUMBER
3780: ) IS
3781: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3782: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3783: l_contact_list HZ_PARTY_SEARCH.contact_list;
3784: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3785: l_match_idx NUMBER;
3786:
3779: x_num_matches OUT NUMBER
3780: ) IS
3781: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3782: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3783: l_contact_list HZ_PARTY_SEARCH.contact_list;
3784: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3785: l_match_idx NUMBER;
3786:
3787: --Fix for bug 4417124
3780: ) IS
3781: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3782: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3783: l_contact_list HZ_PARTY_SEARCH.contact_list;
3784: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3785: l_match_idx NUMBER;
3786:
3787: --Fix for bug 4417124
3788: l_use_contact_addr_info BOOLEAN := TRUE;
3893: x_search_ctx_id OUT NUMBER,
3894: x_num_matches OUT NUMBER
3895: ) IS
3896:
3897: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3898: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3899: l_contact_list HZ_PARTY_SEARCH.contact_list;
3900: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3901: contact_point_ids HZ_PARTY_SEARCH.IDList;
3894: x_num_matches OUT NUMBER
3895: ) IS
3896:
3897: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3898: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3899: l_contact_list HZ_PARTY_SEARCH.contact_list;
3900: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3901: contact_point_ids HZ_PARTY_SEARCH.IDList;
3902: p_party_site_list HZ_PARTY_SEARCH.IDList;
3895: ) IS
3896:
3897: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3898: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3899: l_contact_list HZ_PARTY_SEARCH.contact_list;
3900: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3901: contact_point_ids HZ_PARTY_SEARCH.IDList;
3902: p_party_site_list HZ_PARTY_SEARCH.IDList;
3903: p_contact_ids HZ_PARTY_SEARCH.IDList;
3896:
3897: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3898: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3899: l_contact_list HZ_PARTY_SEARCH.contact_list;
3900: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3901: contact_point_ids HZ_PARTY_SEARCH.IDList;
3902: p_party_site_list HZ_PARTY_SEARCH.IDList;
3903: p_contact_ids HZ_PARTY_SEARCH.IDList;
3904: l_match_idx NUMBER;
3897: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3898: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3899: l_contact_list HZ_PARTY_SEARCH.contact_list;
3900: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3901: contact_point_ids HZ_PARTY_SEARCH.IDList;
3902: p_party_site_list HZ_PARTY_SEARCH.IDList;
3903: p_contact_ids HZ_PARTY_SEARCH.IDList;
3904: l_match_idx NUMBER;
3905: cursor get_cpts_for_party_sites is select contact_point_id
3898: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3899: l_contact_list HZ_PARTY_SEARCH.contact_list;
3900: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3901: contact_point_ids HZ_PARTY_SEARCH.IDList;
3902: p_party_site_list HZ_PARTY_SEARCH.IDList;
3903: p_contact_ids HZ_PARTY_SEARCH.IDList;
3904: l_match_idx NUMBER;
3905: cursor get_cpts_for_party_sites is select contact_point_id
3906: from hz_contact_points
3899: l_contact_list HZ_PARTY_SEARCH.contact_list;
3900: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3901: contact_point_ids HZ_PARTY_SEARCH.IDList;
3902: p_party_site_list HZ_PARTY_SEARCH.IDList;
3903: p_contact_ids HZ_PARTY_SEARCH.IDList;
3904: l_match_idx NUMBER;
3905: cursor get_cpts_for_party_sites is select contact_point_id
3906: from hz_contact_points
3907: where owner_table_name = 'HZ_PARTY_SITES'
3928:
3929: get_search_criteria (
3930: null,
3931: p_party_site_list,
3932: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
3933: contact_point_ids,
3934: l_party_search_rec,
3935: l_party_site_list,
3936: l_contact_list,
3978: x_search_ctx_id OUT NUMBER,
3979: x_num_matches OUT NUMBER
3980: ) IS
3981:
3982: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3983: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3984: l_contact_list HZ_PARTY_SEARCH.contact_list;
3985: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3986: contact_point_ids HZ_PARTY_SEARCH.IDList;
3979: x_num_matches OUT NUMBER
3980: ) IS
3981:
3982: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3983: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3984: l_contact_list HZ_PARTY_SEARCH.contact_list;
3985: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3986: contact_point_ids HZ_PARTY_SEARCH.IDList;
3987: p_party_site_list HZ_PARTY_SEARCH.IDList;
3980: ) IS
3981:
3982: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3983: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3984: l_contact_list HZ_PARTY_SEARCH.contact_list;
3985: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3986: contact_point_ids HZ_PARTY_SEARCH.IDList;
3987: p_party_site_list HZ_PARTY_SEARCH.IDList;
3988: p_contact_ids HZ_PARTY_SEARCH.IDList;
3981:
3982: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3983: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3984: l_contact_list HZ_PARTY_SEARCH.contact_list;
3985: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3986: contact_point_ids HZ_PARTY_SEARCH.IDList;
3987: p_party_site_list HZ_PARTY_SEARCH.IDList;
3988: p_contact_ids HZ_PARTY_SEARCH.IDList;
3989: l_match_idx NUMBER;
3982: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3983: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3984: l_contact_list HZ_PARTY_SEARCH.contact_list;
3985: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3986: contact_point_ids HZ_PARTY_SEARCH.IDList;
3987: p_party_site_list HZ_PARTY_SEARCH.IDList;
3988: p_contact_ids HZ_PARTY_SEARCH.IDList;
3989: l_match_idx NUMBER;
3990: cursor get_cpt_for_contact_id is select contact_point_id
3983: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3984: l_contact_list HZ_PARTY_SEARCH.contact_list;
3985: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3986: contact_point_ids HZ_PARTY_SEARCH.IDList;
3987: p_party_site_list HZ_PARTY_SEARCH.IDList;
3988: p_contact_ids HZ_PARTY_SEARCH.IDList;
3989: l_match_idx NUMBER;
3990: cursor get_cpt_for_contact_id is select contact_point_id
3991: from hz_org_contacts a, hz_relationships b, hz_contact_points c
3984: l_contact_list HZ_PARTY_SEARCH.contact_list;
3985: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3986: contact_point_ids HZ_PARTY_SEARCH.IDList;
3987: p_party_site_list HZ_PARTY_SEARCH.IDList;
3988: p_contact_ids HZ_PARTY_SEARCH.IDList;
3989: l_match_idx NUMBER;
3990: cursor get_cpt_for_contact_id is select contact_point_id
3991: from hz_org_contacts a, hz_relationships b, hz_contact_points c
3992: where a.party_relationship_id = b.relationship_id
4015: CLOSE get_cpt_for_contact_id;
4016:
4017: get_search_criteria (
4018: null,
4019: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4020: p_contact_ids,
4021: contact_point_ids,
4022: l_party_search_rec,
4023: l_party_site_list,
4066: p_match_type IN VARCHAR2,
4067: x_search_ctx_id OUT NUMBER,
4068: x_num_matches OUT NUMBER
4069: ) IS
4070: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4071: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4072: l_contact_list HZ_PARTY_SEARCH.contact_list;
4073: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4074: contact_point_ids HZ_PARTY_SEARCH.IDList;
4067: x_search_ctx_id OUT NUMBER,
4068: x_num_matches OUT NUMBER
4069: ) IS
4070: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4071: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4072: l_contact_list HZ_PARTY_SEARCH.contact_list;
4073: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4074: contact_point_ids HZ_PARTY_SEARCH.IDList;
4075: p_party_site_list HZ_PARTY_SEARCH.IDList;
4068: x_num_matches OUT NUMBER
4069: ) IS
4070: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4071: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4072: l_contact_list HZ_PARTY_SEARCH.contact_list;
4073: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4074: contact_point_ids HZ_PARTY_SEARCH.IDList;
4075: p_party_site_list HZ_PARTY_SEARCH.IDList;
4076: p_contact_ids HZ_PARTY_SEARCH.IDList;
4069: ) IS
4070: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4071: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4072: l_contact_list HZ_PARTY_SEARCH.contact_list;
4073: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4074: contact_point_ids HZ_PARTY_SEARCH.IDList;
4075: p_party_site_list HZ_PARTY_SEARCH.IDList;
4076: p_contact_ids HZ_PARTY_SEARCH.IDList;
4077: l_match_idx NUMBER;
4070: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4071: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4072: l_contact_list HZ_PARTY_SEARCH.contact_list;
4073: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4074: contact_point_ids HZ_PARTY_SEARCH.IDList;
4075: p_party_site_list HZ_PARTY_SEARCH.IDList;
4076: p_contact_ids HZ_PARTY_SEARCH.IDList;
4077: l_match_idx NUMBER;
4078:
4071: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4072: l_contact_list HZ_PARTY_SEARCH.contact_list;
4073: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4074: contact_point_ids HZ_PARTY_SEARCH.IDList;
4075: p_party_site_list HZ_PARTY_SEARCH.IDList;
4076: p_contact_ids HZ_PARTY_SEARCH.IDList;
4077: l_match_idx NUMBER;
4078:
4079: BEGIN
4072: l_contact_list HZ_PARTY_SEARCH.contact_list;
4073: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4074: contact_point_ids HZ_PARTY_SEARCH.IDList;
4075: p_party_site_list HZ_PARTY_SEARCH.IDList;
4076: p_contact_ids HZ_PARTY_SEARCH.IDList;
4077: l_match_idx NUMBER;
4078:
4079: BEGIN
4080: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4089: END IF;
4090: contact_point_ids(1) := p_contact_point_id;
4091: get_search_criteria (
4092: null,
4093: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4094: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4095: contact_point_ids,
4096: l_party_search_rec,
4097: l_party_site_list,
4090: contact_point_ids(1) := p_contact_point_id;
4091: get_search_criteria (
4092: null,
4093: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4094: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4095: contact_point_ids,
4096: l_party_search_rec,
4097: l_party_site_list,
4098: l_contact_list,
4243: ) IS
4244: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4245: l_match_idx NUMBER;
4246: AttrVals AttrList;
4247: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4248: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4249: l_contact_list HZ_PARTY_SEARCH.contact_list;
4250: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4251: l_dup_set_id NUMBER;
4244: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4245: l_match_idx NUMBER;
4246: AttrVals AttrList;
4247: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4248: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4249: l_contact_list HZ_PARTY_SEARCH.contact_list;
4250: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4251: l_dup_set_id NUMBER;
4252: l_idx NUMBER;
4245: l_match_idx NUMBER;
4246: AttrVals AttrList;
4247: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4248: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4249: l_contact_list HZ_PARTY_SEARCH.contact_list;
4250: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4251: l_dup_set_id NUMBER;
4252: l_idx NUMBER;
4253: l_cpt_type VARCHAR2(255);
4246: AttrVals AttrList;
4247: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4248: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4249: l_contact_list HZ_PARTY_SEARCH.contact_list;
4250: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4251: l_dup_set_id NUMBER;
4252: l_idx NUMBER;
4253: l_cpt_type VARCHAR2(255);
4254: FIRST BOOLEAN := TRUE;
4401:
4402: PROCEDURE get_matching_party_sites (
4403: p_rule_id IN NUMBER,
4404: p_party_id IN NUMBER,
4405: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4406: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4407: p_restrict_sql IN VARCHAR2,
4408: p_match_type IN VARCHAR2,
4409: p_dup_party_site_id IN NUMBER,
4402: PROCEDURE get_matching_party_sites (
4403: p_rule_id IN NUMBER,
4404: p_party_id IN NUMBER,
4405: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4406: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4407: p_restrict_sql IN VARCHAR2,
4408: p_match_type IN VARCHAR2,
4409: p_dup_party_site_id IN NUMBER,
4410: x_search_ctx_id OUT NUMBER,
4428: FIRST BOOLEAN;
4429: l_search_ctx_id NUMBER; -- Generated Search Context ID
4430:
4431: l_TX3 VARCHAR2(2000);
4432: H_SCORES HZ_PARTY_SEARCH.score_list;
4433:
4434: l_score NUMBER;
4435: l_idx NUMBER;
4436: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
4459: defps NUMBER :=0;
4460: defct NUMBER :=0;
4461: defcpt NUMBER :=0;
4462: l_index NUMBER;
4463: l_match_ps_list HZ_PARTY_SEARCH.IDList;
4464: l_cnt NUMBER:=1;
4465:
4466:
4467: BEGIN
4483: l_match_str := ' AND ';
4484: ELSIF p_match_type = 'OR' THEN
4485: l_match_str := ' OR ';
4486: END IF;
4487: l_entered_max_score:= init_search( HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC, p_party_site_list, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST, p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
4488: g_score_until_thresh := false;
4489: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4490:
4491: -- ************************************************************
4491: -- ************************************************************
4492: -- STEP 2. Setup of intermedia query strings for Acquisition query
4493: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4494: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4495: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,p_party_site_list,HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,p_contact_point_list);
4496:
4497: -- Setup Search Context ID
4498: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4499: x_search_ctx_id := l_search_ctx_id;
4634:
4635: PROCEDURE get_matching_contacts (
4636: p_rule_id IN NUMBER,
4637: p_party_id IN NUMBER,
4638: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4639: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4640: p_restrict_sql IN VARCHAR2,
4641: p_match_type IN VARCHAR2,
4642: p_dup_contact_id IN NUMBER,
4635: PROCEDURE get_matching_contacts (
4636: p_rule_id IN NUMBER,
4637: p_party_id IN NUMBER,
4638: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4639: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4640: p_restrict_sql IN VARCHAR2,
4641: p_match_type IN VARCHAR2,
4642: p_dup_contact_id IN NUMBER,
4643: x_search_ctx_id OUT NUMBER,
4660: l_entered_max_score NUMBER;
4661: FIRST BOOLEAN;
4662: l_search_ctx_id NUMBER; -- Generated Search Context ID
4663:
4664: H_SCORES HZ_PARTY_SEARCH.score_list;
4665:
4666: l_score NUMBER;
4667: l_idx NUMBER;
4668: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
4688: l_party_max_score NUMBER;
4689: l_ps_max_score NUMBER;
4690: l_contact_max_score NUMBER;
4691: l_cpt_max_score NUMBER;
4692: l_match_contact_list HZ_PARTY_SEARCH.IDList;
4693: l_cnt NUMBER:=1;
4694:
4695:
4696: BEGIN
4712: l_match_str := ' AND ';
4713: ELSIF p_match_type = 'OR' THEN
4714: l_match_str := ' OR ';
4715: END IF;
4716: l_entered_max_score:= init_search( HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC, HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, p_contact_list, p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
4717: g_score_until_thresh := false;
4718: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4719:
4720: -- ************************************************************
4720: -- ************************************************************
4721: -- STEP 2. Setup of intermedia query strings for Acquisition query
4722: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4723: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4724: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,p_contact_list,p_contact_point_list);
4725:
4726: -- Setup Search Context ID
4727: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4728: x_search_ctx_id := l_search_ctx_id;
4859:
4860: PROCEDURE get_matching_contact_points (
4861: p_rule_id IN NUMBER,
4862: p_party_id IN NUMBER,
4863: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4864: p_restrict_sql IN VARCHAR2,
4865: p_match_type IN VARCHAR2,
4866: p_dup_contact_point_id IN NUMBER,
4867: x_search_ctx_id OUT NUMBER,
4888: l_cpt_ps_id NUMBER;
4889: l_cpt_contact_id NUMBER;
4890: l_contact_pt_id NUMBER;
4891: l_cpt_type VARCHAR2(100);
4892: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
4893: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
4894: H_SCORE HZ_PARTY_SEARCH.IDList;
4895:
4896: cnt NUMBER :=0;
4889: l_cpt_contact_id NUMBER;
4890: l_contact_pt_id NUMBER;
4891: l_cpt_type VARCHAR2(100);
4892: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
4893: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
4894: H_SCORE HZ_PARTY_SEARCH.IDList;
4895:
4896: cnt NUMBER :=0;
4897: l_party_max_score NUMBER;
4890: l_contact_pt_id NUMBER;
4891: l_cpt_type VARCHAR2(100);
4892: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
4893: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
4894: H_SCORE HZ_PARTY_SEARCH.IDList;
4895:
4896: cnt NUMBER :=0;
4897: l_party_max_score NUMBER;
4898: l_ps_max_score NUMBER;
4919: l_match_str := ' AND ';
4920: ELSIF p_match_type = 'OR' THEN
4921: l_match_str := ' OR ';
4922: END IF;
4923: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
4924: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
4925: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
4926: g_score_until_thresh := false;
4927: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4920: ELSIF p_match_type = 'OR' THEN
4921: l_match_str := ' OR ';
4922: END IF;
4923: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
4924: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
4925: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
4926: g_score_until_thresh := false;
4927: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4928:
4928:
4929: -- ************************************************************
4930: -- STEP 2. Setup of intermedia query strings for Acquisition query
4931: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4932: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,p_contact_point_list);
4933:
4934: -- Setup Search Context ID
4935: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4936: x_search_ctx_id := l_search_ctx_id;
5003:
5004: PROCEDURE get_score_details (
5005: p_rule_id IN NUMBER,
5006: p_party_id IN NUMBER,
5007: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5008: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5009: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5010: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5011: x_search_ctx_id IN OUT NUMBER
5004: PROCEDURE get_score_details (
5005: p_rule_id IN NUMBER,
5006: p_party_id IN NUMBER,
5007: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5008: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5009: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5010: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5011: x_search_ctx_id IN OUT NUMBER
5012: ) IS
5005: p_rule_id IN NUMBER,
5006: p_party_id IN NUMBER,
5007: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5008: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5009: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5010: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5011: x_search_ctx_id IN OUT NUMBER
5012: ) IS
5013:
5006: p_party_id IN NUMBER,
5007: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5008: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5009: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5010: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5011: x_search_ctx_id IN OUT NUMBER
5012: ) IS
5013:
5014: -- Strings to hold the generated Intermedia query strings
5033: l_TX44 VARCHAR2(2000);
5034: l_max_TX44 VARCHAR2(2000);
5035: l_TX3 VARCHAR2(2000);
5036: l_max_TX3 VARCHAR2(2000);
5037: H_SCORES HZ_PARTY_SEARCH.score_list;
5038:
5039: l_score NUMBER;
5040: l_match_idx NUMBER;
5041: l_idx NUMBER;
5200: ************************************************/
5201:
5202: PROCEDURE get_party_for_search (
5203: p_party_id IN NUMBER,
5204: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5205: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5206: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5207: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5208: ) IS
5201:
5202: PROCEDURE get_party_for_search (
5203: p_party_id IN NUMBER,
5204: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5205: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5206: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5207: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5208: ) IS
5209: l_party_id NUMBER;
5202: PROCEDURE get_party_for_search (
5203: p_party_id IN NUMBER,
5204: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5205: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5206: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5207: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5208: ) IS
5209: l_party_id NUMBER;
5210: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5203: p_party_id IN NUMBER,
5204: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5205: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5206: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5207: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5208: ) IS
5209: l_party_id NUMBER;
5210: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5211: l_contact_ids HZ_PARTY_SEARCH.IDList;
5206: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5207: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5208: ) IS
5209: l_party_id NUMBER;
5210: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5211: l_contact_ids HZ_PARTY_SEARCH.IDList;
5212: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5213: ps NUMBER :=1;
5214: cpt NUMBER :=1;
5207: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5208: ) IS
5209: l_party_id NUMBER;
5210: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5211: l_contact_ids HZ_PARTY_SEARCH.IDList;
5212: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5213: ps NUMBER :=1;
5214: cpt NUMBER :=1;
5215: ct NUMBER :=1;
5208: ) IS
5209: l_party_id NUMBER;
5210: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5211: l_contact_ids HZ_PARTY_SEARCH.IDList;
5212: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5213: ps NUMBER :=1;
5214: cpt NUMBER :=1;
5215: ct NUMBER :=1;
5216: l_use_contact_info varchar2(1);
5272: END get_party_for_search;
5273:
5274: PROCEDURE get_search_criteria (
5275: p_party_id IN NUMBER,
5276: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5277: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5278: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5279: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5280: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5273:
5274: PROCEDURE get_search_criteria (
5275: p_party_id IN NUMBER,
5276: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5277: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5278: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5279: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5280: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5281: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5274: PROCEDURE get_search_criteria (
5275: p_party_id IN NUMBER,
5276: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5277: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5278: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5279: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5280: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5281: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5282: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5275: p_party_id IN NUMBER,
5276: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5277: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5278: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5279: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5280: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5281: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5282: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5283: ) IS
5276: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5277: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5278: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5279: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5280: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5281: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5282: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5283: ) IS
5284: BEGIN
5277: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5278: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5279: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5280: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5281: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5282: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5283: ) IS
5284: BEGIN
5285:
5278: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5279: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5280: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5281: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5282: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5283: ) IS
5284: BEGIN
5285:
5286: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5318: ************************************************/
5319:
5320: PROCEDURE get_party_rec (
5321: p_party_id IN NUMBER,
5322: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type
5323: ) IS
5324: l_party_type VARCHAR2(255);
5325: BEGIN
5326:
5381: the party site search record structure
5382: ************************************************/
5383:
5384: PROCEDURE get_party_site_rec (
5385: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5386: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5387: ) IS
5388: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5389: SELECT party_site_id
5382: ************************************************/
5383:
5384: PROCEDURE get_party_site_rec (
5385: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5386: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5387: ) IS
5388: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5389: SELECT party_site_id
5390: FROM HZ_PARTY_SITES, HZ_LOCATIONS
5435: the contact search record structure
5436: ************************************************/
5437:
5438: PROCEDURE get_contact_rec (
5439: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5440: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5441: ) IS
5442: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5443: SELECT org_contact_id
5436: ************************************************/
5437:
5438: PROCEDURE get_contact_rec (
5439: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5440: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5441: ) IS
5442: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5443: SELECT org_contact_id
5444: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
5491: the contact point search record structure
5492: ************************************************/
5493:
5494: PROCEDURE get_contact_point_rec (
5495: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5496: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5497: ) IS
5498:
5499: -- Cursor to fetch primary contact points for party
5492: ************************************************/
5493:
5494: PROCEDURE get_contact_point_rec (
5495: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5496: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5497: ) IS
5498:
5499: -- Cursor to fetch primary contact points for party
5500: CURSOR c_cpts(cp_contact_point_id NUMBER) IS
5546: ************************************************/
5547:
5548: PROCEDURE MAP_PARTY_REC(
5549: p_search_ctx IN BOOLEAN,
5550: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5551: x_entered_max_score OUT NUMBER,
5552: x_stage_rec IN OUT NOCOPY HZ_PARTY_STAGE.party_stage_rec_type
5553: ) IS
5554: tmp VARCHAR2(4000);
5667: ************************************************/
5668:
5669: PROCEDURE MAP_PARTY_SITE_REC(
5670: p_search_ctx IN BOOLEAN,
5671: p_search_list IN HZ_PARTY_SEARCH.party_site_list,
5672: x_entered_max_score OUT NUMBER,
5673: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.party_site_stage_list
5674: ) IS
5675: l_current_max_score NUMBER;
5736: ************************************************/
5737:
5738: PROCEDURE MAP_CONTACT_REC(
5739: p_search_ctx IN BOOLEAN,
5740: p_search_list IN HZ_PARTY_SEARCH.contact_list,
5741: x_entered_max_score OUT NUMBER,
5742: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_stage_list
5743: ) IS
5744: l_current_max_score NUMBER;
5778: ************************************************/
5779:
5780: PROCEDURE MAP_CONTACT_POINT_REC(
5781: p_search_ctx IN BOOLEAN,
5782: p_search_list IN HZ_PARTY_SEARCH.contact_point_list,
5783: x_entered_max_score OUT NUMBER,
5784: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_pt_stage_list
5785: ) IS
5786: l_current_max_score NUMBER;
5820: 2. Contact Point Type is not null for each condition
5821: ************************************************/
5822:
5823: FUNCTION check_prim_cond(
5824: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5825: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5826: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5827: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
5828: RETURN BOOLEAN IS
5821: ************************************************/
5822:
5823: FUNCTION check_prim_cond(
5824: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5825: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5826: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5827: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
5828: RETURN BOOLEAN IS
5829: BEGIN
5822:
5823: FUNCTION check_prim_cond(
5824: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5825: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5826: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5827: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
5828: RETURN BOOLEAN IS
5829: BEGIN
5830: IF p_party_search_rec.PARTY_ALL_NAMES IS NOT NULL THEN
5823: FUNCTION check_prim_cond(
5824: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5825: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5826: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5827: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
5828: RETURN BOOLEAN IS
5829: BEGIN
5830: IF p_party_search_rec.PARTY_ALL_NAMES IS NOT NULL THEN
5831: RETURN TRUE;
5858: has party site criteria.
5859: ************************************************/
5860:
5861: PROCEDURE check_party_site_cond(
5862: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5863: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5864: x_secondary OUT BOOLEAN,
5865: x_primary OUT BOOLEAN
5866: ) IS
5859: ************************************************/
5860:
5861: PROCEDURE check_party_site_cond(
5862: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5863: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5864: x_secondary OUT BOOLEAN,
5865: x_primary OUT BOOLEAN
5866: ) IS
5867: BEGIN
5902: has contact criteria.
5903: ************************************************/
5904:
5905: PROCEDURE check_contact_cond(
5906: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5907: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5908: x_secondary OUT BOOLEAN,
5909: x_primary OUT BOOLEAN
5910: ) IS
5903: ************************************************/
5904:
5905: PROCEDURE check_contact_cond(
5906: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5907: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5908: x_secondary OUT BOOLEAN,
5909: x_primary OUT BOOLEAN
5910: ) IS
5911: BEGIN
5940: has valid contact point criteria.
5941: ************************************************/
5942:
5943: PROCEDURE check_contact_point_cond(
5944: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5945: x_secondary OUT BOOLEAN,
5946: x_primary OUT BOOLEAN
5947: ) IS
5948: BEGIN