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;
418: PROCEDURE INSERT_PARTY_SCORE (
419: p_party_id IN NUMBER
420: ,p_record_id IN NUMBER
421: ,p_search_ctx_id IN NUMBER
422: ,p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
423: ,p_stage_rec IN HZ_PARTY_STAGE.party_stage_rec_type
424: ,p_table_TX2 VARCHAR2
425: ,p_table_TX8 VARCHAR2
426: ,p_table_TX4 VARCHAR2
500: PROCEDURE INSERT_PARTY_SITES_SCORE (
501: p_party_id IN NUMBER
502: ,p_record_id IN NUMBER
503: ,p_search_ctx_id IN NUMBER
504: ,p_search_rec IN HZ_PARTY_SEARCH.party_site_search_rec_type
505: ,p_stage_rec IN HZ_PARTY_STAGE.party_site_stage_rec_type
506: ,p_table_TX3 VARCHAR2
507: ,p_table_TX4 VARCHAR2
508: ,p_table_TX11 VARCHAR2
575: PROCEDURE INSERT_CONTACTS_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_search_rec_type
580: ,p_stage_rec IN HZ_PARTY_STAGE.contact_stage_rec_type
581: ,p_idx IN NUMBER) IS
582: l_current_score NUMBER:=0;
583: l_score NUMBER;
591: PROCEDURE INSERT_CONTACT_POINTS_SCORE (
592: p_party_id IN NUMBER
593: ,p_record_id IN NUMBER
594: ,p_search_ctx_id IN NUMBER
595: ,p_search_rec IN HZ_PARTY_SEARCH.contact_point_search_rec_type
596: ,p_stage_rec IN HZ_PARTY_STAGE.contact_pt_stage_rec_type
597: ,p_table_TX1 VARCHAR2
598: ,p_table_TX6 VARCHAR2
599: ,p_table_TX5 VARCHAR2
669: for the PARTY Entity
670: ************************************************/
671:
672: PROCEDURE POP_PARTY_COND_REC(
673: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
674: ) IS
675: BEGIN
676: NULL ;
677: EXCEPTION
689: for the PARTY_SITES Entity
690: ************************************************/
691:
692: PROCEDURE POP_PARTY_SITES_COND_REC(
693: p_search_list IN HZ_PARTY_SEARCH.party_site_list
694: ) IS
695: BEGIN
696: NULL ;
697: EXCEPTION
709: for the CONTACTS Entity
710: ************************************************/
711:
712: PROCEDURE POP_CONTACTS_COND_REC(
713: p_search_list IN HZ_PARTY_SEARCH.contact_list
714: ) IS
715: BEGIN
716: NULL ;
717: EXCEPTION
729: for the CONTACT_POINTS Entity
730: ************************************************/
731:
732: PROCEDURE POP_CONTACT_POINTS_COND_REC(
733: p_search_list IN HZ_PARTY_SEARCH.contact_point_list
734: ) IS
735: BEGIN
736: NULL ;
737: EXCEPTION
743: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
744: END;
745:
746: PROCEDURE init_score_context (
747: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
748: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
749: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
750: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
751: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
744: END;
745:
746: PROCEDURE init_score_context (
747: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
748: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
749: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
750: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
751: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
752: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
745:
746: PROCEDURE init_score_context (
747: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
748: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
749: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
750: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
751: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
752: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
753: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
746: PROCEDURE init_score_context (
747: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
748: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
749: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
750: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
751: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
752: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
753: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
754: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
747: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
748: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
749: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
750: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
751: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
752: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
753: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
754: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
755: ) IS
748: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
749: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
750: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
751: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
752: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
753: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
754: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
755: ) IS
756: l_dummy NUMBER;
749: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
750: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
751: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
752: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
753: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
754: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
755: ) IS
756: l_dummy NUMBER;
757: BEGIN
750: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
751: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
752: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
753: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
754: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
755: ) IS
756: l_dummy NUMBER;
757: BEGIN
758: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
770: MAP_CONTACT_POINT_REC(FALSE,p_contact_point_list, l_dummy, g_contact_pt_stage_list);
771:
772: END;
773: FUNCTION init_search(
774: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
775: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
776: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
777: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
778: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
771:
772: END;
773: FUNCTION init_search(
774: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
775: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
776: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
777: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
778: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
779: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
772: END;
773: FUNCTION init_search(
774: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
775: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
776: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
777: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
778: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
779: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
780: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
773: FUNCTION init_search(
774: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
775: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
776: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
777: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
778: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
779: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
780: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
781: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
774: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
775: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
776: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
777: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
778: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
779: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
780: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
781: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
782: p_match_type IN VARCHAR2,
775: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
776: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
777: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
778: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
779: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
780: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
781: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
782: p_match_type IN VARCHAR2,
783: x_party_max_score OUT NUMBER,
776: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
777: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
778: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
779: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
780: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
781: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
782: p_match_type IN VARCHAR2,
783: x_party_max_score OUT NUMBER,
784: x_ps_max_score OUT NUMBER,
777: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
778: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
779: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
780: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
781: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
782: p_match_type IN VARCHAR2,
783: x_party_max_score OUT NUMBER,
784: x_ps_max_score OUT NUMBER,
785: x_contact_max_score OUT NUMBER,
789: l_ps_entered_max_score NUMBER:=0;
790: l_ct_entered_max_score NUMBER:=0;
791: l_cpt_entered_max_score NUMBER:=0;
792: vlist vlisttype;
793: maxscore HZ_PARTY_SEARCH.IDList;
794: l_name VARCHAR2(200);
795: l_idx NUMBER;
796: l_num NUMBER;
797: total NUMBER;
2121: p_party_id NUMBER,
2122: p_party_site_id NUMBER,
2123: p_org_contact_id NUMBER,
2124: p_contact_point_id NUMBER)
2125: RETURN HZ_PARTY_SEARCH.score_rec IS
2126: l_score_rec HZ_PARTY_SEARCH.score_rec;
2127: BEGIN
2128: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2129: 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);
2122: p_party_site_id NUMBER,
2123: p_org_contact_id NUMBER,
2124: p_contact_point_id NUMBER)
2125: RETURN HZ_PARTY_SEARCH.score_rec IS
2126: l_score_rec HZ_PARTY_SEARCH.score_rec;
2127: BEGIN
2128: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2129: 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: 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);
2154: **********************************************************/
2155:
2156: PROCEDURE find_parties (
2157: p_rule_id IN NUMBER,
2158: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2159: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2160: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2161: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2162: p_restrict_sql IN VARCHAR2,
2155:
2156: PROCEDURE find_parties (
2157: p_rule_id IN NUMBER,
2158: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2159: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2160: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2161: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2162: p_restrict_sql IN VARCHAR2,
2163: p_match_type IN VARCHAR2,
2156: PROCEDURE find_parties (
2157: p_rule_id IN NUMBER,
2158: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2159: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2160: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2161: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2162: p_restrict_sql IN VARCHAR2,
2163: p_match_type IN VARCHAR2,
2164: p_search_merged IN VARCHAR2,
2157: p_rule_id IN NUMBER,
2158: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2159: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2160: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2161: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2162: p_restrict_sql IN VARCHAR2,
2163: p_match_type IN VARCHAR2,
2164: p_search_merged IN VARCHAR2,
2165: p_dup_party_id IN NUMBER,
2198: l_TX11 VARCHAR2(2000);
2199: l_TX5 VARCHAR2(2000);
2200: l_TX8 VARCHAR2(2000);
2201: l_TX6 VARCHAR2(2000);
2202: H_SCORES HZ_PARTY_SEARCH.score_list;
2203: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2204: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2205:
2206: l_score NUMBER;
2199: l_TX5 VARCHAR2(2000);
2200: l_TX8 VARCHAR2(2000);
2201: l_TX6 VARCHAR2(2000);
2202: H_SCORES HZ_PARTY_SEARCH.score_list;
2203: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2204: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2205:
2206: l_score NUMBER;
2207: l_idx NUMBER;
2200: l_TX8 VARCHAR2(2000);
2201: l_TX6 VARCHAR2(2000);
2202: H_SCORES HZ_PARTY_SEARCH.score_list;
2203: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2204: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2205:
2206: l_score NUMBER;
2207: l_idx NUMBER;
2208: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
2392: /** Private procedure to acquire and score at party site level ***/
2393: 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
2394: l_party_id_idx NUMBER:=1;
2395: l_ctx_id NUMBER;
2396: h_ps_id HZ_PARTY_SEARCH.IDList;
2397: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2398: h_ps_score HZ_PARTY_SEARCH.IDList;
2399: detcnt NUMBER := 1;
2400: BEGIN
2393: 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
2394: l_party_id_idx NUMBER:=1;
2395: l_ctx_id NUMBER;
2396: h_ps_id HZ_PARTY_SEARCH.IDList;
2397: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2398: h_ps_score HZ_PARTY_SEARCH.IDList;
2399: detcnt NUMBER := 1;
2400: BEGIN
2401: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2394: l_party_id_idx NUMBER:=1;
2395: l_ctx_id NUMBER;
2396: h_ps_id HZ_PARTY_SEARCH.IDList;
2397: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2398: h_ps_score HZ_PARTY_SEARCH.IDList;
2399: detcnt NUMBER := 1;
2400: BEGIN
2401: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2402: 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);
2514: /** Private procedure to acquire and score at contact point level ***/
2515: 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
2516: l_party_id_idx NUMBER:=1;
2517: l_ctx_id NUMBER;
2518: h_ct_id HZ_PARTY_SEARCH.IDList;
2519: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2520: h_ct_score HZ_PARTY_SEARCH.IDList;
2521: detcnt NUMBER := 1;
2522: BEGIN
2515: 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
2516: l_party_id_idx NUMBER:=1;
2517: l_ctx_id NUMBER;
2518: h_ct_id HZ_PARTY_SEARCH.IDList;
2519: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2520: h_ct_score HZ_PARTY_SEARCH.IDList;
2521: detcnt NUMBER := 1;
2522: BEGIN
2523: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2516: l_party_id_idx NUMBER:=1;
2517: l_ctx_id NUMBER;
2518: h_ct_id HZ_PARTY_SEARCH.IDList;
2519: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2520: h_ct_score HZ_PARTY_SEARCH.IDList;
2521: detcnt NUMBER := 1;
2522: BEGIN
2523: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2524: 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);
2623: /** Private procedure to acquire and score at contact point level ***/
2624: 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
2625: l_party_id_idx NUMBER:=1;
2626: l_ctx_id NUMBER;
2627: h_cpt_id HZ_PARTY_SEARCH.IDList;
2628: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2629: h_cpt_score HZ_PARTY_SEARCH.IDList;
2630: detcnt NUMBER := 1;
2631: l_cpt_flag VARCHAR2(1) := 'N';
2624: 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
2625: l_party_id_idx NUMBER:=1;
2626: l_ctx_id NUMBER;
2627: h_cpt_id HZ_PARTY_SEARCH.IDList;
2628: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2629: h_cpt_score HZ_PARTY_SEARCH.IDList;
2630: detcnt NUMBER := 1;
2631: l_cpt_flag VARCHAR2(1) := 'N';
2632: is_a_match VARCHAR2(1) := 'Y';
2625: l_party_id_idx NUMBER:=1;
2626: l_ctx_id NUMBER;
2627: h_cpt_id HZ_PARTY_SEARCH.IDList;
2628: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2629: h_cpt_score HZ_PARTY_SEARCH.IDList;
2630: detcnt NUMBER := 1;
2631: l_cpt_flag VARCHAR2(1) := 'N';
2632: is_a_match VARCHAR2(1) := 'Y';
2633: BEGIN
3074: END find_parties;
3075:
3076: PROCEDURE find_persons (
3077: p_rule_id IN NUMBER,
3078: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3079: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3080: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3081: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3082: p_restrict_sql IN VARCHAR2,
3075:
3076: PROCEDURE find_persons (
3077: p_rule_id IN NUMBER,
3078: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3079: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3080: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3081: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3082: p_restrict_sql IN VARCHAR2,
3083: p_match_type IN VARCHAR2,
3076: PROCEDURE find_persons (
3077: p_rule_id IN NUMBER,
3078: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3079: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3080: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3081: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3082: p_restrict_sql IN VARCHAR2,
3083: p_match_type IN VARCHAR2,
3084: p_search_merged IN VARCHAR2,
3077: p_rule_id IN NUMBER,
3078: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3079: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3080: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3081: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3082: p_restrict_sql IN VARCHAR2,
3083: p_match_type IN VARCHAR2,
3084: p_search_merged IN VARCHAR2,
3085: p_dup_party_id IN NUMBER,
3118: l_TX11 VARCHAR2(2000);
3119: l_TX5 VARCHAR2(2000);
3120: l_TX8 VARCHAR2(2000);
3121: l_TX6 VARCHAR2(2000);
3122: H_SCORES HZ_PARTY_SEARCH.score_list;
3123: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3124: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3125:
3126: l_score NUMBER;
3119: l_TX5 VARCHAR2(2000);
3120: l_TX8 VARCHAR2(2000);
3121: l_TX6 VARCHAR2(2000);
3122: H_SCORES HZ_PARTY_SEARCH.score_list;
3123: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3124: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3125:
3126: l_score NUMBER;
3127: l_idx NUMBER;
3120: l_TX8 VARCHAR2(2000);
3121: l_TX6 VARCHAR2(2000);
3122: H_SCORES HZ_PARTY_SEARCH.score_list;
3123: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3124: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3125:
3126: l_score NUMBER;
3127: l_idx NUMBER;
3128: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
3360: /** Private procedure to acquire and score at party site level ***/
3361: 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
3362: l_party_id_idx NUMBER:=1;
3363: l_ctx_id NUMBER;
3364: h_ps_id HZ_PARTY_SEARCH.IDList;
3365: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3366: h_ps_score HZ_PARTY_SEARCH.IDList;
3367: detcnt NUMBER := 1;
3368: l_person_id NUMBER;
3361: 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
3362: l_party_id_idx NUMBER:=1;
3363: l_ctx_id NUMBER;
3364: h_ps_id HZ_PARTY_SEARCH.IDList;
3365: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3366: h_ps_score HZ_PARTY_SEARCH.IDList;
3367: detcnt NUMBER := 1;
3368: l_person_id NUMBER;
3369: BEGIN
3362: l_party_id_idx NUMBER:=1;
3363: l_ctx_id NUMBER;
3364: h_ps_id HZ_PARTY_SEARCH.IDList;
3365: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3366: h_ps_score HZ_PARTY_SEARCH.IDList;
3367: detcnt NUMBER := 1;
3368: l_person_id NUMBER;
3369: BEGIN
3370: SAVEPOINT eval_start;
3459: /** Private procedure to acquire and score at party site level ***/
3460: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3461: l_party_id_idx NUMBER:=1;
3462: l_ctx_id NUMBER;
3463: h_ct_id HZ_PARTY_SEARCH.IDList;
3464: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3465: h_ct_score HZ_PARTY_SEARCH.IDList;
3466: detcnt NUMBER := 1;
3467: l_person_id NUMBER;
3460: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3461: l_party_id_idx NUMBER:=1;
3462: l_ctx_id NUMBER;
3463: h_ct_id HZ_PARTY_SEARCH.IDList;
3464: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3465: h_ct_score HZ_PARTY_SEARCH.IDList;
3466: detcnt NUMBER := 1;
3467: l_person_id NUMBER;
3468: BEGIN
3461: l_party_id_idx NUMBER:=1;
3462: l_ctx_id NUMBER;
3463: h_ct_id HZ_PARTY_SEARCH.IDList;
3464: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3465: h_ct_score HZ_PARTY_SEARCH.IDList;
3466: detcnt NUMBER := 1;
3467: l_person_id NUMBER;
3468: BEGIN
3469: SAVEPOINT eval_start;
3517: /** Private procedure to acquire and score at contact point level ***/
3518: 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
3519: l_party_id_idx NUMBER:=1;
3520: l_ctx_id NUMBER;
3521: h_cpt_id HZ_PARTY_SEARCH.IDList;
3522: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3523: h_cpt_score HZ_PARTY_SEARCH.IDList;
3524: detcnt NUMBER := 1;
3525: l_person_id NUMBER;
3518: 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
3519: l_party_id_idx NUMBER:=1;
3520: l_ctx_id NUMBER;
3521: h_cpt_id HZ_PARTY_SEARCH.IDList;
3522: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3523: h_cpt_score HZ_PARTY_SEARCH.IDList;
3524: detcnt NUMBER := 1;
3525: l_person_id NUMBER;
3526: is_a_match VARCHAR2(1) := 'Y';
3519: l_party_id_idx NUMBER:=1;
3520: l_ctx_id NUMBER;
3521: h_cpt_id HZ_PARTY_SEARCH.IDList;
3522: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3523: h_cpt_score HZ_PARTY_SEARCH.IDList;
3524: detcnt NUMBER := 1;
3525: l_person_id NUMBER;
3526: is_a_match VARCHAR2(1) := 'Y';
3527: l_cpt_flag VARCHAR2(1) := 'N';
3861: END find_persons;
3862:
3863: PROCEDURE find_persons (
3864: p_rule_id IN NUMBER,
3865: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3866: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3867: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3868: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3869: p_restrict_sql IN VARCHAR2,
3862:
3863: PROCEDURE find_persons (
3864: p_rule_id IN NUMBER,
3865: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3866: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3867: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3868: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3869: p_restrict_sql IN VARCHAR2,
3870: p_match_type IN VARCHAR2,
3863: PROCEDURE find_persons (
3864: p_rule_id IN NUMBER,
3865: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3866: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3867: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3868: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3869: p_restrict_sql IN VARCHAR2,
3870: p_match_type IN VARCHAR2,
3871: p_search_merged IN VARCHAR2,
3864: p_rule_id IN NUMBER,
3865: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3866: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3867: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3868: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3869: p_restrict_sql IN VARCHAR2,
3870: p_match_type IN VARCHAR2,
3871: p_search_merged IN VARCHAR2,
3872: p_ins_details IN VARCHAR2,
3878: 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);
3879: END;
3880: PROCEDURE find_party_details (
3881: p_rule_id IN NUMBER,
3882: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3883: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3884: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3885: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3886: p_restrict_sql IN VARCHAR2,
3879: END;
3880: PROCEDURE find_party_details (
3881: p_rule_id IN NUMBER,
3882: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3883: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3884: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3885: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3886: p_restrict_sql IN VARCHAR2,
3887: p_match_type IN VARCHAR2,
3880: PROCEDURE find_party_details (
3881: p_rule_id IN NUMBER,
3882: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3883: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3884: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3885: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3886: p_restrict_sql IN VARCHAR2,
3887: p_match_type IN VARCHAR2,
3888: p_search_merged IN VARCHAR2,
3881: p_rule_id IN NUMBER,
3882: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3883: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3884: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3885: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3886: p_restrict_sql IN VARCHAR2,
3887: p_match_type IN VARCHAR2,
3888: p_search_merged IN VARCHAR2,
3889: x_search_ctx_id OUT NUMBER,
3938: x_dup_set_id OUT NUMBER,
3939: x_search_ctx_id OUT NUMBER,
3940: x_num_matches OUT NUMBER
3941: ) IS
3942: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3943: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3944: l_contact_list HZ_PARTY_SEARCH.contact_list;
3945: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3946: l_match_idx NUMBER;
3939: x_search_ctx_id OUT NUMBER,
3940: x_num_matches OUT NUMBER
3941: ) IS
3942: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3943: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3944: l_contact_list HZ_PARTY_SEARCH.contact_list;
3945: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3946: l_match_idx NUMBER;
3947:
3940: x_num_matches OUT NUMBER
3941: ) IS
3942: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3943: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3944: l_contact_list HZ_PARTY_SEARCH.contact_list;
3945: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3946: l_match_idx NUMBER;
3947:
3948: --Fix for bug 4417124
3941: ) IS
3942: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
3943: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
3944: l_contact_list HZ_PARTY_SEARCH.contact_list;
3945: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
3946: l_match_idx NUMBER;
3947:
3948: --Fix for bug 4417124
3949: l_use_contact_addr_info BOOLEAN := TRUE;
4054: x_search_ctx_id OUT NUMBER,
4055: x_num_matches OUT NUMBER
4056: ) IS
4057:
4058: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4059: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4060: l_contact_list HZ_PARTY_SEARCH.contact_list;
4061: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4062: contact_point_ids HZ_PARTY_SEARCH.IDList;
4055: x_num_matches OUT NUMBER
4056: ) IS
4057:
4058: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4059: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4060: l_contact_list HZ_PARTY_SEARCH.contact_list;
4061: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4062: contact_point_ids HZ_PARTY_SEARCH.IDList;
4063: p_party_site_list HZ_PARTY_SEARCH.IDList;
4056: ) IS
4057:
4058: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4059: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4060: l_contact_list HZ_PARTY_SEARCH.contact_list;
4061: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4062: contact_point_ids HZ_PARTY_SEARCH.IDList;
4063: p_party_site_list HZ_PARTY_SEARCH.IDList;
4064: p_contact_ids HZ_PARTY_SEARCH.IDList;
4057:
4058: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4059: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4060: l_contact_list HZ_PARTY_SEARCH.contact_list;
4061: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4062: contact_point_ids HZ_PARTY_SEARCH.IDList;
4063: p_party_site_list HZ_PARTY_SEARCH.IDList;
4064: p_contact_ids HZ_PARTY_SEARCH.IDList;
4065: l_match_idx NUMBER;
4058: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4059: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4060: l_contact_list HZ_PARTY_SEARCH.contact_list;
4061: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4062: contact_point_ids HZ_PARTY_SEARCH.IDList;
4063: p_party_site_list HZ_PARTY_SEARCH.IDList;
4064: p_contact_ids HZ_PARTY_SEARCH.IDList;
4065: l_match_idx NUMBER;
4066: cursor get_cpts_for_party_sites is select contact_point_id
4059: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4060: l_contact_list HZ_PARTY_SEARCH.contact_list;
4061: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4062: contact_point_ids HZ_PARTY_SEARCH.IDList;
4063: p_party_site_list HZ_PARTY_SEARCH.IDList;
4064: p_contact_ids HZ_PARTY_SEARCH.IDList;
4065: l_match_idx NUMBER;
4066: cursor get_cpts_for_party_sites is select contact_point_id
4067: from hz_contact_points
4060: l_contact_list HZ_PARTY_SEARCH.contact_list;
4061: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4062: contact_point_ids HZ_PARTY_SEARCH.IDList;
4063: p_party_site_list HZ_PARTY_SEARCH.IDList;
4064: p_contact_ids HZ_PARTY_SEARCH.IDList;
4065: l_match_idx NUMBER;
4066: cursor get_cpts_for_party_sites is select contact_point_id
4067: from hz_contact_points
4068: where owner_table_name = 'HZ_PARTY_SITES'
4089:
4090: get_search_criteria (
4091: null,
4092: p_party_site_list,
4093: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4094: contact_point_ids,
4095: l_party_search_rec,
4096: l_party_site_list,
4097: l_contact_list,
4139: x_search_ctx_id OUT NUMBER,
4140: x_num_matches OUT NUMBER
4141: ) IS
4142:
4143: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4144: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4145: l_contact_list HZ_PARTY_SEARCH.contact_list;
4146: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4147: contact_point_ids HZ_PARTY_SEARCH.IDList;
4140: x_num_matches OUT NUMBER
4141: ) IS
4142:
4143: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4144: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4145: l_contact_list HZ_PARTY_SEARCH.contact_list;
4146: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4147: contact_point_ids HZ_PARTY_SEARCH.IDList;
4148: p_party_site_list HZ_PARTY_SEARCH.IDList;
4141: ) IS
4142:
4143: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4144: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4145: l_contact_list HZ_PARTY_SEARCH.contact_list;
4146: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4147: contact_point_ids HZ_PARTY_SEARCH.IDList;
4148: p_party_site_list HZ_PARTY_SEARCH.IDList;
4149: p_contact_ids HZ_PARTY_SEARCH.IDList;
4142:
4143: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4144: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4145: l_contact_list HZ_PARTY_SEARCH.contact_list;
4146: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4147: contact_point_ids HZ_PARTY_SEARCH.IDList;
4148: p_party_site_list HZ_PARTY_SEARCH.IDList;
4149: p_contact_ids HZ_PARTY_SEARCH.IDList;
4150: l_match_idx NUMBER;
4143: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4144: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4145: l_contact_list HZ_PARTY_SEARCH.contact_list;
4146: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4147: contact_point_ids HZ_PARTY_SEARCH.IDList;
4148: p_party_site_list HZ_PARTY_SEARCH.IDList;
4149: p_contact_ids HZ_PARTY_SEARCH.IDList;
4150: l_match_idx NUMBER;
4151: cursor get_cpt_for_contact_id is select contact_point_id
4144: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4145: l_contact_list HZ_PARTY_SEARCH.contact_list;
4146: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4147: contact_point_ids HZ_PARTY_SEARCH.IDList;
4148: p_party_site_list HZ_PARTY_SEARCH.IDList;
4149: p_contact_ids HZ_PARTY_SEARCH.IDList;
4150: l_match_idx NUMBER;
4151: cursor get_cpt_for_contact_id is select contact_point_id
4152: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4145: l_contact_list HZ_PARTY_SEARCH.contact_list;
4146: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4147: contact_point_ids HZ_PARTY_SEARCH.IDList;
4148: p_party_site_list HZ_PARTY_SEARCH.IDList;
4149: p_contact_ids HZ_PARTY_SEARCH.IDList;
4150: l_match_idx NUMBER;
4151: cursor get_cpt_for_contact_id is select contact_point_id
4152: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4153: where a.party_relationship_id = b.relationship_id
4176: CLOSE get_cpt_for_contact_id;
4177:
4178: get_search_criteria (
4179: null,
4180: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4181: p_contact_ids,
4182: contact_point_ids,
4183: l_party_search_rec,
4184: l_party_site_list,
4227: p_match_type IN VARCHAR2,
4228: x_search_ctx_id OUT NUMBER,
4229: x_num_matches OUT NUMBER
4230: ) IS
4231: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4232: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4233: l_contact_list HZ_PARTY_SEARCH.contact_list;
4234: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4235: contact_point_ids HZ_PARTY_SEARCH.IDList;
4228: x_search_ctx_id OUT NUMBER,
4229: x_num_matches OUT NUMBER
4230: ) IS
4231: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4232: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4233: l_contact_list HZ_PARTY_SEARCH.contact_list;
4234: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4235: contact_point_ids HZ_PARTY_SEARCH.IDList;
4236: p_party_site_list HZ_PARTY_SEARCH.IDList;
4229: x_num_matches OUT NUMBER
4230: ) IS
4231: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4232: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4233: l_contact_list HZ_PARTY_SEARCH.contact_list;
4234: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4235: contact_point_ids HZ_PARTY_SEARCH.IDList;
4236: p_party_site_list HZ_PARTY_SEARCH.IDList;
4237: p_contact_ids HZ_PARTY_SEARCH.IDList;
4230: ) IS
4231: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4232: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4233: l_contact_list HZ_PARTY_SEARCH.contact_list;
4234: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4235: contact_point_ids HZ_PARTY_SEARCH.IDList;
4236: p_party_site_list HZ_PARTY_SEARCH.IDList;
4237: p_contact_ids HZ_PARTY_SEARCH.IDList;
4238: l_match_idx NUMBER;
4231: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4232: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4233: l_contact_list HZ_PARTY_SEARCH.contact_list;
4234: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4235: contact_point_ids HZ_PARTY_SEARCH.IDList;
4236: p_party_site_list HZ_PARTY_SEARCH.IDList;
4237: p_contact_ids HZ_PARTY_SEARCH.IDList;
4238: l_match_idx NUMBER;
4239:
4232: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4233: l_contact_list HZ_PARTY_SEARCH.contact_list;
4234: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4235: contact_point_ids HZ_PARTY_SEARCH.IDList;
4236: p_party_site_list HZ_PARTY_SEARCH.IDList;
4237: p_contact_ids HZ_PARTY_SEARCH.IDList;
4238: l_match_idx NUMBER;
4239:
4240: BEGIN
4233: l_contact_list HZ_PARTY_SEARCH.contact_list;
4234: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4235: contact_point_ids HZ_PARTY_SEARCH.IDList;
4236: p_party_site_list HZ_PARTY_SEARCH.IDList;
4237: p_contact_ids HZ_PARTY_SEARCH.IDList;
4238: l_match_idx NUMBER;
4239:
4240: BEGIN
4241: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4250: END IF;
4251: contact_point_ids(1) := p_contact_point_id;
4252: get_search_criteria (
4253: null,
4254: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4255: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4256: contact_point_ids,
4257: l_party_search_rec,
4258: l_party_site_list,
4251: contact_point_ids(1) := p_contact_point_id;
4252: get_search_criteria (
4253: null,
4254: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4255: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4256: contact_point_ids,
4257: l_party_search_rec,
4258: l_party_site_list,
4259: l_contact_list,
4404: ) IS
4405: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4406: l_match_idx NUMBER;
4407: AttrVals AttrList;
4408: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4409: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4410: l_contact_list HZ_PARTY_SEARCH.contact_list;
4411: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4412: l_dup_set_id NUMBER;
4405: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4406: l_match_idx NUMBER;
4407: AttrVals AttrList;
4408: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4409: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4410: l_contact_list HZ_PARTY_SEARCH.contact_list;
4411: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4412: l_dup_set_id NUMBER;
4413: l_idx NUMBER;
4406: l_match_idx NUMBER;
4407: AttrVals AttrList;
4408: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4409: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4410: l_contact_list HZ_PARTY_SEARCH.contact_list;
4411: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4412: l_dup_set_id NUMBER;
4413: l_idx NUMBER;
4414: l_cpt_type VARCHAR2(255);
4407: AttrVals AttrList;
4408: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4409: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4410: l_contact_list HZ_PARTY_SEARCH.contact_list;
4411: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4412: l_dup_set_id NUMBER;
4413: l_idx NUMBER;
4414: l_cpt_type VARCHAR2(255);
4415: FIRST BOOLEAN := TRUE;
4603:
4604: PROCEDURE get_matching_party_sites (
4605: p_rule_id IN NUMBER,
4606: p_party_id IN NUMBER,
4607: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4608: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4609: p_restrict_sql IN VARCHAR2,
4610: p_match_type IN VARCHAR2,
4611: p_dup_party_site_id IN NUMBER,
4604: PROCEDURE get_matching_party_sites (
4605: p_rule_id IN NUMBER,
4606: p_party_id IN NUMBER,
4607: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4608: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4609: p_restrict_sql IN VARCHAR2,
4610: p_match_type IN VARCHAR2,
4611: p_dup_party_site_id IN NUMBER,
4612: x_search_ctx_id OUT NUMBER,
4635: l_TX4 VARCHAR2(2000);
4636: l_TX11 VARCHAR2(2000);
4637: l_TX5 VARCHAR2(2000);
4638: l_TX6 VARCHAR2(2000);
4639: H_SCORES HZ_PARTY_SEARCH.score_list;
4640:
4641: l_score NUMBER;
4642: l_idx NUMBER;
4643: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
4666: defps NUMBER :=0;
4667: defct NUMBER :=0;
4668: defcpt NUMBER :=0;
4669: l_index NUMBER;
4670: l_match_ps_list HZ_PARTY_SEARCH.IDList;
4671: l_cnt NUMBER:=1;
4672:
4673:
4674: BEGIN
4690: l_match_str := ' AND ';
4691: ELSIF p_match_type = 'OR' THEN
4692: l_match_str := ' OR ';
4693: END IF;
4694: 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);
4695: g_score_until_thresh := false;
4696: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4697:
4698: -- ************************************************************
4698: -- ************************************************************
4699: -- STEP 2. Setup of intermedia query strings for Acquisition query
4700: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4701: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4702: 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);
4703:
4704: -- Setup Search Context ID
4705: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4706: x_search_ctx_id := l_search_ctx_id;
4841:
4842: PROCEDURE get_matching_contacts (
4843: p_rule_id IN NUMBER,
4844: p_party_id IN NUMBER,
4845: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4846: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4847: p_restrict_sql IN VARCHAR2,
4848: p_match_type IN VARCHAR2,
4849: p_dup_contact_id IN NUMBER,
4842: PROCEDURE get_matching_contacts (
4843: p_rule_id IN NUMBER,
4844: p_party_id IN NUMBER,
4845: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
4846: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4847: p_restrict_sql IN VARCHAR2,
4848: p_match_type IN VARCHAR2,
4849: p_dup_contact_id IN NUMBER,
4850: x_search_ctx_id OUT NUMBER,
4870:
4871: l_TX1 VARCHAR2(2000);
4872: l_TX5 VARCHAR2(2000);
4873: l_TX6 VARCHAR2(2000);
4874: H_SCORES HZ_PARTY_SEARCH.score_list;
4875:
4876: l_score NUMBER;
4877: l_idx NUMBER;
4878: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
4898: l_party_max_score NUMBER;
4899: l_ps_max_score NUMBER;
4900: l_contact_max_score NUMBER;
4901: l_cpt_max_score NUMBER;
4902: l_match_contact_list HZ_PARTY_SEARCH.IDList;
4903: l_cnt NUMBER:=1;
4904:
4905:
4906: BEGIN
4922: l_match_str := ' AND ';
4923: ELSIF p_match_type = 'OR' THEN
4924: l_match_str := ' OR ';
4925: END IF;
4926: 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);
4927: g_score_until_thresh := false;
4928: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4929:
4930: -- ************************************************************
4930: -- ************************************************************
4931: -- STEP 2. Setup of intermedia query strings for Acquisition query
4932: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4933: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4934: 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);
4935:
4936: -- Setup Search Context ID
4937: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4938: x_search_ctx_id := l_search_ctx_id;
5069:
5070: PROCEDURE get_matching_contact_points (
5071: p_rule_id IN NUMBER,
5072: p_party_id IN NUMBER,
5073: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5074: p_restrict_sql IN VARCHAR2,
5075: p_match_type IN VARCHAR2,
5076: p_dup_contact_point_id IN NUMBER,
5077: x_search_ctx_id OUT NUMBER,
5101: l_cpt_ps_id NUMBER;
5102: l_cpt_contact_id NUMBER;
5103: l_contact_pt_id NUMBER;
5104: l_cpt_type VARCHAR2(100);
5105: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5106: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5107: H_SCORE HZ_PARTY_SEARCH.IDList;
5108:
5109: cnt NUMBER :=0;
5102: l_cpt_contact_id NUMBER;
5103: l_contact_pt_id NUMBER;
5104: l_cpt_type VARCHAR2(100);
5105: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5106: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5107: H_SCORE HZ_PARTY_SEARCH.IDList;
5108:
5109: cnt NUMBER :=0;
5110: l_party_max_score NUMBER;
5103: l_contact_pt_id NUMBER;
5104: l_cpt_type VARCHAR2(100);
5105: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5106: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5107: H_SCORE HZ_PARTY_SEARCH.IDList;
5108:
5109: cnt NUMBER :=0;
5110: l_party_max_score NUMBER;
5111: l_ps_max_score NUMBER;
5132: l_match_str := ' AND ';
5133: ELSIF p_match_type = 'OR' THEN
5134: l_match_str := ' OR ';
5135: END IF;
5136: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5137: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5138: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5139: g_score_until_thresh := false;
5140: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5133: ELSIF p_match_type = 'OR' THEN
5134: l_match_str := ' OR ';
5135: END IF;
5136: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5137: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5138: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5139: g_score_until_thresh := false;
5140: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5141:
5141:
5142: -- ************************************************************
5143: -- STEP 2. Setup of intermedia query strings for Acquisition query
5144: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5145: 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);
5146:
5147: -- Setup Search Context ID
5148: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5149: x_search_ctx_id := l_search_ctx_id;
5216:
5217: PROCEDURE get_score_details (
5218: p_rule_id IN NUMBER,
5219: p_party_id IN NUMBER,
5220: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5221: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5222: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5223: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5224: x_search_ctx_id IN OUT NUMBER
5217: PROCEDURE get_score_details (
5218: p_rule_id IN NUMBER,
5219: p_party_id IN NUMBER,
5220: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5221: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5222: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5223: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5224: x_search_ctx_id IN OUT NUMBER
5225: ) IS
5218: p_rule_id IN NUMBER,
5219: p_party_id IN NUMBER,
5220: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5221: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5222: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5223: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5224: x_search_ctx_id IN OUT NUMBER
5225: ) IS
5226:
5219: p_party_id IN NUMBER,
5220: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5221: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5222: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5223: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5224: x_search_ctx_id IN OUT NUMBER
5225: ) IS
5226:
5227: -- Strings to hold the generated Intermedia query strings
5256: l_TX8 VARCHAR2(2000);
5257: l_max_TX8 VARCHAR2(2000);
5258: l_TX6 VARCHAR2(2000);
5259: l_max_TX6 VARCHAR2(2000);
5260: H_SCORES HZ_PARTY_SEARCH.score_list;
5261:
5262: l_score NUMBER;
5263: l_match_idx NUMBER;
5264: l_idx NUMBER;
5428: ************************************************/
5429:
5430: PROCEDURE get_party_for_search (
5431: p_party_id IN NUMBER,
5432: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5433: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5434: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5435: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5436: ) IS
5429:
5430: PROCEDURE get_party_for_search (
5431: p_party_id IN NUMBER,
5432: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5433: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5434: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5435: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5436: ) IS
5437: l_party_id NUMBER;
5430: PROCEDURE get_party_for_search (
5431: p_party_id IN NUMBER,
5432: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5433: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5434: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5435: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5436: ) IS
5437: l_party_id NUMBER;
5438: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5431: p_party_id IN NUMBER,
5432: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5433: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5434: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5435: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5436: ) IS
5437: l_party_id NUMBER;
5438: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5439: l_contact_ids HZ_PARTY_SEARCH.IDList;
5434: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5435: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5436: ) IS
5437: l_party_id NUMBER;
5438: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5439: l_contact_ids HZ_PARTY_SEARCH.IDList;
5440: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5441: ps NUMBER :=1;
5442: cpt NUMBER :=1;
5435: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5436: ) IS
5437: l_party_id NUMBER;
5438: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5439: l_contact_ids HZ_PARTY_SEARCH.IDList;
5440: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5441: ps NUMBER :=1;
5442: cpt NUMBER :=1;
5443: ct NUMBER :=1;
5436: ) IS
5437: l_party_id NUMBER;
5438: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5439: l_contact_ids HZ_PARTY_SEARCH.IDList;
5440: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5441: ps NUMBER :=1;
5442: cpt NUMBER :=1;
5443: ct NUMBER :=1;
5444: l_use_contact_info varchar2(1);
5532: END get_party_for_search;
5533:
5534: PROCEDURE get_search_criteria (
5535: p_party_id IN NUMBER,
5536: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5537: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5538: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5539: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5540: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5533:
5534: PROCEDURE get_search_criteria (
5535: p_party_id IN NUMBER,
5536: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5537: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5538: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5539: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5540: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5541: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5534: PROCEDURE get_search_criteria (
5535: p_party_id IN NUMBER,
5536: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5537: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5538: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5539: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5540: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5541: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5542: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5535: p_party_id IN NUMBER,
5536: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5537: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5538: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5539: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5540: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5541: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5542: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5543: ) IS
5536: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5537: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5538: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5539: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5540: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5541: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5542: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5543: ) IS
5544: BEGIN
5537: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5538: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5539: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5540: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5541: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5542: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5543: ) IS
5544: BEGIN
5545:
5538: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5539: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5540: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5541: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5542: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5543: ) IS
5544: BEGIN
5545:
5546: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5584: ************************************************/
5585:
5586: PROCEDURE get_party_rec (
5587: p_party_id IN NUMBER,
5588: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type
5589: ) IS
5590: l_party_type VARCHAR2(255);
5591: BEGIN
5592:
5643: the party site search record structure
5644: ************************************************/
5645:
5646: PROCEDURE get_party_site_rec (
5647: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5648: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5649: ) IS
5650: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5651: SELECT party_site_id
5644: ************************************************/
5645:
5646: PROCEDURE get_party_site_rec (
5647: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5648: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5649: ) IS
5650: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5651: SELECT party_site_id
5652: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
5699: the contact search record structure
5700: ************************************************/
5701:
5702: PROCEDURE get_contact_rec (
5703: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5704: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5705: ) IS
5706: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5707: SELECT org_contact_id
5700: ************************************************/
5701:
5702: PROCEDURE get_contact_rec (
5703: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5704: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5705: ) IS
5706: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5707: SELECT org_contact_id
5708: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
5755: the contact point search record structure
5756: ************************************************/
5757:
5758: PROCEDURE get_contact_point_rec (
5759: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5760: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5761: ) IS
5762:
5763: -- Cursor to fetch primary contact points for party
5756: ************************************************/
5757:
5758: PROCEDURE get_contact_point_rec (
5759: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
5760: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5761: ) IS
5762:
5763: -- Cursor to fetch primary contact points for party
5764: CURSOR c_cpts(cp_contact_point_id NUMBER) IS
5814: ************************************************/
5815:
5816: PROCEDURE MAP_PARTY_REC(
5817: p_search_ctx IN BOOLEAN,
5818: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5819: x_entered_max_score OUT NUMBER,
5820: x_stage_rec IN OUT NOCOPY HZ_PARTY_STAGE.party_stage_rec_type
5821: ) IS
5822: tmp VARCHAR2(4000);
5950: ************************************************/
5951:
5952: PROCEDURE MAP_PARTY_SITE_REC(
5953: p_search_ctx IN BOOLEAN,
5954: p_search_list IN HZ_PARTY_SEARCH.party_site_list,
5955: x_entered_max_score OUT NUMBER,
5956: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.party_site_stage_list
5957: ) IS
5958: l_current_max_score NUMBER;
6068: ************************************************/
6069:
6070: PROCEDURE MAP_CONTACT_REC(
6071: p_search_ctx IN BOOLEAN,
6072: p_search_list IN HZ_PARTY_SEARCH.contact_list,
6073: x_entered_max_score OUT NUMBER,
6074: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_stage_list
6075: ) IS
6076: l_current_max_score NUMBER;
6110: ************************************************/
6111:
6112: PROCEDURE MAP_CONTACT_POINT_REC(
6113: p_search_ctx IN BOOLEAN,
6114: p_search_list IN HZ_PARTY_SEARCH.contact_point_list,
6115: x_entered_max_score OUT NUMBER,
6116: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_pt_stage_list
6117: ) IS
6118: l_current_max_score NUMBER;
6216: 2. Contact Point Type is not null for each condition
6217: ************************************************/
6218:
6219: FUNCTION check_prim_cond(
6220: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6221: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6222: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6223: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6224: RETURN BOOLEAN IS
6217: ************************************************/
6218:
6219: FUNCTION check_prim_cond(
6220: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6221: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6222: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6223: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6224: RETURN BOOLEAN IS
6225: BEGIN
6218:
6219: FUNCTION check_prim_cond(
6220: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6221: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6222: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6223: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6224: RETURN BOOLEAN IS
6225: BEGIN
6226: FOR I IN 1..p_contact_point_list.COUNT LOOP
6219: FUNCTION check_prim_cond(
6220: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6221: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6222: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6223: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6224: RETURN BOOLEAN IS
6225: BEGIN
6226: FOR I IN 1..p_contact_point_list.COUNT LOOP
6227: IF p_contact_point_list(I).CONTACT_POINT_TYPE IS NULL AND (
6271: has party site criteria.
6272: ************************************************/
6273:
6274: PROCEDURE check_party_site_cond(
6275: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6276: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6277: x_secondary OUT BOOLEAN,
6278: x_primary OUT BOOLEAN
6279: ) IS
6272: ************************************************/
6273:
6274: PROCEDURE check_party_site_cond(
6275: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6276: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6277: x_secondary OUT BOOLEAN,
6278: x_primary OUT BOOLEAN
6279: ) IS
6280: BEGIN
6326: has contact criteria.
6327: ************************************************/
6328:
6329: PROCEDURE check_contact_cond(
6330: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6331: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6332: x_secondary OUT BOOLEAN,
6333: x_primary OUT BOOLEAN
6334: ) IS
6327: ************************************************/
6328:
6329: PROCEDURE check_contact_cond(
6330: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6331: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6332: x_secondary OUT BOOLEAN,
6333: x_primary OUT BOOLEAN
6334: ) IS
6335: BEGIN
6373: has valid contact point criteria.
6374: ************************************************/
6375:
6376: PROCEDURE check_contact_point_cond(
6377: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6378: x_secondary OUT BOOLEAN,
6379: x_primary OUT BOOLEAN
6380: ) IS
6381: BEGIN