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_6 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 1090: l_party_contains_str VARCHAR2(32000);

1086: p_party_max_score NUMBER,
1087: p_denorm_max_score NUMBER,
1088: p_non_denorm_max_score NUMBER,
1089: p_threshold NUMBER) RETURN VARCHAR2 IS
1090: l_party_contains_str VARCHAR2(32000);
1091: l_party_filter VARCHAR2(1) := null;
1092: l_prim_temp VARCHAR2(4000);
1093: l_denorm_str VARCHAR2(4000);
1094: BEGIN

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

1130:
1131: IF l_denorm_str IS NOT NULL THEN
1132: IF (p_non_denorm_max_score+50)>=p_threshold THEN
1133: l_denorm_str := NULL;
1134: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1135: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
1136: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1137: l_denorm_str := NULL;
1138: END IF;

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

1132: IF (p_non_denorm_max_score+50)>=p_threshold THEN
1133: l_denorm_str := NULL;
1134: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1135: ELSIF (p_non_denorm_max_score+p_denorm_max_score+50)>=p_threshold THEN
1136: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1137: l_denorm_str := NULL;
1138: END IF;
1139: ELSE
1140: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1136: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1137: l_denorm_str := NULL;
1138: END IF;
1139: ELSE
1140: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1141: END IF;
1142:
1143: -- Setup query string for TAX_REFERENCE
1144: l_prim_temp := null;

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

1147:
1148: IF l_denorm_str IS NOT NULL THEN
1149: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1150: l_denorm_str := NULL;
1151: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1152: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1153: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1154: l_denorm_str := NULL;
1155: END IF;

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

1149: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1150: l_denorm_str := NULL;
1151: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1152: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1153: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1154: l_denorm_str := NULL;
1155: END IF;
1156: ELSE
1157: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1153: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1154: l_denorm_str := NULL;
1155: END IF;
1156: ELSE
1157: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1158: END IF;
1159:
1160: -- Setup query string for DUNS_NUMBER_C
1161: l_prim_temp := null;

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

1170:
1171: IF l_denorm_str IS NOT NULL THEN
1172: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1173: l_denorm_str := NULL;
1174: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1175: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1176: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1177: l_denorm_str := NULL;
1178: END IF;

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

1172: IF (p_non_denorm_max_score+100)>=p_threshold THEN
1173: l_denorm_str := NULL;
1174: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1175: ELSIF (p_non_denorm_max_score+p_denorm_max_score+100)>=p_threshold THEN
1176: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1177: l_denorm_str := NULL;
1178: END IF;
1179: ELSE
1180: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);

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

1176: HZ_DQM_SEARCH_UTIL.add_attribute_with_denorm(l_prim_temp, p_match_str, l_denorm_str, l_party_contains_str);
1177: l_denorm_str := NULL;
1178: END IF;
1179: ELSE
1180: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1181: END IF;
1182: IF lengthb(l_party_contains_str) > 4000 THEN
1183: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1184: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');

Line 1182: IF lengthb(l_party_contains_str) > 4000 THEN

1178: END IF;
1179: ELSE
1180: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, p_match_str, l_party_contains_str);
1181: END IF;
1182: IF lengthb(l_party_contains_str) > 4000 THEN
1183: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1184: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1185: FND_MSG_PUB.ADD;
1186: RAISE FND_API.G_EXC_ERROR;

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

1184: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY');
1185: FND_MSG_PUB.ADD;
1186: RAISE FND_API.G_EXC_ERROR;
1187: END IF;
1188: IF (p_match_str = ' AND ' OR p_party_max_score 1189: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1190: 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);
1191: END IF;
1192: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

1186: RAISE FND_API.G_EXC_ERROR;
1187: END IF;
1188: IF (p_match_str = ' AND ' OR p_party_max_score 1189: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1190: 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);
1191: END IF;
1192: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1193: 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);
1194: END IF;

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

1189: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1190: 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);
1191: END IF;
1192: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1193: 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);
1194: END IF;
1195: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1196: 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);
1197: END IF;

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

1194: END IF;
1195: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1196: 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);
1197: END IF;
1198: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1199: ELSE
1200: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1201: 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);
1202: 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 1201: 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);

1197: END IF;
1198: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1199: ELSE
1200: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1201: 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);
1202: 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);
1203: END IF;
1204: RETURN l_party_contains_str;
1205: END IF;

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

1198: RETURN '('||l_party_contains_str||') AND ('||p_denorm_str||')';
1199: ELSE
1200: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1201: 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);
1202: 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);
1203: END IF;
1204: RETURN l_party_contains_str;
1205: END IF;
1206: END;

Line 1204: RETURN l_party_contains_str;

1200: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1201: 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);
1202: 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);
1203: END IF;
1204: RETURN l_party_contains_str;
1205: END IF;
1206: END;
1207: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1208: l_contains_str VARCHAR2(32000);

Line 1208: l_contains_str VARCHAR2(32000);

1204: RETURN l_party_contains_str;
1205: END IF;
1206: END;
1207: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1208: l_contains_str VARCHAR2(32000);
1209: l_contains_str_temp VARCHAR2(32000);
1210: l_den_contains_str VARCHAR2(32000);
1211: l_den_contains_str_temp VARCHAR2(32000);
1212: l_filter_str VARCHAR2(4000) := null;

Line 1209: l_contains_str_temp VARCHAR2(32000);

1205: END IF;
1206: END;
1207: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1208: l_contains_str VARCHAR2(32000);
1209: l_contains_str_temp VARCHAR2(32000);
1210: l_den_contains_str VARCHAR2(32000);
1211: l_den_contains_str_temp VARCHAR2(32000);
1212: l_filter_str VARCHAR2(4000) := null;
1213: l_prim_temp VARCHAR2(4000) := null;

Line 1210: l_den_contains_str VARCHAR2(32000);

1206: END;
1207: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1208: l_contains_str VARCHAR2(32000);
1209: l_contains_str_temp VARCHAR2(32000);
1210: l_den_contains_str VARCHAR2(32000);
1211: l_den_contains_str_temp VARCHAR2(32000);
1212: l_filter_str VARCHAR2(4000) := null;
1213: l_prim_temp VARCHAR2(4000) := null;
1214: l_prim_temp_den VARCHAR2(4000) := null;

Line 1211: l_den_contains_str_temp VARCHAR2(32000);

1207: FUNCTION INIT_PARTY_SITES_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1208: l_contains_str VARCHAR2(32000);
1209: l_contains_str_temp VARCHAR2(32000);
1210: l_den_contains_str VARCHAR2(32000);
1211: l_den_contains_str_temp VARCHAR2(32000);
1212: l_filter_str VARCHAR2(4000) := null;
1213: l_prim_temp VARCHAR2(4000) := null;
1214: l_prim_temp_den VARCHAR2(4000) := null;
1215: BEGIN

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

1217: 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);
1218: END IF;
1219: x_denorm_str := NULL;
1220:
1221: -- Dynamic setup of party site contains str
1222: --
1223: -- For each primary transformation add to intermedia query if it
1224: -- is not null
1225: FOR I IN 1..g_party_site_stage_list.COUNT LOOP

Line 1226: l_contains_str_temp := null;

1222: --
1223: -- For each primary transformation add to intermedia query if it
1224: -- is not null
1225: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1226: l_contains_str_temp := null;
1227: l_den_contains_str_temp := null;
1228: l_filter_str := null;
1229:
1230: -- Setup query string for ADDRESS

Line 1227: l_den_contains_str_temp := null;

1223: -- For each primary transformation add to intermedia query if it
1224: -- is not null
1225: FOR I IN 1..g_party_site_stage_list.COUNT LOOP
1226: l_contains_str_temp := null;
1227: l_den_contains_str_temp := null;
1228: l_filter_str := null;
1229:
1230: -- Setup query string for ADDRESS
1231: l_prim_temp := null;

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

1231: l_prim_temp := null;
1232: l_prim_temp_den := null;
1233: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRADDRESS
1234: g_party_site_stage_list(I).TX4,'A27',l_prim_temp);
1235: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1236:
1237: -- Setup query string for POSTAL_CODE
1238: l_prim_temp := null;
1239: l_prim_temp_den := null;

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

1240: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1241: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1242: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1243: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1244: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1245: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1246:
1247: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1248: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);

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

1241: g_party_site_stage_list(I).TX11,'A30',l_prim_temp);
1242: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL NOSPC
1243: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1244: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1245: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1246:
1247: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1248: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1249: END LOOP;

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

1243: g_party_site_stage_list(I).TX11,'D_PS',l_prim_temp_den);
1244: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1245: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1246:
1247: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1248: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1249: END LOOP;
1250: -- Add the search criteria to query string
1251: IF lengthb(l_contains_str) > 4000 THEN

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

1244: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp_den, ' AND ', l_den_contains_str_temp);
1245: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1246:
1247: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1248: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1249: END LOOP;
1250: -- Add the search criteria to query string
1251: IF lengthb(l_contains_str) > 4000 THEN
1252: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 1251: IF lengthb(l_contains_str) > 4000 THEN

1247: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1248: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1249: END LOOP;
1250: -- Add the search criteria to query string
1251: IF lengthb(l_contains_str) > 4000 THEN
1252: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1253: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1254: FND_MSG_PUB.ADD;
1255: RAISE FND_API.G_EXC_ERROR;

Line 1257: x_denorm_str := l_den_contains_str;

1253: FND_MESSAGE.SET_TOKEN('ENTITY','PARTY_SITES');
1254: FND_MSG_PUB.ADD;
1255: RAISE FND_API.G_EXC_ERROR;
1256: END IF;
1257: x_denorm_str := l_den_contains_str;
1258: RETURN l_contains_str;
1259: END;
1260:
1261: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS

Line 1258: RETURN l_contains_str;

1254: FND_MSG_PUB.ADD;
1255: RAISE FND_API.G_EXC_ERROR;
1256: END IF;
1257: x_denorm_str := l_den_contains_str;
1258: RETURN l_contains_str;
1259: END;
1260:
1261: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1262: l_contains_str VARCHAR2(32000);

Line 1262: l_contains_str VARCHAR2(32000);

1258: RETURN l_contains_str;
1259: END;
1260:
1261: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1262: l_contains_str VARCHAR2(32000);
1263: l_contains_str_temp VARCHAR2(32000);
1264: l_den_contains_str VARCHAR2(32000);
1265: l_den_contains_str_temp VARCHAR2(32000);
1266: l_filter_str VARCHAR2(4000) := null;

Line 1263: l_contains_str_temp VARCHAR2(32000);

1259: END;
1260:
1261: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1262: l_contains_str VARCHAR2(32000);
1263: l_contains_str_temp VARCHAR2(32000);
1264: l_den_contains_str VARCHAR2(32000);
1265: l_den_contains_str_temp VARCHAR2(32000);
1266: l_filter_str VARCHAR2(4000) := null;
1267: l_prim_temp VARCHAR2(4000) := null;

Line 1264: l_den_contains_str VARCHAR2(32000);

1260:
1261: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1262: l_contains_str VARCHAR2(32000);
1263: l_contains_str_temp VARCHAR2(32000);
1264: l_den_contains_str VARCHAR2(32000);
1265: l_den_contains_str_temp VARCHAR2(32000);
1266: l_filter_str VARCHAR2(4000) := null;
1267: l_prim_temp VARCHAR2(4000) := null;
1268: l_prim_temp_den VARCHAR2(4000) := null;

Line 1265: l_den_contains_str_temp VARCHAR2(32000);

1261: FUNCTION INIT_CONTACTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1262: l_contains_str VARCHAR2(32000);
1263: l_contains_str_temp VARCHAR2(32000);
1264: l_den_contains_str VARCHAR2(32000);
1265: l_den_contains_str_temp VARCHAR2(32000);
1266: l_filter_str VARCHAR2(4000) := null;
1267: l_prim_temp VARCHAR2(4000) := null;
1268: l_prim_temp_den VARCHAR2(4000) := null;
1269: BEGIN

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

1271: hz_utility_v2pub.debug(p_message=>'INIT_CONTACTS_QUERY ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_procedure);
1272: END IF;
1273: x_denorm_str := NULL;
1274:
1275: -- Dynamic setup of party site contains str
1276: --
1277: -- For each primary transformation add to intermedia query if it
1278: -- is not null
1279: FOR I IN 1..g_contact_stage_list.COUNT LOOP

Line 1280: l_contains_str_temp := null;

1276: --
1277: -- For each primary transformation add to intermedia query if it
1278: -- is not null
1279: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1280: l_contains_str_temp := null;
1281: l_den_contains_str_temp := null;
1282: l_filter_str := null;
1283:
1284: -- Setup query string for CONTACT_NAME

Line 1281: l_den_contains_str_temp := null;

1277: -- For each primary transformation add to intermedia query if it
1278: -- is not null
1279: FOR I IN 1..g_contact_stage_list.COUNT LOOP
1280: l_contains_str_temp := null;
1281: l_den_contains_str_temp := null;
1282: l_filter_str := null;
1283:
1284: -- Setup query string for CONTACT_NAME
1285: l_prim_temp := null;

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

1285: l_prim_temp := null;
1286: l_prim_temp_den := null;
1287: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRPERSON
1288: g_contact_stage_list(I).TX6,'A39',l_prim_temp);
1289: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1290:
1291: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1292: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1293: END LOOP;

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

1287: HZ_DQM_SEARCH_UTIL.add_transformation( -- CLEANSE SPECIAL + WRPERSON
1288: g_contact_stage_list(I).TX6,'A39',l_prim_temp);
1289: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1290:
1291: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1292: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1293: END LOOP;
1294: -- Add the search criteria to query string
1295: IF lengthb(l_contains_str) > 4000 THEN

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

1288: g_contact_stage_list(I).TX6,'A39',l_prim_temp);
1289: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1290:
1291: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1292: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1293: END LOOP;
1294: -- Add the search criteria to query string
1295: IF lengthb(l_contains_str) > 4000 THEN
1296: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 1295: IF lengthb(l_contains_str) > 4000 THEN

1291: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1292: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1293: END LOOP;
1294: -- Add the search criteria to query string
1295: IF lengthb(l_contains_str) > 4000 THEN
1296: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1297: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
1298: FND_MSG_PUB.ADD;
1299: RAISE FND_API.G_EXC_ERROR;

Line 1301: x_denorm_str := l_den_contains_str;

1297: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACTS');
1298: FND_MSG_PUB.ADD;
1299: RAISE FND_API.G_EXC_ERROR;
1300: END IF;
1301: x_denorm_str := l_den_contains_str;
1302: RETURN l_contains_str;
1303: END;
1304:
1305: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS

Line 1302: RETURN l_contains_str;

1298: FND_MSG_PUB.ADD;
1299: RAISE FND_API.G_EXC_ERROR;
1300: END IF;
1301: x_denorm_str := l_den_contains_str;
1302: RETURN l_contains_str;
1303: END;
1304:
1305: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1306: l_contains_str VARCHAR2(32000);

Line 1306: l_contains_str VARCHAR2(32000);

1302: RETURN l_contains_str;
1303: END;
1304:
1305: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1306: l_contains_str VARCHAR2(32000);
1307: l_contains_str_temp VARCHAR2(32000);
1308: l_den_contains_str VARCHAR2(32000);
1309: l_den_contains_str_temp VARCHAR2(32000);
1310: l_filter_str VARCHAR2(4000) := null;

Line 1307: l_contains_str_temp VARCHAR2(32000);

1303: END;
1304:
1305: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1306: l_contains_str VARCHAR2(32000);
1307: l_contains_str_temp VARCHAR2(32000);
1308: l_den_contains_str VARCHAR2(32000);
1309: l_den_contains_str_temp VARCHAR2(32000);
1310: l_filter_str VARCHAR2(4000) := null;
1311: l_prim_temp VARCHAR2(4000) := null;

Line 1308: l_den_contains_str VARCHAR2(32000);

1304:
1305: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1306: l_contains_str VARCHAR2(32000);
1307: l_contains_str_temp VARCHAR2(32000);
1308: l_den_contains_str VARCHAR2(32000);
1309: l_den_contains_str_temp VARCHAR2(32000);
1310: l_filter_str VARCHAR2(4000) := null;
1311: l_prim_temp VARCHAR2(4000) := null;
1312: l_prim_temp_den VARCHAR2(4000) := null;

Line 1309: l_den_contains_str_temp VARCHAR2(32000);

1305: FUNCTION INIT_CONTACT_POINTS_QUERY(p_match_str VARCHAR2, x_denorm_str OUT VARCHAR2) RETURN VARCHAR2 IS
1306: l_contains_str VARCHAR2(32000);
1307: l_contains_str_temp VARCHAR2(32000);
1308: l_den_contains_str VARCHAR2(32000);
1309: l_den_contains_str_temp VARCHAR2(32000);
1310: l_filter_str VARCHAR2(4000) := null;
1311: l_prim_temp VARCHAR2(4000) := null;
1312: l_prim_temp_den VARCHAR2(4000) := null;
1313: BEGIN

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

1315: 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);
1316: END IF;
1317: x_denorm_str := NULL;
1318:
1319: -- Dynamic setup of party site contains str
1320: --
1321: -- For each primary transformation add to intermedia query if it
1322: -- is not null
1323: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP

Line 1324: l_contains_str_temp := null;

1320: --
1321: -- For each primary transformation add to intermedia query if it
1322: -- is not null
1323: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1324: l_contains_str_temp := null;
1325: l_den_contains_str_temp := null;
1326: l_filter_str := null;
1327: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1328:

Line 1325: l_den_contains_str_temp := null;

1321: -- For each primary transformation add to intermedia query if it
1322: -- is not null
1323: FOR I IN 1..g_contact_pt_stage_list.COUNT LOOP
1324: l_contains_str_temp := null;
1325: l_den_contains_str_temp := null;
1326: l_filter_str := null;
1327: l_filter_str := '('||g_contact_pt_stage_list(I).CONTACT_POINT_TYPE||') ';
1328:
1329: -- Setup query string for FLEX_FORMAT_PHONE_NUMBER

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

1330: l_prim_temp := null;
1331: l_prim_temp_den := null;
1332: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL CTX
1333: g_contact_pt_stage_list(I).TX1,'A44',l_prim_temp);
1334: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1335:
1336: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1337: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1338: END LOOP;

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

1332: HZ_DQM_SEARCH_UTIL.add_transformation( -- EXACT SPECIAL CTX
1333: g_contact_pt_stage_list(I).TX1,'A44',l_prim_temp);
1334: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1335:
1336: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1337: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1338: END LOOP;
1339: -- Add the search criteria to query string
1340: IF lengthb(l_contains_str) > 4000 THEN

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

1333: g_contact_pt_stage_list(I).TX1,'A44',l_prim_temp);
1334: HZ_DQM_SEARCH_UTIL.add_attribute(l_prim_temp, ' AND ', l_contains_str_temp);
1335:
1336: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1337: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1338: END LOOP;
1339: -- Add the search criteria to query string
1340: IF lengthb(l_contains_str) > 4000 THEN
1341: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');

Line 1340: IF lengthb(l_contains_str) > 4000 THEN

1336: HZ_DQM_SEARCH_UTIL.add_search_record(l_contains_str_temp, l_filter_str, l_contains_str);
1337: HZ_DQM_SEARCH_UTIL.add_search_record(l_den_contains_str_temp, null, l_den_contains_str);
1338: END LOOP;
1339: -- Add the search criteria to query string
1340: IF lengthb(l_contains_str) > 4000 THEN
1341: FND_MESSAGE.SET_NAME('AR', 'HZ_SEARCH_CRIT_LARGE_ERROR');
1342: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1343: FND_MSG_PUB.ADD;
1344: RAISE FND_API.G_EXC_ERROR;

Line 1346: x_denorm_str := l_den_contains_str;

1342: FND_MESSAGE.SET_TOKEN('ENTITY','CONTACT_POINTS');
1343: FND_MSG_PUB.ADD;
1344: RAISE FND_API.G_EXC_ERROR;
1345: END IF;
1346: x_denorm_str := l_den_contains_str;
1347: RETURN l_contains_str;
1348: END;
1349:
1350: PROCEDURE open_party_cursor_direct (

Line 1347: RETURN l_contains_str;

1343: FND_MSG_PUB.ADD;
1344: RAISE FND_API.G_EXC_ERROR;
1345: END IF;
1346: x_denorm_str := l_den_contains_str;
1347: RETURN l_contains_str;
1348: END;
1349:
1350: PROCEDURE open_party_cursor_direct (
1351: p_dup_party_id NUMBER,

Line 1355: p_party_contains_str VARCHAR2,

1351: p_dup_party_id NUMBER,
1352: p_restrict_sql VARCHAR2,
1353: p_match_str VARCHAR2,
1354: p_search_merged VARCHAR2,
1355: p_party_contains_str VARCHAR2,
1356: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1357: l_sqlstr VARCHAR2(4000);
1358: l_search_merged VARCHAR2(1);
1359: BEGIN

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

1368: END IF;
1369: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1370: 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);
1371: END IF;
1372: 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
1373: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1374: 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);
1375: END IF;
1376: OPEN x_cursor FOR

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

1403: l_sqlstr := l_sqlstr || ' (TX44 like :TX44||'' %'' ';
1404: l_sqlstr := l_sqlstr || ' )) ';
1405: l_sqlstr := l_sqlstr || ' ) ';
1406: END IF;
1407: 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
1408: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1409: 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);
1410: END IF;
1411: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';

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

1405: l_sqlstr := l_sqlstr || ' ) ';
1406: END IF;
1407: 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
1408: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1409: 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);
1410: END IF;
1411: l_sqlstr := l_sqlstr || ' AND (:attr IS NULL OR ';
1412: l_sqlstr := l_sqlstr || ' (D_PS like ''% ''||:TX11||'' %'' ';
1413: l_sqlstr := l_sqlstr || ' )) ';

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

1426: END IF;
1427: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1428: 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);
1429: END IF;
1430: 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
1431: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1432: 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);
1433: END IF;
1434: 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 1432: 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);

1428: 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);
1429: END IF;
1430: 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
1431: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1432: 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);
1433: END IF;
1434: 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
1435: ,g_party_stage_rec.TX41
1436: ,g_party_stage_rec.TX41

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

1448: ,g_party_stage_rec.TX41
1449: ,g_party_stage_rec.TX44
1450: ,g_party_stage_rec.TX44
1451: ;
1452: 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
1453: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1454: 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);
1455: END IF;
1456: 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 1454: 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);

1450: ,g_party_stage_rec.TX44
1451: ;
1452: 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
1453: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1454: 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);
1455: END IF;
1456: 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
1457: ,g_party_site_stage_list(1).TX11
1458: ,g_party_site_stage_list(1).TX11

Line 1471: p_contains_str VARCHAR2,

1467: END;
1468: PROCEDURE open_party_cursor(
1469: p_dup_party_id NUMBER,
1470: p_restrict_sql VARCHAR2,
1471: p_contains_str VARCHAR2,
1472: p_search_ctx_id NUMBER,
1473: p_match_str VARCHAR2,
1474: p_search_merged VARCHAR2,
1475: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS

Line 1490: IF p_contains_str IS NULL THEN

1486: l_search_merged := 'N';
1487: ELSE
1488: l_search_merged := p_search_merged;
1489: END IF;
1490: IF p_contains_str IS NULL THEN
1491: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1492: 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);
1493: END IF;
1494: OPEN x_cursor FOR

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

1488: l_search_merged := p_search_merged;
1489: END IF;
1490: IF p_contains_str IS NULL THEN
1491: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1492: 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);
1493: END IF;
1494: OPEN x_cursor FOR
1495: SELECT PARTY_ID , TX2, TX8, TX4, lpad(rtrim(TX41),9,chr(48)), TX44, TX46
1496: FROM HZ_STAGED_PARTIES stage

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

1505: END IF;
1506: OPEN x_cursor FOR
1507: SELECT /*+ INDEX(stage HZ_STAGE_PARTIES_T1) */ PARTY_ID , TX2, TX8, TX4, lpad(rtrim(TX41),9,chr(48)), TX44, TX46
1508: FROM HZ_STAGED_PARTIES stage
1509: WHERE contains( concat_col, p_contains_str)>0
1510: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1511: AND( (l_search_merged ='Y' )
1512: OR (l_search_merged = 'I' AND nvl(stage.status, 'A') in ('A', 'I'))
1513: OR (l_search_merged = 'N' AND nvl(stage.status, 'A') in ('A')) )

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

1518: END IF;
1519: OPEN x_cursor FOR
1520: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_PARTIES_U1) */ stage.PARTY_ID , TX2, TX8, TX4, lpad(rtrim(TX41),9,chr(48)), TX44, TX46
1521: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTIES stage
1522: WHERE contains( concat_col, p_contains_str)>0
1523: AND d.SEARCH_CONTEXT_ID=p_search_ctx_id
1524: AND d.party_id = stage.party_id
1525: AND ((g_party_stage_rec.TX36 IS NULL OR g_party_stage_rec.TX36||' ' = stage.TX36))
1526: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)

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

1543: END IF;
1544: IF p_search_ctx_id IS NULL THEN
1545: l_sqlstr := 'SELECT ' || l_hint || ' PARTY_ID , TX2, TX8, TX4, lpad(rtrim(TX41),9,chr(48)), TX44, TX46'||
1546: ' FROM HZ_STAGED_PARTIES stage'||
1547: ' WHERE contains( concat_col, :cont)>0'||
1548: ' AND ((:TX36 IS NULL OR :TX36||'' '' = stage.TX36))'||
1549: ' AND ('||p_restrict_sql||')' ||
1550: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1551: IF l_search_merged = 'Y' THEN

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

1555: ELSE
1556: l_sqlstr := l_sqlstr ||' AND nvl(stage.status,''A'') in (''A'')';
1557: END IF;
1558: output_long_strings('----------------------------------------------------------');
1559: output_long_strings('Party Contains String = '||p_contains_str);
1560: output_long_strings('Restrict Sql = '||p_restrict_sql);
1561: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1562: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1563: ,p_dup_party_id, p_dup_party_id;

Line 1561: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1557: END IF;
1558: output_long_strings('----------------------------------------------------------');
1559: output_long_strings('Party Contains String = '||p_contains_str);
1560: output_long_strings('Restrict Sql = '||p_restrict_sql);
1561: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1562: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1563: ,p_dup_party_id, p_dup_party_id;
1564: END IF;
1565: END IF;

Line 1592: p_contains_str VARCHAR2,

1588: PROCEDURE open_party_site_cursor(
1589: p_dup_party_id NUMBER,
1590: p_party_id NUMBER,
1591: p_restrict_sql VARCHAR2,
1592: p_contains_str VARCHAR2,
1593: p_search_ctx_id NUMBER,
1594: p_search_merged VARCHAR2,
1595: p_search_rel_sites VARCHAR2,
1596: p_person_api VARCHAR2,

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

1620: IF p_search_rel_sites = 'N' THEN
1621: OPEN x_cursor FOR
1622: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1623: FROM HZ_STAGED_PARTY_SITES stage
1624: WHERE contains( concat_col, p_contains_str)>0
1625: AND( (l_search_merged ='Y' )
1626: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1627: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1628: AND stage.party_id = p_party_id;

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

1629: ELSE
1630: OPEN x_cursor FOR
1631: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1632: FROM HZ_STAGED_PARTY_SITES stage
1633: WHERE contains( concat_col, p_contains_str)>0
1634: AND( (l_search_merged ='Y' )
1635: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1636: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1637: AND stage.party_id = p_party_id

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

1637: AND stage.party_id = p_party_id
1638: UNION
1639: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, stage.PARTY_ID, stage.ORG_CONTACT_ID, TX3, TX4, TX11
1640: FROM HZ_STAGED_PARTY_SITES stage, hz_relationships r, hz_org_contacts oc
1641: WHERE contains( concat_col, p_contains_str)>0
1642: AND( (l_search_merged ='Y' )
1643: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1644: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1645: AND r.object_id = p_party_id

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

1659: END IF;
1660: OPEN x_cursor FOR
1661: SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1662: FROM HZ_STAGED_PARTY_SITES stage
1663: WHERE contains( concat_col, p_contains_str)>0
1664: AND EXISTS (
1665: SELECT 1 FROM HZ_STAGED_PARTIES p
1666: WHERE p.PARTY_ID = stage.PARTY_ID
1667: AND( (l_search_merged ='Y' )

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

1679: IF p_person_api = 'Y' THEN
1680: OPEN x_cursor FOR
1681: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1682: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1683: WHERE contains( concat_col, p_contains_str)>0
1684: AND( (l_search_merged ='Y' )
1685: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1686: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1687: AND d.search_context_id = p_search_ctx_id

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

1689: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
1690: UNION
1691: SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ stage.PARTY_SITE_ID, r.subject_id, stage.ORG_CONTACT_ID, TX3, TX4, TX11
1692: FROM HZ_DQM_PARTIES_GT d, hz_relationships r,hz_org_contacts oc, HZ_STAGED_PARTY_SITES stage
1693: WHERE contains( concat_col, p_contains_str)>0
1694: AND( (l_search_merged ='Y' )
1695: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1696: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1697: AND d.search_context_id = p_search_ctx_id

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

1702: ELSE
1703: OPEN x_cursor FOR
1704: SELECT PARTY_SITE_ID, stage.PARTY_ID, ORG_CONTACT_ID, TX3, TX4, TX11
1705: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_PARTY_SITES stage
1706: WHERE contains( concat_col, p_contains_str)>0
1707: AND( (l_search_merged ='Y' )
1708: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
1709: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
1710: AND d.search_context_id = p_search_ctx_id

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

1747: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
1748: END IF;
1749: l_sqlstr := 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N1) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1750: ' FROM HZ_STAGED_PARTY_SITES stage'||
1751: ' WHERE contains( concat_col, :cont)>0'||
1752: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1753: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1754: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1755: ' AND (ORG_CONTACT_ID IS NULL ' ||

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

1757: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
1758: ' UNION ' ||
1759: 'SELECT /*+ INDEX(stage HZ_STAGED_PARTY_SITES_N2) */ PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1760: ' FROM HZ_STAGED_PARTY_SITES stage'||
1761: ' WHERE contains( concat_col, :cont)>0'||
1762: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1763: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1764: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1765: ' AND ORG_CONTACT_ID IN ' ||

Line 1771: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

1767: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
1768: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1769: ' and ('||p_restrict_sql1|| ') )' ||
1770: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1771: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1772: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1773: ELSE
1774: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1775: ' FROM HZ_STAGED_PARTY_SITES stage'||

Line 1772: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

1768: ' where stage1.relationship_id = oc.party_relationship_id ' ||
1769: ' and ('||p_restrict_sql1|| ') )' ||
1770: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1771: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
1772: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1773: ELSE
1774: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1775: ' FROM HZ_STAGED_PARTY_SITES stage'||
1776: ' WHERE contains( concat_col, :cont)>0'||

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

1772: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
1773: ELSE
1774: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1775: ' FROM HZ_STAGED_PARTY_SITES stage'||
1776: ' WHERE contains( concat_col, :cont)>0'||
1777: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1778: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1779: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1780: ' AND EXISTS ('||

Line 1786: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1782: ' WHERE p.party_id = stage.party_id ' ||
1783: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1784: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
1785: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1786: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1787: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1788: ,p_dup_party_id, p_dup_party_id;
1789: END IF;
1790: ELSE

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

1789: END IF;
1790: ELSE
1791: l_sqlstr := 'SELECT PARTY_SITE_ID, PARTY_ID, ORG_CONTACT_ID , TX3, TX4, TX11'||
1792: ' FROM HZ_STAGED_PARTY_SITES stage'||
1793: ' WHERE contains( concat_col, :cont)>0'||
1794: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
1795: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1796: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1797: ' AND EXISTS ('||

Line 1803: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1799: ' WHERE p.party_id = stage.party_id ' ||
1800: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||
1801: ' AND ('||p_restrict_sql||')' ||
1802: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1803: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1804: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1805: ,p_dup_party_id, p_dup_party_id;
1806: END IF;
1807: END IF;

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

1805: ,p_dup_party_id, p_dup_party_id;
1806: END IF;
1807: END IF;
1808: output_long_strings('----------------------------------------------------------');
1809: output_long_strings('Party Site Contains String = '||p_contains_str);
1810: output_long_strings('Restrict Sql = '||p_restrict_sql);
1811: exception
1812: when others then
1813: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1837: p_contains_str VARCHAR2,

1833: PROCEDURE open_contact_cursor(
1834: p_dup_party_id NUMBER,
1835: p_party_id NUMBER,
1836: p_restrict_sql VARCHAR2,
1837: p_contains_str VARCHAR2,
1838: p_search_ctx_id NUMBER,
1839: p_search_merged VARCHAR2,
1840: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
1841: l_sqlstr VARCHAR2(4000);

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

1861: END IF;
1862: OPEN x_cursor FOR
1863: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, PARTY_ID, TX2, TX6, TX5
1864: FROM HZ_STAGED_CONTACTS stage
1865: WHERE contains( concat_col, p_contains_str)>0
1866: AND EXISTS (
1867: SELECT 1 FROM HZ_STAGED_PARTIES p
1868: WHERE p.PARTY_ID = stage.PARTY_ID
1869: AND( (l_search_merged ='Y' )

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

1884: END IF;
1885: OPEN x_cursor FOR
1886: SELECT ORG_CONTACT_ID, PARTY_ID, TX2, TX6, TX5
1887: FROM HZ_STAGED_CONTACTS stage
1888: WHERE contains( concat_col, p_contains_str)>0
1889: AND EXISTS (
1890: SELECT 1 FROM HZ_STAGED_PARTIES p
1891: WHERE p.PARTY_ID = stage.PARTY_ID
1892: AND( (l_search_merged ='Y' )

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

1903: END IF;
1904: OPEN x_cursor FOR
1905: SELECT /*+ ORDERED INDEX(stage HZ_STAGED_CONTACTS_N1) */ ORG_CONTACT_ID, stage.PARTY_ID, TX2, TX6, TX5
1906: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACTS stage
1907: WHERE contains( concat_col, p_contains_str)>0
1908: AND d.search_context_id = p_search_ctx_id
1909: AND d.party_id = stage.party_id
1910: AND( (l_search_merged ='Y' )
1911: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

1937: l_status_sql := ' AND nvl(p.status,''A'') in (''A'')';
1938: END IF;
1939: l_sqlstr := 'SELECT ' || l_hint || ' ORG_CONTACT_ID, PARTY_ID , TX2, TX6, TX5'||
1940: ' FROM HZ_STAGED_CONTACTS stage'||
1941: ' WHERE contains( concat_col, :cont)>0'||
1942: ' AND EXISTS ('||
1943: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
1944: ' WHERE p.party_id = stage.party_id ' ||
1945: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 1951: OPEN x_cursor FOR l_sqlstr USING p_contains_str

1947: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
1948: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
1949: ' AND ('||p_restrict_sql||')' ||
1950: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
1951: OPEN x_cursor FOR l_sqlstr USING p_contains_str
1952: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1953: ,p_dup_party_id, p_dup_party_id;
1954: END IF;
1955: output_long_strings('----------------------------------------------------------');

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

1952: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
1953: ,p_dup_party_id, p_dup_party_id;
1954: END IF;
1955: output_long_strings('----------------------------------------------------------');
1956: output_long_strings('Contacts Contains String = '||p_contains_str);
1957: output_long_strings('Restrict Sql = '||p_restrict_sql);
1958: exception
1959: when others then
1960: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 1984: p_contains_str VARCHAR2,

1980: PROCEDURE open_contact_pt_cursor(
1981: p_dup_party_id NUMBER,
1982: p_party_id NUMBER,
1983: p_restrict_sql VARCHAR2,
1984: p_contains_str VARCHAR2,
1985: p_search_ctx_id NUMBER,
1986: p_search_merged VARCHAR2,
1987: p_search_rel_cpts VARCHAR2,
1988: p_person_api VARCHAR2,

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

2010: THEN
2011: OPEN x_cursor FOR
2012: 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
2013: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2014: WHERE contains( concat_col, p_contains_str)>0
2015: AND d.search_context_id = p_search_ctx_id
2016: AND( (l_search_merged ='Y' )
2017: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2018: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2022: THEN
2023: OPEN x_cursor FOR
2024: 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
2025: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2026: WHERE contains( concat_col, p_contains_str)>0
2027: AND d.search_context_id = p_search_ctx_id
2028: AND( (l_search_merged ='Y' )
2029: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2030: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2039: IF p_search_rel_cpts = 'N' THEN
2040: OPEN x_cursor FOR
2041: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
2042: FROM HZ_STAGED_CONTACT_POINTS stage
2043: WHERE contains( concat_col, p_contains_str)>0
2044: AND( (l_search_merged ='Y' )
2045: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2046: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2047: AND stage.party_id = p_party_id;

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

2048: ELSE
2049: OPEN x_cursor FOR
2050: SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N1) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
2051: FROM HZ_STAGED_CONTACT_POINTS stage
2052: WHERE contains( concat_col, p_contains_str)>0
2053: AND( (l_search_merged ='Y' )
2054: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2055: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )
2056: AND stage.party_id = p_party_id

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

2056: AND stage.party_id = p_party_id
2057: UNION
2058: 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
2059: FROM HZ_STAGED_CONTACT_POINTS stage, hz_relationships r, hz_org_contacts oc
2060: WHERE contains( concat_col, p_contains_str)>0
2061: AND r.object_id = p_party_id
2062: AND( (l_search_merged ='Y' )
2063: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))
2064: OR (l_search_merged = 'N' AND nvl(stage.status_flag, 'A') = 'A') )

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

2078: END IF;
2079: OPEN x_cursor FOR
2080: SELECT CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
2081: FROM HZ_STAGED_CONTACT_POINTS stage
2082: WHERE contains( concat_col, p_contains_str)>0
2083: AND EXISTS (
2084: SELECT 1 FROM HZ_STAGED_PARTIES p
2085: WHERE p.PARTY_ID = stage.PARTY_ID
2086: AND( (l_search_merged ='Y' )

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

2098: IF p_person_api = 'Y' THEN
2099: OPEN x_cursor FOR
2100: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
2101: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2102: WHERE contains( concat_col, p_contains_str)>0
2103: AND d.search_context_id = p_search_ctx_id
2104: AND d.party_id = stage.party_id
2105: AND( (l_search_merged ='Y' )
2106: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2108: AND (p_dup_party_id IS NULL OR stage.party_id <> p_dup_party_id)
2109: UNION
2110: 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
2111: FROM HZ_DQM_PARTIES_GT d, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc, HZ_STAGED_CONTACT_POINTS stage
2112: WHERE contains( concat_col, p_contains_str)>0
2113: AND d.search_context_id = p_search_ctx_id
2114: AND d.party_id = r.subject_id
2115: AND r.relationship_id = oc.party_relationship_id
2116: AND oc.org_contact_id = stage.org_contact_id

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

2121: ELSE
2122: OPEN x_cursor FOR
2123: SELECT CONTACT_POINT_ID, stage.contact_point_type, stage.PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1
2124: FROM HZ_DQM_PARTIES_GT d, HZ_STAGED_CONTACT_POINTS stage
2125: WHERE contains( concat_col, p_contains_str)>0
2126: AND d.search_context_id = p_search_ctx_id
2127: AND d.party_id = stage.party_id
2128: AND( (l_search_merged ='Y' )
2129: OR (l_search_merged = 'I' AND nvl(stage.status_flag, 'A') in ('A', 'I'))

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

2166: p_restrict_sql1 := 'stage1.'||p_restrict_sql;
2167: END IF;
2168: 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'||
2169: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2170: ' WHERE contains( concat_col, :cont)>0 '||
2171: ' AND (stage.org_contact_id is null '||
2172: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2173: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2174: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||

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

2176: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ' ||
2177: ' UNION ' ||
2178: ' SELECT /*+ INDEX(stage HZ_STAGED_CONTACT_POINTS_N2) */ CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1'||
2179: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2180: ' WHERE contains( concat_col, :cont)>0 '||
2181: ' AND( ('''||l_search_merged||''' =''Y'' ) '||
2182: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2183: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2184: ' AND (stage.org_contact_id in ' ||

Line 2190: OPEN x_cursor FOR l_sqlstr USING p_contains_str,

2186: ' where subject_type = ''PERSON'' AND object_type = ''ORGANIZATION'') stage1 ' ||
2187: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2188: ' and ('||p_restrict_sql1 || ') ) )' ||
2189: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2190: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2191: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2192: ELSE
2193: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1'||
2194: ' FROM HZ_STAGED_CONTACT_POINTS stage'||

Line 2191: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;

2187: ' where stage1.relationship_id = oc.party_relationship_id ' ||
2188: ' and ('||p_restrict_sql1 || ') ) )' ||
2189: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2190: OPEN x_cursor FOR l_sqlstr USING p_contains_str,
2191: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2192: ELSE
2193: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1'||
2194: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2195: ' WHERE contains( concat_col, :cont)>0'||

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

2191: p_dup_party_id, p_dup_party_id, p_contains_str, p_dup_party_id, p_dup_party_id;
2192: ELSE
2193: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1'||
2194: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2195: ' WHERE contains( concat_col, :cont)>0'||
2196: ' AND EXISTS ('||
2197: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2198: ' WHERE p.party_id = stage.party_id ' ||
2199: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 2205: OPEN x_cursor FOR l_sqlstr USING p_contains_str

2201: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2202: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2203: ' AND ('||get_adjusted_restrict_sql(p_restrict_sql)||')' ||
2204: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2205: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2206: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2207: ,p_dup_party_id, p_dup_party_id;
2208: END IF;
2209: ELSE

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

2208: END IF;
2209: ELSE
2210: l_sqlstr := 'SELECT ' || l_hint ||' CONTACT_POINT_ID, stage.contact_point_type, PARTY_ID, PARTY_SITE_ID, ORG_CONTACT_ID , TX1'||
2211: ' FROM HZ_STAGED_CONTACT_POINTS stage'||
2212: ' WHERE contains( concat_col, :cont)>0'||
2213: ' AND EXISTS ('||
2214: ' SELECT 1 FROM HZ_STAGED_PARTIES p ' ||
2215: ' WHERE p.party_id = stage.party_id ' ||
2216: ' AND ((:TX36 IS NULL OR :TX36||'' '' = p.TX36)) '|| l_status_sql ||' ) ' ||

Line 2222: OPEN x_cursor FOR l_sqlstr USING p_contains_str

2218: ' OR ('''||l_search_merged||''' = ''I'' AND nvl(stage.status_flag, ''A'') in (''A'', ''I'')) '||
2219: ' OR ('''||l_search_merged||''' = ''N'' AND nvl(stage.status_flag, ''A'') = ''A'') ) '||
2220: ' AND ('||p_restrict_sql||')' ||
2221: ' AND (:p_dup IS NULL OR stage.party_id <> :p_dup) ';
2222: OPEN x_cursor FOR l_sqlstr USING p_contains_str
2223: ,g_party_stage_rec.TX36,g_party_stage_rec.TX36
2224: ,p_dup_party_id, p_dup_party_id;
2225: END IF;
2226: END IF;

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

2225: END IF;
2226: END IF;
2227: END IF;
2228: output_long_strings('----------------------------------------------------------');
2229: output_long_strings('Contact Points Contains String = '||p_contains_str);
2230: output_long_strings('Restrict Sql = '||p_restrict_sql);
2231: exception
2232: when others then
2233: if (instrb(SQLERRM,'DRG-51030')>0) then

Line 2350: l_party_contains_str VARCHAR2(32000);

2346: x_num_matches OUT NUMBER
2347: ) IS
2348:
2349: -- Strings to hold the generated Intermedia query strings
2350: l_party_contains_str VARCHAR2(32000);
2351: l_party_site_contains_str VARCHAR2(32000);
2352: l_contact_contains_str VARCHAR2(32000);
2353: l_contact_pt_contains_str VARCHAR2(32000);
2354: l_denorm_str VARCHAR2(32000);

Line 2351: l_party_site_contains_str VARCHAR2(32000);

2347: ) IS
2348:
2349: -- Strings to hold the generated Intermedia query strings
2350: l_party_contains_str VARCHAR2(32000);
2351: l_party_site_contains_str VARCHAR2(32000);
2352: l_contact_contains_str VARCHAR2(32000);
2353: l_contact_pt_contains_str VARCHAR2(32000);
2354: l_denorm_str VARCHAR2(32000);
2355: l_ps_denorm_str VARCHAR2(32000);

Line 2352: l_contact_contains_str VARCHAR2(32000);

2348:
2349: -- Strings to hold the generated Intermedia query strings
2350: l_party_contains_str VARCHAR2(32000);
2351: l_party_site_contains_str VARCHAR2(32000);
2352: l_contact_contains_str VARCHAR2(32000);
2353: l_contact_pt_contains_str VARCHAR2(32000);
2354: l_denorm_str VARCHAR2(32000);
2355: l_ps_denorm_str VARCHAR2(32000);
2356: l_ct_denorm_str VARCHAR2(32000);

Line 2353: l_contact_pt_contains_str VARCHAR2(32000);

2349: -- Strings to hold the generated Intermedia query strings
2350: l_party_contains_str VARCHAR2(32000);
2351: l_party_site_contains_str VARCHAR2(32000);
2352: l_contact_contains_str VARCHAR2(32000);
2353: l_contact_pt_contains_str VARCHAR2(32000);
2354: l_denorm_str VARCHAR2(32000);
2355: l_ps_denorm_str VARCHAR2(32000);
2356: l_ct_denorm_str VARCHAR2(32000);
2357: l_cpt_denorm_str VARCHAR2(32000);

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

2445: call_type(2) := 'OR';
2446: END;
2447:
2448: /** Private procedure to acquire and score at party level ***/
2449: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
2450: l_party_id_idx NUMBER:=1;
2451: l_ctx_id NUMBER;
2452: l_precalc_score BOOLEAN := FALSE;
2453: l_TX35_new varchar2(4000);

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

2477: l_search_ctx_id,H_PARTY_ID(I));
2478: l_party_id_idx := H_PARTY_ID.COUNT+1;
2479: ELSE
2480: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2481: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
2482: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2483: 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);
2484: END IF;
2485: 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 2489: IF (l_party_site_contains_str IS NULL AND

2485: 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
2486: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2487: 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);
2488: END IF;
2489: IF (l_party_site_contains_str IS NULL AND
2490: l_contact_contains_str IS NULL AND
2491: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2492: 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);
2493: RAISE L_RETURN_IMM_EXC;

Line 2490: l_contact_contains_str IS NULL AND

2486: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2487: 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);
2488: END IF;
2489: IF (l_party_site_contains_str IS NULL AND
2490: l_contact_contains_str IS NULL AND
2491: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2492: 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);
2493: RAISE L_RETURN_IMM_EXC;
2494: ELSE

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

2487: 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);
2488: END IF;
2489: IF (l_party_site_contains_str IS NULL AND
2490: l_contact_contains_str IS NULL AND
2491: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2492: 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);
2493: RAISE L_RETURN_IMM_EXC;
2494: ELSE
2495: 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 2495: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);

2491: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs AND p_dup_set_id IS NULL THEN
2492: 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);
2493: RAISE L_RETURN_IMM_EXC;
2494: ELSE
2495: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,p_search_merged,p_party_contains_str,l_party_cur);
2496: END IF;
2497: ELSE
2498: IF p_index>1 THEN
2499: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

2514: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2515: 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);
2516: END IF;
2517: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
2518: 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);
2519: END IF;
2520: LOOP
2521: FETCH l_party_cur INTO
2522: l_party_id , l_TX2, l_TX8, l_TX4, l_TX41, l_TX44, l_TX46;

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

2536: l_party_id_idx:= l_party_id_idx+1;
2537: END IF;
2538: IF (l_party_id_idx-1)>l_max_thresh THEN
2539: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2540: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2541: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2542: AND l_contact_pt_contains_str IS NULL THEN
2543: EXIT;
2544: END IF;

Line 2542: AND l_contact_pt_contains_str IS NULL THEN

2538: IF (l_party_id_idx-1)>l_max_thresh THEN
2539: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
2540: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
2541: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
2542: AND l_contact_pt_contains_str IS NULL THEN
2543: EXIT;
2544: END IF;
2545: CLOSE l_party_cur;
2546: IF p_index>1 THEN

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

2567: ROLLBACK to eval_start;
2568: END;
2569:
2570: /** Private procedure to acquire and score at party site level ***/
2571: 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
2572: l_party_id_idx NUMBER:=1;
2573: l_ctx_id NUMBER;
2574: h_ps_id HZ_PARTY_SEARCH.IDList;
2575: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

2579: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2580: 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);
2581: 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);
2582: END IF;
2583: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2584: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2585: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2586: l_party_id := H_SCORES.FIRST;
2587: WHILE l_party_id IS NOT NULL LOOP

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

2580: 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);
2581: 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);
2582: END IF;
2583: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2584: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2585: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2586: l_party_id := H_SCORES.FIRST;
2587: WHILE l_party_id IS NOT NULL LOOP
2588: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;

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

2581: 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);
2582: END IF;
2583: IF (l_party_contains_str IS NOT NULL AND instrb(l_party_contains_str,'D_PS')>0
2584: AND l_contact_contains_str IS NULL and H_PARTY_ID.COUNT > 0 and
2585: l_contact_pt_contains_str IS NULL) AND g_ps_den_only AND p_ins_details <> 'Y' THEN
2586: l_party_id := H_SCORES.FIRST;
2587: WHILE l_party_id IS NOT NULL LOOP
2588: H_SCORES(l_party_id).TOTAL_SCORE := H_SCORES(l_party_id).TOTAL_SCORE + p_emax_score;
2589: l_party_id:=H_SCORES.NEXT(l_party_id);

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

2611: l_search_ctx_id,H_PARTY_ID(I));
2612: l_party_id_idx := H_PARTY_ID.COUNT+1;
2613: ELSE
2614: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2615: and check_estimate_hits('PARTY_SITES',p_party_site_contains_str)>l_max_thresh THEN
2616: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2617: 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);
2618: END IF;
2619: IF p_index>1 THEN

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

2633: END IF;
2634: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2635: 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);
2636: END IF;
2637: 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);
2638: LOOP
2639: FETCH l_party_site_cur INTO
2640: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;
2641: EXIT WHEN l_party_site_cur%NOTFOUND;

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

2689: END IF;
2690: END;
2691:
2692: /** Private procedure to acquire and score at contact point level ***/
2693: 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
2694: l_party_id_idx NUMBER:=1;
2695: l_ctx_id NUMBER;
2696: h_ct_id HZ_PARTY_SEARCH.IDList;
2697: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

2723: l_search_ctx_id,H_PARTY_ID(I));
2724: l_party_id_idx := H_PARTY_ID.COUNT+1;
2725: ELSE
2726: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2727: and check_estimate_hits('CONTACTS',p_contact_contains_str)>l_max_thresh THEN
2728: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2729: 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);
2730: END IF;
2731: IF p_index>1 THEN

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

2745: END IF;
2746: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2747: 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);
2748: END IF;
2749: open_contact_cursor(p_dup_party_id,NULL, p_restrict_sql, p_contact_contains_str,l_ctx_id, p_search_merged, l_contact_cur);
2750: LOOP
2751: FETCH l_contact_cur INTO
2752: l_org_contact_id, l_ct_party_id , l_TX2, l_TX6, l_TX5;
2753: EXIT WHEN l_contact_cur%NOTFOUND;

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

2798: END IF;
2799: END;
2800:
2801: /** Private procedure to acquire and score at contact point level ***/
2802: 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
2803: l_party_id_idx NUMBER:=1;
2804: l_ctx_id NUMBER;
2805: h_cpt_id HZ_PARTY_SEARCH.IDList;
2806: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

2834: l_search_ctx_id,H_PARTY_ID(I));
2835: l_party_id_idx := H_PARTY_ID.COUNT+1;
2836: ELSE
2837: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
2838: and check_estimate_hits('CONTACT_POINTS',p_contact_pt_contains_str)>l_max_thresh THEN
2839: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2840: 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);
2841: END IF;
2842: IF p_index>1 THEN

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

2856: END IF;
2857: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2858: 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);
2859: END IF;
2860: 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);
2861: LOOP
2862: FETCH l_contact_pt_cur INTO
2863: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1;
2864: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 2960: IF l_party_contains_str IS NOT NULL THEN

2956: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
2957: l_threshold := 100;
2958: other_acq_criteria_exists := TRUE ;
2959: --check if acquisition criteria exists for any other entity
2960: IF l_party_contains_str IS NOT NULL THEN
2961: acq_cnt := 1;
2962: END IF;
2963: IF l_party_site_contains_str IS NOT NULL THEN
2964: acq_cnt := acq_cnt+1;

Line 2963: IF l_party_site_contains_str IS NOT NULL THEN

2959: --check if acquisition criteria exists for any other entity
2960: IF l_party_contains_str IS NOT NULL THEN
2961: acq_cnt := 1;
2962: END IF;
2963: IF l_party_site_contains_str IS NOT NULL THEN
2964: acq_cnt := acq_cnt+1;
2965: END IF;
2966: IF l_contact_contains_str IS NOT NULL THEN
2967: acq_cnt := acq_cnt+1;

Line 2966: IF l_contact_contains_str IS NOT NULL THEN

2962: END IF;
2963: IF l_party_site_contains_str IS NOT NULL THEN
2964: acq_cnt := acq_cnt+1;
2965: END IF;
2966: IF l_contact_contains_str IS NOT NULL THEN
2967: acq_cnt := acq_cnt+1;
2968: END IF;
2969: IF l_contact_pt_contains_str IS NOT NULL THEN
2970: acq_cnt := acq_cnt+1;

Line 2969: IF l_contact_pt_contains_str IS NOT NULL THEN

2965: END IF;
2966: IF l_contact_contains_str IS NOT NULL THEN
2967: acq_cnt := acq_cnt+1;
2968: END IF;
2969: IF l_contact_pt_contains_str IS NOT NULL THEN
2970: acq_cnt := acq_cnt+1;
2971: END IF;
2972: IF acq_cnt>1 THEN
2973: other_acq_criteria_exists := TRUE;

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

2999: FND_MSG_PUB.ADD;
3000: RAISE FND_API.G_EXC_ERROR;
3001: END IF;
3002: END IF;
3003: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3004: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3005: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3006: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3007: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN

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

3000: RAISE FND_API.G_EXC_ERROR;
3001: END IF;
3002: END IF;
3003: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3004: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3005: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3006: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3007: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3008: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

3001: END IF;
3002: END IF;
3003: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3004: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3005: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3006: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3007: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3008: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3009: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

3002: END IF;
3003: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3004: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3005: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3006: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3007: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3008: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3009: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3010: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

3003: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3004: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3005: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3006: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3007: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3008: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3009: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3010: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3011: END IF;

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

3004: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3005: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3006: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3007: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3008: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3009: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3010: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3011: END IF;
3012: END;

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

3005: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3006: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3007: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3008: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3009: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3010: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3011: END IF;
3012: END;
3013: /************ End of find_parties private procedures **********/

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

3006: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3007: ELSIF call_order(p_index) = 'CONTACT' AND l_contact_contains_str IS NOT NULL THEN
3008: eval_contact_level(l_contact_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3009: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3010: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3011: END IF;
3012: END;
3013: /************ End of find_parties private procedures **********/
3014:

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

3089: 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);
3090: END IF;
3091: -- ************************************************************
3092: -- STEP 2. Setup of intermedia query strings for Acquisition query
3093: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3094: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3095: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3096: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3097: 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 3094: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);

3090: END IF;
3091: -- ************************************************************
3092: -- STEP 2. Setup of intermedia query strings for Acquisition query
3093: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3094: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3095: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3096: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3097: 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);
3098: END IF;

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

3091: -- ************************************************************
3092: -- STEP 2. Setup of intermedia query strings for Acquisition query
3093: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3094: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3095: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3096: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3097: 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);
3098: END IF;
3099: l_denorm_max_score:=0;

Line 3127: 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/445)*l_entered_max_score));

3123: END IF;
3124: ELSE
3125: l_non_denorm_max_score := l_non_denorm_max_score+l_cpt_max_score;
3126: END IF;
3127: 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/445)*l_entered_max_score));
3128: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3129:
3130: -- Setup Search Context ID
3131: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3137: IF l_party_contains_str IS NULL THEN

3133:
3134: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3135: 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);
3136: END IF;
3137: IF l_party_contains_str IS NULL THEN
3138: defpt := 1;
3139: END IF;
3140: IF l_party_site_contains_str IS NULL THEN
3141: defps := 1;

Line 3140: IF l_party_site_contains_str IS NULL THEN

3136: END IF;
3137: IF l_party_contains_str IS NULL THEN
3138: defpt := 1;
3139: END IF;
3140: IF l_party_site_contains_str IS NULL THEN
3141: defps := 1;
3142: END IF;
3143: IF l_contact_contains_str IS NULL THEN
3144: defct := 1;

Line 3143: IF l_contact_contains_str IS NULL THEN

3139: END IF;
3140: IF l_party_site_contains_str IS NULL THEN
3141: defps := 1;
3142: END IF;
3143: IF l_contact_contains_str IS NULL THEN
3144: defct := 1;
3145: END IF;
3146: IF l_contact_pt_contains_str IS NULL THEN
3147: defcpt := 1;

Line 3146: IF l_contact_pt_contains_str IS NULL THEN

3142: END IF;
3143: IF l_contact_contains_str IS NULL THEN
3144: defct := 1;
3145: END IF;
3146: IF l_contact_pt_contains_str IS NULL THEN
3147: defcpt := 1;
3148: END IF;
3149:
3150: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3151: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3152: 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);
3153: END IF;
3154: 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);
3155: 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);
3156: 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);
3157: 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);
3158: 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);
3159: 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 3156: 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);

3152: 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);
3153: END IF;
3154: 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);
3155: 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);
3156: 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);
3157: 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);
3158: 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);
3159: 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);
3160: END IF;

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

3153: END IF;
3154: 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);
3155: 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);
3156: 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);
3157: 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);
3158: 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);
3159: 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);
3160: END IF;
3161: FOR I in 1..5 LOOP

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

3154: 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);
3155: 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);
3156: 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);
3157: 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);
3158: 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);
3159: 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);
3160: END IF;
3161: FOR I in 1..5 LOOP
3162: do_eval(I);

Line 3272: l_party_contains_str VARCHAR2(32000);

3268: x_num_matches OUT NUMBER
3269: ) IS
3270:
3271: -- Strings to hold the generated Intermedia query strings
3272: l_party_contains_str VARCHAR2(32000);
3273: l_party_site_contains_str VARCHAR2(32000);
3274: l_contact_contains_str VARCHAR2(32000);
3275: l_contact_pt_contains_str VARCHAR2(32000);
3276: l_denorm_str VARCHAR2(32000);

Line 3273: l_party_site_contains_str VARCHAR2(32000);

3269: ) IS
3270:
3271: -- Strings to hold the generated Intermedia query strings
3272: l_party_contains_str VARCHAR2(32000);
3273: l_party_site_contains_str VARCHAR2(32000);
3274: l_contact_contains_str VARCHAR2(32000);
3275: l_contact_pt_contains_str VARCHAR2(32000);
3276: l_denorm_str VARCHAR2(32000);
3277: l_ps_denorm_str VARCHAR2(32000);

Line 3274: l_contact_contains_str VARCHAR2(32000);

3270:
3271: -- Strings to hold the generated Intermedia query strings
3272: l_party_contains_str VARCHAR2(32000);
3273: l_party_site_contains_str VARCHAR2(32000);
3274: l_contact_contains_str VARCHAR2(32000);
3275: l_contact_pt_contains_str VARCHAR2(32000);
3276: l_denorm_str VARCHAR2(32000);
3277: l_ps_denorm_str VARCHAR2(32000);
3278: l_ct_denorm_str VARCHAR2(32000);

Line 3275: l_contact_pt_contains_str VARCHAR2(32000);

3271: -- Strings to hold the generated Intermedia query strings
3272: l_party_contains_str VARCHAR2(32000);
3273: l_party_site_contains_str VARCHAR2(32000);
3274: l_contact_contains_str VARCHAR2(32000);
3275: l_contact_pt_contains_str VARCHAR2(32000);
3276: l_denorm_str VARCHAR2(32000);
3277: l_ps_denorm_str VARCHAR2(32000);
3278: l_ct_denorm_str VARCHAR2(32000);
3279: l_cpt_denorm_str VARCHAR2(32000);

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

3397: call_type(2) := 'OR';
3398: END;
3399:
3400: /** Private procedure to acquire and score at party level ***/
3401: PROCEDURE eval_party_level(p_party_contains_str VARCHAR2,p_call_type VARCHAR2, p_index NUMBER) IS
3402: l_party_id_idx NUMBER:=1;
3403: l_ctx_id NUMBER;
3404: l_TX35_new varchar2(4000);
3405: BEGIN

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

3419: l_search_ctx_id,H_PARTY_ID(I));
3420: l_party_id_idx := H_PARTY_ID.COUNT+1;
3421: ELSE
3422: IF (p_restrict_sql IS NULL OR instrb(p_restrict_sql, 'SELECTIVE')=0)
3423: and check_estimate_hits('PARTY',p_party_contains_str)>l_max_thresh THEN
3424: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3425: 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);
3426: END IF;
3427: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN

Line 3431: IF (l_party_site_contains_str IS NULL AND

3427: IF g_party_stage_rec.TX8 IS NOT NULL AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL' THEN
3428: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3429: 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);
3430: END IF;
3431: IF (l_party_site_contains_str IS NULL AND
3432: l_contact_contains_str IS NULL AND
3433: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3434: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3435: RAISE L_RETURN_IMM_EXC;

Line 3432: l_contact_contains_str IS NULL AND

3428: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3429: 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);
3430: END IF;
3431: IF (l_party_site_contains_str IS NULL AND
3432: l_contact_contains_str IS NULL AND
3433: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3434: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3435: RAISE L_RETURN_IMM_EXC;
3436: ELSE

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

3429: 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);
3430: END IF;
3431: IF (l_party_site_contains_str IS NULL AND
3432: l_contact_contains_str IS NULL AND
3433: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3434: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3435: RAISE L_RETURN_IMM_EXC;
3436: ELSE
3437: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);

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

3433: l_contact_pt_contains_str IS NULL) AND NOT g_other_party_level_attribs IS NULL THEN
3434: return_direct_matches(p_restrict_sql,l_match_str,l_max_thresh,l_search_ctx_id,null,null, x_num_matches);
3435: RAISE L_RETURN_IMM_EXC;
3436: ELSE
3437: open_party_cursor_direct(p_dup_party_id, p_restrict_sql, l_match_str,null,p_party_contains_str,l_party_cur);
3438: END IF;
3439: ELSE
3440: IF p_index>1 THEN
3441: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3453: l_ctx_id := NULL;
3454: l_party_id_idx := H_PARTY_ID.COUNT+1;
3455: END IF;
3456: IF l_party_cur IS NULL OR (not l_party_cur%ISOPEN) THEN
3457: 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);
3458: END IF;
3459: LOOP
3460: FETCH l_party_cur INTO
3461: l_party_id , l_TX2, l_TX8, l_TX4, l_TX41, l_TX44, l_TX46;

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

3475: l_party_id_idx:= l_party_id_idx+1;
3476: END IF;
3477: IF (l_party_id_idx-1)>l_max_thresh THEN
3478: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3479: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3480: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3481: AND l_contact_pt_contains_str IS NULL THEN
3482: H_PARTY_ID.DELETE(l_party_id_idx-1);
3483: H_PARTY_ID_LIST.DELETE(l_index);

Line 3481: AND l_contact_pt_contains_str IS NULL THEN

3477: IF (l_party_id_idx-1)>l_max_thresh THEN
3478: IF p_index=1 AND call_order(2) = 'PARTY_SITE'
3479: AND call_type(2) = 'AND' AND l_contact_contains_str IS NULL
3480: AND nvl(FND_PROFILE.VALUE('HZ_DQM_PN_THRESH_RESOLUTION'),'NONE')='SQL'
3481: AND l_contact_pt_contains_str IS NULL THEN
3482: H_PARTY_ID.DELETE(l_party_id_idx-1);
3483: H_PARTY_ID_LIST.DELETE(l_index);
3484: H_SCORES.DELETE(l_index);
3485: EXIT;

Line 3510: p_contains_str VARCHAR2,

3506: CLOSE l_party_cur;
3507: ROLLBACK to eval_start;
3508: END;
3509: PROCEDURE open_person_contact_cursor(
3510: p_contains_str VARCHAR2,
3511: p_search_ctx_id NUMBER,
3512: x_cursor OUT HZ_PARTY_STAGE.StageCurTyp) IS
3513: BEGIN
3514: OPEN x_cursor FOR

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

3513: BEGIN
3514: OPEN x_cursor FOR
3515: SELECT /*+ INDEX(stage HZ_STAGED_CONTACTS_U1) */ ORG_CONTACT_ID, PARTY_ID, TX2, TX6, TX5
3516: FROM HZ_STAGED_CONTACTS stage
3517: WHERE contains( concat_col, p_contains_str)>0
3518: AND ORG_CONTACT_ID in (
3519: SELECT /*+ ORDERED INDEX(d hz_dqm_parties_gt_n1) USE_NL(d r)*/
3520: org_contact_id
3521: from hz_dqm_parties_gt d, hz_relationships r, hz_org_contacts oc

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

3537: end if;
3538: END;
3539:
3540: /** Private procedure to acquire and score at party site level ***/
3541: 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
3542: l_party_id_idx NUMBER:=1;
3543: l_ctx_id NUMBER;
3544: h_ps_id HZ_PARTY_SEARCH.IDList;
3545: h_ps_party_id HZ_PARTY_SEARCH.IDList;

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

3565: ELSE
3566: l_party_id_idx := H_PARTY_ID.COUNT+1;
3567: l_ctx_id := NULL;
3568: END IF;
3569: 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);
3570: LOOP
3571: FETCH l_party_site_cur INTO
3572: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;
3573: EXIT WHEN l_party_site_cur%NOTFOUND;

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

3636: END IF;
3637: END;
3638:
3639: /** Private procedure to acquire and score at party site level ***/
3640: PROCEDURE eval_contact_level(p_contact_contains_str VARCHAR2,p_ins_details VARCHAR2,p_emax_score NUMBER) IS
3641: l_party_id_idx NUMBER:=1;
3642: l_ctx_id NUMBER;
3643: h_ct_id HZ_PARTY_SEARCH.IDList;
3644: h_ct_party_id HZ_PARTY_SEARCH.IDList;

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

3652: FORALL I in 1..H_PARTY_ID.COUNT
3653: INSERT INTO HZ_DQM_PARTIES_GT (SEARCH_CONTEXT_ID,PARTY_ID) VALUES (
3654: l_search_ctx_id,H_PARTY_ID(I));
3655: l_party_id_idx := H_PARTY_ID.COUNT+1;
3656: open_person_contact_cursor(p_contact_contains_str,l_ctx_id, l_contact_cur);
3657: LOOP
3658: FETCH l_contact_cur INTO
3659: l_org_contact_id, l_ct_party_id , l_TX2, l_TX6, l_TX5;
3660: EXIT WHEN l_contact_cur%NOTFOUND;

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

3694: END IF;
3695: END;
3696:
3697: /** Private procedure to acquire and score at contact point level ***/
3698: 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
3699: l_party_id_idx NUMBER:=1;
3700: l_ctx_id NUMBER;
3701: h_cpt_id HZ_PARTY_SEARCH.IDList;
3702: h_cpt_party_id HZ_PARTY_SEARCH.IDList;

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

3739: END IF;
3740: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3741: 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);
3742: END IF;
3743: 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);
3744: LOOP
3745: FETCH l_contact_pt_cur INTO
3746: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1;
3747: EXIT WHEN l_contact_pt_cur%NOTFOUND;

Line 3851: IF l_party_contains_str IS NOT NULL THEN

3847: IF (p_index=5 AND call_order(5) <> 'NONE' AND H_PARTY_ID.COUNT=0) THEN
3848: l_threshold := 100;
3849: other_acq_criteria_exists := TRUE ;
3850: --check if acquisition criteria exists for any other entity
3851: IF l_party_contains_str IS NOT NULL THEN
3852: acq_cnt := 1;
3853: END IF;
3854: IF l_party_site_contains_str IS NOT NULL THEN
3855: acq_cnt := acq_cnt+1;

Line 3854: IF l_party_site_contains_str IS NOT NULL THEN

3850: --check if acquisition criteria exists for any other entity
3851: IF l_party_contains_str IS NOT NULL THEN
3852: acq_cnt := 1;
3853: END IF;
3854: IF l_party_site_contains_str IS NOT NULL THEN
3855: acq_cnt := acq_cnt+1;
3856: END IF;
3857: IF l_contact_contains_str IS NOT NULL THEN
3858: acq_cnt := acq_cnt+1;

Line 3857: IF l_contact_contains_str IS NOT NULL THEN

3853: END IF;
3854: IF l_party_site_contains_str IS NOT NULL THEN
3855: acq_cnt := acq_cnt+1;
3856: END IF;
3857: IF l_contact_contains_str IS NOT NULL THEN
3858: acq_cnt := acq_cnt+1;
3859: END IF;
3860: IF l_contact_pt_contains_str IS NOT NULL THEN
3861: acq_cnt := acq_cnt+1;

Line 3860: IF l_contact_pt_contains_str IS NOT NULL THEN

3856: END IF;
3857: IF l_contact_contains_str IS NOT NULL THEN
3858: acq_cnt := acq_cnt+1;
3859: END IF;
3860: IF l_contact_pt_contains_str IS NOT NULL THEN
3861: acq_cnt := acq_cnt+1;
3862: END IF;
3863: IF acq_cnt>1 THEN
3864: other_acq_criteria_exists := TRUE;

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

3890: FND_MSG_PUB.ADD;
3891: RAISE FND_API.G_EXC_ERROR;
3892: END IF;
3893: END IF;
3894: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3895: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3896: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3897: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3898: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN

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

3891: RAISE FND_API.G_EXC_ERROR;
3892: END IF;
3893: END IF;
3894: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3895: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3896: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3897: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3898: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3899: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));

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

3892: END IF;
3893: END IF;
3894: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3895: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3896: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3897: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3898: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3899: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3900: END IF;

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

3893: END IF;
3894: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3895: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3896: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3897: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3898: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3899: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3900: END IF;
3901: END;

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

3894: IF call_order(p_index) = 'PARTY' AND l_party_contains_str IS NOT NULL THEN
3895: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3896: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3897: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3898: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3899: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3900: END IF;
3901: END;
3902: /************ End of find_persons private procedures **********/

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

3895: eval_party_level(l_party_contains_str,call_type(p_index), p_index);
3896: ELSIF call_order(p_index) = 'PARTY_SITE' AND l_party_site_contains_str IS NOT NULL THEN
3897: eval_party_site_level(l_party_site_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3898: ELSIF call_order(p_index) = 'CONTACT_POINT' AND l_contact_pt_contains_str IS NOT NULL THEN
3899: eval_cpt_level(l_contact_pt_contains_str,call_type(p_index), p_index,p_ins_details,call_max_score(p_index));
3900: END IF;
3901: END;
3902: /************ End of find_persons private procedures **********/
3903:

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

3930: g_score_until_thresh := false;
3931: END IF;
3932: -- ************************************************************
3933: -- STEP 2. Setup of intermedia query strings for Acquisition query
3934: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3935: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3936: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3937: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3938: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);

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

3931: END IF;
3932: -- ************************************************************
3933: -- STEP 2. Setup of intermedia query strings for Acquisition query
3934: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3935: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3936: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3937: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3938: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3939:

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

3932: -- ************************************************************
3933: -- STEP 2. Setup of intermedia query strings for Acquisition query
3934: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3935: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3936: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3937: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3938: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3939:
3940: -- Setup Search Context ID

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

3933: -- STEP 2. Setup of intermedia query strings for Acquisition query
3934: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_ps_denorm_str);
3935: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_ct_denorm_str);
3936: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_cpt_denorm_str);
3937: l_party_contains_str := INIT_PARTY_QUERY(l_match_str, null, 0, 0, 0,0);
3938: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
3939:
3940: -- Setup Search Context ID
3941: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

Line 3944: IF l_party_contains_str IS NULL THEN

3940: -- Setup Search Context ID
3941: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
3942: x_search_ctx_id := l_search_ctx_id;
3943:
3944: IF l_party_contains_str IS NULL THEN
3945: defpt := 1;
3946: END IF;
3947: IF l_party_site_contains_str IS NULL THEN
3948: defps := 1;

Line 3947: IF l_party_site_contains_str IS NULL THEN

3943:
3944: IF l_party_contains_str IS NULL THEN
3945: defpt := 1;
3946: END IF;
3947: IF l_party_site_contains_str IS NULL THEN
3948: defps := 1;
3949: END IF;
3950: IF l_contact_contains_str IS NULL THEN
3951: defct := 1;

Line 3950: IF l_contact_contains_str IS NULL THEN

3946: END IF;
3947: IF l_party_site_contains_str IS NULL THEN
3948: defps := 1;
3949: END IF;
3950: IF l_contact_contains_str IS NULL THEN
3951: defct := 1;
3952: END IF;
3953: IF l_contact_pt_contains_str IS NULL THEN
3954: defcpt := 1;

Line 3953: IF l_contact_pt_contains_str IS NULL THEN

3949: END IF;
3950: IF l_contact_contains_str IS NULL THEN
3951: defct := 1;
3952: END IF;
3953: IF l_contact_pt_contains_str IS NULL THEN
3954: defcpt := 1;
3955: END IF;
3956:
3957: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN

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

3955: END IF;
3956:
3957: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3958: 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);
3959: 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);
3960: 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);
3961: 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);
3962: 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);
3963: 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 3960: 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);

3956:
3957: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3958: 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);
3959: 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);
3960: 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);
3961: 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);
3962: 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);
3963: 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);
3964: END IF;

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

3957: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3958: 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);
3959: 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);
3960: 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);
3961: 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);
3962: 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);
3963: 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);
3964: END IF;
3965: FOR I in 1..5 LOOP

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

3958: 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);
3959: 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);
3960: 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);
3961: 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);
3962: 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);
3963: 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);
3964: END IF;
3965: FOR I in 1..5 LOOP
3966: do_eval(I);

Line 3968: IF l_contact_contains_str IS NOT NULL THEN

3964: END IF;
3965: FOR I in 1..5 LOOP
3966: do_eval(I);
3967: END LOOP;
3968: IF l_contact_contains_str IS NOT NULL THEN
3969: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3970: END IF;
3971: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3972: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 22 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);

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

3965: FOR I in 1..5 LOOP
3966: do_eval(I);
3967: END LOOP;
3968: IF l_contact_contains_str IS NOT NULL THEN
3969: eval_contact_level(l_contact_contains_str,p_ins_details,l_contact_max_score);
3970: END IF;
3971: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
3972: hz_utility_v2pub.debug(p_message=>'Evaluating Matches. Threshold : 22 ',p_module_prefix=>'dqm',p_module=>'hz_match_rule_xxx',p_prefix=>NULL,p_msg_level=>fnd_log.level_statement);
3973: END IF;

Line 4794: l_party_contains_str VARCHAR2(32000);

4790: x_num_matches OUT NUMBER
4791: ) IS
4792:
4793: -- Strings to hold the generated Intermedia query strings
4794: l_party_contains_str VARCHAR2(32000);
4795: l_match_idx NUMBER;
4796: l_party_site_contains_str VARCHAR2(32000);
4797: l_contact_contains_str VARCHAR2(32000);
4798: l_contact_pt_contains_str VARCHAR2(32000);

Line 4796: l_party_site_contains_str VARCHAR2(32000);

4792:
4793: -- Strings to hold the generated Intermedia query strings
4794: l_party_contains_str VARCHAR2(32000);
4795: l_match_idx NUMBER;
4796: l_party_site_contains_str VARCHAR2(32000);
4797: l_contact_contains_str VARCHAR2(32000);
4798: l_contact_pt_contains_str VARCHAR2(32000);
4799: l_tmp VARCHAR2(32000);
4800:

Line 4797: l_contact_contains_str VARCHAR2(32000);

4793: -- Strings to hold the generated Intermedia query strings
4794: l_party_contains_str VARCHAR2(32000);
4795: l_match_idx NUMBER;
4796: l_party_site_contains_str VARCHAR2(32000);
4797: l_contact_contains_str VARCHAR2(32000);
4798: l_contact_pt_contains_str VARCHAR2(32000);
4799: l_tmp VARCHAR2(32000);
4800:
4801: -- Other local variables

Line 4798: l_contact_pt_contains_str VARCHAR2(32000);

4794: l_party_contains_str VARCHAR2(32000);
4795: l_match_idx NUMBER;
4796: l_party_site_contains_str VARCHAR2(32000);
4797: l_contact_contains_str VARCHAR2(32000);
4798: l_contact_pt_contains_str VARCHAR2(32000);
4799: l_tmp VARCHAR2(32000);
4800:
4801: -- Other local variables
4802: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

4871: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
4872:
4873: -- ************************************************************
4874: -- STEP 2. Setup of intermedia query strings for Acquisition query
4875: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4876: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4877: 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);
4878:
4879: -- Setup Search Context ID

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

4872:
4873: -- ************************************************************
4874: -- STEP 2. Setup of intermedia query strings for Acquisition query
4875: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
4876: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
4877: 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);
4878:
4879: -- Setup Search Context ID
4880: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

4881: x_search_ctx_id := l_search_ctx_id;
4882:
4883: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4884: 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);
4885: 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);
4886: 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);
4887: 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);
4888: END IF;
4889: IF l_party_site_contains_str IS NULL THEN

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

4882:
4883: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
4884: 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);
4885: 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);
4886: 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);
4887: 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);
4888: END IF;
4889: IF l_party_site_contains_str IS NULL THEN
4890: defps := 1;

Line 4889: IF l_party_site_contains_str IS NULL THEN

4885: 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);
4886: 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);
4887: 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);
4888: END IF;
4889: IF l_party_site_contains_str IS NULL THEN
4890: defps := 1;
4891: END IF;
4892: IF l_contact_pt_contains_str IS NULL THEN
4893: defcpt := 1;

Line 4892: IF l_contact_pt_contains_str IS NULL THEN

4888: END IF;
4889: IF l_party_site_contains_str IS NULL THEN
4890: defps := 1;
4891: END IF;
4892: IF l_contact_pt_contains_str IS NULL THEN
4893: defcpt := 1;
4894: END IF;
4895:
4896: IF l_party_site_contains_str IS NOT NULL THEN

Line 4896: IF l_party_site_contains_str IS NOT NULL THEN

4892: IF l_contact_pt_contains_str IS NULL THEN
4893: defcpt := 1;
4894: END IF;
4895:
4896: IF l_party_site_contains_str IS NOT NULL THEN
4897: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4898: LOOP
4899: FETCH l_party_site_cur INTO
4900: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;

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

4893: defcpt := 1;
4894: END IF;
4895:
4896: IF l_party_site_contains_str IS NOT NULL THEN
4897: open_party_site_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_party_site_contains_str,NULL, null,'N', 'N',l_party_site_cur);
4898: LOOP
4899: FETCH l_party_site_cur INTO
4900: l_party_site_id, l_ps_party_id, l_ps_contact_id , l_TX3, l_TX4, l_TX11;
4901: EXIT WHEN l_party_site_cur%NOTFOUND;

Line 4920: IF l_contact_pt_contains_str IS NOT NULL THEN

4916: END LOOP;
4917: CLOSE l_party_site_cur;
4918: END IF;
4919:
4920: IF l_contact_pt_contains_str IS NOT NULL THEN
4921: SAVEPOINT eval_start;
4922: IF l_match_str = ' AND ' OR (
4923: ((l_cpt_max_score/l_entered_max_score)<(100/445))
4924: ) THEN

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

4924: ) THEN
4925: FORALL I in 1..l_match_ps_list.COUNT
4926: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4927: values (l_search_ctx_id,l_match_ps_list(I));
4928: 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');
4929: ELSE
4930: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4931: END IF;
4932: LOOP

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

4926: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
4927: values (l_search_ctx_id,l_match_ps_list(I));
4928: 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');
4929: ELSE
4930: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
4931: END IF;
4932: LOOP
4933: FETCH l_contact_pt_cur INTO
4934: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1;

Line 5030: l_party_contains_str VARCHAR2(32000);

5026: x_num_matches OUT NUMBER
5027: ) IS
5028:
5029: -- Strings to hold the generated Intermedia query strings
5030: l_party_contains_str VARCHAR2(32000);
5031: l_party_site_contains_str VARCHAR2(32000);
5032: l_contact_contains_str VARCHAR2(32000);
5033: l_contact_pt_contains_str VARCHAR2(32000);
5034: l_tmp VARCHAR2(32000);

Line 5031: l_party_site_contains_str VARCHAR2(32000);

5027: ) IS
5028:
5029: -- Strings to hold the generated Intermedia query strings
5030: l_party_contains_str VARCHAR2(32000);
5031: l_party_site_contains_str VARCHAR2(32000);
5032: l_contact_contains_str VARCHAR2(32000);
5033: l_contact_pt_contains_str VARCHAR2(32000);
5034: l_tmp VARCHAR2(32000);
5035:

Line 5032: l_contact_contains_str VARCHAR2(32000);

5028:
5029: -- Strings to hold the generated Intermedia query strings
5030: l_party_contains_str VARCHAR2(32000);
5031: l_party_site_contains_str VARCHAR2(32000);
5032: l_contact_contains_str VARCHAR2(32000);
5033: l_contact_pt_contains_str VARCHAR2(32000);
5034: l_tmp VARCHAR2(32000);
5035:
5036: -- Other local variables

Line 5033: l_contact_pt_contains_str VARCHAR2(32000);

5029: -- Strings to hold the generated Intermedia query strings
5030: l_party_contains_str VARCHAR2(32000);
5031: l_party_site_contains_str VARCHAR2(32000);
5032: l_contact_contains_str VARCHAR2(32000);
5033: l_contact_pt_contains_str VARCHAR2(32000);
5034: l_tmp VARCHAR2(32000);
5035:
5036: -- Other local variables
5037: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5104: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5105:
5106: -- ************************************************************
5107: -- STEP 2. Setup of intermedia query strings for Acquisition query
5108: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5109: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5110: 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);
5111:
5112: -- Setup Search Context ID

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

5105:
5106: -- ************************************************************
5107: -- STEP 2. Setup of intermedia query strings for Acquisition query
5108: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5109: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5110: 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);
5111:
5112: -- Setup Search Context ID
5113: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

5114: x_search_ctx_id := l_search_ctx_id;
5115:
5116: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5117: 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);
5118: 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);
5119: 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);
5120: 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);
5121: END IF;
5122: IF l_contact_contains_str IS NULL THEN

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

5115:
5116: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5117: 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);
5118: 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);
5119: 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);
5120: 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);
5121: END IF;
5122: IF l_contact_contains_str IS NULL THEN
5123: defct := 1;

Line 5122: IF l_contact_contains_str IS NULL THEN

5118: 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);
5119: 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);
5120: 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);
5121: END IF;
5122: IF l_contact_contains_str IS NULL THEN
5123: defct := 1;
5124: END IF;
5125: IF l_contact_pt_contains_str IS NULL THEN
5126: defcpt := 1;

Line 5125: IF l_contact_pt_contains_str IS NULL THEN

5121: END IF;
5122: IF l_contact_contains_str IS NULL THEN
5123: defct := 1;
5124: END IF;
5125: IF l_contact_pt_contains_str IS NULL THEN
5126: defcpt := 1;
5127: END IF;
5128:
5129: IF l_contact_contains_str IS NOT NULL THEN

Line 5129: IF l_contact_contains_str IS NOT NULL THEN

5125: IF l_contact_pt_contains_str IS NULL THEN
5126: defcpt := 1;
5127: END IF;
5128:
5129: IF l_contact_contains_str IS NOT NULL THEN
5130: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5131: LOOP
5132: FETCH l_contact_cur INTO
5133: l_org_contact_id, l_ct_party_id , l_TX2, l_TX6, l_TX5;

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

5126: defcpt := 1;
5127: END IF;
5128:
5129: IF l_contact_contains_str IS NOT NULL THEN
5130: open_contact_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_contains_str,NULL, null, l_contact_cur);
5131: LOOP
5132: FETCH l_contact_cur INTO
5133: l_org_contact_id, l_ct_party_id , l_TX2, l_TX6, l_TX5;
5134: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5151: IF l_contact_pt_contains_str IS NOT NULL THEN

5147: END LOOP;
5148: CLOSE l_contact_cur;
5149: END IF;
5150:
5151: IF l_contact_pt_contains_str IS NOT NULL THEN
5152: SAVEPOINT eval_start;
5153: IF l_match_str = ' AND ' OR (
5154: ((l_cpt_max_score/l_entered_max_score)<(100/445))
5155: ) THEN

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

5155: ) THEN
5156: FORALL I in 1..l_match_contact_list.COUNT
5157: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5158: values (l_search_ctx_id,l_match_contact_list(I));
5159: 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');
5160: ELSE
5161: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5162: END IF;
5163: LOOP

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

5157: INSERT INTO HZ_DQM_PARTIES_GT (search_context_id, party_id)
5158: values (l_search_ctx_id,l_match_contact_list(I));
5159: 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');
5160: ELSE
5161: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5162: END IF;
5163: LOOP
5164: FETCH l_contact_pt_cur INTO
5165: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1;

Line 5259: l_contact_pt_contains_str VARCHAR2(32000);

5255: ) IS
5256:
5257:
5258: -- Strings to hold the generated Intermedia query strings
5259: l_contact_pt_contains_str VARCHAR2(32000);
5260: l_tmp VARCHAR2(32000);
5261:
5262: -- Other local variables
5263: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5314: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5315:
5316: -- ************************************************************
5317: -- STEP 2. Setup of intermedia query strings for Acquisition query
5318: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5319: 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);
5320:
5321: -- Setup Search Context ID
5322: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;

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

5322: SELECT hz_search_ctx_s.nextval INTO l_search_ctx_id FROM dual;
5323: x_search_ctx_id := l_search_ctx_id;
5324: IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
5325: 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);
5326: 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);
5327: 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);
5328: END IF;
5329:
5330: IF l_contact_pt_contains_str IS NOT NULL THEN

Line 5330: IF l_contact_pt_contains_str IS NOT NULL THEN

5326: 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);
5327: 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);
5328: END IF;
5329:
5330: IF l_contact_pt_contains_str IS NOT NULL THEN
5331: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5332: cnt := 1;
5333: LOOP
5334: FETCH l_contact_pt_cur INTO

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

5327: 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);
5328: END IF;
5329:
5330: IF l_contact_pt_contains_str IS NOT NULL THEN
5331: open_contact_pt_cursor(NULL, P_PARTY_ID, p_restrict_sql, l_contact_pt_contains_str,NULL, null,'N', 'N',l_contact_pt_cur);
5332: cnt := 1;
5333: LOOP
5334: FETCH l_contact_pt_cur INTO
5335: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1;

Line 5402: l_party_contains_str VARCHAR2(32000);

5398: x_search_ctx_id IN OUT NUMBER
5399: ) IS
5400:
5401: -- Strings to hold the generated Intermedia query strings
5402: l_party_contains_str VARCHAR2(32000);
5403: l_party_site_contains_str VARCHAR2(32000);
5404: l_contact_contains_str VARCHAR2(32000);
5405: l_contact_pt_contains_str VARCHAR2(32000);
5406: l_tmp VARCHAR2(32000);

Line 5403: l_party_site_contains_str VARCHAR2(32000);

5399: ) IS
5400:
5401: -- Strings to hold the generated Intermedia query strings
5402: l_party_contains_str VARCHAR2(32000);
5403: l_party_site_contains_str VARCHAR2(32000);
5404: l_contact_contains_str VARCHAR2(32000);
5405: l_contact_pt_contains_str VARCHAR2(32000);
5406: l_tmp VARCHAR2(32000);
5407:

Line 5404: l_contact_contains_str VARCHAR2(32000);

5400:
5401: -- Strings to hold the generated Intermedia query strings
5402: l_party_contains_str VARCHAR2(32000);
5403: l_party_site_contains_str VARCHAR2(32000);
5404: l_contact_contains_str VARCHAR2(32000);
5405: l_contact_pt_contains_str VARCHAR2(32000);
5406: l_tmp VARCHAR2(32000);
5407:
5408: -- Other local variables

Line 5405: l_contact_pt_contains_str VARCHAR2(32000);

5401: -- Strings to hold the generated Intermedia query strings
5402: l_party_contains_str VARCHAR2(32000);
5403: l_party_site_contains_str VARCHAR2(32000);
5404: l_contact_contains_str VARCHAR2(32000);
5405: l_contact_pt_contains_str VARCHAR2(32000);
5406: l_tmp VARCHAR2(32000);
5407:
5408: -- Other local variables
5409: l_match_str VARCHAR2(30); -- Match type (AND or OR)

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

5481: g_score_until_thresh := false;
5482: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5483: -- ************************************************************
5484: -- STEP 2. Setup of intermedia query strings for Acquisition query
5485: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5486: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5487: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5488: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5489:

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

5482: IF l_entered_max_score = 0 THEN l_entered_max_score:=1; END IF;
5483: -- ************************************************************
5484: -- STEP 2. Setup of intermedia query strings for Acquisition query
5485: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5486: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5487: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5488: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5489:
5490: -- Setup Search Context ID

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

5483: -- ************************************************************
5484: -- STEP 2. Setup of intermedia query strings for Acquisition query
5485: l_party_site_contains_str := INIT_PARTY_SITES_QUERY(l_match_str,l_tmp);
5486: l_contact_contains_str := INIT_CONTACTS_QUERY(l_match_str,l_tmp);
5487: l_contact_pt_contains_str := INIT_CONTACT_POINTS_QUERY(l_match_str,l_tmp);
5488: init_score_context(p_party_search_rec,p_party_site_list,p_contact_list,p_contact_point_list);
5489:
5490: -- Setup Search Context ID
5491: IF x_search_ctx_id IS NULL THEN

Line 5507: IF l_party_site_contains_str IS NOT NULL THEN

5503: 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_TX41,l_TX44,l_TX46,1);
5504: END LOOP;
5505: CLOSE l_party_cur;
5506:
5507: IF l_party_site_contains_str IS NOT NULL THEN
5508: l_max_score := 0;
5509: l_max_id := 0;
5510: l_max_idx := 0;
5511: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN

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

5508: l_max_score := 0;
5509: l_max_id := 0;
5510: l_max_idx := 0;
5511: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5512: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5513: ELSE
5514: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5515: END IF;
5516: LOOP

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

5510: l_max_idx := 0;
5511: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_addr_info THEN
5512: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'Y','N',l_party_site_cur);
5513: ELSE
5514: open_party_site_cursor(null, p_party_id, null, l_party_site_contains_str,NULL,NULL, 'N','N',l_party_site_cur);
5515: END IF;
5516: LOOP
5517: FETCH l_party_site_cur INTO
5518: l_party_site_id, l_ps_party_id,l_ps_contact_id , l_TX3, l_TX4, l_TX11;

Line 5536: IF l_contact_contains_str IS NOT NULL THEN

5532: 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);
5533: END IF;
5534: END IF;
5535:
5536: IF l_contact_contains_str IS NOT NULL THEN
5537: l_max_score := 0;
5538: l_max_id := 0;
5539: l_max_idx := 0;
5540: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);

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

5536: IF l_contact_contains_str IS NOT NULL THEN
5537: l_max_score := 0;
5538: l_max_id := 0;
5539: l_max_idx := 0;
5540: open_contact_cursor(null, p_party_id, null, l_contact_contains_str,NULL, null, l_contact_cur);
5541: LOOP
5542: FETCH l_contact_cur INTO
5543: l_org_contact_id, l_ct_party_id , l_TX2, l_TX6, l_TX5;
5544: EXIT WHEN l_contact_cur%NOTFOUND;

Line 5561: IF l_contact_pt_contains_str IS NOT NULL THEN

5557: 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_TX2,l_max_TX6,l_max_TX5,l_max_idx);
5558: END IF;
5559: END IF;
5560:
5561: IF l_contact_pt_contains_str IS NOT NULL THEN
5562: l_max_score := 0;
5563: l_max_id := 0;
5564: l_max_idx := 0;
5565: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN

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

5562: l_max_score := 0;
5563: l_max_id := 0;
5564: l_max_idx := 0;
5565: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5566: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5567: ELSE
5568: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5569: END IF;
5570: LOOP

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

5564: l_max_idx := 0;
5565: IF p_party_search_rec.PARTY_TYPE = 'PERSON' AND l_use_contact_cpt_info THEN
5566: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'Y','N',l_contact_pt_cur);
5567: ELSE
5568: open_contact_pt_cursor(null, p_party_id, null, l_contact_pt_contains_str,NULL,NULL, 'N','N',l_contact_pt_cur);
5569: END IF;
5570: LOOP
5571: FETCH l_contact_pt_cur INTO
5572: l_contact_pt_id, l_cpt_type, l_cpt_party_id, l_cpt_ps_id, l_cpt_contact_id , l_TX1;