eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HZ_MATCH_RULE_5 dependencies on CONTAINS

Line 70: p_contains_str VARCHAR2) RETURN NUMBER IS

66: END IF;
67: END ;
68: FUNCTION check_estimate_hits (
69: p_entity VARCHAR2,
70: p_contains_str VARCHAR2) RETURN NUMBER IS
71:
72: ustatus VARCHAR2(255);
73: dstatus VARCHAR2(255);
74: l_bool BOOLEAN;

Line 83: g_index_owner||'.'||'HZ_STAGE_PARTIES_T1',p_contains_str, false);

79: END IF;
80: IF p_entity='PARTY' THEN
81:
82: l_hits := CTX_QUERY.count_hits(
83: g_index_owner||'.'||'HZ_STAGE_PARTIES_T1',p_contains_str, false);
84: ELSIF p_entity='PARTY_SITES' THEN
85: l_hits := CTX_QUERY.count_hits(
86: g_index_owner||'.'||'HZ_STAGE_PARTY_SITES_T1',p_contains_str, false);
87: ELSIF p_entity='CONTACTS' THEN

Line 86: g_index_owner||'.'||'HZ_STAGE_PARTY_SITES_T1',p_contains_str, false);

82: l_hits := CTX_QUERY.count_hits(
83: g_index_owner||'.'||'HZ_STAGE_PARTIES_T1',p_contains_str, false);
84: ELSIF p_entity='PARTY_SITES' THEN
85: l_hits := CTX_QUERY.count_hits(
86: g_index_owner||'.'||'HZ_STAGE_PARTY_SITES_T1',p_contains_str, false);
87: ELSIF p_entity='CONTACTS' THEN
88: l_hits := CTX_QUERY.count_hits(
89: g_index_owner||'.'||'HZ_STAGE_CONTACT_T1',p_contains_str, false);
90: ELSIF p_entity='CONTACT_POINTS' THEN

Line 89: g_index_owner||'.'||'HZ_STAGE_CONTACT_T1',p_contains_str, false);

85: l_hits := CTX_QUERY.count_hits(
86: g_index_owner||'.'||'HZ_STAGE_PARTY_SITES_T1',p_contains_str, false);
87: ELSIF p_entity='CONTACTS' THEN
88: l_hits := CTX_QUERY.count_hits(
89: g_index_owner||'.'||'HZ_STAGE_CONTACT_T1',p_contains_str, false);
90: ELSIF p_entity='CONTACT_POINTS' THEN
91: l_hits := CTX_QUERY.count_hits(
92: g_index_owner||'.'||'HZ_STAGE_CPT_T1',p_contains_str, false);
93: END IF;

Line 92: g_index_owner||'.'||'HZ_STAGE_CPT_T1',p_contains_str, false);

88: l_hits := CTX_QUERY.count_hits(
89: g_index_owner||'.'||'HZ_STAGE_CONTACT_T1',p_contains_str, false);
90: ELSIF p_entity='CONTACT_POINTS' THEN
91: l_hits := CTX_QUERY.count_hits(
92: g_index_owner||'.'||'HZ_STAGE_CPT_T1',p_contains_str, false);
93: END IF;
94: RETURN floor(l_hits/2) ;
95:
96:

Line 771: l_party_contains_str VARCHAR2(32000);

767: p_party_max_score NUMBER,
768: p_denorm_max_score NUMBER,
769: p_non_denorm_max_score NUMBER,
770: p_threshold NUMBER) RETURN VARCHAR2 IS
771: l_party_contains_str VARCHAR2(32000);
772: l_party_filter VARCHAR2(1) := null;
773: l_prim_temp VARCHAR2(4000);
774: l_denorm_str VARCHAR2(4000);
775: BEGIN

Line 815: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

811:
812: IF l_denorm_str IS NOT NULL THEN
813: IF (p_non_denorm_max_score+50)>=p_threshold THEN
814: l_denorm_str := NULL;
815: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
816: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
817: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
818: l_denorm_str := NULL;
819: END IF;

Line 817: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);

813: IF (p_non_denorm_max_score+50)>=p_threshold THEN
814: l_denorm_str := NULL;
815: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
816: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
817: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
818: l_denorm_str := NULL;
819: END IF;
820: ELSE
821: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

Line 821: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

817: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
818: l_denorm_str := NULL;
819: END IF;
820: ELSE
821: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
822: END IF;
823: IF lengthb(l_party_contains_str) > 4000 THEN
824: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
825: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');

Line 823: IF lengthb(l_party_contains_str) > 4000 THEN

819: END IF;
820: ELSE
821: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
822: END IF;
823: IF lengthb(l_party_contains_str) > 4000 THEN
824: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
825: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
826: FND_MSG_PUB.ADD;
827: RAISE FND_API.G_EXC_ERROR;

Line 829: IF (p_match_str = ' AND ' OR p_party_max_score

825: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
826: FND_MSG_PUB.ADD;
827: RAISE FND_API.G_EXC_ERROR;
828: END IF;
829: IF (p_match_str = ' AND ' OR p_party_max_score 830: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
831: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is an AND between these strings ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
832: END IF;
833: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

Line 831: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is an AND between these strings ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

827: RAISE FND_API.G_EXC_ERROR;
828: END IF;
829: IF (p_match_str = ' AND ' OR p_party_max_score 830: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
831: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is an AND between these strings ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
832: END IF;
833: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
834: hz_utility_v2pub.debug(p_message=>'l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
835: END IF;

Line 834: hz_utility_v2pub.debug(p_message=>'l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

830: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
831: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is an AND between these strings ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
832: END IF;
833: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
834: hz_utility_v2pub.debug(p_message=>'l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
835: END IF;
836: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
837: hz_utility_v2pub.debug(p_message=>'p_denorm_str = '||p_denorm_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
838: END IF;

Line 839: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';

835: END IF;
836: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
837: hz_utility_v2pub.debug(p_message=>'p_denorm_str = '||p_denorm_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
838: END IF;
839: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
840: ELSE
841: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
842: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
843: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line 842: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

838: END IF;
839: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
840: ELSE
841: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
842: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
843: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
844: END IF;
845: RETURN l_party_contains_str;
846: END IF;

Line 843: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

839: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
840: ELSE
841: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
842: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
843: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
844: END IF;
845: RETURN l_party_contains_str;
846: END IF;
847: END;

Line 845: RETURN l_party_contains_str;

841: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
842: hz_utility_v2pub.debug(p_message=>'party contains string returned by init_search is ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
843: hz_utility_v2pub.debug(p_message=>'l_party_contains_str',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
844: END IF;
845: RETURN l_party_contains_str;
846: END IF;
847: END;
848: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
849: l_contains_str VARCHAR2(32000);

Line 849: l_contains_str VARCHAR2(32000);

845: RETURN l_party_contains_str;
846: END IF;
847: END;
848: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
849: l_contains_str VARCHAR2(32000);
850: l_contains_str_temp VARCHAR2(32000);
851: l_den_contains_str VARCHAR2(32000);
852: l_den_contains_str_temp VARCHAR2(32000);
853: l_filter_str VARCHAR2(4000) := null;

Line 850: l_contains_str_temp VARCHAR2(32000);

846: END IF;
847: END;
848: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
849: l_contains_str VARCHAR2(32000);
850: l_contains_str_temp VARCHAR2(32000);
851: l_den_contains_str VARCHAR2(32000);
852: l_den_contains_str_temp VARCHAR2(32000);
853: l_filter_str VARCHAR2(4000) := null;
854: l_prim_temp VARCHAR2(4000) := null;

Line 851: l_den_contains_str VARCHAR2(32000);

847: END;
848: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
849: l_contains_str VARCHAR2(32000);
850: l_contains_str_temp VARCHAR2(32000);
851: l_den_contains_str VARCHAR2(32000);
852: l_den_contains_str_temp VARCHAR2(32000);
853: l_filter_str VARCHAR2(4000) := null;
854: l_prim_temp VARCHAR2(4000) := null;
855: l_prim_temp_den VARCHAR2(4000) := null;

Line 852: l_den_contains_str_temp VARCHAR2(32000);

848: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
849: l_contains_str VARCHAR2(32000);
850: l_contains_str_temp VARCHAR2(32000);
851: l_den_contains_str VARCHAR2(32000);
852: l_den_contains_str_temp VARCHAR2(32000);
853: l_filter_str VARCHAR2(4000) := null;
854: l_prim_temp VARCHAR2(4000) := null;
855: l_prim_temp_den VARCHAR2(4000) := null;
856: BEGIN

Line 862: -- Dynamic setup of party site contains str

858: hz_utility_v2pub.debug(p_message=>'INIT_PARTY_SITES_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
859: END IF;
860: x_denorm_str := NULL;
861:
862: -- Dynamic setup of party site contains str
863: --
864: -- For each primary transformation add to intermedia query if it
865: -- is not null
866: FOR I IN 1..g_party_site_stage_list.COUNT LOOP

Line 867: l_contains_str_temp := null;

863: --
864: -- For each primary transformation add to intermedia query if it
865: -- is not null
866: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
867: l_contains_str_temp := null;
868: l_den_contains_str_temp := null;
869: l_filter_str := null;
870:
871: -- Setup query string for POSTAL_CODE

Line 868: l_den_contains_str_temp := null;

864: -- For each primary transformation add to intermedia query if it
865: -- is not null
866: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
867: l_contains_str_temp := null;
868: l_den_contains_str_temp := null;
869: l_filter_str := null;
870:
871: -- Setup query string for POSTAL_CODE
872: l_prim_temp := null;

Line 878: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);

874: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
875: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
876: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
877: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
878: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
879: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
880:
881: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
882: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);

Line 879: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);

875: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
876: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
877: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
878: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
879: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
880:
881: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
882: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
883: END LOOP;

Line 881: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);

877: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
878: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
879: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
880:
881: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
882: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
883: END LOOP;
884: -- Add the search criteria to query string
885: IF lengthb(l_contains_str) > 4000 THEN

Line 882: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);

878: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
879: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
880:
881: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
882: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
883: END LOOP;
884: -- Add the search criteria to query string
885: IF lengthb(l_contains_str) > 4000 THEN
886: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 885: IF lengthb(l_contains_str) > 4000 THEN

881: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
882: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
883: END LOOP;
884: -- Add the search criteria to query string
885: IF lengthb(l_contains_str) > 4000 THEN
886: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
887: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
888: FND_MSG_PUB.ADD;
889: RAISE FND_API.G_EXC_ERROR;

Line 891: x_denorm_str := l_den_contains_str;

887: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
888: FND_MSG_PUB.ADD;
889: RAISE FND_API.G_EXC_ERROR;
890: END IF;
891: x_denorm_str := l_den_contains_str;
892: RETURN l_contains_str;
893: END;
894:
895: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS

Line 892: RETURN l_contains_str;

888: FND_MSG_PUB.ADD;
889: RAISE FND_API.G_EXC_ERROR;
890: END IF;
891: x_denorm_str := l_den_contains_str;
892: RETURN l_contains_str;
893: END;
894:
895: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
896: l_contains_str VARCHAR2(32000);

Line 896: l_contains_str VARCHAR2(32000);

892: RETURN l_contains_str;
893: END;
894:
895: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
896: l_contains_str VARCHAR2(32000);
897: l_contains_str_temp VARCHAR2(32000);
898: l_den_contains_str VARCHAR2(32000);
899: l_den_contains_str_temp VARCHAR2(32000);
900: l_filter_str VARCHAR2(4000) := null;

Line 897: l_contains_str_temp VARCHAR2(32000);

893: END;
894:
895: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
896: l_contains_str VARCHAR2(32000);
897: l_contains_str_temp VARCHAR2(32000);
898: l_den_contains_str VARCHAR2(32000);
899: l_den_contains_str_temp VARCHAR2(32000);
900: l_filter_str VARCHAR2(4000) := null;
901: l_prim_temp VARCHAR2(4000) := null;

Line 898: l_den_contains_str VARCHAR2(32000);

894:
895: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
896: l_contains_str VARCHAR2(32000);
897: l_contains_str_temp VARCHAR2(32000);
898: l_den_contains_str VARCHAR2(32000);
899: l_den_contains_str_temp VARCHAR2(32000);
900: l_filter_str VARCHAR2(4000) := null;
901: l_prim_temp VARCHAR2(4000) := null;
902: l_prim_temp_den VARCHAR2(4000) := null;

Line 899: l_den_contains_str_temp VARCHAR2(32000);

895: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
896: l_contains_str VARCHAR2(32000);
897: l_contains_str_temp VARCHAR2(32000);
898: l_den_contains_str VARCHAR2(32000);
899: l_den_contains_str_temp VARCHAR2(32000);
900: l_filter_str VARCHAR2(4000) := null;
901: l_prim_temp VARCHAR2(4000) := null;
902: l_prim_temp_den VARCHAR2(4000) := null;
903: BEGIN

Line 912: l_contains_str VARCHAR2(32000);

908: RETURN NULL;
909: END;
910:
911: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
912: l_contains_str VARCHAR2(32000);
913: l_contains_str_temp VARCHAR2(32000);
914: l_den_contains_str VARCHAR2(32000);
915: l_den_contains_str_temp VARCHAR2(32000);
916: l_filter_str VARCHAR2(4000) := null;

Line 913: l_contains_str_temp VARCHAR2(32000);

909: END;
910:
911: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
912: l_contains_str VARCHAR2(32000);
913: l_contains_str_temp VARCHAR2(32000);
914: l_den_contains_str VARCHAR2(32000);
915: l_den_contains_str_temp VARCHAR2(32000);
916: l_filter_str VARCHAR2(4000) := null;
917: l_prim_temp VARCHAR2(4000) := null;

Line 914: l_den_contains_str VARCHAR2(32000);

910:
911: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
912: l_contains_str VARCHAR2(32000);
913: l_contains_str_temp VARCHAR2(32000);
914: l_den_contains_str VARCHAR2(32000);
915: l_den_contains_str_temp VARCHAR2(32000);
916: l_filter_str VARCHAR2(4000) := null;
917: l_prim_temp VARCHAR2(4000) := null;
918: l_prim_temp_den VARCHAR2(4000) := null;

Line 915: l_den_contains_str_temp VARCHAR2(32000);

911: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
912: l_contains_str VARCHAR2(32000);
913: l_contains_str_temp VARCHAR2(32000);
914: l_den_contains_str VARCHAR2(32000);
915: l_den_contains_str_temp VARCHAR2(32000);
916: l_filter_str VARCHAR2(4000) := null;
917: l_prim_temp VARCHAR2(4000) := null;
918: l_prim_temp_den VARCHAR2(4000) := null;
919: BEGIN

Line 932: p_party_contains_str VARCHAR2,

928: p_dup_party_id NUMBER,
929: p_restrict_sql VARCHAR2,
930: p_match_str VARCHAR2,
931: p_search_merged VARCHAR2,
932: p_party_contains_str VARCHAR2,
933: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
934: l_sqlstr VARCHAR2(4000);
935: l_search_merged VARCHAR2(1);
936: BEGIN

Line 949: IF p_restrict_sql IS NULL AND NOT g_other_party_level_attribs AND NOT (p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1) THEN

945: END IF;
946: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
947: hz_utility_v2pub.debug(p_message=>'Search Merged Flag - '||l_search_merged,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
948: END IF;
949: IF p_restrict_sql IS NULL AND NOT g_other_party_level_attribs AND NOT (p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1) THEN
950: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
951: hz_utility_v2pub.debug(p_message=>'Restrict SQL is NULL and other conditions met to OPEN x_cursor',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
952: END IF;
953: OPEN x_cursor FOR

Line 980: IF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN

976: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
977: l_sqlstr := l_sqlstr || ' (TX36 like :TX36||'' %'' ';
978: l_sqlstr := l_sqlstr || ' )) ';
979: END IF;
980: IF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
981: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
982: hz_utility_v2pub.debug(p_message=>'p_party_contains_str string is NOT NULL and other conditions met',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
983: END IF;
984: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';

Line 982: hz_utility_v2pub.debug(p_message=>'p_party_contains_str string is NOT NULL and other conditions met',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

978: l_sqlstr := l_sqlstr || ' )) ';
979: END IF;
980: IF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
981: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
982: hz_utility_v2pub.debug(p_message=>'p_party_contains_str string is NOT NULL and other conditions met',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
983: END IF;
984: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
985: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX11||'' %'' ';
986: l_sqlstr := l_sqlstr || ' )) ';

Line 1003: IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN

999: END IF;
1000: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1001: hz_utility_v2pub.debug(p_message=>'l_sqlstr is - '||l_sqlstr,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1002: END IF;
1003: IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1004: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1005: hz_utility_v2pub.debug(p_message=>'IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1006: END IF;
1007: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id

Line 1005: hz_utility_v2pub.debug(p_message=>'IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

1001: hz_utility_v2pub.debug(p_message=>'l_sqlstr is - '||l_sqlstr,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1002: END IF;
1003: IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1004: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1005: hz_utility_v2pub.debug(p_message=>'IF g_other_party_level_attribs AND p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1006: END IF;
1007: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id
1008: ,g_party_stage_rec.TX36
1009: ,g_party_stage_rec.TX36

Line 1021: ELSIF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN

1017: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id
1018: ,g_party_stage_rec.TX36
1019: ,g_party_stage_rec.TX36
1020: ;
1021: ELSIF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1022: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1023: hz_utility_v2pub.debug(p_message=>'ELSIF p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1024: END IF;
1025: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id

Line 1023: hz_utility_v2pub.debug(p_message=>'ELSIF p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

1019: ,g_party_stage_rec.TX36
1020: ;
1021: ELSIF p_party_contains_str IS NOT NULL AND instrb(p_party_contains_str,'D_PS')>0 AND g_party_site_stage_list.COUNT=1 THEN
1022: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1023: hz_utility_v2pub.debug(p_message=>'ELSIF p_party_contains_str IS NOT NULL AND ...',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1024: END IF;
1025: OPEN x_cursor FOR l_sqlstr USING g_party_stage_rec.TX8,g_party_stage_rec.TX36,g_party_stage_rec.TX36,p_dup_party_id,p_dup_party_id
1026: ,g_party_site_stage_list(1).TX11
1027: ,g_party_site_stage_list(1).TX11

Line 1040: p_contains_str VARCHAR2,

1036: END;
1037: PROCEDURE open_party_cursor(
1038: p_dup_party_id NUMBER,
1039: p_restrict_sql VARCHAR2,
1040: p_contains_str VARCHAR2,
1041: p_search_ctx_id NUMBER,
1042: p_match_str VARCHAR2,
1043: p_search_merged VARCHAR2,
1044: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS

Line 1059: IF p_contains_str IS NULL THEN

1055: l_search_merged := 'N';
1056: ELSE
1057: l_search_merged := p_search_merged;
1058: END IF;
1059: IF p_contains_str IS NULL THEN
1060: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1061: hz_utility_v2pub.debug(p_message=>'part contains string is null',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1062: END IF;
1063: OPEN x_cursor FOR

Line 1061: hz_utility_v2pub.debug(p_message=>'part contains string is null',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

1057: l_search_merged := p_search_merged;
1058: END IF;
1059: IF p_contains_str IS NULL THEN
1060: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1061: hz_utility_v2pub.debug(p_message=>'part contains string is null',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
1062: END IF;
1063: OPEN x_cursor FOR
1064: SELECT PARTY_ID , TX2, TX8, TX4
1065: FROM HZ_STAGED_PARTIES stage

Line 1078: WHERE contains( concat_col, p_contains_str)>0

1074: END IF;
1075: OPEN x_cursor FOR
1076: SELECT /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ PARTY_ID , TX2, TX8, TX4
1077: FROM HZ_STAGED_PARTIES stage
1078: WHERE contains( concat_col, p_contains_str)>0
1079: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1080: AND( (l_search_merged ='Y' )
1081: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1082: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )

Line 1091: WHERE contains( concat_col, p_contains_str)>0

1087: END IF;
1088: OPEN x_cursor FOR
1089: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX2, TX8, TX4
1090: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1091: WHERE contains( concat_col, p_contains_str)>0
1092: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1093: AND d.party_id = stage.party_id
1094: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1095: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)

Line 1116: ' WHERE contains( concat_col, :cont)>0'||

1112: END IF;
1113: IF p_search_ctx_id IS NULL THEN
1114: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX2, TX8, TX4'||
1115: ' FROM HZ_STAGED_PARTIES stage'||
1116: ' WHERE contains( concat_col, :cont)>0'||
1117: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1118: ' AND ('||p_restrict_sql||')' ||
1119: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1120: IF l_search_merged = 'Y' THEN

Line 1128: output_long_strings('Party Contains String = '||p_contains_str);

1124: ELSE
1125: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1126: END IF;
1127: output_long_strings('----------------------------------------------------------');
1128: output_long_strings('Party Contains String = '||p_contains_str);
1129: output_long_strings('Restrict Sql = '||p_restrict_sql);
1130: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1131: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1132: ,p_dup_party_id, p_dup_party_id;

Line 1130: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1126: END IF;
1127: output_long_strings('----------------------------------------------------------');
1128: output_long_strings('Party Contains String = '||p_contains_str);
1129: output_long_strings('Restrict Sql = '||p_restrict_sql);
1130: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1131: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1132: ,p_dup_party_id, p_dup_party_id;
1133: END IF;
1134: END IF;

Line 1161: p_contains_str VARCHAR2,

1157: PROCEDURE open_party_site_cursor(
1158: p_dup_party_id NUMBER,
1159: p_party_id NUMBER,
1160: p_restrict_sql VARCHAR2,
1161: p_contains_str VARCHAR2,
1162: p_search_ctx_id NUMBER,
1163: p_search_merged VARCHAR2,
1164: p_search_rel_sites VARCHAR2,
1165: p_person_api VARCHAR2,

Line 1193: WHERE contains( concat_col, p_contains_str)>0

1189: IF p_search_rel_sites = 'N' THEN
1190: OPEN x_cursor FOR
1191: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX4, TX3
1192: FROM HZ_STAGED_PARTY_SITES stage
1193: WHERE contains( concat_col, p_contains_str)>0
1194: AND( (l_search_merged ='Y' )
1195: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1196: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1197: AND stage.party_id = p_party_id;

Line 1202: WHERE contains( concat_col, p_contains_str)>0

1198: ELSE
1199: OPEN x_cursor FOR
1200: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX4, TX3
1201: FROM HZ_STAGED_PARTY_SITES stage
1202: WHERE contains( concat_col, p_contains_str)>0
1203: AND( (l_search_merged ='Y' )
1204: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1205: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1206: AND stage.party_id = p_party_id

Line 1210: WHERE contains( concat_col, p_contains_str)>0

1206: AND stage.party_id = p_party_id
1207: UNION
1208: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX4, TX3
1209: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1210: WHERE contains( concat_col, p_contains_str)>0
1211: AND( (l_search_merged ='Y' )
1212: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1213: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1214: AND r.object_id = p_party_id

Line 1232: WHERE contains( concat_col, p_contains_str)>0

1228: END IF;
1229: OPEN x_cursor FOR
1230: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX4, TX3
1231: FROM HZ_STAGED_PARTY_SITES stage
1232: WHERE contains( concat_col, p_contains_str)>0
1233: AND EXISTS (
1234: SELECT 1 FROM HZ_STAGED_PARTIES p
1235: WHERE p.PARTY_ID = stage.PARTY_ID
1236: AND( (l_search_merged ='Y' )

Line 1252: WHERE contains( concat_col, p_contains_str)>0

1248: IF p_person_api = 'Y' THEN
1249: OPEN x_cursor FOR
1250: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX4, TX3
1251: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1252: WHERE contains( concat_col, p_contains_str)>0
1253: AND( (l_search_merged ='Y' )
1254: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1255: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1256: AND d.search_context_id = p_search_ctx_id

Line 1262: WHERE contains( concat_col, p_contains_str)>0

1258: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1259: UNION
1260: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX4, TX3
1261: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1262: WHERE contains( concat_col, p_contains_str)>0
1263: AND( (l_search_merged ='Y' )
1264: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1265: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1266: AND d.search_context_id = p_search_ctx_id

Line 1275: WHERE contains( concat_col, p_contains_str)>0

1271: ELSE
1272: OPEN x_cursor FOR
1273: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX4, TX3
1274: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1275: WHERE contains( concat_col, p_contains_str)>0
1276: AND( (l_search_merged ='Y' )
1277: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1278: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1279: AND d.search_context_id = p_search_ctx_id

Line 1320: ' WHERE contains( concat_col, :cont)>0'||

1316: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1317: END IF;
1318: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1319: ' FROM HZ_STAGED_PARTY_SITES stage'||
1320: ' WHERE contains( concat_col, :cont)>0'||
1321: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1322: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1323: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1324: ' AND (ORG_CONTACT_ID IS NULL ' ||

Line 1330: ' WHERE contains( concat_col, :cont)>0'||

1326: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1327: ' UNION ' ||
1328: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1329: ' FROM HZ_STAGED_PARTY_SITES stage'||
1330: ' WHERE contains( concat_col, :cont)>0'||
1331: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1332: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1333: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1334: ' AND ORG_CONTACT_ID IN ' ||

Line 1340: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1336: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1337: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1338: ' and ('||p_restrict_sql1|| ') )' ||
1339: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1340: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1341: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1342: ELSE
1343: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1344: ' FROM HZ_STAGED_PARTY_SITES stage'||

Line 1341: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1337: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1338: ' and ('||p_restrict_sql1|| ') )' ||
1339: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1340: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1341: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1342: ELSE
1343: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1344: ' FROM HZ_STAGED_PARTY_SITES stage'||
1345: ' WHERE contains( concat_col, :cont)>0'||

Line 1345: ' WHERE contains( concat_col, :cont)>0'||

1341: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1342: ELSE
1343: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1344: ' FROM HZ_STAGED_PARTY_SITES stage'||
1345: ' WHERE contains( concat_col, :cont)>0'||
1346: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1347: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1348: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1349: ' AND EXISTS ('||

Line 1355: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1351: ' WHERE p.party_id = stage.party_id ' ||
1352: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1353: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1354: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1355: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1356: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1357: ,p_dup_party_id, p_dup_party_id;
1358: END IF;
1359: ELSE

Line 1362: ' WHERE contains( concat_col, :cont)>0'||

1358: END IF;
1359: ELSE
1360: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX4, TX3'||
1361: ' FROM HZ_STAGED_PARTY_SITES stage'||
1362: ' WHERE contains( concat_col, :cont)>0'||
1363: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1364: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1365: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1366: ' AND EXISTS ('||

Line 1372: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1368: ' WHERE p.party_id = stage.party_id ' ||
1369: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1370: ' AND ('||p_restrict_sql||')' ||
1371: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1372: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1373: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1374: ,p_dup_party_id, p_dup_party_id;
1375: END IF;
1376: END IF;

Line 1378: output_long_strings('Party Site Contains String = '||p_contains_str);

1374: ,p_dup_party_id, p_dup_party_id;
1375: END IF;
1376: END IF;
1377: output_long_strings('----------------------------------------------------------');
1378: output_long_strings('Party Site Contains String = '||p_contains_str);
1379: output_long_strings('Restrict Sql = '||p_restrict_sql);
1380: exception
1381: when others then
1382: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1406: p_contains_str VARCHAR2,

1402: PROCEDURE open_contact_cursor(
1403: p_dup_party_id NUMBER,
1404: p_party_id NUMBER,
1405: p_restrict_sql VARCHAR2,
1406: p_contains_str VARCHAR2,
1407: p_search_ctx_id NUMBER,
1408: p_search_merged VARCHAR2,
1409: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1410: l_sqlstr VARCHAR2(4000);

Line 1434: WHERE contains( concat_col, p_contains_str)>0

1430: END IF;
1431: OPEN x_cursor FOR
1432: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1433: FROM HZ_STAGED_CONTACTS stage
1434: WHERE contains( concat_col, p_contains_str)>0
1435: AND EXISTS (
1436: SELECT 1 FROM HZ_STAGED_PARTIES p
1437: WHERE p.PARTY_ID = stage.PARTY_ID
1438: AND( (l_search_merged ='Y' )

Line 1457: WHERE contains( concat_col, p_contains_str)>0

1453: END IF;
1454: OPEN x_cursor FOR
1455: SELECT ORG_CONTACT_ID, PARTY_ID
1456: FROM HZ_STAGED_CONTACTS stage
1457: WHERE contains( concat_col, p_contains_str)>0
1458: AND EXISTS (
1459: SELECT 1 FROM HZ_STAGED_PARTIES p
1460: WHERE p.PARTY_ID = stage.PARTY_ID
1461: AND( (l_search_merged ='Y' )

Line 1476: WHERE contains( concat_col, p_contains_str)>0

1472: END IF;
1473: OPEN x_cursor FOR
1474: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1475: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1476: WHERE contains( concat_col, p_contains_str)>0
1477: AND d.search_context_id = p_search_ctx_id
1478: AND d.party_id = stage.party_id
1479: AND( (l_search_merged ='Y' )
1480: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1510: ' WHERE contains( concat_col, :cont)>0'||

1506: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1507: END IF;
1508: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
1509: ' FROM HZ_STAGED_CONTACTS stage'||
1510: ' WHERE contains( concat_col, :cont)>0'||
1511: ' AND EXISTS ('||
1512: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1513: ' WHERE p.party_id = stage.party_id ' ||
1514: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1520: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1516: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1517: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1518: ' AND ('||p_restrict_sql||')' ||
1519: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1520: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1521: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1522: ,p_dup_party_id, p_dup_party_id;
1523: END IF;
1524: output_long_strings('----------------------------------------------------------');

Line 1525: output_long_strings('Contacts Contains String = '||p_contains_str);

1521: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1522: ,p_dup_party_id, p_dup_party_id;
1523: END IF;
1524: output_long_strings('----------------------------------------------------------');
1525: output_long_strings('Contacts Contains String = '||p_contains_str);
1526: output_long_strings('Restrict Sql = '||p_restrict_sql);
1527: exception
1528: when others then
1529: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1553: p_contains_str VARCHAR2,

1549: PROCEDURE open_contact_pt_cursor(
1550: p_dup_party_id NUMBER,
1551: p_party_id NUMBER,
1552: p_restrict_sql VARCHAR2,
1553: p_contains_str VARCHAR2,
1554: p_search_ctx_id NUMBER,
1555: p_search_merged VARCHAR2,
1556: p_search_rel_cpts VARCHAR2,
1557: p_person_api VARCHAR2,

Line 1583: WHERE contains( concat_col, p_contains_str)>0

1579: THEN
1580: OPEN x_cursor FOR
1581: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1582: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1583: WHERE contains( concat_col, p_contains_str)>0
1584: AND d.search_context_id = p_search_ctx_id
1585: AND( (l_search_merged ='Y' )
1586: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1587: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1595: WHERE contains( concat_col, p_contains_str)>0

1591: THEN
1592: OPEN x_cursor FOR
1593: SELECT /*+ USE_NL(d stage) ORDERED INDEX(stage HZ_STAGED_CONTACT_POINTS_N3) */ CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1594: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1595: WHERE contains( concat_col, p_contains_str)>0
1596: AND d.search_context_id = p_search_ctx_id
1597: AND( (l_search_merged ='Y' )
1598: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1599: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1612: WHERE contains( concat_col, p_contains_str)>0

1608: IF p_search_rel_cpts = 'N' THEN
1609: OPEN x_cursor FOR
1610: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1611: FROM HZ_STAGED_CONTACT_POINTS stage
1612: WHERE contains( concat_col, p_contains_str)>0
1613: AND( (l_search_merged ='Y' )
1614: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1615: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1616: AND stage.party_id = p_party_id;

Line 1621: WHERE contains( concat_col, p_contains_str)>0

1617: ELSE
1618: OPEN x_cursor FOR
1619: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1620: FROM HZ_STAGED_CONTACT_POINTS stage
1621: WHERE contains( concat_col, p_contains_str)>0
1622: AND( (l_search_merged ='Y' )
1623: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1624: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1625: AND stage.party_id = p_party_id

Line 1629: WHERE contains( concat_col, p_contains_str)>0

1625: AND stage.party_id = p_party_id
1626: UNION
1627: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ stage.CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID
1628: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1629: WHERE contains( concat_col, p_contains_str)>0
1630: AND r.object_id = p_party_id
1631: AND( (l_search_merged ='Y' )
1632: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1633: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

Line 1651: WHERE contains( concat_col, p_contains_str)>0

1647: END IF;
1648: OPEN x_cursor FOR
1649: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1650: FROM HZ_STAGED_CONTACT_POINTS stage
1651: WHERE contains( concat_col, p_contains_str)>0
1652: AND EXISTS (
1653: SELECT 1 FROM HZ_STAGED_PARTIES p
1654: WHERE p.PARTY_ID = stage.PARTY_ID
1655: AND( (l_search_merged ='Y' )

Line 1671: WHERE contains( concat_col, p_contains_str)>0

1667: IF p_person_api = 'Y' THEN
1668: OPEN x_cursor FOR
1669: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1670: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1671: WHERE contains( concat_col, p_contains_str)>0
1672: AND d.search_context_id = p_search_ctx_id
1673: AND d.party_id = stage.party_id
1674: AND( (l_search_merged ='Y' )
1675: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1681: WHERE contains( concat_col, p_contains_str)>0

1677: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1678: UNION
1679: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, r.subject_id, stage.PARTY_SITE_ID, stage.ORG_CONTACT_ID
1680: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1681: WHERE contains( concat_col, p_contains_str)>0
1682: AND d.search_context_id = p_search_ctx_id
1683: AND d.party_id = r.subject_id
1684: AND r.relationship_id = oc.party_relationship_id
1685: AND oc.org_contact_id = stage.org_contact_id

Line 1694: WHERE contains( concat_col, p_contains_str)>0

1690: ELSE
1691: OPEN x_cursor FOR
1692: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID
1693: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1694: WHERE contains( concat_col, p_contains_str)>0
1695: AND d.search_context_id = p_search_ctx_id
1696: AND d.party_id = stage.party_id
1697: AND( (l_search_merged ='Y' )
1698: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

Line 1739: ' WHERE contains( concat_col, :cont)>0 '||

1735: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1736: END IF;
1737: l_sqlstr := ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1738: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1739: ' WHERE contains( concat_col, :cont)>0 '||
1740: ' AND (stage.org_contact_id is null '||
1741: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1742: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1743: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

Line 1749: ' WHERE contains( concat_col, :cont)>0 '||

1745: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1746: ' UNION ' ||
1747: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1748: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1749: ' WHERE contains( concat_col, :cont)>0 '||
1750: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1751: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1752: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1753: ' AND (stage.org_contact_id in ' ||

Line 1759: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1755: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1756: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1757: ' and ('||p_restrict_sql1 || ') ) )' ||
1758: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1759: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1760: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1761: ELSE
1762: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1763: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

Line 1760: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1756: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1757: ' and ('||p_restrict_sql1 || ') ) )' ||
1758: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1759: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1760: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1761: ELSE
1762: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1763: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1764: ' WHERE contains( concat_col, :cont)>0'||

Line 1764: ' WHERE contains( concat_col, :cont)>0'||

1760: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1761: ELSE
1762: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1763: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1764: ' WHERE contains( concat_col, :cont)>0'||
1765: ' AND EXISTS ('||
1766: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1767: ' WHERE p.party_id = stage.party_id ' ||
1768: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1774: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1770: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1771: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1772: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1773: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1774: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1775: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1776: ,p_dup_party_id, p_dup_party_id;
1777: END IF;
1778: ELSE

Line 1781: ' WHERE contains( concat_col, :cont)>0'||

1777: END IF;
1778: ELSE
1779: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID '||
1780: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1781: ' WHERE contains( concat_col, :cont)>0'||
1782: ' AND EXISTS ('||
1783: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1784: ' WHERE p.party_id = stage.party_id ' ||
1785: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1791: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1787: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1788: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1789: ' AND ('||p_restrict_sql||')' ||
1790: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1791: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1792: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1793: ,p_dup_party_id, p_dup_party_id;
1794: END IF;
1795: END IF;

Line 1798: output_long_strings('Contact Points Contains String = '||p_contains_str);

1794: END IF;
1795: END IF;
1796: END IF;
1797: output_long_strings('----------------------------------------------------------');
1798: output_long_strings('Contact Points Contains String = '||p_contains_str);
1799: output_long_strings('Restrict Sql = '||p_restrict_sql);
1800: exception
1801: when others then
1802: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1919: l_party_contains_str VARCHAR2(32000);

1915: x_num_matches OUT NUMBER
1916: ) IS
1917:
1918: -- Strings to hold the generated Intermedia query strings
1919: l_party_contains_str VARCHAR2(32000);
1920: l_party_site_contains_str VARCHAR2(32000);
1921: l_contact_contains_str VARCHAR2(32000);
1922: l_contact_pt_contains_str VARCHAR2(32000);
1923: l_denorm_str VARCHAR2(32000);

Line 1920: l_party_site_contains_str VARCHAR2(32000);

1916: ) IS
1917:
1918: -- Strings to hold the generated Intermedia query strings
1919: l_party_contains_str VARCHAR2(32000);
1920: l_party_site_contains_str VARCHAR2(32000);
1921: l_contact_contains_str VARCHAR2(32000);
1922: l_contact_pt_contains_str VARCHAR2(32000);
1923: l_denorm_str VARCHAR2(32000);
1924: l_ps_denorm_str VARCHAR2(32000);

Line 1921: l_contact_contains_str VARCHAR2(32000);

1917:
1918: -- Strings to hold the generated Intermedia query strings
1919: l_party_contains_str VARCHAR2(32000);
1920: l_party_site_contains_str VARCHAR2(32000);
1921: l_contact_contains_str VARCHAR2(32000);
1922: l_contact_pt_contains_str VARCHAR2(32000);
1923: l_denorm_str VARCHAR2(32000);
1924: l_ps_denorm_str VARCHAR2(32000);
1925: l_ct_denorm_str VARCHAR2(32000);

Line 1922: l_contact_pt_contains_str VARCHAR2(32000);

1918: -- Strings to hold the generated Intermedia query strings
1919: l_party_contains_str VARCHAR2(32000);
1920: l_party_site_contains_str VARCHAR2(32000);
1921: l_contact_contains_str VARCHAR2(32000);
1922: l_contact_pt_contains_str VARCHAR2(32000);
1923: l_denorm_str VARCHAR2(32000);
1924: l_ps_denorm_str VARCHAR2(32000);
1925: l_ct_denorm_str VARCHAR2(32000);
1926: l_cpt_denorm_str VARCHAR2(32000);

Line 2011: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS

2007: call_type(2) := 'OR';
2008: END;
2009:
2010: /** Private procedure to acquire and score at party level ***/
2011: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2012: l_party_id_idx NUMBER:=1;
2013: l_ctx_id NUMBER;
2014: l_precalc_score BOOLEAN := FALSE;
2015: l_TX35_new varchar2(4000);

Line 2043: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN

2039: l_search_ctx_id,H_PARTY_ID(I));
2040: l_party_id_idx := H_PARTY_ID.COUNT+1;
2041: ELSE
2042: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2043: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2044: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2045: hz_utility_v2pub.debug(p_message=>'In eval party level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2046: END IF;
2047: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' AND p_dup_batch_id IS NULL THEN

Line 2051: IF (l_party_site_contains_str IS NULL AND

2047: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' AND p_dup_batch_id IS NULL THEN
2048: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2049: hz_utility_v2pub.debug(p_message=>'In eval party level resolution options is set to SQL search.',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2050: END IF;
2051: IF (l_party_site_contains_str IS NULL AND
2052: l_contact_contains_str IS NULL AND
2053: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2054: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,p_search_merged,p_dup_party_id, x_num_matches);
2055: RAISE L_RETURN_IMM_EXC;

Line 2052: l_contact_contains_str IS NULL AND

2048: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2049: hz_utility_v2pub.debug(p_message=>'In eval party level resolution options is set to SQL search.',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2050: END IF;
2051: IF (l_party_site_contains_str IS NULL AND
2052: l_contact_contains_str IS NULL AND
2053: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2054: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,p_search_merged,p_dup_party_id, x_num_matches);
2055: RAISE L_RETURN_IMM_EXC;
2056: ELSE

Line 2053: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN

2049: hz_utility_v2pub.debug(p_message=>'In eval party level resolution options is set to SQL search.',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2050: END IF;
2051: IF (l_party_site_contains_str IS NULL AND
2052: l_contact_contains_str IS NULL AND
2053: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2054: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,p_search_merged,p_dup_party_id, x_num_matches);
2055: RAISE L_RETURN_IMM_EXC;
2056: ELSE
2057: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

Line 2057: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

2053: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2054: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,p_search_merged,p_dup_party_id, x_num_matches);
2055: RAISE L_RETURN_IMM_EXC;
2056: ELSE
2057: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2058: END IF;
2059: ELSE
2060: IF p_index>1 THEN
2061: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

Line 2080: open_party_cursor(p_dup_party_id, p_restrict_sql, p_party_contains_str,l_ctx_id, l_match_str,p_search_merged,l_party_cur);

2076: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2077: hz_utility_v2pub.debug(p_message=>'========== START LOOPING THROUGH WHAT IS RETURNED BY OPEN_PARTY_CURSOR ================',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2078: END IF;
2079: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2080: open_party_cursor(p_dup_party_id, p_restrict_sql, p_party_contains_str,l_ctx_id, l_match_str,p_search_merged,l_party_cur);
2081: END IF;
2082: LOOP
2083: FETCH l_party_cur INTO
2084: l_party_id , l_TX2, l_TX8, l_TX4;

Line 2102: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL

2098: l_party_id_idx:= l_party_id_idx+1;
2099: END IF;
2100: IF (l_party_id_idx-1)>l_max_thresh THEN
2101: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2102: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2103: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2104: AND l_contact_pt_contains_str IS NULL THEN
2105: EXIT;
2106: END IF;

Line 2104: AND l_contact_pt_contains_str IS NULL THEN

2100: IF (l_party_id_idx-1)>l_max_thresh THEN
2101: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2102: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2103: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2104: AND l_contact_pt_contains_str IS NULL THEN
2105: EXIT;
2106: END IF;
2107: CLOSE l_party_cur;
2108: IF p_index>1 THEN

Line 2133: 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

2129: ROLLBACK to eval_start;
2130: END;
2131:
2132: /** Private procedure to acquire and score at party site level ***/
2133: 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
2134: l_party_id_idx NUMBER:=1;
2135: l_ctx_id NUMBER;
2136: h_ps_id HZ_PARTY_SEARCH.IDList;
2137: h_ps_party_id HZ_PARTY_SEARCH.IDList;

Line 2145: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0

2141: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2142: hz_utility_v2pub.debug(p_message=>'-----------------',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2143: hz_utility_v2pub.debug(p_message=>'calling the procedure eval_party_site_level',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2144: END IF;
2145: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2146: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2147: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2148: l_party_id := H_SCORES.FIRST;
2149: WHILE l_party_id IS NOT NULL LOOP

Line 2146: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and

2142: hz_utility_v2pub.debug(p_message=>'-----------------',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2143: hz_utility_v2pub.debug(p_message=>'calling the procedure eval_party_site_level',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2144: END IF;
2145: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2146: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2147: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2148: l_party_id := H_SCORES.FIRST;
2149: WHILE l_party_id IS NOT NULL LOOP
2150: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;

Line 2147: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN

2143: hz_utility_v2pub.debug(p_message=>'calling the procedure eval_party_site_level',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2144: END IF;
2145: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2146: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2147: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2148: l_party_id := H_SCORES.FIRST;
2149: WHILE l_party_id IS NOT NULL LOOP
2150: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2151: l_party_id:=H_SCORES.NEXT(l_party_id);

Line 2177: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN

2173: l_search_ctx_id,H_PARTY_ID(I));
2174: l_party_id_idx := H_PARTY_ID.COUNT+1;
2175: ELSE
2176: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2177: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2178: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2179: hz_utility_v2pub.debug(p_message=>'In eval party site level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2180: END IF;
2181: IF p_index>1 THEN

Line 2199: open_party_site_cursor(p_dup_party_id,NULL, p_restrict_sql, p_party_site_contains_str,l_ctx_id, p_search_merged, 'N','N',l_party_site_cur);

2195: END IF;
2196: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2197: hz_utility_v2pub.debug(p_message=>'========== START LOOPING THROUGH WHAT IS RETURNED BY OPEN_PARTY_SITE_CURSOR ================',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2198: END IF;
2199: open_party_site_cursor(p_dup_party_id,NULL, p_restrict_sql, p_party_site_contains_str,l_ctx_id, p_search_merged, 'N','N',l_party_site_cur);
2200: LOOP
2201: FETCH l_party_site_cur INTO
2202: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX3;
2203: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 2255: 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

2251: END IF;
2252: END;
2253:
2254: /** Private procedure to acquire and score at contact point level ***/
2255: 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
2256: l_party_id_idx NUMBER:=1;
2257: l_ctx_id NUMBER;
2258: h_ct_id HZ_PARTY_SEARCH.IDList;
2259: h_ct_party_id HZ_PARTY_SEARCH.IDList;

Line 2289: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN

2285: l_search_ctx_id,H_PARTY_ID(I));
2286: l_party_id_idx := H_PARTY_ID.COUNT+1;
2287: ELSE
2288: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2289: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2290: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2291: hz_utility_v2pub.debug(p_message=>'In eval contact level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2292: END IF;
2293: IF p_index>1 THEN

Line 2311: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);

2307: END IF;
2308: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2309: hz_utility_v2pub.debug(p_message=>'========== START LOOPING THROUGH WHAT IS RETURNED BY OPEN_CONTACT_CURSOR ================',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2310: END IF;
2311: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2312: LOOP
2313: FETCH l_contact_cur INTO
2314: l_org_contact_id, l_ct_party_id ;
2315: EXIT WHEN l_contact_cur%NOTFOUND;

Line 2364: 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

2360: END IF;
2361: END;
2362:
2363: /** Private procedure to acquire and score at contact point level ***/
2364: 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
2365: l_party_id_idx NUMBER:=1;
2366: l_ctx_id NUMBER;
2367: h_cpt_id HZ_PARTY_SEARCH.IDList;
2368: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

Line 2400: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN

2396: l_search_ctx_id,H_PARTY_ID(I));
2397: l_party_id_idx := H_PARTY_ID.COUNT+1;
2398: ELSE
2399: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2400: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2401: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2402: hz_utility_v2pub.debug(p_message=>'In eval contact point level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2403: END IF;
2404: IF p_index>1 THEN

Line 2422: open_contact_pt_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_pt_contains_str,l_ctx_id, p_search_merged, 'N', 'N',l_contact_pt_cur);

2418: END IF;
2419: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2420: hz_utility_v2pub.debug(p_message=>'========== START LOOPING THROUGH WHAT IS RETURNED BY OPEN_CONTACT_PT_CURSOR ================',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2421: END IF;
2422: open_contact_pt_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_pt_contains_str,l_ctx_id, p_search_merged, 'N', 'N',l_contact_pt_cur);
2423: LOOP
2424: FETCH l_contact_pt_cur INTO
2425: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
2426: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 2522: IF l_party_contains_str IS NOT NULL THEN

2518: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
2519: l_threshold := 65;
2520: other_acq_criteria_exists := TRUE ;
2521: --check if acquisition criteria exists for any other entity
2522: IF l_party_contains_str IS NOT NULL THEN
2523: acq_cnt := 1;
2524: END IF;
2525: IF l_party_site_contains_str IS NOT NULL THEN
2526: acq_cnt := acq_cnt+1;

Line 2525: IF l_party_site_contains_str IS NOT NULL THEN

2521: --check if acquisition criteria exists for any other entity
2522: IF l_party_contains_str IS NOT NULL THEN
2523: acq_cnt := 1;
2524: END IF;
2525: IF l_party_site_contains_str IS NOT NULL THEN
2526: acq_cnt := acq_cnt+1;
2527: END IF;
2528: IF l_contact_contains_str IS NOT NULL THEN
2529: acq_cnt := acq_cnt+1;

Line 2528: IF l_contact_contains_str IS NOT NULL THEN

2524: END IF;
2525: IF l_party_site_contains_str IS NOT NULL THEN
2526: acq_cnt := acq_cnt+1;
2527: END IF;
2528: IF l_contact_contains_str IS NOT NULL THEN
2529: acq_cnt := acq_cnt+1;
2530: END IF;
2531: IF l_contact_pt_contains_str IS NOT NULL THEN
2532: acq_cnt := acq_cnt+1;

Line 2531: IF l_contact_pt_contains_str IS NOT NULL THEN

2527: END IF;
2528: IF l_contact_contains_str IS NOT NULL THEN
2529: acq_cnt := acq_cnt+1;
2530: END IF;
2531: IF l_contact_pt_contains_str IS NOT NULL THEN
2532: acq_cnt := acq_cnt+1;
2533: END IF;
2534: IF acq_cnt>1 THEN
2535: other_acq_criteria_exists := TRUE;

Line 2565: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN

2561: FND_MSG_PUB.ADD;
2562: RAISE FND_API.G_EXC_ERROR;
2563: END IF;
2564: END IF;
2565: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2566: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2567: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2568: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2569: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN

Line 2566: eval_party_level(l_party_contains_str,call_type(p_index), p_index);

2562: RAISE FND_API.G_EXC_ERROR;
2563: END IF;
2564: END IF;
2565: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2566: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2567: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2568: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2569: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2570: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

Line 2567: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN

2563: END IF;
2564: END IF;
2565: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2566: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2567: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2568: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2569: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2570: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2571: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

Line 2568: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

2564: END IF;
2565: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2566: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2567: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2568: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2569: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2570: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2571: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2572: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

Line 2569: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN

2565: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2566: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2567: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2568: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2569: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2570: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2571: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2572: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2573: END IF;

Line 2570: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

2566: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2567: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2568: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2569: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2570: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2571: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2572: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2573: END IF;
2574: END;

Line 2571: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

2567: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2568: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2569: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2570: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2571: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2572: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2573: END IF;
2574: END;
2575: /************ End of find_parties private procedures **********/

Line 2572: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

2568: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2569: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2570: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2571: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2572: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2573: END IF;
2574: END;
2575: /************ End of find_parties private procedures **********/
2576:

Line 2655: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);

2651: hz_utility_v2pub.debug(p_message=>'Maximum records that will be evaluated is '||l_max_thresh,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2652: END IF;
2653: -- ************************************************************
2654: -- STEP 2. Setup of intermedia query strings for Acquisition query
2655: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2656: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2657: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2658: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2659: hz_utility_v2pub.debug(p_message=>'Commencing the DENORM LOGIC in find_parties',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line 2656: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);

2652: END IF;
2653: -- ************************************************************
2654: -- STEP 2. Setup of intermedia query strings for Acquisition query
2655: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2656: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2657: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2658: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2659: hz_utility_v2pub.debug(p_message=>'Commencing the DENORM LOGIC in find_parties',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2660: END IF;

Line 2657: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);

2653: -- ************************************************************
2654: -- STEP 2. Setup of intermedia query strings for Acquisition query
2655: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2656: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2657: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2658: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2659: hz_utility_v2pub.debug(p_message=>'Commencing the DENORM LOGIC in find_parties',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2660: END IF;
2661: l_denorm_max_score:=0;

Line 2689: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, l_denorm_str, l_party_max_score, l_denorm_max_score, l_non_denorm_max_score, round((65/110)*l_entered_max_score));

2685: END IF;
2686: ELSE
2687: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
2688: END IF;
2689: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, l_denorm_str, l_party_max_score, l_denorm_max_score, l_non_denorm_max_score, round((65/110)*l_entered_max_score));
2690: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
2691:
2692: -- Setup Search Context ID
2693: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 2699: IF l_party_contains_str IS NULL THEN

2695:
2696: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2697: hz_utility_v2pub.debug(p_message=>'Search context id in find_parties is '||x_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2698: END IF;
2699: IF l_party_contains_str IS NULL THEN
2700: defpt := 1;
2701: END IF;
2702: IF l_party_site_contains_str IS NULL THEN
2703: defps := 1;

Line 2702: IF l_party_site_contains_str IS NULL THEN

2698: END IF;
2699: IF l_party_contains_str IS NULL THEN
2700: defpt := 1;
2701: END IF;
2702: IF l_party_site_contains_str IS NULL THEN
2703: defps := 1;
2704: END IF;
2705: IF l_contact_contains_str IS NULL THEN
2706: defct := 1;

Line 2705: IF l_contact_contains_str IS NULL THEN

2701: END IF;
2702: IF l_party_site_contains_str IS NULL THEN
2703: defps := 1;
2704: END IF;
2705: IF l_contact_contains_str IS NULL THEN
2706: defct := 1;
2707: END IF;
2708: IF l_contact_pt_contains_str IS NULL THEN
2709: defcpt := 1;

Line 2708: IF l_contact_pt_contains_str IS NULL THEN

2704: END IF;
2705: IF l_contact_contains_str IS NULL THEN
2706: defct := 1;
2707: END IF;
2708: IF l_contact_pt_contains_str IS NULL THEN
2709: defcpt := 1;
2710: END IF;
2711:
2712: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

Line 2717: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

2713: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2714: 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);
2715: END IF;
2716: hz_utility_v2pub.debug(p_message=>'In find_parties l_match_str = '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2717: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2718: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_site_contains_str = '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2719: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_contains_str = '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2720: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_pt_contains_str = '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2721: hz_utility_v2pub.debug(p_message=>'In find_parties l_search_ctx_id = '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line 2718: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_site_contains_str = '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

2714: 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);
2715: END IF;
2716: hz_utility_v2pub.debug(p_message=>'In find_parties l_match_str = '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2717: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2718: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_site_contains_str = '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2719: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_contains_str = '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2720: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_pt_contains_str = '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2721: hz_utility_v2pub.debug(p_message=>'In find_parties l_search_ctx_id = '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2722: END IF;

Line 2719: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_contains_str = '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

2715: END IF;
2716: hz_utility_v2pub.debug(p_message=>'In find_parties l_match_str = '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2717: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2718: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_site_contains_str = '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2719: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_contains_str = '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2720: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_pt_contains_str = '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2721: hz_utility_v2pub.debug(p_message=>'In find_parties l_search_ctx_id = '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2722: END IF;
2723: FOR I in 1..5 LOOP

Line 2720: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_pt_contains_str = '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

2716: hz_utility_v2pub.debug(p_message=>'In find_parties l_match_str = '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2717: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_contains_str = '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2718: hz_utility_v2pub.debug(p_message=>'In find_parties l_party_site_contains_str = '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2719: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_contains_str = '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2720: hz_utility_v2pub.debug(p_message=>'In find_parties l_contact_pt_contains_str = '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2721: hz_utility_v2pub.debug(p_message=>'In find_parties l_search_ctx_id = '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2722: END IF;
2723: FOR I in 1..5 LOOP
2724: do_eval(I);

Line 2834: l_party_contains_str VARCHAR2(32000);

2830: x_num_matches OUT NUMBER
2831: ) IS
2832:
2833: -- Strings to hold the generated Intermedia query strings
2834: l_party_contains_str VARCHAR2(32000);
2835: l_party_site_contains_str VARCHAR2(32000);
2836: l_contact_contains_str VARCHAR2(32000);
2837: l_contact_pt_contains_str VARCHAR2(32000);
2838: l_denorm_str VARCHAR2(32000);

Line 2835: l_party_site_contains_str VARCHAR2(32000);

2831: ) IS
2832:
2833: -- Strings to hold the generated Intermedia query strings
2834: l_party_contains_str VARCHAR2(32000);
2835: l_party_site_contains_str VARCHAR2(32000);
2836: l_contact_contains_str VARCHAR2(32000);
2837: l_contact_pt_contains_str VARCHAR2(32000);
2838: l_denorm_str VARCHAR2(32000);
2839: l_ps_denorm_str VARCHAR2(32000);

Line 2836: l_contact_contains_str VARCHAR2(32000);

2832:
2833: -- Strings to hold the generated Intermedia query strings
2834: l_party_contains_str VARCHAR2(32000);
2835: l_party_site_contains_str VARCHAR2(32000);
2836: l_contact_contains_str VARCHAR2(32000);
2837: l_contact_pt_contains_str VARCHAR2(32000);
2838: l_denorm_str VARCHAR2(32000);
2839: l_ps_denorm_str VARCHAR2(32000);
2840: l_ct_denorm_str VARCHAR2(32000);

Line 2837: l_contact_pt_contains_str VARCHAR2(32000);

2833: -- Strings to hold the generated Intermedia query strings
2834: l_party_contains_str VARCHAR2(32000);
2835: l_party_site_contains_str VARCHAR2(32000);
2836: l_contact_contains_str VARCHAR2(32000);
2837: l_contact_pt_contains_str VARCHAR2(32000);
2838: l_denorm_str VARCHAR2(32000);
2839: l_ps_denorm_str VARCHAR2(32000);
2840: l_ct_denorm_str VARCHAR2(32000);
2841: l_cpt_denorm_str VARCHAR2(32000);

Line 2956: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS

2952: call_type(2) := 'OR';
2953: END;
2954:
2955: /** Private procedure to acquire and score at party level ***/
2956: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2957: l_party_id_idx NUMBER:=1;
2958: l_ctx_id NUMBER;
2959: l_TX35_new varchar2(4000);
2960: BEGIN

Line 2978: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN

2974: l_search_ctx_id,H_PARTY_ID(I));
2975: l_party_id_idx := H_PARTY_ID.COUNT+1;
2976: ELSE
2977: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2978: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2979: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2980: hz_utility_v2pub.debug(p_message=>'In eval party level estimated hits exceed threshold',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2981: END IF;
2982: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN

Line 2986: IF (l_party_site_contains_str IS NULL AND

2982: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
2983: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2984: hz_utility_v2pub.debug(p_message=>'In eval party level resolution option is set to SQL search.',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2985: END IF;
2986: IF (l_party_site_contains_str IS NULL AND
2987: l_contact_contains_str IS NULL AND
2988: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
2989: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
2990: RAISE L_RETURN_IMM_EXC;

Line 2987: l_contact_contains_str IS NULL AND

2983: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2984: hz_utility_v2pub.debug(p_message=>'In eval party level resolution option is set to SQL search.',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2985: END IF;
2986: IF (l_party_site_contains_str IS NULL AND
2987: l_contact_contains_str IS NULL AND
2988: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
2989: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
2990: RAISE L_RETURN_IMM_EXC;
2991: ELSE

Line 2988: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN

2984: hz_utility_v2pub.debug(p_message=>'In eval party level resolution option is set to SQL search.',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2985: END IF;
2986: IF (l_party_site_contains_str IS NULL AND
2987: l_contact_contains_str IS NULL AND
2988: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
2989: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
2990: RAISE L_RETURN_IMM_EXC;
2991: ELSE
2992: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

Line 2992: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

2988: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
2989: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
2990: RAISE L_RETURN_IMM_EXC;
2991: ELSE
2992: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
2993: END IF;
2994: ELSE
2995: IF p_index>1 THEN
2996: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

Line 3012: open_party_cursor(p_dup_party_id, p_restrict_sql, p_party_contains_str,l_ctx_id, l_match_str,p_search_merged,l_party_cur);

3008: l_ctx_id := NULL;
3009: l_party_id_idx := H_PARTY_ID.COUNT+1;
3010: END IF;
3011: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3012: open_party_cursor(p_dup_party_id, p_restrict_sql, p_party_contains_str,l_ctx_id, l_match_str,p_search_merged,l_party_cur);
3013: END IF;
3014: LOOP
3015: FETCH l_party_cur INTO
3016: l_party_id , l_TX2, l_TX8, l_TX4;

Line 3034: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL

3030: l_party_id_idx:= l_party_id_idx+1;
3031: END IF;
3032: IF (l_party_id_idx-1)>l_max_thresh THEN
3033: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3034: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3035: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3036: AND l_contact_pt_contains_str IS NULL THEN
3037: H_PARTY_ID.DELETE(l_party_id_idx-1);
3038: H_PARTY_ID_LIST.DELETE(l_index);

Line 3036: AND l_contact_pt_contains_str IS NULL THEN

3032: IF (l_party_id_idx-1)>l_max_thresh THEN
3033: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3034: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3035: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3036: AND l_contact_pt_contains_str IS NULL THEN
3037: H_PARTY_ID.DELETE(l_party_id_idx-1);
3038: H_PARTY_ID_LIST.DELETE(l_index);
3039: H_SCORES.DELETE(l_index);
3040: EXIT;

Line 3065: p_contains_str VARCHAR2,

3061: CLOSE l_party_cur;
3062: ROLLBACK to eval_start;
3063: END;
3064: PROCEDURE open_person_contact_cursor(
3065: p_contains_str VARCHAR2,
3066: p_search_ctx_id NUMBER,
3067: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3068: BEGIN
3069: OPEN x_cursor FOR

Line 3072: WHERE contains( concat_col, p_contains_str)>0

3068: BEGIN
3069: OPEN x_cursor FOR
3070: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3071: FROM HZ_STAGED_CONTACTS stage
3072: WHERE contains( concat_col, p_contains_str)>0
3073: AND ORG_CONTACT_ID in (
3074: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3075: org_contact_id
3076: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc

Line 3096: 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

3092: end if;
3093: END;
3094:
3095: /** Private procedure to acquire and score at party site level ***/
3096: 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
3097: l_party_id_idx NUMBER:=1;
3098: l_ctx_id NUMBER;
3099: h_ps_id HZ_PARTY_SEARCH.IDList;
3100: h_ps_party_id HZ_PARTY_SEARCH.IDList;

Line 3124: open_party_site_cursor(p_dup_party_id,NULL, p_restrict_sql, p_party_site_contains_str,l_ctx_id, p_search_merged,'N', 'Y',l_party_site_cur);

3120: ELSE
3121: l_party_id_idx := H_PARTY_ID.COUNT+1;
3122: l_ctx_id := NULL;
3123: END IF;
3124: open_party_site_cursor(p_dup_party_id,NULL, p_restrict_sql, p_party_site_contains_str,l_ctx_id, p_search_merged,'N', 'Y',l_party_site_cur);
3125: LOOP
3126: FETCH l_party_site_cur INTO
3127: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX3;
3128: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 3195: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS

3191: END IF;
3192: END;
3193:
3194: /** Private procedure to acquire and score at party site level ***/
3195: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3196: l_party_id_idx NUMBER:=1;
3197: l_ctx_id NUMBER;
3198: h_ct_id HZ_PARTY_SEARCH.IDList;
3199: h_ct_party_id HZ_PARTY_SEARCH.IDList;

Line 3211: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);

3207: FORALL I in 1..H_PARTY_ID.COUNT
3208: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3209: l_search_ctx_id,H_PARTY_ID(I));
3210: l_party_id_idx := H_PARTY_ID.COUNT+1;
3211: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3212: LOOP
3213: FETCH l_contact_cur INTO
3214: l_org_contact_id, l_ct_party_id ;
3215: EXIT WHEN l_contact_cur%NOTFOUND;

Line 3253: 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

3249: END IF;
3250: END;
3251:
3252: /** Private procedure to acquire and score at contact point level ***/
3253: 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
3254: l_party_id_idx NUMBER:=1;
3255: l_ctx_id NUMBER;
3256: h_cpt_id HZ_PARTY_SEARCH.IDList;
3257: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

Line 3298: open_contact_pt_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_pt_contains_str,l_ctx_id, p_search_merged,'N', 'Y',l_contact_pt_cur);

3294: END IF;
3295: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3296: hz_utility_v2pub.debug(p_message=>'====== START LOOPING THROUGH WHAT IS RETURNED BY OPEN_CONTACT_PT_CURSOR =======',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3297: END IF;
3298: open_contact_pt_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_pt_contains_str,l_ctx_id, p_search_merged,'N', 'Y',l_contact_pt_cur);
3299: LOOP
3300: FETCH l_contact_pt_cur INTO
3301: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;
3302: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 3406: IF l_party_contains_str IS NOT NULL THEN

3402: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3403: l_threshold := 65;
3404: other_acq_criteria_exists := TRUE ;
3405: --check if acquisition criteria exists for any other entity
3406: IF l_party_contains_str IS NOT NULL THEN
3407: acq_cnt := 1;
3408: END IF;
3409: IF l_party_site_contains_str IS NOT NULL THEN
3410: acq_cnt := acq_cnt+1;

Line 3409: IF l_party_site_contains_str IS NOT NULL THEN

3405: --check if acquisition criteria exists for any other entity
3406: IF l_party_contains_str IS NOT NULL THEN
3407: acq_cnt := 1;
3408: END IF;
3409: IF l_party_site_contains_str IS NOT NULL THEN
3410: acq_cnt := acq_cnt+1;
3411: END IF;
3412: IF l_contact_contains_str IS NOT NULL THEN
3413: acq_cnt := acq_cnt+1;

Line 3412: IF l_contact_contains_str IS NOT NULL THEN

3408: END IF;
3409: IF l_party_site_contains_str IS NOT NULL THEN
3410: acq_cnt := acq_cnt+1;
3411: END IF;
3412: IF l_contact_contains_str IS NOT NULL THEN
3413: acq_cnt := acq_cnt+1;
3414: END IF;
3415: IF l_contact_pt_contains_str IS NOT NULL THEN
3416: acq_cnt := acq_cnt+1;

Line 3415: IF l_contact_pt_contains_str IS NOT NULL THEN

3411: END IF;
3412: IF l_contact_contains_str IS NOT NULL THEN
3413: acq_cnt := acq_cnt+1;
3414: END IF;
3415: IF l_contact_pt_contains_str IS NOT NULL THEN
3416: acq_cnt := acq_cnt+1;
3417: END IF;
3418: IF acq_cnt>1 THEN
3419: other_acq_criteria_exists := TRUE;

Line 3449: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN

3445: FND_MSG_PUB.ADD;
3446: RAISE FND_API.G_EXC_ERROR;
3447: END IF;
3448: END IF;
3449: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3450: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3451: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3452: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3453: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

Line 3450: eval_party_level(l_party_contains_str,call_type(p_index), p_index);

3446: RAISE FND_API.G_EXC_ERROR;
3447: END IF;
3448: END IF;
3449: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3450: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3451: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3452: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3453: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3454: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

Line 3451: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN

3447: END IF;
3448: END IF;
3449: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3450: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3451: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3452: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3453: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3454: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3455: END IF;

Line 3452: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

3448: END IF;
3449: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3450: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3451: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3452: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3453: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3454: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3455: END IF;
3456: END;

Line 3453: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

3449: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3450: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3451: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3452: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3453: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3454: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3455: END IF;
3456: END;
3457: /************ End of find_persons private procedures **********/

Line 3454: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

3450: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3451: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3452: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3453: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3454: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3455: END IF;
3456: END;
3457: /************ End of find_persons private procedures **********/
3458:

Line 3489: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);

3485: g_score_until_thresh := false;
3486: END IF;
3487: -- ************************************************************
3488: -- STEP 2. Setup of intermedia query strings for Acquisition query
3489: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3490: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3491: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3492: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3493: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);

Line 3490: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);

3486: END IF;
3487: -- ************************************************************
3488: -- STEP 2. Setup of intermedia query strings for Acquisition query
3489: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3490: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3491: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3492: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3493: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3494:

Line 3491: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);

3487: -- ************************************************************
3488: -- STEP 2. Setup of intermedia query strings for Acquisition query
3489: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3490: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3491: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3492: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3493: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3494:
3495: -- Setup Search Context ID

Line 3492: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);

3488: -- STEP 2. Setup of intermedia query strings for Acquisition query
3489: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3490: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3491: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3492: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3493: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3494:
3495: -- Setup Search Context ID
3496: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3499: IF l_party_contains_str IS NULL THEN

3495: -- Setup Search Context ID
3496: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3497: x_search_ctx_id := l_search_ctx_id;
3498:
3499: IF l_party_contains_str IS NULL THEN
3500: defpt := 1;
3501: END IF;
3502: IF l_party_site_contains_str IS NULL THEN
3503: defps := 1;

Line 3502: IF l_party_site_contains_str IS NULL THEN

3498:
3499: IF l_party_contains_str IS NULL THEN
3500: defpt := 1;
3501: END IF;
3502: IF l_party_site_contains_str IS NULL THEN
3503: defps := 1;
3504: END IF;
3505: IF l_contact_contains_str IS NULL THEN
3506: defct := 1;

Line 3505: IF l_contact_contains_str IS NULL THEN

3501: END IF;
3502: IF l_party_site_contains_str IS NULL THEN
3503: defps := 1;
3504: END IF;
3505: IF l_contact_contains_str IS NULL THEN
3506: defct := 1;
3507: END IF;
3508: IF l_contact_pt_contains_str IS NULL THEN
3509: defcpt := 1;

Line 3508: IF l_contact_pt_contains_str IS NULL THEN

3504: END IF;
3505: IF l_contact_contains_str IS NULL THEN
3506: defct := 1;
3507: END IF;
3508: IF l_contact_pt_contains_str IS NULL THEN
3509: defcpt := 1;
3510: END IF;
3511:
3512: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

Line 3514: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

3510: END IF;
3511:
3512: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3513: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3514: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3515: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3516: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3517: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3518: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line 3515: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

3511:
3512: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3513: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3514: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3515: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3516: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3517: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3518: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3519: END IF;

Line 3516: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

3512: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3513: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3514: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3515: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3516: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3517: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3518: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3519: END IF;
3520: FOR I in 1..5 LOOP

Line 3517: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

3513: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3514: hz_utility_v2pub.debug(p_message=>'l_party_contains_str '||l_party_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3515: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3516: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3517: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3518: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3519: END IF;
3520: FOR I in 1..5 LOOP
3521: do_eval(I);

Line 3523: IF l_contact_contains_str IS NOT NULL THEN

3519: END IF;
3520: FOR I in 1..5 LOOP
3521: do_eval(I);
3522: END LOOP;
3523: IF l_contact_contains_str IS NOT NULL THEN
3524: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3525: END IF;
3526: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3527: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 59 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

Line 3524: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);

3520: FOR I in 1..5 LOOP
3521: do_eval(I);
3522: END LOOP;
3523: IF l_contact_contains_str IS NOT NULL THEN
3524: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3525: END IF;
3526: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3527: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 59 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3528: END IF;

Line 4304: l_party_contains_str VARCHAR2(32000);

4300: x_num_matches OUT NUMBER
4301: ) IS
4302:
4303: -- Strings to hold the generated Intermedia query strings
4304: l_party_contains_str VARCHAR2(32000);
4305: l_match_idx NUMBER;
4306: l_party_site_contains_str VARCHAR2(32000);
4307: l_contact_contains_str VARCHAR2(32000);
4308: l_contact_pt_contains_str VARCHAR2(32000);

Line 4306: l_party_site_contains_str VARCHAR2(32000);

4302:
4303: -- Strings to hold the generated Intermedia query strings
4304: l_party_contains_str VARCHAR2(32000);
4305: l_match_idx NUMBER;
4306: l_party_site_contains_str VARCHAR2(32000);
4307: l_contact_contains_str VARCHAR2(32000);
4308: l_contact_pt_contains_str VARCHAR2(32000);
4309: l_tmp VARCHAR2(32000);
4310:

Line 4307: l_contact_contains_str VARCHAR2(32000);

4303: -- Strings to hold the generated Intermedia query strings
4304: l_party_contains_str VARCHAR2(32000);
4305: l_match_idx NUMBER;
4306: l_party_site_contains_str VARCHAR2(32000);
4307: l_contact_contains_str VARCHAR2(32000);
4308: l_contact_pt_contains_str VARCHAR2(32000);
4309: l_tmp VARCHAR2(32000);
4310:
4311: -- Other local variables

Line 4308: l_contact_pt_contains_str VARCHAR2(32000);

4304: l_party_contains_str VARCHAR2(32000);
4305: l_match_idx NUMBER;
4306: l_party_site_contains_str VARCHAR2(32000);
4307: l_contact_contains_str VARCHAR2(32000);
4308: l_contact_pt_contains_str VARCHAR2(32000);
4309: l_tmp VARCHAR2(32000);
4310:
4311: -- Other local variables
4312: l_match_str VARCHAR2(30); -- Match type (AND or OR)

Line 4383: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);

4379: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4380:
4381: -- ************************************************************
4382: -- STEP 2. Setup of intermedia query strings for Acquisition query
4383: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4384: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4385: 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);
4386:
4387: -- Setup Search Context ID

Line 4384: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);

4380:
4381: -- ************************************************************
4382: -- STEP 2. Setup of intermedia query strings for Acquisition query
4383: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4384: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4385: 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);
4386:
4387: -- Setup Search Context ID
4388: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 4393: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

4389: x_search_ctx_id := l_search_ctx_id;
4390:
4391: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4392: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4393: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4394: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4395: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4396: END IF;
4397: IF l_party_site_contains_str IS NULL THEN

Line 4394: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

4390:
4391: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4392: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4393: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4394: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4395: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4396: END IF;
4397: IF l_party_site_contains_str IS NULL THEN
4398: defps := 1;

Line 4397: IF l_party_site_contains_str IS NULL THEN

4393: hz_utility_v2pub.debug(p_message=>'l_party_site_contains_str '||l_party_site_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4394: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4395: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4396: END IF;
4397: IF l_party_site_contains_str IS NULL THEN
4398: defps := 1;
4399: END IF;
4400: IF l_contact_pt_contains_str IS NULL THEN
4401: defcpt := 1;

Line 4400: IF l_contact_pt_contains_str IS NULL THEN

4396: END IF;
4397: IF l_party_site_contains_str IS NULL THEN
4398: defps := 1;
4399: END IF;
4400: IF l_contact_pt_contains_str IS NULL THEN
4401: defcpt := 1;
4402: END IF;
4403:
4404: IF l_party_site_contains_str IS NOT NULL THEN

Line 4404: IF l_party_site_contains_str IS NOT NULL THEN

4400: IF l_contact_pt_contains_str IS NULL THEN
4401: defcpt := 1;
4402: END IF;
4403:
4404: IF l_party_site_contains_str IS NOT NULL THEN
4405: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4406: LOOP
4407: FETCH l_party_site_cur INTO
4408: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX3;

Line 4405: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);

4401: defcpt := 1;
4402: END IF;
4403:
4404: IF l_party_site_contains_str IS NOT NULL THEN
4405: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4406: LOOP
4407: FETCH l_party_site_cur INTO
4408: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX4, l_TX3;
4409: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 4428: IF l_contact_pt_contains_str IS NOT NULL THEN

4424: END LOOP;
4425: CLOSE l_party_site_cur;
4426: END IF;
4427:
4428: IF l_contact_pt_contains_str IS NOT NULL THEN
4429: SAVEPOINT eval_start;
4430: IF l_match_str = ' AND ' OR (
4431: ((l_cpt_max_score/l_entered_max_score)<(65/110))
4432: ) THEN

Line 4436: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'PARTY_SITES');

4432: ) THEN
4433: FORALL I in 1..l_match_ps_list.COUNT
4434: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4435: values (l_search_ctx_id,l_match_ps_list(I));
4436: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'PARTY_SITES');
4437: ELSE
4438: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4439: END IF;
4440: LOOP

Line 4438: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);

4434: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4435: values (l_search_ctx_id,l_match_ps_list(I));
4436: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'PARTY_SITES');
4437: ELSE
4438: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4439: END IF;
4440: LOOP
4441: FETCH l_contact_pt_cur INTO
4442: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 4538: l_party_contains_str VARCHAR2(32000);

4534: x_num_matches OUT NUMBER
4535: ) IS
4536:
4537: -- Strings to hold the generated Intermedia query strings
4538: l_party_contains_str VARCHAR2(32000);
4539: l_party_site_contains_str VARCHAR2(32000);
4540: l_contact_contains_str VARCHAR2(32000);
4541: l_contact_pt_contains_str VARCHAR2(32000);
4542: l_tmp VARCHAR2(32000);

Line 4539: l_party_site_contains_str VARCHAR2(32000);

4535: ) IS
4536:
4537: -- Strings to hold the generated Intermedia query strings
4538: l_party_contains_str VARCHAR2(32000);
4539: l_party_site_contains_str VARCHAR2(32000);
4540: l_contact_contains_str VARCHAR2(32000);
4541: l_contact_pt_contains_str VARCHAR2(32000);
4542: l_tmp VARCHAR2(32000);
4543:

Line 4540: l_contact_contains_str VARCHAR2(32000);

4536:
4537: -- Strings to hold the generated Intermedia query strings
4538: l_party_contains_str VARCHAR2(32000);
4539: l_party_site_contains_str VARCHAR2(32000);
4540: l_contact_contains_str VARCHAR2(32000);
4541: l_contact_pt_contains_str VARCHAR2(32000);
4542: l_tmp VARCHAR2(32000);
4543:
4544: -- Other local variables

Line 4541: l_contact_pt_contains_str VARCHAR2(32000);

4537: -- Strings to hold the generated Intermedia query strings
4538: l_party_contains_str VARCHAR2(32000);
4539: l_party_site_contains_str VARCHAR2(32000);
4540: l_contact_contains_str VARCHAR2(32000);
4541: l_contact_pt_contains_str VARCHAR2(32000);
4542: l_tmp VARCHAR2(32000);
4543:
4544: -- Other local variables
4545: l_match_str VARCHAR2(30); -- Match type (AND or OR)

Line 4612: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);

4608: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4609:
4610: -- ************************************************************
4611: -- STEP 2. Setup of intermedia query strings for Acquisition query
4612: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4613: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4614: 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);
4615:
4616: -- Setup Search Context ID

Line 4613: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);

4609:
4610: -- ************************************************************
4611: -- STEP 2. Setup of intermedia query strings for Acquisition query
4612: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4613: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4614: 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);
4615:
4616: -- Setup Search Context ID
4617: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 4622: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

4618: x_search_ctx_id := l_search_ctx_id;
4619:
4620: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4621: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4622: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4623: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4624: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4625: END IF;
4626: IF l_contact_contains_str IS NULL THEN

Line 4623: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

4619:
4620: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4621: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4622: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4623: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4624: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4625: END IF;
4626: IF l_contact_contains_str IS NULL THEN
4627: defct := 1;

Line 4626: IF l_contact_contains_str IS NULL THEN

4622: hz_utility_v2pub.debug(p_message=>'l_contact_contains_str '||l_contact_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4623: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4624: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4625: END IF;
4626: IF l_contact_contains_str IS NULL THEN
4627: defct := 1;
4628: END IF;
4629: IF l_contact_pt_contains_str IS NULL THEN
4630: defcpt := 1;

Line 4629: IF l_contact_pt_contains_str IS NULL THEN

4625: END IF;
4626: IF l_contact_contains_str IS NULL THEN
4627: defct := 1;
4628: END IF;
4629: IF l_contact_pt_contains_str IS NULL THEN
4630: defcpt := 1;
4631: END IF;
4632:
4633: IF l_contact_contains_str IS NOT NULL THEN

Line 4633: IF l_contact_contains_str IS NOT NULL THEN

4629: IF l_contact_pt_contains_str IS NULL THEN
4630: defcpt := 1;
4631: END IF;
4632:
4633: IF l_contact_contains_str IS NOT NULL THEN
4634: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4635: LOOP
4636: FETCH l_contact_cur INTO
4637: l_org_contact_id, l_ct_party_id ;

Line 4634: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);

4630: defcpt := 1;
4631: END IF;
4632:
4633: IF l_contact_contains_str IS NOT NULL THEN
4634: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4635: LOOP
4636: FETCH l_contact_cur INTO
4637: l_org_contact_id, l_ct_party_id ;
4638: EXIT WHEN l_contact_cur%NOTFOUND;

Line 4655: IF l_contact_pt_contains_str IS NOT NULL THEN

4651: END LOOP;
4652: CLOSE l_contact_cur;
4653: END IF;
4654:
4655: IF l_contact_pt_contains_str IS NOT NULL THEN
4656: SAVEPOINT eval_start;
4657: IF l_match_str = ' AND ' OR (
4658: ((l_cpt_max_score/l_entered_max_score)<(65/110))
4659: ) THEN

Line 4663: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'CONTACTS');

4659: ) THEN
4660: FORALL I in 1..l_match_contact_list.COUNT
4661: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4662: values (l_search_ctx_id,l_match_contact_list(I));
4663: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'CONTACTS');
4664: ELSE
4665: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4666: END IF;
4667: LOOP

Line 4665: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);

4661: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4662: values (l_search_ctx_id,l_match_contact_list(I));
4663: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur,'CONTACTS');
4664: ELSE
4665: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4666: END IF;
4667: LOOP
4668: FETCH l_contact_pt_cur INTO
4669: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 4763: l_contact_pt_contains_str VARCHAR2(32000);

4759: ) IS
4760:
4761:
4762: -- Strings to hold the generated Intermedia query strings
4763: l_contact_pt_contains_str VARCHAR2(32000);
4764: l_tmp VARCHAR2(32000);
4765:
4766: -- Other local variables
4767: l_match_str VARCHAR2(30); -- Match type (AND or OR)

Line 4821: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);

4817: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4818:
4819: -- ************************************************************
4820: -- STEP 2. Setup of intermedia query strings for Acquisition query
4821: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4822: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,p_contact_point_list);
4823:
4824: -- Setup Search Context ID
4825: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 4829: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

4825: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
4826: x_search_ctx_id := l_search_ctx_id;
4827: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4828: hz_utility_v2pub.debug(p_message=>'l_match_str '||l_match_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4829: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4830: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4831: END IF;
4832:
4833: IF l_contact_pt_contains_str IS NOT NULL THEN

Line 4833: IF l_contact_pt_contains_str IS NOT NULL THEN

4829: hz_utility_v2pub.debug(p_message=>'l_contact_pt_contains_str '||l_contact_pt_contains_str,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4830: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4831: END IF;
4832:
4833: IF l_contact_pt_contains_str IS NOT NULL THEN
4834: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4835: cnt := 1;
4836: LOOP
4837: FETCH l_contact_pt_cur INTO

Line 4834: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);

4830: hz_utility_v2pub.debug(p_message=>'l_search_ctx_id '||l_search_ctx_id,p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
4831: END IF;
4832:
4833: IF l_contact_pt_contains_str IS NOT NULL THEN
4834: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4835: cnt := 1;
4836: LOOP
4837: FETCH l_contact_pt_cur INTO
4838: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;

Line 4905: l_party_contains_str VARCHAR2(32000);

4901: x_search_ctx_id IN OUT NUMBER
4902: ) IS
4903:
4904: -- Strings to hold the generated Intermedia query strings
4905: l_party_contains_str VARCHAR2(32000);
4906: l_party_site_contains_str VARCHAR2(32000);
4907: l_contact_contains_str VARCHAR2(32000);
4908: l_contact_pt_contains_str VARCHAR2(32000);
4909: l_tmp VARCHAR2(32000);

Line 4906: l_party_site_contains_str VARCHAR2(32000);

4902: ) IS
4903:
4904: -- Strings to hold the generated Intermedia query strings
4905: l_party_contains_str VARCHAR2(32000);
4906: l_party_site_contains_str VARCHAR2(32000);
4907: l_contact_contains_str VARCHAR2(32000);
4908: l_contact_pt_contains_str VARCHAR2(32000);
4909: l_tmp VARCHAR2(32000);
4910:

Line 4907: l_contact_contains_str VARCHAR2(32000);

4903:
4904: -- Strings to hold the generated Intermedia query strings
4905: l_party_contains_str VARCHAR2(32000);
4906: l_party_site_contains_str VARCHAR2(32000);
4907: l_contact_contains_str VARCHAR2(32000);
4908: l_contact_pt_contains_str VARCHAR2(32000);
4909: l_tmp VARCHAR2(32000);
4910:
4911: -- Other local variables

Line 4908: l_contact_pt_contains_str VARCHAR2(32000);

4904: -- Strings to hold the generated Intermedia query strings
4905: l_party_contains_str VARCHAR2(32000);
4906: l_party_site_contains_str VARCHAR2(32000);
4907: l_contact_contains_str VARCHAR2(32000);
4908: l_contact_pt_contains_str VARCHAR2(32000);
4909: l_tmp VARCHAR2(32000);
4910:
4911: -- Other local variables
4912: l_match_str VARCHAR2(30); -- Match type (AND or OR)

Line 4974: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);

4970: g_score_until_thresh := false;
4971: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4972: -- ************************************************************
4973: -- STEP 2. Setup of intermedia query strings for Acquisition query
4974: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4975: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4976: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4977: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4978:

Line 4975: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);

4971: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4972: -- ************************************************************
4973: -- STEP 2. Setup of intermedia query strings for Acquisition query
4974: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4975: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4976: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4977: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4978:
4979: -- Setup Search Context ID

Line 4976: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);

4972: -- ************************************************************
4973: -- STEP 2. Setup of intermedia query strings for Acquisition query
4974: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4975: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4976: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4977: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
4978:
4979: -- Setup Search Context ID
4980: IF x_search_ctx_id IS NULL THEN

Line 4996: IF l_party_site_contains_str IS NOT NULL THEN

4992: INSERT_PARTY_SCORE(p_party_id, p_party_id, l_search_ctx_id, p_party_search_rec, g_party_stage_rec, l_TX2,l_TX8,l_TX4,1);
4993: END LOOP;
4994: CLOSE l_party_cur;
4995:
4996: IF l_party_site_contains_str IS NOT NULL THEN
4997: l_max_score := 0;
4998: l_max_id := 0;
4999: l_max_idx := 0;
5000: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

Line 5001: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);

4997: l_max_score := 0;
4998: l_max_id := 0;
4999: l_max_idx := 0;
5000: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5001: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5002: ELSE
5003: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5004: END IF;
5005: LOOP

Line 5003: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);

4999: l_max_idx := 0;
5000: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5001: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5002: ELSE
5003: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5004: END IF;
5005: LOOP
5006: FETCH l_party_site_cur INTO
5007: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX4, l_TX3;

Line 5024: IF l_contact_contains_str IS NOT NULL THEN

5020: INSERT_PARTY_SITES_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_party_site_list(l_max_idx), g_party_site_stage_list(l_max_idx) ,l_max_TX4,l_max_TX3,l_max_idx);
5021: END IF;
5022: END IF;
5023:
5024: IF l_contact_contains_str IS NOT NULL THEN
5025: l_max_score := 0;
5026: l_max_id := 0;
5027: l_max_idx := 0;
5028: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

Line 5028: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

5024: IF l_contact_contains_str IS NOT NULL THEN
5025: l_max_score := 0;
5026: l_max_id := 0;
5027: l_max_idx := 0;
5028: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5029: LOOP
5030: FETCH l_contact_cur INTO
5031: l_org_contact_id, l_ct_party_id ;
5032: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5046: IF l_contact_pt_contains_str IS NOT NULL THEN

5042: INSERT_CONTACTS_SCORE(p_party_id,l_max_id,l_search_ctx_id, p_contact_list(l_max_idx), g_contact_stage_list(l_max_idx) ,l_max_idx);
5043: END IF;
5044: END IF;
5045:
5046: IF l_contact_pt_contains_str IS NOT NULL THEN
5047: l_max_score := 0;
5048: l_max_id := 0;
5049: l_max_idx := 0;
5050: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

Line 5051: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);

5047: l_max_score := 0;
5048: l_max_id := 0;
5049: l_max_idx := 0;
5050: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5051: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5052: ELSE
5053: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5054: END IF;
5055: LOOP

Line 5053: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);

5049: l_max_idx := 0;
5050: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5051: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5052: ELSE
5053: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5054: END IF;
5055: LOOP
5056: FETCH l_contact_pt_cur INTO
5057: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id ;