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;
401: PROCEDURE INSERT_PARTY_SCORE (
402: p_party_id IN NUMBER
403: ,p_record_id IN NUMBER
404: ,p_search_ctx_id IN NUMBER
405: ,p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
406: ,p_stage_rec IN HZ_PARTY_STAGE.party_stage_rec_type
407: ,p_table_TX4 VARCHAR2
408: ,p_table_TX45 VARCHAR2
409: ,p_table_TX46 VARCHAR2
495: PROCEDURE INSERT_PARTY_SITES_SCORE (
496: p_party_id IN NUMBER
497: ,p_record_id IN NUMBER
498: ,p_search_ctx_id IN NUMBER
499: ,p_search_rec IN HZ_PARTY_SEARCH.party_site_search_rec_type
500: ,p_stage_rec IN HZ_PARTY_STAGE.party_site_stage_rec_type
501: ,p_table_TX4 VARCHAR2
502: ,p_table_TX11 VARCHAR2
503: ,p_idx IN NUMBER) IS
563: PROCEDURE INSERT_CONTACTS_SCORE (
564: p_party_id IN NUMBER
565: ,p_record_id IN NUMBER
566: ,p_search_ctx_id IN NUMBER
567: ,p_search_rec IN HZ_PARTY_SEARCH.contact_search_rec_type
568: ,p_stage_rec IN HZ_PARTY_STAGE.contact_stage_rec_type
569: ,p_idx IN NUMBER) IS
570: l_current_score NUMBER:=0;
571: l_score NUMBER;
579: PROCEDURE INSERT_CONTACT_POINTS_SCORE (
580: p_party_id IN NUMBER
581: ,p_record_id IN NUMBER
582: ,p_search_ctx_id IN NUMBER
583: ,p_search_rec IN HZ_PARTY_SEARCH.contact_point_search_rec_type
584: ,p_stage_rec IN HZ_PARTY_STAGE.contact_pt_stage_rec_type
585: ,p_table_TX2 VARCHAR2
586: ,p_idx IN NUMBER) IS
587: l_current_score NUMBER:=0;
624: for the PARTY Entity
625: ************************************************/
626:
627: PROCEDURE POP_PARTY_COND_REC(
628: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
629: ) IS
630: BEGIN
631: NULL ;
632: EXCEPTION
644: for the PARTY_SITES Entity
645: ************************************************/
646:
647: PROCEDURE POP_PARTY_SITES_COND_REC(
648: p_search_list IN HZ_PARTY_SEARCH.party_site_list
649: ) IS
650: BEGIN
651: NULL ;
652: EXCEPTION
664: for the CONTACTS Entity
665: ************************************************/
666:
667: PROCEDURE POP_CONTACTS_COND_REC(
668: p_search_list IN HZ_PARTY_SEARCH.contact_list
669: ) IS
670: BEGIN
671: NULL ;
672: EXCEPTION
684: for the CONTACT_POINTS Entity
685: ************************************************/
686:
687: PROCEDURE POP_CONTACT_POINTS_COND_REC(
688: p_search_list IN HZ_PARTY_SEARCH.contact_point_list
689: ) IS
690: BEGIN
691: NULL ;
692: EXCEPTION
698: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
699: END;
700:
701: PROCEDURE init_score_context (
702: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
703: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
704: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
705: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
706: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
699: END;
700:
701: PROCEDURE init_score_context (
702: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
703: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
704: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
705: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
706: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
707: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
700:
701: PROCEDURE init_score_context (
702: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
703: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
704: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
705: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
706: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
707: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
708: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
701: PROCEDURE init_score_context (
702: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
703: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
704: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
705: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
706: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
707: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
708: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
709: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
702: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
703: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
704: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
705: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
706: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
707: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
708: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
709: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
710: ) IS
703: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
704: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
705: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
706: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
707: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
708: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
709: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
710: ) IS
711: l_dummy NUMBER;
704: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
705: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
706: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
707: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
708: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
709: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
710: ) IS
711: l_dummy NUMBER;
712: BEGIN
705: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
706: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
707: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
708: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
709: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
710: ) IS
711: l_dummy NUMBER;
712: BEGIN
713: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
725: MAP_CONTACT_POINT_REC(FALSE,p_contact_point_list, l_dummy, g_contact_pt_stage_list);
726:
727: END;
728: FUNCTION init_search(
729: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
730: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
731: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
732: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
733: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
726:
727: END;
728: FUNCTION init_search(
729: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
730: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
731: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
732: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
733: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
734: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
727: END;
728: FUNCTION init_search(
729: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
730: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
731: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
732: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
733: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
734: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
735: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
728: FUNCTION init_search(
729: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
730: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
731: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
732: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
733: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
734: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
735: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
736: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
729: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
730: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
731: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
732: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
733: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
734: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
735: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
736: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
737: p_match_type IN VARCHAR2,
730: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
731: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
732: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
733: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
734: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
735: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
736: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
737: p_match_type IN VARCHAR2,
738: x_party_max_score OUT NUMBER,
731: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
732: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
733: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
734: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
735: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
736: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
737: p_match_type IN VARCHAR2,
738: x_party_max_score OUT NUMBER,
739: x_ps_max_score OUT NUMBER,
732: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
733: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
734: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
735: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
736: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
737: p_match_type IN VARCHAR2,
738: x_party_max_score OUT NUMBER,
739: x_ps_max_score OUT NUMBER,
740: x_contact_max_score OUT NUMBER,
744: l_ps_entered_max_score NUMBER:=0;
745: l_ct_entered_max_score NUMBER:=0;
746: l_cpt_entered_max_score NUMBER:=0;
747: vlist vlisttype;
748: maxscore HZ_PARTY_SEARCH.IDList;
749: l_name VARCHAR2(200);
750: l_idx NUMBER;
751: l_num NUMBER;
752: total NUMBER;
2129: p_party_id NUMBER,
2130: p_party_site_id NUMBER,
2131: p_org_contact_id NUMBER,
2132: p_contact_point_id NUMBER)
2133: RETURN HZ_PARTY_SEARCH.score_rec IS
2134: l_score_rec HZ_PARTY_SEARCH.score_rec;
2135: BEGIN
2136: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2137: 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);
2130: p_party_site_id NUMBER,
2131: p_org_contact_id NUMBER,
2132: p_contact_point_id NUMBER)
2133: RETURN HZ_PARTY_SEARCH.score_rec IS
2134: l_score_rec HZ_PARTY_SEARCH.score_rec;
2135: BEGIN
2136: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2137: 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);
2138: 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);
2162: **********************************************************/
2163:
2164: PROCEDURE find_parties (
2165: p_rule_id IN NUMBER,
2166: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2167: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2168: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2169: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2170: p_restrict_sql IN VARCHAR2,
2163:
2164: PROCEDURE find_parties (
2165: p_rule_id IN NUMBER,
2166: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2167: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2168: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2169: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2170: p_restrict_sql IN VARCHAR2,
2171: p_match_type IN VARCHAR2,
2164: PROCEDURE find_parties (
2165: p_rule_id IN NUMBER,
2166: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2167: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2168: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2169: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2170: p_restrict_sql IN VARCHAR2,
2171: p_match_type IN VARCHAR2,
2172: p_search_merged IN VARCHAR2,
2165: p_rule_id IN NUMBER,
2166: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2167: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2168: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2169: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2170: p_restrict_sql IN VARCHAR2,
2171: p_match_type IN VARCHAR2,
2172: p_search_merged IN VARCHAR2,
2173: p_dup_party_id IN NUMBER,
2202: l_TX46 VARCHAR2(2000);
2203: l_TX4 VARCHAR2(2000);
2204: l_TX11 VARCHAR2(2000);
2205: l_TX45 VARCHAR2(2000);
2206: H_SCORES HZ_PARTY_SEARCH.score_list;
2207: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2208: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2209:
2210: l_score NUMBER;
2203: l_TX4 VARCHAR2(2000);
2204: l_TX11 VARCHAR2(2000);
2205: l_TX45 VARCHAR2(2000);
2206: H_SCORES HZ_PARTY_SEARCH.score_list;
2207: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2208: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2209:
2210: l_score NUMBER;
2211: l_idx NUMBER;
2204: l_TX11 VARCHAR2(2000);
2205: l_TX45 VARCHAR2(2000);
2206: H_SCORES HZ_PARTY_SEARCH.score_list;
2207: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2208: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2209:
2210: l_score NUMBER;
2211: l_idx NUMBER;
2212: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
2396: /** Private procedure to acquire and score at party site level ***/
2397: 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
2398: l_party_id_idx NUMBER:=1;
2399: l_ctx_id NUMBER;
2400: h_ps_id HZ_PARTY_SEARCH.IDList;
2401: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2402: h_ps_score HZ_PARTY_SEARCH.IDList;
2403: detcnt NUMBER := 1;
2404: BEGIN
2397: 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
2398: l_party_id_idx NUMBER:=1;
2399: l_ctx_id NUMBER;
2400: h_ps_id HZ_PARTY_SEARCH.IDList;
2401: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2402: h_ps_score HZ_PARTY_SEARCH.IDList;
2403: detcnt NUMBER := 1;
2404: BEGIN
2405: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2398: l_party_id_idx NUMBER:=1;
2399: l_ctx_id NUMBER;
2400: h_ps_id HZ_PARTY_SEARCH.IDList;
2401: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2402: h_ps_score HZ_PARTY_SEARCH.IDList;
2403: detcnt NUMBER := 1;
2404: BEGIN
2405: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2406: 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);
2518: /** Private procedure to acquire and score at contact point level ***/
2519: 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
2520: l_party_id_idx NUMBER:=1;
2521: l_ctx_id NUMBER;
2522: h_ct_id HZ_PARTY_SEARCH.IDList;
2523: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2524: h_ct_score HZ_PARTY_SEARCH.IDList;
2525: detcnt NUMBER := 1;
2526: BEGIN
2519: 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
2520: l_party_id_idx NUMBER:=1;
2521: l_ctx_id NUMBER;
2522: h_ct_id HZ_PARTY_SEARCH.IDList;
2523: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2524: h_ct_score HZ_PARTY_SEARCH.IDList;
2525: detcnt NUMBER := 1;
2526: BEGIN
2527: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2520: l_party_id_idx NUMBER:=1;
2521: l_ctx_id NUMBER;
2522: h_ct_id HZ_PARTY_SEARCH.IDList;
2523: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2524: h_ct_score HZ_PARTY_SEARCH.IDList;
2525: detcnt NUMBER := 1;
2526: BEGIN
2527: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2528: 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);
2627: /** Private procedure to acquire and score at contact point level ***/
2628: 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
2629: l_party_id_idx NUMBER:=1;
2630: l_ctx_id NUMBER;
2631: h_cpt_id HZ_PARTY_SEARCH.IDList;
2632: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2633: h_cpt_score HZ_PARTY_SEARCH.IDList;
2634: detcnt NUMBER := 1;
2635: l_cpt_flag VARCHAR2(1) := 'N';
2628: 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
2629: l_party_id_idx NUMBER:=1;
2630: l_ctx_id NUMBER;
2631: h_cpt_id HZ_PARTY_SEARCH.IDList;
2632: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2633: h_cpt_score HZ_PARTY_SEARCH.IDList;
2634: detcnt NUMBER := 1;
2635: l_cpt_flag VARCHAR2(1) := 'N';
2636: is_a_match VARCHAR2(1) := 'Y';
2629: l_party_id_idx NUMBER:=1;
2630: l_ctx_id NUMBER;
2631: h_cpt_id HZ_PARTY_SEARCH.IDList;
2632: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2633: h_cpt_score HZ_PARTY_SEARCH.IDList;
2634: detcnt NUMBER := 1;
2635: l_cpt_flag VARCHAR2(1) := 'N';
2636: is_a_match VARCHAR2(1) := 'Y';
2637: BEGIN
3116: END find_parties;
3117:
3118: PROCEDURE find_persons (
3119: p_rule_id IN NUMBER,
3120: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3121: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3122: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3123: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3124: p_restrict_sql IN VARCHAR2,
3117:
3118: PROCEDURE find_persons (
3119: p_rule_id IN NUMBER,
3120: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3121: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3122: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3123: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3124: p_restrict_sql IN VARCHAR2,
3125: p_match_type IN VARCHAR2,
3118: PROCEDURE find_persons (
3119: p_rule_id IN NUMBER,
3120: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3121: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3122: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3123: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3124: p_restrict_sql IN VARCHAR2,
3125: p_match_type IN VARCHAR2,
3126: p_search_merged IN VARCHAR2,
3119: p_rule_id IN NUMBER,
3120: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3121: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3122: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3123: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3124: p_restrict_sql IN VARCHAR2,
3125: p_match_type IN VARCHAR2,
3126: p_search_merged IN VARCHAR2,
3127: p_dup_party_id IN NUMBER,
3156: l_TX46 VARCHAR2(2000);
3157: l_TX4 VARCHAR2(2000);
3158: l_TX11 VARCHAR2(2000);
3159: l_TX45 VARCHAR2(2000);
3160: H_SCORES HZ_PARTY_SEARCH.score_list;
3161: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3162: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3163:
3164: l_score NUMBER;
3157: l_TX4 VARCHAR2(2000);
3158: l_TX11 VARCHAR2(2000);
3159: l_TX45 VARCHAR2(2000);
3160: H_SCORES HZ_PARTY_SEARCH.score_list;
3161: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3162: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3163:
3164: l_score NUMBER;
3165: l_idx NUMBER;
3158: l_TX11 VARCHAR2(2000);
3159: l_TX45 VARCHAR2(2000);
3160: H_SCORES HZ_PARTY_SEARCH.score_list;
3161: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3162: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3163:
3164: l_score NUMBER;
3165: l_idx NUMBER;
3166: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
3398: /** Private procedure to acquire and score at party site level ***/
3399: 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
3400: l_party_id_idx NUMBER:=1;
3401: l_ctx_id NUMBER;
3402: h_ps_id HZ_PARTY_SEARCH.IDList;
3403: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3404: h_ps_score HZ_PARTY_SEARCH.IDList;
3405: detcnt NUMBER := 1;
3406: l_person_id NUMBER;
3399: 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
3400: l_party_id_idx NUMBER:=1;
3401: l_ctx_id NUMBER;
3402: h_ps_id HZ_PARTY_SEARCH.IDList;
3403: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3404: h_ps_score HZ_PARTY_SEARCH.IDList;
3405: detcnt NUMBER := 1;
3406: l_person_id NUMBER;
3407: BEGIN
3400: l_party_id_idx NUMBER:=1;
3401: l_ctx_id NUMBER;
3402: h_ps_id HZ_PARTY_SEARCH.IDList;
3403: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3404: h_ps_score HZ_PARTY_SEARCH.IDList;
3405: detcnt NUMBER := 1;
3406: l_person_id NUMBER;
3407: BEGIN
3408: SAVEPOINT eval_start;
3497: /** Private procedure to acquire and score at party site level ***/
3498: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3499: l_party_id_idx NUMBER:=1;
3500: l_ctx_id NUMBER;
3501: h_ct_id HZ_PARTY_SEARCH.IDList;
3502: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3503: h_ct_score HZ_PARTY_SEARCH.IDList;
3504: detcnt NUMBER := 1;
3505: l_person_id NUMBER;
3498: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3499: l_party_id_idx NUMBER:=1;
3500: l_ctx_id NUMBER;
3501: h_ct_id HZ_PARTY_SEARCH.IDList;
3502: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3503: h_ct_score HZ_PARTY_SEARCH.IDList;
3504: detcnt NUMBER := 1;
3505: l_person_id NUMBER;
3506: BEGIN
3499: l_party_id_idx NUMBER:=1;
3500: l_ctx_id NUMBER;
3501: h_ct_id HZ_PARTY_SEARCH.IDList;
3502: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3503: h_ct_score HZ_PARTY_SEARCH.IDList;
3504: detcnt NUMBER := 1;
3505: l_person_id NUMBER;
3506: BEGIN
3507: SAVEPOINT eval_start;
3555: /** Private procedure to acquire and score at contact point level ***/
3556: 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
3557: l_party_id_idx NUMBER:=1;
3558: l_ctx_id NUMBER;
3559: h_cpt_id HZ_PARTY_SEARCH.IDList;
3560: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3561: h_cpt_score HZ_PARTY_SEARCH.IDList;
3562: detcnt NUMBER := 1;
3563: l_person_id NUMBER;
3556: 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
3557: l_party_id_idx NUMBER:=1;
3558: l_ctx_id NUMBER;
3559: h_cpt_id HZ_PARTY_SEARCH.IDList;
3560: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3561: h_cpt_score HZ_PARTY_SEARCH.IDList;
3562: detcnt NUMBER := 1;
3563: l_person_id NUMBER;
3564: is_a_match VARCHAR2(1) := 'Y';
3557: l_party_id_idx NUMBER:=1;
3558: l_ctx_id NUMBER;
3559: h_cpt_id HZ_PARTY_SEARCH.IDList;
3560: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3561: h_cpt_score HZ_PARTY_SEARCH.IDList;
3562: detcnt NUMBER := 1;
3563: l_person_id NUMBER;
3564: is_a_match VARCHAR2(1) := 'Y';
3565: l_cpt_flag VARCHAR2(1) := 'N';
3968: END find_persons;
3969:
3970: PROCEDURE find_persons (
3971: p_rule_id IN NUMBER,
3972: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3973: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3974: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3975: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3976: p_restrict_sql IN VARCHAR2,
3969:
3970: PROCEDURE find_persons (
3971: p_rule_id IN NUMBER,
3972: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3973: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3974: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3975: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3976: p_restrict_sql IN VARCHAR2,
3977: p_match_type IN VARCHAR2,
3970: PROCEDURE find_persons (
3971: p_rule_id IN NUMBER,
3972: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3973: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3974: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3975: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3976: p_restrict_sql IN VARCHAR2,
3977: p_match_type IN VARCHAR2,
3978: p_search_merged IN VARCHAR2,
3971: p_rule_id IN NUMBER,
3972: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3973: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3974: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3975: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3976: p_restrict_sql IN VARCHAR2,
3977: p_match_type IN VARCHAR2,
3978: p_search_merged IN VARCHAR2,
3979: p_ins_details IN VARCHAR2,
3985: 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);
3986: END;
3987: PROCEDURE find_party_details (
3988: p_rule_id IN NUMBER,
3989: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3990: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3991: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3992: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3993: p_restrict_sql IN VARCHAR2,
3986: END;
3987: PROCEDURE find_party_details (
3988: p_rule_id IN NUMBER,
3989: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3990: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3991: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3992: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3993: p_restrict_sql IN VARCHAR2,
3994: p_match_type IN VARCHAR2,
3987: PROCEDURE find_party_details (
3988: p_rule_id IN NUMBER,
3989: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3990: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3991: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3992: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3993: p_restrict_sql IN VARCHAR2,
3994: p_match_type IN VARCHAR2,
3995: p_search_merged IN VARCHAR2,
3988: p_rule_id IN NUMBER,
3989: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3990: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3991: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3992: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3993: p_restrict_sql IN VARCHAR2,
3994: p_match_type IN VARCHAR2,
3995: p_search_merged IN VARCHAR2,
3996: x_search_ctx_id OUT NUMBER,
4045: x_dup_set_id OUT NUMBER,
4046: x_search_ctx_id OUT NUMBER,
4047: x_num_matches OUT NUMBER
4048: ) IS
4049: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4050: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4051: l_contact_list HZ_PARTY_SEARCH.contact_list;
4052: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4053: l_match_idx NUMBER;
4046: x_search_ctx_id OUT NUMBER,
4047: x_num_matches OUT NUMBER
4048: ) IS
4049: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4050: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4051: l_contact_list HZ_PARTY_SEARCH.contact_list;
4052: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4053: l_match_idx NUMBER;
4054:
4047: x_num_matches OUT NUMBER
4048: ) IS
4049: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4050: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4051: l_contact_list HZ_PARTY_SEARCH.contact_list;
4052: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4053: l_match_idx NUMBER;
4054:
4055: --Fix for bug 4417124
4048: ) IS
4049: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4050: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4051: l_contact_list HZ_PARTY_SEARCH.contact_list;
4052: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4053: l_match_idx NUMBER;
4054:
4055: --Fix for bug 4417124
4056: l_use_contact_addr_info BOOLEAN := TRUE;
4161: x_search_ctx_id OUT NUMBER,
4162: x_num_matches OUT NUMBER
4163: ) IS
4164:
4165: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4166: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4167: l_contact_list HZ_PARTY_SEARCH.contact_list;
4168: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4169: contact_point_ids HZ_PARTY_SEARCH.IDList;
4162: x_num_matches OUT NUMBER
4163: ) IS
4164:
4165: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4166: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4167: l_contact_list HZ_PARTY_SEARCH.contact_list;
4168: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4169: contact_point_ids HZ_PARTY_SEARCH.IDList;
4170: p_party_site_list HZ_PARTY_SEARCH.IDList;
4163: ) IS
4164:
4165: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4166: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4167: l_contact_list HZ_PARTY_SEARCH.contact_list;
4168: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4169: contact_point_ids HZ_PARTY_SEARCH.IDList;
4170: p_party_site_list HZ_PARTY_SEARCH.IDList;
4171: p_contact_ids HZ_PARTY_SEARCH.IDList;
4164:
4165: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4166: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4167: l_contact_list HZ_PARTY_SEARCH.contact_list;
4168: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4169: contact_point_ids HZ_PARTY_SEARCH.IDList;
4170: p_party_site_list HZ_PARTY_SEARCH.IDList;
4171: p_contact_ids HZ_PARTY_SEARCH.IDList;
4172: l_match_idx NUMBER;
4165: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4166: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4167: l_contact_list HZ_PARTY_SEARCH.contact_list;
4168: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4169: contact_point_ids HZ_PARTY_SEARCH.IDList;
4170: p_party_site_list HZ_PARTY_SEARCH.IDList;
4171: p_contact_ids HZ_PARTY_SEARCH.IDList;
4172: l_match_idx NUMBER;
4173: cursor get_cpts_for_party_sites is select contact_point_id
4166: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4167: l_contact_list HZ_PARTY_SEARCH.contact_list;
4168: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4169: contact_point_ids HZ_PARTY_SEARCH.IDList;
4170: p_party_site_list HZ_PARTY_SEARCH.IDList;
4171: p_contact_ids HZ_PARTY_SEARCH.IDList;
4172: l_match_idx NUMBER;
4173: cursor get_cpts_for_party_sites is select contact_point_id
4174: from hz_contact_points
4167: l_contact_list HZ_PARTY_SEARCH.contact_list;
4168: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4169: contact_point_ids HZ_PARTY_SEARCH.IDList;
4170: p_party_site_list HZ_PARTY_SEARCH.IDList;
4171: p_contact_ids HZ_PARTY_SEARCH.IDList;
4172: l_match_idx NUMBER;
4173: cursor get_cpts_for_party_sites is select contact_point_id
4174: from hz_contact_points
4175: where owner_table_name = 'HZ_PARTY_SITES'
4196:
4197: get_search_criteria (
4198: null,
4199: p_party_site_list,
4200: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4201: contact_point_ids,
4202: l_party_search_rec,
4203: l_party_site_list,
4204: l_contact_list,
4246: x_search_ctx_id OUT NUMBER,
4247: x_num_matches OUT NUMBER
4248: ) IS
4249:
4250: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4251: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4252: l_contact_list HZ_PARTY_SEARCH.contact_list;
4253: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4254: contact_point_ids HZ_PARTY_SEARCH.IDList;
4247: x_num_matches OUT NUMBER
4248: ) IS
4249:
4250: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4251: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4252: l_contact_list HZ_PARTY_SEARCH.contact_list;
4253: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4254: contact_point_ids HZ_PARTY_SEARCH.IDList;
4255: p_party_site_list HZ_PARTY_SEARCH.IDList;
4248: ) IS
4249:
4250: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4251: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4252: l_contact_list HZ_PARTY_SEARCH.contact_list;
4253: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4254: contact_point_ids HZ_PARTY_SEARCH.IDList;
4255: p_party_site_list HZ_PARTY_SEARCH.IDList;
4256: p_contact_ids HZ_PARTY_SEARCH.IDList;
4249:
4250: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4251: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4252: l_contact_list HZ_PARTY_SEARCH.contact_list;
4253: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4254: contact_point_ids HZ_PARTY_SEARCH.IDList;
4255: p_party_site_list HZ_PARTY_SEARCH.IDList;
4256: p_contact_ids HZ_PARTY_SEARCH.IDList;
4257: l_match_idx NUMBER;
4250: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4251: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4252: l_contact_list HZ_PARTY_SEARCH.contact_list;
4253: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4254: contact_point_ids HZ_PARTY_SEARCH.IDList;
4255: p_party_site_list HZ_PARTY_SEARCH.IDList;
4256: p_contact_ids HZ_PARTY_SEARCH.IDList;
4257: l_match_idx NUMBER;
4258: cursor get_cpt_for_contact_id is select contact_point_id
4251: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4252: l_contact_list HZ_PARTY_SEARCH.contact_list;
4253: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4254: contact_point_ids HZ_PARTY_SEARCH.IDList;
4255: p_party_site_list HZ_PARTY_SEARCH.IDList;
4256: p_contact_ids HZ_PARTY_SEARCH.IDList;
4257: l_match_idx NUMBER;
4258: cursor get_cpt_for_contact_id is select contact_point_id
4259: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4252: l_contact_list HZ_PARTY_SEARCH.contact_list;
4253: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4254: contact_point_ids HZ_PARTY_SEARCH.IDList;
4255: p_party_site_list HZ_PARTY_SEARCH.IDList;
4256: p_contact_ids HZ_PARTY_SEARCH.IDList;
4257: l_match_idx NUMBER;
4258: cursor get_cpt_for_contact_id is select contact_point_id
4259: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4260: where a.party_relationship_id = b.relationship_id
4283: CLOSE get_cpt_for_contact_id;
4284:
4285: get_search_criteria (
4286: null,
4287: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4288: p_contact_ids,
4289: contact_point_ids,
4290: l_party_search_rec,
4291: l_party_site_list,
4334: p_match_type IN VARCHAR2,
4335: x_search_ctx_id OUT NUMBER,
4336: x_num_matches OUT NUMBER
4337: ) IS
4338: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4339: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4340: l_contact_list HZ_PARTY_SEARCH.contact_list;
4341: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4342: contact_point_ids HZ_PARTY_SEARCH.IDList;
4335: x_search_ctx_id OUT NUMBER,
4336: x_num_matches OUT NUMBER
4337: ) IS
4338: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4339: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4340: l_contact_list HZ_PARTY_SEARCH.contact_list;
4341: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4342: contact_point_ids HZ_PARTY_SEARCH.IDList;
4343: p_party_site_list HZ_PARTY_SEARCH.IDList;
4336: x_num_matches OUT NUMBER
4337: ) IS
4338: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4339: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4340: l_contact_list HZ_PARTY_SEARCH.contact_list;
4341: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4342: contact_point_ids HZ_PARTY_SEARCH.IDList;
4343: p_party_site_list HZ_PARTY_SEARCH.IDList;
4344: p_contact_ids HZ_PARTY_SEARCH.IDList;
4337: ) IS
4338: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4339: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4340: l_contact_list HZ_PARTY_SEARCH.contact_list;
4341: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4342: contact_point_ids HZ_PARTY_SEARCH.IDList;
4343: p_party_site_list HZ_PARTY_SEARCH.IDList;
4344: p_contact_ids HZ_PARTY_SEARCH.IDList;
4345: l_match_idx NUMBER;
4338: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4339: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4340: l_contact_list HZ_PARTY_SEARCH.contact_list;
4341: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4342: contact_point_ids HZ_PARTY_SEARCH.IDList;
4343: p_party_site_list HZ_PARTY_SEARCH.IDList;
4344: p_contact_ids HZ_PARTY_SEARCH.IDList;
4345: l_match_idx NUMBER;
4346:
4339: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4340: l_contact_list HZ_PARTY_SEARCH.contact_list;
4341: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4342: contact_point_ids HZ_PARTY_SEARCH.IDList;
4343: p_party_site_list HZ_PARTY_SEARCH.IDList;
4344: p_contact_ids HZ_PARTY_SEARCH.IDList;
4345: l_match_idx NUMBER;
4346:
4347: BEGIN
4340: l_contact_list HZ_PARTY_SEARCH.contact_list;
4341: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4342: contact_point_ids HZ_PARTY_SEARCH.IDList;
4343: p_party_site_list HZ_PARTY_SEARCH.IDList;
4344: p_contact_ids HZ_PARTY_SEARCH.IDList;
4345: l_match_idx NUMBER;
4346:
4347: BEGIN
4348: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4357: END IF;
4358: contact_point_ids(1) := p_contact_point_id;
4359: get_search_criteria (
4360: null,
4361: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4362: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4363: contact_point_ids,
4364: l_party_search_rec,
4365: l_party_site_list,
4358: contact_point_ids(1) := p_contact_point_id;
4359: get_search_criteria (
4360: null,
4361: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4362: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4363: contact_point_ids,
4364: l_party_search_rec,
4365: l_party_site_list,
4366: l_contact_list,
4511: ) IS
4512: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4513: l_match_idx NUMBER;
4514: AttrVals AttrList;
4515: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4516: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4517: l_contact_list HZ_PARTY_SEARCH.contact_list;
4518: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4519: l_dup_set_id NUMBER;
4512: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4513: l_match_idx NUMBER;
4514: AttrVals AttrList;
4515: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4516: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4517: l_contact_list HZ_PARTY_SEARCH.contact_list;
4518: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4519: l_dup_set_id NUMBER;
4520: l_idx NUMBER;
4513: l_match_idx NUMBER;
4514: AttrVals AttrList;
4515: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4516: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4517: l_contact_list HZ_PARTY_SEARCH.contact_list;
4518: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4519: l_dup_set_id NUMBER;
4520: l_idx NUMBER;
4521: l_cpt_type VARCHAR2(255);
4514: AttrVals AttrList;
4515: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4516: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4517: l_contact_list HZ_PARTY_SEARCH.contact_list;
4518: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4519: l_dup_set_id NUMBER;
4520: l_idx NUMBER;
4521: l_cpt_type VARCHAR2(255);
4522: FIRST BOOLEAN := TRUE;
4693:
4694: PROCEDURE get_matching_party_sites (
4695: p_rule_id IN NUMBER,
4696: p_party_id IN NUMBER,
4697: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4698: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4699: p_restrict_sql IN VARCHAR2,
4700: p_match_type IN VARCHAR2,
4701: p_dup_party_site_id IN NUMBER,
4694: PROCEDURE get_matching_party_sites (
4695: p_rule_id IN NUMBER,
4696: p_party_id IN NUMBER,
4697: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4698: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4699: p_restrict_sql IN VARCHAR2,
4700: p_match_type IN VARCHAR2,
4701: p_dup_party_site_id IN NUMBER,
4702: x_search_ctx_id OUT NUMBER,
4722:
4723: l_TX2 VARCHAR2(2000);
4724: l_TX4 VARCHAR2(2000);
4725: l_TX11 VARCHAR2(2000);
4726: H_SCORES HZ_PARTY_SEARCH.score_list;
4727:
4728: l_score NUMBER;
4729: l_idx NUMBER;
4730: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
4753: defps NUMBER :=0;
4754: defct NUMBER :=0;
4755: defcpt NUMBER :=0;
4756: l_index NUMBER;
4757: l_match_ps_list HZ_PARTY_SEARCH.IDList;
4758: l_cnt NUMBER:=1;
4759:
4760:
4761: BEGIN
4777: l_match_str := ' AND ';
4778: ELSIF p_match_type = 'OR' THEN
4779: l_match_str := ' OR ';
4780: END IF;
4781: 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);
4782: g_score_until_thresh := false;
4783: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4784:
4785: -- ************************************************************
4785: -- ************************************************************
4786: -- STEP 2. Setup of intermedia query strings for Acquisition query
4787: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4788: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4789: 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);
4790:
4791: -- Setup Search Context ID
4792: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4793: x_search_ctx_id := l_search_ctx_id;
4928:
4929: PROCEDURE get_matching_contacts (
4930: p_rule_id IN NUMBER,
4931: p_party_id IN NUMBER,
4932: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4933: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4934: p_restrict_sql IN VARCHAR2,
4935: p_match_type IN VARCHAR2,
4936: p_dup_contact_id IN NUMBER,
4929: PROCEDURE get_matching_contacts (
4930: p_rule_id IN NUMBER,
4931: p_party_id IN NUMBER,
4932: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4933: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4934: p_restrict_sql IN VARCHAR2,
4935: p_match_type IN VARCHAR2,
4936: p_dup_contact_id IN NUMBER,
4937: x_search_ctx_id OUT NUMBER,
4955: FIRST BOOLEAN;
4956: l_search_ctx_id NUMBER; -- Generated Search Context ID
4957:
4958: l_TX2 VARCHAR2(2000);
4959: H_SCORES HZ_PARTY_SEARCH.score_list;
4960:
4961: l_score NUMBER;
4962: l_idx NUMBER;
4963: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
4983: l_party_max_score NUMBER;
4984: l_ps_max_score NUMBER;
4985: l_contact_max_score NUMBER;
4986: l_cpt_max_score NUMBER;
4987: l_match_contact_list HZ_PARTY_SEARCH.IDList;
4988: l_cnt NUMBER:=1;
4989:
4990:
4991: BEGIN
5007: l_match_str := ' AND ';
5008: ELSIF p_match_type = 'OR' THEN
5009: l_match_str := ' OR ';
5010: END IF;
5011: 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);
5012: g_score_until_thresh := false;
5013: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5014:
5015: -- ************************************************************
5015: -- ************************************************************
5016: -- STEP 2. Setup of intermedia query strings for Acquisition query
5017: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5018: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5019: 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);
5020:
5021: -- Setup Search Context ID
5022: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5023: x_search_ctx_id := l_search_ctx_id;
5154:
5155: PROCEDURE get_matching_contact_points (
5156: p_rule_id IN NUMBER,
5157: p_party_id IN NUMBER,
5158: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5159: p_restrict_sql IN VARCHAR2,
5160: p_match_type IN VARCHAR2,
5161: p_dup_contact_point_id IN NUMBER,
5162: x_search_ctx_id OUT NUMBER,
5184: l_cpt_ps_id NUMBER;
5185: l_cpt_contact_id NUMBER;
5186: l_contact_pt_id NUMBER;
5187: l_cpt_type VARCHAR2(100);
5188: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5189: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5190: H_SCORE HZ_PARTY_SEARCH.IDList;
5191:
5192: cnt NUMBER :=0;
5185: l_cpt_contact_id NUMBER;
5186: l_contact_pt_id NUMBER;
5187: l_cpt_type VARCHAR2(100);
5188: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5189: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5190: H_SCORE HZ_PARTY_SEARCH.IDList;
5191:
5192: cnt NUMBER :=0;
5193: l_party_max_score NUMBER;
5186: l_contact_pt_id NUMBER;
5187: l_cpt_type VARCHAR2(100);
5188: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5189: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5190: H_SCORE HZ_PARTY_SEARCH.IDList;
5191:
5192: cnt NUMBER :=0;
5193: l_party_max_score NUMBER;
5194: l_ps_max_score NUMBER;
5215: l_match_str := ' AND ';
5216: ELSIF p_match_type = 'OR' THEN
5217: l_match_str := ' OR ';
5218: END IF;
5219: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5220: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5221: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5222: g_score_until_thresh := false;
5223: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5216: ELSIF p_match_type = 'OR' THEN
5217: l_match_str := ' OR ';
5218: END IF;
5219: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5220: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5221: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5222: g_score_until_thresh := false;
5223: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5224:
5224:
5225: -- ************************************************************
5226: -- STEP 2. Setup of intermedia query strings for Acquisition query
5227: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5228: 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);
5229:
5230: -- Setup Search Context ID
5231: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5232: x_search_ctx_id := l_search_ctx_id;
5299:
5300: PROCEDURE get_score_details (
5301: p_rule_id IN NUMBER,
5302: p_party_id IN NUMBER,
5303: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5304: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5305: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5306: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5307: x_search_ctx_id IN OUT NUMBER
5300: PROCEDURE get_score_details (
5301: p_rule_id IN NUMBER,
5302: p_party_id IN NUMBER,
5303: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5304: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5305: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5306: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5307: x_search_ctx_id IN OUT NUMBER
5308: ) IS
5301: p_rule_id IN NUMBER,
5302: p_party_id IN NUMBER,
5303: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5304: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5305: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5306: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5307: x_search_ctx_id IN OUT NUMBER
5308: ) IS
5309:
5302: p_party_id IN NUMBER,
5303: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5304: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5305: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5306: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5307: x_search_ctx_id IN OUT NUMBER
5308: ) IS
5309:
5310: -- Strings to hold the generated Intermedia query strings
5331: l_TX11 VARCHAR2(2000);
5332: l_max_TX11 VARCHAR2(2000);
5333: l_TX45 VARCHAR2(2000);
5334: l_max_TX45 VARCHAR2(2000);
5335: H_SCORES HZ_PARTY_SEARCH.score_list;
5336:
5337: l_score NUMBER;
5338: l_match_idx NUMBER;
5339: l_idx NUMBER;
5500: ************************************************/
5501:
5502: PROCEDURE get_party_for_search (
5503: p_party_id IN NUMBER,
5504: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5505: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5506: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5507: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5508: ) IS
5501:
5502: PROCEDURE get_party_for_search (
5503: p_party_id IN NUMBER,
5504: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5505: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5506: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5507: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5508: ) IS
5509: l_party_id NUMBER;
5502: PROCEDURE get_party_for_search (
5503: p_party_id IN NUMBER,
5504: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5505: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5506: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5507: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5508: ) IS
5509: l_party_id NUMBER;
5510: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5503: p_party_id IN NUMBER,
5504: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5505: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5506: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5507: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5508: ) IS
5509: l_party_id NUMBER;
5510: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5511: l_contact_ids HZ_PARTY_SEARCH.IDList;
5506: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5507: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5508: ) IS
5509: l_party_id NUMBER;
5510: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5511: l_contact_ids HZ_PARTY_SEARCH.IDList;
5512: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5513: ps NUMBER :=1;
5514: cpt NUMBER :=1;
5507: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5508: ) IS
5509: l_party_id NUMBER;
5510: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5511: l_contact_ids HZ_PARTY_SEARCH.IDList;
5512: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5513: ps NUMBER :=1;
5514: cpt NUMBER :=1;
5515: ct NUMBER :=1;
5508: ) IS
5509: l_party_id NUMBER;
5510: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5511: l_contact_ids HZ_PARTY_SEARCH.IDList;
5512: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5513: ps NUMBER :=1;
5514: cpt NUMBER :=1;
5515: ct NUMBER :=1;
5516: l_use_contact_info varchar2(1);
5604: END get_party_for_search;
5605:
5606: PROCEDURE get_search_criteria (
5607: p_party_id IN NUMBER,
5608: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5609: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5610: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5611: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5612: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5605:
5606: PROCEDURE get_search_criteria (
5607: p_party_id IN NUMBER,
5608: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5609: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5610: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5611: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5612: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5613: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5606: PROCEDURE get_search_criteria (
5607: p_party_id IN NUMBER,
5608: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5609: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5610: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5611: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5612: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5613: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5614: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5607: p_party_id IN NUMBER,
5608: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5609: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5610: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5611: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5612: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5613: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5614: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5615: ) IS
5608: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5609: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5610: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5611: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5612: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5613: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5614: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5615: ) IS
5616: BEGIN
5609: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5610: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5611: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5612: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5613: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5614: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5615: ) IS
5616: BEGIN
5617:
5610: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5611: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5612: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5613: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5614: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5615: ) IS
5616: BEGIN
5617:
5618: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5656: ************************************************/
5657:
5658: PROCEDURE get_party_rec (
5659: p_party_id IN NUMBER,
5660: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type
5661: ) IS
5662: l_party_type VARCHAR2(255);
5663: BEGIN
5664:
5717: the party site search record structure
5718: ************************************************/
5719:
5720: PROCEDURE get_party_site_rec (
5721: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5722: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5723: ) IS
5724: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5725: SELECT party_site_id
5718: ************************************************/
5719:
5720: PROCEDURE get_party_site_rec (
5721: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5722: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5723: ) IS
5724: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5725: SELECT party_site_id
5726: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
5773: the contact search record structure
5774: ************************************************/
5775:
5776: PROCEDURE get_contact_rec (
5777: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5778: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5779: ) IS
5780: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5781: SELECT org_contact_id
5774: ************************************************/
5775:
5776: PROCEDURE get_contact_rec (
5777: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5778: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5779: ) IS
5780: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5781: SELECT org_contact_id
5782: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
5829: the contact point search record structure
5830: ************************************************/
5831:
5832: PROCEDURE get_contact_point_rec (
5833: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5834: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5835: ) IS
5836:
5837: -- Cursor to fetch primary contact points for party
5830: ************************************************/
5831:
5832: PROCEDURE get_contact_point_rec (
5833: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5834: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5835: ) IS
5836:
5837: -- Cursor to fetch primary contact points for party
5838: CURSOR c_cpts(cp_contact_point_id NUMBER) IS
5886: ************************************************/
5887:
5888: PROCEDURE MAP_PARTY_REC(
5889: p_search_ctx IN BOOLEAN,
5890: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5891: x_entered_max_score OUT NUMBER,
5892: x_stage_rec IN OUT NOCOPY HZ_PARTY_STAGE.party_stage_rec_type
5893: ) IS
5894: tmp VARCHAR2(4000);
6005: ************************************************/
6006:
6007: PROCEDURE MAP_PARTY_SITE_REC(
6008: p_search_ctx IN BOOLEAN,
6009: p_search_list IN HZ_PARTY_SEARCH.party_site_list,
6010: x_entered_max_score OUT NUMBER,
6011: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.party_site_stage_list
6012: ) IS
6013: l_current_max_score NUMBER;
6100: ************************************************/
6101:
6102: PROCEDURE MAP_CONTACT_REC(
6103: p_search_ctx IN BOOLEAN,
6104: p_search_list IN HZ_PARTY_SEARCH.contact_list,
6105: x_entered_max_score OUT NUMBER,
6106: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_stage_list
6107: ) IS
6108: l_current_max_score NUMBER;
6142: ************************************************/
6143:
6144: PROCEDURE MAP_CONTACT_POINT_REC(
6145: p_search_ctx IN BOOLEAN,
6146: p_search_list IN HZ_PARTY_SEARCH.contact_point_list,
6147: x_entered_max_score OUT NUMBER,
6148: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_pt_stage_list
6149: ) IS
6150: l_current_max_score NUMBER;
6233: 2. Contact Point Type is not null for each condition
6234: ************************************************/
6235:
6236: FUNCTION check_prim_cond(
6237: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6238: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6239: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6240: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6241: RETURN BOOLEAN IS
6234: ************************************************/
6235:
6236: FUNCTION check_prim_cond(
6237: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6238: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6239: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6240: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6241: RETURN BOOLEAN IS
6242: BEGIN
6235:
6236: FUNCTION check_prim_cond(
6237: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6238: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6239: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6240: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6241: RETURN BOOLEAN IS
6242: BEGIN
6243: FOR I IN 1..p_contact_point_list.COUNT LOOP
6236: FUNCTION check_prim_cond(
6237: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6238: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6239: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6240: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6241: RETURN BOOLEAN IS
6242: BEGIN
6243: FOR I IN 1..p_contact_point_list.COUNT LOOP
6244: IF p_contact_point_list(I).CONTACT_POINT_TYPE IS NULL AND (
6291: has party site criteria.
6292: ************************************************/
6293:
6294: PROCEDURE check_party_site_cond(
6295: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6296: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6297: x_secondary OUT BOOLEAN,
6298: x_primary OUT BOOLEAN
6299: ) IS
6292: ************************************************/
6293:
6294: PROCEDURE check_party_site_cond(
6295: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6296: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6297: x_secondary OUT BOOLEAN,
6298: x_primary OUT BOOLEAN
6299: ) IS
6300: BEGIN
6345: has contact criteria.
6346: ************************************************/
6347:
6348: PROCEDURE check_contact_cond(
6349: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6350: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6351: x_secondary OUT BOOLEAN,
6352: x_primary OUT BOOLEAN
6353: ) IS
6346: ************************************************/
6347:
6348: PROCEDURE check_contact_cond(
6349: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6350: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6351: x_secondary OUT BOOLEAN,
6352: x_primary OUT BOOLEAN
6353: ) IS
6354: BEGIN
6391: has valid contact point criteria.
6392: ************************************************/
6393:
6394: PROCEDURE check_contact_point_cond(
6395: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6396: x_secondary OUT BOOLEAN,
6397: x_primary OUT BOOLEAN
6398: ) IS
6399: BEGIN