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;
387: PROCEDURE INSERT_PARTY_SCORE (
388: p_party_id IN NUMBER
389: ,p_record_id IN NUMBER
390: ,p_search_ctx_id IN NUMBER
391: ,p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
392: ,p_stage_rec IN HZ_PARTY_STAGE.party_stage_rec_type
393: ,p_table_TX2 VARCHAR2
394: ,p_table_TX4 VARCHAR2
395: ,p_table_TX8 VARCHAR2
495: PROCEDURE INSERT_PARTY_SITES_SCORE (
496: p_party_id IN NUMBER
497: ,p_record_id IN NUMBER
498: ,p_search_ctx_id IN NUMBER
499: ,p_search_rec IN HZ_PARTY_SEARCH.party_site_search_rec_type
500: ,p_stage_rec IN HZ_PARTY_STAGE.party_site_stage_rec_type
501: ,p_table_TX4 VARCHAR2
502: ,p_table_TX3 VARCHAR2
503: ,p_idx IN NUMBER) IS
544: PROCEDURE INSERT_CONTACTS_SCORE (
545: p_party_id IN NUMBER
546: ,p_record_id IN NUMBER
547: ,p_search_ctx_id IN NUMBER
548: ,p_search_rec IN HZ_PARTY_SEARCH.contact_search_rec_type
549: ,p_stage_rec IN HZ_PARTY_STAGE.contact_stage_rec_type
550: ,p_idx IN NUMBER) IS
551: l_current_score NUMBER:=0;
552: l_score NUMBER;
560: PROCEDURE INSERT_CONTACT_POINTS_SCORE (
561: p_party_id IN NUMBER
562: ,p_record_id IN NUMBER
563: ,p_search_ctx_id IN NUMBER
564: ,p_search_rec IN HZ_PARTY_SEARCH.contact_point_search_rec_type
565: ,p_stage_rec IN HZ_PARTY_STAGE.contact_pt_stage_rec_type
566: ,p_idx IN NUMBER) IS
567: l_current_score NUMBER:=0;
568: l_score NUMBER;
579: for the PARTY Entity
580: ************************************************/
581:
582: PROCEDURE POP_PARTY_COND_REC(
583: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
584: ) IS
585: BEGIN
586: NULL ;
587: EXCEPTION
599: for the PARTY_SITES Entity
600: ************************************************/
601:
602: PROCEDURE POP_PARTY_SITES_COND_REC(
603: p_search_list IN HZ_PARTY_SEARCH.party_site_list
604: ) IS
605: BEGIN
606: NULL ;
607: EXCEPTION
619: for the CONTACTS Entity
620: ************************************************/
621:
622: PROCEDURE POP_CONTACTS_COND_REC(
623: p_search_list IN HZ_PARTY_SEARCH.contact_list
624: ) IS
625: BEGIN
626: NULL ;
627: EXCEPTION
639: for the CONTACT_POINTS Entity
640: ************************************************/
641:
642: PROCEDURE POP_CONTACT_POINTS_COND_REC(
643: p_search_list IN HZ_PARTY_SEARCH.contact_point_list
644: ) IS
645: BEGIN
646: NULL ;
647: EXCEPTION
653: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
654: END;
655:
656: PROCEDURE init_score_context (
657: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
658: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
659: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
660: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
661: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
654: END;
655:
656: PROCEDURE init_score_context (
657: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
658: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
659: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
660: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
661: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
662: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
655:
656: PROCEDURE init_score_context (
657: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
658: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
659: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
660: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
661: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
662: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
663: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
656: PROCEDURE init_score_context (
657: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
658: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
659: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
660: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
661: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
662: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
663: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
664: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
657: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
658: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
659: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
660: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
661: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
662: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
663: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
664: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
665: ) IS
658: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
659: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
660: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
661: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
662: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
663: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
664: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
665: ) IS
666: l_dummy NUMBER;
659: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
660: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
661: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
662: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
663: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
664: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
665: ) IS
666: l_dummy NUMBER;
667: BEGIN
660: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
661: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
662: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
663: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
664: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
665: ) IS
666: l_dummy NUMBER;
667: BEGIN
668: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
680: MAP_CONTACT_POINT_REC(FALSE,p_contact_point_list, l_dummy, g_contact_pt_stage_list);
681:
682: END;
683: FUNCTION init_search(
684: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
685: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
686: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
687: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
688: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
681:
682: END;
683: FUNCTION init_search(
684: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
685: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
686: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
687: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
688: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
689: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
682: END;
683: FUNCTION init_search(
684: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
685: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
686: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
687: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
688: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
689: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
690: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
683: FUNCTION init_search(
684: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
685: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
686: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
687: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
688: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
689: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
690: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
691: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
684: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
685: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
686: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
687: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
688: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
689: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
690: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
691: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
692: p_match_type IN VARCHAR2,
685: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
686: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
687: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
688: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
689: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
690: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
691: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
692: p_match_type IN VARCHAR2,
693: x_party_max_score OUT NUMBER,
686: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
687: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
688: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
689: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
690: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
691: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
692: p_match_type IN VARCHAR2,
693: x_party_max_score OUT NUMBER,
694: x_ps_max_score OUT NUMBER,
687: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
688: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
689: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
690: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
691: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
692: p_match_type IN VARCHAR2,
693: x_party_max_score OUT NUMBER,
694: x_ps_max_score OUT NUMBER,
695: x_contact_max_score OUT NUMBER,
699: l_ps_entered_max_score NUMBER:=0;
700: l_ct_entered_max_score NUMBER:=0;
701: l_cpt_entered_max_score NUMBER:=0;
702: vlist vlisttype;
703: maxscore HZ_PARTY_SEARCH.IDList;
704: l_name VARCHAR2(200);
705: l_idx NUMBER;
706: l_num NUMBER;
707: total NUMBER;
1975: p_party_id NUMBER,
1976: p_party_site_id NUMBER,
1977: p_org_contact_id NUMBER,
1978: p_contact_point_id NUMBER)
1979: RETURN HZ_PARTY_SEARCH.score_rec IS
1980: l_score_rec HZ_PARTY_SEARCH.score_rec;
1981: BEGIN
1982: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1983: 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);
1976: p_party_site_id NUMBER,
1977: p_org_contact_id NUMBER,
1978: p_contact_point_id NUMBER)
1979: RETURN HZ_PARTY_SEARCH.score_rec IS
1980: l_score_rec HZ_PARTY_SEARCH.score_rec;
1981: BEGIN
1982: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1983: 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);
1984: 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);
2008: **********************************************************/
2009:
2010: PROCEDURE find_parties (
2011: p_rule_id IN NUMBER,
2012: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2013: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2014: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2015: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2016: p_restrict_sql IN VARCHAR2,
2009:
2010: PROCEDURE find_parties (
2011: p_rule_id IN NUMBER,
2012: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2013: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2014: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2015: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2016: p_restrict_sql IN VARCHAR2,
2017: p_match_type IN VARCHAR2,
2010: PROCEDURE find_parties (
2011: p_rule_id IN NUMBER,
2012: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2013: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2014: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2015: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2016: p_restrict_sql IN VARCHAR2,
2017: p_match_type IN VARCHAR2,
2018: p_search_merged IN VARCHAR2,
2011: p_rule_id IN NUMBER,
2012: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2013: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2014: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2015: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2016: p_restrict_sql IN VARCHAR2,
2017: p_match_type IN VARCHAR2,
2018: p_search_merged IN VARCHAR2,
2019: p_dup_party_id IN NUMBER,
2049: l_TX44 VARCHAR2(2000);
2050: l_TX3 VARCHAR2(2000);
2051: l_TX4 VARCHAR2(2000);
2052: l_TX8 VARCHAR2(2000);
2053: H_SCORES HZ_PARTY_SEARCH.score_list;
2054: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2055: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2056:
2057: l_score NUMBER;
2050: l_TX3 VARCHAR2(2000);
2051: l_TX4 VARCHAR2(2000);
2052: l_TX8 VARCHAR2(2000);
2053: H_SCORES HZ_PARTY_SEARCH.score_list;
2054: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2055: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2056:
2057: l_score NUMBER;
2058: l_idx NUMBER;
2051: l_TX4 VARCHAR2(2000);
2052: l_TX8 VARCHAR2(2000);
2053: H_SCORES HZ_PARTY_SEARCH.score_list;
2054: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2055: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2056:
2057: l_score NUMBER;
2058: l_idx NUMBER;
2059: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
2242: /** Private procedure to acquire and score at party site level ***/
2243: 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
2244: l_party_id_idx NUMBER:=1;
2245: l_ctx_id NUMBER;
2246: h_ps_id HZ_PARTY_SEARCH.IDList;
2247: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2248: h_ps_score HZ_PARTY_SEARCH.IDList;
2249: detcnt NUMBER := 1;
2250: BEGIN
2243: 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
2244: l_party_id_idx NUMBER:=1;
2245: l_ctx_id NUMBER;
2246: h_ps_id HZ_PARTY_SEARCH.IDList;
2247: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2248: h_ps_score HZ_PARTY_SEARCH.IDList;
2249: detcnt NUMBER := 1;
2250: BEGIN
2251: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2244: l_party_id_idx NUMBER:=1;
2245: l_ctx_id NUMBER;
2246: h_ps_id HZ_PARTY_SEARCH.IDList;
2247: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2248: h_ps_score HZ_PARTY_SEARCH.IDList;
2249: detcnt NUMBER := 1;
2250: BEGIN
2251: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2252: 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);
2364: /** Private procedure to acquire and score at contact point level ***/
2365: 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
2366: l_party_id_idx NUMBER:=1;
2367: l_ctx_id NUMBER;
2368: h_ct_id HZ_PARTY_SEARCH.IDList;
2369: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2370: h_ct_score HZ_PARTY_SEARCH.IDList;
2371: detcnt NUMBER := 1;
2372: BEGIN
2365: 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
2366: l_party_id_idx NUMBER:=1;
2367: l_ctx_id NUMBER;
2368: h_ct_id HZ_PARTY_SEARCH.IDList;
2369: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2370: h_ct_score HZ_PARTY_SEARCH.IDList;
2371: detcnt NUMBER := 1;
2372: BEGIN
2373: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2366: l_party_id_idx NUMBER:=1;
2367: l_ctx_id NUMBER;
2368: h_ct_id HZ_PARTY_SEARCH.IDList;
2369: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2370: h_ct_score HZ_PARTY_SEARCH.IDList;
2371: detcnt NUMBER := 1;
2372: BEGIN
2373: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2374: 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);
2473: /** Private procedure to acquire and score at contact point level ***/
2474: 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
2475: l_party_id_idx NUMBER:=1;
2476: l_ctx_id NUMBER;
2477: h_cpt_id HZ_PARTY_SEARCH.IDList;
2478: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2479: h_cpt_score HZ_PARTY_SEARCH.IDList;
2480: detcnt NUMBER := 1;
2481: l_cpt_flag VARCHAR2(1) := 'N';
2474: 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
2475: l_party_id_idx NUMBER:=1;
2476: l_ctx_id NUMBER;
2477: h_cpt_id HZ_PARTY_SEARCH.IDList;
2478: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2479: h_cpt_score HZ_PARTY_SEARCH.IDList;
2480: detcnt NUMBER := 1;
2481: l_cpt_flag VARCHAR2(1) := 'N';
2482: is_a_match VARCHAR2(1) := 'Y';
2475: l_party_id_idx NUMBER:=1;
2476: l_ctx_id NUMBER;
2477: h_cpt_id HZ_PARTY_SEARCH.IDList;
2478: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2479: h_cpt_score HZ_PARTY_SEARCH.IDList;
2480: detcnt NUMBER := 1;
2481: l_cpt_flag VARCHAR2(1) := 'N';
2482: is_a_match VARCHAR2(1) := 'Y';
2483: BEGIN
2924: END find_parties;
2925:
2926: PROCEDURE find_persons (
2927: p_rule_id IN NUMBER,
2928: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2929: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2930: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2931: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2932: p_restrict_sql IN VARCHAR2,
2925:
2926: PROCEDURE find_persons (
2927: p_rule_id IN NUMBER,
2928: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2929: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2930: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2931: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2932: p_restrict_sql IN VARCHAR2,
2933: p_match_type IN VARCHAR2,
2926: PROCEDURE find_persons (
2927: p_rule_id IN NUMBER,
2928: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2929: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2930: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2931: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2932: p_restrict_sql IN VARCHAR2,
2933: p_match_type IN VARCHAR2,
2934: p_search_merged IN VARCHAR2,
2927: p_rule_id IN NUMBER,
2928: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2929: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2930: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2931: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2932: p_restrict_sql IN VARCHAR2,
2933: p_match_type IN VARCHAR2,
2934: p_search_merged IN VARCHAR2,
2935: p_dup_party_id IN NUMBER,
2965: l_TX44 VARCHAR2(2000);
2966: l_TX3 VARCHAR2(2000);
2967: l_TX4 VARCHAR2(2000);
2968: l_TX8 VARCHAR2(2000);
2969: H_SCORES HZ_PARTY_SEARCH.score_list;
2970: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2971: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2972:
2973: l_score NUMBER;
2966: l_TX3 VARCHAR2(2000);
2967: l_TX4 VARCHAR2(2000);
2968: l_TX8 VARCHAR2(2000);
2969: H_SCORES HZ_PARTY_SEARCH.score_list;
2970: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2971: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2972:
2973: l_score NUMBER;
2974: l_idx NUMBER;
2967: l_TX4 VARCHAR2(2000);
2968: l_TX8 VARCHAR2(2000);
2969: H_SCORES HZ_PARTY_SEARCH.score_list;
2970: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2971: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2972:
2973: l_score NUMBER;
2974: l_idx NUMBER;
2975: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
3206: /** Private procedure to acquire and score at party site level ***/
3207: 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
3208: l_party_id_idx NUMBER:=1;
3209: l_ctx_id NUMBER;
3210: h_ps_id HZ_PARTY_SEARCH.IDList;
3211: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3212: h_ps_score HZ_PARTY_SEARCH.IDList;
3213: detcnt NUMBER := 1;
3214: l_person_id NUMBER;
3207: 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
3208: l_party_id_idx NUMBER:=1;
3209: l_ctx_id NUMBER;
3210: h_ps_id HZ_PARTY_SEARCH.IDList;
3211: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3212: h_ps_score HZ_PARTY_SEARCH.IDList;
3213: detcnt NUMBER := 1;
3214: l_person_id NUMBER;
3215: BEGIN
3208: l_party_id_idx NUMBER:=1;
3209: l_ctx_id NUMBER;
3210: h_ps_id HZ_PARTY_SEARCH.IDList;
3211: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3212: h_ps_score HZ_PARTY_SEARCH.IDList;
3213: detcnt NUMBER := 1;
3214: l_person_id NUMBER;
3215: BEGIN
3216: SAVEPOINT eval_start;
3305: /** Private procedure to acquire and score at party site level ***/
3306: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3307: l_party_id_idx NUMBER:=1;
3308: l_ctx_id NUMBER;
3309: h_ct_id HZ_PARTY_SEARCH.IDList;
3310: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3311: h_ct_score HZ_PARTY_SEARCH.IDList;
3312: detcnt NUMBER := 1;
3313: l_person_id NUMBER;
3306: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3307: l_party_id_idx NUMBER:=1;
3308: l_ctx_id NUMBER;
3309: h_ct_id HZ_PARTY_SEARCH.IDList;
3310: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3311: h_ct_score HZ_PARTY_SEARCH.IDList;
3312: detcnt NUMBER := 1;
3313: l_person_id NUMBER;
3314: BEGIN
3307: l_party_id_idx NUMBER:=1;
3308: l_ctx_id NUMBER;
3309: h_ct_id HZ_PARTY_SEARCH.IDList;
3310: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3311: h_ct_score HZ_PARTY_SEARCH.IDList;
3312: detcnt NUMBER := 1;
3313: l_person_id NUMBER;
3314: BEGIN
3315: SAVEPOINT eval_start;
3363: /** Private procedure to acquire and score at contact point level ***/
3364: 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
3365: l_party_id_idx NUMBER:=1;
3366: l_ctx_id NUMBER;
3367: h_cpt_id HZ_PARTY_SEARCH.IDList;
3368: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3369: h_cpt_score HZ_PARTY_SEARCH.IDList;
3370: detcnt NUMBER := 1;
3371: l_person_id NUMBER;
3364: 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
3365: l_party_id_idx NUMBER:=1;
3366: l_ctx_id NUMBER;
3367: h_cpt_id HZ_PARTY_SEARCH.IDList;
3368: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3369: h_cpt_score HZ_PARTY_SEARCH.IDList;
3370: detcnt NUMBER := 1;
3371: l_person_id NUMBER;
3372: is_a_match VARCHAR2(1) := 'Y';
3365: l_party_id_idx NUMBER:=1;
3366: l_ctx_id NUMBER;
3367: h_cpt_id HZ_PARTY_SEARCH.IDList;
3368: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3369: h_cpt_score HZ_PARTY_SEARCH.IDList;
3370: detcnt NUMBER := 1;
3371: l_person_id NUMBER;
3372: is_a_match VARCHAR2(1) := 'Y';
3373: l_cpt_flag VARCHAR2(1) := 'N';
3707: END find_persons;
3708:
3709: PROCEDURE find_persons (
3710: p_rule_id IN NUMBER,
3711: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3712: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3713: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3714: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3715: p_restrict_sql IN VARCHAR2,
3708:
3709: PROCEDURE find_persons (
3710: p_rule_id IN NUMBER,
3711: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3712: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3713: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3714: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3715: p_restrict_sql IN VARCHAR2,
3716: p_match_type IN VARCHAR2,
3709: PROCEDURE find_persons (
3710: p_rule_id IN NUMBER,
3711: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3712: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3713: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3714: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3715: p_restrict_sql IN VARCHAR2,
3716: p_match_type IN VARCHAR2,
3717: p_search_merged IN VARCHAR2,
3710: p_rule_id IN NUMBER,
3711: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3712: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3713: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3714: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3715: p_restrict_sql IN VARCHAR2,
3716: p_match_type IN VARCHAR2,
3717: p_search_merged IN VARCHAR2,
3718: p_ins_details IN VARCHAR2,
3724: 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);
3725: END;
3726: PROCEDURE find_party_details (
3727: p_rule_id IN NUMBER,
3728: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3729: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3730: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3731: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3732: p_restrict_sql IN VARCHAR2,
3725: END;
3726: PROCEDURE find_party_details (
3727: p_rule_id IN NUMBER,
3728: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3729: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3730: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3731: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3732: p_restrict_sql IN VARCHAR2,
3733: p_match_type IN VARCHAR2,
3726: PROCEDURE find_party_details (
3727: p_rule_id IN NUMBER,
3728: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3729: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3730: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3731: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3732: p_restrict_sql IN VARCHAR2,
3733: p_match_type IN VARCHAR2,
3734: p_search_merged IN VARCHAR2,
3727: p_rule_id IN NUMBER,
3728: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3729: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3730: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3731: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3732: p_restrict_sql IN VARCHAR2,
3733: p_match_type IN VARCHAR2,
3734: p_search_merged IN VARCHAR2,
3735: x_search_ctx_id OUT NUMBER,
3784: x_dup_set_id OUT NUMBER,
3785: x_search_ctx_id OUT NUMBER,
3786: x_num_matches OUT NUMBER
3787: ) IS
3788: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3789: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3790: l_contact_list HZ_PARTY_SEARCH.contact_list;
3791: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3792: l_match_idx NUMBER;
3785: x_search_ctx_id OUT NUMBER,
3786: x_num_matches OUT NUMBER
3787: ) IS
3788: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3789: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3790: l_contact_list HZ_PARTY_SEARCH.contact_list;
3791: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3792: l_match_idx NUMBER;
3793:
3786: x_num_matches OUT NUMBER
3787: ) IS
3788: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3789: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3790: l_contact_list HZ_PARTY_SEARCH.contact_list;
3791: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3792: l_match_idx NUMBER;
3793:
3794: --Fix for bug 4417124
3787: ) IS
3788: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3789: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3790: l_contact_list HZ_PARTY_SEARCH.contact_list;
3791: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3792: l_match_idx NUMBER;
3793:
3794: --Fix for bug 4417124
3795: l_use_contact_addr_info BOOLEAN := TRUE;
3900: x_search_ctx_id OUT NUMBER,
3901: x_num_matches OUT NUMBER
3902: ) IS
3903:
3904: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3905: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3906: l_contact_list HZ_PARTY_SEARCH.contact_list;
3907: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3908: contact_point_ids HZ_PARTY_SEARCH.IDList;
3901: x_num_matches OUT NUMBER
3902: ) IS
3903:
3904: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3905: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3906: l_contact_list HZ_PARTY_SEARCH.contact_list;
3907: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3908: contact_point_ids HZ_PARTY_SEARCH.IDList;
3909: p_party_site_list HZ_PARTY_SEARCH.IDList;
3902: ) IS
3903:
3904: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3905: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3906: l_contact_list HZ_PARTY_SEARCH.contact_list;
3907: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3908: contact_point_ids HZ_PARTY_SEARCH.IDList;
3909: p_party_site_list HZ_PARTY_SEARCH.IDList;
3910: p_contact_ids HZ_PARTY_SEARCH.IDList;
3903:
3904: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3905: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3906: l_contact_list HZ_PARTY_SEARCH.contact_list;
3907: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3908: contact_point_ids HZ_PARTY_SEARCH.IDList;
3909: p_party_site_list HZ_PARTY_SEARCH.IDList;
3910: p_contact_ids HZ_PARTY_SEARCH.IDList;
3911: l_match_idx NUMBER;
3904: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3905: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3906: l_contact_list HZ_PARTY_SEARCH.contact_list;
3907: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3908: contact_point_ids HZ_PARTY_SEARCH.IDList;
3909: p_party_site_list HZ_PARTY_SEARCH.IDList;
3910: p_contact_ids HZ_PARTY_SEARCH.IDList;
3911: l_match_idx NUMBER;
3912: cursor get_cpts_for_party_sites is select contact_point_id
3905: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3906: l_contact_list HZ_PARTY_SEARCH.contact_list;
3907: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3908: contact_point_ids HZ_PARTY_SEARCH.IDList;
3909: p_party_site_list HZ_PARTY_SEARCH.IDList;
3910: p_contact_ids HZ_PARTY_SEARCH.IDList;
3911: l_match_idx NUMBER;
3912: cursor get_cpts_for_party_sites is select contact_point_id
3913: from hz_contact_points
3906: l_contact_list HZ_PARTY_SEARCH.contact_list;
3907: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3908: contact_point_ids HZ_PARTY_SEARCH.IDList;
3909: p_party_site_list HZ_PARTY_SEARCH.IDList;
3910: p_contact_ids HZ_PARTY_SEARCH.IDList;
3911: l_match_idx NUMBER;
3912: cursor get_cpts_for_party_sites is select contact_point_id
3913: from hz_contact_points
3914: where owner_table_name = 'HZ_PARTY_SITES'
3935:
3936: get_search_criteria (
3937: null,
3938: p_party_site_list,
3939: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
3940: contact_point_ids,
3941: l_party_search_rec,
3942: l_party_site_list,
3943: l_contact_list,
3985: x_search_ctx_id OUT NUMBER,
3986: x_num_matches OUT NUMBER
3987: ) IS
3988:
3989: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3990: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3991: l_contact_list HZ_PARTY_SEARCH.contact_list;
3992: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3993: contact_point_ids HZ_PARTY_SEARCH.IDList;
3986: x_num_matches OUT NUMBER
3987: ) IS
3988:
3989: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3990: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3991: l_contact_list HZ_PARTY_SEARCH.contact_list;
3992: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3993: contact_point_ids HZ_PARTY_SEARCH.IDList;
3994: p_party_site_list HZ_PARTY_SEARCH.IDList;
3987: ) IS
3988:
3989: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3990: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3991: l_contact_list HZ_PARTY_SEARCH.contact_list;
3992: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3993: contact_point_ids HZ_PARTY_SEARCH.IDList;
3994: p_party_site_list HZ_PARTY_SEARCH.IDList;
3995: p_contact_ids HZ_PARTY_SEARCH.IDList;
3988:
3989: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3990: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3991: l_contact_list HZ_PARTY_SEARCH.contact_list;
3992: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3993: contact_point_ids HZ_PARTY_SEARCH.IDList;
3994: p_party_site_list HZ_PARTY_SEARCH.IDList;
3995: p_contact_ids HZ_PARTY_SEARCH.IDList;
3996: l_match_idx NUMBER;
3989: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
3990: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3991: l_contact_list HZ_PARTY_SEARCH.contact_list;
3992: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3993: contact_point_ids HZ_PARTY_SEARCH.IDList;
3994: p_party_site_list HZ_PARTY_SEARCH.IDList;
3995: p_contact_ids HZ_PARTY_SEARCH.IDList;
3996: l_match_idx NUMBER;
3997: cursor get_cpt_for_contact_id is select contact_point_id
3990: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3991: l_contact_list HZ_PARTY_SEARCH.contact_list;
3992: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3993: contact_point_ids HZ_PARTY_SEARCH.IDList;
3994: p_party_site_list HZ_PARTY_SEARCH.IDList;
3995: p_contact_ids HZ_PARTY_SEARCH.IDList;
3996: l_match_idx NUMBER;
3997: cursor get_cpt_for_contact_id is select contact_point_id
3998: from hz_org_contacts a, hz_relationships b, hz_contact_points c
3991: l_contact_list HZ_PARTY_SEARCH.contact_list;
3992: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
3993: contact_point_ids HZ_PARTY_SEARCH.IDList;
3994: p_party_site_list HZ_PARTY_SEARCH.IDList;
3995: p_contact_ids HZ_PARTY_SEARCH.IDList;
3996: l_match_idx NUMBER;
3997: cursor get_cpt_for_contact_id is select contact_point_id
3998: from hz_org_contacts a, hz_relationships b, hz_contact_points c
3999: where a.party_relationship_id = b.relationship_id
4022: CLOSE get_cpt_for_contact_id;
4023:
4024: get_search_criteria (
4025: null,
4026: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4027: p_contact_ids,
4028: contact_point_ids,
4029: l_party_search_rec,
4030: l_party_site_list,
4073: p_match_type IN VARCHAR2,
4074: x_search_ctx_id OUT NUMBER,
4075: x_num_matches OUT NUMBER
4076: ) IS
4077: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4078: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4079: l_contact_list HZ_PARTY_SEARCH.contact_list;
4080: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4081: contact_point_ids HZ_PARTY_SEARCH.IDList;
4074: x_search_ctx_id OUT NUMBER,
4075: x_num_matches OUT NUMBER
4076: ) IS
4077: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4078: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4079: l_contact_list HZ_PARTY_SEARCH.contact_list;
4080: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4081: contact_point_ids HZ_PARTY_SEARCH.IDList;
4082: p_party_site_list HZ_PARTY_SEARCH.IDList;
4075: x_num_matches OUT NUMBER
4076: ) IS
4077: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4078: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4079: l_contact_list HZ_PARTY_SEARCH.contact_list;
4080: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4081: contact_point_ids HZ_PARTY_SEARCH.IDList;
4082: p_party_site_list HZ_PARTY_SEARCH.IDList;
4083: p_contact_ids HZ_PARTY_SEARCH.IDList;
4076: ) IS
4077: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4078: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4079: l_contact_list HZ_PARTY_SEARCH.contact_list;
4080: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4081: contact_point_ids HZ_PARTY_SEARCH.IDList;
4082: p_party_site_list HZ_PARTY_SEARCH.IDList;
4083: p_contact_ids HZ_PARTY_SEARCH.IDList;
4084: l_match_idx NUMBER;
4077: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4078: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4079: l_contact_list HZ_PARTY_SEARCH.contact_list;
4080: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4081: contact_point_ids HZ_PARTY_SEARCH.IDList;
4082: p_party_site_list HZ_PARTY_SEARCH.IDList;
4083: p_contact_ids HZ_PARTY_SEARCH.IDList;
4084: l_match_idx NUMBER;
4085:
4078: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4079: l_contact_list HZ_PARTY_SEARCH.contact_list;
4080: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4081: contact_point_ids HZ_PARTY_SEARCH.IDList;
4082: p_party_site_list HZ_PARTY_SEARCH.IDList;
4083: p_contact_ids HZ_PARTY_SEARCH.IDList;
4084: l_match_idx NUMBER;
4085:
4086: BEGIN
4079: l_contact_list HZ_PARTY_SEARCH.contact_list;
4080: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4081: contact_point_ids HZ_PARTY_SEARCH.IDList;
4082: p_party_site_list HZ_PARTY_SEARCH.IDList;
4083: p_contact_ids HZ_PARTY_SEARCH.IDList;
4084: l_match_idx NUMBER;
4085:
4086: BEGIN
4087: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4096: END IF;
4097: contact_point_ids(1) := p_contact_point_id;
4098: get_search_criteria (
4099: null,
4100: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4101: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4102: contact_point_ids,
4103: l_party_search_rec,
4104: l_party_site_list,
4097: contact_point_ids(1) := p_contact_point_id;
4098: get_search_criteria (
4099: null,
4100: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4101: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4102: contact_point_ids,
4103: l_party_search_rec,
4104: l_party_site_list,
4105: l_contact_list,
4250: ) IS
4251: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4252: l_match_idx NUMBER;
4253: AttrVals AttrList;
4254: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4255: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4256: l_contact_list HZ_PARTY_SEARCH.contact_list;
4257: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4258: l_dup_set_id NUMBER;
4251: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4252: l_match_idx NUMBER;
4253: AttrVals AttrList;
4254: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4255: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4256: l_contact_list HZ_PARTY_SEARCH.contact_list;
4257: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4258: l_dup_set_id NUMBER;
4259: l_idx NUMBER;
4252: l_match_idx NUMBER;
4253: AttrVals AttrList;
4254: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4255: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4256: l_contact_list HZ_PARTY_SEARCH.contact_list;
4257: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4258: l_dup_set_id NUMBER;
4259: l_idx NUMBER;
4260: l_cpt_type VARCHAR2(255);
4253: AttrVals AttrList;
4254: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4255: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4256: l_contact_list HZ_PARTY_SEARCH.contact_list;
4257: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4258: l_dup_set_id NUMBER;
4259: l_idx NUMBER;
4260: l_cpt_type VARCHAR2(255);
4261: FIRST BOOLEAN := TRUE;
4415:
4416: PROCEDURE get_matching_party_sites (
4417: p_rule_id IN NUMBER,
4418: p_party_id IN NUMBER,
4419: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4420: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4421: p_restrict_sql IN VARCHAR2,
4422: p_match_type IN VARCHAR2,
4423: p_dup_party_site_id IN NUMBER,
4416: PROCEDURE get_matching_party_sites (
4417: p_rule_id IN NUMBER,
4418: p_party_id IN NUMBER,
4419: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4420: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4421: p_restrict_sql IN VARCHAR2,
4422: p_match_type IN VARCHAR2,
4423: p_dup_party_site_id IN NUMBER,
4424: x_search_ctx_id OUT NUMBER,
4443: l_search_ctx_id NUMBER; -- Generated Search Context ID
4444:
4445: l_TX3 VARCHAR2(2000);
4446: l_TX4 VARCHAR2(2000);
4447: H_SCORES HZ_PARTY_SEARCH.score_list;
4448:
4449: l_score NUMBER;
4450: l_idx NUMBER;
4451: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
4474: defps NUMBER :=0;
4475: defct NUMBER :=0;
4476: defcpt NUMBER :=0;
4477: l_index NUMBER;
4478: l_match_ps_list HZ_PARTY_SEARCH.IDList;
4479: l_cnt NUMBER:=1;
4480:
4481:
4482: BEGIN
4498: l_match_str := ' AND ';
4499: ELSIF p_match_type = 'OR' THEN
4500: l_match_str := ' OR ';
4501: END IF;
4502: 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);
4503: g_score_until_thresh := false;
4504: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4505:
4506: -- ************************************************************
4506: -- ************************************************************
4507: -- STEP 2. Setup of intermedia query strings for Acquisition query
4508: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4509: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4510: 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);
4511:
4512: -- Setup Search Context ID
4513: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4514: x_search_ctx_id := l_search_ctx_id;
4649:
4650: PROCEDURE get_matching_contacts (
4651: p_rule_id IN NUMBER,
4652: p_party_id IN NUMBER,
4653: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4654: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4655: p_restrict_sql IN VARCHAR2,
4656: p_match_type IN VARCHAR2,
4657: p_dup_contact_id IN NUMBER,
4650: PROCEDURE get_matching_contacts (
4651: p_rule_id IN NUMBER,
4652: p_party_id IN NUMBER,
4653: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4654: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4655: p_restrict_sql IN VARCHAR2,
4656: p_match_type IN VARCHAR2,
4657: p_dup_contact_id IN NUMBER,
4658: x_search_ctx_id OUT NUMBER,
4675: l_entered_max_score NUMBER;
4676: FIRST BOOLEAN;
4677: l_search_ctx_id NUMBER; -- Generated Search Context ID
4678:
4679: H_SCORES HZ_PARTY_SEARCH.score_list;
4680:
4681: l_score NUMBER;
4682: l_idx NUMBER;
4683: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
4703: l_party_max_score NUMBER;
4704: l_ps_max_score NUMBER;
4705: l_contact_max_score NUMBER;
4706: l_cpt_max_score NUMBER;
4707: l_match_contact_list HZ_PARTY_SEARCH.IDList;
4708: l_cnt NUMBER:=1;
4709:
4710:
4711: BEGIN
4727: l_match_str := ' AND ';
4728: ELSIF p_match_type = 'OR' THEN
4729: l_match_str := ' OR ';
4730: END IF;
4731: 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);
4732: g_score_until_thresh := false;
4733: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4734:
4735: -- ************************************************************
4735: -- ************************************************************
4736: -- STEP 2. Setup of intermedia query strings for Acquisition query
4737: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4738: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4739: 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);
4740:
4741: -- Setup Search Context ID
4742: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4743: x_search_ctx_id := l_search_ctx_id;
4874:
4875: PROCEDURE get_matching_contact_points (
4876: p_rule_id IN NUMBER,
4877: p_party_id IN NUMBER,
4878: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4879: p_restrict_sql IN VARCHAR2,
4880: p_match_type IN VARCHAR2,
4881: p_dup_contact_point_id IN NUMBER,
4882: x_search_ctx_id OUT NUMBER,
4903: l_cpt_ps_id NUMBER;
4904: l_cpt_contact_id NUMBER;
4905: l_contact_pt_id NUMBER;
4906: l_cpt_type VARCHAR2(100);
4907: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
4908: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
4909: H_SCORE HZ_PARTY_SEARCH.IDList;
4910:
4911: cnt NUMBER :=0;
4904: l_cpt_contact_id NUMBER;
4905: l_contact_pt_id NUMBER;
4906: l_cpt_type VARCHAR2(100);
4907: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
4908: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
4909: H_SCORE HZ_PARTY_SEARCH.IDList;
4910:
4911: cnt NUMBER :=0;
4912: l_party_max_score NUMBER;
4905: l_contact_pt_id NUMBER;
4906: l_cpt_type VARCHAR2(100);
4907: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
4908: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
4909: H_SCORE HZ_PARTY_SEARCH.IDList;
4910:
4911: cnt NUMBER :=0;
4912: l_party_max_score NUMBER;
4913: l_ps_max_score NUMBER;
4934: l_match_str := ' AND ';
4935: ELSIF p_match_type = 'OR' THEN
4936: l_match_str := ' OR ';
4937: END IF;
4938: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
4939: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
4940: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
4941: g_score_until_thresh := false;
4942: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4935: ELSIF p_match_type = 'OR' THEN
4936: l_match_str := ' OR ';
4937: END IF;
4938: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
4939: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
4940: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
4941: g_score_until_thresh := false;
4942: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4943:
4943:
4944: -- ************************************************************
4945: -- STEP 2. Setup of intermedia query strings for Acquisition query
4946: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4947: 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);
4948:
4949: -- Setup Search Context ID
4950: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4951: x_search_ctx_id := l_search_ctx_id;
5018:
5019: PROCEDURE get_score_details (
5020: p_rule_id IN NUMBER,
5021: p_party_id IN NUMBER,
5022: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5023: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5024: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5025: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5026: x_search_ctx_id IN OUT NUMBER
5019: PROCEDURE get_score_details (
5020: p_rule_id IN NUMBER,
5021: p_party_id IN NUMBER,
5022: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5023: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5024: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5025: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5026: x_search_ctx_id IN OUT NUMBER
5027: ) IS
5020: p_rule_id IN NUMBER,
5021: p_party_id IN NUMBER,
5022: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5023: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5024: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5025: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5026: x_search_ctx_id IN OUT NUMBER
5027: ) IS
5028:
5021: p_party_id IN NUMBER,
5022: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5023: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5024: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5025: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5026: x_search_ctx_id IN OUT NUMBER
5027: ) IS
5028:
5029: -- Strings to hold the generated Intermedia query strings
5052: l_TX4 VARCHAR2(2000);
5053: l_max_TX4 VARCHAR2(2000);
5054: l_TX8 VARCHAR2(2000);
5055: l_max_TX8 VARCHAR2(2000);
5056: H_SCORES HZ_PARTY_SEARCH.score_list;
5057:
5058: l_score NUMBER;
5059: l_match_idx NUMBER;
5060: l_idx NUMBER;
5219: ************************************************/
5220:
5221: PROCEDURE get_party_for_search (
5222: p_party_id IN NUMBER,
5223: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5224: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5225: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5226: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5227: ) IS
5220:
5221: PROCEDURE get_party_for_search (
5222: p_party_id IN NUMBER,
5223: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5224: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5225: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5226: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5227: ) IS
5228: l_party_id NUMBER;
5221: PROCEDURE get_party_for_search (
5222: p_party_id IN NUMBER,
5223: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5224: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5225: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5226: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5227: ) IS
5228: l_party_id NUMBER;
5229: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5222: p_party_id IN NUMBER,
5223: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5224: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5225: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5226: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5227: ) IS
5228: l_party_id NUMBER;
5229: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5230: l_contact_ids HZ_PARTY_SEARCH.IDList;
5225: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5226: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5227: ) IS
5228: l_party_id NUMBER;
5229: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5230: l_contact_ids HZ_PARTY_SEARCH.IDList;
5231: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5232: ps NUMBER :=1;
5233: cpt NUMBER :=1;
5226: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5227: ) IS
5228: l_party_id NUMBER;
5229: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5230: l_contact_ids HZ_PARTY_SEARCH.IDList;
5231: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5232: ps NUMBER :=1;
5233: cpt NUMBER :=1;
5234: ct NUMBER :=1;
5227: ) IS
5228: l_party_id NUMBER;
5229: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5230: l_contact_ids HZ_PARTY_SEARCH.IDList;
5231: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5232: ps NUMBER :=1;
5233: cpt NUMBER :=1;
5234: ct NUMBER :=1;
5235: l_use_contact_info varchar2(1);
5291: END get_party_for_search;
5292:
5293: PROCEDURE get_search_criteria (
5294: p_party_id IN NUMBER,
5295: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5296: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5297: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5298: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5299: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5292:
5293: PROCEDURE get_search_criteria (
5294: p_party_id IN NUMBER,
5295: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5296: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5297: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5298: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5299: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5300: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5293: PROCEDURE get_search_criteria (
5294: p_party_id IN NUMBER,
5295: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5296: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5297: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5298: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5299: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5300: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5301: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5294: p_party_id IN NUMBER,
5295: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5296: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5297: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5298: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5299: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5300: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5301: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5302: ) IS
5295: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5296: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5297: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5298: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5299: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5300: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5301: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5302: ) IS
5303: BEGIN
5296: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5297: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5298: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5299: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5300: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5301: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5302: ) IS
5303: BEGIN
5304:
5297: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5298: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5299: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5300: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5301: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5302: ) IS
5303: BEGIN
5304:
5305: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5337: ************************************************/
5338:
5339: PROCEDURE get_party_rec (
5340: p_party_id IN NUMBER,
5341: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type
5342: ) IS
5343: l_party_type VARCHAR2(255);
5344: BEGIN
5345:
5402: the party site search record structure
5403: ************************************************/
5404:
5405: PROCEDURE get_party_site_rec (
5406: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5407: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5408: ) IS
5409: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5410: SELECT party_site_id
5403: ************************************************/
5404:
5405: PROCEDURE get_party_site_rec (
5406: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5407: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5408: ) IS
5409: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5410: SELECT party_site_id
5411: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
5458: the contact search record structure
5459: ************************************************/
5460:
5461: PROCEDURE get_contact_rec (
5462: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5463: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5464: ) IS
5465: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5466: SELECT org_contact_id
5459: ************************************************/
5460:
5461: PROCEDURE get_contact_rec (
5462: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5463: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5464: ) IS
5465: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5466: SELECT org_contact_id
5467: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
5514: the contact point search record structure
5515: ************************************************/
5516:
5517: PROCEDURE get_contact_point_rec (
5518: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5519: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5520: ) IS
5521:
5522: -- Cursor to fetch primary contact points for party
5515: ************************************************/
5516:
5517: PROCEDURE get_contact_point_rec (
5518: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5519: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5520: ) IS
5521:
5522: -- Cursor to fetch primary contact points for party
5523: CURSOR c_cpts(cp_contact_point_id NUMBER) IS
5569: ************************************************/
5570:
5571: PROCEDURE MAP_PARTY_REC(
5572: p_search_ctx IN BOOLEAN,
5573: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5574: x_entered_max_score OUT NUMBER,
5575: x_stage_rec IN OUT NOCOPY HZ_PARTY_STAGE.party_stage_rec_type
5576: ) IS
5577: tmp VARCHAR2(4000);
5717: ************************************************/
5718:
5719: PROCEDURE MAP_PARTY_SITE_REC(
5720: p_search_ctx IN BOOLEAN,
5721: p_search_list IN HZ_PARTY_SEARCH.party_site_list,
5722: x_entered_max_score OUT NUMBER,
5723: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.party_site_stage_list
5724: ) IS
5725: l_current_max_score NUMBER;
5832: ************************************************/
5833:
5834: PROCEDURE MAP_CONTACT_REC(
5835: p_search_ctx IN BOOLEAN,
5836: p_search_list IN HZ_PARTY_SEARCH.contact_list,
5837: x_entered_max_score OUT NUMBER,
5838: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_stage_list
5839: ) IS
5840: l_current_max_score NUMBER;
5874: ************************************************/
5875:
5876: PROCEDURE MAP_CONTACT_POINT_REC(
5877: p_search_ctx IN BOOLEAN,
5878: p_search_list IN HZ_PARTY_SEARCH.contact_point_list,
5879: x_entered_max_score OUT NUMBER,
5880: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_pt_stage_list
5881: ) IS
5882: l_current_max_score NUMBER;
5916: 2. Contact Point Type is not null for each condition
5917: ************************************************/
5918:
5919: FUNCTION check_prim_cond(
5920: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5921: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5922: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5923: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
5924: RETURN BOOLEAN IS
5917: ************************************************/
5918:
5919: FUNCTION check_prim_cond(
5920: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5921: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5922: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5923: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
5924: RETURN BOOLEAN IS
5925: BEGIN
5918:
5919: FUNCTION check_prim_cond(
5920: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5921: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5922: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5923: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
5924: RETURN BOOLEAN IS
5925: BEGIN
5926: IF p_party_search_rec.PARTY_NAME IS NOT NULL THEN
5919: FUNCTION check_prim_cond(
5920: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5921: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5922: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5923: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
5924: RETURN BOOLEAN IS
5925: BEGIN
5926: IF p_party_search_rec.PARTY_NAME IS NOT NULL THEN
5927: RETURN TRUE;
5948: has party site criteria.
5949: ************************************************/
5950:
5951: PROCEDURE check_party_site_cond(
5952: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5953: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5954: x_secondary OUT BOOLEAN,
5955: x_primary OUT BOOLEAN
5956: ) IS
5949: ************************************************/
5950:
5951: PROCEDURE check_party_site_cond(
5952: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5953: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5954: x_secondary OUT BOOLEAN,
5955: x_primary OUT BOOLEAN
5956: ) IS
5957: BEGIN
5992: has contact criteria.
5993: ************************************************/
5994:
5995: PROCEDURE check_contact_cond(
5996: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5997: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5998: x_secondary OUT BOOLEAN,
5999: x_primary OUT BOOLEAN
6000: ) IS
5993: ************************************************/
5994:
5995: PROCEDURE check_contact_cond(
5996: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5997: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5998: x_secondary OUT BOOLEAN,
5999: x_primary OUT BOOLEAN
6000: ) IS
6001: BEGIN
6030: has valid contact point criteria.
6031: ************************************************/
6032:
6033: PROCEDURE check_contact_point_cond(
6034: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6035: x_secondary OUT BOOLEAN,
6036: x_primary OUT BOOLEAN
6037: ) IS
6038: BEGIN