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;
409: PROCEDURE INSERT_PARTY_SCORE (
410: p_party_id IN NUMBER
411: ,p_record_id IN NUMBER
412: ,p_search_ctx_id IN NUMBER
413: ,p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
414: ,p_stage_rec IN HZ_PARTY_STAGE.party_stage_rec_type
415: ,p_table_TX2 VARCHAR2
416: ,p_table_TX8 VARCHAR2
417: ,p_table_TX44 VARCHAR2
484: PROCEDURE INSERT_PARTY_SITES_SCORE (
485: p_party_id IN NUMBER
486: ,p_record_id IN NUMBER
487: ,p_search_ctx_id IN NUMBER
488: ,p_search_rec IN HZ_PARTY_SEARCH.party_site_search_rec_type
489: ,p_stage_rec IN HZ_PARTY_STAGE.party_site_stage_rec_type
490: ,p_table_TX3 VARCHAR2
491: ,p_table_TX4 VARCHAR2
492: ,p_table_TX11 VARCHAR2
559: PROCEDURE INSERT_CONTACTS_SCORE (
560: p_party_id IN NUMBER
561: ,p_record_id IN NUMBER
562: ,p_search_ctx_id IN NUMBER
563: ,p_search_rec IN HZ_PARTY_SEARCH.contact_search_rec_type
564: ,p_stage_rec IN HZ_PARTY_STAGE.contact_stage_rec_type
565: ,p_idx IN NUMBER) IS
566: l_current_score NUMBER:=0;
567: l_score NUMBER;
575: PROCEDURE INSERT_CONTACT_POINTS_SCORE (
576: p_party_id IN NUMBER
577: ,p_record_id IN NUMBER
578: ,p_search_ctx_id IN NUMBER
579: ,p_search_rec IN HZ_PARTY_SEARCH.contact_point_search_rec_type
580: ,p_stage_rec IN HZ_PARTY_STAGE.contact_pt_stage_rec_type
581: ,p_table_TX1 VARCHAR2
582: ,p_table_TX6 VARCHAR2
583: ,p_table_TX5 VARCHAR2
653: for the PARTY Entity
654: ************************************************/
655:
656: PROCEDURE POP_PARTY_COND_REC(
657: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
658: ) IS
659: BEGIN
660: NULL ;
661: EXCEPTION
673: for the PARTY_SITES Entity
674: ************************************************/
675:
676: PROCEDURE POP_PARTY_SITES_COND_REC(
677: p_search_list IN HZ_PARTY_SEARCH.party_site_list
678: ) IS
679: BEGIN
680: NULL ;
681: EXCEPTION
693: for the CONTACTS Entity
694: ************************************************/
695:
696: PROCEDURE POP_CONTACTS_COND_REC(
697: p_search_list IN HZ_PARTY_SEARCH.contact_list
698: ) IS
699: BEGIN
700: NULL ;
701: EXCEPTION
713: for the CONTACT_POINTS Entity
714: ************************************************/
715:
716: PROCEDURE POP_CONTACT_POINTS_COND_REC(
717: p_search_list IN HZ_PARTY_SEARCH.contact_point_list
718: ) IS
719: BEGIN
720: NULL ;
721: EXCEPTION
727: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
728: END;
729:
730: PROCEDURE init_score_context (
731: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
732: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
733: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
734: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
735: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
728: END;
729:
730: PROCEDURE init_score_context (
731: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
732: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
733: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
734: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
735: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
736: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
729:
730: PROCEDURE init_score_context (
731: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
732: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
733: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
734: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
735: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
736: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
737: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
730: PROCEDURE init_score_context (
731: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
732: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
733: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
734: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
735: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
736: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
737: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
738: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
731: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
732: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
733: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
734: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
735: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
736: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
737: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
738: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
739: ) IS
732: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
733: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
734: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
735: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
736: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
737: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
738: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
739: ) IS
740: l_dummy NUMBER;
733: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
734: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
735: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
736: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
737: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
738: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
739: ) IS
740: l_dummy NUMBER;
741: BEGIN
734: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
735: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
736: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
737: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
738: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
739: ) IS
740: l_dummy NUMBER;
741: BEGIN
742: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
754: MAP_CONTACT_POINT_REC(FALSE,p_contact_point_list, l_dummy, g_contact_pt_stage_list);
755:
756: END;
757: FUNCTION init_search(
758: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
759: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
760: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
761: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
762: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
755:
756: END;
757: FUNCTION init_search(
758: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
759: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
760: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
761: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
762: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
763: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
756: END;
757: FUNCTION init_search(
758: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
759: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
760: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
761: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
762: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
763: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
764: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
757: FUNCTION init_search(
758: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
759: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
760: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
761: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
762: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
763: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
764: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
765: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
758: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
759: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
760: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
761: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
762: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
763: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
764: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
765: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
766: p_match_type IN VARCHAR2,
759: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
760: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
761: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
762: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
763: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
764: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
765: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
766: p_match_type IN VARCHAR2,
767: x_party_max_score OUT NUMBER,
760: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
761: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
762: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
763: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
764: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
765: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
766: p_match_type IN VARCHAR2,
767: x_party_max_score OUT NUMBER,
768: x_ps_max_score OUT NUMBER,
761: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
762: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
763: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
764: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
765: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
766: p_match_type IN VARCHAR2,
767: x_party_max_score OUT NUMBER,
768: x_ps_max_score OUT NUMBER,
769: x_contact_max_score OUT NUMBER,
773: l_ps_entered_max_score NUMBER:=0;
774: l_ct_entered_max_score NUMBER:=0;
775: l_cpt_entered_max_score NUMBER:=0;
776: vlist vlisttype;
777: maxscore HZ_PARTY_SEARCH.IDList;
778: l_name VARCHAR2(200);
779: l_idx NUMBER;
780: l_num NUMBER;
781: total NUMBER;
2134: p_party_id NUMBER,
2135: p_party_site_id NUMBER,
2136: p_org_contact_id NUMBER,
2137: p_contact_point_id NUMBER)
2138: RETURN HZ_PARTY_SEARCH.score_rec IS
2139: l_score_rec HZ_PARTY_SEARCH.score_rec;
2140: BEGIN
2141: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2142: 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);
2135: p_party_site_id NUMBER,
2136: p_org_contact_id NUMBER,
2137: p_contact_point_id NUMBER)
2138: RETURN HZ_PARTY_SEARCH.score_rec IS
2139: l_score_rec HZ_PARTY_SEARCH.score_rec;
2140: BEGIN
2141: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2142: 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);
2143: 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);
2167: **********************************************************/
2168:
2169: PROCEDURE find_parties (
2170: p_rule_id IN NUMBER,
2171: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2172: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2173: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2174: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2175: p_restrict_sql IN VARCHAR2,
2168:
2169: PROCEDURE find_parties (
2170: p_rule_id IN NUMBER,
2171: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2172: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2173: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2174: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2175: p_restrict_sql IN VARCHAR2,
2176: p_match_type IN VARCHAR2,
2169: PROCEDURE find_parties (
2170: p_rule_id IN NUMBER,
2171: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2172: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2173: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2174: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2175: p_restrict_sql IN VARCHAR2,
2176: p_match_type IN VARCHAR2,
2177: p_search_merged IN VARCHAR2,
2170: p_rule_id IN NUMBER,
2171: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2172: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2173: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2174: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2175: p_restrict_sql IN VARCHAR2,
2176: p_match_type IN VARCHAR2,
2177: p_search_merged IN VARCHAR2,
2178: p_dup_party_id IN NUMBER,
2211: l_TX11 VARCHAR2(2000);
2212: l_TX5 VARCHAR2(2000);
2213: l_TX8 VARCHAR2(2000);
2214: l_TX6 VARCHAR2(2000);
2215: H_SCORES HZ_PARTY_SEARCH.score_list;
2216: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2217: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2218:
2219: l_score NUMBER;
2212: l_TX5 VARCHAR2(2000);
2213: l_TX8 VARCHAR2(2000);
2214: l_TX6 VARCHAR2(2000);
2215: H_SCORES HZ_PARTY_SEARCH.score_list;
2216: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2217: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2218:
2219: l_score NUMBER;
2220: l_idx NUMBER;
2213: l_TX8 VARCHAR2(2000);
2214: l_TX6 VARCHAR2(2000);
2215: H_SCORES HZ_PARTY_SEARCH.score_list;
2216: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2217: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2218:
2219: l_score NUMBER;
2220: l_idx NUMBER;
2221: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
2405: /** Private procedure to acquire and score at party site level ***/
2406: 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
2407: l_party_id_idx NUMBER:=1;
2408: l_ctx_id NUMBER;
2409: h_ps_id HZ_PARTY_SEARCH.IDList;
2410: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2411: h_ps_score HZ_PARTY_SEARCH.IDList;
2412: detcnt NUMBER := 1;
2413: BEGIN
2406: 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
2407: l_party_id_idx NUMBER:=1;
2408: l_ctx_id NUMBER;
2409: h_ps_id HZ_PARTY_SEARCH.IDList;
2410: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2411: h_ps_score HZ_PARTY_SEARCH.IDList;
2412: detcnt NUMBER := 1;
2413: BEGIN
2414: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2407: l_party_id_idx NUMBER:=1;
2408: l_ctx_id NUMBER;
2409: h_ps_id HZ_PARTY_SEARCH.IDList;
2410: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2411: h_ps_score HZ_PARTY_SEARCH.IDList;
2412: detcnt NUMBER := 1;
2413: BEGIN
2414: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2415: 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);
2527: /** Private procedure to acquire and score at contact point level ***/
2528: 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
2529: l_party_id_idx NUMBER:=1;
2530: l_ctx_id NUMBER;
2531: h_ct_id HZ_PARTY_SEARCH.IDList;
2532: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2533: h_ct_score HZ_PARTY_SEARCH.IDList;
2534: detcnt NUMBER := 1;
2535: BEGIN
2528: 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
2529: l_party_id_idx NUMBER:=1;
2530: l_ctx_id NUMBER;
2531: h_ct_id HZ_PARTY_SEARCH.IDList;
2532: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2533: h_ct_score HZ_PARTY_SEARCH.IDList;
2534: detcnt NUMBER := 1;
2535: BEGIN
2536: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2529: l_party_id_idx NUMBER:=1;
2530: l_ctx_id NUMBER;
2531: h_ct_id HZ_PARTY_SEARCH.IDList;
2532: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2533: h_ct_score HZ_PARTY_SEARCH.IDList;
2534: detcnt NUMBER := 1;
2535: BEGIN
2536: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2537: 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);
2636: /** Private procedure to acquire and score at contact point level ***/
2637: 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
2638: l_party_id_idx NUMBER:=1;
2639: l_ctx_id NUMBER;
2640: h_cpt_id HZ_PARTY_SEARCH.IDList;
2641: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2642: h_cpt_score HZ_PARTY_SEARCH.IDList;
2643: detcnt NUMBER := 1;
2644: l_cpt_flag VARCHAR2(1) := 'N';
2637: 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
2638: l_party_id_idx NUMBER:=1;
2639: l_ctx_id NUMBER;
2640: h_cpt_id HZ_PARTY_SEARCH.IDList;
2641: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2642: h_cpt_score HZ_PARTY_SEARCH.IDList;
2643: detcnt NUMBER := 1;
2644: l_cpt_flag VARCHAR2(1) := 'N';
2645: is_a_match VARCHAR2(1) := 'Y';
2638: l_party_id_idx NUMBER:=1;
2639: l_ctx_id NUMBER;
2640: h_cpt_id HZ_PARTY_SEARCH.IDList;
2641: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2642: h_cpt_score HZ_PARTY_SEARCH.IDList;
2643: detcnt NUMBER := 1;
2644: l_cpt_flag VARCHAR2(1) := 'N';
2645: is_a_match VARCHAR2(1) := 'Y';
2646: BEGIN
3125: END find_parties;
3126:
3127: PROCEDURE find_persons (
3128: p_rule_id IN NUMBER,
3129: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3130: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3131: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3132: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3133: p_restrict_sql IN VARCHAR2,
3126:
3127: PROCEDURE find_persons (
3128: p_rule_id IN NUMBER,
3129: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3130: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3131: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3132: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3133: p_restrict_sql IN VARCHAR2,
3134: p_match_type IN VARCHAR2,
3127: PROCEDURE find_persons (
3128: p_rule_id IN NUMBER,
3129: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3130: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3131: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3132: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3133: p_restrict_sql IN VARCHAR2,
3134: p_match_type IN VARCHAR2,
3135: p_search_merged IN VARCHAR2,
3128: p_rule_id IN NUMBER,
3129: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3130: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3131: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3132: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3133: p_restrict_sql IN VARCHAR2,
3134: p_match_type IN VARCHAR2,
3135: p_search_merged IN VARCHAR2,
3136: p_dup_party_id IN NUMBER,
3169: l_TX11 VARCHAR2(2000);
3170: l_TX5 VARCHAR2(2000);
3171: l_TX8 VARCHAR2(2000);
3172: l_TX6 VARCHAR2(2000);
3173: H_SCORES HZ_PARTY_SEARCH.score_list;
3174: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3175: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3176:
3177: l_score NUMBER;
3170: l_TX5 VARCHAR2(2000);
3171: l_TX8 VARCHAR2(2000);
3172: l_TX6 VARCHAR2(2000);
3173: H_SCORES HZ_PARTY_SEARCH.score_list;
3174: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3175: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3176:
3177: l_score NUMBER;
3178: l_idx NUMBER;
3171: l_TX8 VARCHAR2(2000);
3172: l_TX6 VARCHAR2(2000);
3173: H_SCORES HZ_PARTY_SEARCH.score_list;
3174: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3175: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3176:
3177: l_score NUMBER;
3178: l_idx NUMBER;
3179: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
3411: /** Private procedure to acquire and score at party site level ***/
3412: 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
3413: l_party_id_idx NUMBER:=1;
3414: l_ctx_id NUMBER;
3415: h_ps_id HZ_PARTY_SEARCH.IDList;
3416: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3417: h_ps_score HZ_PARTY_SEARCH.IDList;
3418: detcnt NUMBER := 1;
3419: l_person_id NUMBER;
3412: 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
3413: l_party_id_idx NUMBER:=1;
3414: l_ctx_id NUMBER;
3415: h_ps_id HZ_PARTY_SEARCH.IDList;
3416: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3417: h_ps_score HZ_PARTY_SEARCH.IDList;
3418: detcnt NUMBER := 1;
3419: l_person_id NUMBER;
3420: BEGIN
3413: l_party_id_idx NUMBER:=1;
3414: l_ctx_id NUMBER;
3415: h_ps_id HZ_PARTY_SEARCH.IDList;
3416: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3417: h_ps_score HZ_PARTY_SEARCH.IDList;
3418: detcnt NUMBER := 1;
3419: l_person_id NUMBER;
3420: BEGIN
3421: SAVEPOINT eval_start;
3510: /** Private procedure to acquire and score at party site level ***/
3511: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3512: l_party_id_idx NUMBER:=1;
3513: l_ctx_id NUMBER;
3514: h_ct_id HZ_PARTY_SEARCH.IDList;
3515: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3516: h_ct_score HZ_PARTY_SEARCH.IDList;
3517: detcnt NUMBER := 1;
3518: l_person_id NUMBER;
3511: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3512: l_party_id_idx NUMBER:=1;
3513: l_ctx_id NUMBER;
3514: h_ct_id HZ_PARTY_SEARCH.IDList;
3515: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3516: h_ct_score HZ_PARTY_SEARCH.IDList;
3517: detcnt NUMBER := 1;
3518: l_person_id NUMBER;
3519: BEGIN
3512: l_party_id_idx NUMBER:=1;
3513: l_ctx_id NUMBER;
3514: h_ct_id HZ_PARTY_SEARCH.IDList;
3515: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3516: h_ct_score HZ_PARTY_SEARCH.IDList;
3517: detcnt NUMBER := 1;
3518: l_person_id NUMBER;
3519: BEGIN
3520: SAVEPOINT eval_start;
3568: /** Private procedure to acquire and score at contact point level ***/
3569: 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
3570: l_party_id_idx NUMBER:=1;
3571: l_ctx_id NUMBER;
3572: h_cpt_id HZ_PARTY_SEARCH.IDList;
3573: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3574: h_cpt_score HZ_PARTY_SEARCH.IDList;
3575: detcnt NUMBER := 1;
3576: l_person_id NUMBER;
3569: 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
3570: l_party_id_idx NUMBER:=1;
3571: l_ctx_id NUMBER;
3572: h_cpt_id HZ_PARTY_SEARCH.IDList;
3573: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3574: h_cpt_score HZ_PARTY_SEARCH.IDList;
3575: detcnt NUMBER := 1;
3576: l_person_id NUMBER;
3577: is_a_match VARCHAR2(1) := 'Y';
3570: l_party_id_idx NUMBER:=1;
3571: l_ctx_id NUMBER;
3572: h_cpt_id HZ_PARTY_SEARCH.IDList;
3573: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3574: h_cpt_score HZ_PARTY_SEARCH.IDList;
3575: detcnt NUMBER := 1;
3576: l_person_id NUMBER;
3577: is_a_match VARCHAR2(1) := 'Y';
3578: l_cpt_flag VARCHAR2(1) := 'N';
3981: END find_persons;
3982:
3983: PROCEDURE find_persons (
3984: p_rule_id IN NUMBER,
3985: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3986: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3987: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3988: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3989: p_restrict_sql IN VARCHAR2,
3982:
3983: PROCEDURE find_persons (
3984: p_rule_id IN NUMBER,
3985: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3986: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3987: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3988: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3989: p_restrict_sql IN VARCHAR2,
3990: p_match_type IN VARCHAR2,
3983: PROCEDURE find_persons (
3984: p_rule_id IN NUMBER,
3985: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3986: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3987: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3988: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3989: p_restrict_sql IN VARCHAR2,
3990: p_match_type IN VARCHAR2,
3991: p_search_merged IN VARCHAR2,
3984: p_rule_id IN NUMBER,
3985: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3986: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3987: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3988: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3989: p_restrict_sql IN VARCHAR2,
3990: p_match_type IN VARCHAR2,
3991: p_search_merged IN VARCHAR2,
3992: p_ins_details IN VARCHAR2,
3998: 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);
3999: END;
4000: PROCEDURE find_party_details (
4001: p_rule_id IN NUMBER,
4002: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4003: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4004: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4005: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4006: p_restrict_sql IN VARCHAR2,
3999: END;
4000: PROCEDURE find_party_details (
4001: p_rule_id IN NUMBER,
4002: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4003: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4004: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4005: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4006: p_restrict_sql IN VARCHAR2,
4007: p_match_type IN VARCHAR2,
4000: PROCEDURE find_party_details (
4001: p_rule_id IN NUMBER,
4002: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4003: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4004: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4005: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4006: p_restrict_sql IN VARCHAR2,
4007: p_match_type IN VARCHAR2,
4008: p_search_merged IN VARCHAR2,
4001: p_rule_id IN NUMBER,
4002: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4003: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4004: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4005: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4006: p_restrict_sql IN VARCHAR2,
4007: p_match_type IN VARCHAR2,
4008: p_search_merged IN VARCHAR2,
4009: x_search_ctx_id OUT NUMBER,
4058: x_dup_set_id OUT NUMBER,
4059: x_search_ctx_id OUT NUMBER,
4060: x_num_matches OUT NUMBER
4061: ) IS
4062: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4063: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4064: l_contact_list HZ_PARTY_SEARCH.contact_list;
4065: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4066: l_match_idx NUMBER;
4059: x_search_ctx_id OUT NUMBER,
4060: x_num_matches OUT NUMBER
4061: ) IS
4062: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4063: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4064: l_contact_list HZ_PARTY_SEARCH.contact_list;
4065: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4066: l_match_idx NUMBER;
4067:
4060: x_num_matches OUT NUMBER
4061: ) IS
4062: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4063: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4064: l_contact_list HZ_PARTY_SEARCH.contact_list;
4065: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4066: l_match_idx NUMBER;
4067:
4068: --Fix for bug 4417124
4061: ) IS
4062: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4063: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4064: l_contact_list HZ_PARTY_SEARCH.contact_list;
4065: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4066: l_match_idx NUMBER;
4067:
4068: --Fix for bug 4417124
4069: l_use_contact_addr_info BOOLEAN := TRUE;
4174: x_search_ctx_id OUT NUMBER,
4175: x_num_matches OUT NUMBER
4176: ) IS
4177:
4178: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4179: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4180: l_contact_list HZ_PARTY_SEARCH.contact_list;
4181: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4182: contact_point_ids HZ_PARTY_SEARCH.IDList;
4175: x_num_matches OUT NUMBER
4176: ) IS
4177:
4178: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4179: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4180: l_contact_list HZ_PARTY_SEARCH.contact_list;
4181: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4182: contact_point_ids HZ_PARTY_SEARCH.IDList;
4183: p_party_site_list HZ_PARTY_SEARCH.IDList;
4176: ) IS
4177:
4178: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4179: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4180: l_contact_list HZ_PARTY_SEARCH.contact_list;
4181: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4182: contact_point_ids HZ_PARTY_SEARCH.IDList;
4183: p_party_site_list HZ_PARTY_SEARCH.IDList;
4184: p_contact_ids HZ_PARTY_SEARCH.IDList;
4177:
4178: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4179: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4180: l_contact_list HZ_PARTY_SEARCH.contact_list;
4181: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4182: contact_point_ids HZ_PARTY_SEARCH.IDList;
4183: p_party_site_list HZ_PARTY_SEARCH.IDList;
4184: p_contact_ids HZ_PARTY_SEARCH.IDList;
4185: l_match_idx NUMBER;
4178: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4179: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4180: l_contact_list HZ_PARTY_SEARCH.contact_list;
4181: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4182: contact_point_ids HZ_PARTY_SEARCH.IDList;
4183: p_party_site_list HZ_PARTY_SEARCH.IDList;
4184: p_contact_ids HZ_PARTY_SEARCH.IDList;
4185: l_match_idx NUMBER;
4186: cursor get_cpts_for_party_sites is select contact_point_id
4179: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4180: l_contact_list HZ_PARTY_SEARCH.contact_list;
4181: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4182: contact_point_ids HZ_PARTY_SEARCH.IDList;
4183: p_party_site_list HZ_PARTY_SEARCH.IDList;
4184: p_contact_ids HZ_PARTY_SEARCH.IDList;
4185: l_match_idx NUMBER;
4186: cursor get_cpts_for_party_sites is select contact_point_id
4187: from hz_contact_points
4180: l_contact_list HZ_PARTY_SEARCH.contact_list;
4181: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4182: contact_point_ids HZ_PARTY_SEARCH.IDList;
4183: p_party_site_list HZ_PARTY_SEARCH.IDList;
4184: p_contact_ids HZ_PARTY_SEARCH.IDList;
4185: l_match_idx NUMBER;
4186: cursor get_cpts_for_party_sites is select contact_point_id
4187: from hz_contact_points
4188: where owner_table_name = 'HZ_PARTY_SITES'
4209:
4210: get_search_criteria (
4211: null,
4212: p_party_site_list,
4213: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4214: contact_point_ids,
4215: l_party_search_rec,
4216: l_party_site_list,
4217: l_contact_list,
4259: x_search_ctx_id OUT NUMBER,
4260: x_num_matches OUT NUMBER
4261: ) IS
4262:
4263: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4264: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4265: l_contact_list HZ_PARTY_SEARCH.contact_list;
4266: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4267: contact_point_ids HZ_PARTY_SEARCH.IDList;
4260: x_num_matches OUT NUMBER
4261: ) IS
4262:
4263: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4264: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4265: l_contact_list HZ_PARTY_SEARCH.contact_list;
4266: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4267: contact_point_ids HZ_PARTY_SEARCH.IDList;
4268: p_party_site_list HZ_PARTY_SEARCH.IDList;
4261: ) IS
4262:
4263: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4264: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4265: l_contact_list HZ_PARTY_SEARCH.contact_list;
4266: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4267: contact_point_ids HZ_PARTY_SEARCH.IDList;
4268: p_party_site_list HZ_PARTY_SEARCH.IDList;
4269: p_contact_ids HZ_PARTY_SEARCH.IDList;
4262:
4263: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4264: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4265: l_contact_list HZ_PARTY_SEARCH.contact_list;
4266: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4267: contact_point_ids HZ_PARTY_SEARCH.IDList;
4268: p_party_site_list HZ_PARTY_SEARCH.IDList;
4269: p_contact_ids HZ_PARTY_SEARCH.IDList;
4270: l_match_idx NUMBER;
4263: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4264: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4265: l_contact_list HZ_PARTY_SEARCH.contact_list;
4266: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4267: contact_point_ids HZ_PARTY_SEARCH.IDList;
4268: p_party_site_list HZ_PARTY_SEARCH.IDList;
4269: p_contact_ids HZ_PARTY_SEARCH.IDList;
4270: l_match_idx NUMBER;
4271: cursor get_cpt_for_contact_id is select contact_point_id
4264: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4265: l_contact_list HZ_PARTY_SEARCH.contact_list;
4266: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4267: contact_point_ids HZ_PARTY_SEARCH.IDList;
4268: p_party_site_list HZ_PARTY_SEARCH.IDList;
4269: p_contact_ids HZ_PARTY_SEARCH.IDList;
4270: l_match_idx NUMBER;
4271: cursor get_cpt_for_contact_id is select contact_point_id
4272: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4265: l_contact_list HZ_PARTY_SEARCH.contact_list;
4266: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4267: contact_point_ids HZ_PARTY_SEARCH.IDList;
4268: p_party_site_list HZ_PARTY_SEARCH.IDList;
4269: p_contact_ids HZ_PARTY_SEARCH.IDList;
4270: l_match_idx NUMBER;
4271: cursor get_cpt_for_contact_id is select contact_point_id
4272: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4273: where a.party_relationship_id = b.relationship_id
4296: CLOSE get_cpt_for_contact_id;
4297:
4298: get_search_criteria (
4299: null,
4300: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4301: p_contact_ids,
4302: contact_point_ids,
4303: l_party_search_rec,
4304: l_party_site_list,
4347: p_match_type IN VARCHAR2,
4348: x_search_ctx_id OUT NUMBER,
4349: x_num_matches OUT NUMBER
4350: ) IS
4351: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4352: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4353: l_contact_list HZ_PARTY_SEARCH.contact_list;
4354: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4355: contact_point_ids HZ_PARTY_SEARCH.IDList;
4348: x_search_ctx_id OUT NUMBER,
4349: x_num_matches OUT NUMBER
4350: ) IS
4351: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4352: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4353: l_contact_list HZ_PARTY_SEARCH.contact_list;
4354: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4355: contact_point_ids HZ_PARTY_SEARCH.IDList;
4356: p_party_site_list HZ_PARTY_SEARCH.IDList;
4349: x_num_matches OUT NUMBER
4350: ) IS
4351: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4352: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4353: l_contact_list HZ_PARTY_SEARCH.contact_list;
4354: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4355: contact_point_ids HZ_PARTY_SEARCH.IDList;
4356: p_party_site_list HZ_PARTY_SEARCH.IDList;
4357: p_contact_ids HZ_PARTY_SEARCH.IDList;
4350: ) IS
4351: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4352: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4353: l_contact_list HZ_PARTY_SEARCH.contact_list;
4354: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4355: contact_point_ids HZ_PARTY_SEARCH.IDList;
4356: p_party_site_list HZ_PARTY_SEARCH.IDList;
4357: p_contact_ids HZ_PARTY_SEARCH.IDList;
4358: l_match_idx NUMBER;
4351: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4352: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4353: l_contact_list HZ_PARTY_SEARCH.contact_list;
4354: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4355: contact_point_ids HZ_PARTY_SEARCH.IDList;
4356: p_party_site_list HZ_PARTY_SEARCH.IDList;
4357: p_contact_ids HZ_PARTY_SEARCH.IDList;
4358: l_match_idx NUMBER;
4359:
4352: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4353: l_contact_list HZ_PARTY_SEARCH.contact_list;
4354: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4355: contact_point_ids HZ_PARTY_SEARCH.IDList;
4356: p_party_site_list HZ_PARTY_SEARCH.IDList;
4357: p_contact_ids HZ_PARTY_SEARCH.IDList;
4358: l_match_idx NUMBER;
4359:
4360: BEGIN
4353: l_contact_list HZ_PARTY_SEARCH.contact_list;
4354: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4355: contact_point_ids HZ_PARTY_SEARCH.IDList;
4356: p_party_site_list HZ_PARTY_SEARCH.IDList;
4357: p_contact_ids HZ_PARTY_SEARCH.IDList;
4358: l_match_idx NUMBER;
4359:
4360: BEGIN
4361: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4370: END IF;
4371: contact_point_ids(1) := p_contact_point_id;
4372: get_search_criteria (
4373: null,
4374: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4375: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4376: contact_point_ids,
4377: l_party_search_rec,
4378: l_party_site_list,
4371: contact_point_ids(1) := p_contact_point_id;
4372: get_search_criteria (
4373: null,
4374: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4375: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4376: contact_point_ids,
4377: l_party_search_rec,
4378: l_party_site_list,
4379: l_contact_list,
4524: ) IS
4525: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4526: l_match_idx NUMBER;
4527: AttrVals AttrList;
4528: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4529: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4530: l_contact_list HZ_PARTY_SEARCH.contact_list;
4531: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4532: l_dup_set_id NUMBER;
4525: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4526: l_match_idx NUMBER;
4527: AttrVals AttrList;
4528: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4529: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4530: l_contact_list HZ_PARTY_SEARCH.contact_list;
4531: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4532: l_dup_set_id NUMBER;
4533: l_idx NUMBER;
4526: l_match_idx NUMBER;
4527: AttrVals AttrList;
4528: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4529: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4530: l_contact_list HZ_PARTY_SEARCH.contact_list;
4531: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4532: l_dup_set_id NUMBER;
4533: l_idx NUMBER;
4534: l_cpt_type VARCHAR2(255);
4527: AttrVals AttrList;
4528: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4529: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4530: l_contact_list HZ_PARTY_SEARCH.contact_list;
4531: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4532: l_dup_set_id NUMBER;
4533: l_idx NUMBER;
4534: l_cpt_type VARCHAR2(255);
4535: FIRST BOOLEAN := TRUE;
4723:
4724: PROCEDURE get_matching_party_sites (
4725: p_rule_id IN NUMBER,
4726: p_party_id IN NUMBER,
4727: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4728: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4729: p_restrict_sql IN VARCHAR2,
4730: p_match_type IN VARCHAR2,
4731: p_dup_party_site_id IN NUMBER,
4724: PROCEDURE get_matching_party_sites (
4725: p_rule_id IN NUMBER,
4726: p_party_id IN NUMBER,
4727: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4728: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4729: p_restrict_sql IN VARCHAR2,
4730: p_match_type IN VARCHAR2,
4731: p_dup_party_site_id IN NUMBER,
4732: x_search_ctx_id OUT NUMBER,
4755: l_TX4 VARCHAR2(2000);
4756: l_TX11 VARCHAR2(2000);
4757: l_TX5 VARCHAR2(2000);
4758: l_TX6 VARCHAR2(2000);
4759: H_SCORES HZ_PARTY_SEARCH.score_list;
4760:
4761: l_score NUMBER;
4762: l_idx NUMBER;
4763: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
4786: defps NUMBER :=0;
4787: defct NUMBER :=0;
4788: defcpt NUMBER :=0;
4789: l_index NUMBER;
4790: l_match_ps_list HZ_PARTY_SEARCH.IDList;
4791: l_cnt NUMBER:=1;
4792:
4793:
4794: BEGIN
4810: l_match_str := ' AND ';
4811: ELSIF p_match_type = 'OR' THEN
4812: l_match_str := ' OR ';
4813: END IF;
4814: 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);
4815: g_score_until_thresh := false;
4816: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4817:
4818: -- ************************************************************
4818: -- ************************************************************
4819: -- STEP 2. Setup of intermedia query strings for Acquisition query
4820: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4821: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4822: 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);
4823:
4824: -- Setup Search Context ID
4825: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4826: x_search_ctx_id := l_search_ctx_id;
4961:
4962: PROCEDURE get_matching_contacts (
4963: p_rule_id IN NUMBER,
4964: p_party_id IN NUMBER,
4965: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4966: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4967: p_restrict_sql IN VARCHAR2,
4968: p_match_type IN VARCHAR2,
4969: p_dup_contact_id IN NUMBER,
4962: PROCEDURE get_matching_contacts (
4963: p_rule_id IN NUMBER,
4964: p_party_id IN NUMBER,
4965: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4966: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4967: p_restrict_sql IN VARCHAR2,
4968: p_match_type IN VARCHAR2,
4969: p_dup_contact_id IN NUMBER,
4970: x_search_ctx_id OUT NUMBER,
4990:
4991: l_TX1 VARCHAR2(2000);
4992: l_TX5 VARCHAR2(2000);
4993: l_TX6 VARCHAR2(2000);
4994: H_SCORES HZ_PARTY_SEARCH.score_list;
4995:
4996: l_score NUMBER;
4997: l_idx NUMBER;
4998: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
5018: l_party_max_score NUMBER;
5019: l_ps_max_score NUMBER;
5020: l_contact_max_score NUMBER;
5021: l_cpt_max_score NUMBER;
5022: l_match_contact_list HZ_PARTY_SEARCH.IDList;
5023: l_cnt NUMBER:=1;
5024:
5025:
5026: BEGIN
5042: l_match_str := ' AND ';
5043: ELSIF p_match_type = 'OR' THEN
5044: l_match_str := ' OR ';
5045: END IF;
5046: 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);
5047: g_score_until_thresh := false;
5048: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5049:
5050: -- ************************************************************
5050: -- ************************************************************
5051: -- STEP 2. Setup of intermedia query strings for Acquisition query
5052: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5053: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5054: 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);
5055:
5056: -- Setup Search Context ID
5057: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5058: x_search_ctx_id := l_search_ctx_id;
5189:
5190: PROCEDURE get_matching_contact_points (
5191: p_rule_id IN NUMBER,
5192: p_party_id IN NUMBER,
5193: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5194: p_restrict_sql IN VARCHAR2,
5195: p_match_type IN VARCHAR2,
5196: p_dup_contact_point_id IN NUMBER,
5197: 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_TX8 VARCHAR2(2000);
5377: l_max_TX8 VARCHAR2(2000);
5378: l_TX6 VARCHAR2(2000);
5379: l_max_TX6 VARCHAR2(2000);
5380: H_SCORES HZ_PARTY_SEARCH.score_list;
5381:
5382: l_score NUMBER;
5383: l_match_idx NUMBER;
5384: l_idx NUMBER;
5548: ************************************************/
5549:
5550: PROCEDURE get_party_for_search (
5551: p_party_id IN NUMBER,
5552: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5553: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5554: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5555: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5556: ) IS
5549:
5550: PROCEDURE get_party_for_search (
5551: p_party_id IN NUMBER,
5552: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5553: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5554: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5555: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5556: ) IS
5557: l_party_id NUMBER;
5550: PROCEDURE get_party_for_search (
5551: p_party_id IN NUMBER,
5552: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5553: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5554: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5555: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5556: ) IS
5557: l_party_id NUMBER;
5558: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5551: p_party_id IN NUMBER,
5552: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5553: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5554: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5555: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5556: ) IS
5557: l_party_id NUMBER;
5558: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5559: l_contact_ids HZ_PARTY_SEARCH.IDList;
5554: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5555: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5556: ) IS
5557: l_party_id NUMBER;
5558: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5559: l_contact_ids HZ_PARTY_SEARCH.IDList;
5560: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5561: ps NUMBER :=1;
5562: cpt NUMBER :=1;
5555: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5556: ) IS
5557: l_party_id NUMBER;
5558: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5559: l_contact_ids HZ_PARTY_SEARCH.IDList;
5560: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5561: ps NUMBER :=1;
5562: cpt NUMBER :=1;
5563: ct NUMBER :=1;
5556: ) IS
5557: l_party_id NUMBER;
5558: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5559: l_contact_ids HZ_PARTY_SEARCH.IDList;
5560: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5561: ps NUMBER :=1;
5562: cpt NUMBER :=1;
5563: ct NUMBER :=1;
5564: l_use_contact_info varchar2(1);
5652: END get_party_for_search;
5653:
5654: PROCEDURE get_search_criteria (
5655: p_party_id IN NUMBER,
5656: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5657: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5658: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5659: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5660: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5653:
5654: PROCEDURE get_search_criteria (
5655: p_party_id IN NUMBER,
5656: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5657: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5658: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5659: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5660: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5661: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5654: PROCEDURE get_search_criteria (
5655: p_party_id IN NUMBER,
5656: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5657: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5658: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5659: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5660: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5661: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5662: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5655: p_party_id IN NUMBER,
5656: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5657: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5658: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5659: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5660: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5661: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5662: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5663: ) IS
5656: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5657: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5658: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5659: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5660: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5661: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5662: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5663: ) IS
5664: BEGIN
5657: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5658: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5659: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5660: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5661: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5662: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5663: ) IS
5664: BEGIN
5665:
5658: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5659: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5660: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5661: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5662: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5663: ) IS
5664: BEGIN
5665:
5666: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5704: ************************************************/
5705:
5706: PROCEDURE get_party_rec (
5707: p_party_id IN NUMBER,
5708: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type
5709: ) IS
5710: l_party_type VARCHAR2(255);
5711: BEGIN
5712:
5763: the party site search record structure
5764: ************************************************/
5765:
5766: PROCEDURE get_party_site_rec (
5767: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5768: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5769: ) IS
5770: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5771: SELECT party_site_id
5764: ************************************************/
5765:
5766: PROCEDURE get_party_site_rec (
5767: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5768: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5769: ) IS
5770: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5771: SELECT party_site_id
5772: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
5819: the contact search record structure
5820: ************************************************/
5821:
5822: PROCEDURE get_contact_rec (
5823: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5824: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5825: ) IS
5826: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5827: SELECT org_contact_id
5820: ************************************************/
5821:
5822: PROCEDURE get_contact_rec (
5823: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5824: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5825: ) IS
5826: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5827: SELECT org_contact_id
5828: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
5875: the contact point search record structure
5876: ************************************************/
5877:
5878: PROCEDURE get_contact_point_rec (
5879: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5880: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5881: ) IS
5882:
5883: -- Cursor to fetch primary contact points for party
5876: ************************************************/
5877:
5878: PROCEDURE get_contact_point_rec (
5879: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5880: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5881: ) IS
5882:
5883: -- Cursor to fetch primary contact points for party
5884: CURSOR c_cpts(cp_contact_point_id NUMBER) IS
5934: ************************************************/
5935:
5936: PROCEDURE MAP_PARTY_REC(
5937: p_search_ctx IN BOOLEAN,
5938: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5939: x_entered_max_score OUT NUMBER,
5940: x_stage_rec IN OUT NOCOPY HZ_PARTY_STAGE.party_stage_rec_type
5941: ) IS
5942: tmp VARCHAR2(4000);
6049: ************************************************/
6050:
6051: PROCEDURE MAP_PARTY_SITE_REC(
6052: p_search_ctx IN BOOLEAN,
6053: p_search_list IN HZ_PARTY_SEARCH.party_site_list,
6054: x_entered_max_score OUT NUMBER,
6055: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.party_site_stage_list
6056: ) IS
6057: l_current_max_score NUMBER;
6167: ************************************************/
6168:
6169: PROCEDURE MAP_CONTACT_REC(
6170: p_search_ctx IN BOOLEAN,
6171: p_search_list IN HZ_PARTY_SEARCH.contact_list,
6172: x_entered_max_score OUT NUMBER,
6173: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_stage_list
6174: ) IS
6175: l_current_max_score NUMBER;
6209: ************************************************/
6210:
6211: PROCEDURE MAP_CONTACT_POINT_REC(
6212: p_search_ctx IN BOOLEAN,
6213: p_search_list IN HZ_PARTY_SEARCH.contact_point_list,
6214: x_entered_max_score OUT NUMBER,
6215: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_pt_stage_list
6216: ) IS
6217: l_current_max_score NUMBER;
6339: 2. Contact Point Type is not null for each condition
6340: ************************************************/
6341:
6342: FUNCTION check_prim_cond(
6343: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6344: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6345: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6346: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6347: RETURN BOOLEAN IS
6340: ************************************************/
6341:
6342: FUNCTION check_prim_cond(
6343: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6344: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6345: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6346: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6347: RETURN BOOLEAN IS
6348: BEGIN
6341:
6342: FUNCTION check_prim_cond(
6343: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6344: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6345: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6346: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6347: RETURN BOOLEAN IS
6348: BEGIN
6349: FOR I IN 1..p_contact_point_list.COUNT LOOP
6342: FUNCTION check_prim_cond(
6343: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6344: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6345: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6346: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6347: RETURN BOOLEAN IS
6348: BEGIN
6349: FOR I IN 1..p_contact_point_list.COUNT LOOP
6350: IF p_contact_point_list(I).CONTACT_POINT_TYPE IS NULL AND (
6394: has party site criteria.
6395: ************************************************/
6396:
6397: PROCEDURE check_party_site_cond(
6398: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6399: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6400: x_secondary OUT BOOLEAN,
6401: x_primary OUT BOOLEAN
6402: ) IS
6395: ************************************************/
6396:
6397: PROCEDURE check_party_site_cond(
6398: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6399: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6400: x_secondary OUT BOOLEAN,
6401: x_primary OUT BOOLEAN
6402: ) IS
6403: BEGIN
6449: has contact criteria.
6450: ************************************************/
6451:
6452: PROCEDURE check_contact_cond(
6453: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6454: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6455: x_secondary OUT BOOLEAN,
6456: x_primary OUT BOOLEAN
6457: ) IS
6450: ************************************************/
6451:
6452: PROCEDURE check_contact_cond(
6453: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6454: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6455: x_secondary OUT BOOLEAN,
6456: x_primary OUT BOOLEAN
6457: ) IS
6458: BEGIN
6496: has valid contact point criteria.
6497: ************************************************/
6498:
6499: PROCEDURE check_contact_point_cond(
6500: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6501: x_secondary OUT BOOLEAN,
6502: x_primary OUT BOOLEAN
6503: ) IS
6504: BEGIN