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;
468: PROCEDURE INSERT_PARTY_SCORE (
469: p_party_id IN NUMBER
470: ,p_record_id IN NUMBER
471: ,p_search_ctx_id IN NUMBER
472: ,p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
473: ,p_stage_rec IN HZ_PARTY_STAGE.party_stage_rec_type
474: ,p_table_TX4 VARCHAR2
475: ,p_table_TX8 VARCHAR2
476: ,p_table_TX19 VARCHAR2
602: PROCEDURE INSERT_PARTY_SITES_SCORE (
603: p_party_id IN NUMBER
604: ,p_record_id IN NUMBER
605: ,p_search_ctx_id IN NUMBER
606: ,p_search_rec IN HZ_PARTY_SEARCH.party_site_search_rec_type
607: ,p_stage_rec IN HZ_PARTY_STAGE.party_site_stage_rec_type
608: ,p_table_TX4 VARCHAR2
609: ,p_table_TX10 VARCHAR2
610: ,p_table_TX14 VARCHAR2
722: PROCEDURE INSERT_CONTACTS_SCORE (
723: p_party_id IN NUMBER
724: ,p_record_id IN NUMBER
725: ,p_search_ctx_id IN NUMBER
726: ,p_search_rec IN HZ_PARTY_SEARCH.contact_search_rec_type
727: ,p_stage_rec IN HZ_PARTY_STAGE.contact_stage_rec_type
728: ,p_idx IN NUMBER) IS
729: l_current_score NUMBER:=0;
730: l_score NUMBER;
738: PROCEDURE INSERT_CONTACT_POINTS_SCORE (
739: p_party_id IN NUMBER
740: ,p_record_id IN NUMBER
741: ,p_search_ctx_id IN NUMBER
742: ,p_search_rec IN HZ_PARTY_SEARCH.contact_point_search_rec_type
743: ,p_stage_rec IN HZ_PARTY_STAGE.contact_pt_stage_rec_type
744: ,p_idx IN NUMBER) IS
745: l_current_score NUMBER:=0;
746: l_score NUMBER;
757: for the PARTY Entity
758: ************************************************/
759:
760: PROCEDURE POP_PARTY_COND_REC(
761: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
762: ) IS
763: BEGIN
764: NULL ;
765: EXCEPTION
777: for the PARTY_SITES Entity
778: ************************************************/
779:
780: PROCEDURE POP_PARTY_SITES_COND_REC(
781: p_search_list IN HZ_PARTY_SEARCH.party_site_list
782: ) IS
783: BEGIN
784: ---------POPULATE THE GLOBAL WORD CONDITION REC FOR PARTY_SITES-------------
785: ------ Populate global condition record only if search list is not empty -----------
802: for the CONTACTS Entity
803: ************************************************/
804:
805: PROCEDURE POP_CONTACTS_COND_REC(
806: p_search_list IN HZ_PARTY_SEARCH.contact_list
807: ) IS
808: BEGIN
809: NULL ;
810: EXCEPTION
822: for the CONTACT_POINTS Entity
823: ************************************************/
824:
825: PROCEDURE POP_CONTACT_POINTS_COND_REC(
826: p_search_list IN HZ_PARTY_SEARCH.contact_point_list
827: ) IS
828: BEGIN
829: NULL ;
830: EXCEPTION
836: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
837: END;
838:
839: PROCEDURE init_score_context (
840: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
841: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
842: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
843: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
844: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
837: END;
838:
839: PROCEDURE init_score_context (
840: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
841: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
842: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
843: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
844: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
845: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
838:
839: PROCEDURE init_score_context (
840: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
841: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
842: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
843: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
844: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
845: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
846: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
839: PROCEDURE init_score_context (
840: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
841: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
842: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
843: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
844: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
845: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
846: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
847: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
840: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
841: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
842: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
843: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
844: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
845: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
846: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
847: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
848: ) IS
841: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
842: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
843: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
844: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
845: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
846: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
847: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
848: ) IS
849: l_dummy NUMBER;
842: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
843: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
844: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
845: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
846: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
847: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
848: ) IS
849: l_dummy NUMBER;
850: BEGIN
843: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
844: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
845: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
846: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
847: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
848: ) IS
849: l_dummy NUMBER;
850: BEGIN
851: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
863: MAP_CONTACT_POINT_REC(FALSE,p_contact_point_list, l_dummy, g_contact_pt_stage_list);
864:
865: END;
866: FUNCTION init_search(
867: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
868: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
869: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
870: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
871: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
864:
865: END;
866: FUNCTION init_search(
867: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
868: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
869: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
870: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
871: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
872: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
865: END;
866: FUNCTION init_search(
867: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
868: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
869: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
870: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
871: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
872: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
873: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
866: FUNCTION init_search(
867: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
868: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
869: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
870: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
871: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
872: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
873: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
874: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
867: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
868: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
869: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
870: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
871: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
872: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
873: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
874: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
875: p_match_type IN VARCHAR2,
868: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
869: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
870: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
871: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
872: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
873: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
874: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
875: p_match_type IN VARCHAR2,
876: x_party_max_score OUT NUMBER,
869: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
870: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
871: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
872: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
873: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
874: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
875: p_match_type IN VARCHAR2,
876: x_party_max_score OUT NUMBER,
877: x_ps_max_score OUT NUMBER,
870: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
871: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
872: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
873: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
874: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
875: p_match_type IN VARCHAR2,
876: x_party_max_score OUT NUMBER,
877: x_ps_max_score OUT NUMBER,
878: x_contact_max_score OUT NUMBER,
882: l_ps_entered_max_score NUMBER:=0;
883: l_ct_entered_max_score NUMBER:=0;
884: l_cpt_entered_max_score NUMBER:=0;
885: vlist vlisttype;
886: maxscore HZ_PARTY_SEARCH.IDList;
887: l_name VARCHAR2(200);
888: l_idx NUMBER;
889: l_num NUMBER;
890: total NUMBER;
2269: p_party_id NUMBER,
2270: p_party_site_id NUMBER,
2271: p_org_contact_id NUMBER,
2272: p_contact_point_id NUMBER)
2273: RETURN HZ_PARTY_SEARCH.score_rec IS
2274: l_score_rec HZ_PARTY_SEARCH.score_rec;
2275: BEGIN
2276: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2277: 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);
2270: p_party_site_id NUMBER,
2271: p_org_contact_id NUMBER,
2272: p_contact_point_id NUMBER)
2273: RETURN HZ_PARTY_SEARCH.score_rec IS
2274: l_score_rec HZ_PARTY_SEARCH.score_rec;
2275: BEGIN
2276: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2277: 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);
2278: 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);
2302: **********************************************************/
2303:
2304: PROCEDURE find_parties (
2305: p_rule_id IN NUMBER,
2306: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2307: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2308: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2309: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2310: p_restrict_sql IN VARCHAR2,
2303:
2304: PROCEDURE find_parties (
2305: p_rule_id IN NUMBER,
2306: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2307: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2308: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2309: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2310: p_restrict_sql IN VARCHAR2,
2311: p_match_type IN VARCHAR2,
2304: PROCEDURE find_parties (
2305: p_rule_id IN NUMBER,
2306: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2307: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2308: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2309: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2310: p_restrict_sql IN VARCHAR2,
2311: p_match_type IN VARCHAR2,
2312: p_search_merged IN VARCHAR2,
2305: p_rule_id IN NUMBER,
2306: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2307: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2308: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2309: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2310: p_restrict_sql IN VARCHAR2,
2311: p_match_type IN VARCHAR2,
2312: p_search_merged IN VARCHAR2,
2313: p_dup_party_id IN NUMBER,
2346: l_TX8 VARCHAR2(2000);
2347: l_TX34 VARCHAR2(2000);
2348: l_TX36 VARCHAR2(2000);
2349: l_TX14 VARCHAR2(2000);
2350: H_SCORES HZ_PARTY_SEARCH.score_list;
2351: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2352: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2353:
2354: l_score NUMBER;
2347: l_TX34 VARCHAR2(2000);
2348: l_TX36 VARCHAR2(2000);
2349: l_TX14 VARCHAR2(2000);
2350: H_SCORES HZ_PARTY_SEARCH.score_list;
2351: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2352: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2353:
2354: l_score NUMBER;
2355: l_idx NUMBER;
2348: l_TX36 VARCHAR2(2000);
2349: l_TX14 VARCHAR2(2000);
2350: H_SCORES HZ_PARTY_SEARCH.score_list;
2351: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2352: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2353:
2354: l_score NUMBER;
2355: l_idx NUMBER;
2356: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
2540: /** Private procedure to acquire and score at party site level ***/
2541: 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
2542: l_party_id_idx NUMBER:=1;
2543: l_ctx_id NUMBER;
2544: h_ps_id HZ_PARTY_SEARCH.IDList;
2545: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2546: h_ps_score HZ_PARTY_SEARCH.IDList;
2547: detcnt NUMBER := 1;
2548: BEGIN
2541: 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
2542: l_party_id_idx NUMBER:=1;
2543: l_ctx_id NUMBER;
2544: h_ps_id HZ_PARTY_SEARCH.IDList;
2545: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2546: h_ps_score HZ_PARTY_SEARCH.IDList;
2547: detcnt NUMBER := 1;
2548: BEGIN
2549: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2542: l_party_id_idx NUMBER:=1;
2543: l_ctx_id NUMBER;
2544: h_ps_id HZ_PARTY_SEARCH.IDList;
2545: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2546: h_ps_score HZ_PARTY_SEARCH.IDList;
2547: detcnt NUMBER := 1;
2548: BEGIN
2549: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2550: 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);
2662: /** Private procedure to acquire and score at contact point level ***/
2663: 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
2664: l_party_id_idx NUMBER:=1;
2665: l_ctx_id NUMBER;
2666: h_ct_id HZ_PARTY_SEARCH.IDList;
2667: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2668: h_ct_score HZ_PARTY_SEARCH.IDList;
2669: detcnt NUMBER := 1;
2670: BEGIN
2663: 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
2664: l_party_id_idx NUMBER:=1;
2665: l_ctx_id NUMBER;
2666: h_ct_id HZ_PARTY_SEARCH.IDList;
2667: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2668: h_ct_score HZ_PARTY_SEARCH.IDList;
2669: detcnt NUMBER := 1;
2670: BEGIN
2671: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2664: l_party_id_idx NUMBER:=1;
2665: l_ctx_id NUMBER;
2666: h_ct_id HZ_PARTY_SEARCH.IDList;
2667: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2668: h_ct_score HZ_PARTY_SEARCH.IDList;
2669: detcnt NUMBER := 1;
2670: BEGIN
2671: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2672: 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);
2771: /** Private procedure to acquire and score at contact point level ***/
2772: 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
2773: l_party_id_idx NUMBER:=1;
2774: l_ctx_id NUMBER;
2775: h_cpt_id HZ_PARTY_SEARCH.IDList;
2776: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2777: h_cpt_score HZ_PARTY_SEARCH.IDList;
2778: detcnt NUMBER := 1;
2779: l_cpt_flag VARCHAR2(1) := 'N';
2772: 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
2773: l_party_id_idx NUMBER:=1;
2774: l_ctx_id NUMBER;
2775: h_cpt_id HZ_PARTY_SEARCH.IDList;
2776: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2777: h_cpt_score HZ_PARTY_SEARCH.IDList;
2778: detcnt NUMBER := 1;
2779: l_cpt_flag VARCHAR2(1) := 'N';
2780: is_a_match VARCHAR2(1) := 'Y';
2773: l_party_id_idx NUMBER:=1;
2774: l_ctx_id NUMBER;
2775: h_cpt_id HZ_PARTY_SEARCH.IDList;
2776: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2777: h_cpt_score HZ_PARTY_SEARCH.IDList;
2778: detcnt NUMBER := 1;
2779: l_cpt_flag VARCHAR2(1) := 'N';
2780: is_a_match VARCHAR2(1) := 'Y';
2781: BEGIN
3222: END find_parties;
3223:
3224: PROCEDURE find_persons (
3225: p_rule_id IN NUMBER,
3226: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3227: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3228: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3229: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3230: p_restrict_sql IN VARCHAR2,
3223:
3224: PROCEDURE find_persons (
3225: p_rule_id IN NUMBER,
3226: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3227: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3228: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3229: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3230: p_restrict_sql IN VARCHAR2,
3231: p_match_type IN VARCHAR2,
3224: PROCEDURE find_persons (
3225: p_rule_id IN NUMBER,
3226: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3227: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3228: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3229: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3230: p_restrict_sql IN VARCHAR2,
3231: p_match_type IN VARCHAR2,
3232: p_search_merged IN VARCHAR2,
3225: p_rule_id IN NUMBER,
3226: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3227: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3228: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3229: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3230: p_restrict_sql IN VARCHAR2,
3231: p_match_type IN VARCHAR2,
3232: p_search_merged IN VARCHAR2,
3233: p_dup_party_id IN NUMBER,
3266: l_TX8 VARCHAR2(2000);
3267: l_TX34 VARCHAR2(2000);
3268: l_TX36 VARCHAR2(2000);
3269: l_TX14 VARCHAR2(2000);
3270: H_SCORES HZ_PARTY_SEARCH.score_list;
3271: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3272: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3273:
3274: l_score NUMBER;
3267: l_TX34 VARCHAR2(2000);
3268: l_TX36 VARCHAR2(2000);
3269: l_TX14 VARCHAR2(2000);
3270: H_SCORES HZ_PARTY_SEARCH.score_list;
3271: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3272: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3273:
3274: l_score NUMBER;
3275: l_idx NUMBER;
3268: l_TX36 VARCHAR2(2000);
3269: l_TX14 VARCHAR2(2000);
3270: H_SCORES HZ_PARTY_SEARCH.score_list;
3271: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3272: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3273:
3274: l_score NUMBER;
3275: l_idx NUMBER;
3276: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
3508: /** Private procedure to acquire and score at party site level ***/
3509: 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
3510: l_party_id_idx NUMBER:=1;
3511: l_ctx_id NUMBER;
3512: h_ps_id HZ_PARTY_SEARCH.IDList;
3513: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3514: h_ps_score HZ_PARTY_SEARCH.IDList;
3515: detcnt NUMBER := 1;
3516: l_person_id NUMBER;
3509: 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
3510: l_party_id_idx NUMBER:=1;
3511: l_ctx_id NUMBER;
3512: h_ps_id HZ_PARTY_SEARCH.IDList;
3513: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3514: h_ps_score HZ_PARTY_SEARCH.IDList;
3515: detcnt NUMBER := 1;
3516: l_person_id NUMBER;
3517: BEGIN
3510: l_party_id_idx NUMBER:=1;
3511: l_ctx_id NUMBER;
3512: h_ps_id HZ_PARTY_SEARCH.IDList;
3513: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3514: h_ps_score HZ_PARTY_SEARCH.IDList;
3515: detcnt NUMBER := 1;
3516: l_person_id NUMBER;
3517: BEGIN
3518: SAVEPOINT eval_start;
3607: /** Private procedure to acquire and score at party site level ***/
3608: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3609: l_party_id_idx NUMBER:=1;
3610: l_ctx_id NUMBER;
3611: h_ct_id HZ_PARTY_SEARCH.IDList;
3612: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3613: h_ct_score HZ_PARTY_SEARCH.IDList;
3614: detcnt NUMBER := 1;
3615: l_person_id NUMBER;
3608: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3609: l_party_id_idx NUMBER:=1;
3610: l_ctx_id NUMBER;
3611: h_ct_id HZ_PARTY_SEARCH.IDList;
3612: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3613: h_ct_score HZ_PARTY_SEARCH.IDList;
3614: detcnt NUMBER := 1;
3615: l_person_id NUMBER;
3616: BEGIN
3609: l_party_id_idx NUMBER:=1;
3610: l_ctx_id NUMBER;
3611: h_ct_id HZ_PARTY_SEARCH.IDList;
3612: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3613: h_ct_score HZ_PARTY_SEARCH.IDList;
3614: detcnt NUMBER := 1;
3615: l_person_id NUMBER;
3616: BEGIN
3617: SAVEPOINT eval_start;
3665: /** Private procedure to acquire and score at contact point level ***/
3666: 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
3667: l_party_id_idx NUMBER:=1;
3668: l_ctx_id NUMBER;
3669: h_cpt_id HZ_PARTY_SEARCH.IDList;
3670: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3671: h_cpt_score HZ_PARTY_SEARCH.IDList;
3672: detcnt NUMBER := 1;
3673: l_person_id NUMBER;
3666: 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
3667: l_party_id_idx NUMBER:=1;
3668: l_ctx_id NUMBER;
3669: h_cpt_id HZ_PARTY_SEARCH.IDList;
3670: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3671: h_cpt_score HZ_PARTY_SEARCH.IDList;
3672: detcnt NUMBER := 1;
3673: l_person_id NUMBER;
3674: is_a_match VARCHAR2(1) := 'Y';
3667: l_party_id_idx NUMBER:=1;
3668: l_ctx_id NUMBER;
3669: h_cpt_id HZ_PARTY_SEARCH.IDList;
3670: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3671: h_cpt_score HZ_PARTY_SEARCH.IDList;
3672: detcnt NUMBER := 1;
3673: l_person_id NUMBER;
3674: is_a_match VARCHAR2(1) := 'Y';
3675: l_cpt_flag VARCHAR2(1) := 'N';
4009: END find_persons;
4010:
4011: PROCEDURE find_persons (
4012: p_rule_id IN NUMBER,
4013: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4014: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4015: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4016: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4017: p_restrict_sql IN VARCHAR2,
4010:
4011: PROCEDURE find_persons (
4012: p_rule_id IN NUMBER,
4013: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4014: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4015: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4016: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4017: p_restrict_sql IN VARCHAR2,
4018: p_match_type IN VARCHAR2,
4011: PROCEDURE find_persons (
4012: p_rule_id IN NUMBER,
4013: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4014: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4015: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4016: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4017: p_restrict_sql IN VARCHAR2,
4018: p_match_type IN VARCHAR2,
4019: p_search_merged IN VARCHAR2,
4012: p_rule_id IN NUMBER,
4013: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4014: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4015: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4016: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4017: p_restrict_sql IN VARCHAR2,
4018: p_match_type IN VARCHAR2,
4019: p_search_merged IN VARCHAR2,
4020: p_ins_details IN VARCHAR2,
4026: 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);
4027: END;
4028: PROCEDURE find_party_details (
4029: p_rule_id IN NUMBER,
4030: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4031: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4032: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4033: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4034: p_restrict_sql IN VARCHAR2,
4027: END;
4028: PROCEDURE find_party_details (
4029: p_rule_id IN NUMBER,
4030: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4031: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4032: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4033: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4034: p_restrict_sql IN VARCHAR2,
4035: p_match_type IN VARCHAR2,
4028: PROCEDURE find_party_details (
4029: p_rule_id IN NUMBER,
4030: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4031: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4032: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4033: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4034: p_restrict_sql IN VARCHAR2,
4035: p_match_type IN VARCHAR2,
4036: p_search_merged IN VARCHAR2,
4029: p_rule_id IN NUMBER,
4030: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4031: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4032: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4033: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4034: p_restrict_sql IN VARCHAR2,
4035: p_match_type IN VARCHAR2,
4036: p_search_merged IN VARCHAR2,
4037: x_search_ctx_id OUT NUMBER,
4086: x_dup_set_id OUT NUMBER,
4087: x_search_ctx_id OUT NUMBER,
4088: x_num_matches OUT NUMBER
4089: ) IS
4090: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4091: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4092: l_contact_list HZ_PARTY_SEARCH.contact_list;
4093: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4094: l_match_idx NUMBER;
4087: x_search_ctx_id OUT NUMBER,
4088: x_num_matches OUT NUMBER
4089: ) IS
4090: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4091: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4092: l_contact_list HZ_PARTY_SEARCH.contact_list;
4093: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4094: l_match_idx NUMBER;
4095:
4088: x_num_matches OUT NUMBER
4089: ) IS
4090: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4091: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4092: l_contact_list HZ_PARTY_SEARCH.contact_list;
4093: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4094: l_match_idx NUMBER;
4095:
4096: --Fix for bug 4417124
4089: ) IS
4090: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4091: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4092: l_contact_list HZ_PARTY_SEARCH.contact_list;
4093: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4094: l_match_idx NUMBER;
4095:
4096: --Fix for bug 4417124
4097: l_use_contact_addr_info BOOLEAN := TRUE;
4202: x_search_ctx_id OUT NUMBER,
4203: x_num_matches OUT NUMBER
4204: ) IS
4205:
4206: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4207: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4208: l_contact_list HZ_PARTY_SEARCH.contact_list;
4209: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4210: contact_point_ids HZ_PARTY_SEARCH.IDList;
4203: x_num_matches OUT NUMBER
4204: ) IS
4205:
4206: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4207: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4208: l_contact_list HZ_PARTY_SEARCH.contact_list;
4209: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4210: contact_point_ids HZ_PARTY_SEARCH.IDList;
4211: p_party_site_list HZ_PARTY_SEARCH.IDList;
4204: ) IS
4205:
4206: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4207: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4208: l_contact_list HZ_PARTY_SEARCH.contact_list;
4209: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4210: contact_point_ids HZ_PARTY_SEARCH.IDList;
4211: p_party_site_list HZ_PARTY_SEARCH.IDList;
4212: p_contact_ids HZ_PARTY_SEARCH.IDList;
4205:
4206: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4207: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4208: l_contact_list HZ_PARTY_SEARCH.contact_list;
4209: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4210: contact_point_ids HZ_PARTY_SEARCH.IDList;
4211: p_party_site_list HZ_PARTY_SEARCH.IDList;
4212: p_contact_ids HZ_PARTY_SEARCH.IDList;
4213: l_match_idx NUMBER;
4206: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4207: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4208: l_contact_list HZ_PARTY_SEARCH.contact_list;
4209: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4210: contact_point_ids HZ_PARTY_SEARCH.IDList;
4211: p_party_site_list HZ_PARTY_SEARCH.IDList;
4212: p_contact_ids HZ_PARTY_SEARCH.IDList;
4213: l_match_idx NUMBER;
4214: cursor get_cpts_for_party_sites is select contact_point_id
4207: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4208: l_contact_list HZ_PARTY_SEARCH.contact_list;
4209: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4210: contact_point_ids HZ_PARTY_SEARCH.IDList;
4211: p_party_site_list HZ_PARTY_SEARCH.IDList;
4212: p_contact_ids HZ_PARTY_SEARCH.IDList;
4213: l_match_idx NUMBER;
4214: cursor get_cpts_for_party_sites is select contact_point_id
4215: from hz_contact_points
4208: l_contact_list HZ_PARTY_SEARCH.contact_list;
4209: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4210: contact_point_ids HZ_PARTY_SEARCH.IDList;
4211: p_party_site_list HZ_PARTY_SEARCH.IDList;
4212: p_contact_ids HZ_PARTY_SEARCH.IDList;
4213: l_match_idx NUMBER;
4214: cursor get_cpts_for_party_sites is select contact_point_id
4215: from hz_contact_points
4216: where owner_table_name = 'HZ_PARTY_SITES'
4237:
4238: get_search_criteria (
4239: null,
4240: p_party_site_list,
4241: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4242: contact_point_ids,
4243: l_party_search_rec,
4244: l_party_site_list,
4245: l_contact_list,
4287: x_search_ctx_id OUT NUMBER,
4288: x_num_matches OUT NUMBER
4289: ) IS
4290:
4291: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4292: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4293: l_contact_list HZ_PARTY_SEARCH.contact_list;
4294: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4295: contact_point_ids HZ_PARTY_SEARCH.IDList;
4288: x_num_matches OUT NUMBER
4289: ) IS
4290:
4291: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4292: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4293: l_contact_list HZ_PARTY_SEARCH.contact_list;
4294: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4295: contact_point_ids HZ_PARTY_SEARCH.IDList;
4296: p_party_site_list HZ_PARTY_SEARCH.IDList;
4289: ) IS
4290:
4291: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4292: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4293: l_contact_list HZ_PARTY_SEARCH.contact_list;
4294: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4295: contact_point_ids HZ_PARTY_SEARCH.IDList;
4296: p_party_site_list HZ_PARTY_SEARCH.IDList;
4297: p_contact_ids HZ_PARTY_SEARCH.IDList;
4290:
4291: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4292: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4293: l_contact_list HZ_PARTY_SEARCH.contact_list;
4294: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4295: contact_point_ids HZ_PARTY_SEARCH.IDList;
4296: p_party_site_list HZ_PARTY_SEARCH.IDList;
4297: p_contact_ids HZ_PARTY_SEARCH.IDList;
4298: l_match_idx NUMBER;
4291: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4292: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4293: l_contact_list HZ_PARTY_SEARCH.contact_list;
4294: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4295: contact_point_ids HZ_PARTY_SEARCH.IDList;
4296: p_party_site_list HZ_PARTY_SEARCH.IDList;
4297: p_contact_ids HZ_PARTY_SEARCH.IDList;
4298: l_match_idx NUMBER;
4299: cursor get_cpt_for_contact_id is select contact_point_id
4292: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4293: l_contact_list HZ_PARTY_SEARCH.contact_list;
4294: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4295: contact_point_ids HZ_PARTY_SEARCH.IDList;
4296: p_party_site_list HZ_PARTY_SEARCH.IDList;
4297: p_contact_ids HZ_PARTY_SEARCH.IDList;
4298: l_match_idx NUMBER;
4299: cursor get_cpt_for_contact_id is select contact_point_id
4300: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4293: l_contact_list HZ_PARTY_SEARCH.contact_list;
4294: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4295: contact_point_ids HZ_PARTY_SEARCH.IDList;
4296: p_party_site_list HZ_PARTY_SEARCH.IDList;
4297: p_contact_ids HZ_PARTY_SEARCH.IDList;
4298: l_match_idx NUMBER;
4299: cursor get_cpt_for_contact_id is select contact_point_id
4300: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4301: where a.party_relationship_id = b.relationship_id
4324: CLOSE get_cpt_for_contact_id;
4325:
4326: get_search_criteria (
4327: null,
4328: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4329: p_contact_ids,
4330: contact_point_ids,
4331: l_party_search_rec,
4332: l_party_site_list,
4375: p_match_type IN VARCHAR2,
4376: x_search_ctx_id OUT NUMBER,
4377: x_num_matches OUT NUMBER
4378: ) IS
4379: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4380: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4381: l_contact_list HZ_PARTY_SEARCH.contact_list;
4382: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4383: contact_point_ids HZ_PARTY_SEARCH.IDList;
4376: x_search_ctx_id OUT NUMBER,
4377: x_num_matches OUT NUMBER
4378: ) IS
4379: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4380: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4381: l_contact_list HZ_PARTY_SEARCH.contact_list;
4382: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4383: contact_point_ids HZ_PARTY_SEARCH.IDList;
4384: p_party_site_list HZ_PARTY_SEARCH.IDList;
4377: x_num_matches OUT NUMBER
4378: ) IS
4379: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4380: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4381: l_contact_list HZ_PARTY_SEARCH.contact_list;
4382: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4383: contact_point_ids HZ_PARTY_SEARCH.IDList;
4384: p_party_site_list HZ_PARTY_SEARCH.IDList;
4385: p_contact_ids HZ_PARTY_SEARCH.IDList;
4378: ) IS
4379: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4380: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4381: l_contact_list HZ_PARTY_SEARCH.contact_list;
4382: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4383: contact_point_ids HZ_PARTY_SEARCH.IDList;
4384: p_party_site_list HZ_PARTY_SEARCH.IDList;
4385: p_contact_ids HZ_PARTY_SEARCH.IDList;
4386: l_match_idx NUMBER;
4379: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4380: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4381: l_contact_list HZ_PARTY_SEARCH.contact_list;
4382: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4383: contact_point_ids HZ_PARTY_SEARCH.IDList;
4384: p_party_site_list HZ_PARTY_SEARCH.IDList;
4385: p_contact_ids HZ_PARTY_SEARCH.IDList;
4386: l_match_idx NUMBER;
4387:
4380: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4381: l_contact_list HZ_PARTY_SEARCH.contact_list;
4382: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4383: contact_point_ids HZ_PARTY_SEARCH.IDList;
4384: p_party_site_list HZ_PARTY_SEARCH.IDList;
4385: p_contact_ids HZ_PARTY_SEARCH.IDList;
4386: l_match_idx NUMBER;
4387:
4388: BEGIN
4381: l_contact_list HZ_PARTY_SEARCH.contact_list;
4382: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4383: contact_point_ids HZ_PARTY_SEARCH.IDList;
4384: p_party_site_list HZ_PARTY_SEARCH.IDList;
4385: p_contact_ids HZ_PARTY_SEARCH.IDList;
4386: l_match_idx NUMBER;
4387:
4388: BEGIN
4389: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4398: END IF;
4399: contact_point_ids(1) := p_contact_point_id;
4400: get_search_criteria (
4401: null,
4402: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4403: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4404: contact_point_ids,
4405: l_party_search_rec,
4406: l_party_site_list,
4399: contact_point_ids(1) := p_contact_point_id;
4400: get_search_criteria (
4401: null,
4402: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4403: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4404: contact_point_ids,
4405: l_party_search_rec,
4406: l_party_site_list,
4407: l_contact_list,
4552: ) IS
4553: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4554: l_match_idx NUMBER;
4555: AttrVals AttrList;
4556: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4557: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4558: l_contact_list HZ_PARTY_SEARCH.contact_list;
4559: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4560: l_dup_set_id NUMBER;
4553: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4554: l_match_idx NUMBER;
4555: AttrVals AttrList;
4556: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4557: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4558: l_contact_list HZ_PARTY_SEARCH.contact_list;
4559: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4560: l_dup_set_id NUMBER;
4561: l_idx NUMBER;
4554: l_match_idx NUMBER;
4555: AttrVals AttrList;
4556: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4557: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4558: l_contact_list HZ_PARTY_SEARCH.contact_list;
4559: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4560: l_dup_set_id NUMBER;
4561: l_idx NUMBER;
4562: l_cpt_type VARCHAR2(255);
4555: AttrVals AttrList;
4556: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4557: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4558: l_contact_list HZ_PARTY_SEARCH.contact_list;
4559: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4560: l_dup_set_id NUMBER;
4561: l_idx NUMBER;
4562: l_cpt_type VARCHAR2(255);
4563: FIRST BOOLEAN := TRUE;
4731:
4732: PROCEDURE get_matching_party_sites (
4733: p_rule_id IN NUMBER,
4734: p_party_id IN NUMBER,
4735: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4736: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4737: p_restrict_sql IN VARCHAR2,
4738: p_match_type IN VARCHAR2,
4739: p_dup_party_site_id IN NUMBER,
4732: PROCEDURE get_matching_party_sites (
4733: p_rule_id IN NUMBER,
4734: p_party_id IN NUMBER,
4735: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4736: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4737: p_restrict_sql IN VARCHAR2,
4738: p_match_type IN VARCHAR2,
4739: p_dup_party_site_id IN NUMBER,
4740: x_search_ctx_id OUT NUMBER,
4761: l_TX22 VARCHAR2(2000);
4762: l_TX4 VARCHAR2(2000);
4763: l_TX10 VARCHAR2(2000);
4764: l_TX14 VARCHAR2(2000);
4765: H_SCORES HZ_PARTY_SEARCH.score_list;
4766:
4767: l_score NUMBER;
4768: l_idx NUMBER;
4769: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
4792: defps NUMBER :=0;
4793: defct NUMBER :=0;
4794: defcpt NUMBER :=0;
4795: l_index NUMBER;
4796: l_match_ps_list HZ_PARTY_SEARCH.IDList;
4797: l_cnt NUMBER:=1;
4798:
4799:
4800: BEGIN
4816: l_match_str := ' AND ';
4817: ELSIF p_match_type = 'OR' THEN
4818: l_match_str := ' OR ';
4819: END IF;
4820: 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);
4821: g_score_until_thresh := false;
4822: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4823:
4824: -- ************************************************************
4824: -- ************************************************************
4825: -- STEP 2. Setup of intermedia query strings for Acquisition query
4826: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4827: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4828: 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);
4829:
4830: -- Setup Search Context ID
4831: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4832: x_search_ctx_id := l_search_ctx_id;
4967:
4968: PROCEDURE get_matching_contacts (
4969: p_rule_id IN NUMBER,
4970: p_party_id IN NUMBER,
4971: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4972: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4973: p_restrict_sql IN VARCHAR2,
4974: p_match_type IN VARCHAR2,
4975: p_dup_contact_id IN NUMBER,
4968: PROCEDURE get_matching_contacts (
4969: p_rule_id IN NUMBER,
4970: p_party_id IN NUMBER,
4971: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4972: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4973: p_restrict_sql IN VARCHAR2,
4974: p_match_type IN VARCHAR2,
4975: p_dup_contact_id IN NUMBER,
4976: x_search_ctx_id OUT NUMBER,
4993: l_entered_max_score NUMBER;
4994: FIRST BOOLEAN;
4995: l_search_ctx_id NUMBER; -- Generated Search Context ID
4996:
4997: H_SCORES HZ_PARTY_SEARCH.score_list;
4998:
4999: l_score NUMBER;
5000: l_idx NUMBER;
5001: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
5021: l_party_max_score NUMBER;
5022: l_ps_max_score NUMBER;
5023: l_contact_max_score NUMBER;
5024: l_cpt_max_score NUMBER;
5025: l_match_contact_list HZ_PARTY_SEARCH.IDList;
5026: l_cnt NUMBER:=1;
5027:
5028:
5029: BEGIN
5045: l_match_str := ' AND ';
5046: ELSIF p_match_type = 'OR' THEN
5047: l_match_str := ' OR ';
5048: END IF;
5049: 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);
5050: g_score_until_thresh := false;
5051: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5052:
5053: -- ************************************************************
5053: -- ************************************************************
5054: -- STEP 2. Setup of intermedia query strings for Acquisition query
5055: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5056: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5057: 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);
5058:
5059: -- Setup Search Context ID
5060: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5061: x_search_ctx_id := l_search_ctx_id;
5192:
5193: PROCEDURE get_matching_contact_points (
5194: p_rule_id IN NUMBER,
5195: p_party_id IN NUMBER,
5196: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5197: p_restrict_sql IN VARCHAR2,
5198: p_match_type IN VARCHAR2,
5199: p_dup_contact_point_id IN NUMBER,
5200: x_search_ctx_id OUT NUMBER,
5221: l_cpt_ps_id NUMBER;
5222: l_cpt_contact_id NUMBER;
5223: l_contact_pt_id NUMBER;
5224: l_cpt_type VARCHAR2(100);
5225: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5226: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5227: H_SCORE HZ_PARTY_SEARCH.IDList;
5228:
5229: cnt NUMBER :=0;
5222: l_cpt_contact_id NUMBER;
5223: l_contact_pt_id NUMBER;
5224: l_cpt_type VARCHAR2(100);
5225: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5226: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5227: H_SCORE HZ_PARTY_SEARCH.IDList;
5228:
5229: cnt NUMBER :=0;
5230: l_party_max_score NUMBER;
5223: l_contact_pt_id NUMBER;
5224: l_cpt_type VARCHAR2(100);
5225: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5226: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5227: H_SCORE HZ_PARTY_SEARCH.IDList;
5228:
5229: cnt NUMBER :=0;
5230: l_party_max_score NUMBER;
5231: l_ps_max_score NUMBER;
5252: l_match_str := ' AND ';
5253: ELSIF p_match_type = 'OR' THEN
5254: l_match_str := ' OR ';
5255: END IF;
5256: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5257: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5258: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5259: g_score_until_thresh := false;
5260: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5253: ELSIF p_match_type = 'OR' THEN
5254: l_match_str := ' OR ';
5255: END IF;
5256: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5257: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5258: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5259: g_score_until_thresh := false;
5260: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5261:
5261:
5262: -- ************************************************************
5263: -- STEP 2. Setup of intermedia query strings for Acquisition query
5264: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5265: 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);
5266:
5267: -- Setup Search Context ID
5268: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5269: x_search_ctx_id := l_search_ctx_id;
5336:
5337: PROCEDURE get_score_details (
5338: p_rule_id IN NUMBER,
5339: p_party_id IN NUMBER,
5340: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5341: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5342: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5343: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5344: x_search_ctx_id IN OUT NUMBER
5337: PROCEDURE get_score_details (
5338: p_rule_id IN NUMBER,
5339: p_party_id IN NUMBER,
5340: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5341: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5342: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5343: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5344: x_search_ctx_id IN OUT NUMBER
5345: ) IS
5338: p_rule_id IN NUMBER,
5339: p_party_id IN NUMBER,
5340: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5341: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5342: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5343: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5344: x_search_ctx_id IN OUT NUMBER
5345: ) IS
5346:
5339: p_party_id IN NUMBER,
5340: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5341: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5342: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5343: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5344: x_search_ctx_id IN OUT NUMBER
5345: ) IS
5346:
5347: -- Strings to hold the generated Intermedia query strings
5376: l_TX36 VARCHAR2(2000);
5377: l_max_TX36 VARCHAR2(2000);
5378: l_TX14 VARCHAR2(2000);
5379: l_max_TX14 VARCHAR2(2000);
5380: H_SCORES HZ_PARTY_SEARCH.score_list;
5381:
5382: l_score NUMBER;
5383: l_match_idx NUMBER;
5384: l_idx NUMBER;
5546: ************************************************/
5547:
5548: PROCEDURE get_party_for_search (
5549: p_party_id IN NUMBER,
5550: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5551: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5552: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5553: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5554: ) IS
5547:
5548: PROCEDURE get_party_for_search (
5549: p_party_id IN NUMBER,
5550: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5551: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5552: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5553: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5554: ) IS
5555: l_party_id NUMBER;
5548: PROCEDURE get_party_for_search (
5549: p_party_id IN NUMBER,
5550: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5551: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5552: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5553: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5554: ) IS
5555: l_party_id NUMBER;
5556: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5549: p_party_id IN NUMBER,
5550: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5551: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5552: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5553: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5554: ) IS
5555: l_party_id NUMBER;
5556: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5557: l_contact_ids HZ_PARTY_SEARCH.IDList;
5552: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5553: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5554: ) IS
5555: l_party_id NUMBER;
5556: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5557: l_contact_ids HZ_PARTY_SEARCH.IDList;
5558: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5559: ps NUMBER :=1;
5560: cpt NUMBER :=1;
5553: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5554: ) IS
5555: l_party_id NUMBER;
5556: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5557: l_contact_ids HZ_PARTY_SEARCH.IDList;
5558: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5559: ps NUMBER :=1;
5560: cpt NUMBER :=1;
5561: ct NUMBER :=1;
5554: ) IS
5555: l_party_id NUMBER;
5556: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5557: l_contact_ids HZ_PARTY_SEARCH.IDList;
5558: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5559: ps NUMBER :=1;
5560: cpt NUMBER :=1;
5561: ct NUMBER :=1;
5562: l_use_contact_info varchar2(1);
5618: END get_party_for_search;
5619:
5620: PROCEDURE get_search_criteria (
5621: p_party_id IN NUMBER,
5622: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5623: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5624: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5625: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5626: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5619:
5620: PROCEDURE get_search_criteria (
5621: p_party_id IN NUMBER,
5622: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5623: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5624: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5625: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5626: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5627: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5620: PROCEDURE get_search_criteria (
5621: p_party_id IN NUMBER,
5622: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5623: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5624: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5625: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5626: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5627: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5628: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5621: p_party_id IN NUMBER,
5622: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5623: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5624: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5625: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5626: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5627: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5628: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5629: ) IS
5622: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5623: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5624: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5625: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5626: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5627: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5628: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5629: ) IS
5630: BEGIN
5623: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5624: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5625: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5626: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5627: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5628: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5629: ) IS
5630: BEGIN
5631:
5624: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5625: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5626: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5627: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5628: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5629: ) IS
5630: BEGIN
5631:
5632: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5664: ************************************************/
5665:
5666: PROCEDURE get_party_rec (
5667: p_party_id IN NUMBER,
5668: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type
5669: ) IS
5670: l_party_type VARCHAR2(255);
5671: BEGIN
5672:
5729: the party site search record structure
5730: ************************************************/
5731:
5732: PROCEDURE get_party_site_rec (
5733: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5734: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5735: ) IS
5736: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5737: SELECT party_site_id
5730: ************************************************/
5731:
5732: PROCEDURE get_party_site_rec (
5733: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5734: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5735: ) IS
5736: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5737: SELECT party_site_id
5738: ,translate(HZ_LOCATIONS.CITY, '%',' ')
5789: the contact search record structure
5790: ************************************************/
5791:
5792: PROCEDURE get_contact_rec (
5793: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5794: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5795: ) IS
5796: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5797: SELECT org_contact_id
5790: ************************************************/
5791:
5792: PROCEDURE get_contact_rec (
5793: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5794: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5795: ) IS
5796: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5797: SELECT org_contact_id
5798: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
5845: the contact point search record structure
5846: ************************************************/
5847:
5848: PROCEDURE get_contact_point_rec (
5849: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5850: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5851: ) IS
5852:
5853: -- Cursor to fetch primary contact points for party
5846: ************************************************/
5847:
5848: PROCEDURE get_contact_point_rec (
5849: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5850: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5851: ) IS
5852:
5853: -- Cursor to fetch primary contact points for party
5854: CURSOR c_cpts(cp_contact_point_id NUMBER) IS
5900: ************************************************/
5901:
5902: PROCEDURE MAP_PARTY_REC(
5903: p_search_ctx IN BOOLEAN,
5904: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5905: x_entered_max_score OUT NUMBER,
5906: x_stage_rec IN OUT NOCOPY HZ_PARTY_STAGE.party_stage_rec_type
5907: ) IS
5908: tmp VARCHAR2(4000);
6054: ************************************************/
6055:
6056: PROCEDURE MAP_PARTY_SITE_REC(
6057: p_search_ctx IN BOOLEAN,
6058: p_search_list IN HZ_PARTY_SEARCH.party_site_list,
6059: x_entered_max_score OUT NUMBER,
6060: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.party_site_stage_list
6061: ) IS
6062: l_current_max_score NUMBER;
6181: ************************************************/
6182:
6183: PROCEDURE MAP_CONTACT_REC(
6184: p_search_ctx IN BOOLEAN,
6185: p_search_list IN HZ_PARTY_SEARCH.contact_list,
6186: x_entered_max_score OUT NUMBER,
6187: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_stage_list
6188: ) IS
6189: l_current_max_score NUMBER;
6223: ************************************************/
6224:
6225: PROCEDURE MAP_CONTACT_POINT_REC(
6226: p_search_ctx IN BOOLEAN,
6227: p_search_list IN HZ_PARTY_SEARCH.contact_point_list,
6228: x_entered_max_score OUT NUMBER,
6229: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_pt_stage_list
6230: ) IS
6231: l_current_max_score NUMBER;
6265: 2. Contact Point Type is not null for each condition
6266: ************************************************/
6267:
6268: FUNCTION check_prim_cond(
6269: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6270: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6271: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6272: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6273: RETURN BOOLEAN IS
6266: ************************************************/
6267:
6268: FUNCTION check_prim_cond(
6269: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6270: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6271: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6272: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6273: RETURN BOOLEAN IS
6274: BEGIN
6267:
6268: FUNCTION check_prim_cond(
6269: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6270: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6271: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6272: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6273: RETURN BOOLEAN IS
6274: BEGIN
6275: IF p_party_search_rec.PARTY_NAME IS NOT NULL THEN
6268: FUNCTION check_prim_cond(
6269: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6270: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6271: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6272: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6273: RETURN BOOLEAN IS
6274: BEGIN
6275: IF p_party_search_rec.PARTY_NAME IS NOT NULL THEN
6276: RETURN TRUE;
6321: has party site criteria.
6322: ************************************************/
6323:
6324: PROCEDURE check_party_site_cond(
6325: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6326: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6327: x_secondary OUT BOOLEAN,
6328: x_primary OUT BOOLEAN
6329: ) IS
6322: ************************************************/
6323:
6324: PROCEDURE check_party_site_cond(
6325: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6326: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6327: x_secondary OUT BOOLEAN,
6328: x_primary OUT BOOLEAN
6329: ) IS
6330: BEGIN
6371: has contact criteria.
6372: ************************************************/
6373:
6374: PROCEDURE check_contact_cond(
6375: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6376: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6377: x_secondary OUT BOOLEAN,
6378: x_primary OUT BOOLEAN
6379: ) IS
6372: ************************************************/
6373:
6374: PROCEDURE check_contact_cond(
6375: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6376: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6377: x_secondary OUT BOOLEAN,
6378: x_primary OUT BOOLEAN
6379: ) IS
6380: BEGIN
6409: has valid contact point criteria.
6410: ************************************************/
6411:
6412: PROCEDURE check_contact_point_cond(
6413: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6414: x_secondary OUT BOOLEAN,
6415: x_primary OUT BOOLEAN
6416: ) IS
6417: BEGIN