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;
471: PROCEDURE INSERT_PARTY_SCORE (
472: p_party_id IN NUMBER
473: ,p_record_id IN NUMBER
474: ,p_search_ctx_id IN NUMBER
475: ,p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
476: ,p_stage_rec IN HZ_PARTY_STAGE.party_stage_rec_type
477: ,p_table_TX34 VARCHAR2
478: ,p_table_TX36 VARCHAR2
479: ,p_table_TX40 VARCHAR2
605: PROCEDURE INSERT_PARTY_SITES_SCORE (
606: p_party_id IN NUMBER
607: ,p_record_id IN NUMBER
608: ,p_search_ctx_id IN NUMBER
609: ,p_search_rec IN HZ_PARTY_SEARCH.party_site_search_rec_type
610: ,p_stage_rec IN HZ_PARTY_STAGE.party_site_stage_rec_type
611: ,p_table_TX3 VARCHAR2
612: ,p_table_TX4 VARCHAR2
613: ,p_table_TX9 VARCHAR2
739: PROCEDURE INSERT_CONTACTS_SCORE (
740: p_party_id IN NUMBER
741: ,p_record_id IN NUMBER
742: ,p_search_ctx_id IN NUMBER
743: ,p_search_rec IN HZ_PARTY_SEARCH.contact_search_rec_type
744: ,p_stage_rec IN HZ_PARTY_STAGE.contact_stage_rec_type
745: ,p_idx IN NUMBER) IS
746: l_current_score NUMBER:=0;
747: l_score NUMBER;
755: PROCEDURE INSERT_CONTACT_POINTS_SCORE (
756: p_party_id IN NUMBER
757: ,p_record_id IN NUMBER
758: ,p_search_ctx_id IN NUMBER
759: ,p_search_rec IN HZ_PARTY_SEARCH.contact_point_search_rec_type
760: ,p_stage_rec IN HZ_PARTY_STAGE.contact_pt_stage_rec_type
761: ,p_idx IN NUMBER) IS
762: l_current_score NUMBER:=0;
763: l_score NUMBER;
774: for the PARTY Entity
775: ************************************************/
776:
777: PROCEDURE POP_PARTY_COND_REC(
778: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
779: ) IS
780: BEGIN
781: NULL ;
782: EXCEPTION
794: for the PARTY_SITES Entity
795: ************************************************/
796:
797: PROCEDURE POP_PARTY_SITES_COND_REC(
798: p_search_list IN HZ_PARTY_SEARCH.party_site_list
799: ) IS
800: BEGIN
801: ---------POPULATE THE GLOBAL WORD CONDITION REC FOR PARTY_SITES-------------
802: ------ Populate global condition record only if search list is not empty -----------
819: for the CONTACTS Entity
820: ************************************************/
821:
822: PROCEDURE POP_CONTACTS_COND_REC(
823: p_search_list IN HZ_PARTY_SEARCH.contact_list
824: ) IS
825: BEGIN
826: NULL ;
827: EXCEPTION
839: for the CONTACT_POINTS Entity
840: ************************************************/
841:
842: PROCEDURE POP_CONTACT_POINTS_COND_REC(
843: p_search_list IN HZ_PARTY_SEARCH.contact_point_list
844: ) IS
845: BEGIN
846: NULL ;
847: EXCEPTION
853: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
854: END;
855:
856: PROCEDURE init_score_context (
857: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
858: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
859: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
860: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
861: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
854: END;
855:
856: PROCEDURE init_score_context (
857: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
858: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
859: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
860: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
861: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
862: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
855:
856: PROCEDURE init_score_context (
857: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
858: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
859: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
860: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
861: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
862: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
863: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
856: PROCEDURE init_score_context (
857: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
858: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
859: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
860: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
861: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
862: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
863: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
864: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
857: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
858: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
859: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
860: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
861: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
862: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
863: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
864: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
865: ) IS
858: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
859: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
860: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
861: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
862: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
863: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
864: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
865: ) IS
866: l_dummy NUMBER;
859: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
860: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
861: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
862: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
863: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
864: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
865: ) IS
866: l_dummy NUMBER;
867: BEGIN
860: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
861: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
862: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
863: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
864: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
865: ) IS
866: l_dummy NUMBER;
867: BEGIN
868: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
880: MAP_CONTACT_POINT_REC(FALSE,p_contact_point_list, l_dummy, g_contact_pt_stage_list);
881:
882: END;
883: FUNCTION init_search(
884: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
885: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
886: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
887: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
888: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
881:
882: END;
883: FUNCTION init_search(
884: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
885: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
886: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
887: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
888: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
889: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
882: END;
883: FUNCTION init_search(
884: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
885: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
886: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
887: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
888: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
889: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
890: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
883: FUNCTION init_search(
884: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
885: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
886: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
887: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
888: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
889: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
890: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
891: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
884: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
885: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
886: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
887: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
888: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
889: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
890: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
891: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
892: p_match_type IN VARCHAR2,
885: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
886: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
887: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
888: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
889: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
890: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
891: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
892: p_match_type IN VARCHAR2,
893: x_party_max_score OUT NUMBER,
886: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
887: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
888: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
889: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
890: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
891: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
892: p_match_type IN VARCHAR2,
893: x_party_max_score OUT NUMBER,
894: x_ps_max_score OUT NUMBER,
887: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
888: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
889: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
890: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
891: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
892: p_match_type IN VARCHAR2,
893: x_party_max_score OUT NUMBER,
894: x_ps_max_score OUT NUMBER,
895: x_contact_max_score OUT NUMBER,
899: l_ps_entered_max_score NUMBER:=0;
900: l_ct_entered_max_score NUMBER:=0;
901: l_cpt_entered_max_score NUMBER:=0;
902: vlist vlisttype;
903: maxscore HZ_PARTY_SEARCH.IDList;
904: l_name VARCHAR2(200);
905: l_idx NUMBER;
906: l_num NUMBER;
907: total NUMBER;
2302: p_party_id NUMBER,
2303: p_party_site_id NUMBER,
2304: p_org_contact_id NUMBER,
2305: p_contact_point_id NUMBER)
2306: RETURN HZ_PARTY_SEARCH.score_rec IS
2307: l_score_rec HZ_PARTY_SEARCH.score_rec;
2308: BEGIN
2309: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2310: 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);
2303: p_party_site_id NUMBER,
2304: p_org_contact_id NUMBER,
2305: p_contact_point_id NUMBER)
2306: RETURN HZ_PARTY_SEARCH.score_rec IS
2307: l_score_rec HZ_PARTY_SEARCH.score_rec;
2308: BEGIN
2309: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2310: 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);
2311: 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);
2335: **********************************************************/
2336:
2337: PROCEDURE find_parties (
2338: p_rule_id IN NUMBER,
2339: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2340: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2341: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2342: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2343: p_restrict_sql IN VARCHAR2,
2336:
2337: PROCEDURE find_parties (
2338: p_rule_id IN NUMBER,
2339: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2340: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2341: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2342: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2343: p_restrict_sql IN VARCHAR2,
2344: p_match_type IN VARCHAR2,
2337: PROCEDURE find_parties (
2338: p_rule_id IN NUMBER,
2339: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2340: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2341: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2342: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2343: p_restrict_sql IN VARCHAR2,
2344: p_match_type IN VARCHAR2,
2345: p_search_merged IN VARCHAR2,
2338: p_rule_id IN NUMBER,
2339: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2340: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2341: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2342: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2343: p_restrict_sql IN VARCHAR2,
2344: p_match_type IN VARCHAR2,
2345: p_search_merged IN VARCHAR2,
2346: p_dup_party_id IN NUMBER,
2382: l_TX40 VARCHAR2(2000);
2383: l_TX34 VARCHAR2(2000);
2384: l_TX36 VARCHAR2(2000);
2385: l_TX14 VARCHAR2(2000);
2386: H_SCORES HZ_PARTY_SEARCH.score_list;
2387: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2388: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2389:
2390: l_score NUMBER;
2383: l_TX34 VARCHAR2(2000);
2384: l_TX36 VARCHAR2(2000);
2385: l_TX14 VARCHAR2(2000);
2386: H_SCORES HZ_PARTY_SEARCH.score_list;
2387: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2388: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2389:
2390: l_score NUMBER;
2391: l_idx NUMBER;
2384: l_TX36 VARCHAR2(2000);
2385: l_TX14 VARCHAR2(2000);
2386: H_SCORES HZ_PARTY_SEARCH.score_list;
2387: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2388: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2389:
2390: l_score NUMBER;
2391: l_idx NUMBER;
2392: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
2576: /** Private procedure to acquire and score at party site level ***/
2577: 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
2578: l_party_id_idx NUMBER:=1;
2579: l_ctx_id NUMBER;
2580: h_ps_id HZ_PARTY_SEARCH.IDList;
2581: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2582: h_ps_score HZ_PARTY_SEARCH.IDList;
2583: detcnt NUMBER := 1;
2584: BEGIN
2577: 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
2578: l_party_id_idx NUMBER:=1;
2579: l_ctx_id NUMBER;
2580: h_ps_id HZ_PARTY_SEARCH.IDList;
2581: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2582: h_ps_score HZ_PARTY_SEARCH.IDList;
2583: detcnt NUMBER := 1;
2584: BEGIN
2585: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2578: l_party_id_idx NUMBER:=1;
2579: l_ctx_id NUMBER;
2580: h_ps_id HZ_PARTY_SEARCH.IDList;
2581: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2582: h_ps_score HZ_PARTY_SEARCH.IDList;
2583: detcnt NUMBER := 1;
2584: BEGIN
2585: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2586: 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);
2698: /** Private procedure to acquire and score at contact point level ***/
2699: 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
2700: l_party_id_idx NUMBER:=1;
2701: l_ctx_id NUMBER;
2702: h_ct_id HZ_PARTY_SEARCH.IDList;
2703: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2704: h_ct_score HZ_PARTY_SEARCH.IDList;
2705: detcnt NUMBER := 1;
2706: BEGIN
2699: 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
2700: l_party_id_idx NUMBER:=1;
2701: l_ctx_id NUMBER;
2702: h_ct_id HZ_PARTY_SEARCH.IDList;
2703: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2704: h_ct_score HZ_PARTY_SEARCH.IDList;
2705: detcnt NUMBER := 1;
2706: BEGIN
2707: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2700: l_party_id_idx NUMBER:=1;
2701: l_ctx_id NUMBER;
2702: h_ct_id HZ_PARTY_SEARCH.IDList;
2703: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2704: h_ct_score HZ_PARTY_SEARCH.IDList;
2705: detcnt NUMBER := 1;
2706: BEGIN
2707: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2708: 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);
2807: /** Private procedure to acquire and score at contact point level ***/
2808: 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
2809: l_party_id_idx NUMBER:=1;
2810: l_ctx_id NUMBER;
2811: h_cpt_id HZ_PARTY_SEARCH.IDList;
2812: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2813: h_cpt_score HZ_PARTY_SEARCH.IDList;
2814: detcnt NUMBER := 1;
2815: l_cpt_flag VARCHAR2(1) := 'N';
2808: 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
2809: l_party_id_idx NUMBER:=1;
2810: l_ctx_id NUMBER;
2811: h_cpt_id HZ_PARTY_SEARCH.IDList;
2812: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2813: h_cpt_score HZ_PARTY_SEARCH.IDList;
2814: detcnt NUMBER := 1;
2815: l_cpt_flag VARCHAR2(1) := 'N';
2816: is_a_match VARCHAR2(1) := 'Y';
2809: l_party_id_idx NUMBER:=1;
2810: l_ctx_id NUMBER;
2811: h_cpt_id HZ_PARTY_SEARCH.IDList;
2812: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2813: h_cpt_score HZ_PARTY_SEARCH.IDList;
2814: detcnt NUMBER := 1;
2815: l_cpt_flag VARCHAR2(1) := 'N';
2816: is_a_match VARCHAR2(1) := 'Y';
2817: BEGIN
3296: END find_parties;
3297:
3298: PROCEDURE find_persons (
3299: p_rule_id IN NUMBER,
3300: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3301: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3302: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3303: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3304: p_restrict_sql IN VARCHAR2,
3297:
3298: PROCEDURE find_persons (
3299: p_rule_id IN NUMBER,
3300: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3301: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3302: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3303: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3304: p_restrict_sql IN VARCHAR2,
3305: p_match_type IN VARCHAR2,
3298: PROCEDURE find_persons (
3299: p_rule_id IN NUMBER,
3300: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3301: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3302: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3303: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3304: p_restrict_sql IN VARCHAR2,
3305: p_match_type IN VARCHAR2,
3306: p_search_merged IN VARCHAR2,
3299: p_rule_id IN NUMBER,
3300: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3301: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3302: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3303: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3304: p_restrict_sql IN VARCHAR2,
3305: p_match_type IN VARCHAR2,
3306: p_search_merged IN VARCHAR2,
3307: p_dup_party_id IN NUMBER,
3343: l_TX40 VARCHAR2(2000);
3344: l_TX34 VARCHAR2(2000);
3345: l_TX36 VARCHAR2(2000);
3346: l_TX14 VARCHAR2(2000);
3347: H_SCORES HZ_PARTY_SEARCH.score_list;
3348: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3349: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3350:
3351: l_score NUMBER;
3344: l_TX34 VARCHAR2(2000);
3345: l_TX36 VARCHAR2(2000);
3346: l_TX14 VARCHAR2(2000);
3347: H_SCORES HZ_PARTY_SEARCH.score_list;
3348: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3349: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3350:
3351: l_score NUMBER;
3352: l_idx NUMBER;
3345: l_TX36 VARCHAR2(2000);
3346: l_TX14 VARCHAR2(2000);
3347: H_SCORES HZ_PARTY_SEARCH.score_list;
3348: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3349: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3350:
3351: l_score NUMBER;
3352: l_idx NUMBER;
3353: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
3585: /** Private procedure to acquire and score at party site level ***/
3586: 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
3587: l_party_id_idx NUMBER:=1;
3588: l_ctx_id NUMBER;
3589: h_ps_id HZ_PARTY_SEARCH.IDList;
3590: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3591: h_ps_score HZ_PARTY_SEARCH.IDList;
3592: detcnt NUMBER := 1;
3593: l_person_id NUMBER;
3586: 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
3587: l_party_id_idx NUMBER:=1;
3588: l_ctx_id NUMBER;
3589: h_ps_id HZ_PARTY_SEARCH.IDList;
3590: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3591: h_ps_score HZ_PARTY_SEARCH.IDList;
3592: detcnt NUMBER := 1;
3593: l_person_id NUMBER;
3594: BEGIN
3587: l_party_id_idx NUMBER:=1;
3588: l_ctx_id NUMBER;
3589: h_ps_id HZ_PARTY_SEARCH.IDList;
3590: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3591: h_ps_score HZ_PARTY_SEARCH.IDList;
3592: detcnt NUMBER := 1;
3593: l_person_id NUMBER;
3594: BEGIN
3595: SAVEPOINT eval_start;
3684: /** Private procedure to acquire and score at party site level ***/
3685: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3686: l_party_id_idx NUMBER:=1;
3687: l_ctx_id NUMBER;
3688: h_ct_id HZ_PARTY_SEARCH.IDList;
3689: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3690: h_ct_score HZ_PARTY_SEARCH.IDList;
3691: detcnt NUMBER := 1;
3692: l_person_id NUMBER;
3685: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3686: l_party_id_idx NUMBER:=1;
3687: l_ctx_id NUMBER;
3688: h_ct_id HZ_PARTY_SEARCH.IDList;
3689: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3690: h_ct_score HZ_PARTY_SEARCH.IDList;
3691: detcnt NUMBER := 1;
3692: l_person_id NUMBER;
3693: BEGIN
3686: l_party_id_idx NUMBER:=1;
3687: l_ctx_id NUMBER;
3688: h_ct_id HZ_PARTY_SEARCH.IDList;
3689: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3690: h_ct_score HZ_PARTY_SEARCH.IDList;
3691: detcnt NUMBER := 1;
3692: l_person_id NUMBER;
3693: BEGIN
3694: SAVEPOINT eval_start;
3742: /** Private procedure to acquire and score at contact point level ***/
3743: 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
3744: l_party_id_idx NUMBER:=1;
3745: l_ctx_id NUMBER;
3746: h_cpt_id HZ_PARTY_SEARCH.IDList;
3747: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3748: h_cpt_score HZ_PARTY_SEARCH.IDList;
3749: detcnt NUMBER := 1;
3750: l_person_id NUMBER;
3743: 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
3744: l_party_id_idx NUMBER:=1;
3745: l_ctx_id NUMBER;
3746: h_cpt_id HZ_PARTY_SEARCH.IDList;
3747: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3748: h_cpt_score HZ_PARTY_SEARCH.IDList;
3749: detcnt NUMBER := 1;
3750: l_person_id NUMBER;
3751: is_a_match VARCHAR2(1) := 'Y';
3744: l_party_id_idx NUMBER:=1;
3745: l_ctx_id NUMBER;
3746: h_cpt_id HZ_PARTY_SEARCH.IDList;
3747: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3748: h_cpt_score HZ_PARTY_SEARCH.IDList;
3749: detcnt NUMBER := 1;
3750: l_person_id NUMBER;
3751: is_a_match VARCHAR2(1) := 'Y';
3752: l_cpt_flag VARCHAR2(1) := 'N';
4155: END find_persons;
4156:
4157: PROCEDURE find_persons (
4158: p_rule_id IN NUMBER,
4159: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4160: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4161: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4162: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4163: p_restrict_sql IN VARCHAR2,
4156:
4157: PROCEDURE find_persons (
4158: p_rule_id IN NUMBER,
4159: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4160: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4161: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4162: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4163: p_restrict_sql IN VARCHAR2,
4164: p_match_type IN VARCHAR2,
4157: PROCEDURE find_persons (
4158: p_rule_id IN NUMBER,
4159: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4160: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4161: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4162: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4163: p_restrict_sql IN VARCHAR2,
4164: p_match_type IN VARCHAR2,
4165: p_search_merged IN VARCHAR2,
4158: p_rule_id IN NUMBER,
4159: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4160: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4161: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4162: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4163: p_restrict_sql IN VARCHAR2,
4164: p_match_type IN VARCHAR2,
4165: p_search_merged IN VARCHAR2,
4166: p_ins_details IN VARCHAR2,
4172: 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);
4173: END;
4174: PROCEDURE find_party_details (
4175: p_rule_id IN NUMBER,
4176: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4177: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4178: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4179: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4180: p_restrict_sql IN VARCHAR2,
4173: END;
4174: PROCEDURE find_party_details (
4175: p_rule_id IN NUMBER,
4176: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4177: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4178: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4179: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4180: p_restrict_sql IN VARCHAR2,
4181: p_match_type IN VARCHAR2,
4174: PROCEDURE find_party_details (
4175: p_rule_id IN NUMBER,
4176: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4177: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4178: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4179: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4180: p_restrict_sql IN VARCHAR2,
4181: p_match_type IN VARCHAR2,
4182: p_search_merged IN VARCHAR2,
4175: p_rule_id IN NUMBER,
4176: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4177: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4178: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4179: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4180: p_restrict_sql IN VARCHAR2,
4181: p_match_type IN VARCHAR2,
4182: p_search_merged IN VARCHAR2,
4183: x_search_ctx_id OUT NUMBER,
4232: x_dup_set_id OUT NUMBER,
4233: x_search_ctx_id OUT NUMBER,
4234: x_num_matches OUT NUMBER
4235: ) IS
4236: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4237: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4238: l_contact_list HZ_PARTY_SEARCH.contact_list;
4239: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4240: l_match_idx NUMBER;
4233: x_search_ctx_id OUT NUMBER,
4234: x_num_matches OUT NUMBER
4235: ) IS
4236: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4237: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4238: l_contact_list HZ_PARTY_SEARCH.contact_list;
4239: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4240: l_match_idx NUMBER;
4241:
4234: x_num_matches OUT NUMBER
4235: ) IS
4236: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4237: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4238: l_contact_list HZ_PARTY_SEARCH.contact_list;
4239: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4240: l_match_idx NUMBER;
4241:
4242: --Fix for bug 4417124
4235: ) IS
4236: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4237: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4238: l_contact_list HZ_PARTY_SEARCH.contact_list;
4239: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4240: l_match_idx NUMBER;
4241:
4242: --Fix for bug 4417124
4243: l_use_contact_addr_info BOOLEAN := TRUE;
4348: x_search_ctx_id OUT NUMBER,
4349: x_num_matches OUT NUMBER
4350: ) IS
4351:
4352: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4353: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4354: l_contact_list HZ_PARTY_SEARCH.contact_list;
4355: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4356: contact_point_ids HZ_PARTY_SEARCH.IDList;
4349: x_num_matches OUT NUMBER
4350: ) IS
4351:
4352: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4353: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4354: l_contact_list HZ_PARTY_SEARCH.contact_list;
4355: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4356: contact_point_ids HZ_PARTY_SEARCH.IDList;
4357: p_party_site_list HZ_PARTY_SEARCH.IDList;
4350: ) IS
4351:
4352: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4353: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4354: l_contact_list HZ_PARTY_SEARCH.contact_list;
4355: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4356: contact_point_ids HZ_PARTY_SEARCH.IDList;
4357: p_party_site_list HZ_PARTY_SEARCH.IDList;
4358: p_contact_ids HZ_PARTY_SEARCH.IDList;
4351:
4352: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4353: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4354: l_contact_list HZ_PARTY_SEARCH.contact_list;
4355: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4356: contact_point_ids HZ_PARTY_SEARCH.IDList;
4357: p_party_site_list HZ_PARTY_SEARCH.IDList;
4358: p_contact_ids HZ_PARTY_SEARCH.IDList;
4359: l_match_idx NUMBER;
4352: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4353: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4354: l_contact_list HZ_PARTY_SEARCH.contact_list;
4355: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4356: contact_point_ids HZ_PARTY_SEARCH.IDList;
4357: p_party_site_list HZ_PARTY_SEARCH.IDList;
4358: p_contact_ids HZ_PARTY_SEARCH.IDList;
4359: l_match_idx NUMBER;
4360: cursor get_cpts_for_party_sites is select contact_point_id
4353: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4354: l_contact_list HZ_PARTY_SEARCH.contact_list;
4355: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4356: contact_point_ids HZ_PARTY_SEARCH.IDList;
4357: p_party_site_list HZ_PARTY_SEARCH.IDList;
4358: p_contact_ids HZ_PARTY_SEARCH.IDList;
4359: l_match_idx NUMBER;
4360: cursor get_cpts_for_party_sites is select contact_point_id
4361: from hz_contact_points
4354: l_contact_list HZ_PARTY_SEARCH.contact_list;
4355: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4356: contact_point_ids HZ_PARTY_SEARCH.IDList;
4357: p_party_site_list HZ_PARTY_SEARCH.IDList;
4358: p_contact_ids HZ_PARTY_SEARCH.IDList;
4359: l_match_idx NUMBER;
4360: cursor get_cpts_for_party_sites is select contact_point_id
4361: from hz_contact_points
4362: where owner_table_name = 'HZ_PARTY_SITES'
4383:
4384: get_search_criteria (
4385: null,
4386: p_party_site_list,
4387: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4388: contact_point_ids,
4389: l_party_search_rec,
4390: l_party_site_list,
4391: l_contact_list,
4433: x_search_ctx_id OUT NUMBER,
4434: x_num_matches OUT NUMBER
4435: ) IS
4436:
4437: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4438: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4439: l_contact_list HZ_PARTY_SEARCH.contact_list;
4440: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4441: contact_point_ids HZ_PARTY_SEARCH.IDList;
4434: x_num_matches OUT NUMBER
4435: ) IS
4436:
4437: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4438: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4439: l_contact_list HZ_PARTY_SEARCH.contact_list;
4440: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4441: contact_point_ids HZ_PARTY_SEARCH.IDList;
4442: p_party_site_list HZ_PARTY_SEARCH.IDList;
4435: ) IS
4436:
4437: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4438: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4439: l_contact_list HZ_PARTY_SEARCH.contact_list;
4440: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4441: contact_point_ids HZ_PARTY_SEARCH.IDList;
4442: p_party_site_list HZ_PARTY_SEARCH.IDList;
4443: p_contact_ids HZ_PARTY_SEARCH.IDList;
4436:
4437: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4438: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4439: l_contact_list HZ_PARTY_SEARCH.contact_list;
4440: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4441: contact_point_ids HZ_PARTY_SEARCH.IDList;
4442: p_party_site_list HZ_PARTY_SEARCH.IDList;
4443: p_contact_ids HZ_PARTY_SEARCH.IDList;
4444: l_match_idx NUMBER;
4437: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4438: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4439: l_contact_list HZ_PARTY_SEARCH.contact_list;
4440: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4441: contact_point_ids HZ_PARTY_SEARCH.IDList;
4442: p_party_site_list HZ_PARTY_SEARCH.IDList;
4443: p_contact_ids HZ_PARTY_SEARCH.IDList;
4444: l_match_idx NUMBER;
4445: cursor get_cpt_for_contact_id is select contact_point_id
4438: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4439: l_contact_list HZ_PARTY_SEARCH.contact_list;
4440: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4441: contact_point_ids HZ_PARTY_SEARCH.IDList;
4442: p_party_site_list HZ_PARTY_SEARCH.IDList;
4443: p_contact_ids HZ_PARTY_SEARCH.IDList;
4444: l_match_idx NUMBER;
4445: cursor get_cpt_for_contact_id is select contact_point_id
4446: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4439: l_contact_list HZ_PARTY_SEARCH.contact_list;
4440: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4441: contact_point_ids HZ_PARTY_SEARCH.IDList;
4442: p_party_site_list HZ_PARTY_SEARCH.IDList;
4443: p_contact_ids HZ_PARTY_SEARCH.IDList;
4444: l_match_idx NUMBER;
4445: cursor get_cpt_for_contact_id is select contact_point_id
4446: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4447: where a.party_relationship_id = b.relationship_id
4470: CLOSE get_cpt_for_contact_id;
4471:
4472: get_search_criteria (
4473: null,
4474: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4475: p_contact_ids,
4476: contact_point_ids,
4477: l_party_search_rec,
4478: l_party_site_list,
4521: p_match_type IN VARCHAR2,
4522: x_search_ctx_id OUT NUMBER,
4523: x_num_matches OUT NUMBER
4524: ) IS
4525: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4526: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4527: l_contact_list HZ_PARTY_SEARCH.contact_list;
4528: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4529: contact_point_ids HZ_PARTY_SEARCH.IDList;
4522: x_search_ctx_id OUT NUMBER,
4523: x_num_matches OUT NUMBER
4524: ) IS
4525: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4526: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4527: l_contact_list HZ_PARTY_SEARCH.contact_list;
4528: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4529: contact_point_ids HZ_PARTY_SEARCH.IDList;
4530: p_party_site_list HZ_PARTY_SEARCH.IDList;
4523: x_num_matches OUT NUMBER
4524: ) IS
4525: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4526: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4527: l_contact_list HZ_PARTY_SEARCH.contact_list;
4528: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4529: contact_point_ids HZ_PARTY_SEARCH.IDList;
4530: p_party_site_list HZ_PARTY_SEARCH.IDList;
4531: p_contact_ids HZ_PARTY_SEARCH.IDList;
4524: ) IS
4525: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4526: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4527: l_contact_list HZ_PARTY_SEARCH.contact_list;
4528: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4529: contact_point_ids HZ_PARTY_SEARCH.IDList;
4530: p_party_site_list HZ_PARTY_SEARCH.IDList;
4531: p_contact_ids HZ_PARTY_SEARCH.IDList;
4532: l_match_idx NUMBER;
4525: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4526: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4527: l_contact_list HZ_PARTY_SEARCH.contact_list;
4528: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4529: contact_point_ids HZ_PARTY_SEARCH.IDList;
4530: p_party_site_list HZ_PARTY_SEARCH.IDList;
4531: p_contact_ids HZ_PARTY_SEARCH.IDList;
4532: l_match_idx NUMBER;
4533:
4526: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4527: l_contact_list HZ_PARTY_SEARCH.contact_list;
4528: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4529: contact_point_ids HZ_PARTY_SEARCH.IDList;
4530: p_party_site_list HZ_PARTY_SEARCH.IDList;
4531: p_contact_ids HZ_PARTY_SEARCH.IDList;
4532: l_match_idx NUMBER;
4533:
4534: BEGIN
4527: l_contact_list HZ_PARTY_SEARCH.contact_list;
4528: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4529: contact_point_ids HZ_PARTY_SEARCH.IDList;
4530: p_party_site_list HZ_PARTY_SEARCH.IDList;
4531: p_contact_ids HZ_PARTY_SEARCH.IDList;
4532: l_match_idx NUMBER;
4533:
4534: BEGIN
4535: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4544: END IF;
4545: contact_point_ids(1) := p_contact_point_id;
4546: get_search_criteria (
4547: null,
4548: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4549: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4550: contact_point_ids,
4551: l_party_search_rec,
4552: l_party_site_list,
4545: contact_point_ids(1) := p_contact_point_id;
4546: get_search_criteria (
4547: null,
4548: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4549: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4550: contact_point_ids,
4551: l_party_search_rec,
4552: l_party_site_list,
4553: l_contact_list,
4698: ) IS
4699: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4700: l_match_idx NUMBER;
4701: AttrVals AttrList;
4702: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4703: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4704: l_contact_list HZ_PARTY_SEARCH.contact_list;
4705: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4706: l_dup_set_id NUMBER;
4699: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4700: l_match_idx NUMBER;
4701: AttrVals AttrList;
4702: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4703: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4704: l_contact_list HZ_PARTY_SEARCH.contact_list;
4705: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4706: l_dup_set_id NUMBER;
4707: l_idx NUMBER;
4700: l_match_idx NUMBER;
4701: AttrVals AttrList;
4702: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4703: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4704: l_contact_list HZ_PARTY_SEARCH.contact_list;
4705: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4706: l_dup_set_id NUMBER;
4707: l_idx NUMBER;
4708: l_cpt_type VARCHAR2(255);
4701: AttrVals AttrList;
4702: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4703: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4704: l_contact_list HZ_PARTY_SEARCH.contact_list;
4705: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4706: l_dup_set_id NUMBER;
4707: l_idx NUMBER;
4708: l_cpt_type VARCHAR2(255);
4709: FIRST BOOLEAN := TRUE;
4877:
4878: PROCEDURE get_matching_party_sites (
4879: p_rule_id IN NUMBER,
4880: p_party_id IN NUMBER,
4881: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4882: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4883: p_restrict_sql IN VARCHAR2,
4884: p_match_type IN VARCHAR2,
4885: p_dup_party_site_id IN NUMBER,
4878: PROCEDURE get_matching_party_sites (
4879: p_rule_id IN NUMBER,
4880: p_party_id IN NUMBER,
4881: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4882: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4883: p_restrict_sql IN VARCHAR2,
4884: p_match_type IN VARCHAR2,
4885: p_dup_party_site_id IN NUMBER,
4886: x_search_ctx_id OUT NUMBER,
4909: l_TX3 VARCHAR2(2000);
4910: l_TX4 VARCHAR2(2000);
4911: l_TX10 VARCHAR2(2000);
4912: l_TX14 VARCHAR2(2000);
4913: H_SCORES HZ_PARTY_SEARCH.score_list;
4914:
4915: l_score NUMBER;
4916: l_idx NUMBER;
4917: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
4940: defps NUMBER :=0;
4941: defct NUMBER :=0;
4942: defcpt NUMBER :=0;
4943: l_index NUMBER;
4944: l_match_ps_list HZ_PARTY_SEARCH.IDList;
4945: l_cnt NUMBER:=1;
4946:
4947:
4948: BEGIN
4964: l_match_str := ' AND ';
4965: ELSIF p_match_type = 'OR' THEN
4966: l_match_str := ' OR ';
4967: END IF;
4968: 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);
4969: g_score_until_thresh := false;
4970: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4971:
4972: -- ************************************************************
4972: -- ************************************************************
4973: -- STEP 2. Setup of intermedia query strings for Acquisition query
4974: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4975: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4976: 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);
4977:
4978: -- Setup Search Context ID
4979: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4980: x_search_ctx_id := l_search_ctx_id;
5115:
5116: PROCEDURE get_matching_contacts (
5117: p_rule_id IN NUMBER,
5118: p_party_id IN NUMBER,
5119: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
5120: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5121: p_restrict_sql IN VARCHAR2,
5122: p_match_type IN VARCHAR2,
5123: p_dup_contact_id IN NUMBER,
5116: PROCEDURE get_matching_contacts (
5117: p_rule_id IN NUMBER,
5118: p_party_id IN NUMBER,
5119: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
5120: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5121: p_restrict_sql IN VARCHAR2,
5122: p_match_type IN VARCHAR2,
5123: p_dup_contact_id IN NUMBER,
5124: x_search_ctx_id OUT NUMBER,
5141: l_entered_max_score NUMBER;
5142: FIRST BOOLEAN;
5143: l_search_ctx_id NUMBER; -- Generated Search Context ID
5144:
5145: H_SCORES HZ_PARTY_SEARCH.score_list;
5146:
5147: l_score NUMBER;
5148: l_idx NUMBER;
5149: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
5169: l_party_max_score NUMBER;
5170: l_ps_max_score NUMBER;
5171: l_contact_max_score NUMBER;
5172: l_cpt_max_score NUMBER;
5173: l_match_contact_list HZ_PARTY_SEARCH.IDList;
5174: l_cnt NUMBER:=1;
5175:
5176:
5177: BEGIN
5193: l_match_str := ' AND ';
5194: ELSIF p_match_type = 'OR' THEN
5195: l_match_str := ' OR ';
5196: END IF;
5197: 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);
5198: g_score_until_thresh := false;
5199: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5200:
5201: -- ************************************************************
5201: -- ************************************************************
5202: -- STEP 2. Setup of intermedia query strings for Acquisition query
5203: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5204: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5205: 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);
5206:
5207: -- Setup Search Context ID
5208: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5209: x_search_ctx_id := l_search_ctx_id;
5340:
5341: PROCEDURE get_matching_contact_points (
5342: p_rule_id IN NUMBER,
5343: p_party_id IN NUMBER,
5344: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5345: p_restrict_sql IN VARCHAR2,
5346: p_match_type IN VARCHAR2,
5347: p_dup_contact_point_id IN NUMBER,
5348: x_search_ctx_id OUT NUMBER,
5369: l_cpt_ps_id NUMBER;
5370: l_cpt_contact_id NUMBER;
5371: l_contact_pt_id NUMBER;
5372: l_cpt_type VARCHAR2(100);
5373: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5374: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5375: H_SCORE HZ_PARTY_SEARCH.IDList;
5376:
5377: cnt NUMBER :=0;
5370: l_cpt_contact_id NUMBER;
5371: l_contact_pt_id NUMBER;
5372: l_cpt_type VARCHAR2(100);
5373: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5374: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5375: H_SCORE HZ_PARTY_SEARCH.IDList;
5376:
5377: cnt NUMBER :=0;
5378: l_party_max_score NUMBER;
5371: l_contact_pt_id NUMBER;
5372: l_cpt_type VARCHAR2(100);
5373: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5374: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5375: H_SCORE HZ_PARTY_SEARCH.IDList;
5376:
5377: cnt NUMBER :=0;
5378: l_party_max_score NUMBER;
5379: l_ps_max_score NUMBER;
5400: l_match_str := ' AND ';
5401: ELSIF p_match_type = 'OR' THEN
5402: l_match_str := ' OR ';
5403: END IF;
5404: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5405: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5406: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5407: g_score_until_thresh := false;
5408: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5401: ELSIF p_match_type = 'OR' THEN
5402: l_match_str := ' OR ';
5403: END IF;
5404: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5405: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5406: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5407: g_score_until_thresh := false;
5408: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5409:
5409:
5410: -- ************************************************************
5411: -- STEP 2. Setup of intermedia query strings for Acquisition query
5412: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5413: 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);
5414:
5415: -- Setup Search Context ID
5416: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5417: x_search_ctx_id := l_search_ctx_id;
5484:
5485: PROCEDURE get_score_details (
5486: p_rule_id IN NUMBER,
5487: p_party_id IN NUMBER,
5488: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5489: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5490: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5491: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5492: x_search_ctx_id IN OUT NUMBER
5485: PROCEDURE get_score_details (
5486: p_rule_id IN NUMBER,
5487: p_party_id IN NUMBER,
5488: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5489: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5490: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5491: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5492: x_search_ctx_id IN OUT NUMBER
5493: ) IS
5486: p_rule_id IN NUMBER,
5487: p_party_id IN NUMBER,
5488: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5489: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5490: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5491: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5492: x_search_ctx_id IN OUT NUMBER
5493: ) IS
5494:
5487: p_party_id IN NUMBER,
5488: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5489: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5490: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5491: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5492: x_search_ctx_id IN OUT NUMBER
5493: ) IS
5494:
5495: -- Strings to hold the generated Intermedia query strings
5530: l_TX36 VARCHAR2(2000);
5531: l_max_TX36 VARCHAR2(2000);
5532: l_TX14 VARCHAR2(2000);
5533: l_max_TX14 VARCHAR2(2000);
5534: H_SCORES HZ_PARTY_SEARCH.score_list;
5535:
5536: l_score NUMBER;
5537: l_match_idx NUMBER;
5538: l_idx NUMBER;
5702: ************************************************/
5703:
5704: PROCEDURE get_party_for_search (
5705: p_party_id IN NUMBER,
5706: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5707: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5708: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5709: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5710: ) IS
5703:
5704: PROCEDURE get_party_for_search (
5705: p_party_id IN NUMBER,
5706: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5707: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5708: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5709: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5710: ) IS
5711: l_party_id NUMBER;
5704: PROCEDURE get_party_for_search (
5705: p_party_id IN NUMBER,
5706: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5707: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5708: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5709: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5710: ) IS
5711: l_party_id NUMBER;
5712: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5705: p_party_id IN NUMBER,
5706: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5707: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5708: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5709: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5710: ) IS
5711: l_party_id NUMBER;
5712: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5713: l_contact_ids HZ_PARTY_SEARCH.IDList;
5708: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5709: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5710: ) IS
5711: l_party_id NUMBER;
5712: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5713: l_contact_ids HZ_PARTY_SEARCH.IDList;
5714: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5715: ps NUMBER :=1;
5716: cpt NUMBER :=1;
5709: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5710: ) IS
5711: l_party_id NUMBER;
5712: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5713: l_contact_ids HZ_PARTY_SEARCH.IDList;
5714: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5715: ps NUMBER :=1;
5716: cpt NUMBER :=1;
5717: ct NUMBER :=1;
5710: ) IS
5711: l_party_id NUMBER;
5712: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5713: l_contact_ids HZ_PARTY_SEARCH.IDList;
5714: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5715: ps NUMBER :=1;
5716: cpt NUMBER :=1;
5717: ct NUMBER :=1;
5718: l_use_contact_info varchar2(1);
5774: END get_party_for_search;
5775:
5776: PROCEDURE get_search_criteria (
5777: p_party_id IN NUMBER,
5778: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5779: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5780: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5781: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5782: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5775:
5776: PROCEDURE get_search_criteria (
5777: p_party_id IN NUMBER,
5778: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5779: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5780: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5781: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5782: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5783: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5776: PROCEDURE get_search_criteria (
5777: p_party_id IN NUMBER,
5778: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5779: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5780: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5781: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5782: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5783: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5784: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5777: p_party_id IN NUMBER,
5778: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5779: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5780: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5781: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5782: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5783: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5784: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5785: ) IS
5778: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5779: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5780: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5781: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5782: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5783: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5784: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5785: ) IS
5786: BEGIN
5779: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5780: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5781: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5782: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5783: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5784: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5785: ) IS
5786: BEGIN
5787:
5780: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5781: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5782: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5783: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5784: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5785: ) IS
5786: BEGIN
5787:
5788: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5820: ************************************************/
5821:
5822: PROCEDURE get_party_rec (
5823: p_party_id IN NUMBER,
5824: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type
5825: ) IS
5826: l_party_type VARCHAR2(255);
5827: BEGIN
5828:
5883: the party site search record structure
5884: ************************************************/
5885:
5886: PROCEDURE get_party_site_rec (
5887: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5888: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5889: ) IS
5890: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5891: SELECT party_site_id
5884: ************************************************/
5885:
5886: PROCEDURE get_party_site_rec (
5887: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5888: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5889: ) IS
5890: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5891: SELECT party_site_id
5892: ,translate(HZ_LOCATIONS.CITY, '%',' ')
5943: the contact search record structure
5944: ************************************************/
5945:
5946: PROCEDURE get_contact_rec (
5947: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5948: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5949: ) IS
5950: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5951: SELECT org_contact_id
5944: ************************************************/
5945:
5946: PROCEDURE get_contact_rec (
5947: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5948: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5949: ) IS
5950: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5951: SELECT org_contact_id
5952: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
5999: the contact point search record structure
6000: ************************************************/
6001:
6002: PROCEDURE get_contact_point_rec (
6003: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
6004: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6005: ) IS
6006:
6007: -- Cursor to fetch primary contact points for party
6000: ************************************************/
6001:
6002: PROCEDURE get_contact_point_rec (
6003: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
6004: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6005: ) IS
6006:
6007: -- Cursor to fetch primary contact points for party
6008: CURSOR c_cpts(cp_contact_point_id NUMBER) IS
6054: ************************************************/
6055:
6056: PROCEDURE MAP_PARTY_REC(
6057: p_search_ctx IN BOOLEAN,
6058: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6059: x_entered_max_score OUT NUMBER,
6060: x_stage_rec IN OUT NOCOPY HZ_PARTY_STAGE.party_stage_rec_type
6061: ) IS
6062: tmp VARCHAR2(4000);
6208: ************************************************/
6209:
6210: PROCEDURE MAP_PARTY_SITE_REC(
6211: p_search_ctx IN BOOLEAN,
6212: p_search_list IN HZ_PARTY_SEARCH.party_site_list,
6213: x_entered_max_score OUT NUMBER,
6214: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.party_site_stage_list
6215: ) IS
6216: l_current_max_score NUMBER;
6371: ************************************************/
6372:
6373: PROCEDURE MAP_CONTACT_REC(
6374: p_search_ctx IN BOOLEAN,
6375: p_search_list IN HZ_PARTY_SEARCH.contact_list,
6376: x_entered_max_score OUT NUMBER,
6377: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_stage_list
6378: ) IS
6379: l_current_max_score NUMBER;
6413: ************************************************/
6414:
6415: PROCEDURE MAP_CONTACT_POINT_REC(
6416: p_search_ctx IN BOOLEAN,
6417: p_search_list IN HZ_PARTY_SEARCH.contact_point_list,
6418: x_entered_max_score OUT NUMBER,
6419: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_pt_stage_list
6420: ) IS
6421: l_current_max_score NUMBER;
6479: 2. Contact Point Type is not null for each condition
6480: ************************************************/
6481:
6482: FUNCTION check_prim_cond(
6483: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6484: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6485: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6486: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6487: RETURN BOOLEAN IS
6480: ************************************************/
6481:
6482: FUNCTION check_prim_cond(
6483: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6484: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6485: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6486: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6487: RETURN BOOLEAN IS
6488: BEGIN
6481:
6482: FUNCTION check_prim_cond(
6483: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6484: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6485: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6486: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6487: RETURN BOOLEAN IS
6488: BEGIN
6489: IF p_party_search_rec.PARTY_NUMBER IS NOT NULL THEN
6482: FUNCTION check_prim_cond(
6483: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6484: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6485: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6486: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6487: RETURN BOOLEAN IS
6488: BEGIN
6489: IF p_party_search_rec.PARTY_NUMBER IS NOT NULL THEN
6490: RETURN TRUE;
6535: has party site criteria.
6536: ************************************************/
6537:
6538: PROCEDURE check_party_site_cond(
6539: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6540: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6541: x_secondary OUT BOOLEAN,
6542: x_primary OUT BOOLEAN
6543: ) IS
6536: ************************************************/
6537:
6538: PROCEDURE check_party_site_cond(
6539: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6540: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6541: x_secondary OUT BOOLEAN,
6542: x_primary OUT BOOLEAN
6543: ) IS
6544: BEGIN
6585: has contact criteria.
6586: ************************************************/
6587:
6588: PROCEDURE check_contact_cond(
6589: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6590: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6591: x_secondary OUT BOOLEAN,
6592: x_primary OUT BOOLEAN
6593: ) IS
6586: ************************************************/
6587:
6588: PROCEDURE check_contact_cond(
6589: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6590: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6591: x_secondary OUT BOOLEAN,
6592: x_primary OUT BOOLEAN
6593: ) IS
6594: BEGIN
6623: has valid contact point criteria.
6624: ************************************************/
6625:
6626: PROCEDURE check_contact_point_cond(
6627: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6628: x_secondary OUT BOOLEAN,
6629: x_primary OUT BOOLEAN
6630: ) IS
6631: BEGIN