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;
351: PROCEDURE INSERT_PARTY_SCORE (
352: p_party_id IN NUMBER
353: ,p_record_id IN NUMBER
354: ,p_search_ctx_id IN NUMBER
355: ,p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
356: ,p_stage_rec IN HZ_PARTY_STAGE.party_stage_rec_type
357: ,p_table_TX40 VARCHAR2
358: ,p_table_TX39 VARCHAR2
359: ,p_idx IN NUMBER) IS
400: PROCEDURE INSERT_PARTY_SITES_SCORE (
401: p_party_id IN NUMBER
402: ,p_record_id IN NUMBER
403: ,p_search_ctx_id IN NUMBER
404: ,p_search_rec IN HZ_PARTY_SEARCH.party_site_search_rec_type
405: ,p_stage_rec IN HZ_PARTY_STAGE.party_site_stage_rec_type
406: ,p_idx IN NUMBER) IS
407: l_current_score NUMBER:=0;
408: l_score NUMBER;
416: PROCEDURE INSERT_CONTACTS_SCORE (
417: p_party_id IN NUMBER
418: ,p_record_id IN NUMBER
419: ,p_search_ctx_id IN NUMBER
420: ,p_search_rec IN HZ_PARTY_SEARCH.contact_search_rec_type
421: ,p_stage_rec IN HZ_PARTY_STAGE.contact_stage_rec_type
422: ,p_table_TX22 VARCHAR2
423: ,p_idx IN NUMBER) IS
424: l_current_score NUMBER:=0;
458: PROCEDURE INSERT_CONTACT_POINTS_SCORE (
459: p_party_id IN NUMBER
460: ,p_record_id IN NUMBER
461: ,p_search_ctx_id IN NUMBER
462: ,p_search_rec IN HZ_PARTY_SEARCH.contact_point_search_rec_type
463: ,p_stage_rec IN HZ_PARTY_STAGE.contact_pt_stage_rec_type
464: ,p_table_TX1 VARCHAR2
465: ,p_table_TX6 VARCHAR2
466: ,p_table_TX5 VARCHAR2
536: for the PARTY Entity
537: ************************************************/
538:
539: PROCEDURE POP_PARTY_COND_REC(
540: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
541: ) IS
542: BEGIN
543: NULL ;
544: EXCEPTION
556: for the PARTY_SITES Entity
557: ************************************************/
558:
559: PROCEDURE POP_PARTY_SITES_COND_REC(
560: p_search_list IN HZ_PARTY_SEARCH.party_site_list
561: ) IS
562: BEGIN
563: NULL ;
564: EXCEPTION
576: for the CONTACTS Entity
577: ************************************************/
578:
579: PROCEDURE POP_CONTACTS_COND_REC(
580: p_search_list IN HZ_PARTY_SEARCH.contact_list
581: ) IS
582: BEGIN
583: NULL ;
584: EXCEPTION
596: for the CONTACT_POINTS Entity
597: ************************************************/
598:
599: PROCEDURE POP_CONTACT_POINTS_COND_REC(
600: p_search_list IN HZ_PARTY_SEARCH.contact_point_list
601: ) IS
602: BEGIN
603: NULL ;
604: EXCEPTION
610: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
611: END;
612:
613: PROCEDURE init_score_context (
614: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
615: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
616: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
617: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
618: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
611: END;
612:
613: PROCEDURE init_score_context (
614: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
615: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
616: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
617: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
618: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
619: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
612:
613: PROCEDURE init_score_context (
614: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
615: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
616: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
617: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
618: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
619: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
620: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
613: PROCEDURE init_score_context (
614: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
615: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
616: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
617: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
618: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
619: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
620: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
621: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
614: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
615: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
616: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
617: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
618: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
619: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
620: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
621: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
622: ) IS
615: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
616: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
617: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
618: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
619: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
620: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
621: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
622: ) IS
623: l_dummy NUMBER;
616: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
617: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
618: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
619: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
620: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
621: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
622: ) IS
623: l_dummy NUMBER;
624: BEGIN
617: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
618: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
619: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
620: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
621: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
622: ) IS
623: l_dummy NUMBER;
624: BEGIN
625: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
637: MAP_CONTACT_POINT_REC(FALSE,p_contact_point_list, l_dummy, g_contact_pt_stage_list);
638:
639: END;
640: FUNCTION init_search(
641: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
642: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
643: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
644: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
645: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
638:
639: END;
640: FUNCTION init_search(
641: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
642: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
643: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
644: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
645: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
646: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
639: END;
640: FUNCTION init_search(
641: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
642: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
643: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
644: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
645: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
646: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
647: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
640: FUNCTION init_search(
641: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
642: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
643: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
644: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
645: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
646: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
647: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
648: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
641: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
642: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
643: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
644: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
645: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
646: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
647: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
648: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
649: p_match_type IN VARCHAR2,
642: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
643: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
644: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
645: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
646: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
647: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
648: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
649: p_match_type IN VARCHAR2,
650: x_party_max_score OUT NUMBER,
643: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
644: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
645: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
646: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
647: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
648: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
649: p_match_type IN VARCHAR2,
650: x_party_max_score OUT NUMBER,
651: x_ps_max_score OUT NUMBER,
644: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
645: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
646: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
647: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
648: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
649: p_match_type IN VARCHAR2,
650: x_party_max_score OUT NUMBER,
651: x_ps_max_score OUT NUMBER,
652: x_contact_max_score OUT NUMBER,
656: l_ps_entered_max_score NUMBER:=0;
657: l_ct_entered_max_score NUMBER:=0;
658: l_cpt_entered_max_score NUMBER:=0;
659: vlist vlisttype;
660: maxscore HZ_PARTY_SEARCH.IDList;
661: l_name VARCHAR2(200);
662: l_idx NUMBER;
663: l_num NUMBER;
664: total NUMBER;
1994: p_party_id NUMBER,
1995: p_party_site_id NUMBER,
1996: p_org_contact_id NUMBER,
1997: p_contact_point_id NUMBER)
1998: RETURN HZ_PARTY_SEARCH.score_rec IS
1999: l_score_rec HZ_PARTY_SEARCH.score_rec;
2000: BEGIN
2001: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2002: 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);
1995: p_party_site_id NUMBER,
1996: p_org_contact_id NUMBER,
1997: p_contact_point_id NUMBER)
1998: RETURN HZ_PARTY_SEARCH.score_rec IS
1999: l_score_rec HZ_PARTY_SEARCH.score_rec;
2000: BEGIN
2001: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2002: 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);
2003: 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);
2027: **********************************************************/
2028:
2029: PROCEDURE find_parties (
2030: p_rule_id IN NUMBER,
2031: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2032: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2033: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2034: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2035: p_restrict_sql IN VARCHAR2,
2028:
2029: PROCEDURE find_parties (
2030: p_rule_id IN NUMBER,
2031: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2032: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2033: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2034: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2035: p_restrict_sql IN VARCHAR2,
2036: p_match_type IN VARCHAR2,
2029: PROCEDURE find_parties (
2030: p_rule_id IN NUMBER,
2031: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2032: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2033: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2034: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2035: p_restrict_sql IN VARCHAR2,
2036: p_match_type IN VARCHAR2,
2037: p_search_merged IN VARCHAR2,
2030: p_rule_id IN NUMBER,
2031: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2032: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2033: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2034: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2035: p_restrict_sql IN VARCHAR2,
2036: p_match_type IN VARCHAR2,
2037: p_search_merged IN VARCHAR2,
2038: p_dup_party_id IN NUMBER,
2068: l_TX1 VARCHAR2(2000);
2069: l_TX5 VARCHAR2(2000);
2070: l_TX40 VARCHAR2(2000);
2071: l_TX6 VARCHAR2(2000);
2072: H_SCORES HZ_PARTY_SEARCH.score_list;
2073: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2074: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2075:
2076: l_score NUMBER;
2069: l_TX5 VARCHAR2(2000);
2070: l_TX40 VARCHAR2(2000);
2071: l_TX6 VARCHAR2(2000);
2072: H_SCORES HZ_PARTY_SEARCH.score_list;
2073: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2074: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2075:
2076: l_score NUMBER;
2077: l_idx NUMBER;
2070: l_TX40 VARCHAR2(2000);
2071: l_TX6 VARCHAR2(2000);
2072: H_SCORES HZ_PARTY_SEARCH.score_list;
2073: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2074: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2075:
2076: l_score NUMBER;
2077: l_idx NUMBER;
2078: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
2262: /** Private procedure to acquire and score at party site level ***/
2263: 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
2264: l_party_id_idx NUMBER:=1;
2265: l_ctx_id NUMBER;
2266: h_ps_id HZ_PARTY_SEARCH.IDList;
2267: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2268: h_ps_score HZ_PARTY_SEARCH.IDList;
2269: detcnt NUMBER := 1;
2270: BEGIN
2263: 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
2264: l_party_id_idx NUMBER:=1;
2265: l_ctx_id NUMBER;
2266: h_ps_id HZ_PARTY_SEARCH.IDList;
2267: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2268: h_ps_score HZ_PARTY_SEARCH.IDList;
2269: detcnt NUMBER := 1;
2270: BEGIN
2271: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2264: l_party_id_idx NUMBER:=1;
2265: l_ctx_id NUMBER;
2266: h_ps_id HZ_PARTY_SEARCH.IDList;
2267: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2268: h_ps_score HZ_PARTY_SEARCH.IDList;
2269: detcnt NUMBER := 1;
2270: BEGIN
2271: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2272: 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);
2384: /** Private procedure to acquire and score at contact point level ***/
2385: 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
2386: l_party_id_idx NUMBER:=1;
2387: l_ctx_id NUMBER;
2388: h_ct_id HZ_PARTY_SEARCH.IDList;
2389: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2390: h_ct_score HZ_PARTY_SEARCH.IDList;
2391: detcnt NUMBER := 1;
2392: BEGIN
2385: 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
2386: l_party_id_idx NUMBER:=1;
2387: l_ctx_id NUMBER;
2388: h_ct_id HZ_PARTY_SEARCH.IDList;
2389: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2390: h_ct_score HZ_PARTY_SEARCH.IDList;
2391: detcnt NUMBER := 1;
2392: BEGIN
2393: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2386: l_party_id_idx NUMBER:=1;
2387: l_ctx_id NUMBER;
2388: h_ct_id HZ_PARTY_SEARCH.IDList;
2389: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2390: h_ct_score HZ_PARTY_SEARCH.IDList;
2391: detcnt NUMBER := 1;
2392: BEGIN
2393: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2394: 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);
2493: /** Private procedure to acquire and score at contact point level ***/
2494: 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
2495: l_party_id_idx NUMBER:=1;
2496: l_ctx_id NUMBER;
2497: h_cpt_id HZ_PARTY_SEARCH.IDList;
2498: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2499: h_cpt_score HZ_PARTY_SEARCH.IDList;
2500: detcnt NUMBER := 1;
2501: l_cpt_flag VARCHAR2(1) := 'N';
2494: 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
2495: l_party_id_idx NUMBER:=1;
2496: l_ctx_id NUMBER;
2497: h_cpt_id HZ_PARTY_SEARCH.IDList;
2498: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2499: h_cpt_score HZ_PARTY_SEARCH.IDList;
2500: detcnt NUMBER := 1;
2501: l_cpt_flag VARCHAR2(1) := 'N';
2502: is_a_match VARCHAR2(1) := 'Y';
2495: l_party_id_idx NUMBER:=1;
2496: l_ctx_id NUMBER;
2497: h_cpt_id HZ_PARTY_SEARCH.IDList;
2498: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2499: h_cpt_score HZ_PARTY_SEARCH.IDList;
2500: detcnt NUMBER := 1;
2501: l_cpt_flag VARCHAR2(1) := 'N';
2502: is_a_match VARCHAR2(1) := 'Y';
2503: BEGIN
2982: END find_parties;
2983:
2984: PROCEDURE find_persons (
2985: p_rule_id IN NUMBER,
2986: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2987: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2988: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2989: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2990: p_restrict_sql IN VARCHAR2,
2983:
2984: PROCEDURE find_persons (
2985: p_rule_id IN NUMBER,
2986: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2987: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2988: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2989: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2990: p_restrict_sql IN VARCHAR2,
2991: p_match_type IN VARCHAR2,
2984: PROCEDURE find_persons (
2985: p_rule_id IN NUMBER,
2986: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2987: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2988: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2989: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2990: p_restrict_sql IN VARCHAR2,
2991: p_match_type IN VARCHAR2,
2992: p_search_merged IN VARCHAR2,
2985: p_rule_id IN NUMBER,
2986: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2987: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2988: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2989: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2990: p_restrict_sql IN VARCHAR2,
2991: p_match_type IN VARCHAR2,
2992: p_search_merged IN VARCHAR2,
2993: p_dup_party_id IN NUMBER,
3023: l_TX1 VARCHAR2(2000);
3024: l_TX5 VARCHAR2(2000);
3025: l_TX40 VARCHAR2(2000);
3026: l_TX6 VARCHAR2(2000);
3027: H_SCORES HZ_PARTY_SEARCH.score_list;
3028: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3029: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3030:
3031: l_score NUMBER;
3024: l_TX5 VARCHAR2(2000);
3025: l_TX40 VARCHAR2(2000);
3026: l_TX6 VARCHAR2(2000);
3027: H_SCORES HZ_PARTY_SEARCH.score_list;
3028: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3029: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3030:
3031: l_score NUMBER;
3032: l_idx NUMBER;
3025: l_TX40 VARCHAR2(2000);
3026: l_TX6 VARCHAR2(2000);
3027: H_SCORES HZ_PARTY_SEARCH.score_list;
3028: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3029: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3030:
3031: l_score NUMBER;
3032: l_idx NUMBER;
3033: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
3265: /** Private procedure to acquire and score at party site level ***/
3266: 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
3267: l_party_id_idx NUMBER:=1;
3268: l_ctx_id NUMBER;
3269: h_ps_id HZ_PARTY_SEARCH.IDList;
3270: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3271: h_ps_score HZ_PARTY_SEARCH.IDList;
3272: detcnt NUMBER := 1;
3273: l_person_id NUMBER;
3266: 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
3267: l_party_id_idx NUMBER:=1;
3268: l_ctx_id NUMBER;
3269: h_ps_id HZ_PARTY_SEARCH.IDList;
3270: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3271: h_ps_score HZ_PARTY_SEARCH.IDList;
3272: detcnt NUMBER := 1;
3273: l_person_id NUMBER;
3274: BEGIN
3267: l_party_id_idx NUMBER:=1;
3268: l_ctx_id NUMBER;
3269: h_ps_id HZ_PARTY_SEARCH.IDList;
3270: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3271: h_ps_score HZ_PARTY_SEARCH.IDList;
3272: detcnt NUMBER := 1;
3273: l_person_id NUMBER;
3274: BEGIN
3275: SAVEPOINT eval_start;
3364: /** Private procedure to acquire and score at party site level ***/
3365: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3366: l_party_id_idx NUMBER:=1;
3367: l_ctx_id NUMBER;
3368: h_ct_id HZ_PARTY_SEARCH.IDList;
3369: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3370: h_ct_score HZ_PARTY_SEARCH.IDList;
3371: detcnt NUMBER := 1;
3372: l_person_id NUMBER;
3365: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3366: l_party_id_idx NUMBER:=1;
3367: l_ctx_id NUMBER;
3368: h_ct_id HZ_PARTY_SEARCH.IDList;
3369: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3370: h_ct_score HZ_PARTY_SEARCH.IDList;
3371: detcnt NUMBER := 1;
3372: l_person_id NUMBER;
3373: BEGIN
3366: l_party_id_idx NUMBER:=1;
3367: l_ctx_id NUMBER;
3368: h_ct_id HZ_PARTY_SEARCH.IDList;
3369: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3370: h_ct_score HZ_PARTY_SEARCH.IDList;
3371: detcnt NUMBER := 1;
3372: l_person_id NUMBER;
3373: BEGIN
3374: SAVEPOINT eval_start;
3422: /** Private procedure to acquire and score at contact point level ***/
3423: 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
3424: l_party_id_idx NUMBER:=1;
3425: l_ctx_id NUMBER;
3426: h_cpt_id HZ_PARTY_SEARCH.IDList;
3427: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3428: h_cpt_score HZ_PARTY_SEARCH.IDList;
3429: detcnt NUMBER := 1;
3430: l_person_id NUMBER;
3423: 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
3424: l_party_id_idx NUMBER:=1;
3425: l_ctx_id NUMBER;
3426: h_cpt_id HZ_PARTY_SEARCH.IDList;
3427: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3428: h_cpt_score HZ_PARTY_SEARCH.IDList;
3429: detcnt NUMBER := 1;
3430: l_person_id NUMBER;
3431: is_a_match VARCHAR2(1) := 'Y';
3424: l_party_id_idx NUMBER:=1;
3425: l_ctx_id NUMBER;
3426: h_cpt_id HZ_PARTY_SEARCH.IDList;
3427: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3428: h_cpt_score HZ_PARTY_SEARCH.IDList;
3429: detcnt NUMBER := 1;
3430: l_person_id NUMBER;
3431: is_a_match VARCHAR2(1) := 'Y';
3432: l_cpt_flag VARCHAR2(1) := 'N';
3835: END find_persons;
3836:
3837: PROCEDURE find_persons (
3838: p_rule_id IN NUMBER,
3839: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3840: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3841: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3842: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3843: p_restrict_sql IN VARCHAR2,
3836:
3837: PROCEDURE find_persons (
3838: p_rule_id IN NUMBER,
3839: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3840: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3841: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3842: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3843: p_restrict_sql IN VARCHAR2,
3844: p_match_type IN VARCHAR2,
3837: PROCEDURE find_persons (
3838: p_rule_id IN NUMBER,
3839: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3840: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3841: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3842: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3843: p_restrict_sql IN VARCHAR2,
3844: p_match_type IN VARCHAR2,
3845: p_search_merged IN VARCHAR2,
3838: p_rule_id IN NUMBER,
3839: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3840: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3841: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3842: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3843: p_restrict_sql IN VARCHAR2,
3844: p_match_type IN VARCHAR2,
3845: p_search_merged IN VARCHAR2,
3846: p_ins_details IN VARCHAR2,
3852: 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);
3853: END;
3854: PROCEDURE find_party_details (
3855: p_rule_id IN NUMBER,
3856: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3857: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3858: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3859: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3860: p_restrict_sql IN VARCHAR2,
3853: END;
3854: PROCEDURE find_party_details (
3855: p_rule_id IN NUMBER,
3856: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3857: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3858: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3859: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3860: p_restrict_sql IN VARCHAR2,
3861: p_match_type IN VARCHAR2,
3854: PROCEDURE find_party_details (
3855: p_rule_id IN NUMBER,
3856: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3857: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3858: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3859: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3860: p_restrict_sql IN VARCHAR2,
3861: p_match_type IN VARCHAR2,
3862: p_search_merged IN VARCHAR2,
3855: p_rule_id IN NUMBER,
3856: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3857: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3858: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3859: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3860: p_restrict_sql IN VARCHAR2,
3861: p_match_type IN VARCHAR2,
3862: p_search_merged IN VARCHAR2,
3863: x_search_ctx_id OUT NUMBER,
3912: x_dup_set_id OUT NUMBER,
3913: x_search_ctx_id OUT NUMBER,
3914: x_num_matches OUT NUMBER
3915: ) IS
3916: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3917: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3918: l_contact_list HZ_PARTY_SEARCH.contact_list;
3919: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3920: l_match_idx NUMBER;
3913: x_search_ctx_id OUT NUMBER,
3914: x_num_matches OUT NUMBER
3915: ) IS
3916: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3917: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3918: l_contact_list HZ_PARTY_SEARCH.contact_list;
3919: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3920: l_match_idx NUMBER;
3921:
3914: x_num_matches OUT NUMBER
3915: ) IS
3916: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3917: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3918: l_contact_list HZ_PARTY_SEARCH.contact_list;
3919: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3920: l_match_idx NUMBER;
3921:
3922: --Fix for bug 4417124
3915: ) IS
3916: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3917: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3918: l_contact_list HZ_PARTY_SEARCH.contact_list;
3919: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3920: l_match_idx NUMBER;
3921:
3922: --Fix for bug 4417124
3923: l_use_contact_addr_info BOOLEAN := TRUE;
4028: x_search_ctx_id OUT NUMBER,
4029: x_num_matches OUT NUMBER
4030: ) IS
4031:
4032: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4033: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4034: l_contact_list HZ_PARTY_SEARCH.contact_list;
4035: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4036: contact_point_ids HZ_PARTY_SEARCH.IDList;
4029: x_num_matches OUT NUMBER
4030: ) IS
4031:
4032: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4033: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4034: l_contact_list HZ_PARTY_SEARCH.contact_list;
4035: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4036: contact_point_ids HZ_PARTY_SEARCH.IDList;
4037: p_party_site_list HZ_PARTY_SEARCH.IDList;
4030: ) IS
4031:
4032: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4033: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4034: l_contact_list HZ_PARTY_SEARCH.contact_list;
4035: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4036: contact_point_ids HZ_PARTY_SEARCH.IDList;
4037: p_party_site_list HZ_PARTY_SEARCH.IDList;
4038: p_contact_ids HZ_PARTY_SEARCH.IDList;
4031:
4032: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4033: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4034: l_contact_list HZ_PARTY_SEARCH.contact_list;
4035: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4036: contact_point_ids HZ_PARTY_SEARCH.IDList;
4037: p_party_site_list HZ_PARTY_SEARCH.IDList;
4038: p_contact_ids HZ_PARTY_SEARCH.IDList;
4039: l_match_idx NUMBER;
4032: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4033: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4034: l_contact_list HZ_PARTY_SEARCH.contact_list;
4035: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4036: contact_point_ids HZ_PARTY_SEARCH.IDList;
4037: p_party_site_list HZ_PARTY_SEARCH.IDList;
4038: p_contact_ids HZ_PARTY_SEARCH.IDList;
4039: l_match_idx NUMBER;
4040: cursor get_cpts_for_party_sites is select contact_point_id
4033: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4034: l_contact_list HZ_PARTY_SEARCH.contact_list;
4035: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4036: contact_point_ids HZ_PARTY_SEARCH.IDList;
4037: p_party_site_list HZ_PARTY_SEARCH.IDList;
4038: p_contact_ids HZ_PARTY_SEARCH.IDList;
4039: l_match_idx NUMBER;
4040: cursor get_cpts_for_party_sites is select contact_point_id
4041: from hz_contact_points
4034: l_contact_list HZ_PARTY_SEARCH.contact_list;
4035: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4036: contact_point_ids HZ_PARTY_SEARCH.IDList;
4037: p_party_site_list HZ_PARTY_SEARCH.IDList;
4038: p_contact_ids HZ_PARTY_SEARCH.IDList;
4039: l_match_idx NUMBER;
4040: cursor get_cpts_for_party_sites is select contact_point_id
4041: from hz_contact_points
4042: where owner_table_name = 'HZ_PARTY_SITES'
4063:
4064: get_search_criteria (
4065: null,
4066: p_party_site_list,
4067: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4068: contact_point_ids,
4069: l_party_search_rec,
4070: l_party_site_list,
4071: l_contact_list,
4113: x_search_ctx_id OUT NUMBER,
4114: x_num_matches OUT NUMBER
4115: ) IS
4116:
4117: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4118: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4119: l_contact_list HZ_PARTY_SEARCH.contact_list;
4120: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4121: contact_point_ids HZ_PARTY_SEARCH.IDList;
4114: x_num_matches OUT NUMBER
4115: ) IS
4116:
4117: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4118: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4119: l_contact_list HZ_PARTY_SEARCH.contact_list;
4120: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4121: contact_point_ids HZ_PARTY_SEARCH.IDList;
4122: p_party_site_list HZ_PARTY_SEARCH.IDList;
4115: ) IS
4116:
4117: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4118: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4119: l_contact_list HZ_PARTY_SEARCH.contact_list;
4120: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4121: contact_point_ids HZ_PARTY_SEARCH.IDList;
4122: p_party_site_list HZ_PARTY_SEARCH.IDList;
4123: p_contact_ids HZ_PARTY_SEARCH.IDList;
4116:
4117: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4118: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4119: l_contact_list HZ_PARTY_SEARCH.contact_list;
4120: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4121: contact_point_ids HZ_PARTY_SEARCH.IDList;
4122: p_party_site_list HZ_PARTY_SEARCH.IDList;
4123: p_contact_ids HZ_PARTY_SEARCH.IDList;
4124: l_match_idx NUMBER;
4117: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4118: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4119: l_contact_list HZ_PARTY_SEARCH.contact_list;
4120: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4121: contact_point_ids HZ_PARTY_SEARCH.IDList;
4122: p_party_site_list HZ_PARTY_SEARCH.IDList;
4123: p_contact_ids HZ_PARTY_SEARCH.IDList;
4124: l_match_idx NUMBER;
4125: cursor get_cpt_for_contact_id is select contact_point_id
4118: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4119: l_contact_list HZ_PARTY_SEARCH.contact_list;
4120: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4121: contact_point_ids HZ_PARTY_SEARCH.IDList;
4122: p_party_site_list HZ_PARTY_SEARCH.IDList;
4123: p_contact_ids HZ_PARTY_SEARCH.IDList;
4124: l_match_idx NUMBER;
4125: cursor get_cpt_for_contact_id is select contact_point_id
4126: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4119: l_contact_list HZ_PARTY_SEARCH.contact_list;
4120: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4121: contact_point_ids HZ_PARTY_SEARCH.IDList;
4122: p_party_site_list HZ_PARTY_SEARCH.IDList;
4123: p_contact_ids HZ_PARTY_SEARCH.IDList;
4124: l_match_idx NUMBER;
4125: cursor get_cpt_for_contact_id is select contact_point_id
4126: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4127: where a.party_relationship_id = b.relationship_id
4150: CLOSE get_cpt_for_contact_id;
4151:
4152: get_search_criteria (
4153: null,
4154: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4155: p_contact_ids,
4156: contact_point_ids,
4157: l_party_search_rec,
4158: l_party_site_list,
4201: p_match_type IN VARCHAR2,
4202: x_search_ctx_id OUT NUMBER,
4203: x_num_matches OUT NUMBER
4204: ) IS
4205: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4206: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4207: l_contact_list HZ_PARTY_SEARCH.contact_list;
4208: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4209: contact_point_ids HZ_PARTY_SEARCH.IDList;
4202: x_search_ctx_id OUT NUMBER,
4203: x_num_matches OUT NUMBER
4204: ) IS
4205: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4206: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4207: l_contact_list HZ_PARTY_SEARCH.contact_list;
4208: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4209: contact_point_ids HZ_PARTY_SEARCH.IDList;
4210: p_party_site_list HZ_PARTY_SEARCH.IDList;
4203: x_num_matches OUT NUMBER
4204: ) IS
4205: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4206: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4207: l_contact_list HZ_PARTY_SEARCH.contact_list;
4208: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4209: contact_point_ids HZ_PARTY_SEARCH.IDList;
4210: p_party_site_list HZ_PARTY_SEARCH.IDList;
4211: p_contact_ids HZ_PARTY_SEARCH.IDList;
4204: ) IS
4205: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4206: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4207: l_contact_list HZ_PARTY_SEARCH.contact_list;
4208: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4209: contact_point_ids HZ_PARTY_SEARCH.IDList;
4210: p_party_site_list HZ_PARTY_SEARCH.IDList;
4211: p_contact_ids HZ_PARTY_SEARCH.IDList;
4212: l_match_idx NUMBER;
4205: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4206: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4207: l_contact_list HZ_PARTY_SEARCH.contact_list;
4208: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4209: contact_point_ids HZ_PARTY_SEARCH.IDList;
4210: p_party_site_list HZ_PARTY_SEARCH.IDList;
4211: p_contact_ids HZ_PARTY_SEARCH.IDList;
4212: l_match_idx NUMBER;
4213:
4206: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4207: l_contact_list HZ_PARTY_SEARCH.contact_list;
4208: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4209: contact_point_ids HZ_PARTY_SEARCH.IDList;
4210: p_party_site_list HZ_PARTY_SEARCH.IDList;
4211: p_contact_ids HZ_PARTY_SEARCH.IDList;
4212: l_match_idx NUMBER;
4213:
4214: BEGIN
4207: l_contact_list HZ_PARTY_SEARCH.contact_list;
4208: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4209: contact_point_ids HZ_PARTY_SEARCH.IDList;
4210: p_party_site_list HZ_PARTY_SEARCH.IDList;
4211: p_contact_ids HZ_PARTY_SEARCH.IDList;
4212: l_match_idx NUMBER;
4213:
4214: BEGIN
4215: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4224: END IF;
4225: contact_point_ids(1) := p_contact_point_id;
4226: get_search_criteria (
4227: null,
4228: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4229: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4230: contact_point_ids,
4231: l_party_search_rec,
4232: l_party_site_list,
4225: contact_point_ids(1) := p_contact_point_id;
4226: get_search_criteria (
4227: null,
4228: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4229: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4230: contact_point_ids,
4231: l_party_search_rec,
4232: l_party_site_list,
4233: l_contact_list,
4378: ) IS
4379: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4380: l_match_idx NUMBER;
4381: AttrVals AttrList;
4382: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4383: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4384: l_contact_list HZ_PARTY_SEARCH.contact_list;
4385: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4386: l_dup_set_id NUMBER;
4379: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4380: l_match_idx NUMBER;
4381: AttrVals AttrList;
4382: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4383: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4384: l_contact_list HZ_PARTY_SEARCH.contact_list;
4385: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4386: l_dup_set_id NUMBER;
4387: l_idx NUMBER;
4380: l_match_idx NUMBER;
4381: AttrVals AttrList;
4382: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4383: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4384: l_contact_list HZ_PARTY_SEARCH.contact_list;
4385: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4386: l_dup_set_id NUMBER;
4387: l_idx NUMBER;
4388: l_cpt_type VARCHAR2(255);
4381: AttrVals AttrList;
4382: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4383: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4384: l_contact_list HZ_PARTY_SEARCH.contact_list;
4385: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4386: l_dup_set_id NUMBER;
4387: l_idx NUMBER;
4388: l_cpt_type VARCHAR2(255);
4389: FIRST BOOLEAN := TRUE;
4563:
4564: PROCEDURE get_matching_party_sites (
4565: p_rule_id IN NUMBER,
4566: p_party_id IN NUMBER,
4567: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4568: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4569: p_restrict_sql IN VARCHAR2,
4570: p_match_type IN VARCHAR2,
4571: p_dup_party_site_id IN NUMBER,
4564: PROCEDURE get_matching_party_sites (
4565: p_rule_id IN NUMBER,
4566: p_party_id IN NUMBER,
4567: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4568: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4569: p_restrict_sql IN VARCHAR2,
4570: p_match_type IN VARCHAR2,
4571: p_dup_party_site_id IN NUMBER,
4572: x_search_ctx_id OUT NUMBER,
4592:
4593: l_TX1 VARCHAR2(2000);
4594: l_TX5 VARCHAR2(2000);
4595: l_TX6 VARCHAR2(2000);
4596: H_SCORES HZ_PARTY_SEARCH.score_list;
4597:
4598: l_score NUMBER;
4599: l_idx NUMBER;
4600: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
4623: defps NUMBER :=0;
4624: defct NUMBER :=0;
4625: defcpt NUMBER :=0;
4626: l_index NUMBER;
4627: l_match_ps_list HZ_PARTY_SEARCH.IDList;
4628: l_cnt NUMBER:=1;
4629:
4630:
4631: BEGIN
4647: l_match_str := ' AND ';
4648: ELSIF p_match_type = 'OR' THEN
4649: l_match_str := ' OR ';
4650: END IF;
4651: 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);
4652: g_score_until_thresh := false;
4653: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4654:
4655: -- ************************************************************
4655: -- ************************************************************
4656: -- STEP 2. Setup of intermedia query strings for Acquisition query
4657: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4658: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4659: 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);
4660:
4661: -- Setup Search Context ID
4662: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4663: x_search_ctx_id := l_search_ctx_id;
4798:
4799: PROCEDURE get_matching_contacts (
4800: p_rule_id IN NUMBER,
4801: p_party_id IN NUMBER,
4802: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4803: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4804: p_restrict_sql IN VARCHAR2,
4805: p_match_type IN VARCHAR2,
4806: p_dup_contact_id IN NUMBER,
4799: PROCEDURE get_matching_contacts (
4800: p_rule_id IN NUMBER,
4801: p_party_id IN NUMBER,
4802: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4803: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4804: p_restrict_sql IN VARCHAR2,
4805: p_match_type IN VARCHAR2,
4806: p_dup_contact_id IN NUMBER,
4807: x_search_ctx_id OUT NUMBER,
4828: l_TX22 VARCHAR2(2000);
4829: l_TX1 VARCHAR2(2000);
4830: l_TX5 VARCHAR2(2000);
4831: l_TX6 VARCHAR2(2000);
4832: H_SCORES HZ_PARTY_SEARCH.score_list;
4833:
4834: l_score NUMBER;
4835: l_idx NUMBER;
4836: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
4856: l_party_max_score NUMBER;
4857: l_ps_max_score NUMBER;
4858: l_contact_max_score NUMBER;
4859: l_cpt_max_score NUMBER;
4860: l_match_contact_list HZ_PARTY_SEARCH.IDList;
4861: l_cnt NUMBER:=1;
4862:
4863:
4864: BEGIN
4880: l_match_str := ' AND ';
4881: ELSIF p_match_type = 'OR' THEN
4882: l_match_str := ' OR ';
4883: END IF;
4884: 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);
4885: g_score_until_thresh := false;
4886: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4887:
4888: -- ************************************************************
4888: -- ************************************************************
4889: -- STEP 2. Setup of intermedia query strings for Acquisition query
4890: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4891: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4892: 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);
4893:
4894: -- Setup Search Context ID
4895: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4896: x_search_ctx_id := l_search_ctx_id;
5027:
5028: PROCEDURE get_matching_contact_points (
5029: p_rule_id IN NUMBER,
5030: p_party_id IN NUMBER,
5031: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5032: p_restrict_sql IN VARCHAR2,
5033: p_match_type IN VARCHAR2,
5034: p_dup_contact_point_id IN NUMBER,
5035: x_search_ctx_id OUT NUMBER,
5059: l_cpt_ps_id NUMBER;
5060: l_cpt_contact_id NUMBER;
5061: l_contact_pt_id NUMBER;
5062: l_cpt_type VARCHAR2(100);
5063: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5064: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5065: H_SCORE HZ_PARTY_SEARCH.IDList;
5066:
5067: cnt NUMBER :=0;
5060: l_cpt_contact_id NUMBER;
5061: l_contact_pt_id NUMBER;
5062: l_cpt_type VARCHAR2(100);
5063: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5064: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5065: H_SCORE HZ_PARTY_SEARCH.IDList;
5066:
5067: cnt NUMBER :=0;
5068: l_party_max_score NUMBER;
5061: l_contact_pt_id NUMBER;
5062: l_cpt_type VARCHAR2(100);
5063: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5064: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5065: H_SCORE HZ_PARTY_SEARCH.IDList;
5066:
5067: cnt NUMBER :=0;
5068: l_party_max_score NUMBER;
5069: l_ps_max_score NUMBER;
5090: l_match_str := ' AND ';
5091: ELSIF p_match_type = 'OR' THEN
5092: l_match_str := ' OR ';
5093: END IF;
5094: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5095: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5096: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5097: g_score_until_thresh := false;
5098: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5091: ELSIF p_match_type = 'OR' THEN
5092: l_match_str := ' OR ';
5093: END IF;
5094: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5095: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5096: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5097: g_score_until_thresh := false;
5098: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5099:
5099:
5100: -- ************************************************************
5101: -- STEP 2. Setup of intermedia query strings for Acquisition query
5102: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5103: 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);
5104:
5105: -- Setup Search Context ID
5106: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5107: x_search_ctx_id := l_search_ctx_id;
5174:
5175: PROCEDURE get_score_details (
5176: p_rule_id IN NUMBER,
5177: p_party_id IN NUMBER,
5178: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5179: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5180: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5181: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5182: x_search_ctx_id IN OUT NUMBER
5175: PROCEDURE get_score_details (
5176: p_rule_id IN NUMBER,
5177: p_party_id IN NUMBER,
5178: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5179: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5180: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5181: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5182: x_search_ctx_id IN OUT NUMBER
5183: ) IS
5176: p_rule_id IN NUMBER,
5177: p_party_id IN NUMBER,
5178: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5179: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5180: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5181: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5182: x_search_ctx_id IN OUT NUMBER
5183: ) IS
5184:
5177: p_party_id IN NUMBER,
5178: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5179: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5180: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5181: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5182: x_search_ctx_id IN OUT NUMBER
5183: ) IS
5184:
5185: -- Strings to hold the generated Intermedia query strings
5208: l_TX40 VARCHAR2(2000);
5209: l_max_TX40 VARCHAR2(2000);
5210: l_TX6 VARCHAR2(2000);
5211: l_max_TX6 VARCHAR2(2000);
5212: H_SCORES HZ_PARTY_SEARCH.score_list;
5213:
5214: l_score NUMBER;
5215: l_match_idx NUMBER;
5216: l_idx NUMBER;
5378: ************************************************/
5379:
5380: PROCEDURE get_party_for_search (
5381: p_party_id IN NUMBER,
5382: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5383: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5384: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5385: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5386: ) IS
5379:
5380: PROCEDURE get_party_for_search (
5381: p_party_id IN NUMBER,
5382: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5383: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5384: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5385: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5386: ) IS
5387: l_party_id NUMBER;
5380: PROCEDURE get_party_for_search (
5381: p_party_id IN NUMBER,
5382: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5383: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5384: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5385: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5386: ) IS
5387: l_party_id NUMBER;
5388: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5381: p_party_id IN NUMBER,
5382: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5383: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5384: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5385: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5386: ) IS
5387: l_party_id NUMBER;
5388: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5389: l_contact_ids HZ_PARTY_SEARCH.IDList;
5384: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5385: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5386: ) IS
5387: l_party_id NUMBER;
5388: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5389: l_contact_ids HZ_PARTY_SEARCH.IDList;
5390: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5391: ps NUMBER :=1;
5392: cpt NUMBER :=1;
5385: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5386: ) IS
5387: l_party_id NUMBER;
5388: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5389: l_contact_ids HZ_PARTY_SEARCH.IDList;
5390: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5391: ps NUMBER :=1;
5392: cpt NUMBER :=1;
5393: ct NUMBER :=1;
5386: ) IS
5387: l_party_id NUMBER;
5388: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5389: l_contact_ids HZ_PARTY_SEARCH.IDList;
5390: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5391: ps NUMBER :=1;
5392: cpt NUMBER :=1;
5393: ct NUMBER :=1;
5394: l_use_contact_info varchar2(1);
5460: END get_party_for_search;
5461:
5462: PROCEDURE get_search_criteria (
5463: p_party_id IN NUMBER,
5464: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5465: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5466: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5467: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5468: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5461:
5462: PROCEDURE get_search_criteria (
5463: p_party_id IN NUMBER,
5464: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5465: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5466: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5467: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5468: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5469: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5462: PROCEDURE get_search_criteria (
5463: p_party_id IN NUMBER,
5464: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5465: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5466: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5467: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5468: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5469: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5470: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5463: p_party_id IN NUMBER,
5464: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5465: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5466: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5467: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5468: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5469: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5470: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5471: ) IS
5464: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5465: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5466: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5467: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5468: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5469: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5470: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5471: ) IS
5472: BEGIN
5465: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5466: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5467: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5468: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5469: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5470: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5471: ) IS
5472: BEGIN
5473:
5466: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5467: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5468: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5469: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5470: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5471: ) IS
5472: BEGIN
5473:
5474: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5512: ************************************************/
5513:
5514: PROCEDURE get_party_rec (
5515: p_party_id IN NUMBER,
5516: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type
5517: ) IS
5518: l_party_type VARCHAR2(255);
5519: BEGIN
5520:
5555: the party site search record structure
5556: ************************************************/
5557:
5558: PROCEDURE get_party_site_rec (
5559: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5560: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5561: ) IS
5562: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5563: SELECT party_site_id
5556: ************************************************/
5557:
5558: PROCEDURE get_party_site_rec (
5559: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5560: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5561: ) IS
5562: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5563: SELECT party_site_id
5564: FROM HZ_PARTY_SITES, HZ_LOCATIONS
5607: the contact search record structure
5608: ************************************************/
5609:
5610: PROCEDURE get_contact_rec (
5611: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5612: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5613: ) IS
5614: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5615: SELECT org_contact_id
5608: ************************************************/
5609:
5610: PROCEDURE get_contact_rec (
5611: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5612: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5613: ) IS
5614: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5615: SELECT org_contact_id
5616: ,translate(HZ_ORG_CONTACTS.JOB_TITLE, '%',' ')
5665: the contact point search record structure
5666: ************************************************/
5667:
5668: PROCEDURE get_contact_point_rec (
5669: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5670: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5671: ) IS
5672:
5673: -- Cursor to fetch primary contact points for party
5666: ************************************************/
5667:
5668: PROCEDURE get_contact_point_rec (
5669: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5670: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5671: ) IS
5672:
5673: -- Cursor to fetch primary contact points for party
5674: CURSOR c_cpts(cp_contact_point_id NUMBER) IS
5724: ************************************************/
5725:
5726: PROCEDURE MAP_PARTY_REC(
5727: p_search_ctx IN BOOLEAN,
5728: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5729: x_entered_max_score OUT NUMBER,
5730: x_stage_rec IN OUT NOCOPY HZ_PARTY_STAGE.party_stage_rec_type
5731: ) IS
5732: tmp VARCHAR2(4000);
5821: ************************************************/
5822:
5823: PROCEDURE MAP_PARTY_SITE_REC(
5824: p_search_ctx IN BOOLEAN,
5825: p_search_list IN HZ_PARTY_SEARCH.party_site_list,
5826: x_entered_max_score OUT NUMBER,
5827: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.party_site_stage_list
5828: ) IS
5829: l_current_max_score NUMBER;
5864: ************************************************/
5865:
5866: PROCEDURE MAP_CONTACT_REC(
5867: p_search_ctx IN BOOLEAN,
5868: p_search_list IN HZ_PARTY_SEARCH.contact_list,
5869: x_entered_max_score OUT NUMBER,
5870: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_stage_list
5871: ) IS
5872: l_current_max_score NUMBER;
5921: ************************************************/
5922:
5923: PROCEDURE MAP_CONTACT_POINT_REC(
5924: p_search_ctx IN BOOLEAN,
5925: p_search_list IN HZ_PARTY_SEARCH.contact_point_list,
5926: x_entered_max_score OUT NUMBER,
5927: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_pt_stage_list
5928: ) IS
5929: l_current_max_score NUMBER;
6051: 2. Contact Point Type is not null for each condition
6052: ************************************************/
6053:
6054: FUNCTION check_prim_cond(
6055: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6056: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6057: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6058: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6059: RETURN BOOLEAN IS
6052: ************************************************/
6053:
6054: FUNCTION check_prim_cond(
6055: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6056: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6057: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6058: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6059: RETURN BOOLEAN IS
6060: BEGIN
6053:
6054: FUNCTION check_prim_cond(
6055: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6056: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6057: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6058: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6059: RETURN BOOLEAN IS
6060: BEGIN
6061: FOR I IN 1..p_contact_point_list.COUNT LOOP
6054: FUNCTION check_prim_cond(
6055: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6056: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6057: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6058: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6059: RETURN BOOLEAN IS
6060: BEGIN
6061: FOR I IN 1..p_contact_point_list.COUNT LOOP
6062: IF p_contact_point_list(I).CONTACT_POINT_TYPE IS NULL AND (
6104: has party site criteria.
6105: ************************************************/
6106:
6107: PROCEDURE check_party_site_cond(
6108: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6109: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6110: x_secondary OUT BOOLEAN,
6111: x_primary OUT BOOLEAN
6112: ) IS
6105: ************************************************/
6106:
6107: PROCEDURE check_party_site_cond(
6108: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6109: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6110: x_secondary OUT BOOLEAN,
6111: x_primary OUT BOOLEAN
6112: ) IS
6113: BEGIN
6150: has contact criteria.
6151: ************************************************/
6152:
6153: PROCEDURE check_contact_cond(
6154: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6155: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6156: x_secondary OUT BOOLEAN,
6157: x_primary OUT BOOLEAN
6158: ) IS
6151: ************************************************/
6152:
6153: PROCEDURE check_contact_cond(
6154: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6155: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6156: x_secondary OUT BOOLEAN,
6157: x_primary OUT BOOLEAN
6158: ) IS
6159: BEGIN
6206: has valid contact point criteria.
6207: ************************************************/
6208:
6209: PROCEDURE check_contact_point_cond(
6210: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6211: x_secondary OUT BOOLEAN,
6212: x_primary OUT BOOLEAN
6213: ) IS
6214: BEGIN