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_TX39 VARCHAR2
425: ,p_table_TX40 VARCHAR2
426: ,p_table_TX41 VARCHAR2
519: PROCEDURE INSERT_PARTY_SITES_SCORE (
520: p_party_id IN NUMBER
521: ,p_record_id IN NUMBER
522: ,p_search_ctx_id IN NUMBER
523: ,p_search_rec IN HZ_PARTY_SEARCH.party_site_search_rec_type
524: ,p_stage_rec IN HZ_PARTY_STAGE.party_site_stage_rec_type
525: ,p_table_TX3 VARCHAR2
526: ,p_table_TX4 VARCHAR2
527: ,p_table_TX11 VARCHAR2
594: PROCEDURE INSERT_CONTACTS_SCORE (
595: p_party_id IN NUMBER
596: ,p_record_id IN NUMBER
597: ,p_search_ctx_id IN NUMBER
598: ,p_search_rec IN HZ_PARTY_SEARCH.contact_search_rec_type
599: ,p_stage_rec IN HZ_PARTY_STAGE.contact_stage_rec_type
600: ,p_table_TX5 VARCHAR2
601: ,p_table_TX2 VARCHAR2
602: ,p_idx IN NUMBER) IS
643: PROCEDURE INSERT_CONTACT_POINTS_SCORE (
644: p_party_id IN NUMBER
645: ,p_record_id IN NUMBER
646: ,p_search_ctx_id IN NUMBER
647: ,p_search_rec IN HZ_PARTY_SEARCH.contact_point_search_rec_type
648: ,p_stage_rec IN HZ_PARTY_STAGE.contact_pt_stage_rec_type
649: ,p_table_TX1 VARCHAR2
650: ,p_idx IN NUMBER) IS
651: l_current_score NUMBER:=0;
688: for the PARTY Entity
689: ************************************************/
690:
691: PROCEDURE POP_PARTY_COND_REC(
692: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type
693: ) IS
694: BEGIN
695: NULL ;
696: EXCEPTION
708: for the PARTY_SITES Entity
709: ************************************************/
710:
711: PROCEDURE POP_PARTY_SITES_COND_REC(
712: p_search_list IN HZ_PARTY_SEARCH.party_site_list
713: ) IS
714: BEGIN
715: NULL ;
716: EXCEPTION
728: for the CONTACTS Entity
729: ************************************************/
730:
731: PROCEDURE POP_CONTACTS_COND_REC(
732: p_search_list IN HZ_PARTY_SEARCH.contact_list
733: ) IS
734: BEGIN
735: NULL ;
736: EXCEPTION
748: for the CONTACT_POINTS Entity
749: ************************************************/
750:
751: PROCEDURE POP_CONTACT_POINTS_COND_REC(
752: p_search_list IN HZ_PARTY_SEARCH.contact_point_list
753: ) IS
754: BEGIN
755: NULL ;
756: EXCEPTION
762: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
763: END;
764:
765: PROCEDURE init_score_context (
766: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
767: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
768: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
769: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
770: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
763: END;
764:
765: PROCEDURE init_score_context (
766: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
767: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
768: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
769: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
770: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
771: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
764:
765: PROCEDURE init_score_context (
766: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
767: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
768: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
769: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
770: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
771: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
772: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
765: PROCEDURE init_score_context (
766: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
767: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
768: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
769: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
770: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
771: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
772: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
773: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
766: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
767: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
768: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
769: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
770: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
771: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
772: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
773: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
774: ) IS
767: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
768: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
769: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
770: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
771: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
772: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
773: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
774: ) IS
775: l_dummy NUMBER;
768: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
769: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
770: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
771: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
772: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
773: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
774: ) IS
775: l_dummy NUMBER;
776: BEGIN
769: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
770: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
771: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
772: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
773: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST
774: ) IS
775: l_dummy NUMBER;
776: BEGIN
777: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
789: MAP_CONTACT_POINT_REC(FALSE,p_contact_point_list, l_dummy, g_contact_pt_stage_list);
790:
791: END;
792: FUNCTION init_search(
793: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
794: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
795: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
796: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
797: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
790:
791: END;
792: FUNCTION init_search(
793: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
794: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
795: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
796: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
797: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
798: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
791: END;
792: FUNCTION init_search(
793: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
794: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
795: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
796: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
797: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
798: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
799: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
792: FUNCTION init_search(
793: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
794: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
795: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
796: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
797: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
798: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
799: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
800: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
793: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type:=
794: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
795: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
796: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
797: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
798: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
799: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
800: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
801: p_match_type IN VARCHAR2,
794: HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
795: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
796: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
797: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
798: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
799: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
800: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
801: p_match_type IN VARCHAR2,
802: x_party_max_score OUT NUMBER,
795: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list:=
796: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
797: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
798: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
799: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
800: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
801: p_match_type IN VARCHAR2,
802: x_party_max_score OUT NUMBER,
803: x_ps_max_score OUT NUMBER,
796: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,
797: p_contact_list IN HZ_PARTY_SEARCH.contact_list:=
798: HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
799: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list:=
800: HZ_PARTY_SEARCH.G_MISS_CONTACT_POINT_LIST,
801: p_match_type IN VARCHAR2,
802: x_party_max_score OUT NUMBER,
803: x_ps_max_score OUT NUMBER,
804: x_contact_max_score OUT NUMBER,
808: l_ps_entered_max_score NUMBER:=0;
809: l_ct_entered_max_score NUMBER:=0;
810: l_cpt_entered_max_score NUMBER:=0;
811: vlist vlisttype;
812: maxscore HZ_PARTY_SEARCH.IDList;
813: l_name VARCHAR2(200);
814: l_idx NUMBER;
815: l_num NUMBER;
816: total NUMBER;
2239: p_party_id NUMBER,
2240: p_party_site_id NUMBER,
2241: p_org_contact_id NUMBER,
2242: p_contact_point_id NUMBER)
2243: RETURN HZ_PARTY_SEARCH.score_rec IS
2244: l_score_rec HZ_PARTY_SEARCH.score_rec;
2245: BEGIN
2246: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2247: 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);
2240: p_party_site_id NUMBER,
2241: p_org_contact_id NUMBER,
2242: p_contact_point_id NUMBER)
2243: RETURN HZ_PARTY_SEARCH.score_rec IS
2244: l_score_rec HZ_PARTY_SEARCH.score_rec;
2245: BEGIN
2246: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2247: 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);
2248: 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);
2272: **********************************************************/
2273:
2274: PROCEDURE find_parties (
2275: p_rule_id IN NUMBER,
2276: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2277: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2278: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2279: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2280: p_restrict_sql IN VARCHAR2,
2273:
2274: PROCEDURE find_parties (
2275: p_rule_id IN NUMBER,
2276: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2277: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2278: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2279: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2280: p_restrict_sql IN VARCHAR2,
2281: p_match_type IN VARCHAR2,
2274: PROCEDURE find_parties (
2275: p_rule_id IN NUMBER,
2276: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2277: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2278: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2279: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2280: p_restrict_sql IN VARCHAR2,
2281: p_match_type IN VARCHAR2,
2282: p_search_merged IN VARCHAR2,
2275: p_rule_id IN NUMBER,
2276: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
2277: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
2278: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
2279: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
2280: p_restrict_sql IN VARCHAR2,
2281: p_match_type IN VARCHAR2,
2282: p_search_merged IN VARCHAR2,
2283: p_dup_party_id IN NUMBER,
2317: l_TX4 VARCHAR2(2000);
2318: l_TX11 VARCHAR2(2000);
2319: l_TX5 VARCHAR2(2000);
2320: l_TX40 VARCHAR2(2000);
2321: H_SCORES HZ_PARTY_SEARCH.score_list;
2322: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2323: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2324:
2325: l_score NUMBER;
2318: l_TX11 VARCHAR2(2000);
2319: l_TX5 VARCHAR2(2000);
2320: l_TX40 VARCHAR2(2000);
2321: H_SCORES HZ_PARTY_SEARCH.score_list;
2322: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2323: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2324:
2325: l_score NUMBER;
2326: l_idx NUMBER;
2319: l_TX5 VARCHAR2(2000);
2320: l_TX40 VARCHAR2(2000);
2321: H_SCORES HZ_PARTY_SEARCH.score_list;
2322: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
2323: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
2324:
2325: l_score NUMBER;
2326: l_idx NUMBER;
2327: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
2510: /** Private procedure to acquire and score at party site level ***/
2511: 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
2512: l_party_id_idx NUMBER:=1;
2513: l_ctx_id NUMBER;
2514: h_ps_id HZ_PARTY_SEARCH.IDList;
2515: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2516: h_ps_score HZ_PARTY_SEARCH.IDList;
2517: detcnt NUMBER := 1;
2518: BEGIN
2511: 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
2512: l_party_id_idx NUMBER:=1;
2513: l_ctx_id NUMBER;
2514: h_ps_id HZ_PARTY_SEARCH.IDList;
2515: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2516: h_ps_score HZ_PARTY_SEARCH.IDList;
2517: detcnt NUMBER := 1;
2518: BEGIN
2519: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2512: l_party_id_idx NUMBER:=1;
2513: l_ctx_id NUMBER;
2514: h_ps_id HZ_PARTY_SEARCH.IDList;
2515: h_ps_party_id HZ_PARTY_SEARCH.IDList;
2516: h_ps_score HZ_PARTY_SEARCH.IDList;
2517: detcnt NUMBER := 1;
2518: BEGIN
2519: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2520: 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);
2632: /** Private procedure to acquire and score at contact point level ***/
2633: 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
2634: l_party_id_idx NUMBER:=1;
2635: l_ctx_id NUMBER;
2636: h_ct_id HZ_PARTY_SEARCH.IDList;
2637: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2638: h_ct_score HZ_PARTY_SEARCH.IDList;
2639: detcnt NUMBER := 1;
2640: BEGIN
2633: 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
2634: l_party_id_idx NUMBER:=1;
2635: l_ctx_id NUMBER;
2636: h_ct_id HZ_PARTY_SEARCH.IDList;
2637: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2638: h_ct_score HZ_PARTY_SEARCH.IDList;
2639: detcnt NUMBER := 1;
2640: BEGIN
2641: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2634: l_party_id_idx NUMBER:=1;
2635: l_ctx_id NUMBER;
2636: h_ct_id HZ_PARTY_SEARCH.IDList;
2637: h_ct_party_id HZ_PARTY_SEARCH.IDList;
2638: h_ct_score HZ_PARTY_SEARCH.IDList;
2639: detcnt NUMBER := 1;
2640: BEGIN
2641: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2642: 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);
2741: /** Private procedure to acquire and score at contact point level ***/
2742: 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
2743: l_party_id_idx NUMBER:=1;
2744: l_ctx_id NUMBER;
2745: h_cpt_id HZ_PARTY_SEARCH.IDList;
2746: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2747: h_cpt_score HZ_PARTY_SEARCH.IDList;
2748: detcnt NUMBER := 1;
2749: l_cpt_flag VARCHAR2(1) := 'N';
2742: 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
2743: l_party_id_idx NUMBER:=1;
2744: l_ctx_id NUMBER;
2745: h_cpt_id HZ_PARTY_SEARCH.IDList;
2746: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2747: h_cpt_score HZ_PARTY_SEARCH.IDList;
2748: detcnt NUMBER := 1;
2749: l_cpt_flag VARCHAR2(1) := 'N';
2750: is_a_match VARCHAR2(1) := 'Y';
2743: l_party_id_idx NUMBER:=1;
2744: l_ctx_id NUMBER;
2745: h_cpt_id HZ_PARTY_SEARCH.IDList;
2746: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
2747: h_cpt_score HZ_PARTY_SEARCH.IDList;
2748: detcnt NUMBER := 1;
2749: l_cpt_flag VARCHAR2(1) := 'N';
2750: is_a_match VARCHAR2(1) := 'Y';
2751: BEGIN
3230: END find_parties;
3231:
3232: PROCEDURE find_persons (
3233: p_rule_id IN NUMBER,
3234: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3235: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3236: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3237: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3238: p_restrict_sql IN VARCHAR2,
3231:
3232: PROCEDURE find_persons (
3233: p_rule_id IN NUMBER,
3234: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3235: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3236: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3237: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3238: p_restrict_sql IN VARCHAR2,
3239: p_match_type IN VARCHAR2,
3232: PROCEDURE find_persons (
3233: p_rule_id IN NUMBER,
3234: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3235: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3236: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3237: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3238: p_restrict_sql IN VARCHAR2,
3239: p_match_type IN VARCHAR2,
3240: p_search_merged IN VARCHAR2,
3233: p_rule_id IN NUMBER,
3234: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
3235: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
3236: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
3237: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
3238: p_restrict_sql IN VARCHAR2,
3239: p_match_type IN VARCHAR2,
3240: p_search_merged IN VARCHAR2,
3241: p_dup_party_id IN NUMBER,
3275: l_TX4 VARCHAR2(2000);
3276: l_TX11 VARCHAR2(2000);
3277: l_TX5 VARCHAR2(2000);
3278: l_TX40 VARCHAR2(2000);
3279: H_SCORES HZ_PARTY_SEARCH.score_list;
3280: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3281: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3282:
3283: l_score NUMBER;
3276: l_TX11 VARCHAR2(2000);
3277: l_TX5 VARCHAR2(2000);
3278: l_TX40 VARCHAR2(2000);
3279: H_SCORES HZ_PARTY_SEARCH.score_list;
3280: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3281: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3282:
3283: l_score NUMBER;
3284: l_idx NUMBER;
3277: l_TX5 VARCHAR2(2000);
3278: l_TX40 VARCHAR2(2000);
3279: H_SCORES HZ_PARTY_SEARCH.score_list;
3280: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
3281: H_PARTY_ID_LIST HZ_PARTY_SEARCH.IDList;
3282:
3283: l_score NUMBER;
3284: l_idx NUMBER;
3285: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
3516: /** Private procedure to acquire and score at party site level ***/
3517: 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
3518: l_party_id_idx NUMBER:=1;
3519: l_ctx_id NUMBER;
3520: h_ps_id HZ_PARTY_SEARCH.IDList;
3521: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3522: h_ps_score HZ_PARTY_SEARCH.IDList;
3523: detcnt NUMBER := 1;
3524: l_person_id NUMBER;
3517: 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
3518: l_party_id_idx NUMBER:=1;
3519: l_ctx_id NUMBER;
3520: h_ps_id HZ_PARTY_SEARCH.IDList;
3521: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3522: h_ps_score HZ_PARTY_SEARCH.IDList;
3523: detcnt NUMBER := 1;
3524: l_person_id NUMBER;
3525: BEGIN
3518: l_party_id_idx NUMBER:=1;
3519: l_ctx_id NUMBER;
3520: h_ps_id HZ_PARTY_SEARCH.IDList;
3521: h_ps_party_id HZ_PARTY_SEARCH.IDList;
3522: h_ps_score HZ_PARTY_SEARCH.IDList;
3523: detcnt NUMBER := 1;
3524: l_person_id NUMBER;
3525: BEGIN
3526: SAVEPOINT eval_start;
3615: /** Private procedure to acquire and score at party site level ***/
3616: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3617: l_party_id_idx NUMBER:=1;
3618: l_ctx_id NUMBER;
3619: h_ct_id HZ_PARTY_SEARCH.IDList;
3620: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3621: h_ct_score HZ_PARTY_SEARCH.IDList;
3622: detcnt NUMBER := 1;
3623: l_person_id NUMBER;
3616: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3617: l_party_id_idx NUMBER:=1;
3618: l_ctx_id NUMBER;
3619: h_ct_id HZ_PARTY_SEARCH.IDList;
3620: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3621: h_ct_score HZ_PARTY_SEARCH.IDList;
3622: detcnt NUMBER := 1;
3623: l_person_id NUMBER;
3624: BEGIN
3617: l_party_id_idx NUMBER:=1;
3618: l_ctx_id NUMBER;
3619: h_ct_id HZ_PARTY_SEARCH.IDList;
3620: h_ct_party_id HZ_PARTY_SEARCH.IDList;
3621: h_ct_score HZ_PARTY_SEARCH.IDList;
3622: detcnt NUMBER := 1;
3623: l_person_id NUMBER;
3624: BEGIN
3625: SAVEPOINT eval_start;
3673: /** Private procedure to acquire and score at contact point level ***/
3674: 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
3675: l_party_id_idx NUMBER:=1;
3676: l_ctx_id NUMBER;
3677: h_cpt_id HZ_PARTY_SEARCH.IDList;
3678: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3679: h_cpt_score HZ_PARTY_SEARCH.IDList;
3680: detcnt NUMBER := 1;
3681: l_person_id NUMBER;
3674: 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
3675: l_party_id_idx NUMBER:=1;
3676: l_ctx_id NUMBER;
3677: h_cpt_id HZ_PARTY_SEARCH.IDList;
3678: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3679: h_cpt_score HZ_PARTY_SEARCH.IDList;
3680: detcnt NUMBER := 1;
3681: l_person_id NUMBER;
3682: is_a_match VARCHAR2(1) := 'Y';
3675: l_party_id_idx NUMBER:=1;
3676: l_ctx_id NUMBER;
3677: h_cpt_id HZ_PARTY_SEARCH.IDList;
3678: h_cpt_party_id HZ_PARTY_SEARCH.IDList;
3679: h_cpt_score HZ_PARTY_SEARCH.IDList;
3680: detcnt NUMBER := 1;
3681: l_person_id NUMBER;
3682: is_a_match VARCHAR2(1) := 'Y';
3683: l_cpt_flag VARCHAR2(1) := 'N';
4086: END find_persons;
4087:
4088: PROCEDURE find_persons (
4089: p_rule_id IN NUMBER,
4090: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4091: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4092: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4093: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4094: p_restrict_sql IN VARCHAR2,
4087:
4088: PROCEDURE find_persons (
4089: p_rule_id IN NUMBER,
4090: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4091: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4092: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4093: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4094: p_restrict_sql IN VARCHAR2,
4095: p_match_type IN VARCHAR2,
4088: PROCEDURE find_persons (
4089: p_rule_id IN NUMBER,
4090: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4091: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4092: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4093: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4094: p_restrict_sql IN VARCHAR2,
4095: p_match_type IN VARCHAR2,
4096: p_search_merged IN VARCHAR2,
4089: p_rule_id IN NUMBER,
4090: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4091: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4092: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4093: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4094: p_restrict_sql IN VARCHAR2,
4095: p_match_type IN VARCHAR2,
4096: p_search_merged IN VARCHAR2,
4097: p_ins_details IN VARCHAR2,
4103: 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);
4104: END;
4105: PROCEDURE find_party_details (
4106: p_rule_id IN NUMBER,
4107: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4108: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4109: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4110: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4111: p_restrict_sql IN VARCHAR2,
4104: END;
4105: PROCEDURE find_party_details (
4106: p_rule_id IN NUMBER,
4107: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4108: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4109: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4110: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4111: p_restrict_sql IN VARCHAR2,
4112: p_match_type IN VARCHAR2,
4105: PROCEDURE find_party_details (
4106: p_rule_id IN NUMBER,
4107: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4108: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4109: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4110: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4111: p_restrict_sql IN VARCHAR2,
4112: p_match_type IN VARCHAR2,
4113: p_search_merged IN VARCHAR2,
4106: p_rule_id IN NUMBER,
4107: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
4108: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
4109: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
4110: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
4111: p_restrict_sql IN VARCHAR2,
4112: p_match_type IN VARCHAR2,
4113: p_search_merged IN VARCHAR2,
4114: x_search_ctx_id OUT NUMBER,
4163: x_dup_set_id OUT NUMBER,
4164: x_search_ctx_id OUT NUMBER,
4165: x_num_matches OUT NUMBER
4166: ) IS
4167: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4168: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4169: l_contact_list HZ_PARTY_SEARCH.contact_list;
4170: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4171: l_match_idx NUMBER;
4164: x_search_ctx_id OUT NUMBER,
4165: x_num_matches OUT NUMBER
4166: ) IS
4167: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4168: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4169: l_contact_list HZ_PARTY_SEARCH.contact_list;
4170: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4171: l_match_idx NUMBER;
4172:
4165: x_num_matches OUT NUMBER
4166: ) IS
4167: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4168: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4169: l_contact_list HZ_PARTY_SEARCH.contact_list;
4170: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4171: l_match_idx NUMBER;
4172:
4173: --Fix for bug 4417124
4166: ) IS
4167: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4168: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4169: l_contact_list HZ_PARTY_SEARCH.contact_list;
4170: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4171: l_match_idx NUMBER;
4172:
4173: --Fix for bug 4417124
4174: l_use_contact_addr_info BOOLEAN := TRUE;
4279: x_search_ctx_id OUT NUMBER,
4280: x_num_matches OUT NUMBER
4281: ) IS
4282:
4283: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4284: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4285: l_contact_list HZ_PARTY_SEARCH.contact_list;
4286: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4287: contact_point_ids HZ_PARTY_SEARCH.IDList;
4280: x_num_matches OUT NUMBER
4281: ) IS
4282:
4283: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4284: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4285: l_contact_list HZ_PARTY_SEARCH.contact_list;
4286: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4287: contact_point_ids HZ_PARTY_SEARCH.IDList;
4288: p_party_site_list HZ_PARTY_SEARCH.IDList;
4281: ) IS
4282:
4283: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4284: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4285: l_contact_list HZ_PARTY_SEARCH.contact_list;
4286: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4287: contact_point_ids HZ_PARTY_SEARCH.IDList;
4288: p_party_site_list HZ_PARTY_SEARCH.IDList;
4289: p_contact_ids HZ_PARTY_SEARCH.IDList;
4282:
4283: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4284: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4285: l_contact_list HZ_PARTY_SEARCH.contact_list;
4286: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4287: contact_point_ids HZ_PARTY_SEARCH.IDList;
4288: p_party_site_list HZ_PARTY_SEARCH.IDList;
4289: p_contact_ids HZ_PARTY_SEARCH.IDList;
4290: l_match_idx NUMBER;
4283: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4284: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4285: l_contact_list HZ_PARTY_SEARCH.contact_list;
4286: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4287: contact_point_ids HZ_PARTY_SEARCH.IDList;
4288: p_party_site_list HZ_PARTY_SEARCH.IDList;
4289: p_contact_ids HZ_PARTY_SEARCH.IDList;
4290: l_match_idx NUMBER;
4291: cursor get_cpts_for_party_sites is select contact_point_id
4284: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4285: l_contact_list HZ_PARTY_SEARCH.contact_list;
4286: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4287: contact_point_ids HZ_PARTY_SEARCH.IDList;
4288: p_party_site_list HZ_PARTY_SEARCH.IDList;
4289: p_contact_ids HZ_PARTY_SEARCH.IDList;
4290: l_match_idx NUMBER;
4291: cursor get_cpts_for_party_sites is select contact_point_id
4292: from hz_contact_points
4285: l_contact_list HZ_PARTY_SEARCH.contact_list;
4286: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4287: contact_point_ids HZ_PARTY_SEARCH.IDList;
4288: p_party_site_list HZ_PARTY_SEARCH.IDList;
4289: p_contact_ids HZ_PARTY_SEARCH.IDList;
4290: l_match_idx NUMBER;
4291: cursor get_cpts_for_party_sites is select contact_point_id
4292: from hz_contact_points
4293: where owner_table_name = 'HZ_PARTY_SITES'
4314:
4315: get_search_criteria (
4316: null,
4317: p_party_site_list,
4318: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4319: contact_point_ids,
4320: l_party_search_rec,
4321: l_party_site_list,
4322: l_contact_list,
4364: x_search_ctx_id OUT NUMBER,
4365: x_num_matches OUT NUMBER
4366: ) IS
4367:
4368: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4369: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4370: l_contact_list HZ_PARTY_SEARCH.contact_list;
4371: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4372: contact_point_ids HZ_PARTY_SEARCH.IDList;
4365: x_num_matches OUT NUMBER
4366: ) IS
4367:
4368: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4369: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4370: l_contact_list HZ_PARTY_SEARCH.contact_list;
4371: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4372: contact_point_ids HZ_PARTY_SEARCH.IDList;
4373: p_party_site_list HZ_PARTY_SEARCH.IDList;
4366: ) IS
4367:
4368: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4369: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4370: l_contact_list HZ_PARTY_SEARCH.contact_list;
4371: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4372: contact_point_ids HZ_PARTY_SEARCH.IDList;
4373: p_party_site_list HZ_PARTY_SEARCH.IDList;
4374: p_contact_ids HZ_PARTY_SEARCH.IDList;
4367:
4368: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4369: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4370: l_contact_list HZ_PARTY_SEARCH.contact_list;
4371: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4372: contact_point_ids HZ_PARTY_SEARCH.IDList;
4373: p_party_site_list HZ_PARTY_SEARCH.IDList;
4374: p_contact_ids HZ_PARTY_SEARCH.IDList;
4375: l_match_idx NUMBER;
4368: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4369: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4370: l_contact_list HZ_PARTY_SEARCH.contact_list;
4371: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4372: contact_point_ids HZ_PARTY_SEARCH.IDList;
4373: p_party_site_list HZ_PARTY_SEARCH.IDList;
4374: p_contact_ids HZ_PARTY_SEARCH.IDList;
4375: l_match_idx NUMBER;
4376: cursor get_cpt_for_contact_id is select contact_point_id
4369: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4370: l_contact_list HZ_PARTY_SEARCH.contact_list;
4371: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4372: contact_point_ids HZ_PARTY_SEARCH.IDList;
4373: p_party_site_list HZ_PARTY_SEARCH.IDList;
4374: p_contact_ids HZ_PARTY_SEARCH.IDList;
4375: l_match_idx NUMBER;
4376: cursor get_cpt_for_contact_id is select contact_point_id
4377: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4370: l_contact_list HZ_PARTY_SEARCH.contact_list;
4371: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4372: contact_point_ids HZ_PARTY_SEARCH.IDList;
4373: p_party_site_list HZ_PARTY_SEARCH.IDList;
4374: p_contact_ids HZ_PARTY_SEARCH.IDList;
4375: l_match_idx NUMBER;
4376: cursor get_cpt_for_contact_id is select contact_point_id
4377: from hz_org_contacts a, hz_relationships b, hz_contact_points c
4378: where a.party_relationship_id = b.relationship_id
4401: CLOSE get_cpt_for_contact_id;
4402:
4403: get_search_criteria (
4404: null,
4405: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4406: p_contact_ids,
4407: contact_point_ids,
4408: l_party_search_rec,
4409: l_party_site_list,
4452: p_match_type IN VARCHAR2,
4453: x_search_ctx_id OUT NUMBER,
4454: x_num_matches OUT NUMBER
4455: ) IS
4456: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4457: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4458: l_contact_list HZ_PARTY_SEARCH.contact_list;
4459: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4460: contact_point_ids HZ_PARTY_SEARCH.IDList;
4453: x_search_ctx_id OUT NUMBER,
4454: x_num_matches OUT NUMBER
4455: ) IS
4456: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4457: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4458: l_contact_list HZ_PARTY_SEARCH.contact_list;
4459: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4460: contact_point_ids HZ_PARTY_SEARCH.IDList;
4461: p_party_site_list HZ_PARTY_SEARCH.IDList;
4454: x_num_matches OUT NUMBER
4455: ) IS
4456: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4457: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4458: l_contact_list HZ_PARTY_SEARCH.contact_list;
4459: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4460: contact_point_ids HZ_PARTY_SEARCH.IDList;
4461: p_party_site_list HZ_PARTY_SEARCH.IDList;
4462: p_contact_ids HZ_PARTY_SEARCH.IDList;
4455: ) IS
4456: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4457: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4458: l_contact_list HZ_PARTY_SEARCH.contact_list;
4459: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4460: contact_point_ids HZ_PARTY_SEARCH.IDList;
4461: p_party_site_list HZ_PARTY_SEARCH.IDList;
4462: p_contact_ids HZ_PARTY_SEARCH.IDList;
4463: l_match_idx NUMBER;
4456: l_party_search_rec HZ_PARTY_SEARCH.party_search_rec_type;
4457: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4458: l_contact_list HZ_PARTY_SEARCH.contact_list;
4459: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4460: contact_point_ids HZ_PARTY_SEARCH.IDList;
4461: p_party_site_list HZ_PARTY_SEARCH.IDList;
4462: p_contact_ids HZ_PARTY_SEARCH.IDList;
4463: l_match_idx NUMBER;
4464:
4457: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4458: l_contact_list HZ_PARTY_SEARCH.contact_list;
4459: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4460: contact_point_ids HZ_PARTY_SEARCH.IDList;
4461: p_party_site_list HZ_PARTY_SEARCH.IDList;
4462: p_contact_ids HZ_PARTY_SEARCH.IDList;
4463: l_match_idx NUMBER;
4464:
4465: BEGIN
4458: l_contact_list HZ_PARTY_SEARCH.contact_list;
4459: l_contact_point_list HZ_PARTY_SEARCH.contact_point_list;
4460: contact_point_ids HZ_PARTY_SEARCH.IDList;
4461: p_party_site_list HZ_PARTY_SEARCH.IDList;
4462: p_contact_ids HZ_PARTY_SEARCH.IDList;
4463: l_match_idx NUMBER;
4464:
4465: BEGIN
4466: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4475: END IF;
4476: contact_point_ids(1) := p_contact_point_id;
4477: get_search_criteria (
4478: null,
4479: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4480: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4481: contact_point_ids,
4482: l_party_search_rec,
4483: l_party_site_list,
4476: contact_point_ids(1) := p_contact_point_id;
4477: get_search_criteria (
4478: null,
4479: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4480: HZ_PARTY_SEARCH.G_MISS_ID_LIST,
4481: contact_point_ids,
4482: l_party_search_rec,
4483: l_party_site_list,
4484: l_contact_list,
4629: ) IS
4630: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4631: l_match_idx NUMBER;
4632: AttrVals AttrList;
4633: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4634: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4635: l_contact_list HZ_PARTY_SEARCH.contact_list;
4636: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4637: l_dup_set_id NUMBER;
4630: TYPE AttrList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
4631: l_match_idx NUMBER;
4632: AttrVals AttrList;
4633: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4634: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4635: l_contact_list HZ_PARTY_SEARCH.contact_list;
4636: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4637: l_dup_set_id NUMBER;
4638: l_idx NUMBER;
4631: l_match_idx NUMBER;
4632: AttrVals AttrList;
4633: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4634: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4635: l_contact_list HZ_PARTY_SEARCH.contact_list;
4636: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4637: l_dup_set_id NUMBER;
4638: l_idx NUMBER;
4639: l_cpt_type VARCHAR2(255);
4632: AttrVals AttrList;
4633: l_party_rec HZ_PARTY_SEARCH.party_search_rec_type;
4634: l_party_site_list HZ_PARTY_SEARCH.party_site_list;
4635: l_contact_list HZ_PARTY_SEARCH.contact_list;
4636: l_cpt_list HZ_PARTY_SEARCH.contact_point_list;
4637: l_dup_set_id NUMBER;
4638: l_idx NUMBER;
4639: l_cpt_type VARCHAR2(255);
4640: FIRST BOOLEAN := TRUE;
4825:
4826: PROCEDURE get_matching_party_sites (
4827: p_rule_id IN NUMBER,
4828: p_party_id IN NUMBER,
4829: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4830: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4831: p_restrict_sql IN VARCHAR2,
4832: p_match_type IN VARCHAR2,
4833: p_dup_party_site_id IN NUMBER,
4826: PROCEDURE get_matching_party_sites (
4827: p_rule_id IN NUMBER,
4828: p_party_id IN NUMBER,
4829: p_party_site_list IN HZ_PARTY_SEARCH.PARTY_SITE_LIST,
4830: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
4831: p_restrict_sql IN VARCHAR2,
4832: p_match_type IN VARCHAR2,
4833: p_dup_party_site_id IN NUMBER,
4834: x_search_ctx_id OUT NUMBER,
4855: l_TX1 VARCHAR2(2000);
4856: l_TX3 VARCHAR2(2000);
4857: l_TX4 VARCHAR2(2000);
4858: l_TX11 VARCHAR2(2000);
4859: H_SCORES HZ_PARTY_SEARCH.score_list;
4860:
4861: l_score NUMBER;
4862: l_idx NUMBER;
4863: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
4886: defps NUMBER :=0;
4887: defct NUMBER :=0;
4888: defcpt NUMBER :=0;
4889: l_index NUMBER;
4890: l_match_ps_list HZ_PARTY_SEARCH.IDList;
4891: l_cnt NUMBER:=1;
4892:
4893:
4894: BEGIN
4910: l_match_str := ' AND ';
4911: ELSIF p_match_type = 'OR' THEN
4912: l_match_str := ' OR ';
4913: END IF;
4914: 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);
4915: g_score_until_thresh := false;
4916: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4917:
4918: -- ************************************************************
4918: -- ************************************************************
4919: -- STEP 2. Setup of intermedia query strings for Acquisition query
4920: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4921: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4922: 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);
4923:
4924: -- Setup Search Context ID
4925: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4926: x_search_ctx_id := l_search_ctx_id;
5061:
5062: PROCEDURE get_matching_contacts (
5063: p_rule_id IN NUMBER,
5064: p_party_id IN NUMBER,
5065: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
5066: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5067: p_restrict_sql IN VARCHAR2,
5068: p_match_type IN VARCHAR2,
5069: p_dup_contact_id IN NUMBER,
5062: PROCEDURE get_matching_contacts (
5063: p_rule_id IN NUMBER,
5064: p_party_id IN NUMBER,
5065: p_contact_list IN HZ_PARTY_SEARCH.CONTACT_LIST,
5066: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5067: p_restrict_sql IN VARCHAR2,
5068: p_match_type IN VARCHAR2,
5069: p_dup_contact_id IN NUMBER,
5070: x_search_ctx_id OUT NUMBER,
5090:
5091: l_TX2 VARCHAR2(2000);
5092: l_TX1 VARCHAR2(2000);
5093: l_TX5 VARCHAR2(2000);
5094: H_SCORES HZ_PARTY_SEARCH.score_list;
5095:
5096: l_score NUMBER;
5097: l_idx NUMBER;
5098: l_party_cur HZ_PARTY_STAGE.StageCurTyp;
5118: l_party_max_score NUMBER;
5119: l_ps_max_score NUMBER;
5120: l_contact_max_score NUMBER;
5121: l_cpt_max_score NUMBER;
5122: l_match_contact_list HZ_PARTY_SEARCH.IDList;
5123: l_cnt NUMBER:=1;
5124:
5125:
5126: BEGIN
5142: l_match_str := ' AND ';
5143: ELSIF p_match_type = 'OR' THEN
5144: l_match_str := ' OR ';
5145: END IF;
5146: 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);
5147: g_score_until_thresh := false;
5148: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5149:
5150: -- ************************************************************
5150: -- ************************************************************
5151: -- STEP 2. Setup of intermedia query strings for Acquisition query
5152: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5153: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5154: 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);
5155:
5156: -- Setup Search Context ID
5157: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5158: x_search_ctx_id := l_search_ctx_id;
5289:
5290: PROCEDURE get_matching_contact_points (
5291: p_rule_id IN NUMBER,
5292: p_party_id IN NUMBER,
5293: p_contact_point_list IN HZ_PARTY_SEARCH.CONTACT_POINT_LIST,
5294: p_restrict_sql IN VARCHAR2,
5295: p_match_type IN VARCHAR2,
5296: p_dup_contact_point_id IN NUMBER,
5297: x_search_ctx_id OUT NUMBER,
5319: l_cpt_ps_id NUMBER;
5320: l_cpt_contact_id NUMBER;
5321: l_contact_pt_id NUMBER;
5322: l_cpt_type VARCHAR2(100);
5323: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5324: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5325: H_SCORE HZ_PARTY_SEARCH.IDList;
5326:
5327: cnt NUMBER :=0;
5320: l_cpt_contact_id NUMBER;
5321: l_contact_pt_id NUMBER;
5322: l_cpt_type VARCHAR2(100);
5323: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5324: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5325: H_SCORE HZ_PARTY_SEARCH.IDList;
5326:
5327: cnt NUMBER :=0;
5328: l_party_max_score NUMBER;
5321: l_contact_pt_id NUMBER;
5322: l_cpt_type VARCHAR2(100);
5323: H_PARTY_ID HZ_PARTY_SEARCH.IDList;
5324: H_CONTACT_POINT_ID HZ_PARTY_SEARCH.IDList;
5325: H_SCORE HZ_PARTY_SEARCH.IDList;
5326:
5327: cnt NUMBER :=0;
5328: l_party_max_score NUMBER;
5329: l_ps_max_score NUMBER;
5350: l_match_str := ' AND ';
5351: ELSIF p_match_type = 'OR' THEN
5352: l_match_str := ' OR ';
5353: END IF;
5354: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5355: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5356: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5357: g_score_until_thresh := false;
5358: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5351: ELSIF p_match_type = 'OR' THEN
5352: l_match_str := ' OR ';
5353: END IF;
5354: l_entered_max_score:= init_search(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,
5355: HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST, HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,
5356: p_contact_point_list,l_match_str, l_party_max_score, l_ps_max_score, l_contact_max_score, l_cpt_max_score);
5357: g_score_until_thresh := false;
5358: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5359:
5359:
5360: -- ************************************************************
5361: -- STEP 2. Setup of intermedia query strings for Acquisition query
5362: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5363: 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);
5364:
5365: -- Setup Search Context ID
5366: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5367: x_search_ctx_id := l_search_ctx_id;
5434:
5435: PROCEDURE get_score_details (
5436: p_rule_id IN NUMBER,
5437: p_party_id IN NUMBER,
5438: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5439: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5440: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5441: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5442: x_search_ctx_id IN OUT NUMBER
5435: PROCEDURE get_score_details (
5436: p_rule_id IN NUMBER,
5437: p_party_id IN NUMBER,
5438: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5439: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5440: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5441: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5442: x_search_ctx_id IN OUT NUMBER
5443: ) IS
5436: p_rule_id IN NUMBER,
5437: p_party_id IN NUMBER,
5438: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5439: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5440: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5441: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5442: x_search_ctx_id IN OUT NUMBER
5443: ) IS
5444:
5437: p_party_id IN NUMBER,
5438: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
5439: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
5440: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
5441: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
5442: x_search_ctx_id IN OUT NUMBER
5443: ) IS
5444:
5445: -- Strings to hold the generated Intermedia query strings
5476: l_TX5 VARCHAR2(2000);
5477: l_max_TX5 VARCHAR2(2000);
5478: l_TX40 VARCHAR2(2000);
5479: l_max_TX40 VARCHAR2(2000);
5480: H_SCORES HZ_PARTY_SEARCH.score_list;
5481:
5482: l_score NUMBER;
5483: l_match_idx NUMBER;
5484: l_idx NUMBER;
5647: ************************************************/
5648:
5649: PROCEDURE get_party_for_search (
5650: p_party_id IN NUMBER,
5651: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5652: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5653: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5654: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5655: ) IS
5648:
5649: PROCEDURE get_party_for_search (
5650: p_party_id IN NUMBER,
5651: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5652: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5653: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5654: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5655: ) IS
5656: l_party_id NUMBER;
5649: PROCEDURE get_party_for_search (
5650: p_party_id IN NUMBER,
5651: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5652: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5653: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5654: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5655: ) IS
5656: l_party_id NUMBER;
5657: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5650: p_party_id IN NUMBER,
5651: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5652: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5653: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5654: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5655: ) IS
5656: l_party_id NUMBER;
5657: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5658: l_contact_ids HZ_PARTY_SEARCH.IDList;
5653: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5654: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5655: ) IS
5656: l_party_id NUMBER;
5657: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5658: l_contact_ids HZ_PARTY_SEARCH.IDList;
5659: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5660: ps NUMBER :=1;
5661: cpt NUMBER :=1;
5654: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5655: ) IS
5656: l_party_id NUMBER;
5657: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5658: l_contact_ids HZ_PARTY_SEARCH.IDList;
5659: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5660: ps NUMBER :=1;
5661: cpt NUMBER :=1;
5662: ct NUMBER :=1;
5655: ) IS
5656: l_party_id NUMBER;
5657: l_party_site_ids HZ_PARTY_SEARCH.IDList;
5658: l_contact_ids HZ_PARTY_SEARCH.IDList;
5659: l_contact_pt_ids HZ_PARTY_SEARCH.IDList;
5660: ps NUMBER :=1;
5661: cpt NUMBER :=1;
5662: ct NUMBER :=1;
5663: l_use_contact_info varchar2(1);
5764: END get_party_for_search;
5765:
5766: PROCEDURE get_search_criteria (
5767: p_party_id IN NUMBER,
5768: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5769: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5770: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5771: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5772: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5765:
5766: PROCEDURE get_search_criteria (
5767: p_party_id IN NUMBER,
5768: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5769: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5770: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5771: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5772: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5773: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5766: PROCEDURE get_search_criteria (
5767: p_party_id IN NUMBER,
5768: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5769: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5770: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5771: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5772: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5773: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5774: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5767: p_party_id IN NUMBER,
5768: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5769: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5770: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5771: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5772: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5773: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5774: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5775: ) IS
5768: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5769: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5770: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5771: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5772: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5773: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5774: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5775: ) IS
5776: BEGIN
5769: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5770: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5771: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5772: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5773: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5774: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5775: ) IS
5776: BEGIN
5777:
5770: p_contact_pt_ids IN HZ_PARTY_SEARCH.IDList,
5771: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type,
5772: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list,
5773: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list,
5774: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
5775: ) IS
5776: BEGIN
5777:
5778: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5822: ************************************************/
5823:
5824: PROCEDURE get_party_rec (
5825: p_party_id IN NUMBER,
5826: x_party_search_rec OUT NOCOPY HZ_PARTY_SEARCH.party_search_rec_type
5827: ) IS
5828: l_party_type VARCHAR2(255);
5829: BEGIN
5830:
5885: the party site search record structure
5886: ************************************************/
5887:
5888: PROCEDURE get_party_site_rec (
5889: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5890: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5891: ) IS
5892: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5893: SELECT party_site_id
5886: ************************************************/
5887:
5888: PROCEDURE get_party_site_rec (
5889: p_party_site_ids IN HZ_PARTY_SEARCH.IDList,
5890: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5891: ) IS
5892: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5893: SELECT party_site_id
5894: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
5941: the contact search record structure
5942: ************************************************/
5943:
5944: PROCEDURE get_contact_rec (
5945: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5946: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5947: ) IS
5948: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5949: SELECT org_contact_id
5942: ************************************************/
5943:
5944: PROCEDURE get_contact_rec (
5945: p_contact_ids IN HZ_PARTY_SEARCH.IDList,
5946: x_contact_list OUT NOCOPY HZ_PARTY_SEARCH.contact_list
5947: ) IS
5948: CURSOR c_contacts(cp_org_contact_id NUMBER) IS
5949: SELECT org_contact_id
5950: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS, HZ_PERSON_PROFILES
5999: the contact point search record structure
6000: ************************************************/
6001:
6002: PROCEDURE get_contact_point_rec (
6003: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
6004: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6005: ) IS
6006:
6007: -- Cursor to fetch primary contact points for party
6000: ************************************************/
6001:
6002: PROCEDURE get_contact_point_rec (
6003: p_contact_point_ids IN HZ_PARTY_SEARCH.IDList,
6004: x_contact_point_list OUT NOCOPY HZ_PARTY_SEARCH.contact_point_list
6005: ) IS
6006:
6007: -- Cursor to fetch primary contact points for party
6008: CURSOR c_cpts(cp_contact_point_id NUMBER) IS
6056: ************************************************/
6057:
6058: PROCEDURE MAP_PARTY_REC(
6059: p_search_ctx IN BOOLEAN,
6060: p_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6061: x_entered_max_score OUT NUMBER,
6062: x_stage_rec IN OUT NOCOPY HZ_PARTY_STAGE.party_stage_rec_type
6063: ) IS
6064: tmp VARCHAR2(4000);
6198: ************************************************/
6199:
6200: PROCEDURE MAP_PARTY_SITE_REC(
6201: p_search_ctx IN BOOLEAN,
6202: p_search_list IN HZ_PARTY_SEARCH.party_site_list,
6203: x_entered_max_score OUT NUMBER,
6204: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.party_site_stage_list
6205: ) IS
6206: l_current_max_score NUMBER;
6316: ************************************************/
6317:
6318: PROCEDURE MAP_CONTACT_REC(
6319: p_search_ctx IN BOOLEAN,
6320: p_search_list IN HZ_PARTY_SEARCH.contact_list,
6321: x_entered_max_score OUT NUMBER,
6322: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_stage_list
6323: ) IS
6324: l_current_max_score NUMBER;
6396: ************************************************/
6397:
6398: PROCEDURE MAP_CONTACT_POINT_REC(
6399: p_search_ctx IN BOOLEAN,
6400: p_search_list IN HZ_PARTY_SEARCH.contact_point_list,
6401: x_entered_max_score OUT NUMBER,
6402: x_stage_list IN OUT NOCOPY HZ_PARTY_STAGE.contact_pt_stage_list
6403: ) IS
6404: l_current_max_score NUMBER;
6487: 2. Contact Point Type is not null for each condition
6488: ************************************************/
6489:
6490: FUNCTION check_prim_cond(
6491: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6492: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6493: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6494: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6495: RETURN BOOLEAN IS
6488: ************************************************/
6489:
6490: FUNCTION check_prim_cond(
6491: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6492: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6493: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6494: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6495: RETURN BOOLEAN IS
6496: BEGIN
6489:
6490: FUNCTION check_prim_cond(
6491: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6492: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6493: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6494: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6495: RETURN BOOLEAN IS
6496: BEGIN
6497: FOR I IN 1..p_contact_point_list.COUNT LOOP
6490: FUNCTION check_prim_cond(
6491: p_party_search_rec IN HZ_PARTY_SEARCH.party_search_rec_type,
6492: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6493: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6494: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list)
6495: RETURN BOOLEAN IS
6496: BEGIN
6497: FOR I IN 1..p_contact_point_list.COUNT LOOP
6498: IF p_contact_point_list(I).CONTACT_POINT_TYPE IS NULL AND (
6550: has party site criteria.
6551: ************************************************/
6552:
6553: PROCEDURE check_party_site_cond(
6554: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6555: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6556: x_secondary OUT BOOLEAN,
6557: x_primary OUT BOOLEAN
6558: ) IS
6551: ************************************************/
6552:
6553: PROCEDURE check_party_site_cond(
6554: p_party_site_list IN HZ_PARTY_SEARCH.party_site_list,
6555: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6556: x_secondary OUT BOOLEAN,
6557: x_primary OUT BOOLEAN
6558: ) IS
6559: BEGIN
6604: has contact criteria.
6605: ************************************************/
6606:
6607: PROCEDURE check_contact_cond(
6608: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6609: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6610: x_secondary OUT BOOLEAN,
6611: x_primary OUT BOOLEAN
6612: ) IS
6605: ************************************************/
6606:
6607: PROCEDURE check_contact_cond(
6608: p_contact_list IN HZ_PARTY_SEARCH.contact_list,
6609: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6610: x_secondary OUT BOOLEAN,
6611: x_primary OUT BOOLEAN
6612: ) IS
6613: BEGIN
6658: has valid contact point criteria.
6659: ************************************************/
6660:
6661: PROCEDURE check_contact_point_cond(
6662: p_contact_point_list IN HZ_PARTY_SEARCH.contact_point_list,
6663: x_secondary OUT BOOLEAN,
6664: x_primary OUT BOOLEAN
6665: ) IS
6666: BEGIN