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_7 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 972: l_party_contains_str VARCHAR2(32000);

968: p_party_max_score NUMBER,
969: p_denorm_max_score NUMBER,
970: p_non_denorm_max_score NUMBER,
971: p_threshold NUMBER) RETURN VARCHAR2 IS
972: l_party_contains_str VARCHAR2(32000);
973: l_party_filter VARCHAR2(1) := null;
974: l_prim_temp VARCHAR2(4000);
975: l_denorm_str VARCHAR2(4000);
976: BEGIN

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

1012:
1013: IF l_denorm_str IS NOT NULL THEN
1014: IF (p_non_denorm_max_score+50)>=p_threshold THEN
1015: l_denorm_str := NULL;
1016: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1017: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
1018: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1019: l_denorm_str := NULL;
1020: END IF;

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

1014: IF (p_non_denorm_max_score+50)>=p_threshold THEN
1015: l_denorm_str := NULL;
1016: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1017: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
1018: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1019: l_denorm_str := NULL;
1020: END IF;
1021: ELSE
1022: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1018: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1019: l_denorm_str := NULL;
1020: END IF;
1021: ELSE
1022: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1023: END IF;
1024:
1025: -- Setup query string for TAX_REFERENCE
1026: l_prim_temp := null;

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

1029:
1030: IF l_denorm_str IS NOT NULL THEN
1031: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1032: l_denorm_str := NULL;
1033: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1034: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1035: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1036: l_denorm_str := NULL;
1037: END IF;

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

1031: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1032: l_denorm_str := NULL;
1033: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1034: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1035: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1036: l_denorm_str := NULL;
1037: END IF;
1038: ELSE
1039: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1035: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1036: l_denorm_str := NULL;
1037: END IF;
1038: ELSE
1039: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1040: END IF;
1041: IF lengthb(l_party_contains_str) > 4000 THEN
1042: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1043: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');

Line 1041: IF lengthb(l_party_contains_str) > 4000 THEN

1037: END IF;
1038: ELSE
1039: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1040: END IF;
1041: IF lengthb(l_party_contains_str) > 4000 THEN
1042: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1043: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1044: FND_MSG_PUB.ADD;
1045: RAISE FND_API.G_EXC_ERROR;

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

1043: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1044: FND_MSG_PUB.ADD;
1045: RAISE FND_API.G_EXC_ERROR;
1046: END IF;
1047: IF (p_match_str = ' AND ' OR p_party_max_score 1048: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1049: 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);
1050: END IF;
1051: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

Line 1049: 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);

1045: RAISE FND_API.G_EXC_ERROR;
1046: END IF;
1047: IF (p_match_str = ' AND ' OR p_party_max_score 1048: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1049: 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);
1050: END IF;
1051: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1052: 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);
1053: END IF;

Line 1052: 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);

1048: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1049: 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);
1050: END IF;
1051: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1052: 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);
1053: END IF;
1054: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1055: 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);
1056: END IF;

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

1053: END IF;
1054: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1055: 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);
1056: END IF;
1057: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1058: ELSE
1059: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1060: 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);
1061: 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 1060: 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);

1056: END IF;
1057: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1058: ELSE
1059: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1060: 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);
1061: 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);
1062: END IF;
1063: RETURN l_party_contains_str;
1064: END IF;

Line 1061: 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);

1057: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1058: ELSE
1059: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1060: 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);
1061: 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);
1062: END IF;
1063: RETURN l_party_contains_str;
1064: END IF;
1065: END;

Line 1063: RETURN l_party_contains_str;

1059: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1060: 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);
1061: 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);
1062: END IF;
1063: RETURN l_party_contains_str;
1064: END IF;
1065: END;
1066: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1067: l_contains_str VARCHAR2(32000);

Line 1067: l_contains_str VARCHAR2(32000);

1063: RETURN l_party_contains_str;
1064: END IF;
1065: END;
1066: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1067: l_contains_str VARCHAR2(32000);
1068: l_contains_str_temp VARCHAR2(32000);
1069: l_den_contains_str VARCHAR2(32000);
1070: l_den_contains_str_temp VARCHAR2(32000);
1071: l_filter_str VARCHAR2(4000) := null;

Line 1068: l_contains_str_temp VARCHAR2(32000);

1064: END IF;
1065: END;
1066: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1067: l_contains_str VARCHAR2(32000);
1068: l_contains_str_temp VARCHAR2(32000);
1069: l_den_contains_str VARCHAR2(32000);
1070: l_den_contains_str_temp VARCHAR2(32000);
1071: l_filter_str VARCHAR2(4000) := null;
1072: l_prim_temp VARCHAR2(4000) := null;

Line 1069: l_den_contains_str VARCHAR2(32000);

1065: END;
1066: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1067: l_contains_str VARCHAR2(32000);
1068: l_contains_str_temp VARCHAR2(32000);
1069: l_den_contains_str VARCHAR2(32000);
1070: l_den_contains_str_temp VARCHAR2(32000);
1071: l_filter_str VARCHAR2(4000) := null;
1072: l_prim_temp VARCHAR2(4000) := null;
1073: l_prim_temp_den VARCHAR2(4000) := null;

Line 1070: l_den_contains_str_temp VARCHAR2(32000);

1066: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1067: l_contains_str VARCHAR2(32000);
1068: l_contains_str_temp VARCHAR2(32000);
1069: l_den_contains_str VARCHAR2(32000);
1070: l_den_contains_str_temp VARCHAR2(32000);
1071: l_filter_str VARCHAR2(4000) := null;
1072: l_prim_temp VARCHAR2(4000) := null;
1073: l_prim_temp_den VARCHAR2(4000) := null;
1074: BEGIN

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

1076: 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);
1077: END IF;
1078: x_denorm_str := NULL;
1079:
1080: -- Dynamic setup of party site contains str
1081: --
1082: -- For each primary transformation add to intermedia query if it
1083: -- is not null
1084: FOR I IN 1..g_party_site_stage_list.COUNT LOOP

Line 1085: l_contains_str_temp := null;

1081: --
1082: -- For each primary transformation add to intermedia query if it
1083: -- is not null
1084: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1085: l_contains_str_temp := null;
1086: l_den_contains_str_temp := null;
1087: l_filter_str := null;
1088:
1089: -- Setup query string for ADDRESS

Line 1086: l_den_contains_str_temp := null;

1082: -- For each primary transformation add to intermedia query if it
1083: -- is not null
1084: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1085: l_contains_str_temp := null;
1086: l_den_contains_str_temp := null;
1087: l_filter_str := null;
1088:
1089: -- Setup query string for ADDRESS
1090: l_prim_temp := null;

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

1090: l_prim_temp := null;
1091: l_prim_temp_den := null;
1092: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRADDRESS
1093: g_party_site_stage_list(I).TX4,'A27',l_prim_temp);
1094: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1095:
1096: -- Setup query string for POSTAL_CODE
1097: l_prim_temp := null;
1098: l_prim_temp_den := null;

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

1099: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1100: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1101: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1102: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1103: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1104: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1105:
1106: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1107: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);

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

1100: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1101: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1102: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1103: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1104: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1105:
1106: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1107: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1108: END LOOP;

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

1102: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1103: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1104: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1105:
1106: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1107: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1108: END LOOP;
1109: -- Add the search criteria to query string
1110: IF lengthb(l_contains_str) > 4000 THEN

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

1103: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1104: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1105:
1106: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1107: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1108: END LOOP;
1109: -- Add the search criteria to query string
1110: IF lengthb(l_contains_str) > 4000 THEN
1111: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 1110: IF lengthb(l_contains_str) > 4000 THEN

1106: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1107: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1108: END LOOP;
1109: -- Add the search criteria to query string
1110: IF lengthb(l_contains_str) > 4000 THEN
1111: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1112: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1113: FND_MSG_PUB.ADD;
1114: RAISE FND_API.G_EXC_ERROR;

Line 1116: x_denorm_str := l_den_contains_str;

1112: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1113: FND_MSG_PUB.ADD;
1114: RAISE FND_API.G_EXC_ERROR;
1115: END IF;
1116: x_denorm_str := l_den_contains_str;
1117: RETURN l_contains_str;
1118: END;
1119:
1120: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS

Line 1117: RETURN l_contains_str;

1113: FND_MSG_PUB.ADD;
1114: RAISE FND_API.G_EXC_ERROR;
1115: END IF;
1116: x_denorm_str := l_den_contains_str;
1117: RETURN l_contains_str;
1118: END;
1119:
1120: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1121: l_contains_str VARCHAR2(32000);

Line 1121: l_contains_str VARCHAR2(32000);

1117: RETURN l_contains_str;
1118: END;
1119:
1120: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1121: l_contains_str VARCHAR2(32000);
1122: l_contains_str_temp VARCHAR2(32000);
1123: l_den_contains_str VARCHAR2(32000);
1124: l_den_contains_str_temp VARCHAR2(32000);
1125: l_filter_str VARCHAR2(4000) := null;

Line 1122: l_contains_str_temp VARCHAR2(32000);

1118: END;
1119:
1120: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1121: l_contains_str VARCHAR2(32000);
1122: l_contains_str_temp VARCHAR2(32000);
1123: l_den_contains_str VARCHAR2(32000);
1124: l_den_contains_str_temp VARCHAR2(32000);
1125: l_filter_str VARCHAR2(4000) := null;
1126: l_prim_temp VARCHAR2(4000) := null;

Line 1123: l_den_contains_str VARCHAR2(32000);

1119:
1120: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1121: l_contains_str VARCHAR2(32000);
1122: l_contains_str_temp VARCHAR2(32000);
1123: l_den_contains_str VARCHAR2(32000);
1124: l_den_contains_str_temp VARCHAR2(32000);
1125: l_filter_str VARCHAR2(4000) := null;
1126: l_prim_temp VARCHAR2(4000) := null;
1127: l_prim_temp_den VARCHAR2(4000) := null;

Line 1124: l_den_contains_str_temp VARCHAR2(32000);

1120: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1121: l_contains_str VARCHAR2(32000);
1122: l_contains_str_temp VARCHAR2(32000);
1123: l_den_contains_str VARCHAR2(32000);
1124: l_den_contains_str_temp VARCHAR2(32000);
1125: l_filter_str VARCHAR2(4000) := null;
1126: l_prim_temp VARCHAR2(4000) := null;
1127: l_prim_temp_den VARCHAR2(4000) := null;
1128: BEGIN

Line 1137: l_contains_str VARCHAR2(32000);

1133: RETURN NULL;
1134: END;
1135:
1136: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1137: l_contains_str VARCHAR2(32000);
1138: l_contains_str_temp VARCHAR2(32000);
1139: l_den_contains_str VARCHAR2(32000);
1140: l_den_contains_str_temp VARCHAR2(32000);
1141: l_filter_str VARCHAR2(4000) := null;

Line 1138: l_contains_str_temp VARCHAR2(32000);

1134: END;
1135:
1136: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1137: l_contains_str VARCHAR2(32000);
1138: l_contains_str_temp VARCHAR2(32000);
1139: l_den_contains_str VARCHAR2(32000);
1140: l_den_contains_str_temp VARCHAR2(32000);
1141: l_filter_str VARCHAR2(4000) := null;
1142: l_prim_temp VARCHAR2(4000) := null;

Line 1139: l_den_contains_str VARCHAR2(32000);

1135:
1136: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1137: l_contains_str VARCHAR2(32000);
1138: l_contains_str_temp VARCHAR2(32000);
1139: l_den_contains_str VARCHAR2(32000);
1140: l_den_contains_str_temp VARCHAR2(32000);
1141: l_filter_str VARCHAR2(4000) := null;
1142: l_prim_temp VARCHAR2(4000) := null;
1143: l_prim_temp_den VARCHAR2(4000) := null;

Line 1140: l_den_contains_str_temp VARCHAR2(32000);

1136: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1137: l_contains_str VARCHAR2(32000);
1138: l_contains_str_temp VARCHAR2(32000);
1139: l_den_contains_str VARCHAR2(32000);
1140: l_den_contains_str_temp VARCHAR2(32000);
1141: l_filter_str VARCHAR2(4000) := null;
1142: l_prim_temp VARCHAR2(4000) := null;
1143: l_prim_temp_den VARCHAR2(4000) := null;
1144: BEGIN

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

1146: hz_utility_v2pub.debug(p_message=>'INIT_CONTACT_POINTS_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
1147: END IF;
1148: x_denorm_str := NULL;
1149:
1150: -- Dynamic setup of party site contains str
1151: --
1152: -- For each primary transformation add to intermedia query if it
1153: -- is not null
1154: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP

Line 1155: l_contains_str_temp := null;

1151: --
1152: -- For each primary transformation add to intermedia query if it
1153: -- is not null
1154: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1155: l_contains_str_temp := null;
1156: l_den_contains_str_temp := null;
1157: l_filter_str := null;
1158: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1159:

Line 1156: l_den_contains_str_temp := null;

1152: -- For each primary transformation add to intermedia query if it
1153: -- is not null
1154: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1155: l_contains_str_temp := null;
1156: l_den_contains_str_temp := null;
1157: l_filter_str := null;
1158: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1159:
1160: -- Setup query string for EMAIL_ADDRESS

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

1161: l_prim_temp := null;
1162: l_prim_temp_den := null;
1163: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1164: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1165: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1166:
1167: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1168: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1169: END LOOP;

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

1163: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE (EMAIL) + WRDOMAIN
1164: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1165: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1166:
1167: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1168: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1169: END LOOP;
1170: -- Add the search criteria to query string
1171: IF lengthb(l_contains_str) > 4000 THEN

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

1164: g_contact_pt_stage_list(I).TX6,'A48',l_prim_temp);
1165: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1166:
1167: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1168: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1169: END LOOP;
1170: -- Add the search criteria to query string
1171: IF lengthb(l_contains_str) > 4000 THEN
1172: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 1171: IF lengthb(l_contains_str) > 4000 THEN

1167: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1168: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1169: END LOOP;
1170: -- Add the search criteria to query string
1171: IF lengthb(l_contains_str) > 4000 THEN
1172: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1173: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1174: FND_MSG_PUB.ADD;
1175: RAISE FND_API.G_EXC_ERROR;

Line 1177: x_denorm_str := l_den_contains_str;

1173: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1174: FND_MSG_PUB.ADD;
1175: RAISE FND_API.G_EXC_ERROR;
1176: END IF;
1177: x_denorm_str := l_den_contains_str;
1178: RETURN l_contains_str;
1179: END;
1180:
1181: PROCEDURE open_party_cursor_direct (

Line 1178: RETURN l_contains_str;

1174: FND_MSG_PUB.ADD;
1175: RAISE FND_API.G_EXC_ERROR;
1176: END IF;
1177: x_denorm_str := l_den_contains_str;
1178: RETURN l_contains_str;
1179: END;
1180:
1181: PROCEDURE open_party_cursor_direct (
1182: p_dup_party_id NUMBER,

Line 1186: p_party_contains_str VARCHAR2,

1182: p_dup_party_id NUMBER,
1183: p_restrict_sql VARCHAR2,
1184: p_match_str VARCHAR2,
1185: p_search_merged VARCHAR2,
1186: p_party_contains_str VARCHAR2,
1187: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1188: l_sqlstr VARCHAR2(4000);
1189: l_search_merged VARCHAR2(1);
1190: BEGIN

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

1199: END IF;
1200: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1201: 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);
1202: END IF;
1203: 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
1204: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1205: 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);
1206: END IF;
1207: OPEN x_cursor FOR

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

1231: l_sqlstr := l_sqlstr || ' (TX44 like :TX44||'' %'' ';
1232: l_sqlstr := l_sqlstr || ' )) ';
1233: l_sqlstr := l_sqlstr || ' ) ';
1234: END IF;
1235: 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
1236: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1237: 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);
1238: END IF;
1239: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';

Line 1237: 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);

1233: l_sqlstr := l_sqlstr || ' ) ';
1234: END IF;
1235: 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
1236: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1237: 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);
1238: END IF;
1239: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1240: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX11||'' %'' ';
1241: l_sqlstr := l_sqlstr || ' )) ';

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

1254: END IF;
1255: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1256: 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);
1257: END IF;
1258: 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
1259: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1260: 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);
1261: END IF;
1262: 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 1260: 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);

1256: 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);
1257: END IF;
1258: 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
1259: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1260: 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);
1261: END IF;
1262: 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
1263: ,g_party_stage_rec.TX44
1264: ,g_party_stage_rec.TX44

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

1272: 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
1273: ,g_party_stage_rec.TX44
1274: ,g_party_stage_rec.TX44
1275: ;
1276: 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
1277: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1278: 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);
1279: END IF;
1280: 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 1278: 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);

1274: ,g_party_stage_rec.TX44
1275: ;
1276: 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
1277: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1278: 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);
1279: END IF;
1280: 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
1281: ,g_party_site_stage_list(1).TX11
1282: ,g_party_site_stage_list(1).TX11

Line 1295: p_contains_str VARCHAR2,

1291: END;
1292: PROCEDURE open_party_cursor(
1293: p_dup_party_id NUMBER,
1294: p_restrict_sql VARCHAR2,
1295: p_contains_str VARCHAR2,
1296: p_search_ctx_id NUMBER,
1297: p_match_str VARCHAR2,
1298: p_search_merged VARCHAR2,
1299: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS

Line 1314: IF p_contains_str IS NULL THEN

1310: l_search_merged := 'N';
1311: ELSE
1312: l_search_merged := p_search_merged;
1313: END IF;
1314: IF p_contains_str IS NULL THEN
1315: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1316: 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);
1317: END IF;
1318: OPEN x_cursor FOR

Line 1316: 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);

1312: l_search_merged := p_search_merged;
1313: END IF;
1314: IF p_contains_str IS NULL THEN
1315: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1316: 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);
1317: END IF;
1318: OPEN x_cursor FOR
1319: SELECT PARTY_ID , TX2, TX8, TX4, TX44
1320: FROM HZ_STAGED_PARTIES stage

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

1329: END IF;
1330: OPEN x_cursor FOR
1331: SELECT /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ PARTY_ID , TX2, TX8, TX4, TX44
1332: FROM HZ_STAGED_PARTIES stage
1333: WHERE contains( concat_col, p_contains_str)>0
1334: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1335: AND( (l_search_merged ='Y' )
1336: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1337: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )

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

1342: END IF;
1343: OPEN x_cursor FOR
1344: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX2, TX8, TX4, TX44
1345: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1346: WHERE contains( concat_col, p_contains_str)>0
1347: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1348: AND d.party_id = stage.party_id
1349: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1350: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)

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

1367: END IF;
1368: IF p_search_ctx_id IS NULL THEN
1369: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX2, TX8, TX4, TX44'||
1370: ' FROM HZ_STAGED_PARTIES stage'||
1371: ' WHERE contains( concat_col, :cont)>0'||
1372: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1373: ' AND ('||p_restrict_sql||')' ||
1374: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1375: IF l_search_merged = 'Y' THEN

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

1379: ELSE
1380: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1381: END IF;
1382: output_long_strings('----------------------------------------------------------');
1383: output_long_strings('Party Contains String = '||p_contains_str);
1384: output_long_strings('Restrict Sql = '||p_restrict_sql);
1385: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1386: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1387: ,p_dup_party_id, p_dup_party_id;

Line 1385: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1381: END IF;
1382: output_long_strings('----------------------------------------------------------');
1383: output_long_strings('Party Contains String = '||p_contains_str);
1384: output_long_strings('Restrict Sql = '||p_restrict_sql);
1385: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1386: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1387: ,p_dup_party_id, p_dup_party_id;
1388: END IF;
1389: END IF;

Line 1416: p_contains_str VARCHAR2,

1412: PROCEDURE open_party_site_cursor(
1413: p_dup_party_id NUMBER,
1414: p_party_id NUMBER,
1415: p_restrict_sql VARCHAR2,
1416: p_contains_str VARCHAR2,
1417: p_search_ctx_id NUMBER,
1418: p_search_merged VARCHAR2,
1419: p_search_rel_sites VARCHAR2,
1420: p_person_api VARCHAR2,

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

1444: IF p_search_rel_sites = 'N' THEN
1445: OPEN x_cursor FOR
1446: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1447: FROM HZ_STAGED_PARTY_SITES stage
1448: WHERE contains( concat_col, p_contains_str)>0
1449: AND( (l_search_merged ='Y' )
1450: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1451: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1452: AND stage.party_id = p_party_id;

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

1453: ELSE
1454: OPEN x_cursor FOR
1455: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1456: FROM HZ_STAGED_PARTY_SITES stage
1457: WHERE contains( concat_col, p_contains_str)>0
1458: AND( (l_search_merged ='Y' )
1459: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1460: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1461: AND stage.party_id = p_party_id

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

1461: AND stage.party_id = p_party_id
1462: UNION
1463: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3, TX4, TX11
1464: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1465: WHERE contains( concat_col, p_contains_str)>0
1466: AND( (l_search_merged ='Y' )
1467: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1468: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1469: AND r.object_id = p_party_id

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

1483: END IF;
1484: OPEN x_cursor FOR
1485: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1486: FROM HZ_STAGED_PARTY_SITES stage
1487: WHERE contains( concat_col, p_contains_str)>0
1488: AND EXISTS (
1489: SELECT 1 FROM HZ_STAGED_PARTIES p
1490: WHERE p.PARTY_ID = stage.PARTY_ID
1491: AND( (l_search_merged ='Y' )

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

1503: IF p_person_api = 'Y' THEN
1504: OPEN x_cursor FOR
1505: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1506: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1507: WHERE contains( concat_col, p_contains_str)>0
1508: AND( (l_search_merged ='Y' )
1509: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1510: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1511: AND d.search_context_id = p_search_ctx_id

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

1513: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1514: UNION
1515: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3, TX4, TX11
1516: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1517: WHERE contains( concat_col, p_contains_str)>0
1518: AND( (l_search_merged ='Y' )
1519: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1520: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1521: AND d.search_context_id = p_search_ctx_id

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

1526: ELSE
1527: OPEN x_cursor FOR
1528: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1529: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1530: WHERE contains( concat_col, p_contains_str)>0
1531: AND( (l_search_merged ='Y' )
1532: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1533: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1534: AND d.search_context_id = p_search_ctx_id

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

1571: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1572: END IF;
1573: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1574: ' FROM HZ_STAGED_PARTY_SITES stage'||
1575: ' WHERE contains( concat_col, :cont)>0'||
1576: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1577: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1578: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1579: ' AND (ORG_CONTACT_ID IS NULL ' ||

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

1581: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1582: ' UNION ' ||
1583: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1584: ' FROM HZ_STAGED_PARTY_SITES stage'||
1585: ' WHERE contains( concat_col, :cont)>0'||
1586: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1587: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1588: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1589: ' AND ORG_CONTACT_ID IN ' ||

Line 1595: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1591: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1592: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1593: ' and ('||p_restrict_sql1|| ') )' ||
1594: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1595: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1596: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1597: ELSE
1598: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1599: ' FROM HZ_STAGED_PARTY_SITES stage'||

Line 1596: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1592: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1593: ' and ('||p_restrict_sql1|| ') )' ||
1594: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1595: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1596: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1597: ELSE
1598: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1599: ' FROM HZ_STAGED_PARTY_SITES stage'||
1600: ' WHERE contains( concat_col, :cont)>0'||

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

1596: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1597: ELSE
1598: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1599: ' FROM HZ_STAGED_PARTY_SITES stage'||
1600: ' WHERE contains( concat_col, :cont)>0'||
1601: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1602: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1603: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1604: ' AND EXISTS ('||

Line 1610: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1606: ' WHERE p.party_id = stage.party_id ' ||
1607: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1608: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1609: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1610: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1611: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1612: ,p_dup_party_id, p_dup_party_id;
1613: END IF;
1614: ELSE

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

1613: END IF;
1614: ELSE
1615: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1616: ' FROM HZ_STAGED_PARTY_SITES stage'||
1617: ' WHERE contains( concat_col, :cont)>0'||
1618: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1619: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1620: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1621: ' AND EXISTS ('||

Line 1627: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1623: ' WHERE p.party_id = stage.party_id ' ||
1624: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1625: ' AND ('||p_restrict_sql||')' ||
1626: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1627: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1628: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1629: ,p_dup_party_id, p_dup_party_id;
1630: END IF;
1631: END IF;

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

1629: ,p_dup_party_id, p_dup_party_id;
1630: END IF;
1631: END IF;
1632: output_long_strings('----------------------------------------------------------');
1633: output_long_strings('Party Site Contains String = '||p_contains_str);
1634: output_long_strings('Restrict Sql = '||p_restrict_sql);
1635: exception
1636: when others then
1637: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1661: p_contains_str VARCHAR2,

1657: PROCEDURE open_contact_cursor(
1658: p_dup_party_id NUMBER,
1659: p_party_id NUMBER,
1660: p_restrict_sql VARCHAR2,
1661: p_contains_str VARCHAR2,
1662: p_search_ctx_id NUMBER,
1663: p_search_merged VARCHAR2,
1664: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1665: l_sqlstr VARCHAR2(4000);

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

1685: END IF;
1686: OPEN x_cursor FOR
1687: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID
1688: FROM HZ_STAGED_CONTACTS stage
1689: WHERE contains( concat_col, p_contains_str)>0
1690: AND EXISTS (
1691: SELECT 1 FROM HZ_STAGED_PARTIES p
1692: WHERE p.PARTY_ID = stage.PARTY_ID
1693: AND( (l_search_merged ='Y' )

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

1708: END IF;
1709: OPEN x_cursor FOR
1710: SELECT ORG_CONTACT_ID, PARTY_ID
1711: FROM HZ_STAGED_CONTACTS stage
1712: WHERE contains( concat_col, p_contains_str)>0
1713: AND EXISTS (
1714: SELECT 1 FROM HZ_STAGED_PARTIES p
1715: WHERE p.PARTY_ID = stage.PARTY_ID
1716: AND( (l_search_merged ='Y' )

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

1727: END IF;
1728: OPEN x_cursor FOR
1729: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID
1730: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1731: WHERE contains( concat_col, p_contains_str)>0
1732: AND d.search_context_id = p_search_ctx_id
1733: AND d.party_id = stage.party_id
1734: AND( (l_search_merged ='Y' )
1735: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1761: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1762: END IF;
1763: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID '||
1764: ' FROM HZ_STAGED_CONTACTS stage'||
1765: ' WHERE contains( concat_col, :cont)>0'||
1766: ' AND EXISTS ('||
1767: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1768: ' WHERE p.party_id = stage.party_id ' ||
1769: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1775: OPEN x_cursor FOR l_sqlstr USING p_contains_str

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

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

1776: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1777: ,p_dup_party_id, p_dup_party_id;
1778: END IF;
1779: output_long_strings('----------------------------------------------------------');
1780: output_long_strings('Contacts Contains String = '||p_contains_str);
1781: output_long_strings('Restrict Sql = '||p_restrict_sql);
1782: exception
1783: when others then
1784: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1808: p_contains_str VARCHAR2,

1804: PROCEDURE open_contact_pt_cursor(
1805: p_dup_party_id NUMBER,
1806: p_party_id NUMBER,
1807: p_restrict_sql VARCHAR2,
1808: p_contains_str VARCHAR2,
1809: p_search_ctx_id NUMBER,
1810: p_search_merged VARCHAR2,
1811: p_search_rel_cpts VARCHAR2,
1812: p_person_api VARCHAR2,

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

1834: THEN
1835: OPEN x_cursor FOR
1836: 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 , TX1, TX6, TX5
1837: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1838: WHERE contains( concat_col, p_contains_str)>0
1839: AND d.search_context_id = p_search_ctx_id
1840: AND( (l_search_merged ='Y' )
1841: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1842: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1846: THEN
1847: OPEN x_cursor FOR
1848: 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 , TX1, TX6, TX5
1849: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1850: WHERE contains( concat_col, p_contains_str)>0
1851: AND d.search_context_id = p_search_ctx_id
1852: AND( (l_search_merged ='Y' )
1853: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1854: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1863: IF p_search_rel_cpts = 'N' THEN
1864: OPEN x_cursor FOR
1865: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1866: FROM HZ_STAGED_CONTACT_POINTS stage
1867: WHERE contains( concat_col, p_contains_str)>0
1868: AND( (l_search_merged ='Y' )
1869: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1870: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1871: AND stage.party_id = p_party_id;

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

1872: ELSE
1873: OPEN x_cursor FOR
1874: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1875: FROM HZ_STAGED_CONTACT_POINTS stage
1876: WHERE contains( concat_col, p_contains_str)>0
1877: AND( (l_search_merged ='Y' )
1878: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1879: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1880: AND stage.party_id = p_party_id

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

1880: AND stage.party_id = p_party_id
1881: UNION
1882: 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 , TX1, TX6, TX5
1883: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
1884: WHERE contains( concat_col, p_contains_str)>0
1885: AND r.object_id = p_party_id
1886: AND( (l_search_merged ='Y' )
1887: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1888: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

1902: END IF;
1903: OPEN x_cursor FOR
1904: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1905: FROM HZ_STAGED_CONTACT_POINTS stage
1906: WHERE contains( concat_col, p_contains_str)>0
1907: AND EXISTS (
1908: SELECT 1 FROM HZ_STAGED_PARTIES p
1909: WHERE p.PARTY_ID = stage.PARTY_ID
1910: AND( (l_search_merged ='Y' )

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

1922: IF p_person_api = 'Y' THEN
1923: OPEN x_cursor FOR
1924: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1925: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1926: WHERE contains( concat_col, p_contains_str)>0
1927: AND d.search_context_id = p_search_ctx_id
1928: AND d.party_id = stage.party_id
1929: AND( (l_search_merged ='Y' )
1930: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1932: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1933: UNION
1934: 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 , TX1, TX6, TX5
1935: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
1936: WHERE contains( concat_col, p_contains_str)>0
1937: AND d.search_context_id = p_search_ctx_id
1938: AND d.party_id = r.subject_id
1939: AND r.relationship_id = oc.party_relationship_id
1940: AND oc.org_contact_id = stage.org_contact_id

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

1945: ELSE
1946: OPEN x_cursor FOR
1947: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5
1948: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
1949: WHERE contains( concat_col, p_contains_str)>0
1950: AND d.search_context_id = p_search_ctx_id
1951: AND d.party_id = stage.party_id
1952: AND( (l_search_merged ='Y' )
1953: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1990: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1991: END IF;
1992: 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 , TX1, TX6, TX5'||
1993: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
1994: ' WHERE contains( concat_col, :cont)>0 '||
1995: ' AND (stage.org_contact_id is null '||
1996: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1997: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1998: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

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

2000: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2001: ' UNION ' ||
2002: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2003: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2004: ' WHERE contains( concat_col, :cont)>0 '||
2005: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2006: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2007: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2008: ' AND (stage.org_contact_id in ' ||

Line 2014: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

2010: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2011: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2012: ' and ('||p_restrict_sql1 || ') ) )' ||
2013: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2014: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2015: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2016: ELSE
2017: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2018: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

Line 2015: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

2011: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2012: ' and ('||p_restrict_sql1 || ') ) )' ||
2013: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2014: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2015: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2016: ELSE
2017: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2018: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2019: ' WHERE contains( concat_col, :cont)>0'||

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

2015: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2016: ELSE
2017: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2018: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2019: ' WHERE contains( concat_col, :cont)>0'||
2020: ' AND EXISTS ('||
2021: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2022: ' WHERE p.party_id = stage.party_id ' ||
2023: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 2029: OPEN x_cursor FOR l_sqlstr USING p_contains_str

2025: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2026: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2027: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2028: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2029: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2030: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2031: ,p_dup_party_id, p_dup_party_id;
2032: END IF;
2033: ELSE

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

2032: END IF;
2033: ELSE
2034: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1, TX6, TX5'||
2035: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2036: ' WHERE contains( concat_col, :cont)>0'||
2037: ' AND EXISTS ('||
2038: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2039: ' WHERE p.party_id = stage.party_id ' ||
2040: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 2046: OPEN x_cursor FOR l_sqlstr USING p_contains_str

2042: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2043: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2044: ' AND ('||p_restrict_sql||')' ||
2045: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2046: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2047: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2048: ,p_dup_party_id, p_dup_party_id;
2049: END IF;
2050: END IF;

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

2049: END IF;
2050: END IF;
2051: END IF;
2052: output_long_strings('----------------------------------------------------------');
2053: output_long_strings('Contact Points Contains String = '||p_contains_str);
2054: output_long_strings('Restrict Sql = '||p_restrict_sql);
2055: exception
2056: when others then
2057: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 2174: l_party_contains_str VARCHAR2(32000);

2170: x_num_matches OUT NUMBER
2171: ) IS
2172:
2173: -- Strings to hold the generated Intermedia query strings
2174: l_party_contains_str VARCHAR2(32000);
2175: l_party_site_contains_str VARCHAR2(32000);
2176: l_contact_contains_str VARCHAR2(32000);
2177: l_contact_pt_contains_str VARCHAR2(32000);
2178: l_denorm_str VARCHAR2(32000);

Line 2175: l_party_site_contains_str VARCHAR2(32000);

2171: ) IS
2172:
2173: -- Strings to hold the generated Intermedia query strings
2174: l_party_contains_str VARCHAR2(32000);
2175: l_party_site_contains_str VARCHAR2(32000);
2176: l_contact_contains_str VARCHAR2(32000);
2177: l_contact_pt_contains_str VARCHAR2(32000);
2178: l_denorm_str VARCHAR2(32000);
2179: l_ps_denorm_str VARCHAR2(32000);

Line 2176: l_contact_contains_str VARCHAR2(32000);

2172:
2173: -- Strings to hold the generated Intermedia query strings
2174: l_party_contains_str VARCHAR2(32000);
2175: l_party_site_contains_str VARCHAR2(32000);
2176: l_contact_contains_str VARCHAR2(32000);
2177: l_contact_pt_contains_str VARCHAR2(32000);
2178: l_denorm_str VARCHAR2(32000);
2179: l_ps_denorm_str VARCHAR2(32000);
2180: l_ct_denorm_str VARCHAR2(32000);

Line 2177: l_contact_pt_contains_str VARCHAR2(32000);

2173: -- Strings to hold the generated Intermedia query strings
2174: l_party_contains_str VARCHAR2(32000);
2175: l_party_site_contains_str VARCHAR2(32000);
2176: l_contact_contains_str VARCHAR2(32000);
2177: l_contact_pt_contains_str VARCHAR2(32000);
2178: l_denorm_str VARCHAR2(32000);
2179: l_ps_denorm_str VARCHAR2(32000);
2180: l_ct_denorm_str VARCHAR2(32000);
2181: l_cpt_denorm_str VARCHAR2(32000);

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

2267: call_type(2) := 'OR';
2268: END;
2269:
2270: /** Private procedure to acquire and score at party level ***/
2271: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2272: l_party_id_idx NUMBER:=1;
2273: l_ctx_id NUMBER;
2274: l_precalc_score BOOLEAN := FALSE;
2275: l_TX35_new varchar2(4000);

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

2299: l_search_ctx_id,H_PARTY_ID(I));
2300: l_party_id_idx := H_PARTY_ID.COUNT+1;
2301: ELSE
2302: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2303: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2304: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2305: 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);
2306: END IF;
2307: 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 2311: IF (l_party_site_contains_str IS NULL AND

2307: 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
2308: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2309: 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);
2310: END IF;
2311: IF (l_party_site_contains_str IS NULL AND
2312: l_contact_contains_str IS NULL AND
2313: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2314: 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);
2315: RAISE L_RETURN_IMM_EXC;

Line 2312: l_contact_contains_str IS NULL AND

2308: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2309: 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);
2310: END IF;
2311: IF (l_party_site_contains_str IS NULL AND
2312: l_contact_contains_str IS NULL AND
2313: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2314: 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);
2315: RAISE L_RETURN_IMM_EXC;
2316: ELSE

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

2309: 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);
2310: END IF;
2311: IF (l_party_site_contains_str IS NULL AND
2312: l_contact_contains_str IS NULL AND
2313: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2314: 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);
2315: RAISE L_RETURN_IMM_EXC;
2316: ELSE
2317: 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 2317: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

2313: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2314: 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);
2315: RAISE L_RETURN_IMM_EXC;
2316: ELSE
2317: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2318: END IF;
2319: ELSE
2320: IF p_index>1 THEN
2321: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

Line 2340: 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);

2336: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2337: 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);
2338: END IF;
2339: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2340: 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);
2341: END IF;
2342: LOOP
2343: FETCH l_party_cur INTO
2344: l_party_id , l_TX2, l_TX8, l_TX4, l_TX44;

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

2358: l_party_id_idx:= l_party_id_idx+1;
2359: END IF;
2360: IF (l_party_id_idx-1)>l_max_thresh THEN
2361: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2362: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2363: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2364: AND l_contact_pt_contains_str IS NULL THEN
2365: EXIT;
2366: END IF;

Line 2364: AND l_contact_pt_contains_str IS NULL THEN

2360: IF (l_party_id_idx-1)>l_max_thresh THEN
2361: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2362: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2363: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2364: AND l_contact_pt_contains_str IS NULL THEN
2365: EXIT;
2366: END IF;
2367: CLOSE l_party_cur;
2368: IF p_index>1 THEN

Line 2393: PROCEDURE eval_party_site_level(p_party_site_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS

2389: ROLLBACK to eval_start;
2390: END;
2391:
2392: /** Private procedure to acquire and score at party site level ***/
2393: PROCEDURE eval_party_site_level(p_party_site_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
2394: l_party_id_idx NUMBER:=1;
2395: l_ctx_id NUMBER;
2396: h_ps_id HZ_PARTY_SEARCH.IDList;
2397: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

2401: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2402: hz_utility_v2pub.debug(p_message=>'-----------------',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2403: 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);
2404: END IF;
2405: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2406: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2407: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2408: l_party_id := H_SCORES.FIRST;
2409: WHILE l_party_id IS NOT NULL LOOP

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

2402: hz_utility_v2pub.debug(p_message=>'-----------------',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
2403: 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);
2404: END IF;
2405: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2406: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2407: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2408: l_party_id := H_SCORES.FIRST;
2409: WHILE l_party_id IS NOT NULL LOOP
2410: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;

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

2403: 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);
2404: END IF;
2405: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2406: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2407: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2408: l_party_id := H_SCORES.FIRST;
2409: WHILE l_party_id IS NOT NULL LOOP
2410: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2411: l_party_id:=H_SCORES.NEXT(l_party_id);

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

2433: l_search_ctx_id,H_PARTY_ID(I));
2434: l_party_id_idx := H_PARTY_ID.COUNT+1;
2435: ELSE
2436: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2437: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2438: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2439: 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);
2440: END IF;
2441: IF p_index>1 THEN

Line 2459: 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);

2455: END IF;
2456: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2457: 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);
2458: END IF;
2459: 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);
2460: LOOP
2461: FETCH l_party_site_cur INTO
2462: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;
2463: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 2515: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS

2511: END IF;
2512: END;
2513:
2514: /** Private procedure to acquire and score at contact point level ***/
2515: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
2516: l_party_id_idx NUMBER:=1;
2517: l_ctx_id NUMBER;
2518: h_ct_id HZ_PARTY_SEARCH.IDList;
2519: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

2545: l_search_ctx_id,H_PARTY_ID(I));
2546: l_party_id_idx := H_PARTY_ID.COUNT+1;
2547: ELSE
2548: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2549: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2550: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2551: 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);
2552: END IF;
2553: IF p_index>1 THEN

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

2567: END IF;
2568: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2569: 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);
2570: END IF;
2571: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2572: LOOP
2573: FETCH l_contact_cur INTO
2574: l_org_contact_id, l_ct_party_id ;
2575: EXIT WHEN l_contact_cur%NOTFOUND;

Line 2624: PROCEDURE eval_cpt_level(p_contact_pt_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER, p_ins_details VARCHAR2,p_emax_score NUMBER) IS

2620: END IF;
2621: END;
2622:
2623: /** Private procedure to acquire and score at contact point level ***/
2624: PROCEDURE eval_cpt_level(p_contact_pt_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER, p_ins_details VARCHAR2,p_emax_score NUMBER) IS
2625: l_party_id_idx NUMBER:=1;
2626: l_ctx_id NUMBER;
2627: h_cpt_id HZ_PARTY_SEARCH.IDList;
2628: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

2656: l_search_ctx_id,H_PARTY_ID(I));
2657: l_party_id_idx := H_PARTY_ID.COUNT+1;
2658: ELSE
2659: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2660: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2661: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2662: 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);
2663: END IF;
2664: IF p_index>1 THEN

Line 2682: 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);

2678: END IF;
2679: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2680: 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);
2681: END IF;
2682: 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);
2683: LOOP
2684: FETCH l_contact_pt_cur INTO
2685: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
2686: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 2782: IF l_party_contains_str IS NOT NULL THEN

2778: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
2779: l_threshold := 100;
2780: other_acq_criteria_exists := TRUE ;
2781: --check if acquisition criteria exists for any other entity
2782: IF l_party_contains_str IS NOT NULL THEN
2783: acq_cnt := 1;
2784: END IF;
2785: IF l_party_site_contains_str IS NOT NULL THEN
2786: acq_cnt := acq_cnt+1;

Line 2785: IF l_party_site_contains_str IS NOT NULL THEN

2781: --check if acquisition criteria exists for any other entity
2782: IF l_party_contains_str IS NOT NULL THEN
2783: acq_cnt := 1;
2784: END IF;
2785: IF l_party_site_contains_str IS NOT NULL THEN
2786: acq_cnt := acq_cnt+1;
2787: END IF;
2788: IF l_contact_contains_str IS NOT NULL THEN
2789: acq_cnt := acq_cnt+1;

Line 2788: IF l_contact_contains_str IS NOT NULL THEN

2784: END IF;
2785: IF l_party_site_contains_str IS NOT NULL THEN
2786: acq_cnt := acq_cnt+1;
2787: END IF;
2788: IF l_contact_contains_str IS NOT NULL THEN
2789: acq_cnt := acq_cnt+1;
2790: END IF;
2791: IF l_contact_pt_contains_str IS NOT NULL THEN
2792: acq_cnt := acq_cnt+1;

Line 2791: IF l_contact_pt_contains_str IS NOT NULL THEN

2787: END IF;
2788: IF l_contact_contains_str IS NOT NULL THEN
2789: acq_cnt := acq_cnt+1;
2790: END IF;
2791: IF l_contact_pt_contains_str IS NOT NULL THEN
2792: acq_cnt := acq_cnt+1;
2793: END IF;
2794: IF acq_cnt>1 THEN
2795: other_acq_criteria_exists := TRUE;

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

2821: FND_MSG_PUB.ADD;
2822: RAISE FND_API.G_EXC_ERROR;
2823: END IF;
2824: END IF;
2825: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2826: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2827: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2828: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2829: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN

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

2822: RAISE FND_API.G_EXC_ERROR;
2823: END IF;
2824: END IF;
2825: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2826: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2827: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2828: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2829: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2830: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2823: END IF;
2824: END IF;
2825: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2826: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2827: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2828: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2829: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2830: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2831: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

2824: END IF;
2825: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2826: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2827: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2828: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2829: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2830: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2831: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2832: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

2825: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
2826: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2827: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2828: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2829: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2830: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2831: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2832: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2833: END IF;

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

2826: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
2827: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2828: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2829: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2830: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2831: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2832: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2833: END IF;
2834: END;

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

2827: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
2828: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2829: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2830: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2831: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2832: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2833: END IF;
2834: END;
2835: /************ End of find_parties private procedures **********/

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

2828: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2829: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
2830: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2831: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
2832: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
2833: END IF;
2834: END;
2835: /************ End of find_parties private procedures **********/
2836:

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

2911: 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);
2912: END IF;
2913: -- ************************************************************
2914: -- STEP 2. Setup of intermedia query strings for Acquisition query
2915: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2916: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2917: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2918: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2919: 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 2916: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);

2912: END IF;
2913: -- ************************************************************
2914: -- STEP 2. Setup of intermedia query strings for Acquisition query
2915: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2916: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2917: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2918: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2919: 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);
2920: END IF;

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

2913: -- ************************************************************
2914: -- STEP 2. Setup of intermedia query strings for Acquisition query
2915: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
2916: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
2917: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
2918: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2919: 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);
2920: END IF;
2921: l_denorm_max_score:=0;

Line 2949: 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((100/360)*l_entered_max_score));

2945: END IF;
2946: ELSE
2947: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
2948: END IF;
2949: 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((100/360)*l_entered_max_score));
2950: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
2951:
2952: -- Setup Search Context ID
2953: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 2959: IF l_party_contains_str IS NULL THEN

2955:
2956: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2957: 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);
2958: END IF;
2959: IF l_party_contains_str IS NULL THEN
2960: defpt := 1;
2961: END IF;
2962: IF l_party_site_contains_str IS NULL THEN
2963: defps := 1;

Line 2962: IF l_party_site_contains_str IS NULL THEN

2958: END IF;
2959: IF l_party_contains_str IS NULL THEN
2960: defpt := 1;
2961: END IF;
2962: IF l_party_site_contains_str IS NULL THEN
2963: defps := 1;
2964: END IF;
2965: IF l_contact_contains_str IS NULL THEN
2966: defct := 1;

Line 2965: IF l_contact_contains_str IS NULL THEN

2961: END IF;
2962: IF l_party_site_contains_str IS NULL THEN
2963: defps := 1;
2964: END IF;
2965: IF l_contact_contains_str IS NULL THEN
2966: defct := 1;
2967: END IF;
2968: IF l_contact_pt_contains_str IS NULL THEN
2969: defcpt := 1;

Line 2968: IF l_contact_pt_contains_str IS NULL THEN

2964: END IF;
2965: IF l_contact_contains_str IS NULL THEN
2966: defct := 1;
2967: END IF;
2968: IF l_contact_pt_contains_str IS NULL THEN
2969: defcpt := 1;
2970: END IF;
2971:
2972: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

Line 2977: 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);

2973: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2974: 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);
2975: END IF;
2976: 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);
2977: 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);
2978: 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);
2979: 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);
2980: 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);
2981: 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 2978: 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);

2974: 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);
2975: END IF;
2976: 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);
2977: 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);
2978: 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);
2979: 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);
2980: 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);
2981: 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);
2982: END IF;

Line 2979: 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);

2975: END IF;
2976: 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);
2977: 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);
2978: 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);
2979: 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);
2980: 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);
2981: 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);
2982: END IF;
2983: FOR I in 1..5 LOOP

Line 2980: 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);

2976: 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);
2977: 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);
2978: 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);
2979: 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);
2980: 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);
2981: 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);
2982: END IF;
2983: FOR I in 1..5 LOOP
2984: do_eval(I);

Line 3094: l_party_contains_str VARCHAR2(32000);

3090: x_num_matches OUT NUMBER
3091: ) IS
3092:
3093: -- Strings to hold the generated Intermedia query strings
3094: l_party_contains_str VARCHAR2(32000);
3095: l_party_site_contains_str VARCHAR2(32000);
3096: l_contact_contains_str VARCHAR2(32000);
3097: l_contact_pt_contains_str VARCHAR2(32000);
3098: l_denorm_str VARCHAR2(32000);

Line 3095: l_party_site_contains_str VARCHAR2(32000);

3091: ) IS
3092:
3093: -- Strings to hold the generated Intermedia query strings
3094: l_party_contains_str VARCHAR2(32000);
3095: l_party_site_contains_str VARCHAR2(32000);
3096: l_contact_contains_str VARCHAR2(32000);
3097: l_contact_pt_contains_str VARCHAR2(32000);
3098: l_denorm_str VARCHAR2(32000);
3099: l_ps_denorm_str VARCHAR2(32000);

Line 3096: l_contact_contains_str VARCHAR2(32000);

3092:
3093: -- Strings to hold the generated Intermedia query strings
3094: l_party_contains_str VARCHAR2(32000);
3095: l_party_site_contains_str VARCHAR2(32000);
3096: l_contact_contains_str VARCHAR2(32000);
3097: l_contact_pt_contains_str VARCHAR2(32000);
3098: l_denorm_str VARCHAR2(32000);
3099: l_ps_denorm_str VARCHAR2(32000);
3100: l_ct_denorm_str VARCHAR2(32000);

Line 3097: l_contact_pt_contains_str VARCHAR2(32000);

3093: -- Strings to hold the generated Intermedia query strings
3094: l_party_contains_str VARCHAR2(32000);
3095: l_party_site_contains_str VARCHAR2(32000);
3096: l_contact_contains_str VARCHAR2(32000);
3097: l_contact_pt_contains_str VARCHAR2(32000);
3098: l_denorm_str VARCHAR2(32000);
3099: l_ps_denorm_str VARCHAR2(32000);
3100: l_ct_denorm_str VARCHAR2(32000);
3101: l_cpt_denorm_str VARCHAR2(32000);

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

3217: call_type(2) := 'OR';
3218: END;
3219:
3220: /** Private procedure to acquire and score at party level ***/
3221: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3222: l_party_id_idx NUMBER:=1;
3223: l_ctx_id NUMBER;
3224: l_TX35_new varchar2(4000);
3225: BEGIN

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

3239: l_search_ctx_id,H_PARTY_ID(I));
3240: l_party_id_idx := H_PARTY_ID.COUNT+1;
3241: ELSE
3242: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3243: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3244: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3245: 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);
3246: END IF;
3247: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN

Line 3251: IF (l_party_site_contains_str IS NULL AND

3247: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3248: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3249: 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);
3250: END IF;
3251: IF (l_party_site_contains_str IS NULL AND
3252: l_contact_contains_str IS NULL AND
3253: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3254: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3255: RAISE L_RETURN_IMM_EXC;

Line 3252: l_contact_contains_str IS NULL AND

3248: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3249: 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);
3250: END IF;
3251: IF (l_party_site_contains_str IS NULL AND
3252: l_contact_contains_str IS NULL AND
3253: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3254: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3255: RAISE L_RETURN_IMM_EXC;
3256: ELSE

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

3249: 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);
3250: END IF;
3251: IF (l_party_site_contains_str IS NULL AND
3252: l_contact_contains_str IS NULL AND
3253: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3254: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3255: RAISE L_RETURN_IMM_EXC;
3256: ELSE
3257: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

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

3253: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3254: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3255: RAISE L_RETURN_IMM_EXC;
3256: ELSE
3257: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3258: END IF;
3259: ELSE
3260: IF p_index>1 THEN
3261: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

Line 3277: 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);

3273: l_ctx_id := NULL;
3274: l_party_id_idx := H_PARTY_ID.COUNT+1;
3275: END IF;
3276: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3277: 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);
3278: END IF;
3279: LOOP
3280: FETCH l_party_cur INTO
3281: l_party_id , l_TX2, l_TX8, l_TX4, l_TX44;

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

3295: l_party_id_idx:= l_party_id_idx+1;
3296: END IF;
3297: IF (l_party_id_idx-1)>l_max_thresh THEN
3298: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3299: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3300: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3301: AND l_contact_pt_contains_str IS NULL THEN
3302: H_PARTY_ID.DELETE(l_party_id_idx-1);
3303: H_PARTY_ID_LIST.DELETE(l_index);

Line 3301: AND l_contact_pt_contains_str IS NULL THEN

3297: IF (l_party_id_idx-1)>l_max_thresh THEN
3298: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3299: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3300: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3301: AND l_contact_pt_contains_str IS NULL THEN
3302: H_PARTY_ID.DELETE(l_party_id_idx-1);
3303: H_PARTY_ID_LIST.DELETE(l_index);
3304: H_SCORES.DELETE(l_index);
3305: EXIT;

Line 3330: p_contains_str VARCHAR2,

3326: CLOSE l_party_cur;
3327: ROLLBACK to eval_start;
3328: END;
3329: PROCEDURE open_person_contact_cursor(
3330: p_contains_str VARCHAR2,
3331: p_search_ctx_id NUMBER,
3332: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3333: BEGIN
3334: OPEN x_cursor FOR

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

3333: BEGIN
3334: OPEN x_cursor FOR
3335: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID
3336: FROM HZ_STAGED_CONTACTS stage
3337: WHERE contains( concat_col, p_contains_str)>0
3338: AND ORG_CONTACT_ID in (
3339: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3340: org_contact_id
3341: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc

Line 3361: PROCEDURE eval_party_site_level(p_party_site_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS

3357: end if;
3358: END;
3359:
3360: /** Private procedure to acquire and score at party site level ***/
3361: PROCEDURE eval_party_site_level(p_party_site_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3362: l_party_id_idx NUMBER:=1;
3363: l_ctx_id NUMBER;
3364: h_ps_id HZ_PARTY_SEARCH.IDList;
3365: h_ps_party_id HZ_PARTY_SEARCH.IDList;

Line 3389: 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);

3385: ELSE
3386: l_party_id_idx := H_PARTY_ID.COUNT+1;
3387: l_ctx_id := NULL;
3388: END IF;
3389: 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);
3390: LOOP
3391: FETCH l_party_site_cur INTO
3392: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;
3393: EXIT WHEN l_party_site_cur%NOTFOUND;

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

3456: END IF;
3457: END;
3458:
3459: /** Private procedure to acquire and score at party site level ***/
3460: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3461: l_party_id_idx NUMBER:=1;
3462: l_ctx_id NUMBER;
3463: h_ct_id HZ_PARTY_SEARCH.IDList;
3464: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

3472: FORALL I in 1..H_PARTY_ID.COUNT
3473: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3474: l_search_ctx_id,H_PARTY_ID(I));
3475: l_party_id_idx := H_PARTY_ID.COUNT+1;
3476: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3477: LOOP
3478: FETCH l_contact_cur INTO
3479: l_org_contact_id, l_ct_party_id ;
3480: EXIT WHEN l_contact_cur%NOTFOUND;

Line 3518: PROCEDURE eval_cpt_level(p_contact_pt_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER, p_ins_details VARCHAR2,p_emax_score NUMBER) IS

3514: END IF;
3515: END;
3516:
3517: /** Private procedure to acquire and score at contact point level ***/
3518: PROCEDURE eval_cpt_level(p_contact_pt_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER, p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3519: l_party_id_idx NUMBER:=1;
3520: l_ctx_id NUMBER;
3521: h_cpt_id HZ_PARTY_SEARCH.IDList;
3522: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

Line 3563: 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);

3559: END IF;
3560: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3561: 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);
3562: END IF;
3563: 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);
3564: LOOP
3565: FETCH l_contact_pt_cur INTO
3566: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;
3567: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 3671: IF l_party_contains_str IS NOT NULL THEN

3667: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3668: l_threshold := 100;
3669: other_acq_criteria_exists := TRUE ;
3670: --check if acquisition criteria exists for any other entity
3671: IF l_party_contains_str IS NOT NULL THEN
3672: acq_cnt := 1;
3673: END IF;
3674: IF l_party_site_contains_str IS NOT NULL THEN
3675: acq_cnt := acq_cnt+1;

Line 3674: IF l_party_site_contains_str IS NOT NULL THEN

3670: --check if acquisition criteria exists for any other entity
3671: IF l_party_contains_str IS NOT NULL THEN
3672: acq_cnt := 1;
3673: END IF;
3674: IF l_party_site_contains_str IS NOT NULL THEN
3675: acq_cnt := acq_cnt+1;
3676: END IF;
3677: IF l_contact_contains_str IS NOT NULL THEN
3678: acq_cnt := acq_cnt+1;

Line 3677: IF l_contact_contains_str IS NOT NULL THEN

3673: END IF;
3674: IF l_party_site_contains_str IS NOT NULL THEN
3675: acq_cnt := acq_cnt+1;
3676: END IF;
3677: IF l_contact_contains_str IS NOT NULL THEN
3678: acq_cnt := acq_cnt+1;
3679: END IF;
3680: IF l_contact_pt_contains_str IS NOT NULL THEN
3681: acq_cnt := acq_cnt+1;

Line 3680: IF l_contact_pt_contains_str IS NOT NULL THEN

3676: END IF;
3677: IF l_contact_contains_str IS NOT NULL THEN
3678: acq_cnt := acq_cnt+1;
3679: END IF;
3680: IF l_contact_pt_contains_str IS NOT NULL THEN
3681: acq_cnt := acq_cnt+1;
3682: END IF;
3683: IF acq_cnt>1 THEN
3684: other_acq_criteria_exists := TRUE;

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

3710: FND_MSG_PUB.ADD;
3711: RAISE FND_API.G_EXC_ERROR;
3712: END IF;
3713: END IF;
3714: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3715: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3716: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3717: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3718: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

3711: RAISE FND_API.G_EXC_ERROR;
3712: END IF;
3713: END IF;
3714: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3715: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3716: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3717: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3718: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3719: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

3712: END IF;
3713: END IF;
3714: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3715: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3716: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3717: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3718: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3719: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3720: END IF;

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

3713: END IF;
3714: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3715: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3716: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3717: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3718: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3719: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3720: END IF;
3721: END;

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

3714: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3715: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3716: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3717: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3718: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3719: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3720: END IF;
3721: END;
3722: /************ End of find_persons private procedures **********/

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

3715: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3716: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3717: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3718: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3719: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3720: END IF;
3721: END;
3722: /************ End of find_persons private procedures **********/
3723:

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

3750: g_score_until_thresh := false;
3751: END IF;
3752: -- ************************************************************
3753: -- STEP 2. Setup of intermedia query strings for Acquisition query
3754: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3755: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3756: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3757: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3758: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);

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

3751: END IF;
3752: -- ************************************************************
3753: -- STEP 2. Setup of intermedia query strings for Acquisition query
3754: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3755: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3756: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3757: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3758: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3759:

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

3752: -- ************************************************************
3753: -- STEP 2. Setup of intermedia query strings for Acquisition query
3754: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3755: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3756: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3757: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3758: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3759:
3760: -- Setup Search Context ID

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

3753: -- STEP 2. Setup of intermedia query strings for Acquisition query
3754: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3755: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3756: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3757: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3758: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3759:
3760: -- Setup Search Context ID
3761: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3764: IF l_party_contains_str IS NULL THEN

3760: -- Setup Search Context ID
3761: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3762: x_search_ctx_id := l_search_ctx_id;
3763:
3764: IF l_party_contains_str IS NULL THEN
3765: defpt := 1;
3766: END IF;
3767: IF l_party_site_contains_str IS NULL THEN
3768: defps := 1;

Line 3767: IF l_party_site_contains_str IS NULL THEN

3763:
3764: IF l_party_contains_str IS NULL THEN
3765: defpt := 1;
3766: END IF;
3767: IF l_party_site_contains_str IS NULL THEN
3768: defps := 1;
3769: END IF;
3770: IF l_contact_contains_str IS NULL THEN
3771: defct := 1;

Line 3770: IF l_contact_contains_str IS NULL THEN

3766: END IF;
3767: IF l_party_site_contains_str IS NULL THEN
3768: defps := 1;
3769: END IF;
3770: IF l_contact_contains_str IS NULL THEN
3771: defct := 1;
3772: END IF;
3773: IF l_contact_pt_contains_str IS NULL THEN
3774: defcpt := 1;

Line 3773: IF l_contact_pt_contains_str IS NULL THEN

3769: END IF;
3770: IF l_contact_contains_str IS NULL THEN
3771: defct := 1;
3772: END IF;
3773: IF l_contact_pt_contains_str IS NULL THEN
3774: defcpt := 1;
3775: END IF;
3776:
3777: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

Line 3779: 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);

3775: END IF;
3776:
3777: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3778: 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);
3779: 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);
3780: 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);
3781: 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);
3782: 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);
3783: 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 3780: 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);

3776:
3777: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3778: 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);
3779: 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);
3780: 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);
3781: 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);
3782: 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);
3783: 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);
3784: END IF;

Line 3781: 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);

3777: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3778: 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);
3779: 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);
3780: 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);
3781: 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);
3782: 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);
3783: 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);
3784: END IF;
3785: FOR I in 1..5 LOOP

Line 3782: 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);

3778: 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);
3779: 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);
3780: 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);
3781: 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);
3782: 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);
3783: 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);
3784: END IF;
3785: FOR I in 1..5 LOOP
3786: do_eval(I);

Line 3788: IF l_contact_contains_str IS NOT NULL THEN

3784: END IF;
3785: FOR I in 1..5 LOOP
3786: do_eval(I);
3787: END LOOP;
3788: IF l_contact_contains_str IS NOT NULL THEN
3789: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3790: END IF;
3791: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3792: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 28 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

3785: FOR I in 1..5 LOOP
3786: do_eval(I);
3787: END LOOP;
3788: IF l_contact_contains_str IS NOT NULL THEN
3789: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3790: END IF;
3791: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3792: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 28 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3793: END IF;

Line 4617: l_party_contains_str VARCHAR2(32000);

4613: x_num_matches OUT NUMBER
4614: ) IS
4615:
4616: -- Strings to hold the generated Intermedia query strings
4617: l_party_contains_str VARCHAR2(32000);
4618: l_match_idx NUMBER;
4619: l_party_site_contains_str VARCHAR2(32000);
4620: l_contact_contains_str VARCHAR2(32000);
4621: l_contact_pt_contains_str VARCHAR2(32000);

Line 4619: l_party_site_contains_str VARCHAR2(32000);

4615:
4616: -- Strings to hold the generated Intermedia query strings
4617: l_party_contains_str VARCHAR2(32000);
4618: l_match_idx NUMBER;
4619: l_party_site_contains_str VARCHAR2(32000);
4620: l_contact_contains_str VARCHAR2(32000);
4621: l_contact_pt_contains_str VARCHAR2(32000);
4622: l_tmp VARCHAR2(32000);
4623:

Line 4620: l_contact_contains_str VARCHAR2(32000);

4616: -- Strings to hold the generated Intermedia query strings
4617: l_party_contains_str VARCHAR2(32000);
4618: l_match_idx NUMBER;
4619: l_party_site_contains_str VARCHAR2(32000);
4620: l_contact_contains_str VARCHAR2(32000);
4621: l_contact_pt_contains_str VARCHAR2(32000);
4622: l_tmp VARCHAR2(32000);
4623:
4624: -- Other local variables

Line 4621: l_contact_pt_contains_str VARCHAR2(32000);

4617: l_party_contains_str VARCHAR2(32000);
4618: l_match_idx NUMBER;
4619: l_party_site_contains_str VARCHAR2(32000);
4620: l_contact_contains_str VARCHAR2(32000);
4621: l_contact_pt_contains_str VARCHAR2(32000);
4622: l_tmp VARCHAR2(32000);
4623:
4624: -- Other local variables
4625: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4696: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4697:
4698: -- ************************************************************
4699: -- STEP 2. Setup of intermedia query strings for Acquisition query
4700: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4701: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4702: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,p_party_site_list,HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,p_contact_point_list);
4703:
4704: -- Setup Search Context ID

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

4697:
4698: -- ************************************************************
4699: -- STEP 2. Setup of intermedia query strings for Acquisition query
4700: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4701: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4702: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,p_party_site_list,HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,p_contact_point_list);
4703:
4704: -- Setup Search Context ID
4705: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 4710: 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);

4706: x_search_ctx_id := l_search_ctx_id;
4707:
4708: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4709: 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);
4710: 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);
4711: 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);
4712: 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);
4713: END IF;
4714: IF l_party_site_contains_str IS NULL THEN

Line 4711: 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);

4707:
4708: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4709: 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);
4710: 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);
4711: 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);
4712: 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);
4713: END IF;
4714: IF l_party_site_contains_str IS NULL THEN
4715: defps := 1;

Line 4714: IF l_party_site_contains_str IS NULL THEN

4710: 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);
4711: 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);
4712: 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);
4713: END IF;
4714: IF l_party_site_contains_str IS NULL THEN
4715: defps := 1;
4716: END IF;
4717: IF l_contact_pt_contains_str IS NULL THEN
4718: defcpt := 1;

Line 4717: IF l_contact_pt_contains_str IS NULL THEN

4713: END IF;
4714: IF l_party_site_contains_str IS NULL THEN
4715: defps := 1;
4716: END IF;
4717: IF l_contact_pt_contains_str IS NULL THEN
4718: defcpt := 1;
4719: END IF;
4720:
4721: IF l_party_site_contains_str IS NOT NULL THEN

Line 4721: IF l_party_site_contains_str IS NOT NULL THEN

4717: IF l_contact_pt_contains_str IS NULL THEN
4718: defcpt := 1;
4719: END IF;
4720:
4721: IF l_party_site_contains_str IS NOT NULL THEN
4722: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4723: LOOP
4724: FETCH l_party_site_cur INTO
4725: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;

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

4718: defcpt := 1;
4719: END IF;
4720:
4721: IF l_party_site_contains_str IS NOT NULL THEN
4722: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4723: LOOP
4724: FETCH l_party_site_cur INTO
4725: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;
4726: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 4745: IF l_contact_pt_contains_str IS NOT NULL THEN

4741: END LOOP;
4742: CLOSE l_party_site_cur;
4743: END IF;
4744:
4745: IF l_contact_pt_contains_str IS NOT NULL THEN
4746: SAVEPOINT eval_start;
4747: IF l_match_str = ' AND ' OR (
4748: ((l_cpt_max_score/l_entered_max_score)<(100/360))
4749: ) THEN

Line 4753: 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');

4749: ) THEN
4750: FORALL I in 1..l_match_ps_list.COUNT
4751: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4752: values (l_search_ctx_id,l_match_ps_list(I));
4753: 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');
4754: ELSE
4755: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4756: END IF;
4757: LOOP

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

4751: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4752: values (l_search_ctx_id,l_match_ps_list(I));
4753: 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');
4754: ELSE
4755: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4756: END IF;
4757: LOOP
4758: FETCH l_contact_pt_cur INTO
4759: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;

Line 4855: l_party_contains_str VARCHAR2(32000);

4851: x_num_matches OUT NUMBER
4852: ) IS
4853:
4854: -- Strings to hold the generated Intermedia query strings
4855: l_party_contains_str VARCHAR2(32000);
4856: l_party_site_contains_str VARCHAR2(32000);
4857: l_contact_contains_str VARCHAR2(32000);
4858: l_contact_pt_contains_str VARCHAR2(32000);
4859: l_tmp VARCHAR2(32000);

Line 4856: l_party_site_contains_str VARCHAR2(32000);

4852: ) IS
4853:
4854: -- Strings to hold the generated Intermedia query strings
4855: l_party_contains_str VARCHAR2(32000);
4856: l_party_site_contains_str VARCHAR2(32000);
4857: l_contact_contains_str VARCHAR2(32000);
4858: l_contact_pt_contains_str VARCHAR2(32000);
4859: l_tmp VARCHAR2(32000);
4860:

Line 4857: l_contact_contains_str VARCHAR2(32000);

4853:
4854: -- Strings to hold the generated Intermedia query strings
4855: l_party_contains_str VARCHAR2(32000);
4856: l_party_site_contains_str VARCHAR2(32000);
4857: l_contact_contains_str VARCHAR2(32000);
4858: l_contact_pt_contains_str VARCHAR2(32000);
4859: l_tmp VARCHAR2(32000);
4860:
4861: -- Other local variables

Line 4858: l_contact_pt_contains_str VARCHAR2(32000);

4854: -- Strings to hold the generated Intermedia query strings
4855: l_party_contains_str VARCHAR2(32000);
4856: l_party_site_contains_str VARCHAR2(32000);
4857: l_contact_contains_str VARCHAR2(32000);
4858: l_contact_pt_contains_str VARCHAR2(32000);
4859: l_tmp VARCHAR2(32000);
4860:
4861: -- Other local variables
4862: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4928: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4929:
4930: -- ************************************************************
4931: -- STEP 2. Setup of intermedia query strings for Acquisition query
4932: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4933: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4934: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,p_contact_list,p_contact_point_list);
4935:
4936: -- Setup Search Context ID

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

4929:
4930: -- ************************************************************
4931: -- STEP 2. Setup of intermedia query strings for Acquisition query
4932: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
4933: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4934: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,p_contact_list,p_contact_point_list);
4935:
4936: -- Setup Search Context ID
4937: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 4942: 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);

4938: x_search_ctx_id := l_search_ctx_id;
4939:
4940: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4941: 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);
4942: 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);
4943: 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);
4944: 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);
4945: END IF;
4946: IF l_contact_contains_str IS NULL THEN

Line 4943: 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);

4939:
4940: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4941: 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);
4942: 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);
4943: 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);
4944: 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);
4945: END IF;
4946: IF l_contact_contains_str IS NULL THEN
4947: defct := 1;

Line 4946: IF l_contact_contains_str IS NULL THEN

4942: 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);
4943: 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);
4944: 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);
4945: END IF;
4946: IF l_contact_contains_str IS NULL THEN
4947: defct := 1;
4948: END IF;
4949: IF l_contact_pt_contains_str IS NULL THEN
4950: defcpt := 1;

Line 4949: IF l_contact_pt_contains_str IS NULL THEN

4945: END IF;
4946: IF l_contact_contains_str IS NULL THEN
4947: defct := 1;
4948: END IF;
4949: IF l_contact_pt_contains_str IS NULL THEN
4950: defcpt := 1;
4951: END IF;
4952:
4953: IF l_contact_contains_str IS NOT NULL THEN

Line 4953: IF l_contact_contains_str IS NOT NULL THEN

4949: IF l_contact_pt_contains_str IS NULL THEN
4950: defcpt := 1;
4951: END IF;
4952:
4953: IF l_contact_contains_str IS NOT NULL THEN
4954: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4955: LOOP
4956: FETCH l_contact_cur INTO
4957: l_org_contact_id, l_ct_party_id ;

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

4950: defcpt := 1;
4951: END IF;
4952:
4953: IF l_contact_contains_str IS NOT NULL THEN
4954: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
4955: LOOP
4956: FETCH l_contact_cur INTO
4957: l_org_contact_id, l_ct_party_id ;
4958: EXIT WHEN l_contact_cur%NOTFOUND;

Line 4975: IF l_contact_pt_contains_str IS NOT NULL THEN

4971: END LOOP;
4972: CLOSE l_contact_cur;
4973: END IF;
4974:
4975: IF l_contact_pt_contains_str IS NOT NULL THEN
4976: SAVEPOINT eval_start;
4977: IF l_match_str = ' AND ' OR (
4978: ((l_cpt_max_score/l_entered_max_score)<(100/360))
4979: ) THEN

Line 4983: 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');

4979: ) THEN
4980: FORALL I in 1..l_match_contact_list.COUNT
4981: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4982: values (l_search_ctx_id,l_match_contact_list(I));
4983: 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');
4984: ELSE
4985: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4986: END IF;
4987: LOOP

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

4981: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4982: values (l_search_ctx_id,l_match_contact_list(I));
4983: 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');
4984: ELSE
4985: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4986: END IF;
4987: LOOP
4988: FETCH l_contact_pt_cur INTO
4989: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;

Line 5083: l_contact_pt_contains_str VARCHAR2(32000);

5079: ) IS
5080:
5081:
5082: -- Strings to hold the generated Intermedia query strings
5083: l_contact_pt_contains_str VARCHAR2(32000);
5084: l_tmp VARCHAR2(32000);
5085:
5086: -- Other local variables
5087: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5140: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5141:
5142: -- ************************************************************
5143: -- STEP 2. Setup of intermedia query strings for Acquisition query
5144: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5145: init_score_context(HZ_PARTY_SEARCH.G_MISS_PARTY_SEARCH_REC,HZ_PARTY_SEARCH.G_MISS_PARTY_SITE_LIST,HZ_PARTY_SEARCH.G_MISS_CONTACT_LIST,p_contact_point_list);
5146:
5147: -- Setup Search Context ID
5148: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 5152: 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);

5148: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5149: x_search_ctx_id := l_search_ctx_id;
5150: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5151: 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);
5152: 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);
5153: 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);
5154: END IF;
5155:
5156: IF l_contact_pt_contains_str IS NOT NULL THEN

Line 5156: IF l_contact_pt_contains_str IS NOT NULL THEN

5152: 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);
5153: 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);
5154: END IF;
5155:
5156: IF l_contact_pt_contains_str IS NOT NULL THEN
5157: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5158: cnt := 1;
5159: LOOP
5160: FETCH l_contact_pt_cur INTO

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

5153: 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);
5154: END IF;
5155:
5156: IF l_contact_pt_contains_str IS NOT NULL THEN
5157: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5158: cnt := 1;
5159: LOOP
5160: FETCH l_contact_pt_cur INTO
5161: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;

Line 5228: l_party_contains_str VARCHAR2(32000);

5224: x_search_ctx_id IN OUT NUMBER
5225: ) IS
5226:
5227: -- Strings to hold the generated Intermedia query strings
5228: l_party_contains_str VARCHAR2(32000);
5229: l_party_site_contains_str VARCHAR2(32000);
5230: l_contact_contains_str VARCHAR2(32000);
5231: l_contact_pt_contains_str VARCHAR2(32000);
5232: l_tmp VARCHAR2(32000);

Line 5229: l_party_site_contains_str VARCHAR2(32000);

5225: ) IS
5226:
5227: -- Strings to hold the generated Intermedia query strings
5228: l_party_contains_str VARCHAR2(32000);
5229: l_party_site_contains_str VARCHAR2(32000);
5230: l_contact_contains_str VARCHAR2(32000);
5231: l_contact_pt_contains_str VARCHAR2(32000);
5232: l_tmp VARCHAR2(32000);
5233:

Line 5230: l_contact_contains_str VARCHAR2(32000);

5226:
5227: -- Strings to hold the generated Intermedia query strings
5228: l_party_contains_str VARCHAR2(32000);
5229: l_party_site_contains_str VARCHAR2(32000);
5230: l_contact_contains_str VARCHAR2(32000);
5231: l_contact_pt_contains_str VARCHAR2(32000);
5232: l_tmp VARCHAR2(32000);
5233:
5234: -- Other local variables

Line 5231: l_contact_pt_contains_str VARCHAR2(32000);

5227: -- Strings to hold the generated Intermedia query strings
5228: l_party_contains_str VARCHAR2(32000);
5229: l_party_site_contains_str VARCHAR2(32000);
5230: l_contact_contains_str VARCHAR2(32000);
5231: l_contact_pt_contains_str VARCHAR2(32000);
5232: l_tmp VARCHAR2(32000);
5233:
5234: -- Other local variables
5235: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5303: g_score_until_thresh := false;
5304: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5305: -- ************************************************************
5306: -- STEP 2. Setup of intermedia query strings for Acquisition query
5307: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5308: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5309: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5310: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5311:

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

5304: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5305: -- ************************************************************
5306: -- STEP 2. Setup of intermedia query strings for Acquisition query
5307: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5308: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5309: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5310: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5311:
5312: -- Setup Search Context ID

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

5305: -- ************************************************************
5306: -- STEP 2. Setup of intermedia query strings for Acquisition query
5307: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5308: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5309: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5310: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5311:
5312: -- Setup Search Context ID
5313: IF x_search_ctx_id IS NULL THEN

Line 5329: IF l_party_site_contains_str IS NOT NULL THEN

5325: 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,l_TX44,1);
5326: END LOOP;
5327: CLOSE l_party_cur;
5328:
5329: IF l_party_site_contains_str IS NOT NULL THEN
5330: l_max_score := 0;
5331: l_max_id := 0;
5332: l_max_idx := 0;
5333: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

5330: l_max_score := 0;
5331: l_max_id := 0;
5332: l_max_idx := 0;
5333: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5334: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5335: ELSE
5336: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5337: END IF;
5338: LOOP

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

5332: l_max_idx := 0;
5333: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5334: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5335: ELSE
5336: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5337: END IF;
5338: LOOP
5339: FETCH l_party_site_cur INTO
5340: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX3, l_TX4, l_TX11;

Line 5358: IF l_contact_contains_str IS NOT NULL THEN

5354: 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_TX3,l_max_TX4,l_max_TX11,l_max_idx);
5355: END IF;
5356: END IF;
5357:
5358: IF l_contact_contains_str IS NOT NULL THEN
5359: l_max_score := 0;
5360: l_max_id := 0;
5361: l_max_idx := 0;
5362: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5358: IF l_contact_contains_str IS NOT NULL THEN
5359: l_max_score := 0;
5360: l_max_id := 0;
5361: l_max_idx := 0;
5362: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5363: LOOP
5364: FETCH l_contact_cur INTO
5365: l_org_contact_id, l_ct_party_id ;
5366: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5380: IF l_contact_pt_contains_str IS NOT NULL THEN

5376: 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);
5377: END IF;
5378: END IF;
5379:
5380: IF l_contact_pt_contains_str IS NOT NULL THEN
5381: l_max_score := 0;
5382: l_max_id := 0;
5383: l_max_idx := 0;
5384: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5381: l_max_score := 0;
5382: l_max_id := 0;
5383: l_max_idx := 0;
5384: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5385: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5386: ELSE
5387: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5388: END IF;
5389: LOOP

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

5383: l_max_idx := 0;
5384: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5385: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5386: ELSE
5387: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5388: END IF;
5389: LOOP
5390: FETCH l_contact_pt_cur INTO
5391: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1, l_TX6, l_TX5;